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 #======================================================================
39 use List::Util qw(max);
52 $main::lxdebug->enter_sub();
54 my ($self, $myconfig, $form, $locale) = @_;
56 $form->{duedate} ||= $form->{invdate};
59 my $dbh = $form->dbconnect($myconfig);
62 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
63 ($form->{terms}) = selectrow_query($form, $dbh, $query);
65 my (@project_ids, %projectnumbers);
67 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
69 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
72 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
73 $price_factors{$pfac->{id}} = $pfac;
75 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
78 # sort items by partsgroup
79 for my $i (1 .. $form->{rowcount}) {
81 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
82 # $partsgroup = $form->{"partsgroup_$i"};
84 # push @partsgroup, [$i, $partsgroup];
85 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
89 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
90 join(", ", map({ "?" } @project_ids)) . ")";
91 $sth = $dbh->prepare($query);
92 $sth->execute(@project_ids) ||
93 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
94 while (my $ref = $sth->fetchrow_hashref()) {
95 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $form->{"globalprojectnumber"} =
101 $projectnumbers{$form->{"globalproject_id"}};
108 my %oid = ('Pg' => 'oid',
109 'Oracle' => 'rowid');
111 # sort items by partsgroup
112 for $i (1 .. $form->{rowcount}) {
114 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
115 $partsgroup = $form->{"partsgroup_$i"};
117 push @partsgroup, [$i, $partsgroup];
130 my $nodiscount_subtotal = 0;
131 my $discount_subtotal = 0;
133 my $subtotal_header = 0;
136 $form->{discount} = [];
138 IC->prepare_parts_for_printing();
141 qw(runningnumber number description longdescription qty ship unit bin
142 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
143 partnotes serialnumber reqdate sellprice listprice netprice
144 discount p_discount discount_sub nodiscount_sub
145 linetotal nodiscount_linetotal tax_rate projectnumber
146 price_factor price_factor_name);
149 qw(taxbase tax taxdescription taxrate taxnumber);
151 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
154 if ($item->[1] ne $sameitem) {
155 push(@{ $form->{description} }, qq|$item->[1]|);
156 $sameitem = $item->[1];
158 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
161 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
163 if ($form->{"id_$i"} != 0) {
165 # add number, description and qty to $form->{number},
166 if ($form->{"subtotal_$i"} && !$subtotal_header) {
167 $subtotal_header = $i;
168 $position = int($position);
171 } elsif ($subtotal_header) {
173 $position = int($position);
174 $position = $position.".".$subposition;
176 $position = int($position);
180 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
182 push @{ $form->{runningnumber} }, $position;
183 push @{ $form->{number} }, $form->{"partnumber_$i"};
184 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
185 push @{ $form->{bin} }, $form->{"bin_$i"};
186 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
187 push @{ $form->{description} }, $form->{"description_$i"};
188 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
189 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
190 push @{ $form->{unit} }, $form->{"unit_$i"};
191 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
192 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
193 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
194 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
195 push @{ $form->{invnumber} }, $form->{"invnumber"};
196 push @{ $form->{invdate} }, $form->{"invdate"};
197 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
198 push @{ $form->{price_factor_name} }, $price_factor->{description};
200 if ($form->{lizenzen}) {
201 if ($form->{"licensenumber_$i"}) {
202 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
203 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
204 push(@{ $form->{licensenumber} }, $licensenumber);
205 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
208 push(@{ $form->{licensenumber} }, "");
209 push(@{ $form->{validuntil} }, "");
214 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
216 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
217 my ($dec) = ($sellprice =~ /\.(\d+)/);
218 my $decimalplaces = max 2, length($dec);
220 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
221 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
222 my $linetotal = $form->round_amount($linetotal_exact, 2);
223 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
225 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
226 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
228 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
230 $linetotal = ($linetotal != 0) ? $linetotal : '';
232 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
233 push @{ $form->{p_discount} }, $form->{"discount_$i"};
235 $form->{total} += $linetotal;
236 $form->{nodiscount_total} += $nodiscount_linetotal;
237 $form->{discount_total} += $discount;
239 if ($subtotal_header) {
240 $discount_subtotal += $linetotal;
241 $nodiscount_subtotal += $nodiscount_linetotal;
244 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
245 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
246 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
248 $discount_subtotal = 0;
249 $nodiscount_subtotal = 0;
250 $subtotal_header = 0;
253 push @{ $form->{discount_sub} }, "";
254 push @{ $form->{nodiscount_sub} }, "";
257 if (!$form->{"discount_$i"}) {
258 $nodiscount += $linetotal;
261 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
262 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
264 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
266 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
270 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
272 if ($form->{taxincluded}) {
275 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
276 $taxbase = $linetotal - $taxamount;
278 $taxamount = $linetotal * $taxrate;
279 $taxbase = $linetotal;
282 if ($form->round_amount($taxrate, 7) == 0) {
283 if ($form->{taxincluded}) {
284 foreach my $accno (@taxaccounts) {
285 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
287 $taxaccounts{$accno} += $taxamount;
288 $taxdiff += $taxamount;
290 $taxbase{$accno} += $taxbase;
292 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
294 foreach my $accno (@taxaccounts) {
295 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
296 $taxbase{$accno} += $taxbase;
300 foreach my $accno (@taxaccounts) {
301 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
302 $taxbase{$accno} += $taxbase;
305 my $tax_rate = $taxrate * 100;
306 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
307 if ($form->{"assembly_$i"}) {
310 # get parts and push them onto the stack
312 if ($form->{groupitems}) {
314 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
316 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
320 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
322 JOIN parts p ON (a.parts_id = p.id)
323 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
324 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
325 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
327 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
328 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
329 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
330 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
331 push(@{ $form->{description} }, $sameitem);
334 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
336 push(@{ $form->{description} },
337 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
339 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
340 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
348 foreach my $item (sort keys %taxaccounts) {
349 push(@{ $form->{taxbase} },
350 $form->format_amount($myconfig, $taxbase{$item}, 2));
352 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
354 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
355 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
356 push(@{ $form->{taxrate} },
357 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
358 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
361 for my $i (1 .. $form->{paidaccounts}) {
362 if ($form->{"paid_$i"}) {
363 push(@{ $form->{payment} }, $form->{"paid_$i"});
364 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
365 push(@{ $form->{paymentaccount} }, $description);
366 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
367 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
369 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
372 if($form->{taxincluded}) {
373 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
376 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
379 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
380 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
381 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
382 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
384 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
385 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
387 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
388 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
390 $form->set_payment_options($myconfig, $form->{invdate});
392 $form->{username} = $myconfig->{name};
396 $main::lxdebug->leave_sub();
399 sub project_description {
400 $main::lxdebug->enter_sub();
402 my ($self, $dbh, $id) = @_;
403 my $form = \%main::form;
405 my $query = qq|SELECT description FROM project WHERE id = ?|;
406 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
408 $main::lxdebug->leave_sub();
413 sub customer_details {
414 $main::lxdebug->enter_sub();
416 my ($self, $myconfig, $form, @wanted_vars) = @_;
418 # connect to database
419 my $dbh = $form->dbconnect($myconfig);
421 # get contact id, set it if nessessary
424 my @values = (conv_i($form->{customer_id}));
427 if ($form->{cp_id}) {
428 $where = qq| AND (cp.cp_id = ?) |;
429 push(@values, conv_i($form->{cp_id}));
432 # get rest for the customer
434 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
435 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
437 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
438 WHERE (ct.id = ?) $where
441 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
443 # remove id and taxincluded before copy back
444 delete @$ref{qw(id taxincluded)};
446 @wanted_vars = grep({ $_ } @wanted_vars);
447 if (scalar(@wanted_vars) > 0) {
449 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
450 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
453 map { $form->{$_} = $ref->{$_} } keys %$ref;
455 if ($form->{delivery_customer_id}) {
457 qq|SELECT *, notes as customernotes
461 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
463 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
466 if ($form->{delivery_vendor_id}) {
468 qq|SELECT *, notes as customernotes
472 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
474 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
477 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
479 'trans_id' => $form->{customer_id});
480 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
484 $main::lxdebug->leave_sub();
488 $main::lxdebug->enter_sub();
490 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
492 # connect to database, turn off autocommit
493 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
495 my ($query, $sth, $null, $project_id, @values);
496 my $exchangerate = 0;
498 if (!$form->{employee_id}) {
499 $form->get_employee($dbh);
502 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
504 ($null, $form->{department_id}) = split(/--/, $form->{department});
506 my $all_units = AM->retrieve_units($myconfig, $form);
508 if (!$payments_only) {
510 &reverse_invoice($dbh, $form);
513 $query = qq|SELECT nextval('glid')|;
514 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
516 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
517 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
519 if (!$form->{invnumber}) {
521 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
522 "cnnumber" : "invnumber", $dbh);
527 my ($netamount, $invoicediff) = (0, 0);
528 my ($amount, $linetotal, $lastincomeaccno);
530 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
531 my $defaultcurrency = (split m/:/, $currencies)[0];
533 if ($form->{currency} eq $defaultcurrency) {
534 $form->{exchangerate} = 1;
536 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
539 $form->{exchangerate} =
542 : $form->parse_amount($myconfig, $form->{exchangerate});
544 $form->{expense_inventory} = "";
548 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
549 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
552 $form->{amount_cogs} = {};
554 foreach my $i (1 .. $form->{rowcount}) {
555 if ($form->{type} eq "credit_note") {
556 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
557 $form->{shipped} = 1;
559 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
564 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
565 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
566 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
568 if ($form->{storno}) {
569 $form->{"qty_$i"} *= -1;
572 if ($form->{"id_$i"}) {
575 if (defined($baseunits{$form->{"id_$i"}})) {
576 $item_unit = $baseunits{$form->{"id_$i"}};
579 $query = qq|SELECT unit FROM parts WHERE id = ?|;
580 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
581 $baseunits{$form->{"id_$i"}} = $item_unit;
584 if (defined($all_units->{$item_unit}->{factor})
585 && ($all_units->{$item_unit}->{factor} ne '')
586 && ($all_units->{$item_unit}->{factor} != 0)) {
587 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
591 $baseqty = $form->{"qty_$i"} * $basefactor;
593 my ($allocated, $taxrate) = (0, 0);
597 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
599 # keep entered selling price
601 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
603 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
605 my $decimalplaces = ($dec > 2) ? $dec : 2;
607 # undo discount formatting
608 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
611 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
613 # round linetotal to 2 decimal places
614 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
615 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
617 if ($form->{taxincluded}) {
618 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
619 $form->{"sellprice_$i"} =
620 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
622 $taxamount = $linetotal * $taxrate;
625 $netamount += $linetotal;
627 if ($taxamount != 0) {
629 $form->{amount}{ $form->{id} }{$_} +=
630 $taxamount * $form->{"${_}_rate"} / $taxrate
631 } split(/ /, $form->{"taxaccounts_$i"});
634 # add amount to income, $form->{amount}{trans_id}{accno}
635 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
637 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
638 $linetotal = $form->round_amount($linetotal, 2);
640 # this is the difference from the inventory
641 $invoicediff += ($amount - $linetotal);
643 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
646 $lastincomeaccno = $form->{"income_accno_$i"};
648 # adjust and round sellprice
649 $form->{"sellprice_$i"} =
650 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
653 next if $payments_only;
655 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
657 if ($form->{"assembly_$i"}) {
658 # record assembly item as allocated
659 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
662 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
666 # get pricegroup_id and save it
667 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
670 # save detail record in invoice table
672 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
673 sellprice, fxsellprice, discount, allocated, assemblyitem,
674 unit, deliverydate, project_id, serialnumber, pricegroup_id,
675 ordnumber, transdate, cusordnumber, base_qty, subtotal,
676 marge_percent, marge_total, lastcost,
677 price_factor_id, price_factor, marge_price_factor)
678 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
679 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
681 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
682 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
683 $form->{"sellprice_$i"}, $fxsellprice,
684 $form->{"discount_$i"}, $allocated, 'f',
685 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
686 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
687 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
688 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
689 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
690 $form->{"lastcost_$i"},
691 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
692 conv_i($form->{"marge_price_factor_$i"}));
693 do_query($form, $dbh, $query, @values);
695 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
697 qq|INSERT INTO licenseinvoice (trans_id, license_id)
698 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
699 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
700 do_query($form, $dbh, $query, @values);
705 $form->{datepaid} = $form->{invdate};
707 # total payments, don't move we need it here
708 for my $i (1 .. $form->{paidaccounts}) {
709 if ($form->{type} eq "credit_note") {
710 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
712 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
714 $form->{paid} += $form->{"paid_$i"};
715 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
718 my ($tax, $diff) = (0, 0);
720 $netamount = $form->round_amount($netamount, 2);
722 # figure out rounding errors for total amount vs netamount + taxes
723 if ($form->{taxincluded}) {
725 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
726 $diff += $amount - $netamount * $form->{exchangerate};
727 $netamount = $amount;
729 foreach my $item (split(/ /, $form->{taxaccounts})) {
730 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
731 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
732 $tax += $form->{amount}{ $form->{id} }{$item};
733 $netamount -= $form->{amount}{ $form->{id} }{$item};
736 $invoicediff += $diff;
737 ######## this only applies to tax included
738 if ($lastincomeaccno) {
739 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
743 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
744 $diff = $amount - $netamount * $form->{exchangerate};
745 $netamount = $amount;
746 foreach my $item (split(/ /, $form->{taxaccounts})) {
747 $form->{amount}{ $form->{id} }{$item} =
748 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
751 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
754 $amount - $form->{amount}{ $form->{id} }{$item} *
755 $form->{exchangerate};
756 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
757 $tax += $form->{amount}{ $form->{id} }{$item};
761 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
763 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
766 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
768 # update exchangerate
769 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
770 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
771 $form->{exchangerate}, 0);
774 $project_id = conv_i($form->{"globalproject_id"});
776 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
777 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
778 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
780 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
782 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
784 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
785 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
786 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
787 do_query($form, $dbh, $query, @values);
788 $form->{amount_cogs}{$trans_id}{$accno} = 0;
792 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
793 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
795 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
797 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
798 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
799 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
800 do_query($form, $dbh, $query, @values);
805 foreach my $trans_id (keys %{ $form->{amount} }) {
806 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
807 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
809 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
811 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
813 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
814 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
815 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
816 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
817 do_query($form, $dbh, $query, @values);
818 $form->{amount}{$trans_id}{$accno} = 0;
822 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
823 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
825 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
827 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
828 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
829 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
830 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
831 do_query($form, $dbh, $query, @values);
836 # deduct payment differences from diff
837 for my $i (1 .. $form->{paidaccounts}) {
838 if ($form->{"paid_$i"} != 0) {
840 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
841 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
845 # record payments and offsetting AR
846 if (!$form->{storno}) {
847 for my $i (1 .. $form->{paidaccounts}) {
849 next if ($form->{"paid_$i"} == 0);
851 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
852 $form->{"datepaid_$i"} = $form->{invdate}
853 unless ($form->{"datepaid_$i"});
854 $form->{datepaid} = $form->{"datepaid_$i"};
858 if ($form->{currency} eq $defaultcurrency) {
859 $form->{"exchangerate_$i"} = 1;
861 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
862 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
866 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
868 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
870 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
871 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
872 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
873 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
874 do_query($form, $dbh, $query, @values);
878 $form->{"paid_$i"} *= -1;
881 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
882 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
883 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
884 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
885 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
886 do_query($form, $dbh, $query, @values);
888 # exchangerate difference
889 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
890 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
894 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
895 $form->{"exchangerate_$i"};
897 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
900 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
906 # update exchange rate
907 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
908 $form->update_exchangerate($dbh, $form->{currency},
909 $form->{"datepaid_$i"},
910 $form->{"exchangerate_$i"}, 0);
914 } else { # if (!$form->{storno})
915 $form->{marge_total} *= -1;
918 if ($payments_only) {
919 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
920 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
922 if (!$provided_dbh) {
927 $main::lxdebug->leave_sub();
931 # record exchange rate differences and gains/losses
932 foreach my $accno (keys %{ $form->{fx} }) {
933 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
935 ($form->{fx}{$accno}{$transdate} =
936 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
941 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
942 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
943 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
944 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
945 do_query($form, $dbh, $query, @values);
950 $amount = $netamount + $tax;
953 $query = qq|UPDATE ar set
954 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
955 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
956 amount = ?, netamount = ?, paid = ?, datepaid = ?,
957 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
958 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
959 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
960 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
961 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
962 cp_id = ?, marge_total = ?, marge_percent = ?,
963 globalproject_id = ?, delivery_customer_id = ?,
964 transaction_description = ?, delivery_vendor_id = ?
966 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
967 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
968 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
969 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
970 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
971 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
972 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
973 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
974 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
975 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
976 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
977 conv_i($form->{"id"}));
978 do_query($form, $dbh, $query, @values);
980 if($form->{"formname"} eq "credit_note") {
981 for my $i (1 .. $form->{rowcount}) {
982 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
983 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
984 do_query($form, $dbh, $query, @values);
988 if ($form->{storno}) {
991 paid = paid + amount,
993 intnotes = ? || intnotes
995 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
996 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1000 $form->{name} = $form->{customer};
1001 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1003 if (!$form->{shipto_id}) {
1004 $form->add_shipto($dbh, $form->{id}, "AR");
1007 # save printed, emailed and queued
1008 $form->save_status($dbh);
1010 Common::webdav_folder($form) if ($main::webdav);
1012 # Link this record to the records it was created from.
1013 RecordLinks->create_links('dbh' => $dbh,
1015 'from_table' => 'oe',
1016 'from_ids' => $form->{convert_from_oe_ids},
1018 'to_id' => $form->{id},
1020 delete $form->{convert_from_oe_ids};
1022 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1024 if (scalar @convert_from_do_ids) {
1025 DO->close_orders('dbh' => $dbh,
1026 'ids' => \@convert_from_do_ids);
1028 RecordLinks->create_links('dbh' => $dbh,
1030 'from_table' => 'delivery_orders',
1031 'from_ids' => \@convert_from_do_ids,
1033 'to_id' => $form->{id},
1036 delete $form->{convert_from_do_ids};
1038 ARAP->close_orders_if_billed('dbh' => $dbh,
1039 'arap_id' => $form->{id},
1043 if (!$provided_dbh) {
1048 $main::lxdebug->leave_sub();
1053 sub _delete_payments {
1054 $main::lxdebug->enter_sub();
1056 my ($self, $form, $dbh) = @_;
1060 # Delete old payment entries from acc_trans.
1064 WHERE (trans_id = ?) AND fx_transaction
1070 LEFT JOIN chart c ON (at.chart_id = c.id)
1071 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1072 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1077 LEFT JOIN chart c ON (at.chart_id = c.id)
1078 WHERE (trans_id = ?)
1079 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1082 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1085 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1086 do_query($form, $dbh, $query);
1089 $main::lxdebug->leave_sub();
1093 $main::lxdebug->enter_sub();
1095 my ($self, $myconfig, $form, $locale) = @_;
1097 # connect to database, turn off autocommit
1098 my $dbh = $form->dbconnect_noauto($myconfig);
1100 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1102 $old_form = save_form();
1104 # Delete all entries in acc_trans from prior payments.
1105 $self->_delete_payments($form, $dbh);
1107 # Save the new payments the user made before cleaning up $form.
1108 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1110 # Clean up $form so that old content won't tamper the results.
1111 %keep_vars = map { $_, 1 } qw(login password id);
1112 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1114 # Retrieve the invoice from the database.
1115 $self->retrieve_invoice($myconfig, $form);
1117 # Set up the content of $form in the way that IS::post_invoice() expects.
1118 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1120 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1121 $item = $form->{invoice_details}->[$row - 1];
1123 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1125 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1128 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1130 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1132 # Restore the payment options from the user input.
1133 map { $form->{$_} = $payments{$_} } keys %payments;
1135 # Get the AR accno (which is normally done by Form::create_links()).
1139 LEFT JOIN chart c ON (at.chart_id = c.id)
1140 WHERE (trans_id = ?)
1141 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1145 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1147 # Post the new payments.
1148 $self->post_invoice($myconfig, $form, $dbh, 1);
1150 restore_form($old_form);
1152 my $rc = $dbh->commit();
1155 $main::lxdebug->leave_sub();
1160 sub process_assembly {
1161 $main::lxdebug->enter_sub();
1163 my ($dbh, $form, $id, $totalqty) = @_;
1166 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1167 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1169 JOIN parts p ON (a.parts_id = p.id)
1171 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1173 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1177 $ref->{inventory_accno_id} *= 1;
1178 $ref->{expense_accno_id} *= 1;
1180 # multiply by number of assemblies
1181 $ref->{qty} *= $totalqty;
1183 if ($ref->{assembly}) {
1184 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1187 if ($ref->{inventory_accno_id}) {
1188 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1192 # save detail record for individual assembly item in invoice table
1194 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1195 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1196 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1197 do_query($form, $dbh, $query, @values);
1203 $main::lxdebug->leave_sub();
1207 $main::lxdebug->enter_sub();
1209 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1210 $form->{taxzone_id} *=1;
1211 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1212 my $taxzone_id = $form->{"taxzone_id"} * 1;
1214 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1215 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1216 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1217 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1218 FROM invoice i, parts p
1219 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1220 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1221 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1222 WHERE (i.parts_id = p.id)
1223 AND (i.parts_id = ?)
1224 AND ((i.base_qty + i.allocated) < 0)
1226 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1231 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1232 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1236 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1238 # total expenses and inventory
1239 # sellprice is the cost of the item
1240 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1243 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1245 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1246 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1247 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1249 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1250 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1256 last if (($totalqty -= $qty) <= 0);
1261 $main::lxdebug->leave_sub();
1266 sub reverse_invoice {
1267 $main::lxdebug->enter_sub();
1269 my ($dbh, $form) = @_;
1271 # reverse inventory items
1273 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1275 JOIN parts p ON (i.parts_id = p.id)
1276 WHERE i.trans_id = ?|;
1277 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1279 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1281 if ($ref->{inventory_accno_id}) {
1282 # de-allocated purchases
1284 qq|SELECT i.id, i.trans_id, i.allocated
1286 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1287 ORDER BY i.trans_id DESC|;
1288 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1290 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1291 my $qty = $ref->{qty};
1292 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1293 $qty = $inhref->{allocated};
1297 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1299 last if (($ref->{qty} -= $qty) <= 0);
1308 my @values = (conv_i($form->{id}));
1309 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1310 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1312 if ($form->{lizenzen}) {
1314 qq|DELETE FROM licenseinvoice
1315 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1316 do_query($form, $dbh, $query, @values);
1319 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1321 $main::lxdebug->leave_sub();
1324 sub delete_invoice {
1325 $main::lxdebug->enter_sub();
1327 my ($self, $myconfig, $form, $spool) = @_;
1329 # connect to database
1330 my $dbh = $form->dbconnect_noauto($myconfig);
1332 &reverse_invoice($dbh, $form);
1334 my @values = (conv_i($form->{id}));
1337 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1339 # delete spool files
1340 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1342 # delete status entries
1343 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1345 my $rc = $dbh->commit;
1349 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1352 $main::lxdebug->leave_sub();
1357 sub retrieve_invoice {
1358 $main::lxdebug->enter_sub();
1360 my ($self, $myconfig, $form) = @_;
1362 # connect to database
1363 my $dbh = $form->dbconnect_noauto($myconfig);
1365 my ($sth, $ref, $query);
1367 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1371 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1372 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1373 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1374 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1375 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1376 d.curr AS currencies
1380 $ref = selectfirst_hashref_query($form, $dbh, $query);
1381 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1384 my $id = conv_i($form->{id});
1389 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1390 a.orddate, a.quodate, a.globalproject_id,
1391 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1392 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1393 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1394 a.employee_id, a.salesman_id, a.payment_id,
1395 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1396 a.transaction_description,
1397 a.marge_total, a.marge_percent,
1400 LEFT JOIN employee e ON (e.id = a.employee_id)
1402 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1403 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1406 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1409 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1410 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1412 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1414 foreach my $vc (qw(customer vendor)) {
1415 next if !$form->{"delivery_${vc}_id"};
1416 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1419 # get printed, emailed
1420 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1421 $sth = prepare_execute_query($form, $dbh, $query, $id);
1423 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1424 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1425 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1426 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1429 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1431 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1432 : $form->{invdate} ? $dbh->quote($form->{invdate})
1436 my $taxzone_id = $form->{taxzone_id} *= 1;
1437 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1439 # retrieve individual items
1442 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1443 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1444 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1446 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1447 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1448 i.price_factor_id, i.price_factor, i.marge_price_factor,
1449 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1450 pr.projectnumber, pg.partsgroup, prg.pricegroup
1453 LEFT JOIN parts p ON (i.parts_id = p.id)
1454 LEFT JOIN project pr ON (i.project_id = pr.id)
1455 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1456 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1458 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1459 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1460 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1462 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1464 $sth = prepare_execute_query($form, $dbh, $query, $id);
1466 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1467 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1468 delete($ref->{"part_inventory_accno_id"});
1470 foreach my $type (qw(inventory income expense)) {
1471 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1472 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1473 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1477 # get tax rates and description
1478 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1480 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1481 LEFT JOIN chart c ON (c.id = t.chart_id)
1483 (SELECT tk.tax_id FROM taxkeys tk
1484 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1485 AND startdate <= date($transdate)
1486 ORDER BY startdate DESC LIMIT 1)
1488 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1489 $ref->{taxaccounts} = "";
1491 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1493 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1497 $ref->{taxaccounts} .= "$ptr->{accno} ";
1499 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1500 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1501 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1502 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1503 $form->{taxaccounts} .= "$ptr->{accno} ";
1508 if ($form->{lizenzen}) {
1509 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1510 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1511 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1514 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1516 chop $ref->{taxaccounts};
1517 push @{ $form->{invoice_details} }, $ref;
1522 Common::webdav_folder($form) if ($main::webdav);
1525 my $rc = $dbh->commit;
1528 $main::lxdebug->leave_sub();
1534 $main::lxdebug->enter_sub();
1536 my ($self, $myconfig, $form) = @_;
1538 # connect to database
1539 my $dbh = $form->dbconnect($myconfig);
1541 my $dateformat = $myconfig->{dateformat};
1542 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1544 my (@values, $duedate, $ref, $query);
1546 if ($form->{invdate}) {
1547 $duedate = "to_date(?, '$dateformat')";
1548 push @values, $form->{invdate};
1550 $duedate = "current_date";
1553 my $cid = conv_i($form->{customer_id});
1556 if ($form->{payment_id}) {
1557 $payment_id = "(pt.id = ?) OR";
1558 push @values, conv_i($form->{payment_id});
1564 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1565 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1566 c.street, c.zipcode, c.city, c.country,
1567 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1568 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1569 b.discount AS tradediscount, b.description AS business
1571 LEFT JOIN business b ON (b.id = c.business_id)
1572 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1575 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1576 map { $form->{$_} = $ref->{$_} } keys %$ref;
1579 qq|SELECT sum(amount - paid) AS dunning_amount
1581 WHERE (paid < amount)
1582 AND (customer_id = ?)
1583 AND (dunning_config_id IS NOT NULL)|;
1584 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1585 map { $form->{$_} = $ref->{$_} } keys %$ref;
1588 qq|SELECT dnn.dunning_description AS max_dunning_level
1589 FROM dunning_config dnn
1590 WHERE id IN (SELECT dunning_config_id
1592 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1593 ORDER BY dunning_level DESC LIMIT 1|;
1594 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1595 map { $form->{$_} = $ref->{$_} } keys %$ref;
1597 $form->{creditremaining} = $form->{creditlimit};
1598 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1599 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1600 $form->{creditremaining} -= $value;
1604 (SELECT e.buy FROM exchangerate e
1605 WHERE e.curr = o.curr
1606 AND e.transdate = o.transdate)
1608 WHERE o.customer_id = ?
1609 AND o.quotation = '0'
1610 AND o.closed = '0'|;
1611 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1613 while (my ($amount, $exch) = $sth->fetchrow_array) {
1614 $exch = 1 unless $exch;
1615 $form->{creditremaining} -= $amount * $exch;
1619 # get shipto if we did not converted an order or invoice
1620 if (!$form->{shipto}) {
1621 map { delete $form->{$_} }
1622 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1623 shiptostreet shiptozipcode shiptocity shiptocountry
1624 shiptocontact shiptophone shiptofax shiptoemail);
1626 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1627 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1629 map { $form->{$_} = $ref->{$_} } keys %$ref;
1632 # setup last accounts used for this customer
1633 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1635 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1637 JOIN acc_trans ac ON (ac.chart_id = c.id)
1638 JOIN ar a ON (a.id = ac.trans_id)
1639 WHERE a.customer_id = ?
1640 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1641 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1642 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1645 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1646 if ($ref->{category} eq 'I') {
1648 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1650 if ($form->{initial_transdate}) {
1652 qq|SELECT tk.tax_id, t.rate
1654 LEFT JOIN tax t ON tk.tax_id = t.id
1655 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1656 ORDER BY tk.startdate DESC
1658 my ($tax_id, $rate) =
1659 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1660 $form->{initial_transdate});
1661 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1664 if ($ref->{category} eq 'A') {
1665 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1669 $form->{rowcount} = $i if ($i && !$form->{type});
1674 $main::lxdebug->leave_sub();
1678 $main::lxdebug->enter_sub();
1680 my ($self, $myconfig, $form) = @_;
1682 # connect to database
1683 my $dbh = $form->dbconnect($myconfig);
1685 my $i = $form->{rowcount};
1687 my $where = qq|NOT p.obsolete = '1'|;
1690 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1691 my ($table, $field) = split m/\./, $column;
1692 next if !$form->{"${field}_${i}"};
1693 $where .= qq| AND lower(${column}) ILIKE ?|;
1694 push @values, '%' . $form->{"${field}_${i}"} . '%';
1697 if ($form->{"description_$i"}) {
1698 $where .= qq| ORDER BY p.description|;
1700 $where .= qq| ORDER BY p.partnumber|;
1704 if ($form->{type} eq "invoice") {
1706 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1707 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1711 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1715 my $taxzone_id = $form->{taxzone_id} * 1;
1716 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1720 p.id, p.partnumber, p.description, p.sellprice,
1721 p.listprice, p.inventory_accno_id, p.lastcost,
1723 c1.accno AS inventory_accno,
1724 c1.new_chart_id AS inventory_new_chart,
1725 date($transdate) - c1.valid_from AS inventory_valid,
1727 c2.accno AS income_accno,
1728 c2.new_chart_id AS income_new_chart,
1729 date($transdate) - c2.valid_from AS income_valid,
1731 c3.accno AS expense_accno,
1732 c3.new_chart_id AS expense_new_chart,
1733 date($transdate) - c3.valid_from AS expense_valid,
1735 p.unit, p.assembly, p.bin, p.onhand,
1736 p.notes AS partnotes, p.notes AS longdescription,
1737 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1740 pfac.factor AS price_factor,
1745 LEFT JOIN chart c1 ON
1746 ((SELECT inventory_accno_id
1747 FROM buchungsgruppen
1748 WHERE id = p.buchungsgruppen_id) = c1.id)
1749 LEFT JOIN chart c2 ON
1750 ((SELECT income_accno_id_${taxzone_id}
1751 FROM buchungsgruppen
1752 WHERE id = p.buchungsgruppen_id) = c2.id)
1753 LEFT JOIN chart c3 ON
1754 ((SELECT expense_accno_id_${taxzone_id}
1755 FROM buchungsgruppen
1756 WHERE id = p.buchungsgruppen_id) = c3.id)
1757 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1758 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1760 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1762 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1764 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1765 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1766 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1767 if (!$ref->{inventory_accno_id}) {
1768 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1770 delete($ref->{inventory_accno_id});
1772 foreach my $type (qw(inventory income expense)) {
1773 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1775 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1778 ($ref->{"${type}_accno"},
1779 $ref->{"${type}_new_chart"},
1780 $ref->{"${type}_valid"})
1781 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1785 if ($form->{payment_id} eq "") {
1786 $form->{payment_id} = $form->{part_payment_id};
1789 # get tax rates and description
1790 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1792 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1794 LEFT JOIN chart c ON (c.id = t.chart_id)
1798 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1800 ORDER BY startdate DESC
1803 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1804 my $stw = $dbh->prepare($query);
1805 $stw->execute(@values) || $form->dberror($query);
1807 $ref->{taxaccounts} = "";
1809 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1811 # if ($customertax{$ref->{accno}})
1812 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1816 $ref->{taxaccounts} .= "$ptr->{accno} ";
1818 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1819 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1820 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1821 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1822 $form->{taxaccounts} .= "$ptr->{accno} ";
1828 chop $ref->{taxaccounts};
1829 if ($form->{language_id}) {
1831 qq|SELECT tr.translation, tr.longdescription
1833 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1834 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1835 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1836 if ($translation ne "") {
1837 $ref->{description} = $translation;
1838 $ref->{longdescription} = $longdescription;
1842 qq|SELECT tr.translation, tr.longdescription
1844 WHERE tr.language_id IN
1847 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1850 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1851 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1852 if ($translation ne "") {
1853 $ref->{description} = $translation;
1854 $ref->{longdescription} = $longdescription;
1859 $ref->{onhand} *= 1;
1861 push @{ $form->{item_list} }, $ref;
1863 if ($form->{lizenzen}) {
1864 if ($ref->{inventory_accno} > 0) {
1868 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1869 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1870 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1871 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1880 $main::lxdebug->leave_sub();
1883 ##########################
1884 # get pricegroups from database
1885 # build up selected pricegroup
1886 # if an exchange rate - change price
1889 sub get_pricegroups_for_parts {
1891 $main::lxdebug->enter_sub();
1893 my ($self, $myconfig, $form) = @_;
1895 my $dbh = $form->dbconnect($myconfig);
1897 $form->{"PRICES"} = {};
1901 my $all_units = AM->retrieve_units($myconfig, $form);
1902 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1903 $form->{"PRICES"}{$i} = [];
1905 $id = $form->{"id_$i"};
1907 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1909 $id = $form->{"new_id_$i"};
1912 my ($price, $selectedpricegroup_id) = split(/--/,
1913 $form->{"sellprice_pg_$i"});
1915 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1916 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1917 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1919 my $price_new = $form->{"price_new_$i"};
1920 my $price_old = $form->{"price_old_$i"};
1922 if (!$form->{"unit_old_$i"}) {
1923 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1924 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1925 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1926 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1929 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1930 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1931 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1933 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1934 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1935 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1936 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1937 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1938 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1939 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1944 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1945 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1946 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1947 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1948 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1952 if (!$form->{"basefactor_$i"}) {
1953 $form->{"basefactor_$i"} = 1;
1959 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1960 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1970 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1972 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1973 'selected' AS selected
1976 ORDER BY pricegroup|;
1977 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1978 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1980 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
1982 $pkr->{selected} = '';
1984 # if there is an exchange rate change price
1985 if (($form->{exchangerate} * 1) != 0) {
1987 $pkr->{price} /= $form->{exchangerate};
1990 $pkr->{price} *= $form->{"basefactor_$i"};
1992 $pkr->{price} *= $basefactor;
1994 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1996 if ($selectedpricegroup_id eq undef) {
1997 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1999 $pkr->{selected} = ' selected';
2001 # no customer pricesgroup set
2002 if ($pkr->{price} == $pkr->{default_sellprice}) {
2004 $pkr->{price} = $form->{"sellprice_$i"};
2008 $form->{"sellprice_$i"} = $pkr->{price};
2011 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2012 $pkr->{price} = $form->{"sellprice_$i"};
2013 $pkr->{selected} = ' selected';
2017 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2018 if ($selectedpricegroup_id ne $pricegroup_old) {
2019 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2020 $pkr->{selected} = ' selected';
2022 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2023 if ($pkr->{pricegroup_id} == 0) {
2024 $pkr->{price} = $form->{"sellprice_$i"};
2025 $pkr->{selected} = ' selected';
2027 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2028 $pkr->{selected} = ' selected';
2029 if ( ($pkr->{pricegroup_id} == 0)
2030 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2031 # $pkr->{price} = $form->{"sellprice_$i"};
2033 $pkr->{price} = $form->{"sellprice_$i"};
2037 push @{ $form->{PRICES}{$i} }, $pkr;
2040 $form->{"basefactor_$i"} *= $basefactor;
2049 $main::lxdebug->leave_sub();
2053 $main::lxdebug->enter_sub();
2055 my ($self, $myconfig, $form, $table) = @_;
2057 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2059 # make sure there's no funny stuff in $table
2060 # ToDO: die when this happens and throw an error
2061 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2063 my $dbh = $form->dbconnect($myconfig);
2065 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2066 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2070 $main::lxdebug->leave_sub();
2076 $main::lxdebug->enter_sub();
2078 my ($self, $myconfig, $form, $table, $id) = @_;
2080 $main::lxdebug->leave_sub() and return 0 unless ($id);
2082 # make sure there's no funny stuff in $table
2083 # ToDO: die when this happens and throw an error
2084 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2086 my $dbh = $form->dbconnect($myconfig);
2088 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2089 my ($result) = selectrow_query($form, $dbh, $query, $id);
2093 $main::lxdebug->leave_sub();