1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
47 $main::lxdebug->enter_sub();
49 my ($self, $myconfig, $form, $locale) = @_;
51 $form->{duedate} ||= $form->{invdate};
54 my $dbh = $form->dbconnect($myconfig);
57 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
58 ($form->{terms}) = selectrow_query($form, $dbh, $query);
60 my (@project_ids, %projectnumbers);
62 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
64 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
67 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
68 $price_factors{$pfac->{id}} = $pfac;
70 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
73 # sort items by partsgroup
74 for $i (1 .. $form->{rowcount}) {
76 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
77 $partsgroup = $form->{"partsgroup_$i"};
79 push @partsgroup, [$i, $partsgroup];
80 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
84 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
85 join(", ", map({ "?" } @project_ids)) . ")";
86 $sth = $dbh->prepare($query);
87 $sth->execute(@project_ids) ||
88 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
89 while (my $ref = $sth->fetchrow_hashref()) {
90 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
95 $form->{"globalprojectnumber"} =
96 $projectnumbers{$form->{"globalproject_id"}};
103 my %oid = ('Pg' => 'oid',
104 'Oracle' => 'rowid');
106 # sort items by partsgroup
107 for $i (1 .. $form->{rowcount}) {
109 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
110 $partsgroup = $form->{"partsgroup_$i"};
112 push @partsgroup, [$i, $partsgroup];
125 my $nodiscount_subtotal = 0;
126 my $discount_subtotal = 0;
128 my $subtotal_header = 0;
132 qw(runningnumber number description longdescription qty ship unit bin
133 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
134 partnotes serialnumber reqdate sellprice listprice netprice
135 discount p_discount discount_sub nodiscount_sub
136 linetotal nodiscount_linetotal tax_rate projectnumber
137 price_factor price_factor_name);
140 qw(taxbase tax taxdescription taxrate taxnumber);
142 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
145 if ($item->[1] ne $sameitem) {
146 push(@{ $form->{description} }, qq|$item->[1]|);
147 $sameitem = $item->[1];
149 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
152 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
154 if ($form->{"id_$i"} != 0) {
156 # add number, description and qty to $form->{number},
157 if ($form->{"subtotal_$i"} && !$subtotal_header) {
158 $subtotal_header = $i;
159 $position = int($position);
162 } elsif ($subtotal_header) {
164 $position = int($position);
165 $position = $position.".".$subposition;
167 $position = int($position);
171 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
173 push @{ $form->{runningnumber} }, $position;
174 push @{ $form->{number} }, $form->{"partnumber_$i"};
175 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
176 push @{ $form->{bin} }, $form->{"bin_$i"};
177 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
178 push @{ $form->{description} }, $form->{"description_$i"};
179 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
180 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
181 push @{ $form->{unit} }, $form->{"unit_$i"};
182 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
183 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
184 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
185 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
186 push @{ $form->{invnumber} }, $form->{"invnumber"};
187 push @{ $form->{invdate} }, $form->{"invdate"};
188 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
189 push @{ $form->{price_factor_name} }, $price_factor->{description};
191 if ($form->{lizenzen}) {
192 if ($form->{"licensenumber_$i"}) {
193 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
194 ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
195 push(@{ $form->{licensenumber} }, $licensenumber);
196 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
199 push(@{ $form->{licensenumber} }, "");
200 push(@{ $form->{validuntil} }, "");
205 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
207 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
208 my ($dec) = ($sellprice =~ /\.(\d+)/);
209 my $decimalplaces = max 2, length($dec);
211 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
212 my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
213 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
214 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
216 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
218 $linetotal = ($linetotal != 0) ? $linetotal : '';
220 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
221 push @{ $form->{p_discount} }, $form->{"discount_$i"};
223 $form->{total} += $linetotal;
224 $form->{nodiscount_total} += $nodiscount_linetotal;
225 $form->{discount_total} += $discount;
227 if ($subtotal_header) {
228 $discount_subtotal += $linetotal;
229 $nodiscount_subtotal += $nodiscount_linetotal;
232 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
233 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
234 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
236 $discount_subtotal = 0;
237 $nodiscount_subtotal = 0;
238 $subtotal_header = 0;
241 push @{ $form->{discount_sub} }, "";
242 push @{ $form->{nodiscount_sub} }, "";
245 if (!$form->{"discount_$i"}) {
246 $nodiscount += $linetotal;
249 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
250 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
252 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
254 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
258 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
260 if ($form->{taxincluded}) {
263 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
264 $taxbase = $linetotal - $taxamount;
266 $taxamount = $linetotal * $taxrate;
267 $taxbase = $linetotal;
270 if ($form->round_amount($taxrate, 7) == 0) {
271 if ($form->{taxincluded}) {
272 foreach $item (@taxaccounts) {
274 $form->round_amount($linetotal * $form->{"${item}_rate"} /
275 (1 + abs($form->{"${item}_rate"})),
278 $taxaccounts{$item} += $taxamount;
279 $taxdiff += $taxamount;
281 $taxbase{$item} += $taxbase;
283 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
285 foreach $item (@taxaccounts) {
286 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
287 $taxbase{$item} += $taxbase;
291 foreach $item (@taxaccounts) {
292 $taxaccounts{$item} +=
293 $taxamount * $form->{"${item}_rate"} / $taxrate;
294 $taxbase{$item} += $taxbase;
297 $tax_rate = $taxrate * 100;
298 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
299 if ($form->{"assembly_$i"}) {
302 # get parts and push them onto the stack
304 if ($form->{groupitems}) {
306 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
308 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
312 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
314 JOIN parts p ON (a.parts_id = p.id)
315 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
316 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
317 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
319 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
320 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
321 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
322 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
323 push(@{ $form->{description} }, $sameitem);
326 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
328 push(@{ $form->{description} },
329 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
331 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
332 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
340 foreach my $item (sort keys %taxaccounts) {
341 push(@{ $form->{taxbase} },
342 $form->format_amount($myconfig, $taxbase{$item}, 2));
344 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
346 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
347 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
348 push(@{ $form->{taxrate} },
349 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
350 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
353 for my $i (1 .. $form->{paidaccounts}) {
354 if ($form->{"paid_$i"}) {
355 push(@{ $form->{payment} }, $form->{"paid_$i"});
356 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
357 push(@{ $form->{paymentaccount} }, $description);
358 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
359 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
361 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
364 if($form->{taxincluded}) {
365 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
368 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
371 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
372 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
373 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
374 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
376 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
377 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
379 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
380 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
382 $form->set_payment_options($myconfig, $form->{invdate});
384 $form->{username} = $myconfig->{name};
388 $main::lxdebug->leave_sub();
391 sub project_description {
392 $main::lxdebug->enter_sub();
394 my ($self, $dbh, $id) = @_;
396 my $query = qq|SELECT description FROM project WHERE id = ?|;
397 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
399 $main::lxdebug->leave_sub();
404 sub customer_details {
405 $main::lxdebug->enter_sub();
407 my ($self, $myconfig, $form, @wanted_vars) = @_;
409 # connect to database
410 my $dbh = $form->dbconnect($myconfig);
412 # get contact id, set it if nessessary
415 my @values = (conv_i($form->{customer_id}));
418 if ($form->{cp_id}) {
419 $where = qq| AND (cp.cp_id = ?) |;
420 push(@values, conv_i($form->{cp_id}));
423 # get rest for the customer
425 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
426 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
428 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
429 WHERE (ct.id = ?) $where
432 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
434 # remove id and taxincluded before copy back
435 delete @$ref{qw(id taxincluded)};
437 @wanted_vars = grep({ $_ } @wanted_vars);
438 if (scalar(@wanted_vars) > 0) {
440 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
441 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
444 map { $form->{$_} = $ref->{$_} } keys %$ref;
446 if ($form->{delivery_customer_id}) {
448 qq|SELECT *, notes as customernotes
452 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
454 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
457 if ($form->{delivery_vendor_id}) {
459 qq|SELECT *, notes as customernotes
463 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
465 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
468 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
470 'trans_id' => $form->{customer_id});
471 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
475 $main::lxdebug->leave_sub();
479 $main::lxdebug->enter_sub();
481 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
483 # connect to database, turn off autocommit
484 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
486 my ($query, $sth, $null, $project_id, @values);
487 my $exchangerate = 0;
489 if (!$form->{employee_id}) {
490 $form->get_employee($dbh);
493 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
495 ($null, $form->{department_id}) = split(/--/, $form->{department});
497 my $all_units = AM->retrieve_units($myconfig, $form);
499 if (!$payments_only) {
501 &reverse_invoice($dbh, $form);
504 $query = qq|SELECT nextval('glid')|;
505 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
507 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
508 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
510 if (!$form->{invnumber}) {
512 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
513 "cnnumber" : "invnumber", $dbh);
518 my ($netamount, $invoicediff) = (0, 0);
519 my ($amount, $linetotal, $lastincomeaccno);
521 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
522 my $defaultcurrency = (split m/:/, $currencies)[0];
524 if ($form->{currency} eq $defaultcurrency) {
525 $form->{exchangerate} = 1;
528 $form->check_exchangerate($myconfig, $form->{currency},
529 $form->{transdate}, 'buy');
532 $form->{exchangerate} =
535 : $form->parse_amount($myconfig, $form->{exchangerate});
537 $form->{expense_inventory} = "";
541 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
542 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
545 foreach my $i (1 .. $form->{rowcount}) {
546 if ($form->{type} eq "credit_note") {
547 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
548 $form->{shipped} = 1;
550 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
555 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
556 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
557 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
559 if ($form->{storno}) {
560 $form->{"qty_$i"} *= -1;
563 if ($form->{"id_$i"}) {
566 if (defined($baseunits{$form->{"id_$i"}})) {
567 $item_unit = $baseunits{$form->{"id_$i"}};
570 $query = qq|SELECT unit FROM parts WHERE id = ?|;
571 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
572 $baseunits{$form->{"id_$i"}} = $item_unit;
575 if (defined($all_units->{$item_unit}->{factor})
576 && ($all_units->{$item_unit}->{factor} ne '')
577 && ($all_units->{$item_unit}->{factor} != 0)) {
578 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
582 $baseqty = $form->{"qty_$i"} * $basefactor;
584 my ($allocated, $taxrate) = (0, 0);
588 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
590 # keep entered selling price
592 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
594 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
596 my $decimalplaces = ($dec > 2) ? $dec : 2;
598 # undo discount formatting
599 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
602 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
604 # round linetotal to 2 decimal places
605 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
606 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
608 if ($form->{taxincluded}) {
609 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
610 $form->{"sellprice_$i"} =
611 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
613 $taxamount = $linetotal * $taxrate;
616 $netamount += $linetotal;
618 if ($taxamount != 0) {
620 $form->{amount}{ $form->{id} }{$_} +=
621 $taxamount * $form->{"${_}_rate"} / $taxrate
622 } split(/ /, $form->{"taxaccounts_$i"});
625 # add amount to income, $form->{amount}{trans_id}{accno}
626 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
628 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
629 $linetotal = $form->round_amount($linetotal, 2);
631 # this is the difference from the inventory
632 $invoicediff += ($amount - $linetotal);
634 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
637 $lastincomeaccno = $form->{"income_accno_$i"};
639 # adjust and round sellprice
640 $form->{"sellprice_$i"} =
641 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
644 next if $payments_only;
646 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
648 if ($form->{"assembly_$i"}) {
649 # record assembly item as allocated
650 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
652 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
656 # get pricegroup_id and save it
657 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
660 # save detail record in invoice table
662 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
663 sellprice, fxsellprice, discount, allocated, assemblyitem,
664 unit, deliverydate, project_id, serialnumber, pricegroup_id,
665 ordnumber, transdate, cusordnumber, base_qty, subtotal,
666 marge_percent, marge_total, lastcost,
667 price_factor_id, price_factor, marge_price_factor)
668 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
669 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
671 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
672 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
673 $form->{"sellprice_$i"}, $fxsellprice,
674 $form->{"discount_$i"}, $allocated, 'f',
675 $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
676 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
677 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
678 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
679 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
680 $form->{"lastcost_$i"},
681 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
682 conv_i($form->{"marge_price_factor_$i"}));
683 do_query($form, $dbh, $query, @values);
685 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
687 qq|INSERT INTO licenseinvoice (trans_id, license_id)
688 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
689 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
690 do_query($form, $dbh, $query, @values);
695 $form->{datepaid} = $form->{invdate};
697 # total payments, don't move we need it here
698 for my $i (1 .. $form->{paidaccounts}) {
699 if ($form->{type} eq "credit_note") {
700 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
702 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
704 $form->{paid} += $form->{"paid_$i"};
705 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
708 my ($tax, $diff) = (0, 0);
710 $netamount = $form->round_amount($netamount, 2);
712 # figure out rounding errors for total amount vs netamount + taxes
713 if ($form->{taxincluded}) {
715 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
716 $diff += $amount - $netamount * $form->{exchangerate};
717 $netamount = $amount;
719 foreach my $item (split(/ /, $form->{taxaccounts})) {
720 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
721 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
722 $tax += $form->{amount}{ $form->{id} }{$item};
723 $netamount -= $form->{amount}{ $form->{id} }{$item};
726 $invoicediff += $diff;
727 ######## this only applies to tax included
728 if ($lastincomeaccno) {
729 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
733 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
734 $diff = $amount - $netamount * $form->{exchangerate};
735 $netamount = $amount;
736 foreach my $item (split(/ /, $form->{taxaccounts})) {
737 $form->{amount}{ $form->{id} }{$item} =
738 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
741 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
744 $amount - $form->{amount}{ $form->{id} }{$item} *
745 $form->{exchangerate};
746 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
747 $tax += $form->{amount}{ $form->{id} }{$item};
751 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
753 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
756 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
758 # update exchangerate
759 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
760 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
761 $form->{exchangerate}, 0);
764 $project_id = conv_i($form->{"globalproject_id"});
766 foreach my $trans_id (keys %{ $form->{amount} }) {
767 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
768 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
770 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
772 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
774 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
775 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
776 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
777 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
778 do_query($form, $dbh, $query, @values);
779 $form->{amount}{$trans_id}{$accno} = 0;
783 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
784 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
786 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
788 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
789 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
790 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
791 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
792 do_query($form, $dbh, $query, @values);
797 # deduct payment differences from diff
798 for my $i (1 .. $form->{paidaccounts}) {
799 if ($form->{"paid_$i"} != 0) {
801 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
802 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
806 # record payments and offsetting AR
807 if (!$form->{storno}) {
808 for my $i (1 .. $form->{paidaccounts}) {
810 next if ($form->{"paid_$i"} == 0);
812 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
813 $form->{"datepaid_$i"} = $form->{invdate}
814 unless ($form->{"datepaid_$i"});
815 $form->{datepaid} = $form->{"datepaid_$i"};
819 if ($form->{currency} eq $defaultcurrency) {
820 $form->{"exchangerate_$i"} = 1;
823 $form->check_exchangerate($myconfig, $form->{currency},
824 $form->{"datepaid_$i"}, 'buy');
826 $form->{"exchangerate_$i"} =
827 $exchangerate ? $exchangerate
828 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
832 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
834 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
836 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
837 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
838 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
839 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
840 do_query($form, $dbh, $query, @values);
844 $form->{"paid_$i"} *= -1;
847 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
848 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
849 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
850 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
851 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
852 do_query($form, $dbh, $query, @values);
854 # exchangerate difference
855 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
856 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
860 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
861 $form->{"exchangerate_$i"};
863 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
866 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
872 # update exchange rate
873 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
874 $form->update_exchangerate($dbh, $form->{currency},
875 $form->{"datepaid_$i"},
876 $form->{"exchangerate_$i"}, 0);
880 } else { # if (!$form->{storno})
881 $form->{marge_total} *= -1;
884 if ($payments_only) {
885 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
886 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
888 if (!$provided_dbh) {
893 $main::lxdebug->leave_sub();
897 # record exchange rate differences and gains/losses
898 foreach my $accno (keys %{ $form->{fx} }) {
899 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
901 ($form->{fx}{$accno}{$transdate} =
902 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
907 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
908 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
909 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
910 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
911 do_query($form, $dbh, $query, @values);
916 $amount = $netamount + $tax;
919 $query = qq|UPDATE ar set
920 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
921 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
922 amount = ?, netamount = ?, paid = ?, datepaid = ?,
923 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
924 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
925 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
926 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
927 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
928 cp_id = ?, marge_total = ?, marge_percent = ?,
929 globalproject_id = ?, delivery_customer_id = ?,
930 transaction_description = ?, delivery_vendor_id = ?
932 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
933 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
934 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
935 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
936 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
937 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
938 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
939 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
940 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
941 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
942 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
943 conv_i($form->{"id"}));
944 do_query($form, $dbh, $query, @values);
946 if($form->{"formname"} eq "credit_note") {
947 for my $i (1 .. $form->{rowcount}) {
948 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
949 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
950 do_query($form, $dbh, $query, @values);
954 if ($form->{storno}) {
957 paid = paid + amount,
959 intnotes = ? || intnotes
961 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
962 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
966 $form->{name} = $form->{customer};
967 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
969 if (!$form->{shipto_id}) {
970 $form->add_shipto($dbh, $form->{id}, "AR");
973 # save printed, emailed and queued
974 $form->save_status($dbh);
976 Common::webdav_folder($form) if ($main::webdav);
979 if (!$provided_dbh) {
984 $main::lxdebug->leave_sub();
989 sub _delete_payments {
990 $main::lxdebug->enter_sub();
992 my ($self, $form, $dbh) = @_;
996 # Delete old payment entries from acc_trans.
1000 WHERE (trans_id = ?) AND fx_transaction
1006 LEFT JOIN chart c ON (at.chart_id = c.id)
1007 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1008 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1013 LEFT JOIN chart c ON (at.chart_id = c.id)
1014 WHERE (trans_id = ?)
1015 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1018 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1021 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1022 do_query($form, $dbh, $query);
1025 $main::lxdebug->leave_sub();
1029 $main::lxdebug->enter_sub();
1031 my ($self, $myconfig, $form, $locale) = @_;
1033 # connect to database, turn off autocommit
1034 my $dbh = $form->dbconnect_noauto($myconfig);
1036 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1038 $old_form = save_form();
1040 # Delete all entries in acc_trans from prior payments.
1041 $self->_delete_payments($form, $dbh);
1043 # Save the new payments the user made before cleaning up $form.
1044 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1046 # Clean up $form so that old content won't tamper the results.
1047 %keep_vars = map { $_, 1 } qw(login password id);
1048 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1050 # Retrieve the invoice from the database.
1051 $self->retrieve_invoice($myconfig, $form);
1053 # Set up the content of $form in the way that IS::post_invoice() expects.
1054 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1056 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1057 $item = $form->{invoice_details}->[$row - 1];
1059 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1061 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1064 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1066 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1068 # Restore the payment options from the user input.
1069 map { $form->{$_} = $payments{$_} } keys %payments;
1071 # Get the AR accno (which is normally done by Form::create_links()).
1075 LEFT JOIN chart c ON (at.chart_id = c.id)
1076 WHERE (trans_id = ?)
1077 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1081 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1083 # Post the new payments.
1084 $self->post_invoice($myconfig, $form, $dbh, 1);
1086 restore_form($old_form);
1088 my $rc = $dbh->commit();
1091 $main::lxdebug->leave_sub();
1096 sub process_assembly {
1097 $main::lxdebug->enter_sub();
1099 my ($dbh, $form, $id, $totalqty) = @_;
1102 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1103 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1105 JOIN parts p ON (a.parts_id = p.id)
1107 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1109 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1113 $ref->{inventory_accno_id} *= 1;
1114 $ref->{expense_accno_id} *= 1;
1116 # multiply by number of assemblies
1117 $ref->{qty} *= $totalqty;
1119 if ($ref->{assembly}) {
1120 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1123 if ($ref->{inventory_accno_id}) {
1124 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1128 # save detail record for individual assembly item in invoice table
1130 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1131 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1132 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1133 do_query($form, $dbh, $query, @values);
1139 $main::lxdebug->leave_sub();
1143 $main::lxdebug->enter_sub();
1145 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1146 $form->{taxzone_id} *=1;
1147 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1148 my $taxzone_id = $form->{"taxzone_id"} * 1;
1150 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1151 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1152 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1153 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1154 FROM invoice i, parts p
1155 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1156 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1157 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1158 WHERE (i.parts_id = p.id)
1159 AND (i.parts_id = ?)
1160 AND ((i.base_qty + i.allocated) < 0)
1162 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1167 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1168 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1172 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1174 # total expenses and inventory
1175 # sellprice is the cost of the item
1176 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1179 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1181 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1182 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1183 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1185 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1186 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1192 last if (($totalqty -= $qty) <= 0);
1197 $main::lxdebug->leave_sub();
1202 sub reverse_invoice {
1203 $main::lxdebug->enter_sub();
1205 my ($dbh, $form) = @_;
1207 # reverse inventory items
1209 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1211 JOIN parts p ON (i.parts_id = p.id)
1212 WHERE i.trans_id = ?|;
1213 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1215 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1217 if ($ref->{inventory_accno_id}) {
1218 # de-allocated purchases
1220 qq|SELECT i.id, i.trans_id, i.allocated
1222 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1223 ORDER BY i.trans_id DESC|;
1224 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1226 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1228 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1229 $qty = $inhref->{allocated};
1233 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1235 last if (($ref->{qty} -= $qty) <= 0);
1244 @values = (conv_i($form->{id}));
1245 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1246 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1248 if ($form->{lizenzen}) {
1250 qq|DELETE FROM licenseinvoice
1251 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1252 do_query($form, $dbh, $query, @values);
1255 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1257 $main::lxdebug->leave_sub();
1260 sub delete_invoice {
1261 $main::lxdebug->enter_sub();
1263 my ($self, $myconfig, $form, $spool) = @_;
1265 # connect to database
1266 my $dbh = $form->dbconnect_noauto($myconfig);
1268 &reverse_invoice($dbh, $form);
1270 my @values = (conv_i($form->{id}));
1273 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1275 # delete spool files
1276 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1278 # delete status entries
1279 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1281 my $rc = $dbh->commit;
1285 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1288 $main::lxdebug->leave_sub();
1293 sub retrieve_invoice {
1294 $main::lxdebug->enter_sub();
1296 my ($self, $myconfig, $form) = @_;
1298 # connect to database
1299 my $dbh = $form->dbconnect_noauto($myconfig);
1301 my ($sth, $ref, $query);
1303 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1307 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1308 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1309 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1310 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1311 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1312 d.curr AS currencies
1316 $ref = selectfirst_hashref_query($form, $dbh, $query);
1317 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1320 my $id = conv_i($form->{id});
1325 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1326 a.orddate, a.quodate, a.globalproject_id,
1327 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1328 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1329 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1330 a.employee_id, a.salesman_id, a.payment_id,
1331 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1332 a.transaction_description,
1333 a.marge_total, a.marge_percent,
1336 LEFT JOIN employee e ON (e.id = a.employee_id)
1338 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1339 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1342 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1345 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1346 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1348 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1350 foreach my $vc (qw(customer vendor)) {
1351 next if !$form->{"delivery_${vc}_id"};
1352 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1355 # get printed, emailed
1356 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1357 $sth = prepare_execute_query($form, $dbh, $query, $id);
1359 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1360 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1361 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1362 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1365 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1367 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1368 : $form->{invdate} ? $dbh->quote($form->{invdate})
1372 my $taxzone_id = $form->{taxzone_id} *= 1;
1373 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1375 # retrieve individual items
1378 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1379 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1380 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1382 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate,
1383 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1384 i.price_factor_id, i.price_factor, i.marge_price_factor,
1385 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1386 pr.projectnumber, pg.partsgroup, prg.pricegroup
1389 LEFT JOIN parts p ON (i.parts_id = p.id)
1390 LEFT JOIN project pr ON (i.project_id = pr.id)
1391 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1392 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1394 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1395 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1396 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1398 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1400 $sth = prepare_execute_query($form, $dbh, $query, $id);
1402 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1403 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1404 delete($ref->{"part_inventory_accno_id"});
1406 foreach my $type (qw(inventory income expense)) {
1407 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1408 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1409 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1413 # get tax rates and description
1414 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1416 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1417 LEFT JOIN chart c ON (c.id = t.chart_id)
1419 (SELECT tk.tax_id FROM taxkeys tk
1420 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1421 AND startdate <= date($transdate)
1422 ORDER BY startdate DESC LIMIT 1)
1424 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1425 $ref->{taxaccounts} = "";
1427 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1429 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1433 $ref->{taxaccounts} .= "$ptr->{accno} ";
1435 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1436 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1437 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1438 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1439 $form->{taxaccounts} .= "$ptr->{accno} ";
1444 if ($form->{lizenzen}) {
1445 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1446 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1447 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1450 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1452 chop $ref->{taxaccounts};
1453 push @{ $form->{invoice_details} }, $ref;
1458 Common::webdav_folder($form) if ($main::webdav);
1461 my $rc = $dbh->commit;
1464 $main::lxdebug->leave_sub();
1470 $main::lxdebug->enter_sub();
1472 my ($self, $myconfig, $form) = @_;
1474 # connect to database
1475 my $dbh = $form->dbconnect($myconfig);
1477 my $dateformat = $myconfig->{dateformat};
1478 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1480 my (@values, $duedate, $ref, $query);
1482 if ($form->{invdate}) {
1483 $duedate = "to_date(?, '$dateformat')";
1484 push @values, $form->{invdate};
1486 $duedate = "current_date";
1489 my $cid = conv_i($form->{customer_id});
1492 if ($form->{payment_id}) {
1493 $payment_id = "(pt.id = ?) OR";
1494 push @values, conv_i($form->{payment_id});
1500 c.name AS customer, c.discount, c.creditlimit, c.terms,
1501 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1502 c.street, c.zipcode, c.city, c.country,
1503 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1504 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1505 b.discount AS tradediscount, b.description AS business
1507 LEFT JOIN business b ON (b.id = c.business_id)
1508 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1511 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1512 map { $form->{$_} = $ref->{$_} } keys %$ref;
1515 qq|SELECT sum(amount - paid) AS dunning_amount
1517 WHERE (paid < amount)
1518 AND (customer_id = ?)
1519 AND (dunning_config_id IS NOT NULL)|;
1520 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1521 map { $form->{$_} = $ref->{$_} } keys %$ref;
1524 qq|SELECT dnn.dunning_description AS max_dunning_level
1525 FROM dunning_config dnn
1526 WHERE id IN (SELECT dunning_config_id
1528 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1529 ORDER BY dunning_level DESC LIMIT 1|;
1530 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1531 map { $form->{$_} = $ref->{$_} } keys %$ref;
1533 $form->{creditremaining} = $form->{creditlimit};
1534 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1535 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1536 $form->{creditremaining} -= $value;
1540 (SELECT e.buy FROM exchangerate e
1541 WHERE e.curr = o.curr
1542 AND e.transdate = o.transdate)
1544 WHERE o.customer_id = ?
1545 AND o.quotation = '0'
1546 AND o.closed = '0'|;
1547 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1549 while (my ($amount, $exch) = $sth->fetchrow_array) {
1550 $exch = 1 unless $exch;
1551 $form->{creditremaining} -= $amount * $exch;
1555 # get shipto if we did not converted an order or invoice
1556 if (!$form->{shipto}) {
1557 map { delete $form->{$_} }
1558 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1559 shiptostreet shiptozipcode shiptocity shiptocountry
1560 shiptocontact shiptophone shiptofax shiptoemail);
1562 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1563 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1565 map { $form->{$_} = $ref->{$_} } keys %$ref;
1568 # setup last accounts used for this customer
1569 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1571 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1573 JOIN acc_trans ac ON (ac.chart_id = c.id)
1574 JOIN ar a ON (a.id = ac.trans_id)
1575 WHERE a.customer_id = ?
1576 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1577 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1578 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1581 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1582 if ($ref->{category} eq 'I') {
1584 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1586 if ($form->{initial_transdate}) {
1588 qq|SELECT tk.tax_id, t.rate
1590 LEFT JOIN tax t ON tk.tax_id = t.id
1591 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1592 ORDER BY tk.startdate DESC
1594 my ($tax_id, $rate) =
1595 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1596 $form->{initial_transdate});
1597 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1600 if ($ref->{category} eq 'A') {
1601 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1605 $form->{rowcount} = $i if ($i && !$form->{type});
1610 $main::lxdebug->leave_sub();
1614 $main::lxdebug->enter_sub();
1616 my ($self, $myconfig, $form) = @_;
1618 # connect to database
1619 my $dbh = $form->dbconnect($myconfig);
1621 my $i = $form->{rowcount};
1623 my $where = qq|NOT p.obsolete = '1'|;
1626 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1627 my ($table, $field) = split m/\./, $column;
1628 next if !$form->{"${field}_${i}"};
1629 $where .= qq| AND lower(${column}) ILIKE ?|;
1630 push @values, '%' . $form->{"${field}_${i}"} . '%';
1633 if ($form->{"description_$i"}) {
1634 $where .= qq| ORDER BY p.description|;
1636 $where .= qq| ORDER BY p.partnumber|;
1640 if ($form->{type} eq "invoice") {
1642 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1643 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1647 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1651 my $taxzone_id = $form->{taxzone_id} * 1;
1652 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1656 p.id, p.partnumber, p.description, p.sellprice,
1657 p.listprice, p.inventory_accno_id, p.lastcost,
1659 c1.accno AS inventory_accno,
1660 c1.new_chart_id AS inventory_new_chart,
1661 date($transdate) - c1.valid_from AS inventory_valid,
1663 c2.accno AS income_accno,
1664 c2.new_chart_id AS income_new_chart,
1665 date($transdate) - c2.valid_from AS income_valid,
1667 c3.accno AS expense_accno,
1668 c3.new_chart_id AS expense_new_chart,
1669 date($transdate) - c3.valid_from AS expense_valid,
1671 p.unit, p.assembly, p.bin, p.onhand,
1672 p.notes AS partnotes, p.notes AS longdescription,
1673 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1676 pfac.factor AS price_factor,
1681 LEFT JOIN chart c1 ON
1682 ((SELECT inventory_accno_id
1683 FROM buchungsgruppen
1684 WHERE id = p.buchungsgruppen_id) = c1.id)
1685 LEFT JOIN chart c2 ON
1686 ((SELECT income_accno_id_${taxzone_id}
1687 FROM buchungsgruppen
1688 WHERE id = p.buchungsgruppen_id) = c2.id)
1689 LEFT JOIN chart c3 ON
1690 ((SELECT expense_accno_id_${taxzone_id}
1691 FROM buchungsgruppen
1692 WHERE id = p.buchungsgruppen_id) = c3.id)
1693 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1694 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1696 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1698 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1700 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1701 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1702 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1703 if (!$ref->{inventory_accno_id}) {
1704 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1706 delete($ref->{inventory_accno_id});
1708 foreach my $type (qw(inventory income expense)) {
1709 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1711 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1714 ($ref->{"${type}_accno"},
1715 $ref->{"${type}_new_chart"},
1716 $ref->{"${type}_valid"})
1717 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1721 if ($form->{payment_id} eq "") {
1722 $form->{payment_id} = $form->{part_payment_id};
1725 # get tax rates and description
1726 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1728 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1730 LEFT JOIN chart c ON (c.id = t.chart_id)
1734 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1736 ORDER BY startdate DESC
1739 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1740 $stw = $dbh->prepare($query);
1741 $stw->execute(@values) || $form->dberror($query);
1743 $ref->{taxaccounts} = "";
1745 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1747 # if ($customertax{$ref->{accno}}) {
1748 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1752 $ref->{taxaccounts} .= "$ptr->{accno} ";
1754 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1755 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1756 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1757 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1758 $form->{taxaccounts} .= "$ptr->{accno} ";
1764 chop $ref->{taxaccounts};
1765 if ($form->{language_id}) {
1767 qq|SELECT tr.translation, tr.longdescription
1769 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1770 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1771 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1772 if ($translation ne "") {
1773 $ref->{description} = $translation;
1774 $ref->{longdescription} = $longdescription;
1778 qq|SELECT tr.translation, tr.longdescription
1780 WHERE tr.language_id IN
1783 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1786 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1787 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1788 if ($translation ne "") {
1789 $ref->{description} = $translation;
1790 $ref->{longdescription} = $longdescription;
1795 $ref->{onhand} *= 1;
1797 push @{ $form->{item_list} }, $ref;
1799 if ($form->{lizenzen}) {
1800 if ($ref->{inventory_accno} > 0) {
1804 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1805 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1806 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1807 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1816 $main::lxdebug->leave_sub();
1819 ##########################
1820 # get pricegroups from database
1821 # build up selected pricegroup
1822 # if an exchange rate - change price
1825 sub get_pricegroups_for_parts {
1827 $main::lxdebug->enter_sub();
1829 my ($self, $myconfig, $form) = @_;
1831 my $dbh = $form->dbconnect($myconfig);
1833 $form->{"PRICES"} = {};
1837 my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
1838 my $service_units = AM->retrieve_units($myconfig, $form, "service");
1839 my $all_units = AM->retrieve_units($myconfig, $form);
1840 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1841 $form->{"PRICES"}{$i} = [];
1843 $id = $form->{"id_$i"};
1845 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1847 $id = $form->{"new_id_$i"};
1850 ($price, $selectedpricegroup_id) = split(/--/,
1851 $form->{"sellprice_pg_$i"});
1853 $pricegroup_old = $form->{"pricegroup_old_$i"};
1854 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1855 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1857 $price_new = $form->{"price_new_$i"};
1858 $price_old = $form->{"price_old_$i"};
1860 if (!$form->{"unit_old_$i"}) {
1861 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1862 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1863 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1864 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1867 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1868 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1869 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1871 my $check_units = $form->{"inventory_accno_$i"} ? $dimension_units : $service_units;
1872 if (!$check_units->{$form->{"selected_unit_$i"}} ||
1873 ($check_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1874 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1875 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1876 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1877 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1878 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1883 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1884 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1885 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1886 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1887 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1891 if (!$form->{"basefactor_$i"}) {
1892 $form->{"basefactor_$i"} = 1;
1898 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1899 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1909 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1911 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1912 'selected' AS selected
1915 ORDER BY pricegroup|;
1916 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1917 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1919 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1921 $pkr->{selected} = '';
1923 # if there is an exchange rate change price
1924 if (($form->{exchangerate} * 1) != 0) {
1926 $pkr->{price} /= $form->{exchangerate};
1929 $pkr->{price} *= $form->{"basefactor_$i"};
1931 $pkr->{price} *= $basefactor;
1933 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1935 if ($selectedpricegroup_id eq undef) {
1936 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1938 $pkr->{selected} = ' selected';
1940 # no customer pricesgroup set
1941 if ($pkr->{price} == $pkr->{default_sellprice}) {
1943 $pkr->{price} = $form->{"sellprice_$i"};
1947 $form->{"sellprice_$i"} = $pkr->{price};
1950 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1951 $pkr->{price} = $form->{"sellprice_$i"};
1952 $pkr->{selected} = ' selected';
1956 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1957 if ($selectedpricegroup_id ne $pricegroup_old) {
1958 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1959 $pkr->{selected} = ' selected';
1961 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1962 if ($pkr->{pricegroup_id} == 0) {
1963 $pkr->{price} = $form->{"sellprice_$i"};
1964 $pkr->{selected} = ' selected';
1966 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1967 $pkr->{selected} = ' selected';
1968 if ( ($pkr->{pricegroup_id} == 0)
1969 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1970 # $pkr->{price} = $form->{"sellprice_$i"};
1972 $pkr->{price} = $form->{"sellprice_$i"};
1976 push @{ $form->{PRICES}{$i} }, $pkr;
1979 $form->{"basefactor_$i"} *= $basefactor;
1988 $main::lxdebug->leave_sub();
1992 $main::lxdebug->enter_sub();
1994 my ($self, $myconfig, $form, $table) = @_;
1996 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
1998 # make sure there's no funny stuff in $table
1999 # ToDO: die when this happens and throw an error
2000 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2002 my $dbh = $form->dbconnect($myconfig);
2004 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2005 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2009 $main::lxdebug->leave_sub();
2015 $main::lxdebug->enter_sub();
2017 my ($self, $myconfig, $form, $table, $id) = @_;
2019 $main::lxdebug->leave_sub() and return 0 unless ($id);
2021 # make sure there's no funny stuff in $table
2022 # ToDO: die when this happens and throw an error
2023 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2025 my $dbh = $form->dbconnect($myconfig);
2027 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2028 my ($result) = selectrow_query($form, $dbh, $query, $id);
2032 $main::lxdebug->leave_sub();