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,
456 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
457 WHERE (ct.id = ?) $where
460 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
462 # remove id and taxincluded before copy back
463 delete @$ref{qw(id taxincluded)};
465 @wanted_vars = grep({ $_ } @wanted_vars);
466 if (scalar(@wanted_vars) > 0) {
468 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
469 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
472 map { $form->{$_} = $ref->{$_} } keys %$ref;
474 # remove any trailing whitespace
475 $form->{currency} =~ s/\s*$// if ($form->{currency});
477 if ($form->{delivery_customer_id}) {
479 qq|SELECT *, notes as customernotes
483 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
485 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
488 if ($form->{delivery_vendor_id}) {
490 qq|SELECT *, notes as customernotes
494 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
496 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
499 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
501 'trans_id' => $form->{customer_id});
502 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
504 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
505 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
506 'language_id' => $language_id,
507 'allow_fallback' => 1);
510 $main::lxdebug->leave_sub();
514 $main::lxdebug->enter_sub();
516 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
518 # connect to database, turn off autocommit
519 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
521 my ($query, $sth, $null, $project_id, @values);
522 my $exchangerate = 0;
524 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
527 if (!$form->{employee_id}) {
528 $form->get_employee($dbh);
531 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
532 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
533 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
534 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
535 if (!$form->{department_id}){
536 ($null, $form->{department_id}) = split(/--/, $form->{department});
539 my $all_units = AM->retrieve_units($myconfig, $form);
541 if (!$payments_only) {
543 &reverse_invoice($dbh, $form);
546 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
547 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
549 $query = qq|SELECT nextval('glid')|;
550 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
552 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
553 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
555 if (!$form->{invnumber}) {
557 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
558 "cnnumber" : "invnumber", $dbh);
563 my ($netamount, $invoicediff) = (0, 0);
564 my ($amount, $linetotal, $lastincomeaccno);
566 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
567 my $defaultcurrency = (split m/:/, $currencies)[0];
569 if ($form->{currency} eq $defaultcurrency) {
570 $form->{exchangerate} = 1;
572 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
575 $form->{exchangerate} =
578 : $form->parse_amount($myconfig, $form->{exchangerate});
580 $form->{expense_inventory} = "";
584 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
585 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
588 $form->{amount} = {};
589 $form->{amount_cogs} = {};
591 foreach my $i (1 .. $form->{rowcount}) {
592 if ($form->{type} eq "credit_note") {
593 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
594 $form->{shipped} = 1;
596 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
601 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
602 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
603 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
605 if ($form->{storno}) {
606 $form->{"qty_$i"} *= -1;
609 if ($form->{"id_$i"}) {
612 if (defined($baseunits{$form->{"id_$i"}})) {
613 $item_unit = $baseunits{$form->{"id_$i"}};
616 $query = qq|SELECT unit FROM parts WHERE id = ?|;
617 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
618 $baseunits{$form->{"id_$i"}} = $item_unit;
621 if (defined($all_units->{$item_unit}->{factor})
622 && ($all_units->{$item_unit}->{factor} ne '')
623 && ($all_units->{$item_unit}->{factor} != 0)) {
624 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
628 $baseqty = $form->{"qty_$i"} * $basefactor;
630 my ($allocated, $taxrate) = (0, 0);
634 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
636 # keep entered selling price
638 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
640 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
642 my $decimalplaces = ($dec > 2) ? $dec : 2;
644 # undo discount formatting
645 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
648 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
650 # round linetotal to 2 decimal places
651 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
652 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
654 if ($form->{taxincluded}) {
655 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
656 $form->{"sellprice_$i"} =
657 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
659 $taxamount = $linetotal * $taxrate;
662 $netamount += $linetotal;
664 if ($taxamount != 0) {
666 $form->{amount}{ $form->{id} }{$_} +=
667 $taxamount * $form->{"${_}_rate"} / $taxrate
668 } split(/ /, $form->{"taxaccounts_$i"});
671 # add amount to income, $form->{amount}{trans_id}{accno}
672 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
674 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
675 $linetotal = $form->round_amount($linetotal, 2);
677 # this is the difference from the inventory
678 $invoicediff += ($amount - $linetotal);
680 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
683 $lastincomeaccno = $form->{"income_accno_$i"};
685 # adjust and round sellprice
686 $form->{"sellprice_$i"} =
687 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
690 next if $payments_only;
692 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
694 if ($form->{"assembly_$i"}) {
695 # record assembly item as allocated
696 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
699 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
703 # get pricegroup_id and save it
704 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
707 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
709 # save detail record in invoice table
711 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
712 sellprice, fxsellprice, discount, allocated, assemblyitem,
713 unit, deliverydate, project_id, serialnumber, pricegroup_id,
714 ordnumber, transdate, cusordnumber, base_qty, subtotal,
715 marge_percent, marge_total, lastcost,
716 price_factor_id, price_factor, marge_price_factor)
717 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
718 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
720 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
721 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
722 $form->{"sellprice_$i"}, $fxsellprice,
723 $form->{"discount_$i"}, $allocated, 'f',
724 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
725 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
726 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
727 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
728 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
729 $form->{"lastcost_$i"},
730 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
731 conv_i($form->{"marge_price_factor_$i"}));
732 do_query($form, $dbh, $query, @values);
734 CVar->save_custom_variables(module => 'IC',
735 sub_module => 'invoice',
736 trans_id => $invoice_id,
737 configs => $ic_cvar_configs,
739 name_prefix => 'ic_',
740 name_postfix => "_$i",
745 # total payments, don't move we need it here
746 for my $i (1 .. $form->{paidaccounts}) {
747 if ($form->{type} eq "credit_note") {
748 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
750 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
752 $form->{paid} += $form->{"paid_$i"};
753 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
756 my ($tax, $diff) = (0, 0);
758 $netamount = $form->round_amount($netamount, 2);
760 # figure out rounding errors for total amount vs netamount + taxes
761 if ($form->{taxincluded}) {
763 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
764 $diff += $amount - $netamount * $form->{exchangerate};
765 $netamount = $amount;
767 foreach my $item (split(/ /, $form->{taxaccounts})) {
768 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
769 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
770 $tax += $form->{amount}{ $form->{id} }{$item};
771 $netamount -= $form->{amount}{ $form->{id} }{$item};
774 $invoicediff += $diff;
775 ######## this only applies to tax included
776 if ($lastincomeaccno) {
777 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
781 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
782 $diff = $amount - $netamount * $form->{exchangerate};
783 $netamount = $amount;
784 foreach my $item (split(/ /, $form->{taxaccounts})) {
785 $form->{amount}{ $form->{id} }{$item} =
786 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
789 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
792 $amount - $form->{amount}{ $form->{id} }{$item} *
793 $form->{exchangerate};
794 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
795 $tax += $form->{amount}{ $form->{id} }{$item};
799 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
801 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
804 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
806 # update exchangerate
807 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
808 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
809 $form->{exchangerate}, 0);
812 $project_id = conv_i($form->{"globalproject_id"});
814 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
815 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
816 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
818 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
820 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
822 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
823 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
824 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
825 do_query($form, $dbh, $query, @values);
826 $form->{amount_cogs}{$trans_id}{$accno} = 0;
830 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
831 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
833 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
835 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
836 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
837 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
838 do_query($form, $dbh, $query, @values);
843 foreach my $trans_id (keys %{ $form->{amount} }) {
844 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
845 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
847 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
849 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
851 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
852 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
853 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
854 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
855 do_query($form, $dbh, $query, @values);
856 $form->{amount}{$trans_id}{$accno} = 0;
860 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
861 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
863 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
865 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
866 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
867 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
868 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
869 do_query($form, $dbh, $query, @values);
874 # deduct payment differences from diff
875 for my $i (1 .. $form->{paidaccounts}) {
876 if ($form->{"paid_$i"} != 0) {
878 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
879 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
883 # record payments and offsetting AR
884 if (!$form->{storno}) {
885 for my $i (1 .. $form->{paidaccounts}) {
887 if ($form->{"acc_trans_id_$i"}
889 && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
893 next if ($form->{"paid_$i"} == 0);
895 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
896 $form->{"datepaid_$i"} = $form->{invdate}
897 unless ($form->{"datepaid_$i"});
898 $form->{datepaid} = $form->{"datepaid_$i"};
902 if ($form->{currency} eq $defaultcurrency) {
903 $form->{"exchangerate_$i"} = 1;
905 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
906 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
910 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
912 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
914 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
915 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
916 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
917 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
918 do_query($form, $dbh, $query, @values);
922 $form->{"paid_$i"} *= -1;
923 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
926 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
927 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
928 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
929 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
930 $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
931 do_query($form, $dbh, $query, @values);
933 # exchangerate difference
934 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
935 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
939 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
940 $form->{"exchangerate_$i"};
942 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
945 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
951 # update exchange rate
952 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
953 $form->update_exchangerate($dbh, $form->{currency},
954 $form->{"datepaid_$i"},
955 $form->{"exchangerate_$i"}, 0);
959 } else { # if (!$form->{storno})
960 $form->{marge_total} *= -1;
963 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
965 if ($payments_only) {
966 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
967 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
969 $dbh->commit if !$provided_dbh;
971 $main::lxdebug->leave_sub();
975 # record exchange rate differences and gains/losses
976 foreach my $accno (keys %{ $form->{fx} }) {
977 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
979 ($form->{fx}{$accno}{$transdate} =
980 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
985 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
986 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
987 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
988 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
989 do_query($form, $dbh, $query, @values);
994 $amount = $netamount + $tax;
997 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
999 $query = qq|UPDATE ar set
1000 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1001 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1002 amount = ?, netamount = ?, paid = ?,
1003 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1004 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1005 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1006 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1007 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1008 cp_id = ?, marge_total = ?, marge_percent = ?,
1009 globalproject_id = ?, delivery_customer_id = ?,
1010 transaction_description = ?, delivery_vendor_id = ?,
1011 donumber = ?, invnumber_for_credit_note = ?
1013 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1014 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1015 $amount, $netamount, $form->{"paid"},
1016 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1017 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1018 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1019 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1020 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1021 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1022 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1023 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1024 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1025 conv_i($form->{"id"}));
1026 do_query($form, $dbh, $query, @values);
1029 if ($form->{storno}) {
1032 paid = paid + amount,
1034 intnotes = ? || intnotes
1036 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1037 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1041 $form->{name} = $form->{customer};
1042 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1044 if (!$form->{shipto_id}) {
1045 $form->add_shipto($dbh, $form->{id}, "AR");
1048 # save printed, emailed and queued
1049 $form->save_status($dbh);
1051 Common::webdav_folder($form);
1053 # Link this record to the records it was created from.
1054 RecordLinks->create_links('dbh' => $dbh,
1056 'from_table' => 'oe',
1057 'from_ids' => $form->{convert_from_oe_ids},
1059 'to_id' => $form->{id},
1061 delete $form->{convert_from_oe_ids};
1063 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1065 if (scalar @convert_from_do_ids) {
1066 DO->close_orders('dbh' => $dbh,
1067 'ids' => \@convert_from_do_ids);
1069 RecordLinks->create_links('dbh' => $dbh,
1071 'from_table' => 'delivery_orders',
1072 'from_ids' => \@convert_from_do_ids,
1074 'to_id' => $form->{id},
1077 delete $form->{convert_from_do_ids};
1079 ARAP->close_orders_if_billed('dbh' => $dbh,
1080 'arap_id' => $form->{id},
1084 $dbh->commit if !$provided_dbh;
1086 $main::lxdebug->leave_sub();
1091 sub _delete_payments {
1092 $main::lxdebug->enter_sub();
1094 my ($self, $form, $dbh) = @_;
1096 my @delete_acc_trans_ids;
1098 # Delete old payment entries from acc_trans.
1100 qq|SELECT acc_trans_id
1102 WHERE (trans_id = ?) AND fx_transaction
1106 SELECT at.acc_trans_id
1108 LEFT JOIN chart c ON (at.chart_id = c.id)
1109 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1110 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1113 qq|SELECT at.acc_trans_id
1115 LEFT JOIN chart c ON (at.chart_id = c.id)
1116 WHERE (trans_id = ?)
1117 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1118 ORDER BY at.acc_trans_id
1120 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1122 if (@delete_acc_trans_ids) {
1123 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1124 do_query($form, $dbh, $query);
1127 $main::lxdebug->leave_sub();
1131 $main::lxdebug->enter_sub();
1133 my ($self, $myconfig, $form, $locale) = @_;
1135 # connect to database, turn off autocommit
1136 my $dbh = $form->get_standard_dbh;
1139 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1141 $old_form = save_form();
1143 # Delete all entries in acc_trans from prior payments.
1144 if ($::lx_office_conf{features}->{payments_changeable} != 0) {
1145 $self->_delete_payments($form, $dbh);
1148 # Save the new payments the user made before cleaning up $form.
1149 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 };
1151 # Clean up $form so that old content won't tamper the results.
1152 %keep_vars = map { $_, 1 } qw(login password id);
1153 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1155 # Retrieve the invoice from the database.
1156 $self->retrieve_invoice($myconfig, $form);
1158 # Set up the content of $form in the way that IS::post_invoice() expects.
1159 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1161 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1162 $item = $form->{invoice_details}->[$row - 1];
1164 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1166 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1169 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1171 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1173 # Restore the payment options from the user input.
1174 map { $form->{$_} = $payments{$_} } keys %payments;
1176 # Get the AR accno (which is normally done by Form::create_links()).
1180 LEFT JOIN chart c ON (at.chart_id = c.id)
1181 WHERE (trans_id = ?)
1182 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1183 ORDER BY at.acc_trans_id
1186 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1188 # Post the new payments.
1189 $self->post_invoice($myconfig, $form, $dbh, 1);
1191 restore_form($old_form);
1193 my $rc = $dbh->commit();
1195 $main::lxdebug->leave_sub();
1200 sub process_assembly {
1201 $main::lxdebug->enter_sub();
1203 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1206 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1207 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1209 JOIN parts p ON (a.parts_id = p.id)
1211 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1213 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1217 $ref->{inventory_accno_id} *= 1;
1218 $ref->{expense_accno_id} *= 1;
1220 # multiply by number of assemblies
1221 $ref->{qty} *= $totalqty;
1223 if ($ref->{assembly}) {
1224 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1227 if ($ref->{inventory_accno_id}) {
1228 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1232 # save detail record for individual assembly item in invoice table
1234 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1235 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1236 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1237 do_query($form, $dbh, $query, @values);
1243 $main::lxdebug->leave_sub();
1247 $main::lxdebug->enter_sub();
1249 # adjust allocated in table invoice according to FIFO princicple
1250 # for a certain part with part_id $id
1252 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1256 $form->{taxzone_id} *=1;
1257 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1258 my $taxzone_id = $form->{"taxzone_id"} * 1;
1260 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1261 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1262 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1263 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1264 FROM invoice i, parts p
1265 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1266 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1267 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1268 WHERE (i.parts_id = p.id)
1269 AND (i.parts_id = ?)
1270 AND ((i.base_qty + i.allocated) < 0)
1272 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1277 # all invoice entries of an example part:
1279 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1280 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1281 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1282 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1283 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1284 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1285 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1287 # 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
1288 # and all parts have been allocated
1290 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1291 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1293 # in this example there are still 4 unsold articles
1296 # search all invoice entries for the part in question, adjusting "allocated"
1297 # until the total number of sold parts has been reached
1299 # ORDER BY trans_id ensures FIFO
1302 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1303 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1307 # update allocated in invoice
1308 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1310 # total expenses and inventory
1311 # sellprice is the cost of the item
1312 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1314 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1315 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1316 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1318 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1319 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1320 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1322 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1323 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1329 last if (($totalqty -= $qty) <= 0);
1334 $main::lxdebug->leave_sub();
1339 sub reverse_invoice {
1340 $main::lxdebug->enter_sub();
1342 my ($dbh, $form) = @_;
1344 # reverse inventory items
1346 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1348 JOIN parts p ON (i.parts_id = p.id)
1349 WHERE i.trans_id = ?|;
1350 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1352 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1354 if ($ref->{inventory_accno_id}) {
1355 # de-allocated purchases
1357 qq|SELECT i.id, i.trans_id, i.allocated
1359 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1360 ORDER BY i.trans_id DESC|;
1361 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1363 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1364 my $qty = $ref->{qty};
1365 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1366 $qty = $inhref->{allocated};
1370 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1372 last if (($ref->{qty} -= $qty) <= 0);
1381 my @values = (conv_i($form->{id}));
1382 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1383 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1384 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1386 $main::lxdebug->leave_sub();
1389 sub delete_invoice {
1390 $main::lxdebug->enter_sub();
1392 my ($self, $myconfig, $form) = @_;
1394 # connect to database
1395 my $dbh = $form->get_standard_dbh;
1398 &reverse_invoice($dbh, $form);
1400 my @values = (conv_i($form->{id}));
1402 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1403 # zurückgesetzt werden. Vgl:
1404 # id | storno | storno_id | paid | amount
1405 #----+--------+-----------+---------+-----------
1406 # 18 | f | | 0.00000 | 119.00000
1408 # 18 | t | | 119.00000 | 119.00000
1410 if($form->{storno}){
1411 # storno_id auslesen und korrigieren
1412 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1413 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1417 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1419 # delete spool files
1420 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1422 # delete status entries
1423 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1425 my $rc = $dbh->commit;
1428 my $spool = $::lx_office_conf{paths}->{spool};
1429 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1432 $main::lxdebug->leave_sub();
1437 sub retrieve_invoice {
1438 $main::lxdebug->enter_sub();
1440 my ($self, $myconfig, $form) = @_;
1442 # connect to database
1443 my $dbh = $form->get_standard_dbh;
1445 my ($sth, $ref, $query);
1447 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1451 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1452 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1453 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1454 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1455 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1456 d.curr AS currencies
1460 $ref = selectfirst_hashref_query($form, $dbh, $query);
1461 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1464 my $id = conv_i($form->{id});
1467 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1471 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1472 a.orddate, a.quodate, a.globalproject_id,
1473 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1474 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1475 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1476 a.employee_id, a.salesman_id, a.payment_id,
1477 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1478 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1479 a.marge_total, a.marge_percent,
1482 LEFT JOIN employee e ON (e.id = a.employee_id)
1484 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1485 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1487 # remove any trailing whitespace
1488 $form->{currency} =~ s/\s*$//;
1490 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1493 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1494 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1496 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1498 foreach my $vc (qw(customer vendor)) {
1499 next if !$form->{"delivery_${vc}_id"};
1500 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1503 # get printed, emailed
1504 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1505 $sth = prepare_execute_query($form, $dbh, $query, $id);
1507 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1508 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1509 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1510 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1513 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1515 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1516 : $form->{invdate} ? $dbh->quote($form->{invdate})
1520 my $taxzone_id = $form->{taxzone_id} *= 1;
1521 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1523 # retrieve individual items
1526 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1527 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1528 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1531 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1532 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1533 i.price_factor_id, i.price_factor, i.marge_price_factor,
1534 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1535 pr.projectnumber, pg.partsgroup, prg.pricegroup
1538 LEFT JOIN parts p ON (i.parts_id = p.id)
1539 LEFT JOIN project pr ON (i.project_id = pr.id)
1540 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1541 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1543 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1544 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1545 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1547 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1549 $sth = prepare_execute_query($form, $dbh, $query, $id);
1551 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1552 # Retrieve custom variables.
1553 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1555 sub_module => 'invoice',
1556 trans_id => $ref->{invoice_id},
1558 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1559 delete $ref->{invoice_id};
1561 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1562 delete($ref->{"part_inventory_accno_id"});
1564 foreach my $type (qw(inventory income expense)) {
1565 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1566 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1567 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1571 # get tax rates and description
1572 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1574 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1575 LEFT JOIN chart c ON (c.id = t.chart_id)
1577 (SELECT tk.tax_id FROM taxkeys tk
1578 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1579 AND startdate <= date($transdate)
1580 ORDER BY startdate DESC LIMIT 1)
1582 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1583 $ref->{taxaccounts} = "";
1585 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1587 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1591 $ref->{taxaccounts} .= "$ptr->{accno} ";
1593 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1594 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1595 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1596 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1597 $form->{taxaccounts} .= "$ptr->{accno} ";
1602 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1604 chop $ref->{taxaccounts};
1605 push @{ $form->{invoice_details} }, $ref;
1610 Common::webdav_folder($form);
1613 my $rc = $dbh->commit;
1615 $main::lxdebug->leave_sub();
1621 $main::lxdebug->enter_sub();
1623 my ($self, $myconfig, $form) = @_;
1625 # connect to database
1626 my $dbh = $form->get_standard_dbh;
1628 my $dateformat = $myconfig->{dateformat};
1629 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1631 my (@values, $duedate, $ref, $query);
1633 if ($form->{invdate}) {
1634 $duedate = "to_date(?, '$dateformat')";
1635 push @values, $form->{invdate};
1637 $duedate = "current_date";
1640 my $cid = conv_i($form->{customer_id});
1643 if ($form->{payment_id}) {
1644 $payment_id = "(pt.id = ?) OR";
1645 push @values, conv_i($form->{payment_id});
1651 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1652 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1653 c.street, c.zipcode, c.city, c.country,
1654 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1655 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1656 b.discount AS tradediscount, b.description AS business
1658 LEFT JOIN business b ON (b.id = c.business_id)
1659 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1662 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1664 delete $ref->{salesman_id} if !$ref->{salesman_id};
1666 map { $form->{$_} = $ref->{$_} } keys %$ref;
1668 # remove any trailing whitespace
1669 $form->{curr} =~ s/\s*$//;
1671 # use customer currency if not empty
1672 $form->{currency} = $form->{curr} if $form->{curr};
1675 qq|SELECT sum(amount - paid) AS dunning_amount
1677 WHERE (paid < amount)
1678 AND (customer_id = ?)
1679 AND (dunning_config_id IS NOT NULL)|;
1680 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1681 map { $form->{$_} = $ref->{$_} } keys %$ref;
1684 qq|SELECT dnn.dunning_description AS max_dunning_level
1685 FROM dunning_config dnn
1686 WHERE id IN (SELECT dunning_config_id
1688 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1689 ORDER BY dunning_level DESC LIMIT 1|;
1690 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1691 map { $form->{$_} = $ref->{$_} } keys %$ref;
1693 $form->{creditremaining} = $form->{creditlimit};
1694 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1695 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1696 $form->{creditremaining} -= $value;
1700 (SELECT e.buy FROM exchangerate e
1701 WHERE e.curr = o.curr
1702 AND e.transdate = o.transdate)
1704 WHERE o.customer_id = ?
1705 AND o.quotation = '0'
1706 AND o.closed = '0'|;
1707 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1709 while (my ($amount, $exch) = $sth->fetchrow_array) {
1710 $exch = 1 unless $exch;
1711 $form->{creditremaining} -= $amount * $exch;
1715 # get shipto if we did not converted an order or invoice
1716 if (!$form->{shipto}) {
1717 map { delete $form->{$_} }
1718 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1719 shiptostreet shiptozipcode shiptocity shiptocountry
1720 shiptocontact shiptophone shiptofax shiptoemail);
1722 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1723 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1725 map { $form->{$_} = $ref->{$_} } keys %$ref;
1728 # setup last accounts used for this customer
1729 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1731 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1733 JOIN acc_trans ac ON (ac.chart_id = c.id)
1734 JOIN ar a ON (a.id = ac.trans_id)
1735 WHERE a.customer_id = ?
1736 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1737 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1738 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1741 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1742 if ($ref->{category} eq 'I') {
1744 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1746 if ($form->{initial_transdate}) {
1748 qq|SELECT tk.tax_id, t.rate
1750 LEFT JOIN tax t ON tk.tax_id = t.id
1751 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1752 ORDER BY tk.startdate DESC
1754 my ($tax_id, $rate) =
1755 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1756 $form->{initial_transdate});
1757 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1760 if ($ref->{category} eq 'A') {
1761 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1765 $form->{rowcount} = $i if ($i && !$form->{type});
1768 $main::lxdebug->leave_sub();
1772 $main::lxdebug->enter_sub();
1774 my ($self, $myconfig, $form) = @_;
1776 # connect to database
1777 my $dbh = $form->get_standard_dbh;
1779 my $i = $form->{rowcount};
1781 my $where = qq|NOT p.obsolete = '1'|;
1784 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1785 my ($table, $field) = split m/\./, $column;
1786 next if !$form->{"${field}_${i}"};
1787 $where .= qq| AND lower(${column}) ILIKE ?|;
1788 push @values, '%' . $form->{"${field}_${i}"} . '%';
1791 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1792 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1793 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1794 push @values, $form->{"partnumber_$i"};
1797 # Search for part ID overrides all other criteria.
1798 if ($form->{"id_${i}"}) {
1799 $where = qq|p.id = ?|;
1800 @values = ($form->{"id_${i}"});
1803 if ($form->{"description_$i"}) {
1804 $where .= qq| ORDER BY p.description|;
1806 $where .= qq| ORDER BY p.partnumber|;
1810 if ($form->{type} eq "invoice") {
1812 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1813 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1817 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1821 my $taxzone_id = $form->{taxzone_id} * 1;
1822 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1826 p.id, p.partnumber, p.description, p.sellprice,
1827 p.listprice, p.inventory_accno_id, p.lastcost,
1829 c1.accno AS inventory_accno,
1830 c1.new_chart_id AS inventory_new_chart,
1831 date($transdate) - c1.valid_from AS inventory_valid,
1833 c2.accno AS income_accno,
1834 c2.new_chart_id AS income_new_chart,
1835 date($transdate) - c2.valid_from AS income_valid,
1837 c3.accno AS expense_accno,
1838 c3.new_chart_id AS expense_new_chart,
1839 date($transdate) - c3.valid_from AS expense_valid,
1841 p.unit, p.assembly, p.bin, p.onhand,
1842 p.notes AS partnotes, p.notes AS longdescription,
1843 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1846 pfac.factor AS price_factor,
1851 LEFT JOIN chart c1 ON
1852 ((SELECT inventory_accno_id
1853 FROM buchungsgruppen
1854 WHERE id = p.buchungsgruppen_id) = c1.id)
1855 LEFT JOIN chart c2 ON
1856 ((SELECT income_accno_id_${taxzone_id}
1857 FROM buchungsgruppen
1858 WHERE id = p.buchungsgruppen_id) = c2.id)
1859 LEFT JOIN chart c3 ON
1860 ((SELECT expense_accno_id_${taxzone_id}
1861 FROM buchungsgruppen
1862 WHERE id = p.buchungsgruppen_id) = c3.id)
1863 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1864 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1866 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1868 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1870 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1871 [ qq|SELECT tr.translation, tr.longdescription
1873 WHERE tr.language_id IN
1876 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1879 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1881 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1883 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1884 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1885 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1886 if (!$ref->{inventory_accno_id}) {
1887 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1889 delete($ref->{inventory_accno_id});
1891 foreach my $type (qw(inventory income expense)) {
1892 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1894 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1897 ($ref->{"${type}_accno"},
1898 $ref->{"${type}_new_chart"},
1899 $ref->{"${type}_valid"})
1900 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1904 if ($form->{payment_id} eq "") {
1905 $form->{payment_id} = $form->{part_payment_id};
1908 # get tax rates and description
1909 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1911 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1913 LEFT JOIN chart c ON (c.id = t.chart_id)
1917 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1919 ORDER BY startdate DESC
1922 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1923 my $stw = $dbh->prepare($query);
1924 $stw->execute(@values) || $form->dberror($query);
1926 $ref->{taxaccounts} = "";
1928 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1930 # if ($customertax{$ref->{accno}})
1931 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1935 $ref->{taxaccounts} .= "$ptr->{accno} ";
1937 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1938 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1939 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1940 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1941 $form->{taxaccounts} .= "$ptr->{accno} ";
1947 chop $ref->{taxaccounts};
1949 if ($form->{language_id}) {
1950 for my $spec (@translation_queries) {
1951 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1952 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1953 next unless $translation;
1954 $ref->{description} = $translation;
1955 $ref->{longdescription} = $longdescription;
1960 $ref->{onhand} *= 1;
1962 push @{ $form->{item_list} }, $ref;
1965 $_->[1]->finish for @translation_queries;
1967 foreach my $item (@{ $form->{item_list} }) {
1968 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1969 trans_id => $item->{id},
1973 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1976 $main::lxdebug->leave_sub();
1979 ##########################
1980 # get pricegroups from database
1981 # build up selected pricegroup
1982 # if an exchange rate - change price
1985 sub get_pricegroups_for_parts {
1987 $main::lxdebug->enter_sub();
1989 my ($self, $myconfig, $form) = @_;
1991 my $dbh = $form->get_standard_dbh;
1993 $form->{"PRICES"} = {};
1997 my $all_units = AM->retrieve_units($myconfig, $form);
1998 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1999 $form->{"PRICES"}{$i} = [];
2001 $id = $form->{"id_$i"};
2003 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2004 $id = $form->{"new_id_$i"};
2007 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2009 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2011 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2012 my $sellprice = $form->{"sellprice_$i"};
2013 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2014 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2015 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2017 my $price_new = $form->{"price_new_$i"};
2018 my $price_old = $form->{"price_old_$i"};
2020 if (!$form->{"unit_old_$i"}) {
2021 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2022 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2023 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2024 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2027 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2028 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2029 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2031 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2032 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2033 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2034 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2035 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2036 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2037 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2042 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2043 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2044 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2045 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2046 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2050 if (!$form->{"basefactor_$i"}) {
2051 $form->{"basefactor_$i"} = 1;
2057 sellprice AS default_sellprice,
2060 'selected' AS selected
2066 parts.sellprice AS default_sellprice,
2067 pricegroup.pricegroup,
2071 LEFT JOIN parts ON parts.id = parts_id
2072 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2074 ORDER BY pricegroup|;
2075 my @values = (conv_i($id), conv_i($id));
2076 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2078 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2080 $pkr->{selected} = '';
2082 # if there is an exchange rate change price
2083 if (($form->{exchangerate} * 1) != 0) {
2084 $pkr->{price} /= $form->{exchangerate};
2087 $pkr->{price} *= $form->{"basefactor_$i"};
2088 $pkr->{price} *= $basefactor;
2089 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2091 if ($selectedpricegroup_id eq undef) {
2092 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2093 # Case A: open old invoice, no pricegroup selected
2094 # Case B: add new article to invoice, no pricegroup selected
2096 # to distinguish case A and B the variable pricegroup_id_$i is used
2097 # for new articles this variable isn't defined, for loaded articles it is
2098 # sellprice can't be used, as it already has 0,00 set
2100 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2102 $pkr->{selected} = ' selected';
2104 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2105 and not defined $form->{"pricegroup_id_$i"}
2106 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2107 # for the case where pricegroup prices haven't been set
2109 # Case B: use default pricegroup of customer
2111 $pkr->{selected} = ' selected'; # unless $form->{selected};
2113 # no customer pricesgroup set
2114 if ($pkr->{price} == $pkr->{default_sellprice}) {
2116 $pkr->{price} = $form->{"sellprice_$i"};
2120 # this sub should not set anything and only return. --sschoeling, 20090506
2121 # is this correct? put in again... -- grichardson 20110119
2122 $form->{"sellprice_$i"} = $pkr->{price};
2125 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2126 $pkr->{price} = $form->{"sellprice_$i"};
2127 $pkr->{selected} = ' selected';
2131 # existing article: pricegroup or price changed
2132 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2133 if ($selectedpricegroup_id ne $pricegroup_old) {
2134 # pricegroup has changed
2135 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2136 $pkr->{selected} = ' selected';
2138 } elsif ( ($form->parse_amount($myconfig, $price_new)
2139 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2140 and ($price_new ne 0) and defined $price_new) {
2141 # sellprice has changed
2142 # when loading existing invoices $price_new is NULL
2143 if ($pkr->{pricegroup_id} == 0) {
2144 $pkr->{price} = $form->{"sellprice_$i"};
2145 $pkr->{selected} = ' selected';
2147 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2148 # neither sellprice nor pricegroup changed
2149 $pkr->{selected} = ' selected';
2150 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2151 # $pkr->{price} = $form->{"sellprice_$i"};
2153 $pkr->{price} = $form->{"sellprice_$i"};
2157 push @{ $form->{PRICES}{$i} }, $pkr;
2160 $form->{"basefactor_$i"} *= $basefactor;
2167 $main::lxdebug->leave_sub();
2171 $main::lxdebug->enter_sub();
2173 my ($self, $myconfig, $form, $table) = @_;
2175 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2177 # make sure there's no funny stuff in $table
2178 # ToDO: die when this happens and throw an error
2179 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2181 my $dbh = $form->get_standard_dbh;
2183 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2184 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2186 $main::lxdebug->leave_sub();
2192 $main::lxdebug->enter_sub();
2194 my ($self, $myconfig, $form, $table, $id) = @_;
2196 $main::lxdebug->leave_sub() and return 0 unless ($id);
2198 # make sure there's no funny stuff in $table
2199 # ToDO: die when this happens and throw an error
2200 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2202 my $dbh = $form->get_standard_dbh;
2204 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2205 my ($result) = selectrow_query($form, $dbh, $query, $id);
2207 $main::lxdebug->leave_sub();
2212 sub get_standard_accno_current_assets {
2213 $main::lxdebug->enter_sub();
2215 my ($self, $myconfig, $form) = @_;
2217 my $dbh = $form->get_standard_dbh;
2219 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2220 my ($result) = selectrow_query($form, $dbh, $query);
2222 $main::lxdebug->leave_sub();