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);
66 $form->{TEMPLATE_ARRAYS} = {};
68 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
70 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
73 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
74 $price_factors{$pfac->{id}} = $pfac;
76 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
79 # sort items by partsgroup
80 for my $i (1 .. $form->{rowcount}) {
82 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
83 # $partsgroup = $form->{"partsgroup_$i"};
85 # push @partsgroup, [$i, $partsgroup];
86 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
90 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
91 join(", ", map({ "?" } @project_ids)) . ")";
92 $sth = $dbh->prepare($query);
93 $sth->execute(@project_ids) ||
94 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
95 while (my $ref = $sth->fetchrow_hashref()) {
96 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
101 $form->{"globalprojectnumber"} =
102 $projectnumbers{$form->{"globalproject_id"}};
109 my %oid = ('Pg' => 'oid',
110 'Oracle' => 'rowid');
112 # sort items by partsgroup
113 for $i (1 .. $form->{rowcount}) {
115 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
116 $partsgroup = $form->{"partsgroup_$i"};
118 push @partsgroup, [$i, $partsgroup];
131 my $nodiscount_subtotal = 0;
132 my $discount_subtotal = 0;
134 my $subtotal_header = 0;
137 $form->{discount} = [];
139 IC->prepare_parts_for_printing();
142 qw(runningnumber number description longdescription qty ship unit bin
143 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
144 partnotes serialnumber reqdate sellprice listprice netprice
145 discount p_discount discount_sub nodiscount_sub
146 linetotal nodiscount_linetotal tax_rate projectnumber
147 price_factor price_factor_name partsgroup);
149 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
151 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
153 map { $form->{TEMPLATE_ARRAYS}{$_} => [] } (@arrays, @tax_arrays, @payment_arrays);
155 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
158 if ($item->[1] ne $sameitem) {
159 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
160 $sameitem = $item->[1];
162 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
165 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
167 if ($form->{"id_$i"} != 0) {
169 # add number, description and qty to $form->{number},
170 if ($form->{"subtotal_$i"} && !$subtotal_header) {
171 $subtotal_header = $i;
172 $position = int($position);
175 } elsif ($subtotal_header) {
177 $position = int($position);
178 $position = $position.".".$subposition;
180 $position = int($position);
184 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
186 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
187 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
188 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
189 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
190 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
191 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
192 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
193 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
194 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
195 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"deliverydate_$i"};
196 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
202 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
203 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
206 if ($form->{lizenzen}) {
207 if ($form->{"licensenumber_$i"}) {
208 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
209 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
210 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
211 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
214 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
215 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
220 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
222 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
223 my ($dec) = ($sellprice =~ /\.(\d+)/);
224 my $decimalplaces = max 2, length($dec);
226 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
229 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
231 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
232 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
234 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
236 $linetotal = ($linetotal != 0) ? $linetotal : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
241 $form->{total} += $linetotal;
242 $form->{nodiscount_total} += $nodiscount_linetotal;
243 $form->{discount_total} += $discount;
245 if ($subtotal_header) {
246 $discount_subtotal += $linetotal;
247 $nodiscount_subtotal += $nodiscount_linetotal;
250 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
252 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
254 $discount_subtotal = 0;
255 $nodiscount_subtotal = 0;
256 $subtotal_header = 0;
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
263 if (!$form->{"discount_$i"}) {
264 $nodiscount += $linetotal;
267 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
268 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
270 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
272 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
276 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
278 if ($form->{taxincluded}) {
281 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
282 $taxbase = $linetotal - $taxamount;
284 $taxamount = $linetotal * $taxrate;
285 $taxbase = $linetotal;
288 if ($form->round_amount($taxrate, 7) == 0) {
289 if ($form->{taxincluded}) {
290 foreach my $accno (@taxaccounts) {
291 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
293 $taxaccounts{$accno} += $taxamount;
294 $taxdiff += $taxamount;
296 $taxbase{$accno} += $taxbase;
298 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
300 foreach my $accno (@taxaccounts) {
301 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
302 $taxbase{$accno} += $taxbase;
306 foreach my $accno (@taxaccounts) {
307 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
308 $taxbase{$accno} += $taxbase;
311 my $tax_rate = $taxrate * 100;
312 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
313 if ($form->{"assembly_$i"}) {
316 # get parts and push them onto the stack
318 if ($form->{groupitems}) {
320 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
322 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
326 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
328 JOIN parts p ON (a.parts_id = p.id)
329 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
330 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
331 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
333 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
334 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
335 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
336 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
337 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
340 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
342 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
343 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
345 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
354 foreach my $item (sort keys %taxaccounts) {
355 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
357 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
358 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
359 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
360 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
361 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
364 for my $i (1 .. $form->{paidaccounts}) {
365 if ($form->{"paid_$i"}) {
366 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
368 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
369 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
370 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
371 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
372 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
374 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
377 if($form->{taxincluded}) {
378 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
381 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
384 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
385 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
386 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
387 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
389 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
390 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
392 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
393 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
395 $form->set_payment_options($myconfig, $form->{invdate});
397 $form->{username} = $myconfig->{name};
401 $main::lxdebug->leave_sub();
404 sub project_description {
405 $main::lxdebug->enter_sub();
407 my ($self, $dbh, $id) = @_;
408 my $form = \%main::form;
410 my $query = qq|SELECT description FROM project WHERE id = ?|;
411 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
413 $main::lxdebug->leave_sub();
418 sub customer_details {
419 $main::lxdebug->enter_sub();
421 my ($self, $myconfig, $form, @wanted_vars) = @_;
423 # connect to database
424 my $dbh = $form->dbconnect($myconfig);
426 # get contact id, set it if nessessary
429 my @values = (conv_i($form->{customer_id}));
432 if ($form->{cp_id}) {
433 $where = qq| AND (cp.cp_id = ?) |;
434 push(@values, conv_i($form->{cp_id}));
437 # get rest for the customer
439 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
440 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
442 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
443 WHERE (ct.id = ?) $where
446 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
448 # remove id and taxincluded before copy back
449 delete @$ref{qw(id taxincluded)};
451 @wanted_vars = grep({ $_ } @wanted_vars);
452 if (scalar(@wanted_vars) > 0) {
454 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
455 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
458 map { $form->{$_} = $ref->{$_} } keys %$ref;
460 if ($form->{delivery_customer_id}) {
462 qq|SELECT *, notes as customernotes
466 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
468 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
471 if ($form->{delivery_vendor_id}) {
473 qq|SELECT *, notes as customernotes
477 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
479 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
482 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
484 'trans_id' => $form->{customer_id});
485 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
489 $main::lxdebug->leave_sub();
493 $main::lxdebug->enter_sub();
495 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
497 # connect to database, turn off autocommit
498 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
500 my ($query, $sth, $null, $project_id, @values);
501 my $exchangerate = 0;
503 if (!$form->{employee_id}) {
504 $form->get_employee($dbh);
507 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
509 ($null, $form->{department_id}) = split(/--/, $form->{department});
511 my $all_units = AM->retrieve_units($myconfig, $form);
513 if (!$payments_only) {
515 &reverse_invoice($dbh, $form);
518 $query = qq|SELECT nextval('glid')|;
519 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
521 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
522 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
524 if (!$form->{invnumber}) {
526 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
527 "cnnumber" : "invnumber", $dbh);
532 my ($netamount, $invoicediff) = (0, 0);
533 my ($amount, $linetotal, $lastincomeaccno);
535 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
536 my $defaultcurrency = (split m/:/, $currencies)[0];
538 if ($form->{currency} eq $defaultcurrency) {
539 $form->{exchangerate} = 1;
541 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
544 $form->{exchangerate} =
547 : $form->parse_amount($myconfig, $form->{exchangerate});
549 $form->{expense_inventory} = "";
553 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
554 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
557 $form->{amount} = {};
558 $form->{amount_cogs} = {};
560 foreach my $i (1 .. $form->{rowcount}) {
561 if ($form->{type} eq "credit_note") {
562 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
563 $form->{shipped} = 1;
565 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
570 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
571 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
572 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
574 if ($form->{storno}) {
575 $form->{"qty_$i"} *= -1;
578 if ($form->{"id_$i"}) {
581 if (defined($baseunits{$form->{"id_$i"}})) {
582 $item_unit = $baseunits{$form->{"id_$i"}};
585 $query = qq|SELECT unit FROM parts WHERE id = ?|;
586 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
587 $baseunits{$form->{"id_$i"}} = $item_unit;
590 if (defined($all_units->{$item_unit}->{factor})
591 && ($all_units->{$item_unit}->{factor} ne '')
592 && ($all_units->{$item_unit}->{factor} != 0)) {
593 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
597 $baseqty = $form->{"qty_$i"} * $basefactor;
599 my ($allocated, $taxrate) = (0, 0);
603 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
605 # keep entered selling price
607 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
609 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
611 my $decimalplaces = ($dec > 2) ? $dec : 2;
613 # undo discount formatting
614 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
617 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
619 # round linetotal to 2 decimal places
620 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
621 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
623 if ($form->{taxincluded}) {
624 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
625 $form->{"sellprice_$i"} =
626 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
628 $taxamount = $linetotal * $taxrate;
631 $netamount += $linetotal;
633 if ($taxamount != 0) {
635 $form->{amount}{ $form->{id} }{$_} +=
636 $taxamount * $form->{"${_}_rate"} / $taxrate
637 } split(/ /, $form->{"taxaccounts_$i"});
640 # add amount to income, $form->{amount}{trans_id}{accno}
641 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
643 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
644 $linetotal = $form->round_amount($linetotal, 2);
646 # this is the difference from the inventory
647 $invoicediff += ($amount - $linetotal);
649 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
652 $lastincomeaccno = $form->{"income_accno_$i"};
654 # adjust and round sellprice
655 $form->{"sellprice_$i"} =
656 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
659 next if $payments_only;
661 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
663 if ($form->{"assembly_$i"}) {
664 # record assembly item as allocated
665 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
668 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
672 # get pricegroup_id and save it
673 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
676 # save detail record in invoice table
678 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
679 sellprice, fxsellprice, discount, allocated, assemblyitem,
680 unit, deliverydate, project_id, serialnumber, pricegroup_id,
681 ordnumber, transdate, cusordnumber, base_qty, subtotal,
682 marge_percent, marge_total, lastcost,
683 price_factor_id, price_factor, marge_price_factor)
684 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
685 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
687 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
688 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
689 $form->{"sellprice_$i"}, $fxsellprice,
690 $form->{"discount_$i"}, $allocated, 'f',
691 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
692 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
693 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
694 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
695 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
696 $form->{"lastcost_$i"},
697 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
698 conv_i($form->{"marge_price_factor_$i"}));
699 do_query($form, $dbh, $query, @values);
701 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
703 qq|INSERT INTO licenseinvoice (trans_id, license_id)
704 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
705 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
706 do_query($form, $dbh, $query, @values);
711 $form->{datepaid} = $form->{invdate};
713 # total payments, don't move we need it here
714 for my $i (1 .. $form->{paidaccounts}) {
715 if ($form->{type} eq "credit_note") {
716 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
718 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
720 $form->{paid} += $form->{"paid_$i"};
721 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
724 my ($tax, $diff) = (0, 0);
726 $netamount = $form->round_amount($netamount, 2);
728 # figure out rounding errors for total amount vs netamount + taxes
729 if ($form->{taxincluded}) {
731 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
732 $diff += $amount - $netamount * $form->{exchangerate};
733 $netamount = $amount;
735 foreach my $item (split(/ /, $form->{taxaccounts})) {
736 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
737 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
738 $tax += $form->{amount}{ $form->{id} }{$item};
739 $netamount -= $form->{amount}{ $form->{id} }{$item};
742 $invoicediff += $diff;
743 ######## this only applies to tax included
744 if ($lastincomeaccno) {
745 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
749 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
750 $diff = $amount - $netamount * $form->{exchangerate};
751 $netamount = $amount;
752 foreach my $item (split(/ /, $form->{taxaccounts})) {
753 $form->{amount}{ $form->{id} }{$item} =
754 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
757 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
760 $amount - $form->{amount}{ $form->{id} }{$item} *
761 $form->{exchangerate};
762 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
763 $tax += $form->{amount}{ $form->{id} }{$item};
767 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
769 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
772 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
774 # update exchangerate
775 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
776 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
777 $form->{exchangerate}, 0);
780 $project_id = conv_i($form->{"globalproject_id"});
782 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
783 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
784 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
786 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
788 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
790 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
791 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
792 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
793 do_query($form, $dbh, $query, @values);
794 $form->{amount_cogs}{$trans_id}{$accno} = 0;
798 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
799 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
801 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
803 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
804 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
805 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
806 do_query($form, $dbh, $query, @values);
811 foreach my $trans_id (keys %{ $form->{amount} }) {
812 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
813 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
815 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
817 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
819 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
820 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
821 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
822 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
823 do_query($form, $dbh, $query, @values);
824 $form->{amount}{$trans_id}{$accno} = 0;
828 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
829 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
831 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
833 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
834 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
835 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
836 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
837 do_query($form, $dbh, $query, @values);
842 # deduct payment differences from diff
843 for my $i (1 .. $form->{paidaccounts}) {
844 if ($form->{"paid_$i"} != 0) {
846 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
847 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
851 # record payments and offsetting AR
852 if (!$form->{storno}) {
853 for my $i (1 .. $form->{paidaccounts}) {
855 next if ($form->{"paid_$i"} == 0);
857 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
858 $form->{"datepaid_$i"} = $form->{invdate}
859 unless ($form->{"datepaid_$i"});
860 $form->{datepaid} = $form->{"datepaid_$i"};
864 if ($form->{currency} eq $defaultcurrency) {
865 $form->{"exchangerate_$i"} = 1;
867 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
868 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
872 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
874 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
876 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
877 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
878 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
879 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
880 do_query($form, $dbh, $query, @values);
884 $form->{"paid_$i"} *= -1;
887 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
888 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
889 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
890 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
891 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
892 do_query($form, $dbh, $query, @values);
894 # exchangerate difference
895 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
896 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
900 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
901 $form->{"exchangerate_$i"};
903 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
906 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
912 # update exchange rate
913 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
914 $form->update_exchangerate($dbh, $form->{currency},
915 $form->{"datepaid_$i"},
916 $form->{"exchangerate_$i"}, 0);
920 } else { # if (!$form->{storno})
921 $form->{marge_total} *= -1;
924 if ($payments_only) {
925 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
926 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
928 if (!$provided_dbh) {
933 $main::lxdebug->leave_sub();
937 # record exchange rate differences and gains/losses
938 foreach my $accno (keys %{ $form->{fx} }) {
939 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
941 ($form->{fx}{$accno}{$transdate} =
942 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
947 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
948 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
949 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
950 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
951 do_query($form, $dbh, $query, @values);
956 $amount = $netamount + $tax;
959 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
961 $query = qq|UPDATE ar set
962 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
963 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
964 amount = ?, netamount = ?, paid = ?, datepaid = ?,
965 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
966 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
967 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
968 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
969 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
970 cp_id = ?, marge_total = ?, marge_percent = ?,
971 globalproject_id = ?, delivery_customer_id = ?,
972 transaction_description = ?, delivery_vendor_id = ?,
975 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
976 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
977 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
978 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
979 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
980 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
981 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
982 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
983 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
984 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
985 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
986 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
987 conv_i($form->{"id"}));
988 do_query($form, $dbh, $query, @values);
990 if($form->{"formname"} eq "credit_note") {
991 for my $i (1 .. $form->{rowcount}) {
992 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
993 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
994 do_query($form, $dbh, $query, @values);
998 if ($form->{storno}) {
1001 paid = paid + amount,
1003 intnotes = ? || intnotes
1005 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1006 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1010 $form->{name} = $form->{customer};
1011 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1013 if (!$form->{shipto_id}) {
1014 $form->add_shipto($dbh, $form->{id}, "AR");
1017 # save printed, emailed and queued
1018 $form->save_status($dbh);
1020 Common::webdav_folder($form) if ($main::webdav);
1022 # Link this record to the records it was created from.
1023 RecordLinks->create_links('dbh' => $dbh,
1025 'from_table' => 'oe',
1026 'from_ids' => $form->{convert_from_oe_ids},
1028 'to_id' => $form->{id},
1030 delete $form->{convert_from_oe_ids};
1032 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1034 if (scalar @convert_from_do_ids) {
1035 DO->close_orders('dbh' => $dbh,
1036 'ids' => \@convert_from_do_ids);
1038 RecordLinks->create_links('dbh' => $dbh,
1040 'from_table' => 'delivery_orders',
1041 'from_ids' => \@convert_from_do_ids,
1043 'to_id' => $form->{id},
1046 delete $form->{convert_from_do_ids};
1048 ARAP->close_orders_if_billed('dbh' => $dbh,
1049 'arap_id' => $form->{id},
1053 if (!$provided_dbh) {
1058 $main::lxdebug->leave_sub();
1063 sub _delete_payments {
1064 $main::lxdebug->enter_sub();
1066 my ($self, $form, $dbh) = @_;
1068 my @delete_acc_trans_ids;
1070 # Delete old payment entries from acc_trans.
1072 qq|SELECT acc_trans_id
1074 WHERE (trans_id = ?) AND fx_transaction
1078 SELECT at.acc_trans_id
1080 LEFT JOIN chart c ON (at.chart_id = c.id)
1081 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1082 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1085 qq|SELECT at.acc_trans_id
1087 LEFT JOIN chart c ON (at.chart_id = c.id)
1088 WHERE (trans_id = ?)
1089 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1090 ORDER BY at.acc_trans_id
1092 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1094 if (@delete_acc_trans_ids) {
1095 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1096 do_query($form, $dbh, $query);
1099 $main::lxdebug->leave_sub();
1103 $main::lxdebug->enter_sub();
1105 my ($self, $myconfig, $form, $locale) = @_;
1107 # connect to database, turn off autocommit
1108 my $dbh = $form->dbconnect_noauto($myconfig);
1110 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1112 $old_form = save_form();
1114 # Delete all entries in acc_trans from prior payments.
1115 $self->_delete_payments($form, $dbh);
1117 # Save the new payments the user made before cleaning up $form.
1118 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1120 # Clean up $form so that old content won't tamper the results.
1121 %keep_vars = map { $_, 1 } qw(login password id);
1122 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1124 # Retrieve the invoice from the database.
1125 $self->retrieve_invoice($myconfig, $form);
1127 # Set up the content of $form in the way that IS::post_invoice() expects.
1128 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1130 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1131 $item = $form->{invoice_details}->[$row - 1];
1133 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1135 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1138 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1140 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1142 # Restore the payment options from the user input.
1143 map { $form->{$_} = $payments{$_} } keys %payments;
1145 # Get the AR accno (which is normally done by Form::create_links()).
1149 LEFT JOIN chart c ON (at.chart_id = c.id)
1150 WHERE (trans_id = ?)
1151 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1152 ORDER BY at.acc_trans_id
1155 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1157 # Post the new payments.
1158 $self->post_invoice($myconfig, $form, $dbh, 1);
1160 restore_form($old_form);
1162 my $rc = $dbh->commit();
1165 $main::lxdebug->leave_sub();
1170 sub process_assembly {
1171 $main::lxdebug->enter_sub();
1173 my ($dbh, $form, $id, $totalqty) = @_;
1176 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1177 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1179 JOIN parts p ON (a.parts_id = p.id)
1181 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1183 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1187 $ref->{inventory_accno_id} *= 1;
1188 $ref->{expense_accno_id} *= 1;
1190 # multiply by number of assemblies
1191 $ref->{qty} *= $totalqty;
1193 if ($ref->{assembly}) {
1194 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1197 if ($ref->{inventory_accno_id}) {
1198 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1202 # save detail record for individual assembly item in invoice table
1204 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1205 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1206 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1207 do_query($form, $dbh, $query, @values);
1213 $main::lxdebug->leave_sub();
1217 $main::lxdebug->enter_sub();
1219 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1223 $form->{taxzone_id} *=1;
1224 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1225 my $taxzone_id = $form->{"taxzone_id"} * 1;
1227 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1228 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1229 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1230 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1231 FROM invoice i, parts p
1232 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1233 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1234 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1235 WHERE (i.parts_id = p.id)
1236 AND (i.parts_id = ?)
1237 AND ((i.base_qty + i.allocated) < 0)
1239 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1244 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1245 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1249 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1251 # total expenses and inventory
1252 # sellprice is the cost of the item
1253 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1256 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1258 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1259 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1260 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1262 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1263 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1269 last if (($totalqty -= $qty) <= 0);
1274 $main::lxdebug->leave_sub();
1279 sub reverse_invoice {
1280 $main::lxdebug->enter_sub();
1282 my ($dbh, $form) = @_;
1284 # reverse inventory items
1286 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1288 JOIN parts p ON (i.parts_id = p.id)
1289 WHERE i.trans_id = ?|;
1290 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1292 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1294 if ($ref->{inventory_accno_id}) {
1295 # de-allocated purchases
1297 qq|SELECT i.id, i.trans_id, i.allocated
1299 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1300 ORDER BY i.trans_id DESC|;
1301 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1303 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1304 my $qty = $ref->{qty};
1305 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1306 $qty = $inhref->{allocated};
1310 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1312 last if (($ref->{qty} -= $qty) <= 0);
1321 my @values = (conv_i($form->{id}));
1322 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1323 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1325 if ($form->{lizenzen}) {
1327 qq|DELETE FROM licenseinvoice
1328 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1329 do_query($form, $dbh, $query, @values);
1332 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1334 $main::lxdebug->leave_sub();
1337 sub delete_invoice {
1338 $main::lxdebug->enter_sub();
1340 my ($self, $myconfig, $form, $spool) = @_;
1342 # connect to database
1343 my $dbh = $form->dbconnect_noauto($myconfig);
1345 &reverse_invoice($dbh, $form);
1347 my @values = (conv_i($form->{id}));
1350 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1352 # delete spool files
1353 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1355 # delete status entries
1356 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1358 my $rc = $dbh->commit;
1362 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1365 $main::lxdebug->leave_sub();
1370 sub retrieve_invoice {
1371 $main::lxdebug->enter_sub();
1373 my ($self, $myconfig, $form) = @_;
1375 # connect to database
1376 my $dbh = $form->dbconnect_noauto($myconfig);
1378 my ($sth, $ref, $query);
1380 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1384 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1385 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1386 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1387 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1388 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1389 d.curr AS currencies
1393 $ref = selectfirst_hashref_query($form, $dbh, $query);
1394 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1397 my $id = conv_i($form->{id});
1400 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1404 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1405 a.orddate, a.quodate, a.globalproject_id,
1406 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1407 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1408 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1409 a.employee_id, a.salesman_id, a.payment_id,
1410 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1411 a.transaction_description,
1412 a.marge_total, a.marge_percent,
1413 e.name AS employee, a.donumber
1415 LEFT JOIN employee e ON (e.id = a.employee_id)
1417 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1418 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1421 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1424 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1425 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1427 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1429 foreach my $vc (qw(customer vendor)) {
1430 next if !$form->{"delivery_${vc}_id"};
1431 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1434 # get printed, emailed
1435 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1436 $sth = prepare_execute_query($form, $dbh, $query, $id);
1438 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1439 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1440 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1441 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1444 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1446 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1447 : $form->{invdate} ? $dbh->quote($form->{invdate})
1451 my $taxzone_id = $form->{taxzone_id} *= 1;
1452 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1454 # retrieve individual items
1457 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1458 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1459 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1461 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1462 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1463 i.price_factor_id, i.price_factor, i.marge_price_factor,
1464 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1465 pr.projectnumber, pg.partsgroup, prg.pricegroup
1468 LEFT JOIN parts p ON (i.parts_id = p.id)
1469 LEFT JOIN project pr ON (i.project_id = pr.id)
1470 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1471 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1473 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1474 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1475 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1477 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1479 $sth = prepare_execute_query($form, $dbh, $query, $id);
1481 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1482 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1483 delete($ref->{"part_inventory_accno_id"});
1485 foreach my $type (qw(inventory income expense)) {
1486 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1487 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1488 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1492 # get tax rates and description
1493 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1495 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1496 LEFT JOIN chart c ON (c.id = t.chart_id)
1498 (SELECT tk.tax_id FROM taxkeys tk
1499 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1500 AND startdate <= date($transdate)
1501 ORDER BY startdate DESC LIMIT 1)
1503 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1504 $ref->{taxaccounts} = "";
1506 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1508 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1512 $ref->{taxaccounts} .= "$ptr->{accno} ";
1514 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1515 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1516 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1517 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1518 $form->{taxaccounts} .= "$ptr->{accno} ";
1523 if ($form->{lizenzen}) {
1524 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1525 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1526 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1529 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1531 chop $ref->{taxaccounts};
1532 push @{ $form->{invoice_details} }, $ref;
1537 Common::webdav_folder($form) if ($main::webdav);
1540 my $rc = $dbh->commit;
1543 $main::lxdebug->leave_sub();
1549 $main::lxdebug->enter_sub();
1551 my ($self, $myconfig, $form) = @_;
1553 # connect to database
1554 my $dbh = $form->dbconnect($myconfig);
1556 my $dateformat = $myconfig->{dateformat};
1557 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1559 my (@values, $duedate, $ref, $query);
1561 if ($form->{invdate}) {
1562 $duedate = "to_date(?, '$dateformat')";
1563 push @values, $form->{invdate};
1565 $duedate = "current_date";
1568 my $cid = conv_i($form->{customer_id});
1571 if ($form->{payment_id}) {
1572 $payment_id = "(pt.id = ?) OR";
1573 push @values, conv_i($form->{payment_id});
1579 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1580 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1581 c.street, c.zipcode, c.city, c.country,
1582 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1583 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1584 b.discount AS tradediscount, b.description AS business
1586 LEFT JOIN business b ON (b.id = c.business_id)
1587 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1590 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1592 delete $ref->{salesman_id} if !$ref->{salesman_id};
1594 map { $form->{$_} = $ref->{$_} } keys %$ref;
1597 qq|SELECT sum(amount - paid) AS dunning_amount
1599 WHERE (paid < amount)
1600 AND (customer_id = ?)
1601 AND (dunning_config_id IS NOT NULL)|;
1602 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1603 map { $form->{$_} = $ref->{$_} } keys %$ref;
1606 qq|SELECT dnn.dunning_description AS max_dunning_level
1607 FROM dunning_config dnn
1608 WHERE id IN (SELECT dunning_config_id
1610 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1611 ORDER BY dunning_level DESC LIMIT 1|;
1612 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1613 map { $form->{$_} = $ref->{$_} } keys %$ref;
1615 $form->{creditremaining} = $form->{creditlimit};
1616 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1617 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1618 $form->{creditremaining} -= $value;
1622 (SELECT e.buy FROM exchangerate e
1623 WHERE e.curr = o.curr
1624 AND e.transdate = o.transdate)
1626 WHERE o.customer_id = ?
1627 AND o.quotation = '0'
1628 AND o.closed = '0'|;
1629 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1631 while (my ($amount, $exch) = $sth->fetchrow_array) {
1632 $exch = 1 unless $exch;
1633 $form->{creditremaining} -= $amount * $exch;
1637 # get shipto if we did not converted an order or invoice
1638 if (!$form->{shipto}) {
1639 map { delete $form->{$_} }
1640 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1641 shiptostreet shiptozipcode shiptocity shiptocountry
1642 shiptocontact shiptophone shiptofax shiptoemail);
1644 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1645 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1647 map { $form->{$_} = $ref->{$_} } keys %$ref;
1650 # setup last accounts used for this customer
1651 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1653 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1655 JOIN acc_trans ac ON (ac.chart_id = c.id)
1656 JOIN ar a ON (a.id = ac.trans_id)
1657 WHERE a.customer_id = ?
1658 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1659 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1660 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1663 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1664 if ($ref->{category} eq 'I') {
1666 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1668 if ($form->{initial_transdate}) {
1670 qq|SELECT tk.tax_id, t.rate
1672 LEFT JOIN tax t ON tk.tax_id = t.id
1673 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1674 ORDER BY tk.startdate DESC
1676 my ($tax_id, $rate) =
1677 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1678 $form->{initial_transdate});
1679 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1682 if ($ref->{category} eq 'A') {
1683 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1687 $form->{rowcount} = $i if ($i && !$form->{type});
1692 $main::lxdebug->leave_sub();
1696 $main::lxdebug->enter_sub();
1698 my ($self, $myconfig, $form) = @_;
1700 # connect to database
1701 my $dbh = $form->dbconnect($myconfig);
1703 my $i = $form->{rowcount};
1705 my $where = qq|NOT p.obsolete = '1'|;
1708 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1709 my ($table, $field) = split m/\./, $column;
1710 next if !$form->{"${field}_${i}"};
1711 $where .= qq| AND lower(${column}) ILIKE ?|;
1712 push @values, '%' . $form->{"${field}_${i}"} . '%';
1715 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1716 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1717 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1718 push @values, $form->{"partnumber_$i"};
1721 if ($form->{"description_$i"}) {
1722 $where .= qq| ORDER BY p.description|;
1724 $where .= qq| ORDER BY p.partnumber|;
1728 if ($form->{type} eq "invoice") {
1730 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1731 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1735 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1739 my $taxzone_id = $form->{taxzone_id} * 1;
1740 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1744 p.id, p.partnumber, p.description, p.sellprice,
1745 p.listprice, p.inventory_accno_id, p.lastcost,
1747 c1.accno AS inventory_accno,
1748 c1.new_chart_id AS inventory_new_chart,
1749 date($transdate) - c1.valid_from AS inventory_valid,
1751 c2.accno AS income_accno,
1752 c2.new_chart_id AS income_new_chart,
1753 date($transdate) - c2.valid_from AS income_valid,
1755 c3.accno AS expense_accno,
1756 c3.new_chart_id AS expense_new_chart,
1757 date($transdate) - c3.valid_from AS expense_valid,
1759 p.unit, p.assembly, p.bin, p.onhand,
1760 p.notes AS partnotes, p.notes AS longdescription,
1761 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1764 pfac.factor AS price_factor,
1769 LEFT JOIN chart c1 ON
1770 ((SELECT inventory_accno_id
1771 FROM buchungsgruppen
1772 WHERE id = p.buchungsgruppen_id) = c1.id)
1773 LEFT JOIN chart c2 ON
1774 ((SELECT income_accno_id_${taxzone_id}
1775 FROM buchungsgruppen
1776 WHERE id = p.buchungsgruppen_id) = c2.id)
1777 LEFT JOIN chart c3 ON
1778 ((SELECT expense_accno_id_${taxzone_id}
1779 FROM buchungsgruppen
1780 WHERE id = p.buchungsgruppen_id) = c3.id)
1781 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1782 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1784 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1786 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1788 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1789 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1790 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1791 if (!$ref->{inventory_accno_id}) {
1792 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1794 delete($ref->{inventory_accno_id});
1796 foreach my $type (qw(inventory income expense)) {
1797 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1799 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1802 ($ref->{"${type}_accno"},
1803 $ref->{"${type}_new_chart"},
1804 $ref->{"${type}_valid"})
1805 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1809 if ($form->{payment_id} eq "") {
1810 $form->{payment_id} = $form->{part_payment_id};
1813 # get tax rates and description
1814 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1816 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1818 LEFT JOIN chart c ON (c.id = t.chart_id)
1822 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1824 ORDER BY startdate DESC
1827 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1828 my $stw = $dbh->prepare($query);
1829 $stw->execute(@values) || $form->dberror($query);
1831 $ref->{taxaccounts} = "";
1833 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1835 # if ($customertax{$ref->{accno}})
1836 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1840 $ref->{taxaccounts} .= "$ptr->{accno} ";
1842 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1843 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1844 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1845 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1846 $form->{taxaccounts} .= "$ptr->{accno} ";
1852 chop $ref->{taxaccounts};
1853 if ($form->{language_id}) {
1855 qq|SELECT tr.translation, tr.longdescription
1857 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1858 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1859 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1860 if ($translation ne "") {
1861 $ref->{description} = $translation;
1862 $ref->{longdescription} = $longdescription;
1866 qq|SELECT tr.translation, tr.longdescription
1868 WHERE tr.language_id IN
1871 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1874 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1875 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1876 if ($translation ne "") {
1877 $ref->{description} = $translation;
1878 $ref->{longdescription} = $longdescription;
1883 $ref->{onhand} *= 1;
1885 push @{ $form->{item_list} }, $ref;
1887 if ($form->{lizenzen}) {
1888 if ($ref->{inventory_accno} > 0) {
1892 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1893 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1894 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1895 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1904 $main::lxdebug->leave_sub();
1907 ##########################
1908 # get pricegroups from database
1909 # build up selected pricegroup
1910 # if an exchange rate - change price
1913 sub get_pricegroups_for_parts {
1915 $main::lxdebug->enter_sub();
1917 my ($self, $myconfig, $form) = @_;
1919 my $dbh = $form->dbconnect($myconfig);
1921 $form->{"PRICES"} = {};
1925 my $all_units = AM->retrieve_units($myconfig, $form);
1926 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1927 $form->{"PRICES"}{$i} = [];
1929 $id = $form->{"id_$i"};
1931 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1933 $id = $form->{"new_id_$i"};
1936 my ($price, $selectedpricegroup_id) = split(/--/,
1937 $form->{"sellprice_pg_$i"});
1939 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1940 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1941 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1943 my $price_new = $form->{"price_new_$i"};
1944 my $price_old = $form->{"price_old_$i"};
1946 if (!$form->{"unit_old_$i"}) {
1947 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1948 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1949 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1950 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1953 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1954 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1955 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1957 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1958 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1959 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1960 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1961 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1962 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1963 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1968 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1969 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1970 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1971 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1972 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1976 if (!$form->{"basefactor_$i"}) {
1977 $form->{"basefactor_$i"} = 1;
1983 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1984 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1994 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1996 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1997 'selected' AS selected
2000 ORDER BY pricegroup|;
2001 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2002 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2004 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2006 $pkr->{selected} = '';
2008 # if there is an exchange rate change price
2009 if (($form->{exchangerate} * 1) != 0) {
2011 $pkr->{price} /= $form->{exchangerate};
2014 $pkr->{price} *= $form->{"basefactor_$i"};
2016 $pkr->{price} *= $basefactor;
2018 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2020 if ($selectedpricegroup_id eq undef) {
2021 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2023 $pkr->{selected} = ' selected';
2025 # no customer pricesgroup set
2026 if ($pkr->{price} == $pkr->{default_sellprice}) {
2028 $pkr->{price} = $form->{"sellprice_$i"};
2032 # this sub should not set anything and only return. --sschoeling, 20090506
2033 # $form->{"sellprice_$i"} = $pkr->{price};
2036 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2037 $pkr->{price} = $form->{"sellprice_$i"};
2038 $pkr->{selected} = ' selected';
2042 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2043 if ($selectedpricegroup_id ne $pricegroup_old) {
2044 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2045 $pkr->{selected} = ' selected';
2047 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2048 if ($pkr->{pricegroup_id} == 0) {
2049 $pkr->{price} = $form->{"sellprice_$i"};
2050 $pkr->{selected} = ' selected';
2052 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2053 $pkr->{selected} = ' selected';
2054 if ( ($pkr->{pricegroup_id} == 0)
2055 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2056 # $pkr->{price} = $form->{"sellprice_$i"};
2058 $pkr->{price} = $form->{"sellprice_$i"};
2062 push @{ $form->{PRICES}{$i} }, $pkr;
2065 $form->{"basefactor_$i"} *= $basefactor;
2074 $main::lxdebug->leave_sub();
2078 $main::lxdebug->enter_sub();
2080 my ($self, $myconfig, $form, $table) = @_;
2082 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2084 # make sure there's no funny stuff in $table
2085 # ToDO: die when this happens and throw an error
2086 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2088 my $dbh = $form->dbconnect($myconfig);
2090 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2091 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2095 $main::lxdebug->leave_sub();
2101 $main::lxdebug->enter_sub();
2103 my ($self, $myconfig, $form, $table, $id) = @_;
2105 $main::lxdebug->leave_sub() and return 0 unless ($id);
2107 # make sure there's no funny stuff in $table
2108 # ToDO: die when this happens and throw an error
2109 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2111 my $dbh = $form->dbconnect($myconfig);
2113 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2114 my ($result) = selectrow_query($form, $dbh, $query, $id);
2118 $main::lxdebug->leave_sub();