1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
48 $main::lxdebug->enter_sub();
50 my ($self, $myconfig, $form, $locale) = @_;
52 $form->{duedate} ||= $form->{invdate};
55 my $dbh = $form->dbconnect($myconfig);
58 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
59 ($form->{terms}) = selectrow_query($form, $dbh, $query);
61 my (@project_ids, %projectnumbers);
63 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
65 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
68 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
69 $price_factors{$pfac->{id}} = $pfac;
71 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
74 # sort items by partsgroup
75 for $i (1 .. $form->{rowcount}) {
77 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
78 $partsgroup = $form->{"partsgroup_$i"};
80 push @partsgroup, [$i, $partsgroup];
81 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
85 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
86 join(", ", map({ "?" } @project_ids)) . ")";
87 $sth = $dbh->prepare($query);
88 $sth->execute(@project_ids) ||
89 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
90 while (my $ref = $sth->fetchrow_hashref()) {
91 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
96 $form->{"globalprojectnumber"} =
97 $projectnumbers{$form->{"globalproject_id"}};
104 my %oid = ('Pg' => 'oid',
105 'Oracle' => 'rowid');
107 # sort items by partsgroup
108 for $i (1 .. $form->{rowcount}) {
110 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
111 $partsgroup = $form->{"partsgroup_$i"};
113 push @partsgroup, [$i, $partsgroup];
126 my $nodiscount_subtotal = 0;
127 my $discount_subtotal = 0;
129 my $subtotal_header = 0;
133 qw(runningnumber number description longdescription qty ship unit bin
134 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
135 partnotes serialnumber reqdate sellprice listprice netprice
136 discount p_discount discount_sub nodiscount_sub
137 linetotal nodiscount_linetotal tax_rate projectnumber
138 price_factor price_factor_name);
141 qw(taxbase tax taxdescription taxrate taxnumber);
143 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
146 if ($item->[1] ne $sameitem) {
147 push(@{ $form->{description} }, qq|$item->[1]|);
148 $sameitem = $item->[1];
150 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
153 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
155 if ($form->{"id_$i"} != 0) {
157 # add number, description and qty to $form->{number},
158 if ($form->{"subtotal_$i"} && !$subtotal_header) {
159 $subtotal_header = $i;
160 $position = int($position);
163 } elsif ($subtotal_header) {
165 $position = int($position);
166 $position = $position.".".$subposition;
168 $position = int($position);
172 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
174 push @{ $form->{runningnumber} }, $position;
175 push @{ $form->{number} }, $form->{"partnumber_$i"};
176 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
177 push @{ $form->{bin} }, $form->{"bin_$i"};
178 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
179 push @{ $form->{description} }, $form->{"description_$i"};
180 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
181 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
182 push @{ $form->{unit} }, $form->{"unit_$i"};
183 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
184 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
185 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
186 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
187 push @{ $form->{invnumber} }, $form->{"invnumber"};
188 push @{ $form->{invdate} }, $form->{"invdate"};
189 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
190 push @{ $form->{price_factor_name} }, $price_factor->{description};
192 if ($form->{lizenzen}) {
193 if ($form->{"licensenumber_$i"}) {
194 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
195 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
196 push(@{ $form->{licensenumber} }, $licensenumber);
197 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
200 push(@{ $form->{licensenumber} }, "");
201 push(@{ $form->{validuntil} }, "");
206 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
208 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
209 my ($dec) = ($sellprice =~ /\.(\d+)/);
210 my $decimalplaces = max 2, length($dec);
212 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
213 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
214 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
215 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
217 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
219 $linetotal = ($linetotal != 0) ? $linetotal : '';
221 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
222 push @{ $form->{p_discount} }, $form->{"discount_$i"};
224 $form->{total} += $linetotal;
225 $form->{nodiscount_total} += $nodiscount_linetotal;
226 $form->{discount_total} += $discount;
228 if ($subtotal_header) {
229 $discount_subtotal += $linetotal;
230 $nodiscount_subtotal += $nodiscount_linetotal;
233 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
234 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
235 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
237 $discount_subtotal = 0;
238 $nodiscount_subtotal = 0;
239 $subtotal_header = 0;
242 push @{ $form->{discount_sub} }, "";
243 push @{ $form->{nodiscount_sub} }, "";
246 if (!$form->{"discount_$i"}) {
247 $nodiscount += $linetotal;
250 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
251 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
253 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
255 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
259 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
261 if ($form->{taxincluded}) {
264 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
265 $taxbase = $linetotal - $taxamount;
267 $taxamount = $linetotal * $taxrate;
268 $taxbase = $linetotal;
271 if ($form->round_amount($taxrate, 7) == 0) {
272 if ($form->{taxincluded}) {
273 foreach $item (@taxaccounts) {
275 $form->round_amount($linetotal * $form->{"${item}_rate"} /
276 (1 + abs($form->{"${item}_rate"})),
279 $taxaccounts{$item} += $taxamount;
280 $taxdiff += $taxamount;
282 $taxbase{$item} += $taxbase;
284 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
286 foreach $item (@taxaccounts) {
287 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
288 $taxbase{$item} += $taxbase;
292 foreach $item (@taxaccounts) {
293 $taxaccounts{$item} +=
294 $taxamount * $form->{"${item}_rate"} / $taxrate;
295 $taxbase{$item} += $taxbase;
298 $tax_rate = $taxrate * 100;
299 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
300 if ($form->{"assembly_$i"}) {
303 # get parts and push them onto the stack
305 if ($form->{groupitems}) {
307 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
309 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
313 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
315 JOIN parts p ON (a.parts_id = p.id)
316 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
317 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
318 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
320 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
321 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
322 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
323 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
324 push(@{ $form->{description} }, $sameitem);
327 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
329 push(@{ $form->{description} },
330 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
332 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
333 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
341 foreach my $item (sort keys %taxaccounts) {
342 push(@{ $form->{taxbase} },
343 $form->format_amount($myconfig, $taxbase{$item}, 2));
345 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
347 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
348 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
349 push(@{ $form->{taxrate} },
350 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
351 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
354 for my $i (1 .. $form->{paidaccounts}) {
355 if ($form->{"paid_$i"}) {
356 push(@{ $form->{payment} }, $form->{"paid_$i"});
357 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
358 push(@{ $form->{paymentaccount} }, $description);
359 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
360 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
362 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
365 if($form->{taxincluded}) {
366 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
369 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
372 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
373 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
374 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
375 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
377 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
378 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
380 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
381 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
383 $form->set_payment_options($myconfig, $form->{invdate});
385 $form->{username} = $myconfig->{name};
389 $main::lxdebug->leave_sub();
392 sub project_description {
393 $main::lxdebug->enter_sub();
395 my ($self, $dbh, $id) = @_;
397 my $query = qq|SELECT description FROM project WHERE id = ?|;
398 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
400 $main::lxdebug->leave_sub();
405 sub customer_details {
406 $main::lxdebug->enter_sub();
408 my ($self, $myconfig, $form, @wanted_vars) = @_;
410 # connect to database
411 my $dbh = $form->dbconnect($myconfig);
413 # get contact id, set it if nessessary
416 my @values = (conv_i($form->{customer_id}));
419 if ($form->{cp_id}) {
420 $where = qq| AND (cp.cp_id = ?) |;
421 push(@values, conv_i($form->{cp_id}));
424 # get rest for the customer
426 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
427 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
429 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
430 WHERE (ct.id = ?) $where
433 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
435 # remove id and taxincluded before copy back
436 delete @$ref{qw(id taxincluded)};
438 @wanted_vars = grep({ $_ } @wanted_vars);
439 if (scalar(@wanted_vars) > 0) {
441 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
442 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
445 map { $form->{$_} = $ref->{$_} } keys %$ref;
447 if ($form->{delivery_customer_id}) {
449 qq|SELECT *, notes as customernotes
453 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
455 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
458 if ($form->{delivery_vendor_id}) {
460 qq|SELECT *, notes as customernotes
464 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
466 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
469 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
471 'trans_id' => $form->{customer_id});
472 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
476 $main::lxdebug->leave_sub();
480 $main::lxdebug->enter_sub();
482 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
484 # connect to database, turn off autocommit
485 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
487 my ($query, $sth, $null, $project_id, @values);
488 my $exchangerate = 0;
490 if (!$form->{employee_id}) {
491 $form->get_employee($dbh);
494 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
496 ($null, $form->{department_id}) = split(/--/, $form->{department});
498 my $all_units = AM->retrieve_units($myconfig, $form);
500 if (!$payments_only) {
502 &reverse_invoice($dbh, $form);
505 $query = qq|SELECT nextval('glid')|;
506 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
508 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
509 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
511 if (!$form->{invnumber}) {
513 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
514 "cnnumber" : "invnumber", $dbh);
519 my ($netamount, $invoicediff) = (0, 0);
520 my ($amount, $linetotal, $lastincomeaccno);
522 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
523 my $defaultcurrency = (split m/:/, $currencies)[0];
525 if ($form->{currency} eq $defaultcurrency) {
526 $form->{exchangerate} = 1;
528 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
531 $form->{exchangerate} =
534 : $form->parse_amount($myconfig, $form->{exchangerate});
536 $form->{expense_inventory} = "";
540 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
541 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
544 foreach my $i (1 .. $form->{rowcount}) {
545 if ($form->{type} eq "credit_note") {
546 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
547 $form->{shipped} = 1;
549 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
554 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
555 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
556 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
558 if ($form->{storno}) {
559 $form->{"qty_$i"} *= -1;
562 if ($form->{"id_$i"}) {
565 if (defined($baseunits{$form->{"id_$i"}})) {
566 $item_unit = $baseunits{$form->{"id_$i"}};
569 $query = qq|SELECT unit FROM parts WHERE id = ?|;
570 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
571 $baseunits{$form->{"id_$i"}} = $item_unit;
574 if (defined($all_units->{$item_unit}->{factor})
575 && ($all_units->{$item_unit}->{factor} ne '')
576 && ($all_units->{$item_unit}->{factor} != 0)) {
577 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
581 $baseqty = $form->{"qty_$i"} * $basefactor;
583 my ($allocated, $taxrate) = (0, 0);
587 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
589 # keep entered selling price
591 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
593 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
595 my $decimalplaces = ($dec > 2) ? $dec : 2;
597 # undo discount formatting
598 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
601 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
603 # round linetotal to 2 decimal places
604 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
605 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
607 if ($form->{taxincluded}) {
608 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
609 $form->{"sellprice_$i"} =
610 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
612 $taxamount = $linetotal * $taxrate;
615 $netamount += $linetotal;
617 if ($taxamount != 0) {
619 $form->{amount}{ $form->{id} }{$_} +=
620 $taxamount * $form->{"${_}_rate"} / $taxrate
621 } split(/ /, $form->{"taxaccounts_$i"});
624 # add amount to income, $form->{amount}{trans_id}{accno}
625 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
627 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
628 $linetotal = $form->round_amount($linetotal, 2);
630 # this is the difference from the inventory
631 $invoicediff += ($amount - $linetotal);
633 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
636 $lastincomeaccno = $form->{"income_accno_$i"};
638 # adjust and round sellprice
639 $form->{"sellprice_$i"} =
640 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
643 next if $payments_only;
645 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
647 if ($form->{"assembly_$i"}) {
648 # record assembly item as allocated
649 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
652 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
656 # get pricegroup_id and save it
657 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
660 # save detail record in invoice table
662 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
663 sellprice, fxsellprice, discount, allocated, assemblyitem,
664 unit, deliverydate, project_id, serialnumber, pricegroup_id,
665 ordnumber, transdate, cusordnumber, base_qty, subtotal,
666 marge_percent, marge_total, lastcost,
667 price_factor_id, price_factor, marge_price_factor)
668 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
669 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
671 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
672 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
673 $form->{"sellprice_$i"}, $fxsellprice,
674 $form->{"discount_$i"}, $allocated, 'f',
675 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
676 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
677 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
678 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
679 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
680 $form->{"lastcost_$i"},
681 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
682 conv_i($form->{"marge_price_factor_$i"}));
683 do_query($form, $dbh, $query, @values);
685 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
687 qq|INSERT INTO licenseinvoice (trans_id, license_id)
688 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
689 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
690 do_query($form, $dbh, $query, @values);
695 $form->{datepaid} = $form->{invdate};
697 # total payments, don't move we need it here
698 for my $i (1 .. $form->{paidaccounts}) {
699 if ($form->{type} eq "credit_note") {
700 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
702 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
704 $form->{paid} += $form->{"paid_$i"};
705 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
708 my ($tax, $diff) = (0, 0);
710 $netamount = $form->round_amount($netamount, 2);
712 # figure out rounding errors for total amount vs netamount + taxes
713 if ($form->{taxincluded}) {
715 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
716 $diff += $amount - $netamount * $form->{exchangerate};
717 $netamount = $amount;
719 foreach my $item (split(/ /, $form->{taxaccounts})) {
720 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
721 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
722 $tax += $form->{amount}{ $form->{id} }{$item};
723 $netamount -= $form->{amount}{ $form->{id} }{$item};
726 $invoicediff += $diff;
727 ######## this only applies to tax included
728 if ($lastincomeaccno) {
729 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
733 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
734 $diff = $amount - $netamount * $form->{exchangerate};
735 $netamount = $amount;
736 foreach my $item (split(/ /, $form->{taxaccounts})) {
737 $form->{amount}{ $form->{id} }{$item} =
738 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
741 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
744 $amount - $form->{amount}{ $form->{id} }{$item} *
745 $form->{exchangerate};
746 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
747 $tax += $form->{amount}{ $form->{id} }{$item};
751 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
753 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
756 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
758 # update exchangerate
759 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
760 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
761 $form->{exchangerate}, 0);
764 $project_id = conv_i($form->{"globalproject_id"});
766 foreach my $trans_id (keys %{ $form->{amount} }) {
767 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
768 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
770 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
772 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
774 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
775 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
776 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
777 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
778 do_query($form, $dbh, $query, @values);
779 $form->{amount}{$trans_id}{$accno} = 0;
783 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
784 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
786 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
788 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
789 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
790 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
791 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
792 do_query($form, $dbh, $query, @values);
797 # deduct payment differences from diff
798 for my $i (1 .. $form->{paidaccounts}) {
799 if ($form->{"paid_$i"} != 0) {
801 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
802 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
806 # record payments and offsetting AR
807 if (!$form->{storno}) {
808 for my $i (1 .. $form->{paidaccounts}) {
810 next if ($form->{"paid_$i"} == 0);
812 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
813 $form->{"datepaid_$i"} = $form->{invdate}
814 unless ($form->{"datepaid_$i"});
815 $form->{datepaid} = $form->{"datepaid_$i"};
819 if ($form->{currency} eq $defaultcurrency) {
820 $form->{"exchangerate_$i"} = 1;
822 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
823 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
827 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
829 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
833 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
834 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
835 do_query($form, $dbh, $query, @values);
839 $form->{"paid_$i"} *= -1;
842 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
843 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
844 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
845 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
846 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
847 do_query($form, $dbh, $query, @values);
849 # exchangerate difference
850 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
851 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
855 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
856 $form->{"exchangerate_$i"};
858 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
861 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
867 # update exchange rate
868 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
869 $form->update_exchangerate($dbh, $form->{currency},
870 $form->{"datepaid_$i"},
871 $form->{"exchangerate_$i"}, 0);
875 } else { # if (!$form->{storno})
876 $form->{marge_total} *= -1;
879 if ($payments_only) {
880 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
881 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
883 if (!$provided_dbh) {
888 $main::lxdebug->leave_sub();
892 # record exchange rate differences and gains/losses
893 foreach my $accno (keys %{ $form->{fx} }) {
894 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
896 ($form->{fx}{$accno}{$transdate} =
897 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
902 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
903 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
904 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
905 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
906 do_query($form, $dbh, $query, @values);
911 $amount = $netamount + $tax;
914 $query = qq|UPDATE ar set
915 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
916 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
917 amount = ?, netamount = ?, paid = ?, datepaid = ?,
918 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
919 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
920 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
921 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
922 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
923 cp_id = ?, marge_total = ?, marge_percent = ?,
924 globalproject_id = ?, delivery_customer_id = ?,
925 transaction_description = ?, delivery_vendor_id = ?
927 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
928 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
929 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
930 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
931 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
932 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
933 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
934 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
935 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
936 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
937 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
938 conv_i($form->{"id"}));
939 do_query($form, $dbh, $query, @values);
941 if($form->{"formname"} eq "credit_note") {
942 for my $i (1 .. $form->{rowcount}) {
943 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
944 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
945 do_query($form, $dbh, $query, @values);
949 if ($form->{storno}) {
952 paid = paid + amount,
954 intnotes = ? || intnotes
956 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
957 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
961 $form->{name} = $form->{customer};
962 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
964 if (!$form->{shipto_id}) {
965 $form->add_shipto($dbh, $form->{id}, "AR");
968 # save printed, emailed and queued
969 $form->save_status($dbh);
971 Common::webdav_folder($form) if ($main::webdav);
973 # Link this record to the records it was created from.
974 RecordLinks->create_links('dbh' => $dbh,
976 'from_table' => 'oe',
977 'from_ids' => $form->{convert_from_oe_ids},
979 'to_id' => $form->{id},
981 delete $form->{convert_from_oe_ids};
983 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
985 if (scalar @convert_from_do_ids) {
986 DO->close_orders('dbh' => $dbh,
987 'ids' => \@convert_from_do_ids);
989 RecordLinks->create_links('dbh' => $dbh,
991 'from_table' => 'delivery_orders',
992 'from_ids' => \@convert_from_do_ids,
994 'to_id' => $form->{id},
999 if (!$provided_dbh) {
1004 $main::lxdebug->leave_sub();
1009 sub _delete_payments {
1010 $main::lxdebug->enter_sub();
1012 my ($self, $form, $dbh) = @_;
1016 # Delete old payment entries from acc_trans.
1020 WHERE (trans_id = ?) AND fx_transaction
1026 LEFT JOIN chart c ON (at.chart_id = c.id)
1027 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1028 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1033 LEFT JOIN chart c ON (at.chart_id = c.id)
1034 WHERE (trans_id = ?)
1035 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1038 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1041 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1042 do_query($form, $dbh, $query);
1045 $main::lxdebug->leave_sub();
1049 $main::lxdebug->enter_sub();
1051 my ($self, $myconfig, $form, $locale) = @_;
1053 # connect to database, turn off autocommit
1054 my $dbh = $form->dbconnect_noauto($myconfig);
1056 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1058 $old_form = save_form();
1060 # Delete all entries in acc_trans from prior payments.
1061 $self->_delete_payments($form, $dbh);
1063 # Save the new payments the user made before cleaning up $form.
1064 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1066 # Clean up $form so that old content won't tamper the results.
1067 %keep_vars = map { $_, 1 } qw(login password id);
1068 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1070 # Retrieve the invoice from the database.
1071 $self->retrieve_invoice($myconfig, $form);
1073 # Set up the content of $form in the way that IS::post_invoice() expects.
1074 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1076 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1077 $item = $form->{invoice_details}->[$row - 1];
1079 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1081 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1084 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1086 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1088 # Restore the payment options from the user input.
1089 map { $form->{$_} = $payments{$_} } keys %payments;
1091 # Get the AR accno (which is normally done by Form::create_links()).
1095 LEFT JOIN chart c ON (at.chart_id = c.id)
1096 WHERE (trans_id = ?)
1097 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1101 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1103 # Post the new payments.
1104 $self->post_invoice($myconfig, $form, $dbh, 1);
1106 restore_form($old_form);
1108 my $rc = $dbh->commit();
1111 $main::lxdebug->leave_sub();
1116 sub process_assembly {
1117 $main::lxdebug->enter_sub();
1119 my ($dbh, $form, $id, $totalqty) = @_;
1122 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1123 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1125 JOIN parts p ON (a.parts_id = p.id)
1127 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1129 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1133 $ref->{inventory_accno_id} *= 1;
1134 $ref->{expense_accno_id} *= 1;
1136 # multiply by number of assemblies
1137 $ref->{qty} *= $totalqty;
1139 if ($ref->{assembly}) {
1140 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1143 if ($ref->{inventory_accno_id}) {
1144 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1148 # save detail record for individual assembly item in invoice table
1150 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1151 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1152 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1153 do_query($form, $dbh, $query, @values);
1159 $main::lxdebug->leave_sub();
1163 $main::lxdebug->enter_sub();
1165 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1166 $form->{taxzone_id} *=1;
1167 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1168 my $taxzone_id = $form->{"taxzone_id"} * 1;
1170 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1171 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1172 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1173 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1174 FROM invoice i, parts p
1175 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1176 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1177 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1178 WHERE (i.parts_id = p.id)
1179 AND (i.parts_id = ?)
1180 AND ((i.base_qty + i.allocated) < 0)
1182 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1187 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1188 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1192 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1194 # total expenses and inventory
1195 # sellprice is the cost of the item
1196 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1199 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1201 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1202 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1203 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1205 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1206 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1212 last if (($totalqty -= $qty) <= 0);
1217 $main::lxdebug->leave_sub();
1222 sub reverse_invoice {
1223 $main::lxdebug->enter_sub();
1225 my ($dbh, $form) = @_;
1227 # reverse inventory items
1229 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1231 JOIN parts p ON (i.parts_id = p.id)
1232 WHERE i.trans_id = ?|;
1233 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1235 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1237 if ($ref->{inventory_accno_id}) {
1238 # de-allocated purchases
1240 qq|SELECT i.id, i.trans_id, i.allocated
1242 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1243 ORDER BY i.trans_id DESC|;
1244 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1246 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1248 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1249 $qty = $inhref->{allocated};
1253 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1255 last if (($ref->{qty} -= $qty) <= 0);
1264 @values = (conv_i($form->{id}));
1265 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1266 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1268 if ($form->{lizenzen}) {
1270 qq|DELETE FROM licenseinvoice
1271 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1272 do_query($form, $dbh, $query, @values);
1275 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1277 $main::lxdebug->leave_sub();
1280 sub delete_invoice {
1281 $main::lxdebug->enter_sub();
1283 my ($self, $myconfig, $form, $spool) = @_;
1285 # connect to database
1286 my $dbh = $form->dbconnect_noauto($myconfig);
1288 &reverse_invoice($dbh, $form);
1290 my @values = (conv_i($form->{id}));
1293 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1295 # delete spool files
1296 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1298 # delete status entries
1299 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1301 my $rc = $dbh->commit;
1305 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1308 $main::lxdebug->leave_sub();
1313 sub retrieve_invoice {
1314 $main::lxdebug->enter_sub();
1316 my ($self, $myconfig, $form) = @_;
1318 # connect to database
1319 my $dbh = $form->dbconnect_noauto($myconfig);
1321 my ($sth, $ref, $query);
1323 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1327 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1328 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1329 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1330 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1331 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1332 d.curr AS currencies
1336 $ref = selectfirst_hashref_query($form, $dbh, $query);
1337 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1340 my $id = conv_i($form->{id});
1345 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1346 a.orddate, a.quodate, a.globalproject_id,
1347 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1348 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1349 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1350 a.employee_id, a.salesman_id, a.payment_id,
1351 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1352 a.transaction_description,
1353 a.marge_total, a.marge_percent,
1356 LEFT JOIN employee e ON (e.id = a.employee_id)
1358 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1359 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1362 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1365 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1366 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1368 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1370 foreach my $vc (qw(customer vendor)) {
1371 next if !$form->{"delivery_${vc}_id"};
1372 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1375 # get printed, emailed
1376 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1377 $sth = prepare_execute_query($form, $dbh, $query, $id);
1379 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1380 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1381 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1382 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1385 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1387 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1388 : $form->{invdate} ? $dbh->quote($form->{invdate})
1392 my $taxzone_id = $form->{taxzone_id} *= 1;
1393 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1395 # retrieve individual items
1398 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1399 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1400 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1402 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1403 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1404 i.price_factor_id, i.price_factor, i.marge_price_factor,
1405 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1406 pr.projectnumber, pg.partsgroup, prg.pricegroup
1409 LEFT JOIN parts p ON (i.parts_id = p.id)
1410 LEFT JOIN project pr ON (i.project_id = pr.id)
1411 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1412 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1414 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1415 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1416 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1418 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1420 $sth = prepare_execute_query($form, $dbh, $query, $id);
1422 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1423 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1424 delete($ref->{"part_inventory_accno_id"});
1426 foreach my $type (qw(inventory income expense)) {
1427 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1428 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1429 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1433 # get tax rates and description
1434 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1436 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1437 LEFT JOIN chart c ON (c.id = t.chart_id)
1439 (SELECT tk.tax_id FROM taxkeys tk
1440 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1441 AND startdate <= date($transdate)
1442 ORDER BY startdate DESC LIMIT 1)
1444 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1445 $ref->{taxaccounts} = "";
1447 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1449 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1453 $ref->{taxaccounts} .= "$ptr->{accno} ";
1455 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1456 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1457 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1458 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1459 $form->{taxaccounts} .= "$ptr->{accno} ";
1464 if ($form->{lizenzen}) {
1465 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1466 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1467 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1470 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1472 chop $ref->{taxaccounts};
1473 push @{ $form->{invoice_details} }, $ref;
1478 Common::webdav_folder($form) if ($main::webdav);
1481 my $rc = $dbh->commit;
1484 $main::lxdebug->leave_sub();
1490 $main::lxdebug->enter_sub();
1492 my ($self, $myconfig, $form) = @_;
1494 # connect to database
1495 my $dbh = $form->dbconnect($myconfig);
1497 my $dateformat = $myconfig->{dateformat};
1498 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1500 my (@values, $duedate, $ref, $query);
1502 if ($form->{invdate}) {
1503 $duedate = "to_date(?, '$dateformat')";
1504 push @values, $form->{invdate};
1506 $duedate = "current_date";
1509 my $cid = conv_i($form->{customer_id});
1512 if ($form->{payment_id}) {
1513 $payment_id = "(pt.id = ?) OR";
1514 push @values, conv_i($form->{payment_id});
1520 c.name AS customer, c.discount, c.creditlimit, c.terms,
1521 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1522 c.street, c.zipcode, c.city, c.country,
1523 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1524 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1525 b.discount AS tradediscount, b.description AS business
1527 LEFT JOIN business b ON (b.id = c.business_id)
1528 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1531 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1532 map { $form->{$_} = $ref->{$_} } keys %$ref;
1535 qq|SELECT sum(amount - paid) AS dunning_amount
1537 WHERE (paid < amount)
1538 AND (customer_id = ?)
1539 AND (dunning_config_id IS NOT NULL)|;
1540 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1541 map { $form->{$_} = $ref->{$_} } keys %$ref;
1544 qq|SELECT dnn.dunning_description AS max_dunning_level
1545 FROM dunning_config dnn
1546 WHERE id IN (SELECT dunning_config_id
1548 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1549 ORDER BY dunning_level DESC LIMIT 1|;
1550 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1551 map { $form->{$_} = $ref->{$_} } keys %$ref;
1553 $form->{creditremaining} = $form->{creditlimit};
1554 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1555 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1556 $form->{creditremaining} -= $value;
1560 (SELECT e.buy FROM exchangerate e
1561 WHERE e.curr = o.curr
1562 AND e.transdate = o.transdate)
1564 WHERE o.customer_id = ?
1565 AND o.quotation = '0'
1566 AND o.closed = '0'|;
1567 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1569 while (my ($amount, $exch) = $sth->fetchrow_array) {
1570 $exch = 1 unless $exch;
1571 $form->{creditremaining} -= $amount * $exch;
1575 # get shipto if we did not converted an order or invoice
1576 if (!$form->{shipto}) {
1577 map { delete $form->{$_} }
1578 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1579 shiptostreet shiptozipcode shiptocity shiptocountry
1580 shiptocontact shiptophone shiptofax shiptoemail);
1582 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1583 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1585 map { $form->{$_} = $ref->{$_} } keys %$ref;
1588 # setup last accounts used for this customer
1589 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1591 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1593 JOIN acc_trans ac ON (ac.chart_id = c.id)
1594 JOIN ar a ON (a.id = ac.trans_id)
1595 WHERE a.customer_id = ?
1596 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1597 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1598 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1601 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1602 if ($ref->{category} eq 'I') {
1604 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1606 if ($form->{initial_transdate}) {
1608 qq|SELECT tk.tax_id, t.rate
1610 LEFT JOIN tax t ON tk.tax_id = t.id
1611 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1612 ORDER BY tk.startdate DESC
1614 my ($tax_id, $rate) =
1615 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1616 $form->{initial_transdate});
1617 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1620 if ($ref->{category} eq 'A') {
1621 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1625 $form->{rowcount} = $i if ($i && !$form->{type});
1630 $main::lxdebug->leave_sub();
1634 $main::lxdebug->enter_sub();
1636 my ($self, $myconfig, $form) = @_;
1638 # connect to database
1639 my $dbh = $form->dbconnect($myconfig);
1641 my $i = $form->{rowcount};
1643 my $where = qq|NOT p.obsolete = '1'|;
1646 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1647 my ($table, $field) = split m/\./, $column;
1648 next if !$form->{"${field}_${i}"};
1649 $where .= qq| AND lower(${column}) ILIKE ?|;
1650 push @values, '%' . $form->{"${field}_${i}"} . '%';
1653 if ($form->{"description_$i"}) {
1654 $where .= qq| ORDER BY p.description|;
1656 $where .= qq| ORDER BY p.partnumber|;
1660 if ($form->{type} eq "invoice") {
1662 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1663 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1667 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1671 my $taxzone_id = $form->{taxzone_id} * 1;
1672 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1676 p.id, p.partnumber, p.description, p.sellprice,
1677 p.listprice, p.inventory_accno_id, p.lastcost,
1679 c1.accno AS inventory_accno,
1680 c1.new_chart_id AS inventory_new_chart,
1681 date($transdate) - c1.valid_from AS inventory_valid,
1683 c2.accno AS income_accno,
1684 c2.new_chart_id AS income_new_chart,
1685 date($transdate) - c2.valid_from AS income_valid,
1687 c3.accno AS expense_accno,
1688 c3.new_chart_id AS expense_new_chart,
1689 date($transdate) - c3.valid_from AS expense_valid,
1691 p.unit, p.assembly, p.bin, p.onhand,
1692 p.notes AS partnotes, p.notes AS longdescription,
1693 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1696 pfac.factor AS price_factor,
1701 LEFT JOIN chart c1 ON
1702 ((SELECT inventory_accno_id
1703 FROM buchungsgruppen
1704 WHERE id = p.buchungsgruppen_id) = c1.id)
1705 LEFT JOIN chart c2 ON
1706 ((SELECT income_accno_id_${taxzone_id}
1707 FROM buchungsgruppen
1708 WHERE id = p.buchungsgruppen_id) = c2.id)
1709 LEFT JOIN chart c3 ON
1710 ((SELECT expense_accno_id_${taxzone_id}
1711 FROM buchungsgruppen
1712 WHERE id = p.buchungsgruppen_id) = c3.id)
1713 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1714 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1716 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1718 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1720 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1721 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1722 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1723 if (!$ref->{inventory_accno_id}) {
1724 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1726 delete($ref->{inventory_accno_id});
1728 foreach my $type (qw(inventory income expense)) {
1729 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1731 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1734 ($ref->{"${type}_accno"},
1735 $ref->{"${type}_new_chart"},
1736 $ref->{"${type}_valid"})
1737 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1741 if ($form->{payment_id} eq "") {
1742 $form->{payment_id} = $form->{part_payment_id};
1745 # get tax rates and description
1746 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1748 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1750 LEFT JOIN chart c ON (c.id = t.chart_id)
1754 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1756 ORDER BY startdate DESC
1759 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1760 $stw = $dbh->prepare($query);
1761 $stw->execute(@values) || $form->dberror($query);
1763 $ref->{taxaccounts} = "";
1765 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1767 # if ($customertax{$ref->{accno}}) {
1768 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1772 $ref->{taxaccounts} .= "$ptr->{accno} ";
1774 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1775 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1776 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1777 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1778 $form->{taxaccounts} .= "$ptr->{accno} ";
1784 chop $ref->{taxaccounts};
1785 if ($form->{language_id}) {
1787 qq|SELECT tr.translation, tr.longdescription
1789 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1790 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1791 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1792 if ($translation ne "") {
1793 $ref->{description} = $translation;
1794 $ref->{longdescription} = $longdescription;
1798 qq|SELECT tr.translation, tr.longdescription
1800 WHERE tr.language_id IN
1803 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1806 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1807 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1808 if ($translation ne "") {
1809 $ref->{description} = $translation;
1810 $ref->{longdescription} = $longdescription;
1815 $ref->{onhand} *= 1;
1817 push @{ $form->{item_list} }, $ref;
1819 if ($form->{lizenzen}) {
1820 if ($ref->{inventory_accno} > 0) {
1824 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1825 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1826 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1827 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1836 $main::lxdebug->leave_sub();
1839 ##########################
1840 # get pricegroups from database
1841 # build up selected pricegroup
1842 # if an exchange rate - change price
1845 sub get_pricegroups_for_parts {
1847 $main::lxdebug->enter_sub();
1849 my ($self, $myconfig, $form) = @_;
1851 my $dbh = $form->dbconnect($myconfig);
1853 $form->{"PRICES"} = {};
1857 my $all_units = AM->retrieve_units($myconfig, $form);
1858 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1859 $form->{"PRICES"}{$i} = [];
1861 $id = $form->{"id_$i"};
1863 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1865 $id = $form->{"new_id_$i"};
1868 ($price, $selectedpricegroup_id) = split(/--/,
1869 $form->{"sellprice_pg_$i"});
1871 $pricegroup_old = $form->{"pricegroup_old_$i"};
1872 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1873 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1875 $price_new = $form->{"price_new_$i"};
1876 $price_old = $form->{"price_old_$i"};
1878 if (!$form->{"unit_old_$i"}) {
1879 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1880 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1881 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1882 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1885 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1886 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1887 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1889 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1890 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1891 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1892 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1893 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1894 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1895 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1900 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1901 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1902 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1903 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1904 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1908 if (!$form->{"basefactor_$i"}) {
1909 $form->{"basefactor_$i"} = 1;
1915 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1916 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1926 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1928 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1929 'selected' AS selected
1932 ORDER BY pricegroup|;
1933 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1934 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1936 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1938 $pkr->{selected} = '';
1940 # if there is an exchange rate change price
1941 if (($form->{exchangerate} * 1) != 0) {
1943 $pkr->{price} /= $form->{exchangerate};
1946 $pkr->{price} *= $form->{"basefactor_$i"};
1948 $pkr->{price} *= $basefactor;
1950 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1952 if ($selectedpricegroup_id eq undef) {
1953 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1955 $pkr->{selected} = ' selected';
1957 # no customer pricesgroup set
1958 if ($pkr->{price} == $pkr->{default_sellprice}) {
1960 $pkr->{price} = $form->{"sellprice_$i"};
1964 $form->{"sellprice_$i"} = $pkr->{price};
1967 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1968 $pkr->{price} = $form->{"sellprice_$i"};
1969 $pkr->{selected} = ' selected';
1973 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1974 if ($selectedpricegroup_id ne $pricegroup_old) {
1975 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1976 $pkr->{selected} = ' selected';
1978 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1979 if ($pkr->{pricegroup_id} == 0) {
1980 $pkr->{price} = $form->{"sellprice_$i"};
1981 $pkr->{selected} = ' selected';
1983 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1984 $pkr->{selected} = ' selected';
1985 if ( ($pkr->{pricegroup_id} == 0)
1986 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1987 # $pkr->{price} = $form->{"sellprice_$i"};
1989 $pkr->{price} = $form->{"sellprice_$i"};
1993 push @{ $form->{PRICES}{$i} }, $pkr;
1996 $form->{"basefactor_$i"} *= $basefactor;
2005 $main::lxdebug->leave_sub();
2009 $main::lxdebug->enter_sub();
2011 my ($self, $myconfig, $form, $table) = @_;
2013 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2015 # make sure there's no funny stuff in $table
2016 # ToDO: die when this happens and throw an error
2017 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2019 my $dbh = $form->dbconnect($myconfig);
2021 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2022 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2026 $main::lxdebug->leave_sub();
2032 $main::lxdebug->enter_sub();
2034 my ($self, $myconfig, $form, $table, $id) = @_;
2036 $main::lxdebug->leave_sub() and return 0 unless ($id);
2038 # make sure there's no funny stuff in $table
2039 # ToDO: die when this happens and throw an error
2040 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2042 my $dbh = $form->dbconnect($myconfig);
2044 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2045 my ($result) = selectrow_query($form, $dbh, $query, $id);
2049 $main::lxdebug->leave_sub();