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;
527 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
530 $form->{exchangerate} =
533 : $form->parse_amount($myconfig, $form->{exchangerate});
535 $form->{expense_inventory} = "";
539 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
540 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
543 foreach my $i (1 .. $form->{rowcount}) {
544 if ($form->{type} eq "credit_note") {
545 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
546 $form->{shipped} = 1;
548 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
553 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
554 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
555 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
557 if ($form->{storno}) {
558 $form->{"qty_$i"} *= -1;
561 if ($form->{"id_$i"}) {
564 if (defined($baseunits{$form->{"id_$i"}})) {
565 $item_unit = $baseunits{$form->{"id_$i"}};
568 $query = qq|SELECT unit FROM parts WHERE id = ?|;
569 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
570 $baseunits{$form->{"id_$i"}} = $item_unit;
573 if (defined($all_units->{$item_unit}->{factor})
574 && ($all_units->{$item_unit}->{factor} ne '')
575 && ($all_units->{$item_unit}->{factor} != 0)) {
576 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
580 $baseqty = $form->{"qty_$i"} * $basefactor;
582 my ($allocated, $taxrate) = (0, 0);
586 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
588 # keep entered selling price
590 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
592 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
594 my $decimalplaces = ($dec > 2) ? $dec : 2;
596 # undo discount formatting
597 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
600 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
602 # round linetotal to 2 decimal places
603 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
604 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
606 if ($form->{taxincluded}) {
607 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
608 $form->{"sellprice_$i"} =
609 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
611 $taxamount = $linetotal * $taxrate;
614 $netamount += $linetotal;
616 if ($taxamount != 0) {
618 $form->{amount}{ $form->{id} }{$_} +=
619 $taxamount * $form->{"${_}_rate"} / $taxrate
620 } split(/ /, $form->{"taxaccounts_$i"});
623 # add amount to income, $form->{amount}{trans_id}{accno}
624 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
626 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
627 $linetotal = $form->round_amount($linetotal, 2);
629 # this is the difference from the inventory
630 $invoicediff += ($amount - $linetotal);
632 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
635 $lastincomeaccno = $form->{"income_accno_$i"};
637 # adjust and round sellprice
638 $form->{"sellprice_$i"} =
639 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
642 next if $payments_only;
644 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
646 if ($form->{"assembly_$i"}) {
647 # record assembly item as allocated
648 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
651 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
655 # get pricegroup_id and save it
656 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
659 # save detail record in invoice table
661 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
662 sellprice, fxsellprice, discount, allocated, assemblyitem,
663 unit, deliverydate, project_id, serialnumber, pricegroup_id,
664 ordnumber, transdate, cusordnumber, base_qty, subtotal,
665 marge_percent, marge_total, lastcost,
666 price_factor_id, price_factor, marge_price_factor)
667 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
668 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
670 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
671 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
672 $form->{"sellprice_$i"}, $fxsellprice,
673 $form->{"discount_$i"}, $allocated, 'f',
674 $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
675 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
676 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
677 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
678 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
679 $form->{"lastcost_$i"},
680 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
681 conv_i($form->{"marge_price_factor_$i"}));
682 do_query($form, $dbh, $query, @values);
684 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
686 qq|INSERT INTO licenseinvoice (trans_id, license_id)
687 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
688 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
689 do_query($form, $dbh, $query, @values);
694 $form->{datepaid} = $form->{invdate};
696 # total payments, don't move we need it here
697 for my $i (1 .. $form->{paidaccounts}) {
698 if ($form->{type} eq "credit_note") {
699 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
701 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
703 $form->{paid} += $form->{"paid_$i"};
704 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
707 my ($tax, $diff) = (0, 0);
709 $netamount = $form->round_amount($netamount, 2);
711 # figure out rounding errors for total amount vs netamount + taxes
712 if ($form->{taxincluded}) {
714 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
715 $diff += $amount - $netamount * $form->{exchangerate};
716 $netamount = $amount;
718 foreach my $item (split(/ /, $form->{taxaccounts})) {
719 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
720 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
721 $tax += $form->{amount}{ $form->{id} }{$item};
722 $netamount -= $form->{amount}{ $form->{id} }{$item};
725 $invoicediff += $diff;
726 ######## this only applies to tax included
727 if ($lastincomeaccno) {
728 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
732 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
733 $diff = $amount - $netamount * $form->{exchangerate};
734 $netamount = $amount;
735 foreach my $item (split(/ /, $form->{taxaccounts})) {
736 $form->{amount}{ $form->{id} }{$item} =
737 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
740 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
743 $amount - $form->{amount}{ $form->{id} }{$item} *
744 $form->{exchangerate};
745 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
746 $tax += $form->{amount}{ $form->{id} }{$item};
750 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
752 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
755 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
757 # update exchangerate
758 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
759 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
760 $form->{exchangerate}, 0);
763 $project_id = conv_i($form->{"globalproject_id"});
765 foreach my $trans_id (keys %{ $form->{amount} }) {
766 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
767 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
769 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
771 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
773 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
774 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
775 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
776 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
777 do_query($form, $dbh, $query, @values);
778 $form->{amount}{$trans_id}{$accno} = 0;
782 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
783 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
785 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
787 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
788 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
789 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
790 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
791 do_query($form, $dbh, $query, @values);
796 # deduct payment differences from diff
797 for my $i (1 .. $form->{paidaccounts}) {
798 if ($form->{"paid_$i"} != 0) {
800 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
801 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
805 # record payments and offsetting AR
806 if (!$form->{storno}) {
807 for my $i (1 .. $form->{paidaccounts}) {
809 next if ($form->{"paid_$i"} == 0);
811 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
812 $form->{"datepaid_$i"} = $form->{invdate}
813 unless ($form->{"datepaid_$i"});
814 $form->{datepaid} = $form->{"datepaid_$i"};
818 if ($form->{currency} eq $defaultcurrency) {
819 $form->{"exchangerate_$i"} = 1;
821 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
822 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
826 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
828 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
830 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
831 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
832 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
833 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
834 do_query($form, $dbh, $query, @values);
838 $form->{"paid_$i"} *= -1;
841 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
842 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
843 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
844 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
845 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
846 do_query($form, $dbh, $query, @values);
848 # exchangerate difference
849 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
850 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
854 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
855 $form->{"exchangerate_$i"};
857 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
860 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
866 # update exchange rate
867 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
868 $form->update_exchangerate($dbh, $form->{currency},
869 $form->{"datepaid_$i"},
870 $form->{"exchangerate_$i"}, 0);
874 } else { # if (!$form->{storno})
875 $form->{marge_total} *= -1;
878 if ($payments_only) {
879 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
880 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
882 if (!$provided_dbh) {
887 $main::lxdebug->leave_sub();
891 # record exchange rate differences and gains/losses
892 foreach my $accno (keys %{ $form->{fx} }) {
893 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
895 ($form->{fx}{$accno}{$transdate} =
896 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
901 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
902 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
903 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
904 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
905 do_query($form, $dbh, $query, @values);
910 $amount = $netamount + $tax;
913 $query = qq|UPDATE ar set
914 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
915 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
916 amount = ?, netamount = ?, paid = ?, datepaid = ?,
917 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
918 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
919 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
920 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
921 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
922 cp_id = ?, marge_total = ?, marge_percent = ?,
923 globalproject_id = ?, delivery_customer_id = ?,
924 transaction_description = ?, delivery_vendor_id = ?
926 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
927 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
928 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
929 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
930 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
931 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
932 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
933 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
934 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
935 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
936 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
937 conv_i($form->{"id"}));
938 do_query($form, $dbh, $query, @values);
940 if($form->{"formname"} eq "credit_note") {
941 for my $i (1 .. $form->{rowcount}) {
942 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
943 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
944 do_query($form, $dbh, $query, @values);
948 if ($form->{storno}) {
951 paid = paid + amount,
953 intnotes = ? || intnotes
955 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
956 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
960 $form->{name} = $form->{customer};
961 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
963 if (!$form->{shipto_id}) {
964 $form->add_shipto($dbh, $form->{id}, "AR");
967 # save printed, emailed and queued
968 $form->save_status($dbh);
970 Common::webdav_folder($form) if ($main::webdav);
973 if (!$provided_dbh) {
978 $main::lxdebug->leave_sub();
983 sub _delete_payments {
984 $main::lxdebug->enter_sub();
986 my ($self, $form, $dbh) = @_;
990 # Delete old payment entries from acc_trans.
994 WHERE (trans_id = ?) AND fx_transaction
1000 LEFT JOIN chart c ON (at.chart_id = c.id)
1001 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1002 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1007 LEFT JOIN chart c ON (at.chart_id = c.id)
1008 WHERE (trans_id = ?)
1009 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1012 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1015 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1016 do_query($form, $dbh, $query);
1019 $main::lxdebug->leave_sub();
1023 $main::lxdebug->enter_sub();
1025 my ($self, $myconfig, $form, $locale) = @_;
1027 # connect to database, turn off autocommit
1028 my $dbh = $form->dbconnect_noauto($myconfig);
1030 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1032 $old_form = save_form();
1034 # Delete all entries in acc_trans from prior payments.
1035 $self->_delete_payments($form, $dbh);
1037 # Save the new payments the user made before cleaning up $form.
1038 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1040 # Clean up $form so that old content won't tamper the results.
1041 %keep_vars = map { $_, 1 } qw(login password id);
1042 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1044 # Retrieve the invoice from the database.
1045 $self->retrieve_invoice($myconfig, $form);
1047 # Set up the content of $form in the way that IS::post_invoice() expects.
1048 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1050 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1051 $item = $form->{invoice_details}->[$row - 1];
1053 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1055 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1058 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1060 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1062 # Restore the payment options from the user input.
1063 map { $form->{$_} = $payments{$_} } keys %payments;
1065 # Get the AR accno (which is normally done by Form::create_links()).
1069 LEFT JOIN chart c ON (at.chart_id = c.id)
1070 WHERE (trans_id = ?)
1071 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1075 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1077 # Post the new payments.
1078 $self->post_invoice($myconfig, $form, $dbh, 1);
1080 restore_form($old_form);
1082 my $rc = $dbh->commit();
1085 $main::lxdebug->leave_sub();
1090 sub process_assembly {
1091 $main::lxdebug->enter_sub();
1093 my ($dbh, $form, $id, $totalqty) = @_;
1096 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1097 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1099 JOIN parts p ON (a.parts_id = p.id)
1101 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1103 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1107 $ref->{inventory_accno_id} *= 1;
1108 $ref->{expense_accno_id} *= 1;
1110 # multiply by number of assemblies
1111 $ref->{qty} *= $totalqty;
1113 if ($ref->{assembly}) {
1114 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1117 if ($ref->{inventory_accno_id}) {
1118 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1122 # save detail record for individual assembly item in invoice table
1124 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1125 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1126 @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1127 do_query($form, $dbh, $query, @values);
1133 $main::lxdebug->leave_sub();
1137 $main::lxdebug->enter_sub();
1139 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1140 $form->{taxzone_id} *=1;
1141 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1142 my $taxzone_id = $form->{"taxzone_id"} * 1;
1144 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1145 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1146 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1147 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1148 FROM invoice i, parts p
1149 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1150 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1151 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1152 WHERE (i.parts_id = p.id)
1153 AND (i.parts_id = ?)
1154 AND ((i.base_qty + i.allocated) < 0)
1156 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1161 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1162 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1166 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1168 # total expenses and inventory
1169 # sellprice is the cost of the item
1170 $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
1173 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1175 $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1176 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1177 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1179 $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1180 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1186 last if (($totalqty -= $qty) <= 0);
1191 $main::lxdebug->leave_sub();
1196 sub reverse_invoice {
1197 $main::lxdebug->enter_sub();
1199 my ($dbh, $form) = @_;
1201 # reverse inventory items
1203 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1205 JOIN parts p ON (i.parts_id = p.id)
1206 WHERE i.trans_id = ?|;
1207 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1209 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1211 if ($ref->{inventory_accno_id}) {
1212 # de-allocated purchases
1214 qq|SELECT i.id, i.trans_id, i.allocated
1216 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1217 ORDER BY i.trans_id DESC|;
1218 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1220 while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
1222 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1223 $qty = $inhref->{allocated};
1227 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1229 last if (($ref->{qty} -= $qty) <= 0);
1238 @values = (conv_i($form->{id}));
1239 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1240 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1242 if ($form->{lizenzen}) {
1244 qq|DELETE FROM licenseinvoice
1245 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1246 do_query($form, $dbh, $query, @values);
1249 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1251 $main::lxdebug->leave_sub();
1254 sub delete_invoice {
1255 $main::lxdebug->enter_sub();
1257 my ($self, $myconfig, $form, $spool) = @_;
1259 # connect to database
1260 my $dbh = $form->dbconnect_noauto($myconfig);
1262 &reverse_invoice($dbh, $form);
1264 my @values = (conv_i($form->{id}));
1267 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1269 # delete spool files
1270 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1272 # delete status entries
1273 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1275 my $rc = $dbh->commit;
1279 map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
1282 $main::lxdebug->leave_sub();
1287 sub retrieve_invoice {
1288 $main::lxdebug->enter_sub();
1290 my ($self, $myconfig, $form) = @_;
1292 # connect to database
1293 my $dbh = $form->dbconnect_noauto($myconfig);
1295 my ($sth, $ref, $query);
1297 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1301 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1302 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1303 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1304 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1305 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1306 d.curr AS currencies
1310 $ref = selectfirst_hashref_query($form, $dbh, $query);
1311 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1314 my $id = conv_i($form->{id});
1319 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1320 a.orddate, a.quodate, a.globalproject_id,
1321 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1322 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1323 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1324 a.employee_id, a.salesman_id, a.payment_id,
1325 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1326 a.transaction_description,
1327 a.marge_total, a.marge_percent,
1330 LEFT JOIN employee e ON (e.id = a.employee_id)
1332 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1333 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1336 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1339 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1340 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1342 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1344 foreach my $vc (qw(customer vendor)) {
1345 next if !$form->{"delivery_${vc}_id"};
1346 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1349 # get printed, emailed
1350 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1351 $sth = prepare_execute_query($form, $dbh, $query, $id);
1353 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1354 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1355 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1356 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1359 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1361 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1362 : $form->{invdate} ? $dbh->quote($form->{invdate})
1366 my $taxzone_id = $form->{taxzone_id} *= 1;
1367 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1369 # retrieve individual items
1372 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1373 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1374 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1376 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate,
1377 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1378 i.price_factor_id, i.price_factor, i.marge_price_factor,
1379 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1380 pr.projectnumber, pg.partsgroup, prg.pricegroup
1383 LEFT JOIN parts p ON (i.parts_id = p.id)
1384 LEFT JOIN project pr ON (i.project_id = pr.id)
1385 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1386 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1388 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1389 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1390 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1392 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1394 $sth = prepare_execute_query($form, $dbh, $query, $id);
1396 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1397 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1398 delete($ref->{"part_inventory_accno_id"});
1400 foreach my $type (qw(inventory income expense)) {
1401 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1402 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1403 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1407 # get tax rates and description
1408 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1410 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1411 LEFT JOIN chart c ON (c.id = t.chart_id)
1413 (SELECT tk.tax_id FROM taxkeys tk
1414 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1415 AND startdate <= date($transdate)
1416 ORDER BY startdate DESC LIMIT 1)
1418 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1419 $ref->{taxaccounts} = "";
1421 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1423 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1427 $ref->{taxaccounts} .= "$ptr->{accno} ";
1429 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1430 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1431 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1432 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1433 $form->{taxaccounts} .= "$ptr->{accno} ";
1438 if ($form->{lizenzen}) {
1439 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1440 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1441 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1444 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1446 chop $ref->{taxaccounts};
1447 push @{ $form->{invoice_details} }, $ref;
1452 Common::webdav_folder($form) if ($main::webdav);
1455 my $rc = $dbh->commit;
1458 $main::lxdebug->leave_sub();
1464 $main::lxdebug->enter_sub();
1466 my ($self, $myconfig, $form) = @_;
1468 # connect to database
1469 my $dbh = $form->dbconnect($myconfig);
1471 my $dateformat = $myconfig->{dateformat};
1472 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1474 my (@values, $duedate, $ref, $query);
1476 if ($form->{invdate}) {
1477 $duedate = "to_date(?, '$dateformat')";
1478 push @values, $form->{invdate};
1480 $duedate = "current_date";
1483 my $cid = conv_i($form->{customer_id});
1486 if ($form->{payment_id}) {
1487 $payment_id = "(pt.id = ?) OR";
1488 push @values, conv_i($form->{payment_id});
1494 c.name AS customer, c.discount, c.creditlimit, c.terms,
1495 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1496 c.street, c.zipcode, c.city, c.country,
1497 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1498 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1499 b.discount AS tradediscount, b.description AS business
1501 LEFT JOIN business b ON (b.id = c.business_id)
1502 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1505 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1506 map { $form->{$_} = $ref->{$_} } keys %$ref;
1509 qq|SELECT sum(amount - paid) AS dunning_amount
1511 WHERE (paid < amount)
1512 AND (customer_id = ?)
1513 AND (dunning_config_id IS NOT NULL)|;
1514 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1515 map { $form->{$_} = $ref->{$_} } keys %$ref;
1518 qq|SELECT dnn.dunning_description AS max_dunning_level
1519 FROM dunning_config dnn
1520 WHERE id IN (SELECT dunning_config_id
1522 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1523 ORDER BY dunning_level DESC LIMIT 1|;
1524 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1525 map { $form->{$_} = $ref->{$_} } keys %$ref;
1527 $form->{creditremaining} = $form->{creditlimit};
1528 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1529 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1530 $form->{creditremaining} -= $value;
1534 (SELECT e.buy FROM exchangerate e
1535 WHERE e.curr = o.curr
1536 AND e.transdate = o.transdate)
1538 WHERE o.customer_id = ?
1539 AND o.quotation = '0'
1540 AND o.closed = '0'|;
1541 $sth = prepare_execute_query($form, $dbh, $query, $cid);
1543 while (my ($amount, $exch) = $sth->fetchrow_array) {
1544 $exch = 1 unless $exch;
1545 $form->{creditremaining} -= $amount * $exch;
1549 # get shipto if we did not converted an order or invoice
1550 if (!$form->{shipto}) {
1551 map { delete $form->{$_} }
1552 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1553 shiptostreet shiptozipcode shiptocity shiptocountry
1554 shiptocontact shiptophone shiptofax shiptoemail);
1556 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1557 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1559 map { $form->{$_} = $ref->{$_} } keys %$ref;
1562 # setup last accounts used for this customer
1563 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1565 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1567 JOIN acc_trans ac ON (ac.chart_id = c.id)
1568 JOIN ar a ON (a.id = ac.trans_id)
1569 WHERE a.customer_id = ?
1570 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1571 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1572 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1575 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1576 if ($ref->{category} eq 'I') {
1578 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1580 if ($form->{initial_transdate}) {
1582 qq|SELECT tk.tax_id, t.rate
1584 LEFT JOIN tax t ON tk.tax_id = t.id
1585 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1586 ORDER BY tk.startdate DESC
1588 my ($tax_id, $rate) =
1589 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1590 $form->{initial_transdate});
1591 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1594 if ($ref->{category} eq 'A') {
1595 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1599 $form->{rowcount} = $i if ($i && !$form->{type});
1604 $main::lxdebug->leave_sub();
1608 $main::lxdebug->enter_sub();
1610 my ($self, $myconfig, $form) = @_;
1612 # connect to database
1613 my $dbh = $form->dbconnect($myconfig);
1615 my $i = $form->{rowcount};
1617 my $where = qq|NOT p.obsolete = '1'|;
1620 foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
1621 my ($table, $field) = split m/\./, $column;
1622 next if !$form->{"${field}_${i}"};
1623 $where .= qq| AND lower(${column}) ILIKE ?|;
1624 push @values, '%' . $form->{"${field}_${i}"} . '%';
1627 if ($form->{"description_$i"}) {
1628 $where .= qq| ORDER BY p.description|;
1630 $where .= qq| ORDER BY p.partnumber|;
1634 if ($form->{type} eq "invoice") {
1636 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1637 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1641 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1645 my $taxzone_id = $form->{taxzone_id} * 1;
1646 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1650 p.id, p.partnumber, p.description, p.sellprice,
1651 p.listprice, p.inventory_accno_id, p.lastcost,
1653 c1.accno AS inventory_accno,
1654 c1.new_chart_id AS inventory_new_chart,
1655 date($transdate) - c1.valid_from AS inventory_valid,
1657 c2.accno AS income_accno,
1658 c2.new_chart_id AS income_new_chart,
1659 date($transdate) - c2.valid_from AS income_valid,
1661 c3.accno AS expense_accno,
1662 c3.new_chart_id AS expense_new_chart,
1663 date($transdate) - c3.valid_from AS expense_valid,
1665 p.unit, p.assembly, p.bin, p.onhand,
1666 p.notes AS partnotes, p.notes AS longdescription,
1667 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1670 pfac.factor AS price_factor,
1675 LEFT JOIN chart c1 ON
1676 ((SELECT inventory_accno_id
1677 FROM buchungsgruppen
1678 WHERE id = p.buchungsgruppen_id) = c1.id)
1679 LEFT JOIN chart c2 ON
1680 ((SELECT income_accno_id_${taxzone_id}
1681 FROM buchungsgruppen
1682 WHERE id = p.buchungsgruppen_id) = c2.id)
1683 LEFT JOIN chart c3 ON
1684 ((SELECT expense_accno_id_${taxzone_id}
1685 FROM buchungsgruppen
1686 WHERE id = p.buchungsgruppen_id) = c3.id)
1687 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1688 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1690 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1692 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1694 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1695 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1696 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1697 if (!$ref->{inventory_accno_id}) {
1698 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1700 delete($ref->{inventory_accno_id});
1702 foreach my $type (qw(inventory income expense)) {
1703 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1705 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1708 ($ref->{"${type}_accno"},
1709 $ref->{"${type}_new_chart"},
1710 $ref->{"${type}_valid"})
1711 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1715 if ($form->{payment_id} eq "") {
1716 $form->{payment_id} = $form->{part_payment_id};
1719 # get tax rates and description
1720 $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1722 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1724 LEFT JOIN chart c ON (c.id = t.chart_id)
1728 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1730 ORDER BY startdate DESC
1733 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1734 $stw = $dbh->prepare($query);
1735 $stw->execute(@values) || $form->dberror($query);
1737 $ref->{taxaccounts} = "";
1739 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
1741 # if ($customertax{$ref->{accno}}) {
1742 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1746 $ref->{taxaccounts} .= "$ptr->{accno} ";
1748 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1749 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1750 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1751 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1752 $form->{taxaccounts} .= "$ptr->{accno} ";
1758 chop $ref->{taxaccounts};
1759 if ($form->{language_id}) {
1761 qq|SELECT tr.translation, tr.longdescription
1763 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1764 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1765 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1766 if ($translation ne "") {
1767 $ref->{description} = $translation;
1768 $ref->{longdescription} = $longdescription;
1772 qq|SELECT tr.translation, tr.longdescription
1774 WHERE tr.language_id IN
1777 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1780 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1781 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1782 if ($translation ne "") {
1783 $ref->{description} = $translation;
1784 $ref->{longdescription} = $longdescription;
1789 $ref->{onhand} *= 1;
1791 push @{ $form->{item_list} }, $ref;
1793 if ($form->{lizenzen}) {
1794 if ($ref->{inventory_accno} > 0) {
1798 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1799 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1800 while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
1801 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1810 $main::lxdebug->leave_sub();
1813 ##########################
1814 # get pricegroups from database
1815 # build up selected pricegroup
1816 # if an exchange rate - change price
1819 sub get_pricegroups_for_parts {
1821 $main::lxdebug->enter_sub();
1823 my ($self, $myconfig, $form) = @_;
1825 my $dbh = $form->dbconnect($myconfig);
1827 $form->{"PRICES"} = {};
1831 my $all_units = AM->retrieve_units($myconfig, $form);
1832 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1833 $form->{"PRICES"}{$i} = [];
1835 $id = $form->{"id_$i"};
1837 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1839 $id = $form->{"new_id_$i"};
1842 ($price, $selectedpricegroup_id) = split(/--/,
1843 $form->{"sellprice_pg_$i"});
1845 $pricegroup_old = $form->{"pricegroup_old_$i"};
1846 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1847 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1849 $price_new = $form->{"price_new_$i"};
1850 $price_old = $form->{"price_old_$i"};
1852 if (!$form->{"unit_old_$i"}) {
1853 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1854 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1855 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1856 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1859 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1860 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1861 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1863 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1864 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1865 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1866 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1867 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1868 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1869 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1874 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1875 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1876 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1877 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1878 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1882 if (!$form->{"basefactor_$i"}) {
1883 $form->{"basefactor_$i"} = 1;
1889 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1890 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1900 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1902 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1903 'selected' AS selected
1906 ORDER BY pricegroup|;
1907 @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1908 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1910 while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
1912 $pkr->{selected} = '';
1914 # if there is an exchange rate change price
1915 if (($form->{exchangerate} * 1) != 0) {
1917 $pkr->{price} /= $form->{exchangerate};
1920 $pkr->{price} *= $form->{"basefactor_$i"};
1922 $pkr->{price} *= $basefactor;
1924 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
1926 if ($selectedpricegroup_id eq undef) {
1927 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
1929 $pkr->{selected} = ' selected';
1931 # no customer pricesgroup set
1932 if ($pkr->{price} == $pkr->{default_sellprice}) {
1934 $pkr->{price} = $form->{"sellprice_$i"};
1938 $form->{"sellprice_$i"} = $pkr->{price};
1941 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
1942 $pkr->{price} = $form->{"sellprice_$i"};
1943 $pkr->{selected} = ' selected';
1947 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
1948 if ($selectedpricegroup_id ne $pricegroup_old) {
1949 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1950 $pkr->{selected} = ' selected';
1952 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
1953 if ($pkr->{pricegroup_id} == 0) {
1954 $pkr->{price} = $form->{"sellprice_$i"};
1955 $pkr->{selected} = ' selected';
1957 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
1958 $pkr->{selected} = ' selected';
1959 if ( ($pkr->{pricegroup_id} == 0)
1960 and ($pkr->{price} == $form->{"sellprice_$i"})) {
1961 # $pkr->{price} = $form->{"sellprice_$i"};
1963 $pkr->{price} = $form->{"sellprice_$i"};
1967 push @{ $form->{PRICES}{$i} }, $pkr;
1970 $form->{"basefactor_$i"} *= $basefactor;
1979 $main::lxdebug->leave_sub();
1983 $main::lxdebug->enter_sub();
1985 my ($self, $myconfig, $form, $table) = @_;
1987 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
1989 # make sure there's no funny stuff in $table
1990 # ToDO: die when this happens and throw an error
1991 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
1993 my $dbh = $form->dbconnect($myconfig);
1995 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
1996 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2000 $main::lxdebug->leave_sub();
2006 $main::lxdebug->enter_sub();
2008 my ($self, $myconfig, $form, $table, $id) = @_;
2010 $main::lxdebug->leave_sub() and return 0 unless ($id);
2012 # make sure there's no funny stuff in $table
2013 # ToDO: die when this happens and throw an error
2014 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2016 my $dbh = $form->dbconnect($myconfig);
2018 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2019 my ($result) = selectrow_query($form, $dbh, $query, $id);
2023 $main::lxdebug->leave_sub();