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 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
455 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
456 WHERE (ct.id = ?) $where
459 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
461 # remove id and taxincluded before copy back
462 delete @$ref{qw(id taxincluded)};
464 @wanted_vars = grep({ $_ } @wanted_vars);
465 if (scalar(@wanted_vars) > 0) {
467 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
468 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
471 map { $form->{$_} = $ref->{$_} } keys %$ref;
473 if ($form->{delivery_customer_id}) {
475 qq|SELECT *, notes as customernotes
479 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
481 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
484 if ($form->{delivery_vendor_id}) {
486 qq|SELECT *, notes as customernotes
490 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
492 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
495 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
497 'trans_id' => $form->{customer_id});
498 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
500 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
501 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
502 'language_id' => $language_id,
503 'allow_fallback' => 1);
506 $main::lxdebug->leave_sub();
510 $main::lxdebug->enter_sub();
512 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
514 # connect to database, turn off autocommit
515 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
517 my ($query, $sth, $null, $project_id, @values);
518 my $exchangerate = 0;
520 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
523 if (!$form->{employee_id}) {
524 $form->get_employee($dbh);
527 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
528 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
529 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
530 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
531 if (!$form->{department_id}){
532 ($null, $form->{department_id}) = split(/--/, $form->{department});
535 my $all_units = AM->retrieve_units($myconfig, $form);
537 if (!$payments_only) {
539 &reverse_invoice($dbh, $form);
542 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
543 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
545 $query = qq|SELECT nextval('glid')|;
546 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
548 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
549 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
551 if (!$form->{invnumber}) {
553 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
554 "cnnumber" : "invnumber", $dbh);
559 my ($netamount, $invoicediff) = (0, 0);
560 my ($amount, $linetotal, $lastincomeaccno);
562 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
563 my $defaultcurrency = (split m/:/, $currencies)[0];
565 if ($form->{currency} eq $defaultcurrency) {
566 $form->{exchangerate} = 1;
568 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
571 $form->{exchangerate} =
574 : $form->parse_amount($myconfig, $form->{exchangerate});
576 $form->{expense_inventory} = "";
580 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
581 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
584 $form->{amount} = {};
585 $form->{amount_cogs} = {};
587 foreach my $i (1 .. $form->{rowcount}) {
588 if ($form->{type} eq "credit_note") {
589 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
590 $form->{shipped} = 1;
592 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
597 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
598 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
599 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
601 if ($form->{storno}) {
602 $form->{"qty_$i"} *= -1;
605 if ($form->{"id_$i"}) {
608 if (defined($baseunits{$form->{"id_$i"}})) {
609 $item_unit = $baseunits{$form->{"id_$i"}};
612 $query = qq|SELECT unit FROM parts WHERE id = ?|;
613 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
614 $baseunits{$form->{"id_$i"}} = $item_unit;
617 if (defined($all_units->{$item_unit}->{factor})
618 && ($all_units->{$item_unit}->{factor} ne '')
619 && ($all_units->{$item_unit}->{factor} != 0)) {
620 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
624 $baseqty = $form->{"qty_$i"} * $basefactor;
626 my ($allocated, $taxrate) = (0, 0);
630 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
632 # keep entered selling price
634 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
636 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
638 my $decimalplaces = ($dec > 2) ? $dec : 2;
640 # undo discount formatting
641 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
644 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
646 # round linetotal to 2 decimal places
647 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
648 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
650 if ($form->{taxincluded}) {
651 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
652 $form->{"sellprice_$i"} =
653 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
655 $taxamount = $linetotal * $taxrate;
658 $netamount += $linetotal;
660 if ($taxamount != 0) {
662 $form->{amount}{ $form->{id} }{$_} +=
663 $taxamount * $form->{"${_}_rate"} / $taxrate
664 } split(/ /, $form->{"taxaccounts_$i"});
667 # add amount to income, $form->{amount}{trans_id}{accno}
668 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
670 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
671 $linetotal = $form->round_amount($linetotal, 2);
673 # this is the difference from the inventory
674 $invoicediff += ($amount - $linetotal);
676 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
679 $lastincomeaccno = $form->{"income_accno_$i"};
681 # adjust and round sellprice
682 $form->{"sellprice_$i"} =
683 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
686 next if $payments_only;
688 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
690 if ($form->{"assembly_$i"}) {
691 # record assembly item as allocated
692 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
695 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
699 # get pricegroup_id and save it
700 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
703 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
705 # save detail record in invoice table
707 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
708 sellprice, fxsellprice, discount, allocated, assemblyitem,
709 unit, deliverydate, project_id, serialnumber, pricegroup_id,
710 ordnumber, transdate, cusordnumber, base_qty, subtotal,
711 marge_percent, marge_total, lastcost,
712 price_factor_id, price_factor, marge_price_factor)
713 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
714 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
716 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
717 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
718 $form->{"sellprice_$i"}, $fxsellprice,
719 $form->{"discount_$i"}, $allocated, 'f',
720 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
721 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
722 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
723 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
724 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
725 $form->{"lastcost_$i"},
726 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
727 conv_i($form->{"marge_price_factor_$i"}));
728 do_query($form, $dbh, $query, @values);
730 CVar->save_custom_variables(module => 'IC',
731 sub_module => 'invoice',
732 trans_id => $invoice_id,
733 configs => $ic_cvar_configs,
735 name_prefix => 'ic_',
736 name_postfix => "_$i",
741 # total payments, don't move we need it here
742 for my $i (1 .. $form->{paidaccounts}) {
743 if ($form->{type} eq "credit_note") {
744 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
746 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
748 $form->{paid} += $form->{"paid_$i"};
749 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
752 my ($tax, $diff) = (0, 0);
754 $netamount = $form->round_amount($netamount, 2);
756 # figure out rounding errors for total amount vs netamount + taxes
757 if ($form->{taxincluded}) {
759 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
760 $diff += $amount - $netamount * $form->{exchangerate};
761 $netamount = $amount;
763 foreach my $item (split(/ /, $form->{taxaccounts})) {
764 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
765 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
766 $tax += $form->{amount}{ $form->{id} }{$item};
767 $netamount -= $form->{amount}{ $form->{id} }{$item};
770 $invoicediff += $diff;
771 ######## this only applies to tax included
772 if ($lastincomeaccno) {
773 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
777 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
778 $diff = $amount - $netamount * $form->{exchangerate};
779 $netamount = $amount;
780 foreach my $item (split(/ /, $form->{taxaccounts})) {
781 $form->{amount}{ $form->{id} }{$item} =
782 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
785 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
788 $amount - $form->{amount}{ $form->{id} }{$item} *
789 $form->{exchangerate};
790 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
791 $tax += $form->{amount}{ $form->{id} }{$item};
795 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
797 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
800 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
802 # update exchangerate
803 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
804 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
805 $form->{exchangerate}, 0);
808 $project_id = conv_i($form->{"globalproject_id"});
810 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
811 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
812 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
814 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
816 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
818 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
819 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
820 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
821 do_query($form, $dbh, $query, @values);
822 $form->{amount_cogs}{$trans_id}{$accno} = 0;
826 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
827 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
829 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
833 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
834 do_query($form, $dbh, $query, @values);
839 foreach my $trans_id (keys %{ $form->{amount} }) {
840 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
841 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
843 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
845 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
847 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
848 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
849 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
850 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
851 do_query($form, $dbh, $query, @values);
852 $form->{amount}{$trans_id}{$accno} = 0;
856 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
857 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
859 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
861 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
862 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
863 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
864 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
865 do_query($form, $dbh, $query, @values);
870 # deduct payment differences from diff
871 for my $i (1 .. $form->{paidaccounts}) {
872 if ($form->{"paid_$i"} != 0) {
874 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
875 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
879 # record payments and offsetting AR
880 if (!$form->{storno}) {
881 for my $i (1 .. $form->{paidaccounts}) {
883 if ($form->{"acc_trans_id_$i"}
885 && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
889 next if ($form->{"paid_$i"} == 0);
891 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
892 $form->{"datepaid_$i"} = $form->{invdate}
893 unless ($form->{"datepaid_$i"});
894 $form->{datepaid} = $form->{"datepaid_$i"};
898 if ($form->{currency} eq $defaultcurrency) {
899 $form->{"exchangerate_$i"} = 1;
901 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
902 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
906 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
908 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
910 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
911 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
912 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
913 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
914 do_query($form, $dbh, $query, @values);
918 $form->{"paid_$i"} *= -1;
919 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
922 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
923 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
924 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
925 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
926 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
927 do_query($form, $dbh, $query, @values);
929 # exchangerate difference
930 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
931 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
935 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
936 $form->{"exchangerate_$i"};
938 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
941 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
947 # update exchange rate
948 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
949 $form->update_exchangerate($dbh, $form->{currency},
950 $form->{"datepaid_$i"},
951 $form->{"exchangerate_$i"}, 0);
955 } else { # if (!$form->{storno})
956 $form->{marge_total} *= -1;
959 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
961 if ($payments_only) {
962 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
963 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
965 $dbh->commit if !$provided_dbh;
967 $main::lxdebug->leave_sub();
971 # record exchange rate differences and gains/losses
972 foreach my $accno (keys %{ $form->{fx} }) {
973 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
975 ($form->{fx}{$accno}{$transdate} =
976 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
981 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
982 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
983 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
984 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
985 do_query($form, $dbh, $query, @values);
990 $amount = $netamount + $tax;
993 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
995 $query = qq|UPDATE ar set
996 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
997 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
998 amount = ?, netamount = ?, paid = ?,
999 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1000 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1001 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1002 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1003 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1004 cp_id = ?, marge_total = ?, marge_percent = ?,
1005 globalproject_id = ?, delivery_customer_id = ?,
1006 transaction_description = ?, delivery_vendor_id = ?,
1007 donumber = ?, invnumber_for_credit_note = ?
1009 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1010 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1011 $amount, $netamount, $form->{"paid"},
1012 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1013 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1014 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1015 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1016 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1017 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1018 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1019 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1020 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1021 conv_i($form->{"id"}));
1022 do_query($form, $dbh, $query, @values);
1025 if ($form->{storno}) {
1028 paid = paid + amount,
1030 intnotes = ? || intnotes
1032 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1033 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1037 $form->{name} = $form->{customer};
1038 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1040 if (!$form->{shipto_id}) {
1041 $form->add_shipto($dbh, $form->{id}, "AR");
1044 # save printed, emailed and queued
1045 $form->save_status($dbh);
1047 Common::webdav_folder($form);
1049 # Link this record to the records it was created from.
1050 RecordLinks->create_links('dbh' => $dbh,
1052 'from_table' => 'oe',
1053 'from_ids' => $form->{convert_from_oe_ids},
1055 'to_id' => $form->{id},
1057 delete $form->{convert_from_oe_ids};
1059 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1061 if (scalar @convert_from_do_ids) {
1062 DO->close_orders('dbh' => $dbh,
1063 'ids' => \@convert_from_do_ids);
1065 RecordLinks->create_links('dbh' => $dbh,
1067 'from_table' => 'delivery_orders',
1068 'from_ids' => \@convert_from_do_ids,
1070 'to_id' => $form->{id},
1073 delete $form->{convert_from_do_ids};
1075 ARAP->close_orders_if_billed('dbh' => $dbh,
1076 'arap_id' => $form->{id},
1080 $dbh->commit if !$provided_dbh;
1082 $main::lxdebug->leave_sub();
1087 sub _delete_payments {
1088 $main::lxdebug->enter_sub();
1090 my ($self, $form, $dbh) = @_;
1092 my @delete_acc_trans_ids;
1094 # Delete old payment entries from acc_trans.
1096 qq|SELECT acc_trans_id
1098 WHERE (trans_id = ?) AND fx_transaction
1102 SELECT at.acc_trans_id
1104 LEFT JOIN chart c ON (at.chart_id = c.id)
1105 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1106 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1109 qq|SELECT at.acc_trans_id
1111 LEFT JOIN chart c ON (at.chart_id = c.id)
1112 WHERE (trans_id = ?)
1113 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1114 ORDER BY at.acc_trans_id
1116 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1118 if (@delete_acc_trans_ids) {
1119 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1120 do_query($form, $dbh, $query);
1123 $main::lxdebug->leave_sub();
1127 $main::lxdebug->enter_sub();
1129 my ($self, $myconfig, $form, $locale) = @_;
1131 # connect to database, turn off autocommit
1132 my $dbh = $form->get_standard_dbh;
1135 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1137 $old_form = save_form();
1139 # Delete all entries in acc_trans from prior payments.
1140 if ($::lx_office_conf{features}->{payments_changeable} != 0) {
1141 $self->_delete_payments($form, $dbh);
1144 # Save the new payments the user made before cleaning up $form.
1145 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 };
1147 # Clean up $form so that old content won't tamper the results.
1148 %keep_vars = map { $_, 1 } qw(login password id);
1149 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1151 # Retrieve the invoice from the database.
1152 $self->retrieve_invoice($myconfig, $form);
1154 # Set up the content of $form in the way that IS::post_invoice() expects.
1155 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1157 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1158 $item = $form->{invoice_details}->[$row - 1];
1160 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1162 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1165 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1167 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1169 # Restore the payment options from the user input.
1170 map { $form->{$_} = $payments{$_} } keys %payments;
1172 # Get the AR accno (which is normally done by Form::create_links()).
1176 LEFT JOIN chart c ON (at.chart_id = c.id)
1177 WHERE (trans_id = ?)
1178 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1179 ORDER BY at.acc_trans_id
1182 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1184 # Post the new payments.
1185 $self->post_invoice($myconfig, $form, $dbh, 1);
1187 restore_form($old_form);
1189 my $rc = $dbh->commit();
1191 $main::lxdebug->leave_sub();
1196 sub process_assembly {
1197 $main::lxdebug->enter_sub();
1199 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1202 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1203 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1205 JOIN parts p ON (a.parts_id = p.id)
1207 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1209 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1213 $ref->{inventory_accno_id} *= 1;
1214 $ref->{expense_accno_id} *= 1;
1216 # multiply by number of assemblies
1217 $ref->{qty} *= $totalqty;
1219 if ($ref->{assembly}) {
1220 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1223 if ($ref->{inventory_accno_id}) {
1224 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1228 # save detail record for individual assembly item in invoice table
1230 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1231 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1232 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1233 do_query($form, $dbh, $query, @values);
1239 $main::lxdebug->leave_sub();
1243 $main::lxdebug->enter_sub();
1245 # adjust allocated in table invoice according to FIFO princicple
1246 # for a certain part with part_id $id
1248 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1252 $form->{taxzone_id} *=1;
1253 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1254 my $taxzone_id = $form->{"taxzone_id"} * 1;
1256 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1257 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1258 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1259 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1260 FROM invoice i, parts p
1261 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1262 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1263 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1264 WHERE (i.parts_id = p.id)
1265 AND (i.parts_id = ?)
1266 AND ((i.base_qty + i.allocated) < 0)
1268 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1273 # all invoice entries of an example part:
1275 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1276 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1277 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1278 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1279 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1280 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1281 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1283 # 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
1284 # and all parts have been allocated
1286 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1287 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1289 # in this example there are still 4 unsold articles
1292 # search all invoice entries for the part in question, adjusting "allocated"
1293 # until the total number of sold parts has been reached
1295 # ORDER BY trans_id ensures FIFO
1298 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1299 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1303 # update allocated in invoice
1304 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1306 # total expenses and inventory
1307 # sellprice is the cost of the item
1308 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1310 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1311 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1312 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1314 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1315 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1316 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1318 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1319 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1325 last if (($totalqty -= $qty) <= 0);
1330 $main::lxdebug->leave_sub();
1335 sub reverse_invoice {
1336 $main::lxdebug->enter_sub();
1338 my ($dbh, $form) = @_;
1340 # reverse inventory items
1342 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1344 JOIN parts p ON (i.parts_id = p.id)
1345 WHERE i.trans_id = ?|;
1346 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1348 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1350 if ($ref->{inventory_accno_id}) {
1351 # de-allocated purchases
1353 qq|SELECT i.id, i.trans_id, i.allocated
1355 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1356 ORDER BY i.trans_id DESC|;
1357 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1359 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1360 my $qty = $ref->{qty};
1361 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1362 $qty = $inhref->{allocated};
1366 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1368 last if (($ref->{qty} -= $qty) <= 0);
1377 my @values = (conv_i($form->{id}));
1378 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1379 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1380 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1382 $main::lxdebug->leave_sub();
1385 sub delete_invoice {
1386 $main::lxdebug->enter_sub();
1388 my ($self, $myconfig, $form) = @_;
1390 # connect to database
1391 my $dbh = $form->get_standard_dbh;
1394 &reverse_invoice($dbh, $form);
1396 my @values = (conv_i($form->{id}));
1398 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1399 # zurückgesetzt werden. Vgl:
1400 # id | storno | storno_id | paid | amount
1401 #----+--------+-----------+---------+-----------
1402 # 18 | f | | 0.00000 | 119.00000
1404 # 18 | t | | 119.00000 | 119.00000
1406 if($form->{storno}){
1407 # storno_id auslesen und korrigieren
1408 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1409 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1413 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1415 # delete spool files
1416 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1418 # delete status entries
1419 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1421 my $rc = $dbh->commit;
1424 my $spool = $::lx_office_conf{paths}->{spool};
1425 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1428 $main::lxdebug->leave_sub();
1433 sub retrieve_invoice {
1434 $main::lxdebug->enter_sub();
1436 my ($self, $myconfig, $form) = @_;
1438 # connect to database
1439 my $dbh = $form->get_standard_dbh;
1441 my ($sth, $ref, $query);
1443 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1447 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1448 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1449 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1450 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1451 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1452 d.curr AS currencies
1456 $ref = selectfirst_hashref_query($form, $dbh, $query);
1457 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1460 my $id = conv_i($form->{id});
1463 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1467 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1468 a.orddate, a.quodate, a.globalproject_id,
1469 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1470 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1471 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1472 a.employee_id, a.salesman_id, a.payment_id,
1473 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1474 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1475 a.marge_total, a.marge_percent,
1478 LEFT JOIN employee e ON (e.id = a.employee_id)
1480 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1481 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1483 # remove any trailing whitespace
1484 $form->{currency} =~ s/\s*$//;
1486 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1489 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1490 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1492 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1494 foreach my $vc (qw(customer vendor)) {
1495 next if !$form->{"delivery_${vc}_id"};
1496 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1499 # get printed, emailed
1500 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1501 $sth = prepare_execute_query($form, $dbh, $query, $id);
1503 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1504 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1505 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1506 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1509 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1511 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1512 : $form->{invdate} ? $dbh->quote($form->{invdate})
1516 my $taxzone_id = $form->{taxzone_id} *= 1;
1517 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1519 # retrieve individual items
1522 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1523 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1524 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1527 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1528 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1529 i.price_factor_id, i.price_factor, i.marge_price_factor,
1530 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1531 pr.projectnumber, pg.partsgroup, prg.pricegroup
1534 LEFT JOIN parts p ON (i.parts_id = p.id)
1535 LEFT JOIN project pr ON (i.project_id = pr.id)
1536 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1537 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1539 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1540 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1541 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1543 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1545 $sth = prepare_execute_query($form, $dbh, $query, $id);
1547 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1548 # Retrieve custom variables.
1549 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1551 sub_module => 'invoice',
1552 trans_id => $ref->{invoice_id},
1554 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1555 delete $ref->{invoice_id};
1557 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1558 delete($ref->{"part_inventory_accno_id"});
1560 foreach my $type (qw(inventory income expense)) {
1561 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1562 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1563 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1567 # get tax rates and description
1568 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1570 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1571 LEFT JOIN chart c ON (c.id = t.chart_id)
1573 (SELECT tk.tax_id FROM taxkeys tk
1574 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1575 AND startdate <= date($transdate)
1576 ORDER BY startdate DESC LIMIT 1)
1578 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1579 $ref->{taxaccounts} = "";
1581 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1583 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1587 $ref->{taxaccounts} .= "$ptr->{accno} ";
1589 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1590 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1591 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1592 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1593 $form->{taxaccounts} .= "$ptr->{accno} ";
1598 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1600 chop $ref->{taxaccounts};
1601 push @{ $form->{invoice_details} }, $ref;
1606 Common::webdav_folder($form);
1609 my $rc = $dbh->commit;
1611 $main::lxdebug->leave_sub();
1617 $main::lxdebug->enter_sub();
1619 my ($self, $myconfig, $form) = @_;
1621 # connect to database
1622 my $dbh = $form->get_standard_dbh;
1624 my $dateformat = $myconfig->{dateformat};
1625 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1627 my (@values, $duedate, $ref, $query);
1629 if ($form->{invdate}) {
1630 $duedate = "to_date(?, '$dateformat')";
1631 push @values, $form->{invdate};
1633 $duedate = "current_date";
1636 my $cid = conv_i($form->{customer_id});
1639 if ($form->{payment_id}) {
1640 $payment_id = "(pt.id = ?) OR";
1641 push @values, conv_i($form->{payment_id});
1647 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1648 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1649 c.street, c.zipcode, c.city, c.country,
1650 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1651 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1652 b.discount AS tradediscount, b.description AS business
1654 LEFT JOIN business b ON (b.id = c.business_id)
1655 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1658 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1660 delete $ref->{salesman_id} if !$ref->{salesman_id};
1662 map { $form->{$_} = $ref->{$_} } keys %$ref;
1665 qq|SELECT sum(amount - paid) AS dunning_amount
1667 WHERE (paid < amount)
1668 AND (customer_id = ?)
1669 AND (dunning_config_id IS NOT NULL)|;
1670 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1671 map { $form->{$_} = $ref->{$_} } keys %$ref;
1674 qq|SELECT dnn.dunning_description AS max_dunning_level
1675 FROM dunning_config dnn
1676 WHERE id IN (SELECT dunning_config_id
1678 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1679 ORDER BY dunning_level DESC LIMIT 1|;
1680 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1681 map { $form->{$_} = $ref->{$_} } keys %$ref;
1683 $form->{creditremaining} = $form->{creditlimit};
1684 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1685 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1686 $form->{creditremaining} -= $value;
1690 (SELECT e.buy FROM exchangerate e
1691 WHERE e.curr = o.curr
1692 AND e.transdate = o.transdate)
1694 WHERE o.customer_id = ?
1695 AND o.quotation = '0'
1696 AND o.closed = '0'|;
1697 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1699 while (my ($amount, $exch) = $sth->fetchrow_array) {
1700 $exch = 1 unless $exch;
1701 $form->{creditremaining} -= $amount * $exch;
1705 # get shipto if we did not converted an order or invoice
1706 if (!$form->{shipto}) {
1707 map { delete $form->{$_} }
1708 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1709 shiptostreet shiptozipcode shiptocity shiptocountry
1710 shiptocontact shiptophone shiptofax shiptoemail);
1712 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1713 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1715 map { $form->{$_} = $ref->{$_} } keys %$ref;
1718 # setup last accounts used for this customer
1719 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1721 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1723 JOIN acc_trans ac ON (ac.chart_id = c.id)
1724 JOIN ar a ON (a.id = ac.trans_id)
1725 WHERE a.customer_id = ?
1726 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1727 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1728 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1731 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1732 if ($ref->{category} eq 'I') {
1734 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1736 if ($form->{initial_transdate}) {
1738 qq|SELECT tk.tax_id, t.rate
1740 LEFT JOIN tax t ON tk.tax_id = t.id
1741 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1742 ORDER BY tk.startdate DESC
1744 my ($tax_id, $rate) =
1745 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1746 $form->{initial_transdate});
1747 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1750 if ($ref->{category} eq 'A') {
1751 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1755 $form->{rowcount} = $i if ($i && !$form->{type});
1758 $main::lxdebug->leave_sub();
1762 $main::lxdebug->enter_sub();
1764 my ($self, $myconfig, $form) = @_;
1766 # connect to database
1767 my $dbh = $form->get_standard_dbh;
1769 my $i = $form->{rowcount};
1771 my $where = qq|NOT p.obsolete = '1'|;
1774 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1775 my ($table, $field) = split m/\./, $column;
1776 next if !$form->{"${field}_${i}"};
1777 $where .= qq| AND lower(${column}) ILIKE ?|;
1778 push @values, '%' . $form->{"${field}_${i}"} . '%';
1781 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1782 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1783 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1784 push @values, $form->{"partnumber_$i"};
1787 # Search for part ID overrides all other criteria.
1788 if ($form->{"id_${i}"}) {
1789 $where = qq|p.id = ?|;
1790 @values = ($form->{"id_${i}"});
1793 if ($form->{"description_$i"}) {
1794 $where .= qq| ORDER BY p.description|;
1796 $where .= qq| ORDER BY p.partnumber|;
1800 if ($form->{type} eq "invoice") {
1802 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1803 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1807 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1811 my $taxzone_id = $form->{taxzone_id} * 1;
1812 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1816 p.id, p.partnumber, p.description, p.sellprice,
1817 p.listprice, p.inventory_accno_id, p.lastcost,
1819 c1.accno AS inventory_accno,
1820 c1.new_chart_id AS inventory_new_chart,
1821 date($transdate) - c1.valid_from AS inventory_valid,
1823 c2.accno AS income_accno,
1824 c2.new_chart_id AS income_new_chart,
1825 date($transdate) - c2.valid_from AS income_valid,
1827 c3.accno AS expense_accno,
1828 c3.new_chart_id AS expense_new_chart,
1829 date($transdate) - c3.valid_from AS expense_valid,
1831 p.unit, p.assembly, p.bin, p.onhand,
1832 p.notes AS partnotes, p.notes AS longdescription,
1833 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1836 pfac.factor AS price_factor,
1841 LEFT JOIN chart c1 ON
1842 ((SELECT inventory_accno_id
1843 FROM buchungsgruppen
1844 WHERE id = p.buchungsgruppen_id) = c1.id)
1845 LEFT JOIN chart c2 ON
1846 ((SELECT income_accno_id_${taxzone_id}
1847 FROM buchungsgruppen
1848 WHERE id = p.buchungsgruppen_id) = c2.id)
1849 LEFT JOIN chart c3 ON
1850 ((SELECT expense_accno_id_${taxzone_id}
1851 FROM buchungsgruppen
1852 WHERE id = p.buchungsgruppen_id) = c3.id)
1853 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1854 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1856 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1858 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1860 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1861 [ qq|SELECT tr.translation, tr.longdescription
1863 WHERE tr.language_id IN
1866 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1869 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1871 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1873 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1874 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1875 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1876 if (!$ref->{inventory_accno_id}) {
1877 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1879 delete($ref->{inventory_accno_id});
1881 foreach my $type (qw(inventory income expense)) {
1882 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1884 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1887 ($ref->{"${type}_accno"},
1888 $ref->{"${type}_new_chart"},
1889 $ref->{"${type}_valid"})
1890 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1894 if ($form->{payment_id} eq "") {
1895 $form->{payment_id} = $form->{part_payment_id};
1898 # get tax rates and description
1899 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1901 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1903 LEFT JOIN chart c ON (c.id = t.chart_id)
1907 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1909 ORDER BY startdate DESC
1912 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1913 my $stw = $dbh->prepare($query);
1914 $stw->execute(@values) || $form->dberror($query);
1916 $ref->{taxaccounts} = "";
1918 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1920 # if ($customertax{$ref->{accno}})
1921 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1925 $ref->{taxaccounts} .= "$ptr->{accno} ";
1927 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1928 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1929 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1930 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1931 $form->{taxaccounts} .= "$ptr->{accno} ";
1937 chop $ref->{taxaccounts};
1939 if ($form->{language_id}) {
1940 for my $spec (@translation_queries) {
1941 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1942 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1943 next unless $translation;
1944 $ref->{description} = $translation;
1945 $ref->{longdescription} = $longdescription;
1950 $ref->{onhand} *= 1;
1952 push @{ $form->{item_list} }, $ref;
1955 $_->[1]->finish for @translation_queries;
1957 foreach my $item (@{ $form->{item_list} }) {
1958 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1959 trans_id => $item->{id},
1963 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1966 $main::lxdebug->leave_sub();
1969 ##########################
1970 # get pricegroups from database
1971 # build up selected pricegroup
1972 # if an exchange rate - change price
1975 sub get_pricegroups_for_parts {
1977 $main::lxdebug->enter_sub();
1979 my ($self, $myconfig, $form) = @_;
1981 my $dbh = $form->get_standard_dbh;
1983 $form->{"PRICES"} = {};
1987 my $all_units = AM->retrieve_units($myconfig, $form);
1988 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1989 $form->{"PRICES"}{$i} = [];
1991 $id = $form->{"id_$i"};
1993 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1994 $id = $form->{"new_id_$i"};
1997 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1999 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2001 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2002 my $sellprice = $form->{"sellprice_$i"};
2003 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2004 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2005 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2007 my $price_new = $form->{"price_new_$i"};
2008 my $price_old = $form->{"price_old_$i"};
2010 if (!$form->{"unit_old_$i"}) {
2011 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2012 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2013 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2014 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2017 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2018 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2019 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2021 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2022 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2023 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2024 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2025 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2026 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2027 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2032 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2033 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2034 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2035 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2036 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2040 if (!$form->{"basefactor_$i"}) {
2041 $form->{"basefactor_$i"} = 1;
2047 sellprice AS default_sellprice,
2050 'selected' AS selected
2056 parts.sellprice AS default_sellprice,
2057 pricegroup.pricegroup,
2061 LEFT JOIN parts ON parts.id = parts_id
2062 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2064 ORDER BY pricegroup|;
2065 my @values = (conv_i($id), conv_i($id));
2066 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2068 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2070 $pkr->{selected} = '';
2072 # if there is an exchange rate change price
2073 if (($form->{exchangerate} * 1) != 0) {
2074 $pkr->{price} /= $form->{exchangerate};
2077 $pkr->{price} *= $form->{"basefactor_$i"};
2078 $pkr->{price} *= $basefactor;
2079 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2081 if ($selectedpricegroup_id eq undef) {
2082 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2083 # Case A: open old invoice, no pricegroup selected
2084 # Case B: add new article to invoice, no pricegroup selected
2086 # to distinguish case A and B the variable pricegroup_id_$i is used
2087 # for new articles this variable isn't defined, for loaded articles it is
2088 # sellprice can't be used, as it already has 0,00 set
2090 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2092 $pkr->{selected} = ' selected';
2094 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2095 and not defined $form->{"pricegroup_id_$i"}
2096 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2097 # for the case where pricegroup prices haven't been set
2099 # Case B: use default pricegroup of customer
2101 $pkr->{selected} = ' selected'; # unless $form->{selected};
2103 # no customer pricesgroup set
2104 if ($pkr->{price} == $pkr->{default_sellprice}) {
2106 $pkr->{price} = $form->{"sellprice_$i"};
2110 # this sub should not set anything and only return. --sschoeling, 20090506
2111 # is this correct? put in again... -- grichardson 20110119
2112 $form->{"sellprice_$i"} = $pkr->{price};
2115 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2116 $pkr->{price} = $form->{"sellprice_$i"};
2117 $pkr->{selected} = ' selected';
2121 # existing article: pricegroup or price changed
2122 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2123 if ($selectedpricegroup_id ne $pricegroup_old) {
2124 # pricegroup has changed
2125 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2126 $pkr->{selected} = ' selected';
2128 } elsif ( ($form->parse_amount($myconfig, $price_new)
2129 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2130 and ($price_new ne 0) and defined $price_new) {
2131 # sellprice has changed
2132 # when loading existing invoices $price_new is NULL
2133 if ($pkr->{pricegroup_id} == 0) {
2134 $pkr->{price} = $form->{"sellprice_$i"};
2135 $pkr->{selected} = ' selected';
2137 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2138 # neither sellprice nor pricegroup changed
2139 $pkr->{selected} = ' selected';
2140 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2141 # $pkr->{price} = $form->{"sellprice_$i"};
2143 $pkr->{price} = $form->{"sellprice_$i"};
2147 push @{ $form->{PRICES}{$i} }, $pkr;
2150 $form->{"basefactor_$i"} *= $basefactor;
2157 $main::lxdebug->leave_sub();
2161 $main::lxdebug->enter_sub();
2163 my ($self, $myconfig, $form, $table) = @_;
2165 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2167 # make sure there's no funny stuff in $table
2168 # ToDO: die when this happens and throw an error
2169 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2171 my $dbh = $form->get_standard_dbh;
2173 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2174 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2176 $main::lxdebug->leave_sub();
2182 $main::lxdebug->enter_sub();
2184 my ($self, $myconfig, $form, $table, $id) = @_;
2186 $main::lxdebug->leave_sub() and return 0 unless ($id);
2188 # make sure there's no funny stuff in $table
2189 # ToDO: die when this happens and throw an error
2190 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2192 my $dbh = $form->get_standard_dbh;
2194 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2195 my ($result) = selectrow_query($form, $dbh, $query, $id);
2197 $main::lxdebug->leave_sub();
2202 sub get_standard_accno_current_assets {
2203 $main::lxdebug->enter_sub();
2205 my ($self, $myconfig, $form) = @_;
2207 my $dbh = $form->get_standard_dbh;
2209 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2210 my ($result) = selectrow_query($form, $dbh, $query);
2212 $main::lxdebug->leave_sub();