diff options
| -rw-r--r-- | LedgerSMB/AA.pm | 5 | ||||
| -rw-r--r-- | LedgerSMB/Reconciliation.pm | 113 | ||||
| -rw-r--r-- | LedgerSMB/Reconciliation/CSV.pm | 69 | ||||
| -rwxr-xr-x | LedgerSMB/Template.pm | 7 | ||||
| -rw-r--r-- | UI/Reconciliation/correct.html | 44 | ||||
| -rw-r--r-- | UI/Reconciliation/list.html | 12 | ||||
| -rw-r--r-- | UI/Reconciliation/report.html | 56 | ||||
| -rw-r--r-- | UI/Reconciliation/search.html | 18 | ||||
| -rw-r--r-- | UI/Reconciliation/upload.html | 17 | ||||
| -rw-r--r-- | scripts/Reconciliation.pl | 253 | ||||
| -rwxr-xr-x | templates/Default-statement.tex | 1 |
11 files changed, 469 insertions, 126 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm index d5d8a1fe..93e64e61 100644 --- a/LedgerSMB/AA.pm +++ b/LedgerSMB/AA.pm @@ -311,9 +311,14 @@ sub post_transaction { # AR/AP Transaction. # ~A $query = qq| +<<<<<<< .mine + INSERT INTO $table (invnumber, person_id) + VALUES (?, (select e.id from person p, entity e, users u +======= INSERT INTO $table (invnumber, person_id, entity_credit_account) VALUES (?, (select e.id from person p, entity e, users u +>>>>>>> .r2156 where u.username = ? AND e.id = u.entity_id AND p.entity_id = e.id ), ?)|; diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm index e1096f63..f0f57804 100644 --- a/LedgerSMB/Reconciliation.pm +++ b/LedgerSMB/Reconciliation.pm @@ -85,14 +85,55 @@ your software. =cut -package LedgerSMB::DBObject::Reconciliation; +package LedgerSMB::Reconciliation; -use base qw(LedgerSMB); +use base qw(LedgerSMB::DBObject); use LedgerSMB::DBObject; +use LedgerSMB::Reconciliation::CSV; # don't need new -#sub reconcile { +sub import_file { + + my $self = shift @_; + + # We need to know what the format is, for the text file. We should have a set of formats + # for the given file, listed in the DB, or based on modules available. + # + # Probably based on modules. + + # my $module = 'LedgerSMB/Reconciliaton/CSV/'.$self->{file_format}; + # require $module."pm"; + # my $obj_name = $module; + # $obj_name =~ s/\//::/g; + # my $parser = $obj_name::new(base=>$self); + + # $self->filename is currently a lie. There's no facility in the LSMB + # design to accomadate an uploaded file. + my $csv = LedgerSMB::Reconciliation::CSV->new(base=>$self); + $csv->process(); + + return $self->{entries}; +} + +sub approve { + + my $self = shift @_; + # the user should be embedded into the $self object. + my $report_id = shift @_; + + my $code = $self->exec_method(funcname=>'report_approve', args=>[$report_id]); # user + + if ($code == 0) { # no problem. + return $code; + } + # this is destined to change as we figure out the Error system. + elsif ($code == 99) { + + $self->error("User $self->{user}->{name} cannot approve report, must be a different user."); + } +} + sub new_report { my $self = shift @_; @@ -102,48 +143,37 @@ sub new_report { # Total is in here somewhere, too - my $report_id = $self->new_report_id()[0]; # gives us a report ID to insert with. + # gives us a report ID to insert with. + my $report_id = $self->exec_method(funcname=>'reconciliation__new_report_id'); # Now that we have this, we need to create the internal report representation. # Ideally, we OUGHT to not return anything here, save the report number. unshift @{$entries}, { scn => -1, balance=> $total, - old_balance=> $self->current_balance, + old_balance=> $self->exec_method(funcname=>'reconciliation__current_balance'), date=>$month }; - for my $entry (@{$entries}) { + for my $entry ( @{$entries} ) { # Codes: # 0 is success # 1 is found, but mismatch # 2 is not found + $code = $self->exec_method( + funcname=>'reconciliation__add_entry', + args=>[ + $report_id, + ] + ); $entry{report_id} = $report_id; $entry{code} = $self->add_entry( $entry ); } - $self->pending_transactions($report_id, $date); + $self->exec_method(funcname=>'reconciliation__pending_transactions', args=>[$report_id, $date]); - return $entries; # returns the report ID. -} - -sub approve { - - my $self = shift @_; - # the user should be embedded into the $self object. - my $report_id = shift @_; - - my $code = $self->report_approve($report_id); # user - - if ($code == 0) { # no problem. - return $code; - } - # this is destined to change as we figure out the Error system. - elsif ($code == 99) { - - $self->error("User $self->{user}->{name} cannot approve report, must be a different user."); - } + return ($report_id, $entries); # returns the report ID. } sub correct_entry { @@ -154,7 +184,10 @@ sub correct_entry { my $new_amount = $self->{new_amount}; #shift @_; # correct should return the new code value - whether or not it actually "matches" - my $code = $self->correct($report_id, $scn, $new_amount); + my $code = $self->exec_method( + funcname=>'reconciliation__correct', + args=>[$report_id, $scn, $new_amount] + ); return $code[0]->{'correct'}; } @@ -162,21 +195,39 @@ sub get_report { my $self = shift @_; - return $self->report($self->{report_id}); + return $self->exec_method(funcname=>'reconciliation__report', args=>[$self->{report_id}]); } sub get_corrections { my $self = shift @_; - return $self->corrections($self->{report_id},$self->{entry_id}); + return $self->exec_method( + funcname=>'reconciliation__corrections', + args=>[$self->{report_id}, $self->{entry_id}] + ); } sub entry { my $self = shift @_; - return $self->single_entry($self->{report_id},$self->{entry_id}); + return $self->exec_method( + funcname=>'reconciliation__single_entry', + args=>[$self->{report_id}, $self->{entry_id}] + ); +} + +sub search { + + my $self = shift @_; + + return $self->exec_method( + funcname=>'reconciliation__search', + args=>[$self->{date_begin}, $self->{date_end}, $self->{account}, $self->{status}] + ); } -1; + + +1;
\ No newline at end of file diff --git a/LedgerSMB/Reconciliation/CSV.pm b/LedgerSMB/Reconciliation/CSV.pm new file mode 100644 index 00000000..f95bf194 --- /dev/null +++ b/LedgerSMB/Reconciliation/CSV.pm @@ -0,0 +1,69 @@ +# CSV parser is basically a framework to handle any CSV files or fixed-width format files. +# Parsers are defined in CSV/parser_type. + +package LedgerSMB::Reconciliation::CSV; + +use base qw/LedgerSMB/; +use Datetime; + +sub load_file { + + my $self = shift @_; + my $filename = shift @_; + my $contents; + do { + + local $/; # I think this is the right way to outrageously cheat + open(FH,$filename); + $contents = <FH>; + } + return $contents; +} + +sub process { + + # thoroughly implementation-dependent. + my $self = shift @_; + my $contents = $self->load_file($self->{csv_filename}); + + foreach my $line (split /\n/,$contents) { + # Unpack for the format it is inexplicably in + ($accno, + $checkno, + $issuedate + $amount + $cleared, + $last_three) = unpack("A10A10A6A10A6A3",$line); + + push @{ $self->{entries} }, { + account_num => $accno, + scn => $checkno, + issue_date => $issuedate, + amount => $amount, + cleared_date => $cleared + }; + } + # Okay, now how do I test to see if this is actually, y'know, bad data. + + for my $line (@{ $self->{entries} }) { + + # First check the account number. + # According to the docs I have, it's all numbers. + + + } + + return; +} + +sub is_error { + + +} + +sub error { + + +} + +1;
\ No newline at end of file diff --git a/LedgerSMB/Template.pm b/LedgerSMB/Template.pm index 3a92e1c2..c7386928 100755 --- a/LedgerSMB/Template.pm +++ b/LedgerSMB/Template.pm @@ -254,9 +254,10 @@ sub render { if (UNIVERSAL::isa($self->{locale}, 'LedgerSMB::Locale')){ $cleanvars->{text} = sub { return $self->{locale}->text(@_)}; - } else { - $cleanvars->{text} = sub { return shift @_ }; - } + } + else { + $cleanvars->{text} = sub { return shift @_ }; + } $format->can('process')->($self, $cleanvars); #return $format->can('postprocess')->($self); diff --git a/UI/Reconciliation/correct.html b/UI/Reconciliation/correct.html index e69de29b..b74ab4b0 100644 --- a/UI/Reconciliation/correct.html +++ b/UI/Reconciliation/correct.html @@ -0,0 +1,44 @@ +<div> + + <div class="title">Correction for Reconciliation Entry</div> + <table> + <tr> + <td> + Account/SCN + </td> + <td> + <input type="input" name="scn" value="<?lsmb entry.scn?>"/> + </td> + </tr> + <tr> + <td> + Amount + </td> + <td> + <input type="input" name="amount" value="<?lsmb entry.their_balance?>"/> + </td> + </tr> + <tr> + <td> + Cleared + </td> + <td> + <input type="input" name="cleared" value="<?lsmb entry.clear_time?>"/> + </td> + </tr> + <tr> + <td> + Cleared + </td> + <td> + <input type="input" name="cleared" value="<?lsmb entry.clear?>"/> + </td> + </tr> + <tr> + <td>Reason</td> + <td><textarea rows="15" cols="15"></textarea></td> + </tr> + </table> + + +</div>
\ No newline at end of file diff --git a/UI/Reconciliation/list.html b/UI/Reconciliation/list.html new file mode 100644 index 00000000..48d9bb31 --- /dev/null +++ b/UI/Reconciliation/list.html @@ -0,0 +1,12 @@ +<div> + <table> + <td> + <tr>ID</tr> + <tr>Account</tr> + <tr></tr> + </td> + <?lsmb FOR result IN results?> + + <?lsmb END?> + </table> +</div>
\ No newline at end of file diff --git a/UI/Reconciliation/report.html b/UI/Reconciliation/report.html index cd6c8972..41c24a9d 100644 --- a/UI/Reconciliation/report.html +++ b/UI/Reconciliation/report.html @@ -1,22 +1,22 @@ -<center>Reconciliation Report for [% total.account %] for the month of [%total.month%]</center> +<center>Reconciliation Report for <?lsmb total.account ?> for the month of <?lsmbtotal.month?></center> <center> - [%IF total.errorcode != 0 %] + <?lsmbIF total.errorcode != 0 ?> <div style="color:blue; border-style:solid; border-width:1px; border-color: blue;"> - [%ELSE%] + <?lsmbELSE?> <div style="color:red; border-style:solid; border-width:1px; border-color: blue;"> - [%END%] + <?lsmbEND?> Our Balance: total.our_balance | Bank Balance: total.their_balance </div> </center> -<center>Report generated by [% total.user %]</center> +<center>Report generated by <?lsmb total.user ?></center> -[% if recon.error %] +<?lsmb if recon.error ?> <div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" > - [% recon.error %] + <?lsmb recon.error ?> </div> -[%end%] +<?lsmbend?> <table border=0> @@ -27,26 +27,34 @@ <td>Their Balance</td> <td>Error Corrections</td> <td>Error Code</td> + <td></td> </tr> - [% FOREACH row = records %] - [%IF row.errorcode != 0 %] + <?lsmb FOREACH row = records ?> + <?lsmbIF row.errorcode != 0 ?> <tr style="background-color:red;"> - [% ELSIF row.id = corrected %] + <?lsmb ELSIF row.id = corrected ?> <tr style="background-color:yellow;"> - [%ELSE%] + <?lsmbELSE?> <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%] + <?lsmbEND?> + <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> + <?lsmb IF row.errorcode > 0 ?> + <td><?lsmb row.errorcode ?> <a href="/reconciliation.pl?action=corrections&entry=<?lsmbrow.entry_id?>">View Corrections</a> </td> + <?lsmbELSE?> <td>0</td> - [%END%] + <?lsmbEND?> + <td><a href="/reconciliation.pl?action=correct&report_id=<?lsmbrow.report_id?>&entry_id=<?lsmbrow.entry_id?>">Correct</a></td> </tr> - [% END %] -</table>
\ No newline at end of file + <?lsmb END ?> +</table> + +<?lsmb if not recon.error?> +<form name="approval" method="POST" action="/reconciliation.pl"> + <input type="submit" name="action" value="Approve" /> +</form> +<?lsmb end?>
\ No newline at end of file diff --git a/UI/Reconciliation/search.html b/UI/Reconciliation/search.html new file mode 100644 index 00000000..58528c64 --- /dev/null +++ b/UI/Reconciliation/search.html @@ -0,0 +1,18 @@ +<form name="reconciliation__search" method="POST" action=""> + + <div> + Date:<br/> + <input type="input" size="15" name="date_begin" alt="mon/day/year"/> to <input type="input" size="15" name="date_end" alt="mon/day/year"/><br/> + </div> + + <div> + Account:<br/> + <input type="input" size="15" name="account" alt="Chart account #"/> + </div> + + <div> + Status:<br/> + + </div> + +</form>
\ No newline at end of file diff --git a/UI/Reconciliation/upload.html b/UI/Reconciliation/upload.html new file mode 100644 index 00000000..fdf3ac4e --- /dev/null +++ b/UI/Reconciliation/upload.html @@ -0,0 +1,17 @@ +<div> + <div class="title"> + New Reconciliation Report + </div> + <?lsmb IF error?> + <div class="error">Error detected in file upload: <?lsmb error?><br/> + Please check your CSV file and try again. + </div> + <?lsmb END?> + <form name="csv_upload" method="POST" action="/reconciliation.pl"> + + <input type="hidden" name="action" value="new_report" /> + <label for="file_upload">CSV File:</label> + <input type="file" name="csv_file" id="file_upload" /> + <input type="submit" action="submit" value="Create New Report"> + </form> +</div>
\ No newline at end of file diff --git a/scripts/Reconciliation.pl b/scripts/Reconciliation.pl index d648ae35..f4769c8c 100644 --- a/scripts/Reconciliation.pl +++ b/scripts/Reconciliation.pl @@ -40,22 +40,59 @@ 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}, + template => "reconciliation/report.html", language => $user->{language}, format=>'html' ); my $report = $recon->get_report(); my $total = $recon->get_total(); - $template->render({report=>$report, total=>$total}); + $template->render({report=>$report, total=>$total, recon=>$recon}); } +=pod + +=over + +=item search($self, $request, $user) + +Renders out a list of meta-reports based on the search criteria passed to the +search function. +Meta-reports are report_id, date_range, and likely errors. +Search criteria accepted are +date_begin +date_end +account +status + +=back + +=cut + 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, - template => 'employee_search.html', language => $user->{language}, - format => 'html'); - $template->render($employee); + + if ($request->type() eq "POST") { + # WE HAS DATUMS + # INTENTIONAL BAD PLURALIZATION OF LATIN + + my $template = LedgerSMB::Template->new( + user => $user, + template=>'reconciliation/search.html', + language=>$user->{language}, + format=>'html' + ); + return $template->render(); + + } else { + my $search = LedgerSMB::Reconciliation->new(base => $request, copy => 'all'); + my $results = $search->search(); + my $total = $search->total(); + + + my $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation/report.html', language => $user->{language}, + format => 'html'); + return $template->render({report => $results, total => $total}); + } } =pod @@ -77,36 +114,71 @@ This is to prevent arbitrary editing of the database by unscrupulous users. sub correct { my ($class, $request) = @_; - my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); - - $recon->correct_entry(); - - if ($recon->{corrected_id}) { + if ($request->type() eq "POST") { - 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() - } ); - } + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); + + $recon->correct_entry(); + + # Are we getting data? + 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() + } ); + } + } 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() - } ); + # We are not getting data sent + # ergo, we render out stuff. + + if ($request->{report_id} && $request->{entry_id}) { + + # draw the editor interface. + + my $template = LedgerSMB::Template->new( + user=>$user, + template=>"reconciliation/correct.html", + language=> $user->{language}, + format=>'html' + ); + my $recon = LedgerSMB::DBObject::Reconciliation->new(base=>$request, copy=>'all'); + + $template->render($recon->details($request->{report_id})); + } + elsif ($request->{report_id}) { + + my $template = LedgerSMB::Template->new( + user=>$user, + template=>"reconciliation/correct.html", + language=> $user->{language}, + format=>'html' + ); + $class->display_report(); + } } + } =pod @@ -131,23 +203,58 @@ sub new_report { # probably select a list of statements that are available to build # reconciliation reports with. + # This should do some fun stuff. + my $template; - my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); my $return; - if ($request->{selection}) { + + if ($request->type() eq "POST") { - $template = LedgerSMB::Template->new( user => $user, - template => 'reconciliation_report.html', language => $user->{language}, - format => 'html'); + # We can assume that we're doing something useful with new data. + # We can also assume that we've got a file. + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); + + # $self is expected to have both the file handling logic, as well as + # the logic to load the processing module. + + # Why isn't this testing for errors? + my ($report_id, $entries) = $recon->new_report($recon->import_file()); + if ($recon->is_error()) { - $template->render($recon->new_report()); - } + $template = LedgerSMB::Template->new( + user=>$user, + template=> 'reconciliation/upload.html', + language=>$user->{language}, + format=>'html' + ); + return $template->render({error=>$recon->error()}); + } + + $template = LedgerSMB::Template->new( + user=> $user, + template => 'reconciliation/new_report.html', + language => $user->{language}, + format=>'html' + ); + return $template->render( + { + entries=>$entries, + report_id=>$report_id + } + ); + } else { - # Generate the list of available bank statements/bank statements that - # we have access to. + # we can assume we're to generate the "Make a happy new report!" page. + $template = LedgerSMB::Template->new( + user => $user, + template => 'reconciliation/upload.html', + language => $user->{language}, + format => 'html' + ); + return $template->render(); } - return $return; + return undef; } @@ -177,30 +284,43 @@ sub approve { # been cleared. This will also provide for return-home links, auditing, # etc. - my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all'); - - my $template; - my $report; - if ($recon->approve()) { + if ($request->type() eq "POST") { - $template = LedgerSMB::Template->new( user => $user, - template => 'reconciliation_approve.html', language => $user->{language}, - format => 'html'); + # we need a report_id for this. + + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all'); + + my $template; + my $code = $recon->approve($request->{report_id}); + if ($code == 0) { + + $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation/approve.html', language => $user->{language}, + format => 'html'); + + return $template->render(); + } + else { + + # failure case + + $template = LedgerSMB::Template->new( + user => $user, + template => 'reconciliation/report.html', + language => $user->{language}, + format => 'html'); + return $template->render( + { + entries=>$recon->get_report($request->{report_id}), + total=>$recon->get_total($request->{report_id}), + error_code => $code + } + ); + } } else { - - $template = LedgerSMB::Template->new( user => $user, - 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 }; + return $class->display_report($request); } - $template->render($report); } =pod @@ -228,7 +348,7 @@ sub corrections { my $template; $template = LedgerSMB::Template->new( user => $user, - template => 'reconciliation_corrected.html', language => $user->{language}, + template => 'reconciliation/corrected.html', language => $user->{language}, format => 'html'); return $template->render( @@ -238,8 +358,7 @@ sub corrections { } ); } - -eval { do "scripts/custom/Reconciliation.pl"}; +# eval { do "scripts/custom/Reconciliation.pl" }; 1; =pod diff --git a/templates/Default-statement.tex b/templates/Default-statement.tex index 4343c5c6..320bc23b 100755 --- a/templates/Default-statement.tex +++ b/templates/Default-statement.tex @@ -73,6 +73,5 @@ Fax: <?lsmb customerfax ?> All amounts in \textbf{<?lsmb currency ?>} funds. Please make check payable to \textbf{<?lsmb company ?>} - \end{document} |