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 #======================================================================
39 use List::Util qw(max);
47 use SL::GenericTranslations;
53 $main::lxdebug->enter_sub();
55 my ($self, $myconfig, $form, $locale) = @_;
57 $form->{duedate} ||= $form->{invdate};
60 my $dbh = $form->dbconnect($myconfig);
63 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
64 ($form->{terms}) = selectrow_query($form, $dbh, $query);
66 my (@project_ids, %projectnumbers);
67 $form->{TEMPLATE_ARRAYS} = {};
69 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
71 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
74 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
75 $price_factors{$pfac->{id}} = $pfac;
77 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
80 # sort items by partsgroup
81 for my $i (1 .. $form->{rowcount}) {
83 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
84 # $partsgroup = $form->{"partsgroup_$i"};
86 # push @partsgroup, [$i, $partsgroup];
87 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
91 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
92 join(", ", map({ "?" } @project_ids)) . ")";
93 $sth = $dbh->prepare($query);
94 $sth->execute(@project_ids) ||
95 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
96 while (my $ref = $sth->fetchrow_hashref()) {
97 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $form->{"globalprojectnumber"} =
103 $projectnumbers{$form->{"globalproject_id"}};
110 my %oid = ('Pg' => 'oid',
111 'Oracle' => 'rowid');
113 # sort items by partsgroup
114 for $i (1 .. $form->{rowcount}) {
116 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
117 $partsgroup = $form->{"partsgroup_$i"};
119 push @partsgroup, [$i, $partsgroup];
132 my $nodiscount_subtotal = 0;
133 my $discount_subtotal = 0;
135 my $subtotal_header = 0;
138 $form->{discount} = [];
140 IC->prepare_parts_for_printing();
143 qw(runningnumber number description longdescription qty ship unit bin
144 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
145 partnotes serialnumber reqdate sellprice listprice netprice
146 discount p_discount discount_sub nodiscount_sub
147 linetotal nodiscount_linetotal tax_rate projectnumber
148 price_factor price_factor_name partsgroup);
150 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
152 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
154 map { $form->{TEMPLATE_ARRAYS}{$_} => [] } (@arrays, @tax_arrays, @payment_arrays);
156 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
159 if ($item->[1] ne $sameitem) {
160 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
161 $sameitem = $item->[1];
163 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
166 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
168 if ($form->{"id_$i"} != 0) {
170 # add number, description and qty to $form->{number},
171 if ($form->{"subtotal_$i"} && !$subtotal_header) {
172 $subtotal_header = $i;
173 $position = int($position);
176 } elsif ($subtotal_header) {
178 $position = int($position);
179 $position = $position.".".$subposition;
181 $position = int($position);
185 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
187 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
188 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
189 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
190 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
191 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
192 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
193 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
194 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
195 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
196 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"deliverydate_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
203 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
204 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
207 if ($form->{lizenzen}) {
208 if ($form->{"licensenumber_$i"}) {
209 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
210 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
211 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
212 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
215 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
216 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
221 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
223 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
224 my ($dec) = ($sellprice =~ /\.(\d+)/);
225 my $decimalplaces = max 2, length($dec);
227 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
228 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
229 my $linetotal = $form->round_amount($linetotal_exact, 2);
230 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
232 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
233 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
235 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
237 $linetotal = ($linetotal != 0) ? $linetotal : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
242 $form->{total} += $linetotal;
243 $form->{nodiscount_total} += $nodiscount_linetotal;
244 $form->{discount_total} += $discount;
246 if ($subtotal_header) {
247 $discount_subtotal += $linetotal;
248 $nodiscount_subtotal += $nodiscount_linetotal;
251 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
255 $discount_subtotal = 0;
256 $nodiscount_subtotal = 0;
257 $subtotal_header = 0;
260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
261 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
264 if (!$form->{"discount_$i"}) {
265 $nodiscount += $linetotal;
268 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
269 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
271 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
273 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
277 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
279 if ($form->{taxincluded}) {
282 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
283 $taxbase = $linetotal - $taxamount;
285 $taxamount = $linetotal * $taxrate;
286 $taxbase = $linetotal;
289 if ($form->round_amount($taxrate, 7) == 0) {
290 if ($form->{taxincluded}) {
291 foreach my $accno (@taxaccounts) {
292 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
294 $taxaccounts{$accno} += $taxamount;
295 $taxdiff += $taxamount;
297 $taxbase{$accno} += $taxbase;
299 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
301 foreach my $accno (@taxaccounts) {
302 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
303 $taxbase{$accno} += $taxbase;
307 foreach my $accno (@taxaccounts) {
308 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
309 $taxbase{$accno} += $taxbase;
312 my $tax_rate = $taxrate * 100;
313 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
314 if ($form->{"assembly_$i"}) {
317 # get parts and push them onto the stack
319 if ($form->{groupitems}) {
321 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
323 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
327 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
329 JOIN parts p ON (a.parts_id = p.id)
330 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
331 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
332 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
334 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
335 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
336 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
337 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
338 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
341 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
343 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
344 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
346 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
347 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
355 foreach my $item (sort keys %taxaccounts) {
356 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
358 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
359 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
360 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
361 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
362 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
365 for my $i (1 .. $form->{paidaccounts}) {
366 if ($form->{"paid_$i"}) {
367 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
369 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
370 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
371 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
372 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
373 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
375 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
378 if($form->{taxincluded}) {
379 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
382 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
385 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
386 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
387 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
388 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
390 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
391 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
393 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
394 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
396 $form->set_payment_options($myconfig, $form->{invdate});
398 $form->{username} = $myconfig->{name};
402 $main::lxdebug->leave_sub();
405 sub project_description {
406 $main::lxdebug->enter_sub();
408 my ($self, $dbh, $id) = @_;
409 my $form = \%main::form;
411 my $query = qq|SELECT description FROM project WHERE id = ?|;
412 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
414 $main::lxdebug->leave_sub();
419 sub customer_details {
420 $main::lxdebug->enter_sub();
422 my ($self, $myconfig, $form, @wanted_vars) = @_;
424 # connect to database
425 my $dbh = $form->dbconnect($myconfig);
427 my $language_id = $form->{language_id};
429 # get contact id, set it if nessessary
432 my @values = (conv_i($form->{customer_id}));
435 if ($form->{cp_id}) {
436 $where = qq| AND (cp.cp_id = ?) |;
437 push(@values, conv_i($form->{cp_id}));
440 # get rest for the customer
442 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
443 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
445 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
446 WHERE (ct.id = ?) $where
449 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
451 # remove id and taxincluded before copy back
452 delete @$ref{qw(id taxincluded)};
454 @wanted_vars = grep({ $_ } @wanted_vars);
455 if (scalar(@wanted_vars) > 0) {
457 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
458 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
461 map { $form->{$_} = $ref->{$_} } keys %$ref;
463 if ($form->{delivery_customer_id}) {
465 qq|SELECT *, notes as customernotes
469 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
471 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
474 if ($form->{delivery_vendor_id}) {
476 qq|SELECT *, notes as customernotes
480 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
482 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
485 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
487 'trans_id' => $form->{customer_id});
488 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
490 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
491 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
492 'language_id' => $language_id,
493 'allow_fallback' => 1);
498 $main::lxdebug->leave_sub();
502 $main::lxdebug->enter_sub();
504 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
506 # connect to database, turn off autocommit
507 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
509 my ($query, $sth, $null, $project_id, @values);
510 my $exchangerate = 0;
512 if (!$form->{employee_id}) {
513 $form->get_employee($dbh);
516 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
518 ($null, $form->{department_id}) = split(/--/, $form->{department});
520 my $all_units = AM->retrieve_units($myconfig, $form);
522 if (!$payments_only) {
524 &reverse_invoice($dbh, $form);
527 $query = qq|SELECT nextval('glid')|;
528 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
530 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
531 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
533 if (!$form->{invnumber}) {
535 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
536 "cnnumber" : "invnumber", $dbh);
541 my ($netamount, $invoicediff) = (0, 0);
542 my ($amount, $linetotal, $lastincomeaccno);
544 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
545 my $defaultcurrency = (split m/:/, $currencies)[0];
547 if ($form->{currency} eq $defaultcurrency) {
548 $form->{exchangerate} = 1;
550 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
553 $form->{exchangerate} =
556 : $form->parse_amount($myconfig, $form->{exchangerate});
558 $form->{expense_inventory} = "";
562 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
563 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
566 $form->{amount} = {};
567 $form->{amount_cogs} = {};
569 foreach my $i (1 .. $form->{rowcount}) {
570 if ($form->{type} eq "credit_note") {
571 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
572 $form->{shipped} = 1;
574 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
579 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
580 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
581 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
583 if ($form->{storno}) {
584 $form->{"qty_$i"} *= -1;
587 if ($form->{"id_$i"}) {
590 if (defined($baseunits{$form->{"id_$i"}})) {
591 $item_unit = $baseunits{$form->{"id_$i"}};
594 $query = qq|SELECT unit FROM parts WHERE id = ?|;
595 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
596 $baseunits{$form->{"id_$i"}} = $item_unit;
599 if (defined($all_units->{$item_unit}->{factor})
600 && ($all_units->{$item_unit}->{factor} ne '')
601 && ($all_units->{$item_unit}->{factor} != 0)) {
602 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
606 $baseqty = $form->{"qty_$i"} * $basefactor;
608 my ($allocated, $taxrate) = (0, 0);
612 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
614 # keep entered selling price
616 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
618 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
620 my $decimalplaces = ($dec > 2) ? $dec : 2;
622 # undo discount formatting
623 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
626 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
628 # round linetotal to 2 decimal places
629 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
630 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
632 if ($form->{taxincluded}) {
633 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
634 $form->{"sellprice_$i"} =
635 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
637 $taxamount = $linetotal * $taxrate;
640 $netamount += $linetotal;
642 if ($taxamount != 0) {
644 $form->{amount}{ $form->{id} }{$_} +=
645 $taxamount * $form->{"${_}_rate"} / $taxrate
646 } split(/ /, $form->{"taxaccounts_$i"});
649 # add amount to income, $form->{amount}{trans_id}{accno}
650 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
652 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
653 $linetotal = $form->round_amount($linetotal, 2);
655 # this is the difference from the inventory
656 $invoicediff += ($amount - $linetotal);
658 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
661 $lastincomeaccno = $form->{"income_accno_$i"};
663 # adjust and round sellprice
664 $form->{"sellprice_$i"} =
665 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
668 next if $payments_only;
670 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
672 if ($form->{"assembly_$i"}) {
673 # record assembly item as allocated
674 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
677 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
681 # get pricegroup_id and save it
682 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
685 # save detail record in invoice table
687 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
688 sellprice, fxsellprice, discount, allocated, assemblyitem,
689 unit, deliverydate, project_id, serialnumber, pricegroup_id,
690 ordnumber, transdate, cusordnumber, base_qty, subtotal,
691 marge_percent, marge_total, lastcost,
692 price_factor_id, price_factor, marge_price_factor)
693 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
694 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
696 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
697 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
698 $form->{"sellprice_$i"}, $fxsellprice,
699 $form->{"discount_$i"}, $allocated, 'f',
700 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
701 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
702 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
703 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
704 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
705 $form->{"lastcost_$i"},
706 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
707 conv_i($form->{"marge_price_factor_$i"}));
708 do_query($form, $dbh, $query, @values);
710 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
712 qq|INSERT INTO licenseinvoice (trans_id, license_id)
713 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
714 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
715 do_query($form, $dbh, $query, @values);
720 $form->{datepaid} = $form->{invdate};
722 # total payments, don't move we need it here
723 for my $i (1 .. $form->{paidaccounts}) {
724 if ($form->{type} eq "credit_note") {
725 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
727 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
729 $form->{paid} += $form->{"paid_$i"};
730 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
733 my ($tax, $diff) = (0, 0);
735 $netamount = $form->round_amount($netamount, 2);
737 # figure out rounding errors for total amount vs netamount + taxes
738 if ($form->{taxincluded}) {
740 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
741 $diff += $amount - $netamount * $form->{exchangerate};
742 $netamount = $amount;
744 foreach my $item (split(/ /, $form->{taxaccounts})) {
745 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
746 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
747 $tax += $form->{amount}{ $form->{id} }{$item};
748 $netamount -= $form->{amount}{ $form->{id} }{$item};
751 $invoicediff += $diff;
752 ######## this only applies to tax included
753 if ($lastincomeaccno) {
754 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
758 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
759 $diff = $amount - $netamount * $form->{exchangerate};
760 $netamount = $amount;
761 foreach my $item (split(/ /, $form->{taxaccounts})) {
762 $form->{amount}{ $form->{id} }{$item} =
763 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
766 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
769 $amount - $form->{amount}{ $form->{id} }{$item} *
770 $form->{exchangerate};
771 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
772 $tax += $form->{amount}{ $form->{id} }{$item};
776 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
778 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
781 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
783 # update exchangerate
784 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
785 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
786 $form->{exchangerate}, 0);
789 $project_id = conv_i($form->{"globalproject_id"});
791 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
792 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
793 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
795 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
797 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
799 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
800 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
801 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
802 do_query($form, $dbh, $query, @values);
803 $form->{amount_cogs}{$trans_id}{$accno} = 0;
807 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
808 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
810 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
812 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
813 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
814 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
815 do_query($form, $dbh, $query, @values);
820 foreach my $trans_id (keys %{ $form->{amount} }) {
821 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
822 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
824 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
826 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
828 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
829 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
830 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
831 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
832 do_query($form, $dbh, $query, @values);
833 $form->{amount}{$trans_id}{$accno} = 0;
837 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
838 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
840 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
842 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
843 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
844 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
845 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
846 do_query($form, $dbh, $query, @values);
851 # deduct payment differences from diff
852 for my $i (1 .. $form->{paidaccounts}) {
853 if ($form->{"paid_$i"} != 0) {
855 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
856 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
860 # record payments and offsetting AR
861 if (!$form->{storno}) {
862 for my $i (1 .. $form->{paidaccounts}) {
864 next if ($form->{"paid_$i"} == 0);
866 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
867 $form->{"datepaid_$i"} = $form->{invdate}
868 unless ($form->{"datepaid_$i"});
869 $form->{datepaid} = $form->{"datepaid_$i"};
873 if ($form->{currency} eq $defaultcurrency) {
874 $form->{"exchangerate_$i"} = 1;
876 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
877 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
881 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
883 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
885 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
886 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
887 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
888 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
889 do_query($form, $dbh, $query, @values);
893 $form->{"paid_$i"} *= -1;
896 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
897 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
898 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
899 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
900 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
901 do_query($form, $dbh, $query, @values);
903 # exchangerate difference
904 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
905 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
909 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
910 $form->{"exchangerate_$i"};
912 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
915 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
921 # update exchange rate
922 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
923 $form->update_exchangerate($dbh, $form->{currency},
924 $form->{"datepaid_$i"},
925 $form->{"exchangerate_$i"}, 0);
929 } else { # if (!$form->{storno})
930 $form->{marge_total} *= -1;
933 if ($payments_only) {
934 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
935 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
937 if (!$provided_dbh) {
942 $main::lxdebug->leave_sub();
946 # record exchange rate differences and gains/losses
947 foreach my $accno (keys %{ $form->{fx} }) {
948 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
950 ($form->{fx}{$accno}{$transdate} =
951 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
956 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
957 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
958 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
959 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
960 do_query($form, $dbh, $query, @values);
965 $amount = $netamount + $tax;
968 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
970 $query = qq|UPDATE ar set
971 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
972 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
973 amount = ?, netamount = ?, paid = ?, datepaid = ?,
974 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
975 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
976 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
977 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
978 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
979 cp_id = ?, marge_total = ?, marge_percent = ?,
980 globalproject_id = ?, delivery_customer_id = ?,
981 transaction_description = ?, delivery_vendor_id = ?,
984 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
985 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
986 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
987 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
988 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
989 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
990 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
991 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
992 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
993 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
994 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
995 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
996 conv_i($form->{"id"}));
997 do_query($form, $dbh, $query, @values);
999 if($form->{"formname"} eq "credit_note") {
1000 for my $i (1 .. $form->{rowcount}) {
1001 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
1002 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
1003 do_query($form, $dbh, $query, @values);
1007 if ($form->{storno}) {
1010 paid = paid + amount,
1012 intnotes = ? || intnotes
1014 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1015 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1019 $form->{name} = $form->{customer};
1020 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1022 if (!$form->{shipto_id}) {
1023 $form->add_shipto($dbh, $form->{id}, "AR");
1026 # save printed, emailed and queued
1027 $form->save_status($dbh);
1029 Common::webdav_folder($form) if ($main::webdav);
1031 # Link this record to the records it was created from.
1032 RecordLinks->create_links('dbh' => $dbh,
1034 'from_table' => 'oe',
1035 'from_ids' => $form->{convert_from_oe_ids},
1037 'to_id' => $form->{id},
1039 delete $form->{convert_from_oe_ids};
1041 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1043 if (scalar @convert_from_do_ids) {
1044 DO->close_orders('dbh' => $dbh,
1045 'ids' => \@convert_from_do_ids);
1047 RecordLinks->create_links('dbh' => $dbh,
1049 'from_table' => 'delivery_orders',
1050 'from_ids' => \@convert_from_do_ids,
1052 'to_id' => $form->{id},
1055 delete $form->{convert_from_do_ids};
1057 ARAP->close_orders_if_billed('dbh' => $dbh,
1058 'arap_id' => $form->{id},
1062 if (!$provided_dbh) {
1067 $main::lxdebug->leave_sub();
1072 sub _delete_payments {
1073 $main::lxdebug->enter_sub();
1075 my ($self, $form, $dbh) = @_;
1077 my @delete_acc_trans_ids;
1079 # Delete old payment entries from acc_trans.
1081 qq|SELECT acc_trans_id
1083 WHERE (trans_id = ?) AND fx_transaction
1087 SELECT at.acc_trans_id
1089 LEFT JOIN chart c ON (at.chart_id = c.id)
1090 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1091 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1094 qq|SELECT at.acc_trans_id
1096 LEFT JOIN chart c ON (at.chart_id = c.id)
1097 WHERE (trans_id = ?)
1098 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1099 ORDER BY at.acc_trans_id
1101 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1103 if (@delete_acc_trans_ids) {
1104 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1105 do_query($form, $dbh, $query);
1108 $main::lxdebug->leave_sub();
1112 $main::lxdebug->enter_sub();
1114 my ($self, $myconfig, $form, $locale) = @_;
1116 # connect to database, turn off autocommit
1117 my $dbh = $form->dbconnect_noauto($myconfig);
1119 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1121 $old_form = save_form();
1123 # Delete all entries in acc_trans from prior payments.
1124 $self->_delete_payments($form, $dbh);
1126 # Save the new payments the user made before cleaning up $form.
1127 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1129 # Clean up $form so that old content won't tamper the results.
1130 %keep_vars = map { $_, 1 } qw(login password id);
1131 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1133 # Retrieve the invoice from the database.
1134 $self->retrieve_invoice($myconfig, $form);
1136 # Set up the content of $form in the way that IS::post_invoice() expects.
1137 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1139 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1140 $item = $form->{invoice_details}->[$row - 1];
1142 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1144 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1147 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1149 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1151 # Restore the payment options from the user input.
1152 map { $form->{$_} = $payments{$_} } keys %payments;
1154 # Get the AR accno (which is normally done by Form::create_links()).
1158 LEFT JOIN chart c ON (at.chart_id = c.id)
1159 WHERE (trans_id = ?)
1160 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1161 ORDER BY at.acc_trans_id
1164 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1166 # Post the new payments.
1167 $self->post_invoice($myconfig, $form, $dbh, 1);
1169 restore_form($old_form);
1171 my $rc = $dbh->commit();
1174 $main::lxdebug->leave_sub();
1179 sub process_assembly {
1180 $main::lxdebug->enter_sub();
1182 my ($dbh, $form, $id, $totalqty) = @_;
1185 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1186 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1188 JOIN parts p ON (a.parts_id = p.id)
1190 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1192 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1196 $ref->{inventory_accno_id} *= 1;
1197 $ref->{expense_accno_id} *= 1;
1199 # multiply by number of assemblies
1200 $ref->{qty} *= $totalqty;
1202 if ($ref->{assembly}) {
1203 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1206 if ($ref->{inventory_accno_id}) {
1207 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1211 # save detail record for individual assembly item in invoice table
1213 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1214 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1215 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1216 do_query($form, $dbh, $query, @values);
1222 $main::lxdebug->leave_sub();
1226 $main::lxdebug->enter_sub();
1228 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1232 $form->{taxzone_id} *=1;
1233 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1234 my $taxzone_id = $form->{"taxzone_id"} * 1;
1236 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1237 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1238 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1239 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1240 FROM invoice i, parts p
1241 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1242 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1243 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1244 WHERE (i.parts_id = p.id)
1245 AND (i.parts_id = ?)
1246 AND ((i.base_qty + i.allocated) < 0)
1248 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1253 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1254 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1258 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1260 # total expenses and inventory
1261 # sellprice is the cost of the item
1262 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1265 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1267 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1268 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1269 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1271 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1272 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1278 last if (($totalqty -= $qty) <= 0);
1283 $main::lxdebug->leave_sub();
1288 sub reverse_invoice {
1289 $main::lxdebug->enter_sub();
1291 my ($dbh, $form) = @_;
1293 # reverse inventory items
1295 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1297 JOIN parts p ON (i.parts_id = p.id)
1298 WHERE i.trans_id = ?|;
1299 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1301 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1303 if ($ref->{inventory_accno_id}) {
1304 # de-allocated purchases
1306 qq|SELECT i.id, i.trans_id, i.allocated
1308 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1309 ORDER BY i.trans_id DESC|;
1310 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1312 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1313 my $qty = $ref->{qty};
1314 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1315 $qty = $inhref->{allocated};
1319 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1321 last if (($ref->{qty} -= $qty) <= 0);
1330 my @values = (conv_i($form->{id}));
1331 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1332 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1334 if ($form->{lizenzen}) {
1336 qq|DELETE FROM licenseinvoice
1337 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1338 do_query($form, $dbh, $query, @values);
1341 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1343 $main::lxdebug->leave_sub();
1346 sub delete_invoice {
1347 $main::lxdebug->enter_sub();
1349 my ($self, $myconfig, $form, $spool) = @_;
1351 # connect to database
1352 my $dbh = $form->dbconnect_noauto($myconfig);
1354 &reverse_invoice($dbh, $form);
1356 my @values = (conv_i($form->{id}));
1359 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1361 # delete spool files
1362 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1364 # delete status entries
1365 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1367 my $rc = $dbh->commit;
1371 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1374 $main::lxdebug->leave_sub();
1379 sub retrieve_invoice {
1380 $main::lxdebug->enter_sub();
1382 my ($self, $myconfig, $form) = @_;
1384 # connect to database
1385 my $dbh = $form->dbconnect_noauto($myconfig);
1387 my ($sth, $ref, $query);
1389 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1393 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1394 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1395 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1396 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1397 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1398 d.curr AS currencies
1402 $ref = selectfirst_hashref_query($form, $dbh, $query);
1403 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1406 my $id = conv_i($form->{id});
1409 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1413 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1414 a.orddate, a.quodate, a.globalproject_id,
1415 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1416 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1417 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1418 a.employee_id, a.salesman_id, a.payment_id,
1419 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1420 a.transaction_description,
1421 a.marge_total, a.marge_percent,
1422 e.name AS employee, a.donumber
1424 LEFT JOIN employee e ON (e.id = a.employee_id)
1426 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1427 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1430 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1433 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1434 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1436 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1438 foreach my $vc (qw(customer vendor)) {
1439 next if !$form->{"delivery_${vc}_id"};
1440 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1443 # get printed, emailed
1444 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1445 $sth = prepare_execute_query($form, $dbh, $query, $id);
1447 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1448 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1449 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1450 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1453 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1455 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1456 : $form->{invdate} ? $dbh->quote($form->{invdate})
1460 my $taxzone_id = $form->{taxzone_id} *= 1;
1461 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1463 # retrieve individual items
1466 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1467 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1468 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1470 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1471 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1472 i.price_factor_id, i.price_factor, i.marge_price_factor,
1473 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1474 pr.projectnumber, pg.partsgroup, prg.pricegroup
1477 LEFT JOIN parts p ON (i.parts_id = p.id)
1478 LEFT JOIN project pr ON (i.project_id = pr.id)
1479 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1480 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1482 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1483 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1484 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1486 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1488 $sth = prepare_execute_query($form, $dbh, $query, $id);
1490 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1491 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1492 delete($ref->{"part_inventory_accno_id"});
1494 foreach my $type (qw(inventory income expense)) {
1495 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1496 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1497 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1501 # get tax rates and description
1502 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1504 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1505 LEFT JOIN chart c ON (c.id = t.chart_id)
1507 (SELECT tk.tax_id FROM taxkeys tk
1508 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1509 AND startdate <= date($transdate)
1510 ORDER BY startdate DESC LIMIT 1)
1512 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1513 $ref->{taxaccounts} = "";
1515 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1517 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1521 $ref->{taxaccounts} .= "$ptr->{accno} ";
1523 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1524 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1525 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1526 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1527 $form->{taxaccounts} .= "$ptr->{accno} ";
1532 if ($form->{lizenzen}) {
1533 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1534 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1535 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1538 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1540 chop $ref->{taxaccounts};
1541 push @{ $form->{invoice_details} }, $ref;
1546 Common::webdav_folder($form) if ($main::webdav);
1549 my $rc = $dbh->commit;
1552 $main::lxdebug->leave_sub();
1558 $main::lxdebug->enter_sub();
1560 my ($self, $myconfig, $form) = @_;
1562 # connect to database
1563 my $dbh = $form->dbconnect($myconfig);
1565 my $dateformat = $myconfig->{dateformat};
1566 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1568 my (@values, $duedate, $ref, $query);
1570 if ($form->{invdate}) {
1571 $duedate = "to_date(?, '$dateformat')";
1572 push @values, $form->{invdate};
1574 $duedate = "current_date";
1577 my $cid = conv_i($form->{customer_id});
1580 if ($form->{payment_id}) {
1581 $payment_id = "(pt.id = ?) OR";
1582 push @values, conv_i($form->{payment_id});
1588 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1589 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1590 c.street, c.zipcode, c.city, c.country,
1591 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1592 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1593 b.discount AS tradediscount, b.description AS business
1595 LEFT JOIN business b ON (b.id = c.business_id)
1596 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1599 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1601 delete $ref->{salesman_id} if !$ref->{salesman_id};
1603 map { $form->{$_} = $ref->{$_} } keys %$ref;
1606 qq|SELECT sum(amount - paid) AS dunning_amount
1608 WHERE (paid < amount)
1609 AND (customer_id = ?)
1610 AND (dunning_config_id IS NOT NULL)|;
1611 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1612 map { $form->{$_} = $ref->{$_} } keys %$ref;
1615 qq|SELECT dnn.dunning_description AS max_dunning_level
1616 FROM dunning_config dnn
1617 WHERE id IN (SELECT dunning_config_id
1619 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1620 ORDER BY dunning_level DESC LIMIT 1|;
1621 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1622 map { $form->{$_} = $ref->{$_} } keys %$ref;
1624 $form->{creditremaining} = $form->{creditlimit};
1625 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1626 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1627 $form->{creditremaining} -= $value;
1631 (SELECT e.buy FROM exchangerate e
1632 WHERE e.curr = o.curr
1633 AND e.transdate = o.transdate)
1635 WHERE o.customer_id = ?
1636 AND o.quotation = '0'
1637 AND o.closed = '0'|;
1638 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1640 while (my ($amount, $exch) = $sth->fetchrow_array) {
1641 $exch = 1 unless $exch;
1642 $form->{creditremaining} -= $amount * $exch;
1646 # get shipto if we did not converted an order or invoice
1647 if (!$form->{shipto}) {
1648 map { delete $form->{$_} }
1649 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1650 shiptostreet shiptozipcode shiptocity shiptocountry
1651 shiptocontact shiptophone shiptofax shiptoemail);
1653 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1654 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1656 map { $form->{$_} = $ref->{$_} } keys %$ref;
1659 # setup last accounts used for this customer
1660 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1662 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1664 JOIN acc_trans ac ON (ac.chart_id = c.id)
1665 JOIN ar a ON (a.id = ac.trans_id)
1666 WHERE a.customer_id = ?
1667 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1668 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1669 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1672 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1673 if ($ref->{category} eq 'I') {
1675 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1677 if ($form->{initial_transdate}) {
1679 qq|SELECT tk.tax_id, t.rate
1681 LEFT JOIN tax t ON tk.tax_id = t.id
1682 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1683 ORDER BY tk.startdate DESC
1685 my ($tax_id, $rate) =
1686 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1687 $form->{initial_transdate});
1688 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1691 if ($ref->{category} eq 'A') {
1692 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1696 $form->{rowcount} = $i if ($i && !$form->{type});
1701 $main::lxdebug->leave_sub();
1705 $main::lxdebug->enter_sub();
1707 my ($self, $myconfig, $form) = @_;
1709 # connect to database
1710 my $dbh = $form->dbconnect($myconfig);
1712 my $i = $form->{rowcount};
1714 my $where = qq|NOT p.obsolete = '1'|;
1717 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1718 my ($table, $field) = split m/\./, $column;
1719 next if !$form->{"${field}_${i}"};
1720 $where .= qq| AND lower(${column}) ILIKE ?|;
1721 push @values, '%' . $form->{"${field}_${i}"} . '%';
1724 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1725 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1726 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1727 push @values, $form->{"partnumber_$i"};
1730 if ($form->{"description_$i"}) {
1731 $where .= qq| ORDER BY p.description|;
1733 $where .= qq| ORDER BY p.partnumber|;
1737 if ($form->{type} eq "invoice") {
1739 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1740 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1744 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1748 my $taxzone_id = $form->{taxzone_id} * 1;
1749 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1753 p.id, p.partnumber, p.description, p.sellprice,
1754 p.listprice, p.inventory_accno_id, p.lastcost,
1756 c1.accno AS inventory_accno,
1757 c1.new_chart_id AS inventory_new_chart,
1758 date($transdate) - c1.valid_from AS inventory_valid,
1760 c2.accno AS income_accno,
1761 c2.new_chart_id AS income_new_chart,
1762 date($transdate) - c2.valid_from AS income_valid,
1764 c3.accno AS expense_accno,
1765 c3.new_chart_id AS expense_new_chart,
1766 date($transdate) - c3.valid_from AS expense_valid,
1768 p.unit, p.assembly, p.bin, p.onhand,
1769 p.notes AS partnotes, p.notes AS longdescription,
1770 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1773 pfac.factor AS price_factor,
1778 LEFT JOIN chart c1 ON
1779 ((SELECT inventory_accno_id
1780 FROM buchungsgruppen
1781 WHERE id = p.buchungsgruppen_id) = c1.id)
1782 LEFT JOIN chart c2 ON
1783 ((SELECT income_accno_id_${taxzone_id}
1784 FROM buchungsgruppen
1785 WHERE id = p.buchungsgruppen_id) = c2.id)
1786 LEFT JOIN chart c3 ON
1787 ((SELECT expense_accno_id_${taxzone_id}
1788 FROM buchungsgruppen
1789 WHERE id = p.buchungsgruppen_id) = c3.id)
1790 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1791 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1793 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1795 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1797 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1798 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1799 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1800 if (!$ref->{inventory_accno_id}) {
1801 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1803 delete($ref->{inventory_accno_id});
1805 foreach my $type (qw(inventory income expense)) {
1806 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1808 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1811 ($ref->{"${type}_accno"},
1812 $ref->{"${type}_new_chart"},
1813 $ref->{"${type}_valid"})
1814 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1818 if ($form->{payment_id} eq "") {
1819 $form->{payment_id} = $form->{part_payment_id};
1822 # get tax rates and description
1823 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1825 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1827 LEFT JOIN chart c ON (c.id = t.chart_id)
1831 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1833 ORDER BY startdate DESC
1836 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1837 my $stw = $dbh->prepare($query);
1838 $stw->execute(@values) || $form->dberror($query);
1840 $ref->{taxaccounts} = "";
1842 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1844 # if ($customertax{$ref->{accno}})
1845 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1849 $ref->{taxaccounts} .= "$ptr->{accno} ";
1851 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1852 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1853 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1854 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1855 $form->{taxaccounts} .= "$ptr->{accno} ";
1861 chop $ref->{taxaccounts};
1862 if ($form->{language_id}) {
1864 qq|SELECT tr.translation, tr.longdescription
1866 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1867 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1868 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1869 if ($translation ne "") {
1870 $ref->{description} = $translation;
1871 $ref->{longdescription} = $longdescription;
1875 qq|SELECT tr.translation, tr.longdescription
1877 WHERE tr.language_id IN
1880 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1883 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1884 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1885 if ($translation ne "") {
1886 $ref->{description} = $translation;
1887 $ref->{longdescription} = $longdescription;
1892 $ref->{onhand} *= 1;
1894 push @{ $form->{item_list} }, $ref;
1896 if ($form->{lizenzen}) {
1897 if ($ref->{inventory_accno} > 0) {
1901 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1902 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1903 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1904 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1912 foreach my $item (@{ $form->{item_list} }) {
1913 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1914 trans_id => $item->{id},
1918 $main::lxdebug->dump(0, "cvar", $custom_variables);
1919 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1924 $main::lxdebug->leave_sub();
1927 ##########################
1928 # get pricegroups from database
1929 # build up selected pricegroup
1930 # if an exchange rate - change price
1933 sub get_pricegroups_for_parts {
1935 $main::lxdebug->enter_sub();
1937 my ($self, $myconfig, $form) = @_;
1939 my $dbh = $form->dbconnect($myconfig);
1941 $form->{"PRICES"} = {};
1945 my $all_units = AM->retrieve_units($myconfig, $form);
1946 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1947 $form->{"PRICES"}{$i} = [];
1949 $id = $form->{"id_$i"};
1951 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1953 $id = $form->{"new_id_$i"};
1956 my ($price, $selectedpricegroup_id) = split(/--/,
1957 $form->{"sellprice_pg_$i"});
1959 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1960 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1961 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1963 my $price_new = $form->{"price_new_$i"};
1964 my $price_old = $form->{"price_old_$i"};
1966 if (!$form->{"unit_old_$i"}) {
1967 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1968 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1969 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1970 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1973 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1974 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1975 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1977 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1978 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1979 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1980 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1981 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1982 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1983 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1988 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1989 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1990 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1991 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1992 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1996 if (!$form->{"basefactor_$i"}) {
1997 $form->{"basefactor_$i"} = 1;
2003 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2004 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2014 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2016 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2017 'selected' AS selected
2020 ORDER BY pricegroup|;
2021 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2022 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2024 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2026 $pkr->{selected} = '';
2028 # if there is an exchange rate change price
2029 if (($form->{exchangerate} * 1) != 0) {
2031 $pkr->{price} /= $form->{exchangerate};
2034 $pkr->{price} *= $form->{"basefactor_$i"};
2036 $pkr->{price} *= $basefactor;
2038 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2040 if ($selectedpricegroup_id eq undef) {
2041 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2043 $pkr->{selected} = ' selected';
2045 # no customer pricesgroup set
2046 if ($pkr->{price} == $pkr->{default_sellprice}) {
2048 $pkr->{price} = $form->{"sellprice_$i"};
2052 # this sub should not set anything and only return. --sschoeling, 20090506
2053 # $form->{"sellprice_$i"} = $pkr->{price};
2056 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2057 $pkr->{price} = $form->{"sellprice_$i"};
2058 $pkr->{selected} = ' selected';
2062 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2063 if ($selectedpricegroup_id ne $pricegroup_old) {
2064 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2065 $pkr->{selected} = ' selected';
2067 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2068 if ($pkr->{pricegroup_id} == 0) {
2069 $pkr->{price} = $form->{"sellprice_$i"};
2070 $pkr->{selected} = ' selected';
2072 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2073 $pkr->{selected} = ' selected';
2074 if ( ($pkr->{pricegroup_id} == 0)
2075 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2076 # $pkr->{price} = $form->{"sellprice_$i"};
2078 $pkr->{price} = $form->{"sellprice_$i"};
2082 push @{ $form->{PRICES}{$i} }, $pkr;
2085 $form->{"basefactor_$i"} *= $basefactor;
2094 $main::lxdebug->leave_sub();
2098 $main::lxdebug->enter_sub();
2100 my ($self, $myconfig, $form, $table) = @_;
2102 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2104 # make sure there's no funny stuff in $table
2105 # ToDO: die when this happens and throw an error
2106 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2108 my $dbh = $form->dbconnect($myconfig);
2110 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2111 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2115 $main::lxdebug->leave_sub();
2121 $main::lxdebug->enter_sub();
2123 my ($self, $myconfig, $form, $table, $id) = @_;
2125 $main::lxdebug->leave_sub() and return 0 unless ($id);
2127 # make sure there's no funny stuff in $table
2128 # ToDO: die when this happens and throw an error
2129 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2131 my $dbh = $form->dbconnect($myconfig);
2133 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2134 my ($result) = selectrow_query($form, $dbh, $query, $id);
2138 $main::lxdebug->leave_sub();