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 my $accno (@taxaccounts) {
283 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
285 $taxaccounts{$accno} += $taxamount;
286 $taxdiff += $taxamount;
288 $taxbase{$accno} += $taxbase;
290 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
292 foreach my $accno (@taxaccounts) {
293 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
294 $taxbase{$accno} += $taxbase;
298 foreach my $accno (@taxaccounts) {
299 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
300 $taxbase{$accno} += $taxbase;
303 $tax_rate = $taxrate * 100;
304 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
305 if ($form->{"assembly_$i"}) {
308 # get parts and push them onto the stack
310 if ($form->{groupitems}) {
312 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
314 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
318 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
320 JOIN parts p ON (a.parts_id = p.id)
321 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
322 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
323 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
325 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
326 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
327 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
328 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
329 push(@{ $form->{description} }, $sameitem);
332 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
334 push(@{ $form->{description} },
335 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
337 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
338 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
346 foreach my $item (sort keys %taxaccounts) {
347 push(@{ $form->{taxbase} },
348 $form->format_amount($myconfig, $taxbase{$item}, 2));
350 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
352 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
353 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
354 push(@{ $form->{taxrate} },
355 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
356 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
359 for my $i (1 .. $form->{paidaccounts}) {
360 if ($form->{"paid_$i"}) {
361 push(@{ $form->{payment} }, $form->{"paid_$i"});
362 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
363 push(@{ $form->{paymentaccount} }, $description);
364 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
365 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
367 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
370 if($form->{taxincluded}) {
371 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
374 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
377 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
378 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
379 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
380 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
382 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
383 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
385 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
386 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
388 $form->set_payment_options($myconfig, $form->{invdate});
390 $form->{username} = $myconfig->{name};
394 $main::lxdebug->leave_sub();
397 sub project_description {
398 $main::lxdebug->enter_sub();
400 my ($self, $dbh, $id) = @_;
402 my $query = qq|SELECT description FROM project WHERE id = ?|;
403 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
405 $main::lxdebug->leave_sub();
410 sub customer_details {
411 $main::lxdebug->enter_sub();
413 my ($self, $myconfig, $form, @wanted_vars) = @_;
415 # connect to database
416 my $dbh = $form->dbconnect($myconfig);
418 # get contact id, set it if nessessary
421 my @values = (conv_i($form->{customer_id}));
424 if ($form->{cp_id}) {
425 $where = qq| AND (cp.cp_id = ?) |;
426 push(@values, conv_i($form->{cp_id}));
429 # get rest for the customer
431 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
432 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
434 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
435 WHERE (ct.id = ?) $where
438 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
440 # remove id and taxincluded before copy back
441 delete @$ref{qw(id taxincluded)};
443 @wanted_vars = grep({ $_ } @wanted_vars);
444 if (scalar(@wanted_vars) > 0) {
446 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
447 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
450 map { $form->{$_} = $ref->{$_} } keys %$ref;
452 if ($form->{delivery_customer_id}) {
454 qq|SELECT *, notes as customernotes
458 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
460 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
463 if ($form->{delivery_vendor_id}) {
465 qq|SELECT *, notes as customernotes
469 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
471 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
474 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
476 'trans_id' => $form->{customer_id});
477 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
481 $main::lxdebug->leave_sub();
485 $main::lxdebug->enter_sub();
487 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
489 # connect to database, turn off autocommit
490 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
492 my ($query, $sth, $null, $project_id, @values);
493 my $exchangerate = 0;
495 if (!$form->{employee_id}) {
496 $form->get_employee($dbh);
499 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
501 ($null, $form->{department_id}) = split(/--/, $form->{department});
503 my $all_units = AM->retrieve_units($myconfig, $form);
505 if (!$payments_only) {
507 &reverse_invoice($dbh, $form);
510 $query = qq|SELECT nextval('glid')|;
511 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
513 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
514 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
516 if (!$form->{invnumber}) {
518 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
519 "cnnumber" : "invnumber", $dbh);
524 my ($netamount, $invoicediff) = (0, 0);
525 my ($amount, $linetotal, $lastincomeaccno);
527 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
528 my $defaultcurrency = (split m/:/, $currencies)[0];
530 if ($form->{currency} eq $defaultcurrency) {
531 $form->{exchangerate} = 1;
533 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
536 $form->{exchangerate} =
539 : $form->parse_amount($myconfig, $form->{exchangerate});
541 $form->{expense_inventory} = "";
545 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
546 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
549 foreach my $i (1 .. $form->{rowcount}) {
550 if ($form->{type} eq "credit_note") {
551 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
552 $form->{shipped} = 1;
554 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
559 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
560 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
561 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
563 if ($form->{storno}) {
564 $form->{"qty_$i"} *= -1;
567 if ($form->{"id_$i"}) {
570 if (defined($baseunits{$form->{"id_$i"}})) {
571 $item_unit = $baseunits{$form->{"id_$i"}};
574 $query = qq|SELECT unit FROM parts WHERE id = ?|;
575 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
576 $baseunits{$form->{"id_$i"}} = $item_unit;
579 if (defined($all_units->{$item_unit}->{factor})
580 && ($all_units->{$item_unit}->{factor} ne '')
581 && ($all_units->{$item_unit}->{factor} != 0)) {
582 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
586 $baseqty = $form->{"qty_$i"} * $basefactor;
588 my ($allocated, $taxrate) = (0, 0);
592 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
594 # keep entered selling price
596 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
598 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
600 my $decimalplaces = ($dec > 2) ? $dec : 2;
602 # undo discount formatting
603 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
606 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
608 # round linetotal to 2 decimal places
609 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
610 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
612 if ($form->{taxincluded}) {
613 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
614 $form->{"sellprice_$i"} =
615 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
617 $taxamount = $linetotal * $taxrate;
620 $netamount += $linetotal;
622 if ($taxamount != 0) {
624 $form->{amount}{ $form->{id} }{$_} +=
625 $taxamount * $form->{"${_}_rate"} / $taxrate
626 } split(/ /, $form->{"taxaccounts_$i"});
629 # add amount to income, $form->{amount}{trans_id}{accno}
630 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
632 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
633 $linetotal = $form->round_amount($linetotal, 2);
635 # this is the difference from the inventory
636 $invoicediff += ($amount - $linetotal);
638 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
641 $lastincomeaccno = $form->{"income_accno_$i"};
643 # adjust and round sellprice
644 $form->{"sellprice_$i"} =
645 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
648 next if $payments_only;
650 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
652 if ($form->{"assembly_$i"}) {
653 # record assembly item as allocated
654 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
657 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
661 # get pricegroup_id and save it
662 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
665 # save detail record in invoice table
667 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
668 sellprice, fxsellprice, discount, allocated, assemblyitem,
669 unit, deliverydate, project_id, serialnumber, pricegroup_id,
670 ordnumber, transdate, cusordnumber, base_qty, subtotal,
671 marge_percent, marge_total, lastcost,
672 price_factor_id, price_factor, marge_price_factor)
673 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
674 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
676 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
677 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
678 $form->{"sellprice_$i"}, $fxsellprice,
679 $form->{"discount_$i"}, $allocated, 'f',
680 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
681 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
682 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
683 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
684 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
685 $form->{"lastcost_$i"},
686 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
687 conv_i($form->{"marge_price_factor_$i"}));
688 do_query($form, $dbh, $query, @values);
690 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
692 qq|INSERT INTO licenseinvoice (trans_id, license_id)
693 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
694 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
695 do_query($form, $dbh, $query, @values);
700 $form->{datepaid} = $form->{invdate};
702 # total payments, don't move we need it here
703 for my $i (1 .. $form->{paidaccounts}) {
704 if ($form->{type} eq "credit_note") {
705 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
707 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
709 $form->{paid} += $form->{"paid_$i"};
710 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
713 my ($tax, $diff) = (0, 0);
715 $netamount = $form->round_amount($netamount, 2);
717 # figure out rounding errors for total amount vs netamount + taxes
718 if ($form->{taxincluded}) {
720 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
721 $diff += $amount - $netamount * $form->{exchangerate};
722 $netamount = $amount;
724 foreach my $item (split(/ /, $form->{taxaccounts})) {
725 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
726 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
727 $tax += $form->{amount}{ $form->{id} }{$item};
728 $netamount -= $form->{amount}{ $form->{id} }{$item};
731 $invoicediff += $diff;
732 ######## this only applies to tax included
733 if ($lastincomeaccno) {
734 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
738 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
739 $diff = $amount - $netamount * $form->{exchangerate};
740 $netamount = $amount;
741 foreach my $item (split(/ /, $form->{taxaccounts})) {
742 $form->{amount}{ $form->{id} }{$item} =
743 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
746 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
749 $amount - $form->{amount}{ $form->{id} }{$item} *
750 $form->{exchangerate};
751 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
752 $tax += $form->{amount}{ $form->{id} }{$item};
756 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
758 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
761 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
763 # update exchangerate
764 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
765 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
766 $form->{exchangerate}, 0);
769 $project_id = conv_i($form->{"globalproject_id"});
771 foreach my $trans_id (keys %{ $form->{amount} }) {
772 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
773 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
775 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
777 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
779 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
780 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
781 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
782 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
783 do_query($form, $dbh, $query, @values);
784 $form->{amount}{$trans_id}{$accno} = 0;
788 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
789 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
791 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
793 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
794 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
795 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
796 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
797 do_query($form, $dbh, $query, @values);
802 # deduct payment differences from diff
803 for my $i (1 .. $form->{paidaccounts}) {
804 if ($form->{"paid_$i"} != 0) {
806 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
807 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
811 # record payments and offsetting AR
812 if (!$form->{storno}) {
813 for my $i (1 .. $form->{paidaccounts}) {
815 next if ($form->{"paid_$i"} == 0);
817 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
818 $form->{"datepaid_$i"} = $form->{invdate}
819 unless ($form->{"datepaid_$i"});
820 $form->{datepaid} = $form->{"datepaid_$i"};
824 if ($form->{currency} eq $defaultcurrency) {
825 $form->{"exchangerate_$i"} = 1;
827 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
828 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
832 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
834 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
836 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
837 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
838 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
839 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
840 do_query($form, $dbh, $query, @values);
844 $form->{"paid_$i"} *= -1;
847 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
848 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
849 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
850 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
851 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
852 do_query($form, $dbh, $query, @values);
854 # exchangerate difference
855 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
856 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
860 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
861 $form->{"exchangerate_$i"};
863 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
866 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
872 # update exchange rate
873 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
874 $form->update_exchangerate($dbh, $form->{currency},
875 $form->{"datepaid_$i"},
876 $form->{"exchangerate_$i"}, 0);
880 } else { # if (!$form->{storno})
881 $form->{marge_total} *= -1;
884 if ($payments_only) {
885 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
886 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
888 if (!$provided_dbh) {
893 $main::lxdebug->leave_sub();
897 # record exchange rate differences and gains/losses
898 foreach my $accno (keys %{ $form->{fx} }) {
899 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
901 ($form->{fx}{$accno}{$transdate} =
902 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
907 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
908 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
909 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
910 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
911 do_query($form, $dbh, $query, @values);
916 $amount = $netamount + $tax;
919 $query = qq|UPDATE ar set
920 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
921 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
922 amount = ?, netamount = ?, paid = ?, datepaid = ?,
923 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
924 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
925 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
926 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
927 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
928 cp_id = ?, marge_total = ?, marge_percent = ?,
929 globalproject_id = ?, delivery_customer_id = ?,
930 transaction_description = ?, delivery_vendor_id = ?
932 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
933 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
934 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
935 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
936 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
937 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
938 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
939 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
940 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
941 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
942 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
943 conv_i($form->{"id"}));
944 do_query($form, $dbh, $query, @values);
946 if($form->{"formname"} eq "credit_note") {
947 for my $i (1 .. $form->{rowcount}) {
948 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
949 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
950 do_query($form, $dbh, $query, @values);
954 if ($form->{storno}) {
957 paid = paid + amount,
959 intnotes = ? || intnotes
961 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
962 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
966 $form->{name} = $form->{customer};
967 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
969 if (!$form->{shipto_id}) {
970 $form->add_shipto($dbh, $form->{id}, "AR");
973 # save printed, emailed and queued
974 $form->save_status($dbh);
976 Common::webdav_folder($form) if ($main::webdav);
978 # Link this record to the records it was created from.
979 RecordLinks->create_links('dbh' => $dbh,
981 'from_table' => 'oe',
982 'from_ids' => $form->{convert_from_oe_ids},
984 'to_id' => $form->{id},
986 delete $form->{convert_from_oe_ids};
988 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
990 if (scalar @convert_from_do_ids) {
991 DO->close_orders('dbh' => $dbh,
992 'ids' => \@convert_from_do_ids);
994 RecordLinks->create_links('dbh' => $dbh,
996 'from_table' => 'delivery_orders',
997 'from_ids' => \@convert_from_do_ids,
999 'to_id' => $form->{id},
1002 delete $form->{convert_from_do_ids};
1004 ARAP->close_orders_if_billed('dbh' => $dbh,
1005 'arap_id' => $form->{id},
1009 if (!$provided_dbh) {
1014 $main::lxdebug->leave_sub();
1019 sub _delete_payments {
1020 $main::lxdebug->enter_sub();
1022 my ($self, $form, $dbh) = @_;
1026 # Delete old payment entries from acc_trans.
1030 WHERE (trans_id = ?) AND fx_transaction
1036 LEFT JOIN chart c ON (at.chart_id = c.id)
1037 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1038 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1043 LEFT JOIN chart c ON (at.chart_id = c.id)
1044 WHERE (trans_id = ?)
1045 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1048 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1051 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1052 do_query($form, $dbh, $query);
1055 $main::lxdebug->leave_sub();
1059 $main::lxdebug->enter_sub();
1061 my ($self, $myconfig, $form, $locale) = @_;
1063 # connect to database, turn off autocommit
1064 my $dbh = $form->dbconnect_noauto($myconfig);
1066 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1068 $old_form = save_form();
1070 # Delete all entries in acc_trans from prior payments.
1071 $self->_delete_payments($form, $dbh);
1073 # Save the new payments the user made before cleaning up $form.
1074 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1076 # Clean up $form so that old content won't tamper the results.
1077 %keep_vars = map { $_, 1 } qw(login password id);
1078 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1080 # Retrieve the invoice from the database.
1081 $self->retrieve_invoice($myconfig, $form);
1083 # Set up the content of $form in the way that IS::post_invoice() expects.
1084 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1086 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1087 $item = $form->{invoice_details}->[$row - 1];
1089 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1091 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1094 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1096 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1098 # Restore the payment options from the user input.
1099 map { $form->{$_} = $payments{$_} } keys %payments;
1101 # Get the AR accno (which is normally done by Form::create_links()).
1105 LEFT JOIN chart c ON (at.chart_id = c.id)
1106 WHERE (trans_id = ?)
1107 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1111 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1113 # Post the new payments.
1114 $self->post_invoice($myconfig, $form, $dbh, 1);
1116 restore_form($old_form);
1118 my $rc = $dbh->commit();
1121 $main::lxdebug->leave_sub();
1126 sub process_assembly {
1127 $main::lxdebug->enter_sub();
1129 my ($dbh, $form, $id, $totalqty) = @_;
1132 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1133 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1135 JOIN parts p ON (a.parts_id = p.id)
1137 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1139 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1143 $ref->{inventory_accno_id} *= 1;
1144 $ref->{expense_accno_id} *= 1;
1146 # multiply by number of assemblies
1147 $ref->{qty} *= $totalqty;
1149 if ($ref->{assembly}) {
1150 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1153 if ($ref->{inventory_accno_id}) {
1154 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1158 # save detail record for individual assembly item in invoice table
1160 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1161 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1162 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1163 do_query($form, $dbh, $query, @values);
1169 $main::lxdebug->leave_sub();
1173 $main::lxdebug->enter_sub();
1175 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1176 $form->{taxzone_id} *=1;
1177 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1178 my $taxzone_id = $form->{"taxzone_id"} * 1;
1180 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1181 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1182 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1183 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1184 FROM invoice i, parts p
1185 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1186 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1187 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1188 WHERE (i.parts_id = p.id)
1189 AND (i.parts_id = ?)
1190 AND ((i.base_qty + i.allocated) < 0)
1192 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1197 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1198 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1202 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1204 # total expenses and inventory
1205 # sellprice is the cost of the item
1206 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1209 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1211 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1212 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1213 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1215 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1216 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1222 last if (($totalqty -= $qty) <= 0);
1227 $main::lxdebug->leave_sub();
1232 sub reverse_invoice {
1233 $main::lxdebug->enter_sub();
1235 my ($dbh, $form) = @_;
1237 # reverse inventory items
1239 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1241 JOIN parts p ON (i.parts_id = p.id)
1242 WHERE i.trans_id = ?|;
1243 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1245 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1247 if ($ref->{inventory_accno_id}) {
1248 # de-allocated purchases
1250 qq|SELECT i.id, i.trans_id, i.allocated
1252 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1253 ORDER BY i.trans_id DESC|;
1254 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1256 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1258 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1259 $qty = $inhref->{allocated};
1263 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1265 last if (($ref->{qty} -= $qty) <= 0);
1274 @values = (conv_i($form->{id}));
1275 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1276 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1278 if ($form->{lizenzen}) {
1280 qq|DELETE FROM licenseinvoice
1281 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1282 do_query($form, $dbh, $query, @values);
1285 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1287 $main::lxdebug->leave_sub();
1290 sub delete_invoice {
1291 $main::lxdebug->enter_sub();
1293 my ($self, $myconfig, $form, $spool) = @_;
1295 # connect to database
1296 my $dbh = $form->dbconnect_noauto($myconfig);
1298 &reverse_invoice($dbh, $form);
1300 my @values = (conv_i($form->{id}));
1303 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1305 # delete spool files
1306 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1308 # delete status entries
1309 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1311 my $rc = $dbh->commit;
1315 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1318 $main::lxdebug->leave_sub();
1323 sub retrieve_invoice {
1324 $main::lxdebug->enter_sub();
1326 my ($self, $myconfig, $form) = @_;
1328 # connect to database
1329 my $dbh = $form->dbconnect_noauto($myconfig);
1331 my ($sth, $ref, $query);
1333 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1337 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1338 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1339 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1340 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1341 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1342 d.curr AS currencies
1346 $ref = selectfirst_hashref_query($form, $dbh, $query);
1347 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1350 my $id = conv_i($form->{id});
1355 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1356 a.orddate, a.quodate, a.globalproject_id,
1357 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1358 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1359 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1360 a.employee_id, a.salesman_id, a.payment_id,
1361 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1362 a.transaction_description,
1363 a.marge_total, a.marge_percent,
1366 LEFT JOIN employee e ON (e.id = a.employee_id)
1368 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1369 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1372 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1375 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1376 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1378 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1380 foreach my $vc (qw(customer vendor)) {
1381 next if !$form->{"delivery_${vc}_id"};
1382 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1385 # get printed, emailed
1386 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1387 $sth = prepare_execute_query($form, $dbh, $query, $id);
1389 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1390 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1391 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1392 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1395 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1397 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1398 : $form->{invdate} ? $dbh->quote($form->{invdate})
1402 my $taxzone_id = $form->{taxzone_id} *= 1;
1403 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1405 # retrieve individual items
1408 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1409 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1410 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1412 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1413 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1414 i.price_factor_id, i.price_factor, i.marge_price_factor,
1415 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1416 pr.projectnumber, pg.partsgroup, prg.pricegroup
1419 LEFT JOIN parts p ON (i.parts_id = p.id)
1420 LEFT JOIN project pr ON (i.project_id = pr.id)
1421 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1422 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1424 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1425 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1426 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1428 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1430 $sth = prepare_execute_query($form, $dbh, $query, $id);
1432 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1433 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1434 delete($ref->{"part_inventory_accno_id"});
1436 foreach my $type (qw(inventory income expense)) {
1437 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1438 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1439 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1443 # get tax rates and description
1444 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1446 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1447 LEFT JOIN chart c ON (c.id = t.chart_id)
1449 (SELECT tk.tax_id FROM taxkeys tk
1450 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1451 AND startdate <= date($transdate)
1452 ORDER BY startdate DESC LIMIT 1)
1454 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1455 $ref->{taxaccounts} = "";
1457 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1459 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1463 $ref->{taxaccounts} .= "$ptr->{accno} ";
1465 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1466 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1467 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1468 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1469 $form->{taxaccounts} .= "$ptr->{accno} ";
1474 if ($form->{lizenzen}) {
1475 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1476 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1477 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1480 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1482 chop $ref->{taxaccounts};
1483 push @{ $form->{invoice_details} }, $ref;
1488 Common::webdav_folder($form) if ($main::webdav);
1491 my $rc = $dbh->commit;
1494 $main::lxdebug->leave_sub();
1500 $main::lxdebug->enter_sub();
1502 my ($self, $myconfig, $form) = @_;
1504 # connect to database
1505 my $dbh = $form->dbconnect($myconfig);
1507 my $dateformat = $myconfig->{dateformat};
1508 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1510 my (@values, $duedate, $ref, $query);
1512 if ($form->{invdate}) {
1513 $duedate = "to_date(?, '$dateformat')";
1514 push @values, $form->{invdate};
1516 $duedate = "current_date";
1519 my $cid = conv_i($form->{customer_id});
1522 if ($form->{payment_id}) {
1523 $payment_id = "(pt.id = ?) OR";
1524 push @values, conv_i($form->{payment_id});
1530 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1531 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1532 c.street, c.zipcode, c.city, c.country,
1533 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1534 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1535 b.discount AS tradediscount, b.description AS business
1537 LEFT JOIN business b ON (b.id = c.business_id)
1538 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1541 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1542 map { $form->{$_} = $ref->{$_} } keys %$ref;
1545 qq|SELECT sum(amount - paid) AS dunning_amount
1547 WHERE (paid < amount)
1548 AND (customer_id = ?)
1549 AND (dunning_config_id IS NOT NULL)|;
1550 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1551 map { $form->{$_} = $ref->{$_} } keys %$ref;
1554 qq|SELECT dnn.dunning_description AS max_dunning_level
1555 FROM dunning_config dnn
1556 WHERE id IN (SELECT dunning_config_id
1558 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1559 ORDER BY dunning_level DESC LIMIT 1|;
1560 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1561 map { $form->{$_} = $ref->{$_} } keys %$ref;
1563 $form->{creditremaining} = $form->{creditlimit};
1564 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1565 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1566 $form->{creditremaining} -= $value;
1570 (SELECT e.buy FROM exchangerate e
1571 WHERE e.curr = o.curr
1572 AND e.transdate = o.transdate)
1574 WHERE o.customer_id = ?
1575 AND o.quotation = '0'
1576 AND o.closed = '0'|;
1577 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1579 while (my ($amount, $exch) = $sth->fetchrow_array) {
1580 $exch = 1 unless $exch;
1581 $form->{creditremaining} -= $amount * $exch;
1585 # get shipto if we did not converted an order or invoice
1586 if (!$form->{shipto}) {
1587 map { delete $form->{$_} }
1588 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1589 shiptostreet shiptozipcode shiptocity shiptocountry
1590 shiptocontact shiptophone shiptofax shiptoemail);
1592 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1593 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1595 map { $form->{$_} = $ref->{$_} } keys %$ref;
1598 # setup last accounts used for this customer
1599 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1601 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1603 JOIN acc_trans ac ON (ac.chart_id = c.id)
1604 JOIN ar a ON (a.id = ac.trans_id)
1605 WHERE a.customer_id = ?
1606 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1607 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1608 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1611 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1612 if ($ref->{category} eq 'I') {
1614 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1616 if ($form->{initial_transdate}) {
1618 qq|SELECT tk.tax_id, t.rate
1620 LEFT JOIN tax t ON tk.tax_id = t.id
1621 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1622 ORDER BY tk.startdate DESC
1624 my ($tax_id, $rate) =
1625 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1626 $form->{initial_transdate});
1627 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1630 if ($ref->{category} eq 'A') {
1631 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1635 $form->{rowcount} = $i if ($i && !$form->{type});
1640 $main::lxdebug->leave_sub();
1644 $main::lxdebug->enter_sub();
1646 my ($self, $myconfig, $form) = @_;
1648 # connect to database
1649 my $dbh = $form->dbconnect($myconfig);
1651 my $i = $form->{rowcount};
1653 my $where = qq|NOT p.obsolete = '1'|;
1656 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1657 my ($table, $field) = split m/\./, $column;
1658 next if !$form->{"${field}_${i}"};
1659 $where .= qq| AND lower(${column}) ILIKE ?|;
1660 push @values, '%' . $form->{"${field}_${i}"} . '%';
1663 if ($form->{"description_$i"}) {
1664 $where .= qq| ORDER BY p.description|;
1666 $where .= qq| ORDER BY p.partnumber|;
1670 if ($form->{type} eq "invoice") {
1672 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1673 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1677 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1681 my $taxzone_id = $form->{taxzone_id} * 1;
1682 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1686 p.id, p.partnumber, p.description, p.sellprice,
1687 p.listprice, p.inventory_accno_id, p.lastcost,
1689 c1.accno AS inventory_accno,
1690 c1.new_chart_id AS inventory_new_chart,
1691 date($transdate) - c1.valid_from AS inventory_valid,
1693 c2.accno AS income_accno,
1694 c2.new_chart_id AS income_new_chart,
1695 date($transdate) - c2.valid_from AS income_valid,
1697 c3.accno AS expense_accno,
1698 c3.new_chart_id AS expense_new_chart,
1699 date($transdate) - c3.valid_from AS expense_valid,
1701 p.unit, p.assembly, p.bin, p.onhand,
1702 p.notes AS partnotes, p.notes AS longdescription,
1703 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1706 pfac.factor AS price_factor,
1711 LEFT JOIN chart c1 ON
1712 ((SELECT inventory_accno_id
1713 FROM buchungsgruppen
1714 WHERE id = p.buchungsgruppen_id) = c1.id)
1715 LEFT JOIN chart c2 ON
1716 ((SELECT income_accno_id_${taxzone_id}
1717 FROM buchungsgruppen
1718 WHERE id = p.buchungsgruppen_id) = c2.id)
1719 LEFT JOIN chart c3 ON
1720 ((SELECT expense_accno_id_${taxzone_id}
1721 FROM buchungsgruppen
1722 WHERE id = p.buchungsgruppen_id) = c3.id)
1723 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1724 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1726 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1728 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1730 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1731 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1732 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1733 if (!$ref->{inventory_accno_id}) {
1734 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1736 delete($ref->{inventory_accno_id});
1738 foreach my $type (qw(inventory income expense)) {
1739 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1741 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1744 ($ref->{"${type}_accno"},
1745 $ref->{"${type}_new_chart"},
1746 $ref->{"${type}_valid"})
1747 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1751 if ($form->{payment_id} eq "") {
1752 $form->{payment_id} = $form->{part_payment_id};
1755 # get tax rates and description
1756 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1758 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1760 LEFT JOIN chart c ON (c.id = t.chart_id)
1764 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1766 ORDER BY startdate DESC
1769 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1770 $stw = $dbh->prepare($query);
1771 $stw->execute(@values) || $form->dberror($query);
1773 $ref->{taxaccounts} = "";
1775 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1777 # if ($customertax{$ref->{accno}}) {
1778 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1782 $ref->{taxaccounts} .= "$ptr->{accno} ";
1784 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1785 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1786 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1787 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1788 $form->{taxaccounts} .= "$ptr->{accno} ";
1794 chop $ref->{taxaccounts};
1795 if ($form->{language_id}) {
1797 qq|SELECT tr.translation, tr.longdescription
1799 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1800 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1801 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1802 if ($translation ne "") {
1803 $ref->{description} = $translation;
1804 $ref->{longdescription} = $longdescription;
1808 qq|SELECT tr.translation, tr.longdescription
1810 WHERE tr.language_id IN
1813 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1816 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1817 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1818 if ($translation ne "") {
1819 $ref->{description} = $translation;
1820 $ref->{longdescription} = $longdescription;
1825 $ref->{onhand} *= 1;
1827 push @{ $form->{item_list} }, $ref;
1829 if ($form->{lizenzen}) {
1830 if ($ref->{inventory_accno} > 0) {
1834 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1835 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1836 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1837 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1846 $main::lxdebug->leave_sub();
1849 ##########################
1850 # get pricegroups from database
1851 # build up selected pricegroup
1852 # if an exchange rate - change price
1855 sub get_pricegroups_for_parts {
1857 $main::lxdebug->enter_sub();
1859 my ($self, $myconfig, $form) = @_;
1861 my $dbh = $form->dbconnect($myconfig);
1863 $form->{"PRICES"} = {};
1867 my $all_units = AM->retrieve_units($myconfig, $form);
1868 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1869 $form->{"PRICES"}{$i} = [];
1871 $id = $form->{"id_$i"};
1873 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1875 $id = $form->{"new_id_$i"};
1878 ($price, $selectedpricegroup_id) = split(/--/,
1879 $form->{"sellprice_pg_$i"});
1881 $pricegroup_old = $form->{"pricegroup_old_$i"};
1882 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1883 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1885 $price_new = $form->{"price_new_$i"};
1886 $price_old = $form->{"price_old_$i"};
1888 if (!$form->{"unit_old_$i"}) {
1889 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1890 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1891 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1892 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1895 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1896 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1897 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1899 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1900 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1901 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1902 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1903 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1904 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1905 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1910 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1911 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1912 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1913 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1914 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1918 if (!$form->{"basefactor_$i"}) {
1919 $form->{"basefactor_$i"} = 1;
1925 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1926 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1936 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1938 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1939 'selected' AS selected
1942 ORDER BY pricegroup|;
1943 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1944 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1946 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1948 $pkr->{selected} = '';
1950 # if there is an exchange rate change price
1951 if (($form->{exchangerate} * 1) != 0) {
1953 $pkr->{price} /= $form->{exchangerate};
1956 $pkr->{price} *= $form->{"basefactor_$i"};
1958 $pkr->{price} *= $basefactor;
1960 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1962 if ($selectedpricegroup_id eq undef) {
1963 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1965 $pkr->{selected} = ' selected';
1967 # no customer pricesgroup set
1968 if ($pkr->{price} == $pkr->{default_sellprice}) {
1970 $pkr->{price} = $form->{"sellprice_$i"};
1974 $form->{"sellprice_$i"} = $pkr->{price};
1977 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1978 $pkr->{price} = $form->{"sellprice_$i"};
1979 $pkr->{selected} = ' selected';
1983 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1984 if ($selectedpricegroup_id ne $pricegroup_old) {
1985 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1986 $pkr->{selected} = ' selected';
1988 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1989 if ($pkr->{pricegroup_id} == 0) {
1990 $pkr->{price} = $form->{"sellprice_$i"};
1991 $pkr->{selected} = ' selected';
1993 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1994 $pkr->{selected} = ' selected';
1995 if ( ($pkr->{pricegroup_id} == 0)
1996 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1997 # $pkr->{price} = $form->{"sellprice_$i"};
1999 $pkr->{price} = $form->{"sellprice_$i"};
2003 push @{ $form->{PRICES}{$i} }, $pkr;
2006 $form->{"basefactor_$i"} *= $basefactor;
2015 $main::lxdebug->leave_sub();
2019 $main::lxdebug->enter_sub();
2021 my ($self, $myconfig, $form, $table) = @_;
2023 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2025 # make sure there's no funny stuff in $table
2026 # ToDO: die when this happens and throw an error
2027 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2029 my $dbh = $form->dbconnect($myconfig);
2031 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2032 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2036 $main::lxdebug->leave_sub();
2042 $main::lxdebug->enter_sub();
2044 my ($self, $myconfig, $form, $table, $id) = @_;
2046 $main::lxdebug->leave_sub() and return 0 unless ($id);
2048 # make sure there's no funny stuff in $table
2049 # ToDO: die when this happens and throw an error
2050 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2052 my $dbh = $form->dbconnect($myconfig);
2054 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2055 my ($result) = selectrow_query($form, $dbh, $query, $id);
2059 $main::lxdebug->leave_sub();