1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
49 $main::lxdebug->enter_sub();
51 my ($self, $myconfig, $form, $locale) = @_;
53 $form->{duedate} ||= $form->{invdate};
56 my $dbh = $form->dbconnect($myconfig);
59 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
60 ($form->{terms}) = selectrow_query($form, $dbh, $query);
62 my (@project_ids, %projectnumbers);
64 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
66 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
69 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
70 $price_factors{$pfac->{id}} = $pfac;
72 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
75 # sort items by partsgroup
76 for $i (1 .. $form->{rowcount}) {
78 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
79 $partsgroup = $form->{"partsgroup_$i"};
81 push @partsgroup, [$i, $partsgroup];
82 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
86 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
87 join(", ", map({ "?" } @project_ids)) . ")";
88 $sth = $dbh->prepare($query);
89 $sth->execute(@project_ids) ||
90 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
91 while (my $ref = $sth->fetchrow_hashref()) {
92 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
97 $form->{"globalprojectnumber"} =
98 $projectnumbers{$form->{"globalproject_id"}};
105 my %oid = ('Pg' => 'oid',
106 'Oracle' => 'rowid');
108 # sort items by partsgroup
109 for $i (1 .. $form->{rowcount}) {
111 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
112 $partsgroup = $form->{"partsgroup_$i"};
114 push @partsgroup, [$i, $partsgroup];
127 my $nodiscount_subtotal = 0;
128 my $discount_subtotal = 0;
130 my $subtotal_header = 0;
134 qw(runningnumber number description longdescription qty ship unit bin
135 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
136 partnotes serialnumber reqdate sellprice listprice netprice
137 discount p_discount discount_sub nodiscount_sub
138 linetotal nodiscount_linetotal tax_rate projectnumber
139 price_factor price_factor_name);
142 qw(taxbase tax taxdescription taxrate taxnumber);
144 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
147 if ($item->[1] ne $sameitem) {
148 push(@{ $form->{description} }, qq|$item->[1]|);
149 $sameitem = $item->[1];
151 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
154 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
156 if ($form->{"id_$i"} != 0) {
158 # add number, description and qty to $form->{number},
159 if ($form->{"subtotal_$i"} && !$subtotal_header) {
160 $subtotal_header = $i;
161 $position = int($position);
164 } elsif ($subtotal_header) {
166 $position = int($position);
167 $position = $position.".".$subposition;
169 $position = int($position);
173 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
175 push @{ $form->{runningnumber} }, $position;
176 push @{ $form->{number} }, $form->{"partnumber_$i"};
177 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
178 push @{ $form->{bin} }, $form->{"bin_$i"};
179 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
180 push @{ $form->{description} }, $form->{"description_$i"};
181 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
182 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
183 push @{ $form->{unit} }, $form->{"unit_$i"};
184 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
185 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
186 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
187 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
188 push @{ $form->{invnumber} }, $form->{"invnumber"};
189 push @{ $form->{invdate} }, $form->{"invdate"};
190 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
191 push @{ $form->{price_factor_name} }, $price_factor->{description};
193 if ($form->{lizenzen}) {
194 if ($form->{"licensenumber_$i"}) {
195 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
196 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
197 push(@{ $form->{licensenumber} }, $licensenumber);
198 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
201 push(@{ $form->{licensenumber} }, "");
202 push(@{ $form->{validuntil} }, "");
207 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
209 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
210 my ($dec) = ($sellprice =~ /\.(\d+)/);
211 my $decimalplaces = max 2, length($dec);
213 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
214 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
215 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
216 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
218 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
220 $linetotal = ($linetotal != 0) ? $linetotal : '';
222 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
223 push @{ $form->{p_discount} }, $form->{"discount_$i"};
225 $form->{total} += $linetotal;
226 $form->{nodiscount_total} += $nodiscount_linetotal;
227 $form->{discount_total} += $discount;
229 if ($subtotal_header) {
230 $discount_subtotal += $linetotal;
231 $nodiscount_subtotal += $nodiscount_linetotal;
234 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
235 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
236 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
238 $discount_subtotal = 0;
239 $nodiscount_subtotal = 0;
240 $subtotal_header = 0;
243 push @{ $form->{discount_sub} }, "";
244 push @{ $form->{nodiscount_sub} }, "";
247 if (!$form->{"discount_$i"}) {
248 $nodiscount += $linetotal;
251 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
252 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
254 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
256 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
260 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
262 if ($form->{taxincluded}) {
265 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
266 $taxbase = $linetotal - $taxamount;
268 $taxamount = $linetotal * $taxrate;
269 $taxbase = $linetotal;
272 if ($form->round_amount($taxrate, 7) == 0) {
273 if ($form->{taxincluded}) {
274 foreach $item (@taxaccounts) {
276 $form->round_amount($linetotal * $form->{"${item}_rate"} /
277 (1 + abs($form->{"${item}_rate"})),
280 $taxaccounts{$item} += $taxamount;
281 $taxdiff += $taxamount;
283 $taxbase{$item} += $taxbase;
285 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
287 foreach $item (@taxaccounts) {
288 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
289 $taxbase{$item} += $taxbase;
293 foreach $item (@taxaccounts) {
294 $taxaccounts{$item} +=
295 $taxamount * $form->{"${item}_rate"} / $taxrate;
296 $taxbase{$item} += $taxbase;
299 $tax_rate = $taxrate * 100;
300 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
301 if ($form->{"assembly_$i"}) {
304 # get parts and push them onto the stack
306 if ($form->{groupitems}) {
308 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
310 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
314 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
316 JOIN parts p ON (a.parts_id = p.id)
317 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
318 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
319 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
321 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
322 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
323 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
324 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
325 push(@{ $form->{description} }, $sameitem);
328 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
330 push(@{ $form->{description} },
331 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
333 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
334 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
342 foreach my $item (sort keys %taxaccounts) {
343 push(@{ $form->{taxbase} },
344 $form->format_amount($myconfig, $taxbase{$item}, 2));
346 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
348 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
349 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
350 push(@{ $form->{taxrate} },
351 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
352 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
355 for my $i (1 .. $form->{paidaccounts}) {
356 if ($form->{"paid_$i"}) {
357 push(@{ $form->{payment} }, $form->{"paid_$i"});
358 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
359 push(@{ $form->{paymentaccount} }, $description);
360 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
361 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
363 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
366 if($form->{taxincluded}) {
367 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
370 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
373 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
374 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
375 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
376 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
378 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
379 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
381 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
382 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
384 $form->set_payment_options($myconfig, $form->{invdate});
386 $form->{username} = $myconfig->{name};
390 $main::lxdebug->leave_sub();
393 sub project_description {
394 $main::lxdebug->enter_sub();
396 my ($self, $dbh, $id) = @_;
398 my $query = qq|SELECT description FROM project WHERE id = ?|;
399 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
401 $main::lxdebug->leave_sub();
406 sub customer_details {
407 $main::lxdebug->enter_sub();
409 my ($self, $myconfig, $form, @wanted_vars) = @_;
411 # connect to database
412 my $dbh = $form->dbconnect($myconfig);
414 # get contact id, set it if nessessary
417 my @values = (conv_i($form->{customer_id}));
420 if ($form->{cp_id}) {
421 $where = qq| AND (cp.cp_id = ?) |;
422 push(@values, conv_i($form->{cp_id}));
425 # get rest for the customer
427 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
428 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
430 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
431 WHERE (ct.id = ?) $where
434 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
436 # remove id and taxincluded before copy back
437 delete @$ref{qw(id taxincluded)};
439 @wanted_vars = grep({ $_ } @wanted_vars);
440 if (scalar(@wanted_vars) > 0) {
442 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
443 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
446 map { $form->{$_} = $ref->{$_} } keys %$ref;
448 if ($form->{delivery_customer_id}) {
450 qq|SELECT *, notes as customernotes
454 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
456 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
459 if ($form->{delivery_vendor_id}) {
461 qq|SELECT *, notes as customernotes
465 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
467 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
470 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
472 'trans_id' => $form->{customer_id});
473 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
477 $main::lxdebug->leave_sub();
481 $main::lxdebug->enter_sub();
483 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
485 # connect to database, turn off autocommit
486 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
488 my ($query, $sth, $null, $project_id, @values);
489 my $exchangerate = 0;
491 if (!$form->{employee_id}) {
492 $form->get_employee($dbh);
495 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
497 ($null, $form->{department_id}) = split(/--/, $form->{department});
499 my $all_units = AM->retrieve_units($myconfig, $form);
501 if (!$payments_only) {
503 &reverse_invoice($dbh, $form);
506 $query = qq|SELECT nextval('glid')|;
507 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
509 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
510 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
512 if (!$form->{invnumber}) {
514 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
515 "cnnumber" : "invnumber", $dbh);
520 my ($netamount, $invoicediff) = (0, 0);
521 my ($amount, $linetotal, $lastincomeaccno);
523 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
524 my $defaultcurrency = (split m/:/, $currencies)[0];
526 if ($form->{currency} eq $defaultcurrency) {
527 $form->{exchangerate} = 1;
529 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
532 $form->{exchangerate} =
535 : $form->parse_amount($myconfig, $form->{exchangerate});
537 $form->{expense_inventory} = "";
541 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
542 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
545 foreach my $i (1 .. $form->{rowcount}) {
546 if ($form->{type} eq "credit_note") {
547 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
548 $form->{shipped} = 1;
550 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
555 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
556 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
557 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
559 if ($form->{storno}) {
560 $form->{"qty_$i"} *= -1;
563 if ($form->{"id_$i"}) {
566 if (defined($baseunits{$form->{"id_$i"}})) {
567 $item_unit = $baseunits{$form->{"id_$i"}};
570 $query = qq|SELECT unit FROM parts WHERE id = ?|;
571 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
572 $baseunits{$form->{"id_$i"}} = $item_unit;
575 if (defined($all_units->{$item_unit}->{factor})
576 && ($all_units->{$item_unit}->{factor} ne '')
577 && ($all_units->{$item_unit}->{factor} != 0)) {
578 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
582 $baseqty = $form->{"qty_$i"} * $basefactor;
584 my ($allocated, $taxrate) = (0, 0);
588 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
590 # keep entered selling price
592 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
594 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
596 my $decimalplaces = ($dec > 2) ? $dec : 2;
598 # undo discount formatting
599 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
602 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
604 # round linetotal to 2 decimal places
605 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
606 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
608 if ($form->{taxincluded}) {
609 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
610 $form->{"sellprice_$i"} =
611 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
613 $taxamount = $linetotal * $taxrate;
616 $netamount += $linetotal;
618 if ($taxamount != 0) {
620 $form->{amount}{ $form->{id} }{$_} +=
621 $taxamount * $form->{"${_}_rate"} / $taxrate
622 } split(/ /, $form->{"taxaccounts_$i"});
625 # add amount to income, $form->{amount}{trans_id}{accno}
626 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
628 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
629 $linetotal = $form->round_amount($linetotal, 2);
631 # this is the difference from the inventory
632 $invoicediff += ($amount - $linetotal);
634 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
637 $lastincomeaccno = $form->{"income_accno_$i"};
639 # adjust and round sellprice
640 $form->{"sellprice_$i"} =
641 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
644 next if $payments_only;
646 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
648 if ($form->{"assembly_$i"}) {
649 # record assembly item as allocated
650 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
653 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
657 # get pricegroup_id and save it
658 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
661 # save detail record in invoice table
663 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
664 sellprice, fxsellprice, discount, allocated, assemblyitem,
665 unit, deliverydate, project_id, serialnumber, pricegroup_id,
666 ordnumber, transdate, cusordnumber, base_qty, subtotal,
667 marge_percent, marge_total, lastcost,
668 price_factor_id, price_factor, marge_price_factor)
669 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
670 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
672 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
673 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
674 $form->{"sellprice_$i"}, $fxsellprice,
675 $form->{"discount_$i"}, $allocated, 'f',
676 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
677 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
678 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
679 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
680 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
681 $form->{"lastcost_$i"},
682 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
683 conv_i($form->{"marge_price_factor_$i"}));
684 do_query($form, $dbh, $query, @values);
686 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
688 qq|INSERT INTO licenseinvoice (trans_id, license_id)
689 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
690 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
691 do_query($form, $dbh, $query, @values);
696 $form->{datepaid} = $form->{invdate};
698 # total payments, don't move we need it here
699 for my $i (1 .. $form->{paidaccounts}) {
700 if ($form->{type} eq "credit_note") {
701 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
703 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
705 $form->{paid} += $form->{"paid_$i"};
706 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
709 my ($tax, $diff) = (0, 0);
711 $netamount = $form->round_amount($netamount, 2);
713 # figure out rounding errors for total amount vs netamount + taxes
714 if ($form->{taxincluded}) {
716 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
717 $diff += $amount - $netamount * $form->{exchangerate};
718 $netamount = $amount;
720 foreach my $item (split(/ /, $form->{taxaccounts})) {
721 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
722 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
723 $tax += $form->{amount}{ $form->{id} }{$item};
724 $netamount -= $form->{amount}{ $form->{id} }{$item};
727 $invoicediff += $diff;
728 ######## this only applies to tax included
729 if ($lastincomeaccno) {
730 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
734 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
735 $diff = $amount - $netamount * $form->{exchangerate};
736 $netamount = $amount;
737 foreach my $item (split(/ /, $form->{taxaccounts})) {
738 $form->{amount}{ $form->{id} }{$item} =
739 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
742 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
745 $amount - $form->{amount}{ $form->{id} }{$item} *
746 $form->{exchangerate};
747 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
748 $tax += $form->{amount}{ $form->{id} }{$item};
752 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
754 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
757 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
759 # update exchangerate
760 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
761 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
762 $form->{exchangerate}, 0);
765 $project_id = conv_i($form->{"globalproject_id"});
767 foreach my $trans_id (keys %{ $form->{amount} }) {
768 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
769 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
771 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
773 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
775 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
776 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
777 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
778 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
779 do_query($form, $dbh, $query, @values);
780 $form->{amount}{$trans_id}{$accno} = 0;
784 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
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);
798 # deduct payment differences from diff
799 for my $i (1 .. $form->{paidaccounts}) {
800 if ($form->{"paid_$i"} != 0) {
802 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
803 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
807 # record payments and offsetting AR
808 if (!$form->{storno}) {
809 for my $i (1 .. $form->{paidaccounts}) {
811 next if ($form->{"paid_$i"} == 0);
813 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
814 $form->{"datepaid_$i"} = $form->{invdate}
815 unless ($form->{"datepaid_$i"});
816 $form->{datepaid} = $form->{"datepaid_$i"};
820 if ($form->{currency} eq $defaultcurrency) {
821 $form->{"exchangerate_$i"} = 1;
823 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
824 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
828 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
830 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
832 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
833 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
834 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
835 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
836 do_query($form, $dbh, $query, @values);
840 $form->{"paid_$i"} *= -1;
843 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
844 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
845 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
846 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
847 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
848 do_query($form, $dbh, $query, @values);
850 # exchangerate difference
851 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
852 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
856 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
857 $form->{"exchangerate_$i"};
859 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
862 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
868 # update exchange rate
869 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
870 $form->update_exchangerate($dbh, $form->{currency},
871 $form->{"datepaid_$i"},
872 $form->{"exchangerate_$i"}, 0);
876 } else { # if (!$form->{storno})
877 $form->{marge_total} *= -1;
880 if ($payments_only) {
881 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
882 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
884 if (!$provided_dbh) {
889 $main::lxdebug->leave_sub();
893 # record exchange rate differences and gains/losses
894 foreach my $accno (keys %{ $form->{fx} }) {
895 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
897 ($form->{fx}{$accno}{$transdate} =
898 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
903 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
904 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
905 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
906 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
907 do_query($form, $dbh, $query, @values);
912 $amount = $netamount + $tax;
915 $query = qq|UPDATE ar set
916 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
917 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
918 amount = ?, netamount = ?, paid = ?, datepaid = ?,
919 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
920 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
921 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
922 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
923 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
924 cp_id = ?, marge_total = ?, marge_percent = ?,
925 globalproject_id = ?, delivery_customer_id = ?,
926 transaction_description = ?, delivery_vendor_id = ?
928 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
929 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
930 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
931 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
932 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
933 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
934 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
935 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
936 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
937 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
938 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
939 conv_i($form->{"id"}));
940 do_query($form, $dbh, $query, @values);
942 if($form->{"formname"} eq "credit_note") {
943 for my $i (1 .. $form->{rowcount}) {
944 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
945 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
946 do_query($form, $dbh, $query, @values);
950 if ($form->{storno}) {
953 paid = paid + amount,
955 intnotes = ? || intnotes
957 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
958 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
962 $form->{name} = $form->{customer};
963 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
965 if (!$form->{shipto_id}) {
966 $form->add_shipto($dbh, $form->{id}, "AR");
969 # save printed, emailed and queued
970 $form->save_status($dbh);
972 Common::webdav_folder($form) if ($main::webdav);
974 # Link this record to the records it was created from.
975 RecordLinks->create_links('dbh' => $dbh,
977 'from_table' => 'oe',
978 'from_ids' => $form->{convert_from_oe_ids},
980 'to_id' => $form->{id},
982 delete $form->{convert_from_oe_ids};
984 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
986 if (scalar @convert_from_do_ids) {
987 DO->close_orders('dbh' => $dbh,
988 'ids' => \@convert_from_do_ids);
990 RecordLinks->create_links('dbh' => $dbh,
992 'from_table' => 'delivery_orders',
993 'from_ids' => \@convert_from_do_ids,
995 'to_id' => $form->{id},
998 delete $form->{convert_from_do_ids};
1000 ARAP->close_orders_if_billed('dbh' => $dbh,
1001 'arap_id' => $form->{id},
1005 if (!$provided_dbh) {
1010 $main::lxdebug->leave_sub();
1015 sub _delete_payments {
1016 $main::lxdebug->enter_sub();
1018 my ($self, $form, $dbh) = @_;
1022 # Delete old payment entries from acc_trans.
1026 WHERE (trans_id = ?) AND fx_transaction
1032 LEFT JOIN chart c ON (at.chart_id = c.id)
1033 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1034 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1039 LEFT JOIN chart c ON (at.chart_id = c.id)
1040 WHERE (trans_id = ?)
1041 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1044 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1047 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1048 do_query($form, $dbh, $query);
1051 $main::lxdebug->leave_sub();
1055 $main::lxdebug->enter_sub();
1057 my ($self, $myconfig, $form, $locale) = @_;
1059 # connect to database, turn off autocommit
1060 my $dbh = $form->dbconnect_noauto($myconfig);
1062 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1064 $old_form = save_form();
1066 # Delete all entries in acc_trans from prior payments.
1067 $self->_delete_payments($form, $dbh);
1069 # Save the new payments the user made before cleaning up $form.
1070 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1072 # Clean up $form so that old content won't tamper the results.
1073 %keep_vars = map { $_, 1 } qw(login password id);
1074 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1076 # Retrieve the invoice from the database.
1077 $self->retrieve_invoice($myconfig, $form);
1079 # Set up the content of $form in the way that IS::post_invoice() expects.
1080 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1082 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1083 $item = $form->{invoice_details}->[$row - 1];
1085 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1087 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1090 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1092 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1094 # Restore the payment options from the user input.
1095 map { $form->{$_} = $payments{$_} } keys %payments;
1097 # Get the AR accno (which is normally done by Form::create_links()).
1101 LEFT JOIN chart c ON (at.chart_id = c.id)
1102 WHERE (trans_id = ?)
1103 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1107 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1109 # Post the new payments.
1110 $self->post_invoice($myconfig, $form, $dbh, 1);
1112 restore_form($old_form);
1114 my $rc = $dbh->commit();
1117 $main::lxdebug->leave_sub();
1122 sub process_assembly {
1123 $main::lxdebug->enter_sub();
1125 my ($dbh, $form, $id, $totalqty) = @_;
1128 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1129 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1131 JOIN parts p ON (a.parts_id = p.id)
1133 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1135 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1139 $ref->{inventory_accno_id} *= 1;
1140 $ref->{expense_accno_id} *= 1;
1142 # multiply by number of assemblies
1143 $ref->{qty} *= $totalqty;
1145 if ($ref->{assembly}) {
1146 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1149 if ($ref->{inventory_accno_id}) {
1150 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1154 # save detail record for individual assembly item in invoice table
1156 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1157 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1158 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1159 do_query($form, $dbh, $query, @values);
1165 $main::lxdebug->leave_sub();
1169 $main::lxdebug->enter_sub();
1171 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1172 $form->{taxzone_id} *=1;
1173 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1174 my $taxzone_id = $form->{"taxzone_id"} * 1;
1176 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1177 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1178 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1179 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1180 FROM invoice i, parts p
1181 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1182 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1183 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1184 WHERE (i.parts_id = p.id)
1185 AND (i.parts_id = ?)
1186 AND ((i.base_qty + i.allocated) < 0)
1188 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1193 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1194 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1198 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1200 # total expenses and inventory
1201 # sellprice is the cost of the item
1202 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1205 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1207 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1208 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1209 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1211 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1212 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1218 last if (($totalqty -= $qty) <= 0);
1223 $main::lxdebug->leave_sub();
1228 sub reverse_invoice {
1229 $main::lxdebug->enter_sub();
1231 my ($dbh, $form) = @_;
1233 # reverse inventory items
1235 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1237 JOIN parts p ON (i.parts_id = p.id)
1238 WHERE i.trans_id = ?|;
1239 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1241 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1243 if ($ref->{inventory_accno_id}) {
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 AS reqdate,
1409 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1410 i.price_factor_id, i.price_factor, i.marge_price_factor,
1411 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1412 pr.projectnumber, pg.partsgroup, prg.pricegroup
1415 LEFT JOIN parts p ON (i.parts_id = p.id)
1416 LEFT JOIN project pr ON (i.project_id = pr.id)
1417 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1418 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1420 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1421 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1422 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1424 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1426 $sth = prepare_execute_query($form, $dbh, $query, $id);
1428 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1429 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1430 delete($ref->{"part_inventory_accno_id"});
1432 foreach my $type (qw(inventory income expense)) {
1433 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1434 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1435 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1439 # get tax rates and description
1440 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1442 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1443 LEFT JOIN chart c ON (c.id = t.chart_id)
1445 (SELECT tk.tax_id FROM taxkeys tk
1446 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1447 AND startdate <= date($transdate)
1448 ORDER BY startdate DESC LIMIT 1)
1450 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1451 $ref->{taxaccounts} = "";
1453 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1455 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1459 $ref->{taxaccounts} .= "$ptr->{accno} ";
1461 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1462 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1463 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1464 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1465 $form->{taxaccounts} .= "$ptr->{accno} ";
1470 if ($form->{lizenzen}) {
1471 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1472 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1473 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1476 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1478 chop $ref->{taxaccounts};
1479 push @{ $form->{invoice_details} }, $ref;
1484 Common::webdav_folder($form) if ($main::webdav);
1487 my $rc = $dbh->commit;
1490 $main::lxdebug->leave_sub();
1496 $main::lxdebug->enter_sub();
1498 my ($self, $myconfig, $form) = @_;
1500 # connect to database
1501 my $dbh = $form->dbconnect($myconfig);
1503 my $dateformat = $myconfig->{dateformat};
1504 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1506 my (@values, $duedate, $ref, $query);
1508 if ($form->{invdate}) {
1509 $duedate = "to_date(?, '$dateformat')";
1510 push @values, $form->{invdate};
1512 $duedate = "current_date";
1515 my $cid = conv_i($form->{customer_id});
1518 if ($form->{payment_id}) {
1519 $payment_id = "(pt.id = ?) OR";
1520 push @values, conv_i($form->{payment_id});
1526 c.name AS customer, c.discount, c.creditlimit, c.terms,
1527 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1528 c.street, c.zipcode, c.city, c.country,
1529 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1530 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1531 b.discount AS tradediscount, b.description AS business
1533 LEFT JOIN business b ON (b.id = c.business_id)
1534 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1537 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1538 map { $form->{$_} = $ref->{$_} } keys %$ref;
1541 qq|SELECT sum(amount - paid) AS dunning_amount
1543 WHERE (paid < amount)
1544 AND (customer_id = ?)
1545 AND (dunning_config_id IS NOT NULL)|;
1546 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1547 map { $form->{$_} = $ref->{$_} } keys %$ref;
1550 qq|SELECT dnn.dunning_description AS max_dunning_level
1551 FROM dunning_config dnn
1552 WHERE id IN (SELECT dunning_config_id
1554 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1555 ORDER BY dunning_level DESC LIMIT 1|;
1556 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1557 map { $form->{$_} = $ref->{$_} } keys %$ref;
1559 $form->{creditremaining} = $form->{creditlimit};
1560 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1561 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1562 $form->{creditremaining} -= $value;
1566 (SELECT e.buy FROM exchangerate e
1567 WHERE e.curr = o.curr
1568 AND e.transdate = o.transdate)
1570 WHERE o.customer_id = ?
1571 AND o.quotation = '0'
1572 AND o.closed = '0'|;
1573 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1575 while (my ($amount, $exch) = $sth->fetchrow_array) {
1576 $exch = 1 unless $exch;
1577 $form->{creditremaining} -= $amount * $exch;
1581 # get shipto if we did not converted an order or invoice
1582 if (!$form->{shipto}) {
1583 map { delete $form->{$_} }
1584 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1585 shiptostreet shiptozipcode shiptocity shiptocountry
1586 shiptocontact shiptophone shiptofax shiptoemail);
1588 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1589 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1591 map { $form->{$_} = $ref->{$_} } keys %$ref;
1594 # setup last accounts used for this customer
1595 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1597 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1599 JOIN acc_trans ac ON (ac.chart_id = c.id)
1600 JOIN ar a ON (a.id = ac.trans_id)
1601 WHERE a.customer_id = ?
1602 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1603 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1604 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1607 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1608 if ($ref->{category} eq 'I') {
1610 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1612 if ($form->{initial_transdate}) {
1614 qq|SELECT tk.tax_id, t.rate
1616 LEFT JOIN tax t ON tk.tax_id = t.id
1617 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1618 ORDER BY tk.startdate DESC
1620 my ($tax_id, $rate) =
1621 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1622 $form->{initial_transdate});
1623 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1626 if ($ref->{category} eq 'A') {
1627 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1631 $form->{rowcount} = $i if ($i && !$form->{type});
1636 $main::lxdebug->leave_sub();
1640 $main::lxdebug->enter_sub();
1642 my ($self, $myconfig, $form) = @_;
1644 # connect to database
1645 my $dbh = $form->dbconnect($myconfig);
1647 my $i = $form->{rowcount};
1649 my $where = qq|NOT p.obsolete = '1'|;
1652 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1653 my ($table, $field) = split m/\./, $column;
1654 next if !$form->{"${field}_${i}"};
1655 $where .= qq| AND lower(${column}) ILIKE ?|;
1656 push @values, '%' . $form->{"${field}_${i}"} . '%';
1659 if ($form->{"description_$i"}) {
1660 $where .= qq| ORDER BY p.description|;
1662 $where .= qq| ORDER BY p.partnumber|;
1666 if ($form->{type} eq "invoice") {
1668 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1669 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1673 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1677 my $taxzone_id = $form->{taxzone_id} * 1;
1678 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1682 p.id, p.partnumber, p.description, p.sellprice,
1683 p.listprice, p.inventory_accno_id, p.lastcost,
1685 c1.accno AS inventory_accno,
1686 c1.new_chart_id AS inventory_new_chart,
1687 date($transdate) - c1.valid_from AS inventory_valid,
1689 c2.accno AS income_accno,
1690 c2.new_chart_id AS income_new_chart,
1691 date($transdate) - c2.valid_from AS income_valid,
1693 c3.accno AS expense_accno,
1694 c3.new_chart_id AS expense_new_chart,
1695 date($transdate) - c3.valid_from AS expense_valid,
1697 p.unit, p.assembly, p.bin, p.onhand,
1698 p.notes AS partnotes, p.notes AS longdescription,
1699 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1702 pfac.factor AS price_factor,
1707 LEFT JOIN chart c1 ON
1708 ((SELECT inventory_accno_id
1709 FROM buchungsgruppen
1710 WHERE id = p.buchungsgruppen_id) = c1.id)
1711 LEFT JOIN chart c2 ON
1712 ((SELECT income_accno_id_${taxzone_id}
1713 FROM buchungsgruppen
1714 WHERE id = p.buchungsgruppen_id) = c2.id)
1715 LEFT JOIN chart c3 ON
1716 ((SELECT expense_accno_id_${taxzone_id}
1717 FROM buchungsgruppen
1718 WHERE id = p.buchungsgruppen_id) = c3.id)
1719 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1720 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1722 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1724 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1726 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1727 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1728 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1729 if (!$ref->{inventory_accno_id}) {
1730 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1732 delete($ref->{inventory_accno_id});
1734 foreach my $type (qw(inventory income expense)) {
1735 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1737 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1740 ($ref->{"${type}_accno"},
1741 $ref->{"${type}_new_chart"},
1742 $ref->{"${type}_valid"})
1743 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1747 if ($form->{payment_id} eq "") {
1748 $form->{payment_id} = $form->{part_payment_id};
1751 # get tax rates and description
1752 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1754 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1756 LEFT JOIN chart c ON (c.id = t.chart_id)
1760 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1762 ORDER BY startdate DESC
1765 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1766 $stw = $dbh->prepare($query);
1767 $stw->execute(@values) || $form->dberror($query);
1769 $ref->{taxaccounts} = "";
1771 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1773 # if ($customertax{$ref->{accno}}) {
1774 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1778 $ref->{taxaccounts} .= "$ptr->{accno} ";
1780 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1781 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1782 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1783 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1784 $form->{taxaccounts} .= "$ptr->{accno} ";
1790 chop $ref->{taxaccounts};
1791 if ($form->{language_id}) {
1793 qq|SELECT tr.translation, tr.longdescription
1795 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1796 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1797 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1798 if ($translation ne "") {
1799 $ref->{description} = $translation;
1800 $ref->{longdescription} = $longdescription;
1804 qq|SELECT tr.translation, tr.longdescription
1806 WHERE tr.language_id IN
1809 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1812 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1813 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1814 if ($translation ne "") {
1815 $ref->{description} = $translation;
1816 $ref->{longdescription} = $longdescription;
1821 $ref->{onhand} *= 1;
1823 push @{ $form->{item_list} }, $ref;
1825 if ($form->{lizenzen}) {
1826 if ($ref->{inventory_accno} > 0) {
1830 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1831 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1832 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1833 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1842 $main::lxdebug->leave_sub();
1845 ##########################
1846 # get pricegroups from database
1847 # build up selected pricegroup
1848 # if an exchange rate - change price
1851 sub get_pricegroups_for_parts {
1853 $main::lxdebug->enter_sub();
1855 my ($self, $myconfig, $form) = @_;
1857 my $dbh = $form->dbconnect($myconfig);
1859 $form->{"PRICES"} = {};
1863 my $all_units = AM->retrieve_units($myconfig, $form);
1864 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1865 $form->{"PRICES"}{$i} = [];
1867 $id = $form->{"id_$i"};
1869 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1871 $id = $form->{"new_id_$i"};
1874 ($price, $selectedpricegroup_id) = split(/--/,
1875 $form->{"sellprice_pg_$i"});
1877 $pricegroup_old = $form->{"pricegroup_old_$i"};
1878 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1879 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1881 $price_new = $form->{"price_new_$i"};
1882 $price_old = $form->{"price_old_$i"};
1884 if (!$form->{"unit_old_$i"}) {
1885 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1886 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1887 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1888 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1891 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1892 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1893 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1895 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1896 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1897 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1898 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1899 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1900 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1901 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1906 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1907 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1908 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1909 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1910 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1914 if (!$form->{"basefactor_$i"}) {
1915 $form->{"basefactor_$i"} = 1;
1921 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1922 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1932 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1934 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1935 'selected' AS selected
1938 ORDER BY pricegroup|;
1939 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1940 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1942 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1944 $pkr->{selected} = '';
1946 # if there is an exchange rate change price
1947 if (($form->{exchangerate} * 1) != 0) {
1949 $pkr->{price} /= $form->{exchangerate};
1952 $pkr->{price} *= $form->{"basefactor_$i"};
1954 $pkr->{price} *= $basefactor;
1956 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1958 if ($selectedpricegroup_id eq undef) {
1959 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1961 $pkr->{selected} = ' selected';
1963 # no customer pricesgroup set
1964 if ($pkr->{price} == $pkr->{default_sellprice}) {
1966 $pkr->{price} = $form->{"sellprice_$i"};
1970 $form->{"sellprice_$i"} = $pkr->{price};
1973 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1974 $pkr->{price} = $form->{"sellprice_$i"};
1975 $pkr->{selected} = ' selected';
1979 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1980 if ($selectedpricegroup_id ne $pricegroup_old) {
1981 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1982 $pkr->{selected} = ' selected';
1984 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1985 if ($pkr->{pricegroup_id} == 0) {
1986 $pkr->{price} = $form->{"sellprice_$i"};
1987 $pkr->{selected} = ' selected';
1989 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1990 $pkr->{selected} = ' selected';
1991 if ( ($pkr->{pricegroup_id} == 0)
1992 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1993 # $pkr->{price} = $form->{"sellprice_$i"};
1995 $pkr->{price} = $form->{"sellprice_$i"};
1999 push @{ $form->{PRICES}{$i} }, $pkr;
2002 $form->{"basefactor_$i"} *= $basefactor;
2011 $main::lxdebug->leave_sub();
2015 $main::lxdebug->enter_sub();
2017 my ($self, $myconfig, $form, $table) = @_;
2019 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2021 # make sure there's no funny stuff in $table
2022 # ToDO: die when this happens and throw an error
2023 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2025 my $dbh = $form->dbconnect($myconfig);
2027 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2028 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2032 $main::lxdebug->leave_sub();
2038 $main::lxdebug->enter_sub();
2040 my ($self, $myconfig, $form, $table, $id) = @_;
2042 $main::lxdebug->leave_sub() and return 0 unless ($id);
2044 # make sure there's no funny stuff in $table
2045 # ToDO: die when this happens and throw an error
2046 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2048 my $dbh = $form->dbconnect($myconfig);
2050 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2051 my ($result) = selectrow_query($form, $dbh, $query, $id);
2055 $main::lxdebug->leave_sub();