1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # Account reconciliation routines
27 #======================================================================
33 my ($self, $myconfig, $form) = @_;
36 my $dbh = $form->dbconnect($myconfig);
38 my $query = qq|SELECT accno, description
40 WHERE link LIKE '%_paid%'
43 my $sth = $dbh->prepare($query);
44 $sth->execute || $form->dberror($query);
46 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
47 push @{ $form->{PR} }, $ref;
55 sub payment_transactions {
56 my ($self, $myconfig, $form) = @_;
58 # connect to database, turn AutoCommit off
59 my $dbh = $form->dbconnect_noauto($myconfig);
64 if ($form->{fromdate}) {
65 $query = qq|SELECT sum(a.amount)
66 FROM acc_trans a, chart c
67 WHERE a.transdate < date '$form->{fromdate}'
70 AND c.accno = '$form->{accno}'
73 $query = qq|SELECT sum(a.amount)
74 FROM acc_trans a, chart c
77 AND c.accno = '$form->{accno}'
81 $sth = $dbh->prepare($query);
82 $sth->execute || $form->dberror($query);
84 ($form->{beginningbalance}) = $sth->fetchrow_array;
88 my %oid = ( 'Pg' => 'ac.oid',
89 'Oracle' => 'ac.rowid');
91 $query = qq|SELECT c.name, ac.source, ac.transdate, ac.cleared,
92 ac.fx_transaction, ac.amount, a.id,
93 $oid{$myconfig->{dbdriver}} AS oid
94 FROM customer c, acc_trans ac, ar a, chart ch
95 WHERE c.id = a.customer_id
97 AND ac.trans_id = a.id
98 AND ac.chart_id = ch.id
99 AND ch.accno = '$form->{accno}'
102 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
103 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
109 SELECT v.name, ac.source, ac.transdate, ac.cleared,
110 ac.fx_transaction, ac.amount, a.id,
111 $oid{$myconfig->{dbdriver}} AS oid
112 FROM vendor v, acc_trans ac, ap a, chart ch
113 WHERE v.id = a.vendor_id
115 AND ac.trans_id = a.id
116 AND ac.chart_id = ch.id
117 AND ch.accno = '$form->{accno}'
120 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
121 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
126 SELECT g.description, ac.source, ac.transdate, ac.cleared,
127 ac.fx_transaction, ac.amount, g.id,
128 $oid{$myconfig->{dbdriver}} AS oid
129 FROM gl g, acc_trans ac, chart ch
130 WHERE g.id = ac.trans_id
132 AND ac.trans_id = g.id
133 AND ac.chart_id = ch.id
134 AND ch.accno = '$form->{accno}'
137 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
138 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
140 $query .= " ORDER BY 3,7,8";
142 $sth = $dbh->prepare($query);
143 $sth->execute || $form->dberror($query);
145 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
146 push @{ $form->{PR} }, $pr;
156 my ($self, $myconfig, $form) = @_;
158 # connect to database
159 my $dbh = $form->dbconnect($myconfig);
162 my %oid = ( 'Pg' => 'oid',
163 'Oracle' => 'rowid');
166 for $i (1 .. $form->{rowcount}) {
167 if ($form->{"cleared_$i"}) {
168 $query = qq|UPDATE acc_trans SET cleared = '1'
169 WHERE $oid{$myconfig->{dbdriver}} = $form->{"oid_$i"}|;
170 $dbh->do($query) || $form->dberror($query);
172 # clear fx_transaction
173 if ($form->{"fxoid_$i"}) {
174 $query = qq|UPDATE acc_trans SET cleared = '1'
175 WHERE $oid{$myconfig->{dbdriver}} = $form->{"fxoid_$i"}|;
176 $dbh->do($query) || $form->dberror($query);