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 #======================================================================
42 $main::lxdebug->enter_sub();
44 my ($self, $myconfig, $form) = @_;
46 # connect to database, turn off autocommit
47 my $dbh = $form->dbconnect_noauto($myconfig);
49 my ($query, $sth, $null, $project_id);
57 my $service_units = AM->retrieve_units($myconfig,$form,"service");
58 my $part_units = AM->retrieve_units($myconfig,$form,"dimension");
62 &reverse_invoice($dbh, $form);
65 my $uid = rand() . time;
67 $uid .= $form->{login};
69 $uid = substr($uid, 2, 75);
71 $query = qq|INSERT INTO ap (invnumber, employee_id)
72 VALUES ('$uid', (SELECT e.id FROM employee e
73 WHERE e.login = '$form->{login}'))|;
74 $dbh->do($query) || $form->dberror($query);
76 $query = qq|SELECT a.id FROM ap a
77 WHERE a.invnumber = '$uid'|;
78 $sth = $dbh->prepare($query);
79 $sth->execute || $form->dberror($query);
81 ($form->{id}) = $sth->fetchrow_array;
85 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
87 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
88 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
90 if ($form->{currency} eq $form->{defaultcurrency}) {
91 $form->{exchangerate} = 1;
94 $form->check_exchangerate($myconfig, $form->{currency},
95 $form->{transdate}, 'sell');
98 $form->{exchangerate} =
101 : $form->parse_amount($myconfig, $form->{exchangerate});
103 for my $i (1 .. $form->{rowcount}) {
104 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
106 if ($form->{storno}) {
107 $form->{"qty_$i"} *= -1;
111 $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
114 if ($form->{"id_$i"}) {
117 $query = qq|SELECT p.unit
119 WHERE p.id = $form->{"id_$i"}|;
120 $sth = $dbh->prepare($query);
121 $sth->execute || $form->dberror($query);
123 my ($item_unit) = $sth->fetchrow_array();
126 if ($form->{"inventory_accno_$i"}) {
127 if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
128 $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
132 $baseqty = $form->{"qty_$i"} * $basefactor;
134 if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
135 $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
139 $baseqty = $form->{"qty_$i"} * $basefactor;
142 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
143 qw(partnumber description unit);
145 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
150 $form->{"sellprice_$i"} =
151 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
152 my $fxsellprice = $form->{"sellprice_$i"};
154 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
156 my $decimalplaces = ($dec > 2) ? $dec : 2;
158 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
160 if ($form->{"inventory_accno_$i"}) {
163 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
165 if ($form->{taxincluded}) {
166 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
167 $form->{"sellprice_$i"} =
168 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
170 $taxamount = $linetotal * $taxrate;
173 $netamount += $linetotal;
175 if ($form->round_amount($taxrate, 7) == 0) {
176 if ($form->{taxincluded}) {
177 foreach $item (@taxaccounts) {
179 $form->round_amount($linetotal * $form->{"${item}_rate"} /
180 (1 + abs($form->{"${item}_rate"})),
182 $taxdiff += $taxamount;
183 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
185 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
188 $form->{amount}{ $form->{id} }{$_} -=
189 $linetotal * $form->{"${_}_rate"}
194 $form->{amount}{ $form->{id} }{$_} -=
195 $taxamount * $form->{"${_}_rate"} / $taxrate
199 # add purchase to inventory, this one is without the tax!
201 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
203 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
204 $form->{exchangerate};
205 $linetotal = $form->round_amount($linetotal, 2);
207 # this is the difference for the inventory
208 $invoicediff += ($amount - $linetotal);
210 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
213 # adjust and round sellprice
214 $form->{"sellprice_$i"} =
215 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
219 $query = qq|UPDATE parts SET
220 lastcost = $form->{"sellprice_$i"}
221 WHERE id = $form->{"id_$i"}|;
223 $dbh->do($query) || $form->dberror($query);
225 $form->update_balance($dbh, "parts", "onhand",
226 qq|id = $form->{"id_$i"}|,
228 unless $form->{shipped};
230 # check if we sold the item already and
231 # make an entry for the expense and inventory
232 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
233 p.inventory_accno_id, p.expense_accno_id, a.transdate
234 FROM invoice i, ar a, parts p
235 WHERE i.parts_id = p.id
236 AND i.parts_id = $form->{"id_$i"}
237 AND (i.base_qty + i.allocated) > 0
238 AND i.trans_id = a.id
240 $sth = $dbh->prepare($query);
241 $sth->execute || $form->dberror($query);
243 my $totalqty = $base_qty;
245 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
247 my $qty = $ref->{base_qty} + $ref->{allocated};
249 if (($qty - $totalqty) > 0) {
253 $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
255 if ($ref->{allocated} < 0) {
257 # we have an entry for it already, adjust amount
258 $form->update_balance(
262 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
265 $form->update_balance(
269 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
274 # add entry for inventory, this one is for the sold item
275 if ($linetotal != 0) {
276 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
278 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
279 $linetotal, '$ref->{transdate}')|;
280 $dbh->do($query) || $form->dberror($query);
283 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
285 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
286 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
287 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
288 $dbh->do($query) || $form->dberror($query);
292 # update allocated for sold item
293 $form->update_balance($dbh, "invoice", "allocated",
299 last if (($totalqty -= $qty) <= 0);
304 $lastinventoryaccno = $form->{"inventory_accno_$i"};
309 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
311 if ($form->{taxincluded}) {
312 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
314 $form->{"sellprice_$i"} =
315 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
317 $taxamount = $linetotal * $taxrate;
320 $netamount += $linetotal;
322 if ($form->round_amount($taxrate, 7) == 0) {
323 if ($form->{taxincluded}) {
324 foreach $item (@taxaccounts) {
326 $linetotal * $form->{"${item}_rate"} /
327 (1 + abs($form->{"${item}_rate"}));
328 $totaltax += $taxamount;
329 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
333 $form->{amount}{ $form->{id} }{$_} -=
334 $linetotal * $form->{"${_}_rate"}
339 $form->{amount}{ $form->{id} }{$_} -=
340 $taxamount * $form->{"${_}_rate"} / $taxrate
345 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
347 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
348 $form->{exchangerate};
349 $linetotal = $form->round_amount($linetotal, 2);
351 # this is the difference for expense
352 $expensediff += ($amount - $linetotal);
354 # add amount to expense
355 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
358 $lastexpenseaccno = $form->{"expense_accno_$i"};
360 # adjust and round sellprice
361 $form->{"sellprice_$i"} =
362 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
366 $query = qq|UPDATE parts SET
367 lastcost = $form->{"sellprice_$i"}
368 WHERE id = $form->{"id_$i"}|;
370 $dbh->do($query) || $form->dberror($query);
375 ($form->{"deliverydate_$i"})
376 ? qq|'$form->{"deliverydate_$i"}'|
379 # save detail record in invoice table
380 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
381 sellprice, fxsellprice, allocated, unit, deliverydate,
382 project_id, serialnumber)
383 VALUES ($form->{id}, $form->{"id_$i"},
384 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
385 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
386 '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}) . qq|,
387 '$form->{"serialnumber_$i"}')|;
388 $dbh->do($query) || $form->dberror($query);
392 $project_id = conv_i($form->{"globalproject_id"});
394 $form->{datepaid} = $form->{invdate};
396 # all amounts are in natural state, netamount includes the taxes
397 # if tax is included, netamount is rounded to 2 decimal places,
401 for my $i (1 .. $form->{paidaccounts}) {
402 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
403 $form->{paid} += $form->{"paid_$i"};
404 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
407 my ($tax, $paiddiff) = (0, 0);
409 $netamount = $form->round_amount($netamount, 2);
411 # figure out rounding errors for amount paid and total amount
412 if ($form->{taxincluded}) {
414 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
415 $paiddiff = $amount - $netamount * $form->{exchangerate};
416 $netamount = $amount;
418 foreach $item (split / /, $form->{taxaccounts}) {
419 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
420 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
421 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
423 $netamount -= $amount;
426 $invoicediff += $paiddiff;
427 $expensediff += $paiddiff;
429 ######## this only applies to tax included
430 if ($lastinventoryaccno) {
431 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
433 if ($lastexpenseaccno) {
434 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
438 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
439 $paiddiff = $amount - $netamount * $form->{exchangerate};
440 $netamount = $amount;
441 foreach my $item (split / /, $form->{taxaccounts}) {
442 $form->{amount}{ $form->{id} }{$item} =
443 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
446 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
449 $amount - $form->{amount}{ $form->{id} }{$item} *
450 $form->{exchangerate} * -1;
451 $form->{amount}{ $form->{id} }{$item} =
452 $form->round_amount($amount * -1, 2);
453 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
458 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
460 if ($form->{paid} != 0) {
462 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
466 # update exchangerate
467 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
468 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
469 $form->{exchangerate});
472 # record acc_trans transactions
473 foreach my $trans_id (keys %{ $form->{amount} }) {
474 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
476 ($form->{amount}{$trans_id}{$accno} =
477 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
480 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
481 transdate, taxkey, project_id)
482 VALUES ($trans_id, (SELECT c.id FROM chart c
483 WHERE c.accno = '$accno'),
484 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
485 (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|;
486 do_query($form, $dbh, $query, $project_id);
491 # deduct payment differences from paiddiff
492 for my $i (1 .. $form->{paidaccounts}) {
493 if ($form->{"paid_$i"} != 0) {
495 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
496 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
500 # force AP entry if 0
501 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
502 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
504 # record payments and offsetting AP
505 for my $i (1 .. $form->{paidaccounts}) {
507 if ($form->{"paid_$i"} != 0) {
508 my ($accno) = split /--/, $form->{"AP_paid_$i"};
509 $form->{"datepaid_$i"} = $form->{invdate}
510 unless ($form->{"datepaid_$i"});
511 $form->{datepaid} = $form->{"datepaid_$i"};
515 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
521 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
522 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
523 transdate, project_id)
524 VALUES ($form->{id}, (SELECT c.id FROM chart c
525 WHERE c.accno = '$form->{AP}'),
526 $amount, '$form->{"datepaid_$i"}', ?)|;
527 do_query($form, $dbh, $query, $project_id);
532 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
533 source, memo, project_id)
534 VALUES ($form->{id}, (SELECT c.id FROM chart c
535 WHERE c.accno = '$accno'),
536 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
537 '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
538 do_query($form, $dbh, $query, $project_id);
542 if ($form->{currency} eq $form->{defaultcurrency}) {
543 $form->{"exchangerate_$i"} = 1;
546 $form->check_exchangerate($myconfig, $form->{currency},
547 $form->{"datepaid_$i"}, 'sell');
549 $form->{"exchangerate_$i"} =
552 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
555 # exchangerate difference
556 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
557 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
561 ($form->{"paid_$i"} * $form->{exchangerate}) -
562 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
564 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
567 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
573 # update exchange rate
574 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
575 $form->update_exchangerate($dbh, $form->{currency},
576 $form->{"datepaid_$i"},
577 0, $form->{"exchangerate_$i"});
582 # record exchange rate differences and gains/losses
583 foreach my $accno (keys %{ $form->{fx} }) {
584 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
586 ($form->{fx}{$accno}{$transdate} =
587 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
591 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
592 transdate, cleared, fx_transaction, project_id)
593 VALUES ($form->{id}, (SELECT c.id FROM chart c
594 WHERE c.accno = '$accno'),
595 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
596 do_query($form, $dbh, $query, $project_id);
601 $amount = $netamount + $tax;
603 # set values which could be empty
604 $form->{taxincluded} *= 1;
605 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
606 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
608 ($null, $form->{department_id}) = split(/--/, $form->{department});
609 $form->{department_id} *= 1;
610 $form->{payment_id} *= 1;
611 $form->{language_id} *= 1;
612 $form->{taxzone_id} *= 1;
613 $form->{storno} *= 1;
616 $form->{invnumber} = $form->{id} unless $form->{invnumber};
619 $query = qq|UPDATE ap set
620 invnumber = '$form->{invnumber}',
621 ordnumber = '$form->{ordnumber}',
622 quonumber = '$form->{quonumber}',
623 transdate = '$form->{invdate}',
624 orddate = | . conv_dateq($form->{"orddate"}) . qq|,
625 quodate = | . conv_dateq($form->{"quodate"}) . qq|,
626 vendor_id = $form->{vendor_id},
628 netamount = $netamount,
629 paid = $form->{paid},
630 datepaid = $datepaid,
633 taxzone_id = '$form->{taxzone_id}',
634 taxincluded = '$form->{taxincluded}',
635 notes = '$form->{notes}',
636 intnotes = '$form->{intnotes}',
637 curr = '$form->{currency}',
638 department_id = $form->{department_id},
639 storno = '$form->{storno}',
640 globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
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 Common::webdav_folder($form) if ($main::webdav);
670 my $rc = $dbh->commit;
673 $main::lxdebug->leave_sub();
678 sub reverse_invoice {
679 $main::lxdebug->enter_sub();
681 my ($dbh, $form) = @_;
683 # reverse inventory items
684 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
685 i.qty, i.allocated, i.sellprice
686 FROM invoice i, parts p
687 WHERE i.parts_id = p.id
688 AND i.trans_id = $form->{id}|;
689 my $sth = $dbh->prepare($query);
690 $sth->execute || $form->dberror($query);
694 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
695 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
697 if ($ref->{inventory_accno_id}) {
700 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
703 # if $ref->{allocated} > 0 than we sold that many items
704 if ($ref->{allocated} > 0) {
706 # get references for sold items
707 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
709 WHERE i.parts_id = $ref->{parts_id}
711 AND i.trans_id = a.id
712 ORDER BY transdate DESC|;
713 my $sth = $dbh->prepare($query);
714 $sth->execute || $form->dberror($query);
716 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
717 my $qty = $ref->{allocated};
718 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
719 $qty = $pthref->{allocated} * -1;
722 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
725 $form->update_balance($dbh, "invoice", "allocated",
726 qq|id = $pthref->{id}|, $qty);
728 $form->update_balance(
732 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
735 $form->update_balance(
739 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
742 last if (($ref->{allocated} -= $qty) <= 0);
751 $query = qq|DELETE FROM acc_trans
752 WHERE trans_id = $form->{id}|;
753 $dbh->do($query) || $form->dberror($query);
755 # delete invoice entries
756 $query = qq|DELETE FROM invoice
757 WHERE trans_id = $form->{id}|;
758 $dbh->do($query) || $form->dberror($query);
760 $query = qq|DELETE FROM shipto
761 WHERE trans_id = $form->{id} AND module = 'AP'|;
762 $dbh->do($query) || $form->dberror($query);
764 $main::lxdebug->leave_sub();
768 $main::lxdebug->enter_sub();
770 my ($self, $myconfig, $form) = @_;
772 # connect to database
773 my $dbh = $form->dbconnect_noauto($myconfig);
775 &reverse_invoice($dbh, $form);
777 # delete zero entries
778 my $query = qq|DELETE FROM acc_trans
780 $dbh->do($query) || $form->dberror($query);
783 my $query = qq|DELETE FROM ap
784 WHERE id = $form->{id}|;
785 $dbh->do($query) || $form->dberror($query);
787 my $rc = $dbh->commit;
790 $main::lxdebug->leave_sub();
795 sub retrieve_invoice {
796 $main::lxdebug->enter_sub();
798 my ($self, $myconfig, $form) = @_;
800 # connect to database
801 my $dbh = $form->dbconnect_noauto($myconfig);
807 # get default accounts and last invoice number
808 $query = qq|SELECT (SELECT c.accno FROM chart c
809 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
810 (SELECT c.accno FROM chart c
811 WHERE d.income_accno_id = c.id) AS income_accno,
812 (SELECT c.accno FROM chart c
813 WHERE d.expense_accno_id = c.id) AS expense_accno,
814 (SELECT c.accno FROM chart c
815 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
816 (SELECT c.accno FROM chart c
817 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
821 $query = qq|SELECT (SELECT c.accno FROM chart c
822 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
823 (SELECT c.accno FROM chart c
824 WHERE d.income_accno_id = c.id) AS income_accno,
825 (SELECT c.accno FROM chart c
826 WHERE d.expense_accno_id = c.id) AS expense_accno,
827 (SELECT c.accno FROM chart c
828 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
829 (SELECT c.accno FROM chart c
830 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
831 d.curr AS currencies,
832 current_date AS invdate
835 my $sth = $dbh->prepare($query);
836 $sth->execute || $form->dberror($query);
838 my $ref = $sth->fetchrow_hashref(NAME_lc);
839 map { $form->{$_} = $ref->{$_} } keys %$ref;
845 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
846 a.orddate, a.quodate, a.globalproject_id,
847 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
848 a.intnotes, a.curr AS currency
850 WHERE a.id = $form->{id}|;
851 $sth = $dbh->prepare($query);
852 $sth->execute || $form->dberror($query);
854 $ref = $sth->fetchrow_hashref(NAME_lc);
855 map { $form->{$_} = $ref->{$_} } keys %$ref;
858 $form->{exchangerate} =
859 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
863 $query = qq|SELECT s.* FROM shipto s
864 WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
865 $sth = $dbh->prepare($query);
866 $sth->execute || $form->dberror($query);
868 $ref = $sth->fetchrow_hashref(NAME_lc);
870 map { $form->{$_} = $ref->{$_} } keys %$ref;
874 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
876 if(!$form->{taxzone_id}) {
877 $form->{taxzone_id} = 0;
879 # retrieve individual items
880 $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
881 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
882 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
883 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
884 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
886 i.project_id, i.serialnumber,
889 JOIN parts p ON (i.parts_id = p.id)
890 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
891 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
892 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
893 LEFT JOIN project pr ON (i.project_id = pr.id)
894 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
895 WHERE i.trans_id = $form->{id}
897 $sth = $dbh->prepare($query);
898 $sth->execute || $form->dberror($query);
900 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
901 if (!$ref->{"part_inventory_accno_id"}) {
902 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
904 delete($ref->{"part_inventory_accno_id"});
906 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
907 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}|;
908 my $stw = $dbh->prepare($query);
909 $stw->execute || $form->dberror($query);
910 ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
914 while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
915 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}|;
916 my $stw = $dbh->prepare($query);
917 $stw->execute || $form->dberror($query);
918 ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
922 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
923 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}|;
924 my $stw = $dbh->prepare($query);
925 $stw->execute || $form->dberror($query);
926 ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
930 # get tax rates and description
932 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
933 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
934 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
935 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)
937 $stw = $dbh->prepare($query);
938 $stw->execute || $form->dberror($query);
939 $ref->{taxaccounts} = "";
941 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
943 # if ($customertax{$ref->{accno}}) {
944 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
948 $ref->{taxaccounts} .= "$ptr->{accno} ";
949 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
950 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
951 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
952 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
953 $form->{taxaccounts} .= "$ptr->{accno} ";
958 chop $ref->{taxaccounts};
959 push @{ $form->{invoice_details} }, $ref;
964 Common::webdav_folder($form) if ($main::webdav);
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,
998 b.description AS business
1000 LEFT JOIN business b ON b.id = v.business_id
1001 WHERE v.id = $form->{vendor_id}|;
1002 my $sth = $dbh->prepare($query);
1003 $sth->execute || $form->dberror($query);
1005 $ref = $sth->fetchrow_hashref(NAME_lc);
1006 map { $form->{$_} = $ref->{$_} } keys %$ref;
1009 $form->{creditremaining} = $form->{creditlimit};
1010 $query = qq|SELECT SUM(a.amount - a.paid)
1012 WHERE a.vendor_id = $form->{vendor_id}|;
1013 $sth = $dbh->prepare($query);
1014 $sth->execute || $form->dberror($query);
1016 ($form->{creditremaining}) -= $sth->fetchrow_array;
1020 $query = qq|SELECT o.amount,
1021 (SELECT e.sell FROM exchangerate e
1022 WHERE e.curr = o.curr
1023 AND e.transdate = o.transdate)
1025 WHERE o.vendor_id = $form->{vendor_id}
1026 AND o.quotation = '0'
1027 AND o.closed = '0'|;
1028 $sth = $dbh->prepare($query);
1029 $sth->execute || $form->dberror($query);
1031 while (my ($amount, $exch) = $sth->fetchrow_array) {
1032 $exch = 1 unless $exch;
1033 $form->{creditremaining} -= $amount * $exch;
1037 # get shipto if we do not convert an order or invoice
1038 if (!$form->{shipto}) {
1039 map { delete $form->{$_} }
1040 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
1042 $query = qq|SELECT s.* FROM shipto s
1043 WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
1044 $sth = $dbh->prepare($query);
1045 $sth->execute || $form->dberror($query);
1047 $ref = $sth->fetchrow_hashref(NAME_lc);
1048 map { $form->{$_} = $ref->{$_} } keys %$ref;
1052 # get taxes for vendor
1053 $query = qq|SELECT c.accno
1055 JOIN vendortax v ON (v.chart_id = c.id)
1056 WHERE v.vendor_id = $form->{vendor_id}|;
1057 $sth = $dbh->prepare($query);
1058 $sth->execute || $form->dberror($query);
1061 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1062 $vendortax{ $ref->{accno} } = 1;
1066 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1068 # setup last accounts used
1069 $query = qq|SELECT c.accno, c.description, c.link, c.category
1071 JOIN acc_trans ac ON (ac.chart_id = c.id)
1072 JOIN ap a ON (a.id = ac.trans_id)
1073 WHERE a.vendor_id = $form->{vendor_id}
1074 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1075 AND a.id IN (SELECT max(a2.id) FROM ap a2
1076 WHERE a2.vendor_id = $form->{vendor_id})|;
1077 $sth = $dbh->prepare($query);
1078 $sth->execute || $form->dberror($query);
1081 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1082 if ($ref->{category} eq 'E') {
1084 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1086 if ($ref->{category} eq 'L') {
1087 $form->{APselected} = $form->{AP_1} =
1088 "$ref->{accno}--$ref->{description}";
1092 $form->{rowcount} = $i if ($i && !$form->{type});
1097 $main::lxdebug->leave_sub();
1101 $main::lxdebug->enter_sub();
1103 my ($self, $myconfig, $form) = @_;
1105 # connect to database
1106 my $dbh = $form->dbconnect($myconfig);
1108 my $i = $form->{rowcount};
1110 # don't include assemblies or obsolete parts
1111 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1113 if ($form->{"partnumber_$i"}) {
1114 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1115 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1118 if ($form->{"description_$i"}) {
1119 my $description = $form->like(lc $form->{"description_$i"});
1120 $where .= " AND lower(p.description) LIKE '$description'";
1123 if ($form->{"partsgroup_$i"}) {
1124 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1125 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1128 if ($form->{"description_$i"}) {
1129 $where .= " ORDER BY p.description";
1131 $where .= " ORDER BY p.partnumber";
1135 if ($form->{type} eq "invoice") {
1137 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1140 $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1143 my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS sellprice,
1144 p.listprice, p.inventory_accno_id,
1145 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1146 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1147 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1148 p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
1149 pg.partsgroup, p.formel
1151 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
1152 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
1153 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
1154 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1156 my $sth = $dbh->prepare($query);
1157 $sth->execute || $form->dberror($query);
1159 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1161 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1162 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1163 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1164 if (!$ref->{inventory_accno_id}) {
1165 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1167 delete($ref->{inventory_accno_id});
1169 # get tax rates and description
1171 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1172 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1173 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
1174 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)
1176 $stw = $dbh->prepare($query);
1177 $stw->execute || $form->dberror($query);
1179 $ref->{taxaccounts} = "";
1181 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1183 # if ($customertax{$ref->{accno}}) {
1184 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1188 $ref->{taxaccounts} .= "$ptr->{accno} ";
1189 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1190 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1191 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1192 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1193 $form->{taxaccounts} .= "$ptr->{accno} ";
1199 chop $ref->{taxaccounts};
1201 push @{ $form->{item_list} }, $ref;
1208 $main::lxdebug->leave_sub();
1211 sub vendor_details {
1212 $main::lxdebug->enter_sub();
1214 my ($self, $myconfig, $form, @wanted_vars) = @_;
1216 # connect to database
1217 my $dbh = $form->dbconnect($myconfig);
1219 # get contact id, set it if nessessary
1220 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1223 if ($form->{cp_id}) {
1224 $contact = "and cp.cp_id = $form->{cp_id}";
1227 # get rest for the vendor
1228 # fax and phone and email as vendor*
1230 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1232 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1233 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1234 my $sth = $dbh->prepare($query);
1235 $sth->execute || $form->dberror($query);
1237 $ref = $sth->fetchrow_hashref(NAME_lc);
1239 # remove id and taxincluded before copy back
1240 delete @$ref{qw(id taxincluded)};
1242 @wanted_vars = grep({ $_ } @wanted_vars);
1243 if (scalar(@wanted_vars) > 0) {
1245 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
1246 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
1249 map { $form->{$_} = $ref->{$_} } keys %$ref;
1254 $main::lxdebug->leave_sub();
1258 $main::lxdebug->enter_sub();
1260 my ($self, $myconfig, $form) = @_;
1262 # connect to database
1263 my $dbh = $form->dbconnect($myconfig);
1265 my $query = qq|SELECT c.accno, c.description, c.link
1267 WHERE c.link LIKE '%IC%'
1269 my $sth = $dbh->prepare($query);
1270 $sth->execute || $form->dberror($query);
1272 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1273 foreach my $key (split(/:/, $ref->{link})) {
1275 push @{ $form->{IC_links}{$key} },
1276 { accno => $ref->{accno},
1277 description => $ref->{description} };
1283 $main::lxdebug->leave_sub();
1287 $main::lxdebug->enter_sub();
1289 my ($self, $myconfig, $form, $locale) = @_;
1291 # connect to database, turn off autocommit
1292 my $dbh = $form->dbconnect_noauto($myconfig);
1294 $form->{datepaid} = $form->{invdate};
1296 # total payments, don't move we need it here
1297 for my $i (1 .. $form->{paidaccounts}) {
1298 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
1299 $form->{paid} += $form->{"paid_$i"};
1300 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
1303 $form->{exchangerate} =
1304 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
1307 my $project_id = conv_i($form->{"globalproject_id"});
1309 # record payments and offsetting AP
1310 for my $i (1 .. $form->{paidaccounts}) {
1312 if ($form->{"paid_$i"} != 0) {
1313 my ($accno) = split /--/, $form->{"AP_paid_$i"};
1314 $form->{"datepaid_$i"} = $form->{invdate}
1315 unless ($form->{"datepaid_$i"});
1316 $form->{datepaid} = $form->{"datepaid_$i"};
1319 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
1320 $form->{"exchangerate_$i"} = 1;
1323 $form->check_exchangerate($myconfig, $form->{currency},
1324 $form->{"datepaid_$i"}, 'buy');
1326 $form->{"exchangerate_$i"} =
1329 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
1334 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
1338 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1339 WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
1340 $dbh->do($query) || $form->dberror($query);
1342 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1343 transdate, project_id)
1344 VALUES ($form->{id}, (SELECT c.id FROM chart c
1345 WHERE c.accno = '$form->{AP}'),
1346 $amount, '$form->{"datepaid_$i"}', ?)|;
1347 do_query($form, $dbh, $query, $project_id);
1351 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1352 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
1353 $dbh->do($query) || $form->dberror($query);
1355 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1356 source, memo, project_id)
1357 VALUES ($form->{id}, (SELECT c.id FROM chart c
1358 WHERE c.accno = '$accno'),
1359 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
1360 '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
1361 do_query($form, $dbh, $query, $project_id);
1366 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1367 $form->{"exchangerate_$i"};
1369 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
1372 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
1378 # update exchange rate
1379 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
1380 $form->update_exchangerate($dbh, $form->{currency},
1381 $form->{"datepaid_$i"},
1382 $form->{"exchangerate_$i"}, 0);
1387 # record exchange rate differences and gains/losses
1388 foreach my $accno (keys %{ $form->{fx} }) {
1389 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1391 ($form->{fx}{$accno}{$transdate} =
1392 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
1395 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1396 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
1397 $dbh->do($query) || $form->dberror($query);
1398 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1399 transdate, cleared, fx_transaction, project_id)
1400 VALUES ($form->{id},
1401 (SELECT c.id FROM chart c
1402 WHERE c.accno = '$accno'),
1403 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
1404 do_query($form, $dbh, $query, $project_id);
1408 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
1411 my $query = qq|UPDATE ap set
1412 paid = $form->{paid},
1413 datepaid = $datepaid
1414 WHERE id=$form->{id}|;
1416 $dbh->do($query) || $form->dberror($query);
1418 my $rc = $dbh->commit;
1421 $main::lxdebug->leave_sub();