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 #======================================================================
40 $main::lxdebug->enter_sub();
42 my ($self, $myconfig, $form) = @_;
44 # connect to database, turn off autocommit
45 my $dbh = $form->dbconnect_noauto($myconfig);
47 my ($query, $sth, $null, $project_id);
55 my $service_units = AM->retrieve_units($myconfig,$form,"service");
56 my $part_units = AM->retrieve_units($myconfig,$form,"dimension");
60 &reverse_invoice($dbh, $form);
63 my $uid = rand() . time;
65 $uid .= $form->{login};
67 $uid = substr($uid, 2, 75);
69 $query = qq|INSERT INTO ap (invnumber, employee_id)
70 VALUES ('$uid', (SELECT e.id FROM employee e
71 WHERE e.login = '$form->{login}'))|;
72 $dbh->do($query) || $form->dberror($query);
74 $query = qq|SELECT a.id FROM ap a
75 WHERE a.invnumber = '$uid'|;
76 $sth = $dbh->prepare($query);
77 $sth->execute || $form->dberror($query);
79 ($form->{id}) = $sth->fetchrow_array;
83 ($null, $form->{contact_id}) = split /--/, $form->{contact};
84 $form->{contact_id} *= 1;
86 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
88 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
89 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
91 if ($form->{currency} eq $form->{defaultcurrency}) {
92 $form->{exchangerate} = 1;
95 $form->check_exchangerate($myconfig, $form->{currency},
96 $form->{transdate}, 'sell');
99 $form->{exchangerate} =
102 : $form->parse_amount($myconfig, $form->{exchangerate});
104 for my $i (1 .. $form->{rowcount}) {
105 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
107 if ($form->{storno}) {
108 $form->{"qty_$i"} *= -1;
111 if ($form->{"qty_$i"} != 0) {
114 $query = qq|SELECT p.unit
116 WHERE p.id = $form->{"id_$i"}|;
117 $sth = $dbh->prepare($query);
118 $sth->execute || $form->dberror($query);
120 my ($item_unit) = $sth->fetchrow_array();
123 if ($form->{"inventory_accno_$i"}) {
124 if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
125 $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
129 $baseqty = $form->{"qty_$i"} * $basefactor;
131 if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
132 $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
136 $baseqty = $form->{"qty_$i"} * $basefactor;
139 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
140 qw(partnumber description unit);
142 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
147 $form->{"sellprice_$i"} =
148 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
149 my $fxsellprice = $form->{"sellprice_$i"};
151 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
153 my $decimalplaces = ($dec > 2) ? $dec : 2;
155 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
157 if ($form->{"inventory_accno_$i"}) {
160 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
162 if ($form->{taxincluded}) {
163 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
164 $form->{"sellprice_$i"} =
165 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
167 $taxamount = $linetotal * $taxrate;
170 $netamount += $linetotal;
172 if ($form->round_amount($taxrate, 7) == 0) {
173 if ($form->{taxincluded}) {
174 foreach $item (@taxaccounts) {
176 $form->round_amount($linetotal * $form->{"${item}_rate"} /
177 (1 + abs($form->{"${item}_rate"})),
179 $taxdiff += $taxamount;
180 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
182 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
185 $form->{amount}{ $form->{id} }{$_} -=
186 $linetotal * $form->{"${_}_rate"}
191 $form->{amount}{ $form->{id} }{$_} -=
192 $taxamount * $form->{"${_}_rate"} / $taxrate
196 # add purchase to inventory, this one is without the tax!
198 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
200 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
201 $form->{exchangerate};
202 $linetotal = $form->round_amount($linetotal, 2);
204 # this is the difference for the inventory
205 $invoicediff += ($amount - $linetotal);
207 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
210 # adjust and round sellprice
211 $form->{"sellprice_$i"} =
212 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
216 $query = qq|UPDATE parts SET
217 lastcost = $form->{"sellprice_$i"}
218 WHERE id = $form->{"id_$i"}|;
220 $dbh->do($query) || $form->dberror($query);
222 $form->update_balance($dbh, "parts", "onhand",
223 qq|id = $form->{"id_$i"}|,
225 unless $form->{shipped};
227 # check if we sold the item already and
228 # make an entry for the expense and inventory
229 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
230 p.inventory_accno_id, p.expense_accno_id, a.transdate
231 FROM invoice i, ar a, parts p
232 WHERE i.parts_id = p.id
233 AND i.parts_id = $form->{"id_$i"}
234 AND (i.base_qty + i.allocated) > 0
235 AND i.trans_id = a.id
237 $sth = $dbh->prepare($query);
238 $sth->execute || $form->dberror($query);
240 my $totalqty = $base_qty;
242 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
244 my $qty = $ref->{base_qty} + $ref->{allocated};
246 if (($qty - $totalqty) > 0) {
250 $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
252 if ($ref->{allocated} < 0) {
254 # we have an entry for it already, adjust amount
255 $form->update_balance(
259 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
262 $form->update_balance(
266 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
271 # add entry for inventory, this one is for the sold item
272 if ($linetotal != 0) {
273 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
275 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
276 $linetotal, '$ref->{transdate}')|;
277 $dbh->do($query) || $form->dberror($query);
280 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
282 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
283 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
284 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
285 $dbh->do($query) || $form->dberror($query);
289 # update allocated for sold item
290 $form->update_balance($dbh, "invoice", "allocated",
296 last if (($totalqty -= $qty) <= 0);
301 $lastinventoryaccno = $form->{"inventory_accno_$i"};
306 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
308 if ($form->{taxincluded}) {
309 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
311 $form->{"sellprice_$i"} =
312 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
314 $taxamount = $linetotal * $taxrate;
317 $netamount += $linetotal;
319 if ($form->round_amount($taxrate, 7) == 0) {
320 if ($form->{taxincluded}) {
321 foreach $item (@taxaccounts) {
323 $linetotal * $form->{"${item}_rate"} /
324 (1 + abs($form->{"${item}_rate"}));
325 $totaltax += $taxamount;
326 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
330 $form->{amount}{ $form->{id} }{$_} -=
331 $linetotal * $form->{"${_}_rate"}
336 $form->{amount}{ $form->{id} }{$_} -=
337 $taxamount * $form->{"${_}_rate"} / $taxrate
342 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
344 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
345 $form->{exchangerate};
346 $linetotal = $form->round_amount($linetotal, 2);
348 # this is the difference for expense
349 $expensediff += ($amount - $linetotal);
351 # add amount to expense
352 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
355 $lastexpenseaccno = $form->{"expense_accno_$i"};
357 # adjust and round sellprice
358 $form->{"sellprice_$i"} =
359 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
363 $query = qq|UPDATE parts SET
364 lastcost = $form->{"sellprice_$i"}
365 WHERE id = $form->{"id_$i"}|;
367 $dbh->do($query) || $form->dberror($query);
371 $project_id = 'NULL';
372 if ($form->{"projectnumber_$i"}) {
373 $project_id = $form->{"projectnumber_$i"};
376 ($form->{"deliverydate_$i"})
377 ? qq|'$form->{"deliverydate_$i"}'|
380 # save detail record in invoice table
381 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
382 sellprice, fxsellprice, allocated, unit, deliverydate,
383 project_id, serialnumber)
384 VALUES ($form->{id}, $form->{"id_$i"},
385 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
386 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
387 '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
388 '$form->{"serialnumber_$i"}')|;
389 $dbh->do($query) || $form->dberror($query);
393 $form->{datepaid} = $form->{invdate};
395 # all amounts are in natural state, netamount includes the taxes
396 # if tax is included, netamount is rounded to 2 decimal places,
400 for my $i (1 .. $form->{paidaccounts}) {
401 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
402 $form->{paid} += $form->{"paid_$i"};
403 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
406 my ($tax, $paiddiff) = (0, 0);
408 $netamount = $form->round_amount($netamount, 2);
410 # figure out rounding errors for amount paid and total amount
411 if ($form->{taxincluded}) {
413 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
414 $paiddiff = $amount - $netamount * $form->{exchangerate};
415 $netamount = $amount;
417 foreach $item (split / /, $form->{taxaccounts}) {
418 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
419 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
420 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
422 $netamount -= $amount;
425 $invoicediff += $paiddiff;
426 $expensediff += $paiddiff;
428 ######## this only applies to tax included
429 if ($lastinventoryaccno) {
430 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
432 if ($lastexpenseaccno) {
433 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
437 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
438 $paiddiff = $amount - $netamount * $form->{exchangerate};
439 $netamount = $amount;
440 foreach my $item (split / /, $form->{taxaccounts}) {
441 $form->{amount}{ $form->{id} }{$item} =
442 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
445 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
448 $amount - $form->{amount}{ $form->{id} }{$item} *
449 $form->{exchangerate} * -1;
450 $form->{amount}{ $form->{id} }{$item} =
451 $form->round_amount($amount * -1, 2);
452 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
457 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
459 if ($form->{paid} != 0) {
461 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
465 # update exchangerate
466 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
467 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
468 $form->{exchangerate});
471 # record acc_trans transactions
472 foreach my $trans_id (keys %{ $form->{amount} }) {
473 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
475 ($form->{amount}{$trans_id}{$accno} =
476 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
479 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
481 VALUES ($trans_id, (SELECT c.id FROM chart c
482 WHERE c.accno = '$accno'),
483 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
484 (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
485 $dbh->do($query) || $form->dberror($query);
490 # deduct payment differences from paiddiff
491 for my $i (1 .. $form->{paidaccounts}) {
492 if ($form->{"paid_$i"} != 0) {
494 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
495 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
499 # force AP entry if 0
500 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
501 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
503 # record payments and offsetting AP
504 for my $i (1 .. $form->{paidaccounts}) {
506 if ($form->{"paid_$i"} != 0) {
507 my ($accno) = split /--/, $form->{"AP_paid_$i"};
508 $form->{"datepaid_$i"} = $form->{invdate}
509 unless ($form->{"datepaid_$i"});
510 $form->{datepaid} = $form->{"datepaid_$i"};
514 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
520 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
521 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
523 VALUES ($form->{id}, (SELECT c.id FROM chart c
524 WHERE c.accno = '$form->{AP}'),
525 $amount, '$form->{"datepaid_$i"}')|;
526 $dbh->do($query) || $form->dberror($query);
531 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
533 VALUES ($form->{id}, (SELECT c.id FROM chart c
534 WHERE c.accno = '$accno'),
535 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
536 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
537 $dbh->do($query) || $form->dberror($query);
541 if ($form->{currency} eq $form->{defaultcurrency}) {
542 $form->{"exchangerate_$i"} = 1;
545 $form->check_exchangerate($myconfig, $form->{currency},
546 $form->{"datepaid_$i"}, 'sell');
548 $form->{"exchangerate_$i"} =
551 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
554 # exchangerate difference
555 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
556 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
560 ($form->{"paid_$i"} * $form->{exchangerate}) -
561 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
563 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
566 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
572 # update exchange rate
573 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
574 $form->update_exchangerate($dbh, $form->{currency},
575 $form->{"datepaid_$i"},
576 0, $form->{"exchangerate_$i"});
581 # record exchange rate differences and gains/losses
582 foreach my $accno (keys %{ $form->{fx} }) {
583 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
585 ($form->{fx}{$accno}{$transdate} =
586 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
590 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
591 transdate, cleared, fx_transaction)
592 VALUES ($form->{id}, (SELECT c.id FROM chart c
593 WHERE c.accno = '$accno'),
594 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
595 $dbh->do($query) || $form->dberror($query);
600 $amount = $netamount + $tax;
602 # set values which could be empty
603 $form->{taxincluded} *= 1;
604 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
605 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
607 ($null, $form->{department_id}) = split(/--/, $form->{department});
608 $form->{department_id} *= 1;
609 $form->{payment_id} *= 1;
610 $form->{language_id} *= 1;
611 $form->{taxzone_id} *= 1;
612 $form->{storno} *= 1;
615 $form->{invnumber} = $form->{id} unless $form->{invnumber};
618 $query = qq|UPDATE ap set
619 invnumber = '$form->{invnumber}',
620 ordnumber = '$form->{ordnumber}',
621 quonumber = '$form->{quonumber}',
622 transdate = '$form->{invdate}',
623 vendor_id = $form->{vendor_id},
625 netamount = $netamount,
626 paid = $form->{paid},
627 datepaid = $datepaid,
630 taxzone_id = '$form->{taxzone_id}',
631 taxincluded = '$form->{taxincluded}',
632 notes = '$form->{notes}',
633 intnotes = '$form->{intnotes}',
634 curr = '$form->{currency}',
635 department_id = $form->{department_id},
636 storno = '$form->{storno}',
637 cp_id = $form->{contact_id}
638 WHERE id = $form->{id}|;
639 $dbh->do($query) || $form->dberror($query);
641 if ($form->{storno}) {
642 $query = qq| update ap set paid=paid+amount where id=$form->{storno_id}|;
643 $dbh->do($query) || $form->dberror($query);
644 $query = qq| update ap set storno='$form->{storno}' where id=$form->{storno_id}|;
645 $dbh->do($query) || $form->dberror($query);
646 $query = qq§ update ap set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}§;
647 $dbh->do($query) || $form->dberror($query);
649 $query = qq| update ap set paid=amount where id=$form->{id}|;
650 $dbh->do($query) || $form->dberror($query);
655 $form->{name} = $form->{vendor};
656 $form->{name} =~ s/--$form->{vendor_id}//;
657 $form->add_shipto($dbh, $form->{id}, "AP");
659 # delete zero entries
660 $query = qq|DELETE FROM acc_trans
662 $dbh->do($query) || $form->dberror($query);
664 if ($form->{webdav}) {
665 &webdav_folder($myconfig, $form);
668 my $rc = $dbh->commit;
671 $main::lxdebug->leave_sub();
676 sub reverse_invoice {
677 $main::lxdebug->enter_sub();
679 my ($dbh, $form) = @_;
681 # reverse inventory items
682 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
683 i.qty, i.allocated, i.sellprice
684 FROM invoice i, parts p
685 WHERE i.parts_id = p.id
686 AND i.trans_id = $form->{id}|;
687 my $sth = $dbh->prepare($query);
688 $sth->execute || $form->dberror($query);
692 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
693 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
695 if ($ref->{inventory_accno_id}) {
698 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
701 # if $ref->{allocated} > 0 than we sold that many items
702 if ($ref->{allocated} > 0) {
704 # get references for sold items
705 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
707 WHERE i.parts_id = $ref->{parts_id}
709 AND i.trans_id = a.id
710 ORDER BY transdate DESC|;
711 my $sth = $dbh->prepare($query);
712 $sth->execute || $form->dberror($query);
714 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
715 my $qty = $ref->{allocated};
716 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
717 $qty = $pthref->{allocated} * -1;
720 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
723 $form->update_balance($dbh, "invoice", "allocated",
724 qq|id = $pthref->{id}|, $qty);
726 $form->update_balance(
730 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
733 $form->update_balance(
737 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
740 last if (($ref->{allocated} -= $qty) <= 0);
749 $query = qq|DELETE FROM acc_trans
750 WHERE trans_id = $form->{id}|;
751 $dbh->do($query) || $form->dberror($query);
753 # delete invoice entries
754 $query = qq|DELETE FROM invoice
755 WHERE trans_id = $form->{id}|;
756 $dbh->do($query) || $form->dberror($query);
758 $query = qq|DELETE FROM shipto
759 WHERE trans_id = $form->{id} AND module = 'AP'|;
760 $dbh->do($query) || $form->dberror($query);
762 $main::lxdebug->leave_sub();
766 $main::lxdebug->enter_sub();
768 my ($self, $myconfig, $form) = @_;
770 # connect to database
771 my $dbh = $form->dbconnect_noauto($myconfig);
773 &reverse_invoice($dbh, $form);
775 # delete zero entries
776 my $query = qq|DELETE FROM acc_trans
778 $dbh->do($query) || $form->dberror($query);
781 my $query = qq|DELETE FROM ap
782 WHERE id = $form->{id}|;
783 $dbh->do($query) || $form->dberror($query);
785 my $rc = $dbh->commit;
788 $main::lxdebug->leave_sub();
793 sub retrieve_invoice {
794 $main::lxdebug->enter_sub();
796 my ($self, $myconfig, $form) = @_;
798 # connect to database
799 my $dbh = $form->dbconnect_noauto($myconfig);
805 # get default accounts and last invoice number
806 $query = qq|SELECT (SELECT c.accno FROM chart c
807 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
808 (SELECT c.accno FROM chart c
809 WHERE d.income_accno_id = c.id) AS income_accno,
810 (SELECT c.accno FROM chart c
811 WHERE d.expense_accno_id = c.id) AS expense_accno,
812 (SELECT c.accno FROM chart c
813 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
814 (SELECT c.accno FROM chart c
815 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
819 $query = qq|SELECT (SELECT c.accno FROM chart c
820 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
821 (SELECT c.accno FROM chart c
822 WHERE d.income_accno_id = c.id) AS income_accno,
823 (SELECT c.accno FROM chart c
824 WHERE d.expense_accno_id = c.id) AS expense_accno,
825 (SELECT c.accno FROM chart c
826 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
827 (SELECT c.accno FROM chart c
828 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
829 d.curr AS currencies,
830 current_date AS invdate
833 my $sth = $dbh->prepare($query);
834 $sth->execute || $form->dberror($query);
836 my $ref = $sth->fetchrow_hashref(NAME_lc);
837 map { $form->{$_} = $ref->{$_} } keys %$ref;
843 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
844 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno,
845 a.intnotes, a.curr AS currency
847 WHERE a.id = $form->{id}|;
848 $sth = $dbh->prepare($query);
849 $sth->execute || $form->dberror($query);
851 $ref = $sth->fetchrow_hashref(NAME_lc);
852 map { $form->{$_} = $ref->{$_} } keys %$ref;
855 $form->{exchangerate} =
856 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
860 $query = qq|SELECT s.* FROM shipto s
861 WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
862 $sth = $dbh->prepare($query);
863 $sth->execute || $form->dberror($query);
865 $ref = $sth->fetchrow_hashref(NAME_lc);
867 map { $form->{$_} = $ref->{$_} } keys %$ref;
871 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
873 if(!$form->{taxzone_id}) {
874 $form->{taxzone_id} = 0;
876 # retrieve individual items
877 $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
878 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
879 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
880 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
881 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
883 i.project_id, i.serialnumber,
886 JOIN parts p ON (i.parts_id = p.id)
887 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
888 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
889 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
890 LEFT JOIN project pr ON (i.project_id = pr.id)
891 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
892 WHERE i.trans_id = $form->{id}
894 $sth = $dbh->prepare($query);
895 $sth->execute || $form->dberror($query);
897 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
898 if (!$ref->{"part_inventory_accno_id"}) {
899 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
901 delete($ref->{"part_inventory_accno_id"});
903 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
904 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}|;
905 my $stw = $dbh->prepare($query);
906 $stw->execute || $form->dberror($query);
907 ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
911 while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
912 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}|;
913 my $stw = $dbh->prepare($query);
914 $stw->execute || $form->dberror($query);
915 ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
919 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
920 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}|;
921 my $stw = $dbh->prepare($query);
922 $stw->execute || $form->dberror($query);
923 ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
927 # get tax rates and description
929 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
930 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
931 FROM tax t LEFT join chart c ON (c.id=t.chart_id)
932 WHERE t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
934 $stw = $dbh->prepare($query);
935 $stw->execute || $form->dberror($query);
936 $ref->{taxaccounts} = "";
938 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
940 # if ($customertax{$ref->{accno}}) {
941 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
945 $ref->{taxaccounts} .= "$ptr->{accno} ";
946 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
947 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
948 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
949 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
950 $form->{taxaccounts} .= "$ptr->{accno} ";
955 chop $ref->{taxaccounts};
956 push @{ $form->{invoice_details} }, $ref;
961 if ($form->{webdav}) {
962 &webdav_folder($myconfig, $form);
967 my $rc = $dbh->commit;
970 $main::lxdebug->leave_sub();
976 $main::lxdebug->enter_sub();
978 my ($self, $myconfig, $form) = @_;
980 # connect to database
981 my $dbh = $form->dbconnect($myconfig);
983 my $dateformat = $myconfig->{dateformat};
984 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
988 ? "to_date('$form->{invdate}', '$dateformat')"
991 $form->{vendor_id} *= 1;
994 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
995 v.email, v.cc, v.bcc, v.language_id, v.payment_id,
996 v.street, v.zipcode, v.city, v.country, v.taxzone_id,
997 $duedate + v.terms AS duedate
999 WHERE v.id = $form->{vendor_id}|;
1000 my $sth = $dbh->prepare($query);
1001 $sth->execute || $form->dberror($query);
1003 $ref = $sth->fetchrow_hashref(NAME_lc);
1004 map { $form->{$_} = $ref->{$_} } keys %$ref;
1007 $form->{creditremaining} = $form->{creditlimit};
1008 $query = qq|SELECT SUM(a.amount - a.paid)
1010 WHERE a.vendor_id = $form->{vendor_id}|;
1011 $sth = $dbh->prepare($query);
1012 $sth->execute || $form->dberror($query);
1014 ($form->{creditremaining}) -= $sth->fetchrow_array;
1018 $query = qq|SELECT o.amount,
1019 (SELECT e.sell FROM exchangerate e
1020 WHERE e.curr = o.curr
1021 AND e.transdate = o.transdate)
1023 WHERE o.vendor_id = $form->{vendor_id}
1024 AND o.quotation = '0'
1025 AND o.closed = '0'|;
1026 $sth = $dbh->prepare($query);
1027 $sth->execute || $form->dberror($query);
1029 while (my ($amount, $exch) = $sth->fetchrow_array) {
1030 $exch = 1 unless $exch;
1031 $form->{creditremaining} -= $amount * $exch;
1035 $form->get_contacts($dbh, $form->{vendor_id});
1037 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1039 # get contact if selected
1040 if ($form->{contact} ne "--" && $form->{contact} ne "") {
1041 $form->get_contact($dbh, $form->{cp_id});
1044 # get shipto if we do not convert an order or invoice
1045 if (!$form->{shipto}) {
1046 map { delete $form->{$_} }
1047 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
1049 $query = qq|SELECT s.* FROM shipto s
1050 WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
1051 $sth = $dbh->prepare($query);
1052 $sth->execute || $form->dberror($query);
1054 $ref = $sth->fetchrow_hashref(NAME_lc);
1055 map { $form->{$_} = $ref->{$_} } keys %$ref;
1059 # get taxes for vendor
1060 $query = qq|SELECT c.accno
1062 JOIN vendortax v ON (v.chart_id = c.id)
1063 WHERE v.vendor_id = $form->{vendor_id}|;
1064 $sth = $dbh->prepare($query);
1065 $sth->execute || $form->dberror($query);
1068 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1069 $vendortax{ $ref->{accno} } = 1;
1073 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1075 # setup last accounts used
1076 $query = qq|SELECT c.accno, c.description, c.link, c.category
1078 JOIN acc_trans ac ON (ac.chart_id = c.id)
1079 JOIN ap a ON (a.id = ac.trans_id)
1080 WHERE a.vendor_id = $form->{vendor_id}
1081 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1082 AND a.id IN (SELECT max(a2.id) FROM ap a2
1083 WHERE a2.vendor_id = $form->{vendor_id})|;
1084 $sth = $dbh->prepare($query);
1085 $sth->execute || $form->dberror($query);
1088 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1089 if ($ref->{category} eq 'E') {
1091 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1093 if ($ref->{category} eq 'L') {
1094 $form->{APselected} = $form->{AP_1} =
1095 "$ref->{accno}--$ref->{description}";
1099 $form->{rowcount} = $i if ($i && !$form->{type});
1104 $main::lxdebug->leave_sub();
1108 $main::lxdebug->enter_sub();
1110 my ($self, $myconfig, $form) = @_;
1112 # connect to database
1113 my $dbh = $form->dbconnect($myconfig);
1115 my $i = $form->{rowcount};
1117 # don't include assemblies or obsolete parts
1118 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1120 if ($form->{"partnumber_$i"}) {
1121 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1122 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1125 if ($form->{"description_$i"}) {
1126 my $description = $form->like(lc $form->{"description_$i"});
1127 $where .= " AND lower(p.description) LIKE '$description'";
1130 if ($form->{"partsgroup_$i"}) {
1131 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1132 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1135 if ($form->{"description_$i"}) {
1136 $where .= " ORDER BY p.description";
1138 $where .= " ORDER BY p.partnumber";
1142 if ($form->{type} eq "invoice") {
1144 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1147 $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1150 my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
1151 p.listprice, p.inventory_accno_id,
1152 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1153 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1154 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1155 p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
1156 pg.partsgroup, p.formel
1158 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
1159 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
1160 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
1161 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1163 my $sth = $dbh->prepare($query);
1164 $sth->execute || $form->dberror($query);
1166 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1168 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1169 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1170 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1171 if (!$ref->{inventory_accno_id}) {
1172 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1174 delete($ref->{inventory_accno_id});
1176 # get tax rates and description
1178 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1179 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1180 FROM tax t LEFT JOIN chart c ON (c.id=t.chart_id)
1181 WHERE t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
1183 $stw = $dbh->prepare($query);
1184 $stw->execute || $form->dberror($query);
1186 $ref->{taxaccounts} = "";
1188 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1190 # if ($customertax{$ref->{accno}}) {
1191 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1195 $ref->{taxaccounts} .= "$ptr->{accno} ";
1196 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1197 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1198 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1199 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1200 $form->{taxaccounts} .= "$ptr->{accno} ";
1206 chop $ref->{taxaccounts};
1208 push @{ $form->{item_list} }, $ref;
1215 $main::lxdebug->leave_sub();
1218 sub vendor_details {
1219 $main::lxdebug->enter_sub();
1221 my ($self, $myconfig, $form) = @_;
1223 # connect to database
1224 my $dbh = $form->dbconnect($myconfig);
1226 # get contact id, set it if nessessary
1227 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1230 if ($form->{cp_id}) {
1231 $contact = "and cp.cp_id = $form->{cp_id}";
1234 # get rest for the vendor
1235 # fax and phone and email as vendor*
1237 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1239 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1240 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1241 my $sth = $dbh->prepare($query);
1242 $sth->execute || $form->dberror($query);
1244 $ref = $sth->fetchrow_hashref(NAME_lc);
1246 # remove id and taxincluded before copy back
1247 delete @$ref{qw(id taxincluded)};
1248 map { $form->{$_} = $ref->{$_} } keys %$ref;
1253 $main::lxdebug->leave_sub();
1257 $main::lxdebug->enter_sub();
1259 my ($self, $myconfig, $form) = @_;
1261 # connect to database
1262 my $dbh = $form->dbconnect($myconfig);
1264 my $query = qq|SELECT c.accno, c.description, c.link
1266 WHERE c.link LIKE '%IC%'
1268 my $sth = $dbh->prepare($query);
1269 $sth->execute || $form->dberror($query);
1271 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1272 foreach my $key (split(/:/, $ref->{link})) {
1274 push @{ $form->{IC_links}{$key} },
1275 { accno => $ref->{accno},
1276 description => $ref->{description} };
1282 $main::lxdebug->leave_sub();
1286 $main::lxdebug->enter_sub();
1288 my ($myconfig, $form) = @_;
1291 $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1292 if ($form->{vc} eq "customer");
1293 $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1294 if ($form->{vc} eq "vendor");
1298 mkdir($path, 0770) or die "can't make directory $!\n";
1302 foreach $file (@files) {
1304 $file =~ /\/([^\/]*)$/;
1306 $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1308 $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1309 $form->{WEBDAV}{$fname} = $link;
1314 $main::lxdebug->leave_sub();
1318 $main::lxdebug->enter_sub();
1320 my ($self, $myconfig, $form, $locale) = @_;
1322 # connect to database, turn off autocommit
1323 my $dbh = $form->dbconnect_noauto($myconfig);
1325 $form->{datepaid} = $form->{invdate};
1327 # total payments, don't move we need it here
1328 for my $i (1 .. $form->{paidaccounts}) {
1329 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
1330 $form->{paid} += $form->{"paid_$i"};
1331 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
1334 $form->{exchangerate} =
1335 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
1338 # record payments and offsetting AP
1339 for my $i (1 .. $form->{paidaccounts}) {
1341 if ($form->{"paid_$i"} != 0) {
1342 my ($accno) = split /--/, $form->{"AP_paid_$i"};
1343 $form->{"datepaid_$i"} = $form->{invdate}
1344 unless ($form->{"datepaid_$i"});
1345 $form->{datepaid} = $form->{"datepaid_$i"};
1348 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
1349 $form->{"exchangerate_$i"} = 1;
1352 $form->check_exchangerate($myconfig, $form->{currency},
1353 $form->{"datepaid_$i"}, 'buy');
1355 $form->{"exchangerate_$i"} =
1358 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
1363 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
1367 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1368 WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
1369 $dbh->do($query) || $form->dberror($query);
1371 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1373 VALUES ($form->{id}, (SELECT c.id FROM chart c
1374 WHERE c.accno = '$form->{AP}'),
1375 $amount, '$form->{"datepaid_$i"}')|;
1376 $dbh->do($query) || $form->dberror($query);
1380 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1381 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
1382 $dbh->do($query) || $form->dberror($query);
1384 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1386 VALUES ($form->{id}, (SELECT c.id FROM chart c
1387 WHERE c.accno = '$accno'),
1388 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
1389 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
1390 $dbh->do($query) || $form->dberror($query);
1395 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1396 $form->{"exchangerate_$i"};
1398 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
1401 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
1407 # update exchange rate
1408 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
1409 $form->update_exchangerate($dbh, $form->{currency},
1410 $form->{"datepaid_$i"},
1411 $form->{"exchangerate_$i"}, 0);
1416 # record exchange rate differences and gains/losses
1417 foreach my $accno (keys %{ $form->{fx} }) {
1418 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1420 ($form->{fx}{$accno}{$transdate} =
1421 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
1424 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1425 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
1426 $dbh->do($query) || $form->dberror($query);
1427 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1428 transdate, cleared, fx_transaction)
1429 VALUES ($form->{id},
1430 (SELECT c.id FROM chart c
1431 WHERE c.accno = '$accno'),
1432 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
1433 $dbh->do($query) || $form->dberror($query);
1437 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
1440 my $query = qq|UPDATE ap set
1441 paid = $form->{paid},
1442 datepaid = $datepaid
1443 WHERE id=$form->{id}|;
1445 $dbh->do($query) || $form->dberror($query);
1447 my $rc = $dbh->commit;
1450 $main::lxdebug->leave_sub();