summaryrefslogtreecommitdiff
path: root/sql-ledger/SL/CP.pm
diff options
context:
space:
mode:
Diffstat (limited to 'sql-ledger/SL/CP.pm')
-rw-r--r--sql-ledger/SL/CP.pm308
1 files changed, 308 insertions, 0 deletions
diff --git a/sql-ledger/SL/CP.pm b/sql-ledger/SL/CP.pm
new file mode 100644
index 0000000..f84bd15
--- /dev/null
+++ b/sql-ledger/SL/CP.pm
@@ -0,0 +1,308 @@
+#=====================================================================
+# SQL-Ledger Accounting
+# Copyright (C) 2002
+#
+# 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
+ FROM chart
+ WHERE link LIKE '%$form->{arap}_paid%'
+ ORDER BY accno|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{PR} }, $ref;
+ }
+ $sth->finish;
+
+ # get currencies and closedto
+ $query = qq|SELECT curr, closedto
+ FROM defaults|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ ($form->{currencies}, $form->{closedto}) = $sth->fetchrow_array;
+ $sth->finish;
+
+ $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 $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+ my ($count) = $sth->fetchrow_array;
+ $sth->finish;
+
+ # 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 (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{"all_$form->{vc}"} }, $ref;
+ }
+
+ $sth->finish;
+
+ }
+
+ $dbh->disconnect;
+
+}
+
+
+sub get_openinvoices {
+ my ($self, $myconfig, $form) = @_;
+
+ return unless $form->{"$form->{vc}_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 NOT amount = paid|;
+
+ if ($form->{transdatefrom}) {
+ $where .= " AND transdate >= '$form->{transdatefrom}'";
+ }
+ if ($form->{transdateto}) {
+ $where .= " AND transdate <= '$form->{transdateto}'";
+ }
+
+ my ($arap, $buysell);
+ if ($form->{vc} eq 'customer') {
+ $arap = "ar";
+ $buysell = "buy";
+ } else {
+ $arap = "ap";
+ $buysell = "sell";
+ }
+
+ my $query = qq|SELECT id, invnumber, transdate, amount, paid, curr
+ FROM $arap
+ $where
+ ORDER BY id|;
+ 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 ($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 $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ my ($fxgain_accno_id, $fxloss_accno_id) = $sth->fetchrow_array;
+ $sth->finish;
+
+ my ($ARAP, $arap, $buysell);
+
+ if ($form->{vc} eq 'customer') {
+ $ARAP = "AR";
+ $arap = "ar";
+ $buysell = "buy";
+ } else {
+ $ARAP = "AP";
+ $arap = "ap";
+ $buysell = "sell";
+ }
+
+ # go through line by line
+ for my $i (1 .. $form->{rowcount}) {
+
+ if ($form->{"paid_$i"}) {
+
+ $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
+
+ # get exchangerate for original
+ $query = qq|SELECT $buysell FROM exchangerate e, $arap a
+ WHERE e.curr = '$form->{currency}'
+ AND a.transdate = e.transdate
+ AND a.id = $form->{"id_$i"}|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ my ($exchangerate) = $sth->fetchrow_array;
+ $sth->finish;
+
+ $exchangerate = 1 unless $exchangerate;
+
+ $query = qq|SELECT c.id FROM chart c, acc_trans a
+ WHERE a.chart_id = c.id
+ AND c.link = '$ARAP'
+ AND a.trans_id = $form->{"id_$i"}|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ my ($id) = $sth->fetchrow_array;
+ $sth->finish;
+
+ my $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate * -1, 2);
+ $ml = ($ARAP eq 'AR') ? -1 : 1;
+ # 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)
+ VALUES ($form->{"id_$i"},
+ (SELECT id FROM chart
+ WHERE accno = '$paymentaccno'),
+ '$form->{datepaid}', $form->{"paid_$i"} * $ml,
+ '$form->{source}')|;
+ $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, '0', '1')|;
+ $dbh->do($query) || $form->dberror($query);
+
+ # gain/loss
+
+ $amount = $form->round_amount($form->{"paid_$i"} * ($exchangerate - $form->{exchangerate}) * $ml, 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);
+
+ # update AR/AP transaction
+ $query = qq|UPDATE $arap set
+ paid = paid + $form->{"paid_$i"},
+ datepaid = '$form->{datepaid}'
+ WHERE id = $form->{"id_$i"}|;
+ $dbh->do($query) || $form->dberror($query);
+ }
+ }
+
+ my $rc = $dbh->commit;
+ $dbh->disconnect;
+
+ $rc;
+
+}
+
+
+1;
+
generated by cgit v1.1 at 2024-06-09 13:24:11 +0000