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;
56 $main::lxdebug->enter_sub();
58 my ($self, $myconfig, $form, $locale) = @_;
60 $form->{duedate} ||= $form->{invdate};
63 my $dbh = $form->get_standard_dbh;
66 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
67 ($form->{terms}) = selectrow_query($form, $dbh, $query);
69 my (@project_ids, %projectnumbers, %projectdescriptions);
70 $form->{TEMPLATE_ARRAYS} = {};
72 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
74 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
77 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
78 $price_factors{$pfac->{id}} = $pfac;
80 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
83 # sort items by partsgroup
84 for my $i (1 .. $form->{rowcount}) {
86 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
87 # $partsgroup = $form->{"partsgroup_$i"};
89 # push @partsgroup, [$i, $partsgroup];
90 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
94 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
95 join(", ", map({ "?" } @project_ids)) . ")";
96 $sth = $dbh->prepare($query);
97 $sth->execute(@project_ids) ||
98 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
99 while (my $ref = $sth->fetchrow_hashref()) {
100 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
101 $projectdescriptions{$ref->{id}} = $ref->{description};
106 $form->{"globalprojectnumber"} =
107 $projectnumbers{$form->{"globalproject_id"}};
108 $form->{"globalprojectdescription"} =
109 $projectdescriptions{$form->{"globalproject_id"}};
116 my %oid = ('Pg' => 'oid',
117 'Oracle' => 'rowid');
119 # sort items by partsgroup
120 for $i (1 .. $form->{rowcount}) {
122 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
123 $partsgroup = $form->{"partsgroup_$i"};
125 push @partsgroup, [$i, $partsgroup];
138 my $nodiscount_subtotal = 0;
139 my $discount_subtotal = 0;
141 my $subtotal_header = 0;
144 $form->{discount} = [];
146 IC->prepare_parts_for_printing();
148 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
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);
158 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
160 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
162 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
164 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
166 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
169 if ($item->[1] ne $sameitem) {
170 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
171 $sameitem = $item->[1];
173 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
176 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
178 if ($form->{"id_$i"} != 0) {
180 # add number, description and qty to $form->{number},
181 if ($form->{"subtotal_$i"} && !$subtotal_header) {
182 $subtotal_header = $i;
183 $position = int($position);
186 } elsif ($subtotal_header) {
188 $position = int($position);
189 $position = $position.".".$subposition;
191 $position = int($position);
195 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
197 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
198 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
210 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
216 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
217 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
218 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
220 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
221 my ($dec) = ($sellprice =~ /\.(\d+)/);
222 my $decimalplaces = max 2, length($dec);
224 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
225 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
226 my $linetotal = $form->round_amount($linetotal_exact, 2);
227 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
229 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
230 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
232 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
233 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
235 $linetotal = ($linetotal != 0) ? $linetotal : '';
237 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
241 $form->{total} += $linetotal;
242 $form->{nodiscount_total} += $nodiscount_linetotal;
243 $form->{discount_total} += $discount;
245 if ($subtotal_header) {
246 $discount_subtotal += $linetotal;
247 $nodiscount_subtotal += $nodiscount_linetotal;
250 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
254 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
256 $discount_subtotal = 0;
257 $nodiscount_subtotal = 0;
258 $subtotal_header = 0;
261 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
264 if (!$form->{"discount_$i"}) {
265 $nodiscount += $linetotal;
268 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
269 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
270 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
271 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
273 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
274 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
276 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
280 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
282 if ($form->{taxincluded}) {
285 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
286 $taxbase = $linetotal - $taxamount;
288 $taxamount = $linetotal * $taxrate;
289 $taxbase = $linetotal;
292 if ($form->round_amount($taxrate, 7) == 0) {
293 if ($form->{taxincluded}) {
294 foreach my $accno (@taxaccounts) {
295 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
297 $taxaccounts{$accno} += $taxamount;
298 $taxdiff += $taxamount;
300 $taxbase{$accno} += $taxbase;
302 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
304 foreach my $accno (@taxaccounts) {
305 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
306 $taxbase{$accno} += $taxbase;
310 foreach my $accno (@taxaccounts) {
311 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
312 $taxbase{$accno} += $taxbase;
315 my $tax_rate = $taxrate * 100;
316 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
317 if ($form->{"assembly_$i"}) {
320 # get parts and push them onto the stack
322 if ($form->{groupitems}) {
324 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
326 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
330 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
332 JOIN parts p ON (a.parts_id = p.id)
333 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
334 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
335 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
337 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
338 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
339 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
340 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
341 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
344 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
346 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
347 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
349 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
350 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
356 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
357 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
358 for @{ $ic_cvar_configs };
362 foreach my $item (sort keys %taxaccounts) {
363 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
365 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
366 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
367 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
368 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
369 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
371 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
375 for my $i (1 .. $form->{paidaccounts}) {
376 if ($form->{"paid_$i"}) {
377 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
381 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
382 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
383 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
385 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
388 if($form->{taxincluded}) {
389 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
390 $form->{subtotal_nofmt} = $form->{total} - $tax;
393 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
394 $form->{subtotal_nofmt} = $form->{total};
397 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
398 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
399 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
400 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
402 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
403 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
405 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
406 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
408 $form->set_payment_options($myconfig, $form->{invdate});
410 $form->{username} = $myconfig->{name};
412 $main::lxdebug->leave_sub();
415 sub project_description {
416 $main::lxdebug->enter_sub();
418 my ($self, $dbh, $id) = @_;
419 my $form = \%main::form;
421 my $query = qq|SELECT description FROM project WHERE id = ?|;
422 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
424 $main::lxdebug->leave_sub();
429 sub customer_details {
430 $main::lxdebug->enter_sub();
432 my ($self, $myconfig, $form, @wanted_vars) = @_;
434 # connect to database
435 my $dbh = $form->get_standard_dbh;
437 my $language_id = $form->{language_id};
439 # get contact id, set it if nessessary
442 my @values = (conv_i($form->{customer_id}));
445 if ($form->{cp_id}) {
446 $where = qq| AND (cp.cp_id = ?) |;
447 push(@values, conv_i($form->{cp_id}));
450 # get rest for the customer
452 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
453 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
456 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
457 WHERE (ct.id = ?) $where
460 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
462 # remove id and taxincluded before copy back
463 delete @$ref{qw(id taxincluded)};
465 @wanted_vars = grep({ $_ } @wanted_vars);
466 if (scalar(@wanted_vars) > 0) {
468 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
469 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
472 map { $form->{$_} = $ref->{$_} } keys %$ref;
474 # remove any trailing whitespace
475 $form->{currency} =~ s/\s*$// if ($form->{currency});
477 if ($form->{delivery_customer_id}) {
479 qq|SELECT *, notes as customernotes
483 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
485 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
488 if ($form->{delivery_vendor_id}) {
490 qq|SELECT *, notes as customernotes
494 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
496 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
499 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
501 'trans_id' => $form->{customer_id});
502 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
504 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
505 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
506 'language_id' => $language_id,
507 'allow_fallback' => 1);
510 $main::lxdebug->leave_sub();
514 $main::lxdebug->enter_sub();
516 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
518 # connect to database, turn off autocommit
519 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
521 my ($query, $sth, $null, $project_id, @values);
522 my $exchangerate = 0;
524 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
527 if (!$form->{employee_id}) {
528 $form->get_employee($dbh);
531 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
532 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
533 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
534 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
535 if (!$form->{department_id}){
536 ($null, $form->{department_id}) = split(/--/, $form->{department});
539 my $all_units = AM->retrieve_units($myconfig, $form);
541 if (!$payments_only) {
543 &reverse_invoice($dbh, $form);
546 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
547 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
549 $query = qq|SELECT nextval('glid')|;
550 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
552 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
553 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
555 if (!$form->{invnumber}) {
557 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
558 "cnnumber" : "invnumber", $dbh);
563 my ($netamount, $invoicediff) = (0, 0);
564 my ($amount, $linetotal, $lastincomeaccno);
566 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
567 my $defaultcurrency = (split m/:/, $currencies)[0];
569 if ($form->{currency} eq $defaultcurrency) {
570 $form->{exchangerate} = 1;
572 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
575 $form->{exchangerate} =
578 : $form->parse_amount($myconfig, $form->{exchangerate});
580 $form->{expense_inventory} = "";
584 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
585 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
588 $form->{amount} = {};
589 $form->{amount_cogs} = {};
591 foreach my $i (1 .. $form->{rowcount}) {
592 if ($form->{type} eq "credit_note") {
593 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
594 $form->{shipped} = 1;
596 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
601 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
602 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
603 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
605 if ($form->{storno}) {
606 $form->{"qty_$i"} *= -1;
609 if ($form->{"id_$i"}) {
612 if (defined($baseunits{$form->{"id_$i"}})) {
613 $item_unit = $baseunits{$form->{"id_$i"}};
616 $query = qq|SELECT unit FROM parts WHERE id = ?|;
617 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
618 $baseunits{$form->{"id_$i"}} = $item_unit;
621 if (defined($all_units->{$item_unit}->{factor})
622 && ($all_units->{$item_unit}->{factor} ne '')
623 && ($all_units->{$item_unit}->{factor} != 0)) {
624 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
628 $baseqty = $form->{"qty_$i"} * $basefactor;
630 my ($allocated, $taxrate) = (0, 0);
634 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
636 # keep entered selling price
638 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
640 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
642 my $decimalplaces = ($dec > 2) ? $dec : 2;
644 # undo discount formatting
645 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
648 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
650 # round linetotal to 2 decimal places
651 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
652 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
654 if ($form->{taxincluded}) {
655 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
656 $form->{"sellprice_$i"} =
657 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
659 $taxamount = $linetotal * $taxrate;
662 $netamount += $linetotal;
664 if ($taxamount != 0) {
666 $form->{amount}{ $form->{id} }{$_} +=
667 $taxamount * $form->{"${_}_rate"} / $taxrate
668 } split(/ /, $form->{"taxaccounts_$i"});
671 # add amount to income, $form->{amount}{trans_id}{accno}
672 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
674 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
675 $linetotal = $form->round_amount($linetotal, 2);
677 # this is the difference from the inventory
678 $invoicediff += ($amount - $linetotal);
680 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
683 $lastincomeaccno = $form->{"income_accno_$i"};
685 # adjust and round sellprice
686 $form->{"sellprice_$i"} =
687 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
690 next if $payments_only;
692 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
694 if ($form->{"assembly_$i"}) {
695 # record assembly item as allocated
696 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
699 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
703 # get pricegroup_id and save it
704 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
707 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
709 # save detail record in invoice table
711 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
712 sellprice, fxsellprice, discount, allocated, assemblyitem,
713 unit, deliverydate, project_id, serialnumber, pricegroup_id,
714 ordnumber, transdate, cusordnumber, base_qty, subtotal,
715 marge_percent, marge_total, lastcost,
716 price_factor_id, price_factor, marge_price_factor)
717 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
718 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
720 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
721 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
722 $form->{"sellprice_$i"}, $fxsellprice,
723 $form->{"discount_$i"}, $allocated, 'f',
724 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
725 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
726 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
727 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
728 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
729 $form->{"lastcost_$i"},
730 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
731 conv_i($form->{"marge_price_factor_$i"}));
732 do_query($form, $dbh, $query, @values);
734 CVar->save_custom_variables(module => 'IC',
735 sub_module => 'invoice',
736 trans_id => $invoice_id,
737 configs => $ic_cvar_configs,
739 name_prefix => 'ic_',
740 name_postfix => "_$i",
745 # total payments, don't move we need it here
746 for my $i (1 .. $form->{paidaccounts}) {
747 if ($form->{type} eq "credit_note") {
748 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
750 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
752 $form->{paid} += $form->{"paid_$i"};
753 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
756 my ($tax, $diff) = (0, 0);
758 $netamount = $form->round_amount($netamount, 2);
760 # figure out rounding errors for total amount vs netamount + taxes
761 if ($form->{taxincluded}) {
763 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
764 $diff += $amount - $netamount * $form->{exchangerate};
765 $netamount = $amount;
767 foreach my $item (split(/ /, $form->{taxaccounts})) {
768 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
769 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
770 $tax += $form->{amount}{ $form->{id} }{$item};
771 $netamount -= $form->{amount}{ $form->{id} }{$item};
774 $invoicediff += $diff;
775 ######## this only applies to tax included
776 if ($lastincomeaccno) {
777 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
781 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
782 $diff = $amount - $netamount * $form->{exchangerate};
783 $netamount = $amount;
784 foreach my $item (split(/ /, $form->{taxaccounts})) {
785 $form->{amount}{ $form->{id} }{$item} =
786 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
789 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
792 $amount - $form->{amount}{ $form->{id} }{$item} *
793 $form->{exchangerate};
794 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
795 $tax += $form->{amount}{ $form->{id} }{$item};
799 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
801 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
804 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
806 # update exchangerate
807 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
808 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
809 $form->{exchangerate}, 0);
812 $project_id = conv_i($form->{"globalproject_id"});
814 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
815 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
816 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
818 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
820 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
822 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
823 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
824 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
825 do_query($form, $dbh, $query, @values);
826 $form->{amount_cogs}{$trans_id}{$accno} = 0;
830 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
831 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
833 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
838 do_query($form, $dbh, $query, @values);
843 foreach my $trans_id (keys %{ $form->{amount} }) {
844 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
845 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
847 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
849 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
851 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
852 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
853 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
854 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
855 do_query($form, $dbh, $query, @values);
856 $form->{amount}{$trans_id}{$accno} = 0;
860 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
861 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
863 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
865 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
866 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
867 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
868 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
869 do_query($form, $dbh, $query, @values);
874 # deduct payment differences from diff
875 for my $i (1 .. $form->{paidaccounts}) {
876 if ($form->{"paid_$i"} != 0) {
878 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
879 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
883 # record payments and offsetting AR
884 if (!$form->{storno}) {
885 for my $i (1 .. $form->{paidaccounts}) {
887 if ($form->{"acc_trans_id_$i"}
889 && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
893 next if ($form->{"paid_$i"} == 0);
895 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
896 $form->{"datepaid_$i"} = $form->{invdate}
897 unless ($form->{"datepaid_$i"});
898 $form->{datepaid} = $form->{"datepaid_$i"};
902 if ($form->{currency} eq $defaultcurrency) {
903 $form->{"exchangerate_$i"} = 1;
905 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
906 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
910 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
912 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
914 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
915 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
916 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
917 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
918 do_query($form, $dbh, $query, @values);
922 $form->{"paid_$i"} *= -1;
923 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
926 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
927 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
928 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
929 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
930 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
931 do_query($form, $dbh, $query, @values);
933 # exchangerate difference
934 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
935 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
939 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
940 $form->{"exchangerate_$i"};
942 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
944 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
949 # update exchange rate
950 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
951 $form->update_exchangerate($dbh, $form->{currency},
952 $form->{"datepaid_$i"},
953 $form->{"exchangerate_$i"}, 0);
957 } else { # if (!$form->{storno})
958 $form->{marge_total} *= -1;
961 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
963 # record exchange rate differences and gains/losses
964 foreach my $accno (keys %{ $form->{fx} }) {
965 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
966 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
967 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
970 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
971 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
972 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
973 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_i($project_id));
974 do_query($form, $dbh, $query, @values);
979 if ($payments_only) {
980 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
981 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
983 $dbh->commit if !$provided_dbh;
985 $main::lxdebug->leave_sub();
989 $amount = $netamount + $tax;
992 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
994 $query = qq|UPDATE ar set
995 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
996 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
997 amount = ?, netamount = ?, paid = ?,
998 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
999 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1000 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1001 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1002 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1003 cp_id = ?, marge_total = ?, marge_percent = ?,
1004 globalproject_id = ?, delivery_customer_id = ?,
1005 transaction_description = ?, delivery_vendor_id = ?,
1006 donumber = ?, invnumber_for_credit_note = ?
1008 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1009 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1010 $amount, $netamount, $form->{"paid"},
1011 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1012 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1013 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1014 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1015 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1016 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1017 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1018 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1019 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1020 conv_i($form->{"id"}));
1021 do_query($form, $dbh, $query, @values);
1024 if ($form->{storno}) {
1027 paid = paid + amount,
1029 intnotes = ? || intnotes
1031 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1032 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1036 $form->{name} = $form->{customer};
1037 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1039 if (!$form->{shipto_id}) {
1040 $form->add_shipto($dbh, $form->{id}, "AR");
1043 # save printed, emailed and queued
1044 $form->save_status($dbh);
1046 Common::webdav_folder($form);
1048 # Link this record to the records it was created from.
1049 RecordLinks->create_links('dbh' => $dbh,
1051 'from_table' => 'oe',
1052 'from_ids' => $form->{convert_from_oe_ids},
1054 'to_id' => $form->{id},
1056 delete $form->{convert_from_oe_ids};
1058 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1060 if (scalar @convert_from_do_ids) {
1061 DO->close_orders('dbh' => $dbh,
1062 'ids' => \@convert_from_do_ids);
1064 RecordLinks->create_links('dbh' => $dbh,
1066 'from_table' => 'delivery_orders',
1067 'from_ids' => \@convert_from_do_ids,
1069 'to_id' => $form->{id},
1072 delete $form->{convert_from_do_ids};
1074 ARAP->close_orders_if_billed('dbh' => $dbh,
1075 'arap_id' => $form->{id},
1078 # safety check datev export
1079 if ($::lx_office_conf{datev_check}{check_on_sales_invoice}) {
1080 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1081 $transdate ||= DateTime->today;
1083 my $datev = SL::DATEV->new(
1084 exporttype => DATEV_ET_BUCHUNGEN,
1085 format => DATEV_FORMAT_KNE,
1093 if ($datev->errors) {
1095 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1100 $dbh->commit if !$provided_dbh;
1102 $main::lxdebug->leave_sub();
1107 sub _delete_payments {
1108 $main::lxdebug->enter_sub();
1110 my ($self, $form, $dbh) = @_;
1112 my @delete_acc_trans_ids;
1114 # Delete old payment entries from acc_trans.
1116 qq|SELECT acc_trans_id
1118 WHERE (trans_id = ?) AND fx_transaction
1122 SELECT at.acc_trans_id
1124 LEFT JOIN chart c ON (at.chart_id = c.id)
1125 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1126 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1129 qq|SELECT at.acc_trans_id
1131 LEFT JOIN chart c ON (at.chart_id = c.id)
1132 WHERE (trans_id = ?)
1133 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1134 ORDER BY at.acc_trans_id
1136 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1138 if (@delete_acc_trans_ids) {
1139 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1140 do_query($form, $dbh, $query);
1143 $main::lxdebug->leave_sub();
1147 $main::lxdebug->enter_sub();
1149 my ($self, $myconfig, $form, $locale) = @_;
1151 # connect to database, turn off autocommit
1152 my $dbh = $form->get_standard_dbh;
1155 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1157 $old_form = save_form();
1159 # Delete all entries in acc_trans from prior payments.
1160 if ($::lx_office_conf{features}->{payments_changeable} != 0) {
1161 $self->_delete_payments($form, $dbh);
1164 # Save the new payments the user made before cleaning up $form.
1165 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 };
1167 # Clean up $form so that old content won't tamper the results.
1168 %keep_vars = map { $_, 1 } qw(login password id);
1169 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1171 # Retrieve the invoice from the database.
1172 $self->retrieve_invoice($myconfig, $form);
1174 # Set up the content of $form in the way that IS::post_invoice() expects.
1175 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1177 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1178 $item = $form->{invoice_details}->[$row - 1];
1180 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1182 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1185 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1187 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1189 # Restore the payment options from the user input.
1190 map { $form->{$_} = $payments{$_} } keys %payments;
1192 # Get the AR accno (which is normally done by Form::create_links()).
1196 LEFT JOIN chart c ON (at.chart_id = c.id)
1197 WHERE (trans_id = ?)
1198 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1199 ORDER BY at.acc_trans_id
1202 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1204 # Post the new payments.
1205 $self->post_invoice($myconfig, $form, $dbh, 1);
1207 restore_form($old_form);
1209 my $rc = $dbh->commit();
1211 $main::lxdebug->leave_sub();
1216 sub process_assembly {
1217 $main::lxdebug->enter_sub();
1219 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1222 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1223 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1225 JOIN parts p ON (a.parts_id = p.id)
1227 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1229 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1233 $ref->{inventory_accno_id} *= 1;
1234 $ref->{expense_accno_id} *= 1;
1236 # multiply by number of assemblies
1237 $ref->{qty} *= $totalqty;
1239 if ($ref->{assembly}) {
1240 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1243 if ($ref->{inventory_accno_id}) {
1244 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1248 # save detail record for individual assembly item in invoice table
1250 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1251 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1252 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1253 do_query($form, $dbh, $query, @values);
1259 $main::lxdebug->leave_sub();
1263 $main::lxdebug->enter_sub();
1265 # adjust allocated in table invoice according to FIFO princicple
1266 # for a certain part with part_id $id
1268 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1272 $form->{taxzone_id} *=1;
1273 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1274 my $taxzone_id = $form->{"taxzone_id"} * 1;
1276 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1277 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1278 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1279 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1280 FROM invoice i, parts p
1281 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1282 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1283 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1284 WHERE (i.parts_id = p.id)
1285 AND (i.parts_id = ?)
1286 AND ((i.base_qty + i.allocated) < 0)
1288 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1293 # all invoice entries of an example part:
1295 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1296 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1297 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1298 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1299 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1300 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1301 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1303 # 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
1304 # and all parts have been allocated
1306 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1307 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1309 # in this example there are still 4 unsold articles
1312 # search all invoice entries for the part in question, adjusting "allocated"
1313 # until the total number of sold parts has been reached
1315 # ORDER BY trans_id ensures FIFO
1318 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1319 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1323 # update allocated in invoice
1324 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1326 # total expenses and inventory
1327 # sellprice is the cost of the item
1328 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1330 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1331 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1332 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1334 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1335 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1336 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1338 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1339 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1345 last if (($totalqty -= $qty) <= 0);
1350 $main::lxdebug->leave_sub();
1355 sub reverse_invoice {
1356 $main::lxdebug->enter_sub();
1358 my ($dbh, $form) = @_;
1360 # reverse inventory items
1362 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1364 JOIN parts p ON (i.parts_id = p.id)
1365 WHERE i.trans_id = ?|;
1366 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1368 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1370 if ($ref->{inventory_accno_id}) {
1371 # de-allocated purchases
1373 qq|SELECT i.id, i.trans_id, i.allocated
1375 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1376 ORDER BY i.trans_id DESC|;
1377 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1379 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1380 my $qty = $ref->{qty};
1381 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1382 $qty = $inhref->{allocated};
1386 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1388 last if (($ref->{qty} -= $qty) <= 0);
1397 my @values = (conv_i($form->{id}));
1398 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1399 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1400 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1402 $main::lxdebug->leave_sub();
1405 sub delete_invoice {
1406 $main::lxdebug->enter_sub();
1408 my ($self, $myconfig, $form) = @_;
1410 # connect to database
1411 my $dbh = $form->get_standard_dbh;
1414 &reverse_invoice($dbh, $form);
1416 my @values = (conv_i($form->{id}));
1418 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1419 # zurückgesetzt werden. Vgl:
1420 # id | storno | storno_id | paid | amount
1421 #----+--------+-----------+---------+-----------
1422 # 18 | f | | 0.00000 | 119.00000
1424 # 18 | t | | 119.00000 | 119.00000
1426 if($form->{storno}){
1427 # storno_id auslesen und korrigieren
1428 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1429 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1432 # delete spool files
1433 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1436 qq|DELETE FROM status WHERE trans_id = ?|,
1437 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1438 qq|DELETE FROM ar WHERE id = ?|,
1441 map { do_query($form, $dbh, $_, @values) } @queries;
1443 my $rc = $dbh->commit;
1446 my $spool = $::lx_office_conf{paths}->{spool};
1447 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1450 $main::lxdebug->leave_sub();
1455 sub retrieve_invoice {
1456 $main::lxdebug->enter_sub();
1458 my ($self, $myconfig, $form) = @_;
1460 # connect to database
1461 my $dbh = $form->get_standard_dbh;
1463 my ($sth, $ref, $query);
1465 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1469 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1470 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1471 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1472 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1473 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1474 d.curr AS currencies
1478 $ref = selectfirst_hashref_query($form, $dbh, $query);
1479 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1482 my $id = conv_i($form->{id});
1485 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1489 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1490 a.orddate, a.quodate, a.globalproject_id,
1491 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1492 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1493 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1494 a.employee_id, a.salesman_id, a.payment_id,
1495 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1496 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1497 a.marge_total, a.marge_percent,
1500 LEFT JOIN employee e ON (e.id = a.employee_id)
1502 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1503 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1505 # remove any trailing whitespace
1506 $form->{currency} =~ s/\s*$//;
1508 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1511 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1512 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1514 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1516 foreach my $vc (qw(customer vendor)) {
1517 next if !$form->{"delivery_${vc}_id"};
1518 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1521 # get printed, emailed
1522 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1523 $sth = prepare_execute_query($form, $dbh, $query, $id);
1525 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1526 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1527 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1528 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1531 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1533 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1534 : $form->{invdate} ? $dbh->quote($form->{invdate})
1538 my $taxzone_id = $form->{taxzone_id} *= 1;
1539 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1541 # retrieve individual items
1544 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1545 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1546 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1549 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1550 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1551 i.price_factor_id, i.price_factor, i.marge_price_factor,
1552 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1553 pr.projectnumber, pg.partsgroup, prg.pricegroup
1556 LEFT JOIN parts p ON (i.parts_id = p.id)
1557 LEFT JOIN project pr ON (i.project_id = pr.id)
1558 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1559 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1561 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1562 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1563 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1565 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1567 $sth = prepare_execute_query($form, $dbh, $query, $id);
1569 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1570 # Retrieve custom variables.
1571 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1573 sub_module => 'invoice',
1574 trans_id => $ref->{invoice_id},
1576 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1577 delete $ref->{invoice_id};
1579 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1580 delete($ref->{"part_inventory_accno_id"});
1582 foreach my $type (qw(inventory income expense)) {
1583 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1584 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1585 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1589 # get tax rates and description
1590 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1592 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1593 LEFT JOIN chart c ON (c.id = t.chart_id)
1595 (SELECT tk.tax_id FROM taxkeys tk
1596 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1597 AND startdate <= date($transdate)
1598 ORDER BY startdate DESC LIMIT 1)
1600 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1601 $ref->{taxaccounts} = "";
1603 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1605 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1609 $ref->{taxaccounts} .= "$ptr->{accno} ";
1611 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1612 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1613 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1614 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1615 $form->{taxaccounts} .= "$ptr->{accno} ";
1620 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1622 chop $ref->{taxaccounts};
1623 push @{ $form->{invoice_details} }, $ref;
1628 Common::webdav_folder($form);
1631 my $rc = $dbh->commit;
1633 $main::lxdebug->leave_sub();
1639 $main::lxdebug->enter_sub();
1641 my ($self, $myconfig, $form) = @_;
1643 # connect to database
1644 my $dbh = $form->get_standard_dbh;
1646 my $dateformat = $myconfig->{dateformat};
1647 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1649 my (@values, $duedate, $ref, $query);
1651 if ($form->{invdate}) {
1652 $duedate = "to_date(?, '$dateformat')";
1653 push @values, $form->{invdate};
1655 $duedate = "current_date";
1658 my $cid = conv_i($form->{customer_id});
1661 if ($form->{payment_id}) {
1662 $payment_id = "(pt.id = ?) OR";
1663 push @values, conv_i($form->{payment_id});
1669 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1670 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1671 c.street, c.zipcode, c.city, c.country,
1672 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1673 c.taxincluded_checked,
1674 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1675 b.discount AS tradediscount, b.description AS business
1677 LEFT JOIN business b ON (b.id = c.business_id)
1678 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1681 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1683 delete $ref->{salesman_id} if !$ref->{salesman_id};
1685 map { $form->{$_} = $ref->{$_} } keys %$ref;
1687 # remove any trailing whitespace
1688 $form->{curr} =~ s/\s*$//;
1690 # use customer currency if not empty
1691 $form->{currency} = $form->{curr} if $form->{curr};
1694 qq|SELECT sum(amount - paid) AS dunning_amount
1696 WHERE (paid < amount)
1697 AND (customer_id = ?)
1698 AND (dunning_config_id IS NOT NULL)|;
1699 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1700 map { $form->{$_} = $ref->{$_} } keys %$ref;
1703 qq|SELECT dnn.dunning_description AS max_dunning_level
1704 FROM dunning_config dnn
1705 WHERE id IN (SELECT dunning_config_id
1707 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1708 ORDER BY dunning_level DESC LIMIT 1|;
1709 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1710 map { $form->{$_} = $ref->{$_} } keys %$ref;
1712 $form->{creditremaining} = $form->{creditlimit};
1713 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1714 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1715 $form->{creditremaining} -= $value;
1719 (SELECT e.buy FROM exchangerate e
1720 WHERE e.curr = o.curr
1721 AND e.transdate = o.transdate)
1723 WHERE o.customer_id = ?
1724 AND o.quotation = '0'
1725 AND o.closed = '0'|;
1726 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1728 while (my ($amount, $exch) = $sth->fetchrow_array) {
1729 $exch = 1 unless $exch;
1730 $form->{creditremaining} -= $amount * $exch;
1734 # get shipto if we did not converted an order or invoice
1735 if (!$form->{shipto}) {
1736 map { delete $form->{$_} }
1737 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1738 shiptostreet shiptozipcode shiptocity shiptocountry
1739 shiptocontact shiptophone shiptofax shiptoemail);
1741 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1742 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1744 map { $form->{$_} = $ref->{$_} } keys %$ref;
1747 # setup last accounts used for this customer
1748 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1750 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1752 JOIN acc_trans ac ON (ac.chart_id = c.id)
1753 JOIN ar a ON (a.id = ac.trans_id)
1754 WHERE a.customer_id = ?
1755 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1756 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1757 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1760 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1761 if ($ref->{category} eq 'I') {
1763 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1765 if ($form->{initial_transdate}) {
1767 qq|SELECT tk.tax_id, t.rate
1769 LEFT JOIN tax t ON tk.tax_id = t.id
1770 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1771 ORDER BY tk.startdate DESC
1773 my ($tax_id, $rate) =
1774 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1775 $form->{initial_transdate});
1776 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1779 if ($ref->{category} eq 'A') {
1780 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1784 $form->{rowcount} = $i if ($i && !$form->{type});
1787 $main::lxdebug->leave_sub();
1791 $main::lxdebug->enter_sub();
1793 my ($self, $myconfig, $form) = @_;
1795 # connect to database
1796 my $dbh = $form->get_standard_dbh;
1798 my $i = $form->{rowcount};
1800 my $where = qq|NOT p.obsolete = '1'|;
1803 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1804 my ($table, $field) = split m/\./, $column;
1805 next if !$form->{"${field}_${i}"};
1806 $where .= qq| AND lower(${column}) ILIKE ?|;
1807 push @values, '%' . $form->{"${field}_${i}"} . '%';
1810 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1811 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1812 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1813 push @values, $form->{"partnumber_$i"};
1816 # Search for part ID overrides all other criteria.
1817 if ($form->{"id_${i}"}) {
1818 $where = qq|p.id = ?|;
1819 @values = ($form->{"id_${i}"});
1822 if ($form->{"description_$i"}) {
1823 $where .= qq| ORDER BY p.description|;
1825 $where .= qq| ORDER BY p.partnumber|;
1829 if ($form->{type} eq "invoice") {
1831 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1832 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1836 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1840 my $taxzone_id = $form->{taxzone_id} * 1;
1841 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1845 p.id, p.partnumber, p.description, p.sellprice,
1846 p.listprice, p.inventory_accno_id, p.lastcost,
1848 c1.accno AS inventory_accno,
1849 c1.new_chart_id AS inventory_new_chart,
1850 date($transdate) - c1.valid_from AS inventory_valid,
1852 c2.accno AS income_accno,
1853 c2.new_chart_id AS income_new_chart,
1854 date($transdate) - c2.valid_from AS income_valid,
1856 c3.accno AS expense_accno,
1857 c3.new_chart_id AS expense_new_chart,
1858 date($transdate) - c3.valid_from AS expense_valid,
1860 p.unit, p.assembly, p.bin, p.onhand,
1861 p.notes AS partnotes, p.notes AS longdescription,
1862 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1865 pfac.factor AS price_factor,
1870 LEFT JOIN chart c1 ON
1871 ((SELECT inventory_accno_id
1872 FROM buchungsgruppen
1873 WHERE id = p.buchungsgruppen_id) = c1.id)
1874 LEFT JOIN chart c2 ON
1875 ((SELECT income_accno_id_${taxzone_id}
1876 FROM buchungsgruppen
1877 WHERE id = p.buchungsgruppen_id) = c2.id)
1878 LEFT JOIN chart c3 ON
1879 ((SELECT expense_accno_id_${taxzone_id}
1880 FROM buchungsgruppen
1881 WHERE id = p.buchungsgruppen_id) = c3.id)
1882 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1883 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1885 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1887 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1889 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1890 [ qq|SELECT tr.translation, tr.longdescription
1892 WHERE tr.language_id IN
1895 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1898 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1900 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1902 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1903 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1904 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1905 if (!$ref->{inventory_accno_id}) {
1906 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1908 delete($ref->{inventory_accno_id});
1910 foreach my $type (qw(inventory income expense)) {
1911 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1913 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1916 ($ref->{"${type}_accno"},
1917 $ref->{"${type}_new_chart"},
1918 $ref->{"${type}_valid"})
1919 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1923 if ($form->{payment_id} eq "") {
1924 $form->{payment_id} = $form->{part_payment_id};
1927 # get tax rates and description
1928 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1930 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1932 LEFT JOIN chart c ON (c.id = t.chart_id)
1936 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1938 ORDER BY startdate DESC
1941 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1942 my $stw = $dbh->prepare($query);
1943 $stw->execute(@values) || $form->dberror($query);
1945 $ref->{taxaccounts} = "";
1947 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1949 # if ($customertax{$ref->{accno}})
1950 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1954 $ref->{taxaccounts} .= "$ptr->{accno} ";
1956 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1957 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1958 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1959 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1960 $form->{taxaccounts} .= "$ptr->{accno} ";
1966 chop $ref->{taxaccounts};
1968 if ($form->{language_id}) {
1969 for my $spec (@translation_queries) {
1970 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1971 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1972 next unless $translation;
1973 $ref->{description} = $translation;
1974 $ref->{longdescription} = $longdescription;
1979 $ref->{onhand} *= 1;
1981 push @{ $form->{item_list} }, $ref;
1984 $_->[1]->finish for @translation_queries;
1986 foreach my $item (@{ $form->{item_list} }) {
1987 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1988 trans_id => $item->{id},
1992 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1995 $main::lxdebug->leave_sub();
1998 ##########################
1999 # get pricegroups from database
2000 # build up selected pricegroup
2001 # if an exchange rate - change price
2004 sub get_pricegroups_for_parts {
2006 $main::lxdebug->enter_sub();
2008 my ($self, $myconfig, $form) = @_;
2010 my $dbh = $form->get_standard_dbh;
2012 $form->{"PRICES"} = {};
2016 my $all_units = AM->retrieve_units($myconfig, $form);
2017 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2018 $form->{"PRICES"}{$i} = [];
2020 $id = $form->{"id_$i"};
2022 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2023 $id = $form->{"new_id_$i"};
2026 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2028 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2030 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2031 my $sellprice = $form->{"sellprice_$i"};
2032 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2033 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2034 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2036 my $price_new = $form->{"price_new_$i"};
2037 my $price_old = $form->{"price_old_$i"};
2039 if (!$form->{"unit_old_$i"}) {
2040 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2041 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2042 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2043 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2046 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2047 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2048 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2050 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2051 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2052 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2053 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2054 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2055 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2056 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2061 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2062 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2063 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2064 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2065 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2069 if (!$form->{"basefactor_$i"}) {
2070 $form->{"basefactor_$i"} = 1;
2076 sellprice AS default_sellprice,
2079 'selected' AS selected
2085 parts.sellprice AS default_sellprice,
2086 pricegroup.pricegroup,
2090 LEFT JOIN parts ON parts.id = parts_id
2091 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2093 ORDER BY pricegroup|;
2094 my @values = (conv_i($id), conv_i($id));
2095 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2097 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2099 $pkr->{selected} = '';
2101 # if there is an exchange rate change price
2102 if (($form->{exchangerate} * 1) != 0) {
2103 $pkr->{price} /= $form->{exchangerate};
2106 $pkr->{price} *= $form->{"basefactor_$i"};
2107 $pkr->{price} *= $basefactor;
2108 $pkr->{price_ufmt} = $pkr->{price};
2109 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2111 if (!defined $selectedpricegroup_id) {
2112 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2113 # Case A: open old invoice, no pricegroup selected
2114 # Case B: add new article to invoice, no pricegroup selected
2116 # to distinguish case A and B the variable pricegroup_id_$i is used
2117 # for new articles this variable isn't defined, for loaded articles it is
2118 # sellprice can't be used, as it already has 0,00 set
2120 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2122 $pkr->{selected} = ' selected';
2123 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2124 and not defined $form->{"pricegroup_id_$i"}
2125 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2126 # for the case where pricegroup prices haven't been set
2128 # Case B: use default pricegroup of customer
2130 $pkr->{selected} = ' selected'; # unless $form->{selected};
2131 # no customer pricesgroup set
2132 if ($pkr->{price_unfmt} == $pkr->{default_sellprice}) {
2134 $pkr->{price} = $form->{"sellprice_$i"};
2138 # this sub should not set anything and only return. --sschoeling, 20090506
2139 # is this correct? put in again... -- grichardson 20110119
2140 $form->{"sellprice_$i"} = $pkr->{price};
2143 } elsif ($pkr->{price_unfmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2144 $pkr->{price} = $form->{"sellprice_$i"};
2145 $pkr->{selected} = ' selected';
2149 # existing article: pricegroup or price changed
2150 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2151 if ($selectedpricegroup_id ne $pricegroup_old) {
2152 # pricegroup has changed
2153 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2154 $pkr->{selected} = ' selected';
2156 } elsif ( ($form->parse_amount($myconfig, $price_new)
2157 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2158 and ($price_new ne 0) and defined $price_new) {
2159 # sellprice has changed
2160 # when loading existing invoices $price_new is NULL
2161 if ($pkr->{pricegroup_id} == 0) {
2162 $pkr->{price} = $form->{"sellprice_$i"};
2163 $pkr->{selected} = ' selected';
2165 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2166 # neither sellprice nor pricegroup changed
2167 $pkr->{selected} = ' selected';
2168 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2169 # $pkr->{price} = $form->{"sellprice_$i"};
2171 $pkr->{price} = $form->{"sellprice_$i"};
2175 push @{ $form->{PRICES}{$i} }, $pkr;
2178 $form->{"basefactor_$i"} *= $basefactor;
2185 $main::lxdebug->leave_sub();
2189 $main::lxdebug->enter_sub();
2191 my ($self, $myconfig, $form, $table) = @_;
2193 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2195 # make sure there's no funny stuff in $table
2196 # ToDO: die when this happens and throw an error
2197 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2199 my $dbh = $form->get_standard_dbh;
2201 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2202 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2204 $main::lxdebug->leave_sub();
2210 $main::lxdebug->enter_sub();
2212 my ($self, $myconfig, $form, $table, $id) = @_;
2214 $main::lxdebug->leave_sub() and return 0 unless ($id);
2216 # make sure there's no funny stuff in $table
2217 # ToDO: die when this happens and throw an error
2218 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2220 my $dbh = $form->get_standard_dbh;
2222 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2223 my ($result) = selectrow_query($form, $dbh, $query, $id);
2225 $main::lxdebug->leave_sub();
2230 sub get_standard_accno_current_assets {
2231 $main::lxdebug->enter_sub();
2233 my ($self, $myconfig, $form) = @_;
2235 my $dbh = $form->get_standard_dbh;
2237 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2238 my ($result) = selectrow_query($form, $dbh, $query);
2240 $main::lxdebug->leave_sub();