1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
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.
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 #======================================================================
31 # Accounts Receivable module backend routines
33 #======================================================================
40 sub post_transaction {
41 $main::lxdebug->enter_sub();
43 my ($self, $myconfig, $form) = @_;
45 my ($null, $taxrate, $amount, $tax, $diff);
51 my $dbh = $form->dbconnect_noauto($myconfig);
53 if ($form->{currency} eq $form->{defaultcurrency}) {
54 $form->{exchangerate} = 1;
57 $form->check_exchangerate($myconfig, $form->{currency},
58 $form->{transdate}, 'buy');
60 for $i (1 .. $form->{rowcount}) {
61 $form->{AR_amounts}{"amount_$i"} =
62 (split(/--/, $form->{"AR_amount_$i"}))[0];
64 ($form->{AR_amounts}{receivables}) = split(/--/, $form->{ARselected});
65 ($form->{AR}{receivables}) = split(/--/, $form->{ARselected});
67 $form->{exchangerate} =
70 : $form->parse_amount($myconfig, $form->{exchangerate});
72 for $i (1 .. $form->{rowcount}) {
74 $form->{"amount_$i"} =
75 $form->round_amount($form->parse_amount($myconfig, $form->{"amount_$i"})
76 * $form->{exchangerate},
79 $form->{netamount} += $form->{"amount_$i"};
81 # parse tax_$i for later
82 $form->{"tax_$i"} = $form->parse_amount($myconfig, $form->{"tax_$i"});
87 $form->{amount} = $form->{netamount};
90 $form->{netamount} = 0;
91 $form->{total_tax} = 0;
93 # taxincluded doesn't make sense if there is no amount
95 $form->{taxincluded} = 0 if ($form->{amount} == 0);
96 for $i (1 .. $form->{rowcount}) {
97 ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
99 $query = qq|SELECT c.accno, t.taxkey, t.rate
100 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
101 WHERE t.id=$form->{"tax_id_$i"}
104 $sth = $dbh->prepare($query);
105 $sth->execute || $form->dberror($query);
106 ($form->{AR_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) =
107 $sth->fetchrow_array;
108 $form->{AR_amounts}{"tax_$i"}{taxkey} = $form->{"taxkey_$i"};
109 $form->{AR_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"};
112 if ($form->{taxincluded} *= 1) {
113 if (!$form->{"korrektur_$i"}) {
115 $form->{"amount_$i"} -
116 ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1));
118 $tax = $form->{"tax_$i"};
120 $amount = $form->{"amount_$i"} - $tax;
121 $form->{"amount_$i"} = $form->round_amount($amount, 2);
122 $diff += $amount - $form->{"amount_$i"};
123 $form->{"tax_$i"} = $form->round_amount($tax, 2);
124 $form->{netamount} += $form->{"amount_$i"};
126 if (!$form->{"korrektur_$i"}) {
127 $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"};
130 $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2);
131 $form->{netamount} += $form->{"amount_$i"};
134 $form->{total_tax} += $form->{"tax_$i"};
137 # adjust paidaccounts if there is no date in the last row
138 $form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
142 for $i (1 .. $form->{paidaccounts}) {
144 $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
147 $form->{paid} += $form->{"paid_$i"};
148 $form->{datepaid} = $form->{"datepaid_$i"};
152 $form->{amount} = $form->{netamount} + $form->{total_tax};
154 $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
156 my ($query, $sth, $null);
158 ($null, $form->{employee_id}) = split /--/, $form->{employee};
159 unless ($form->{employee_id}) {
160 $form->get_employee($dbh);
163 # if we have an id delete old records
166 # delete detail records
167 $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
168 $dbh->do($query) || $form->dberror($query);
171 my $uid = rand() . time;
173 $uid .= $form->{login};
175 $uid = substr($uid, 2, 75);
177 $query = qq|INSERT INTO ar (invnumber, employee_id)
178 VALUES ('$uid', $form->{employee_id})|;
179 $dbh->do($query) || $form->dberror($query);
181 $query = qq|SELECT a.id FROM ar a
182 WHERE a.invnumber = '$uid'|;
183 $sth = $dbh->prepare($query);
184 $sth->execute || $form->dberror($query);
186 ($form->{id}) = $sth->fetchrow_array;
192 ($null, $form->{department_id}) = split(/--/, $form->{department});
193 $form->{department_id} *= 1;
196 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes);
198 # record last payment date in ar table
199 $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
200 my $datepaid = ($form->{paid} != 0) ? qq|'$form->{datepaid}'| : 'NULL';
202 $query = qq|UPDATE ar set
203 invnumber = '$form->{invnumber}',
204 ordnumber = '$form->{ordnumber}',
205 transdate = '$form->{transdate}',
206 customer_id = $form->{customer_id},
207 taxincluded = '$form->{taxincluded}',
208 amount = $form->{amount},
209 duedate = '$form->{duedate}',
210 paid = $form->{paid},
211 datepaid = $datepaid,
212 netamount = $form->{netamount},
213 curr = '$form->{currency}',
214 notes = '$form->{notes}',
215 department_id = $form->{department_id},
216 employee_id = $form->{employee_id}
217 WHERE id = $form->{id}|;
218 $dbh->do($query) || $form->dberror($query);
220 # amount for AR account
221 $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
223 # update exchangerate
224 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
225 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate},
226 $form->{exchangerate}, 0);
229 # add individual transactions for AR, amount and taxes
230 for $i (1 .. $form->{rowcount}) {
231 if ($form->{"amount_$i"} != 0) {
232 my $project_id = undef;
233 $project_id = conv_i($form->{"project_id_$i"});
234 $taxkey = $form->{AR_amounts}{"amount_$i"}{taxkey};
236 @values = ($project_id);
237 # insert detail records in acc_trans
238 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
240 VALUES ($form->{id}, (SELECT c.id FROM chart c
241 WHERE c.accno = '$form->{AR_amounts}{"amount_$i"}'),
242 $form->{"amount_$i"}, '$form->{transdate}', ?, '$taxkey')|;
243 do_query($form, $dbh, $query, @values);
244 if ($form->{"tax_$i"} != 0) {
246 @values = ($project_id);
247 # insert detail records in acc_trans
249 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
251 VALUES ($form->{id}, (SELECT c.id FROM chart c
252 WHERE c.accno = '$form->{AR_amounts}{"tax_$i"}'),
253 $form->{"tax_$i"}, '$form->{transdate}', ?, '$taxkey')|;
254 do_query($form, $dbh, $query, @values);
260 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate)
261 VALUES ($form->{id}, (SELECT c.id FROM chart c
262 WHERE c.accno = '$form->{AR_amounts}{receivables}'),
263 $form->{receivables}, '$form->{transdate}')|;
264 $dbh->do($query) || $form->dberror($query);
266 # add paid transactions
267 for my $i (1 .. $form->{paidaccounts}) {
268 if ($form->{"paid_$i"} != 0) {
270 $form->{"AR_paid_$i"} =~ s/\"//g;
271 ($form->{AR}{"paid_$i"}) = split(/--/, $form->{"AR_paid_$i"});
272 $form->{"datepaid_$i"} = $form->{transdate}
273 unless ($form->{"datepaid_$i"});
276 if ($form->{currency} eq $form->{defaultcurrency}) {
277 $form->{"exchangerate_$i"} = 1;
280 $form->check_exchangerate($myconfig, $form->{currency},
281 $form->{"datepaid_$i"}, 'buy');
283 $form->{"exchangerate_$i"} =
286 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
289 # if there is no amount and invtotal is zero there is no exchangerate
290 if ($form->{amount} == 0 && $form->{netamount} == 0) {
291 $form->{exchangerate} = $form->{"exchangerate_$i"};
296 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
298 if ($form->{receivables} != 0) {
301 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
304 (SELECT c.id FROM chart c
305 WHERE c.accno = '$form->{AR}{receivables}'),
306 $amount, '$form->{"datepaid_$i"}')|;
307 $dbh->do($query) || $form->dberror($query);
309 $form->{receivables} = $amount;
311 $form->{"memo_$i"} =~ s/\'/\'\'/g;
313 if ($form->{"paid_$i"} != 0) {
316 $amount = $form->{"paid_$i"} * -1;
317 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
318 transdate, source, memo)
320 (SELECT c.id FROM chart c
321 WHERE c.accno = '$form->{AR}{"paid_$i"}'),
322 $amount, '$form->{"datepaid_$i"}',
323 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
324 $dbh->do($query) || $form->dberror($query);
326 # exchangerate difference for payment
329 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1,
333 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
334 transdate, fx_transaction, cleared)
336 (SELECT c.id FROM chart c
337 WHERE c.accno = '$form->{AR}{"paid_$i"}'),
338 $amount, '$form->{"datepaid_$i"}', '1', '0')|;
339 $dbh->do($query) || $form->dberror($query);
342 # exchangerate gain/loss
346 ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1,
351 ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
352 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
353 transdate, fx_transaction, cleared)
354 VALUES ($form->{id}, (SELECT c.id FROM chart c
355 WHERE c.accno = '$accno'),
356 $amount, '$form->{"datepaid_$i"}', '1', '0')|;
357 $dbh->do($query) || $form->dberror($query);
361 # update exchangerate record
362 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
363 $form->update_exchangerate($dbh, $form->{currency},
364 $form->{"datepaid_$i"},
365 $form->{"exchangerate_$i"}, 0);
370 my $rc = $dbh->commit;
373 $main::lxdebug->leave_sub();
379 $main::lxdebug->enter_sub();
381 my ($self, $myconfig, $form, $locale) = @_;
383 # connect to database, turn off autocommit
384 my $dbh = $form->dbconnect_noauto($myconfig);
386 $form->{datepaid} = $form->{invdate};
388 # total payments, don't move we need it here
389 for my $i (1 .. $form->{paidaccounts}) {
390 if ($form->{type} eq "credit_note") {
391 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
393 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
395 $form->{paid} += $form->{"paid_$i"};
396 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
399 $form->{exchangerate} =
400 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
403 # record payments and offsetting AR
404 for my $i (1 .. $form->{paidaccounts}) {
406 if ($form->{"paid_$i"} != 0) {
407 my ($accno) = split /--/, $form->{"AR_paid_$i"};
408 $form->{"datepaid_$i"} = $form->{invdate}
409 unless ($form->{"datepaid_$i"});
410 $form->{datepaid} = $form->{"datepaid_$i"};
413 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
414 $form->{"exchangerate_$i"} = 1;
417 $form->check_exchangerate($myconfig, $form->{currency},
418 $form->{"datepaid_$i"}, 'buy');
420 $form->{"exchangerate_$i"} =
423 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
428 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
432 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
433 WHERE c.accno = '$form->{AR}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
434 $dbh->do($query) || $form->dberror($query);
436 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
438 VALUES ($form->{id}, (SELECT c.id FROM chart c
439 WHERE c.accno = '$form->{AR}'),
440 $amount, '$form->{"datepaid_$i"}')|;
441 $dbh->do($query) || $form->dberror($query);
445 $form->{"paid_$i"} *= -1;
447 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
448 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
449 $dbh->do($query) || $form->dberror($query);
451 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
453 VALUES ($form->{id}, (SELECT c.id FROM chart c
454 WHERE c.accno = '$accno'),
455 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
456 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
457 $dbh->do($query) || $form->dberror($query);
462 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
463 $form->{"exchangerate_$i"};
465 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
468 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
474 # update exchange rate
475 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
476 $form->update_exchangerate($dbh, $form->{currency},
477 $form->{"datepaid_$i"},
478 $form->{"exchangerate_$i"}, 0);
483 # record exchange rate differences and gains/losses
484 foreach my $accno (keys %{ $form->{fx} }) {
485 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
487 ($form->{fx}{$accno}{$transdate} =
488 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
491 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
492 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
493 $dbh->do($query) || $form->dberror($query);
494 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
495 transdate, cleared, fx_transaction)
497 (SELECT c.id FROM chart c
498 WHERE c.accno = '$accno'),
499 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
500 $dbh->do($query) || $form->dberror($query);
504 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
507 my $query = qq|UPDATE ar set
508 paid = $form->{paid},
510 WHERE id=$form->{id}|;
512 $dbh->do($query) || $form->dberror($query);
514 my $rc = $dbh->commit;
517 $main::lxdebug->leave_sub();
522 sub delete_transaction {
523 $main::lxdebug->enter_sub();
525 my ($self, $myconfig, $form) = @_;
527 # connect to database, turn AutoCommit off
528 my $dbh = $form->dbconnect_noauto($myconfig);
530 my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
531 $dbh->do($query) || $form->dberror($query);
533 $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
534 $dbh->do($query) || $form->dberror($query);
537 my $rc = $dbh->commit;
540 $main::lxdebug->leave_sub();
545 sub ar_transactions {
546 $main::lxdebug->enter_sub();
548 my ($self, $myconfig, $form) = @_;
550 # connect to database
551 my $dbh = $form->dbconnect($myconfig);
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, a.globalproject_id,
559 pr.projectnumber AS globalprojectnumber,
562 JOIN customer c ON (a.customer_id = c.id)
563 LEFT JOIN employee e ON (a.employee_id = e.id)
564 LEFT JOIN project pr ON a.globalproject_id = pr.id|;
567 if ($form->{customer_id}) {
568 $where .= " AND a.customer_id = $form->{customer_id}";
570 if ($form->{customer}) {
571 my $customer = $form->like(lc $form->{customer});
572 $where .= " AND lower(c.name) LIKE '$customer'";
575 if ($form->{department}) {
576 my ($null, $department_id) = split /--/, $form->{department};
577 $where .= " AND a.department_id = $department_id";
579 if ($form->{invnumber}) {
580 my $invnumber = $form->like(lc $form->{invnumber});
581 $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
583 if ($form->{ordnumber}) {
584 my $ordnumber = $form->like(lc $form->{ordnumber});
585 $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
587 if ($form->{notes}) {
588 my $notes = $form->like(lc $form->{notes});
589 $where .= " AND lower(a.notes) LIKE '$notes'";
591 if ($form->{"project_id"}) {
593 qq|AND ((a.globalproject_id = ?) OR EXISTS | .
594 qq| (SELECT * FROM invoice i | .
595 qq| WHERE i.project_id = ? AND i.trans_id = a.id))|;
596 push(@values, $form->{"project_id"}, $form->{"project_id"});
599 $where .= " AND a.transdate >= '$form->{transdatefrom}'"
600 if $form->{transdatefrom};
601 $where .= " AND a.transdate <= '$form->{transdateto}'"
602 if $form->{transdateto};
603 if ($form->{open} || $form->{closed}) {
604 unless ($form->{open} && $form->{closed}) {
605 $where .= " AND a.amount <> a.paid" if ($form->{open});
606 $where .= " AND a.amount = a.paid" if ($form->{closed});
610 my @a = (transdate, invnumber, name);
611 push @a, "employee" if $form->{l_employee};
612 my $sortorder = join ', ', $form->sort_columns(@a);
613 $sortorder = $form->{sort} if $form->{sort};
615 $query .= " WHERE $where
616 ORDER by $sortorder";
618 my $sth = $dbh->prepare($query);
619 $sth->execute(@values) ||
620 $form->dberror($query . " (" . join(", ", @values) . ")");
622 while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
623 push @{ $form->{AR} }, $ar;
629 $main::lxdebug->leave_sub();
633 $main::lxdebug->enter_sub();
635 my ($self, $myconfig, $form) = @_;
637 # connect to database
638 my $dbh = $form->dbconnect($myconfig);
642 " (SELECT transdate FROM ar WHERE id = " .
643 " (SELECT MAX(id) FROM ar) LIMIT 1), " .
645 ($form->{transdate}) = $dbh->selectrow_array($query);
649 $main::lxdebug->leave_sub();