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);
434 $form->{username} = $myconfig->{name};
436 $main::lxdebug->leave_sub();
439 sub project_description {
440 $main::lxdebug->enter_sub();
442 my ($self, $dbh, $id) = @_;
443 my $form = \%main::form;
445 my $query = qq|SELECT description FROM project WHERE id = ?|;
446 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
448 $main::lxdebug->leave_sub();
453 sub customer_details {
454 $main::lxdebug->enter_sub();
456 my ($self, $myconfig, $form, @wanted_vars) = @_;
458 # connect to database
459 my $dbh = $form->get_standard_dbh;
461 my $language_id = $form->{language_id};
463 # get contact id, set it if nessessary
466 my @values = (conv_i($form->{customer_id}));
469 if ($form->{cp_id}) {
470 $where = qq| AND (cp.cp_id = ?) |;
471 push(@values, conv_i($form->{cp_id}));
474 # get rest for the customer
476 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
477 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
480 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
481 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
482 WHERE (ct.id = ?) $where
485 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
487 # remove id and taxincluded before copy back
488 delete @$ref{qw(id taxincluded)};
490 @wanted_vars = grep({ $_ } @wanted_vars);
491 if (scalar(@wanted_vars) > 0) {
493 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
494 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
497 map { $form->{$_} = $ref->{$_} } keys %$ref;
499 if ($form->{delivery_customer_id}) {
501 qq|SELECT *, notes as customernotes
505 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
507 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
510 if ($form->{delivery_vendor_id}) {
512 qq|SELECT *, notes as customernotes
516 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
518 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
521 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
523 'trans_id' => $form->{customer_id});
524 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
526 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
527 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
528 'language_id' => $language_id,
529 'allow_fallback' => 1);
532 $main::lxdebug->leave_sub();
536 $main::lxdebug->enter_sub();
538 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
540 # connect to database, turn off autocommit
541 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
543 my ($query, $sth, $null, $project_id, @values);
544 my $exchangerate = 0;
546 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
549 if (!$form->{employee_id}) {
550 $form->get_employee($dbh);
553 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
554 my $defaultcurrency = $form->{defaultcurrency};
556 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
557 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
558 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
559 if (!$form->{department_id}){
560 ($null, $form->{department_id}) = split(/--/, $form->{department});
563 my $all_units = AM->retrieve_units($myconfig, $form);
565 if (!$payments_only) {
567 &reverse_invoice($dbh, $form);
570 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
571 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
573 $query = qq|SELECT nextval('glid')|;
574 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
576 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
577 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
579 if (!$form->{invnumber}) {
581 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
582 "cnnumber" : "invnumber", $dbh);
587 my ($netamount, $invoicediff) = (0, 0);
588 my ($amount, $linetotal, $lastincomeaccno);
590 if ($form->{currency} eq $defaultcurrency) {
591 $form->{exchangerate} = 1;
593 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
596 $form->{exchangerate} =
599 : $form->parse_amount($myconfig, $form->{exchangerate});
601 $form->{expense_inventory} = "";
605 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
606 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
609 $form->{amount} = {};
610 $form->{amount_cogs} = {};
612 foreach my $i (1 .. $form->{rowcount}) {
613 if ($form->{type} eq "credit_note") {
614 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
615 $form->{shipped} = 1;
617 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
622 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
623 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
624 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
626 if ($form->{storno}) {
627 $form->{"qty_$i"} *= -1;
630 if ($form->{"id_$i"}) {
633 if (defined($baseunits{$form->{"id_$i"}})) {
634 $item_unit = $baseunits{$form->{"id_$i"}};
637 $query = qq|SELECT unit FROM parts WHERE id = ?|;
638 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
639 $baseunits{$form->{"id_$i"}} = $item_unit;
642 if (defined($all_units->{$item_unit}->{factor})
643 && ($all_units->{$item_unit}->{factor} ne '')
644 && ($all_units->{$item_unit}->{factor} != 0)) {
645 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
649 $baseqty = $form->{"qty_$i"} * $basefactor;
651 my ($allocated, $taxrate) = (0, 0);
655 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
657 # keep entered selling price
659 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
661 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
663 my $decimalplaces = ($dec > 2) ? $dec : 2;
665 # undo discount formatting
666 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
669 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
671 # round linetotal to 2 decimal places
672 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
673 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
675 if ($form->{taxincluded}) {
676 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
677 $form->{"sellprice_$i"} =
678 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
680 $taxamount = $linetotal * $taxrate;
683 $netamount += $linetotal;
685 if ($taxamount != 0) {
687 $form->{amount}{ $form->{id} }{$_} +=
688 $taxamount * $form->{"${_}_rate"} / $taxrate
689 } split(/ /, $form->{"taxaccounts_$i"});
692 # add amount to income, $form->{amount}{trans_id}{accno}
693 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
695 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
696 $linetotal = $form->round_amount($linetotal, 2);
698 # this is the difference from the inventory
699 $invoicediff += ($amount - $linetotal);
701 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
704 $lastincomeaccno = $form->{"income_accno_$i"};
706 # adjust and round sellprice
707 $form->{"sellprice_$i"} =
708 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
711 next if $payments_only;
713 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
715 if ($form->{"assembly_$i"}) {
716 # record assembly item as allocated
717 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
720 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
724 # Get pricegroup_id and save it. Unfortunately the interface
725 # also uses ID "0" for signalling that none is selected, but "0"
726 # must not be stored in the database. Therefore we cannot simply
728 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
730 $pricegroup_id = undef if !$pricegroup_id;
732 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
734 # save detail record in invoice table
736 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
737 sellprice, fxsellprice, discount, allocated, assemblyitem,
738 unit, deliverydate, project_id, serialnumber, pricegroup_id,
739 ordnumber, transdate, cusordnumber, base_qty, subtotal,
740 marge_percent, marge_total, lastcost,
741 price_factor_id, price_factor, marge_price_factor)
742 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
743 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
745 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
746 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
747 $form->{"sellprice_$i"}, $fxsellprice,
748 $form->{"discount_$i"}, $allocated, 'f',
749 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
750 $form->{"serialnumber_$i"}, $pricegroup_id,
751 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
752 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
753 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
754 $form->{"lastcost_$i"},
755 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
756 conv_i($form->{"marge_price_factor_$i"}));
757 do_query($form, $dbh, $query, @values);
759 CVar->save_custom_variables(module => 'IC',
760 sub_module => 'invoice',
761 trans_id => $invoice_id,
762 configs => $ic_cvar_configs,
764 name_prefix => 'ic_',
765 name_postfix => "_$i",
770 # total payments, don't move we need it here
771 for my $i (1 .. $form->{paidaccounts}) {
772 if ($form->{type} eq "credit_note") {
773 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
775 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
777 $form->{paid} += $form->{"paid_$i"};
778 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
781 my ($tax, $diff) = (0, 0);
783 $netamount = $form->round_amount($netamount, 2);
785 # figure out rounding errors for total amount vs netamount + taxes
786 if ($form->{taxincluded}) {
788 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
789 $diff += $amount - $netamount * $form->{exchangerate};
790 $netamount = $amount;
792 foreach my $item (split(/ /, $form->{taxaccounts})) {
793 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
794 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
795 $tax += $form->{amount}{ $form->{id} }{$item};
796 $netamount -= $form->{amount}{ $form->{id} }{$item};
799 $invoicediff += $diff;
800 ######## this only applies to tax included
801 if ($lastincomeaccno) {
802 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
806 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
807 $diff = $amount - $netamount * $form->{exchangerate};
808 $netamount = $amount;
809 foreach my $item (split(/ /, $form->{taxaccounts})) {
810 $form->{amount}{ $form->{id} }{$item} =
811 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
814 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
817 $amount - $form->{amount}{ $form->{id} }{$item} *
818 $form->{exchangerate};
819 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
820 $tax += $form->{amount}{ $form->{id} }{$item};
824 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
826 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
829 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
831 # update exchangerate
832 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
833 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
834 $form->{exchangerate}, 0);
837 $project_id = conv_i($form->{"globalproject_id"});
838 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
839 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
841 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
842 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
843 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
845 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
847 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
849 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
850 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
851 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
852 do_query($form, $dbh, $query, @values);
853 $form->{amount_cogs}{$trans_id}{$accno} = 0;
857 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
858 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
860 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
862 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
863 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
864 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
865 do_query($form, $dbh, $query, @values);
870 foreach my $trans_id (keys %{ $form->{amount} }) {
871 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
872 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
874 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
876 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
878 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
879 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
882 WHERE chart_id= (SELECT id
886 ORDER BY startdate DESC LIMIT 1),
889 WHERE chart_id= (SELECT id
893 ORDER BY startdate DESC LIMIT 1),
895 (SELECT link FROM chart WHERE accno = ?))|;
896 @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);
897 do_query($form, $dbh, $query, @values);
898 $form->{amount}{$trans_id}{$accno} = 0;
902 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
903 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
905 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
907 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
908 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
911 WHERE chart_id= (SELECT id
915 ORDER BY startdate DESC LIMIT 1),
918 WHERE chart_id= (SELECT id
922 ORDER BY startdate DESC LIMIT 1),
924 (SELECT link FROM chart WHERE accno = ?))|;
925 @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);
926 do_query($form, $dbh, $query, @values);
931 # deduct payment differences from diff
932 for my $i (1 .. $form->{paidaccounts}) {
933 if ($form->{"paid_$i"} != 0) {
935 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
936 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
940 # record payments and offsetting AR
941 if (!$form->{storno}) {
942 for my $i (1 .. $form->{paidaccounts}) {
944 if ($form->{"acc_trans_id_$i"}
946 && (SL::DB::Default->get->payments_changeable == 0)) {
950 next if ($form->{"paid_$i"} == 0);
952 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
953 $form->{"datepaid_$i"} = $form->{invdate}
954 unless ($form->{"datepaid_$i"});
955 $form->{datepaid} = $form->{"datepaid_$i"};
959 if ($form->{currency} eq $defaultcurrency) {
960 $form->{"exchangerate_$i"} = 1;
962 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
963 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
967 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
969 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
971 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
972 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
975 WHERE chart_id= (SELECT id
979 ORDER BY startdate DESC LIMIT 1),
982 WHERE chart_id= (SELECT id
986 ORDER BY startdate DESC LIMIT 1),
988 (SELECT link FROM chart WHERE accno = ?))|;
989 @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});
990 do_query($form, $dbh, $query, @values);
994 $form->{"paid_$i"} *= -1;
995 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
998 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
999 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1002 WHERE chart_id= (SELECT id
1006 ORDER BY startdate DESC LIMIT 1),
1009 WHERE chart_id= (SELECT id
1013 ORDER BY startdate DESC LIMIT 1),
1015 (SELECT link FROM chart WHERE accno = ?))|;
1016 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1017 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1018 do_query($form, $dbh, $query, @values);
1020 # exchangerate difference
1021 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1022 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1026 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1027 $form->{"exchangerate_$i"};
1029 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1031 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1036 # update exchange rate
1037 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1038 $form->update_exchangerate($dbh, $form->{currency},
1039 $form->{"datepaid_$i"},
1040 $form->{"exchangerate_$i"}, 0);
1044 } else { # if (!$form->{storno})
1045 $form->{marge_total} *= -1;
1048 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1050 # record exchange rate differences and gains/losses
1051 foreach my $accno (keys %{ $form->{fx} }) {
1052 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1053 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1054 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1057 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1058 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1061 WHERE chart_id= (SELECT id
1065 ORDER BY startdate DESC LIMIT 1),
1068 WHERE chart_id= (SELECT id
1072 ORDER BY startdate DESC LIMIT 1),
1074 (SELECT link FROM chart WHERE accno = ?))|;
1075 @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);
1076 do_query($form, $dbh, $query, @values);
1081 if ($payments_only) {
1082 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1083 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1085 $dbh->commit if !$provided_dbh;
1087 $main::lxdebug->leave_sub();
1091 $amount = $netamount + $tax;
1094 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1096 $query = qq|UPDATE ar set
1097 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1098 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1099 amount = ?, netamount = ?, paid = ?,
1100 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1101 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1102 currency_id = (SELECT id FROM currencies WHERE name = ?),
1103 department_id = ?, payment_id = ?, taxincluded = ?,
1104 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1105 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1106 cp_id = ?, marge_total = ?, marge_percent = ?,
1107 globalproject_id = ?, delivery_customer_id = ?,
1108 transaction_description = ?, delivery_vendor_id = ?,
1109 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1110 delivery_term_id = ?
1112 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1113 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1114 $amount, $netamount, $form->{"paid"},
1115 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1116 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1117 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1118 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1119 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1120 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1121 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1122 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1123 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1124 conv_i($form->{delivery_term_id}),
1125 conv_i($form->{"id"}));
1126 do_query($form, $dbh, $query, @values);
1129 if ($form->{storno}) {
1132 paid = paid + amount,
1134 intnotes = ? || intnotes
1136 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1137 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1141 $form->{name} = $form->{customer};
1142 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1144 if (!$form->{shipto_id}) {
1145 $form->add_shipto($dbh, $form->{id}, "AR");
1148 # save printed, emailed and queued
1149 $form->save_status($dbh);
1151 Common::webdav_folder($form);
1153 # Link this record to the records it was created from.
1154 RecordLinks->create_links('dbh' => $dbh,
1156 'from_table' => 'oe',
1157 'from_ids' => $form->{convert_from_oe_ids},
1159 'to_id' => $form->{id},
1161 delete $form->{convert_from_oe_ids};
1163 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1165 if (scalar @convert_from_do_ids) {
1166 DO->close_orders('dbh' => $dbh,
1167 'ids' => \@convert_from_do_ids);
1169 RecordLinks->create_links('dbh' => $dbh,
1171 'from_table' => 'delivery_orders',
1172 'from_ids' => \@convert_from_do_ids,
1174 'to_id' => $form->{id},
1177 delete $form->{convert_from_do_ids};
1179 ARAP->close_orders_if_billed('dbh' => $dbh,
1180 'arap_id' => $form->{id},
1183 # safety check datev export
1184 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1185 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1186 $transdate ||= DateTime->today;
1188 my $datev = SL::DATEV->new(
1189 exporttype => DATEV_ET_BUCHUNGEN,
1190 format => DATEV_FORMAT_KNE,
1194 trans_id => $form->{id},
1199 if ($datev->errors) {
1201 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1206 $dbh->commit if !$provided_dbh;
1208 $main::lxdebug->leave_sub();
1213 sub _delete_payments {
1214 $main::lxdebug->enter_sub();
1216 my ($self, $form, $dbh) = @_;
1218 my @delete_acc_trans_ids;
1220 # Delete old payment entries from acc_trans.
1222 qq|SELECT acc_trans_id
1224 WHERE (trans_id = ?) AND fx_transaction
1228 SELECT at.acc_trans_id
1230 LEFT JOIN chart c ON (at.chart_id = c.id)
1231 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1232 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1235 qq|SELECT at.acc_trans_id
1237 LEFT JOIN chart c ON (at.chart_id = c.id)
1238 WHERE (trans_id = ?)
1239 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1240 ORDER BY at.acc_trans_id
1242 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1244 if (@delete_acc_trans_ids) {
1245 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1246 do_query($form, $dbh, $query);
1249 $main::lxdebug->leave_sub();
1253 $main::lxdebug->enter_sub();
1255 my ($self, $myconfig, $form, $locale) = @_;
1257 # connect to database, turn off autocommit
1258 my $dbh = $form->get_standard_dbh;
1260 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1262 $old_form = save_form();
1264 # Delete all entries in acc_trans from prior payments.
1265 if (SL::DB::Default->get->payments_changeable != 0) {
1266 $self->_delete_payments($form, $dbh);
1269 # Save the new payments the user made before cleaning up $form.
1270 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 };
1272 # Clean up $form so that old content won't tamper the results.
1273 %keep_vars = map { $_, 1 } qw(login password id);
1274 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1276 # Retrieve the invoice from the database.
1277 $self->retrieve_invoice($myconfig, $form);
1279 # Set up the content of $form in the way that IS::post_invoice() expects.
1280 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1282 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1283 $item = $form->{invoice_details}->[$row - 1];
1285 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1287 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1290 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1292 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1294 # Restore the payment options from the user input.
1295 map { $form->{$_} = $payments{$_} } keys %payments;
1297 # Get the AR accno (which is normally done by Form::create_links()).
1301 LEFT JOIN chart c ON (at.chart_id = c.id)
1302 WHERE (trans_id = ?)
1303 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1304 ORDER BY at.acc_trans_id
1307 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1309 # Post the new payments.
1310 $self->post_invoice($myconfig, $form, $dbh, 1);
1312 restore_form($old_form);
1314 my $rc = $dbh->commit();
1316 $main::lxdebug->leave_sub();
1321 sub process_assembly {
1322 $main::lxdebug->enter_sub();
1324 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1327 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1328 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1330 JOIN parts p ON (a.parts_id = p.id)
1332 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1334 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1338 $ref->{inventory_accno_id} *= 1;
1339 $ref->{expense_accno_id} *= 1;
1341 # multiply by number of assemblies
1342 $ref->{qty} *= $totalqty;
1344 if ($ref->{assembly}) {
1345 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1348 if ($ref->{inventory_accno_id}) {
1349 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1353 # save detail record for individual assembly item in invoice table
1355 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1356 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1357 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1358 do_query($form, $dbh, $query, @values);
1364 $main::lxdebug->leave_sub();
1368 $main::lxdebug->enter_sub();
1370 # adjust allocated in table invoice according to FIFO princicple
1371 # for a certain part with part_id $id
1373 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1377 $form->{taxzone_id} *=1;
1378 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1379 my $taxzone_id = $form->{"taxzone_id"} * 1;
1381 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1382 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1383 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1384 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1385 FROM invoice i, parts p
1386 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1387 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1388 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1389 WHERE (i.parts_id = p.id)
1390 AND (i.parts_id = ?)
1391 AND ((i.base_qty + i.allocated) < 0)
1393 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1398 # all invoice entries of an example part:
1400 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1401 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1402 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1403 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1404 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1405 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1406 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1408 # 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
1409 # and all parts have been allocated
1411 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1412 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1414 # in this example there are still 4 unsold articles
1417 # search all invoice entries for the part in question, adjusting "allocated"
1418 # until the total number of sold parts has been reached
1420 # ORDER BY trans_id ensures FIFO
1423 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1424 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1428 # update allocated in invoice
1429 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1431 # total expenses and inventory
1432 # sellprice is the cost of the item
1433 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1435 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1436 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1437 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1439 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1440 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1441 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1443 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1444 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1450 last if (($totalqty -= $qty) <= 0);
1455 $main::lxdebug->leave_sub();
1460 sub reverse_invoice {
1461 $main::lxdebug->enter_sub();
1463 my ($dbh, $form) = @_;
1465 # reverse inventory items
1467 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1469 JOIN parts p ON (i.parts_id = p.id)
1470 WHERE i.trans_id = ?|;
1471 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1473 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1475 if ($ref->{inventory_accno_id}) {
1476 # de-allocated purchases
1478 qq|SELECT i.id, i.trans_id, i.allocated
1480 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1481 ORDER BY i.trans_id DESC|;
1482 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1484 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1485 my $qty = $ref->{qty};
1486 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1487 $qty = $inhref->{allocated};
1491 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1493 last if (($ref->{qty} -= $qty) <= 0);
1502 my @values = (conv_i($form->{id}));
1503 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1504 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1505 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1507 $main::lxdebug->leave_sub();
1510 sub delete_invoice {
1511 $main::lxdebug->enter_sub();
1513 my ($self, $myconfig, $form) = @_;
1515 # connect to database
1516 my $dbh = $form->get_standard_dbh;
1518 &reverse_invoice($dbh, $form);
1520 my @values = (conv_i($form->{id}));
1522 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1523 # zurückgesetzt werden. Vgl:
1524 # id | storno | storno_id | paid | amount
1525 #----+--------+-----------+---------+-----------
1526 # 18 | f | | 0.00000 | 119.00000
1528 # 18 | t | | 119.00000 | 119.00000
1530 if($form->{storno}){
1531 # storno_id auslesen und korrigieren
1532 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1533 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1536 # delete spool files
1537 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1540 qq|DELETE FROM status WHERE trans_id = ?|,
1541 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1542 qq|DELETE FROM ar WHERE id = ?|,
1545 map { do_query($form, $dbh, $_, @values) } @queries;
1547 my $rc = $dbh->commit;
1550 my $spool = $::lx_office_conf{paths}->{spool};
1551 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1554 $main::lxdebug->leave_sub();
1559 sub retrieve_invoice {
1560 $main::lxdebug->enter_sub();
1562 my ($self, $myconfig, $form) = @_;
1564 # connect to database
1565 my $dbh = $form->get_standard_dbh;
1567 my ($sth, $ref, $query);
1569 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1573 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1574 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1575 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1576 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1577 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1581 $ref = selectfirst_hashref_query($form, $dbh, $query);
1582 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1585 my $id = conv_i($form->{id});
1588 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1592 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1593 a.orddate, a.quodate, a.globalproject_id,
1594 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1595 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1596 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1597 a.employee_id, a.salesman_id, a.payment_id,
1598 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1599 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1600 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1603 LEFT JOIN employee e ON (e.id = a.employee_id)
1605 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1606 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1608 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1611 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1612 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1614 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1616 foreach my $vc (qw(customer vendor)) {
1617 next if !$form->{"delivery_${vc}_id"};
1618 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1621 # get printed, emailed
1622 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1623 $sth = prepare_execute_query($form, $dbh, $query, $id);
1625 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1626 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1627 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1628 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1631 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1633 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1634 : $form->{invdate} ? $dbh->quote($form->{invdate})
1638 my $taxzone_id = $form->{taxzone_id} *= 1;
1639 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1641 # retrieve individual items
1644 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1645 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1646 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1649 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1650 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1651 i.price_factor_id, i.price_factor, i.marge_price_factor,
1652 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1653 pr.projectnumber, pg.partsgroup, prg.pricegroup
1656 LEFT JOIN parts p ON (i.parts_id = p.id)
1657 LEFT JOIN project pr ON (i.project_id = pr.id)
1658 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1659 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1661 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1662 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1663 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1665 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1667 $sth = prepare_execute_query($form, $dbh, $query, $id);
1669 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1670 # Retrieve custom variables.
1671 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1673 sub_module => 'invoice',
1674 trans_id => $ref->{invoice_id},
1676 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1677 delete $ref->{invoice_id};
1679 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1680 delete($ref->{"part_inventory_accno_id"});
1682 foreach my $type (qw(inventory income expense)) {
1683 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1684 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1685 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1689 # get tax rates and description
1690 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1692 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1693 LEFT JOIN chart c ON (c.id = t.chart_id)
1695 (SELECT tk.tax_id FROM taxkeys tk
1696 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1697 AND startdate <= date($transdate)
1698 ORDER BY startdate DESC LIMIT 1)
1700 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1701 $ref->{taxaccounts} = "";
1703 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1705 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1709 $ref->{taxaccounts} .= "$ptr->{accno} ";
1711 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1712 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1713 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1714 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1715 $form->{taxaccounts} .= "$ptr->{accno} ";
1720 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1722 chop $ref->{taxaccounts};
1723 push @{ $form->{invoice_details} }, $ref;
1728 Common::webdav_folder($form);
1731 my $rc = $dbh->commit;
1733 $main::lxdebug->leave_sub();
1739 $main::lxdebug->enter_sub();
1741 my ($self, $myconfig, $form) = @_;
1743 # connect to database
1744 my $dbh = $form->get_standard_dbh;
1746 my $dateformat = $myconfig->{dateformat};
1747 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1749 my (@values, $duedate, $ref, $query);
1751 if ($form->{invdate}) {
1752 $duedate = "to_date(?, '$dateformat')";
1753 push @values, $form->{invdate};
1755 $duedate = "current_date";
1758 my $cid = conv_i($form->{customer_id});
1761 if ($form->{payment_id}) {
1762 $payment_id = "(pt.id = ?) OR";
1763 push @values, conv_i($form->{payment_id});
1769 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1770 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1771 c.street, c.zipcode, c.city, c.country,
1772 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1773 c.taxincluded_checked, c.direct_debit,
1774 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1775 b.discount AS tradediscount, b.description AS business
1777 LEFT JOIN business b ON (b.id = c.business_id)
1778 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1779 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1782 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1784 delete $ref->{salesman_id} if !$ref->{salesman_id};
1786 map { $form->{$_} = $ref->{$_} } keys %$ref;
1788 # use customer currency
1789 $form->{currency} = $form->{curr};
1792 qq|SELECT sum(amount - paid) AS dunning_amount
1794 WHERE (paid < amount)
1795 AND (customer_id = ?)
1796 AND (dunning_config_id IS NOT NULL)|;
1797 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1798 map { $form->{$_} = $ref->{$_} } keys %$ref;
1801 qq|SELECT dnn.dunning_description AS max_dunning_level
1802 FROM dunning_config dnn
1803 WHERE id IN (SELECT dunning_config_id
1805 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1806 ORDER BY dunning_level DESC LIMIT 1|;
1807 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1808 map { $form->{$_} = $ref->{$_} } keys %$ref;
1810 $form->{creditremaining} = $form->{creditlimit};
1811 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1812 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1813 $form->{creditremaining} -= $value;
1817 (SELECT e.buy FROM exchangerate e
1818 WHERE e.currency_id = o.currency_id
1819 AND e.transdate = o.transdate)
1821 WHERE o.customer_id = ?
1822 AND o.quotation = '0'
1823 AND o.closed = '0'|;
1824 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1826 while (my ($amount, $exch) = $sth->fetchrow_array) {
1827 $exch = 1 unless $exch;
1828 $form->{creditremaining} -= $amount * $exch;
1832 # get shipto if we did not converted an order or invoice
1833 if (!$form->{shipto}) {
1834 map { delete $form->{$_} }
1835 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1836 shiptostreet shiptozipcode shiptocity shiptocountry
1837 shiptocontact shiptophone shiptofax shiptoemail);
1839 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1840 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1842 map { $form->{$_} = $ref->{$_} } keys %$ref;
1845 # setup last accounts used for this customer
1846 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1848 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1850 JOIN acc_trans ac ON (ac.chart_id = c.id)
1851 JOIN ar a ON (a.id = ac.trans_id)
1852 WHERE a.customer_id = ?
1853 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1854 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1855 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1858 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1859 if ($ref->{category} eq 'I') {
1861 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1863 if ($form->{initial_transdate}) {
1865 qq|SELECT tk.tax_id, t.rate
1867 LEFT JOIN tax t ON tk.tax_id = t.id
1868 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1869 ORDER BY tk.startdate DESC
1871 my ($tax_id, $rate) =
1872 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1873 $form->{initial_transdate});
1874 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1877 if ($ref->{category} eq 'A') {
1878 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1882 $form->{rowcount} = $i if ($i && !$form->{type});
1885 $main::lxdebug->leave_sub();
1889 $main::lxdebug->enter_sub();
1891 my ($self, $myconfig, $form) = @_;
1893 # connect to database
1894 my $dbh = $form->get_standard_dbh;
1896 my $i = $form->{rowcount};
1898 my $where = qq|NOT p.obsolete = '1'|;
1901 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1902 my ($table, $field) = split m/\./, $column;
1903 next if !$form->{"${field}_${i}"};
1904 $where .= qq| AND lower(${column}) ILIKE ?|;
1905 push @values, '%' . $form->{"${field}_${i}"} . '%';
1908 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1909 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1910 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1911 push @values, $form->{"partnumber_$i"};
1914 # Search for part ID overrides all other criteria.
1915 if ($form->{"id_${i}"}) {
1916 $where = qq|p.id = ?|;
1917 @values = ($form->{"id_${i}"});
1920 if ($form->{"description_$i"}) {
1921 $where .= qq| ORDER BY p.description|;
1923 $where .= qq| ORDER BY p.partnumber|;
1927 if ($form->{type} eq "invoice") {
1929 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1930 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1934 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1938 my $taxzone_id = $form->{taxzone_id} * 1;
1939 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1943 p.id, p.partnumber, p.description, p.sellprice,
1944 p.listprice, p.inventory_accno_id, p.lastcost,
1946 c1.accno AS inventory_accno,
1947 c1.new_chart_id AS inventory_new_chart,
1948 date($transdate) - c1.valid_from AS inventory_valid,
1950 c2.accno AS income_accno,
1951 c2.new_chart_id AS income_new_chart,
1952 date($transdate) - c2.valid_from AS income_valid,
1954 c3.accno AS expense_accno,
1955 c3.new_chart_id AS expense_new_chart,
1956 date($transdate) - c3.valid_from AS expense_valid,
1958 p.unit, p.assembly, p.onhand,
1959 p.notes AS partnotes, p.notes AS longdescription,
1960 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1961 p.price_factor_id, p.weight,
1963 pfac.factor AS price_factor,
1968 LEFT JOIN chart c1 ON
1969 ((SELECT inventory_accno_id
1970 FROM buchungsgruppen
1971 WHERE id = p.buchungsgruppen_id) = c1.id)
1972 LEFT JOIN chart c2 ON
1973 ((SELECT income_accno_id_${taxzone_id}
1974 FROM buchungsgruppen
1975 WHERE id = p.buchungsgruppen_id) = c2.id)
1976 LEFT JOIN chart c3 ON
1977 ((SELECT expense_accno_id_${taxzone_id}
1978 FROM buchungsgruppen
1979 WHERE id = p.buchungsgruppen_id) = c3.id)
1980 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1981 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1983 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1985 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1987 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1988 [ qq|SELECT tr.translation, tr.longdescription
1990 WHERE tr.language_id IN
1993 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1996 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1998 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2000 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2001 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2002 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2003 if (!$ref->{inventory_accno_id}) {
2004 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2006 delete($ref->{inventory_accno_id});
2008 foreach my $type (qw(inventory income expense)) {
2009 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2011 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2014 ($ref->{"${type}_accno"},
2015 $ref->{"${type}_new_chart"},
2016 $ref->{"${type}_valid"})
2017 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2021 if ($form->{payment_id} eq "") {
2022 $form->{payment_id} = $form->{part_payment_id};
2025 # get tax rates and description
2026 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2028 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2030 LEFT JOIN chart c ON (c.id = t.chart_id)
2034 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2036 ORDER BY startdate DESC
2039 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2040 my $stw = $dbh->prepare($query);
2041 $stw->execute(@values) || $form->dberror($query);
2043 $ref->{taxaccounts} = "";
2045 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2047 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2051 $ref->{taxaccounts} .= "$ptr->{accno} ";
2053 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2054 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2055 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2056 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2057 $form->{taxaccounts} .= "$ptr->{accno} ";
2063 chop $ref->{taxaccounts};
2065 if ($form->{language_id}) {
2066 for my $spec (@translation_queries) {
2067 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2068 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2069 next unless $translation;
2070 $ref->{description} = $translation;
2071 $ref->{longdescription} = $longdescription;
2076 $ref->{onhand} *= 1;
2078 push @{ $form->{item_list} }, $ref;
2081 $_->[1]->finish for @translation_queries;
2083 foreach my $item (@{ $form->{item_list} }) {
2084 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2085 trans_id => $item->{id},
2089 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2092 $main::lxdebug->leave_sub();
2095 ##########################
2096 # get pricegroups from database
2097 # build up selected pricegroup
2098 # if an exchange rate - change price
2101 sub get_pricegroups_for_parts {
2103 $main::lxdebug->enter_sub();
2105 my ($self, $myconfig, $form) = @_;
2107 my $dbh = $form->get_standard_dbh;
2109 $form->{"PRICES"} = {};
2113 my $all_units = AM->retrieve_units($myconfig, $form);
2114 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2115 $form->{"PRICES"}{$i} = [];
2117 $id = $form->{"id_$i"};
2119 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2120 $id = $form->{"new_id_$i"};
2123 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2125 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2127 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2128 my $sellprice = $form->{"sellprice_$i"};
2129 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2130 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2131 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2133 my $price_new = $form->{"price_new_$i"};
2134 my $price_old = $form->{"price_old_$i"};
2136 if (!$form->{"unit_old_$i"}) {
2137 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2138 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2139 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2140 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2143 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2144 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2145 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2147 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2148 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2149 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2150 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2151 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2152 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2153 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2158 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2159 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2160 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2161 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2162 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2166 if (!$form->{"basefactor_$i"}) {
2167 $form->{"basefactor_$i"} = 1;
2173 sellprice AS default_sellprice,
2176 'selected' AS selected
2182 parts.sellprice AS default_sellprice,
2183 pricegroup.pricegroup,
2187 LEFT JOIN parts ON parts.id = parts_id
2188 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2190 ORDER BY pricegroup|;
2191 my @values = (conv_i($id), conv_i($id));
2192 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2194 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2196 $pkr->{selected} = '';
2198 # if there is an exchange rate change price
2199 if (($form->{exchangerate} * 1) != 0) {
2200 $pkr->{price} /= $form->{exchangerate};
2203 $pkr->{price} *= $form->{"basefactor_$i"};
2204 $pkr->{price} *= $basefactor;
2205 $pkr->{price_ufmt} = $pkr->{price};
2206 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2208 if (!defined $selectedpricegroup_id) {
2209 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2210 # Case A: open old invoice, no pricegroup selected
2211 # Case B: add new article to invoice, no pricegroup selected
2213 # to distinguish case A and B the variable pricegroup_id_$i is used
2214 # for new articles this variable isn't defined, for loaded articles it is
2215 # sellprice can't be used, as it already has 0,00 set
2217 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2219 $pkr->{selected} = ' selected';
2220 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2221 and not defined $form->{"pricegroup_id_$i"}
2222 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2223 # for the case where pricegroup prices haven't been set
2225 # Case B: use default pricegroup of customer
2227 $pkr->{selected} = ' selected'; # unless $form->{selected};
2228 # no customer pricesgroup set
2229 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2231 $pkr->{price} = $form->{"sellprice_$i"};
2235 # this sub should not set anything and only return. --sschoeling, 20090506
2236 # is this correct? put in again... -- grichardson 20110119
2237 $form->{"sellprice_$i"} = $pkr->{price};
2240 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2241 $pkr->{price} = $form->{"sellprice_$i"};
2242 $pkr->{selected} = ' selected';
2246 # existing article: pricegroup or price changed
2247 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2248 if ($selectedpricegroup_id ne $pricegroup_old) {
2249 # pricegroup has changed
2250 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2251 $pkr->{selected} = ' selected';
2253 } elsif ( ($form->parse_amount($myconfig, $price_new)
2254 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2255 and ($price_new ne 0) and defined $price_new) {
2256 # sellprice has changed
2257 # when loading existing invoices $price_new is NULL
2258 if ($pkr->{pricegroup_id} == 0) {
2259 $pkr->{price} = $form->{"sellprice_$i"};
2260 $pkr->{selected} = ' selected';
2262 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2263 # neither sellprice nor pricegroup changed
2264 $pkr->{selected} = ' selected';
2265 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2266 # $pkr->{price} = $form->{"sellprice_$i"};
2268 $pkr->{price} = $form->{"sellprice_$i"};
2272 push @{ $form->{PRICES}{$i} }, $pkr;
2275 $form->{"basefactor_$i"} *= $basefactor;
2282 $main::lxdebug->leave_sub();
2286 $main::lxdebug->enter_sub();
2288 my ($self, $myconfig, $form, $table) = @_;
2290 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2292 # make sure there's no funny stuff in $table
2293 # ToDO: die when this happens and throw an error
2294 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2296 my $dbh = $form->get_standard_dbh;
2298 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2299 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2301 $main::lxdebug->leave_sub();
2307 $main::lxdebug->enter_sub();
2309 my ($self, $myconfig, $form, $table, $id) = @_;
2311 $main::lxdebug->leave_sub() and return 0 unless ($id);
2313 # make sure there's no funny stuff in $table
2314 # ToDO: die when this happens and throw an error
2315 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2317 my $dbh = $form->get_standard_dbh;
2319 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2320 my ($result) = selectrow_query($form, $dbh, $query, $id);
2322 $main::lxdebug->leave_sub();
2327 sub get_standard_accno_current_assets {
2328 $main::lxdebug->enter_sub();
2330 my ($self, $myconfig, $form) = @_;
2332 my $dbh = $form->get_standard_dbh;
2334 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2335 my ($result) = selectrow_query($form, $dbh, $query);
2337 $main::lxdebug->leave_sub();