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);
49 $main::lxdebug->enter_sub();
51 my ($self, $myconfig, $form, $locale) = @_;
53 $form->{duedate} ||= $form->{invdate};
56 my $dbh = $form->dbconnect($myconfig);
59 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
60 ($form->{terms}) = selectrow_query($form, $dbh, $query);
62 my (@project_ids, %projectnumbers);
64 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
66 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
69 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
70 $price_factors{$pfac->{id}} = $pfac;
72 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
75 # sort items by partsgroup
76 for $i (1 .. $form->{rowcount}) {
78 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
79 $partsgroup = $form->{"partsgroup_$i"};
81 push @partsgroup, [$i, $partsgroup];
82 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
86 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
87 join(", ", map({ "?" } @project_ids)) . ")";
88 $sth = $dbh->prepare($query);
89 $sth->execute(@project_ids) ||
90 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
91 while (my $ref = $sth->fetchrow_hashref()) {
92 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
97 $form->{"globalprojectnumber"} =
98 $projectnumbers{$form->{"globalproject_id"}};
105 my %oid = ('Pg' => 'oid',
106 'Oracle' => 'rowid');
108 # sort items by partsgroup
109 for $i (1 .. $form->{rowcount}) {
111 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
112 $partsgroup = $form->{"partsgroup_$i"};
114 push @partsgroup, [$i, $partsgroup];
127 my $nodiscount_subtotal = 0;
128 my $discount_subtotal = 0;
130 my $subtotal_header = 0;
133 $form->{discount} = [];
136 qw(runningnumber number description longdescription qty ship unit bin
137 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
138 partnotes serialnumber reqdate sellprice listprice netprice
139 discount p_discount discount_sub nodiscount_sub
140 linetotal nodiscount_linetotal tax_rate projectnumber
141 price_factor price_factor_name);
144 qw(taxbase tax taxdescription taxrate taxnumber);
146 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
149 if ($item->[1] ne $sameitem) {
150 push(@{ $form->{description} }, qq|$item->[1]|);
151 $sameitem = $item->[1];
153 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
156 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
158 if ($form->{"id_$i"} != 0) {
160 # add number, description and qty to $form->{number},
161 if ($form->{"subtotal_$i"} && !$subtotal_header) {
162 $subtotal_header = $i;
163 $position = int($position);
166 } elsif ($subtotal_header) {
168 $position = int($position);
169 $position = $position.".".$subposition;
171 $position = int($position);
175 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
177 push @{ $form->{runningnumber} }, $position;
178 push @{ $form->{number} }, $form->{"partnumber_$i"};
179 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
180 push @{ $form->{bin} }, $form->{"bin_$i"};
181 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
182 push @{ $form->{description} }, $form->{"description_$i"};
183 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
184 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
185 push @{ $form->{unit} }, $form->{"unit_$i"};
186 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
187 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
188 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
189 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
190 push @{ $form->{invnumber} }, $form->{"invnumber"};
191 push @{ $form->{invdate} }, $form->{"invdate"};
192 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
193 push @{ $form->{price_factor_name} }, $price_factor->{description};
195 if ($form->{lizenzen}) {
196 if ($form->{"licensenumber_$i"}) {
197 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
198 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
199 push(@{ $form->{licensenumber} }, $licensenumber);
200 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
203 push(@{ $form->{licensenumber} }, "");
204 push(@{ $form->{validuntil} }, "");
209 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
211 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
212 my ($dec) = ($sellprice =~ /\.(\d+)/);
213 my $decimalplaces = max 2, length($dec);
215 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
216 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
217 my $linetotal = $form->round_amount($linetotal_exact, 2);
218 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
220 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
221 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
223 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
225 $linetotal = ($linetotal != 0) ? $linetotal : '';
227 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
228 push @{ $form->{p_discount} }, $form->{"discount_$i"};
230 $form->{total} += $linetotal;
231 $form->{nodiscount_total} += $nodiscount_linetotal;
232 $form->{discount_total} += $discount;
234 if ($subtotal_header) {
235 $discount_subtotal += $linetotal;
236 $nodiscount_subtotal += $nodiscount_linetotal;
239 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
240 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
241 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
243 $discount_subtotal = 0;
244 $nodiscount_subtotal = 0;
245 $subtotal_header = 0;
248 push @{ $form->{discount_sub} }, "";
249 push @{ $form->{nodiscount_sub} }, "";
252 if (!$form->{"discount_$i"}) {
253 $nodiscount += $linetotal;
256 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
257 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
259 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
261 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
265 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
267 if ($form->{taxincluded}) {
270 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
271 $taxbase = $linetotal - $taxamount;
273 $taxamount = $linetotal * $taxrate;
274 $taxbase = $linetotal;
277 if ($form->round_amount($taxrate, 7) == 0) {
278 if ($form->{taxincluded}) {
279 foreach $item (@taxaccounts) {
281 $form->round_amount($linetotal * $form->{"${item}_rate"} /
282 (1 + abs($form->{"${item}_rate"})),
285 $taxaccounts{$item} += $taxamount;
286 $taxdiff += $taxamount;
288 $taxbase{$item} += $taxbase;
290 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
292 foreach $item (@taxaccounts) {
293 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
294 $taxbase{$item} += $taxbase;
298 foreach $item (@taxaccounts) {
299 $taxaccounts{$item} +=
300 $taxamount * $form->{"${item}_rate"} / $taxrate;
301 $taxbase{$item} += $taxbase;
304 $tax_rate = $taxrate * 100;
305 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
306 if ($form->{"assembly_$i"}) {
309 # get parts and push them onto the stack
311 if ($form->{groupitems}) {
313 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
315 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
319 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
321 JOIN parts p ON (a.parts_id = p.id)
322 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
323 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
324 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
326 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
327 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
328 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
329 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
330 push(@{ $form->{description} }, $sameitem);
333 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
335 push(@{ $form->{description} },
336 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
338 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
339 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 foreach my $item (sort keys %taxaccounts) {
348 push(@{ $form->{taxbase} },
349 $form->format_amount($myconfig, $taxbase{$item}, 2));
351 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
353 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
354 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
355 push(@{ $form->{taxrate} },
356 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
357 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
360 for my $i (1 .. $form->{paidaccounts}) {
361 if ($form->{"paid_$i"}) {
362 push(@{ $form->{payment} }, $form->{"paid_$i"});
363 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
364 push(@{ $form->{paymentaccount} }, $description);
365 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
366 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
368 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
371 if($form->{taxincluded}) {
372 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
375 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
378 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
379 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
380 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
381 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
383 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
384 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
386 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
387 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
389 $form->set_payment_options($myconfig, $form->{invdate});
391 $form->{username} = $myconfig->{name};
395 $main::lxdebug->leave_sub();
398 sub project_description {
399 $main::lxdebug->enter_sub();
401 my ($self, $dbh, $id) = @_;
403 my $query = qq|SELECT description FROM project WHERE id = ?|;
404 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
406 $main::lxdebug->leave_sub();
411 sub customer_details {
412 $main::lxdebug->enter_sub();
414 my ($self, $myconfig, $form, @wanted_vars) = @_;
416 # connect to database
417 my $dbh = $form->dbconnect($myconfig);
419 # get contact id, set it if nessessary
422 my @values = (conv_i($form->{customer_id}));
425 if ($form->{cp_id}) {
426 $where = qq| AND (cp.cp_id = ?) |;
427 push(@values, conv_i($form->{cp_id}));
430 # get rest for the customer
432 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
433 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
435 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
436 WHERE (ct.id = ?) $where
439 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
441 # remove id and taxincluded before copy back
442 delete @$ref{qw(id taxincluded)};
444 @wanted_vars = grep({ $_ } @wanted_vars);
445 if (scalar(@wanted_vars) > 0) {
447 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
448 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
451 map { $form->{$_} = $ref->{$_} } keys %$ref;
453 if ($form->{delivery_customer_id}) {
455 qq|SELECT *, notes as customernotes
459 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
461 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
464 if ($form->{delivery_vendor_id}) {
466 qq|SELECT *, notes as customernotes
470 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
472 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
475 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
477 'trans_id' => $form->{customer_id});
478 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
482 $main::lxdebug->leave_sub();
486 $main::lxdebug->enter_sub();
488 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
490 # connect to database, turn off autocommit
491 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
493 my ($query, $sth, $null, $project_id, @values);
494 my $exchangerate = 0;
496 if (!$form->{employee_id}) {
497 $form->get_employee($dbh);
500 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
502 ($null, $form->{department_id}) = split(/--/, $form->{department});
504 my $all_units = AM->retrieve_units($myconfig, $form);
506 if (!$payments_only) {
508 &reverse_invoice($dbh, $form);
511 $query = qq|SELECT nextval('glid')|;
512 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
514 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
515 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
517 if (!$form->{invnumber}) {
519 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
520 "cnnumber" : "invnumber", $dbh);
525 my ($netamount, $invoicediff) = (0, 0);
526 my ($amount, $linetotal, $lastincomeaccno);
528 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
529 my $defaultcurrency = (split m/:/, $currencies)[0];
531 if ($form->{currency} eq $defaultcurrency) {
532 $form->{exchangerate} = 1;
534 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
537 $form->{exchangerate} =
540 : $form->parse_amount($myconfig, $form->{exchangerate});
542 $form->{expense_inventory} = "";
546 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
547 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
550 foreach my $i (1 .. $form->{rowcount}) {
551 if ($form->{type} eq "credit_note") {
552 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
553 $form->{shipped} = 1;
555 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
560 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
561 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
562 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
564 if ($form->{storno}) {
565 $form->{"qty_$i"} *= -1;
568 if ($form->{"id_$i"}) {
571 if (defined($baseunits{$form->{"id_$i"}})) {
572 $item_unit = $baseunits{$form->{"id_$i"}};
575 $query = qq|SELECT unit FROM parts WHERE id = ?|;
576 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
577 $baseunits{$form->{"id_$i"}} = $item_unit;
580 if (defined($all_units->{$item_unit}->{factor})
581 && ($all_units->{$item_unit}->{factor} ne '')
582 && ($all_units->{$item_unit}->{factor} != 0)) {
583 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
587 $baseqty = $form->{"qty_$i"} * $basefactor;
589 my ($allocated, $taxrate) = (0, 0);
593 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
595 # keep entered selling price
597 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
599 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
601 my $decimalplaces = ($dec > 2) ? $dec : 2;
603 # undo discount formatting
604 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
607 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
609 # round linetotal to 2 decimal places
610 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
611 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
613 if ($form->{taxincluded}) {
614 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
615 $form->{"sellprice_$i"} =
616 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
618 $taxamount = $linetotal * $taxrate;
621 $netamount += $linetotal;
623 if ($taxamount != 0) {
625 $form->{amount}{ $form->{id} }{$_} +=
626 $taxamount * $form->{"${_}_rate"} / $taxrate
627 } split(/ /, $form->{"taxaccounts_$i"});
630 # add amount to income, $form->{amount}{trans_id}{accno}
631 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
633 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
634 $linetotal = $form->round_amount($linetotal, 2);
636 # this is the difference from the inventory
637 $invoicediff += ($amount - $linetotal);
639 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
642 $lastincomeaccno = $form->{"income_accno_$i"};
644 # adjust and round sellprice
645 $form->{"sellprice_$i"} =
646 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
649 next if $payments_only;
651 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
653 if ($form->{"assembly_$i"}) {
654 # record assembly item as allocated
655 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
658 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
662 # get pricegroup_id and save it
663 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
666 # save detail record in invoice table
668 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
669 sellprice, fxsellprice, discount, allocated, assemblyitem,
670 unit, deliverydate, project_id, serialnumber, pricegroup_id,
671 ordnumber, transdate, cusordnumber, base_qty, subtotal,
672 marge_percent, marge_total, lastcost,
673 price_factor_id, price_factor, marge_price_factor)
674 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
675 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
677 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
678 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
679 $form->{"sellprice_$i"}, $fxsellprice,
680 $form->{"discount_$i"}, $allocated, 'f',
681 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
682 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
683 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
684 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
685 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
686 $form->{"lastcost_$i"},
687 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
688 conv_i($form->{"marge_price_factor_$i"}));
689 do_query($form, $dbh, $query, @values);
691 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
693 qq|INSERT INTO licenseinvoice (trans_id, license_id)
694 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
695 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
696 do_query($form, $dbh, $query, @values);
701 $form->{datepaid} = $form->{invdate};
703 # total payments, don't move we need it here
704 for my $i (1 .. $form->{paidaccounts}) {
705 if ($form->{type} eq "credit_note") {
706 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
708 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
710 $form->{paid} += $form->{"paid_$i"};
711 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
714 my ($tax, $diff) = (0, 0);
716 $netamount = $form->round_amount($netamount, 2);
718 # figure out rounding errors for total amount vs netamount + taxes
719 if ($form->{taxincluded}) {
721 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
722 $diff += $amount - $netamount * $form->{exchangerate};
723 $netamount = $amount;
725 foreach my $item (split(/ /, $form->{taxaccounts})) {
726 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
727 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
728 $tax += $form->{amount}{ $form->{id} }{$item};
729 $netamount -= $form->{amount}{ $form->{id} }{$item};
732 $invoicediff += $diff;
733 ######## this only applies to tax included
734 if ($lastincomeaccno) {
735 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
739 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
740 $diff = $amount - $netamount * $form->{exchangerate};
741 $netamount = $amount;
742 foreach my $item (split(/ /, $form->{taxaccounts})) {
743 $form->{amount}{ $form->{id} }{$item} =
744 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
747 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
750 $amount - $form->{amount}{ $form->{id} }{$item} *
751 $form->{exchangerate};
752 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
753 $tax += $form->{amount}{ $form->{id} }{$item};
757 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
759 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
762 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
764 # update exchangerate
765 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
766 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
767 $form->{exchangerate}, 0);
770 $project_id = conv_i($form->{"globalproject_id"});
772 foreach my $trans_id (keys %{ $form->{amount} }) {
773 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
774 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
776 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
778 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
780 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
781 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
782 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
783 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
784 do_query($form, $dbh, $query, @values);
785 $form->{amount}{$trans_id}{$accno} = 0;
789 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
790 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
792 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
794 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
795 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
796 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
797 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
798 do_query($form, $dbh, $query, @values);
803 # deduct payment differences from diff
804 for my $i (1 .. $form->{paidaccounts}) {
805 if ($form->{"paid_$i"} != 0) {
807 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
808 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
812 # record payments and offsetting AR
813 if (!$form->{storno}) {
814 for my $i (1 .. $form->{paidaccounts}) {
816 next if ($form->{"paid_$i"} == 0);
818 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
819 $form->{"datepaid_$i"} = $form->{invdate}
820 unless ($form->{"datepaid_$i"});
821 $form->{datepaid} = $form->{"datepaid_$i"};
825 if ($form->{currency} eq $defaultcurrency) {
826 $form->{"exchangerate_$i"} = 1;
828 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
829 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
833 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
835 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
837 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
838 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
839 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
840 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
841 do_query($form, $dbh, $query, @values);
845 $form->{"paid_$i"} *= -1;
848 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
850 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
851 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
852 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
853 do_query($form, $dbh, $query, @values);
855 # exchangerate difference
856 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
857 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
861 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
862 $form->{"exchangerate_$i"};
864 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
867 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
873 # update exchange rate
874 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
875 $form->update_exchangerate($dbh, $form->{currency},
876 $form->{"datepaid_$i"},
877 $form->{"exchangerate_$i"}, 0);
881 } else { # if (!$form->{storno})
882 $form->{marge_total} *= -1;
885 if ($payments_only) {
886 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
887 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
889 if (!$provided_dbh) {
894 $main::lxdebug->leave_sub();
898 # record exchange rate differences and gains/losses
899 foreach my $accno (keys %{ $form->{fx} }) {
900 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
902 ($form->{fx}{$accno}{$transdate} =
903 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
908 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
909 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
910 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
911 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
912 do_query($form, $dbh, $query, @values);
917 $amount = $netamount + $tax;
920 $query = qq|UPDATE ar set
921 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
922 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
923 amount = ?, netamount = ?, paid = ?, datepaid = ?,
924 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
925 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
926 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
927 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
928 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
929 cp_id = ?, marge_total = ?, marge_percent = ?,
930 globalproject_id = ?, delivery_customer_id = ?,
931 transaction_description = ?, delivery_vendor_id = ?
933 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
934 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
935 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
936 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
937 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
938 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
939 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
940 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
941 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
942 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
943 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
944 conv_i($form->{"id"}));
945 do_query($form, $dbh, $query, @values);
947 if($form->{"formname"} eq "credit_note") {
948 for my $i (1 .. $form->{rowcount}) {
949 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
950 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
951 do_query($form, $dbh, $query, @values);
955 if ($form->{storno}) {
958 paid = paid + amount,
960 intnotes = ? || intnotes
962 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
963 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
967 $form->{name} = $form->{customer};
968 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
970 if (!$form->{shipto_id}) {
971 $form->add_shipto($dbh, $form->{id}, "AR");
974 # save printed, emailed and queued
975 $form->save_status($dbh);
977 Common::webdav_folder($form) if ($main::webdav);
979 # Link this record to the records it was created from.
980 RecordLinks->create_links('dbh' => $dbh,
982 'from_table' => 'oe',
983 'from_ids' => $form->{convert_from_oe_ids},
985 'to_id' => $form->{id},
987 delete $form->{convert_from_oe_ids};
989 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
991 if (scalar @convert_from_do_ids) {
992 DO->close_orders('dbh' => $dbh,
993 'ids' => \@convert_from_do_ids);
995 RecordLinks->create_links('dbh' => $dbh,
997 'from_table' => 'delivery_orders',
998 'from_ids' => \@convert_from_do_ids,
1000 'to_id' => $form->{id},
1003 delete $form->{convert_from_do_ids};
1005 ARAP->close_orders_if_billed('dbh' => $dbh,
1006 'arap_id' => $form->{id},
1010 if (!$provided_dbh) {
1015 $main::lxdebug->leave_sub();
1020 sub _delete_payments {
1021 $main::lxdebug->enter_sub();
1023 my ($self, $form, $dbh) = @_;
1027 # Delete old payment entries from acc_trans.
1031 WHERE (trans_id = ?) AND fx_transaction
1037 LEFT JOIN chart c ON (at.chart_id = c.id)
1038 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1039 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1044 LEFT JOIN chart c ON (at.chart_id = c.id)
1045 WHERE (trans_id = ?)
1046 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1049 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1052 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1053 do_query($form, $dbh, $query);
1056 $main::lxdebug->leave_sub();
1060 $main::lxdebug->enter_sub();
1062 my ($self, $myconfig, $form, $locale) = @_;
1064 # connect to database, turn off autocommit
1065 my $dbh = $form->dbconnect_noauto($myconfig);
1067 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1069 $old_form = save_form();
1071 # Delete all entries in acc_trans from prior payments.
1072 $self->_delete_payments($form, $dbh);
1074 # Save the new payments the user made before cleaning up $form.
1075 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1077 # Clean up $form so that old content won't tamper the results.
1078 %keep_vars = map { $_, 1 } qw(login password id);
1079 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1081 # Retrieve the invoice from the database.
1082 $self->retrieve_invoice($myconfig, $form);
1084 # Set up the content of $form in the way that IS::post_invoice() expects.
1085 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1087 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1088 $item = $form->{invoice_details}->[$row - 1];
1090 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1092 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1095 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1097 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1099 # Restore the payment options from the user input.
1100 map { $form->{$_} = $payments{$_} } keys %payments;
1102 # Get the AR accno (which is normally done by Form::create_links()).
1106 LEFT JOIN chart c ON (at.chart_id = c.id)
1107 WHERE (trans_id = ?)
1108 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1112 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1114 # Post the new payments.
1115 $self->post_invoice($myconfig, $form, $dbh, 1);
1117 restore_form($old_form);
1119 my $rc = $dbh->commit();
1122 $main::lxdebug->leave_sub();
1127 sub process_assembly {
1128 $main::lxdebug->enter_sub();
1130 my ($dbh, $form, $id, $totalqty) = @_;
1133 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1134 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1136 JOIN parts p ON (a.parts_id = p.id)
1138 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1140 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1144 $ref->{inventory_accno_id} *= 1;
1145 $ref->{expense_accno_id} *= 1;
1147 # multiply by number of assemblies
1148 $ref->{qty} *= $totalqty;
1150 if ($ref->{assembly}) {
1151 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1154 if ($ref->{inventory_accno_id}) {
1155 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1159 # save detail record for individual assembly item in invoice table
1161 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1162 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1163 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1164 do_query($form, $dbh, $query, @values);
1170 $main::lxdebug->leave_sub();
1174 $main::lxdebug->enter_sub();
1176 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1177 $form->{taxzone_id} *=1;
1178 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1179 my $taxzone_id = $form->{"taxzone_id"} * 1;
1181 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1182 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1183 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1184 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1185 FROM invoice i, parts p
1186 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1187 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1188 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1189 WHERE (i.parts_id = p.id)
1190 AND (i.parts_id = ?)
1191 AND ((i.base_qty + i.allocated) < 0)
1193 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1198 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1199 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1203 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1205 # total expenses and inventory
1206 # sellprice is the cost of the item
1207 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1210 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1212 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1213 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1214 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1216 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1217 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1223 last if (($totalqty -= $qty) <= 0);
1228 $main::lxdebug->leave_sub();
1233 sub reverse_invoice {
1234 $main::lxdebug->enter_sub();
1236 my ($dbh, $form) = @_;
1238 # reverse inventory items
1240 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1242 JOIN parts p ON (i.parts_id = p.id)
1243 WHERE i.trans_id = ?|;
1244 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1246 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1248 if ($ref->{inventory_accno_id}) {
1249 # de-allocated purchases
1251 qq|SELECT i.id, i.trans_id, i.allocated
1253 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1254 ORDER BY i.trans_id DESC|;
1255 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1257 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1259 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1260 $qty = $inhref->{allocated};
1264 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1266 last if (($ref->{qty} -= $qty) <= 0);
1275 @values = (conv_i($form->{id}));
1276 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1277 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1279 if ($form->{lizenzen}) {
1281 qq|DELETE FROM licenseinvoice
1282 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1283 do_query($form, $dbh, $query, @values);
1286 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1288 $main::lxdebug->leave_sub();
1291 sub delete_invoice {
1292 $main::lxdebug->enter_sub();
1294 my ($self, $myconfig, $form, $spool) = @_;
1296 # connect to database
1297 my $dbh = $form->dbconnect_noauto($myconfig);
1299 &reverse_invoice($dbh, $form);
1301 my @values = (conv_i($form->{id}));
1304 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1306 # delete spool files
1307 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1309 # delete status entries
1310 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1312 my $rc = $dbh->commit;
1316 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1319 $main::lxdebug->leave_sub();
1324 sub retrieve_invoice {
1325 $main::lxdebug->enter_sub();
1327 my ($self, $myconfig, $form) = @_;
1329 # connect to database
1330 my $dbh = $form->dbconnect_noauto($myconfig);
1332 my ($sth, $ref, $query);
1334 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1338 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1339 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1340 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1341 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1342 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1343 d.curr AS currencies
1347 $ref = selectfirst_hashref_query($form, $dbh, $query);
1348 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1351 my $id = conv_i($form->{id});
1356 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1357 a.orddate, a.quodate, a.globalproject_id,
1358 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1359 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1360 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1361 a.employee_id, a.salesman_id, a.payment_id,
1362 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1363 a.transaction_description,
1364 a.marge_total, a.marge_percent,
1367 LEFT JOIN employee e ON (e.id = a.employee_id)
1369 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1370 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1373 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1376 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1377 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1379 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1381 foreach my $vc (qw(customer vendor)) {
1382 next if !$form->{"delivery_${vc}_id"};
1383 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1386 # get printed, emailed
1387 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1388 $sth = prepare_execute_query($form, $dbh, $query, $id);
1390 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1391 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1392 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1393 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1396 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1398 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1399 : $form->{invdate} ? $dbh->quote($form->{invdate})
1403 my $taxzone_id = $form->{taxzone_id} *= 1;
1404 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1406 # retrieve individual items
1409 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1410 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1411 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1413 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1414 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1415 i.price_factor_id, i.price_factor, i.marge_price_factor,
1416 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1417 pr.projectnumber, pg.partsgroup, prg.pricegroup
1420 LEFT JOIN parts p ON (i.parts_id = p.id)
1421 LEFT JOIN project pr ON (i.project_id = pr.id)
1422 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1423 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1425 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1426 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1427 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1429 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1431 $sth = prepare_execute_query($form, $dbh, $query, $id);
1433 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1434 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1435 delete($ref->{"part_inventory_accno_id"});
1437 foreach my $type (qw(inventory income expense)) {
1438 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1439 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1440 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1444 # get tax rates and description
1445 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1447 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1448 LEFT JOIN chart c ON (c.id = t.chart_id)
1450 (SELECT tk.tax_id FROM taxkeys tk
1451 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1452 AND startdate <= date($transdate)
1453 ORDER BY startdate DESC LIMIT 1)
1455 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1456 $ref->{taxaccounts} = "";
1458 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1460 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1464 $ref->{taxaccounts} .= "$ptr->{accno} ";
1466 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1467 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1468 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1469 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1470 $form->{taxaccounts} .= "$ptr->{accno} ";
1475 if ($form->{lizenzen}) {
1476 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1477 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1478 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1481 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1483 chop $ref->{taxaccounts};
1484 push @{ $form->{invoice_details} }, $ref;
1489 Common::webdav_folder($form) if ($main::webdav);
1492 my $rc = $dbh->commit;
1495 $main::lxdebug->leave_sub();
1501 $main::lxdebug->enter_sub();
1503 my ($self, $myconfig, $form) = @_;
1505 # connect to database
1506 my $dbh = $form->dbconnect($myconfig);
1508 my $dateformat = $myconfig->{dateformat};
1509 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1511 my (@values, $duedate, $ref, $query);
1513 if ($form->{invdate}) {
1514 $duedate = "to_date(?, '$dateformat')";
1515 push @values, $form->{invdate};
1517 $duedate = "current_date";
1520 my $cid = conv_i($form->{customer_id});
1523 if ($form->{payment_id}) {
1524 $payment_id = "(pt.id = ?) OR";
1525 push @values, conv_i($form->{payment_id});
1531 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1532 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1533 c.street, c.zipcode, c.city, c.country,
1534 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1535 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1536 b.discount AS tradediscount, b.description AS business
1538 LEFT JOIN business b ON (b.id = c.business_id)
1539 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1542 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1543 map { $form->{$_} = $ref->{$_} } keys %$ref;
1546 qq|SELECT sum(amount - paid) AS dunning_amount
1548 WHERE (paid < amount)
1549 AND (customer_id = ?)
1550 AND (dunning_config_id IS NOT NULL)|;
1551 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1552 map { $form->{$_} = $ref->{$_} } keys %$ref;
1555 qq|SELECT dnn.dunning_description AS max_dunning_level
1556 FROM dunning_config dnn
1557 WHERE id IN (SELECT dunning_config_id
1559 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1560 ORDER BY dunning_level DESC LIMIT 1|;
1561 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1562 map { $form->{$_} = $ref->{$_} } keys %$ref;
1564 $form->{creditremaining} = $form->{creditlimit};
1565 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1566 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1567 $form->{creditremaining} -= $value;
1571 (SELECT e.buy FROM exchangerate e
1572 WHERE e.curr = o.curr
1573 AND e.transdate = o.transdate)
1575 WHERE o.customer_id = ?
1576 AND o.quotation = '0'
1577 AND o.closed = '0'|;
1578 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1580 while (my ($amount, $exch) = $sth->fetchrow_array) {
1581 $exch = 1 unless $exch;
1582 $form->{creditremaining} -= $amount * $exch;
1586 # get shipto if we did not converted an order or invoice
1587 if (!$form->{shipto}) {
1588 map { delete $form->{$_} }
1589 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1590 shiptostreet shiptozipcode shiptocity shiptocountry
1591 shiptocontact shiptophone shiptofax shiptoemail);
1593 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1594 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1596 map { $form->{$_} = $ref->{$_} } keys %$ref;
1599 # setup last accounts used for this customer
1600 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1602 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1604 JOIN acc_trans ac ON (ac.chart_id = c.id)
1605 JOIN ar a ON (a.id = ac.trans_id)
1606 WHERE a.customer_id = ?
1607 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1608 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1609 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1612 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1613 if ($ref->{category} eq 'I') {
1615 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1617 if ($form->{initial_transdate}) {
1619 qq|SELECT tk.tax_id, t.rate
1621 LEFT JOIN tax t ON tk.tax_id = t.id
1622 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1623 ORDER BY tk.startdate DESC
1625 my ($tax_id, $rate) =
1626 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1627 $form->{initial_transdate});
1628 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1631 if ($ref->{category} eq 'A') {
1632 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1636 $form->{rowcount} = $i if ($i && !$form->{type});
1641 $main::lxdebug->leave_sub();
1645 $main::lxdebug->enter_sub();
1647 my ($self, $myconfig, $form) = @_;
1649 # connect to database
1650 my $dbh = $form->dbconnect($myconfig);
1652 my $i = $form->{rowcount};
1654 my $where = qq|NOT p.obsolete = '1'|;
1657 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1658 my ($table, $field) = split m/\./, $column;
1659 next if !$form->{"${field}_${i}"};
1660 $where .= qq| AND lower(${column}) ILIKE ?|;
1661 push @values, '%' . $form->{"${field}_${i}"} . '%';
1664 if ($form->{"description_$i"}) {
1665 $where .= qq| ORDER BY p.description|;
1667 $where .= qq| ORDER BY p.partnumber|;
1671 if ($form->{type} eq "invoice") {
1673 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1674 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1678 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1682 my $taxzone_id = $form->{taxzone_id} * 1;
1683 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1687 p.id, p.partnumber, p.description, p.sellprice,
1688 p.listprice, p.inventory_accno_id, p.lastcost,
1690 c1.accno AS inventory_accno,
1691 c1.new_chart_id AS inventory_new_chart,
1692 date($transdate) - c1.valid_from AS inventory_valid,
1694 c2.accno AS income_accno,
1695 c2.new_chart_id AS income_new_chart,
1696 date($transdate) - c2.valid_from AS income_valid,
1698 c3.accno AS expense_accno,
1699 c3.new_chart_id AS expense_new_chart,
1700 date($transdate) - c3.valid_from AS expense_valid,
1702 p.unit, p.assembly, p.bin, p.onhand,
1703 p.notes AS partnotes, p.notes AS longdescription,
1704 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1707 pfac.factor AS price_factor,
1712 LEFT JOIN chart c1 ON
1713 ((SELECT inventory_accno_id
1714 FROM buchungsgruppen
1715 WHERE id = p.buchungsgruppen_id) = c1.id)
1716 LEFT JOIN chart c2 ON
1717 ((SELECT income_accno_id_${taxzone_id}
1718 FROM buchungsgruppen
1719 WHERE id = p.buchungsgruppen_id) = c2.id)
1720 LEFT JOIN chart c3 ON
1721 ((SELECT expense_accno_id_${taxzone_id}
1722 FROM buchungsgruppen
1723 WHERE id = p.buchungsgruppen_id) = c3.id)
1724 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1725 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1727 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1729 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1731 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1732 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1733 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1734 if (!$ref->{inventory_accno_id}) {
1735 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1737 delete($ref->{inventory_accno_id});
1739 foreach my $type (qw(inventory income expense)) {
1740 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1742 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1745 ($ref->{"${type}_accno"},
1746 $ref->{"${type}_new_chart"},
1747 $ref->{"${type}_valid"})
1748 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1752 if ($form->{payment_id} eq "") {
1753 $form->{payment_id} = $form->{part_payment_id};
1756 # get tax rates and description
1757 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1759 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1761 LEFT JOIN chart c ON (c.id = t.chart_id)
1765 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1767 ORDER BY startdate DESC
1770 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1771 $stw = $dbh->prepare($query);
1772 $stw->execute(@values) || $form->dberror($query);
1774 $ref->{taxaccounts} = "";
1776 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1778 # if ($customertax{$ref->{accno}}) {
1779 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1783 $ref->{taxaccounts} .= "$ptr->{accno} ";
1785 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1786 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1787 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1788 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1789 $form->{taxaccounts} .= "$ptr->{accno} ";
1795 chop $ref->{taxaccounts};
1796 if ($form->{language_id}) {
1798 qq|SELECT tr.translation, tr.longdescription
1800 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1801 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1802 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1803 if ($translation ne "") {
1804 $ref->{description} = $translation;
1805 $ref->{longdescription} = $longdescription;
1809 qq|SELECT tr.translation, tr.longdescription
1811 WHERE tr.language_id IN
1814 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1817 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1818 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1819 if ($translation ne "") {
1820 $ref->{description} = $translation;
1821 $ref->{longdescription} = $longdescription;
1826 $ref->{onhand} *= 1;
1828 push @{ $form->{item_list} }, $ref;
1830 if ($form->{lizenzen}) {
1831 if ($ref->{inventory_accno} > 0) {
1835 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1836 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1837 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1838 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1847 $main::lxdebug->leave_sub();
1850 ##########################
1851 # get pricegroups from database
1852 # build up selected pricegroup
1853 # if an exchange rate - change price
1856 sub get_pricegroups_for_parts {
1858 $main::lxdebug->enter_sub();
1860 my ($self, $myconfig, $form) = @_;
1862 my $dbh = $form->dbconnect($myconfig);
1864 $form->{"PRICES"} = {};
1868 my $all_units = AM->retrieve_units($myconfig, $form);
1869 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1870 $form->{"PRICES"}{$i} = [];
1872 $id = $form->{"id_$i"};
1874 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1876 $id = $form->{"new_id_$i"};
1879 ($price, $selectedpricegroup_id) = split(/--/,
1880 $form->{"sellprice_pg_$i"});
1882 $pricegroup_old = $form->{"pricegroup_old_$i"};
1883 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1884 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1886 $price_new = $form->{"price_new_$i"};
1887 $price_old = $form->{"price_old_$i"};
1889 if (!$form->{"unit_old_$i"}) {
1890 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1891 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1892 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1893 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1896 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1897 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1898 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1900 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1901 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1902 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1903 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1904 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1905 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1906 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1911 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1912 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1913 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1914 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1915 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1919 if (!$form->{"basefactor_$i"}) {
1920 $form->{"basefactor_$i"} = 1;
1926 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1927 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1937 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1939 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1940 'selected' AS selected
1943 ORDER BY pricegroup|;
1944 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1945 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1947 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1949 $pkr->{selected} = '';
1951 # if there is an exchange rate change price
1952 if (($form->{exchangerate} * 1) != 0) {
1954 $pkr->{price} /= $form->{exchangerate};
1957 $pkr->{price} *= $form->{"basefactor_$i"};
1959 $pkr->{price} *= $basefactor;
1961 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1963 if ($selectedpricegroup_id eq undef) {
1964 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1966 $pkr->{selected} = ' selected';
1968 # no customer pricesgroup set
1969 if ($pkr->{price} == $pkr->{default_sellprice}) {
1971 $pkr->{price} = $form->{"sellprice_$i"};
1975 $form->{"sellprice_$i"} = $pkr->{price};
1978 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1979 $pkr->{price} = $form->{"sellprice_$i"};
1980 $pkr->{selected} = ' selected';
1984 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1985 if ($selectedpricegroup_id ne $pricegroup_old) {
1986 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1987 $pkr->{selected} = ' selected';
1989 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1990 if ($pkr->{pricegroup_id} == 0) {
1991 $pkr->{price} = $form->{"sellprice_$i"};
1992 $pkr->{selected} = ' selected';
1994 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1995 $pkr->{selected} = ' selected';
1996 if ( ($pkr->{pricegroup_id} == 0)
1997 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1998 # $pkr->{price} = $form->{"sellprice_$i"};
2000 $pkr->{price} = $form->{"sellprice_$i"};
2004 push @{ $form->{PRICES}{$i} }, $pkr;
2007 $form->{"basefactor_$i"} *= $basefactor;
2016 $main::lxdebug->leave_sub();
2020 $main::lxdebug->enter_sub();
2022 my ($self, $myconfig, $form, $table) = @_;
2024 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2026 # make sure there's no funny stuff in $table
2027 # ToDO: die when this happens and throw an error
2028 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2030 my $dbh = $form->dbconnect($myconfig);
2032 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2033 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2037 $main::lxdebug->leave_sub();
2043 $main::lxdebug->enter_sub();
2045 my ($self, $myconfig, $form, $table, $id) = @_;
2047 $main::lxdebug->leave_sub() and return 0 unless ($id);
2049 # make sure there's no funny stuff in $table
2050 # ToDO: die when this happens and throw an error
2051 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2053 my $dbh = $form->dbconnect($myconfig);
2055 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2056 my ($result) = selectrow_query($form, $dbh, $query, $id);
2060 $main::lxdebug->leave_sub();