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->dbconnect($myconfig);
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}->{unit} }, $form->{"unit_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
212 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
213 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
216 if ($form->{lizenzen}) {
217 if ($form->{"licensenumber_$i"}) {
218 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
219 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
220 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
221 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
224 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
225 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
230 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
232 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
233 my ($dec) = ($sellprice =~ /\.(\d+)/);
234 my $decimalplaces = max 2, length($dec);
236 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
237 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
238 my $linetotal = $form->round_amount($linetotal_exact, 2);
239 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
241 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
242 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
244 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
246 $linetotal = ($linetotal != 0) ? $linetotal : '';
248 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
249 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
251 $form->{total} += $linetotal;
252 $form->{nodiscount_total} += $nodiscount_linetotal;
253 $form->{discount_total} += $discount;
255 if ($subtotal_header) {
256 $discount_subtotal += $linetotal;
257 $nodiscount_subtotal += $nodiscount_linetotal;
260 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
261 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
264 $discount_subtotal = 0;
265 $nodiscount_subtotal = 0;
266 $subtotal_header = 0;
269 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
270 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
273 if (!$form->{"discount_$i"}) {
274 $nodiscount += $linetotal;
277 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
367 foreach my $item (sort keys %taxaccounts) {
368 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
371 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
377 for my $i (1 .. $form->{paidaccounts}) {
378 if ($form->{"paid_$i"}) {
379 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
382 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
383 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
384 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
385 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
387 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
390 if($form->{taxincluded}) {
391 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
394 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
397 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
398 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
399 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
400 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
402 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
403 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
405 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
406 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
408 $form->set_payment_options($myconfig, $form->{invdate});
410 $form->{username} = $myconfig->{name};
414 $main::lxdebug->leave_sub();
417 sub project_description {
418 $main::lxdebug->enter_sub();
420 my ($self, $dbh, $id) = @_;
421 my $form = \%main::form;
423 my $query = qq|SELECT description FROM project WHERE id = ?|;
424 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
426 $main::lxdebug->leave_sub();
431 sub customer_details {
432 $main::lxdebug->enter_sub();
434 my ($self, $myconfig, $form, @wanted_vars) = @_;
436 # connect to database
437 my $dbh = $form->dbconnect($myconfig);
439 my $language_id = $form->{language_id};
441 # get contact id, set it if nessessary
444 my @values = (conv_i($form->{customer_id}));
447 if ($form->{cp_id}) {
448 $where = qq| AND (cp.cp_id = ?) |;
449 push(@values, conv_i($form->{cp_id}));
452 # get rest for the customer
454 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
455 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
457 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
458 WHERE (ct.id = ?) $where
461 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
463 # remove id and taxincluded before copy back
464 delete @$ref{qw(id taxincluded)};
466 @wanted_vars = grep({ $_ } @wanted_vars);
467 if (scalar(@wanted_vars) > 0) {
469 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
470 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
473 map { $form->{$_} = $ref->{$_} } keys %$ref;
475 if ($form->{delivery_customer_id}) {
477 qq|SELECT *, notes as customernotes
481 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
483 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
486 if ($form->{delivery_vendor_id}) {
488 qq|SELECT *, notes as customernotes
492 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
494 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
497 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
499 'trans_id' => $form->{customer_id});
500 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
502 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
503 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
504 'language_id' => $language_id,
505 'allow_fallback' => 1);
510 $main::lxdebug->leave_sub();
514 $main::lxdebug->enter_sub();
516 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
518 # connect to database, turn off autocommit
519 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
521 my ($query, $sth, $null, $project_id, @values);
522 my $exchangerate = 0;
524 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
527 if (!$form->{employee_id}) {
528 $form->get_employee($dbh);
531 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
533 ($null, $form->{department_id}) = split(/--/, $form->{department});
535 my $all_units = AM->retrieve_units($myconfig, $form);
537 if (!$payments_only) {
539 &reverse_invoice($dbh, $form);
542 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
543 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
545 $query = qq|SELECT nextval('glid')|;
546 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
548 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
549 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
551 if (!$form->{invnumber}) {
553 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
554 "cnnumber" : "invnumber", $dbh);
559 my ($netamount, $invoicediff) = (0, 0);
560 my ($amount, $linetotal, $lastincomeaccno);
562 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
563 my $defaultcurrency = (split m/:/, $currencies)[0];
565 if ($form->{currency} eq $defaultcurrency) {
566 $form->{exchangerate} = 1;
568 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
571 $form->{exchangerate} =
574 : $form->parse_amount($myconfig, $form->{exchangerate});
576 $form->{expense_inventory} = "";
580 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
581 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
584 $form->{amount} = {};
585 $form->{amount_cogs} = {};
587 foreach my $i (1 .. $form->{rowcount}) {
588 if ($form->{type} eq "credit_note") {
589 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
590 $form->{shipped} = 1;
592 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
597 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
598 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
599 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
601 if ($form->{storno}) {
602 $form->{"qty_$i"} *= -1;
605 if ($form->{"id_$i"}) {
608 if (defined($baseunits{$form->{"id_$i"}})) {
609 $item_unit = $baseunits{$form->{"id_$i"}};
612 $query = qq|SELECT unit FROM parts WHERE id = ?|;
613 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
614 $baseunits{$form->{"id_$i"}} = $item_unit;
617 if (defined($all_units->{$item_unit}->{factor})
618 && ($all_units->{$item_unit}->{factor} ne '')
619 && ($all_units->{$item_unit}->{factor} != 0)) {
620 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
624 $baseqty = $form->{"qty_$i"} * $basefactor;
626 my ($allocated, $taxrate) = (0, 0);
630 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
632 # keep entered selling price
634 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
636 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
638 my $decimalplaces = ($dec > 2) ? $dec : 2;
640 # undo discount formatting
641 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
644 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
646 # round linetotal to 2 decimal places
647 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
648 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
650 if ($form->{taxincluded}) {
651 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
652 $form->{"sellprice_$i"} =
653 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
655 $taxamount = $linetotal * $taxrate;
658 $netamount += $linetotal;
660 if ($taxamount != 0) {
662 $form->{amount}{ $form->{id} }{$_} +=
663 $taxamount * $form->{"${_}_rate"} / $taxrate
664 } split(/ /, $form->{"taxaccounts_$i"});
667 # add amount to income, $form->{amount}{trans_id}{accno}
668 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
670 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
671 $linetotal = $form->round_amount($linetotal, 2);
673 # this is the difference from the inventory
674 $invoicediff += ($amount - $linetotal);
676 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
679 $lastincomeaccno = $form->{"income_accno_$i"};
681 # adjust and round sellprice
682 $form->{"sellprice_$i"} =
683 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
686 next if $payments_only;
688 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
690 if ($form->{"assembly_$i"}) {
691 # record assembly item as allocated
692 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
695 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
699 # get pricegroup_id and save it
700 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
703 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
705 # save detail record in invoice table
707 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
708 sellprice, fxsellprice, discount, allocated, assemblyitem,
709 unit, deliverydate, project_id, serialnumber, pricegroup_id,
710 ordnumber, transdate, cusordnumber, base_qty, subtotal,
711 marge_percent, marge_total, lastcost,
712 price_factor_id, price_factor, marge_price_factor)
713 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
714 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
716 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
717 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
718 $form->{"sellprice_$i"}, $fxsellprice,
719 $form->{"discount_$i"}, $allocated, 'f',
720 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
721 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
722 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
723 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
724 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
725 $form->{"lastcost_$i"},
726 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
727 conv_i($form->{"marge_price_factor_$i"}));
728 do_query($form, $dbh, $query, @values);
730 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
732 qq|INSERT INTO licenseinvoice (trans_id, license_id)
733 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
734 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
735 do_query($form, $dbh, $query, @values);
738 CVar->save_custom_variables(module => 'IC',
739 sub_module => 'invoice',
740 trans_id => $invoice_id,
741 configs => $ic_cvar_configs,
743 name_prefix => 'ic_',
744 name_postfix => "_$i",
749 # total payments, don't move we need it here
750 for my $i (1 .. $form->{paidaccounts}) {
751 if ($form->{type} eq "credit_note") {
752 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
754 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
756 $form->{paid} += $form->{"paid_$i"};
757 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
760 my ($tax, $diff) = (0, 0);
762 $netamount = $form->round_amount($netamount, 2);
764 # figure out rounding errors for total amount vs netamount + taxes
765 if ($form->{taxincluded}) {
767 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
768 $diff += $amount - $netamount * $form->{exchangerate};
769 $netamount = $amount;
771 foreach my $item (split(/ /, $form->{taxaccounts})) {
772 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
773 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
774 $tax += $form->{amount}{ $form->{id} }{$item};
775 $netamount -= $form->{amount}{ $form->{id} }{$item};
778 $invoicediff += $diff;
779 ######## this only applies to tax included
780 if ($lastincomeaccno) {
781 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
785 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
786 $diff = $amount - $netamount * $form->{exchangerate};
787 $netamount = $amount;
788 foreach my $item (split(/ /, $form->{taxaccounts})) {
789 $form->{amount}{ $form->{id} }{$item} =
790 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
793 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
796 $amount - $form->{amount}{ $form->{id} }{$item} *
797 $form->{exchangerate};
798 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
799 $tax += $form->{amount}{ $form->{id} }{$item};
803 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
805 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
808 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
810 # update exchangerate
811 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
812 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
813 $form->{exchangerate}, 0);
816 $project_id = conv_i($form->{"globalproject_id"});
818 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
819 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
820 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
822 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
824 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
826 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
827 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
828 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
829 do_query($form, $dbh, $query, @values);
830 $form->{amount_cogs}{$trans_id}{$accno} = 0;
834 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
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);
847 foreach my $trans_id (keys %{ $form->{amount} }) {
848 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
849 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
851 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
853 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
855 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
856 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
857 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
858 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
859 do_query($form, $dbh, $query, @values);
860 $form->{amount}{$trans_id}{$accno} = 0;
864 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
865 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
867 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
869 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
870 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
871 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
872 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
873 do_query($form, $dbh, $query, @values);
878 # deduct payment differences from diff
879 for my $i (1 .. $form->{paidaccounts}) {
880 if ($form->{"paid_$i"} != 0) {
882 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
883 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
887 # record payments and offsetting AR
888 if (!$form->{storno}) {
889 for my $i (1 .. $form->{paidaccounts}) {
891 next if ($form->{"paid_$i"} == 0);
893 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
894 $form->{"datepaid_$i"} = $form->{invdate}
895 unless ($form->{"datepaid_$i"});
896 $form->{datepaid} = $form->{"datepaid_$i"};
900 if ($form->{currency} eq $defaultcurrency) {
901 $form->{"exchangerate_$i"} = 1;
903 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
904 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
908 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
910 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
912 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
913 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
914 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
915 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
916 do_query($form, $dbh, $query, @values);
920 $form->{"paid_$i"} *= -1;
923 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
924 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
925 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
926 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
927 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
928 do_query($form, $dbh, $query, @values);
930 # exchangerate difference
931 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
932 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
936 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
937 $form->{"exchangerate_$i"};
939 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
942 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
948 # update exchange rate
949 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
950 $form->update_exchangerate($dbh, $form->{currency},
951 $form->{"datepaid_$i"},
952 $form->{"exchangerate_$i"}, 0);
956 } else { # if (!$form->{storno})
957 $form->{marge_total} *= -1;
960 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
962 if ($payments_only) {
963 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
964 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
966 $dbh->commit if !$provided_dbh;
968 $main::lxdebug->leave_sub();
972 # record exchange rate differences and gains/losses
973 foreach my $accno (keys %{ $form->{fx} }) {
974 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
976 ($form->{fx}{$accno}{$transdate} =
977 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
982 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
983 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
984 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
985 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
986 do_query($form, $dbh, $query, @values);
991 $amount = $netamount + $tax;
994 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
996 $query = qq|UPDATE ar set
997 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
998 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
999 amount = ?, netamount = ?, paid = ?,
1000 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1001 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1002 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1003 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1004 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1005 cp_id = ?, marge_total = ?, marge_percent = ?,
1006 globalproject_id = ?, delivery_customer_id = ?,
1007 transaction_description = ?, delivery_vendor_id = ?,
1010 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1011 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1012 $amount, $netamount, $form->{"paid"},
1013 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1014 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1015 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1016 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1017 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1018 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1019 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1020 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1021 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
1022 conv_i($form->{"id"}));
1023 do_query($form, $dbh, $query, @values);
1026 if ($form->{storno}) {
1029 paid = paid + amount,
1031 intnotes = ? || intnotes
1033 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1034 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1038 $form->{name} = $form->{customer};
1039 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1041 if (!$form->{shipto_id}) {
1042 $form->add_shipto($dbh, $form->{id}, "AR");
1045 # save printed, emailed and queued
1046 $form->save_status($dbh);
1048 Common::webdav_folder($form);
1050 # Link this record to the records it was created from.
1051 RecordLinks->create_links('dbh' => $dbh,
1053 'from_table' => 'oe',
1054 'from_ids' => $form->{convert_from_oe_ids},
1056 'to_id' => $form->{id},
1058 delete $form->{convert_from_oe_ids};
1060 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1062 if (scalar @convert_from_do_ids) {
1063 DO->close_orders('dbh' => $dbh,
1064 'ids' => \@convert_from_do_ids);
1066 RecordLinks->create_links('dbh' => $dbh,
1068 'from_table' => 'delivery_orders',
1069 'from_ids' => \@convert_from_do_ids,
1071 'to_id' => $form->{id},
1074 delete $form->{convert_from_do_ids};
1076 ARAP->close_orders_if_billed('dbh' => $dbh,
1077 'arap_id' => $form->{id},
1081 $dbh->commit if !$provided_dbh;
1083 $main::lxdebug->leave_sub();
1088 sub _delete_payments {
1089 $main::lxdebug->enter_sub();
1091 my ($self, $form, $dbh) = @_;
1093 my @delete_acc_trans_ids;
1095 # Delete old payment entries from acc_trans.
1097 qq|SELECT acc_trans_id
1099 WHERE (trans_id = ?) AND fx_transaction
1103 SELECT at.acc_trans_id
1105 LEFT JOIN chart c ON (at.chart_id = c.id)
1106 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1107 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1110 qq|SELECT at.acc_trans_id
1112 LEFT JOIN chart c ON (at.chart_id = c.id)
1113 WHERE (trans_id = ?)
1114 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1115 ORDER BY at.acc_trans_id
1117 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1119 if (@delete_acc_trans_ids) {
1120 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1121 do_query($form, $dbh, $query);
1124 $main::lxdebug->leave_sub();
1128 $main::lxdebug->enter_sub();
1130 my ($self, $myconfig, $form, $locale) = @_;
1132 # connect to database, turn off autocommit
1133 my $dbh = $form->dbconnect_noauto($myconfig);
1135 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1137 $old_form = save_form();
1139 # Delete all entries in acc_trans from prior payments.
1140 $self->_delete_payments($form, $dbh);
1142 # Save the new payments the user made before cleaning up $form.
1143 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1145 # Clean up $form so that old content won't tamper the results.
1146 %keep_vars = map { $_, 1 } qw(login password id);
1147 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1149 # Retrieve the invoice from the database.
1150 $self->retrieve_invoice($myconfig, $form);
1152 # Set up the content of $form in the way that IS::post_invoice() expects.
1153 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1155 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1156 $item = $form->{invoice_details}->[$row - 1];
1158 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1160 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1163 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1165 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1167 # Restore the payment options from the user input.
1168 map { $form->{$_} = $payments{$_} } keys %payments;
1170 # Get the AR accno (which is normally done by Form::create_links()).
1174 LEFT JOIN chart c ON (at.chart_id = c.id)
1175 WHERE (trans_id = ?)
1176 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1177 ORDER BY at.acc_trans_id
1180 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1182 # Post the new payments.
1183 $self->post_invoice($myconfig, $form, $dbh, 1);
1185 restore_form($old_form);
1187 my $rc = $dbh->commit();
1190 $main::lxdebug->leave_sub();
1195 sub process_assembly {
1196 $main::lxdebug->enter_sub();
1198 my ($dbh, $form, $id, $totalqty) = @_;
1201 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1202 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1204 JOIN parts p ON (a.parts_id = p.id)
1206 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1208 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1212 $ref->{inventory_accno_id} *= 1;
1213 $ref->{expense_accno_id} *= 1;
1215 # multiply by number of assemblies
1216 $ref->{qty} *= $totalqty;
1218 if ($ref->{assembly}) {
1219 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1222 if ($ref->{inventory_accno_id}) {
1223 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1227 # save detail record for individual assembly item in invoice table
1229 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1230 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1231 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1232 do_query($form, $dbh, $query, @values);
1238 $main::lxdebug->leave_sub();
1242 $main::lxdebug->enter_sub();
1244 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1248 $form->{taxzone_id} *=1;
1249 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1250 my $taxzone_id = $form->{"taxzone_id"} * 1;
1252 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1253 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1254 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1255 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1256 FROM invoice i, parts p
1257 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1258 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1259 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1260 WHERE (i.parts_id = p.id)
1261 AND (i.parts_id = ?)
1262 AND ((i.base_qty + i.allocated) < 0)
1264 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1269 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1270 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1274 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1276 # total expenses and inventory
1277 # sellprice is the cost of the item
1278 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1280 if (!$::lx_office_conf{system}->{eur}) {
1281 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1283 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1284 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1285 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1287 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1288 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1294 last if (($totalqty -= $qty) <= 0);
1299 $main::lxdebug->leave_sub();
1304 sub reverse_invoice {
1305 $main::lxdebug->enter_sub();
1307 my ($dbh, $form) = @_;
1309 # reverse inventory items
1311 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1313 JOIN parts p ON (i.parts_id = p.id)
1314 WHERE i.trans_id = ?|;
1315 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1317 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1319 if ($ref->{inventory_accno_id}) {
1320 # de-allocated purchases
1322 qq|SELECT i.id, i.trans_id, i.allocated
1324 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1325 ORDER BY i.trans_id DESC|;
1326 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1328 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1329 my $qty = $ref->{qty};
1330 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1331 $qty = $inhref->{allocated};
1335 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1337 last if (($ref->{qty} -= $qty) <= 0);
1346 my @values = (conv_i($form->{id}));
1347 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1348 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1350 if ($form->{lizenzen}) {
1352 qq|DELETE FROM licenseinvoice
1353 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1354 do_query($form, $dbh, $query, @values);
1357 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1359 $main::lxdebug->leave_sub();
1362 sub delete_invoice {
1363 $main::lxdebug->enter_sub();
1365 my ($self, $myconfig, $form) = @_;
1367 # connect to database
1368 my $dbh = $form->dbconnect_noauto($myconfig);
1370 &reverse_invoice($dbh, $form);
1372 my @values = (conv_i($form->{id}));
1374 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1375 # zurückgesetzt werden. Vgl:
1376 # id | storno | storno_id | paid | amount
1377 #----+--------+-----------+---------+-----------
1378 # 18 | f | | 0.00000 | 119.00000
1380 # 18 | t | | 119.00000 | 119.00000
1382 if($form->{storno}){
1383 # storno_id auslesen und korrigieren
1384 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1385 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1389 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1391 # delete spool files
1392 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1394 # delete status entries
1395 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1397 my $rc = $dbh->commit;
1401 my $spool = $::lx_office_conf{paths}->{spool};
1402 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1405 $main::lxdebug->leave_sub();
1410 sub retrieve_invoice {
1411 $main::lxdebug->enter_sub();
1413 my ($self, $myconfig, $form) = @_;
1415 # connect to database
1416 my $dbh = $form->get_standard_dbh;
1418 my ($sth, $ref, $query);
1420 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1424 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1425 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1426 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1427 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1428 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1429 d.curr AS currencies
1433 $ref = selectfirst_hashref_query($form, $dbh, $query);
1434 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1437 my $id = conv_i($form->{id});
1440 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1444 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1445 a.orddate, a.quodate, a.globalproject_id,
1446 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1447 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1448 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1449 a.employee_id, a.salesman_id, a.payment_id,
1450 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1451 a.transaction_description,
1452 a.marge_total, a.marge_percent,
1453 e.name AS employee, a.donumber
1455 LEFT JOIN employee e ON (e.id = a.employee_id)
1457 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1458 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1461 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1464 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1465 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1467 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1469 foreach my $vc (qw(customer vendor)) {
1470 next if !$form->{"delivery_${vc}_id"};
1471 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1474 # get printed, emailed
1475 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1476 $sth = prepare_execute_query($form, $dbh, $query, $id);
1478 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1479 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1480 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1481 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1484 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1486 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1487 : $form->{invdate} ? $dbh->quote($form->{invdate})
1491 my $taxzone_id = $form->{taxzone_id} *= 1;
1492 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1494 # retrieve individual items
1497 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1498 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1499 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1502 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1503 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1504 i.price_factor_id, i.price_factor, i.marge_price_factor,
1505 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1506 pr.projectnumber, pg.partsgroup, prg.pricegroup
1509 LEFT JOIN parts p ON (i.parts_id = p.id)
1510 LEFT JOIN project pr ON (i.project_id = pr.id)
1511 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1512 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1514 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1515 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1516 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1518 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1520 $sth = prepare_execute_query($form, $dbh, $query, $id);
1522 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1523 # Retrieve custom variables.
1524 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1526 sub_module => 'invoice',
1527 trans_id => $ref->{invoice_id},
1529 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1530 delete $ref->{invoice_id};
1532 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1533 delete($ref->{"part_inventory_accno_id"});
1535 foreach my $type (qw(inventory income expense)) {
1536 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1537 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1538 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1542 # get tax rates and description
1543 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1545 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1546 LEFT JOIN chart c ON (c.id = t.chart_id)
1548 (SELECT tk.tax_id FROM taxkeys tk
1549 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1550 AND startdate <= date($transdate)
1551 ORDER BY startdate DESC LIMIT 1)
1553 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1554 $ref->{taxaccounts} = "";
1556 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1558 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1562 $ref->{taxaccounts} .= "$ptr->{accno} ";
1564 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1565 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1566 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1567 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1568 $form->{taxaccounts} .= "$ptr->{accno} ";
1573 if ($form->{lizenzen}) {
1574 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1575 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1576 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1579 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1581 chop $ref->{taxaccounts};
1582 push @{ $form->{invoice_details} }, $ref;
1587 Common::webdav_folder($form);
1590 my $rc = $dbh->commit;
1592 $main::lxdebug->leave_sub();
1598 $main::lxdebug->enter_sub();
1600 my ($self, $myconfig, $form) = @_;
1602 # connect to database
1603 my $dbh = $form->get_standard_dbh;
1605 my $dateformat = $myconfig->{dateformat};
1606 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1608 my (@values, $duedate, $ref, $query);
1610 if ($form->{invdate}) {
1611 $duedate = "to_date(?, '$dateformat')";
1612 push @values, $form->{invdate};
1614 $duedate = "current_date";
1617 my $cid = conv_i($form->{customer_id});
1620 if ($form->{payment_id}) {
1621 $payment_id = "(pt.id = ?) OR";
1622 push @values, conv_i($form->{payment_id});
1628 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1629 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1630 c.street, c.zipcode, c.city, c.country,
1631 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1632 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1633 b.discount AS tradediscount, b.description AS business
1635 LEFT JOIN business b ON (b.id = c.business_id)
1636 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1639 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1641 delete $ref->{salesman_id} if !$ref->{salesman_id};
1643 map { $form->{$_} = $ref->{$_} } keys %$ref;
1646 qq|SELECT sum(amount - paid) AS dunning_amount
1648 WHERE (paid < amount)
1649 AND (customer_id = ?)
1650 AND (dunning_config_id IS NOT NULL)|;
1651 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1652 map { $form->{$_} = $ref->{$_} } keys %$ref;
1655 qq|SELECT dnn.dunning_description AS max_dunning_level
1656 FROM dunning_config dnn
1657 WHERE id IN (SELECT dunning_config_id
1659 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1660 ORDER BY dunning_level DESC LIMIT 1|;
1661 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1662 map { $form->{$_} = $ref->{$_} } keys %$ref;
1664 $form->{creditremaining} = $form->{creditlimit};
1665 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1666 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1667 $form->{creditremaining} -= $value;
1671 (SELECT e.buy FROM exchangerate e
1672 WHERE e.curr = o.curr
1673 AND e.transdate = o.transdate)
1675 WHERE o.customer_id = ?
1676 AND o.quotation = '0'
1677 AND o.closed = '0'|;
1678 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1680 while (my ($amount, $exch) = $sth->fetchrow_array) {
1681 $exch = 1 unless $exch;
1682 $form->{creditremaining} -= $amount * $exch;
1686 # get shipto if we did not converted an order or invoice
1687 if (!$form->{shipto}) {
1688 map { delete $form->{$_} }
1689 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1690 shiptostreet shiptozipcode shiptocity shiptocountry
1691 shiptocontact shiptophone shiptofax shiptoemail);
1693 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1694 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1696 map { $form->{$_} = $ref->{$_} } keys %$ref;
1699 # setup last accounts used for this customer
1700 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1702 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1704 JOIN acc_trans ac ON (ac.chart_id = c.id)
1705 JOIN ar a ON (a.id = ac.trans_id)
1706 WHERE a.customer_id = ?
1707 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1708 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1709 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1712 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1713 if ($ref->{category} eq 'I') {
1715 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1717 if ($form->{initial_transdate}) {
1719 qq|SELECT tk.tax_id, t.rate
1721 LEFT JOIN tax t ON tk.tax_id = t.id
1722 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1723 ORDER BY tk.startdate DESC
1725 my ($tax_id, $rate) =
1726 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1727 $form->{initial_transdate});
1728 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1731 if ($ref->{category} eq 'A') {
1732 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1736 $form->{rowcount} = $i if ($i && !$form->{type});
1739 $main::lxdebug->leave_sub();
1743 $main::lxdebug->enter_sub();
1745 my ($self, $myconfig, $form) = @_;
1747 # connect to database
1748 my $dbh = $form->dbconnect($myconfig);
1750 my $i = $form->{rowcount};
1752 my $where = qq|NOT p.obsolete = '1'|;
1755 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1756 my ($table, $field) = split m/\./, $column;
1757 next if !$form->{"${field}_${i}"};
1758 $where .= qq| AND lower(${column}) ILIKE ?|;
1759 push @values, '%' . $form->{"${field}_${i}"} . '%';
1762 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1763 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1764 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1765 push @values, $form->{"partnumber_$i"};
1768 if ($form->{"description_$i"}) {
1769 $where .= qq| ORDER BY p.description|;
1771 $where .= qq| ORDER BY p.partnumber|;
1775 if ($form->{type} eq "invoice") {
1777 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1778 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1782 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1786 my $taxzone_id = $form->{taxzone_id} * 1;
1787 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1791 p.id, p.partnumber, p.description, p.sellprice,
1792 p.listprice, p.inventory_accno_id, p.lastcost,
1794 c1.accno AS inventory_accno,
1795 c1.new_chart_id AS inventory_new_chart,
1796 date($transdate) - c1.valid_from AS inventory_valid,
1798 c2.accno AS income_accno,
1799 c2.new_chart_id AS income_new_chart,
1800 date($transdate) - c2.valid_from AS income_valid,
1802 c3.accno AS expense_accno,
1803 c3.new_chart_id AS expense_new_chart,
1804 date($transdate) - c3.valid_from AS expense_valid,
1806 p.unit, p.assembly, p.bin, p.onhand,
1807 p.notes AS partnotes, p.notes AS longdescription,
1808 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1811 pfac.factor AS price_factor,
1816 LEFT JOIN chart c1 ON
1817 ((SELECT inventory_accno_id
1818 FROM buchungsgruppen
1819 WHERE id = p.buchungsgruppen_id) = c1.id)
1820 LEFT JOIN chart c2 ON
1821 ((SELECT income_accno_id_${taxzone_id}
1822 FROM buchungsgruppen
1823 WHERE id = p.buchungsgruppen_id) = c2.id)
1824 LEFT JOIN chart c3 ON
1825 ((SELECT expense_accno_id_${taxzone_id}
1826 FROM buchungsgruppen
1827 WHERE id = p.buchungsgruppen_id) = c3.id)
1828 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1829 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1831 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1833 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1835 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1836 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1837 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1838 if (!$ref->{inventory_accno_id}) {
1839 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1841 delete($ref->{inventory_accno_id});
1843 foreach my $type (qw(inventory income expense)) {
1844 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1846 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1849 ($ref->{"${type}_accno"},
1850 $ref->{"${type}_new_chart"},
1851 $ref->{"${type}_valid"})
1852 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1856 if ($form->{payment_id} eq "") {
1857 $form->{payment_id} = $form->{part_payment_id};
1860 # get tax rates and description
1861 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1863 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1865 LEFT JOIN chart c ON (c.id = t.chart_id)
1869 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1871 ORDER BY startdate DESC
1874 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1875 my $stw = $dbh->prepare($query);
1876 $stw->execute(@values) || $form->dberror($query);
1878 $ref->{taxaccounts} = "";
1880 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1882 # if ($customertax{$ref->{accno}})
1883 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1887 $ref->{taxaccounts} .= "$ptr->{accno} ";
1889 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1890 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1891 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1892 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1893 $form->{taxaccounts} .= "$ptr->{accno} ";
1899 chop $ref->{taxaccounts};
1900 if ($form->{language_id}) {
1902 qq|SELECT tr.translation, tr.longdescription
1904 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1905 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1906 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1907 if ($translation ne "") {
1908 $ref->{description} = $translation;
1909 $ref->{longdescription} = $longdescription;
1913 qq|SELECT tr.translation, tr.longdescription
1915 WHERE tr.language_id IN
1918 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1921 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1922 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1923 if ($translation ne "") {
1924 $ref->{description} = $translation;
1925 $ref->{longdescription} = $longdescription;
1930 $ref->{onhand} *= 1;
1932 push @{ $form->{item_list} }, $ref;
1934 if ($form->{lizenzen}) {
1935 if ($ref->{inventory_accno} > 0) {
1939 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1940 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1941 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1942 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1950 foreach my $item (@{ $form->{item_list} }) {
1951 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1952 trans_id => $item->{id},
1956 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1961 $main::lxdebug->leave_sub();
1964 ##########################
1965 # get pricegroups from database
1966 # build up selected pricegroup
1967 # if an exchange rate - change price
1970 sub get_pricegroups_for_parts {
1972 $main::lxdebug->enter_sub();
1974 my ($self, $myconfig, $form) = @_;
1976 my $dbh = $form->dbconnect($myconfig);
1978 $form->{"PRICES"} = {};
1982 my $all_units = AM->retrieve_units($myconfig, $form);
1983 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1984 $form->{"PRICES"}{$i} = [];
1986 $id = $form->{"id_$i"};
1988 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1989 $id = $form->{"new_id_$i"};
1992 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1994 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1996 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
1997 my $sellprice = $form->{"sellprice_$i"};
1998 my $pricegroup_id = $form->{"pricegroup_id_$i"};
1999 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2000 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2002 my $price_new = $form->{"price_new_$i"};
2003 my $price_old = $form->{"price_old_$i"};
2005 if (!$form->{"unit_old_$i"}) {
2006 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2007 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2008 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2009 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2012 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2013 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2014 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2016 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2017 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2018 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2019 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2020 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2021 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2022 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2027 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2028 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2029 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2030 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2031 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2035 if (!$form->{"basefactor_$i"}) {
2036 $form->{"basefactor_$i"} = 1;
2042 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2043 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2053 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2055 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2056 'selected' AS selected
2059 ORDER BY pricegroup|;
2060 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2061 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2063 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2065 $pkr->{selected} = '';
2067 # if there is an exchange rate change price
2068 if (($form->{exchangerate} * 1) != 0) {
2069 $pkr->{price} /= $form->{exchangerate};
2072 $pkr->{price} *= $form->{"basefactor_$i"};
2073 $pkr->{price} *= $basefactor;
2074 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2076 if ($selectedpricegroup_id eq undef) {
2077 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2078 # Case A: open old invoice, no pricegroup selected
2079 # Case B: add new article to invoice, no pricegroup selected
2081 # to distinguish case A and B the variable pricegroup_id_$i is used
2082 # for new articles this variable isn't defined, for loaded articles it is
2083 # sellprice can't be used, as it already has 0,00 set
2085 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2087 $pkr->{selected} = ' selected';
2089 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2090 and not defined $form->{"pricegroup_id_$i"}
2091 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2092 # for the case where pricegroup prices haven't been set
2094 # Case B: use default pricegroup of customer
2096 $pkr->{selected} = ' selected'; # unless $form->{selected};
2098 # no customer pricesgroup set
2099 if ($pkr->{price} == $pkr->{default_sellprice}) {
2101 $pkr->{price} = $form->{"sellprice_$i"};
2105 # this sub should not set anything and only return. --sschoeling, 20090506
2106 # is this correct? put in again... -- grichardson 20110119
2107 $form->{"sellprice_$i"} = $pkr->{price};
2110 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2111 $pkr->{price} = $form->{"sellprice_$i"};
2112 $pkr->{selected} = ' selected';
2116 # existing article: pricegroup or price changed
2117 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2118 if ($selectedpricegroup_id ne $pricegroup_old) {
2119 # pricegroup has changed
2120 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2121 $pkr->{selected} = ' selected';
2123 } elsif ( ($form->parse_amount($myconfig, $price_new)
2124 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2125 and ($price_new ne 0) and defined $price_new) {
2126 # sellprice has changed
2127 # when loading existing invoices $price_new is NULL
2128 if ($pkr->{pricegroup_id} == 0) {
2129 $pkr->{price} = $form->{"sellprice_$i"};
2130 $pkr->{selected} = ' selected';
2132 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2133 # neither sellprice nor pricegroup changed
2134 $pkr->{selected} = ' selected';
2135 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2136 # $pkr->{price} = $form->{"sellprice_$i"};
2138 $pkr->{price} = $form->{"sellprice_$i"};
2142 push @{ $form->{PRICES}{$i} }, $pkr;
2145 $form->{"basefactor_$i"} *= $basefactor;
2154 $main::lxdebug->leave_sub();
2158 $main::lxdebug->enter_sub();
2160 my ($self, $myconfig, $form, $table) = @_;
2162 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2164 # make sure there's no funny stuff in $table
2165 # ToDO: die when this happens and throw an error
2166 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2168 my $dbh = $form->dbconnect($myconfig);
2170 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2171 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2175 $main::lxdebug->leave_sub();
2181 $main::lxdebug->enter_sub();
2183 my ($self, $myconfig, $form, $table, $id) = @_;
2185 $main::lxdebug->leave_sub() and return 0 unless ($id);
2187 # make sure there's no funny stuff in $table
2188 # ToDO: die when this happens and throw an error
2189 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2191 my $dbh = $form->dbconnect($myconfig);
2193 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2194 my ($result) = selectrow_query($form, $dbh, $query, $id);
2198 $main::lxdebug->leave_sub();
2203 sub get_standard_accno_current_assets {
2204 $main::lxdebug->enter_sub();
2206 my ($self, $myconfig, $form) = @_;
2208 my $dbh = $form->dbconnect($myconfig);
2210 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2211 my ($result) = selectrow_query($form, $dbh, $query);
2215 $main::lxdebug->leave_sub();