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 #======================================================================
39 use List::Util qw(max);
52 $main::lxdebug->enter_sub();
54 my ($self, $myconfig, $form, $locale) = @_;
56 $form->{duedate} ||= $form->{invdate};
59 my $dbh = $form->dbconnect($myconfig);
62 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
63 ($form->{terms}) = selectrow_query($form, $dbh, $query);
65 my (@project_ids, %projectnumbers);
67 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
69 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
72 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
73 $price_factors{$pfac->{id}} = $pfac;
75 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
78 # sort items by partsgroup
79 for my $i (1 .. $form->{rowcount}) {
81 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
82 # $partsgroup = $form->{"partsgroup_$i"};
84 # push @partsgroup, [$i, $partsgroup];
85 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
89 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
90 join(", ", map({ "?" } @project_ids)) . ")";
91 $sth = $dbh->prepare($query);
92 $sth->execute(@project_ids) ||
93 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
94 while (my $ref = $sth->fetchrow_hashref()) {
95 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $form->{"globalprojectnumber"} =
101 $projectnumbers{$form->{"globalproject_id"}};
108 my %oid = ('Pg' => 'oid',
109 'Oracle' => 'rowid');
111 # sort items by partsgroup
112 for $i (1 .. $form->{rowcount}) {
114 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
115 $partsgroup = $form->{"partsgroup_$i"};
117 push @partsgroup, [$i, $partsgroup];
130 my $nodiscount_subtotal = 0;
131 my $discount_subtotal = 0;
133 my $subtotal_header = 0;
136 $form->{discount} = [];
138 IC->prepare_parts_for_printing();
141 qw(runningnumber number description longdescription qty ship unit bin
142 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
143 partnotes serialnumber reqdate sellprice listprice netprice
144 discount p_discount discount_sub nodiscount_sub
145 linetotal nodiscount_linetotal tax_rate projectnumber
146 price_factor price_factor_name);
149 qw(taxbase tax taxdescription taxrate taxnumber);
151 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
154 if ($item->[1] ne $sameitem) {
155 push(@{ $form->{description} }, qq|$item->[1]|);
156 $sameitem = $item->[1];
158 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
161 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
163 if ($form->{"id_$i"} != 0) {
165 # add number, description and qty to $form->{number},
166 if ($form->{"subtotal_$i"} && !$subtotal_header) {
167 $subtotal_header = $i;
168 $position = int($position);
171 } elsif ($subtotal_header) {
173 $position = int($position);
174 $position = $position.".".$subposition;
176 $position = int($position);
180 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
182 push @{ $form->{runningnumber} }, $position;
183 push @{ $form->{number} }, $form->{"partnumber_$i"};
184 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
185 push @{ $form->{bin} }, $form->{"bin_$i"};
186 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
187 push @{ $form->{description} }, $form->{"description_$i"};
188 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
189 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
190 push @{ $form->{unit} }, $form->{"unit_$i"};
191 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
192 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
193 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
194 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
195 push @{ $form->{invnumber} }, $form->{"invnumber"};
196 push @{ $form->{invdate} }, $form->{"invdate"};
197 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
198 push @{ $form->{price_factor_name} }, $price_factor->{description};
200 if ($form->{lizenzen}) {
201 if ($form->{"licensenumber_$i"}) {
202 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
203 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
204 push(@{ $form->{licensenumber} }, $licensenumber);
205 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
208 push(@{ $form->{licensenumber} }, "");
209 push(@{ $form->{validuntil} }, "");
214 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
216 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
217 my ($dec) = ($sellprice =~ /\.(\d+)/);
218 my $decimalplaces = max 2, length($dec);
220 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
221 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
222 my $linetotal = $form->round_amount($linetotal_exact, 2);
223 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
225 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
226 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
228 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
230 $linetotal = ($linetotal != 0) ? $linetotal : '';
232 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
233 push @{ $form->{p_discount} }, $form->{"discount_$i"};
235 $form->{total} += $linetotal;
236 $form->{nodiscount_total} += $nodiscount_linetotal;
237 $form->{discount_total} += $discount;
239 if ($subtotal_header) {
240 $discount_subtotal += $linetotal;
241 $nodiscount_subtotal += $nodiscount_linetotal;
244 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
245 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
246 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
248 $discount_subtotal = 0;
249 $nodiscount_subtotal = 0;
250 $subtotal_header = 0;
253 push @{ $form->{discount_sub} }, "";
254 push @{ $form->{nodiscount_sub} }, "";
257 if (!$form->{"discount_$i"}) {
258 $nodiscount += $linetotal;
261 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
262 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
264 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
266 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
270 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
272 if ($form->{taxincluded}) {
275 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
276 $taxbase = $linetotal - $taxamount;
278 $taxamount = $linetotal * $taxrate;
279 $taxbase = $linetotal;
282 if ($form->round_amount($taxrate, 7) == 0) {
283 if ($form->{taxincluded}) {
284 foreach my $accno (@taxaccounts) {
285 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
287 $taxaccounts{$accno} += $taxamount;
288 $taxdiff += $taxamount;
290 $taxbase{$accno} += $taxbase;
292 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
294 foreach my $accno (@taxaccounts) {
295 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
296 $taxbase{$accno} += $taxbase;
300 foreach my $accno (@taxaccounts) {
301 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
302 $taxbase{$accno} += $taxbase;
305 my $tax_rate = $taxrate * 100;
306 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
307 if ($form->{"assembly_$i"}) {
310 # get parts and push them onto the stack
312 if ($form->{groupitems}) {
314 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
316 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
320 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
322 JOIN parts p ON (a.parts_id = p.id)
323 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
324 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
325 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
327 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
328 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
329 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
330 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
331 push(@{ $form->{description} }, $sameitem);
334 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
336 push(@{ $form->{description} },
337 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
339 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
340 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
348 foreach my $item (sort keys %taxaccounts) {
349 push(@{ $form->{taxbase} },
350 $form->format_amount($myconfig, $taxbase{$item}, 2));
352 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
354 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
355 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
356 push(@{ $form->{taxrate} },
357 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
358 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
361 for my $i (1 .. $form->{paidaccounts}) {
362 if ($form->{"paid_$i"}) {
363 push(@{ $form->{payment} }, $form->{"paid_$i"});
364 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
365 push(@{ $form->{paymentaccount} }, $description);
366 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
367 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
369 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
372 if($form->{taxincluded}) {
373 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
376 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
379 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
380 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
381 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
382 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
384 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
385 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
387 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
388 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
390 $form->set_payment_options($myconfig, $form->{invdate});
392 $form->{username} = $myconfig->{name};
396 $main::lxdebug->leave_sub();
399 sub project_description {
400 $main::lxdebug->enter_sub();
402 my ($self, $dbh, $id) = @_;
403 my $form = \%main::form;
405 my $query = qq|SELECT description FROM project WHERE id = ?|;
406 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
408 $main::lxdebug->leave_sub();
413 sub customer_details {
414 $main::lxdebug->enter_sub();
416 my ($self, $myconfig, $form, @wanted_vars) = @_;
418 # connect to database
419 my $dbh = $form->dbconnect($myconfig);
421 # get contact id, set it if nessessary
424 my @values = (conv_i($form->{customer_id}));
427 if ($form->{cp_id}) {
428 $where = qq| AND (cp.cp_id = ?) |;
429 push(@values, conv_i($form->{cp_id}));
432 # get rest for the customer
434 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
435 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
437 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
438 WHERE (ct.id = ?) $where
441 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
443 # remove id and taxincluded before copy back
444 delete @$ref{qw(id taxincluded)};
446 @wanted_vars = grep({ $_ } @wanted_vars);
447 if (scalar(@wanted_vars) > 0) {
449 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
450 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
453 map { $form->{$_} = $ref->{$_} } keys %$ref;
455 if ($form->{delivery_customer_id}) {
457 qq|SELECT *, notes as customernotes
461 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
463 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
466 if ($form->{delivery_vendor_id}) {
468 qq|SELECT *, notes as customernotes
472 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
474 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
477 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
479 'trans_id' => $form->{customer_id});
480 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
484 $main::lxdebug->leave_sub();
488 $main::lxdebug->enter_sub();
490 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
492 # connect to database, turn off autocommit
493 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
495 my ($query, $sth, $null, $project_id, @values);
496 my $exchangerate = 0;
498 if (!$form->{employee_id}) {
499 $form->get_employee($dbh);
502 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
504 ($null, $form->{department_id}) = split(/--/, $form->{department});
506 my $all_units = AM->retrieve_units($myconfig, $form);
508 if (!$payments_only) {
510 &reverse_invoice($dbh, $form);
513 $query = qq|SELECT nextval('glid')|;
514 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
516 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
517 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
519 if (!$form->{invnumber}) {
521 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
522 "cnnumber" : "invnumber", $dbh);
527 my ($netamount, $invoicediff) = (0, 0);
528 my ($amount, $linetotal, $lastincomeaccno);
530 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
531 my $defaultcurrency = (split m/:/, $currencies)[0];
533 if ($form->{currency} eq $defaultcurrency) {
534 $form->{exchangerate} = 1;
536 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
539 $form->{exchangerate} =
542 : $form->parse_amount($myconfig, $form->{exchangerate});
544 $form->{expense_inventory} = "";
548 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
549 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
552 foreach my $i (1 .. $form->{rowcount}) {
553 if ($form->{type} eq "credit_note") {
554 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
555 $form->{shipped} = 1;
557 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
562 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
563 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
564 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
566 if ($form->{storno}) {
567 $form->{"qty_$i"} *= -1;
570 if ($form->{"id_$i"}) {
573 if (defined($baseunits{$form->{"id_$i"}})) {
574 $item_unit = $baseunits{$form->{"id_$i"}};
577 $query = qq|SELECT unit FROM parts WHERE id = ?|;
578 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
579 $baseunits{$form->{"id_$i"}} = $item_unit;
582 if (defined($all_units->{$item_unit}->{factor})
583 && ($all_units->{$item_unit}->{factor} ne '')
584 && ($all_units->{$item_unit}->{factor} != 0)) {
585 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
589 $baseqty = $form->{"qty_$i"} * $basefactor;
591 my ($allocated, $taxrate) = (0, 0);
595 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
597 # keep entered selling price
599 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
601 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
603 my $decimalplaces = ($dec > 2) ? $dec : 2;
605 # undo discount formatting
606 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
609 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
611 # round linetotal to 2 decimal places
612 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
613 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
615 if ($form->{taxincluded}) {
616 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
617 $form->{"sellprice_$i"} =
618 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
620 $taxamount = $linetotal * $taxrate;
623 $netamount += $linetotal;
625 if ($taxamount != 0) {
627 $form->{amount}{ $form->{id} }{$_} +=
628 $taxamount * $form->{"${_}_rate"} / $taxrate
629 } split(/ /, $form->{"taxaccounts_$i"});
632 # add amount to income, $form->{amount}{trans_id}{accno}
633 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
635 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
636 $linetotal = $form->round_amount($linetotal, 2);
638 # this is the difference from the inventory
639 $invoicediff += ($amount - $linetotal);
641 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
644 $lastincomeaccno = $form->{"income_accno_$i"};
646 # adjust and round sellprice
647 $form->{"sellprice_$i"} =
648 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
651 next if $payments_only;
653 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
655 if ($form->{"assembly_$i"}) {
656 # record assembly item as allocated
657 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
660 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
664 # get pricegroup_id and save it
665 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
668 # save detail record in invoice table
670 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
671 sellprice, fxsellprice, discount, allocated, assemblyitem,
672 unit, deliverydate, project_id, serialnumber, pricegroup_id,
673 ordnumber, transdate, cusordnumber, base_qty, subtotal,
674 marge_percent, marge_total, lastcost,
675 price_factor_id, price_factor, marge_price_factor)
676 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
677 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
679 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
680 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
681 $form->{"sellprice_$i"}, $fxsellprice,
682 $form->{"discount_$i"}, $allocated, 'f',
683 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
684 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
685 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
686 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
687 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
688 $form->{"lastcost_$i"},
689 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
690 conv_i($form->{"marge_price_factor_$i"}));
691 do_query($form, $dbh, $query, @values);
693 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
695 qq|INSERT INTO licenseinvoice (trans_id, license_id)
696 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
697 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
698 do_query($form, $dbh, $query, @values);
703 $form->{datepaid} = $form->{invdate};
705 # total payments, don't move we need it here
706 for my $i (1 .. $form->{paidaccounts}) {
707 if ($form->{type} eq "credit_note") {
708 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
710 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
712 $form->{paid} += $form->{"paid_$i"};
713 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
716 my ($tax, $diff) = (0, 0);
718 $netamount = $form->round_amount($netamount, 2);
720 # figure out rounding errors for total amount vs netamount + taxes
721 if ($form->{taxincluded}) {
723 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
724 $diff += $amount - $netamount * $form->{exchangerate};
725 $netamount = $amount;
727 foreach my $item (split(/ /, $form->{taxaccounts})) {
728 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
729 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
730 $tax += $form->{amount}{ $form->{id} }{$item};
731 $netamount -= $form->{amount}{ $form->{id} }{$item};
734 $invoicediff += $diff;
735 ######## this only applies to tax included
736 if ($lastincomeaccno) {
737 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
741 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
742 $diff = $amount - $netamount * $form->{exchangerate};
743 $netamount = $amount;
744 foreach my $item (split(/ /, $form->{taxaccounts})) {
745 $form->{amount}{ $form->{id} }{$item} =
746 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
749 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
752 $amount - $form->{amount}{ $form->{id} }{$item} *
753 $form->{exchangerate};
754 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
755 $tax += $form->{amount}{ $form->{id} }{$item};
759 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
761 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
764 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
766 # update exchangerate
767 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
768 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
769 $form->{exchangerate}, 0);
772 $project_id = conv_i($form->{"globalproject_id"});
774 foreach my $trans_id (keys %{ $form->{amount} }) {
775 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
776 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
778 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
780 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
782 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
783 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
784 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
785 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
786 do_query($form, $dbh, $query, @values);
787 $form->{amount}{$trans_id}{$accno} = 0;
791 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
792 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
794 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
796 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
797 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
798 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
799 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
800 do_query($form, $dbh, $query, @values);
805 # deduct payment differences from diff
806 for my $i (1 .. $form->{paidaccounts}) {
807 if ($form->{"paid_$i"} != 0) {
809 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
810 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
814 # record payments and offsetting AR
815 if (!$form->{storno}) {
816 for my $i (1 .. $form->{paidaccounts}) {
818 next if ($form->{"paid_$i"} == 0);
820 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
821 $form->{"datepaid_$i"} = $form->{invdate}
822 unless ($form->{"datepaid_$i"});
823 $form->{datepaid} = $form->{"datepaid_$i"};
827 if ($form->{currency} eq $defaultcurrency) {
828 $form->{"exchangerate_$i"} = 1;
830 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
831 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
835 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
837 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
839 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
840 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
841 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
842 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
843 do_query($form, $dbh, $query, @values);
847 $form->{"paid_$i"} *= -1;
850 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
851 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
852 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
853 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
854 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
855 do_query($form, $dbh, $query, @values);
857 # exchangerate difference
858 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
859 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
863 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
864 $form->{"exchangerate_$i"};
866 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
869 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
875 # update exchange rate
876 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
877 $form->update_exchangerate($dbh, $form->{currency},
878 $form->{"datepaid_$i"},
879 $form->{"exchangerate_$i"}, 0);
883 } else { # if (!$form->{storno})
884 $form->{marge_total} *= -1;
887 if ($payments_only) {
888 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
889 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
891 if (!$provided_dbh) {
896 $main::lxdebug->leave_sub();
900 # record exchange rate differences and gains/losses
901 foreach my $accno (keys %{ $form->{fx} }) {
902 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
904 ($form->{fx}{$accno}{$transdate} =
905 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
910 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
911 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
912 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
913 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
914 do_query($form, $dbh, $query, @values);
919 $amount = $netamount + $tax;
922 $query = qq|UPDATE ar set
923 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
924 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
925 amount = ?, netamount = ?, paid = ?, datepaid = ?,
926 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
927 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
928 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
929 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
930 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
931 cp_id = ?, marge_total = ?, marge_percent = ?,
932 globalproject_id = ?, delivery_customer_id = ?,
933 transaction_description = ?, delivery_vendor_id = ?
935 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
936 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
937 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
938 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
939 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
940 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
941 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
942 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
943 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
944 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
945 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
946 conv_i($form->{"id"}));
947 do_query($form, $dbh, $query, @values);
949 if($form->{"formname"} eq "credit_note") {
950 for my $i (1 .. $form->{rowcount}) {
951 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
952 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
953 do_query($form, $dbh, $query, @values);
957 if ($form->{storno}) {
960 paid = paid + amount,
962 intnotes = ? || intnotes
964 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
965 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
969 $form->{name} = $form->{customer};
970 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
972 if (!$form->{shipto_id}) {
973 $form->add_shipto($dbh, $form->{id}, "AR");
976 # save printed, emailed and queued
977 $form->save_status($dbh);
979 Common::webdav_folder($form) if ($main::webdav);
981 # Link this record to the records it was created from.
982 RecordLinks->create_links('dbh' => $dbh,
984 'from_table' => 'oe',
985 'from_ids' => $form->{convert_from_oe_ids},
987 'to_id' => $form->{id},
989 delete $form->{convert_from_oe_ids};
991 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
993 if (scalar @convert_from_do_ids) {
994 DO->close_orders('dbh' => $dbh,
995 'ids' => \@convert_from_do_ids);
997 RecordLinks->create_links('dbh' => $dbh,
999 'from_table' => 'delivery_orders',
1000 'from_ids' => \@convert_from_do_ids,
1002 'to_id' => $form->{id},
1005 delete $form->{convert_from_do_ids};
1007 ARAP->close_orders_if_billed('dbh' => $dbh,
1008 'arap_id' => $form->{id},
1012 if (!$provided_dbh) {
1017 $main::lxdebug->leave_sub();
1022 sub _delete_payments {
1023 $main::lxdebug->enter_sub();
1025 my ($self, $form, $dbh) = @_;
1029 # Delete old payment entries from acc_trans.
1033 WHERE (trans_id = ?) AND fx_transaction
1039 LEFT JOIN chart c ON (at.chart_id = c.id)
1040 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1041 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1046 LEFT JOIN chart c ON (at.chart_id = c.id)
1047 WHERE (trans_id = ?)
1048 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1051 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1054 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1055 do_query($form, $dbh, $query);
1058 $main::lxdebug->leave_sub();
1062 $main::lxdebug->enter_sub();
1064 my ($self, $myconfig, $form, $locale) = @_;
1066 # connect to database, turn off autocommit
1067 my $dbh = $form->dbconnect_noauto($myconfig);
1069 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1071 $old_form = save_form();
1073 # Delete all entries in acc_trans from prior payments.
1074 $self->_delete_payments($form, $dbh);
1076 # Save the new payments the user made before cleaning up $form.
1077 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1079 # Clean up $form so that old content won't tamper the results.
1080 %keep_vars = map { $_, 1 } qw(login password id);
1081 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1083 # Retrieve the invoice from the database.
1084 $self->retrieve_invoice($myconfig, $form);
1086 # Set up the content of $form in the way that IS::post_invoice() expects.
1087 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1089 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1090 $item = $form->{invoice_details}->[$row - 1];
1092 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1094 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1097 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1099 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1101 # Restore the payment options from the user input.
1102 map { $form->{$_} = $payments{$_} } keys %payments;
1104 # Get the AR accno (which is normally done by Form::create_links()).
1108 LEFT JOIN chart c ON (at.chart_id = c.id)
1109 WHERE (trans_id = ?)
1110 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1114 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1116 # Post the new payments.
1117 $self->post_invoice($myconfig, $form, $dbh, 1);
1119 restore_form($old_form);
1121 my $rc = $dbh->commit();
1124 $main::lxdebug->leave_sub();
1129 sub process_assembly {
1130 $main::lxdebug->enter_sub();
1132 my ($dbh, $form, $id, $totalqty) = @_;
1135 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1136 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1138 JOIN parts p ON (a.parts_id = p.id)
1140 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1142 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1146 $ref->{inventory_accno_id} *= 1;
1147 $ref->{expense_accno_id} *= 1;
1149 # multiply by number of assemblies
1150 $ref->{qty} *= $totalqty;
1152 if ($ref->{assembly}) {
1153 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1156 if ($ref->{inventory_accno_id}) {
1157 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1161 # save detail record for individual assembly item in invoice table
1163 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1164 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1165 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1166 do_query($form, $dbh, $query, @values);
1172 $main::lxdebug->leave_sub();
1176 $main::lxdebug->enter_sub();
1178 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1179 $form->{taxzone_id} *=1;
1180 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1181 my $taxzone_id = $form->{"taxzone_id"} * 1;
1183 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1184 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1185 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1186 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1187 FROM invoice i, parts p
1188 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1189 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1190 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1191 WHERE (i.parts_id = p.id)
1192 AND (i.parts_id = ?)
1193 AND ((i.base_qty + i.allocated) < 0)
1195 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1200 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1201 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1205 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1207 # total expenses and inventory
1208 # sellprice is the cost of the item
1209 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1212 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1214 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1215 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1216 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1218 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1219 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1225 last if (($totalqty -= $qty) <= 0);
1230 $main::lxdebug->leave_sub();
1235 sub reverse_invoice {
1236 $main::lxdebug->enter_sub();
1238 my ($dbh, $form) = @_;
1240 # reverse inventory items
1242 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1244 JOIN parts p ON (i.parts_id = p.id)
1245 WHERE i.trans_id = ?|;
1246 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1248 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1250 if ($ref->{inventory_accno_id}) {
1251 # de-allocated purchases
1253 qq|SELECT i.id, i.trans_id, i.allocated
1255 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1256 ORDER BY i.trans_id DESC|;
1257 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1259 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1260 my $qty = $ref->{qty};
1261 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1262 $qty = $inhref->{allocated};
1266 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1268 last if (($ref->{qty} -= $qty) <= 0);
1277 my @values = (conv_i($form->{id}));
1278 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1279 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1281 if ($form->{lizenzen}) {
1283 qq|DELETE FROM licenseinvoice
1284 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1285 do_query($form, $dbh, $query, @values);
1288 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1290 $main::lxdebug->leave_sub();
1293 sub delete_invoice {
1294 $main::lxdebug->enter_sub();
1296 my ($self, $myconfig, $form, $spool) = @_;
1298 # connect to database
1299 my $dbh = $form->dbconnect_noauto($myconfig);
1301 &reverse_invoice($dbh, $form);
1303 my @values = (conv_i($form->{id}));
1306 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1308 # delete spool files
1309 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1311 # delete status entries
1312 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1314 my $rc = $dbh->commit;
1318 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1321 $main::lxdebug->leave_sub();
1326 sub retrieve_invoice {
1327 $main::lxdebug->enter_sub();
1329 my ($self, $myconfig, $form) = @_;
1331 # connect to database
1332 my $dbh = $form->dbconnect_noauto($myconfig);
1334 my ($sth, $ref, $query);
1336 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1340 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1341 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1342 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1343 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1344 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1345 d.curr AS currencies
1349 $ref = selectfirst_hashref_query($form, $dbh, $query);
1350 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1353 my $id = conv_i($form->{id});
1358 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1359 a.orddate, a.quodate, a.globalproject_id,
1360 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1361 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1362 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1363 a.employee_id, a.salesman_id, a.payment_id,
1364 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1365 a.transaction_description,
1366 a.marge_total, a.marge_percent,
1369 LEFT JOIN employee e ON (e.id = a.employee_id)
1371 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1372 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1375 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1378 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1379 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1381 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1383 foreach my $vc (qw(customer vendor)) {
1384 next if !$form->{"delivery_${vc}_id"};
1385 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1388 # get printed, emailed
1389 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1390 $sth = prepare_execute_query($form, $dbh, $query, $id);
1392 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1393 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1394 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1395 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1398 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1400 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1401 : $form->{invdate} ? $dbh->quote($form->{invdate})
1405 my $taxzone_id = $form->{taxzone_id} *= 1;
1406 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1408 # retrieve individual items
1411 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1412 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1413 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1415 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1416 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1417 i.price_factor_id, i.price_factor, i.marge_price_factor,
1418 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1419 pr.projectnumber, pg.partsgroup, prg.pricegroup
1422 LEFT JOIN parts p ON (i.parts_id = p.id)
1423 LEFT JOIN project pr ON (i.project_id = pr.id)
1424 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1425 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1427 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1428 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1429 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1431 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1433 $sth = prepare_execute_query($form, $dbh, $query, $id);
1435 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1436 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1437 delete($ref->{"part_inventory_accno_id"});
1439 foreach my $type (qw(inventory income expense)) {
1440 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1441 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1442 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1446 # get tax rates and description
1447 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1449 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1450 LEFT JOIN chart c ON (c.id = t.chart_id)
1452 (SELECT tk.tax_id FROM taxkeys tk
1453 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1454 AND startdate <= date($transdate)
1455 ORDER BY startdate DESC LIMIT 1)
1457 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1458 $ref->{taxaccounts} = "";
1460 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1462 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1466 $ref->{taxaccounts} .= "$ptr->{accno} ";
1468 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1469 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1470 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1471 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1472 $form->{taxaccounts} .= "$ptr->{accno} ";
1477 if ($form->{lizenzen}) {
1478 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1479 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1480 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1483 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1485 chop $ref->{taxaccounts};
1486 push @{ $form->{invoice_details} }, $ref;
1491 Common::webdav_folder($form) if ($main::webdav);
1494 my $rc = $dbh->commit;
1497 $main::lxdebug->leave_sub();
1503 $main::lxdebug->enter_sub();
1505 my ($self, $myconfig, $form) = @_;
1507 # connect to database
1508 my $dbh = $form->dbconnect($myconfig);
1510 my $dateformat = $myconfig->{dateformat};
1511 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1513 my (@values, $duedate, $ref, $query);
1515 if ($form->{invdate}) {
1516 $duedate = "to_date(?, '$dateformat')";
1517 push @values, $form->{invdate};
1519 $duedate = "current_date";
1522 my $cid = conv_i($form->{customer_id});
1525 if ($form->{payment_id}) {
1526 $payment_id = "(pt.id = ?) OR";
1527 push @values, conv_i($form->{payment_id});
1533 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1534 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1535 c.street, c.zipcode, c.city, c.country,
1536 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1537 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1538 b.discount AS tradediscount, b.description AS business
1540 LEFT JOIN business b ON (b.id = c.business_id)
1541 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1544 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1545 map { $form->{$_} = $ref->{$_} } keys %$ref;
1548 qq|SELECT sum(amount - paid) AS dunning_amount
1550 WHERE (paid < amount)
1551 AND (customer_id = ?)
1552 AND (dunning_config_id IS NOT NULL)|;
1553 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1554 map { $form->{$_} = $ref->{$_} } keys %$ref;
1557 qq|SELECT dnn.dunning_description AS max_dunning_level
1558 FROM dunning_config dnn
1559 WHERE id IN (SELECT dunning_config_id
1561 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1562 ORDER BY dunning_level DESC LIMIT 1|;
1563 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1564 map { $form->{$_} = $ref->{$_} } keys %$ref;
1566 $form->{creditremaining} = $form->{creditlimit};
1567 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1568 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1569 $form->{creditremaining} -= $value;
1573 (SELECT e.buy FROM exchangerate e
1574 WHERE e.curr = o.curr
1575 AND e.transdate = o.transdate)
1577 WHERE o.customer_id = ?
1578 AND o.quotation = '0'
1579 AND o.closed = '0'|;
1580 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1582 while (my ($amount, $exch) = $sth->fetchrow_array) {
1583 $exch = 1 unless $exch;
1584 $form->{creditremaining} -= $amount * $exch;
1588 # get shipto if we did not converted an order or invoice
1589 if (!$form->{shipto}) {
1590 map { delete $form->{$_} }
1591 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1592 shiptostreet shiptozipcode shiptocity shiptocountry
1593 shiptocontact shiptophone shiptofax shiptoemail);
1595 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1596 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1598 map { $form->{$_} = $ref->{$_} } keys %$ref;
1601 # setup last accounts used for this customer
1602 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1604 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1606 JOIN acc_trans ac ON (ac.chart_id = c.id)
1607 JOIN ar a ON (a.id = ac.trans_id)
1608 WHERE a.customer_id = ?
1609 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1610 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1611 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1614 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1615 if ($ref->{category} eq 'I') {
1617 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1619 if ($form->{initial_transdate}) {
1621 qq|SELECT tk.tax_id, t.rate
1623 LEFT JOIN tax t ON tk.tax_id = t.id
1624 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1625 ORDER BY tk.startdate DESC
1627 my ($tax_id, $rate) =
1628 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1629 $form->{initial_transdate});
1630 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1633 if ($ref->{category} eq 'A') {
1634 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1638 $form->{rowcount} = $i if ($i && !$form->{type});
1643 $main::lxdebug->leave_sub();
1647 $main::lxdebug->enter_sub();
1649 my ($self, $myconfig, $form) = @_;
1651 # connect to database
1652 my $dbh = $form->dbconnect($myconfig);
1654 my $i = $form->{rowcount};
1656 my $where = qq|NOT p.obsolete = '1'|;
1659 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1660 my ($table, $field) = split m/\./, $column;
1661 next if !$form->{"${field}_${i}"};
1662 $where .= qq| AND lower(${column}) ILIKE ?|;
1663 push @values, '%' . $form->{"${field}_${i}"} . '%';
1666 if ($form->{"description_$i"}) {
1667 $where .= qq| ORDER BY p.description|;
1669 $where .= qq| ORDER BY p.partnumber|;
1673 if ($form->{type} eq "invoice") {
1675 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1676 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1680 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1684 my $taxzone_id = $form->{taxzone_id} * 1;
1685 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1689 p.id, p.partnumber, p.description, p.sellprice,
1690 p.listprice, p.inventory_accno_id, p.lastcost,
1692 c1.accno AS inventory_accno,
1693 c1.new_chart_id AS inventory_new_chart,
1694 date($transdate) - c1.valid_from AS inventory_valid,
1696 c2.accno AS income_accno,
1697 c2.new_chart_id AS income_new_chart,
1698 date($transdate) - c2.valid_from AS income_valid,
1700 c3.accno AS expense_accno,
1701 c3.new_chart_id AS expense_new_chart,
1702 date($transdate) - c3.valid_from AS expense_valid,
1704 p.unit, p.assembly, p.bin, p.onhand,
1705 p.notes AS partnotes, p.notes AS longdescription,
1706 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1709 pfac.factor AS price_factor,
1714 LEFT JOIN chart c1 ON
1715 ((SELECT inventory_accno_id
1716 FROM buchungsgruppen
1717 WHERE id = p.buchungsgruppen_id) = c1.id)
1718 LEFT JOIN chart c2 ON
1719 ((SELECT income_accno_id_${taxzone_id}
1720 FROM buchungsgruppen
1721 WHERE id = p.buchungsgruppen_id) = c2.id)
1722 LEFT JOIN chart c3 ON
1723 ((SELECT expense_accno_id_${taxzone_id}
1724 FROM buchungsgruppen
1725 WHERE id = p.buchungsgruppen_id) = c3.id)
1726 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1727 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1729 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1731 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1733 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1734 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1735 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1736 if (!$ref->{inventory_accno_id}) {
1737 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1739 delete($ref->{inventory_accno_id});
1741 foreach my $type (qw(inventory income expense)) {
1742 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1744 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1747 ($ref->{"${type}_accno"},
1748 $ref->{"${type}_new_chart"},
1749 $ref->{"${type}_valid"})
1750 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1754 if ($form->{payment_id} eq "") {
1755 $form->{payment_id} = $form->{part_payment_id};
1758 # get tax rates and description
1759 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1761 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1763 LEFT JOIN chart c ON (c.id = t.chart_id)
1767 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1769 ORDER BY startdate DESC
1772 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1773 my $stw = $dbh->prepare($query);
1774 $stw->execute(@values) || $form->dberror($query);
1776 $ref->{taxaccounts} = "";
1778 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1780 # if ($customertax{$ref->{accno}})
1781 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1785 $ref->{taxaccounts} .= "$ptr->{accno} ";
1787 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1788 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1789 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1790 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1791 $form->{taxaccounts} .= "$ptr->{accno} ";
1797 chop $ref->{taxaccounts};
1798 if ($form->{language_id}) {
1800 qq|SELECT tr.translation, tr.longdescription
1802 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1803 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1804 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1805 if ($translation ne "") {
1806 $ref->{description} = $translation;
1807 $ref->{longdescription} = $longdescription;
1811 qq|SELECT tr.translation, tr.longdescription
1813 WHERE tr.language_id IN
1816 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1819 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1820 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1821 if ($translation ne "") {
1822 $ref->{description} = $translation;
1823 $ref->{longdescription} = $longdescription;
1828 $ref->{onhand} *= 1;
1830 push @{ $form->{item_list} }, $ref;
1832 if ($form->{lizenzen}) {
1833 if ($ref->{inventory_accno} > 0) {
1837 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1838 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1839 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1840 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1849 $main::lxdebug->leave_sub();
1852 ##########################
1853 # get pricegroups from database
1854 # build up selected pricegroup
1855 # if an exchange rate - change price
1858 sub get_pricegroups_for_parts {
1860 $main::lxdebug->enter_sub();
1862 my ($self, $myconfig, $form) = @_;
1864 my $dbh = $form->dbconnect($myconfig);
1866 $form->{"PRICES"} = {};
1870 my $all_units = AM->retrieve_units($myconfig, $form);
1871 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1872 $form->{"PRICES"}{$i} = [];
1874 $id = $form->{"id_$i"};
1876 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1878 $id = $form->{"new_id_$i"};
1881 my ($price, $selectedpricegroup_id) = split(/--/,
1882 $form->{"sellprice_pg_$i"});
1884 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1885 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1886 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1888 my $price_new = $form->{"price_new_$i"};
1889 my $price_old = $form->{"price_old_$i"};
1891 if (!$form->{"unit_old_$i"}) {
1892 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1893 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1894 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1895 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1898 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1899 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1900 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1902 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1903 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1904 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1905 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1906 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1907 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1908 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1913 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1914 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1915 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1916 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1917 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1921 if (!$form->{"basefactor_$i"}) {
1922 $form->{"basefactor_$i"} = 1;
1928 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1929 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1939 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1941 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1942 'selected' AS selected
1945 ORDER BY pricegroup|;
1946 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1947 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1949 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
1951 $pkr->{selected} = '';
1953 # if there is an exchange rate change price
1954 if (($form->{exchangerate} * 1) != 0) {
1956 $pkr->{price} /= $form->{exchangerate};
1959 $pkr->{price} *= $form->{"basefactor_$i"};
1961 $pkr->{price} *= $basefactor;
1963 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1965 if ($selectedpricegroup_id eq undef) {
1966 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1968 $pkr->{selected} = ' selected';
1970 # no customer pricesgroup set
1971 if ($pkr->{price} == $pkr->{default_sellprice}) {
1973 $pkr->{price} = $form->{"sellprice_$i"};
1977 $form->{"sellprice_$i"} = $pkr->{price};
1980 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1981 $pkr->{price} = $form->{"sellprice_$i"};
1982 $pkr->{selected} = ' selected';
1986 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1987 if ($selectedpricegroup_id ne $pricegroup_old) {
1988 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1989 $pkr->{selected} = ' selected';
1991 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1992 if ($pkr->{pricegroup_id} == 0) {
1993 $pkr->{price} = $form->{"sellprice_$i"};
1994 $pkr->{selected} = ' selected';
1996 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1997 $pkr->{selected} = ' selected';
1998 if ( ($pkr->{pricegroup_id} == 0)
1999 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2000 # $pkr->{price} = $form->{"sellprice_$i"};
2002 $pkr->{price} = $form->{"sellprice_$i"};
2006 push @{ $form->{PRICES}{$i} }, $pkr;
2009 $form->{"basefactor_$i"} *= $basefactor;
2018 $main::lxdebug->leave_sub();
2022 $main::lxdebug->enter_sub();
2024 my ($self, $myconfig, $form, $table) = @_;
2026 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2028 # make sure there's no funny stuff in $table
2029 # ToDO: die when this happens and throw an error
2030 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2032 my $dbh = $form->dbconnect($myconfig);
2034 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2035 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2039 $main::lxdebug->leave_sub();
2045 $main::lxdebug->enter_sub();
2047 my ($self, $myconfig, $form, $table, $id) = @_;
2049 $main::lxdebug->leave_sub() and return 0 unless ($id);
2051 # make sure there's no funny stuff in $table
2052 # ToDO: die when this happens and throw an error
2053 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2055 my $dbh = $form->dbconnect($myconfig);
2057 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2058 my ($result) = selectrow_query($form, $dbh, $query, $id);
2062 $main::lxdebug->leave_sub();