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);
50 $main::lxdebug->enter_sub();
52 my ($self, $myconfig, $form, $locale) = @_;
54 $form->{duedate} ||= $form->{invdate};
57 my $dbh = $form->dbconnect($myconfig);
60 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
61 ($form->{terms}) = selectrow_query($form, $dbh, $query);
63 my (@project_ids, %projectnumbers);
65 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
67 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
70 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
71 $price_factors{$pfac->{id}} = $pfac;
73 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
76 # sort items by partsgroup
77 for $i (1 .. $form->{rowcount}) {
79 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
80 $partsgroup = $form->{"partsgroup_$i"};
82 push @partsgroup, [$i, $partsgroup];
83 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
87 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
88 join(", ", map({ "?" } @project_ids)) . ")";
89 $sth = $dbh->prepare($query);
90 $sth->execute(@project_ids) ||
91 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
92 while (my $ref = $sth->fetchrow_hashref()) {
93 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
98 $form->{"globalprojectnumber"} =
99 $projectnumbers{$form->{"globalproject_id"}};
106 my %oid = ('Pg' => 'oid',
107 'Oracle' => 'rowid');
109 # sort items by partsgroup
110 for $i (1 .. $form->{rowcount}) {
112 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
113 $partsgroup = $form->{"partsgroup_$i"};
115 push @partsgroup, [$i, $partsgroup];
128 my $nodiscount_subtotal = 0;
129 my $discount_subtotal = 0;
131 my $subtotal_header = 0;
134 $form->{discount} = [];
136 IC->prepare_parts_for_printing();
139 qw(runningnumber number description longdescription qty ship unit bin
140 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
141 partnotes serialnumber reqdate sellprice listprice netprice
142 discount p_discount discount_sub nodiscount_sub
143 linetotal nodiscount_linetotal tax_rate projectnumber
144 price_factor price_factor_name);
147 qw(taxbase tax taxdescription taxrate taxnumber);
149 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
152 if ($item->[1] ne $sameitem) {
153 push(@{ $form->{description} }, qq|$item->[1]|);
154 $sameitem = $item->[1];
156 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
159 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
161 if ($form->{"id_$i"} != 0) {
163 # add number, description and qty to $form->{number},
164 if ($form->{"subtotal_$i"} && !$subtotal_header) {
165 $subtotal_header = $i;
166 $position = int($position);
169 } elsif ($subtotal_header) {
171 $position = int($position);
172 $position = $position.".".$subposition;
174 $position = int($position);
178 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
180 push @{ $form->{runningnumber} }, $position;
181 push @{ $form->{number} }, $form->{"partnumber_$i"};
182 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
183 push @{ $form->{bin} }, $form->{"bin_$i"};
184 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
185 push @{ $form->{description} }, $form->{"description_$i"};
186 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
187 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
188 push @{ $form->{unit} }, $form->{"unit_$i"};
189 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
190 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
191 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
192 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
193 push @{ $form->{invnumber} }, $form->{"invnumber"};
194 push @{ $form->{invdate} }, $form->{"invdate"};
195 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
196 push @{ $form->{price_factor_name} }, $price_factor->{description};
198 if ($form->{lizenzen}) {
199 if ($form->{"licensenumber_$i"}) {
200 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
201 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
202 push(@{ $form->{licensenumber} }, $licensenumber);
203 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
206 push(@{ $form->{licensenumber} }, "");
207 push(@{ $form->{validuntil} }, "");
212 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
214 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
215 my ($dec) = ($sellprice =~ /\.(\d+)/);
216 my $decimalplaces = max 2, length($dec);
218 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
219 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
220 my $linetotal = $form->round_amount($linetotal_exact, 2);
221 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
223 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
224 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
226 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
228 $linetotal = ($linetotal != 0) ? $linetotal : '';
230 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
231 push @{ $form->{p_discount} }, $form->{"discount_$i"};
233 $form->{total} += $linetotal;
234 $form->{nodiscount_total} += $nodiscount_linetotal;
235 $form->{discount_total} += $discount;
237 if ($subtotal_header) {
238 $discount_subtotal += $linetotal;
239 $nodiscount_subtotal += $nodiscount_linetotal;
242 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
243 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
244 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
246 $discount_subtotal = 0;
247 $nodiscount_subtotal = 0;
248 $subtotal_header = 0;
251 push @{ $form->{discount_sub} }, "";
252 push @{ $form->{nodiscount_sub} }, "";
255 if (!$form->{"discount_$i"}) {
256 $nodiscount += $linetotal;
259 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
260 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
262 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
264 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
268 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
270 if ($form->{taxincluded}) {
273 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
274 $taxbase = $linetotal - $taxamount;
276 $taxamount = $linetotal * $taxrate;
277 $taxbase = $linetotal;
280 if ($form->round_amount($taxrate, 7) == 0) {
281 if ($form->{taxincluded}) {
282 foreach $item (@taxaccounts) {
284 $form->round_amount($linetotal * $form->{"${item}_rate"} /
285 (1 + abs($form->{"${item}_rate"})),
288 $taxaccounts{$item} += $taxamount;
289 $taxdiff += $taxamount;
291 $taxbase{$item} += $taxbase;
293 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
295 foreach $item (@taxaccounts) {
296 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
297 $taxbase{$item} += $taxbase;
301 foreach $item (@taxaccounts) {
302 $taxaccounts{$item} +=
303 $taxamount * $form->{"${item}_rate"} / $taxrate;
304 $taxbase{$item} += $taxbase;
307 $tax_rate = $taxrate * 100;
308 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
309 if ($form->{"assembly_$i"}) {
312 # get parts and push them onto the stack
314 if ($form->{groupitems}) {
316 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
318 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
322 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
324 JOIN parts p ON (a.parts_id = p.id)
325 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
326 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
327 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
329 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
330 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
331 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
332 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
333 push(@{ $form->{description} }, $sameitem);
336 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
338 push(@{ $form->{description} },
339 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
341 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
342 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
350 foreach my $item (sort keys %taxaccounts) {
351 push(@{ $form->{taxbase} },
352 $form->format_amount($myconfig, $taxbase{$item}, 2));
354 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
356 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
357 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
358 push(@{ $form->{taxrate} },
359 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
360 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
363 for my $i (1 .. $form->{paidaccounts}) {
364 if ($form->{"paid_$i"}) {
365 push(@{ $form->{payment} }, $form->{"paid_$i"});
366 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
367 push(@{ $form->{paymentaccount} }, $description);
368 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
369 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
371 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
374 if($form->{taxincluded}) {
375 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
378 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
381 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
382 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
383 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
384 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
386 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
387 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
389 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
390 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
392 $form->set_payment_options($myconfig, $form->{invdate});
394 $form->{username} = $myconfig->{name};
398 $main::lxdebug->leave_sub();
401 sub project_description {
402 $main::lxdebug->enter_sub();
404 my ($self, $dbh, $id) = @_;
406 my $query = qq|SELECT description FROM project WHERE id = ?|;
407 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
409 $main::lxdebug->leave_sub();
414 sub customer_details {
415 $main::lxdebug->enter_sub();
417 my ($self, $myconfig, $form, @wanted_vars) = @_;
419 # connect to database
420 my $dbh = $form->dbconnect($myconfig);
422 # get contact id, set it if nessessary
425 my @values = (conv_i($form->{customer_id}));
428 if ($form->{cp_id}) {
429 $where = qq| AND (cp.cp_id = ?) |;
430 push(@values, conv_i($form->{cp_id}));
433 # get rest for the customer
435 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
436 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
438 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
439 WHERE (ct.id = ?) $where
442 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
444 # remove id and taxincluded before copy back
445 delete @$ref{qw(id taxincluded)};
447 @wanted_vars = grep({ $_ } @wanted_vars);
448 if (scalar(@wanted_vars) > 0) {
450 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
451 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
454 map { $form->{$_} = $ref->{$_} } keys %$ref;
456 if ($form->{delivery_customer_id}) {
458 qq|SELECT *, notes as customernotes
462 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
464 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
467 if ($form->{delivery_vendor_id}) {
469 qq|SELECT *, notes as customernotes
473 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
475 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
478 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
480 'trans_id' => $form->{customer_id});
481 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
485 $main::lxdebug->leave_sub();
489 $main::lxdebug->enter_sub();
491 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
493 # connect to database, turn off autocommit
494 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
496 my ($query, $sth, $null, $project_id, @values);
497 my $exchangerate = 0;
499 if (!$form->{employee_id}) {
500 $form->get_employee($dbh);
503 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
505 ($null, $form->{department_id}) = split(/--/, $form->{department});
507 my $all_units = AM->retrieve_units($myconfig, $form);
509 if (!$payments_only) {
511 &reverse_invoice($dbh, $form);
514 $query = qq|SELECT nextval('glid')|;
515 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
517 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
518 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
520 if (!$form->{invnumber}) {
522 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
523 "cnnumber" : "invnumber", $dbh);
528 my ($netamount, $invoicediff) = (0, 0);
529 my ($amount, $linetotal, $lastincomeaccno);
531 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
532 my $defaultcurrency = (split m/:/, $currencies)[0];
534 if ($form->{currency} eq $defaultcurrency) {
535 $form->{exchangerate} = 1;
537 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
540 $form->{exchangerate} =
543 : $form->parse_amount($myconfig, $form->{exchangerate});
545 $form->{expense_inventory} = "";
549 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
550 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
553 foreach my $i (1 .. $form->{rowcount}) {
554 if ($form->{type} eq "credit_note") {
555 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
556 $form->{shipped} = 1;
558 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
563 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
564 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
565 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
567 if ($form->{storno}) {
568 $form->{"qty_$i"} *= -1;
571 if ($form->{"id_$i"}) {
574 if (defined($baseunits{$form->{"id_$i"}})) {
575 $item_unit = $baseunits{$form->{"id_$i"}};
578 $query = qq|SELECT unit FROM parts WHERE id = ?|;
579 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
580 $baseunits{$form->{"id_$i"}} = $item_unit;
583 if (defined($all_units->{$item_unit}->{factor})
584 && ($all_units->{$item_unit}->{factor} ne '')
585 && ($all_units->{$item_unit}->{factor} != 0)) {
586 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
590 $baseqty = $form->{"qty_$i"} * $basefactor;
592 my ($allocated, $taxrate) = (0, 0);
596 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
598 # keep entered selling price
600 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
602 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
604 my $decimalplaces = ($dec > 2) ? $dec : 2;
606 # undo discount formatting
607 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
610 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
612 # round linetotal to 2 decimal places
613 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
614 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
616 if ($form->{taxincluded}) {
617 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
618 $form->{"sellprice_$i"} =
619 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
621 $taxamount = $linetotal * $taxrate;
624 $netamount += $linetotal;
626 if ($taxamount != 0) {
628 $form->{amount}{ $form->{id} }{$_} +=
629 $taxamount * $form->{"${_}_rate"} / $taxrate
630 } split(/ /, $form->{"taxaccounts_$i"});
633 # add amount to income, $form->{amount}{trans_id}{accno}
634 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
636 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
637 $linetotal = $form->round_amount($linetotal, 2);
639 # this is the difference from the inventory
640 $invoicediff += ($amount - $linetotal);
642 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
645 $lastincomeaccno = $form->{"income_accno_$i"};
647 # adjust and round sellprice
648 $form->{"sellprice_$i"} =
649 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
652 next if $payments_only;
654 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
656 if ($form->{"assembly_$i"}) {
657 # record assembly item as allocated
658 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
661 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
665 # get pricegroup_id and save it
666 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
669 # save detail record in invoice table
671 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
672 sellprice, fxsellprice, discount, allocated, assemblyitem,
673 unit, deliverydate, project_id, serialnumber, pricegroup_id,
674 ordnumber, transdate, cusordnumber, base_qty, subtotal,
675 marge_percent, marge_total, lastcost,
676 price_factor_id, price_factor, marge_price_factor)
677 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
678 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
680 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
681 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
682 $form->{"sellprice_$i"}, $fxsellprice,
683 $form->{"discount_$i"}, $allocated, 'f',
684 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
685 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
686 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
687 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
688 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
689 $form->{"lastcost_$i"},
690 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
691 conv_i($form->{"marge_price_factor_$i"}));
692 do_query($form, $dbh, $query, @values);
694 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
696 qq|INSERT INTO licenseinvoice (trans_id, license_id)
697 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
698 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
699 do_query($form, $dbh, $query, @values);
704 $form->{datepaid} = $form->{invdate};
706 # total payments, don't move we need it here
707 for my $i (1 .. $form->{paidaccounts}) {
708 if ($form->{type} eq "credit_note") {
709 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
711 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
713 $form->{paid} += $form->{"paid_$i"};
714 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
717 my ($tax, $diff) = (0, 0);
719 $netamount = $form->round_amount($netamount, 2);
721 # figure out rounding errors for total amount vs netamount + taxes
722 if ($form->{taxincluded}) {
724 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
725 $diff += $amount - $netamount * $form->{exchangerate};
726 $netamount = $amount;
728 foreach my $item (split(/ /, $form->{taxaccounts})) {
729 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
730 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
731 $tax += $form->{amount}{ $form->{id} }{$item};
732 $netamount -= $form->{amount}{ $form->{id} }{$item};
735 $invoicediff += $diff;
736 ######## this only applies to tax included
737 if ($lastincomeaccno) {
738 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
742 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
743 $diff = $amount - $netamount * $form->{exchangerate};
744 $netamount = $amount;
745 foreach my $item (split(/ /, $form->{taxaccounts})) {
746 $form->{amount}{ $form->{id} }{$item} =
747 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
750 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
753 $amount - $form->{amount}{ $form->{id} }{$item} *
754 $form->{exchangerate};
755 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
756 $tax += $form->{amount}{ $form->{id} }{$item};
760 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
762 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
765 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
767 # update exchangerate
768 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
769 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
770 $form->{exchangerate}, 0);
773 $project_id = conv_i($form->{"globalproject_id"});
775 foreach my $trans_id (keys %{ $form->{amount} }) {
776 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
777 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
779 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
781 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
783 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
784 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
785 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
786 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
787 do_query($form, $dbh, $query, @values);
788 $form->{amount}{$trans_id}{$accno} = 0;
792 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
793 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
795 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
797 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
798 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
799 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
800 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
801 do_query($form, $dbh, $query, @values);
806 # deduct payment differences from diff
807 for my $i (1 .. $form->{paidaccounts}) {
808 if ($form->{"paid_$i"} != 0) {
810 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
811 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
815 # record payments and offsetting AR
816 if (!$form->{storno}) {
817 for my $i (1 .. $form->{paidaccounts}) {
819 next if ($form->{"paid_$i"} == 0);
821 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
822 $form->{"datepaid_$i"} = $form->{invdate}
823 unless ($form->{"datepaid_$i"});
824 $form->{datepaid} = $form->{"datepaid_$i"};
828 if ($form->{currency} eq $defaultcurrency) {
829 $form->{"exchangerate_$i"} = 1;
831 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
832 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
836 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
838 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
840 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
841 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
842 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
843 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
844 do_query($form, $dbh, $query, @values);
848 $form->{"paid_$i"} *= -1;
851 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, 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"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
855 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
856 do_query($form, $dbh, $query, @values);
858 # exchangerate difference
859 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
860 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
864 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
865 $form->{"exchangerate_$i"};
867 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
870 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
876 # update exchange rate
877 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
878 $form->update_exchangerate($dbh, $form->{currency},
879 $form->{"datepaid_$i"},
880 $form->{"exchangerate_$i"}, 0);
884 } else { # if (!$form->{storno})
885 $form->{marge_total} *= -1;
888 if ($payments_only) {
889 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
890 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
892 if (!$provided_dbh) {
897 $main::lxdebug->leave_sub();
901 # record exchange rate differences and gains/losses
902 foreach my $accno (keys %{ $form->{fx} }) {
903 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
905 ($form->{fx}{$accno}{$transdate} =
906 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
911 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
912 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
913 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
914 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
915 do_query($form, $dbh, $query, @values);
920 $amount = $netamount + $tax;
923 $query = qq|UPDATE ar set
924 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
925 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
926 amount = ?, netamount = ?, paid = ?, datepaid = ?,
927 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
928 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
929 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
930 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
931 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
932 cp_id = ?, marge_total = ?, marge_percent = ?,
933 globalproject_id = ?, delivery_customer_id = ?,
934 transaction_description = ?, delivery_vendor_id = ?
936 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
937 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
938 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
939 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
940 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
941 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
942 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
943 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
944 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
945 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
946 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
947 conv_i($form->{"id"}));
948 do_query($form, $dbh, $query, @values);
950 if($form->{"formname"} eq "credit_note") {
951 for my $i (1 .. $form->{rowcount}) {
952 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
953 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
954 do_query($form, $dbh, $query, @values);
958 if ($form->{storno}) {
961 paid = paid + amount,
963 intnotes = ? || intnotes
965 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
966 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
970 $form->{name} = $form->{customer};
971 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
973 if (!$form->{shipto_id}) {
974 $form->add_shipto($dbh, $form->{id}, "AR");
977 # save printed, emailed and queued
978 $form->save_status($dbh);
980 Common::webdav_folder($form) if ($main::webdav);
982 # Link this record to the records it was created from.
983 RecordLinks->create_links('dbh' => $dbh,
985 'from_table' => 'oe',
986 'from_ids' => $form->{convert_from_oe_ids},
988 'to_id' => $form->{id},
990 delete $form->{convert_from_oe_ids};
992 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
994 if (scalar @convert_from_do_ids) {
995 DO->close_orders('dbh' => $dbh,
996 'ids' => \@convert_from_do_ids);
998 RecordLinks->create_links('dbh' => $dbh,
1000 'from_table' => 'delivery_orders',
1001 'from_ids' => \@convert_from_do_ids,
1003 'to_id' => $form->{id},
1006 delete $form->{convert_from_do_ids};
1008 ARAP->close_orders_if_billed('dbh' => $dbh,
1009 'arap_id' => $form->{id},
1013 if (!$provided_dbh) {
1018 $main::lxdebug->leave_sub();
1023 sub _delete_payments {
1024 $main::lxdebug->enter_sub();
1026 my ($self, $form, $dbh) = @_;
1030 # Delete old payment entries from acc_trans.
1034 WHERE (trans_id = ?) AND fx_transaction
1040 LEFT JOIN chart c ON (at.chart_id = c.id)
1041 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1042 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1047 LEFT JOIN chart c ON (at.chart_id = c.id)
1048 WHERE (trans_id = ?)
1049 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1052 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1055 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1056 do_query($form, $dbh, $query);
1059 $main::lxdebug->leave_sub();
1063 $main::lxdebug->enter_sub();
1065 my ($self, $myconfig, $form, $locale) = @_;
1067 # connect to database, turn off autocommit
1068 my $dbh = $form->dbconnect_noauto($myconfig);
1070 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1072 $old_form = save_form();
1074 # Delete all entries in acc_trans from prior payments.
1075 $self->_delete_payments($form, $dbh);
1077 # Save the new payments the user made before cleaning up $form.
1078 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1080 # Clean up $form so that old content won't tamper the results.
1081 %keep_vars = map { $_, 1 } qw(login password id);
1082 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1084 # Retrieve the invoice from the database.
1085 $self->retrieve_invoice($myconfig, $form);
1087 # Set up the content of $form in the way that IS::post_invoice() expects.
1088 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1090 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1091 $item = $form->{invoice_details}->[$row - 1];
1093 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1095 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1098 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1100 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1102 # Restore the payment options from the user input.
1103 map { $form->{$_} = $payments{$_} } keys %payments;
1105 # Get the AR accno (which is normally done by Form::create_links()).
1109 LEFT JOIN chart c ON (at.chart_id = c.id)
1110 WHERE (trans_id = ?)
1111 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1115 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1117 # Post the new payments.
1118 $self->post_invoice($myconfig, $form, $dbh, 1);
1120 restore_form($old_form);
1122 my $rc = $dbh->commit();
1125 $main::lxdebug->leave_sub();
1130 sub process_assembly {
1131 $main::lxdebug->enter_sub();
1133 my ($dbh, $form, $id, $totalqty) = @_;
1136 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1137 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1139 JOIN parts p ON (a.parts_id = p.id)
1141 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1143 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1147 $ref->{inventory_accno_id} *= 1;
1148 $ref->{expense_accno_id} *= 1;
1150 # multiply by number of assemblies
1151 $ref->{qty} *= $totalqty;
1153 if ($ref->{assembly}) {
1154 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1157 if ($ref->{inventory_accno_id}) {
1158 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1162 # save detail record for individual assembly item in invoice table
1164 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1165 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1166 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1167 do_query($form, $dbh, $query, @values);
1173 $main::lxdebug->leave_sub();
1177 $main::lxdebug->enter_sub();
1179 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1180 $form->{taxzone_id} *=1;
1181 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1182 my $taxzone_id = $form->{"taxzone_id"} * 1;
1184 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1185 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1186 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1187 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1188 FROM invoice i, parts p
1189 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1190 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1191 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1192 WHERE (i.parts_id = p.id)
1193 AND (i.parts_id = ?)
1194 AND ((i.base_qty + i.allocated) < 0)
1196 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1201 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1202 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1206 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1208 # total expenses and inventory
1209 # sellprice is the cost of the item
1210 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1213 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1215 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1216 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1217 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1219 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1220 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1226 last if (($totalqty -= $qty) <= 0);
1231 $main::lxdebug->leave_sub();
1236 sub reverse_invoice {
1237 $main::lxdebug->enter_sub();
1239 my ($dbh, $form) = @_;
1241 # reverse inventory items
1243 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1245 JOIN parts p ON (i.parts_id = p.id)
1246 WHERE i.trans_id = ?|;
1247 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1249 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1251 if ($ref->{inventory_accno_id}) {
1252 # de-allocated purchases
1254 qq|SELECT i.id, i.trans_id, i.allocated
1256 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1257 ORDER BY i.trans_id DESC|;
1258 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1260 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1262 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1263 $qty = $inhref->{allocated};
1267 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1269 last if (($ref->{qty} -= $qty) <= 0);
1278 @values = (conv_i($form->{id}));
1279 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1280 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1282 if ($form->{lizenzen}) {
1284 qq|DELETE FROM licenseinvoice
1285 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1286 do_query($form, $dbh, $query, @values);
1289 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1291 $main::lxdebug->leave_sub();
1294 sub delete_invoice {
1295 $main::lxdebug->enter_sub();
1297 my ($self, $myconfig, $form, $spool) = @_;
1299 # connect to database
1300 my $dbh = $form->dbconnect_noauto($myconfig);
1302 &reverse_invoice($dbh, $form);
1304 my @values = (conv_i($form->{id}));
1307 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1309 # delete spool files
1310 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1312 # delete status entries
1313 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1315 my $rc = $dbh->commit;
1319 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1322 $main::lxdebug->leave_sub();
1327 sub retrieve_invoice {
1328 $main::lxdebug->enter_sub();
1330 my ($self, $myconfig, $form) = @_;
1332 # connect to database
1333 my $dbh = $form->dbconnect_noauto($myconfig);
1335 my ($sth, $ref, $query);
1337 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1341 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1342 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1343 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1344 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1345 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1346 d.curr AS currencies
1350 $ref = selectfirst_hashref_query($form, $dbh, $query);
1351 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1354 my $id = conv_i($form->{id});
1359 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1360 a.orddate, a.quodate, a.globalproject_id,
1361 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1362 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1363 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1364 a.employee_id, a.salesman_id, a.payment_id,
1365 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1366 a.transaction_description,
1367 a.marge_total, a.marge_percent,
1370 LEFT JOIN employee e ON (e.id = a.employee_id)
1372 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1373 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1376 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1379 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1380 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1382 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1384 foreach my $vc (qw(customer vendor)) {
1385 next if !$form->{"delivery_${vc}_id"};
1386 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1389 # get printed, emailed
1390 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1391 $sth = prepare_execute_query($form, $dbh, $query, $id);
1393 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1394 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1395 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1396 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1399 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1401 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1402 : $form->{invdate} ? $dbh->quote($form->{invdate})
1406 my $taxzone_id = $form->{taxzone_id} *= 1;
1407 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1409 # retrieve individual items
1412 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1413 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1414 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1416 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1417 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1418 i.price_factor_id, i.price_factor, i.marge_price_factor,
1419 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1420 pr.projectnumber, pg.partsgroup, prg.pricegroup
1423 LEFT JOIN parts p ON (i.parts_id = p.id)
1424 LEFT JOIN project pr ON (i.project_id = pr.id)
1425 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1426 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1428 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1429 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1430 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1432 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1434 $sth = prepare_execute_query($form, $dbh, $query, $id);
1436 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1437 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1438 delete($ref->{"part_inventory_accno_id"});
1440 foreach my $type (qw(inventory income expense)) {
1441 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1442 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1443 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1447 # get tax rates and description
1448 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1450 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1451 LEFT JOIN chart c ON (c.id = t.chart_id)
1453 (SELECT tk.tax_id FROM taxkeys tk
1454 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1455 AND startdate <= date($transdate)
1456 ORDER BY startdate DESC LIMIT 1)
1458 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1459 $ref->{taxaccounts} = "";
1461 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1463 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1467 $ref->{taxaccounts} .= "$ptr->{accno} ";
1469 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1470 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1471 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1472 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1473 $form->{taxaccounts} .= "$ptr->{accno} ";
1478 if ($form->{lizenzen}) {
1479 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1480 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1481 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1484 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1486 chop $ref->{taxaccounts};
1487 push @{ $form->{invoice_details} }, $ref;
1492 Common::webdav_folder($form) if ($main::webdav);
1495 my $rc = $dbh->commit;
1498 $main::lxdebug->leave_sub();
1504 $main::lxdebug->enter_sub();
1506 my ($self, $myconfig, $form) = @_;
1508 # connect to database
1509 my $dbh = $form->dbconnect($myconfig);
1511 my $dateformat = $myconfig->{dateformat};
1512 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1514 my (@values, $duedate, $ref, $query);
1516 if ($form->{invdate}) {
1517 $duedate = "to_date(?, '$dateformat')";
1518 push @values, $form->{invdate};
1520 $duedate = "current_date";
1523 my $cid = conv_i($form->{customer_id});
1526 if ($form->{payment_id}) {
1527 $payment_id = "(pt.id = ?) OR";
1528 push @values, conv_i($form->{payment_id});
1534 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1535 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1536 c.street, c.zipcode, c.city, c.country,
1537 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1538 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1539 b.discount AS tradediscount, b.description AS business
1541 LEFT JOIN business b ON (b.id = c.business_id)
1542 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1545 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1546 map { $form->{$_} = $ref->{$_} } keys %$ref;
1549 qq|SELECT sum(amount - paid) AS dunning_amount
1551 WHERE (paid < amount)
1552 AND (customer_id = ?)
1553 AND (dunning_config_id IS NOT NULL)|;
1554 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1555 map { $form->{$_} = $ref->{$_} } keys %$ref;
1558 qq|SELECT dnn.dunning_description AS max_dunning_level
1559 FROM dunning_config dnn
1560 WHERE id IN (SELECT dunning_config_id
1562 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1563 ORDER BY dunning_level DESC LIMIT 1|;
1564 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1565 map { $form->{$_} = $ref->{$_} } keys %$ref;
1567 $form->{creditremaining} = $form->{creditlimit};
1568 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1569 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1570 $form->{creditremaining} -= $value;
1574 (SELECT e.buy FROM exchangerate e
1575 WHERE e.curr = o.curr
1576 AND e.transdate = o.transdate)
1578 WHERE o.customer_id = ?
1579 AND o.quotation = '0'
1580 AND o.closed = '0'|;
1581 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1583 while (my ($amount, $exch) = $sth->fetchrow_array) {
1584 $exch = 1 unless $exch;
1585 $form->{creditremaining} -= $amount * $exch;
1589 # get shipto if we did not converted an order or invoice
1590 if (!$form->{shipto}) {
1591 map { delete $form->{$_} }
1592 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1593 shiptostreet shiptozipcode shiptocity shiptocountry
1594 shiptocontact shiptophone shiptofax shiptoemail);
1596 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1597 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1599 map { $form->{$_} = $ref->{$_} } keys %$ref;
1602 # setup last accounts used for this customer
1603 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1605 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1607 JOIN acc_trans ac ON (ac.chart_id = c.id)
1608 JOIN ar a ON (a.id = ac.trans_id)
1609 WHERE a.customer_id = ?
1610 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1611 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1612 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1615 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1616 if ($ref->{category} eq 'I') {
1618 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1620 if ($form->{initial_transdate}) {
1622 qq|SELECT tk.tax_id, t.rate
1624 LEFT JOIN tax t ON tk.tax_id = t.id
1625 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1626 ORDER BY tk.startdate DESC
1628 my ($tax_id, $rate) =
1629 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1630 $form->{initial_transdate});
1631 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1634 if ($ref->{category} eq 'A') {
1635 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1639 $form->{rowcount} = $i if ($i && !$form->{type});
1644 $main::lxdebug->leave_sub();
1648 $main::lxdebug->enter_sub();
1650 my ($self, $myconfig, $form) = @_;
1652 # connect to database
1653 my $dbh = $form->dbconnect($myconfig);
1655 my $i = $form->{rowcount};
1657 my $where = qq|NOT p.obsolete = '1'|;
1660 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1661 my ($table, $field) = split m/\./, $column;
1662 next if !$form->{"${field}_${i}"};
1663 $where .= qq| AND lower(${column}) ILIKE ?|;
1664 push @values, '%' . $form->{"${field}_${i}"} . '%';
1667 if ($form->{"description_$i"}) {
1668 $where .= qq| ORDER BY p.description|;
1670 $where .= qq| ORDER BY p.partnumber|;
1674 if ($form->{type} eq "invoice") {
1676 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1677 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1681 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1685 my $taxzone_id = $form->{taxzone_id} * 1;
1686 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1690 p.id, p.partnumber, p.description, p.sellprice,
1691 p.listprice, p.inventory_accno_id, p.lastcost,
1693 c1.accno AS inventory_accno,
1694 c1.new_chart_id AS inventory_new_chart,
1695 date($transdate) - c1.valid_from AS inventory_valid,
1697 c2.accno AS income_accno,
1698 c2.new_chart_id AS income_new_chart,
1699 date($transdate) - c2.valid_from AS income_valid,
1701 c3.accno AS expense_accno,
1702 c3.new_chart_id AS expense_new_chart,
1703 date($transdate) - c3.valid_from AS expense_valid,
1705 p.unit, p.assembly, p.bin, p.onhand,
1706 p.notes AS partnotes, p.notes AS longdescription,
1707 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1710 pfac.factor AS price_factor,
1715 LEFT JOIN chart c1 ON
1716 ((SELECT inventory_accno_id
1717 FROM buchungsgruppen
1718 WHERE id = p.buchungsgruppen_id) = c1.id)
1719 LEFT JOIN chart c2 ON
1720 ((SELECT income_accno_id_${taxzone_id}
1721 FROM buchungsgruppen
1722 WHERE id = p.buchungsgruppen_id) = c2.id)
1723 LEFT JOIN chart c3 ON
1724 ((SELECT expense_accno_id_${taxzone_id}
1725 FROM buchungsgruppen
1726 WHERE id = p.buchungsgruppen_id) = c3.id)
1727 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1728 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1730 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1732 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1734 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1735 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1736 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1737 if (!$ref->{inventory_accno_id}) {
1738 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1740 delete($ref->{inventory_accno_id});
1742 foreach my $type (qw(inventory income expense)) {
1743 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1745 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1748 ($ref->{"${type}_accno"},
1749 $ref->{"${type}_new_chart"},
1750 $ref->{"${type}_valid"})
1751 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1755 if ($form->{payment_id} eq "") {
1756 $form->{payment_id} = $form->{part_payment_id};
1759 # get tax rates and description
1760 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1762 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1764 LEFT JOIN chart c ON (c.id = t.chart_id)
1768 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1770 ORDER BY startdate DESC
1773 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1774 $stw = $dbh->prepare($query);
1775 $stw->execute(@values) || $form->dberror($query);
1777 $ref->{taxaccounts} = "";
1779 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1781 # if ($customertax{$ref->{accno}}) {
1782 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1786 $ref->{taxaccounts} .= "$ptr->{accno} ";
1788 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1789 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1790 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1791 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1792 $form->{taxaccounts} .= "$ptr->{accno} ";
1798 chop $ref->{taxaccounts};
1799 if ($form->{language_id}) {
1801 qq|SELECT tr.translation, tr.longdescription
1803 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1804 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1805 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1806 if ($translation ne "") {
1807 $ref->{description} = $translation;
1808 $ref->{longdescription} = $longdescription;
1812 qq|SELECT tr.translation, tr.longdescription
1814 WHERE tr.language_id IN
1817 WHERE article_code = (SELECT article_code FROM language WHERE 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;
1829 $ref->{onhand} *= 1;
1831 push @{ $form->{item_list} }, $ref;
1833 if ($form->{lizenzen}) {
1834 if ($ref->{inventory_accno} > 0) {
1838 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1839 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1840 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1841 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1850 $main::lxdebug->leave_sub();
1853 ##########################
1854 # get pricegroups from database
1855 # build up selected pricegroup
1856 # if an exchange rate - change price
1859 sub get_pricegroups_for_parts {
1861 $main::lxdebug->enter_sub();
1863 my ($self, $myconfig, $form) = @_;
1865 my $dbh = $form->dbconnect($myconfig);
1867 $form->{"PRICES"} = {};
1871 my $all_units = AM->retrieve_units($myconfig, $form);
1872 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1873 $form->{"PRICES"}{$i} = [];
1875 $id = $form->{"id_$i"};
1877 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1879 $id = $form->{"new_id_$i"};
1882 ($price, $selectedpricegroup_id) = split(/--/,
1883 $form->{"sellprice_pg_$i"});
1885 $pricegroup_old = $form->{"pricegroup_old_$i"};
1886 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1887 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1889 $price_new = $form->{"price_new_$i"};
1890 $price_old = $form->{"price_old_$i"};
1892 if (!$form->{"unit_old_$i"}) {
1893 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1894 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1895 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1896 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1899 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1900 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1901 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1903 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1904 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1905 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1906 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1907 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1908 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1909 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1914 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1915 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1916 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1917 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1918 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1922 if (!$form->{"basefactor_$i"}) {
1923 $form->{"basefactor_$i"} = 1;
1929 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1930 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1940 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1942 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1943 'selected' AS selected
1946 ORDER BY pricegroup|;
1947 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1948 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1950 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1952 $pkr->{selected} = '';
1954 # if there is an exchange rate change price
1955 if (($form->{exchangerate} * 1) != 0) {
1957 $pkr->{price} /= $form->{exchangerate};
1960 $pkr->{price} *= $form->{"basefactor_$i"};
1962 $pkr->{price} *= $basefactor;
1964 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1966 if ($selectedpricegroup_id eq undef) {
1967 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1969 $pkr->{selected} = ' selected';
1971 # no customer pricesgroup set
1972 if ($pkr->{price} == $pkr->{default_sellprice}) {
1974 $pkr->{price} = $form->{"sellprice_$i"};
1978 $form->{"sellprice_$i"} = $pkr->{price};
1981 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1982 $pkr->{price} = $form->{"sellprice_$i"};
1983 $pkr->{selected} = ' selected';
1987 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1988 if ($selectedpricegroup_id ne $pricegroup_old) {
1989 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1990 $pkr->{selected} = ' selected';
1992 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1993 if ($pkr->{pricegroup_id} == 0) {
1994 $pkr->{price} = $form->{"sellprice_$i"};
1995 $pkr->{selected} = ' selected';
1997 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1998 $pkr->{selected} = ' selected';
1999 if ( ($pkr->{pricegroup_id} == 0)
2000 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2001 # $pkr->{price} = $form->{"sellprice_$i"};
2003 $pkr->{price} = $form->{"sellprice_$i"};
2007 push @{ $form->{PRICES}{$i} }, $pkr;
2010 $form->{"basefactor_$i"} *= $basefactor;
2019 $main::lxdebug->leave_sub();
2023 $main::lxdebug->enter_sub();
2025 my ($self, $myconfig, $form, $table) = @_;
2027 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2029 # make sure there's no funny stuff in $table
2030 # ToDO: die when this happens and throw an error
2031 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2033 my $dbh = $form->dbconnect($myconfig);
2035 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2036 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2040 $main::lxdebug->leave_sub();
2046 $main::lxdebug->enter_sub();
2048 my ($self, $myconfig, $form, $table, $id) = @_;
2050 $main::lxdebug->leave_sub() and return 0 unless ($id);
2052 # make sure there's no funny stuff in $table
2053 # ToDO: die when this happens and throw an error
2054 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2056 my $dbh = $form->dbconnect($myconfig);
2058 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2059 my ($result) = selectrow_query($form, $dbh, $query, $id);
2063 $main::lxdebug->leave_sub();