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->{"deliverydate_$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);
1020 if($form->{"formname"} eq "credit_note") {
1021 for my $i (1 .. $form->{rowcount}) {
1022 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
1023 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
1024 do_query($form, $dbh, $query, @values);
1028 if ($form->{storno}) {
1031 paid = paid + amount,
1033 intnotes = ? || intnotes
1035 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1036 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1040 $form->{name} = $form->{customer};
1041 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1043 if (!$form->{shipto_id}) {
1044 $form->add_shipto($dbh, $form->{id}, "AR");
1047 # save printed, emailed and queued
1048 $form->save_status($dbh);
1050 Common::webdav_folder($form) if ($main::webdav);
1052 # Link this record to the records it was created from.
1053 RecordLinks->create_links('dbh' => $dbh,
1055 'from_table' => 'oe',
1056 'from_ids' => $form->{convert_from_oe_ids},
1058 'to_id' => $form->{id},
1060 delete $form->{convert_from_oe_ids};
1062 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1064 if (scalar @convert_from_do_ids) {
1065 DO->close_orders('dbh' => $dbh,
1066 'ids' => \@convert_from_do_ids);
1068 RecordLinks->create_links('dbh' => $dbh,
1070 'from_table' => 'delivery_orders',
1071 'from_ids' => \@convert_from_do_ids,
1073 'to_id' => $form->{id},
1076 delete $form->{convert_from_do_ids};
1078 ARAP->close_orders_if_billed('dbh' => $dbh,
1079 'arap_id' => $form->{id},
1083 if (!$provided_dbh) {
1088 $main::lxdebug->leave_sub();
1093 sub _delete_payments {
1094 $main::lxdebug->enter_sub();
1096 my ($self, $form, $dbh) = @_;
1098 my @delete_acc_trans_ids;
1100 # Delete old payment entries from acc_trans.
1102 qq|SELECT acc_trans_id
1104 WHERE (trans_id = ?) AND fx_transaction
1108 SELECT at.acc_trans_id
1110 LEFT JOIN chart c ON (at.chart_id = c.id)
1111 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1112 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1115 qq|SELECT at.acc_trans_id
1117 LEFT JOIN chart c ON (at.chart_id = c.id)
1118 WHERE (trans_id = ?)
1119 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1120 ORDER BY at.acc_trans_id
1122 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1124 if (@delete_acc_trans_ids) {
1125 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1126 do_query($form, $dbh, $query);
1129 $main::lxdebug->leave_sub();
1133 $main::lxdebug->enter_sub();
1135 my ($self, $myconfig, $form, $locale) = @_;
1137 # connect to database, turn off autocommit
1138 my $dbh = $form->dbconnect_noauto($myconfig);
1140 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1142 $old_form = save_form();
1144 # Delete all entries in acc_trans from prior payments.
1145 $self->_delete_payments($form, $dbh);
1147 # Save the new payments the user made before cleaning up $form.
1148 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1150 # Clean up $form so that old content won't tamper the results.
1151 %keep_vars = map { $_, 1 } qw(login password id);
1152 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1154 # Retrieve the invoice from the database.
1155 $self->retrieve_invoice($myconfig, $form);
1157 # Set up the content of $form in the way that IS::post_invoice() expects.
1158 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1160 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1161 $item = $form->{invoice_details}->[$row - 1];
1163 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1165 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1168 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1170 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1172 # Restore the payment options from the user input.
1173 map { $form->{$_} = $payments{$_} } keys %payments;
1175 # Get the AR accno (which is normally done by Form::create_links()).
1179 LEFT JOIN chart c ON (at.chart_id = c.id)
1180 WHERE (trans_id = ?)
1181 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1182 ORDER BY at.acc_trans_id
1185 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1187 # Post the new payments.
1188 $self->post_invoice($myconfig, $form, $dbh, 1);
1190 restore_form($old_form);
1192 my $rc = $dbh->commit();
1195 $main::lxdebug->leave_sub();
1200 sub process_assembly {
1201 $main::lxdebug->enter_sub();
1203 my ($dbh, $form, $id, $totalqty) = @_;
1206 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1207 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1209 JOIN parts p ON (a.parts_id = p.id)
1211 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1213 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1217 $ref->{inventory_accno_id} *= 1;
1218 $ref->{expense_accno_id} *= 1;
1220 # multiply by number of assemblies
1221 $ref->{qty} *= $totalqty;
1223 if ($ref->{assembly}) {
1224 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1227 if ($ref->{inventory_accno_id}) {
1228 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1232 # save detail record for individual assembly item in invoice table
1234 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1235 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1236 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1237 do_query($form, $dbh, $query, @values);
1243 $main::lxdebug->leave_sub();
1247 $main::lxdebug->enter_sub();
1249 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1253 $form->{taxzone_id} *=1;
1254 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1255 my $taxzone_id = $form->{"taxzone_id"} * 1;
1257 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1258 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1259 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1260 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1261 FROM invoice i, parts p
1262 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1263 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1264 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1265 WHERE (i.parts_id = p.id)
1266 AND (i.parts_id = ?)
1267 AND ((i.base_qty + i.allocated) < 0)
1269 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1274 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1275 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1279 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1281 # total expenses and inventory
1282 # sellprice is the cost of the item
1283 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1286 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1288 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1289 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1290 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1292 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1293 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1299 last if (($totalqty -= $qty) <= 0);
1304 $main::lxdebug->leave_sub();
1309 sub reverse_invoice {
1310 $main::lxdebug->enter_sub();
1312 my ($dbh, $form) = @_;
1314 # reverse inventory items
1316 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1318 JOIN parts p ON (i.parts_id = p.id)
1319 WHERE i.trans_id = ?|;
1320 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1322 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1324 if ($ref->{inventory_accno_id}) {
1325 # de-allocated purchases
1327 qq|SELECT i.id, i.trans_id, i.allocated
1329 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1330 ORDER BY i.trans_id DESC|;
1331 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1333 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1334 my $qty = $ref->{qty};
1335 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1336 $qty = $inhref->{allocated};
1340 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1342 last if (($ref->{qty} -= $qty) <= 0);
1351 my @values = (conv_i($form->{id}));
1352 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1353 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1355 if ($form->{lizenzen}) {
1357 qq|DELETE FROM licenseinvoice
1358 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1359 do_query($form, $dbh, $query, @values);
1362 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1364 $main::lxdebug->leave_sub();
1367 sub delete_invoice {
1368 $main::lxdebug->enter_sub();
1370 my ($self, $myconfig, $form, $spool) = @_;
1372 # connect to database
1373 my $dbh = $form->dbconnect_noauto($myconfig);
1375 &reverse_invoice($dbh, $form);
1377 my @values = (conv_i($form->{id}));
1380 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1382 # delete spool files
1383 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1385 # delete status entries
1386 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1388 my $rc = $dbh->commit;
1392 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1395 $main::lxdebug->leave_sub();
1400 sub retrieve_invoice {
1401 $main::lxdebug->enter_sub();
1403 my ($self, $myconfig, $form) = @_;
1405 # connect to database
1406 my $dbh = $form->dbconnect_noauto($myconfig);
1408 my ($sth, $ref, $query);
1410 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1414 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1415 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1416 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1417 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1418 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1419 d.curr AS currencies
1423 $ref = selectfirst_hashref_query($form, $dbh, $query);
1424 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1427 my $id = conv_i($form->{id});
1430 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1434 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1435 a.orddate, a.quodate, a.globalproject_id,
1436 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1437 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1438 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1439 a.employee_id, a.salesman_id, a.payment_id,
1440 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1441 a.transaction_description,
1442 a.marge_total, a.marge_percent,
1443 e.name AS employee, a.donumber
1445 LEFT JOIN employee e ON (e.id = a.employee_id)
1447 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1448 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1451 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1454 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1455 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1457 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1459 foreach my $vc (qw(customer vendor)) {
1460 next if !$form->{"delivery_${vc}_id"};
1461 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1464 # get printed, emailed
1465 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1466 $sth = prepare_execute_query($form, $dbh, $query, $id);
1468 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1469 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1470 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1471 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1474 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1476 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1477 : $form->{invdate} ? $dbh->quote($form->{invdate})
1481 my $taxzone_id = $form->{taxzone_id} *= 1;
1482 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1484 # retrieve individual items
1487 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1488 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1489 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1492 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1493 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1494 i.price_factor_id, i.price_factor, i.marge_price_factor,
1495 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1496 pr.projectnumber, pg.partsgroup, prg.pricegroup
1499 LEFT JOIN parts p ON (i.parts_id = p.id)
1500 LEFT JOIN project pr ON (i.project_id = pr.id)
1501 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1502 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1504 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1505 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1506 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1508 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1510 $sth = prepare_execute_query($form, $dbh, $query, $id);
1512 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1513 # Retrieve custom variables.
1514 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1516 sub_module => 'invoice',
1517 trans_id => $ref->{invoice_id},
1519 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1520 delete $ref->{invoice_id};
1522 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1523 delete($ref->{"part_inventory_accno_id"});
1525 foreach my $type (qw(inventory income expense)) {
1526 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1527 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1528 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1532 # get tax rates and description
1533 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1535 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1536 LEFT JOIN chart c ON (c.id = t.chart_id)
1538 (SELECT tk.tax_id FROM taxkeys tk
1539 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1540 AND startdate <= date($transdate)
1541 ORDER BY startdate DESC LIMIT 1)
1543 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1544 $ref->{taxaccounts} = "";
1546 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1548 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1552 $ref->{taxaccounts} .= "$ptr->{accno} ";
1554 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1555 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1556 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1557 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1558 $form->{taxaccounts} .= "$ptr->{accno} ";
1563 if ($form->{lizenzen}) {
1564 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1565 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1566 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1569 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1571 chop $ref->{taxaccounts};
1572 push @{ $form->{invoice_details} }, $ref;
1577 Common::webdav_folder($form) if ($main::webdav);
1580 my $rc = $dbh->commit;
1583 $main::lxdebug->leave_sub();
1589 $main::lxdebug->enter_sub();
1591 my ($self, $myconfig, $form) = @_;
1593 # connect to database
1594 my $dbh = $form->dbconnect($myconfig);
1596 my $dateformat = $myconfig->{dateformat};
1597 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1599 my (@values, $duedate, $ref, $query);
1601 if ($form->{invdate}) {
1602 $duedate = "to_date(?, '$dateformat')";
1603 push @values, $form->{invdate};
1605 $duedate = "current_date";
1608 my $cid = conv_i($form->{customer_id});
1611 if ($form->{payment_id}) {
1612 $payment_id = "(pt.id = ?) OR";
1613 push @values, conv_i($form->{payment_id});
1619 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1620 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1621 c.street, c.zipcode, c.city, c.country,
1622 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1623 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1624 b.discount AS tradediscount, b.description AS business
1626 LEFT JOIN business b ON (b.id = c.business_id)
1627 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1630 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1632 delete $ref->{salesman_id} if !$ref->{salesman_id};
1634 map { $form->{$_} = $ref->{$_} } keys %$ref;
1637 qq|SELECT sum(amount - paid) AS dunning_amount
1639 WHERE (paid < amount)
1640 AND (customer_id = ?)
1641 AND (dunning_config_id IS NOT NULL)|;
1642 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1643 map { $form->{$_} = $ref->{$_} } keys %$ref;
1646 qq|SELECT dnn.dunning_description AS max_dunning_level
1647 FROM dunning_config dnn
1648 WHERE id IN (SELECT dunning_config_id
1650 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1651 ORDER BY dunning_level DESC LIMIT 1|;
1652 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1653 map { $form->{$_} = $ref->{$_} } keys %$ref;
1655 $form->{creditremaining} = $form->{creditlimit};
1656 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1657 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1658 $form->{creditremaining} -= $value;
1662 (SELECT e.buy FROM exchangerate e
1663 WHERE e.curr = o.curr
1664 AND e.transdate = o.transdate)
1666 WHERE o.customer_id = ?
1667 AND o.quotation = '0'
1668 AND o.closed = '0'|;
1669 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1671 while (my ($amount, $exch) = $sth->fetchrow_array) {
1672 $exch = 1 unless $exch;
1673 $form->{creditremaining} -= $amount * $exch;
1677 # get shipto if we did not converted an order or invoice
1678 if (!$form->{shipto}) {
1679 map { delete $form->{$_} }
1680 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1681 shiptostreet shiptozipcode shiptocity shiptocountry
1682 shiptocontact shiptophone shiptofax shiptoemail);
1684 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1685 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1687 map { $form->{$_} = $ref->{$_} } keys %$ref;
1690 # setup last accounts used for this customer
1691 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1693 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1695 JOIN acc_trans ac ON (ac.chart_id = c.id)
1696 JOIN ar a ON (a.id = ac.trans_id)
1697 WHERE a.customer_id = ?
1698 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1699 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1700 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1703 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1704 if ($ref->{category} eq 'I') {
1706 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1708 if ($form->{initial_transdate}) {
1710 qq|SELECT tk.tax_id, t.rate
1712 LEFT JOIN tax t ON tk.tax_id = t.id
1713 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1714 ORDER BY tk.startdate DESC
1716 my ($tax_id, $rate) =
1717 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1718 $form->{initial_transdate});
1719 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1722 if ($ref->{category} eq 'A') {
1723 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1727 $form->{rowcount} = $i if ($i && !$form->{type});
1732 $main::lxdebug->leave_sub();
1736 $main::lxdebug->enter_sub();
1738 my ($self, $myconfig, $form) = @_;
1740 # connect to database
1741 my $dbh = $form->dbconnect($myconfig);
1743 my $i = $form->{rowcount};
1745 my $where = qq|NOT p.obsolete = '1'|;
1748 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1749 my ($table, $field) = split m/\./, $column;
1750 next if !$form->{"${field}_${i}"};
1751 $where .= qq| AND lower(${column}) ILIKE ?|;
1752 push @values, '%' . $form->{"${field}_${i}"} . '%';
1755 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1756 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1757 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1758 push @values, $form->{"partnumber_$i"};
1761 if ($form->{"description_$i"}) {
1762 $where .= qq| ORDER BY p.description|;
1764 $where .= qq| ORDER BY p.partnumber|;
1768 if ($form->{type} eq "invoice") {
1770 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1771 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1775 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1779 my $taxzone_id = $form->{taxzone_id} * 1;
1780 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1784 p.id, p.partnumber, p.description, p.sellprice,
1785 p.listprice, p.inventory_accno_id, p.lastcost,
1787 c1.accno AS inventory_accno,
1788 c1.new_chart_id AS inventory_new_chart,
1789 date($transdate) - c1.valid_from AS inventory_valid,
1791 c2.accno AS income_accno,
1792 c2.new_chart_id AS income_new_chart,
1793 date($transdate) - c2.valid_from AS income_valid,
1795 c3.accno AS expense_accno,
1796 c3.new_chart_id AS expense_new_chart,
1797 date($transdate) - c3.valid_from AS expense_valid,
1799 p.unit, p.assembly, p.bin, p.onhand,
1800 p.notes AS partnotes, p.notes AS longdescription,
1801 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1804 pfac.factor AS price_factor,
1809 LEFT JOIN chart c1 ON
1810 ((SELECT inventory_accno_id
1811 FROM buchungsgruppen
1812 WHERE id = p.buchungsgruppen_id) = c1.id)
1813 LEFT JOIN chart c2 ON
1814 ((SELECT income_accno_id_${taxzone_id}
1815 FROM buchungsgruppen
1816 WHERE id = p.buchungsgruppen_id) = c2.id)
1817 LEFT JOIN chart c3 ON
1818 ((SELECT expense_accno_id_${taxzone_id}
1819 FROM buchungsgruppen
1820 WHERE id = p.buchungsgruppen_id) = c3.id)
1821 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1822 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1824 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1826 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1828 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1829 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1830 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1831 if (!$ref->{inventory_accno_id}) {
1832 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1834 delete($ref->{inventory_accno_id});
1836 foreach my $type (qw(inventory income expense)) {
1837 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1839 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1842 ($ref->{"${type}_accno"},
1843 $ref->{"${type}_new_chart"},
1844 $ref->{"${type}_valid"})
1845 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1849 if ($form->{payment_id} eq "") {
1850 $form->{payment_id} = $form->{part_payment_id};
1853 # get tax rates and description
1854 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1856 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1858 LEFT JOIN chart c ON (c.id = t.chart_id)
1862 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1864 ORDER BY startdate DESC
1867 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1868 my $stw = $dbh->prepare($query);
1869 $stw->execute(@values) || $form->dberror($query);
1871 $ref->{taxaccounts} = "";
1873 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1875 # if ($customertax{$ref->{accno}})
1876 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1880 $ref->{taxaccounts} .= "$ptr->{accno} ";
1882 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1883 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1884 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1885 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1886 $form->{taxaccounts} .= "$ptr->{accno} ";
1892 chop $ref->{taxaccounts};
1893 if ($form->{language_id}) {
1895 qq|SELECT tr.translation, tr.longdescription
1897 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1898 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1899 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1900 if ($translation ne "") {
1901 $ref->{description} = $translation;
1902 $ref->{longdescription} = $longdescription;
1906 qq|SELECT tr.translation, tr.longdescription
1908 WHERE tr.language_id IN
1911 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1914 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1915 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1916 if ($translation ne "") {
1917 $ref->{description} = $translation;
1918 $ref->{longdescription} = $longdescription;
1923 $ref->{onhand} *= 1;
1925 push @{ $form->{item_list} }, $ref;
1927 if ($form->{lizenzen}) {
1928 if ($ref->{inventory_accno} > 0) {
1932 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1933 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1934 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1935 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1943 foreach my $item (@{ $form->{item_list} }) {
1944 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1945 trans_id => $item->{id},
1949 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1954 $main::lxdebug->leave_sub();
1957 ##########################
1958 # get pricegroups from database
1959 # build up selected pricegroup
1960 # if an exchange rate - change price
1963 sub get_pricegroups_for_parts {
1965 $main::lxdebug->enter_sub();
1967 my ($self, $myconfig, $form) = @_;
1969 my $dbh = $form->dbconnect($myconfig);
1971 $form->{"PRICES"} = {};
1975 my $all_units = AM->retrieve_units($myconfig, $form);
1976 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1977 $form->{"PRICES"}{$i} = [];
1979 $id = $form->{"id_$i"};
1981 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1982 $id = $form->{"new_id_$i"};
1985 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1987 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1988 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1989 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1991 my $price_new = $form->{"price_new_$i"};
1992 my $price_old = $form->{"price_old_$i"};
1994 if (!$form->{"unit_old_$i"}) {
1995 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1996 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1997 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1998 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2001 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2002 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2003 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2005 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2006 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2007 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2008 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2009 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2010 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2011 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2016 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2017 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2018 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2019 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2020 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2024 if (!$form->{"basefactor_$i"}) {
2025 $form->{"basefactor_$i"} = 1;
2031 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2032 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2042 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2044 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2045 'selected' AS selected
2048 ORDER BY pricegroup|;
2049 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2050 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2052 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2054 $pkr->{selected} = '';
2056 # if there is an exchange rate change price
2057 if (($form->{exchangerate} * 1) != 0) {
2058 $pkr->{price} /= $form->{exchangerate};
2061 $pkr->{price} *= $form->{"basefactor_$i"};
2062 $pkr->{price} *= $basefactor;
2063 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2065 if ($selectedpricegroup_id eq undef) {
2066 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2068 $pkr->{selected} = ' selected';
2070 # no customer pricesgroup set
2071 if ($pkr->{price} == $pkr->{default_sellprice}) {
2073 $pkr->{price} = $form->{"sellprice_$i"};
2077 # this sub should not set anything and only return. --sschoeling, 20090506
2078 # $form->{"sellprice_$i"} = $pkr->{price};
2081 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2082 $pkr->{price} = $form->{"sellprice_$i"};
2083 $pkr->{selected} = ' selected';
2087 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2088 if ($selectedpricegroup_id ne $pricegroup_old) {
2089 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2090 $pkr->{selected} = ' selected';
2092 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2093 if ($pkr->{pricegroup_id} == 0) {
2094 $pkr->{price} = $form->{"sellprice_$i"};
2095 $pkr->{selected} = ' selected';
2097 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2098 $pkr->{selected} = ' selected';
2099 if ( ($pkr->{pricegroup_id} == 0)
2100 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2101 # $pkr->{price} = $form->{"sellprice_$i"};
2103 $pkr->{price} = $form->{"sellprice_$i"};
2107 push @{ $form->{PRICES}{$i} }, $pkr;
2110 $form->{"basefactor_$i"} *= $basefactor;
2119 $main::lxdebug->leave_sub();
2123 $main::lxdebug->enter_sub();
2125 my ($self, $myconfig, $form, $table) = @_;
2127 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2129 # make sure there's no funny stuff in $table
2130 # ToDO: die when this happens and throw an error
2131 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2133 my $dbh = $form->dbconnect($myconfig);
2135 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2136 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2140 $main::lxdebug->leave_sub();
2146 $main::lxdebug->enter_sub();
2148 my ($self, $myconfig, $form, $table, $id) = @_;
2150 $main::lxdebug->leave_sub() and return 0 unless ($id);
2152 # make sure there's no funny stuff in $table
2153 # ToDO: die when this happens and throw an error
2154 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2156 my $dbh = $form->dbconnect($myconfig);
2158 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2159 my ($result) = selectrow_query($form, $dbh, $query, $id);
2163 $main::lxdebug->leave_sub();