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;
58 $main::lxdebug->enter_sub();
60 my ($self, $myconfig, $form, $locale) = @_;
62 $form->{duedate} ||= $form->{invdate};
65 my $dbh = $form->get_standard_dbh;
68 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
69 ($form->{terms}) = selectrow_query($form, $dbh, $query);
71 my (@project_ids, %projectnumbers, %projectdescriptions);
72 $form->{TEMPLATE_ARRAYS} = {};
74 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
76 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
79 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
80 $price_factors{$pfac->{id}} = $pfac;
82 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
85 # sort items by partsgroup
86 for my $i (1 .. $form->{rowcount}) {
88 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
89 # $partsgroup = $form->{"partsgroup_$i"};
91 # push @partsgroup, [$i, $partsgroup];
92 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
96 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
97 join(", ", map({ "?" } @project_ids)) . ")";
98 $sth = $dbh->prepare($query);
99 $sth->execute(@project_ids) ||
100 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
101 while (my $ref = $sth->fetchrow_hashref()) {
102 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
103 $projectdescriptions{$ref->{id}} = $ref->{description};
108 $form->{"globalprojectnumber"} =
109 $projectnumbers{$form->{"globalproject_id"}};
110 $form->{"globalprojectdescription"} =
111 $projectdescriptions{$form->{"globalproject_id"}};
119 # sort items by partsgroup
120 for $i (1 .. $form->{rowcount}) {
122 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
123 $partsgroup = $form->{"partsgroup_$i"};
125 push @partsgroup, [$i, $partsgroup];
138 my $nodiscount_subtotal = 0;
139 my $discount_subtotal = 0;
141 my $subtotal_header = 0;
144 $form->{discount} = [];
146 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
148 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
151 qw(runningnumber number description longdescription qty ship unit bin
152 deliverydate_oe ordnumber_oe transdate_oe validuntil
153 partnotes serialnumber reqdate sellprice listprice netprice
154 discount p_discount discount_sub nodiscount_sub
155 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
156 price_factor price_factor_name partsgroup weight lineweight);
158 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
160 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
162 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
164 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 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
284 $totalweight += $lineweight;
285 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
286 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
287 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
288 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
290 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
294 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
296 if ($form->{taxincluded}) {
299 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
300 $taxbase = $linetotal - $taxamount;
302 $taxamount = $linetotal * $taxrate;
303 $taxbase = $linetotal;
306 if ($form->round_amount($taxrate, 7) == 0) {
307 if ($form->{taxincluded}) {
308 foreach my $accno (@taxaccounts) {
309 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
311 $taxaccounts{$accno} += $taxamount;
312 $taxdiff += $taxamount;
314 $taxbase{$accno} += $taxbase;
316 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
318 foreach my $accno (@taxaccounts) {
319 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
320 $taxbase{$accno} += $taxbase;
324 foreach my $accno (@taxaccounts) {
325 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
326 $taxbase{$accno} += $taxbase;
329 my $tax_rate = $taxrate * 100;
330 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
331 if ($form->{"assembly_$i"}) {
334 # get parts and push them onto the stack
336 if ($form->{groupitems}) {
338 qq|ORDER BY pg.partsgroup, a.oid|;
340 $sortorder = qq|ORDER BY a.oid|;
344 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
346 JOIN parts p ON (a.parts_id = p.id)
347 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
348 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
349 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
351 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
352 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
353 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
354 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
355 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
358 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
360 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
361 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
363 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
364 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
370 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
371 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
372 for @{ $ic_cvar_configs };
376 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
377 $form->{totalweight_nofmt} = $totalweight;
378 my $defaults = AM->get_defaults();
379 $form->{weightunit} = $defaults->{weightunit};
381 foreach my $item (sort keys %taxaccounts) {
382 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
384 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
387 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
388 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
389 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
390 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
392 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
393 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
394 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
397 for my $i (1 .. $form->{paidaccounts}) {
398 if ($form->{"paid_$i"}) {
399 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
401 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
402 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
403 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
404 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
405 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
407 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
410 if($form->{taxincluded}) {
411 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
412 $form->{subtotal_nofmt} = $form->{total} - $tax;
415 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
416 $form->{subtotal_nofmt} = $form->{total};
419 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
420 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
421 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
422 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
424 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
425 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
427 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
428 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
430 $form->set_payment_options($myconfig, $form->{invdate});
432 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
433 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
435 $form->{username} = $myconfig->{name};
437 $main::lxdebug->leave_sub();
440 sub project_description {
441 $main::lxdebug->enter_sub();
443 my ($self, $dbh, $id) = @_;
444 my $form = \%main::form;
446 my $query = qq|SELECT description FROM project WHERE id = ?|;
447 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
449 $main::lxdebug->leave_sub();
454 sub customer_details {
455 $main::lxdebug->enter_sub();
457 my ($self, $myconfig, $form, @wanted_vars) = @_;
459 # connect to database
460 my $dbh = $form->get_standard_dbh;
462 my $language_id = $form->{language_id};
464 # get contact id, set it if nessessary
467 my @values = (conv_i($form->{customer_id}));
470 if ($form->{cp_id}) {
471 $where = qq| AND (cp.cp_id = ?) |;
472 push(@values, conv_i($form->{cp_id}));
475 # get rest for the customer
477 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
478 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
481 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
482 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
483 WHERE (ct.id = ?) $where
486 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
488 # remove id and taxincluded before copy back
489 delete @$ref{qw(id taxincluded)};
491 @wanted_vars = grep({ $_ } @wanted_vars);
492 if (scalar(@wanted_vars) > 0) {
494 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
495 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
498 map { $form->{$_} = $ref->{$_} } keys %$ref;
500 if ($form->{delivery_customer_id}) {
502 qq|SELECT *, notes as customernotes
506 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
508 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
511 if ($form->{delivery_vendor_id}) {
513 qq|SELECT *, notes as customernotes
517 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
519 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
522 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
524 'trans_id' => $form->{customer_id});
525 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
527 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
528 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
529 'language_id' => $language_id,
530 'allow_fallback' => 1);
533 $main::lxdebug->leave_sub();
537 $main::lxdebug->enter_sub();
539 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
541 # connect to database, turn off autocommit
542 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
544 my ($query, $sth, $null, $project_id, @values);
545 my $exchangerate = 0;
547 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
550 if (!$form->{employee_id}) {
551 $form->get_employee($dbh);
554 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
555 my $defaultcurrency = $form->{defaultcurrency};
557 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
558 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
559 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
560 if (!$form->{department_id}){
561 ($null, $form->{department_id}) = split(/--/, $form->{department});
564 my $all_units = AM->retrieve_units($myconfig, $form);
566 if (!$payments_only) {
568 &reverse_invoice($dbh, $form);
571 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
572 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
574 $query = qq|SELECT nextval('glid')|;
575 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
577 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
578 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
580 if (!$form->{invnumber}) {
582 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
583 "cnnumber" : "invnumber", $dbh);
588 my ($netamount, $invoicediff) = (0, 0);
589 my ($amount, $linetotal, $lastincomeaccno);
591 if ($form->{currency} eq $defaultcurrency) {
592 $form->{exchangerate} = 1;
594 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
597 $form->{exchangerate} =
600 : $form->parse_amount($myconfig, $form->{exchangerate});
602 $form->{expense_inventory} = "";
606 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
607 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
610 $form->{amount} = {};
611 $form->{amount_cogs} = {};
613 foreach my $i (1 .. $form->{rowcount}) {
614 if ($form->{type} eq "credit_note") {
615 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
616 $form->{shipped} = 1;
618 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
623 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
624 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
625 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
627 if ($form->{storno}) {
628 $form->{"qty_$i"} *= -1;
631 if ($form->{"id_$i"}) {
634 if (defined($baseunits{$form->{"id_$i"}})) {
635 $item_unit = $baseunits{$form->{"id_$i"}};
638 $query = qq|SELECT unit FROM parts WHERE id = ?|;
639 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
640 $baseunits{$form->{"id_$i"}} = $item_unit;
643 if (defined($all_units->{$item_unit}->{factor})
644 && ($all_units->{$item_unit}->{factor} ne '')
645 && ($all_units->{$item_unit}->{factor} != 0)) {
646 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
650 $baseqty = $form->{"qty_$i"} * $basefactor;
652 my ($allocated, $taxrate) = (0, 0);
656 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
658 # keep entered selling price
660 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
662 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
664 my $decimalplaces = ($dec > 2) ? $dec : 2;
666 # undo discount formatting
667 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
670 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
672 # round linetotal to 2 decimal places
673 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
674 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
676 if ($form->{taxincluded}) {
677 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
678 $form->{"sellprice_$i"} =
679 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
681 $taxamount = $linetotal * $taxrate;
684 $netamount += $linetotal;
686 if ($taxamount != 0) {
688 $form->{amount}{ $form->{id} }{$_} +=
689 $taxamount * $form->{"${_}_rate"} / $taxrate
690 } split(/ /, $form->{"taxaccounts_$i"});
693 # add amount to income, $form->{amount}{trans_id}{accno}
694 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
696 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
697 $linetotal = $form->round_amount($linetotal, 2);
699 # this is the difference from the inventory
700 $invoicediff += ($amount - $linetotal);
702 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
705 $lastincomeaccno = $form->{"income_accno_$i"};
707 # adjust and round sellprice
708 $form->{"sellprice_$i"} =
709 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
712 next if $payments_only;
714 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
716 if ($form->{"assembly_$i"}) {
717 # record assembly item as allocated
718 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
721 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
725 # Get pricegroup_id and save it. Unfortunately the interface
726 # also uses ID "0" for signalling that none is selected, but "0"
727 # must not be stored in the database. Therefore we cannot simply
729 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
731 $pricegroup_id = undef if !$pricegroup_id;
733 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
735 # save detail record in invoice table
737 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
738 sellprice, fxsellprice, discount, allocated, assemblyitem,
739 unit, deliverydate, project_id, serialnumber, pricegroup_id,
740 ordnumber, transdate, cusordnumber, base_qty, subtotal,
741 marge_percent, marge_total, lastcost,
742 price_factor_id, price_factor, marge_price_factor)
743 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
744 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
746 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
747 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
748 $form->{"sellprice_$i"}, $fxsellprice,
749 $form->{"discount_$i"}, $allocated, 'f',
750 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
751 $form->{"serialnumber_$i"}, $pricegroup_id,
752 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
753 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
754 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
755 $form->{"lastcost_$i"},
756 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
757 conv_i($form->{"marge_price_factor_$i"}));
758 do_query($form, $dbh, $query, @values);
760 CVar->save_custom_variables(module => 'IC',
761 sub_module => 'invoice',
762 trans_id => $invoice_id,
763 configs => $ic_cvar_configs,
765 name_prefix => 'ic_',
766 name_postfix => "_$i",
771 # total payments, don't move we need it here
772 for my $i (1 .. $form->{paidaccounts}) {
773 if ($form->{type} eq "credit_note") {
774 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
776 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
778 $form->{paid} += $form->{"paid_$i"};
779 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
782 my ($tax, $diff) = (0, 0);
784 $netamount = $form->round_amount($netamount, 2);
786 # figure out rounding errors for total amount vs netamount + taxes
787 if ($form->{taxincluded}) {
789 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
790 $diff += $amount - $netamount * $form->{exchangerate};
791 $netamount = $amount;
793 foreach my $item (split(/ /, $form->{taxaccounts})) {
794 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
795 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
796 $tax += $form->{amount}{ $form->{id} }{$item};
797 $netamount -= $form->{amount}{ $form->{id} }{$item};
800 $invoicediff += $diff;
801 ######## this only applies to tax included
802 if ($lastincomeaccno) {
803 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
807 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
808 $diff = $amount - $netamount * $form->{exchangerate};
809 $netamount = $amount;
810 foreach my $item (split(/ /, $form->{taxaccounts})) {
811 $form->{amount}{ $form->{id} }{$item} =
812 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
815 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
818 $amount - $form->{amount}{ $form->{id} }{$item} *
819 $form->{exchangerate};
820 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
821 $tax += $form->{amount}{ $form->{id} }{$item};
825 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
827 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
830 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
832 # update exchangerate
833 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
834 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
835 $form->{exchangerate}, 0);
838 $project_id = conv_i($form->{"globalproject_id"});
839 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
840 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
842 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
843 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
844 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
846 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
848 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
850 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
851 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
852 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
853 do_query($form, $dbh, $query, @values);
854 $form->{amount_cogs}{$trans_id}{$accno} = 0;
858 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
859 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
861 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
863 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
864 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
865 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
866 do_query($form, $dbh, $query, @values);
871 foreach my $trans_id (keys %{ $form->{amount} }) {
872 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
873 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
875 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
877 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
879 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
880 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
883 WHERE chart_id= (SELECT id
887 ORDER BY startdate DESC LIMIT 1),
890 WHERE chart_id= (SELECT id
894 ORDER BY startdate DESC LIMIT 1),
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_date($taxdate), conv_i($project_id), $accno);
898 do_query($form, $dbh, $query, @values);
899 $form->{amount}{$trans_id}{$accno} = 0;
903 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
904 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
906 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
908 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
909 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
912 WHERE chart_id= (SELECT id
916 ORDER BY startdate DESC LIMIT 1),
919 WHERE chart_id= (SELECT id
923 ORDER BY startdate DESC LIMIT 1),
925 (SELECT link FROM chart WHERE accno = ?))|;
926 @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);
927 do_query($form, $dbh, $query, @values);
932 # deduct payment differences from diff
933 for my $i (1 .. $form->{paidaccounts}) {
934 if ($form->{"paid_$i"} != 0) {
936 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
937 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
941 # record payments and offsetting AR
942 if (!$form->{storno}) {
943 for my $i (1 .. $form->{paidaccounts}) {
945 if ($form->{"acc_trans_id_$i"}
947 && (SL::DB::Default->get->payments_changeable == 0)) {
951 next if ($form->{"paid_$i"} == 0);
953 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
954 $form->{"datepaid_$i"} = $form->{invdate}
955 unless ($form->{"datepaid_$i"});
956 $form->{datepaid} = $form->{"datepaid_$i"};
960 if ($form->{currency} eq $defaultcurrency) {
961 $form->{"exchangerate_$i"} = 1;
963 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
964 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
968 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
970 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
972 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
973 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
976 WHERE chart_id= (SELECT id
980 ORDER BY startdate DESC LIMIT 1),
983 WHERE chart_id= (SELECT id
987 ORDER BY startdate DESC LIMIT 1),
989 (SELECT link FROM chart WHERE accno = ?))|;
990 @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});
991 do_query($form, $dbh, $query, @values);
995 $form->{"paid_$i"} *= -1;
996 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
999 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1000 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1003 WHERE chart_id= (SELECT id
1007 ORDER BY startdate DESC LIMIT 1),
1010 WHERE chart_id= (SELECT id
1014 ORDER BY startdate DESC LIMIT 1),
1016 (SELECT link FROM chart WHERE accno = ?))|;
1017 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1018 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1019 do_query($form, $dbh, $query, @values);
1021 # exchangerate difference
1022 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1023 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1027 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1028 $form->{"exchangerate_$i"};
1030 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1032 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1037 # update exchange rate
1038 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1039 $form->update_exchangerate($dbh, $form->{currency},
1040 $form->{"datepaid_$i"},
1041 $form->{"exchangerate_$i"}, 0);
1045 } else { # if (!$form->{storno})
1046 $form->{marge_total} *= -1;
1049 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1051 # record exchange rate differences and gains/losses
1052 foreach my $accno (keys %{ $form->{fx} }) {
1053 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1054 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1055 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1058 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1059 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1062 WHERE chart_id= (SELECT id
1066 ORDER BY startdate DESC LIMIT 1),
1069 WHERE chart_id= (SELECT id
1073 ORDER BY startdate DESC LIMIT 1),
1075 (SELECT link FROM chart WHERE accno = ?))|;
1076 @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);
1077 do_query($form, $dbh, $query, @values);
1082 if ($payments_only) {
1083 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1084 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1086 $dbh->commit if !$provided_dbh;
1088 $main::lxdebug->leave_sub();
1092 $amount = $netamount + $tax;
1095 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1097 $query = qq|UPDATE ar set
1098 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1099 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1100 amount = ?, netamount = ?, paid = ?,
1101 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1102 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1103 currency_id = (SELECT id FROM currencies WHERE name = ?),
1104 department_id = ?, payment_id = ?, taxincluded = ?,
1105 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1106 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1107 cp_id = ?, marge_total = ?, marge_percent = ?,
1108 globalproject_id = ?, delivery_customer_id = ?,
1109 transaction_description = ?, delivery_vendor_id = ?,
1110 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1111 delivery_term_id = ?
1113 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1114 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1115 $amount, $netamount, $form->{"paid"},
1116 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1117 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1118 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1119 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1120 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1121 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1122 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1123 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1124 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1125 conv_i($form->{delivery_term_id}),
1126 conv_i($form->{"id"}));
1127 do_query($form, $dbh, $query, @values);
1130 if ($form->{storno}) {
1133 paid = paid + amount,
1135 intnotes = ? || intnotes
1137 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1138 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1142 $form->{name} = $form->{customer};
1143 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1145 if (!$form->{shipto_id}) {
1146 $form->add_shipto($dbh, $form->{id}, "AR");
1149 # save printed, emailed and queued
1150 $form->save_status($dbh);
1152 Common::webdav_folder($form);
1154 # Link this record to the records it was created from.
1155 RecordLinks->create_links('dbh' => $dbh,
1157 'from_table' => 'oe',
1158 'from_ids' => $form->{convert_from_oe_ids},
1160 'to_id' => $form->{id},
1162 delete $form->{convert_from_oe_ids};
1164 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1166 if (scalar @convert_from_do_ids) {
1167 DO->close_orders('dbh' => $dbh,
1168 'ids' => \@convert_from_do_ids);
1170 RecordLinks->create_links('dbh' => $dbh,
1172 'from_table' => 'delivery_orders',
1173 'from_ids' => \@convert_from_do_ids,
1175 'to_id' => $form->{id},
1178 delete $form->{convert_from_do_ids};
1180 ARAP->close_orders_if_billed('dbh' => $dbh,
1181 'arap_id' => $form->{id},
1184 # safety check datev export
1185 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1186 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1187 $transdate ||= DateTime->today;
1189 my $datev = SL::DATEV->new(
1190 exporttype => DATEV_ET_BUCHUNGEN,
1191 format => DATEV_FORMAT_KNE,
1195 trans_id => $form->{id},
1200 if ($datev->errors) {
1202 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1207 $dbh->commit if !$provided_dbh;
1209 $main::lxdebug->leave_sub();
1214 sub _delete_payments {
1215 $main::lxdebug->enter_sub();
1217 my ($self, $form, $dbh) = @_;
1219 my @delete_acc_trans_ids;
1221 # Delete old payment entries from acc_trans.
1223 qq|SELECT acc_trans_id
1225 WHERE (trans_id = ?) AND fx_transaction
1229 SELECT at.acc_trans_id
1231 LEFT JOIN chart c ON (at.chart_id = c.id)
1232 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1233 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1236 qq|SELECT at.acc_trans_id
1238 LEFT JOIN chart c ON (at.chart_id = c.id)
1239 WHERE (trans_id = ?)
1240 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1241 ORDER BY at.acc_trans_id
1243 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1245 if (@delete_acc_trans_ids) {
1246 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1247 do_query($form, $dbh, $query);
1250 $main::lxdebug->leave_sub();
1254 $main::lxdebug->enter_sub();
1256 my ($self, $myconfig, $form, $locale) = @_;
1258 # connect to database, turn off autocommit
1259 my $dbh = $form->get_standard_dbh;
1261 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1263 $old_form = save_form();
1265 # Delete all entries in acc_trans from prior payments.
1266 if (SL::DB::Default->get->payments_changeable != 0) {
1267 $self->_delete_payments($form, $dbh);
1270 # Save the new payments the user made before cleaning up $form.
1271 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 };
1273 # Clean up $form so that old content won't tamper the results.
1274 %keep_vars = map { $_, 1 } qw(login password id);
1275 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1277 # Retrieve the invoice from the database.
1278 $self->retrieve_invoice($myconfig, $form);
1280 # Set up the content of $form in the way that IS::post_invoice() expects.
1281 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1283 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1284 $item = $form->{invoice_details}->[$row - 1];
1286 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1288 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1291 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1293 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1295 # Restore the payment options from the user input.
1296 map { $form->{$_} = $payments{$_} } keys %payments;
1298 # Get the AR accno (which is normally done by Form::create_links()).
1302 LEFT JOIN chart c ON (at.chart_id = c.id)
1303 WHERE (trans_id = ?)
1304 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1305 ORDER BY at.acc_trans_id
1308 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1310 # Post the new payments.
1311 $self->post_invoice($myconfig, $form, $dbh, 1);
1313 restore_form($old_form);
1315 my $rc = $dbh->commit();
1317 $main::lxdebug->leave_sub();
1322 sub process_assembly {
1323 $main::lxdebug->enter_sub();
1325 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1328 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1329 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1331 JOIN parts p ON (a.parts_id = p.id)
1333 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1335 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1339 $ref->{inventory_accno_id} *= 1;
1340 $ref->{expense_accno_id} *= 1;
1342 # multiply by number of assemblies
1343 $ref->{qty} *= $totalqty;
1345 if ($ref->{assembly}) {
1346 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1349 if ($ref->{inventory_accno_id}) {
1350 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1354 # save detail record for individual assembly item in invoice table
1356 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1357 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1358 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1359 do_query($form, $dbh, $query, @values);
1365 $main::lxdebug->leave_sub();
1369 $main::lxdebug->enter_sub();
1371 # adjust allocated in table invoice according to FIFO princicple
1372 # for a certain part with part_id $id
1374 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1378 $form->{taxzone_id} *=1;
1379 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1380 my $taxzone_id = $form->{"taxzone_id"} * 1;
1382 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1383 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1384 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1385 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1386 FROM invoice i, parts p
1387 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1388 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1389 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1390 WHERE (i.parts_id = p.id)
1391 AND (i.parts_id = ?)
1392 AND ((i.base_qty + i.allocated) < 0)
1394 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1399 # all invoice entries of an example part:
1401 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1402 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1403 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1404 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1405 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1406 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1407 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1409 # 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
1410 # and all parts have been allocated
1412 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1413 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1415 # in this example there are still 4 unsold articles
1418 # search all invoice entries for the part in question, adjusting "allocated"
1419 # until the total number of sold parts has been reached
1421 # ORDER BY trans_id ensures FIFO
1424 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1425 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1429 # update allocated in invoice
1430 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1432 # total expenses and inventory
1433 # sellprice is the cost of the item
1434 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1436 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1437 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1438 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1440 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1441 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1442 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1444 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1445 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1451 last if (($totalqty -= $qty) <= 0);
1456 $main::lxdebug->leave_sub();
1461 sub reverse_invoice {
1462 $main::lxdebug->enter_sub();
1464 my ($dbh, $form) = @_;
1466 # reverse inventory items
1468 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1470 JOIN parts p ON (i.parts_id = p.id)
1471 WHERE i.trans_id = ?|;
1472 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1474 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1476 if ($ref->{inventory_accno_id}) {
1477 # de-allocated purchases
1479 qq|SELECT i.id, i.trans_id, i.allocated
1481 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1482 ORDER BY i.trans_id DESC|;
1483 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1485 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1486 my $qty = $ref->{qty};
1487 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1488 $qty = $inhref->{allocated};
1492 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1494 last if (($ref->{qty} -= $qty) <= 0);
1503 my @values = (conv_i($form->{id}));
1504 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1505 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1506 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1508 $main::lxdebug->leave_sub();
1511 sub delete_invoice {
1512 $main::lxdebug->enter_sub();
1514 my ($self, $myconfig, $form) = @_;
1516 # connect to database
1517 my $dbh = $form->get_standard_dbh;
1519 &reverse_invoice($dbh, $form);
1521 my @values = (conv_i($form->{id}));
1523 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1524 # zurückgesetzt werden. Vgl:
1525 # id | storno | storno_id | paid | amount
1526 #----+--------+-----------+---------+-----------
1527 # 18 | f | | 0.00000 | 119.00000
1529 # 18 | t | | 119.00000 | 119.00000
1531 if($form->{storno}){
1532 # storno_id auslesen und korrigieren
1533 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1534 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1537 # delete spool files
1538 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1541 qq|DELETE FROM status WHERE trans_id = ?|,
1542 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1543 qq|DELETE FROM ar WHERE id = ?|,
1546 map { do_query($form, $dbh, $_, @values) } @queries;
1548 my $rc = $dbh->commit;
1551 my $spool = $::lx_office_conf{paths}->{spool};
1552 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1555 $main::lxdebug->leave_sub();
1560 sub retrieve_invoice {
1561 $main::lxdebug->enter_sub();
1563 my ($self, $myconfig, $form) = @_;
1565 # connect to database
1566 my $dbh = $form->get_standard_dbh;
1568 my ($sth, $ref, $query);
1570 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1574 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1575 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1576 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1577 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1578 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1582 $ref = selectfirst_hashref_query($form, $dbh, $query);
1583 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1586 my $id = conv_i($form->{id});
1589 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1593 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1594 a.orddate, a.quodate, a.globalproject_id,
1595 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1596 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1597 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1598 a.employee_id, a.salesman_id, a.payment_id,
1599 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1600 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1601 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1604 LEFT JOIN employee e ON (e.id = a.employee_id)
1606 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1607 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1609 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1612 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1613 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1615 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1617 foreach my $vc (qw(customer vendor)) {
1618 next if !$form->{"delivery_${vc}_id"};
1619 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1622 # get printed, emailed
1623 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1624 $sth = prepare_execute_query($form, $dbh, $query, $id);
1626 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1627 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1628 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1629 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1632 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1634 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1635 : $form->{invdate} ? $dbh->quote($form->{invdate})
1639 my $taxzone_id = $form->{taxzone_id} *= 1;
1640 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1642 # retrieve individual items
1645 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1646 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1647 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1650 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1651 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1652 i.price_factor_id, i.price_factor, i.marge_price_factor,
1653 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1654 pr.projectnumber, pg.partsgroup, prg.pricegroup
1657 LEFT JOIN parts p ON (i.parts_id = p.id)
1658 LEFT JOIN project pr ON (i.project_id = pr.id)
1659 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1660 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1662 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1663 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1664 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1666 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1668 $sth = prepare_execute_query($form, $dbh, $query, $id);
1670 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1671 # Retrieve custom variables.
1672 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1674 sub_module => 'invoice',
1675 trans_id => $ref->{invoice_id},
1677 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1678 delete $ref->{invoice_id};
1680 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1681 delete($ref->{"part_inventory_accno_id"});
1683 foreach my $type (qw(inventory income expense)) {
1684 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1685 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1686 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1690 # get tax rates and description
1691 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1693 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1694 LEFT JOIN chart c ON (c.id = t.chart_id)
1696 (SELECT tk.tax_id FROM taxkeys tk
1697 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1698 AND startdate <= date($transdate)
1699 ORDER BY startdate DESC LIMIT 1)
1701 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1702 $ref->{taxaccounts} = "";
1704 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1706 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1710 $ref->{taxaccounts} .= "$ptr->{accno} ";
1712 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1713 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1714 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1715 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1716 $form->{taxaccounts} .= "$ptr->{accno} ";
1721 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1723 chop $ref->{taxaccounts};
1724 push @{ $form->{invoice_details} }, $ref;
1729 Common::webdav_folder($form);
1732 my $rc = $dbh->commit;
1734 $main::lxdebug->leave_sub();
1740 $main::lxdebug->enter_sub();
1742 my ($self, $myconfig, $form) = @_;
1744 # connect to database
1745 my $dbh = $form->get_standard_dbh;
1747 my $dateformat = $myconfig->{dateformat};
1748 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1750 my (@values, $duedate, $ref, $query);
1752 if ($form->{invdate}) {
1753 $duedate = "to_date(?, '$dateformat')";
1754 push @values, $form->{invdate};
1756 $duedate = "current_date";
1759 my $cid = conv_i($form->{customer_id});
1762 if ($form->{payment_id}) {
1763 $payment_id = "(pt.id = ?) OR";
1764 push @values, conv_i($form->{payment_id});
1770 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1771 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1772 c.street, c.zipcode, c.city, c.country,
1773 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1774 c.taxincluded_checked, c.direct_debit,
1775 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1776 b.discount AS tradediscount, b.description AS business
1778 LEFT JOIN business b ON (b.id = c.business_id)
1779 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1780 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1783 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1785 delete $ref->{salesman_id} if !$ref->{salesman_id};
1787 map { $form->{$_} = $ref->{$_} } keys %$ref;
1789 # use customer currency
1790 $form->{currency} = $form->{curr};
1793 qq|SELECT sum(amount - paid) AS dunning_amount
1795 WHERE (paid < amount)
1796 AND (customer_id = ?)
1797 AND (dunning_config_id IS NOT NULL)|;
1798 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1799 map { $form->{$_} = $ref->{$_} } keys %$ref;
1802 qq|SELECT dnn.dunning_description AS max_dunning_level
1803 FROM dunning_config dnn
1804 WHERE id IN (SELECT dunning_config_id
1806 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1807 ORDER BY dunning_level DESC LIMIT 1|;
1808 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1809 map { $form->{$_} = $ref->{$_} } keys %$ref;
1811 $form->{creditremaining} = $form->{creditlimit};
1812 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1813 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1814 $form->{creditremaining} -= $value;
1818 (SELECT e.buy FROM exchangerate e
1819 WHERE e.currency_id = o.currency_id
1820 AND e.transdate = o.transdate)
1822 WHERE o.customer_id = ?
1823 AND o.quotation = '0'
1824 AND o.closed = '0'|;
1825 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1827 while (my ($amount, $exch) = $sth->fetchrow_array) {
1828 $exch = 1 unless $exch;
1829 $form->{creditremaining} -= $amount * $exch;
1833 # get shipto if we did not converted an order or invoice
1834 if (!$form->{shipto}) {
1835 map { delete $form->{$_} }
1836 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1837 shiptostreet shiptozipcode shiptocity shiptocountry
1838 shiptocontact shiptophone shiptofax shiptoemail);
1840 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1841 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1843 map { $form->{$_} = $ref->{$_} } keys %$ref;
1846 # setup last accounts used for this customer
1847 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1849 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1851 JOIN acc_trans ac ON (ac.chart_id = c.id)
1852 JOIN ar a ON (a.id = ac.trans_id)
1853 WHERE a.customer_id = ?
1854 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1855 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1856 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1859 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1860 if ($ref->{category} eq 'I') {
1862 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1864 if ($form->{initial_transdate}) {
1866 qq|SELECT tk.tax_id, t.rate
1868 LEFT JOIN tax t ON tk.tax_id = t.id
1869 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1870 ORDER BY tk.startdate DESC
1872 my ($tax_id, $rate) =
1873 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1874 $form->{initial_transdate});
1875 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1878 if ($ref->{category} eq 'A') {
1879 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1883 $form->{rowcount} = $i if ($i && !$form->{type});
1886 $main::lxdebug->leave_sub();
1890 $main::lxdebug->enter_sub();
1892 my ($self, $myconfig, $form) = @_;
1894 # connect to database
1895 my $dbh = $form->get_standard_dbh;
1897 my $i = $form->{rowcount};
1899 my $where = qq|NOT p.obsolete = '1'|;
1902 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1903 my ($table, $field) = split m/\./, $column;
1904 next if !$form->{"${field}_${i}"};
1905 $where .= qq| AND lower(${column}) ILIKE ?|;
1906 push @values, '%' . $form->{"${field}_${i}"} . '%';
1909 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1910 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1911 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1912 push @values, $form->{"partnumber_$i"};
1915 # Search for part ID overrides all other criteria.
1916 if ($form->{"id_${i}"}) {
1917 $where = qq|p.id = ?|;
1918 @values = ($form->{"id_${i}"});
1921 if ($form->{"description_$i"}) {
1922 $where .= qq| ORDER BY p.description|;
1924 $where .= qq| ORDER BY p.partnumber|;
1928 if ($form->{type} eq "invoice") {
1930 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1931 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1935 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1939 my $taxzone_id = $form->{taxzone_id} * 1;
1940 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1944 p.id, p.partnumber, p.description, p.sellprice,
1945 p.listprice, p.inventory_accno_id, p.lastcost,
1947 c1.accno AS inventory_accno,
1948 c1.new_chart_id AS inventory_new_chart,
1949 date($transdate) - c1.valid_from AS inventory_valid,
1951 c2.accno AS income_accno,
1952 c2.new_chart_id AS income_new_chart,
1953 date($transdate) - c2.valid_from AS income_valid,
1955 c3.accno AS expense_accno,
1956 c3.new_chart_id AS expense_new_chart,
1957 date($transdate) - c3.valid_from AS expense_valid,
1959 p.unit, p.assembly, p.onhand,
1960 p.notes AS partnotes, p.notes AS longdescription,
1961 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1962 p.price_factor_id, p.weight,
1964 pfac.factor AS price_factor,
1969 LEFT JOIN chart c1 ON
1970 ((SELECT inventory_accno_id
1971 FROM buchungsgruppen
1972 WHERE id = p.buchungsgruppen_id) = c1.id)
1973 LEFT JOIN chart c2 ON
1974 ((SELECT income_accno_id_${taxzone_id}
1975 FROM buchungsgruppen
1976 WHERE id = p.buchungsgruppen_id) = c2.id)
1977 LEFT JOIN chart c3 ON
1978 ((SELECT expense_accno_id_${taxzone_id}
1979 FROM buchungsgruppen
1980 WHERE id = p.buchungsgruppen_id) = c3.id)
1981 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1982 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1984 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1986 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1988 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1989 [ qq|SELECT tr.translation, tr.longdescription
1991 WHERE tr.language_id IN
1994 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1997 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1999 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2001 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2002 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2003 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2004 if (!$ref->{inventory_accno_id}) {
2005 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2007 delete($ref->{inventory_accno_id});
2009 foreach my $type (qw(inventory income expense)) {
2010 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2012 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2015 ($ref->{"${type}_accno"},
2016 $ref->{"${type}_new_chart"},
2017 $ref->{"${type}_valid"})
2018 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2022 if ($form->{payment_id} eq "") {
2023 $form->{payment_id} = $form->{part_payment_id};
2026 # get tax rates and description
2027 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2029 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2031 LEFT JOIN chart c ON (c.id = t.chart_id)
2035 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2037 ORDER BY startdate DESC
2040 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2041 my $stw = $dbh->prepare($query);
2042 $stw->execute(@values) || $form->dberror($query);
2044 $ref->{taxaccounts} = "";
2046 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2048 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2052 $ref->{taxaccounts} .= "$ptr->{accno} ";
2054 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2055 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2056 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2057 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2058 $form->{taxaccounts} .= "$ptr->{accno} ";
2064 chop $ref->{taxaccounts};
2066 if ($form->{language_id}) {
2067 for my $spec (@translation_queries) {
2068 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2069 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2070 next unless $translation;
2071 $ref->{description} = $translation;
2072 $ref->{longdescription} = $longdescription;
2077 $ref->{onhand} *= 1;
2079 push @{ $form->{item_list} }, $ref;
2082 $_->[1]->finish for @translation_queries;
2084 foreach my $item (@{ $form->{item_list} }) {
2085 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2086 trans_id => $item->{id},
2090 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2093 $main::lxdebug->leave_sub();
2096 ##########################
2097 # get pricegroups from database
2098 # build up selected pricegroup
2099 # if an exchange rate - change price
2102 sub get_pricegroups_for_parts {
2104 $main::lxdebug->enter_sub();
2106 my ($self, $myconfig, $form) = @_;
2108 my $dbh = $form->get_standard_dbh;
2110 $form->{"PRICES"} = {};
2114 my $all_units = AM->retrieve_units($myconfig, $form);
2115 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2116 $form->{"PRICES"}{$i} = [];
2118 $id = $form->{"id_$i"};
2120 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2121 $id = $form->{"new_id_$i"};
2124 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2126 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2128 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2129 my $sellprice = $form->{"sellprice_$i"};
2130 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2131 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2132 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2134 my $price_new = $form->{"price_new_$i"};
2135 my $price_old = $form->{"price_old_$i"};
2137 if (!$form->{"unit_old_$i"}) {
2138 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2139 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2140 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2141 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2144 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2145 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2146 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2148 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2149 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2150 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2151 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2152 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2153 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2154 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2159 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2160 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2161 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2162 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2163 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2167 if (!$form->{"basefactor_$i"}) {
2168 $form->{"basefactor_$i"} = 1;
2174 sellprice AS default_sellprice,
2177 'selected' AS selected
2183 parts.sellprice AS default_sellprice,
2184 pricegroup.pricegroup,
2188 LEFT JOIN parts ON parts.id = parts_id
2189 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2191 ORDER BY pricegroup|;
2192 my @values = (conv_i($id), conv_i($id));
2193 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2195 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2197 $pkr->{selected} = '';
2199 # if there is an exchange rate change price
2200 if (($form->{exchangerate} * 1) != 0) {
2201 $pkr->{price} /= $form->{exchangerate};
2204 $pkr->{price} *= $form->{"basefactor_$i"};
2205 $pkr->{price} *= $basefactor;
2206 $pkr->{price_ufmt} = $pkr->{price};
2207 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2209 if (!defined $selectedpricegroup_id) {
2210 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2211 # Case A: open old invoice, no pricegroup selected
2212 # Case B: add new article to invoice, no pricegroup selected
2214 # to distinguish case A and B the variable pricegroup_id_$i is used
2215 # for new articles this variable isn't defined, for loaded articles it is
2216 # sellprice can't be used, as it already has 0,00 set
2218 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2220 $pkr->{selected} = ' selected';
2221 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2222 and not defined $form->{"pricegroup_id_$i"}
2223 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2224 # for the case where pricegroup prices haven't been set
2226 # Case B: use default pricegroup of customer
2228 $pkr->{selected} = ' selected'; # unless $form->{selected};
2229 # no customer pricesgroup set
2230 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2232 $pkr->{price} = $form->{"sellprice_$i"};
2236 # this sub should not set anything and only return. --sschoeling, 20090506
2237 # is this correct? put in again... -- grichardson 20110119
2238 $form->{"sellprice_$i"} = $pkr->{price};
2241 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2242 $pkr->{price} = $form->{"sellprice_$i"};
2243 $pkr->{selected} = ' selected';
2247 # existing article: pricegroup or price changed
2248 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2249 if ($selectedpricegroup_id ne $pricegroup_old) {
2250 # pricegroup has changed
2251 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2252 $pkr->{selected} = ' selected';
2254 } elsif ( ($form->parse_amount($myconfig, $price_new)
2255 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2256 and ($price_new ne 0) and defined $price_new) {
2257 # sellprice has changed
2258 # when loading existing invoices $price_new is NULL
2259 if ($pkr->{pricegroup_id} == 0) {
2260 $pkr->{price} = $form->{"sellprice_$i"};
2261 $pkr->{selected} = ' selected';
2263 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2264 # neither sellprice nor pricegroup changed
2265 $pkr->{selected} = ' selected';
2266 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2267 # $pkr->{price} = $form->{"sellprice_$i"};
2269 $pkr->{price} = $form->{"sellprice_$i"};
2273 push @{ $form->{PRICES}{$i} }, $pkr;
2276 $form->{"basefactor_$i"} *= $basefactor;
2283 $main::lxdebug->leave_sub();
2287 $main::lxdebug->enter_sub();
2289 my ($self, $myconfig, $form, $table) = @_;
2291 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2293 # make sure there's no funny stuff in $table
2294 # ToDO: die when this happens and throw an error
2295 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2297 my $dbh = $form->get_standard_dbh;
2299 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2300 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2302 $main::lxdebug->leave_sub();
2308 $main::lxdebug->enter_sub();
2310 my ($self, $myconfig, $form, $table, $id) = @_;
2312 $main::lxdebug->leave_sub() and return 0 unless ($id);
2314 # make sure there's no funny stuff in $table
2315 # ToDO: die when this happens and throw an error
2316 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2318 my $dbh = $form->get_standard_dbh;
2320 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2321 my ($result) = selectrow_query($form, $dbh, $query, $id);
2323 $main::lxdebug->leave_sub();
2328 sub get_standard_accno_current_assets {
2329 $main::lxdebug->enter_sub();
2331 my ($self, $myconfig, $form) = @_;
2333 my $dbh = $form->get_standard_dbh;
2335 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2336 my ($result) = selectrow_query($form, $dbh, $query);
2338 $main::lxdebug->leave_sub();