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);
45 use SL::GenericTranslations;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form, $locale) = @_;
59 $form->{duedate} ||= $form->{invdate};
62 my $dbh = $form->get_standard_dbh;
65 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
66 ($form->{terms}) = selectrow_query($form, $dbh, $query);
68 my (@project_ids, %projectnumbers, %projectdescriptions);
69 $form->{TEMPLATE_ARRAYS} = {};
71 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
73 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
76 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
77 $price_factors{$pfac->{id}} = $pfac;
79 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
82 # sort items by partsgroup
83 for my $i (1 .. $form->{rowcount}) {
85 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
86 # $partsgroup = $form->{"partsgroup_$i"};
88 # push @partsgroup, [$i, $partsgroup];
89 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
93 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
94 join(", ", map({ "?" } @project_ids)) . ")";
95 $sth = $dbh->prepare($query);
96 $sth->execute(@project_ids) ||
97 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
98 while (my $ref = $sth->fetchrow_hashref()) {
99 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $projectdescriptions{$ref->{id}} = $ref->{description};
105 $form->{"globalprojectnumber"} =
106 $projectnumbers{$form->{"globalproject_id"}};
107 $form->{"globalprojectdescription"} =
108 $projectdescriptions{$form->{"globalproject_id"}};
115 my %oid = ('Pg' => 'oid',
116 'Oracle' => 'rowid');
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();
147 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
150 qw(runningnumber number description longdescription qty ship unit bin
151 deliverydate_oe ordnumber_oe transdate_oe validuntil
152 partnotes serialnumber reqdate sellprice listprice netprice
153 discount p_discount discount_sub nodiscount_sub
154 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
155 price_factor price_factor_name partsgroup);
157 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
159 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
161 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
163 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
165 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
168 if ($item->[1] ne $sameitem) {
169 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
170 $sameitem = $item->[1];
172 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
175 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
177 if ($form->{"id_$i"} != 0) {
179 # add number, description and qty to $form->{number},
180 if ($form->{"subtotal_$i"} && !$subtotal_header) {
181 $subtotal_header = $i;
182 $position = int($position);
185 } elsif ($subtotal_header) {
187 $position = int($position);
188 $position = $position.".".$subposition;
190 $position = int($position);
194 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
196 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
197 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
204 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
209 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
214 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
215 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
216 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
217 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
219 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
220 my ($dec) = ($sellprice =~ /\.(\d+)/);
221 my $decimalplaces = max 2, length($dec);
223 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
224 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
225 my $linetotal = $form->round_amount($linetotal_exact, 2);
226 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
228 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
229 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
231 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
232 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
234 $linetotal = ($linetotal != 0) ? $linetotal : '';
236 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
237 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
240 $form->{total} += $linetotal;
241 $form->{nodiscount_total} += $nodiscount_linetotal;
242 $form->{discount_total} += $discount;
244 if ($subtotal_header) {
245 $discount_subtotal += $linetotal;
246 $nodiscount_subtotal += $nodiscount_linetotal;
249 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
250 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
252 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
255 $discount_subtotal = 0;
256 $nodiscount_subtotal = 0;
257 $subtotal_header = 0;
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
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 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
360 foreach my $item (sort keys %taxaccounts) {
361 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
363 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
364 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
365 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
366 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
368 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
369 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
373 for my $i (1 .. $form->{paidaccounts}) {
374 if ($form->{"paid_$i"}) {
375 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
377 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
378 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
379 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
383 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
386 if($form->{taxincluded}) {
387 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
388 $form->{subtotal_nofmt} = $form->{total} - $tax;
391 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
392 $form->{subtotal_nofmt} = $form->{total};
395 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
396 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
397 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
398 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
400 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
401 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
403 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
404 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
406 $form->set_payment_options($myconfig, $form->{invdate});
408 $form->{username} = $myconfig->{name};
410 $main::lxdebug->leave_sub();
413 sub project_description {
414 $main::lxdebug->enter_sub();
416 my ($self, $dbh, $id) = @_;
417 my $form = \%main::form;
419 my $query = qq|SELECT description FROM project WHERE id = ?|;
420 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
422 $main::lxdebug->leave_sub();
427 sub customer_details {
428 $main::lxdebug->enter_sub();
430 my ($self, $myconfig, $form, @wanted_vars) = @_;
432 # connect to database
433 my $dbh = $form->get_standard_dbh;
435 my $language_id = $form->{language_id};
437 # get contact id, set it if nessessary
440 my @values = (conv_i($form->{customer_id}));
443 if ($form->{cp_id}) {
444 $where = qq| AND (cp.cp_id = ?) |;
445 push(@values, conv_i($form->{cp_id}));
448 # get rest for the customer
450 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
451 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
453 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
454 WHERE (ct.id = ?) $where
457 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
459 # remove id and taxincluded before copy back
460 delete @$ref{qw(id taxincluded)};
462 @wanted_vars = grep({ $_ } @wanted_vars);
463 if (scalar(@wanted_vars) > 0) {
465 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
466 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
469 map { $form->{$_} = $ref->{$_} } keys %$ref;
471 if ($form->{delivery_customer_id}) {
473 qq|SELECT *, notes as customernotes
477 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
479 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
482 if ($form->{delivery_vendor_id}) {
484 qq|SELECT *, notes as customernotes
488 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
490 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
493 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
495 'trans_id' => $form->{customer_id});
496 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
498 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
499 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
500 'language_id' => $language_id,
501 'allow_fallback' => 1);
504 $main::lxdebug->leave_sub();
508 $main::lxdebug->enter_sub();
510 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
512 # connect to database, turn off autocommit
513 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
515 my ($query, $sth, $null, $project_id, @values);
516 my $exchangerate = 0;
518 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
521 if (!$form->{employee_id}) {
522 $form->get_employee($dbh);
525 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
526 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
527 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
528 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
529 if (!$form->{department_id}){
530 ($null, $form->{department_id}) = split(/--/, $form->{department});
533 my $all_units = AM->retrieve_units($myconfig, $form);
535 if (!$payments_only) {
537 &reverse_invoice($dbh, $form);
540 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
541 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
543 $query = qq|SELECT nextval('glid')|;
544 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
546 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
547 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
549 if (!$form->{invnumber}) {
551 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
552 "cnnumber" : "invnumber", $dbh);
557 my ($netamount, $invoicediff) = (0, 0);
558 my ($amount, $linetotal, $lastincomeaccno);
560 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
561 my $defaultcurrency = (split m/:/, $currencies)[0];
563 if ($form->{currency} eq $defaultcurrency) {
564 $form->{exchangerate} = 1;
566 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
569 $form->{exchangerate} =
572 : $form->parse_amount($myconfig, $form->{exchangerate});
574 $form->{expense_inventory} = "";
578 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
579 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
582 $form->{amount} = {};
583 $form->{amount_cogs} = {};
585 foreach my $i (1 .. $form->{rowcount}) {
586 if ($form->{type} eq "credit_note") {
587 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
588 $form->{shipped} = 1;
590 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
595 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
596 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
597 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
599 if ($form->{storno}) {
600 $form->{"qty_$i"} *= -1;
603 if ($form->{"id_$i"}) {
606 if (defined($baseunits{$form->{"id_$i"}})) {
607 $item_unit = $baseunits{$form->{"id_$i"}};
610 $query = qq|SELECT unit FROM parts WHERE id = ?|;
611 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
612 $baseunits{$form->{"id_$i"}} = $item_unit;
615 if (defined($all_units->{$item_unit}->{factor})
616 && ($all_units->{$item_unit}->{factor} ne '')
617 && ($all_units->{$item_unit}->{factor} != 0)) {
618 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
622 $baseqty = $form->{"qty_$i"} * $basefactor;
624 my ($allocated, $taxrate) = (0, 0);
628 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
630 # keep entered selling price
632 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
634 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
636 my $decimalplaces = ($dec > 2) ? $dec : 2;
638 # undo discount formatting
639 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
642 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
644 # round linetotal to 2 decimal places
645 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
646 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
648 if ($form->{taxincluded}) {
649 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
650 $form->{"sellprice_$i"} =
651 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
653 $taxamount = $linetotal * $taxrate;
656 $netamount += $linetotal;
658 if ($taxamount != 0) {
660 $form->{amount}{ $form->{id} }{$_} +=
661 $taxamount * $form->{"${_}_rate"} / $taxrate
662 } split(/ /, $form->{"taxaccounts_$i"});
665 # add amount to income, $form->{amount}{trans_id}{accno}
666 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
668 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
669 $linetotal = $form->round_amount($linetotal, 2);
671 # this is the difference from the inventory
672 $invoicediff += ($amount - $linetotal);
674 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
677 $lastincomeaccno = $form->{"income_accno_$i"};
679 # adjust and round sellprice
680 $form->{"sellprice_$i"} =
681 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
684 next if $payments_only;
686 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
688 if ($form->{"assembly_$i"}) {
689 # record assembly item as allocated
690 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
693 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
697 # get pricegroup_id and save it
698 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
701 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
703 # save detail record in invoice table
705 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
706 sellprice, fxsellprice, discount, allocated, assemblyitem,
707 unit, deliverydate, project_id, serialnumber, pricegroup_id,
708 ordnumber, transdate, cusordnumber, base_qty, subtotal,
709 marge_percent, marge_total, lastcost,
710 price_factor_id, price_factor, marge_price_factor)
711 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
712 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
714 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
715 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
716 $form->{"sellprice_$i"}, $fxsellprice,
717 $form->{"discount_$i"}, $allocated, 'f',
718 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
719 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
720 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
721 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
722 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
723 $form->{"lastcost_$i"},
724 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
725 conv_i($form->{"marge_price_factor_$i"}));
726 do_query($form, $dbh, $query, @values);
728 CVar->save_custom_variables(module => 'IC',
729 sub_module => 'invoice',
730 trans_id => $invoice_id,
731 configs => $ic_cvar_configs,
733 name_prefix => 'ic_',
734 name_postfix => "_$i",
739 # total payments, don't move we need it here
740 for my $i (1 .. $form->{paidaccounts}) {
741 if ($form->{type} eq "credit_note") {
742 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
744 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
746 $form->{paid} += $form->{"paid_$i"};
747 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
750 my ($tax, $diff) = (0, 0);
752 $netamount = $form->round_amount($netamount, 2);
754 # figure out rounding errors for total amount vs netamount + taxes
755 if ($form->{taxincluded}) {
757 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
758 $diff += $amount - $netamount * $form->{exchangerate};
759 $netamount = $amount;
761 foreach my $item (split(/ /, $form->{taxaccounts})) {
762 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
763 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
764 $tax += $form->{amount}{ $form->{id} }{$item};
765 $netamount -= $form->{amount}{ $form->{id} }{$item};
768 $invoicediff += $diff;
769 ######## this only applies to tax included
770 if ($lastincomeaccno) {
771 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
775 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
776 $diff = $amount - $netamount * $form->{exchangerate};
777 $netamount = $amount;
778 foreach my $item (split(/ /, $form->{taxaccounts})) {
779 $form->{amount}{ $form->{id} }{$item} =
780 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
783 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
786 $amount - $form->{amount}{ $form->{id} }{$item} *
787 $form->{exchangerate};
788 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
789 $tax += $form->{amount}{ $form->{id} }{$item};
793 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
795 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
798 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
800 # update exchangerate
801 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
802 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
803 $form->{exchangerate}, 0);
806 $project_id = conv_i($form->{"globalproject_id"});
808 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
809 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
810 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
812 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
814 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
816 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
817 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
818 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
819 do_query($form, $dbh, $query, @values);
820 $form->{amount_cogs}{$trans_id}{$accno} = 0;
824 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
825 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
827 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
829 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
830 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
831 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
832 do_query($form, $dbh, $query, @values);
837 foreach my $trans_id (keys %{ $form->{amount} }) {
838 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
839 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
841 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
843 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
845 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
846 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
847 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
848 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
849 do_query($form, $dbh, $query, @values);
850 $form->{amount}{$trans_id}{$accno} = 0;
854 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
855 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
857 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
859 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
860 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
861 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
862 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
863 do_query($form, $dbh, $query, @values);
868 # deduct payment differences from diff
869 for my $i (1 .. $form->{paidaccounts}) {
870 if ($form->{"paid_$i"} != 0) {
872 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
873 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
877 # record payments and offsetting AR
878 if (!$form->{storno}) {
879 for my $i (1 .. $form->{paidaccounts}) {
881 next if ($form->{"paid_$i"} == 0);
883 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
884 $form->{"datepaid_$i"} = $form->{invdate}
885 unless ($form->{"datepaid_$i"});
886 $form->{datepaid} = $form->{"datepaid_$i"};
890 if ($form->{currency} eq $defaultcurrency) {
891 $form->{"exchangerate_$i"} = 1;
893 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
894 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
898 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
900 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
902 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
903 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
904 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
905 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
906 do_query($form, $dbh, $query, @values);
910 $form->{"paid_$i"} *= -1;
913 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
914 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
915 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
916 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
917 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
918 do_query($form, $dbh, $query, @values);
920 # exchangerate difference
921 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
922 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
926 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
927 $form->{"exchangerate_$i"};
929 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
932 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
938 # update exchange rate
939 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
940 $form->update_exchangerate($dbh, $form->{currency},
941 $form->{"datepaid_$i"},
942 $form->{"exchangerate_$i"}, 0);
946 } else { # if (!$form->{storno})
947 $form->{marge_total} *= -1;
950 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
952 if ($payments_only) {
953 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
954 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
956 $dbh->commit if !$provided_dbh;
958 $main::lxdebug->leave_sub();
962 # record exchange rate differences and gains/losses
963 foreach my $accno (keys %{ $form->{fx} }) {
964 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
966 ($form->{fx}{$accno}{$transdate} =
967 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
972 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
973 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
974 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
975 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
976 do_query($form, $dbh, $query, @values);
981 $amount = $netamount + $tax;
984 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
986 $query = qq|UPDATE ar set
987 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
988 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
989 amount = ?, netamount = ?, paid = ?,
990 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
991 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
992 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
993 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
994 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
995 cp_id = ?, marge_total = ?, marge_percent = ?,
996 globalproject_id = ?, delivery_customer_id = ?,
997 transaction_description = ?, delivery_vendor_id = ?,
998 donumber = ?, invnumber_for_credit_note = ?
1000 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1001 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1002 $amount, $netamount, $form->{"paid"},
1003 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1004 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1005 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1006 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1007 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1008 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1009 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1010 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1011 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1012 conv_i($form->{"id"}));
1013 do_query($form, $dbh, $query, @values);
1016 if ($form->{storno}) {
1019 paid = paid + amount,
1021 intnotes = ? || intnotes
1023 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1024 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1028 $form->{name} = $form->{customer};
1029 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1031 if (!$form->{shipto_id}) {
1032 $form->add_shipto($dbh, $form->{id}, "AR");
1035 # save printed, emailed and queued
1036 $form->save_status($dbh);
1038 Common::webdav_folder($form);
1040 # Link this record to the records it was created from.
1041 RecordLinks->create_links('dbh' => $dbh,
1043 'from_table' => 'oe',
1044 'from_ids' => $form->{convert_from_oe_ids},
1046 'to_id' => $form->{id},
1048 delete $form->{convert_from_oe_ids};
1050 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1052 if (scalar @convert_from_do_ids) {
1053 DO->close_orders('dbh' => $dbh,
1054 'ids' => \@convert_from_do_ids);
1056 RecordLinks->create_links('dbh' => $dbh,
1058 'from_table' => 'delivery_orders',
1059 'from_ids' => \@convert_from_do_ids,
1061 'to_id' => $form->{id},
1064 delete $form->{convert_from_do_ids};
1066 ARAP->close_orders_if_billed('dbh' => $dbh,
1067 'arap_id' => $form->{id},
1071 $dbh->commit if !$provided_dbh;
1073 $main::lxdebug->leave_sub();
1078 sub _delete_payments {
1079 $main::lxdebug->enter_sub();
1081 my ($self, $form, $dbh) = @_;
1083 my @delete_acc_trans_ids;
1085 # Delete old payment entries from acc_trans.
1087 qq|SELECT acc_trans_id
1089 WHERE (trans_id = ?) AND fx_transaction
1093 SELECT at.acc_trans_id
1095 LEFT JOIN chart c ON (at.chart_id = c.id)
1096 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1097 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1100 qq|SELECT at.acc_trans_id
1102 LEFT JOIN chart c ON (at.chart_id = c.id)
1103 WHERE (trans_id = ?)
1104 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1105 ORDER BY at.acc_trans_id
1107 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1109 if (@delete_acc_trans_ids) {
1110 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1111 do_query($form, $dbh, $query);
1114 $main::lxdebug->leave_sub();
1118 $main::lxdebug->enter_sub();
1120 my ($self, $myconfig, $form, $locale) = @_;
1122 # connect to database, turn off autocommit
1123 my $dbh = $form->get_standard_dbh;
1126 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1128 $old_form = save_form();
1130 # Delete all entries in acc_trans from prior payments.
1131 $self->_delete_payments($form, $dbh);
1133 # Save the new payments the user made before cleaning up $form.
1134 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1136 # Clean up $form so that old content won't tamper the results.
1137 %keep_vars = map { $_, 1 } qw(login password id);
1138 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1140 # Retrieve the invoice from the database.
1141 $self->retrieve_invoice($myconfig, $form);
1143 # Set up the content of $form in the way that IS::post_invoice() expects.
1144 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1146 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1147 $item = $form->{invoice_details}->[$row - 1];
1149 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1151 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1154 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1156 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1158 # Restore the payment options from the user input.
1159 map { $form->{$_} = $payments{$_} } keys %payments;
1161 # Get the AR accno (which is normally done by Form::create_links()).
1165 LEFT JOIN chart c ON (at.chart_id = c.id)
1166 WHERE (trans_id = ?)
1167 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1168 ORDER BY at.acc_trans_id
1171 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1173 # Post the new payments.
1174 $self->post_invoice($myconfig, $form, $dbh, 1);
1176 restore_form($old_form);
1178 my $rc = $dbh->commit();
1180 $main::lxdebug->leave_sub();
1185 sub process_assembly {
1186 $main::lxdebug->enter_sub();
1188 my ($dbh, $form, $id, $totalqty) = @_;
1191 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1192 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1194 JOIN parts p ON (a.parts_id = p.id)
1196 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1198 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1202 $ref->{inventory_accno_id} *= 1;
1203 $ref->{expense_accno_id} *= 1;
1205 # multiply by number of assemblies
1206 $ref->{qty} *= $totalqty;
1208 if ($ref->{assembly}) {
1209 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1212 if ($ref->{inventory_accno_id}) {
1213 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1217 # save detail record for individual assembly item in invoice table
1219 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1220 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1221 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1222 do_query($form, $dbh, $query, @values);
1228 $main::lxdebug->leave_sub();
1232 $main::lxdebug->enter_sub();
1234 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1238 $form->{taxzone_id} *=1;
1239 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1240 my $taxzone_id = $form->{"taxzone_id"} * 1;
1242 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1243 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1244 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1245 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1246 FROM invoice i, parts p
1247 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1248 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1249 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1250 WHERE (i.parts_id = p.id)
1251 AND (i.parts_id = ?)
1252 AND ((i.base_qty + i.allocated) < 0)
1254 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1259 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1260 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1264 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1266 # total expenses and inventory
1267 # sellprice is the cost of the item
1268 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1270 if (!$::lx_office_conf{system}->{eur}) {
1271 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1273 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1274 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1275 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1277 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1278 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1284 last if (($totalqty -= $qty) <= 0);
1289 $main::lxdebug->leave_sub();
1294 sub reverse_invoice {
1295 $main::lxdebug->enter_sub();
1297 my ($dbh, $form) = @_;
1299 # reverse inventory items
1301 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1303 JOIN parts p ON (i.parts_id = p.id)
1304 WHERE i.trans_id = ?|;
1305 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1307 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1309 if ($ref->{inventory_accno_id}) {
1310 # de-allocated purchases
1312 qq|SELECT i.id, i.trans_id, i.allocated
1314 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1315 ORDER BY i.trans_id DESC|;
1316 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1318 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1319 my $qty = $ref->{qty};
1320 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1321 $qty = $inhref->{allocated};
1325 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1327 last if (($ref->{qty} -= $qty) <= 0);
1336 my @values = (conv_i($form->{id}));
1337 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1338 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1339 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1341 $main::lxdebug->leave_sub();
1344 sub delete_invoice {
1345 $main::lxdebug->enter_sub();
1347 my ($self, $myconfig, $form) = @_;
1349 # connect to database
1350 my $dbh = $form->get_standard_dbh;
1353 &reverse_invoice($dbh, $form);
1355 my @values = (conv_i($form->{id}));
1357 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1358 # zurückgesetzt werden. Vgl:
1359 # id | storno | storno_id | paid | amount
1360 #----+--------+-----------+---------+-----------
1361 # 18 | f | | 0.00000 | 119.00000
1363 # 18 | t | | 119.00000 | 119.00000
1365 if($form->{storno}){
1366 # storno_id auslesen und korrigieren
1367 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1368 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1372 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1374 # delete spool files
1375 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1377 # delete status entries
1378 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1380 my $rc = $dbh->commit;
1383 my $spool = $::lx_office_conf{paths}->{spool};
1384 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1387 $main::lxdebug->leave_sub();
1392 sub retrieve_invoice {
1393 $main::lxdebug->enter_sub();
1395 my ($self, $myconfig, $form) = @_;
1397 # connect to database
1398 my $dbh = $form->get_standard_dbh;
1400 my ($sth, $ref, $query);
1402 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1406 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1407 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1408 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1409 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1410 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1411 d.curr AS currencies
1415 $ref = selectfirst_hashref_query($form, $dbh, $query);
1416 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1419 my $id = conv_i($form->{id});
1422 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1426 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1427 a.orddate, a.quodate, a.globalproject_id,
1428 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1429 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1430 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1431 a.employee_id, a.salesman_id, a.payment_id,
1432 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1433 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1434 a.marge_total, a.marge_percent,
1437 LEFT JOIN employee e ON (e.id = a.employee_id)
1439 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1440 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1443 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1446 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1447 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1449 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1451 foreach my $vc (qw(customer vendor)) {
1452 next if !$form->{"delivery_${vc}_id"};
1453 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1456 # get printed, emailed
1457 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1458 $sth = prepare_execute_query($form, $dbh, $query, $id);
1460 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1461 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1462 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1463 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1466 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1468 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1469 : $form->{invdate} ? $dbh->quote($form->{invdate})
1473 my $taxzone_id = $form->{taxzone_id} *= 1;
1474 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1476 # retrieve individual items
1479 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1480 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1481 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1484 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1485 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1486 i.price_factor_id, i.price_factor, i.marge_price_factor,
1487 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1488 pr.projectnumber, pg.partsgroup, prg.pricegroup
1491 LEFT JOIN parts p ON (i.parts_id = p.id)
1492 LEFT JOIN project pr ON (i.project_id = pr.id)
1493 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1494 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1496 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1497 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1498 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1500 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1502 $sth = prepare_execute_query($form, $dbh, $query, $id);
1504 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1505 # Retrieve custom variables.
1506 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1508 sub_module => 'invoice',
1509 trans_id => $ref->{invoice_id},
1511 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1512 delete $ref->{invoice_id};
1514 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1515 delete($ref->{"part_inventory_accno_id"});
1517 foreach my $type (qw(inventory income expense)) {
1518 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1519 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1520 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1524 # get tax rates and description
1525 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1527 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1528 LEFT JOIN chart c ON (c.id = t.chart_id)
1530 (SELECT tk.tax_id FROM taxkeys tk
1531 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1532 AND startdate <= date($transdate)
1533 ORDER BY startdate DESC LIMIT 1)
1535 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1536 $ref->{taxaccounts} = "";
1538 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1540 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1544 $ref->{taxaccounts} .= "$ptr->{accno} ";
1546 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1547 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1548 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1549 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1550 $form->{taxaccounts} .= "$ptr->{accno} ";
1555 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1557 chop $ref->{taxaccounts};
1558 push @{ $form->{invoice_details} }, $ref;
1563 Common::webdav_folder($form);
1566 my $rc = $dbh->commit;
1568 $main::lxdebug->leave_sub();
1574 $main::lxdebug->enter_sub();
1576 my ($self, $myconfig, $form) = @_;
1578 # connect to database
1579 my $dbh = $form->get_standard_dbh;
1581 my $dateformat = $myconfig->{dateformat};
1582 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1584 my (@values, $duedate, $ref, $query);
1586 if ($form->{invdate}) {
1587 $duedate = "to_date(?, '$dateformat')";
1588 push @values, $form->{invdate};
1590 $duedate = "current_date";
1593 my $cid = conv_i($form->{customer_id});
1596 if ($form->{payment_id}) {
1597 $payment_id = "(pt.id = ?) OR";
1598 push @values, conv_i($form->{payment_id});
1604 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1605 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1606 c.street, c.zipcode, c.city, c.country,
1607 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1608 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1609 b.discount AS tradediscount, b.description AS business
1611 LEFT JOIN business b ON (b.id = c.business_id)
1612 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1615 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1617 delete $ref->{salesman_id} if !$ref->{salesman_id};
1619 map { $form->{$_} = $ref->{$_} } keys %$ref;
1622 qq|SELECT sum(amount - paid) AS dunning_amount
1624 WHERE (paid < amount)
1625 AND (customer_id = ?)
1626 AND (dunning_config_id IS NOT NULL)|;
1627 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1628 map { $form->{$_} = $ref->{$_} } keys %$ref;
1631 qq|SELECT dnn.dunning_description AS max_dunning_level
1632 FROM dunning_config dnn
1633 WHERE id IN (SELECT dunning_config_id
1635 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1636 ORDER BY dunning_level DESC LIMIT 1|;
1637 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1638 map { $form->{$_} = $ref->{$_} } keys %$ref;
1640 $form->{creditremaining} = $form->{creditlimit};
1641 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1642 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1643 $form->{creditremaining} -= $value;
1647 (SELECT e.buy FROM exchangerate e
1648 WHERE e.curr = o.curr
1649 AND e.transdate = o.transdate)
1651 WHERE o.customer_id = ?
1652 AND o.quotation = '0'
1653 AND o.closed = '0'|;
1654 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1656 while (my ($amount, $exch) = $sth->fetchrow_array) {
1657 $exch = 1 unless $exch;
1658 $form->{creditremaining} -= $amount * $exch;
1662 # get shipto if we did not converted an order or invoice
1663 if (!$form->{shipto}) {
1664 map { delete $form->{$_} }
1665 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1666 shiptostreet shiptozipcode shiptocity shiptocountry
1667 shiptocontact shiptophone shiptofax shiptoemail);
1669 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1670 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1672 map { $form->{$_} = $ref->{$_} } keys %$ref;
1675 # setup last accounts used for this customer
1676 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1678 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1680 JOIN acc_trans ac ON (ac.chart_id = c.id)
1681 JOIN ar a ON (a.id = ac.trans_id)
1682 WHERE a.customer_id = ?
1683 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1684 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1685 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1688 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1689 if ($ref->{category} eq 'I') {
1691 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1693 if ($form->{initial_transdate}) {
1695 qq|SELECT tk.tax_id, t.rate
1697 LEFT JOIN tax t ON tk.tax_id = t.id
1698 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1699 ORDER BY tk.startdate DESC
1701 my ($tax_id, $rate) =
1702 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1703 $form->{initial_transdate});
1704 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1707 if ($ref->{category} eq 'A') {
1708 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1712 $form->{rowcount} = $i if ($i && !$form->{type});
1715 $main::lxdebug->leave_sub();
1719 $main::lxdebug->enter_sub();
1721 my ($self, $myconfig, $form) = @_;
1723 # connect to database
1724 my $dbh = $form->get_standard_dbh;
1726 my $i = $form->{rowcount};
1728 my $where = qq|NOT p.obsolete = '1'|;
1731 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1732 my ($table, $field) = split m/\./, $column;
1733 next if !$form->{"${field}_${i}"};
1734 $where .= qq| AND lower(${column}) ILIKE ?|;
1735 push @values, '%' . $form->{"${field}_${i}"} . '%';
1738 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1739 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1740 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1741 push @values, $form->{"partnumber_$i"};
1744 if ($form->{"id_${i}"}) {
1745 $where .= qq| AND p.id = ?|;
1746 push @values, $form->{"id_${i}"};
1749 if ($form->{"description_$i"}) {
1750 $where .= qq| ORDER BY p.description|;
1752 $where .= qq| ORDER BY p.partnumber|;
1756 if ($form->{type} eq "invoice") {
1758 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1759 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1763 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1767 my $taxzone_id = $form->{taxzone_id} * 1;
1768 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1772 p.id, p.partnumber, p.description, p.sellprice,
1773 p.listprice, p.inventory_accno_id, p.lastcost,
1775 c1.accno AS inventory_accno,
1776 c1.new_chart_id AS inventory_new_chart,
1777 date($transdate) - c1.valid_from AS inventory_valid,
1779 c2.accno AS income_accno,
1780 c2.new_chart_id AS income_new_chart,
1781 date($transdate) - c2.valid_from AS income_valid,
1783 c3.accno AS expense_accno,
1784 c3.new_chart_id AS expense_new_chart,
1785 date($transdate) - c3.valid_from AS expense_valid,
1787 p.unit, p.assembly, p.bin, p.onhand,
1788 p.notes AS partnotes, p.notes AS longdescription,
1789 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1792 pfac.factor AS price_factor,
1797 LEFT JOIN chart c1 ON
1798 ((SELECT inventory_accno_id
1799 FROM buchungsgruppen
1800 WHERE id = p.buchungsgruppen_id) = c1.id)
1801 LEFT JOIN chart c2 ON
1802 ((SELECT income_accno_id_${taxzone_id}
1803 FROM buchungsgruppen
1804 WHERE id = p.buchungsgruppen_id) = c2.id)
1805 LEFT JOIN chart c3 ON
1806 ((SELECT expense_accno_id_${taxzone_id}
1807 FROM buchungsgruppen
1808 WHERE id = p.buchungsgruppen_id) = c3.id)
1809 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1810 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1812 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1814 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1816 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1817 [ qq|SELECT tr.translation, tr.longdescription
1819 WHERE tr.language_id IN
1822 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1825 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1827 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1829 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1830 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1831 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1832 if (!$ref->{inventory_accno_id}) {
1833 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1835 delete($ref->{inventory_accno_id});
1837 foreach my $type (qw(inventory income expense)) {
1838 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1840 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1843 ($ref->{"${type}_accno"},
1844 $ref->{"${type}_new_chart"},
1845 $ref->{"${type}_valid"})
1846 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1850 if ($form->{payment_id} eq "") {
1851 $form->{payment_id} = $form->{part_payment_id};
1854 # get tax rates and description
1855 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1857 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1859 LEFT JOIN chart c ON (c.id = t.chart_id)
1863 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1865 ORDER BY startdate DESC
1868 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1869 my $stw = $dbh->prepare($query);
1870 $stw->execute(@values) || $form->dberror($query);
1872 $ref->{taxaccounts} = "";
1874 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1876 # if ($customertax{$ref->{accno}})
1877 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1881 $ref->{taxaccounts} .= "$ptr->{accno} ";
1883 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1884 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1885 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1886 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1887 $form->{taxaccounts} .= "$ptr->{accno} ";
1893 chop $ref->{taxaccounts};
1895 if ($form->{language_id}) {
1896 for my $spec (@translation_queries) {
1897 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1898 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1899 next unless $translation;
1900 $ref->{description} = $translation;
1901 $ref->{longdescription} = $longdescription;
1906 $ref->{onhand} *= 1;
1908 push @{ $form->{item_list} }, $ref;
1911 $_->[1]->finish for @translation_queries;
1913 foreach my $item (@{ $form->{item_list} }) {
1914 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1915 trans_id => $item->{id},
1919 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1922 $main::lxdebug->leave_sub();
1925 ##########################
1926 # get pricegroups from database
1927 # build up selected pricegroup
1928 # if an exchange rate - change price
1931 sub get_pricegroups_for_parts {
1933 $main::lxdebug->enter_sub();
1935 my ($self, $myconfig, $form) = @_;
1937 my $dbh = $form->get_standard_dbh;
1939 $form->{"PRICES"} = {};
1943 my $all_units = AM->retrieve_units($myconfig, $form);
1944 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1945 $form->{"PRICES"}{$i} = [];
1947 $id = $form->{"id_$i"};
1949 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1950 $id = $form->{"new_id_$i"};
1953 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1955 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1957 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
1958 my $sellprice = $form->{"sellprice_$i"};
1959 my $pricegroup_id = $form->{"pricegroup_id_$i"};
1960 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1961 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1963 my $price_new = $form->{"price_new_$i"};
1964 my $price_old = $form->{"price_old_$i"};
1966 if (!$form->{"unit_old_$i"}) {
1967 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1968 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1969 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1970 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1973 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1974 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1975 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1977 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1978 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1979 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1980 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1981 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1982 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1983 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1988 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1989 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1990 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1991 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1992 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1996 if (!$form->{"basefactor_$i"}) {
1997 $form->{"basefactor_$i"} = 1;
2003 sellprice AS default_sellprice,
2006 'selected' AS selected
2012 parts.sellprice AS default_sellprice,
2013 pricegroup.pricegroup,
2017 LEFT JOIN parts ON parts.id = parts_id
2018 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2020 ORDER BY pricegroup|;
2021 my @values = (conv_i($id), conv_i($id));
2022 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2024 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2026 $pkr->{selected} = '';
2028 # if there is an exchange rate change price
2029 if (($form->{exchangerate} * 1) != 0) {
2030 $pkr->{price} /= $form->{exchangerate};
2033 $pkr->{price} *= $form->{"basefactor_$i"};
2034 $pkr->{price} *= $basefactor;
2035 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2037 if ($selectedpricegroup_id eq undef) {
2038 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2039 # Case A: open old invoice, no pricegroup selected
2040 # Case B: add new article to invoice, no pricegroup selected
2042 # to distinguish case A and B the variable pricegroup_id_$i is used
2043 # for new articles this variable isn't defined, for loaded articles it is
2044 # sellprice can't be used, as it already has 0,00 set
2046 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2048 $pkr->{selected} = ' selected';
2050 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2051 and not defined $form->{"pricegroup_id_$i"}
2052 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2053 # for the case where pricegroup prices haven't been set
2055 # Case B: use default pricegroup of customer
2057 $pkr->{selected} = ' selected'; # unless $form->{selected};
2059 # no customer pricesgroup set
2060 if ($pkr->{price} == $pkr->{default_sellprice}) {
2062 $pkr->{price} = $form->{"sellprice_$i"};
2066 # this sub should not set anything and only return. --sschoeling, 20090506
2067 # is this correct? put in again... -- grichardson 20110119
2068 $form->{"sellprice_$i"} = $pkr->{price};
2071 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2072 $pkr->{price} = $form->{"sellprice_$i"};
2073 $pkr->{selected} = ' selected';
2077 # existing article: pricegroup or price changed
2078 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2079 if ($selectedpricegroup_id ne $pricegroup_old) {
2080 # pricegroup has changed
2081 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2082 $pkr->{selected} = ' selected';
2084 } elsif ( ($form->parse_amount($myconfig, $price_new)
2085 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2086 and ($price_new ne 0) and defined $price_new) {
2087 # sellprice has changed
2088 # when loading existing invoices $price_new is NULL
2089 if ($pkr->{pricegroup_id} == 0) {
2090 $pkr->{price} = $form->{"sellprice_$i"};
2091 $pkr->{selected} = ' selected';
2093 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2094 # neither sellprice nor pricegroup changed
2095 $pkr->{selected} = ' selected';
2096 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2097 # $pkr->{price} = $form->{"sellprice_$i"};
2099 $pkr->{price} = $form->{"sellprice_$i"};
2103 push @{ $form->{PRICES}{$i} }, $pkr;
2106 $form->{"basefactor_$i"} *= $basefactor;
2113 $main::lxdebug->leave_sub();
2117 $main::lxdebug->enter_sub();
2119 my ($self, $myconfig, $form, $table) = @_;
2121 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2123 # make sure there's no funny stuff in $table
2124 # ToDO: die when this happens and throw an error
2125 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2127 my $dbh = $form->get_standard_dbh;
2129 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2130 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2132 $main::lxdebug->leave_sub();
2138 $main::lxdebug->enter_sub();
2140 my ($self, $myconfig, $form, $table, $id) = @_;
2142 $main::lxdebug->leave_sub() and return 0 unless ($id);
2144 # make sure there's no funny stuff in $table
2145 # ToDO: die when this happens and throw an error
2146 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2148 my $dbh = $form->get_standard_dbh;
2150 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2151 my ($result) = selectrow_query($form, $dbh, $query, $id);
2153 $main::lxdebug->leave_sub();
2158 sub get_standard_accno_current_assets {
2159 $main::lxdebug->enter_sub();
2161 my ($self, $myconfig, $form) = @_;
2163 my $dbh = $form->get_standard_dbh;
2165 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2166 my ($result) = selectrow_query($form, $dbh, $query);
2168 $main::lxdebug->leave_sub();