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;
59 $main::lxdebug->enter_sub();
61 my ($self, $myconfig, $form, $locale) = @_;
63 $form->{duedate} ||= $form->{invdate};
66 my $dbh = $form->get_standard_dbh;
69 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
70 ($form->{terms}) = selectrow_query($form, $dbh, $query);
73 $form->{TEMPLATE_ARRAYS} = {};
75 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
77 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
80 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
81 $price_factors{$pfac->{id}} = $pfac;
83 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
86 # sort items by partsgroup
87 for my $i (1 .. $form->{rowcount}) {
89 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
90 # $partsgroup = $form->{"partsgroup_$i"};
92 # push @partsgroup, [$i, $partsgroup];
93 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
99 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
100 %projects_by_id = map { $_->id => $_ } @$projects;
103 if ($projects_by_id{$form->{"globalproject_id"}}) {
104 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
105 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
107 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
108 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
118 # sort items by partsgroup
119 for $i (1 .. $form->{rowcount}) {
121 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
122 $partsgroup = $form->{"partsgroup_$i"};
124 push @partsgroup, [$i, $partsgroup];
137 my $nodiscount_subtotal = 0;
138 my $discount_subtotal = 0;
140 my $subtotal_header = 0;
143 $form->{discount} = [];
145 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
147 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
148 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
151 qw(runningnumber number description longdescription qty ship unit bin
152 deliverydate_oe ordnumber_oe transdate_oe validuntil
153 partnotes serialnumber reqdate sellprice listprice netprice
154 discount p_discount discount_sub nodiscount_sub
155 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
156 price_factor price_factor_name partsgroup weight lineweight);
158 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
159 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
168 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
171 if ($item->[1] ne $sameitem) {
172 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
173 $sameitem = $item->[1];
175 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
178 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
180 if ($form->{"id_$i"} != 0) {
182 # add number, description and qty to $form->{number},
183 if ($form->{"subtotal_$i"} && !$subtotal_header) {
184 $subtotal_header = $i;
185 $position = int($position);
188 } elsif ($subtotal_header) {
190 $position = int($position);
191 $position = $position.".".$subposition;
193 $position = int($position);
197 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
199 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
200 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
207 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
212 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
217 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
218 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
219 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
220 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
222 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
223 my ($dec) = ($sellprice =~ /\.(\d+)/);
224 my $decimalplaces = max 2, length($dec);
226 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
228 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
229 my $linetotal = $form->round_amount($linetotal_exact, 2);
231 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
232 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
234 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
236 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
238 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
241 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
243 $linetotal = ($linetotal != 0) ? $linetotal : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
246 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
247 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
249 $form->{total} += $linetotal;
250 $form->{nodiscount_total} += $nodiscount_linetotal;
251 $form->{discount_total} += $discount;
253 if ($subtotal_header) {
254 $discount_subtotal += $linetotal;
255 $nodiscount_subtotal += $nodiscount_linetotal;
258 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
259 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
264 $discount_subtotal = 0;
265 $nodiscount_subtotal = 0;
266 $subtotal_header = 0;
269 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
272 if (!$form->{"discount_$i"}) {
273 $nodiscount += $linetotal;
276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
277 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
279 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
281 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
283 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
284 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
286 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
287 $totalweight += $lineweight;
288 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
289 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
290 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
291 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
293 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
297 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
299 if ($form->{taxincluded}) {
302 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
303 $taxbase = $linetotal - $taxamount;
305 $taxamount = $linetotal * $taxrate;
306 $taxbase = $linetotal;
309 if ($form->round_amount($taxrate, 7) == 0) {
310 if ($form->{taxincluded}) {
311 foreach my $accno (@taxaccounts) {
312 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
314 $taxaccounts{$accno} += $taxamount;
315 $taxdiff += $taxamount;
317 $taxbase{$accno} += $taxbase;
319 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
321 foreach my $accno (@taxaccounts) {
322 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
323 $taxbase{$accno} += $taxbase;
327 foreach my $accno (@taxaccounts) {
328 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
329 $taxbase{$accno} += $taxbase;
332 my $tax_rate = $taxrate * 100;
333 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
334 if ($form->{"assembly_$i"}) {
337 # get parts and push them onto the stack
339 if ($form->{groupitems}) {
341 qq|ORDER BY pg.partsgroup, a.oid|;
343 $sortorder = qq|ORDER BY a.oid|;
347 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
349 JOIN parts p ON (a.parts_id = p.id)
350 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
351 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
352 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
354 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
355 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
356 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
357 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
358 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
361 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
363 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
364 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
366 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
367 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
373 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
374 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
375 for @{ $ic_cvar_configs };
377 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
381 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
382 $form->{totalweight_nofmt} = $totalweight;
383 my $defaults = AM->get_defaults();
384 $form->{weightunit} = $defaults->{weightunit};
386 foreach my $item (sort keys %taxaccounts) {
387 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
389 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
390 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
391 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
392 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
393 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
394 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
395 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
397 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
398 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
399 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
402 for my $i (1 .. $form->{paidaccounts}) {
403 if ($form->{"paid_$i"}) {
404 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
406 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
407 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
408 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
409 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
410 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
412 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
415 if($form->{taxincluded}) {
416 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
417 $form->{subtotal_nofmt} = $form->{total} - $tax;
420 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
421 $form->{subtotal_nofmt} = $form->{total};
424 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
425 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
426 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
427 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
429 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
430 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
432 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
433 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
435 $form->set_payment_options($myconfig, $form->{invdate});
437 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
438 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
440 $form->{username} = $myconfig->{name};
442 $main::lxdebug->leave_sub();
445 sub project_description {
446 $main::lxdebug->enter_sub();
448 my ($self, $dbh, $id) = @_;
449 my $form = \%main::form;
451 my $query = qq|SELECT description FROM project WHERE id = ?|;
452 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
454 $main::lxdebug->leave_sub();
459 sub customer_details {
460 $main::lxdebug->enter_sub();
462 my ($self, $myconfig, $form, @wanted_vars) = @_;
464 # connect to database
465 my $dbh = $form->get_standard_dbh;
467 my $language_id = $form->{language_id};
469 # get contact id, set it if nessessary
472 my @values = (conv_i($form->{customer_id}));
475 if ($form->{cp_id}) {
476 $where = qq| AND (cp.cp_id = ?) |;
477 push(@values, conv_i($form->{cp_id}));
480 # get rest for the customer
482 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
483 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
486 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
487 LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
488 WHERE (ct.id = ?) $where
491 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
493 # remove id and taxincluded before copy back
494 delete @$ref{qw(id taxincluded)};
496 @wanted_vars = grep({ $_ } @wanted_vars);
497 if (scalar(@wanted_vars) > 0) {
499 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
500 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
503 map { $form->{$_} = $ref->{$_} } keys %$ref;
505 if ($form->{delivery_customer_id}) {
507 qq|SELECT *, notes as customernotes
511 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
513 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
516 if ($form->{delivery_vendor_id}) {
518 qq|SELECT *, notes as customernotes
522 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
524 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
527 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
529 'trans_id' => $form->{customer_id});
530 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
532 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
533 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
534 'language_id' => $language_id,
535 'allow_fallback' => 1);
538 $main::lxdebug->leave_sub();
542 $main::lxdebug->enter_sub();
544 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
546 # connect to database, turn off autocommit
547 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
549 my ($query, $sth, $null, $project_id, @values);
550 my $exchangerate = 0;
552 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
555 if (!$form->{employee_id}) {
556 $form->get_employee($dbh);
559 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
560 my $defaultcurrency = $form->{defaultcurrency};
562 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
563 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
564 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
565 if (!$form->{department_id}){
566 ($null, $form->{department_id}) = split(/--/, $form->{department});
569 my $all_units = AM->retrieve_units($myconfig, $form);
571 if (!$payments_only) {
573 &reverse_invoice($dbh, $form);
576 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
577 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
579 $query = qq|SELECT nextval('glid')|;
580 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
582 $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
583 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
585 if (!$form->{invnumber}) {
586 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
587 $form->{invnumber} = $trans_number->create_unique;
592 my ($netamount, $invoicediff) = (0, 0);
593 my ($amount, $linetotal, $lastincomeaccno);
595 if ($form->{currency} eq $defaultcurrency) {
596 $form->{exchangerate} = 1;
598 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
601 $form->{exchangerate} =
604 : $form->parse_amount($myconfig, $form->{exchangerate});
606 $form->{expense_inventory} = "";
610 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
611 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
614 $form->{amount} = {};
615 $form->{amount_cogs} = {};
617 foreach my $i (1 .. $form->{rowcount}) {
618 if ($form->{type} eq "credit_note") {
619 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
620 $form->{shipped} = 1;
622 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
627 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
628 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
629 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
631 if ($form->{storno}) {
632 $form->{"qty_$i"} *= -1;
635 if ($form->{"id_$i"}) {
638 if (defined($baseunits{$form->{"id_$i"}})) {
639 $item_unit = $baseunits{$form->{"id_$i"}};
642 $query = qq|SELECT unit FROM parts WHERE id = ?|;
643 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
644 $baseunits{$form->{"id_$i"}} = $item_unit;
647 if (defined($all_units->{$item_unit}->{factor})
648 && ($all_units->{$item_unit}->{factor} ne '')
649 && ($all_units->{$item_unit}->{factor} != 0)) {
650 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
654 $baseqty = $form->{"qty_$i"} * $basefactor;
656 my ($allocated, $taxrate) = (0, 0);
660 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
662 # keep entered selling price
664 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
666 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
668 my $decimalplaces = ($dec > 2) ? $dec : 2;
670 # undo discount formatting
671 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
674 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
676 # round linetotal to 2 decimal places
677 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
678 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
680 if ($form->{taxincluded}) {
681 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
682 $form->{"sellprice_$i"} =
683 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
685 $taxamount = $linetotal * $taxrate;
688 $netamount += $linetotal;
690 if ($taxamount != 0) {
692 $form->{amount}{ $form->{id} }{$_} +=
693 $taxamount * $form->{"${_}_rate"} / $taxrate
694 } split(/ /, $form->{"taxaccounts_$i"});
697 # add amount to income, $form->{amount}{trans_id}{accno}
698 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
700 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
701 $linetotal = $form->round_amount($linetotal, 2);
703 # this is the difference from the inventory
704 $invoicediff += ($amount - $linetotal);
706 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
709 $lastincomeaccno = $form->{"income_accno_$i"};
711 # adjust and round sellprice
712 $form->{"sellprice_$i"} =
713 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
716 next if $payments_only;
718 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
720 if ($form->{"assembly_$i"}) {
721 # record assembly item as allocated
722 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
725 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
729 # Get pricegroup_id and save it. Unfortunately the interface
730 # also uses ID "0" for signalling that none is selected, but "0"
731 # must not be stored in the database. Therefore we cannot simply
733 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
735 $pricegroup_id = undef if !$pricegroup_id;
737 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
739 # save detail record in invoice table
741 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
742 sellprice, fxsellprice, discount, allocated, assemblyitem,
743 unit, deliverydate, project_id, serialnumber, pricegroup_id,
744 ordnumber, transdate, cusordnumber, base_qty, subtotal,
745 marge_percent, marge_total, lastcost,
746 price_factor_id, price_factor, marge_price_factor)
747 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
748 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
750 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
751 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
752 $form->{"sellprice_$i"}, $fxsellprice,
753 $form->{"discount_$i"}, $allocated, 'f',
754 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
755 $form->{"serialnumber_$i"}, $pricegroup_id,
756 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
757 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
758 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
759 $form->{"lastcost_$i"},
760 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
761 conv_i($form->{"marge_price_factor_$i"}));
762 do_query($form, $dbh, $query, @values);
764 CVar->save_custom_variables(module => 'IC',
765 sub_module => 'invoice',
766 trans_id => $invoice_id,
767 configs => $ic_cvar_configs,
769 name_prefix => 'ic_',
770 name_postfix => "_$i",
775 # total payments, don't move we need it here
776 for my $i (1 .. $form->{paidaccounts}) {
777 if ($form->{type} eq "credit_note") {
778 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
780 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
782 $form->{paid} += $form->{"paid_$i"};
783 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
786 my ($tax, $diff) = (0, 0);
788 $netamount = $form->round_amount($netamount, 2);
790 # figure out rounding errors for total amount vs netamount + taxes
791 if ($form->{taxincluded}) {
793 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
794 $diff += $amount - $netamount * $form->{exchangerate};
795 $netamount = $amount;
797 foreach my $item (split(/ /, $form->{taxaccounts})) {
798 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
799 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
800 $tax += $form->{amount}{ $form->{id} }{$item};
801 $netamount -= $form->{amount}{ $form->{id} }{$item};
804 $invoicediff += $diff;
805 ######## this only applies to tax included
806 if ($lastincomeaccno) {
807 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
811 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
812 $diff = $amount - $netamount * $form->{exchangerate};
813 $netamount = $amount;
814 foreach my $item (split(/ /, $form->{taxaccounts})) {
815 $form->{amount}{ $form->{id} }{$item} =
816 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
819 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
822 $amount - $form->{amount}{ $form->{id} }{$item} *
823 $form->{exchangerate};
824 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
825 $tax += $form->{amount}{ $form->{id} }{$item};
829 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
831 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
834 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
836 # update exchangerate
837 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
838 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
839 $form->{exchangerate}, 0);
842 $project_id = conv_i($form->{"globalproject_id"});
843 # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
844 my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
846 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
847 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
848 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
850 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
852 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
854 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
855 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
856 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
857 do_query($form, $dbh, $query, @values);
858 $form->{amount_cogs}{$trans_id}{$accno} = 0;
862 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
863 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
865 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
867 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
868 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
869 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
870 do_query($form, $dbh, $query, @values);
875 foreach my $trans_id (keys %{ $form->{amount} }) {
876 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
877 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
879 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
881 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
883 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
884 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
887 WHERE chart_id= (SELECT id
891 ORDER BY startdate DESC LIMIT 1),
894 WHERE chart_id= (SELECT id
898 ORDER BY startdate DESC LIMIT 1),
900 (SELECT link FROM chart WHERE accno = ?))|;
901 @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);
902 do_query($form, $dbh, $query, @values);
903 $form->{amount}{$trans_id}{$accno} = 0;
907 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
908 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
910 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
912 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
913 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
916 WHERE chart_id= (SELECT id
920 ORDER BY startdate DESC LIMIT 1),
923 WHERE chart_id= (SELECT id
927 ORDER BY startdate DESC LIMIT 1),
929 (SELECT link FROM chart WHERE accno = ?))|;
930 @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);
931 do_query($form, $dbh, $query, @values);
936 # deduct payment differences from diff
937 for my $i (1 .. $form->{paidaccounts}) {
938 if ($form->{"paid_$i"} != 0) {
940 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
941 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
945 # record payments and offsetting AR
946 if (!$form->{storno}) {
947 for my $i (1 .. $form->{paidaccounts}) {
949 if ($form->{"acc_trans_id_$i"}
951 && (SL::DB::Default->get->payments_changeable == 0)) {
955 next if ($form->{"paid_$i"} == 0);
957 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
958 $form->{"datepaid_$i"} = $form->{invdate}
959 unless ($form->{"datepaid_$i"});
960 $form->{datepaid} = $form->{"datepaid_$i"};
964 if ($form->{currency} eq $defaultcurrency) {
965 $form->{"exchangerate_$i"} = 1;
967 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
968 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
972 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
974 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
976 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
977 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
980 WHERE chart_id= (SELECT id
984 ORDER BY startdate DESC LIMIT 1),
987 WHERE chart_id= (SELECT id
991 ORDER BY startdate DESC LIMIT 1),
993 (SELECT link FROM chart WHERE accno = ?))|;
994 @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});
995 do_query($form, $dbh, $query, @values);
999 $form->{"paid_$i"} *= -1;
1000 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
1003 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
1004 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
1007 WHERE chart_id= (SELECT id
1011 ORDER BY startdate DESC LIMIT 1),
1014 WHERE chart_id= (SELECT id
1018 ORDER BY startdate DESC LIMIT 1),
1020 (SELECT link FROM chart WHERE accno = ?))|;
1021 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
1022 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
1023 do_query($form, $dbh, $query, @values);
1025 # exchangerate difference
1026 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
1027 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
1031 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
1032 $form->{"exchangerate_$i"};
1034 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
1036 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
1041 # update exchange rate
1042 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
1043 $form->update_exchangerate($dbh, $form->{currency},
1044 $form->{"datepaid_$i"},
1045 $form->{"exchangerate_$i"}, 0);
1049 } else { # if (!$form->{storno})
1050 $form->{marge_total} *= -1;
1053 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
1055 # record exchange rate differences and gains/losses
1056 foreach my $accno (keys %{ $form->{fx} }) {
1057 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
1058 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
1059 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
1062 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
1063 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
1066 WHERE chart_id= (SELECT id
1070 ORDER BY startdate DESC LIMIT 1),
1073 WHERE chart_id= (SELECT id
1077 ORDER BY startdate DESC LIMIT 1),
1079 (SELECT link FROM chart WHERE accno = ?))|;
1080 @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);
1081 do_query($form, $dbh, $query, @values);
1086 if ($payments_only) {
1087 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
1088 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
1090 $dbh->commit if !$provided_dbh;
1092 $main::lxdebug->leave_sub();
1096 $amount = $netamount + $tax;
1099 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1101 $query = qq|UPDATE ar set
1102 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1103 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1104 amount = ?, netamount = ?, paid = ?,
1105 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1106 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1107 currency_id = (SELECT id FROM currencies WHERE name = ?),
1108 department_id = ?, payment_id = ?, taxincluded = ?,
1109 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1110 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1111 cp_id = ?, marge_total = ?, marge_percent = ?,
1112 globalproject_id = ?, delivery_customer_id = ?,
1113 transaction_description = ?, delivery_vendor_id = ?,
1114 donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
1115 delivery_term_id = ?
1117 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1118 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1119 $amount, $netamount, $form->{"paid"},
1120 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1121 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1122 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1123 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1124 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1125 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1126 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1127 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1128 $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
1129 conv_i($form->{delivery_term_id}),
1130 conv_i($form->{"id"}));
1131 do_query($form, $dbh, $query, @values);
1134 if ($form->{storno}) {
1137 paid = paid + amount,
1139 intnotes = ? || intnotes
1141 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1142 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1146 $form->{name} = $form->{customer};
1147 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1149 if (!$form->{shipto_id}) {
1150 $form->add_shipto($dbh, $form->{id}, "AR");
1153 # save printed, emailed and queued
1154 $form->save_status($dbh);
1156 Common::webdav_folder($form);
1158 # Link this record to the records it was created from.
1159 RecordLinks->create_links('dbh' => $dbh,
1161 'from_table' => 'oe',
1162 'from_ids' => $form->{convert_from_oe_ids},
1164 'to_id' => $form->{id},
1166 delete $form->{convert_from_oe_ids};
1168 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1170 if (scalar @convert_from_do_ids) {
1171 DO->close_orders('dbh' => $dbh,
1172 'ids' => \@convert_from_do_ids);
1174 RecordLinks->create_links('dbh' => $dbh,
1176 'from_table' => 'delivery_orders',
1177 'from_ids' => \@convert_from_do_ids,
1179 'to_id' => $form->{id},
1182 delete $form->{convert_from_do_ids};
1184 ARAP->close_orders_if_billed('dbh' => $dbh,
1185 'arap_id' => $form->{id},
1188 # safety check datev export
1189 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1190 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1191 $transdate ||= DateTime->today;
1193 my $datev = SL::DATEV->new(
1194 exporttype => DATEV_ET_BUCHUNGEN,
1195 format => DATEV_FORMAT_KNE,
1199 trans_id => $form->{id},
1204 if ($datev->errors) {
1206 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1211 $dbh->commit if !$provided_dbh;
1213 $main::lxdebug->leave_sub();
1218 sub _delete_payments {
1219 $main::lxdebug->enter_sub();
1221 my ($self, $form, $dbh) = @_;
1223 my @delete_acc_trans_ids;
1225 # Delete old payment entries from acc_trans.
1227 qq|SELECT acc_trans_id
1229 WHERE (trans_id = ?) AND fx_transaction
1233 SELECT at.acc_trans_id
1235 LEFT JOIN chart c ON (at.chart_id = c.id)
1236 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1237 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1240 qq|SELECT at.acc_trans_id
1242 LEFT JOIN chart c ON (at.chart_id = c.id)
1243 WHERE (trans_id = ?)
1244 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1245 ORDER BY at.acc_trans_id
1247 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1249 if (@delete_acc_trans_ids) {
1250 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1251 do_query($form, $dbh, $query);
1254 $main::lxdebug->leave_sub();
1258 $main::lxdebug->enter_sub();
1260 my ($self, $myconfig, $form, $locale) = @_;
1262 # connect to database, turn off autocommit
1263 my $dbh = $form->get_standard_dbh;
1265 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1267 $old_form = save_form();
1269 # Delete all entries in acc_trans from prior payments.
1270 if (SL::DB::Default->get->payments_changeable != 0) {
1271 $self->_delete_payments($form, $dbh);
1274 # Save the new payments the user made before cleaning up $form.
1275 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 };
1277 # Clean up $form so that old content won't tamper the results.
1278 %keep_vars = map { $_, 1 } qw(login password id);
1279 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1281 # Retrieve the invoice from the database.
1282 $self->retrieve_invoice($myconfig, $form);
1284 # Set up the content of $form in the way that IS::post_invoice() expects.
1285 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1287 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1288 $item = $form->{invoice_details}->[$row - 1];
1290 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1292 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1295 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1297 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1299 # Restore the payment options from the user input.
1300 map { $form->{$_} = $payments{$_} } keys %payments;
1302 # Get the AR accno (which is normally done by Form::create_links()).
1306 LEFT JOIN chart c ON (at.chart_id = c.id)
1307 WHERE (trans_id = ?)
1308 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1309 ORDER BY at.acc_trans_id
1312 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1314 # Post the new payments.
1315 $self->post_invoice($myconfig, $form, $dbh, 1);
1317 restore_form($old_form);
1319 my $rc = $dbh->commit();
1321 $main::lxdebug->leave_sub();
1326 sub process_assembly {
1327 $main::lxdebug->enter_sub();
1329 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1332 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1333 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1335 JOIN parts p ON (a.parts_id = p.id)
1337 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1339 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1343 $ref->{inventory_accno_id} *= 1;
1344 $ref->{expense_accno_id} *= 1;
1346 # multiply by number of assemblies
1347 $ref->{qty} *= $totalqty;
1349 if ($ref->{assembly}) {
1350 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1353 if ($ref->{inventory_accno_id}) {
1354 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1358 # save detail record for individual assembly item in invoice table
1360 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1361 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1362 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1363 do_query($form, $dbh, $query, @values);
1369 $main::lxdebug->leave_sub();
1373 $main::lxdebug->enter_sub();
1375 # adjust allocated in table invoice according to FIFO princicple
1376 # for a certain part with part_id $id
1378 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1382 $form->{taxzone_id} *=1;
1383 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1384 my $taxzone_id = $form->{"taxzone_id"} * 1;
1386 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1387 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1388 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1389 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1390 FROM invoice i, parts p
1391 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1392 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1393 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1394 WHERE (i.parts_id = p.id)
1395 AND (i.parts_id = ?)
1396 AND ((i.base_qty + i.allocated) < 0)
1398 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1403 # all invoice entries of an example part:
1405 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1406 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1407 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1408 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1409 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1410 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1411 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1413 # 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
1414 # and all parts have been allocated
1416 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1417 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1419 # in this example there are still 4 unsold articles
1422 # search all invoice entries for the part in question, adjusting "allocated"
1423 # until the total number of sold parts has been reached
1425 # ORDER BY trans_id ensures FIFO
1428 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1429 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1433 # update allocated in invoice
1434 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1436 # total expenses and inventory
1437 # sellprice is the cost of the item
1438 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1440 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1441 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1442 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1444 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1445 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1446 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1448 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1449 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1455 last if (($totalqty -= $qty) <= 0);
1460 $main::lxdebug->leave_sub();
1465 sub reverse_invoice {
1466 $main::lxdebug->enter_sub();
1468 my ($dbh, $form) = @_;
1470 # reverse inventory items
1472 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1474 JOIN parts p ON (i.parts_id = p.id)
1475 WHERE i.trans_id = ?|;
1476 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1478 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1480 if ($ref->{inventory_accno_id}) {
1481 # de-allocated purchases
1483 qq|SELECT i.id, i.trans_id, i.allocated
1485 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1486 ORDER BY i.trans_id DESC|;
1487 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1489 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1490 my $qty = $ref->{qty};
1491 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1492 $qty = $inhref->{allocated};
1496 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1498 last if (($ref->{qty} -= $qty) <= 0);
1507 my @values = (conv_i($form->{id}));
1508 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1509 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1510 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1512 $main::lxdebug->leave_sub();
1515 sub delete_invoice {
1516 $main::lxdebug->enter_sub();
1518 my ($self, $myconfig, $form) = @_;
1520 # connect to database
1521 my $dbh = $form->get_standard_dbh;
1523 &reverse_invoice($dbh, $form);
1525 my @values = (conv_i($form->{id}));
1527 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1528 # zurückgesetzt werden. Vgl:
1529 # id | storno | storno_id | paid | amount
1530 #----+--------+-----------+---------+-----------
1531 # 18 | f | | 0.00000 | 119.00000
1533 # 18 | t | | 119.00000 | 119.00000
1535 if($form->{storno}){
1536 # storno_id auslesen und korrigieren
1537 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1538 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1541 # delete spool files
1542 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1545 qq|DELETE FROM status WHERE trans_id = ?|,
1546 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1547 qq|DELETE FROM ar WHERE id = ?|,
1550 map { do_query($form, $dbh, $_, @values) } @queries;
1552 my $rc = $dbh->commit;
1555 my $spool = $::lx_office_conf{paths}->{spool};
1556 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1559 $main::lxdebug->leave_sub();
1564 sub retrieve_invoice {
1565 $main::lxdebug->enter_sub();
1567 my ($self, $myconfig, $form) = @_;
1569 # connect to database
1570 my $dbh = $form->get_standard_dbh;
1572 my ($sth, $ref, $query);
1574 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1578 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1579 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1580 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1581 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1582 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
1586 $ref = selectfirst_hashref_query($form, $dbh, $query);
1587 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1590 my $id = conv_i($form->{id});
1593 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1597 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1598 a.orddate, a.quodate, a.globalproject_id,
1599 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1600 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1601 a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
1602 a.employee_id, a.salesman_id, a.payment_id,
1603 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1604 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1605 a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
1608 LEFT JOIN employee e ON (e.id = a.employee_id)
1610 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1611 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1613 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1616 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1617 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1619 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1621 foreach my $vc (qw(customer vendor)) {
1622 next if !$form->{"delivery_${vc}_id"};
1623 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1626 # get printed, emailed
1627 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1628 $sth = prepare_execute_query($form, $dbh, $query, $id);
1630 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1631 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1632 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1633 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1636 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1638 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1639 : $form->{invdate} ? $dbh->quote($form->{invdate})
1643 my $taxzone_id = $form->{taxzone_id} *= 1;
1644 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1646 # retrieve individual items
1649 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1650 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1651 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1654 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1655 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1656 i.price_factor_id, i.price_factor, i.marge_price_factor,
1657 p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1658 pr.projectnumber, pg.partsgroup, prg.pricegroup
1661 LEFT JOIN parts p ON (i.parts_id = p.id)
1662 LEFT JOIN project pr ON (i.project_id = pr.id)
1663 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1664 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1666 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1667 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1668 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1670 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1672 $sth = prepare_execute_query($form, $dbh, $query, $id);
1674 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1675 # Retrieve custom variables.
1676 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1678 sub_module => 'invoice',
1679 trans_id => $ref->{invoice_id},
1681 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1682 delete $ref->{invoice_id};
1684 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1685 delete($ref->{"part_inventory_accno_id"});
1687 foreach my $type (qw(inventory income expense)) {
1688 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1689 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1690 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1694 # get tax rates and description
1695 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1697 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1698 LEFT JOIN chart c ON (c.id = t.chart_id)
1700 (SELECT tk.tax_id FROM taxkeys tk
1701 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1702 AND startdate <= date($transdate)
1703 ORDER BY startdate DESC LIMIT 1)
1705 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1706 $ref->{taxaccounts} = "";
1708 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1710 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1714 $ref->{taxaccounts} .= "$ptr->{accno} ";
1716 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1717 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1718 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1719 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1720 $form->{taxaccounts} .= "$ptr->{accno} ";
1725 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1727 chop $ref->{taxaccounts};
1728 push @{ $form->{invoice_details} }, $ref;
1733 Common::webdav_folder($form);
1736 my $rc = $dbh->commit;
1738 $main::lxdebug->leave_sub();
1744 $main::lxdebug->enter_sub();
1746 my ($self, $myconfig, $form) = @_;
1748 # connect to database
1749 my $dbh = $form->get_standard_dbh;
1751 my $dateformat = $myconfig->{dateformat};
1752 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1754 my (@values, $duedate, $ref, $query);
1756 if ($form->{invdate}) {
1757 $duedate = "to_date(?, '$dateformat')";
1758 push @values, $form->{invdate};
1760 $duedate = "current_date";
1763 my $cid = conv_i($form->{customer_id});
1766 if ($form->{payment_id}) {
1767 $payment_id = "(pt.id = ?) OR";
1768 push @values, conv_i($form->{payment_id});
1774 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1775 c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
1776 c.street, c.zipcode, c.city, c.country,
1777 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
1778 c.taxincluded_checked, c.direct_debit,
1779 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1780 b.discount AS tradediscount, b.description AS business
1782 LEFT JOIN business b ON (b.id = c.business_id)
1783 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1784 LEFT JOIN currencies cu ON (c.currency_id=cu.id)
1787 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1789 delete $ref->{salesman_id} if !$ref->{salesman_id};
1791 map { $form->{$_} = $ref->{$_} } keys %$ref;
1793 # use customer currency
1794 $form->{currency} = $form->{curr};
1797 qq|SELECT sum(amount - paid) AS dunning_amount
1799 WHERE (paid < amount)
1800 AND (customer_id = ?)
1801 AND (dunning_config_id IS NOT NULL)|;
1802 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1803 map { $form->{$_} = $ref->{$_} } keys %$ref;
1806 qq|SELECT dnn.dunning_description AS max_dunning_level
1807 FROM dunning_config dnn
1808 WHERE id IN (SELECT dunning_config_id
1810 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1811 ORDER BY dunning_level DESC LIMIT 1|;
1812 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1813 map { $form->{$_} = $ref->{$_} } keys %$ref;
1815 $form->{creditremaining} = $form->{creditlimit};
1816 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1817 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1818 $form->{creditremaining} -= $value;
1822 (SELECT e.buy FROM exchangerate e
1823 WHERE e.currency_id = o.currency_id
1824 AND e.transdate = o.transdate)
1826 WHERE o.customer_id = ?
1827 AND o.quotation = '0'
1828 AND o.closed = '0'|;
1829 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1831 while (my ($amount, $exch) = $sth->fetchrow_array) {
1832 $exch = 1 unless $exch;
1833 $form->{creditremaining} -= $amount * $exch;
1837 # get shipto if we did not converted an order or invoice
1838 if (!$form->{shipto}) {
1839 map { delete $form->{$_} }
1840 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1841 shiptostreet shiptozipcode shiptocity shiptocountry
1842 shiptocontact shiptophone shiptofax shiptoemail);
1844 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1845 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1847 map { $form->{$_} = $ref->{$_} } keys %$ref;
1850 # setup last accounts used for this customer
1851 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1853 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1855 JOIN acc_trans ac ON (ac.chart_id = c.id)
1856 JOIN ar a ON (a.id = ac.trans_id)
1857 WHERE a.customer_id = ?
1858 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1859 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1860 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1863 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1864 if ($ref->{category} eq 'I') {
1866 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1868 if ($form->{initial_transdate}) {
1870 qq|SELECT tk.tax_id, t.rate
1872 LEFT JOIN tax t ON tk.tax_id = t.id
1873 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1874 ORDER BY tk.startdate DESC
1876 my ($tax_id, $rate) =
1877 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1878 $form->{initial_transdate});
1879 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1882 if ($ref->{category} eq 'A') {
1883 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1887 $form->{rowcount} = $i if ($i && !$form->{type});
1890 $main::lxdebug->leave_sub();
1894 $main::lxdebug->enter_sub();
1896 my ($self, $myconfig, $form) = @_;
1898 # connect to database
1899 my $dbh = $form->get_standard_dbh;
1901 my $i = $form->{rowcount};
1903 my $where = qq|NOT p.obsolete = '1'|;
1906 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1907 my ($table, $field) = split m/\./, $column;
1908 next if !$form->{"${field}_${i}"};
1909 $where .= qq| AND lower(${column}) ILIKE ?|;
1910 push @values, '%' . $form->{"${field}_${i}"} . '%';
1913 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1914 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1915 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1916 push @values, $form->{"partnumber_$i"};
1919 # Search for part ID overrides all other criteria.
1920 if ($form->{"id_${i}"}) {
1921 $where = qq|p.id = ?|;
1922 @values = ($form->{"id_${i}"});
1925 if ($form->{"description_$i"}) {
1926 $where .= qq| ORDER BY p.description|;
1928 $where .= qq| ORDER BY p.partnumber|;
1932 if ($form->{type} eq "invoice") {
1934 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1935 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1939 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1943 my $taxzone_id = $form->{taxzone_id} * 1;
1944 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1948 p.id, p.partnumber, p.description, p.sellprice,
1949 p.listprice, p.inventory_accno_id, p.lastcost,
1951 c1.accno AS inventory_accno,
1952 c1.new_chart_id AS inventory_new_chart,
1953 date($transdate) - c1.valid_from AS inventory_valid,
1955 c2.accno AS income_accno,
1956 c2.new_chart_id AS income_new_chart,
1957 date($transdate) - c2.valid_from AS income_valid,
1959 c3.accno AS expense_accno,
1960 c3.new_chart_id AS expense_new_chart,
1961 date($transdate) - c3.valid_from AS expense_valid,
1963 p.unit, p.assembly, p.onhand,
1964 p.notes AS partnotes, p.notes AS longdescription,
1965 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1966 p.price_factor_id, p.weight,
1968 pfac.factor AS price_factor,
1973 LEFT JOIN chart c1 ON
1974 ((SELECT inventory_accno_id
1975 FROM buchungsgruppen
1976 WHERE id = p.buchungsgruppen_id) = c1.id)
1977 LEFT JOIN chart c2 ON
1978 ((SELECT income_accno_id_${taxzone_id}
1979 FROM buchungsgruppen
1980 WHERE id = p.buchungsgruppen_id) = c2.id)
1981 LEFT JOIN chart c3 ON
1982 ((SELECT expense_accno_id_${taxzone_id}
1983 FROM buchungsgruppen
1984 WHERE id = p.buchungsgruppen_id) = c3.id)
1985 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1986 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1988 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1990 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1992 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1993 [ qq|SELECT tr.translation, tr.longdescription
1995 WHERE tr.language_id IN
1998 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
2001 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
2003 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
2005 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
2006 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
2007 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
2008 if (!$ref->{inventory_accno_id}) {
2009 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
2011 delete($ref->{inventory_accno_id});
2013 foreach my $type (qw(inventory income expense)) {
2014 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
2016 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
2019 ($ref->{"${type}_accno"},
2020 $ref->{"${type}_new_chart"},
2021 $ref->{"${type}_valid"})
2022 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
2026 if ($form->{payment_id} eq "") {
2027 $form->{payment_id} = $form->{part_payment_id};
2030 # get tax rates and description
2031 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
2033 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
2035 LEFT JOIN chart c ON (c.id = t.chart_id)
2039 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
2041 ORDER BY startdate DESC
2044 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
2045 my $stw = $dbh->prepare($query);
2046 $stw->execute(@values) || $form->dberror($query);
2048 $ref->{taxaccounts} = "";
2050 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
2052 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
2056 $ref->{taxaccounts} .= "$ptr->{accno} ";
2058 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
2059 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
2060 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
2061 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
2062 $form->{taxaccounts} .= "$ptr->{accno} ";
2068 chop $ref->{taxaccounts};
2070 if ($form->{language_id}) {
2071 for my $spec (@translation_queries) {
2072 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
2073 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
2074 next unless $translation;
2075 $ref->{description} = $translation;
2076 $ref->{longdescription} = $longdescription;
2081 $ref->{onhand} *= 1;
2083 push @{ $form->{item_list} }, $ref;
2086 $_->[1]->finish for @translation_queries;
2088 foreach my $item (@{ $form->{item_list} }) {
2089 my $custom_variables = CVar->get_custom_variables(module => 'IC',
2090 trans_id => $item->{id},
2094 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2097 $main::lxdebug->leave_sub();
2100 ##########################
2101 # get pricegroups from database
2102 # build up selected pricegroup
2103 # if an exchange rate - change price
2106 sub get_pricegroups_for_parts {
2108 $main::lxdebug->enter_sub();
2110 my ($self, $myconfig, $form) = @_;
2112 my $dbh = $form->get_standard_dbh;
2114 $form->{"PRICES"} = {};
2118 my $all_units = AM->retrieve_units($myconfig, $form);
2119 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2120 $form->{"PRICES"}{$i} = [];
2122 $id = $form->{"id_$i"};
2124 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2125 $id = $form->{"new_id_$i"};
2128 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2130 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2132 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2133 my $sellprice = $form->{"sellprice_$i"};
2134 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2135 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2136 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2138 my $price_new = $form->{"price_new_$i"};
2139 my $price_old = $form->{"price_old_$i"};
2141 if (!$form->{"unit_old_$i"}) {
2142 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2143 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2144 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2145 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2148 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2149 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2150 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2152 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2153 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2154 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2155 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2156 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2157 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2158 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2163 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2164 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2165 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2166 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2167 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2171 if (!$form->{"basefactor_$i"}) {
2172 $form->{"basefactor_$i"} = 1;
2178 sellprice AS default_sellprice,
2181 'selected' AS selected
2187 parts.sellprice AS default_sellprice,
2188 pricegroup.pricegroup,
2192 LEFT JOIN parts ON parts.id = parts_id
2193 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2195 ORDER BY pricegroup|;
2196 my @values = (conv_i($id), conv_i($id));
2197 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2199 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2201 $pkr->{selected} = '';
2203 # if there is an exchange rate change price
2204 if (($form->{exchangerate} * 1) != 0) {
2205 $pkr->{price} /= $form->{exchangerate};
2208 $pkr->{price} *= $form->{"basefactor_$i"};
2209 $pkr->{price} *= $basefactor;
2210 $pkr->{price_ufmt} = $pkr->{price};
2211 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2213 if (!defined $selectedpricegroup_id) {
2214 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2215 # Case A: open old invoice, no pricegroup selected
2216 # Case B: add new article to invoice, no pricegroup selected
2218 # to distinguish case A and B the variable pricegroup_id_$i is used
2219 # for new articles this variable isn't defined, for loaded articles it is
2220 # sellprice can't be used, as it already has 0,00 set
2222 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2224 $pkr->{selected} = ' selected';
2225 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2226 and not defined $form->{"pricegroup_id_$i"}
2227 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2228 # for the case where pricegroup prices haven't been set
2230 # Case B: use default pricegroup of customer
2232 $pkr->{selected} = ' selected'; # unless $form->{selected};
2233 # no customer pricesgroup set
2234 if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
2236 $pkr->{price} = $form->{"sellprice_$i"};
2240 # this sub should not set anything and only return. --sschoeling, 20090506
2241 # is this correct? put in again... -- grichardson 20110119
2242 $form->{"sellprice_$i"} = $pkr->{price};
2245 } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2246 $pkr->{price} = $form->{"sellprice_$i"};
2247 $pkr->{selected} = ' selected';
2251 # existing article: pricegroup or price changed
2252 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2253 if ($selectedpricegroup_id ne $pricegroup_old) {
2254 # pricegroup has changed
2255 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2256 $pkr->{selected} = ' selected';
2258 } elsif ( ($form->parse_amount($myconfig, $price_new)
2259 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2260 and ($price_new ne 0) and defined $price_new) {
2261 # sellprice has changed
2262 # when loading existing invoices $price_new is NULL
2263 if ($pkr->{pricegroup_id} == 0) {
2264 $pkr->{price} = $form->{"sellprice_$i"};
2265 $pkr->{selected} = ' selected';
2267 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2268 # neither sellprice nor pricegroup changed
2269 $pkr->{selected} = ' selected';
2270 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2271 # $pkr->{price} = $form->{"sellprice_$i"};
2273 $pkr->{price} = $form->{"sellprice_$i"};
2277 push @{ $form->{PRICES}{$i} }, $pkr;
2280 $form->{"basefactor_$i"} *= $basefactor;
2287 $main::lxdebug->leave_sub();
2291 $main::lxdebug->enter_sub();
2293 my ($self, $myconfig, $form, $table) = @_;
2295 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2297 # make sure there's no funny stuff in $table
2298 # ToDO: die when this happens and throw an error
2299 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2301 my $dbh = $form->get_standard_dbh;
2303 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2304 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2306 $main::lxdebug->leave_sub();
2312 $main::lxdebug->enter_sub();
2314 my ($self, $myconfig, $form, $table, $id) = @_;
2316 $main::lxdebug->leave_sub() and return 0 unless ($id);
2318 # make sure there's no funny stuff in $table
2319 # ToDO: die when this happens and throw an error
2320 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2322 my $dbh = $form->get_standard_dbh;
2324 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2325 my ($result) = selectrow_query($form, $dbh, $query, $id);
2327 $main::lxdebug->leave_sub();
2332 sub get_standard_accno_current_assets {
2333 $main::lxdebug->enter_sub();
2335 my ($self, $myconfig, $form) = @_;
2337 my $dbh = $form->get_standard_dbh;
2339 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2340 my ($result) = selectrow_query($form, $dbh, $query);
2342 $main::lxdebug->leave_sub();