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 #======================================================================
38 $main::lxdebug->enter_sub();
40 my ($self, $myconfig, $form) = @_;
42 # connect to database, turn off autocommit
43 my $dbh = $form->dbconnect_noauto($myconfig);
45 my ($query, $sth, $null, $project_id);
55 &reverse_invoice($dbh, $form);
58 my $uid = rand() . time;
60 $uid .= $form->{login};
62 $uid = substr($uid, 2, 75);
64 $query = qq|INSERT INTO ap (invnumber, employee_id)
65 VALUES ('$uid', (SELECT e.id FROM employee e
66 WHERE e.login = '$form->{login}'))|;
67 $dbh->do($query) || $form->dberror($query);
69 $query = qq|SELECT a.id FROM ap a
70 WHERE a.invnumber = '$uid'|;
71 $sth = $dbh->prepare($query);
72 $sth->execute || $form->dberror($query);
74 ($form->{id}) = $sth->fetchrow_array;
78 ($null, $form->{contact_id}) = split /--/, $form->{contact};
79 $form->{contact_id} *= 1;
81 map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
83 my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
84 my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
86 if ($form->{currency} eq $form->{defaultcurrency}) {
87 $form->{exchangerate} = 1;
90 $form->check_exchangerate($myconfig, $form->{currency},
91 $form->{transdate}, 'sell');
94 $form->{exchangerate} =
97 : $form->parse_amount($myconfig, $form->{exchangerate});
99 for my $i (1 .. $form->{rowcount}) {
100 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
102 if ($form->{"qty_$i"} != 0) {
104 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
105 qw(partnumber description unit);
107 @taxaccounts = split / /, $form->{"taxaccounts_$i"};
112 $form->{"sellprice_$i"} =
113 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
114 my $fxsellprice = $form->{"sellprice_$i"};
116 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
118 my $decimalplaces = ($dec > 2) ? $dec : 2;
120 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
122 if ($form->{"inventory_accno_$i"}) {
125 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
127 if ($form->{taxincluded}) {
128 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
129 $form->{"sellprice_$i"} =
130 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
132 $taxamount = $linetotal * $taxrate;
135 $netamount += $linetotal;
137 if ($form->round_amount($taxrate, 7) == 0) {
138 if ($form->{taxincluded}) {
139 foreach $item (@taxaccounts) {
141 $form->round_amount($linetotal * $form->{"${item}_rate"} /
142 (1 + abs($form->{"${item}_rate"})),
144 $taxdiff += $taxamount;
145 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
147 $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
150 $form->{amount}{ $form->{id} }{$_} -=
151 $linetotal * $form->{"${_}_rate"}
156 $form->{amount}{ $form->{id} }{$_} -=
157 $taxamount * $form->{"${_}_rate"} / $taxrate
161 # add purchase to inventory, this one is without the tax!
163 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
165 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
166 $form->{exchangerate};
167 $linetotal = $form->round_amount($linetotal, 2);
169 # this is the difference for the inventory
170 $invoicediff += ($amount - $linetotal);
172 $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
175 # adjust and round sellprice
176 $form->{"sellprice_$i"} =
177 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
181 $query = qq|UPDATE parts SET
182 lastcost = $form->{"sellprice_$i"}
183 WHERE id = $form->{"id_$i"}|;
185 $dbh->do($query) || $form->dberror($query);
187 $form->update_balance($dbh, "parts", "onhand",
188 qq|id = $form->{"id_$i"}|,
190 unless $form->{shipped};
192 # check if we sold the item already and
193 # make an entry for the expense and inventory
194 $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
195 p.inventory_accno_id, p.expense_accno_id, a.transdate
196 FROM invoice i, ar a, parts p
197 WHERE i.parts_id = p.id
198 AND i.parts_id = $form->{"id_$i"}
199 AND (i.qty + i.allocated) > 0
200 AND i.trans_id = a.id
202 $sth = $dbh->prepare($query);
203 $sth->execute || $form->dberror($query);
205 my $totalqty = $form->{"qty_$i"};
207 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
209 my $qty = $ref->{qty} + $ref->{allocated};
211 if (($qty - $totalqty) > 0) {
215 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $qty, 2);
217 if ($ref->{allocated} < 0) {
219 # we have an entry for it already, adjust amount
220 $form->update_balance(
224 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
227 $form->update_balance(
231 qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
236 # add entry for inventory, this one is for the sold item
237 if ($linetotal != 0) {
238 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
240 VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
241 $linetotal, '$ref->{transdate}')|;
242 $dbh->do($query) || $form->dberror($query);
245 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
247 VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
248 | . ($linetotal * -1) . qq|, '$ref->{transdate}',
249 (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
250 $dbh->do($query) || $form->dberror($query);
254 # update allocated for sold item
255 $form->update_balance($dbh, "invoice", "allocated",
261 last if (($totalqty -= $qty) <= 0);
266 $lastinventoryaccno = $form->{"inventory_accno_$i"};
271 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
273 if ($form->{taxincluded}) {
274 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
276 $form->{"sellprice_$i"} =
277 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
279 $taxamount = $linetotal * $taxrate;
282 $netamount += $linetotal;
284 if ($form->round_amount($taxrate, 7) == 0) {
285 if ($form->{taxincluded}) {
286 foreach $item (@taxaccounts) {
288 $linetotal * $form->{"${item}_rate"} /
289 (1 + abs($form->{"${item}_rate"}));
290 $totaltax += $taxamount;
291 $form->{amount}{ $form->{id} }{$item} -= $taxamount;
295 $form->{amount}{ $form->{id} }{$_} -=
296 $linetotal * $form->{"${_}_rate"}
301 $form->{amount}{ $form->{id} }{$_} -=
302 $taxamount * $form->{"${_}_rate"} / $taxrate
307 $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
309 $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
310 $form->{exchangerate};
311 $linetotal = $form->round_amount($linetotal, 2);
313 # this is the difference for expense
314 $expensediff += ($amount - $linetotal);
316 # add amount to expense
317 $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
320 $lastexpenseaccno = $form->{"expense_accno_$i"};
322 # adjust and round sellprice
323 $form->{"sellprice_$i"} =
324 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
328 $query = qq|UPDATE parts SET
329 lastcost = $form->{"sellprice_$i"}
330 WHERE id = $form->{"id_$i"}|;
332 $dbh->do($query) || $form->dberror($query);
336 $project_id = 'NULL';
337 if ($form->{"projectnumber_$i"}) {
338 $project_id = $form->{"projectnumber_$i"};
341 ($form->{"deliverydate_$i"})
342 ? qq|'$form->{"deliverydate_$i"}'|
345 # save detail record in invoice table
346 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty,
347 sellprice, fxsellprice, allocated, unit, deliverydate,
348 project_id, serialnumber)
349 VALUES ($form->{id}, $form->{"id_$i"},
350 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|,
351 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
352 '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
353 '$form->{"serialnumber_$i"}')|;
354 $dbh->do($query) || $form->dberror($query);
358 $form->{datepaid} = $form->{invdate};
360 # all amounts are in natural state, netamount includes the taxes
361 # if tax is included, netamount is rounded to 2 decimal places,
365 for my $i (1 .. $form->{paidaccounts}) {
366 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
367 $form->{paid} += $form->{"paid_$i"};
368 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
371 my ($tax, $paiddiff) = (0, 0);
373 $netamount = $form->round_amount($netamount, 2);
375 # figure out rounding errors for amount paid and total amount
376 if ($form->{taxincluded}) {
378 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
379 $paiddiff = $amount - $netamount * $form->{exchangerate};
380 $netamount = $amount;
382 foreach $item (split / /, $form->{taxaccounts}) {
383 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
384 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
385 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
387 $netamount -= $amount;
390 $invoicediff += $paiddiff;
391 $expensediff += $paiddiff;
393 ######## this only applies to tax included
394 if ($lastinventoryaccno) {
395 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
397 if ($lastexpenseaccno) {
398 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
402 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
403 $paiddiff = $amount - $netamount * $form->{exchangerate};
404 $netamount = $amount;
405 foreach my $item (split / /, $form->{taxaccounts}) {
406 $form->{amount}{ $form->{id} }{$item} =
407 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
410 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
413 $amount - $form->{amount}{ $form->{id} }{$item} *
414 $form->{exchangerate} * -1;
415 $form->{amount}{ $form->{id} }{$item} =
416 $form->round_amount($amount * -1, 2);
417 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
422 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
424 if ($form->{paid} != 0) {
426 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
430 # update exchangerate
431 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
432 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
433 $form->{exchangerate});
436 # record acc_trans transactions
437 foreach my $trans_id (keys %{ $form->{amount} }) {
438 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
440 ($form->{amount}{$trans_id}{$accno} =
441 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
444 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
446 VALUES ($trans_id, (SELECT c.id FROM chart c
447 WHERE c.accno = '$accno'),
448 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
449 (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
450 $dbh->do($query) || $form->dberror($query);
455 # deduct payment differences from paiddiff
456 for my $i (1 .. $form->{paidaccounts}) {
457 if ($form->{"paid_$i"} != 0) {
459 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
460 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
464 # force AP entry if 0
465 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
466 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
468 # record payments and offsetting AP
469 for my $i (1 .. $form->{paidaccounts}) {
471 if ($form->{"paid_$i"} != 0) {
472 my ($accno) = split /--/, $form->{"AP_paid_$i"};
473 $form->{"datepaid_$i"} = $form->{invdate}
474 unless ($form->{"datepaid_$i"});
475 $form->{datepaid} = $form->{"datepaid_$i"};
479 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
485 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
486 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
488 VALUES ($form->{id}, (SELECT c.id FROM chart c
489 WHERE c.accno = '$form->{AP}'),
490 $amount, '$form->{"datepaid_$i"}')|;
491 $dbh->do($query) || $form->dberror($query);
496 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
498 VALUES ($form->{id}, (SELECT c.id FROM chart c
499 WHERE c.accno = '$accno'),
500 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
501 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
502 $dbh->do($query) || $form->dberror($query);
506 if ($form->{currency} eq $form->{defaultcurrency}) {
507 $form->{"exchangerate_$i"} = 1;
510 $form->check_exchangerate($myconfig, $form->{currency},
511 $form->{"datepaid_$i"}, 'sell');
513 $form->{"exchangerate_$i"} =
516 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
519 # exchangerate difference
520 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
521 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
525 ($form->{"paid_$i"} * $form->{exchangerate}) -
526 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
528 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
531 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
537 # update exchange rate
538 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
539 $form->update_exchangerate($dbh, $form->{currency},
540 $form->{"datepaid_$i"},
541 0, $form->{"exchangerate_$i"});
546 # record exchange rate differences and gains/losses
547 foreach my $accno (keys %{ $form->{fx} }) {
548 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
550 ($form->{fx}{$accno}{$transdate} =
551 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
555 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
556 transdate, cleared, fx_transaction)
557 VALUES ($form->{id}, (SELECT c.id FROM chart c
558 WHERE c.accno = '$accno'),
559 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
560 $dbh->do($query) || $form->dberror($query);
565 $amount = $netamount + $tax;
567 # set values which could be empty
568 $form->{taxincluded} *= 1;
569 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
570 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
572 ($null, $form->{department_id}) = split(/--/, $form->{department});
573 $form->{department_id} *= 1;
575 $form->{invnumber} = $form->{id} unless $form->{invnumber};
578 $query = qq|UPDATE ap set
579 invnumber = '$form->{invnumber}',
580 ordnumber = '$form->{ordnumber}',
581 quonumber = '$form->{quonumber}',
582 transdate = '$form->{invdate}',
583 vendor_id = $form->{vendor_id},
585 netamount = $netamount,
586 paid = $form->{paid},
587 datepaid = $datepaid,
590 taxincluded = '$form->{taxincluded}',
591 notes = '$form->{notes}',
592 intnotes = '$form->{intnotes}',
593 curr = '$form->{currency}',
594 department_id = $form->{department_id},
595 cp_id = $form->{contact_id}
596 WHERE id = $form->{id}|;
597 $dbh->do($query) || $form->dberror($query);
600 $form->{name} = $form->{vendor};
601 $form->{name} =~ s/--$form->{vendor_id}//;
602 $form->add_shipto($dbh, $form->{id});
604 # delete zero entries
605 $query = qq|DELETE FROM acc_trans
607 $dbh->do($query) || $form->dberror($query);
609 if ($form->{webdav}) {
610 &webdav_folder($myconfig, $form);
613 my $rc = $dbh->commit;
616 $main::lxdebug->leave_sub();
621 sub reverse_invoice {
622 $main::lxdebug->enter_sub();
624 my ($dbh, $form) = @_;
626 # reverse inventory items
627 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
628 i.qty, i.allocated, i.sellprice
629 FROM invoice i, parts p
630 WHERE i.parts_id = p.id
631 AND i.trans_id = $form->{id}|;
632 my $sth = $dbh->prepare($query);
633 $sth->execute || $form->dberror($query);
637 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
638 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
640 if ($ref->{inventory_accno_id}) {
643 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
646 # if $ref->{allocated} > 0 than we sold that many items
647 if ($ref->{allocated} > 0) {
649 # get references for sold items
650 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
652 WHERE i.parts_id = $ref->{parts_id}
654 AND i.trans_id = a.id
655 ORDER BY transdate DESC|;
656 my $sth = $dbh->prepare($query);
657 $sth->execute || $form->dberror($query);
659 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
660 my $qty = $ref->{allocated};
661 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
662 $qty = $pthref->{allocated} * -1;
665 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
668 $form->update_balance($dbh, "invoice", "allocated",
669 qq|id = $pthref->{id}|, $qty);
671 $form->update_balance(
675 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
678 $form->update_balance(
682 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
685 last if (($ref->{allocated} -= $qty) <= 0);
694 $query = qq|DELETE FROM acc_trans
695 WHERE trans_id = $form->{id}|;
696 $dbh->do($query) || $form->dberror($query);
698 # delete invoice entries
699 $query = qq|DELETE FROM invoice
700 WHERE trans_id = $form->{id}|;
701 $dbh->do($query) || $form->dberror($query);
703 $query = qq|DELETE FROM shipto
704 WHERE trans_id = $form->{id}|;
705 $dbh->do($query) || $form->dberror($query);
707 $main::lxdebug->leave_sub();
711 $main::lxdebug->enter_sub();
713 my ($self, $myconfig, $form) = @_;
715 # connect to database
716 my $dbh = $form->dbconnect_noauto($myconfig);
718 &reverse_invoice($dbh, $form);
720 # delete zero entries
721 my $query = qq|DELETE FROM acc_trans
723 $dbh->do($query) || $form->dberror($query);
726 my $query = qq|DELETE FROM ap
727 WHERE id = $form->{id}|;
728 $dbh->do($query) || $form->dberror($query);
730 my $rc = $dbh->commit;
733 $main::lxdebug->leave_sub();
738 sub retrieve_invoice {
739 $main::lxdebug->enter_sub();
741 my ($self, $myconfig, $form) = @_;
743 # connect to database
744 my $dbh = $form->dbconnect_noauto($myconfig);
750 # get default accounts and last invoice number
751 $query = qq|SELECT (SELECT c.accno FROM chart c
752 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
753 (SELECT c.accno FROM chart c
754 WHERE d.income_accno_id = c.id) AS income_accno,
755 (SELECT c.accno FROM chart c
756 WHERE d.expense_accno_id = c.id) AS expense_accno,
757 (SELECT c.accno FROM chart c
758 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
759 (SELECT c.accno FROM chart c
760 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
764 $query = qq|SELECT (SELECT c.accno FROM chart c
765 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
766 (SELECT c.accno FROM chart c
767 WHERE d.income_accno_id = c.id) AS income_accno,
768 (SELECT c.accno FROM chart c
769 WHERE d.expense_accno_id = c.id) AS expense_accno,
770 (SELECT c.accno FROM chart c
771 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
772 (SELECT c.accno FROM chart c
773 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
774 d.curr AS currencies,
775 current_date AS invdate
778 my $sth = $dbh->prepare($query);
779 $sth->execute || $form->dberror($query);
781 my $ref = $sth->fetchrow_hashref(NAME_lc);
782 map { $form->{$_} = $ref->{$_} } keys %$ref;
788 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
789 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes,
790 a.intnotes, a.curr AS currency
792 WHERE a.id = $form->{id}|;
793 $sth = $dbh->prepare($query);
794 $sth->execute || $form->dberror($query);
796 $ref = $sth->fetchrow_hashref(NAME_lc);
797 map { $form->{$_} = $ref->{$_} } keys %$ref;
800 $form->{exchangerate} =
801 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
805 $query = qq|SELECT s.* FROM shipto s
806 WHERE s.trans_id = $form->{id}|;
807 $sth = $dbh->prepare($query);
808 $sth->execute || $form->dberror($query);
810 $ref = $sth->fetchrow_hashref(NAME_lc);
811 map { $form->{$_} = $ref->{$_} } keys %$ref;
814 # retrieve individual items
815 $query = qq|SELECT c1.accno AS inventory_accno,
816 c2.accno AS income_accno,
817 c3.accno AS expense_accno,
818 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice,
819 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
821 i.project_id, i.serialnumber,
824 JOIN parts p ON (i.parts_id = p.id)
825 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
826 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
827 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
828 LEFT JOIN project pr ON (i.project_id = pr.id)
829 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
830 WHERE i.trans_id = $form->{id}
832 $sth = $dbh->prepare($query);
833 $sth->execute || $form->dberror($query);
835 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
837 #set expense_accno=inventory_accno if they are different => bilanz
839 ($ref->{expense_accno} != $ref->{inventory_accno})
840 ? $ref->{inventory_accno}
841 : $ref->{expense_accno};
843 ($ref->{inventory_accno})
844 ? $ref->{inventory_accno}
845 : $ref->{expense_accno};
847 # get tax rates and description
849 ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
850 $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
852 WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
854 $stw = $dbh->prepare($query);
855 $stw->execute || $form->dberror($query);
856 $ref->{taxaccounts} = "";
857 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
859 # if ($customertax{$ref->{accno}}) {
860 $ref->{taxaccounts} .= "$ptr->{accno} ";
861 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
862 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
863 $form->{"$ptr->{accno}_description"} = $ptr->{description};
864 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
865 $form->{taxaccounts} .= "$ptr->{accno} ";
870 chop $ref->{taxaccounts};
871 push @{ $form->{invoice_details} }, $ref;
876 if ($form->{webdav}) {
877 &webdav_folder($myconfig, $form);
882 my $rc = $dbh->commit;
885 $main::lxdebug->leave_sub();
891 $main::lxdebug->enter_sub();
893 my ($self, $myconfig, $form) = @_;
895 # connect to database
896 my $dbh = $form->dbconnect($myconfig);
898 my $dateformat = $myconfig->{dateformat};
899 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
903 ? "to_date('$form->{invdate}', '$dateformat')"
906 $form->{vendor_id} *= 1;
909 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
910 v.email, v.cc, v.bcc, v.language,
911 v.street, v.zipcode, v.city, v.country,
912 $duedate + v.terms AS duedate, v.notes AS intnotes
914 WHERE v.id = $form->{vendor_id}|;
915 my $sth = $dbh->prepare($query);
916 $sth->execute || $form->dberror($query);
918 $ref = $sth->fetchrow_hashref(NAME_lc);
919 map { $form->{$_} = $ref->{$_} } keys %$ref;
922 $form->{creditremaining} = $form->{creditlimit};
923 $query = qq|SELECT SUM(a.amount - a.paid)
925 WHERE a.vendor_id = $form->{vendor_id}|;
926 $sth = $dbh->prepare($query);
927 $sth->execute || $form->dberror($query);
929 ($form->{creditremaining}) -= $sth->fetchrow_array;
933 $query = qq|SELECT o.amount,
934 (SELECT e.sell FROM exchangerate e
935 WHERE e.curr = o.curr
936 AND e.transdate = o.transdate)
938 WHERE o.vendor_id = $form->{vendor_id}
939 AND o.quotation = '0'
941 $sth = $dbh->prepare($query);
942 $sth->execute || $form->dberror($query);
944 while (my ($amount, $exch) = $sth->fetchrow_array) {
945 $exch = 1 unless $exch;
946 $form->{creditremaining} -= $amount * $exch;
950 $form->get_contacts($dbh, $form->{vendor_id});
952 ($null, $form->{cp_id}) = split /--/, $form->{contact};
954 # get contact if selected
955 if ($form->{contact} ne "--" && $form->{contact} ne "") {
956 $form->get_contact($dbh, $form->{cp_id});
959 # get shipto if we do not convert an order or invoice
960 if (!$form->{shipto}) {
961 map { delete $form->{$_} }
962 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
964 $query = qq|SELECT s.* FROM shipto s
965 WHERE s.trans_id = $form->{vendor_id}|;
966 $sth = $dbh->prepare($query);
967 $sth->execute || $form->dberror($query);
969 $ref = $sth->fetchrow_hashref(NAME_lc);
970 map { $form->{$_} = $ref->{$_} } keys %$ref;
974 # get taxes for vendor
975 $query = qq|SELECT c.accno
977 JOIN vendortax v ON (v.chart_id = c.id)
978 WHERE v.vendor_id = $form->{vendor_id}|;
979 $sth = $dbh->prepare($query);
980 $sth->execute || $form->dberror($query);
983 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
984 $vendortax{ $ref->{accno} } = 1;
988 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
990 # setup last accounts used
991 $query = qq|SELECT c.accno, c.description, c.link, c.category
993 JOIN acc_trans ac ON (ac.chart_id = c.id)
994 JOIN ap a ON (a.id = ac.trans_id)
995 WHERE a.vendor_id = $form->{vendor_id}
996 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
997 AND a.id IN (SELECT max(a2.id) FROM ap a2
998 WHERE a2.vendor_id = $form->{vendor_id})|;
999 $sth = $dbh->prepare($query);
1000 $sth->execute || $form->dberror($query);
1003 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1004 if ($ref->{category} eq 'E') {
1006 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1008 if ($ref->{category} eq 'L') {
1009 $form->{APselected} = $form->{AP_1} =
1010 "$ref->{accno}--$ref->{description}";
1014 $form->{rowcount} = $i if ($i && !$form->{type});
1019 $main::lxdebug->leave_sub();
1023 $main::lxdebug->enter_sub();
1025 my ($self, $myconfig, $form) = @_;
1027 my $i = $form->{rowcount};
1029 # don't include assemblies or obsolete parts
1030 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1032 if ($form->{"partnumber_$i"}) {
1033 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1034 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1037 if ($form->{"description_$i"}) {
1038 my $description = $form->like(lc $form->{"description_$i"});
1039 $where .= " AND lower(p.description) LIKE '$description'";
1042 if ($form->{"partsgroup_$i"}) {
1043 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1044 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1047 if ($form->{"description_$i"}) {
1048 $where .= " ORDER BY p.description";
1050 $where .= " ORDER BY p.partnumber";
1053 # connect to database
1054 my $dbh = $form->dbconnect($myconfig);
1056 my $query = qq|SELECT p.id, p.partnumber, p.description,
1057 c1.accno AS inventory_accno,
1058 c2.accno AS income_accno,
1059 c3.accno AS expense_accno,
1061 p.lastcost AS sellprice, p.unit, p.bin, p.onhand, p.notes AS partnotes
1063 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
1064 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
1065 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
1066 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1068 my $sth = $dbh->prepare($query);
1069 $sth->execute || $form->dberror($query);
1071 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1073 #set expense_accno=inventory_accno if they are different => bilanz
1075 ($ref->{expense_accno} != $ref->{inventory_accno})
1076 ? $ref->{inventory_accno}
1077 : $ref->{expense_accno};
1079 ($ref->{inventory_accno})
1080 ? $ref->{inventory_accno}
1081 : $ref->{expense_accno};
1083 # get tax rates and description
1085 ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
1086 $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
1088 WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
1090 $stw = $dbh->prepare($query);
1091 $stw->execute || $form->dberror($query);
1093 $ref->{taxaccounts} = "";
1094 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1096 # if ($customertax{$ref->{accno}}) {
1097 $ref->{taxaccounts} .= "$ptr->{accno} ";
1098 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1099 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1100 $form->{"$ptr->{accno}_description"} = $ptr->{description};
1101 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1102 $form->{taxaccounts} .= "$ptr->{accno} ";
1108 chop $ref->{taxaccounts};
1110 push @{ $form->{item_list} }, $ref;
1117 $main::lxdebug->leave_sub();
1120 sub vendor_details {
1121 $main::lxdebug->enter_sub();
1123 my ($self, $myconfig, $form) = @_;
1125 # connect to database
1126 my $dbh = $form->dbconnect($myconfig);
1128 # get contact id, set it if nessessary
1129 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1132 if ($form->{cp_id}) {
1133 $contact = "and cp.cp_id = $form->{cp_id}";
1136 # get rest for the vendor
1137 # fax and phone and email as vendor*
1139 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1141 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1142 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1143 my $sth = $dbh->prepare($query);
1144 $sth->execute || $form->dberror($query);
1146 $ref = $sth->fetchrow_hashref(NAME_lc);
1148 # remove id and taxincluded before copy back
1149 delete @$ref{qw(id taxincluded)};
1150 map { $form->{$_} = $ref->{$_} } keys %$ref;
1155 $main::lxdebug->leave_sub();
1159 $main::lxdebug->enter_sub();
1161 my ($self, $myconfig, $form) = @_;
1163 # connect to database
1164 my $dbh = $form->dbconnect($myconfig);
1166 my $query = qq|SELECT c.accno, c.description, c.link
1168 WHERE c.link LIKE '%IC%'
1170 my $sth = $dbh->prepare($query);
1171 $sth->execute || $form->dberror($query);
1173 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1174 foreach my $key (split(/:/, $ref->{link})) {
1176 push @{ $form->{IC_links}{$key} },
1177 { accno => $ref->{accno},
1178 description => $ref->{description} };
1184 $main::lxdebug->leave_sub();
1188 $main::lxdebug->enter_sub();
1190 my ($myconfig, $form) = @_;
1193 $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1194 if ($form->{vc} eq "customer");
1195 $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1196 if ($form->{vc} eq "vendor");
1200 mkdir($path, 0770) or die "can't make directory $!\n";
1204 foreach $file (@files) {
1206 $file =~ /\/([^\/]*)$/;
1208 $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1210 $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1211 $form->{WEBDAV}{$fname} = $link;
1216 $main::lxdebug->leave_sub();