1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
39 use List::Util qw(max);
52 $main::lxdebug->enter_sub();
54 my ($self, $myconfig, $form, $locale) = @_;
56 $form->{duedate} ||= $form->{invdate};
59 my $dbh = $form->dbconnect($myconfig);
62 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
63 ($form->{terms}) = selectrow_query($form, $dbh, $query);
65 my (@project_ids, %projectnumbers);
67 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
69 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
72 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
73 $price_factors{$pfac->{id}} = $pfac;
75 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
78 # sort items by partsgroup
79 for my $i (1 .. $form->{rowcount}) {
81 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
82 # $partsgroup = $form->{"partsgroup_$i"};
84 # push @partsgroup, [$i, $partsgroup];
85 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
89 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
90 join(", ", map({ "?" } @project_ids)) . ")";
91 $sth = $dbh->prepare($query);
92 $sth->execute(@project_ids) ||
93 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
94 while (my $ref = $sth->fetchrow_hashref()) {
95 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $form->{"globalprojectnumber"} =
101 $projectnumbers{$form->{"globalproject_id"}};
108 my %oid = ('Pg' => 'oid',
109 'Oracle' => 'rowid');
111 # sort items by partsgroup
112 for $i (1 .. $form->{rowcount}) {
114 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
115 $partsgroup = $form->{"partsgroup_$i"};
117 push @partsgroup, [$i, $partsgroup];
130 my $nodiscount_subtotal = 0;
131 my $discount_subtotal = 0;
133 my $subtotal_header = 0;
136 $form->{discount} = [];
138 IC->prepare_parts_for_printing();
141 qw(runningnumber number description longdescription qty ship unit bin
142 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
143 partnotes serialnumber reqdate sellprice listprice netprice
144 discount p_discount discount_sub nodiscount_sub
145 linetotal nodiscount_linetotal tax_rate projectnumber
146 price_factor price_factor_name partsgroup);
149 qw(taxbase tax taxdescription taxrate taxnumber);
151 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
154 if ($item->[1] ne $sameitem) {
155 push(@{ $form->{description} }, qq|$item->[1]|);
156 $sameitem = $item->[1];
158 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
161 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
163 if ($form->{"id_$i"} != 0) {
165 # add number, description and qty to $form->{number},
166 if ($form->{"subtotal_$i"} && !$subtotal_header) {
167 $subtotal_header = $i;
168 $position = int($position);
171 } elsif ($subtotal_header) {
173 $position = int($position);
174 $position = $position.".".$subposition;
176 $position = int($position);
180 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
182 push @{ $form->{runningnumber} }, $position;
183 push @{ $form->{number} }, $form->{"partnumber_$i"};
184 push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
185 push @{ $form->{bin} }, $form->{"bin_$i"};
186 push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"};
187 push @{ $form->{description} }, $form->{"description_$i"};
188 push @{ $form->{longdescription} }, $form->{"longdescription_$i"};
189 push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
190 push @{ $form->{unit} }, $form->{"unit_$i"};
191 push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"};
192 push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
193 push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
194 push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
195 push @{ $form->{invnumber} }, $form->{"invnumber"};
196 push @{ $form->{invdate} }, $form->{"invdate"};
197 push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
198 push @{ $form->{price_factor_name} }, $price_factor->{description};
199 push @{ $form->{partsgroup} }, $form->{"partsgroup_$i"};
201 if ($form->{lizenzen}) {
202 if ($form->{"licensenumber_$i"}) {
203 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
204 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
205 push(@{ $form->{licensenumber} }, $licensenumber);
206 push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
209 push(@{ $form->{licensenumber} }, "");
210 push(@{ $form->{validuntil} }, "");
215 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
217 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
218 my ($dec) = ($sellprice =~ /\.(\d+)/);
219 my $decimalplaces = max 2, length($dec);
221 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
222 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
223 my $linetotal = $form->round_amount($linetotal_exact, 2);
224 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
226 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
227 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
229 push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
231 $linetotal = ($linetotal != 0) ? $linetotal : '';
233 push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
234 push @{ $form->{p_discount} }, $form->{"discount_$i"};
236 $form->{total} += $linetotal;
237 $form->{nodiscount_total} += $nodiscount_linetotal;
238 $form->{discount_total} += $discount;
240 if ($subtotal_header) {
241 $discount_subtotal += $linetotal;
242 $nodiscount_subtotal += $nodiscount_linetotal;
245 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
246 push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
247 push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
249 $discount_subtotal = 0;
250 $nodiscount_subtotal = 0;
251 $subtotal_header = 0;
254 push @{ $form->{discount_sub} }, "";
255 push @{ $form->{nodiscount_sub} }, "";
258 if (!$form->{"discount_$i"}) {
259 $nodiscount += $linetotal;
262 push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
263 push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
265 push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
267 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
271 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
273 if ($form->{taxincluded}) {
276 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
277 $taxbase = $linetotal - $taxamount;
279 $taxamount = $linetotal * $taxrate;
280 $taxbase = $linetotal;
283 if ($form->round_amount($taxrate, 7) == 0) {
284 if ($form->{taxincluded}) {
285 foreach my $accno (@taxaccounts) {
286 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
288 $taxaccounts{$accno} += $taxamount;
289 $taxdiff += $taxamount;
291 $taxbase{$accno} += $taxbase;
293 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
295 foreach my $accno (@taxaccounts) {
296 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
297 $taxbase{$accno} += $taxbase;
301 foreach my $accno (@taxaccounts) {
302 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
303 $taxbase{$accno} += $taxbase;
306 my $tax_rate = $taxrate * 100;
307 push(@{ $form->{tax_rate} }, qq|$tax_rate|);
308 if ($form->{"assembly_$i"}) {
311 # get parts and push them onto the stack
313 if ($form->{groupitems}) {
315 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
317 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
321 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
323 JOIN parts p ON (a.parts_id = p.id)
324 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
325 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
326 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
328 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
329 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
330 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
331 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
332 push(@{ $form->{description} }, $sameitem);
335 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
337 push(@{ $form->{description} },
338 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
340 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
341 map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
349 foreach my $item (sort keys %taxaccounts) {
350 push(@{ $form->{taxbase} },
351 $form->format_amount($myconfig, $taxbase{$item}, 2));
353 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
355 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
356 push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
357 push(@{ $form->{taxrate} },
358 $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
359 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
362 for my $i (1 .. $form->{paidaccounts}) {
363 if ($form->{"paid_$i"}) {
364 push(@{ $form->{payment} }, $form->{"paid_$i"});
365 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
366 push(@{ $form->{paymentaccount} }, $description);
367 push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
368 push(@{ $form->{paymentsource} }, $form->{"source_$i"});
369 push(@{ $form->{paymentmemo} }, $form->{"memo_$i"});
371 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
374 if($form->{taxincluded}) {
375 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
378 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
381 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
382 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
383 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
384 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
386 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
387 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
389 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
390 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
392 $form->set_payment_options($myconfig, $form->{invdate});
394 $form->{username} = $myconfig->{name};
398 $main::lxdebug->leave_sub();
401 sub project_description {
402 $main::lxdebug->enter_sub();
404 my ($self, $dbh, $id) = @_;
405 my $form = \%main::form;
407 my $query = qq|SELECT description FROM project WHERE id = ?|;
408 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
410 $main::lxdebug->leave_sub();
415 sub customer_details {
416 $main::lxdebug->enter_sub();
418 my ($self, $myconfig, $form, @wanted_vars) = @_;
420 # connect to database
421 my $dbh = $form->dbconnect($myconfig);
423 # get contact id, set it if nessessary
426 my @values = (conv_i($form->{customer_id}));
429 if ($form->{cp_id}) {
430 $where = qq| AND (cp.cp_id = ?) |;
431 push(@values, conv_i($form->{cp_id}));
434 # get rest for the customer
436 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
437 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
439 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
440 WHERE (ct.id = ?) $where
443 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
445 # remove id and taxincluded before copy back
446 delete @$ref{qw(id taxincluded)};
448 @wanted_vars = grep({ $_ } @wanted_vars);
449 if (scalar(@wanted_vars) > 0) {
451 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
452 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
455 map { $form->{$_} = $ref->{$_} } keys %$ref;
457 if ($form->{delivery_customer_id}) {
459 qq|SELECT *, notes as customernotes
463 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
465 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
468 if ($form->{delivery_vendor_id}) {
470 qq|SELECT *, notes as customernotes
474 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
476 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
479 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
481 'trans_id' => $form->{customer_id});
482 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
486 $main::lxdebug->leave_sub();
490 $main::lxdebug->enter_sub();
492 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
494 # connect to database, turn off autocommit
495 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
497 my ($query, $sth, $null, $project_id, @values);
498 my $exchangerate = 0;
500 if (!$form->{employee_id}) {
501 $form->get_employee($dbh);
504 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
506 ($null, $form->{department_id}) = split(/--/, $form->{department});
508 my $all_units = AM->retrieve_units($myconfig, $form);
510 if (!$payments_only) {
512 &reverse_invoice($dbh, $form);
515 $query = qq|SELECT nextval('glid')|;
516 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
518 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
519 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
521 if (!$form->{invnumber}) {
523 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
524 "cnnumber" : "invnumber", $dbh);
529 my ($netamount, $invoicediff) = (0, 0);
530 my ($amount, $linetotal, $lastincomeaccno);
532 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
533 my $defaultcurrency = (split m/:/, $currencies)[0];
535 if ($form->{currency} eq $defaultcurrency) {
536 $form->{exchangerate} = 1;
538 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
541 $form->{exchangerate} =
544 : $form->parse_amount($myconfig, $form->{exchangerate});
546 $form->{expense_inventory} = "";
550 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
551 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
554 $form->{amount_cogs} = {};
556 foreach my $i (1 .. $form->{rowcount}) {
557 if ($form->{type} eq "credit_note") {
558 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
559 $form->{shipped} = 1;
561 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
566 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
567 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
568 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
570 if ($form->{storno}) {
571 $form->{"qty_$i"} *= -1;
574 if ($form->{"id_$i"}) {
577 if (defined($baseunits{$form->{"id_$i"}})) {
578 $item_unit = $baseunits{$form->{"id_$i"}};
581 $query = qq|SELECT unit FROM parts WHERE id = ?|;
582 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
583 $baseunits{$form->{"id_$i"}} = $item_unit;
586 if (defined($all_units->{$item_unit}->{factor})
587 && ($all_units->{$item_unit}->{factor} ne '')
588 && ($all_units->{$item_unit}->{factor} != 0)) {
589 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
593 $baseqty = $form->{"qty_$i"} * $basefactor;
595 my ($allocated, $taxrate) = (0, 0);
599 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
601 # keep entered selling price
603 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
605 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
607 my $decimalplaces = ($dec > 2) ? $dec : 2;
609 # undo discount formatting
610 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
613 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
615 # round linetotal to 2 decimal places
616 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
617 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
619 if ($form->{taxincluded}) {
620 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
621 $form->{"sellprice_$i"} =
622 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
624 $taxamount = $linetotal * $taxrate;
627 $netamount += $linetotal;
629 if ($taxamount != 0) {
631 $form->{amount}{ $form->{id} }{$_} +=
632 $taxamount * $form->{"${_}_rate"} / $taxrate
633 } split(/ /, $form->{"taxaccounts_$i"});
636 # add amount to income, $form->{amount}{trans_id}{accno}
637 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
639 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
640 $linetotal = $form->round_amount($linetotal, 2);
642 # this is the difference from the inventory
643 $invoicediff += ($amount - $linetotal);
645 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
648 $lastincomeaccno = $form->{"income_accno_$i"};
650 # adjust and round sellprice
651 $form->{"sellprice_$i"} =
652 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
655 next if $payments_only;
657 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
659 if ($form->{"assembly_$i"}) {
660 # record assembly item as allocated
661 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
664 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
668 # get pricegroup_id and save it
669 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
672 # save detail record in invoice table
674 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
675 sellprice, fxsellprice, discount, allocated, assemblyitem,
676 unit, deliverydate, project_id, serialnumber, pricegroup_id,
677 ordnumber, transdate, cusordnumber, base_qty, subtotal,
678 marge_percent, marge_total, lastcost,
679 price_factor_id, price_factor, marge_price_factor)
680 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
681 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
683 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
684 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
685 $form->{"sellprice_$i"}, $fxsellprice,
686 $form->{"discount_$i"}, $allocated, 'f',
687 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
688 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
689 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
690 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
691 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
692 $form->{"lastcost_$i"},
693 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
694 conv_i($form->{"marge_price_factor_$i"}));
695 do_query($form, $dbh, $query, @values);
697 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
699 qq|INSERT INTO licenseinvoice (trans_id, license_id)
700 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
701 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
702 do_query($form, $dbh, $query, @values);
707 $form->{datepaid} = $form->{invdate};
709 # total payments, don't move we need it here
710 for my $i (1 .. $form->{paidaccounts}) {
711 if ($form->{type} eq "credit_note") {
712 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
714 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
716 $form->{paid} += $form->{"paid_$i"};
717 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
720 my ($tax, $diff) = (0, 0);
722 $netamount = $form->round_amount($netamount, 2);
724 # figure out rounding errors for total amount vs netamount + taxes
725 if ($form->{taxincluded}) {
727 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
728 $diff += $amount - $netamount * $form->{exchangerate};
729 $netamount = $amount;
731 foreach my $item (split(/ /, $form->{taxaccounts})) {
732 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
733 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
734 $tax += $form->{amount}{ $form->{id} }{$item};
735 $netamount -= $form->{amount}{ $form->{id} }{$item};
738 $invoicediff += $diff;
739 ######## this only applies to tax included
740 if ($lastincomeaccno) {
741 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
745 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
746 $diff = $amount - $netamount * $form->{exchangerate};
747 $netamount = $amount;
748 foreach my $item (split(/ /, $form->{taxaccounts})) {
749 $form->{amount}{ $form->{id} }{$item} =
750 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
753 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
756 $amount - $form->{amount}{ $form->{id} }{$item} *
757 $form->{exchangerate};
758 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
759 $tax += $form->{amount}{ $form->{id} }{$item};
763 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
765 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
768 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
770 # update exchangerate
771 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
772 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
773 $form->{exchangerate}, 0);
776 $project_id = conv_i($form->{"globalproject_id"});
778 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
779 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
780 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
782 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
784 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
786 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
787 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
788 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
789 do_query($form, $dbh, $query, @values);
790 $form->{amount_cogs}{$trans_id}{$accno} = 0;
794 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
795 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
797 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
799 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
800 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
801 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
802 do_query($form, $dbh, $query, @values);
807 foreach my $trans_id (keys %{ $form->{amount} }) {
808 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
809 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
811 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
813 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
815 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
816 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
817 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
818 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
819 do_query($form, $dbh, $query, @values);
820 $form->{amount}{$trans_id}{$accno} = 0;
824 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
825 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
827 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
829 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
830 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
831 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
832 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
833 do_query($form, $dbh, $query, @values);
838 # deduct payment differences from diff
839 for my $i (1 .. $form->{paidaccounts}) {
840 if ($form->{"paid_$i"} != 0) {
842 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
843 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
847 # record payments and offsetting AR
848 if (!$form->{storno}) {
849 for my $i (1 .. $form->{paidaccounts}) {
851 next if ($form->{"paid_$i"} == 0);
853 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
854 $form->{"datepaid_$i"} = $form->{invdate}
855 unless ($form->{"datepaid_$i"});
856 $form->{datepaid} = $form->{"datepaid_$i"};
860 if ($form->{currency} eq $defaultcurrency) {
861 $form->{"exchangerate_$i"} = 1;
863 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
864 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
868 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
870 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
872 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
873 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
874 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
875 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
876 do_query($form, $dbh, $query, @values);
880 $form->{"paid_$i"} *= -1;
883 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
884 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
885 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
886 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
887 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
888 do_query($form, $dbh, $query, @values);
890 # exchangerate difference
891 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
892 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
896 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
897 $form->{"exchangerate_$i"};
899 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
902 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
908 # update exchange rate
909 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
910 $form->update_exchangerate($dbh, $form->{currency},
911 $form->{"datepaid_$i"},
912 $form->{"exchangerate_$i"}, 0);
916 } else { # if (!$form->{storno})
917 $form->{marge_total} *= -1;
920 if ($payments_only) {
921 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
922 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
924 if (!$provided_dbh) {
929 $main::lxdebug->leave_sub();
933 # record exchange rate differences and gains/losses
934 foreach my $accno (keys %{ $form->{fx} }) {
935 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
937 ($form->{fx}{$accno}{$transdate} =
938 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
943 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
944 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
945 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
946 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
947 do_query($form, $dbh, $query, @values);
952 $amount = $netamount + $tax;
955 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
957 $query = qq|UPDATE ar set
958 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
959 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
960 amount = ?, netamount = ?, paid = ?, datepaid = ?,
961 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
962 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
963 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
964 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
965 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
966 cp_id = ?, marge_total = ?, marge_percent = ?,
967 globalproject_id = ?, delivery_customer_id = ?,
968 transaction_description = ?, delivery_vendor_id = ?,
971 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
972 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
973 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
974 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
975 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
976 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
977 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
978 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
979 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
980 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
981 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
982 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
983 conv_i($form->{"id"}));
984 do_query($form, $dbh, $query, @values);
986 if($form->{"formname"} eq "credit_note") {
987 for my $i (1 .. $form->{rowcount}) {
988 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
989 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
990 do_query($form, $dbh, $query, @values);
994 if ($form->{storno}) {
997 paid = paid + amount,
999 intnotes = ? || intnotes
1001 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1002 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1006 $form->{name} = $form->{customer};
1007 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1009 if (!$form->{shipto_id}) {
1010 $form->add_shipto($dbh, $form->{id}, "AR");
1013 # save printed, emailed and queued
1014 $form->save_status($dbh);
1016 Common::webdav_folder($form) if ($main::webdav);
1018 # Link this record to the records it was created from.
1019 RecordLinks->create_links('dbh' => $dbh,
1021 'from_table' => 'oe',
1022 'from_ids' => $form->{convert_from_oe_ids},
1024 'to_id' => $form->{id},
1026 delete $form->{convert_from_oe_ids};
1028 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1030 if (scalar @convert_from_do_ids) {
1031 DO->close_orders('dbh' => $dbh,
1032 'ids' => \@convert_from_do_ids);
1034 RecordLinks->create_links('dbh' => $dbh,
1036 'from_table' => 'delivery_orders',
1037 'from_ids' => \@convert_from_do_ids,
1039 'to_id' => $form->{id},
1042 delete $form->{convert_from_do_ids};
1044 ARAP->close_orders_if_billed('dbh' => $dbh,
1045 'arap_id' => $form->{id},
1049 if (!$provided_dbh) {
1054 $main::lxdebug->leave_sub();
1059 sub _delete_payments {
1060 $main::lxdebug->enter_sub();
1062 my ($self, $form, $dbh) = @_;
1066 # Delete old payment entries from acc_trans.
1070 WHERE (trans_id = ?) AND fx_transaction
1076 LEFT JOIN chart c ON (at.chart_id = c.id)
1077 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1078 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1083 LEFT JOIN chart c ON (at.chart_id = c.id)
1084 WHERE (trans_id = ?)
1085 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1088 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1091 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1092 do_query($form, $dbh, $query);
1095 $main::lxdebug->leave_sub();
1099 $main::lxdebug->enter_sub();
1101 my ($self, $myconfig, $form, $locale) = @_;
1103 # connect to database, turn off autocommit
1104 my $dbh = $form->dbconnect_noauto($myconfig);
1106 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1108 $old_form = save_form();
1110 # Delete all entries in acc_trans from prior payments.
1111 $self->_delete_payments($form, $dbh);
1113 # Save the new payments the user made before cleaning up $form.
1114 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1116 # Clean up $form so that old content won't tamper the results.
1117 %keep_vars = map { $_, 1 } qw(login password id);
1118 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1120 # Retrieve the invoice from the database.
1121 $self->retrieve_invoice($myconfig, $form);
1123 # Set up the content of $form in the way that IS::post_invoice() expects.
1124 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1126 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1127 $item = $form->{invoice_details}->[$row - 1];
1129 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1131 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1134 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1136 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1138 # Restore the payment options from the user input.
1139 map { $form->{$_} = $payments{$_} } keys %payments;
1141 # Get the AR accno (which is normally done by Form::create_links()).
1145 LEFT JOIN chart c ON (at.chart_id = c.id)
1146 WHERE (trans_id = ?)
1147 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1151 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1153 # Post the new payments.
1154 $self->post_invoice($myconfig, $form, $dbh, 1);
1156 restore_form($old_form);
1158 my $rc = $dbh->commit();
1161 $main::lxdebug->leave_sub();
1166 sub process_assembly {
1167 $main::lxdebug->enter_sub();
1169 my ($dbh, $form, $id, $totalqty) = @_;
1172 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1173 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1175 JOIN parts p ON (a.parts_id = p.id)
1177 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1179 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1183 $ref->{inventory_accno_id} *= 1;
1184 $ref->{expense_accno_id} *= 1;
1186 # multiply by number of assemblies
1187 $ref->{qty} *= $totalqty;
1189 if ($ref->{assembly}) {
1190 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1193 if ($ref->{inventory_accno_id}) {
1194 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1198 # save detail record for individual assembly item in invoice table
1200 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1201 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1202 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1203 do_query($form, $dbh, $query, @values);
1209 $main::lxdebug->leave_sub();
1213 $main::lxdebug->enter_sub();
1215 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1219 $form->{taxzone_id} *=1;
1220 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1221 my $taxzone_id = $form->{"taxzone_id"} * 1;
1223 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1224 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1225 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1226 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1227 FROM invoice i, parts p
1228 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1229 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1230 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1231 WHERE (i.parts_id = p.id)
1232 AND (i.parts_id = ?)
1233 AND ((i.base_qty + i.allocated) < 0)
1235 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1240 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1241 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1245 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1247 # total expenses and inventory
1248 # sellprice is the cost of the item
1249 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1252 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1254 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1255 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1256 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1258 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1259 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1265 last if (($totalqty -= $qty) <= 0);
1270 $main::lxdebug->leave_sub();
1275 sub reverse_invoice {
1276 $main::lxdebug->enter_sub();
1278 my ($dbh, $form) = @_;
1280 # reverse inventory items
1282 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1284 JOIN parts p ON (i.parts_id = p.id)
1285 WHERE i.trans_id = ?|;
1286 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1288 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1290 if ($ref->{inventory_accno_id}) {
1291 # de-allocated purchases
1293 qq|SELECT i.id, i.trans_id, i.allocated
1295 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1296 ORDER BY i.trans_id DESC|;
1297 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1299 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1300 my $qty = $ref->{qty};
1301 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1302 $qty = $inhref->{allocated};
1306 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1308 last if (($ref->{qty} -= $qty) <= 0);
1317 my @values = (conv_i($form->{id}));
1318 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1319 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1321 if ($form->{lizenzen}) {
1323 qq|DELETE FROM licenseinvoice
1324 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1325 do_query($form, $dbh, $query, @values);
1328 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1330 $main::lxdebug->leave_sub();
1333 sub delete_invoice {
1334 $main::lxdebug->enter_sub();
1336 my ($self, $myconfig, $form, $spool) = @_;
1338 # connect to database
1339 my $dbh = $form->dbconnect_noauto($myconfig);
1341 &reverse_invoice($dbh, $form);
1343 my @values = (conv_i($form->{id}));
1346 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1348 # delete spool files
1349 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1351 # delete status entries
1352 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1354 my $rc = $dbh->commit;
1358 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1361 $main::lxdebug->leave_sub();
1366 sub retrieve_invoice {
1367 $main::lxdebug->enter_sub();
1369 my ($self, $myconfig, $form) = @_;
1371 # connect to database
1372 my $dbh = $form->dbconnect_noauto($myconfig);
1374 my ($sth, $ref, $query);
1376 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1380 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1381 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1382 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1383 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1384 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1385 d.curr AS currencies
1389 $ref = selectfirst_hashref_query($form, $dbh, $query);
1390 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1393 my $id = conv_i($form->{id});
1396 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1400 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1401 a.orddate, a.quodate, a.globalproject_id,
1402 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1403 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1404 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1405 a.employee_id, a.salesman_id, a.payment_id,
1406 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1407 a.transaction_description,
1408 a.marge_total, a.marge_percent,
1409 e.name AS employee, a.donumber
1411 LEFT JOIN employee e ON (e.id = a.employee_id)
1413 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1414 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1417 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1420 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1421 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1423 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1425 foreach my $vc (qw(customer vendor)) {
1426 next if !$form->{"delivery_${vc}_id"};
1427 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1430 # get printed, emailed
1431 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1432 $sth = prepare_execute_query($form, $dbh, $query, $id);
1434 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1435 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1436 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1437 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1440 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1442 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1443 : $form->{invdate} ? $dbh->quote($form->{invdate})
1447 my $taxzone_id = $form->{taxzone_id} *= 1;
1448 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1450 # retrieve individual items
1453 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1454 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1455 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1457 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1458 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1459 i.price_factor_id, i.price_factor, i.marge_price_factor,
1460 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1461 pr.projectnumber, pg.partsgroup, prg.pricegroup
1464 LEFT JOIN parts p ON (i.parts_id = p.id)
1465 LEFT JOIN project pr ON (i.project_id = pr.id)
1466 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1467 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1469 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1470 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1471 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1473 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1475 $sth = prepare_execute_query($form, $dbh, $query, $id);
1477 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1478 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1479 delete($ref->{"part_inventory_accno_id"});
1481 foreach my $type (qw(inventory income expense)) {
1482 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1483 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1484 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1488 # get tax rates and description
1489 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1491 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1492 LEFT JOIN chart c ON (c.id = t.chart_id)
1494 (SELECT tk.tax_id FROM taxkeys tk
1495 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1496 AND startdate <= date($transdate)
1497 ORDER BY startdate DESC LIMIT 1)
1499 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1500 $ref->{taxaccounts} = "";
1502 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1504 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1508 $ref->{taxaccounts} .= "$ptr->{accno} ";
1510 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1511 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1512 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1513 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1514 $form->{taxaccounts} .= "$ptr->{accno} ";
1519 if ($form->{lizenzen}) {
1520 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1521 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1522 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1525 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1527 chop $ref->{taxaccounts};
1528 push @{ $form->{invoice_details} }, $ref;
1533 Common::webdav_folder($form) if ($main::webdav);
1536 my $rc = $dbh->commit;
1539 $main::lxdebug->leave_sub();
1545 $main::lxdebug->enter_sub();
1547 my ($self, $myconfig, $form) = @_;
1549 # connect to database
1550 my $dbh = $form->dbconnect($myconfig);
1552 my $dateformat = $myconfig->{dateformat};
1553 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1555 my (@values, $duedate, $ref, $query);
1557 if ($form->{invdate}) {
1558 $duedate = "to_date(?, '$dateformat')";
1559 push @values, $form->{invdate};
1561 $duedate = "current_date";
1564 my $cid = conv_i($form->{customer_id});
1567 if ($form->{payment_id}) {
1568 $payment_id = "(pt.id = ?) OR";
1569 push @values, conv_i($form->{payment_id});
1575 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1576 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1577 c.street, c.zipcode, c.city, c.country,
1578 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1579 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1580 b.discount AS tradediscount, b.description AS business
1582 LEFT JOIN business b ON (b.id = c.business_id)
1583 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1586 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1587 map { $form->{$_} = $ref->{$_} } keys %$ref;
1590 qq|SELECT sum(amount - paid) AS dunning_amount
1592 WHERE (paid < amount)
1593 AND (customer_id = ?)
1594 AND (dunning_config_id IS NOT NULL)|;
1595 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1596 map { $form->{$_} = $ref->{$_} } keys %$ref;
1599 qq|SELECT dnn.dunning_description AS max_dunning_level
1600 FROM dunning_config dnn
1601 WHERE id IN (SELECT dunning_config_id
1603 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1604 ORDER BY dunning_level DESC LIMIT 1|;
1605 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1606 map { $form->{$_} = $ref->{$_} } keys %$ref;
1608 $form->{creditremaining} = $form->{creditlimit};
1609 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1610 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1611 $form->{creditremaining} -= $value;
1615 (SELECT e.buy FROM exchangerate e
1616 WHERE e.curr = o.curr
1617 AND e.transdate = o.transdate)
1619 WHERE o.customer_id = ?
1620 AND o.quotation = '0'
1621 AND o.closed = '0'|;
1622 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1624 while (my ($amount, $exch) = $sth->fetchrow_array) {
1625 $exch = 1 unless $exch;
1626 $form->{creditremaining} -= $amount * $exch;
1630 # get shipto if we did not converted an order or invoice
1631 if (!$form->{shipto}) {
1632 map { delete $form->{$_} }
1633 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1634 shiptostreet shiptozipcode shiptocity shiptocountry
1635 shiptocontact shiptophone shiptofax shiptoemail);
1637 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1638 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1640 map { $form->{$_} = $ref->{$_} } keys %$ref;
1643 # setup last accounts used for this customer
1644 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1646 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1648 JOIN acc_trans ac ON (ac.chart_id = c.id)
1649 JOIN ar a ON (a.id = ac.trans_id)
1650 WHERE a.customer_id = ?
1651 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1652 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1653 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1656 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1657 if ($ref->{category} eq 'I') {
1659 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1661 if ($form->{initial_transdate}) {
1663 qq|SELECT tk.tax_id, t.rate
1665 LEFT JOIN tax t ON tk.tax_id = t.id
1666 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1667 ORDER BY tk.startdate DESC
1669 my ($tax_id, $rate) =
1670 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1671 $form->{initial_transdate});
1672 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1675 if ($ref->{category} eq 'A') {
1676 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1680 $form->{rowcount} = $i if ($i && !$form->{type});
1685 $main::lxdebug->leave_sub();
1689 $main::lxdebug->enter_sub();
1691 my ($self, $myconfig, $form) = @_;
1693 # connect to database
1694 my $dbh = $form->dbconnect($myconfig);
1696 my $i = $form->{rowcount};
1698 my $where = qq|NOT p.obsolete = '1'|;
1701 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1702 my ($table, $field) = split m/\./, $column;
1703 next if !$form->{"${field}_${i}"};
1704 $where .= qq| AND lower(${column}) ILIKE ?|;
1705 push @values, '%' . $form->{"${field}_${i}"} . '%';
1708 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1709 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1710 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1711 push @values, $form->{"partnumber_$i"};
1714 if ($form->{"description_$i"}) {
1715 $where .= qq| ORDER BY p.description|;
1717 $where .= qq| ORDER BY p.partnumber|;
1721 if ($form->{type} eq "invoice") {
1723 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1724 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1728 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1732 my $taxzone_id = $form->{taxzone_id} * 1;
1733 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1737 p.id, p.partnumber, p.description, p.sellprice,
1738 p.listprice, p.inventory_accno_id, p.lastcost,
1740 c1.accno AS inventory_accno,
1741 c1.new_chart_id AS inventory_new_chart,
1742 date($transdate) - c1.valid_from AS inventory_valid,
1744 c2.accno AS income_accno,
1745 c2.new_chart_id AS income_new_chart,
1746 date($transdate) - c2.valid_from AS income_valid,
1748 c3.accno AS expense_accno,
1749 c3.new_chart_id AS expense_new_chart,
1750 date($transdate) - c3.valid_from AS expense_valid,
1752 p.unit, p.assembly, p.bin, p.onhand,
1753 p.notes AS partnotes, p.notes AS longdescription,
1754 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1757 pfac.factor AS price_factor,
1762 LEFT JOIN chart c1 ON
1763 ((SELECT inventory_accno_id
1764 FROM buchungsgruppen
1765 WHERE id = p.buchungsgruppen_id) = c1.id)
1766 LEFT JOIN chart c2 ON
1767 ((SELECT income_accno_id_${taxzone_id}
1768 FROM buchungsgruppen
1769 WHERE id = p.buchungsgruppen_id) = c2.id)
1770 LEFT JOIN chart c3 ON
1771 ((SELECT expense_accno_id_${taxzone_id}
1772 FROM buchungsgruppen
1773 WHERE id = p.buchungsgruppen_id) = c3.id)
1774 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1775 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1777 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1779 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1781 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1782 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1783 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1784 if (!$ref->{inventory_accno_id}) {
1785 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1787 delete($ref->{inventory_accno_id});
1789 foreach my $type (qw(inventory income expense)) {
1790 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1792 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1795 ($ref->{"${type}_accno"},
1796 $ref->{"${type}_new_chart"},
1797 $ref->{"${type}_valid"})
1798 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1802 if ($form->{payment_id} eq "") {
1803 $form->{payment_id} = $form->{part_payment_id};
1806 # get tax rates and description
1807 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1809 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1811 LEFT JOIN chart c ON (c.id = t.chart_id)
1815 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1817 ORDER BY startdate DESC
1820 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1821 my $stw = $dbh->prepare($query);
1822 $stw->execute(@values) || $form->dberror($query);
1824 $ref->{taxaccounts} = "";
1826 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1828 # if ($customertax{$ref->{accno}})
1829 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1833 $ref->{taxaccounts} .= "$ptr->{accno} ";
1835 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1836 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1837 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1838 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1839 $form->{taxaccounts} .= "$ptr->{accno} ";
1845 chop $ref->{taxaccounts};
1846 if ($form->{language_id}) {
1848 qq|SELECT tr.translation, tr.longdescription
1850 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1851 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1852 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1853 if ($translation ne "") {
1854 $ref->{description} = $translation;
1855 $ref->{longdescription} = $longdescription;
1859 qq|SELECT tr.translation, tr.longdescription
1861 WHERE tr.language_id IN
1864 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1867 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1868 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1869 if ($translation ne "") {
1870 $ref->{description} = $translation;
1871 $ref->{longdescription} = $longdescription;
1876 $ref->{onhand} *= 1;
1878 push @{ $form->{item_list} }, $ref;
1880 if ($form->{lizenzen}) {
1881 if ($ref->{inventory_accno} > 0) {
1885 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1886 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1887 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1888 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1897 $main::lxdebug->leave_sub();
1900 ##########################
1901 # get pricegroups from database
1902 # build up selected pricegroup
1903 # if an exchange rate - change price
1906 sub get_pricegroups_for_parts {
1908 $main::lxdebug->enter_sub();
1910 my ($self, $myconfig, $form) = @_;
1912 my $dbh = $form->dbconnect($myconfig);
1914 $form->{"PRICES"} = {};
1918 my $all_units = AM->retrieve_units($myconfig, $form);
1919 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1920 $form->{"PRICES"}{$i} = [];
1922 $id = $form->{"id_$i"};
1924 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1926 $id = $form->{"new_id_$i"};
1929 my ($price, $selectedpricegroup_id) = split(/--/,
1930 $form->{"sellprice_pg_$i"});
1932 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1933 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1934 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1936 my $price_new = $form->{"price_new_$i"};
1937 my $price_old = $form->{"price_old_$i"};
1939 if (!$form->{"unit_old_$i"}) {
1940 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1941 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1942 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1943 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1946 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1947 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1948 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1950 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1951 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1952 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1953 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1954 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1955 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1956 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1961 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1962 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1963 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1964 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1965 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1969 if (!$form->{"basefactor_$i"}) {
1970 $form->{"basefactor_$i"} = 1;
1976 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1977 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1987 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1989 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1990 'selected' AS selected
1993 ORDER BY pricegroup|;
1994 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1995 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1997 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
1999 $pkr->{selected} = '';
2001 # if there is an exchange rate change price
2002 if (($form->{exchangerate} * 1) != 0) {
2004 $pkr->{price} /= $form->{exchangerate};
2007 $pkr->{price} *= $form->{"basefactor_$i"};
2009 $pkr->{price} *= $basefactor;
2011 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2013 if ($selectedpricegroup_id eq undef) {
2014 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2016 $pkr->{selected} = ' selected';
2018 # no customer pricesgroup set
2019 if ($pkr->{price} == $pkr->{default_sellprice}) {
2021 $pkr->{price} = $form->{"sellprice_$i"};
2025 $form->{"sellprice_$i"} = $pkr->{price};
2028 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2029 $pkr->{price} = $form->{"sellprice_$i"};
2030 $pkr->{selected} = ' selected';
2034 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2035 if ($selectedpricegroup_id ne $pricegroup_old) {
2036 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2037 $pkr->{selected} = ' selected';
2039 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2040 if ($pkr->{pricegroup_id} == 0) {
2041 $pkr->{price} = $form->{"sellprice_$i"};
2042 $pkr->{selected} = ' selected';
2044 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2045 $pkr->{selected} = ' selected';
2046 if ( ($pkr->{pricegroup_id} == 0)
2047 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2048 # $pkr->{price} = $form->{"sellprice_$i"};
2050 $pkr->{price} = $form->{"sellprice_$i"};
2054 push @{ $form->{PRICES}{$i} }, $pkr;
2057 $form->{"basefactor_$i"} *= $basefactor;
2066 $main::lxdebug->leave_sub();
2070 $main::lxdebug->enter_sub();
2072 my ($self, $myconfig, $form, $table) = @_;
2074 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2076 # make sure there's no funny stuff in $table
2077 # ToDO: die when this happens and throw an error
2078 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2080 my $dbh = $form->dbconnect($myconfig);
2082 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2083 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2087 $main::lxdebug->leave_sub();
2093 $main::lxdebug->enter_sub();
2095 my ($self, $myconfig, $form, $table, $id) = @_;
2097 $main::lxdebug->leave_sub() and return 0 unless ($id);
2099 # make sure there's no funny stuff in $table
2100 # ToDO: die when this happens and throw an error
2101 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2103 my $dbh = $form->dbconnect($myconfig);
2105 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2106 my ($result) = selectrow_query($form, $dbh, $query, $id);
2110 $main::lxdebug->leave_sub();