From f31f0fd95aa2eb2e61f09900a2a011984da5a274 Mon Sep 17 00:00:00 2001 From: aurynn_cmd Date: Tue, 1 Jul 2008 23:34:41 +0000 Subject: Adding Pending Transactions support to reconciliation. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2182 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/Reconciliation.pm | 8 +++++++ UI/Reconciliation/correct.html | 16 ++++++------- UI/Reconciliation/pending.html | 54 ++++++++++++++++++++++++++++++++++++++++++ scripts/Reconciliation.pl | 42 ++++++++++++++++++++++++++++++++ sql/modules/Reconciliaton.sql | 36 ++++++---------------------- 5 files changed, 119 insertions(+), 37 deletions(-) create mode 100644 UI/Reconciliation/pending.html diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm index f0f57804..458b757c 100644 --- a/LedgerSMB/Reconciliation.pm +++ b/LedgerSMB/Reconciliation.pm @@ -228,6 +228,14 @@ sub search { ); } +sub get_pending { + + my $self = shift @_; + return $self->exec_method( + funcname=>'reconciliation__pending', + args=>[$self->{month}] + ); +} 1; \ No newline at end of file diff --git a/UI/Reconciliation/correct.html b/UI/Reconciliation/correct.html index b74ab4b0..3921f084 100644 --- a/UI/Reconciliation/correct.html +++ b/UI/Reconciliation/correct.html @@ -4,34 +4,34 @@ diff --git a/UI/Reconciliation/pending.html b/UI/Reconciliation/pending.html new file mode 100644 index 00000000..c2636d09 --- /dev/null +++ b/UI/Reconciliation/pending.html @@ -0,0 +1,54 @@ +
+ +
- Account/SCN + Account - +
- Amount + SCN - +
- Cleared + Amount - +
- Cleared + Cleared at - +
+ + + + + + + + + + + + + + + + + + + Year: + + Month: + + + +
+ Account + SCNEntryAmountTransaction Date
Full Entry
+ \ No newline at end of file diff --git a/scripts/Reconciliation.pl b/scripts/Reconciliation.pl index f4769c8c..9c823a09 100644 --- a/scripts/Reconciliation.pl +++ b/scripts/Reconciliation.pl @@ -358,6 +358,48 @@ sub corrections { } ); } + +=pod + +=over + +=item pending ($self, $request, $user) + +Requires {date} and {month}, to handle the month-to-month pending transactions +in the database. No mechanism is provided to grab ALL pending transactions +from the acc_trans table. + +=back + +=cut + + +sub pending { + + my ($class, $request) = @_; + + my $recon = LedgerSMB::DBObject::Reconciliation->new(base=>request, copy=>'all'); + my $template; + + $template= LedgerSMB::Template->new( + user => $user, + template=>'reconciliation/pending.html', + language=>$user->{language}, + format=>'html' + ); + if ($request->type() eq "POST") { + return $template->render( + { + pending=>$recon->get_pending($request->{year}."-".$request->{month}); + } + ); + } + else { + + return $template->render(); + } +} + # eval { do "scripts/custom/Reconciliation.pl" }; 1; diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql index 5bc88c0e..b8aa2d97 100644 --- a/sql/modules/Reconciliaton.sql +++ b/sql/modules/Reconciliaton.sql @@ -1,7 +1,8 @@ CREATE TABLE reports ( id bigserial primary key not null, report_id int NOT NULL, - scn int, -- What is SCN? + account text not null, + scn text not null, -- What is SCN? their_balance numeric, our_balance numeric, errorcode INT, @@ -78,7 +79,7 @@ CREATE OR REPLACE FUNCTION reconciliation__correct_bank_statement (in_report_id in_user TEXT; BEGIN select into in_user from current_user; - select into current_row from reports where reports.id = in_report_id and reports.scn = in_scn; + select into current_row from reports where reports.id = in_id and reports.report_id = in_report_id; IF NOT FOUND THEN RAISE EXCEPTION 'No such SCN % in this report.', in_scn; END IF; @@ -323,44 +324,21 @@ create or replace function reconciliation__add_entry( END; $$ language 'plpgsql'; -create or replace function reconciliation__pending_transactions (in_report_id INT, in_month TIMESTAMP, in_user INT) RETURNS setof int as $$ +create or replace function reconciliation__pending_transactions (in_month DATE) RETURNS setof acc_trans as $$ DECLARE gl_row acc_trans; BEGIN - FOR gl_row IN select gl.* from acc_trans gl, reports pr where gl.cleared = 'f' and date_trunc('month',gl.transdate) <= date_trunc('month', in_month) - and gl.entry_id <> pr.lid + and gl.entry_id <> pr.ledger_id -- there's no entries in the reports for this LOOP - - INSERT INTO reports ( - report_id, - scn, - their_balance, - our_balance, - error_code, - user, - clear_time, - ledger_id - ) - VALUES ( - in_report_id, -- add it to the report ID - gl_row.source, -- the source control number - 0, -- The banks' amount for the transaction - gl_row.amount, -- our recorded amount - 4, -- The error code, meaning it's uncleared. - in_user, -- the report-generating user - in_month, -- basically, right now. - gl_row.entry_id -- the foreign key to the ledger - ); - + RETURN NEXT gl_row; END LOOP; END; - -$$ language plpgsql; +$$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof reports as $$ -- cgit v1.2.3