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);
52 $main::lxdebug->enter_sub();
54 my ($self, $myconfig, $form, $locale) = @_;
56 $form->{duedate} ||= $form->{invdate};
59 my $dbh = $form->dbconnect($myconfig);
62 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
63 ($form->{terms}) = selectrow_query($form, $dbh, $query);
65 my (@project_ids, %projectnumbers);
67 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
69 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
72 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
73 $price_factors{$pfac->{id}} = $pfac;
75 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
78 # sort items by partsgroup
79 for my $i (1 .. $form->{rowcount}) {
81 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
82 # $partsgroup = $form->{"partsgroup_$i"};
84 # push @partsgroup, [$i, $partsgroup];
85 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
89 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
90 join(", ", map({ "?" } @project_ids)) . ")";
91 $sth = $dbh->prepare($query);
92 $sth->execute(@project_ids) ||
93 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
94 while (my $ref = $sth->fetchrow_hashref()) {
95 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $form->{"globalprojectnumber"} =
101 $projectnumbers{$form->{"globalproject_id"}};
108 my %oid = ('Pg' => 'oid',
109 'Oracle' => 'rowid');
111 # sort items by partsgroup
112 for $i (1 .. $form->{rowcount}) {
114 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
115 $partsgroup = $form->{"partsgroup_$i"};
117 push @partsgroup, [$i, $partsgroup];
130 my $nodiscount_subtotal = 0;
131 my $discount_subtotal = 0;
133 my $subtotal_header = 0;
136 $form->{discount} = [];
138 IC->prepare_parts_for_printing();
141 qw(runningnumber number description longdescription qty ship unit bin
142 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
143 partnotes serialnumber reqdate sellprice listprice netprice
144 discount p_discount discount_sub nodiscount_sub
145 linetotal nodiscount_linetotal tax_rate projectnumber
146 price_factor price_factor_name partsgroup);
148 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
150 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
152 $form->{TEMPLATE_ARRAYS} = { map { $_ => [] } (@arrays, @tax_arrays, @payment_arrays) };
154 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
157 if ($item->[1] ne $sameitem) {
158 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
159 $sameitem = $item->[1];
161 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
164 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
166 if ($form->{"id_$i"} != 0) {
168 # add number, description and qty to $form->{number},
169 if ($form->{"subtotal_$i"} && !$subtotal_header) {
170 $subtotal_header = $i;
171 $position = int($position);
174 } elsif ($subtotal_header) {
176 $position = int($position);
177 $position = $position.".".$subposition;
179 $position = int($position);
183 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
185 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
186 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
187 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
188 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
189 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
190 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
191 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
192 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
193 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
194 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"deliverydate_$i"};
195 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
196 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
197 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
201 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
205 if ($form->{lizenzen}) {
206 if ($form->{"licensenumber_$i"}) {
207 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
208 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
209 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
210 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
213 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
214 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
226 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
227 my $linetotal = $form->round_amount($linetotal_exact, 2);
228 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
230 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
231 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
233 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
235 $linetotal = ($linetotal != 0) ? $linetotal : '';
237 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
240 $form->{total} += $linetotal;
241 $form->{nodiscount_total} += $nodiscount_linetotal;
242 $form->{discount_total} += $discount;
244 if ($subtotal_header) {
245 $discount_subtotal += $linetotal;
246 $nodiscount_subtotal += $nodiscount_linetotal;
249 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
250 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
251 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
253 $discount_subtotal = 0;
254 $nodiscount_subtotal = 0;
255 $subtotal_header = 0;
258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
259 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
262 if (!$form->{"discount_$i"}) {
263 $nodiscount += $linetotal;
266 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
267 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
269 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
271 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
275 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
277 if ($form->{taxincluded}) {
280 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
281 $taxbase = $linetotal - $taxamount;
283 $taxamount = $linetotal * $taxrate;
284 $taxbase = $linetotal;
287 if ($form->round_amount($taxrate, 7) == 0) {
288 if ($form->{taxincluded}) {
289 foreach my $accno (@taxaccounts) {
290 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
292 $taxaccounts{$accno} += $taxamount;
293 $taxdiff += $taxamount;
295 $taxbase{$accno} += $taxbase;
297 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
299 foreach my $accno (@taxaccounts) {
300 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
301 $taxbase{$accno} += $taxbase;
305 foreach my $accno (@taxaccounts) {
306 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
307 $taxbase{$accno} += $taxbase;
310 my $tax_rate = $taxrate * 100;
311 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
312 if ($form->{"assembly_$i"}) {
315 # get parts and push them onto the stack
317 if ($form->{groupitems}) {
319 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
321 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
325 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
327 JOIN parts p ON (a.parts_id = p.id)
328 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
329 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
330 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
332 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
333 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
334 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
335 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
336 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
339 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
341 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
342 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
344 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
345 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
353 foreach my $item (sort keys %taxaccounts) {
354 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
356 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
357 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
358 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
359 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
360 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
363 for my $i (1 .. $form->{paidaccounts}) {
364 if ($form->{"paid_$i"}) {
365 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
367 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
368 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
369 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
370 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
371 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
373 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
376 if($form->{taxincluded}) {
377 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
380 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
383 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
384 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
385 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
386 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
388 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
389 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
391 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
392 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
394 $form->set_payment_options($myconfig, $form->{invdate});
396 $form->{username} = $myconfig->{name};
400 $main::lxdebug->leave_sub();
403 sub project_description {
404 $main::lxdebug->enter_sub();
406 my ($self, $dbh, $id) = @_;
407 my $form = \%main::form;
409 my $query = qq|SELECT description FROM project WHERE id = ?|;
410 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
412 $main::lxdebug->leave_sub();
417 sub customer_details {
418 $main::lxdebug->enter_sub();
420 my ($self, $myconfig, $form, @wanted_vars) = @_;
422 # connect to database
423 my $dbh = $form->dbconnect($myconfig);
425 # get contact id, set it if nessessary
428 my @values = (conv_i($form->{customer_id}));
431 if ($form->{cp_id}) {
432 $where = qq| AND (cp.cp_id = ?) |;
433 push(@values, conv_i($form->{cp_id}));
436 # get rest for the customer
438 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
439 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
441 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
442 WHERE (ct.id = ?) $where
445 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
447 # remove id and taxincluded before copy back
448 delete @$ref{qw(id taxincluded)};
450 @wanted_vars = grep({ $_ } @wanted_vars);
451 if (scalar(@wanted_vars) > 0) {
453 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
454 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
457 map { $form->{$_} = $ref->{$_} } keys %$ref;
459 if ($form->{delivery_customer_id}) {
461 qq|SELECT *, notes as customernotes
465 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
467 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
470 if ($form->{delivery_vendor_id}) {
472 qq|SELECT *, notes as customernotes
476 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
478 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
481 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
483 'trans_id' => $form->{customer_id});
484 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
488 $main::lxdebug->leave_sub();
492 $main::lxdebug->enter_sub();
494 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
496 # connect to database, turn off autocommit
497 my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
499 my ($query, $sth, $null, $project_id, @values);
500 my $exchangerate = 0;
502 if (!$form->{employee_id}) {
503 $form->get_employee($dbh);
506 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
508 ($null, $form->{department_id}) = split(/--/, $form->{department});
510 my $all_units = AM->retrieve_units($myconfig, $form);
512 if (!$payments_only) {
514 &reverse_invoice($dbh, $form);
517 $query = qq|SELECT nextval('glid')|;
518 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
520 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
521 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
523 if (!$form->{invnumber}) {
525 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
526 "cnnumber" : "invnumber", $dbh);
531 my ($netamount, $invoicediff) = (0, 0);
532 my ($amount, $linetotal, $lastincomeaccno);
534 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
535 my $defaultcurrency = (split m/:/, $currencies)[0];
537 if ($form->{currency} eq $defaultcurrency) {
538 $form->{exchangerate} = 1;
540 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
543 $form->{exchangerate} =
546 : $form->parse_amount($myconfig, $form->{exchangerate});
548 $form->{expense_inventory} = "";
552 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
553 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
556 $form->{amount} = {};
557 $form->{amount_cogs} = {};
559 foreach my $i (1 .. $form->{rowcount}) {
560 if ($form->{type} eq "credit_note") {
561 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
562 $form->{shipped} = 1;
564 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
569 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
570 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
571 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
573 if ($form->{storno}) {
574 $form->{"qty_$i"} *= -1;
577 if ($form->{"id_$i"}) {
580 if (defined($baseunits{$form->{"id_$i"}})) {
581 $item_unit = $baseunits{$form->{"id_$i"}};
584 $query = qq|SELECT unit FROM parts WHERE id = ?|;
585 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
586 $baseunits{$form->{"id_$i"}} = $item_unit;
589 if (defined($all_units->{$item_unit}->{factor})
590 && ($all_units->{$item_unit}->{factor} ne '')
591 && ($all_units->{$item_unit}->{factor} != 0)) {
592 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
596 $baseqty = $form->{"qty_$i"} * $basefactor;
598 my ($allocated, $taxrate) = (0, 0);
602 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
604 # keep entered selling price
606 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
608 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
610 my $decimalplaces = ($dec > 2) ? $dec : 2;
612 # undo discount formatting
613 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
616 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
618 # round linetotal to 2 decimal places
619 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
620 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
622 if ($form->{taxincluded}) {
623 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
624 $form->{"sellprice_$i"} =
625 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
627 $taxamount = $linetotal * $taxrate;
630 $netamount += $linetotal;
632 if ($taxamount != 0) {
634 $form->{amount}{ $form->{id} }{$_} +=
635 $taxamount * $form->{"${_}_rate"} / $taxrate
636 } split(/ /, $form->{"taxaccounts_$i"});
639 # add amount to income, $form->{amount}{trans_id}{accno}
640 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
642 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
643 $linetotal = $form->round_amount($linetotal, 2);
645 # this is the difference from the inventory
646 $invoicediff += ($amount - $linetotal);
648 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
651 $lastincomeaccno = $form->{"income_accno_$i"};
653 # adjust and round sellprice
654 $form->{"sellprice_$i"} =
655 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
658 next if $payments_only;
660 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
662 if ($form->{"assembly_$i"}) {
663 # record assembly item as allocated
664 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
667 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
671 # get pricegroup_id and save it
672 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
675 # save detail record in invoice table
677 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
678 sellprice, fxsellprice, discount, allocated, assemblyitem,
679 unit, deliverydate, project_id, serialnumber, pricegroup_id,
680 ordnumber, transdate, cusordnumber, base_qty, subtotal,
681 marge_percent, marge_total, lastcost,
682 price_factor_id, price_factor, marge_price_factor)
683 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
684 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
686 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
687 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
688 $form->{"sellprice_$i"}, $fxsellprice,
689 $form->{"discount_$i"}, $allocated, 'f',
690 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
691 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
692 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
693 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
694 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
695 $form->{"lastcost_$i"},
696 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
697 conv_i($form->{"marge_price_factor_$i"}));
698 do_query($form, $dbh, $query, @values);
700 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
702 qq|INSERT INTO licenseinvoice (trans_id, license_id)
703 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
704 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
705 do_query($form, $dbh, $query, @values);
710 $form->{datepaid} = $form->{invdate};
712 # total payments, don't move we need it here
713 for my $i (1 .. $form->{paidaccounts}) {
714 if ($form->{type} eq "credit_note") {
715 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
717 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
719 $form->{paid} += $form->{"paid_$i"};
720 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
723 my ($tax, $diff) = (0, 0);
725 $netamount = $form->round_amount($netamount, 2);
727 # figure out rounding errors for total amount vs netamount + taxes
728 if ($form->{taxincluded}) {
730 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
731 $diff += $amount - $netamount * $form->{exchangerate};
732 $netamount = $amount;
734 foreach my $item (split(/ /, $form->{taxaccounts})) {
735 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
736 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
737 $tax += $form->{amount}{ $form->{id} }{$item};
738 $netamount -= $form->{amount}{ $form->{id} }{$item};
741 $invoicediff += $diff;
742 ######## this only applies to tax included
743 if ($lastincomeaccno) {
744 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
748 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
749 $diff = $amount - $netamount * $form->{exchangerate};
750 $netamount = $amount;
751 foreach my $item (split(/ /, $form->{taxaccounts})) {
752 $form->{amount}{ $form->{id} }{$item} =
753 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
756 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
759 $amount - $form->{amount}{ $form->{id} }{$item} *
760 $form->{exchangerate};
761 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
762 $tax += $form->{amount}{ $form->{id} }{$item};
766 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
768 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
771 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
773 # update exchangerate
774 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
775 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
776 $form->{exchangerate}, 0);
779 $project_id = conv_i($form->{"globalproject_id"});
781 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
782 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
783 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
785 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
787 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
789 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
790 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
791 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
792 do_query($form, $dbh, $query, @values);
793 $form->{amount_cogs}{$trans_id}{$accno} = 0;
797 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
798 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
800 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
802 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
803 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
804 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
805 do_query($form, $dbh, $query, @values);
810 foreach my $trans_id (keys %{ $form->{amount} }) {
811 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
812 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
814 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
816 if (!$payments_only && ($form->{amount}{$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 = ?), ?, ?,
820 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
821 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
822 do_query($form, $dbh, $query, @values);
823 $form->{amount}{$trans_id}{$accno} = 0;
827 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
828 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
830 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
832 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
833 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
834 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
835 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
836 do_query($form, $dbh, $query, @values);
841 # deduct payment differences from diff
842 for my $i (1 .. $form->{paidaccounts}) {
843 if ($form->{"paid_$i"} != 0) {
845 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
846 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
850 # record payments and offsetting AR
851 if (!$form->{storno}) {
852 for my $i (1 .. $form->{paidaccounts}) {
854 next if ($form->{"paid_$i"} == 0);
856 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
857 $form->{"datepaid_$i"} = $form->{invdate}
858 unless ($form->{"datepaid_$i"});
859 $form->{datepaid} = $form->{"datepaid_$i"};
863 if ($form->{currency} eq $defaultcurrency) {
864 $form->{"exchangerate_$i"} = 1;
866 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
867 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
871 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
873 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
875 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
876 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
877 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
878 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
879 do_query($form, $dbh, $query, @values);
883 $form->{"paid_$i"} *= -1;
886 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
887 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
888 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
889 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
890 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
891 do_query($form, $dbh, $query, @values);
893 # exchangerate difference
894 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
895 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
899 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
900 $form->{"exchangerate_$i"};
902 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
905 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
911 # update exchange rate
912 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
913 $form->update_exchangerate($dbh, $form->{currency},
914 $form->{"datepaid_$i"},
915 $form->{"exchangerate_$i"}, 0);
919 } else { # if (!$form->{storno})
920 $form->{marge_total} *= -1;
923 if ($payments_only) {
924 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
925 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
927 if (!$provided_dbh) {
932 $main::lxdebug->leave_sub();
936 # record exchange rate differences and gains/losses
937 foreach my $accno (keys %{ $form->{fx} }) {
938 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
940 ($form->{fx}{$accno}{$transdate} =
941 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
946 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
947 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
948 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
949 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
950 do_query($form, $dbh, $query, @values);
955 $amount = $netamount + $tax;
958 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
960 $query = qq|UPDATE ar set
961 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
962 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
963 amount = ?, netamount = ?, paid = ?, datepaid = ?,
964 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
965 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
966 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
967 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
968 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
969 cp_id = ?, marge_total = ?, marge_percent = ?,
970 globalproject_id = ?, delivery_customer_id = ?,
971 transaction_description = ?, delivery_vendor_id = ?,
974 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
975 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
976 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
977 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
978 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
979 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
980 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
981 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
982 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
983 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
984 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
985 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
986 conv_i($form->{"id"}));
987 do_query($form, $dbh, $query, @values);
989 if($form->{"formname"} eq "credit_note") {
990 for my $i (1 .. $form->{rowcount}) {
991 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
992 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
993 do_query($form, $dbh, $query, @values);
997 if ($form->{storno}) {
1000 paid = paid + amount,
1002 intnotes = ? || intnotes
1004 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1005 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1009 $form->{name} = $form->{customer};
1010 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1012 if (!$form->{shipto_id}) {
1013 $form->add_shipto($dbh, $form->{id}, "AR");
1016 # save printed, emailed and queued
1017 $form->save_status($dbh);
1019 Common::webdav_folder($form) if ($main::webdav);
1021 # Link this record to the records it was created from.
1022 RecordLinks->create_links('dbh' => $dbh,
1024 'from_table' => 'oe',
1025 'from_ids' => $form->{convert_from_oe_ids},
1027 'to_id' => $form->{id},
1029 delete $form->{convert_from_oe_ids};
1031 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1033 if (scalar @convert_from_do_ids) {
1034 DO->close_orders('dbh' => $dbh,
1035 'ids' => \@convert_from_do_ids);
1037 RecordLinks->create_links('dbh' => $dbh,
1039 'from_table' => 'delivery_orders',
1040 'from_ids' => \@convert_from_do_ids,
1042 'to_id' => $form->{id},
1045 delete $form->{convert_from_do_ids};
1047 ARAP->close_orders_if_billed('dbh' => $dbh,
1048 'arap_id' => $form->{id},
1052 if (!$provided_dbh) {
1057 $main::lxdebug->leave_sub();
1062 sub _delete_payments {
1063 $main::lxdebug->enter_sub();
1065 my ($self, $form, $dbh) = @_;
1067 my @delete_acc_trans_ids;
1069 # Delete old payment entries from acc_trans.
1071 qq|SELECT acc_trans_id
1073 WHERE (trans_id = ?) AND fx_transaction
1077 SELECT at.acc_trans_id
1079 LEFT JOIN chart c ON (at.chart_id = c.id)
1080 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1081 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1084 qq|SELECT at.acc_trans_id
1086 LEFT JOIN chart c ON (at.chart_id = c.id)
1087 WHERE (trans_id = ?)
1088 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1089 ORDER BY at.acc_trans_id
1091 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1093 if (@delete_acc_trans_ids) {
1094 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1095 do_query($form, $dbh, $query);
1098 $main::lxdebug->leave_sub();
1102 $main::lxdebug->enter_sub();
1104 my ($self, $myconfig, $form, $locale) = @_;
1106 # connect to database, turn off autocommit
1107 my $dbh = $form->dbconnect_noauto($myconfig);
1109 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1111 $old_form = save_form();
1113 # Delete all entries in acc_trans from prior payments.
1114 $self->_delete_payments($form, $dbh);
1116 # Save the new payments the user made before cleaning up $form.
1117 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1119 # Clean up $form so that old content won't tamper the results.
1120 %keep_vars = map { $_, 1 } qw(login password id);
1121 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1123 # Retrieve the invoice from the database.
1124 $self->retrieve_invoice($myconfig, $form);
1126 # Set up the content of $form in the way that IS::post_invoice() expects.
1127 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1129 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1130 $item = $form->{invoice_details}->[$row - 1];
1132 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1134 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1137 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1139 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1141 # Restore the payment options from the user input.
1142 map { $form->{$_} = $payments{$_} } keys %payments;
1144 # Get the AR accno (which is normally done by Form::create_links()).
1148 LEFT JOIN chart c ON (at.chart_id = c.id)
1149 WHERE (trans_id = ?)
1150 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1151 ORDER BY at.acc_trans_id
1154 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1156 # Post the new payments.
1157 $self->post_invoice($myconfig, $form, $dbh, 1);
1159 restore_form($old_form);
1161 my $rc = $dbh->commit();
1164 $main::lxdebug->leave_sub();
1169 sub process_assembly {
1170 $main::lxdebug->enter_sub();
1172 my ($dbh, $form, $id, $totalqty) = @_;
1175 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1176 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1178 JOIN parts p ON (a.parts_id = p.id)
1180 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1182 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1186 $ref->{inventory_accno_id} *= 1;
1187 $ref->{expense_accno_id} *= 1;
1189 # multiply by number of assemblies
1190 $ref->{qty} *= $totalqty;
1192 if ($ref->{assembly}) {
1193 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1196 if ($ref->{inventory_accno_id}) {
1197 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1201 # save detail record for individual assembly item in invoice table
1203 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1204 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1205 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1206 do_query($form, $dbh, $query, @values);
1212 $main::lxdebug->leave_sub();
1216 $main::lxdebug->enter_sub();
1218 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1222 $form->{taxzone_id} *=1;
1223 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1224 my $taxzone_id = $form->{"taxzone_id"} * 1;
1226 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1227 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1228 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1229 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1230 FROM invoice i, parts p
1231 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1232 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1233 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1234 WHERE (i.parts_id = p.id)
1235 AND (i.parts_id = ?)
1236 AND ((i.base_qty + i.allocated) < 0)
1238 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1243 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1244 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1248 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1250 # total expenses and inventory
1251 # sellprice is the cost of the item
1252 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1255 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1257 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1258 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1259 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1261 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1262 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1268 last if (($totalqty -= $qty) <= 0);
1273 $main::lxdebug->leave_sub();
1278 sub reverse_invoice {
1279 $main::lxdebug->enter_sub();
1281 my ($dbh, $form) = @_;
1283 # reverse inventory items
1285 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1287 JOIN parts p ON (i.parts_id = p.id)
1288 WHERE i.trans_id = ?|;
1289 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1291 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1293 if ($ref->{inventory_accno_id}) {
1294 # de-allocated purchases
1296 qq|SELECT i.id, i.trans_id, i.allocated
1298 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1299 ORDER BY i.trans_id DESC|;
1300 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1302 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1303 my $qty = $ref->{qty};
1304 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1305 $qty = $inhref->{allocated};
1309 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1311 last if (($ref->{qty} -= $qty) <= 0);
1320 my @values = (conv_i($form->{id}));
1321 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1322 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1324 if ($form->{lizenzen}) {
1326 qq|DELETE FROM licenseinvoice
1327 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1328 do_query($form, $dbh, $query, @values);
1331 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1333 $main::lxdebug->leave_sub();
1336 sub delete_invoice {
1337 $main::lxdebug->enter_sub();
1339 my ($self, $myconfig, $form, $spool) = @_;
1341 # connect to database
1342 my $dbh = $form->dbconnect_noauto($myconfig);
1344 &reverse_invoice($dbh, $form);
1346 my @values = (conv_i($form->{id}));
1349 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1351 # delete spool files
1352 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1354 # delete status entries
1355 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1357 my $rc = $dbh->commit;
1361 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1364 $main::lxdebug->leave_sub();
1369 sub retrieve_invoice {
1370 $main::lxdebug->enter_sub();
1372 my ($self, $myconfig, $form) = @_;
1374 # connect to database
1375 my $dbh = $form->dbconnect_noauto($myconfig);
1377 my ($sth, $ref, $query);
1379 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1383 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1384 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1385 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1386 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1387 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1388 d.curr AS currencies
1392 $ref = selectfirst_hashref_query($form, $dbh, $query);
1393 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1396 my $id = conv_i($form->{id});
1399 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1403 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1404 a.orddate, a.quodate, a.globalproject_id,
1405 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1406 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1407 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1408 a.employee_id, a.salesman_id, a.payment_id,
1409 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1410 a.transaction_description,
1411 a.marge_total, a.marge_percent,
1412 e.name AS employee, a.donumber
1414 LEFT JOIN employee e ON (e.id = a.employee_id)
1416 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1417 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1420 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1423 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1424 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1426 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1428 foreach my $vc (qw(customer vendor)) {
1429 next if !$form->{"delivery_${vc}_id"};
1430 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1433 # get printed, emailed
1434 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1435 $sth = prepare_execute_query($form, $dbh, $query, $id);
1437 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1438 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1439 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1440 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1443 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1445 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1446 : $form->{invdate} ? $dbh->quote($form->{invdate})
1450 my $taxzone_id = $form->{taxzone_id} *= 1;
1451 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1453 # retrieve individual items
1456 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1457 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1458 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1460 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1461 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1462 i.price_factor_id, i.price_factor, i.marge_price_factor,
1463 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1464 pr.projectnumber, pg.partsgroup, prg.pricegroup
1467 LEFT JOIN parts p ON (i.parts_id = p.id)
1468 LEFT JOIN project pr ON (i.project_id = pr.id)
1469 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1470 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1472 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1473 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1474 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1476 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1478 $sth = prepare_execute_query($form, $dbh, $query, $id);
1480 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1481 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1482 delete($ref->{"part_inventory_accno_id"});
1484 foreach my $type (qw(inventory income expense)) {
1485 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1486 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1487 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1491 # get tax rates and description
1492 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1494 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1495 LEFT JOIN chart c ON (c.id = t.chart_id)
1497 (SELECT tk.tax_id FROM taxkeys tk
1498 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1499 AND startdate <= date($transdate)
1500 ORDER BY startdate DESC LIMIT 1)
1502 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1503 $ref->{taxaccounts} = "";
1505 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1507 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1511 $ref->{taxaccounts} .= "$ptr->{accno} ";
1513 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1514 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1515 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1516 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1517 $form->{taxaccounts} .= "$ptr->{accno} ";
1522 if ($form->{lizenzen}) {
1523 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1524 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1525 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1528 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1530 chop $ref->{taxaccounts};
1531 push @{ $form->{invoice_details} }, $ref;
1536 Common::webdav_folder($form) if ($main::webdav);
1539 my $rc = $dbh->commit;
1542 $main::lxdebug->leave_sub();
1548 $main::lxdebug->enter_sub();
1550 my ($self, $myconfig, $form) = @_;
1552 # connect to database
1553 my $dbh = $form->dbconnect($myconfig);
1555 my $dateformat = $myconfig->{dateformat};
1556 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1558 my (@values, $duedate, $ref, $query);
1560 if ($form->{invdate}) {
1561 $duedate = "to_date(?, '$dateformat')";
1562 push @values, $form->{invdate};
1564 $duedate = "current_date";
1567 my $cid = conv_i($form->{customer_id});
1570 if ($form->{payment_id}) {
1571 $payment_id = "(pt.id = ?) OR";
1572 push @values, conv_i($form->{payment_id});
1578 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1579 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1580 c.street, c.zipcode, c.city, c.country,
1581 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1582 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1583 b.discount AS tradediscount, b.description AS business
1585 LEFT JOIN business b ON (b.id = c.business_id)
1586 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1589 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1590 map { $form->{$_} = $ref->{$_} } keys %$ref;
1593 qq|SELECT sum(amount - paid) AS dunning_amount
1595 WHERE (paid < amount)
1596 AND (customer_id = ?)
1597 AND (dunning_config_id IS NOT NULL)|;
1598 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1599 map { $form->{$_} = $ref->{$_} } keys %$ref;
1602 qq|SELECT dnn.dunning_description AS max_dunning_level
1603 FROM dunning_config dnn
1604 WHERE id IN (SELECT dunning_config_id
1606 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1607 ORDER BY dunning_level DESC LIMIT 1|;
1608 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1609 map { $form->{$_} = $ref->{$_} } keys %$ref;
1611 $form->{creditremaining} = $form->{creditlimit};
1612 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1613 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1614 $form->{creditremaining} -= $value;
1618 (SELECT e.buy FROM exchangerate e
1619 WHERE e.curr = o.curr
1620 AND e.transdate = o.transdate)
1622 WHERE o.customer_id = ?
1623 AND o.quotation = '0'
1624 AND o.closed = '0'|;
1625 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1627 while (my ($amount, $exch) = $sth->fetchrow_array) {
1628 $exch = 1 unless $exch;
1629 $form->{creditremaining} -= $amount * $exch;
1633 # get shipto if we did not converted an order or invoice
1634 if (!$form->{shipto}) {
1635 map { delete $form->{$_} }
1636 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1637 shiptostreet shiptozipcode shiptocity shiptocountry
1638 shiptocontact shiptophone shiptofax shiptoemail);
1640 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1641 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1643 map { $form->{$_} = $ref->{$_} } keys %$ref;
1646 # setup last accounts used for this customer
1647 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1649 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1651 JOIN acc_trans ac ON (ac.chart_id = c.id)
1652 JOIN ar a ON (a.id = ac.trans_id)
1653 WHERE a.customer_id = ?
1654 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1655 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1656 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1659 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1660 if ($ref->{category} eq 'I') {
1662 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1664 if ($form->{initial_transdate}) {
1666 qq|SELECT tk.tax_id, t.rate
1668 LEFT JOIN tax t ON tk.tax_id = t.id
1669 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1670 ORDER BY tk.startdate DESC
1672 my ($tax_id, $rate) =
1673 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1674 $form->{initial_transdate});
1675 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1678 if ($ref->{category} eq 'A') {
1679 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1683 $form->{rowcount} = $i if ($i && !$form->{type});
1688 $main::lxdebug->leave_sub();
1692 $main::lxdebug->enter_sub();
1694 my ($self, $myconfig, $form) = @_;
1696 # connect to database
1697 my $dbh = $form->dbconnect($myconfig);
1699 my $i = $form->{rowcount};
1701 my $where = qq|NOT p.obsolete = '1'|;
1704 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1705 my ($table, $field) = split m/\./, $column;
1706 next if !$form->{"${field}_${i}"};
1707 $where .= qq| AND lower(${column}) ILIKE ?|;
1708 push @values, '%' . $form->{"${field}_${i}"} . '%';
1711 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1712 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1713 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1714 push @values, $form->{"partnumber_$i"};
1717 if ($form->{"description_$i"}) {
1718 $where .= qq| ORDER BY p.description|;
1720 $where .= qq| ORDER BY p.partnumber|;
1724 if ($form->{type} eq "invoice") {
1726 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1727 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1731 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1735 my $taxzone_id = $form->{taxzone_id} * 1;
1736 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1740 p.id, p.partnumber, p.description, p.sellprice,
1741 p.listprice, p.inventory_accno_id, p.lastcost,
1743 c1.accno AS inventory_accno,
1744 c1.new_chart_id AS inventory_new_chart,
1745 date($transdate) - c1.valid_from AS inventory_valid,
1747 c2.accno AS income_accno,
1748 c2.new_chart_id AS income_new_chart,
1749 date($transdate) - c2.valid_from AS income_valid,
1751 c3.accno AS expense_accno,
1752 c3.new_chart_id AS expense_new_chart,
1753 date($transdate) - c3.valid_from AS expense_valid,
1755 p.unit, p.assembly, p.bin, p.onhand,
1756 p.notes AS partnotes, p.notes AS longdescription,
1757 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1760 pfac.factor AS price_factor,
1765 LEFT JOIN chart c1 ON
1766 ((SELECT inventory_accno_id
1767 FROM buchungsgruppen
1768 WHERE id = p.buchungsgruppen_id) = c1.id)
1769 LEFT JOIN chart c2 ON
1770 ((SELECT income_accno_id_${taxzone_id}
1771 FROM buchungsgruppen
1772 WHERE id = p.buchungsgruppen_id) = c2.id)
1773 LEFT JOIN chart c3 ON
1774 ((SELECT expense_accno_id_${taxzone_id}
1775 FROM buchungsgruppen
1776 WHERE id = p.buchungsgruppen_id) = c3.id)
1777 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1778 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1780 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1782 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1784 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1785 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1786 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1787 if (!$ref->{inventory_accno_id}) {
1788 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1790 delete($ref->{inventory_accno_id});
1792 foreach my $type (qw(inventory income expense)) {
1793 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1795 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1798 ($ref->{"${type}_accno"},
1799 $ref->{"${type}_new_chart"},
1800 $ref->{"${type}_valid"})
1801 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1805 if ($form->{payment_id} eq "") {
1806 $form->{payment_id} = $form->{part_payment_id};
1809 # get tax rates and description
1810 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1812 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1814 LEFT JOIN chart c ON (c.id = t.chart_id)
1818 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1820 ORDER BY startdate DESC
1823 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1824 my $stw = $dbh->prepare($query);
1825 $stw->execute(@values) || $form->dberror($query);
1827 $ref->{taxaccounts} = "";
1829 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1831 # if ($customertax{$ref->{accno}})
1832 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1836 $ref->{taxaccounts} .= "$ptr->{accno} ";
1838 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1839 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1840 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1841 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1842 $form->{taxaccounts} .= "$ptr->{accno} ";
1848 chop $ref->{taxaccounts};
1849 if ($form->{language_id}) {
1851 qq|SELECT tr.translation, tr.longdescription
1853 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1854 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1855 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1856 if ($translation ne "") {
1857 $ref->{description} = $translation;
1858 $ref->{longdescription} = $longdescription;
1862 qq|SELECT tr.translation, tr.longdescription
1864 WHERE tr.language_id IN
1867 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1870 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1871 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1872 if ($translation ne "") {
1873 $ref->{description} = $translation;
1874 $ref->{longdescription} = $longdescription;
1879 $ref->{onhand} *= 1;
1881 push @{ $form->{item_list} }, $ref;
1883 if ($form->{lizenzen}) {
1884 if ($ref->{inventory_accno} > 0) {
1888 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1889 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1890 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1891 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1900 $main::lxdebug->leave_sub();
1903 ##########################
1904 # get pricegroups from database
1905 # build up selected pricegroup
1906 # if an exchange rate - change price
1909 sub get_pricegroups_for_parts {
1911 $main::lxdebug->enter_sub();
1913 my ($self, $myconfig, $form) = @_;
1915 my $dbh = $form->dbconnect($myconfig);
1917 $form->{"PRICES"} = {};
1921 my $all_units = AM->retrieve_units($myconfig, $form);
1922 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1923 $form->{"PRICES"}{$i} = [];
1925 $id = $form->{"id_$i"};
1927 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1929 $id = $form->{"new_id_$i"};
1932 my ($price, $selectedpricegroup_id) = split(/--/,
1933 $form->{"sellprice_pg_$i"});
1935 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1936 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1937 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1939 my $price_new = $form->{"price_new_$i"};
1940 my $price_old = $form->{"price_old_$i"};
1942 if (!$form->{"unit_old_$i"}) {
1943 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1944 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1945 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1946 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1949 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1950 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1951 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1953 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1954 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1955 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1956 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1957 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1958 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1959 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1964 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1965 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1966 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1967 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1968 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1972 if (!$form->{"basefactor_$i"}) {
1973 $form->{"basefactor_$i"} = 1;
1979 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1980 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1990 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1992 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1993 'selected' AS selected
1996 ORDER BY pricegroup|;
1997 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1998 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2000 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2002 $pkr->{selected} = '';
2004 # if there is an exchange rate change price
2005 if (($form->{exchangerate} * 1) != 0) {
2007 $pkr->{price} /= $form->{exchangerate};
2010 $pkr->{price} *= $form->{"basefactor_$i"};
2012 $pkr->{price} *= $basefactor;
2014 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2016 if ($selectedpricegroup_id eq undef) {
2017 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2019 $pkr->{selected} = ' selected';
2021 # no customer pricesgroup set
2022 if ($pkr->{price} == $pkr->{default_sellprice}) {
2024 $pkr->{price} = $form->{"sellprice_$i"};
2028 # this sub should not set anything and only return. --sschoeling, 20090506
2029 # $form->{"sellprice_$i"} = $pkr->{price};
2032 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2033 $pkr->{price} = $form->{"sellprice_$i"};
2034 $pkr->{selected} = ' selected';
2038 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2039 if ($selectedpricegroup_id ne $pricegroup_old) {
2040 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2041 $pkr->{selected} = ' selected';
2043 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2044 if ($pkr->{pricegroup_id} == 0) {
2045 $pkr->{price} = $form->{"sellprice_$i"};
2046 $pkr->{selected} = ' selected';
2048 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2049 $pkr->{selected} = ' selected';
2050 if ( ($pkr->{pricegroup_id} == 0)
2051 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2052 # $pkr->{price} = $form->{"sellprice_$i"};
2054 $pkr->{price} = $form->{"sellprice_$i"};
2058 push @{ $form->{PRICES}{$i} }, $pkr;
2061 $form->{"basefactor_$i"} *= $basefactor;
2070 $main::lxdebug->leave_sub();
2074 $main::lxdebug->enter_sub();
2076 my ($self, $myconfig, $form, $table) = @_;
2078 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2080 # make sure there's no funny stuff in $table
2081 # ToDO: die when this happens and throw an error
2082 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2084 my $dbh = $form->dbconnect($myconfig);
2086 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2087 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2091 $main::lxdebug->leave_sub();
2097 $main::lxdebug->enter_sub();
2099 my ($self, $myconfig, $form, $table, $id) = @_;
2101 $main::lxdebug->leave_sub() and return 0 unless ($id);
2103 # make sure there's no funny stuff in $table
2104 # ToDO: die when this happens and throw an error
2105 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2107 my $dbh = $form->dbconnect($myconfig);
2109 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2110 my ($result) = selectrow_query($form, $dbh, $query, $id);
2114 $main::lxdebug->leave_sub();