+++ /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