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 licensenumber 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"};
218 if ($form->{lizenzen}) {
219 if ($form->{"licensenumber_$i"}) {
220 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
221 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
222 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
223 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
226 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
227 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
232 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
234 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
235 my ($dec) = ($sellprice =~ /\.(\d+)/);
236 my $decimalplaces = max 2, length($dec);
238 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
239 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
240 my $linetotal = $form->round_amount($linetotal_exact, 2);
241 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
243 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
244 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
246 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
247 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
249 $linetotal = ($linetotal != 0) ? $linetotal : '';
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
253 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
255 $form->{total} += $linetotal;
256 $form->{nodiscount_total} += $nodiscount_linetotal;
257 $form->{discount_total} += $discount;
259 if ($subtotal_header) {
260 $discount_subtotal += $linetotal;
261 $nodiscount_subtotal += $nodiscount_linetotal;
264 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
265 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
266 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
267 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
268 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
270 $discount_subtotal = 0;
271 $nodiscount_subtotal = 0;
272 $subtotal_header = 0;
275 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
276 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
279 if (!$form->{"discount_$i"}) {
280 $nodiscount += $linetotal;
283 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
284 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
285 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
286 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
288 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
289 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
291 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
295 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
297 if ($form->{taxincluded}) {
300 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
301 $taxbase = $linetotal - $taxamount;
303 $taxamount = $linetotal * $taxrate;
304 $taxbase = $linetotal;
307 if ($form->round_amount($taxrate, 7) == 0) {
308 if ($form->{taxincluded}) {
309 foreach my $accno (@taxaccounts) {
310 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
312 $taxaccounts{$accno} += $taxamount;
313 $taxdiff += $taxamount;
315 $taxbase{$accno} += $taxbase;
317 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
319 foreach my $accno (@taxaccounts) {
320 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
321 $taxbase{$accno} += $taxbase;
325 foreach my $accno (@taxaccounts) {
326 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
327 $taxbase{$accno} += $taxbase;
330 my $tax_rate = $taxrate * 100;
331 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
332 if ($form->{"assembly_$i"}) {
335 # get parts and push them onto the stack
337 if ($form->{groupitems}) {
339 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
341 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
345 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
347 JOIN parts p ON (a.parts_id = p.id)
348 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
349 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
350 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
352 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
353 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
354 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
355 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
356 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
359 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
361 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
362 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
364 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
365 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
371 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
375 foreach my $item (sort keys %taxaccounts) {
376 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
381 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
382 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
383 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
384 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
388 for my $i (1 .. $form->{paidaccounts}) {
389 if ($form->{"paid_$i"}) {
390 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
392 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
393 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
394 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
395 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
396 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
398 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
401 if($form->{taxincluded}) {
402 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
403 $form->{subtotal_nofmt} = $form->{total} - $tax;
406 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
407 $form->{subtotal_nofmt} = $form->{total};
410 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
411 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
412 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
413 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
415 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
416 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
418 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
419 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
421 $form->set_payment_options($myconfig, $form->{invdate});
423 $form->{username} = $myconfig->{name};
425 $main::lxdebug->leave_sub();
428 sub project_description {
429 $main::lxdebug->enter_sub();
431 my ($self, $dbh, $id) = @_;
432 my $form = \%main::form;
434 my $query = qq|SELECT description FROM project WHERE id = ?|;
435 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
437 $main::lxdebug->leave_sub();
442 sub customer_details {
443 $main::lxdebug->enter_sub();
445 my ($self, $myconfig, $form, @wanted_vars) = @_;
447 # connect to database
448 my $dbh = $form->get_standard_dbh;
450 my $language_id = $form->{language_id};
452 # get contact id, set it if nessessary
455 my @values = (conv_i($form->{customer_id}));
458 if ($form->{cp_id}) {
459 $where = qq| AND (cp.cp_id = ?) |;
460 push(@values, conv_i($form->{cp_id}));
463 # get rest for the customer
465 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
466 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
468 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
469 WHERE (ct.id = ?) $where
472 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
474 # remove id and taxincluded before copy back
475 delete @$ref{qw(id taxincluded)};
477 @wanted_vars = grep({ $_ } @wanted_vars);
478 if (scalar(@wanted_vars) > 0) {
480 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
481 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
484 map { $form->{$_} = $ref->{$_} } keys %$ref;
486 if ($form->{delivery_customer_id}) {
488 qq|SELECT *, notes as customernotes
492 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
494 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
497 if ($form->{delivery_vendor_id}) {
499 qq|SELECT *, notes as customernotes
503 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
505 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
508 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
510 'trans_id' => $form->{customer_id});
511 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
513 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
514 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
515 'language_id' => $language_id,
516 'allow_fallback' => 1);
519 $main::lxdebug->leave_sub();
523 $main::lxdebug->enter_sub();
525 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
527 # connect to database, turn off autocommit
528 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
530 my ($query, $sth, $null, $project_id, @values);
531 my $exchangerate = 0;
533 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
536 if (!$form->{employee_id}) {
537 $form->get_employee($dbh);
540 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
541 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
542 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
543 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
544 if (!$form->{department_id}){
545 ($null, $form->{department_id}) = split(/--/, $form->{department});
548 my $all_units = AM->retrieve_units($myconfig, $form);
550 if (!$payments_only) {
552 &reverse_invoice($dbh, $form);
555 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
556 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
558 $query = qq|SELECT nextval('glid')|;
559 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
561 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
562 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
564 if (!$form->{invnumber}) {
566 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
567 "cnnumber" : "invnumber", $dbh);
572 my ($netamount, $invoicediff) = (0, 0);
573 my ($amount, $linetotal, $lastincomeaccno);
575 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
576 my $defaultcurrency = (split m/:/, $currencies)[0];
578 if ($form->{currency} eq $defaultcurrency) {
579 $form->{exchangerate} = 1;
581 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
584 $form->{exchangerate} =
587 : $form->parse_amount($myconfig, $form->{exchangerate});
589 $form->{expense_inventory} = "";
593 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
594 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
597 $form->{amount} = {};
598 $form->{amount_cogs} = {};
600 foreach my $i (1 .. $form->{rowcount}) {
601 if ($form->{type} eq "credit_note") {
602 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
603 $form->{shipped} = 1;
605 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
610 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
611 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
612 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
614 if ($form->{storno}) {
615 $form->{"qty_$i"} *= -1;
618 if ($form->{"id_$i"}) {
621 if (defined($baseunits{$form->{"id_$i"}})) {
622 $item_unit = $baseunits{$form->{"id_$i"}};
625 $query = qq|SELECT unit FROM parts WHERE id = ?|;
626 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
627 $baseunits{$form->{"id_$i"}} = $item_unit;
630 if (defined($all_units->{$item_unit}->{factor})
631 && ($all_units->{$item_unit}->{factor} ne '')
632 && ($all_units->{$item_unit}->{factor} != 0)) {
633 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
637 $baseqty = $form->{"qty_$i"} * $basefactor;
639 my ($allocated, $taxrate) = (0, 0);
643 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
645 # keep entered selling price
647 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
649 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
651 my $decimalplaces = ($dec > 2) ? $dec : 2;
653 # undo discount formatting
654 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
657 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
659 # round linetotal to 2 decimal places
660 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
661 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
663 if ($form->{taxincluded}) {
664 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
665 $form->{"sellprice_$i"} =
666 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
668 $taxamount = $linetotal * $taxrate;
671 $netamount += $linetotal;
673 if ($taxamount != 0) {
675 $form->{amount}{ $form->{id} }{$_} +=
676 $taxamount * $form->{"${_}_rate"} / $taxrate
677 } split(/ /, $form->{"taxaccounts_$i"});
680 # add amount to income, $form->{amount}{trans_id}{accno}
681 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
683 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
684 $linetotal = $form->round_amount($linetotal, 2);
686 # this is the difference from the inventory
687 $invoicediff += ($amount - $linetotal);
689 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
692 $lastincomeaccno = $form->{"income_accno_$i"};
694 # adjust and round sellprice
695 $form->{"sellprice_$i"} =
696 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
699 next if $payments_only;
701 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
703 if ($form->{"assembly_$i"}) {
704 # record assembly item as allocated
705 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
708 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
712 # get pricegroup_id and save it
713 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
716 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
718 # save detail record in invoice table
720 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
721 sellprice, fxsellprice, discount, allocated, assemblyitem,
722 unit, deliverydate, project_id, serialnumber, pricegroup_id,
723 ordnumber, transdate, cusordnumber, base_qty, subtotal,
724 marge_percent, marge_total, lastcost,
725 price_factor_id, price_factor, marge_price_factor)
726 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
727 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
729 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
730 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
731 $form->{"sellprice_$i"}, $fxsellprice,
732 $form->{"discount_$i"}, $allocated, 'f',
733 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
734 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
735 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
736 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
737 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
738 $form->{"lastcost_$i"},
739 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
740 conv_i($form->{"marge_price_factor_$i"}));
741 do_query($form, $dbh, $query, @values);
743 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
745 qq|INSERT INTO licenseinvoice (trans_id, license_id)
746 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
747 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
748 do_query($form, $dbh, $query, @values);
751 CVar->save_custom_variables(module => 'IC',
752 sub_module => 'invoice',
753 trans_id => $invoice_id,
754 configs => $ic_cvar_configs,
756 name_prefix => 'ic_',
757 name_postfix => "_$i",
762 # total payments, don't move we need it here
763 for my $i (1 .. $form->{paidaccounts}) {
764 if ($form->{type} eq "credit_note") {
765 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
767 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
769 $form->{paid} += $form->{"paid_$i"};
770 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
773 my ($tax, $diff) = (0, 0);
775 $netamount = $form->round_amount($netamount, 2);
777 # figure out rounding errors for total amount vs netamount + taxes
778 if ($form->{taxincluded}) {
780 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
781 $diff += $amount - $netamount * $form->{exchangerate};
782 $netamount = $amount;
784 foreach my $item (split(/ /, $form->{taxaccounts})) {
785 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
786 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
787 $tax += $form->{amount}{ $form->{id} }{$item};
788 $netamount -= $form->{amount}{ $form->{id} }{$item};
791 $invoicediff += $diff;
792 ######## this only applies to tax included
793 if ($lastincomeaccno) {
794 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
798 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
799 $diff = $amount - $netamount * $form->{exchangerate};
800 $netamount = $amount;
801 foreach my $item (split(/ /, $form->{taxaccounts})) {
802 $form->{amount}{ $form->{id} }{$item} =
803 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
806 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
809 $amount - $form->{amount}{ $form->{id} }{$item} *
810 $form->{exchangerate};
811 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
812 $tax += $form->{amount}{ $form->{id} }{$item};
816 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
818 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
821 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
823 # update exchangerate
824 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
825 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
826 $form->{exchangerate}, 0);
829 $project_id = conv_i($form->{"globalproject_id"});
831 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
832 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
833 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
835 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
837 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
839 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
840 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
841 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
842 do_query($form, $dbh, $query, @values);
843 $form->{amount_cogs}{$trans_id}{$accno} = 0;
847 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
848 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
850 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
852 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
853 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
854 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
855 do_query($form, $dbh, $query, @values);
860 foreach my $trans_id (keys %{ $form->{amount} }) {
861 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
862 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
864 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
866 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
868 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
869 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
870 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
871 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
872 do_query($form, $dbh, $query, @values);
873 $form->{amount}{$trans_id}{$accno} = 0;
877 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
878 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
880 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
882 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
883 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
884 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
885 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
886 do_query($form, $dbh, $query, @values);
891 # deduct payment differences from diff
892 for my $i (1 .. $form->{paidaccounts}) {
893 if ($form->{"paid_$i"} != 0) {
895 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
896 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
900 # record payments and offsetting AR
901 if (!$form->{storno}) {
902 for my $i (1 .. $form->{paidaccounts}) {
904 next if ($form->{"paid_$i"} == 0);
906 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
907 $form->{"datepaid_$i"} = $form->{invdate}
908 unless ($form->{"datepaid_$i"});
909 $form->{datepaid} = $form->{"datepaid_$i"};
913 if ($form->{currency} eq $defaultcurrency) {
914 $form->{"exchangerate_$i"} = 1;
916 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
917 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
921 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
923 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
925 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
926 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
927 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
928 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
929 do_query($form, $dbh, $query, @values);
933 $form->{"paid_$i"} *= -1;
936 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
937 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
938 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
939 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
940 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
941 do_query($form, $dbh, $query, @values);
943 # exchangerate difference
944 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
945 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
949 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
950 $form->{"exchangerate_$i"};
952 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
955 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
961 # update exchange rate
962 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
963 $form->update_exchangerate($dbh, $form->{currency},
964 $form->{"datepaid_$i"},
965 $form->{"exchangerate_$i"}, 0);
969 } else { # if (!$form->{storno})
970 $form->{marge_total} *= -1;
973 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
975 if ($payments_only) {
976 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
977 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
979 $dbh->commit if !$provided_dbh;
981 $main::lxdebug->leave_sub();
985 # record exchange rate differences and gains/losses
986 foreach my $accno (keys %{ $form->{fx} }) {
987 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
989 ($form->{fx}{$accno}{$transdate} =
990 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
995 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
996 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
997 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
998 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
999 do_query($form, $dbh, $query, @values);
1004 $amount = $netamount + $tax;
1007 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1009 $query = qq|UPDATE ar set
1010 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1011 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1012 amount = ?, netamount = ?, paid = ?,
1013 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1014 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1015 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1016 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1017 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1018 cp_id = ?, marge_total = ?, marge_percent = ?,
1019 globalproject_id = ?, delivery_customer_id = ?,
1020 transaction_description = ?, delivery_vendor_id = ?,
1021 donumber = ?, invnumber_for_credit_note = ?
1023 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1024 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1025 $amount, $netamount, $form->{"paid"},
1026 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1027 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1028 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1029 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1030 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1031 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1032 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1033 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1034 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1035 conv_i($form->{"id"}));
1036 do_query($form, $dbh, $query, @values);
1039 if ($form->{storno}) {
1042 paid = paid + amount,
1044 intnotes = ? || intnotes
1046 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1047 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1051 $form->{name} = $form->{customer};
1052 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1054 if (!$form->{shipto_id}) {
1055 $form->add_shipto($dbh, $form->{id}, "AR");
1058 # save printed, emailed and queued
1059 $form->save_status($dbh);
1061 Common::webdav_folder($form);
1063 # Link this record to the records it was created from.
1064 RecordLinks->create_links('dbh' => $dbh,
1066 'from_table' => 'oe',
1067 'from_ids' => $form->{convert_from_oe_ids},
1069 'to_id' => $form->{id},
1071 delete $form->{convert_from_oe_ids};
1073 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1075 if (scalar @convert_from_do_ids) {
1076 DO->close_orders('dbh' => $dbh,
1077 'ids' => \@convert_from_do_ids);
1079 RecordLinks->create_links('dbh' => $dbh,
1081 'from_table' => 'delivery_orders',
1082 'from_ids' => \@convert_from_do_ids,
1084 'to_id' => $form->{id},
1087 delete $form->{convert_from_do_ids};
1089 ARAP->close_orders_if_billed('dbh' => $dbh,
1090 'arap_id' => $form->{id},
1094 $dbh->commit if !$provided_dbh;
1096 $main::lxdebug->leave_sub();
1101 sub _delete_payments {
1102 $main::lxdebug->enter_sub();
1104 my ($self, $form, $dbh) = @_;
1106 my @delete_acc_trans_ids;
1108 # Delete old payment entries from acc_trans.
1110 qq|SELECT acc_trans_id
1112 WHERE (trans_id = ?) AND fx_transaction
1116 SELECT at.acc_trans_id
1118 LEFT JOIN chart c ON (at.chart_id = c.id)
1119 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1120 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1123 qq|SELECT at.acc_trans_id
1125 LEFT JOIN chart c ON (at.chart_id = c.id)
1126 WHERE (trans_id = ?)
1127 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1128 ORDER BY at.acc_trans_id
1130 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1132 if (@delete_acc_trans_ids) {
1133 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1134 do_query($form, $dbh, $query);
1137 $main::lxdebug->leave_sub();
1141 $main::lxdebug->enter_sub();
1143 my ($self, $myconfig, $form, $locale) = @_;
1145 # connect to database, turn off autocommit
1146 my $dbh = $form->get_standard_dbh;
1149 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1151 $old_form = save_form();
1153 # Delete all entries in acc_trans from prior payments.
1154 $self->_delete_payments($form, $dbh);
1156 # Save the new payments the user made before cleaning up $form.
1157 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1159 # Clean up $form so that old content won't tamper the results.
1160 %keep_vars = map { $_, 1 } qw(login password id);
1161 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1163 # Retrieve the invoice from the database.
1164 $self->retrieve_invoice($myconfig, $form);
1166 # Set up the content of $form in the way that IS::post_invoice() expects.
1167 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1169 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1170 $item = $form->{invoice_details}->[$row - 1];
1172 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1174 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1177 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1179 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1181 # Restore the payment options from the user input.
1182 map { $form->{$_} = $payments{$_} } keys %payments;
1184 # Get the AR accno (which is normally done by Form::create_links()).
1188 LEFT JOIN chart c ON (at.chart_id = c.id)
1189 WHERE (trans_id = ?)
1190 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1191 ORDER BY at.acc_trans_id
1194 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1196 # Post the new payments.
1197 $self->post_invoice($myconfig, $form, $dbh, 1);
1199 restore_form($old_form);
1201 my $rc = $dbh->commit();
1203 $main::lxdebug->leave_sub();
1208 sub process_assembly {
1209 $main::lxdebug->enter_sub();
1211 my ($dbh, $form, $id, $totalqty) = @_;
1214 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1215 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1217 JOIN parts p ON (a.parts_id = p.id)
1219 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1221 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1225 $ref->{inventory_accno_id} *= 1;
1226 $ref->{expense_accno_id} *= 1;
1228 # multiply by number of assemblies
1229 $ref->{qty} *= $totalqty;
1231 if ($ref->{assembly}) {
1232 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1235 if ($ref->{inventory_accno_id}) {
1236 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1240 # save detail record for individual assembly item in invoice table
1242 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1243 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1244 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1245 do_query($form, $dbh, $query, @values);
1251 $main::lxdebug->leave_sub();
1255 $main::lxdebug->enter_sub();
1257 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1261 $form->{taxzone_id} *=1;
1262 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1263 my $taxzone_id = $form->{"taxzone_id"} * 1;
1265 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1266 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1267 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1268 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1269 FROM invoice i, parts p
1270 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1271 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1272 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1273 WHERE (i.parts_id = p.id)
1274 AND (i.parts_id = ?)
1275 AND ((i.base_qty + i.allocated) < 0)
1277 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1282 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1283 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1287 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1289 # total expenses and inventory
1290 # sellprice is the cost of the item
1291 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1293 if (!$::lx_office_conf{system}->{eur}) {
1294 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1296 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1297 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1298 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1300 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1301 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1307 last if (($totalqty -= $qty) <= 0);
1312 $main::lxdebug->leave_sub();
1317 sub reverse_invoice {
1318 $main::lxdebug->enter_sub();
1320 my ($dbh, $form) = @_;
1322 # reverse inventory items
1324 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1326 JOIN parts p ON (i.parts_id = p.id)
1327 WHERE i.trans_id = ?|;
1328 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1330 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1332 if ($ref->{inventory_accno_id}) {
1333 # de-allocated purchases
1335 qq|SELECT i.id, i.trans_id, i.allocated
1337 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1338 ORDER BY i.trans_id DESC|;
1339 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1341 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1342 my $qty = $ref->{qty};
1343 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1344 $qty = $inhref->{allocated};
1348 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1350 last if (($ref->{qty} -= $qty) <= 0);
1359 my @values = (conv_i($form->{id}));
1360 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1361 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1363 if ($form->{lizenzen}) {
1365 qq|DELETE FROM licenseinvoice
1366 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1367 do_query($form, $dbh, $query, @values);
1370 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1372 $main::lxdebug->leave_sub();
1375 sub delete_invoice {
1376 $main::lxdebug->enter_sub();
1378 my ($self, $myconfig, $form) = @_;
1380 # connect to database
1381 my $dbh = $form->get_standard_dbh;
1384 &reverse_invoice($dbh, $form);
1386 my @values = (conv_i($form->{id}));
1388 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1389 # zurückgesetzt werden. Vgl:
1390 # id | storno | storno_id | paid | amount
1391 #----+--------+-----------+---------+-----------
1392 # 18 | f | | 0.00000 | 119.00000
1394 # 18 | t | | 119.00000 | 119.00000
1396 if($form->{storno}){
1397 # storno_id auslesen und korrigieren
1398 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1399 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1403 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1405 # delete spool files
1406 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1408 # delete status entries
1409 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1411 my $rc = $dbh->commit;
1414 my $spool = $::lx_office_conf{paths}->{spool};
1415 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1418 $main::lxdebug->leave_sub();
1423 sub retrieve_invoice {
1424 $main::lxdebug->enter_sub();
1426 my ($self, $myconfig, $form) = @_;
1428 # connect to database
1429 my $dbh = $form->get_standard_dbh;
1431 my ($sth, $ref, $query);
1433 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1437 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1438 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1439 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1440 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1441 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1442 d.curr AS currencies
1446 $ref = selectfirst_hashref_query($form, $dbh, $query);
1447 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1450 my $id = conv_i($form->{id});
1453 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1457 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1458 a.orddate, a.quodate, a.globalproject_id,
1459 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1460 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1461 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1462 a.employee_id, a.salesman_id, a.payment_id,
1463 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1464 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1465 a.marge_total, a.marge_percent,
1468 LEFT JOIN employee e ON (e.id = a.employee_id)
1470 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1471 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1474 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1477 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1478 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1480 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1482 foreach my $vc (qw(customer vendor)) {
1483 next if !$form->{"delivery_${vc}_id"};
1484 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1487 # get printed, emailed
1488 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1489 $sth = prepare_execute_query($form, $dbh, $query, $id);
1491 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1492 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1493 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1494 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1497 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1499 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1500 : $form->{invdate} ? $dbh->quote($form->{invdate})
1504 my $taxzone_id = $form->{taxzone_id} *= 1;
1505 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1507 # retrieve individual items
1510 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1511 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1512 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1515 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1516 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1517 i.price_factor_id, i.price_factor, i.marge_price_factor,
1518 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1519 pr.projectnumber, pg.partsgroup, prg.pricegroup
1522 LEFT JOIN parts p ON (i.parts_id = p.id)
1523 LEFT JOIN project pr ON (i.project_id = pr.id)
1524 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1525 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1527 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1528 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1529 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1531 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1533 $sth = prepare_execute_query($form, $dbh, $query, $id);
1535 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1536 # Retrieve custom variables.
1537 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1539 sub_module => 'invoice',
1540 trans_id => $ref->{invoice_id},
1542 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1543 delete $ref->{invoice_id};
1545 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1546 delete($ref->{"part_inventory_accno_id"});
1548 foreach my $type (qw(inventory income expense)) {
1549 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1550 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1551 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1555 # get tax rates and description
1556 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1558 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1559 LEFT JOIN chart c ON (c.id = t.chart_id)
1561 (SELECT tk.tax_id FROM taxkeys tk
1562 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1563 AND startdate <= date($transdate)
1564 ORDER BY startdate DESC LIMIT 1)
1566 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1567 $ref->{taxaccounts} = "";
1569 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1571 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1575 $ref->{taxaccounts} .= "$ptr->{accno} ";
1577 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1578 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1579 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1580 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1581 $form->{taxaccounts} .= "$ptr->{accno} ";
1586 if ($form->{lizenzen}) {
1587 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1588 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1589 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1592 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1594 chop $ref->{taxaccounts};
1595 push @{ $form->{invoice_details} }, $ref;
1600 Common::webdav_folder($form);
1603 my $rc = $dbh->commit;
1605 $main::lxdebug->leave_sub();
1611 $main::lxdebug->enter_sub();
1613 my ($self, $myconfig, $form) = @_;
1615 # connect to database
1616 my $dbh = $form->get_standard_dbh;
1618 my $dateformat = $myconfig->{dateformat};
1619 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1621 my (@values, $duedate, $ref, $query);
1623 if ($form->{invdate}) {
1624 $duedate = "to_date(?, '$dateformat')";
1625 push @values, $form->{invdate};
1627 $duedate = "current_date";
1630 my $cid = conv_i($form->{customer_id});
1633 if ($form->{payment_id}) {
1634 $payment_id = "(pt.id = ?) OR";
1635 push @values, conv_i($form->{payment_id});
1641 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1642 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1643 c.street, c.zipcode, c.city, c.country,
1644 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1645 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1646 b.discount AS tradediscount, b.description AS business
1648 LEFT JOIN business b ON (b.id = c.business_id)
1649 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1652 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1654 delete $ref->{salesman_id} if !$ref->{salesman_id};
1656 map { $form->{$_} = $ref->{$_} } keys %$ref;
1659 qq|SELECT sum(amount - paid) AS dunning_amount
1661 WHERE (paid < amount)
1662 AND (customer_id = ?)
1663 AND (dunning_config_id IS NOT NULL)|;
1664 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1665 map { $form->{$_} = $ref->{$_} } keys %$ref;
1668 qq|SELECT dnn.dunning_description AS max_dunning_level
1669 FROM dunning_config dnn
1670 WHERE id IN (SELECT dunning_config_id
1672 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1673 ORDER BY dunning_level DESC LIMIT 1|;
1674 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1675 map { $form->{$_} = $ref->{$_} } keys %$ref;
1677 $form->{creditremaining} = $form->{creditlimit};
1678 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1679 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1680 $form->{creditremaining} -= $value;
1684 (SELECT e.buy FROM exchangerate e
1685 WHERE e.curr = o.curr
1686 AND e.transdate = o.transdate)
1688 WHERE o.customer_id = ?
1689 AND o.quotation = '0'
1690 AND o.closed = '0'|;
1691 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1693 while (my ($amount, $exch) = $sth->fetchrow_array) {
1694 $exch = 1 unless $exch;
1695 $form->{creditremaining} -= $amount * $exch;
1699 # get shipto if we did not converted an order or invoice
1700 if (!$form->{shipto}) {
1701 map { delete $form->{$_} }
1702 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1703 shiptostreet shiptozipcode shiptocity shiptocountry
1704 shiptocontact shiptophone shiptofax shiptoemail);
1706 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1707 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1709 map { $form->{$_} = $ref->{$_} } keys %$ref;
1712 # setup last accounts used for this customer
1713 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1715 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1717 JOIN acc_trans ac ON (ac.chart_id = c.id)
1718 JOIN ar a ON (a.id = ac.trans_id)
1719 WHERE a.customer_id = ?
1720 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1721 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1722 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1725 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1726 if ($ref->{category} eq 'I') {
1728 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1730 if ($form->{initial_transdate}) {
1732 qq|SELECT tk.tax_id, t.rate
1734 LEFT JOIN tax t ON tk.tax_id = t.id
1735 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1736 ORDER BY tk.startdate DESC
1738 my ($tax_id, $rate) =
1739 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1740 $form->{initial_transdate});
1741 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1744 if ($ref->{category} eq 'A') {
1745 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1749 $form->{rowcount} = $i if ($i && !$form->{type});
1752 $main::lxdebug->leave_sub();
1756 $main::lxdebug->enter_sub();
1758 my ($self, $myconfig, $form) = @_;
1760 # connect to database
1761 my $dbh = $form->get_standard_dbh;
1763 my $i = $form->{rowcount};
1765 my $where = qq|NOT p.obsolete = '1'|;
1768 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1769 my ($table, $field) = split m/\./, $column;
1770 next if !$form->{"${field}_${i}"};
1771 $where .= qq| AND lower(${column}) ILIKE ?|;
1772 push @values, '%' . $form->{"${field}_${i}"} . '%';
1775 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1776 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1777 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1778 push @values, $form->{"partnumber_$i"};
1781 if ($form->{"description_$i"}) {
1782 $where .= qq| ORDER BY p.description|;
1784 $where .= qq| ORDER BY p.partnumber|;
1788 if ($form->{type} eq "invoice") {
1790 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1791 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1795 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1799 my $taxzone_id = $form->{taxzone_id} * 1;
1800 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1804 p.id, p.partnumber, p.description, p.sellprice,
1805 p.listprice, p.inventory_accno_id, p.lastcost,
1807 c1.accno AS inventory_accno,
1808 c1.new_chart_id AS inventory_new_chart,
1809 date($transdate) - c1.valid_from AS inventory_valid,
1811 c2.accno AS income_accno,
1812 c2.new_chart_id AS income_new_chart,
1813 date($transdate) - c2.valid_from AS income_valid,
1815 c3.accno AS expense_accno,
1816 c3.new_chart_id AS expense_new_chart,
1817 date($transdate) - c3.valid_from AS expense_valid,
1819 p.unit, p.assembly, p.bin, p.onhand,
1820 p.notes AS partnotes, p.notes AS longdescription,
1821 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1824 pfac.factor AS price_factor,
1829 LEFT JOIN chart c1 ON
1830 ((SELECT inventory_accno_id
1831 FROM buchungsgruppen
1832 WHERE id = p.buchungsgruppen_id) = c1.id)
1833 LEFT JOIN chart c2 ON
1834 ((SELECT income_accno_id_${taxzone_id}
1835 FROM buchungsgruppen
1836 WHERE id = p.buchungsgruppen_id) = c2.id)
1837 LEFT JOIN chart c3 ON
1838 ((SELECT expense_accno_id_${taxzone_id}
1839 FROM buchungsgruppen
1840 WHERE id = p.buchungsgruppen_id) = c3.id)
1841 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1842 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1844 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1846 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1848 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1849 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1850 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1851 if (!$ref->{inventory_accno_id}) {
1852 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1854 delete($ref->{inventory_accno_id});
1856 foreach my $type (qw(inventory income expense)) {
1857 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1859 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1862 ($ref->{"${type}_accno"},
1863 $ref->{"${type}_new_chart"},
1864 $ref->{"${type}_valid"})
1865 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1869 if ($form->{payment_id} eq "") {
1870 $form->{payment_id} = $form->{part_payment_id};
1873 # get tax rates and description
1874 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1876 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1878 LEFT JOIN chart c ON (c.id = t.chart_id)
1882 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1884 ORDER BY startdate DESC
1887 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1888 my $stw = $dbh->prepare($query);
1889 $stw->execute(@values) || $form->dberror($query);
1891 $ref->{taxaccounts} = "";
1893 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1895 # if ($customertax{$ref->{accno}})
1896 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1900 $ref->{taxaccounts} .= "$ptr->{accno} ";
1902 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1903 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1904 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1905 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1906 $form->{taxaccounts} .= "$ptr->{accno} ";
1912 chop $ref->{taxaccounts};
1913 if ($form->{language_id}) {
1915 qq|SELECT tr.translation, tr.longdescription
1917 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1918 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1919 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1920 if ($translation ne "") {
1921 $ref->{description} = $translation;
1922 $ref->{longdescription} = $longdescription;
1926 qq|SELECT tr.translation, tr.longdescription
1928 WHERE tr.language_id IN
1931 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1934 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1935 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1936 if ($translation ne "") {
1937 $ref->{description} = $translation;
1938 $ref->{longdescription} = $longdescription;
1943 $ref->{onhand} *= 1;
1945 push @{ $form->{item_list} }, $ref;
1947 if ($form->{lizenzen}) {
1948 if ($ref->{inventory_accno} > 0) {
1952 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1953 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1954 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1955 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1963 foreach my $item (@{ $form->{item_list} }) {
1964 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1965 trans_id => $item->{id},
1969 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1972 $main::lxdebug->leave_sub();
1975 ##########################
1976 # get pricegroups from database
1977 # build up selected pricegroup
1978 # if an exchange rate - change price
1981 sub get_pricegroups_for_parts {
1983 $main::lxdebug->enter_sub();
1985 my ($self, $myconfig, $form) = @_;
1987 my $dbh = $form->get_standard_dbh;
1989 $form->{"PRICES"} = {};
1993 my $all_units = AM->retrieve_units($myconfig, $form);
1994 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1995 $form->{"PRICES"}{$i} = [];
1997 $id = $form->{"id_$i"};
1999 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2000 $id = $form->{"new_id_$i"};
2003 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2005 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2007 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2008 my $sellprice = $form->{"sellprice_$i"};
2009 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2010 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2011 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2013 my $price_new = $form->{"price_new_$i"};
2014 my $price_old = $form->{"price_old_$i"};
2016 if (!$form->{"unit_old_$i"}) {
2017 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2018 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2019 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2020 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2023 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2024 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2025 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2027 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2028 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2029 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2030 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2031 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2032 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2033 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2038 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2039 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2040 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2041 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2042 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2046 if (!$form->{"basefactor_$i"}) {
2047 $form->{"basefactor_$i"} = 1;
2053 sellprice AS default_sellprice,
2056 'selected' AS selected
2062 parts.sellprice AS default_sellprice,
2063 pricegroup.pricegroup,
2067 LEFT JOIN parts ON parts.id = parts_id
2068 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2070 ORDER BY pricegroup|;
2071 my @values = (conv_i($id), conv_i($id));
2072 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2074 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2076 $pkr->{selected} = '';
2078 # if there is an exchange rate change price
2079 if (($form->{exchangerate} * 1) != 0) {
2080 $pkr->{price} /= $form->{exchangerate};
2083 $pkr->{price} *= $form->{"basefactor_$i"};
2084 $pkr->{price} *= $basefactor;
2085 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2087 if ($selectedpricegroup_id eq undef) {
2088 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2089 # Case A: open old invoice, no pricegroup selected
2090 # Case B: add new article to invoice, no pricegroup selected
2092 # to distinguish case A and B the variable pricegroup_id_$i is used
2093 # for new articles this variable isn't defined, for loaded articles it is
2094 # sellprice can't be used, as it already has 0,00 set
2096 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2098 $pkr->{selected} = ' selected';
2100 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2101 and not defined $form->{"pricegroup_id_$i"}
2102 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2103 # for the case where pricegroup prices haven't been set
2105 # Case B: use default pricegroup of customer
2107 $pkr->{selected} = ' selected'; # unless $form->{selected};
2109 # no customer pricesgroup set
2110 if ($pkr->{price} == $pkr->{default_sellprice}) {
2112 $pkr->{price} = $form->{"sellprice_$i"};
2116 # this sub should not set anything and only return. --sschoeling, 20090506
2117 # is this correct? put in again... -- grichardson 20110119
2118 $form->{"sellprice_$i"} = $pkr->{price};
2121 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2122 $pkr->{price} = $form->{"sellprice_$i"};
2123 $pkr->{selected} = ' selected';
2127 # existing article: pricegroup or price changed
2128 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2129 if ($selectedpricegroup_id ne $pricegroup_old) {
2130 # pricegroup has changed
2131 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2132 $pkr->{selected} = ' selected';
2134 } elsif ( ($form->parse_amount($myconfig, $price_new)
2135 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2136 and ($price_new ne 0) and defined $price_new) {
2137 # sellprice has changed
2138 # when loading existing invoices $price_new is NULL
2139 if ($pkr->{pricegroup_id} == 0) {
2140 $pkr->{price} = $form->{"sellprice_$i"};
2141 $pkr->{selected} = ' selected';
2143 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2144 # neither sellprice nor pricegroup changed
2145 $pkr->{selected} = ' selected';
2146 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2147 # $pkr->{price} = $form->{"sellprice_$i"};
2149 $pkr->{price} = $form->{"sellprice_$i"};
2153 push @{ $form->{PRICES}{$i} }, $pkr;
2156 $form->{"basefactor_$i"} *= $basefactor;
2163 $main::lxdebug->leave_sub();
2167 $main::lxdebug->enter_sub();
2169 my ($self, $myconfig, $form, $table) = @_;
2171 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2173 # make sure there's no funny stuff in $table
2174 # ToDO: die when this happens and throw an error
2175 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2177 my $dbh = $form->get_standard_dbh;
2179 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2180 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2182 $main::lxdebug->leave_sub();
2188 $main::lxdebug->enter_sub();
2190 my ($self, $myconfig, $form, $table, $id) = @_;
2192 $main::lxdebug->leave_sub() and return 0 unless ($id);
2194 # make sure there's no funny stuff in $table
2195 # ToDO: die when this happens and throw an error
2196 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2198 my $dbh = $form->get_standard_dbh;
2200 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2201 my ($result) = selectrow_query($form, $dbh, $query, $id);
2203 $main::lxdebug->leave_sub();
2208 sub get_standard_accno_current_assets {
2209 $main::lxdebug->enter_sub();
2211 my ($self, $myconfig, $form) = @_;
2213 my $dbh = $form->get_standard_dbh;
2215 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2216 my ($result) = selectrow_query($form, $dbh, $query);
2218 $main::lxdebug->leave_sub();