1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing();
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
227 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
228 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
231 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
233 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
235 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
248 $form->{total} += $linetotal;
249 $form->{nodiscount_total} += $nodiscount_linetotal;
250 $form->{discount_total} += $discount;
252 if ($subtotal_header) {
253 $discount_subtotal += $linetotal;
254 $nodiscount_subtotal += $nodiscount_linetotal;
257 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
260 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
263 $discount_subtotal = 0;
264 $nodiscount_subtotal = 0;
265 $subtotal_header = 0;
268 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
271 if (!$form->{"discount_$i"}) {
272 $nodiscount += $linetotal;
275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
364 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
365 for @{ $ic_cvar_configs };
369 foreach my $item (sort keys %taxaccounts) {
370 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
375 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
377 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
382 for my $i (1 .. $form->{paidaccounts}) {
383 if ($form->{"paid_$i"}) {
384 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
386 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
388 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
389 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
390 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
392 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
395 if($form->{taxincluded}) {
396 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
397 $form->{subtotal_nofmt} = $form->{total} - $tax;
400 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
401 $form->{subtotal_nofmt} = $form->{total};
404 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
405 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
406 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
407 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
409 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
410 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
412 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
413 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
415 $form->set_payment_options($myconfig, $form->{invdate});
417 $form->{username} = $myconfig->{name};
419 $main::lxdebug->leave_sub();
422 sub project_description {
423 $main::lxdebug->enter_sub();
425 my ($self, $dbh, $id) = @_;
426 my $form = \%main::form;
428 my $query = qq|SELECT description FROM project WHERE id = ?|;
429 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
431 $main::lxdebug->leave_sub();
436 sub customer_details {
437 $main::lxdebug->enter_sub();
439 my ($self, $myconfig, $form, @wanted_vars) = @_;
441 # connect to database
442 my $dbh = $form->get_standard_dbh;
444 my $language_id = $form->{language_id};
446 # get contact id, set it if nessessary
449 my @values = (conv_i($form->{customer_id}));
452 if ($form->{cp_id}) {
453 $where = qq| AND (cp.cp_id = ?) |;
454 push(@values, conv_i($form->{cp_id}));
457 # get rest for the customer
459 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
460 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
463 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
464 WHERE (ct.id = ?) $where
467 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
469 # remove id and taxincluded before copy back
470 delete @$ref{qw(id taxincluded)};
472 @wanted_vars = grep({ $_ } @wanted_vars);
473 if (scalar(@wanted_vars) > 0) {
475 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
476 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
479 map { $form->{$_} = $ref->{$_} } keys %$ref;
481 # remove any trailing whitespace
482 $form->{currency} =~ s/\s*$// if ($form->{currency});
484 if ($form->{delivery_customer_id}) {
486 qq|SELECT *, notes as customernotes
490 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
492 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
495 if ($form->{delivery_vendor_id}) {
497 qq|SELECT *, notes as customernotes
501 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
503 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
506 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
508 'trans_id' => $form->{customer_id});
509 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
511 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
512 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
513 'language_id' => $language_id,
514 'allow_fallback' => 1);
517 $main::lxdebug->leave_sub();
521 $main::lxdebug->enter_sub();
523 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
525 # connect to database, turn off autocommit
526 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
528 my ($query, $sth, $null, $project_id, @values);
529 my $exchangerate = 0;
531 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
534 if (!$form->{employee_id}) {
535 $form->get_employee($dbh);
538 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
539 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
540 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
541 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
542 if (!$form->{department_id}){
543 ($null, $form->{department_id}) = split(/--/, $form->{department});
546 my $all_units = AM->retrieve_units($myconfig, $form);
548 if (!$payments_only) {
550 &reverse_invoice($dbh, $form);
553 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
554 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
556 $query = qq|SELECT nextval('glid')|;
557 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
559 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
560 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
562 if (!$form->{invnumber}) {
564 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
565 "cnnumber" : "invnumber", $dbh);
570 my ($netamount, $invoicediff) = (0, 0);
571 my ($amount, $linetotal, $lastincomeaccno);
573 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
574 my $defaultcurrency = (split m/:/, $currencies)[0];
576 if ($form->{currency} eq $defaultcurrency) {
577 $form->{exchangerate} = 1;
579 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
582 $form->{exchangerate} =
585 : $form->parse_amount($myconfig, $form->{exchangerate});
587 $form->{expense_inventory} = "";
591 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
592 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
595 $form->{amount} = {};
596 $form->{amount_cogs} = {};
598 foreach my $i (1 .. $form->{rowcount}) {
599 if ($form->{type} eq "credit_note") {
600 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
601 $form->{shipped} = 1;
603 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
608 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
609 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
610 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
612 if ($form->{storno}) {
613 $form->{"qty_$i"} *= -1;
616 if ($form->{"id_$i"}) {
619 if (defined($baseunits{$form->{"id_$i"}})) {
620 $item_unit = $baseunits{$form->{"id_$i"}};
623 $query = qq|SELECT unit FROM parts WHERE id = ?|;
624 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
625 $baseunits{$form->{"id_$i"}} = $item_unit;
628 if (defined($all_units->{$item_unit}->{factor})
629 && ($all_units->{$item_unit}->{factor} ne '')
630 && ($all_units->{$item_unit}->{factor} != 0)) {
631 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
635 $baseqty = $form->{"qty_$i"} * $basefactor;
637 my ($allocated, $taxrate) = (0, 0);
641 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
643 # keep entered selling price
645 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
647 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
649 my $decimalplaces = ($dec > 2) ? $dec : 2;
651 # undo discount formatting
652 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
655 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
657 # round linetotal to 2 decimal places
658 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
659 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
661 if ($form->{taxincluded}) {
662 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
663 $form->{"sellprice_$i"} =
664 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
666 $taxamount = $linetotal * $taxrate;
669 $netamount += $linetotal;
671 if ($taxamount != 0) {
673 $form->{amount}{ $form->{id} }{$_} +=
674 $taxamount * $form->{"${_}_rate"} / $taxrate
675 } split(/ /, $form->{"taxaccounts_$i"});
678 # add amount to income, $form->{amount}{trans_id}{accno}
679 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
681 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
682 $linetotal = $form->round_amount($linetotal, 2);
684 # this is the difference from the inventory
685 $invoicediff += ($amount - $linetotal);
687 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
690 $lastincomeaccno = $form->{"income_accno_$i"};
692 # adjust and round sellprice
693 $form->{"sellprice_$i"} =
694 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
697 next if $payments_only;
699 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
701 if ($form->{"assembly_$i"}) {
702 # record assembly item as allocated
703 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
706 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
710 # Get pricegroup_id and save it. Unfortunately the interface
711 # also uses ID "0" for signalling that none is selected, but "0"
712 # must not be stored in the database. Therefore we cannot simply
714 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
716 $pricegroup_id = undef if !$pricegroup_id;
718 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
720 # save detail record in invoice table
722 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
723 sellprice, fxsellprice, discount, allocated, assemblyitem,
724 unit, deliverydate, project_id, serialnumber, pricegroup_id,
725 ordnumber, transdate, cusordnumber, base_qty, subtotal,
726 marge_percent, marge_total, lastcost,
727 price_factor_id, price_factor, marge_price_factor)
728 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
729 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
731 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
732 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
733 $form->{"sellprice_$i"}, $fxsellprice,
734 $form->{"discount_$i"}, $allocated, 'f',
735 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
736 $form->{"serialnumber_$i"}, $pricegroup_id,
737 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
738 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
739 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
740 $form->{"lastcost_$i"},
741 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
742 conv_i($form->{"marge_price_factor_$i"}));
743 do_query($form, $dbh, $query, @values);
745 CVar->save_custom_variables(module => 'IC',
746 sub_module => 'invoice',
747 trans_id => $invoice_id,
748 configs => $ic_cvar_configs,
750 name_prefix => 'ic_',
751 name_postfix => "_$i",
756 # total payments, don't move we need it here
757 for my $i (1 .. $form->{paidaccounts}) {
758 if ($form->{type} eq "credit_note") {
759 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
761 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
763 $form->{paid} += $form->{"paid_$i"};
764 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
767 my ($tax, $diff) = (0, 0);
769 $netamount = $form->round_amount($netamount, 2);
771 # figure out rounding errors for total amount vs netamount + taxes
772 if ($form->{taxincluded}) {
774 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
775 $diff += $amount - $netamount * $form->{exchangerate};
776 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
780 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
781 $tax += $form->{amount}{ $form->{id} }{$item};
782 $netamount -= $form->{amount}{ $form->{id} }{$item};
785 $invoicediff += $diff;
786 ######## this only applies to tax included
787 if ($lastincomeaccno) {
788 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
792 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
793 $diff = $amount - $netamount * $form->{exchangerate};
794 $netamount = $amount;
795 foreach my $item (split(/ /, $form->{taxaccounts})) {
796 $form->{amount}{ $form->{id} }{$item} =
797 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
800 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
803 $amount - $form->{amount}{ $form->{id} }{$item} *
804 $form->{exchangerate};
805 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
806 $tax += $form->{amount}{ $form->{id} }{$item};
810 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
812 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
815 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
817 # update exchangerate
818 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
819 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
820 $form->{exchangerate}, 0);
823 $project_id = conv_i($form->{"globalproject_id"});
825 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
827 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
828 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
829 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
831 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
833 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
838 do_query($form, $dbh, $query, @values);
839 $form->{amount_cogs}{$trans_id}{$accno} = 0;
843 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
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)
849 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|;
850 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
851 do_query($form, $dbh, $query, @values);
856 foreach my $trans_id (keys %{ $form->{amount} }) {
857 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
858 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
860 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
862 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
864 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
865 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
868 WHERE chart_id= (SELECT id
872 ORDER BY startdate DESC LIMIT 1),
873 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
874 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
875 do_query($form, $dbh, $query, @values);
876 $form->{amount}{$trans_id}{$accno} = 0;
880 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
881 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
883 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
885 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
886 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
889 WHERE chart_id= (SELECT id
893 ORDER BY startdate DESC LIMIT 1),
894 (SELECT taxkey_id 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_i($project_id));
896 do_query($form, $dbh, $query, @values);
901 # deduct payment differences from diff
902 for my $i (1 .. $form->{paidaccounts}) {
903 if ($form->{"paid_$i"} != 0) {
905 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
906 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
910 # record payments and offsetting AR
911 if (!$form->{storno}) {
912 for my $i (1 .. $form->{paidaccounts}) {
914 if ($form->{"acc_trans_id_$i"}
916 && (SL::DB::Default->get->payments_changeable == 0)) {
920 next if ($form->{"paid_$i"} == 0);
922 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
923 $form->{"datepaid_$i"} = $form->{invdate}
924 unless ($form->{"datepaid_$i"});
925 $form->{datepaid} = $form->{"datepaid_$i"};
929 if ($form->{currency} eq $defaultcurrency) {
930 $form->{"exchangerate_$i"} = 1;
932 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
933 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
937 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
939 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
941 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
942 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
945 WHERE chart_id= (SELECT id
949 ORDER BY startdate DESC LIMIT 1),
950 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
951 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
952 do_query($form, $dbh, $query, @values);
956 $form->{"paid_$i"} *= -1;
957 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
960 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id)
961 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
964 WHERE chart_id= (SELECT id
968 ORDER BY startdate DESC LIMIT 1),
969 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
970 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
971 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);
972 do_query($form, $dbh, $query, @values);
974 # exchangerate difference
975 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
976 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
980 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
981 $form->{"exchangerate_$i"};
983 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
985 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
990 # update exchange rate
991 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
992 $form->update_exchangerate($dbh, $form->{currency},
993 $form->{"datepaid_$i"},
994 $form->{"exchangerate_$i"}, 0);
998 } else { # if (!$form->{storno})
999 $form->{marge_total} *= -1;
1002 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1004 # record exchange rate differences and gains/losses
1005 foreach my $accno (keys %{ $form->{fx} }) {
1006 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1007 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1008 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1011 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id)
1012 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1015 WHERE chart_id= (SELECT id
1019 ORDER BY startdate DESC LIMIT 1),
1020 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
1021 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
1022 do_query($form, $dbh, $query, @values);
1027 if ($payments_only) {
1028 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1029 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1031 $dbh->commit if !$provided_dbh;
1033 $main::lxdebug->leave_sub();
1037 $amount = $netamount + $tax;
1040 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1042 $query = qq|UPDATE ar set
1043 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1044 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1045 amount = ?, netamount = ?, paid = ?,
1046 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1047 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1048 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1049 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1050 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1051 cp_id = ?, marge_total = ?, marge_percent = ?,
1052 globalproject_id = ?, delivery_customer_id = ?,
1053 transaction_description = ?, delivery_vendor_id = ?,
1054 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1056 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1057 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1058 $amount, $netamount, $form->{"paid"},
1059 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1060 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1061 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1062 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1063 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1064 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1065 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1066 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1067 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1068 conv_i($form->{"id"}));
1069 do_query($form, $dbh, $query, @values);
1072 if ($form->{storno}) {
1075 paid = paid + amount,
1077 intnotes = ? || intnotes
1079 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1080 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1084 $form->{name} = $form->{customer};
1085 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1087 if (!$form->{shipto_id}) {
1088 $form->add_shipto($dbh, $form->{id}, "AR");
1091 # save printed, emailed and queued
1092 $form->save_status($dbh);
1094 Common::webdav_folder($form);
1096 # Link this record to the records it was created from.
1097 RecordLinks->create_links('dbh' => $dbh,
1099 'from_table' => 'oe',
1100 'from_ids' => $form->{convert_from_oe_ids},
1102 'to_id' => $form->{id},
1104 delete $form->{convert_from_oe_ids};
1106 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1108 if (scalar @convert_from_do_ids) {
1109 DO->close_orders('dbh' => $dbh,
1110 'ids' => \@convert_from_do_ids);
1112 RecordLinks->create_links('dbh' => $dbh,
1114 'from_table' => 'delivery_orders',
1115 'from_ids' => \@convert_from_do_ids,
1117 'to_id' => $form->{id},
1120 delete $form->{convert_from_do_ids};
1122 ARAP->close_orders_if_billed('dbh' => $dbh,
1123 'arap_id' => $form->{id},
1126 # safety check datev export
1127 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1128 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1129 $transdate ||= DateTime->today;
1131 my $datev = SL::DATEV->new(
1132 exporttype => DATEV_ET_BUCHUNGEN,
1133 format => DATEV_FORMAT_KNE,
1141 if ($datev->errors) {
1143 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1148 $dbh->commit if !$provided_dbh;
1150 $main::lxdebug->leave_sub();
1155 sub _delete_payments {
1156 $main::lxdebug->enter_sub();
1158 my ($self, $form, $dbh) = @_;
1160 my @delete_acc_trans_ids;
1162 # Delete old payment entries from acc_trans.
1164 qq|SELECT acc_trans_id
1166 WHERE (trans_id = ?) AND fx_transaction
1170 SELECT at.acc_trans_id
1172 LEFT JOIN chart c ON (at.chart_id = c.id)
1173 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1174 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1177 qq|SELECT at.acc_trans_id
1179 LEFT JOIN chart c ON (at.chart_id = c.id)
1180 WHERE (trans_id = ?)
1181 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1182 ORDER BY at.acc_trans_id
1184 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1186 if (@delete_acc_trans_ids) {
1187 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1188 do_query($form, $dbh, $query);
1191 $main::lxdebug->leave_sub();
1195 $main::lxdebug->enter_sub();
1197 my ($self, $myconfig, $form, $locale) = @_;
1199 # connect to database, turn off autocommit
1200 my $dbh = $form->get_standard_dbh;
1203 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1205 $old_form = save_form();
1207 # Delete all entries in acc_trans from prior payments.
1208 if (SL::DB::Default->get->payments_changeable != 0) {
1209 $self->_delete_payments($form, $dbh);
1212 # Save the new payments the user made before cleaning up $form.
1213 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 };
1215 # Clean up $form so that old content won't tamper the results.
1216 %keep_vars = map { $_, 1 } qw(login password id);
1217 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1219 # Retrieve the invoice from the database.
1220 $self->retrieve_invoice($myconfig, $form);
1222 # Set up the content of $form in the way that IS::post_invoice() expects.
1223 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1225 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1226 $item = $form->{invoice_details}->[$row - 1];
1228 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1230 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1233 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1235 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1237 # Restore the payment options from the user input.
1238 map { $form->{$_} = $payments{$_} } keys %payments;
1240 # Get the AR accno (which is normally done by Form::create_links()).
1244 LEFT JOIN chart c ON (at.chart_id = c.id)
1245 WHERE (trans_id = ?)
1246 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1247 ORDER BY at.acc_trans_id
1250 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1252 # Post the new payments.
1253 $self->post_invoice($myconfig, $form, $dbh, 1);
1255 restore_form($old_form);
1257 my $rc = $dbh->commit();
1259 $main::lxdebug->leave_sub();
1264 sub process_assembly {
1265 $main::lxdebug->enter_sub();
1267 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1270 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1271 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1273 JOIN parts p ON (a.parts_id = p.id)
1275 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1277 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1281 $ref->{inventory_accno_id} *= 1;
1282 $ref->{expense_accno_id} *= 1;
1284 # multiply by number of assemblies
1285 $ref->{qty} *= $totalqty;
1287 if ($ref->{assembly}) {
1288 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1291 if ($ref->{inventory_accno_id}) {
1292 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1296 # save detail record for individual assembly item in invoice table
1298 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1299 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1300 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1301 do_query($form, $dbh, $query, @values);
1307 $main::lxdebug->leave_sub();
1311 $main::lxdebug->enter_sub();
1313 # adjust allocated in table invoice according to FIFO princicple
1314 # for a certain part with part_id $id
1316 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1320 $form->{taxzone_id} *=1;
1321 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1322 my $taxzone_id = $form->{"taxzone_id"} * 1;
1324 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1325 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1326 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1327 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1328 FROM invoice i, parts p
1329 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1330 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1331 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1332 WHERE (i.parts_id = p.id)
1333 AND (i.parts_id = ?)
1334 AND ((i.base_qty + i.allocated) < 0)
1336 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1341 # all invoice entries of an example part:
1343 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1344 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1345 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1346 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1347 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1348 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1349 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1351 # 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
1352 # and all parts have been allocated
1354 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1355 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1357 # in this example there are still 4 unsold articles
1360 # search all invoice entries for the part in question, adjusting "allocated"
1361 # until the total number of sold parts has been reached
1363 # ORDER BY trans_id ensures FIFO
1366 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1367 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1371 # update allocated in invoice
1372 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1374 # total expenses and inventory
1375 # sellprice is the cost of the item
1376 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1378 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1379 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1380 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1382 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1383 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1384 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1386 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1387 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1393 last if (($totalqty -= $qty) <= 0);
1398 $main::lxdebug->leave_sub();
1403 sub reverse_invoice {
1404 $main::lxdebug->enter_sub();
1406 my ($dbh, $form) = @_;
1408 # reverse inventory items
1410 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1412 JOIN parts p ON (i.parts_id = p.id)
1413 WHERE i.trans_id = ?|;
1414 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1416 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1418 if ($ref->{inventory_accno_id}) {
1419 # de-allocated purchases
1421 qq|SELECT i.id, i.trans_id, i.allocated
1423 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1424 ORDER BY i.trans_id DESC|;
1425 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1427 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1428 my $qty = $ref->{qty};
1429 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1430 $qty = $inhref->{allocated};
1434 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1436 last if (($ref->{qty} -= $qty) <= 0);
1445 my @values = (conv_i($form->{id}));
1446 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1447 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1448 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1450 $main::lxdebug->leave_sub();
1453 sub delete_invoice {
1454 $main::lxdebug->enter_sub();
1456 my ($self, $myconfig, $form) = @_;
1458 # connect to database
1459 my $dbh = $form->get_standard_dbh;
1462 &reverse_invoice($dbh, $form);
1464 my @values = (conv_i($form->{id}));
1466 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1467 # zurückgesetzt werden. Vgl:
1468 # id | storno | storno_id | paid | amount
1469 #----+--------+-----------+---------+-----------
1470 # 18 | f | | 0.00000 | 119.00000
1472 # 18 | t | | 119.00000 | 119.00000
1474 if($form->{storno}){
1475 # storno_id auslesen und korrigieren
1476 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1477 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1480 # delete spool files
1481 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1484 qq|DELETE FROM status WHERE trans_id = ?|,
1485 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1486 qq|DELETE FROM ar WHERE id = ?|,
1489 map { do_query($form, $dbh, $_, @values) } @queries;
1491 my $rc = $dbh->commit;
1494 my $spool = $::lx_office_conf{paths}->{spool};
1495 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1498 $main::lxdebug->leave_sub();
1503 sub retrieve_invoice {
1504 $main::lxdebug->enter_sub();
1506 my ($self, $myconfig, $form) = @_;
1508 # connect to database
1509 my $dbh = $form->get_standard_dbh;
1511 my ($sth, $ref, $query);
1513 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1517 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1518 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1519 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1520 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1521 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1522 d.curr AS currencies
1526 $ref = selectfirst_hashref_query($form, $dbh, $query);
1527 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1530 my $id = conv_i($form->{id});
1533 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1537 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1538 a.orddate, a.quodate, a.globalproject_id,
1539 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1540 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1541 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1542 a.employee_id, a.salesman_id, a.payment_id,
1543 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1544 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1545 a.marge_total, a.marge_percent, a.direct_debit,
1548 LEFT JOIN employee e ON (e.id = a.employee_id)
1550 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1551 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1553 # remove any trailing whitespace
1554 $form->{currency} =~ s/\s*$//;
1556 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1559 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1560 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1562 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1564 foreach my $vc (qw(customer vendor)) {
1565 next if !$form->{"delivery_${vc}_id"};
1566 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1569 # get printed, emailed
1570 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1571 $sth = prepare_execute_query($form, $dbh, $query, $id);
1573 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1574 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1575 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1576 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1579 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1581 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1582 : $form->{invdate} ? $dbh->quote($form->{invdate})
1586 my $taxzone_id = $form->{taxzone_id} *= 1;
1587 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1589 # retrieve individual items
1592 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1593 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1594 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1597 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1598 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1599 i.price_factor_id, i.price_factor, i.marge_price_factor,
1600 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1601 pr.projectnumber, pg.partsgroup, prg.pricegroup
1604 LEFT JOIN parts p ON (i.parts_id = p.id)
1605 LEFT JOIN project pr ON (i.project_id = pr.id)
1606 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1607 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1609 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1610 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1611 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1613 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1615 $sth = prepare_execute_query($form, $dbh, $query, $id);
1617 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1618 # Retrieve custom variables.
1619 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1621 sub_module => 'invoice',
1622 trans_id => $ref->{invoice_id},
1624 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1625 delete $ref->{invoice_id};
1627 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1628 delete($ref->{"part_inventory_accno_id"});
1630 foreach my $type (qw(inventory income expense)) {
1631 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1632 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1633 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1637 # get tax rates and description
1638 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1640 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1641 LEFT JOIN chart c ON (c.id = t.chart_id)
1643 (SELECT tk.tax_id FROM taxkeys tk
1644 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1645 AND startdate <= date($transdate)
1646 ORDER BY startdate DESC LIMIT 1)
1648 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1649 $ref->{taxaccounts} = "";
1651 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1653 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1657 $ref->{taxaccounts} .= "$ptr->{accno} ";
1659 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1660 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1661 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1662 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1663 $form->{taxaccounts} .= "$ptr->{accno} ";
1668 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1670 chop $ref->{taxaccounts};
1671 push @{ $form->{invoice_details} }, $ref;
1676 Common::webdav_folder($form);
1679 my $rc = $dbh->commit;
1681 $main::lxdebug->leave_sub();
1687 $main::lxdebug->enter_sub();
1689 my ($self, $myconfig, $form) = @_;
1691 # connect to database
1692 my $dbh = $form->get_standard_dbh;
1694 my $dateformat = $myconfig->{dateformat};
1695 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1697 my (@values, $duedate, $ref, $query);
1699 if ($form->{invdate}) {
1700 $duedate = "to_date(?, '$dateformat')";
1701 push @values, $form->{invdate};
1703 $duedate = "current_date";
1706 my $cid = conv_i($form->{customer_id});
1709 if ($form->{payment_id}) {
1710 $payment_id = "(pt.id = ?) OR";
1711 push @values, conv_i($form->{payment_id});
1717 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1718 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1719 c.street, c.zipcode, c.city, c.country,
1720 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1721 c.taxincluded_checked, c.direct_debit,
1722 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1723 b.discount AS tradediscount, b.description AS business
1725 LEFT JOIN business b ON (b.id = c.business_id)
1726 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1729 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1731 delete $ref->{salesman_id} if !$ref->{salesman_id};
1733 map { $form->{$_} = $ref->{$_} } keys %$ref;
1735 # remove any trailing whitespace
1736 $form->{curr} =~ s/\s*$//;
1738 # use customer currency if not empty
1739 $form->{currency} = $form->{curr} if $form->{curr};
1742 qq|SELECT sum(amount - paid) AS dunning_amount
1744 WHERE (paid < amount)
1745 AND (customer_id = ?)
1746 AND (dunning_config_id IS NOT NULL)|;
1747 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1748 map { $form->{$_} = $ref->{$_} } keys %$ref;
1751 qq|SELECT dnn.dunning_description AS max_dunning_level
1752 FROM dunning_config dnn
1753 WHERE id IN (SELECT dunning_config_id
1755 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1756 ORDER BY dunning_level DESC LIMIT 1|;
1757 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1758 map { $form->{$_} = $ref->{$_} } keys %$ref;
1760 $form->{creditremaining} = $form->{creditlimit};
1761 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1762 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1763 $form->{creditremaining} -= $value;
1767 (SELECT e.buy FROM exchangerate e
1768 WHERE e.curr = o.curr
1769 AND e.transdate = o.transdate)
1771 WHERE o.customer_id = ?
1772 AND o.quotation = '0'
1773 AND o.closed = '0'|;
1774 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1776 while (my ($amount, $exch) = $sth->fetchrow_array) {
1777 $exch = 1 unless $exch;
1778 $form->{creditremaining} -= $amount * $exch;
1782 # get shipto if we did not converted an order or invoice
1783 if (!$form->{shipto}) {
1784 map { delete $form->{$_} }
1785 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1786 shiptostreet shiptozipcode shiptocity shiptocountry
1787 shiptocontact shiptophone shiptofax shiptoemail);
1789 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1790 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1792 map { $form->{$_} = $ref->{$_} } keys %$ref;
1795 # setup last accounts used for this customer
1796 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1798 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1800 JOIN acc_trans ac ON (ac.chart_id = c.id)
1801 JOIN ar a ON (a.id = ac.trans_id)
1802 WHERE a.customer_id = ?
1803 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1804 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1805 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1808 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1809 if ($ref->{category} eq 'I') {
1811 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1813 if ($form->{initial_transdate}) {
1815 qq|SELECT tk.tax_id, t.rate
1817 LEFT JOIN tax t ON tk.tax_id = t.id
1818 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1819 ORDER BY tk.startdate DESC
1821 my ($tax_id, $rate) =
1822 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1823 $form->{initial_transdate});
1824 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1827 if ($ref->{category} eq 'A') {
1828 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1832 $form->{rowcount} = $i if ($i && !$form->{type});
1835 $main::lxdebug->leave_sub();
1839 $main::lxdebug->enter_sub();
1841 my ($self, $myconfig, $form) = @_;
1843 # connect to database
1844 my $dbh = $form->get_standard_dbh;
1846 my $i = $form->{rowcount};
1848 my $where = qq|NOT p.obsolete = '1'|;
1851 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1852 my ($table, $field) = split m/\./, $column;
1853 next if !$form->{"${field}_${i}"};
1854 $where .= qq| AND lower(${column}) ILIKE ?|;
1855 push @values, '%' . $form->{"${field}_${i}"} . '%';
1858 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1859 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1860 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1861 push @values, $form->{"partnumber_$i"};
1864 # Search for part ID overrides all other criteria.
1865 if ($form->{"id_${i}"}) {
1866 $where = qq|p.id = ?|;
1867 @values = ($form->{"id_${i}"});
1870 if ($form->{"description_$i"}) {
1871 $where .= qq| ORDER BY p.description|;
1873 $where .= qq| ORDER BY p.partnumber|;
1877 if ($form->{type} eq "invoice") {
1879 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1880 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1884 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1888 my $taxzone_id = $form->{taxzone_id} * 1;
1889 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1893 p.id, p.partnumber, p.description, p.sellprice,
1894 p.listprice, p.inventory_accno_id, p.lastcost,
1896 c1.accno AS inventory_accno,
1897 c1.new_chart_id AS inventory_new_chart,
1898 date($transdate) - c1.valid_from AS inventory_valid,
1900 c2.accno AS income_accno,
1901 c2.new_chart_id AS income_new_chart,
1902 date($transdate) - c2.valid_from AS income_valid,
1904 c3.accno AS expense_accno,
1905 c3.new_chart_id AS expense_new_chart,
1906 date($transdate) - c3.valid_from AS expense_valid,
1908 p.unit, p.assembly, p.bin, p.onhand,
1909 p.notes AS partnotes, p.notes AS longdescription,
1910 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1913 pfac.factor AS price_factor,
1918 LEFT JOIN chart c1 ON
1919 ((SELECT inventory_accno_id
1920 FROM buchungsgruppen
1921 WHERE id = p.buchungsgruppen_id) = c1.id)
1922 LEFT JOIN chart c2 ON
1923 ((SELECT income_accno_id_${taxzone_id}
1924 FROM buchungsgruppen
1925 WHERE id = p.buchungsgruppen_id) = c2.id)
1926 LEFT JOIN chart c3 ON
1927 ((SELECT expense_accno_id_${taxzone_id}
1928 FROM buchungsgruppen
1929 WHERE id = p.buchungsgruppen_id) = c3.id)
1930 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1931 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1933 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1935 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1937 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1938 [ qq|SELECT tr.translation, tr.longdescription
1940 WHERE tr.language_id IN
1943 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1946 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1948 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1950 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1951 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1952 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1953 if (!$ref->{inventory_accno_id}) {
1954 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1956 delete($ref->{inventory_accno_id});
1958 foreach my $type (qw(inventory income expense)) {
1959 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1961 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1964 ($ref->{"${type}_accno"},
1965 $ref->{"${type}_new_chart"},
1966 $ref->{"${type}_valid"})
1967 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1971 if ($form->{payment_id} eq "") {
1972 $form->{payment_id} = $form->{part_payment_id};
1975 # get tax rates and description
1976 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1978 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1980 LEFT JOIN chart c ON (c.id = t.chart_id)
1984 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1986 ORDER BY startdate DESC
1989 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1990 my $stw = $dbh->prepare($query);
1991 $stw->execute(@values) || $form->dberror($query);
1993 $ref->{taxaccounts} = "";
1995 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1997 # if ($customertax{$ref->{accno}})
1998 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2002 $ref->{taxaccounts} .= "$ptr->{accno} ";
2004 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2005 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2006 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2007 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2008 $form->{taxaccounts} .= "$ptr->{accno} ";
2014 chop $ref->{taxaccounts};
2016 if ($form->{language_id}) {
2017 for my $spec (@translation_queries) {
2018 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2019 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2020 next unless $translation;
2021 $ref->{description} = $translation;
2022 $ref->{longdescription} = $longdescription;
2027 $ref->{onhand} *= 1;
2029 push @{ $form->{item_list} }, $ref;
2032 $_->[1]->finish for @translation_queries;
2034 foreach my $item (@{ $form->{item_list} }) {
2035 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2036 trans_id => $item->{id},
2040 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2043 $main::lxdebug->leave_sub();
2046 ##########################
2047 # get pricegroups from database
2048 # build up selected pricegroup
2049 # if an exchange rate - change price
2052 sub get_pricegroups_for_parts {
2054 $main::lxdebug->enter_sub();
2056 my ($self, $myconfig, $form) = @_;
2058 my $dbh = $form->get_standard_dbh;
2060 $form->{"PRICES"} = {};
2064 my $all_units = AM->retrieve_units($myconfig, $form);
2065 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2066 $form->{"PRICES"}{$i} = [];
2068 $id = $form->{"id_$i"};
2070 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2071 $id = $form->{"new_id_$i"};
2074 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2076 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2078 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2079 my $sellprice = $form->{"sellprice_$i"};
2080 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2081 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2082 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2084 my $price_new = $form->{"price_new_$i"};
2085 my $price_old = $form->{"price_old_$i"};
2087 if (!$form->{"unit_old_$i"}) {
2088 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2089 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2090 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2091 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2094 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2095 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2096 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2098 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2099 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2100 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2101 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2102 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2103 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2104 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2109 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2110 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2111 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2112 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2113 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2117 if (!$form->{"basefactor_$i"}) {
2118 $form->{"basefactor_$i"} = 1;
2124 sellprice AS default_sellprice,
2127 'selected' AS selected
2133 parts.sellprice AS default_sellprice,
2134 pricegroup.pricegroup,
2138 LEFT JOIN parts ON parts.id = parts_id
2139 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2141 ORDER BY pricegroup|;
2142 my @values = (conv_i($id), conv_i($id));
2143 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2145 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2147 $pkr->{selected} = '';
2149 # if there is an exchange rate change price
2150 if (($form->{exchangerate} * 1) != 0) {
2151 $pkr->{price} /= $form->{exchangerate};
2154 $pkr->{price} *= $form->{"basefactor_$i"};
2155 $pkr->{price} *= $basefactor;
2156 $pkr->{price_ufmt} = $pkr->{price};
2157 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2159 if (!defined $selectedpricegroup_id) {
2160 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2161 # Case A: open old invoice, no pricegroup selected
2162 # Case B: add new article to invoice, no pricegroup selected
2164 # to distinguish case A and B the variable pricegroup_id_$i is used
2165 # for new articles this variable isn't defined, for loaded articles it is
2166 # sellprice can't be used, as it already has 0,00 set
2168 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2170 $pkr->{selected} = ' selected';
2171 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2172 and not defined $form->{"pricegroup_id_$i"}
2173 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2174 # for the case where pricegroup prices haven't been set
2176 # Case B: use default pricegroup of customer
2178 $pkr->{selected} = ' selected'; # unless $form->{selected};
2179 # no customer pricesgroup set
2180 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2182 $pkr->{price} = $form->{"sellprice_$i"};
2186 # this sub should not set anything and only return. --sschoeling, 20090506
2187 # is this correct? put in again... -- grichardson 20110119
2188 $form->{"sellprice_$i"} = $pkr->{price};
2191 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2192 $pkr->{price} = $form->{"sellprice_$i"};
2193 $pkr->{selected} = ' selected';
2197 # existing article: pricegroup or price changed
2198 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2199 if ($selectedpricegroup_id ne $pricegroup_old) {
2200 # pricegroup has changed
2201 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2202 $pkr->{selected} = ' selected';
2204 } elsif ( ($form->parse_amount($myconfig, $price_new)
2205 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2206 and ($price_new ne 0) and defined $price_new) {
2207 # sellprice has changed
2208 # when loading existing invoices $price_new is NULL
2209 if ($pkr->{pricegroup_id} == 0) {
2210 $pkr->{price} = $form->{"sellprice_$i"};
2211 $pkr->{selected} = ' selected';
2213 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2214 # neither sellprice nor pricegroup changed
2215 $pkr->{selected} = ' selected';
2216 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2217 # $pkr->{price} = $form->{"sellprice_$i"};
2219 $pkr->{price} = $form->{"sellprice_$i"};
2223 push @{ $form->{PRICES}{$i} }, $pkr;
2226 $form->{"basefactor_$i"} *= $basefactor;
2233 $main::lxdebug->leave_sub();
2237 $main::lxdebug->enter_sub();
2239 my ($self, $myconfig, $form, $table) = @_;
2241 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2243 # make sure there's no funny stuff in $table
2244 # ToDO: die when this happens and throw an error
2245 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2247 my $dbh = $form->get_standard_dbh;
2249 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2250 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2252 $main::lxdebug->leave_sub();
2258 $main::lxdebug->enter_sub();
2260 my ($self, $myconfig, $form, $table, $id) = @_;
2262 $main::lxdebug->leave_sub() and return 0 unless ($id);
2264 # make sure there's no funny stuff in $table
2265 # ToDO: die when this happens and throw an error
2266 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2268 my $dbh = $form->get_standard_dbh;
2270 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2271 my ($result) = selectrow_query($form, $dbh, $query, $id);
2273 $main::lxdebug->leave_sub();
2278 sub get_standard_accno_current_assets {
2279 $main::lxdebug->enter_sub();
2281 my ($self, $myconfig, $form) = @_;
2283 my $dbh = $form->get_standard_dbh;
2285 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2286 my ($result) = selectrow_query($form, $dbh, $query);
2288 $main::lxdebug->leave_sub();