1#=====================================================================
2# SQL-Ledger ERP
3# Copyright (C) 2006
4#
5#  Author: DWS Systems Inc.
6#     Web: http://www.sql-ledger.com
7#
8#======================================================================
9#
10# Check and receipt printing payment module backend routines
11# Number to text conversion routines are in
12# locale/{countrycode}/Num2text
13#
14#======================================================================
15
16package CP;
17
18
19sub new {
20  my ($type, $countrycode) = @_;
21
22  my $self = {};
23
24  if ($countrycode) {
25    if (-f "locale/$countrycode/Num2text") {
26      require "locale/$countrycode/Num2text";
27    } else {
28      use SL::Num2text;
29    }
30  } else {
31    use SL::Num2text;
32  }
33
34  bless $self, $type;
35
36}
37
38
39sub paymentaccounts {
40  my ($self, $myconfig, $form, $dbh) = @_;
41
42  my $disconnect = ($dbh) ? 0 : 1;
43
44  # connect to database
45  $dbh = $form->dbconnect($myconfig) unless $dbh;
46
47  my $query = qq|SELECT c.accno, c.description, c.link,
48                 l.description AS translation
49                 FROM chart c
50		             LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
51                 WHERE c.link LIKE '%$form->{ARAP}%'
52                 AND c.closed = '0'
53                 ORDER BY c.accno|;
54  my $sth = $dbh->prepare($query);
55  $sth->execute || $form->dberror($query);
56
57  $form->{PR}{$form->{ARAP}} = ();
58  $form->{PR}{"$form->{ARAP}_paid"} = ();
59  $form->{PR}{"$form->{ARAP}_discount"} = ();
60
61  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
62    $ref->{description} = $ref->{translation} if $ref->{translation};
63    foreach my $item (split /:/, $ref->{link}) {
64      if ($item eq $form->{ARAP}) {
65        push @{ $form->{PR}{$form->{ARAP}} }, $ref;
66      }
67      if ($item eq "$form->{ARAP}_paid") {
68        push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
69      }
70      if ($item eq "$form->{ARAP}_discount") {
71        push @{ $form->{PR}{"$form->{ARAP}_discount"} }, $ref;
72      }
73    }
74  }
75  $sth->finish;
76
77  # get currencies and closedto
78  $form->{datepaid} = $form->current_date($myconfig);
79
80  ($form->{employee}) = $form->get_employee($dbh);
81
82  my %defaults = $form->get_defaults($dbh, \@{['closedto', "$form->{type}\_%"]});
83  for (keys %defaults) { $form->{$_} = $defaults{$_} }
84
85  $form->{currencies} = $form->get_currencies($myconfig, $dbh);
86
87  if ($form->{payment} eq 'payments') {
88    # get language codes
89    $form->all_languages($myconfig, $dbh);
90
91    $form->all_departments($myconfig, $dbh, $form->{vc});
92  }
93
94  if ($form->{vc} eq 'vendor') {
95    # get business types
96    $query = qq|SELECT *
97                FROM business
98                ORDER BY rn|;
99    $sth = $dbh->prepare($query);
100    $sth->execute || $form->dberror($query);
101
102    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
103      push @{ $form->{all_business} }, $ref;
104    }
105    $sth->finish;
106  }
107
108  $query = qq|SELECT *
109              FROM paymentmethod
110              ORDER BY rn|;
111  $sth = $dbh->prepare($query);
112  $sth->execute || $form->dberror($query);
113
114  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
115    push @{ $form->{all_paymentmethod} }, $ref;
116  }
117  $sth->finish;
118
119  $form->get_peripherals($dbh);
120
121  $dbh->disconnect if $disconnect;
122
123}
124
125
126sub get_openvc {
127  my ($self, $myconfig, $form) = @_;
128
129  my $dbh = $form->dbconnect($myconfig);
130
131  $form->remove_locks($myconfig, $dbh, $form->{arap});
132  $form->{redo} = 1;
133  $form->{locks_removed} = 1;
134
135  my $where = qq|a.amount != a.paid
136                 AND a.approved = '1'
137                 AND a.onhold = '0'
138                 AND NOT a.id IN (SELECT id
139		             FROM semaphore)|;
140
141  $form->{vc} =~ s/;//g;
142  my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
143
144  my $sth;
145  my $ref;
146  my $i = 0;
147  my $var;
148
149  if ($form->{duedatefrom}) {
150    $where .= qq|
151	      AND a.duedate >= '$form->{duedatefrom}'|;
152  }
153  if ($form->{duedateto}) {
154    $where .= qq|
155	      AND a.duedate <= '$form->{duedateto}'|;
156  }
157
158  my $id;
159  my $description;
160
161  if (! $form->{all_vc}) {
162    ($id, $description) = split /--/, $form->{$form->{ARAP}};
163    if ($id) {
164      $where .= qq|
165      	AND c.accno = '$id'|;
166    }
167    if ($form->{vc} eq 'vendor') {
168      ($description, $id) = split /--/, $form->{business};
169      if ($id) {
170        $where .= qq|
171        AND vc.business_id = $id|;
172      }
173    }
174  }
175
176  ($description, $id) = split /--/, $form->{paymentmethod};
177
178  if ($id) {
179    $where .= qq|
180	    AND a.paymentmethod_id = $id|;
181  }
182
183  if (! $form->{"select$form->{vc}"}) {
184    if ($form->{$form->{vc}}) {
185      $var = $form->like(lc $form->{$form->{vc}});
186      $where .= qq| AND lower(vc.name) LIKE '$var'|;
187    }
188    if ($form->{"$form->{vc}number"}) {
189      $var = $form->like(lc $form->{"$form->{vc}number"});
190      $where .= qq| AND lower(vc.$form->{vc}number) LIKE '$var'|;
191    }
192  }
193
194  my %defaults = $form->get_defaults($dbh, \@{['namesbynumber']});
195  my $sortorder = "name";
196  if ($defaults{namesbynumber}) {
197    $sortorder = "$form->{vc}number";
198  }
199
200  # build selection list
201  $query = qq|SELECT vc.*,
202              ad.address1, ad.address2, ad.city, ad.state, ad.zipcode,
203              ad.country, a.amount, a.paid,
204              a.exchangerate,
205              l.description AS translation,
206              ch.accno AS $form->{ARAP},
207              ch.description AS $form->{ARAP}_description,
208              pa.accno AS $form->{ARAP}_paid,
209              pa.description AS $form->{ARAP}_paid_description,
210              pm.description AS paymentmethod
211              FROM $form->{vc} vc
212              JOIN $arap a ON (a.$form->{vc}_id = vc.id)
213              JOIN acc_trans ac ON (a.id = ac.trans_id)
214              JOIN chart c ON (c.id = ac.chart_id)
215              JOIN address ad ON (ad.trans_id = vc.id)
216              LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
217              LEFT JOIN chart ch ON (ch.id = vc.arap_accno_id)
218              LEFT JOIN chart pa ON (pa.id = vc.payment_accno_id)
219              LEFT JOIN paymentmethod pm ON (pm.id = vc.paymentmethod_id)
220              WHERE $where
221              ORDER BY vc.$sortorder|;
222  $sth = $dbh->prepare($query);
223  $sth->execute || $form->dberror($query);
224
225  my %due;
226  my @transactions = ();
227
228  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
229
230    $ref->{exchangerate} ||= 1;
231    $ref->{description} = $ref->{translation} if $ref->{translation};
232
233    if ($form->{vc} eq 'vendor') {
234      $ref->{fxdue} = $form->round_amount(($ref->{amount} - $ref->{paid}) / $ref->{exchangerate}, $form->{precision});
235      $due{$ref->{id}} += $ref->{fxdue};
236    }
237    push @transactions, $ref;
238  }
239  $sth->finish;
240
241  my %vc;
242
243  @{ $form->{name_list} } = ();
244
245  foreach $ref (@transactions) {
246
247    next if $vc{$ref->{id}};
248    if ($form->{vc} eq 'vendor') {
249      if ($ref->{threshold} > 0) {
250        next if $due{$ref->{id}} < $ref->{threshold};
251      }
252    }
253
254    $i++;
255    $vc{$ref->{id}} = 1;
256    push @{ $form->{name_list} }, $ref;
257
258  }
259
260  $form->all_departments($myconfig, $dbh, $form->{vc});
261
262  $form->all_languages($myconfig, $dbh);
263
264  $dbh->disconnect;
265
266  $i;
267
268}
269
270
271sub retrieve {
272  my ($self, $myconfig, $form) = @_;
273
274  my $id;
275
276  # connect to database
277  my $dbh = $form->dbconnect($myconfig);
278
279  $form->{id} *= 1;
280
281  my %defaults = $form->get_defaults($dbh, \@{['precision']});
282  for (keys %defaults) { $form->{$_} = $defaults{$_} }
283
284  my $ml = 1;
285
286  if ($form->{vc} eq 'customer') {
287    $ml = -1;
288  }
289
290  my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate,
291                 ac.transdate AS datepaid, a.amount,
292		 SUM(ac.amount) * $ml AS paid, ac.source, ac.memo,
293		 a.$form->{vc}_id,
294		 a.curr, a.discountterms, a.cashdiscount, a.netamount,
295		 date '$form->{transdate}' <= a.transdate + a.discountterms AS calcdiscount,
296		 ac.approved,
297		 a.exchangerate,
298		    (SELECT acc.amount * $ml
299		     FROM acc_trans acc
300		     JOIN chart c ON (c.id = acc.chart_id)
301		     WHERE acc.trans_id = ac.trans_id
302		     AND acc.fx_transaction = '0'
303		     AND acc.vr_id = $form->{id}
304		     AND c.link LIKE '%$form->{ARAP}_discount%') AS discount
305	         FROM $form->{arap} a
306		 JOIN acc_trans ac ON (ac.trans_id = a.id)
307		 JOIN chart ch ON (ch.id = ac.chart_id)
308		 WHERE ac.vr_id = $form->{id}
309		 AND ac.fx_transaction = '0'
310		 AND ch.link LIKE '%$form->{ARAP}_paid%'
311                 GROUP BY a.id, a.invnumber, a.transdate, a.duedate,
312		 a.amount, a.paid, a.discountterms, a.cashdiscount, a.netamount,
313		 a.$form->{vc}_id, a.curr, ac.transdate, calcdiscount,
314		 ac.approved, a.exchangerate, ac.trans_id, ac.source, ac.memo|;
315
316  my @sf = qw(transdate invnumber);
317  my %ordinal = $form->ordinal_order($dbh, $query);
318  $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal);
319
320  my $sth = $dbh->prepare($query);
321  $sth->execute || $form->dberror($query);
322
323  $query = qq|SELECT c.accno, c.description,
324              l.description AS translation
325              FROM acc_trans ac
326	      JOIN chart c ON (c.id = ac.chart_id)
327	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
328	      WHERE ac.trans_id = ?
329	      AND ac.fx_transaction = '0'
330	      AND (c.link LIKE '$form->{ARAP}%'
331	           OR c.link LIKE '%:$form->{ARAP}')|;
332  my $ath = $dbh->prepare($query);
333
334  $query = qq|SELECT c.accno, c.description,
335              l.description AS translation
336              FROM acc_trans ac
337	      JOIN chart c ON (c.id = ac.chart_id)
338	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
339	      WHERE ac.trans_id = ?
340	      AND ac.transdate = ?
341	      AND ac.fx_transaction = '0'
342	      AND c.link LIKE '%$form->{ARAP}_paid%'|;
343  my $pth = $dbh->prepare($query);
344
345  $query = qq|SELECT c.accno, c.description,
346              l.description AS translation
347              FROM acc_trans ac
348	      JOIN chart c ON (c.id = ac.chart_id)
349	      LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
350	      WHERE ac.trans_id = ?
351	      AND ac.fx_transaction = '0'
352	      AND c.link LIKE '%$form->{ARAP}_discount%'|;
353  my $dth = $dbh->prepare($query);
354
355  my $accno;
356  my $description;
357  my $translation;
358
359  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
360    $form->{edit} .= "$ref->{id} ";
361
362    push @{ $form->{transactions} }, $ref;
363
364    $form->{"$form->{vc}_id"} = $ref->{"$form->{vc}_id"};
365    $form->{source} ||= $ref->{source};
366    $form->{memo} ||= $ref->{memo};
367    $form->{approved} ||= $ref->{approved};
368    $ref->{calcdiscount} = 0 unless $ref->{discountterms};
369
370    # AR/AP account
371    if (!$form->{arap_accno}) {
372      $ath->execute($ref->{id});
373      ($accno, $description, $translation) = $ath->fetchrow_array;
374      $ath->finish;
375      $description = $translation if $translation;
376      $form->{arap_accno} = "${accno}--$description";
377    }
378    # payment
379    if (!$form->{payment_accno}) {
380      $pth->execute($ref->{id}, $ref->{datepaid});
381      ($accno, $description, $translation) = $pth->fetchrow_array;
382      $pth->finish;
383      $description = $translation if $translation;
384      $form->{"$form->{ARAP}_paid"} = "${accno}--$description";
385      $form->{payment_accno} = $form->{"$form->{ARAP}_paid"};
386    }
387    # discount
388    if (!$form->{discount_accno}) {
389      if ($ref->{discount}) {
390	$dth->execute($ref->{id});
391	($accno, $description, $translation) = $dth->fetchrow_array;
392	$dth->finish;
393	$description = $translation if $translation;
394	$form->{"$form->{ARAP}_discount"} = "${accno}--$description";
395	$form->{discount_accno} = $form->{"$form->{ARAP}_discount"};
396      }
397    }
398
399  }
400  chop $form->{edit};
401  $sth->finish;
402
403  $query = qq|SELECT br.description, vr.vouchernumber
404              FROM vr
405	      JOIN br ON (br.id = vr.br_id)
406	      WHERE vr.id = $form->{id}|;
407  ($form->{batchdescription}, $form->{vouchernumber}) = $dbh->selectrow_array($query);
408
409  $form->{voucherid} = $form->{id};
410  $form->{id} *= 1;
411  AA->get_name($myconfig, $form, $dbh);
412
413  $form->{"old$form->{vc}"} = qq|$form->{$form->{vc}}--$form->{"$form->{vc}_id"}|;
414
415  &paymentaccounts("", $myconfig, $form, $dbh);
416
417  $form->all_departments($myconfig, $dbh, $form->{vc});
418
419  $form->all_languages($myconfig, $dbh);
420
421  $dbh->disconnect;
422
423}
424
425
426sub get_openinvoices {
427  my ($self, $myconfig, $form) = @_;
428
429  my $id;
430
431  # connect to database
432  my $dbh = $form->dbconnect($myconfig);
433
434  $form->{vc} =~ s/;//g;
435
436  # remove locks
437  $form->remove_locks($myconfig, $dbh, $form->{arap});
438
439  my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"}
440	         AND a.amount != a.paid
441		 AND a.approved = '1'
442		 AND a.onhold = '0'
443		 AND NOT a.id IN (SELECT id
444		                  FROM semaphore)|;
445
446  my %defaults = $form->get_defaults($dbh, \@{[qw(namesbynumber cdt precision)]});
447
448  for (keys %defaults) { $form->{$_} = $defaults{$_} }
449
450  my @sf = qw(transdate invnumber);
451
452  if ($form->{payment} eq 'payments') {
453    $where = qq|WHERE a.amount != a.paid
454                AND a.approved = '1'
455		AND a.onhold = '0'
456		AND NOT a.id IN (SELECT id
457		                 FROM semaphore)|;
458    @sf = qw(name transdate);
459    if ($defaults{namesbynumber}) {
460      @sf = ("$form->{vc}number", "transdate");
461    }
462  }
463
464  $where .= qq|
465              AND a.curr = '$form->{currency}'| if $form->{currency};
466  $where .= qq|
467	      AND a.duedate >= '$form->{duedatefrom}'| if $form->{duedatefrom};
468  $where .= qq|
469	      AND a.duedate <= '$form->{duedateto}'| if $form->{duedateto};
470
471  (undef, $id) = split /--/, $form->{department};
472  $where .= qq|
473                 AND a.department_id = $id| if $id;
474
475  (undef, $id) = split /--/, $form->{paymentmethod};
476  $where .= qq|
477                 AND a.paymentmethod_id = $id| if $id;
478
479  ($id) = split /--/, $form->{$form->{ARAP}};
480  $where .= qq|
481		 AND ch.accno = '$id'| if $id;
482
483  if ($form->{vc} eq 'vendor') {
484    (undef, $id) = split /--/, $form->{business};
485    $where .= qq|
486		   AND vc.business_id = $id| if $id;
487
488  }
489
490  my $datepaid = ($form->{datepaid}) ? "date '$form->{datepaid}'" : 'current_date';
491  my $query = qq|SELECT DISTINCT a.id, a.invnumber, a.transdate, a.duedate,
492                 a.description AS invdescription,
493		 a.amount, a.paid, a.curr, vc.$form->{vc}number, vc.name,
494		 vc.language_code, vc.threshold, vc.curr AS currency,
495		 vc.payment_accno_id,
496		 a.$form->{vc}_id,
497		 a.discountterms, a.cashdiscount, a.netamount,
498		 $datepaid <= a.transdate + a.discountterms AS calcdiscount,
499		 a.exchangerate, ex.exchangerate AS vcexch,
500		 a.taxincluded
501		 FROM acc_trans ac
502		 JOIN $form->{arap} a ON (a.id = ac.trans_id)
503		 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id)
504		 JOIN chart ch ON (ch.id = ac.chart_id)
505		 LEFT JOIN exchangerate ex ON (ex.curr = vc.curr AND ex.transdate = a.transdate)
506		 $where|;
507
508  my %ordinal = $form->ordinal_order($dbh, $query);
509  $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal);
510
511  my $sth = $dbh->prepare($query);
512  $sth->execute || $form->dberror($query);
513
514  # discount
515  $query = qq|SELECT sum(ac.amount)
516              FROM acc_trans ac
517	      JOIN chart c ON (c.id = ac.chart_id)
518	      WHERE c.link LIKE '%$form->{ARAP}_discount%'
519	      AND ac.approved = '1'
520	      AND ac.trans_id = ?|;
521  my $tth = $dbh->prepare($query) || $form->dberror($query);
522
523  my %total;
524  my @transactions = ();
525
526  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
527
528    $ref->{calcdiscount} = 0 if ! $ref->{cashdiscount};
529    if ($ref->{calcdiscount}) {
530      $tth->execute($ref->{id});
531      if ($tth->fetchrow_array) {
532	$ref->{calcdiscount} = 0;
533      }
534      $tth->finish;
535    }
536
537    $ref->{exchangerate} ||= 1;
538    $ref->{vcexch} ||= 1;
539
540    # for threshold calculation
541    $ref->{fxdue} = $form->round_amount(($ref->{amount} - $ref->{paid}) / $ref->{vcexch}, $form->{precision});
542
543    $total{$ref->{"$form->{vc}_id"}} += $ref->{fxdue};
544    push @transactions, $ref;
545  }
546
547  $sth->finish;
548
549  $form->{PR} = ();
550
551  foreach $ref (@transactions) {
552    if ($form->{vc} eq 'vendor') {
553      if ($ref->{threshold} > 0) {
554	$total{$ref->{"$form->{vc}_id"}} = $form->round_amount($total{$ref->{"$form->{vc}_id"}}, $form->{precision});
555        next if $total{$ref->{"$form->{vc}_id"}} < $ref->{threshold};
556      }
557    }
558    $form->create_lock($myconfig, $dbh, $ref->{id}, $form->{arap});
559    push @{ $form->{PR} }, $ref;
560  }
561
562  $dbh->disconnect;
563
564}
565
566
567
568sub post_payment {
569  my ($self, $myconfig, $form) = @_;
570
571  # connect to database, turn AutoCommit off
572  $dbh = $form->dbconnect_noauto($myconfig);
573
574  my ($paymentaccno) = split /--/, $form->{"$form->{ARAP}_paid"};
575  my ($discountaccno) = split /--/, $form->{"$form->{ARAP}_discount"};
576
577  # if currency ne defaultcurrency update exchangerate
578  if ($form->{currency} ne $form->{defaultcurrency}) {
579    $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
580
581    $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate});
582  } else {
583    $form->{exchangerate} = 1;
584  }
585
586  $form->update_defaults($myconfig, qq|$form->{type}_$paymentaccno|, $dbh, $form->{source});
587
588  my $query;
589  my $sth;
590
591  # tax accounts
592  $query = qq|SELECT DISTINCT c.accno, t.rate, t.validto
593              FROM chart c
594	      JOIN acc_trans ac ON (ac.chart_id = c.id)
595	      JOIN tax t ON (t.chart_id = c.id)
596	      WHERE c.link LIKE '%$form->{ARAP}_tax%'
597	      AND ac.trans_id = ?
598	      AND (t.validto >= ? OR t.validto IS NULL)
599	      ORDER BY t.validto DESC|;
600  my $tth = $dbh->prepare($query) || $form->dberror($query);
601
602  my %defaults = $form->get_defaults($dbh, \@{['fxgainloss_accno_id', 'cdt']});
603
604  my $ml = ($form->{ARAP} eq 'AR') ? 1 : -1;
605
606  $form->{arap} = lc $form->{ARAP};
607
608  my $where = qq|
609		c.link = '$form->{ARAP}'
610		|;
611
612  # AR/AP default account
613  $query = qq|SELECT c.id
614              FROM chart c
615              WHERE $where|;
616  my ($arapdefault) = $dbh->selectrow_array($query);
617
618  # AR/AP account
619  $query = qq|SELECT DISTINCT c.id
620              FROM chart c
621	      JOIN acc_trans a ON (a.chart_id = c.id)
622	      WHERE $where
623	      AND a.trans_id = ?|;
624  my $ath = $dbh->prepare($query) || $form->dberror($query);
625
626  my $paymentamount = $form->parse_amount($myconfig, $form->{amount});
627
628  # query to retrieve paid amount
629  $query = qq|SELECT amount, netamount, paid, transdate, taxincluded,
630              exchangerate
631              FROM $form->{arap}
632              WHERE id = ?
633 	      FOR UPDATE|;
634  my $pth = $dbh->prepare($query) || $form->dberror($query);
635
636  my %trans;
637  my $ref;
638  my $amount;
639  my $vth;
640  my $dth;
641  my $ith;
642  my $dith;
643
644  my %cdt;
645  my $diff;
646  my $accno;
647  my $rate;
648
649  # delete payments
650  $form->{voucherid} *= 1;
651  if ($form->{edit} && $form->{voucherid}) {
652    $query = qq|SELECT SUM(ac.amount) * $ml * -1
653                FROM acc_trans ac
654		JOIN chart c ON (c.id = ac.chart_id)
655		WHERE ac.trans_id = ?
656		AND ac.vr_id = $form->{voucherid}
657		AND c.link LIKE '%$form->{ARAP}_paid%'
658		AND NOT (ac.chart_id = $defaults{fxgainloss_accno_id})|;
659    $sth = $dbh->prepare($query) || $form->dberror($query);
660
661    # discount
662    $query = qq|SELECT sum(ac.amount) * $ml * -1
663                FROM acc_trans ac
664		JOIN chart c ON (c.id = ac.chart_id)
665		WHERE ac.trans_id = ?
666		AND ac.vr_id = $form->{voucherid}
667		AND c.link LIKE '%$form->{ARAP}_discount%'|;
668    $dth = $dbh->prepare($query) || $form->dberror($query);
669
670    foreach $id (split / /, $form->{edit}) {
671
672      $query = qq|SELECT id
673		  FROM acc_trans
674		  WHERE trans_id = $id
675		  AND vr_id = $form->{voucherid}|;
676      $ith = $dbh->prepare($query) || $form->dberror($query);
677      $ith->execute;
678
679      while (($paymentid) = $ith->fetchrow_array) {
680	$query = qq|DELETE FROM payment
681		    WHERE id = $paymentid
682		    AND trans_id = $id|;
683	$dith = $dbh->prepare($query) || $form->dberror($query);
684	$dbh->do($query) || $form->dberror($query);
685      }
686      $ith->finish;
687
688      # payments
689      $sth->execute($id);
690      $ref = $sth->fetchrow_hashref(NAME_lc);
691      for (keys %$ref) { $trans{$id}{$_} = $ref->{$_} }
692      $sth->finish;
693
694      # discount
695      $dth->execute($id);
696      ($trans{$id}{discount}) = $dth->fetchrow_array;
697      $dth->finish;
698
699      # update arap
700      $form->update_balance($dbh,
701                            $form->{arap},
702			    'paid',
703			    qq|id = $id|,
704			    ($trans{$id}{amount} + $trans{$id}{discount}) * -1);
705
706      # update batch
707      $form->update_balance($dbh,
708  			    'br',
709			    'amount',
710			    qq|id = $form->{batchid}|,
711			    ($trans{$id}{amount} + $trans{$id}{discount}) * -1);
712
713    }
714
715    $query = qq|DELETE FROM acc_trans
716                WHERE vr_id = $form->{voucherid}|;
717    $dbh->do($query) || $form->dberror($query);
718
719    $query = qq|DELETE FROM vr
720                WHERE id = $form->{voucherid}|;
721    $dbh->do($query) || $form->dberror($query);
722
723  }
724
725  my %audittrail;
726  my $action = 'posted';
727  my $approved = ($form->{pending}) ? '0' : '1';
728
729  if (!$approved) {
730    $action = 'saved';
731  }
732
733  my $voucherid = 'NULL';
734
735  if ($form->{batch}) {
736    $voucherid = $form->{voucherid};
737    if (! $form->{voucherid}) {
738      $query = qq|SELECT nextval('id')|;
739      ($voucherid) = $dbh->selectrow_array($query);
740    }
741    $form->{vouchernumber} = $form->update_defaults($myconfig, 'vouchernumber', $dbh) unless $form->{vouchernumber};
742  }
743
744  $query = qq|INSERT INTO vr (br_id, trans_id, id, vouchernumber)
745              VALUES ($form->{batchid}, ?, $voucherid, |
746	      .$dbh->quote($form->{vouchernumber}).qq|)|;
747  $vth = $dbh->prepare($query) || $form->dberror($query);
748
749  my $assignvoucherid;
750  my $arap;
751  my (undef, $paymentmethod_id) = split /--/, $form->{paymentmethod};
752  $paymentmethod_id *= 1;
753
754  # go through line by line
755  for my $i (1 .. $form->{rowcount}) {
756
757    $assignvoucherid = 1;
758    $cdt = 0;
759
760    for (qw(paid discount)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
761
762    if ($form->{"checked_$i"}) {
763
764      # paymentid
765      $query = qq|SELECT MAX(id)
766		  FROM payment
767		  WHERE trans_id = $form->{"id_$i"}|;
768      ($paymentid) = $dbh->selectrow_array($query);
769      $paymentid++;
770
771      # original paid
772      # lock for update
773      $pth->execute($form->{"id_$i"}) || $form->dberror;
774      $ref = $pth->fetchrow_hashref(NAME_lc);
775      for (keys %$ref) { $trans{$form->{"id_$i"}}{$_} = $ref->{$_} }
776
777      $paymentamount -= $form->{"paid_$i"};
778
779      $ath->execute($form->{"id_$i"}) || $form->dberror;
780      ($arap) = $ath->fetchrow_array || $arapdefault;
781      $ath->finish;
782
783      $amount = $form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision});
784
785      # add AR/AP
786      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
787                  amount, approved, vr_id)
788                  VALUES ($form->{"id_$i"}, $arap, '$form->{datepaid}',
789		  $amount * $ml, '$approved',
790		  $voucherid)|;
791      $dbh->do($query) || $form->dberror($query);
792
793      # add payment
794      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
795                  amount, source, memo, approved, vr_id, id)
796                  VALUES ($form->{"id_$i"},
797		         (SELECT id FROM chart
798		          WHERE accno = '$paymentaccno'),
799		  '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
800		  .$dbh->quote($form->{source}).qq|, |
801		  .$dbh->quote($form->{memo}).qq|, '$approved',
802		  $voucherid, $paymentid)|;
803      $dbh->do($query) || $form->dberror($query);
804
805      $query = qq|INSERT INTO payment (id, trans_id, exchangerate,
806                  paymentmethod_id)
807                  VALUES ($paymentid, $form->{"id_$i"}, $form->{exchangerate},
808		  $paymentmethod_id)|;
809      $dbh->do($query) || $form->dberror($query);
810
811      # add exchangerate difference if currency ne defaultcurrency
812      $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1) * $ml * -1, $form->{precision});
813
814      if ($amount) {
815        # exchangerate difference
816	$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
817		    amount, fx_transaction, source, approved, vr_id)
818		    VALUES ($form->{"id_$i"},
819		           (SELECT id FROM chart
820			    WHERE accno = '$paymentaccno'),
821		  '$form->{datepaid}', $amount, '1', |
822		  .$dbh->quote($form->{source}).qq|, '$approved',
823		  $voucherid)|;
824	$dbh->do($query) || $form->dberror($query);
825      }
826
827      # gain/loss
828      $amount = $form->round_amount(($form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}) - $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, $form->{precision})) * $ml * -1, $form->{precision});
829      if ($amount) {
830	$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
831		    amount, fx_transaction, approved, vr_id)
832		    VALUES ($form->{"id_$i"}, $defaults{fxgainloss_accno_id},
833		    '$form->{datepaid}', $amount, '1', '$approved',
834		    $voucherid)|;
835	$dbh->do($query) || $form->dberror($query);
836      }
837
838
839      # deduct tax for cash discount
840      if ($form->{"discount_$i"}) {
841
842	%cdt = ();
843	$diff = 0;
844
845	if ($defaults{cdt} && !$trans{$form->{"id_$i"}}{taxincluded}) {
846
847	  $tth->execute($form->{"id_$i"}, $trans{$form->{"id_$i"}}{transdate}) || $form->dberror;
848
849	  my $totalrate = 0;
850	  while (($accno, $rate) = $tth->fetchrow_array) {
851	    $totalrate += $rate;
852	    $cdt{$accno} = $rate;
853	  }
854	  $tth->finish;
855
856	  $cdt = $form->round_amount($form->{"discount_$i"} * $totalrate, $form->{precision});
857
858	  for (keys %cdt) {
859	    $accno = $_;
860	    if ($totalrate) {
861	      $amount = $cdt * $cdt{$_} / $totalrate;
862	      $cdt{$_} = $form->round_amount($amount, $form->{precision});
863	      $diff += ($amount - $cdt{$_});
864	    }
865	  }
866	  $diff = $form->round_amount($diff, $form->{precision});
867	  if ($diff != 0) {
868	    $cdt{$accno} -= $diff;
869	  }
870
871	  $cdt = $form->round_amount($cdt * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision});
872
873	}
874
875	$cdt{$discountaccno} = $form->{"discount_$i"};
876
877	for (keys %cdt) {
878          # add AR/AP
879	  $amount = $form->round_amount($cdt{$_} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision});
880	  $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
881		      amount, approved, vr_id, id)
882		      VALUES ($form->{"id_$i"}, $arap, '$form->{datepaid}',
883		      $amount * $ml, '$approved',
884		      $voucherid, $form->{"id_$i"})|;
885	  $dbh->do($query) || $form->dberror($query);
886
887          # add discount
888	  $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
889		      amount, source, memo, approved, vr_id, id)
890		      VALUES ($form->{"id_$i"},
891			     (SELECT id FROM chart
892			      WHERE accno = '$_'),
893		      '$form->{datepaid}', $cdt{$_} * $ml * -1, |
894		      .$dbh->quote($form->{source}).qq|, |
895		      .$dbh->quote($form->{memo}).qq|, '$approved',
896		      $voucherid, $form->{"id_$i"})|;
897	  $dbh->do($query) || $form->dberror($query);
898
899	  # add exchangerate difference if currency ne defaultcurrency
900	  $amount = $form->round_amount($cdt{$_} * ($form->{exchangerate} - 1) * $ml * -1, $form->{precision});
901
902	  if ($amount) {
903	    # exchangerate difference
904	    $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
905			amount, fx_transaction, source, approved, vr_id, id)
906			VALUES ($form->{"id_$i"},
907			       (SELECT id FROM chart
908				WHERE accno = '$_'),
909		      '$form->{datepaid}', $amount, '1', |
910		      .$dbh->quote($form->{source}).qq|, '$approved',
911		      $voucherid, $form->{"id_$i"})|;
912	    $dbh->do($query) || $form->dberror($query);
913
914	    # gain/loss
915	    $amount = $form->round_amount(($form->round_amount($cdt{$_} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}) - $form->round_amount($cdt{$_} * $form->{exchangerate}, $form->{precision})) * $ml * -1, $form->{precision});
916
917	    if ($amount) {
918	      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
919			  amount, fx_transaction, approved, vr_id, id)
920			  VALUES ($form->{"id_$i"}, $defaults{fxgainloss_accno_id},
921			  '$form->{datepaid}', $amount, '1', '$approved',
922			  $voucherid, $form->{"id_$i"})|;
923	      $dbh->do($query) || $form->dberror($query);
924	    }
925	  }
926	}
927      }
928
929
930      $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision});
931      $form->{"discount_$i"} = $form->round_amount($form->{"discount_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision});
932
933      # unlock arap
934      $pth->finish;
935
936      $amount = $form->round_amount($trans{$form->{"id_$i"}}{paid} + $form->{"paid_$i"} + $form->{"discount_$i"}, $form->{precision});
937
938      # if discount taxable adjust ar/ap amount
939      if ($defaults{cdt} && !$trans{$form->{"id_$i"}}{taxincluded}) {
940	$trans{$form->{"id_$i"}}{amount} -= $cdt;
941      }
942
943      # update AR/AP transaction
944      $query = qq|UPDATE $form->{arap} set
945                  amount = $trans{$form->{"id_$i"}}{amount},
946		  paid = $amount,
947		  datepaid = '$form->{datepaid}',
948		  bank_id = (SELECT id FROM chart WHERE accno = '$paymentaccno'),
949		  paymentmethod_id = $paymentmethod_id
950		  WHERE id = $form->{"id_$i"}|;
951      $dbh->do($query) || $form->dberror($query);
952
953      %audittrail = ( tablename  => $form->{arap},
954                      reference  => $form->{source},
955		      formname   => $form->{formname},
956		      action     => $action,
957		      id         => $form->{"id_$i"} );
958
959      $form->audittrail($dbh, "", \%audittrail);
960
961
962      if ($form->{batch}) {
963	  # add voucher
964	  $vth->execute($form->{"id_$i"});
965	  $vth->finish;
966
967	  # update batch
968	  $form->update_balance($dbh,
969				'br',
970				'amount',
971				qq|id = $form->{batchid}|,
972				$amount);
973      }
974    }
975  }
976
977  # record a AR/AP with a payment
978  if ($form->round_amount($paymentamount, $form->{precision})) {
979    $form->{invnumber} = "";
980    if ($assignvoucherid) {
981      for (qw(id number)) { delete $form->{"voucher$_"} }
982    }
983    OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml);
984  }
985
986  $form->remove_locks($myconfig, $dbh, $form->{arap}) if $form->{payment} eq 'payment';
987
988  my $rc = $dbh->commit;
989  $dbh->disconnect;
990
991  $rc;
992
993}
994
995
996
997sub invoice_ids {
998  my ($self, $myconfig, $form) = @_;
999
1000  # connect to database
1001  my $dbh = $form->dbconnect($myconfig);
1002
1003  my $datepaid = ($form->{datepaid}) ? "date '$form->{datepaid}'" : 'current_date';
1004
1005  $form->{vc} =~ s/;//g;
1006  $form->{arap} = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
1007
1008  my $query = qq|SELECT DISTINCT a.id, a.invnumber, a.transdate, a.duedate,
1009                 a.description AS invdescription,
1010		 a.amount, a.paid, vc.$form->{vc}number, vc.name,
1011		 a.$form->{vc}_id, a.cashdiscount, a.netamount,
1012                 $datepaid <= a.transdate + a.discountterms AS calcdiscount,
1013		 a.exchangerate,
1014		    (SELECT acc.amount
1015		     FROM acc_trans acc
1016		     JOIN chart c ON (c.id = acc.chart_id)
1017		     WHERE acc.trans_id = ac.trans_id
1018		     AND acc.fx_transaction = '0'
1019		     AND c.link LIKE '%$form->{ARAP}_discount%') AS discount
1020		 FROM acc_trans ac
1021		 JOIN $form->{arap} a ON (a.id = ac.trans_id)
1022		 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id)
1023		 WHERE a.id = ?|;
1024  my $sth = $dbh->prepare($query) || $form->dberror($query);
1025
1026  my $ref;
1027
1028  for (1 .. $form->{rowcount}) {
1029    for $id (split / /, $form->{"id_$_"}) {
1030      $sth->execute($id) || $form->dberror;
1031      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1032	push @{ $form->{PR} }, $ref;
1033      }
1034      $sth->finish;
1035    }
1036  }
1037
1038  $dbh->disconnect;
1039
1040}
1041
1042
1043sub payment_register {
1044  my ($self, $myconfig, $form) = @_;
1045
1046  # connect to database
1047  my $dbh = $form->dbconnect($myconfig);
1048
1049  my %defaults = $form->get_defaults($dbh, \@{['precision']});
1050  $form->{precision} = $defaults{precision};
1051
1052  my $query = qq|SELECT c.id, c.accno, c.description,
1053                 l.description AS translation
1054                 FROM chart c
1055		 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}')
1056		 WHERE c.charttype = 'A'
1057                 AND c.link LIKE '%$form->{ARAP}_paid%'
1058                 AND c.closed = '0'
1059		 ORDER BY c.accno|;
1060  my $sth = $dbh->prepare($query);
1061  $sth->execute || $form->dberror($query);
1062
1063  my @accno;
1064  my $ref;
1065  my $arap;
1066  my $ml = 1;
1067
1068  if ($form->{ARAP} eq 'AP') {
1069    $arap = 'ap';
1070  } else {
1071    $arap = 'ar';
1072    $ml = -1;
1073  }
1074
1075  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1076    $ref->{description} = $ref->{translation} if $ref->{translation};
1077    push @accno, $ref;
1078  }
1079  $sth->finish;
1080
1081  $query = qq|SELECT acc.amount * $ml AS amount, acc.transdate AS datepaid,
1082              acc.source, acc.trans_id,
1083              vc.name, vc.id AS $form->{vc}_id
1084              FROM acc_trans acc
1085              JOIN $arap a ON (a.id = acc.trans_id)
1086              JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id)|;
1087
1088  my $where = "WHERE acc.fx_transaction = '0'
1089               AND acc.chart_id = ?";
1090
1091  unless ($form->{datepaidfrom} || $form->{datepaidto}) {
1092    ($form->{datepaidfrom}, $form->{datepaidto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
1093  }
1094  if ($form->{datepaidfrom}) {
1095    $where .= " AND acc.transdate >= '$form->{datepaidfrom}'";
1096  }
1097  if ($form->{datepaidto}) {
1098    $where .= " AND acc.transdate <= '$form->{datepaidto}'";
1099  }
1100  if ($form->{"$form->{vc}_id"}) {
1101    $where .= qq| AND vc.id = $form->{"$form->{vc}_id"}|;
1102  }
1103  $query .= qq| $where ORDER BY acc.source|;
1104
1105  $sth = $dbh->prepare($query);
1106
1107  # check if void
1108  $query = qq|SELECT SUM(acc.amount) * $ml AS amount, vc.id
1109              FROM acc_trans acc
1110              JOIN $arap a ON (a.id = acc.trans_id)
1111              JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id)
1112              WHERE acc.fx_transaction = '0'
1113              AND acc.source = ?
1114              AND acc.chart_id = ?
1115              GROUP BY vc.id|;
1116  my $ach = $dbh->prepare($query);
1117
1118  # printed
1119  my $formname = ($form->{ARAP} eq 'AP') ? 'check' : 'payment';
1120  $query = qq|SELECT printed
1121              FROM status
1122              WHERE formname LIKE '$formname'
1123              AND printed
1124              AND trans_id = ?|;
1125  my $pth = $dbh->prepare($query);
1126
1127  my $accno;
1128  ($accno) = split /--/, $form->{accno};
1129  if ($accno) {
1130    @accno = grep { $_->{accno} eq $accno } @accno;
1131  }
1132
1133  my $samesource;
1134  my %source;
1135  my %inv;
1136  my (undef, $option) = split /--/, $form->{option};
1137
1138  for (@accno) {
1139    $sth->execute($_->{id});
1140
1141    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1142      if ($form->{checknumberfrom}) {
1143        next if $ref->{source} < $form->{checknumberfrom};
1144      }
1145      if ($form->{checknumberto}) {
1146        next if $ref->{source} > $form->{checknumberto};
1147      }
1148
1149      if ($option eq 'all') {
1150        if ($ref->{amount}) {
1151          $ach->execute($ref->{source}, $_->{id});
1152          ($amount) = $ach->fetchrow_array;
1153          $ach->finish;
1154          if ($form->round_amount($amount,10) == 0) {
1155            $ref->{amount} = 0;
1156            next if $inv{$ref->{trans_id}};
1157            $inv{$ref->{trans_id}} = 1;
1158            $ref->{void} = 1;
1159          }
1160        }
1161      } elsif ($option eq 'void') {
1162        $ach->execute($ref->{source}, $_->{id});
1163        ($amount) = $ach->fetchrow_array;
1164        $ach->finish;
1165        next if ($ref->{amount} > 0);
1166        $ref->{amount} = 0;
1167        next if $form->round_amount($amount,10);
1168      } elsif ($option eq 'exclude') {
1169        $ach->execute($ref->{source}, $_->{id});
1170        ($amount) = $ach->fetchrow_array;
1171        $ach->finish;
1172        next if ($form->round_amount($amount,10) == 0);
1173      } elsif ($option eq 'printed') {
1174        $pth->execute($ref->{trans_id});
1175        ($ok) = $pth->fetchrow_array;
1176        $pth->finish;
1177        next unless $ok;
1178
1179        $ach->execute($ref->{source}, $_->{id});
1180        ($amount) = $ach->fetchrow_array;
1181        $ach->finish;
1182        next if ($form->round_amount($amount,10) == 0);
1183
1184      } elsif ($option eq 'notprinted') {
1185        $pth->execute($ref->{trans_id});
1186        ($ok) = $pth->fetchrow_array;
1187        $pth->finish;
1188        next if $ok;
1189
1190        $ach->execute($ref->{source}, $_->{id});
1191        ($amount) = $ach->fetchrow_array;
1192        $ach->finish;
1193        next if ($form->round_amount($amount,10) == 0);
1194
1195      }
1196
1197      $ref->{datetonum} = $form->datetonum($myconfig, $ref->{datepaid});
1198
1199      $form->{"$_->{accno}"} = "$_->{accno}--$_->{description}";
1200
1201      if ($ref->{source} && $ref->{source} eq $samesource) {
1202        $i = @{ $source{$_->{accno}} };
1203        $source{$_->{accno}}[$i-1]->{amount} += $ref->{amount};
1204        $source{$_->{accno}}[$i-1]->{trans_id} .= "\n$ref->{trans_id}";
1205      } else {
1206        push @{ $source{$_->{accno}} }, $ref;
1207      }
1208
1209      $samesource = $ref->{source};
1210    }
1211    $sth->finish;
1212  }
1213
1214  for (keys %source) {
1215    for $ref (sort { sortsource($a, $b, $form) } @{ $source{$_} }) {
1216      push @{ $form->{CHK}{$_} }, $ref;
1217    }
1218  }
1219
1220  $dbh->disconnect;
1221
1222}
1223
1224
1225sub sortsource {
1226  my ($a, $b, $form) = @_;
1227
1228  if ($form->{sort} eq 'datepaid') {
1229    if ($form->{direction} eq 'DESC') {
1230      return $b->{datetonum} <=> $a->{datetonum};
1231    }
1232    return $a->{datetonum} <=> $b->{datetonum};
1233  }
1234
1235  if ($form->{sort} eq 'source') {
1236    if ($form->{direction} eq 'DESC') {
1237      return $b->{source} <=> $a->{source};
1238    }
1239    return $a->{source} <=> $b->{source};
1240  }
1241
1242  if ($form->{direction} eq 'DESC') {
1243    return $b->{$form->{sort}} cmp $a->{$form->{sort}};
1244  }
1245  return $a->{$form->{sort}} cmp $b->{$form->{sort}};
1246
1247}
1248
1249
1250sub void_payments {
1251  my ($self, $myconfig, $form) = @_;
1252
1253  # connect to database
1254  my $dbh = $form->dbconnect_noauto($myconfig);
1255
1256  my $query = qq|SELECT SUM(acc.amount), MAX(acc.id), acc.project_id,
1257                 acc.chart_id
1258                 FROM acc_trans acc
1259                 JOIN chart c ON (c.id = acc.chart_id)
1260                 WHERE fx_transaction = '0'
1261                 AND c.accno = ?
1262                 AND acc.trans_id = ?
1263                 AND acc.source = ?
1264                 GROUP BY acc.project_id, acc.chart_id|;
1265  my $sth = $dbh->prepare($query) || $form->dberror($query);
1266
1267  $query = qq|INSERT INTO acc_trans
1268              (trans_id, chart_id, amount, source, project_id, id)
1269              VALUES (?, ?, ?, ?, ?, ?)|;
1270  my $ath = $dbh->prepare($query) || $form->dberror($query);
1271
1272  my $arap = lc $form->{ARAP};
1273
1274  for (1 .. $form->{rowcount}) {
1275    if ($form->{"id_$_"}) {
1276      for my $trans_id (split /\n/, $form->{"id_$_"}) {
1277        $sth->execute($form->{"accno_$_"}, $trans_id, $form->{"source_$_"});
1278        my ($amount, $id, $project_id, $chart_id) = $sth->fetchrow_array;
1279        $sth->finish;
1280
1281        $amount *= -1;
1282        $id++;
1283        $ath->execute($trans_id, $chart_id, $amount, $form->{"source_$_"}, $project_id, $id);
1284        $ath->finish;
1285
1286        $form->update_balance($dbh,
1287                              $arap,
1288                              "paid",
1289                              qq|id = $trans_id|,
1290                              $amount);
1291      }
1292    }
1293  }
1294
1295  my $rc = $dbh->commit;
1296  $dbh->disconnect;
1297
1298  $rc;
1299
1300}
1301
1302
1303sub create_selects {
1304  my ($self, $myconfig, $form) = @_;
1305
1306  my ($query, $sth);
1307
1308  my $dbh = $form->dbconnect($myconfig);
1309
1310  $form->get_peripherals($dbh);
1311
1312  $form->all_languages($myconfig, $dbh);
1313
1314  $query = qq|SELECT id FROM chart WHERE accno = '$form->{"accno_1"}'|;
1315  ($chart_id) = $dbh->selectrow_array($query);
1316
1317  my %defaults = $form->get_defaults($dbh, \@{["chknumber_${chart_id}"]});
1318  $form->{source} = $defaults{"chknumber_${chart_id}"} + 1;
1319
1320  $dbh->disconnect;
1321
1322}
1323
1324
1325sub reissue_payment {
1326  my ($self, $myconfig, $form, $i) = @_;
1327
1328  my $dbh = $form->dbconnect_noauto($myconfig);
1329
1330  my %defaults = $form->get_defaults($dbh, \@{['precision']});
1331  $form->{precision} = $defaults{precision};
1332
1333  my $query;
1334  my $sth;
1335  my $tth;
1336  my $ath;
1337  my $zth;
1338  my $ref;
1339  my $trans_id;
1340
1341  my $rc = 0;
1342  my $arap = lc $form->{ARAP};
1343
1344  # retrieve name, address
1345  $query = qq|SELECT vc.*, ad.*, current_date AS datepaid
1346              FROM $form->{vc} vc
1347              JOIN address ad ON (ad.trans_id = vc.id)
1348              WHERE vc.id = $form->{"$form->{vc}_id"}|;
1349  $sth = $dbh->prepare($query);
1350  $sth->execute || $form->dberror($query);
1351  $ref = $sth->fetchrow_hashref(NAME_lc);
1352  for (qw(name address1 address2 city state zipcode country datepaid)) { $form->{$_} = $ref->{$_} }
1353  $sth->finish;
1354
1355  # invoices
1356  $query = qq|SELECT a.*, a.transdate AS invdate
1357              FROM $arap a
1358              WHERE a.id = ?|;
1359  $sth = $dbh->prepare($query);
1360
1361  $query = qq|SELECT id
1362              FROM chart
1363              WHERE accno = '$form->{"accno_$i"}'|;
1364  my ($chart_id) = $dbh->selectrow_array($query);
1365
1366  # update acc_trans for each trans_id
1367  $query = qq|UPDATE acc_trans SET
1368              source = '$form->{source}',
1369              transdate = '$form->{datepaid}'
1370              WHERE source = '$form->{"source_$i"}'
1371              AND fx_transaction = '0'
1372              AND trans_id = ?
1373              AND chart_id = $chart_id|;
1374  $tth = $dbh->prepare($query);
1375
1376  $query = qq|UPDATE $arap
1377              SET datepaid = '$form->{datepaid}'
1378              WHERE id = ?|;
1379  $ath = $dbh->prepare($query);
1380
1381  # amount of check for each invoice
1382  $query = qq|SELECT SUM(amount)
1383              FROM acc_trans
1384              WHERE fx_transaction = '0'
1385              AND trans_id = ?
1386              AND source = '$form->{"source_$i"}'
1387              AND chart_id = $chart_id|;
1388  $zth = $dbh->prepare($query);
1389
1390  $form->{amount} = 0;
1391  for (qw(invnumber invdescription invdate due paid)) { @{ $form->{$_} } = () }
1392
1393  for $trans_id (split /\n/, $form->{"id_$i"}) {
1394
1395    $zth->execute($trans_id);
1396    $paid = 0;
1397    while ($amount = $zth->fetchrow_array) {
1398      $form->{amount} += $amount;
1399      $paid += $amount;
1400    }
1401    $zth->finish;
1402
1403    $sth->execute($trans_id);
1404
1405    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1406      $ref->{due} = $form->format_amount($myconfig, $ref->{amount}, $form->{precision});
1407      $ref->{paid} = $form->format_amount($myconfig, $paid, $form->{precision});
1408      for (qw(invnumber invdescription invdate due paid)) { push @{ $form->{$_} }, $ref->{$_} }
1409    }
1410    $sth->finish;
1411
1412    # update source, datepaid
1413    $tth->execute($trans_id);
1414    $tth->finish;
1415
1416    # update datepaid
1417    $ath->execute($trans_id);
1418    $ath->finish;
1419  }
1420
1421  $form->{"source_$i"} = $form->{source};
1422
1423  my $chkno = ($form->{ARAP} eq 'AP') ? qq|chknumber_$form->{"accno_$i"}| : qq|receipt_$form->{"accno_$i"}|;
1424  # record last check number
1425  $form->update_defaults($myconfig, $chkno, $dbh, $form->{source});
1426
1427  my $rc = $dbh->commit;
1428  $dbh->disconnect;
1429
1430  $rc;
1431
1432}
1433
1434
1435
14361;
1437
1438