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%'
41 AND (category = 'A' OR category = 'L')
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;
51 $form->all_years($dbh, $myconfig);
58 sub payment_transactions {
59 my ($self, $myconfig, $form) = @_;
61 # connect to database, turn AutoCommit off
62 my $dbh = $form->dbconnect_noauto($myconfig);
67 $query = qq|SELECT category FROM chart
68 WHERE accno = '$form->{accno}'|;
69 ($form->{category}) = $dbh->selectrow_array($query);
73 ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
75 my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;
77 if (! $form->{fromdate}) {
78 $cleared = qq| AND ac.cleared = '1'|;
82 # get beginning balance
83 $query = qq|SELECT sum(ac.amount)
85 JOIN chart c ON (c.id = ac.chart_id)
86 WHERE c.accno = '$form->{accno}'
90 ($form->{beginningbalance}) = $dbh->selectrow_array($query);
93 $query = qq|SELECT sum(ac.amount)
95 JOIN chart c ON (c.id = ac.chart_id)
96 WHERE c.accno = '$form->{accno}'
97 AND ac.fx_transaction = '1'
101 ($form->{fx_balance}) = $dbh->selectrow_array($query);
105 if ($form->{todate}) {
106 $transdate = qq| AND ac.transdate <= date '$form->{todate}'|;
109 # get statement balance
110 $query = qq|SELECT sum(ac.amount)
112 JOIN chart c ON (c.id = ac.chart_id)
113 WHERE c.accno = '$form->{accno}'
116 ($form->{endingbalance}) = $dbh->selectrow_array($query);
119 $query = qq|SELECT sum(ac.amount)
121 JOIN chart c ON (c.id = ac.chart_id)
122 WHERE c.accno = '$form->{accno}'
123 AND ac.fx_transaction = '1'
126 ($form->{fx_endingbalance}) = $dbh->selectrow_array($query);
129 $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
131 if ($form->{report}) {
132 $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
133 if ($form->{cleared}) {
134 $cleared = qq| AND ac.cleared = '1'|;
136 if ($form->{outstanding}) {
137 $cleared = ($form->{cleared}) ? "" : qq| AND ac.cleared = '0'|;
139 if (! $form->{fromdate}) {
140 $form->{beginningbalance} = 0;
141 $form->{fx_balance} = 0;
146 if ($form->{summary}) {
147 $query = qq|SELECT ac.transdate, ac.source,
148 sum(ac.amount) AS amount, ac.cleared
150 JOIN chart ch ON (ac.chart_id = ch.id)
151 WHERE ch.accno = '$form->{accno}'
153 AND ac.fx_transaction = '0'
155 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
156 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
157 $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
160 SELECT ac.transdate, ac.source,
161 sum(ac.amount) AS amount, ac.cleared
163 JOIN chart ch ON (ac.chart_id = ch.id)
164 WHERE ch.accno = '$form->{accno}'
166 AND ac.fx_transaction = '0'
168 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
169 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
170 $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
172 $query .= " ORDER BY 1,2";
176 $query = qq|SELECT ac.transdate, ac.source, ac.fx_transaction,
177 ac.amount, ac.cleared, g.id, g.description
179 JOIN chart ch ON (ac.chart_id = ch.id)
180 JOIN gl g ON (g.id = ac.trans_id)
181 WHERE ch.accno = '$form->{accno}'
182 AND ac.fx_transaction = '0'
184 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
185 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
189 SELECT ac.transdate, ac.source, ac.fx_transaction,
190 ac.amount, ac.cleared, a.id, n.name
192 JOIN chart ch ON (ac.chart_id = ch.id)
193 JOIN ar a ON (a.id = ac.trans_id)
194 JOIN customer n ON (n.id = a.customer_id)
195 WHERE ch.accno = '$form->{accno}'
196 AND ac.fx_transaction = '0'
198 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
199 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
203 SELECT ac.transdate, ac.source, ac.fx_transaction,
204 ac.amount, ac.cleared, a.id, n.name
206 JOIN chart ch ON (ac.chart_id = ch.id)
207 JOIN ap a ON (a.id = ac.trans_id)
208 JOIN vendor n ON (n.id = a.vendor_id)
209 WHERE ch.accno = '$form->{accno}'
210 AND ac.fx_transaction = '0'
212 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
213 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
215 $query .= " ORDER BY 1,2,3";
218 $sth = $dbh->prepare($query);
219 $sth->execute || $form->dberror($query);
225 if ($form->{summary}) {
226 $query = qq|SELECT ac.amount, ac.cleared
228 JOIN ar a ON (a.id = ac.trans_id)
229 JOIN customer n ON (n.id = a.customer_id)
230 WHERE ac.fx_transaction = '1'
233 AND ac.trans_id IN (SELECT id FROM ar a
234 JOIN acc_trans ac ON (a.id = ac.trans_id)
239 (SELECT fxgain_accno_id FROM defaults
241 SELECT fxloss_accno_id FROM defaults))
246 SELECT ac.amount, ac.cleared
248 JOIN ap a ON (a.id = ac.trans_id)
249 JOIN vendor n ON (n.id = a.vendor_id)
250 WHERE ac.fx_transaction = '1'
253 AND ac.trans_id IN (SELECT id FROM ap a
254 JOIN acc_trans ac ON (a.id = ac.trans_id)
259 (SELECT fxgain_accno_id FROM defaults
261 SELECT fxloss_accno_id FROM defaults))
266 $query = qq|SELECT ac.amount, ac.cleared
268 WHERE ac.trans_id = ?
269 AND ac.fx_transaction = '1'
273 (SELECT fxgain_accno_id FROM defaults
275 SELECT fxloss_accno_id FROM defaults))
280 $fxs = $dbh->prepare($query);
283 if ($form->{summary}) {
284 $query = qq|SELECT c.name
286 JOIN ar a ON (c.id = a.customer_id)
287 JOIN acc_trans ac ON (a.id = ac.trans_id)
288 WHERE ac.transdate = ?
295 JOIN ap a ON (v.id = a.vendor_id)
296 JOIN acc_trans ac ON (a.id = ac.trans_id)
297 WHERE ac.transdate = ?
304 JOIN acc_trans ac ON (g.id = ac.trans_id)
305 WHERE ac.transdate = ?
311 $query .= " ORDER BY 1";
312 $dr = $dbh->prepare($query);
315 $query = qq|SELECT c.name
317 JOIN ar a ON (c.id = a.customer_id)
318 JOIN acc_trans ac ON (a.id = ac.trans_id)
319 WHERE ac.transdate = ?
326 JOIN ap a ON (v.id = a.vendor_id)
327 JOIN acc_trans ac ON (a.id = ac.trans_id)
328 WHERE ac.transdate = ?
335 JOIN acc_trans ac ON (g.id = ac.trans_id)
336 WHERE ac.transdate = ?
342 $query .= " ORDER BY 1";
343 $cr = $dbh->prepare($query);
351 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
353 if ($form->{summary}) {
355 if ($ref->{amount} > 0) {
356 $dr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
357 $ref->{oldcleared} = $ref->{cleared};
359 while (($name) = $dr->fetchrow_array) {
360 push @{ $ref->{name} }, $name;
365 $cr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
366 $ref->{oldcleared} = $ref->{cleared};
368 while (($name) = $cr->fetchrow_array) {
369 push @{ $ref->{name} }, $name;
376 push @{ $ref->{name} }, $ref->{description};
379 push @{ $form->{PR} }, $ref;
381 # include fx transactions
384 $ref->{oldcleared} = $ref->{cleared};
385 if ($form->{summary}) {
386 foreach $name (@{ $ref->{name} }) {
387 $fxs->execute($name, $ref->{transdate}, $ref->{source}, $ref->{cleared}, $name, $ref->{transdate}, $ref->{source}, $ref->{cleared});
388 while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
390 $amount += $fxref->{amount};
395 $fxs->execute($ref->{id});
396 while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
398 $amount += $fxref->{amount};
405 map { $fxref->{$_} = $ref->{$_} } keys %$ref;
406 $fxref->{fx_transaction} = 1;
408 $fxref->{source} = "";
409 $fxref->{transdate} = "";
410 $fxref->{amount} = $amount;
411 push @{ $form->{PR} }, $fxref;
423 my ($self, $myconfig, $form) = @_;
425 # connect to database
426 my $dbh = $form->dbconnect($myconfig);
428 my $query = qq|SELECT id FROM chart
429 WHERE accno = '$form->{accno}'|;
430 my ($chart_id) = $dbh->selectrow_array($query);
433 $query = qq|SELECT trans_id FROM acc_trans
437 my $sth = $dbh->prepare($query) || $form->dberror($query);
442 $query = qq|UPDATE acc_trans SET cleared = '1'
446 AND chart_id = $chart_id|;
447 my $tth = $dbh->prepare($query) || $form->dberror($query);
450 for $i (1 .. $form->{rowcount}) {
451 if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
452 if ($form->{summary}) {
453 $sth->execute($form->{"source_$i"}, $form->{"transdate_$i"}) || $form->dberror;
455 while (($trans_id) = $sth->fetchrow_array) {
456 $tth->execute($trans_id, $form->{"transdate_$i"}) || $form->dberror;
463 $tth->execute($form->{"id_$i"}, $form->{"transdate_$i"}) || $form->dberror;