1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup weight lineweight);
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);
168 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
171 if ($item->[1] ne $sameitem) {
172 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
173 $sameitem = $item->[1];
175 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
178 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
180 if ($form->{"id_$i"} != 0) {
182 # add number, description and qty to $form->{number},
183 if ($form->{"subtotal_$i"} && !$subtotal_header) {
184 $subtotal_header = $i;
185 $position = int($position);
188 } elsif ($subtotal_header) {
190 $position = int($position);
191 $position = $position.".".$subposition;
193 $position = int($position);
197 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
199 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
200 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
207 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
212 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
217 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
218 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
219 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
220 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
222 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
223 my ($dec) = ($sellprice =~ /\.(\d+)/);
224 my $decimalplaces = max 2, length($dec);
226 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
228 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
229 my $linetotal = $form->round_amount($linetotal_exact, 2);
231 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
232 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
234 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
236 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
238 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
241 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
243 $linetotal = ($linetotal != 0) ? $linetotal : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
247 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
249 $form->{total} += $linetotal;
250 $form->{nodiscount_total} += $nodiscount_linetotal;
251 $form->{discount_total} += $discount;
253 if ($subtotal_header) {
254 $discount_subtotal += $linetotal;
255 $nodiscount_subtotal += $nodiscount_linetotal;
258 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
264 $discount_subtotal = 0;
265 $nodiscount_subtotal = 0;
266 $subtotal_header = 0;
269 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
272 if (!$form->{"discount_$i"}) {
273 $nodiscount += $linetotal;
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
277 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
279 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
282 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
284 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
285 $totalweight += $lineweight;
286 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
287 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
288 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
289 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
291 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
295 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
297 if ($form->{taxincluded}) {
300 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
301 $taxbase = $linetotal - $taxamount;
303 $taxamount = $linetotal * $taxrate;
304 $taxbase = $linetotal;
307 if ($form->round_amount($taxrate, 7) == 0) {
308 if ($form->{taxincluded}) {
309 foreach my $accno (@taxaccounts) {
310 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
312 $taxaccounts{$accno} += $taxamount;
313 $taxdiff += $taxamount;
315 $taxbase{$accno} += $taxbase;
317 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
319 foreach my $accno (@taxaccounts) {
320 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
321 $taxbase{$accno} += $taxbase;
325 foreach my $accno (@taxaccounts) {
326 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
327 $taxbase{$accno} += $taxbase;
330 my $tax_rate = $taxrate * 100;
331 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
332 if ($form->{"assembly_$i"}) {
335 # get parts and push them onto the stack
337 if ($form->{groupitems}) {
339 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
341 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
345 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
347 JOIN parts p ON (a.parts_id = p.id)
348 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
349 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
350 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
352 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
353 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
354 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
355 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
356 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
359 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
361 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
362 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
364 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
365 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
371 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
372 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
373 for @{ $ic_cvar_configs };
377 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
378 $form->{totalweight_nofmt} = $totalweight;
379 my $defaults = AM->get_defaults();
380 $form->{weightunit} = $defaults->{weightunit};
382 foreach my $item (sort keys %taxaccounts) {
383 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
386 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
388 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
389 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
390 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
391 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
392 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
395 for my $i (1 .. $form->{paidaccounts}) {
396 if ($form->{"paid_$i"}) {
397 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
399 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
400 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
401 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
402 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
403 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
405 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
408 if($form->{taxincluded}) {
409 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
410 $form->{subtotal_nofmt} = $form->{total} - $tax;
413 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
414 $form->{subtotal_nofmt} = $form->{total};
417 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
418 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
419 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
420 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
422 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
423 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
425 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
426 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
428 $form->set_payment_options($myconfig, $form->{invdate});
430 $form->{username} = $myconfig->{name};
432 $main::lxdebug->leave_sub();
435 sub project_description {
436 $main::lxdebug->enter_sub();
438 my ($self, $dbh, $id) = @_;
439 my $form = \%main::form;
441 my $query = qq|SELECT description FROM project WHERE id = ?|;
442 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
444 $main::lxdebug->leave_sub();
449 sub customer_details {
450 $main::lxdebug->enter_sub();
452 my ($self, $myconfig, $form, @wanted_vars) = @_;
454 # connect to database
455 my $dbh = $form->get_standard_dbh;
457 my $language_id = $form->{language_id};
459 # get contact id, set it if nessessary
462 my @values = (conv_i($form->{customer_id}));
465 if ($form->{cp_id}) {
466 $where = qq| AND (cp.cp_id = ?) |;
467 push(@values, conv_i($form->{cp_id}));
470 # get rest for the customer
472 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
473 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
476 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
477 WHERE (ct.id = ?) $where
480 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
482 # remove id and taxincluded before copy back
483 delete @$ref{qw(id taxincluded)};
485 @wanted_vars = grep({ $_ } @wanted_vars);
486 if (scalar(@wanted_vars) > 0) {
488 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
489 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
492 map { $form->{$_} = $ref->{$_} } keys %$ref;
494 # remove any trailing whitespace
495 $form->{currency} =~ s/\s*$// if ($form->{currency});
497 if ($form->{delivery_customer_id}) {
499 qq|SELECT *, notes as customernotes
503 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
505 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
508 if ($form->{delivery_vendor_id}) {
510 qq|SELECT *, notes as customernotes
514 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
516 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
519 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
521 'trans_id' => $form->{customer_id});
522 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
524 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
525 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
526 'language_id' => $language_id,
527 'allow_fallback' => 1);
530 $main::lxdebug->leave_sub();
534 $main::lxdebug->enter_sub();
536 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
538 # connect to database, turn off autocommit
539 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
541 my ($query, $sth, $null, $project_id, @values);
542 my $exchangerate = 0;
544 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
547 if (!$form->{employee_id}) {
548 $form->get_employee($dbh);
551 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
552 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
553 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
554 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
555 if (!$form->{department_id}){
556 ($null, $form->{department_id}) = split(/--/, $form->{department});
559 my $all_units = AM->retrieve_units($myconfig, $form);
561 if (!$payments_only) {
563 &reverse_invoice($dbh, $form);
566 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
567 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
569 $query = qq|SELECT nextval('glid')|;
570 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
572 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
573 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
575 if (!$form->{invnumber}) {
577 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
578 "cnnumber" : "invnumber", $dbh);
583 my ($netamount, $invoicediff) = (0, 0);
584 my ($amount, $linetotal, $lastincomeaccno);
586 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
587 my $defaultcurrency = (split m/:/, $currencies)[0];
589 if ($form->{currency} eq $defaultcurrency) {
590 $form->{exchangerate} = 1;
592 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
595 $form->{exchangerate} =
598 : $form->parse_amount($myconfig, $form->{exchangerate});
600 $form->{expense_inventory} = "";
604 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
605 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
608 $form->{amount} = {};
609 $form->{amount_cogs} = {};
611 foreach my $i (1 .. $form->{rowcount}) {
612 if ($form->{type} eq "credit_note") {
613 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
614 $form->{shipped} = 1;
616 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
621 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
622 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
623 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
625 if ($form->{storno}) {
626 $form->{"qty_$i"} *= -1;
629 if ($form->{"id_$i"}) {
632 if (defined($baseunits{$form->{"id_$i"}})) {
633 $item_unit = $baseunits{$form->{"id_$i"}};
636 $query = qq|SELECT unit FROM parts WHERE id = ?|;
637 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
638 $baseunits{$form->{"id_$i"}} = $item_unit;
641 if (defined($all_units->{$item_unit}->{factor})
642 && ($all_units->{$item_unit}->{factor} ne '')
643 && ($all_units->{$item_unit}->{factor} != 0)) {
644 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
648 $baseqty = $form->{"qty_$i"} * $basefactor;
650 my ($allocated, $taxrate) = (0, 0);
654 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
656 # keep entered selling price
658 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
660 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
662 my $decimalplaces = ($dec > 2) ? $dec : 2;
664 # undo discount formatting
665 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
668 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
670 # round linetotal to 2 decimal places
671 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
672 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
674 if ($form->{taxincluded}) {
675 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
676 $form->{"sellprice_$i"} =
677 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
679 $taxamount = $linetotal * $taxrate;
682 $netamount += $linetotal;
684 if ($taxamount != 0) {
686 $form->{amount}{ $form->{id} }{$_} +=
687 $taxamount * $form->{"${_}_rate"} / $taxrate
688 } split(/ /, $form->{"taxaccounts_$i"});
691 # add amount to income, $form->{amount}{trans_id}{accno}
692 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
694 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
695 $linetotal = $form->round_amount($linetotal, 2);
697 # this is the difference from the inventory
698 $invoicediff += ($amount - $linetotal);
700 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
703 $lastincomeaccno = $form->{"income_accno_$i"};
705 # adjust and round sellprice
706 $form->{"sellprice_$i"} =
707 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
710 next if $payments_only;
712 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
714 if ($form->{"assembly_$i"}) {
715 # record assembly item as allocated
716 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
719 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
723 # Get pricegroup_id and save it. Unfortunately the interface
724 # also uses ID "0" for signalling that none is selected, but "0"
725 # must not be stored in the database. Therefore we cannot simply
727 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
729 $pricegroup_id = undef if !$pricegroup_id;
731 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
733 # save detail record in invoice table
735 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
736 sellprice, fxsellprice, discount, allocated, assemblyitem,
737 unit, deliverydate, project_id, serialnumber, pricegroup_id,
738 ordnumber, transdate, cusordnumber, base_qty, subtotal,
739 marge_percent, marge_total, lastcost,
740 price_factor_id, price_factor, marge_price_factor)
741 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
742 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
744 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
745 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
746 $form->{"sellprice_$i"}, $fxsellprice,
747 $form->{"discount_$i"}, $allocated, 'f',
748 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
749 $form->{"serialnumber_$i"}, $pricegroup_id,
750 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
751 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
752 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
753 $form->{"lastcost_$i"},
754 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
755 conv_i($form->{"marge_price_factor_$i"}));
756 do_query($form, $dbh, $query, @values);
758 CVar->save_custom_variables(module => 'IC',
759 sub_module => 'invoice',
760 trans_id => $invoice_id,
761 configs => $ic_cvar_configs,
763 name_prefix => 'ic_',
764 name_postfix => "_$i",
769 # total payments, don't move we need it here
770 for my $i (1 .. $form->{paidaccounts}) {
771 if ($form->{type} eq "credit_note") {
772 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
774 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
776 $form->{paid} += $form->{"paid_$i"};
777 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
780 my ($tax, $diff) = (0, 0);
782 $netamount = $form->round_amount($netamount, 2);
784 # figure out rounding errors for total amount vs netamount + taxes
785 if ($form->{taxincluded}) {
787 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
788 $diff += $amount - $netamount * $form->{exchangerate};
789 $netamount = $amount;
791 foreach my $item (split(/ /, $form->{taxaccounts})) {
792 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
793 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
794 $tax += $form->{amount}{ $form->{id} }{$item};
795 $netamount -= $form->{amount}{ $form->{id} }{$item};
798 $invoicediff += $diff;
799 ######## this only applies to tax included
800 if ($lastincomeaccno) {
801 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
805 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
806 $diff = $amount - $netamount * $form->{exchangerate};
807 $netamount = $amount;
808 foreach my $item (split(/ /, $form->{taxaccounts})) {
809 $form->{amount}{ $form->{id} }{$item} =
810 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
813 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
816 $amount - $form->{amount}{ $form->{id} }{$item} *
817 $form->{exchangerate};
818 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
819 $tax += $form->{amount}{ $form->{id} }{$item};
823 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
825 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
828 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
830 # update exchangerate
831 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
832 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
833 $form->{exchangerate}, 0);
836 $project_id = conv_i($form->{"globalproject_id"});
837 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
838 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
840 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
841 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
842 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
844 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
846 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
848 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
850 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
851 do_query($form, $dbh, $query, @values);
852 $form->{amount_cogs}{$trans_id}{$accno} = 0;
856 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
857 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
859 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
861 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
862 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
863 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
864 do_query($form, $dbh, $query, @values);
869 foreach my $trans_id (keys %{ $form->{amount} }) {
870 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
871 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
873 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
875 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
877 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
878 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
881 WHERE chart_id= (SELECT id
885 ORDER BY startdate DESC LIMIT 1),
888 WHERE chart_id= (SELECT id
892 ORDER BY startdate DESC LIMIT 1),
894 (SELECT link FROM chart WHERE accno = ?))|;
895 @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);
896 do_query($form, $dbh, $query, @values);
897 $form->{amount}{$trans_id}{$accno} = 0;
901 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
902 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
904 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
906 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
907 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
910 WHERE chart_id= (SELECT id
914 ORDER BY startdate DESC LIMIT 1),
917 WHERE chart_id= (SELECT id
921 ORDER BY startdate DESC LIMIT 1),
923 (SELECT link FROM chart WHERE accno = ?))|;
924 @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);
925 do_query($form, $dbh, $query, @values);
930 # deduct payment differences from diff
931 for my $i (1 .. $form->{paidaccounts}) {
932 if ($form->{"paid_$i"} != 0) {
934 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
935 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
939 # record payments and offsetting AR
940 if (!$form->{storno}) {
941 for my $i (1 .. $form->{paidaccounts}) {
943 if ($form->{"acc_trans_id_$i"}
945 && (SL::DB::Default->get->payments_changeable == 0)) {
949 next if ($form->{"paid_$i"} == 0);
951 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
952 $form->{"datepaid_$i"} = $form->{invdate}
953 unless ($form->{"datepaid_$i"});
954 $form->{datepaid} = $form->{"datepaid_$i"};
958 if ($form->{currency} eq $defaultcurrency) {
959 $form->{"exchangerate_$i"} = 1;
961 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
962 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
966 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
968 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
970 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
971 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
974 WHERE chart_id= (SELECT id
978 ORDER BY startdate DESC LIMIT 1),
981 WHERE chart_id= (SELECT id
985 ORDER BY startdate DESC LIMIT 1),
987 (SELECT link FROM chart WHERE accno = ?))|;
988 @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});
989 do_query($form, $dbh, $query, @values);
993 $form->{"paid_$i"} *= -1;
994 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
997 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
998 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1001 WHERE chart_id= (SELECT id
1005 ORDER BY startdate DESC LIMIT 1),
1008 WHERE chart_id= (SELECT id
1012 ORDER BY startdate DESC LIMIT 1),
1014 (SELECT link FROM chart WHERE accno = ?))|;
1015 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1016 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1017 do_query($form, $dbh, $query, @values);
1019 # exchangerate difference
1020 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1021 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1025 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1026 $form->{"exchangerate_$i"};
1028 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1030 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1035 # update exchange rate
1036 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1037 $form->update_exchangerate($dbh, $form->{currency},
1038 $form->{"datepaid_$i"},
1039 $form->{"exchangerate_$i"}, 0);
1043 } else { # if (!$form->{storno})
1044 $form->{marge_total} *= -1;
1047 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1049 # record exchange rate differences and gains/losses
1050 foreach my $accno (keys %{ $form->{fx} }) {
1051 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1052 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1053 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1056 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1057 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1060 WHERE chart_id= (SELECT id
1064 ORDER BY startdate DESC LIMIT 1),
1067 WHERE chart_id= (SELECT id
1071 ORDER BY startdate DESC LIMIT 1),
1073 (SELECT link FROM chart WHERE accno = ?))|;
1074 @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);
1075 do_query($form, $dbh, $query, @values);
1080 if ($payments_only) {
1081 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1082 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1084 $dbh->commit if !$provided_dbh;
1086 $main::lxdebug->leave_sub();
1090 $amount = $netamount + $tax;
1093 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1095 $query = qq|UPDATE ar set
1096 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1097 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1098 amount = ?, netamount = ?, paid = ?,
1099 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1100 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1101 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1102 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1103 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1104 cp_id = ?, marge_total = ?, marge_percent = ?,
1105 globalproject_id = ?, delivery_customer_id = ?,
1106 transaction_description = ?, delivery_vendor_id = ?,
1107 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1109 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1110 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1111 $amount, $netamount, $form->{"paid"},
1112 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1113 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1114 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1115 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1116 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1117 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1118 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1119 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1120 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1121 conv_i($form->{"id"}));
1122 do_query($form, $dbh, $query, @values);
1125 if ($form->{storno}) {
1128 paid = paid + amount,
1130 intnotes = ? || intnotes
1132 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1133 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1137 $form->{name} = $form->{customer};
1138 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1140 if (!$form->{shipto_id}) {
1141 $form->add_shipto($dbh, $form->{id}, "AR");
1144 # save printed, emailed and queued
1145 $form->save_status($dbh);
1147 Common::webdav_folder($form);
1149 # Link this record to the records it was created from.
1150 RecordLinks->create_links('dbh' => $dbh,
1152 'from_table' => 'oe',
1153 'from_ids' => $form->{convert_from_oe_ids},
1155 'to_id' => $form->{id},
1157 delete $form->{convert_from_oe_ids};
1159 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1161 if (scalar @convert_from_do_ids) {
1162 DO->close_orders('dbh' => $dbh,
1163 'ids' => \@convert_from_do_ids);
1165 RecordLinks->create_links('dbh' => $dbh,
1167 'from_table' => 'delivery_orders',
1168 'from_ids' => \@convert_from_do_ids,
1170 'to_id' => $form->{id},
1173 delete $form->{convert_from_do_ids};
1175 ARAP->close_orders_if_billed('dbh' => $dbh,
1176 'arap_id' => $form->{id},
1179 # safety check datev export
1180 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1181 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1182 $transdate ||= DateTime->today;
1184 my $datev = SL::DATEV->new(
1185 exporttype => DATEV_ET_BUCHUNGEN,
1186 format => DATEV_FORMAT_KNE,
1190 trans_id => $form->{id},
1195 if ($datev->errors) {
1197 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1202 $dbh->commit if !$provided_dbh;
1204 $main::lxdebug->leave_sub();
1209 sub _delete_payments {
1210 $main::lxdebug->enter_sub();
1212 my ($self, $form, $dbh) = @_;
1214 my @delete_acc_trans_ids;
1216 # Delete old payment entries from acc_trans.
1218 qq|SELECT acc_trans_id
1220 WHERE (trans_id = ?) AND fx_transaction
1224 SELECT at.acc_trans_id
1226 LEFT JOIN chart c ON (at.chart_id = c.id)
1227 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1228 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1231 qq|SELECT at.acc_trans_id
1233 LEFT JOIN chart c ON (at.chart_id = c.id)
1234 WHERE (trans_id = ?)
1235 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1236 ORDER BY at.acc_trans_id
1238 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1240 if (@delete_acc_trans_ids) {
1241 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1242 do_query($form, $dbh, $query);
1245 $main::lxdebug->leave_sub();
1249 $main::lxdebug->enter_sub();
1251 my ($self, $myconfig, $form, $locale) = @_;
1253 # connect to database, turn off autocommit
1254 my $dbh = $form->get_standard_dbh;
1257 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1259 $old_form = save_form();
1261 # Delete all entries in acc_trans from prior payments.
1262 if (SL::DB::Default->get->payments_changeable != 0) {
1263 $self->_delete_payments($form, $dbh);
1266 # Save the new payments the user made before cleaning up $form.
1267 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 };
1269 # Clean up $form so that old content won't tamper the results.
1270 %keep_vars = map { $_, 1 } qw(login password id);
1271 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1273 # Retrieve the invoice from the database.
1274 $self->retrieve_invoice($myconfig, $form);
1276 # Set up the content of $form in the way that IS::post_invoice() expects.
1277 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1279 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1280 $item = $form->{invoice_details}->[$row - 1];
1282 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1284 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1287 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1289 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1291 # Restore the payment options from the user input.
1292 map { $form->{$_} = $payments{$_} } keys %payments;
1294 # Get the AR accno (which is normally done by Form::create_links()).
1298 LEFT JOIN chart c ON (at.chart_id = c.id)
1299 WHERE (trans_id = ?)
1300 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1301 ORDER BY at.acc_trans_id
1304 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1306 # Post the new payments.
1307 $self->post_invoice($myconfig, $form, $dbh, 1);
1309 restore_form($old_form);
1311 my $rc = $dbh->commit();
1313 $main::lxdebug->leave_sub();
1318 sub process_assembly {
1319 $main::lxdebug->enter_sub();
1321 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1324 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1325 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1327 JOIN parts p ON (a.parts_id = p.id)
1329 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1331 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1335 $ref->{inventory_accno_id} *= 1;
1336 $ref->{expense_accno_id} *= 1;
1338 # multiply by number of assemblies
1339 $ref->{qty} *= $totalqty;
1341 if ($ref->{assembly}) {
1342 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1345 if ($ref->{inventory_accno_id}) {
1346 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1350 # save detail record for individual assembly item in invoice table
1352 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1353 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1354 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1355 do_query($form, $dbh, $query, @values);
1361 $main::lxdebug->leave_sub();
1365 $main::lxdebug->enter_sub();
1367 # adjust allocated in table invoice according to FIFO princicple
1368 # for a certain part with part_id $id
1370 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1374 $form->{taxzone_id} *=1;
1375 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1376 my $taxzone_id = $form->{"taxzone_id"} * 1;
1378 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1379 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1380 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1381 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1382 FROM invoice i, parts p
1383 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1384 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1385 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1386 WHERE (i.parts_id = p.id)
1387 AND (i.parts_id = ?)
1388 AND ((i.base_qty + i.allocated) < 0)
1390 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1395 # all invoice entries of an example part:
1397 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1398 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1399 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1400 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1401 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1402 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1403 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1405 # 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
1406 # and all parts have been allocated
1408 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1409 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1411 # in this example there are still 4 unsold articles
1414 # search all invoice entries for the part in question, adjusting "allocated"
1415 # until the total number of sold parts has been reached
1417 # ORDER BY trans_id ensures FIFO
1420 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1421 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1425 # update allocated in invoice
1426 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1428 # total expenses and inventory
1429 # sellprice is the cost of the item
1430 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1432 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1433 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1434 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1436 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1437 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1438 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1440 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1441 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1447 last if (($totalqty -= $qty) <= 0);
1452 $main::lxdebug->leave_sub();
1457 sub reverse_invoice {
1458 $main::lxdebug->enter_sub();
1460 my ($dbh, $form) = @_;
1462 # reverse inventory items
1464 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1466 JOIN parts p ON (i.parts_id = p.id)
1467 WHERE i.trans_id = ?|;
1468 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1470 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1472 if ($ref->{inventory_accno_id}) {
1473 # de-allocated purchases
1475 qq|SELECT i.id, i.trans_id, i.allocated
1477 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1478 ORDER BY i.trans_id DESC|;
1479 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1481 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1482 my $qty = $ref->{qty};
1483 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1484 $qty = $inhref->{allocated};
1488 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1490 last if (($ref->{qty} -= $qty) <= 0);
1499 my @values = (conv_i($form->{id}));
1500 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1501 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1502 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1504 $main::lxdebug->leave_sub();
1507 sub delete_invoice {
1508 $main::lxdebug->enter_sub();
1510 my ($self, $myconfig, $form) = @_;
1512 # connect to database
1513 my $dbh = $form->get_standard_dbh;
1516 &reverse_invoice($dbh, $form);
1518 my @values = (conv_i($form->{id}));
1520 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1521 # zurückgesetzt werden. Vgl:
1522 # id | storno | storno_id | paid | amount
1523 #----+--------+-----------+---------+-----------
1524 # 18 | f | | 0.00000 | 119.00000
1526 # 18 | t | | 119.00000 | 119.00000
1528 if($form->{storno}){
1529 # storno_id auslesen und korrigieren
1530 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1531 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1534 # delete spool files
1535 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1538 qq|DELETE FROM status WHERE trans_id = ?|,
1539 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1540 qq|DELETE FROM ar WHERE id = ?|,
1543 map { do_query($form, $dbh, $_, @values) } @queries;
1545 my $rc = $dbh->commit;
1548 my $spool = $::lx_office_conf{paths}->{spool};
1549 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1552 $main::lxdebug->leave_sub();
1557 sub retrieve_invoice {
1558 $main::lxdebug->enter_sub();
1560 my ($self, $myconfig, $form) = @_;
1562 # connect to database
1563 my $dbh = $form->get_standard_dbh;
1565 my ($sth, $ref, $query);
1567 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1571 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1572 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1573 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1574 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1575 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1576 d.curr AS currencies
1580 $ref = selectfirst_hashref_query($form, $dbh, $query);
1581 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1584 my $id = conv_i($form->{id});
1587 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1591 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1592 a.orddate, a.quodate, a.globalproject_id,
1593 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1594 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1595 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1596 a.employee_id, a.salesman_id, a.payment_id,
1597 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1598 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1599 a.marge_total, a.marge_percent, a.direct_debit,
1602 LEFT JOIN employee e ON (e.id = a.employee_id)
1604 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1605 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1607 # remove any trailing whitespace
1608 $form->{currency} =~ s/\s*$//;
1610 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1613 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1614 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1616 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1618 foreach my $vc (qw(customer vendor)) {
1619 next if !$form->{"delivery_${vc}_id"};
1620 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1623 # get printed, emailed
1624 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1625 $sth = prepare_execute_query($form, $dbh, $query, $id);
1627 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1628 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1629 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1630 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1633 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1635 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1636 : $form->{invdate} ? $dbh->quote($form->{invdate})
1640 my $taxzone_id = $form->{taxzone_id} *= 1;
1641 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1643 # retrieve individual items
1646 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1647 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1648 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1651 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1652 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1653 i.price_factor_id, i.price_factor, i.marge_price_factor,
1654 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1655 pr.projectnumber, pg.partsgroup, prg.pricegroup
1658 LEFT JOIN parts p ON (i.parts_id = p.id)
1659 LEFT JOIN project pr ON (i.project_id = pr.id)
1660 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1661 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1663 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1664 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1665 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1667 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1669 $sth = prepare_execute_query($form, $dbh, $query, $id);
1671 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1672 # Retrieve custom variables.
1673 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1675 sub_module => 'invoice',
1676 trans_id => $ref->{invoice_id},
1678 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1679 delete $ref->{invoice_id};
1681 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1682 delete($ref->{"part_inventory_accno_id"});
1684 foreach my $type (qw(inventory income expense)) {
1685 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1686 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1687 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1691 # get tax rates and description
1692 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1694 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1695 LEFT JOIN chart c ON (c.id = t.chart_id)
1697 (SELECT tk.tax_id FROM taxkeys tk
1698 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1699 AND startdate <= date($transdate)
1700 ORDER BY startdate DESC LIMIT 1)
1702 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1703 $ref->{taxaccounts} = "";
1705 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1707 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1711 $ref->{taxaccounts} .= "$ptr->{accno} ";
1713 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1714 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1715 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1716 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1717 $form->{taxaccounts} .= "$ptr->{accno} ";
1722 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1724 chop $ref->{taxaccounts};
1725 push @{ $form->{invoice_details} }, $ref;
1730 Common::webdav_folder($form);
1733 my $rc = $dbh->commit;
1735 $main::lxdebug->leave_sub();
1741 $main::lxdebug->enter_sub();
1743 my ($self, $myconfig, $form) = @_;
1745 # connect to database
1746 my $dbh = $form->get_standard_dbh;
1748 my $dateformat = $myconfig->{dateformat};
1749 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1751 my (@values, $duedate, $ref, $query);
1753 if ($form->{invdate}) {
1754 $duedate = "to_date(?, '$dateformat')";
1755 push @values, $form->{invdate};
1757 $duedate = "current_date";
1760 my $cid = conv_i($form->{customer_id});
1763 if ($form->{payment_id}) {
1764 $payment_id = "(pt.id = ?) OR";
1765 push @values, conv_i($form->{payment_id});
1771 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1772 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1773 c.street, c.zipcode, c.city, c.country,
1774 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1775 c.taxincluded_checked, c.direct_debit,
1776 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1777 b.discount AS tradediscount, b.description AS business
1779 LEFT JOIN business b ON (b.id = c.business_id)
1780 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1783 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1785 delete $ref->{salesman_id} if !$ref->{salesman_id};
1787 map { $form->{$_} = $ref->{$_} } keys %$ref;
1789 # remove any trailing whitespace
1790 $form->{curr} =~ s/\s*$//;
1792 # use customer currency if not empty
1793 $form->{currency} = $form->{curr} if $form->{curr};
1796 qq|SELECT sum(amount - paid) AS dunning_amount
1798 WHERE (paid < amount)
1799 AND (customer_id = ?)
1800 AND (dunning_config_id IS NOT NULL)|;
1801 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1802 map { $form->{$_} = $ref->{$_} } keys %$ref;
1805 qq|SELECT dnn.dunning_description AS max_dunning_level
1806 FROM dunning_config dnn
1807 WHERE id IN (SELECT dunning_config_id
1809 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1810 ORDER BY dunning_level DESC LIMIT 1|;
1811 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1812 map { $form->{$_} = $ref->{$_} } keys %$ref;
1814 $form->{creditremaining} = $form->{creditlimit};
1815 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1816 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1817 $form->{creditremaining} -= $value;
1821 (SELECT e.buy FROM exchangerate e
1822 WHERE e.curr = o.curr
1823 AND e.transdate = o.transdate)
1825 WHERE o.customer_id = ?
1826 AND o.quotation = '0'
1827 AND o.closed = '0'|;
1828 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1830 while (my ($amount, $exch) = $sth->fetchrow_array) {
1831 $exch = 1 unless $exch;
1832 $form->{creditremaining} -= $amount * $exch;
1836 # get shipto if we did not converted an order or invoice
1837 if (!$form->{shipto}) {
1838 map { delete $form->{$_} }
1839 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1840 shiptostreet shiptozipcode shiptocity shiptocountry
1841 shiptocontact shiptophone shiptofax shiptoemail);
1843 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1844 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1846 map { $form->{$_} = $ref->{$_} } keys %$ref;
1849 # setup last accounts used for this customer
1850 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1852 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1854 JOIN acc_trans ac ON (ac.chart_id = c.id)
1855 JOIN ar a ON (a.id = ac.trans_id)
1856 WHERE a.customer_id = ?
1857 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1858 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1859 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1862 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1863 if ($ref->{category} eq 'I') {
1865 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1867 if ($form->{initial_transdate}) {
1869 qq|SELECT tk.tax_id, t.rate
1871 LEFT JOIN tax t ON tk.tax_id = t.id
1872 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1873 ORDER BY tk.startdate DESC
1875 my ($tax_id, $rate) =
1876 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1877 $form->{initial_transdate});
1878 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1881 if ($ref->{category} eq 'A') {
1882 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1886 $form->{rowcount} = $i if ($i && !$form->{type});
1889 $main::lxdebug->leave_sub();
1893 $main::lxdebug->enter_sub();
1895 my ($self, $myconfig, $form) = @_;
1897 # connect to database
1898 my $dbh = $form->get_standard_dbh;
1900 my $i = $form->{rowcount};
1902 my $where = qq|NOT p.obsolete = '1'|;
1905 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1906 my ($table, $field) = split m/\./, $column;
1907 next if !$form->{"${field}_${i}"};
1908 $where .= qq| AND lower(${column}) ILIKE ?|;
1909 push @values, '%' . $form->{"${field}_${i}"} . '%';
1912 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1913 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1914 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1915 push @values, $form->{"partnumber_$i"};
1918 # Search for part ID overrides all other criteria.
1919 if ($form->{"id_${i}"}) {
1920 $where = qq|p.id = ?|;
1921 @values = ($form->{"id_${i}"});
1924 if ($form->{"description_$i"}) {
1925 $where .= qq| ORDER BY p.description|;
1927 $where .= qq| ORDER BY p.partnumber|;
1931 if ($form->{type} eq "invoice") {
1933 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1934 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1938 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1942 my $taxzone_id = $form->{taxzone_id} * 1;
1943 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1947 p.id, p.partnumber, p.description, p.sellprice,
1948 p.listprice, p.inventory_accno_id, p.lastcost,
1950 c1.accno AS inventory_accno,
1951 c1.new_chart_id AS inventory_new_chart,
1952 date($transdate) - c1.valid_from AS inventory_valid,
1954 c2.accno AS income_accno,
1955 c2.new_chart_id AS income_new_chart,
1956 date($transdate) - c2.valid_from AS income_valid,
1958 c3.accno AS expense_accno,
1959 c3.new_chart_id AS expense_new_chart,
1960 date($transdate) - c3.valid_from AS expense_valid,
1962 p.unit, p.assembly, p.bin, p.onhand,
1963 p.notes AS partnotes, p.notes AS longdescription,
1964 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1965 p.price_factor_id, p.weight,
1967 pfac.factor AS price_factor,
1972 LEFT JOIN chart c1 ON
1973 ((SELECT inventory_accno_id
1974 FROM buchungsgruppen
1975 WHERE id = p.buchungsgruppen_id) = c1.id)
1976 LEFT JOIN chart c2 ON
1977 ((SELECT income_accno_id_${taxzone_id}
1978 FROM buchungsgruppen
1979 WHERE id = p.buchungsgruppen_id) = c2.id)
1980 LEFT JOIN chart c3 ON
1981 ((SELECT expense_accno_id_${taxzone_id}
1982 FROM buchungsgruppen
1983 WHERE id = p.buchungsgruppen_id) = c3.id)
1984 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1985 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1987 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1989 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1991 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1992 [ qq|SELECT tr.translation, tr.longdescription
1994 WHERE tr.language_id IN
1997 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2000 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2002 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2004 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2005 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2006 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2007 if (!$ref->{inventory_accno_id}) {
2008 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2010 delete($ref->{inventory_accno_id});
2012 foreach my $type (qw(inventory income expense)) {
2013 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2015 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2018 ($ref->{"${type}_accno"},
2019 $ref->{"${type}_new_chart"},
2020 $ref->{"${type}_valid"})
2021 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2025 if ($form->{payment_id} eq "") {
2026 $form->{payment_id} = $form->{part_payment_id};
2029 # get tax rates and description
2030 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2032 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2034 LEFT JOIN chart c ON (c.id = t.chart_id)
2038 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2040 ORDER BY startdate DESC
2043 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2044 my $stw = $dbh->prepare($query);
2045 $stw->execute(@values) || $form->dberror($query);
2047 $ref->{taxaccounts} = "";
2049 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2051 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2055 $ref->{taxaccounts} .= "$ptr->{accno} ";
2057 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2058 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2059 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2060 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2061 $form->{taxaccounts} .= "$ptr->{accno} ";
2067 chop $ref->{taxaccounts};
2069 if ($form->{language_id}) {
2070 for my $spec (@translation_queries) {
2071 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2072 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2073 next unless $translation;
2074 $ref->{description} = $translation;
2075 $ref->{longdescription} = $longdescription;
2080 $ref->{onhand} *= 1;
2082 push @{ $form->{item_list} }, $ref;
2085 $_->[1]->finish for @translation_queries;
2087 foreach my $item (@{ $form->{item_list} }) {
2088 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2089 trans_id => $item->{id},
2093 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2096 $main::lxdebug->leave_sub();
2099 ##########################
2100 # get pricegroups from database
2101 # build up selected pricegroup
2102 # if an exchange rate - change price
2105 sub get_pricegroups_for_parts {
2107 $main::lxdebug->enter_sub();
2109 my ($self, $myconfig, $form) = @_;
2111 my $dbh = $form->get_standard_dbh;
2113 $form->{"PRICES"} = {};
2117 my $all_units = AM->retrieve_units($myconfig, $form);
2118 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2119 $form->{"PRICES"}{$i} = [];
2121 $id = $form->{"id_$i"};
2123 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2124 $id = $form->{"new_id_$i"};
2127 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2129 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2131 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2132 my $sellprice = $form->{"sellprice_$i"};
2133 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2134 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2135 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2137 my $price_new = $form->{"price_new_$i"};
2138 my $price_old = $form->{"price_old_$i"};
2140 if (!$form->{"unit_old_$i"}) {
2141 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2142 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2143 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2144 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2147 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2148 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2149 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2151 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2152 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2153 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2154 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2155 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2156 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2157 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2162 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2163 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2164 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2165 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2166 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2170 if (!$form->{"basefactor_$i"}) {
2171 $form->{"basefactor_$i"} = 1;
2177 sellprice AS default_sellprice,
2180 'selected' AS selected
2186 parts.sellprice AS default_sellprice,
2187 pricegroup.pricegroup,
2191 LEFT JOIN parts ON parts.id = parts_id
2192 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2194 ORDER BY pricegroup|;
2195 my @values = (conv_i($id), conv_i($id));
2196 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2198 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2200 $pkr->{selected} = '';
2202 # if there is an exchange rate change price
2203 if (($form->{exchangerate} * 1) != 0) {
2204 $pkr->{price} /= $form->{exchangerate};
2207 $pkr->{price} *= $form->{"basefactor_$i"};
2208 $pkr->{price} *= $basefactor;
2209 $pkr->{price_ufmt} = $pkr->{price};
2210 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2212 if (!defined $selectedpricegroup_id) {
2213 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2214 # Case A: open old invoice, no pricegroup selected
2215 # Case B: add new article to invoice, no pricegroup selected
2217 # to distinguish case A and B the variable pricegroup_id_$i is used
2218 # for new articles this variable isn't defined, for loaded articles it is
2219 # sellprice can't be used, as it already has 0,00 set
2221 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2223 $pkr->{selected} = ' selected';
2224 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2225 and not defined $form->{"pricegroup_id_$i"}
2226 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2227 # for the case where pricegroup prices haven't been set
2229 # Case B: use default pricegroup of customer
2231 $pkr->{selected} = ' selected'; # unless $form->{selected};
2232 # no customer pricesgroup set
2233 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2235 $pkr->{price} = $form->{"sellprice_$i"};
2239 # this sub should not set anything and only return. --sschoeling, 20090506
2240 # is this correct? put in again... -- grichardson 20110119
2241 $form->{"sellprice_$i"} = $pkr->{price};
2244 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2245 $pkr->{price} = $form->{"sellprice_$i"};
2246 $pkr->{selected} = ' selected';
2250 # existing article: pricegroup or price changed
2251 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2252 if ($selectedpricegroup_id ne $pricegroup_old) {
2253 # pricegroup has changed
2254 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2255 $pkr->{selected} = ' selected';
2257 } elsif ( ($form->parse_amount($myconfig, $price_new)
2258 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2259 and ($price_new ne 0) and defined $price_new) {
2260 # sellprice has changed
2261 # when loading existing invoices $price_new is NULL
2262 if ($pkr->{pricegroup_id} == 0) {
2263 $pkr->{price} = $form->{"sellprice_$i"};
2264 $pkr->{selected} = ' selected';
2266 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2267 # neither sellprice nor pricegroup changed
2268 $pkr->{selected} = ' selected';
2269 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2270 # $pkr->{price} = $form->{"sellprice_$i"};
2272 $pkr->{price} = $form->{"sellprice_$i"};
2276 push @{ $form->{PRICES}{$i} }, $pkr;
2279 $form->{"basefactor_$i"} *= $basefactor;
2286 $main::lxdebug->leave_sub();
2290 $main::lxdebug->enter_sub();
2292 my ($self, $myconfig, $form, $table) = @_;
2294 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2296 # make sure there's no funny stuff in $table
2297 # ToDO: die when this happens and throw an error
2298 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2300 my $dbh = $form->get_standard_dbh;
2302 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2303 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2305 $main::lxdebug->leave_sub();
2311 $main::lxdebug->enter_sub();
2313 my ($self, $myconfig, $form, $table, $id) = @_;
2315 $main::lxdebug->leave_sub() and return 0 unless ($id);
2317 # make sure there's no funny stuff in $table
2318 # ToDO: die when this happens and throw an error
2319 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2321 my $dbh = $form->get_standard_dbh;
2323 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2324 my ($result) = selectrow_query($form, $dbh, $query, $id);
2326 $main::lxdebug->leave_sub();
2331 sub get_standard_accno_current_assets {
2332 $main::lxdebug->enter_sub();
2334 my ($self, $myconfig, $form) = @_;
2336 my $dbh = $form->get_standard_dbh;
2338 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2339 my ($result) = selectrow_query($form, $dbh, $query);
2341 $main::lxdebug->leave_sub();