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 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
822 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
823 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
825 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
827 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
829 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
830 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
831 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
832 do_query($form, $dbh, $query, @values);
833 $form->{amount_cogs}{$trans_id}{$accno} = 0;
837 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
838 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
840 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
842 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
843 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
844 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
845 do_query($form, $dbh, $query, @values);
850 foreach my $trans_id (keys %{ $form->{amount} }) {
851 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
852 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
854 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
856 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
858 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
859 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
860 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
861 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
862 do_query($form, $dbh, $query, @values);
863 $form->{amount}{$trans_id}{$accno} = 0;
867 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
868 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
870 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
872 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
873 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
874 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
875 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
876 do_query($form, $dbh, $query, @values);
881 # deduct payment differences from diff
882 for my $i (1 .. $form->{paidaccounts}) {
883 if ($form->{"paid_$i"} != 0) {
885 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
886 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
890 # record payments and offsetting AR
891 if (!$form->{storno}) {
892 for my $i (1 .. $form->{paidaccounts}) {
894 if ($form->{"acc_trans_id_$i"}
896 && (SL::DB::Default->get->payments_changeable == 0)) {
900 next if ($form->{"paid_$i"} == 0);
902 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
903 $form->{"datepaid_$i"} = $form->{invdate}
904 unless ($form->{"datepaid_$i"});
905 $form->{datepaid} = $form->{"datepaid_$i"};
909 if ($form->{currency} eq $defaultcurrency) {
910 $form->{"exchangerate_$i"} = 1;
912 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
913 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
917 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
919 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
921 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
922 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
923 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
924 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
925 do_query($form, $dbh, $query, @values);
929 $form->{"paid_$i"} *= -1;
930 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
933 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
934 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
935 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
936 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
937 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
938 do_query($form, $dbh, $query, @values);
940 # exchangerate difference
941 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
942 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
946 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
947 $form->{"exchangerate_$i"};
949 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
951 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
956 # update exchange rate
957 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
958 $form->update_exchangerate($dbh, $form->{currency},
959 $form->{"datepaid_$i"},
960 $form->{"exchangerate_$i"}, 0);
964 } else { # if (!$form->{storno})
965 $form->{marge_total} *= -1;
968 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
970 # record exchange rate differences and gains/losses
971 foreach my $accno (keys %{ $form->{fx} }) {
972 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
973 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
974 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
977 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
978 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
979 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
980 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_i($project_id));
981 do_query($form, $dbh, $query, @values);
986 if ($payments_only) {
987 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
988 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
990 $dbh->commit if !$provided_dbh;
992 $main::lxdebug->leave_sub();
996 $amount = $netamount + $tax;
999 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1001 $query = qq|UPDATE ar set
1002 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1003 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1004 amount = ?, netamount = ?, paid = ?,
1005 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1006 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1007 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1008 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1009 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1010 cp_id = ?, marge_total = ?, marge_percent = ?,
1011 globalproject_id = ?, delivery_customer_id = ?,
1012 transaction_description = ?, delivery_vendor_id = ?,
1013 donumber = ?, invnumber_for_credit_note = ?
1015 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1016 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1017 $amount, $netamount, $form->{"paid"},
1018 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1019 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1020 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1021 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1022 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1023 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1024 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1025 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1026 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1027 conv_i($form->{"id"}));
1028 do_query($form, $dbh, $query, @values);
1031 if ($form->{storno}) {
1034 paid = paid + amount,
1036 intnotes = ? || intnotes
1038 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1039 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1043 $form->{name} = $form->{customer};
1044 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1046 if (!$form->{shipto_id}) {
1047 $form->add_shipto($dbh, $form->{id}, "AR");
1050 # save printed, emailed and queued
1051 $form->save_status($dbh);
1053 Common::webdav_folder($form);
1055 # Link this record to the records it was created from.
1056 RecordLinks->create_links('dbh' => $dbh,
1058 'from_table' => 'oe',
1059 'from_ids' => $form->{convert_from_oe_ids},
1061 'to_id' => $form->{id},
1063 delete $form->{convert_from_oe_ids};
1065 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1067 if (scalar @convert_from_do_ids) {
1068 DO->close_orders('dbh' => $dbh,
1069 'ids' => \@convert_from_do_ids);
1071 RecordLinks->create_links('dbh' => $dbh,
1073 'from_table' => 'delivery_orders',
1074 'from_ids' => \@convert_from_do_ids,
1076 'to_id' => $form->{id},
1079 delete $form->{convert_from_do_ids};
1081 ARAP->close_orders_if_billed('dbh' => $dbh,
1082 'arap_id' => $form->{id},
1085 # safety check datev export
1086 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1087 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1088 $transdate ||= DateTime->today;
1090 my $datev = SL::DATEV->new(
1091 exporttype => DATEV_ET_BUCHUNGEN,
1092 format => DATEV_FORMAT_KNE,
1100 if ($datev->errors) {
1102 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1107 $dbh->commit if !$provided_dbh;
1109 $main::lxdebug->leave_sub();
1114 sub _delete_payments {
1115 $main::lxdebug->enter_sub();
1117 my ($self, $form, $dbh) = @_;
1119 my @delete_acc_trans_ids;
1121 # Delete old payment entries from acc_trans.
1123 qq|SELECT acc_trans_id
1125 WHERE (trans_id = ?) AND fx_transaction
1129 SELECT at.acc_trans_id
1131 LEFT JOIN chart c ON (at.chart_id = c.id)
1132 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1133 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1136 qq|SELECT at.acc_trans_id
1138 LEFT JOIN chart c ON (at.chart_id = c.id)
1139 WHERE (trans_id = ?)
1140 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1141 ORDER BY at.acc_trans_id
1143 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1145 if (@delete_acc_trans_ids) {
1146 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1147 do_query($form, $dbh, $query);
1150 $main::lxdebug->leave_sub();
1154 $main::lxdebug->enter_sub();
1156 my ($self, $myconfig, $form, $locale) = @_;
1158 # connect to database, turn off autocommit
1159 my $dbh = $form->get_standard_dbh;
1162 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1164 $old_form = save_form();
1166 # Delete all entries in acc_trans from prior payments.
1167 if (SL::DB::Default->get->payments_changeable != 0) {
1168 $self->_delete_payments($form, $dbh);
1171 # Save the new payments the user made before cleaning up $form.
1172 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 };
1174 # Clean up $form so that old content won't tamper the results.
1175 %keep_vars = map { $_, 1 } qw(login password id);
1176 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1178 # Retrieve the invoice from the database.
1179 $self->retrieve_invoice($myconfig, $form);
1181 # Set up the content of $form in the way that IS::post_invoice() expects.
1182 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1184 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1185 $item = $form->{invoice_details}->[$row - 1];
1187 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1189 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1192 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1194 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1196 # Restore the payment options from the user input.
1197 map { $form->{$_} = $payments{$_} } keys %payments;
1199 # Get the AR accno (which is normally done by Form::create_links()).
1203 LEFT JOIN chart c ON (at.chart_id = c.id)
1204 WHERE (trans_id = ?)
1205 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1206 ORDER BY at.acc_trans_id
1209 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1211 # Post the new payments.
1212 $self->post_invoice($myconfig, $form, $dbh, 1);
1214 restore_form($old_form);
1216 my $rc = $dbh->commit();
1218 $main::lxdebug->leave_sub();
1223 sub process_assembly {
1224 $main::lxdebug->enter_sub();
1226 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1229 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1230 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1232 JOIN parts p ON (a.parts_id = p.id)
1234 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1236 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1240 $ref->{inventory_accno_id} *= 1;
1241 $ref->{expense_accno_id} *= 1;
1243 # multiply by number of assemblies
1244 $ref->{qty} *= $totalqty;
1246 if ($ref->{assembly}) {
1247 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1250 if ($ref->{inventory_accno_id}) {
1251 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1255 # save detail record for individual assembly item in invoice table
1257 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1258 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1259 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1260 do_query($form, $dbh, $query, @values);
1266 $main::lxdebug->leave_sub();
1270 $main::lxdebug->enter_sub();
1272 # adjust allocated in table invoice according to FIFO princicple
1273 # for a certain part with part_id $id
1275 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1279 $form->{taxzone_id} *=1;
1280 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1281 my $taxzone_id = $form->{"taxzone_id"} * 1;
1283 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1284 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1285 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1286 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1287 FROM invoice i, parts p
1288 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1289 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1290 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1291 WHERE (i.parts_id = p.id)
1292 AND (i.parts_id = ?)
1293 AND ((i.base_qty + i.allocated) < 0)
1295 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1300 # all invoice entries of an example part:
1302 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1303 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1304 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1305 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1306 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1307 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1308 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1310 # 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
1311 # and all parts have been allocated
1313 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1314 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1316 # in this example there are still 4 unsold articles
1319 # search all invoice entries for the part in question, adjusting "allocated"
1320 # until the total number of sold parts has been reached
1322 # ORDER BY trans_id ensures FIFO
1325 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1326 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1330 # update allocated in invoice
1331 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1333 # total expenses and inventory
1334 # sellprice is the cost of the item
1335 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1337 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1338 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1339 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1341 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1342 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1343 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1345 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1346 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1352 last if (($totalqty -= $qty) <= 0);
1357 $main::lxdebug->leave_sub();
1362 sub reverse_invoice {
1363 $main::lxdebug->enter_sub();
1365 my ($dbh, $form) = @_;
1367 # reverse inventory items
1369 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1371 JOIN parts p ON (i.parts_id = p.id)
1372 WHERE i.trans_id = ?|;
1373 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1375 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1377 if ($ref->{inventory_accno_id}) {
1378 # de-allocated purchases
1380 qq|SELECT i.id, i.trans_id, i.allocated
1382 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1383 ORDER BY i.trans_id DESC|;
1384 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1386 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1387 my $qty = $ref->{qty};
1388 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1389 $qty = $inhref->{allocated};
1393 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1395 last if (($ref->{qty} -= $qty) <= 0);
1404 my @values = (conv_i($form->{id}));
1405 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1406 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1407 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1409 $main::lxdebug->leave_sub();
1412 sub delete_invoice {
1413 $main::lxdebug->enter_sub();
1415 my ($self, $myconfig, $form) = @_;
1417 # connect to database
1418 my $dbh = $form->get_standard_dbh;
1421 &reverse_invoice($dbh, $form);
1423 my @values = (conv_i($form->{id}));
1425 # Falls wir ein Storno haben, mĂ¼ssen zwei Felder in der stornierten Rechnung wieder
1426 # zurĂ¼ckgesetzt werden. Vgl:
1427 # id | storno | storno_id | paid | amount
1428 #----+--------+-----------+---------+-----------
1429 # 18 | f | | 0.00000 | 119.00000
1431 # 18 | t | | 119.00000 | 119.00000
1433 if($form->{storno}){
1434 # storno_id auslesen und korrigieren
1435 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1436 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1439 # delete spool files
1440 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1443 qq|DELETE FROM status WHERE trans_id = ?|,
1444 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1445 qq|DELETE FROM ar WHERE id = ?|,
1448 map { do_query($form, $dbh, $_, @values) } @queries;
1450 my $rc = $dbh->commit;
1453 my $spool = $::lx_office_conf{paths}->{spool};
1454 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1457 $main::lxdebug->leave_sub();
1462 sub retrieve_invoice {
1463 $main::lxdebug->enter_sub();
1465 my ($self, $myconfig, $form) = @_;
1467 # connect to database
1468 my $dbh = $form->get_standard_dbh;
1470 my ($sth, $ref, $query);
1472 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1476 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1477 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1478 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1479 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1480 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1481 d.curr AS currencies
1485 $ref = selectfirst_hashref_query($form, $dbh, $query);
1486 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1489 my $id = conv_i($form->{id});
1492 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1496 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1497 a.orddate, a.quodate, a.globalproject_id,
1498 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1499 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1500 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1501 a.employee_id, a.salesman_id, a.payment_id,
1502 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1503 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1504 a.marge_total, a.marge_percent,
1507 LEFT JOIN employee e ON (e.id = a.employee_id)
1509 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1510 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1512 # remove any trailing whitespace
1513 $form->{currency} =~ s/\s*$//;
1515 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1518 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1519 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1521 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1523 foreach my $vc (qw(customer vendor)) {
1524 next if !$form->{"delivery_${vc}_id"};
1525 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1528 # get printed, emailed
1529 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1530 $sth = prepare_execute_query($form, $dbh, $query, $id);
1532 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1533 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1534 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1535 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1538 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1540 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1541 : $form->{invdate} ? $dbh->quote($form->{invdate})
1545 my $taxzone_id = $form->{taxzone_id} *= 1;
1546 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1548 # retrieve individual items
1551 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1552 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1553 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1556 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1557 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1558 i.price_factor_id, i.price_factor, i.marge_price_factor,
1559 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1560 pr.projectnumber, pg.partsgroup, prg.pricegroup
1563 LEFT JOIN parts p ON (i.parts_id = p.id)
1564 LEFT JOIN project pr ON (i.project_id = pr.id)
1565 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1566 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1568 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1569 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1570 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1572 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1574 $sth = prepare_execute_query($form, $dbh, $query, $id);
1576 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1577 # Retrieve custom variables.
1578 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1580 sub_module => 'invoice',
1581 trans_id => $ref->{invoice_id},
1583 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1584 delete $ref->{invoice_id};
1586 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1587 delete($ref->{"part_inventory_accno_id"});
1589 foreach my $type (qw(inventory income expense)) {
1590 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1591 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1592 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1596 # get tax rates and description
1597 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1599 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1600 LEFT JOIN chart c ON (c.id = t.chart_id)
1602 (SELECT tk.tax_id FROM taxkeys tk
1603 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1604 AND startdate <= date($transdate)
1605 ORDER BY startdate DESC LIMIT 1)
1607 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1608 $ref->{taxaccounts} = "";
1610 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1612 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1616 $ref->{taxaccounts} .= "$ptr->{accno} ";
1618 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1619 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1620 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1621 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1622 $form->{taxaccounts} .= "$ptr->{accno} ";
1627 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1629 chop $ref->{taxaccounts};
1630 push @{ $form->{invoice_details} }, $ref;
1635 Common::webdav_folder($form);
1638 my $rc = $dbh->commit;
1640 $main::lxdebug->leave_sub();
1646 $main::lxdebug->enter_sub();
1648 my ($self, $myconfig, $form) = @_;
1650 # connect to database
1651 my $dbh = $form->get_standard_dbh;
1653 my $dateformat = $myconfig->{dateformat};
1654 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1656 my (@values, $duedate, $ref, $query);
1658 if ($form->{invdate}) {
1659 $duedate = "to_date(?, '$dateformat')";
1660 push @values, $form->{invdate};
1662 $duedate = "current_date";
1665 my $cid = conv_i($form->{customer_id});
1668 if ($form->{payment_id}) {
1669 $payment_id = "(pt.id = ?) OR";
1670 push @values, conv_i($form->{payment_id});
1676 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1677 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1678 c.street, c.zipcode, c.city, c.country,
1679 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1680 c.taxincluded_checked,
1681 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1682 b.discount AS tradediscount, b.description AS business
1684 LEFT JOIN business b ON (b.id = c.business_id)
1685 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1688 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1690 delete $ref->{salesman_id} if !$ref->{salesman_id};
1692 map { $form->{$_} = $ref->{$_} } keys %$ref;
1694 # remove any trailing whitespace
1695 $form->{curr} =~ s/\s*$//;
1697 # use customer currency if not empty
1698 $form->{currency} = $form->{curr} if $form->{curr};
1701 qq|SELECT sum(amount - paid) AS dunning_amount
1703 WHERE (paid < amount)
1704 AND (customer_id = ?)
1705 AND (dunning_config_id IS NOT NULL)|;
1706 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1707 map { $form->{$_} = $ref->{$_} } keys %$ref;
1710 qq|SELECT dnn.dunning_description AS max_dunning_level
1711 FROM dunning_config dnn
1712 WHERE id IN (SELECT dunning_config_id
1714 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1715 ORDER BY dunning_level DESC LIMIT 1|;
1716 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1717 map { $form->{$_} = $ref->{$_} } keys %$ref;
1719 $form->{creditremaining} = $form->{creditlimit};
1720 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1721 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1722 $form->{creditremaining} -= $value;
1726 (SELECT e.buy FROM exchangerate e
1727 WHERE e.curr = o.curr
1728 AND e.transdate = o.transdate)
1730 WHERE o.customer_id = ?
1731 AND o.quotation = '0'
1732 AND o.closed = '0'|;
1733 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1735 while (my ($amount, $exch) = $sth->fetchrow_array) {
1736 $exch = 1 unless $exch;
1737 $form->{creditremaining} -= $amount * $exch;
1741 # get shipto if we did not converted an order or invoice
1742 if (!$form->{shipto}) {
1743 map { delete $form->{$_} }
1744 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1745 shiptostreet shiptozipcode shiptocity shiptocountry
1746 shiptocontact shiptophone shiptofax shiptoemail);
1748 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1749 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1751 map { $form->{$_} = $ref->{$_} } keys %$ref;
1754 # setup last accounts used for this customer
1755 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1757 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1759 JOIN acc_trans ac ON (ac.chart_id = c.id)
1760 JOIN ar a ON (a.id = ac.trans_id)
1761 WHERE a.customer_id = ?
1762 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1763 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1764 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1767 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1768 if ($ref->{category} eq 'I') {
1770 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1772 if ($form->{initial_transdate}) {
1774 qq|SELECT tk.tax_id, t.rate
1776 LEFT JOIN tax t ON tk.tax_id = t.id
1777 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1778 ORDER BY tk.startdate DESC
1780 my ($tax_id, $rate) =
1781 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1782 $form->{initial_transdate});
1783 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1786 if ($ref->{category} eq 'A') {
1787 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1791 $form->{rowcount} = $i if ($i && !$form->{type});
1794 $main::lxdebug->leave_sub();
1798 $main::lxdebug->enter_sub();
1800 my ($self, $myconfig, $form) = @_;
1802 # connect to database
1803 my $dbh = $form->get_standard_dbh;
1805 my $i = $form->{rowcount};
1807 my $where = qq|NOT p.obsolete = '1'|;
1810 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1811 my ($table, $field) = split m/\./, $column;
1812 next if !$form->{"${field}_${i}"};
1813 $where .= qq| AND lower(${column}) ILIKE ?|;
1814 push @values, '%' . $form->{"${field}_${i}"} . '%';
1817 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1818 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1819 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1820 push @values, $form->{"partnumber_$i"};
1823 # Search for part ID overrides all other criteria.
1824 if ($form->{"id_${i}"}) {
1825 $where = qq|p.id = ?|;
1826 @values = ($form->{"id_${i}"});
1829 if ($form->{"description_$i"}) {
1830 $where .= qq| ORDER BY p.description|;
1832 $where .= qq| ORDER BY p.partnumber|;
1836 if ($form->{type} eq "invoice") {
1838 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1839 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1843 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1847 my $taxzone_id = $form->{taxzone_id} * 1;
1848 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1852 p.id, p.partnumber, p.description, p.sellprice,
1853 p.listprice, p.inventory_accno_id, p.lastcost,
1855 c1.accno AS inventory_accno,
1856 c1.new_chart_id AS inventory_new_chart,
1857 date($transdate) - c1.valid_from AS inventory_valid,
1859 c2.accno AS income_accno,
1860 c2.new_chart_id AS income_new_chart,
1861 date($transdate) - c2.valid_from AS income_valid,
1863 c3.accno AS expense_accno,
1864 c3.new_chart_id AS expense_new_chart,
1865 date($transdate) - c3.valid_from AS expense_valid,
1867 p.unit, p.assembly, p.bin, p.onhand,
1868 p.notes AS partnotes, p.notes AS longdescription,
1869 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1872 pfac.factor AS price_factor,
1877 LEFT JOIN chart c1 ON
1878 ((SELECT inventory_accno_id
1879 FROM buchungsgruppen
1880 WHERE id = p.buchungsgruppen_id) = c1.id)
1881 LEFT JOIN chart c2 ON
1882 ((SELECT income_accno_id_${taxzone_id}
1883 FROM buchungsgruppen
1884 WHERE id = p.buchungsgruppen_id) = c2.id)
1885 LEFT JOIN chart c3 ON
1886 ((SELECT expense_accno_id_${taxzone_id}
1887 FROM buchungsgruppen
1888 WHERE id = p.buchungsgruppen_id) = c3.id)
1889 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1890 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1892 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1894 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1896 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1897 [ qq|SELECT tr.translation, tr.longdescription
1899 WHERE tr.language_id IN
1902 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1905 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1907 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1909 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1910 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1911 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1912 if (!$ref->{inventory_accno_id}) {
1913 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1915 delete($ref->{inventory_accno_id});
1917 foreach my $type (qw(inventory income expense)) {
1918 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1920 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1923 ($ref->{"${type}_accno"},
1924 $ref->{"${type}_new_chart"},
1925 $ref->{"${type}_valid"})
1926 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1930 if ($form->{payment_id} eq "") {
1931 $form->{payment_id} = $form->{part_payment_id};
1934 # get tax rates and description
1935 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1937 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1939 LEFT JOIN chart c ON (c.id = t.chart_id)
1943 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1945 ORDER BY startdate DESC
1948 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1949 my $stw = $dbh->prepare($query);
1950 $stw->execute(@values) || $form->dberror($query);
1952 $ref->{taxaccounts} = "";
1954 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1956 # if ($customertax{$ref->{accno}})
1957 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1961 $ref->{taxaccounts} .= "$ptr->{accno} ";
1963 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1964 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1965 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1966 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1967 $form->{taxaccounts} .= "$ptr->{accno} ";
1973 chop $ref->{taxaccounts};
1975 if ($form->{language_id}) {
1976 for my $spec (@translation_queries) {
1977 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1978 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1979 next unless $translation;
1980 $ref->{description} = $translation;
1981 $ref->{longdescription} = $longdescription;
1986 $ref->{onhand} *= 1;
1988 push @{ $form->{item_list} }, $ref;
1991 $_->[1]->finish for @translation_queries;
1993 foreach my $item (@{ $form->{item_list} }) {
1994 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1995 trans_id => $item->{id},
1999 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2002 $main::lxdebug->leave_sub();
2005 ##########################
2006 # get pricegroups from database
2007 # build up selected pricegroup
2008 # if an exchange rate - change price
2011 sub get_pricegroups_for_parts {
2013 $main::lxdebug->enter_sub();
2015 my ($self, $myconfig, $form) = @_;
2017 my $dbh = $form->get_standard_dbh;
2019 $form->{"PRICES"} = {};
2023 my $all_units = AM->retrieve_units($myconfig, $form);
2024 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2025 $form->{"PRICES"}{$i} = [];
2027 $id = $form->{"id_$i"};
2029 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2030 $id = $form->{"new_id_$i"};
2033 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2035 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2037 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2038 my $sellprice = $form->{"sellprice_$i"};
2039 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2040 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2041 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2043 my $price_new = $form->{"price_new_$i"};
2044 my $price_old = $form->{"price_old_$i"};
2046 if (!$form->{"unit_old_$i"}) {
2047 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2048 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2049 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2050 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2053 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2054 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2055 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2057 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2058 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2059 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2060 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2061 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2062 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2063 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2068 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2069 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2070 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2071 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2072 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2076 if (!$form->{"basefactor_$i"}) {
2077 $form->{"basefactor_$i"} = 1;
2083 sellprice AS default_sellprice,
2086 'selected' AS selected
2092 parts.sellprice AS default_sellprice,
2093 pricegroup.pricegroup,
2097 LEFT JOIN parts ON parts.id = parts_id
2098 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2100 ORDER BY pricegroup|;
2101 my @values = (conv_i($id), conv_i($id));
2102 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2104 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2106 $pkr->{selected} = '';
2108 # if there is an exchange rate change price
2109 if (($form->{exchangerate} * 1) != 0) {
2110 $pkr->{price} /= $form->{exchangerate};
2113 $pkr->{price} *= $form->{"basefactor_$i"};
2114 $pkr->{price} *= $basefactor;
2115 $pkr->{price_ufmt} = $pkr->{price};
2116 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2118 if (!defined $selectedpricegroup_id) {
2119 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2120 # Case A: open old invoice, no pricegroup selected
2121 # Case B: add new article to invoice, no pricegroup selected
2123 # to distinguish case A and B the variable pricegroup_id_$i is used
2124 # for new articles this variable isn't defined, for loaded articles it is
2125 # sellprice can't be used, as it already has 0,00 set
2127 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2129 $pkr->{selected} = ' selected';
2130 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2131 and not defined $form->{"pricegroup_id_$i"}
2132 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2133 # for the case where pricegroup prices haven't been set
2135 # Case B: use default pricegroup of customer
2137 $pkr->{selected} = ' selected'; # unless $form->{selected};
2138 # no customer pricesgroup set
2139 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2141 $pkr->{price} = $form->{"sellprice_$i"};
2145 # this sub should not set anything and only return. --sschoeling, 20090506
2146 # is this correct? put in again... -- grichardson 20110119
2147 $form->{"sellprice_$i"} = $pkr->{price};
2150 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2151 $pkr->{price} = $form->{"sellprice_$i"};
2152 $pkr->{selected} = ' selected';
2156 # existing article: pricegroup or price changed
2157 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2158 if ($selectedpricegroup_id ne $pricegroup_old) {
2159 # pricegroup has changed
2160 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2161 $pkr->{selected} = ' selected';
2163 } elsif ( ($form->parse_amount($myconfig, $price_new)
2164 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2165 and ($price_new ne 0) and defined $price_new) {
2166 # sellprice has changed
2167 # when loading existing invoices $price_new is NULL
2168 if ($pkr->{pricegroup_id} == 0) {
2169 $pkr->{price} = $form->{"sellprice_$i"};
2170 $pkr->{selected} = ' selected';
2172 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2173 # neither sellprice nor pricegroup changed
2174 $pkr->{selected} = ' selected';
2175 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2176 # $pkr->{price} = $form->{"sellprice_$i"};
2178 $pkr->{price} = $form->{"sellprice_$i"};
2182 push @{ $form->{PRICES}{$i} }, $pkr;
2185 $form->{"basefactor_$i"} *= $basefactor;
2192 $main::lxdebug->leave_sub();
2196 $main::lxdebug->enter_sub();
2198 my ($self, $myconfig, $form, $table) = @_;
2200 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2202 # make sure there's no funny stuff in $table
2203 # ToDO: die when this happens and throw an error
2204 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2206 my $dbh = $form->get_standard_dbh;
2208 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2209 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2211 $main::lxdebug->leave_sub();
2217 $main::lxdebug->enter_sub();
2219 my ($self, $myconfig, $form, $table, $id) = @_;
2221 $main::lxdebug->leave_sub() and return 0 unless ($id);
2223 # make sure there's no funny stuff in $table
2224 # ToDO: die when this happens and throw an error
2225 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2227 my $dbh = $form->get_standard_dbh;
2229 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2230 my ($result) = selectrow_query($form, $dbh, $query, $id);
2232 $main::lxdebug->leave_sub();
2237 sub get_standard_accno_current_assets {
2238 $main::lxdebug->enter_sub();
2240 my ($self, $myconfig, $form) = @_;
2242 my $dbh = $form->get_standard_dbh;
2244 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2245 my ($result) = selectrow_query($form, $dbh, $query);
2247 $main::lxdebug->leave_sub();