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);
47 $main::lxdebug->enter_sub();
49 my ($self, $myconfig, $form, $locale) = @_;
51 $form->{duedate} ||= $form->{invdate};
54 my $dbh = $form->dbconnect($myconfig);
57 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
58 ($form->{terms}) = selectrow_query($form, $dbh, $query);
60 my (@project_ids, %projectnumbers);
62 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
64 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
67 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
68 $price_factors{$pfac->{id}} = $pfac;
70 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
73 # sort items by partsgroup
74 for $i (1 .. $form->{rowcount}) {
76 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
77 $partsgroup = $form->{"partsgroup_$i"};
79 push @partsgroup, [$i, $partsgroup];
80 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
84 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
85 join(", ", map({ "?" } @project_ids)) . ")";
86 $sth = $dbh->prepare($query);
87 $sth->execute(@project_ids) ||
88 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
89 while (my $ref = $sth->fetchrow_hashref()) {
90 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
95 $form->{"globalprojectnumber"} =
96 $projectnumbers{$form->{"globalproject_id"}};
103 my %oid = ('Pg' => 'oid',
104 'Oracle' => 'rowid');
106 # sort items by partsgroup
107 for $i (1 .. $form->{rowcount}) {
109 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
110 $partsgroup = $form->{"partsgroup_$i"};
112 push @partsgroup, [$i, $partsgroup];
125 my $nodiscount_subtotal = 0;
126 my $discount_subtotal = 0;
128 my $subtotal_header = 0;
132 qw(runningnumber number description longdescription qty ship unit bin
133 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
134 partnotes serialnumber reqdate sellprice listprice netprice
135 discount p_discount discount_sub nodiscount_sub
136 linetotal nodiscount_linetotal tax_rate projectnumber
137 price_factor price_factor_name);
140 qw(taxbase tax taxdescription taxrate taxnumber);
142 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
145 if ($item->[1] ne $sameitem) {
146 push(@{ $form->{description} }, qq|$item->[1]|);
147 $sameitem = $item->[1];
149 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
152 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
154 if ($form->{"id_$i"} != 0) {
156 # add number, description and qty to $form->{number},
157 if ($form->{"subtotal_$i"} && !$subtotal_header) {
158 $subtotal_header = $i;
159 $position = int($position);
162 } elsif ($subtotal_header) {
164 $position = int($position);
165 $position = $position.".".$subposition;
167 $position = int($position);
171 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
173 push @{ $form->{runningnumber} }, $position;
174 push @{ $form->{number} }, $form->{"partnumber_$i"};
175 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
176 push @{ $form->{bin} }, $form->{"bin_$i"};
177 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
178 push @{ $form->{description} }, $form->{"description_$i"};
179 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
180 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
181 push @{ $form->{unit} }, $form->{"unit_$i"};
182 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
183 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
184 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
185 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
186 push @{ $form->{invnumber} }, $form->{"invnumber"};
187 push @{ $form->{invdate} }, $form->{"invdate"};
188 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
189 push @{ $form->{price_factor_name} }, $price_factor->{description};
191 if ($form->{lizenzen}) {
192 if ($form->{"licensenumber_$i"}) {
193 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
194 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
195 push(@{ $form->{licensenumber} }, $licensenumber);
196 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
199 push(@{ $form->{licensenumber} }, "");
200 push(@{ $form->{validuntil} }, "");
205 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
207 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
208 my ($dec) = ($sellprice =~ /\.(\d+)/);
209 my $decimalplaces = max 2, length($dec);
211 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
212 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
213 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
214 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
216 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
218 $linetotal = ($linetotal != 0) ? $linetotal : '';
220 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
221 push @{ $form->{p_discount} }, $form->{"discount_$i"};
223 $form->{total} += $linetotal;
224 $form->{nodiscount_total} += $nodiscount_linetotal;
225 $form->{discount_total} += $discount;
227 if ($subtotal_header) {
228 $discount_subtotal += $linetotal;
229 $nodiscount_subtotal += $nodiscount_linetotal;
232 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
233 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
234 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
236 $discount_subtotal = 0;
237 $nodiscount_subtotal = 0;
238 $subtotal_header = 0;
241 push @{ $form->{discount_sub} }, "";
242 push @{ $form->{nodiscount_sub} }, "";
245 if (!$form->{"discount_$i"}) {
246 $nodiscount += $linetotal;
249 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
250 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
252 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
254 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
258 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
260 if ($form->{taxincluded}) {
263 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
264 $taxbase = $linetotal - $taxamount;
266 $taxamount = $linetotal * $taxrate;
267 $taxbase = $linetotal;
270 if ($form->round_amount($taxrate, 7) == 0) {
271 if ($form->{taxincluded}) {
272 foreach $item (@taxaccounts) {
274 $form->round_amount($linetotal * $form->{"${item}_rate"} /
275 (1 + abs($form->{"${item}_rate"})),
278 $taxaccounts{$item} += $taxamount;
279 $taxdiff += $taxamount;
281 $taxbase{$item} += $taxbase;
283 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
285 foreach $item (@taxaccounts) {
286 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
287 $taxbase{$item} += $taxbase;
291 foreach $item (@taxaccounts) {
292 $taxaccounts{$item} +=
293 $taxamount * $form->{"${item}_rate"} / $taxrate;
294 $taxbase{$item} += $taxbase;
297 $tax_rate = $taxrate * 100;
298 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
299 if ($form->{"assembly_$i"}) {
302 # get parts and push them onto the stack
304 if ($form->{groupitems}) {
306 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
308 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
312 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
314 JOIN parts p ON (a.parts_id = p.id)
315 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
316 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
317 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
319 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
320 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
321 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
322 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
323 push(@{ $form->{description} }, $sameitem);
326 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
328 push(@{ $form->{description} },
329 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
331 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
332 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
340 foreach my $item (sort keys %taxaccounts) {
341 push(@{ $form->{taxbase} },
342 $form->format_amount($myconfig, $taxbase{$item}, 2));
344 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
346 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
347 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
348 push(@{ $form->{taxrate} },
349 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
350 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
353 for my $i (1 .. $form->{paidaccounts}) {
354 if ($form->{"paid_$i"}) {
355 push(@{ $form->{payment} }, $form->{"paid_$i"});
356 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
357 push(@{ $form->{paymentaccount} }, $description);
358 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
359 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
361 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
364 if($form->{taxincluded}) {
365 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
368 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
371 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
372 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
373 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
374 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
376 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
377 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
379 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
380 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
382 $form->set_payment_options($myconfig, $form->{invdate});
384 $form->{username} = $myconfig->{name};
388 $main::lxdebug->leave_sub();
391 sub project_description {
392 $main::lxdebug->enter_sub();
394 my ($self, $dbh, $id) = @_;
396 my $query = qq|SELECT description FROM project WHERE id = ?|;
397 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
399 $main::lxdebug->leave_sub();
404 sub customer_details {
405 $main::lxdebug->enter_sub();
407 my ($self, $myconfig, $form, @wanted_vars) = @_;
409 # connect to database
410 my $dbh = $form->dbconnect($myconfig);
412 # get contact id, set it if nessessary
415 my @values = (conv_i($form->{customer_id}));
418 if ($form->{cp_id}) {
419 $where = qq| AND (cp.cp_id = ?) |;
420 push(@values, conv_i($form->{cp_id}));
423 # get rest for the customer
425 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
426 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
428 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
429 WHERE (ct.id = ?) $where
432 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
434 # remove id and taxincluded before copy back
435 delete @$ref{qw(id taxincluded)};
437 @wanted_vars = grep({ $_ } @wanted_vars);
438 if (scalar(@wanted_vars) > 0) {
440 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
441 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
444 map { $form->{$_} = $ref->{$_} } keys %$ref;
446 if ($form->{delivery_customer_id}) {
448 qq|SELECT *, notes as customernotes
452 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
454 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
457 if ($form->{delivery_vendor_id}) {
459 qq|SELECT *, notes as customernotes
463 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
465 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
468 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
470 'trans_id' => $form->{customer_id});
471 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
475 $main::lxdebug->leave_sub();
479 $main::lxdebug->enter_sub();
481 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
483 # connect to database, turn off autocommit
484 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
486 my ($query, $sth, $null, $project_id, @values);
487 my $exchangerate = 0;
489 if (!$form->{employee_id}) {
490 $form->get_employee($dbh);
493 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
495 ($null, $form->{department_id}) = split(/--/, $form->{department});
497 my $all_units = AM->retrieve_units($myconfig, $form);
499 if (!$payments_only) {
501 &reverse_invoice($dbh, $form);
504 $query = qq|SELECT nextval('glid')|;
505 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
507 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
508 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
510 if (!$form->{invnumber}) {
512 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
513 "cnnumber" : "invnumber", $dbh);
518 my ($netamount, $invoicediff) = (0, 0);
519 my ($amount, $linetotal, $lastincomeaccno);
521 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
522 my $defaultcurrency = (split m/:/, $currencies)[0];
524 if ($form->{currency} eq $defaultcurrency) {
525 $form->{exchangerate} = 1;
528 $form->check_exchangerate($myconfig, $form->{currency},
529 $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 # adjust parts onhand quantity
650 if ($form->{"assembly_$i"}) {
652 # do not update if assembly consists of all services
654 qq|SELECT sum(p.inventory_accno_id)
656 JOIN assembly a ON (a.parts_id = p.id)
658 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
660 if ($sth->fetchrow_array) {
661 $form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
662 $baseqty * -1, $form->{"id_$i"})
663 unless $form->{shipped};
667 # record assembly item as allocated
668 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
670 $form->update_balance($dbh, "parts", "onhand", qq|id = ?|,
671 $baseqty * -1, $form->{"id_$i"})
672 unless $form->{shipped};
674 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
678 # get pricegroup_id and save it
679 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
682 # save detail record in invoice table
684 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
685 sellprice, fxsellprice, discount, allocated, assemblyitem,
686 unit, deliverydate, project_id, serialnumber, pricegroup_id,
687 ordnumber, transdate, cusordnumber, base_qty, subtotal,
688 marge_percent, marge_total, lastcost,
689 price_factor_id, price_factor, marge_price_factor)
690 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
691 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
693 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
694 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
695 $form->{"sellprice_$i"}, $fxsellprice,
696 $form->{"discount_$i"}, $allocated, 'f',
697 $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
698 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
699 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
700 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
701 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
702 $form->{"lastcost_$i"},
703 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
704 conv_i($form->{"marge_price_factor_$i"}));
705 do_query($form, $dbh, $query, @values);
707 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
709 qq|INSERT INTO licenseinvoice (trans_id, license_id)
710 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
711 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
712 do_query($form, $dbh, $query, @values);
717 $form->{datepaid} = $form->{invdate};
719 # total payments, don't move we need it here
720 for my $i (1 .. $form->{paidaccounts}) {
721 if ($form->{type} eq "credit_note") {
722 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
724 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
726 $form->{paid} += $form->{"paid_$i"};
727 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
730 my ($tax, $diff) = (0, 0);
732 $netamount = $form->round_amount($netamount, 2);
734 # figure out rounding errors for total amount vs netamount + taxes
735 if ($form->{taxincluded}) {
737 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
738 $diff += $amount - $netamount * $form->{exchangerate};
739 $netamount = $amount;
741 foreach my $item (split(/ /, $form->{taxaccounts})) {
742 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
743 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
744 $tax += $form->{amount}{ $form->{id} }{$item};
745 $netamount -= $form->{amount}{ $form->{id} }{$item};
748 $invoicediff += $diff;
749 ######## this only applies to tax included
750 if ($lastincomeaccno) {
751 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
755 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
756 $diff = $amount - $netamount * $form->{exchangerate};
757 $netamount = $amount;
758 foreach my $item (split(/ /, $form->{taxaccounts})) {
759 $form->{amount}{ $form->{id} }{$item} =
760 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
763 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
766 $amount - $form->{amount}{ $form->{id} }{$item} *
767 $form->{exchangerate};
768 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
769 $tax += $form->{amount}{ $form->{id} }{$item};
773 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
775 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
778 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
780 # update exchangerate
781 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
782 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
783 $form->{exchangerate}, 0);
786 $project_id = conv_i($form->{"globalproject_id"});
788 foreach my $trans_id (keys %{ $form->{amount} }) {
789 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
790 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
792 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
794 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
796 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
797 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
798 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
799 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
800 do_query($form, $dbh, $query, @values);
801 $form->{amount}{$trans_id}{$accno} = 0;
805 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
806 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
808 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
810 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
811 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
812 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
813 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
814 do_query($form, $dbh, $query, @values);
819 # deduct payment differences from diff
820 for my $i (1 .. $form->{paidaccounts}) {
821 if ($form->{"paid_$i"} != 0) {
823 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
824 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
828 # record payments and offsetting AR
829 if (!$form->{storno}) {
830 for my $i (1 .. $form->{paidaccounts}) {
832 next if ($form->{"paid_$i"} == 0);
834 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
835 $form->{"datepaid_$i"} = $form->{invdate}
836 unless ($form->{"datepaid_$i"});
837 $form->{datepaid} = $form->{"datepaid_$i"};
841 if ($form->{currency} eq $defaultcurrency) {
842 $form->{"exchangerate_$i"} = 1;
845 $form->check_exchangerate($myconfig, $form->{currency},
846 $form->{"datepaid_$i"}, 'buy');
848 $form->{"exchangerate_$i"} =
849 $exchangerate ? $exchangerate
850 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
854 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
856 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
858 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
859 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
860 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
861 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
862 do_query($form, $dbh, $query, @values);
866 $form->{"paid_$i"} *= -1;
869 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
870 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
871 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
872 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
873 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
874 do_query($form, $dbh, $query, @values);
876 # exchangerate difference
877 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
878 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
882 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
883 $form->{"exchangerate_$i"};
885 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
888 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
894 # update exchange rate
895 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
896 $form->update_exchangerate($dbh, $form->{currency},
897 $form->{"datepaid_$i"},
898 $form->{"exchangerate_$i"}, 0);
902 } else { # if (!$form->{storno})
903 $form->{marge_total} *= -1;
906 if ($payments_only) {
907 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
908 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
910 if (!$provided_dbh) {
915 $main::lxdebug->leave_sub();
919 # record exchange rate differences and gains/losses
920 foreach my $accno (keys %{ $form->{fx} }) {
921 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
923 ($form->{fx}{$accno}{$transdate} =
924 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
929 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
930 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
931 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
932 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
933 do_query($form, $dbh, $query, @values);
938 $amount = $netamount + $tax;
941 $query = qq|UPDATE ar set
942 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
943 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
944 amount = ?, netamount = ?, paid = ?, datepaid = ?,
945 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
946 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
947 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
948 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
949 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
950 cp_id = ?, marge_total = ?, marge_percent = ?,
951 globalproject_id = ?, delivery_customer_id = ?,
952 transaction_description = ?, delivery_vendor_id = ?
954 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
955 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
956 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
957 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
958 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
959 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
960 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
961 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
962 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
963 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
964 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
965 conv_i($form->{"id"}));
966 do_query($form, $dbh, $query, @values);
968 if($form->{"formname"} eq "credit_note") {
969 for my $i (1 .. $form->{rowcount}) {
970 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
971 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
972 do_query($form, $dbh, $query, @values);
976 if ($form->{storno}) {
979 paid = paid + amount,
981 intnotes = ? || intnotes
983 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
984 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
988 $form->{name} = $form->{customer};
989 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
991 if (!$form->{shipto_id}) {
992 $form->add_shipto($dbh, $form->{id}, "AR");
995 # save printed, emailed and queued
996 $form->save_status($dbh);
998 Common::webdav_folder($form) if ($main::webdav);
1001 if (!$provided_dbh) {
1006 $main::lxdebug->leave_sub();
1011 sub _delete_payments {
1012 $main::lxdebug->enter_sub();
1014 my ($self, $form, $dbh) = @_;
1018 # Delete old payment entries from acc_trans.
1022 WHERE (trans_id = ?) AND fx_transaction
1028 LEFT JOIN chart c ON (at.chart_id = c.id)
1029 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1030 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1035 LEFT JOIN chart c ON (at.chart_id = c.id)
1036 WHERE (trans_id = ?)
1037 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1040 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1043 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1044 do_query($form, $dbh, $query);
1047 $main::lxdebug->leave_sub();
1051 $main::lxdebug->enter_sub();
1053 my ($self, $myconfig, $form, $locale) = @_;
1055 # connect to database, turn off autocommit
1056 my $dbh = $form->dbconnect_noauto($myconfig);
1058 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1060 $old_form = save_form();
1062 # Delete all entries in acc_trans from prior payments.
1063 $self->_delete_payments($form, $dbh);
1065 # Save the new payments the user made before cleaning up $form.
1066 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1068 # Clean up $form so that old content won't tamper the results.
1069 %keep_vars = map { $_, 1 } qw(login password id);
1070 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1072 # Retrieve the invoice from the database.
1073 $self->retrieve_invoice($myconfig, $form);
1075 # Set up the content of $form in the way that IS::post_invoice() expects.
1076 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1078 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1079 $item = $form->{invoice_details}->[$row - 1];
1081 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1083 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1086 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1088 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1090 # Restore the payment options from the user input.
1091 map { $form->{$_} = $payments{$_} } keys %payments;
1093 # Get the AR accno (which is normally done by Form::create_links()).
1097 LEFT JOIN chart c ON (at.chart_id = c.id)
1098 WHERE (trans_id = ?)
1099 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1103 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1105 # Post the new payments.
1106 $self->post_invoice($myconfig, $form, $dbh, 1);
1108 restore_form($old_form);
1110 my $rc = $dbh->commit();
1113 $main::lxdebug->leave_sub();
1118 sub process_assembly {
1119 $main::lxdebug->enter_sub();
1121 my ($dbh, $form, $id, $totalqty) = @_;
1124 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1125 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1127 JOIN parts p ON (a.parts_id = p.id)
1129 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1131 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1135 $ref->{inventory_accno_id} *= 1;
1136 $ref->{expense_accno_id} *= 1;
1138 # multiply by number of assemblies
1139 $ref->{qty} *= $totalqty;
1141 if ($ref->{assembly}) {
1142 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1145 if ($ref->{inventory_accno_id}) {
1146 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1150 # save detail record for individual assembly item in invoice table
1152 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1153 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1154 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1155 do_query($form, $dbh, $query, @values);
1161 $main::lxdebug->leave_sub();
1165 $main::lxdebug->enter_sub();
1167 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1168 $form->{taxzone_id} *=1;
1169 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1170 my $taxzone_id = $form->{"taxzone_id"} * 1;
1172 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1173 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1174 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1175 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1176 FROM invoice i, parts p
1177 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1178 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1179 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1180 WHERE (i.parts_id = p.id)
1181 AND (i.parts_id = ?)
1182 AND ((i.base_qty + i.allocated) < 0)
1184 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1189 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1190 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1194 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1196 # total expenses and inventory
1197 # sellprice is the cost of the item
1198 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1201 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1203 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1204 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1205 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1207 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1208 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1214 last if (($totalqty -= $qty) <= 0);
1219 $main::lxdebug->leave_sub();
1224 sub reverse_invoice {
1225 $main::lxdebug->enter_sub();
1227 my ($dbh, $form) = @_;
1229 # reverse inventory items
1231 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1233 JOIN parts p ON (i.parts_id = p.id)
1234 WHERE i.trans_id = ?|;
1235 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1237 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1239 if ($ref->{inventory_accno_id} || $ref->{assembly}) {
1241 # if the invoice item is not an assemblyitem adjust parts onhand
1242 if (!$ref->{assemblyitem}) {
1244 # adjust onhand in parts table
1245 $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
1248 # loop if it is an assembly
1249 next if ($ref->{assembly});
1251 # de-allocated purchases
1253 qq|SELECT i.id, i.trans_id, i.allocated
1255 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1256 ORDER BY i.trans_id DESC|;
1257 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1259 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1261 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1262 $qty = $inhref->{allocated};
1266 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1268 last if (($ref->{qty} -= $qty) <= 0);
1277 @values = (conv_i($form->{id}));
1278 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1279 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1281 if ($form->{lizenzen}) {
1283 qq|DELETE FROM licenseinvoice
1284 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1285 do_query($form, $dbh, $query, @values);
1288 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1290 $main::lxdebug->leave_sub();
1293 sub delete_invoice {
1294 $main::lxdebug->enter_sub();
1296 my ($self, $myconfig, $form, $spool) = @_;
1298 # connect to database
1299 my $dbh = $form->dbconnect_noauto($myconfig);
1301 &reverse_invoice($dbh, $form);
1303 my @values = (conv_i($form->{id}));
1306 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1308 # delete spool files
1309 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1311 # delete status entries
1312 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1314 my $rc = $dbh->commit;
1318 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1321 $main::lxdebug->leave_sub();
1326 sub retrieve_invoice {
1327 $main::lxdebug->enter_sub();
1329 my ($self, $myconfig, $form) = @_;
1331 # connect to database
1332 my $dbh = $form->dbconnect_noauto($myconfig);
1334 my ($sth, $ref, $query);
1336 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1340 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1341 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1342 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1343 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1344 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1345 d.curr AS currencies
1349 $ref = selectfirst_hashref_query($form, $dbh, $query);
1350 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1353 my $id = conv_i($form->{id});
1358 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1359 a.orddate, a.quodate, a.globalproject_id,
1360 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1361 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1362 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1363 a.employee_id, a.salesman_id, a.payment_id,
1364 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1365 a.transaction_description,
1366 a.marge_total, a.marge_percent,
1369 LEFT JOIN employee e ON (e.id = a.employee_id)
1371 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1372 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1375 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1378 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1379 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1381 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1383 foreach my $vc (qw(customer vendor)) {
1384 next if !$form->{"delivery_${vc}_id"};
1385 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1388 # get printed, emailed
1389 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1390 $sth = prepare_execute_query($form, $dbh, $query, $id);
1392 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1393 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1394 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1395 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1398 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1400 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1401 : $form->{invdate} ? $dbh->quote($form->{invdate})
1405 my $taxzone_id = $form->{taxzone_id} *= 1;
1406 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1408 # retrieve individual items
1411 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1412 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1413 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1415 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate,
1416 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1417 i.price_factor_id, i.price_factor, i.marge_price_factor,
1418 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1419 pr.projectnumber, pg.partsgroup, prg.pricegroup
1422 LEFT JOIN parts p ON (i.parts_id = p.id)
1423 LEFT JOIN project pr ON (i.project_id = pr.id)
1424 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1425 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1427 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1428 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1429 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1431 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1433 $sth = prepare_execute_query($form, $dbh, $query, $id);
1435 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1436 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1437 delete($ref->{"part_inventory_accno_id"});
1439 foreach my $type (qw(inventory income expense)) {
1440 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1441 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1442 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1446 # get tax rates and description
1447 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1449 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1450 LEFT JOIN chart c ON (c.id = t.chart_id)
1452 (SELECT tk.tax_id FROM taxkeys tk
1453 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1454 AND startdate <= date($transdate)
1455 ORDER BY startdate DESC LIMIT 1)
1457 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1458 $ref->{taxaccounts} = "";
1460 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1462 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1466 $ref->{taxaccounts} .= "$ptr->{accno} ";
1468 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1469 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1470 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1471 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1472 $form->{taxaccounts} .= "$ptr->{accno} ";
1477 if ($form->{lizenzen}) {
1478 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1479 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1480 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1483 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1485 chop $ref->{taxaccounts};
1486 push @{ $form->{invoice_details} }, $ref;
1491 Common::webdav_folder($form) if ($main::webdav);
1494 my $rc = $dbh->commit;
1497 $main::lxdebug->leave_sub();
1503 $main::lxdebug->enter_sub();
1505 my ($self, $myconfig, $form) = @_;
1507 # connect to database
1508 my $dbh = $form->dbconnect($myconfig);
1510 my $dateformat = $myconfig->{dateformat};
1511 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1513 my (@values, $duedate, $ref, $query);
1515 if ($form->{invdate}) {
1516 $duedate = "to_date(?, '$dateformat')";
1517 push @values, $form->{invdate};
1519 $duedate = "current_date";
1522 my $cid = conv_i($form->{customer_id});
1525 if ($form->{payment_id}) {
1526 $payment_id = "(pt.id = ?) OR";
1527 push @values, conv_i($form->{payment_id});
1533 c.name AS customer, c.discount, c.creditlimit, c.terms,
1534 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1535 c.street, c.zipcode, c.city, c.country,
1536 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1537 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1538 b.discount AS tradediscount, b.description AS business
1540 LEFT JOIN business b ON (b.id = c.business_id)
1541 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1544 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1545 map { $form->{$_} = $ref->{$_} } keys %$ref;
1548 qq|SELECT sum(amount - paid) AS dunning_amount
1550 WHERE (paid < amount)
1551 AND (customer_id = ?)
1552 AND (dunning_config_id IS NOT NULL)|;
1553 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1554 map { $form->{$_} = $ref->{$_} } keys %$ref;
1557 qq|SELECT dnn.dunning_description AS max_dunning_level
1558 FROM dunning_config dnn
1559 WHERE id IN (SELECT dunning_config_id
1561 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1562 ORDER BY dunning_level DESC LIMIT 1|;
1563 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1564 map { $form->{$_} = $ref->{$_} } keys %$ref;
1566 $form->{creditremaining} = $form->{creditlimit};
1567 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1568 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1569 $form->{creditremaining} -= $value;
1573 (SELECT e.buy FROM exchangerate e
1574 WHERE e.curr = o.curr
1575 AND e.transdate = o.transdate)
1577 WHERE o.customer_id = ?
1578 AND o.quotation = '0'
1579 AND o.closed = '0'|;
1580 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1582 while (my ($amount, $exch) = $sth->fetchrow_array) {
1583 $exch = 1 unless $exch;
1584 $form->{creditremaining} -= $amount * $exch;
1588 # get shipto if we did not converted an order or invoice
1589 if (!$form->{shipto}) {
1590 map { delete $form->{$_} }
1591 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1592 shiptostreet shiptozipcode shiptocity shiptocountry
1593 shiptocontact shiptophone shiptofax shiptoemail);
1595 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1596 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1598 map { $form->{$_} = $ref->{$_} } keys %$ref;
1601 # setup last accounts used for this customer
1602 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1604 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1606 JOIN acc_trans ac ON (ac.chart_id = c.id)
1607 JOIN ar a ON (a.id = ac.trans_id)
1608 WHERE a.customer_id = ?
1609 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1610 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1611 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1614 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1615 if ($ref->{category} eq 'I') {
1617 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1619 if ($form->{initial_transdate}) {
1621 qq|SELECT tk.tax_id, t.rate
1623 LEFT JOIN tax t ON tk.tax_id = t.id
1624 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1625 ORDER BY tk.startdate DESC
1627 my ($tax_id, $rate) =
1628 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1629 $form->{initial_transdate});
1630 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1633 if ($ref->{category} eq 'A') {
1634 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1638 $form->{rowcount} = $i if ($i && !$form->{type});
1643 $main::lxdebug->leave_sub();
1647 $main::lxdebug->enter_sub();
1649 my ($self, $myconfig, $form) = @_;
1651 # connect to database
1652 my $dbh = $form->dbconnect($myconfig);
1654 my $i = $form->{rowcount};
1656 my $where = qq|NOT p.obsolete = '1'|;
1659 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1660 my ($table, $field) = split m/\./, $column;
1661 next if !$form->{"${field}_${i}"};
1662 $where .= qq| AND lower(${column}) ILIKE ?|;
1663 push @values, '%' . $form->{"${field}_${i}"} . '%';
1666 if ($form->{"description_$i"}) {
1667 $where .= qq| ORDER BY p.description|;
1669 $where .= qq| ORDER BY p.partnumber|;
1673 if ($form->{type} eq "invoice") {
1675 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1676 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1680 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1684 my $taxzone_id = $form->{taxzone_id} * 1;
1685 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1689 p.id, p.partnumber, p.description, p.sellprice,
1690 p.listprice, p.inventory_accno_id, p.lastcost,
1692 c1.accno AS inventory_accno,
1693 c1.new_chart_id AS inventory_new_chart,
1694 date($transdate) - c1.valid_from AS inventory_valid,
1696 c2.accno AS income_accno,
1697 c2.new_chart_id AS income_new_chart,
1698 date($transdate) - c2.valid_from AS income_valid,
1700 c3.accno AS expense_accno,
1701 c3.new_chart_id AS expense_new_chart,
1702 date($transdate) - c3.valid_from AS expense_valid,
1704 p.unit, p.assembly, p.bin, p.onhand,
1705 p.notes AS partnotes, p.notes AS longdescription,
1706 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1709 pfac.factor AS price_factor,
1714 LEFT JOIN chart c1 ON
1715 ((SELECT inventory_accno_id
1716 FROM buchungsgruppen
1717 WHERE id = p.buchungsgruppen_id) = c1.id)
1718 LEFT JOIN chart c2 ON
1719 ((SELECT income_accno_id_${taxzone_id}
1720 FROM buchungsgruppen
1721 WHERE id = p.buchungsgruppen_id) = c2.id)
1722 LEFT JOIN chart c3 ON
1723 ((SELECT expense_accno_id_${taxzone_id}
1724 FROM buchungsgruppen
1725 WHERE id = p.buchungsgruppen_id) = c3.id)
1726 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1727 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1729 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1731 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1733 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1734 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1735 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1736 if (!$ref->{inventory_accno_id}) {
1737 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1739 delete($ref->{inventory_accno_id});
1741 foreach my $type (qw(inventory income expense)) {
1742 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1744 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1747 ($ref->{"${type}_accno"},
1748 $ref->{"${type}_new_chart"},
1749 $ref->{"${type}_valid"})
1750 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1754 if ($form->{payment_id} eq "") {
1755 $form->{payment_id} = $form->{part_payment_id};
1758 # get tax rates and description
1759 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1761 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1763 LEFT JOIN chart c ON (c.id = t.chart_id)
1767 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1769 ORDER BY startdate DESC
1772 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1773 $stw = $dbh->prepare($query);
1774 $stw->execute(@values) || $form->dberror($query);
1776 $ref->{taxaccounts} = "";
1778 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1780 # if ($customertax{$ref->{accno}}) {
1781 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1785 $ref->{taxaccounts} .= "$ptr->{accno} ";
1787 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1788 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1789 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1790 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1791 $form->{taxaccounts} .= "$ptr->{accno} ";
1797 chop $ref->{taxaccounts};
1798 if ($form->{language_id}) {
1800 qq|SELECT tr.translation, tr.longdescription
1802 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1803 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1804 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1805 if ($translation ne "") {
1806 $ref->{description} = $translation;
1807 $ref->{longdescription} = $longdescription;
1811 qq|SELECT tr.translation, tr.longdescription
1813 WHERE tr.language_id IN
1816 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1819 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1820 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1821 if ($translation ne "") {
1822 $ref->{description} = $translation;
1823 $ref->{longdescription} = $longdescription;
1828 push @{ $form->{item_list} }, $ref;
1830 if ($form->{lizenzen}) {
1831 if ($ref->{inventory_accno} > 0) {
1835 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1836 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1837 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1838 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1847 $main::lxdebug->leave_sub();
1850 ##########################
1851 # get pricegroups from database
1852 # build up selected pricegroup
1853 # if an exchange rate - change price
1856 sub get_pricegroups_for_parts {
1858 $main::lxdebug->enter_sub();
1860 my ($self, $myconfig, $form) = @_;
1862 my $dbh = $form->dbconnect($myconfig);
1864 $form->{"PRICES"} = {};
1868 my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
1869 my $service_units = AM->retrieve_units($myconfig, $form, "service");
1870 my $all_units = AM->retrieve_units($myconfig, $form);
1871 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1872 $form->{"PRICES"}{$i} = [];
1874 $id = $form->{"id_$i"};
1876 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1878 $id = $form->{"new_id_$i"};
1881 ($price, $selectedpricegroup_id) = split(/--/,
1882 $form->{"sellprice_pg_$i"});
1884 $pricegroup_old = $form->{"pricegroup_old_$i"};
1885 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1886 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1888 $price_new = $form->{"price_new_$i"};
1889 $price_old = $form->{"price_old_$i"};
1891 if (!$form->{"unit_old_$i"}) {
1892 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1893 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1894 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1895 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1898 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1899 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1900 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1902 my $check_units = $form->{"inventory_accno_$i"} ? $dimension_units : $service_units;
1903 if (!$check_units->{$form->{"selected_unit_$i"}} ||
1904 ($check_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1905 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1906 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1907 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1908 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1909 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1914 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1915 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1916 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1917 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1918 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1922 if (!$form->{"basefactor_$i"}) {
1923 $form->{"basefactor_$i"} = 1;
1929 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1930 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1940 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1942 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1943 'selected' AS selected
1946 ORDER BY pricegroup|;
1947 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1948 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1950 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1952 $pkr->{selected} = '';
1954 # if there is an exchange rate change price
1955 if (($form->{exchangerate} * 1) != 0) {
1957 $pkr->{price} /= $form->{exchangerate};
1960 $pkr->{price} *= $form->{"basefactor_$i"};
1962 $pkr->{price} *= $basefactor;
1964 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1966 if ($selectedpricegroup_id eq undef) {
1967 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1969 $pkr->{selected} = ' selected';
1971 # no customer pricesgroup set
1972 if ($pkr->{price} == $pkr->{default_sellprice}) {
1974 $pkr->{price} = $form->{"sellprice_$i"};
1978 $form->{"sellprice_$i"} = $pkr->{price};
1981 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1982 $pkr->{price} = $form->{"sellprice_$i"};
1983 $pkr->{selected} = ' selected';
1987 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1988 if ($selectedpricegroup_id ne $pricegroup_old) {
1989 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1990 $pkr->{selected} = ' selected';
1992 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1993 if ($pkr->{pricegroup_id} == 0) {
1994 $pkr->{price} = $form->{"sellprice_$i"};
1995 $pkr->{selected} = ' selected';
1997 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1998 $pkr->{selected} = ' selected';
1999 if ( ($pkr->{pricegroup_id} == 0)
2000 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2001 # $pkr->{price} = $form->{"sellprice_$i"};
2003 $pkr->{price} = $form->{"sellprice_$i"};
2007 push @{ $form->{PRICES}{$i} }, $pkr;
2010 $form->{"basefactor_$i"} *= $basefactor;
2019 $main::lxdebug->leave_sub();
2023 $main::lxdebug->enter_sub();
2025 my ($self, $myconfig, $form, $table) = @_;
2027 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2029 # make sure there's no funny stuff in $table
2030 # ToDO: die when this happens and throw an error
2031 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2033 my $dbh = $form->dbconnect($myconfig);
2035 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2036 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2040 $main::lxdebug->leave_sub();
2046 $main::lxdebug->enter_sub();
2048 my ($self, $myconfig, $form, $table, $id) = @_;
2050 $main::lxdebug->leave_sub() and return 0 unless ($id);
2052 # make sure there's no funny stuff in $table
2053 # ToDO: die when this happens and throw an error
2054 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2056 my $dbh = $form->dbconnect($myconfig);
2058 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2059 my ($result) = selectrow_query($form, $dbh, $query, $id);
2063 $main::lxdebug->leave_sub();