1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing();
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
231 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
233 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
235 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
248 $form->{total} += $linetotal;
249 $form->{nodiscount_total} += $nodiscount_linetotal;
250 $form->{discount_total} += $discount;
252 if ($subtotal_header) {
253 $discount_subtotal += $linetotal;
254 $nodiscount_subtotal += $nodiscount_linetotal;
257 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
271 if (!$form->{"discount_$i"}) {
272 $nodiscount += $linetotal;
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
364 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
365 for @{ $ic_cvar_configs };
369 foreach my $item (sort keys %taxaccounts) {
370 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
375 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
377 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
382 for my $i (1 .. $form->{paidaccounts}) {
383 if ($form->{"paid_$i"}) {
384 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
388 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
389 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
392 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
395 if($form->{taxincluded}) {
396 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
397 $form->{subtotal_nofmt} = $form->{total} - $tax;
400 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
401 $form->{subtotal_nofmt} = $form->{total};
404 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
405 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
406 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
407 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
409 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
410 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
412 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
413 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
415 $form->set_payment_options($myconfig, $form->{invdate});
417 $form->{username} = $myconfig->{name};
419 $main::lxdebug->leave_sub();
422 sub project_description {
423 $main::lxdebug->enter_sub();
425 my ($self, $dbh, $id) = @_;
426 my $form = \%main::form;
428 my $query = qq|SELECT description FROM project WHERE id = ?|;
429 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
431 $main::lxdebug->leave_sub();
436 sub customer_details {
437 $main::lxdebug->enter_sub();
439 my ($self, $myconfig, $form, @wanted_vars) = @_;
441 # connect to database
442 my $dbh = $form->get_standard_dbh;
444 my $language_id = $form->{language_id};
446 # get contact id, set it if nessessary
449 my @values = (conv_i($form->{customer_id}));
452 if ($form->{cp_id}) {
453 $where = qq| AND (cp.cp_id = ?) |;
454 push(@values, conv_i($form->{cp_id}));
457 # get rest for the customer
459 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
460 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
463 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
464 WHERE (ct.id = ?) $where
467 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
469 # remove id and taxincluded before copy back
470 delete @$ref{qw(id taxincluded)};
472 @wanted_vars = grep({ $_ } @wanted_vars);
473 if (scalar(@wanted_vars) > 0) {
475 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
476 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
479 map { $form->{$_} = $ref->{$_} } keys %$ref;
481 # remove any trailing whitespace
482 $form->{currency} =~ s/\s*$// if ($form->{currency});
484 if ($form->{delivery_customer_id}) {
486 qq|SELECT *, notes as customernotes
490 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
492 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
495 if ($form->{delivery_vendor_id}) {
497 qq|SELECT *, notes as customernotes
501 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
503 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
506 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
508 'trans_id' => $form->{customer_id});
509 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
511 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
512 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
513 'language_id' => $language_id,
514 'allow_fallback' => 1);
517 $main::lxdebug->leave_sub();
521 $main::lxdebug->enter_sub();
523 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
525 # connect to database, turn off autocommit
526 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
528 my ($query, $sth, $null, $project_id, @values);
529 my $exchangerate = 0;
531 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
534 if (!$form->{employee_id}) {
535 $form->get_employee($dbh);
538 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
539 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
540 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
541 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
542 if (!$form->{department_id}){
543 ($null, $form->{department_id}) = split(/--/, $form->{department});
546 my $all_units = AM->retrieve_units($myconfig, $form);
548 if (!$payments_only) {
550 &reverse_invoice($dbh, $form);
553 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
554 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
556 $query = qq|SELECT nextval('glid')|;
557 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
559 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
560 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
562 if (!$form->{invnumber}) {
564 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
565 "cnnumber" : "invnumber", $dbh);
570 my ($netamount, $invoicediff) = (0, 0);
571 my ($amount, $linetotal, $lastincomeaccno);
573 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
574 my $defaultcurrency = (split m/:/, $currencies)[0];
576 if ($form->{currency} eq $defaultcurrency) {
577 $form->{exchangerate} = 1;
579 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
582 $form->{exchangerate} =
585 : $form->parse_amount($myconfig, $form->{exchangerate});
587 $form->{expense_inventory} = "";
591 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
592 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
595 $form->{amount} = {};
596 $form->{amount_cogs} = {};
598 foreach my $i (1 .. $form->{rowcount}) {
599 if ($form->{type} eq "credit_note") {
600 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
601 $form->{shipped} = 1;
603 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
608 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
609 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
610 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
612 if ($form->{storno}) {
613 $form->{"qty_$i"} *= -1;
616 if ($form->{"id_$i"}) {
619 if (defined($baseunits{$form->{"id_$i"}})) {
620 $item_unit = $baseunits{$form->{"id_$i"}};
623 $query = qq|SELECT unit FROM parts WHERE id = ?|;
624 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
625 $baseunits{$form->{"id_$i"}} = $item_unit;
628 if (defined($all_units->{$item_unit}->{factor})
629 && ($all_units->{$item_unit}->{factor} ne '')
630 && ($all_units->{$item_unit}->{factor} != 0)) {
631 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
635 $baseqty = $form->{"qty_$i"} * $basefactor;
637 my ($allocated, $taxrate) = (0, 0);
641 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
643 # keep entered selling price
645 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
647 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
649 my $decimalplaces = ($dec > 2) ? $dec : 2;
651 # undo discount formatting
652 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
655 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
657 # round linetotal to 2 decimal places
658 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
659 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
661 if ($form->{taxincluded}) {
662 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
663 $form->{"sellprice_$i"} =
664 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
666 $taxamount = $linetotal * $taxrate;
669 $netamount += $linetotal;
671 if ($taxamount != 0) {
673 $form->{amount}{ $form->{id} }{$_} +=
674 $taxamount * $form->{"${_}_rate"} / $taxrate
675 } split(/ /, $form->{"taxaccounts_$i"});
678 # add amount to income, $form->{amount}{trans_id}{accno}
679 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
681 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
682 $linetotal = $form->round_amount($linetotal, 2);
684 # this is the difference from the inventory
685 $invoicediff += ($amount - $linetotal);
687 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
690 $lastincomeaccno = $form->{"income_accno_$i"};
692 # adjust and round sellprice
693 $form->{"sellprice_$i"} =
694 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
697 next if $payments_only;
699 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
701 if ($form->{"assembly_$i"}) {
702 # record assembly item as allocated
703 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
706 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
710 # Get pricegroup_id and save it. Unfortunately the interface
711 # also uses ID "0" for signalling that none is selected, but "0"
712 # must not be stored in the database. Therefore we cannot simply
714 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
716 $pricegroup_id = undef if !$pricegroup_id;
718 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
720 # save detail record in invoice table
722 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
723 sellprice, fxsellprice, discount, allocated, assemblyitem,
724 unit, deliverydate, project_id, serialnumber, pricegroup_id,
725 ordnumber, transdate, cusordnumber, base_qty, subtotal,
726 marge_percent, marge_total, lastcost,
727 price_factor_id, price_factor, marge_price_factor)
728 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
729 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
731 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
732 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
733 $form->{"sellprice_$i"}, $fxsellprice,
734 $form->{"discount_$i"}, $allocated, 'f',
735 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
736 $form->{"serialnumber_$i"}, $pricegroup_id,
737 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
738 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
739 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
740 $form->{"lastcost_$i"},
741 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
742 conv_i($form->{"marge_price_factor_$i"}));
743 do_query($form, $dbh, $query, @values);
745 CVar->save_custom_variables(module => 'IC',
746 sub_module => 'invoice',
747 trans_id => $invoice_id,
748 configs => $ic_cvar_configs,
750 name_prefix => 'ic_',
751 name_postfix => "_$i",
756 # total payments, don't move we need it here
757 for my $i (1 .. $form->{paidaccounts}) {
758 if ($form->{type} eq "credit_note") {
759 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
761 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
763 $form->{paid} += $form->{"paid_$i"};
764 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
767 my ($tax, $diff) = (0, 0);
769 $netamount = $form->round_amount($netamount, 2);
771 # figure out rounding errors for total amount vs netamount + taxes
772 if ($form->{taxincluded}) {
774 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
775 $diff += $amount - $netamount * $form->{exchangerate};
776 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
780 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
781 $tax += $form->{amount}{ $form->{id} }{$item};
782 $netamount -= $form->{amount}{ $form->{id} }{$item};
785 $invoicediff += $diff;
786 ######## this only applies to tax included
787 if ($lastincomeaccno) {
788 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
792 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
793 $diff = $amount - $netamount * $form->{exchangerate};
794 $netamount = $amount;
795 foreach my $item (split(/ /, $form->{taxaccounts})) {
796 $form->{amount}{ $form->{id} }{$item} =
797 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
800 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
803 $amount - $form->{amount}{ $form->{id} }{$item} *
804 $form->{exchangerate};
805 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
806 $tax += $form->{amount}{ $form->{id} }{$item};
810 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
812 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
815 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
817 # update exchangerate
818 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
819 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
820 $form->{exchangerate}, 0);
823 $project_id = conv_i($form->{"globalproject_id"});
825 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
826 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
827 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
829 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
831 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
833 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
834 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
835 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
836 do_query($form, $dbh, $query, @values);
837 $form->{amount_cogs}{$trans_id}{$accno} = 0;
841 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
842 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
844 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
846 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
847 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
848 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
849 do_query($form, $dbh, $query, @values);
854 foreach my $trans_id (keys %{ $form->{amount} }) {
855 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
856 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
858 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
860 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
862 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
863 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
864 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
865 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
866 do_query($form, $dbh, $query, @values);
867 $form->{amount}{$trans_id}{$accno} = 0;
871 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
872 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
874 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
876 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
877 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
878 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
879 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
880 do_query($form, $dbh, $query, @values);
885 # deduct payment differences from diff
886 for my $i (1 .. $form->{paidaccounts}) {
887 if ($form->{"paid_$i"} != 0) {
889 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
890 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
894 # record payments and offsetting AR
895 if (!$form->{storno}) {
896 for my $i (1 .. $form->{paidaccounts}) {
898 if ($form->{"acc_trans_id_$i"}
900 && (SL::DB::Default->get->payments_changeable == 0)) {
904 next if ($form->{"paid_$i"} == 0);
906 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
907 $form->{"datepaid_$i"} = $form->{invdate}
908 unless ($form->{"datepaid_$i"});
909 $form->{datepaid} = $form->{"datepaid_$i"};
913 if ($form->{currency} eq $defaultcurrency) {
914 $form->{"exchangerate_$i"} = 1;
916 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
917 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
921 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
923 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
925 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
926 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
927 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
928 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
929 do_query($form, $dbh, $query, @values);
933 $form->{"paid_$i"} *= -1;
934 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
937 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
938 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
939 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
940 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
941 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
942 do_query($form, $dbh, $query, @values);
944 # exchangerate difference
945 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
946 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
950 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
951 $form->{"exchangerate_$i"};
953 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
955 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
960 # update exchange rate
961 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
962 $form->update_exchangerate($dbh, $form->{currency},
963 $form->{"datepaid_$i"},
964 $form->{"exchangerate_$i"}, 0);
968 } else { # if (!$form->{storno})
969 $form->{marge_total} *= -1;
972 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
974 # record exchange rate differences and gains/losses
975 foreach my $accno (keys %{ $form->{fx} }) {
976 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
977 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
978 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
981 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
982 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
983 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
984 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_i($project_id));
985 do_query($form, $dbh, $query, @values);
990 if ($payments_only) {
991 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
992 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
994 $dbh->commit if !$provided_dbh;
996 $main::lxdebug->leave_sub();
1000 $amount = $netamount + $tax;
1003 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1005 $query = qq|UPDATE ar set
1006 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1007 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1008 amount = ?, netamount = ?, paid = ?,
1009 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1010 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1011 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1012 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1013 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1014 cp_id = ?, marge_total = ?, marge_percent = ?,
1015 globalproject_id = ?, delivery_customer_id = ?,
1016 transaction_description = ?, delivery_vendor_id = ?,
1017 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1019 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1020 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1021 $amount, $netamount, $form->{"paid"},
1022 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1023 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1024 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1025 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1026 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1027 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1028 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1029 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1030 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1031 conv_i($form->{"id"}));
1032 do_query($form, $dbh, $query, @values);
1035 if ($form->{storno}) {
1038 paid = paid + amount,
1040 intnotes = ? || intnotes
1042 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1043 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1047 $form->{name} = $form->{customer};
1048 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1050 if (!$form->{shipto_id}) {
1051 $form->add_shipto($dbh, $form->{id}, "AR");
1054 # save printed, emailed and queued
1055 $form->save_status($dbh);
1057 Common::webdav_folder($form);
1059 # Link this record to the records it was created from.
1060 RecordLinks->create_links('dbh' => $dbh,
1062 'from_table' => 'oe',
1063 'from_ids' => $form->{convert_from_oe_ids},
1065 'to_id' => $form->{id},
1067 delete $form->{convert_from_oe_ids};
1069 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1071 if (scalar @convert_from_do_ids) {
1072 DO->close_orders('dbh' => $dbh,
1073 'ids' => \@convert_from_do_ids);
1075 RecordLinks->create_links('dbh' => $dbh,
1077 'from_table' => 'delivery_orders',
1078 'from_ids' => \@convert_from_do_ids,
1080 'to_id' => $form->{id},
1083 delete $form->{convert_from_do_ids};
1085 ARAP->close_orders_if_billed('dbh' => $dbh,
1086 'arap_id' => $form->{id},
1089 # safety check datev export
1090 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1091 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1092 $transdate ||= DateTime->today;
1094 my $datev = SL::DATEV->new(
1095 exporttype => DATEV_ET_BUCHUNGEN,
1096 format => DATEV_FORMAT_KNE,
1104 if ($datev->errors) {
1106 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1111 $dbh->commit if !$provided_dbh;
1113 $main::lxdebug->leave_sub();
1118 sub _delete_payments {
1119 $main::lxdebug->enter_sub();
1121 my ($self, $form, $dbh) = @_;
1123 my @delete_acc_trans_ids;
1125 # Delete old payment entries from acc_trans.
1127 qq|SELECT acc_trans_id
1129 WHERE (trans_id = ?) AND fx_transaction
1133 SELECT at.acc_trans_id
1135 LEFT JOIN chart c ON (at.chart_id = c.id)
1136 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1137 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1140 qq|SELECT at.acc_trans_id
1142 LEFT JOIN chart c ON (at.chart_id = c.id)
1143 WHERE (trans_id = ?)
1144 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1145 ORDER BY at.acc_trans_id
1147 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1149 if (@delete_acc_trans_ids) {
1150 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1151 do_query($form, $dbh, $query);
1154 $main::lxdebug->leave_sub();
1158 $main::lxdebug->enter_sub();
1160 my ($self, $myconfig, $form, $locale) = @_;
1162 # connect to database, turn off autocommit
1163 my $dbh = $form->get_standard_dbh;
1166 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1168 $old_form = save_form();
1170 # Delete all entries in acc_trans from prior payments.
1171 if (SL::DB::Default->get->payments_changeable != 0) {
1172 $self->_delete_payments($form, $dbh);
1175 # Save the new payments the user made before cleaning up $form.
1176 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 };
1178 # Clean up $form so that old content won't tamper the results.
1179 %keep_vars = map { $_, 1 } qw(login password id);
1180 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1182 # Retrieve the invoice from the database.
1183 $self->retrieve_invoice($myconfig, $form);
1185 # Set up the content of $form in the way that IS::post_invoice() expects.
1186 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1188 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1189 $item = $form->{invoice_details}->[$row - 1];
1191 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1193 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1196 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1198 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1200 # Restore the payment options from the user input.
1201 map { $form->{$_} = $payments{$_} } keys %payments;
1203 # Get the AR accno (which is normally done by Form::create_links()).
1207 LEFT JOIN chart c ON (at.chart_id = c.id)
1208 WHERE (trans_id = ?)
1209 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1210 ORDER BY at.acc_trans_id
1213 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1215 # Post the new payments.
1216 $self->post_invoice($myconfig, $form, $dbh, 1);
1218 restore_form($old_form);
1220 my $rc = $dbh->commit();
1222 $main::lxdebug->leave_sub();
1227 sub process_assembly {
1228 $main::lxdebug->enter_sub();
1230 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1233 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1234 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1236 JOIN parts p ON (a.parts_id = p.id)
1238 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1240 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1244 $ref->{inventory_accno_id} *= 1;
1245 $ref->{expense_accno_id} *= 1;
1247 # multiply by number of assemblies
1248 $ref->{qty} *= $totalqty;
1250 if ($ref->{assembly}) {
1251 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1254 if ($ref->{inventory_accno_id}) {
1255 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1259 # save detail record for individual assembly item in invoice table
1261 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1262 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1263 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1264 do_query($form, $dbh, $query, @values);
1270 $main::lxdebug->leave_sub();
1274 $main::lxdebug->enter_sub();
1276 # adjust allocated in table invoice according to FIFO princicple
1277 # for a certain part with part_id $id
1279 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1283 $form->{taxzone_id} *=1;
1284 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1285 my $taxzone_id = $form->{"taxzone_id"} * 1;
1287 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1288 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1289 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1290 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1291 FROM invoice i, parts p
1292 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1293 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1294 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1295 WHERE (i.parts_id = p.id)
1296 AND (i.parts_id = ?)
1297 AND ((i.base_qty + i.allocated) < 0)
1299 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1304 # all invoice entries of an example part:
1306 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1307 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1308 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1309 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1310 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1311 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1312 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1314 # 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
1315 # and all parts have been allocated
1317 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1318 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1320 # in this example there are still 4 unsold articles
1323 # search all invoice entries for the part in question, adjusting "allocated"
1324 # until the total number of sold parts has been reached
1326 # ORDER BY trans_id ensures FIFO
1329 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1330 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1334 # update allocated in invoice
1335 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1337 # total expenses and inventory
1338 # sellprice is the cost of the item
1339 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1341 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1342 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1343 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1345 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1346 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1347 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1349 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1350 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1356 last if (($totalqty -= $qty) <= 0);
1361 $main::lxdebug->leave_sub();
1366 sub reverse_invoice {
1367 $main::lxdebug->enter_sub();
1369 my ($dbh, $form) = @_;
1371 # reverse inventory items
1373 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1375 JOIN parts p ON (i.parts_id = p.id)
1376 WHERE i.trans_id = ?|;
1377 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1379 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1381 if ($ref->{inventory_accno_id}) {
1382 # de-allocated purchases
1384 qq|SELECT i.id, i.trans_id, i.allocated
1386 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1387 ORDER BY i.trans_id DESC|;
1388 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1390 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1391 my $qty = $ref->{qty};
1392 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1393 $qty = $inhref->{allocated};
1397 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1399 last if (($ref->{qty} -= $qty) <= 0);
1408 my @values = (conv_i($form->{id}));
1409 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1410 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1411 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1413 $main::lxdebug->leave_sub();
1416 sub delete_invoice {
1417 $main::lxdebug->enter_sub();
1419 my ($self, $myconfig, $form) = @_;
1421 # connect to database
1422 my $dbh = $form->get_standard_dbh;
1425 &reverse_invoice($dbh, $form);
1427 my @values = (conv_i($form->{id}));
1429 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1430 # zurückgesetzt werden. Vgl:
1431 # id | storno | storno_id | paid | amount
1432 #----+--------+-----------+---------+-----------
1433 # 18 | f | | 0.00000 | 119.00000
1435 # 18 | t | | 119.00000 | 119.00000
1437 if($form->{storno}){
1438 # storno_id auslesen und korrigieren
1439 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1440 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1443 # delete spool files
1444 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1447 qq|DELETE FROM status WHERE trans_id = ?|,
1448 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1449 qq|DELETE FROM ar WHERE id = ?|,
1452 map { do_query($form, $dbh, $_, @values) } @queries;
1454 my $rc = $dbh->commit;
1457 my $spool = $::lx_office_conf{paths}->{spool};
1458 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1461 $main::lxdebug->leave_sub();
1466 sub retrieve_invoice {
1467 $main::lxdebug->enter_sub();
1469 my ($self, $myconfig, $form) = @_;
1471 # connect to database
1472 my $dbh = $form->get_standard_dbh;
1474 my ($sth, $ref, $query);
1476 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1480 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1481 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1482 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1483 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1484 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1485 d.curr AS currencies
1489 $ref = selectfirst_hashref_query($form, $dbh, $query);
1490 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1493 my $id = conv_i($form->{id});
1496 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1500 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1501 a.orddate, a.quodate, a.globalproject_id,
1502 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1503 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1504 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1505 a.employee_id, a.salesman_id, a.payment_id,
1506 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1507 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1508 a.marge_total, a.marge_percent, a.direct_debit,
1511 LEFT JOIN employee e ON (e.id = a.employee_id)
1513 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1514 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1516 # remove any trailing whitespace
1517 $form->{currency} =~ s/\s*$//;
1519 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1522 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1523 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1525 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1527 foreach my $vc (qw(customer vendor)) {
1528 next if !$form->{"delivery_${vc}_id"};
1529 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1532 # get printed, emailed
1533 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1534 $sth = prepare_execute_query($form, $dbh, $query, $id);
1536 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1537 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1538 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1539 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1542 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1544 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1545 : $form->{invdate} ? $dbh->quote($form->{invdate})
1549 my $taxzone_id = $form->{taxzone_id} *= 1;
1550 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1552 # retrieve individual items
1555 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1556 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1557 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1560 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1561 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1562 i.price_factor_id, i.price_factor, i.marge_price_factor,
1563 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1564 pr.projectnumber, pg.partsgroup, prg.pricegroup
1567 LEFT JOIN parts p ON (i.parts_id = p.id)
1568 LEFT JOIN project pr ON (i.project_id = pr.id)
1569 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1570 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1572 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1573 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1574 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1576 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1578 $sth = prepare_execute_query($form, $dbh, $query, $id);
1580 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1581 # Retrieve custom variables.
1582 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1584 sub_module => 'invoice',
1585 trans_id => $ref->{invoice_id},
1587 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1588 delete $ref->{invoice_id};
1590 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1591 delete($ref->{"part_inventory_accno_id"});
1593 foreach my $type (qw(inventory income expense)) {
1594 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1595 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1596 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1600 # get tax rates and description
1601 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1603 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1604 LEFT JOIN chart c ON (c.id = t.chart_id)
1606 (SELECT tk.tax_id FROM taxkeys tk
1607 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1608 AND startdate <= date($transdate)
1609 ORDER BY startdate DESC LIMIT 1)
1611 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1612 $ref->{taxaccounts} = "";
1614 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1616 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1620 $ref->{taxaccounts} .= "$ptr->{accno} ";
1622 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1623 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1624 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1625 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1626 $form->{taxaccounts} .= "$ptr->{accno} ";
1631 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1633 chop $ref->{taxaccounts};
1634 push @{ $form->{invoice_details} }, $ref;
1639 Common::webdav_folder($form);
1642 my $rc = $dbh->commit;
1644 $main::lxdebug->leave_sub();
1650 $main::lxdebug->enter_sub();
1652 my ($self, $myconfig, $form) = @_;
1654 # connect to database
1655 my $dbh = $form->get_standard_dbh;
1657 my $dateformat = $myconfig->{dateformat};
1658 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1660 my (@values, $duedate, $ref, $query);
1662 if ($form->{invdate}) {
1663 $duedate = "to_date(?, '$dateformat')";
1664 push @values, $form->{invdate};
1666 $duedate = "current_date";
1669 my $cid = conv_i($form->{customer_id});
1672 if ($form->{payment_id}) {
1673 $payment_id = "(pt.id = ?) OR";
1674 push @values, conv_i($form->{payment_id});
1680 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1681 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1682 c.street, c.zipcode, c.city, c.country,
1683 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1684 c.taxincluded_checked, c.direct_debit,
1685 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1686 b.discount AS tradediscount, b.description AS business
1688 LEFT JOIN business b ON (b.id = c.business_id)
1689 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1692 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1694 delete $ref->{salesman_id} if !$ref->{salesman_id};
1696 map { $form->{$_} = $ref->{$_} } keys %$ref;
1698 # remove any trailing whitespace
1699 $form->{curr} =~ s/\s*$//;
1701 # use customer currency if not empty
1702 $form->{currency} = $form->{curr} if $form->{curr};
1705 qq|SELECT sum(amount - paid) AS dunning_amount
1707 WHERE (paid < amount)
1708 AND (customer_id = ?)
1709 AND (dunning_config_id IS NOT NULL)|;
1710 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1711 map { $form->{$_} = $ref->{$_} } keys %$ref;
1714 qq|SELECT dnn.dunning_description AS max_dunning_level
1715 FROM dunning_config dnn
1716 WHERE id IN (SELECT dunning_config_id
1718 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1719 ORDER BY dunning_level DESC LIMIT 1|;
1720 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1721 map { $form->{$_} = $ref->{$_} } keys %$ref;
1723 $form->{creditremaining} = $form->{creditlimit};
1724 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1725 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1726 $form->{creditremaining} -= $value;
1730 (SELECT e.buy FROM exchangerate e
1731 WHERE e.curr = o.curr
1732 AND e.transdate = o.transdate)
1734 WHERE o.customer_id = ?
1735 AND o.quotation = '0'
1736 AND o.closed = '0'|;
1737 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1739 while (my ($amount, $exch) = $sth->fetchrow_array) {
1740 $exch = 1 unless $exch;
1741 $form->{creditremaining} -= $amount * $exch;
1745 # get shipto if we did not converted an order or invoice
1746 if (!$form->{shipto}) {
1747 map { delete $form->{$_} }
1748 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1749 shiptostreet shiptozipcode shiptocity shiptocountry
1750 shiptocontact shiptophone shiptofax shiptoemail);
1752 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1753 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1755 map { $form->{$_} = $ref->{$_} } keys %$ref;
1758 # setup last accounts used for this customer
1759 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1761 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1763 JOIN acc_trans ac ON (ac.chart_id = c.id)
1764 JOIN ar a ON (a.id = ac.trans_id)
1765 WHERE a.customer_id = ?
1766 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1767 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1768 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1771 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1772 if ($ref->{category} eq 'I') {
1774 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1776 if ($form->{initial_transdate}) {
1778 qq|SELECT tk.tax_id, t.rate
1780 LEFT JOIN tax t ON tk.tax_id = t.id
1781 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1782 ORDER BY tk.startdate DESC
1784 my ($tax_id, $rate) =
1785 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1786 $form->{initial_transdate});
1787 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1790 if ($ref->{category} eq 'A') {
1791 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1795 $form->{rowcount} = $i if ($i && !$form->{type});
1798 $main::lxdebug->leave_sub();
1802 $main::lxdebug->enter_sub();
1804 my ($self, $myconfig, $form) = @_;
1806 # connect to database
1807 my $dbh = $form->get_standard_dbh;
1809 my $i = $form->{rowcount};
1811 my $where = qq|NOT p.obsolete = '1'|;
1814 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1815 my ($table, $field) = split m/\./, $column;
1816 next if !$form->{"${field}_${i}"};
1817 $where .= qq| AND lower(${column}) ILIKE ?|;
1818 push @values, '%' . $form->{"${field}_${i}"} . '%';
1821 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1822 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1823 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1824 push @values, $form->{"partnumber_$i"};
1827 # Search for part ID overrides all other criteria.
1828 if ($form->{"id_${i}"}) {
1829 $where = qq|p.id = ?|;
1830 @values = ($form->{"id_${i}"});
1833 if ($form->{"description_$i"}) {
1834 $where .= qq| ORDER BY p.description|;
1836 $where .= qq| ORDER BY p.partnumber|;
1840 if ($form->{type} eq "invoice") {
1842 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1843 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1847 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1851 my $taxzone_id = $form->{taxzone_id} * 1;
1852 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1856 p.id, p.partnumber, p.description, p.sellprice,
1857 p.listprice, p.inventory_accno_id, p.lastcost,
1859 c1.accno AS inventory_accno,
1860 c1.new_chart_id AS inventory_new_chart,
1861 date($transdate) - c1.valid_from AS inventory_valid,
1863 c2.accno AS income_accno,
1864 c2.new_chart_id AS income_new_chart,
1865 date($transdate) - c2.valid_from AS income_valid,
1867 c3.accno AS expense_accno,
1868 c3.new_chart_id AS expense_new_chart,
1869 date($transdate) - c3.valid_from AS expense_valid,
1871 p.unit, p.assembly, p.bin, p.onhand,
1872 p.notes AS partnotes, p.notes AS longdescription,
1873 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1876 pfac.factor AS price_factor,
1881 LEFT JOIN chart c1 ON
1882 ((SELECT inventory_accno_id
1883 FROM buchungsgruppen
1884 WHERE id = p.buchungsgruppen_id) = c1.id)
1885 LEFT JOIN chart c2 ON
1886 ((SELECT income_accno_id_${taxzone_id}
1887 FROM buchungsgruppen
1888 WHERE id = p.buchungsgruppen_id) = c2.id)
1889 LEFT JOIN chart c3 ON
1890 ((SELECT expense_accno_id_${taxzone_id}
1891 FROM buchungsgruppen
1892 WHERE id = p.buchungsgruppen_id) = c3.id)
1893 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1894 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1896 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1898 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1900 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1901 [ qq|SELECT tr.translation, tr.longdescription
1903 WHERE tr.language_id IN
1906 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1909 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1911 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1913 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1914 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1915 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1916 if (!$ref->{inventory_accno_id}) {
1917 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1919 delete($ref->{inventory_accno_id});
1921 foreach my $type (qw(inventory income expense)) {
1922 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1924 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1927 ($ref->{"${type}_accno"},
1928 $ref->{"${type}_new_chart"},
1929 $ref->{"${type}_valid"})
1930 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1934 if ($form->{payment_id} eq "") {
1935 $form->{payment_id} = $form->{part_payment_id};
1938 # get tax rates and description
1939 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1941 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1943 LEFT JOIN chart c ON (c.id = t.chart_id)
1947 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1949 ORDER BY startdate DESC
1952 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1953 my $stw = $dbh->prepare($query);
1954 $stw->execute(@values) || $form->dberror($query);
1956 $ref->{taxaccounts} = "";
1958 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1960 # if ($customertax{$ref->{accno}})
1961 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1965 $ref->{taxaccounts} .= "$ptr->{accno} ";
1967 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1968 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1969 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1970 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1971 $form->{taxaccounts} .= "$ptr->{accno} ";
1977 chop $ref->{taxaccounts};
1979 if ($form->{language_id}) {
1980 for my $spec (@translation_queries) {
1981 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1982 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1983 next unless $translation;
1984 $ref->{description} = $translation;
1985 $ref->{longdescription} = $longdescription;
1990 $ref->{onhand} *= 1;
1992 push @{ $form->{item_list} }, $ref;
1995 $_->[1]->finish for @translation_queries;
1997 foreach my $item (@{ $form->{item_list} }) {
1998 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1999 trans_id => $item->{id},
2003 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2006 $main::lxdebug->leave_sub();
2009 ##########################
2010 # get pricegroups from database
2011 # build up selected pricegroup
2012 # if an exchange rate - change price
2015 sub get_pricegroups_for_parts {
2017 $main::lxdebug->enter_sub();
2019 my ($self, $myconfig, $form) = @_;
2021 my $dbh = $form->get_standard_dbh;
2023 $form->{"PRICES"} = {};
2027 my $all_units = AM->retrieve_units($myconfig, $form);
2028 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2029 $form->{"PRICES"}{$i} = [];
2031 $id = $form->{"id_$i"};
2033 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2034 $id = $form->{"new_id_$i"};
2037 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2039 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2041 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2042 my $sellprice = $form->{"sellprice_$i"};
2043 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2044 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2045 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2047 my $price_new = $form->{"price_new_$i"};
2048 my $price_old = $form->{"price_old_$i"};
2050 if (!$form->{"unit_old_$i"}) {
2051 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2052 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2053 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2054 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2057 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2058 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2059 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2061 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2062 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2063 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2064 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2065 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2066 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2067 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2072 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2073 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2074 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2075 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2076 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2080 if (!$form->{"basefactor_$i"}) {
2081 $form->{"basefactor_$i"} = 1;
2087 sellprice AS default_sellprice,
2090 'selected' AS selected
2096 parts.sellprice AS default_sellprice,
2097 pricegroup.pricegroup,
2101 LEFT JOIN parts ON parts.id = parts_id
2102 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2104 ORDER BY pricegroup|;
2105 my @values = (conv_i($id), conv_i($id));
2106 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2108 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2110 $pkr->{selected} = '';
2112 # if there is an exchange rate change price
2113 if (($form->{exchangerate} * 1) != 0) {
2114 $pkr->{price} /= $form->{exchangerate};
2117 $pkr->{price} *= $form->{"basefactor_$i"};
2118 $pkr->{price} *= $basefactor;
2119 $pkr->{price_ufmt} = $pkr->{price};
2120 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2122 if (!defined $selectedpricegroup_id) {
2123 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2124 # Case A: open old invoice, no pricegroup selected
2125 # Case B: add new article to invoice, no pricegroup selected
2127 # to distinguish case A and B the variable pricegroup_id_$i is used
2128 # for new articles this variable isn't defined, for loaded articles it is
2129 # sellprice can't be used, as it already has 0,00 set
2131 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2133 $pkr->{selected} = ' selected';
2134 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2135 and not defined $form->{"pricegroup_id_$i"}
2136 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2137 # for the case where pricegroup prices haven't been set
2139 # Case B: use default pricegroup of customer
2141 $pkr->{selected} = ' selected'; # unless $form->{selected};
2142 # no customer pricesgroup set
2143 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2145 $pkr->{price} = $form->{"sellprice_$i"};
2149 # this sub should not set anything and only return. --sschoeling, 20090506
2150 # is this correct? put in again... -- grichardson 20110119
2151 $form->{"sellprice_$i"} = $pkr->{price};
2154 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2155 $pkr->{price} = $form->{"sellprice_$i"};
2156 $pkr->{selected} = ' selected';
2160 # existing article: pricegroup or price changed
2161 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2162 if ($selectedpricegroup_id ne $pricegroup_old) {
2163 # pricegroup has changed
2164 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2165 $pkr->{selected} = ' selected';
2167 } elsif ( ($form->parse_amount($myconfig, $price_new)
2168 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2169 and ($price_new ne 0) and defined $price_new) {
2170 # sellprice has changed
2171 # when loading existing invoices $price_new is NULL
2172 if ($pkr->{pricegroup_id} == 0) {
2173 $pkr->{price} = $form->{"sellprice_$i"};
2174 $pkr->{selected} = ' selected';
2176 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2177 # neither sellprice nor pricegroup changed
2178 $pkr->{selected} = ' selected';
2179 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2180 # $pkr->{price} = $form->{"sellprice_$i"};
2182 $pkr->{price} = $form->{"sellprice_$i"};
2186 push @{ $form->{PRICES}{$i} }, $pkr;
2189 $form->{"basefactor_$i"} *= $basefactor;
2196 $main::lxdebug->leave_sub();
2200 $main::lxdebug->enter_sub();
2202 my ($self, $myconfig, $form, $table) = @_;
2204 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2206 # make sure there's no funny stuff in $table
2207 # ToDO: die when this happens and throw an error
2208 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2210 my $dbh = $form->get_standard_dbh;
2212 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2213 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2215 $main::lxdebug->leave_sub();
2221 $main::lxdebug->enter_sub();
2223 my ($self, $myconfig, $form, $table, $id) = @_;
2225 $main::lxdebug->leave_sub() and return 0 unless ($id);
2227 # make sure there's no funny stuff in $table
2228 # ToDO: die when this happens and throw an error
2229 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2231 my $dbh = $form->get_standard_dbh;
2233 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2234 my ($result) = selectrow_query($form, $dbh, $query, $id);
2236 $main::lxdebug->leave_sub();
2241 sub get_standard_accno_current_assets {
2242 $main::lxdebug->enter_sub();
2244 my ($self, $myconfig, $form) = @_;
2246 my $dbh = $form->get_standard_dbh;
2248 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2249 my ($result) = selectrow_query($form, $dbh, $query);
2251 $main::lxdebug->leave_sub();