1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
49 $main::lxdebug->enter_sub();
51 my ($self, $myconfig, $form, $locale) = @_;
53 $form->{duedate} ||= $form->{invdate};
56 my $dbh = $form->dbconnect($myconfig);
59 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
60 ($form->{terms}) = selectrow_query($form, $dbh, $query);
62 my (@project_ids, %projectnumbers);
64 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
66 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
69 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
70 $price_factors{$pfac->{id}} = $pfac;
72 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
75 # sort items by partsgroup
76 for $i (1 .. $form->{rowcount}) {
78 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
79 $partsgroup = $form->{"partsgroup_$i"};
81 push @partsgroup, [$i, $partsgroup];
82 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
86 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
87 join(", ", map({ "?" } @project_ids)) . ")";
88 $sth = $dbh->prepare($query);
89 $sth->execute(@project_ids) ||
90 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
91 while (my $ref = $sth->fetchrow_hashref()) {
92 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
97 $form->{"globalprojectnumber"} =
98 $projectnumbers{$form->{"globalproject_id"}};
105 my %oid = ('Pg' => 'oid',
106 'Oracle' => 'rowid');
108 # sort items by partsgroup
109 for $i (1 .. $form->{rowcount}) {
111 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
112 $partsgroup = $form->{"partsgroup_$i"};
114 push @partsgroup, [$i, $partsgroup];
127 my $nodiscount_subtotal = 0;
128 my $discount_subtotal = 0;
130 my $subtotal_header = 0;
133 $form->{discount} = [];
136 qw(runningnumber number description longdescription qty ship unit bin
137 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
138 partnotes serialnumber reqdate sellprice listprice netprice
139 discount p_discount discount_sub nodiscount_sub
140 linetotal nodiscount_linetotal tax_rate projectnumber
141 price_factor price_factor_name);
144 qw(taxbase tax taxdescription taxrate taxnumber);
146 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
149 if ($item->[1] ne $sameitem) {
150 push(@{ $form->{description} }, qq|$item->[1]|);
151 $sameitem = $item->[1];
153 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
156 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
158 if ($form->{"id_$i"} != 0) {
160 # add number, description and qty to $form->{number},
161 if ($form->{"subtotal_$i"} && !$subtotal_header) {
162 $subtotal_header = $i;
163 $position = int($position);
166 } elsif ($subtotal_header) {
168 $position = int($position);
169 $position = $position.".".$subposition;
171 $position = int($position);
175 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
177 push @{ $form->{runningnumber} }, $position;
178 push @{ $form->{number} }, $form->{"partnumber_$i"};
179 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
180 push @{ $form->{bin} }, $form->{"bin_$i"};
181 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
182 push @{ $form->{description} }, $form->{"description_$i"};
183 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
184 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
185 push @{ $form->{unit} }, $form->{"unit_$i"};
186 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
187 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
188 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
189 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
190 push @{ $form->{invnumber} }, $form->{"invnumber"};
191 push @{ $form->{invdate} }, $form->{"invdate"};
192 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
193 push @{ $form->{price_factor_name} }, $price_factor->{description};
195 if ($form->{lizenzen}) {
196 if ($form->{"licensenumber_$i"}) {
197 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
198 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
199 push(@{ $form->{licensenumber} }, $licensenumber);
200 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
203 push(@{ $form->{licensenumber} }, "");
204 push(@{ $form->{validuntil} }, "");
209 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
211 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
212 my ($dec) = ($sellprice =~ /\.(\d+)/);
213 my $decimalplaces = max 2, length($dec);
215 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
216 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
217 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
218 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
220 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
222 $linetotal = ($linetotal != 0) ? $linetotal : '';
224 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
225 push @{ $form->{p_discount} }, $form->{"discount_$i"};
227 $form->{total} += $linetotal;
228 $form->{nodiscount_total} += $nodiscount_linetotal;
229 $form->{discount_total} += $discount;
231 if ($subtotal_header) {
232 $discount_subtotal += $linetotal;
233 $nodiscount_subtotal += $nodiscount_linetotal;
236 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
237 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
238 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
240 $discount_subtotal = 0;
241 $nodiscount_subtotal = 0;
242 $subtotal_header = 0;
245 push @{ $form->{discount_sub} }, "";
246 push @{ $form->{nodiscount_sub} }, "";
249 if (!$form->{"discount_$i"}) {
250 $nodiscount += $linetotal;
253 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
254 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
256 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
258 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
262 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
264 if ($form->{taxincluded}) {
267 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
268 $taxbase = $linetotal - $taxamount;
270 $taxamount = $linetotal * $taxrate;
271 $taxbase = $linetotal;
274 if ($form->round_amount($taxrate, 7) == 0) {
275 if ($form->{taxincluded}) {
276 foreach $item (@taxaccounts) {
278 $form->round_amount($linetotal * $form->{"${item}_rate"} /
279 (1 + abs($form->{"${item}_rate"})),
282 $taxaccounts{$item} += $taxamount;
283 $taxdiff += $taxamount;
285 $taxbase{$item} += $taxbase;
287 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
289 foreach $item (@taxaccounts) {
290 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
291 $taxbase{$item} += $taxbase;
295 foreach $item (@taxaccounts) {
296 $taxaccounts{$item} +=
297 $taxamount * $form->{"${item}_rate"} / $taxrate;
298 $taxbase{$item} += $taxbase;
301 $tax_rate = $taxrate * 100;
302 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
303 if ($form->{"assembly_$i"}) {
306 # get parts and push them onto the stack
308 if ($form->{groupitems}) {
310 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
312 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
316 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
318 JOIN parts p ON (a.parts_id = p.id)
319 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
320 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
321 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
323 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
324 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
325 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
326 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
327 push(@{ $form->{description} }, $sameitem);
330 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
332 push(@{ $form->{description} },
333 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
335 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
336 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
344 foreach my $item (sort keys %taxaccounts) {
345 push(@{ $form->{taxbase} },
346 $form->format_amount($myconfig, $taxbase{$item}, 2));
348 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
350 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
351 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
352 push(@{ $form->{taxrate} },
353 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
354 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
357 for my $i (1 .. $form->{paidaccounts}) {
358 if ($form->{"paid_$i"}) {
359 push(@{ $form->{payment} }, $form->{"paid_$i"});
360 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
361 push(@{ $form->{paymentaccount} }, $description);
362 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
363 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
365 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
368 if($form->{taxincluded}) {
369 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
372 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
375 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
376 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
377 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
378 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
380 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
381 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
383 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
384 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
386 $form->set_payment_options($myconfig, $form->{invdate});
388 $form->{username} = $myconfig->{name};
392 $main::lxdebug->leave_sub();
395 sub project_description {
396 $main::lxdebug->enter_sub();
398 my ($self, $dbh, $id) = @_;
400 my $query = qq|SELECT description FROM project WHERE id = ?|;
401 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
403 $main::lxdebug->leave_sub();
408 sub customer_details {
409 $main::lxdebug->enter_sub();
411 my ($self, $myconfig, $form, @wanted_vars) = @_;
413 # connect to database
414 my $dbh = $form->dbconnect($myconfig);
416 # get contact id, set it if nessessary
419 my @values = (conv_i($form->{customer_id}));
422 if ($form->{cp_id}) {
423 $where = qq| AND (cp.cp_id = ?) |;
424 push(@values, conv_i($form->{cp_id}));
427 # get rest for the customer
429 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
430 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
432 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
433 WHERE (ct.id = ?) $where
436 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
438 # remove id and taxincluded before copy back
439 delete @$ref{qw(id taxincluded)};
441 @wanted_vars = grep({ $_ } @wanted_vars);
442 if (scalar(@wanted_vars) > 0) {
444 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
445 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
448 map { $form->{$_} = $ref->{$_} } keys %$ref;
450 if ($form->{delivery_customer_id}) {
452 qq|SELECT *, notes as customernotes
456 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
458 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
461 if ($form->{delivery_vendor_id}) {
463 qq|SELECT *, notes as customernotes
467 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
469 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
472 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
474 'trans_id' => $form->{customer_id});
475 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
479 $main::lxdebug->leave_sub();
483 $main::lxdebug->enter_sub();
485 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
487 # connect to database, turn off autocommit
488 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
490 my ($query, $sth, $null, $project_id, @values);
491 my $exchangerate = 0;
493 if (!$form->{employee_id}) {
494 $form->get_employee($dbh);
497 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
499 ($null, $form->{department_id}) = split(/--/, $form->{department});
501 my $all_units = AM->retrieve_units($myconfig, $form);
503 if (!$payments_only) {
505 &reverse_invoice($dbh, $form);
508 $query = qq|SELECT nextval('glid')|;
509 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
511 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
512 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
514 if (!$form->{invnumber}) {
516 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
517 "cnnumber" : "invnumber", $dbh);
522 my ($netamount, $invoicediff) = (0, 0);
523 my ($amount, $linetotal, $lastincomeaccno);
525 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
526 my $defaultcurrency = (split m/:/, $currencies)[0];
528 if ($form->{currency} eq $defaultcurrency) {
529 $form->{exchangerate} = 1;
531 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
534 $form->{exchangerate} =
537 : $form->parse_amount($myconfig, $form->{exchangerate});
539 $form->{expense_inventory} = "";
543 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
544 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
547 foreach my $i (1 .. $form->{rowcount}) {
548 if ($form->{type} eq "credit_note") {
549 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
550 $form->{shipped} = 1;
552 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
557 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
558 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
559 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
561 if ($form->{storno}) {
562 $form->{"qty_$i"} *= -1;
565 if ($form->{"id_$i"}) {
568 if (defined($baseunits{$form->{"id_$i"}})) {
569 $item_unit = $baseunits{$form->{"id_$i"}};
572 $query = qq|SELECT unit FROM parts WHERE id = ?|;
573 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
574 $baseunits{$form->{"id_$i"}} = $item_unit;
577 if (defined($all_units->{$item_unit}->{factor})
578 && ($all_units->{$item_unit}->{factor} ne '')
579 && ($all_units->{$item_unit}->{factor} != 0)) {
580 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
584 $baseqty = $form->{"qty_$i"} * $basefactor;
586 my ($allocated, $taxrate) = (0, 0);
590 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
592 # keep entered selling price
594 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
596 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
598 my $decimalplaces = ($dec > 2) ? $dec : 2;
600 # undo discount formatting
601 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
604 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
606 # round linetotal to 2 decimal places
607 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
608 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
610 if ($form->{taxincluded}) {
611 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
612 $form->{"sellprice_$i"} =
613 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
615 $taxamount = $linetotal * $taxrate;
618 $netamount += $linetotal;
620 if ($taxamount != 0) {
622 $form->{amount}{ $form->{id} }{$_} +=
623 $taxamount * $form->{"${_}_rate"} / $taxrate
624 } split(/ /, $form->{"taxaccounts_$i"});
627 # add amount to income, $form->{amount}{trans_id}{accno}
628 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
630 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
631 $linetotal = $form->round_amount($linetotal, 2);
633 # this is the difference from the inventory
634 $invoicediff += ($amount - $linetotal);
636 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
639 $lastincomeaccno = $form->{"income_accno_$i"};
641 # adjust and round sellprice
642 $form->{"sellprice_$i"} =
643 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
646 next if $payments_only;
648 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
650 if ($form->{"assembly_$i"}) {
651 # record assembly item as allocated
652 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
655 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
659 # get pricegroup_id and save it
660 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
663 # save detail record in invoice table
665 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
666 sellprice, fxsellprice, discount, allocated, assemblyitem,
667 unit, deliverydate, project_id, serialnumber, pricegroup_id,
668 ordnumber, transdate, cusordnumber, base_qty, subtotal,
669 marge_percent, marge_total, lastcost,
670 price_factor_id, price_factor, marge_price_factor)
671 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
672 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
674 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
675 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
676 $form->{"sellprice_$i"}, $fxsellprice,
677 $form->{"discount_$i"}, $allocated, 'f',
678 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
679 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
680 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
681 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
682 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
683 $form->{"lastcost_$i"},
684 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
685 conv_i($form->{"marge_price_factor_$i"}));
686 do_query($form, $dbh, $query, @values);
688 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
690 qq|INSERT INTO licenseinvoice (trans_id, license_id)
691 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
692 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
693 do_query($form, $dbh, $query, @values);
698 $form->{datepaid} = $form->{invdate};
700 # total payments, don't move we need it here
701 for my $i (1 .. $form->{paidaccounts}) {
702 if ($form->{type} eq "credit_note") {
703 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
705 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
707 $form->{paid} += $form->{"paid_$i"};
708 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
711 my ($tax, $diff) = (0, 0);
713 $netamount = $form->round_amount($netamount, 2);
715 # figure out rounding errors for total amount vs netamount + taxes
716 if ($form->{taxincluded}) {
718 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
719 $diff += $amount - $netamount * $form->{exchangerate};
720 $netamount = $amount;
722 foreach my $item (split(/ /, $form->{taxaccounts})) {
723 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
724 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
725 $tax += $form->{amount}{ $form->{id} }{$item};
726 $netamount -= $form->{amount}{ $form->{id} }{$item};
729 $invoicediff += $diff;
730 ######## this only applies to tax included
731 if ($lastincomeaccno) {
732 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
736 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
737 $diff = $amount - $netamount * $form->{exchangerate};
738 $netamount = $amount;
739 foreach my $item (split(/ /, $form->{taxaccounts})) {
740 $form->{amount}{ $form->{id} }{$item} =
741 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
744 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
747 $amount - $form->{amount}{ $form->{id} }{$item} *
748 $form->{exchangerate};
749 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
750 $tax += $form->{amount}{ $form->{id} }{$item};
754 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
756 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
759 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
761 # update exchangerate
762 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
763 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
764 $form->{exchangerate}, 0);
767 $project_id = conv_i($form->{"globalproject_id"});
769 foreach my $trans_id (keys %{ $form->{amount} }) {
770 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
771 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
773 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
775 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
777 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
778 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
779 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
780 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
781 do_query($form, $dbh, $query, @values);
782 $form->{amount}{$trans_id}{$accno} = 0;
786 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
787 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
789 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
791 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
792 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
793 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
794 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
795 do_query($form, $dbh, $query, @values);
800 # deduct payment differences from diff
801 for my $i (1 .. $form->{paidaccounts}) {
802 if ($form->{"paid_$i"} != 0) {
804 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
805 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
809 # record payments and offsetting AR
810 if (!$form->{storno}) {
811 for my $i (1 .. $form->{paidaccounts}) {
813 next if ($form->{"paid_$i"} == 0);
815 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
816 $form->{"datepaid_$i"} = $form->{invdate}
817 unless ($form->{"datepaid_$i"});
818 $form->{datepaid} = $form->{"datepaid_$i"};
822 if ($form->{currency} eq $defaultcurrency) {
823 $form->{"exchangerate_$i"} = 1;
825 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
826 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
830 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
832 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
834 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
835 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
836 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
837 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
838 do_query($form, $dbh, $query, @values);
842 $form->{"paid_$i"} *= -1;
845 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
846 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
847 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
848 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
849 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
850 do_query($form, $dbh, $query, @values);
852 # exchangerate difference
853 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
854 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
858 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
859 $form->{"exchangerate_$i"};
861 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
864 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
870 # update exchange rate
871 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
872 $form->update_exchangerate($dbh, $form->{currency},
873 $form->{"datepaid_$i"},
874 $form->{"exchangerate_$i"}, 0);
878 } else { # if (!$form->{storno})
879 $form->{marge_total} *= -1;
882 if ($payments_only) {
883 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
884 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
886 if (!$provided_dbh) {
891 $main::lxdebug->leave_sub();
895 # record exchange rate differences and gains/losses
896 foreach my $accno (keys %{ $form->{fx} }) {
897 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
899 ($form->{fx}{$accno}{$transdate} =
900 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
905 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
906 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
907 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
908 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
909 do_query($form, $dbh, $query, @values);
914 $amount = $netamount + $tax;
917 $query = qq|UPDATE ar set
918 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
919 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
920 amount = ?, netamount = ?, paid = ?, datepaid = ?,
921 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
922 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
923 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
924 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
925 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
926 cp_id = ?, marge_total = ?, marge_percent = ?,
927 globalproject_id = ?, delivery_customer_id = ?,
928 transaction_description = ?, delivery_vendor_id = ?
930 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
931 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
932 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
933 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
934 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
935 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
936 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
937 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
938 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
939 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
940 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
941 conv_i($form->{"id"}));
942 do_query($form, $dbh, $query, @values);
944 if($form->{"formname"} eq "credit_note") {
945 for my $i (1 .. $form->{rowcount}) {
946 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
947 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
948 do_query($form, $dbh, $query, @values);
952 if ($form->{storno}) {
955 paid = paid + amount,
957 intnotes = ? || intnotes
959 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
960 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
964 $form->{name} = $form->{customer};
965 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
967 if (!$form->{shipto_id}) {
968 $form->add_shipto($dbh, $form->{id}, "AR");
971 # save printed, emailed and queued
972 $form->save_status($dbh);
974 Common::webdav_folder($form) if ($main::webdav);
976 # Link this record to the records it was created from.
977 RecordLinks->create_links('dbh' => $dbh,
979 'from_table' => 'oe',
980 'from_ids' => $form->{convert_from_oe_ids},
982 'to_id' => $form->{id},
984 delete $form->{convert_from_oe_ids};
986 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
988 if (scalar @convert_from_do_ids) {
989 DO->close_orders('dbh' => $dbh,
990 'ids' => \@convert_from_do_ids);
992 RecordLinks->create_links('dbh' => $dbh,
994 'from_table' => 'delivery_orders',
995 'from_ids' => \@convert_from_do_ids,
997 'to_id' => $form->{id},
1000 delete $form->{convert_from_do_ids};
1002 ARAP->close_orders_if_billed('dbh' => $dbh,
1003 'arap_id' => $form->{id},
1007 if (!$provided_dbh) {
1012 $main::lxdebug->leave_sub();
1017 sub _delete_payments {
1018 $main::lxdebug->enter_sub();
1020 my ($self, $form, $dbh) = @_;
1024 # Delete old payment entries from acc_trans.
1028 WHERE (trans_id = ?) AND fx_transaction
1034 LEFT JOIN chart c ON (at.chart_id = c.id)
1035 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1036 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1041 LEFT JOIN chart c ON (at.chart_id = c.id)
1042 WHERE (trans_id = ?)
1043 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1046 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1049 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1050 do_query($form, $dbh, $query);
1053 $main::lxdebug->leave_sub();
1057 $main::lxdebug->enter_sub();
1059 my ($self, $myconfig, $form, $locale) = @_;
1061 # connect to database, turn off autocommit
1062 my $dbh = $form->dbconnect_noauto($myconfig);
1064 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1066 $old_form = save_form();
1068 # Delete all entries in acc_trans from prior payments.
1069 $self->_delete_payments($form, $dbh);
1071 # Save the new payments the user made before cleaning up $form.
1072 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1074 # Clean up $form so that old content won't tamper the results.
1075 %keep_vars = map { $_, 1 } qw(login password id);
1076 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1078 # Retrieve the invoice from the database.
1079 $self->retrieve_invoice($myconfig, $form);
1081 # Set up the content of $form in the way that IS::post_invoice() expects.
1082 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1084 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1085 $item = $form->{invoice_details}->[$row - 1];
1087 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1089 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1092 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1094 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1096 # Restore the payment options from the user input.
1097 map { $form->{$_} = $payments{$_} } keys %payments;
1099 # Get the AR accno (which is normally done by Form::create_links()).
1103 LEFT JOIN chart c ON (at.chart_id = c.id)
1104 WHERE (trans_id = ?)
1105 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1109 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1111 # Post the new payments.
1112 $self->post_invoice($myconfig, $form, $dbh, 1);
1114 restore_form($old_form);
1116 my $rc = $dbh->commit();
1119 $main::lxdebug->leave_sub();
1124 sub process_assembly {
1125 $main::lxdebug->enter_sub();
1127 my ($dbh, $form, $id, $totalqty) = @_;
1130 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1131 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1133 JOIN parts p ON (a.parts_id = p.id)
1135 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1137 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1141 $ref->{inventory_accno_id} *= 1;
1142 $ref->{expense_accno_id} *= 1;
1144 # multiply by number of assemblies
1145 $ref->{qty} *= $totalqty;
1147 if ($ref->{assembly}) {
1148 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1151 if ($ref->{inventory_accno_id}) {
1152 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1156 # save detail record for individual assembly item in invoice table
1158 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1159 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1160 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1161 do_query($form, $dbh, $query, @values);
1167 $main::lxdebug->leave_sub();
1171 $main::lxdebug->enter_sub();
1173 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1174 $form->{taxzone_id} *=1;
1175 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1176 my $taxzone_id = $form->{"taxzone_id"} * 1;
1178 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1179 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1180 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1181 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1182 FROM invoice i, parts p
1183 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1184 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1185 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1186 WHERE (i.parts_id = p.id)
1187 AND (i.parts_id = ?)
1188 AND ((i.base_qty + i.allocated) < 0)
1190 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1195 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1196 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1200 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1202 # total expenses and inventory
1203 # sellprice is the cost of the item
1204 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1207 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1209 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1210 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1211 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1213 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1214 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1220 last if (($totalqty -= $qty) <= 0);
1225 $main::lxdebug->leave_sub();
1230 sub reverse_invoice {
1231 $main::lxdebug->enter_sub();
1233 my ($dbh, $form) = @_;
1235 # reverse inventory items
1237 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1239 JOIN parts p ON (i.parts_id = p.id)
1240 WHERE i.trans_id = ?|;
1241 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1243 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1245 if ($ref->{inventory_accno_id}) {
1246 # de-allocated purchases
1248 qq|SELECT i.id, i.trans_id, i.allocated
1250 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1251 ORDER BY i.trans_id DESC|;
1252 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1254 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1256 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1257 $qty = $inhref->{allocated};
1261 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1263 last if (($ref->{qty} -= $qty) <= 0);
1272 @values = (conv_i($form->{id}));
1273 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1274 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1276 if ($form->{lizenzen}) {
1278 qq|DELETE FROM licenseinvoice
1279 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1280 do_query($form, $dbh, $query, @values);
1283 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1285 $main::lxdebug->leave_sub();
1288 sub delete_invoice {
1289 $main::lxdebug->enter_sub();
1291 my ($self, $myconfig, $form, $spool) = @_;
1293 # connect to database
1294 my $dbh = $form->dbconnect_noauto($myconfig);
1296 &reverse_invoice($dbh, $form);
1298 my @values = (conv_i($form->{id}));
1301 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1303 # delete spool files
1304 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1306 # delete status entries
1307 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1309 my $rc = $dbh->commit;
1313 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1316 $main::lxdebug->leave_sub();
1321 sub retrieve_invoice {
1322 $main::lxdebug->enter_sub();
1324 my ($self, $myconfig, $form) = @_;
1326 # connect to database
1327 my $dbh = $form->dbconnect_noauto($myconfig);
1329 my ($sth, $ref, $query);
1331 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1335 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1336 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1337 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1338 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1339 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1340 d.curr AS currencies
1344 $ref = selectfirst_hashref_query($form, $dbh, $query);
1345 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1348 my $id = conv_i($form->{id});
1353 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1354 a.orddate, a.quodate, a.globalproject_id,
1355 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1356 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1357 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1358 a.employee_id, a.salesman_id, a.payment_id,
1359 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1360 a.transaction_description,
1361 a.marge_total, a.marge_percent,
1364 LEFT JOIN employee e ON (e.id = a.employee_id)
1366 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1367 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1370 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1373 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1374 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1376 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1378 foreach my $vc (qw(customer vendor)) {
1379 next if !$form->{"delivery_${vc}_id"};
1380 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1383 # get printed, emailed
1384 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1385 $sth = prepare_execute_query($form, $dbh, $query, $id);
1387 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1388 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1389 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1390 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1393 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1395 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1396 : $form->{invdate} ? $dbh->quote($form->{invdate})
1400 my $taxzone_id = $form->{taxzone_id} *= 1;
1401 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1403 # retrieve individual items
1406 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1407 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1408 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1410 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1411 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1412 i.price_factor_id, i.price_factor, i.marge_price_factor,
1413 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1414 pr.projectnumber, pg.partsgroup, prg.pricegroup
1417 LEFT JOIN parts p ON (i.parts_id = p.id)
1418 LEFT JOIN project pr ON (i.project_id = pr.id)
1419 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1420 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1422 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1423 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1424 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1426 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1428 $sth = prepare_execute_query($form, $dbh, $query, $id);
1430 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1431 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1432 delete($ref->{"part_inventory_accno_id"});
1434 foreach my $type (qw(inventory income expense)) {
1435 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1436 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1437 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1441 # get tax rates and description
1442 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1444 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1445 LEFT JOIN chart c ON (c.id = t.chart_id)
1447 (SELECT tk.tax_id FROM taxkeys tk
1448 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1449 AND startdate <= date($transdate)
1450 ORDER BY startdate DESC LIMIT 1)
1452 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1453 $ref->{taxaccounts} = "";
1455 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1457 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1461 $ref->{taxaccounts} .= "$ptr->{accno} ";
1463 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1464 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1465 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1466 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1467 $form->{taxaccounts} .= "$ptr->{accno} ";
1472 if ($form->{lizenzen}) {
1473 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1474 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1475 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1478 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1480 chop $ref->{taxaccounts};
1481 push @{ $form->{invoice_details} }, $ref;
1486 Common::webdav_folder($form) if ($main::webdav);
1489 my $rc = $dbh->commit;
1492 $main::lxdebug->leave_sub();
1498 $main::lxdebug->enter_sub();
1500 my ($self, $myconfig, $form) = @_;
1502 # connect to database
1503 my $dbh = $form->dbconnect($myconfig);
1505 my $dateformat = $myconfig->{dateformat};
1506 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1508 my (@values, $duedate, $ref, $query);
1510 if ($form->{invdate}) {
1511 $duedate = "to_date(?, '$dateformat')";
1512 push @values, $form->{invdate};
1514 $duedate = "current_date";
1517 my $cid = conv_i($form->{customer_id});
1520 if ($form->{payment_id}) {
1521 $payment_id = "(pt.id = ?) OR";
1522 push @values, conv_i($form->{payment_id});
1528 c.name AS customer, c.discount, c.creditlimit, c.terms,
1529 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1530 c.street, c.zipcode, c.city, c.country,
1531 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1532 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1533 b.discount AS tradediscount, b.description AS business
1535 LEFT JOIN business b ON (b.id = c.business_id)
1536 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1539 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1540 map { $form->{$_} = $ref->{$_} } keys %$ref;
1543 qq|SELECT sum(amount - paid) AS dunning_amount
1545 WHERE (paid < amount)
1546 AND (customer_id = ?)
1547 AND (dunning_config_id IS NOT NULL)|;
1548 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1549 map { $form->{$_} = $ref->{$_} } keys %$ref;
1552 qq|SELECT dnn.dunning_description AS max_dunning_level
1553 FROM dunning_config dnn
1554 WHERE id IN (SELECT dunning_config_id
1556 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1557 ORDER BY dunning_level DESC LIMIT 1|;
1558 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1559 map { $form->{$_} = $ref->{$_} } keys %$ref;
1561 $form->{creditremaining} = $form->{creditlimit};
1562 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1563 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1564 $form->{creditremaining} -= $value;
1568 (SELECT e.buy FROM exchangerate e
1569 WHERE e.curr = o.curr
1570 AND e.transdate = o.transdate)
1572 WHERE o.customer_id = ?
1573 AND o.quotation = '0'
1574 AND o.closed = '0'|;
1575 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1577 while (my ($amount, $exch) = $sth->fetchrow_array) {
1578 $exch = 1 unless $exch;
1579 $form->{creditremaining} -= $amount * $exch;
1583 # get shipto if we did not converted an order or invoice
1584 if (!$form->{shipto}) {
1585 map { delete $form->{$_} }
1586 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1587 shiptostreet shiptozipcode shiptocity shiptocountry
1588 shiptocontact shiptophone shiptofax shiptoemail);
1590 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1591 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1593 map { $form->{$_} = $ref->{$_} } keys %$ref;
1596 # setup last accounts used for this customer
1597 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1599 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1601 JOIN acc_trans ac ON (ac.chart_id = c.id)
1602 JOIN ar a ON (a.id = ac.trans_id)
1603 WHERE a.customer_id = ?
1604 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1605 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1606 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1609 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1610 if ($ref->{category} eq 'I') {
1612 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1614 if ($form->{initial_transdate}) {
1616 qq|SELECT tk.tax_id, t.rate
1618 LEFT JOIN tax t ON tk.tax_id = t.id
1619 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1620 ORDER BY tk.startdate DESC
1622 my ($tax_id, $rate) =
1623 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1624 $form->{initial_transdate});
1625 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1628 if ($ref->{category} eq 'A') {
1629 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1633 $form->{rowcount} = $i if ($i && !$form->{type});
1638 $main::lxdebug->leave_sub();
1642 $main::lxdebug->enter_sub();
1644 my ($self, $myconfig, $form) = @_;
1646 # connect to database
1647 my $dbh = $form->dbconnect($myconfig);
1649 my $i = $form->{rowcount};
1651 my $where = qq|NOT p.obsolete = '1'|;
1654 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1655 my ($table, $field) = split m/\./, $column;
1656 next if !$form->{"${field}_${i}"};
1657 $where .= qq| AND lower(${column}) ILIKE ?|;
1658 push @values, '%' . $form->{"${field}_${i}"} . '%';
1661 if ($form->{"description_$i"}) {
1662 $where .= qq| ORDER BY p.description|;
1664 $where .= qq| ORDER BY p.partnumber|;
1668 if ($form->{type} eq "invoice") {
1670 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1671 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1675 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1679 my $taxzone_id = $form->{taxzone_id} * 1;
1680 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1684 p.id, p.partnumber, p.description, p.sellprice,
1685 p.listprice, p.inventory_accno_id, p.lastcost,
1687 c1.accno AS inventory_accno,
1688 c1.new_chart_id AS inventory_new_chart,
1689 date($transdate) - c1.valid_from AS inventory_valid,
1691 c2.accno AS income_accno,
1692 c2.new_chart_id AS income_new_chart,
1693 date($transdate) - c2.valid_from AS income_valid,
1695 c3.accno AS expense_accno,
1696 c3.new_chart_id AS expense_new_chart,
1697 date($transdate) - c3.valid_from AS expense_valid,
1699 p.unit, p.assembly, p.bin, p.onhand,
1700 p.notes AS partnotes, p.notes AS longdescription,
1701 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1704 pfac.factor AS price_factor,
1709 LEFT JOIN chart c1 ON
1710 ((SELECT inventory_accno_id
1711 FROM buchungsgruppen
1712 WHERE id = p.buchungsgruppen_id) = c1.id)
1713 LEFT JOIN chart c2 ON
1714 ((SELECT income_accno_id_${taxzone_id}
1715 FROM buchungsgruppen
1716 WHERE id = p.buchungsgruppen_id) = c2.id)
1717 LEFT JOIN chart c3 ON
1718 ((SELECT expense_accno_id_${taxzone_id}
1719 FROM buchungsgruppen
1720 WHERE id = p.buchungsgruppen_id) = c3.id)
1721 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1722 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1724 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1726 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1728 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1729 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1730 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1731 if (!$ref->{inventory_accno_id}) {
1732 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1734 delete($ref->{inventory_accno_id});
1736 foreach my $type (qw(inventory income expense)) {
1737 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1739 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1742 ($ref->{"${type}_accno"},
1743 $ref->{"${type}_new_chart"},
1744 $ref->{"${type}_valid"})
1745 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1749 if ($form->{payment_id} eq "") {
1750 $form->{payment_id} = $form->{part_payment_id};
1753 # get tax rates and description
1754 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1756 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1758 LEFT JOIN chart c ON (c.id = t.chart_id)
1762 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1764 ORDER BY startdate DESC
1767 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1768 $stw = $dbh->prepare($query);
1769 $stw->execute(@values) || $form->dberror($query);
1771 $ref->{taxaccounts} = "";
1773 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1775 # if ($customertax{$ref->{accno}}) {
1776 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1780 $ref->{taxaccounts} .= "$ptr->{accno} ";
1782 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1783 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1784 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1785 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1786 $form->{taxaccounts} .= "$ptr->{accno} ";
1792 chop $ref->{taxaccounts};
1793 if ($form->{language_id}) {
1795 qq|SELECT tr.translation, tr.longdescription
1797 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1798 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1799 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1800 if ($translation ne "") {
1801 $ref->{description} = $translation;
1802 $ref->{longdescription} = $longdescription;
1806 qq|SELECT tr.translation, tr.longdescription
1808 WHERE tr.language_id IN
1811 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1814 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1815 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1816 if ($translation ne "") {
1817 $ref->{description} = $translation;
1818 $ref->{longdescription} = $longdescription;
1823 $ref->{onhand} *= 1;
1825 push @{ $form->{item_list} }, $ref;
1827 if ($form->{lizenzen}) {
1828 if ($ref->{inventory_accno} > 0) {
1832 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1833 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1834 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1835 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1844 $main::lxdebug->leave_sub();
1847 ##########################
1848 # get pricegroups from database
1849 # build up selected pricegroup
1850 # if an exchange rate - change price
1853 sub get_pricegroups_for_parts {
1855 $main::lxdebug->enter_sub();
1857 my ($self, $myconfig, $form) = @_;
1859 my $dbh = $form->dbconnect($myconfig);
1861 $form->{"PRICES"} = {};
1865 my $all_units = AM->retrieve_units($myconfig, $form);
1866 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1867 $form->{"PRICES"}{$i} = [];
1869 $id = $form->{"id_$i"};
1871 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1873 $id = $form->{"new_id_$i"};
1876 ($price, $selectedpricegroup_id) = split(/--/,
1877 $form->{"sellprice_pg_$i"});
1879 $pricegroup_old = $form->{"pricegroup_old_$i"};
1880 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1881 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1883 $price_new = $form->{"price_new_$i"};
1884 $price_old = $form->{"price_old_$i"};
1886 if (!$form->{"unit_old_$i"}) {
1887 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1888 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1889 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1890 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1893 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1894 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1895 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1897 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1898 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1899 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1900 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1901 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1902 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1903 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1908 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1909 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1910 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1911 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1912 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1916 if (!$form->{"basefactor_$i"}) {
1917 $form->{"basefactor_$i"} = 1;
1923 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1924 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1934 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1936 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1937 'selected' AS selected
1940 ORDER BY pricegroup|;
1941 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1942 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1944 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1946 $pkr->{selected} = '';
1948 # if there is an exchange rate change price
1949 if (($form->{exchangerate} * 1) != 0) {
1951 $pkr->{price} /= $form->{exchangerate};
1954 $pkr->{price} *= $form->{"basefactor_$i"};
1956 $pkr->{price} *= $basefactor;
1958 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1960 if ($selectedpricegroup_id eq undef) {
1961 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1963 $pkr->{selected} = ' selected';
1965 # no customer pricesgroup set
1966 if ($pkr->{price} == $pkr->{default_sellprice}) {
1968 $pkr->{price} = $form->{"sellprice_$i"};
1972 $form->{"sellprice_$i"} = $pkr->{price};
1975 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1976 $pkr->{price} = $form->{"sellprice_$i"};
1977 $pkr->{selected} = ' selected';
1981 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1982 if ($selectedpricegroup_id ne $pricegroup_old) {
1983 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1984 $pkr->{selected} = ' selected';
1986 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1987 if ($pkr->{pricegroup_id} == 0) {
1988 $pkr->{price} = $form->{"sellprice_$i"};
1989 $pkr->{selected} = ' selected';
1991 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1992 $pkr->{selected} = ' selected';
1993 if ( ($pkr->{pricegroup_id} == 0)
1994 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1995 # $pkr->{price} = $form->{"sellprice_$i"};
1997 $pkr->{price} = $form->{"sellprice_$i"};
2001 push @{ $form->{PRICES}{$i} }, $pkr;
2004 $form->{"basefactor_$i"} *= $basefactor;
2013 $main::lxdebug->leave_sub();
2017 $main::lxdebug->enter_sub();
2019 my ($self, $myconfig, $form, $table) = @_;
2021 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2023 # make sure there's no funny stuff in $table
2024 # ToDO: die when this happens and throw an error
2025 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2027 my $dbh = $form->dbconnect($myconfig);
2029 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2030 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2034 $main::lxdebug->leave_sub();
2040 $main::lxdebug->enter_sub();
2042 my ($self, $myconfig, $form, $table, $id) = @_;
2044 $main::lxdebug->leave_sub() and return 0 unless ($id);
2046 # make sure there's no funny stuff in $table
2047 # ToDO: die when this happens and throw an error
2048 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2050 my $dbh = $form->dbconnect($myconfig);
2052 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2053 my ($result) = selectrow_query($form, $dbh, $query, $id);
2057 $main::lxdebug->leave_sub();