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(myconfig => $myconfig, form => $form);
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"});
824 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
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),
875 WHERE chart_id= (SELECT id
879 ORDER BY startdate DESC LIMIT 1),
881 (SELECT link FROM chart WHERE accno = ?))|;
882 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
883 do_query($form, $dbh, $query, @values);
884 $form->{amount}{$trans_id}{$accno} = 0;
888 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
889 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
891 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
893 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
894 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
897 WHERE chart_id= (SELECT id
901 ORDER BY startdate DESC LIMIT 1),
904 WHERE chart_id= (SELECT id
908 ORDER BY startdate DESC LIMIT 1),
910 (SELECT link FROM chart WHERE accno = ?))|;
911 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
912 do_query($form, $dbh, $query, @values);
917 # deduct payment differences from diff
918 for my $i (1 .. $form->{paidaccounts}) {
919 if ($form->{"paid_$i"} != 0) {
921 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
922 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
926 # record payments and offsetting AR
927 if (!$form->{storno}) {
928 for my $i (1 .. $form->{paidaccounts}) {
930 if ($form->{"acc_trans_id_$i"}
932 && (SL::DB::Default->get->payments_changeable == 0)) {
936 next if ($form->{"paid_$i"} == 0);
938 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
939 $form->{"datepaid_$i"} = $form->{invdate}
940 unless ($form->{"datepaid_$i"});
941 $form->{datepaid} = $form->{"datepaid_$i"};
945 if ($form->{currency} eq $defaultcurrency) {
946 $form->{"exchangerate_$i"} = 1;
948 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
949 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
953 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
955 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
957 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
958 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
961 WHERE chart_id= (SELECT id
965 ORDER BY startdate DESC LIMIT 1),
968 WHERE chart_id= (SELECT id
972 ORDER BY startdate DESC LIMIT 1),
974 (SELECT link FROM chart WHERE accno = ?))|;
975 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
976 do_query($form, $dbh, $query, @values);
980 $form->{"paid_$i"} *= -1;
981 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
984 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
985 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
988 WHERE chart_id= (SELECT id
992 ORDER BY startdate DESC LIMIT 1),
995 WHERE chart_id= (SELECT id
999 ORDER BY startdate DESC LIMIT 1),
1001 (SELECT link FROM chart WHERE accno = ?))|;
1002 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1003 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1004 do_query($form, $dbh, $query, @values);
1006 # exchangerate difference
1007 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1008 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1012 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1013 $form->{"exchangerate_$i"};
1015 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1017 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1022 # update exchange rate
1023 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1024 $form->update_exchangerate($dbh, $form->{currency},
1025 $form->{"datepaid_$i"},
1026 $form->{"exchangerate_$i"}, 0);
1030 } else { # if (!$form->{storno})
1031 $form->{marge_total} *= -1;
1034 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1036 # record exchange rate differences and gains/losses
1037 foreach my $accno (keys %{ $form->{fx} }) {
1038 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1039 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1040 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1043 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1044 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1047 WHERE chart_id= (SELECT id
1051 ORDER BY startdate DESC LIMIT 1),
1054 WHERE chart_id= (SELECT id
1058 ORDER BY startdate DESC LIMIT 1),
1060 (SELECT link FROM chart WHERE accno = ?))|;
1061 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
1062 do_query($form, $dbh, $query, @values);
1067 if ($payments_only) {
1068 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1069 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1071 $dbh->commit if !$provided_dbh;
1073 $main::lxdebug->leave_sub();
1077 $amount = $netamount + $tax;
1080 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1082 $query = qq|UPDATE ar set
1083 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1084 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1085 amount = ?, netamount = ?, paid = ?,
1086 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1087 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1088 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1089 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1090 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1091 cp_id = ?, marge_total = ?, marge_percent = ?,
1092 globalproject_id = ?, delivery_customer_id = ?,
1093 transaction_description = ?, delivery_vendor_id = ?,
1094 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1096 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1097 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1098 $amount, $netamount, $form->{"paid"},
1099 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1100 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1101 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1102 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1103 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1104 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1105 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1106 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1107 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1108 conv_i($form->{"id"}));
1109 do_query($form, $dbh, $query, @values);
1112 if ($form->{storno}) {
1115 paid = paid + amount,
1117 intnotes = ? || intnotes
1119 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1120 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1124 $form->{name} = $form->{customer};
1125 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1127 if (!$form->{shipto_id}) {
1128 $form->add_shipto($dbh, $form->{id}, "AR");
1131 # save printed, emailed and queued
1132 $form->save_status($dbh);
1134 Common::webdav_folder($form);
1136 # Link this record to the records it was created from.
1137 RecordLinks->create_links('dbh' => $dbh,
1139 'from_table' => 'oe',
1140 'from_ids' => $form->{convert_from_oe_ids},
1142 'to_id' => $form->{id},
1144 delete $form->{convert_from_oe_ids};
1146 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1148 if (scalar @convert_from_do_ids) {
1149 DO->close_orders('dbh' => $dbh,
1150 'ids' => \@convert_from_do_ids);
1152 RecordLinks->create_links('dbh' => $dbh,
1154 'from_table' => 'delivery_orders',
1155 'from_ids' => \@convert_from_do_ids,
1157 'to_id' => $form->{id},
1160 delete $form->{convert_from_do_ids};
1162 ARAP->close_orders_if_billed('dbh' => $dbh,
1163 'arap_id' => $form->{id},
1166 # safety check datev export
1167 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1168 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1169 $transdate ||= DateTime->today;
1171 my $datev = SL::DATEV->new(
1172 exporttype => DATEV_ET_BUCHUNGEN,
1173 format => DATEV_FORMAT_KNE,
1177 trans_id => $form->{id},
1182 if ($datev->errors) {
1184 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1189 $dbh->commit if !$provided_dbh;
1191 $main::lxdebug->leave_sub();
1196 sub _delete_payments {
1197 $main::lxdebug->enter_sub();
1199 my ($self, $form, $dbh) = @_;
1201 my @delete_acc_trans_ids;
1203 # Delete old payment entries from acc_trans.
1205 qq|SELECT acc_trans_id
1207 WHERE (trans_id = ?) AND fx_transaction
1211 SELECT at.acc_trans_id
1213 LEFT JOIN chart c ON (at.chart_id = c.id)
1214 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1215 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1218 qq|SELECT at.acc_trans_id
1220 LEFT JOIN chart c ON (at.chart_id = c.id)
1221 WHERE (trans_id = ?)
1222 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1223 ORDER BY at.acc_trans_id
1225 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1227 if (@delete_acc_trans_ids) {
1228 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1229 do_query($form, $dbh, $query);
1232 $main::lxdebug->leave_sub();
1236 $main::lxdebug->enter_sub();
1238 my ($self, $myconfig, $form, $locale) = @_;
1240 # connect to database, turn off autocommit
1241 my $dbh = $form->get_standard_dbh;
1244 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1246 $old_form = save_form();
1248 # Delete all entries in acc_trans from prior payments.
1249 if (SL::DB::Default->get->payments_changeable != 0) {
1250 $self->_delete_payments($form, $dbh);
1253 # Save the new payments the user made before cleaning up $form.
1254 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 };
1256 # Clean up $form so that old content won't tamper the results.
1257 %keep_vars = map { $_, 1 } qw(login password id);
1258 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1260 # Retrieve the invoice from the database.
1261 $self->retrieve_invoice($myconfig, $form);
1263 # Set up the content of $form in the way that IS::post_invoice() expects.
1264 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1266 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1267 $item = $form->{invoice_details}->[$row - 1];
1269 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1271 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1274 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1276 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1278 # Restore the payment options from the user input.
1279 map { $form->{$_} = $payments{$_} } keys %payments;
1281 # Get the AR accno (which is normally done by Form::create_links()).
1285 LEFT JOIN chart c ON (at.chart_id = c.id)
1286 WHERE (trans_id = ?)
1287 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1288 ORDER BY at.acc_trans_id
1291 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1293 # Post the new payments.
1294 $self->post_invoice($myconfig, $form, $dbh, 1);
1296 restore_form($old_form);
1298 my $rc = $dbh->commit();
1300 $main::lxdebug->leave_sub();
1305 sub process_assembly {
1306 $main::lxdebug->enter_sub();
1308 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1311 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1312 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1314 JOIN parts p ON (a.parts_id = p.id)
1316 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1318 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1322 $ref->{inventory_accno_id} *= 1;
1323 $ref->{expense_accno_id} *= 1;
1325 # multiply by number of assemblies
1326 $ref->{qty} *= $totalqty;
1328 if ($ref->{assembly}) {
1329 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1332 if ($ref->{inventory_accno_id}) {
1333 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1337 # save detail record for individual assembly item in invoice table
1339 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1340 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1341 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1342 do_query($form, $dbh, $query, @values);
1348 $main::lxdebug->leave_sub();
1352 $main::lxdebug->enter_sub();
1354 # adjust allocated in table invoice according to FIFO princicple
1355 # for a certain part with part_id $id
1357 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1361 $form->{taxzone_id} *=1;
1362 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1363 my $taxzone_id = $form->{"taxzone_id"} * 1;
1365 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1366 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1367 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1368 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1369 FROM invoice i, parts p
1370 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1371 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1372 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1373 WHERE (i.parts_id = p.id)
1374 AND (i.parts_id = ?)
1375 AND ((i.base_qty + i.allocated) < 0)
1377 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1382 # all invoice entries of an example part:
1384 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1385 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1386 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1387 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1388 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1389 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1390 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1392 # 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
1393 # and all parts have been allocated
1395 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1396 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1398 # in this example there are still 4 unsold articles
1401 # search all invoice entries for the part in question, adjusting "allocated"
1402 # until the total number of sold parts has been reached
1404 # ORDER BY trans_id ensures FIFO
1407 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1408 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1412 # update allocated in invoice
1413 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1415 # total expenses and inventory
1416 # sellprice is the cost of the item
1417 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1419 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1420 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1421 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1423 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1424 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1425 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1427 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1428 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1434 last if (($totalqty -= $qty) <= 0);
1439 $main::lxdebug->leave_sub();
1444 sub reverse_invoice {
1445 $main::lxdebug->enter_sub();
1447 my ($dbh, $form) = @_;
1449 # reverse inventory items
1451 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1453 JOIN parts p ON (i.parts_id = p.id)
1454 WHERE i.trans_id = ?|;
1455 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1457 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1459 if ($ref->{inventory_accno_id}) {
1460 # de-allocated purchases
1462 qq|SELECT i.id, i.trans_id, i.allocated
1464 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1465 ORDER BY i.trans_id DESC|;
1466 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1468 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1469 my $qty = $ref->{qty};
1470 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1471 $qty = $inhref->{allocated};
1475 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1477 last if (($ref->{qty} -= $qty) <= 0);
1486 my @values = (conv_i($form->{id}));
1487 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1488 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1489 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1491 $main::lxdebug->leave_sub();
1494 sub delete_invoice {
1495 $main::lxdebug->enter_sub();
1497 my ($self, $myconfig, $form) = @_;
1499 # connect to database
1500 my $dbh = $form->get_standard_dbh;
1503 &reverse_invoice($dbh, $form);
1505 my @values = (conv_i($form->{id}));
1507 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1508 # zurückgesetzt werden. Vgl:
1509 # id | storno | storno_id | paid | amount
1510 #----+--------+-----------+---------+-----------
1511 # 18 | f | | 0.00000 | 119.00000
1513 # 18 | t | | 119.00000 | 119.00000
1515 if($form->{storno}){
1516 # storno_id auslesen und korrigieren
1517 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1518 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1521 # delete spool files
1522 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1525 qq|DELETE FROM status WHERE trans_id = ?|,
1526 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1527 qq|DELETE FROM ar WHERE id = ?|,
1530 map { do_query($form, $dbh, $_, @values) } @queries;
1532 my $rc = $dbh->commit;
1535 my $spool = $::lx_office_conf{paths}->{spool};
1536 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1539 $main::lxdebug->leave_sub();
1544 sub retrieve_invoice {
1545 $main::lxdebug->enter_sub();
1547 my ($self, $myconfig, $form) = @_;
1549 # connect to database
1550 my $dbh = $form->get_standard_dbh;
1552 my ($sth, $ref, $query);
1554 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1558 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1559 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1560 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1561 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1562 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1563 d.curr AS currencies
1567 $ref = selectfirst_hashref_query($form, $dbh, $query);
1568 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1571 my $id = conv_i($form->{id});
1574 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1578 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1579 a.orddate, a.quodate, a.globalproject_id,
1580 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1581 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1582 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1583 a.employee_id, a.salesman_id, a.payment_id,
1584 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1585 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1586 a.marge_total, a.marge_percent, a.direct_debit,
1589 LEFT JOIN employee e ON (e.id = a.employee_id)
1591 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1592 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1594 # remove any trailing whitespace
1595 $form->{currency} =~ s/\s*$//;
1597 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1600 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1601 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1603 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1605 foreach my $vc (qw(customer vendor)) {
1606 next if !$form->{"delivery_${vc}_id"};
1607 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1610 # get printed, emailed
1611 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1612 $sth = prepare_execute_query($form, $dbh, $query, $id);
1614 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1615 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1616 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1617 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1620 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1622 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1623 : $form->{invdate} ? $dbh->quote($form->{invdate})
1627 my $taxzone_id = $form->{taxzone_id} *= 1;
1628 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1630 # retrieve individual items
1633 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1634 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1635 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1638 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1639 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1640 i.price_factor_id, i.price_factor, i.marge_price_factor,
1641 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1642 pr.projectnumber, pg.partsgroup, prg.pricegroup
1645 LEFT JOIN parts p ON (i.parts_id = p.id)
1646 LEFT JOIN project pr ON (i.project_id = pr.id)
1647 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1648 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1650 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1651 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1652 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1654 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1656 $sth = prepare_execute_query($form, $dbh, $query, $id);
1658 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1659 # Retrieve custom variables.
1660 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1662 sub_module => 'invoice',
1663 trans_id => $ref->{invoice_id},
1665 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1666 delete $ref->{invoice_id};
1668 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1669 delete($ref->{"part_inventory_accno_id"});
1671 foreach my $type (qw(inventory income expense)) {
1672 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1673 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1674 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1678 # get tax rates and description
1679 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1681 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1682 LEFT JOIN chart c ON (c.id = t.chart_id)
1684 (SELECT tk.tax_id FROM taxkeys tk
1685 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1686 AND startdate <= date($transdate)
1687 ORDER BY startdate DESC LIMIT 1)
1689 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1690 $ref->{taxaccounts} = "";
1692 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1694 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1698 $ref->{taxaccounts} .= "$ptr->{accno} ";
1700 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1701 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1702 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1703 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1704 $form->{taxaccounts} .= "$ptr->{accno} ";
1709 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1711 chop $ref->{taxaccounts};
1712 push @{ $form->{invoice_details} }, $ref;
1717 Common::webdav_folder($form);
1720 my $rc = $dbh->commit;
1722 $main::lxdebug->leave_sub();
1728 $main::lxdebug->enter_sub();
1730 my ($self, $myconfig, $form) = @_;
1732 # connect to database
1733 my $dbh = $form->get_standard_dbh;
1735 my $dateformat = $myconfig->{dateformat};
1736 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1738 my (@values, $duedate, $ref, $query);
1740 if ($form->{invdate}) {
1741 $duedate = "to_date(?, '$dateformat')";
1742 push @values, $form->{invdate};
1744 $duedate = "current_date";
1747 my $cid = conv_i($form->{customer_id});
1750 if ($form->{payment_id}) {
1751 $payment_id = "(pt.id = ?) OR";
1752 push @values, conv_i($form->{payment_id});
1758 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1759 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1760 c.street, c.zipcode, c.city, c.country,
1761 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1762 c.taxincluded_checked, c.direct_debit,
1763 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1764 b.discount AS tradediscount, b.description AS business
1766 LEFT JOIN business b ON (b.id = c.business_id)
1767 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1770 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1772 delete $ref->{salesman_id} if !$ref->{salesman_id};
1774 map { $form->{$_} = $ref->{$_} } keys %$ref;
1776 # remove any trailing whitespace
1777 $form->{curr} =~ s/\s*$//;
1779 # use customer currency if not empty
1780 $form->{currency} = $form->{curr} if $form->{curr};
1783 qq|SELECT sum(amount - paid) AS dunning_amount
1785 WHERE (paid < amount)
1786 AND (customer_id = ?)
1787 AND (dunning_config_id IS NOT NULL)|;
1788 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1789 map { $form->{$_} = $ref->{$_} } keys %$ref;
1792 qq|SELECT dnn.dunning_description AS max_dunning_level
1793 FROM dunning_config dnn
1794 WHERE id IN (SELECT dunning_config_id
1796 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1797 ORDER BY dunning_level DESC LIMIT 1|;
1798 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1799 map { $form->{$_} = $ref->{$_} } keys %$ref;
1801 $form->{creditremaining} = $form->{creditlimit};
1802 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1803 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1804 $form->{creditremaining} -= $value;
1808 (SELECT e.buy FROM exchangerate e
1809 WHERE e.curr = o.curr
1810 AND e.transdate = o.transdate)
1812 WHERE o.customer_id = ?
1813 AND o.quotation = '0'
1814 AND o.closed = '0'|;
1815 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1817 while (my ($amount, $exch) = $sth->fetchrow_array) {
1818 $exch = 1 unless $exch;
1819 $form->{creditremaining} -= $amount * $exch;
1823 # get shipto if we did not converted an order or invoice
1824 if (!$form->{shipto}) {
1825 map { delete $form->{$_} }
1826 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1827 shiptostreet shiptozipcode shiptocity shiptocountry
1828 shiptocontact shiptophone shiptofax shiptoemail);
1830 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1831 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1833 map { $form->{$_} = $ref->{$_} } keys %$ref;
1836 # setup last accounts used for this customer
1837 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1839 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1841 JOIN acc_trans ac ON (ac.chart_id = c.id)
1842 JOIN ar a ON (a.id = ac.trans_id)
1843 WHERE a.customer_id = ?
1844 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1845 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1846 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1849 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1850 if ($ref->{category} eq 'I') {
1852 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1854 if ($form->{initial_transdate}) {
1856 qq|SELECT tk.tax_id, t.rate
1858 LEFT JOIN tax t ON tk.tax_id = t.id
1859 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1860 ORDER BY tk.startdate DESC
1862 my ($tax_id, $rate) =
1863 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1864 $form->{initial_transdate});
1865 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1868 if ($ref->{category} eq 'A') {
1869 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1873 $form->{rowcount} = $i if ($i && !$form->{type});
1876 $main::lxdebug->leave_sub();
1880 $main::lxdebug->enter_sub();
1882 my ($self, $myconfig, $form) = @_;
1884 # connect to database
1885 my $dbh = $form->get_standard_dbh;
1887 my $i = $form->{rowcount};
1889 my $where = qq|NOT p.obsolete = '1'|;
1892 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1893 my ($table, $field) = split m/\./, $column;
1894 next if !$form->{"${field}_${i}"};
1895 $where .= qq| AND lower(${column}) ILIKE ?|;
1896 push @values, '%' . $form->{"${field}_${i}"} . '%';
1899 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1900 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1901 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1902 push @values, $form->{"partnumber_$i"};
1905 # Search for part ID overrides all other criteria.
1906 if ($form->{"id_${i}"}) {
1907 $where = qq|p.id = ?|;
1908 @values = ($form->{"id_${i}"});
1911 if ($form->{"description_$i"}) {
1912 $where .= qq| ORDER BY p.description|;
1914 $where .= qq| ORDER BY p.partnumber|;
1918 if ($form->{type} eq "invoice") {
1920 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1921 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1925 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1929 my $taxzone_id = $form->{taxzone_id} * 1;
1930 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1934 p.id, p.partnumber, p.description, p.sellprice,
1935 p.listprice, p.inventory_accno_id, p.lastcost,
1937 c1.accno AS inventory_accno,
1938 c1.new_chart_id AS inventory_new_chart,
1939 date($transdate) - c1.valid_from AS inventory_valid,
1941 c2.accno AS income_accno,
1942 c2.new_chart_id AS income_new_chart,
1943 date($transdate) - c2.valid_from AS income_valid,
1945 c3.accno AS expense_accno,
1946 c3.new_chart_id AS expense_new_chart,
1947 date($transdate) - c3.valid_from AS expense_valid,
1949 p.unit, p.assembly, p.bin, p.onhand,
1950 p.notes AS partnotes, p.notes AS longdescription,
1951 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1952 p.price_factor_id, p.weight,
1954 pfac.factor AS price_factor,
1959 LEFT JOIN chart c1 ON
1960 ((SELECT inventory_accno_id
1961 FROM buchungsgruppen
1962 WHERE id = p.buchungsgruppen_id) = c1.id)
1963 LEFT JOIN chart c2 ON
1964 ((SELECT income_accno_id_${taxzone_id}
1965 FROM buchungsgruppen
1966 WHERE id = p.buchungsgruppen_id) = c2.id)
1967 LEFT JOIN chart c3 ON
1968 ((SELECT expense_accno_id_${taxzone_id}
1969 FROM buchungsgruppen
1970 WHERE id = p.buchungsgruppen_id) = c3.id)
1971 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1972 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1974 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1976 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1978 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1979 [ qq|SELECT tr.translation, tr.longdescription
1981 WHERE tr.language_id IN
1984 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1987 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1989 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1991 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1992 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1993 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1994 if (!$ref->{inventory_accno_id}) {
1995 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1997 delete($ref->{inventory_accno_id});
1999 foreach my $type (qw(inventory income expense)) {
2000 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2002 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2005 ($ref->{"${type}_accno"},
2006 $ref->{"${type}_new_chart"},
2007 $ref->{"${type}_valid"})
2008 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2012 if ($form->{payment_id} eq "") {
2013 $form->{payment_id} = $form->{part_payment_id};
2016 # get tax rates and description
2017 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2019 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2021 LEFT JOIN chart c ON (c.id = t.chart_id)
2025 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2027 ORDER BY startdate DESC
2030 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2031 my $stw = $dbh->prepare($query);
2032 $stw->execute(@values) || $form->dberror($query);
2034 $ref->{taxaccounts} = "";
2036 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2038 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2042 $ref->{taxaccounts} .= "$ptr->{accno} ";
2044 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2045 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2046 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2047 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2048 $form->{taxaccounts} .= "$ptr->{accno} ";
2054 chop $ref->{taxaccounts};
2056 if ($form->{language_id}) {
2057 for my $spec (@translation_queries) {
2058 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2059 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2060 next unless $translation;
2061 $ref->{description} = $translation;
2062 $ref->{longdescription} = $longdescription;
2067 $ref->{onhand} *= 1;
2069 push @{ $form->{item_list} }, $ref;
2072 $_->[1]->finish for @translation_queries;
2074 foreach my $item (@{ $form->{item_list} }) {
2075 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2076 trans_id => $item->{id},
2080 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2083 $main::lxdebug->leave_sub();
2086 ##########################
2087 # get pricegroups from database
2088 # build up selected pricegroup
2089 # if an exchange rate - change price
2092 sub get_pricegroups_for_parts {
2094 $main::lxdebug->enter_sub();
2096 my ($self, $myconfig, $form) = @_;
2098 my $dbh = $form->get_standard_dbh;
2100 $form->{"PRICES"} = {};
2104 my $all_units = AM->retrieve_units($myconfig, $form);
2105 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2106 $form->{"PRICES"}{$i} = [];
2108 $id = $form->{"id_$i"};
2110 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2111 $id = $form->{"new_id_$i"};
2114 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2116 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2118 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2119 my $sellprice = $form->{"sellprice_$i"};
2120 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2121 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2122 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2124 my $price_new = $form->{"price_new_$i"};
2125 my $price_old = $form->{"price_old_$i"};
2127 if (!$form->{"unit_old_$i"}) {
2128 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2129 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2130 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2131 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2134 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2135 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2136 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2138 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2139 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2140 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2141 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2142 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2143 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2144 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2149 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2150 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2151 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2152 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2153 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2157 if (!$form->{"basefactor_$i"}) {
2158 $form->{"basefactor_$i"} = 1;
2164 sellprice AS default_sellprice,
2167 'selected' AS selected
2173 parts.sellprice AS default_sellprice,
2174 pricegroup.pricegroup,
2178 LEFT JOIN parts ON parts.id = parts_id
2179 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2181 ORDER BY pricegroup|;
2182 my @values = (conv_i($id), conv_i($id));
2183 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2185 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2187 $pkr->{selected} = '';
2189 # if there is an exchange rate change price
2190 if (($form->{exchangerate} * 1) != 0) {
2191 $pkr->{price} /= $form->{exchangerate};
2194 $pkr->{price} *= $form->{"basefactor_$i"};
2195 $pkr->{price} *= $basefactor;
2196 $pkr->{price_ufmt} = $pkr->{price};
2197 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2199 if (!defined $selectedpricegroup_id) {
2200 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2201 # Case A: open old invoice, no pricegroup selected
2202 # Case B: add new article to invoice, no pricegroup selected
2204 # to distinguish case A and B the variable pricegroup_id_$i is used
2205 # for new articles this variable isn't defined, for loaded articles it is
2206 # sellprice can't be used, as it already has 0,00 set
2208 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2210 $pkr->{selected} = ' selected';
2211 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2212 and not defined $form->{"pricegroup_id_$i"}
2213 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2214 # for the case where pricegroup prices haven't been set
2216 # Case B: use default pricegroup of customer
2218 $pkr->{selected} = ' selected'; # unless $form->{selected};
2219 # no customer pricesgroup set
2220 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2222 $pkr->{price} = $form->{"sellprice_$i"};
2226 # this sub should not set anything and only return. --sschoeling, 20090506
2227 # is this correct? put in again... -- grichardson 20110119
2228 $form->{"sellprice_$i"} = $pkr->{price};
2231 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2232 $pkr->{price} = $form->{"sellprice_$i"};
2233 $pkr->{selected} = ' selected';
2237 # existing article: pricegroup or price changed
2238 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2239 if ($selectedpricegroup_id ne $pricegroup_old) {
2240 # pricegroup has changed
2241 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2242 $pkr->{selected} = ' selected';
2244 } elsif ( ($form->parse_amount($myconfig, $price_new)
2245 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2246 and ($price_new ne 0) and defined $price_new) {
2247 # sellprice has changed
2248 # when loading existing invoices $price_new is NULL
2249 if ($pkr->{pricegroup_id} == 0) {
2250 $pkr->{price} = $form->{"sellprice_$i"};
2251 $pkr->{selected} = ' selected';
2253 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2254 # neither sellprice nor pricegroup changed
2255 $pkr->{selected} = ' selected';
2256 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2257 # $pkr->{price} = $form->{"sellprice_$i"};
2259 $pkr->{price} = $form->{"sellprice_$i"};
2263 push @{ $form->{PRICES}{$i} }, $pkr;
2266 $form->{"basefactor_$i"} *= $basefactor;
2273 $main::lxdebug->leave_sub();
2277 $main::lxdebug->enter_sub();
2279 my ($self, $myconfig, $form, $table) = @_;
2281 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2283 # make sure there's no funny stuff in $table
2284 # ToDO: die when this happens and throw an error
2285 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2287 my $dbh = $form->get_standard_dbh;
2289 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2290 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2292 $main::lxdebug->leave_sub();
2298 $main::lxdebug->enter_sub();
2300 my ($self, $myconfig, $form, $table, $id) = @_;
2302 $main::lxdebug->leave_sub() and return 0 unless ($id);
2304 # make sure there's no funny stuff in $table
2305 # ToDO: die when this happens and throw an error
2306 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2308 my $dbh = $form->get_standard_dbh;
2310 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2311 my ($result) = selectrow_query($form, $dbh, $query, $id);
2313 $main::lxdebug->leave_sub();
2318 sub get_standard_accno_current_assets {
2319 $main::lxdebug->enter_sub();
2321 my ($self, $myconfig, $form) = @_;
2323 my $dbh = $form->get_standard_dbh;
2325 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2326 my ($result) = selectrow_query($form, $dbh, $query);
2328 $main::lxdebug->leave_sub();