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 #9
497 my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id});
498 $ref->{name} = $customer->name;
499 $ref->{street} = $customer->street;
500 $ref->{zipcode} = $customer->zipcode;
501 $ref->{country} = $customer->country;
502 my $contact = SL::DB::Manager::Contact->find_by(cp_id => $::form->{cp_id});
503 $ref->{cp_name} = $contact->cp_name;
504 $ref->{cp_givenname} = $contact->cp_givenname;
505 $ref->{cp_gender} = $contact->cp_gender;
507 # remove id and taxincluded before copy back
508 delete @$ref{qw(id taxincluded)};
510 @wanted_vars = grep({ $_ } @wanted_vars);
511 if (scalar(@wanted_vars) > 0) {
513 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
514 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
517 map { $form->{$_} = $ref->{$_} } keys %$ref;
519 if ($form->{delivery_customer_id}) {
521 qq|SELECT *, notes as customernotes
525 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
527 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
530 if ($form->{delivery_vendor_id}) {
532 qq|SELECT *, notes as customernotes
536 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
538 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
541 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
543 'trans_id' => $form->{customer_id});
544 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
546 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
547 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
548 'language_id' => $language_id,
549 'allow_fallback' => 1);
552 $main::lxdebug->leave_sub();
556 $main::lxdebug->enter_sub();
558 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
560 # connect to database, turn off autocommit
561 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
562 my $restricter = SL::HTML::Restrict->create;
564 my ($query, $sth, $null, $project_id, @values);
565 my $exchangerate = 0;
567 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
570 if (!$form->{employee_id}) {
571 $form->get_employee($dbh);
574 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
575 my $defaultcurrency = $form->{defaultcurrency};
577 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
578 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
579 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
580 if (!$form->{department_id}){
581 ($null, $form->{department_id}) = split(/--/, $form->{department});
584 my $all_units = AM->retrieve_units($myconfig, $form);
586 if (!$payments_only) {
588 &reverse_invoice($dbh, $form);
591 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
592 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
594 $query = qq|SELECT nextval('glid')|;
595 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
597 $query = qq|INSERT INTO ar (id, invnumber, currency_id, taxzone_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?), ?)|;
598 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}, $form->{taxzone_id});
600 if (!$form->{invnumber}) {
601 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
602 $form->{invnumber} = $trans_number->create_unique;
607 my ($netamount, $invoicediff) = (0, 0);
608 my ($amount, $linetotal, $lastincomeaccno);
610 if ($form->{currency} eq $defaultcurrency) {
611 $form->{exchangerate} = 1;
613 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
616 $form->{exchangerate} =
619 : $form->parse_amount($myconfig, $form->{exchangerate});
621 $form->{expense_inventory} = "";
625 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
626 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
629 $form->{amount} = {};
630 $form->{amount_cogs} = {};
632 foreach my $i (1 .. $form->{rowcount}) {
633 if ($form->{type} eq "credit_note") {
634 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
635 $form->{shipped} = 1;
637 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
642 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
643 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
644 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
646 if ($form->{storno}) {
647 $form->{"qty_$i"} *= -1;
650 if ($form->{"id_$i"}) {
653 if (defined($baseunits{$form->{"id_$i"}})) {
654 $item_unit = $baseunits{$form->{"id_$i"}};
657 $query = qq|SELECT unit FROM parts WHERE id = ?|;
658 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
659 $baseunits{$form->{"id_$i"}} = $item_unit;
662 if (defined($all_units->{$item_unit}->{factor})
663 && ($all_units->{$item_unit}->{factor} ne '')
664 && ($all_units->{$item_unit}->{factor} != 0)) {
665 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
669 $baseqty = $form->{"qty_$i"} * $basefactor;
671 my ($allocated, $taxrate) = (0, 0);
675 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
677 # keep entered selling price
679 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
681 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
683 my $decimalplaces = ($dec > 2) ? $dec : 2;
685 # undo discount formatting
686 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
689 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
691 # round linetotal to 2 decimal places
692 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
693 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
695 if ($form->{taxincluded}) {
696 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
697 $form->{"sellprice_$i"} =
698 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
700 $taxamount = $linetotal * $taxrate;
703 $netamount += $linetotal;
705 if ($taxamount != 0) {
707 $form->{amount}{ $form->{id} }{$_} +=
708 $taxamount * $form->{"${_}_rate"} / $taxrate
709 } split(/ /, $form->{"taxaccounts_$i"});
712 # add amount to income, $form->{amount}{trans_id}{accno}
713 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
715 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
716 $linetotal = $form->round_amount($linetotal, 2);
718 # this is the difference from the inventory
719 $invoicediff += ($amount - $linetotal);
721 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
724 $lastincomeaccno = $form->{"income_accno_$i"};
726 # adjust and round sellprice
727 $form->{"sellprice_$i"} =
728 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
731 next if $payments_only;
733 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
735 if ($form->{"assembly_$i"}) {
736 # record assembly item as allocated
737 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
740 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
744 # Get pricegroup_id and save it. Unfortunately the interface
745 # also uses ID "0" for signalling that none is selected, but "0"
746 # must not be stored in the database. Therefore we cannot simply
748 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
750 $pricegroup_id = undef if !$pricegroup_id;
752 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
754 # save detail record in invoice table
756 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
757 sellprice, fxsellprice, discount, allocated, assemblyitem,
758 unit, deliverydate, project_id, serialnumber, pricegroup_id,
759 ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
760 marge_percent, marge_total, lastcost,
761 price_factor_id, price_factor, marge_price_factor)
762 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
763 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
765 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
766 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
767 $form->{"sellprice_$i"}, $fxsellprice,
768 $form->{"discount_$i"}, $allocated, 'f',
769 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
770 $form->{"serialnumber_$i"}, $pricegroup_id,
771 $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
772 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
773 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
774 $form->{"lastcost_$i"},
775 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
776 conv_i($form->{"marge_price_factor_$i"}));
777 do_query($form, $dbh, $query, @values);
779 CVar->save_custom_variables(module => 'IC',
780 sub_module => 'invoice',
781 trans_id => $invoice_id,
782 configs => $ic_cvar_configs,
784 name_prefix => 'ic_',
785 name_postfix => "_$i",
790 # total payments, don't move we need it here
791 for my $i (1 .. $form->{paidaccounts}) {
792 if ($form->{type} eq "credit_note") {
793 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
795 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
797 $form->{paid} += $form->{"paid_$i"};
798 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
801 my ($tax, $diff) = (0, 0);
803 $netamount = $form->round_amount($netamount, 2);
805 # figure out rounding errors for total amount vs netamount + taxes
806 if ($form->{taxincluded}) {
808 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
809 $diff += $amount - $netamount * $form->{exchangerate};
810 $netamount = $amount;
812 foreach my $item (split(/ /, $form->{taxaccounts})) {
813 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
814 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
815 $tax += $form->{amount}{ $form->{id} }{$item};
816 $netamount -= $form->{amount}{ $form->{id} }{$item};
819 $invoicediff += $diff;
820 ######## this only applies to tax included
821 if ($lastincomeaccno) {
822 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
826 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
827 $diff = $amount - $netamount * $form->{exchangerate};
828 $netamount = $amount;
829 foreach my $item (split(/ /, $form->{taxaccounts})) {
830 $form->{amount}{ $form->{id} }{$item} =
831 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
834 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
837 $amount - $form->{amount}{ $form->{id} }{$item} *
838 $form->{exchangerate};
839 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
840 $tax += $form->{amount}{ $form->{id} }{$item};
844 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
846 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
849 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
851 # update exchangerate
852 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
853 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
854 $form->{exchangerate}, 0);
857 $project_id = conv_i($form->{"globalproject_id"});
858 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
859 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
861 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
862 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
863 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
865 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
867 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
869 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
870 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
871 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
872 do_query($form, $dbh, $query, @values);
873 $form->{amount_cogs}{$trans_id}{$accno} = 0;
877 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
878 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
880 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
882 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
883 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
884 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
885 do_query($form, $dbh, $query, @values);
890 foreach my $trans_id (keys %{ $form->{amount} }) {
891 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
892 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
894 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
896 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
898 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
899 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
902 WHERE chart_id= (SELECT id
906 ORDER BY startdate DESC LIMIT 1),
909 WHERE chart_id= (SELECT id
913 ORDER BY startdate DESC LIMIT 1),
915 (SELECT link FROM chart WHERE accno = ?))|;
916 @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);
917 do_query($form, $dbh, $query, @values);
918 $form->{amount}{$trans_id}{$accno} = 0;
922 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
923 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
925 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
927 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
928 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
931 WHERE chart_id= (SELECT id
935 ORDER BY startdate DESC LIMIT 1),
938 WHERE chart_id= (SELECT id
942 ORDER BY startdate DESC LIMIT 1),
944 (SELECT link FROM chart WHERE accno = ?))|;
945 @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);
946 do_query($form, $dbh, $query, @values);
951 # deduct payment differences from diff
952 for my $i (1 .. $form->{paidaccounts}) {
953 if ($form->{"paid_$i"} != 0) {
955 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
956 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
960 # record payments and offsetting AR
961 if (!$form->{storno}) {
962 for my $i (1 .. $form->{paidaccounts}) {
964 if ($form->{"acc_trans_id_$i"}
966 && (SL::DB::Default->get->payments_changeable == 0)) {
970 next if ($form->{"paid_$i"} == 0);
972 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
973 $form->{"datepaid_$i"} = $form->{invdate}
974 unless ($form->{"datepaid_$i"});
975 $form->{datepaid} = $form->{"datepaid_$i"};
979 if ($form->{currency} eq $defaultcurrency) {
980 $form->{"exchangerate_$i"} = 1;
982 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
983 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
987 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
989 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
991 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
992 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
995 WHERE chart_id= (SELECT id
999 ORDER BY startdate DESC LIMIT 1),
1002 WHERE chart_id= (SELECT id
1006 ORDER BY startdate DESC LIMIT 1),
1008 (SELECT link FROM chart WHERE accno = ?))|;
1009 @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});
1010 do_query($form, $dbh, $query, @values);
1014 $form->{"paid_$i"} *= -1;
1015 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1018 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1019 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1022 WHERE chart_id= (SELECT id
1026 ORDER BY startdate DESC LIMIT 1),
1029 WHERE chart_id= (SELECT id
1033 ORDER BY startdate DESC LIMIT 1),
1035 (SELECT link FROM chart WHERE accno = ?))|;
1036 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1037 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1038 do_query($form, $dbh, $query, @values);
1040 # exchangerate difference
1041 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1042 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1046 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1047 $form->{"exchangerate_$i"};
1049 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1051 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1056 # update exchange rate
1057 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1058 $form->update_exchangerate($dbh, $form->{currency},
1059 $form->{"datepaid_$i"},
1060 $form->{"exchangerate_$i"}, 0);
1064 } else { # if (!$form->{storno})
1065 $form->{marge_total} *= -1;
1068 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1070 # record exchange rate differences and gains/losses
1071 foreach my $accno (keys %{ $form->{fx} }) {
1072 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1073 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1074 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1077 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1078 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1081 WHERE chart_id= (SELECT id
1085 ORDER BY startdate DESC LIMIT 1),
1088 WHERE chart_id= (SELECT id
1092 ORDER BY startdate DESC LIMIT 1),
1094 (SELECT link FROM chart WHERE accno = ?))|;
1095 @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);
1096 do_query($form, $dbh, $query, @values);
1101 if ($payments_only) {
1102 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1103 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1105 $dbh->commit if !$provided_dbh;
1107 $main::lxdebug->leave_sub();
1111 $amount = $netamount + $tax;
1114 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1116 $query = qq|UPDATE ar set
1117 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1118 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1119 amount = ?, netamount = ?, paid = ?,
1120 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1121 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1122 currency_id = (SELECT id FROM currencies WHERE name = ?),
1123 department_id = ?, payment_id = ?, taxincluded = ?,
1124 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1125 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1126 cp_id = ?, marge_total = ?, marge_percent = ?,
1127 globalproject_id = ?, delivery_customer_id = ?,
1128 transaction_description = ?, delivery_vendor_id = ?,
1129 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1130 delivery_term_id = ?
1132 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1133 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1134 $amount, $netamount, $form->{"paid"},
1135 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1136 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1137 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1138 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1139 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1140 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1141 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1142 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1143 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1144 conv_i($form->{delivery_term_id}),
1145 conv_i($form->{"id"}));
1146 do_query($form, $dbh, $query, @values);
1149 if ($form->{storno}) {
1152 paid = paid + amount,
1154 intnotes = ? || intnotes
1156 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1157 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1160 $form->{name} = $form->{customer};
1161 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1164 if (!$form->{shipto_id}) {
1165 $form->add_shipto($dbh, $form->{id}, "AR");
1168 # save printed, emailed and queued
1169 $form->save_status($dbh);
1171 Common::webdav_folder($form);
1173 # Link this record to the records it was created from.
1174 RecordLinks->create_links('dbh' => $dbh,
1176 'from_table' => 'oe',
1177 'from_ids' => $form->{convert_from_oe_ids},
1179 'to_id' => $form->{id},
1181 delete $form->{convert_from_oe_ids};
1183 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1185 if (scalar @convert_from_do_ids) {
1186 DO->close_orders('dbh' => $dbh,
1187 'ids' => \@convert_from_do_ids);
1189 RecordLinks->create_links('dbh' => $dbh,
1191 'from_table' => 'delivery_orders',
1192 'from_ids' => \@convert_from_do_ids,
1194 'to_id' => $form->{id},
1197 delete $form->{convert_from_do_ids};
1199 ARAP->close_orders_if_billed('dbh' => $dbh,
1200 'arap_id' => $form->{id},
1203 # safety check datev export
1204 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1205 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1206 $transdate ||= DateTime->today;
1208 my $datev = SL::DATEV->new(
1209 exporttype => DATEV_ET_BUCHUNGEN,
1210 format => DATEV_FORMAT_KNE,
1214 trans_id => $form->{id},
1219 if ($datev->errors) {
1221 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1226 $dbh->commit if !$provided_dbh;
1228 $main::lxdebug->leave_sub();
1233 sub _delete_payments {
1234 $main::lxdebug->enter_sub();
1236 my ($self, $form, $dbh) = @_;
1238 my @delete_acc_trans_ids;
1240 # Delete old payment entries from acc_trans.
1242 qq|SELECT acc_trans_id
1244 WHERE (trans_id = ?) AND fx_transaction
1248 SELECT at.acc_trans_id
1250 LEFT JOIN chart c ON (at.chart_id = c.id)
1251 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1252 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1255 qq|SELECT at.acc_trans_id
1257 LEFT JOIN chart c ON (at.chart_id = c.id)
1258 WHERE (trans_id = ?)
1259 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1260 ORDER BY at.acc_trans_id
1262 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1264 if (@delete_acc_trans_ids) {
1265 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1266 do_query($form, $dbh, $query);
1269 $main::lxdebug->leave_sub();
1273 $main::lxdebug->enter_sub();
1275 my ($self, $myconfig, $form, $locale) = @_;
1277 # connect to database, turn off autocommit
1278 my $dbh = $form->get_standard_dbh;
1280 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1282 $old_form = save_form();
1284 # Delete all entries in acc_trans from prior payments.
1285 if (SL::DB::Default->get->payments_changeable != 0) {
1286 $self->_delete_payments($form, $dbh);
1289 # Save the new payments the user made before cleaning up $form.
1290 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 };
1292 # Clean up $form so that old content won't tamper the results.
1293 %keep_vars = map { $_, 1 } qw(login password id);
1294 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1296 # Retrieve the invoice from the database.
1297 $self->retrieve_invoice($myconfig, $form);
1299 # Set up the content of $form in the way that IS::post_invoice() expects.
1300 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1302 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1303 $item = $form->{invoice_details}->[$row - 1];
1305 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1307 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1310 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1312 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1314 # Restore the payment options from the user input.
1315 map { $form->{$_} = $payments{$_} } keys %payments;
1317 # Get the AR accno (which is normally done by Form::create_links()).
1321 LEFT JOIN chart c ON (at.chart_id = c.id)
1322 WHERE (trans_id = ?)
1323 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1324 ORDER BY at.acc_trans_id
1327 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1329 # Post the new payments.
1330 $self->post_invoice($myconfig, $form, $dbh, 1);
1332 restore_form($old_form);
1334 my $rc = $dbh->commit();
1336 $main::lxdebug->leave_sub();
1341 sub process_assembly {
1342 $main::lxdebug->enter_sub();
1344 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1347 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1348 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1350 JOIN parts p ON (a.parts_id = p.id)
1352 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1354 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1358 $ref->{inventory_accno_id} *= 1;
1359 $ref->{expense_accno_id} *= 1;
1361 # multiply by number of assemblies
1362 $ref->{qty} *= $totalqty;
1364 if ($ref->{assembly}) {
1365 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1368 if ($ref->{inventory_accno_id}) {
1369 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1373 # save detail record for individual assembly item in invoice table
1375 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1376 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1377 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1378 do_query($form, $dbh, $query, @values);
1384 $main::lxdebug->leave_sub();
1388 $main::lxdebug->enter_sub();
1390 # adjust allocated in table invoice according to FIFO princicple
1391 # for a certain part with part_id $id
1393 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1397 $form->{taxzone_id} *=1;
1398 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1399 my $taxzone_id = $form->{"taxzone_id"} * 1;
1401 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1402 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1403 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1404 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1405 FROM invoice i, parts p
1406 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1407 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)
1408 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)
1409 WHERE (i.parts_id = p.id)
1410 AND (i.parts_id = ?)
1411 AND ((i.base_qty + i.allocated) < 0)
1413 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1418 # all invoice entries of an example part:
1420 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1421 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1422 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1423 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1424 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1425 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1426 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1428 # 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
1429 # and all parts have been allocated
1431 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1432 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1434 # in this example there are still 4 unsold articles
1437 # search all invoice entries for the part in question, adjusting "allocated"
1438 # until the total number of sold parts has been reached
1440 # ORDER BY trans_id ensures FIFO
1443 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1444 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1448 # update allocated in invoice
1449 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1451 # total expenses and inventory
1452 # sellprice is the cost of the item
1453 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1455 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1456 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1457 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1459 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1460 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1461 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1463 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1464 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1470 last if (($totalqty -= $qty) <= 0);
1475 $main::lxdebug->leave_sub();
1480 sub reverse_invoice {
1481 $main::lxdebug->enter_sub();
1483 my ($dbh, $form) = @_;
1485 # reverse inventory items
1487 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1489 JOIN parts p ON (i.parts_id = p.id)
1490 WHERE i.trans_id = ?|;
1491 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1493 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1495 if ($ref->{inventory_accno_id}) {
1496 # de-allocated purchases
1498 qq|SELECT i.id, i.trans_id, i.allocated
1500 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1501 ORDER BY i.trans_id DESC|;
1502 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1504 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1505 my $qty = $ref->{qty};
1506 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1507 $qty = $inhref->{allocated};
1511 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1513 last if (($ref->{qty} -= $qty) <= 0);
1522 my @values = (conv_i($form->{id}));
1523 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1524 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1525 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1527 $main::lxdebug->leave_sub();
1530 sub delete_invoice {
1531 $main::lxdebug->enter_sub();
1533 my ($self, $myconfig, $form) = @_;
1535 # connect to database
1536 my $dbh = $form->get_standard_dbh;
1538 &reverse_invoice($dbh, $form);
1540 my @values = (conv_i($form->{id}));
1542 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1543 # zurückgesetzt werden. Vgl:
1544 # id | storno | storno_id | paid | amount
1545 #----+--------+-----------+---------+-----------
1546 # 18 | f | | 0.00000 | 119.00000
1548 # 18 | t | | 119.00000 | 119.00000
1550 if($form->{storno}){
1551 # storno_id auslesen und korrigieren
1552 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1553 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1556 # delete spool files
1557 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1560 qq|DELETE FROM status WHERE trans_id = ?|,
1561 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1562 qq|DELETE FROM ar WHERE id = ?|,
1565 map { do_query($form, $dbh, $_, @values) } @queries;
1567 my $rc = $dbh->commit;
1570 my $spool = $::lx_office_conf{paths}->{spool};
1571 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1574 $main::lxdebug->leave_sub();
1579 sub retrieve_invoice {
1580 $main::lxdebug->enter_sub();
1582 my ($self, $myconfig, $form) = @_;
1584 # connect to database
1585 my $dbh = $form->get_standard_dbh;
1587 my ($sth, $ref, $query);
1589 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1593 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1594 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1595 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1596 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1597 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1601 $ref = selectfirst_hashref_query($form, $dbh, $query);
1602 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1605 my $id = conv_i($form->{id});
1608 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1612 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1613 a.orddate, a.quodate, a.globalproject_id,
1614 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1615 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1616 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1617 a.employee_id, a.salesman_id, a.payment_id,
1618 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1619 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1620 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1623 LEFT JOIN employee e ON (e.id = a.employee_id)
1625 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1626 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1628 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1630 foreach my $vc (qw(customer vendor)) {
1631 next if !$form->{"delivery_${vc}_id"};
1632 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1635 # get printed, emailed
1636 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1637 $sth = prepare_execute_query($form, $dbh, $query, $id);
1639 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1640 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1641 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1642 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1645 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1647 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1648 : $form->{invdate} ? $dbh->quote($form->{invdate})
1652 my $taxzone_id = $form->{taxzone_id} *= 1;
1653 $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
1655 # retrieve individual items
1658 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1659 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1660 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1663 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1664 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,
1665 i.price_factor_id, i.price_factor, i.marge_price_factor,
1666 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1667 pr.projectnumber, pg.partsgroup, prg.pricegroup
1670 LEFT JOIN parts p ON (i.parts_id = p.id)
1671 LEFT JOIN project pr ON (i.project_id = pr.id)
1672 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1673 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1675 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1676 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)
1677 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)
1679 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1681 $sth = prepare_execute_query($form, $dbh, $query, $id);
1683 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1684 # Retrieve custom variables.
1685 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1687 sub_module => 'invoice',
1688 trans_id => $ref->{invoice_id},
1690 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1691 delete $ref->{invoice_id};
1693 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1694 delete($ref->{"part_inventory_accno_id"});
1696 foreach my $type (qw(inventory income expense)) {
1697 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1698 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1699 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1703 # get tax rates and description
1704 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1706 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1707 LEFT JOIN chart c ON (c.id = t.chart_id)
1709 (SELECT tk.tax_id FROM taxkeys tk
1710 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1711 AND startdate <= date($transdate)
1712 ORDER BY startdate DESC LIMIT 1)
1714 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1715 $ref->{taxaccounts} = "";
1717 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1719 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1723 $ref->{taxaccounts} .= "$ptr->{accno} ";
1725 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1726 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1727 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1728 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1729 $form->{taxaccounts} .= "$ptr->{accno} ";
1734 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1736 chop $ref->{taxaccounts};
1737 push @{ $form->{invoice_details} }, $ref;
1742 Common::webdav_folder($form);
1745 my $rc = $dbh->commit;
1747 $main::lxdebug->leave_sub();
1753 $main::lxdebug->enter_sub();
1755 my ($self, $myconfig, $form) = @_;
1757 # connect to database
1758 my $dbh = $form->get_standard_dbh;
1760 my $dateformat = $myconfig->{dateformat};
1761 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1763 my (@values, $duedate, $ref, $query);
1765 if ($form->{invdate}) {
1766 $duedate = "to_date(?, '$dateformat')";
1767 push @values, $form->{invdate};
1769 $duedate = "current_date";
1772 my $cid = conv_i($form->{customer_id});
1775 if ($form->{payment_id}) {
1776 $payment_id = "(pt.id = ?) OR";
1777 push @values, conv_i($form->{payment_id});
1783 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1784 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1785 c.street, c.zipcode, c.city, c.country,
1786 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1787 c.taxincluded_checked, c.direct_debit,
1788 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1789 b.discount AS tradediscount, b.description AS business
1791 LEFT JOIN business b ON (b.id = c.business_id)
1792 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1793 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1796 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1798 delete $ref->{salesman_id} if !$ref->{salesman_id};
1800 map { $form->{$_} = $ref->{$_} } keys %$ref;
1802 # use customer currency
1803 $form->{currency} = $form->{curr};
1806 qq|SELECT sum(amount - paid) AS dunning_amount
1808 WHERE (paid < amount)
1809 AND (customer_id = ?)
1810 AND (dunning_config_id IS NOT NULL)|;
1811 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1812 map { $form->{$_} = $ref->{$_} } keys %$ref;
1815 qq|SELECT dnn.dunning_description AS max_dunning_level
1816 FROM dunning_config dnn
1817 WHERE id IN (SELECT dunning_config_id
1819 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1820 ORDER BY dunning_level DESC LIMIT 1|;
1821 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1822 map { $form->{$_} = $ref->{$_} } keys %$ref;
1824 $form->{creditremaining} = $form->{creditlimit};
1825 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1826 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1827 $form->{creditremaining} -= $value;
1831 (SELECT e.buy FROM exchangerate e
1832 WHERE e.currency_id = o.currency_id
1833 AND e.transdate = o.transdate)
1835 WHERE o.customer_id = ?
1836 AND o.quotation = '0'
1837 AND o.closed = '0'|;
1838 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1840 while (my ($amount, $exch) = $sth->fetchrow_array) {
1841 $exch = 1 unless $exch;
1842 $form->{creditremaining} -= $amount * $exch;
1846 # setup last accounts used for this customer
1847 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1849 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1851 JOIN acc_trans ac ON (ac.chart_id = c.id)
1852 JOIN ar a ON (a.id = ac.trans_id)
1853 WHERE a.customer_id = ?
1854 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1855 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1856 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1859 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1860 if ($ref->{category} eq 'I') {
1862 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1864 if ($form->{initial_transdate}) {
1866 qq|SELECT tk.tax_id, t.rate
1868 LEFT JOIN tax t ON tk.tax_id = t.id
1869 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1870 ORDER BY tk.startdate DESC
1872 my ($tax_id, $rate) =
1873 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1874 $form->{initial_transdate});
1875 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1878 if ($ref->{category} eq 'A') {
1879 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1883 $form->{rowcount} = $i if ($i && !$form->{type});
1886 $main::lxdebug->leave_sub();
1890 $main::lxdebug->enter_sub();
1892 my ($self, $myconfig, $form) = @_;
1894 # connect to database
1895 my $dbh = $form->get_standard_dbh;
1897 my $i = $form->{rowcount};
1899 my $where = qq|NOT p.obsolete = '1'|;
1902 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1903 my ($table, $field) = split m/\./, $column;
1904 next if !$form->{"${field}_${i}"};
1905 $where .= qq| AND lower(${column}) ILIKE ?|;
1906 push @values, '%' . $form->{"${field}_${i}"} . '%';
1909 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1910 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1911 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1912 push @values, $form->{"partnumber_$i"};
1915 # Search for part ID overrides all other criteria.
1916 if ($form->{"id_${i}"}) {
1917 $where = qq|p.id = ?|;
1918 @values = ($form->{"id_${i}"});
1921 if ($form->{"description_$i"}) {
1922 $where .= qq| ORDER BY p.description|;
1924 $where .= qq| ORDER BY p.partnumber|;
1928 if ($form->{type} eq "invoice") {
1930 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1931 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1935 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1939 my $taxzone_id = $form->{taxzone_id} * 1;
1940 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1944 p.id, p.partnumber, p.description, p.sellprice,
1945 p.listprice, p.inventory_accno_id, p.lastcost,
1947 c1.accno AS inventory_accno,
1948 c1.new_chart_id AS inventory_new_chart,
1949 date($transdate) - c1.valid_from AS inventory_valid,
1951 c2.accno AS income_accno,
1952 c2.new_chart_id AS income_new_chart,
1953 date($transdate) - c2.valid_from AS income_valid,
1955 c3.accno AS expense_accno,
1956 c3.new_chart_id AS expense_new_chart,
1957 date($transdate) - c3.valid_from AS expense_valid,
1959 p.unit, p.assembly, p.onhand,
1960 p.notes AS partnotes, p.notes AS longdescription,
1961 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1962 p.price_factor_id, p.weight,
1964 pfac.factor AS price_factor,
1969 LEFT JOIN chart c1 ON
1970 ((SELECT inventory_accno_id
1971 FROM buchungsgruppen
1972 WHERE id = p.buchungsgruppen_id) = c1.id)
1973 LEFT JOIN chart c2 ON
1974 ((SELECT tc.income_accno_id
1975 FROM taxzone_charts tc
1976 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
1977 LEFT JOIN chart c3 ON
1978 ((SELECT tc.expense_accno_id
1979 FROM taxzone_charts tc
1980 WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
1981 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1982 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1984 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1986 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1988 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1989 [ qq|SELECT tr.translation, tr.longdescription
1991 WHERE tr.language_id IN
1994 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1997 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1999 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2001 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2002 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2003 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2004 if (!$ref->{inventory_accno_id}) {
2005 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2007 delete($ref->{inventory_accno_id});
2009 foreach my $type (qw(inventory income expense)) {
2010 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2012 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2015 ($ref->{"${type}_accno"},
2016 $ref->{"${type}_new_chart"},
2017 $ref->{"${type}_valid"})
2018 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2022 if ($form->{payment_id} eq "") {
2023 $form->{payment_id} = $form->{part_payment_id};
2026 # get tax rates and description
2027 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2029 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2031 LEFT JOIN chart c ON (c.id = t.chart_id)
2035 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2037 ORDER BY startdate DESC
2040 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2041 my $stw = $dbh->prepare($query);
2042 $stw->execute(@values) || $form->dberror($query);
2044 $ref->{taxaccounts} = "";
2046 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2048 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2052 $ref->{taxaccounts} .= "$ptr->{accno} ";
2054 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2055 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2056 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2057 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2058 $form->{taxaccounts} .= "$ptr->{accno} ";
2064 chop $ref->{taxaccounts};
2066 if ($form->{language_id}) {
2067 for my $spec (@translation_queries) {
2068 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2069 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2070 next unless $translation;
2071 $ref->{description} = $translation;
2072 $ref->{longdescription} = $longdescription;
2077 $ref->{onhand} *= 1;
2079 push @{ $form->{item_list} }, $ref;
2082 $_->[1]->finish for @translation_queries;
2084 foreach my $item (@{ $form->{item_list} }) {
2085 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2086 trans_id => $item->{id},
2090 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2093 $main::lxdebug->leave_sub();
2096 ##########################
2097 # get pricegroups from database
2098 # build up selected pricegroup
2099 # if an exchange rate - change price
2102 sub get_pricegroups_for_parts {
2104 $main::lxdebug->enter_sub();
2106 my ($self, $myconfig, $form) = @_;
2108 my $dbh = $form->get_standard_dbh;
2110 $form->{"PRICES"} = {};
2114 my $all_units = AM->retrieve_units($myconfig, $form);
2115 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2116 $form->{"PRICES"}{$i} = [];
2118 $id = $form->{"id_$i"};
2120 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2121 $id = $form->{"new_id_$i"};
2124 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2126 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2128 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2129 my $sellprice = $form->{"sellprice_$i"};
2130 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2131 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2132 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2134 my $price_new = $form->{"price_new_$i"};
2135 my $price_old = $form->{"price_old_$i"};
2137 if (!$form->{"unit_old_$i"}) {
2138 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2139 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2140 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2141 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2144 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2145 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2146 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2148 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2149 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2150 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2151 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2152 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2153 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2154 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2159 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2160 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2161 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2162 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2163 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2167 if (!$form->{"basefactor_$i"}) {
2168 $form->{"basefactor_$i"} = 1;
2174 sellprice AS default_sellprice,
2177 'selected' AS selected
2183 parts.sellprice AS default_sellprice,
2184 pricegroup.pricegroup,
2188 LEFT JOIN parts ON parts.id = parts_id
2189 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2191 ORDER BY pricegroup|;
2192 my @values = (conv_i($id), conv_i($id));
2193 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2195 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2197 $pkr->{selected} = '';
2199 # if there is an exchange rate change price
2200 if (($form->{exchangerate} * 1) != 0) {
2201 $pkr->{price} /= $form->{exchangerate};
2204 $pkr->{price} *= $form->{"basefactor_$i"};
2205 $pkr->{price} *= $basefactor;
2206 $pkr->{price_ufmt} = $pkr->{price};
2207 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2209 if (!defined $selectedpricegroup_id) {
2210 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2211 # Case A: open old invoice, no pricegroup selected
2212 # Case B: add new article to invoice, no pricegroup selected
2214 # to distinguish case A and B the variable pricegroup_id_$i is used
2215 # for new articles this variable isn't defined, for loaded articles it is
2216 # sellprice can't be used, as it already has 0,00 set
2218 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2220 $pkr->{selected} = ' selected';
2221 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2222 and not defined $form->{"pricegroup_id_$i"}
2223 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2224 # for the case where pricegroup prices haven't been set
2226 # Case B: use default pricegroup of customer
2228 $pkr->{selected} = ' selected'; # unless $form->{selected};
2229 # no customer pricesgroup set
2230 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2232 $pkr->{price} = $form->{"sellprice_$i"};
2236 # this sub should not set anything and only return. --sschoeling, 20090506
2237 # is this correct? put in again... -- grichardson 20110119
2238 $form->{"sellprice_$i"} = $pkr->{price};
2241 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2242 $pkr->{price} = $form->{"sellprice_$i"};
2243 $pkr->{selected} = ' selected';
2247 # existing article: pricegroup or price changed
2248 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2249 if ($selectedpricegroup_id ne $pricegroup_old) {
2250 # pricegroup has changed
2251 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2252 $pkr->{selected} = ' selected';
2254 } elsif ( ($form->parse_amount($myconfig, $price_new)
2255 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2256 and ($price_new ne 0) and defined $price_new) {
2257 # sellprice has changed
2258 # when loading existing invoices $price_new is NULL
2259 if ($pkr->{pricegroup_id} == 0) {
2260 $pkr->{price} = $form->{"sellprice_$i"};
2261 $pkr->{selected} = ' selected';
2263 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2264 # neither sellprice nor pricegroup changed
2265 $pkr->{selected} = ' selected';
2266 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2267 # $pkr->{price} = $form->{"sellprice_$i"};
2269 $pkr->{price} = $form->{"sellprice_$i"};
2273 push @{ $form->{PRICES}{$i} }, $pkr;
2276 $form->{"basefactor_$i"} *= $basefactor;
2283 $main::lxdebug->leave_sub();
2287 $main::lxdebug->enter_sub();
2289 my ($self, $myconfig, $form, $table) = @_;
2291 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2293 # make sure there's no funny stuff in $table
2294 # ToDO: die when this happens and throw an error
2295 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2297 my $dbh = $form->get_standard_dbh;
2299 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2300 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2302 $main::lxdebug->leave_sub();
2308 $main::lxdebug->enter_sub();
2310 my ($self, $myconfig, $form, $table, $id) = @_;
2312 $main::lxdebug->leave_sub() and return 0 unless ($id);
2314 # make sure there's no funny stuff in $table
2315 # ToDO: die when this happens and throw an error
2316 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2318 my $dbh = $form->get_standard_dbh;
2320 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2321 my ($result) = selectrow_query($form, $dbh, $query, $id);
2323 $main::lxdebug->leave_sub();
2328 sub get_standard_accno_current_assets {
2329 $main::lxdebug->enter_sub();
2331 my ($self, $myconfig, $form) = @_;
2333 my $dbh = $form->get_standard_dbh;
2335 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2336 my ($result) = selectrow_query($form, $dbh, $query);
2338 $main::lxdebug->leave_sub();