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);
43 use SL::DATEV qw(:CONSTANTS);
46 use SL::GenericTranslations;
56 $main::lxdebug->enter_sub();
58 my ($self, $myconfig, $form, $locale) = @_;
60 $form->{duedate} ||= $form->{invdate};
63 my $dbh = $form->get_standard_dbh;
66 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
67 ($form->{terms}) = selectrow_query($form, $dbh, $query);
69 my (@project_ids, %projectnumbers, %projectdescriptions);
70 $form->{TEMPLATE_ARRAYS} = {};
72 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
74 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
77 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
78 $price_factors{$pfac->{id}} = $pfac;
80 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
83 # sort items by partsgroup
84 for my $i (1 .. $form->{rowcount}) {
86 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
87 # $partsgroup = $form->{"partsgroup_$i"};
89 # push @partsgroup, [$i, $partsgroup];
90 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
94 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
95 join(", ", map({ "?" } @project_ids)) . ")";
96 $sth = $dbh->prepare($query);
97 $sth->execute(@project_ids) ||
98 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
99 while (my $ref = $sth->fetchrow_hashref()) {
100 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
101 $projectdescriptions{$ref->{id}} = $ref->{description};
106 $form->{"globalprojectnumber"} =
107 $projectnumbers{$form->{"globalproject_id"}};
108 $form->{"globalprojectdescription"} =
109 $projectdescriptions{$form->{"globalproject_id"}};
116 my %oid = ('Pg' => 'oid',
117 'Oracle' => 'rowid');
119 # sort items by partsgroup
120 for $i (1 .. $form->{rowcount}) {
122 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
123 $partsgroup = $form->{"partsgroup_$i"};
125 push @partsgroup, [$i, $partsgroup];
138 my $nodiscount_subtotal = 0;
139 my $discount_subtotal = 0;
141 my $subtotal_header = 0;
144 $form->{discount} = [];
146 IC->prepare_parts_for_printing();
148 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
151 qw(runningnumber number description longdescription qty ship unit bin
152 deliverydate_oe ordnumber_oe transdate_oe validuntil
153 partnotes serialnumber reqdate sellprice listprice netprice
154 discount p_discount discount_sub nodiscount_sub
155 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
156 price_factor price_factor_name partsgroup);
158 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
160 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
162 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
164 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
166 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
169 if ($item->[1] ne $sameitem) {
170 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
171 $sameitem = $item->[1];
173 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
176 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
178 if ($form->{"id_$i"} != 0) {
180 # add number, description and qty to $form->{number},
181 if ($form->{"subtotal_$i"} && !$subtotal_header) {
182 $subtotal_header = $i;
183 $position = int($position);
186 } elsif ($subtotal_header) {
188 $position = int($position);
189 $position = $position.".".$subposition;
191 $position = int($position);
195 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
197 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
198 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
199 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
206 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
210 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
211 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
216 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
217 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
218 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
220 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
221 my ($dec) = ($sellprice =~ /\.(\d+)/);
222 my $decimalplaces = max 2, length($dec);
224 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
225 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
226 my $linetotal = $form->round_amount($linetotal_exact, 2);
227 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
229 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
230 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
232 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
233 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
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}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
241 $form->{total} += $linetotal;
242 $form->{nodiscount_total} += $nodiscount_linetotal;
243 $form->{discount_total} += $discount;
245 if ($subtotal_header) {
246 $discount_subtotal += $linetotal;
247 $nodiscount_subtotal += $nodiscount_linetotal;
250 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
251 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
254 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
256 $discount_subtotal = 0;
257 $nodiscount_subtotal = 0;
258 $subtotal_header = 0;
261 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
265 if (!$form->{"discount_$i"}) {
266 $nodiscount += $linetotal;
269 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
270 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
271 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
272 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
274 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
275 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
277 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
281 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
283 if ($form->{taxincluded}) {
286 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
287 $taxbase = $linetotal - $taxamount;
289 $taxamount = $linetotal * $taxrate;
290 $taxbase = $linetotal;
293 if ($form->round_amount($taxrate, 7) == 0) {
294 if ($form->{taxincluded}) {
295 foreach my $accno (@taxaccounts) {
296 $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
298 $taxaccounts{$accno} += $taxamount;
299 $taxdiff += $taxamount;
301 $taxbase{$accno} += $taxbase;
303 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
305 foreach my $accno (@taxaccounts) {
306 $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
307 $taxbase{$accno} += $taxbase;
311 foreach my $accno (@taxaccounts) {
312 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
313 $taxbase{$accno} += $taxbase;
316 my $tax_rate = $taxrate * 100;
317 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
318 if ($form->{"assembly_$i"}) {
321 # get parts and push them onto the stack
323 if ($form->{groupitems}) {
325 qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
327 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
331 qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
333 JOIN parts p ON (a.parts_id = p.id)
334 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
335 WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
336 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
338 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
339 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
340 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
341 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
342 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
345 map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
347 push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
348 $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
350 . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
351 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
357 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
358 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
359 for @{ $ic_cvar_configs };
363 foreach my $item (sort keys %taxaccounts) {
364 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
366 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
368 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
369 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
371 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
373 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
376 for my $i (1 .. $form->{paidaccounts}) {
377 if ($form->{"paid_$i"}) {
378 my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
380 push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
381 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
382 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
383 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
384 push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
386 $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
389 if($form->{taxincluded}) {
390 $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
391 $form->{subtotal_nofmt} = $form->{total} - $tax;
394 $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
395 $form->{subtotal_nofmt} = $form->{total};
398 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
399 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
400 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
401 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
403 $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
404 $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
406 $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
407 $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
409 $form->set_payment_options($myconfig, $form->{invdate});
411 $form->{username} = $myconfig->{name};
413 $main::lxdebug->leave_sub();
416 sub project_description {
417 $main::lxdebug->enter_sub();
419 my ($self, $dbh, $id) = @_;
420 my $form = \%main::form;
422 my $query = qq|SELECT description FROM project WHERE id = ?|;
423 my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
425 $main::lxdebug->leave_sub();
430 sub customer_details {
431 $main::lxdebug->enter_sub();
433 my ($self, $myconfig, $form, @wanted_vars) = @_;
435 # connect to database
436 my $dbh = $form->get_standard_dbh;
438 my $language_id = $form->{language_id};
440 # get contact id, set it if nessessary
443 my @values = (conv_i($form->{customer_id}));
446 if ($form->{cp_id}) {
447 $where = qq| AND (cp.cp_id = ?) |;
448 push(@values, conv_i($form->{cp_id}));
451 # get rest for the customer
453 qq|SELECT ct.*, cp.*, ct.notes as customernotes,
454 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 # remove any trailing whitespace
476 $form->{currency} =~ s/\s*$// if ($form->{currency});
478 if ($form->{delivery_customer_id}) {
480 qq|SELECT *, notes as customernotes
484 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
486 map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
489 if ($form->{delivery_vendor_id}) {
491 qq|SELECT *, notes as customernotes
495 $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
497 map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
500 my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
502 'trans_id' => $form->{customer_id});
503 map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
505 $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
506 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
507 'language_id' => $language_id,
508 'allow_fallback' => 1);
511 $main::lxdebug->leave_sub();
515 $main::lxdebug->enter_sub();
517 my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
519 # connect to database, turn off autocommit
520 my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
522 my ($query, $sth, $null, $project_id, @values);
523 my $exchangerate = 0;
525 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
528 if (!$form->{employee_id}) {
529 $form->get_employee($dbh);
532 $form->{defaultcurrency} = $form->get_default_currency($myconfig);
533 # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
534 # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
535 # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
536 if (!$form->{department_id}){
537 ($null, $form->{department_id}) = split(/--/, $form->{department});
540 my $all_units = AM->retrieve_units($myconfig, $form);
542 if (!$payments_only) {
544 &reverse_invoice($dbh, $form);
547 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
548 $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
550 $query = qq|SELECT nextval('glid')|;
551 ($form->{"id"}) = selectrow_query($form, $dbh, $query);
553 $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
554 do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
556 if (!$form->{invnumber}) {
558 $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
559 "cnnumber" : "invnumber", $dbh);
564 my ($netamount, $invoicediff) = (0, 0);
565 my ($amount, $linetotal, $lastincomeaccno);
567 my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
568 my $defaultcurrency = (split m/:/, $currencies)[0];
570 if ($form->{currency} eq $defaultcurrency) {
571 $form->{exchangerate} = 1;
573 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
576 $form->{exchangerate} =
579 : $form->parse_amount($myconfig, $form->{exchangerate});
581 $form->{expense_inventory} = "";
585 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
586 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
589 $form->{amount} = {};
590 $form->{amount_cogs} = {};
592 foreach my $i (1 .. $form->{rowcount}) {
593 if ($form->{type} eq "credit_note") {
594 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
595 $form->{shipped} = 1;
597 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
602 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
603 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
604 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
606 if ($form->{storno}) {
607 $form->{"qty_$i"} *= -1;
610 if ($form->{"id_$i"}) {
613 if (defined($baseunits{$form->{"id_$i"}})) {
614 $item_unit = $baseunits{$form->{"id_$i"}};
617 $query = qq|SELECT unit FROM parts WHERE id = ?|;
618 ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
619 $baseunits{$form->{"id_$i"}} = $item_unit;
622 if (defined($all_units->{$item_unit}->{factor})
623 && ($all_units->{$item_unit}->{factor} ne '')
624 && ($all_units->{$item_unit}->{factor} != 0)) {
625 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
629 $baseqty = $form->{"qty_$i"} * $basefactor;
631 my ($allocated, $taxrate) = (0, 0);
635 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
637 # keep entered selling price
639 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
641 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
643 my $decimalplaces = ($dec > 2) ? $dec : 2;
645 # undo discount formatting
646 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
649 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
651 # round linetotal to 2 decimal places
652 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
653 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
655 if ($form->{taxincluded}) {
656 $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
657 $form->{"sellprice_$i"} =
658 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
660 $taxamount = $linetotal * $taxrate;
663 $netamount += $linetotal;
665 if ($taxamount != 0) {
667 $form->{amount}{ $form->{id} }{$_} +=
668 $taxamount * $form->{"${_}_rate"} / $taxrate
669 } split(/ /, $form->{"taxaccounts_$i"});
672 # add amount to income, $form->{amount}{trans_id}{accno}
673 $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
675 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
676 $linetotal = $form->round_amount($linetotal, 2);
678 # this is the difference from the inventory
679 $invoicediff += ($amount - $linetotal);
681 $form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
684 $lastincomeaccno = $form->{"income_accno_$i"};
686 # adjust and round sellprice
687 $form->{"sellprice_$i"} =
688 $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
691 next if $payments_only;
693 if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
695 if ($form->{"assembly_$i"}) {
696 # record assembly item as allocated
697 &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
700 $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
704 # get pricegroup_id and save it
705 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
708 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
710 # save detail record in invoice table
712 qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
713 sellprice, fxsellprice, discount, allocated, assemblyitem,
714 unit, deliverydate, project_id, serialnumber, pricegroup_id,
715 ordnumber, transdate, cusordnumber, base_qty, subtotal,
716 marge_percent, marge_total, lastcost,
717 price_factor_id, price_factor, marge_price_factor)
718 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
719 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
721 @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
722 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
723 $form->{"sellprice_$i"}, $fxsellprice,
724 $form->{"discount_$i"}, $allocated, 'f',
725 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
726 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
727 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
728 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
729 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
730 $form->{"lastcost_$i"},
731 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
732 conv_i($form->{"marge_price_factor_$i"}));
733 do_query($form, $dbh, $query, @values);
735 CVar->save_custom_variables(module => 'IC',
736 sub_module => 'invoice',
737 trans_id => $invoice_id,
738 configs => $ic_cvar_configs,
740 name_prefix => 'ic_',
741 name_postfix => "_$i",
746 # total payments, don't move we need it here
747 for my $i (1 .. $form->{paidaccounts}) {
748 if ($form->{type} eq "credit_note") {
749 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
751 $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
753 $form->{paid} += $form->{"paid_$i"};
754 $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
757 my ($tax, $diff) = (0, 0);
759 $netamount = $form->round_amount($netamount, 2);
761 # figure out rounding errors for total amount vs netamount + taxes
762 if ($form->{taxincluded}) {
764 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
765 $diff += $amount - $netamount * $form->{exchangerate};
766 $netamount = $amount;
768 foreach my $item (split(/ /, $form->{taxaccounts})) {
769 $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
770 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
771 $tax += $form->{amount}{ $form->{id} }{$item};
772 $netamount -= $form->{amount}{ $form->{id} }{$item};
775 $invoicediff += $diff;
776 ######## this only applies to tax included
777 if ($lastincomeaccno) {
778 $form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
782 $amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
783 $diff = $amount - $netamount * $form->{exchangerate};
784 $netamount = $amount;
785 foreach my $item (split(/ /, $form->{taxaccounts})) {
786 $form->{amount}{ $form->{id} }{$item} =
787 $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
790 $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
793 $amount - $form->{amount}{ $form->{id} }{$item} *
794 $form->{exchangerate};
795 $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
796 $tax += $form->{amount}{ $form->{id} }{$item};
800 $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
802 $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
805 $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
807 # update exchangerate
808 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
809 $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
810 $form->{exchangerate}, 0);
813 $project_id = conv_i($form->{"globalproject_id"});
815 foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
816 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
817 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
819 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
821 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
823 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
824 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
825 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
826 do_query($form, $dbh, $query, @values);
827 $form->{amount_cogs}{$trans_id}{$accno} = 0;
831 foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
832 $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
834 if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
836 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
837 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
838 @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
839 do_query($form, $dbh, $query, @values);
844 foreach my $trans_id (keys %{ $form->{amount} }) {
845 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
846 next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
848 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
850 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
852 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
853 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
854 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
855 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
856 do_query($form, $dbh, $query, @values);
857 $form->{amount}{$trans_id}{$accno} = 0;
861 foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
862 $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
864 if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
866 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
867 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
868 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
869 @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
870 do_query($form, $dbh, $query, @values);
875 # deduct payment differences from diff
876 for my $i (1 .. $form->{paidaccounts}) {
877 if ($form->{"paid_$i"} != 0) {
879 $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
880 $diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
884 # record payments and offsetting AR
885 if (!$form->{storno}) {
886 for my $i (1 .. $form->{paidaccounts}) {
888 if ($form->{"acc_trans_id_$i"}
890 && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
894 next if ($form->{"paid_$i"} == 0);
896 my ($accno) = split(/--/, $form->{"AR_paid_$i"});
897 $form->{"datepaid_$i"} = $form->{invdate}
898 unless ($form->{"datepaid_$i"});
899 $form->{datepaid} = $form->{"datepaid_$i"};
903 if ($form->{currency} eq $defaultcurrency) {
904 $form->{"exchangerate_$i"} = 1;
906 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
907 $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
911 $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
913 if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
915 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
916 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
917 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
918 @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
919 do_query($form, $dbh, $query, @values);
923 $form->{"paid_$i"} *= -1;
924 my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
927 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, 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 $gldate, $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},
1084 # safety check datev export
1085 if ($::lx_office_conf{datev_check}{check_on_sales_invoice}) {
1086 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1087 $transdate ||= DateTime->today;
1089 my $datev = SL::DATEV->new(
1090 exporttype => DATEV_ET_BUCHUNGEN,
1091 format => DATEV_FORMAT_KNE,
1099 if ($datev->errors) {
1101 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1106 $dbh->commit if !$provided_dbh;
1108 $main::lxdebug->leave_sub();
1113 sub _delete_payments {
1114 $main::lxdebug->enter_sub();
1116 my ($self, $form, $dbh) = @_;
1118 my @delete_acc_trans_ids;
1120 # Delete old payment entries from acc_trans.
1122 qq|SELECT acc_trans_id
1124 WHERE (trans_id = ?) AND fx_transaction
1128 SELECT at.acc_trans_id
1130 LEFT JOIN chart c ON (at.chart_id = c.id)
1131 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1132 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1135 qq|SELECT at.acc_trans_id
1137 LEFT JOIN chart c ON (at.chart_id = c.id)
1138 WHERE (trans_id = ?)
1139 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1140 ORDER BY at.acc_trans_id
1142 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1144 if (@delete_acc_trans_ids) {
1145 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1146 do_query($form, $dbh, $query);
1149 $main::lxdebug->leave_sub();
1153 $main::lxdebug->enter_sub();
1155 my ($self, $myconfig, $form, $locale) = @_;
1157 # connect to database, turn off autocommit
1158 my $dbh = $form->get_standard_dbh;
1161 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1163 $old_form = save_form();
1165 # Delete all entries in acc_trans from prior payments.
1166 if ($::lx_office_conf{features}->{payments_changeable} != 0) {
1167 $self->_delete_payments($form, $dbh);
1170 # Save the new payments the user made before cleaning up $form.
1171 map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
1173 # Clean up $form so that old content won't tamper the results.
1174 %keep_vars = map { $_, 1 } qw(login password id);
1175 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1177 # Retrieve the invoice from the database.
1178 $self->retrieve_invoice($myconfig, $form);
1180 # Set up the content of $form in the way that IS::post_invoice() expects.
1181 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1183 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1184 $item = $form->{invoice_details}->[$row - 1];
1186 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1188 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1191 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1193 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1195 # Restore the payment options from the user input.
1196 map { $form->{$_} = $payments{$_} } keys %payments;
1198 # Get the AR accno (which is normally done by Form::create_links()).
1202 LEFT JOIN chart c ON (at.chart_id = c.id)
1203 WHERE (trans_id = ?)
1204 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1205 ORDER BY at.acc_trans_id
1208 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1210 # Post the new payments.
1211 $self->post_invoice($myconfig, $form, $dbh, 1);
1213 restore_form($old_form);
1215 my $rc = $dbh->commit();
1217 $main::lxdebug->leave_sub();
1222 sub process_assembly {
1223 $main::lxdebug->enter_sub();
1225 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1228 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1229 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1231 JOIN parts p ON (a.parts_id = p.id)
1233 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1235 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1239 $ref->{inventory_accno_id} *= 1;
1240 $ref->{expense_accno_id} *= 1;
1242 # multiply by number of assemblies
1243 $ref->{qty} *= $totalqty;
1245 if ($ref->{assembly}) {
1246 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1249 if ($ref->{inventory_accno_id}) {
1250 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1254 # save detail record for individual assembly item in invoice table
1256 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1257 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1258 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1259 do_query($form, $dbh, $query, @values);
1265 $main::lxdebug->leave_sub();
1269 $main::lxdebug->enter_sub();
1271 # adjust allocated in table invoice according to FIFO princicple
1272 # for a certain part with part_id $id
1274 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1278 $form->{taxzone_id} *=1;
1279 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1280 my $taxzone_id = $form->{"taxzone_id"} * 1;
1282 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1283 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1284 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1285 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1286 FROM invoice i, parts p
1287 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1288 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1289 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1290 WHERE (i.parts_id = p.id)
1291 AND (i.parts_id = ?)
1292 AND ((i.base_qty + i.allocated) < 0)
1294 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1299 # all invoice entries of an example part:
1301 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1302 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1303 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1304 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1305 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1306 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1307 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1309 # AND ((i.base_qty + i.allocated) < 0) filters out all but line with id=7, elsewhere i.base_qty + i.allocated has already reached 0
1310 # and all parts have been allocated
1312 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1313 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1315 # in this example there are still 4 unsold articles
1318 # search all invoice entries for the part in question, adjusting "allocated"
1319 # until the total number of sold parts has been reached
1321 # ORDER BY trans_id ensures FIFO
1324 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1325 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1329 # update allocated in invoice
1330 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1332 # total expenses and inventory
1333 # sellprice is the cost of the item
1334 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1336 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1337 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1338 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1340 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1341 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1342 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1344 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1345 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1351 last if (($totalqty -= $qty) <= 0);
1356 $main::lxdebug->leave_sub();
1361 sub reverse_invoice {
1362 $main::lxdebug->enter_sub();
1364 my ($dbh, $form) = @_;
1366 # reverse inventory items
1368 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1370 JOIN parts p ON (i.parts_id = p.id)
1371 WHERE i.trans_id = ?|;
1372 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1374 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1376 if ($ref->{inventory_accno_id}) {
1377 # de-allocated purchases
1379 qq|SELECT i.id, i.trans_id, i.allocated
1381 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1382 ORDER BY i.trans_id DESC|;
1383 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1385 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1386 my $qty = $ref->{qty};
1387 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1388 $qty = $inhref->{allocated};
1392 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1394 last if (($ref->{qty} -= $qty) <= 0);
1403 my @values = (conv_i($form->{id}));
1404 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1405 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1406 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1408 $main::lxdebug->leave_sub();
1411 sub delete_invoice {
1412 $main::lxdebug->enter_sub();
1414 my ($self, $myconfig, $form) = @_;
1416 # connect to database
1417 my $dbh = $form->get_standard_dbh;
1420 &reverse_invoice($dbh, $form);
1422 my @values = (conv_i($form->{id}));
1424 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1425 # zurückgesetzt werden. Vgl:
1426 # id | storno | storno_id | paid | amount
1427 #----+--------+-----------+---------+-----------
1428 # 18 | f | | 0.00000 | 119.00000
1430 # 18 | t | | 119.00000 | 119.00000
1432 if($form->{storno}){
1433 # storno_id auslesen und korrigieren
1434 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1435 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1438 # delete spool files
1439 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1442 qq|DELETE FROM status WHERE trans_id = ?|,
1443 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1444 qq|DELETE FROM ar WHERE id = ?|,
1447 map { do_query($form, $dbh, $_, @values) } @queries;
1449 my $rc = $dbh->commit;
1452 my $spool = $::lx_office_conf{paths}->{spool};
1453 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1456 $main::lxdebug->leave_sub();
1461 sub retrieve_invoice {
1462 $main::lxdebug->enter_sub();
1464 my ($self, $myconfig, $form) = @_;
1466 # connect to database
1467 my $dbh = $form->get_standard_dbh;
1469 my ($sth, $ref, $query);
1471 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1475 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1476 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1477 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1478 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1479 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1480 d.curr AS currencies
1484 $ref = selectfirst_hashref_query($form, $dbh, $query);
1485 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1488 my $id = conv_i($form->{id});
1491 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1495 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1496 a.orddate, a.quodate, a.globalproject_id,
1497 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1498 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1499 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1500 a.employee_id, a.salesman_id, a.payment_id,
1501 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1502 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1503 a.marge_total, a.marge_percent,
1506 LEFT JOIN employee e ON (e.id = a.employee_id)
1508 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1509 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1511 # remove any trailing whitespace
1512 $form->{currency} =~ s/\s*$//;
1514 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1517 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1518 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1520 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1522 foreach my $vc (qw(customer vendor)) {
1523 next if !$form->{"delivery_${vc}_id"};
1524 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1527 # get printed, emailed
1528 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1529 $sth = prepare_execute_query($form, $dbh, $query, $id);
1531 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1532 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1533 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1534 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1537 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1539 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1540 : $form->{invdate} ? $dbh->quote($form->{invdate})
1544 my $taxzone_id = $form->{taxzone_id} *= 1;
1545 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1547 # retrieve individual items
1550 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1551 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1552 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1555 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1556 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1557 i.price_factor_id, i.price_factor, i.marge_price_factor,
1558 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1559 pr.projectnumber, pg.partsgroup, prg.pricegroup
1562 LEFT JOIN parts p ON (i.parts_id = p.id)
1563 LEFT JOIN project pr ON (i.project_id = pr.id)
1564 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1565 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1567 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1568 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1569 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1571 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1573 $sth = prepare_execute_query($form, $dbh, $query, $id);
1575 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1576 # Retrieve custom variables.
1577 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1579 sub_module => 'invoice',
1580 trans_id => $ref->{invoice_id},
1582 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1583 delete $ref->{invoice_id};
1585 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1586 delete($ref->{"part_inventory_accno_id"});
1588 foreach my $type (qw(inventory income expense)) {
1589 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1590 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1591 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1595 # get tax rates and description
1596 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1598 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1599 LEFT JOIN chart c ON (c.id = t.chart_id)
1601 (SELECT tk.tax_id FROM taxkeys tk
1602 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1603 AND startdate <= date($transdate)
1604 ORDER BY startdate DESC LIMIT 1)
1606 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1607 $ref->{taxaccounts} = "";
1609 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1611 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1615 $ref->{taxaccounts} .= "$ptr->{accno} ";
1617 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1618 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1619 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1620 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1621 $form->{taxaccounts} .= "$ptr->{accno} ";
1626 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1628 chop $ref->{taxaccounts};
1629 push @{ $form->{invoice_details} }, $ref;
1634 Common::webdav_folder($form);
1637 my $rc = $dbh->commit;
1639 $main::lxdebug->leave_sub();
1645 $main::lxdebug->enter_sub();
1647 my ($self, $myconfig, $form) = @_;
1649 # connect to database
1650 my $dbh = $form->get_standard_dbh;
1652 my $dateformat = $myconfig->{dateformat};
1653 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1655 my (@values, $duedate, $ref, $query);
1657 if ($form->{invdate}) {
1658 $duedate = "to_date(?, '$dateformat')";
1659 push @values, $form->{invdate};
1661 $duedate = "current_date";
1664 my $cid = conv_i($form->{customer_id});
1667 if ($form->{payment_id}) {
1668 $payment_id = "(pt.id = ?) OR";
1669 push @values, conv_i($form->{payment_id});
1675 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1676 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1677 c.street, c.zipcode, c.city, c.country,
1678 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1679 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1680 b.discount AS tradediscount, b.description AS business
1682 LEFT JOIN business b ON (b.id = c.business_id)
1683 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1686 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1688 delete $ref->{salesman_id} if !$ref->{salesman_id};
1690 map { $form->{$_} = $ref->{$_} } keys %$ref;
1692 # remove any trailing whitespace
1693 $form->{curr} =~ s/\s*$//;
1695 # use customer currency if not empty
1696 $form->{currency} = $form->{curr} if $form->{curr};
1699 qq|SELECT sum(amount - paid) AS dunning_amount
1701 WHERE (paid < amount)
1702 AND (customer_id = ?)
1703 AND (dunning_config_id IS NOT NULL)|;
1704 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1705 map { $form->{$_} = $ref->{$_} } keys %$ref;
1708 qq|SELECT dnn.dunning_description AS max_dunning_level
1709 FROM dunning_config dnn
1710 WHERE id IN (SELECT dunning_config_id
1712 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1713 ORDER BY dunning_level DESC LIMIT 1|;
1714 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1715 map { $form->{$_} = $ref->{$_} } keys %$ref;
1717 $form->{creditremaining} = $form->{creditlimit};
1718 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1719 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1720 $form->{creditremaining} -= $value;
1724 (SELECT e.buy FROM exchangerate e
1725 WHERE e.curr = o.curr
1726 AND e.transdate = o.transdate)
1728 WHERE o.customer_id = ?
1729 AND o.quotation = '0'
1730 AND o.closed = '0'|;
1731 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1733 while (my ($amount, $exch) = $sth->fetchrow_array) {
1734 $exch = 1 unless $exch;
1735 $form->{creditremaining} -= $amount * $exch;
1739 # get shipto if we did not converted an order or invoice
1740 if (!$form->{shipto}) {
1741 map { delete $form->{$_} }
1742 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1743 shiptostreet shiptozipcode shiptocity shiptocountry
1744 shiptocontact shiptophone shiptofax shiptoemail);
1746 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1747 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1749 map { $form->{$_} = $ref->{$_} } keys %$ref;
1752 # setup last accounts used for this customer
1753 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1755 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1757 JOIN acc_trans ac ON (ac.chart_id = c.id)
1758 JOIN ar a ON (a.id = ac.trans_id)
1759 WHERE a.customer_id = ?
1760 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1761 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1762 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1765 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1766 if ($ref->{category} eq 'I') {
1768 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1770 if ($form->{initial_transdate}) {
1772 qq|SELECT tk.tax_id, t.rate
1774 LEFT JOIN tax t ON tk.tax_id = t.id
1775 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1776 ORDER BY tk.startdate DESC
1778 my ($tax_id, $rate) =
1779 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1780 $form->{initial_transdate});
1781 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1784 if ($ref->{category} eq 'A') {
1785 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1789 $form->{rowcount} = $i if ($i && !$form->{type});
1792 $main::lxdebug->leave_sub();
1796 $main::lxdebug->enter_sub();
1798 my ($self, $myconfig, $form) = @_;
1800 # connect to database
1801 my $dbh = $form->get_standard_dbh;
1803 my $i = $form->{rowcount};
1805 my $where = qq|NOT p.obsolete = '1'|;
1808 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1809 my ($table, $field) = split m/\./, $column;
1810 next if !$form->{"${field}_${i}"};
1811 $where .= qq| AND lower(${column}) ILIKE ?|;
1812 push @values, '%' . $form->{"${field}_${i}"} . '%';
1815 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1816 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1817 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1818 push @values, $form->{"partnumber_$i"};
1821 # Search for part ID overrides all other criteria.
1822 if ($form->{"id_${i}"}) {
1823 $where = qq|p.id = ?|;
1824 @values = ($form->{"id_${i}"});
1827 if ($form->{"description_$i"}) {
1828 $where .= qq| ORDER BY p.description|;
1830 $where .= qq| ORDER BY p.partnumber|;
1834 if ($form->{type} eq "invoice") {
1836 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1837 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1841 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1845 my $taxzone_id = $form->{taxzone_id} * 1;
1846 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1850 p.id, p.partnumber, p.description, p.sellprice,
1851 p.listprice, p.inventory_accno_id, p.lastcost,
1853 c1.accno AS inventory_accno,
1854 c1.new_chart_id AS inventory_new_chart,
1855 date($transdate) - c1.valid_from AS inventory_valid,
1857 c2.accno AS income_accno,
1858 c2.new_chart_id AS income_new_chart,
1859 date($transdate) - c2.valid_from AS income_valid,
1861 c3.accno AS expense_accno,
1862 c3.new_chart_id AS expense_new_chart,
1863 date($transdate) - c3.valid_from AS expense_valid,
1865 p.unit, p.assembly, p.bin, p.onhand,
1866 p.notes AS partnotes, p.notes AS longdescription,
1867 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1870 pfac.factor AS price_factor,
1875 LEFT JOIN chart c1 ON
1876 ((SELECT inventory_accno_id
1877 FROM buchungsgruppen
1878 WHERE id = p.buchungsgruppen_id) = c1.id)
1879 LEFT JOIN chart c2 ON
1880 ((SELECT income_accno_id_${taxzone_id}
1881 FROM buchungsgruppen
1882 WHERE id = p.buchungsgruppen_id) = c2.id)
1883 LEFT JOIN chart c3 ON
1884 ((SELECT expense_accno_id_${taxzone_id}
1885 FROM buchungsgruppen
1886 WHERE id = p.buchungsgruppen_id) = c3.id)
1887 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1888 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1890 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1892 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1894 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1895 [ qq|SELECT tr.translation, tr.longdescription
1897 WHERE tr.language_id IN
1900 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1903 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1905 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1907 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1908 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1909 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1910 if (!$ref->{inventory_accno_id}) {
1911 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1913 delete($ref->{inventory_accno_id});
1915 foreach my $type (qw(inventory income expense)) {
1916 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1918 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1921 ($ref->{"${type}_accno"},
1922 $ref->{"${type}_new_chart"},
1923 $ref->{"${type}_valid"})
1924 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1928 if ($form->{payment_id} eq "") {
1929 $form->{payment_id} = $form->{part_payment_id};
1932 # get tax rates and description
1933 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1935 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1937 LEFT JOIN chart c ON (c.id = t.chart_id)
1941 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1943 ORDER BY startdate DESC
1946 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1947 my $stw = $dbh->prepare($query);
1948 $stw->execute(@values) || $form->dberror($query);
1950 $ref->{taxaccounts} = "";
1952 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1954 # if ($customertax{$ref->{accno}})
1955 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1959 $ref->{taxaccounts} .= "$ptr->{accno} ";
1961 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1962 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1963 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1964 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1965 $form->{taxaccounts} .= "$ptr->{accno} ";
1971 chop $ref->{taxaccounts};
1973 if ($form->{language_id}) {
1974 for my $spec (@translation_queries) {
1975 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1976 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1977 next unless $translation;
1978 $ref->{description} = $translation;
1979 $ref->{longdescription} = $longdescription;
1984 $ref->{onhand} *= 1;
1986 push @{ $form->{item_list} }, $ref;
1989 $_->[1]->finish for @translation_queries;
1991 foreach my $item (@{ $form->{item_list} }) {
1992 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1993 trans_id => $item->{id},
1997 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
2000 $main::lxdebug->leave_sub();
2003 ##########################
2004 # get pricegroups from database
2005 # build up selected pricegroup
2006 # if an exchange rate - change price
2009 sub get_pricegroups_for_parts {
2011 $main::lxdebug->enter_sub();
2013 my ($self, $myconfig, $form) = @_;
2015 my $dbh = $form->get_standard_dbh;
2017 $form->{"PRICES"} = {};
2021 my $all_units = AM->retrieve_units($myconfig, $form);
2022 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2023 $form->{"PRICES"}{$i} = [];
2025 $id = $form->{"id_$i"};
2027 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2028 $id = $form->{"new_id_$i"};
2031 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2033 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2035 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2036 my $sellprice = $form->{"sellprice_$i"};
2037 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2038 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2039 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2041 my $price_new = $form->{"price_new_$i"};
2042 my $price_old = $form->{"price_old_$i"};
2044 if (!$form->{"unit_old_$i"}) {
2045 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2046 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2047 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2048 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2051 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2052 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2053 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2055 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2056 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2057 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2058 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2059 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2060 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2061 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2066 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2067 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2068 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2069 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2070 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2074 if (!$form->{"basefactor_$i"}) {
2075 $form->{"basefactor_$i"} = 1;
2081 sellprice AS default_sellprice,
2084 'selected' AS selected
2090 parts.sellprice AS default_sellprice,
2091 pricegroup.pricegroup,
2095 LEFT JOIN parts ON parts.id = parts_id
2096 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2098 ORDER BY pricegroup|;
2099 my @values = (conv_i($id), conv_i($id));
2100 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2102 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2104 $pkr->{selected} = '';
2106 # if there is an exchange rate change price
2107 if (($form->{exchangerate} * 1) != 0) {
2108 $pkr->{price} /= $form->{exchangerate};
2111 $pkr->{price} *= $form->{"basefactor_$i"};
2112 $pkr->{price} *= $basefactor;
2113 $pkr->{price_ufmt} = $pkr->{price};
2114 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2116 if (!defined $selectedpricegroup_id) {
2117 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2118 # Case A: open old invoice, no pricegroup selected
2119 # Case B: add new article to invoice, no pricegroup selected
2121 # to distinguish case A and B the variable pricegroup_id_$i is used
2122 # for new articles this variable isn't defined, for loaded articles it is
2123 # sellprice can't be used, as it already has 0,00 set
2125 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2127 $pkr->{selected} = ' selected';
2128 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2129 and not defined $form->{"pricegroup_id_$i"}
2130 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2131 # for the case where pricegroup prices haven't been set
2133 # Case B: use default pricegroup of customer
2135 $pkr->{selected} = ' selected'; # unless $form->{selected};
2136 # no customer pricesgroup set
2137 if ($pkr->{price_unfmt} == $pkr->{default_sellprice}) {
2139 $pkr->{price} = $form->{"sellprice_$i"};
2143 # this sub should not set anything and only return. --sschoeling, 20090506
2144 # is this correct? put in again... -- grichardson 20110119
2145 $form->{"sellprice_$i"} = $pkr->{price};
2148 } elsif ($pkr->{price_unfmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2149 $pkr->{price} = $form->{"sellprice_$i"};
2150 $pkr->{selected} = ' selected';
2154 # existing article: pricegroup or price changed
2155 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2156 if ($selectedpricegroup_id ne $pricegroup_old) {
2157 # pricegroup has changed
2158 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2159 $pkr->{selected} = ' selected';
2161 } elsif ( ($form->parse_amount($myconfig, $price_new)
2162 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2163 and ($price_new ne 0) and defined $price_new) {
2164 # sellprice has changed
2165 # when loading existing invoices $price_new is NULL
2166 if ($pkr->{pricegroup_id} == 0) {
2167 $pkr->{price} = $form->{"sellprice_$i"};
2168 $pkr->{selected} = ' selected';
2170 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2171 # neither sellprice nor pricegroup changed
2172 $pkr->{selected} = ' selected';
2173 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2174 # $pkr->{price} = $form->{"sellprice_$i"};
2176 $pkr->{price} = $form->{"sellprice_$i"};
2180 push @{ $form->{PRICES}{$i} }, $pkr;
2183 $form->{"basefactor_$i"} *= $basefactor;
2190 $main::lxdebug->leave_sub();
2194 $main::lxdebug->enter_sub();
2196 my ($self, $myconfig, $form, $table) = @_;
2198 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2200 # make sure there's no funny stuff in $table
2201 # ToDO: die when this happens and throw an error
2202 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2204 my $dbh = $form->get_standard_dbh;
2206 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2207 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2209 $main::lxdebug->leave_sub();
2215 $main::lxdebug->enter_sub();
2217 my ($self, $myconfig, $form, $table, $id) = @_;
2219 $main::lxdebug->leave_sub() and return 0 unless ($id);
2221 # make sure there's no funny stuff in $table
2222 # ToDO: die when this happens and throw an error
2223 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2225 my $dbh = $form->get_standard_dbh;
2227 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2228 my ($result) = selectrow_query($form, $dbh, $query, $id);
2230 $main::lxdebug->leave_sub();
2235 sub get_standard_accno_current_assets {
2236 $main::lxdebug->enter_sub();
2238 my ($self, $myconfig, $form) = @_;
2240 my $dbh = $form->get_standard_dbh;
2242 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2243 my ($result) = selectrow_query($form, $dbh, $query);
2245 $main::lxdebug->leave_sub();