diff options
| -rw-r--r-- | LedgerSMB/Reconciliation.pm | 24 | ||||
| -rw-r--r-- | Reconciliation.pl | 3 | ||||
| -rw-r--r-- | UI/approved.html | 28 | ||||
| -rw-r--r-- | UI/correct.html | bin | 0 -> 72 bytes | |||
| -rw-r--r-- | UI/corrections.html | 39 | ||||
| -rw-r--r-- | UI/get_password.html | 45 | ||||
| -rw-r--r-- | UI/report.html | 52 | ||||
| -rw-r--r-- | UI/voucher.html | 35 | ||||
| -rw-r--r-- | scripts/Reconciliation.pl | 110 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 51 |
10 files changed, 374 insertions, 13 deletions
diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm index f12251e5..6dca96d2 100644 --- a/LedgerSMB/Reconciliation.pm +++ b/LedgerSMB/Reconciliation.pm @@ -65,6 +65,16 @@ arrayref of entries in the table for the given report_id. Returns undef in the event of no records found. +=item get_corrections($self, $report_id, $entry_id) + +Returns the corrections made for the selected report and entry. +Returns undef in the event of no corrections found. + +=item entry ($self,$report_id,$entry_id) + +Returns a single entry from the pending reports table, either cleared or +uncleared. + =back =head1 Copyright (C) 2007, The LedgerSMB core team. @@ -155,4 +165,18 @@ sub get_report { return $self->report($self->{report_id}); } +sub get_corrections { + + my $self = shift @_; + + return $self->corrections($self->{report_id},$self->{entry_id}); +} + +sub entry { + + my $self = shift @_; + + return $self->single_entry($self->{report_id},$self->{entry_id}); +} + 1;
\ No newline at end of file diff --git a/Reconciliation.pl b/Reconciliation.pl new file mode 100644 index 00000000..76f60115 --- /dev/null +++ b/Reconciliation.pl @@ -0,0 +1,3 @@ +#!/usr/bin/perl + +require "lsmb-request.pl"; diff --git a/UI/approved.html b/UI/approved.html new file mode 100644 index 00000000..16afc6cc --- /dev/null +++ b/UI/approved.html @@ -0,0 +1,28 @@ +<div> + <center> + Bank statement for <?lsmbaccount?> on <?lsmbdate?> has been approved! + </center> +</div> + +<table border=0 style="opacity:50%;"> + + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + <td>Error Corrections</td> + <td>Error Code</td> + </tr> + + <?lsmb FOREACH row = records ?> + <tr> + <td><?lsmb row.clear_time ?></td> + <td><?lsmb row.transaction_type ?> </td> + <td><?lsmb row.our_balance ?></td> + <td><?lsmb row.their_balance?></td> + <td><?lsmb row.corrections ?></td> + <td><?lsmb row.errorcode ?></td> + </tr> + <?lsmb END ?> +</table>
\ No newline at end of file diff --git a/UI/correct.html b/UI/correct.html Binary files differnew file mode 100644 index 00000000..e9cb708a --- /dev/null +++ b/UI/correct.html diff --git a/UI/corrections.html b/UI/corrections.html new file mode 100644 index 00000000..bdf7424a --- /dev/null +++ b/UI/corrections.html @@ -0,0 +1,39 @@ +<table border=0> + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + </tr> + <tr> + <td><?lsmb entry.clear_time ?></td> + <td><?lsmb entry.transaction_type ?> </td> + <td><?lsmb entry.our_balance ?></td> + <td><?lsmb entry.their_balance?></td> + </tr> +</table> + +<?lsmb IF NOT corrections ?> +Corrections: +<table border=0> + <?lsmb FOREACH row = corrections ?> + <tr> + <td> + <div> + <span><?lsmbrow.user?> at <?lsmbrow.insert_time?></span> + <div> + <?lsmbrow.reason?> + </div> + </div> + </td> + </tr> + <?lsmbEND?> + +</table> +<?lsmbELSE?> + +<div> + No corrections found. +</div> + +<?lsmbEND?>
\ No newline at end of file diff --git a/UI/get_password.html b/UI/get_password.html new file mode 100644 index 00000000..4f3bfc9a --- /dev/null +++ b/UI/get_password.html @@ -0,0 +1,45 @@ +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" + "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> +<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> +<head> + <title><?lsmb titlebar ?></title> + <meta http-equiv="Pragma" content="no-cache" /> + <meta http-equiv="Expires" content="-1" /> + <link rel="shortcut icon" href="favicon.ico" type="image/x-icon" /> + <link rel="stylesheet" href="css/<?lsmb stylesheet ?>" type="text/css" /> + <link rel="stylesheet" href="UI/employee.css" type="text/css" /> + + <meta http-equiv="content-type" + content="text/html; charset=utf-8" /> + <meta name="robots" content="noindex,nofollow" /> +<script language="JavaScript" type="text/javascript"> +<!-- +function focus_input(){ + document.getpassword.password.focus(); +} +// End --> +</script> +</head> +<body onload="focus_input()"> +<?lsmb IF sessionexpired ?> + <p><span class="warning_label"> + <?lsmb text('Session expired!') ?> + </span></p> +<?lsmb END ?> +<form method="post" action="<?lsmb script ?>" name="getpassword"> + +<div> + <span align=right class="label"> <?lsmb text('Password') ?> </span> + <span class="input"><input type="password" name="password" size="30"/> + </span> + <span class="input"><button type="submit" value="continue"> + <?lsmb text('Continue') ?> + </button></span> +</div> + +<?lsmb FOREACH var = hidden ?> +<input name="<?lsmb var.name ?>" type="hidden" value="<?lsmb var.value ?>" /> +</form> + +</body> +</html> diff --git a/UI/report.html b/UI/report.html new file mode 100644 index 00000000..cd6c8972 --- /dev/null +++ b/UI/report.html @@ -0,0 +1,52 @@ +<center>Reconciliation Report for [% total.account %] for the month of [%total.month%]</center> + +<center> + [%IF total.errorcode != 0 %] + <div style="color:blue; border-style:solid; border-width:1px; border-color: blue;"> + [%ELSE%] + <div style="color:red; border-style:solid; border-width:1px; border-color: blue;"> + [%END%] + Our Balance: total.our_balance | Bank Balance: total.their_balance + </div> +</center> + +<center>Report generated by [% total.user %]</center> + +[% if recon.error %] +<div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" > + [% recon.error %] +</div> +[%end%] + +<table border=0> + + <tr> + <td>Clear date</td> + <td>Transaction Type</td> + <td>Our Balance</td> + <td>Their Balance</td> + <td>Error Corrections</td> + <td>Error Code</td> + </tr> + + [% FOREACH row = records %] + [%IF row.errorcode != 0 %] + <tr style="background-color:red;"> + [% ELSIF row.id = corrected %] + <tr style="background-color:yellow;"> + [%ELSE%] + <tr> + [%END%] + <td>[% row.clear_time %]</td> + <td>[% row.transaction_type %] </td> + <td>[% row.our_balance %]</td> + <td>[% row.their_balance%]</td> + <td>[% row.corrections %]</td> + [% IF row.errorcode > 0 %] + <td>[% row.errorcode %] <a href="/reconciliation.pl?corrections&entry=[%row.entry_id%]">View Corrections</a> </td> + [%ELSE%] + <td>0</td> + [%END%] + </tr> + [% END %] +</table>
\ No newline at end of file diff --git a/UI/voucher.html b/UI/voucher.html new file mode 100644 index 00000000..4cf6bf29 --- /dev/null +++ b/UI/voucher.html @@ -0,0 +1,35 @@ +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" + "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> +<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> +<head> + <title><?lsmb titlebar ?></title> + <meta http-equiv="Pragma" content="no-cache" /> + <meta http-equiv="Expires" content="-1" /> + <link rel="shortcut icon" href="favicon.ico" type="image/x-icon" /> + <link rel="stylesheet" href="css/<?lsmb stylesheet ?>" type="text/css" /> + <link rel="stylesheet" href="UI/employee.css" type="text/css" /> + + <meta http-equiv="content-type" + content="text/html; charset=<?lsmb charset ?>" /> + <meta name="robots" content="noindex,nofollow" /> +</head> +<body> +<div id="title" class="pageheader"><span>GL Voucher Reports</span></div> +<div id="filter"> + <div id="date_from" class="header_entry"> + <span class="label" id="date_from_label">Date From:</span> + <span class="field" id="date_from_field"><input name="date_from"></span> + </div> + <div id="date_to" class="header_entry"> + <span class="label" id="date_to_label">Date To:</span> + <span class="field" id="date_to_field"><input name="date_to"></span> + </div> + <div id="include_date" class="header_entry"> + <span class="label" id="include_date_label">And Date:</span> + <span class="field" id="include_date_field"> + <input name="include_date"> + </span> + </div> +</body> +</body> +</html> diff --git a/scripts/Reconciliation.pl b/scripts/Reconciliation.pl index 189eecb4..8826f037 100644 --- a/scripts/Reconciliation.pl +++ b/scripts/Reconciliation.pl @@ -2,8 +2,21 @@ =head1 NAME +LedgerSMB::Scripts::Reconciliation - LedgerSMB class defining the Controller +functions, template instantiation and rendering. + +=head1 SYOPSIS + +This module acts as the UI controller class for Reconciliation. It controls +interfacing with the Core Logic and database layers. + +=head1 METHODS + =cut +# NOTE: This is a first draft modification to use the current parameter type. +# It will certainly need some fine tuning on my part. Chris + package LedgerSMB::Scripts::Reconciliation; use LedgerSMB::Template; @@ -24,7 +37,7 @@ report_id. =cut sub display_report { - + my ($class, $request) = @_; my $recon = LedgerSMB::Employee->new(base => $request, copy => 'all'); my $template = LedgerSMB::Template->new( user=>$user, template => "reconciliation_report.html", language => $user->{language}, @@ -36,6 +49,7 @@ sub display_report { } sub search { + my ($class, $request) = @_; my $search = LedgerSMB::Employee->new(base => $request, copy => 'all'); $employee->{search_results} = $employee->search(); my $template = LedgerSMB::Template->new( user => $user, @@ -61,14 +75,38 @@ This is to prevent arbitrary editing of the database by unscrupulous users. =cut sub correct { + my ($class, $request) = @_; my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); - - my $template = LedgerSMB::Template->new( user => $user, - template => 'reconciliation_correct.html', language => $user->{language}, - format => 'html'); + $recon->correct_entry(); - $template->render($recon->get_report()); + + if ($recon->{corrected_id}) { + + my $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation_report.html', language => $user->{language}, + format => 'html'); + + $template->render( { + corrected=> $recon->{corrected_id}, + report=> $recon->get_report(), + total=> $recon->get_total() + } ); + } + else { + + # indicate we were unable to correct this entry, with the error code + # spat back to us by the DB. + my $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation_report.html', language => $user->{language}, + format => 'html'); + + $template->render( { + recon => $recon, + report=> $recon->get_report(), + total=> $recon->get_total() + } ); + } } =pod @@ -88,6 +126,7 @@ it has been created. =cut sub new_report { + my ($class, $request) = @_; # how are we going to allow this to be created? Grr. # probably select a list of statements that are available to build # reconciliation reports with. @@ -116,7 +155,7 @@ sub new_report { =over -=item ($self, $request, $user) +=item approve ($self, $request, $user) Requires report_id @@ -131,6 +170,12 @@ the uncorrected entries. =cut sub approve { + my ($class, $request) = @_; + + # Approve will also display the report in a blurred/opaqued out version, + # with the controls removed/disabled, so that we know that it has in fact + # been cleared. This will also provide for return-home links, auditing, + # etc. my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all'); @@ -148,11 +193,60 @@ sub approve { template => 'reconciliation_report.html', language => $user->{language}, format => 'html'); $report = $recon->get_report(); + ## relies on foreknowledge in the template ## we basically tell the template, we can't approve, this uncorrected ## error is preventing us. + $report->{ error } = { approval => 1 }; } $template->render($report); } -1;
\ No newline at end of file + +=pod + +=over + +=item corrections ($self, $request, $user) + +Requires report_id and entry_id. + +Loads the selected entry id and all corrections associated with it. If there +aren't any corrections, it will display "no corrections found". +=back + +=cut + +sub corrections { + my ($class, $request) = @_; + + # Load the corrections for a given report & entry id. + # possibly should use a "micro" popup window? + + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all'); + + my $template; + + $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation_corrected.html', language => $user->{language}, + format => 'html'); + + return $template->render( + { + corrections=>$recon->get_corrections(), + entry=>$recon->entry($self->{report_id}, $self->{entry_id}) + } + ); +} + +1; + +=pod + +=head1 Copyright (C) 2007, The LedgerSMB core team. + +This file is licensed under the Gnu General Public License version 2, or at your +option any later version. A copy of the license should have been included with +your software. + +=cut diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql index 2700a2fb..1dfc7d7e 100644 --- a/sql/modules/Reconciliaton.sql +++ b/sql/modules/Reconciliaton.sql @@ -13,6 +13,15 @@ CREATE TABLE pending_reports ( overlook boolean not null default 'f' ); +CREATE TABLE report_corrections ( + id serial primary key not null, + correction_id int not null default 1, + entry references pending_reports(id) not null, + user text not null, + reason text not null, + insert_time timestamptz not null default now() +); + -- to correct OUR wrong amount. CREATE OR REPLACE FUNCTION reconciliation_correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$ @@ -371,14 +380,46 @@ CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns s AND scn = -1; IF NOT FOUND THEN -- I think this is a fairly major error condition - - RAISE EXCEPTION "No Bank Total found." - + RAISE EXCEPTION "No Bank Total found."; ELSE - return row; - END IF; END; $$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION reconciliation_corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$ + + DECLARE + corr report_corrections; + BEGIN + + SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1; + IF NOT FOUND THEN + RAISE EXCEPTION "No corrections for selected entry."; + ELSE + FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP + RETURN NEXT corr; + END LOOP; + END IF; + END; + +$$ language 'plplsql'; + +CREATE OR REPLACE FUNCTION reconciliation_single_entry (in_report_id INT, in_id INT) returns setof pending_reports AS + + DECLARE + row pending_reports; + BEGIN + + SELECT INTO row FROM pending_reports WHERE report_id = in_report_id and id = in_id LIMIT 1; + -- if there's more than one, that's a Bad Thing + + IF NOT FOUND THEN + RAISE EXCEPTION "Could not find selected report entry"; + ELSE + RETURN row; + END IF; + END; + +$$ language 'plpgsql';
\ No newline at end of file |