1 files changed, 396 insertions, 0 deletions
diff --git a/sql-ledger/sql-ledger/SL/CP.pm b/sql-ledger/sql-ledger/SL/CP.pm
new file mode 100644
index 000000000..539ff6d9a
--- /dev/null
+++ b/sql-ledger/sql-ledger/SL/CP.pm
@@ -0,0 +1,396 @@
+#=====================================================================
+# SQL-Ledger Accounting
+# Copyright (C) 2003
+#
+#  Author: Dieter Simader
+#   Email: dsimader@sql-ledger.org
+#     Web: http://www.sql-ledger.org
+#
+#  Contributors:
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 2 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+#======================================================================
+#
+# Check and receipt printing payment module backend routines
+# Number to text conversion routines are in
+# locale/{countrycode}/Num2text
+#
+#======================================================================
+
+package CP;
+
+
+sub new {
+  my ($type, $countrycode) = @_;
+
+  $self = {};
+
+  if ($countrycode) {
+    if (-f "locale/$countrycode/Num2text") {
+      require "locale/$countrycode/Num2text";
+    } else {
+      use SL::Num2text;
+    }
+  } else {
+    use SL::Num2text;
+  }
+
+  bless $self, $type;
+
+}
+
+
+sub paymentaccounts {
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+  
+  my $query = qq|SELECT accno, description, link
+                 FROM chart
+		 WHERE link LIKE '%$form->{ARAP}%'
+		 ORDER BY accno|;
+  my $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+
+  $form->{PR}{$form->{ARAP}} = ();
+  $form->{PR}{"$form->{ARAP}_paid"} = ();
+  
+  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+    foreach my $item (split /:/, $ref->{link}) {
+      if ($item eq $form->{ARAP}) {
+	push @{ $form->{PR}{$form->{ARAP}} }, $ref;
+      }
+      if ($item eq "$form->{ARAP}_paid") {
+	push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
+      }
+    }
+  }
+  $sth->finish;
+  
+  # get currencies and closedto
+  $query = qq|SELECT curr, closedto, current_date
+              FROM defaults|;
+  ($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);
+
+  $dbh->disconnect;
+
+}
+
+
+sub get_openvc {
+  my ($self, $myconfig, $form) = @_;
+
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
+  my $query = qq|SELECT count(*)
+                 FROM $form->{vc} ct, $arap a
+		 WHERE a.$form->{vc}_id = ct.id
+                 AND a.amount != a.paid|;
+  my ($count) = $dbh->selectrow_array($query);
+
+  my $sth;
+  my $ref;
+
+  # build selection list
+  if ($count < $myconfig->{vclimit}) {
+    $query = qq|SELECT DISTINCT ct.id, ct.name
+                FROM $form->{vc} ct, $arap a
+		WHERE a.$form->{vc}_id = ct.id
+		AND a.amount != a.paid
+		ORDER BY name|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);
+
+    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+      push @{ $form->{"all_$form->{vc}"} }, $ref;
+    }
+
+    $sth->finish;
+
+  }
+
+  if ($form->{ARAP} eq 'AR') {
+    $query = qq|SELECT id, description
+                FROM department
+		WHERE role = 'P'
+		ORDER BY 2|;
+  } else {
+    $query = qq|SELECT id, description
+                FROM department
+		ORDER BY 2|;
+  }
+  $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_departments} }, $ref;
+  }
+  $sth->finish;
+
+  # get language codes
+  $query = qq|SELECT *
+              FROM language
+              ORDER BY 2|;
+  $sth = $dbh->prepare($query);
+  $sth->execute || $self->dberror($query);
+
+  $form->{all_languages} = ();
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    push @{ $form->{all_languages} }, $ref;
+  }
+  $sth->finish;
+
+  # get currency for first name
+  if ($form->{"all_$form->{vc}"}) {
+    $query = qq|SELECT curr FROM $form->{vc}
+		WHERE id = $form->{"all_$form->{vc}"}->[0]->{id}|;
+    ($form->{currency}) = $dbh->selectrow_array($query);
+  }
+
+  $dbh->disconnect;
+
+}
+
+
+sub get_openinvoices {
+  my ($self, $myconfig, $form) = @_;
+  
+  my $null;
+  my $department_id;
+ 
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+ 
+  my $where = qq|WHERE $form->{vc}_id = $form->{"$form->{vc}_id"}
+                 AND curr = '$form->{currency}'
+	         AND amount != paid|;
+  
+  my ($buysell);
+  if ($form->{vc} eq 'customer') {
+    $buysell = "buy";
+  } else {
+    $buysell = "sell";
+  }
+  
+  ($null, $department_id) = split /--/, $form->{department};
+  if ($department_id) {
+    $where .= qq|
+                 AND department_id = $department_id|;
+  }
+
+  my $query = qq|SELECT id, invnumber, transdate, amount, paid, curr
+	         FROM $form->{arap}
+		 $where
+		 ORDER BY transdate, invnumber|;
+  my $sth = $dbh->prepare($query);
+  $sth->execute || $form->dberror($query);
+
+  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    # if this is a foreign currency transaction get exchangerate
+    $ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});
+    push @{ $form->{PR} }, $ref;
+  }
+  
+  $sth->finish;
+  $dbh->disconnect;
+
+}
+
+
+
+sub process_payment {
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database, turn AutoCommit off
+  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  my $sth;
+  
+  my ($paymentaccno) = split /--/, $form->{account};
+  
+  # if currency ne defaultcurrency update exchangerate
+  if ($form->{currency} ne $form->{defaultcurrency}) {
+    $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
+
+    if ($form->{vc} eq 'customer') {
+      $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
+    } else {
+      $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
+    }
+  } else {
+    $form->{exchangerate} = 1;
+  }
+
+  my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
+                 FROM defaults|;
+  my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
+
+  my ($buysell);
+  
+  if ($form->{vc} eq 'customer') {
+    $buysell = "buy";
+  } else {
+    $buysell = "sell";
+  }
+
+  my $ml;
+  my $where;
+  
+  if ($form->{ARAP} eq 'AR') {
+    $ml = 1;
+    $where = qq|
+		(c.link = 'AR'
+		OR c.link LIKE 'AR:%')
+		|;
+  } else {
+    $ml = -1;
+    $where = qq|
+                (c.link = 'AP'
+                OR c.link LIKE '%:AP'
+		OR c.link LIKE '%:AP:%')
+		|;
+  }
+  
+  my $paymentamount = $form->parse_amount($myconfig, $form->{amount});
+  
+  my $null;
+  ($null, $form->{department_id}) = split /--/, $form->{department};
+  $form->{department_id} *= 1;
+
+
+  # query to retrieve paid amount
+  $query = qq|SELECT paid FROM $form->{arap}
+              WHERE id = ?
+ 	      FOR UPDATE|;
+  my $pth = $dbh->prepare($query) || $form->dberror($query);
+
+  my %audittrail;
+ 
+  # go through line by line
+  for my $i (1 .. $form->{rowcount}) {
+
+    $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
+    $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});
+    
+    if ($form->{"checked_$i"} && $form->{"paid_$i"}) {
+
+      $paymentamount -= $form->{"paid_$i"};
+      
+      # get exchangerate for original 
+      $query = qq|SELECT $buysell
+                  FROM exchangerate e
+                  JOIN $form->{arap} a ON (a.transdate = e.transdate)
+		  WHERE e.curr = '$form->{currency}'
+		  AND a.id = $form->{"id_$i"}|;
+      my ($exchangerate) = $dbh->selectrow_array($query);
+
+      $exchangerate = 1 unless $exchangerate;
+
+      $query = qq|SELECT c.id
+                  FROM chart c
+		  JOIN acc_trans a ON (a.chart_id = c.id)
+	  	  WHERE $where
+		  AND a.trans_id = $form->{"id_$i"}|;
+      my ($id) = $dbh->selectrow_array($query);
+     
+      $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
+      
+      # add AR/AP
+      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
+                  amount)
+                  VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
+		  $amount * $ml)|;
+      $dbh->do($query) || $form->dberror($query);
+      
+      # add payment
+      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
+                  amount, source, memo)
+                  VALUES ($form->{"id_$i"},
+		         (SELECT id FROM chart
+		          WHERE accno = '$paymentaccno'),
+		  '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
+		  .$dbh->quote($form->{source}).qq|, |
+		  .$dbh->quote($form->{memo}).qq|)|;
+      $dbh->do($query) || $form->dberror($query);
+
+      # add exchangerate difference if currency ne defaultcurrency
+      $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);
+
+      if ($amount != 0) {
+        # exchangerate difference
+	$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
+		    amount, cleared, fx_transaction)
+		    VALUES ($form->{"id_$i"},
+		           (SELECT id FROM chart
+			    WHERE accno = '$paymentaccno'),
+		  '$form->{datepaid}', $amount * $ml * -1, '0', '1')|;
+	$dbh->do($query) || $form->dberror($query);
+
+        # gain/loss
+	$amount = $form->round_amount($form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}) * $ml * -1, 2);
+	if ($amount != 0) {
+	  my $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;
+	  $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
+		      amount, cleared, fx_transaction)
+		      VALUES ($form->{"id_$i"}, $accno_id,
+		      '$form->{datepaid}', $amount, '0', '1')|;
+	  $dbh->do($query) || $form->dberror($query);
+	}
+      }
+
+      $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
+
+      $pth->execute($form->{"id_$i"}) || $form->dberror;
+      ($amount) = $pth->fetchrow_array;
+      $pth->finish;
+
+      $amount += $form->{"paid_$i"};
+		  
+      # update AR/AP transaction
+      $query = qq|UPDATE $form->{arap} set
+		  paid = $amount,
+		  datepaid = '$form->{datepaid}'
+		  WHERE id = $form->{"id_$i"}|;
+      $dbh->do($query) || $form->dberror($query);
+      
+      %audittrail = ( tablename  => $form->{arap},
+                      reference  => $form->{source},
+		      formname   => $form->{formname},
+		      action     => 'posted',
+		      id         => $form->{"id_$i"} );
+ 
+      $form->audittrail($dbh, "", \%audittrail);
+      
+    }
+  }
+
+
+  # record a AR/AP with a payment
+  if ($form->round_amount($paymentamount, 2) != 0) {
+    $form->{invnumber} = "";
+    OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
+  }
+
+  my $rc = $dbh->commit;
+  $dbh->disconnect;
+
+  $rc;
+
+}
+
+
+1;
+