1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
45 use SL::GenericTranslations;
54 $main::lxdebug->enter_sub();
56 my ($self, $myconfig, $form, $locale) = @_;
58 $form->{duedate} ||= $form->{invdate};
61 my $dbh = $form->dbconnect($myconfig);
64 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
65 ($form->{terms}) = selectrow_query($form, $dbh, $query);
67 my (@project_ids, %projectnumbers, %projectdescriptions);
68 $form->{TEMPLATE_ARRAYS} = {};
70 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
72 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
75 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
76 $price_factors{$pfac->{id}} = $pfac;
78 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
81 # sort items by partsgroup
82 for my $i (1 .. $form->{rowcount}) {
84 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
85 # $partsgroup = $form->{"partsgroup_$i"};
87 # push @partsgroup, [$i, $partsgroup];
88 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
92 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
93 join(", ", map({ "?" } @project_ids)) . ")";
94 $sth = $dbh->prepare($query);
95 $sth->execute(@project_ids) ||
96 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
97 while (my $ref = $sth->fetchrow_hashref()) {
98 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
99 $projectdescriptions{$ref->{id}} = $ref->{description};
104 $form->{"globalprojectnumber"} =
105 $projectnumbers{$form->{"globalproject_id"}};
106 $form->{"globalprojectdescription"} =
107 $projectdescriptions{$form->{"globalproject_id"}};
114 my %oid = ('Pg' => 'oid',
115 'Oracle' => 'rowid');
117 # sort items by partsgroup
118 for $i (1 .. $form->{rowcount}) {
120 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
121 $partsgroup = $form->{"partsgroup_$i"};
123 push @partsgroup, [$i, $partsgroup];
136 my $nodiscount_subtotal = 0;
137 my $discount_subtotal = 0;
139 my $subtotal_header = 0;
142 $form->{discount} = [];
144 IC->prepare_parts_for_printing();
146 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
149 qw(runningnumber number description longdescription qty ship unit bin
150 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
151 partnotes serialnumber reqdate sellprice listprice netprice
152 discount p_discount discount_sub nodiscount_sub
153 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
154 price_factor price_factor_name partsgroup);
156 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
158 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
160 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
162 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
164 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
167 if ($item->[1] ne $sameitem) {
168 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
169 $sameitem = $item->[1];
171 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
174 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
176 if ($form->{"id_$i"} != 0) {
178 # add number, description and qty to $form->{number},
179 if ($form->{"subtotal_$i"} && !$subtotal_header) {
180 $subtotal_header = $i;
181 $position = int($position);
184 } elsif ($subtotal_header) {
186 $position = int($position);
187 $position = $position.".".$subposition;
189 $position = int($position);
193 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
195 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
196 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
203 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
211 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
212 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
215 if ($form->{lizenzen}) {
216 if ($form->{"licensenumber_$i"}) {
217 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
218 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
219 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
220 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
223 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
224 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
229 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
231 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
232 my ($dec) = ($sellprice =~ /\.(\d+)/);
233 my $decimalplaces = max 2, length($dec);
235 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
236 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
237 my $linetotal = $form->round_amount($linetotal_exact, 2);
238 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
240 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
241 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
243 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
245 $linetotal = ($linetotal != 0) ? $linetotal : '';
247 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
248 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
250 $form->{total} += $linetotal;
251 $form->{nodiscount_total} += $nodiscount_linetotal;
252 $form->{discount_total} += $discount;
254 if ($subtotal_header) {
255 $discount_subtotal += $linetotal;
256 $nodiscount_subtotal += $nodiscount_linetotal;
259 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
269 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
272 if (!$form->{"discount_$i"}) {
273 $nodiscount += $linetotal;
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
279 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
282 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
286 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
288 if ($form->{taxincluded}) {
291 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
292 $taxbase = $linetotal - $taxamount;
294 $taxamount = $linetotal * $taxrate;
295 $taxbase = $linetotal;
298 if ($form->round_amount($taxrate, 7) == 0) {
299 if ($form->{taxincluded}) {
300 foreach my $accno (@taxaccounts) {
301 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
303 $taxaccounts{$accno} += $taxamount;
304 $taxdiff += $taxamount;
306 $taxbase{$accno} += $taxbase;
308 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
310 foreach my $accno (@taxaccounts) {
311 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
312 $taxbase{$accno} += $taxbase;
316 foreach my $accno (@taxaccounts) {
317 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
318 $taxbase{$accno} += $taxbase;
321 my $tax_rate = $taxrate * 100;
322 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
323 if ($form->{"assembly_$i"}) {
326 # get parts and push them onto the stack
328 if ($form->{groupitems}) {
330 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
332 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
336 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
338 JOIN parts p ON (a.parts_id = p.id)
339 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
340 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
341 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
343 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
344 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
345 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
346 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
347 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
350 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
352 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
353 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
355 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
356 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
362 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
366 foreach my $item (sort keys %taxaccounts) {
367 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
369 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
370 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
371 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
376 for my $i (1 .. $form->{paidaccounts}) {
377 if ($form->{"paid_$i"}) {
378 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
382 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
383 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
384 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
386 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
389 if($form->{taxincluded}) {
390 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
393 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
396 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
397 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
398 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
399 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
401 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
402 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
404 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
405 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
407 $form->set_payment_options($myconfig, $form->{invdate});
409 $form->{username} = $myconfig->{name};
413 $main::lxdebug->leave_sub();
416 sub project_description {
417 $main::lxdebug->enter_sub();
419 my ($self, $dbh, $id) = @_;
420 my $form = \%main::form;
422 my $query = qq|SELECT description FROM project WHERE id = ?|;
423 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
425 $main::lxdebug->leave_sub();
430 sub customer_details {
431 $main::lxdebug->enter_sub();
433 my ($self, $myconfig, $form, @wanted_vars) = @_;
435 # connect to database
436 my $dbh = $form->dbconnect($myconfig);
438 my $language_id = $form->{language_id};
440 # get contact id, set it if nessessary
443 my @values = (conv_i($form->{customer_id}));
446 if ($form->{cp_id}) {
447 $where = qq| AND (cp.cp_id = ?) |;
448 push(@values, conv_i($form->{cp_id}));
451 # get rest for the customer
453 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
454 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
456 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
457 WHERE (ct.id = ?) $where
460 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
462 # remove id and taxincluded before copy back
463 delete @$ref{qw(id taxincluded)};
465 @wanted_vars = grep({ $_ } @wanted_vars);
466 if (scalar(@wanted_vars) > 0) {
468 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
469 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
472 map { $form->{$_} = $ref->{$_} } keys %$ref;
474 if ($form->{delivery_customer_id}) {
476 qq|SELECT *, notes as customernotes
480 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
482 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
485 if ($form->{delivery_vendor_id}) {
487 qq|SELECT *, notes as customernotes
491 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
493 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
496 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
498 'trans_id' => $form->{customer_id});
499 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
501 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
502 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
503 'language_id' => $language_id,
504 'allow_fallback' => 1);
509 $main::lxdebug->leave_sub();
513 $main::lxdebug->enter_sub();
515 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
517 # connect to database, turn off autocommit
518 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
520 my ($query, $sth, $null, $project_id, @values);
521 my $exchangerate = 0;
523 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
526 if (!$form->{employee_id}) {
527 $form->get_employee($dbh);
530 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
532 ($null, $form->{department_id}) = split(/--/, $form->{department});
534 my $all_units = AM->retrieve_units($myconfig, $form);
536 if (!$payments_only) {
538 &reverse_invoice($dbh, $form);
541 $query = qq|SELECT nextval('glid')|;
542 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
544 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
545 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
547 if (!$form->{invnumber}) {
549 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
550 "cnnumber" : "invnumber", $dbh);
555 my ($netamount, $invoicediff) = (0, 0);
556 my ($amount, $linetotal, $lastincomeaccno);
558 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
559 my $defaultcurrency = (split m/:/, $currencies)[0];
561 if ($form->{currency} eq $defaultcurrency) {
562 $form->{exchangerate} = 1;
564 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
567 $form->{exchangerate} =
570 : $form->parse_amount($myconfig, $form->{exchangerate});
572 $form->{expense_inventory} = "";
576 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
577 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
580 $form->{amount} = {};
581 $form->{amount_cogs} = {};
583 foreach my $i (1 .. $form->{rowcount}) {
584 if ($form->{type} eq "credit_note") {
585 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
586 $form->{shipped} = 1;
588 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
593 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
594 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
595 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
597 if ($form->{storno}) {
598 $form->{"qty_$i"} *= -1;
601 if ($form->{"id_$i"}) {
604 if (defined($baseunits{$form->{"id_$i"}})) {
605 $item_unit = $baseunits{$form->{"id_$i"}};
608 $query = qq|SELECT unit FROM parts WHERE id = ?|;
609 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
610 $baseunits{$form->{"id_$i"}} = $item_unit;
613 if (defined($all_units->{$item_unit}->{factor})
614 && ($all_units->{$item_unit}->{factor} ne '')
615 && ($all_units->{$item_unit}->{factor} != 0)) {
616 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
620 $baseqty = $form->{"qty_$i"} * $basefactor;
622 my ($allocated, $taxrate) = (0, 0);
626 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
628 # keep entered selling price
630 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
632 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
634 my $decimalplaces = ($dec > 2) ? $dec : 2;
636 # undo discount formatting
637 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
640 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
642 # round linetotal to 2 decimal places
643 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
644 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
646 if ($form->{taxincluded}) {
647 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
648 $form->{"sellprice_$i"} =
649 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
651 $taxamount = $linetotal * $taxrate;
654 $netamount += $linetotal;
656 if ($taxamount != 0) {
658 $form->{amount}{ $form->{id} }{$_} +=
659 $taxamount * $form->{"${_}_rate"} / $taxrate
660 } split(/ /, $form->{"taxaccounts_$i"});
663 # add amount to income, $form->{amount}{trans_id}{accno}
664 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
666 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
667 $linetotal = $form->round_amount($linetotal, 2);
669 # this is the difference from the inventory
670 $invoicediff += ($amount - $linetotal);
672 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
675 $lastincomeaccno = $form->{"income_accno_$i"};
677 # adjust and round sellprice
678 $form->{"sellprice_$i"} =
679 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
682 next if $payments_only;
684 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
686 if ($form->{"assembly_$i"}) {
687 # record assembly item as allocated
688 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
691 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
695 # get pricegroup_id and save it
696 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
699 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
701 # save detail record in invoice table
703 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
704 sellprice, fxsellprice, discount, allocated, assemblyitem,
705 unit, deliverydate, project_id, serialnumber, pricegroup_id,
706 ordnumber, transdate, cusordnumber, base_qty, subtotal,
707 marge_percent, marge_total, lastcost,
708 price_factor_id, price_factor, marge_price_factor)
709 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
710 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
712 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
713 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
714 $form->{"sellprice_$i"}, $fxsellprice,
715 $form->{"discount_$i"}, $allocated, 'f',
716 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
717 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
718 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
719 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
720 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
721 $form->{"lastcost_$i"},
722 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
723 conv_i($form->{"marge_price_factor_$i"}));
724 do_query($form, $dbh, $query, @values);
726 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
728 qq|INSERT INTO licenseinvoice (trans_id, license_id)
729 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
730 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
731 do_query($form, $dbh, $query, @values);
734 CVar->save_custom_variables(module => 'IC',
735 sub_module => 'invoice',
736 trans_id => $invoice_id,
737 configs => $ic_cvar_configs,
739 name_prefix => 'ic_',
740 name_postfix => "_$i",
745 # total payments, don't move we need it here
746 for my $i (1 .. $form->{paidaccounts}) {
747 if ($form->{type} eq "credit_note") {
748 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
750 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
752 $form->{paid} += $form->{"paid_$i"};
753 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
756 my ($tax, $diff) = (0, 0);
758 $netamount = $form->round_amount($netamount, 2);
760 # figure out rounding errors for total amount vs netamount + taxes
761 if ($form->{taxincluded}) {
763 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
764 $diff += $amount - $netamount * $form->{exchangerate};
765 $netamount = $amount;
767 foreach my $item (split(/ /, $form->{taxaccounts})) {
768 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
769 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
770 $tax += $form->{amount}{ $form->{id} }{$item};
771 $netamount -= $form->{amount}{ $form->{id} }{$item};
774 $invoicediff += $diff;
775 ######## this only applies to tax included
776 if ($lastincomeaccno) {
777 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
781 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
782 $diff = $amount - $netamount * $form->{exchangerate};
783 $netamount = $amount;
784 foreach my $item (split(/ /, $form->{taxaccounts})) {
785 $form->{amount}{ $form->{id} }{$item} =
786 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
789 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
792 $amount - $form->{amount}{ $form->{id} }{$item} *
793 $form->{exchangerate};
794 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
795 $tax += $form->{amount}{ $form->{id} }{$item};
799 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
801 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
804 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
806 # update exchangerate
807 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
808 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
809 $form->{exchangerate}, 0);
812 $project_id = conv_i($form->{"globalproject_id"});
814 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
815 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
816 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
818 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
820 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
822 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
823 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
824 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
825 do_query($form, $dbh, $query, @values);
826 $form->{amount_cogs}{$trans_id}{$accno} = 0;
830 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
831 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
833 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
838 do_query($form, $dbh, $query, @values);
843 foreach my $trans_id (keys %{ $form->{amount} }) {
844 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
845 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
847 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
849 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
851 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
852 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
853 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
854 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
855 do_query($form, $dbh, $query, @values);
856 $form->{amount}{$trans_id}{$accno} = 0;
860 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
861 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
863 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
865 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
866 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
867 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
868 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
869 do_query($form, $dbh, $query, @values);
874 # deduct payment differences from diff
875 for my $i (1 .. $form->{paidaccounts}) {
876 if ($form->{"paid_$i"} != 0) {
878 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
879 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
883 # record payments and offsetting AR
884 if (!$form->{storno}) {
885 for my $i (1 .. $form->{paidaccounts}) {
887 next if ($form->{"paid_$i"} == 0);
889 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
890 $form->{"datepaid_$i"} = $form->{invdate}
891 unless ($form->{"datepaid_$i"});
892 $form->{datepaid} = $form->{"datepaid_$i"};
896 if ($form->{currency} eq $defaultcurrency) {
897 $form->{"exchangerate_$i"} = 1;
899 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
900 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
904 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
906 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
908 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
909 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
910 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
911 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
912 do_query($form, $dbh, $query, @values);
916 $form->{"paid_$i"} *= -1;
919 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
920 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
921 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
922 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
923 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
924 do_query($form, $dbh, $query, @values);
926 # exchangerate difference
927 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
928 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
932 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
933 $form->{"exchangerate_$i"};
935 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
938 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
944 # update exchange rate
945 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
946 $form->update_exchangerate($dbh, $form->{currency},
947 $form->{"datepaid_$i"},
948 $form->{"exchangerate_$i"}, 0);
952 } else { # if (!$form->{storno})
953 $form->{marge_total} *= -1;
956 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
958 if ($payments_only) {
959 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
960 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
962 if (!$provided_dbh) {
967 $main::lxdebug->leave_sub();
971 # record exchange rate differences and gains/losses
972 foreach my $accno (keys %{ $form->{fx} }) {
973 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
975 ($form->{fx}{$accno}{$transdate} =
976 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
981 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
982 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
983 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
984 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
985 do_query($form, $dbh, $query, @values);
990 $amount = $netamount + $tax;
993 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
995 $query = qq|UPDATE ar set
996 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
997 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
998 amount = ?, netamount = ?, paid = ?,
999 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1000 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1001 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1002 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1003 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1004 cp_id = ?, marge_total = ?, marge_percent = ?,
1005 globalproject_id = ?, delivery_customer_id = ?,
1006 transaction_description = ?, delivery_vendor_id = ?,
1009 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1010 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1011 $amount, $netamount, $form->{"paid"},
1012 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1013 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1014 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1015 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1016 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1017 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1018 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1019 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1020 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
1021 conv_i($form->{"id"}));
1022 do_query($form, $dbh, $query, @values);
1025 if ($form->{storno}) {
1028 paid = paid + amount,
1030 intnotes = ? || intnotes
1032 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1033 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1037 $form->{name} = $form->{customer};
1038 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1040 if (!$form->{shipto_id}) {
1041 $form->add_shipto($dbh, $form->{id}, "AR");
1044 # save printed, emailed and queued
1045 $form->save_status($dbh);
1047 Common::webdav_folder($form) if ($main::webdav);
1049 # Link this record to the records it was created from.
1050 RecordLinks->create_links('dbh' => $dbh,
1052 'from_table' => 'oe',
1053 'from_ids' => $form->{convert_from_oe_ids},
1055 'to_id' => $form->{id},
1057 delete $form->{convert_from_oe_ids};
1059 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1061 if (scalar @convert_from_do_ids) {
1062 DO->close_orders('dbh' => $dbh,
1063 'ids' => \@convert_from_do_ids);
1065 RecordLinks->create_links('dbh' => $dbh,
1067 'from_table' => 'delivery_orders',
1068 'from_ids' => \@convert_from_do_ids,
1070 'to_id' => $form->{id},
1073 delete $form->{convert_from_do_ids};
1075 ARAP->close_orders_if_billed('dbh' => $dbh,
1076 'arap_id' => $form->{id},
1080 if (!$provided_dbh) {
1085 $main::lxdebug->leave_sub();
1090 sub _delete_payments {
1091 $main::lxdebug->enter_sub();
1093 my ($self, $form, $dbh) = @_;
1095 my @delete_acc_trans_ids;
1097 # Delete old payment entries from acc_trans.
1099 qq|SELECT acc_trans_id
1101 WHERE (trans_id = ?) AND fx_transaction
1105 SELECT at.acc_trans_id
1107 LEFT JOIN chart c ON (at.chart_id = c.id)
1108 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1109 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1112 qq|SELECT at.acc_trans_id
1114 LEFT JOIN chart c ON (at.chart_id = c.id)
1115 WHERE (trans_id = ?)
1116 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1117 ORDER BY at.acc_trans_id
1119 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1121 if (@delete_acc_trans_ids) {
1122 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1123 do_query($form, $dbh, $query);
1126 $main::lxdebug->leave_sub();
1130 $main::lxdebug->enter_sub();
1132 my ($self, $myconfig, $form, $locale) = @_;
1134 # connect to database, turn off autocommit
1135 my $dbh = $form->dbconnect_noauto($myconfig);
1137 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1139 $old_form = save_form();
1141 # Delete all entries in acc_trans from prior payments.
1142 $self->_delete_payments($form, $dbh);
1144 # Save the new payments the user made before cleaning up $form.
1145 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1147 # Clean up $form so that old content won't tamper the results.
1148 %keep_vars = map { $_, 1 } qw(login password id);
1149 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1151 # Retrieve the invoice from the database.
1152 $self->retrieve_invoice($myconfig, $form);
1154 # Set up the content of $form in the way that IS::post_invoice() expects.
1155 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1157 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1158 $item = $form->{invoice_details}->[$row - 1];
1160 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1162 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1165 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1167 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1169 # Restore the payment options from the user input.
1170 map { $form->{$_} = $payments{$_} } keys %payments;
1172 # Get the AR accno (which is normally done by Form::create_links()).
1176 LEFT JOIN chart c ON (at.chart_id = c.id)
1177 WHERE (trans_id = ?)
1178 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1179 ORDER BY at.acc_trans_id
1182 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1184 # Post the new payments.
1185 $self->post_invoice($myconfig, $form, $dbh, 1);
1187 restore_form($old_form);
1189 my $rc = $dbh->commit();
1192 $main::lxdebug->leave_sub();
1197 sub process_assembly {
1198 $main::lxdebug->enter_sub();
1200 my ($dbh, $form, $id, $totalqty) = @_;
1203 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1204 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1206 JOIN parts p ON (a.parts_id = p.id)
1208 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1210 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1214 $ref->{inventory_accno_id} *= 1;
1215 $ref->{expense_accno_id} *= 1;
1217 # multiply by number of assemblies
1218 $ref->{qty} *= $totalqty;
1220 if ($ref->{assembly}) {
1221 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1224 if ($ref->{inventory_accno_id}) {
1225 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1229 # save detail record for individual assembly item in invoice table
1231 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1232 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1233 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1234 do_query($form, $dbh, $query, @values);
1240 $main::lxdebug->leave_sub();
1244 $main::lxdebug->enter_sub();
1246 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1250 $form->{taxzone_id} *=1;
1251 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1252 my $taxzone_id = $form->{"taxzone_id"} * 1;
1254 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1255 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1256 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1257 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1258 FROM invoice i, parts p
1259 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1260 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1261 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1262 WHERE (i.parts_id = p.id)
1263 AND (i.parts_id = ?)
1264 AND ((i.base_qty + i.allocated) < 0)
1266 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1271 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1272 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1276 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1278 # total expenses and inventory
1279 # sellprice is the cost of the item
1280 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1283 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1285 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1286 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1287 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1289 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1290 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1296 last if (($totalqty -= $qty) <= 0);
1301 $main::lxdebug->leave_sub();
1306 sub reverse_invoice {
1307 $main::lxdebug->enter_sub();
1309 my ($dbh, $form) = @_;
1311 # reverse inventory items
1313 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1315 JOIN parts p ON (i.parts_id = p.id)
1316 WHERE i.trans_id = ?|;
1317 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1319 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1321 if ($ref->{inventory_accno_id}) {
1322 # de-allocated purchases
1324 qq|SELECT i.id, i.trans_id, i.allocated
1326 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1327 ORDER BY i.trans_id DESC|;
1328 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1330 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1331 my $qty = $ref->{qty};
1332 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1333 $qty = $inhref->{allocated};
1337 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1339 last if (($ref->{qty} -= $qty) <= 0);
1348 my @values = (conv_i($form->{id}));
1349 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1350 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1352 if ($form->{lizenzen}) {
1354 qq|DELETE FROM licenseinvoice
1355 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1356 do_query($form, $dbh, $query, @values);
1359 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1361 $main::lxdebug->leave_sub();
1364 sub delete_invoice {
1365 $main::lxdebug->enter_sub();
1367 my ($self, $myconfig, $form, $spool) = @_;
1369 # connect to database
1370 my $dbh = $form->dbconnect_noauto($myconfig);
1372 &reverse_invoice($dbh, $form);
1374 my @values = (conv_i($form->{id}));
1377 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1379 # delete spool files
1380 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1382 # delete status entries
1383 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1385 my $rc = $dbh->commit;
1389 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1392 $main::lxdebug->leave_sub();
1397 sub retrieve_invoice {
1398 $main::lxdebug->enter_sub();
1400 my ($self, $myconfig, $form) = @_;
1402 # connect to database
1403 my $dbh = $form->get_standard_dbh;
1405 my ($sth, $ref, $query);
1407 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1411 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1412 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1413 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1414 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1415 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1416 d.curr AS currencies
1420 $ref = selectfirst_hashref_query($form, $dbh, $query);
1421 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1424 my $id = conv_i($form->{id});
1427 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1431 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1432 a.orddate, a.quodate, a.globalproject_id,
1433 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1434 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1435 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1436 a.employee_id, a.salesman_id, a.payment_id,
1437 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1438 a.transaction_description,
1439 a.marge_total, a.marge_percent,
1440 e.name AS employee, a.donumber
1442 LEFT JOIN employee e ON (e.id = a.employee_id)
1444 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1445 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1448 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1451 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1452 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1454 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1456 foreach my $vc (qw(customer vendor)) {
1457 next if !$form->{"delivery_${vc}_id"};
1458 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1461 # get printed, emailed
1462 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1463 $sth = prepare_execute_query($form, $dbh, $query, $id);
1465 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1466 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1467 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1468 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1471 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1473 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1474 : $form->{invdate} ? $dbh->quote($form->{invdate})
1478 my $taxzone_id = $form->{taxzone_id} *= 1;
1479 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1481 # retrieve individual items
1484 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1485 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1486 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1489 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1490 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1491 i.price_factor_id, i.price_factor, i.marge_price_factor,
1492 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1493 pr.projectnumber, pg.partsgroup, prg.pricegroup
1496 LEFT JOIN parts p ON (i.parts_id = p.id)
1497 LEFT JOIN project pr ON (i.project_id = pr.id)
1498 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1499 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1501 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1502 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1503 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1505 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1507 $sth = prepare_execute_query($form, $dbh, $query, $id);
1509 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1510 # Retrieve custom variables.
1511 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1513 sub_module => 'invoice',
1514 trans_id => $ref->{invoice_id},
1516 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1517 delete $ref->{invoice_id};
1519 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1520 delete($ref->{"part_inventory_accno_id"});
1522 foreach my $type (qw(inventory income expense)) {
1523 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1524 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1525 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1529 # get tax rates and description
1530 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1532 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1533 LEFT JOIN chart c ON (c.id = t.chart_id)
1535 (SELECT tk.tax_id FROM taxkeys tk
1536 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1537 AND startdate <= date($transdate)
1538 ORDER BY startdate DESC LIMIT 1)
1540 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1541 $ref->{taxaccounts} = "";
1543 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1545 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1549 $ref->{taxaccounts} .= "$ptr->{accno} ";
1551 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1552 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1553 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1554 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1555 $form->{taxaccounts} .= "$ptr->{accno} ";
1560 if ($form->{lizenzen}) {
1561 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1562 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1563 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1566 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1568 chop $ref->{taxaccounts};
1569 push @{ $form->{invoice_details} }, $ref;
1574 Common::webdav_folder($form) if ($main::webdav);
1577 my $rc = $dbh->commit;
1579 $main::lxdebug->leave_sub();
1585 $main::lxdebug->enter_sub();
1587 my ($self, $myconfig, $form) = @_;
1589 # connect to database
1590 my $dbh = $form->get_standard_dbh;
1592 my $dateformat = $myconfig->{dateformat};
1593 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1595 my (@values, $duedate, $ref, $query);
1597 if ($form->{invdate}) {
1598 $duedate = "to_date(?, '$dateformat')";
1599 push @values, $form->{invdate};
1601 $duedate = "current_date";
1604 my $cid = conv_i($form->{customer_id});
1607 if ($form->{payment_id}) {
1608 $payment_id = "(pt.id = ?) OR";
1609 push @values, conv_i($form->{payment_id});
1615 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1616 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1617 c.street, c.zipcode, c.city, c.country,
1618 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1619 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1620 b.discount AS tradediscount, b.description AS business
1622 LEFT JOIN business b ON (b.id = c.business_id)
1623 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1626 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1628 delete $ref->{salesman_id} if !$ref->{salesman_id};
1630 map { $form->{$_} = $ref->{$_} } keys %$ref;
1633 qq|SELECT sum(amount - paid) AS dunning_amount
1635 WHERE (paid < amount)
1636 AND (customer_id = ?)
1637 AND (dunning_config_id IS NOT NULL)|;
1638 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1639 map { $form->{$_} = $ref->{$_} } keys %$ref;
1642 qq|SELECT dnn.dunning_description AS max_dunning_level
1643 FROM dunning_config dnn
1644 WHERE id IN (SELECT dunning_config_id
1646 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1647 ORDER BY dunning_level DESC LIMIT 1|;
1648 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1649 map { $form->{$_} = $ref->{$_} } keys %$ref;
1651 $form->{creditremaining} = $form->{creditlimit};
1652 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1653 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1654 $form->{creditremaining} -= $value;
1658 (SELECT e.buy FROM exchangerate e
1659 WHERE e.curr = o.curr
1660 AND e.transdate = o.transdate)
1662 WHERE o.customer_id = ?
1663 AND o.quotation = '0'
1664 AND o.closed = '0'|;
1665 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1667 while (my ($amount, $exch) = $sth->fetchrow_array) {
1668 $exch = 1 unless $exch;
1669 $form->{creditremaining} -= $amount * $exch;
1673 # get shipto if we did not converted an order or invoice
1674 if (!$form->{shipto}) {
1675 map { delete $form->{$_} }
1676 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1677 shiptostreet shiptozipcode shiptocity shiptocountry
1678 shiptocontact shiptophone shiptofax shiptoemail);
1680 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1681 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1683 map { $form->{$_} = $ref->{$_} } keys %$ref;
1686 # setup last accounts used for this customer
1687 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1689 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1691 JOIN acc_trans ac ON (ac.chart_id = c.id)
1692 JOIN ar a ON (a.id = ac.trans_id)
1693 WHERE a.customer_id = ?
1694 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1695 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1696 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1699 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1700 if ($ref->{category} eq 'I') {
1702 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1704 if ($form->{initial_transdate}) {
1706 qq|SELECT tk.tax_id, t.rate
1708 LEFT JOIN tax t ON tk.tax_id = t.id
1709 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1710 ORDER BY tk.startdate DESC
1712 my ($tax_id, $rate) =
1713 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1714 $form->{initial_transdate});
1715 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1718 if ($ref->{category} eq 'A') {
1719 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1723 $form->{rowcount} = $i if ($i && !$form->{type});
1726 $main::lxdebug->leave_sub();
1730 $main::lxdebug->enter_sub();
1732 my ($self, $myconfig, $form) = @_;
1734 # connect to database
1735 my $dbh = $form->dbconnect($myconfig);
1737 my $i = $form->{rowcount};
1739 my $where = qq|NOT p.obsolete = '1'|;
1742 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1743 my ($table, $field) = split m/\./, $column;
1744 next if !$form->{"${field}_${i}"};
1745 $where .= qq| AND lower(${column}) ILIKE ?|;
1746 push @values, '%' . $form->{"${field}_${i}"} . '%';
1749 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1750 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1751 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1752 push @values, $form->{"partnumber_$i"};
1755 if ($form->{"description_$i"}) {
1756 $where .= qq| ORDER BY p.description|;
1758 $where .= qq| ORDER BY p.partnumber|;
1762 if ($form->{type} eq "invoice") {
1764 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1765 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1769 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1773 my $taxzone_id = $form->{taxzone_id} * 1;
1774 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1778 p.id, p.partnumber, p.description, p.sellprice,
1779 p.listprice, p.inventory_accno_id, p.lastcost,
1781 c1.accno AS inventory_accno,
1782 c1.new_chart_id AS inventory_new_chart,
1783 date($transdate) - c1.valid_from AS inventory_valid,
1785 c2.accno AS income_accno,
1786 c2.new_chart_id AS income_new_chart,
1787 date($transdate) - c2.valid_from AS income_valid,
1789 c3.accno AS expense_accno,
1790 c3.new_chart_id AS expense_new_chart,
1791 date($transdate) - c3.valid_from AS expense_valid,
1793 p.unit, p.assembly, p.bin, p.onhand,
1794 p.notes AS partnotes, p.notes AS longdescription,
1795 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1798 pfac.factor AS price_factor,
1803 LEFT JOIN chart c1 ON
1804 ((SELECT inventory_accno_id
1805 FROM buchungsgruppen
1806 WHERE id = p.buchungsgruppen_id) = c1.id)
1807 LEFT JOIN chart c2 ON
1808 ((SELECT income_accno_id_${taxzone_id}
1809 FROM buchungsgruppen
1810 WHERE id = p.buchungsgruppen_id) = c2.id)
1811 LEFT JOIN chart c3 ON
1812 ((SELECT expense_accno_id_${taxzone_id}
1813 FROM buchungsgruppen
1814 WHERE id = p.buchungsgruppen_id) = c3.id)
1815 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1816 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1818 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1820 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1822 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1823 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1824 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1825 if (!$ref->{inventory_accno_id}) {
1826 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1828 delete($ref->{inventory_accno_id});
1830 foreach my $type (qw(inventory income expense)) {
1831 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1833 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1836 ($ref->{"${type}_accno"},
1837 $ref->{"${type}_new_chart"},
1838 $ref->{"${type}_valid"})
1839 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1843 if ($form->{payment_id} eq "") {
1844 $form->{payment_id} = $form->{part_payment_id};
1847 # get tax rates and description
1848 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1850 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1852 LEFT JOIN chart c ON (c.id = t.chart_id)
1856 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1858 ORDER BY startdate DESC
1861 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1862 my $stw = $dbh->prepare($query);
1863 $stw->execute(@values) || $form->dberror($query);
1865 $ref->{taxaccounts} = "";
1867 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1869 # if ($customertax{$ref->{accno}})
1870 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1874 $ref->{taxaccounts} .= "$ptr->{accno} ";
1876 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1877 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1878 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1879 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1880 $form->{taxaccounts} .= "$ptr->{accno} ";
1886 chop $ref->{taxaccounts};
1887 if ($form->{language_id}) {
1889 qq|SELECT tr.translation, tr.longdescription
1891 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1892 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1893 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1894 if ($translation ne "") {
1895 $ref->{description} = $translation;
1896 $ref->{longdescription} = $longdescription;
1900 qq|SELECT tr.translation, tr.longdescription
1902 WHERE tr.language_id IN
1905 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1908 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1909 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1910 if ($translation ne "") {
1911 $ref->{description} = $translation;
1912 $ref->{longdescription} = $longdescription;
1917 $ref->{onhand} *= 1;
1919 push @{ $form->{item_list} }, $ref;
1921 if ($form->{lizenzen}) {
1922 if ($ref->{inventory_accno} > 0) {
1926 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1927 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1928 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1929 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1937 foreach my $item (@{ $form->{item_list} }) {
1938 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1939 trans_id => $item->{id},
1943 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1948 $main::lxdebug->leave_sub();
1951 ##########################
1952 # get pricegroups from database
1953 # build up selected pricegroup
1954 # if an exchange rate - change price
1957 sub get_pricegroups_for_parts {
1959 $main::lxdebug->enter_sub();
1961 my ($self, $myconfig, $form) = @_;
1963 my $dbh = $form->dbconnect($myconfig);
1965 $form->{"PRICES"} = {};
1969 my $all_units = AM->retrieve_units($myconfig, $form);
1970 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1971 $form->{"PRICES"}{$i} = [];
1973 $id = $form->{"id_$i"};
1975 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1976 $id = $form->{"new_id_$i"};
1979 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1981 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1982 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1983 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1985 my $price_new = $form->{"price_new_$i"};
1986 my $price_old = $form->{"price_old_$i"};
1988 if (!$form->{"unit_old_$i"}) {
1989 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1990 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1991 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1992 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1995 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1996 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1997 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1999 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2000 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2001 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2002 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2003 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2004 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2005 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2010 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2011 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2012 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2013 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2014 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2018 if (!$form->{"basefactor_$i"}) {
2019 $form->{"basefactor_$i"} = 1;
2025 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2026 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2036 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2038 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2039 'selected' AS selected
2042 ORDER BY pricegroup|;
2043 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2044 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2046 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2048 $pkr->{selected} = '';
2050 # if there is an exchange rate change price
2051 if (($form->{exchangerate} * 1) != 0) {
2052 $pkr->{price} /= $form->{exchangerate};
2055 $pkr->{price} *= $form->{"basefactor_$i"};
2056 $pkr->{price} *= $basefactor;
2057 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2059 if ($selectedpricegroup_id eq undef) {
2060 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2062 $pkr->{selected} = ' selected';
2064 # no customer pricesgroup set
2065 if ($pkr->{price} == $pkr->{default_sellprice}) {
2067 $pkr->{price} = $form->{"sellprice_$i"};
2071 # this sub should not set anything and only return. --sschoeling, 20090506
2072 # $form->{"sellprice_$i"} = $pkr->{price};
2075 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2076 $pkr->{price} = $form->{"sellprice_$i"};
2077 $pkr->{selected} = ' selected';
2081 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2082 if ($selectedpricegroup_id ne $pricegroup_old) {
2083 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2084 $pkr->{selected} = ' selected';
2086 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2087 if ($pkr->{pricegroup_id} == 0) {
2088 $pkr->{price} = $form->{"sellprice_$i"};
2089 $pkr->{selected} = ' selected';
2091 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2092 $pkr->{selected} = ' selected';
2093 if ( ($pkr->{pricegroup_id} == 0)
2094 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2095 # $pkr->{price} = $form->{"sellprice_$i"};
2097 $pkr->{price} = $form->{"sellprice_$i"};
2101 push @{ $form->{PRICES}{$i} }, $pkr;
2104 $form->{"basefactor_$i"} *= $basefactor;
2113 $main::lxdebug->leave_sub();
2117 $main::lxdebug->enter_sub();
2119 my ($self, $myconfig, $form, $table) = @_;
2121 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2123 # make sure there's no funny stuff in $table
2124 # ToDO: die when this happens and throw an error
2125 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2127 my $dbh = $form->dbconnect($myconfig);
2129 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2130 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2134 $main::lxdebug->leave_sub();
2140 $main::lxdebug->enter_sub();
2142 my ($self, $myconfig, $form, $table, $id) = @_;
2144 $main::lxdebug->leave_sub() and return 0 unless ($id);
2146 # make sure there's no funny stuff in $table
2147 # ToDO: die when this happens and throw an error
2148 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2150 my $dbh = $form->dbconnect($myconfig);
2152 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2153 my ($result) = selectrow_query($form, $dbh, $query, $id);
2157 $main::lxdebug->leave_sub();