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);
1416 # delete spool files
1417 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1420 qq|DELETE FROM status WHERE trans_id = ?|,
1421 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1422 qq|DELETE FROM ar WHERE id = ?|,
1425 map { do_query($form, $dbh, $_, @values) } @queries;
1427 my $rc = $dbh->commit;
1430 my $spool = $::lx_office_conf{paths}->{spool};
1431 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1434 $main::lxdebug->leave_sub();
1439 sub retrieve_invoice {
1440 $main::lxdebug->enter_sub();
1442 my ($self, $myconfig, $form) = @_;
1444 # connect to database
1445 my $dbh = $form->get_standard_dbh;
1447 my ($sth, $ref, $query);
1449 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1453 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1454 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1455 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1456 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1457 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1458 d.curr AS currencies
1462 $ref = selectfirst_hashref_query($form, $dbh, $query);
1463 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1466 my $id = conv_i($form->{id});
1469 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1473 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1474 a.orddate, a.quodate, a.globalproject_id,
1475 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1476 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1477 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1478 a.employee_id, a.salesman_id, a.payment_id,
1479 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1480 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1481 a.marge_total, a.marge_percent,
1484 LEFT JOIN employee e ON (e.id = a.employee_id)
1486 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1487 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1489 # remove any trailing whitespace
1490 $form->{currency} =~ s/\s*$//;
1492 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1495 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1496 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1498 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1500 foreach my $vc (qw(customer vendor)) {
1501 next if !$form->{"delivery_${vc}_id"};
1502 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1505 # get printed, emailed
1506 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1507 $sth = prepare_execute_query($form, $dbh, $query, $id);
1509 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1510 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1511 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1512 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1515 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1517 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1518 : $form->{invdate} ? $dbh->quote($form->{invdate})
1522 my $taxzone_id = $form->{taxzone_id} *= 1;
1523 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1525 # retrieve individual items
1528 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1529 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1530 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1533 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1534 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1535 i.price_factor_id, i.price_factor, i.marge_price_factor,
1536 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1537 pr.projectnumber, pg.partsgroup, prg.pricegroup
1540 LEFT JOIN parts p ON (i.parts_id = p.id)
1541 LEFT JOIN project pr ON (i.project_id = pr.id)
1542 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1543 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1545 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1546 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1547 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1549 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1551 $sth = prepare_execute_query($form, $dbh, $query, $id);
1553 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1554 # Retrieve custom variables.
1555 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1557 sub_module => 'invoice',
1558 trans_id => $ref->{invoice_id},
1560 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1561 delete $ref->{invoice_id};
1563 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1564 delete($ref->{"part_inventory_accno_id"});
1566 foreach my $type (qw(inventory income expense)) {
1567 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1568 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1569 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1573 # get tax rates and description
1574 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1576 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1577 LEFT JOIN chart c ON (c.id = t.chart_id)
1579 (SELECT tk.tax_id FROM taxkeys tk
1580 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1581 AND startdate <= date($transdate)
1582 ORDER BY startdate DESC LIMIT 1)
1584 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1585 $ref->{taxaccounts} = "";
1587 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1589 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1593 $ref->{taxaccounts} .= "$ptr->{accno} ";
1595 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1596 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1597 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1598 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1599 $form->{taxaccounts} .= "$ptr->{accno} ";
1604 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1606 chop $ref->{taxaccounts};
1607 push @{ $form->{invoice_details} }, $ref;
1612 Common::webdav_folder($form);
1615 my $rc = $dbh->commit;
1617 $main::lxdebug->leave_sub();
1623 $main::lxdebug->enter_sub();
1625 my ($self, $myconfig, $form) = @_;
1627 # connect to database
1628 my $dbh = $form->get_standard_dbh;
1630 my $dateformat = $myconfig->{dateformat};
1631 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1633 my (@values, $duedate, $ref, $query);
1635 if ($form->{invdate}) {
1636 $duedate = "to_date(?, '$dateformat')";
1637 push @values, $form->{invdate};
1639 $duedate = "current_date";
1642 my $cid = conv_i($form->{customer_id});
1645 if ($form->{payment_id}) {
1646 $payment_id = "(pt.id = ?) OR";
1647 push @values, conv_i($form->{payment_id});
1653 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1654 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1655 c.street, c.zipcode, c.city, c.country,
1656 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1657 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1658 b.discount AS tradediscount, b.description AS business
1660 LEFT JOIN business b ON (b.id = c.business_id)
1661 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1664 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1666 delete $ref->{salesman_id} if !$ref->{salesman_id};
1668 map { $form->{$_} = $ref->{$_} } keys %$ref;
1670 # remove any trailing whitespace
1671 $form->{curr} =~ s/\s*$//;
1673 # use customer currency if not empty
1674 $form->{currency} = $form->{curr} if $form->{curr};
1677 qq|SELECT sum(amount - paid) AS dunning_amount
1679 WHERE (paid < amount)
1680 AND (customer_id = ?)
1681 AND (dunning_config_id IS NOT NULL)|;
1682 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1683 map { $form->{$_} = $ref->{$_} } keys %$ref;
1686 qq|SELECT dnn.dunning_description AS max_dunning_level
1687 FROM dunning_config dnn
1688 WHERE id IN (SELECT dunning_config_id
1690 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1691 ORDER BY dunning_level DESC LIMIT 1|;
1692 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1693 map { $form->{$_} = $ref->{$_} } keys %$ref;
1695 $form->{creditremaining} = $form->{creditlimit};
1696 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1697 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1698 $form->{creditremaining} -= $value;
1702 (SELECT e.buy FROM exchangerate e
1703 WHERE e.curr = o.curr
1704 AND e.transdate = o.transdate)
1706 WHERE o.customer_id = ?
1707 AND o.quotation = '0'
1708 AND o.closed = '0'|;
1709 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1711 while (my ($amount, $exch) = $sth->fetchrow_array) {
1712 $exch = 1 unless $exch;
1713 $form->{creditremaining} -= $amount * $exch;
1717 # get shipto if we did not converted an order or invoice
1718 if (!$form->{shipto}) {
1719 map { delete $form->{$_} }
1720 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1721 shiptostreet shiptozipcode shiptocity shiptocountry
1722 shiptocontact shiptophone shiptofax shiptoemail);
1724 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1725 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1727 map { $form->{$_} = $ref->{$_} } keys %$ref;
1730 # setup last accounts used for this customer
1731 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1733 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1735 JOIN acc_trans ac ON (ac.chart_id = c.id)
1736 JOIN ar a ON (a.id = ac.trans_id)
1737 WHERE a.customer_id = ?
1738 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1739 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1740 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1743 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1744 if ($ref->{category} eq 'I') {
1746 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1748 if ($form->{initial_transdate}) {
1750 qq|SELECT tk.tax_id, t.rate
1752 LEFT JOIN tax t ON tk.tax_id = t.id
1753 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1754 ORDER BY tk.startdate DESC
1756 my ($tax_id, $rate) =
1757 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1758 $form->{initial_transdate});
1759 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1762 if ($ref->{category} eq 'A') {
1763 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1767 $form->{rowcount} = $i if ($i && !$form->{type});
1770 $main::lxdebug->leave_sub();
1774 $main::lxdebug->enter_sub();
1776 my ($self, $myconfig, $form) = @_;
1778 # connect to database
1779 my $dbh = $form->get_standard_dbh;
1781 my $i = $form->{rowcount};
1783 my $where = qq|NOT p.obsolete = '1'|;
1786 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1787 my ($table, $field) = split m/\./, $column;
1788 next if !$form->{"${field}_${i}"};
1789 $where .= qq| AND lower(${column}) ILIKE ?|;
1790 push @values, '%' . $form->{"${field}_${i}"} . '%';
1793 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1794 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1795 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1796 push @values, $form->{"partnumber_$i"};
1799 # Search for part ID overrides all other criteria.
1800 if ($form->{"id_${i}"}) {
1801 $where = qq|p.id = ?|;
1802 @values = ($form->{"id_${i}"});
1805 if ($form->{"description_$i"}) {
1806 $where .= qq| ORDER BY p.description|;
1808 $where .= qq| ORDER BY p.partnumber|;
1812 if ($form->{type} eq "invoice") {
1814 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1815 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1819 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1823 my $taxzone_id = $form->{taxzone_id} * 1;
1824 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1828 p.id, p.partnumber, p.description, p.sellprice,
1829 p.listprice, p.inventory_accno_id, p.lastcost,
1831 c1.accno AS inventory_accno,
1832 c1.new_chart_id AS inventory_new_chart,
1833 date($transdate) - c1.valid_from AS inventory_valid,
1835 c2.accno AS income_accno,
1836 c2.new_chart_id AS income_new_chart,
1837 date($transdate) - c2.valid_from AS income_valid,
1839 c3.accno AS expense_accno,
1840 c3.new_chart_id AS expense_new_chart,
1841 date($transdate) - c3.valid_from AS expense_valid,
1843 p.unit, p.assembly, p.bin, p.onhand,
1844 p.notes AS partnotes, p.notes AS longdescription,
1845 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1848 pfac.factor AS price_factor,
1853 LEFT JOIN chart c1 ON
1854 ((SELECT inventory_accno_id
1855 FROM buchungsgruppen
1856 WHERE id = p.buchungsgruppen_id) = c1.id)
1857 LEFT JOIN chart c2 ON
1858 ((SELECT income_accno_id_${taxzone_id}
1859 FROM buchungsgruppen
1860 WHERE id = p.buchungsgruppen_id) = c2.id)
1861 LEFT JOIN chart c3 ON
1862 ((SELECT expense_accno_id_${taxzone_id}
1863 FROM buchungsgruppen
1864 WHERE id = p.buchungsgruppen_id) = c3.id)
1865 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1866 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1868 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1870 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1872 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1873 [ qq|SELECT tr.translation, tr.longdescription
1875 WHERE tr.language_id IN
1878 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1881 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1883 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1885 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1886 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1887 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1888 if (!$ref->{inventory_accno_id}) {
1889 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1891 delete($ref->{inventory_accno_id});
1893 foreach my $type (qw(inventory income expense)) {
1894 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1896 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1899 ($ref->{"${type}_accno"},
1900 $ref->{"${type}_new_chart"},
1901 $ref->{"${type}_valid"})
1902 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1906 if ($form->{payment_id} eq "") {
1907 $form->{payment_id} = $form->{part_payment_id};
1910 # get tax rates and description
1911 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1913 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1915 LEFT JOIN chart c ON (c.id = t.chart_id)
1919 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1921 ORDER BY startdate DESC
1924 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1925 my $stw = $dbh->prepare($query);
1926 $stw->execute(@values) || $form->dberror($query);
1928 $ref->{taxaccounts} = "";
1930 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1932 # if ($customertax{$ref->{accno}})
1933 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1937 $ref->{taxaccounts} .= "$ptr->{accno} ";
1939 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1940 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1941 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1942 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1943 $form->{taxaccounts} .= "$ptr->{accno} ";
1949 chop $ref->{taxaccounts};
1951 if ($form->{language_id}) {
1952 for my $spec (@translation_queries) {
1953 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1954 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1955 next unless $translation;
1956 $ref->{description} = $translation;
1957 $ref->{longdescription} = $longdescription;
1962 $ref->{onhand} *= 1;
1964 push @{ $form->{item_list} }, $ref;
1967 $_->[1]->finish for @translation_queries;
1969 foreach my $item (@{ $form->{item_list} }) {
1970 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1971 trans_id => $item->{id},
1975 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1978 $main::lxdebug->leave_sub();
1981 ##########################
1982 # get pricegroups from database
1983 # build up selected pricegroup
1984 # if an exchange rate - change price
1987 sub get_pricegroups_for_parts {
1989 $main::lxdebug->enter_sub();
1991 my ($self, $myconfig, $form) = @_;
1993 my $dbh = $form->get_standard_dbh;
1995 $form->{"PRICES"} = {};
1999 my $all_units = AM->retrieve_units($myconfig, $form);
2000 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2001 $form->{"PRICES"}{$i} = [];
2003 $id = $form->{"id_$i"};
2005 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2006 $id = $form->{"new_id_$i"};
2009 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2011 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2013 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2014 my $sellprice = $form->{"sellprice_$i"};
2015 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2016 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2017 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2019 my $price_new = $form->{"price_new_$i"};
2020 my $price_old = $form->{"price_old_$i"};
2022 if (!$form->{"unit_old_$i"}) {
2023 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2024 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2025 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2026 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2029 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2030 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2031 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2033 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2034 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2035 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2036 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2037 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2038 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2039 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2044 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2045 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2046 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2047 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2048 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2052 if (!$form->{"basefactor_$i"}) {
2053 $form->{"basefactor_$i"} = 1;
2059 sellprice AS default_sellprice,
2062 'selected' AS selected
2068 parts.sellprice AS default_sellprice,
2069 pricegroup.pricegroup,
2073 LEFT JOIN parts ON parts.id = parts_id
2074 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2076 ORDER BY pricegroup|;
2077 my @values = (conv_i($id), conv_i($id));
2078 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2080 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2082 $pkr->{selected} = '';
2084 # if there is an exchange rate change price
2085 if (($form->{exchangerate} * 1) != 0) {
2086 $pkr->{price} /= $form->{exchangerate};
2089 $pkr->{price} *= $form->{"basefactor_$i"};
2090 $pkr->{price} *= $basefactor;
2091 $pkr->{price_ufmt} = $pkr->{price};
2092 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2094 if ($selectedpricegroup_id eq undef) {
2095 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2096 # Case A: open old invoice, no pricegroup selected
2097 # Case B: add new article to invoice, no pricegroup selected
2099 # to distinguish case A and B the variable pricegroup_id_$i is used
2100 # for new articles this variable isn't defined, for loaded articles it is
2101 # sellprice can't be used, as it already has 0,00 set
2103 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2105 $pkr->{selected} = ' selected';
2106 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2107 and not defined $form->{"pricegroup_id_$i"}
2108 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2109 # for the case where pricegroup prices haven't been set
2111 # Case B: use default pricegroup of customer
2113 $pkr->{selected} = ' selected'; # unless $form->{selected};
2114 # no customer pricesgroup set
2115 if ($pkr->{price_unfmt} == $pkr->{default_sellprice}) {
2117 $pkr->{price} = $form->{"sellprice_$i"};
2121 # this sub should not set anything and only return. --sschoeling, 20090506
2122 # is this correct? put in again... -- grichardson 20110119
2123 $form->{"sellprice_$i"} = $pkr->{price};
2126 } elsif ($pkr->{price_unfmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2127 $pkr->{price} = $form->{"sellprice_$i"};
2128 $pkr->{selected} = ' selected';
2132 # existing article: pricegroup or price changed
2133 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2134 if ($selectedpricegroup_id ne $pricegroup_old) {
2135 # pricegroup has changed
2136 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2137 $pkr->{selected} = ' selected';
2139 } elsif ( ($form->parse_amount($myconfig, $price_new)
2140 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2141 and ($price_new ne 0) and defined $price_new) {
2142 # sellprice has changed
2143 # when loading existing invoices $price_new is NULL
2144 if ($pkr->{pricegroup_id} == 0) {
2145 $pkr->{price} = $form->{"sellprice_$i"};
2146 $pkr->{selected} = ' selected';
2148 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2149 # neither sellprice nor pricegroup changed
2150 $pkr->{selected} = ' selected';
2151 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2152 # $pkr->{price} = $form->{"sellprice_$i"};
2154 $pkr->{price} = $form->{"sellprice_$i"};
2158 push @{ $form->{PRICES}{$i} }, $pkr;
2161 $form->{"basefactor_$i"} *= $basefactor;
2168 $main::lxdebug->leave_sub();
2172 $main::lxdebug->enter_sub();
2174 my ($self, $myconfig, $form, $table) = @_;
2176 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2178 # make sure there's no funny stuff in $table
2179 # ToDO: die when this happens and throw an error
2180 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2182 my $dbh = $form->get_standard_dbh;
2184 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2185 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2187 $main::lxdebug->leave_sub();
2193 $main::lxdebug->enter_sub();
2195 my ($self, $myconfig, $form, $table, $id) = @_;
2197 $main::lxdebug->leave_sub() and return 0 unless ($id);
2199 # make sure there's no funny stuff in $table
2200 # ToDO: die when this happens and throw an error
2201 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2203 my $dbh = $form->get_standard_dbh;
2205 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2206 my ($result) = selectrow_query($form, $dbh, $query, $id);
2208 $main::lxdebug->leave_sub();
2213 sub get_standard_accno_current_assets {
2214 $main::lxdebug->enter_sub();
2216 my ($self, $myconfig, $form) = @_;
2218 my $dbh = $form->get_standard_dbh;
2220 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2221 my ($result) = selectrow_query($form, $dbh, $query);
2223 $main::lxdebug->leave_sub();