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);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing();
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
231 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
233 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
235 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
248 $form->{total} += $linetotal;
249 $form->{nodiscount_total} += $nodiscount_linetotal;
250 $form->{discount_total} += $discount;
252 if ($subtotal_header) {
253 $discount_subtotal += $linetotal;
254 $nodiscount_subtotal += $nodiscount_linetotal;
257 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
271 if (!$form->{"discount_$i"}) {
272 $nodiscount += $linetotal;
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
364 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
365 for @{ $ic_cvar_configs };
369 foreach my $item (sort keys %taxaccounts) {
370 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
375 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
377 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
382 for my $i (1 .. $form->{paidaccounts}) {
383 if ($form->{"paid_$i"}) {
384 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
388 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
389 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
392 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
395 if($form->{taxincluded}) {
396 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
397 $form->{subtotal_nofmt} = $form->{total} - $tax;
400 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
401 $form->{subtotal_nofmt} = $form->{total};
404 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
405 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
406 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
407 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
409 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
410 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
412 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
413 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
415 $form->set_payment_options($myconfig, $form->{invdate});
417 $form->{username} = $myconfig->{name};
419 $main::lxdebug->leave_sub();
422 sub project_description {
423 $main::lxdebug->enter_sub();
425 my ($self, $dbh, $id) = @_;
426 my $form = \%main::form;
428 my $query = qq|SELECT description FROM project WHERE id = ?|;
429 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
431 $main::lxdebug->leave_sub();
436 sub customer_details {
437 $main::lxdebug->enter_sub();
439 my ($self, $myconfig, $form, @wanted_vars) = @_;
441 # connect to database
442 my $dbh = $form->get_standard_dbh;
444 my $language_id = $form->{language_id};
446 # get contact id, set it if nessessary
449 my @values = (conv_i($form->{customer_id}));
452 if ($form->{cp_id}) {
453 $where = qq| AND (cp.cp_id = ?) |;
454 push(@values, conv_i($form->{cp_id}));
457 # get rest for the customer
459 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
460 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
463 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
464 WHERE (ct.id = ?) $where
467 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
469 # remove id and taxincluded before copy back
470 delete @$ref{qw(id taxincluded)};
472 @wanted_vars = grep({ $_ } @wanted_vars);
473 if (scalar(@wanted_vars) > 0) {
475 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
476 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
479 map { $form->{$_} = $ref->{$_} } keys %$ref;
481 # remove any trailing whitespace
482 $form->{currency} =~ s/\s*$// if ($form->{currency});
484 if ($form->{delivery_customer_id}) {
486 qq|SELECT *, notes as customernotes
490 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
492 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
495 if ($form->{delivery_vendor_id}) {
497 qq|SELECT *, notes as customernotes
501 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
503 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
506 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
508 'trans_id' => $form->{customer_id});
509 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
511 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
512 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
513 'language_id' => $language_id,
514 'allow_fallback' => 1);
517 $main::lxdebug->leave_sub();
521 $main::lxdebug->enter_sub();
523 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
525 # connect to database, turn off autocommit
526 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
528 my ($query, $sth, $null, $project_id, @values);
529 my $exchangerate = 0;
531 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
534 if (!$form->{employee_id}) {
535 $form->get_employee($dbh);
538 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
539 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
540 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
541 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
542 if (!$form->{department_id}){
543 ($null, $form->{department_id}) = split(/--/, $form->{department});
546 my $all_units = AM->retrieve_units($myconfig, $form);
548 if (!$payments_only) {
550 &reverse_invoice($dbh, $form);
553 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
554 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
556 $query = qq|SELECT nextval('glid')|;
557 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
559 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
560 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
562 if (!$form->{invnumber}) {
564 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
565 "cnnumber" : "invnumber", $dbh);
570 my ($netamount, $invoicediff) = (0, 0);
571 my ($amount, $linetotal, $lastincomeaccno);
573 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
574 my $defaultcurrency = (split m/:/, $currencies)[0];
576 if ($form->{currency} eq $defaultcurrency) {
577 $form->{exchangerate} = 1;
579 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
582 $form->{exchangerate} =
585 : $form->parse_amount($myconfig, $form->{exchangerate});
587 $form->{expense_inventory} = "";
591 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
592 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
595 $form->{amount} = {};
596 $form->{amount_cogs} = {};
598 foreach my $i (1 .. $form->{rowcount}) {
599 if ($form->{type} eq "credit_note") {
600 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
601 $form->{shipped} = 1;
603 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
608 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
609 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
610 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
612 if ($form->{storno}) {
613 $form->{"qty_$i"} *= -1;
616 if ($form->{"id_$i"}) {
619 if (defined($baseunits{$form->{"id_$i"}})) {
620 $item_unit = $baseunits{$form->{"id_$i"}};
623 $query = qq|SELECT unit FROM parts WHERE id = ?|;
624 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
625 $baseunits{$form->{"id_$i"}} = $item_unit;
628 if (defined($all_units->{$item_unit}->{factor})
629 && ($all_units->{$item_unit}->{factor} ne '')
630 && ($all_units->{$item_unit}->{factor} != 0)) {
631 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
635 $baseqty = $form->{"qty_$i"} * $basefactor;
637 my ($allocated, $taxrate) = (0, 0);
641 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
643 # keep entered selling price
645 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
647 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
649 my $decimalplaces = ($dec > 2) ? $dec : 2;
651 # undo discount formatting
652 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
655 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
657 # round linetotal to 2 decimal places
658 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
659 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
661 if ($form->{taxincluded}) {
662 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
663 $form->{"sellprice_$i"} =
664 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
666 $taxamount = $linetotal * $taxrate;
669 $netamount += $linetotal;
671 if ($taxamount != 0) {
673 $form->{amount}{ $form->{id} }{$_} +=
674 $taxamount * $form->{"${_}_rate"} / $taxrate
675 } split(/ /, $form->{"taxaccounts_$i"});
678 # add amount to income, $form->{amount}{trans_id}{accno}
679 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
681 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
682 $linetotal = $form->round_amount($linetotal, 2);
684 # this is the difference from the inventory
685 $invoicediff += ($amount - $linetotal);
687 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
690 $lastincomeaccno = $form->{"income_accno_$i"};
692 # adjust and round sellprice
693 $form->{"sellprice_$i"} =
694 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
697 next if $payments_only;
699 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
701 if ($form->{"assembly_$i"}) {
702 # record assembly item as allocated
703 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
706 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
710 # Get pricegroup_id and save it. Unfortunately the interface
711 # also uses ID "0" for signalling that none is selected, but "0"
712 # must not be stored in the database. Therefore we cannot simply
714 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
716 $pricegroup_id = undef if !$pricegroup_id;
718 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
720 # save detail record in invoice table
722 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
723 sellprice, fxsellprice, discount, allocated, assemblyitem,
724 unit, deliverydate, project_id, serialnumber, pricegroup_id,
725 ordnumber, transdate, cusordnumber, base_qty, subtotal,
726 marge_percent, marge_total, lastcost,
727 price_factor_id, price_factor, marge_price_factor)
728 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
729 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
731 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
732 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
733 $form->{"sellprice_$i"}, $fxsellprice,
734 $form->{"discount_$i"}, $allocated, 'f',
735 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
736 $form->{"serialnumber_$i"}, $pricegroup_id,
737 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
738 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
739 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
740 $form->{"lastcost_$i"},
741 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
742 conv_i($form->{"marge_price_factor_$i"}));
743 do_query($form, $dbh, $query, @values);
745 CVar->save_custom_variables(module => 'IC',
746 sub_module => 'invoice',
747 trans_id => $invoice_id,
748 configs => $ic_cvar_configs,
750 name_prefix => 'ic_',
751 name_postfix => "_$i",
756 # total payments, don't move we need it here
757 for my $i (1 .. $form->{paidaccounts}) {
758 if ($form->{type} eq "credit_note") {
759 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
761 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
763 $form->{paid} += $form->{"paid_$i"};
764 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
767 my ($tax, $diff) = (0, 0);
769 $netamount = $form->round_amount($netamount, 2);
771 # figure out rounding errors for total amount vs netamount + taxes
772 if ($form->{taxincluded}) {
774 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
775 $diff += $amount - $netamount * $form->{exchangerate};
776 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
780 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
781 $tax += $form->{amount}{ $form->{id} }{$item};
782 $netamount -= $form->{amount}{ $form->{id} }{$item};
785 $invoicediff += $diff;
786 ######## this only applies to tax included
787 if ($lastincomeaccno) {
788 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
792 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
793 $diff = $amount - $netamount * $form->{exchangerate};
794 $netamount = $amount;
795 foreach my $item (split(/ /, $form->{taxaccounts})) {
796 $form->{amount}{ $form->{id} }{$item} =
797 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
800 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
803 $amount - $form->{amount}{ $form->{id} }{$item} *
804 $form->{exchangerate};
805 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
806 $tax += $form->{amount}{ $form->{id} }{$item};
810 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
812 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
815 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
817 # update exchangerate
818 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
819 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
820 $form->{exchangerate}, 0);
823 $project_id = conv_i($form->{"globalproject_id"});
825 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
827 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
828 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
829 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
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, tax_id, taxkey, project_id, chart_link)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
838 do_query($form, $dbh, $query, @values);
839 $form->{amount_cogs}{$trans_id}{$accno} = 0;
843 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
844 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
846 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
848 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
850 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
851 do_query($form, $dbh, $query, @values);
856 foreach my $trans_id (keys %{ $form->{amount} }) {
857 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
858 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
860 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
862 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
864 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
865 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
868 WHERE chart_id= (SELECT id
872 ORDER BY startdate DESC LIMIT 1),
873 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
874 (SELECT link FROM chart WHERE accno = ?))|;
875 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id), $accno);
876 do_query($form, $dbh, $query, @values);
877 $form->{amount}{$trans_id}{$accno} = 0;
881 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
882 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
884 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
886 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
887 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
890 WHERE chart_id= (SELECT id
894 ORDER BY startdate DESC LIMIT 1),
895 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
896 (SELECT link FROM chart WHERE accno = ?))|;
897 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id), $accno);
898 do_query($form, $dbh, $query, @values);
903 # deduct payment differences from diff
904 for my $i (1 .. $form->{paidaccounts}) {
905 if ($form->{"paid_$i"} != 0) {
907 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
908 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
912 # record payments and offsetting AR
913 if (!$form->{storno}) {
914 for my $i (1 .. $form->{paidaccounts}) {
916 if ($form->{"acc_trans_id_$i"}
918 && (SL::DB::Default->get->payments_changeable == 0)) {
922 next if ($form->{"paid_$i"} == 0);
924 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
925 $form->{"datepaid_$i"} = $form->{invdate}
926 unless ($form->{"datepaid_$i"});
927 $form->{datepaid} = $form->{"datepaid_$i"};
931 if ($form->{currency} eq $defaultcurrency) {
932 $form->{"exchangerate_$i"} = 1;
934 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
935 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
939 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
941 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
943 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
944 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
947 WHERE chart_id= (SELECT id
951 ORDER BY startdate DESC LIMIT 1),
952 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
953 (SELECT link FROM chart WHERE accno = ?))|;
954 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id, $form->{AR});
955 do_query($form, $dbh, $query, @values);
959 $form->{"paid_$i"} *= -1;
960 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
963 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
964 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
967 WHERE chart_id= (SELECT id
971 ORDER BY startdate DESC LIMIT 1),
972 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
973 (SELECT link FROM chart WHERE accno = ?))|;
974 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
975 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id, $accno);
976 do_query($form, $dbh, $query, @values);
978 # exchangerate difference
979 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
980 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
984 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
985 $form->{"exchangerate_$i"};
987 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
989 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
994 # update exchange rate
995 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
996 $form->update_exchangerate($dbh, $form->{currency},
997 $form->{"datepaid_$i"},
998 $form->{"exchangerate_$i"}, 0);
1002 } else { # if (!$form->{storno})
1003 $form->{marge_total} *= -1;
1006 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1008 # record exchange rate differences and gains/losses
1009 foreach my $accno (keys %{ $form->{fx} }) {
1010 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1011 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1012 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1015 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1016 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1019 WHERE chart_id= (SELECT id
1023 ORDER BY startdate DESC LIMIT 1),
1024 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
1025 (SELECT link FROM chart WHERE accno = ?))|;
1026 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id), $accno);
1027 do_query($form, $dbh, $query, @values);
1032 if ($payments_only) {
1033 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1034 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1036 $dbh->commit if !$provided_dbh;
1038 $main::lxdebug->leave_sub();
1042 $amount = $netamount + $tax;
1045 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1047 $query = qq|UPDATE ar set
1048 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1049 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1050 amount = ?, netamount = ?, paid = ?,
1051 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1052 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1053 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1054 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1055 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1056 cp_id = ?, marge_total = ?, marge_percent = ?,
1057 globalproject_id = ?, delivery_customer_id = ?,
1058 transaction_description = ?, delivery_vendor_id = ?,
1059 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1061 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1062 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1063 $amount, $netamount, $form->{"paid"},
1064 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1065 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1066 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1067 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1068 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1069 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1070 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1071 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1072 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1073 conv_i($form->{"id"}));
1074 do_query($form, $dbh, $query, @values);
1077 if ($form->{storno}) {
1080 paid = paid + amount,
1082 intnotes = ? || intnotes
1084 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1085 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1089 $form->{name} = $form->{customer};
1090 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1092 if (!$form->{shipto_id}) {
1093 $form->add_shipto($dbh, $form->{id}, "AR");
1096 # save printed, emailed and queued
1097 $form->save_status($dbh);
1099 Common::webdav_folder($form);
1101 # Link this record to the records it was created from.
1102 RecordLinks->create_links('dbh' => $dbh,
1104 'from_table' => 'oe',
1105 'from_ids' => $form->{convert_from_oe_ids},
1107 'to_id' => $form->{id},
1109 delete $form->{convert_from_oe_ids};
1111 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1113 if (scalar @convert_from_do_ids) {
1114 DO->close_orders('dbh' => $dbh,
1115 'ids' => \@convert_from_do_ids);
1117 RecordLinks->create_links('dbh' => $dbh,
1119 'from_table' => 'delivery_orders',
1120 'from_ids' => \@convert_from_do_ids,
1122 'to_id' => $form->{id},
1125 delete $form->{convert_from_do_ids};
1127 ARAP->close_orders_if_billed('dbh' => $dbh,
1128 'arap_id' => $form->{id},
1131 # safety check datev export
1132 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1133 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1134 $transdate ||= DateTime->today;
1136 my $datev = SL::DATEV->new(
1137 exporttype => DATEV_ET_BUCHUNGEN,
1138 format => DATEV_FORMAT_KNE,
1146 if ($datev->errors) {
1148 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1153 $dbh->commit if !$provided_dbh;
1155 $main::lxdebug->leave_sub();
1160 sub _delete_payments {
1161 $main::lxdebug->enter_sub();
1163 my ($self, $form, $dbh) = @_;
1165 my @delete_acc_trans_ids;
1167 # Delete old payment entries from acc_trans.
1169 qq|SELECT acc_trans_id
1171 WHERE (trans_id = ?) AND fx_transaction
1175 SELECT at.acc_trans_id
1177 LEFT JOIN chart c ON (at.chart_id = c.id)
1178 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1179 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1182 qq|SELECT at.acc_trans_id
1184 LEFT JOIN chart c ON (at.chart_id = c.id)
1185 WHERE (trans_id = ?)
1186 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1187 ORDER BY at.acc_trans_id
1189 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1191 if (@delete_acc_trans_ids) {
1192 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1193 do_query($form, $dbh, $query);
1196 $main::lxdebug->leave_sub();
1200 $main::lxdebug->enter_sub();
1202 my ($self, $myconfig, $form, $locale) = @_;
1204 # connect to database, turn off autocommit
1205 my $dbh = $form->get_standard_dbh;
1208 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1210 $old_form = save_form();
1212 # Delete all entries in acc_trans from prior payments.
1213 if (SL::DB::Default->get->payments_changeable != 0) {
1214 $self->_delete_payments($form, $dbh);
1217 # Save the new payments the user made before cleaning up $form.
1218 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 };
1220 # Clean up $form so that old content won't tamper the results.
1221 %keep_vars = map { $_, 1 } qw(login password id);
1222 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1224 # Retrieve the invoice from the database.
1225 $self->retrieve_invoice($myconfig, $form);
1227 # Set up the content of $form in the way that IS::post_invoice() expects.
1228 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1230 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1231 $item = $form->{invoice_details}->[$row - 1];
1233 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1235 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1238 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1240 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1242 # Restore the payment options from the user input.
1243 map { $form->{$_} = $payments{$_} } keys %payments;
1245 # Get the AR accno (which is normally done by Form::create_links()).
1249 LEFT JOIN chart c ON (at.chart_id = c.id)
1250 WHERE (trans_id = ?)
1251 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1252 ORDER BY at.acc_trans_id
1255 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1257 # Post the new payments.
1258 $self->post_invoice($myconfig, $form, $dbh, 1);
1260 restore_form($old_form);
1262 my $rc = $dbh->commit();
1264 $main::lxdebug->leave_sub();
1269 sub process_assembly {
1270 $main::lxdebug->enter_sub();
1272 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1275 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1276 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1278 JOIN parts p ON (a.parts_id = p.id)
1280 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1282 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1286 $ref->{inventory_accno_id} *= 1;
1287 $ref->{expense_accno_id} *= 1;
1289 # multiply by number of assemblies
1290 $ref->{qty} *= $totalqty;
1292 if ($ref->{assembly}) {
1293 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1296 if ($ref->{inventory_accno_id}) {
1297 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1301 # save detail record for individual assembly item in invoice table
1303 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1304 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1305 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1306 do_query($form, $dbh, $query, @values);
1312 $main::lxdebug->leave_sub();
1316 $main::lxdebug->enter_sub();
1318 # adjust allocated in table invoice according to FIFO princicple
1319 # for a certain part with part_id $id
1321 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1325 $form->{taxzone_id} *=1;
1326 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1327 my $taxzone_id = $form->{"taxzone_id"} * 1;
1329 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1330 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1331 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1332 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1333 FROM invoice i, parts p
1334 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1335 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1336 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1337 WHERE (i.parts_id = p.id)
1338 AND (i.parts_id = ?)
1339 AND ((i.base_qty + i.allocated) < 0)
1341 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1346 # all invoice entries of an example part:
1348 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1349 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1350 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1351 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1352 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1353 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1354 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1356 # 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
1357 # and all parts have been allocated
1359 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1360 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1362 # in this example there are still 4 unsold articles
1365 # search all invoice entries for the part in question, adjusting "allocated"
1366 # until the total number of sold parts has been reached
1368 # ORDER BY trans_id ensures FIFO
1371 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1372 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1376 # update allocated in invoice
1377 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1379 # total expenses and inventory
1380 # sellprice is the cost of the item
1381 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1383 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1384 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1385 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1387 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1388 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1389 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1391 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1392 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1398 last if (($totalqty -= $qty) <= 0);
1403 $main::lxdebug->leave_sub();
1408 sub reverse_invoice {
1409 $main::lxdebug->enter_sub();
1411 my ($dbh, $form) = @_;
1413 # reverse inventory items
1415 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1417 JOIN parts p ON (i.parts_id = p.id)
1418 WHERE i.trans_id = ?|;
1419 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1421 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1423 if ($ref->{inventory_accno_id}) {
1424 # de-allocated purchases
1426 qq|SELECT i.id, i.trans_id, i.allocated
1428 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1429 ORDER BY i.trans_id DESC|;
1430 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1432 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1433 my $qty = $ref->{qty};
1434 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1435 $qty = $inhref->{allocated};
1439 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1441 last if (($ref->{qty} -= $qty) <= 0);
1450 my @values = (conv_i($form->{id}));
1451 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1452 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1453 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1455 $main::lxdebug->leave_sub();
1458 sub delete_invoice {
1459 $main::lxdebug->enter_sub();
1461 my ($self, $myconfig, $form) = @_;
1463 # connect to database
1464 my $dbh = $form->get_standard_dbh;
1467 &reverse_invoice($dbh, $form);
1469 my @values = (conv_i($form->{id}));
1471 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1472 # zurückgesetzt werden. Vgl:
1473 # id | storno | storno_id | paid | amount
1474 #----+--------+-----------+---------+-----------
1475 # 18 | f | | 0.00000 | 119.00000
1477 # 18 | t | | 119.00000 | 119.00000
1479 if($form->{storno}){
1480 # storno_id auslesen und korrigieren
1481 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1482 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1485 # delete spool files
1486 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1489 qq|DELETE FROM status WHERE trans_id = ?|,
1490 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1491 qq|DELETE FROM ar WHERE id = ?|,
1494 map { do_query($form, $dbh, $_, @values) } @queries;
1496 my $rc = $dbh->commit;
1499 my $spool = $::lx_office_conf{paths}->{spool};
1500 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1503 $main::lxdebug->leave_sub();
1508 sub retrieve_invoice {
1509 $main::lxdebug->enter_sub();
1511 my ($self, $myconfig, $form) = @_;
1513 # connect to database
1514 my $dbh = $form->get_standard_dbh;
1516 my ($sth, $ref, $query);
1518 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1522 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1523 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1524 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1525 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1526 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1527 d.curr AS currencies
1531 $ref = selectfirst_hashref_query($form, $dbh, $query);
1532 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1535 my $id = conv_i($form->{id});
1538 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1542 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1543 a.orddate, a.quodate, a.globalproject_id,
1544 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1545 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1546 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1547 a.employee_id, a.salesman_id, a.payment_id,
1548 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1549 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1550 a.marge_total, a.marge_percent, a.direct_debit,
1553 LEFT JOIN employee e ON (e.id = a.employee_id)
1555 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1556 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1558 # remove any trailing whitespace
1559 $form->{currency} =~ s/\s*$//;
1561 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1564 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1565 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1567 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1569 foreach my $vc (qw(customer vendor)) {
1570 next if !$form->{"delivery_${vc}_id"};
1571 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1574 # get printed, emailed
1575 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1576 $sth = prepare_execute_query($form, $dbh, $query, $id);
1578 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1579 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1580 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1581 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1584 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1586 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1587 : $form->{invdate} ? $dbh->quote($form->{invdate})
1591 my $taxzone_id = $form->{taxzone_id} *= 1;
1592 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1594 # retrieve individual items
1597 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1598 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1599 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1602 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1603 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1604 i.price_factor_id, i.price_factor, i.marge_price_factor,
1605 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1606 pr.projectnumber, pg.partsgroup, prg.pricegroup
1609 LEFT JOIN parts p ON (i.parts_id = p.id)
1610 LEFT JOIN project pr ON (i.project_id = pr.id)
1611 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1612 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1614 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1615 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1616 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1618 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1620 $sth = prepare_execute_query($form, $dbh, $query, $id);
1622 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1623 # Retrieve custom variables.
1624 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1626 sub_module => 'invoice',
1627 trans_id => $ref->{invoice_id},
1629 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1630 delete $ref->{invoice_id};
1632 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1633 delete($ref->{"part_inventory_accno_id"});
1635 foreach my $type (qw(inventory income expense)) {
1636 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1637 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1638 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1642 # get tax rates and description
1643 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1645 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1646 LEFT JOIN chart c ON (c.id = t.chart_id)
1648 (SELECT tk.tax_id FROM taxkeys tk
1649 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1650 AND startdate <= date($transdate)
1651 ORDER BY startdate DESC LIMIT 1)
1653 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1654 $ref->{taxaccounts} = "";
1656 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1658 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1662 $ref->{taxaccounts} .= "$ptr->{accno} ";
1664 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1665 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1666 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1667 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1668 $form->{taxaccounts} .= "$ptr->{accno} ";
1673 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1675 chop $ref->{taxaccounts};
1676 push @{ $form->{invoice_details} }, $ref;
1681 Common::webdav_folder($form);
1684 my $rc = $dbh->commit;
1686 $main::lxdebug->leave_sub();
1692 $main::lxdebug->enter_sub();
1694 my ($self, $myconfig, $form) = @_;
1696 # connect to database
1697 my $dbh = $form->get_standard_dbh;
1699 my $dateformat = $myconfig->{dateformat};
1700 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1702 my (@values, $duedate, $ref, $query);
1704 if ($form->{invdate}) {
1705 $duedate = "to_date(?, '$dateformat')";
1706 push @values, $form->{invdate};
1708 $duedate = "current_date";
1711 my $cid = conv_i($form->{customer_id});
1714 if ($form->{payment_id}) {
1715 $payment_id = "(pt.id = ?) OR";
1716 push @values, conv_i($form->{payment_id});
1722 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1723 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1724 c.street, c.zipcode, c.city, c.country,
1725 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1726 c.taxincluded_checked, c.direct_debit,
1727 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1728 b.discount AS tradediscount, b.description AS business
1730 LEFT JOIN business b ON (b.id = c.business_id)
1731 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1734 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1736 delete $ref->{salesman_id} if !$ref->{salesman_id};
1738 map { $form->{$_} = $ref->{$_} } keys %$ref;
1740 # remove any trailing whitespace
1741 $form->{curr} =~ s/\s*$//;
1743 # use customer currency if not empty
1744 $form->{currency} = $form->{curr} if $form->{curr};
1747 qq|SELECT sum(amount - paid) AS dunning_amount
1749 WHERE (paid < amount)
1750 AND (customer_id = ?)
1751 AND (dunning_config_id IS NOT NULL)|;
1752 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1753 map { $form->{$_} = $ref->{$_} } keys %$ref;
1756 qq|SELECT dnn.dunning_description AS max_dunning_level
1757 FROM dunning_config dnn
1758 WHERE id IN (SELECT dunning_config_id
1760 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1761 ORDER BY dunning_level DESC LIMIT 1|;
1762 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1763 map { $form->{$_} = $ref->{$_} } keys %$ref;
1765 $form->{creditremaining} = $form->{creditlimit};
1766 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1767 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1768 $form->{creditremaining} -= $value;
1772 (SELECT e.buy FROM exchangerate e
1773 WHERE e.curr = o.curr
1774 AND e.transdate = o.transdate)
1776 WHERE o.customer_id = ?
1777 AND o.quotation = '0'
1778 AND o.closed = '0'|;
1779 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1781 while (my ($amount, $exch) = $sth->fetchrow_array) {
1782 $exch = 1 unless $exch;
1783 $form->{creditremaining} -= $amount * $exch;
1787 # get shipto if we did not converted an order or invoice
1788 if (!$form->{shipto}) {
1789 map { delete $form->{$_} }
1790 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1791 shiptostreet shiptozipcode shiptocity shiptocountry
1792 shiptocontact shiptophone shiptofax shiptoemail);
1794 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1795 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1797 map { $form->{$_} = $ref->{$_} } keys %$ref;
1800 # setup last accounts used for this customer
1801 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1803 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1805 JOIN acc_trans ac ON (ac.chart_id = c.id)
1806 JOIN ar a ON (a.id = ac.trans_id)
1807 WHERE a.customer_id = ?
1808 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1809 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1810 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1813 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1814 if ($ref->{category} eq 'I') {
1816 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1818 if ($form->{initial_transdate}) {
1820 qq|SELECT tk.tax_id, t.rate
1822 LEFT JOIN tax t ON tk.tax_id = t.id
1823 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1824 ORDER BY tk.startdate DESC
1826 my ($tax_id, $rate) =
1827 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1828 $form->{initial_transdate});
1829 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1832 if ($ref->{category} eq 'A') {
1833 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1837 $form->{rowcount} = $i if ($i && !$form->{type});
1840 $main::lxdebug->leave_sub();
1844 $main::lxdebug->enter_sub();
1846 my ($self, $myconfig, $form) = @_;
1848 # connect to database
1849 my $dbh = $form->get_standard_dbh;
1851 my $i = $form->{rowcount};
1853 my $where = qq|NOT p.obsolete = '1'|;
1856 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1857 my ($table, $field) = split m/\./, $column;
1858 next if !$form->{"${field}_${i}"};
1859 $where .= qq| AND lower(${column}) ILIKE ?|;
1860 push @values, '%' . $form->{"${field}_${i}"} . '%';
1863 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1864 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1865 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1866 push @values, $form->{"partnumber_$i"};
1869 # Search for part ID overrides all other criteria.
1870 if ($form->{"id_${i}"}) {
1871 $where = qq|p.id = ?|;
1872 @values = ($form->{"id_${i}"});
1875 if ($form->{"description_$i"}) {
1876 $where .= qq| ORDER BY p.description|;
1878 $where .= qq| ORDER BY p.partnumber|;
1882 if ($form->{type} eq "invoice") {
1884 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1885 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1889 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1893 my $taxzone_id = $form->{taxzone_id} * 1;
1894 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1898 p.id, p.partnumber, p.description, p.sellprice,
1899 p.listprice, p.inventory_accno_id, p.lastcost,
1901 c1.accno AS inventory_accno,
1902 c1.new_chart_id AS inventory_new_chart,
1903 date($transdate) - c1.valid_from AS inventory_valid,
1905 c2.accno AS income_accno,
1906 c2.new_chart_id AS income_new_chart,
1907 date($transdate) - c2.valid_from AS income_valid,
1909 c3.accno AS expense_accno,
1910 c3.new_chart_id AS expense_new_chart,
1911 date($transdate) - c3.valid_from AS expense_valid,
1913 p.unit, p.assembly, p.bin, p.onhand,
1914 p.notes AS partnotes, p.notes AS longdescription,
1915 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1918 pfac.factor AS price_factor,
1923 LEFT JOIN chart c1 ON
1924 ((SELECT inventory_accno_id
1925 FROM buchungsgruppen
1926 WHERE id = p.buchungsgruppen_id) = c1.id)
1927 LEFT JOIN chart c2 ON
1928 ((SELECT income_accno_id_${taxzone_id}
1929 FROM buchungsgruppen
1930 WHERE id = p.buchungsgruppen_id) = c2.id)
1931 LEFT JOIN chart c3 ON
1932 ((SELECT expense_accno_id_${taxzone_id}
1933 FROM buchungsgruppen
1934 WHERE id = p.buchungsgruppen_id) = c3.id)
1935 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1936 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1938 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1940 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1942 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1943 [ qq|SELECT tr.translation, tr.longdescription
1945 WHERE tr.language_id IN
1948 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1951 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1953 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1955 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1956 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1957 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1958 if (!$ref->{inventory_accno_id}) {
1959 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1961 delete($ref->{inventory_accno_id});
1963 foreach my $type (qw(inventory income expense)) {
1964 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1966 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1969 ($ref->{"${type}_accno"},
1970 $ref->{"${type}_new_chart"},
1971 $ref->{"${type}_valid"})
1972 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1976 if ($form->{payment_id} eq "") {
1977 $form->{payment_id} = $form->{part_payment_id};
1980 # get tax rates and description
1981 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1983 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1985 LEFT JOIN chart c ON (c.id = t.chart_id)
1989 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1991 ORDER BY startdate DESC
1994 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1995 my $stw = $dbh->prepare($query);
1996 $stw->execute(@values) || $form->dberror($query);
1998 $ref->{taxaccounts} = "";
2000 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2002 # if ($customertax{$ref->{accno}})
2003 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2007 $ref->{taxaccounts} .= "$ptr->{accno} ";
2009 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2010 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2011 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2012 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2013 $form->{taxaccounts} .= "$ptr->{accno} ";
2019 chop $ref->{taxaccounts};
2021 if ($form->{language_id}) {
2022 for my $spec (@translation_queries) {
2023 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2024 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2025 next unless $translation;
2026 $ref->{description} = $translation;
2027 $ref->{longdescription} = $longdescription;
2032 $ref->{onhand} *= 1;
2034 push @{ $form->{item_list} }, $ref;
2037 $_->[1]->finish for @translation_queries;
2039 foreach my $item (@{ $form->{item_list} }) {
2040 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2041 trans_id => $item->{id},
2045 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2048 $main::lxdebug->leave_sub();
2051 ##########################
2052 # get pricegroups from database
2053 # build up selected pricegroup
2054 # if an exchange rate - change price
2057 sub get_pricegroups_for_parts {
2059 $main::lxdebug->enter_sub();
2061 my ($self, $myconfig, $form) = @_;
2063 my $dbh = $form->get_standard_dbh;
2065 $form->{"PRICES"} = {};
2069 my $all_units = AM->retrieve_units($myconfig, $form);
2070 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2071 $form->{"PRICES"}{$i} = [];
2073 $id = $form->{"id_$i"};
2075 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2076 $id = $form->{"new_id_$i"};
2079 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2081 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2083 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2084 my $sellprice = $form->{"sellprice_$i"};
2085 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2086 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2087 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2089 my $price_new = $form->{"price_new_$i"};
2090 my $price_old = $form->{"price_old_$i"};
2092 if (!$form->{"unit_old_$i"}) {
2093 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2094 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2095 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2096 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2099 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2100 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2101 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2103 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2104 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2105 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2106 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2107 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2108 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2109 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2114 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2115 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2116 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2117 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2118 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2122 if (!$form->{"basefactor_$i"}) {
2123 $form->{"basefactor_$i"} = 1;
2129 sellprice AS default_sellprice,
2132 'selected' AS selected
2138 parts.sellprice AS default_sellprice,
2139 pricegroup.pricegroup,
2143 LEFT JOIN parts ON parts.id = parts_id
2144 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2146 ORDER BY pricegroup|;
2147 my @values = (conv_i($id), conv_i($id));
2148 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2150 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2152 $pkr->{selected} = '';
2154 # if there is an exchange rate change price
2155 if (($form->{exchangerate} * 1) != 0) {
2156 $pkr->{price} /= $form->{exchangerate};
2159 $pkr->{price} *= $form->{"basefactor_$i"};
2160 $pkr->{price} *= $basefactor;
2161 $pkr->{price_ufmt} = $pkr->{price};
2162 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2164 if (!defined $selectedpricegroup_id) {
2165 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2166 # Case A: open old invoice, no pricegroup selected
2167 # Case B: add new article to invoice, no pricegroup selected
2169 # to distinguish case A and B the variable pricegroup_id_$i is used
2170 # for new articles this variable isn't defined, for loaded articles it is
2171 # sellprice can't be used, as it already has 0,00 set
2173 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2175 $pkr->{selected} = ' selected';
2176 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2177 and not defined $form->{"pricegroup_id_$i"}
2178 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2179 # for the case where pricegroup prices haven't been set
2181 # Case B: use default pricegroup of customer
2183 $pkr->{selected} = ' selected'; # unless $form->{selected};
2184 # no customer pricesgroup set
2185 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2187 $pkr->{price} = $form->{"sellprice_$i"};
2191 # this sub should not set anything and only return. --sschoeling, 20090506
2192 # is this correct? put in again... -- grichardson 20110119
2193 $form->{"sellprice_$i"} = $pkr->{price};
2196 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2197 $pkr->{price} = $form->{"sellprice_$i"};
2198 $pkr->{selected} = ' selected';
2202 # existing article: pricegroup or price changed
2203 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2204 if ($selectedpricegroup_id ne $pricegroup_old) {
2205 # pricegroup has changed
2206 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2207 $pkr->{selected} = ' selected';
2209 } elsif ( ($form->parse_amount($myconfig, $price_new)
2210 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2211 and ($price_new ne 0) and defined $price_new) {
2212 # sellprice has changed
2213 # when loading existing invoices $price_new is NULL
2214 if ($pkr->{pricegroup_id} == 0) {
2215 $pkr->{price} = $form->{"sellprice_$i"};
2216 $pkr->{selected} = ' selected';
2218 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2219 # neither sellprice nor pricegroup changed
2220 $pkr->{selected} = ' selected';
2221 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2222 # $pkr->{price} = $form->{"sellprice_$i"};
2224 $pkr->{price} = $form->{"sellprice_$i"};
2228 push @{ $form->{PRICES}{$i} }, $pkr;
2231 $form->{"basefactor_$i"} *= $basefactor;
2238 $main::lxdebug->leave_sub();
2242 $main::lxdebug->enter_sub();
2244 my ($self, $myconfig, $form, $table) = @_;
2246 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2248 # make sure there's no funny stuff in $table
2249 # ToDO: die when this happens and throw an error
2250 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2252 my $dbh = $form->get_standard_dbh;
2254 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2255 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2257 $main::lxdebug->leave_sub();
2263 $main::lxdebug->enter_sub();
2265 my ($self, $myconfig, $form, $table, $id) = @_;
2267 $main::lxdebug->leave_sub() and return 0 unless ($id);
2269 # make sure there's no funny stuff in $table
2270 # ToDO: die when this happens and throw an error
2271 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2273 my $dbh = $form->get_standard_dbh;
2275 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2276 my ($result) = selectrow_query($form, $dbh, $query, $id);
2278 $main::lxdebug->leave_sub();
2283 sub get_standard_accno_current_assets {
2284 $main::lxdebug->enter_sub();
2286 my ($self, $myconfig, $form) = @_;
2288 my $dbh = $form->get_standard_dbh;
2290 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2291 my ($result) = selectrow_query($form, $dbh, $query);
2293 $main::lxdebug->leave_sub();