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