1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
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 invoicing module
33 #======================================================================
37 use List::Util qw(max);
46 $main::lxdebug->enter_sub();
48 my ($self, $myconfig, $form, $locale) = @_;
50 $form->{duedate} ||= $form->{invdate};
53 my $dbh = $form->dbconnect($myconfig);
56 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
57 ($form->{terms}) = selectrow_query($form, $dbh, $query);
59 my (@project_ids, %projectnumbers);
61 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
63 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
66 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
67 $price_factors{$pfac->{id}} = $pfac;
69 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
72 # sort items by partsgroup
73 for $i (1 .. $form->{rowcount}) {
75 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
76 $partsgroup = $form->{"partsgroup_$i"};
78 push @partsgroup, [$i, $partsgroup];
79 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
83 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
84 join(", ", map({ "?" } @project_ids)) . ")";
85 $sth = $dbh->prepare($query);
86 $sth->execute(@project_ids) ||
87 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
88 while (my $ref = $sth->fetchrow_hashref()) {
89 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
94 $form->{"globalprojectnumber"} =
95 $projectnumbers{$form->{"globalproject_id"}};
102 my %oid = ('Pg' => 'oid',
103 'Oracle' => 'rowid');
105 # sort items by partsgroup
106 for $i (1 .. $form->{rowcount}) {
108 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
109 $partsgroup = $form->{"partsgroup_$i"};
111 push @partsgroup, [$i, $partsgroup];
124 my $nodiscount_subtotal = 0;
125 my $discount_subtotal = 0;
127 my $subtotal_header = 0;
131 qw(runningnumber number description longdescription qty ship unit bin
132 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
133 partnotes serialnumber reqdate sellprice listprice netprice
134 discount p_discount discount_sub nodiscount_sub
135 linetotal nodiscount_linetotal tax_rate projectnumber
136 price_factor price_factor_name);
139 qw(taxbase tax taxdescription taxrate taxnumber);
141 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
144 if ($item->[1] ne $sameitem) {
145 push(@{ $form->{description} }, qq|$item->[1]|);
146 $sameitem = $item->[1];
148 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
151 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
153 if ($form->{"id_$i"} != 0) {
155 # add number, description and qty to $form->{number},
156 if ($form->{"subtotal_$i"} && !$subtotal_header) {
157 $subtotal_header = $i;
158 $position = int($position);
161 } elsif ($subtotal_header) {
163 $position = int($position);
164 $position = $position.".".$subposition;
166 $position = int($position);
170 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
172 push @{ $form->{runningnumber} }, $position;
173 push @{ $form->{number} }, $form->{"partnumber_$i"};
174 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
175 push @{ $form->{bin} }, $form->{"bin_$i"};
176 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
177 push @{ $form->{description} }, $form->{"description_$i"};
178 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
179 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
180 push @{ $form->{unit} }, $form->{"unit_$i"};
181 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
182 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
183 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
184 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
185 push @{ $form->{invnumber} }, $form->{"invnumber"};
186 push @{ $form->{invdate} }, $form->{"invdate"};
187 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
188 push @{ $form->{price_factor_name} }, $price_factor->{description};
190 if ($form->{lizenzen}) {
191 if ($form->{"licensenumber_$i"}) {
192 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
193 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
194 push(@{ $form->{licensenumber} }, $licensenumber);
195 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
198 push(@{ $form->{licensenumber} }, "");
199 push(@{ $form->{validuntil} }, "");
204 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
206 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
207 my ($dec) = ($sellprice =~ /\.(\d+)/);
208 my $decimalplaces = max 2, length($dec);
210 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
211 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
212 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
213 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
215 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
217 $linetotal = ($linetotal != 0) ? $linetotal : '';
219 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
220 push @{ $form->{p_discount} }, $form->{"discount_$i"};
222 $form->{total} += $linetotal;
223 $form->{nodiscount_total} += $nodiscount_linetotal;
224 $form->{discount_total} += $discount;
226 if ($subtotal_header) {
227 $discount_subtotal += $linetotal;
228 $nodiscount_subtotal += $nodiscount_linetotal;
231 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
232 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
233 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
235 $discount_subtotal = 0;
236 $nodiscount_subtotal = 0;
237 $subtotal_header = 0;
240 push @{ $form->{discount_sub} }, "";
241 push @{ $form->{nodiscount_sub} }, "";
244 if (!$form->{"discount_$i"}) {
245 $nodiscount += $linetotal;
248 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
249 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
251 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
253 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
257 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
259 if ($form->{taxincluded}) {
262 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
263 $taxbase = $linetotal - $taxamount;
265 $taxamount = $linetotal * $taxrate;
266 $taxbase = $linetotal;
269 if ($form->round_amount($taxrate, 7) == 0) {
270 if ($form->{taxincluded}) {
271 foreach $item (@taxaccounts) {
273 $form->round_amount($linetotal * $form->{"${item}_rate"} /
274 (1 + abs($form->{"${item}_rate"})),
277 $taxaccounts{$item} += $taxamount;
278 $taxdiff += $taxamount;
280 $taxbase{$item} += $taxbase;
282 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
284 foreach $item (@taxaccounts) {
285 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
286 $taxbase{$item} += $taxbase;
290 foreach $item (@taxaccounts) {
291 $taxaccounts{$item} +=
292 $taxamount * $form->{"${item}_rate"} / $taxrate;
293 $taxbase{$item} += $taxbase;
296 $tax_rate = $taxrate * 100;
297 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
298 if ($form->{"assembly_$i"}) {
301 # get parts and push them onto the stack
303 if ($form->{groupitems}) {
305 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
307 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
311 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
313 JOIN parts p ON (a.parts_id = p.id)
314 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
315 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
316 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
318 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
319 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
320 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
321 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
322 push(@{ $form->{description} }, $sameitem);
325 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
327 push(@{ $form->{description} },
328 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
330 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
331 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
339 foreach my $item (sort keys %taxaccounts) {
340 push(@{ $form->{taxbase} },
341 $form->format_amount($myconfig, $taxbase{$item}, 2));
343 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
345 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
346 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
347 push(@{ $form->{taxrate} },
348 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
349 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
352 for my $i (1 .. $form->{paidaccounts}) {
353 if ($form->{"paid_$i"}) {
354 push(@{ $form->{payment} }, $form->{"paid_$i"});
355 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
356 push(@{ $form->{paymentaccount} }, $description);
357 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
358 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
360 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
363 if($form->{taxincluded}) {
364 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
367 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
370 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
371 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
372 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
373 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
375 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
376 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
378 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
379 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
381 $form->set_payment_options($myconfig, $form->{invdate});
383 $form->{username} = $myconfig->{name};
387 $main::lxdebug->leave_sub();
390 sub project_description {
391 $main::lxdebug->enter_sub();
393 my ($self, $dbh, $id) = @_;
395 my $query = qq|SELECT description FROM project WHERE id = ?|;
396 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
398 $main::lxdebug->leave_sub();
403 sub customer_details {
404 $main::lxdebug->enter_sub();
406 my ($self, $myconfig, $form, @wanted_vars) = @_;
408 # connect to database
409 my $dbh = $form->dbconnect($myconfig);
411 # get contact id, set it if nessessary
414 my @values = (conv_i($form->{customer_id}));
417 if ($form->{cp_id}) {
418 $where = qq| AND (cp.cp_id = ?) |;
419 push(@values, conv_i($form->{cp_id}));
422 # get rest for the customer
424 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
425 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
427 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
428 WHERE (ct.id = ?) $where
431 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
433 # remove id and taxincluded before copy back
434 delete @$ref{qw(id taxincluded)};
436 @wanted_vars = grep({ $_ } @wanted_vars);
437 if (scalar(@wanted_vars) > 0) {
439 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
440 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
443 map { $form->{$_} = $ref->{$_} } keys %$ref;
445 if ($form->{delivery_customer_id}) {
447 qq|SELECT *, notes as customernotes
451 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
453 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
456 if ($form->{delivery_vendor_id}) {
458 qq|SELECT *, notes as customernotes
462 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
464 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
468 $main::lxdebug->leave_sub();
472 $main::lxdebug->enter_sub();
474 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
476 # connect to database, turn off autocommit
477 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
479 my ($query, $sth, $null, $project_id, @values);
480 my $exchangerate = 0;
482 if (!$form->{employee_id}) {
483 $form->get_employee($dbh);
486 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
488 ($null, $form->{department_id}) = split(/--/, $form->{department});
490 my $all_units = AM->retrieve_units($myconfig, $form);
492 if (!$payments_only) {
494 &reverse_invoice($dbh, $form);
497 $query = qq|SELECT nextval('glid')|;
498 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
500 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
501 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
503 if (!$form->{invnumber}) {
505 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
506 "cnnumber" : "invnumber", $dbh);
511 my ($netamount, $invoicediff) = (0, 0);
512 my ($amount, $linetotal, $lastincomeaccno);
514 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
515 my $defaultcurrency = (split m/:/, $currencies)[0];
517 if ($form->{currency} eq $defaultcurrency) {
518 $form->{exchangerate} = 1;
521 $form->check_exchangerate($myconfig, $form->{currency},
522 $form->{transdate}, 'buy');
525 $form->{exchangerate} =
528 : $form->parse_amount($myconfig, $form->{exchangerate});
530 $form->{expense_inventory} = "";
534 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
535 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
538 foreach my $i (1 .. $form->{rowcount}) {
539 if ($form->{type} eq "credit_note") {
540 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
541 $form->{shipped} = 1;
543 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
548 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
549 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
550 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
552 if ($form->{storno}) {
553 $form->{"qty_$i"} *= -1;
556 if ($form->{"id_$i"}) {
559 if (defined($baseunits{$form->{"id_$i"}})) {
560 $item_unit = $baseunits{$form->{"id_$i"}};
563 $query = qq|SELECT unit FROM parts WHERE id = ?|;
564 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
565 $baseunits{$form->{"id_$i"}} = $item_unit;
568 if (defined($all_units->{$item_unit}->{factor})
569 && ($all_units->{$item_unit}->{factor} ne '')
570 && ($all_units->{$item_unit}->{factor} != 0)) {
571 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
575 $baseqty = $form->{"qty_$i"} * $basefactor;
577 my ($allocated, $taxrate) = (0, 0);
581 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
583 # keep entered selling price
585 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
587 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
589 my $decimalplaces = ($dec > 2) ? $dec : 2;
591 # undo discount formatting
592 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
595 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
597 # round linetotal to 2 decimal places
598 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
599 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
601 if ($form->{taxincluded}) {
602 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
603 $form->{"sellprice_$i"} =
604 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
606 $taxamount = $linetotal * $taxrate;
609 $netamount += $linetotal;
611 if ($taxamount != 0) {
613 $form->{amount}{ $form->{id} }{$_} +=
614 $taxamount * $form->{"${_}_rate"} / $taxrate
615 } split(/ /, $form->{"taxaccounts_$i"});
618 # add amount to income, $form->{amount}{trans_id}{accno}
619 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
621 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
622 $linetotal = $form->round_amount($linetotal, 2);
624 # this is the difference from the inventory
625 $invoicediff += ($amount - $linetotal);
627 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
630 $lastincomeaccno = $form->{"income_accno_$i"};
632 # adjust and round sellprice
633 $form->{"sellprice_$i"} =
634 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
637 next if $payments_only;
639 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
641 # adjust parts onhand quantity
643 if ($form->{"assembly_$i"}) {
645 # do not update if assembly consists of all services
647 qq|SELECT sum(p.inventory_accno_id)
649 JOIN assembly a ON (a.parts_id = p.id)
651 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
653 if ($sth->fetchrow_array) {
654 $form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
655 $baseqty * -1, $form->{"id_$i"})
656 unless $form->{shipped};
660 # record assembly item as allocated
661 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
663 $form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
664 $baseqty * -1, $form->{"id_$i"})
665 unless $form->{shipped};
667 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
671 # get pricegroup_id and save it
672 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
675 # save detail record in invoice table
677 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
678 sellprice, fxsellprice, discount, allocated, assemblyitem,
679 unit, deliverydate, project_id, serialnumber, pricegroup_id,
680 ordnumber, transdate, cusordnumber, base_qty, subtotal,
681 marge_percent, marge_total, lastcost,
682 price_factor_id, price_factor, marge_price_factor)
683 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
684 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
686 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
687 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
688 $form->{"sellprice_$i"}, $fxsellprice,
689 $form->{"discount_$i"}, $allocated, 'f',
690 $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
691 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
692 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
693 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
694 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
695 $form->{"lastcost_$i"},
696 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
697 conv_i($form->{"marge_price_factor_$i"}));
698 do_query($form, $dbh, $query, @values);
700 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
702 qq|INSERT INTO licenseinvoice (trans_id, license_id)
703 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
704 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
705 do_query($form, $dbh, $query, @values);
710 $form->{datepaid} = $form->{invdate};
712 # total payments, don't move we need it here
713 for my $i (1 .. $form->{paidaccounts}) {
714 if ($form->{type} eq "credit_note") {
715 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
717 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
719 $form->{paid} += $form->{"paid_$i"};
720 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
723 my ($tax, $diff) = (0, 0);
725 $netamount = $form->round_amount($netamount, 2);
727 # figure out rounding errors for total amount vs netamount + taxes
728 if ($form->{taxincluded}) {
730 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
731 $diff += $amount - $netamount * $form->{exchangerate};
732 $netamount = $amount;
734 foreach my $item (split(/ /, $form->{taxaccounts})) {
735 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
736 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
737 $tax += $form->{amount}{ $form->{id} }{$item};
738 $netamount -= $form->{amount}{ $form->{id} }{$item};
741 $invoicediff += $diff;
742 ######## this only applies to tax included
743 if ($lastincomeaccno) {
744 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
748 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
749 $diff = $amount - $netamount * $form->{exchangerate};
750 $netamount = $amount;
751 foreach my $item (split(/ /, $form->{taxaccounts})) {
752 $form->{amount}{ $form->{id} }{$item} =
753 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
756 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
759 $amount - $form->{amount}{ $form->{id} }{$item} *
760 $form->{exchangerate};
761 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
762 $tax += $form->{amount}{ $form->{id} }{$item};
766 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
768 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
771 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
773 # update exchangerate
774 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
775 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
776 $form->{exchangerate}, 0);
779 $project_id = conv_i($form->{"globalproject_id"});
781 foreach my $trans_id (keys %{ $form->{amount} }) {
782 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
783 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
785 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
787 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
789 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
790 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
791 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
792 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
793 do_query($form, $dbh, $query, @values);
794 $form->{amount}{$trans_id}{$accno} = 0;
798 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
799 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
801 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
803 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
804 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
805 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
806 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
807 do_query($form, $dbh, $query, @values);
812 # deduct payment differences from diff
813 for my $i (1 .. $form->{paidaccounts}) {
814 if ($form->{"paid_$i"} != 0) {
816 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
817 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
821 # record payments and offsetting AR
822 if (!$form->{storno}) {
823 for my $i (1 .. $form->{paidaccounts}) {
825 next if ($form->{"paid_$i"} == 0);
827 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
828 $form->{"datepaid_$i"} = $form->{invdate}
829 unless ($form->{"datepaid_$i"});
830 $form->{datepaid} = $form->{"datepaid_$i"};
834 if ($form->{currency} eq $defaultcurrency) {
835 $form->{"exchangerate_$i"} = 1;
838 $form->check_exchangerate($myconfig, $form->{currency},
839 $form->{"datepaid_$i"}, 'buy');
841 $form->{"exchangerate_$i"} =
842 $exchangerate ? $exchangerate
843 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
847 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
849 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
851 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
852 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
853 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
854 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
855 do_query($form, $dbh, $query, @values);
859 $form->{"paid_$i"} *= -1;
862 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
863 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
864 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
865 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
866 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
867 do_query($form, $dbh, $query, @values);
869 # exchangerate difference
870 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
871 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
875 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
876 $form->{"exchangerate_$i"};
878 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
881 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
887 # update exchange rate
888 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
889 $form->update_exchangerate($dbh, $form->{currency},
890 $form->{"datepaid_$i"},
891 $form->{"exchangerate_$i"}, 0);
895 } else { # if (!$form->{storno})
896 $form->{marge_total} *= -1;
899 if ($payments_only) {
900 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
901 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
903 if (!$provided_dbh) {
908 $main::lxdebug->leave_sub();
912 # record exchange rate differences and gains/losses
913 foreach my $accno (keys %{ $form->{fx} }) {
914 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
916 ($form->{fx}{$accno}{$transdate} =
917 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
922 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
923 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
924 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
925 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
926 do_query($form, $dbh, $query, @values);
931 $amount = $netamount + $tax;
934 $query = qq|UPDATE ar set
935 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
936 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
937 amount = ?, netamount = ?, paid = ?, datepaid = ?,
938 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
939 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
940 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
941 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
942 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
943 cp_id = ?, marge_total = ?, marge_percent = ?,
944 globalproject_id = ?, delivery_customer_id = ?,
945 transaction_description = ?, delivery_vendor_id = ?
947 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
948 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
949 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
950 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
951 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
952 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
953 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
954 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
955 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
956 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
957 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
958 conv_i($form->{"id"}));
959 do_query($form, $dbh, $query, @values);
961 if($form->{"formname"} eq "credit_note") {
962 for my $i (1 .. $form->{rowcount}) {
963 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
964 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
965 do_query($form, $dbh, $query, @values);
969 if ($form->{storno}) {
972 paid = paid + amount,
974 intnotes = ? || intnotes
976 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
977 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
981 $form->{name} = $form->{customer};
982 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
984 if (!$form->{shipto_id}) {
985 $form->add_shipto($dbh, $form->{id}, "AR");
988 # save printed, emailed and queued
989 $form->save_status($dbh);
991 Common::webdav_folder($form) if ($main::webdav);
994 if (!$provided_dbh) {
999 $main::lxdebug->leave_sub();
1004 sub _delete_payments {
1005 $main::lxdebug->enter_sub();
1007 my ($self, $form, $dbh) = @_;
1011 # Delete old payment entries from acc_trans.
1015 WHERE (trans_id = ?) AND fx_transaction
1021 LEFT JOIN chart c ON (at.chart_id = c.id)
1022 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1023 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1028 LEFT JOIN chart c ON (at.chart_id = c.id)
1029 WHERE (trans_id = ?)
1030 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1033 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1036 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1037 do_query($form, $dbh, $query);
1040 $main::lxdebug->leave_sub();
1044 $main::lxdebug->enter_sub();
1046 my ($self, $myconfig, $form, $locale) = @_;
1048 # connect to database, turn off autocommit
1049 my $dbh = $form->dbconnect_noauto($myconfig);
1051 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1053 $old_form = save_form();
1055 # Delete all entries in acc_trans from prior payments.
1056 $self->_delete_payments($form, $dbh);
1058 # Save the new payments the user made before cleaning up $form.
1059 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1061 # Clean up $form so that old content won't tamper the results.
1062 %keep_vars = map { $_, 1 } qw(login password id);
1063 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1065 # Retrieve the invoice from the database.
1066 $self->retrieve_invoice($myconfig, $form);
1068 # Set up the content of $form in the way that IS::post_invoice() expects.
1069 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1071 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1072 $item = $form->{invoice_details}->[$row - 1];
1074 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1076 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1079 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1081 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1083 # Restore the payment options from the user input.
1084 map { $form->{$_} = $payments{$_} } keys %payments;
1086 # Get the AR accno (which is normally done by Form::create_links()).
1090 LEFT JOIN chart c ON (at.chart_id = c.id)
1091 WHERE (trans_id = ?)
1092 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1096 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1098 # Post the new payments.
1099 $self->post_invoice($myconfig, $form, $dbh, 1);
1101 restore_form($old_form);
1103 my $rc = $dbh->commit();
1106 $main::lxdebug->leave_sub();
1111 sub process_assembly {
1112 $main::lxdebug->enter_sub();
1114 my ($dbh, $form, $id, $totalqty) = @_;
1117 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1118 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1120 JOIN parts p ON (a.parts_id = p.id)
1122 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1124 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1128 $ref->{inventory_accno_id} *= 1;
1129 $ref->{expense_accno_id} *= 1;
1131 # multiply by number of assemblies
1132 $ref->{qty} *= $totalqty;
1134 if ($ref->{assembly}) {
1135 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1138 if ($ref->{inventory_accno_id}) {
1139 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1143 # save detail record for individual assembly item in invoice table
1145 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1146 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1147 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1148 do_query($form, $dbh, $query, @values);
1154 $main::lxdebug->leave_sub();
1158 $main::lxdebug->enter_sub();
1160 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1161 $form->{taxzone_id} *=1;
1162 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1163 my $taxzone_id = $form->{"taxzone_id"} * 1;
1165 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1166 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1167 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1168 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1169 FROM invoice i, parts p
1170 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1171 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1172 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1173 WHERE (i.parts_id = p.id)
1174 AND (i.parts_id = ?)
1175 AND ((i.base_qty + i.allocated) < 0)
1177 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1182 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1183 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1187 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1189 # total expenses and inventory
1190 # sellprice is the cost of the item
1191 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1194 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1196 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1197 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1198 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1200 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1201 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1207 last if (($totalqty -= $qty) <= 0);
1212 $main::lxdebug->leave_sub();
1217 sub reverse_invoice {
1218 $main::lxdebug->enter_sub();
1220 my ($dbh, $form) = @_;
1222 # reverse inventory items
1224 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1226 JOIN parts p ON (i.parts_id = p.id)
1227 WHERE i.trans_id = ?|;
1228 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1230 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1232 if ($ref->{inventory_accno_id} || $ref->{assembly}) {
1234 # if the invoice item is not an assemblyitem adjust parts onhand
1235 if (!$ref->{assemblyitem}) {
1237 # adjust onhand in parts table
1238 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
1241 # loop if it is an assembly
1242 next if ($ref->{assembly});
1244 # de-allocated purchases
1246 qq|SELECT i.id, i.trans_id, i.allocated
1248 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1249 ORDER BY i.trans_id DESC|;
1250 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1252 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1254 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1255 $qty = $inhref->{allocated};
1259 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1261 last if (($ref->{qty} -= $qty) <= 0);
1270 @values = (conv_i($form->{id}));
1271 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1272 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1274 if ($form->{lizenzen}) {
1276 qq|DELETE FROM licenseinvoice
1277 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1278 do_query($form, $dbh, $query, @values);
1281 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1283 $main::lxdebug->leave_sub();
1286 sub delete_invoice {
1287 $main::lxdebug->enter_sub();
1289 my ($self, $myconfig, $form, $spool) = @_;
1291 # connect to database
1292 my $dbh = $form->dbconnect_noauto($myconfig);
1294 &reverse_invoice($dbh, $form);
1296 my @values = (conv_i($form->{id}));
1299 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1301 # delete spool files
1302 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1304 # delete status entries
1305 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1307 my $rc = $dbh->commit;
1311 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1314 $main::lxdebug->leave_sub();
1319 sub retrieve_invoice {
1320 $main::lxdebug->enter_sub();
1322 my ($self, $myconfig, $form) = @_;
1324 # connect to database
1325 my $dbh = $form->dbconnect_noauto($myconfig);
1327 my ($sth, $ref, $query);
1329 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1333 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1334 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1335 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1336 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1337 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1338 d.curr AS currencies
1342 $ref = selectfirst_hashref_query($form, $dbh, $query);
1343 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1346 my $id = conv_i($form->{id});
1351 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1352 a.orddate, a.quodate, a.globalproject_id,
1353 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1354 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1355 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1356 a.employee_id, a.salesman_id, a.payment_id,
1357 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1358 a.transaction_description,
1359 a.marge_total, a.marge_percent,
1362 LEFT JOIN employee e ON (e.id = a.employee_id)
1364 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1365 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1368 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1371 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1372 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1374 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1376 foreach my $vc (qw(customer vendor)) {
1377 next if !$form->{"delivery_${vc}_id"};
1378 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1381 # get printed, emailed
1382 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1383 $sth = prepare_execute_query($form, $dbh, $query, $id);
1385 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1386 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1387 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1388 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1391 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1393 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1394 : $form->{invdate} ? $dbh->quote($form->{invdate})
1398 my $taxzone_id = $form->{taxzone_id} *= 1;
1399 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1401 # retrieve individual items
1404 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1405 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1406 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1408 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate,
1409 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1410 i.price_factor_id, i.price_factor, i.marge_price_factor,
1411 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1412 pr.projectnumber, pg.partsgroup, prg.pricegroup
1415 LEFT JOIN parts p ON (i.parts_id = p.id)
1416 LEFT JOIN project pr ON (i.project_id = pr.id)
1417 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1418 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1420 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1421 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1422 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1424 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1426 $sth = prepare_execute_query($form, $dbh, $query, $id);
1428 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1429 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1430 delete($ref->{"part_inventory_accno_id"});
1432 foreach my $type (qw(inventory income expense)) {
1433 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1434 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1435 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1439 # get tax rates and description
1440 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1442 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1443 LEFT JOIN chart c ON (c.id = t.chart_id)
1445 (SELECT tk.tax_id FROM taxkeys tk
1446 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1447 AND startdate <= date($transdate)
1448 ORDER BY startdate DESC LIMIT 1)
1450 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1451 $ref->{taxaccounts} = "";
1453 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1455 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1459 $ref->{taxaccounts} .= "$ptr->{accno} ";
1461 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1462 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1463 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1464 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1465 $form->{taxaccounts} .= "$ptr->{accno} ";
1470 if ($form->{lizenzen}) {
1471 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1472 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1473 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1476 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1478 chop $ref->{taxaccounts};
1479 push @{ $form->{invoice_details} }, $ref;
1484 Common::webdav_folder($form) if ($main::webdav);
1487 my $rc = $dbh->commit;
1490 $main::lxdebug->leave_sub();
1496 $main::lxdebug->enter_sub();
1498 my ($self, $myconfig, $form) = @_;
1500 # connect to database
1501 my $dbh = $form->dbconnect($myconfig);
1503 my $dateformat = $myconfig->{dateformat};
1504 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1506 my (@values, $duedate, $ref, $query);
1508 if ($form->{invdate}) {
1509 $duedate = "to_date(?, '$dateformat')";
1510 push @values, $form->{invdate};
1512 $duedate = "current_date";
1515 my $cid = conv_i($form->{customer_id});
1520 c.name AS customer, c.discount, c.creditlimit, c.terms,
1521 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1522 c.street, c.zipcode, c.city, c.country,
1523 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1524 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1525 b.discount AS tradediscount, b.description AS business
1527 LEFT JOIN business b ON (b.id = c.business_id)
1528 LEFT JOIN payment_terms pt ON (c.payment_id = pt.id)
1531 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1532 map { $form->{$_} = $ref->{$_} } keys %$ref;
1535 qq|SELECT sum(amount - paid) AS dunning_amount
1537 WHERE (paid < amount)
1538 AND (customer_id = ?)
1539 AND (dunning_config_id IS NOT NULL)|;
1540 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1541 map { $form->{$_} = $ref->{$_} } keys %$ref;
1544 qq|SELECT dnn.dunning_description AS max_dunning_level
1545 FROM dunning_config dnn
1546 WHERE id IN (SELECT dunning_config_id
1548 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1549 ORDER BY dunning_level DESC LIMIT 1|;
1550 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1551 map { $form->{$_} = $ref->{$_} } keys %$ref;
1553 $form->{creditremaining} = $form->{creditlimit};
1554 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1555 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1556 $form->{creditremaining} -= $value;
1560 (SELECT e.buy FROM exchangerate e
1561 WHERE e.curr = o.curr
1562 AND e.transdate = o.transdate)
1564 WHERE o.customer_id = ?
1565 AND o.quotation = '0'
1566 AND o.closed = '0'|;
1567 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1569 while (my ($amount, $exch) = $sth->fetchrow_array) {
1570 $exch = 1 unless $exch;
1571 $form->{creditremaining} -= $amount * $exch;
1575 # get shipto if we did not converted an order or invoice
1576 if (!$form->{shipto}) {
1577 map { delete $form->{$_} }
1578 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1579 shiptostreet shiptozipcode shiptocity shiptocountry
1580 shiptocontact shiptophone shiptofax shiptoemail);
1582 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1583 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1585 map { $form->{$_} = $ref->{$_} } keys %$ref;
1588 # setup last accounts used for this customer
1589 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1591 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1593 JOIN acc_trans ac ON (ac.chart_id = c.id)
1594 JOIN ar a ON (a.id = ac.trans_id)
1595 WHERE a.customer_id = ?
1596 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1597 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1598 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1601 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1602 if ($ref->{category} eq 'I') {
1604 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1606 if ($form->{initial_transdate}) {
1608 qq|SELECT tk.tax_id, t.rate
1610 LEFT JOIN tax t ON tk.tax_id = t.id
1611 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1612 ORDER BY tk.startdate DESC
1614 my ($tax_id, $rate) =
1615 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1616 $form->{initial_transdate});
1617 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1620 if ($ref->{category} eq 'A') {
1621 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1625 $form->{rowcount} = $i if ($i && !$form->{type});
1630 $main::lxdebug->leave_sub();
1634 $main::lxdebug->enter_sub();
1636 my ($self, $myconfig, $form) = @_;
1638 # connect to database
1639 my $dbh = $form->dbconnect($myconfig);
1641 my $i = $form->{rowcount};
1643 my $where = qq|NOT p.obsolete = '1'|;
1646 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1647 my ($table, $field) = split m/\./, $column;
1648 next if !$form->{"${field}_${i}"};
1649 $where .= qq| AND lower(${column}) ILIKE ?|;
1650 push @values, '%' . $form->{"${field}_${i}"} . '%';
1653 if ($form->{"description_$i"}) {
1654 $where .= qq| ORDER BY p.description|;
1656 $where .= qq| ORDER BY p.partnumber|;
1660 if ($form->{type} eq "invoice") {
1662 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1663 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1667 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1671 my $taxzone_id = $form->{taxzone_id} * 1;
1672 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1676 p.id, p.partnumber, p.description, p.sellprice,
1677 p.listprice, p.inventory_accno_id, p.lastcost,
1679 c1.accno AS inventory_accno,
1680 c1.new_chart_id AS inventory_new_chart,
1681 date($transdate) - c1.valid_from AS inventory_valid,
1683 c2.accno AS income_accno,
1684 c2.new_chart_id AS income_new_chart,
1685 date($transdate) - c2.valid_from AS income_valid,
1687 c3.accno AS expense_accno,
1688 c3.new_chart_id AS expense_new_chart,
1689 date($transdate) - c3.valid_from AS expense_valid,
1691 p.unit, p.assembly, p.bin, p.onhand,
1692 p.notes AS partnotes, p.notes AS longdescription,
1693 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1696 pfac.factor AS price_factor,
1701 LEFT JOIN chart c1 ON
1702 ((SELECT inventory_accno_id
1703 FROM buchungsgruppen
1704 WHERE id = p.buchungsgruppen_id) = c1.id)
1705 LEFT JOIN chart c2 ON
1706 ((SELECT income_accno_id_${taxzone_id}
1707 FROM buchungsgruppen
1708 WHERE id = p.buchungsgruppen_id) = c2.id)
1709 LEFT JOIN chart c3 ON
1710 ((SELECT expense_accno_id_${taxzone_id}
1711 FROM buchungsgruppen
1712 WHERE id = p.buchungsgruppen_id) = c3.id)
1713 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1714 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1716 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1718 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1720 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1721 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1722 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1723 if (!$ref->{inventory_accno_id}) {
1724 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1726 delete($ref->{inventory_accno_id});
1728 foreach my $type (qw(inventory income expense)) {
1729 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1731 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1734 ($ref->{"${type}_accno"},
1735 $ref->{"${type}_new_chart"},
1736 $ref->{"${type}_valid"})
1737 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1741 if ($form->{payment_id} eq "") {
1742 $form->{payment_id} = $form->{part_payment_id};
1745 # get tax rates and description
1746 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1748 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1750 LEFT JOIN chart c ON (c.id = t.chart_id)
1754 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1756 ORDER BY startdate DESC
1759 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1760 $stw = $dbh->prepare($query);
1761 $stw->execute(@values) || $form->dberror($query);
1763 $ref->{taxaccounts} = "";
1765 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1767 # if ($customertax{$ref->{accno}}) {
1768 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1772 $ref->{taxaccounts} .= "$ptr->{accno} ";
1774 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1775 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1776 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1777 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1778 $form->{taxaccounts} .= "$ptr->{accno} ";
1784 chop $ref->{taxaccounts};
1785 if ($form->{language_id}) {
1787 qq|SELECT tr.translation, tr.longdescription
1789 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1790 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1791 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1792 if ($translation ne "") {
1793 $ref->{description} = $translation;
1794 $ref->{longdescription} = $longdescription;
1798 qq|SELECT tr.translation, tr.longdescription
1800 WHERE tr.language_id IN
1803 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1806 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1807 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1808 if ($translation ne "") {
1809 $ref->{description} = $translation;
1810 $ref->{longdescription} = $longdescription;
1815 push @{ $form->{item_list} }, $ref;
1817 if ($form->{lizenzen}) {
1818 if ($ref->{inventory_accno} > 0) {
1822 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1823 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1824 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1825 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1834 $main::lxdebug->leave_sub();
1837 ##########################
1838 # get pricegroups from database
1839 # build up selected pricegroup
1840 # if an exchange rate - change price
1843 sub get_pricegroups_for_parts {
1845 $main::lxdebug->enter_sub();
1847 my ($self, $myconfig, $form) = @_;
1849 my $dbh = $form->dbconnect($myconfig);
1851 $form->{"PRICES"} = {};
1855 my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
1856 my $service_units = AM->retrieve_units($myconfig, $form, "service");
1857 my $all_units = AM->retrieve_units($myconfig, $form);
1858 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1859 $form->{"PRICES"}{$i} = [];
1861 $id = $form->{"id_$i"};
1863 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1865 $id = $form->{"new_id_$i"};
1868 ($price, $selectedpricegroup_id) = split(/--/,
1869 $form->{"sellprice_pg_$i"});
1871 $pricegroup_old = $form->{"pricegroup_old_$i"};
1872 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1873 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1875 $price_new = $form->{"price_new_$i"};
1876 $price_old = $form->{"price_old_$i"};
1878 if (!$form->{"unit_old_$i"}) {
1879 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1880 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1881 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1882 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1885 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1886 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1887 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1889 my $check_units = $form->{"inventory_accno_$i"} ? $dimension_units : $service_units;
1890 if (!$check_units->{$form->{"selected_unit_$i"}} ||
1891 ($check_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1892 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1893 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1894 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1895 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1896 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1901 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1902 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1903 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1904 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1905 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1909 if (!$form->{"basefactor_$i"}) {
1910 $form->{"basefactor_$i"} = 1;
1916 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1917 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1927 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1929 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1930 'selected' AS selected
1933 ORDER BY pricegroup|;
1934 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1935 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1937 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1939 $pkr->{selected} = '';
1941 # if there is an exchange rate change price
1942 if (($form->{exchangerate} * 1) != 0) {
1944 $pkr->{price} /= $form->{exchangerate};
1947 $pkr->{price} *= $form->{"basefactor_$i"};
1949 $pkr->{price} *= $basefactor;
1951 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1953 if ($selectedpricegroup_id eq undef) {
1954 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1956 $pkr->{selected} = ' selected';
1958 # no customer pricesgroup set
1959 if ($pkr->{price} == $pkr->{default_sellprice}) {
1961 $pkr->{price} = $form->{"sellprice_$i"};
1965 $form->{"sellprice_$i"} = $pkr->{price};
1968 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1969 $pkr->{price} = $form->{"sellprice_$i"};
1970 $pkr->{selected} = ' selected';
1974 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1975 if ($selectedpricegroup_id ne $pricegroup_old) {
1976 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1977 $pkr->{selected} = ' selected';
1979 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1980 if ($pkr->{pricegroup_id} == 0) {
1981 $pkr->{price} = $form->{"sellprice_$i"};
1982 $pkr->{selected} = ' selected';
1984 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1985 $pkr->{selected} = ' selected';
1986 if ( ($pkr->{pricegroup_id} == 0)
1987 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1988 # $pkr->{price} = $form->{"sellprice_$i"};
1990 $pkr->{price} = $form->{"sellprice_$i"};
1994 push @{ $form->{PRICES}{$i} }, $pkr;
1997 $form->{"basefactor_$i"} *= $basefactor;
2006 $main::lxdebug->leave_sub();
2010 $main::lxdebug->enter_sub();
2012 my ($self, $myconfig, $form, $table) = @_;
2014 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2016 # make sure there's no funny stuff in $table
2017 # ToDO: die when this happens and throw an error
2018 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2020 my $dbh = $form->dbconnect($myconfig);
2022 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2023 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2027 $main::lxdebug->leave_sub();
2033 $main::lxdebug->enter_sub();
2035 my ($self, $myconfig, $form, $table, $id) = @_;
2037 $main::lxdebug->leave_sub() and return 0 unless ($id);
2039 # make sure there's no funny stuff in $table
2040 # ToDO: die when this happens and throw an error
2041 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2043 my $dbh = $form->dbconnect($myconfig);
2045 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2046 my ($result) = selectrow_query($form, $dbh, $query, $id);
2050 $main::lxdebug->leave_sub();