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_cogs} = {};
558 foreach my $i (1 .. $form->{rowcount}) {
559 if ($form->{type} eq "credit_note") {
560 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
561 $form->{shipped} = 1;
563 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
568 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
569 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
570 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
572 if ($form->{storno}) {
573 $form->{"qty_$i"} *= -1;
576 if ($form->{"id_$i"}) {
579 if (defined($baseunits{$form->{"id_$i"}})) {
580 $item_unit = $baseunits{$form->{"id_$i"}};
583 $query = qq|SELECT unit FROM parts WHERE id = ?|;
584 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
585 $baseunits{$form->{"id_$i"}} = $item_unit;
588 if (defined($all_units->{$item_unit}->{factor})
589 && ($all_units->{$item_unit}->{factor} ne '')
590 && ($all_units->{$item_unit}->{factor} != 0)) {
591 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
595 $baseqty = $form->{"qty_$i"} * $basefactor;
597 my ($allocated, $taxrate) = (0, 0);
601 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
603 # keep entered selling price
605 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
607 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
609 my $decimalplaces = ($dec > 2) ? $dec : 2;
611 # undo discount formatting
612 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
615 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
617 # round linetotal to 2 decimal places
618 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
619 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
621 if ($form->{taxincluded}) {
622 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
623 $form->{"sellprice_$i"} =
624 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
626 $taxamount = $linetotal * $taxrate;
629 $netamount += $linetotal;
631 if ($taxamount != 0) {
633 $form->{amount}{ $form->{id} }{$_} +=
634 $taxamount * $form->{"${_}_rate"} / $taxrate
635 } split(/ /, $form->{"taxaccounts_$i"});
638 # add amount to income, $form->{amount}{trans_id}{accno}
639 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
641 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
642 $linetotal = $form->round_amount($linetotal, 2);
644 # this is the difference from the inventory
645 $invoicediff += ($amount - $linetotal);
647 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
650 $lastincomeaccno = $form->{"income_accno_$i"};
652 # adjust and round sellprice
653 $form->{"sellprice_$i"} =
654 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
657 next if $payments_only;
659 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
661 if ($form->{"assembly_$i"}) {
662 # record assembly item as allocated
663 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
666 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
670 # get pricegroup_id and save it
671 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
674 # save detail record in invoice table
676 qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
677 sellprice, fxsellprice, discount, allocated, assemblyitem,
678 unit, deliverydate, project_id, serialnumber, pricegroup_id,
679 ordnumber, transdate, cusordnumber, base_qty, subtotal,
680 marge_percent, marge_total, lastcost,
681 price_factor_id, price_factor, marge_price_factor)
682 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
683 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
685 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
686 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
687 $form->{"sellprice_$i"}, $fxsellprice,
688 $form->{"discount_$i"}, $allocated, 'f',
689 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
690 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
691 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
692 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
693 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
694 $form->{"lastcost_$i"},
695 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
696 conv_i($form->{"marge_price_factor_$i"}));
697 do_query($form, $dbh, $query, @values);
699 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
701 qq|INSERT INTO licenseinvoice (trans_id, license_id)
702 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
703 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
704 do_query($form, $dbh, $query, @values);
709 $form->{datepaid} = $form->{invdate};
711 # total payments, don't move we need it here
712 for my $i (1 .. $form->{paidaccounts}) {
713 if ($form->{type} eq "credit_note") {
714 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
716 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
718 $form->{paid} += $form->{"paid_$i"};
719 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
722 my ($tax, $diff) = (0, 0);
724 $netamount = $form->round_amount($netamount, 2);
726 # figure out rounding errors for total amount vs netamount + taxes
727 if ($form->{taxincluded}) {
729 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
730 $diff += $amount - $netamount * $form->{exchangerate};
731 $netamount = $amount;
733 foreach my $item (split(/ /, $form->{taxaccounts})) {
734 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
735 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
736 $tax += $form->{amount}{ $form->{id} }{$item};
737 $netamount -= $form->{amount}{ $form->{id} }{$item};
740 $invoicediff += $diff;
741 ######## this only applies to tax included
742 if ($lastincomeaccno) {
743 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
747 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
748 $diff = $amount - $netamount * $form->{exchangerate};
749 $netamount = $amount;
750 foreach my $item (split(/ /, $form->{taxaccounts})) {
751 $form->{amount}{ $form->{id} }{$item} =
752 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
755 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
758 $amount - $form->{amount}{ $form->{id} }{$item} *
759 $form->{exchangerate};
760 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
761 $tax += $form->{amount}{ $form->{id} }{$item};
765 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
767 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
770 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
772 # update exchangerate
773 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
774 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
775 $form->{exchangerate}, 0);
778 $project_id = conv_i($form->{"globalproject_id"});
780 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
781 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
782 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
784 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
786 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
788 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
789 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
790 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
791 do_query($form, $dbh, $query, @values);
792 $form->{amount_cogs}{$trans_id}{$accno} = 0;
796 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
797 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
799 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
801 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
802 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
803 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
804 do_query($form, $dbh, $query, @values);
809 foreach my $trans_id (keys %{ $form->{amount} }) {
810 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
811 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
813 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
815 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
817 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
818 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
819 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
820 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
821 do_query($form, $dbh, $query, @values);
822 $form->{amount}{$trans_id}{$accno} = 0;
826 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
827 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
829 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
831 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
832 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
833 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
834 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
835 do_query($form, $dbh, $query, @values);
840 # deduct payment differences from diff
841 for my $i (1 .. $form->{paidaccounts}) {
842 if ($form->{"paid_$i"} != 0) {
844 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
845 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
849 # record payments and offsetting AR
850 if (!$form->{storno}) {
851 for my $i (1 .. $form->{paidaccounts}) {
853 next if ($form->{"paid_$i"} == 0);
855 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
856 $form->{"datepaid_$i"} = $form->{invdate}
857 unless ($form->{"datepaid_$i"});
858 $form->{datepaid} = $form->{"datepaid_$i"};
862 if ($form->{currency} eq $defaultcurrency) {
863 $form->{"exchangerate_$i"} = 1;
865 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
866 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
870 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
872 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
874 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
875 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
876 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
877 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
878 do_query($form, $dbh, $query, @values);
882 $form->{"paid_$i"} *= -1;
885 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, 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"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
889 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
890 do_query($form, $dbh, $query, @values);
892 # exchangerate difference
893 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
894 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
898 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
899 $form->{"exchangerate_$i"};
901 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
904 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
910 # update exchange rate
911 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
912 $form->update_exchangerate($dbh, $form->{currency},
913 $form->{"datepaid_$i"},
914 $form->{"exchangerate_$i"}, 0);
918 } else { # if (!$form->{storno})
919 $form->{marge_total} *= -1;
922 if ($payments_only) {
923 $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
924 do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
926 if (!$provided_dbh) {
931 $main::lxdebug->leave_sub();
935 # record exchange rate differences and gains/losses
936 foreach my $accno (keys %{ $form->{fx} }) {
937 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
939 ($form->{fx}{$accno}{$transdate} =
940 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
945 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
946 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
947 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
948 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
949 do_query($form, $dbh, $query, @values);
954 $amount = $netamount + $tax;
957 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
959 $query = qq|UPDATE ar set
960 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
961 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
962 amount = ?, netamount = ?, paid = ?, datepaid = ?,
963 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
964 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
965 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
966 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
967 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
968 cp_id = ?, marge_total = ?, marge_percent = ?,
969 globalproject_id = ?, delivery_customer_id = ?,
970 transaction_description = ?, delivery_vendor_id = ?,
973 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
974 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
975 $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
976 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
977 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
978 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
979 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
980 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
981 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
982 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
983 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
984 $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
985 conv_i($form->{"id"}));
986 do_query($form, $dbh, $query, @values);
988 if($form->{"formname"} eq "credit_note") {
989 for my $i (1 .. $form->{rowcount}) {
990 $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
991 @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
992 do_query($form, $dbh, $query, @values);
996 if ($form->{storno}) {
999 paid = paid + amount,
1001 intnotes = ? || intnotes
1003 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1004 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1008 $form->{name} = $form->{customer};
1009 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1011 if (!$form->{shipto_id}) {
1012 $form->add_shipto($dbh, $form->{id}, "AR");
1015 # save printed, emailed and queued
1016 $form->save_status($dbh);
1018 Common::webdav_folder($form) if ($main::webdav);
1020 # Link this record to the records it was created from.
1021 RecordLinks->create_links('dbh' => $dbh,
1023 'from_table' => 'oe',
1024 'from_ids' => $form->{convert_from_oe_ids},
1026 'to_id' => $form->{id},
1028 delete $form->{convert_from_oe_ids};
1030 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1032 if (scalar @convert_from_do_ids) {
1033 DO->close_orders('dbh' => $dbh,
1034 'ids' => \@convert_from_do_ids);
1036 RecordLinks->create_links('dbh' => $dbh,
1038 'from_table' => 'delivery_orders',
1039 'from_ids' => \@convert_from_do_ids,
1041 'to_id' => $form->{id},
1044 delete $form->{convert_from_do_ids};
1046 ARAP->close_orders_if_billed('dbh' => $dbh,
1047 'arap_id' => $form->{id},
1051 if (!$provided_dbh) {
1056 $main::lxdebug->leave_sub();
1061 sub _delete_payments {
1062 $main::lxdebug->enter_sub();
1064 my ($self, $form, $dbh) = @_;
1066 my @delete_acc_trans_ids;
1068 # Delete old payment entries from acc_trans.
1070 qq|SELECT acc_trans_id
1072 WHERE (trans_id = ?) AND fx_transaction
1076 SELECT at.acc_trans_id
1078 LEFT JOIN chart c ON (at.chart_id = c.id)
1079 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1080 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1083 qq|SELECT at.acc_trans_id
1085 LEFT JOIN chart c ON (at.chart_id = c.id)
1086 WHERE (trans_id = ?)
1087 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1088 ORDER BY at.acc_trans_id
1090 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1092 if (@delete_acc_trans_ids) {
1093 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1094 do_query($form, $dbh, $query);
1097 $main::lxdebug->leave_sub();
1101 $main::lxdebug->enter_sub();
1103 my ($self, $myconfig, $form, $locale) = @_;
1105 # connect to database, turn off autocommit
1106 my $dbh = $form->dbconnect_noauto($myconfig);
1108 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1110 $old_form = save_form();
1112 # Delete all entries in acc_trans from prior payments.
1113 $self->_delete_payments($form, $dbh);
1115 # Save the new payments the user made before cleaning up $form.
1116 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1118 # Clean up $form so that old content won't tamper the results.
1119 %keep_vars = map { $_, 1 } qw(login password id);
1120 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1122 # Retrieve the invoice from the database.
1123 $self->retrieve_invoice($myconfig, $form);
1125 # Set up the content of $form in the way that IS::post_invoice() expects.
1126 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1128 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1129 $item = $form->{invoice_details}->[$row - 1];
1131 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1133 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1136 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1138 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1140 # Restore the payment options from the user input.
1141 map { $form->{$_} = $payments{$_} } keys %payments;
1143 # Get the AR accno (which is normally done by Form::create_links()).
1147 LEFT JOIN chart c ON (at.chart_id = c.id)
1148 WHERE (trans_id = ?)
1149 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1150 ORDER BY at.acc_trans_id
1153 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1155 # Post the new payments.
1156 $self->post_invoice($myconfig, $form, $dbh, 1);
1158 restore_form($old_form);
1160 my $rc = $dbh->commit();
1163 $main::lxdebug->leave_sub();
1168 sub process_assembly {
1169 $main::lxdebug->enter_sub();
1171 my ($dbh, $form, $id, $totalqty) = @_;
1174 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1175 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1177 JOIN parts p ON (a.parts_id = p.id)
1179 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1181 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1185 $ref->{inventory_accno_id} *= 1;
1186 $ref->{expense_accno_id} *= 1;
1188 # multiply by number of assemblies
1189 $ref->{qty} *= $totalqty;
1191 if ($ref->{assembly}) {
1192 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1195 if ($ref->{inventory_accno_id}) {
1196 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1200 # save detail record for individual assembly item in invoice table
1202 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1203 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1204 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1205 do_query($form, $dbh, $query, @values);
1211 $main::lxdebug->leave_sub();
1215 $main::lxdebug->enter_sub();
1217 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1221 $form->{taxzone_id} *=1;
1222 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1223 my $taxzone_id = $form->{"taxzone_id"} * 1;
1225 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
1226 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1227 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1228 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1229 FROM invoice i, parts p
1230 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1231 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1232 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1233 WHERE (i.parts_id = p.id)
1234 AND (i.parts_id = ?)
1235 AND ((i.base_qty + i.allocated) < 0)
1237 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1242 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1243 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1247 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1249 # total expenses and inventory
1250 # sellprice is the cost of the item
1251 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
1254 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1256 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1257 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1258 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1260 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1261 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1267 last if (($totalqty -= $qty) <= 0);
1272 $main::lxdebug->leave_sub();
1277 sub reverse_invoice {
1278 $main::lxdebug->enter_sub();
1280 my ($dbh, $form) = @_;
1282 # reverse inventory items
1284 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1286 JOIN parts p ON (i.parts_id = p.id)
1287 WHERE i.trans_id = ?|;
1288 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1290 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1292 if ($ref->{inventory_accno_id}) {
1293 # de-allocated purchases
1295 qq|SELECT i.id, i.trans_id, i.allocated
1297 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1298 ORDER BY i.trans_id DESC|;
1299 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1301 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1302 my $qty = $ref->{qty};
1303 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1304 $qty = $inhref->{allocated};
1308 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1310 last if (($ref->{qty} -= $qty) <= 0);
1319 my @values = (conv_i($form->{id}));
1320 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1321 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1323 if ($form->{lizenzen}) {
1325 qq|DELETE FROM licenseinvoice
1326 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1327 do_query($form, $dbh, $query, @values);
1330 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1332 $main::lxdebug->leave_sub();
1335 sub delete_invoice {
1336 $main::lxdebug->enter_sub();
1338 my ($self, $myconfig, $form, $spool) = @_;
1340 # connect to database
1341 my $dbh = $form->dbconnect_noauto($myconfig);
1343 &reverse_invoice($dbh, $form);
1345 my @values = (conv_i($form->{id}));
1348 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1350 # delete spool files
1351 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1353 # delete status entries
1354 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1356 my $rc = $dbh->commit;
1360 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1363 $main::lxdebug->leave_sub();
1368 sub retrieve_invoice {
1369 $main::lxdebug->enter_sub();
1371 my ($self, $myconfig, $form) = @_;
1373 # connect to database
1374 my $dbh = $form->dbconnect_noauto($myconfig);
1376 my ($sth, $ref, $query);
1378 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1382 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1383 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1384 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1385 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1386 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1387 d.curr AS currencies
1391 $ref = selectfirst_hashref_query($form, $dbh, $query);
1392 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1395 my $id = conv_i($form->{id});
1398 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1402 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1403 a.orddate, a.quodate, a.globalproject_id,
1404 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1405 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1406 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1407 a.employee_id, a.salesman_id, a.payment_id,
1408 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1409 a.transaction_description,
1410 a.marge_total, a.marge_percent,
1411 e.name AS employee, a.donumber
1413 LEFT JOIN employee e ON (e.id = a.employee_id)
1415 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1416 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1419 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1422 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1423 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1425 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1427 foreach my $vc (qw(customer vendor)) {
1428 next if !$form->{"delivery_${vc}_id"};
1429 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1432 # get printed, emailed
1433 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1434 $sth = prepare_execute_query($form, $dbh, $query, $id);
1436 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1437 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1438 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1439 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1442 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1444 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1445 : $form->{invdate} ? $dbh->quote($form->{invdate})
1449 my $taxzone_id = $form->{taxzone_id} *= 1;
1450 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1452 # retrieve individual items
1455 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1456 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1457 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1459 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1460 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1461 i.price_factor_id, i.price_factor, i.marge_price_factor,
1462 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1463 pr.projectnumber, pg.partsgroup, prg.pricegroup
1466 LEFT JOIN parts p ON (i.parts_id = p.id)
1467 LEFT JOIN project pr ON (i.project_id = pr.id)
1468 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1469 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1471 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1472 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1473 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1475 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1477 $sth = prepare_execute_query($form, $dbh, $query, $id);
1479 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1480 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1481 delete($ref->{"part_inventory_accno_id"});
1483 foreach my $type (qw(inventory income expense)) {
1484 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1485 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1486 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1490 # get tax rates and description
1491 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1493 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1494 LEFT JOIN chart c ON (c.id = t.chart_id)
1496 (SELECT tk.tax_id FROM taxkeys tk
1497 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1498 AND startdate <= date($transdate)
1499 ORDER BY startdate DESC LIMIT 1)
1501 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1502 $ref->{taxaccounts} = "";
1504 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1506 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1510 $ref->{taxaccounts} .= "$ptr->{accno} ";
1512 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1513 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1514 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1515 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1516 $form->{taxaccounts} .= "$ptr->{accno} ";
1521 if ($form->{lizenzen}) {
1522 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1523 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1524 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1527 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1529 chop $ref->{taxaccounts};
1530 push @{ $form->{invoice_details} }, $ref;
1535 Common::webdav_folder($form) if ($main::webdav);
1538 my $rc = $dbh->commit;
1541 $main::lxdebug->leave_sub();
1547 $main::lxdebug->enter_sub();
1549 my ($self, $myconfig, $form) = @_;
1551 # connect to database
1552 my $dbh = $form->dbconnect($myconfig);
1554 my $dateformat = $myconfig->{dateformat};
1555 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1557 my (@values, $duedate, $ref, $query);
1559 if ($form->{invdate}) {
1560 $duedate = "to_date(?, '$dateformat')";
1561 push @values, $form->{invdate};
1563 $duedate = "current_date";
1566 my $cid = conv_i($form->{customer_id});
1569 if ($form->{payment_id}) {
1570 $payment_id = "(pt.id = ?) OR";
1571 push @values, conv_i($form->{payment_id});
1577 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1578 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1579 c.street, c.zipcode, c.city, c.country,
1580 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1581 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1582 b.discount AS tradediscount, b.description AS business
1584 LEFT JOIN business b ON (b.id = c.business_id)
1585 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1588 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1589 map { $form->{$_} = $ref->{$_} } keys %$ref;
1592 qq|SELECT sum(amount - paid) AS dunning_amount
1594 WHERE (paid < amount)
1595 AND (customer_id = ?)
1596 AND (dunning_config_id IS NOT NULL)|;
1597 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1598 map { $form->{$_} = $ref->{$_} } keys %$ref;
1601 qq|SELECT dnn.dunning_description AS max_dunning_level
1602 FROM dunning_config dnn
1603 WHERE id IN (SELECT dunning_config_id
1605 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1606 ORDER BY dunning_level DESC LIMIT 1|;
1607 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1608 map { $form->{$_} = $ref->{$_} } keys %$ref;
1610 $form->{creditremaining} = $form->{creditlimit};
1611 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1612 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1613 $form->{creditremaining} -= $value;
1617 (SELECT e.buy FROM exchangerate e
1618 WHERE e.curr = o.curr
1619 AND e.transdate = o.transdate)
1621 WHERE o.customer_id = ?
1622 AND o.quotation = '0'
1623 AND o.closed = '0'|;
1624 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1626 while (my ($amount, $exch) = $sth->fetchrow_array) {
1627 $exch = 1 unless $exch;
1628 $form->{creditremaining} -= $amount * $exch;
1632 # get shipto if we did not converted an order or invoice
1633 if (!$form->{shipto}) {
1634 map { delete $form->{$_} }
1635 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1636 shiptostreet shiptozipcode shiptocity shiptocountry
1637 shiptocontact shiptophone shiptofax shiptoemail);
1639 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1640 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1642 map { $form->{$_} = $ref->{$_} } keys %$ref;
1645 # setup last accounts used for this customer
1646 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1648 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1650 JOIN acc_trans ac ON (ac.chart_id = c.id)
1651 JOIN ar a ON (a.id = ac.trans_id)
1652 WHERE a.customer_id = ?
1653 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1654 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1655 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1658 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1659 if ($ref->{category} eq 'I') {
1661 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1663 if ($form->{initial_transdate}) {
1665 qq|SELECT tk.tax_id, t.rate
1667 LEFT JOIN tax t ON tk.tax_id = t.id
1668 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1669 ORDER BY tk.startdate DESC
1671 my ($tax_id, $rate) =
1672 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1673 $form->{initial_transdate});
1674 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1677 if ($ref->{category} eq 'A') {
1678 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1682 $form->{rowcount} = $i if ($i && !$form->{type});
1687 $main::lxdebug->leave_sub();
1691 $main::lxdebug->enter_sub();
1693 my ($self, $myconfig, $form) = @_;
1695 # connect to database
1696 my $dbh = $form->dbconnect($myconfig);
1698 my $i = $form->{rowcount};
1700 my $where = qq|NOT p.obsolete = '1'|;
1703 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1704 my ($table, $field) = split m/\./, $column;
1705 next if !$form->{"${field}_${i}"};
1706 $where .= qq| AND lower(${column}) ILIKE ?|;
1707 push @values, '%' . $form->{"${field}_${i}"} . '%';
1710 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1711 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1712 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1713 push @values, $form->{"partnumber_$i"};
1716 if ($form->{"description_$i"}) {
1717 $where .= qq| ORDER BY p.description|;
1719 $where .= qq| ORDER BY p.partnumber|;
1723 if ($form->{type} eq "invoice") {
1725 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1726 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1730 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1734 my $taxzone_id = $form->{taxzone_id} * 1;
1735 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1739 p.id, p.partnumber, p.description, p.sellprice,
1740 p.listprice, p.inventory_accno_id, p.lastcost,
1742 c1.accno AS inventory_accno,
1743 c1.new_chart_id AS inventory_new_chart,
1744 date($transdate) - c1.valid_from AS inventory_valid,
1746 c2.accno AS income_accno,
1747 c2.new_chart_id AS income_new_chart,
1748 date($transdate) - c2.valid_from AS income_valid,
1750 c3.accno AS expense_accno,
1751 c3.new_chart_id AS expense_new_chart,
1752 date($transdate) - c3.valid_from AS expense_valid,
1754 p.unit, p.assembly, p.bin, p.onhand,
1755 p.notes AS partnotes, p.notes AS longdescription,
1756 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1759 pfac.factor AS price_factor,
1764 LEFT JOIN chart c1 ON
1765 ((SELECT inventory_accno_id
1766 FROM buchungsgruppen
1767 WHERE id = p.buchungsgruppen_id) = c1.id)
1768 LEFT JOIN chart c2 ON
1769 ((SELECT income_accno_id_${taxzone_id}
1770 FROM buchungsgruppen
1771 WHERE id = p.buchungsgruppen_id) = c2.id)
1772 LEFT JOIN chart c3 ON
1773 ((SELECT expense_accno_id_${taxzone_id}
1774 FROM buchungsgruppen
1775 WHERE id = p.buchungsgruppen_id) = c3.id)
1776 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1777 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1779 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1781 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1783 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1784 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1785 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1786 if (!$ref->{inventory_accno_id}) {
1787 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1789 delete($ref->{inventory_accno_id});
1791 foreach my $type (qw(inventory income expense)) {
1792 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1794 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1797 ($ref->{"${type}_accno"},
1798 $ref->{"${type}_new_chart"},
1799 $ref->{"${type}_valid"})
1800 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1804 if ($form->{payment_id} eq "") {
1805 $form->{payment_id} = $form->{part_payment_id};
1808 # get tax rates and description
1809 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1811 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1813 LEFT JOIN chart c ON (c.id = t.chart_id)
1817 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1819 ORDER BY startdate DESC
1822 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1823 my $stw = $dbh->prepare($query);
1824 $stw->execute(@values) || $form->dberror($query);
1826 $ref->{taxaccounts} = "";
1828 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1830 # if ($customertax{$ref->{accno}})
1831 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1835 $ref->{taxaccounts} .= "$ptr->{accno} ";
1837 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1838 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1839 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1840 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1841 $form->{taxaccounts} .= "$ptr->{accno} ";
1847 chop $ref->{taxaccounts};
1848 if ($form->{language_id}) {
1850 qq|SELECT tr.translation, tr.longdescription
1852 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1853 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1854 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1855 if ($translation ne "") {
1856 $ref->{description} = $translation;
1857 $ref->{longdescription} = $longdescription;
1861 qq|SELECT tr.translation, tr.longdescription
1863 WHERE tr.language_id IN
1866 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1869 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1870 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1871 if ($translation ne "") {
1872 $ref->{description} = $translation;
1873 $ref->{longdescription} = $longdescription;
1878 $ref->{onhand} *= 1;
1880 push @{ $form->{item_list} }, $ref;
1882 if ($form->{lizenzen}) {
1883 if ($ref->{inventory_accno} > 0) {
1887 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1888 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1889 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1890 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1899 $main::lxdebug->leave_sub();
1902 ##########################
1903 # get pricegroups from database
1904 # build up selected pricegroup
1905 # if an exchange rate - change price
1908 sub get_pricegroups_for_parts {
1910 $main::lxdebug->enter_sub();
1912 my ($self, $myconfig, $form) = @_;
1914 my $dbh = $form->dbconnect($myconfig);
1916 $form->{"PRICES"} = {};
1920 my $all_units = AM->retrieve_units($myconfig, $form);
1921 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1922 $form->{"PRICES"}{$i} = [];
1924 $id = $form->{"id_$i"};
1926 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1928 $id = $form->{"new_id_$i"};
1931 my ($price, $selectedpricegroup_id) = split(/--/,
1932 $form->{"sellprice_pg_$i"});
1934 my $pricegroup_old = $form->{"pricegroup_old_$i"};
1935 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
1936 $form->{"old_pricegroup_$i"} = $pricegroup_old;
1938 my $price_new = $form->{"price_new_$i"};
1939 my $price_old = $form->{"price_old_$i"};
1941 if (!$form->{"unit_old_$i"}) {
1942 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
1943 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
1944 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
1945 $form->{"unit_old_$i"} = $form->{"unit_$i"};
1948 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
1949 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
1950 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
1952 if (!$all_units->{$form->{"selected_unit_$i"}} ||
1953 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
1954 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
1955 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
1956 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
1957 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
1958 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
1963 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
1964 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
1965 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
1966 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
1967 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
1971 if (!$form->{"basefactor_$i"}) {
1972 $form->{"basefactor_$i"} = 1;
1978 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
1979 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
1989 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
1991 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
1992 'selected' AS selected
1995 ORDER BY pricegroup|;
1996 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
1997 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
1999 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2001 $pkr->{selected} = '';
2003 # if there is an exchange rate change price
2004 if (($form->{exchangerate} * 1) != 0) {
2006 $pkr->{price} /= $form->{exchangerate};
2009 $pkr->{price} *= $form->{"basefactor_$i"};
2011 $pkr->{price} *= $basefactor;
2013 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2015 if ($selectedpricegroup_id eq undef) {
2016 if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
2018 $pkr->{selected} = ' selected';
2020 # no customer pricesgroup set
2021 if ($pkr->{price} == $pkr->{default_sellprice}) {
2023 $pkr->{price} = $form->{"sellprice_$i"};
2027 # this sub should not set anything and only return. --sschoeling, 20090506
2028 # $form->{"sellprice_$i"} = $pkr->{price};
2031 } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
2032 $pkr->{price} = $form->{"sellprice_$i"};
2033 $pkr->{selected} = ' selected';
2037 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2038 if ($selectedpricegroup_id ne $pricegroup_old) {
2039 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2040 $pkr->{selected} = ' selected';
2042 } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
2043 if ($pkr->{pricegroup_id} == 0) {
2044 $pkr->{price} = $form->{"sellprice_$i"};
2045 $pkr->{selected} = ' selected';
2047 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2048 $pkr->{selected} = ' selected';
2049 if ( ($pkr->{pricegroup_id} == 0)
2050 and ($pkr->{price} == $form->{"sellprice_$i"})) {
2051 # $pkr->{price} = $form->{"sellprice_$i"};
2053 $pkr->{price} = $form->{"sellprice_$i"};
2057 push @{ $form->{PRICES}{$i} }, $pkr;
2060 $form->{"basefactor_$i"} *= $basefactor;
2069 $main::lxdebug->leave_sub();
2073 $main::lxdebug->enter_sub();
2075 my ($self, $myconfig, $form, $table) = @_;
2077 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2079 # make sure there's no funny stuff in $table
2080 # ToDO: die when this happens and throw an error
2081 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2083 my $dbh = $form->dbconnect($myconfig);
2085 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2086 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2090 $main::lxdebug->leave_sub();
2096 $main::lxdebug->enter_sub();
2098 my ($self, $myconfig, $form, $table, $id) = @_;
2100 $main::lxdebug->leave_sub() and return 0 unless ($id);
2102 # make sure there's no funny stuff in $table
2103 # ToDO: die when this happens and throw an error
2104 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2106 my $dbh = $form->dbconnect($myconfig);
2108 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2109 my ($result) = selectrow_query($form, $dbh, $query, $id);
2113 $main::lxdebug->leave_sub();