diff options
| -rw-r--r-- | LedgerSMB/Reconciliation.pm | 8 | ||||
| -rw-r--r-- | UI/Reconciliation/correct.html | 16 | ||||
| -rw-r--r-- | UI/Reconciliation/pending.html | 54 | ||||
| -rw-r--r-- | scripts/Reconciliation.pl | 42 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 36 |
5 files changed, 119 insertions, 37 deletions
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 @@ <table> <tr> <td> - Account/SCN + Account </td> <td> - <input type="input" name="scn" value="<?lsmb entry.scn?>"/> + <input type="input" name="scn" value="<?lsmb entry.account?>"/> </td> </tr> <tr> <td> - Amount + SCN </td> <td> - <input type="input" name="amount" value="<?lsmb entry.their_balance?>"/> + <input type="input" name="scn" value="<?lsmb entry.scn?>"/> </td> </tr> <tr> <td> - Cleared + Amount </td> <td> - <input type="input" name="cleared" value="<?lsmb entry.clear_time?>"/> + <input type="input" name="amount" value="<?lsmb entry.their_balance?>"/> </td> </tr> <tr> <td> - Cleared + Cleared at </td> <td> - <input type="input" name="cleared" value="<?lsmb entry.clear?>"/> + <input type="input" name="cleared" value="<?lsmb entry.clear_time?>"/> </td> </tr> <tr> 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 @@ +<div> + <?lsmb IF pending?> + <table> + <tr> + <td> + Account + </td> + <td>SCN</td> + <td>Entry</td> + <td>Amount</td> + <td>Transaction Date</td> + </tr> + <?lsmb FOR trans IN pending?> + + <td><?lsmb trans.account?></td> + <td><?lsmb trans.source?></td> + <td><a href="transactions.pl?entry=<?lsmb trans.entry_id?>">Full Entry</a></td> + <td><?lsmb trans.transdate?></td> + + <?lsmb END?> + <?lsmb ELSE?> + <form name="month" method="reconciliation.pl" action="POST"> + <input type="hidden" name="action" value="pending"/> + Year: <select name="year"> + <option value="2008">2008</option> + <option value="2007">2008</option> + <option value="2006">2008</option> + <option value="2005">2008</option> + <option value="2004">2008</option> + <option value="2003">2008</option> + <option value="2002">2008</option> + <option value="2001">2008</option> + <option value="2000">2000</option> + </select> + + Month: <select name="month"> + <option value="12">December</option> + <option value="11">November</option> + <option value="10">October</option> + <option value="9">September</option> + <option value="8">August</option> + <option value="7">July</option> + <option value="6">June</option> + <option value="5">May</option> + <option value="4">April</option> + <option value="3">March</option> + <option value="2">February</option> + <option value="1">January</option> + </select> + <input type="submit" value="Get Pending Transactions"> + </form> + <?lsmb END?> + </table> +</div>
\ 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 $$ |