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;
53 $main::lxdebug->enter_sub();
55 my ($self, $myconfig, $form, $locale) = @_;
57 $form->{duedate} ||= $form->{invdate};
60 my $dbh = $form->dbconnect($myconfig);
63 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
64 ($form->{terms}) = selectrow_query($form, $dbh, $query);
66 my (@project_ids, %projectnumbers);
67 $form->{TEMPLATE_ARRAYS} = {};
69 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
71 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
74 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
75 $price_factors{$pfac->{id}} = $pfac;
77 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
80 # sort items by partsgroup
81 for my $i (1 .. $form->{rowcount}) {
83 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
84 # $partsgroup = $form->{"partsgroup_$i"};
86 # push @partsgroup, [$i, $partsgroup];
87 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
91 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
92 join(", ", map({ "?" } @project_ids)) . ")";
93 $sth = $dbh->prepare($query);
94 $sth->execute(@project_ids) ||
95 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
96 while (my $ref = $sth->fetchrow_hashref()) {
97 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $form->{"globalprojectnumber"} =
103 $projectnumbers{$form->{"globalproject_id"}};
110 my %oid = ('Pg' => 'oid',
111 'Oracle' => 'rowid');
113 # sort items by partsgroup
114 for $i (1 .. $form->{rowcount}) {
116 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
117 $partsgroup = $form->{"partsgroup_$i"};
119 push @partsgroup, [$i, $partsgroup];
132 my $nodiscount_subtotal = 0;
133 my $discount_subtotal = 0;
135 my $subtotal_header = 0;
138 $form->{discount} = [];
140 IC->prepare_parts_for_printing();
142 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
145 qw(runningnumber number description longdescription qty ship unit bin
146 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
147 partnotes serialnumber reqdate sellprice listprice netprice
148 discount p_discount discount_sub nodiscount_sub
149 linetotal nodiscount_linetotal tax_rate projectnumber
150 price_factor price_factor_name partsgroup);
152 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
154 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
156 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
158 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
160 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
163 if ($item->[1] ne $sameitem) {
164 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
165 $sameitem = $item->[1];
167 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
170 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
172 if ($form->{"id_$i"} != 0) {
174 # add number, description and qty to $form->{number},
175 if ($form->{"subtotal_$i"} && !$subtotal_header) {
176 $subtotal_header = $i;
177 $position = int($position);
180 } elsif ($subtotal_header) {
182 $position = int($position);
183 $position = $position.".".$subposition;
185 $position = int($position);
189 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
191 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
192 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
193 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
194 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
195 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
196 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
199 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"deliverydate_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
207 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
208 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
211 if ($form->{lizenzen}) {
212 if ($form->{"licensenumber_$i"}) {
213 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
214 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
215 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
216 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
219 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
220 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
225 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
227 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
228 my ($dec) = ($sellprice =~ /\.(\d+)/);
229 my $decimalplaces = max 2, length($dec);
231 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
232 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
233 my $linetotal = $form->round_amount($linetotal_exact, 2);
234 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
236 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
237 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
239 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
241 $linetotal = ($linetotal != 0) ? $linetotal : '';
243 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
246 $form->{total} += $linetotal;
247 $form->{nodiscount_total} += $nodiscount_linetotal;
248 $form->{discount_total} += $discount;
250 if ($subtotal_header) {
251 $discount_subtotal += $linetotal;
252 $nodiscount_subtotal += $nodiscount_linetotal;
255 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
256 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
257 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
259 $discount_subtotal = 0;
260 $nodiscount_subtotal = 0;
261 $subtotal_header = 0;
264 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
265 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
268 if (!$form->{"discount_$i"}) {
269 $nodiscount += $linetotal;
272 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
273 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
275 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
277 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
281 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
283 if ($form->{taxincluded}) {
286 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
287 $taxbase = $linetotal - $taxamount;
289 $taxamount = $linetotal * $taxrate;
290 $taxbase = $linetotal;
293 if ($form->round_amount($taxrate, 7) == 0) {
294 if ($form->{taxincluded}) {
295 foreach my $accno (@taxaccounts) {
296 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
298 $taxaccounts{$accno} += $taxamount;
299 $taxdiff += $taxamount;
301 $taxbase{$accno} += $taxbase;
303 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
305 foreach my $accno (@taxaccounts) {
306 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
307 $taxbase{$accno} += $taxbase;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
313 $taxbase{$accno} += $taxbase;
316 my $tax_rate = $taxrate * 100;
317 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
318 if ($form->{"assembly_$i"}) {
321 # get parts and push them onto the stack
323 if ($form->{groupitems}) {
325 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
327 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
331 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
333 JOIN parts p ON (a.parts_id = p.id)
334 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
335 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
336 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
338 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
339 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
340 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
341 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
342 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
345 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
347 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
348 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
350 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
351 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
357 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
361 foreach my $item (sort keys %taxaccounts) {
362 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
364 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
365 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
366 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
368 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
371 for my $i (1 .. $form->{paidaccounts}) {
372 if ($form->{"paid_$i"}) {
373 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
375 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
376 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
377 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
378 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
381 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
384 if($form->{taxincluded}) {
385 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
388 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
391 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
392 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
393 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
394 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
396 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
397 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
399 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
400 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
402 $form->set_payment_options($myconfig, $form->{invdate});
404 $form->{username} = $myconfig->{name};
408 $main::lxdebug->leave_sub();
411 sub project_description {
412 $main::lxdebug->enter_sub();
414 my ($self, $dbh, $id) = @_;
415 my $form = \%main::form;
417 my $query = qq|SELECT description FROM project WHERE id = ?|;
418 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
420 $main::lxdebug->leave_sub();
425 sub customer_details {
426 $main::lxdebug->enter_sub();
428 my ($self, $myconfig, $form, @wanted_vars) = @_;
430 # connect to database
431 my $dbh = $form->dbconnect($myconfig);
433 my $language_id = $form->{language_id};
435 # get contact id, set it if nessessary
438 my @values = (conv_i($form->{customer_id}));
441 if ($form->{cp_id}) {
442 $where = qq| AND (cp.cp_id = ?) |;
443 push(@values, conv_i($form->{cp_id}));
446 # get rest for the customer
448 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
449 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
451 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
452 WHERE (ct.id = ?) $where
455 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
457 # remove id and taxincluded before copy back
458 delete @$ref{qw(id taxincluded)};
460 @wanted_vars = grep({ $_ } @wanted_vars);
461 if (scalar(@wanted_vars) > 0) {
463 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
464 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
467 map { $form->{$_} = $ref->{$_} } keys %$ref;
469 if ($form->{delivery_customer_id}) {
471 qq|SELECT *, notes as customernotes
475 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
477 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
480 if ($form->{delivery_vendor_id}) {
482 qq|SELECT *, notes as customernotes
486 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
488 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
491 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
493 'trans_id' => $form->{customer_id});
494 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
496 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
497 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
498 'language_id' => $language_id,
499 'allow_fallback' => 1);
504 $main::lxdebug->leave_sub();
508 $main::lxdebug->enter_sub();
510 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
512 # connect to database, turn off autocommit
513 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
515 my ($query, $sth, $null, $project_id, @values);
516 my $exchangerate = 0;
518 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
521 if (!$form->{employee_id}) {
522 $form->get_employee($dbh);
525 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
527 ($null, $form->{department_id}) = split(/--/, $form->{department});
529 my $all_units = AM->retrieve_units($myconfig, $form);
531 if (!$payments_only) {
533 &reverse_invoice($dbh, $form);
536 $query = qq|SELECT nextval('glid')|;
537 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
539 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
540 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
542 if (!$form->{invnumber}) {
544 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
545 "cnnumber" : "invnumber", $dbh);
550 my ($netamount, $invoicediff) = (0, 0);
551 my ($amount, $linetotal, $lastincomeaccno);
553 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
554 my $defaultcurrency = (split m/:/, $currencies)[0];
556 if ($form->{currency} eq $defaultcurrency) {
557 $form->{exchangerate} = 1;
559 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
562 $form->{exchangerate} =
565 : $form->parse_amount($myconfig, $form->{exchangerate});
567 $form->{expense_inventory} = "";
571 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
572 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
575 $form->{amount} = {};
576 $form->{amount_cogs} = {};
578 foreach my $i (1 .. $form->{rowcount}) {
579 if ($form->{type} eq "credit_note") {
580 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
581 $form->{shipped} = 1;
583 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
588 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
589 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
590 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
592 if ($form->{storno}) {
593 $form->{"qty_$i"} *= -1;
596 if ($form->{"id_$i"}) {
599 if (defined($baseunits{$form->{"id_$i"}})) {
600 $item_unit = $baseunits{$form->{"id_$i"}};
603 $query = qq|SELECT unit FROM parts WHERE id = ?|;
604 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
605 $baseunits{$form->{"id_$i"}} = $item_unit;
608 if (defined($all_units->{$item_unit}->{factor})
609 && ($all_units->{$item_unit}->{factor} ne '')
610 && ($all_units->{$item_unit}->{factor} != 0)) {
611 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
615 $baseqty = $form->{"qty_$i"} * $basefactor;
617 my ($allocated, $taxrate) = (0, 0);
621 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
623 # keep entered selling price
625 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
627 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
629 my $decimalplaces = ($dec > 2) ? $dec : 2;
631 # undo discount formatting
632 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
635 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
637 # round linetotal to 2 decimal places
638 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
639 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
641 if ($form->{taxincluded}) {
642 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
643 $form->{"sellprice_$i"} =
644 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
646 $taxamount = $linetotal * $taxrate;
649 $netamount += $linetotal;
651 if ($taxamount != 0) {
653 $form->{amount}{ $form->{id} }{$_} +=
654 $taxamount * $form->{"${_}_rate"} / $taxrate
655 } split(/ /, $form->{"taxaccounts_$i"});
658 # add amount to income, $form->{amount}{trans_id}{accno}
659 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
661 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
662 $linetotal = $form->round_amount($linetotal, 2);
664 # this is the difference from the inventory
665 $invoicediff += ($amount - $linetotal);
667 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
670 $lastincomeaccno = $form->{"income_accno_$i"};
672 # adjust and round sellprice
673 $form->{"sellprice_$i"} =
674 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
677 next if $payments_only;
679 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
681 if ($form->{"assembly_$i"}) {
682 # record assembly item as allocated
683 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
686 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
690 # get pricegroup_id and save it
691 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
694 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
696 # save detail record in invoice table
698 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
699 sellprice, fxsellprice, discount, allocated, assemblyitem,
700 unit, deliverydate, project_id, serialnumber, pricegroup_id,
701 ordnumber, transdate, cusordnumber, base_qty, subtotal,
702 marge_percent, marge_total, lastcost,
703 price_factor_id, price_factor, marge_price_factor)
704 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
705 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
707 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
708 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
709 $form->{"sellprice_$i"}, $fxsellprice,
710 $form->{"discount_$i"}, $allocated, 'f',
711 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
712 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
713 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
714 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
715 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
716 $form->{"lastcost_$i"},
717 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
718 conv_i($form->{"marge_price_factor_$i"}));
719 do_query($form, $dbh, $query, @values);
721 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
723 qq|INSERT INTO licenseinvoice (trans_id, license_id)
724 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
725 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
726 do_query($form, $dbh, $query, @values);
729 CVar->save_custom_variables(module => 'IC',
730 sub_module => 'invoice',
731 trans_id => $invoice_id,
732 configs => $ic_cvar_configs,
734 name_prefix => 'ic_',
735 name_postfix => "_$i",
740 # total payments, don't move we need it here
741 for my $i (1 .. $form->{paidaccounts}) {
742 if ($form->{type} eq "credit_note") {
743 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
745 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
747 $form->{paid} += $form->{"paid_$i"};
748 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
751 my ($tax, $diff) = (0, 0);
753 $netamount = $form->round_amount($netamount, 2);
755 # figure out rounding errors for total amount vs netamount + taxes
756 if ($form->{taxincluded}) {
758 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
759 $diff += $amount - $netamount * $form->{exchangerate};
760 $netamount = $amount;
762 foreach my $item (split(/ /, $form->{taxaccounts})) {
763 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
764 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
765 $tax += $form->{amount}{ $form->{id} }{$item};
766 $netamount -= $form->{amount}{ $form->{id} }{$item};
769 $invoicediff += $diff;
770 ######## this only applies to tax included
771 if ($lastincomeaccno) {
772 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
776 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
777 $diff = $amount - $netamount * $form->{exchangerate};
778 $netamount = $amount;
779 foreach my $item (split(/ /, $form->{taxaccounts})) {
780 $form->{amount}{ $form->{id} }{$item} =
781 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
784 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
787 $amount - $form->{amount}{ $form->{id} }{$item} *
788 $form->{exchangerate};
789 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
790 $tax += $form->{amount}{ $form->{id} }{$item};
794 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
796 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
799 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
801 # update exchangerate
802 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
803 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
804 $form->{exchangerate}, 0);
807 $project_id = conv_i($form->{"globalproject_id"});
809 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
810 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
811 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
813 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
815 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
817 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
818 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
819 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
820 do_query($form, $dbh, $query, @values);
821 $form->{amount_cogs}{$trans_id}{$accno} = 0;
825 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
826 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
828 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
830 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
831 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
832 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
833 do_query($form, $dbh, $query, @values);
838 foreach my $trans_id (keys %{ $form->{amount} }) {
839 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
840 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
842 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
844 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
846 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
847 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
848 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
849 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
850 do_query($form, $dbh, $query, @values);
851 $form->{amount}{$trans_id}{$accno} = 0;
855 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
856 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
858 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
860 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
861 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
862 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
863 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
864 do_query($form, $dbh, $query, @values);
869 # deduct payment differences from diff
870 for my $i (1 .. $form->{paidaccounts}) {
871 if ($form->{"paid_$i"} != 0) {
873 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
874 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
878 # record payments and offsetting AR
879 if (!$form->{storno}) {
880 for my $i (1 .. $form->{paidaccounts}) {
882 next if ($form->{"paid_$i"} == 0);
884 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
885 $form->{"datepaid_$i"} = $form->{invdate}
886 unless ($form->{"datepaid_$i"});
887 $form->{datepaid} = $form->{"datepaid_$i"};
891 if ($form->{currency} eq $defaultcurrency) {
892 $form->{"exchangerate_$i"} = 1;
894 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
895 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
899 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
901 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
903 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
904 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
905 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
906 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
907 do_query($form, $dbh, $query, @values);
911 $form->{"paid_$i"} *= -1;
914 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
915 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
916 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
917 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
918 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
919 do_query($form, $dbh, $query, @values);
921 # exchangerate difference
922 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
923 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
927 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
928 $form->{"exchangerate_$i"};
930 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
933 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
939 # update exchange rate
940 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
941 $form->update_exchangerate($dbh, $form->{currency},
942 $form->{"datepaid_$i"},
943 $form->{"exchangerate_$i"}, 0);
947 } else { # if (!$form->{storno})
948 $form->{marge_total} *= -1;
951 if ($payments_only) {
952 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
953 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
955 if (!$provided_dbh) {
960 $main::lxdebug->leave_sub();
964 # record exchange rate differences and gains/losses
965 foreach my $accno (keys %{ $form->{fx} }) {
966 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
968 ($form->{fx}{$accno}{$transdate} =
969 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
974 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
975 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
976 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
977 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
978 do_query($form, $dbh, $query, @values);
983 $amount = $netamount + $tax;
986 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
988 $query = qq|UPDATE ar set
989 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
990 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
991 amount = ?, netamount = ?, paid = ?, datepaid = ?,
992 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
993 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
994 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
995 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
996 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
997 cp_id = ?, marge_total = ?, marge_percent = ?,
998 globalproject_id = ?, delivery_customer_id = ?,
999 transaction_description = ?, delivery_vendor_id = ?,
1002 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1003 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1004 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
1005 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1006 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1007 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1008 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1009 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1010 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1011 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1012 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1013 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
1014 conv_i($form->{"id"}));
1015 do_query($form, $dbh, $query, @values);
1017 if($form->{"formname"} eq "credit_note") {
1018 for my $i (1 .. $form->{rowcount}) {
1019 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
1020 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
1021 do_query($form, $dbh, $query, @values);
1025 if ($form->{storno}) {
1028 paid = paid + amount,
1030 intnotes = ? || intnotes
1032 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1033 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1037 $form->{name} = $form->{customer};
1038 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1040 if (!$form->{shipto_id}) {
1041 $form->add_shipto($dbh, $form->{id}, "AR");
1044 # save printed, emailed and queued
1045 $form->save_status($dbh);
1047 Common::webdav_folder($form) if ($main::webdav);
1049 # Link this record to the records it was created from.
1050 RecordLinks->create_links('dbh' => $dbh,
1052 'from_table' => 'oe',
1053 'from_ids' => $form->{convert_from_oe_ids},
1055 'to_id' => $form->{id},
1057 delete $form->{convert_from_oe_ids};
1059 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1061 if (scalar @convert_from_do_ids) {
1062 DO->close_orders('dbh' => $dbh,
1063 'ids' => \@convert_from_do_ids);
1065 RecordLinks->create_links('dbh' => $dbh,
1067 'from_table' => 'delivery_orders',
1068 'from_ids' => \@convert_from_do_ids,
1070 'to_id' => $form->{id},
1073 delete $form->{convert_from_do_ids};
1075 ARAP->close_orders_if_billed('dbh' => $dbh,
1076 'arap_id' => $form->{id},
1080 if (!$provided_dbh) {
1085 $main::lxdebug->leave_sub();
1090 sub _delete_payments {
1091 $main::lxdebug->enter_sub();
1093 my ($self, $form, $dbh) = @_;
1095 my @delete_acc_trans_ids;
1097 # Delete old payment entries from acc_trans.
1099 qq|SELECT acc_trans_id
1101 WHERE (trans_id = ?) AND fx_transaction
1105 SELECT at.acc_trans_id
1107 LEFT JOIN chart c ON (at.chart_id = c.id)
1108 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1109 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1112 qq|SELECT at.acc_trans_id
1114 LEFT JOIN chart c ON (at.chart_id = c.id)
1115 WHERE (trans_id = ?)
1116 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1117 ORDER BY at.acc_trans_id
1119 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1121 if (@delete_acc_trans_ids) {
1122 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1123 do_query($form, $dbh, $query);
1126 $main::lxdebug->leave_sub();
1130 $main::lxdebug->enter_sub();
1132 my ($self, $myconfig, $form, $locale) = @_;
1134 # connect to database, turn off autocommit
1135 my $dbh = $form->dbconnect_noauto($myconfig);
1137 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1139 $old_form = save_form();
1141 # Delete all entries in acc_trans from prior payments.
1142 $self->_delete_payments($form, $dbh);
1144 # Save the new payments the user made before cleaning up $form.
1145 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1147 # Clean up $form so that old content won't tamper the results.
1148 %keep_vars = map { $_, 1 } qw(login password id);
1149 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1151 # Retrieve the invoice from the database.
1152 $self->retrieve_invoice($myconfig, $form);
1154 # Set up the content of $form in the way that IS::post_invoice() expects.
1155 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1157 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1158 $item = $form->{invoice_details}->[$row - 1];
1160 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1162 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1165 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1167 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1169 # Restore the payment options from the user input.
1170 map { $form->{$_} = $payments{$_} } keys %payments;
1172 # Get the AR accno (which is normally done by Form::create_links()).
1176 LEFT JOIN chart c ON (at.chart_id = c.id)
1177 WHERE (trans_id = ?)
1178 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1179 ORDER BY at.acc_trans_id
1182 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1184 # Post the new payments.
1185 $self->post_invoice($myconfig, $form, $dbh, 1);
1187 restore_form($old_form);
1189 my $rc = $dbh->commit();
1192 $main::lxdebug->leave_sub();
1197 sub process_assembly {
1198 $main::lxdebug->enter_sub();
1200 my ($dbh, $form, $id, $totalqty) = @_;
1203 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1204 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1206 JOIN parts p ON (a.parts_id = p.id)
1208 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1210 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1214 $ref->{inventory_accno_id} *= 1;
1215 $ref->{expense_accno_id} *= 1;
1217 # multiply by number of assemblies
1218 $ref->{qty} *= $totalqty;
1220 if ($ref->{assembly}) {
1221 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1224 if ($ref->{inventory_accno_id}) {
1225 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1229 # save detail record for individual assembly item in invoice table
1231 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1232 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1233 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1234 do_query($form, $dbh, $query, @values);
1240 $main::lxdebug->leave_sub();
1244 $main::lxdebug->enter_sub();
1246 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1250 $form->{taxzone_id} *=1;
1251 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1252 my $taxzone_id = $form->{"taxzone_id"} * 1;
1254 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1255 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1256 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1257 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1258 FROM invoice i, parts p
1259 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1260 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1261 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1262 WHERE (i.parts_id = p.id)
1263 AND (i.parts_id = ?)
1264 AND ((i.base_qty + i.allocated) < 0)
1266 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1271 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1272 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1276 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1278 # total expenses and inventory
1279 # sellprice is the cost of the item
1280 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1283 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1285 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1286 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1287 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1289 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1290 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1296 last if (($totalqty -= $qty) <= 0);
1301 $main::lxdebug->leave_sub();
1306 sub reverse_invoice {
1307 $main::lxdebug->enter_sub();
1309 my ($dbh, $form) = @_;
1311 # reverse inventory items
1313 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1315 JOIN parts p ON (i.parts_id = p.id)
1316 WHERE i.trans_id = ?|;
1317 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1319 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1321 if ($ref->{inventory_accno_id}) {
1322 # de-allocated purchases
1324 qq|SELECT i.id, i.trans_id, i.allocated
1326 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1327 ORDER BY i.trans_id DESC|;
1328 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1330 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1331 my $qty = $ref->{qty};
1332 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1333 $qty = $inhref->{allocated};
1337 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1339 last if (($ref->{qty} -= $qty) <= 0);
1348 my @values = (conv_i($form->{id}));
1349 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1350 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1352 if ($form->{lizenzen}) {
1354 qq|DELETE FROM licenseinvoice
1355 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1356 do_query($form, $dbh, $query, @values);
1359 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1361 $main::lxdebug->leave_sub();
1364 sub delete_invoice {
1365 $main::lxdebug->enter_sub();
1367 my ($self, $myconfig, $form, $spool) = @_;
1369 # connect to database
1370 my $dbh = $form->dbconnect_noauto($myconfig);
1372 &reverse_invoice($dbh, $form);
1374 my @values = (conv_i($form->{id}));
1377 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1379 # delete spool files
1380 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1382 # delete status entries
1383 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1385 my $rc = $dbh->commit;
1389 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1392 $main::lxdebug->leave_sub();
1397 sub retrieve_invoice {
1398 $main::lxdebug->enter_sub();
1400 my ($self, $myconfig, $form) = @_;
1402 # connect to database
1403 my $dbh = $form->dbconnect_noauto($myconfig);
1405 my ($sth, $ref, $query);
1407 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1411 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1412 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1413 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1414 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1415 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1416 d.curr AS currencies
1420 $ref = selectfirst_hashref_query($form, $dbh, $query);
1421 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1424 my $id = conv_i($form->{id});
1427 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1431 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1432 a.orddate, a.quodate, a.globalproject_id,
1433 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1434 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1435 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1436 a.employee_id, a.salesman_id, a.payment_id,
1437 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1438 a.transaction_description,
1439 a.marge_total, a.marge_percent,
1440 e.name AS employee, a.donumber
1442 LEFT JOIN employee e ON (e.id = a.employee_id)
1444 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1445 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1448 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1451 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1452 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1454 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1456 foreach my $vc (qw(customer vendor)) {
1457 next if !$form->{"delivery_${vc}_id"};
1458 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1461 # get printed, emailed
1462 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1463 $sth = prepare_execute_query($form, $dbh, $query, $id);
1465 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1466 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1467 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1468 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1471 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1473 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1474 : $form->{invdate} ? $dbh->quote($form->{invdate})
1478 my $taxzone_id = $form->{taxzone_id} *= 1;
1479 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1481 # retrieve individual items
1484 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1485 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1486 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1489 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1490 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1491 i.price_factor_id, i.price_factor, i.marge_price_factor,
1492 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1493 pr.projectnumber, pg.partsgroup, prg.pricegroup
1496 LEFT JOIN parts p ON (i.parts_id = p.id)
1497 LEFT JOIN project pr ON (i.project_id = pr.id)
1498 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1499 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1501 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1502 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1503 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1505 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1507 $sth = prepare_execute_query($form, $dbh, $query, $id);
1509 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1510 # Retrieve custom variables.
1511 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1513 sub_module => 'invoice',
1514 trans_id => $ref->{invoice_id},
1516 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1517 delete $ref->{invoice_id};
1519 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1520 delete($ref->{"part_inventory_accno_id"});
1522 foreach my $type (qw(inventory income expense)) {
1523 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1524 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1525 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1529 # get tax rates and description
1530 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1532 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1533 LEFT JOIN chart c ON (c.id = t.chart_id)
1535 (SELECT tk.tax_id FROM taxkeys tk
1536 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1537 AND startdate <= date($transdate)
1538 ORDER BY startdate DESC LIMIT 1)
1540 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1541 $ref->{taxaccounts} = "";
1543 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1545 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1549 $ref->{taxaccounts} .= "$ptr->{accno} ";
1551 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1552 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1553 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1554 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1555 $form->{taxaccounts} .= "$ptr->{accno} ";
1560 if ($form->{lizenzen}) {
1561 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1562 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1563 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1566 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1568 chop $ref->{taxaccounts};
1569 push @{ $form->{invoice_details} }, $ref;
1574 Common::webdav_folder($form) if ($main::webdav);
1577 my $rc = $dbh->commit;
1580 $main::lxdebug->leave_sub();
1586 $main::lxdebug->enter_sub();
1588 my ($self, $myconfig, $form) = @_;
1590 # connect to database
1591 my $dbh = $form->dbconnect($myconfig);
1593 my $dateformat = $myconfig->{dateformat};
1594 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1596 my (@values, $duedate, $ref, $query);
1598 if ($form->{invdate}) {
1599 $duedate = "to_date(?, '$dateformat')";
1600 push @values, $form->{invdate};
1602 $duedate = "current_date";
1605 my $cid = conv_i($form->{customer_id});
1608 if ($form->{payment_id}) {
1609 $payment_id = "(pt.id = ?) OR";
1610 push @values, conv_i($form->{payment_id});
1616 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1617 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1618 c.street, c.zipcode, c.city, c.country,
1619 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1620 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1621 b.discount AS tradediscount, b.description AS business
1623 LEFT JOIN business b ON (b.id = c.business_id)
1624 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1627 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1629 delete $ref->{salesman_id} if !$ref->{salesman_id};
1631 map { $form->{$_} = $ref->{$_} } keys %$ref;
1634 qq|SELECT sum(amount - paid) AS dunning_amount
1636 WHERE (paid < amount)
1637 AND (customer_id = ?)
1638 AND (dunning_config_id IS NOT NULL)|;
1639 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1640 map { $form->{$_} = $ref->{$_} } keys %$ref;
1643 qq|SELECT dnn.dunning_description AS max_dunning_level
1644 FROM dunning_config dnn
1645 WHERE id IN (SELECT dunning_config_id
1647 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1648 ORDER BY dunning_level DESC LIMIT 1|;
1649 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1650 map { $form->{$_} = $ref->{$_} } keys %$ref;
1652 $form->{creditremaining} = $form->{creditlimit};
1653 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1654 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1655 $form->{creditremaining} -= $value;
1659 (SELECT e.buy FROM exchangerate e
1660 WHERE e.curr = o.curr
1661 AND e.transdate = o.transdate)
1663 WHERE o.customer_id = ?
1664 AND o.quotation = '0'
1665 AND o.closed = '0'|;
1666 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1668 while (my ($amount, $exch) = $sth->fetchrow_array) {
1669 $exch = 1 unless $exch;
1670 $form->{creditremaining} -= $amount * $exch;
1674 # get shipto if we did not converted an order or invoice
1675 if (!$form->{shipto}) {
1676 map { delete $form->{$_} }
1677 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1678 shiptostreet shiptozipcode shiptocity shiptocountry
1679 shiptocontact shiptophone shiptofax shiptoemail);
1681 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1682 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1684 map { $form->{$_} = $ref->{$_} } keys %$ref;
1687 # setup last accounts used for this customer
1688 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1690 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1692 JOIN acc_trans ac ON (ac.chart_id = c.id)
1693 JOIN ar a ON (a.id = ac.trans_id)
1694 WHERE a.customer_id = ?
1695 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1696 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1697 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1700 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1701 if ($ref->{category} eq 'I') {
1703 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1705 if ($form->{initial_transdate}) {
1707 qq|SELECT tk.tax_id, t.rate
1709 LEFT JOIN tax t ON tk.tax_id = t.id
1710 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1711 ORDER BY tk.startdate DESC
1713 my ($tax_id, $rate) =
1714 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1715 $form->{initial_transdate});
1716 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1719 if ($ref->{category} eq 'A') {
1720 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1724 $form->{rowcount} = $i if ($i && !$form->{type});
1729 $main::lxdebug->leave_sub();
1733 $main::lxdebug->enter_sub();
1735 my ($self, $myconfig, $form) = @_;
1737 # connect to database
1738 my $dbh = $form->dbconnect($myconfig);
1740 my $i = $form->{rowcount};
1742 my $where = qq|NOT p.obsolete = '1'|;
1745 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1746 my ($table, $field) = split m/\./, $column;
1747 next if !$form->{"${field}_${i}"};
1748 $where .= qq| AND lower(${column}) ILIKE ?|;
1749 push @values, '%' . $form->{"${field}_${i}"} . '%';
1752 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1753 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1754 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1755 push @values, $form->{"partnumber_$i"};
1758 if ($form->{"description_$i"}) {
1759 $where .= qq| ORDER BY p.description|;
1761 $where .= qq| ORDER BY p.partnumber|;
1765 if ($form->{type} eq "invoice") {
1767 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1768 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1772 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1776 my $taxzone_id = $form->{taxzone_id} * 1;
1777 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1781 p.id, p.partnumber, p.description, p.sellprice,
1782 p.listprice, p.inventory_accno_id, p.lastcost,
1784 c1.accno AS inventory_accno,
1785 c1.new_chart_id AS inventory_new_chart,
1786 date($transdate) - c1.valid_from AS inventory_valid,
1788 c2.accno AS income_accno,
1789 c2.new_chart_id AS income_new_chart,
1790 date($transdate) - c2.valid_from AS income_valid,
1792 c3.accno AS expense_accno,
1793 c3.new_chart_id AS expense_new_chart,
1794 date($transdate) - c3.valid_from AS expense_valid,
1796 p.unit, p.assembly, p.bin, p.onhand,
1797 p.notes AS partnotes, p.notes AS longdescription,
1798 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1801 pfac.factor AS price_factor,
1806 LEFT JOIN chart c1 ON
1807 ((SELECT inventory_accno_id
1808 FROM buchungsgruppen
1809 WHERE id = p.buchungsgruppen_id) = c1.id)
1810 LEFT JOIN chart c2 ON
1811 ((SELECT income_accno_id_${taxzone_id}
1812 FROM buchungsgruppen
1813 WHERE id = p.buchungsgruppen_id) = c2.id)
1814 LEFT JOIN chart c3 ON
1815 ((SELECT expense_accno_id_${taxzone_id}
1816 FROM buchungsgruppen
1817 WHERE id = p.buchungsgruppen_id) = c3.id)
1818 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1819 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1821 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1823 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1825 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1826 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1827 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1828 if (!$ref->{inventory_accno_id}) {
1829 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1831 delete($ref->{inventory_accno_id});
1833 foreach my $type (qw(inventory income expense)) {
1834 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1836 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1839 ($ref->{"${type}_accno"},
1840 $ref->{"${type}_new_chart"},
1841 $ref->{"${type}_valid"})
1842 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1846 if ($form->{payment_id} eq "") {
1847 $form->{payment_id} = $form->{part_payment_id};
1850 # get tax rates and description
1851 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1853 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1855 LEFT JOIN chart c ON (c.id = t.chart_id)
1859 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1861 ORDER BY startdate DESC
1864 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1865 my $stw = $dbh->prepare($query);
1866 $stw->execute(@values) || $form->dberror($query);
1868 $ref->{taxaccounts} = "";
1870 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1872 # if ($customertax{$ref->{accno}})
1873 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1877 $ref->{taxaccounts} .= "$ptr->{accno} ";
1879 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1880 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1881 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1882 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1883 $form->{taxaccounts} .= "$ptr->{accno} ";
1889 chop $ref->{taxaccounts};
1890 if ($form->{language_id}) {
1892 qq|SELECT tr.translation, tr.longdescription
1894 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1895 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1896 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1897 if ($translation ne "") {
1898 $ref->{description} = $translation;
1899 $ref->{longdescription} = $longdescription;
1903 qq|SELECT tr.translation, tr.longdescription
1905 WHERE tr.language_id IN
1908 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1911 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1912 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1913 if ($translation ne "") {
1914 $ref->{description} = $translation;
1915 $ref->{longdescription} = $longdescription;
1920 $ref->{onhand} *= 1;
1922 push @{ $form->{item_list} }, $ref;
1924 if ($form->{lizenzen}) {
1925 if ($ref->{inventory_accno} > 0) {
1929 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1930 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1931 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1932 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1940 foreach my $item (@{ $form->{item_list} }) {
1941 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1942 trans_id => $item->{id},
1946 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1951 $main::lxdebug->leave_sub();
1954 ##########################
1955 # get pricegroups from database
1956 # build up selected pricegroup
1957 # if an exchange rate - change price
1960 sub get_pricegroups_for_parts {
1962 $main::lxdebug->enter_sub();
1964 my ($self, $myconfig, $form) = @_;
1966 my $dbh = $form->dbconnect($myconfig);
1968 $form->{"PRICES"} = {};
1972 my $all_units = AM->retrieve_units($myconfig, $form);
1973 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1974 $form->{"PRICES"}{$i} = [];
1976 $id = $form->{"id_$i"};
1978 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1980 $id = $form->{"new_id_$i"};
1983 my ($price, $selectedpricegroup_id) = split(/--/,
1984 $form->{"sellprice_pg_$i"});
1986 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1987 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1988 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1990 my $price_new = $form->{"price_new_$i"};
1991 my $price_old = $form->{"price_old_$i"};
1993 if (!$form->{"unit_old_$i"}) {
1994 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1995 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1996 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1997 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2000 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2001 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2002 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2004 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2005 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2006 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2007 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2008 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2009 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2010 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2015 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2016 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2017 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2018 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2019 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2023 if (!$form->{"basefactor_$i"}) {
2024 $form->{"basefactor_$i"} = 1;
2030 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2031 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2041 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2043 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2044 'selected' AS selected
2047 ORDER BY pricegroup|;
2048 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2049 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2051 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2053 $pkr->{selected} = '';
2055 # if there is an exchange rate change price
2056 if (($form->{exchangerate} * 1) != 0) {
2058 $pkr->{price} /= $form->{exchangerate};
2061 $pkr->{price} *= $form->{"basefactor_$i"};
2063 $pkr->{price} *= $basefactor;
2065 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2067 if ($selectedpricegroup_id eq undef) {
2068 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2070 $pkr->{selected} = ' selected';
2072 # no customer pricesgroup set
2073 if ($pkr->{price} == $pkr->{default_sellprice}) {
2075 $pkr->{price} = $form->{"sellprice_$i"};
2079 # this sub should not set anything and only return. --sschoeling, 20090506
2080 # $form->{"sellprice_$i"} = $pkr->{price};
2083 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2084 $pkr->{price} = $form->{"sellprice_$i"};
2085 $pkr->{selected} = ' selected';
2089 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2090 if ($selectedpricegroup_id ne $pricegroup_old) {
2091 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2092 $pkr->{selected} = ' selected';
2094 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2095 if ($pkr->{pricegroup_id} == 0) {
2096 $pkr->{price} = $form->{"sellprice_$i"};
2097 $pkr->{selected} = ' selected';
2099 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2100 $pkr->{selected} = ' selected';
2101 if ( ($pkr->{pricegroup_id} == 0)
2102 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2103 # $pkr->{price} = $form->{"sellprice_$i"};
2105 $pkr->{price} = $form->{"sellprice_$i"};
2109 push @{ $form->{PRICES}{$i} }, $pkr;
2112 $form->{"basefactor_$i"} *= $basefactor;
2121 $main::lxdebug->leave_sub();
2125 $main::lxdebug->enter_sub();
2127 my ($self, $myconfig, $form, $table) = @_;
2129 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2131 # make sure there's no funny stuff in $table
2132 # ToDO: die when this happens and throw an error
2133 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2135 my $dbh = $form->dbconnect($myconfig);
2137 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2138 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2142 $main::lxdebug->leave_sub();
2148 $main::lxdebug->enter_sub();
2150 my ($self, $myconfig, $form, $table, $id) = @_;
2152 $main::lxdebug->leave_sub() and return 0 unless ($id);
2154 # make sure there's no funny stuff in $table
2155 # ToDO: die when this happens and throw an error
2156 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2158 my $dbh = $form->dbconnect($myconfig);
2160 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2161 my ($result) = selectrow_query($form, $dbh, $query, $id);
2165 $main::lxdebug->leave_sub();