Added Reconciliation PM

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1250 4979c152-3d1c-0410-bac9-87ea11338e46

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;