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 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
86 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
87 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
89 if ($form->{currency} eq $form->{defaultcurrency}) {
90 $form->{exchangerate} = 1;
93 $form->check_exchangerate($myconfig, $form->{currency},
94 $form->{transdate}, 'sell');
97 $form->{exchangerate} =
100 : $form->parse_amount($myconfig, $form->{exchangerate});
102 for my $i (1 .. $form->{rowcount}) {
103 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
105 if ($form->{storno}) {
106 $form->{"qty_$i"} *= -1;
110 $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
113 if ($form->{"id_$i"}) {
116 $query = qq|SELECT p.unit
118 WHERE p.id = $form->{"id_$i"}|;
119 $sth = $dbh->prepare($query);
120 $sth->execute || $form->dberror($query);
122 my ($item_unit) = $sth->fetchrow_array();
125 if ($form->{"inventory_accno_$i"}) {
126 if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
127 $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
131 $baseqty = $form->{"qty_$i"} * $basefactor;
133 if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
134 $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
138 $baseqty = $form->{"qty_$i"} * $basefactor;
141 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
142 qw(partnumber description unit);
144 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
149 $form->{"sellprice_$i"} =
150 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
151 my $fxsellprice = $form->{"sellprice_$i"};
153 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
155 my $decimalplaces = ($dec > 2) ? $dec : 2;
157 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
159 if ($form->{"inventory_accno_$i"}) {
162 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
164 if ($form->{taxincluded}) {
165 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
166 $form->{"sellprice_$i"} =
167 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
169 $taxamount = $linetotal * $taxrate;
172 $netamount += $linetotal;
174 if ($form->round_amount($taxrate, 7) == 0) {
175 if ($form->{taxincluded}) {
176 foreach $item (@taxaccounts) {
178 $form->round_amount($linetotal * $form->{"${item}_rate"} /
179 (1 + abs($form->{"${item}_rate"})),
181 $taxdiff += $taxamount;
182 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
184 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
187 $form->{amount}{ $form->{id} }{$_} -=
188 $linetotal * $form->{"${_}_rate"}
193 $form->{amount}{ $form->{id} }{$_} -=
194 $taxamount * $form->{"${_}_rate"} / $taxrate
198 # add purchase to inventory, this one is without the tax!
200 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
202 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
203 $form->{exchangerate};
204 $linetotal = $form->round_amount($linetotal, 2);
206 # this is the difference for the inventory
207 $invoicediff += ($amount - $linetotal);
209 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
212 # adjust and round sellprice
213 $form->{"sellprice_$i"} =
214 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
218 $query = qq|UPDATE parts SET
219 lastcost = $form->{"sellprice_$i"}
220 WHERE id = $form->{"id_$i"}|;
222 $dbh->do($query) || $form->dberror($query);
224 $form->update_balance($dbh, "parts", "onhand",
225 qq|id = $form->{"id_$i"}|,
227 unless $form->{shipped};
229 # check if we sold the item already and
230 # make an entry for the expense and inventory
231 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
232 p.inventory_accno_id, p.expense_accno_id, a.transdate
233 FROM invoice i, ar a, parts p
234 WHERE i.parts_id = p.id
235 AND i.parts_id = $form->{"id_$i"}
236 AND (i.base_qty + i.allocated) > 0
237 AND i.trans_id = a.id
239 $sth = $dbh->prepare($query);
240 $sth->execute || $form->dberror($query);
242 my $totalqty = $base_qty;
244 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
246 my $qty = $ref->{base_qty} + $ref->{allocated};
248 if (($qty - $totalqty) > 0) {
252 $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
254 if ($ref->{allocated} < 0) {
256 # we have an entry for it already, adjust amount
257 $form->update_balance(
261 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
264 $form->update_balance(
268 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
273 # add entry for inventory, this one is for the sold item
274 if ($linetotal != 0) {
275 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
277 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
278 $linetotal, '$ref->{transdate}')|;
279 $dbh->do($query) || $form->dberror($query);
282 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
284 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
285 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
286 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
287 $dbh->do($query) || $form->dberror($query);
291 # update allocated for sold item
292 $form->update_balance($dbh, "invoice", "allocated",
298 last if (($totalqty -= $qty) <= 0);
303 $lastinventoryaccno = $form->{"inventory_accno_$i"};
308 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
310 if ($form->{taxincluded}) {
311 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
313 $form->{"sellprice_$i"} =
314 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
316 $taxamount = $linetotal * $taxrate;
319 $netamount += $linetotal;
321 if ($form->round_amount($taxrate, 7) == 0) {
322 if ($form->{taxincluded}) {
323 foreach $item (@taxaccounts) {
325 $linetotal * $form->{"${item}_rate"} /
326 (1 + abs($form->{"${item}_rate"}));
327 $totaltax += $taxamount;
328 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
332 $form->{amount}{ $form->{id} }{$_} -=
333 $linetotal * $form->{"${_}_rate"}
338 $form->{amount}{ $form->{id} }{$_} -=
339 $taxamount * $form->{"${_}_rate"} / $taxrate
344 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
346 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
347 $form->{exchangerate};
348 $linetotal = $form->round_amount($linetotal, 2);
350 # this is the difference for expense
351 $expensediff += ($amount - $linetotal);
353 # add amount to expense
354 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
357 $lastexpenseaccno = $form->{"expense_accno_$i"};
359 # adjust and round sellprice
360 $form->{"sellprice_$i"} =
361 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
365 $query = qq|UPDATE parts SET
366 lastcost = $form->{"sellprice_$i"}
367 WHERE id = $form->{"id_$i"}|;
369 $dbh->do($query) || $form->dberror($query);
373 $project_id = 'NULL';
374 if ($form->{"projectnumber_$i"}) {
375 $project_id = $form->{"projectnumber_$i"};
378 ($form->{"deliverydate_$i"})
379 ? qq|'$form->{"deliverydate_$i"}'|
382 # save detail record in invoice table
383 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
384 sellprice, fxsellprice, allocated, unit, deliverydate,
385 project_id, serialnumber)
386 VALUES ($form->{id}, $form->{"id_$i"},
387 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
388 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
389 '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
390 '$form->{"serialnumber_$i"}')|;
391 $dbh->do($query) || $form->dberror($query);
395 $form->{datepaid} = $form->{invdate};
397 # all amounts are in natural state, netamount includes the taxes
398 # if tax is included, netamount is rounded to 2 decimal places,
402 for my $i (1 .. $form->{paidaccounts}) {
403 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
404 $form->{paid} += $form->{"paid_$i"};
405 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
408 my ($tax, $paiddiff) = (0, 0);
410 $netamount = $form->round_amount($netamount, 2);
412 # figure out rounding errors for amount paid and total amount
413 if ($form->{taxincluded}) {
415 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
416 $paiddiff = $amount - $netamount * $form->{exchangerate};
417 $netamount = $amount;
419 foreach $item (split / /, $form->{taxaccounts}) {
420 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
421 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
422 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
424 $netamount -= $amount;
427 $invoicediff += $paiddiff;
428 $expensediff += $paiddiff;
430 ######## this only applies to tax included
431 if ($lastinventoryaccno) {
432 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
434 if ($lastexpenseaccno) {
435 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
439 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
440 $paiddiff = $amount - $netamount * $form->{exchangerate};
441 $netamount = $amount;
442 foreach my $item (split / /, $form->{taxaccounts}) {
443 $form->{amount}{ $form->{id} }{$item} =
444 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
447 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
450 $amount - $form->{amount}{ $form->{id} }{$item} *
451 $form->{exchangerate} * -1;
452 $form->{amount}{ $form->{id} }{$item} =
453 $form->round_amount($amount * -1, 2);
454 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
459 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
461 if ($form->{paid} != 0) {
463 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
467 # update exchangerate
468 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
469 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
470 $form->{exchangerate});
473 # record acc_trans transactions
474 foreach my $trans_id (keys %{ $form->{amount} }) {
475 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
477 ($form->{amount}{$trans_id}{$accno} =
478 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
481 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
483 VALUES ($trans_id, (SELECT c.id FROM chart c
484 WHERE c.accno = '$accno'),
485 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
486 (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
487 $dbh->do($query) || $form->dberror($query);
492 # deduct payment differences from paiddiff
493 for my $i (1 .. $form->{paidaccounts}) {
494 if ($form->{"paid_$i"} != 0) {
496 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
497 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
501 # force AP entry if 0
502 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
503 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
505 # record payments and offsetting AP
506 for my $i (1 .. $form->{paidaccounts}) {
508 if ($form->{"paid_$i"} != 0) {
509 my ($accno) = split /--/, $form->{"AP_paid_$i"};
510 $form->{"datepaid_$i"} = $form->{invdate}
511 unless ($form->{"datepaid_$i"});
512 $form->{datepaid} = $form->{"datepaid_$i"};
516 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
522 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
523 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
525 VALUES ($form->{id}, (SELECT c.id FROM chart c
526 WHERE c.accno = '$form->{AP}'),
527 $amount, '$form->{"datepaid_$i"}')|;
528 $dbh->do($query) || $form->dberror($query);
533 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
535 VALUES ($form->{id}, (SELECT c.id FROM chart c
536 WHERE c.accno = '$accno'),
537 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
538 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
539 $dbh->do($query) || $form->dberror($query);
543 if ($form->{currency} eq $form->{defaultcurrency}) {
544 $form->{"exchangerate_$i"} = 1;
547 $form->check_exchangerate($myconfig, $form->{currency},
548 $form->{"datepaid_$i"}, 'sell');
550 $form->{"exchangerate_$i"} =
553 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
556 # exchangerate difference
557 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
558 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
562 ($form->{"paid_$i"} * $form->{exchangerate}) -
563 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
565 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
568 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
574 # update exchange rate
575 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
576 $form->update_exchangerate($dbh, $form->{currency},
577 $form->{"datepaid_$i"},
578 0, $form->{"exchangerate_$i"});
583 # record exchange rate differences and gains/losses
584 foreach my $accno (keys %{ $form->{fx} }) {
585 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
587 ($form->{fx}{$accno}{$transdate} =
588 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
592 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
593 transdate, cleared, fx_transaction)
594 VALUES ($form->{id}, (SELECT c.id FROM chart c
595 WHERE c.accno = '$accno'),
596 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
597 $dbh->do($query) || $form->dberror($query);
602 $amount = $netamount + $tax;
604 # set values which could be empty
605 $form->{taxincluded} *= 1;
606 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
607 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
609 ($null, $form->{department_id}) = split(/--/, $form->{department});
610 $form->{department_id} *= 1;
611 $form->{payment_id} *= 1;
612 $form->{language_id} *= 1;
613 $form->{taxzone_id} *= 1;
614 $form->{storno} *= 1;
617 $form->{invnumber} = $form->{id} unless $form->{invnumber};
620 $query = qq|UPDATE ap set
621 invnumber = '$form->{invnumber}',
622 ordnumber = '$form->{ordnumber}',
623 quonumber = '$form->{quonumber}',
624 transdate = '$form->{invdate}',
625 orddate = | . conv_dateq($form->{"orddate"}) . qq|,
626 quodate = | . conv_dateq($form->{"quodate"}) . qq|,
627 vendor_id = $form->{vendor_id},
629 netamount = $netamount,
630 paid = $form->{paid},
631 datepaid = $datepaid,
634 taxzone_id = '$form->{taxzone_id}',
635 taxincluded = '$form->{taxincluded}',
636 notes = '$form->{notes}',
637 intnotes = '$form->{intnotes}',
638 curr = '$form->{currency}',
639 department_id = $form->{department_id},
640 storno = '$form->{storno}',
641 cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
642 WHERE id = $form->{id}|;
643 $dbh->do($query) || $form->dberror($query);
645 if ($form->{storno}) {
646 $query = qq| update ap set paid=paid+amount where id=$form->{storno_id}|;
647 $dbh->do($query) || $form->dberror($query);
648 $query = qq| update ap set storno='$form->{storno}' where id=$form->{storno_id}|;
649 $dbh->do($query) || $form->dberror($query);
650 $query = qq§ update ap set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}§;
651 $dbh->do($query) || $form->dberror($query);
653 $query = qq| update ap set paid=amount where id=$form->{id}|;
654 $dbh->do($query) || $form->dberror($query);
659 $form->{name} = $form->{vendor};
660 $form->{name} =~ s/--$form->{vendor_id}//;
661 $form->add_shipto($dbh, $form->{id}, "AP");
663 # delete zero entries
664 $query = qq|DELETE FROM acc_trans
666 $dbh->do($query) || $form->dberror($query);
668 if ($form->{webdav}) {
669 &webdav_folder($myconfig, $form);
672 my $rc = $dbh->commit;
675 $main::lxdebug->leave_sub();
680 sub reverse_invoice {
681 $main::lxdebug->enter_sub();
683 my ($dbh, $form) = @_;
685 # reverse inventory items
686 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
687 i.qty, i.allocated, i.sellprice
688 FROM invoice i, parts p
689 WHERE i.parts_id = p.id
690 AND i.trans_id = $form->{id}|;
691 my $sth = $dbh->prepare($query);
692 $sth->execute || $form->dberror($query);
696 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
697 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
699 if ($ref->{inventory_accno_id}) {
702 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
705 # if $ref->{allocated} > 0 than we sold that many items
706 if ($ref->{allocated} > 0) {
708 # get references for sold items
709 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
711 WHERE i.parts_id = $ref->{parts_id}
713 AND i.trans_id = a.id
714 ORDER BY transdate DESC|;
715 my $sth = $dbh->prepare($query);
716 $sth->execute || $form->dberror($query);
718 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
719 my $qty = $ref->{allocated};
720 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
721 $qty = $pthref->{allocated} * -1;
724 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
727 $form->update_balance($dbh, "invoice", "allocated",
728 qq|id = $pthref->{id}|, $qty);
730 $form->update_balance(
734 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
737 $form->update_balance(
741 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
744 last if (($ref->{allocated} -= $qty) <= 0);
753 $query = qq|DELETE FROM acc_trans
754 WHERE trans_id = $form->{id}|;
755 $dbh->do($query) || $form->dberror($query);
757 # delete invoice entries
758 $query = qq|DELETE FROM invoice
759 WHERE trans_id = $form->{id}|;
760 $dbh->do($query) || $form->dberror($query);
762 $query = qq|DELETE FROM shipto
763 WHERE trans_id = $form->{id} AND module = 'AP'|;
764 $dbh->do($query) || $form->dberror($query);
766 $main::lxdebug->leave_sub();
770 $main::lxdebug->enter_sub();
772 my ($self, $myconfig, $form) = @_;
774 # connect to database
775 my $dbh = $form->dbconnect_noauto($myconfig);
777 &reverse_invoice($dbh, $form);
779 # delete zero entries
780 my $query = qq|DELETE FROM acc_trans
782 $dbh->do($query) || $form->dberror($query);
785 my $query = qq|DELETE FROM ap
786 WHERE id = $form->{id}|;
787 $dbh->do($query) || $form->dberror($query);
789 my $rc = $dbh->commit;
792 $main::lxdebug->leave_sub();
797 sub retrieve_invoice {
798 $main::lxdebug->enter_sub();
800 my ($self, $myconfig, $form) = @_;
802 # connect to database
803 my $dbh = $form->dbconnect_noauto($myconfig);
809 # get default accounts and last invoice number
810 $query = qq|SELECT (SELECT c.accno FROM chart c
811 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
812 (SELECT c.accno FROM chart c
813 WHERE d.income_accno_id = c.id) AS income_accno,
814 (SELECT c.accno FROM chart c
815 WHERE d.expense_accno_id = c.id) AS expense_accno,
816 (SELECT c.accno FROM chart c
817 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
818 (SELECT c.accno FROM chart c
819 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
823 $query = qq|SELECT (SELECT c.accno FROM chart c
824 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
825 (SELECT c.accno FROM chart c
826 WHERE d.income_accno_id = c.id) AS income_accno,
827 (SELECT c.accno FROM chart c
828 WHERE d.expense_accno_id = c.id) AS expense_accno,
829 (SELECT c.accno FROM chart c
830 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
831 (SELECT c.accno FROM chart c
832 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
833 d.curr AS currencies,
834 current_date AS invdate
837 my $sth = $dbh->prepare($query);
838 $sth->execute || $form->dberror($query);
840 my $ref = $sth->fetchrow_hashref(NAME_lc);
841 map { $form->{$_} = $ref->{$_} } keys %$ref;
847 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
848 a.orddate, a.quodate,
849 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
850 a.intnotes, a.curr AS currency
852 WHERE a.id = $form->{id}|;
853 $sth = $dbh->prepare($query);
854 $sth->execute || $form->dberror($query);
856 $ref = $sth->fetchrow_hashref(NAME_lc);
857 map { $form->{$_} = $ref->{$_} } keys %$ref;
860 $form->{exchangerate} =
861 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
865 $query = qq|SELECT s.* FROM shipto s
866 WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
867 $sth = $dbh->prepare($query);
868 $sth->execute || $form->dberror($query);
870 $ref = $sth->fetchrow_hashref(NAME_lc);
872 map { $form->{$_} = $ref->{$_} } keys %$ref;
876 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
878 if(!$form->{taxzone_id}) {
879 $form->{taxzone_id} = 0;
881 # retrieve individual items
882 $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
883 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
884 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
885 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
886 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
888 i.project_id, i.serialnumber,
891 JOIN parts p ON (i.parts_id = p.id)
892 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
893 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
894 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
895 LEFT JOIN project pr ON (i.project_id = pr.id)
896 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
897 WHERE i.trans_id = $form->{id}
899 $sth = $dbh->prepare($query);
900 $sth->execute || $form->dberror($query);
902 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
903 if (!$ref->{"part_inventory_accno_id"}) {
904 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
906 delete($ref->{"part_inventory_accno_id"});
908 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
909 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}|;
910 my $stw = $dbh->prepare($query);
911 $stw->execute || $form->dberror($query);
912 ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
916 while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
917 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}|;
918 my $stw = $dbh->prepare($query);
919 $stw->execute || $form->dberror($query);
920 ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
924 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
925 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}|;
926 my $stw = $dbh->prepare($query);
927 $stw->execute || $form->dberror($query);
928 ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
932 # get tax rates and description
934 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
935 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
936 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
937 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)
939 $stw = $dbh->prepare($query);
940 $stw->execute || $form->dberror($query);
941 $ref->{taxaccounts} = "";
943 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
945 # if ($customertax{$ref->{accno}}) {
946 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
950 $ref->{taxaccounts} .= "$ptr->{accno} ";
951 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
952 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
953 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
954 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
955 $form->{taxaccounts} .= "$ptr->{accno} ";
960 chop $ref->{taxaccounts};
961 push @{ $form->{invoice_details} }, $ref;
966 if ($form->{webdav}) {
967 &webdav_folder($myconfig, $form);
972 my $rc = $dbh->commit;
975 $main::lxdebug->leave_sub();
981 $main::lxdebug->enter_sub();
983 my ($self, $myconfig, $form) = @_;
985 # connect to database
986 my $dbh = $form->dbconnect($myconfig);
988 my $dateformat = $myconfig->{dateformat};
989 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
993 ? "to_date('$form->{invdate}', '$dateformat')"
996 $form->{vendor_id} *= 1;
999 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
1000 v.email, v.cc, v.bcc, v.language_id, v.payment_id,
1001 v.street, v.zipcode, v.city, v.country, v.taxzone_id,
1002 $duedate + v.terms AS duedate,
1003 b.description AS business
1005 LEFT JOIN business b ON b.id = v.business_id
1006 WHERE v.id = $form->{vendor_id}|;
1007 my $sth = $dbh->prepare($query);
1008 $sth->execute || $form->dberror($query);
1010 $ref = $sth->fetchrow_hashref(NAME_lc);
1011 map { $form->{$_} = $ref->{$_} } keys %$ref;
1014 $form->{creditremaining} = $form->{creditlimit};
1015 $query = qq|SELECT SUM(a.amount - a.paid)
1017 WHERE a.vendor_id = $form->{vendor_id}|;
1018 $sth = $dbh->prepare($query);
1019 $sth->execute || $form->dberror($query);
1021 ($form->{creditremaining}) -= $sth->fetchrow_array;
1025 $query = qq|SELECT o.amount,
1026 (SELECT e.sell FROM exchangerate e
1027 WHERE e.curr = o.curr
1028 AND e.transdate = o.transdate)
1030 WHERE o.vendor_id = $form->{vendor_id}
1031 AND o.quotation = '0'
1032 AND o.closed = '0'|;
1033 $sth = $dbh->prepare($query);
1034 $sth->execute || $form->dberror($query);
1036 while (my ($amount, $exch) = $sth->fetchrow_array) {
1037 $exch = 1 unless $exch;
1038 $form->{creditremaining} -= $amount * $exch;
1042 # get shipto if we do not convert an order or invoice
1043 if (!$form->{shipto}) {
1044 map { delete $form->{$_} }
1045 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
1047 $query = qq|SELECT s.* FROM shipto s
1048 WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
1049 $sth = $dbh->prepare($query);
1050 $sth->execute || $form->dberror($query);
1052 $ref = $sth->fetchrow_hashref(NAME_lc);
1053 map { $form->{$_} = $ref->{$_} } keys %$ref;
1057 # get taxes for vendor
1058 $query = qq|SELECT c.accno
1060 JOIN vendortax v ON (v.chart_id = c.id)
1061 WHERE v.vendor_id = $form->{vendor_id}|;
1062 $sth = $dbh->prepare($query);
1063 $sth->execute || $form->dberror($query);
1066 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1067 $vendortax{ $ref->{accno} } = 1;
1071 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1073 # setup last accounts used
1074 $query = qq|SELECT c.accno, c.description, c.link, c.category
1076 JOIN acc_trans ac ON (ac.chart_id = c.id)
1077 JOIN ap a ON (a.id = ac.trans_id)
1078 WHERE a.vendor_id = $form->{vendor_id}
1079 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1080 AND a.id IN (SELECT max(a2.id) FROM ap a2
1081 WHERE a2.vendor_id = $form->{vendor_id})|;
1082 $sth = $dbh->prepare($query);
1083 $sth->execute || $form->dberror($query);
1086 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1087 if ($ref->{category} eq 'E') {
1089 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1091 if ($ref->{category} eq 'L') {
1092 $form->{APselected} = $form->{AP_1} =
1093 "$ref->{accno}--$ref->{description}";
1097 $form->{rowcount} = $i if ($i && !$form->{type});
1102 $main::lxdebug->leave_sub();
1106 $main::lxdebug->enter_sub();
1108 my ($self, $myconfig, $form) = @_;
1110 # connect to database
1111 my $dbh = $form->dbconnect($myconfig);
1113 my $i = $form->{rowcount};
1115 # don't include assemblies or obsolete parts
1116 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1118 if ($form->{"partnumber_$i"}) {
1119 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1120 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1123 if ($form->{"description_$i"}) {
1124 my $description = $form->like(lc $form->{"description_$i"});
1125 $where .= " AND lower(p.description) LIKE '$description'";
1128 if ($form->{"partsgroup_$i"}) {
1129 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1130 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1133 if ($form->{"description_$i"}) {
1134 $where .= " ORDER BY p.description";
1136 $where .= " ORDER BY p.partnumber";
1140 if ($form->{type} eq "invoice") {
1142 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1145 $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1148 my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS sellprice,
1149 p.listprice, p.inventory_accno_id,
1150 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1151 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1152 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1153 p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
1154 pg.partsgroup, p.formel
1156 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
1157 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
1158 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
1159 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1161 my $sth = $dbh->prepare($query);
1162 $sth->execute || $form->dberror($query);
1164 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1166 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1167 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1168 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1169 if (!$ref->{inventory_accno_id}) {
1170 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1172 delete($ref->{inventory_accno_id});
1174 # get tax rates and description
1176 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1177 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1178 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
1179 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)
1181 $stw = $dbh->prepare($query);
1182 $stw->execute || $form->dberror($query);
1184 $ref->{taxaccounts} = "";
1186 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1188 # if ($customertax{$ref->{accno}}) {
1189 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1193 $ref->{taxaccounts} .= "$ptr->{accno} ";
1194 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1195 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1196 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1197 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1198 $form->{taxaccounts} .= "$ptr->{accno} ";
1204 chop $ref->{taxaccounts};
1206 push @{ $form->{item_list} }, $ref;
1213 $main::lxdebug->leave_sub();
1216 sub vendor_details {
1217 $main::lxdebug->enter_sub();
1219 my ($self, $myconfig, $form, @wanted_vars) = @_;
1221 # connect to database
1222 my $dbh = $form->dbconnect($myconfig);
1224 # get contact id, set it if nessessary
1225 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1228 if ($form->{cp_id}) {
1229 $contact = "and cp.cp_id = $form->{cp_id}";
1232 # get rest for the vendor
1233 # fax and phone and email as vendor*
1235 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1237 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1238 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1239 my $sth = $dbh->prepare($query);
1240 $sth->execute || $form->dberror($query);
1242 $ref = $sth->fetchrow_hashref(NAME_lc);
1244 # remove id and taxincluded before copy back
1245 delete @$ref{qw(id taxincluded)};
1247 @wanted_vars = grep({ $_ } @wanted_vars);
1248 if (scalar(@wanted_vars) > 0) {
1250 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
1251 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
1254 map { $form->{$_} = $ref->{$_} } keys %$ref;
1259 $main::lxdebug->leave_sub();
1263 $main::lxdebug->enter_sub();
1265 my ($self, $myconfig, $form) = @_;
1267 # connect to database
1268 my $dbh = $form->dbconnect($myconfig);
1270 my $query = qq|SELECT c.accno, c.description, c.link
1272 WHERE c.link LIKE '%IC%'
1274 my $sth = $dbh->prepare($query);
1275 $sth->execute || $form->dberror($query);
1277 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1278 foreach my $key (split(/:/, $ref->{link})) {
1280 push @{ $form->{IC_links}{$key} },
1281 { accno => $ref->{accno},
1282 description => $ref->{description} };
1288 $main::lxdebug->leave_sub();
1292 $main::lxdebug->enter_sub();
1294 my ($myconfig, $form) = @_;
1297 $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1298 if ($form->{vc} eq "customer");
1299 $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1300 if ($form->{vc} eq "vendor");
1304 mkdir($path, 0770) or die "can't make directory $!\n";
1308 foreach $file (@files) {
1310 $file =~ /\/([^\/]*)$/;
1312 $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1314 $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1315 $form->{WEBDAV}{$fname} = $link;
1320 $main::lxdebug->leave_sub();
1324 $main::lxdebug->enter_sub();
1326 my ($self, $myconfig, $form, $locale) = @_;
1328 # connect to database, turn off autocommit
1329 my $dbh = $form->dbconnect_noauto($myconfig);
1331 $form->{datepaid} = $form->{invdate};
1333 # total payments, don't move we need it here
1334 for my $i (1 .. $form->{paidaccounts}) {
1335 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
1336 $form->{paid} += $form->{"paid_$i"};
1337 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
1340 $form->{exchangerate} =
1341 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
1344 # record payments and offsetting AP
1345 for my $i (1 .. $form->{paidaccounts}) {
1347 if ($form->{"paid_$i"} != 0) {
1348 my ($accno) = split /--/, $form->{"AP_paid_$i"};
1349 $form->{"datepaid_$i"} = $form->{invdate}
1350 unless ($form->{"datepaid_$i"});
1351 $form->{datepaid} = $form->{"datepaid_$i"};
1354 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
1355 $form->{"exchangerate_$i"} = 1;
1358 $form->check_exchangerate($myconfig, $form->{currency},
1359 $form->{"datepaid_$i"}, 'buy');
1361 $form->{"exchangerate_$i"} =
1364 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
1369 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
1373 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1374 WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
1375 $dbh->do($query) || $form->dberror($query);
1377 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1379 VALUES ($form->{id}, (SELECT c.id FROM chart c
1380 WHERE c.accno = '$form->{AP}'),
1381 $amount, '$form->{"datepaid_$i"}')|;
1382 $dbh->do($query) || $form->dberror($query);
1386 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1387 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
1388 $dbh->do($query) || $form->dberror($query);
1390 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1392 VALUES ($form->{id}, (SELECT c.id FROM chart c
1393 WHERE c.accno = '$accno'),
1394 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
1395 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
1396 $dbh->do($query) || $form->dberror($query);
1401 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1402 $form->{"exchangerate_$i"};
1404 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
1407 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
1413 # update exchange rate
1414 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
1415 $form->update_exchangerate($dbh, $form->{currency},
1416 $form->{"datepaid_$i"},
1417 $form->{"exchangerate_$i"}, 0);
1422 # record exchange rate differences and gains/losses
1423 foreach my $accno (keys %{ $form->{fx} }) {
1424 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1426 ($form->{fx}{$accno}{$transdate} =
1427 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
1430 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1431 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
1432 $dbh->do($query) || $form->dberror($query);
1433 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1434 transdate, cleared, fx_transaction)
1435 VALUES ($form->{id},
1436 (SELECT c.id FROM chart c
1437 WHERE c.accno = '$accno'),
1438 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
1439 $dbh->do($query) || $form->dberror($query);
1443 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
1446 my $query = qq|UPDATE ap set
1447 paid = $form->{paid},
1448 datepaid = $datepaid
1449 WHERE id=$form->{id}|;
1451 $dbh->do($query) || $form->dberror($query);
1453 my $rc = $dbh->commit;
1456 $main::lxdebug->leave_sub();