1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing();
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
231 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
233 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
235 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
248 $form->{total} += $linetotal;
249 $form->{nodiscount_total} += $nodiscount_linetotal;
250 $form->{discount_total} += $discount;
252 if ($subtotal_header) {
253 $discount_subtotal += $linetotal;
254 $nodiscount_subtotal += $nodiscount_linetotal;
257 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
271 if (!$form->{"discount_$i"}) {
272 $nodiscount += $linetotal;
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
364 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
365 for @{ $ic_cvar_configs };
369 foreach my $item (sort keys %taxaccounts) {
370 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
375 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
377 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
382 for my $i (1 .. $form->{paidaccounts}) {
383 if ($form->{"paid_$i"}) {
384 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
388 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
389 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
392 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
395 if($form->{taxincluded}) {
396 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
397 $form->{subtotal_nofmt} = $form->{total} - $tax;
400 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
401 $form->{subtotal_nofmt} = $form->{total};
404 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
405 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
406 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
407 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
409 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
410 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
412 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
413 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
415 $form->set_payment_options($myconfig, $form->{invdate});
417 $form->{username} = $myconfig->{name};
419 $main::lxdebug->leave_sub();
422 sub project_description {
423 $main::lxdebug->enter_sub();
425 my ($self, $dbh, $id) = @_;
426 my $form = \%main::form;
428 my $query = qq|SELECT description FROM project WHERE id = ?|;
429 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
431 $main::lxdebug->leave_sub();
436 sub customer_details {
437 $main::lxdebug->enter_sub();
439 my ($self, $myconfig, $form, @wanted_vars) = @_;
441 # connect to database
442 my $dbh = $form->get_standard_dbh;
444 my $language_id = $form->{language_id};
446 # get contact id, set it if nessessary
449 my @values = (conv_i($form->{customer_id}));
452 if ($form->{cp_id}) {
453 $where = qq| AND (cp.cp_id = ?) |;
454 push(@values, conv_i($form->{cp_id}));
457 # get rest for the customer
459 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
460 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
463 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
464 WHERE (ct.id = ?) $where
467 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
469 # remove id and taxincluded before copy back
470 delete @$ref{qw(id taxincluded)};
472 @wanted_vars = grep({ $_ } @wanted_vars);
473 if (scalar(@wanted_vars) > 0) {
475 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
476 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
479 map { $form->{$_} = $ref->{$_} } keys %$ref;
481 # remove any trailing whitespace
482 $form->{currency} =~ s/\s*$// if ($form->{currency});
484 if ($form->{delivery_customer_id}) {
486 qq|SELECT *, notes as customernotes
490 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
492 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
495 if ($form->{delivery_vendor_id}) {
497 qq|SELECT *, notes as customernotes
501 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
503 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
506 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
508 'trans_id' => $form->{customer_id});
509 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
511 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
512 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
513 'language_id' => $language_id,
514 'allow_fallback' => 1);
517 $main::lxdebug->leave_sub();
521 $main::lxdebug->enter_sub();
523 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
525 # connect to database, turn off autocommit
526 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
528 my ($query, $sth, $null, $project_id, @values);
529 my $exchangerate = 0;
531 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
534 if (!$form->{employee_id}) {
535 $form->get_employee($dbh);
538 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
539 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
540 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
541 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
542 if (!$form->{department_id}){
543 ($null, $form->{department_id}) = split(/--/, $form->{department});
546 my $all_units = AM->retrieve_units($myconfig, $form);
548 if (!$payments_only) {
550 &reverse_invoice($dbh, $form);
553 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
554 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
556 $query = qq|SELECT nextval('glid')|;
557 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
559 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
560 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
562 if (!$form->{invnumber}) {
564 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
565 "cnnumber" : "invnumber", $dbh);
570 my ($netamount, $invoicediff) = (0, 0);
571 my ($amount, $linetotal, $lastincomeaccno);
573 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
574 my $defaultcurrency = (split m/:/, $currencies)[0];
576 if ($form->{currency} eq $defaultcurrency) {
577 $form->{exchangerate} = 1;
579 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
582 $form->{exchangerate} =
585 : $form->parse_amount($myconfig, $form->{exchangerate});
587 $form->{expense_inventory} = "";
591 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
592 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
595 $form->{amount} = {};
596 $form->{amount_cogs} = {};
598 foreach my $i (1 .. $form->{rowcount}) {
599 if ($form->{type} eq "credit_note") {
600 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
601 $form->{shipped} = 1;
603 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
608 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
609 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
610 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
612 if ($form->{storno}) {
613 $form->{"qty_$i"} *= -1;
616 if ($form->{"id_$i"}) {
619 if (defined($baseunits{$form->{"id_$i"}})) {
620 $item_unit = $baseunits{$form->{"id_$i"}};
623 $query = qq|SELECT unit FROM parts WHERE id = ?|;
624 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
625 $baseunits{$form->{"id_$i"}} = $item_unit;
628 if (defined($all_units->{$item_unit}->{factor})
629 && ($all_units->{$item_unit}->{factor} ne '')
630 && ($all_units->{$item_unit}->{factor} != 0)) {
631 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
635 $baseqty = $form->{"qty_$i"} * $basefactor;
637 my ($allocated, $taxrate) = (0, 0);
641 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
643 # keep entered selling price
645 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
647 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
649 my $decimalplaces = ($dec > 2) ? $dec : 2;
651 # undo discount formatting
652 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
655 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
657 # round linetotal to 2 decimal places
658 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
659 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
661 if ($form->{taxincluded}) {
662 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
663 $form->{"sellprice_$i"} =
664 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
666 $taxamount = $linetotal * $taxrate;
669 $netamount += $linetotal;
671 if ($taxamount != 0) {
673 $form->{amount}{ $form->{id} }{$_} +=
674 $taxamount * $form->{"${_}_rate"} / $taxrate
675 } split(/ /, $form->{"taxaccounts_$i"});
678 # add amount to income, $form->{amount}{trans_id}{accno}
679 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
681 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
682 $linetotal = $form->round_amount($linetotal, 2);
684 # this is the difference from the inventory
685 $invoicediff += ($amount - $linetotal);
687 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
690 $lastincomeaccno = $form->{"income_accno_$i"};
692 # adjust and round sellprice
693 $form->{"sellprice_$i"} =
694 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
697 next if $payments_only;
699 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
701 if ($form->{"assembly_$i"}) {
702 # record assembly item as allocated
703 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
706 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
710 # Get pricegroup_id and save it. Unfortunately the interface
711 # also uses ID "0" for signalling that none is selected, but "0"
712 # must not be stored in the database. Therefore we cannot simply
714 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
716 $pricegroup_id = undef if !$pricegroup_id;
718 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
720 # save detail record in invoice table
722 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
723 sellprice, fxsellprice, discount, allocated, assemblyitem,
724 unit, deliverydate, project_id, serialnumber, pricegroup_id,
725 ordnumber, transdate, cusordnumber, base_qty, subtotal,
726 marge_percent, marge_total, lastcost,
727 price_factor_id, price_factor, marge_price_factor)
728 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
729 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
731 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
732 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
733 $form->{"sellprice_$i"}, $fxsellprice,
734 $form->{"discount_$i"}, $allocated, 'f',
735 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
736 $form->{"serialnumber_$i"}, $pricegroup_id,
737 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
738 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
739 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
740 $form->{"lastcost_$i"},
741 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
742 conv_i($form->{"marge_price_factor_$i"}));
743 do_query($form, $dbh, $query, @values);
745 CVar->save_custom_variables(module => 'IC',
746 sub_module => 'invoice',
747 trans_id => $invoice_id,
748 configs => $ic_cvar_configs,
750 name_prefix => 'ic_',
751 name_postfix => "_$i",
756 # total payments, don't move we need it here
757 for my $i (1 .. $form->{paidaccounts}) {
758 if ($form->{type} eq "credit_note") {
759 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
761 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
763 $form->{paid} += $form->{"paid_$i"};
764 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
767 my ($tax, $diff) = (0, 0);
769 $netamount = $form->round_amount($netamount, 2);
771 # figure out rounding errors for total amount vs netamount + taxes
772 if ($form->{taxincluded}) {
774 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
775 $diff += $amount - $netamount * $form->{exchangerate};
776 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
780 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
781 $tax += $form->{amount}{ $form->{id} }{$item};
782 $netamount -= $form->{amount}{ $form->{id} }{$item};
785 $invoicediff += $diff;
786 ######## this only applies to tax included
787 if ($lastincomeaccno) {
788 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
792 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
793 $diff = $amount - $netamount * $form->{exchangerate};
794 $netamount = $amount;
795 foreach my $item (split(/ /, $form->{taxaccounts})) {
796 $form->{amount}{ $form->{id} }{$item} =
797 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
800 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
803 $amount - $form->{amount}{ $form->{id} }{$item} *
804 $form->{exchangerate};
805 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
806 $tax += $form->{amount}{ $form->{id} }{$item};
810 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
812 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
815 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
817 # update exchangerate
818 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
819 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
820 $form->{exchangerate}, 0);
823 $project_id = conv_i($form->{"globalproject_id"});
825 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
827 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
828 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
829 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
831 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
833 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
838 do_query($form, $dbh, $query, @values);
839 $form->{amount_cogs}{$trans_id}{$accno} = 0;
843 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
844 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
846 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
848 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
850 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
851 do_query($form, $dbh, $query, @values);
856 foreach my $trans_id (keys %{ $form->{amount} }) {
857 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
858 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
860 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
862 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
864 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
865 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
866 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
867 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
868 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
869 do_query($form, $dbh, $query, @values);
870 $form->{amount}{$trans_id}{$accno} = 0;
874 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
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)
880 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
881 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
882 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
883 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
884 do_query($form, $dbh, $query, @values);
889 # deduct payment differences from diff
890 for my $i (1 .. $form->{paidaccounts}) {
891 if ($form->{"paid_$i"} != 0) {
893 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
894 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
898 # record payments and offsetting AR
899 if (!$form->{storno}) {
900 for my $i (1 .. $form->{paidaccounts}) {
902 if ($form->{"acc_trans_id_$i"}
904 && (SL::DB::Default->get->payments_changeable == 0)) {
908 next if ($form->{"paid_$i"} == 0);
910 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
911 $form->{"datepaid_$i"} = $form->{invdate}
912 unless ($form->{"datepaid_$i"});
913 $form->{datepaid} = $form->{"datepaid_$i"};
917 if ($form->{currency} eq $defaultcurrency) {
918 $form->{"exchangerate_$i"} = 1;
920 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
921 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
925 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
927 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
929 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
930 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
931 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
932 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
933 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
934 do_query($form, $dbh, $query, @values);
938 $form->{"paid_$i"} *= -1;
939 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
942 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id)
943 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
944 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
945 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
946 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
947 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);
948 do_query($form, $dbh, $query, @values);
950 # exchangerate difference
951 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
952 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
956 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
957 $form->{"exchangerate_$i"};
959 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
961 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
966 # update exchange rate
967 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
968 $form->update_exchangerate($dbh, $form->{currency},
969 $form->{"datepaid_$i"},
970 $form->{"exchangerate_$i"}, 0);
974 } else { # if (!$form->{storno})
975 $form->{marge_total} *= -1;
978 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
980 # record exchange rate differences and gains/losses
981 foreach my $accno (keys %{ $form->{fx} }) {
982 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
983 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
984 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
987 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id)
988 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
989 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
990 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
991 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
992 do_query($form, $dbh, $query, @values);
997 if ($payments_only) {
998 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
999 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1001 $dbh->commit if !$provided_dbh;
1003 $main::lxdebug->leave_sub();
1007 $amount = $netamount + $tax;
1010 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1012 $query = qq|UPDATE ar set
1013 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1014 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1015 amount = ?, netamount = ?, paid = ?,
1016 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1017 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1018 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1019 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1020 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1021 cp_id = ?, marge_total = ?, marge_percent = ?,
1022 globalproject_id = ?, delivery_customer_id = ?,
1023 transaction_description = ?, delivery_vendor_id = ?,
1024 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1026 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1027 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1028 $amount, $netamount, $form->{"paid"},
1029 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1030 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1031 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1032 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1033 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1034 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1035 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1036 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1037 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1038 conv_i($form->{"id"}));
1039 do_query($form, $dbh, $query, @values);
1042 if ($form->{storno}) {
1045 paid = paid + amount,
1047 intnotes = ? || intnotes
1049 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1050 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1054 $form->{name} = $form->{customer};
1055 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1057 if (!$form->{shipto_id}) {
1058 $form->add_shipto($dbh, $form->{id}, "AR");
1061 # save printed, emailed and queued
1062 $form->save_status($dbh);
1064 Common::webdav_folder($form);
1066 # Link this record to the records it was created from.
1067 RecordLinks->create_links('dbh' => $dbh,
1069 'from_table' => 'oe',
1070 'from_ids' => $form->{convert_from_oe_ids},
1072 'to_id' => $form->{id},
1074 delete $form->{convert_from_oe_ids};
1076 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1078 if (scalar @convert_from_do_ids) {
1079 DO->close_orders('dbh' => $dbh,
1080 'ids' => \@convert_from_do_ids);
1082 RecordLinks->create_links('dbh' => $dbh,
1084 'from_table' => 'delivery_orders',
1085 'from_ids' => \@convert_from_do_ids,
1087 'to_id' => $form->{id},
1090 delete $form->{convert_from_do_ids};
1092 ARAP->close_orders_if_billed('dbh' => $dbh,
1093 'arap_id' => $form->{id},
1096 # safety check datev export
1097 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1098 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1099 $transdate ||= DateTime->today;
1101 my $datev = SL::DATEV->new(
1102 exporttype => DATEV_ET_BUCHUNGEN,
1103 format => DATEV_FORMAT_KNE,
1111 if ($datev->errors) {
1113 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1118 $dbh->commit if !$provided_dbh;
1120 $main::lxdebug->leave_sub();
1125 sub _delete_payments {
1126 $main::lxdebug->enter_sub();
1128 my ($self, $form, $dbh) = @_;
1130 my @delete_acc_trans_ids;
1132 # Delete old payment entries from acc_trans.
1134 qq|SELECT acc_trans_id
1136 WHERE (trans_id = ?) AND fx_transaction
1140 SELECT at.acc_trans_id
1142 LEFT JOIN chart c ON (at.chart_id = c.id)
1143 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1144 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1147 qq|SELECT at.acc_trans_id
1149 LEFT JOIN chart c ON (at.chart_id = c.id)
1150 WHERE (trans_id = ?)
1151 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1152 ORDER BY at.acc_trans_id
1154 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1156 if (@delete_acc_trans_ids) {
1157 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1158 do_query($form, $dbh, $query);
1161 $main::lxdebug->leave_sub();
1165 $main::lxdebug->enter_sub();
1167 my ($self, $myconfig, $form, $locale) = @_;
1169 # connect to database, turn off autocommit
1170 my $dbh = $form->get_standard_dbh;
1173 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1175 $old_form = save_form();
1177 # Delete all entries in acc_trans from prior payments.
1178 if (SL::DB::Default->get->payments_changeable != 0) {
1179 $self->_delete_payments($form, $dbh);
1182 # Save the new payments the user made before cleaning up $form.
1183 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 };
1185 # Clean up $form so that old content won't tamper the results.
1186 %keep_vars = map { $_, 1 } qw(login password id);
1187 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1189 # Retrieve the invoice from the database.
1190 $self->retrieve_invoice($myconfig, $form);
1192 # Set up the content of $form in the way that IS::post_invoice() expects.
1193 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1195 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1196 $item = $form->{invoice_details}->[$row - 1];
1198 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1200 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1203 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1205 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1207 # Restore the payment options from the user input.
1208 map { $form->{$_} = $payments{$_} } keys %payments;
1210 # Get the AR accno (which is normally done by Form::create_links()).
1214 LEFT JOIN chart c ON (at.chart_id = c.id)
1215 WHERE (trans_id = ?)
1216 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1217 ORDER BY at.acc_trans_id
1220 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1222 # Post the new payments.
1223 $self->post_invoice($myconfig, $form, $dbh, 1);
1225 restore_form($old_form);
1227 my $rc = $dbh->commit();
1229 $main::lxdebug->leave_sub();
1234 sub process_assembly {
1235 $main::lxdebug->enter_sub();
1237 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1240 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1241 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1243 JOIN parts p ON (a.parts_id = p.id)
1245 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1247 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1251 $ref->{inventory_accno_id} *= 1;
1252 $ref->{expense_accno_id} *= 1;
1254 # multiply by number of assemblies
1255 $ref->{qty} *= $totalqty;
1257 if ($ref->{assembly}) {
1258 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1261 if ($ref->{inventory_accno_id}) {
1262 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1266 # save detail record for individual assembly item in invoice table
1268 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1269 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1270 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1271 do_query($form, $dbh, $query, @values);
1277 $main::lxdebug->leave_sub();
1281 $main::lxdebug->enter_sub();
1283 # adjust allocated in table invoice according to FIFO princicple
1284 # for a certain part with part_id $id
1286 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1290 $form->{taxzone_id} *=1;
1291 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1292 my $taxzone_id = $form->{"taxzone_id"} * 1;
1294 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1295 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1296 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1297 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1298 FROM invoice i, parts p
1299 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1300 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1301 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1302 WHERE (i.parts_id = p.id)
1303 AND (i.parts_id = ?)
1304 AND ((i.base_qty + i.allocated) < 0)
1306 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1311 # all invoice entries of an example part:
1313 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1314 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1315 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1316 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1317 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1318 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1319 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1321 # 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
1322 # and all parts have been allocated
1324 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1325 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1327 # in this example there are still 4 unsold articles
1330 # search all invoice entries for the part in question, adjusting "allocated"
1331 # until the total number of sold parts has been reached
1333 # ORDER BY trans_id ensures FIFO
1336 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1337 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1341 # update allocated in invoice
1342 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1344 # total expenses and inventory
1345 # sellprice is the cost of the item
1346 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1348 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1349 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1350 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1352 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1353 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1354 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1356 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1357 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1363 last if (($totalqty -= $qty) <= 0);
1368 $main::lxdebug->leave_sub();
1373 sub reverse_invoice {
1374 $main::lxdebug->enter_sub();
1376 my ($dbh, $form) = @_;
1378 # reverse inventory items
1380 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1382 JOIN parts p ON (i.parts_id = p.id)
1383 WHERE i.trans_id = ?|;
1384 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1386 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1388 if ($ref->{inventory_accno_id}) {
1389 # de-allocated purchases
1391 qq|SELECT i.id, i.trans_id, i.allocated
1393 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1394 ORDER BY i.trans_id DESC|;
1395 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1397 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1398 my $qty = $ref->{qty};
1399 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1400 $qty = $inhref->{allocated};
1404 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1406 last if (($ref->{qty} -= $qty) <= 0);
1415 my @values = (conv_i($form->{id}));
1416 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1417 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1418 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1420 $main::lxdebug->leave_sub();
1423 sub delete_invoice {
1424 $main::lxdebug->enter_sub();
1426 my ($self, $myconfig, $form) = @_;
1428 # connect to database
1429 my $dbh = $form->get_standard_dbh;
1432 &reverse_invoice($dbh, $form);
1434 my @values = (conv_i($form->{id}));
1436 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1437 # zurückgesetzt werden. Vgl:
1438 # id | storno | storno_id | paid | amount
1439 #----+--------+-----------+---------+-----------
1440 # 18 | f | | 0.00000 | 119.00000
1442 # 18 | t | | 119.00000 | 119.00000
1444 if($form->{storno}){
1445 # storno_id auslesen und korrigieren
1446 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1447 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1450 # delete spool files
1451 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1454 qq|DELETE FROM status WHERE trans_id = ?|,
1455 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1456 qq|DELETE FROM ar WHERE id = ?|,
1459 map { do_query($form, $dbh, $_, @values) } @queries;
1461 my $rc = $dbh->commit;
1464 my $spool = $::lx_office_conf{paths}->{spool};
1465 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1468 $main::lxdebug->leave_sub();
1473 sub retrieve_invoice {
1474 $main::lxdebug->enter_sub();
1476 my ($self, $myconfig, $form) = @_;
1478 # connect to database
1479 my $dbh = $form->get_standard_dbh;
1481 my ($sth, $ref, $query);
1483 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1487 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1488 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1489 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1490 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1491 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1492 d.curr AS currencies
1496 $ref = selectfirst_hashref_query($form, $dbh, $query);
1497 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1500 my $id = conv_i($form->{id});
1503 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1507 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1508 a.orddate, a.quodate, a.globalproject_id,
1509 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1510 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1511 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1512 a.employee_id, a.salesman_id, a.payment_id,
1513 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1514 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1515 a.marge_total, a.marge_percent, a.direct_debit,
1518 LEFT JOIN employee e ON (e.id = a.employee_id)
1520 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1521 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1523 # remove any trailing whitespace
1524 $form->{currency} =~ s/\s*$//;
1526 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1529 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1530 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1532 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1534 foreach my $vc (qw(customer vendor)) {
1535 next if !$form->{"delivery_${vc}_id"};
1536 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1539 # get printed, emailed
1540 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1541 $sth = prepare_execute_query($form, $dbh, $query, $id);
1543 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1544 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1545 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1546 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1549 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1551 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1552 : $form->{invdate} ? $dbh->quote($form->{invdate})
1556 my $taxzone_id = $form->{taxzone_id} *= 1;
1557 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1559 # retrieve individual items
1562 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1563 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1564 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1567 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1568 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1569 i.price_factor_id, i.price_factor, i.marge_price_factor,
1570 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1571 pr.projectnumber, pg.partsgroup, prg.pricegroup
1574 LEFT JOIN parts p ON (i.parts_id = p.id)
1575 LEFT JOIN project pr ON (i.project_id = pr.id)
1576 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1577 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1579 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1580 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1581 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1583 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1585 $sth = prepare_execute_query($form, $dbh, $query, $id);
1587 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1588 # Retrieve custom variables.
1589 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1591 sub_module => 'invoice',
1592 trans_id => $ref->{invoice_id},
1594 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1595 delete $ref->{invoice_id};
1597 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1598 delete($ref->{"part_inventory_accno_id"});
1600 foreach my $type (qw(inventory income expense)) {
1601 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1602 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1603 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1607 # get tax rates and description
1608 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1610 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1611 LEFT JOIN chart c ON (c.id = t.chart_id)
1613 (SELECT tk.tax_id FROM taxkeys tk
1614 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1615 AND startdate <= date($transdate)
1616 ORDER BY startdate DESC LIMIT 1)
1618 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1619 $ref->{taxaccounts} = "";
1621 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1623 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1627 $ref->{taxaccounts} .= "$ptr->{accno} ";
1629 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1630 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1631 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1632 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1633 $form->{taxaccounts} .= "$ptr->{accno} ";
1638 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1640 chop $ref->{taxaccounts};
1641 push @{ $form->{invoice_details} }, $ref;
1646 Common::webdav_folder($form);
1649 my $rc = $dbh->commit;
1651 $main::lxdebug->leave_sub();
1657 $main::lxdebug->enter_sub();
1659 my ($self, $myconfig, $form) = @_;
1661 # connect to database
1662 my $dbh = $form->get_standard_dbh;
1664 my $dateformat = $myconfig->{dateformat};
1665 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1667 my (@values, $duedate, $ref, $query);
1669 if ($form->{invdate}) {
1670 $duedate = "to_date(?, '$dateformat')";
1671 push @values, $form->{invdate};
1673 $duedate = "current_date";
1676 my $cid = conv_i($form->{customer_id});
1679 if ($form->{payment_id}) {
1680 $payment_id = "(pt.id = ?) OR";
1681 push @values, conv_i($form->{payment_id});
1687 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1688 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1689 c.street, c.zipcode, c.city, c.country,
1690 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1691 c.taxincluded_checked, c.direct_debit,
1692 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1693 b.discount AS tradediscount, b.description AS business
1695 LEFT JOIN business b ON (b.id = c.business_id)
1696 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1699 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1701 delete $ref->{salesman_id} if !$ref->{salesman_id};
1703 map { $form->{$_} = $ref->{$_} } keys %$ref;
1705 # remove any trailing whitespace
1706 $form->{curr} =~ s/\s*$//;
1708 # use customer currency if not empty
1709 $form->{currency} = $form->{curr} if $form->{curr};
1712 qq|SELECT sum(amount - paid) AS dunning_amount
1714 WHERE (paid < amount)
1715 AND (customer_id = ?)
1716 AND (dunning_config_id IS NOT NULL)|;
1717 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1718 map { $form->{$_} = $ref->{$_} } keys %$ref;
1721 qq|SELECT dnn.dunning_description AS max_dunning_level
1722 FROM dunning_config dnn
1723 WHERE id IN (SELECT dunning_config_id
1725 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1726 ORDER BY dunning_level DESC LIMIT 1|;
1727 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1728 map { $form->{$_} = $ref->{$_} } keys %$ref;
1730 $form->{creditremaining} = $form->{creditlimit};
1731 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1732 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1733 $form->{creditremaining} -= $value;
1737 (SELECT e.buy FROM exchangerate e
1738 WHERE e.curr = o.curr
1739 AND e.transdate = o.transdate)
1741 WHERE o.customer_id = ?
1742 AND o.quotation = '0'
1743 AND o.closed = '0'|;
1744 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1746 while (my ($amount, $exch) = $sth->fetchrow_array) {
1747 $exch = 1 unless $exch;
1748 $form->{creditremaining} -= $amount * $exch;
1752 # get shipto if we did not converted an order or invoice
1753 if (!$form->{shipto}) {
1754 map { delete $form->{$_} }
1755 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1756 shiptostreet shiptozipcode shiptocity shiptocountry
1757 shiptocontact shiptophone shiptofax shiptoemail);
1759 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1760 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1762 map { $form->{$_} = $ref->{$_} } keys %$ref;
1765 # setup last accounts used for this customer
1766 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1768 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1770 JOIN acc_trans ac ON (ac.chart_id = c.id)
1771 JOIN ar a ON (a.id = ac.trans_id)
1772 WHERE a.customer_id = ?
1773 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1774 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1775 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1778 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1779 if ($ref->{category} eq 'I') {
1781 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1783 if ($form->{initial_transdate}) {
1785 qq|SELECT tk.tax_id, t.rate
1787 LEFT JOIN tax t ON tk.tax_id = t.id
1788 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1789 ORDER BY tk.startdate DESC
1791 my ($tax_id, $rate) =
1792 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1793 $form->{initial_transdate});
1794 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1797 if ($ref->{category} eq 'A') {
1798 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1802 $form->{rowcount} = $i if ($i && !$form->{type});
1805 $main::lxdebug->leave_sub();
1809 $main::lxdebug->enter_sub();
1811 my ($self, $myconfig, $form) = @_;
1813 # connect to database
1814 my $dbh = $form->get_standard_dbh;
1816 my $i = $form->{rowcount};
1818 my $where = qq|NOT p.obsolete = '1'|;
1821 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1822 my ($table, $field) = split m/\./, $column;
1823 next if !$form->{"${field}_${i}"};
1824 $where .= qq| AND lower(${column}) ILIKE ?|;
1825 push @values, '%' . $form->{"${field}_${i}"} . '%';
1828 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1829 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1830 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1831 push @values, $form->{"partnumber_$i"};
1834 # Search for part ID overrides all other criteria.
1835 if ($form->{"id_${i}"}) {
1836 $where = qq|p.id = ?|;
1837 @values = ($form->{"id_${i}"});
1840 if ($form->{"description_$i"}) {
1841 $where .= qq| ORDER BY p.description|;
1843 $where .= qq| ORDER BY p.partnumber|;
1847 if ($form->{type} eq "invoice") {
1849 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1850 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1854 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1858 my $taxzone_id = $form->{taxzone_id} * 1;
1859 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1863 p.id, p.partnumber, p.description, p.sellprice,
1864 p.listprice, p.inventory_accno_id, p.lastcost,
1866 c1.accno AS inventory_accno,
1867 c1.new_chart_id AS inventory_new_chart,
1868 date($transdate) - c1.valid_from AS inventory_valid,
1870 c2.accno AS income_accno,
1871 c2.new_chart_id AS income_new_chart,
1872 date($transdate) - c2.valid_from AS income_valid,
1874 c3.accno AS expense_accno,
1875 c3.new_chart_id AS expense_new_chart,
1876 date($transdate) - c3.valid_from AS expense_valid,
1878 p.unit, p.assembly, p.bin, p.onhand,
1879 p.notes AS partnotes, p.notes AS longdescription,
1880 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1883 pfac.factor AS price_factor,
1888 LEFT JOIN chart c1 ON
1889 ((SELECT inventory_accno_id
1890 FROM buchungsgruppen
1891 WHERE id = p.buchungsgruppen_id) = c1.id)
1892 LEFT JOIN chart c2 ON
1893 ((SELECT income_accno_id_${taxzone_id}
1894 FROM buchungsgruppen
1895 WHERE id = p.buchungsgruppen_id) = c2.id)
1896 LEFT JOIN chart c3 ON
1897 ((SELECT expense_accno_id_${taxzone_id}
1898 FROM buchungsgruppen
1899 WHERE id = p.buchungsgruppen_id) = c3.id)
1900 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1901 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1903 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1905 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1907 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1908 [ qq|SELECT tr.translation, tr.longdescription
1910 WHERE tr.language_id IN
1913 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1916 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1918 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1920 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1921 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1922 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1923 if (!$ref->{inventory_accno_id}) {
1924 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1926 delete($ref->{inventory_accno_id});
1928 foreach my $type (qw(inventory income expense)) {
1929 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1931 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1934 ($ref->{"${type}_accno"},
1935 $ref->{"${type}_new_chart"},
1936 $ref->{"${type}_valid"})
1937 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1941 if ($form->{payment_id} eq "") {
1942 $form->{payment_id} = $form->{part_payment_id};
1945 # get tax rates and description
1946 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1948 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1950 LEFT JOIN chart c ON (c.id = t.chart_id)
1954 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1956 ORDER BY startdate DESC
1959 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1960 my $stw = $dbh->prepare($query);
1961 $stw->execute(@values) || $form->dberror($query);
1963 $ref->{taxaccounts} = "";
1965 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1967 # if ($customertax{$ref->{accno}})
1968 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1972 $ref->{taxaccounts} .= "$ptr->{accno} ";
1974 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1975 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1976 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1977 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1978 $form->{taxaccounts} .= "$ptr->{accno} ";
1984 chop $ref->{taxaccounts};
1986 if ($form->{language_id}) {
1987 for my $spec (@translation_queries) {
1988 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1989 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1990 next unless $translation;
1991 $ref->{description} = $translation;
1992 $ref->{longdescription} = $longdescription;
1997 $ref->{onhand} *= 1;
1999 push @{ $form->{item_list} }, $ref;
2002 $_->[1]->finish for @translation_queries;
2004 foreach my $item (@{ $form->{item_list} }) {
2005 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2006 trans_id => $item->{id},
2010 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2013 $main::lxdebug->leave_sub();
2016 ##########################
2017 # get pricegroups from database
2018 # build up selected pricegroup
2019 # if an exchange rate - change price
2022 sub get_pricegroups_for_parts {
2024 $main::lxdebug->enter_sub();
2026 my ($self, $myconfig, $form) = @_;
2028 my $dbh = $form->get_standard_dbh;
2030 $form->{"PRICES"} = {};
2034 my $all_units = AM->retrieve_units($myconfig, $form);
2035 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2036 $form->{"PRICES"}{$i} = [];
2038 $id = $form->{"id_$i"};
2040 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2041 $id = $form->{"new_id_$i"};
2044 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2046 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2048 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2049 my $sellprice = $form->{"sellprice_$i"};
2050 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2051 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2052 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2054 my $price_new = $form->{"price_new_$i"};
2055 my $price_old = $form->{"price_old_$i"};
2057 if (!$form->{"unit_old_$i"}) {
2058 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2059 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2060 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2061 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2064 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2065 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2066 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2068 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2069 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2070 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2071 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2072 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2073 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2074 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2079 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2080 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2081 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2082 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2083 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2087 if (!$form->{"basefactor_$i"}) {
2088 $form->{"basefactor_$i"} = 1;
2094 sellprice AS default_sellprice,
2097 'selected' AS selected
2103 parts.sellprice AS default_sellprice,
2104 pricegroup.pricegroup,
2108 LEFT JOIN parts ON parts.id = parts_id
2109 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2111 ORDER BY pricegroup|;
2112 my @values = (conv_i($id), conv_i($id));
2113 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2115 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2117 $pkr->{selected} = '';
2119 # if there is an exchange rate change price
2120 if (($form->{exchangerate} * 1) != 0) {
2121 $pkr->{price} /= $form->{exchangerate};
2124 $pkr->{price} *= $form->{"basefactor_$i"};
2125 $pkr->{price} *= $basefactor;
2126 $pkr->{price_ufmt} = $pkr->{price};
2127 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2129 if (!defined $selectedpricegroup_id) {
2130 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2131 # Case A: open old invoice, no pricegroup selected
2132 # Case B: add new article to invoice, no pricegroup selected
2134 # to distinguish case A and B the variable pricegroup_id_$i is used
2135 # for new articles this variable isn't defined, for loaded articles it is
2136 # sellprice can't be used, as it already has 0,00 set
2138 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2140 $pkr->{selected} = ' selected';
2141 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2142 and not defined $form->{"pricegroup_id_$i"}
2143 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2144 # for the case where pricegroup prices haven't been set
2146 # Case B: use default pricegroup of customer
2148 $pkr->{selected} = ' selected'; # unless $form->{selected};
2149 # no customer pricesgroup set
2150 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2152 $pkr->{price} = $form->{"sellprice_$i"};
2156 # this sub should not set anything and only return. --sschoeling, 20090506
2157 # is this correct? put in again... -- grichardson 20110119
2158 $form->{"sellprice_$i"} = $pkr->{price};
2161 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2162 $pkr->{price} = $form->{"sellprice_$i"};
2163 $pkr->{selected} = ' selected';
2167 # existing article: pricegroup or price changed
2168 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2169 if ($selectedpricegroup_id ne $pricegroup_old) {
2170 # pricegroup has changed
2171 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2172 $pkr->{selected} = ' selected';
2174 } elsif ( ($form->parse_amount($myconfig, $price_new)
2175 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2176 and ($price_new ne 0) and defined $price_new) {
2177 # sellprice has changed
2178 # when loading existing invoices $price_new is NULL
2179 if ($pkr->{pricegroup_id} == 0) {
2180 $pkr->{price} = $form->{"sellprice_$i"};
2181 $pkr->{selected} = ' selected';
2183 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2184 # neither sellprice nor pricegroup changed
2185 $pkr->{selected} = ' selected';
2186 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2187 # $pkr->{price} = $form->{"sellprice_$i"};
2189 $pkr->{price} = $form->{"sellprice_$i"};
2193 push @{ $form->{PRICES}{$i} }, $pkr;
2196 $form->{"basefactor_$i"} *= $basefactor;
2203 $main::lxdebug->leave_sub();
2207 $main::lxdebug->enter_sub();
2209 my ($self, $myconfig, $form, $table) = @_;
2211 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2213 # make sure there's no funny stuff in $table
2214 # ToDO: die when this happens and throw an error
2215 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2217 my $dbh = $form->get_standard_dbh;
2219 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2220 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2222 $main::lxdebug->leave_sub();
2228 $main::lxdebug->enter_sub();
2230 my ($self, $myconfig, $form, $table, $id) = @_;
2232 $main::lxdebug->leave_sub() and return 0 unless ($id);
2234 # make sure there's no funny stuff in $table
2235 # ToDO: die when this happens and throw an error
2236 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2238 my $dbh = $form->get_standard_dbh;
2240 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2241 my ($result) = selectrow_query($form, $dbh, $query, $id);
2243 $main::lxdebug->leave_sub();
2248 sub get_standard_accno_current_assets {
2249 $main::lxdebug->enter_sub();
2251 my ($self, $myconfig, $form) = @_;
2253 my $dbh = $form->get_standard_dbh;
2255 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2256 my ($result) = selectrow_query($form, $dbh, $query);
2258 $main::lxdebug->leave_sub();