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);
48 $main::lxdebug->enter_sub();
50 my ($self, $myconfig, $form, $locale) = @_;
52 $form->{duedate} ||= $form->{invdate};
55 my $dbh = $form->dbconnect($myconfig);
58 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
59 ($form->{terms}) = selectrow_query($form, $dbh, $query);
61 my (@project_ids, %projectnumbers);
63 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
65 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
68 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
69 $price_factors{$pfac->{id}} = $pfac;
71 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
74 # sort items by partsgroup
75 for $i (1 .. $form->{rowcount}) {
77 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
78 $partsgroup = $form->{"partsgroup_$i"};
80 push @partsgroup, [$i, $partsgroup];
81 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
85 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
86 join(", ", map({ "?" } @project_ids)) . ")";
87 $sth = $dbh->prepare($query);
88 $sth->execute(@project_ids) ||
89 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
90 while (my $ref = $sth->fetchrow_hashref()) {
91 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
96 $form->{"globalprojectnumber"} =
97 $projectnumbers{$form->{"globalproject_id"}};
104 my %oid = ('Pg' => 'oid',
105 'Oracle' => 'rowid');
107 # sort items by partsgroup
108 for $i (1 .. $form->{rowcount}) {
110 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
111 $partsgroup = $form->{"partsgroup_$i"};
113 push @partsgroup, [$i, $partsgroup];
126 my $nodiscount_subtotal = 0;
127 my $discount_subtotal = 0;
129 my $subtotal_header = 0;
133 qw(runningnumber number description longdescription qty ship unit bin
134 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
135 partnotes serialnumber reqdate sellprice listprice netprice
136 discount p_discount discount_sub nodiscount_sub
137 linetotal nodiscount_linetotal tax_rate projectnumber
138 price_factor price_factor_name);
141 qw(taxbase tax taxdescription taxrate taxnumber);
143 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
146 if ($item->[1] ne $sameitem) {
147 push(@{ $form->{description} }, qq|$item->[1]|);
148 $sameitem = $item->[1];
150 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
153 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
155 if ($form->{"id_$i"} != 0) {
157 # add number, description and qty to $form->{number},
158 if ($form->{"subtotal_$i"} && !$subtotal_header) {
159 $subtotal_header = $i;
160 $position = int($position);
163 } elsif ($subtotal_header) {
165 $position = int($position);
166 $position = $position.".".$subposition;
168 $position = int($position);
172 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
174 push @{ $form->{runningnumber} }, $position;
175 push @{ $form->{number} }, $form->{"partnumber_$i"};
176 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
177 push @{ $form->{bin} }, $form->{"bin_$i"};
178 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
179 push @{ $form->{description} }, $form->{"description_$i"};
180 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
181 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
182 push @{ $form->{unit} }, $form->{"unit_$i"};
183 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
184 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
185 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
186 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
187 push @{ $form->{invnumber} }, $form->{"invnumber"};
188 push @{ $form->{invdate} }, $form->{"invdate"};
189 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
190 push @{ $form->{price_factor_name} }, $price_factor->{description};
192 if ($form->{lizenzen}) {
193 if ($form->{"licensenumber_$i"}) {
194 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
195 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
196 push(@{ $form->{licensenumber} }, $licensenumber);
197 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
200 push(@{ $form->{licensenumber} }, "");
201 push(@{ $form->{validuntil} }, "");
206 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
208 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
209 my ($dec) = ($sellprice =~ /\.(\d+)/);
210 my $decimalplaces = max 2, length($dec);
212 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
213 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
214 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
215 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
217 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
219 $linetotal = ($linetotal != 0) ? $linetotal : '';
221 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
222 push @{ $form->{p_discount} }, $form->{"discount_$i"};
224 $form->{total} += $linetotal;
225 $form->{nodiscount_total} += $nodiscount_linetotal;
226 $form->{discount_total} += $discount;
228 if ($subtotal_header) {
229 $discount_subtotal += $linetotal;
230 $nodiscount_subtotal += $nodiscount_linetotal;
233 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
234 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
235 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
237 $discount_subtotal = 0;
238 $nodiscount_subtotal = 0;
239 $subtotal_header = 0;
242 push @{ $form->{discount_sub} }, "";
243 push @{ $form->{nodiscount_sub} }, "";
246 if (!$form->{"discount_$i"}) {
247 $nodiscount += $linetotal;
250 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
251 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
253 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
255 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
259 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
261 if ($form->{taxincluded}) {
264 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
265 $taxbase = $linetotal - $taxamount;
267 $taxamount = $linetotal * $taxrate;
268 $taxbase = $linetotal;
271 if ($form->round_amount($taxrate, 7) == 0) {
272 if ($form->{taxincluded}) {
273 foreach $item (@taxaccounts) {
275 $form->round_amount($linetotal * $form->{"${item}_rate"} /
276 (1 + abs($form->{"${item}_rate"})),
279 $taxaccounts{$item} += $taxamount;
280 $taxdiff += $taxamount;
282 $taxbase{$item} += $taxbase;
284 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
286 foreach $item (@taxaccounts) {
287 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
288 $taxbase{$item} += $taxbase;
292 foreach $item (@taxaccounts) {
293 $taxaccounts{$item} +=
294 $taxamount * $form->{"${item}_rate"} / $taxrate;
295 $taxbase{$item} += $taxbase;
298 $tax_rate = $taxrate * 100;
299 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
300 if ($form->{"assembly_$i"}) {
303 # get parts and push them onto the stack
305 if ($form->{groupitems}) {
307 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
309 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
313 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
315 JOIN parts p ON (a.parts_id = p.id)
316 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
317 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
318 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
320 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
321 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
322 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
323 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
324 push(@{ $form->{description} }, $sameitem);
327 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
329 push(@{ $form->{description} },
330 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
332 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
333 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
341 foreach my $item (sort keys %taxaccounts) {
342 push(@{ $form->{taxbase} },
343 $form->format_amount($myconfig, $taxbase{$item}, 2));
345 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
347 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
348 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
349 push(@{ $form->{taxrate} },
350 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
351 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
354 for my $i (1 .. $form->{paidaccounts}) {
355 if ($form->{"paid_$i"}) {
356 push(@{ $form->{payment} }, $form->{"paid_$i"});
357 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
358 push(@{ $form->{paymentaccount} }, $description);
359 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
360 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
362 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
365 if($form->{taxincluded}) {
366 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
369 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
372 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
373 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
374 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
375 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
377 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
378 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
380 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
381 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
383 $form->set_payment_options($myconfig, $form->{invdate});
385 $form->{username} = $myconfig->{name};
389 $main::lxdebug->leave_sub();
392 sub project_description {
393 $main::lxdebug->enter_sub();
395 my ($self, $dbh, $id) = @_;
397 my $query = qq|SELECT description FROM project WHERE id = ?|;
398 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
400 $main::lxdebug->leave_sub();
405 sub customer_details {
406 $main::lxdebug->enter_sub();
408 my ($self, $myconfig, $form, @wanted_vars) = @_;
410 # connect to database
411 my $dbh = $form->dbconnect($myconfig);
413 # get contact id, set it if nessessary
416 my @values = (conv_i($form->{customer_id}));
419 if ($form->{cp_id}) {
420 $where = qq| AND (cp.cp_id = ?) |;
421 push(@values, conv_i($form->{cp_id}));
424 # get rest for the customer
426 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
427 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
429 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
430 WHERE (ct.id = ?) $where
433 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
435 # remove id and taxincluded before copy back
436 delete @$ref{qw(id taxincluded)};
438 @wanted_vars = grep({ $_ } @wanted_vars);
439 if (scalar(@wanted_vars) > 0) {
441 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
442 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
445 map { $form->{$_} = $ref->{$_} } keys %$ref;
447 if ($form->{delivery_customer_id}) {
449 qq|SELECT *, notes as customernotes
453 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
455 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
458 if ($form->{delivery_vendor_id}) {
460 qq|SELECT *, notes as customernotes
464 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
466 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
469 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
471 'trans_id' => $form->{customer_id});
472 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
476 $main::lxdebug->leave_sub();
480 $main::lxdebug->enter_sub();
482 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
484 # connect to database, turn off autocommit
485 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
487 my ($query, $sth, $null, $project_id, @values);
488 my $exchangerate = 0;
490 if (!$form->{employee_id}) {
491 $form->get_employee($dbh);
494 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
496 ($null, $form->{department_id}) = split(/--/, $form->{department});
498 my $all_units = AM->retrieve_units($myconfig, $form);
500 if (!$payments_only) {
502 &reverse_invoice($dbh, $form);
505 $query = qq|SELECT nextval('glid')|;
506 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
508 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
509 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
511 if (!$form->{invnumber}) {
513 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
514 "cnnumber" : "invnumber", $dbh);
519 my ($netamount, $invoicediff) = (0, 0);
520 my ($amount, $linetotal, $lastincomeaccno);
522 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
523 my $defaultcurrency = (split m/:/, $currencies)[0];
525 if ($form->{currency} eq $defaultcurrency) {
526 $form->{exchangerate} = 1;
528 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
531 $form->{exchangerate} =
534 : $form->parse_amount($myconfig, $form->{exchangerate});
536 $form->{expense_inventory} = "";
540 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
541 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
544 foreach my $i (1 .. $form->{rowcount}) {
545 if ($form->{type} eq "credit_note") {
546 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
547 $form->{shipped} = 1;
549 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
554 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
555 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
556 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
558 if ($form->{storno}) {
559 $form->{"qty_$i"} *= -1;
562 if ($form->{"id_$i"}) {
565 if (defined($baseunits{$form->{"id_$i"}})) {
566 $item_unit = $baseunits{$form->{"id_$i"}};
569 $query = qq|SELECT unit FROM parts WHERE id = ?|;
570 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
571 $baseunits{$form->{"id_$i"}} = $item_unit;
574 if (defined($all_units->{$item_unit}->{factor})
575 && ($all_units->{$item_unit}->{factor} ne '')
576 && ($all_units->{$item_unit}->{factor} != 0)) {
577 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
581 $baseqty = $form->{"qty_$i"} * $basefactor;
583 my ($allocated, $taxrate) = (0, 0);
587 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
589 # keep entered selling price
591 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
593 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
595 my $decimalplaces = ($dec > 2) ? $dec : 2;
597 # undo discount formatting
598 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
601 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
603 # round linetotal to 2 decimal places
604 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
605 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
607 if ($form->{taxincluded}) {
608 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
609 $form->{"sellprice_$i"} =
610 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
612 $taxamount = $linetotal * $taxrate;
615 $netamount += $linetotal;
617 if ($taxamount != 0) {
619 $form->{amount}{ $form->{id} }{$_} +=
620 $taxamount * $form->{"${_}_rate"} / $taxrate
621 } split(/ /, $form->{"taxaccounts_$i"});
624 # add amount to income, $form->{amount}{trans_id}{accno}
625 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
627 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
628 $linetotal = $form->round_amount($linetotal, 2);
630 # this is the difference from the inventory
631 $invoicediff += ($amount - $linetotal);
633 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
636 $lastincomeaccno = $form->{"income_accno_$i"};
638 # adjust and round sellprice
639 $form->{"sellprice_$i"} =
640 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
643 next if $payments_only;
645 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
647 if ($form->{"assembly_$i"}) {
648 # record assembly item as allocated
649 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
652 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
656 # get pricegroup_id and save it
657 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
660 # save detail record in invoice table
662 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
663 sellprice, fxsellprice, discount, allocated, assemblyitem,
664 unit, deliverydate, project_id, serialnumber, pricegroup_id,
665 ordnumber, transdate, cusordnumber, base_qty, subtotal,
666 marge_percent, marge_total, lastcost,
667 price_factor_id, price_factor, marge_price_factor)
668 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
669 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
671 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
672 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
673 $form->{"sellprice_$i"}, $fxsellprice,
674 $form->{"discount_$i"}, $allocated, 'f',
675 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
676 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
677 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
678 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
679 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
680 $form->{"lastcost_$i"},
681 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
682 conv_i($form->{"marge_price_factor_$i"}));
683 do_query($form, $dbh, $query, @values);
685 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
687 qq|INSERT INTO licenseinvoice (trans_id, license_id)
688 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
689 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
690 do_query($form, $dbh, $query, @values);
695 $form->{datepaid} = $form->{invdate};
697 # total payments, don't move we need it here
698 for my $i (1 .. $form->{paidaccounts}) {
699 if ($form->{type} eq "credit_note") {
700 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
702 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
704 $form->{paid} += $form->{"paid_$i"};
705 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
708 my ($tax, $diff) = (0, 0);
710 $netamount = $form->round_amount($netamount, 2);
712 # figure out rounding errors for total amount vs netamount + taxes
713 if ($form->{taxincluded}) {
715 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
716 $diff += $amount - $netamount * $form->{exchangerate};
717 $netamount = $amount;
719 foreach my $item (split(/ /, $form->{taxaccounts})) {
720 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
721 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
722 $tax += $form->{amount}{ $form->{id} }{$item};
723 $netamount -= $form->{amount}{ $form->{id} }{$item};
726 $invoicediff += $diff;
727 ######## this only applies to tax included
728 if ($lastincomeaccno) {
729 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
733 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
734 $diff = $amount - $netamount * $form->{exchangerate};
735 $netamount = $amount;
736 foreach my $item (split(/ /, $form->{taxaccounts})) {
737 $form->{amount}{ $form->{id} }{$item} =
738 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
741 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
744 $amount - $form->{amount}{ $form->{id} }{$item} *
745 $form->{exchangerate};
746 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
747 $tax += $form->{amount}{ $form->{id} }{$item};
751 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
753 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
756 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
758 # update exchangerate
759 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
760 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
761 $form->{exchangerate}, 0);
764 $project_id = conv_i($form->{"globalproject_id"});
766 foreach my $trans_id (keys %{ $form->{amount} }) {
767 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
768 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
770 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
772 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
774 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
775 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
776 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
777 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
778 do_query($form, $dbh, $query, @values);
779 $form->{amount}{$trans_id}{$accno} = 0;
783 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
784 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
786 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
788 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
789 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
790 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
791 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
792 do_query($form, $dbh, $query, @values);
797 # deduct payment differences from diff
798 for my $i (1 .. $form->{paidaccounts}) {
799 if ($form->{"paid_$i"} != 0) {
801 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
802 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
806 # record payments and offsetting AR
807 if (!$form->{storno}) {
808 for my $i (1 .. $form->{paidaccounts}) {
810 next if ($form->{"paid_$i"} == 0);
812 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
813 $form->{"datepaid_$i"} = $form->{invdate}
814 unless ($form->{"datepaid_$i"});
815 $form->{datepaid} = $form->{"datepaid_$i"};
819 if ($form->{currency} eq $defaultcurrency) {
820 $form->{"exchangerate_$i"} = 1;
822 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
823 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
827 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
829 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
833 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
834 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
835 do_query($form, $dbh, $query, @values);
839 $form->{"paid_$i"} *= -1;
842 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
843 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
844 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
845 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
846 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
847 do_query($form, $dbh, $query, @values);
849 # exchangerate difference
850 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
851 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
855 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
856 $form->{"exchangerate_$i"};
858 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
861 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
867 # update exchange rate
868 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
869 $form->update_exchangerate($dbh, $form->{currency},
870 $form->{"datepaid_$i"},
871 $form->{"exchangerate_$i"}, 0);
875 } else { # if (!$form->{storno})
876 $form->{marge_total} *= -1;
879 if ($payments_only) {
880 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
881 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
883 if (!$provided_dbh) {
888 $main::lxdebug->leave_sub();
892 # record exchange rate differences and gains/losses
893 foreach my $accno (keys %{ $form->{fx} }) {
894 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
896 ($form->{fx}{$accno}{$transdate} =
897 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
902 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
903 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
904 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
905 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
906 do_query($form, $dbh, $query, @values);
911 $amount = $netamount + $tax;
914 $query = qq|UPDATE ar set
915 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
916 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
917 amount = ?, netamount = ?, paid = ?, datepaid = ?,
918 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
919 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
920 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
921 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
922 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
923 cp_id = ?, marge_total = ?, marge_percent = ?,
924 globalproject_id = ?, delivery_customer_id = ?,
925 transaction_description = ?, delivery_vendor_id = ?
927 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
928 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
929 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
930 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
931 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
932 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
933 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
934 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
935 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
936 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
937 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
938 conv_i($form->{"id"}));
939 do_query($form, $dbh, $query, @values);
941 if($form->{"formname"} eq "credit_note") {
942 for my $i (1 .. $form->{rowcount}) {
943 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
944 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
945 do_query($form, $dbh, $query, @values);
949 if ($form->{storno}) {
952 paid = paid + amount,
954 intnotes = ? || intnotes
956 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
957 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
961 $form->{name} = $form->{customer};
962 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
964 if (!$form->{shipto_id}) {
965 $form->add_shipto($dbh, $form->{id}, "AR");
968 # save printed, emailed and queued
969 $form->save_status($dbh);
971 Common::webdav_folder($form) if ($main::webdav);
973 my @close_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{close_do_ids};
975 if (scalar @close_do_ids) {
976 DO->close_orders('dbh' => $dbh,
977 'ids' => \@close_do_ids);
981 if (!$provided_dbh) {
986 $main::lxdebug->leave_sub();
991 sub _delete_payments {
992 $main::lxdebug->enter_sub();
994 my ($self, $form, $dbh) = @_;
998 # Delete old payment entries from acc_trans.
1002 WHERE (trans_id = ?) AND fx_transaction
1008 LEFT JOIN chart c ON (at.chart_id = c.id)
1009 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1010 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1015 LEFT JOIN chart c ON (at.chart_id = c.id)
1016 WHERE (trans_id = ?)
1017 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1020 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1023 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1024 do_query($form, $dbh, $query);
1027 $main::lxdebug->leave_sub();
1031 $main::lxdebug->enter_sub();
1033 my ($self, $myconfig, $form, $locale) = @_;
1035 # connect to database, turn off autocommit
1036 my $dbh = $form->dbconnect_noauto($myconfig);
1038 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1040 $old_form = save_form();
1042 # Delete all entries in acc_trans from prior payments.
1043 $self->_delete_payments($form, $dbh);
1045 # Save the new payments the user made before cleaning up $form.
1046 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1048 # Clean up $form so that old content won't tamper the results.
1049 %keep_vars = map { $_, 1 } qw(login password id);
1050 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1052 # Retrieve the invoice from the database.
1053 $self->retrieve_invoice($myconfig, $form);
1055 # Set up the content of $form in the way that IS::post_invoice() expects.
1056 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1058 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1059 $item = $form->{invoice_details}->[$row - 1];
1061 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1063 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1066 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1068 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1070 # Restore the payment options from the user input.
1071 map { $form->{$_} = $payments{$_} } keys %payments;
1073 # Get the AR accno (which is normally done by Form::create_links()).
1077 LEFT JOIN chart c ON (at.chart_id = c.id)
1078 WHERE (trans_id = ?)
1079 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1083 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1085 # Post the new payments.
1086 $self->post_invoice($myconfig, $form, $dbh, 1);
1088 restore_form($old_form);
1090 my $rc = $dbh->commit();
1093 $main::lxdebug->leave_sub();
1098 sub process_assembly {
1099 $main::lxdebug->enter_sub();
1101 my ($dbh, $form, $id, $totalqty) = @_;
1104 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1105 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1107 JOIN parts p ON (a.parts_id = p.id)
1109 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1111 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1115 $ref->{inventory_accno_id} *= 1;
1116 $ref->{expense_accno_id} *= 1;
1118 # multiply by number of assemblies
1119 $ref->{qty} *= $totalqty;
1121 if ($ref->{assembly}) {
1122 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1125 if ($ref->{inventory_accno_id}) {
1126 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1130 # save detail record for individual assembly item in invoice table
1132 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1133 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1134 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1135 do_query($form, $dbh, $query, @values);
1141 $main::lxdebug->leave_sub();
1145 $main::lxdebug->enter_sub();
1147 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1148 $form->{taxzone_id} *=1;
1149 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1150 my $taxzone_id = $form->{"taxzone_id"} * 1;
1152 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1153 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1154 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1155 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1156 FROM invoice i, parts p
1157 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1158 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1159 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1160 WHERE (i.parts_id = p.id)
1161 AND (i.parts_id = ?)
1162 AND ((i.base_qty + i.allocated) < 0)
1164 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1169 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1170 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1174 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1176 # total expenses and inventory
1177 # sellprice is the cost of the item
1178 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1181 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1183 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1184 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1185 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1187 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1188 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1194 last if (($totalqty -= $qty) <= 0);
1199 $main::lxdebug->leave_sub();
1204 sub reverse_invoice {
1205 $main::lxdebug->enter_sub();
1207 my ($dbh, $form) = @_;
1209 # reverse inventory items
1211 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1213 JOIN parts p ON (i.parts_id = p.id)
1214 WHERE i.trans_id = ?|;
1215 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1217 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1219 if ($ref->{inventory_accno_id}) {
1220 # de-allocated purchases
1222 qq|SELECT i.id, i.trans_id, i.allocated
1224 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1225 ORDER BY i.trans_id DESC|;
1226 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1228 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1230 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1231 $qty = $inhref->{allocated};
1235 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1237 last if (($ref->{qty} -= $qty) <= 0);
1246 @values = (conv_i($form->{id}));
1247 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1248 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1250 if ($form->{lizenzen}) {
1252 qq|DELETE FROM licenseinvoice
1253 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1254 do_query($form, $dbh, $query, @values);
1257 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1259 $main::lxdebug->leave_sub();
1262 sub delete_invoice {
1263 $main::lxdebug->enter_sub();
1265 my ($self, $myconfig, $form, $spool) = @_;
1267 # connect to database
1268 my $dbh = $form->dbconnect_noauto($myconfig);
1270 &reverse_invoice($dbh, $form);
1272 my @values = (conv_i($form->{id}));
1275 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1277 # delete spool files
1278 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1280 # delete status entries
1281 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1283 my $rc = $dbh->commit;
1287 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1290 $main::lxdebug->leave_sub();
1295 sub retrieve_invoice {
1296 $main::lxdebug->enter_sub();
1298 my ($self, $myconfig, $form) = @_;
1300 # connect to database
1301 my $dbh = $form->dbconnect_noauto($myconfig);
1303 my ($sth, $ref, $query);
1305 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1309 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1310 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1311 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1312 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1313 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1314 d.curr AS currencies
1318 $ref = selectfirst_hashref_query($form, $dbh, $query);
1319 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1322 my $id = conv_i($form->{id});
1327 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1328 a.orddate, a.quodate, a.globalproject_id,
1329 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1330 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1331 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1332 a.employee_id, a.salesman_id, a.payment_id,
1333 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1334 a.transaction_description,
1335 a.marge_total, a.marge_percent,
1338 LEFT JOIN employee e ON (e.id = a.employee_id)
1340 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1341 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1344 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1347 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1348 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1350 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1352 foreach my $vc (qw(customer vendor)) {
1353 next if !$form->{"delivery_${vc}_id"};
1354 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1357 # get printed, emailed
1358 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1359 $sth = prepare_execute_query($form, $dbh, $query, $id);
1361 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1362 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1363 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1364 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1367 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1369 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1370 : $form->{invdate} ? $dbh->quote($form->{invdate})
1374 my $taxzone_id = $form->{taxzone_id} *= 1;
1375 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1377 # retrieve individual items
1380 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1381 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1382 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1384 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1385 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1386 i.price_factor_id, i.price_factor, i.marge_price_factor,
1387 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1388 pr.projectnumber, pg.partsgroup, prg.pricegroup
1391 LEFT JOIN parts p ON (i.parts_id = p.id)
1392 LEFT JOIN project pr ON (i.project_id = pr.id)
1393 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1394 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1396 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1397 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1398 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1400 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1402 $sth = prepare_execute_query($form, $dbh, $query, $id);
1404 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1405 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1406 delete($ref->{"part_inventory_accno_id"});
1408 foreach my $type (qw(inventory income expense)) {
1409 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1410 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1411 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1415 # get tax rates and description
1416 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1418 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1419 LEFT JOIN chart c ON (c.id = t.chart_id)
1421 (SELECT tk.tax_id FROM taxkeys tk
1422 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1423 AND startdate <= date($transdate)
1424 ORDER BY startdate DESC LIMIT 1)
1426 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1427 $ref->{taxaccounts} = "";
1429 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1431 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1435 $ref->{taxaccounts} .= "$ptr->{accno} ";
1437 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1438 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1439 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1440 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1441 $form->{taxaccounts} .= "$ptr->{accno} ";
1446 if ($form->{lizenzen}) {
1447 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1448 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1449 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1452 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1454 chop $ref->{taxaccounts};
1455 push @{ $form->{invoice_details} }, $ref;
1460 Common::webdav_folder($form) if ($main::webdav);
1463 my $rc = $dbh->commit;
1466 $main::lxdebug->leave_sub();
1472 $main::lxdebug->enter_sub();
1474 my ($self, $myconfig, $form) = @_;
1476 # connect to database
1477 my $dbh = $form->dbconnect($myconfig);
1479 my $dateformat = $myconfig->{dateformat};
1480 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1482 my (@values, $duedate, $ref, $query);
1484 if ($form->{invdate}) {
1485 $duedate = "to_date(?, '$dateformat')";
1486 push @values, $form->{invdate};
1488 $duedate = "current_date";
1491 my $cid = conv_i($form->{customer_id});
1494 if ($form->{payment_id}) {
1495 $payment_id = "(pt.id = ?) OR";
1496 push @values, conv_i($form->{payment_id});
1502 c.name AS customer, c.discount, c.creditlimit, c.terms,
1503 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1504 c.street, c.zipcode, c.city, c.country,
1505 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1506 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1507 b.discount AS tradediscount, b.description AS business
1509 LEFT JOIN business b ON (b.id = c.business_id)
1510 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1513 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1514 map { $form->{$_} = $ref->{$_} } keys %$ref;
1517 qq|SELECT sum(amount - paid) AS dunning_amount
1519 WHERE (paid < amount)
1520 AND (customer_id = ?)
1521 AND (dunning_config_id IS NOT NULL)|;
1522 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1523 map { $form->{$_} = $ref->{$_} } keys %$ref;
1526 qq|SELECT dnn.dunning_description AS max_dunning_level
1527 FROM dunning_config dnn
1528 WHERE id IN (SELECT dunning_config_id
1530 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1531 ORDER BY dunning_level DESC LIMIT 1|;
1532 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1533 map { $form->{$_} = $ref->{$_} } keys %$ref;
1535 $form->{creditremaining} = $form->{creditlimit};
1536 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1537 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1538 $form->{creditremaining} -= $value;
1542 (SELECT e.buy FROM exchangerate e
1543 WHERE e.curr = o.curr
1544 AND e.transdate = o.transdate)
1546 WHERE o.customer_id = ?
1547 AND o.quotation = '0'
1548 AND o.closed = '0'|;
1549 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1551 while (my ($amount, $exch) = $sth->fetchrow_array) {
1552 $exch = 1 unless $exch;
1553 $form->{creditremaining} -= $amount * $exch;
1557 # get shipto if we did not converted an order or invoice
1558 if (!$form->{shipto}) {
1559 map { delete $form->{$_} }
1560 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1561 shiptostreet shiptozipcode shiptocity shiptocountry
1562 shiptocontact shiptophone shiptofax shiptoemail);
1564 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1565 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1567 map { $form->{$_} = $ref->{$_} } keys %$ref;
1570 # setup last accounts used for this customer
1571 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1573 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1575 JOIN acc_trans ac ON (ac.chart_id = c.id)
1576 JOIN ar a ON (a.id = ac.trans_id)
1577 WHERE a.customer_id = ?
1578 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1579 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1580 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1583 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1584 if ($ref->{category} eq 'I') {
1586 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1588 if ($form->{initial_transdate}) {
1590 qq|SELECT tk.tax_id, t.rate
1592 LEFT JOIN tax t ON tk.tax_id = t.id
1593 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1594 ORDER BY tk.startdate DESC
1596 my ($tax_id, $rate) =
1597 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1598 $form->{initial_transdate});
1599 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1602 if ($ref->{category} eq 'A') {
1603 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1607 $form->{rowcount} = $i if ($i && !$form->{type});
1612 $main::lxdebug->leave_sub();
1616 $main::lxdebug->enter_sub();
1618 my ($self, $myconfig, $form) = @_;
1620 # connect to database
1621 my $dbh = $form->dbconnect($myconfig);
1623 my $i = $form->{rowcount};
1625 my $where = qq|NOT p.obsolete = '1'|;
1628 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1629 my ($table, $field) = split m/\./, $column;
1630 next if !$form->{"${field}_${i}"};
1631 $where .= qq| AND lower(${column}) ILIKE ?|;
1632 push @values, '%' . $form->{"${field}_${i}"} . '%';
1635 if ($form->{"description_$i"}) {
1636 $where .= qq| ORDER BY p.description|;
1638 $where .= qq| ORDER BY p.partnumber|;
1642 if ($form->{type} eq "invoice") {
1644 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1645 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1649 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1653 my $taxzone_id = $form->{taxzone_id} * 1;
1654 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1658 p.id, p.partnumber, p.description, p.sellprice,
1659 p.listprice, p.inventory_accno_id, p.lastcost,
1661 c1.accno AS inventory_accno,
1662 c1.new_chart_id AS inventory_new_chart,
1663 date($transdate) - c1.valid_from AS inventory_valid,
1665 c2.accno AS income_accno,
1666 c2.new_chart_id AS income_new_chart,
1667 date($transdate) - c2.valid_from AS income_valid,
1669 c3.accno AS expense_accno,
1670 c3.new_chart_id AS expense_new_chart,
1671 date($transdate) - c3.valid_from AS expense_valid,
1673 p.unit, p.assembly, p.bin, p.onhand,
1674 p.notes AS partnotes, p.notes AS longdescription,
1675 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1678 pfac.factor AS price_factor,
1683 LEFT JOIN chart c1 ON
1684 ((SELECT inventory_accno_id
1685 FROM buchungsgruppen
1686 WHERE id = p.buchungsgruppen_id) = c1.id)
1687 LEFT JOIN chart c2 ON
1688 ((SELECT income_accno_id_${taxzone_id}
1689 FROM buchungsgruppen
1690 WHERE id = p.buchungsgruppen_id) = c2.id)
1691 LEFT JOIN chart c3 ON
1692 ((SELECT expense_accno_id_${taxzone_id}
1693 FROM buchungsgruppen
1694 WHERE id = p.buchungsgruppen_id) = c3.id)
1695 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1696 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1698 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1700 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1702 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1703 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1704 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1705 if (!$ref->{inventory_accno_id}) {
1706 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1708 delete($ref->{inventory_accno_id});
1710 foreach my $type (qw(inventory income expense)) {
1711 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1713 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1716 ($ref->{"${type}_accno"},
1717 $ref->{"${type}_new_chart"},
1718 $ref->{"${type}_valid"})
1719 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1723 if ($form->{payment_id} eq "") {
1724 $form->{payment_id} = $form->{part_payment_id};
1727 # get tax rates and description
1728 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1730 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1732 LEFT JOIN chart c ON (c.id = t.chart_id)
1736 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1738 ORDER BY startdate DESC
1741 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1742 $stw = $dbh->prepare($query);
1743 $stw->execute(@values) || $form->dberror($query);
1745 $ref->{taxaccounts} = "";
1747 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1749 # if ($customertax{$ref->{accno}}) {
1750 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1754 $ref->{taxaccounts} .= "$ptr->{accno} ";
1756 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1757 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1758 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1759 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1760 $form->{taxaccounts} .= "$ptr->{accno} ";
1766 chop $ref->{taxaccounts};
1767 if ($form->{language_id}) {
1769 qq|SELECT tr.translation, tr.longdescription
1771 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1772 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1773 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1774 if ($translation ne "") {
1775 $ref->{description} = $translation;
1776 $ref->{longdescription} = $longdescription;
1780 qq|SELECT tr.translation, tr.longdescription
1782 WHERE tr.language_id IN
1785 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1788 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1789 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1790 if ($translation ne "") {
1791 $ref->{description} = $translation;
1792 $ref->{longdescription} = $longdescription;
1797 $ref->{onhand} *= 1;
1799 push @{ $form->{item_list} }, $ref;
1801 if ($form->{lizenzen}) {
1802 if ($ref->{inventory_accno} > 0) {
1806 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1807 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1808 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1809 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1818 $main::lxdebug->leave_sub();
1821 ##########################
1822 # get pricegroups from database
1823 # build up selected pricegroup
1824 # if an exchange rate - change price
1827 sub get_pricegroups_for_parts {
1829 $main::lxdebug->enter_sub();
1831 my ($self, $myconfig, $form) = @_;
1833 my $dbh = $form->dbconnect($myconfig);
1835 $form->{"PRICES"} = {};
1839 my $all_units = AM->retrieve_units($myconfig, $form);
1840 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1841 $form->{"PRICES"}{$i} = [];
1843 $id = $form->{"id_$i"};
1845 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1847 $id = $form->{"new_id_$i"};
1850 ($price, $selectedpricegroup_id) = split(/--/,
1851 $form->{"sellprice_pg_$i"});
1853 $pricegroup_old = $form->{"pricegroup_old_$i"};
1854 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1855 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1857 $price_new = $form->{"price_new_$i"};
1858 $price_old = $form->{"price_old_$i"};
1860 if (!$form->{"unit_old_$i"}) {
1861 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1862 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1863 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1864 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1867 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1868 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1869 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1871 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1872 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1873 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1874 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1875 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1876 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1877 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1882 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1883 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1884 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1885 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1886 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1890 if (!$form->{"basefactor_$i"}) {
1891 $form->{"basefactor_$i"} = 1;
1897 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1898 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1908 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1910 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1911 'selected' AS selected
1914 ORDER BY pricegroup|;
1915 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1916 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1918 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1920 $pkr->{selected} = '';
1922 # if there is an exchange rate change price
1923 if (($form->{exchangerate} * 1) != 0) {
1925 $pkr->{price} /= $form->{exchangerate};
1928 $pkr->{price} *= $form->{"basefactor_$i"};
1930 $pkr->{price} *= $basefactor;
1932 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1934 if ($selectedpricegroup_id eq undef) {
1935 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1937 $pkr->{selected} = ' selected';
1939 # no customer pricesgroup set
1940 if ($pkr->{price} == $pkr->{default_sellprice}) {
1942 $pkr->{price} = $form->{"sellprice_$i"};
1946 $form->{"sellprice_$i"} = $pkr->{price};
1949 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1950 $pkr->{price} = $form->{"sellprice_$i"};
1951 $pkr->{selected} = ' selected';
1955 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1956 if ($selectedpricegroup_id ne $pricegroup_old) {
1957 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1958 $pkr->{selected} = ' selected';
1960 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1961 if ($pkr->{pricegroup_id} == 0) {
1962 $pkr->{price} = $form->{"sellprice_$i"};
1963 $pkr->{selected} = ' selected';
1965 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1966 $pkr->{selected} = ' selected';
1967 if ( ($pkr->{pricegroup_id} == 0)
1968 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1969 # $pkr->{price} = $form->{"sellprice_$i"};
1971 $pkr->{price} = $form->{"sellprice_$i"};
1975 push @{ $form->{PRICES}{$i} }, $pkr;
1978 $form->{"basefactor_$i"} *= $basefactor;
1987 $main::lxdebug->leave_sub();
1991 $main::lxdebug->enter_sub();
1993 my ($self, $myconfig, $form, $table) = @_;
1995 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
1997 # make sure there's no funny stuff in $table
1998 # ToDO: die when this happens and throw an error
1999 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2001 my $dbh = $form->dbconnect($myconfig);
2003 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2004 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2008 $main::lxdebug->leave_sub();
2014 $main::lxdebug->enter_sub();
2016 my ($self, $myconfig, $form, $table, $id) = @_;
2018 $main::lxdebug->leave_sub() and return 0 unless ($id);
2020 # make sure there's no funny stuff in $table
2021 # ToDO: die when this happens and throw an error
2022 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2024 my $dbh = $form->dbconnect($myconfig);
2026 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2027 my ($result) = selectrow_query($form, $dbh, $query, $id);
2031 $main::lxdebug->leave_sub();