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;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form, $locale) = @_;
59 $form->{duedate} ||= $form->{invdate};
62 my $dbh = $form->get_standard_dbh;
65 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
66 ($form->{terms}) = selectrow_query($form, $dbh, $query);
68 my (@project_ids, %projectnumbers, %projectdescriptions);
69 $form->{TEMPLATE_ARRAYS} = {};
71 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
73 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
76 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
77 $price_factors{$pfac->{id}} = $pfac;
79 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
82 # sort items by partsgroup
83 for my $i (1 .. $form->{rowcount}) {
85 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
86 # $partsgroup = $form->{"partsgroup_$i"};
88 # push @partsgroup, [$i, $partsgroup];
89 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
93 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
94 join(", ", map({ "?" } @project_ids)) . ")";
95 $sth = $dbh->prepare($query);
96 $sth->execute(@project_ids) ||
97 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
98 while (my $ref = $sth->fetchrow_hashref()) {
99 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $projectdescriptions{$ref->{id}} = $ref->{description};
105 $form->{"globalprojectnumber"} =
106 $projectnumbers{$form->{"globalproject_id"}};
107 $form->{"globalprojectdescription"} =
108 $projectdescriptions{$form->{"globalproject_id"}};
115 my %oid = ('Pg' => 'oid',
116 'Oracle' => 'rowid');
118 # sort items by partsgroup
119 for $i (1 .. $form->{rowcount}) {
121 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
122 $partsgroup = $form->{"partsgroup_$i"};
124 push @partsgroup, [$i, $partsgroup];
137 my $nodiscount_subtotal = 0;
138 my $discount_subtotal = 0;
140 my $subtotal_header = 0;
143 $form->{discount} = [];
145 IC->prepare_parts_for_printing();
147 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
150 qw(runningnumber number description longdescription qty ship unit bin
151 deliverydate_oe ordnumber_oe transdate_oe validuntil
152 partnotes serialnumber reqdate sellprice listprice netprice
153 discount p_discount discount_sub nodiscount_sub
154 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
155 price_factor price_factor_name partsgroup);
157 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
159 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
161 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
163 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
165 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
168 if ($item->[1] ne $sameitem) {
169 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
170 $sameitem = $item->[1];
172 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
175 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
177 if ($form->{"id_$i"} != 0) {
179 # add number, description and qty to $form->{number},
180 if ($form->{"subtotal_$i"} && !$subtotal_header) {
181 $subtotal_header = $i;
182 $position = int($position);
185 } elsif ($subtotal_header) {
187 $position = int($position);
188 $position = $position.".".$subposition;
190 $position = int($position);
194 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
196 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
197 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
204 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
209 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
214 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
215 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
217 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
219 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
220 my ($dec) = ($sellprice =~ /\.(\d+)/);
221 my $decimalplaces = max 2, length($dec);
223 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
224 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
225 my $linetotal = $form->round_amount($linetotal_exact, 2);
226 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
228 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
229 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
231 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
232 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
234 $linetotal = ($linetotal != 0) ? $linetotal : '';
236 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
237 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
240 $form->{total} += $linetotal;
241 $form->{nodiscount_total} += $nodiscount_linetotal;
242 $form->{discount_total} += $discount;
244 if ($subtotal_header) {
245 $discount_subtotal += $linetotal;
246 $nodiscount_subtotal += $nodiscount_linetotal;
249 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
250 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
252 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
255 $discount_subtotal = 0;
256 $nodiscount_subtotal = 0;
257 $subtotal_header = 0;
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
264 if (!$form->{"discount_$i"}) {
265 $nodiscount += $linetotal;
268 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
269 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
270 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
271 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
273 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
274 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
276 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
280 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
282 if ($form->{taxincluded}) {
285 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
286 $taxbase = $linetotal - $taxamount;
288 $taxamount = $linetotal * $taxrate;
289 $taxbase = $linetotal;
292 if ($form->round_amount($taxrate, 7) == 0) {
293 if ($form->{taxincluded}) {
294 foreach my $accno (@taxaccounts) {
295 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
297 $taxaccounts{$accno} += $taxamount;
298 $taxdiff += $taxamount;
300 $taxbase{$accno} += $taxbase;
302 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
304 foreach my $accno (@taxaccounts) {
305 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
306 $taxbase{$accno} += $taxbase;
310 foreach my $accno (@taxaccounts) {
311 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
312 $taxbase{$accno} += $taxbase;
315 my $tax_rate = $taxrate * 100;
316 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
317 if ($form->{"assembly_$i"}) {
320 # get parts and push them onto the stack
322 if ($form->{groupitems}) {
324 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
326 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
330 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
332 JOIN parts p ON (a.parts_id = p.id)
333 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
334 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
335 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
337 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
338 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
339 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
340 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
341 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
344 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
346 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
347 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
349 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
350 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
356 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
360 foreach my $item (sort keys %taxaccounts) {
361 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
363 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
364 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
365 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
366 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
368 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
369 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
373 for my $i (1 .. $form->{paidaccounts}) {
374 if ($form->{"paid_$i"}) {
375 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
377 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
378 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
379 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
383 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
386 if($form->{taxincluded}) {
387 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
388 $form->{subtotal_nofmt} = $form->{total} - $tax;
391 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
392 $form->{subtotal_nofmt} = $form->{total};
395 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
396 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
397 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
398 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
400 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
401 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
403 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
404 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
406 $form->set_payment_options($myconfig, $form->{invdate});
408 $form->{username} = $myconfig->{name};
410 $main::lxdebug->leave_sub();
413 sub project_description {
414 $main::lxdebug->enter_sub();
416 my ($self, $dbh, $id) = @_;
417 my $form = \%main::form;
419 my $query = qq|SELECT description FROM project WHERE id = ?|;
420 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
422 $main::lxdebug->leave_sub();
427 sub customer_details {
428 $main::lxdebug->enter_sub();
430 my ($self, $myconfig, $form, @wanted_vars) = @_;
432 # connect to database
433 my $dbh = $form->get_standard_dbh;
435 my $language_id = $form->{language_id};
437 # get contact id, set it if nessessary
440 my @values = (conv_i($form->{customer_id}));
443 if ($form->{cp_id}) {
444 $where = qq| AND (cp.cp_id = ?) |;
445 push(@values, conv_i($form->{cp_id}));
448 # get rest for the customer
450 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
451 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
453 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
454 WHERE (ct.id = ?) $where
457 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
459 # remove id and taxincluded before copy back
460 delete @$ref{qw(id taxincluded)};
462 @wanted_vars = grep({ $_ } @wanted_vars);
463 if (scalar(@wanted_vars) > 0) {
465 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
466 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
469 map { $form->{$_} = $ref->{$_} } keys %$ref;
471 if ($form->{delivery_customer_id}) {
473 qq|SELECT *, notes as customernotes
477 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
479 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
482 if ($form->{delivery_vendor_id}) {
484 qq|SELECT *, notes as customernotes
488 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
490 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
493 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
495 'trans_id' => $form->{customer_id});
496 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
498 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
499 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
500 'language_id' => $language_id,
501 'allow_fallback' => 1);
504 $main::lxdebug->leave_sub();
508 $main::lxdebug->enter_sub();
510 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
512 # connect to database, turn off autocommit
513 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
515 my ($query, $sth, $null, $project_id, @values);
516 my $exchangerate = 0;
518 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
521 if (!$form->{employee_id}) {
522 $form->get_employee($dbh);
525 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
526 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
527 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
528 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
529 if (!$form->{department_id}){
530 ($null, $form->{department_id}) = split(/--/, $form->{department});
533 my $all_units = AM->retrieve_units($myconfig, $form);
535 if (!$payments_only) {
537 &reverse_invoice($dbh, $form);
540 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
541 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
543 $query = qq|SELECT nextval('glid')|;
544 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
546 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
547 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
549 if (!$form->{invnumber}) {
551 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
552 "cnnumber" : "invnumber", $dbh);
557 my ($netamount, $invoicediff) = (0, 0);
558 my ($amount, $linetotal, $lastincomeaccno);
560 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
561 my $defaultcurrency = (split m/:/, $currencies)[0];
563 if ($form->{currency} eq $defaultcurrency) {
564 $form->{exchangerate} = 1;
566 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
569 $form->{exchangerate} =
572 : $form->parse_amount($myconfig, $form->{exchangerate});
574 $form->{expense_inventory} = "";
578 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
579 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
582 $form->{amount} = {};
583 $form->{amount_cogs} = {};
585 foreach my $i (1 .. $form->{rowcount}) {
586 if ($form->{type} eq "credit_note") {
587 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
588 $form->{shipped} = 1;
590 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
595 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
596 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
597 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
599 if ($form->{storno}) {
600 $form->{"qty_$i"} *= -1;
603 if ($form->{"id_$i"}) {
606 if (defined($baseunits{$form->{"id_$i"}})) {
607 $item_unit = $baseunits{$form->{"id_$i"}};
610 $query = qq|SELECT unit FROM parts WHERE id = ?|;
611 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
612 $baseunits{$form->{"id_$i"}} = $item_unit;
615 if (defined($all_units->{$item_unit}->{factor})
616 && ($all_units->{$item_unit}->{factor} ne '')
617 && ($all_units->{$item_unit}->{factor} != 0)) {
618 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
622 $baseqty = $form->{"qty_$i"} * $basefactor;
624 my ($allocated, $taxrate) = (0, 0);
628 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
630 # keep entered selling price
632 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
634 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
636 my $decimalplaces = ($dec > 2) ? $dec : 2;
638 # undo discount formatting
639 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
642 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
644 # round linetotal to 2 decimal places
645 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
646 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
648 if ($form->{taxincluded}) {
649 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
650 $form->{"sellprice_$i"} =
651 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
653 $taxamount = $linetotal * $taxrate;
656 $netamount += $linetotal;
658 if ($taxamount != 0) {
660 $form->{amount}{ $form->{id} }{$_} +=
661 $taxamount * $form->{"${_}_rate"} / $taxrate
662 } split(/ /, $form->{"taxaccounts_$i"});
665 # add amount to income, $form->{amount}{trans_id}{accno}
666 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
668 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
669 $linetotal = $form->round_amount($linetotal, 2);
671 # this is the difference from the inventory
672 $invoicediff += ($amount - $linetotal);
674 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
677 $lastincomeaccno = $form->{"income_accno_$i"};
679 # adjust and round sellprice
680 $form->{"sellprice_$i"} =
681 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
684 next if $payments_only;
686 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
688 if ($form->{"assembly_$i"}) {
689 # record assembly item as allocated
690 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
693 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
697 # get pricegroup_id and save it
698 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
701 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
703 # save detail record in invoice table
705 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
706 sellprice, fxsellprice, discount, allocated, assemblyitem,
707 unit, deliverydate, project_id, serialnumber, pricegroup_id,
708 ordnumber, transdate, cusordnumber, base_qty, subtotal,
709 marge_percent, marge_total, lastcost,
710 price_factor_id, price_factor, marge_price_factor)
711 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
712 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
714 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
715 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
716 $form->{"sellprice_$i"}, $fxsellprice,
717 $form->{"discount_$i"}, $allocated, 'f',
718 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
719 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
720 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
721 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
722 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
723 $form->{"lastcost_$i"},
724 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
725 conv_i($form->{"marge_price_factor_$i"}));
726 do_query($form, $dbh, $query, @values);
728 CVar->save_custom_variables(module => 'IC',
729 sub_module => 'invoice',
730 trans_id => $invoice_id,
731 configs => $ic_cvar_configs,
733 name_prefix => 'ic_',
734 name_postfix => "_$i",
739 # total payments, don't move we need it here
740 for my $i (1 .. $form->{paidaccounts}) {
741 if ($form->{type} eq "credit_note") {
742 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
744 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
746 $form->{paid} += $form->{"paid_$i"};
747 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
750 my ($tax, $diff) = (0, 0);
752 $netamount = $form->round_amount($netamount, 2);
754 # figure out rounding errors for total amount vs netamount + taxes
755 if ($form->{taxincluded}) {
757 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
758 $diff += $amount - $netamount * $form->{exchangerate};
759 $netamount = $amount;
761 foreach my $item (split(/ /, $form->{taxaccounts})) {
762 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
763 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
764 $tax += $form->{amount}{ $form->{id} }{$item};
765 $netamount -= $form->{amount}{ $form->{id} }{$item};
768 $invoicediff += $diff;
769 ######## this only applies to tax included
770 if ($lastincomeaccno) {
771 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
775 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
776 $diff = $amount - $netamount * $form->{exchangerate};
777 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $form->{amount}{ $form->{id} }{$item} =
780 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
783 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
786 $amount - $form->{amount}{ $form->{id} }{$item} *
787 $form->{exchangerate};
788 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
789 $tax += $form->{amount}{ $form->{id} }{$item};
793 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
795 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
798 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
800 # update exchangerate
801 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
802 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
803 $form->{exchangerate}, 0);
806 $project_id = conv_i($form->{"globalproject_id"});
808 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
809 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
810 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
812 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
814 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
816 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
817 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
818 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
819 do_query($form, $dbh, $query, @values);
820 $form->{amount_cogs}{$trans_id}{$accno} = 0;
824 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
825 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
827 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
829 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
830 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
831 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
832 do_query($form, $dbh, $query, @values);
837 foreach my $trans_id (keys %{ $form->{amount} }) {
838 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
839 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
841 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
843 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
845 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
846 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
847 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
848 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
849 do_query($form, $dbh, $query, @values);
850 $form->{amount}{$trans_id}{$accno} = 0;
854 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
855 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
857 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
859 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
860 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
861 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
862 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
863 do_query($form, $dbh, $query, @values);
868 # deduct payment differences from diff
869 for my $i (1 .. $form->{paidaccounts}) {
870 if ($form->{"paid_$i"} != 0) {
872 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
873 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
877 # record payments and offsetting AR
878 if (!$form->{storno}) {
879 for my $i (1 .. $form->{paidaccounts}) {
881 if ($form->{"acc_trans_id_$i"}
883 && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
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;
917 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
920 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
921 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
922 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
923 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
924 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
925 do_query($form, $dbh, $query, @values);
927 # exchangerate difference
928 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
929 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
933 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
934 $form->{"exchangerate_$i"};
936 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
939 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
945 # update exchange rate
946 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
947 $form->update_exchangerate($dbh, $form->{currency},
948 $form->{"datepaid_$i"},
949 $form->{"exchangerate_$i"}, 0);
953 } else { # if (!$form->{storno})
954 $form->{marge_total} *= -1;
957 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
959 if ($payments_only) {
960 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
961 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
963 $dbh->commit if !$provided_dbh;
965 $main::lxdebug->leave_sub();
969 # record exchange rate differences and gains/losses
970 foreach my $accno (keys %{ $form->{fx} }) {
971 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
973 ($form->{fx}{$accno}{$transdate} =
974 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
979 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
980 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
981 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
982 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
983 do_query($form, $dbh, $query, @values);
988 $amount = $netamount + $tax;
991 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
993 $query = qq|UPDATE ar set
994 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
995 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
996 amount = ?, netamount = ?, paid = ?,
997 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
998 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
999 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1000 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1001 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1002 cp_id = ?, marge_total = ?, marge_percent = ?,
1003 globalproject_id = ?, delivery_customer_id = ?,
1004 transaction_description = ?, delivery_vendor_id = ?,
1005 donumber = ?, invnumber_for_credit_note = ?
1007 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1008 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1009 $amount, $netamount, $form->{"paid"},
1010 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1011 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1012 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1013 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1014 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1015 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1016 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1017 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1018 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1019 conv_i($form->{"id"}));
1020 do_query($form, $dbh, $query, @values);
1023 if ($form->{storno}) {
1026 paid = paid + amount,
1028 intnotes = ? || intnotes
1030 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1031 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1035 $form->{name} = $form->{customer};
1036 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1038 if (!$form->{shipto_id}) {
1039 $form->add_shipto($dbh, $form->{id}, "AR");
1042 # save printed, emailed and queued
1043 $form->save_status($dbh);
1045 Common::webdav_folder($form);
1047 # Link this record to the records it was created from.
1048 RecordLinks->create_links('dbh' => $dbh,
1050 'from_table' => 'oe',
1051 'from_ids' => $form->{convert_from_oe_ids},
1053 'to_id' => $form->{id},
1055 delete $form->{convert_from_oe_ids};
1057 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1059 if (scalar @convert_from_do_ids) {
1060 DO->close_orders('dbh' => $dbh,
1061 'ids' => \@convert_from_do_ids);
1063 RecordLinks->create_links('dbh' => $dbh,
1065 'from_table' => 'delivery_orders',
1066 'from_ids' => \@convert_from_do_ids,
1068 'to_id' => $form->{id},
1071 delete $form->{convert_from_do_ids};
1073 ARAP->close_orders_if_billed('dbh' => $dbh,
1074 'arap_id' => $form->{id},
1078 $dbh->commit if !$provided_dbh;
1080 $main::lxdebug->leave_sub();
1085 sub _delete_payments {
1086 $main::lxdebug->enter_sub();
1088 my ($self, $form, $dbh) = @_;
1090 my @delete_acc_trans_ids;
1092 # Delete old payment entries from acc_trans.
1094 qq|SELECT acc_trans_id
1096 WHERE (trans_id = ?) AND fx_transaction
1100 SELECT at.acc_trans_id
1102 LEFT JOIN chart c ON (at.chart_id = c.id)
1103 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1104 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1107 qq|SELECT at.acc_trans_id
1109 LEFT JOIN chart c ON (at.chart_id = c.id)
1110 WHERE (trans_id = ?)
1111 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1112 ORDER BY at.acc_trans_id
1114 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1116 if (@delete_acc_trans_ids) {
1117 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1118 do_query($form, $dbh, $query);
1121 $main::lxdebug->leave_sub();
1125 $main::lxdebug->enter_sub();
1127 my ($self, $myconfig, $form, $locale) = @_;
1129 # connect to database, turn off autocommit
1130 my $dbh = $form->get_standard_dbh;
1133 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1135 $old_form = save_form();
1137 # Delete all entries in acc_trans from prior payments.
1138 if ($::lx_office_conf{features}->{payments_changeable} != 0) {
1139 $self->_delete_payments($form, $dbh);
1142 # Save the new payments the user made before cleaning up $form.
1143 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1145 # Clean up $form so that old content won't tamper the results.
1146 %keep_vars = map { $_, 1 } qw(login password id);
1147 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1149 # Retrieve the invoice from the database.
1150 $self->retrieve_invoice($myconfig, $form);
1152 # Set up the content of $form in the way that IS::post_invoice() expects.
1153 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1155 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1156 $item = $form->{invoice_details}->[$row - 1];
1158 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1160 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1163 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1165 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1167 # Restore the payment options from the user input.
1168 map { $form->{$_} = $payments{$_} } keys %payments;
1170 # Get the AR accno (which is normally done by Form::create_links()).
1174 LEFT JOIN chart c ON (at.chart_id = c.id)
1175 WHERE (trans_id = ?)
1176 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1177 ORDER BY at.acc_trans_id
1180 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1182 # Post the new payments.
1183 $self->post_invoice($myconfig, $form, $dbh, 1);
1185 restore_form($old_form);
1187 my $rc = $dbh->commit();
1189 $main::lxdebug->leave_sub();
1194 sub process_assembly {
1195 $main::lxdebug->enter_sub();
1197 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1200 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1201 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1203 JOIN parts p ON (a.parts_id = p.id)
1205 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1207 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1211 $ref->{inventory_accno_id} *= 1;
1212 $ref->{expense_accno_id} *= 1;
1214 # multiply by number of assemblies
1215 $ref->{qty} *= $totalqty;
1217 if ($ref->{assembly}) {
1218 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1221 if ($ref->{inventory_accno_id}) {
1222 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1226 # save detail record for individual assembly item in invoice table
1228 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1229 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1230 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1231 do_query($form, $dbh, $query, @values);
1237 $main::lxdebug->leave_sub();
1241 $main::lxdebug->enter_sub();
1243 # adjust allocated in table invoice according to FIFO princicple
1244 # for a certain part with part_id $id
1246 my ($dbh, $myconfig, $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, i.price_factor,
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 # all invoice entries of an example part:
1273 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1274 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1275 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1276 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1277 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1278 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1279 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1281 # AND ((i.base_qty + i.allocated) < 0) filters out all but line with id=7, elsewhere i.base_qty + i.allocated has already reached 0
1282 # and all parts have been allocated
1284 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1285 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1287 # in this example there are still 4 unsold articles
1290 # search all invoice entries for the part in question, adjusting "allocated"
1291 # until the total number of sold parts has been reached
1293 # ORDER BY trans_id ensures FIFO
1296 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1297 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1301 # update allocated in invoice
1302 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1304 # total expenses and inventory
1305 # sellprice is the cost of the item
1306 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1308 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1309 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1310 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1312 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1313 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1314 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1316 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1317 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1323 last if (($totalqty -= $qty) <= 0);
1328 $main::lxdebug->leave_sub();
1333 sub reverse_invoice {
1334 $main::lxdebug->enter_sub();
1336 my ($dbh, $form) = @_;
1338 # reverse inventory items
1340 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1342 JOIN parts p ON (i.parts_id = p.id)
1343 WHERE i.trans_id = ?|;
1344 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1346 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1348 if ($ref->{inventory_accno_id}) {
1349 # de-allocated purchases
1351 qq|SELECT i.id, i.trans_id, i.allocated
1353 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1354 ORDER BY i.trans_id DESC|;
1355 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1357 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1358 my $qty = $ref->{qty};
1359 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1360 $qty = $inhref->{allocated};
1364 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1366 last if (($ref->{qty} -= $qty) <= 0);
1375 my @values = (conv_i($form->{id}));
1376 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1377 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1378 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1380 $main::lxdebug->leave_sub();
1383 sub delete_invoice {
1384 $main::lxdebug->enter_sub();
1386 my ($self, $myconfig, $form) = @_;
1388 # connect to database
1389 my $dbh = $form->get_standard_dbh;
1392 &reverse_invoice($dbh, $form);
1394 my @values = (conv_i($form->{id}));
1396 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1397 # zurückgesetzt werden. Vgl:
1398 # id | storno | storno_id | paid | amount
1399 #----+--------+-----------+---------+-----------
1400 # 18 | f | | 0.00000 | 119.00000
1402 # 18 | t | | 119.00000 | 119.00000
1404 if($form->{storno}){
1405 # storno_id auslesen und korrigieren
1406 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1407 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1411 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1413 # delete spool files
1414 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1416 # delete status entries
1417 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1419 my $rc = $dbh->commit;
1422 my $spool = $::lx_office_conf{paths}->{spool};
1423 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1426 $main::lxdebug->leave_sub();
1431 sub retrieve_invoice {
1432 $main::lxdebug->enter_sub();
1434 my ($self, $myconfig, $form) = @_;
1436 # connect to database
1437 my $dbh = $form->get_standard_dbh;
1439 my ($sth, $ref, $query);
1441 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1445 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1446 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1447 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1448 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1449 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1450 d.curr AS currencies
1454 $ref = selectfirst_hashref_query($form, $dbh, $query);
1455 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1458 my $id = conv_i($form->{id});
1461 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1465 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1466 a.orddate, a.quodate, a.globalproject_id,
1467 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1468 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1469 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1470 a.employee_id, a.salesman_id, a.payment_id,
1471 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1472 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1473 a.marge_total, a.marge_percent,
1476 LEFT JOIN employee e ON (e.id = a.employee_id)
1478 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1479 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1482 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1485 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1486 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1488 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1490 foreach my $vc (qw(customer vendor)) {
1491 next if !$form->{"delivery_${vc}_id"};
1492 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1495 # get printed, emailed
1496 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1497 $sth = prepare_execute_query($form, $dbh, $query, $id);
1499 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1500 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1501 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1502 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1505 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1507 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1508 : $form->{invdate} ? $dbh->quote($form->{invdate})
1512 my $taxzone_id = $form->{taxzone_id} *= 1;
1513 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1515 # retrieve individual items
1518 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1519 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1520 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1523 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1524 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1525 i.price_factor_id, i.price_factor, i.marge_price_factor,
1526 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1527 pr.projectnumber, pg.partsgroup, prg.pricegroup
1530 LEFT JOIN parts p ON (i.parts_id = p.id)
1531 LEFT JOIN project pr ON (i.project_id = pr.id)
1532 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1533 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1535 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1536 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1537 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1539 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1541 $sth = prepare_execute_query($form, $dbh, $query, $id);
1543 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1544 # Retrieve custom variables.
1545 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1547 sub_module => 'invoice',
1548 trans_id => $ref->{invoice_id},
1550 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1551 delete $ref->{invoice_id};
1553 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1554 delete($ref->{"part_inventory_accno_id"});
1556 foreach my $type (qw(inventory income expense)) {
1557 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1558 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1559 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1563 # get tax rates and description
1564 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1566 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1567 LEFT JOIN chart c ON (c.id = t.chart_id)
1569 (SELECT tk.tax_id FROM taxkeys tk
1570 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1571 AND startdate <= date($transdate)
1572 ORDER BY startdate DESC LIMIT 1)
1574 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1575 $ref->{taxaccounts} = "";
1577 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1579 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1583 $ref->{taxaccounts} .= "$ptr->{accno} ";
1585 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1586 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1587 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1588 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1589 $form->{taxaccounts} .= "$ptr->{accno} ";
1594 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1596 chop $ref->{taxaccounts};
1597 push @{ $form->{invoice_details} }, $ref;
1602 Common::webdav_folder($form);
1605 my $rc = $dbh->commit;
1607 $main::lxdebug->leave_sub();
1613 $main::lxdebug->enter_sub();
1615 my ($self, $myconfig, $form) = @_;
1617 # connect to database
1618 my $dbh = $form->get_standard_dbh;
1620 my $dateformat = $myconfig->{dateformat};
1621 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1623 my (@values, $duedate, $ref, $query);
1625 if ($form->{invdate}) {
1626 $duedate = "to_date(?, '$dateformat')";
1627 push @values, $form->{invdate};
1629 $duedate = "current_date";
1632 my $cid = conv_i($form->{customer_id});
1635 if ($form->{payment_id}) {
1636 $payment_id = "(pt.id = ?) OR";
1637 push @values, conv_i($form->{payment_id});
1643 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1644 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1645 c.street, c.zipcode, c.city, c.country,
1646 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1647 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1648 b.discount AS tradediscount, b.description AS business
1650 LEFT JOIN business b ON (b.id = c.business_id)
1651 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1654 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1656 delete $ref->{salesman_id} if !$ref->{salesman_id};
1658 map { $form->{$_} = $ref->{$_} } keys %$ref;
1661 qq|SELECT sum(amount - paid) AS dunning_amount
1663 WHERE (paid < amount)
1664 AND (customer_id = ?)
1665 AND (dunning_config_id IS NOT NULL)|;
1666 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1667 map { $form->{$_} = $ref->{$_} } keys %$ref;
1670 qq|SELECT dnn.dunning_description AS max_dunning_level
1671 FROM dunning_config dnn
1672 WHERE id IN (SELECT dunning_config_id
1674 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1675 ORDER BY dunning_level DESC LIMIT 1|;
1676 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1677 map { $form->{$_} = $ref->{$_} } keys %$ref;
1679 $form->{creditremaining} = $form->{creditlimit};
1680 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1681 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1682 $form->{creditremaining} -= $value;
1686 (SELECT e.buy FROM exchangerate e
1687 WHERE e.curr = o.curr
1688 AND e.transdate = o.transdate)
1690 WHERE o.customer_id = ?
1691 AND o.quotation = '0'
1692 AND o.closed = '0'|;
1693 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1695 while (my ($amount, $exch) = $sth->fetchrow_array) {
1696 $exch = 1 unless $exch;
1697 $form->{creditremaining} -= $amount * $exch;
1701 # get shipto if we did not converted an order or invoice
1702 if (!$form->{shipto}) {
1703 map { delete $form->{$_} }
1704 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1705 shiptostreet shiptozipcode shiptocity shiptocountry
1706 shiptocontact shiptophone shiptofax shiptoemail);
1708 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1709 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1711 map { $form->{$_} = $ref->{$_} } keys %$ref;
1714 # setup last accounts used for this customer
1715 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1717 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1719 JOIN acc_trans ac ON (ac.chart_id = c.id)
1720 JOIN ar a ON (a.id = ac.trans_id)
1721 WHERE a.customer_id = ?
1722 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1723 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1724 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1727 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1728 if ($ref->{category} eq 'I') {
1730 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1732 if ($form->{initial_transdate}) {
1734 qq|SELECT tk.tax_id, t.rate
1736 LEFT JOIN tax t ON tk.tax_id = t.id
1737 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1738 ORDER BY tk.startdate DESC
1740 my ($tax_id, $rate) =
1741 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1742 $form->{initial_transdate});
1743 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1746 if ($ref->{category} eq 'A') {
1747 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1751 $form->{rowcount} = $i if ($i && !$form->{type});
1754 $main::lxdebug->leave_sub();
1758 $main::lxdebug->enter_sub();
1760 my ($self, $myconfig, $form) = @_;
1762 # connect to database
1763 my $dbh = $form->get_standard_dbh;
1765 my $i = $form->{rowcount};
1767 my $where = qq|NOT p.obsolete = '1'|;
1770 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1771 my ($table, $field) = split m/\./, $column;
1772 next if !$form->{"${field}_${i}"};
1773 $where .= qq| AND lower(${column}) ILIKE ?|;
1774 push @values, '%' . $form->{"${field}_${i}"} . '%';
1777 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1778 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1779 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1780 push @values, $form->{"partnumber_$i"};
1783 # Search for part ID overrides all other criteria.
1784 if ($form->{"id_${i}"}) {
1785 $where = qq|p.id = ?|;
1786 @values = ($form->{"id_${i}"});
1789 if ($form->{"description_$i"}) {
1790 $where .= qq| ORDER BY p.description|;
1792 $where .= qq| ORDER BY p.partnumber|;
1796 if ($form->{type} eq "invoice") {
1798 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1799 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1803 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1807 my $taxzone_id = $form->{taxzone_id} * 1;
1808 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1812 p.id, p.partnumber, p.description, p.sellprice,
1813 p.listprice, p.inventory_accno_id, p.lastcost,
1815 c1.accno AS inventory_accno,
1816 c1.new_chart_id AS inventory_new_chart,
1817 date($transdate) - c1.valid_from AS inventory_valid,
1819 c2.accno AS income_accno,
1820 c2.new_chart_id AS income_new_chart,
1821 date($transdate) - c2.valid_from AS income_valid,
1823 c3.accno AS expense_accno,
1824 c3.new_chart_id AS expense_new_chart,
1825 date($transdate) - c3.valid_from AS expense_valid,
1827 p.unit, p.assembly, p.bin, p.onhand,
1828 p.notes AS partnotes, p.notes AS longdescription,
1829 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1832 pfac.factor AS price_factor,
1837 LEFT JOIN chart c1 ON
1838 ((SELECT inventory_accno_id
1839 FROM buchungsgruppen
1840 WHERE id = p.buchungsgruppen_id) = c1.id)
1841 LEFT JOIN chart c2 ON
1842 ((SELECT income_accno_id_${taxzone_id}
1843 FROM buchungsgruppen
1844 WHERE id = p.buchungsgruppen_id) = c2.id)
1845 LEFT JOIN chart c3 ON
1846 ((SELECT expense_accno_id_${taxzone_id}
1847 FROM buchungsgruppen
1848 WHERE id = p.buchungsgruppen_id) = c3.id)
1849 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1850 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1852 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1854 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1856 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1857 [ qq|SELECT tr.translation, tr.longdescription
1859 WHERE tr.language_id IN
1862 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1865 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1867 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1869 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1870 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1871 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1872 if (!$ref->{inventory_accno_id}) {
1873 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1875 delete($ref->{inventory_accno_id});
1877 foreach my $type (qw(inventory income expense)) {
1878 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1880 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1883 ($ref->{"${type}_accno"},
1884 $ref->{"${type}_new_chart"},
1885 $ref->{"${type}_valid"})
1886 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1890 if ($form->{payment_id} eq "") {
1891 $form->{payment_id} = $form->{part_payment_id};
1894 # get tax rates and description
1895 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1897 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1899 LEFT JOIN chart c ON (c.id = t.chart_id)
1903 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1905 ORDER BY startdate DESC
1908 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1909 my $stw = $dbh->prepare($query);
1910 $stw->execute(@values) || $form->dberror($query);
1912 $ref->{taxaccounts} = "";
1914 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1916 # if ($customertax{$ref->{accno}})
1917 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1921 $ref->{taxaccounts} .= "$ptr->{accno} ";
1923 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1924 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1925 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1926 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1927 $form->{taxaccounts} .= "$ptr->{accno} ";
1933 chop $ref->{taxaccounts};
1935 if ($form->{language_id}) {
1936 for my $spec (@translation_queries) {
1937 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1938 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1939 next unless $translation;
1940 $ref->{description} = $translation;
1941 $ref->{longdescription} = $longdescription;
1946 $ref->{onhand} *= 1;
1948 push @{ $form->{item_list} }, $ref;
1951 $_->[1]->finish for @translation_queries;
1953 foreach my $item (@{ $form->{item_list} }) {
1954 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1955 trans_id => $item->{id},
1959 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1962 $main::lxdebug->leave_sub();
1965 ##########################
1966 # get pricegroups from database
1967 # build up selected pricegroup
1968 # if an exchange rate - change price
1971 sub get_pricegroups_for_parts {
1973 $main::lxdebug->enter_sub();
1975 my ($self, $myconfig, $form) = @_;
1977 my $dbh = $form->get_standard_dbh;
1979 $form->{"PRICES"} = {};
1983 my $all_units = AM->retrieve_units($myconfig, $form);
1984 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1985 $form->{"PRICES"}{$i} = [];
1987 $id = $form->{"id_$i"};
1989 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1990 $id = $form->{"new_id_$i"};
1993 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1995 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1997 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
1998 my $sellprice = $form->{"sellprice_$i"};
1999 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2000 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2001 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2003 my $price_new = $form->{"price_new_$i"};
2004 my $price_old = $form->{"price_old_$i"};
2006 if (!$form->{"unit_old_$i"}) {
2007 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2008 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2009 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2010 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2013 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2014 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2015 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2017 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2018 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2019 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2020 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2021 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2022 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2023 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2028 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2029 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2030 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2031 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2032 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2036 if (!$form->{"basefactor_$i"}) {
2037 $form->{"basefactor_$i"} = 1;
2043 sellprice AS default_sellprice,
2046 'selected' AS selected
2052 parts.sellprice AS default_sellprice,
2053 pricegroup.pricegroup,
2057 LEFT JOIN parts ON parts.id = parts_id
2058 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2060 ORDER BY pricegroup|;
2061 my @values = (conv_i($id), conv_i($id));
2062 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2064 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2066 $pkr->{selected} = '';
2068 # if there is an exchange rate change price
2069 if (($form->{exchangerate} * 1) != 0) {
2070 $pkr->{price} /= $form->{exchangerate};
2073 $pkr->{price} *= $form->{"basefactor_$i"};
2074 $pkr->{price} *= $basefactor;
2075 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2077 if ($selectedpricegroup_id eq undef) {
2078 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2079 # Case A: open old invoice, no pricegroup selected
2080 # Case B: add new article to invoice, no pricegroup selected
2082 # to distinguish case A and B the variable pricegroup_id_$i is used
2083 # for new articles this variable isn't defined, for loaded articles it is
2084 # sellprice can't be used, as it already has 0,00 set
2086 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2088 $pkr->{selected} = ' selected';
2090 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2091 and not defined $form->{"pricegroup_id_$i"}
2092 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2093 # for the case where pricegroup prices haven't been set
2095 # Case B: use default pricegroup of customer
2097 $pkr->{selected} = ' selected'; # unless $form->{selected};
2099 # no customer pricesgroup set
2100 if ($pkr->{price} == $pkr->{default_sellprice}) {
2102 $pkr->{price} = $form->{"sellprice_$i"};
2106 # this sub should not set anything and only return. --sschoeling, 20090506
2107 # is this correct? put in again... -- grichardson 20110119
2108 $form->{"sellprice_$i"} = $pkr->{price};
2111 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2112 $pkr->{price} = $form->{"sellprice_$i"};
2113 $pkr->{selected} = ' selected';
2117 # existing article: pricegroup or price changed
2118 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2119 if ($selectedpricegroup_id ne $pricegroup_old) {
2120 # pricegroup has changed
2121 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2122 $pkr->{selected} = ' selected';
2124 } elsif ( ($form->parse_amount($myconfig, $price_new)
2125 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2126 and ($price_new ne 0) and defined $price_new) {
2127 # sellprice has changed
2128 # when loading existing invoices $price_new is NULL
2129 if ($pkr->{pricegroup_id} == 0) {
2130 $pkr->{price} = $form->{"sellprice_$i"};
2131 $pkr->{selected} = ' selected';
2133 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2134 # neither sellprice nor pricegroup changed
2135 $pkr->{selected} = ' selected';
2136 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2137 # $pkr->{price} = $form->{"sellprice_$i"};
2139 $pkr->{price} = $form->{"sellprice_$i"};
2143 push @{ $form->{PRICES}{$i} }, $pkr;
2146 $form->{"basefactor_$i"} *= $basefactor;
2153 $main::lxdebug->leave_sub();
2157 $main::lxdebug->enter_sub();
2159 my ($self, $myconfig, $form, $table) = @_;
2161 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2163 # make sure there's no funny stuff in $table
2164 # ToDO: die when this happens and throw an error
2165 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2167 my $dbh = $form->get_standard_dbh;
2169 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2170 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2172 $main::lxdebug->leave_sub();
2178 $main::lxdebug->enter_sub();
2180 my ($self, $myconfig, $form, $table, $id) = @_;
2182 $main::lxdebug->leave_sub() and return 0 unless ($id);
2184 # make sure there's no funny stuff in $table
2185 # ToDO: die when this happens and throw an error
2186 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2188 my $dbh = $form->get_standard_dbh;
2190 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2191 my ($result) = selectrow_query($form, $dbh, $query, $id);
2193 $main::lxdebug->leave_sub();
2198 sub get_standard_accno_current_assets {
2199 $main::lxdebug->enter_sub();
2201 my ($self, $myconfig, $form) = @_;
2203 my $dbh = $form->get_standard_dbh;
2205 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2206 my ($result) = selectrow_query($form, $dbh, $query);
2208 $main::lxdebug->leave_sub();