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"};
340 print(STDERR $project_id, " Project_id\n");
342 ($form->{"deliverydate_$i"})
343 ? qq|'$form->{"deliverydate_$i"}'|
346 # save detail record in invoice table
347 $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty,
348 sellprice, fxsellprice, allocated, unit, deliverydate,
349 project_id, serialnumber)
350 VALUES ($form->{id}, $form->{"id_$i"},
351 '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|,
352 $form->{"sellprice_$i"}, $fxsellprice, $allocated,
353 '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
354 '$form->{"serialnumber_$i"}')|;
355 $dbh->do($query) || $form->dberror($query);
356 print(STDERR $query, "\n\n");
360 $form->{datepaid} = $form->{invdate};
362 # all amounts are in natural state, netamount includes the taxes
363 # if tax is included, netamount is rounded to 2 decimal places,
367 for my $i (1 .. $form->{paidaccounts}) {
368 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
369 $form->{paid} += $form->{"paid_$i"};
370 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
373 my ($tax, $paiddiff) = (0, 0);
375 $netamount = $form->round_amount($netamount, 2);
377 # figure out rounding errors for amount paid and total amount
378 if ($form->{taxincluded}) {
380 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
381 $paiddiff = $amount - $netamount * $form->{exchangerate};
382 $netamount = $amount;
384 foreach $item (split / /, $form->{taxaccounts}) {
385 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
386 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
387 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
389 $netamount -= $amount;
392 $invoicediff += $paiddiff;
393 $expensediff += $paiddiff;
395 ######## this only applies to tax included
396 if ($lastinventoryaccno) {
397 $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
399 if ($lastexpenseaccno) {
400 $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
404 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
405 $paiddiff = $amount - $netamount * $form->{exchangerate};
406 $netamount = $amount;
407 foreach my $item (split / /, $form->{taxaccounts}) {
408 $form->{amount}{ $form->{id} }{$item} =
409 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
412 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
415 $amount - $form->{amount}{ $form->{id} }{$item} *
416 $form->{exchangerate} * -1;
417 $form->{amount}{ $form->{id} }{$item} =
418 $form->round_amount($amount * -1, 2);
419 $amount = $form->{amount}{ $form->{id} }{$item} * -1;
424 $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
426 if ($form->{paid} != 0) {
428 $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
432 # update exchangerate
433 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
434 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
435 $form->{exchangerate});
438 # record acc_trans transactions
439 foreach my $trans_id (keys %{ $form->{amount} }) {
440 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
442 ($form->{amount}{$trans_id}{$accno} =
443 $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
446 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
448 VALUES ($trans_id, (SELECT c.id FROM chart c
449 WHERE c.accno = '$accno'),
450 $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
451 (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
452 $dbh->do($query) || $form->dberror($query);
457 # deduct payment differences from paiddiff
458 for my $i (1 .. $form->{paidaccounts}) {
459 if ($form->{"paid_$i"} != 0) {
461 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
462 $paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
466 # force AP entry if 0
467 $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
468 if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
470 # record payments and offsetting AP
471 for my $i (1 .. $form->{paidaccounts}) {
473 if ($form->{"paid_$i"} != 0) {
474 my ($accno) = split /--/, $form->{"AP_paid_$i"};
475 $form->{"datepaid_$i"} = $form->{invdate}
476 unless ($form->{"datepaid_$i"});
477 $form->{datepaid} = $form->{"datepaid_$i"};
481 $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
487 if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
488 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
490 VALUES ($form->{id}, (SELECT c.id FROM chart c
491 WHERE c.accno = '$form->{AP}'),
492 $amount, '$form->{"datepaid_$i"}')|;
493 $dbh->do($query) || $form->dberror($query);
498 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
500 VALUES ($form->{id}, (SELECT c.id FROM chart c
501 WHERE c.accno = '$accno'),
502 $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
503 '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
504 $dbh->do($query) || $form->dberror($query);
508 if ($form->{currency} eq $form->{defaultcurrency}) {
509 $form->{"exchangerate_$i"} = 1;
512 $form->check_exchangerate($myconfig, $form->{currency},
513 $form->{"datepaid_$i"}, 'sell');
515 $form->{"exchangerate_$i"} =
518 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
521 # exchangerate difference
522 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
523 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
527 ($form->{"paid_$i"} * $form->{exchangerate}) -
528 ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
530 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
533 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
539 # update exchange rate
540 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
541 $form->update_exchangerate($dbh, $form->{currency},
542 $form->{"datepaid_$i"},
543 0, $form->{"exchangerate_$i"});
548 # record exchange rate differences and gains/losses
549 foreach my $accno (keys %{ $form->{fx} }) {
550 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
552 ($form->{fx}{$accno}{$transdate} =
553 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
557 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
558 transdate, cleared, fx_transaction)
559 VALUES ($form->{id}, (SELECT c.id FROM chart c
560 WHERE c.accno = '$accno'),
561 $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
562 $dbh->do($query) || $form->dberror($query);
567 $amount = $netamount + $tax;
569 # set values which could be empty
570 $form->{taxincluded} *= 1;
571 my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
572 my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
574 ($null, $form->{department_id}) = split(/--/, $form->{department});
575 $form->{department_id} *= 1;
577 $form->{invnumber} = $form->{id} unless $form->{invnumber};
580 $query = qq|UPDATE ap set
581 invnumber = '$form->{invnumber}',
582 ordnumber = '$form->{ordnumber}',
583 quonumber = '$form->{quonumber}',
584 transdate = '$form->{invdate}',
585 vendor_id = $form->{vendor_id},
587 netamount = $netamount,
588 paid = $form->{paid},
589 datepaid = $datepaid,
592 taxincluded = '$form->{taxincluded}',
593 notes = '$form->{notes}',
594 intnotes = '$form->{intnotes}',
595 curr = '$form->{currency}',
596 department_id = $form->{department_id},
597 cp_id = $form->{contact_id}
598 WHERE id = $form->{id}|;
599 $dbh->do($query) || $form->dberror($query);
602 $form->{name} = $form->{vendor};
603 $form->{name} =~ s/--$form->{vendor_id}//;
604 $form->add_shipto($dbh, $form->{id});
606 # delete zero entries
607 $query = qq|DELETE FROM acc_trans
609 $dbh->do($query) || $form->dberror($query);
611 if ($form->{webdav}) {
612 &webdav_folder($myconfig, $form);
615 my $rc = $dbh->commit;
618 $main::lxdebug->leave_sub();
623 sub reverse_invoice {
624 $main::lxdebug->enter_sub();
626 my ($dbh, $form) = @_;
628 # reverse inventory items
629 my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
630 i.qty, i.allocated, i.sellprice
631 FROM invoice i, parts p
632 WHERE i.parts_id = p.id
633 AND i.trans_id = $form->{id}|;
634 my $sth = $dbh->prepare($query);
635 $sth->execute || $form->dberror($query);
639 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
640 $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
642 if ($ref->{inventory_accno_id}) {
645 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
648 # if $ref->{allocated} > 0 than we sold that many items
649 if ($ref->{allocated} > 0) {
651 # get references for sold items
652 $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
654 WHERE i.parts_id = $ref->{parts_id}
656 AND i.trans_id = a.id
657 ORDER BY transdate DESC|;
658 my $sth = $dbh->prepare($query);
659 $sth->execute || $form->dberror($query);
661 while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
662 my $qty = $ref->{allocated};
663 if (($ref->{allocated} + $pthref->{allocated}) > 0) {
664 $qty = $pthref->{allocated} * -1;
667 my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
670 $form->update_balance($dbh, "invoice", "allocated",
671 qq|id = $pthref->{id}|, $qty);
673 $form->update_balance(
677 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
680 $form->update_balance(
684 qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
687 last if (($ref->{allocated} -= $qty) <= 0);
696 $query = qq|DELETE FROM acc_trans
697 WHERE trans_id = $form->{id}|;
698 $dbh->do($query) || $form->dberror($query);
700 # delete invoice entries
701 $query = qq|DELETE FROM invoice
702 WHERE trans_id = $form->{id}|;
703 $dbh->do($query) || $form->dberror($query);
705 $query = qq|DELETE FROM shipto
706 WHERE trans_id = $form->{id}|;
707 $dbh->do($query) || $form->dberror($query);
709 $main::lxdebug->leave_sub();
713 $main::lxdebug->enter_sub();
715 my ($self, $myconfig, $form) = @_;
717 # connect to database
718 my $dbh = $form->dbconnect_noauto($myconfig);
720 &reverse_invoice($dbh, $form);
722 # delete zero entries
723 my $query = qq|DELETE FROM acc_trans
725 $dbh->do($query) || $form->dberror($query);
728 my $query = qq|DELETE FROM ap
729 WHERE id = $form->{id}|;
730 $dbh->do($query) || $form->dberror($query);
732 my $rc = $dbh->commit;
735 $main::lxdebug->leave_sub();
740 sub retrieve_invoice {
741 $main::lxdebug->enter_sub();
743 my ($self, $myconfig, $form) = @_;
745 # connect to database
746 my $dbh = $form->dbconnect_noauto($myconfig);
752 # get default accounts and last invoice number
753 $query = qq|SELECT (SELECT c.accno FROM chart c
754 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
755 (SELECT c.accno FROM chart c
756 WHERE d.income_accno_id = c.id) AS income_accno,
757 (SELECT c.accno FROM chart c
758 WHERE d.expense_accno_id = c.id) AS expense_accno,
759 (SELECT c.accno FROM chart c
760 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
761 (SELECT c.accno FROM chart c
762 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
766 $query = qq|SELECT (SELECT c.accno FROM chart c
767 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
768 (SELECT c.accno FROM chart c
769 WHERE d.income_accno_id = c.id) AS income_accno,
770 (SELECT c.accno FROM chart c
771 WHERE d.expense_accno_id = c.id) AS expense_accno,
772 (SELECT c.accno FROM chart c
773 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
774 (SELECT c.accno FROM chart c
775 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
776 d.curr AS currencies,
777 current_date AS invdate
780 my $sth = $dbh->prepare($query);
781 $sth->execute || $form->dberror($query);
783 my $ref = $sth->fetchrow_hashref(NAME_lc);
784 map { $form->{$_} = $ref->{$_} } keys %$ref;
790 $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
791 a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes,
792 a.intnotes, a.curr AS currency
794 WHERE a.id = $form->{id}|;
795 $sth = $dbh->prepare($query);
796 $sth->execute || $form->dberror($query);
798 $ref = $sth->fetchrow_hashref(NAME_lc);
799 map { $form->{$_} = $ref->{$_} } keys %$ref;
802 $form->{exchangerate} =
803 $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
807 $query = qq|SELECT s.* FROM shipto s
808 WHERE s.trans_id = $form->{id}|;
809 $sth = $dbh->prepare($query);
810 $sth->execute || $form->dberror($query);
812 $ref = $sth->fetchrow_hashref(NAME_lc);
813 map { $form->{$_} = $ref->{$_} } keys %$ref;
816 # retrieve individual items
817 $query = qq|SELECT c1.accno AS inventory_accno,
818 c2.accno AS income_accno,
819 c3.accno AS expense_accno,
820 p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice,
821 i.parts_id AS id, i.unit, p.bin, i.deliverydate,
823 i.project_id, i.serialnumber,
826 JOIN parts p ON (i.parts_id = p.id)
827 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
828 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
829 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
830 LEFT JOIN project pr ON (i.project_id = pr.id)
831 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
832 WHERE i.trans_id = $form->{id}
834 $sth = $dbh->prepare($query);
835 $sth->execute || $form->dberror($query);
837 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
839 #set expense_accno=inventory_accno if they are different => bilanz
841 ($ref->{expense_accno} != $ref->{inventory_accno})
842 ? $ref->{inventory_accno}
843 : $ref->{expense_accno};
845 ($ref->{inventory_accno})
846 ? $ref->{inventory_accno}
847 : $ref->{expense_accno};
849 # get tax rates and description
851 ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
852 $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
854 WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
856 $stw = $dbh->prepare($query);
857 $stw->execute || $form->dberror($query);
858 $ref->{taxaccounts} = "";
859 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
861 # if ($customertax{$ref->{accno}}) {
862 $ref->{taxaccounts} .= "$ptr->{accno} ";
863 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
864 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
865 $form->{"$ptr->{accno}_description"} = $ptr->{description};
866 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
867 $form->{taxaccounts} .= "$ptr->{accno} ";
872 chop $ref->{taxaccounts};
873 push @{ $form->{invoice_details} }, $ref;
878 if ($form->{webdav}) {
879 &webdav_folder($myconfig, $form);
884 my $rc = $dbh->commit;
887 $main::lxdebug->leave_sub();
893 $main::lxdebug->enter_sub();
895 my ($self, $myconfig, $form) = @_;
897 # connect to database
898 my $dbh = $form->dbconnect($myconfig);
900 my $dateformat = $myconfig->{dateformat};
901 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
905 ? "to_date('$form->{invdate}', '$dateformat')"
908 $form->{vendor_id} *= 1;
911 my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
912 v.email, v.cc, v.bcc, v.language,
913 v.street, v.zipcode, v.city, v.country,
914 $duedate + v.terms AS duedate, v.notes AS intnotes
916 WHERE v.id = $form->{vendor_id}|;
917 my $sth = $dbh->prepare($query);
918 $sth->execute || $form->dberror($query);
920 $ref = $sth->fetchrow_hashref(NAME_lc);
921 map { $form->{$_} = $ref->{$_} } keys %$ref;
924 $form->{creditremaining} = $form->{creditlimit};
925 $query = qq|SELECT SUM(a.amount - a.paid)
927 WHERE a.vendor_id = $form->{vendor_id}|;
928 $sth = $dbh->prepare($query);
929 $sth->execute || $form->dberror($query);
931 ($form->{creditremaining}) -= $sth->fetchrow_array;
935 $query = qq|SELECT o.amount,
936 (SELECT e.sell FROM exchangerate e
937 WHERE e.curr = o.curr
938 AND e.transdate = o.transdate)
940 WHERE o.vendor_id = $form->{vendor_id}
941 AND o.quotation = '0'
943 $sth = $dbh->prepare($query);
944 $sth->execute || $form->dberror($query);
946 while (my ($amount, $exch) = $sth->fetchrow_array) {
947 $exch = 1 unless $exch;
948 $form->{creditremaining} -= $amount * $exch;
952 $form->get_contacts($dbh, $form->{vendor_id});
954 ($null, $form->{cp_id}) = split /--/, $form->{contact};
956 # get contact if selected
957 if ($form->{contact} ne "--" && $form->{contact} ne "") {
958 $form->get_contact($dbh, $form->{cp_id});
961 # get shipto if we do not convert an order or invoice
962 if (!$form->{shipto}) {
963 map { delete $form->{$_} }
964 qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
966 $query = qq|SELECT s.* FROM shipto s
967 WHERE s.trans_id = $form->{vendor_id}|;
968 $sth = $dbh->prepare($query);
969 $sth->execute || $form->dberror($query);
971 $ref = $sth->fetchrow_hashref(NAME_lc);
972 map { $form->{$_} = $ref->{$_} } keys %$ref;
976 # get taxes for vendor
977 $query = qq|SELECT c.accno
979 JOIN vendortax v ON (v.chart_id = c.id)
980 WHERE v.vendor_id = $form->{vendor_id}|;
981 $sth = $dbh->prepare($query);
982 $sth->execute || $form->dberror($query);
985 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
986 $vendortax{ $ref->{accno} } = 1;
990 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
992 # setup last accounts used
993 $query = qq|SELECT c.accno, c.description, c.link, c.category
995 JOIN acc_trans ac ON (ac.chart_id = c.id)
996 JOIN ap a ON (a.id = ac.trans_id)
997 WHERE a.vendor_id = $form->{vendor_id}
998 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
999 AND a.id IN (SELECT max(a2.id) FROM ap a2
1000 WHERE a2.vendor_id = $form->{vendor_id})|;
1001 $sth = $dbh->prepare($query);
1002 $sth->execute || $form->dberror($query);
1005 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1006 if ($ref->{category} eq 'E') {
1008 $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
1010 if ($ref->{category} eq 'L') {
1011 $form->{APselected} = $form->{AP_1} =
1012 "$ref->{accno}--$ref->{description}";
1016 $form->{rowcount} = $i if ($i && !$form->{type});
1021 $main::lxdebug->leave_sub();
1025 $main::lxdebug->enter_sub();
1027 my ($self, $myconfig, $form) = @_;
1029 my $i = $form->{rowcount};
1031 # don't include assemblies or obsolete parts
1032 my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
1034 if ($form->{"partnumber_$i"}) {
1035 my $partnumber = $form->like(lc $form->{"partnumber_$i"});
1036 $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
1039 if ($form->{"description_$i"}) {
1040 my $description = $form->like(lc $form->{"description_$i"});
1041 $where .= " AND lower(p.description) LIKE '$description'";
1044 if ($form->{"partsgroup_$i"}) {
1045 my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
1046 $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
1049 if ($form->{"description_$i"}) {
1050 $where .= " ORDER BY p.description";
1052 $where .= " ORDER BY p.partnumber";
1055 # connect to database
1056 my $dbh = $form->dbconnect($myconfig);
1058 my $query = qq|SELECT p.id, p.partnumber, p.description,
1059 c1.accno AS inventory_accno,
1060 c2.accno AS income_accno,
1061 c3.accno AS expense_accno,
1063 p.lastcost AS sellprice, p.unit, p.bin, p.onhand, p.notes AS partnotes
1065 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
1066 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
1067 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
1068 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1070 my $sth = $dbh->prepare($query);
1071 $sth->execute || $form->dberror($query);
1073 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1075 #set expense_accno=inventory_accno if they are different => bilanz
1077 ($ref->{expense_accno} != $ref->{inventory_accno})
1078 ? $ref->{inventory_accno}
1079 : $ref->{expense_accno};
1081 ($ref->{inventory_accno})
1082 ? $ref->{inventory_accno}
1083 : $ref->{expense_accno};
1085 # get tax rates and description
1087 ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
1088 $query = qq|SELECT c.accno, c.description, t.rate, t.taxnumber
1090 WHERE c.id=t.chart_id AND t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
1092 $stw = $dbh->prepare($query);
1093 $stw->execute || $form->dberror($query);
1095 $ref->{taxaccounts} = "";
1096 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1098 # if ($customertax{$ref->{accno}}) {
1099 $ref->{taxaccounts} .= "$ptr->{accno} ";
1100 if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
1101 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1102 $form->{"$ptr->{accno}_description"} = $ptr->{description};
1103 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1104 $form->{taxaccounts} .= "$ptr->{accno} ";
1110 chop $ref->{taxaccounts};
1112 push @{ $form->{item_list} }, $ref;
1119 $main::lxdebug->leave_sub();
1122 sub vendor_details {
1123 $main::lxdebug->enter_sub();
1125 my ($self, $myconfig, $form) = @_;
1127 # connect to database
1128 my $dbh = $form->dbconnect($myconfig);
1130 # get contact id, set it if nessessary
1131 ($null, $form->{cp_id}) = split /--/, $form->{contact};
1134 if ($form->{cp_id}) {
1135 $contact = "and cp.cp_id = $form->{cp_id}";
1138 $taxincluded = $form->{taxincluded};
1140 # get rest for the vendor
1141 # fax and phone and email as vendor*
1143 qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
1145 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
1146 WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
1147 my $sth = $dbh->prepare($query);
1148 $sth->execute || $form->dberror($query);
1150 $ref = $sth->fetchrow_hashref(NAME_lc);
1151 map { $form->{$_} = $ref->{$_} } keys %$ref;
1153 $form->{taxincluded} = $taxincluded;
1158 $main::lxdebug->leave_sub();
1162 $main::lxdebug->enter_sub();
1164 my ($self, $myconfig, $form) = @_;
1166 # connect to database
1167 my $dbh = $form->dbconnect($myconfig);
1169 my $query = qq|SELECT c.accno, c.description, c.link
1171 WHERE c.link LIKE '%IC%'
1173 my $sth = $dbh->prepare($query);
1174 $sth->execute || $form->dberror($query);
1176 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1177 foreach my $key (split(/:/, $ref->{link})) {
1179 push @{ $form->{IC_links}{$key} },
1180 { accno => $ref->{accno},
1181 description => $ref->{description} };
1187 $main::lxdebug->leave_sub();
1191 $main::lxdebug->enter_sub();
1193 my ($myconfig, $form) = @_;
1196 $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
1197 if ($form->{vc} eq "customer");
1198 $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
1199 if ($form->{vc} eq "vendor");
1203 mkdir($path, 0770) or die "can't make directory $!\n";
1207 foreach $file (@files) {
1209 $file =~ /\/([^\/]*)$/;
1211 $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
1213 $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
1214 $form->{WEBDAV}{$fname} = $link;
1219 $main::lxdebug->leave_sub();