diff options
| -rw-r--r-- | LedgerSMB/Reconciliation.pm | 50 | ||||
| -rw-r--r-- | scripts/Reconciliation.pl | 158 | ||||
| -rw-r--r-- | scripts/employees.pl | 43 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 44 |
4 files changed, 280 insertions, 15 deletions
diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm index e9963ae9..f12251e5 100644 --- a/LedgerSMB/Reconciliation.pm +++ b/LedgerSMB/Reconciliation.pm @@ -23,7 +23,7 @@ Of course the base object can be any object that inherits LedgerSMB, so you can use any subclass of that. The per-session dbh is passed between the objects this way as is any information that is needed. -=item reconcile($self, $total, $entries) +=item reconcile($self, $total, $month, $entries) Accepts the total balance, as well as a list of all entries from the bank statement as an array reference, and generates the pending report from @@ -31,6 +31,9 @@ this list. The first entry is always the total balance of the general ledger as compared to the balance held by the bank. +Month is taken to be the date that the statement as represented by Entries +is applicable to. + Returns the new report ID. || An arrayref of entries. =item approve($self,$reportid) @@ -54,6 +57,13 @@ Returns the error code assigned to this entry. 0 for success 1 for found in general ledger, but does not match $new_balance 2 $source_control_number cannot be found in the general ledger + +=item get_report($self, $report_id) + +Collects all the rows from the database in the given report. Returns an +arrayref of entries in the table for the given report_id. + +Returns undef in the event of no records found. =back @@ -67,23 +77,31 @@ your software. package LedgerSMB::DBObject::Reconciliation; -use base qw(LedgerSMB::DBObject); +use base qw(LedgerSMB); +use LedgerSMB::DBObject; # don't need new -sub reconcile { +#sub reconcile { +sub new_report { my $self = shift @_; my $total = shift @_; + my $month = shift @_; my $entries = shift @_; # expects an arrayref. # Total is in here somewhere, too - my $report_id = $self->new_report(); # gives us a report ID to insert with. + my $report_id = $self->new_report_id()[0]; # gives us a report ID to insert with. # 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 => 0, balance=> $total, old_balance=> $self->current_balance, code=> $self->compare_total($total) }; + unshift @{$entries}, { + scn => -1, + balance=> $total, + old_balance=> $self->current_balance, + date=>$month + }; for my $entry (@{$entries}) { # Codes: @@ -94,8 +112,9 @@ sub reconcile { $entry{code} = $self->add_entry( $entry ); } - # Based on chatting with Chris T, we are going to use an arrayref of hashrefs to handle - # the varying return states. + + $self->pending_transactions($report_id, $date); + return $entries; # returns the report ID. } @@ -105,7 +124,7 @@ sub approve { # the user should be embedded into the $self object. my $report_id = shift @_; - my $code = $self->report_approve($report_id,$self->{user}->{id}); # user + my $code = $self->report_approve($report_id); # user if ($code == 0) { # no problem. return $code; @@ -120,13 +139,20 @@ sub approve { sub correct_entry { my $self = shift @_; - my $report_id = shift @_; - my $scn = shift @_; - my $new_amount = shift @_; + my $report_id = $self->{report_id}; # shift @_; + my $scn = $self->{id}; #shift @_; + 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); return $code[0]->{'correct'}; } -1; +sub get_report { + + my $self = shift @_; + + return $self->report($self->{report_id}); +} + +1;
\ No newline at end of file diff --git a/scripts/Reconciliation.pl b/scripts/Reconciliation.pl new file mode 100644 index 00000000..189eecb4 --- /dev/null +++ b/scripts/Reconciliation.pl @@ -0,0 +1,158 @@ +=pod + +=head1 NAME + +=cut + +package LedgerSMB::Scripts::Reconciliation; + +use LedgerSMB::Template; +use LedgerSMB::DBObject::Reconciliation; + +=pod + +=over + +=item display_report($self, $request, $user) + +Renders out the selected report given by the incoming variable report_id. +Returns HTML, or raises an error from being unable to find the selected +report_id. + +=back + +=cut + +sub display_report { + + my $recon = LedgerSMB::Employee->new(base => $request, copy => 'all'); + my $template = LedgerSMB::Template->new( user=>$user, + 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}); +} + +sub search { + 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); +} + +=pod + +=over + +=item correct ($self, $request, $user) + +Requires report_id, entry_id. + +Correct is a strange one. Based on the type of transaction listed in the +report, it will run one of several correction functions in the database. +This is to prevent arbitrary editing of the database by unscrupulous users. + +=back + +=cut + +sub correct { + + 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()); +} + +=pod + +=over + +=item new_report ($self, $request, $user) + +Creates a new report, from a selectable set of bank statements that have been +received (or can be received from, depending on implementation) + +Allows for an optional selection key, which will return the new report after +it has been created. + +=back + +=cut + +sub new_report { + # 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. + + my $template; + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all'); + my $return; + if ($request->{selection}) { + + $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation_report.html', language => $user->{language}, + format => 'html'); + + $template->render($recon->new_report()); + } + else { + + # Generate the list of available bank statements/bank statements that + # we have access to. + } + return $return; + +} + +=pod + +=over + +=item ($self, $request, $user) + +Requires report_id + +Approves the given report based on id. Generally, the roles should be +configured so as to disallow the same user from approving, as created the report. + +Returns a success page on success, returns a new report on failure, showing +the uncorrected entries. + +=back + +=cut + +sub approve { + + my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all'); + + my $template; + my $report; + if ($recon->approve()) { + + $template = LedgerSMB::Template->new( user => $user, + template => 'reconciliation_approve.html', language => $user->{language}, + format => 'html'); + } + 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 }; + } + $template->render($report); +} +1;
\ No newline at end of file diff --git a/scripts/employees.pl b/scripts/employees.pl new file mode 100644 index 00000000..fa9ec05f --- /dev/null +++ b/scripts/employees.pl @@ -0,0 +1,43 @@ +# The handler, prior to handing the execution off to this script will create a +# $request object from the LedgerSMB namespace. This object contains the http +# request parameters, db connections, and the like. A $user object is also +# created +# +# Entrence points are functions which do not begin with an underscore (_) +use LedgerSMB::Template; + +sub save { + my $employee = LedgerSMB::Employee->new(base => $request, copy => 'all'); + $employee->save(); + &_display; +} + +sub search { + 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); +} + +sub add { + my $employee = LedgerSMB::Employee->new(base => $request, copy => 'all'); + &_display; +} + +sub edit { + my $employee = LedgerSMB::Employee->new(base => $request, copy => 'all'); + $employee->get(); + &_display; +} + +sub _display { + my $template = LedgerSMB::Template->new( user => $user, + template => 'employee.html', language => $user->{language}, + format => 'html'); + $template->render($employee); + +} + +1; diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql index 161d9369..2700a2fb 100644 --- a/sql/modules/Reconciliaton.sql +++ b/sql/modules/Reconciliaton.sql @@ -4,7 +4,7 @@ CREATE TABLE pending_reports ( scn int, their_balance INT, our_balance INT, - code INT, + errorcode INT, user TEXT, corrections INT NOT NULL DEFAULT 0 clear_time TIMESTAMP NOT NULL, @@ -223,7 +223,7 @@ CREATE OR REPLACE FUNCTION reconciliation_report_approve (in_report_id INT) retu $$ language 'plpgsql'; -CREATE OR REPLACE FUNCTION reconciliation_new_report () returns INT as $$ +CREATE OR REPLACE FUNCTION reconciliation_new_report_id () returns INT as $$ SELECT nextval('pending_report_report_id_seq'); @@ -343,4 +343,42 @@ create or replace function reconciliation_pending_transactions (in_report_id INT END LOOP; END; -$$ language plpgsql;
\ No newline at end of file +$$ language plpgsql; + +CREATE OR REPLACE FUNCTION reconciliation_report (in_report_id INT) RETURNS setof pending_reports as $$ + + DECLARE + row pending_reports; + BEGIN + FOR row IN select * from pending_reports where report_id = in_report_id LOOP + + RETURN NEXT row; + + END LOOP; + END; + +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns setof pending_reports AS $$ + + DECLARE + row pending_reports; + BEGIN + + SELECT INTO row FROM pending_reports + WHERE ledger_id IS NULL + and report_id = in_report_id + AND scn = -1; + + IF NOT FOUND THEN -- I think this is a fairly major error condition + + RAISE EXCEPTION "No Bank Total found." + + ELSE + + return row; + + END IF; + END; + +$$ language 'plpgsql'; |