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) if ($main::webdav);
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);
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, $spool) = @_;
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 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1404 $main::lxdebug->leave_sub();
1409 sub retrieve_invoice {
1410 $main::lxdebug->enter_sub();
1412 my ($self, $myconfig, $form) = @_;
1414 # connect to database
1415 my $dbh = $form->get_standard_dbh;
1417 my ($sth, $ref, $query);
1419 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1423 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1424 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1425 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1426 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1427 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1428 d.curr AS currencies
1432 $ref = selectfirst_hashref_query($form, $dbh, $query);
1433 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1436 my $id = conv_i($form->{id});
1439 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1443 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1444 a.orddate, a.quodate, a.globalproject_id,
1445 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1446 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1447 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1448 a.employee_id, a.salesman_id, a.payment_id,
1449 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1450 a.transaction_description,
1451 a.marge_total, a.marge_percent,
1452 e.name AS employee, a.donumber
1454 LEFT JOIN employee e ON (e.id = a.employee_id)
1456 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1457 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1460 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1463 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1464 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1466 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1468 foreach my $vc (qw(customer vendor)) {
1469 next if !$form->{"delivery_${vc}_id"};
1470 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1473 # get printed, emailed
1474 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1475 $sth = prepare_execute_query($form, $dbh, $query, $id);
1477 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1478 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1479 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1480 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1483 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1485 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1486 : $form->{invdate} ? $dbh->quote($form->{invdate})
1490 my $taxzone_id = $form->{taxzone_id} *= 1;
1491 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1493 # retrieve individual items
1496 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1497 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1498 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1501 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1502 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1503 i.price_factor_id, i.price_factor, i.marge_price_factor,
1504 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1505 pr.projectnumber, pg.partsgroup, prg.pricegroup
1508 LEFT JOIN parts p ON (i.parts_id = p.id)
1509 LEFT JOIN project pr ON (i.project_id = pr.id)
1510 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1511 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1513 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1514 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1515 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1517 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1519 $sth = prepare_execute_query($form, $dbh, $query, $id);
1521 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1522 # Retrieve custom variables.
1523 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1525 sub_module => 'invoice',
1526 trans_id => $ref->{invoice_id},
1528 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1529 delete $ref->{invoice_id};
1531 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1532 delete($ref->{"part_inventory_accno_id"});
1534 foreach my $type (qw(inventory income expense)) {
1535 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1536 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1537 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1541 # get tax rates and description
1542 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1544 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1545 LEFT JOIN chart c ON (c.id = t.chart_id)
1547 (SELECT tk.tax_id FROM taxkeys tk
1548 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1549 AND startdate <= date($transdate)
1550 ORDER BY startdate DESC LIMIT 1)
1552 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1553 $ref->{taxaccounts} = "";
1555 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1557 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1561 $ref->{taxaccounts} .= "$ptr->{accno} ";
1563 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1564 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1565 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1566 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1567 $form->{taxaccounts} .= "$ptr->{accno} ";
1572 if ($form->{lizenzen}) {
1573 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1574 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1575 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1578 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1580 chop $ref->{taxaccounts};
1581 push @{ $form->{invoice_details} }, $ref;
1586 Common::webdav_folder($form) if ($main::webdav);
1589 my $rc = $dbh->commit;
1591 $main::lxdebug->leave_sub();
1597 $main::lxdebug->enter_sub();
1599 my ($self, $myconfig, $form) = @_;
1601 # connect to database
1602 my $dbh = $form->get_standard_dbh;
1604 my $dateformat = $myconfig->{dateformat};
1605 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1607 my (@values, $duedate, $ref, $query);
1609 if ($form->{invdate}) {
1610 $duedate = "to_date(?, '$dateformat')";
1611 push @values, $form->{invdate};
1613 $duedate = "current_date";
1616 my $cid = conv_i($form->{customer_id});
1619 if ($form->{payment_id}) {
1620 $payment_id = "(pt.id = ?) OR";
1621 push @values, conv_i($form->{payment_id});
1627 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1628 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1629 c.street, c.zipcode, c.city, c.country,
1630 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1631 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1632 b.discount AS tradediscount, b.description AS business
1634 LEFT JOIN business b ON (b.id = c.business_id)
1635 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1638 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1640 delete $ref->{salesman_id} if !$ref->{salesman_id};
1642 map { $form->{$_} = $ref->{$_} } keys %$ref;
1645 qq|SELECT sum(amount - paid) AS dunning_amount
1647 WHERE (paid < amount)
1648 AND (customer_id = ?)
1649 AND (dunning_config_id IS NOT NULL)|;
1650 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1651 map { $form->{$_} = $ref->{$_} } keys %$ref;
1654 qq|SELECT dnn.dunning_description AS max_dunning_level
1655 FROM dunning_config dnn
1656 WHERE id IN (SELECT dunning_config_id
1658 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1659 ORDER BY dunning_level DESC LIMIT 1|;
1660 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1661 map { $form->{$_} = $ref->{$_} } keys %$ref;
1663 $form->{creditremaining} = $form->{creditlimit};
1664 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1665 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1666 $form->{creditremaining} -= $value;
1670 (SELECT e.buy FROM exchangerate e
1671 WHERE e.curr = o.curr
1672 AND e.transdate = o.transdate)
1674 WHERE o.customer_id = ?
1675 AND o.quotation = '0'
1676 AND o.closed = '0'|;
1677 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1679 while (my ($amount, $exch) = $sth->fetchrow_array) {
1680 $exch = 1 unless $exch;
1681 $form->{creditremaining} -= $amount * $exch;
1685 # get shipto if we did not converted an order or invoice
1686 if (!$form->{shipto}) {
1687 map { delete $form->{$_} }
1688 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1689 shiptostreet shiptozipcode shiptocity shiptocountry
1690 shiptocontact shiptophone shiptofax shiptoemail);
1692 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1693 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1695 map { $form->{$_} = $ref->{$_} } keys %$ref;
1698 # setup last accounts used for this customer
1699 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1701 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1703 JOIN acc_trans ac ON (ac.chart_id = c.id)
1704 JOIN ar a ON (a.id = ac.trans_id)
1705 WHERE a.customer_id = ?
1706 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1707 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1708 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1711 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1712 if ($ref->{category} eq 'I') {
1714 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1716 if ($form->{initial_transdate}) {
1718 qq|SELECT tk.tax_id, t.rate
1720 LEFT JOIN tax t ON tk.tax_id = t.id
1721 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1722 ORDER BY tk.startdate DESC
1724 my ($tax_id, $rate) =
1725 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1726 $form->{initial_transdate});
1727 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1730 if ($ref->{category} eq 'A') {
1731 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1735 $form->{rowcount} = $i if ($i && !$form->{type});
1738 $main::lxdebug->leave_sub();
1742 $main::lxdebug->enter_sub();
1744 my ($self, $myconfig, $form) = @_;
1746 # connect to database
1747 my $dbh = $form->dbconnect($myconfig);
1749 my $i = $form->{rowcount};
1751 my $where = qq|NOT p.obsolete = '1'|;
1754 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1755 my ($table, $field) = split m/\./, $column;
1756 next if !$form->{"${field}_${i}"};
1757 $where .= qq| AND lower(${column}) ILIKE ?|;
1758 push @values, '%' . $form->{"${field}_${i}"} . '%';
1761 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1762 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1763 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1764 push @values, $form->{"partnumber_$i"};
1767 if ($form->{"description_$i"}) {
1768 $where .= qq| ORDER BY p.description|;
1770 $where .= qq| ORDER BY p.partnumber|;
1774 if ($form->{type} eq "invoice") {
1776 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1777 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1781 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1785 my $taxzone_id = $form->{taxzone_id} * 1;
1786 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1790 p.id, p.partnumber, p.description, p.sellprice,
1791 p.listprice, p.inventory_accno_id, p.lastcost,
1793 c1.accno AS inventory_accno,
1794 c1.new_chart_id AS inventory_new_chart,
1795 date($transdate) - c1.valid_from AS inventory_valid,
1797 c2.accno AS income_accno,
1798 c2.new_chart_id AS income_new_chart,
1799 date($transdate) - c2.valid_from AS income_valid,
1801 c3.accno AS expense_accno,
1802 c3.new_chart_id AS expense_new_chart,
1803 date($transdate) - c3.valid_from AS expense_valid,
1805 p.unit, p.assembly, p.bin, p.onhand,
1806 p.notes AS partnotes, p.notes AS longdescription,
1807 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1810 pfac.factor AS price_factor,
1815 LEFT JOIN chart c1 ON
1816 ((SELECT inventory_accno_id
1817 FROM buchungsgruppen
1818 WHERE id = p.buchungsgruppen_id) = c1.id)
1819 LEFT JOIN chart c2 ON
1820 ((SELECT income_accno_id_${taxzone_id}
1821 FROM buchungsgruppen
1822 WHERE id = p.buchungsgruppen_id) = c2.id)
1823 LEFT JOIN chart c3 ON
1824 ((SELECT expense_accno_id_${taxzone_id}
1825 FROM buchungsgruppen
1826 WHERE id = p.buchungsgruppen_id) = c3.id)
1827 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1828 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1830 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1832 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1834 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1835 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1836 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1837 if (!$ref->{inventory_accno_id}) {
1838 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1840 delete($ref->{inventory_accno_id});
1842 foreach my $type (qw(inventory income expense)) {
1843 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1845 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1848 ($ref->{"${type}_accno"},
1849 $ref->{"${type}_new_chart"},
1850 $ref->{"${type}_valid"})
1851 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1855 if ($form->{payment_id} eq "") {
1856 $form->{payment_id} = $form->{part_payment_id};
1859 # get tax rates and description
1860 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1862 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1864 LEFT JOIN chart c ON (c.id = t.chart_id)
1868 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1870 ORDER BY startdate DESC
1873 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1874 my $stw = $dbh->prepare($query);
1875 $stw->execute(@values) || $form->dberror($query);
1877 $ref->{taxaccounts} = "";
1879 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1881 # if ($customertax{$ref->{accno}})
1882 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1886 $ref->{taxaccounts} .= "$ptr->{accno} ";
1888 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1889 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1890 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1891 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1892 $form->{taxaccounts} .= "$ptr->{accno} ";
1898 chop $ref->{taxaccounts};
1899 if ($form->{language_id}) {
1901 qq|SELECT tr.translation, tr.longdescription
1903 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1904 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1905 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1906 if ($translation ne "") {
1907 $ref->{description} = $translation;
1908 $ref->{longdescription} = $longdescription;
1912 qq|SELECT tr.translation, tr.longdescription
1914 WHERE tr.language_id IN
1917 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1920 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1921 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1922 if ($translation ne "") {
1923 $ref->{description} = $translation;
1924 $ref->{longdescription} = $longdescription;
1929 $ref->{onhand} *= 1;
1931 push @{ $form->{item_list} }, $ref;
1933 if ($form->{lizenzen}) {
1934 if ($ref->{inventory_accno} > 0) {
1938 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1939 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1940 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1941 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1949 foreach my $item (@{ $form->{item_list} }) {
1950 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1951 trans_id => $item->{id},
1955 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1960 $main::lxdebug->leave_sub();
1963 ##########################
1964 # get pricegroups from database
1965 # build up selected pricegroup
1966 # if an exchange rate - change price
1969 sub get_pricegroups_for_parts {
1971 $main::lxdebug->enter_sub();
1973 my ($self, $myconfig, $form) = @_;
1975 my $dbh = $form->dbconnect($myconfig);
1977 $form->{"PRICES"} = {};
1981 my $all_units = AM->retrieve_units($myconfig, $form);
1982 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1983 $form->{"PRICES"}{$i} = [];
1985 $id = $form->{"id_$i"};
1987 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1988 $id = $form->{"new_id_$i"};
1991 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1993 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1994 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1995 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1997 my $price_new = $form->{"price_new_$i"};
1998 my $price_old = $form->{"price_old_$i"};
2000 if (!$form->{"unit_old_$i"}) {
2001 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2002 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2003 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2004 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2007 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2008 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2009 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2011 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2012 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2013 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2014 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2015 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2016 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2017 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2022 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2023 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2024 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2025 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2026 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2030 if (!$form->{"basefactor_$i"}) {
2031 $form->{"basefactor_$i"} = 1;
2037 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2038 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2048 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2050 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2051 'selected' AS selected
2054 ORDER BY pricegroup|;
2055 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2056 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2058 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2060 $pkr->{selected} = '';
2062 # if there is an exchange rate change price
2063 if (($form->{exchangerate} * 1) != 0) {
2064 $pkr->{price} /= $form->{exchangerate};
2067 $pkr->{price} *= $form->{"basefactor_$i"};
2068 $pkr->{price} *= $basefactor;
2069 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2071 if ($selectedpricegroup_id eq undef) {
2072 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2074 $pkr->{selected} = ' selected';
2076 # no customer pricesgroup set
2077 if ($pkr->{price} == $pkr->{default_sellprice}) {
2079 $pkr->{price} = $form->{"sellprice_$i"};
2083 # this sub should not set anything and only return. --sschoeling, 20090506
2084 # $form->{"sellprice_$i"} = $pkr->{price};
2087 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2088 $pkr->{price} = $form->{"sellprice_$i"};
2089 $pkr->{selected} = ' selected';
2092 if ($selectedpricegroup_id ne $pricegroup_old) {
2093 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2094 $pkr->{selected} = ' selected';
2096 } elsif ( ( $form->parse_amount($myconfig, $price_new)
2097 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2098 and ($price_new ne 0)) {
2099 if ($pkr->{pricegroup_id} == 0) {
2100 $pkr->{price} = $form->{"sellprice_$i"};
2101 $pkr->{selected} = ' selected';
2103 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2104 $pkr->{selected} = ' selected';
2105 if ( ($pkr->{pricegroup_id} == 0)
2106 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2107 # $pkr->{price} = $form->{"sellprice_$i"};
2109 $pkr->{price} = $form->{"sellprice_$i"};
2113 push @{ $form->{PRICES}{$i} }, $pkr;
2116 $form->{"basefactor_$i"} *= $basefactor;
2125 $main::lxdebug->leave_sub();
2129 $main::lxdebug->enter_sub();
2131 my ($self, $myconfig, $form, $table) = @_;
2133 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2135 # make sure there's no funny stuff in $table
2136 # ToDO: die when this happens and throw an error
2137 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2139 my $dbh = $form->dbconnect($myconfig);
2141 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2142 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2146 $main::lxdebug->leave_sub();
2152 $main::lxdebug->enter_sub();
2154 my ($self, $myconfig, $form, $table, $id) = @_;
2156 $main::lxdebug->leave_sub() and return 0 unless ($id);
2158 # make sure there's no funny stuff in $table
2159 # ToDO: die when this happens and throw an error
2160 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2162 my $dbh = $form->dbconnect($myconfig);
2164 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2165 my ($result) = selectrow_query($form, $dbh, $query, $id);
2169 $main::lxdebug->leave_sub();
2174 sub get_standard_accno_current_assets {
2175 $main::lxdebug->enter_sub();
2177 my ($self, $myconfig, $form) = @_;
2179 my $dbh = $form->dbconnect($myconfig);
2181 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2182 my ($result) = selectrow_query($form, $dbh, $query);
2186 $main::lxdebug->leave_sub();