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"});
370 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
373 if($form->{taxincluded}) {
374 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
377 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
380 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
381 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
382 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
383 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
385 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
386 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
388 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
389 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
391 $form->set_payment_options($myconfig, $form->{invdate});
393 $form->{username} = $myconfig->{name};
397 $main::lxdebug->leave_sub();
400 sub project_description {
401 $main::lxdebug->enter_sub();
403 my ($self, $dbh, $id) = @_;
404 my $form = \%main::form;
406 my $query = qq|SELECT description FROM project WHERE id = ?|;
407 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
409 $main::lxdebug->leave_sub();
414 sub customer_details {
415 $main::lxdebug->enter_sub();
417 my ($self, $myconfig, $form, @wanted_vars) = @_;
419 # connect to database
420 my $dbh = $form->dbconnect($myconfig);
422 # get contact id, set it if nessessary
425 my @values = (conv_i($form->{customer_id}));
428 if ($form->{cp_id}) {
429 $where = qq| AND (cp.cp_id = ?) |;
430 push(@values, conv_i($form->{cp_id}));
433 # get rest for the customer
435 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
436 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
438 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
439 WHERE (ct.id = ?) $where
442 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
444 # remove id and taxincluded before copy back
445 delete @$ref{qw(id taxincluded)};
447 @wanted_vars = grep({ $_ } @wanted_vars);
448 if (scalar(@wanted_vars) > 0) {
450 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
451 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
454 map { $form->{$_} = $ref->{$_} } keys %$ref;
456 if ($form->{delivery_customer_id}) {
458 qq|SELECT *, notes as customernotes
462 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
464 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
467 if ($form->{delivery_vendor_id}) {
469 qq|SELECT *, notes as customernotes
473 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
475 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
478 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
480 'trans_id' => $form->{customer_id});
481 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
485 $main::lxdebug->leave_sub();
489 $main::lxdebug->enter_sub();
491 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
493 # connect to database, turn off autocommit
494 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
496 my ($query, $sth, $null, $project_id, @values);
497 my $exchangerate = 0;
499 if (!$form->{employee_id}) {
500 $form->get_employee($dbh);
503 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
505 ($null, $form->{department_id}) = split(/--/, $form->{department});
507 my $all_units = AM->retrieve_units($myconfig, $form);
509 if (!$payments_only) {
511 &reverse_invoice($dbh, $form);
514 $query = qq|SELECT nextval('glid')|;
515 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
517 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
518 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
520 if (!$form->{invnumber}) {
522 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
523 "cnnumber" : "invnumber", $dbh);
528 my ($netamount, $invoicediff) = (0, 0);
529 my ($amount, $linetotal, $lastincomeaccno);
531 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
532 my $defaultcurrency = (split m/:/, $currencies)[0];
534 if ($form->{currency} eq $defaultcurrency) {
535 $form->{exchangerate} = 1;
537 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
540 $form->{exchangerate} =
543 : $form->parse_amount($myconfig, $form->{exchangerate});
545 $form->{expense_inventory} = "";
549 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
550 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
553 $form->{amount_cogs} = {};
555 foreach my $i (1 .. $form->{rowcount}) {
556 if ($form->{type} eq "credit_note") {
557 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
558 $form->{shipped} = 1;
560 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
565 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
566 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
567 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
569 if ($form->{storno}) {
570 $form->{"qty_$i"} *= -1;
573 if ($form->{"id_$i"}) {
576 if (defined($baseunits{$form->{"id_$i"}})) {
577 $item_unit = $baseunits{$form->{"id_$i"}};
580 $query = qq|SELECT unit FROM parts WHERE id = ?|;
581 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
582 $baseunits{$form->{"id_$i"}} = $item_unit;
585 if (defined($all_units->{$item_unit}->{factor})
586 && ($all_units->{$item_unit}->{factor} ne '')
587 && ($all_units->{$item_unit}->{factor} != 0)) {
588 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
592 $baseqty = $form->{"qty_$i"} * $basefactor;
594 my ($allocated, $taxrate) = (0, 0);
598 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
600 # keep entered selling price
602 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
604 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
606 my $decimalplaces = ($dec > 2) ? $dec : 2;
608 # undo discount formatting
609 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
612 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
614 # round linetotal to 2 decimal places
615 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
616 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
618 if ($form->{taxincluded}) {
619 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
620 $form->{"sellprice_$i"} =
621 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
623 $taxamount = $linetotal * $taxrate;
626 $netamount += $linetotal;
628 if ($taxamount != 0) {
630 $form->{amount}{ $form->{id} }{$_} +=
631 $taxamount * $form->{"${_}_rate"} / $taxrate
632 } split(/ /, $form->{"taxaccounts_$i"});
635 # add amount to income, $form->{amount}{trans_id}{accno}
636 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
638 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
639 $linetotal = $form->round_amount($linetotal, 2);
641 # this is the difference from the inventory
642 $invoicediff += ($amount - $linetotal);
644 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
647 $lastincomeaccno = $form->{"income_accno_$i"};
649 # adjust and round sellprice
650 $form->{"sellprice_$i"} =
651 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
654 next if $payments_only;
656 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
658 if ($form->{"assembly_$i"}) {
659 # record assembly item as allocated
660 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
663 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
667 # get pricegroup_id and save it
668 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
671 # save detail record in invoice table
673 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
674 sellprice, fxsellprice, discount, allocated, assemblyitem,
675 unit, deliverydate, project_id, serialnumber, pricegroup_id,
676 ordnumber, transdate, cusordnumber, base_qty, subtotal,
677 marge_percent, marge_total, lastcost,
678 price_factor_id, price_factor, marge_price_factor)
679 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
680 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
682 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
683 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
684 $form->{"sellprice_$i"}, $fxsellprice,
685 $form->{"discount_$i"}, $allocated, 'f',
686 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
687 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
688 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
689 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
690 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
691 $form->{"lastcost_$i"},
692 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
693 conv_i($form->{"marge_price_factor_$i"}));
694 do_query($form, $dbh, $query, @values);
696 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
698 qq|INSERT INTO licenseinvoice (trans_id, license_id)
699 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
700 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
701 do_query($form, $dbh, $query, @values);
706 $form->{datepaid} = $form->{invdate};
708 # total payments, don't move we need it here
709 for my $i (1 .. $form->{paidaccounts}) {
710 if ($form->{type} eq "credit_note") {
711 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
713 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
715 $form->{paid} += $form->{"paid_$i"};
716 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
719 my ($tax, $diff) = (0, 0);
721 $netamount = $form->round_amount($netamount, 2);
723 # figure out rounding errors for total amount vs netamount + taxes
724 if ($form->{taxincluded}) {
726 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
727 $diff += $amount - $netamount * $form->{exchangerate};
728 $netamount = $amount;
730 foreach my $item (split(/ /, $form->{taxaccounts})) {
731 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
732 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
733 $tax += $form->{amount}{ $form->{id} }{$item};
734 $netamount -= $form->{amount}{ $form->{id} }{$item};
737 $invoicediff += $diff;
738 ######## this only applies to tax included
739 if ($lastincomeaccno) {
740 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
744 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
745 $diff = $amount - $netamount * $form->{exchangerate};
746 $netamount = $amount;
747 foreach my $item (split(/ /, $form->{taxaccounts})) {
748 $form->{amount}{ $form->{id} }{$item} =
749 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
752 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
755 $amount - $form->{amount}{ $form->{id} }{$item} *
756 $form->{exchangerate};
757 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
758 $tax += $form->{amount}{ $form->{id} }{$item};
762 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
764 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
767 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
769 # update exchangerate
770 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
771 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
772 $form->{exchangerate}, 0);
775 $project_id = conv_i($form->{"globalproject_id"});
777 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
778 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
779 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
781 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
783 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
785 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
786 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
787 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
788 do_query($form, $dbh, $query, @values);
789 $form->{amount_cogs}{$trans_id}{$accno} = 0;
793 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
794 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
796 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
798 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
799 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
800 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
801 do_query($form, $dbh, $query, @values);
806 foreach my $trans_id (keys %{ $form->{amount} }) {
807 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
808 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
810 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
812 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
814 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
815 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
816 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
817 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
818 do_query($form, $dbh, $query, @values);
819 $form->{amount}{$trans_id}{$accno} = 0;
823 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
824 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
826 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
828 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
829 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
830 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
831 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
832 do_query($form, $dbh, $query, @values);
837 # deduct payment differences from diff
838 for my $i (1 .. $form->{paidaccounts}) {
839 if ($form->{"paid_$i"} != 0) {
841 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
842 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
846 # record payments and offsetting AR
847 if (!$form->{storno}) {
848 for my $i (1 .. $form->{paidaccounts}) {
850 next if ($form->{"paid_$i"} == 0);
852 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
853 $form->{"datepaid_$i"} = $form->{invdate}
854 unless ($form->{"datepaid_$i"});
855 $form->{datepaid} = $form->{"datepaid_$i"};
859 if ($form->{currency} eq $defaultcurrency) {
860 $form->{"exchangerate_$i"} = 1;
862 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
863 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
867 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
869 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
871 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
872 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
873 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
874 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
875 do_query($form, $dbh, $query, @values);
879 $form->{"paid_$i"} *= -1;
882 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
883 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
884 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
885 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
886 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
887 do_query($form, $dbh, $query, @values);
889 # exchangerate difference
890 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
891 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
895 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
896 $form->{"exchangerate_$i"};
898 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
901 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
907 # update exchange rate
908 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
909 $form->update_exchangerate($dbh, $form->{currency},
910 $form->{"datepaid_$i"},
911 $form->{"exchangerate_$i"}, 0);
915 } else { # if (!$form->{storno})
916 $form->{marge_total} *= -1;
919 if ($payments_only) {
920 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
921 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
923 if (!$provided_dbh) {
928 $main::lxdebug->leave_sub();
932 # record exchange rate differences and gains/losses
933 foreach my $accno (keys %{ $form->{fx} }) {
934 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
936 ($form->{fx}{$accno}{$transdate} =
937 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
942 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
943 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
944 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
945 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
946 do_query($form, $dbh, $query, @values);
951 $amount = $netamount + $tax;
954 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
956 $query = qq|UPDATE ar set
957 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
958 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
959 amount = ?, netamount = ?, paid = ?, datepaid = ?,
960 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
961 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
962 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
963 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
964 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
965 cp_id = ?, marge_total = ?, marge_percent = ?,
966 globalproject_id = ?, delivery_customer_id = ?,
967 transaction_description = ?, delivery_vendor_id = ?,
970 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
971 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
972 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
973 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
974 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
975 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
976 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
977 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
978 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
979 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
980 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
981 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
982 conv_i($form->{"id"}));
983 do_query($form, $dbh, $query, @values);
985 if($form->{"formname"} eq "credit_note") {
986 for my $i (1 .. $form->{rowcount}) {
987 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
988 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
989 do_query($form, $dbh, $query, @values);
993 if ($form->{storno}) {
996 paid = paid + amount,
998 intnotes = ? || intnotes
1000 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1001 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1005 $form->{name} = $form->{customer};
1006 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1008 if (!$form->{shipto_id}) {
1009 $form->add_shipto($dbh, $form->{id}, "AR");
1012 # save printed, emailed and queued
1013 $form->save_status($dbh);
1015 Common::webdav_folder($form) if ($main::webdav);
1017 # Link this record to the records it was created from.
1018 RecordLinks->create_links('dbh' => $dbh,
1020 'from_table' => 'oe',
1021 'from_ids' => $form->{convert_from_oe_ids},
1023 'to_id' => $form->{id},
1025 delete $form->{convert_from_oe_ids};
1027 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1029 if (scalar @convert_from_do_ids) {
1030 DO->close_orders('dbh' => $dbh,
1031 'ids' => \@convert_from_do_ids);
1033 RecordLinks->create_links('dbh' => $dbh,
1035 'from_table' => 'delivery_orders',
1036 'from_ids' => \@convert_from_do_ids,
1038 'to_id' => $form->{id},
1041 delete $form->{convert_from_do_ids};
1043 ARAP->close_orders_if_billed('dbh' => $dbh,
1044 'arap_id' => $form->{id},
1048 if (!$provided_dbh) {
1053 $main::lxdebug->leave_sub();
1058 sub _delete_payments {
1059 $main::lxdebug->enter_sub();
1061 my ($self, $form, $dbh) = @_;
1065 # Delete old payment entries from acc_trans.
1069 WHERE (trans_id = ?) AND fx_transaction
1075 LEFT JOIN chart c ON (at.chart_id = c.id)
1076 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1077 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1082 LEFT JOIN chart c ON (at.chart_id = c.id)
1083 WHERE (trans_id = ?)
1084 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1087 push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1090 $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
1091 do_query($form, $dbh, $query);
1094 $main::lxdebug->leave_sub();
1098 $main::lxdebug->enter_sub();
1100 my ($self, $myconfig, $form, $locale) = @_;
1102 # connect to database, turn off autocommit
1103 my $dbh = $form->dbconnect_noauto($myconfig);
1105 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1107 $old_form = save_form();
1109 # Delete all entries in acc_trans from prior payments.
1110 $self->_delete_payments($form, $dbh);
1112 # Save the new payments the user made before cleaning up $form.
1113 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1115 # Clean up $form so that old content won't tamper the results.
1116 %keep_vars = map { $_, 1 } qw(login password id);
1117 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1119 # Retrieve the invoice from the database.
1120 $self->retrieve_invoice($myconfig, $form);
1122 # Set up the content of $form in the way that IS::post_invoice() expects.
1123 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1125 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1126 $item = $form->{invoice_details}->[$row - 1];
1128 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1130 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1133 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1135 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1137 # Restore the payment options from the user input.
1138 map { $form->{$_} = $payments{$_} } keys %payments;
1140 # Get the AR accno (which is normally done by Form::create_links()).
1144 LEFT JOIN chart c ON (at.chart_id = c.id)
1145 WHERE (trans_id = ?)
1146 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1150 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1152 # Post the new payments.
1153 $self->post_invoice($myconfig, $form, $dbh, 1);
1155 restore_form($old_form);
1157 my $rc = $dbh->commit();
1160 $main::lxdebug->leave_sub();
1165 sub process_assembly {
1166 $main::lxdebug->enter_sub();
1168 my ($dbh, $form, $id, $totalqty) = @_;
1171 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1172 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1174 JOIN parts p ON (a.parts_id = p.id)
1176 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1178 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1182 $ref->{inventory_accno_id} *= 1;
1183 $ref->{expense_accno_id} *= 1;
1185 # multiply by number of assemblies
1186 $ref->{qty} *= $totalqty;
1188 if ($ref->{assembly}) {
1189 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1192 if ($ref->{inventory_accno_id}) {
1193 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1197 # save detail record for individual assembly item in invoice table
1199 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1200 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1201 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1202 do_query($form, $dbh, $query, @values);
1208 $main::lxdebug->leave_sub();
1212 $main::lxdebug->enter_sub();
1214 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1218 $form->{taxzone_id} *=1;
1219 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1220 my $taxzone_id = $form->{"taxzone_id"} * 1;
1222 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1223 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1224 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1225 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1226 FROM invoice i, parts p
1227 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1228 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1229 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1230 WHERE (i.parts_id = p.id)
1231 AND (i.parts_id = ?)
1232 AND ((i.base_qty + i.allocated) < 0)
1234 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1239 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1240 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1244 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1246 # total expenses and inventory
1247 # sellprice is the cost of the item
1248 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1251 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1253 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1254 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1255 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1257 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1258 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1264 last if (($totalqty -= $qty) <= 0);
1269 $main::lxdebug->leave_sub();
1274 sub reverse_invoice {
1275 $main::lxdebug->enter_sub();
1277 my ($dbh, $form) = @_;
1279 # reverse inventory items
1281 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1283 JOIN parts p ON (i.parts_id = p.id)
1284 WHERE i.trans_id = ?|;
1285 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1287 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1289 if ($ref->{inventory_accno_id}) {
1290 # de-allocated purchases
1292 qq|SELECT i.id, i.trans_id, i.allocated
1294 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1295 ORDER BY i.trans_id DESC|;
1296 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1298 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1299 my $qty = $ref->{qty};
1300 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1301 $qty = $inhref->{allocated};
1305 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1307 last if (($ref->{qty} -= $qty) <= 0);
1316 my @values = (conv_i($form->{id}));
1317 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1318 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1320 if ($form->{lizenzen}) {
1322 qq|DELETE FROM licenseinvoice
1323 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1324 do_query($form, $dbh, $query, @values);
1327 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1329 $main::lxdebug->leave_sub();
1332 sub delete_invoice {
1333 $main::lxdebug->enter_sub();
1335 my ($self, $myconfig, $form, $spool) = @_;
1337 # connect to database
1338 my $dbh = $form->dbconnect_noauto($myconfig);
1340 &reverse_invoice($dbh, $form);
1342 my @values = (conv_i($form->{id}));
1345 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1347 # delete spool files
1348 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1350 # delete status entries
1351 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1353 my $rc = $dbh->commit;
1357 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1360 $main::lxdebug->leave_sub();
1365 sub retrieve_invoice {
1366 $main::lxdebug->enter_sub();
1368 my ($self, $myconfig, $form) = @_;
1370 # connect to database
1371 my $dbh = $form->dbconnect_noauto($myconfig);
1373 my ($sth, $ref, $query);
1375 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1379 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1380 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1381 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1382 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1383 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1384 d.curr AS currencies
1388 $ref = selectfirst_hashref_query($form, $dbh, $query);
1389 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1392 my $id = conv_i($form->{id});
1395 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1399 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1400 a.orddate, a.quodate, a.globalproject_id,
1401 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1402 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1403 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1404 a.employee_id, a.salesman_id, a.payment_id,
1405 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1406 a.transaction_description,
1407 a.marge_total, a.marge_percent,
1408 e.name AS employee, a.donumber
1410 LEFT JOIN employee e ON (e.id = a.employee_id)
1412 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1413 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1416 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1419 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1420 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1422 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1424 foreach my $vc (qw(customer vendor)) {
1425 next if !$form->{"delivery_${vc}_id"};
1426 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1429 # get printed, emailed
1430 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1431 $sth = prepare_execute_query($form, $dbh, $query, $id);
1433 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1434 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1435 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1436 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1439 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1441 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1442 : $form->{invdate} ? $dbh->quote($form->{invdate})
1446 my $taxzone_id = $form->{taxzone_id} *= 1;
1447 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1449 # retrieve individual items
1452 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1453 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1454 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1456 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1457 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1458 i.price_factor_id, i.price_factor, i.marge_price_factor,
1459 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1460 pr.projectnumber, pg.partsgroup, prg.pricegroup
1463 LEFT JOIN parts p ON (i.parts_id = p.id)
1464 LEFT JOIN project pr ON (i.project_id = pr.id)
1465 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1466 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1468 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1469 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1470 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1472 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1474 $sth = prepare_execute_query($form, $dbh, $query, $id);
1476 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1477 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1478 delete($ref->{"part_inventory_accno_id"});
1480 foreach my $type (qw(inventory income expense)) {
1481 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1482 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1483 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1487 # get tax rates and description
1488 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1490 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1491 LEFT JOIN chart c ON (c.id = t.chart_id)
1493 (SELECT tk.tax_id FROM taxkeys tk
1494 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1495 AND startdate <= date($transdate)
1496 ORDER BY startdate DESC LIMIT 1)
1498 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1499 $ref->{taxaccounts} = "";
1501 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1503 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1507 $ref->{taxaccounts} .= "$ptr->{accno} ";
1509 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1510 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1511 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1512 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1513 $form->{taxaccounts} .= "$ptr->{accno} ";
1518 if ($form->{lizenzen}) {
1519 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1520 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1521 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1524 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1526 chop $ref->{taxaccounts};
1527 push @{ $form->{invoice_details} }, $ref;
1532 Common::webdav_folder($form) if ($main::webdav);
1535 my $rc = $dbh->commit;
1538 $main::lxdebug->leave_sub();
1544 $main::lxdebug->enter_sub();
1546 my ($self, $myconfig, $form) = @_;
1548 # connect to database
1549 my $dbh = $form->dbconnect($myconfig);
1551 my $dateformat = $myconfig->{dateformat};
1552 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1554 my (@values, $duedate, $ref, $query);
1556 if ($form->{invdate}) {
1557 $duedate = "to_date(?, '$dateformat')";
1558 push @values, $form->{invdate};
1560 $duedate = "current_date";
1563 my $cid = conv_i($form->{customer_id});
1566 if ($form->{payment_id}) {
1567 $payment_id = "(pt.id = ?) OR";
1568 push @values, conv_i($form->{payment_id});
1574 c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
1575 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1576 c.street, c.zipcode, c.city, c.country,
1577 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1578 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1579 b.discount AS tradediscount, b.description AS business
1581 LEFT JOIN business b ON (b.id = c.business_id)
1582 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1585 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1586 map { $form->{$_} = $ref->{$_} } keys %$ref;
1589 qq|SELECT sum(amount - paid) AS dunning_amount
1591 WHERE (paid < amount)
1592 AND (customer_id = ?)
1593 AND (dunning_config_id IS NOT NULL)|;
1594 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1595 map { $form->{$_} = $ref->{$_} } keys %$ref;
1598 qq|SELECT dnn.dunning_description AS max_dunning_level
1599 FROM dunning_config dnn
1600 WHERE id IN (SELECT dunning_config_id
1602 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1603 ORDER BY dunning_level DESC LIMIT 1|;
1604 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1605 map { $form->{$_} = $ref->{$_} } keys %$ref;
1607 $form->{creditremaining} = $form->{creditlimit};
1608 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1609 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1610 $form->{creditremaining} -= $value;
1614 (SELECT e.buy FROM exchangerate e
1615 WHERE e.curr = o.curr
1616 AND e.transdate = o.transdate)
1618 WHERE o.customer_id = ?
1619 AND o.quotation = '0'
1620 AND o.closed = '0'|;
1621 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1623 while (my ($amount, $exch) = $sth->fetchrow_array) {
1624 $exch = 1 unless $exch;
1625 $form->{creditremaining} -= $amount * $exch;
1629 # get shipto if we did not converted an order or invoice
1630 if (!$form->{shipto}) {
1631 map { delete $form->{$_} }
1632 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1633 shiptostreet shiptozipcode shiptocity shiptocountry
1634 shiptocontact shiptophone shiptofax shiptoemail);
1636 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1637 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1639 map { $form->{$_} = $ref->{$_} } keys %$ref;
1642 # setup last accounts used for this customer
1643 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1645 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1647 JOIN acc_trans ac ON (ac.chart_id = c.id)
1648 JOIN ar a ON (a.id = ac.trans_id)
1649 WHERE a.customer_id = ?
1650 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1651 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1652 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1655 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1656 if ($ref->{category} eq 'I') {
1658 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1660 if ($form->{initial_transdate}) {
1662 qq|SELECT tk.tax_id, t.rate
1664 LEFT JOIN tax t ON tk.tax_id = t.id
1665 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1666 ORDER BY tk.startdate DESC
1668 my ($tax_id, $rate) =
1669 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1670 $form->{initial_transdate});
1671 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1674 if ($ref->{category} eq 'A') {
1675 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1679 $form->{rowcount} = $i if ($i && !$form->{type});
1684 $main::lxdebug->leave_sub();
1688 $main::lxdebug->enter_sub();
1690 my ($self, $myconfig, $form) = @_;
1692 # connect to database
1693 my $dbh = $form->dbconnect($myconfig);
1695 my $i = $form->{rowcount};
1697 my $where = qq|NOT p.obsolete = '1'|;
1700 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1701 my ($table, $field) = split m/\./, $column;
1702 next if !$form->{"${field}_${i}"};
1703 $where .= qq| AND lower(${column}) ILIKE ?|;
1704 push @values, '%' . $form->{"${field}_${i}"} . '%';
1707 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1708 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1709 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1710 push @values, $form->{"partnumber_$i"};
1713 if ($form->{"description_$i"}) {
1714 $where .= qq| ORDER BY p.description|;
1716 $where .= qq| ORDER BY p.partnumber|;
1720 if ($form->{type} eq "invoice") {
1722 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1723 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1727 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1731 my $taxzone_id = $form->{taxzone_id} * 1;
1732 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1736 p.id, p.partnumber, p.description, p.sellprice,
1737 p.listprice, p.inventory_accno_id, p.lastcost,
1739 c1.accno AS inventory_accno,
1740 c1.new_chart_id AS inventory_new_chart,
1741 date($transdate) - c1.valid_from AS inventory_valid,
1743 c2.accno AS income_accno,
1744 c2.new_chart_id AS income_new_chart,
1745 date($transdate) - c2.valid_from AS income_valid,
1747 c3.accno AS expense_accno,
1748 c3.new_chart_id AS expense_new_chart,
1749 date($transdate) - c3.valid_from AS expense_valid,
1751 p.unit, p.assembly, p.bin, p.onhand,
1752 p.notes AS partnotes, p.notes AS longdescription,
1753 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1756 pfac.factor AS price_factor,
1761 LEFT JOIN chart c1 ON
1762 ((SELECT inventory_accno_id
1763 FROM buchungsgruppen
1764 WHERE id = p.buchungsgruppen_id) = c1.id)
1765 LEFT JOIN chart c2 ON
1766 ((SELECT income_accno_id_${taxzone_id}
1767 FROM buchungsgruppen
1768 WHERE id = p.buchungsgruppen_id) = c2.id)
1769 LEFT JOIN chart c3 ON
1770 ((SELECT expense_accno_id_${taxzone_id}
1771 FROM buchungsgruppen
1772 WHERE id = p.buchungsgruppen_id) = c3.id)
1773 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1774 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1776 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1778 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1780 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1781 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1782 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1783 if (!$ref->{inventory_accno_id}) {
1784 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1786 delete($ref->{inventory_accno_id});
1788 foreach my $type (qw(inventory income expense)) {
1789 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1791 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1794 ($ref->{"${type}_accno"},
1795 $ref->{"${type}_new_chart"},
1796 $ref->{"${type}_valid"})
1797 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1801 if ($form->{payment_id} eq "") {
1802 $form->{payment_id} = $form->{part_payment_id};
1805 # get tax rates and description
1806 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1808 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1810 LEFT JOIN chart c ON (c.id = t.chart_id)
1814 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1816 ORDER BY startdate DESC
1819 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1820 my $stw = $dbh->prepare($query);
1821 $stw->execute(@values) || $form->dberror($query);
1823 $ref->{taxaccounts} = "";
1825 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1827 # if ($customertax{$ref->{accno}})
1828 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1832 $ref->{taxaccounts} .= "$ptr->{accno} ";
1834 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1835 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1836 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1837 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1838 $form->{taxaccounts} .= "$ptr->{accno} ";
1844 chop $ref->{taxaccounts};
1845 if ($form->{language_id}) {
1847 qq|SELECT tr.translation, tr.longdescription
1849 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1850 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1851 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1852 if ($translation ne "") {
1853 $ref->{description} = $translation;
1854 $ref->{longdescription} = $longdescription;
1858 qq|SELECT tr.translation, tr.longdescription
1860 WHERE tr.language_id IN
1863 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1866 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1867 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1868 if ($translation ne "") {
1869 $ref->{description} = $translation;
1870 $ref->{longdescription} = $longdescription;
1875 $ref->{onhand} *= 1;
1877 push @{ $form->{item_list} }, $ref;
1879 if ($form->{lizenzen}) {
1880 if ($ref->{inventory_accno} > 0) {
1884 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1885 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1886 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1887 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1896 $main::lxdebug->leave_sub();
1899 ##########################
1900 # get pricegroups from database
1901 # build up selected pricegroup
1902 # if an exchange rate - change price
1905 sub get_pricegroups_for_parts {
1907 $main::lxdebug->enter_sub();
1909 my ($self, $myconfig, $form) = @_;
1911 my $dbh = $form->dbconnect($myconfig);
1913 $form->{"PRICES"} = {};
1917 my $all_units = AM->retrieve_units($myconfig, $form);
1918 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1919 $form->{"PRICES"}{$i} = [];
1921 $id = $form->{"id_$i"};
1923 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1925 $id = $form->{"new_id_$i"};
1928 my ($price, $selectedpricegroup_id) = split(/--/,
1929 $form->{"sellprice_pg_$i"});
1931 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1932 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1933 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1935 my $price_new = $form->{"price_new_$i"};
1936 my $price_old = $form->{"price_old_$i"};
1938 if (!$form->{"unit_old_$i"}) {
1939 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1940 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1941 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1942 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1945 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1946 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1947 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1949 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1950 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1951 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1952 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1953 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1954 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1955 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1960 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1961 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1962 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1963 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1964 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1968 if (!$form->{"basefactor_$i"}) {
1969 $form->{"basefactor_$i"} = 1;
1975 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1976 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1986 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1988 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1989 'selected' AS selected
1992 ORDER BY pricegroup|;
1993 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1994 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1996 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
1998 $pkr->{selected} = '';
2000 # if there is an exchange rate change price
2001 if (($form->{exchangerate} * 1) != 0) {
2003 $pkr->{price} /= $form->{exchangerate};
2006 $pkr->{price} *= $form->{"basefactor_$i"};
2008 $pkr->{price} *= $basefactor;
2010 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2012 if ($selectedpricegroup_id eq undef) {
2013 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2015 $pkr->{selected} = ' selected';
2017 # no customer pricesgroup set
2018 if ($pkr->{price} == $pkr->{default_sellprice}) {
2020 $pkr->{price} = $form->{"sellprice_$i"};
2024 $form->{"sellprice_$i"} = $pkr->{price};
2027 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2028 $pkr->{price} = $form->{"sellprice_$i"};
2029 $pkr->{selected} = ' selected';
2033 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2034 if ($selectedpricegroup_id ne $pricegroup_old) {
2035 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2036 $pkr->{selected} = ' selected';
2038 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2039 if ($pkr->{pricegroup_id} == 0) {
2040 $pkr->{price} = $form->{"sellprice_$i"};
2041 $pkr->{selected} = ' selected';
2043 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2044 $pkr->{selected} = ' selected';
2045 if ( ($pkr->{pricegroup_id} == 0)
2046 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2047 # $pkr->{price} = $form->{"sellprice_$i"};
2049 $pkr->{price} = $form->{"sellprice_$i"};
2053 push @{ $form->{PRICES}{$i} }, $pkr;
2056 $form->{"basefactor_$i"} *= $basefactor;
2065 $main::lxdebug->leave_sub();
2069 $main::lxdebug->enter_sub();
2071 my ($self, $myconfig, $form, $table) = @_;
2073 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2075 # make sure there's no funny stuff in $table
2076 # ToDO: die when this happens and throw an error
2077 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2079 my $dbh = $form->dbconnect($myconfig);
2081 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2082 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2086 $main::lxdebug->leave_sub();
2092 $main::lxdebug->enter_sub();
2094 my ($self, $myconfig, $form, $table, $id) = @_;
2096 $main::lxdebug->leave_sub() and return 0 unless ($id);
2098 # make sure there's no funny stuff in $table
2099 # ToDO: die when this happens and throw an error
2100 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2102 my $dbh = $form->dbconnect($myconfig);
2104 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2105 my ($result) = selectrow_query($form, $dbh, $query, $id);
2109 $main::lxdebug->leave_sub();