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