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;
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 WHERE (ct.id = ?) $where
478 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
480 # remove id and taxincluded before copy back
481 delete @$ref{qw(id taxincluded)};
483 @wanted_vars = grep({ $_ } @wanted_vars);
484 if (scalar(@wanted_vars) > 0) {
486 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
487 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
490 map { $form->{$_} = $ref->{$_} } keys %$ref;
492 # remove any trailing whitespace
493 $form->{currency} =~ s/\s*$// if ($form->{currency});
495 if ($form->{delivery_customer_id}) {
497 qq|SELECT *, notes as customernotes
501 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
503 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
506 if ($form->{delivery_vendor_id}) {
508 qq|SELECT *, notes as customernotes
512 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
514 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
517 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
519 'trans_id' => $form->{customer_id});
520 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
522 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
523 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
524 'language_id' => $language_id,
525 'allow_fallback' => 1);
528 $main::lxdebug->leave_sub();
532 $main::lxdebug->enter_sub();
534 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
536 # connect to database, turn off autocommit
537 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
539 my ($query, $sth, $null, $project_id, @values);
540 my $exchangerate = 0;
542 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
545 if (!$form->{employee_id}) {
546 $form->get_employee($dbh);
549 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
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) VALUES (?, ?)|;
571 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
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 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
585 my $defaultcurrency = (split m/:/, $currencies)[0];
587 if ($form->{currency} eq $defaultcurrency) {
588 $form->{exchangerate} = 1;
590 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
593 $form->{exchangerate} =
596 : $form->parse_amount($myconfig, $form->{exchangerate});
598 $form->{expense_inventory} = "";
602 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
603 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
606 $form->{amount} = {};
607 $form->{amount_cogs} = {};
609 foreach my $i (1 .. $form->{rowcount}) {
610 if ($form->{type} eq "credit_note") {
611 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
612 $form->{shipped} = 1;
614 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
619 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
620 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
621 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
623 if ($form->{storno}) {
624 $form->{"qty_$i"} *= -1;
627 if ($form->{"id_$i"}) {
630 if (defined($baseunits{$form->{"id_$i"}})) {
631 $item_unit = $baseunits{$form->{"id_$i"}};
634 $query = qq|SELECT unit FROM parts WHERE id = ?|;
635 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
636 $baseunits{$form->{"id_$i"}} = $item_unit;
639 if (defined($all_units->{$item_unit}->{factor})
640 && ($all_units->{$item_unit}->{factor} ne '')
641 && ($all_units->{$item_unit}->{factor} != 0)) {
642 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
646 $baseqty = $form->{"qty_$i"} * $basefactor;
648 my ($allocated, $taxrate) = (0, 0);
652 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
654 # keep entered selling price
656 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
658 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
660 my $decimalplaces = ($dec > 2) ? $dec : 2;
662 # undo discount formatting
663 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
666 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
668 # round linetotal to 2 decimal places
669 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
670 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
672 if ($form->{taxincluded}) {
673 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
674 $form->{"sellprice_$i"} =
675 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
677 $taxamount = $linetotal * $taxrate;
680 $netamount += $linetotal;
682 if ($taxamount != 0) {
684 $form->{amount}{ $form->{id} }{$_} +=
685 $taxamount * $form->{"${_}_rate"} / $taxrate
686 } split(/ /, $form->{"taxaccounts_$i"});
689 # add amount to income, $form->{amount}{trans_id}{accno}
690 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
692 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
693 $linetotal = $form->round_amount($linetotal, 2);
695 # this is the difference from the inventory
696 $invoicediff += ($amount - $linetotal);
698 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
701 $lastincomeaccno = $form->{"income_accno_$i"};
703 # adjust and round sellprice
704 $form->{"sellprice_$i"} =
705 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
708 next if $payments_only;
710 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
712 if ($form->{"assembly_$i"}) {
713 # record assembly item as allocated
714 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
717 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
721 # Get pricegroup_id and save it. Unfortunately the interface
722 # also uses ID "0" for signalling that none is selected, but "0"
723 # must not be stored in the database. Therefore we cannot simply
725 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
727 $pricegroup_id = undef if !$pricegroup_id;
729 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
731 # save detail record in invoice table
733 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
734 sellprice, fxsellprice, discount, allocated, assemblyitem,
735 unit, deliverydate, project_id, serialnumber, pricegroup_id,
736 ordnumber, transdate, cusordnumber, base_qty, subtotal,
737 marge_percent, marge_total, lastcost,
738 price_factor_id, price_factor, marge_price_factor)
739 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
740 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
742 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
743 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
744 $form->{"sellprice_$i"}, $fxsellprice,
745 $form->{"discount_$i"}, $allocated, 'f',
746 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
747 $form->{"serialnumber_$i"}, $pricegroup_id,
748 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
749 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
750 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
751 $form->{"lastcost_$i"},
752 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
753 conv_i($form->{"marge_price_factor_$i"}));
754 do_query($form, $dbh, $query, @values);
756 CVar->save_custom_variables(module => 'IC',
757 sub_module => 'invoice',
758 trans_id => $invoice_id,
759 configs => $ic_cvar_configs,
761 name_prefix => 'ic_',
762 name_postfix => "_$i",
767 # total payments, don't move we need it here
768 for my $i (1 .. $form->{paidaccounts}) {
769 if ($form->{type} eq "credit_note") {
770 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
772 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
774 $form->{paid} += $form->{"paid_$i"};
775 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
778 my ($tax, $diff) = (0, 0);
780 $netamount = $form->round_amount($netamount, 2);
782 # figure out rounding errors for total amount vs netamount + taxes
783 if ($form->{taxincluded}) {
785 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
786 $diff += $amount - $netamount * $form->{exchangerate};
787 $netamount = $amount;
789 foreach my $item (split(/ /, $form->{taxaccounts})) {
790 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
791 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
792 $tax += $form->{amount}{ $form->{id} }{$item};
793 $netamount -= $form->{amount}{ $form->{id} }{$item};
796 $invoicediff += $diff;
797 ######## this only applies to tax included
798 if ($lastincomeaccno) {
799 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
803 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
804 $diff = $amount - $netamount * $form->{exchangerate};
805 $netamount = $amount;
806 foreach my $item (split(/ /, $form->{taxaccounts})) {
807 $form->{amount}{ $form->{id} }{$item} =
808 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
811 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
814 $amount - $form->{amount}{ $form->{id} }{$item} *
815 $form->{exchangerate};
816 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
817 $tax += $form->{amount}{ $form->{id} }{$item};
821 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
823 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
826 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
828 # update exchangerate
829 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
830 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
831 $form->{exchangerate}, 0);
834 $project_id = conv_i($form->{"globalproject_id"});
835 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
836 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
838 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
839 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
840 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
842 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
844 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
846 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
847 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
848 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
849 do_query($form, $dbh, $query, @values);
850 $form->{amount_cogs}{$trans_id}{$accno} = 0;
854 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
855 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
857 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
859 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
860 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
861 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
862 do_query($form, $dbh, $query, @values);
867 foreach my $trans_id (keys %{ $form->{amount} }) {
868 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
869 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
871 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
873 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
875 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
876 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
879 WHERE chart_id= (SELECT id
883 ORDER BY startdate DESC LIMIT 1),
886 WHERE chart_id= (SELECT id
890 ORDER BY startdate DESC LIMIT 1),
892 (SELECT link FROM chart WHERE accno = ?))|;
893 @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);
894 do_query($form, $dbh, $query, @values);
895 $form->{amount}{$trans_id}{$accno} = 0;
899 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
900 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
902 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
904 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
905 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
908 WHERE chart_id= (SELECT id
912 ORDER BY startdate DESC LIMIT 1),
915 WHERE chart_id= (SELECT id
919 ORDER BY startdate DESC LIMIT 1),
921 (SELECT link FROM chart WHERE accno = ?))|;
922 @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);
923 do_query($form, $dbh, $query, @values);
928 # deduct payment differences from diff
929 for my $i (1 .. $form->{paidaccounts}) {
930 if ($form->{"paid_$i"} != 0) {
932 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
933 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
937 # record payments and offsetting AR
938 if (!$form->{storno}) {
939 for my $i (1 .. $form->{paidaccounts}) {
941 if ($form->{"acc_trans_id_$i"}
943 && (SL::DB::Default->get->payments_changeable == 0)) {
947 next if ($form->{"paid_$i"} == 0);
949 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
950 $form->{"datepaid_$i"} = $form->{invdate}
951 unless ($form->{"datepaid_$i"});
952 $form->{datepaid} = $form->{"datepaid_$i"};
956 if ($form->{currency} eq $defaultcurrency) {
957 $form->{"exchangerate_$i"} = 1;
959 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
960 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
964 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
966 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
968 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
969 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
972 WHERE chart_id= (SELECT id
976 ORDER BY startdate DESC LIMIT 1),
979 WHERE chart_id= (SELECT id
983 ORDER BY startdate DESC LIMIT 1),
985 (SELECT link FROM chart WHERE accno = ?))|;
986 @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});
987 do_query($form, $dbh, $query, @values);
991 $form->{"paid_$i"} *= -1;
992 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
995 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
996 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
999 WHERE chart_id= (SELECT id
1003 ORDER BY startdate DESC LIMIT 1),
1006 WHERE chart_id= (SELECT id
1010 ORDER BY startdate DESC LIMIT 1),
1012 (SELECT link FROM chart WHERE accno = ?))|;
1013 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1014 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1015 do_query($form, $dbh, $query, @values);
1017 # exchangerate difference
1018 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1019 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1023 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1024 $form->{"exchangerate_$i"};
1026 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1028 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1033 # update exchange rate
1034 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1035 $form->update_exchangerate($dbh, $form->{currency},
1036 $form->{"datepaid_$i"},
1037 $form->{"exchangerate_$i"}, 0);
1041 } else { # if (!$form->{storno})
1042 $form->{marge_total} *= -1;
1045 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1047 # record exchange rate differences and gains/losses
1048 foreach my $accno (keys %{ $form->{fx} }) {
1049 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1050 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1051 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1054 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1055 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1058 WHERE chart_id= (SELECT id
1062 ORDER BY startdate DESC LIMIT 1),
1065 WHERE chart_id= (SELECT id
1069 ORDER BY startdate DESC LIMIT 1),
1071 (SELECT link FROM chart WHERE accno = ?))|;
1072 @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);
1073 do_query($form, $dbh, $query, @values);
1078 if ($payments_only) {
1079 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1080 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1082 $dbh->commit if !$provided_dbh;
1084 $main::lxdebug->leave_sub();
1088 $amount = $netamount + $tax;
1091 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1093 $query = qq|UPDATE ar set
1094 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1095 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1096 amount = ?, netamount = ?, paid = ?,
1097 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1098 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1099 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1100 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1101 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1102 cp_id = ?, marge_total = ?, marge_percent = ?,
1103 globalproject_id = ?, delivery_customer_id = ?,
1104 transaction_description = ?, delivery_vendor_id = ?,
1105 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1107 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1108 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1109 $amount, $netamount, $form->{"paid"},
1110 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1111 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1112 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1113 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1114 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1115 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1116 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1117 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1118 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1119 conv_i($form->{"id"}));
1120 do_query($form, $dbh, $query, @values);
1123 if ($form->{storno}) {
1126 paid = paid + amount,
1128 intnotes = ? || intnotes
1130 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1131 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1135 $form->{name} = $form->{customer};
1136 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1138 if (!$form->{shipto_id}) {
1139 $form->add_shipto($dbh, $form->{id}, "AR");
1142 # save printed, emailed and queued
1143 $form->save_status($dbh);
1145 Common::webdav_folder($form);
1147 # Link this record to the records it was created from.
1148 RecordLinks->create_links('dbh' => $dbh,
1150 'from_table' => 'oe',
1151 'from_ids' => $form->{convert_from_oe_ids},
1153 'to_id' => $form->{id},
1155 delete $form->{convert_from_oe_ids};
1157 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1159 if (scalar @convert_from_do_ids) {
1160 DO->close_orders('dbh' => $dbh,
1161 'ids' => \@convert_from_do_ids);
1163 RecordLinks->create_links('dbh' => $dbh,
1165 'from_table' => 'delivery_orders',
1166 'from_ids' => \@convert_from_do_ids,
1168 'to_id' => $form->{id},
1171 delete $form->{convert_from_do_ids};
1173 ARAP->close_orders_if_billed('dbh' => $dbh,
1174 'arap_id' => $form->{id},
1177 # safety check datev export
1178 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1179 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1180 $transdate ||= DateTime->today;
1182 my $datev = SL::DATEV->new(
1183 exporttype => DATEV_ET_BUCHUNGEN,
1184 format => DATEV_FORMAT_KNE,
1188 trans_id => $form->{id},
1193 if ($datev->errors) {
1195 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1200 $dbh->commit if !$provided_dbh;
1202 $main::lxdebug->leave_sub();
1207 sub _delete_payments {
1208 $main::lxdebug->enter_sub();
1210 my ($self, $form, $dbh) = @_;
1212 my @delete_acc_trans_ids;
1214 # Delete old payment entries from acc_trans.
1216 qq|SELECT acc_trans_id
1218 WHERE (trans_id = ?) AND fx_transaction
1222 SELECT at.acc_trans_id
1224 LEFT JOIN chart c ON (at.chart_id = c.id)
1225 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1226 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1229 qq|SELECT at.acc_trans_id
1231 LEFT JOIN chart c ON (at.chart_id = c.id)
1232 WHERE (trans_id = ?)
1233 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1234 ORDER BY at.acc_trans_id
1236 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1238 if (@delete_acc_trans_ids) {
1239 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1240 do_query($form, $dbh, $query);
1243 $main::lxdebug->leave_sub();
1247 $main::lxdebug->enter_sub();
1249 my ($self, $myconfig, $form, $locale) = @_;
1251 # connect to database, turn off autocommit
1252 my $dbh = $form->get_standard_dbh;
1255 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1257 $old_form = save_form();
1259 # Delete all entries in acc_trans from prior payments.
1260 if (SL::DB::Default->get->payments_changeable != 0) {
1261 $self->_delete_payments($form, $dbh);
1264 # Save the new payments the user made before cleaning up $form.
1265 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 };
1267 # Clean up $form so that old content won't tamper the results.
1268 %keep_vars = map { $_, 1 } qw(login password id);
1269 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1271 # Retrieve the invoice from the database.
1272 $self->retrieve_invoice($myconfig, $form);
1274 # Set up the content of $form in the way that IS::post_invoice() expects.
1275 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1277 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1278 $item = $form->{invoice_details}->[$row - 1];
1280 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1282 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1285 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1287 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1289 # Restore the payment options from the user input.
1290 map { $form->{$_} = $payments{$_} } keys %payments;
1292 # Get the AR accno (which is normally done by Form::create_links()).
1296 LEFT JOIN chart c ON (at.chart_id = c.id)
1297 WHERE (trans_id = ?)
1298 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1299 ORDER BY at.acc_trans_id
1302 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1304 # Post the new payments.
1305 $self->post_invoice($myconfig, $form, $dbh, 1);
1307 restore_form($old_form);
1309 my $rc = $dbh->commit();
1311 $main::lxdebug->leave_sub();
1316 sub process_assembly {
1317 $main::lxdebug->enter_sub();
1319 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1322 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1323 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1325 JOIN parts p ON (a.parts_id = p.id)
1327 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1329 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1333 $ref->{inventory_accno_id} *= 1;
1334 $ref->{expense_accno_id} *= 1;
1336 # multiply by number of assemblies
1337 $ref->{qty} *= $totalqty;
1339 if ($ref->{assembly}) {
1340 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1343 if ($ref->{inventory_accno_id}) {
1344 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1348 # save detail record for individual assembly item in invoice table
1350 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1351 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1352 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1353 do_query($form, $dbh, $query, @values);
1359 $main::lxdebug->leave_sub();
1363 $main::lxdebug->enter_sub();
1365 # adjust allocated in table invoice according to FIFO princicple
1366 # for a certain part with part_id $id
1368 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1372 $form->{taxzone_id} *=1;
1373 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1374 my $taxzone_id = $form->{"taxzone_id"} * 1;
1376 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1377 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1378 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1379 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1380 FROM invoice i, parts p
1381 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1382 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1383 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1384 WHERE (i.parts_id = p.id)
1385 AND (i.parts_id = ?)
1386 AND ((i.base_qty + i.allocated) < 0)
1388 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1393 # all invoice entries of an example part:
1395 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1396 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1397 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1398 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1399 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1400 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1401 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1403 # 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
1404 # and all parts have been allocated
1406 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1407 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1409 # in this example there are still 4 unsold articles
1412 # search all invoice entries for the part in question, adjusting "allocated"
1413 # until the total number of sold parts has been reached
1415 # ORDER BY trans_id ensures FIFO
1418 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1419 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1423 # update allocated in invoice
1424 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1426 # total expenses and inventory
1427 # sellprice is the cost of the item
1428 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1430 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1431 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1432 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1434 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1435 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1436 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1438 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1439 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1445 last if (($totalqty -= $qty) <= 0);
1450 $main::lxdebug->leave_sub();
1455 sub reverse_invoice {
1456 $main::lxdebug->enter_sub();
1458 my ($dbh, $form) = @_;
1460 # reverse inventory items
1462 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1464 JOIN parts p ON (i.parts_id = p.id)
1465 WHERE i.trans_id = ?|;
1466 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1468 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1470 if ($ref->{inventory_accno_id}) {
1471 # de-allocated purchases
1473 qq|SELECT i.id, i.trans_id, i.allocated
1475 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1476 ORDER BY i.trans_id DESC|;
1477 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1479 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1480 my $qty = $ref->{qty};
1481 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1482 $qty = $inhref->{allocated};
1486 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1488 last if (($ref->{qty} -= $qty) <= 0);
1497 my @values = (conv_i($form->{id}));
1498 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1499 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1500 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1502 $main::lxdebug->leave_sub();
1505 sub delete_invoice {
1506 $main::lxdebug->enter_sub();
1508 my ($self, $myconfig, $form) = @_;
1510 # connect to database
1511 my $dbh = $form->get_standard_dbh;
1514 &reverse_invoice($dbh, $form);
1516 my @values = (conv_i($form->{id}));
1518 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1519 # zurückgesetzt werden. Vgl:
1520 # id | storno | storno_id | paid | amount
1521 #----+--------+-----------+---------+-----------
1522 # 18 | f | | 0.00000 | 119.00000
1524 # 18 | t | | 119.00000 | 119.00000
1526 if($form->{storno}){
1527 # storno_id auslesen und korrigieren
1528 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1529 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1532 # delete spool files
1533 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1536 qq|DELETE FROM status WHERE trans_id = ?|,
1537 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1538 qq|DELETE FROM ar WHERE id = ?|,
1541 map { do_query($form, $dbh, $_, @values) } @queries;
1543 my $rc = $dbh->commit;
1546 my $spool = $::lx_office_conf{paths}->{spool};
1547 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1550 $main::lxdebug->leave_sub();
1555 sub retrieve_invoice {
1556 $main::lxdebug->enter_sub();
1558 my ($self, $myconfig, $form) = @_;
1560 # connect to database
1561 my $dbh = $form->get_standard_dbh;
1563 my ($sth, $ref, $query);
1565 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1569 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1570 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1571 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1572 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1573 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1574 d.curr AS currencies
1578 $ref = selectfirst_hashref_query($form, $dbh, $query);
1579 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1582 my $id = conv_i($form->{id});
1585 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1589 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1590 a.orddate, a.quodate, a.globalproject_id,
1591 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1592 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1593 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1594 a.employee_id, a.salesman_id, a.payment_id,
1595 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1596 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1597 a.marge_total, a.marge_percent, a.direct_debit,
1600 LEFT JOIN employee e ON (e.id = a.employee_id)
1602 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1603 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1605 # remove any trailing whitespace
1606 $form->{currency} =~ s/\s*$//;
1608 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1611 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1612 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1614 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1616 foreach my $vc (qw(customer vendor)) {
1617 next if !$form->{"delivery_${vc}_id"};
1618 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1621 # get printed, emailed
1622 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1623 $sth = prepare_execute_query($form, $dbh, $query, $id);
1625 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1626 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1627 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1628 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1631 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1633 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1634 : $form->{invdate} ? $dbh->quote($form->{invdate})
1638 my $taxzone_id = $form->{taxzone_id} *= 1;
1639 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1641 # retrieve individual items
1644 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1645 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1646 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1649 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1650 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1651 i.price_factor_id, i.price_factor, i.marge_price_factor,
1652 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1653 pr.projectnumber, pg.partsgroup, prg.pricegroup
1656 LEFT JOIN parts p ON (i.parts_id = p.id)
1657 LEFT JOIN project pr ON (i.project_id = pr.id)
1658 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1659 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1661 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1662 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1663 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1665 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1667 $sth = prepare_execute_query($form, $dbh, $query, $id);
1669 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1670 # Retrieve custom variables.
1671 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1673 sub_module => 'invoice',
1674 trans_id => $ref->{invoice_id},
1676 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1677 delete $ref->{invoice_id};
1679 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1680 delete($ref->{"part_inventory_accno_id"});
1682 foreach my $type (qw(inventory income expense)) {
1683 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1684 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1685 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1689 # get tax rates and description
1690 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1692 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1693 LEFT JOIN chart c ON (c.id = t.chart_id)
1695 (SELECT tk.tax_id FROM taxkeys tk
1696 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1697 AND startdate <= date($transdate)
1698 ORDER BY startdate DESC LIMIT 1)
1700 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1701 $ref->{taxaccounts} = "";
1703 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1705 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1709 $ref->{taxaccounts} .= "$ptr->{accno} ";
1711 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1712 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1713 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1714 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1715 $form->{taxaccounts} .= "$ptr->{accno} ";
1720 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1722 chop $ref->{taxaccounts};
1723 push @{ $form->{invoice_details} }, $ref;
1728 Common::webdav_folder($form);
1731 my $rc = $dbh->commit;
1733 $main::lxdebug->leave_sub();
1739 $main::lxdebug->enter_sub();
1741 my ($self, $myconfig, $form) = @_;
1743 # connect to database
1744 my $dbh = $form->get_standard_dbh;
1746 my $dateformat = $myconfig->{dateformat};
1747 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1749 my (@values, $duedate, $ref, $query);
1751 if ($form->{invdate}) {
1752 $duedate = "to_date(?, '$dateformat')";
1753 push @values, $form->{invdate};
1755 $duedate = "current_date";
1758 my $cid = conv_i($form->{customer_id});
1761 if ($form->{payment_id}) {
1762 $payment_id = "(pt.id = ?) OR";
1763 push @values, conv_i($form->{payment_id});
1769 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1770 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1771 c.street, c.zipcode, c.city, c.country,
1772 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1773 c.taxincluded_checked, c.direct_debit,
1774 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1775 b.discount AS tradediscount, b.description AS business
1777 LEFT JOIN business b ON (b.id = c.business_id)
1778 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1781 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1783 delete $ref->{salesman_id} if !$ref->{salesman_id};
1785 map { $form->{$_} = $ref->{$_} } keys %$ref;
1787 # remove any trailing whitespace
1788 $form->{curr} =~ s/\s*$//;
1790 # use customer currency if not empty
1791 $form->{currency} = $form->{curr} if $form->{curr};
1794 qq|SELECT sum(amount - paid) AS dunning_amount
1796 WHERE (paid < amount)
1797 AND (customer_id = ?)
1798 AND (dunning_config_id IS NOT NULL)|;
1799 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1800 map { $form->{$_} = $ref->{$_} } keys %$ref;
1803 qq|SELECT dnn.dunning_description AS max_dunning_level
1804 FROM dunning_config dnn
1805 WHERE id IN (SELECT dunning_config_id
1807 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1808 ORDER BY dunning_level DESC LIMIT 1|;
1809 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1810 map { $form->{$_} = $ref->{$_} } keys %$ref;
1812 $form->{creditremaining} = $form->{creditlimit};
1813 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1814 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1815 $form->{creditremaining} -= $value;
1819 (SELECT e.buy FROM exchangerate e
1820 WHERE e.curr = o.curr
1821 AND e.transdate = o.transdate)
1823 WHERE o.customer_id = ?
1824 AND o.quotation = '0'
1825 AND o.closed = '0'|;
1826 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1828 while (my ($amount, $exch) = $sth->fetchrow_array) {
1829 $exch = 1 unless $exch;
1830 $form->{creditremaining} -= $amount * $exch;
1834 # get shipto if we did not converted an order or invoice
1835 if (!$form->{shipto}) {
1836 map { delete $form->{$_} }
1837 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1838 shiptostreet shiptozipcode shiptocity shiptocountry
1839 shiptocontact shiptophone shiptofax shiptoemail);
1841 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1842 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1844 map { $form->{$_} = $ref->{$_} } keys %$ref;
1847 # setup last accounts used for this customer
1848 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1850 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1852 JOIN acc_trans ac ON (ac.chart_id = c.id)
1853 JOIN ar a ON (a.id = ac.trans_id)
1854 WHERE a.customer_id = ?
1855 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1856 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1857 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1860 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1861 if ($ref->{category} eq 'I') {
1863 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1865 if ($form->{initial_transdate}) {
1867 qq|SELECT tk.tax_id, t.rate
1869 LEFT JOIN tax t ON tk.tax_id = t.id
1870 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1871 ORDER BY tk.startdate DESC
1873 my ($tax_id, $rate) =
1874 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1875 $form->{initial_transdate});
1876 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1879 if ($ref->{category} eq 'A') {
1880 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1884 $form->{rowcount} = $i if ($i && !$form->{type});
1887 $main::lxdebug->leave_sub();
1891 $main::lxdebug->enter_sub();
1893 my ($self, $myconfig, $form) = @_;
1895 # connect to database
1896 my $dbh = $form->get_standard_dbh;
1898 my $i = $form->{rowcount};
1900 my $where = qq|NOT p.obsolete = '1'|;
1903 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1904 my ($table, $field) = split m/\./, $column;
1905 next if !$form->{"${field}_${i}"};
1906 $where .= qq| AND lower(${column}) ILIKE ?|;
1907 push @values, '%' . $form->{"${field}_${i}"} . '%';
1910 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1911 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1912 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1913 push @values, $form->{"partnumber_$i"};
1916 # Search for part ID overrides all other criteria.
1917 if ($form->{"id_${i}"}) {
1918 $where = qq|p.id = ?|;
1919 @values = ($form->{"id_${i}"});
1922 if ($form->{"description_$i"}) {
1923 $where .= qq| ORDER BY p.description|;
1925 $where .= qq| ORDER BY p.partnumber|;
1929 if ($form->{type} eq "invoice") {
1931 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1932 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1936 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1940 my $taxzone_id = $form->{taxzone_id} * 1;
1941 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1945 p.id, p.partnumber, p.description, p.sellprice,
1946 p.listprice, p.inventory_accno_id, p.lastcost,
1948 c1.accno AS inventory_accno,
1949 c1.new_chart_id AS inventory_new_chart,
1950 date($transdate) - c1.valid_from AS inventory_valid,
1952 c2.accno AS income_accno,
1953 c2.new_chart_id AS income_new_chart,
1954 date($transdate) - c2.valid_from AS income_valid,
1956 c3.accno AS expense_accno,
1957 c3.new_chart_id AS expense_new_chart,
1958 date($transdate) - c3.valid_from AS expense_valid,
1960 p.unit, p.assembly, p.bin, p.onhand,
1961 p.notes AS partnotes, p.notes AS longdescription,
1962 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1963 p.price_factor_id, p.weight,
1965 pfac.factor AS price_factor,
1970 LEFT JOIN chart c1 ON
1971 ((SELECT inventory_accno_id
1972 FROM buchungsgruppen
1973 WHERE id = p.buchungsgruppen_id) = c1.id)
1974 LEFT JOIN chart c2 ON
1975 ((SELECT income_accno_id_${taxzone_id}
1976 FROM buchungsgruppen
1977 WHERE id = p.buchungsgruppen_id) = c2.id)
1978 LEFT JOIN chart c3 ON
1979 ((SELECT expense_accno_id_${taxzone_id}
1980 FROM buchungsgruppen
1981 WHERE id = p.buchungsgruppen_id) = c3.id)
1982 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1983 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1985 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1987 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1989 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1990 [ qq|SELECT tr.translation, tr.longdescription
1992 WHERE tr.language_id IN
1995 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1998 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2000 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2002 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2003 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2004 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2005 if (!$ref->{inventory_accno_id}) {
2006 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2008 delete($ref->{inventory_accno_id});
2010 foreach my $type (qw(inventory income expense)) {
2011 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2013 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2016 ($ref->{"${type}_accno"},
2017 $ref->{"${type}_new_chart"},
2018 $ref->{"${type}_valid"})
2019 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2023 if ($form->{payment_id} eq "") {
2024 $form->{payment_id} = $form->{part_payment_id};
2027 # get tax rates and description
2028 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2030 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2032 LEFT JOIN chart c ON (c.id = t.chart_id)
2036 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2038 ORDER BY startdate DESC
2041 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2042 my $stw = $dbh->prepare($query);
2043 $stw->execute(@values) || $form->dberror($query);
2045 $ref->{taxaccounts} = "";
2047 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2049 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2053 $ref->{taxaccounts} .= "$ptr->{accno} ";
2055 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2056 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2057 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2058 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2059 $form->{taxaccounts} .= "$ptr->{accno} ";
2065 chop $ref->{taxaccounts};
2067 if ($form->{language_id}) {
2068 for my $spec (@translation_queries) {
2069 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2070 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2071 next unless $translation;
2072 $ref->{description} = $translation;
2073 $ref->{longdescription} = $longdescription;
2078 $ref->{onhand} *= 1;
2080 push @{ $form->{item_list} }, $ref;
2083 $_->[1]->finish for @translation_queries;
2085 foreach my $item (@{ $form->{item_list} }) {
2086 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2087 trans_id => $item->{id},
2091 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2094 $main::lxdebug->leave_sub();
2097 ##########################
2098 # get pricegroups from database
2099 # build up selected pricegroup
2100 # if an exchange rate - change price
2103 sub get_pricegroups_for_parts {
2105 $main::lxdebug->enter_sub();
2107 my ($self, $myconfig, $form) = @_;
2109 my $dbh = $form->get_standard_dbh;
2111 $form->{"PRICES"} = {};
2115 my $all_units = AM->retrieve_units($myconfig, $form);
2116 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2117 $form->{"PRICES"}{$i} = [];
2119 $id = $form->{"id_$i"};
2121 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2122 $id = $form->{"new_id_$i"};
2125 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2127 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2129 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2130 my $sellprice = $form->{"sellprice_$i"};
2131 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2132 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2133 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2135 my $price_new = $form->{"price_new_$i"};
2136 my $price_old = $form->{"price_old_$i"};
2138 if (!$form->{"unit_old_$i"}) {
2139 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2140 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2141 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2142 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2145 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2146 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2147 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2149 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2150 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2151 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2152 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2153 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2154 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2155 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2160 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2161 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2162 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2163 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2164 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2168 if (!$form->{"basefactor_$i"}) {
2169 $form->{"basefactor_$i"} = 1;
2175 sellprice AS default_sellprice,
2178 'selected' AS selected
2184 parts.sellprice AS default_sellprice,
2185 pricegroup.pricegroup,
2189 LEFT JOIN parts ON parts.id = parts_id
2190 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2192 ORDER BY pricegroup|;
2193 my @values = (conv_i($id), conv_i($id));
2194 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2196 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2198 $pkr->{selected} = '';
2200 # if there is an exchange rate change price
2201 if (($form->{exchangerate} * 1) != 0) {
2202 $pkr->{price} /= $form->{exchangerate};
2205 $pkr->{price} *= $form->{"basefactor_$i"};
2206 $pkr->{price} *= $basefactor;
2207 $pkr->{price_ufmt} = $pkr->{price};
2208 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2210 if (!defined $selectedpricegroup_id) {
2211 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2212 # Case A: open old invoice, no pricegroup selected
2213 # Case B: add new article to invoice, no pricegroup selected
2215 # to distinguish case A and B the variable pricegroup_id_$i is used
2216 # for new articles this variable isn't defined, for loaded articles it is
2217 # sellprice can't be used, as it already has 0,00 set
2219 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2221 $pkr->{selected} = ' selected';
2222 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2223 and not defined $form->{"pricegroup_id_$i"}
2224 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2225 # for the case where pricegroup prices haven't been set
2227 # Case B: use default pricegroup of customer
2229 $pkr->{selected} = ' selected'; # unless $form->{selected};
2230 # no customer pricesgroup set
2231 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2233 $pkr->{price} = $form->{"sellprice_$i"};
2237 # this sub should not set anything and only return. --sschoeling, 20090506
2238 # is this correct? put in again... -- grichardson 20110119
2239 $form->{"sellprice_$i"} = $pkr->{price};
2242 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2243 $pkr->{price} = $form->{"sellprice_$i"};
2244 $pkr->{selected} = ' selected';
2248 # existing article: pricegroup or price changed
2249 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2250 if ($selectedpricegroup_id ne $pricegroup_old) {
2251 # pricegroup has changed
2252 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2253 $pkr->{selected} = ' selected';
2255 } elsif ( ($form->parse_amount($myconfig, $price_new)
2256 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2257 and ($price_new ne 0) and defined $price_new) {
2258 # sellprice has changed
2259 # when loading existing invoices $price_new is NULL
2260 if ($pkr->{pricegroup_id} == 0) {
2261 $pkr->{price} = $form->{"sellprice_$i"};
2262 $pkr->{selected} = ' selected';
2264 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2265 # neither sellprice nor pricegroup changed
2266 $pkr->{selected} = ' selected';
2267 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2268 # $pkr->{price} = $form->{"sellprice_$i"};
2270 $pkr->{price} = $form->{"sellprice_$i"};
2274 push @{ $form->{PRICES}{$i} }, $pkr;
2277 $form->{"basefactor_$i"} *= $basefactor;
2284 $main::lxdebug->leave_sub();
2288 $main::lxdebug->enter_sub();
2290 my ($self, $myconfig, $form, $table) = @_;
2292 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2294 # make sure there's no funny stuff in $table
2295 # ToDO: die when this happens and throw an error
2296 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2298 my $dbh = $form->get_standard_dbh;
2300 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2301 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2303 $main::lxdebug->leave_sub();
2309 $main::lxdebug->enter_sub();
2311 my ($self, $myconfig, $form, $table, $id) = @_;
2313 $main::lxdebug->leave_sub() and return 0 unless ($id);
2315 # make sure there's no funny stuff in $table
2316 # ToDO: die when this happens and throw an error
2317 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2319 my $dbh = $form->get_standard_dbh;
2321 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2322 my ($result) = selectrow_query($form, $dbh, $query, $id);
2324 $main::lxdebug->leave_sub();
2329 sub get_standard_accno_current_assets {
2330 $main::lxdebug->enter_sub();
2332 my ($self, $myconfig, $form) = @_;
2334 my $dbh = $form->get_standard_dbh;
2336 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2337 my ($result) = selectrow_query($form, $dbh, $query);
2339 $main::lxdebug->leave_sub();