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;
47 use SL::HTML::Restrict;
60 $main::lxdebug->enter_sub();
62 my ($self, $myconfig, $form, $locale) = @_;
64 $form->{duedate} ||= $form->{invdate};
67 my $dbh = $form->get_standard_dbh;
70 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
71 ($form->{terms}) = selectrow_query($form, $dbh, $query);
74 $form->{TEMPLATE_ARRAYS} = {};
76 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
78 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
81 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
82 $price_factors{$pfac->{id}} = $pfac;
84 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
87 # sort items by partsgroup
88 for my $i (1 .. $form->{rowcount}) {
90 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
91 # $partsgroup = $form->{"partsgroup_$i"};
93 # push @partsgroup, [$i, $partsgroup];
94 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
100 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
101 %projects_by_id = map { $_->id => $_ } @$projects;
104 if ($projects_by_id{$form->{"globalproject_id"}}) {
105 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
106 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
108 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
109 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
119 # sort items by partsgroup
120 for $i (1 .. $form->{rowcount}) {
122 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
123 $partsgroup = $form->{"partsgroup_$i"};
125 push @partsgroup, [$i, $partsgroup];
138 my $nodiscount_subtotal = 0;
139 my $discount_subtotal = 0;
141 my $subtotal_header = 0;
144 $form->{discount} = [];
146 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
148 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
149 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe donumber_do transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup weight lineweight);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
160 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
162 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
164 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
166 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
169 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
172 if ($item->[1] ne $sameitem) {
173 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
174 $sameitem = $item->[1];
176 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
179 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
181 if ($form->{"id_$i"} != 0) {
183 # add number, description and qty to $form->{number},
184 if ($form->{"subtotal_$i"} && !$subtotal_header) {
185 $subtotal_header = $i;
186 $position = int($position);
189 } elsif ($subtotal_header) {
191 $position = int($position);
192 $position = $position.".".$subposition;
194 $position = int($position);
198 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
200 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
201 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
208 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
213 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$i"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
217 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
219 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
220 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
221 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
222 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
224 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
225 my ($dec) = ($sellprice =~ /\.(\d+)/);
226 my $decimalplaces = max 2, length($dec);
228 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
230 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
231 my $linetotal = $form->round_amount($linetotal_exact, 2);
233 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
234 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
236 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
238 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
240 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
242 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
243 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
245 $linetotal = ($linetotal != 0) ? $linetotal : '';
247 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
248 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
249 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
251 $form->{total} += $linetotal;
252 $form->{nodiscount_total} += $nodiscount_linetotal;
253 $form->{discount_total} += $discount;
255 if ($subtotal_header) {
256 $discount_subtotal += $linetotal;
257 $nodiscount_subtotal += $nodiscount_linetotal;
260 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
261 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
262 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
263 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
264 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
266 $discount_subtotal = 0;
267 $nodiscount_subtotal = 0;
268 $subtotal_header = 0;
271 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
274 if (!$form->{"discount_$i"}) {
275 $nodiscount += $linetotal;
278 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
279 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
280 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
281 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
283 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
285 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
286 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
288 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
289 $totalweight += $lineweight;
290 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
291 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
292 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
293 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
295 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
299 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
301 if ($form->{taxincluded}) {
304 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
305 $taxbase = $linetotal - $taxamount;
307 $taxamount = $linetotal * $taxrate;
308 $taxbase = $linetotal;
311 if ($form->round_amount($taxrate, 7) == 0) {
312 if ($form->{taxincluded}) {
313 foreach my $accno (@taxaccounts) {
314 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
316 $taxaccounts{$accno} += $taxamount;
317 $taxdiff += $taxamount;
319 $taxbase{$accno} += $taxbase;
321 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
323 foreach my $accno (@taxaccounts) {
324 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
325 $taxbase{$accno} += $taxbase;
329 foreach my $accno (@taxaccounts) {
330 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
331 $taxbase{$accno} += $taxbase;
334 my $tax_rate = $taxrate * 100;
335 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
336 if ($form->{"assembly_$i"}) {
339 # get parts and push them onto the stack
341 if ($form->{groupitems}) {
343 qq|ORDER BY pg.partsgroup, a.oid|;
345 $sortorder = qq|ORDER BY a.oid|;
349 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
351 JOIN parts p ON (a.parts_id = p.id)
352 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
353 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
354 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
356 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
357 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
358 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
359 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
360 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
363 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
365 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
366 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
368 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
369 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
375 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
376 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
377 for @{ $ic_cvar_configs };
379 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
383 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
384 $form->{totalweight_nofmt} = $totalweight;
385 my $defaults = AM->get_defaults();
386 $form->{weightunit} = $defaults->{weightunit};
388 foreach my $item (sort keys %taxaccounts) {
389 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
391 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
392 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
393 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
394 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
395 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
396 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
397 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
399 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
400 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
401 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
404 for my $i (1 .. $form->{paidaccounts}) {
405 if ($form->{"paid_$i"}) {
406 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
408 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
409 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
410 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
411 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
412 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
414 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
417 if($form->{taxincluded}) {
418 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
419 $form->{subtotal_nofmt} = $form->{total} - $tax;
422 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
423 $form->{subtotal_nofmt} = $form->{total};
426 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
427 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
428 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
429 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
431 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
432 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
434 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
435 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
437 $form->set_payment_options($myconfig, $form->{invdate});
439 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
440 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
442 $form->{username} = $myconfig->{name};
444 $main::lxdebug->leave_sub();
447 sub project_description {
448 $main::lxdebug->enter_sub();
450 my ($self, $dbh, $id) = @_;
451 my $form = \%main::form;
453 my $query = qq|SELECT description FROM project WHERE id = ?|;
454 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
456 $main::lxdebug->leave_sub();
461 sub customer_details {
462 $main::lxdebug->enter_sub();
464 my ($self, $myconfig, $form, @wanted_vars) = @_;
466 # connect to database
467 my $dbh = $form->get_standard_dbh;
469 my $language_id = $form->{language_id};
471 # get contact id, set it if nessessary
474 my @values = (conv_i($form->{customer_id}));
477 if ($form->{cp_id}) {
478 $where = qq| AND (cp.cp_id = ?) |;
479 push(@values, conv_i($form->{cp_id}));
482 # get rest for the customer
484 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
485 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
488 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
489 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
490 WHERE (ct.id = ?) $where
493 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
495 # remove id and taxincluded before copy back
496 delete @$ref{qw(id taxincluded)};
498 @wanted_vars = grep({ $_ } @wanted_vars);
499 if (scalar(@wanted_vars) > 0) {
501 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
502 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
505 map { $form->{$_} = $ref->{$_} } keys %$ref;
507 if ($form->{delivery_customer_id}) {
509 qq|SELECT *, notes as customernotes
513 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
515 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
518 if ($form->{delivery_vendor_id}) {
520 qq|SELECT *, notes as customernotes
524 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
526 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
529 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
531 'trans_id' => $form->{customer_id});
532 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
534 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
535 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
536 'language_id' => $language_id,
537 'allow_fallback' => 1);
540 $main::lxdebug->leave_sub();
544 $main::lxdebug->enter_sub();
546 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
548 # connect to database, turn off autocommit
549 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
550 my $restricter = SL::HTML::Restrict->create;
552 my ($query, $sth, $null, $project_id, @values);
553 my $exchangerate = 0;
555 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
558 if (!$form->{employee_id}) {
559 $form->get_employee($dbh);
562 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
563 my $defaultcurrency = $form->{defaultcurrency};
565 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
566 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
567 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
568 if (!$form->{department_id}){
569 ($null, $form->{department_id}) = split(/--/, $form->{department});
572 my $all_units = AM->retrieve_units($myconfig, $form);
574 if (!$payments_only) {
576 &reverse_invoice($dbh, $form);
579 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
580 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
582 $query = qq|SELECT nextval('glid')|;
583 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
585 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
586 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
588 if (!$form->{invnumber}) {
589 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
590 $form->{invnumber} = $trans_number->create_unique;
595 my ($netamount, $invoicediff) = (0, 0);
596 my ($amount, $linetotal, $lastincomeaccno);
598 if ($form->{currency} eq $defaultcurrency) {
599 $form->{exchangerate} = 1;
601 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
604 $form->{exchangerate} =
607 : $form->parse_amount($myconfig, $form->{exchangerate});
609 $form->{expense_inventory} = "";
613 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
614 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
617 $form->{amount} = {};
618 $form->{amount_cogs} = {};
620 foreach my $i (1 .. $form->{rowcount}) {
621 if ($form->{type} eq "credit_note") {
622 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
623 $form->{shipped} = 1;
625 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
630 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
631 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
632 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
634 if ($form->{storno}) {
635 $form->{"qty_$i"} *= -1;
638 if ($form->{"id_$i"}) {
641 if (defined($baseunits{$form->{"id_$i"}})) {
642 $item_unit = $baseunits{$form->{"id_$i"}};
645 $query = qq|SELECT unit FROM parts WHERE id = ?|;
646 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
647 $baseunits{$form->{"id_$i"}} = $item_unit;
650 if (defined($all_units->{$item_unit}->{factor})
651 && ($all_units->{$item_unit}->{factor} ne '')
652 && ($all_units->{$item_unit}->{factor} != 0)) {
653 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
657 $baseqty = $form->{"qty_$i"} * $basefactor;
659 my ($allocated, $taxrate) = (0, 0);
663 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
665 # keep entered selling price
667 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
669 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
671 my $decimalplaces = ($dec > 2) ? $dec : 2;
673 # undo discount formatting
674 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
677 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
679 # round linetotal to 2 decimal places
680 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
681 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
683 if ($form->{taxincluded}) {
684 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
685 $form->{"sellprice_$i"} =
686 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
688 $taxamount = $linetotal * $taxrate;
691 $netamount += $linetotal;
693 if ($taxamount != 0) {
695 $form->{amount}{ $form->{id} }{$_} +=
696 $taxamount * $form->{"${_}_rate"} / $taxrate
697 } split(/ /, $form->{"taxaccounts_$i"});
700 # add amount to income, $form->{amount}{trans_id}{accno}
701 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
703 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
704 $linetotal = $form->round_amount($linetotal, 2);
706 # this is the difference from the inventory
707 $invoicediff += ($amount - $linetotal);
709 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
712 $lastincomeaccno = $form->{"income_accno_$i"};
714 # adjust and round sellprice
715 $form->{"sellprice_$i"} =
716 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
719 next if $payments_only;
721 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
723 if ($form->{"assembly_$i"}) {
724 # record assembly item as allocated
725 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
728 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
732 # Get pricegroup_id and save it. Unfortunately the interface
733 # also uses ID "0" for signalling that none is selected, but "0"
734 # must not be stored in the database. Therefore we cannot simply
736 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
738 $pricegroup_id = undef if !$pricegroup_id;
740 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
742 # save detail record in invoice table
744 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
745 sellprice, fxsellprice, discount, allocated, assemblyitem,
746 unit, deliverydate, project_id, serialnumber, pricegroup_id,
747 ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
748 marge_percent, marge_total, lastcost,
749 price_factor_id, price_factor, marge_price_factor)
750 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
751 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
753 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
754 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
755 $form->{"sellprice_$i"}, $fxsellprice,
756 $form->{"discount_$i"}, $allocated, 'f',
757 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
758 $form->{"serialnumber_$i"}, $pricegroup_id,
759 $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
760 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
761 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
762 $form->{"lastcost_$i"},
763 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
764 conv_i($form->{"marge_price_factor_$i"}));
765 do_query($form, $dbh, $query, @values);
767 CVar->save_custom_variables(module => 'IC',
768 sub_module => 'invoice',
769 trans_id => $invoice_id,
770 configs => $ic_cvar_configs,
772 name_prefix => 'ic_',
773 name_postfix => "_$i",
778 # total payments, don't move we need it here
779 for my $i (1 .. $form->{paidaccounts}) {
780 if ($form->{type} eq "credit_note") {
781 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
783 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
785 $form->{paid} += $form->{"paid_$i"};
786 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
789 my ($tax, $diff) = (0, 0);
791 $netamount = $form->round_amount($netamount, 2);
793 # figure out rounding errors for total amount vs netamount + taxes
794 if ($form->{taxincluded}) {
796 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
797 $diff += $amount - $netamount * $form->{exchangerate};
798 $netamount = $amount;
800 foreach my $item (split(/ /, $form->{taxaccounts})) {
801 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
802 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
803 $tax += $form->{amount}{ $form->{id} }{$item};
804 $netamount -= $form->{amount}{ $form->{id} }{$item};
807 $invoicediff += $diff;
808 ######## this only applies to tax included
809 if ($lastincomeaccno) {
810 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
814 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
815 $diff = $amount - $netamount * $form->{exchangerate};
816 $netamount = $amount;
817 foreach my $item (split(/ /, $form->{taxaccounts})) {
818 $form->{amount}{ $form->{id} }{$item} =
819 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
822 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
825 $amount - $form->{amount}{ $form->{id} }{$item} *
826 $form->{exchangerate};
827 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
828 $tax += $form->{amount}{ $form->{id} }{$item};
832 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
834 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
837 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
839 # update exchangerate
840 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
841 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
842 $form->{exchangerate}, 0);
845 $project_id = conv_i($form->{"globalproject_id"});
846 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
847 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
849 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
850 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
851 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
853 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
855 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
857 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
858 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
859 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
860 do_query($form, $dbh, $query, @values);
861 $form->{amount_cogs}{$trans_id}{$accno} = 0;
865 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
866 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
868 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
870 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
871 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
872 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
873 do_query($form, $dbh, $query, @values);
878 foreach my $trans_id (keys %{ $form->{amount} }) {
879 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
880 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
882 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
884 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
886 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
887 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
890 WHERE chart_id= (SELECT id
894 ORDER BY startdate DESC LIMIT 1),
897 WHERE chart_id= (SELECT id
901 ORDER BY startdate DESC LIMIT 1),
903 (SELECT link FROM chart WHERE accno = ?))|;
904 @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);
905 do_query($form, $dbh, $query, @values);
906 $form->{amount}{$trans_id}{$accno} = 0;
910 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
911 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
913 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
915 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
916 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
919 WHERE chart_id= (SELECT id
923 ORDER BY startdate DESC LIMIT 1),
926 WHERE chart_id= (SELECT id
930 ORDER BY startdate DESC LIMIT 1),
932 (SELECT link FROM chart WHERE accno = ?))|;
933 @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);
934 do_query($form, $dbh, $query, @values);
939 # deduct payment differences from diff
940 for my $i (1 .. $form->{paidaccounts}) {
941 if ($form->{"paid_$i"} != 0) {
943 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
944 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
948 # record payments and offsetting AR
949 if (!$form->{storno}) {
950 for my $i (1 .. $form->{paidaccounts}) {
952 if ($form->{"acc_trans_id_$i"}
954 && (SL::DB::Default->get->payments_changeable == 0)) {
958 next if ($form->{"paid_$i"} == 0);
960 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
961 $form->{"datepaid_$i"} = $form->{invdate}
962 unless ($form->{"datepaid_$i"});
963 $form->{datepaid} = $form->{"datepaid_$i"};
967 if ($form->{currency} eq $defaultcurrency) {
968 $form->{"exchangerate_$i"} = 1;
970 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
971 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
975 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
977 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
979 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
980 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
983 WHERE chart_id= (SELECT id
987 ORDER BY startdate DESC LIMIT 1),
990 WHERE chart_id= (SELECT id
994 ORDER BY startdate DESC LIMIT 1),
996 (SELECT link FROM chart WHERE accno = ?))|;
997 @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});
998 do_query($form, $dbh, $query, @values);
1002 $form->{"paid_$i"} *= -1;
1003 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1006 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1007 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1010 WHERE chart_id= (SELECT id
1014 ORDER BY startdate DESC LIMIT 1),
1017 WHERE chart_id= (SELECT id
1021 ORDER BY startdate DESC LIMIT 1),
1023 (SELECT link FROM chart WHERE accno = ?))|;
1024 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1025 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1026 do_query($form, $dbh, $query, @values);
1028 # exchangerate difference
1029 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1030 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1034 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1035 $form->{"exchangerate_$i"};
1037 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1039 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1044 # update exchange rate
1045 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1046 $form->update_exchangerate($dbh, $form->{currency},
1047 $form->{"datepaid_$i"},
1048 $form->{"exchangerate_$i"}, 0);
1052 } else { # if (!$form->{storno})
1053 $form->{marge_total} *= -1;
1056 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1058 # record exchange rate differences and gains/losses
1059 foreach my $accno (keys %{ $form->{fx} }) {
1060 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1061 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1062 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1065 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1066 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1069 WHERE chart_id= (SELECT id
1073 ORDER BY startdate DESC LIMIT 1),
1076 WHERE chart_id= (SELECT id
1080 ORDER BY startdate DESC LIMIT 1),
1082 (SELECT link FROM chart WHERE accno = ?))|;
1083 @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);
1084 do_query($form, $dbh, $query, @values);
1089 if ($payments_only) {
1090 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1091 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1093 $dbh->commit if !$provided_dbh;
1095 $main::lxdebug->leave_sub();
1099 $amount = $netamount + $tax;
1102 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1104 $query = qq|UPDATE ar set
1105 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1106 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1107 amount = ?, netamount = ?, paid = ?,
1108 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1109 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1110 currency_id = (SELECT id FROM currencies WHERE name = ?),
1111 department_id = ?, payment_id = ?, taxincluded = ?,
1112 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1113 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1114 cp_id = ?, marge_total = ?, marge_percent = ?,
1115 globalproject_id = ?, delivery_customer_id = ?,
1116 transaction_description = ?, delivery_vendor_id = ?,
1117 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1118 delivery_term_id = ?
1120 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1121 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1122 $amount, $netamount, $form->{"paid"},
1123 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1124 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1125 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1126 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1127 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1128 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1129 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1130 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1131 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1132 conv_i($form->{delivery_term_id}),
1133 conv_i($form->{"id"}));
1134 do_query($form, $dbh, $query, @values);
1137 if ($form->{storno}) {
1140 paid = paid + amount,
1142 intnotes = ? || intnotes
1144 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1145 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1149 $form->{name} = $form->{customer};
1150 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1152 if (!$form->{shipto_id}) {
1153 $form->add_shipto($dbh, $form->{id}, "AR");
1156 # save printed, emailed and queued
1157 $form->save_status($dbh);
1159 Common::webdav_folder($form);
1161 # Link this record to the records it was created from.
1162 RecordLinks->create_links('dbh' => $dbh,
1164 'from_table' => 'oe',
1165 'from_ids' => $form->{convert_from_oe_ids},
1167 'to_id' => $form->{id},
1169 delete $form->{convert_from_oe_ids};
1171 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1173 if (scalar @convert_from_do_ids) {
1174 DO->close_orders('dbh' => $dbh,
1175 'ids' => \@convert_from_do_ids);
1177 RecordLinks->create_links('dbh' => $dbh,
1179 'from_table' => 'delivery_orders',
1180 'from_ids' => \@convert_from_do_ids,
1182 'to_id' => $form->{id},
1185 delete $form->{convert_from_do_ids};
1187 ARAP->close_orders_if_billed('dbh' => $dbh,
1188 'arap_id' => $form->{id},
1191 # safety check datev export
1192 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1193 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1194 $transdate ||= DateTime->today;
1196 my $datev = SL::DATEV->new(
1197 exporttype => DATEV_ET_BUCHUNGEN,
1198 format => DATEV_FORMAT_KNE,
1202 trans_id => $form->{id},
1207 if ($datev->errors) {
1209 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1214 $dbh->commit if !$provided_dbh;
1216 $main::lxdebug->leave_sub();
1221 sub _delete_payments {
1222 $main::lxdebug->enter_sub();
1224 my ($self, $form, $dbh) = @_;
1226 my @delete_acc_trans_ids;
1228 # Delete old payment entries from acc_trans.
1230 qq|SELECT acc_trans_id
1232 WHERE (trans_id = ?) AND fx_transaction
1236 SELECT at.acc_trans_id
1238 LEFT JOIN chart c ON (at.chart_id = c.id)
1239 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1240 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1243 qq|SELECT at.acc_trans_id
1245 LEFT JOIN chart c ON (at.chart_id = c.id)
1246 WHERE (trans_id = ?)
1247 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1248 ORDER BY at.acc_trans_id
1250 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1252 if (@delete_acc_trans_ids) {
1253 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1254 do_query($form, $dbh, $query);
1257 $main::lxdebug->leave_sub();
1261 $main::lxdebug->enter_sub();
1263 my ($self, $myconfig, $form, $locale) = @_;
1265 # connect to database, turn off autocommit
1266 my $dbh = $form->get_standard_dbh;
1268 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1270 $old_form = save_form();
1272 # Delete all entries in acc_trans from prior payments.
1273 if (SL::DB::Default->get->payments_changeable != 0) {
1274 $self->_delete_payments($form, $dbh);
1277 # Save the new payments the user made before cleaning up $form.
1278 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 };
1280 # Clean up $form so that old content won't tamper the results.
1281 %keep_vars = map { $_, 1 } qw(login password id);
1282 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1284 # Retrieve the invoice from the database.
1285 $self->retrieve_invoice($myconfig, $form);
1287 # Set up the content of $form in the way that IS::post_invoice() expects.
1288 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1290 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1291 $item = $form->{invoice_details}->[$row - 1];
1293 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1295 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1298 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1300 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1302 # Restore the payment options from the user input.
1303 map { $form->{$_} = $payments{$_} } keys %payments;
1305 # Get the AR accno (which is normally done by Form::create_links()).
1309 LEFT JOIN chart c ON (at.chart_id = c.id)
1310 WHERE (trans_id = ?)
1311 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1312 ORDER BY at.acc_trans_id
1315 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1317 # Post the new payments.
1318 $self->post_invoice($myconfig, $form, $dbh, 1);
1320 restore_form($old_form);
1322 my $rc = $dbh->commit();
1324 $main::lxdebug->leave_sub();
1329 sub process_assembly {
1330 $main::lxdebug->enter_sub();
1332 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1335 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1336 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1338 JOIN parts p ON (a.parts_id = p.id)
1340 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1342 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1346 $ref->{inventory_accno_id} *= 1;
1347 $ref->{expense_accno_id} *= 1;
1349 # multiply by number of assemblies
1350 $ref->{qty} *= $totalqty;
1352 if ($ref->{assembly}) {
1353 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1356 if ($ref->{inventory_accno_id}) {
1357 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1361 # save detail record for individual assembly item in invoice table
1363 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1364 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1365 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1366 do_query($form, $dbh, $query, @values);
1372 $main::lxdebug->leave_sub();
1376 $main::lxdebug->enter_sub();
1378 # adjust allocated in table invoice according to FIFO princicple
1379 # for a certain part with part_id $id
1381 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1385 $form->{taxzone_id} *=1;
1386 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1387 my $taxzone_id = $form->{"taxzone_id"} * 1;
1389 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1390 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1391 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1392 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1393 FROM invoice i, parts p
1394 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1395 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1396 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1397 WHERE (i.parts_id = p.id)
1398 AND (i.parts_id = ?)
1399 AND ((i.base_qty + i.allocated) < 0)
1401 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1406 # all invoice entries of an example part:
1408 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1409 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1410 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1411 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1412 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1413 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1414 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1416 # 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
1417 # and all parts have been allocated
1419 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1420 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1422 # in this example there are still 4 unsold articles
1425 # search all invoice entries for the part in question, adjusting "allocated"
1426 # until the total number of sold parts has been reached
1428 # ORDER BY trans_id ensures FIFO
1431 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1432 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1436 # update allocated in invoice
1437 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1439 # total expenses and inventory
1440 # sellprice is the cost of the item
1441 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1443 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1444 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1445 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1447 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1448 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1449 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1451 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1452 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1458 last if (($totalqty -= $qty) <= 0);
1463 $main::lxdebug->leave_sub();
1468 sub reverse_invoice {
1469 $main::lxdebug->enter_sub();
1471 my ($dbh, $form) = @_;
1473 # reverse inventory items
1475 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1477 JOIN parts p ON (i.parts_id = p.id)
1478 WHERE i.trans_id = ?|;
1479 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1481 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1483 if ($ref->{inventory_accno_id}) {
1484 # de-allocated purchases
1486 qq|SELECT i.id, i.trans_id, i.allocated
1488 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1489 ORDER BY i.trans_id DESC|;
1490 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1492 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1493 my $qty = $ref->{qty};
1494 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1495 $qty = $inhref->{allocated};
1499 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1501 last if (($ref->{qty} -= $qty) <= 0);
1510 my @values = (conv_i($form->{id}));
1511 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1512 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1513 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1515 $main::lxdebug->leave_sub();
1518 sub delete_invoice {
1519 $main::lxdebug->enter_sub();
1521 my ($self, $myconfig, $form) = @_;
1523 # connect to database
1524 my $dbh = $form->get_standard_dbh;
1526 &reverse_invoice($dbh, $form);
1528 my @values = (conv_i($form->{id}));
1530 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1531 # zurückgesetzt werden. Vgl:
1532 # id | storno | storno_id | paid | amount
1533 #----+--------+-----------+---------+-----------
1534 # 18 | f | | 0.00000 | 119.00000
1536 # 18 | t | | 119.00000 | 119.00000
1538 if($form->{storno}){
1539 # storno_id auslesen und korrigieren
1540 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1541 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1544 # delete spool files
1545 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1548 qq|DELETE FROM status WHERE trans_id = ?|,
1549 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1550 qq|DELETE FROM ar WHERE id = ?|,
1553 map { do_query($form, $dbh, $_, @values) } @queries;
1555 my $rc = $dbh->commit;
1558 my $spool = $::lx_office_conf{paths}->{spool};
1559 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1562 $main::lxdebug->leave_sub();
1567 sub retrieve_invoice {
1568 $main::lxdebug->enter_sub();
1570 my ($self, $myconfig, $form) = @_;
1572 # connect to database
1573 my $dbh = $form->get_standard_dbh;
1575 my ($sth, $ref, $query);
1577 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1581 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1582 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1583 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1584 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1585 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1589 $ref = selectfirst_hashref_query($form, $dbh, $query);
1590 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1593 my $id = conv_i($form->{id});
1596 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1600 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1601 a.orddate, a.quodate, a.globalproject_id,
1602 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1603 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1604 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1605 a.employee_id, a.salesman_id, a.payment_id,
1606 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1607 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1608 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1611 LEFT JOIN employee e ON (e.id = a.employee_id)
1613 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1614 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1616 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1619 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1620 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1622 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1624 foreach my $vc (qw(customer vendor)) {
1625 next if !$form->{"delivery_${vc}_id"};
1626 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1629 # get printed, emailed
1630 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1631 $sth = prepare_execute_query($form, $dbh, $query, $id);
1633 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1634 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1635 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1636 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1639 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1641 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1642 : $form->{invdate} ? $dbh->quote($form->{invdate})
1646 my $taxzone_id = $form->{taxzone_id} *= 1;
1647 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1649 # retrieve individual items
1652 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1653 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1654 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1657 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1658 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1659 i.price_factor_id, i.price_factor, i.marge_price_factor,
1660 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1661 pr.projectnumber, pg.partsgroup, prg.pricegroup
1664 LEFT JOIN parts p ON (i.parts_id = p.id)
1665 LEFT JOIN project pr ON (i.project_id = pr.id)
1666 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1667 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1669 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1670 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1671 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1673 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1675 $sth = prepare_execute_query($form, $dbh, $query, $id);
1677 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1678 # Retrieve custom variables.
1679 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1681 sub_module => 'invoice',
1682 trans_id => $ref->{invoice_id},
1684 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1685 delete $ref->{invoice_id};
1687 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1688 delete($ref->{"part_inventory_accno_id"});
1690 foreach my $type (qw(inventory income expense)) {
1691 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1692 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1693 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1697 # get tax rates and description
1698 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1700 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1701 LEFT JOIN chart c ON (c.id = t.chart_id)
1703 (SELECT tk.tax_id FROM taxkeys tk
1704 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1705 AND startdate <= date($transdate)
1706 ORDER BY startdate DESC LIMIT 1)
1708 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1709 $ref->{taxaccounts} = "";
1711 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1713 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1717 $ref->{taxaccounts} .= "$ptr->{accno} ";
1719 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1720 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1721 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1722 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1723 $form->{taxaccounts} .= "$ptr->{accno} ";
1728 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1730 chop $ref->{taxaccounts};
1731 push @{ $form->{invoice_details} }, $ref;
1736 Common::webdav_folder($form);
1739 my $rc = $dbh->commit;
1741 $main::lxdebug->leave_sub();
1747 $main::lxdebug->enter_sub();
1749 my ($self, $myconfig, $form) = @_;
1751 # connect to database
1752 my $dbh = $form->get_standard_dbh;
1754 my $dateformat = $myconfig->{dateformat};
1755 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1757 my (@values, $duedate, $ref, $query);
1759 if ($form->{invdate}) {
1760 $duedate = "to_date(?, '$dateformat')";
1761 push @values, $form->{invdate};
1763 $duedate = "current_date";
1766 my $cid = conv_i($form->{customer_id});
1769 if ($form->{payment_id}) {
1770 $payment_id = "(pt.id = ?) OR";
1771 push @values, conv_i($form->{payment_id});
1777 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1778 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1779 c.street, c.zipcode, c.city, c.country,
1780 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1781 c.taxincluded_checked, c.direct_debit,
1782 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1783 b.discount AS tradediscount, b.description AS business
1785 LEFT JOIN business b ON (b.id = c.business_id)
1786 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1787 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1790 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1792 delete $ref->{salesman_id} if !$ref->{salesman_id};
1794 map { $form->{$_} = $ref->{$_} } keys %$ref;
1796 # use customer currency
1797 $form->{currency} = $form->{curr};
1800 qq|SELECT sum(amount - paid) AS dunning_amount
1802 WHERE (paid < amount)
1803 AND (customer_id = ?)
1804 AND (dunning_config_id IS NOT NULL)|;
1805 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1806 map { $form->{$_} = $ref->{$_} } keys %$ref;
1809 qq|SELECT dnn.dunning_description AS max_dunning_level
1810 FROM dunning_config dnn
1811 WHERE id IN (SELECT dunning_config_id
1813 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1814 ORDER BY dunning_level DESC LIMIT 1|;
1815 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1816 map { $form->{$_} = $ref->{$_} } keys %$ref;
1818 $form->{creditremaining} = $form->{creditlimit};
1819 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1820 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1821 $form->{creditremaining} -= $value;
1825 (SELECT e.buy FROM exchangerate e
1826 WHERE e.currency_id = o.currency_id
1827 AND e.transdate = o.transdate)
1829 WHERE o.customer_id = ?
1830 AND o.quotation = '0'
1831 AND o.closed = '0'|;
1832 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1834 while (my ($amount, $exch) = $sth->fetchrow_array) {
1835 $exch = 1 unless $exch;
1836 $form->{creditremaining} -= $amount * $exch;
1840 # get shipto if we did not converted an order or invoice
1841 if (!$form->{shipto}) {
1842 map { delete $form->{$_} }
1843 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1844 shiptostreet shiptozipcode shiptocity shiptocountry
1845 shiptocontact shiptophone shiptofax shiptoemail);
1847 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1848 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1850 map { $form->{$_} = $ref->{$_} } keys %$ref;
1853 # setup last accounts used for this customer
1854 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1856 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1858 JOIN acc_trans ac ON (ac.chart_id = c.id)
1859 JOIN ar a ON (a.id = ac.trans_id)
1860 WHERE a.customer_id = ?
1861 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1862 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1863 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1866 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1867 if ($ref->{category} eq 'I') {
1869 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1871 if ($form->{initial_transdate}) {
1873 qq|SELECT tk.tax_id, t.rate
1875 LEFT JOIN tax t ON tk.tax_id = t.id
1876 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1877 ORDER BY tk.startdate DESC
1879 my ($tax_id, $rate) =
1880 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1881 $form->{initial_transdate});
1882 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1885 if ($ref->{category} eq 'A') {
1886 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1890 $form->{rowcount} = $i if ($i && !$form->{type});
1893 $main::lxdebug->leave_sub();
1897 $main::lxdebug->enter_sub();
1899 my ($self, $myconfig, $form) = @_;
1901 # connect to database
1902 my $dbh = $form->get_standard_dbh;
1904 my $i = $form->{rowcount};
1906 my $where = qq|NOT p.obsolete = '1'|;
1909 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1910 my ($table, $field) = split m/\./, $column;
1911 next if !$form->{"${field}_${i}"};
1912 $where .= qq| AND lower(${column}) ILIKE ?|;
1913 push @values, '%' . $form->{"${field}_${i}"} . '%';
1916 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1917 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1918 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1919 push @values, $form->{"partnumber_$i"};
1922 # Search for part ID overrides all other criteria.
1923 if ($form->{"id_${i}"}) {
1924 $where = qq|p.id = ?|;
1925 @values = ($form->{"id_${i}"});
1928 if ($form->{"description_$i"}) {
1929 $where .= qq| ORDER BY p.description|;
1931 $where .= qq| ORDER BY p.partnumber|;
1935 if ($form->{type} eq "invoice") {
1937 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1938 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1942 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1946 my $taxzone_id = $form->{taxzone_id} * 1;
1947 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1951 p.id, p.partnumber, p.description, p.sellprice,
1952 p.listprice, p.inventory_accno_id, p.lastcost,
1954 c1.accno AS inventory_accno,
1955 c1.new_chart_id AS inventory_new_chart,
1956 date($transdate) - c1.valid_from AS inventory_valid,
1958 c2.accno AS income_accno,
1959 c2.new_chart_id AS income_new_chart,
1960 date($transdate) - c2.valid_from AS income_valid,
1962 c3.accno AS expense_accno,
1963 c3.new_chart_id AS expense_new_chart,
1964 date($transdate) - c3.valid_from AS expense_valid,
1966 p.unit, p.assembly, p.onhand,
1967 p.notes AS partnotes, p.notes AS longdescription,
1968 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1969 p.price_factor_id, p.weight,
1971 pfac.factor AS price_factor,
1976 LEFT JOIN chart c1 ON
1977 ((SELECT inventory_accno_id
1978 FROM buchungsgruppen
1979 WHERE id = p.buchungsgruppen_id) = c1.id)
1980 LEFT JOIN chart c2 ON
1981 ((SELECT income_accno_id_${taxzone_id}
1982 FROM buchungsgruppen
1983 WHERE id = p.buchungsgruppen_id) = c2.id)
1984 LEFT JOIN chart c3 ON
1985 ((SELECT expense_accno_id_${taxzone_id}
1986 FROM buchungsgruppen
1987 WHERE id = p.buchungsgruppen_id) = c3.id)
1988 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1989 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1991 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1993 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1995 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1996 [ qq|SELECT tr.translation, tr.longdescription
1998 WHERE tr.language_id IN
2001 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2004 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2006 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2008 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2009 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2010 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2011 if (!$ref->{inventory_accno_id}) {
2012 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2014 delete($ref->{inventory_accno_id});
2016 foreach my $type (qw(inventory income expense)) {
2017 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2019 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2022 ($ref->{"${type}_accno"},
2023 $ref->{"${type}_new_chart"},
2024 $ref->{"${type}_valid"})
2025 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2029 if ($form->{payment_id} eq "") {
2030 $form->{payment_id} = $form->{part_payment_id};
2033 # get tax rates and description
2034 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2036 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2038 LEFT JOIN chart c ON (c.id = t.chart_id)
2042 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2044 ORDER BY startdate DESC
2047 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2048 my $stw = $dbh->prepare($query);
2049 $stw->execute(@values) || $form->dberror($query);
2051 $ref->{taxaccounts} = "";
2053 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2055 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2059 $ref->{taxaccounts} .= "$ptr->{accno} ";
2061 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2062 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2063 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2064 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2065 $form->{taxaccounts} .= "$ptr->{accno} ";
2071 chop $ref->{taxaccounts};
2073 if ($form->{language_id}) {
2074 for my $spec (@translation_queries) {
2075 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2076 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2077 next unless $translation;
2078 $ref->{description} = $translation;
2079 $ref->{longdescription} = $longdescription;
2084 $ref->{onhand} *= 1;
2086 push @{ $form->{item_list} }, $ref;
2089 $_->[1]->finish for @translation_queries;
2091 foreach my $item (@{ $form->{item_list} }) {
2092 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2093 trans_id => $item->{id},
2097 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2100 $main::lxdebug->leave_sub();
2103 ##########################
2104 # get pricegroups from database
2105 # build up selected pricegroup
2106 # if an exchange rate - change price
2109 sub get_pricegroups_for_parts {
2111 $main::lxdebug->enter_sub();
2113 my ($self, $myconfig, $form) = @_;
2115 my $dbh = $form->get_standard_dbh;
2117 $form->{"PRICES"} = {};
2121 my $all_units = AM->retrieve_units($myconfig, $form);
2122 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2123 $form->{"PRICES"}{$i} = [];
2125 $id = $form->{"id_$i"};
2127 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2128 $id = $form->{"new_id_$i"};
2131 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2133 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2135 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2136 my $sellprice = $form->{"sellprice_$i"};
2137 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2138 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2139 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2141 my $price_new = $form->{"price_new_$i"};
2142 my $price_old = $form->{"price_old_$i"};
2144 if (!$form->{"unit_old_$i"}) {
2145 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2146 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2147 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2148 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2151 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2152 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2153 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2155 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2156 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2157 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2158 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2159 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2160 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2161 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2166 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2167 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2168 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2169 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2170 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2174 if (!$form->{"basefactor_$i"}) {
2175 $form->{"basefactor_$i"} = 1;
2181 sellprice AS default_sellprice,
2184 'selected' AS selected
2190 parts.sellprice AS default_sellprice,
2191 pricegroup.pricegroup,
2195 LEFT JOIN parts ON parts.id = parts_id
2196 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2198 ORDER BY pricegroup|;
2199 my @values = (conv_i($id), conv_i($id));
2200 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2202 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2204 $pkr->{selected} = '';
2206 # if there is an exchange rate change price
2207 if (($form->{exchangerate} * 1) != 0) {
2208 $pkr->{price} /= $form->{exchangerate};
2211 $pkr->{price} *= $form->{"basefactor_$i"};
2212 $pkr->{price} *= $basefactor;
2213 $pkr->{price_ufmt} = $pkr->{price};
2214 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2216 if (!defined $selectedpricegroup_id) {
2217 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2218 # Case A: open old invoice, no pricegroup selected
2219 # Case B: add new article to invoice, no pricegroup selected
2221 # to distinguish case A and B the variable pricegroup_id_$i is used
2222 # for new articles this variable isn't defined, for loaded articles it is
2223 # sellprice can't be used, as it already has 0,00 set
2225 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2227 $pkr->{selected} = ' selected';
2228 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2229 and not defined $form->{"pricegroup_id_$i"}
2230 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2231 # for the case where pricegroup prices haven't been set
2233 # Case B: use default pricegroup of customer
2235 $pkr->{selected} = ' selected'; # unless $form->{selected};
2236 # no customer pricesgroup set
2237 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2239 $pkr->{price} = $form->{"sellprice_$i"};
2243 # this sub should not set anything and only return. --sschoeling, 20090506
2244 # is this correct? put in again... -- grichardson 20110119
2245 $form->{"sellprice_$i"} = $pkr->{price};
2248 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2249 $pkr->{price} = $form->{"sellprice_$i"};
2250 $pkr->{selected} = ' selected';
2254 # existing article: pricegroup or price changed
2255 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2256 if ($selectedpricegroup_id ne $pricegroup_old) {
2257 # pricegroup has changed
2258 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2259 $pkr->{selected} = ' selected';
2261 } elsif ( ($form->parse_amount($myconfig, $price_new)
2262 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2263 and ($price_new ne 0) and defined $price_new) {
2264 # sellprice has changed
2265 # when loading existing invoices $price_new is NULL
2266 if ($pkr->{pricegroup_id} == 0) {
2267 $pkr->{price} = $form->{"sellprice_$i"};
2268 $pkr->{selected} = ' selected';
2270 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2271 # neither sellprice nor pricegroup changed
2272 $pkr->{selected} = ' selected';
2273 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2274 # $pkr->{price} = $form->{"sellprice_$i"};
2276 $pkr->{price} = $form->{"sellprice_$i"};
2280 push @{ $form->{PRICES}{$i} }, $pkr;
2283 $form->{"basefactor_$i"} *= $basefactor;
2290 $main::lxdebug->leave_sub();
2294 $main::lxdebug->enter_sub();
2296 my ($self, $myconfig, $form, $table) = @_;
2298 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2300 # make sure there's no funny stuff in $table
2301 # ToDO: die when this happens and throw an error
2302 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2304 my $dbh = $form->get_standard_dbh;
2306 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2307 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2309 $main::lxdebug->leave_sub();
2315 $main::lxdebug->enter_sub();
2317 my ($self, $myconfig, $form, $table, $id) = @_;
2319 $main::lxdebug->leave_sub() and return 0 unless ($id);
2321 # make sure there's no funny stuff in $table
2322 # ToDO: die when this happens and throw an error
2323 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2325 my $dbh = $form->get_standard_dbh;
2327 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2328 my ($result) = selectrow_query($form, $dbh, $query, $id);
2330 $main::lxdebug->leave_sub();
2335 sub get_standard_accno_current_assets {
2336 $main::lxdebug->enter_sub();
2338 my ($self, $myconfig, $form) = @_;
2340 my $dbh = $form->get_standard_dbh;
2342 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2343 my ($result) = selectrow_query($form, $dbh, $query);
2345 $main::lxdebug->leave_sub();