1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
49 $main::lxdebug->enter_sub();
51 my ($self, $myconfig, $form, $locale) = @_;
53 $form->{duedate} ||= $form->{invdate};
56 my $dbh = $form->dbconnect($myconfig);
59 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
60 ($form->{terms}) = selectrow_query($form, $dbh, $query);
62 my (@project_ids, %projectnumbers);
64 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
66 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
69 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
70 $price_factors{$pfac->{id}} = $pfac;
72 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
75 # sort items by partsgroup
76 for $i (1 .. $form->{rowcount}) {
78 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
79 $partsgroup = $form->{"partsgroup_$i"};
81 push @partsgroup, [$i, $partsgroup];
82 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
86 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
87 join(", ", map({ "?" } @project_ids)) . ")";
88 $sth = $dbh->prepare($query);
89 $sth->execute(@project_ids) ||
90 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
91 while (my $ref = $sth->fetchrow_hashref()) {
92 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
97 $form->{"globalprojectnumber"} =
98 $projectnumbers{$form->{"globalproject_id"}};
105 my %oid = ('Pg' => 'oid',
106 'Oracle' => 'rowid');
108 # sort items by partsgroup
109 for $i (1 .. $form->{rowcount}) {
111 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
112 $partsgroup = $form->{"partsgroup_$i"};
114 push @partsgroup, [$i, $partsgroup];
127 my $nodiscount_subtotal = 0;
128 my $discount_subtotal = 0;
130 my $subtotal_header = 0;
133 $form->{discount} = [];
136 qw(runningnumber number description longdescription qty ship unit bin
137 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
138 partnotes serialnumber reqdate sellprice listprice netprice
139 discount p_discount discount_sub nodiscount_sub
140 linetotal nodiscount_linetotal tax_rate projectnumber
141 price_factor price_factor_name);
144 qw(taxbase tax taxdescription taxrate taxnumber);
146 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
149 if ($item->[1] ne $sameitem) {
150 push(@{ $form->{description} }, qq|$item->[1]|);
151 $sameitem = $item->[1];
153 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
156 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
158 if ($form->{"id_$i"} != 0) {
160 # add number, description and qty to $form->{number},
161 if ($form->{"subtotal_$i"} && !$subtotal_header) {
162 $subtotal_header = $i;
163 $position = int($position);
166 } elsif ($subtotal_header) {
168 $position = int($position);
169 $position = $position.".".$subposition;
171 $position = int($position);
175 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
177 push @{ $form->{runningnumber} }, $position;
178 push @{ $form->{number} }, $form->{"partnumber_$i"};
179 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
180 push @{ $form->{bin} }, $form->{"bin_$i"};
181 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
182 push @{ $form->{description} }, $form->{"description_$i"};
183 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
184 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
185 push @{ $form->{unit} }, $form->{"unit_$i"};
186 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
187 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
188 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
189 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
190 push @{ $form->{invnumber} }, $form->{"invnumber"};
191 push @{ $form->{invdate} }, $form->{"invdate"};
192 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
193 push @{ $form->{price_factor_name} }, $price_factor->{description};
195 if ($form->{lizenzen}) {
196 if ($form->{"licensenumber_$i"}) {
197 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
198 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
199 push(@{ $form->{licensenumber} }, $licensenumber);
200 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
203 push(@{ $form->{licensenumber} }, "");
204 push(@{ $form->{validuntil} }, "");
209 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
211 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
212 my ($dec) = ($sellprice =~ /\.(\d+)/);
213 my $decimalplaces = max 2, length($dec);
215 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
216 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor}, $decimalplaces);
217 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor}, 2);
218 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
219 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
221 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
223 $linetotal = ($linetotal != 0) ? $linetotal : '';
225 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
226 push @{ $form->{p_discount} }, $form->{"discount_$i"};
228 $form->{total} += $linetotal;
229 $form->{nodiscount_total} += $nodiscount_linetotal;
230 $form->{discount_total} += $discount;
232 if ($subtotal_header) {
233 $discount_subtotal += $linetotal;
234 $nodiscount_subtotal += $nodiscount_linetotal;
237 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
238 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
239 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
241 $discount_subtotal = 0;
242 $nodiscount_subtotal = 0;
243 $subtotal_header = 0;
246 push @{ $form->{discount_sub} }, "";
247 push @{ $form->{nodiscount_sub} }, "";
250 if (!$form->{"discount_$i"}) {
251 $nodiscount += $linetotal;
254 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
255 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
257 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
259 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
263 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
265 if ($form->{taxincluded}) {
268 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
269 $taxbase = $linetotal - $taxamount;
271 $taxamount = $linetotal * $taxrate;
272 $taxbase = $linetotal;
275 if ($form->round_amount($taxrate, 7) == 0) {
276 if ($form->{taxincluded}) {
277 foreach $item (@taxaccounts) {
279 $form->round_amount($linetotal * $form->{"${item}_rate"} /
280 (1 + abs($form->{"${item}_rate"})),
283 $taxaccounts{$item} += $taxamount;
284 $taxdiff += $taxamount;
286 $taxbase{$item} += $taxbase;
288 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
290 foreach $item (@taxaccounts) {
291 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
292 $taxbase{$item} += $taxbase;
296 foreach $item (@taxaccounts) {
297 $taxaccounts{$item} +=
298 $taxamount * $form->{"${item}_rate"} / $taxrate;
299 $taxbase{$item} += $taxbase;
302 $tax_rate = $taxrate * 100;
303 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
304 if ($form->{"assembly_$i"}) {
307 # get parts and push them onto the stack
309 if ($form->{groupitems}) {
311 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
313 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
317 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
319 JOIN parts p ON (a.parts_id = p.id)
320 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
321 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
322 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
324 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
325 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
326 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
327 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
328 push(@{ $form->{description} }, $sameitem);
331 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
333 push(@{ $form->{description} },
334 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
336 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
337 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
345 foreach my $item (sort keys %taxaccounts) {
346 push(@{ $form->{taxbase} },
347 $form->format_amount($myconfig, $taxbase{$item}, 2));
349 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
351 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
352 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
353 push(@{ $form->{taxrate} },
354 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
355 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
358 for my $i (1 .. $form->{paidaccounts}) {
359 if ($form->{"paid_$i"}) {
360 push(@{ $form->{payment} }, $form->{"paid_$i"});
361 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
362 push(@{ $form->{paymentaccount} }, $description);
363 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
364 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
366 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
369 if($form->{taxincluded}) {
370 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
373 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
376 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
377 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
378 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
379 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
381 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
382 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
384 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
385 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
387 $form->set_payment_options($myconfig, $form->{invdate});
389 $form->{username} = $myconfig->{name};
393 $main::lxdebug->leave_sub();
396 sub project_description {
397 $main::lxdebug->enter_sub();
399 my ($self, $dbh, $id) = @_;
401 my $query = qq|SELECT description FROM project WHERE id = ?|;
402 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
404 $main::lxdebug->leave_sub();
409 sub customer_details {
410 $main::lxdebug->enter_sub();
412 my ($self, $myconfig, $form, @wanted_vars) = @_;
414 # connect to database
415 my $dbh = $form->dbconnect($myconfig);
417 # get contact id, set it if nessessary
420 my @values = (conv_i($form->{customer_id}));
423 if ($form->{cp_id}) {
424 $where = qq| AND (cp.cp_id = ?) |;
425 push(@values, conv_i($form->{cp_id}));
428 # get rest for the customer
430 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
431 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
433 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
434 WHERE (ct.id = ?) $where
437 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
439 # remove id and taxincluded before copy back
440 delete @$ref{qw(id taxincluded)};
442 @wanted_vars = grep({ $_ } @wanted_vars);
443 if (scalar(@wanted_vars) > 0) {
445 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
446 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
449 map { $form->{$_} = $ref->{$_} } keys %$ref;
451 if ($form->{delivery_customer_id}) {
453 qq|SELECT *, notes as customernotes
457 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
459 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
462 if ($form->{delivery_vendor_id}) {
464 qq|SELECT *, notes as customernotes
468 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
470 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
473 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
475 'trans_id' => $form->{customer_id});
476 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
480 $main::lxdebug->leave_sub();
484 $main::lxdebug->enter_sub();
486 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
488 # connect to database, turn off autocommit
489 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
491 my ($query, $sth, $null, $project_id, @values);
492 my $exchangerate = 0;
494 if (!$form->{employee_id}) {
495 $form->get_employee($dbh);
498 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
500 ($null, $form->{department_id}) = split(/--/, $form->{department});
502 my $all_units = AM->retrieve_units($myconfig, $form);
504 if (!$payments_only) {
506 &reverse_invoice($dbh, $form);
509 $query = qq|SELECT nextval('glid')|;
510 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
512 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
513 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
515 if (!$form->{invnumber}) {
517 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
518 "cnnumber" : "invnumber", $dbh);
523 my ($netamount, $invoicediff) = (0, 0);
524 my ($amount, $linetotal, $lastincomeaccno);
526 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
527 my $defaultcurrency = (split m/:/, $currencies)[0];
529 if ($form->{currency} eq $defaultcurrency) {
530 $form->{exchangerate} = 1;
532 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
535 $form->{exchangerate} =
538 : $form->parse_amount($myconfig, $form->{exchangerate});
540 $form->{expense_inventory} = "";
544 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
545 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
548 foreach my $i (1 .. $form->{rowcount}) {
549 if ($form->{type} eq "credit_note") {
550 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
551 $form->{shipped} = 1;
553 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
558 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
559 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
560 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
562 if ($form->{storno}) {
563 $form->{"qty_$i"} *= -1;
566 if ($form->{"id_$i"}) {
569 if (defined($baseunits{$form->{"id_$i"}})) {
570 $item_unit = $baseunits{$form->{"id_$i"}};
573 $query = qq|SELECT unit FROM parts WHERE id = ?|;
574 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
575 $baseunits{$form->{"id_$i"}} = $item_unit;
578 if (defined($all_units->{$item_unit}->{factor})
579 && ($all_units->{$item_unit}->{factor} ne '')
580 && ($all_units->{$item_unit}->{factor} != 0)) {
581 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
585 $baseqty = $form->{"qty_$i"} * $basefactor;
587 my ($allocated, $taxrate) = (0, 0);
591 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
593 # keep entered selling price
595 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
597 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
599 my $decimalplaces = ($dec > 2) ? $dec : 2;
601 # undo discount formatting
602 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
605 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
607 # round linetotal to 2 decimal places
608 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
609 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
611 if ($form->{taxincluded}) {
612 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
613 $form->{"sellprice_$i"} =
614 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
616 $taxamount = $linetotal * $taxrate;
619 $netamount += $linetotal;
621 if ($taxamount != 0) {
623 $form->{amount}{ $form->{id} }{$_} +=
624 $taxamount * $form->{"${_}_rate"} / $taxrate
625 } split(/ /, $form->{"taxaccounts_$i"});
628 # add amount to income, $form->{amount}{trans_id}{accno}
629 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
631 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
632 $linetotal = $form->round_amount($linetotal, 2);
634 # this is the difference from the inventory
635 $invoicediff += ($amount - $linetotal);
637 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
640 $lastincomeaccno = $form->{"income_accno_$i"};
642 # adjust and round sellprice
643 $form->{"sellprice_$i"} =
644 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
647 next if $payments_only;
649 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
651 if ($form->{"assembly_$i"}) {
652 # record assembly item as allocated
653 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
656 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
660 # get pricegroup_id and save it
661 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
664 # save detail record in invoice table
666 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
667 sellprice, fxsellprice, discount, allocated, assemblyitem,
668 unit, deliverydate, project_id, serialnumber, pricegroup_id,
669 ordnumber, transdate, cusordnumber, base_qty, subtotal,
670 marge_percent, marge_total, lastcost,
671 price_factor_id, price_factor, marge_price_factor)
672 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
673 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
675 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
676 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
677 $form->{"sellprice_$i"}, $fxsellprice,
678 $form->{"discount_$i"}, $allocated, 'f',
679 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
680 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
681 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
682 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
683 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
684 $form->{"lastcost_$i"},
685 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
686 conv_i($form->{"marge_price_factor_$i"}));
687 do_query($form, $dbh, $query, @values);
689 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
691 qq|INSERT INTO licenseinvoice (trans_id, license_id)
692 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
693 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
694 do_query($form, $dbh, $query, @values);
699 $form->{datepaid} = $form->{invdate};
701 # total payments, don't move we need it here
702 for my $i (1 .. $form->{paidaccounts}) {
703 if ($form->{type} eq "credit_note") {
704 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
706 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
708 $form->{paid} += $form->{"paid_$i"};
709 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
712 my ($tax, $diff) = (0, 0);
714 $netamount = $form->round_amount($netamount, 2);
716 # figure out rounding errors for total amount vs netamount + taxes
717 if ($form->{taxincluded}) {
719 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
720 $diff += $amount - $netamount * $form->{exchangerate};
721 $netamount = $amount;
723 foreach my $item (split(/ /, $form->{taxaccounts})) {
724 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
725 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
726 $tax += $form->{amount}{ $form->{id} }{$item};
727 $netamount -= $form->{amount}{ $form->{id} }{$item};
730 $invoicediff += $diff;
731 ######## this only applies to tax included
732 if ($lastincomeaccno) {
733 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
737 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
738 $diff = $amount - $netamount * $form->{exchangerate};
739 $netamount = $amount;
740 foreach my $item (split(/ /, $form->{taxaccounts})) {
741 $form->{amount}{ $form->{id} }{$item} =
742 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
745 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
748 $amount - $form->{amount}{ $form->{id} }{$item} *
749 $form->{exchangerate};
750 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
751 $tax += $form->{amount}{ $form->{id} }{$item};
755 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
757 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
760 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
762 # update exchangerate
763 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
764 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
765 $form->{exchangerate}, 0);
768 $project_id = conv_i($form->{"globalproject_id"});
770 foreach my $trans_id (keys %{ $form->{amount} }) {
771 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
772 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
774 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
776 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
778 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
779 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
780 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
781 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
782 do_query($form, $dbh, $query, @values);
783 $form->{amount}{$trans_id}{$accno} = 0;
787 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
788 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
790 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
792 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
793 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
794 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
795 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
796 do_query($form, $dbh, $query, @values);
801 # deduct payment differences from diff
802 for my $i (1 .. $form->{paidaccounts}) {
803 if ($form->{"paid_$i"} != 0) {
805 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
806 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
810 # record payments and offsetting AR
811 if (!$form->{storno}) {
812 for my $i (1 .. $form->{paidaccounts}) {
814 next if ($form->{"paid_$i"} == 0);
816 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
817 $form->{"datepaid_$i"} = $form->{invdate}
818 unless ($form->{"datepaid_$i"});
819 $form->{datepaid} = $form->{"datepaid_$i"};
823 if ($form->{currency} eq $defaultcurrency) {
824 $form->{"exchangerate_$i"} = 1;
826 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
827 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
831 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
833 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
837 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
838 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
839 do_query($form, $dbh, $query, @values);
843 $form->{"paid_$i"} *= -1;
846 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
847 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
848 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
849 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
850 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
851 do_query($form, $dbh, $query, @values);
853 # exchangerate difference
854 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
855 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
859 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
860 $form->{"exchangerate_$i"};
862 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
865 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
871 # update exchange rate
872 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
873 $form->update_exchangerate($dbh, $form->{currency},
874 $form->{"datepaid_$i"},
875 $form->{"exchangerate_$i"}, 0);
879 } else { # if (!$form->{storno})
880 $form->{marge_total} *= -1;
883 if ($payments_only) {
884 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
885 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
887 if (!$provided_dbh) {
892 $main::lxdebug->leave_sub();
896 # record exchange rate differences and gains/losses
897 foreach my $accno (keys %{ $form->{fx} }) {
898 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
900 ($form->{fx}{$accno}{$transdate} =
901 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
906 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
907 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
908 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
909 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
910 do_query($form, $dbh, $query, @values);
915 $amount = $netamount + $tax;
918 $query = qq|UPDATE ar set
919 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
920 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
921 amount = ?, netamount = ?, paid = ?, datepaid = ?,
922 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
923 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
924 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
925 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
926 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
927 cp_id = ?, marge_total = ?, marge_percent = ?,
928 globalproject_id = ?, delivery_customer_id = ?,
929 transaction_description = ?, delivery_vendor_id = ?
931 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
932 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
933 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
934 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
935 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
936 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
937 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
938 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
939 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
940 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
941 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
942 conv_i($form->{"id"}));
943 do_query($form, $dbh, $query, @values);
945 if($form->{"formname"} eq "credit_note") {
946 for my $i (1 .. $form->{rowcount}) {
947 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
948 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
949 do_query($form, $dbh, $query, @values);
953 if ($form->{storno}) {
956 paid = paid + amount,
958 intnotes = ? || intnotes
960 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
961 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
965 $form->{name} = $form->{customer};
966 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
968 if (!$form->{shipto_id}) {
969 $form->add_shipto($dbh, $form->{id}, "AR");
972 # save printed, emailed and queued
973 $form->save_status($dbh);
975 Common::webdav_folder($form) if ($main::webdav);
977 # Link this record to the records it was created from.
978 RecordLinks->create_links('dbh' => $dbh,
980 'from_table' => 'oe',
981 'from_ids' => $form->{convert_from_oe_ids},
983 'to_id' => $form->{id},
985 delete $form->{convert_from_oe_ids};
987 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
989 if (scalar @convert_from_do_ids) {
990 DO->close_orders('dbh' => $dbh,
991 'ids' => \@convert_from_do_ids);
993 RecordLinks->create_links('dbh' => $dbh,
995 'from_table' => 'delivery_orders',
996 'from_ids' => \@convert_from_do_ids,
998 'to_id' => $form->{id},
1001 delete $form->{convert_from_do_ids};
1003 ARAP->close_orders_if_billed('dbh' => $dbh,
1004 'arap_id' => $form->{id},
1008 if (!$provided_dbh) {
1013 $main::lxdebug->leave_sub();
1018 sub _delete_payments {
1019 $main::lxdebug->enter_sub();
1021 my ($self, $form, $dbh) = @_;
1025 # Delete old payment entries from acc_trans.
1029 WHERE (trans_id = ?) AND fx_transaction
1035 LEFT JOIN chart c ON (at.chart_id = c.id)
1036 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1037 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1042 LEFT JOIN chart c ON (at.chart_id = c.id)
1043 WHERE (trans_id = ?)
1044 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1047 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1050 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1051 do_query($form, $dbh, $query);
1054 $main::lxdebug->leave_sub();
1058 $main::lxdebug->enter_sub();
1060 my ($self, $myconfig, $form, $locale) = @_;
1062 # connect to database, turn off autocommit
1063 my $dbh = $form->dbconnect_noauto($myconfig);
1065 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1067 $old_form = save_form();
1069 # Delete all entries in acc_trans from prior payments.
1070 $self->_delete_payments($form, $dbh);
1072 # Save the new payments the user made before cleaning up $form.
1073 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1075 # Clean up $form so that old content won't tamper the results.
1076 %keep_vars = map { $_, 1 } qw(login password id);
1077 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1079 # Retrieve the invoice from the database.
1080 $self->retrieve_invoice($myconfig, $form);
1082 # Set up the content of $form in the way that IS::post_invoice() expects.
1083 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1085 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1086 $item = $form->{invoice_details}->[$row - 1];
1088 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1090 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1093 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1095 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1097 # Restore the payment options from the user input.
1098 map { $form->{$_} = $payments{$_} } keys %payments;
1100 # Get the AR accno (which is normally done by Form::create_links()).
1104 LEFT JOIN chart c ON (at.chart_id = c.id)
1105 WHERE (trans_id = ?)
1106 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1110 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1112 # Post the new payments.
1113 $self->post_invoice($myconfig, $form, $dbh, 1);
1115 restore_form($old_form);
1117 my $rc = $dbh->commit();
1120 $main::lxdebug->leave_sub();
1125 sub process_assembly {
1126 $main::lxdebug->enter_sub();
1128 my ($dbh, $form, $id, $totalqty) = @_;
1131 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1132 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1134 JOIN parts p ON (a.parts_id = p.id)
1136 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1138 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1142 $ref->{inventory_accno_id} *= 1;
1143 $ref->{expense_accno_id} *= 1;
1145 # multiply by number of assemblies
1146 $ref->{qty} *= $totalqty;
1148 if ($ref->{assembly}) {
1149 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1152 if ($ref->{inventory_accno_id}) {
1153 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1157 # save detail record for individual assembly item in invoice table
1159 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1160 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1161 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1162 do_query($form, $dbh, $query, @values);
1168 $main::lxdebug->leave_sub();
1172 $main::lxdebug->enter_sub();
1174 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1175 $form->{taxzone_id} *=1;
1176 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1177 my $taxzone_id = $form->{"taxzone_id"} * 1;
1179 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1180 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1181 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1182 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1183 FROM invoice i, parts p
1184 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1185 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1186 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1187 WHERE (i.parts_id = p.id)
1188 AND (i.parts_id = ?)
1189 AND ((i.base_qty + i.allocated) < 0)
1191 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1196 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1197 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1201 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1203 # total expenses and inventory
1204 # sellprice is the cost of the item
1205 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1208 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1210 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1211 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1212 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1214 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1215 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1221 last if (($totalqty -= $qty) <= 0);
1226 $main::lxdebug->leave_sub();
1231 sub reverse_invoice {
1232 $main::lxdebug->enter_sub();
1234 my ($dbh, $form) = @_;
1236 # reverse inventory items
1238 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1240 JOIN parts p ON (i.parts_id = p.id)
1241 WHERE i.trans_id = ?|;
1242 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1244 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1246 if ($ref->{inventory_accno_id}) {
1247 # de-allocated purchases
1249 qq|SELECT i.id, i.trans_id, i.allocated
1251 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1252 ORDER BY i.trans_id DESC|;
1253 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1255 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1257 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1258 $qty = $inhref->{allocated};
1262 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1264 last if (($ref->{qty} -= $qty) <= 0);
1273 @values = (conv_i($form->{id}));
1274 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1275 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1277 if ($form->{lizenzen}) {
1279 qq|DELETE FROM licenseinvoice
1280 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1281 do_query($form, $dbh, $query, @values);
1284 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1286 $main::lxdebug->leave_sub();
1289 sub delete_invoice {
1290 $main::lxdebug->enter_sub();
1292 my ($self, $myconfig, $form, $spool) = @_;
1294 # connect to database
1295 my $dbh = $form->dbconnect_noauto($myconfig);
1297 &reverse_invoice($dbh, $form);
1299 my @values = (conv_i($form->{id}));
1302 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1304 # delete spool files
1305 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1307 # delete status entries
1308 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1310 my $rc = $dbh->commit;
1314 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1317 $main::lxdebug->leave_sub();
1322 sub retrieve_invoice {
1323 $main::lxdebug->enter_sub();
1325 my ($self, $myconfig, $form) = @_;
1327 # connect to database
1328 my $dbh = $form->dbconnect_noauto($myconfig);
1330 my ($sth, $ref, $query);
1332 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1336 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1337 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1338 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1339 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1340 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1341 d.curr AS currencies
1345 $ref = selectfirst_hashref_query($form, $dbh, $query);
1346 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1349 my $id = conv_i($form->{id});
1354 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1355 a.orddate, a.quodate, a.globalproject_id,
1356 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1357 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1358 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1359 a.employee_id, a.salesman_id, a.payment_id,
1360 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1361 a.transaction_description,
1362 a.marge_total, a.marge_percent,
1365 LEFT JOIN employee e ON (e.id = a.employee_id)
1367 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1368 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1371 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1374 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1375 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1377 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1379 foreach my $vc (qw(customer vendor)) {
1380 next if !$form->{"delivery_${vc}_id"};
1381 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1384 # get printed, emailed
1385 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1386 $sth = prepare_execute_query($form, $dbh, $query, $id);
1388 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1389 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1390 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1391 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1394 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1396 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1397 : $form->{invdate} ? $dbh->quote($form->{invdate})
1401 my $taxzone_id = $form->{taxzone_id} *= 1;
1402 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1404 # retrieve individual items
1407 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1408 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1409 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1411 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1412 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1413 i.price_factor_id, i.price_factor, i.marge_price_factor,
1414 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1415 pr.projectnumber, pg.partsgroup, prg.pricegroup
1418 LEFT JOIN parts p ON (i.parts_id = p.id)
1419 LEFT JOIN project pr ON (i.project_id = pr.id)
1420 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1421 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1423 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1424 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1425 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1427 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1429 $sth = prepare_execute_query($form, $dbh, $query, $id);
1431 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1432 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1433 delete($ref->{"part_inventory_accno_id"});
1435 foreach my $type (qw(inventory income expense)) {
1436 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1437 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1438 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1442 # get tax rates and description
1443 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1445 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1446 LEFT JOIN chart c ON (c.id = t.chart_id)
1448 (SELECT tk.tax_id FROM taxkeys tk
1449 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1450 AND startdate <= date($transdate)
1451 ORDER BY startdate DESC LIMIT 1)
1453 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1454 $ref->{taxaccounts} = "";
1456 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1458 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1462 $ref->{taxaccounts} .= "$ptr->{accno} ";
1464 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1465 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1466 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1467 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1468 $form->{taxaccounts} .= "$ptr->{accno} ";
1473 if ($form->{lizenzen}) {
1474 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1475 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1476 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1479 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1481 chop $ref->{taxaccounts};
1482 push @{ $form->{invoice_details} }, $ref;
1487 Common::webdav_folder($form) if ($main::webdav);
1490 my $rc = $dbh->commit;
1493 $main::lxdebug->leave_sub();
1499 $main::lxdebug->enter_sub();
1501 my ($self, $myconfig, $form) = @_;
1503 # connect to database
1504 my $dbh = $form->dbconnect($myconfig);
1506 my $dateformat = $myconfig->{dateformat};
1507 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1509 my (@values, $duedate, $ref, $query);
1511 if ($form->{invdate}) {
1512 $duedate = "to_date(?, '$dateformat')";
1513 push @values, $form->{invdate};
1515 $duedate = "current_date";
1518 my $cid = conv_i($form->{customer_id});
1521 if ($form->{payment_id}) {
1522 $payment_id = "(pt.id = ?) OR";
1523 push @values, conv_i($form->{payment_id});
1529 c.name AS customer, c.discount, c.creditlimit, c.terms,
1530 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1531 c.street, c.zipcode, c.city, c.country,
1532 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1533 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1534 b.discount AS tradediscount, b.description AS business
1536 LEFT JOIN business b ON (b.id = c.business_id)
1537 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1540 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1541 map { $form->{$_} = $ref->{$_} } keys %$ref;
1544 qq|SELECT sum(amount - paid) AS dunning_amount
1546 WHERE (paid < amount)
1547 AND (customer_id = ?)
1548 AND (dunning_config_id IS NOT NULL)|;
1549 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1550 map { $form->{$_} = $ref->{$_} } keys %$ref;
1553 qq|SELECT dnn.dunning_description AS max_dunning_level
1554 FROM dunning_config dnn
1555 WHERE id IN (SELECT dunning_config_id
1557 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1558 ORDER BY dunning_level DESC LIMIT 1|;
1559 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1560 map { $form->{$_} = $ref->{$_} } keys %$ref;
1562 $form->{creditremaining} = $form->{creditlimit};
1563 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1564 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1565 $form->{creditremaining} -= $value;
1569 (SELECT e.buy FROM exchangerate e
1570 WHERE e.curr = o.curr
1571 AND e.transdate = o.transdate)
1573 WHERE o.customer_id = ?
1574 AND o.quotation = '0'
1575 AND o.closed = '0'|;
1576 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1578 while (my ($amount, $exch) = $sth->fetchrow_array) {
1579 $exch = 1 unless $exch;
1580 $form->{creditremaining} -= $amount * $exch;
1584 # get shipto if we did not converted an order or invoice
1585 if (!$form->{shipto}) {
1586 map { delete $form->{$_} }
1587 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1588 shiptostreet shiptozipcode shiptocity shiptocountry
1589 shiptocontact shiptophone shiptofax shiptoemail);
1591 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1592 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1594 map { $form->{$_} = $ref->{$_} } keys %$ref;
1597 # setup last accounts used for this customer
1598 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1600 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1602 JOIN acc_trans ac ON (ac.chart_id = c.id)
1603 JOIN ar a ON (a.id = ac.trans_id)
1604 WHERE a.customer_id = ?
1605 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1606 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1607 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1610 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1611 if ($ref->{category} eq 'I') {
1613 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1615 if ($form->{initial_transdate}) {
1617 qq|SELECT tk.tax_id, t.rate
1619 LEFT JOIN tax t ON tk.tax_id = t.id
1620 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1621 ORDER BY tk.startdate DESC
1623 my ($tax_id, $rate) =
1624 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1625 $form->{initial_transdate});
1626 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1629 if ($ref->{category} eq 'A') {
1630 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1634 $form->{rowcount} = $i if ($i && !$form->{type});
1639 $main::lxdebug->leave_sub();
1643 $main::lxdebug->enter_sub();
1645 my ($self, $myconfig, $form) = @_;
1647 # connect to database
1648 my $dbh = $form->dbconnect($myconfig);
1650 my $i = $form->{rowcount};
1652 my $where = qq|NOT p.obsolete = '1'|;
1655 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1656 my ($table, $field) = split m/\./, $column;
1657 next if !$form->{"${field}_${i}"};
1658 $where .= qq| AND lower(${column}) ILIKE ?|;
1659 push @values, '%' . $form->{"${field}_${i}"} . '%';
1662 if ($form->{"description_$i"}) {
1663 $where .= qq| ORDER BY p.description|;
1665 $where .= qq| ORDER BY p.partnumber|;
1669 if ($form->{type} eq "invoice") {
1671 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1672 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1676 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1680 my $taxzone_id = $form->{taxzone_id} * 1;
1681 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1685 p.id, p.partnumber, p.description, p.sellprice,
1686 p.listprice, p.inventory_accno_id, p.lastcost,
1688 c1.accno AS inventory_accno,
1689 c1.new_chart_id AS inventory_new_chart,
1690 date($transdate) - c1.valid_from AS inventory_valid,
1692 c2.accno AS income_accno,
1693 c2.new_chart_id AS income_new_chart,
1694 date($transdate) - c2.valid_from AS income_valid,
1696 c3.accno AS expense_accno,
1697 c3.new_chart_id AS expense_new_chart,
1698 date($transdate) - c3.valid_from AS expense_valid,
1700 p.unit, p.assembly, p.bin, p.onhand,
1701 p.notes AS partnotes, p.notes AS longdescription,
1702 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1705 pfac.factor AS price_factor,
1710 LEFT JOIN chart c1 ON
1711 ((SELECT inventory_accno_id
1712 FROM buchungsgruppen
1713 WHERE id = p.buchungsgruppen_id) = c1.id)
1714 LEFT JOIN chart c2 ON
1715 ((SELECT income_accno_id_${taxzone_id}
1716 FROM buchungsgruppen
1717 WHERE id = p.buchungsgruppen_id) = c2.id)
1718 LEFT JOIN chart c3 ON
1719 ((SELECT expense_accno_id_${taxzone_id}
1720 FROM buchungsgruppen
1721 WHERE id = p.buchungsgruppen_id) = c3.id)
1722 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1723 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1725 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1727 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1729 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1730 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1731 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1732 if (!$ref->{inventory_accno_id}) {
1733 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1735 delete($ref->{inventory_accno_id});
1737 foreach my $type (qw(inventory income expense)) {
1738 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1740 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1743 ($ref->{"${type}_accno"},
1744 $ref->{"${type}_new_chart"},
1745 $ref->{"${type}_valid"})
1746 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1750 if ($form->{payment_id} eq "") {
1751 $form->{payment_id} = $form->{part_payment_id};
1754 # get tax rates and description
1755 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1757 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1759 LEFT JOIN chart c ON (c.id = t.chart_id)
1763 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1765 ORDER BY startdate DESC
1768 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1769 $stw = $dbh->prepare($query);
1770 $stw->execute(@values) || $form->dberror($query);
1772 $ref->{taxaccounts} = "";
1774 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1776 # if ($customertax{$ref->{accno}}) {
1777 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1781 $ref->{taxaccounts} .= "$ptr->{accno} ";
1783 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1784 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1785 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1786 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1787 $form->{taxaccounts} .= "$ptr->{accno} ";
1793 chop $ref->{taxaccounts};
1794 if ($form->{language_id}) {
1796 qq|SELECT tr.translation, tr.longdescription
1798 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1799 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1800 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1801 if ($translation ne "") {
1802 $ref->{description} = $translation;
1803 $ref->{longdescription} = $longdescription;
1807 qq|SELECT tr.translation, tr.longdescription
1809 WHERE tr.language_id IN
1812 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1815 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1816 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1817 if ($translation ne "") {
1818 $ref->{description} = $translation;
1819 $ref->{longdescription} = $longdescription;
1824 $ref->{onhand} *= 1;
1826 push @{ $form->{item_list} }, $ref;
1828 if ($form->{lizenzen}) {
1829 if ($ref->{inventory_accno} > 0) {
1833 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1834 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1835 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1836 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1845 $main::lxdebug->leave_sub();
1848 ##########################
1849 # get pricegroups from database
1850 # build up selected pricegroup
1851 # if an exchange rate - change price
1854 sub get_pricegroups_for_parts {
1856 $main::lxdebug->enter_sub();
1858 my ($self, $myconfig, $form) = @_;
1860 my $dbh = $form->dbconnect($myconfig);
1862 $form->{"PRICES"} = {};
1866 my $all_units = AM->retrieve_units($myconfig, $form);
1867 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1868 $form->{"PRICES"}{$i} = [];
1870 $id = $form->{"id_$i"};
1872 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1874 $id = $form->{"new_id_$i"};
1877 ($price, $selectedpricegroup_id) = split(/--/,
1878 $form->{"sellprice_pg_$i"});
1880 $pricegroup_old = $form->{"pricegroup_old_$i"};
1881 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1882 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1884 $price_new = $form->{"price_new_$i"};
1885 $price_old = $form->{"price_old_$i"};
1887 if (!$form->{"unit_old_$i"}) {
1888 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1889 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1890 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1891 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1894 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1895 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1896 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1898 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1899 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1900 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1901 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1902 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1903 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1904 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1909 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1910 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1911 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1912 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1913 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1917 if (!$form->{"basefactor_$i"}) {
1918 $form->{"basefactor_$i"} = 1;
1924 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1925 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1935 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1937 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1938 'selected' AS selected
1941 ORDER BY pricegroup|;
1942 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1943 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1945 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1947 $pkr->{selected} = '';
1949 # if there is an exchange rate change price
1950 if (($form->{exchangerate} * 1) != 0) {
1952 $pkr->{price} /= $form->{exchangerate};
1955 $pkr->{price} *= $form->{"basefactor_$i"};
1957 $pkr->{price} *= $basefactor;
1959 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1961 if ($selectedpricegroup_id eq undef) {
1962 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1964 $pkr->{selected} = ' selected';
1966 # no customer pricesgroup set
1967 if ($pkr->{price} == $pkr->{default_sellprice}) {
1969 $pkr->{price} = $form->{"sellprice_$i"};
1973 $form->{"sellprice_$i"} = $pkr->{price};
1976 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1977 $pkr->{price} = $form->{"sellprice_$i"};
1978 $pkr->{selected} = ' selected';
1982 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1983 if ($selectedpricegroup_id ne $pricegroup_old) {
1984 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1985 $pkr->{selected} = ' selected';
1987 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1988 if ($pkr->{pricegroup_id} == 0) {
1989 $pkr->{price} = $form->{"sellprice_$i"};
1990 $pkr->{selected} = ' selected';
1992 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1993 $pkr->{selected} = ' selected';
1994 if ( ($pkr->{pricegroup_id} == 0)
1995 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1996 # $pkr->{price} = $form->{"sellprice_$i"};
1998 $pkr->{price} = $form->{"sellprice_$i"};
2002 push @{ $form->{PRICES}{$i} }, $pkr;
2005 $form->{"basefactor_$i"} *= $basefactor;
2014 $main::lxdebug->leave_sub();
2018 $main::lxdebug->enter_sub();
2020 my ($self, $myconfig, $form, $table) = @_;
2022 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2024 # make sure there's no funny stuff in $table
2025 # ToDO: die when this happens and throw an error
2026 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2028 my $dbh = $form->dbconnect($myconfig);
2030 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2031 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2035 $main::lxdebug->leave_sub();
2041 $main::lxdebug->enter_sub();
2043 my ($self, $myconfig, $form, $table, $id) = @_;
2045 $main::lxdebug->leave_sub() and return 0 unless ($id);
2047 # make sure there's no funny stuff in $table
2048 # ToDO: die when this happens and throw an error
2049 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2051 my $dbh = $form->dbconnect($myconfig);
2053 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2054 my ($result) = selectrow_query($form, $dbh, $query, $id);
2058 $main::lxdebug->leave_sub();