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 $all_units = AM->retrieve_units($myconfig, $form);
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', '$form->{employee_id}')|;
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 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
85 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
86 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
88 if ($form->{currency} eq $form->{defaultcurrency}) {
89 $form->{exchangerate} = 1;
92 $form->check_exchangerate($myconfig, $form->{currency},
93 $form->{transdate}, 'sell');
96 $form->{exchangerate} =
99 : $form->parse_amount($myconfig, $form->{exchangerate});
101 $form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
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 (defined($all_units->{$item_unit}->{factor})
127 && ($all_units->{$item_unit}->{factor} ne '')
128 && ($all_units->{$item_unit}->{factor} * 1 != 0)) {
129 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
133 $baseqty = $form->{"qty_$i"} * $basefactor;
135 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
136 qw(partnumber description unit);
138 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
143 $form->{"sellprice_$i"} =
144 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
145 my $fxsellprice = $form->{"sellprice_$i"};
147 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
149 my $decimalplaces = ($dec > 2) ? $dec : 2;
151 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
153 if ($form->{"inventory_accno_$i"}) {
156 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
158 if ($form->{taxincluded}) {
159 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
160 $form->{"sellprice_$i"} =
161 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
163 $taxamount = $linetotal * $taxrate;
166 $netamount += $linetotal;
168 if ($form->round_amount($taxrate, 7) == 0) {
169 if ($form->{taxincluded}) {
170 foreach $item (@taxaccounts) {
172 $form->round_amount($linetotal * $form->{"${item}_rate"} /
173 (1 + abs($form->{"${item}_rate"})),
175 $taxdiff += $taxamount;
176 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
178 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
181 $form->{amount}{ $form->{id} }{$_} -=
182 $linetotal * $form->{"${_}_rate"}
187 $form->{amount}{ $form->{id} }{$_} -=
188 $taxamount * $form->{"${_}_rate"} / $taxrate
192 # add purchase to inventory, this one is without the tax!
194 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
196 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
197 $form->{exchangerate};
198 $linetotal = $form->round_amount($linetotal, 2);
200 # this is the difference for the inventory
201 $invoicediff += ($amount - $linetotal);
203 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
206 # adjust and round sellprice
207 $form->{"sellprice_$i"} =
208 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
212 $query = qq|UPDATE parts SET
213 lastcost = $form->{"sellprice_$i"}
214 WHERE id = $form->{"id_$i"}|;
216 $dbh->do($query) || $form->dberror($query);
218 $form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
219 $baseqty, $form->{"id_$i"})
220 unless $form->{shipped};
222 # check if we sold the item already and
223 # make an entry for the expense and inventory
224 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
225 p.inventory_accno_id, p.expense_accno_id, a.transdate
226 FROM invoice i, ar a, parts p
227 WHERE i.parts_id = p.id
228 AND i.parts_id = $form->{"id_$i"}
229 AND (i.base_qty + i.allocated) > 0
230 AND i.trans_id = a.id
232 $sth = $dbh->prepare($query);
233 $sth->execute || $form->dberror($query);
235 my $totalqty = $base_qty;
237 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
239 my $qty = $ref->{base_qty} + $ref->{allocated};
241 if (($qty - $totalqty) > 0) {
245 $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
247 if ($ref->{allocated} < 0) {
249 # we have an entry for it already, adjust amount
250 $form->update_balance(
254 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
257 $form->update_balance(
261 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
266 # add entry for inventory, this one is for the sold item
267 if ($linetotal != 0) {
268 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
270 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
271 $linetotal, '$ref->{transdate}', (SELECT taxkey_id FROM chart WHERE id = $ref->{inventory_accno_id}))|;
272 $dbh->do($query) || $form->dberror($query);
275 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
277 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
278 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
279 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
280 $dbh->do($query) || $form->dberror($query);
284 # update allocated for sold item
285 $form->update_balance($dbh, "invoice", "allocated",
291 last if (($totalqty -= $qty) <= 0);
296 $lastinventoryaccno = $form->{"inventory_accno_$i"};
301 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
303 if ($form->{taxincluded}) {
304 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
306 $form->{"sellprice_$i"} =
307 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
309 $taxamount = $linetotal * $taxrate;
312 $netamount += $linetotal;
314 if ($form->round_amount($taxrate, 7) == 0) {
315 if ($form->{taxincluded}) {
316 foreach $item (@taxaccounts) {
318 $linetotal * $form->{"${item}_rate"} /
319 (1 + abs($form->{"${item}_rate"}));
320 $totaltax += $taxamount;
321 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
325 $form->{amount}{ $form->{id} }{$_} -=
326 $linetotal * $form->{"${_}_rate"}
331 $form->{amount}{ $form->{id} }{$_} -=
332 $taxamount * $form->{"${_}_rate"} / $taxrate
337 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
339 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
340 $form->{exchangerate};
341 $linetotal = $form->round_amount($linetotal, 2);
343 # this is the difference for expense
344 $expensediff += ($amount - $linetotal);
346 # add amount to expense
347 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
350 $lastexpenseaccno = $form->{"expense_accno_$i"};
352 # adjust and round sellprice
353 $form->{"sellprice_$i"} =
354 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
358 $query = qq|UPDATE parts SET
359 lastcost = $form->{"sellprice_$i"}
360 WHERE id = $form->{"id_$i"}|;
362 $dbh->do($query) || $form->dberror($query);
367 ($form->{"deliverydate_$i"})
368 ? qq|'$form->{"deliverydate_$i"}'|
371 # save detail record in invoice table
372 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
373 sellprice, fxsellprice, allocated, unit, deliverydate,
374 project_id, serialnumber)
375 VALUES ($form->{id}, $form->{"id_$i"},
376 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
377 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
378 '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
379 '$form->{"serialnumber_$i"}')|;
380 $dbh->do($query) || $form->dberror($query);
384 $project_id = conv_i($form->{"globalproject_id"});
386 $form->{datepaid} = $form->{invdate};
388 # all amounts are in natural state, netamount includes the taxes
389 # if tax is included, netamount is rounded to 2 decimal places,
393 for my $i (1 .. $form->{paidaccounts}) {
394 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
395 $form->{paid} += $form->{"paid_$i"};
396 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
399 my ($tax, $paiddiff) = (0, 0);
401 $netamount = $form->round_amount($netamount, 2);
403 # figure out rounding errors for amount paid and total amount
404 if ($form->{taxincluded}) {
406 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
407 $paiddiff = $amount - $netamount * $form->{exchangerate};
408 $netamount = $amount;
410 foreach $item (split / /, $form->{taxaccounts}) {
411 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
412 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
413 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
415 $netamount -= $amount;
418 $invoicediff += $paiddiff;
419 $expensediff += $paiddiff;
421 ######## this only applies to tax included
422 if ($lastinventoryaccno) {
423 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
425 if ($lastexpenseaccno) {
426 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
430 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
431 $paiddiff = $amount - $netamount * $form->{exchangerate};
432 $netamount = $amount;
433 foreach my $item (split / /, $form->{taxaccounts}) {
434 $form->{amount}{ $form->{id} }{$item} =
435 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
438 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
441 $amount - $form->{amount}{ $form->{id} }{$item} *
442 $form->{exchangerate} * -1;
443 $form->{amount}{ $form->{id} }{$item} =
444 $form->round_amount($amount * -1, 2);
445 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
450 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
452 if ($form->{paid} != 0) {
454 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
458 # update exchangerate
459 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
460 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
461 $form->{exchangerate});
464 # record acc_trans transactions
465 foreach my $trans_id (keys %{ $form->{amount} }) {
466 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
468 ($form->{amount}{$trans_id}{$accno} =
469 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
472 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
473 transdate, taxkey, project_id)
474 VALUES ($trans_id, (SELECT c.id FROM chart c
475 WHERE c.accno = '$accno'),
476 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
477 (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|;
478 do_query($form, $dbh, $query, $project_id);
483 # deduct payment differences from paiddiff
484 for my $i (1 .. $form->{paidaccounts}) {
485 if ($form->{"paid_$i"} != 0) {
487 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
488 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
492 # force AP entry if 0
493 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
494 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
496 # record payments and offsetting AP
497 for my $i (1 .. $form->{paidaccounts}) {
499 if ($form->{"paid_$i"} != 0) {
500 my ($accno) = split /--/, $form->{"AP_paid_$i"};
501 $form->{"datepaid_$i"} = $form->{invdate}
502 unless ($form->{"datepaid_$i"});
503 $form->{datepaid} = $form->{"datepaid_$i"};
507 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
513 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
514 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
515 transdate, taxkey, project_id)
516 VALUES ($form->{id}, (SELECT c.id FROM chart c
518 $amount, '$form->{"datepaid_$i"}',
519 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
520 do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
525 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
526 source, memo, taxkey, project_id)
527 VALUES ($form->{id}, (SELECT c.id FROM chart c
529 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
530 '$form->{"source_$i"}', '$form->{"memo_$i"}',
531 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
532 do_query($form, $dbh, $query, $accno, $accno, $project_id);
536 if ($form->{currency} eq $form->{defaultcurrency}) {
537 $form->{"exchangerate_$i"} = 1;
540 $form->check_exchangerate($myconfig, $form->{currency},
541 $form->{"datepaid_$i"}, 'sell');
543 $form->{"exchangerate_$i"} =
546 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
549 # exchangerate difference
550 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
551 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
555 ($form->{"paid_$i"} * $form->{exchangerate}) -
556 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
558 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
561 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
567 # update exchange rate
568 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
569 $form->update_exchangerate($dbh, $form->{currency},
570 $form->{"datepaid_$i"},
571 0, $form->{"exchangerate_$i"});
576 # record exchange rate differences and gains/losses
577 foreach my $accno (keys %{ $form->{fx} }) {
578 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
580 ($form->{fx}{$accno}{$transdate} =
581 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
585 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
586 transdate, cleared, fx_transaction, taxkey, project_id)
587 VALUES ($form->{id}, (SELECT c.id FROM chart c
588 WHERE c.accno = '$accno'),
589 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', 0, ?)|;
590 do_query($form, $dbh, $query, $project_id);
595 $amount = $netamount + $tax;
597 # set values which could be empty
598 $form->{taxincluded} *= 1;
599 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
600 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
602 ($null, $form->{department_id}) = split(/--/, $form->{department});
603 $form->{department_id} *= 1;
604 $form->{payment_id} *= 1;
605 $form->{language_id} *= 1;
606 $form->{taxzone_id} *= 1;
607 $form->{storno} *= 1;
610 $form->{invnumber} = $form->{id} unless $form->{invnumber};
613 $query = qq|UPDATE ap set
614 invnumber = '$form->{invnumber}',
615 ordnumber = '$form->{ordnumber}',
616 quonumber = '$form->{quonumber}',
617 transdate = '$form->{invdate}',
618 orddate = | . conv_dateq($form->{"orddate"}) . qq|,
619 quodate = | . conv_dateq($form->{"quodate"}) . qq|,
620 vendor_id = $form->{vendor_id},
622 netamount = $netamount,
623 paid = $form->{paid},
624 datepaid = $datepaid,
627 taxzone_id = '$form->{taxzone_id}',
628 taxincluded = '$form->{taxincluded}',
629 notes = '$form->{notes}',
630 intnotes = '$form->{intnotes}',
631 curr = '$form->{currency}',
632 department_id = $form->{department_id},
633 storno = '$form->{storno}',
634 globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
635 cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
636 WHERE id = $form->{id}|;
637 $dbh->do($query) || $form->dberror($query);
639 if ($form->{storno}) {
640 $query = qq| UPDATE ap SET paid = paid+amount WHERE id = $form->{storno_id}|;
641 $dbh->do($query) || $form->dberror($query);
642 $query = qq| UPDATE ap SET storno = '$form->{storno}' WHERE id = $form->{storno_id}|;
643 $dbh->do($query) || $form->dberror($query);
644 $query = qq§ UPDATE ap SET intnotes = 'Rechnung storniert am $form->{invdate} ' || intnotes WHERE id = $form->{storno_id}§;
645 $dbh->do($query) || $form->dberror($query);
647 $query = qq| UPDATE ap SET paid = amount WHERE id = $form->{id}|;
648 $dbh->do($query) || $form->dberror($query);
653 $form->{name} = $form->{vendor};
654 $form->{name} =~ s/--$form->{vendor_id}//;
655 $form->add_shipto($dbh, $form->{id}, "AP");
657 # delete zero entries
658 $query = qq|DELETE FROM acc_trans
660 $dbh->do($query) || $form->dberror($query);
662 Common::webdav_folder($form) if ($main::webdav);
664 my $rc = $dbh->commit;
667 $main::lxdebug->leave_sub();
672 sub reverse_invoice {
673 $main::lxdebug->enter_sub();
675 my ($dbh, $form) = @_;
677 # reverse inventory items
678 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
679 i.qty, i.allocated, i.sellprice
680 FROM invoice i, parts p
681 WHERE i.parts_id = p.id
682 AND i.trans_id = $form->{id}|;
683 my $sth = $dbh->prepare($query);
684 $sth->execute || $form->dberror($query);
688 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
689 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
691 if ($ref->{inventory_accno_id}) {
694 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
697 # if $ref->{allocated} > 0 than we sold that many items
698 if ($ref->{allocated} > 0) {
700 # get references for sold items
701 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
703 WHERE i.parts_id = $ref->{parts_id}
705 AND i.trans_id = a.id
706 ORDER BY transdate DESC|;
707 my $sth = $dbh->prepare($query);
708 $sth->execute || $form->dberror($query);
710 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
711 my $qty = $ref->{allocated};
712 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
713 $qty = $pthref->{allocated} * -1;
716 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
719 $form->update_balance($dbh, "invoice", "allocated",
720 qq|id = $pthref->{id}|, $qty);
722 $form->update_balance(
726 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
729 $form->update_balance(
733 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
736 last if (($ref->{allocated} -= $qty) <= 0);
745 $query = qq|DELETE FROM acc_trans
746 WHERE trans_id = $form->{id}|;
747 $dbh->do($query) || $form->dberror($query);
749 # delete invoice entries
750 $query = qq|DELETE FROM invoice
751 WHERE trans_id = $form->{id}|;
752 $dbh->do($query) || $form->dberror($query);
754 $query = qq|DELETE FROM shipto
755 WHERE trans_id = $form->{id} AND module = 'AP'|;
756 $dbh->do($query) || $form->dberror($query);
758 $main::lxdebug->leave_sub();
762 $main::lxdebug->enter_sub();
764 my ($self, $myconfig, $form) = @_;
766 # connect to database
767 my $dbh = $form->dbconnect_noauto($myconfig);
769 &reverse_invoice($dbh, $form);
771 # delete zero entries
772 my $query = qq|DELETE FROM acc_trans
774 $dbh->do($query) || $form->dberror($query);
777 my $query = qq|DELETE FROM ap
778 WHERE id = $form->{id}|;
779 $dbh->do($query) || $form->dberror($query);
781 my $rc = $dbh->commit;
784 $main::lxdebug->leave_sub();
789 sub retrieve_invoice {
790 $main::lxdebug->enter_sub();
792 my ($self, $myconfig, $form) = @_;
794 # connect to database
795 my $dbh = $form->dbconnect_noauto($myconfig);
801 # get default accounts and last invoice number
802 $query = qq|SELECT (SELECT c.accno FROM chart c
803 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
804 (SELECT c.accno FROM chart c
805 WHERE d.income_accno_id = c.id) AS income_accno,
806 (SELECT c.accno FROM chart c
807 WHERE d.expense_accno_id = c.id) AS expense_accno,
808 (SELECT c.accno FROM chart c
809 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
810 (SELECT c.accno FROM chart c
811 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
815 $query = qq|SELECT (SELECT c.accno FROM chart c
816 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
817 (SELECT c.accno FROM chart c
818 WHERE d.income_accno_id = c.id) AS income_accno,
819 (SELECT c.accno FROM chart c
820 WHERE d.expense_accno_id = c.id) AS expense_accno,
821 (SELECT c.accno FROM chart c
822 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
823 (SELECT c.accno FROM chart c
824 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
825 d.curr AS currencies,
826 current_date AS invdate
829 my $sth = $dbh->prepare($query);
830 $sth->execute || $form->dberror($query);
832 my $ref = $sth->fetchrow_hashref(NAME_lc);
833 map { $form->{$_} = $ref->{$_} } keys %$ref;
839 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
840 a.orddate, a.quodate, a.globalproject_id,
841 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
842 a.intnotes, a.curr AS currency
844 WHERE a.id = $form->{id}|;
845 $sth = $dbh->prepare($query);
846 $sth->execute || $form->dberror($query);
848 $ref = $sth->fetchrow_hashref(NAME_lc);
849 map { $form->{$_} = $ref->{$_} } keys %$ref;
852 $form->{exchangerate} =
853 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
857 $query = qq|SELECT s.* FROM shipto s
858 WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
859 $sth = $dbh->prepare($query);
860 $sth->execute || $form->dberror($query);
862 $ref = $sth->fetchrow_hashref(NAME_lc);
864 map { $form->{$_} = $ref->{$_} } keys %$ref;
868 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
870 if(!$form->{taxzone_id}) {
871 $form->{taxzone_id} = 0;
873 # retrieve individual items
874 $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
875 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
876 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
877 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
878 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
880 i.project_id, i.serialnumber,
883 JOIN parts p ON (i.parts_id = p.id)
884 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
885 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
886 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
887 LEFT JOIN project pr ON (i.project_id = pr.id)
888 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
889 WHERE i.trans_id = $form->{id}
891 $sth = $dbh->prepare($query);
892 $sth->execute || $form->dberror($query);
894 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
895 if (!$ref->{"part_inventory_accno_id"}) {
896 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
898 delete($ref->{"part_inventory_accno_id"});
900 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
901 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}|;
902 my $stw = $dbh->prepare($query);
903 $stw->execute || $form->dberror($query);
904 ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
908 while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
909 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}|;
910 my $stw = $dbh->prepare($query);
911 $stw->execute || $form->dberror($query);
912 ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
916 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
917 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}|;
918 my $stw = $dbh->prepare($query);
919 $stw->execute || $form->dberror($query);
920 ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
924 # get tax rates and description
926 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
927 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
928 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
929 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)
931 $stw = $dbh->prepare($query);
932 $stw->execute || $form->dberror($query);
933 $ref->{taxaccounts} = "";
935 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
937 # if ($customertax{$ref->{accno}}) {
938 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
942 $ref->{taxaccounts} .= "$ptr->{accno} ";
943 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
944 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
945 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
946 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
947 $form->{taxaccounts} .= "$ptr->{accno} ";
952 chop $ref->{taxaccounts};
953 push @{ $form->{invoice_details} }, $ref;
958 Common::webdav_folder($form) if ($main::webdav);
961 my $rc = $dbh->commit;
964 $main::lxdebug->leave_sub();
970 $main::lxdebug->enter_sub();
972 my ($self, $myconfig, $form) = @_;
974 # connect to database
975 my $dbh = $form->dbconnect($myconfig);
977 my $dateformat = $myconfig->{dateformat};
978 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
982 ? "to_date('$form->{invdate}', '$dateformat')"
985 $form->{vendor_id} *= 1;
988 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes,
989 v.email, v.cc, v.bcc, v.language_id, v.payment_id,
990 v.street, v.zipcode, v.city, v.country, v.taxzone_id,
991 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
992 b.description AS business
994 LEFT JOIN business b ON b.id = v.business_id
995 LEFT JOIN payment_terms pt ON v.payment_id = pt.id
997 $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
998 map { $form->{$_} = $ref->{$_} } keys %$ref;
1000 $form->{creditremaining} = $form->{creditlimit};
1001 $query = qq|SELECT SUM(a.amount - a.paid) FROM ap a WHERE a.vendor_id = ?|;
1002 my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $form->{vendor_id});
1003 $form->{creditremaining} -= $unpaid_invoices;
1005 $query = qq|SELECT o.amount,
1006 (SELECT e.sell FROM exchangerate e
1007 WHERE e.curr = o.curr AND e.transdate = o.transdate) AS exch
1008 FROM oe o WHERE o.vendor_id = ?
1009 AND o.quotation = '0' AND o.closed = '0'|;
1010 my $sth = prepare_execute_query($form, $dbh, $query, $form->{vendor_id});
1011 while (my ($amount, $exch) = $sth->fetchrow_array()) {
1012 $exch = 1 unless $exch;
1013 $form->{creditremaining} -= $amount * $exch;
1017 # get shipto if we do not convert an order or invoice
1018 if (!$form->{shipto}) {
1019 map { delete $form->{$_} }
1020 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
1022 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module= 'CT'|;
1023 $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
1024 map { $form->{$_} = $ref->{$_} } keys %$ref;
1027 # get taxes for vendor
1028 $query = qq|SELECT c.accno FROM chart c
1029 JOIN vendortax v ON (v.chart_id = c.id)
1030 WHERE v.vendor_id = ?|;
1032 $ref = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id});
1033 map { $vendortax{ $_->{accno} } = 1 } @$ref;
1035 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1037 # setup last accounts used
1038 $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category
1040 JOIN acc_trans ac ON (ac.chart_id = c.id)
1041 JOIN ap a ON (a.id = ac.trans_id)
1042 WHERE a.vendor_id = ?
1043 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1044 AND a.id IN (SELECT max(a2.id) FROM ap a2
1045 WHERE a2.vendor_id = ?)|;
1046 $refs = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id}, $form->{vendor_id});
1050 if ($ref->{category} eq 'E') {
1053 if ($form->{initial_transdate}) {
1054 my $tax_query = qq|SELECT tk.tax_id, t.rate FROM taxkeys tk
1055 LEFT JOIN tax t ON tk.tax_id = t.id
1056 WHERE tk.chart_id = ? AND startdate <= ?
1057 ORDER BY tk.startdate DESC LIMIT 1|;
1058 my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id},
1059 $form->{initial_transdate});
1060 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1063 $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
1065 if ($ref->{category} eq 'L') {
1066 $form->{APselected} = $form->{AP_1} = $ref->{accno};
1069 $form->{rowcount} = $i if ($i && !$form->{type});
1074 $main::lxdebug->leave_sub();
1078 $main::lxdebug->enter_sub();
1080 my ($self, $myconfig, $form) = @_;
1082 # connect to database
1083 my $dbh = $form->dbconnect($myconfig);
1085 my $i = $form->{rowcount};
1087 # don't include assemblies or obsolete parts
1088 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1090 if ($form->{"partnumber_$i"}) {
1091 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1092 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1095 if ($form->{"description_$i"}) {
1096 my $description = $form->like(lc $form->{"description_$i"});
1097 $where .= " AND lower(p.description) LIKE '$description'";
1100 if ($form->{"partsgroup_$i"}) {
1101 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1102 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1105 if ($form->{"description_$i"}) {
1106 $where .= " ORDER BY p.description";
1108 $where .= " ORDER BY p.partnumber";
1112 if ($form->{type} eq "invoice") {
1114 $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1117 $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1120 my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS sellprice,
1121 p.listprice, p.inventory_accno_id,
1122 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1123 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1124 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1125 p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
1126 pg.partsgroup, p.formel
1128 LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
1129 LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
1130 LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
1131 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1133 my $sth = $dbh->prepare($query);
1134 $sth->execute || $form->dberror($query);
1136 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1138 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1139 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1140 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1141 if (!$ref->{inventory_accno_id}) {
1142 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1144 delete($ref->{inventory_accno_id});
1146 # get tax rates and description
1148 ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1149 $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1150 FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
1151 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)
1153 $stw = $dbh->prepare($query);
1154 $stw->execute || $form->dberror($query);
1156 $ref->{taxaccounts} = "";
1158 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1160 # if ($customertax{$ref->{accno}}) {
1161 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1165 $ref->{taxaccounts} .= "$ptr->{accno} ";
1166 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1167 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1168 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1169 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1170 $form->{taxaccounts} .= "$ptr->{accno} ";
1176 chop $ref->{taxaccounts};
1178 push @{ $form->{item_list} }, $ref;
1185 $main::lxdebug->leave_sub();
1188 sub vendor_details {
1189 $main::lxdebug->enter_sub();
1191 my ($self, $myconfig, $form, @wanted_vars) = @_;
1193 # connect to database
1194 my $dbh = $form->dbconnect($myconfig);
1196 # get contact id, set it if nessessary
1197 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1200 if ($form->{cp_id}) {
1201 $contact = "and cp.cp_id = $form->{cp_id}";
1204 # get rest for the vendor
1205 # fax and phone and email as vendor*
1207 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1209 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1210 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1211 my $sth = $dbh->prepare($query);
1212 $sth->execute || $form->dberror($query);
1214 $ref = $sth->fetchrow_hashref(NAME_lc);
1216 # remove id and taxincluded before copy back
1217 delete @$ref{qw(id taxincluded)};
1219 @wanted_vars = grep({ $_ } @wanted_vars);
1220 if (scalar(@wanted_vars) > 0) {
1222 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
1223 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
1226 map { $form->{$_} = $ref->{$_} } keys %$ref;
1231 $main::lxdebug->leave_sub();
1235 $main::lxdebug->enter_sub();
1237 my ($self, $myconfig, $form) = @_;
1239 # connect to database
1240 my $dbh = $form->dbconnect($myconfig);
1242 my $query = qq|SELECT c.accno, c.description, c.link
1244 WHERE c.link LIKE '%IC%'
1246 my $sth = $dbh->prepare($query);
1247 $sth->execute || $form->dberror($query);
1249 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1250 foreach my $key (split(/:/, $ref->{link})) {
1252 push @{ $form->{IC_links}{$key} },
1253 { accno => $ref->{accno},
1254 description => $ref->{description} };
1260 $main::lxdebug->leave_sub();
1264 $main::lxdebug->enter_sub();
1266 my ($self, $myconfig, $form, $locale) = @_;
1268 # connect to database, turn off autocommit
1269 my $dbh = $form->dbconnect_noauto($myconfig);
1271 $form->{datepaid} = $form->{invdate};
1273 # total payments, don't move we need it here
1274 for my $i (1 .. $form->{paidaccounts}) {
1275 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
1276 $form->{paid} += $form->{"paid_$i"};
1277 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
1280 $form->{exchangerate} =
1281 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
1284 my $project_id = conv_i($form->{"globalproject_id"});
1286 # record payments and offsetting AP
1287 for my $i (1 .. $form->{paidaccounts}) {
1289 if ($form->{"paid_$i"} != 0) {
1290 my ($accno) = split /--/, $form->{"AP_paid_$i"};
1291 $form->{"datepaid_$i"} = $form->{invdate}
1292 unless ($form->{"datepaid_$i"});
1293 $form->{datepaid} = $form->{"datepaid_$i"};
1296 if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
1297 $form->{"exchangerate_$i"} = 1;
1300 $form->check_exchangerate($myconfig, $form->{currency},
1301 $form->{"datepaid_$i"}, 'buy');
1303 $form->{"exchangerate_$i"} =
1306 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
1311 $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
1315 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1316 WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
1317 $dbh->do($query) || $form->dberror($query);
1319 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1320 transdate, taxkey, project_id)
1321 VALUES ($form->{id}, (SELECT c.id FROM chart c
1323 $amount, '$form->{"datepaid_$i"}',
1324 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
1325 do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
1329 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1330 WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
1331 $dbh->do($query) || $form->dberror($query);
1333 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1334 source, memo, taxkey, project_id)
1335 VALUES ($form->{id}, (SELECT c.id FROM chart c
1337 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
1338 '$form->{"source_$i"}', '$form->{"memo_$i"}',
1339 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
1340 do_query($form, $dbh, $query, $accno, $accno, $project_id);
1345 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1346 $form->{"exchangerate_$i"};
1348 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
1351 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
1357 # update exchange rate
1358 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
1359 $form->update_exchangerate($dbh, $form->{currency},
1360 $form->{"datepaid_$i"},
1361 $form->{"exchangerate_$i"}, 0);
1366 # record exchange rate differences and gains/losses
1367 foreach my $accno (keys %{ $form->{fx} }) {
1368 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1370 ($form->{fx}{$accno}{$transdate} =
1371 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
1374 $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
1375 WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
1376 $dbh->do($query) || $form->dberror($query);
1377 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
1378 transdate, cleared, fx_transaction, taxkey, project_id)
1379 VALUES ($form->{id},
1380 (SELECT c.id FROM chart c
1382 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1',
1383 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
1384 do_query($form, $dbh, $query, $accno, $accno, $project_id);
1388 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
1391 my $query = qq|UPDATE ap set
1392 paid = $form->{paid},
1393 datepaid = $datepaid
1394 WHERE id=$form->{id}|;
1396 $dbh->do($query) || $form->dberror($query);
1398 my $rc = $dbh->commit;
1401 $main::lxdebug->leave_sub();