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;
54 $main::lxdebug->enter_sub();
56 my ($self, $myconfig, $form, $locale) = @_;
58 $form->{duedate} ||= $form->{invdate};
61 my $dbh = $form->dbconnect($myconfig);
64 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
65 ($form->{terms}) = selectrow_query($form, $dbh, $query);
67 my (@project_ids, %projectnumbers);
68 $form->{TEMPLATE_ARRAYS} = {};
70 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
72 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
75 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
76 $price_factors{$pfac->{id}} = $pfac;
78 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
81 # sort items by partsgroup
82 for my $i (1 .. $form->{rowcount}) {
84 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
85 # $partsgroup = $form->{"partsgroup_$i"};
87 # push @partsgroup, [$i, $partsgroup];
88 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
92 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
93 join(", ", map({ "?" } @project_ids)) . ")";
94 $sth = $dbh->prepare($query);
95 $sth->execute(@project_ids) ||
96 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
97 while (my $ref = $sth->fetchrow_hashref()) {
98 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
103 $form->{"globalprojectnumber"} =
104 $projectnumbers{$form->{"globalproject_id"}};
111 my %oid = ('Pg' => 'oid',
112 'Oracle' => 'rowid');
114 # sort items by partsgroup
115 for $i (1 .. $form->{rowcount}) {
117 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
118 $partsgroup = $form->{"partsgroup_$i"};
120 push @partsgroup, [$i, $partsgroup];
133 my $nodiscount_subtotal = 0;
134 my $discount_subtotal = 0;
136 my $subtotal_header = 0;
139 $form->{discount} = [];
141 IC->prepare_parts_for_printing();
143 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
146 qw(runningnumber number description longdescription qty ship unit bin
147 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
148 partnotes serialnumber reqdate sellprice listprice netprice
149 discount p_discount discount_sub nodiscount_sub
150 linetotal nodiscount_linetotal tax_rate projectnumber
151 price_factor price_factor_name partsgroup);
153 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
155 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
157 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
159 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
161 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
164 if ($item->[1] ne $sameitem) {
165 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
166 $sameitem = $item->[1];
168 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
171 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
173 if ($form->{"id_$i"} != 0) {
175 # add number, description and qty to $form->{number},
176 if ($form->{"subtotal_$i"} && !$subtotal_header) {
177 $subtotal_header = $i;
178 $position = int($position);
181 } elsif ($subtotal_header) {
183 $position = int($position);
184 $position = $position.".".$subposition;
186 $position = int($position);
190 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
192 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
193 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
194 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
195 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
196 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
200 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
208 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
209 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
212 if ($form->{lizenzen}) {
213 if ($form->{"licensenumber_$i"}) {
214 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
215 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
216 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
217 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
220 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
221 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
226 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
228 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
229 my ($dec) = ($sellprice =~ /\.(\d+)/);
230 my $decimalplaces = max 2, length($dec);
232 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
233 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
234 my $linetotal = $form->round_amount($linetotal_exact, 2);
235 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
237 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
238 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
240 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
242 $linetotal = ($linetotal != 0) ? $linetotal : '';
244 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
245 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
247 $form->{total} += $linetotal;
248 $form->{nodiscount_total} += $nodiscount_linetotal;
249 $form->{discount_total} += $discount;
251 if ($subtotal_header) {
252 $discount_subtotal += $linetotal;
253 $nodiscount_subtotal += $nodiscount_linetotal;
256 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
257 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
258 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
260 $discount_subtotal = 0;
261 $nodiscount_subtotal = 0;
262 $subtotal_header = 0;
265 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
266 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
269 if (!$form->{"discount_$i"}) {
270 $nodiscount += $linetotal;
273 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
274 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
276 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
278 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
282 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
284 if ($form->{taxincluded}) {
287 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
288 $taxbase = $linetotal - $taxamount;
290 $taxamount = $linetotal * $taxrate;
291 $taxbase = $linetotal;
294 if ($form->round_amount($taxrate, 7) == 0) {
295 if ($form->{taxincluded}) {
296 foreach my $accno (@taxaccounts) {
297 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
299 $taxaccounts{$accno} += $taxamount;
300 $taxdiff += $taxamount;
302 $taxbase{$accno} += $taxbase;
304 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
306 foreach my $accno (@taxaccounts) {
307 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
308 $taxbase{$accno} += $taxbase;
312 foreach my $accno (@taxaccounts) {
313 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
314 $taxbase{$accno} += $taxbase;
317 my $tax_rate = $taxrate * 100;
318 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
319 if ($form->{"assembly_$i"}) {
322 # get parts and push them onto the stack
324 if ($form->{groupitems}) {
326 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
328 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
332 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
334 JOIN parts p ON (a.parts_id = p.id)
335 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
336 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
337 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
339 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
340 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
341 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
342 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
343 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
346 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
349 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
351 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
352 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
358 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
362 foreach my $item (sort keys %taxaccounts) {
363 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
365 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
366 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
368 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
369 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
372 for my $i (1 .. $form->{paidaccounts}) {
373 if ($form->{"paid_$i"}) {
374 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
376 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
377 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
378 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
379 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
382 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
385 if($form->{taxincluded}) {
386 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
389 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
392 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
393 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
394 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
395 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
397 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
398 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
400 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
401 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
403 $form->set_payment_options($myconfig, $form->{invdate});
405 $form->{username} = $myconfig->{name};
409 $main::lxdebug->leave_sub();
412 sub project_description {
413 $main::lxdebug->enter_sub();
415 my ($self, $dbh, $id) = @_;
416 my $form = \%main::form;
418 my $query = qq|SELECT description FROM project WHERE id = ?|;
419 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
421 $main::lxdebug->leave_sub();
426 sub customer_details {
427 $main::lxdebug->enter_sub();
429 my ($self, $myconfig, $form, @wanted_vars) = @_;
431 # connect to database
432 my $dbh = $form->dbconnect($myconfig);
434 my $language_id = $form->{language_id};
436 # get contact id, set it if nessessary
439 my @values = (conv_i($form->{customer_id}));
442 if ($form->{cp_id}) {
443 $where = qq| AND (cp.cp_id = ?) |;
444 push(@values, conv_i($form->{cp_id}));
447 # get rest for the customer
449 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
450 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
452 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
453 WHERE (ct.id = ?) $where
456 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
458 # remove id and taxincluded before copy back
459 delete @$ref{qw(id taxincluded)};
461 @wanted_vars = grep({ $_ } @wanted_vars);
462 if (scalar(@wanted_vars) > 0) {
464 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
465 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
468 map { $form->{$_} = $ref->{$_} } keys %$ref;
470 if ($form->{delivery_customer_id}) {
472 qq|SELECT *, notes as customernotes
476 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
478 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
481 if ($form->{delivery_vendor_id}) {
483 qq|SELECT *, notes as customernotes
487 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
489 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
492 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
494 'trans_id' => $form->{customer_id});
495 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
497 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
498 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
499 'language_id' => $language_id,
500 'allow_fallback' => 1);
505 $main::lxdebug->leave_sub();
509 $main::lxdebug->enter_sub();
511 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
513 # connect to database, turn off autocommit
514 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
516 my ($query, $sth, $null, $project_id, @values);
517 my $exchangerate = 0;
519 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
522 if (!$form->{employee_id}) {
523 $form->get_employee($dbh);
526 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
528 ($null, $form->{department_id}) = split(/--/, $form->{department});
530 my $all_units = AM->retrieve_units($myconfig, $form);
532 if (!$payments_only) {
534 &reverse_invoice($dbh, $form);
537 $query = qq|SELECT nextval('glid')|;
538 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
540 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
541 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
543 if (!$form->{invnumber}) {
545 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
546 "cnnumber" : "invnumber", $dbh);
551 my ($netamount, $invoicediff) = (0, 0);
552 my ($amount, $linetotal, $lastincomeaccno);
554 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
555 my $defaultcurrency = (split m/:/, $currencies)[0];
557 if ($form->{currency} eq $defaultcurrency) {
558 $form->{exchangerate} = 1;
560 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
563 $form->{exchangerate} =
566 : $form->parse_amount($myconfig, $form->{exchangerate});
568 $form->{expense_inventory} = "";
572 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
573 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
576 $form->{amount} = {};
577 $form->{amount_cogs} = {};
579 foreach my $i (1 .. $form->{rowcount}) {
580 if ($form->{type} eq "credit_note") {
581 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
582 $form->{shipped} = 1;
584 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
589 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
590 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
591 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
593 if ($form->{storno}) {
594 $form->{"qty_$i"} *= -1;
597 if ($form->{"id_$i"}) {
600 if (defined($baseunits{$form->{"id_$i"}})) {
601 $item_unit = $baseunits{$form->{"id_$i"}};
604 $query = qq|SELECT unit FROM parts WHERE id = ?|;
605 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
606 $baseunits{$form->{"id_$i"}} = $item_unit;
609 if (defined($all_units->{$item_unit}->{factor})
610 && ($all_units->{$item_unit}->{factor} ne '')
611 && ($all_units->{$item_unit}->{factor} != 0)) {
612 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
616 $baseqty = $form->{"qty_$i"} * $basefactor;
618 my ($allocated, $taxrate) = (0, 0);
622 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
624 # keep entered selling price
626 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
628 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
630 my $decimalplaces = ($dec > 2) ? $dec : 2;
632 # undo discount formatting
633 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
636 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
638 # round linetotal to 2 decimal places
639 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
640 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
642 if ($form->{taxincluded}) {
643 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
644 $form->{"sellprice_$i"} =
645 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
647 $taxamount = $linetotal * $taxrate;
650 $netamount += $linetotal;
652 if ($taxamount != 0) {
654 $form->{amount}{ $form->{id} }{$_} +=
655 $taxamount * $form->{"${_}_rate"} / $taxrate
656 } split(/ /, $form->{"taxaccounts_$i"});
659 # add amount to income, $form->{amount}{trans_id}{accno}
660 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
662 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
663 $linetotal = $form->round_amount($linetotal, 2);
665 # this is the difference from the inventory
666 $invoicediff += ($amount - $linetotal);
668 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
671 $lastincomeaccno = $form->{"income_accno_$i"};
673 # adjust and round sellprice
674 $form->{"sellprice_$i"} =
675 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
678 next if $payments_only;
680 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
682 if ($form->{"assembly_$i"}) {
683 # record assembly item as allocated
684 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
687 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
691 # get pricegroup_id and save it
692 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
695 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
697 # save detail record in invoice table
699 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
700 sellprice, fxsellprice, discount, allocated, assemblyitem,
701 unit, deliverydate, project_id, serialnumber, pricegroup_id,
702 ordnumber, transdate, cusordnumber, base_qty, subtotal,
703 marge_percent, marge_total, lastcost,
704 price_factor_id, price_factor, marge_price_factor)
705 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
706 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
708 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
709 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
710 $form->{"sellprice_$i"}, $fxsellprice,
711 $form->{"discount_$i"}, $allocated, 'f',
712 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
713 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
714 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
715 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
716 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
717 $form->{"lastcost_$i"},
718 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
719 conv_i($form->{"marge_price_factor_$i"}));
720 do_query($form, $dbh, $query, @values);
722 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
724 qq|INSERT INTO licenseinvoice (trans_id, license_id)
725 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
726 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
727 do_query($form, $dbh, $query, @values);
730 CVar->save_custom_variables(module => 'IC',
731 sub_module => 'invoice',
732 trans_id => $invoice_id,
733 configs => $ic_cvar_configs,
735 name_prefix => 'ic_',
736 name_postfix => "_$i",
741 # total payments, don't move we need it here
742 for my $i (1 .. $form->{paidaccounts}) {
743 if ($form->{type} eq "credit_note") {
744 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
746 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
748 $form->{paid} += $form->{"paid_$i"};
749 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
752 my ($tax, $diff) = (0, 0);
754 $netamount = $form->round_amount($netamount, 2);
756 # figure out rounding errors for total amount vs netamount + taxes
757 if ($form->{taxincluded}) {
759 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
760 $diff += $amount - $netamount * $form->{exchangerate};
761 $netamount = $amount;
763 foreach my $item (split(/ /, $form->{taxaccounts})) {
764 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
765 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
766 $tax += $form->{amount}{ $form->{id} }{$item};
767 $netamount -= $form->{amount}{ $form->{id} }{$item};
770 $invoicediff += $diff;
771 ######## this only applies to tax included
772 if ($lastincomeaccno) {
773 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
777 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
778 $diff = $amount - $netamount * $form->{exchangerate};
779 $netamount = $amount;
780 foreach my $item (split(/ /, $form->{taxaccounts})) {
781 $form->{amount}{ $form->{id} }{$item} =
782 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
785 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
788 $amount - $form->{amount}{ $form->{id} }{$item} *
789 $form->{exchangerate};
790 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
791 $tax += $form->{amount}{ $form->{id} }{$item};
795 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
797 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
800 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
802 # update exchangerate
803 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
804 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
805 $form->{exchangerate}, 0);
808 $project_id = conv_i($form->{"globalproject_id"});
810 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
811 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
812 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
814 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
816 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
818 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
819 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
820 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
821 do_query($form, $dbh, $query, @values);
822 $form->{amount_cogs}{$trans_id}{$accno} = 0;
826 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
827 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
829 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
833 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
834 do_query($form, $dbh, $query, @values);
839 foreach my $trans_id (keys %{ $form->{amount} }) {
840 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
841 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
843 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
845 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
847 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
848 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
849 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
850 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
851 do_query($form, $dbh, $query, @values);
852 $form->{amount}{$trans_id}{$accno} = 0;
856 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
857 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
859 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
861 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
862 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
863 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
864 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
865 do_query($form, $dbh, $query, @values);
870 # deduct payment differences from diff
871 for my $i (1 .. $form->{paidaccounts}) {
872 if ($form->{"paid_$i"} != 0) {
874 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
875 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
879 # record payments and offsetting AR
880 if (!$form->{storno}) {
881 for my $i (1 .. $form->{paidaccounts}) {
883 next if ($form->{"paid_$i"} == 0);
885 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
886 $form->{"datepaid_$i"} = $form->{invdate}
887 unless ($form->{"datepaid_$i"});
888 $form->{datepaid} = $form->{"datepaid_$i"};
892 if ($form->{currency} eq $defaultcurrency) {
893 $form->{"exchangerate_$i"} = 1;
895 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
896 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
900 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
902 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
904 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
905 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
906 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
907 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
908 do_query($form, $dbh, $query, @values);
912 $form->{"paid_$i"} *= -1;
915 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
916 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
917 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
918 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
919 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
920 do_query($form, $dbh, $query, @values);
922 # exchangerate difference
923 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
924 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
928 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
929 $form->{"exchangerate_$i"};
931 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
934 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
940 # update exchange rate
941 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
942 $form->update_exchangerate($dbh, $form->{currency},
943 $form->{"datepaid_$i"},
944 $form->{"exchangerate_$i"}, 0);
948 } else { # if (!$form->{storno})
949 $form->{marge_total} *= -1;
952 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
954 if ($payments_only) {
955 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
956 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
958 if (!$provided_dbh) {
963 $main::lxdebug->leave_sub();
967 # record exchange rate differences and gains/losses
968 foreach my $accno (keys %{ $form->{fx} }) {
969 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
971 ($form->{fx}{$accno}{$transdate} =
972 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
977 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
978 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
979 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
980 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
981 do_query($form, $dbh, $query, @values);
986 $amount = $netamount + $tax;
989 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
991 $query = qq|UPDATE ar set
992 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
993 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
994 amount = ?, netamount = ?, paid = ?,
995 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
996 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
997 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
998 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
999 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1000 cp_id = ?, marge_total = ?, marge_percent = ?,
1001 globalproject_id = ?, delivery_customer_id = ?,
1002 transaction_description = ?, delivery_vendor_id = ?,
1005 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1006 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1007 $amount, $netamount, $form->{"paid"},
1008 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1009 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1010 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1011 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1012 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1013 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1014 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1015 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1016 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
1017 conv_i($form->{"id"}));
1018 do_query($form, $dbh, $query, @values);
1021 if ($form->{storno}) {
1024 paid = paid + amount,
1026 intnotes = ? || intnotes
1028 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1029 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1033 $form->{name} = $form->{customer};
1034 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1036 if (!$form->{shipto_id}) {
1037 $form->add_shipto($dbh, $form->{id}, "AR");
1040 # save printed, emailed and queued
1041 $form->save_status($dbh);
1043 Common::webdav_folder($form) if ($main::webdav);
1045 # Link this record to the records it was created from.
1046 RecordLinks->create_links('dbh' => $dbh,
1048 'from_table' => 'oe',
1049 'from_ids' => $form->{convert_from_oe_ids},
1051 'to_id' => $form->{id},
1053 delete $form->{convert_from_oe_ids};
1055 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1057 if (scalar @convert_from_do_ids) {
1058 DO->close_orders('dbh' => $dbh,
1059 'ids' => \@convert_from_do_ids);
1061 RecordLinks->create_links('dbh' => $dbh,
1063 'from_table' => 'delivery_orders',
1064 'from_ids' => \@convert_from_do_ids,
1066 'to_id' => $form->{id},
1069 delete $form->{convert_from_do_ids};
1071 ARAP->close_orders_if_billed('dbh' => $dbh,
1072 'arap_id' => $form->{id},
1076 if (!$provided_dbh) {
1081 $main::lxdebug->leave_sub();
1086 sub _delete_payments {
1087 $main::lxdebug->enter_sub();
1089 my ($self, $form, $dbh) = @_;
1091 my @delete_acc_trans_ids;
1093 # Delete old payment entries from acc_trans.
1095 qq|SELECT acc_trans_id
1097 WHERE (trans_id = ?) AND fx_transaction
1101 SELECT at.acc_trans_id
1103 LEFT JOIN chart c ON (at.chart_id = c.id)
1104 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1105 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1108 qq|SELECT at.acc_trans_id
1110 LEFT JOIN chart c ON (at.chart_id = c.id)
1111 WHERE (trans_id = ?)
1112 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1113 ORDER BY at.acc_trans_id
1115 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1117 if (@delete_acc_trans_ids) {
1118 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1119 do_query($form, $dbh, $query);
1122 $main::lxdebug->leave_sub();
1126 $main::lxdebug->enter_sub();
1128 my ($self, $myconfig, $form, $locale) = @_;
1130 # connect to database, turn off autocommit
1131 my $dbh = $form->dbconnect_noauto($myconfig);
1133 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1135 $old_form = save_form();
1137 # Delete all entries in acc_trans from prior payments.
1138 $self->_delete_payments($form, $dbh);
1140 # Save the new payments the user made before cleaning up $form.
1141 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1143 # Clean up $form so that old content won't tamper the results.
1144 %keep_vars = map { $_, 1 } qw(login password id);
1145 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1147 # Retrieve the invoice from the database.
1148 $self->retrieve_invoice($myconfig, $form);
1150 # Set up the content of $form in the way that IS::post_invoice() expects.
1151 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1153 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1154 $item = $form->{invoice_details}->[$row - 1];
1156 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1158 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1161 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1163 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1165 # Restore the payment options from the user input.
1166 map { $form->{$_} = $payments{$_} } keys %payments;
1168 # Get the AR accno (which is normally done by Form::create_links()).
1172 LEFT JOIN chart c ON (at.chart_id = c.id)
1173 WHERE (trans_id = ?)
1174 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1175 ORDER BY at.acc_trans_id
1178 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1180 # Post the new payments.
1181 $self->post_invoice($myconfig, $form, $dbh, 1);
1183 restore_form($old_form);
1185 my $rc = $dbh->commit();
1188 $main::lxdebug->leave_sub();
1193 sub process_assembly {
1194 $main::lxdebug->enter_sub();
1196 my ($dbh, $form, $id, $totalqty) = @_;
1199 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1200 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1202 JOIN parts p ON (a.parts_id = p.id)
1204 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1206 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1210 $ref->{inventory_accno_id} *= 1;
1211 $ref->{expense_accno_id} *= 1;
1213 # multiply by number of assemblies
1214 $ref->{qty} *= $totalqty;
1216 if ($ref->{assembly}) {
1217 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1220 if ($ref->{inventory_accno_id}) {
1221 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1225 # save detail record for individual assembly item in invoice table
1227 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1228 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1229 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1230 do_query($form, $dbh, $query, @values);
1236 $main::lxdebug->leave_sub();
1240 $main::lxdebug->enter_sub();
1242 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1246 $form->{taxzone_id} *=1;
1247 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1248 my $taxzone_id = $form->{"taxzone_id"} * 1;
1250 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1251 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1252 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1253 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1254 FROM invoice i, parts p
1255 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1256 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1257 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1258 WHERE (i.parts_id = p.id)
1259 AND (i.parts_id = ?)
1260 AND ((i.base_qty + i.allocated) < 0)
1262 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1267 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1268 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1272 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1274 # total expenses and inventory
1275 # sellprice is the cost of the item
1276 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1279 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1281 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1282 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1283 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1285 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1286 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1292 last if (($totalqty -= $qty) <= 0);
1297 $main::lxdebug->leave_sub();
1302 sub reverse_invoice {
1303 $main::lxdebug->enter_sub();
1305 my ($dbh, $form) = @_;
1307 # reverse inventory items
1309 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1311 JOIN parts p ON (i.parts_id = p.id)
1312 WHERE i.trans_id = ?|;
1313 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1315 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1317 if ($ref->{inventory_accno_id}) {
1318 # de-allocated purchases
1320 qq|SELECT i.id, i.trans_id, i.allocated
1322 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1323 ORDER BY i.trans_id DESC|;
1324 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1326 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1327 my $qty = $ref->{qty};
1328 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1329 $qty = $inhref->{allocated};
1333 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1335 last if (($ref->{qty} -= $qty) <= 0);
1344 my @values = (conv_i($form->{id}));
1345 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1346 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1348 if ($form->{lizenzen}) {
1350 qq|DELETE FROM licenseinvoice
1351 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1352 do_query($form, $dbh, $query, @values);
1355 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1357 $main::lxdebug->leave_sub();
1360 sub delete_invoice {
1361 $main::lxdebug->enter_sub();
1363 my ($self, $myconfig, $form, $spool) = @_;
1365 # connect to database
1366 my $dbh = $form->dbconnect_noauto($myconfig);
1368 &reverse_invoice($dbh, $form);
1370 my @values = (conv_i($form->{id}));
1373 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1375 # delete spool files
1376 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1378 # delete status entries
1379 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1381 my $rc = $dbh->commit;
1385 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1388 $main::lxdebug->leave_sub();
1393 sub retrieve_invoice {
1394 $main::lxdebug->enter_sub();
1396 my ($self, $myconfig, $form) = @_;
1398 # connect to database
1399 my $dbh = $form->dbconnect_noauto($myconfig);
1401 my ($sth, $ref, $query);
1403 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1407 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1408 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1409 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1410 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1411 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1412 d.curr AS currencies
1416 $ref = selectfirst_hashref_query($form, $dbh, $query);
1417 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1420 my $id = conv_i($form->{id});
1423 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1427 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1428 a.orddate, a.quodate, a.globalproject_id,
1429 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1430 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1431 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1432 a.employee_id, a.salesman_id, a.payment_id,
1433 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1434 a.transaction_description,
1435 a.marge_total, a.marge_percent,
1436 e.name AS employee, a.donumber
1438 LEFT JOIN employee e ON (e.id = a.employee_id)
1440 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1441 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1444 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1447 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1448 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1450 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1452 foreach my $vc (qw(customer vendor)) {
1453 next if !$form->{"delivery_${vc}_id"};
1454 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1457 # get printed, emailed
1458 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1459 $sth = prepare_execute_query($form, $dbh, $query, $id);
1461 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1462 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1463 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1464 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1467 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1469 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1470 : $form->{invdate} ? $dbh->quote($form->{invdate})
1474 my $taxzone_id = $form->{taxzone_id} *= 1;
1475 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1477 # retrieve individual items
1480 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1481 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1482 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1485 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1486 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1487 i.price_factor_id, i.price_factor, i.marge_price_factor,
1488 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1489 pr.projectnumber, pg.partsgroup, prg.pricegroup
1492 LEFT JOIN parts p ON (i.parts_id = p.id)
1493 LEFT JOIN project pr ON (i.project_id = pr.id)
1494 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1495 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1497 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1498 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1499 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1501 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1503 $sth = prepare_execute_query($form, $dbh, $query, $id);
1505 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1506 # Retrieve custom variables.
1507 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1509 sub_module => 'invoice',
1510 trans_id => $ref->{invoice_id},
1512 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1513 delete $ref->{invoice_id};
1515 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1516 delete($ref->{"part_inventory_accno_id"});
1518 foreach my $type (qw(inventory income expense)) {
1519 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1520 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1521 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1525 # get tax rates and description
1526 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1528 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1529 LEFT JOIN chart c ON (c.id = t.chart_id)
1531 (SELECT tk.tax_id FROM taxkeys tk
1532 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1533 AND startdate <= date($transdate)
1534 ORDER BY startdate DESC LIMIT 1)
1536 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1537 $ref->{taxaccounts} = "";
1539 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1541 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1545 $ref->{taxaccounts} .= "$ptr->{accno} ";
1547 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1548 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1549 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1550 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1551 $form->{taxaccounts} .= "$ptr->{accno} ";
1556 if ($form->{lizenzen}) {
1557 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1558 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1559 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1562 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1564 chop $ref->{taxaccounts};
1565 push @{ $form->{invoice_details} }, $ref;
1570 Common::webdav_folder($form) if ($main::webdav);
1573 my $rc = $dbh->commit;
1576 $main::lxdebug->leave_sub();
1582 $main::lxdebug->enter_sub();
1584 my ($self, $myconfig, $form) = @_;
1586 # connect to database
1587 my $dbh = $form->dbconnect($myconfig);
1589 my $dateformat = $myconfig->{dateformat};
1590 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1592 my (@values, $duedate, $ref, $query);
1594 if ($form->{invdate}) {
1595 $duedate = "to_date(?, '$dateformat')";
1596 push @values, $form->{invdate};
1598 $duedate = "current_date";
1601 my $cid = conv_i($form->{customer_id});
1604 if ($form->{payment_id}) {
1605 $payment_id = "(pt.id = ?) OR";
1606 push @values, conv_i($form->{payment_id});
1612 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1613 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1614 c.street, c.zipcode, c.city, c.country,
1615 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1616 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1617 b.discount AS tradediscount, b.description AS business
1619 LEFT JOIN business b ON (b.id = c.business_id)
1620 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1623 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1625 delete $ref->{salesman_id} if !$ref->{salesman_id};
1627 map { $form->{$_} = $ref->{$_} } keys %$ref;
1630 qq|SELECT sum(amount - paid) AS dunning_amount
1632 WHERE (paid < amount)
1633 AND (customer_id = ?)
1634 AND (dunning_config_id IS NOT NULL)|;
1635 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1636 map { $form->{$_} = $ref->{$_} } keys %$ref;
1639 qq|SELECT dnn.dunning_description AS max_dunning_level
1640 FROM dunning_config dnn
1641 WHERE id IN (SELECT dunning_config_id
1643 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1644 ORDER BY dunning_level DESC LIMIT 1|;
1645 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1646 map { $form->{$_} = $ref->{$_} } keys %$ref;
1648 $form->{creditremaining} = $form->{creditlimit};
1649 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1650 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1651 $form->{creditremaining} -= $value;
1655 (SELECT e.buy FROM exchangerate e
1656 WHERE e.curr = o.curr
1657 AND e.transdate = o.transdate)
1659 WHERE o.customer_id = ?
1660 AND o.quotation = '0'
1661 AND o.closed = '0'|;
1662 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1664 while (my ($amount, $exch) = $sth->fetchrow_array) {
1665 $exch = 1 unless $exch;
1666 $form->{creditremaining} -= $amount * $exch;
1670 # get shipto if we did not converted an order or invoice
1671 if (!$form->{shipto}) {
1672 map { delete $form->{$_} }
1673 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1674 shiptostreet shiptozipcode shiptocity shiptocountry
1675 shiptocontact shiptophone shiptofax shiptoemail);
1677 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1678 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1680 map { $form->{$_} = $ref->{$_} } keys %$ref;
1683 # setup last accounts used for this customer
1684 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1686 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1688 JOIN acc_trans ac ON (ac.chart_id = c.id)
1689 JOIN ar a ON (a.id = ac.trans_id)
1690 WHERE a.customer_id = ?
1691 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1692 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1693 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1696 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1697 if ($ref->{category} eq 'I') {
1699 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1701 if ($form->{initial_transdate}) {
1703 qq|SELECT tk.tax_id, t.rate
1705 LEFT JOIN tax t ON tk.tax_id = t.id
1706 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1707 ORDER BY tk.startdate DESC
1709 my ($tax_id, $rate) =
1710 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1711 $form->{initial_transdate});
1712 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1715 if ($ref->{category} eq 'A') {
1716 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1720 $form->{rowcount} = $i if ($i && !$form->{type});
1725 $main::lxdebug->leave_sub();
1729 $main::lxdebug->enter_sub();
1731 my ($self, $myconfig, $form) = @_;
1733 # connect to database
1734 my $dbh = $form->dbconnect($myconfig);
1736 my $i = $form->{rowcount};
1738 my $where = qq|NOT p.obsolete = '1'|;
1741 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1742 my ($table, $field) = split m/\./, $column;
1743 next if !$form->{"${field}_${i}"};
1744 $where .= qq| AND lower(${column}) ILIKE ?|;
1745 push @values, '%' . $form->{"${field}_${i}"} . '%';
1748 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1749 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1750 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1751 push @values, $form->{"partnumber_$i"};
1754 if ($form->{"description_$i"}) {
1755 $where .= qq| ORDER BY p.description|;
1757 $where .= qq| ORDER BY p.partnumber|;
1761 if ($form->{type} eq "invoice") {
1763 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1764 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1768 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1772 my $taxzone_id = $form->{taxzone_id} * 1;
1773 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1777 p.id, p.partnumber, p.description, p.sellprice,
1778 p.listprice, p.inventory_accno_id, p.lastcost,
1780 c1.accno AS inventory_accno,
1781 c1.new_chart_id AS inventory_new_chart,
1782 date($transdate) - c1.valid_from AS inventory_valid,
1784 c2.accno AS income_accno,
1785 c2.new_chart_id AS income_new_chart,
1786 date($transdate) - c2.valid_from AS income_valid,
1788 c3.accno AS expense_accno,
1789 c3.new_chart_id AS expense_new_chart,
1790 date($transdate) - c3.valid_from AS expense_valid,
1792 p.unit, p.assembly, p.bin, p.onhand,
1793 p.notes AS partnotes, p.notes AS longdescription,
1794 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1797 pfac.factor AS price_factor,
1802 LEFT JOIN chart c1 ON
1803 ((SELECT inventory_accno_id
1804 FROM buchungsgruppen
1805 WHERE id = p.buchungsgruppen_id) = c1.id)
1806 LEFT JOIN chart c2 ON
1807 ((SELECT income_accno_id_${taxzone_id}
1808 FROM buchungsgruppen
1809 WHERE id = p.buchungsgruppen_id) = c2.id)
1810 LEFT JOIN chart c3 ON
1811 ((SELECT expense_accno_id_${taxzone_id}
1812 FROM buchungsgruppen
1813 WHERE id = p.buchungsgruppen_id) = c3.id)
1814 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1815 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1817 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1819 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1821 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1822 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1823 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1824 if (!$ref->{inventory_accno_id}) {
1825 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1827 delete($ref->{inventory_accno_id});
1829 foreach my $type (qw(inventory income expense)) {
1830 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1832 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1835 ($ref->{"${type}_accno"},
1836 $ref->{"${type}_new_chart"},
1837 $ref->{"${type}_valid"})
1838 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1842 if ($form->{payment_id} eq "") {
1843 $form->{payment_id} = $form->{part_payment_id};
1846 # get tax rates and description
1847 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1849 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1851 LEFT JOIN chart c ON (c.id = t.chart_id)
1855 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1857 ORDER BY startdate DESC
1860 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1861 my $stw = $dbh->prepare($query);
1862 $stw->execute(@values) || $form->dberror($query);
1864 $ref->{taxaccounts} = "";
1866 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1868 # if ($customertax{$ref->{accno}})
1869 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1873 $ref->{taxaccounts} .= "$ptr->{accno} ";
1875 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1876 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1877 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1878 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1879 $form->{taxaccounts} .= "$ptr->{accno} ";
1885 chop $ref->{taxaccounts};
1886 if ($form->{language_id}) {
1888 qq|SELECT tr.translation, tr.longdescription
1890 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1891 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1892 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1893 if ($translation ne "") {
1894 $ref->{description} = $translation;
1895 $ref->{longdescription} = $longdescription;
1899 qq|SELECT tr.translation, tr.longdescription
1901 WHERE tr.language_id IN
1904 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1907 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1908 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1909 if ($translation ne "") {
1910 $ref->{description} = $translation;
1911 $ref->{longdescription} = $longdescription;
1916 $ref->{onhand} *= 1;
1918 push @{ $form->{item_list} }, $ref;
1920 if ($form->{lizenzen}) {
1921 if ($ref->{inventory_accno} > 0) {
1925 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1926 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1927 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1928 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1936 foreach my $item (@{ $form->{item_list} }) {
1937 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1938 trans_id => $item->{id},
1942 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1947 $main::lxdebug->leave_sub();
1950 ##########################
1951 # get pricegroups from database
1952 # build up selected pricegroup
1953 # if an exchange rate - change price
1956 sub get_pricegroups_for_parts {
1958 $main::lxdebug->enter_sub();
1960 my ($self, $myconfig, $form) = @_;
1962 my $dbh = $form->dbconnect($myconfig);
1964 $form->{"PRICES"} = {};
1968 my $all_units = AM->retrieve_units($myconfig, $form);
1969 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1970 $form->{"PRICES"}{$i} = [];
1972 $id = $form->{"id_$i"};
1974 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1975 $id = $form->{"new_id_$i"};
1978 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1980 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1981 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1982 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1984 my $price_new = $form->{"price_new_$i"};
1985 my $price_old = $form->{"price_old_$i"};
1987 if (!$form->{"unit_old_$i"}) {
1988 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1989 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1990 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1991 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1994 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1995 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1996 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1998 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1999 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2000 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2001 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2002 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2003 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2004 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2009 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2010 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2011 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2012 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2013 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2017 if (!$form->{"basefactor_$i"}) {
2018 $form->{"basefactor_$i"} = 1;
2024 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2025 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2035 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2037 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2038 'selected' AS selected
2041 ORDER BY pricegroup|;
2042 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2043 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2045 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2047 $pkr->{selected} = '';
2049 # if there is an exchange rate change price
2050 if (($form->{exchangerate} * 1) != 0) {
2051 $pkr->{price} /= $form->{exchangerate};
2054 $pkr->{price} *= $form->{"basefactor_$i"};
2055 $pkr->{price} *= $basefactor;
2056 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2058 if ($selectedpricegroup_id eq undef) {
2059 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2061 $pkr->{selected} = ' selected';
2063 # no customer pricesgroup set
2064 if ($pkr->{price} == $pkr->{default_sellprice}) {
2066 $pkr->{price} = $form->{"sellprice_$i"};
2070 # this sub should not set anything and only return. --sschoeling, 20090506
2071 # $form->{"sellprice_$i"} = $pkr->{price};
2074 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2075 $pkr->{price} = $form->{"sellprice_$i"};
2076 $pkr->{selected} = ' selected';
2080 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2081 if ($selectedpricegroup_id ne $pricegroup_old) {
2082 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2083 $pkr->{selected} = ' selected';
2085 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2086 if ($pkr->{pricegroup_id} == 0) {
2087 $pkr->{price} = $form->{"sellprice_$i"};
2088 $pkr->{selected} = ' selected';
2090 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2091 $pkr->{selected} = ' selected';
2092 if ( ($pkr->{pricegroup_id} == 0)
2093 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2094 # $pkr->{price} = $form->{"sellprice_$i"};
2096 $pkr->{price} = $form->{"sellprice_$i"};
2100 push @{ $form->{PRICES}{$i} }, $pkr;
2103 $form->{"basefactor_$i"} *= $basefactor;
2112 $main::lxdebug->leave_sub();
2116 $main::lxdebug->enter_sub();
2118 my ($self, $myconfig, $form, $table) = @_;
2120 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2122 # make sure there's no funny stuff in $table
2123 # ToDO: die when this happens and throw an error
2124 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2126 my $dbh = $form->dbconnect($myconfig);
2128 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2129 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2133 $main::lxdebug->leave_sub();
2139 $main::lxdebug->enter_sub();
2141 my ($self, $myconfig, $form, $table, $id) = @_;
2143 $main::lxdebug->leave_sub() and return 0 unless ($id);
2145 # make sure there's no funny stuff in $table
2146 # ToDO: die when this happens and throw an error
2147 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2149 my $dbh = $form->dbconnect($myconfig);
2151 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2152 my ($result) = selectrow_query($form, $dbh, $query, $id);
2156 $main::lxdebug->leave_sub();