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;
285 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_raw} }, $form->format_amount($myconfig, $linetotal_exact, 8);
286 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_raw_nofmt} }, $linetotal_exact;
287 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
288 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
290 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
291 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
293 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
297 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
299 if ($form->{taxincluded}) {
302 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
303 $taxbase = $linetotal - $taxamount;
305 $taxamount = $linetotal * $taxrate;
306 $taxbase = $linetotal;
309 if ($form->round_amount($taxrate, 7) == 0) {
310 if ($form->{taxincluded}) {
311 foreach my $accno (@taxaccounts) {
312 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
314 $taxaccounts{$accno} += $taxamount;
315 $taxdiff += $taxamount;
317 $taxbase{$accno} += $taxbase;
319 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
321 foreach my $accno (@taxaccounts) {
322 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
323 $taxbase{$accno} += $taxbase;
327 foreach my $accno (@taxaccounts) {
328 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
329 $taxbase{$accno} += $taxbase;
332 my $tax_rate = $taxrate * 100;
333 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
334 if ($form->{"assembly_$i"}) {
337 # get parts and push them onto the stack
339 if ($form->{groupitems}) {
341 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
343 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
347 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
349 JOIN parts p ON (a.parts_id = p.id)
350 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
351 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
352 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
354 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
355 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
356 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
357 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
358 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
361 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
363 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
364 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
366 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
367 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
373 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
377 foreach my $item (sort keys %taxaccounts) {
378 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
380 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
381 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
382 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
383 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
384 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
386 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
387 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
390 for my $i (1 .. $form->{paidaccounts}) {
391 if ($form->{"paid_$i"}) {
392 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
394 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
395 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
396 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
397 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
398 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
400 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
403 if($form->{taxincluded}) {
404 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
405 $form->{subtotal_nofmt} = $form->{total} - $tax;
408 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
409 $form->{subtotal_nofmt} = $form->{total};
412 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
413 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
414 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
415 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
417 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
418 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
420 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
421 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
423 $form->set_payment_options($myconfig, $form->{invdate});
425 $form->{username} = $myconfig->{name};
427 $main::lxdebug->leave_sub();
430 sub project_description {
431 $main::lxdebug->enter_sub();
433 my ($self, $dbh, $id) = @_;
434 my $form = \%main::form;
436 my $query = qq|SELECT description FROM project WHERE id = ?|;
437 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
439 $main::lxdebug->leave_sub();
444 sub customer_details {
445 $main::lxdebug->enter_sub();
447 my ($self, $myconfig, $form, @wanted_vars) = @_;
449 # connect to database
450 my $dbh = $form->get_standard_dbh;
452 my $language_id = $form->{language_id};
454 # get contact id, set it if nessessary
457 my @values = (conv_i($form->{customer_id}));
460 if ($form->{cp_id}) {
461 $where = qq| AND (cp.cp_id = ?) |;
462 push(@values, conv_i($form->{cp_id}));
465 # get rest for the customer
467 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
468 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
470 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
471 WHERE (ct.id = ?) $where
474 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
476 # remove id and taxincluded before copy back
477 delete @$ref{qw(id taxincluded)};
479 @wanted_vars = grep({ $_ } @wanted_vars);
480 if (scalar(@wanted_vars) > 0) {
482 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
483 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
486 map { $form->{$_} = $ref->{$_} } keys %$ref;
488 if ($form->{delivery_customer_id}) {
490 qq|SELECT *, notes as customernotes
494 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
496 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
499 if ($form->{delivery_vendor_id}) {
501 qq|SELECT *, notes as customernotes
505 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
507 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
510 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
512 'trans_id' => $form->{customer_id});
513 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
515 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
516 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
517 'language_id' => $language_id,
518 'allow_fallback' => 1);
521 $main::lxdebug->leave_sub();
525 $main::lxdebug->enter_sub();
527 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
529 # connect to database, turn off autocommit
530 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
532 my ($query, $sth, $null, $project_id, @values);
533 my $exchangerate = 0;
535 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
538 if (!$form->{employee_id}) {
539 $form->get_employee($dbh);
542 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
543 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
544 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
545 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
546 if (!$form->{department_id}){
547 ($null, $form->{department_id}) = split(/--/, $form->{department});
550 my $all_units = AM->retrieve_units($myconfig, $form);
552 if (!$payments_only) {
554 &reverse_invoice($dbh, $form);
557 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
558 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
560 $query = qq|SELECT nextval('glid')|;
561 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
563 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
564 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
566 if (!$form->{invnumber}) {
568 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
569 "cnnumber" : "invnumber", $dbh);
574 my ($netamount, $invoicediff) = (0, 0);
575 my ($amount, $linetotal, $lastincomeaccno);
577 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
578 my $defaultcurrency = (split m/:/, $currencies)[0];
580 if ($form->{currency} eq $defaultcurrency) {
581 $form->{exchangerate} = 1;
583 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
586 $form->{exchangerate} =
589 : $form->parse_amount($myconfig, $form->{exchangerate});
591 $form->{expense_inventory} = "";
595 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
596 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
599 $form->{amount} = {};
600 $form->{amount_cogs} = {};
602 foreach my $i (1 .. $form->{rowcount}) {
603 if ($form->{type} eq "credit_note") {
604 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
605 $form->{shipped} = 1;
607 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
612 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
613 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
614 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
616 if ($form->{storno}) {
617 $form->{"qty_$i"} *= -1;
620 if ($form->{"id_$i"}) {
623 if (defined($baseunits{$form->{"id_$i"}})) {
624 $item_unit = $baseunits{$form->{"id_$i"}};
627 $query = qq|SELECT unit FROM parts WHERE id = ?|;
628 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
629 $baseunits{$form->{"id_$i"}} = $item_unit;
632 if (defined($all_units->{$item_unit}->{factor})
633 && ($all_units->{$item_unit}->{factor} ne '')
634 && ($all_units->{$item_unit}->{factor} != 0)) {
635 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
639 $baseqty = $form->{"qty_$i"} * $basefactor;
641 my ($allocated, $taxrate) = (0, 0);
645 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
647 # keep entered selling price
649 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
651 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
653 my $decimalplaces = ($dec > 2) ? $dec : 2;
655 # undo discount formatting
656 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
659 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
661 # round linetotal to 2 decimal places
662 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
663 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
665 if ($form->{taxincluded}) {
666 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
667 $form->{"sellprice_$i"} =
668 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
670 $taxamount = $linetotal * $taxrate;
673 $netamount += $linetotal;
675 if ($taxamount != 0) {
677 $form->{amount}{ $form->{id} }{$_} +=
678 $taxamount * $form->{"${_}_rate"} / $taxrate
679 } split(/ /, $form->{"taxaccounts_$i"});
682 # add amount to income, $form->{amount}{trans_id}{accno}
683 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
685 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
686 $linetotal = $form->round_amount($linetotal, 2);
688 # this is the difference from the inventory
689 $invoicediff += ($amount - $linetotal);
691 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
694 $lastincomeaccno = $form->{"income_accno_$i"};
696 # adjust and round sellprice
697 $form->{"sellprice_$i"} =
698 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
701 next if $payments_only;
703 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
705 if ($form->{"assembly_$i"}) {
706 # record assembly item as allocated
707 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
710 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
714 # get pricegroup_id and save it
715 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
718 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
720 # save detail record in invoice table
722 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
723 sellprice, fxsellprice, discount, allocated, assemblyitem,
724 unit, deliverydate, project_id, serialnumber, pricegroup_id,
725 ordnumber, transdate, cusordnumber, base_qty, subtotal,
726 marge_percent, marge_total, lastcost,
727 price_factor_id, price_factor, marge_price_factor)
728 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
729 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
731 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
732 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
733 $form->{"sellprice_$i"}, $fxsellprice,
734 $form->{"discount_$i"}, $allocated, 'f',
735 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
736 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
737 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
738 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
739 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
740 $form->{"lastcost_$i"},
741 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
742 conv_i($form->{"marge_price_factor_$i"}));
743 do_query($form, $dbh, $query, @values);
745 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
747 qq|INSERT INTO licenseinvoice (trans_id, license_id)
748 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
749 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
750 do_query($form, $dbh, $query, @values);
753 CVar->save_custom_variables(module => 'IC',
754 sub_module => 'invoice',
755 trans_id => $invoice_id,
756 configs => $ic_cvar_configs,
758 name_prefix => 'ic_',
759 name_postfix => "_$i",
764 # total payments, don't move we need it here
765 for my $i (1 .. $form->{paidaccounts}) {
766 if ($form->{type} eq "credit_note") {
767 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
769 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
771 $form->{paid} += $form->{"paid_$i"};
772 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
775 my ($tax, $diff) = (0, 0);
777 $netamount = $form->round_amount($netamount, 2);
779 # figure out rounding errors for total amount vs netamount + taxes
780 if ($form->{taxincluded}) {
782 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
783 $diff += $amount - $netamount * $form->{exchangerate};
784 $netamount = $amount;
786 foreach my $item (split(/ /, $form->{taxaccounts})) {
787 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
788 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
789 $tax += $form->{amount}{ $form->{id} }{$item};
790 $netamount -= $form->{amount}{ $form->{id} }{$item};
793 $invoicediff += $diff;
794 ######## this only applies to tax included
795 if ($lastincomeaccno) {
796 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
800 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
801 $diff = $amount - $netamount * $form->{exchangerate};
802 $netamount = $amount;
803 foreach my $item (split(/ /, $form->{taxaccounts})) {
804 $form->{amount}{ $form->{id} }{$item} =
805 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
808 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
811 $amount - $form->{amount}{ $form->{id} }{$item} *
812 $form->{exchangerate};
813 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
814 $tax += $form->{amount}{ $form->{id} }{$item};
818 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
820 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
823 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
825 # update exchangerate
826 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
827 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
828 $form->{exchangerate}, 0);
831 $project_id = conv_i($form->{"globalproject_id"});
833 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
834 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
835 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
837 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
839 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
841 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
842 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
843 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
844 do_query($form, $dbh, $query, @values);
845 $form->{amount_cogs}{$trans_id}{$accno} = 0;
849 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
850 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
852 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
854 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
855 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
856 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
857 do_query($form, $dbh, $query, @values);
862 foreach my $trans_id (keys %{ $form->{amount} }) {
863 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
864 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
866 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
868 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
870 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
871 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
872 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
873 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
874 do_query($form, $dbh, $query, @values);
875 $form->{amount}{$trans_id}{$accno} = 0;
879 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
880 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
882 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
884 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
885 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
886 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
887 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
888 do_query($form, $dbh, $query, @values);
893 # deduct payment differences from diff
894 for my $i (1 .. $form->{paidaccounts}) {
895 if ($form->{"paid_$i"} != 0) {
897 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
898 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
902 # record payments and offsetting AR
903 if (!$form->{storno}) {
904 for my $i (1 .. $form->{paidaccounts}) {
906 next if ($form->{"paid_$i"} == 0);
908 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
909 $form->{"datepaid_$i"} = $form->{invdate}
910 unless ($form->{"datepaid_$i"});
911 $form->{datepaid} = $form->{"datepaid_$i"};
915 if ($form->{currency} eq $defaultcurrency) {
916 $form->{"exchangerate_$i"} = 1;
918 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
919 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
923 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
925 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
927 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
928 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
929 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
930 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
931 do_query($form, $dbh, $query, @values);
935 $form->{"paid_$i"} *= -1;
938 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
939 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
940 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
941 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
942 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
943 do_query($form, $dbh, $query, @values);
945 # exchangerate difference
946 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
947 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
951 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
952 $form->{"exchangerate_$i"};
954 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
957 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
963 # update exchange rate
964 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
965 $form->update_exchangerate($dbh, $form->{currency},
966 $form->{"datepaid_$i"},
967 $form->{"exchangerate_$i"}, 0);
971 } else { # if (!$form->{storno})
972 $form->{marge_total} *= -1;
975 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
977 if ($payments_only) {
978 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
979 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
981 $dbh->commit if !$provided_dbh;
983 $main::lxdebug->leave_sub();
987 # record exchange rate differences and gains/losses
988 foreach my $accno (keys %{ $form->{fx} }) {
989 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
991 ($form->{fx}{$accno}{$transdate} =
992 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
997 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
998 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
999 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
1000 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
1001 do_query($form, $dbh, $query, @values);
1006 $amount = $netamount + $tax;
1009 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1011 $query = qq|UPDATE ar set
1012 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1013 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1014 amount = ?, netamount = ?, paid = ?,
1015 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1016 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1017 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1018 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1019 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1020 cp_id = ?, marge_total = ?, marge_percent = ?,
1021 globalproject_id = ?, delivery_customer_id = ?,
1022 transaction_description = ?, delivery_vendor_id = ?,
1023 donumber = ?, invnumber_for_credit_note = ?
1025 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1026 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1027 $amount, $netamount, $form->{"paid"},
1028 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1029 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1030 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1031 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1032 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1033 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1034 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1035 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1036 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1037 conv_i($form->{"id"}));
1038 do_query($form, $dbh, $query, @values);
1041 if ($form->{storno}) {
1044 paid = paid + amount,
1046 intnotes = ? || intnotes
1048 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1049 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1053 $form->{name} = $form->{customer};
1054 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1056 if (!$form->{shipto_id}) {
1057 $form->add_shipto($dbh, $form->{id}, "AR");
1060 # save printed, emailed and queued
1061 $form->save_status($dbh);
1063 Common::webdav_folder($form);
1065 # Link this record to the records it was created from.
1066 RecordLinks->create_links('dbh' => $dbh,
1068 'from_table' => 'oe',
1069 'from_ids' => $form->{convert_from_oe_ids},
1071 'to_id' => $form->{id},
1073 delete $form->{convert_from_oe_ids};
1075 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1077 if (scalar @convert_from_do_ids) {
1078 DO->close_orders('dbh' => $dbh,
1079 'ids' => \@convert_from_do_ids);
1081 RecordLinks->create_links('dbh' => $dbh,
1083 'from_table' => 'delivery_orders',
1084 'from_ids' => \@convert_from_do_ids,
1086 'to_id' => $form->{id},
1089 delete $form->{convert_from_do_ids};
1091 ARAP->close_orders_if_billed('dbh' => $dbh,
1092 'arap_id' => $form->{id},
1096 $dbh->commit if !$provided_dbh;
1098 $main::lxdebug->leave_sub();
1103 sub _delete_payments {
1104 $main::lxdebug->enter_sub();
1106 my ($self, $form, $dbh) = @_;
1108 my @delete_acc_trans_ids;
1110 # Delete old payment entries from acc_trans.
1112 qq|SELECT acc_trans_id
1114 WHERE (trans_id = ?) AND fx_transaction
1118 SELECT at.acc_trans_id
1120 LEFT JOIN chart c ON (at.chart_id = c.id)
1121 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1122 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1125 qq|SELECT at.acc_trans_id
1127 LEFT JOIN chart c ON (at.chart_id = c.id)
1128 WHERE (trans_id = ?)
1129 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1130 ORDER BY at.acc_trans_id
1132 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1134 if (@delete_acc_trans_ids) {
1135 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1136 do_query($form, $dbh, $query);
1139 $main::lxdebug->leave_sub();
1143 $main::lxdebug->enter_sub();
1145 my ($self, $myconfig, $form, $locale) = @_;
1147 # connect to database, turn off autocommit
1148 my $dbh = $form->get_standard_dbh;
1151 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1153 $old_form = save_form();
1155 # Delete all entries in acc_trans from prior payments.
1156 $self->_delete_payments($form, $dbh);
1158 # Save the new payments the user made before cleaning up $form.
1159 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1161 # Clean up $form so that old content won't tamper the results.
1162 %keep_vars = map { $_, 1 } qw(login password id);
1163 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1165 # Retrieve the invoice from the database.
1166 $self->retrieve_invoice($myconfig, $form);
1168 # Set up the content of $form in the way that IS::post_invoice() expects.
1169 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1171 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1172 $item = $form->{invoice_details}->[$row - 1];
1174 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1176 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1179 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1181 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1183 # Restore the payment options from the user input.
1184 map { $form->{$_} = $payments{$_} } keys %payments;
1186 # Get the AR accno (which is normally done by Form::create_links()).
1190 LEFT JOIN chart c ON (at.chart_id = c.id)
1191 WHERE (trans_id = ?)
1192 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1193 ORDER BY at.acc_trans_id
1196 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1198 # Post the new payments.
1199 $self->post_invoice($myconfig, $form, $dbh, 1);
1201 restore_form($old_form);
1203 my $rc = $dbh->commit();
1205 $main::lxdebug->leave_sub();
1210 sub process_assembly {
1211 $main::lxdebug->enter_sub();
1213 my ($dbh, $form, $id, $totalqty) = @_;
1216 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1217 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1219 JOIN parts p ON (a.parts_id = p.id)
1221 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1223 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1227 $ref->{inventory_accno_id} *= 1;
1228 $ref->{expense_accno_id} *= 1;
1230 # multiply by number of assemblies
1231 $ref->{qty} *= $totalqty;
1233 if ($ref->{assembly}) {
1234 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1237 if ($ref->{inventory_accno_id}) {
1238 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1242 # save detail record for individual assembly item in invoice table
1244 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1245 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1246 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1247 do_query($form, $dbh, $query, @values);
1253 $main::lxdebug->leave_sub();
1257 $main::lxdebug->enter_sub();
1259 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1263 $form->{taxzone_id} *=1;
1264 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1265 my $taxzone_id = $form->{"taxzone_id"} * 1;
1267 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1268 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1269 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1270 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1271 FROM invoice i, parts p
1272 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1273 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1274 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1275 WHERE (i.parts_id = p.id)
1276 AND (i.parts_id = ?)
1277 AND ((i.base_qty + i.allocated) < 0)
1279 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1284 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1285 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1289 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1291 # total expenses and inventory
1292 # sellprice is the cost of the item
1293 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1295 if (!$::lx_office_conf{system}->{eur}) {
1296 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1298 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1299 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1300 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1302 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1303 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1309 last if (($totalqty -= $qty) <= 0);
1314 $main::lxdebug->leave_sub();
1319 sub reverse_invoice {
1320 $main::lxdebug->enter_sub();
1322 my ($dbh, $form) = @_;
1324 # reverse inventory items
1326 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1328 JOIN parts p ON (i.parts_id = p.id)
1329 WHERE i.trans_id = ?|;
1330 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1332 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1334 if ($ref->{inventory_accno_id}) {
1335 # de-allocated purchases
1337 qq|SELECT i.id, i.trans_id, i.allocated
1339 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1340 ORDER BY i.trans_id DESC|;
1341 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1343 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1344 my $qty = $ref->{qty};
1345 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1346 $qty = $inhref->{allocated};
1350 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1352 last if (($ref->{qty} -= $qty) <= 0);
1361 my @values = (conv_i($form->{id}));
1362 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1363 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1365 if ($form->{lizenzen}) {
1367 qq|DELETE FROM licenseinvoice
1368 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1369 do_query($form, $dbh, $query, @values);
1372 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1374 $main::lxdebug->leave_sub();
1377 sub delete_invoice {
1378 $main::lxdebug->enter_sub();
1380 my ($self, $myconfig, $form) = @_;
1382 # connect to database
1383 my $dbh = $form->get_standard_dbh;
1386 &reverse_invoice($dbh, $form);
1388 my @values = (conv_i($form->{id}));
1390 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1391 # zurückgesetzt werden. Vgl:
1392 # id | storno | storno_id | paid | amount
1393 #----+--------+-----------+---------+-----------
1394 # 18 | f | | 0.00000 | 119.00000
1396 # 18 | t | | 119.00000 | 119.00000
1398 if($form->{storno}){
1399 # storno_id auslesen und korrigieren
1400 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1401 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1405 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1407 # delete spool files
1408 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1410 # delete status entries
1411 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1413 my $rc = $dbh->commit;
1416 my $spool = $::lx_office_conf{paths}->{spool};
1417 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1420 $main::lxdebug->leave_sub();
1425 sub retrieve_invoice {
1426 $main::lxdebug->enter_sub();
1428 my ($self, $myconfig, $form) = @_;
1430 # connect to database
1431 my $dbh = $form->get_standard_dbh;
1433 my ($sth, $ref, $query);
1435 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1439 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1440 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1441 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1442 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1443 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1444 d.curr AS currencies
1448 $ref = selectfirst_hashref_query($form, $dbh, $query);
1449 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1452 my $id = conv_i($form->{id});
1455 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1459 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1460 a.orddate, a.quodate, a.globalproject_id,
1461 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1462 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1463 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1464 a.employee_id, a.salesman_id, a.payment_id,
1465 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1466 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1467 a.marge_total, a.marge_percent,
1470 LEFT JOIN employee e ON (e.id = a.employee_id)
1472 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1473 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1476 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1479 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1480 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1482 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1484 foreach my $vc (qw(customer vendor)) {
1485 next if !$form->{"delivery_${vc}_id"};
1486 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1489 # get printed, emailed
1490 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1491 $sth = prepare_execute_query($form, $dbh, $query, $id);
1493 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1494 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1495 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1496 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1499 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1501 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1502 : $form->{invdate} ? $dbh->quote($form->{invdate})
1506 my $taxzone_id = $form->{taxzone_id} *= 1;
1507 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1509 # retrieve individual items
1512 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1513 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1514 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1517 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1518 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1519 i.price_factor_id, i.price_factor, i.marge_price_factor,
1520 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1521 pr.projectnumber, pg.partsgroup, prg.pricegroup
1524 LEFT JOIN parts p ON (i.parts_id = p.id)
1525 LEFT JOIN project pr ON (i.project_id = pr.id)
1526 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1527 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1529 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1530 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1531 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1533 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1535 $sth = prepare_execute_query($form, $dbh, $query, $id);
1537 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1538 # Retrieve custom variables.
1539 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1541 sub_module => 'invoice',
1542 trans_id => $ref->{invoice_id},
1544 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1545 delete $ref->{invoice_id};
1547 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1548 delete($ref->{"part_inventory_accno_id"});
1550 foreach my $type (qw(inventory income expense)) {
1551 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1552 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1553 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1557 # get tax rates and description
1558 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1560 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1561 LEFT JOIN chart c ON (c.id = t.chart_id)
1563 (SELECT tk.tax_id FROM taxkeys tk
1564 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1565 AND startdate <= date($transdate)
1566 ORDER BY startdate DESC LIMIT 1)
1568 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1569 $ref->{taxaccounts} = "";
1571 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1573 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1577 $ref->{taxaccounts} .= "$ptr->{accno} ";
1579 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1580 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1581 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1582 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1583 $form->{taxaccounts} .= "$ptr->{accno} ";
1588 if ($form->{lizenzen}) {
1589 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1590 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1591 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1594 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1596 chop $ref->{taxaccounts};
1597 push @{ $form->{invoice_details} }, $ref;
1602 Common::webdav_folder($form);
1605 my $rc = $dbh->commit;
1607 $main::lxdebug->leave_sub();
1613 $main::lxdebug->enter_sub();
1615 my ($self, $myconfig, $form) = @_;
1617 # connect to database
1618 my $dbh = $form->get_standard_dbh;
1620 my $dateformat = $myconfig->{dateformat};
1621 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1623 my (@values, $duedate, $ref, $query);
1625 if ($form->{invdate}) {
1626 $duedate = "to_date(?, '$dateformat')";
1627 push @values, $form->{invdate};
1629 $duedate = "current_date";
1632 my $cid = conv_i($form->{customer_id});
1635 if ($form->{payment_id}) {
1636 $payment_id = "(pt.id = ?) OR";
1637 push @values, conv_i($form->{payment_id});
1643 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1644 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1645 c.street, c.zipcode, c.city, c.country,
1646 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1647 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1648 b.discount AS tradediscount, b.description AS business
1650 LEFT JOIN business b ON (b.id = c.business_id)
1651 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1654 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1656 delete $ref->{salesman_id} if !$ref->{salesman_id};
1658 map { $form->{$_} = $ref->{$_} } keys %$ref;
1661 qq|SELECT sum(amount - paid) AS dunning_amount
1663 WHERE (paid < amount)
1664 AND (customer_id = ?)
1665 AND (dunning_config_id IS NOT NULL)|;
1666 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1667 map { $form->{$_} = $ref->{$_} } keys %$ref;
1670 qq|SELECT dnn.dunning_description AS max_dunning_level
1671 FROM dunning_config dnn
1672 WHERE id IN (SELECT dunning_config_id
1674 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1675 ORDER BY dunning_level DESC LIMIT 1|;
1676 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1677 map { $form->{$_} = $ref->{$_} } keys %$ref;
1679 $form->{creditremaining} = $form->{creditlimit};
1680 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1681 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1682 $form->{creditremaining} -= $value;
1686 (SELECT e.buy FROM exchangerate e
1687 WHERE e.curr = o.curr
1688 AND e.transdate = o.transdate)
1690 WHERE o.customer_id = ?
1691 AND o.quotation = '0'
1692 AND o.closed = '0'|;
1693 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1695 while (my ($amount, $exch) = $sth->fetchrow_array) {
1696 $exch = 1 unless $exch;
1697 $form->{creditremaining} -= $amount * $exch;
1701 # get shipto if we did not converted an order or invoice
1702 if (!$form->{shipto}) {
1703 map { delete $form->{$_} }
1704 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1705 shiptostreet shiptozipcode shiptocity shiptocountry
1706 shiptocontact shiptophone shiptofax shiptoemail);
1708 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1709 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1711 map { $form->{$_} = $ref->{$_} } keys %$ref;
1714 # setup last accounts used for this customer
1715 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1717 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1719 JOIN acc_trans ac ON (ac.chart_id = c.id)
1720 JOIN ar a ON (a.id = ac.trans_id)
1721 WHERE a.customer_id = ?
1722 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1723 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1724 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1727 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1728 if ($ref->{category} eq 'I') {
1730 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1732 if ($form->{initial_transdate}) {
1734 qq|SELECT tk.tax_id, t.rate
1736 LEFT JOIN tax t ON tk.tax_id = t.id
1737 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1738 ORDER BY tk.startdate DESC
1740 my ($tax_id, $rate) =
1741 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1742 $form->{initial_transdate});
1743 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1746 if ($ref->{category} eq 'A') {
1747 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1751 $form->{rowcount} = $i if ($i && !$form->{type});
1754 $main::lxdebug->leave_sub();
1758 $main::lxdebug->enter_sub();
1760 my ($self, $myconfig, $form) = @_;
1762 # connect to database
1763 my $dbh = $form->get_standard_dbh;
1765 my $i = $form->{rowcount};
1767 my $where = qq|NOT p.obsolete = '1'|;
1770 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1771 my ($table, $field) = split m/\./, $column;
1772 next if !$form->{"${field}_${i}"};
1773 $where .= qq| AND lower(${column}) ILIKE ?|;
1774 push @values, '%' . $form->{"${field}_${i}"} . '%';
1777 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1778 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1779 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1780 push @values, $form->{"partnumber_$i"};
1783 if ($form->{"description_$i"}) {
1784 $where .= qq| ORDER BY p.description|;
1786 $where .= qq| ORDER BY p.partnumber|;
1790 if ($form->{type} eq "invoice") {
1792 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1793 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1797 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1801 my $taxzone_id = $form->{taxzone_id} * 1;
1802 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1806 p.id, p.partnumber, p.description, p.sellprice,
1807 p.listprice, p.inventory_accno_id, p.lastcost,
1809 c1.accno AS inventory_accno,
1810 c1.new_chart_id AS inventory_new_chart,
1811 date($transdate) - c1.valid_from AS inventory_valid,
1813 c2.accno AS income_accno,
1814 c2.new_chart_id AS income_new_chart,
1815 date($transdate) - c2.valid_from AS income_valid,
1817 c3.accno AS expense_accno,
1818 c3.new_chart_id AS expense_new_chart,
1819 date($transdate) - c3.valid_from AS expense_valid,
1821 p.unit, p.assembly, p.bin, p.onhand,
1822 p.notes AS partnotes, p.notes AS longdescription,
1823 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1826 pfac.factor AS price_factor,
1831 LEFT JOIN chart c1 ON
1832 ((SELECT inventory_accno_id
1833 FROM buchungsgruppen
1834 WHERE id = p.buchungsgruppen_id) = c1.id)
1835 LEFT JOIN chart c2 ON
1836 ((SELECT income_accno_id_${taxzone_id}
1837 FROM buchungsgruppen
1838 WHERE id = p.buchungsgruppen_id) = c2.id)
1839 LEFT JOIN chart c3 ON
1840 ((SELECT expense_accno_id_${taxzone_id}
1841 FROM buchungsgruppen
1842 WHERE id = p.buchungsgruppen_id) = c3.id)
1843 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1844 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1846 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1848 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1850 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1851 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1852 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1853 if (!$ref->{inventory_accno_id}) {
1854 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1856 delete($ref->{inventory_accno_id});
1858 foreach my $type (qw(inventory income expense)) {
1859 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1861 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1864 ($ref->{"${type}_accno"},
1865 $ref->{"${type}_new_chart"},
1866 $ref->{"${type}_valid"})
1867 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1871 if ($form->{payment_id} eq "") {
1872 $form->{payment_id} = $form->{part_payment_id};
1875 # get tax rates and description
1876 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1878 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1880 LEFT JOIN chart c ON (c.id = t.chart_id)
1884 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1886 ORDER BY startdate DESC
1889 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1890 my $stw = $dbh->prepare($query);
1891 $stw->execute(@values) || $form->dberror($query);
1893 $ref->{taxaccounts} = "";
1895 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1897 # if ($customertax{$ref->{accno}})
1898 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1902 $ref->{taxaccounts} .= "$ptr->{accno} ";
1904 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1905 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1906 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1907 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1908 $form->{taxaccounts} .= "$ptr->{accno} ";
1914 chop $ref->{taxaccounts};
1915 if ($form->{language_id}) {
1917 qq|SELECT tr.translation, tr.longdescription
1919 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1920 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1921 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1922 if ($translation ne "") {
1923 $ref->{description} = $translation;
1924 $ref->{longdescription} = $longdescription;
1928 qq|SELECT tr.translation, tr.longdescription
1930 WHERE tr.language_id IN
1933 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1936 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1937 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1938 if ($translation ne "") {
1939 $ref->{description} = $translation;
1940 $ref->{longdescription} = $longdescription;
1945 $ref->{onhand} *= 1;
1947 push @{ $form->{item_list} }, $ref;
1949 if ($form->{lizenzen}) {
1950 if ($ref->{inventory_accno} > 0) {
1954 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1955 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1956 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1957 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1965 foreach my $item (@{ $form->{item_list} }) {
1966 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1967 trans_id => $item->{id},
1971 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1974 $main::lxdebug->leave_sub();
1977 ##########################
1978 # get pricegroups from database
1979 # build up selected pricegroup
1980 # if an exchange rate - change price
1983 sub get_pricegroups_for_parts {
1985 $main::lxdebug->enter_sub();
1987 my ($self, $myconfig, $form) = @_;
1989 my $dbh = $form->get_standard_dbh;
1991 $form->{"PRICES"} = {};
1995 my $all_units = AM->retrieve_units($myconfig, $form);
1996 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1997 $form->{"PRICES"}{$i} = [];
1999 $id = $form->{"id_$i"};
2001 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2002 $id = $form->{"new_id_$i"};
2005 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2007 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2009 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2010 my $sellprice = $form->{"sellprice_$i"};
2011 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2012 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2013 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2015 my $price_new = $form->{"price_new_$i"};
2016 my $price_old = $form->{"price_old_$i"};
2018 if (!$form->{"unit_old_$i"}) {
2019 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2020 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2021 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2022 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2025 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2026 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2027 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2029 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2030 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2031 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2032 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2033 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2034 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2035 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2040 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2041 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2042 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2043 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2044 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2048 if (!$form->{"basefactor_$i"}) {
2049 $form->{"basefactor_$i"} = 1;
2055 sellprice AS default_sellprice,
2058 'selected' AS selected
2064 parts.sellprice AS default_sellprice,
2065 pricegroup.pricegroup,
2069 LEFT JOIN parts ON parts.id = parts_id
2070 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2072 ORDER BY pricegroup|;
2073 my @values = (conv_i($id), conv_i($id));
2074 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2076 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2078 $pkr->{selected} = '';
2080 # if there is an exchange rate change price
2081 if (($form->{exchangerate} * 1) != 0) {
2082 $pkr->{price} /= $form->{exchangerate};
2085 $pkr->{price} *= $form->{"basefactor_$i"};
2086 $pkr->{price} *= $basefactor;
2087 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2089 if ($selectedpricegroup_id eq undef) {
2090 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2091 # Case A: open old invoice, no pricegroup selected
2092 # Case B: add new article to invoice, no pricegroup selected
2094 # to distinguish case A and B the variable pricegroup_id_$i is used
2095 # for new articles this variable isn't defined, for loaded articles it is
2096 # sellprice can't be used, as it already has 0,00 set
2098 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2100 $pkr->{selected} = ' selected';
2102 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2103 and not defined $form->{"pricegroup_id_$i"}
2104 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2105 # for the case where pricegroup prices haven't been set
2107 # Case B: use default pricegroup of customer
2109 $pkr->{selected} = ' selected'; # unless $form->{selected};
2111 # no customer pricesgroup set
2112 if ($pkr->{price} == $pkr->{default_sellprice}) {
2114 $pkr->{price} = $form->{"sellprice_$i"};
2118 # this sub should not set anything and only return. --sschoeling, 20090506
2119 # is this correct? put in again... -- grichardson 20110119
2120 $form->{"sellprice_$i"} = $pkr->{price};
2123 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2124 $pkr->{price} = $form->{"sellprice_$i"};
2125 $pkr->{selected} = ' selected';
2129 # existing article: pricegroup or price changed
2130 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2131 if ($selectedpricegroup_id ne $pricegroup_old) {
2132 # pricegroup has changed
2133 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2134 $pkr->{selected} = ' selected';
2136 } elsif ( ($form->parse_amount($myconfig, $price_new)
2137 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2138 and ($price_new ne 0) and defined $price_new) {
2139 # sellprice has changed
2140 # when loading existing invoices $price_new is NULL
2141 if ($pkr->{pricegroup_id} == 0) {
2142 $pkr->{price} = $form->{"sellprice_$i"};
2143 $pkr->{selected} = ' selected';
2145 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2146 # neither sellprice nor pricegroup changed
2147 $pkr->{selected} = ' selected';
2148 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2149 # $pkr->{price} = $form->{"sellprice_$i"};
2151 $pkr->{price} = $form->{"sellprice_$i"};
2155 push @{ $form->{PRICES}{$i} }, $pkr;
2158 $form->{"basefactor_$i"} *= $basefactor;
2165 $main::lxdebug->leave_sub();
2169 $main::lxdebug->enter_sub();
2171 my ($self, $myconfig, $form, $table) = @_;
2173 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2175 # make sure there's no funny stuff in $table
2176 # ToDO: die when this happens and throw an error
2177 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2179 my $dbh = $form->get_standard_dbh;
2181 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2182 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2184 $main::lxdebug->leave_sub();
2190 $main::lxdebug->enter_sub();
2192 my ($self, $myconfig, $form, $table, $id) = @_;
2194 $main::lxdebug->leave_sub() and return 0 unless ($id);
2196 # make sure there's no funny stuff in $table
2197 # ToDO: die when this happens and throw an error
2198 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2200 my $dbh = $form->get_standard_dbh;
2202 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2203 my ($result) = selectrow_query($form, $dbh, $query, $id);
2205 $main::lxdebug->leave_sub();
2210 sub get_standard_accno_current_assets {
2211 $main::lxdebug->enter_sub();
2213 my ($self, $myconfig, $form) = @_;
2215 my $dbh = $form->get_standard_dbh;
2217 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2218 my ($result) = selectrow_query($form, $dbh, $query);
2220 $main::lxdebug->leave_sub();