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"}};
118 # sort items by partsgroup
119 for $i (1 .. $form->{rowcount}) {
121 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
122 $partsgroup = $form->{"partsgroup_$i"};
124 push @partsgroup, [$i, $partsgroup];
137 my $nodiscount_subtotal = 0;
138 my $discount_subtotal = 0;
140 my $subtotal_header = 0;
143 $form->{discount} = [];
145 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
147 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
150 qw(runningnumber number description longdescription qty ship unit bin
151 deliverydate_oe ordnumber_oe transdate_oe validuntil
152 partnotes serialnumber reqdate sellprice listprice netprice
153 discount p_discount discount_sub nodiscount_sub
154 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
155 price_factor price_factor_name partsgroup weight lineweight);
157 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
159 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
161 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
163 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
166 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
169 if ($item->[1] ne $sameitem) {
170 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
171 $sameitem = $item->[1];
173 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
176 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
178 if ($form->{"id_$i"} != 0) {
180 # add number, description and qty to $form->{number},
181 if ($form->{"subtotal_$i"} && !$subtotal_header) {
182 $subtotal_header = $i;
183 $position = int($position);
186 } elsif ($subtotal_header) {
188 $position = int($position);
189 $position = $position.".".$subposition;
191 $position = int($position);
195 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
197 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
198 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
210 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
216 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
217 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
218 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
220 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
221 my ($dec) = ($sellprice =~ /\.(\d+)/);
222 my $decimalplaces = max 2, length($dec);
224 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
226 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
227 my $linetotal = $form->round_amount($linetotal_exact, 2);
229 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
230 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
232 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
234 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
236 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
238 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
241 $linetotal = ($linetotal != 0) ? $linetotal : '';
243 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
247 $form->{total} += $linetotal;
248 $form->{nodiscount_total} += $nodiscount_linetotal;
249 $form->{discount_total} += $discount;
251 if ($subtotal_header) {
252 $discount_subtotal += $linetotal;
253 $nodiscount_subtotal += $nodiscount_linetotal;
256 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
257 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
259 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
262 $discount_subtotal = 0;
263 $nodiscount_subtotal = 0;
264 $subtotal_header = 0;
267 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
270 if (!$form->{"discount_$i"}) {
271 $nodiscount += $linetotal;
274 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
276 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
279 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
282 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
283 $totalweight += $lineweight;
284 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
285 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
286 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
287 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
289 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
293 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
295 if ($form->{taxincluded}) {
298 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
299 $taxbase = $linetotal - $taxamount;
301 $taxamount = $linetotal * $taxrate;
302 $taxbase = $linetotal;
305 if ($form->round_amount($taxrate, 7) == 0) {
306 if ($form->{taxincluded}) {
307 foreach my $accno (@taxaccounts) {
308 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
310 $taxaccounts{$accno} += $taxamount;
311 $taxdiff += $taxamount;
313 $taxbase{$accno} += $taxbase;
315 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
319 $taxbase{$accno} += $taxbase;
323 foreach my $accno (@taxaccounts) {
324 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
325 $taxbase{$accno} += $taxbase;
328 my $tax_rate = $taxrate * 100;
329 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
330 if ($form->{"assembly_$i"}) {
333 # get parts and push them onto the stack
335 if ($form->{groupitems}) {
337 qq|ORDER BY pg.partsgroup, a.oid|;
339 $sortorder = qq|ORDER BY a.oid|;
343 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
345 JOIN parts p ON (a.parts_id = p.id)
346 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
347 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
348 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
350 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
351 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
352 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
353 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
354 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
357 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
359 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
360 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
362 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
363 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
369 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
370 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
371 for @{ $ic_cvar_configs };
375 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
376 $form->{totalweight_nofmt} = $totalweight;
377 my $defaults = AM->get_defaults();
378 $form->{weightunit} = $defaults->{weightunit};
380 foreach my $item (sort keys %taxaccounts) {
381 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
383 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
384 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
385 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
386 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
387 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
388 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
389 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
393 for my $i (1 .. $form->{paidaccounts}) {
394 if ($form->{"paid_$i"}) {
395 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
397 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
398 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
399 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
400 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
401 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
403 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
406 if($form->{taxincluded}) {
407 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
408 $form->{subtotal_nofmt} = $form->{total} - $tax;
411 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
412 $form->{subtotal_nofmt} = $form->{total};
415 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
416 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
417 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
418 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
420 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
421 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
423 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
424 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
426 $form->set_payment_options($myconfig, $form->{invdate});
428 $form->{username} = $myconfig->{name};
430 $main::lxdebug->leave_sub();
433 sub project_description {
434 $main::lxdebug->enter_sub();
436 my ($self, $dbh, $id) = @_;
437 my $form = \%main::form;
439 my $query = qq|SELECT description FROM project WHERE id = ?|;
440 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
442 $main::lxdebug->leave_sub();
447 sub customer_details {
448 $main::lxdebug->enter_sub();
450 my ($self, $myconfig, $form, @wanted_vars) = @_;
452 # connect to database
453 my $dbh = $form->get_standard_dbh;
455 my $language_id = $form->{language_id};
457 # get contact id, set it if nessessary
460 my @values = (conv_i($form->{customer_id}));
463 if ($form->{cp_id}) {
464 $where = qq| AND (cp.cp_id = ?) |;
465 push(@values, conv_i($form->{cp_id}));
468 # get rest for the customer
470 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
471 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
474 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
475 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
476 WHERE (ct.id = ?) $where
479 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
481 # remove id and taxincluded before copy back
482 delete @$ref{qw(id taxincluded)};
484 @wanted_vars = grep({ $_ } @wanted_vars);
485 if (scalar(@wanted_vars) > 0) {
487 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
488 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
491 map { $form->{$_} = $ref->{$_} } keys %$ref;
493 if ($form->{delivery_customer_id}) {
495 qq|SELECT *, notes as customernotes
499 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
501 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
504 if ($form->{delivery_vendor_id}) {
506 qq|SELECT *, notes as customernotes
510 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
512 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
515 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
517 'trans_id' => $form->{customer_id});
518 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
520 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
521 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
522 'language_id' => $language_id,
523 'allow_fallback' => 1);
526 $main::lxdebug->leave_sub();
530 $main::lxdebug->enter_sub();
532 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
534 # connect to database, turn off autocommit
535 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
537 my ($query, $sth, $null, $project_id, @values);
538 my $exchangerate = 0;
540 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
543 if (!$form->{employee_id}) {
544 $form->get_employee($dbh);
547 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
548 my $defaultcurrency = $form->{defaultcurrency};
550 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
551 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
552 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
553 if (!$form->{department_id}){
554 ($null, $form->{department_id}) = split(/--/, $form->{department});
557 my $all_units = AM->retrieve_units($myconfig, $form);
559 if (!$payments_only) {
561 &reverse_invoice($dbh, $form);
564 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
565 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
567 $query = qq|SELECT nextval('glid')|;
568 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
570 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
571 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
573 if (!$form->{invnumber}) {
575 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
576 "cnnumber" : "invnumber", $dbh);
581 my ($netamount, $invoicediff) = (0, 0);
582 my ($amount, $linetotal, $lastincomeaccno);
584 if ($form->{currency} eq $defaultcurrency) {
585 $form->{exchangerate} = 1;
587 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
590 $form->{exchangerate} =
593 : $form->parse_amount($myconfig, $form->{exchangerate});
595 $form->{expense_inventory} = "";
599 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
600 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
603 $form->{amount} = {};
604 $form->{amount_cogs} = {};
606 foreach my $i (1 .. $form->{rowcount}) {
607 if ($form->{type} eq "credit_note") {
608 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
609 $form->{shipped} = 1;
611 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
616 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
617 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
618 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
620 if ($form->{storno}) {
621 $form->{"qty_$i"} *= -1;
624 if ($form->{"id_$i"}) {
627 if (defined($baseunits{$form->{"id_$i"}})) {
628 $item_unit = $baseunits{$form->{"id_$i"}};
631 $query = qq|SELECT unit FROM parts WHERE id = ?|;
632 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
633 $baseunits{$form->{"id_$i"}} = $item_unit;
636 if (defined($all_units->{$item_unit}->{factor})
637 && ($all_units->{$item_unit}->{factor} ne '')
638 && ($all_units->{$item_unit}->{factor} != 0)) {
639 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
643 $baseqty = $form->{"qty_$i"} * $basefactor;
645 my ($allocated, $taxrate) = (0, 0);
649 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
651 # keep entered selling price
653 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
655 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
657 my $decimalplaces = ($dec > 2) ? $dec : 2;
659 # undo discount formatting
660 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
663 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
665 # round linetotal to 2 decimal places
666 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
667 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
669 if ($form->{taxincluded}) {
670 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
671 $form->{"sellprice_$i"} =
672 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
674 $taxamount = $linetotal * $taxrate;
677 $netamount += $linetotal;
679 if ($taxamount != 0) {
681 $form->{amount}{ $form->{id} }{$_} +=
682 $taxamount * $form->{"${_}_rate"} / $taxrate
683 } split(/ /, $form->{"taxaccounts_$i"});
686 # add amount to income, $form->{amount}{trans_id}{accno}
687 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
689 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
690 $linetotal = $form->round_amount($linetotal, 2);
692 # this is the difference from the inventory
693 $invoicediff += ($amount - $linetotal);
695 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
698 $lastincomeaccno = $form->{"income_accno_$i"};
700 # adjust and round sellprice
701 $form->{"sellprice_$i"} =
702 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
705 next if $payments_only;
707 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
709 if ($form->{"assembly_$i"}) {
710 # record assembly item as allocated
711 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
714 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
718 # Get pricegroup_id and save it. Unfortunately the interface
719 # also uses ID "0" for signalling that none is selected, but "0"
720 # must not be stored in the database. Therefore we cannot simply
722 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
724 $pricegroup_id = undef if !$pricegroup_id;
726 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
728 # save detail record in invoice table
730 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
731 sellprice, fxsellprice, discount, allocated, assemblyitem,
732 unit, deliverydate, project_id, serialnumber, pricegroup_id,
733 ordnumber, transdate, cusordnumber, base_qty, subtotal,
734 marge_percent, marge_total, lastcost,
735 price_factor_id, price_factor, marge_price_factor)
736 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
737 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
739 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
740 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
741 $form->{"sellprice_$i"}, $fxsellprice,
742 $form->{"discount_$i"}, $allocated, 'f',
743 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
744 $form->{"serialnumber_$i"}, $pricegroup_id,
745 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
746 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
747 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
748 $form->{"lastcost_$i"},
749 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
750 conv_i($form->{"marge_price_factor_$i"}));
751 do_query($form, $dbh, $query, @values);
753 CVar->save_custom_variables(module => 'IC',
754 sub_module => 'invoice',
755 trans_id => $invoice_id,
756 configs => $ic_cvar_configs,
758 name_prefix => 'ic_',
759 name_postfix => "_$i",
764 # total payments, don't move we need it here
765 for my $i (1 .. $form->{paidaccounts}) {
766 if ($form->{type} eq "credit_note") {
767 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
769 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
771 $form->{paid} += $form->{"paid_$i"};
772 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
775 my ($tax, $diff) = (0, 0);
777 $netamount = $form->round_amount($netamount, 2);
779 # figure out rounding errors for total amount vs netamount + taxes
780 if ($form->{taxincluded}) {
782 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
783 $diff += $amount - $netamount * $form->{exchangerate};
784 $netamount = $amount;
786 foreach my $item (split(/ /, $form->{taxaccounts})) {
787 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
788 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
789 $tax += $form->{amount}{ $form->{id} }{$item};
790 $netamount -= $form->{amount}{ $form->{id} }{$item};
793 $invoicediff += $diff;
794 ######## this only applies to tax included
795 if ($lastincomeaccno) {
796 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
800 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
801 $diff = $amount - $netamount * $form->{exchangerate};
802 $netamount = $amount;
803 foreach my $item (split(/ /, $form->{taxaccounts})) {
804 $form->{amount}{ $form->{id} }{$item} =
805 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
808 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
811 $amount - $form->{amount}{ $form->{id} }{$item} *
812 $form->{exchangerate};
813 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
814 $tax += $form->{amount}{ $form->{id} }{$item};
818 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
820 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
823 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
825 # update exchangerate
826 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
827 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
828 $form->{exchangerate}, 0);
831 $project_id = conv_i($form->{"globalproject_id"});
832 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
833 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
835 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
836 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
837 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
839 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
841 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
843 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
844 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
845 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
846 do_query($form, $dbh, $query, @values);
847 $form->{amount_cogs}{$trans_id}{$accno} = 0;
851 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
852 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
854 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
856 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
857 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
858 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
859 do_query($form, $dbh, $query, @values);
864 foreach my $trans_id (keys %{ $form->{amount} }) {
865 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
866 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
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, tax_id, taxkey, project_id, chart_link)
873 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
876 WHERE chart_id= (SELECT id
880 ORDER BY startdate DESC LIMIT 1),
883 WHERE chart_id= (SELECT id
887 ORDER BY startdate DESC LIMIT 1),
889 (SELECT link FROM chart WHERE accno = ?))|;
890 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
891 do_query($form, $dbh, $query, @values);
892 $form->{amount}{$trans_id}{$accno} = 0;
896 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
897 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
899 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
901 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
902 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
905 WHERE chart_id= (SELECT id
909 ORDER BY startdate DESC LIMIT 1),
912 WHERE chart_id= (SELECT id
916 ORDER BY startdate DESC LIMIT 1),
918 (SELECT link FROM chart WHERE accno = ?))|;
919 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
920 do_query($form, $dbh, $query, @values);
925 # deduct payment differences from diff
926 for my $i (1 .. $form->{paidaccounts}) {
927 if ($form->{"paid_$i"} != 0) {
929 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
930 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
934 # record payments and offsetting AR
935 if (!$form->{storno}) {
936 for my $i (1 .. $form->{paidaccounts}) {
938 if ($form->{"acc_trans_id_$i"}
940 && (SL::DB::Default->get->payments_changeable == 0)) {
944 next if ($form->{"paid_$i"} == 0);
946 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
947 $form->{"datepaid_$i"} = $form->{invdate}
948 unless ($form->{"datepaid_$i"});
949 $form->{datepaid} = $form->{"datepaid_$i"};
953 if ($form->{currency} eq $defaultcurrency) {
954 $form->{"exchangerate_$i"} = 1;
956 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
957 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
961 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
963 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
965 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
966 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
969 WHERE chart_id= (SELECT id
973 ORDER BY startdate DESC LIMIT 1),
976 WHERE chart_id= (SELECT id
980 ORDER BY startdate DESC LIMIT 1),
982 (SELECT link FROM chart WHERE accno = ?))|;
983 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
984 do_query($form, $dbh, $query, @values);
988 $form->{"paid_$i"} *= -1;
989 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
992 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
993 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
996 WHERE chart_id= (SELECT id
1000 ORDER BY startdate DESC LIMIT 1),
1003 WHERE chart_id= (SELECT id
1007 ORDER BY startdate DESC LIMIT 1),
1009 (SELECT link FROM chart WHERE accno = ?))|;
1010 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1011 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1012 do_query($form, $dbh, $query, @values);
1014 # exchangerate difference
1015 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1016 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1020 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1021 $form->{"exchangerate_$i"};
1023 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1025 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1030 # update exchange rate
1031 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1032 $form->update_exchangerate($dbh, $form->{currency},
1033 $form->{"datepaid_$i"},
1034 $form->{"exchangerate_$i"}, 0);
1038 } else { # if (!$form->{storno})
1039 $form->{marge_total} *= -1;
1042 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1044 # record exchange rate differences and gains/losses
1045 foreach my $accno (keys %{ $form->{fx} }) {
1046 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1047 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1048 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1051 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1052 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1055 WHERE chart_id= (SELECT id
1059 ORDER BY startdate DESC LIMIT 1),
1062 WHERE chart_id= (SELECT id
1066 ORDER BY startdate DESC LIMIT 1),
1068 (SELECT link FROM chart WHERE accno = ?))|;
1069 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
1070 do_query($form, $dbh, $query, @values);
1075 if ($payments_only) {
1076 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1077 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1079 $dbh->commit if !$provided_dbh;
1081 $main::lxdebug->leave_sub();
1085 $amount = $netamount + $tax;
1088 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1090 $query = qq|UPDATE ar set
1091 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1092 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1093 amount = ?, netamount = ?, paid = ?,
1094 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1095 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1096 currency_id = (SELECT id FROM currencies WHERE name = ?),
1097 department_id = ?, payment_id = ?, taxincluded = ?,
1098 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1099 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1100 cp_id = ?, marge_total = ?, marge_percent = ?,
1101 globalproject_id = ?, delivery_customer_id = ?,
1102 transaction_description = ?, delivery_vendor_id = ?,
1103 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1105 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1106 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1107 $amount, $netamount, $form->{"paid"},
1108 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1109 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1110 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1111 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1112 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1113 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1114 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1115 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1116 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1117 conv_i($form->{"id"}));
1118 do_query($form, $dbh, $query, @values);
1121 if ($form->{storno}) {
1124 paid = paid + amount,
1126 intnotes = ? || intnotes
1128 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1129 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1133 $form->{name} = $form->{customer};
1134 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1136 if (!$form->{shipto_id}) {
1137 $form->add_shipto($dbh, $form->{id}, "AR");
1140 # save printed, emailed and queued
1141 $form->save_status($dbh);
1143 Common::webdav_folder($form);
1145 # Link this record to the records it was created from.
1146 RecordLinks->create_links('dbh' => $dbh,
1148 'from_table' => 'oe',
1149 'from_ids' => $form->{convert_from_oe_ids},
1151 'to_id' => $form->{id},
1153 delete $form->{convert_from_oe_ids};
1155 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1157 if (scalar @convert_from_do_ids) {
1158 DO->close_orders('dbh' => $dbh,
1159 'ids' => \@convert_from_do_ids);
1161 RecordLinks->create_links('dbh' => $dbh,
1163 'from_table' => 'delivery_orders',
1164 'from_ids' => \@convert_from_do_ids,
1166 'to_id' => $form->{id},
1169 delete $form->{convert_from_do_ids};
1171 ARAP->close_orders_if_billed('dbh' => $dbh,
1172 'arap_id' => $form->{id},
1175 # safety check datev export
1176 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1177 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1178 $transdate ||= DateTime->today;
1180 my $datev = SL::DATEV->new(
1181 exporttype => DATEV_ET_BUCHUNGEN,
1182 format => DATEV_FORMAT_KNE,
1186 trans_id => $form->{id},
1191 if ($datev->errors) {
1193 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1198 $dbh->commit if !$provided_dbh;
1200 $main::lxdebug->leave_sub();
1205 sub _delete_payments {
1206 $main::lxdebug->enter_sub();
1208 my ($self, $form, $dbh) = @_;
1210 my @delete_acc_trans_ids;
1212 # Delete old payment entries from acc_trans.
1214 qq|SELECT acc_trans_id
1216 WHERE (trans_id = ?) AND fx_transaction
1220 SELECT at.acc_trans_id
1222 LEFT JOIN chart c ON (at.chart_id = c.id)
1223 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1224 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1227 qq|SELECT at.acc_trans_id
1229 LEFT JOIN chart c ON (at.chart_id = c.id)
1230 WHERE (trans_id = ?)
1231 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1232 ORDER BY at.acc_trans_id
1234 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1236 if (@delete_acc_trans_ids) {
1237 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1238 do_query($form, $dbh, $query);
1241 $main::lxdebug->leave_sub();
1245 $main::lxdebug->enter_sub();
1247 my ($self, $myconfig, $form, $locale) = @_;
1249 # connect to database, turn off autocommit
1250 my $dbh = $form->get_standard_dbh;
1252 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1254 $old_form = save_form();
1256 # Delete all entries in acc_trans from prior payments.
1257 if (SL::DB::Default->get->payments_changeable != 0) {
1258 $self->_delete_payments($form, $dbh);
1261 # Save the new payments the user made before cleaning up $form.
1262 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 };
1264 # Clean up $form so that old content won't tamper the results.
1265 %keep_vars = map { $_, 1 } qw(login password id);
1266 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1268 # Retrieve the invoice from the database.
1269 $self->retrieve_invoice($myconfig, $form);
1271 # Set up the content of $form in the way that IS::post_invoice() expects.
1272 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1274 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1275 $item = $form->{invoice_details}->[$row - 1];
1277 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1279 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1282 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1284 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1286 # Restore the payment options from the user input.
1287 map { $form->{$_} = $payments{$_} } keys %payments;
1289 # Get the AR accno (which is normally done by Form::create_links()).
1293 LEFT JOIN chart c ON (at.chart_id = c.id)
1294 WHERE (trans_id = ?)
1295 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1296 ORDER BY at.acc_trans_id
1299 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1301 # Post the new payments.
1302 $self->post_invoice($myconfig, $form, $dbh, 1);
1304 restore_form($old_form);
1306 my $rc = $dbh->commit();
1308 $main::lxdebug->leave_sub();
1313 sub process_assembly {
1314 $main::lxdebug->enter_sub();
1316 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1319 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1320 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1322 JOIN parts p ON (a.parts_id = p.id)
1324 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1326 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1330 $ref->{inventory_accno_id} *= 1;
1331 $ref->{expense_accno_id} *= 1;
1333 # multiply by number of assemblies
1334 $ref->{qty} *= $totalqty;
1336 if ($ref->{assembly}) {
1337 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1340 if ($ref->{inventory_accno_id}) {
1341 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1345 # save detail record for individual assembly item in invoice table
1347 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1348 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1349 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1350 do_query($form, $dbh, $query, @values);
1356 $main::lxdebug->leave_sub();
1360 $main::lxdebug->enter_sub();
1362 # adjust allocated in table invoice according to FIFO princicple
1363 # for a certain part with part_id $id
1365 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1369 $form->{taxzone_id} *=1;
1370 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1371 my $taxzone_id = $form->{"taxzone_id"} * 1;
1373 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1374 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1375 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1376 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1377 FROM invoice i, parts p
1378 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1379 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1380 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1381 WHERE (i.parts_id = p.id)
1382 AND (i.parts_id = ?)
1383 AND ((i.base_qty + i.allocated) < 0)
1385 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1390 # all invoice entries of an example part:
1392 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1393 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1394 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1395 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1396 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1397 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1398 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1400 # 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
1401 # and all parts have been allocated
1403 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1404 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1406 # in this example there are still 4 unsold articles
1409 # search all invoice entries for the part in question, adjusting "allocated"
1410 # until the total number of sold parts has been reached
1412 # ORDER BY trans_id ensures FIFO
1415 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1416 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1420 # update allocated in invoice
1421 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1423 # total expenses and inventory
1424 # sellprice is the cost of the item
1425 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1427 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1428 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1429 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1431 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1432 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1433 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1435 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1436 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1442 last if (($totalqty -= $qty) <= 0);
1447 $main::lxdebug->leave_sub();
1452 sub reverse_invoice {
1453 $main::lxdebug->enter_sub();
1455 my ($dbh, $form) = @_;
1457 # reverse inventory items
1459 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1461 JOIN parts p ON (i.parts_id = p.id)
1462 WHERE i.trans_id = ?|;
1463 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1465 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1467 if ($ref->{inventory_accno_id}) {
1468 # de-allocated purchases
1470 qq|SELECT i.id, i.trans_id, i.allocated
1472 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1473 ORDER BY i.trans_id DESC|;
1474 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1476 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1477 my $qty = $ref->{qty};
1478 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1479 $qty = $inhref->{allocated};
1483 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1485 last if (($ref->{qty} -= $qty) <= 0);
1494 my @values = (conv_i($form->{id}));
1495 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1496 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1497 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1499 $main::lxdebug->leave_sub();
1502 sub delete_invoice {
1503 $main::lxdebug->enter_sub();
1505 my ($self, $myconfig, $form) = @_;
1507 # connect to database
1508 my $dbh = $form->get_standard_dbh;
1510 &reverse_invoice($dbh, $form);
1512 my @values = (conv_i($form->{id}));
1514 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1515 # zurückgesetzt werden. Vgl:
1516 # id | storno | storno_id | paid | amount
1517 #----+--------+-----------+---------+-----------
1518 # 18 | f | | 0.00000 | 119.00000
1520 # 18 | t | | 119.00000 | 119.00000
1522 if($form->{storno}){
1523 # storno_id auslesen und korrigieren
1524 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1525 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1528 # delete spool files
1529 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1532 qq|DELETE FROM status WHERE trans_id = ?|,
1533 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1534 qq|DELETE FROM ar WHERE id = ?|,
1537 map { do_query($form, $dbh, $_, @values) } @queries;
1539 my $rc = $dbh->commit;
1542 my $spool = $::lx_office_conf{paths}->{spool};
1543 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1546 $main::lxdebug->leave_sub();
1551 sub retrieve_invoice {
1552 $main::lxdebug->enter_sub();
1554 my ($self, $myconfig, $form) = @_;
1556 # connect to database
1557 my $dbh = $form->get_standard_dbh;
1559 my ($sth, $ref, $query);
1561 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1565 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1566 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1567 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1568 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1569 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1573 $ref = selectfirst_hashref_query($form, $dbh, $query);
1574 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1577 my $id = conv_i($form->{id});
1580 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1584 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1585 a.orddate, a.quodate, a.globalproject_id,
1586 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1587 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1588 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1589 a.employee_id, a.salesman_id, a.payment_id,
1590 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1591 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1592 a.marge_total, a.marge_percent, a.direct_debit,
1595 LEFT JOIN employee e ON (e.id = a.employee_id)
1597 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1598 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1600 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1603 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1604 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1606 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1608 foreach my $vc (qw(customer vendor)) {
1609 next if !$form->{"delivery_${vc}_id"};
1610 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1613 # get printed, emailed
1614 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1615 $sth = prepare_execute_query($form, $dbh, $query, $id);
1617 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1618 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1619 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1620 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1623 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1625 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1626 : $form->{invdate} ? $dbh->quote($form->{invdate})
1630 my $taxzone_id = $form->{taxzone_id} *= 1;
1631 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1633 # retrieve individual items
1636 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1637 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1638 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1641 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1642 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1643 i.price_factor_id, i.price_factor, i.marge_price_factor,
1644 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1645 pr.projectnumber, pg.partsgroup, prg.pricegroup
1648 LEFT JOIN parts p ON (i.parts_id = p.id)
1649 LEFT JOIN project pr ON (i.project_id = pr.id)
1650 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1651 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1653 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1654 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1655 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1657 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1659 $sth = prepare_execute_query($form, $dbh, $query, $id);
1661 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1662 # Retrieve custom variables.
1663 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1665 sub_module => 'invoice',
1666 trans_id => $ref->{invoice_id},
1668 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1669 delete $ref->{invoice_id};
1671 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1672 delete($ref->{"part_inventory_accno_id"});
1674 foreach my $type (qw(inventory income expense)) {
1675 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1676 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1677 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1681 # get tax rates and description
1682 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1684 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1685 LEFT JOIN chart c ON (c.id = t.chart_id)
1687 (SELECT tk.tax_id FROM taxkeys tk
1688 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1689 AND startdate <= date($transdate)
1690 ORDER BY startdate DESC LIMIT 1)
1692 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1693 $ref->{taxaccounts} = "";
1695 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1697 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1701 $ref->{taxaccounts} .= "$ptr->{accno} ";
1703 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1704 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1705 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1706 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1707 $form->{taxaccounts} .= "$ptr->{accno} ";
1712 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1714 chop $ref->{taxaccounts};
1715 push @{ $form->{invoice_details} }, $ref;
1720 Common::webdav_folder($form);
1723 my $rc = $dbh->commit;
1725 $main::lxdebug->leave_sub();
1731 $main::lxdebug->enter_sub();
1733 my ($self, $myconfig, $form) = @_;
1735 # connect to database
1736 my $dbh = $form->get_standard_dbh;
1738 my $dateformat = $myconfig->{dateformat};
1739 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1741 my (@values, $duedate, $ref, $query);
1743 if ($form->{invdate}) {
1744 $duedate = "to_date(?, '$dateformat')";
1745 push @values, $form->{invdate};
1747 $duedate = "current_date";
1750 my $cid = conv_i($form->{customer_id});
1753 if ($form->{payment_id}) {
1754 $payment_id = "(pt.id = ?) OR";
1755 push @values, conv_i($form->{payment_id});
1761 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1762 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1763 c.street, c.zipcode, c.city, c.country,
1764 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1765 c.taxincluded_checked, c.direct_debit,
1766 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1767 b.discount AS tradediscount, b.description AS business
1769 LEFT JOIN business b ON (b.id = c.business_id)
1770 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1771 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1774 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1776 delete $ref->{salesman_id} if !$ref->{salesman_id};
1778 map { $form->{$_} = $ref->{$_} } keys %$ref;
1780 # use customer currency
1781 $form->{currency} = $form->{curr};
1784 qq|SELECT sum(amount - paid) AS dunning_amount
1786 WHERE (paid < amount)
1787 AND (customer_id = ?)
1788 AND (dunning_config_id IS NOT NULL)|;
1789 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1790 map { $form->{$_} = $ref->{$_} } keys %$ref;
1793 qq|SELECT dnn.dunning_description AS max_dunning_level
1794 FROM dunning_config dnn
1795 WHERE id IN (SELECT dunning_config_id
1797 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1798 ORDER BY dunning_level DESC LIMIT 1|;
1799 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1800 map { $form->{$_} = $ref->{$_} } keys %$ref;
1802 $form->{creditremaining} = $form->{creditlimit};
1803 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1804 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1805 $form->{creditremaining} -= $value;
1809 (SELECT e.buy FROM exchangerate e
1810 WHERE e.currency_id = o.currency_id
1811 AND e.transdate = o.transdate)
1813 WHERE o.customer_id = ?
1814 AND o.quotation = '0'
1815 AND o.closed = '0'|;
1816 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1818 while (my ($amount, $exch) = $sth->fetchrow_array) {
1819 $exch = 1 unless $exch;
1820 $form->{creditremaining} -= $amount * $exch;
1824 # get shipto if we did not converted an order or invoice
1825 if (!$form->{shipto}) {
1826 map { delete $form->{$_} }
1827 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1828 shiptostreet shiptozipcode shiptocity shiptocountry
1829 shiptocontact shiptophone shiptofax shiptoemail);
1831 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1832 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1834 map { $form->{$_} = $ref->{$_} } keys %$ref;
1837 # setup last accounts used for this customer
1838 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1840 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1842 JOIN acc_trans ac ON (ac.chart_id = c.id)
1843 JOIN ar a ON (a.id = ac.trans_id)
1844 WHERE a.customer_id = ?
1845 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1846 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1847 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1850 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1851 if ($ref->{category} eq 'I') {
1853 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1855 if ($form->{initial_transdate}) {
1857 qq|SELECT tk.tax_id, t.rate
1859 LEFT JOIN tax t ON tk.tax_id = t.id
1860 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1861 ORDER BY tk.startdate DESC
1863 my ($tax_id, $rate) =
1864 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1865 $form->{initial_transdate});
1866 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1869 if ($ref->{category} eq 'A') {
1870 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1874 $form->{rowcount} = $i if ($i && !$form->{type});
1877 $main::lxdebug->leave_sub();
1881 $main::lxdebug->enter_sub();
1883 my ($self, $myconfig, $form) = @_;
1885 # connect to database
1886 my $dbh = $form->get_standard_dbh;
1888 my $i = $form->{rowcount};
1890 my $where = qq|NOT p.obsolete = '1'|;
1893 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1894 my ($table, $field) = split m/\./, $column;
1895 next if !$form->{"${field}_${i}"};
1896 $where .= qq| AND lower(${column}) ILIKE ?|;
1897 push @values, '%' . $form->{"${field}_${i}"} . '%';
1900 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1901 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1902 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1903 push @values, $form->{"partnumber_$i"};
1906 # Search for part ID overrides all other criteria.
1907 if ($form->{"id_${i}"}) {
1908 $where = qq|p.id = ?|;
1909 @values = ($form->{"id_${i}"});
1912 if ($form->{"description_$i"}) {
1913 $where .= qq| ORDER BY p.description|;
1915 $where .= qq| ORDER BY p.partnumber|;
1919 if ($form->{type} eq "invoice") {
1921 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1922 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1926 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1930 my $taxzone_id = $form->{taxzone_id} * 1;
1931 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1935 p.id, p.partnumber, p.description, p.sellprice,
1936 p.listprice, p.inventory_accno_id, p.lastcost,
1938 c1.accno AS inventory_accno,
1939 c1.new_chart_id AS inventory_new_chart,
1940 date($transdate) - c1.valid_from AS inventory_valid,
1942 c2.accno AS income_accno,
1943 c2.new_chart_id AS income_new_chart,
1944 date($transdate) - c2.valid_from AS income_valid,
1946 c3.accno AS expense_accno,
1947 c3.new_chart_id AS expense_new_chart,
1948 date($transdate) - c3.valid_from AS expense_valid,
1950 p.unit, p.assembly, p.onhand,
1951 p.notes AS partnotes, p.notes AS longdescription,
1952 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1953 p.price_factor_id, p.weight,
1955 pfac.factor AS price_factor,
1960 LEFT JOIN chart c1 ON
1961 ((SELECT inventory_accno_id
1962 FROM buchungsgruppen
1963 WHERE id = p.buchungsgruppen_id) = c1.id)
1964 LEFT JOIN chart c2 ON
1965 ((SELECT income_accno_id_${taxzone_id}
1966 FROM buchungsgruppen
1967 WHERE id = p.buchungsgruppen_id) = c2.id)
1968 LEFT JOIN chart c3 ON
1969 ((SELECT expense_accno_id_${taxzone_id}
1970 FROM buchungsgruppen
1971 WHERE id = p.buchungsgruppen_id) = c3.id)
1972 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1973 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1975 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1977 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1979 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1980 [ qq|SELECT tr.translation, tr.longdescription
1982 WHERE tr.language_id IN
1985 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1988 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1990 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1992 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1993 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1994 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1995 if (!$ref->{inventory_accno_id}) {
1996 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1998 delete($ref->{inventory_accno_id});
2000 foreach my $type (qw(inventory income expense)) {
2001 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2003 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2006 ($ref->{"${type}_accno"},
2007 $ref->{"${type}_new_chart"},
2008 $ref->{"${type}_valid"})
2009 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2013 if ($form->{payment_id} eq "") {
2014 $form->{payment_id} = $form->{part_payment_id};
2017 # get tax rates and description
2018 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2020 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2022 LEFT JOIN chart c ON (c.id = t.chart_id)
2026 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2028 ORDER BY startdate DESC
2031 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2032 my $stw = $dbh->prepare($query);
2033 $stw->execute(@values) || $form->dberror($query);
2035 $ref->{taxaccounts} = "";
2037 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2039 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2043 $ref->{taxaccounts} .= "$ptr->{accno} ";
2045 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2046 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2047 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2048 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2049 $form->{taxaccounts} .= "$ptr->{accno} ";
2055 chop $ref->{taxaccounts};
2057 if ($form->{language_id}) {
2058 for my $spec (@translation_queries) {
2059 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2060 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2061 next unless $translation;
2062 $ref->{description} = $translation;
2063 $ref->{longdescription} = $longdescription;
2068 $ref->{onhand} *= 1;
2070 push @{ $form->{item_list} }, $ref;
2073 $_->[1]->finish for @translation_queries;
2075 foreach my $item (@{ $form->{item_list} }) {
2076 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2077 trans_id => $item->{id},
2081 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2084 $main::lxdebug->leave_sub();
2087 ##########################
2088 # get pricegroups from database
2089 # build up selected pricegroup
2090 # if an exchange rate - change price
2093 sub get_pricegroups_for_parts {
2095 $main::lxdebug->enter_sub();
2097 my ($self, $myconfig, $form) = @_;
2099 my $dbh = $form->get_standard_dbh;
2101 $form->{"PRICES"} = {};
2105 my $all_units = AM->retrieve_units($myconfig, $form);
2106 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2107 $form->{"PRICES"}{$i} = [];
2109 $id = $form->{"id_$i"};
2111 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2112 $id = $form->{"new_id_$i"};
2115 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2117 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2119 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2120 my $sellprice = $form->{"sellprice_$i"};
2121 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2122 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2123 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2125 my $price_new = $form->{"price_new_$i"};
2126 my $price_old = $form->{"price_old_$i"};
2128 if (!$form->{"unit_old_$i"}) {
2129 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2130 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2131 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2132 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2135 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2136 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2137 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2139 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2140 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2141 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2142 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2143 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2144 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2145 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2150 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2151 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2152 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2153 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2154 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2158 if (!$form->{"basefactor_$i"}) {
2159 $form->{"basefactor_$i"} = 1;
2165 sellprice AS default_sellprice,
2168 'selected' AS selected
2174 parts.sellprice AS default_sellprice,
2175 pricegroup.pricegroup,
2179 LEFT JOIN parts ON parts.id = parts_id
2180 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2182 ORDER BY pricegroup|;
2183 my @values = (conv_i($id), conv_i($id));
2184 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2186 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2188 $pkr->{selected} = '';
2190 # if there is an exchange rate change price
2191 if (($form->{exchangerate} * 1) != 0) {
2192 $pkr->{price} /= $form->{exchangerate};
2195 $pkr->{price} *= $form->{"basefactor_$i"};
2196 $pkr->{price} *= $basefactor;
2197 $pkr->{price_ufmt} = $pkr->{price};
2198 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2200 if (!defined $selectedpricegroup_id) {
2201 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2202 # Case A: open old invoice, no pricegroup selected
2203 # Case B: add new article to invoice, no pricegroup selected
2205 # to distinguish case A and B the variable pricegroup_id_$i is used
2206 # for new articles this variable isn't defined, for loaded articles it is
2207 # sellprice can't be used, as it already has 0,00 set
2209 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2211 $pkr->{selected} = ' selected';
2212 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2213 and not defined $form->{"pricegroup_id_$i"}
2214 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2215 # for the case where pricegroup prices haven't been set
2217 # Case B: use default pricegroup of customer
2219 $pkr->{selected} = ' selected'; # unless $form->{selected};
2220 # no customer pricesgroup set
2221 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2223 $pkr->{price} = $form->{"sellprice_$i"};
2227 # this sub should not set anything and only return. --sschoeling, 20090506
2228 # is this correct? put in again... -- grichardson 20110119
2229 $form->{"sellprice_$i"} = $pkr->{price};
2232 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2233 $pkr->{price} = $form->{"sellprice_$i"};
2234 $pkr->{selected} = ' selected';
2238 # existing article: pricegroup or price changed
2239 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2240 if ($selectedpricegroup_id ne $pricegroup_old) {
2241 # pricegroup has changed
2242 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2243 $pkr->{selected} = ' selected';
2245 } elsif ( ($form->parse_amount($myconfig, $price_new)
2246 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2247 and ($price_new ne 0) and defined $price_new) {
2248 # sellprice has changed
2249 # when loading existing invoices $price_new is NULL
2250 if ($pkr->{pricegroup_id} == 0) {
2251 $pkr->{price} = $form->{"sellprice_$i"};
2252 $pkr->{selected} = ' selected';
2254 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2255 # neither sellprice nor pricegroup changed
2256 $pkr->{selected} = ' selected';
2257 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2258 # $pkr->{price} = $form->{"sellprice_$i"};
2260 $pkr->{price} = $form->{"sellprice_$i"};
2264 push @{ $form->{PRICES}{$i} }, $pkr;
2267 $form->{"basefactor_$i"} *= $basefactor;
2274 $main::lxdebug->leave_sub();
2278 $main::lxdebug->enter_sub();
2280 my ($self, $myconfig, $form, $table) = @_;
2282 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2284 # make sure there's no funny stuff in $table
2285 # ToDO: die when this happens and throw an error
2286 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2288 my $dbh = $form->get_standard_dbh;
2290 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2291 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2293 $main::lxdebug->leave_sub();
2299 $main::lxdebug->enter_sub();
2301 my ($self, $myconfig, $form, $table, $id) = @_;
2303 $main::lxdebug->leave_sub() and return 0 unless ($id);
2305 # make sure there's no funny stuff in $table
2306 # ToDO: die when this happens and throw an error
2307 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2309 my $dbh = $form->get_standard_dbh;
2311 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2312 my ($result) = selectrow_query($form, $dbh, $query, $id);
2314 $main::lxdebug->leave_sub();
2319 sub get_standard_accno_current_assets {
2320 $main::lxdebug->enter_sub();
2322 my ($self, $myconfig, $form) = @_;
2324 my $dbh = $form->get_standard_dbh;
2326 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2327 my ($result) = selectrow_query($form, $dbh, $query);
2329 $main::lxdebug->leave_sub();