1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
231 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
233 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
235 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
248 $form->{total} += $linetotal;
249 $form->{nodiscount_total} += $nodiscount_linetotal;
250 $form->{discount_total} += $discount;
252 if ($subtotal_header) {
253 $discount_subtotal += $linetotal;
254 $nodiscount_subtotal += $nodiscount_linetotal;
257 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
271 if (!$form->{"discount_$i"}) {
272 $nodiscount += $linetotal;
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
364 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
365 for @{ $ic_cvar_configs };
369 foreach my $item (sort keys %taxaccounts) {
370 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
375 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
377 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
382 for my $i (1 .. $form->{paidaccounts}) {
383 if ($form->{"paid_$i"}) {
384 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
388 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
389 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
392 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
395 if($form->{taxincluded}) {
396 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
397 $form->{subtotal_nofmt} = $form->{total} - $tax;
400 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
401 $form->{subtotal_nofmt} = $form->{total};
404 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
405 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
406 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
407 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
409 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
410 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
412 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
413 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
415 $form->set_payment_options($myconfig, $form->{invdate});
417 $form->{username} = $myconfig->{name};
419 $main::lxdebug->leave_sub();
422 sub project_description {
423 $main::lxdebug->enter_sub();
425 my ($self, $dbh, $id) = @_;
426 my $form = \%main::form;
428 my $query = qq|SELECT description FROM project WHERE id = ?|;
429 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
431 $main::lxdebug->leave_sub();
436 sub customer_details {
437 $main::lxdebug->enter_sub();
439 my ($self, $myconfig, $form, @wanted_vars) = @_;
441 # connect to database
442 my $dbh = $form->get_standard_dbh;
444 my $language_id = $form->{language_id};
446 # get contact id, set it if nessessary
449 my @values = (conv_i($form->{customer_id}));
452 if ($form->{cp_id}) {
453 $where = qq| AND (cp.cp_id = ?) |;
454 push(@values, conv_i($form->{cp_id}));
457 # get rest for the customer
459 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
460 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
461 (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency
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 if ($form->{delivery_customer_id}) {
483 qq|SELECT *, notes as customernotes
487 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
489 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
492 if ($form->{delivery_vendor_id}) {
494 qq|SELECT *, notes as customernotes
498 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
500 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
503 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
505 'trans_id' => $form->{customer_id});
506 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
508 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
509 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
510 'language_id' => $language_id,
511 'allow_fallback' => 1);
514 $main::lxdebug->leave_sub();
518 $main::lxdebug->enter_sub();
520 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
522 # connect to database, turn off autocommit
523 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
525 my ($query, $sth, $null, $project_id, @values);
526 my $exchangerate = 0;
528 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
531 if (!$form->{employee_id}) {
532 $form->get_employee($dbh);
535 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
536 my $defaultcurrency = $form->{defaultcurrency};
538 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
539 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
540 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
541 if (!$form->{department_id}){
542 ($null, $form->{department_id}) = split(/--/, $form->{department});
545 my $all_units = AM->retrieve_units($myconfig, $form);
547 if (!$payments_only) {
549 &reverse_invoice($dbh, $form);
552 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
553 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
555 $query = qq|SELECT nextval('glid')|;
556 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
558 $query = qq|INSERT INTO ar (id, invnumber, curr) VALUES (?, ?, (SELECT id FROM currencies WHERE curr=?))|;
559 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
561 if (!$form->{invnumber}) {
563 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
564 "cnnumber" : "invnumber", $dbh);
569 my ($netamount, $invoicediff) = (0, 0);
570 my ($amount, $linetotal, $lastincomeaccno);
572 if ($form->{currency} eq $defaultcurrency) {
573 $form->{exchangerate} = 1;
575 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
578 $form->{exchangerate} =
581 : $form->parse_amount($myconfig, $form->{exchangerate});
583 $form->{expense_inventory} = "";
587 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
588 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
591 $form->{amount} = {};
592 $form->{amount_cogs} = {};
594 foreach my $i (1 .. $form->{rowcount}) {
595 if ($form->{type} eq "credit_note") {
596 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
597 $form->{shipped} = 1;
599 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
604 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
605 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
606 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
608 if ($form->{storno}) {
609 $form->{"qty_$i"} *= -1;
612 if ($form->{"id_$i"}) {
615 if (defined($baseunits{$form->{"id_$i"}})) {
616 $item_unit = $baseunits{$form->{"id_$i"}};
619 $query = qq|SELECT unit FROM parts WHERE id = ?|;
620 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
621 $baseunits{$form->{"id_$i"}} = $item_unit;
624 if (defined($all_units->{$item_unit}->{factor})
625 && ($all_units->{$item_unit}->{factor} ne '')
626 && ($all_units->{$item_unit}->{factor} != 0)) {
627 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
631 $baseqty = $form->{"qty_$i"} * $basefactor;
633 my ($allocated, $taxrate) = (0, 0);
637 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
639 # keep entered selling price
641 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
643 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
645 my $decimalplaces = ($dec > 2) ? $dec : 2;
647 # undo discount formatting
648 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
651 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
653 # round linetotal to 2 decimal places
654 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
655 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
657 if ($form->{taxincluded}) {
658 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
659 $form->{"sellprice_$i"} =
660 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
662 $taxamount = $linetotal * $taxrate;
665 $netamount += $linetotal;
667 if ($taxamount != 0) {
669 $form->{amount}{ $form->{id} }{$_} +=
670 $taxamount * $form->{"${_}_rate"} / $taxrate
671 } split(/ /, $form->{"taxaccounts_$i"});
674 # add amount to income, $form->{amount}{trans_id}{accno}
675 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
677 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
678 $linetotal = $form->round_amount($linetotal, 2);
680 # this is the difference from the inventory
681 $invoicediff += ($amount - $linetotal);
683 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
686 $lastincomeaccno = $form->{"income_accno_$i"};
688 # adjust and round sellprice
689 $form->{"sellprice_$i"} =
690 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
693 next if $payments_only;
695 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
697 if ($form->{"assembly_$i"}) {
698 # record assembly item as allocated
699 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
702 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
706 # Get pricegroup_id and save it. Unfortunately the interface
707 # also uses ID "0" for signalling that none is selected, but "0"
708 # must not be stored in the database. Therefore we cannot simply
710 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
712 $pricegroup_id = undef if !$pricegroup_id;
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"}, $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"});
820 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
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, chart_link)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
833 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
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, chart_link)
845 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
846 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
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, chart_link)
861 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
864 WHERE chart_id= (SELECT id
868 ORDER BY startdate DESC LIMIT 1),
871 WHERE chart_id= (SELECT id
875 ORDER BY startdate DESC LIMIT 1),
877 (SELECT link FROM chart WHERE accno = ?))|;
878 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
879 do_query($form, $dbh, $query, @values);
880 $form->{amount}{$trans_id}{$accno} = 0;
884 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
885 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
887 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
889 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
890 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
893 WHERE chart_id= (SELECT id
897 ORDER BY startdate DESC LIMIT 1),
900 WHERE chart_id= (SELECT id
904 ORDER BY startdate DESC LIMIT 1),
906 (SELECT link FROM chart WHERE accno = ?))|;
907 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
908 do_query($form, $dbh, $query, @values);
913 # deduct payment differences from diff
914 for my $i (1 .. $form->{paidaccounts}) {
915 if ($form->{"paid_$i"} != 0) {
917 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
918 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
922 # record payments and offsetting AR
923 if (!$form->{storno}) {
924 for my $i (1 .. $form->{paidaccounts}) {
926 if ($form->{"acc_trans_id_$i"}
928 && (SL::DB::Default->get->payments_changeable == 0)) {
932 next if ($form->{"paid_$i"} == 0);
934 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
935 $form->{"datepaid_$i"} = $form->{invdate}
936 unless ($form->{"datepaid_$i"});
937 $form->{datepaid} = $form->{"datepaid_$i"};
941 if ($form->{currency} eq $defaultcurrency) {
942 $form->{"exchangerate_$i"} = 1;
944 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
945 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
949 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
951 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
953 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
954 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
957 WHERE chart_id= (SELECT id
961 ORDER BY startdate DESC LIMIT 1),
964 WHERE chart_id= (SELECT id
968 ORDER BY startdate DESC LIMIT 1),
970 (SELECT link FROM chart WHERE accno = ?))|;
971 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
972 do_query($form, $dbh, $query, @values);
976 $form->{"paid_$i"} *= -1;
977 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
980 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
981 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
984 WHERE chart_id= (SELECT id
988 ORDER BY startdate DESC LIMIT 1),
991 WHERE chart_id= (SELECT id
995 ORDER BY startdate DESC LIMIT 1),
997 (SELECT link FROM chart WHERE accno = ?))|;
998 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
999 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1000 do_query($form, $dbh, $query, @values);
1002 # exchangerate difference
1003 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1004 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1008 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1009 $form->{"exchangerate_$i"};
1011 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1013 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1018 # update exchange rate
1019 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1020 $form->update_exchangerate($dbh, $form->{currency},
1021 $form->{"datepaid_$i"},
1022 $form->{"exchangerate_$i"}, 0);
1026 } else { # if (!$form->{storno})
1027 $form->{marge_total} *= -1;
1030 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1032 # record exchange rate differences and gains/losses
1033 foreach my $accno (keys %{ $form->{fx} }) {
1034 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1035 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1036 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1039 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1040 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1043 WHERE chart_id= (SELECT id
1047 ORDER BY startdate DESC LIMIT 1),
1050 WHERE chart_id= (SELECT id
1054 ORDER BY startdate DESC LIMIT 1),
1056 (SELECT link FROM chart WHERE accno = ?))|;
1057 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
1058 do_query($form, $dbh, $query, @values);
1063 if ($payments_only) {
1064 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1065 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1067 $dbh->commit if !$provided_dbh;
1069 $main::lxdebug->leave_sub();
1073 $amount = $netamount + $tax;
1076 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1078 $query = qq|UPDATE ar set
1079 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1080 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1081 amount = ?, netamount = ?, paid = ?,
1082 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1083 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1084 curr = (SELECT id FROM currencies WHERE curr= ?),
1085 department_id = ?, payment_id = ?, taxincluded = ?,
1086 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1087 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1088 cp_id = ?, marge_total = ?, marge_percent = ?,
1089 globalproject_id = ?, delivery_customer_id = ?,
1090 transaction_description = ?, delivery_vendor_id = ?,
1091 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1093 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1094 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1095 $amount, $netamount, $form->{"paid"},
1096 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1097 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1098 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1099 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1100 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1101 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1102 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1103 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1104 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1105 conv_i($form->{"id"}));
1106 do_query($form, $dbh, $query, @values);
1109 if ($form->{storno}) {
1112 paid = paid + amount,
1114 intnotes = ? || intnotes
1116 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1117 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1121 $form->{name} = $form->{customer};
1122 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1124 if (!$form->{shipto_id}) {
1125 $form->add_shipto($dbh, $form->{id}, "AR");
1128 # save printed, emailed and queued
1129 $form->save_status($dbh);
1131 Common::webdav_folder($form);
1133 # Link this record to the records it was created from.
1134 RecordLinks->create_links('dbh' => $dbh,
1136 'from_table' => 'oe',
1137 'from_ids' => $form->{convert_from_oe_ids},
1139 'to_id' => $form->{id},
1141 delete $form->{convert_from_oe_ids};
1143 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1145 if (scalar @convert_from_do_ids) {
1146 DO->close_orders('dbh' => $dbh,
1147 'ids' => \@convert_from_do_ids);
1149 RecordLinks->create_links('dbh' => $dbh,
1151 'from_table' => 'delivery_orders',
1152 'from_ids' => \@convert_from_do_ids,
1154 'to_id' => $form->{id},
1157 delete $form->{convert_from_do_ids};
1159 ARAP->close_orders_if_billed('dbh' => $dbh,
1160 'arap_id' => $form->{id},
1163 # safety check datev export
1164 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1165 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1166 $transdate ||= DateTime->today;
1168 my $datev = SL::DATEV->new(
1169 exporttype => DATEV_ET_BUCHUNGEN,
1170 format => DATEV_FORMAT_KNE,
1178 if ($datev->errors) {
1180 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1185 $dbh->commit if !$provided_dbh;
1187 $main::lxdebug->leave_sub();
1192 sub _delete_payments {
1193 $main::lxdebug->enter_sub();
1195 my ($self, $form, $dbh) = @_;
1197 my @delete_acc_trans_ids;
1199 # Delete old payment entries from acc_trans.
1201 qq|SELECT acc_trans_id
1203 WHERE (trans_id = ?) AND fx_transaction
1207 SELECT at.acc_trans_id
1209 LEFT JOIN chart c ON (at.chart_id = c.id)
1210 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1211 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1214 qq|SELECT at.acc_trans_id
1216 LEFT JOIN chart c ON (at.chart_id = c.id)
1217 WHERE (trans_id = ?)
1218 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1219 ORDER BY at.acc_trans_id
1221 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1223 if (@delete_acc_trans_ids) {
1224 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1225 do_query($form, $dbh, $query);
1228 $main::lxdebug->leave_sub();
1232 $main::lxdebug->enter_sub();
1234 my ($self, $myconfig, $form, $locale) = @_;
1236 # connect to database, turn off autocommit
1237 my $dbh = $form->get_standard_dbh;
1240 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1242 $old_form = save_form();
1244 # Delete all entries in acc_trans from prior payments.
1245 if (SL::DB::Default->get->payments_changeable != 0) {
1246 $self->_delete_payments($form, $dbh);
1249 # Save the new payments the user made before cleaning up $form.
1250 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 };
1252 # Clean up $form so that old content won't tamper the results.
1253 %keep_vars = map { $_, 1 } qw(login password id);
1254 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1256 # Retrieve the invoice from the database.
1257 $self->retrieve_invoice($myconfig, $form);
1259 # Set up the content of $form in the way that IS::post_invoice() expects.
1260 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1262 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1263 $item = $form->{invoice_details}->[$row - 1];
1265 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1267 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1270 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1272 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1274 # Restore the payment options from the user input.
1275 map { $form->{$_} = $payments{$_} } keys %payments;
1277 # Get the AR accno (which is normally done by Form::create_links()).
1281 LEFT JOIN chart c ON (at.chart_id = c.id)
1282 WHERE (trans_id = ?)
1283 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1284 ORDER BY at.acc_trans_id
1287 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1289 # Post the new payments.
1290 $self->post_invoice($myconfig, $form, $dbh, 1);
1292 restore_form($old_form);
1294 my $rc = $dbh->commit();
1296 $main::lxdebug->leave_sub();
1301 sub process_assembly {
1302 $main::lxdebug->enter_sub();
1304 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1307 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1308 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1310 JOIN parts p ON (a.parts_id = p.id)
1312 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1314 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1318 $ref->{inventory_accno_id} *= 1;
1319 $ref->{expense_accno_id} *= 1;
1321 # multiply by number of assemblies
1322 $ref->{qty} *= $totalqty;
1324 if ($ref->{assembly}) {
1325 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1328 if ($ref->{inventory_accno_id}) {
1329 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1333 # save detail record for individual assembly item in invoice table
1335 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1336 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1337 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1338 do_query($form, $dbh, $query, @values);
1344 $main::lxdebug->leave_sub();
1348 $main::lxdebug->enter_sub();
1350 # adjust allocated in table invoice according to FIFO princicple
1351 # for a certain part with part_id $id
1353 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1357 $form->{taxzone_id} *=1;
1358 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1359 my $taxzone_id = $form->{"taxzone_id"} * 1;
1361 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1362 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1363 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1364 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1365 FROM invoice i, parts p
1366 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1367 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1368 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1369 WHERE (i.parts_id = p.id)
1370 AND (i.parts_id = ?)
1371 AND ((i.base_qty + i.allocated) < 0)
1373 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1378 # all invoice entries of an example part:
1380 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1381 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1382 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1383 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1384 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1385 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1386 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1388 # 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
1389 # and all parts have been allocated
1391 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1392 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1394 # in this example there are still 4 unsold articles
1397 # search all invoice entries for the part in question, adjusting "allocated"
1398 # until the total number of sold parts has been reached
1400 # ORDER BY trans_id ensures FIFO
1403 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1404 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1408 # update allocated in invoice
1409 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1411 # total expenses and inventory
1412 # sellprice is the cost of the item
1413 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1415 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1416 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1417 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1419 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1420 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1421 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1423 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1424 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1430 last if (($totalqty -= $qty) <= 0);
1435 $main::lxdebug->leave_sub();
1440 sub reverse_invoice {
1441 $main::lxdebug->enter_sub();
1443 my ($dbh, $form) = @_;
1445 # reverse inventory items
1447 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1449 JOIN parts p ON (i.parts_id = p.id)
1450 WHERE i.trans_id = ?|;
1451 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1453 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1455 if ($ref->{inventory_accno_id}) {
1456 # de-allocated purchases
1458 qq|SELECT i.id, i.trans_id, i.allocated
1460 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1461 ORDER BY i.trans_id DESC|;
1462 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1464 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1465 my $qty = $ref->{qty};
1466 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1467 $qty = $inhref->{allocated};
1471 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1473 last if (($ref->{qty} -= $qty) <= 0);
1482 my @values = (conv_i($form->{id}));
1483 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1484 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1485 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1487 $main::lxdebug->leave_sub();
1490 sub delete_invoice {
1491 $main::lxdebug->enter_sub();
1493 my ($self, $myconfig, $form) = @_;
1495 # connect to database
1496 my $dbh = $form->get_standard_dbh;
1499 &reverse_invoice($dbh, $form);
1501 my @values = (conv_i($form->{id}));
1503 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1504 # zurückgesetzt werden. Vgl:
1505 # id | storno | storno_id | paid | amount
1506 #----+--------+-----------+---------+-----------
1507 # 18 | f | | 0.00000 | 119.00000
1509 # 18 | t | | 119.00000 | 119.00000
1511 if($form->{storno}){
1512 # storno_id auslesen und korrigieren
1513 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1514 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1517 # delete spool files
1518 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1521 qq|DELETE FROM status WHERE trans_id = ?|,
1522 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1523 qq|DELETE FROM ar WHERE id = ?|,
1526 map { do_query($form, $dbh, $_, @values) } @queries;
1528 my $rc = $dbh->commit;
1531 my $spool = $::lx_office_conf{paths}->{spool};
1532 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1535 $main::lxdebug->leave_sub();
1540 sub retrieve_invoice {
1541 $main::lxdebug->enter_sub();
1543 my ($self, $myconfig, $form) = @_;
1545 # connect to database
1546 my $dbh = $form->get_standard_dbh;
1548 my ($sth, $ref, $query);
1550 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1554 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1555 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1556 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1557 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1558 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1562 $ref = selectfirst_hashref_query($form, $dbh, $query);
1563 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1566 my $id = conv_i($form->{id});
1569 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1573 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1574 a.orddate, a.quodate, a.globalproject_id,
1575 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1576 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1577 a.duedate, a.taxincluded, (SELECT cu.curr FROM currencies cu WHERE cu.id=a.curr) AS currency, a.shipto_id, a.cp_id,
1578 a.employee_id, a.salesman_id, a.payment_id,
1579 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1580 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1581 a.marge_total, a.marge_percent, a.direct_debit,
1584 LEFT JOIN employee e ON (e.id = a.employee_id)
1586 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1587 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1589 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1592 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1593 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1595 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1597 foreach my $vc (qw(customer vendor)) {
1598 next if !$form->{"delivery_${vc}_id"};
1599 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1602 # get printed, emailed
1603 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1604 $sth = prepare_execute_query($form, $dbh, $query, $id);
1606 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1607 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1608 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1609 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1612 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1614 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1615 : $form->{invdate} ? $dbh->quote($form->{invdate})
1619 my $taxzone_id = $form->{taxzone_id} *= 1;
1620 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1622 # retrieve individual items
1625 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1626 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1627 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1630 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1631 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1632 i.price_factor_id, i.price_factor, i.marge_price_factor,
1633 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1634 pr.projectnumber, pg.partsgroup, prg.pricegroup
1637 LEFT JOIN parts p ON (i.parts_id = p.id)
1638 LEFT JOIN project pr ON (i.project_id = pr.id)
1639 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1640 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1642 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1643 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1644 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1646 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1648 $sth = prepare_execute_query($form, $dbh, $query, $id);
1650 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1651 # Retrieve custom variables.
1652 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1654 sub_module => 'invoice',
1655 trans_id => $ref->{invoice_id},
1657 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1658 delete $ref->{invoice_id};
1660 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1661 delete($ref->{"part_inventory_accno_id"});
1663 foreach my $type (qw(inventory income expense)) {
1664 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1665 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1666 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1670 # get tax rates and description
1671 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1673 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1674 LEFT JOIN chart c ON (c.id = t.chart_id)
1676 (SELECT tk.tax_id FROM taxkeys tk
1677 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1678 AND startdate <= date($transdate)
1679 ORDER BY startdate DESC LIMIT 1)
1681 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1682 $ref->{taxaccounts} = "";
1684 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1686 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1690 $ref->{taxaccounts} .= "$ptr->{accno} ";
1692 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1693 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1694 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1695 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1696 $form->{taxaccounts} .= "$ptr->{accno} ";
1701 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1703 chop $ref->{taxaccounts};
1704 push @{ $form->{invoice_details} }, $ref;
1709 Common::webdav_folder($form);
1712 my $rc = $dbh->commit;
1714 $main::lxdebug->leave_sub();
1720 $main::lxdebug->enter_sub();
1722 my ($self, $myconfig, $form) = @_;
1724 # connect to database
1725 my $dbh = $form->get_standard_dbh;
1727 my $dateformat = $myconfig->{dateformat};
1728 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1730 my (@values, $duedate, $ref, $query);
1732 if ($form->{invdate}) {
1733 $duedate = "to_date(?, '$dateformat')";
1734 push @values, $form->{invdate};
1736 $duedate = "current_date";
1739 my $cid = conv_i($form->{customer_id});
1742 if ($form->{payment_id}) {
1743 $payment_id = "(pt.id = ?) OR";
1744 push @values, conv_i($form->{payment_id});
1750 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1751 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1752 c.street, c.zipcode, c.city, c.country,
1753 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, (SELECT cu.curr FROM currencies cu WHERE cu.id=c.curr) AS curr,
1754 c.taxincluded_checked, c.direct_debit,
1755 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1756 b.discount AS tradediscount, b.description AS business
1758 LEFT JOIN business b ON (b.id = c.business_id)
1759 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1762 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1764 delete $ref->{salesman_id} if !$ref->{salesman_id};
1766 map { $form->{$_} = $ref->{$_} } keys %$ref;
1768 # use customer currency
1769 $form->{currency} = $form->{curr};
1772 qq|SELECT sum(amount - paid) AS dunning_amount
1774 WHERE (paid < amount)
1775 AND (customer_id = ?)
1776 AND (dunning_config_id IS NOT NULL)|;
1777 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1778 map { $form->{$_} = $ref->{$_} } keys %$ref;
1781 qq|SELECT dnn.dunning_description AS max_dunning_level
1782 FROM dunning_config dnn
1783 WHERE id IN (SELECT dunning_config_id
1785 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1786 ORDER BY dunning_level DESC LIMIT 1|;
1787 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1788 map { $form->{$_} = $ref->{$_} } keys %$ref;
1790 $form->{creditremaining} = $form->{creditlimit};
1791 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1792 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1793 $form->{creditremaining} -= $value;
1797 (SELECT e.buy FROM exchangerate e
1798 WHERE e.curr = o.curr
1799 AND e.transdate = o.transdate)
1801 WHERE o.customer_id = ?
1802 AND o.quotation = '0'
1803 AND o.closed = '0'|;
1804 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1806 while (my ($amount, $exch) = $sth->fetchrow_array) {
1807 $exch = 1 unless $exch;
1808 $form->{creditremaining} -= $amount * $exch;
1812 # get shipto if we did not converted an order or invoice
1813 if (!$form->{shipto}) {
1814 map { delete $form->{$_} }
1815 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1816 shiptostreet shiptozipcode shiptocity shiptocountry
1817 shiptocontact shiptophone shiptofax shiptoemail);
1819 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1820 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1822 map { $form->{$_} = $ref->{$_} } keys %$ref;
1825 # setup last accounts used for this customer
1826 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1828 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1830 JOIN acc_trans ac ON (ac.chart_id = c.id)
1831 JOIN ar a ON (a.id = ac.trans_id)
1832 WHERE a.customer_id = ?
1833 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1834 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1835 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1838 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1839 if ($ref->{category} eq 'I') {
1841 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1843 if ($form->{initial_transdate}) {
1845 qq|SELECT tk.tax_id, t.rate
1847 LEFT JOIN tax t ON tk.tax_id = t.id
1848 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1849 ORDER BY tk.startdate DESC
1851 my ($tax_id, $rate) =
1852 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1853 $form->{initial_transdate});
1854 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1857 if ($ref->{category} eq 'A') {
1858 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1862 $form->{rowcount} = $i if ($i && !$form->{type});
1865 $main::lxdebug->leave_sub();
1869 $main::lxdebug->enter_sub();
1871 my ($self, $myconfig, $form) = @_;
1873 # connect to database
1874 my $dbh = $form->get_standard_dbh;
1876 my $i = $form->{rowcount};
1878 my $where = qq|NOT p.obsolete = '1'|;
1881 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1882 my ($table, $field) = split m/\./, $column;
1883 next if !$form->{"${field}_${i}"};
1884 $where .= qq| AND lower(${column}) ILIKE ?|;
1885 push @values, '%' . $form->{"${field}_${i}"} . '%';
1888 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1889 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1890 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1891 push @values, $form->{"partnumber_$i"};
1894 # Search for part ID overrides all other criteria.
1895 if ($form->{"id_${i}"}) {
1896 $where = qq|p.id = ?|;
1897 @values = ($form->{"id_${i}"});
1900 if ($form->{"description_$i"}) {
1901 $where .= qq| ORDER BY p.description|;
1903 $where .= qq| ORDER BY p.partnumber|;
1907 if ($form->{type} eq "invoice") {
1909 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1910 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1914 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1918 my $taxzone_id = $form->{taxzone_id} * 1;
1919 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1923 p.id, p.partnumber, p.description, p.sellprice,
1924 p.listprice, p.inventory_accno_id, p.lastcost,
1926 c1.accno AS inventory_accno,
1927 c1.new_chart_id AS inventory_new_chart,
1928 date($transdate) - c1.valid_from AS inventory_valid,
1930 c2.accno AS income_accno,
1931 c2.new_chart_id AS income_new_chart,
1932 date($transdate) - c2.valid_from AS income_valid,
1934 c3.accno AS expense_accno,
1935 c3.new_chart_id AS expense_new_chart,
1936 date($transdate) - c3.valid_from AS expense_valid,
1938 p.unit, p.assembly, p.bin, p.onhand,
1939 p.notes AS partnotes, p.notes AS longdescription,
1940 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1943 pfac.factor AS price_factor,
1948 LEFT JOIN chart c1 ON
1949 ((SELECT inventory_accno_id
1950 FROM buchungsgruppen
1951 WHERE id = p.buchungsgruppen_id) = c1.id)
1952 LEFT JOIN chart c2 ON
1953 ((SELECT income_accno_id_${taxzone_id}
1954 FROM buchungsgruppen
1955 WHERE id = p.buchungsgruppen_id) = c2.id)
1956 LEFT JOIN chart c3 ON
1957 ((SELECT expense_accno_id_${taxzone_id}
1958 FROM buchungsgruppen
1959 WHERE id = p.buchungsgruppen_id) = c3.id)
1960 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1961 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1963 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1965 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1967 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1968 [ qq|SELECT tr.translation, tr.longdescription
1970 WHERE tr.language_id IN
1973 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1976 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1978 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1980 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1981 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1982 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1983 if (!$ref->{inventory_accno_id}) {
1984 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1986 delete($ref->{inventory_accno_id});
1988 foreach my $type (qw(inventory income expense)) {
1989 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1991 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1994 ($ref->{"${type}_accno"},
1995 $ref->{"${type}_new_chart"},
1996 $ref->{"${type}_valid"})
1997 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2001 if ($form->{payment_id} eq "") {
2002 $form->{payment_id} = $form->{part_payment_id};
2005 # get tax rates and description
2006 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2008 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2010 LEFT JOIN chart c ON (c.id = t.chart_id)
2014 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2016 ORDER BY startdate DESC
2019 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2020 my $stw = $dbh->prepare($query);
2021 $stw->execute(@values) || $form->dberror($query);
2023 $ref->{taxaccounts} = "";
2025 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2027 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2031 $ref->{taxaccounts} .= "$ptr->{accno} ";
2033 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2034 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2035 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2036 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2037 $form->{taxaccounts} .= "$ptr->{accno} ";
2043 chop $ref->{taxaccounts};
2045 if ($form->{language_id}) {
2046 for my $spec (@translation_queries) {
2047 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2048 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2049 next unless $translation;
2050 $ref->{description} = $translation;
2051 $ref->{longdescription} = $longdescription;
2056 $ref->{onhand} *= 1;
2058 push @{ $form->{item_list} }, $ref;
2061 $_->[1]->finish for @translation_queries;
2063 foreach my $item (@{ $form->{item_list} }) {
2064 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2065 trans_id => $item->{id},
2069 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2072 $main::lxdebug->leave_sub();
2075 ##########################
2076 # get pricegroups from database
2077 # build up selected pricegroup
2078 # if an exchange rate - change price
2081 sub get_pricegroups_for_parts {
2083 $main::lxdebug->enter_sub();
2085 my ($self, $myconfig, $form) = @_;
2087 my $dbh = $form->get_standard_dbh;
2089 $form->{"PRICES"} = {};
2093 my $all_units = AM->retrieve_units($myconfig, $form);
2094 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2095 $form->{"PRICES"}{$i} = [];
2097 $id = $form->{"id_$i"};
2099 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2100 $id = $form->{"new_id_$i"};
2103 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2105 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2107 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2108 my $sellprice = $form->{"sellprice_$i"};
2109 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2110 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2111 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2113 my $price_new = $form->{"price_new_$i"};
2114 my $price_old = $form->{"price_old_$i"};
2116 if (!$form->{"unit_old_$i"}) {
2117 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2118 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2119 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2120 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2123 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2124 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2125 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2127 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2128 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2129 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2130 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2131 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2132 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2133 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2138 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2139 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2140 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2141 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2142 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2146 if (!$form->{"basefactor_$i"}) {
2147 $form->{"basefactor_$i"} = 1;
2153 sellprice AS default_sellprice,
2156 'selected' AS selected
2162 parts.sellprice AS default_sellprice,
2163 pricegroup.pricegroup,
2167 LEFT JOIN parts ON parts.id = parts_id
2168 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2170 ORDER BY pricegroup|;
2171 my @values = (conv_i($id), conv_i($id));
2172 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2174 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2176 $pkr->{selected} = '';
2178 # if there is an exchange rate change price
2179 if (($form->{exchangerate} * 1) != 0) {
2180 $pkr->{price} /= $form->{exchangerate};
2183 $pkr->{price} *= $form->{"basefactor_$i"};
2184 $pkr->{price} *= $basefactor;
2185 $pkr->{price_ufmt} = $pkr->{price};
2186 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2188 if (!defined $selectedpricegroup_id) {
2189 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2190 # Case A: open old invoice, no pricegroup selected
2191 # Case B: add new article to invoice, no pricegroup selected
2193 # to distinguish case A and B the variable pricegroup_id_$i is used
2194 # for new articles this variable isn't defined, for loaded articles it is
2195 # sellprice can't be used, as it already has 0,00 set
2197 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2199 $pkr->{selected} = ' selected';
2200 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2201 and not defined $form->{"pricegroup_id_$i"}
2202 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2203 # for the case where pricegroup prices haven't been set
2205 # Case B: use default pricegroup of customer
2207 $pkr->{selected} = ' selected'; # unless $form->{selected};
2208 # no customer pricesgroup set
2209 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2211 $pkr->{price} = $form->{"sellprice_$i"};
2215 # this sub should not set anything and only return. --sschoeling, 20090506
2216 # is this correct? put in again... -- grichardson 20110119
2217 $form->{"sellprice_$i"} = $pkr->{price};
2220 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2221 $pkr->{price} = $form->{"sellprice_$i"};
2222 $pkr->{selected} = ' selected';
2226 # existing article: pricegroup or price changed
2227 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2228 if ($selectedpricegroup_id ne $pricegroup_old) {
2229 # pricegroup has changed
2230 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2231 $pkr->{selected} = ' selected';
2233 } elsif ( ($form->parse_amount($myconfig, $price_new)
2234 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2235 and ($price_new ne 0) and defined $price_new) {
2236 # sellprice has changed
2237 # when loading existing invoices $price_new is NULL
2238 if ($pkr->{pricegroup_id} == 0) {
2239 $pkr->{price} = $form->{"sellprice_$i"};
2240 $pkr->{selected} = ' selected';
2242 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2243 # neither sellprice nor pricegroup changed
2244 $pkr->{selected} = ' selected';
2245 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2246 # $pkr->{price} = $form->{"sellprice_$i"};
2248 $pkr->{price} = $form->{"sellprice_$i"};
2252 push @{ $form->{PRICES}{$i} }, $pkr;
2255 $form->{"basefactor_$i"} *= $basefactor;
2262 $main::lxdebug->leave_sub();
2266 $main::lxdebug->enter_sub();
2268 my ($self, $myconfig, $form, $table) = @_;
2270 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2272 # make sure there's no funny stuff in $table
2273 # ToDO: die when this happens and throw an error
2274 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2276 my $dbh = $form->get_standard_dbh;
2278 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2279 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2281 $main::lxdebug->leave_sub();
2287 $main::lxdebug->enter_sub();
2289 my ($self, $myconfig, $form, $table, $id) = @_;
2291 $main::lxdebug->leave_sub() and return 0 unless ($id);
2293 # make sure there's no funny stuff in $table
2294 # ToDO: die when this happens and throw an error
2295 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2297 my $dbh = $form->get_standard_dbh;
2299 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2300 my ($result) = selectrow_query($form, $dbh, $query, $id);
2302 $main::lxdebug->leave_sub();
2307 sub get_standard_accno_current_assets {
2308 $main::lxdebug->enter_sub();
2310 my ($self, $myconfig, $form) = @_;
2312 my $dbh = $form->get_standard_dbh;
2314 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2315 my ($result) = selectrow_query($form, $dbh, $query);
2317 $main::lxdebug->leave_sub();