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;
61 $main::lxdebug->enter_sub();
63 my ($self, $myconfig, $form, $locale) = @_;
65 $form->{duedate} ||= $form->{invdate};
68 my $dbh = $form->get_standard_dbh;
71 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
72 ($form->{terms}) = selectrow_query($form, $dbh, $query);
75 $form->{TEMPLATE_ARRAYS} = {};
77 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
79 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
82 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
83 $price_factors{$pfac->{id}} = $pfac;
85 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
88 # sort items by partsgroup
89 for my $i (1 .. $form->{rowcount}) {
91 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
92 # $partsgroup = $form->{"partsgroup_$i"};
94 # push @partsgroup, [$i, $partsgroup];
95 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
101 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
102 %projects_by_id = map { $_->id => $_ } @$projects;
105 if ($projects_by_id{$form->{"globalproject_id"}}) {
106 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
107 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
109 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
110 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
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');
150 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
153 qw(runningnumber number description longdescription qty ship unit bin
154 deliverydate_oe ordnumber_oe donumber_do transdate_oe validuntil
155 partnotes serialnumber reqdate sellprice listprice netprice
156 discount p_discount discount_sub nodiscount_sub
157 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
158 price_factor price_factor_name partsgroup weight lineweight);
160 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
163 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
165 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
167 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
170 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
173 if ($item->[1] ne $sameitem) {
174 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
175 $sameitem = $item->[1];
177 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
180 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
182 if ($form->{"id_$i"} != 0) {
184 # add number, description and qty to $form->{number},
185 if ($form->{"subtotal_$i"} && !$subtotal_header) {
186 $subtotal_header = $i;
187 $position = int($position);
190 } elsif ($subtotal_header) {
192 $position = int($position);
193 $position = $position.".".$subposition;
195 $position = int($position);
199 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
201 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
202 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
209 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
214 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$i"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
217 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
219 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
220 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
221 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
222 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
223 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
225 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
226 my ($dec) = ($sellprice =~ /\.(\d+)/);
227 my $decimalplaces = max 2, length($dec);
229 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
231 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
232 my $linetotal = $form->round_amount($linetotal_exact, 2);
234 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
235 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
237 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
239 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
241 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
243 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
246 $linetotal = ($linetotal != 0) ? $linetotal : '';
248 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
249 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
250 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
252 $form->{total} += $linetotal;
253 $form->{nodiscount_total} += $nodiscount_linetotal;
254 $form->{discount_total} += $discount;
256 if ($subtotal_header) {
257 $discount_subtotal += $linetotal;
258 $nodiscount_subtotal += $nodiscount_linetotal;
261 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
262 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
263 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
264 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
265 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
267 $discount_subtotal = 0;
268 $nodiscount_subtotal = 0;
269 $subtotal_header = 0;
272 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
275 if (!$form->{"discount_$i"}) {
276 $nodiscount += $linetotal;
279 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
280 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
281 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
282 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
284 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
286 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
287 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
289 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
290 $totalweight += $lineweight;
291 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
292 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
293 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
294 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
296 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
300 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
302 if ($form->{taxincluded}) {
305 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
306 $taxbase = $linetotal - $taxamount;
308 $taxamount = $linetotal * $taxrate;
309 $taxbase = $linetotal;
312 if ($form->round_amount($taxrate, 7) == 0) {
313 if ($form->{taxincluded}) {
314 foreach my $accno (@taxaccounts) {
315 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
317 $taxaccounts{$accno} += $taxamount;
318 $taxdiff += $taxamount;
320 $taxbase{$accno} += $taxbase;
322 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
324 foreach my $accno (@taxaccounts) {
325 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
326 $taxbase{$accno} += $taxbase;
330 foreach my $accno (@taxaccounts) {
331 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
332 $taxbase{$accno} += $taxbase;
335 my $tax_rate = $taxrate * 100;
336 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
337 if ($form->{"assembly_$i"}) {
340 # get parts and push them onto the stack
342 if ($form->{groupitems}) {
344 qq|ORDER BY pg.partsgroup, a.oid|;
346 $sortorder = qq|ORDER BY a.oid|;
350 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
352 JOIN parts p ON (a.parts_id = p.id)
353 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
354 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
355 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
357 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
358 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
359 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
360 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
361 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
364 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
366 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
367 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
369 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
370 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
376 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
377 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
378 for @{ $ic_cvar_configs };
380 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
384 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
385 $form->{totalweight_nofmt} = $totalweight;
386 my $defaults = AM->get_defaults();
387 $form->{weightunit} = $defaults->{weightunit};
389 foreach my $item (sort keys %taxaccounts) {
390 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
392 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
393 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
394 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
395 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
396 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
397 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
398 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
400 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
401 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
402 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
405 for my $i (1 .. $form->{paidaccounts}) {
406 if ($form->{"paid_$i"}) {
407 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
409 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
410 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
411 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
412 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
413 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
415 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
418 if($form->{taxincluded}) {
419 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
420 $form->{subtotal_nofmt} = $form->{total} - $tax;
423 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
424 $form->{subtotal_nofmt} = $form->{total};
427 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
428 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
429 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
430 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
432 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
433 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
435 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
436 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
438 $form->set_payment_options($myconfig, $form->{invdate});
440 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
441 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
443 $form->{username} = $myconfig->{name};
445 $main::lxdebug->leave_sub();
448 sub project_description {
449 $main::lxdebug->enter_sub();
451 my ($self, $dbh, $id) = @_;
452 my $form = \%main::form;
454 my $query = qq|SELECT description FROM project WHERE id = ?|;
455 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
457 $main::lxdebug->leave_sub();
462 sub customer_details {
463 $main::lxdebug->enter_sub();
465 my ($self, $myconfig, $form, @wanted_vars) = @_;
467 # connect to database
468 my $dbh = $form->get_standard_dbh;
470 my $language_id = $form->{language_id};
472 # get contact id, set it if nessessary
475 my @values = (conv_i($form->{customer_id}));
478 if ($form->{cp_id}) {
479 $where = qq| AND (cp.cp_id = ?) |;
480 push(@values, conv_i($form->{cp_id}));
483 # get rest for the customer
485 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
486 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
489 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
490 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
491 WHERE (ct.id = ?) $where
494 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
496 # remove id and taxincluded before copy back
497 delete @$ref{qw(id taxincluded)};
499 @wanted_vars = grep({ $_ } @wanted_vars);
500 if (scalar(@wanted_vars) > 0) {
502 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
503 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
506 map { $form->{$_} = $ref->{$_} } keys %$ref;
508 if ($form->{delivery_customer_id}) {
510 qq|SELECT *, notes as customernotes
514 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
516 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
519 if ($form->{delivery_vendor_id}) {
521 qq|SELECT *, notes as customernotes
525 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
527 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
530 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
532 'trans_id' => $form->{customer_id});
533 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
535 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
536 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
537 'language_id' => $language_id,
538 'allow_fallback' => 1);
541 $main::lxdebug->leave_sub();
545 $main::lxdebug->enter_sub();
547 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
549 # connect to database, turn off autocommit
550 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
551 my $restricter = SL::HTML::Restrict->create;
553 my ($query, $sth, $null, $project_id, @values);
554 my $exchangerate = 0;
556 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
559 if (!$form->{employee_id}) {
560 $form->get_employee($dbh);
563 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
564 my $defaultcurrency = $form->{defaultcurrency};
566 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
567 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
568 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
569 if (!$form->{department_id}){
570 ($null, $form->{department_id}) = split(/--/, $form->{department});
573 my $all_units = AM->retrieve_units($myconfig, $form);
575 if (!$payments_only) {
577 &reverse_invoice($dbh, $form);
580 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
581 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
583 $query = qq|SELECT nextval('glid')|;
584 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
586 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
587 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
589 if (!$form->{invnumber}) {
590 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
591 $form->{invnumber} = $trans_number->create_unique;
596 my ($netamount, $invoicediff) = (0, 0);
597 my ($amount, $linetotal, $lastincomeaccno);
599 if ($form->{currency} eq $defaultcurrency) {
600 $form->{exchangerate} = 1;
602 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
605 $form->{exchangerate} =
608 : $form->parse_amount($myconfig, $form->{exchangerate});
610 $form->{expense_inventory} = "";
614 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
615 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
618 $form->{amount} = {};
619 $form->{amount_cogs} = {};
621 foreach my $i (1 .. $form->{rowcount}) {
622 if ($form->{type} eq "credit_note") {
623 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
624 $form->{shipped} = 1;
626 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
631 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
632 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
633 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
635 if ($form->{storno}) {
636 $form->{"qty_$i"} *= -1;
639 if ($form->{"id_$i"}) {
642 if (defined($baseunits{$form->{"id_$i"}})) {
643 $item_unit = $baseunits{$form->{"id_$i"}};
646 $query = qq|SELECT unit FROM parts WHERE id = ?|;
647 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
648 $baseunits{$form->{"id_$i"}} = $item_unit;
651 if (defined($all_units->{$item_unit}->{factor})
652 && ($all_units->{$item_unit}->{factor} ne '')
653 && ($all_units->{$item_unit}->{factor} != 0)) {
654 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
658 $baseqty = $form->{"qty_$i"} * $basefactor;
660 my ($allocated, $taxrate) = (0, 0);
664 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
666 # keep entered selling price
668 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
670 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
672 my $decimalplaces = ($dec > 2) ? $dec : 2;
674 # undo discount formatting
675 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
678 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
680 # round linetotal to 2 decimal places
681 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
682 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
684 if ($form->{taxincluded}) {
685 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
686 $form->{"sellprice_$i"} =
687 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
689 $taxamount = $linetotal * $taxrate;
692 $netamount += $linetotal;
694 if ($taxamount != 0) {
696 $form->{amount}{ $form->{id} }{$_} +=
697 $taxamount * $form->{"${_}_rate"} / $taxrate
698 } split(/ /, $form->{"taxaccounts_$i"});
701 # add amount to income, $form->{amount}{trans_id}{accno}
702 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
704 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
705 $linetotal = $form->round_amount($linetotal, 2);
707 # this is the difference from the inventory
708 $invoicediff += ($amount - $linetotal);
710 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
713 $lastincomeaccno = $form->{"income_accno_$i"};
715 # adjust and round sellprice
716 $form->{"sellprice_$i"} =
717 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
720 next if $payments_only;
722 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
724 if ($form->{"assembly_$i"}) {
725 # record assembly item as allocated
726 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
729 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
733 # Get pricegroup_id and save it. Unfortunately the interface
734 # also uses ID "0" for signalling that none is selected, but "0"
735 # must not be stored in the database. Therefore we cannot simply
737 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
739 $pricegroup_id = undef if !$pricegroup_id;
741 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
743 # save detail record in invoice table
745 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
746 sellprice, fxsellprice, discount, allocated, assemblyitem,
747 unit, deliverydate, project_id, serialnumber, pricegroup_id,
748 ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
749 marge_percent, marge_total, lastcost,
750 price_factor_id, price_factor, marge_price_factor)
751 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
752 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
754 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
755 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
756 $form->{"sellprice_$i"}, $fxsellprice,
757 $form->{"discount_$i"}, $allocated, 'f',
758 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
759 $form->{"serialnumber_$i"}, $pricegroup_id,
760 $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
761 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
762 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
763 $form->{"lastcost_$i"},
764 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
765 conv_i($form->{"marge_price_factor_$i"}));
766 do_query($form, $dbh, $query, @values);
768 CVar->save_custom_variables(module => 'IC',
769 sub_module => 'invoice',
770 trans_id => $invoice_id,
771 configs => $ic_cvar_configs,
773 name_prefix => 'ic_',
774 name_postfix => "_$i",
779 # total payments, don't move we need it here
780 for my $i (1 .. $form->{paidaccounts}) {
781 if ($form->{type} eq "credit_note") {
782 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
784 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
786 $form->{paid} += $form->{"paid_$i"};
787 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
790 my ($tax, $diff) = (0, 0);
792 $netamount = $form->round_amount($netamount, 2);
794 # figure out rounding errors for total amount vs netamount + taxes
795 if ($form->{taxincluded}) {
797 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
798 $diff += $amount - $netamount * $form->{exchangerate};
799 $netamount = $amount;
801 foreach my $item (split(/ /, $form->{taxaccounts})) {
802 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
803 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
804 $tax += $form->{amount}{ $form->{id} }{$item};
805 $netamount -= $form->{amount}{ $form->{id} }{$item};
808 $invoicediff += $diff;
809 ######## this only applies to tax included
810 if ($lastincomeaccno) {
811 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
815 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
816 $diff = $amount - $netamount * $form->{exchangerate};
817 $netamount = $amount;
818 foreach my $item (split(/ /, $form->{taxaccounts})) {
819 $form->{amount}{ $form->{id} }{$item} =
820 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
823 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
826 $amount - $form->{amount}{ $form->{id} }{$item} *
827 $form->{exchangerate};
828 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
829 $tax += $form->{amount}{ $form->{id} }{$item};
833 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
835 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
838 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
840 # update exchangerate
841 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
842 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
843 $form->{exchangerate}, 0);
846 $project_id = conv_i($form->{"globalproject_id"});
847 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
848 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
850 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
851 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
852 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
854 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
856 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
858 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
859 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
860 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
861 do_query($form, $dbh, $query, @values);
862 $form->{amount_cogs}{$trans_id}{$accno} = 0;
866 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
867 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
869 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
871 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
872 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
873 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
874 do_query($form, $dbh, $query, @values);
879 foreach my $trans_id (keys %{ $form->{amount} }) {
880 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
881 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
883 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
885 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
887 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
888 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
891 WHERE chart_id= (SELECT id
895 ORDER BY startdate DESC LIMIT 1),
898 WHERE chart_id= (SELECT id
902 ORDER BY startdate DESC LIMIT 1),
904 (SELECT link FROM chart WHERE accno = ?))|;
905 @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);
906 do_query($form, $dbh, $query, @values);
907 $form->{amount}{$trans_id}{$accno} = 0;
911 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
912 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
914 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
916 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
917 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
920 WHERE chart_id= (SELECT id
924 ORDER BY startdate DESC LIMIT 1),
927 WHERE chart_id= (SELECT id
931 ORDER BY startdate DESC LIMIT 1),
933 (SELECT link FROM chart WHERE accno = ?))|;
934 @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);
935 do_query($form, $dbh, $query, @values);
940 # deduct payment differences from diff
941 for my $i (1 .. $form->{paidaccounts}) {
942 if ($form->{"paid_$i"} != 0) {
944 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
945 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
949 # record payments and offsetting AR
950 if (!$form->{storno}) {
951 for my $i (1 .. $form->{paidaccounts}) {
953 if ($form->{"acc_trans_id_$i"}
955 && (SL::DB::Default->get->payments_changeable == 0)) {
959 next if ($form->{"paid_$i"} == 0);
961 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
962 $form->{"datepaid_$i"} = $form->{invdate}
963 unless ($form->{"datepaid_$i"});
964 $form->{datepaid} = $form->{"datepaid_$i"};
968 if ($form->{currency} eq $defaultcurrency) {
969 $form->{"exchangerate_$i"} = 1;
971 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
972 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
976 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
978 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
980 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
981 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
984 WHERE chart_id= (SELECT id
988 ORDER BY startdate DESC LIMIT 1),
991 WHERE chart_id= (SELECT id
995 ORDER BY startdate DESC LIMIT 1),
997 (SELECT link FROM chart WHERE accno = ?))|;
998 @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});
999 do_query($form, $dbh, $query, @values);
1003 $form->{"paid_$i"} *= -1;
1004 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1007 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1008 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1011 WHERE chart_id= (SELECT id
1015 ORDER BY startdate DESC LIMIT 1),
1018 WHERE chart_id= (SELECT id
1022 ORDER BY startdate DESC LIMIT 1),
1024 (SELECT link FROM chart WHERE accno = ?))|;
1025 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1026 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1027 do_query($form, $dbh, $query, @values);
1029 # exchangerate difference
1030 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1031 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1035 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1036 $form->{"exchangerate_$i"};
1038 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1040 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1045 # update exchange rate
1046 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1047 $form->update_exchangerate($dbh, $form->{currency},
1048 $form->{"datepaid_$i"},
1049 $form->{"exchangerate_$i"}, 0);
1053 } else { # if (!$form->{storno})
1054 $form->{marge_total} *= -1;
1057 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1059 # record exchange rate differences and gains/losses
1060 foreach my $accno (keys %{ $form->{fx} }) {
1061 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1062 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1063 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1066 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1067 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1070 WHERE chart_id= (SELECT id
1074 ORDER BY startdate DESC LIMIT 1),
1077 WHERE chart_id= (SELECT id
1081 ORDER BY startdate DESC LIMIT 1),
1083 (SELECT link FROM chart WHERE accno = ?))|;
1084 @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);
1085 do_query($form, $dbh, $query, @values);
1090 if ($payments_only) {
1091 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1092 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1094 $dbh->commit if !$provided_dbh;
1096 $main::lxdebug->leave_sub();
1100 $amount = $netamount + $tax;
1103 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1105 $query = qq|UPDATE ar set
1106 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1107 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1108 amount = ?, netamount = ?, paid = ?,
1109 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1110 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1111 currency_id = (SELECT id FROM currencies WHERE name = ?),
1112 department_id = ?, payment_id = ?, taxincluded = ?,
1113 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1114 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1115 cp_id = ?, marge_total = ?, marge_percent = ?,
1116 globalproject_id = ?, delivery_customer_id = ?,
1117 transaction_description = ?, delivery_vendor_id = ?,
1118 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1119 delivery_term_id = ?
1121 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1122 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1123 $amount, $netamount, $form->{"paid"},
1124 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1125 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1126 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1127 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1128 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1129 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1130 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1131 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1132 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1133 conv_i($form->{delivery_term_id}),
1134 conv_i($form->{"id"}));
1135 do_query($form, $dbh, $query, @values);
1138 if ($form->{storno}) {
1141 paid = paid + amount,
1143 intnotes = ? || intnotes
1145 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1146 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//;
1153 if (!$form->{shipto_id}) {
1154 $form->add_shipto($dbh, $form->{id}, "AR");
1157 # save printed, emailed and queued
1158 $form->save_status($dbh);
1160 Common::webdav_folder($form);
1162 # Link this record to the records it was created from.
1163 RecordLinks->create_links('dbh' => $dbh,
1165 'from_table' => 'oe',
1166 'from_ids' => $form->{convert_from_oe_ids},
1168 'to_id' => $form->{id},
1170 delete $form->{convert_from_oe_ids};
1172 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1174 if (scalar @convert_from_do_ids) {
1175 DO->close_orders('dbh' => $dbh,
1176 'ids' => \@convert_from_do_ids);
1178 RecordLinks->create_links('dbh' => $dbh,
1180 'from_table' => 'delivery_orders',
1181 'from_ids' => \@convert_from_do_ids,
1183 'to_id' => $form->{id},
1186 delete $form->{convert_from_do_ids};
1188 ARAP->close_orders_if_billed('dbh' => $dbh,
1189 'arap_id' => $form->{id},
1192 # safety check datev export
1193 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1194 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1195 $transdate ||= DateTime->today;
1197 my $datev = SL::DATEV->new(
1198 exporttype => DATEV_ET_BUCHUNGEN,
1199 format => DATEV_FORMAT_KNE,
1203 trans_id => $form->{id},
1208 if ($datev->errors) {
1210 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1215 $dbh->commit if !$provided_dbh;
1217 $main::lxdebug->leave_sub();
1222 sub _delete_payments {
1223 $main::lxdebug->enter_sub();
1225 my ($self, $form, $dbh) = @_;
1227 my @delete_acc_trans_ids;
1229 # Delete old payment entries from acc_trans.
1231 qq|SELECT acc_trans_id
1233 WHERE (trans_id = ?) AND fx_transaction
1237 SELECT at.acc_trans_id
1239 LEFT JOIN chart c ON (at.chart_id = c.id)
1240 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1241 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1244 qq|SELECT at.acc_trans_id
1246 LEFT JOIN chart c ON (at.chart_id = c.id)
1247 WHERE (trans_id = ?)
1248 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1249 ORDER BY at.acc_trans_id
1251 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1253 if (@delete_acc_trans_ids) {
1254 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1255 do_query($form, $dbh, $query);
1258 $main::lxdebug->leave_sub();
1262 $main::lxdebug->enter_sub();
1264 my ($self, $myconfig, $form, $locale) = @_;
1266 # connect to database, turn off autocommit
1267 my $dbh = $form->get_standard_dbh;
1269 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1271 $old_form = save_form();
1273 # Delete all entries in acc_trans from prior payments.
1274 if (SL::DB::Default->get->payments_changeable != 0) {
1275 $self->_delete_payments($form, $dbh);
1278 # Save the new payments the user made before cleaning up $form.
1279 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 };
1281 # Clean up $form so that old content won't tamper the results.
1282 %keep_vars = map { $_, 1 } qw(login password id);
1283 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1285 # Retrieve the invoice from the database.
1286 $self->retrieve_invoice($myconfig, $form);
1288 # Set up the content of $form in the way that IS::post_invoice() expects.
1289 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1291 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1292 $item = $form->{invoice_details}->[$row - 1];
1294 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1296 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1299 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1301 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1303 # Restore the payment options from the user input.
1304 map { $form->{$_} = $payments{$_} } keys %payments;
1306 # Get the AR accno (which is normally done by Form::create_links()).
1310 LEFT JOIN chart c ON (at.chart_id = c.id)
1311 WHERE (trans_id = ?)
1312 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1313 ORDER BY at.acc_trans_id
1316 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1318 # Post the new payments.
1319 $self->post_invoice($myconfig, $form, $dbh, 1);
1321 restore_form($old_form);
1323 my $rc = $dbh->commit();
1325 $main::lxdebug->leave_sub();
1330 sub process_assembly {
1331 $main::lxdebug->enter_sub();
1333 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1336 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1337 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1339 JOIN parts p ON (a.parts_id = p.id)
1341 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1343 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1347 $ref->{inventory_accno_id} *= 1;
1348 $ref->{expense_accno_id} *= 1;
1350 # multiply by number of assemblies
1351 $ref->{qty} *= $totalqty;
1353 if ($ref->{assembly}) {
1354 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1357 if ($ref->{inventory_accno_id}) {
1358 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1362 # save detail record for individual assembly item in invoice table
1364 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1365 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1366 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1367 do_query($form, $dbh, $query, @values);
1373 $main::lxdebug->leave_sub();
1377 $main::lxdebug->enter_sub();
1379 # adjust allocated in table invoice according to FIFO princicple
1380 # for a certain part with part_id $id
1382 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1386 $form->{taxzone_id} *=1;
1387 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1388 my $taxzone_id = $form->{"taxzone_id"} * 1;
1390 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1391 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1392 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1393 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1394 FROM invoice i, parts p
1395 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1396 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
1397 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
1398 WHERE (i.parts_id = p.id)
1399 AND (i.parts_id = ?)
1400 AND ((i.base_qty + i.allocated) < 0)
1402 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1407 # all invoice entries of an example part:
1409 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1410 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1411 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1412 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1413 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1414 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1415 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1417 # 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
1418 # and all parts have been allocated
1420 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1421 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1423 # in this example there are still 4 unsold articles
1426 # search all invoice entries for the part in question, adjusting "allocated"
1427 # until the total number of sold parts has been reached
1429 # ORDER BY trans_id ensures FIFO
1432 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1433 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1437 # update allocated in invoice
1438 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1440 # total expenses and inventory
1441 # sellprice is the cost of the item
1442 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1444 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1445 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1446 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1448 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1449 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1450 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1452 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1453 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1459 last if (($totalqty -= $qty) <= 0);
1464 $main::lxdebug->leave_sub();
1469 sub reverse_invoice {
1470 $main::lxdebug->enter_sub();
1472 my ($dbh, $form) = @_;
1474 # reverse inventory items
1476 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1478 JOIN parts p ON (i.parts_id = p.id)
1479 WHERE i.trans_id = ?|;
1480 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1482 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1484 if ($ref->{inventory_accno_id}) {
1485 # de-allocated purchases
1487 qq|SELECT i.id, i.trans_id, i.allocated
1489 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1490 ORDER BY i.trans_id DESC|;
1491 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1493 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1494 my $qty = $ref->{qty};
1495 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1496 $qty = $inhref->{allocated};
1500 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1502 last if (($ref->{qty} -= $qty) <= 0);
1511 my @values = (conv_i($form->{id}));
1512 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1513 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1514 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1516 $main::lxdebug->leave_sub();
1519 sub delete_invoice {
1520 $main::lxdebug->enter_sub();
1522 my ($self, $myconfig, $form) = @_;
1524 # connect to database
1525 my $dbh = $form->get_standard_dbh;
1527 &reverse_invoice($dbh, $form);
1529 my @values = (conv_i($form->{id}));
1531 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1532 # zurückgesetzt werden. Vgl:
1533 # id | storno | storno_id | paid | amount
1534 #----+--------+-----------+---------+-----------
1535 # 18 | f | | 0.00000 | 119.00000
1537 # 18 | t | | 119.00000 | 119.00000
1539 if($form->{storno}){
1540 # storno_id auslesen und korrigieren
1541 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1542 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1545 # delete spool files
1546 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1549 qq|DELETE FROM status WHERE trans_id = ?|,
1550 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1551 qq|DELETE FROM ar WHERE id = ?|,
1554 map { do_query($form, $dbh, $_, @values) } @queries;
1556 my $rc = $dbh->commit;
1559 my $spool = $::lx_office_conf{paths}->{spool};
1560 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1563 $main::lxdebug->leave_sub();
1568 sub retrieve_invoice {
1569 $main::lxdebug->enter_sub();
1571 my ($self, $myconfig, $form) = @_;
1573 # connect to database
1574 my $dbh = $form->get_standard_dbh;
1576 my ($sth, $ref, $query);
1578 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1582 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1583 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1584 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1585 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1586 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1590 $ref = selectfirst_hashref_query($form, $dbh, $query);
1591 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1594 my $id = conv_i($form->{id});
1597 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1601 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1602 a.orddate, a.quodate, a.globalproject_id,
1603 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1604 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1605 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1606 a.employee_id, a.salesman_id, a.payment_id,
1607 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1608 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1609 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1612 LEFT JOIN employee e ON (e.id = a.employee_id)
1614 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1615 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1617 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1619 foreach my $vc (qw(customer vendor)) {
1620 next if !$form->{"delivery_${vc}_id"};
1621 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1624 # get printed, emailed
1625 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1626 $sth = prepare_execute_query($form, $dbh, $query, $id);
1628 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1629 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1630 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1631 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1634 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1636 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1637 : $form->{invdate} ? $dbh->quote($form->{invdate})
1641 my $taxzone_id = $form->{taxzone_id} *= 1;
1642 $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
1644 # retrieve individual items
1647 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1648 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1649 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1652 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1653 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,
1654 i.price_factor_id, i.price_factor, i.marge_price_factor,
1655 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1656 pr.projectnumber, pg.partsgroup, prg.pricegroup
1659 LEFT JOIN parts p ON (i.parts_id = p.id)
1660 LEFT JOIN project pr ON (i.project_id = pr.id)
1661 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1662 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1664 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1665 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
1666 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
1668 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1670 $sth = prepare_execute_query($form, $dbh, $query, $id);
1672 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1673 # Retrieve custom variables.
1674 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1676 sub_module => 'invoice',
1677 trans_id => $ref->{invoice_id},
1679 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1680 delete $ref->{invoice_id};
1682 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1683 delete($ref->{"part_inventory_accno_id"});
1685 foreach my $type (qw(inventory income expense)) {
1686 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1687 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1688 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1692 # get tax rates and description
1693 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1695 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1696 LEFT JOIN chart c ON (c.id = t.chart_id)
1698 (SELECT tk.tax_id FROM taxkeys tk
1699 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1700 AND startdate <= date($transdate)
1701 ORDER BY startdate DESC LIMIT 1)
1703 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1704 $ref->{taxaccounts} = "";
1706 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1708 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1712 $ref->{taxaccounts} .= "$ptr->{accno} ";
1714 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1715 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1716 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1717 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1718 $form->{taxaccounts} .= "$ptr->{accno} ";
1723 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1725 chop $ref->{taxaccounts};
1726 push @{ $form->{invoice_details} }, $ref;
1731 Common::webdav_folder($form);
1734 my $rc = $dbh->commit;
1736 $main::lxdebug->leave_sub();
1742 $main::lxdebug->enter_sub();
1744 my ($self, $myconfig, $form) = @_;
1746 # connect to database
1747 my $dbh = $form->get_standard_dbh;
1749 my $dateformat = $myconfig->{dateformat};
1750 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1752 my (@values, $duedate, $ref, $query);
1754 if ($form->{invdate}) {
1755 $duedate = "to_date(?, '$dateformat')";
1756 push @values, $form->{invdate};
1758 $duedate = "current_date";
1761 my $cid = conv_i($form->{customer_id});
1764 if ($form->{payment_id}) {
1765 $payment_id = "(pt.id = ?) OR";
1766 push @values, conv_i($form->{payment_id});
1772 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1773 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1774 c.street, c.zipcode, c.city, c.country,
1775 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1776 c.taxincluded_checked, c.direct_debit,
1777 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1778 b.discount AS tradediscount, b.description AS business
1780 LEFT JOIN business b ON (b.id = c.business_id)
1781 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1782 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1785 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1787 delete $ref->{salesman_id} if !$ref->{salesman_id};
1789 map { $form->{$_} = $ref->{$_} } keys %$ref;
1791 # use customer currency
1792 $form->{currency} = $form->{curr};
1795 qq|SELECT sum(amount - paid) AS dunning_amount
1797 WHERE (paid < amount)
1798 AND (customer_id = ?)
1799 AND (dunning_config_id IS NOT NULL)|;
1800 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1801 map { $form->{$_} = $ref->{$_} } keys %$ref;
1804 qq|SELECT dnn.dunning_description AS max_dunning_level
1805 FROM dunning_config dnn
1806 WHERE id IN (SELECT dunning_config_id
1808 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1809 ORDER BY dunning_level DESC LIMIT 1|;
1810 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1811 map { $form->{$_} = $ref->{$_} } keys %$ref;
1813 $form->{creditremaining} = $form->{creditlimit};
1814 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1815 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1816 $form->{creditremaining} -= $value;
1820 (SELECT e.buy FROM exchangerate e
1821 WHERE e.currency_id = o.currency_id
1822 AND e.transdate = o.transdate)
1824 WHERE o.customer_id = ?
1825 AND o.quotation = '0'
1826 AND o.closed = '0'|;
1827 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1829 while (my ($amount, $exch) = $sth->fetchrow_array) {
1830 $exch = 1 unless $exch;
1831 $form->{creditremaining} -= $amount * $exch;
1835 # setup last accounts used for this customer
1836 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1838 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1840 JOIN acc_trans ac ON (ac.chart_id = c.id)
1841 JOIN ar a ON (a.id = ac.trans_id)
1842 WHERE a.customer_id = ?
1843 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1844 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1845 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1848 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1849 if ($ref->{category} eq 'I') {
1851 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1853 if ($form->{initial_transdate}) {
1855 qq|SELECT tk.tax_id, t.rate
1857 LEFT JOIN tax t ON tk.tax_id = t.id
1858 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1859 ORDER BY tk.startdate DESC
1861 my ($tax_id, $rate) =
1862 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1863 $form->{initial_transdate});
1864 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1867 if ($ref->{category} eq 'A') {
1868 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1872 $form->{rowcount} = $i if ($i && !$form->{type});
1875 $main::lxdebug->leave_sub();
1879 $main::lxdebug->enter_sub();
1881 my ($self, $myconfig, $form) = @_;
1883 # connect to database
1884 my $dbh = $form->get_standard_dbh;
1886 my $i = $form->{rowcount};
1888 my $where = qq|NOT p.obsolete = '1'|;
1891 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1892 my ($table, $field) = split m/\./, $column;
1893 next if !$form->{"${field}_${i}"};
1894 $where .= qq| AND lower(${column}) ILIKE ?|;
1895 push @values, '%' . $form->{"${field}_${i}"} . '%';
1898 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1899 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1900 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1901 push @values, $form->{"partnumber_$i"};
1904 # Search for part ID overrides all other criteria.
1905 if ($form->{"id_${i}"}) {
1906 $where = qq|p.id = ?|;
1907 @values = ($form->{"id_${i}"});
1910 if ($form->{"description_$i"}) {
1911 $where .= qq| ORDER BY p.description|;
1913 $where .= qq| ORDER BY p.partnumber|;
1917 if ($form->{type} eq "invoice") {
1919 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1920 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1924 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1928 my $taxzone_id = $form->{taxzone_id} * 1;
1929 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1933 p.id, p.partnumber, p.description, p.sellprice,
1934 p.listprice, p.inventory_accno_id, p.lastcost,
1936 c1.accno AS inventory_accno,
1937 c1.new_chart_id AS inventory_new_chart,
1938 date($transdate) - c1.valid_from AS inventory_valid,
1940 c2.accno AS income_accno,
1941 c2.new_chart_id AS income_new_chart,
1942 date($transdate) - c2.valid_from AS income_valid,
1944 c3.accno AS expense_accno,
1945 c3.new_chart_id AS expense_new_chart,
1946 date($transdate) - c3.valid_from AS expense_valid,
1948 p.unit, p.assembly, p.onhand,
1949 p.notes AS partnotes, p.notes AS longdescription,
1950 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1951 p.price_factor_id, p.weight,
1953 pfac.factor AS price_factor,
1958 LEFT JOIN chart c1 ON
1959 ((SELECT inventory_accno_id
1960 FROM buchungsgruppen
1961 WHERE id = p.buchungsgruppen_id) = c1.id)
1962 LEFT JOIN chart c2 ON
1963 ((SELECT tc.income_accno_id
1964 FROM taxzone_charts tc
1965 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
1966 LEFT JOIN chart c3 ON
1967 ((SELECT tc.expense_accno_id
1968 FROM taxzone_charts tc
1969 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
1970 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1971 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1973 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1975 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1977 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1978 [ qq|SELECT tr.translation, tr.longdescription
1980 WHERE tr.language_id IN
1983 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1986 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1988 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1990 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1991 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1992 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1993 if (!$ref->{inventory_accno_id}) {
1994 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1996 delete($ref->{inventory_accno_id});
1998 foreach my $type (qw(inventory income expense)) {
1999 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2001 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2004 ($ref->{"${type}_accno"},
2005 $ref->{"${type}_new_chart"},
2006 $ref->{"${type}_valid"})
2007 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2011 if ($form->{payment_id} eq "") {
2012 $form->{payment_id} = $form->{part_payment_id};
2015 # get tax rates and description
2016 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2018 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2020 LEFT JOIN chart c ON (c.id = t.chart_id)
2024 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2026 ORDER BY startdate DESC
2029 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2030 my $stw = $dbh->prepare($query);
2031 $stw->execute(@values) || $form->dberror($query);
2033 $ref->{taxaccounts} = "";
2035 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2037 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2041 $ref->{taxaccounts} .= "$ptr->{accno} ";
2043 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2044 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2045 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2046 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2047 $form->{taxaccounts} .= "$ptr->{accno} ";
2053 chop $ref->{taxaccounts};
2055 if ($form->{language_id}) {
2056 for my $spec (@translation_queries) {
2057 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2058 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2059 next unless $translation;
2060 $ref->{description} = $translation;
2061 $ref->{longdescription} = $longdescription;
2066 $ref->{onhand} *= 1;
2068 push @{ $form->{item_list} }, $ref;
2071 $_->[1]->finish for @translation_queries;
2073 foreach my $item (@{ $form->{item_list} }) {
2074 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2075 trans_id => $item->{id},
2079 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2082 $main::lxdebug->leave_sub();
2085 ##########################
2086 # get pricegroups from database
2087 # build up selected pricegroup
2088 # if an exchange rate - change price
2091 sub get_pricegroups_for_parts {
2093 $main::lxdebug->enter_sub();
2095 my ($self, $myconfig, $form) = @_;
2097 my $dbh = $form->get_standard_dbh;
2099 $form->{"PRICES"} = {};
2103 my $all_units = AM->retrieve_units($myconfig, $form);
2104 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2105 $form->{"PRICES"}{$i} = [];
2107 $id = $form->{"id_$i"};
2109 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2110 $id = $form->{"new_id_$i"};
2113 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2115 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2117 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2118 my $sellprice = $form->{"sellprice_$i"};
2119 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2120 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2121 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2123 my $price_new = $form->{"price_new_$i"};
2124 my $price_old = $form->{"price_old_$i"};
2126 if (!$form->{"unit_old_$i"}) {
2127 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2128 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2129 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2130 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2133 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2134 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2135 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2137 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2138 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2139 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2140 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2141 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2142 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2143 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2148 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2149 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2150 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2151 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2152 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2156 if (!$form->{"basefactor_$i"}) {
2157 $form->{"basefactor_$i"} = 1;
2163 sellprice AS default_sellprice,
2166 'selected' AS selected
2172 parts.sellprice AS default_sellprice,
2173 pricegroup.pricegroup,
2177 LEFT JOIN parts ON parts.id = parts_id
2178 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2180 ORDER BY pricegroup|;
2181 my @values = (conv_i($id), conv_i($id));
2182 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2184 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2186 $pkr->{selected} = '';
2188 # if there is an exchange rate change price
2189 if (($form->{exchangerate} * 1) != 0) {
2190 $pkr->{price} /= $form->{exchangerate};
2193 $pkr->{price} *= $form->{"basefactor_$i"};
2194 $pkr->{price} *= $basefactor;
2195 $pkr->{price_ufmt} = $pkr->{price};
2196 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2198 if (!defined $selectedpricegroup_id) {
2199 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2200 # Case A: open old invoice, no pricegroup selected
2201 # Case B: add new article to invoice, no pricegroup selected
2203 # to distinguish case A and B the variable pricegroup_id_$i is used
2204 # for new articles this variable isn't defined, for loaded articles it is
2205 # sellprice can't be used, as it already has 0,00 set
2207 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2209 $pkr->{selected} = ' selected';
2210 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2211 and not defined $form->{"pricegroup_id_$i"}
2212 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2213 # for the case where pricegroup prices haven't been set
2215 # Case B: use default pricegroup of customer
2217 $pkr->{selected} = ' selected'; # unless $form->{selected};
2218 # no customer pricesgroup set
2219 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2221 $pkr->{price} = $form->{"sellprice_$i"};
2225 # this sub should not set anything and only return. --sschoeling, 20090506
2226 # is this correct? put in again... -- grichardson 20110119
2227 $form->{"sellprice_$i"} = $pkr->{price};
2230 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2231 $pkr->{price} = $form->{"sellprice_$i"};
2232 $pkr->{selected} = ' selected';
2236 # existing article: pricegroup or price changed
2237 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2238 if ($selectedpricegroup_id ne $pricegroup_old) {
2239 # pricegroup has changed
2240 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2241 $pkr->{selected} = ' selected';
2243 } elsif ( ($form->parse_amount($myconfig, $price_new)
2244 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2245 and ($price_new ne 0) and defined $price_new) {
2246 # sellprice has changed
2247 # when loading existing invoices $price_new is NULL
2248 if ($pkr->{pricegroup_id} == 0) {
2249 $pkr->{price} = $form->{"sellprice_$i"};
2250 $pkr->{selected} = ' selected';
2252 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2253 # neither sellprice nor pricegroup changed
2254 $pkr->{selected} = ' selected';
2255 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2256 # $pkr->{price} = $form->{"sellprice_$i"};
2258 $pkr->{price} = $form->{"sellprice_$i"};
2262 push @{ $form->{PRICES}{$i} }, $pkr;
2265 $form->{"basefactor_$i"} *= $basefactor;
2272 $main::lxdebug->leave_sub();
2276 $main::lxdebug->enter_sub();
2278 my ($self, $myconfig, $form, $table) = @_;
2280 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2282 # make sure there's no funny stuff in $table
2283 # ToDO: die when this happens and throw an error
2284 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2286 my $dbh = $form->get_standard_dbh;
2288 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2289 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2291 $main::lxdebug->leave_sub();
2297 $main::lxdebug->enter_sub();
2299 my ($self, $myconfig, $form, $table, $id) = @_;
2301 $main::lxdebug->leave_sub() and return 0 unless ($id);
2303 # make sure there's no funny stuff in $table
2304 # ToDO: die when this happens and throw an error
2305 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2307 my $dbh = $form->get_standard_dbh;
2309 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2310 my ($result) = selectrow_query($form, $dbh, $query, $id);
2312 $main::lxdebug->leave_sub();
2317 sub get_standard_accno_current_assets {
2318 $main::lxdebug->enter_sub();
2320 my ($self, $myconfig, $form) = @_;
2322 my $dbh = $form->get_standard_dbh;
2324 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2325 my ($result) = selectrow_query($form, $dbh, $query);
2327 $main::lxdebug->leave_sub();