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,
302 transdate, project_id)
304 (SELECT c.id FROM chart c
305 WHERE c.accno = '$form->{AR}{receivables}'),
306 $amount, '$form->{"datepaid_$i"}', ?)|;
307 do_query($form, $dbh, $query, $project_id);
309 $form->{receivables} = $amount;
311 $form->{"memo_$i"} =~ s/\'/\'\'/g;
313 if ($form->{"paid_$i"} != 0) {
314 my $project_id = conv_i($form->{"paid_project_id_$i"});
316 $amount = $form->{"paid_$i"} * -1;
317 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
318 transdate, source, memo, project_id)
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 do_query($form, $dbh, $query, $project_id);
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, project_id)
336 (SELECT c.id FROM chart c
337 WHERE c.accno = '$form->{AR}{"paid_$i"}'),
338 $amount, '$form->{"datepaid_$i"}', '1', '0', ?)|;
339 do_query($form, $dbh, $query, $project_id);
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, project_id)
354 VALUES ($form->{id}, (SELECT c.id FROM chart c
355 WHERE c.accno = '$accno'),
356 $amount, '$form->{"datepaid_$i"}', '1', '0', ?)|;
357 do_query($form, $dbh, $query, $project_id);
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->{transdate};
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->{transdate},
403 my ($accno_ar) = split(/--/, $form->{ARselected});
405 # record payments and offsetting AR
406 for my $i (1 .. $form->{paidaccounts}) {
408 if ($form->{"paid_$i"} != 0) {
409 my $project_id = conv_i($form->{"paid_project_id_$i"});
411 my ($accno) = split /--/, $form->{"AR_paid_$i"};
412 $form->{"datepaid_$i"} = $form->{transdate}
413 unless ($form->{"datepaid_$i"});
414 $form->{datepaid} = $form->{"datepaid_$i"};
417 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
418 $form->{"exchangerate_$i"} = 1;
421 $form->check_exchangerate($myconfig, $form->{currency},
422 $form->{"datepaid_$i"}, 'buy');
424 $form->{"exchangerate_$i"} =
427 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
432 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
436 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
437 WHERE c.accno = '$accno_ar') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
438 $dbh->do($query) || $form->dberror($query);
440 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
441 transdate, project_id)
442 VALUES ($form->{id}, (SELECT c.id FROM chart c
443 WHERE c.accno = '$accno_ar'),
444 $amount, '$form->{"datepaid_$i"}', ?)|;
445 do_query($form, $dbh, $query, $project_id);
449 $form->{"paid_$i"} *= -1;
451 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
452 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
453 $dbh->do($query) || $form->dberror($query);
455 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
456 source, memo, project_id)
457 VALUES ($form->{id}, (SELECT c.id FROM chart c
458 WHERE c.accno = '$accno'),
459 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
460 '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
461 do_query($form, $dbh, $query, $project_id);
466 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
467 $form->{"exchangerate_$i"};
469 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
472 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
478 # update exchange rate
479 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
480 $form->update_exchangerate($dbh, $form->{currency},
481 $form->{"datepaid_$i"},
482 $form->{"exchangerate_$i"}, 0);
487 # record exchange rate differences and gains/losses
488 foreach my $accno (keys %{ $form->{fx} }) {
489 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
491 ($form->{fx}{$accno}{$transdate} =
492 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
495 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
496 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
497 $dbh->do($query) || $form->dberror($query);
498 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
499 transdate, cleared, fx_transaction, project_id)
501 (SELECT c.id FROM chart c
502 WHERE c.accno = '$accno'),
503 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
504 do_query($form, $dbh, $query, $project_id);
508 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
511 my $query = qq|UPDATE ar set
512 paid = $form->{paid},
514 WHERE id=$form->{id}|;
516 $dbh->do($query) || $form->dberror($query);
518 my $rc = $dbh->commit;
521 $main::lxdebug->leave_sub();
526 sub delete_transaction {
527 $main::lxdebug->enter_sub();
529 my ($self, $myconfig, $form) = @_;
531 # connect to database, turn AutoCommit off
532 my $dbh = $form->dbconnect_noauto($myconfig);
534 my $query = qq|DELETE FROM ar WHERE id = $form->{id}|;
535 $dbh->do($query) || $form->dberror($query);
537 $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
538 $dbh->do($query) || $form->dberror($query);
541 my $rc = $dbh->commit;
544 $main::lxdebug->leave_sub();
549 sub ar_transactions {
550 $main::lxdebug->enter_sub();
552 my ($self, $myconfig, $form) = @_;
554 # connect to database
555 my $dbh = $form->dbconnect($myconfig);
559 my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
560 a.duedate, a.netamount, a.amount, a.paid, c.name,
561 a.invoice, a.datepaid, a.terms, a.notes, a.shipvia,
562 a.shippingpoint, a.storno, a.globalproject_id,
563 pr.projectnumber AS globalprojectnumber,
566 JOIN customer c ON (a.customer_id = c.id)
567 LEFT JOIN employee e ON (a.employee_id = e.id)
568 LEFT JOIN project pr ON a.globalproject_id = pr.id|;
571 if ($form->{customer_id}) {
572 $where .= " AND a.customer_id = $form->{customer_id}";
574 if ($form->{customer}) {
575 my $customer = $form->like(lc $form->{customer});
576 $where .= " AND lower(c.name) LIKE '$customer'";
579 if ($form->{department}) {
580 my ($null, $department_id) = split /--/, $form->{department};
581 $where .= " AND a.department_id = $department_id";
583 if ($form->{invnumber}) {
584 my $invnumber = $form->like(lc $form->{invnumber});
585 $where .= " AND lower(a.invnumber) LIKE '$invnumber'";
587 if ($form->{ordnumber}) {
588 my $ordnumber = $form->like(lc $form->{ordnumber});
589 $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
591 if ($form->{notes}) {
592 my $notes = $form->like(lc $form->{notes});
593 $where .= " AND lower(a.notes) LIKE '$notes'";
595 if ($form->{"project_id"}) {
597 qq|AND ((a.globalproject_id = ?) OR EXISTS | .
598 qq| (SELECT * FROM invoice i | .
599 qq| WHERE i.project_id = ? AND i.trans_id = a.id))|;
600 push(@values, $form->{"project_id"}, $form->{"project_id"});
603 $where .= " AND a.transdate >= '$form->{transdatefrom}'"
604 if $form->{transdatefrom};
605 $where .= " AND a.transdate <= '$form->{transdateto}'"
606 if $form->{transdateto};
607 if ($form->{open} || $form->{closed}) {
608 unless ($form->{open} && $form->{closed}) {
609 $where .= " AND a.amount <> a.paid" if ($form->{open});
610 $where .= " AND a.amount = a.paid" if ($form->{closed});
614 my @a = (transdate, invnumber, name);
615 push @a, "employee" if $form->{l_employee};
616 my $sortorder = join ', ', $form->sort_columns(@a);
617 $sortorder = $form->{sort} if $form->{sort};
619 $query .= " WHERE $where
620 ORDER by $sortorder";
622 my $sth = $dbh->prepare($query);
623 $sth->execute(@values) ||
624 $form->dberror($query . " (" . join(", ", @values) . ")");
626 while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
627 push @{ $form->{AR} }, $ar;
633 $main::lxdebug->leave_sub();
637 $main::lxdebug->enter_sub();
639 my ($self, $myconfig, $form) = @_;
641 # connect to database
642 my $dbh = $form->dbconnect($myconfig);
646 " (SELECT transdate FROM ar WHERE id = " .
647 " (SELECT MAX(id) FROM ar) LIMIT 1), " .
649 ($form->{transdate}) = $dbh->selectrow_array($query);
653 $main::lxdebug->leave_sub();