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);
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 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
465 WHERE (ct.id = ?) $where
468 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
470 # remove id and taxincluded before copy back
471 delete @$ref{qw(id taxincluded)};
473 @wanted_vars = grep({ $_ } @wanted_vars);
474 if (scalar(@wanted_vars) > 0) {
476 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
477 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
480 map { $form->{$_} = $ref->{$_} } keys %$ref;
482 if ($form->{delivery_customer_id}) {
484 qq|SELECT *, notes as customernotes
488 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
490 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
493 if ($form->{delivery_vendor_id}) {
495 qq|SELECT *, notes as customernotes
499 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
501 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
504 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
506 'trans_id' => $form->{customer_id});
507 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
509 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
510 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
511 'language_id' => $language_id,
512 'allow_fallback' => 1);
515 $main::lxdebug->leave_sub();
519 $main::lxdebug->enter_sub();
521 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
523 # connect to database, turn off autocommit
524 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
526 my ($query, $sth, $null, $project_id, @values);
527 my $exchangerate = 0;
529 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
532 if (!$form->{employee_id}) {
533 $form->get_employee($dbh);
536 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
537 my $defaultcurrency = $form->{defaultcurrency};
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, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
560 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
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 if ($form->{currency} eq $defaultcurrency) {
574 $form->{exchangerate} = 1;
576 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
579 $form->{exchangerate} =
582 : $form->parse_amount($myconfig, $form->{exchangerate});
584 $form->{expense_inventory} = "";
588 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
589 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
592 $form->{amount} = {};
593 $form->{amount_cogs} = {};
595 foreach my $i (1 .. $form->{rowcount}) {
596 if ($form->{type} eq "credit_note") {
597 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
598 $form->{shipped} = 1;
600 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
605 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
606 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
607 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
609 if ($form->{storno}) {
610 $form->{"qty_$i"} *= -1;
613 if ($form->{"id_$i"}) {
616 if (defined($baseunits{$form->{"id_$i"}})) {
617 $item_unit = $baseunits{$form->{"id_$i"}};
620 $query = qq|SELECT unit FROM parts WHERE id = ?|;
621 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
622 $baseunits{$form->{"id_$i"}} = $item_unit;
625 if (defined($all_units->{$item_unit}->{factor})
626 && ($all_units->{$item_unit}->{factor} ne '')
627 && ($all_units->{$item_unit}->{factor} != 0)) {
628 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
632 $baseqty = $form->{"qty_$i"} * $basefactor;
634 my ($allocated, $taxrate) = (0, 0);
638 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
640 # keep entered selling price
642 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
644 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
646 my $decimalplaces = ($dec > 2) ? $dec : 2;
648 # undo discount formatting
649 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
652 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
654 # round linetotal to 2 decimal places
655 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
656 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
658 if ($form->{taxincluded}) {
659 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
660 $form->{"sellprice_$i"} =
661 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
663 $taxamount = $linetotal * $taxrate;
666 $netamount += $linetotal;
668 if ($taxamount != 0) {
670 $form->{amount}{ $form->{id} }{$_} +=
671 $taxamount * $form->{"${_}_rate"} / $taxrate
672 } split(/ /, $form->{"taxaccounts_$i"});
675 # add amount to income, $form->{amount}{trans_id}{accno}
676 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
678 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
679 $linetotal = $form->round_amount($linetotal, 2);
681 # this is the difference from the inventory
682 $invoicediff += ($amount - $linetotal);
684 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
687 $lastincomeaccno = $form->{"income_accno_$i"};
689 # adjust and round sellprice
690 $form->{"sellprice_$i"} =
691 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
694 next if $payments_only;
696 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
698 if ($form->{"assembly_$i"}) {
699 # record assembly item as allocated
700 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
703 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
707 # Get pricegroup_id and save it. Unfortunately the interface
708 # also uses ID "0" for signalling that none is selected, but "0"
709 # must not be stored in the database. Therefore we cannot simply
711 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
713 $pricegroup_id = undef if !$pricegroup_id;
715 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
717 # save detail record in invoice table
719 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
720 sellprice, fxsellprice, discount, allocated, assemblyitem,
721 unit, deliverydate, project_id, serialnumber, pricegroup_id,
722 ordnumber, transdate, cusordnumber, base_qty, subtotal,
723 marge_percent, marge_total, lastcost,
724 price_factor_id, price_factor, marge_price_factor)
725 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
726 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
728 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
729 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
730 $form->{"sellprice_$i"}, $fxsellprice,
731 $form->{"discount_$i"}, $allocated, 'f',
732 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
733 $form->{"serialnumber_$i"}, $pricegroup_id,
734 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
735 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
736 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
737 $form->{"lastcost_$i"},
738 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
739 conv_i($form->{"marge_price_factor_$i"}));
740 do_query($form, $dbh, $query, @values);
742 CVar->save_custom_variables(module => 'IC',
743 sub_module => 'invoice',
744 trans_id => $invoice_id,
745 configs => $ic_cvar_configs,
747 name_prefix => 'ic_',
748 name_postfix => "_$i",
753 # total payments, don't move we need it here
754 for my $i (1 .. $form->{paidaccounts}) {
755 if ($form->{type} eq "credit_note") {
756 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
758 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
760 $form->{paid} += $form->{"paid_$i"};
761 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
764 my ($tax, $diff) = (0, 0);
766 $netamount = $form->round_amount($netamount, 2);
768 # figure out rounding errors for total amount vs netamount + taxes
769 if ($form->{taxincluded}) {
771 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
772 $diff += $amount - $netamount * $form->{exchangerate};
773 $netamount = $amount;
775 foreach my $item (split(/ /, $form->{taxaccounts})) {
776 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
777 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
778 $tax += $form->{amount}{ $form->{id} }{$item};
779 $netamount -= $form->{amount}{ $form->{id} }{$item};
782 $invoicediff += $diff;
783 ######## this only applies to tax included
784 if ($lastincomeaccno) {
785 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
789 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
790 $diff = $amount - $netamount * $form->{exchangerate};
791 $netamount = $amount;
792 foreach my $item (split(/ /, $form->{taxaccounts})) {
793 $form->{amount}{ $form->{id} }{$item} =
794 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
797 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
800 $amount - $form->{amount}{ $form->{id} }{$item} *
801 $form->{exchangerate};
802 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
803 $tax += $form->{amount}{ $form->{id} }{$item};
807 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
809 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
812 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
814 # update exchangerate
815 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
816 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
817 $form->{exchangerate}, 0);
820 $project_id = conv_i($form->{"globalproject_id"});
821 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
822 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
824 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
825 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
826 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
828 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
830 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
832 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
833 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
834 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
835 do_query($form, $dbh, $query, @values);
836 $form->{amount_cogs}{$trans_id}{$accno} = 0;
840 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
841 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
843 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
845 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
846 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
847 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
848 do_query($form, $dbh, $query, @values);
853 foreach my $trans_id (keys %{ $form->{amount} }) {
854 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
855 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
857 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
859 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
861 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
862 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
865 WHERE chart_id= (SELECT id
869 ORDER BY startdate DESC LIMIT 1),
872 WHERE chart_id= (SELECT id
876 ORDER BY startdate DESC LIMIT 1),
878 (SELECT link FROM chart WHERE accno = ?))|;
879 @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);
880 do_query($form, $dbh, $query, @values);
881 $form->{amount}{$trans_id}{$accno} = 0;
885 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
886 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
888 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
890 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
891 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
894 WHERE chart_id= (SELECT id
898 ORDER BY startdate DESC LIMIT 1),
901 WHERE chart_id= (SELECT id
905 ORDER BY startdate DESC LIMIT 1),
907 (SELECT link FROM chart WHERE accno = ?))|;
908 @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);
909 do_query($form, $dbh, $query, @values);
914 # deduct payment differences from diff
915 for my $i (1 .. $form->{paidaccounts}) {
916 if ($form->{"paid_$i"} != 0) {
918 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
919 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
923 # record payments and offsetting AR
924 if (!$form->{storno}) {
925 for my $i (1 .. $form->{paidaccounts}) {
927 if ($form->{"acc_trans_id_$i"}
929 && (SL::DB::Default->get->payments_changeable == 0)) {
933 next if ($form->{"paid_$i"} == 0);
935 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
936 $form->{"datepaid_$i"} = $form->{invdate}
937 unless ($form->{"datepaid_$i"});
938 $form->{datepaid} = $form->{"datepaid_$i"};
942 if ($form->{currency} eq $defaultcurrency) {
943 $form->{"exchangerate_$i"} = 1;
945 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
946 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
950 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
952 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
954 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
955 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
958 WHERE chart_id= (SELECT id
962 ORDER BY startdate DESC LIMIT 1),
965 WHERE chart_id= (SELECT id
969 ORDER BY startdate DESC LIMIT 1),
971 (SELECT link FROM chart WHERE accno = ?))|;
972 @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});
973 do_query($form, $dbh, $query, @values);
977 $form->{"paid_$i"} *= -1;
978 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
981 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
982 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
985 WHERE chart_id= (SELECT id
989 ORDER BY startdate DESC LIMIT 1),
992 WHERE chart_id= (SELECT id
996 ORDER BY startdate DESC LIMIT 1),
998 (SELECT link FROM chart WHERE accno = ?))|;
999 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1000 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1001 do_query($form, $dbh, $query, @values);
1003 # exchangerate difference
1004 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1005 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1009 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1010 $form->{"exchangerate_$i"};
1012 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1014 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1019 # update exchange rate
1020 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1021 $form->update_exchangerate($dbh, $form->{currency},
1022 $form->{"datepaid_$i"},
1023 $form->{"exchangerate_$i"}, 0);
1027 } else { # if (!$form->{storno})
1028 $form->{marge_total} *= -1;
1031 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1033 # record exchange rate differences and gains/losses
1034 foreach my $accno (keys %{ $form->{fx} }) {
1035 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1036 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1037 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1040 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1041 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1044 WHERE chart_id= (SELECT id
1048 ORDER BY startdate DESC LIMIT 1),
1051 WHERE chart_id= (SELECT id
1055 ORDER BY startdate DESC LIMIT 1),
1057 (SELECT link FROM chart WHERE accno = ?))|;
1058 @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);
1059 do_query($form, $dbh, $query, @values);
1064 if ($payments_only) {
1065 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1066 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1068 $dbh->commit if !$provided_dbh;
1070 $main::lxdebug->leave_sub();
1074 $amount = $netamount + $tax;
1077 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1079 $query = qq|UPDATE ar set
1080 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1081 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1082 amount = ?, netamount = ?, paid = ?,
1083 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1084 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1085 currency_id = (SELECT id FROM currencies WHERE name = ?),
1086 department_id = ?, payment_id = ?, taxincluded = ?,
1087 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1088 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1089 cp_id = ?, marge_total = ?, marge_percent = ?,
1090 globalproject_id = ?, delivery_customer_id = ?,
1091 transaction_description = ?, delivery_vendor_id = ?,
1092 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?
1094 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1095 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1096 $amount, $netamount, $form->{"paid"},
1097 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1098 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1099 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1100 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1101 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1102 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1103 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1104 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1105 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1106 conv_i($form->{"id"}));
1107 do_query($form, $dbh, $query, @values);
1110 if ($form->{storno}) {
1113 paid = paid + amount,
1115 intnotes = ? || intnotes
1117 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1118 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1122 $form->{name} = $form->{customer};
1123 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1125 if (!$form->{shipto_id}) {
1126 $form->add_shipto($dbh, $form->{id}, "AR");
1129 # save printed, emailed and queued
1130 $form->save_status($dbh);
1132 Common::webdav_folder($form);
1134 # Link this record to the records it was created from.
1135 RecordLinks->create_links('dbh' => $dbh,
1137 'from_table' => 'oe',
1138 'from_ids' => $form->{convert_from_oe_ids},
1140 'to_id' => $form->{id},
1142 delete $form->{convert_from_oe_ids};
1144 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1146 if (scalar @convert_from_do_ids) {
1147 DO->close_orders('dbh' => $dbh,
1148 'ids' => \@convert_from_do_ids);
1150 RecordLinks->create_links('dbh' => $dbh,
1152 'from_table' => 'delivery_orders',
1153 'from_ids' => \@convert_from_do_ids,
1155 'to_id' => $form->{id},
1158 delete $form->{convert_from_do_ids};
1160 ARAP->close_orders_if_billed('dbh' => $dbh,
1161 'arap_id' => $form->{id},
1164 # safety check datev export
1165 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1166 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1167 $transdate ||= DateTime->today;
1169 my $datev = SL::DATEV->new(
1170 exporttype => DATEV_ET_BUCHUNGEN,
1171 format => DATEV_FORMAT_KNE,
1175 trans_id => $form->{id},
1180 if ($datev->errors) {
1182 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1187 $dbh->commit if !$provided_dbh;
1189 $main::lxdebug->leave_sub();
1194 sub _delete_payments {
1195 $main::lxdebug->enter_sub();
1197 my ($self, $form, $dbh) = @_;
1199 my @delete_acc_trans_ids;
1201 # Delete old payment entries from acc_trans.
1203 qq|SELECT acc_trans_id
1205 WHERE (trans_id = ?) AND fx_transaction
1209 SELECT at.acc_trans_id
1211 LEFT JOIN chart c ON (at.chart_id = c.id)
1212 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1213 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1216 qq|SELECT at.acc_trans_id
1218 LEFT JOIN chart c ON (at.chart_id = c.id)
1219 WHERE (trans_id = ?)
1220 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1221 ORDER BY at.acc_trans_id
1223 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1225 if (@delete_acc_trans_ids) {
1226 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1227 do_query($form, $dbh, $query);
1230 $main::lxdebug->leave_sub();
1234 $main::lxdebug->enter_sub();
1236 my ($self, $myconfig, $form, $locale) = @_;
1238 # connect to database, turn off autocommit
1239 my $dbh = $form->get_standard_dbh;
1242 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1244 $old_form = save_form();
1246 # Delete all entries in acc_trans from prior payments.
1247 if (SL::DB::Default->get->payments_changeable != 0) {
1248 $self->_delete_payments($form, $dbh);
1251 # Save the new payments the user made before cleaning up $form.
1252 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 };
1254 # Clean up $form so that old content won't tamper the results.
1255 %keep_vars = map { $_, 1 } qw(login password id);
1256 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1258 # Retrieve the invoice from the database.
1259 $self->retrieve_invoice($myconfig, $form);
1261 # Set up the content of $form in the way that IS::post_invoice() expects.
1262 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1264 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1265 $item = $form->{invoice_details}->[$row - 1];
1267 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1269 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1272 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1274 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1276 # Restore the payment options from the user input.
1277 map { $form->{$_} = $payments{$_} } keys %payments;
1279 # Get the AR accno (which is normally done by Form::create_links()).
1283 LEFT JOIN chart c ON (at.chart_id = c.id)
1284 WHERE (trans_id = ?)
1285 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1286 ORDER BY at.acc_trans_id
1289 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1291 # Post the new payments.
1292 $self->post_invoice($myconfig, $form, $dbh, 1);
1294 restore_form($old_form);
1296 my $rc = $dbh->commit();
1298 $main::lxdebug->leave_sub();
1303 sub process_assembly {
1304 $main::lxdebug->enter_sub();
1306 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1309 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1310 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1312 JOIN parts p ON (a.parts_id = p.id)
1314 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1316 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1320 $ref->{inventory_accno_id} *= 1;
1321 $ref->{expense_accno_id} *= 1;
1323 # multiply by number of assemblies
1324 $ref->{qty} *= $totalqty;
1326 if ($ref->{assembly}) {
1327 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1330 if ($ref->{inventory_accno_id}) {
1331 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1335 # save detail record for individual assembly item in invoice table
1337 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1338 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1339 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1340 do_query($form, $dbh, $query, @values);
1346 $main::lxdebug->leave_sub();
1350 $main::lxdebug->enter_sub();
1352 # adjust allocated in table invoice according to FIFO princicple
1353 # for a certain part with part_id $id
1355 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1359 $form->{taxzone_id} *=1;
1360 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1361 my $taxzone_id = $form->{"taxzone_id"} * 1;
1363 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1364 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1365 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1366 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1367 FROM invoice i, parts p
1368 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1369 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1370 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1371 WHERE (i.parts_id = p.id)
1372 AND (i.parts_id = ?)
1373 AND ((i.base_qty + i.allocated) < 0)
1375 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1380 # all invoice entries of an example part:
1382 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1383 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1384 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1385 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1386 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1387 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1388 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1390 # 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
1391 # and all parts have been allocated
1393 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1394 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1396 # in this example there are still 4 unsold articles
1399 # search all invoice entries for the part in question, adjusting "allocated"
1400 # until the total number of sold parts has been reached
1402 # ORDER BY trans_id ensures FIFO
1405 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1406 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1410 # update allocated in invoice
1411 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1413 # total expenses and inventory
1414 # sellprice is the cost of the item
1415 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1417 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1418 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1419 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1421 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1422 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1423 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1425 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1426 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1432 last if (($totalqty -= $qty) <= 0);
1437 $main::lxdebug->leave_sub();
1442 sub reverse_invoice {
1443 $main::lxdebug->enter_sub();
1445 my ($dbh, $form) = @_;
1447 # reverse inventory items
1449 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1451 JOIN parts p ON (i.parts_id = p.id)
1452 WHERE i.trans_id = ?|;
1453 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1455 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1457 if ($ref->{inventory_accno_id}) {
1458 # de-allocated purchases
1460 qq|SELECT i.id, i.trans_id, i.allocated
1462 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1463 ORDER BY i.trans_id DESC|;
1464 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1466 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1467 my $qty = $ref->{qty};
1468 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1469 $qty = $inhref->{allocated};
1473 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1475 last if (($ref->{qty} -= $qty) <= 0);
1484 my @values = (conv_i($form->{id}));
1485 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1486 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1487 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1489 $main::lxdebug->leave_sub();
1492 sub delete_invoice {
1493 $main::lxdebug->enter_sub();
1495 my ($self, $myconfig, $form) = @_;
1497 # connect to database
1498 my $dbh = $form->get_standard_dbh;
1501 &reverse_invoice($dbh, $form);
1503 my @values = (conv_i($form->{id}));
1505 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1506 # zurückgesetzt werden. Vgl:
1507 # id | storno | storno_id | paid | amount
1508 #----+--------+-----------+---------+-----------
1509 # 18 | f | | 0.00000 | 119.00000
1511 # 18 | t | | 119.00000 | 119.00000
1513 if($form->{storno}){
1514 # storno_id auslesen und korrigieren
1515 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1516 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1519 # delete spool files
1520 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1523 qq|DELETE FROM status WHERE trans_id = ?|,
1524 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1525 qq|DELETE FROM ar WHERE id = ?|,
1528 map { do_query($form, $dbh, $_, @values) } @queries;
1530 my $rc = $dbh->commit;
1533 my $spool = $::lx_office_conf{paths}->{spool};
1534 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1537 $main::lxdebug->leave_sub();
1542 sub retrieve_invoice {
1543 $main::lxdebug->enter_sub();
1545 my ($self, $myconfig, $form) = @_;
1547 # connect to database
1548 my $dbh = $form->get_standard_dbh;
1550 my ($sth, $ref, $query);
1552 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1556 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1557 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1558 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1559 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1560 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1564 $ref = selectfirst_hashref_query($form, $dbh, $query);
1565 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1568 my $id = conv_i($form->{id});
1571 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1575 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1576 a.orddate, a.quodate, a.globalproject_id,
1577 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1578 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1579 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1580 a.employee_id, a.salesman_id, a.payment_id,
1581 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1582 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1583 a.marge_total, a.marge_percent, a.direct_debit,
1586 LEFT JOIN employee e ON (e.id = a.employee_id)
1588 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1589 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1591 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1594 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1595 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1597 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1599 foreach my $vc (qw(customer vendor)) {
1600 next if !$form->{"delivery_${vc}_id"};
1601 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1604 # get printed, emailed
1605 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1606 $sth = prepare_execute_query($form, $dbh, $query, $id);
1608 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1609 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1610 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1611 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1614 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1616 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1617 : $form->{invdate} ? $dbh->quote($form->{invdate})
1621 my $taxzone_id = $form->{taxzone_id} *= 1;
1622 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1624 # retrieve individual items
1627 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1628 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1629 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1632 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1633 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1634 i.price_factor_id, i.price_factor, i.marge_price_factor,
1635 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1636 pr.projectnumber, pg.partsgroup, prg.pricegroup
1639 LEFT JOIN parts p ON (i.parts_id = p.id)
1640 LEFT JOIN project pr ON (i.project_id = pr.id)
1641 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1642 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1644 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1645 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1646 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1648 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1650 $sth = prepare_execute_query($form, $dbh, $query, $id);
1652 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1653 # Retrieve custom variables.
1654 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1656 sub_module => 'invoice',
1657 trans_id => $ref->{invoice_id},
1659 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1660 delete $ref->{invoice_id};
1662 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1663 delete($ref->{"part_inventory_accno_id"});
1665 foreach my $type (qw(inventory income expense)) {
1666 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1667 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1668 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1672 # get tax rates and description
1673 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1675 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1676 LEFT JOIN chart c ON (c.id = t.chart_id)
1678 (SELECT tk.tax_id FROM taxkeys tk
1679 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1680 AND startdate <= date($transdate)
1681 ORDER BY startdate DESC LIMIT 1)
1683 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1684 $ref->{taxaccounts} = "";
1686 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1688 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1692 $ref->{taxaccounts} .= "$ptr->{accno} ";
1694 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1695 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1696 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1697 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1698 $form->{taxaccounts} .= "$ptr->{accno} ";
1703 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1705 chop $ref->{taxaccounts};
1706 push @{ $form->{invoice_details} }, $ref;
1711 Common::webdav_folder($form);
1714 my $rc = $dbh->commit;
1716 $main::lxdebug->leave_sub();
1722 $main::lxdebug->enter_sub();
1724 my ($self, $myconfig, $form) = @_;
1726 # connect to database
1727 my $dbh = $form->get_standard_dbh;
1729 my $dateformat = $myconfig->{dateformat};
1730 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1732 my (@values, $duedate, $ref, $query);
1734 if ($form->{invdate}) {
1735 $duedate = "to_date(?, '$dateformat')";
1736 push @values, $form->{invdate};
1738 $duedate = "current_date";
1741 my $cid = conv_i($form->{customer_id});
1744 if ($form->{payment_id}) {
1745 $payment_id = "(pt.id = ?) OR";
1746 push @values, conv_i($form->{payment_id});
1752 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1753 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1754 c.street, c.zipcode, c.city, c.country,
1755 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1756 c.taxincluded_checked, c.direct_debit,
1757 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1758 b.discount AS tradediscount, b.description AS business
1760 LEFT JOIN business b ON (b.id = c.business_id)
1761 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1762 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1765 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1767 delete $ref->{salesman_id} if !$ref->{salesman_id};
1769 map { $form->{$_} = $ref->{$_} } keys %$ref;
1771 # use customer currency
1772 $form->{currency} = $form->{curr};
1775 qq|SELECT sum(amount - paid) AS dunning_amount
1777 WHERE (paid < amount)
1778 AND (customer_id = ?)
1779 AND (dunning_config_id IS NOT NULL)|;
1780 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1781 map { $form->{$_} = $ref->{$_} } keys %$ref;
1784 qq|SELECT dnn.dunning_description AS max_dunning_level
1785 FROM dunning_config dnn
1786 WHERE id IN (SELECT dunning_config_id
1788 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1789 ORDER BY dunning_level DESC LIMIT 1|;
1790 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1791 map { $form->{$_} = $ref->{$_} } keys %$ref;
1793 $form->{creditremaining} = $form->{creditlimit};
1794 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1795 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1796 $form->{creditremaining} -= $value;
1800 (SELECT e.buy FROM exchangerate e
1801 WHERE e.currency_id = o.currency_id
1802 AND e.transdate = o.transdate)
1804 WHERE o.customer_id = ?
1805 AND o.quotation = '0'
1806 AND o.closed = '0'|;
1807 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1809 while (my ($amount, $exch) = $sth->fetchrow_array) {
1810 $exch = 1 unless $exch;
1811 $form->{creditremaining} -= $amount * $exch;
1815 # get shipto if we did not converted an order or invoice
1816 if (!$form->{shipto}) {
1817 map { delete $form->{$_} }
1818 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1819 shiptostreet shiptozipcode shiptocity shiptocountry
1820 shiptocontact shiptophone shiptofax shiptoemail);
1822 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1823 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1825 map { $form->{$_} = $ref->{$_} } keys %$ref;
1828 # setup last accounts used for this customer
1829 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1831 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1833 JOIN acc_trans ac ON (ac.chart_id = c.id)
1834 JOIN ar a ON (a.id = ac.trans_id)
1835 WHERE a.customer_id = ?
1836 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1837 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1838 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1841 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1842 if ($ref->{category} eq 'I') {
1844 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1846 if ($form->{initial_transdate}) {
1848 qq|SELECT tk.tax_id, t.rate
1850 LEFT JOIN tax t ON tk.tax_id = t.id
1851 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1852 ORDER BY tk.startdate DESC
1854 my ($tax_id, $rate) =
1855 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1856 $form->{initial_transdate});
1857 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1860 if ($ref->{category} eq 'A') {
1861 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1865 $form->{rowcount} = $i if ($i && !$form->{type});
1868 $main::lxdebug->leave_sub();
1872 $main::lxdebug->enter_sub();
1874 my ($self, $myconfig, $form) = @_;
1876 # connect to database
1877 my $dbh = $form->get_standard_dbh;
1879 my $i = $form->{rowcount};
1881 my $where = qq|NOT p.obsolete = '1'|;
1884 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1885 my ($table, $field) = split m/\./, $column;
1886 next if !$form->{"${field}_${i}"};
1887 $where .= qq| AND lower(${column}) ILIKE ?|;
1888 push @values, '%' . $form->{"${field}_${i}"} . '%';
1891 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1892 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1893 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1894 push @values, $form->{"partnumber_$i"};
1897 # Search for part ID overrides all other criteria.
1898 if ($form->{"id_${i}"}) {
1899 $where = qq|p.id = ?|;
1900 @values = ($form->{"id_${i}"});
1903 if ($form->{"description_$i"}) {
1904 $where .= qq| ORDER BY p.description|;
1906 $where .= qq| ORDER BY p.partnumber|;
1910 if ($form->{type} eq "invoice") {
1912 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1913 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1917 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1921 my $taxzone_id = $form->{taxzone_id} * 1;
1922 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1926 p.id, p.partnumber, p.description, p.sellprice,
1927 p.listprice, p.inventory_accno_id, p.lastcost,
1929 c1.accno AS inventory_accno,
1930 c1.new_chart_id AS inventory_new_chart,
1931 date($transdate) - c1.valid_from AS inventory_valid,
1933 c2.accno AS income_accno,
1934 c2.new_chart_id AS income_new_chart,
1935 date($transdate) - c2.valid_from AS income_valid,
1937 c3.accno AS expense_accno,
1938 c3.new_chart_id AS expense_new_chart,
1939 date($transdate) - c3.valid_from AS expense_valid,
1941 p.unit, p.assembly, p.bin, p.onhand,
1942 p.notes AS partnotes, p.notes AS longdescription,
1943 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1946 pfac.factor AS price_factor,
1951 LEFT JOIN chart c1 ON
1952 ((SELECT inventory_accno_id
1953 FROM buchungsgruppen
1954 WHERE id = p.buchungsgruppen_id) = c1.id)
1955 LEFT JOIN chart c2 ON
1956 ((SELECT income_accno_id_${taxzone_id}
1957 FROM buchungsgruppen
1958 WHERE id = p.buchungsgruppen_id) = c2.id)
1959 LEFT JOIN chart c3 ON
1960 ((SELECT expense_accno_id_${taxzone_id}
1961 FROM buchungsgruppen
1962 WHERE id = p.buchungsgruppen_id) = c3.id)
1963 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1964 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1966 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1968 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1970 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1971 [ qq|SELECT tr.translation, tr.longdescription
1973 WHERE tr.language_id IN
1976 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1979 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1981 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1983 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1984 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1985 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1986 if (!$ref->{inventory_accno_id}) {
1987 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1989 delete($ref->{inventory_accno_id});
1991 foreach my $type (qw(inventory income expense)) {
1992 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1994 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1997 ($ref->{"${type}_accno"},
1998 $ref->{"${type}_new_chart"},
1999 $ref->{"${type}_valid"})
2000 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2004 if ($form->{payment_id} eq "") {
2005 $form->{payment_id} = $form->{part_payment_id};
2008 # get tax rates and description
2009 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2011 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2013 LEFT JOIN chart c ON (c.id = t.chart_id)
2017 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2019 ORDER BY startdate DESC
2022 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2023 my $stw = $dbh->prepare($query);
2024 $stw->execute(@values) || $form->dberror($query);
2026 $ref->{taxaccounts} = "";
2028 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2030 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2034 $ref->{taxaccounts} .= "$ptr->{accno} ";
2036 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2037 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2038 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2039 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2040 $form->{taxaccounts} .= "$ptr->{accno} ";
2046 chop $ref->{taxaccounts};
2048 if ($form->{language_id}) {
2049 for my $spec (@translation_queries) {
2050 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2051 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2052 next unless $translation;
2053 $ref->{description} = $translation;
2054 $ref->{longdescription} = $longdescription;
2059 $ref->{onhand} *= 1;
2061 push @{ $form->{item_list} }, $ref;
2064 $_->[1]->finish for @translation_queries;
2066 foreach my $item (@{ $form->{item_list} }) {
2067 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2068 trans_id => $item->{id},
2072 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2075 $main::lxdebug->leave_sub();
2078 ##########################
2079 # get pricegroups from database
2080 # build up selected pricegroup
2081 # if an exchange rate - change price
2084 sub get_pricegroups_for_parts {
2086 $main::lxdebug->enter_sub();
2088 my ($self, $myconfig, $form) = @_;
2090 my $dbh = $form->get_standard_dbh;
2092 $form->{"PRICES"} = {};
2096 my $all_units = AM->retrieve_units($myconfig, $form);
2097 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2098 $form->{"PRICES"}{$i} = [];
2100 $id = $form->{"id_$i"};
2102 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2103 $id = $form->{"new_id_$i"};
2106 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2108 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2110 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2111 my $sellprice = $form->{"sellprice_$i"};
2112 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2113 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2114 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2116 my $price_new = $form->{"price_new_$i"};
2117 my $price_old = $form->{"price_old_$i"};
2119 if (!$form->{"unit_old_$i"}) {
2120 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2121 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2122 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2123 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2126 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2127 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2128 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2130 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2131 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2132 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2133 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2134 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2135 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2136 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2141 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2142 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2143 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2144 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2145 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2149 if (!$form->{"basefactor_$i"}) {
2150 $form->{"basefactor_$i"} = 1;
2156 sellprice AS default_sellprice,
2159 'selected' AS selected
2165 parts.sellprice AS default_sellprice,
2166 pricegroup.pricegroup,
2170 LEFT JOIN parts ON parts.id = parts_id
2171 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2173 ORDER BY pricegroup|;
2174 my @values = (conv_i($id), conv_i($id));
2175 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2177 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2179 $pkr->{selected} = '';
2181 # if there is an exchange rate change price
2182 if (($form->{exchangerate} * 1) != 0) {
2183 $pkr->{price} /= $form->{exchangerate};
2186 $pkr->{price} *= $form->{"basefactor_$i"};
2187 $pkr->{price} *= $basefactor;
2188 $pkr->{price_ufmt} = $pkr->{price};
2189 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2191 if (!defined $selectedpricegroup_id) {
2192 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2193 # Case A: open old invoice, no pricegroup selected
2194 # Case B: add new article to invoice, no pricegroup selected
2196 # to distinguish case A and B the variable pricegroup_id_$i is used
2197 # for new articles this variable isn't defined, for loaded articles it is
2198 # sellprice can't be used, as it already has 0,00 set
2200 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2202 $pkr->{selected} = ' selected';
2203 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2204 and not defined $form->{"pricegroup_id_$i"}
2205 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2206 # for the case where pricegroup prices haven't been set
2208 # Case B: use default pricegroup of customer
2210 $pkr->{selected} = ' selected'; # unless $form->{selected};
2211 # no customer pricesgroup set
2212 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2214 $pkr->{price} = $form->{"sellprice_$i"};
2218 # this sub should not set anything and only return. --sschoeling, 20090506
2219 # is this correct? put in again... -- grichardson 20110119
2220 $form->{"sellprice_$i"} = $pkr->{price};
2223 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2224 $pkr->{price} = $form->{"sellprice_$i"};
2225 $pkr->{selected} = ' selected';
2229 # existing article: pricegroup or price changed
2230 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2231 if ($selectedpricegroup_id ne $pricegroup_old) {
2232 # pricegroup has changed
2233 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2234 $pkr->{selected} = ' selected';
2236 } elsif ( ($form->parse_amount($myconfig, $price_new)
2237 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2238 and ($price_new ne 0) and defined $price_new) {
2239 # sellprice has changed
2240 # when loading existing invoices $price_new is NULL
2241 if ($pkr->{pricegroup_id} == 0) {
2242 $pkr->{price} = $form->{"sellprice_$i"};
2243 $pkr->{selected} = ' selected';
2245 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2246 # neither sellprice nor pricegroup changed
2247 $pkr->{selected} = ' selected';
2248 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2249 # $pkr->{price} = $form->{"sellprice_$i"};
2251 $pkr->{price} = $form->{"sellprice_$i"};
2255 push @{ $form->{PRICES}{$i} }, $pkr;
2258 $form->{"basefactor_$i"} *= $basefactor;
2265 $main::lxdebug->leave_sub();
2269 $main::lxdebug->enter_sub();
2271 my ($self, $myconfig, $form, $table) = @_;
2273 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2275 # make sure there's no funny stuff in $table
2276 # ToDO: die when this happens and throw an error
2277 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2279 my $dbh = $form->get_standard_dbh;
2281 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2282 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2284 $main::lxdebug->leave_sub();
2290 $main::lxdebug->enter_sub();
2292 my ($self, $myconfig, $form, $table, $id) = @_;
2294 $main::lxdebug->leave_sub() and return 0 unless ($id);
2296 # make sure there's no funny stuff in $table
2297 # ToDO: die when this happens and throw an error
2298 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2300 my $dbh = $form->get_standard_dbh;
2302 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2303 my ($result) = selectrow_query($form, $dbh, $query, $id);
2305 $main::lxdebug->leave_sub();
2310 sub get_standard_accno_current_assets {
2311 $main::lxdebug->enter_sub();
2313 my ($self, $myconfig, $form) = @_;
2315 my $dbh = $form->get_standard_dbh;
2317 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2318 my ($result) = selectrow_query($form, $dbh, $query);
2320 $main::lxdebug->leave_sub();