--- /dev/null
+#=====================================================================
+# 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.
+#======================================================================
+#
+# Account reconciliation routines
+#
+#======================================================================
+
+package RC;
+
+
+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 '%_paid%'
+ AND (category = 'A' OR category = 'L')
+ 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;
+
+ $form->all_years($dbh, $myconfig);
+
+ $dbh->disconnect;
+
+}
+
+
+sub payment_transactions {
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database, turn AutoCommit off
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ my $query;
+ my $sth;
+
+ $query = qq|SELECT category FROM chart
+ WHERE accno = '$form->{accno}'|;
+ ($form->{category}) = $dbh->selectrow_array($query);
+
+ my $cleared;
+
+ ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
+
+ my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;
+
+ if (! $form->{fromdate}) {
+ $cleared = qq| AND ac.cleared = '1'|;
+ $transdate = "";
+ }
+
+ # get beginning balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ $transdate
+ $cleared
+ |;
+ ($form->{beginningbalance}) = $dbh->selectrow_array($query);
+
+ # fx balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ AND ac.fx_transaction = '1'
+ $transdate
+ $cleared
+ |;
+ ($form->{fx_balance}) = $dbh->selectrow_array($query);
+
+
+ $transdate = "";
+ if ($form->{todate}) {
+ $transdate = qq| AND ac.transdate <= date '$form->{todate}'|;
+ }
+
+ # get statement balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ $transdate
+ |;
+ ($form->{endingbalance}) = $dbh->selectrow_array($query);
+
+ # fx balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ AND ac.fx_transaction = '1'
+ $transdate
+ |;
+ ($form->{fx_endingbalance}) = $dbh->selectrow_array($query);
+
+
+ $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
+
+ if ($form->{report}) {
+ $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
+ if ($form->{cleared}) {
+ $cleared = qq| AND ac.cleared = '1'|;
+ }
+ if ($form->{outstanding}) {
+ $cleared = ($form->{cleared}) ? "" : qq| AND ac.cleared = '0'|;
+ }
+ if (! $form->{fromdate}) {
+ $form->{beginningbalance} = 0;
+ $form->{fx_balance} = 0;
+ }
+ }
+
+
+ if ($form->{summary}) {
+ $query = qq|SELECT ac.transdate, ac.source,
+ sum(ac.amount) AS amount, ac.cleared
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.amount >= 0
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source,
+ sum(ac.amount) AS amount, ac.cleared
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.amount < 0
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
+
+ $query .= " ORDER BY 1,2";
+
+ } else {
+
+ $query = qq|SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, g.id, g.description
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN gl g ON (g.id = ac.trans_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, a.id, n.name
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN customer n ON (n.id = a.customer_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, a.id, n.name
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN vendor n ON (n.id = a.vendor_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= " ORDER BY 1,2,3";
+ }
+
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ my $dr;
+ my $cr;
+ my $fxs;
+
+ if ($form->{summary}) {
+ $query = qq|SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN customer n ON (n.id = a.customer_id)
+ WHERE ac.fx_transaction = '1'
+ AND n.name = ?
+ AND ac.transdate = ?
+ AND ac.trans_id IN (SELECT id FROM ar a
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.source = ?)
+ AND ac.cleared = ?
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ $query .= qq|
+ UNION
+ SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN vendor n ON (n.id = a.vendor_id)
+ WHERE ac.fx_transaction = '1'
+ AND n.name = ?
+ AND ac.transdate = ?
+ AND ac.trans_id IN (SELECT id FROM ap a
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.source = ?)
+ AND ac.cleared = ?
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ } else {
+
+ $query = qq|SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ WHERE ac.trans_id = ?
+ AND ac.fx_transaction = '1'
+ $cleared
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ }
+
+ $fxs = $dbh->prepare($query);
+
+
+ if ($form->{summary}) {
+ $query = qq|SELECT c.name
+ FROM customer c
+ JOIN ar a ON (c.id = a.customer_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ UNION
+ SELECT v.name
+ FROM vendor v
+ JOIN ap a ON (v.id = a.vendor_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ UNION
+ SELECT g.description
+ FROM gl g
+ JOIN acc_trans ac ON (g.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ |;
+
+ $query .= " ORDER BY 1";
+ $dr = $dbh->prepare($query);
+
+
+ $query = qq|SELECT c.name
+ FROM customer c
+ JOIN ar a ON (c.id = a.customer_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ UNION
+ SELECT v.name
+ FROM vendor v
+ JOIN ap a ON (v.id = a.vendor_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ UNION
+ SELECT g.description
+ FROM gl g
+ JOIN acc_trans ac ON (g.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ |;
+
+ $query .= " ORDER BY 1";
+ $cr = $dbh->prepare($query);
+ }
+
+
+ my $name;
+ my $ref;
+ my $xfref;
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+ if ($form->{summary}) {
+
+ if ($ref->{amount} > 0) {
+ $dr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
+ $ref->{oldcleared} = $ref->{cleared};
+ $ref->{name} = ();
+ while (($name) = $dr->fetchrow_array) {
+ push @{ $ref->{name} }, $name;
+ }
+ $dr->finish;
+ } else {
+
+ $cr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
+ $ref->{oldcleared} = $ref->{cleared};
+ $ref->{name} = ();
+ while (($name) = $cr->fetchrow_array) {
+ push @{ $ref->{name} }, $name;
+ }
+ $cr->finish;
+
+ }
+
+ } else {
+ push @{ $ref->{name} }, $ref->{description};
+ }
+
+ push @{ $form->{PR} }, $ref;
+
+ # include fx transactions
+ $amount = 0;
+ $addfx = 0;
+ $ref->{oldcleared} = $ref->{cleared};
+ if ($form->{summary}) {
+ foreach $name (@{ $ref->{name} }) {
+ $fxs->execute($name, $ref->{transdate}, $ref->{source}, $ref->{cleared}, $name, $ref->{transdate}, $ref->{source}, $ref->{cleared});
+ while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
+ $addfx = 1;
+ $amount += $fxref->{amount};
+ }
+ $fxs->finish;
+ }
+ } else {
+ $fxs->execute($ref->{id});
+ while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
+ $addfx = 1;
+ $amount += $fxref->{amount};
+ }
+ $fxs->finish;
+ }
+
+ if ($addfx) {
+ $fxref = ();
+ map { $fxref->{$_} = $ref->{$_} } keys %$ref;
+ $fxref->{fx_transaction} = 1;
+ $fxref->{name} = ();
+ $fxref->{source} = "";
+ $fxref->{transdate} = "";
+ $fxref->{amount} = $amount;
+ push @{ $form->{PR} }, $fxref;
+ }
+
+ }
+ $sth->finish;
+
+ $dbh->disconnect;
+
+}
+
+
+sub reconcile {
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT id FROM chart
+ WHERE accno = '$form->{accno}'|;
+ my ($chart_id) = $dbh->selectrow_array($query);
+ $chart_id *= 1;
+
+ $query = qq|SELECT trans_id FROM acc_trans
+ WHERE source = ?
+ AND transdate = ?
+ AND cleared = '0'|;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ my $i;
+ my $trans_id;
+
+ $query = qq|UPDATE acc_trans SET cleared = '1'
+ WHERE cleared = '0'
+ AND trans_id = ?
+ AND transdate = ?
+ AND chart_id = $chart_id|;
+ my $tth = $dbh->prepare($query) || $form->dberror($query);
+
+ # clear flags
+ for $i (1 .. $form->{rowcount}) {
+ if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
+ if ($form->{summary}) {
+ $sth->execute($form->{"source_$i"}, $form->{"transdate_$i"}) || $form->dberror;
+
+ while (($trans_id) = $sth->fetchrow_array) {
+ $tth->execute($trans_id, $form->{"transdate_$i"}) || $form->dberror;
+ $tth->finish;
+ }
+ $sth->finish;
+
+ } else {
+
+ $tth->execute($form->{"id_$i"}, $form->{"transdate_$i"}) || $form->dberror;
+ $tth->finish;
+ }
+ }
+ }
+
+ $dbh->disconnect;
+
+}
+
+1;
+
projects / freeside.git / blobdiff