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, active_price_source, active_discount_source,
766 price_factor_id, price_factor, marge_price_factor)
767 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
768 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
770 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
771 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
772 $form->{"sellprice_$i"}, $fxsellprice,
773 $form->{"discount_$i"}, $allocated, 'f',
774 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
775 $form->{"serialnumber_$i"}, $pricegroup_id,
776 $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
777 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
778 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
779 $form->{"lastcost_$i"},
780 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
781 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
782 conv_i($form->{"marge_price_factor_$i"}));
783 do_query($form, $dbh, $query, @values);
785 CVar->save_custom_variables(module => 'IC',
786 sub_module => 'invoice',
787 trans_id => $invoice_id,
788 configs => $ic_cvar_configs,
790 name_prefix => 'ic_',
791 name_postfix => "_$i",
796 # total payments, don't move we need it here
797 for my $i (1 .. $form->{paidaccounts}) {
798 if ($form->{type} eq "credit_note") {
799 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
801 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
803 $form->{paid} += $form->{"paid_$i"};
804 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
807 my ($tax, $diff) = (0, 0);
809 $netamount = $form->round_amount($netamount, 2);
811 # figure out rounding errors for total amount vs netamount + taxes
812 if ($form->{taxincluded}) {
814 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
815 $diff += $amount - $netamount * $form->{exchangerate};
816 $netamount = $amount;
818 foreach my $item (split(/ /, $form->{taxaccounts})) {
819 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
820 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
821 $tax += $form->{amount}{ $form->{id} }{$item};
822 $netamount -= $form->{amount}{ $form->{id} }{$item};
825 $invoicediff += $diff;
826 ######## this only applies to tax included
827 if ($lastincomeaccno) {
828 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
832 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
833 $diff = $amount - $netamount * $form->{exchangerate};
834 $netamount = $amount;
835 foreach my $item (split(/ /, $form->{taxaccounts})) {
836 $form->{amount}{ $form->{id} }{$item} =
837 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
840 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
843 $amount - $form->{amount}{ $form->{id} }{$item} *
844 $form->{exchangerate};
845 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
846 $tax += $form->{amount}{ $form->{id} }{$item};
850 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
852 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
855 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
857 # update exchangerate
858 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
859 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
860 $form->{exchangerate}, 0);
863 $project_id = conv_i($form->{"globalproject_id"});
864 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
865 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
867 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
868 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
869 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
871 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
873 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
875 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
876 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
877 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
878 do_query($form, $dbh, $query, @values);
879 $form->{amount_cogs}{$trans_id}{$accno} = 0;
883 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
884 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
886 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
888 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
889 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
890 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
891 do_query($form, $dbh, $query, @values);
896 foreach my $trans_id (keys %{ $form->{amount} }) {
897 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
898 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
900 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
902 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
904 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
905 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
908 WHERE chart_id= (SELECT id
912 ORDER BY startdate DESC LIMIT 1),
915 WHERE chart_id= (SELECT id
919 ORDER BY startdate DESC LIMIT 1),
921 (SELECT link FROM chart WHERE accno = ?))|;
922 @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);
923 do_query($form, $dbh, $query, @values);
924 $form->{amount}{$trans_id}{$accno} = 0;
928 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
929 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
931 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
933 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
934 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
937 WHERE chart_id= (SELECT id
941 ORDER BY startdate DESC LIMIT 1),
944 WHERE chart_id= (SELECT id
948 ORDER BY startdate DESC LIMIT 1),
950 (SELECT link FROM chart WHERE accno = ?))|;
951 @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);
952 do_query($form, $dbh, $query, @values);
957 # deduct payment differences from diff
958 for my $i (1 .. $form->{paidaccounts}) {
959 if ($form->{"paid_$i"} != 0) {
961 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
962 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
966 # record payments and offsetting AR
967 if (!$form->{storno}) {
968 for my $i (1 .. $form->{paidaccounts}) {
970 if ($form->{"acc_trans_id_$i"}
972 && (SL::DB::Default->get->payments_changeable == 0)) {
976 next if ($form->{"paid_$i"} == 0);
978 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
979 $form->{"datepaid_$i"} = $form->{invdate}
980 unless ($form->{"datepaid_$i"});
981 $form->{datepaid} = $form->{"datepaid_$i"};
985 if ($form->{currency} eq $defaultcurrency) {
986 $form->{"exchangerate_$i"} = 1;
988 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
989 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
993 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
995 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
997 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
998 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
1001 WHERE chart_id= (SELECT id
1005 ORDER BY startdate DESC LIMIT 1),
1008 WHERE chart_id= (SELECT id
1012 ORDER BY startdate DESC LIMIT 1),
1014 (SELECT link FROM chart WHERE accno = ?))|;
1015 @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});
1016 do_query($form, $dbh, $query, @values);
1020 $form->{"paid_$i"} *= -1;
1021 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1024 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1025 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1028 WHERE chart_id= (SELECT id
1032 ORDER BY startdate DESC LIMIT 1),
1035 WHERE chart_id= (SELECT id
1039 ORDER BY startdate DESC LIMIT 1),
1041 (SELECT link FROM chart WHERE accno = ?))|;
1042 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1043 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1044 do_query($form, $dbh, $query, @values);
1046 # exchangerate difference
1047 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1048 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1052 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1053 $form->{"exchangerate_$i"};
1055 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1057 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1062 # update exchange rate
1063 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1064 $form->update_exchangerate($dbh, $form->{currency},
1065 $form->{"datepaid_$i"},
1066 $form->{"exchangerate_$i"}, 0);
1070 } else { # if (!$form->{storno})
1071 $form->{marge_total} *= -1;
1074 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1076 # record exchange rate differences and gains/losses
1077 foreach my $accno (keys %{ $form->{fx} }) {
1078 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1079 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1080 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1083 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1084 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1087 WHERE chart_id= (SELECT id
1091 ORDER BY startdate DESC LIMIT 1),
1094 WHERE chart_id= (SELECT id
1098 ORDER BY startdate DESC LIMIT 1),
1100 (SELECT link FROM chart WHERE accno = ?))|;
1101 @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);
1102 do_query($form, $dbh, $query, @values);
1107 if ($payments_only) {
1108 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1109 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1111 $dbh->commit if !$provided_dbh;
1113 $main::lxdebug->leave_sub();
1117 $amount = $netamount + $tax;
1120 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1122 $query = qq|UPDATE ar set
1123 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1124 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1125 amount = ?, netamount = ?, paid = ?,
1126 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1127 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1128 currency_id = (SELECT id FROM currencies WHERE name = ?),
1129 department_id = ?, payment_id = ?, taxincluded = ?,
1130 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1131 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1132 cp_id = ?, marge_total = ?, marge_percent = ?,
1133 globalproject_id = ?, delivery_customer_id = ?,
1134 transaction_description = ?, delivery_vendor_id = ?,
1135 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1136 delivery_term_id = ?
1138 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1139 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1140 $amount, $netamount, $form->{"paid"},
1141 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1142 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1143 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1144 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1145 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1146 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1147 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1148 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1149 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1150 conv_i($form->{delivery_term_id}),
1151 conv_i($form->{"id"}));
1152 do_query($form, $dbh, $query, @values);
1155 if ($form->{storno}) {
1158 paid = paid + amount,
1160 intnotes = ? || intnotes
1162 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1163 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1166 $form->{name} = $form->{customer};
1167 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1170 if (!$form->{shipto_id}) {
1171 $form->add_shipto($dbh, $form->{id}, "AR");
1174 # save printed, emailed and queued
1175 $form->save_status($dbh);
1177 Common::webdav_folder($form);
1179 if ($form->{convert_from_ar_ids}) {
1180 RecordLinks->create_links('dbh' => $dbh,
1182 'from_table' => 'ar',
1183 'from_ids' => $form->{convert_from_ar_ids},
1185 'to_id' => $form->{id},
1187 delete $form->{convert_from_ar_ids};
1190 # Link this record to the records it was created from.
1191 RecordLinks->create_links('dbh' => $dbh,
1193 'from_table' => 'oe',
1194 'from_ids' => $form->{convert_from_oe_ids},
1196 'to_id' => $form->{id},
1198 delete $form->{convert_from_oe_ids};
1200 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1202 if (scalar @convert_from_do_ids) {
1203 DO->close_orders('dbh' => $dbh,
1204 'ids' => \@convert_from_do_ids);
1206 RecordLinks->create_links('dbh' => $dbh,
1208 'from_table' => 'delivery_orders',
1209 'from_ids' => \@convert_from_do_ids,
1211 'to_id' => $form->{id},
1214 delete $form->{convert_from_do_ids};
1216 ARAP->close_orders_if_billed('dbh' => $dbh,
1217 'arap_id' => $form->{id},
1220 # safety check datev export
1221 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1222 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1223 $transdate ||= DateTime->today;
1225 my $datev = SL::DATEV->new(
1226 exporttype => DATEV_ET_BUCHUNGEN,
1227 format => DATEV_FORMAT_KNE,
1231 trans_id => $form->{id},
1236 if ($datev->errors) {
1238 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1243 $dbh->commit if !$provided_dbh;
1245 $main::lxdebug->leave_sub();
1250 sub _delete_payments {
1251 $main::lxdebug->enter_sub();
1253 my ($self, $form, $dbh) = @_;
1255 my @delete_acc_trans_ids;
1257 # Delete old payment entries from acc_trans.
1259 qq|SELECT acc_trans_id
1261 WHERE (trans_id = ?) AND fx_transaction
1265 SELECT at.acc_trans_id
1267 LEFT JOIN chart c ON (at.chart_id = c.id)
1268 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1269 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1272 qq|SELECT at.acc_trans_id
1274 LEFT JOIN chart c ON (at.chart_id = c.id)
1275 WHERE (trans_id = ?)
1276 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1277 ORDER BY at.acc_trans_id
1279 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1281 if (@delete_acc_trans_ids) {
1282 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1283 do_query($form, $dbh, $query);
1286 $main::lxdebug->leave_sub();
1290 $main::lxdebug->enter_sub();
1292 my ($self, $myconfig, $form, $locale) = @_;
1294 # connect to database, turn off autocommit
1295 my $dbh = $form->get_standard_dbh;
1297 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1299 $old_form = save_form();
1301 # Delete all entries in acc_trans from prior payments.
1302 if (SL::DB::Default->get->payments_changeable != 0) {
1303 $self->_delete_payments($form, $dbh);
1306 # Save the new payments the user made before cleaning up $form.
1307 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 };
1309 # Clean up $form so that old content won't tamper the results.
1310 %keep_vars = map { $_, 1 } qw(login password id);
1311 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1313 # Retrieve the invoice from the database.
1314 $self->retrieve_invoice($myconfig, $form);
1316 # Set up the content of $form in the way that IS::post_invoice() expects.
1317 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1319 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1320 $item = $form->{invoice_details}->[$row - 1];
1322 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1324 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1327 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1329 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1331 # Restore the payment options from the user input.
1332 map { $form->{$_} = $payments{$_} } keys %payments;
1334 # Get the AR accno (which is normally done by Form::create_links()).
1338 LEFT JOIN chart c ON (at.chart_id = c.id)
1339 WHERE (trans_id = ?)
1340 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1341 ORDER BY at.acc_trans_id
1344 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1346 # Post the new payments.
1347 $self->post_invoice($myconfig, $form, $dbh, 1);
1349 restore_form($old_form);
1351 my $rc = $dbh->commit();
1353 $main::lxdebug->leave_sub();
1358 sub process_assembly {
1359 $main::lxdebug->enter_sub();
1361 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1364 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1365 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1367 JOIN parts p ON (a.parts_id = p.id)
1369 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1371 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1375 $ref->{inventory_accno_id} *= 1;
1376 $ref->{expense_accno_id} *= 1;
1378 # multiply by number of assemblies
1379 $ref->{qty} *= $totalqty;
1381 if ($ref->{assembly}) {
1382 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1385 if ($ref->{inventory_accno_id}) {
1386 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1390 # save detail record for individual assembly item in invoice table
1392 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1393 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1394 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1395 do_query($form, $dbh, $query, @values);
1401 $main::lxdebug->leave_sub();
1405 $main::lxdebug->enter_sub();
1407 # adjust allocated in table invoice according to FIFO princicple
1408 # for a certain part with part_id $id
1410 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1414 $form->{taxzone_id} *=1;
1415 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1416 my $taxzone_id = $form->{"taxzone_id"} * 1;
1418 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1419 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1420 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1421 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1422 FROM invoice i, parts p
1423 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1424 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)
1425 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)
1426 WHERE (i.parts_id = p.id)
1427 AND (i.parts_id = ?)
1428 AND ((i.base_qty + i.allocated) < 0)
1430 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1435 # all invoice entries of an example part:
1437 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1438 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1439 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1440 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1441 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1442 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1443 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1445 # 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
1446 # and all parts have been allocated
1448 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1449 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1451 # in this example there are still 4 unsold articles
1454 # search all invoice entries for the part in question, adjusting "allocated"
1455 # until the total number of sold parts has been reached
1457 # ORDER BY trans_id ensures FIFO
1460 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1461 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1465 # update allocated in invoice
1466 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1468 # total expenses and inventory
1469 # sellprice is the cost of the item
1470 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1472 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1473 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1474 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1476 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1477 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1478 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1480 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1481 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1487 last if (($totalqty -= $qty) <= 0);
1492 $main::lxdebug->leave_sub();
1497 sub reverse_invoice {
1498 $main::lxdebug->enter_sub();
1500 my ($dbh, $form) = @_;
1502 # reverse inventory items
1504 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1506 JOIN parts p ON (i.parts_id = p.id)
1507 WHERE i.trans_id = ?|;
1508 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1510 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1512 if ($ref->{inventory_accno_id}) {
1513 # de-allocated purchases
1515 qq|SELECT i.id, i.trans_id, i.allocated
1517 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1518 ORDER BY i.trans_id DESC|;
1519 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1521 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1522 my $qty = $ref->{qty};
1523 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1524 $qty = $inhref->{allocated};
1528 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1530 last if (($ref->{qty} -= $qty) <= 0);
1539 my @values = (conv_i($form->{id}));
1540 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1541 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1542 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1544 $main::lxdebug->leave_sub();
1547 sub delete_invoice {
1548 $main::lxdebug->enter_sub();
1550 my ($self, $myconfig, $form) = @_;
1552 # connect to database
1553 my $dbh = $form->get_standard_dbh;
1555 &reverse_invoice($dbh, $form);
1557 my @values = (conv_i($form->{id}));
1559 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1560 # zurückgesetzt werden. Vgl:
1561 # id | storno | storno_id | paid | amount
1562 #----+--------+-----------+---------+-----------
1563 # 18 | f | | 0.00000 | 119.00000
1565 # 18 | t | | 119.00000 | 119.00000
1567 if($form->{storno}){
1568 # storno_id auslesen und korrigieren
1569 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1570 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1573 # delete spool files
1574 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1577 qq|DELETE FROM status WHERE trans_id = ?|,
1578 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1579 qq|DELETE FROM ar WHERE id = ?|,
1582 map { do_query($form, $dbh, $_, @values) } @queries;
1584 my $rc = $dbh->commit;
1587 my $spool = $::lx_office_conf{paths}->{spool};
1588 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1591 $main::lxdebug->leave_sub();
1596 sub retrieve_invoice {
1597 $main::lxdebug->enter_sub();
1599 my ($self, $myconfig, $form) = @_;
1601 # connect to database
1602 my $dbh = $form->get_standard_dbh;
1604 my ($sth, $ref, $query);
1606 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1610 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1611 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1612 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1613 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1614 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1618 $ref = selectfirst_hashref_query($form, $dbh, $query);
1619 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1622 my $id = conv_i($form->{id});
1625 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1629 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1630 a.orddate, a.quodate, a.globalproject_id,
1631 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1632 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1633 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1634 a.employee_id, a.salesman_id, a.payment_id,
1635 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1636 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1637 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1640 LEFT JOIN employee e ON (e.id = a.employee_id)
1642 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1643 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1645 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1647 foreach my $vc (qw(customer vendor)) {
1648 next if !$form->{"delivery_${vc}_id"};
1649 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1652 # get printed, emailed
1653 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1654 $sth = prepare_execute_query($form, $dbh, $query, $id);
1656 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1657 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1658 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1659 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1662 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1664 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1665 : $form->{invdate} ? $dbh->quote($form->{invdate})
1669 my $taxzone_id = $form->{taxzone_id} *= 1;
1670 $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
1672 # retrieve individual items
1675 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1676 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1677 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1680 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1681 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,
1682 i.price_factor_id, i.price_factor, i.marge_price_factor, i.active_price_source, i.active_discount_source,
1683 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1684 pr.projectnumber, pg.partsgroup, prg.pricegroup
1687 LEFT JOIN parts p ON (i.parts_id = p.id)
1688 LEFT JOIN project pr ON (i.project_id = pr.id)
1689 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1690 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1692 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1693 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)
1694 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)
1696 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1698 $sth = prepare_execute_query($form, $dbh, $query, $id);
1700 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1701 # Retrieve custom variables.
1702 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1704 sub_module => 'invoice',
1705 trans_id => $ref->{invoice_id},
1707 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1708 delete $ref->{invoice_id};
1710 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1711 delete($ref->{"part_inventory_accno_id"});
1713 foreach my $type (qw(inventory income expense)) {
1714 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1715 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1716 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1720 # get tax rates and description
1721 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1723 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1724 LEFT JOIN chart c ON (c.id = t.chart_id)
1726 (SELECT tk.tax_id FROM taxkeys tk
1727 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1728 AND startdate <= date($transdate)
1729 ORDER BY startdate DESC LIMIT 1)
1731 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1732 $ref->{taxaccounts} = "";
1734 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1736 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1740 $ref->{taxaccounts} .= "$ptr->{accno} ";
1742 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1743 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1744 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1745 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1746 $form->{taxaccounts} .= "$ptr->{accno} ";
1751 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1753 chop $ref->{taxaccounts};
1754 push @{ $form->{invoice_details} }, $ref;
1759 Common::webdav_folder($form);
1762 my $rc = $dbh->commit;
1764 $main::lxdebug->leave_sub();
1770 $main::lxdebug->enter_sub();
1772 my ($self, $myconfig, $form) = @_;
1774 # connect to database
1775 my $dbh = $form->get_standard_dbh;
1777 my $dateformat = $myconfig->{dateformat};
1778 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1780 my (@values, $duedate, $ref, $query);
1782 if ($form->{invdate}) {
1783 $duedate = "to_date(?, '$dateformat')";
1784 push @values, $form->{invdate};
1786 $duedate = "current_date";
1789 my $cid = conv_i($form->{customer_id});
1792 if ($form->{payment_id}) {
1793 $payment_id = "(pt.id = ?) OR";
1794 push @values, conv_i($form->{payment_id});
1800 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1801 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1802 c.street, c.zipcode, c.city, c.country,
1803 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1804 c.taxincluded_checked, c.direct_debit,
1805 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1806 b.discount AS tradediscount, b.description AS business
1808 LEFT JOIN business b ON (b.id = c.business_id)
1809 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1810 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1813 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1815 delete $ref->{salesman_id} if !$ref->{salesman_id};
1817 map { $form->{$_} = $ref->{$_} } keys %$ref;
1819 # use customer currency
1820 $form->{currency} = $form->{curr};
1823 qq|SELECT sum(amount - paid) AS dunning_amount
1825 WHERE (paid < amount)
1826 AND (customer_id = ?)
1827 AND (dunning_config_id IS NOT NULL)|;
1828 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1829 map { $form->{$_} = $ref->{$_} } keys %$ref;
1832 qq|SELECT dnn.dunning_description AS max_dunning_level
1833 FROM dunning_config dnn
1834 WHERE id IN (SELECT dunning_config_id
1836 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1837 ORDER BY dunning_level DESC LIMIT 1|;
1838 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1839 map { $form->{$_} = $ref->{$_} } keys %$ref;
1841 $form->{creditremaining} = $form->{creditlimit};
1842 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1843 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1844 $form->{creditremaining} -= $value;
1848 (SELECT e.buy FROM exchangerate e
1849 WHERE e.currency_id = o.currency_id
1850 AND e.transdate = o.transdate)
1852 WHERE o.customer_id = ?
1853 AND o.quotation = '0'
1854 AND o.closed = '0'|;
1855 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1857 while (my ($amount, $exch) = $sth->fetchrow_array) {
1858 $exch = 1 unless $exch;
1859 $form->{creditremaining} -= $amount * $exch;
1863 # setup last accounts used for this customer
1864 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1866 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1868 JOIN acc_trans ac ON (ac.chart_id = c.id)
1869 JOIN ar a ON (a.id = ac.trans_id)
1870 WHERE a.customer_id = ?
1871 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1872 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1873 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1876 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1877 if ($ref->{category} eq 'I') {
1879 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1881 if ($form->{initial_transdate}) {
1883 qq|SELECT tk.tax_id, t.rate
1885 LEFT JOIN tax t ON tk.tax_id = t.id
1886 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1887 ORDER BY tk.startdate DESC
1889 my ($tax_id, $rate) =
1890 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1891 $form->{initial_transdate});
1892 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1895 if ($ref->{category} eq 'A') {
1896 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1900 $form->{rowcount} = $i if ($i && !$form->{type});
1903 $main::lxdebug->leave_sub();
1907 $main::lxdebug->enter_sub();
1909 my ($self, $myconfig, $form) = @_;
1911 # connect to database
1912 my $dbh = $form->get_standard_dbh;
1914 my $i = $form->{rowcount};
1916 my $where = qq|NOT p.obsolete = '1'|;
1919 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1920 my ($table, $field) = split m/\./, $column;
1921 next if !$form->{"${field}_${i}"};
1922 $where .= qq| AND lower(${column}) ILIKE ?|;
1923 push @values, '%' . $form->{"${field}_${i}"} . '%';
1927 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1928 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1929 push @values, $form->{"partnumber_$i"};
1931 # also search hits in makemodels, but only cache the results by id and merge later
1933 SELECT parts_id, model FROM makemodel LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ?;
1935 my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%');
1936 my @mm_ids = map { $_->{parts_id} } @$mm_results;
1937 push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
1940 $where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|;
1941 push @values, @mm_ids;
1945 # Search for part ID overrides all other criteria.
1946 if ($form->{"id_${i}"}) {
1947 $where = qq|p.id = ?|;
1948 @values = ($form->{"id_${i}"});
1951 if ($form->{"description_$i"}) {
1952 $where .= qq| ORDER BY p.description|;
1954 $where .= qq| ORDER BY p.partnumber|;
1958 if ($form->{type} eq "invoice") {
1960 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1961 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1965 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1969 my $taxzone_id = $form->{taxzone_id} * 1;
1970 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1974 p.id, p.partnumber, p.description, p.sellprice,
1975 p.listprice, p.inventory_accno_id, p.lastcost,
1978 c1.accno AS inventory_accno,
1979 c1.new_chart_id AS inventory_new_chart,
1980 date($transdate) - c1.valid_from AS inventory_valid,
1982 c2.accno AS income_accno,
1983 c2.new_chart_id AS income_new_chart,
1984 date($transdate) - c2.valid_from AS income_valid,
1986 c3.accno AS expense_accno,
1987 c3.new_chart_id AS expense_new_chart,
1988 date($transdate) - c3.valid_from AS expense_valid,
1990 p.unit, p.assembly, p.onhand,
1991 p.notes AS partnotes, p.notes AS longdescription,
1992 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1993 p.price_factor_id, p.weight,
1995 pfac.factor AS price_factor,
2000 LEFT JOIN chart c1 ON
2001 ((SELECT inventory_accno_id
2002 FROM buchungsgruppen
2003 WHERE id = p.buchungsgruppen_id) = c1.id)
2004 LEFT JOIN chart c2 ON
2005 ((SELECT tc.income_accno_id
2006 FROM taxzone_charts tc
2007 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
2008 LEFT JOIN chart c3 ON
2009 ((SELECT tc.expense_accno_id
2010 FROM taxzone_charts tc
2011 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
2012 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
2013 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
2015 my $sth = prepare_execute_query($form, $dbh, $query, @values);
2017 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
2019 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
2020 [ qq|SELECT tr.translation, tr.longdescription
2022 WHERE tr.language_id IN
2025 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2028 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2030 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2032 if ($mm_by_id{$ref->{id}}) {
2033 $ref->{makemodels} = $mm_by_id{$ref->{id}};
2034 push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
2037 if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
2038 push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
2041 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2042 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2043 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2044 if (!$ref->{inventory_accno_id}) {
2045 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2047 delete($ref->{inventory_accno_id});
2049 foreach my $type (qw(inventory income expense)) {
2050 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2052 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2055 ($ref->{"${type}_accno"},
2056 $ref->{"${type}_new_chart"},
2057 $ref->{"${type}_valid"})
2058 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2062 if ($form->{payment_id} eq "") {
2063 $form->{payment_id} = $form->{part_payment_id};
2066 # get tax rates and description
2067 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2069 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2071 LEFT JOIN chart c ON (c.id = t.chart_id)
2075 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2077 ORDER BY startdate DESC
2080 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2081 my $stw = $dbh->prepare($query);
2082 $stw->execute(@values) || $form->dberror($query);
2084 $ref->{taxaccounts} = "";
2086 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2088 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2092 $ref->{taxaccounts} .= "$ptr->{accno} ";
2094 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2095 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2096 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2097 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2098 $form->{taxaccounts} .= "$ptr->{accno} ";
2104 chop $ref->{taxaccounts};
2106 if ($form->{language_id}) {
2107 for my $spec (@translation_queries) {
2108 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2109 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2110 next unless $translation;
2111 $ref->{description} = $translation;
2112 $ref->{longdescription} = $longdescription;
2117 $ref->{onhand} *= 1;
2119 push @{ $form->{item_list} }, $ref;
2122 $_->[1]->finish for @translation_queries;
2124 foreach my $item (@{ $form->{item_list} }) {
2125 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2126 trans_id => $item->{id},
2130 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2133 $main::lxdebug->leave_sub();
2137 $main::lxdebug->enter_sub();
2139 my ($self, $myconfig, $form, $table) = @_;
2141 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2143 # make sure there's no funny stuff in $table
2144 # ToDO: die when this happens and throw an error
2145 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2147 my $dbh = $form->get_standard_dbh;
2149 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2150 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2152 $main::lxdebug->leave_sub();
2158 $main::lxdebug->enter_sub();
2160 my ($self, $myconfig, $form, $table, $id) = @_;
2162 $main::lxdebug->leave_sub() and return 0 unless ($id);
2164 # make sure there's no funny stuff in $table
2165 # ToDO: die when this happens and throw an error
2166 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2168 my $dbh = $form->get_standard_dbh;
2170 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2171 my ($result) = selectrow_query($form, $dbh, $query, $id);
2173 $main::lxdebug->leave_sub();
2178 sub get_standard_accno_current_assets {
2179 $main::lxdebug->enter_sub();
2181 my ($self, $myconfig, $form) = @_;
2183 my $dbh = $form->get_standard_dbh;
2185 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2186 my ($result) = selectrow_query($form, $dbh, $query);
2188 $main::lxdebug->leave_sub();