Weitere Abfragen umformatiert.
[kivitendo-erp.git] / SL / AR.pm
1 #=====================================================================
2 # LX-Office ERP
3 # Copyright (C) 2004
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
6 #
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 2001
10 #
11 #  Author: Dieter Simader
12 #   Email: dsimader@sql-ledger.org
13 #     Web: http://www.sql-ledger.org
14 #
15 #  Contributors:
16 #
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
21 #
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
30 #
31 # Accounts Receivable module backend routines
32 #
33 #======================================================================
34
35 package AR;
36
37 use Data::Dumper;
38
39 sub post_transaction {
40   $main::lxdebug->enter_sub();
41
42   my ($self, $myconfig, $form) = @_;
43
44   my ($null, $taxrate, $amount, $tax, $diff);
45   my $exchangerate = 0;
46   my $i;
47
48   my $dbh = $form->dbconnect_noauto($myconfig);
49
50   if ($form->{currency} eq $form->{defaultcurrency}) {
51     $form->{exchangerate} = 1;
52   } else {
53     $exchangerate =
54       $form->check_exchangerate($myconfig, $form->{currency},
55                                 $form->{transdate}, 'buy');
56   }
57   for $i (1 .. $form->{rowcount}) {
58     $form->{AR_amounts}{"amount_$i"} =
59       (split(/--/, $form->{"AR_amount_$i"}))[0];
60   }
61   ($form->{AR_amounts}{receivables}) = split(/--/, $form->{ARselected});
62   ($form->{AR}{receivables})         = split(/--/, $form->{ARselected});
63
64   $form->{exchangerate} =
65     ($exchangerate)
66     ? $exchangerate
67     : $form->parse_amount($myconfig, $form->{exchangerate});
68
69   for $i (1 .. $form->{rowcount}) {
70
71     $form->{"amount_$i"} =
72       $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"})
73                             * $form->{exchangerate},
74                           2);
75
76     $form->{netamount} += $form->{"amount_$i"};
77
78     # parse tax_$i for later
79     $form->{"tax_$i"} = $form->parse_amount($myconfig, $form->{"tax_$i"});
80   }
81
82   # this is for ar
83
84   $form->{amount} = $form->{netamount};
85
86   $form->{tax}       = 0;
87   $form->{netamount} = 0;
88   $form->{total_tax} = 0;
89
90   # taxincluded doesn't make sense if there is no amount
91
92   $form->{taxincluded} = 0 if ($form->{amount} == 0);
93   for $i (1 .. $form->{rowcount}) {
94     ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
95
96     $query = qq|SELECT c.accno, t.taxkey, t.rate
97             FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
98             WHERE t.id=$form->{"tax_id_$i"}
99             ORDER BY c.accno|;
100
101     $sth = $dbh->prepare($query);
102     $sth->execute || $form->dberror($query);
103     ($form->{AR_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) =
104       $sth->fetchrow_array;
105     $form->{AR_amounts}{"tax_$i"}{taxkey}    = $form->{"taxkey_$i"};
106     $form->{AR_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"};
107
108     $sth->finish;
109     if ($form->{taxincluded} *= 1) {
110       if (!$form->{"korrektur_$i"}) {
111       $tax =
112         $form->{"amount_$i"} -
113         ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1));
114       } else {
115         $tax = $form->{"tax_$i"};
116       }
117       $amount = $form->{"amount_$i"} - $tax;
118       $form->{"amount_$i"} = $form->round_amount($amount, 2);
119       $diff += $amount - $form->{"amount_$i"};
120       $form->{"tax_$i"} = $form->round_amount($tax, 2);
121       $form->{netamount} += $form->{"amount_$i"};
122     } else {
123       if (!$form->{"korrektur_$i"}) {
124         $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"};
125       }
126       $form->{"tax_$i"} =
127         $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2);
128       $form->{netamount} += $form->{"amount_$i"};
129     }
130     
131     $form->{total_tax} += $form->{"tax_$i"};
132   }
133
134   # adjust paidaccounts if there is no date in the last row
135   $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
136   $form->{paid} = 0;
137
138   # add payments
139   for $i (1 .. $form->{paidaccounts}) {
140     $form->{"paid_$i"} =
141       $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
142                           2);
143
144     $form->{paid} += $form->{"paid_$i"};
145     $form->{datepaid} = $form->{"datepaid_$i"};
146
147   }
148
149   $form->{amount} = $form->{netamount} + $form->{total_tax};
150   $form->{paid}   =
151     $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
152
153   my ($query, $sth, $null);
154
155   ($null, $form->{employee_id}) = split /--/, $form->{employee};
156   unless ($form->{employee_id}) {
157     $form->get_employee($dbh);
158   }
159
160   # if we have an id delete old records
161   if ($form->{id}) {
162
163     # delete detail records
164     $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
165     $dbh->do($query) || $form->dberror($query);
166
167   } else {
168     my $uid = rand() . time;
169
170     $uid .= $form->{login};
171
172     $uid = substr($uid, 2, 75);
173
174     $query = qq|INSERT INTO ar (invnumber, employee_id)
175                 VALUES ('$uid', $form->{employee_id})|;
176     $dbh->do($query) || $form->dberror($query);
177
178     $query = qq|SELECT a.id FROM ar a
179                 WHERE a.invnumber = '$uid'|;
180     $sth = $dbh->prepare($query);
181     $sth->execute || $form->dberror($query);
182
183     ($form->{id}) = $sth->fetchrow_array;
184     $sth->finish;
185
186   }
187
188   # update department
189   ($null, $form->{department_id}) = split(/--/, $form->{department});
190   $form->{department_id} *= 1;
191
192   # escape '
193   map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
194
195   # record last payment date in ar table
196   $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
197   my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
198
199   $query = qq|UPDATE ar set
200               invnumber = '$form->{invnumber}',
201               ordnumber = '$form->{ordnumber}',
202               transdate = '$form->{transdate}',
203               customer_id = $form->{customer_id},
204               taxincluded = '$form->{taxincluded}',
205               amount = $form->{amount},
206               duedate = '$form->{duedate}',
207               paid = $form->{paid},
208               datepaid = $datepaid,
209               netamount = $form->{netamount},
210               curr = '$form->{currency}',
211               notes = '$form->{notes}',
212               department_id = $form->{department_id},
213               employee_id = $form->{employee_id}
214               WHERE id = $form->{id}|;
215   $dbh->do($query) || $form->dberror($query);
216
217   # amount for AR account
218   $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
219
220   # update exchangerate
221   if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
222     $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate},
223                                $form->{exchangerate}, 0);
224   }
225
226   # add individual transactions for AR, amount and taxes
227   for $i (1 .. $form->{rowcount}) {
228     if ($form->{"amount_$i"} != 0) {
229       $project_id = 'NULL';
230       if ("amount_$i" =~ /amount_/) {
231         if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) {
232           $project_id = $form->{"project_id_$i"};
233         }
234       }
235       if ("amount_$i" =~ /amount/) {
236         $taxkey = $form->{AR_amounts}{"amount_$i"}{taxkey};
237       }
238
239       # insert detail records in acc_trans
240       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
241                                          project_id, taxkey)
242                   VALUES ($form->{id}, (SELECT c.id FROM chart c
243                                         WHERE c.accno = '$form->{AR_amounts}{"amount_$i"}'),
244                   $form->{"amount_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
245       $dbh->do($query) || $form->dberror($query);
246       if ($form->{"tax_$i"} != 0) {
247
248         # insert detail records in acc_trans
249         $query =
250           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
251                                           project_id, taxkey)
252                     VALUES ($form->{id}, (SELECT c.id FROM chart c
253                                           WHERE c.accno = '$form->{AR_amounts}{"tax_$i"}'),
254                     $form->{"tax_$i"}, '$form->{transdate}', $project_id, '$taxkey')|;
255         $dbh->do($query) || $form->dberror($query);
256       }
257     }
258   }
259
260   # add recievables
261   $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
262                                       project_id)
263               VALUES ($form->{id}, (SELECT c.id FROM chart c
264                                     WHERE c.accno = '$form->{AR_amounts}{receivables}'),
265               $form->{receivables}, '$form->{transdate}', $project_id)|;
266   $dbh->do($query) || $form->dberror($query);
267
268   # add paid transactions
269   for my $i (1 .. $form->{paidaccounts}) {
270     if ($form->{"paid_$i"} != 0) {
271
272       $form->{"AR_paid_$i"} =~ s/\"//g;
273       ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
274       $form->{"datepaid_$i"} = $form->{transdate}
275         unless ($form->{"datepaid_$i"});
276
277       $exchangerate = 0;
278       if ($form->{currency} eq $form->{defaultcurrency}) {
279         $form->{"exchangerate_$i"} = 1;
280       } else {
281         $exchangerate =
282           $form->check_exchangerate($myconfig, $form->{currency},
283                                     $form->{"datepaid_$i"}, 'buy');
284
285         $form->{"exchangerate_$i"} =
286           ($exchangerate)
287           ? $exchangerate
288           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
289       }
290
291       # if there is no amount and invtotal is zero there is no exchangerate
292       if ($form->{amount} == 0 && $form->{netamount} == 0) {
293         $form->{exchangerate} = $form->{"exchangerate_$i"};
294       }
295
296       # receivables amount
297       $amount =
298         $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
299
300       if ($form->{receivables} != 0) {
301
302         # add receivable
303         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
304                     transdate)
305                     VALUES ($form->{id},
306                            (SELECT c.id FROM chart c
307                             WHERE c.accno = '$form->{AR}{receivables}'),
308                     $amount, '$form->{"datepaid_$i"}')|;
309         $dbh->do($query) || $form->dberror($query);
310       }
311       $form->{receivables} = $amount;
312
313       $form->{"memo_$i"} =~ s/\'/\'\'/g;
314
315       if ($form->{"paid_$i"} != 0) {
316
317         # add payment
318         $amount = $form->{"paid_$i"} * -1;
319         $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
320                     transdate, source, memo)
321                     VALUES ($form->{id},
322                            (SELECT c.id FROM chart c
323                             WHERE c.accno = '$form->{AR}{"paid_$i"}'),
324                     $amount, '$form->{"datepaid_$i"}',
325                     '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
326         $dbh->do($query) || $form->dberror($query);
327
328         # exchangerate difference for payment
329         $amount =
330           $form->round_amount(
331                     $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1,
332                     2);
333
334         if ($amount != 0) {
335           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
336                       transdate, fx_transaction, cleared)
337                       VALUES ($form->{id},
338                              (SELECT c.id FROM chart c
339                               WHERE c.accno = '$form->{AR}{"paid_$i"}'),
340                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
341           $dbh->do($query) || $form->dberror($query);
342         }
343
344         # exchangerate gain/loss
345         $amount =
346           $form->round_amount(
347                    $form->{"paid_$i"} *
348                      ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1,
349                    2);
350
351         if ($amount != 0) {
352           $accno =
353             ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
354           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
355                       transdate, fx_transaction, cleared)
356                       VALUES ($form->{id}, (SELECT c.id FROM chart c
357                                             WHERE c.accno = '$accno'),
358                       $amount, '$form->{"datepaid_$i"}', '1', '0')|;
359           $dbh->do($query) || $form->dberror($query);
360         }
361       }
362
363       # update exchangerate record
364       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
365         $form->update_exchangerate($dbh, $form->{currency},
366                                    $form->{"datepaid_$i"},
367                                    $form->{"exchangerate_$i"}, 0);
368       }
369     }
370   }
371
372   my $rc = $dbh->commit;
373   $dbh->disconnect;
374
375   $main::lxdebug->leave_sub();
376
377   return $rc;
378 }
379
380 sub post_payment {
381   $main::lxdebug->enter_sub();
382
383   my ($self, $myconfig, $form, $locale) = @_;
384
385   # connect to database, turn off autocommit
386   my $dbh = $form->dbconnect_noauto($myconfig);
387
388   $form->{datepaid} = $form->{invdate};
389
390   # total payments, don't move we need it here
391   for my $i (1 .. $form->{paidaccounts}) {
392     if ($form->{type} eq "credit_note") {
393       $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
394     } else {
395       $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
396     } 
397     $form->{paid} += $form->{"paid_$i"};
398     $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
399   }
400
401   $form->{exchangerate} =
402       $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
403                               "buy");
404
405   # record payments and offsetting AR
406   for my $i (1 .. $form->{paidaccounts}) {
407
408     if ($form->{"paid_$i"} != 0) {
409       my ($accno) = split /--/, $form->{"AR_paid_$i"};
410       $form->{"datepaid_$i"} = $form->{invdate}
411         unless ($form->{"datepaid_$i"});
412       $form->{datepaid} = $form->{"datepaid_$i"};
413
414       $exchangerate = 0;
415       if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
416         $form->{"exchangerate_$i"} = 1;
417       } else {
418         $exchangerate =
419           $form->check_exchangerate($myconfig, $form->{currency},
420                                     $form->{"datepaid_$i"}, 'buy');
421
422         $form->{"exchangerate_$i"} =
423           ($exchangerate)
424           ? $exchangerate
425           : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
426       }
427
428       # record AR
429       $amount =
430         $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
431                             2);
432
433
434       $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
435                                       WHERE c.accno = '$form->{AR}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
436       $dbh->do($query) || $form->dberror($query);
437
438       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
439                   transdate)
440                   VALUES ($form->{id}, (SELECT c.id FROM chart c
441                                       WHERE c.accno = '$form->{AR}'),
442                   $amount, '$form->{"datepaid_$i"}')|;
443       $dbh->do($query) || $form->dberror($query);
444
445
446       # record payment
447       $form->{"paid_$i"} *= -1;
448
449       $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
450                                       WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
451       $dbh->do($query) || $form->dberror($query);
452
453       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
454                   source, memo)
455                   VALUES ($form->{id}, (SELECT c.id FROM chart c
456                                       WHERE c.accno = '$accno'),
457                   $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
458                   '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
459       $dbh->do($query) || $form->dberror($query);
460
461
462       # gain/loss
463       $amount =
464         $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
465         $form->{"exchangerate_$i"};
466       if ($amount > 0) {
467         $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
468           $amount;
469       } else {
470         $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
471           $amount;
472       }
473
474       $diff = 0;
475
476       # update exchange rate
477       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
478         $form->update_exchangerate($dbh, $form->{currency},
479                                    $form->{"datepaid_$i"},
480                                    $form->{"exchangerate_$i"}, 0);
481       }
482     }
483   }
484
485   # record exchange rate differences and gains/losses
486   foreach my $accno (keys %{ $form->{fx} }) {
487     foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
488       if (
489           ($form->{fx}{$accno}{$transdate} =
490            $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
491           ) != 0
492         ) {
493         $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
494                                         WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
495         $dbh->do($query) || $form->dberror($query);
496         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
497                     transdate, cleared, fx_transaction)
498                     VALUES ($form->{id},
499                            (SELECT c.id FROM chart c
500                             WHERE c.accno = '$accno'),
501                     $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
502         $dbh->do($query) || $form->dberror($query);
503       }
504     }
505   }
506   my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
507
508   # save AR record
509   my $query = qq|UPDATE ar set
510               paid = $form->{paid},
511               datepaid = $datepaid
512               WHERE id=$form->{id}|;
513
514   $dbh->do($query) || $form->dberror($query);
515
516   my $rc = $dbh->commit;
517   $dbh->disconnect;
518
519   $main::lxdebug->leave_sub();
520
521   return $rc;
522 }
523
524 sub delete_transaction {
525   $main::lxdebug->enter_sub();
526
527   my ($self, $myconfig, $form) = @_;
528
529   # connect to database, turn AutoCommit off
530   my $dbh = $form->dbconnect_noauto($myconfig);
531
532   my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
533   $dbh->do($query) || $form->dberror($query);
534
535   $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
536   $dbh->do($query) || $form->dberror($query);
537
538   # commit
539   my $rc = $dbh->commit;
540   $dbh->disconnect;
541
542   $main::lxdebug->leave_sub();
543
544   return $rc;
545 }
546
547 sub ar_transactions {
548   $main::lxdebug->enter_sub();
549
550   my ($self, $myconfig, $form) = @_;
551
552   # connect to database
553   my $dbh = $form->dbconnect($myconfig);
554
555   my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
556                  a.duedate, a.netamount, a.amount, a.paid, c.name,
557                  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia,
558                  a.shippingpoint, a.storno,
559                  e.name AS employee
560                  FROM ar a
561               JOIN customer c ON (a.customer_id = c.id)
562               LEFT JOIN employee e ON (a.employee_id = e.id)|;
563
564   my $where = "1 = 1";
565   if ($form->{customer_id}) {
566     $where .= " AND a.customer_id = $form->{customer_id}";
567   } else {
568     if ($form->{customer}) {
569       my $customer = $form->like(lc $form->{customer});
570       $where .= " AND lower(c.name) LIKE '$customer'";
571     }
572   }
573   if ($form->{department}) {
574     my ($null, $department_id) = split /--/, $form->{department};
575     $where .= " AND a.department_id = $department_id";
576   }
577   if ($form->{invnumber}) {
578     my $invnumber = $form->like(lc $form->{invnumber});
579     $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
580   }
581   if ($form->{ordnumber}) {
582     my $ordnumber = $form->like(lc $form->{ordnumber});
583     $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
584   }
585   if ($form->{notes}) {
586     my $notes = $form->like(lc $form->{notes});
587     $where .= " AND lower(a.notes) LIKE '$notes'";
588   }
589
590   $where .= " AND a.transdate >= '$form->{transdatefrom}'"
591     if $form->{transdatefrom};
592   $where .= " AND a.transdate <= '$form->{transdateto}'"
593     if $form->{transdateto};
594   if ($form->{open} || $form->{closed}) {
595     unless ($form->{open} && $form->{closed}) {
596       $where .= " AND a.amount <> a.paid" if ($form->{open});
597       $where .= " AND a.amount = a.paid"  if ($form->{closed});
598     }
599   }
600
601   my @a = (transdate, invnumber, name);
602   push @a, "employee" if $form->{l_employee};
603   my $sortorder = join ', ', $form->sort_columns(@a);
604   $sortorder = $form->{sort} if $form->{sort};
605
606   $query .= "WHERE $where
607              ORDER by $sortorder";
608
609   my $sth = $dbh->prepare($query);
610   $sth->execute || $form->dberror($query);
611
612   while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
613     push @{ $form->{AR} }, $ar;
614   }
615
616   $sth->finish;
617   $dbh->disconnect;
618
619   $main::lxdebug->leave_sub();
620 }
621
622 sub get_transdate {
623   $main::lxdebug->enter_sub();
624
625   my ($self, $myconfig, $form) = @_;
626
627   # connect to database
628   my $dbh = $form->dbconnect($myconfig);
629
630   my $query =
631     "SELECT COALESCE(" .
632     "  (SELECT transdate FROM ar WHERE id = " .
633     "    (SELECT MAX(id) FROM ar) LIMIT 1), " .
634     "  current_date)";
635   ($form->{transdate}) = $dbh->selectrow_array($query);
636
637   $dbh->disconnect;
638
639   $main::lxdebug->leave_sub();
640 }
641
642 1;
643