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 if (!$provided_dbh) {
971 $main::lxdebug->leave_sub();
975 # record exchange rate differences and gains/losses
976 foreach my $accno (keys %{ $form->{fx} }) {
977 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
979 ($form->{fx}{$accno}{$transdate} =
980 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
985 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
986 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
987 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
988 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
989 do_query($form, $dbh, $query, @values);
994 $amount = $netamount + $tax;
997 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
999 $query = qq|UPDATE ar set
1000 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1001 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1002 amount = ?, netamount = ?, paid = ?,
1003 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1004 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1005 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1006 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1007 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1008 cp_id = ?, marge_total = ?, marge_percent = ?,
1009 globalproject_id = ?, delivery_customer_id = ?,
1010 transaction_description = ?, delivery_vendor_id = ?,
1013 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1014 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1015 $amount, $netamount, $form->{"paid"},
1016 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1017 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1018 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1019 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1020 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1021 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1022 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1023 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1024 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
1025 conv_i($form->{"id"}));
1026 do_query($form, $dbh, $query, @values);
1029 if ($form->{storno}) {
1032 paid = paid + amount,
1034 intnotes = ? || intnotes
1036 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1037 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1041 $form->{name} = $form->{customer};
1042 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1044 if (!$form->{shipto_id}) {
1045 $form->add_shipto($dbh, $form->{id}, "AR");
1048 # save printed, emailed and queued
1049 $form->save_status($dbh);
1051 Common::webdav_folder($form) if ($main::webdav);
1053 # Link this record to the records it was created from.
1054 RecordLinks->create_links('dbh' => $dbh,
1056 'from_table' => 'oe',
1057 'from_ids' => $form->{convert_from_oe_ids},
1059 'to_id' => $form->{id},
1061 delete $form->{convert_from_oe_ids};
1063 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1065 if (scalar @convert_from_do_ids) {
1066 DO->close_orders('dbh' => $dbh,
1067 'ids' => \@convert_from_do_ids);
1069 RecordLinks->create_links('dbh' => $dbh,
1071 'from_table' => 'delivery_orders',
1072 'from_ids' => \@convert_from_do_ids,
1074 'to_id' => $form->{id},
1077 delete $form->{convert_from_do_ids};
1079 ARAP->close_orders_if_billed('dbh' => $dbh,
1080 'arap_id' => $form->{id},
1084 if (!$provided_dbh) {
1089 $main::lxdebug->leave_sub();
1094 sub _delete_payments {
1095 $main::lxdebug->enter_sub();
1097 my ($self, $form, $dbh) = @_;
1099 my @delete_acc_trans_ids;
1101 # Delete old payment entries from acc_trans.
1103 qq|SELECT acc_trans_id
1105 WHERE (trans_id = ?) AND fx_transaction
1109 SELECT at.acc_trans_id
1111 LEFT JOIN chart c ON (at.chart_id = c.id)
1112 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1113 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1116 qq|SELECT at.acc_trans_id
1118 LEFT JOIN chart c ON (at.chart_id = c.id)
1119 WHERE (trans_id = ?)
1120 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1121 ORDER BY at.acc_trans_id
1123 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1125 if (@delete_acc_trans_ids) {
1126 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1127 do_query($form, $dbh, $query);
1130 $main::lxdebug->leave_sub();
1134 $main::lxdebug->enter_sub();
1136 my ($self, $myconfig, $form, $locale) = @_;
1138 # connect to database, turn off autocommit
1139 my $dbh = $form->dbconnect_noauto($myconfig);
1141 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1143 $old_form = save_form();
1145 # Delete all entries in acc_trans from prior payments.
1146 $self->_delete_payments($form, $dbh);
1148 # Save the new payments the user made before cleaning up $form.
1149 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1151 # Clean up $form so that old content won't tamper the results.
1152 %keep_vars = map { $_, 1 } qw(login password id);
1153 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1155 # Retrieve the invoice from the database.
1156 $self->retrieve_invoice($myconfig, $form);
1158 # Set up the content of $form in the way that IS::post_invoice() expects.
1159 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1161 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1162 $item = $form->{invoice_details}->[$row - 1];
1164 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1166 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1169 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1171 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1173 # Restore the payment options from the user input.
1174 map { $form->{$_} = $payments{$_} } keys %payments;
1176 # Get the AR accno (which is normally done by Form::create_links()).
1180 LEFT JOIN chart c ON (at.chart_id = c.id)
1181 WHERE (trans_id = ?)
1182 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1183 ORDER BY at.acc_trans_id
1186 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1188 # Post the new payments.
1189 $self->post_invoice($myconfig, $form, $dbh, 1);
1191 restore_form($old_form);
1193 my $rc = $dbh->commit();
1196 $main::lxdebug->leave_sub();
1201 sub process_assembly {
1202 $main::lxdebug->enter_sub();
1204 my ($dbh, $form, $id, $totalqty) = @_;
1207 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1208 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1210 JOIN parts p ON (a.parts_id = p.id)
1212 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1214 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1218 $ref->{inventory_accno_id} *= 1;
1219 $ref->{expense_accno_id} *= 1;
1221 # multiply by number of assemblies
1222 $ref->{qty} *= $totalqty;
1224 if ($ref->{assembly}) {
1225 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1228 if ($ref->{inventory_accno_id}) {
1229 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1233 # save detail record for individual assembly item in invoice table
1235 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1236 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1237 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1238 do_query($form, $dbh, $query, @values);
1244 $main::lxdebug->leave_sub();
1248 $main::lxdebug->enter_sub();
1250 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1254 $form->{taxzone_id} *=1;
1255 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1256 my $taxzone_id = $form->{"taxzone_id"} * 1;
1258 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1259 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1260 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1261 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1262 FROM invoice i, parts p
1263 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1264 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1265 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1266 WHERE (i.parts_id = p.id)
1267 AND (i.parts_id = ?)
1268 AND ((i.base_qty + i.allocated) < 0)
1270 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1275 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1276 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1280 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1282 # total expenses and inventory
1283 # sellprice is the cost of the item
1284 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1287 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1289 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1290 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1291 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1293 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1294 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1300 last if (($totalqty -= $qty) <= 0);
1305 $main::lxdebug->leave_sub();
1310 sub reverse_invoice {
1311 $main::lxdebug->enter_sub();
1313 my ($dbh, $form) = @_;
1315 # reverse inventory items
1317 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1319 JOIN parts p ON (i.parts_id = p.id)
1320 WHERE i.trans_id = ?|;
1321 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1323 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1325 if ($ref->{inventory_accno_id}) {
1326 # de-allocated purchases
1328 qq|SELECT i.id, i.trans_id, i.allocated
1330 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1331 ORDER BY i.trans_id DESC|;
1332 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1334 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1335 my $qty = $ref->{qty};
1336 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1337 $qty = $inhref->{allocated};
1341 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1343 last if (($ref->{qty} -= $qty) <= 0);
1352 my @values = (conv_i($form->{id}));
1353 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1354 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1356 if ($form->{lizenzen}) {
1358 qq|DELETE FROM licenseinvoice
1359 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1360 do_query($form, $dbh, $query, @values);
1363 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1365 $main::lxdebug->leave_sub();
1368 sub delete_invoice {
1369 $main::lxdebug->enter_sub();
1371 my ($self, $myconfig, $form, $spool) = @_;
1373 # connect to database
1374 my $dbh = $form->dbconnect_noauto($myconfig);
1376 &reverse_invoice($dbh, $form);
1378 my @values = (conv_i($form->{id}));
1381 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1383 # delete spool files
1384 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1386 # delete status entries
1387 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1389 my $rc = $dbh->commit;
1393 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1396 $main::lxdebug->leave_sub();
1401 sub retrieve_invoice {
1402 $main::lxdebug->enter_sub();
1404 my ($self, $myconfig, $form) = @_;
1406 # connect to database
1407 my $dbh = $form->get_standard_dbh;
1409 my ($sth, $ref, $query);
1411 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1415 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1416 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1417 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1418 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1419 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1420 d.curr AS currencies
1424 $ref = selectfirst_hashref_query($form, $dbh, $query);
1425 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1428 my $id = conv_i($form->{id});
1431 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1435 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1436 a.orddate, a.quodate, a.globalproject_id,
1437 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1438 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1439 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1440 a.employee_id, a.salesman_id, a.payment_id,
1441 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1442 a.transaction_description,
1443 a.marge_total, a.marge_percent,
1444 e.name AS employee, a.donumber
1446 LEFT JOIN employee e ON (e.id = a.employee_id)
1448 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1449 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1452 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1455 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1456 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1458 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1460 foreach my $vc (qw(customer vendor)) {
1461 next if !$form->{"delivery_${vc}_id"};
1462 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1465 # get printed, emailed
1466 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1467 $sth = prepare_execute_query($form, $dbh, $query, $id);
1469 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1470 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1471 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1472 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1475 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1477 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1478 : $form->{invdate} ? $dbh->quote($form->{invdate})
1482 my $taxzone_id = $form->{taxzone_id} *= 1;
1483 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1485 # retrieve individual items
1488 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1489 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1490 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1493 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1494 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1495 i.price_factor_id, i.price_factor, i.marge_price_factor,
1496 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1497 pr.projectnumber, pg.partsgroup, prg.pricegroup
1500 LEFT JOIN parts p ON (i.parts_id = p.id)
1501 LEFT JOIN project pr ON (i.project_id = pr.id)
1502 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1503 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1505 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1506 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1507 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1509 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1511 $sth = prepare_execute_query($form, $dbh, $query, $id);
1513 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1514 # Retrieve custom variables.
1515 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1517 sub_module => 'invoice',
1518 trans_id => $ref->{invoice_id},
1520 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1521 delete $ref->{invoice_id};
1523 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1524 delete($ref->{"part_inventory_accno_id"});
1526 foreach my $type (qw(inventory income expense)) {
1527 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1528 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1529 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1533 # get tax rates and description
1534 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1536 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1537 LEFT JOIN chart c ON (c.id = t.chart_id)
1539 (SELECT tk.tax_id FROM taxkeys tk
1540 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1541 AND startdate <= date($transdate)
1542 ORDER BY startdate DESC LIMIT 1)
1544 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1545 $ref->{taxaccounts} = "";
1547 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1549 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1553 $ref->{taxaccounts} .= "$ptr->{accno} ";
1555 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1556 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1557 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1558 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1559 $form->{taxaccounts} .= "$ptr->{accno} ";
1564 if ($form->{lizenzen}) {
1565 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1566 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1567 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1570 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1572 chop $ref->{taxaccounts};
1573 push @{ $form->{invoice_details} }, $ref;
1578 Common::webdav_folder($form) if ($main::webdav);
1581 my $rc = $dbh->commit;
1583 $main::lxdebug->leave_sub();
1589 $main::lxdebug->enter_sub();
1591 my ($self, $myconfig, $form) = @_;
1593 # connect to database
1594 my $dbh = $form->get_standard_dbh;
1596 my $dateformat = $myconfig->{dateformat};
1597 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1599 my (@values, $duedate, $ref, $query);
1601 if ($form->{invdate}) {
1602 $duedate = "to_date(?, '$dateformat')";
1603 push @values, $form->{invdate};
1605 $duedate = "current_date";
1608 my $cid = conv_i($form->{customer_id});
1611 if ($form->{payment_id}) {
1612 $payment_id = "(pt.id = ?) OR";
1613 push @values, conv_i($form->{payment_id});
1619 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1620 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1621 c.street, c.zipcode, c.city, c.country,
1622 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1623 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1624 b.discount AS tradediscount, b.description AS business
1626 LEFT JOIN business b ON (b.id = c.business_id)
1627 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1630 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1632 delete $ref->{salesman_id} if !$ref->{salesman_id};
1634 map { $form->{$_} = $ref->{$_} } keys %$ref;
1637 qq|SELECT sum(amount - paid) AS dunning_amount
1639 WHERE (paid < amount)
1640 AND (customer_id = ?)
1641 AND (dunning_config_id IS NOT NULL)|;
1642 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1643 map { $form->{$_} = $ref->{$_} } keys %$ref;
1646 qq|SELECT dnn.dunning_description AS max_dunning_level
1647 FROM dunning_config dnn
1648 WHERE id IN (SELECT dunning_config_id
1650 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1651 ORDER BY dunning_level DESC LIMIT 1|;
1652 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1653 map { $form->{$_} = $ref->{$_} } keys %$ref;
1655 $form->{creditremaining} = $form->{creditlimit};
1656 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1657 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1658 $form->{creditremaining} -= $value;
1662 (SELECT e.buy FROM exchangerate e
1663 WHERE e.curr = o.curr
1664 AND e.transdate = o.transdate)
1666 WHERE o.customer_id = ?
1667 AND o.quotation = '0'
1668 AND o.closed = '0'|;
1669 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1671 while (my ($amount, $exch) = $sth->fetchrow_array) {
1672 $exch = 1 unless $exch;
1673 $form->{creditremaining} -= $amount * $exch;
1677 # get shipto if we did not converted an order or invoice
1678 if (!$form->{shipto}) {
1679 map { delete $form->{$_} }
1680 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1681 shiptostreet shiptozipcode shiptocity shiptocountry
1682 shiptocontact shiptophone shiptofax shiptoemail);
1684 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1685 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1687 map { $form->{$_} = $ref->{$_} } keys %$ref;
1690 # setup last accounts used for this customer
1691 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1693 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1695 JOIN acc_trans ac ON (ac.chart_id = c.id)
1696 JOIN ar a ON (a.id = ac.trans_id)
1697 WHERE a.customer_id = ?
1698 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1699 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1700 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1703 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1704 if ($ref->{category} eq 'I') {
1706 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1708 if ($form->{initial_transdate}) {
1710 qq|SELECT tk.tax_id, t.rate
1712 LEFT JOIN tax t ON tk.tax_id = t.id
1713 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1714 ORDER BY tk.startdate DESC
1716 my ($tax_id, $rate) =
1717 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1718 $form->{initial_transdate});
1719 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1722 if ($ref->{category} eq 'A') {
1723 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1727 $form->{rowcount} = $i if ($i && !$form->{type});
1730 $main::lxdebug->leave_sub();
1734 $main::lxdebug->enter_sub();
1736 my ($self, $myconfig, $form) = @_;
1738 # connect to database
1739 my $dbh = $form->dbconnect($myconfig);
1741 my $i = $form->{rowcount};
1743 my $where = qq|NOT p.obsolete = '1'|;
1746 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1747 my ($table, $field) = split m/\./, $column;
1748 next if !$form->{"${field}_${i}"};
1749 $where .= qq| AND lower(${column}) ILIKE ?|;
1750 push @values, '%' . $form->{"${field}_${i}"} . '%';
1753 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1754 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1755 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1756 push @values, $form->{"partnumber_$i"};
1759 if ($form->{"description_$i"}) {
1760 $where .= qq| ORDER BY p.description|;
1762 $where .= qq| ORDER BY p.partnumber|;
1766 if ($form->{type} eq "invoice") {
1768 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1769 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1773 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1777 my $taxzone_id = $form->{taxzone_id} * 1;
1778 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1782 p.id, p.partnumber, p.description, p.sellprice,
1783 p.listprice, p.inventory_accno_id, p.lastcost,
1785 c1.accno AS inventory_accno,
1786 c1.new_chart_id AS inventory_new_chart,
1787 date($transdate) - c1.valid_from AS inventory_valid,
1789 c2.accno AS income_accno,
1790 c2.new_chart_id AS income_new_chart,
1791 date($transdate) - c2.valid_from AS income_valid,
1793 c3.accno AS expense_accno,
1794 c3.new_chart_id AS expense_new_chart,
1795 date($transdate) - c3.valid_from AS expense_valid,
1797 p.unit, p.assembly, p.bin, p.onhand,
1798 p.notes AS partnotes, p.notes AS longdescription,
1799 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1802 pfac.factor AS price_factor,
1807 LEFT JOIN chart c1 ON
1808 ((SELECT inventory_accno_id
1809 FROM buchungsgruppen
1810 WHERE id = p.buchungsgruppen_id) = c1.id)
1811 LEFT JOIN chart c2 ON
1812 ((SELECT income_accno_id_${taxzone_id}
1813 FROM buchungsgruppen
1814 WHERE id = p.buchungsgruppen_id) = c2.id)
1815 LEFT JOIN chart c3 ON
1816 ((SELECT expense_accno_id_${taxzone_id}
1817 FROM buchungsgruppen
1818 WHERE id = p.buchungsgruppen_id) = c3.id)
1819 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1820 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1822 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1824 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1826 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1827 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1828 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1829 if (!$ref->{inventory_accno_id}) {
1830 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1832 delete($ref->{inventory_accno_id});
1834 foreach my $type (qw(inventory income expense)) {
1835 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1837 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1840 ($ref->{"${type}_accno"},
1841 $ref->{"${type}_new_chart"},
1842 $ref->{"${type}_valid"})
1843 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1847 if ($form->{payment_id} eq "") {
1848 $form->{payment_id} = $form->{part_payment_id};
1851 # get tax rates and description
1852 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1854 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1856 LEFT JOIN chart c ON (c.id = t.chart_id)
1860 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1862 ORDER BY startdate DESC
1865 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1866 my $stw = $dbh->prepare($query);
1867 $stw->execute(@values) || $form->dberror($query);
1869 $ref->{taxaccounts} = "";
1871 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1873 # if ($customertax{$ref->{accno}})
1874 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1878 $ref->{taxaccounts} .= "$ptr->{accno} ";
1880 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1881 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1882 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1883 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1884 $form->{taxaccounts} .= "$ptr->{accno} ";
1890 chop $ref->{taxaccounts};
1891 if ($form->{language_id}) {
1893 qq|SELECT tr.translation, tr.longdescription
1895 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1896 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1897 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1898 if ($translation ne "") {
1899 $ref->{description} = $translation;
1900 $ref->{longdescription} = $longdescription;
1904 qq|SELECT tr.translation, tr.longdescription
1906 WHERE tr.language_id IN
1909 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1912 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1913 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1914 if ($translation ne "") {
1915 $ref->{description} = $translation;
1916 $ref->{longdescription} = $longdescription;
1921 $ref->{onhand} *= 1;
1923 push @{ $form->{item_list} }, $ref;
1925 if ($form->{lizenzen}) {
1926 if ($ref->{inventory_accno} > 0) {
1930 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1931 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1932 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1933 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1941 foreach my $item (@{ $form->{item_list} }) {
1942 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1943 trans_id => $item->{id},
1947 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1952 $main::lxdebug->leave_sub();
1955 ##########################
1956 # get pricegroups from database
1957 # build up selected pricegroup
1958 # if an exchange rate - change price
1961 sub get_pricegroups_for_parts {
1963 $main::lxdebug->enter_sub();
1965 my ($self, $myconfig, $form) = @_;
1967 my $dbh = $form->dbconnect($myconfig);
1969 $form->{"PRICES"} = {};
1973 my $all_units = AM->retrieve_units($myconfig, $form);
1974 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1975 $form->{"PRICES"}{$i} = [];
1977 $id = $form->{"id_$i"};
1979 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1980 $id = $form->{"new_id_$i"};
1983 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1985 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1986 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1987 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1989 my $price_new = $form->{"price_new_$i"};
1990 my $price_old = $form->{"price_old_$i"};
1992 if (!$form->{"unit_old_$i"}) {
1993 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1994 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1995 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1996 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1999 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2000 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2001 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2003 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2004 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2005 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2006 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2007 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2008 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2009 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2014 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2015 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2016 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2017 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2018 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2022 if (!$form->{"basefactor_$i"}) {
2023 $form->{"basefactor_$i"} = 1;
2029 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2030 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2040 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2042 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2043 'selected' AS selected
2046 ORDER BY pricegroup|;
2047 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2048 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2050 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2052 $pkr->{selected} = '';
2054 # if there is an exchange rate change price
2055 if (($form->{exchangerate} * 1) != 0) {
2056 $pkr->{price} /= $form->{exchangerate};
2059 $pkr->{price} *= $form->{"basefactor_$i"};
2060 $pkr->{price} *= $basefactor;
2061 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2063 if ($selectedpricegroup_id eq undef) {
2064 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2066 $pkr->{selected} = ' selected';
2068 # no customer pricesgroup set
2069 if ($pkr->{price} == $pkr->{default_sellprice}) {
2071 $pkr->{price} = $form->{"sellprice_$i"};
2075 # this sub should not set anything and only return. --sschoeling, 20090506
2076 # $form->{"sellprice_$i"} = $pkr->{price};
2079 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2080 $pkr->{price} = $form->{"sellprice_$i"};
2081 $pkr->{selected} = ' selected';
2084 if ($selectedpricegroup_id ne $pricegroup_old) {
2085 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2086 $pkr->{selected} = ' selected';
2088 } elsif ( ( $form->parse_amount($myconfig, $price_new)
2089 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2090 and ($price_new ne 0)) {
2091 if ($pkr->{pricegroup_id} == 0) {
2092 $pkr->{price} = $form->{"sellprice_$i"};
2093 $pkr->{selected} = ' selected';
2095 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2096 $pkr->{selected} = ' selected';
2097 if ( ($pkr->{pricegroup_id} == 0)
2098 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2099 # $pkr->{price} = $form->{"sellprice_$i"};
2101 $pkr->{price} = $form->{"sellprice_$i"};
2105 push @{ $form->{PRICES}{$i} }, $pkr;
2108 $form->{"basefactor_$i"} *= $basefactor;
2117 $main::lxdebug->leave_sub();
2121 $main::lxdebug->enter_sub();
2123 my ($self, $myconfig, $form, $table) = @_;
2125 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2127 # make sure there's no funny stuff in $table
2128 # ToDO: die when this happens and throw an error
2129 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2131 my $dbh = $form->dbconnect($myconfig);
2133 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2134 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2138 $main::lxdebug->leave_sub();
2144 $main::lxdebug->enter_sub();
2146 my ($self, $myconfig, $form, $table, $id) = @_;
2148 $main::lxdebug->leave_sub() and return 0 unless ($id);
2150 # make sure there's no funny stuff in $table
2151 # ToDO: die when this happens and throw an error
2152 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2154 my $dbh = $form->dbconnect($myconfig);
2156 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2157 my ($result) = selectrow_query($form, $dbh, $query, $id);
2161 $main::lxdebug->leave_sub();
2166 sub get_standard_accno_current_assets {
2167 $main::lxdebug->enter_sub();
2169 my ($self, $myconfig, $form) = @_;
2171 my $dbh = $form->dbconnect($myconfig);
2173 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2174 my ($result) = selectrow_query($form, $dbh, $query);
2178 $main::lxdebug->leave_sub();