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, $decimalplaces);
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);
495 # we have no values, probably a invalid contact person. hotfix and first idea for issue #10
497 my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id});
499 $ref->{name} = $customer->name;
500 $ref->{street} = $customer->street;
501 $ref->{zipcode} = $customer->zipcode;
502 $ref->{country} = $customer->country;
504 my $contact = SL::DB::Manager::Contact->find_by(cp_id => $::form->{cp_id});
506 $ref->{cp_name} = $contact->cp_name;
507 $ref->{cp_givenname} = $contact->cp_givenname;
508 $ref->{cp_gender} = $contact->cp_gender;
511 # remove id and taxincluded before copy back
512 delete @$ref{qw(id taxincluded)};
514 @wanted_vars = grep({ $_ } @wanted_vars);
515 if (scalar(@wanted_vars) > 0) {
517 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
518 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
521 map { $form->{$_} = $ref->{$_} } keys %$ref;
523 if ($form->{delivery_customer_id}) {
525 qq|SELECT *, notes as customernotes
529 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
531 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
534 if ($form->{delivery_vendor_id}) {
536 qq|SELECT *, notes as customernotes
540 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
542 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
545 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
547 'trans_id' => $form->{customer_id});
548 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
550 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
551 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
552 'language_id' => $language_id,
553 'allow_fallback' => 1);
556 $main::lxdebug->leave_sub();
560 $main::lxdebug->enter_sub();
562 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
564 # connect to database, turn off autocommit
565 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
566 my $restricter = SL::HTML::Restrict->create;
568 my ($query, $sth, $null, $project_id, @values);
569 my $exchangerate = 0;
571 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
574 if (!$form->{employee_id}) {
575 $form->get_employee($dbh);
578 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
579 my $defaultcurrency = $form->{defaultcurrency};
581 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
582 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
583 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
584 if (!$form->{department_id}){
585 ($null, $form->{department_id}) = split(/--/, $form->{department});
588 my $all_units = AM->retrieve_units($myconfig, $form);
590 if (!$payments_only) {
592 &reverse_invoice($dbh, $form);
595 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
596 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
598 $query = qq|SELECT nextval('glid')|;
599 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
601 $query = qq|INSERT INTO ar (id, invnumber, currency_id, taxzone_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?), ?)|;
602 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}, $form->{taxzone_id});
604 if (!$form->{invnumber}) {
605 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
606 $form->{invnumber} = $trans_number->create_unique;
611 my ($netamount, $invoicediff) = (0, 0);
612 my ($amount, $linetotal, $lastincomeaccno);
614 if ($form->{currency} eq $defaultcurrency) {
615 $form->{exchangerate} = 1;
617 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
620 $form->{exchangerate} =
623 : $form->parse_amount($myconfig, $form->{exchangerate});
625 $form->{expense_inventory} = "";
629 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
630 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
633 $form->{amount} = {};
634 $form->{amount_cogs} = {};
636 foreach my $i (1 .. $form->{rowcount}) {
637 if ($form->{type} eq "credit_note") {
638 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
639 $form->{shipped} = 1;
641 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
646 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
647 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
648 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
650 if ($form->{storno}) {
651 $form->{"qty_$i"} *= -1;
654 if ($form->{"id_$i"}) {
657 if (defined($baseunits{$form->{"id_$i"}})) {
658 $item_unit = $baseunits{$form->{"id_$i"}};
661 $query = qq|SELECT unit FROM parts WHERE id = ?|;
662 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
663 $baseunits{$form->{"id_$i"}} = $item_unit;
666 if (defined($all_units->{$item_unit}->{factor})
667 && ($all_units->{$item_unit}->{factor} ne '')
668 && ($all_units->{$item_unit}->{factor} != 0)) {
669 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
673 $baseqty = $form->{"qty_$i"} * $basefactor;
675 my ($allocated, $taxrate) = (0, 0);
679 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
681 # keep entered selling price
683 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
685 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
687 my $decimalplaces = ($dec > 2) ? $dec : 2;
689 # undo discount formatting
690 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
693 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
695 # round linetotal to 2 decimal places
696 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
697 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
699 if ($form->{taxincluded}) {
700 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
701 $form->{"sellprice_$i"} =
702 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
704 $taxamount = $linetotal * $taxrate;
707 $netamount += $linetotal;
709 if ($taxamount != 0) {
711 $form->{amount}{ $form->{id} }{$_} +=
712 $taxamount * $form->{"${_}_rate"} / $taxrate
713 } split(/ /, $form->{"taxaccounts_$i"});
716 # add amount to income, $form->{amount}{trans_id}{accno}
717 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
719 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
720 $linetotal = $form->round_amount($linetotal, 2);
722 # this is the difference from the inventory
723 $invoicediff += ($amount - $linetotal);
725 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
728 $lastincomeaccno = $form->{"income_accno_$i"};
730 # adjust and round sellprice
731 $form->{"sellprice_$i"} =
732 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
735 next if $payments_only;
737 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
739 if ($form->{"assembly_$i"}) {
740 # record assembly item as allocated
741 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
744 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
748 # Get pricegroup_id and save it. Unfortunately the interface
749 # also uses ID "0" for signalling that none is selected, but "0"
750 # must not be stored in the database. Therefore we cannot simply
752 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
754 $pricegroup_id = undef if !$pricegroup_id;
756 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
758 # save detail record in invoice table
760 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
761 sellprice, fxsellprice, discount, allocated, assemblyitem,
762 unit, deliverydate, project_id, serialnumber, pricegroup_id,
763 ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
764 marge_percent, marge_total, lastcost,
765 price_factor_id, price_factor, marge_price_factor)
766 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
767 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
769 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
770 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
771 $form->{"sellprice_$i"}, $fxsellprice,
772 $form->{"discount_$i"}, $allocated, 'f',
773 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
774 $form->{"serialnumber_$i"}, $pricegroup_id,
775 $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
776 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
777 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
778 $form->{"lastcost_$i"},
779 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
780 conv_i($form->{"marge_price_factor_$i"}));
781 do_query($form, $dbh, $query, @values);
783 CVar->save_custom_variables(module => 'IC',
784 sub_module => 'invoice',
785 trans_id => $invoice_id,
786 configs => $ic_cvar_configs,
788 name_prefix => 'ic_',
789 name_postfix => "_$i",
794 # total payments, don't move we need it here
795 for my $i (1 .. $form->{paidaccounts}) {
796 if ($form->{type} eq "credit_note") {
797 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
799 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
801 $form->{paid} += $form->{"paid_$i"};
802 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
805 my ($tax, $diff) = (0, 0);
807 $netamount = $form->round_amount($netamount, 2);
809 # figure out rounding errors for total amount vs netamount + taxes
810 if ($form->{taxincluded}) {
812 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
813 $diff += $amount - $netamount * $form->{exchangerate};
814 $netamount = $amount;
816 foreach my $item (split(/ /, $form->{taxaccounts})) {
817 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
818 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
819 $tax += $form->{amount}{ $form->{id} }{$item};
820 $netamount -= $form->{amount}{ $form->{id} }{$item};
823 $invoicediff += $diff;
824 ######## this only applies to tax included
825 if ($lastincomeaccno) {
826 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
830 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
831 $diff = $amount - $netamount * $form->{exchangerate};
832 $netamount = $amount;
833 foreach my $item (split(/ /, $form->{taxaccounts})) {
834 $form->{amount}{ $form->{id} }{$item} =
835 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
838 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
841 $amount - $form->{amount}{ $form->{id} }{$item} *
842 $form->{exchangerate};
843 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
844 $tax += $form->{amount}{ $form->{id} }{$item};
848 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
850 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
853 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
855 # update exchangerate
856 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
857 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
858 $form->{exchangerate}, 0);
861 $project_id = conv_i($form->{"globalproject_id"});
862 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
863 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
865 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
866 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
867 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
869 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
871 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
873 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
874 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
875 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
876 do_query($form, $dbh, $query, @values);
877 $form->{amount_cogs}{$trans_id}{$accno} = 0;
881 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
882 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
884 if (!$payments_only && ($form->{amount_cogs}{$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 = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
888 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
889 do_query($form, $dbh, $query, @values);
894 foreach my $trans_id (keys %{ $form->{amount} }) {
895 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
896 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
898 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
900 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
902 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
903 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
906 WHERE chart_id= (SELECT id
910 ORDER BY startdate DESC LIMIT 1),
913 WHERE chart_id= (SELECT id
917 ORDER BY startdate DESC LIMIT 1),
919 (SELECT link FROM chart WHERE accno = ?))|;
920 @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);
921 do_query($form, $dbh, $query, @values);
922 $form->{amount}{$trans_id}{$accno} = 0;
926 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
927 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
929 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
931 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
932 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
935 WHERE chart_id= (SELECT id
939 ORDER BY startdate DESC LIMIT 1),
942 WHERE chart_id= (SELECT id
946 ORDER BY startdate DESC LIMIT 1),
948 (SELECT link FROM chart WHERE accno = ?))|;
949 @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);
950 do_query($form, $dbh, $query, @values);
955 # deduct payment differences from diff
956 for my $i (1 .. $form->{paidaccounts}) {
957 if ($form->{"paid_$i"} != 0) {
959 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
960 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
964 # record payments and offsetting AR
965 if (!$form->{storno}) {
966 for my $i (1 .. $form->{paidaccounts}) {
968 if ($form->{"acc_trans_id_$i"}
970 && (SL::DB::Default->get->payments_changeable == 0)) {
974 next if ($form->{"paid_$i"} == 0);
976 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
977 $form->{"datepaid_$i"} = $form->{invdate}
978 unless ($form->{"datepaid_$i"});
979 $form->{datepaid} = $form->{"datepaid_$i"};
983 if ($form->{currency} eq $defaultcurrency) {
984 $form->{"exchangerate_$i"} = 1;
986 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
987 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
991 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
993 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
995 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
996 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
999 WHERE chart_id= (SELECT id
1003 ORDER BY startdate DESC LIMIT 1),
1006 WHERE chart_id= (SELECT id
1010 ORDER BY startdate DESC LIMIT 1),
1012 (SELECT link FROM chart WHERE accno = ?))|;
1013 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
1014 do_query($form, $dbh, $query, @values);
1018 $form->{"paid_$i"} *= -1;
1019 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1022 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1023 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1026 WHERE chart_id= (SELECT id
1030 ORDER BY startdate DESC LIMIT 1),
1033 WHERE chart_id= (SELECT id
1037 ORDER BY startdate DESC LIMIT 1),
1039 (SELECT link FROM chart WHERE accno = ?))|;
1040 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1041 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1042 do_query($form, $dbh, $query, @values);
1044 # exchangerate difference
1045 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1046 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1050 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1051 $form->{"exchangerate_$i"};
1053 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1055 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1060 # update exchange rate
1061 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1062 $form->update_exchangerate($dbh, $form->{currency},
1063 $form->{"datepaid_$i"},
1064 $form->{"exchangerate_$i"}, 0);
1068 } else { # if (!$form->{storno})
1069 $form->{marge_total} *= -1;
1072 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1074 # record exchange rate differences and gains/losses
1075 foreach my $accno (keys %{ $form->{fx} }) {
1076 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1077 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1078 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1081 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1082 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1085 WHERE chart_id= (SELECT id
1089 ORDER BY startdate DESC LIMIT 1),
1092 WHERE chart_id= (SELECT id
1096 ORDER BY startdate DESC LIMIT 1),
1098 (SELECT link FROM chart WHERE accno = ?))|;
1099 @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);
1100 do_query($form, $dbh, $query, @values);
1105 if ($payments_only) {
1106 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1107 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1109 $dbh->commit if !$provided_dbh;
1111 $main::lxdebug->leave_sub();
1115 $amount = $netamount + $tax;
1118 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1120 $query = qq|UPDATE ar set
1121 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1122 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1123 amount = ?, netamount = ?, paid = ?,
1124 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1125 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1126 currency_id = (SELECT id FROM currencies WHERE name = ?),
1127 department_id = ?, payment_id = ?, taxincluded = ?,
1128 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1129 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1130 cp_id = ?, marge_total = ?, marge_percent = ?,
1131 globalproject_id = ?, delivery_customer_id = ?,
1132 transaction_description = ?, delivery_vendor_id = ?,
1133 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1134 delivery_term_id = ?
1136 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1137 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1138 $amount, $netamount, $form->{"paid"},
1139 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1140 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1141 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1142 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1143 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1144 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1145 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1146 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1147 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1148 conv_i($form->{delivery_term_id}),
1149 conv_i($form->{"id"}));
1150 do_query($form, $dbh, $query, @values);
1153 if ($form->{storno}) {
1156 paid = paid + amount,
1158 intnotes = ? || intnotes
1160 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1161 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1164 $form->{name} = $form->{customer};
1165 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1168 if (!$form->{shipto_id}) {
1169 $form->add_shipto($dbh, $form->{id}, "AR");
1172 # save printed, emailed and queued
1173 $form->save_status($dbh);
1175 Common::webdav_folder($form);
1177 if ($form->{convert_from_ar_ids}) {
1178 RecordLinks->create_links('dbh' => $dbh,
1180 'from_table' => 'ar',
1181 'from_ids' => $form->{convert_from_ar_ids},
1183 'to_id' => $form->{id},
1185 delete $form->{convert_from_ar_ids};
1188 # Link this record to the records it was created from.
1189 RecordLinks->create_links('dbh' => $dbh,
1191 'from_table' => 'oe',
1192 'from_ids' => $form->{convert_from_oe_ids},
1194 'to_id' => $form->{id},
1196 delete $form->{convert_from_oe_ids};
1198 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1200 if (scalar @convert_from_do_ids) {
1201 DO->close_orders('dbh' => $dbh,
1202 'ids' => \@convert_from_do_ids);
1204 RecordLinks->create_links('dbh' => $dbh,
1206 'from_table' => 'delivery_orders',
1207 'from_ids' => \@convert_from_do_ids,
1209 'to_id' => $form->{id},
1212 delete $form->{convert_from_do_ids};
1214 ARAP->close_orders_if_billed('dbh' => $dbh,
1215 'arap_id' => $form->{id},
1218 # safety check datev export
1219 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1220 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1221 $transdate ||= DateTime->today;
1223 my $datev = SL::DATEV->new(
1224 exporttype => DATEV_ET_BUCHUNGEN,
1225 format => DATEV_FORMAT_KNE,
1229 trans_id => $form->{id},
1234 if ($datev->errors) {
1236 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1241 $dbh->commit if !$provided_dbh;
1243 $main::lxdebug->leave_sub();
1248 sub _delete_payments {
1249 $main::lxdebug->enter_sub();
1251 my ($self, $form, $dbh) = @_;
1253 my @delete_acc_trans_ids;
1255 # Delete old payment entries from acc_trans.
1257 qq|SELECT acc_trans_id
1259 WHERE (trans_id = ?) AND fx_transaction
1263 SELECT at.acc_trans_id
1265 LEFT JOIN chart c ON (at.chart_id = c.id)
1266 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1267 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1270 qq|SELECT at.acc_trans_id
1272 LEFT JOIN chart c ON (at.chart_id = c.id)
1273 WHERE (trans_id = ?)
1274 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1275 ORDER BY at.acc_trans_id
1277 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1279 if (@delete_acc_trans_ids) {
1280 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1281 do_query($form, $dbh, $query);
1284 $main::lxdebug->leave_sub();
1288 $main::lxdebug->enter_sub();
1290 my ($self, $myconfig, $form, $locale) = @_;
1292 # connect to database, turn off autocommit
1293 my $dbh = $form->get_standard_dbh;
1295 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1297 $old_form = save_form();
1299 # Delete all entries in acc_trans from prior payments.
1300 if (SL::DB::Default->get->payments_changeable != 0) {
1301 $self->_delete_payments($form, $dbh);
1304 # Save the new payments the user made before cleaning up $form.
1305 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 };
1307 # Clean up $form so that old content won't tamper the results.
1308 %keep_vars = map { $_, 1 } qw(login password id);
1309 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1311 # Retrieve the invoice from the database.
1312 $self->retrieve_invoice($myconfig, $form);
1314 # Set up the content of $form in the way that IS::post_invoice() expects.
1315 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1317 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1318 $item = $form->{invoice_details}->[$row - 1];
1320 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1322 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1325 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1327 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1329 # Restore the payment options from the user input.
1330 map { $form->{$_} = $payments{$_} } keys %payments;
1332 # Get the AR accno (which is normally done by Form::create_links()).
1336 LEFT JOIN chart c ON (at.chart_id = c.id)
1337 WHERE (trans_id = ?)
1338 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1339 ORDER BY at.acc_trans_id
1342 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1344 # Post the new payments.
1345 $self->post_invoice($myconfig, $form, $dbh, 1);
1347 restore_form($old_form);
1349 my $rc = $dbh->commit();
1351 $main::lxdebug->leave_sub();
1356 sub process_assembly {
1357 $main::lxdebug->enter_sub();
1359 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1362 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1363 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1365 JOIN parts p ON (a.parts_id = p.id)
1367 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1369 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1373 $ref->{inventory_accno_id} *= 1;
1374 $ref->{expense_accno_id} *= 1;
1376 # multiply by number of assemblies
1377 $ref->{qty} *= $totalqty;
1379 if ($ref->{assembly}) {
1380 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1383 if ($ref->{inventory_accno_id}) {
1384 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1388 # save detail record for individual assembly item in invoice table
1390 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1391 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1392 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1393 do_query($form, $dbh, $query, @values);
1399 $main::lxdebug->leave_sub();
1403 $main::lxdebug->enter_sub();
1405 # adjust allocated in table invoice according to FIFO princicple
1406 # for a certain part with part_id $id
1408 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1412 $form->{taxzone_id} *=1;
1413 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1414 my $taxzone_id = $form->{"taxzone_id"} * 1;
1416 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1417 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1418 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1419 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1420 FROM invoice i, parts p
1421 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1422 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)
1423 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)
1424 WHERE (i.parts_id = p.id)
1425 AND (i.parts_id = ?)
1426 AND ((i.base_qty + i.allocated) < 0)
1428 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1433 # all invoice entries of an example part:
1435 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1436 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1437 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1438 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1439 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1440 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1441 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1443 # 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
1444 # and all parts have been allocated
1446 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1447 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1449 # in this example there are still 4 unsold articles
1452 # search all invoice entries for the part in question, adjusting "allocated"
1453 # until the total number of sold parts has been reached
1455 # ORDER BY trans_id ensures FIFO
1458 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1459 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1463 # update allocated in invoice
1464 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1466 # total expenses and inventory
1467 # sellprice is the cost of the item
1468 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1470 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1471 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1472 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1474 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1475 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1476 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1478 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1479 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1485 last if (($totalqty -= $qty) <= 0);
1490 $main::lxdebug->leave_sub();
1495 sub reverse_invoice {
1496 $main::lxdebug->enter_sub();
1498 my ($dbh, $form) = @_;
1500 # reverse inventory items
1502 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1504 JOIN parts p ON (i.parts_id = p.id)
1505 WHERE i.trans_id = ?|;
1506 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1508 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1510 if ($ref->{inventory_accno_id}) {
1511 # de-allocated purchases
1513 qq|SELECT i.id, i.trans_id, i.allocated
1515 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1516 ORDER BY i.trans_id DESC|;
1517 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1519 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1520 my $qty = $ref->{qty};
1521 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1522 $qty = $inhref->{allocated};
1526 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1528 last if (($ref->{qty} -= $qty) <= 0);
1537 my @values = (conv_i($form->{id}));
1538 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1539 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1540 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1542 $main::lxdebug->leave_sub();
1545 sub delete_invoice {
1546 $main::lxdebug->enter_sub();
1548 my ($self, $myconfig, $form) = @_;
1550 # connect to database
1551 my $dbh = $form->get_standard_dbh;
1553 &reverse_invoice($dbh, $form);
1555 my @values = (conv_i($form->{id}));
1557 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1558 # zurückgesetzt werden. Vgl:
1559 # id | storno | storno_id | paid | amount
1560 #----+--------+-----------+---------+-----------
1561 # 18 | f | | 0.00000 | 119.00000
1563 # 18 | t | | 119.00000 | 119.00000
1565 if($form->{storno}){
1566 # storno_id auslesen und korrigieren
1567 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1568 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1571 # delete spool files
1572 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1575 qq|DELETE FROM status WHERE trans_id = ?|,
1576 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1577 qq|DELETE FROM ar WHERE id = ?|,
1580 map { do_query($form, $dbh, $_, @values) } @queries;
1582 my $rc = $dbh->commit;
1585 my $spool = $::lx_office_conf{paths}->{spool};
1586 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1589 $main::lxdebug->leave_sub();
1594 sub retrieve_invoice {
1595 $main::lxdebug->enter_sub();
1597 my ($self, $myconfig, $form) = @_;
1599 # connect to database
1600 my $dbh = $form->get_standard_dbh;
1602 my ($sth, $ref, $query);
1604 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1608 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1609 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1610 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1611 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1612 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1616 $ref = selectfirst_hashref_query($form, $dbh, $query);
1617 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1620 my $id = conv_i($form->{id});
1623 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1627 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1628 a.orddate, a.quodate, a.globalproject_id,
1629 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1630 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1631 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1632 a.employee_id, a.salesman_id, a.payment_id,
1633 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1634 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1635 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1638 LEFT JOIN employee e ON (e.id = a.employee_id)
1640 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1641 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1643 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1645 foreach my $vc (qw(customer vendor)) {
1646 next if !$form->{"delivery_${vc}_id"};
1647 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1650 # get printed, emailed
1651 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1652 $sth = prepare_execute_query($form, $dbh, $query, $id);
1654 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1655 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1656 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1657 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1660 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1662 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1663 : $form->{invdate} ? $dbh->quote($form->{invdate})
1667 my $taxzone_id = $form->{taxzone_id} *= 1;
1668 $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
1670 # retrieve individual items
1673 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1674 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1675 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1678 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1679 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,
1680 i.price_factor_id, i.price_factor, i.marge_price_factor,
1681 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1682 pr.projectnumber, pg.partsgroup, prg.pricegroup
1685 LEFT JOIN parts p ON (i.parts_id = p.id)
1686 LEFT JOIN project pr ON (i.project_id = pr.id)
1687 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1688 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1690 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1691 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)
1692 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)
1694 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1696 $sth = prepare_execute_query($form, $dbh, $query, $id);
1698 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1699 # Retrieve custom variables.
1700 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1702 sub_module => 'invoice',
1703 trans_id => $ref->{invoice_id},
1705 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1706 delete $ref->{invoice_id};
1708 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1709 delete($ref->{"part_inventory_accno_id"});
1711 foreach my $type (qw(inventory income expense)) {
1712 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1713 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1714 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1718 # get tax rates and description
1719 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1721 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1722 LEFT JOIN chart c ON (c.id = t.chart_id)
1724 (SELECT tk.tax_id FROM taxkeys tk
1725 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1726 AND startdate <= date($transdate)
1727 ORDER BY startdate DESC LIMIT 1)
1729 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1730 $ref->{taxaccounts} = "";
1732 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1734 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1738 $ref->{taxaccounts} .= "$ptr->{accno} ";
1740 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1741 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1742 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1743 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1744 $form->{taxaccounts} .= "$ptr->{accno} ";
1749 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1751 chop $ref->{taxaccounts};
1752 push @{ $form->{invoice_details} }, $ref;
1757 Common::webdav_folder($form);
1760 my $rc = $dbh->commit;
1762 $main::lxdebug->leave_sub();
1768 $main::lxdebug->enter_sub();
1770 my ($self, $myconfig, $form) = @_;
1772 # connect to database
1773 my $dbh = $form->get_standard_dbh;
1775 my $dateformat = $myconfig->{dateformat};
1776 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1778 my (@values, $duedate, $ref, $query);
1780 if ($form->{invdate}) {
1781 $duedate = "to_date(?, '$dateformat')";
1782 push @values, $form->{invdate};
1784 $duedate = "current_date";
1787 my $cid = conv_i($form->{customer_id});
1790 if ($form->{payment_id}) {
1791 $payment_id = "(pt.id = ?) OR";
1792 push @values, conv_i($form->{payment_id});
1798 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1799 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1800 c.street, c.zipcode, c.city, c.country,
1801 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1802 c.taxincluded_checked, c.direct_debit,
1803 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1804 b.discount AS tradediscount, b.description AS business
1806 LEFT JOIN business b ON (b.id = c.business_id)
1807 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1808 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1811 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1813 delete $ref->{salesman_id} if !$ref->{salesman_id};
1815 map { $form->{$_} = $ref->{$_} } keys %$ref;
1817 # use customer currency
1818 $form->{currency} = $form->{curr};
1821 qq|SELECT sum(amount - paid) AS dunning_amount
1823 WHERE (paid < amount)
1824 AND (customer_id = ?)
1825 AND (dunning_config_id IS NOT NULL)|;
1826 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1827 map { $form->{$_} = $ref->{$_} } keys %$ref;
1830 qq|SELECT dnn.dunning_description AS max_dunning_level
1831 FROM dunning_config dnn
1832 WHERE id IN (SELECT dunning_config_id
1834 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1835 ORDER BY dunning_level DESC LIMIT 1|;
1836 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1837 map { $form->{$_} = $ref->{$_} } keys %$ref;
1839 $form->{creditremaining} = $form->{creditlimit};
1840 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1841 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1842 $form->{creditremaining} -= $value;
1846 (SELECT e.buy FROM exchangerate e
1847 WHERE e.currency_id = o.currency_id
1848 AND e.transdate = o.transdate)
1850 WHERE o.customer_id = ?
1851 AND o.quotation = '0'
1852 AND o.closed = '0'|;
1853 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1855 while (my ($amount, $exch) = $sth->fetchrow_array) {
1856 $exch = 1 unless $exch;
1857 $form->{creditremaining} -= $amount * $exch;
1861 # setup last accounts used for this customer
1862 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1864 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1866 JOIN acc_trans ac ON (ac.chart_id = c.id)
1867 JOIN ar a ON (a.id = ac.trans_id)
1868 WHERE a.customer_id = ?
1869 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1870 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1871 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1874 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1875 if ($ref->{category} eq 'I') {
1877 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1879 if ($form->{initial_transdate}) {
1881 qq|SELECT tk.tax_id, t.rate
1883 LEFT JOIN tax t ON tk.tax_id = t.id
1884 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1885 ORDER BY tk.startdate DESC
1887 my ($tax_id, $rate) =
1888 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1889 $form->{initial_transdate});
1890 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1893 if ($ref->{category} eq 'A') {
1894 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1898 $form->{rowcount} = $i if ($i && !$form->{type});
1901 $main::lxdebug->leave_sub();
1905 $main::lxdebug->enter_sub();
1907 my ($self, $myconfig, $form) = @_;
1909 # connect to database
1910 my $dbh = $form->get_standard_dbh;
1912 my $i = $form->{rowcount};
1914 my $where = qq|NOT p.obsolete = '1'|;
1917 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1918 my ($table, $field) = split m/\./, $column;
1919 next if !$form->{"${field}_${i}"};
1920 $where .= qq| AND lower(${column}) ILIKE ?|;
1921 push @values, '%' . $form->{"${field}_${i}"} . '%';
1925 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1926 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1927 push @values, $form->{"partnumber_$i"};
1929 # also search hits in makemodels, but only cache the results by id and merge later
1931 SELECT parts_id, model FROM makemodel LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ?;
1933 my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%');
1934 my @mm_ids = map { $_->{parts_id} } @$mm_results;
1935 push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
1938 $where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|;
1939 push @values, @mm_ids;
1943 # Search for part ID overrides all other criteria.
1944 if ($form->{"id_${i}"}) {
1945 $where = qq|p.id = ?|;
1946 @values = ($form->{"id_${i}"});
1949 if ($form->{"description_$i"}) {
1950 $where .= qq| ORDER BY p.description|;
1952 $where .= qq| ORDER BY p.partnumber|;
1956 if ($form->{type} eq "invoice") {
1958 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1959 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1963 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1967 my $taxzone_id = $form->{taxzone_id} * 1;
1968 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1972 p.id, p.partnumber, p.description, p.sellprice,
1973 p.listprice, p.inventory_accno_id, p.lastcost,
1976 c1.accno AS inventory_accno,
1977 c1.new_chart_id AS inventory_new_chart,
1978 date($transdate) - c1.valid_from AS inventory_valid,
1980 c2.accno AS income_accno,
1981 c2.new_chart_id AS income_new_chart,
1982 date($transdate) - c2.valid_from AS income_valid,
1984 c3.accno AS expense_accno,
1985 c3.new_chart_id AS expense_new_chart,
1986 date($transdate) - c3.valid_from AS expense_valid,
1988 p.unit, p.assembly, p.onhand,
1989 p.notes AS partnotes, p.notes AS longdescription,
1990 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1991 p.price_factor_id, p.weight,
1993 pfac.factor AS price_factor,
1998 LEFT JOIN chart c1 ON
1999 ((SELECT inventory_accno_id
2000 FROM buchungsgruppen
2001 WHERE id = p.buchungsgruppen_id) = c1.id)
2002 LEFT JOIN chart c2 ON
2003 ((SELECT tc.income_accno_id
2004 FROM taxzone_charts tc
2005 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
2006 LEFT JOIN chart c3 ON
2007 ((SELECT tc.expense_accno_id
2008 FROM taxzone_charts tc
2009 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
2010 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
2011 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
2013 my $sth = prepare_execute_query($form, $dbh, $query, @values);
2015 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
2017 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
2018 [ qq|SELECT tr.translation, tr.longdescription
2020 WHERE tr.language_id IN
2023 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2026 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2028 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2030 if ($mm_by_id{$ref->{id}}) {
2031 $ref->{makemodels} = $mm_by_id{$ref->{id}};
2032 push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
2035 if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
2036 push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
2039 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2040 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2041 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2042 if (!$ref->{inventory_accno_id}) {
2043 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2045 delete($ref->{inventory_accno_id});
2047 foreach my $type (qw(inventory income expense)) {
2048 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2050 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2053 ($ref->{"${type}_accno"},
2054 $ref->{"${type}_new_chart"},
2055 $ref->{"${type}_valid"})
2056 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2060 if ($form->{payment_id} eq "") {
2061 $form->{payment_id} = $form->{part_payment_id};
2064 # get tax rates and description
2065 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2067 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2069 LEFT JOIN chart c ON (c.id = t.chart_id)
2073 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2075 ORDER BY startdate DESC
2078 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2079 my $stw = $dbh->prepare($query);
2080 $stw->execute(@values) || $form->dberror($query);
2082 $ref->{taxaccounts} = "";
2084 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2086 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2090 $ref->{taxaccounts} .= "$ptr->{accno} ";
2092 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2093 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2094 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2095 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2096 $form->{taxaccounts} .= "$ptr->{accno} ";
2102 chop $ref->{taxaccounts};
2104 if ($form->{language_id}) {
2105 for my $spec (@translation_queries) {
2106 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2107 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2108 next unless $translation;
2109 $ref->{description} = $translation;
2110 $ref->{longdescription} = $longdescription;
2115 $ref->{onhand} *= 1;
2117 push @{ $form->{item_list} }, $ref;
2120 $_->[1]->finish for @translation_queries;
2122 foreach my $item (@{ $form->{item_list} }) {
2123 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2124 trans_id => $item->{id},
2128 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2131 $main::lxdebug->leave_sub();
2134 ##########################
2135 # get pricegroups from database
2136 # build up selected pricegroup
2137 # if an exchange rate - change price
2140 sub get_pricegroups_for_parts {
2142 $main::lxdebug->enter_sub();
2144 my ($self, $myconfig, $form) = @_;
2146 my $dbh = $form->get_standard_dbh;
2148 $form->{"PRICES"} = {};
2152 my $all_units = AM->retrieve_units($myconfig, $form);
2153 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2154 $form->{"PRICES"}{$i} = [];
2156 $id = $form->{"id_$i"};
2158 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2159 $id = $form->{"new_id_$i"};
2162 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2164 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2166 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2167 my $sellprice = $form->{"sellprice_$i"};
2168 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2169 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2170 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2172 my $price_new = $form->{"price_new_$i"};
2173 my $price_old = $form->{"price_old_$i"};
2175 if (!$form->{"unit_old_$i"}) {
2176 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2177 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2178 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2179 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2182 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2183 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2184 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2186 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2187 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2188 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2189 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2190 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2191 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2192 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2197 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2198 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2199 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2200 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2201 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2205 if (!$form->{"basefactor_$i"}) {
2206 $form->{"basefactor_$i"} = 1;
2212 sellprice AS default_sellprice,
2215 'selected' AS selected
2221 parts.sellprice AS default_sellprice,
2222 pricegroup.pricegroup,
2226 LEFT JOIN parts ON parts.id = parts_id
2227 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2229 ORDER BY pricegroup|;
2230 my @values = (conv_i($id), conv_i($id));
2231 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2233 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2235 $pkr->{selected} = '';
2237 # if there is an exchange rate change price
2238 if (($form->{exchangerate} * 1) != 0) {
2239 $pkr->{price} /= $form->{exchangerate};
2242 $pkr->{price} *= $form->{"basefactor_$i"};
2243 $pkr->{price} *= $basefactor;
2244 $pkr->{price_ufmt} = $pkr->{price};
2245 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2247 if (!defined $selectedpricegroup_id) {
2248 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2249 # Case A: open old invoice, no pricegroup selected
2250 # Case B: add new article to invoice, no pricegroup selected
2252 # to distinguish case A and B the variable pricegroup_id_$i is used
2253 # for new articles this variable isn't defined, for loaded articles it is
2254 # sellprice can't be used, as it already has 0,00 set
2256 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2258 $pkr->{selected} = ' selected';
2259 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2260 and not defined $form->{"pricegroup_id_$i"}
2261 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2262 # for the case where pricegroup prices haven't been set
2264 # Case B: use default pricegroup of customer
2266 $pkr->{selected} = ' selected'; # unless $form->{selected};
2267 # no customer pricesgroup set
2268 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2270 $pkr->{price} = $form->{"sellprice_$i"};
2274 # this sub should not set anything and only return. --sschoeling, 20090506
2275 # is this correct? put in again... -- grichardson 20110119
2276 $form->{"sellprice_$i"} = $pkr->{price};
2279 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2280 $pkr->{price} = $form->{"sellprice_$i"};
2281 $pkr->{selected} = ' selected';
2285 # existing article: pricegroup or price changed
2286 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2287 if ($selectedpricegroup_id ne $pricegroup_old) {
2288 # pricegroup has changed
2289 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2290 $pkr->{selected} = ' selected';
2292 } elsif ( ($form->parse_amount($myconfig, $price_new)
2293 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2294 and ($price_new ne 0) and defined $price_new) {
2295 # sellprice has changed
2296 # when loading existing invoices $price_new is NULL
2297 if ($pkr->{pricegroup_id} == 0) {
2298 $pkr->{price} = $form->{"sellprice_$i"};
2299 $pkr->{selected} = ' selected';
2301 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2302 # neither sellprice nor pricegroup changed
2303 $pkr->{selected} = ' selected';
2304 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2305 # $pkr->{price} = $form->{"sellprice_$i"};
2307 $pkr->{price} = $form->{"sellprice_$i"};
2311 push @{ $form->{PRICES}{$i} }, $pkr;
2314 $form->{"basefactor_$i"} *= $basefactor;
2321 $main::lxdebug->leave_sub();
2325 $main::lxdebug->enter_sub();
2327 my ($self, $myconfig, $form, $table) = @_;
2329 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2331 # make sure there's no funny stuff in $table
2332 # ToDO: die when this happens and throw an error
2333 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2335 my $dbh = $form->get_standard_dbh;
2337 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2338 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2340 $main::lxdebug->leave_sub();
2346 $main::lxdebug->enter_sub();
2348 my ($self, $myconfig, $form, $table, $id) = @_;
2350 $main::lxdebug->leave_sub() and return 0 unless ($id);
2352 # make sure there's no funny stuff in $table
2353 # ToDO: die when this happens and throw an error
2354 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2356 my $dbh = $form->get_standard_dbh;
2358 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2359 my ($result) = selectrow_query($form, $dbh, $query, $id);
2361 $main::lxdebug->leave_sub();
2366 sub get_standard_accno_current_assets {
2367 $main::lxdebug->enter_sub();
2369 my ($self, $myconfig, $form) = @_;
2371 my $dbh = $form->get_standard_dbh;
2373 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2374 my ($result) = selectrow_query($form, $dbh, $query);
2376 $main::lxdebug->leave_sub();