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);
653 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
657 # get pricegroup_id and save it
658 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
661 # save detail record in invoice table
663 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
664 sellprice, fxsellprice, discount, allocated, assemblyitem,
665 unit, deliverydate, project_id, serialnumber, pricegroup_id,
666 ordnumber, transdate, cusordnumber, base_qty, subtotal,
667 marge_percent, marge_total, lastcost,
668 price_factor_id, price_factor, marge_price_factor)
669 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
670 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
672 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
673 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
674 $form->{"sellprice_$i"}, $fxsellprice,
675 $form->{"discount_$i"}, $allocated, 'f',
676 $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
677 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
678 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
679 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
680 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
681 $form->{"lastcost_$i"},
682 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
683 conv_i($form->{"marge_price_factor_$i"}));
684 do_query($form, $dbh, $query, @values);
686 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
688 qq|INSERT INTO licenseinvoice (trans_id, license_id)
689 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
690 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
691 do_query($form, $dbh, $query, @values);
696 $form->{datepaid} = $form->{invdate};
698 # total payments, don't move we need it here
699 for my $i (1 .. $form->{paidaccounts}) {
700 if ($form->{type} eq "credit_note") {
701 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
703 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
705 $form->{paid} += $form->{"paid_$i"};
706 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
709 my ($tax, $diff) = (0, 0);
711 $netamount = $form->round_amount($netamount, 2);
713 # figure out rounding errors for total amount vs netamount + taxes
714 if ($form->{taxincluded}) {
716 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
717 $diff += $amount - $netamount * $form->{exchangerate};
718 $netamount = $amount;
720 foreach my $item (split(/ /, $form->{taxaccounts})) {
721 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
722 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
723 $tax += $form->{amount}{ $form->{id} }{$item};
724 $netamount -= $form->{amount}{ $form->{id} }{$item};
727 $invoicediff += $diff;
728 ######## this only applies to tax included
729 if ($lastincomeaccno) {
730 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
734 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
735 $diff = $amount - $netamount * $form->{exchangerate};
736 $netamount = $amount;
737 foreach my $item (split(/ /, $form->{taxaccounts})) {
738 $form->{amount}{ $form->{id} }{$item} =
739 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
742 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
745 $amount - $form->{amount}{ $form->{id} }{$item} *
746 $form->{exchangerate};
747 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
748 $tax += $form->{amount}{ $form->{id} }{$item};
752 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
754 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
757 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
759 # update exchangerate
760 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
761 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
762 $form->{exchangerate}, 0);
765 $project_id = conv_i($form->{"globalproject_id"});
767 foreach my $trans_id (keys %{ $form->{amount} }) {
768 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
769 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
771 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
773 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
775 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
776 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
777 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
778 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
779 do_query($form, $dbh, $query, @values);
780 $form->{amount}{$trans_id}{$accno} = 0;
784 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
785 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
787 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
789 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
790 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
791 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
792 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
793 do_query($form, $dbh, $query, @values);
798 # deduct payment differences from diff
799 for my $i (1 .. $form->{paidaccounts}) {
800 if ($form->{"paid_$i"} != 0) {
802 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
803 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
807 # record payments and offsetting AR
808 if (!$form->{storno}) {
809 for my $i (1 .. $form->{paidaccounts}) {
811 next if ($form->{"paid_$i"} == 0);
813 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
814 $form->{"datepaid_$i"} = $form->{invdate}
815 unless ($form->{"datepaid_$i"});
816 $form->{datepaid} = $form->{"datepaid_$i"};
820 if ($form->{currency} eq $defaultcurrency) {
821 $form->{"exchangerate_$i"} = 1;
824 $form->check_exchangerate($myconfig, $form->{currency},
825 $form->{"datepaid_$i"}, 'buy');
827 $form->{"exchangerate_$i"} =
828 $exchangerate ? $exchangerate
829 : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
833 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
835 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
837 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
838 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
839 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
840 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
841 do_query($form, $dbh, $query, @values);
845 $form->{"paid_$i"} *= -1;
848 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
850 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
851 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
852 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
853 do_query($form, $dbh, $query, @values);
855 # exchangerate difference
856 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
857 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
861 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
862 $form->{"exchangerate_$i"};
864 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
867 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
873 # update exchange rate
874 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
875 $form->update_exchangerate($dbh, $form->{currency},
876 $form->{"datepaid_$i"},
877 $form->{"exchangerate_$i"}, 0);
881 } else { # if (!$form->{storno})
882 $form->{marge_total} *= -1;
885 if ($payments_only) {
886 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
887 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
889 if (!$provided_dbh) {
894 $main::lxdebug->leave_sub();
898 # record exchange rate differences and gains/losses
899 foreach my $accno (keys %{ $form->{fx} }) {
900 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
902 ($form->{fx}{$accno}{$transdate} =
903 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
908 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
909 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
910 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
911 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
912 do_query($form, $dbh, $query, @values);
917 $amount = $netamount + $tax;
920 $query = qq|UPDATE ar set
921 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
922 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
923 amount = ?, netamount = ?, paid = ?, datepaid = ?,
924 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
925 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
926 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
927 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
928 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
929 cp_id = ?, marge_total = ?, marge_percent = ?,
930 globalproject_id = ?, delivery_customer_id = ?,
931 transaction_description = ?, delivery_vendor_id = ?
933 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
934 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
935 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
936 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
937 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
938 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
939 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
940 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
941 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
942 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
943 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
944 conv_i($form->{"id"}));
945 do_query($form, $dbh, $query, @values);
947 if($form->{"formname"} eq "credit_note") {
948 for my $i (1 .. $form->{rowcount}) {
949 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
950 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
951 do_query($form, $dbh, $query, @values);
955 if ($form->{storno}) {
958 paid = paid + amount,
960 intnotes = ? || intnotes
962 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
963 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
967 $form->{name} = $form->{customer};
968 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
970 if (!$form->{shipto_id}) {
971 $form->add_shipto($dbh, $form->{id}, "AR");
974 # save printed, emailed and queued
975 $form->save_status($dbh);
977 Common::webdav_folder($form) if ($main::webdav);
980 if (!$provided_dbh) {
985 $main::lxdebug->leave_sub();
990 sub _delete_payments {
991 $main::lxdebug->enter_sub();
993 my ($self, $form, $dbh) = @_;
997 # Delete old payment entries from acc_trans.
1001 WHERE (trans_id = ?) AND fx_transaction
1007 LEFT JOIN chart c ON (at.chart_id = c.id)
1008 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1009 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1014 LEFT JOIN chart c ON (at.chart_id = c.id)
1015 WHERE (trans_id = ?)
1016 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1019 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1022 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1023 do_query($form, $dbh, $query);
1026 $main::lxdebug->leave_sub();
1030 $main::lxdebug->enter_sub();
1032 my ($self, $myconfig, $form, $locale) = @_;
1034 # connect to database, turn off autocommit
1035 my $dbh = $form->dbconnect_noauto($myconfig);
1037 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1039 $old_form = save_form();
1041 # Delete all entries in acc_trans from prior payments.
1042 $self->_delete_payments($form, $dbh);
1044 # Save the new payments the user made before cleaning up $form.
1045 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1047 # Clean up $form so that old content won't tamper the results.
1048 %keep_vars = map { $_, 1 } qw(login password id);
1049 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1051 # Retrieve the invoice from the database.
1052 $self->retrieve_invoice($myconfig, $form);
1054 # Set up the content of $form in the way that IS::post_invoice() expects.
1055 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1057 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1058 $item = $form->{invoice_details}->[$row - 1];
1060 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1062 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1065 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1067 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1069 # Restore the payment options from the user input.
1070 map { $form->{$_} = $payments{$_} } keys %payments;
1072 # Get the AR accno (which is normally done by Form::create_links()).
1076 LEFT JOIN chart c ON (at.chart_id = c.id)
1077 WHERE (trans_id = ?)
1078 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1082 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1084 # Post the new payments.
1085 $self->post_invoice($myconfig, $form, $dbh, 1);
1087 restore_form($old_form);
1089 my $rc = $dbh->commit();
1092 $main::lxdebug->leave_sub();
1097 sub process_assembly {
1098 $main::lxdebug->enter_sub();
1100 my ($dbh, $form, $id, $totalqty) = @_;
1103 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1104 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1106 JOIN parts p ON (a.parts_id = p.id)
1108 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1110 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1114 $ref->{inventory_accno_id} *= 1;
1115 $ref->{expense_accno_id} *= 1;
1117 # multiply by number of assemblies
1118 $ref->{qty} *= $totalqty;
1120 if ($ref->{assembly}) {
1121 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1124 if ($ref->{inventory_accno_id}) {
1125 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1129 # save detail record for individual assembly item in invoice table
1131 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1132 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1133 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1134 do_query($form, $dbh, $query, @values);
1140 $main::lxdebug->leave_sub();
1144 $main::lxdebug->enter_sub();
1146 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1147 $form->{taxzone_id} *=1;
1148 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1149 my $taxzone_id = $form->{"taxzone_id"} * 1;
1151 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1152 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1153 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1154 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1155 FROM invoice i, parts p
1156 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1157 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1158 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1159 WHERE (i.parts_id = p.id)
1160 AND (i.parts_id = ?)
1161 AND ((i.base_qty + i.allocated) < 0)
1163 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1168 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1169 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1173 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1175 # total expenses and inventory
1176 # sellprice is the cost of the item
1177 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1180 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1182 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1183 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1184 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1186 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1187 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1193 last if (($totalqty -= $qty) <= 0);
1198 $main::lxdebug->leave_sub();
1203 sub reverse_invoice {
1204 $main::lxdebug->enter_sub();
1206 my ($dbh, $form) = @_;
1208 # reverse inventory items
1210 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1212 JOIN parts p ON (i.parts_id = p.id)
1213 WHERE i.trans_id = ?|;
1214 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1216 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1218 if ($ref->{inventory_accno_id}) {
1219 # de-allocated purchases
1221 qq|SELECT i.id, i.trans_id, i.allocated
1223 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1224 ORDER BY i.trans_id DESC|;
1225 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1227 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1229 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1230 $qty = $inhref->{allocated};
1234 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1236 last if (($ref->{qty} -= $qty) <= 0);
1245 @values = (conv_i($form->{id}));
1246 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1247 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1249 if ($form->{lizenzen}) {
1251 qq|DELETE FROM licenseinvoice
1252 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1253 do_query($form, $dbh, $query, @values);
1256 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1258 $main::lxdebug->leave_sub();
1261 sub delete_invoice {
1262 $main::lxdebug->enter_sub();
1264 my ($self, $myconfig, $form, $spool) = @_;
1266 # connect to database
1267 my $dbh = $form->dbconnect_noauto($myconfig);
1269 &reverse_invoice($dbh, $form);
1271 my @values = (conv_i($form->{id}));
1274 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1276 # delete spool files
1277 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1279 # delete status entries
1280 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1282 my $rc = $dbh->commit;
1286 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1289 $main::lxdebug->leave_sub();
1294 sub retrieve_invoice {
1295 $main::lxdebug->enter_sub();
1297 my ($self, $myconfig, $form) = @_;
1299 # connect to database
1300 my $dbh = $form->dbconnect_noauto($myconfig);
1302 my ($sth, $ref, $query);
1304 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1308 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1309 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1310 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1311 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1312 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1313 d.curr AS currencies
1317 $ref = selectfirst_hashref_query($form, $dbh, $query);
1318 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1321 my $id = conv_i($form->{id});
1326 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1327 a.orddate, a.quodate, a.globalproject_id,
1328 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1329 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1330 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1331 a.employee_id, a.salesman_id, a.payment_id,
1332 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1333 a.transaction_description,
1334 a.marge_total, a.marge_percent,
1337 LEFT JOIN employee e ON (e.id = a.employee_id)
1339 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1340 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1343 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1346 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1347 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1349 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1351 foreach my $vc (qw(customer vendor)) {
1352 next if !$form->{"delivery_${vc}_id"};
1353 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1356 # get printed, emailed
1357 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1358 $sth = prepare_execute_query($form, $dbh, $query, $id);
1360 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1361 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1362 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1363 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1366 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1368 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1369 : $form->{invdate} ? $dbh->quote($form->{invdate})
1373 my $taxzone_id = $form->{taxzone_id} *= 1;
1374 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1376 # retrieve individual items
1379 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1380 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1381 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1383 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate,
1384 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1385 i.price_factor_id, i.price_factor, i.marge_price_factor,
1386 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1387 pr.projectnumber, pg.partsgroup, prg.pricegroup
1390 LEFT JOIN parts p ON (i.parts_id = p.id)
1391 LEFT JOIN project pr ON (i.project_id = pr.id)
1392 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1393 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1395 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1396 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1397 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1399 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1401 $sth = prepare_execute_query($form, $dbh, $query, $id);
1403 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1404 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1405 delete($ref->{"part_inventory_accno_id"});
1407 foreach my $type (qw(inventory income expense)) {
1408 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1409 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1410 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1414 # get tax rates and description
1415 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1417 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1418 LEFT JOIN chart c ON (c.id = t.chart_id)
1420 (SELECT tk.tax_id FROM taxkeys tk
1421 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1422 AND startdate <= date($transdate)
1423 ORDER BY startdate DESC LIMIT 1)
1425 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1426 $ref->{taxaccounts} = "";
1428 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1430 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1434 $ref->{taxaccounts} .= "$ptr->{accno} ";
1436 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1437 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1438 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1439 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1440 $form->{taxaccounts} .= "$ptr->{accno} ";
1445 if ($form->{lizenzen}) {
1446 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1447 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1448 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1451 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1453 chop $ref->{taxaccounts};
1454 push @{ $form->{invoice_details} }, $ref;
1459 Common::webdav_folder($form) if ($main::webdav);
1462 my $rc = $dbh->commit;
1465 $main::lxdebug->leave_sub();
1471 $main::lxdebug->enter_sub();
1473 my ($self, $myconfig, $form) = @_;
1475 # connect to database
1476 my $dbh = $form->dbconnect($myconfig);
1478 my $dateformat = $myconfig->{dateformat};
1479 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1481 my (@values, $duedate, $ref, $query);
1483 if ($form->{invdate}) {
1484 $duedate = "to_date(?, '$dateformat')";
1485 push @values, $form->{invdate};
1487 $duedate = "current_date";
1490 my $cid = conv_i($form->{customer_id});
1493 if ($form->{payment_id}) {
1494 $payment_id = "(pt.id = ?) OR";
1495 push @values, conv_i($form->{payment_id});
1501 c.name AS customer, c.discount, c.creditlimit, c.terms,
1502 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1503 c.street, c.zipcode, c.city, c.country,
1504 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1505 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1506 b.discount AS tradediscount, b.description AS business
1508 LEFT JOIN business b ON (b.id = c.business_id)
1509 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1512 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1513 map { $form->{$_} = $ref->{$_} } keys %$ref;
1516 qq|SELECT sum(amount - paid) AS dunning_amount
1518 WHERE (paid < amount)
1519 AND (customer_id = ?)
1520 AND (dunning_config_id IS NOT NULL)|;
1521 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1522 map { $form->{$_} = $ref->{$_} } keys %$ref;
1525 qq|SELECT dnn.dunning_description AS max_dunning_level
1526 FROM dunning_config dnn
1527 WHERE id IN (SELECT dunning_config_id
1529 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1530 ORDER BY dunning_level DESC LIMIT 1|;
1531 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1532 map { $form->{$_} = $ref->{$_} } keys %$ref;
1534 $form->{creditremaining} = $form->{creditlimit};
1535 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1536 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1537 $form->{creditremaining} -= $value;
1541 (SELECT e.buy FROM exchangerate e
1542 WHERE e.curr = o.curr
1543 AND e.transdate = o.transdate)
1545 WHERE o.customer_id = ?
1546 AND o.quotation = '0'
1547 AND o.closed = '0'|;
1548 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1550 while (my ($amount, $exch) = $sth->fetchrow_array) {
1551 $exch = 1 unless $exch;
1552 $form->{creditremaining} -= $amount * $exch;
1556 # get shipto if we did not converted an order or invoice
1557 if (!$form->{shipto}) {
1558 map { delete $form->{$_} }
1559 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1560 shiptostreet shiptozipcode shiptocity shiptocountry
1561 shiptocontact shiptophone shiptofax shiptoemail);
1563 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1564 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1566 map { $form->{$_} = $ref->{$_} } keys %$ref;
1569 # setup last accounts used for this customer
1570 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1572 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1574 JOIN acc_trans ac ON (ac.chart_id = c.id)
1575 JOIN ar a ON (a.id = ac.trans_id)
1576 WHERE a.customer_id = ?
1577 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1578 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1579 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1582 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1583 if ($ref->{category} eq 'I') {
1585 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1587 if ($form->{initial_transdate}) {
1589 qq|SELECT tk.tax_id, t.rate
1591 LEFT JOIN tax t ON tk.tax_id = t.id
1592 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1593 ORDER BY tk.startdate DESC
1595 my ($tax_id, $rate) =
1596 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1597 $form->{initial_transdate});
1598 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1601 if ($ref->{category} eq 'A') {
1602 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1606 $form->{rowcount} = $i if ($i && !$form->{type});
1611 $main::lxdebug->leave_sub();
1615 $main::lxdebug->enter_sub();
1617 my ($self, $myconfig, $form) = @_;
1619 # connect to database
1620 my $dbh = $form->dbconnect($myconfig);
1622 my $i = $form->{rowcount};
1624 my $where = qq|NOT p.obsolete = '1'|;
1627 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1628 my ($table, $field) = split m/\./, $column;
1629 next if !$form->{"${field}_${i}"};
1630 $where .= qq| AND lower(${column}) ILIKE ?|;
1631 push @values, '%' . $form->{"${field}_${i}"} . '%';
1634 if ($form->{"description_$i"}) {
1635 $where .= qq| ORDER BY p.description|;
1637 $where .= qq| ORDER BY p.partnumber|;
1641 if ($form->{type} eq "invoice") {
1643 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1644 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1648 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1652 my $taxzone_id = $form->{taxzone_id} * 1;
1653 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1657 p.id, p.partnumber, p.description, p.sellprice,
1658 p.listprice, p.inventory_accno_id, p.lastcost,
1660 c1.accno AS inventory_accno,
1661 c1.new_chart_id AS inventory_new_chart,
1662 date($transdate) - c1.valid_from AS inventory_valid,
1664 c2.accno AS income_accno,
1665 c2.new_chart_id AS income_new_chart,
1666 date($transdate) - c2.valid_from AS income_valid,
1668 c3.accno AS expense_accno,
1669 c3.new_chart_id AS expense_new_chart,
1670 date($transdate) - c3.valid_from AS expense_valid,
1672 p.unit, p.assembly, p.bin, p.onhand,
1673 p.notes AS partnotes, p.notes AS longdescription,
1674 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1677 pfac.factor AS price_factor,
1682 LEFT JOIN chart c1 ON
1683 ((SELECT inventory_accno_id
1684 FROM buchungsgruppen
1685 WHERE id = p.buchungsgruppen_id) = c1.id)
1686 LEFT JOIN chart c2 ON
1687 ((SELECT income_accno_id_${taxzone_id}
1688 FROM buchungsgruppen
1689 WHERE id = p.buchungsgruppen_id) = c2.id)
1690 LEFT JOIN chart c3 ON
1691 ((SELECT expense_accno_id_${taxzone_id}
1692 FROM buchungsgruppen
1693 WHERE id = p.buchungsgruppen_id) = c3.id)
1694 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1695 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1697 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1699 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1701 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1702 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1703 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1704 if (!$ref->{inventory_accno_id}) {
1705 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1707 delete($ref->{inventory_accno_id});
1709 foreach my $type (qw(inventory income expense)) {
1710 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1712 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1715 ($ref->{"${type}_accno"},
1716 $ref->{"${type}_new_chart"},
1717 $ref->{"${type}_valid"})
1718 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1722 if ($form->{payment_id} eq "") {
1723 $form->{payment_id} = $form->{part_payment_id};
1726 # get tax rates and description
1727 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1729 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1731 LEFT JOIN chart c ON (c.id = t.chart_id)
1735 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1737 ORDER BY startdate DESC
1740 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1741 $stw = $dbh->prepare($query);
1742 $stw->execute(@values) || $form->dberror($query);
1744 $ref->{taxaccounts} = "";
1746 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1748 # if ($customertax{$ref->{accno}}) {
1749 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1753 $ref->{taxaccounts} .= "$ptr->{accno} ";
1755 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1756 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1757 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1758 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1759 $form->{taxaccounts} .= "$ptr->{accno} ";
1765 chop $ref->{taxaccounts};
1766 if ($form->{language_id}) {
1768 qq|SELECT tr.translation, tr.longdescription
1770 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1771 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1772 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1773 if ($translation ne "") {
1774 $ref->{description} = $translation;
1775 $ref->{longdescription} = $longdescription;
1779 qq|SELECT tr.translation, tr.longdescription
1781 WHERE tr.language_id IN
1784 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1787 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1788 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1789 if ($translation ne "") {
1790 $ref->{description} = $translation;
1791 $ref->{longdescription} = $longdescription;
1796 $ref->{onhand} *= 1;
1798 push @{ $form->{item_list} }, $ref;
1800 if ($form->{lizenzen}) {
1801 if ($ref->{inventory_accno} > 0) {
1805 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1806 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1807 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1808 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1817 $main::lxdebug->leave_sub();
1820 ##########################
1821 # get pricegroups from database
1822 # build up selected pricegroup
1823 # if an exchange rate - change price
1826 sub get_pricegroups_for_parts {
1828 $main::lxdebug->enter_sub();
1830 my ($self, $myconfig, $form) = @_;
1832 my $dbh = $form->dbconnect($myconfig);
1834 $form->{"PRICES"} = {};
1838 my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
1839 my $service_units = AM->retrieve_units($myconfig, $form, "service");
1840 my $all_units = AM->retrieve_units($myconfig, $form);
1841 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1842 $form->{"PRICES"}{$i} = [];
1844 $id = $form->{"id_$i"};
1846 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1848 $id = $form->{"new_id_$i"};
1851 ($price, $selectedpricegroup_id) = split(/--/,
1852 $form->{"sellprice_pg_$i"});
1854 $pricegroup_old = $form->{"pricegroup_old_$i"};
1855 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1856 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1858 $price_new = $form->{"price_new_$i"};
1859 $price_old = $form->{"price_old_$i"};
1861 if (!$form->{"unit_old_$i"}) {
1862 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1863 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1864 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1865 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1868 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1869 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1870 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1872 my $check_units = $form->{"inventory_accno_$i"} ? $dimension_units : $service_units;
1873 if (!$check_units->{$form->{"selected_unit_$i"}} ||
1874 ($check_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1875 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1876 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1877 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1878 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1879 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1884 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1885 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1886 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1887 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1888 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1892 if (!$form->{"basefactor_$i"}) {
1893 $form->{"basefactor_$i"} = 1;
1899 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1900 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1910 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1912 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1913 'selected' AS selected
1916 ORDER BY pricegroup|;
1917 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1918 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1920 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1922 $pkr->{selected} = '';
1924 # if there is an exchange rate change price
1925 if (($form->{exchangerate} * 1) != 0) {
1927 $pkr->{price} /= $form->{exchangerate};
1930 $pkr->{price} *= $form->{"basefactor_$i"};
1932 $pkr->{price} *= $basefactor;
1934 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1936 if ($selectedpricegroup_id eq undef) {
1937 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1939 $pkr->{selected} = ' selected';
1941 # no customer pricesgroup set
1942 if ($pkr->{price} == $pkr->{default_sellprice}) {
1944 $pkr->{price} = $form->{"sellprice_$i"};
1948 $form->{"sellprice_$i"} = $pkr->{price};
1951 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1952 $pkr->{price} = $form->{"sellprice_$i"};
1953 $pkr->{selected} = ' selected';
1957 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1958 if ($selectedpricegroup_id ne $pricegroup_old) {
1959 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1960 $pkr->{selected} = ' selected';
1962 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1963 if ($pkr->{pricegroup_id} == 0) {
1964 $pkr->{price} = $form->{"sellprice_$i"};
1965 $pkr->{selected} = ' selected';
1967 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1968 $pkr->{selected} = ' selected';
1969 if ( ($pkr->{pricegroup_id} == 0)
1970 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1971 # $pkr->{price} = $form->{"sellprice_$i"};
1973 $pkr->{price} = $form->{"sellprice_$i"};
1977 push @{ $form->{PRICES}{$i} }, $pkr;
1980 $form->{"basefactor_$i"} *= $basefactor;
1989 $main::lxdebug->leave_sub();
1993 $main::lxdebug->enter_sub();
1995 my ($self, $myconfig, $form, $table) = @_;
1997 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
1999 # make sure there's no funny stuff in $table
2000 # ToDO: die when this happens and throw an error
2001 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2003 my $dbh = $form->dbconnect($myconfig);
2005 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2006 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2010 $main::lxdebug->leave_sub();
2016 $main::lxdebug->enter_sub();
2018 my ($self, $myconfig, $form, $table, $id) = @_;
2020 $main::lxdebug->leave_sub() and return 0 unless ($id);
2022 # make sure there's no funny stuff in $table
2023 # ToDO: die when this happens and throw an error
2024 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2026 my $dbh = $form->dbconnect($myconfig);
2028 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2029 my ($result) = selectrow_query($form, $dbh, $query, $id);
2033 $main::lxdebug->leave_sub();