1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory invoicing module
33 #======================================================================
37 use List::Util qw(max);
45 use SL::GenericTranslations;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form, $locale) = @_;
59 $form->{duedate} ||= $form->{invdate};
62 my $dbh = $form->dbconnect($myconfig);
65 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
66 ($form->{terms}) = selectrow_query($form, $dbh, $query);
68 my (@project_ids, %projectnumbers, %projectdescriptions);
69 $form->{TEMPLATE_ARRAYS} = {};
71 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
73 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
76 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
77 $price_factors{$pfac->{id}} = $pfac;
79 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
82 # sort items by partsgroup
83 for my $i (1 .. $form->{rowcount}) {
85 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
86 # $partsgroup = $form->{"partsgroup_$i"};
88 # push @partsgroup, [$i, $partsgroup];
89 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
93 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
94 join(", ", map({ "?" } @project_ids)) . ")";
95 $sth = $dbh->prepare($query);
96 $sth->execute(@project_ids) ||
97 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
98 while (my $ref = $sth->fetchrow_hashref()) {
99 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
100 $projectdescriptions{$ref->{id}} = $ref->{description};
105 $form->{"globalprojectnumber"} =
106 $projectnumbers{$form->{"globalproject_id"}};
107 $form->{"globalprojectdescription"} =
108 $projectdescriptions{$form->{"globalproject_id"}};
115 my %oid = ('Pg' => 'oid',
116 'Oracle' => 'rowid');
118 # sort items by partsgroup
119 for $i (1 .. $form->{rowcount}) {
121 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
122 $partsgroup = $form->{"partsgroup_$i"};
124 push @partsgroup, [$i, $partsgroup];
137 my $nodiscount_subtotal = 0;
138 my $discount_subtotal = 0;
140 my $subtotal_header = 0;
143 $form->{discount} = [];
145 IC->prepare_parts_for_printing();
147 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
150 qw(runningnumber number description longdescription qty ship unit bin
151 deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
152 partnotes serialnumber reqdate sellprice listprice netprice
153 discount p_discount discount_sub nodiscount_sub
154 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
155 price_factor price_factor_name partsgroup);
157 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
159 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
161 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
163 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
165 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
168 if ($item->[1] ne $sameitem) {
169 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
170 $sameitem = $item->[1];
172 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
175 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
177 if ($form->{"id_$i"} != 0) {
179 # add number, description and qty to $form->{number},
180 if ($form->{"subtotal_$i"} && !$subtotal_header) {
181 $subtotal_header = $i;
182 $position = int($position);
185 } elsif ($subtotal_header) {
187 $position = int($position);
188 $position = $position.".".$subposition;
190 $position = int($position);
194 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
196 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
197 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
198 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
204 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
212 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
213 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
216 if ($form->{lizenzen}) {
217 if ($form->{"licensenumber_$i"}) {
218 $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
219 my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
220 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
221 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
224 push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
225 push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
230 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
232 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
233 my ($dec) = ($sellprice =~ /\.(\d+)/);
234 my $decimalplaces = max 2, length($dec);
236 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
237 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
238 my $linetotal = $form->round_amount($linetotal_exact, 2);
239 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
241 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
242 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
244 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
246 $linetotal = ($linetotal != 0) ? $linetotal : '';
248 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
249 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
251 $form->{total} += $linetotal;
252 $form->{nodiscount_total} += $nodiscount_linetotal;
253 $form->{discount_total} += $discount;
255 if ($subtotal_header) {
256 $discount_subtotal += $linetotal;
257 $nodiscount_subtotal += $nodiscount_linetotal;
260 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
261 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
264 $discount_subtotal = 0;
265 $nodiscount_subtotal = 0;
266 $subtotal_header = 0;
269 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
270 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
273 if (!$form->{"discount_$i"}) {
274 $nodiscount += $linetotal;
277 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
280 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
281 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
283 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
287 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
289 if ($form->{taxincluded}) {
292 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
293 $taxbase = $linetotal - $taxamount;
295 $taxamount = $linetotal * $taxrate;
296 $taxbase = $linetotal;
299 if ($form->round_amount($taxrate, 7) == 0) {
300 if ($form->{taxincluded}) {
301 foreach my $accno (@taxaccounts) {
302 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
304 $taxaccounts{$accno} += $taxamount;
305 $taxdiff += $taxamount;
307 $taxbase{$accno} += $taxbase;
309 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
313 $taxbase{$accno} += $taxbase;
317 foreach my $accno (@taxaccounts) {
318 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
319 $taxbase{$accno} += $taxbase;
322 my $tax_rate = $taxrate * 100;
323 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
324 if ($form->{"assembly_$i"}) {
327 # get parts and push them onto the stack
329 if ($form->{groupitems}) {
331 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
333 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
337 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
339 JOIN parts p ON (a.parts_id = p.id)
340 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
341 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
342 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
344 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
345 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
346 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
347 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
348 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
351 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
353 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
354 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
356 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
357 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
363 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
367 foreach my $item (sort keys %taxaccounts) {
368 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
371 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
374 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
377 for my $i (1 .. $form->{paidaccounts}) {
378 if ($form->{"paid_$i"}) {
379 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
382 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
383 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
384 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
385 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
387 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
390 if($form->{taxincluded}) {
391 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
394 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
397 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
398 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
399 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
400 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
402 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
403 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
405 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
406 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
408 $form->set_payment_options($myconfig, $form->{invdate});
410 $form->{username} = $myconfig->{name};
414 $main::lxdebug->leave_sub();
417 sub project_description {
418 $main::lxdebug->enter_sub();
420 my ($self, $dbh, $id) = @_;
421 my $form = \%main::form;
423 my $query = qq|SELECT description FROM project WHERE id = ?|;
424 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
426 $main::lxdebug->leave_sub();
431 sub customer_details {
432 $main::lxdebug->enter_sub();
434 my ($self, $myconfig, $form, @wanted_vars) = @_;
436 # connect to database
437 my $dbh = $form->dbconnect($myconfig);
439 my $language_id = $form->{language_id};
441 # get contact id, set it if nessessary
444 my @values = (conv_i($form->{customer_id}));
447 if ($form->{cp_id}) {
448 $where = qq| AND (cp.cp_id = ?) |;
449 push(@values, conv_i($form->{cp_id}));
452 # get rest for the customer
454 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
455 ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
457 LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
458 WHERE (ct.id = ?) $where
461 my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
463 # remove id and taxincluded before copy back
464 delete @$ref{qw(id taxincluded)};
466 @wanted_vars = grep({ $_ } @wanted_vars);
467 if (scalar(@wanted_vars) > 0) {
469 map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
470 map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
473 map { $form->{$_} = $ref->{$_} } keys %$ref;
475 if ($form->{delivery_customer_id}) {
477 qq|SELECT *, notes as customernotes
481 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
483 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
486 if ($form->{delivery_vendor_id}) {
488 qq|SELECT *, notes as customernotes
492 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
494 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
497 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
499 'trans_id' => $form->{customer_id});
500 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
502 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
503 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
504 'language_id' => $language_id,
505 'allow_fallback' => 1);
510 $main::lxdebug->leave_sub();
514 $main::lxdebug->enter_sub();
516 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
518 # connect to database, turn off autocommit
519 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
521 my ($query, $sth, $null, $project_id, @values);
522 my $exchangerate = 0;
524 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
527 if (!$form->{employee_id}) {
528 $form->get_employee($dbh);
531 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
532 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
533 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
534 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
535 if (!$form->{department_id}){
536 ($null, $form->{department_id}) = split(/--/, $form->{department});
539 my $all_units = AM->retrieve_units($myconfig, $form);
541 if (!$payments_only) {
543 &reverse_invoice($dbh, $form);
546 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
547 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
549 $query = qq|SELECT nextval('glid')|;
550 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
552 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
553 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
555 if (!$form->{invnumber}) {
557 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
558 "cnnumber" : "invnumber", $dbh);
563 my ($netamount, $invoicediff) = (0, 0);
564 my ($amount, $linetotal, $lastincomeaccno);
566 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
567 my $defaultcurrency = (split m/:/, $currencies)[0];
569 if ($form->{currency} eq $defaultcurrency) {
570 $form->{exchangerate} = 1;
572 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
575 $form->{exchangerate} =
578 : $form->parse_amount($myconfig, $form->{exchangerate});
580 $form->{expense_inventory} = "";
584 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
585 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
588 $form->{amount} = {};
589 $form->{amount_cogs} = {};
591 foreach my $i (1 .. $form->{rowcount}) {
592 if ($form->{type} eq "credit_note") {
593 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
594 $form->{shipped} = 1;
596 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
601 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
602 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
603 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
605 if ($form->{storno}) {
606 $form->{"qty_$i"} *= -1;
609 if ($form->{"id_$i"}) {
612 if (defined($baseunits{$form->{"id_$i"}})) {
613 $item_unit = $baseunits{$form->{"id_$i"}};
616 $query = qq|SELECT unit FROM parts WHERE id = ?|;
617 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
618 $baseunits{$form->{"id_$i"}} = $item_unit;
621 if (defined($all_units->{$item_unit}->{factor})
622 && ($all_units->{$item_unit}->{factor} ne '')
623 && ($all_units->{$item_unit}->{factor} != 0)) {
624 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
628 $baseqty = $form->{"qty_$i"} * $basefactor;
630 my ($allocated, $taxrate) = (0, 0);
634 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
636 # keep entered selling price
638 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
640 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
642 my $decimalplaces = ($dec > 2) ? $dec : 2;
644 # undo discount formatting
645 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
648 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
650 # round linetotal to 2 decimal places
651 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
652 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
654 if ($form->{taxincluded}) {
655 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
656 $form->{"sellprice_$i"} =
657 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
659 $taxamount = $linetotal * $taxrate;
662 $netamount += $linetotal;
664 if ($taxamount != 0) {
666 $form->{amount}{ $form->{id} }{$_} +=
667 $taxamount * $form->{"${_}_rate"} / $taxrate
668 } split(/ /, $form->{"taxaccounts_$i"});
671 # add amount to income, $form->{amount}{trans_id}{accno}
672 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
674 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
675 $linetotal = $form->round_amount($linetotal, 2);
677 # this is the difference from the inventory
678 $invoicediff += ($amount - $linetotal);
680 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
683 $lastincomeaccno = $form->{"income_accno_$i"};
685 # adjust and round sellprice
686 $form->{"sellprice_$i"} =
687 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
690 next if $payments_only;
692 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
694 if ($form->{"assembly_$i"}) {
695 # record assembly item as allocated
696 &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
699 $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
703 # get pricegroup_id and save it
704 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
707 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
709 # save detail record in invoice table
711 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
712 sellprice, fxsellprice, discount, allocated, assemblyitem,
713 unit, deliverydate, project_id, serialnumber, pricegroup_id,
714 ordnumber, transdate, cusordnumber, base_qty, subtotal,
715 marge_percent, marge_total, lastcost,
716 price_factor_id, price_factor, marge_price_factor)
717 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
718 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
720 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
721 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
722 $form->{"sellprice_$i"}, $fxsellprice,
723 $form->{"discount_$i"}, $allocated, 'f',
724 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
725 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
726 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
727 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
728 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
729 $form->{"lastcost_$i"},
730 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
731 conv_i($form->{"marge_price_factor_$i"}));
732 do_query($form, $dbh, $query, @values);
734 if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
736 qq|INSERT INTO licenseinvoice (trans_id, license_id)
737 VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
738 @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
739 do_query($form, $dbh, $query, @values);
742 CVar->save_custom_variables(module => 'IC',
743 sub_module => 'invoice',
744 trans_id => $invoice_id,
745 configs => $ic_cvar_configs,
747 name_prefix => 'ic_',
748 name_postfix => "_$i",
753 # total payments, don't move we need it here
754 for my $i (1 .. $form->{paidaccounts}) {
755 if ($form->{type} eq "credit_note") {
756 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
758 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
760 $form->{paid} += $form->{"paid_$i"};
761 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
764 my ($tax, $diff) = (0, 0);
766 $netamount = $form->round_amount($netamount, 2);
768 # figure out rounding errors for total amount vs netamount + taxes
769 if ($form->{taxincluded}) {
771 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
772 $diff += $amount - $netamount * $form->{exchangerate};
773 $netamount = $amount;
775 foreach my $item (split(/ /, $form->{taxaccounts})) {
776 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
777 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
778 $tax += $form->{amount}{ $form->{id} }{$item};
779 $netamount -= $form->{amount}{ $form->{id} }{$item};
782 $invoicediff += $diff;
783 ######## this only applies to tax included
784 if ($lastincomeaccno) {
785 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
789 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
790 $diff = $amount - $netamount * $form->{exchangerate};
791 $netamount = $amount;
792 foreach my $item (split(/ /, $form->{taxaccounts})) {
793 $form->{amount}{ $form->{id} }{$item} =
794 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
797 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
800 $amount - $form->{amount}{ $form->{id} }{$item} *
801 $form->{exchangerate};
802 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
803 $tax += $form->{amount}{ $form->{id} }{$item};
807 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
809 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
812 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
814 # update exchangerate
815 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
816 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
817 $form->{exchangerate}, 0);
820 $project_id = conv_i($form->{"globalproject_id"});
822 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
823 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
824 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
826 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
828 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
830 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
831 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
832 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
833 do_query($form, $dbh, $query, @values);
834 $form->{amount_cogs}{$trans_id}{$accno} = 0;
838 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
839 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
841 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
843 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
844 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
845 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
846 do_query($form, $dbh, $query, @values);
851 foreach my $trans_id (keys %{ $form->{amount} }) {
852 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
853 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
855 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
857 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
859 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
860 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
861 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
862 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
863 do_query($form, $dbh, $query, @values);
864 $form->{amount}{$trans_id}{$accno} = 0;
868 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
869 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
871 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
873 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
874 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
875 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
876 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
877 do_query($form, $dbh, $query, @values);
882 # deduct payment differences from diff
883 for my $i (1 .. $form->{paidaccounts}) {
884 if ($form->{"paid_$i"} != 0) {
886 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
887 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
891 # record payments and offsetting AR
892 if (!$form->{storno}) {
893 for my $i (1 .. $form->{paidaccounts}) {
895 next if ($form->{"paid_$i"} == 0);
897 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
898 $form->{"datepaid_$i"} = $form->{invdate}
899 unless ($form->{"datepaid_$i"});
900 $form->{datepaid} = $form->{"datepaid_$i"};
904 if ($form->{currency} eq $defaultcurrency) {
905 $form->{"exchangerate_$i"} = 1;
907 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
908 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
912 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
914 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
916 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
917 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
918 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
919 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
920 do_query($form, $dbh, $query, @values);
924 $form->{"paid_$i"} *= -1;
927 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
928 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
929 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
930 @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
931 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
932 do_query($form, $dbh, $query, @values);
934 # exchangerate difference
935 $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
936 $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
940 $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
941 $form->{"exchangerate_$i"};
943 $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
946 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
952 # update exchange rate
953 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
954 $form->update_exchangerate($dbh, $form->{currency},
955 $form->{"datepaid_$i"},
956 $form->{"exchangerate_$i"}, 0);
960 } else { # if (!$form->{storno})
961 $form->{marge_total} *= -1;
964 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
966 if ($payments_only) {
967 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
968 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
970 $dbh->commit if !$provided_dbh;
972 $main::lxdebug->leave_sub();
976 # record exchange rate differences and gains/losses
977 foreach my $accno (keys %{ $form->{fx} }) {
978 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
980 ($form->{fx}{$accno}{$transdate} =
981 $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
986 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
987 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
988 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
989 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
990 do_query($form, $dbh, $query, @values);
995 $amount = $netamount + $tax;
998 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
1000 $query = qq|UPDATE ar set
1001 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
1002 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
1003 amount = ?, netamount = ?, paid = ?,
1004 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1005 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1006 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1007 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1008 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1009 cp_id = ?, marge_total = ?, marge_percent = ?,
1010 globalproject_id = ?, delivery_customer_id = ?,
1011 transaction_description = ?, delivery_vendor_id = ?,
1012 donumber = ?, invnumber_for_credit_note = ?
1014 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1015 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1016 $amount, $netamount, $form->{"paid"},
1017 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1018 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1019 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1020 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1021 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1022 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1023 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1024 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1025 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1026 conv_i($form->{"id"}));
1027 do_query($form, $dbh, $query, @values);
1030 if ($form->{storno}) {
1033 paid = paid + amount,
1035 intnotes = ? || intnotes
1037 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1038 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1042 $form->{name} = $form->{customer};
1043 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1045 if (!$form->{shipto_id}) {
1046 $form->add_shipto($dbh, $form->{id}, "AR");
1049 # save printed, emailed and queued
1050 $form->save_status($dbh);
1052 Common::webdav_folder($form);
1054 # Link this record to the records it was created from.
1055 RecordLinks->create_links('dbh' => $dbh,
1057 'from_table' => 'oe',
1058 'from_ids' => $form->{convert_from_oe_ids},
1060 'to_id' => $form->{id},
1062 delete $form->{convert_from_oe_ids};
1064 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1066 if (scalar @convert_from_do_ids) {
1067 DO->close_orders('dbh' => $dbh,
1068 'ids' => \@convert_from_do_ids);
1070 RecordLinks->create_links('dbh' => $dbh,
1072 'from_table' => 'delivery_orders',
1073 'from_ids' => \@convert_from_do_ids,
1075 'to_id' => $form->{id},
1078 delete $form->{convert_from_do_ids};
1080 ARAP->close_orders_if_billed('dbh' => $dbh,
1081 'arap_id' => $form->{id},
1085 $dbh->commit if !$provided_dbh;
1087 $main::lxdebug->leave_sub();
1092 sub _delete_payments {
1093 $main::lxdebug->enter_sub();
1095 my ($self, $form, $dbh) = @_;
1097 my @delete_acc_trans_ids;
1099 # Delete old payment entries from acc_trans.
1101 qq|SELECT acc_trans_id
1103 WHERE (trans_id = ?) AND fx_transaction
1107 SELECT at.acc_trans_id
1109 LEFT JOIN chart c ON (at.chart_id = c.id)
1110 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1111 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1114 qq|SELECT at.acc_trans_id
1116 LEFT JOIN chart c ON (at.chart_id = c.id)
1117 WHERE (trans_id = ?)
1118 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1119 ORDER BY at.acc_trans_id
1121 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1123 if (@delete_acc_trans_ids) {
1124 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1125 do_query($form, $dbh, $query);
1128 $main::lxdebug->leave_sub();
1132 $main::lxdebug->enter_sub();
1134 my ($self, $myconfig, $form, $locale) = @_;
1136 # connect to database, turn off autocommit
1137 my $dbh = $form->dbconnect_noauto($myconfig);
1139 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1141 $old_form = save_form();
1143 # Delete all entries in acc_trans from prior payments.
1144 $self->_delete_payments($form, $dbh);
1146 # Save the new payments the user made before cleaning up $form.
1147 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1149 # Clean up $form so that old content won't tamper the results.
1150 %keep_vars = map { $_, 1 } qw(login password id);
1151 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1153 # Retrieve the invoice from the database.
1154 $self->retrieve_invoice($myconfig, $form);
1156 # Set up the content of $form in the way that IS::post_invoice() expects.
1157 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1159 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1160 $item = $form->{invoice_details}->[$row - 1];
1162 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1164 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1167 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1169 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1171 # Restore the payment options from the user input.
1172 map { $form->{$_} = $payments{$_} } keys %payments;
1174 # Get the AR accno (which is normally done by Form::create_links()).
1178 LEFT JOIN chart c ON (at.chart_id = c.id)
1179 WHERE (trans_id = ?)
1180 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1181 ORDER BY at.acc_trans_id
1184 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1186 # Post the new payments.
1187 $self->post_invoice($myconfig, $form, $dbh, 1);
1189 restore_form($old_form);
1191 my $rc = $dbh->commit();
1194 $main::lxdebug->leave_sub();
1199 sub process_assembly {
1200 $main::lxdebug->enter_sub();
1202 my ($dbh, $form, $id, $totalqty) = @_;
1205 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1206 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1208 JOIN parts p ON (a.parts_id = p.id)
1210 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1212 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1216 $ref->{inventory_accno_id} *= 1;
1217 $ref->{expense_accno_id} *= 1;
1219 # multiply by number of assemblies
1220 $ref->{qty} *= $totalqty;
1222 if ($ref->{assembly}) {
1223 &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
1226 if ($ref->{inventory_accno_id}) {
1227 $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
1231 # save detail record for individual assembly item in invoice table
1233 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1234 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1235 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1236 do_query($form, $dbh, $query, @values);
1242 $main::lxdebug->leave_sub();
1246 $main::lxdebug->enter_sub();
1248 my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
1252 $form->{taxzone_id} *=1;
1253 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1254 my $taxzone_id = $form->{"taxzone_id"} * 1;
1256 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1257 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1258 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1259 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1260 FROM invoice i, parts p
1261 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1262 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1263 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1264 WHERE (i.parts_id = p.id)
1265 AND (i.parts_id = ?)
1266 AND ((i.base_qty + i.allocated) < 0)
1268 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1273 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1274 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1278 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1280 # total expenses and inventory
1281 # sellprice is the cost of the item
1282 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1284 if (!$::lx_office_conf{system}->{eur}) {
1285 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1287 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1288 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1289 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1291 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1292 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1298 last if (($totalqty -= $qty) <= 0);
1303 $main::lxdebug->leave_sub();
1308 sub reverse_invoice {
1309 $main::lxdebug->enter_sub();
1311 my ($dbh, $form) = @_;
1313 # reverse inventory items
1315 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1317 JOIN parts p ON (i.parts_id = p.id)
1318 WHERE i.trans_id = ?|;
1319 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1321 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1323 if ($ref->{inventory_accno_id}) {
1324 # de-allocated purchases
1326 qq|SELECT i.id, i.trans_id, i.allocated
1328 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1329 ORDER BY i.trans_id DESC|;
1330 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1332 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1333 my $qty = $ref->{qty};
1334 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1335 $qty = $inhref->{allocated};
1339 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1341 last if (($ref->{qty} -= $qty) <= 0);
1350 my @values = (conv_i($form->{id}));
1351 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1352 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1354 if ($form->{lizenzen}) {
1356 qq|DELETE FROM licenseinvoice
1357 WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
1358 do_query($form, $dbh, $query, @values);
1361 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1363 $main::lxdebug->leave_sub();
1366 sub delete_invoice {
1367 $main::lxdebug->enter_sub();
1369 my ($self, $myconfig, $form) = @_;
1371 # connect to database
1372 my $dbh = $form->dbconnect_noauto($myconfig);
1374 &reverse_invoice($dbh, $form);
1376 my @values = (conv_i($form->{id}));
1378 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1379 # zurückgesetzt werden. Vgl:
1380 # id | storno | storno_id | paid | amount
1381 #----+--------+-----------+---------+-----------
1382 # 18 | f | | 0.00000 | 119.00000
1384 # 18 | t | | 119.00000 | 119.00000
1386 if($form->{storno}){
1387 # storno_id auslesen und korrigieren
1388 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1389 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1393 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
1395 # delete spool files
1396 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1398 # delete status entries
1399 do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
1401 my $rc = $dbh->commit;
1405 my $spool = $::lx_office_conf{paths}->{spool};
1406 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1409 $main::lxdebug->leave_sub();
1414 sub retrieve_invoice {
1415 $main::lxdebug->enter_sub();
1417 my ($self, $myconfig, $form) = @_;
1419 # connect to database
1420 my $dbh = $form->get_standard_dbh;
1422 my ($sth, $ref, $query);
1424 my $query_transdate = ", current_date AS invdate" if !$form->{id};
1428 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1429 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1430 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1431 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1432 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1433 d.curr AS currencies
1437 $ref = selectfirst_hashref_query($form, $dbh, $query);
1438 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1441 my $id = conv_i($form->{id});
1444 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1448 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1449 a.orddate, a.quodate, a.globalproject_id,
1450 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1451 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1452 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1453 a.employee_id, a.salesman_id, a.payment_id,
1454 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1455 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1456 a.marge_total, a.marge_percent,
1459 LEFT JOIN employee e ON (e.id = a.employee_id)
1461 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1462 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1465 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1468 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1469 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1471 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1473 foreach my $vc (qw(customer vendor)) {
1474 next if !$form->{"delivery_${vc}_id"};
1475 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1478 # get printed, emailed
1479 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1480 $sth = prepare_execute_query($form, $dbh, $query, $id);
1482 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1483 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1484 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1485 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1488 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1490 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1491 : $form->{invdate} ? $dbh->quote($form->{invdate})
1495 my $taxzone_id = $form->{taxzone_id} *= 1;
1496 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1498 # retrieve individual items
1501 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1502 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1503 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1506 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1507 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1508 i.price_factor_id, i.price_factor, i.marge_price_factor,
1509 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
1510 pr.projectnumber, pg.partsgroup, prg.pricegroup
1513 LEFT JOIN parts p ON (i.parts_id = p.id)
1514 LEFT JOIN project pr ON (i.project_id = pr.id)
1515 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1516 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1518 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1519 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1520 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1522 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1524 $sth = prepare_execute_query($form, $dbh, $query, $id);
1526 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1527 # Retrieve custom variables.
1528 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1530 sub_module => 'invoice',
1531 trans_id => $ref->{invoice_id},
1533 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1534 delete $ref->{invoice_id};
1536 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1537 delete($ref->{"part_inventory_accno_id"});
1539 foreach my $type (qw(inventory income expense)) {
1540 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1541 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1542 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1546 # get tax rates and description
1547 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1549 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1550 LEFT JOIN chart c ON (c.id = t.chart_id)
1552 (SELECT tk.tax_id FROM taxkeys tk
1553 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1554 AND startdate <= date($transdate)
1555 ORDER BY startdate DESC LIMIT 1)
1557 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1558 $ref->{taxaccounts} = "";
1560 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1562 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1566 $ref->{taxaccounts} .= "$ptr->{accno} ";
1568 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1569 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1570 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1571 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1572 $form->{taxaccounts} .= "$ptr->{accno} ";
1577 if ($form->{lizenzen}) {
1578 $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
1579 my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
1580 $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
1583 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1585 chop $ref->{taxaccounts};
1586 push @{ $form->{invoice_details} }, $ref;
1591 Common::webdav_folder($form);
1594 my $rc = $dbh->commit;
1596 $main::lxdebug->leave_sub();
1602 $main::lxdebug->enter_sub();
1604 my ($self, $myconfig, $form) = @_;
1606 # connect to database
1607 my $dbh = $form->get_standard_dbh;
1609 my $dateformat = $myconfig->{dateformat};
1610 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1612 my (@values, $duedate, $ref, $query);
1614 if ($form->{invdate}) {
1615 $duedate = "to_date(?, '$dateformat')";
1616 push @values, $form->{invdate};
1618 $duedate = "current_date";
1621 my $cid = conv_i($form->{customer_id});
1624 if ($form->{payment_id}) {
1625 $payment_id = "(pt.id = ?) OR";
1626 push @values, conv_i($form->{payment_id});
1632 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1633 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1634 c.street, c.zipcode, c.city, c.country,
1635 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
1636 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1637 b.discount AS tradediscount, b.description AS business
1639 LEFT JOIN business b ON (b.id = c.business_id)
1640 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1643 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1645 delete $ref->{salesman_id} if !$ref->{salesman_id};
1647 map { $form->{$_} = $ref->{$_} } keys %$ref;
1650 qq|SELECT sum(amount - paid) AS dunning_amount
1652 WHERE (paid < amount)
1653 AND (customer_id = ?)
1654 AND (dunning_config_id IS NOT NULL)|;
1655 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1656 map { $form->{$_} = $ref->{$_} } keys %$ref;
1659 qq|SELECT dnn.dunning_description AS max_dunning_level
1660 FROM dunning_config dnn
1661 WHERE id IN (SELECT dunning_config_id
1663 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1664 ORDER BY dunning_level DESC LIMIT 1|;
1665 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1666 map { $form->{$_} = $ref->{$_} } keys %$ref;
1668 $form->{creditremaining} = $form->{creditlimit};
1669 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1670 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1671 $form->{creditremaining} -= $value;
1675 (SELECT e.buy FROM exchangerate e
1676 WHERE e.curr = o.curr
1677 AND e.transdate = o.transdate)
1679 WHERE o.customer_id = ?
1680 AND o.quotation = '0'
1681 AND o.closed = '0'|;
1682 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1684 while (my ($amount, $exch) = $sth->fetchrow_array) {
1685 $exch = 1 unless $exch;
1686 $form->{creditremaining} -= $amount * $exch;
1690 # get shipto if we did not converted an order or invoice
1691 if (!$form->{shipto}) {
1692 map { delete $form->{$_} }
1693 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1694 shiptostreet shiptozipcode shiptocity shiptocountry
1695 shiptocontact shiptophone shiptofax shiptoemail);
1697 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1698 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1700 map { $form->{$_} = $ref->{$_} } keys %$ref;
1703 # setup last accounts used for this customer
1704 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1706 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1708 JOIN acc_trans ac ON (ac.chart_id = c.id)
1709 JOIN ar a ON (a.id = ac.trans_id)
1710 WHERE a.customer_id = ?
1711 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1712 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1713 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1716 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1717 if ($ref->{category} eq 'I') {
1719 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1721 if ($form->{initial_transdate}) {
1723 qq|SELECT tk.tax_id, t.rate
1725 LEFT JOIN tax t ON tk.tax_id = t.id
1726 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1727 ORDER BY tk.startdate DESC
1729 my ($tax_id, $rate) =
1730 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1731 $form->{initial_transdate});
1732 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1735 if ($ref->{category} eq 'A') {
1736 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1740 $form->{rowcount} = $i if ($i && !$form->{type});
1743 $main::lxdebug->leave_sub();
1747 $main::lxdebug->enter_sub();
1749 my ($self, $myconfig, $form) = @_;
1751 # connect to database
1752 my $dbh = $form->dbconnect($myconfig);
1754 my $i = $form->{rowcount};
1756 my $where = qq|NOT p.obsolete = '1'|;
1759 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1760 my ($table, $field) = split m/\./, $column;
1761 next if !$form->{"${field}_${i}"};
1762 $where .= qq| AND lower(${column}) ILIKE ?|;
1763 push @values, '%' . $form->{"${field}_${i}"} . '%';
1766 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1767 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1768 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1769 push @values, $form->{"partnumber_$i"};
1772 if ($form->{"description_$i"}) {
1773 $where .= qq| ORDER BY p.description|;
1775 $where .= qq| ORDER BY p.partnumber|;
1779 if ($form->{type} eq "invoice") {
1781 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1782 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1786 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1790 my $taxzone_id = $form->{taxzone_id} * 1;
1791 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1795 p.id, p.partnumber, p.description, p.sellprice,
1796 p.listprice, p.inventory_accno_id, p.lastcost,
1798 c1.accno AS inventory_accno,
1799 c1.new_chart_id AS inventory_new_chart,
1800 date($transdate) - c1.valid_from AS inventory_valid,
1802 c2.accno AS income_accno,
1803 c2.new_chart_id AS income_new_chart,
1804 date($transdate) - c2.valid_from AS income_valid,
1806 c3.accno AS expense_accno,
1807 c3.new_chart_id AS expense_new_chart,
1808 date($transdate) - c3.valid_from AS expense_valid,
1810 p.unit, p.assembly, p.bin, p.onhand,
1811 p.notes AS partnotes, p.notes AS longdescription,
1812 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1815 pfac.factor AS price_factor,
1820 LEFT JOIN chart c1 ON
1821 ((SELECT inventory_accno_id
1822 FROM buchungsgruppen
1823 WHERE id = p.buchungsgruppen_id) = c1.id)
1824 LEFT JOIN chart c2 ON
1825 ((SELECT income_accno_id_${taxzone_id}
1826 FROM buchungsgruppen
1827 WHERE id = p.buchungsgruppen_id) = c2.id)
1828 LEFT JOIN chart c3 ON
1829 ((SELECT expense_accno_id_${taxzone_id}
1830 FROM buchungsgruppen
1831 WHERE id = p.buchungsgruppen_id) = c3.id)
1832 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1833 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1835 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1837 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1839 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1840 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1841 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1842 if (!$ref->{inventory_accno_id}) {
1843 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1845 delete($ref->{inventory_accno_id});
1847 foreach my $type (qw(inventory income expense)) {
1848 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1850 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1853 ($ref->{"${type}_accno"},
1854 $ref->{"${type}_new_chart"},
1855 $ref->{"${type}_valid"})
1856 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1860 if ($form->{payment_id} eq "") {
1861 $form->{payment_id} = $form->{part_payment_id};
1864 # get tax rates and description
1865 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1867 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1869 LEFT JOIN chart c ON (c.id = t.chart_id)
1873 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1875 ORDER BY startdate DESC
1878 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1879 my $stw = $dbh->prepare($query);
1880 $stw->execute(@values) || $form->dberror($query);
1882 $ref->{taxaccounts} = "";
1884 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1886 # if ($customertax{$ref->{accno}})
1887 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1891 $ref->{taxaccounts} .= "$ptr->{accno} ";
1893 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1894 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1895 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1896 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1897 $form->{taxaccounts} .= "$ptr->{accno} ";
1903 chop $ref->{taxaccounts};
1904 if ($form->{language_id}) {
1906 qq|SELECT tr.translation, tr.longdescription
1908 WHERE tr.language_id = ? AND tr.parts_id = ?|;
1909 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1910 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1911 if ($translation ne "") {
1912 $ref->{description} = $translation;
1913 $ref->{longdescription} = $longdescription;
1917 qq|SELECT tr.translation, tr.longdescription
1919 WHERE tr.language_id IN
1922 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1925 @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
1926 my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
1927 if ($translation ne "") {
1928 $ref->{description} = $translation;
1929 $ref->{longdescription} = $longdescription;
1934 $ref->{onhand} *= 1;
1936 push @{ $form->{item_list} }, $ref;
1938 if ($form->{lizenzen}) {
1939 if ($ref->{inventory_accno} > 0) {
1943 WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
1944 my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
1945 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1946 push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
1954 foreach my $item (@{ $form->{item_list} }) {
1955 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1956 trans_id => $item->{id},
1960 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1965 $main::lxdebug->leave_sub();
1968 ##########################
1969 # get pricegroups from database
1970 # build up selected pricegroup
1971 # if an exchange rate - change price
1974 sub get_pricegroups_for_parts {
1976 $main::lxdebug->enter_sub();
1978 my ($self, $myconfig, $form) = @_;
1980 my $dbh = $form->dbconnect($myconfig);
1982 $form->{"PRICES"} = {};
1986 my $all_units = AM->retrieve_units($myconfig, $form);
1987 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
1988 $form->{"PRICES"}{$i} = [];
1990 $id = $form->{"id_$i"};
1992 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
1993 $id = $form->{"new_id_$i"};
1996 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
1998 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2000 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2001 my $sellprice = $form->{"sellprice_$i"};
2002 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2003 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2004 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2006 my $price_new = $form->{"price_new_$i"};
2007 my $price_old = $form->{"price_old_$i"};
2009 if (!$form->{"unit_old_$i"}) {
2010 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2011 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2012 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2013 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2016 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2017 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2018 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2020 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2021 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2022 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2023 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2024 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2025 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2026 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2031 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2032 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2033 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2034 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2035 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2039 if (!$form->{"basefactor_$i"}) {
2040 $form->{"basefactor_$i"} = 1;
2046 (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
2047 (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
2057 (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
2059 (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
2060 'selected' AS selected
2063 ORDER BY pricegroup|;
2064 my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
2065 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2067 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2069 $pkr->{selected} = '';
2071 # if there is an exchange rate change price
2072 if (($form->{exchangerate} * 1) != 0) {
2073 $pkr->{price} /= $form->{exchangerate};
2076 $pkr->{price} *= $form->{"basefactor_$i"};
2077 $pkr->{price} *= $basefactor;
2078 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2080 if ($selectedpricegroup_id eq undef) {
2081 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2082 # Case A: open old invoice, no pricegroup selected
2083 # Case B: add new article to invoice, no pricegroup selected
2085 # to distinguish case A and B the variable pricegroup_id_$i is used
2086 # for new articles this variable isn't defined, for loaded articles it is
2087 # sellprice can't be used, as it already has 0,00 set
2089 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2091 $pkr->{selected} = ' selected';
2093 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2094 and not defined $form->{"pricegroup_id_$i"}
2095 and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2096 # for the case where pricegroup prices haven't been set
2098 # Case B: use default pricegroup of customer
2100 $pkr->{selected} = ' selected'; # unless $form->{selected};
2102 # no customer pricesgroup set
2103 if ($pkr->{price} == $pkr->{default_sellprice}) {
2105 $pkr->{price} = $form->{"sellprice_$i"};
2109 # this sub should not set anything and only return. --sschoeling, 20090506
2110 # is this correct? put in again... -- grichardson 20110119
2111 $form->{"sellprice_$i"} = $pkr->{price};
2114 } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2115 $pkr->{price} = $form->{"sellprice_$i"};
2116 $pkr->{selected} = ' selected';
2120 # existing article: pricegroup or price changed
2121 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2122 if ($selectedpricegroup_id ne $pricegroup_old) {
2123 # pricegroup has changed
2124 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2125 $pkr->{selected} = ' selected';
2127 } elsif ( ($form->parse_amount($myconfig, $price_new)
2128 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2129 and ($price_new ne 0) and defined $price_new) {
2130 # sellprice has changed
2131 # when loading existing invoices $price_new is NULL
2132 if ($pkr->{pricegroup_id} == 0) {
2133 $pkr->{price} = $form->{"sellprice_$i"};
2134 $pkr->{selected} = ' selected';
2136 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2137 # neither sellprice nor pricegroup changed
2138 $pkr->{selected} = ' selected';
2139 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2140 # $pkr->{price} = $form->{"sellprice_$i"};
2142 $pkr->{price} = $form->{"sellprice_$i"};
2146 push @{ $form->{PRICES}{$i} }, $pkr;
2149 $form->{"basefactor_$i"} *= $basefactor;
2158 $main::lxdebug->leave_sub();
2162 $main::lxdebug->enter_sub();
2164 my ($self, $myconfig, $form, $table) = @_;
2166 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2168 # make sure there's no funny stuff in $table
2169 # ToDO: die when this happens and throw an error
2170 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2172 my $dbh = $form->dbconnect($myconfig);
2174 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2175 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2179 $main::lxdebug->leave_sub();
2185 $main::lxdebug->enter_sub();
2187 my ($self, $myconfig, $form, $table, $id) = @_;
2189 $main::lxdebug->leave_sub() and return 0 unless ($id);
2191 # make sure there's no funny stuff in $table
2192 # ToDO: die when this happens and throw an error
2193 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2195 my $dbh = $form->dbconnect($myconfig);
2197 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2198 my ($result) = selectrow_query($form, $dbh, $query, $id);
2202 $main::lxdebug->leave_sub();
2207 sub get_standard_accno_current_assets {
2208 $main::lxdebug->enter_sub();
2210 my ($self, $myconfig, $form) = @_;
2212 my $dbh = $form->dbconnect($myconfig);
2214 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2215 my ($result) = selectrow_query($form, $dbh, $query);
2219 $main::lxdebug->leave_sub();