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,
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 $form->{"active_price_source_$i"},
780 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
781 conv_i($form->{"marge_price_factor_$i"}));
782 do_query($form, $dbh, $query, @values);
784 CVar->save_custom_variables(module => 'IC',
785 sub_module => 'invoice',
786 trans_id => $invoice_id,
787 configs => $ic_cvar_configs,
789 name_prefix => 'ic_',
790 name_postfix => "_$i",
795 # total payments, don't move we need it here
796 for my $i (1 .. $form->{paidaccounts}) {
797 if ($form->{type} eq "credit_note") {
798 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
800 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
802 $form->{paid} += $form->{"paid_$i"};
803 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
806 my ($tax, $diff) = (0, 0);
808 $netamount = $form->round_amount($netamount, 2);
810 # figure out rounding errors for total amount vs netamount + taxes
811 if ($form->{taxincluded}) {
813 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
814 $diff += $amount - $netamount * $form->{exchangerate};
815 $netamount = $amount;
817 foreach my $item (split(/ /, $form->{taxaccounts})) {
818 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
819 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
820 $tax += $form->{amount}{ $form->{id} }{$item};
821 $netamount -= $form->{amount}{ $form->{id} }{$item};
824 $invoicediff += $diff;
825 ######## this only applies to tax included
826 if ($lastincomeaccno) {
827 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
831 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
832 $diff = $amount - $netamount * $form->{exchangerate};
833 $netamount = $amount;
834 foreach my $item (split(/ /, $form->{taxaccounts})) {
835 $form->{amount}{ $form->{id} }{$item} =
836 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
839 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
842 $amount - $form->{amount}{ $form->{id} }{$item} *
843 $form->{exchangerate};
844 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
845 $tax += $form->{amount}{ $form->{id} }{$item};
849 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
851 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
854 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
856 # update exchangerate
857 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
858 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
859 $form->{exchangerate}, 0);
862 $project_id = conv_i($form->{"globalproject_id"});
863 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
864 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
866 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
867 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
868 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
870 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
872 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
874 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
875 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
876 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
877 do_query($form, $dbh, $query, @values);
878 $form->{amount_cogs}{$trans_id}{$accno} = 0;
882 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
883 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
885 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
887 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
888 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
889 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
890 do_query($form, $dbh, $query, @values);
895 foreach my $trans_id (keys %{ $form->{amount} }) {
896 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
897 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
899 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
901 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
903 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
904 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
907 WHERE chart_id= (SELECT id
911 ORDER BY startdate DESC LIMIT 1),
914 WHERE chart_id= (SELECT id
918 ORDER BY startdate DESC LIMIT 1),
920 (SELECT link FROM chart WHERE accno = ?))|;
921 @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);
922 do_query($form, $dbh, $query, @values);
923 $form->{amount}{$trans_id}{$accno} = 0;
927 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
928 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
930 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
932 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
933 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
936 WHERE chart_id= (SELECT id
940 ORDER BY startdate DESC LIMIT 1),
943 WHERE chart_id= (SELECT id
947 ORDER BY startdate DESC LIMIT 1),
949 (SELECT link FROM chart WHERE accno = ?))|;
950 @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);
951 do_query($form, $dbh, $query, @values);
956 # deduct payment differences from diff
957 for my $i (1 .. $form->{paidaccounts}) {
958 if ($form->{"paid_$i"} != 0) {
960 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
961 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
965 # record payments and offsetting AR
966 if (!$form->{storno}) {
967 for my $i (1 .. $form->{paidaccounts}) {
969 if ($form->{"acc_trans_id_$i"}
971 && (SL::DB::Default->get->payments_changeable == 0)) {
975 next if ($form->{"paid_$i"} == 0);
977 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
978 $form->{"datepaid_$i"} = $form->{invdate}
979 unless ($form->{"datepaid_$i"});
980 $form->{datepaid} = $form->{"datepaid_$i"};
984 if ($form->{currency} eq $defaultcurrency) {
985 $form->{"exchangerate_$i"} = 1;
987 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
988 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
992 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
994 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
996 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
997 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
1000 WHERE chart_id= (SELECT id
1004 ORDER BY startdate DESC LIMIT 1),
1007 WHERE chart_id= (SELECT id
1011 ORDER BY startdate DESC LIMIT 1),
1013 (SELECT link FROM chart WHERE accno = ?))|;
1014 @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});
1015 do_query($form, $dbh, $query, @values);
1019 $form->{"paid_$i"} *= -1;
1020 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1023 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1024 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1027 WHERE chart_id= (SELECT id
1031 ORDER BY startdate DESC LIMIT 1),
1034 WHERE chart_id= (SELECT id
1038 ORDER BY startdate DESC LIMIT 1),
1040 (SELECT link FROM chart WHERE accno = ?))|;
1041 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1042 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1043 do_query($form, $dbh, $query, @values);
1045 # exchangerate difference
1046 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1047 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1051 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1052 $form->{"exchangerate_$i"};
1054 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1056 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1061 # update exchange rate
1062 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1063 $form->update_exchangerate($dbh, $form->{currency},
1064 $form->{"datepaid_$i"},
1065 $form->{"exchangerate_$i"}, 0);
1069 } else { # if (!$form->{storno})
1070 $form->{marge_total} *= -1;
1073 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1075 # record exchange rate differences and gains/losses
1076 foreach my $accno (keys %{ $form->{fx} }) {
1077 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1078 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1079 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1082 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1083 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1086 WHERE chart_id= (SELECT id
1090 ORDER BY startdate DESC LIMIT 1),
1093 WHERE chart_id= (SELECT id
1097 ORDER BY startdate DESC LIMIT 1),
1099 (SELECT link FROM chart WHERE accno = ?))|;
1100 @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);
1101 do_query($form, $dbh, $query, @values);
1106 if ($payments_only) {
1107 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1108 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1110 $dbh->commit if !$provided_dbh;
1112 $main::lxdebug->leave_sub();
1116 $amount = $netamount + $tax;
1119 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1121 $query = qq|UPDATE ar set
1122 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1123 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1124 amount = ?, netamount = ?, paid = ?,
1125 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1126 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1127 currency_id = (SELECT id FROM currencies WHERE name = ?),
1128 department_id = ?, payment_id = ?, taxincluded = ?,
1129 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1130 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1131 cp_id = ?, marge_total = ?, marge_percent = ?,
1132 globalproject_id = ?, delivery_customer_id = ?,
1133 transaction_description = ?, delivery_vendor_id = ?,
1134 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1135 delivery_term_id = ?
1137 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1138 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1139 $amount, $netamount, $form->{"paid"},
1140 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1141 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1142 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1143 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1144 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1145 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1146 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1147 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1148 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1149 conv_i($form->{delivery_term_id}),
1150 conv_i($form->{"id"}));
1151 do_query($form, $dbh, $query, @values);
1154 if ($form->{storno}) {
1157 paid = paid + amount,
1159 intnotes = ? || intnotes
1161 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1162 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1165 $form->{name} = $form->{customer};
1166 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1169 if (!$form->{shipto_id}) {
1170 $form->add_shipto($dbh, $form->{id}, "AR");
1173 # save printed, emailed and queued
1174 $form->save_status($dbh);
1176 Common::webdav_folder($form);
1178 if ($form->{convert_from_ar_ids}) {
1179 RecordLinks->create_links('dbh' => $dbh,
1181 'from_table' => 'ar',
1182 'from_ids' => $form->{convert_from_ar_ids},
1184 'to_id' => $form->{id},
1186 delete $form->{convert_from_ar_ids};
1189 # Link this record to the records it was created from.
1190 RecordLinks->create_links('dbh' => $dbh,
1192 'from_table' => 'oe',
1193 'from_ids' => $form->{convert_from_oe_ids},
1195 'to_id' => $form->{id},
1197 delete $form->{convert_from_oe_ids};
1199 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1201 if (scalar @convert_from_do_ids) {
1202 DO->close_orders('dbh' => $dbh,
1203 'ids' => \@convert_from_do_ids);
1205 RecordLinks->create_links('dbh' => $dbh,
1207 'from_table' => 'delivery_orders',
1208 'from_ids' => \@convert_from_do_ids,
1210 'to_id' => $form->{id},
1213 delete $form->{convert_from_do_ids};
1215 ARAP->close_orders_if_billed('dbh' => $dbh,
1216 'arap_id' => $form->{id},
1219 # safety check datev export
1220 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1221 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1222 $transdate ||= DateTime->today;
1224 my $datev = SL::DATEV->new(
1225 exporttype => DATEV_ET_BUCHUNGEN,
1226 format => DATEV_FORMAT_KNE,
1230 trans_id => $form->{id},
1235 if ($datev->errors) {
1237 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1242 $dbh->commit if !$provided_dbh;
1244 $main::lxdebug->leave_sub();
1249 sub _delete_payments {
1250 $main::lxdebug->enter_sub();
1252 my ($self, $form, $dbh) = @_;
1254 my @delete_acc_trans_ids;
1256 # Delete old payment entries from acc_trans.
1258 qq|SELECT acc_trans_id
1260 WHERE (trans_id = ?) AND fx_transaction
1264 SELECT at.acc_trans_id
1266 LEFT JOIN chart c ON (at.chart_id = c.id)
1267 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1268 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1271 qq|SELECT at.acc_trans_id
1273 LEFT JOIN chart c ON (at.chart_id = c.id)
1274 WHERE (trans_id = ?)
1275 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1276 ORDER BY at.acc_trans_id
1278 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1280 if (@delete_acc_trans_ids) {
1281 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1282 do_query($form, $dbh, $query);
1285 $main::lxdebug->leave_sub();
1289 $main::lxdebug->enter_sub();
1291 my ($self, $myconfig, $form, $locale) = @_;
1293 # connect to database, turn off autocommit
1294 my $dbh = $form->get_standard_dbh;
1296 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1298 $old_form = save_form();
1300 # Delete all entries in acc_trans from prior payments.
1301 if (SL::DB::Default->get->payments_changeable != 0) {
1302 $self->_delete_payments($form, $dbh);
1305 # Save the new payments the user made before cleaning up $form.
1306 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 };
1308 # Clean up $form so that old content won't tamper the results.
1309 %keep_vars = map { $_, 1 } qw(login password id);
1310 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1312 # Retrieve the invoice from the database.
1313 $self->retrieve_invoice($myconfig, $form);
1315 # Set up the content of $form in the way that IS::post_invoice() expects.
1316 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1318 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1319 $item = $form->{invoice_details}->[$row - 1];
1321 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1323 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1326 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1328 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1330 # Restore the payment options from the user input.
1331 map { $form->{$_} = $payments{$_} } keys %payments;
1333 # Get the AR accno (which is normally done by Form::create_links()).
1337 LEFT JOIN chart c ON (at.chart_id = c.id)
1338 WHERE (trans_id = ?)
1339 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1340 ORDER BY at.acc_trans_id
1343 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1345 # Post the new payments.
1346 $self->post_invoice($myconfig, $form, $dbh, 1);
1348 restore_form($old_form);
1350 my $rc = $dbh->commit();
1352 $main::lxdebug->leave_sub();
1357 sub process_assembly {
1358 $main::lxdebug->enter_sub();
1360 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1363 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1364 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1366 JOIN parts p ON (a.parts_id = p.id)
1368 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1370 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1374 $ref->{inventory_accno_id} *= 1;
1375 $ref->{expense_accno_id} *= 1;
1377 # multiply by number of assemblies
1378 $ref->{qty} *= $totalqty;
1380 if ($ref->{assembly}) {
1381 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1384 if ($ref->{inventory_accno_id}) {
1385 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1389 # save detail record for individual assembly item in invoice table
1391 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1392 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1393 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1394 do_query($form, $dbh, $query, @values);
1400 $main::lxdebug->leave_sub();
1404 $main::lxdebug->enter_sub();
1406 # adjust allocated in table invoice according to FIFO princicple
1407 # for a certain part with part_id $id
1409 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1413 $form->{taxzone_id} *=1;
1414 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1415 my $taxzone_id = $form->{"taxzone_id"} * 1;
1417 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1418 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1419 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1420 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1421 FROM invoice i, parts p
1422 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1423 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)
1424 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)
1425 WHERE (i.parts_id = p.id)
1426 AND (i.parts_id = ?)
1427 AND ((i.base_qty + i.allocated) < 0)
1429 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1434 # all invoice entries of an example part:
1436 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1437 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1438 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1439 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1440 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1441 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1442 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1444 # 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
1445 # and all parts have been allocated
1447 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1448 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1450 # in this example there are still 4 unsold articles
1453 # search all invoice entries for the part in question, adjusting "allocated"
1454 # until the total number of sold parts has been reached
1456 # ORDER BY trans_id ensures FIFO
1459 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1460 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1464 # update allocated in invoice
1465 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1467 # total expenses and inventory
1468 # sellprice is the cost of the item
1469 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1471 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1472 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1473 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1475 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1476 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1477 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1479 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1480 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1486 last if (($totalqty -= $qty) <= 0);
1491 $main::lxdebug->leave_sub();
1496 sub reverse_invoice {
1497 $main::lxdebug->enter_sub();
1499 my ($dbh, $form) = @_;
1501 # reverse inventory items
1503 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1505 JOIN parts p ON (i.parts_id = p.id)
1506 WHERE i.trans_id = ?|;
1507 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1509 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1511 if ($ref->{inventory_accno_id}) {
1512 # de-allocated purchases
1514 qq|SELECT i.id, i.trans_id, i.allocated
1516 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1517 ORDER BY i.trans_id DESC|;
1518 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1520 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1521 my $qty = $ref->{qty};
1522 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1523 $qty = $inhref->{allocated};
1527 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1529 last if (($ref->{qty} -= $qty) <= 0);
1538 my @values = (conv_i($form->{id}));
1539 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1540 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1541 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1543 $main::lxdebug->leave_sub();
1546 sub delete_invoice {
1547 $main::lxdebug->enter_sub();
1549 my ($self, $myconfig, $form) = @_;
1551 # connect to database
1552 my $dbh = $form->get_standard_dbh;
1554 &reverse_invoice($dbh, $form);
1556 my @values = (conv_i($form->{id}));
1558 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1559 # zurückgesetzt werden. Vgl:
1560 # id | storno | storno_id | paid | amount
1561 #----+--------+-----------+---------+-----------
1562 # 18 | f | | 0.00000 | 119.00000
1564 # 18 | t | | 119.00000 | 119.00000
1566 if($form->{storno}){
1567 # storno_id auslesen und korrigieren
1568 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1569 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1572 # delete spool files
1573 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1576 qq|DELETE FROM status WHERE trans_id = ?|,
1577 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1578 qq|DELETE FROM ar WHERE id = ?|,
1581 map { do_query($form, $dbh, $_, @values) } @queries;
1583 my $rc = $dbh->commit;
1586 my $spool = $::lx_office_conf{paths}->{spool};
1587 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1590 $main::lxdebug->leave_sub();
1595 sub retrieve_invoice {
1596 $main::lxdebug->enter_sub();
1598 my ($self, $myconfig, $form) = @_;
1600 # connect to database
1601 my $dbh = $form->get_standard_dbh;
1603 my ($sth, $ref, $query);
1605 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1609 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1610 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1611 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1612 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1613 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1617 $ref = selectfirst_hashref_query($form, $dbh, $query);
1618 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1621 my $id = conv_i($form->{id});
1624 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1628 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1629 a.orddate, a.quodate, a.globalproject_id,
1630 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1631 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1632 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1633 a.employee_id, a.salesman_id, a.payment_id,
1634 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1635 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1636 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1639 LEFT JOIN employee e ON (e.id = a.employee_id)
1641 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1642 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1644 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1646 foreach my $vc (qw(customer vendor)) {
1647 next if !$form->{"delivery_${vc}_id"};
1648 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1651 # get printed, emailed
1652 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1653 $sth = prepare_execute_query($form, $dbh, $query, $id);
1655 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1656 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1657 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1658 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1661 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1663 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1664 : $form->{invdate} ? $dbh->quote($form->{invdate})
1668 my $taxzone_id = $form->{taxzone_id} *= 1;
1669 $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
1671 # retrieve individual items
1674 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1675 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1676 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1679 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1680 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,
1681 i.price_factor_id, i.price_factor, i.marge_price_factor, i.active_price_source,
1682 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1683 pr.projectnumber, pg.partsgroup, prg.pricegroup
1686 LEFT JOIN parts p ON (i.parts_id = p.id)
1687 LEFT JOIN project pr ON (i.project_id = pr.id)
1688 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1689 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1691 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1692 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)
1693 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)
1695 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1697 $sth = prepare_execute_query($form, $dbh, $query, $id);
1699 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1700 # Retrieve custom variables.
1701 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1703 sub_module => 'invoice',
1704 trans_id => $ref->{invoice_id},
1706 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1707 delete $ref->{invoice_id};
1709 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1710 delete($ref->{"part_inventory_accno_id"});
1712 foreach my $type (qw(inventory income expense)) {
1713 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1714 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1715 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1719 # get tax rates and description
1720 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1722 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1723 LEFT JOIN chart c ON (c.id = t.chart_id)
1725 (SELECT tk.tax_id FROM taxkeys tk
1726 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1727 AND startdate <= date($transdate)
1728 ORDER BY startdate DESC LIMIT 1)
1730 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1731 $ref->{taxaccounts} = "";
1733 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1735 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1739 $ref->{taxaccounts} .= "$ptr->{accno} ";
1741 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1742 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1743 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1744 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1745 $form->{taxaccounts} .= "$ptr->{accno} ";
1750 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1752 chop $ref->{taxaccounts};
1753 push @{ $form->{invoice_details} }, $ref;
1758 Common::webdav_folder($form);
1761 my $rc = $dbh->commit;
1763 $main::lxdebug->leave_sub();
1769 $main::lxdebug->enter_sub();
1771 my ($self, $myconfig, $form) = @_;
1773 # connect to database
1774 my $dbh = $form->get_standard_dbh;
1776 my $dateformat = $myconfig->{dateformat};
1777 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1779 my (@values, $duedate, $ref, $query);
1781 if ($form->{invdate}) {
1782 $duedate = "to_date(?, '$dateformat')";
1783 push @values, $form->{invdate};
1785 $duedate = "current_date";
1788 my $cid = conv_i($form->{customer_id});
1791 if ($form->{payment_id}) {
1792 $payment_id = "(pt.id = ?) OR";
1793 push @values, conv_i($form->{payment_id});
1799 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1800 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1801 c.street, c.zipcode, c.city, c.country,
1802 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1803 c.taxincluded_checked, c.direct_debit,
1804 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1805 b.discount AS tradediscount, b.description AS business
1807 LEFT JOIN business b ON (b.id = c.business_id)
1808 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1809 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1812 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1814 delete $ref->{salesman_id} if !$ref->{salesman_id};
1816 map { $form->{$_} = $ref->{$_} } keys %$ref;
1818 # use customer currency
1819 $form->{currency} = $form->{curr};
1822 qq|SELECT sum(amount - paid) AS dunning_amount
1824 WHERE (paid < amount)
1825 AND (customer_id = ?)
1826 AND (dunning_config_id IS NOT NULL)|;
1827 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1828 map { $form->{$_} = $ref->{$_} } keys %$ref;
1831 qq|SELECT dnn.dunning_description AS max_dunning_level
1832 FROM dunning_config dnn
1833 WHERE id IN (SELECT dunning_config_id
1835 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1836 ORDER BY dunning_level DESC LIMIT 1|;
1837 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1838 map { $form->{$_} = $ref->{$_} } keys %$ref;
1840 $form->{creditremaining} = $form->{creditlimit};
1841 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1842 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1843 $form->{creditremaining} -= $value;
1847 (SELECT e.buy FROM exchangerate e
1848 WHERE e.currency_id = o.currency_id
1849 AND e.transdate = o.transdate)
1851 WHERE o.customer_id = ?
1852 AND o.quotation = '0'
1853 AND o.closed = '0'|;
1854 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1856 while (my ($amount, $exch) = $sth->fetchrow_array) {
1857 $exch = 1 unless $exch;
1858 $form->{creditremaining} -= $amount * $exch;
1862 # setup last accounts used for this customer
1863 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1865 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1867 JOIN acc_trans ac ON (ac.chart_id = c.id)
1868 JOIN ar a ON (a.id = ac.trans_id)
1869 WHERE a.customer_id = ?
1870 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1871 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1872 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1875 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1876 if ($ref->{category} eq 'I') {
1878 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1880 if ($form->{initial_transdate}) {
1882 qq|SELECT tk.tax_id, t.rate
1884 LEFT JOIN tax t ON tk.tax_id = t.id
1885 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1886 ORDER BY tk.startdate DESC
1888 my ($tax_id, $rate) =
1889 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1890 $form->{initial_transdate});
1891 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1894 if ($ref->{category} eq 'A') {
1895 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1899 $form->{rowcount} = $i if ($i && !$form->{type});
1902 $main::lxdebug->leave_sub();
1906 $main::lxdebug->enter_sub();
1908 my ($self, $myconfig, $form) = @_;
1910 # connect to database
1911 my $dbh = $form->get_standard_dbh;
1913 my $i = $form->{rowcount};
1915 my $where = qq|NOT p.obsolete = '1'|;
1918 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1919 my ($table, $field) = split m/\./, $column;
1920 next if !$form->{"${field}_${i}"};
1921 $where .= qq| AND lower(${column}) ILIKE ?|;
1922 push @values, '%' . $form->{"${field}_${i}"} . '%';
1926 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1927 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1928 push @values, $form->{"partnumber_$i"};
1930 # also search hits in makemodels, but only cache the results by id and merge later
1932 SELECT parts_id, model FROM makemodel LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ?;
1934 my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%');
1935 my @mm_ids = map { $_->{parts_id} } @$mm_results;
1936 push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
1939 $where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|;
1940 push @values, @mm_ids;
1944 # Search for part ID overrides all other criteria.
1945 if ($form->{"id_${i}"}) {
1946 $where = qq|p.id = ?|;
1947 @values = ($form->{"id_${i}"});
1950 if ($form->{"description_$i"}) {
1951 $where .= qq| ORDER BY p.description|;
1953 $where .= qq| ORDER BY p.partnumber|;
1957 if ($form->{type} eq "invoice") {
1959 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1960 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1964 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1968 my $taxzone_id = $form->{taxzone_id} * 1;
1969 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1973 p.id, p.partnumber, p.description, p.sellprice,
1974 p.listprice, p.inventory_accno_id, p.lastcost,
1977 c1.accno AS inventory_accno,
1978 c1.new_chart_id AS inventory_new_chart,
1979 date($transdate) - c1.valid_from AS inventory_valid,
1981 c2.accno AS income_accno,
1982 c2.new_chart_id AS income_new_chart,
1983 date($transdate) - c2.valid_from AS income_valid,
1985 c3.accno AS expense_accno,
1986 c3.new_chart_id AS expense_new_chart,
1987 date($transdate) - c3.valid_from AS expense_valid,
1989 p.unit, p.assembly, p.onhand,
1990 p.notes AS partnotes, p.notes AS longdescription,
1991 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1992 p.price_factor_id, p.weight,
1994 pfac.factor AS price_factor,
1999 LEFT JOIN chart c1 ON
2000 ((SELECT inventory_accno_id
2001 FROM buchungsgruppen
2002 WHERE id = p.buchungsgruppen_id) = c1.id)
2003 LEFT JOIN chart c2 ON
2004 ((SELECT tc.income_accno_id
2005 FROM taxzone_charts tc
2006 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
2007 LEFT JOIN chart c3 ON
2008 ((SELECT tc.expense_accno_id
2009 FROM taxzone_charts tc
2010 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
2011 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
2012 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
2014 my $sth = prepare_execute_query($form, $dbh, $query, @values);
2016 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
2018 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
2019 [ qq|SELECT tr.translation, tr.longdescription
2021 WHERE tr.language_id IN
2024 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2027 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2029 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2031 if ($mm_by_id{$ref->{id}}) {
2032 $ref->{makemodels} = $mm_by_id{$ref->{id}};
2033 push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
2036 if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
2037 push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
2040 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2041 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2042 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2043 if (!$ref->{inventory_accno_id}) {
2044 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2046 delete($ref->{inventory_accno_id});
2048 foreach my $type (qw(inventory income expense)) {
2049 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2051 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2054 ($ref->{"${type}_accno"},
2055 $ref->{"${type}_new_chart"},
2056 $ref->{"${type}_valid"})
2057 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2061 if ($form->{payment_id} eq "") {
2062 $form->{payment_id} = $form->{part_payment_id};
2065 # get tax rates and description
2066 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2068 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2070 LEFT JOIN chart c ON (c.id = t.chart_id)
2074 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2076 ORDER BY startdate DESC
2079 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2080 my $stw = $dbh->prepare($query);
2081 $stw->execute(@values) || $form->dberror($query);
2083 $ref->{taxaccounts} = "";
2085 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2087 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2091 $ref->{taxaccounts} .= "$ptr->{accno} ";
2093 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2094 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2095 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2096 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2097 $form->{taxaccounts} .= "$ptr->{accno} ";
2103 chop $ref->{taxaccounts};
2105 if ($form->{language_id}) {
2106 for my $spec (@translation_queries) {
2107 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2108 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2109 next unless $translation;
2110 $ref->{description} = $translation;
2111 $ref->{longdescription} = $longdescription;
2116 $ref->{onhand} *= 1;
2118 push @{ $form->{item_list} }, $ref;
2121 $_->[1]->finish for @translation_queries;
2123 foreach my $item (@{ $form->{item_list} }) {
2124 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2125 trans_id => $item->{id},
2129 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2132 $main::lxdebug->leave_sub();
2136 $main::lxdebug->enter_sub();
2138 my ($self, $myconfig, $form, $table) = @_;
2140 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2142 # make sure there's no funny stuff in $table
2143 # ToDO: die when this happens and throw an error
2144 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2146 my $dbh = $form->get_standard_dbh;
2148 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2149 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2151 $main::lxdebug->leave_sub();
2157 $main::lxdebug->enter_sub();
2159 my ($self, $myconfig, $form, $table, $id) = @_;
2161 $main::lxdebug->leave_sub() and return 0 unless ($id);
2163 # make sure there's no funny stuff in $table
2164 # ToDO: die when this happens and throw an error
2165 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2167 my $dbh = $form->get_standard_dbh;
2169 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2170 my ($result) = selectrow_query($form, $dbh, $query, $id);
2172 $main::lxdebug->leave_sub();
2177 sub get_standard_accno_current_assets {
2178 $main::lxdebug->enter_sub();
2180 my ($self, $myconfig, $form) = @_;
2182 my $dbh = $form->get_standard_dbh;
2184 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2185 my ($result) = selectrow_query($form, $dbh, $query);
2187 $main::lxdebug->leave_sub();