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
711 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
714 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
716 # save detail record in invoice table
718 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
719 sellprice, fxsellprice, discount, allocated, assemblyitem,
720 unit, deliverydate, project_id, serialnumber, pricegroup_id,
721 ordnumber, transdate, cusordnumber, base_qty, subtotal,
722 marge_percent, marge_total, lastcost,
723 price_factor_id, price_factor, marge_price_factor)
724 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
725 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
727 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
728 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
729 $form->{"sellprice_$i"}, $fxsellprice,
730 $form->{"discount_$i"}, $allocated, 'f',
731 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
732 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
733 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
734 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
735 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
736 $form->{"lastcost_$i"},
737 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
738 conv_i($form->{"marge_price_factor_$i"}));
739 do_query($form, $dbh, $query, @values);
741 CVar->save_custom_variables(module => 'IC',
742 sub_module => 'invoice',
743 trans_id => $invoice_id,
744 configs => $ic_cvar_configs,
746 name_prefix => 'ic_',
747 name_postfix => "_$i",
752 # total payments, don't move we need it here
753 for my $i (1 .. $form->{paidaccounts}) {
754 if ($form->{type} eq "credit_note") {
755 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
757 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
759 $form->{paid} += $form->{"paid_$i"};
760 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
763 my ($tax, $diff) = (0, 0);
765 $netamount = $form->round_amount($netamount, 2);
767 # figure out rounding errors for total amount vs netamount + taxes
768 if ($form->{taxincluded}) {
770 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
771 $diff += $amount - $netamount * $form->{exchangerate};
772 $netamount = $amount;
774 foreach my $item (split(/ /, $form->{taxaccounts})) {
775 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
776 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
777 $tax += $form->{amount}{ $form->{id} }{$item};
778 $netamount -= $form->{amount}{ $form->{id} }{$item};
781 $invoicediff += $diff;
782 ######## this only applies to tax included
783 if ($lastincomeaccno) {
784 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
788 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
789 $diff = $amount - $netamount * $form->{exchangerate};
790 $netamount = $amount;
791 foreach my $item (split(/ /, $form->{taxaccounts})) {
792 $form->{amount}{ $form->{id} }{$item} =
793 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
796 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
799 $amount - $form->{amount}{ $form->{id} }{$item} *
800 $form->{exchangerate};
801 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
802 $tax += $form->{amount}{ $form->{id} }{$item};
806 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
808 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
811 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
813 # update exchangerate
814 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
815 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
816 $form->{exchangerate}, 0);
819 $project_id = conv_i($form->{"globalproject_id"});
821 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
823 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
824 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
825 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
827 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
829 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
833 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
834 do_query($form, $dbh, $query, @values);
835 $form->{amount_cogs}{$trans_id}{$accno} = 0;
839 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
840 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
842 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
844 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
845 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
846 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
847 do_query($form, $dbh, $query, @values);
852 foreach my $trans_id (keys %{ $form->{amount} }) {
853 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
854 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
856 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
858 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
860 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
861 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
862 (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
863 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
864 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
865 do_query($form, $dbh, $query, @values);
866 $form->{amount}{$trans_id}{$accno} = 0;
870 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
871 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
873 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
875 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
876 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
877 (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
878 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
879 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
880 do_query($form, $dbh, $query, @values);
885 # deduct payment differences from diff
886 for my $i (1 .. $form->{paidaccounts}) {
887 if ($form->{"paid_$i"} != 0) {
889 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
890 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
894 # record payments and offsetting AR
895 if (!$form->{storno}) {
896 for my $i (1 .. $form->{paidaccounts}) {
898 if ($form->{"acc_trans_id_$i"}
900 && (SL::DB::Default->get->payments_changeable == 0)) {
904 next if ($form->{"paid_$i"} == 0);
906 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
907 $form->{"datepaid_$i"} = $form->{invdate}
908 unless ($form->{"datepaid_$i"});
909 $form->{datepaid} = $form->{"datepaid_$i"};
913 if ($form->{currency} eq $defaultcurrency) {
914 $form->{"exchangerate_$i"} = 1;
916 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
917 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
921 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
923 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
925 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
926 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
927 (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
928 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
929 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
930 do_query($form, $dbh, $query, @values);
934 $form->{"paid_$i"} *= -1;
935 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
938 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id)
939 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
940 (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
941 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
942 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
943 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);
944 do_query($form, $dbh, $query, @values);
946 # exchangerate difference
947 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
948 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
952 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
953 $form->{"exchangerate_$i"};
955 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
957 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
962 # update exchange rate
963 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
964 $form->update_exchangerate($dbh, $form->{currency},
965 $form->{"datepaid_$i"},
966 $form->{"exchangerate_$i"}, 0);
970 } else { # if (!$form->{storno})
971 $form->{marge_total} *= -1;
974 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
976 # record exchange rate differences and gains/losses
977 foreach my $accno (keys %{ $form->{fx} }) {
978 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
979 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
980 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
983 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id)
984 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
985 (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
986 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
987 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
988 do_query($form, $dbh, $query, @values);
993 if ($payments_only) {
994 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
995 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
997 $dbh->commit if !$provided_dbh;
999 $main::lxdebug->leave_sub();
1003 $amount = $netamount + $tax;
1006 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1008 $query = qq|UPDATE ar set
1009 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1010 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1011 amount = ?, netamount = ?, paid = ?,
1012 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1013 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1014 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1015 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1016 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1017 cp_id = ?, marge_total = ?, marge_percent = ?,
1018 globalproject_id = ?, delivery_customer_id = ?,
1019 transaction_description = ?, delivery_vendor_id = ?,
1020 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1022 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1023 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1024 $amount, $netamount, $form->{"paid"},
1025 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1026 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1027 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1028 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1029 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1030 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1031 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1032 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1033 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1034 conv_i($form->{"id"}));
1035 do_query($form, $dbh, $query, @values);
1038 if ($form->{storno}) {
1041 paid = paid + amount,
1043 intnotes = ? || intnotes
1045 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1046 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1050 $form->{name} = $form->{customer};
1051 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1053 if (!$form->{shipto_id}) {
1054 $form->add_shipto($dbh, $form->{id}, "AR");
1057 # save printed, emailed and queued
1058 $form->save_status($dbh);
1060 Common::webdav_folder($form);
1062 # Link this record to the records it was created from.
1063 RecordLinks->create_links('dbh' => $dbh,
1065 'from_table' => 'oe',
1066 'from_ids' => $form->{convert_from_oe_ids},
1068 'to_id' => $form->{id},
1070 delete $form->{convert_from_oe_ids};
1072 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1074 if (scalar @convert_from_do_ids) {
1075 DO->close_orders('dbh' => $dbh,
1076 'ids' => \@convert_from_do_ids);
1078 RecordLinks->create_links('dbh' => $dbh,
1080 'from_table' => 'delivery_orders',
1081 'from_ids' => \@convert_from_do_ids,
1083 'to_id' => $form->{id},
1086 delete $form->{convert_from_do_ids};
1088 ARAP->close_orders_if_billed('dbh' => $dbh,
1089 'arap_id' => $form->{id},
1092 # safety check datev export
1093 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1094 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1095 $transdate ||= DateTime->today;
1097 my $datev = SL::DATEV->new(
1098 exporttype => DATEV_ET_BUCHUNGEN,
1099 format => DATEV_FORMAT_KNE,
1107 if ($datev->errors) {
1109 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1114 $dbh->commit if !$provided_dbh;
1116 $main::lxdebug->leave_sub();
1121 sub _delete_payments {
1122 $main::lxdebug->enter_sub();
1124 my ($self, $form, $dbh) = @_;
1126 my @delete_acc_trans_ids;
1128 # Delete old payment entries from acc_trans.
1130 qq|SELECT acc_trans_id
1132 WHERE (trans_id = ?) AND fx_transaction
1136 SELECT at.acc_trans_id
1138 LEFT JOIN chart c ON (at.chart_id = c.id)
1139 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1140 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1143 qq|SELECT at.acc_trans_id
1145 LEFT JOIN chart c ON (at.chart_id = c.id)
1146 WHERE (trans_id = ?)
1147 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1148 ORDER BY at.acc_trans_id
1150 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1152 if (@delete_acc_trans_ids) {
1153 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1154 do_query($form, $dbh, $query);
1157 $main::lxdebug->leave_sub();
1161 $main::lxdebug->enter_sub();
1163 my ($self, $myconfig, $form, $locale) = @_;
1165 # connect to database, turn off autocommit
1166 my $dbh = $form->get_standard_dbh;
1169 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1171 $old_form = save_form();
1173 # Delete all entries in acc_trans from prior payments.
1174 if (SL::DB::Default->get->payments_changeable != 0) {
1175 $self->_delete_payments($form, $dbh);
1178 # Save the new payments the user made before cleaning up $form.
1179 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 };
1181 # Clean up $form so that old content won't tamper the results.
1182 %keep_vars = map { $_, 1 } qw(login password id);
1183 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1185 # Retrieve the invoice from the database.
1186 $self->retrieve_invoice($myconfig, $form);
1188 # Set up the content of $form in the way that IS::post_invoice() expects.
1189 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1191 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1192 $item = $form->{invoice_details}->[$row - 1];
1194 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1196 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1199 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1201 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1203 # Restore the payment options from the user input.
1204 map { $form->{$_} = $payments{$_} } keys %payments;
1206 # Get the AR accno (which is normally done by Form::create_links()).
1210 LEFT JOIN chart c ON (at.chart_id = c.id)
1211 WHERE (trans_id = ?)
1212 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1213 ORDER BY at.acc_trans_id
1216 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1218 # Post the new payments.
1219 $self->post_invoice($myconfig, $form, $dbh, 1);
1221 restore_form($old_form);
1223 my $rc = $dbh->commit();
1225 $main::lxdebug->leave_sub();
1230 sub process_assembly {
1231 $main::lxdebug->enter_sub();
1233 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1236 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1237 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1239 JOIN parts p ON (a.parts_id = p.id)
1241 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1243 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1247 $ref->{inventory_accno_id} *= 1;
1248 $ref->{expense_accno_id} *= 1;
1250 # multiply by number of assemblies
1251 $ref->{qty} *= $totalqty;
1253 if ($ref->{assembly}) {
1254 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1257 if ($ref->{inventory_accno_id}) {
1258 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1262 # save detail record for individual assembly item in invoice table
1264 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1265 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1266 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1267 do_query($form, $dbh, $query, @values);
1273 $main::lxdebug->leave_sub();
1277 $main::lxdebug->enter_sub();
1279 # adjust allocated in table invoice according to FIFO princicple
1280 # for a certain part with part_id $id
1282 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1286 $form->{taxzone_id} *=1;
1287 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1288 my $taxzone_id = $form->{"taxzone_id"} * 1;
1290 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1291 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1292 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1293 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1294 FROM invoice i, parts p
1295 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1296 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1297 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1298 WHERE (i.parts_id = p.id)
1299 AND (i.parts_id = ?)
1300 AND ((i.base_qty + i.allocated) < 0)
1302 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1307 # all invoice entries of an example part:
1309 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1310 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1311 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1312 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1313 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1314 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1315 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1317 # 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
1318 # and all parts have been allocated
1320 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1321 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1323 # in this example there are still 4 unsold articles
1326 # search all invoice entries for the part in question, adjusting "allocated"
1327 # until the total number of sold parts has been reached
1329 # ORDER BY trans_id ensures FIFO
1332 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1333 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1337 # update allocated in invoice
1338 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1340 # total expenses and inventory
1341 # sellprice is the cost of the item
1342 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1344 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1345 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1346 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1348 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1349 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1350 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1352 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1353 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1359 last if (($totalqty -= $qty) <= 0);
1364 $main::lxdebug->leave_sub();
1369 sub reverse_invoice {
1370 $main::lxdebug->enter_sub();
1372 my ($dbh, $form) = @_;
1374 # reverse inventory items
1376 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1378 JOIN parts p ON (i.parts_id = p.id)
1379 WHERE i.trans_id = ?|;
1380 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1382 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1384 if ($ref->{inventory_accno_id}) {
1385 # de-allocated purchases
1387 qq|SELECT i.id, i.trans_id, i.allocated
1389 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1390 ORDER BY i.trans_id DESC|;
1391 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1393 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1394 my $qty = $ref->{qty};
1395 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1396 $qty = $inhref->{allocated};
1400 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1402 last if (($ref->{qty} -= $qty) <= 0);
1411 my @values = (conv_i($form->{id}));
1412 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1413 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1414 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1416 $main::lxdebug->leave_sub();
1419 sub delete_invoice {
1420 $main::lxdebug->enter_sub();
1422 my ($self, $myconfig, $form) = @_;
1424 # connect to database
1425 my $dbh = $form->get_standard_dbh;
1428 &reverse_invoice($dbh, $form);
1430 my @values = (conv_i($form->{id}));
1432 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1433 # zurückgesetzt werden. Vgl:
1434 # id | storno | storno_id | paid | amount
1435 #----+--------+-----------+---------+-----------
1436 # 18 | f | | 0.00000 | 119.00000
1438 # 18 | t | | 119.00000 | 119.00000
1440 if($form->{storno}){
1441 # storno_id auslesen und korrigieren
1442 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1443 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1446 # delete spool files
1447 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1450 qq|DELETE FROM status WHERE trans_id = ?|,
1451 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1452 qq|DELETE FROM ar WHERE id = ?|,
1455 map { do_query($form, $dbh, $_, @values) } @queries;
1457 my $rc = $dbh->commit;
1460 my $spool = $::lx_office_conf{paths}->{spool};
1461 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1464 $main::lxdebug->leave_sub();
1469 sub retrieve_invoice {
1470 $main::lxdebug->enter_sub();
1472 my ($self, $myconfig, $form) = @_;
1474 # connect to database
1475 my $dbh = $form->get_standard_dbh;
1477 my ($sth, $ref, $query);
1479 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1483 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1484 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1485 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1486 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1487 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1488 d.curr AS currencies
1492 $ref = selectfirst_hashref_query($form, $dbh, $query);
1493 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1496 my $id = conv_i($form->{id});
1499 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1503 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1504 a.orddate, a.quodate, a.globalproject_id,
1505 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1506 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1507 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1508 a.employee_id, a.salesman_id, a.payment_id,
1509 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1510 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1511 a.marge_total, a.marge_percent, a.direct_debit,
1514 LEFT JOIN employee e ON (e.id = a.employee_id)
1516 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1517 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1519 # remove any trailing whitespace
1520 $form->{currency} =~ s/\s*$//;
1522 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1525 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1526 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1528 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1530 foreach my $vc (qw(customer vendor)) {
1531 next if !$form->{"delivery_${vc}_id"};
1532 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1535 # get printed, emailed
1536 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1537 $sth = prepare_execute_query($form, $dbh, $query, $id);
1539 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1540 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1541 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1542 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1545 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1547 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1548 : $form->{invdate} ? $dbh->quote($form->{invdate})
1552 my $taxzone_id = $form->{taxzone_id} *= 1;
1553 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1555 # retrieve individual items
1558 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1559 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1560 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1563 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1564 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1565 i.price_factor_id, i.price_factor, i.marge_price_factor,
1566 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1567 pr.projectnumber, pg.partsgroup, prg.pricegroup
1570 LEFT JOIN parts p ON (i.parts_id = p.id)
1571 LEFT JOIN project pr ON (i.project_id = pr.id)
1572 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1573 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1575 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1576 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1577 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1579 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1581 $sth = prepare_execute_query($form, $dbh, $query, $id);
1583 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1584 # Retrieve custom variables.
1585 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1587 sub_module => 'invoice',
1588 trans_id => $ref->{invoice_id},
1590 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1591 delete $ref->{invoice_id};
1593 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1594 delete($ref->{"part_inventory_accno_id"});
1596 foreach my $type (qw(inventory income expense)) {
1597 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1598 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1599 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1603 # get tax rates and description
1604 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1606 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1607 LEFT JOIN chart c ON (c.id = t.chart_id)
1609 (SELECT tk.tax_id FROM taxkeys tk
1610 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1611 AND startdate <= date($transdate)
1612 ORDER BY startdate DESC LIMIT 1)
1614 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1615 $ref->{taxaccounts} = "";
1617 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1619 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1623 $ref->{taxaccounts} .= "$ptr->{accno} ";
1625 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1626 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1627 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1628 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1629 $form->{taxaccounts} .= "$ptr->{accno} ";
1634 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1636 chop $ref->{taxaccounts};
1637 push @{ $form->{invoice_details} }, $ref;
1642 Common::webdav_folder($form);
1645 my $rc = $dbh->commit;
1647 $main::lxdebug->leave_sub();
1653 $main::lxdebug->enter_sub();
1655 my ($self, $myconfig, $form) = @_;
1657 # connect to database
1658 my $dbh = $form->get_standard_dbh;
1660 my $dateformat = $myconfig->{dateformat};
1661 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1663 my (@values, $duedate, $ref, $query);
1665 if ($form->{invdate}) {
1666 $duedate = "to_date(?, '$dateformat')";
1667 push @values, $form->{invdate};
1669 $duedate = "current_date";
1672 my $cid = conv_i($form->{customer_id});
1675 if ($form->{payment_id}) {
1676 $payment_id = "(pt.id = ?) OR";
1677 push @values, conv_i($form->{payment_id});
1683 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1684 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1685 c.street, c.zipcode, c.city, c.country,
1686 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1687 c.taxincluded_checked, c.direct_debit,
1688 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1689 b.discount AS tradediscount, b.description AS business
1691 LEFT JOIN business b ON (b.id = c.business_id)
1692 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1695 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1697 delete $ref->{salesman_id} if !$ref->{salesman_id};
1699 map { $form->{$_} = $ref->{$_} } keys %$ref;
1701 # remove any trailing whitespace
1702 $form->{curr} =~ s/\s*$//;
1704 # use customer currency if not empty
1705 $form->{currency} = $form->{curr} if $form->{curr};
1708 qq|SELECT sum(amount - paid) AS dunning_amount
1710 WHERE (paid < amount)
1711 AND (customer_id = ?)
1712 AND (dunning_config_id IS NOT NULL)|;
1713 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1714 map { $form->{$_} = $ref->{$_} } keys %$ref;
1717 qq|SELECT dnn.dunning_description AS max_dunning_level
1718 FROM dunning_config dnn
1719 WHERE id IN (SELECT dunning_config_id
1721 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1722 ORDER BY dunning_level DESC LIMIT 1|;
1723 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1724 map { $form->{$_} = $ref->{$_} } keys %$ref;
1726 $form->{creditremaining} = $form->{creditlimit};
1727 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1728 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1729 $form->{creditremaining} -= $value;
1733 (SELECT e.buy FROM exchangerate e
1734 WHERE e.curr = o.curr
1735 AND e.transdate = o.transdate)
1737 WHERE o.customer_id = ?
1738 AND o.quotation = '0'
1739 AND o.closed = '0'|;
1740 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1742 while (my ($amount, $exch) = $sth->fetchrow_array) {
1743 $exch = 1 unless $exch;
1744 $form->{creditremaining} -= $amount * $exch;
1748 # get shipto if we did not converted an order or invoice
1749 if (!$form->{shipto}) {
1750 map { delete $form->{$_} }
1751 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1752 shiptostreet shiptozipcode shiptocity shiptocountry
1753 shiptocontact shiptophone shiptofax shiptoemail);
1755 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1756 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1758 map { $form->{$_} = $ref->{$_} } keys %$ref;
1761 # setup last accounts used for this customer
1762 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1764 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1766 JOIN acc_trans ac ON (ac.chart_id = c.id)
1767 JOIN ar a ON (a.id = ac.trans_id)
1768 WHERE a.customer_id = ?
1769 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1770 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1771 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1774 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1775 if ($ref->{category} eq 'I') {
1777 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1779 if ($form->{initial_transdate}) {
1781 qq|SELECT tk.tax_id, t.rate
1783 LEFT JOIN tax t ON tk.tax_id = t.id
1784 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1785 ORDER BY tk.startdate DESC
1787 my ($tax_id, $rate) =
1788 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1789 $form->{initial_transdate});
1790 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1793 if ($ref->{category} eq 'A') {
1794 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1798 $form->{rowcount} = $i if ($i && !$form->{type});
1801 $main::lxdebug->leave_sub();
1805 $main::lxdebug->enter_sub();
1807 my ($self, $myconfig, $form) = @_;
1809 # connect to database
1810 my $dbh = $form->get_standard_dbh;
1812 my $i = $form->{rowcount};
1814 my $where = qq|NOT p.obsolete = '1'|;
1817 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1818 my ($table, $field) = split m/\./, $column;
1819 next if !$form->{"${field}_${i}"};
1820 $where .= qq| AND lower(${column}) ILIKE ?|;
1821 push @values, '%' . $form->{"${field}_${i}"} . '%';
1824 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1825 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1826 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1827 push @values, $form->{"partnumber_$i"};
1830 # Search for part ID overrides all other criteria.
1831 if ($form->{"id_${i}"}) {
1832 $where = qq|p.id = ?|;
1833 @values = ($form->{"id_${i}"});
1836 if ($form->{"description_$i"}) {
1837 $where .= qq| ORDER BY p.description|;
1839 $where .= qq| ORDER BY p.partnumber|;
1843 if ($form->{type} eq "invoice") {
1845 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1846 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1850 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1854 my $taxzone_id = $form->{taxzone_id} * 1;
1855 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1859 p.id, p.partnumber, p.description, p.sellprice,
1860 p.listprice, p.inventory_accno_id, p.lastcost,
1862 c1.accno AS inventory_accno,
1863 c1.new_chart_id AS inventory_new_chart,
1864 date($transdate) - c1.valid_from AS inventory_valid,
1866 c2.accno AS income_accno,
1867 c2.new_chart_id AS income_new_chart,
1868 date($transdate) - c2.valid_from AS income_valid,
1870 c3.accno AS expense_accno,
1871 c3.new_chart_id AS expense_new_chart,
1872 date($transdate) - c3.valid_from AS expense_valid,
1874 p.unit, p.assembly, p.bin, p.onhand,
1875 p.notes AS partnotes, p.notes AS longdescription,
1876 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1879 pfac.factor AS price_factor,
1884 LEFT JOIN chart c1 ON
1885 ((SELECT inventory_accno_id
1886 FROM buchungsgruppen
1887 WHERE id = p.buchungsgruppen_id) = c1.id)
1888 LEFT JOIN chart c2 ON
1889 ((SELECT income_accno_id_${taxzone_id}
1890 FROM buchungsgruppen
1891 WHERE id = p.buchungsgruppen_id) = c2.id)
1892 LEFT JOIN chart c3 ON
1893 ((SELECT expense_accno_id_${taxzone_id}
1894 FROM buchungsgruppen
1895 WHERE id = p.buchungsgruppen_id) = c3.id)
1896 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1897 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1899 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1901 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1903 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1904 [ qq|SELECT tr.translation, tr.longdescription
1906 WHERE tr.language_id IN
1909 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1912 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1914 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1916 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1917 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1918 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1919 if (!$ref->{inventory_accno_id}) {
1920 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1922 delete($ref->{inventory_accno_id});
1924 foreach my $type (qw(inventory income expense)) {
1925 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1927 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1930 ($ref->{"${type}_accno"},
1931 $ref->{"${type}_new_chart"},
1932 $ref->{"${type}_valid"})
1933 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1937 if ($form->{payment_id} eq "") {
1938 $form->{payment_id} = $form->{part_payment_id};
1941 # get tax rates and description
1942 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1944 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1946 LEFT JOIN chart c ON (c.id = t.chart_id)
1950 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1952 ORDER BY startdate DESC
1955 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1956 my $stw = $dbh->prepare($query);
1957 $stw->execute(@values) || $form->dberror($query);
1959 $ref->{taxaccounts} = "";
1961 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1963 # if ($customertax{$ref->{accno}})
1964 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1968 $ref->{taxaccounts} .= "$ptr->{accno} ";
1970 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1971 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1972 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1973 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1974 $form->{taxaccounts} .= "$ptr->{accno} ";
1980 chop $ref->{taxaccounts};
1982 if ($form->{language_id}) {
1983 for my $spec (@translation_queries) {
1984 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1985 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1986 next unless $translation;
1987 $ref->{description} = $translation;
1988 $ref->{longdescription} = $longdescription;
1993 $ref->{onhand} *= 1;
1995 push @{ $form->{item_list} }, $ref;
1998 $_->[1]->finish for @translation_queries;
2000 foreach my $item (@{ $form->{item_list} }) {
2001 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2002 trans_id => $item->{id},
2006 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2009 $main::lxdebug->leave_sub();
2012 ##########################
2013 # get pricegroups from database
2014 # build up selected pricegroup
2015 # if an exchange rate - change price
2018 sub get_pricegroups_for_parts {
2020 $main::lxdebug->enter_sub();
2022 my ($self, $myconfig, $form) = @_;
2024 my $dbh = $form->get_standard_dbh;
2026 $form->{"PRICES"} = {};
2030 my $all_units = AM->retrieve_units($myconfig, $form);
2031 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2032 $form->{"PRICES"}{$i} = [];
2034 $id = $form->{"id_$i"};
2036 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2037 $id = $form->{"new_id_$i"};
2040 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2042 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2044 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2045 my $sellprice = $form->{"sellprice_$i"};
2046 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2047 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2048 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2050 my $price_new = $form->{"price_new_$i"};
2051 my $price_old = $form->{"price_old_$i"};
2053 if (!$form->{"unit_old_$i"}) {
2054 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2055 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2056 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2057 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2060 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2061 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2062 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2064 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2065 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2066 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2067 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2068 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2069 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2070 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2075 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2076 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2077 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2078 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2079 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2083 if (!$form->{"basefactor_$i"}) {
2084 $form->{"basefactor_$i"} = 1;
2090 sellprice AS default_sellprice,
2093 'selected' AS selected
2099 parts.sellprice AS default_sellprice,
2100 pricegroup.pricegroup,
2104 LEFT JOIN parts ON parts.id = parts_id
2105 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2107 ORDER BY pricegroup|;
2108 my @values = (conv_i($id), conv_i($id));
2109 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2111 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2113 $pkr->{selected} = '';
2115 # if there is an exchange rate change price
2116 if (($form->{exchangerate} * 1) != 0) {
2117 $pkr->{price} /= $form->{exchangerate};
2120 $pkr->{price} *= $form->{"basefactor_$i"};
2121 $pkr->{price} *= $basefactor;
2122 $pkr->{price_ufmt} = $pkr->{price};
2123 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2125 if (!defined $selectedpricegroup_id) {
2126 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2127 # Case A: open old invoice, no pricegroup selected
2128 # Case B: add new article to invoice, no pricegroup selected
2130 # to distinguish case A and B the variable pricegroup_id_$i is used
2131 # for new articles this variable isn't defined, for loaded articles it is
2132 # sellprice can't be used, as it already has 0,00 set
2134 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2136 $pkr->{selected} = ' selected';
2137 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2138 and not defined $form->{"pricegroup_id_$i"}
2139 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2140 # for the case where pricegroup prices haven't been set
2142 # Case B: use default pricegroup of customer
2144 $pkr->{selected} = ' selected'; # unless $form->{selected};
2145 # no customer pricesgroup set
2146 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2148 $pkr->{price} = $form->{"sellprice_$i"};
2152 # this sub should not set anything and only return. --sschoeling, 20090506
2153 # is this correct? put in again... -- grichardson 20110119
2154 $form->{"sellprice_$i"} = $pkr->{price};
2157 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2158 $pkr->{price} = $form->{"sellprice_$i"};
2159 $pkr->{selected} = ' selected';
2163 # existing article: pricegroup or price changed
2164 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2165 if ($selectedpricegroup_id ne $pricegroup_old) {
2166 # pricegroup has changed
2167 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2168 $pkr->{selected} = ' selected';
2170 } elsif ( ($form->parse_amount($myconfig, $price_new)
2171 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2172 and ($price_new ne 0) and defined $price_new) {
2173 # sellprice has changed
2174 # when loading existing invoices $price_new is NULL
2175 if ($pkr->{pricegroup_id} == 0) {
2176 $pkr->{price} = $form->{"sellprice_$i"};
2177 $pkr->{selected} = ' selected';
2179 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2180 # neither sellprice nor pricegroup changed
2181 $pkr->{selected} = ' selected';
2182 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2183 # $pkr->{price} = $form->{"sellprice_$i"};
2185 $pkr->{price} = $form->{"sellprice_$i"};
2189 push @{ $form->{PRICES}{$i} }, $pkr;
2192 $form->{"basefactor_$i"} *= $basefactor;
2199 $main::lxdebug->leave_sub();
2203 $main::lxdebug->enter_sub();
2205 my ($self, $myconfig, $form, $table) = @_;
2207 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2209 # make sure there's no funny stuff in $table
2210 # ToDO: die when this happens and throw an error
2211 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2213 my $dbh = $form->get_standard_dbh;
2215 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2216 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2218 $main::lxdebug->leave_sub();
2224 $main::lxdebug->enter_sub();
2226 my ($self, $myconfig, $form, $table, $id) = @_;
2228 $main::lxdebug->leave_sub() and return 0 unless ($id);
2230 # make sure there's no funny stuff in $table
2231 # ToDO: die when this happens and throw an error
2232 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2234 my $dbh = $form->get_standard_dbh;
2236 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2237 my ($result) = selectrow_query($form, $dbh, $query, $id);
2239 $main::lxdebug->leave_sub();
2244 sub get_standard_accno_current_assets {
2245 $main::lxdebug->enter_sub();
2247 my ($self, $myconfig, $form) = @_;
2249 my $dbh = $form->get_standard_dbh;
2251 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2252 my ($result) = selectrow_query($form, $dbh, $query);
2254 $main::lxdebug->leave_sub();