diff options
| -rw-r--r-- | LedgerSMB/AA.pm | 28 | ||||
| -rw-r--r-- | LedgerSMB/Form.pm | 54 | ||||
| -rw-r--r-- | LedgerSMB/Reconciliation.pm | 132 |
3 files changed, 179 insertions, 35 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm index 22f32474..1165b232 100644 --- a/LedgerSMB/AA.pm +++ b/LedgerSMB/AA.pm @@ -944,19 +944,18 @@ sub get_name { # get customer/vendor my $query = qq| - SELECT c.name AS $form->{vc}, c.discount, c.creditlimit, - c.terms, c.email, c.cc, c.bcc, c.taxincluded, - c.address1, c.address2, c.city, c.state, - c.zipcode, c.country, c.curr AS currency, + SELECT entity.name AS $form->{vc}, c.discount, + c.creditlimit, + c.terms, c.taxincluded, + c.curr AS currency, c.language_code, $duedate AS duedate, - c.notes AS intnotes, b.discount AS tradediscount, - b.description AS business, - e.name AS employee, e.id AS employee_id + b.description AS business FROM $form->{vc} c + JOIN entity ON (entity.id = c.entity_id) LEFT JOIN business b ON (b.id = c.business_id) - LEFT JOIN employees e ON (e.id = c.employee_id) WHERE c.id = ?|; + # TODO: Add location join @queryargs = ( $form->{"$form->{vc}_id"} ); my $sth = $dbh->prepare($query); @@ -974,6 +973,8 @@ sub get_name { for ( keys %$ref ) { $form->{$_} = $ref->{$_} } $sth->finish; + # TODO: Retrieve contact records + my $buysell = ( $form->{vc} eq 'customer' ) ? "buy" : "sell"; # if no currency use defaultcurrency @@ -1005,7 +1006,7 @@ sub get_name { $query = qq| SELECT SUM(amount - paid) FROM $arap - WHERE $form->{vc}_id = ?|; + WHERE id = ?|; $sth = $dbh->prepare($query); $sth->execute( $form->{"$form->{vc}_id"} ) @@ -1023,7 +1024,8 @@ sub get_name { WHERE e.curr = o.curr AND e.transdate = o.transdate) FROM oe o - WHERE o.$form->{vc}_id = ? + WHERE o.entity_id = + (select entity_id from $form->{vc} WHERE id = ?) AND o.quotation = '0' AND o.closed = '0'|; $sth = $dbh->prepare($query); @@ -1124,15 +1126,15 @@ sub get_name { $query = qq| SELECT c.accno, c.description, c.link, c.category, - ac.project_id + ac.project_id, a.department_id FROM chart c JOIN acc_trans ac ON (ac.chart_id = c.id) JOIN $arap a ON (a.id = ac.trans_id) - WHERE a.$form->{vc}_id = ? + WHERE a.entity_id = ? AND a.id = (SELECT max(id) FROM $arap - WHERE $form->{vc}_id = + WHERE entity_id = ?) |; diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm index dba2194d..206669c6 100644 --- a/LedgerSMB/Form.pm +++ b/LedgerSMB/Form.pm @@ -621,7 +621,7 @@ sub get_my_emp_num { # we got a connection, check the version my $query = qq| - SELECT employeenumber FROM employees + SELECT employeenumber FROM employee WHERE login = ?|; my $sth = $dbh->prepare($query); $sth->execute( $form->{login} ) || $form->dberror($query); @@ -1815,9 +1815,11 @@ sub get_employee { my $login = $self->{login}; $login =~ s/@.*//; - my $query = qq|SELECT name, id - FROM employees - WHERE login = ?|; + my $query = qq| + SELECT name, id + FROM entity WHERE id IN (select entity_id + FROM employee + WHERE login = ?)|; $sth = $self->{dbh}->prepare($query); $sth->execute($login); @@ -1910,16 +1912,18 @@ sub all_vc { if ( $count < $myconfig->{vclimit} ) { $self->{"${vc}_id"} *= 1; + $where = "AND $where" if $where; $query = qq|SELECT id, name - FROM $vc - WHERE 1=1 - $where + FROM entity + WHERE id IN (select entity_id + FROM $vc) + $where UNION SELECT id,name - FROM $vc + FROM entity WHERE id = ? ORDER BY name|; @@ -2018,24 +2022,27 @@ sub all_employees { my @whereargs = (); # setup employees/sales contacts - my $query = qq|SELECT id, name - FROM employees - WHERE 1 = 1|; + my $query = qq| + SELECT id, name + FROM entity + WHERE id IN (SELECT entity_id FROM employee + WHERE|; if ($transdate) { - $query .= qq| AND (startdate IS NULL OR startdate <= ?) - AND (enddate IS NULL OR enddate >= ?)|; + $query .= qq| (startdate IS NULL OR startdate <= ?) + AND (enddate IS NULL OR enddate >= ?) AND|; @whereargs = ( $transdate, $transdate ); } else { - $query .= qq| AND enddate IS NULL|; + $query .= qq| enddate IS NULL AND|; } if ($sales) { - $query .= qq| AND sales = '1'|; + $query .= qq| sales = '1' AND|; } - $query .= qq| ORDER BY name|; + $query =~ s/(WHERE|AND)$//; + $query .= qq|) ORDER BY name|; my $sth = $dbh->prepare($query); $sth->execute(@whereargs) || $self->dberror($query); @@ -2245,7 +2252,7 @@ sub create_links { c.language_code, a.ponumber FROM $arap a JOIN $vc c ON (a.${vc}_id = c.id) - LEFT JOIN employees e ON (e.id = a.employee_id) + LEFT JOIN employee e ON (e.id = a.employee_id) LEFT JOIN department d ON (d.id = a.department_id) WHERE a.id = ?|; @@ -2375,7 +2382,6 @@ sub lastname_used { my $dbh = $self->{dbh}; $vc ||= $self->{vc}; # add default to correct for improper passing my $arap = ( $vc eq 'customer' ) ? "ar" : "ap"; - my $where = "1 = 1"; my $sth; if ( $self->{type} =~ /_order/ ) { @@ -2387,14 +2393,18 @@ sub lastname_used { $arap = 'oe'; $where = "quotation = '1'"; } - + $where = "AND $where " if $where; + $inv_notes = "ct.invoice_notes," if $vc eq 'customer'; my $query = qq| - SELECT ct.name AS $vc, ct.curr AS currency, ct.id AS ${vc}_id, + SELECT entity.name, ct.curr AS currency, ct.id AS ${vc}_id, current_date + ct.terms AS duedate, - ct.notes, + $inv_notes ct.curr AS currency FROM $vc ct - WHERE ct.id = (select ${vc}_id from $arap where $where AND ${vc}_id IS NOT NULL order by id DESC limit 1)|; + JOIN entity ON (ct.entity_id = entity.id) + WHERE entity.id = (select entity_id from $arap + where entity_id IS NOT NULL $where + order by id DESC limit 1)|; $sth = $dbh->prepare($query); $sth->execute() || $self->dberror($query); diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm new file mode 100644 index 00000000..e9963ae9 --- /dev/null +++ b/LedgerSMB/Reconciliation.pm @@ -0,0 +1,132 @@ + +=pod + +=head1 NAME + +LedgerSMB::DBObject::Reconciliation - LedgerSMB class defining the core +database interaction logic for Reconciliation. + +=head1 SYOPSIS + +This module creates object instances based on LedgerSMB's in-database ORM. + +=head1 METHODS + +=over + +=item new ($class, base => $LedgerSMB::hash) + +This is the base constructor for all child classes. It must be used with base +argument because this is necessary for database connectivity and the like. + +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) + +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 +this list. +The first entry is always the total balance of the general ledger as +compared to the balance held by the bank. + +Returns the new report ID. || An arrayref of entries. + +=item approve($self,$reportid) + +Approves the pending report $reportid. +Checks for error codes in the pending report, and approves the report if none +are found. + +Limitations: The creating user may not approve the report. + +Returns 1 on success. + +=item correct_entry($self, $report_id, $source_control_number, $new_balance) + +If the given entry $source_control_number in the report $report_id has an error +code, the entry will be updated with $new_balance, and the error code +recomputed. + +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 + +=back + +=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 + +package LedgerSMB::DBObject::Reconciliation; + +use base qw(LedgerSMB::DBObject); + +# don't need new + +sub reconcile { + + my $self = shift @_; + my $total = 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. + + # 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) }; + for my $entry (@{$entries}) { + + # Codes: + # 0 is success + # 1 is found, but mismatch + # 2 is not found + $entry{report_id} = $report_id; + $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. + 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,$self->{user}->{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 correct_entry { + + my $self = shift @_; + my $report_id = shift @_; + my $scn = shift @_; + my $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; |