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 # Inventory received module
33 #======================================================================
41 $main::lxdebug->enter_sub();
43 my ($self, $myconfig, $form) = @_;
45 # connect to database, turn off autocommit
46 my $dbh = $form->dbconnect_noauto($myconfig);
48 my ($query, $sth, $null, $project_id);
56 my $service_units = AM->retrieve_units($myconfig,$form,"service");
57 my $part_units = AM->retrieve_units($myconfig,$form,"dimension");
61 &reverse_invoice($dbh, $form);
64 my $uid = rand() . time;
66 $uid .= $form->{login};
68 $uid = substr($uid, 2, 75);
70 $query = qq|INSERT INTO ap (invnumber, employee_id)
71 VALUES ('$uid', (SELECT e.id FROM employee e
72 WHERE e.login = '$form->{login}'))|;
73 $dbh->do($query) || $form->dberror($query);
75 $query = qq|SELECT a.id FROM ap a
76 WHERE a.invnumber = '$uid'|;
77 $sth = $dbh->prepare($query);
78 $sth->execute || $form->dberror($query);
80 ($form->{id}) = $sth->fetchrow_array;
84 ($null, $form->{contact_id}) = split /--/, $form->{contact};
85 $form->{contact_id} *= 1;
87 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
89 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
90 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
92 if ($form->{currency} eq $form->{defaultcurrency}) {
93 $form->{exchangerate} = 1;
96 $form->check_exchangerate($myconfig, $form->{currency},
97 $form->{transdate}, 'sell');
100 $form->{exchangerate} =
103 : $form->parse_amount($myconfig, $form->{exchangerate});
105 for my $i (1 .. $form->{rowcount}) {
106 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
108 if ($form->{storno}) {
109 $form->{"qty_$i"} *= -1;
113 $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
116 if ($form->{"id_$i"}) {
119 $query = qq|SELECT p.unit
121 WHERE p.id = $form->{"id_$i"}|;
122 $sth = $dbh->prepare($query);
123 $sth->execute || $form->dberror($query);
125 my ($item_unit) = $sth->fetchrow_array();
128 if ($form->{"inventory_accno_$i"}) {
129 if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
130 $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
134 $baseqty = $form->{"qty_$i"} * $basefactor;
136 if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
137 $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
141 $baseqty = $form->{"qty_$i"} * $basefactor;
144 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
145 qw(partnumber description unit);
147 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
152 $form->{"sellprice_$i"} =
153 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
154 my $fxsellprice = $form->{"sellprice_$i"};
156 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
158 my $decimalplaces = ($dec > 2) ? $dec : 2;
160 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
162 if ($form->{"inventory_accno_$i"}) {
165 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
167 if ($form->{taxincluded}) {
168 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
169 $form->{"sellprice_$i"} =
170 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
172 $taxamount = $linetotal * $taxrate;
175 $netamount += $linetotal;
177 if ($form->round_amount($taxrate, 7) == 0) {
178 if ($form->{taxincluded}) {
179 foreach $item (@taxaccounts) {
181 $form->round_amount($linetotal * $form->{"${item}_rate"} /
182 (1 + abs($form->{"${item}_rate"})),
184 $taxdiff += $taxamount;
185 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
187 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
190 $form->{amount}{ $form->{id} }{$_} -=
191 $linetotal * $form->{"${_}_rate"}
196 $form->{amount}{ $form->{id} }{$_} -=
197 $taxamount * $form->{"${_}_rate"} / $taxrate
201 # add purchase to inventory, this one is without the tax!
203 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
205 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
206 $form->{exchangerate};
207 $linetotal = $form->round_amount($linetotal, 2);
209 # this is the difference for the inventory
210 $invoicediff += ($amount - $linetotal);
212 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
215 # adjust and round sellprice
216 $form->{"sellprice_$i"} =
217 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
221 $query = qq|UPDATE parts SET
222 lastcost = $form->{"sellprice_$i"}
223 WHERE id = $form->{"id_$i"}|;
225 $dbh->do($query) || $form->dberror($query);
227 $form->update_balance($dbh, "parts", "onhand",
228 qq|id = $form->{"id_$i"}|,
230 unless $form->{shipped};
232 # check if we sold the item already and
233 # make an entry for the expense and inventory
234 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
235 p.inventory_accno_id, p.expense_accno_id, a.transdate
236 FROM invoice i, ar a, parts p
237 WHERE i.parts_id = p.id
238 AND i.parts_id = $form->{"id_$i"}
239 AND (i.base_qty + i.allocated) > 0
240 AND i.trans_id = a.id
242 $sth = $dbh->prepare($query);
243 $sth->execute || $form->dberror($query);
245 my $totalqty = $base_qty;
247 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
249 my $qty = $ref->{base_qty} + $ref->{allocated};
251 if (($qty - $totalqty) > 0) {
255 $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
257 if ($ref->{allocated} < 0) {
259 # we have an entry for it already, adjust amount
260 $form->update_balance(
264 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
267 $form->update_balance(
271 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
276 # add entry for inventory, this one is for the sold item
277 if ($linetotal != 0) {
278 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
280 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
281 $linetotal, '$ref->{transdate}')|;
282 $dbh->do($query) || $form->dberror($query);
285 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
287 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
288 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
289 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
290 $dbh->do($query) || $form->dberror($query);
294 # update allocated for sold item
295 $form->update_balance($dbh, "invoice", "allocated",
301 last if (($totalqty -= $qty) <= 0);
306 $lastinventoryaccno = $form->{"inventory_accno_$i"};
311 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
313 if ($form->{taxincluded}) {
314 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
316 $form->{"sellprice_$i"} =
317 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
319 $taxamount = $linetotal * $taxrate;
322 $netamount += $linetotal;
324 if ($form->round_amount($taxrate, 7) == 0) {
325 if ($form->{taxincluded}) {
326 foreach $item (@taxaccounts) {
328 $linetotal * $form->{"${item}_rate"} /
329 (1 + abs($form->{"${item}_rate"}));
330 $totaltax += $taxamount;
331 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
335 $form->{amount}{ $form->{id} }{$_} -=
336 $linetotal * $form->{"${_}_rate"}
341 $form->{amount}{ $form->{id} }{$_} -=
342 $taxamount * $form->{"${_}_rate"} / $taxrate
347 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
349 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
350 $form->{exchangerate};
351 $linetotal = $form->round_amount($linetotal, 2);
353 # this is the difference for expense
354 $expensediff += ($amount - $linetotal);
356 # add amount to expense
357 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
360 $lastexpenseaccno = $form->{"expense_accno_$i"};
362 # adjust and round sellprice
363 $form->{"sellprice_$i"} =
364 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
368 $query = qq|UPDATE parts SET
369 lastcost = $form->{"sellprice_$i"}
370 WHERE id = $form->{"id_$i"}|;
372 $dbh->do($query) || $form->dberror($query);
376 $project_id = 'NULL';
377 if ($form->{"projectnumber_$i"}) {
378 $project_id = $form->{"projectnumber_$i"};
381 ($form->{"deliverydate_$i"})
382 ? qq|'$form->{"deliverydate_$i"}'|
385 # save detail record in invoice table
386 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
387 sellprice, fxsellprice, allocated, unit, deliverydate,
388 project_id, serialnumber)
389 VALUES ($form->{id}, $form->{"id_$i"},
390 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
391 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
392 '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
393 '$form->{"serialnumber_$i"}')|;
394 $dbh->do($query) || $form->dberror($query);
398 $form->{datepaid} = $form->{invdate};
400 # all amounts are in natural state, netamount includes the taxes
401 # if tax is included, netamount is rounded to 2 decimal places,
405 for my $i (1 .. $form->{paidaccounts}) {
406 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
407 $form->{paid} += $form->{"paid_$i"};
408 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
411 my ($tax, $paiddiff) = (0, 0);
413 $netamount = $form->round_amount($netamount, 2);
415 # figure out rounding errors for amount paid and total amount
416 if ($form->{taxincluded}) {
418 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
419 $paiddiff = $amount - $netamount * $form->{exchangerate};
420 $netamount = $amount;
422 foreach $item (split / /, $form->{taxaccounts}) {
423 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
424 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
425 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
427 $netamount -= $amount;
430 $invoicediff += $paiddiff;
431 $expensediff += $paiddiff;
433 ######## this only applies to tax included
434 if ($lastinventoryaccno) {
435 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
437 if ($lastexpenseaccno) {
438 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
442 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
443 $paiddiff = $amount - $netamount * $form->{exchangerate};
444 $netamount = $amount;
445 foreach my $item (split / /, $form->{taxaccounts}) {
446 $form->{amount}{ $form->{id} }{$item} =
447 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
450 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
453 $amount - $form->{amount}{ $form->{id} }{$item} *
454 $form->{exchangerate} * -1;
455 $form->{amount}{ $form->{id} }{$item} =
456 $form->round_amount($amount * -1, 2);
457 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
462 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
464 if ($form->{paid} != 0) {
466 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
470 # update exchangerate
471 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
472 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
473 $form->{exchangerate});
476 # record acc_trans transactions
477 foreach my $trans_id (keys %{ $form->{amount} }) {
478 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
480 ($form->{amount}{$trans_id}{$accno} =
481 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
484 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
486 VALUES ($trans_id, (SELECT c.id FROM chart c
487 WHERE c.accno = '$accno'),
488 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
489 (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
490 $dbh->do($query) || $form->dberror($query);
495 # deduct payment differences from paiddiff
496 for my $i (1 .. $form->{paidaccounts}) {
497 if ($form->{"paid_$i"} != 0) {
499 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
500 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
504 # force AP entry if 0
505 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
506 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
508 # record payments and offsetting AP
509 for my $i (1 .. $form->{paidaccounts}) {
511 if ($form->{"paid_$i"} != 0) {
512 my ($accno) = split /--/, $form->{"AP_paid_$i"};
513 $form->{"datepaid_$i"} = $form->{invdate}
514 unless ($form->{"datepaid_$i"});
515 $form->{datepaid} = $form->{"datepaid_$i"};
519 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
525 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
526 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
528 VALUES ($form->{id}, (SELECT c.id FROM chart c
529 WHERE c.accno = '$form->{AP}'),
530 $amount, '$form->{"datepaid_$i"}')|;
531 $dbh->do($query) || $form->dberror($query);
536 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
538 VALUES ($form->{id}, (SELECT c.id FROM chart c
539 WHERE c.accno = '$accno'),
540 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
541 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
542 $dbh->do($query) || $form->dberror($query);
546 if ($form->{currency} eq $form->{defaultcurrency}) {
547 $form->{"exchangerate_$i"} = 1;
550 $form->check_exchangerate($myconfig, $form->{currency},
551 $form->{"datepaid_$i"}, 'sell');
553 $form->{"exchangerate_$i"} =
556 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
559 # exchangerate difference
560 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
561 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
565 ($form->{"paid_$i"} * $form->{exchangerate}) -
566 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
568 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
571 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
577 # update exchange rate
578 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
579 $form->update_exchangerate($dbh, $form->{currency},
580 $form->{"datepaid_$i"},
581 0, $form->{"exchangerate_$i"});
586 # record exchange rate differences and gains/losses
587 foreach my $accno (keys %{ $form->{fx} }) {
588 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
590 ($form->{fx}{$accno}{$transdate} =
591 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
595 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
596 transdate, cleared, fx_transaction)
597 VALUES ($form->{id}, (SELECT c.id FROM chart c
598 WHERE c.accno = '$accno'),
599 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
600 $dbh->do($query) || $form->dberror($query);
605 $amount = $netamount + $tax;
607 # set values which could be empty
608 $form->{taxincluded} *= 1;
609 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
610 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
612 ($null, $form->{department_id}) = split(/--/, $form->{department});
613 $form->{department_id} *= 1;
614 $form->{payment_id} *= 1;
615 $form->{language_id} *= 1;
616 $form->{taxzone_id} *= 1;
617 $form->{storno} *= 1;
620 $form->{invnumber} = $form->{id} unless $form->{invnumber};
623 $query = qq|UPDATE ap set
624 invnumber = '$form->{invnumber}',
625 ordnumber = '$form->{ordnumber}',
626 quonumber = '$form->{quonumber}',
627 transdate = '$form->{invdate}',
628 orddate = | . conv_dateq($form->{"orddate"}) . qq|,
629 quodate = | . conv_dateq($form->{"quodate"}) . qq|,
630 vendor_id = $form->{vendor_id},
632 netamount = $netamount,
633 paid = $form->{paid},
634 datepaid = $datepaid,
637 taxzone_id = '$form->{taxzone_id}',
638 taxincluded = '$form->{taxincluded}',
639 notes = '$form->{notes}',
640 intnotes = '$form->{intnotes}',
641 curr = '$form->{currency}',
642 department_id = $form->{department_id},
643 storno = '$form->{storno}',
644 cp_id = $form->{contact_id}
645 WHERE id = $form->{id}|;
646 $dbh->do($query) || $form->dberror($query);
648 if ($form->{storno}) {
649 $query = qq| update ap set paid=paid+amount where id=$form->{storno_id}|;
650 $dbh->do($query) || $form->dberror($query);
651 $query = qq| update ap set storno='$form->{storno}' where id=$form->{storno_id}|;
652 $dbh->do($query) || $form->dberror($query);
653 $query = qq§ update ap set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}§;
654 $dbh->do($query) || $form->dberror($query);
656 $query = qq| update ap set paid=amount where id=$form->{id}|;
657 $dbh->do($query) || $form->dberror($query);
662 $form->{name} = $form->{vendor};
663 $form->{name} =~ s/--$form->{vendor_id}//;
664 $form->add_shipto($dbh, $form->{id}, "AP");
666 # delete zero entries
667 $query = qq|DELETE FROM acc_trans
669 $dbh->do($query) || $form->dberror($query);
671 if ($form->{webdav}) {
672 &webdav_folder($myconfig, $form);
675 my $rc = $dbh->commit;
678 $main::lxdebug->leave_sub();
683 sub reverse_invoice {
684 $main::lxdebug->enter_sub();
686 my ($dbh, $form) = @_;
688 # reverse inventory items
689 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
690 i.qty, i.allocated, i.sellprice
691 FROM invoice i, parts p
692 WHERE i.parts_id = p.id
693 AND i.trans_id = $form->{id}|;
694 my $sth = $dbh->prepare($query);
695 $sth->execute || $form->dberror($query);
699 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
700 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
702 if ($ref->{inventory_accno_id}) {
705 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
708 # if $ref->{allocated} > 0 than we sold that many items
709 if ($ref->{allocated} > 0) {
711 # get references for sold items
712 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
714 WHERE i.parts_id = $ref->{parts_id}
716 AND i.trans_id = a.id
717 ORDER BY transdate DESC|;
718 my $sth = $dbh->prepare($query);
719 $sth->execute || $form->dberror($query);
721 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
722 my $qty = $ref->{allocated};
723 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
724 $qty = $pthref->{allocated} * -1;
727 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
730 $form->update_balance($dbh, "invoice", "allocated",
731 qq|id = $pthref->{id}|, $qty);
733 $form->update_balance(
737 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
740 $form->update_balance(
744 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
747 last if (($ref->{allocated} -= $qty) <= 0);
756 $query = qq|DELETE FROM acc_trans
757 WHERE trans_id = $form->{id}|;
758 $dbh->do($query) || $form->dberror($query);
760 # delete invoice entries
761 $query = qq|DELETE FROM invoice
762 WHERE trans_id = $form->{id}|;
763 $dbh->do($query) || $form->dberror($query);
765 $query = qq|DELETE FROM shipto
766 WHERE trans_id = $form->{id} AND module = 'AP'|;
767 $dbh->do($query) || $form->dberror($query);
769 $main::lxdebug->leave_sub();
773 $main::lxdebug->enter_sub();
775 my ($self, $myconfig, $form) = @_;
777 # connect to database
778 my $dbh = $form->dbconnect_noauto($myconfig);
780 &reverse_invoice($dbh, $form);
782 # delete zero entries
783 my $query = qq|DELETE FROM acc_trans
785 $dbh->do($query) || $form->dberror($query);
788 my $query = qq|DELETE FROM ap
789 WHERE id = $form->{id}|;
790 $dbh->do($query) || $form->dberror($query);
792 my $rc = $dbh->commit;
795 $main::lxdebug->leave_sub();
800 sub retrieve_invoice {
801 $main::lxdebug->enter_sub();
803 my ($self, $myconfig, $form) = @_;
805 # connect to database
806 my $dbh = $form->dbconnect_noauto($myconfig);
812 # get default accounts and last invoice number
813 $query = qq|SELECT (SELECT c.accno FROM chart c
814 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
815 (SELECT c.accno FROM chart c
816 WHERE d.income_accno_id = c.id) AS income_accno,
817 (SELECT c.accno FROM chart c
818 WHERE d.expense_accno_id = c.id) AS expense_accno,
819 (SELECT c.accno FROM chart c
820 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
821 (SELECT c.accno FROM chart c
822 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
826 $query = qq|SELECT (SELECT c.accno FROM chart c
827 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
828 (SELECT c.accno FROM chart c
829 WHERE d.income_accno_id = c.id) AS income_accno,
830 (SELECT c.accno FROM chart c
831 WHERE d.expense_accno_id = c.id) AS expense_accno,
832 (SELECT c.accno FROM chart c
833 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
834 (SELECT c.accno FROM chart c
835 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
836 d.curr AS currencies,
837 current_date AS invdate
840 my $sth = $dbh->prepare($query);
841 $sth->execute || $form->dberror($query);
843 my $ref = $sth->fetchrow_hashref(NAME_lc);
844 map { $form->{$_} = $ref->{$_} } keys %$ref;
850 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
851 a.orddate, a.quodate,
852 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
853 a.intnotes, a.curr AS currency
855 WHERE a.id = $form->{id}|;
856 $sth = $dbh->prepare($query);
857 $sth->execute || $form->dberror($query);
859 $ref = $sth->fetchrow_hashref(NAME_lc);
860 map { $form->{$_} = $ref->{$_} } keys %$ref;
863 $form->{exchangerate} =
864 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
868 $query = qq|SELECT s.* FROM shipto s
869 WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
870 $sth = $dbh->prepare($query);
871 $sth->execute || $form->dberror($query);
873 $ref = $sth->fetchrow_hashref(NAME_lc);
875 map { $form->{$_} = $ref->{$_} } keys %$ref;
879 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
881 if(!$form->{taxzone_id}) {
882 $form->{taxzone_id} = 0;
884 # retrieve individual items
885 $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
886 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
887 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
888 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
889 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
891 i.project_id, i.serialnumber,
894 JOIN parts p ON (i.parts_id = p.id)
895 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
896 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
897 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
898 LEFT JOIN project pr ON (i.project_id = pr.id)
899 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
900 WHERE i.trans_id = $form->{id}
902 $sth = $dbh->prepare($query);
903 $sth->execute || $form->dberror($query);
905 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
906 if (!$ref->{"part_inventory_accno_id"}) {
907 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
909 delete($ref->{"part_inventory_accno_id"});
911 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
912 my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
913 my $stw = $dbh->prepare($query);
914 $stw->execute || $form->dberror($query);
915 ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
919 while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
920 my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
921 my $stw = $dbh->prepare($query);
922 $stw->execute || $form->dberror($query);
923 ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
927 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
928 my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
929 my $stw = $dbh->prepare($query);
930 $stw->execute || $form->dberror($query);
931 ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
935 # get tax rates and description
937 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
938 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
939 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
940 WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
942 $stw = $dbh->prepare($query);
943 $stw->execute || $form->dberror($query);
944 $ref->{taxaccounts} = "";
946 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
948 # if ($customertax{$ref->{accno}}) {
949 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
953 $ref->{taxaccounts} .= "$ptr->{accno} ";
954 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
955 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
956 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
957 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
958 $form->{taxaccounts} .= "$ptr->{accno} ";
963 chop $ref->{taxaccounts};
964 push @{ $form->{invoice_details} }, $ref;
969 if ($form->{webdav}) {
970 &webdav_folder($myconfig, $form);
975 my $rc = $dbh->commit;
978 $main::lxdebug->leave_sub();
984 $main::lxdebug->enter_sub();
986 my ($self, $myconfig, $form) = @_;
988 # connect to database
989 my $dbh = $form->dbconnect($myconfig);
991 my $dateformat = $myconfig->{dateformat};
992 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
996 ? "to_date('$form->{invdate}', '$dateformat')"
999 $form->{vendor_id} *= 1;
1002 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
1003 v.email, v.cc, v.bcc, v.language_id, v.payment_id,
1004 v.street, v.zipcode, v.city, v.country, v.taxzone_id,
1005 $duedate + v.terms AS duedate,
1006 b.description AS business
1008 LEFT JOIN business b ON b.id = v.business_id
1009 WHERE v.id = $form->{vendor_id}|;
1010 my $sth = $dbh->prepare($query);
1011 $sth->execute || $form->dberror($query);
1013 $ref = $sth->fetchrow_hashref(NAME_lc);
1014 map { $form->{$_} = $ref->{$_} } keys %$ref;
1017 $form->{creditremaining} = $form->{creditlimit};
1018 $query = qq|SELECT SUM(a.amount - a.paid)
1020 WHERE a.vendor_id = $form->{vendor_id}|;
1021 $sth = $dbh->prepare($query);
1022 $sth->execute || $form->dberror($query);
1024 ($form->{creditremaining}) -= $sth->fetchrow_array;
1028 $query = qq|SELECT o.amount,
1029 (SELECT e.sell FROM exchangerate e
1030 WHERE e.curr = o.curr
1031 AND e.transdate = o.transdate)
1033 WHERE o.vendor_id = $form->{vendor_id}
1034 AND o.quotation = '0'
1035 AND o.closed = '0'|;
1036 $sth = $dbh->prepare($query);
1037 $sth->execute || $form->dberror($query);
1039 while (my ($amount, $exch) = $sth->fetchrow_array) {
1040 $exch = 1 unless $exch;
1041 $form->{creditremaining} -= $amount * $exch;
1045 $form->get_contacts($dbh, $form->{vendor_id});
1047 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1049 # get contact if selected
1050 if ($form->{contact} ne "--" && $form->{contact} ne "") {
1051 $form->get_contact($dbh, $form->{cp_id});
1054 # get shipto if we do not convert an order or invoice
1055 if (!$form->{shipto}) {
1056 map { delete $form->{$_} }
1057 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
1059 $query = qq|SELECT s.* FROM shipto s
1060 WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
1061 $sth = $dbh->prepare($query);
1062 $sth->execute || $form->dberror($query);
1064 $ref = $sth->fetchrow_hashref(NAME_lc);
1065 map { $form->{$_} = $ref->{$_} } keys %$ref;
1069 # get taxes for vendor
1070 $query = qq|SELECT c.accno
1072 JOIN vendortax v ON (v.chart_id = c.id)
1073 WHERE v.vendor_id = $form->{vendor_id}|;
1074 $sth = $dbh->prepare($query);
1075 $sth->execute || $form->dberror($query);
1078 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1079 $vendortax{ $ref->{accno} } = 1;
1083 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1085 # setup last accounts used
1086 $query = qq|SELECT c.accno, c.description, c.link, c.category
1088 JOIN acc_trans ac ON (ac.chart_id = c.id)
1089 JOIN ap a ON (a.id = ac.trans_id)
1090 WHERE a.vendor_id = $form->{vendor_id}
1091 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1092 AND a.id IN (SELECT max(a2.id) FROM ap a2
1093 WHERE a2.vendor_id = $form->{vendor_id})|;
1094 $sth = $dbh->prepare($query);
1095 $sth->execute || $form->dberror($query);
1098 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1099 if ($ref->{category} eq 'E') {
1101 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1103 if ($ref->{category} eq 'L') {
1104 $form->{APselected} = $form->{AP_1} =
1105 "$ref->{accno}--$ref->{description}";
1109 $form->{rowcount} = $i if ($i && !$form->{type});
1114 $main::lxdebug->leave_sub();
1118 $main::lxdebug->enter_sub();
1120 my ($self, $myconfig, $form) = @_;
1122 # connect to database
1123 my $dbh = $form->dbconnect($myconfig);
1125 my $i = $form->{rowcount};
1127 # don't include assemblies or obsolete parts
1128 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1130 if ($form->{"partnumber_$i"}) {
1131 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1132 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1135 if ($form->{"description_$i"}) {
1136 my $description = $form->like(lc $form->{"description_$i"});
1137 $where .= " AND lower(p.description) LIKE '$description'";
1140 if ($form->{"partsgroup_$i"}) {
1141 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1142 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1145 if ($form->{"description_$i"}) {
1146 $where .= " ORDER BY p.description";
1148 $where .= " ORDER BY p.partnumber";
1152 if ($form->{type} eq "invoice") {
1154 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1157 $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1160 my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS sellprice,
1161 p.listprice, p.inventory_accno_id,
1162 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1163 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1164 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1165 p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
1166 pg.partsgroup, p.formel
1168 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
1169 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
1170 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
1171 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1173 my $sth = $dbh->prepare($query);
1174 $sth->execute || $form->dberror($query);
1176 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1178 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1179 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1180 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1181 if (!$ref->{inventory_accno_id}) {
1182 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1184 delete($ref->{inventory_accno_id});
1186 # get tax rates and description
1188 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1189 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1190 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
1191 WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
1193 $stw = $dbh->prepare($query);
1194 $stw->execute || $form->dberror($query);
1196 $ref->{taxaccounts} = "";
1198 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1200 # if ($customertax{$ref->{accno}}) {
1201 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1205 $ref->{taxaccounts} .= "$ptr->{accno} ";
1206 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1207 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1208 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1209 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1210 $form->{taxaccounts} .= "$ptr->{accno} ";
1216 chop $ref->{taxaccounts};
1218 push @{ $form->{item_list} }, $ref;
1225 $main::lxdebug->leave_sub();
1228 sub vendor_details {
1229 $main::lxdebug->enter_sub();
1231 my ($self, $myconfig, $form, @wanted_vars) = @_;
1233 # connect to database
1234 my $dbh = $form->dbconnect($myconfig);
1236 # get contact id, set it if nessessary
1237 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1240 if ($form->{cp_id}) {
1241 $contact = "and cp.cp_id = $form->{cp_id}";
1244 # get rest for the vendor
1245 # fax and phone and email as vendor*
1247 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1249 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1250 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1251 my $sth = $dbh->prepare($query);
1252 $sth->execute || $form->dberror($query);
1254 $ref = $sth->fetchrow_hashref(NAME_lc);
1256 # remove id and taxincluded before copy back
1257 delete @$ref{qw(id taxincluded)};
1259 @wanted_vars = grep({ $_ } @wanted_vars);
1260 if (scalar(@wanted_vars) > 0) {
1262 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
1263 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
1266 map { $form->{$_} = $ref->{$_} } keys %$ref;
1271 $main::lxdebug->leave_sub();
1275 $main::lxdebug->enter_sub();
1277 my ($self, $myconfig, $form) = @_;
1279 # connect to database
1280 my $dbh = $form->dbconnect($myconfig);
1282 my $query = qq|SELECT c.accno, c.description, c.link
1284 WHERE c.link LIKE '%IC%'
1286 my $sth = $dbh->prepare($query);
1287 $sth->execute || $form->dberror($query);
1289 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1290 foreach my $key (split(/:/, $ref->{link})) {
1292 push @{ $form->{IC_links}{$key} },
1293 { accno => $ref->{accno},
1294 description => $ref->{description} };
1300 $main::lxdebug->leave_sub();
1304 $main::lxdebug->enter_sub();
1306 my ($myconfig, $form) = @_;
1309 $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1310 if ($form->{vc} eq "customer");
1311 $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1312 if ($form->{vc} eq "vendor");
1316 mkdir($path, 0770) or die "can't make directory $!\n";
1320 foreach $file (@files) {
1322 $file =~ /\/([^\/]*)$/;
1324 $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1326 $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1327 $form->{WEBDAV}{$fname} = $link;
1332 $main::lxdebug->leave_sub();
1336 $main::lxdebug->enter_sub();
1338 my ($self, $myconfig, $form, $locale) = @_;
1340 # connect to database, turn off autocommit
1341 my $dbh = $form->dbconnect_noauto($myconfig);
1343 $form->{datepaid} = $form->{invdate};
1345 # total payments, don't move we need it here
1346 for my $i (1 .. $form->{paidaccounts}) {
1347 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
1348 $form->{paid} += $form->{"paid_$i"};
1349 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
1352 $form->{exchangerate} =
1353 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
1356 # record payments and offsetting AP
1357 for my $i (1 .. $form->{paidaccounts}) {
1359 if ($form->{"paid_$i"} != 0) {
1360 my ($accno) = split /--/, $form->{"AP_paid_$i"};
1361 $form->{"datepaid_$i"} = $form->{invdate}
1362 unless ($form->{"datepaid_$i"});
1363 $form->{datepaid} = $form->{"datepaid_$i"};
1366 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
1367 $form->{"exchangerate_$i"} = 1;
1370 $form->check_exchangerate($myconfig, $form->{currency},
1371 $form->{"datepaid_$i"}, 'buy');
1373 $form->{"exchangerate_$i"} =
1376 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
1381 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
1385 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1386 WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
1387 $dbh->do($query) || $form->dberror($query);
1389 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1391 VALUES ($form->{id}, (SELECT c.id FROM chart c
1392 WHERE c.accno = '$form->{AP}'),
1393 $amount, '$form->{"datepaid_$i"}')|;
1394 $dbh->do($query) || $form->dberror($query);
1398 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1399 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
1400 $dbh->do($query) || $form->dberror($query);
1402 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1404 VALUES ($form->{id}, (SELECT c.id FROM chart c
1405 WHERE c.accno = '$accno'),
1406 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
1407 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
1408 $dbh->do($query) || $form->dberror($query);
1413 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1414 $form->{"exchangerate_$i"};
1416 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
1419 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
1425 # update exchange rate
1426 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
1427 $form->update_exchangerate($dbh, $form->{currency},
1428 $form->{"datepaid_$i"},
1429 $form->{"exchangerate_$i"}, 0);
1434 # record exchange rate differences and gains/losses
1435 foreach my $accno (keys %{ $form->{fx} }) {
1436 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1438 ($form->{fx}{$accno}{$transdate} =
1439 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
1442 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1443 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
1444 $dbh->do($query) || $form->dberror($query);
1445 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1446 transdate, cleared, fx_transaction)
1447 VALUES ($form->{id},
1448 (SELECT c.id FROM chart c
1449 WHERE c.accno = '$accno'),
1450 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
1451 $dbh->do($query) || $form->dberror($query);
1455 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
1458 my $query = qq|UPDATE ap set
1459 paid = $form->{paid},
1460 datepaid = $datepaid
1461 WHERE id=$form->{id}|;
1463 $dbh->do($query) || $form->dberror($query);
1465 my $rc = $dbh->commit;
1468 $main::lxdebug->leave_sub();