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;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form, $locale) = @_;
61 $form->{duedate} ||= $form->{invdate};
64 my $dbh = $form->get_standard_dbh;
67 my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
68 ($form->{terms}) = selectrow_query($form, $dbh, $query);
70 my (@project_ids, %projectnumbers, %projectdescriptions);
71 $form->{TEMPLATE_ARRAYS} = {};
73 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
75 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
78 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
79 $price_factors{$pfac->{id}} = $pfac;
81 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
84 # sort items by partsgroup
85 for my $i (1 .. $form->{rowcount}) {
87 # if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
88 # $partsgroup = $form->{"partsgroup_$i"};
90 # push @partsgroup, [$i, $partsgroup];
91 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
95 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
96 join(", ", map({ "?" } @project_ids)) . ")";
97 $sth = $dbh->prepare($query);
98 $sth->execute(@project_ids) ||
99 $form->dberror($query . " (" . join(", ", @project_ids) . ")");
100 while (my $ref = $sth->fetchrow_hashref()) {
101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
102 $projectdescriptions{$ref->{id}} = $ref->{description};
107 $form->{"globalprojectnumber"} =
108 $projectnumbers{$form->{"globalproject_id"}};
109 $form->{"globalprojectdescription"} =
110 $projectdescriptions{$form->{"globalproject_id"}};
117 my %oid = ('Pg' => 'oid',
118 'Oracle' => 'rowid');
120 # sort items by partsgroup
121 for $i (1 .. $form->{rowcount}) {
123 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
124 $partsgroup = $form->{"partsgroup_$i"};
126 push @partsgroup, [$i, $partsgroup];
139 my $nodiscount_subtotal = 0;
140 my $discount_subtotal = 0;
142 my $subtotal_header = 0;
145 $form->{discount} = [];
147 IC->prepare_parts_for_printing();
149 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
152 qw(runningnumber number description longdescription qty ship unit bin
153 deliverydate_oe ordnumber_oe transdate_oe validuntil
154 partnotes serialnumber reqdate sellprice listprice netprice
155 discount p_discount discount_sub nodiscount_sub
156 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
157 price_factor price_factor_name partsgroup);
159 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
163 my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
165 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
170 if ($item->[1] ne $sameitem) {
171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
172 $sameitem = $item->[1];
174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
179 if ($form->{"id_$i"} != 0) {
181 # add number, description and qty to $form->{number},
182 if ($form->{"subtotal_$i"} && !$subtotal_header) {
183 $subtotal_header = $i;
184 $position = int($position);
187 } elsif ($subtotal_header) {
189 $position = int($position);
190 $position = $position.".".$subposition;
192 $position = int($position);
196 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
198 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
199 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
200 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
201 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
202 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
203 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
204 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
205 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
206 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
208 push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"};
209 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
210 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
211 push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
212 push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
213 push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
214 push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
218 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
219 push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
221 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
222 my ($dec) = ($sellprice =~ /\.(\d+)/);
223 my $decimalplaces = max 2, length($dec);
225 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
226 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
227 my $linetotal = $form->round_amount($linetotal_exact, 2);
228 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
230 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
231 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
233 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
234 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
236 $linetotal = ($linetotal != 0) ? $linetotal : '';
238 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
239 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
240 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
242 $form->{total} += $linetotal;
243 $form->{nodiscount_total} += $nodiscount_linetotal;
244 $form->{discount_total} += $discount;
246 if ($subtotal_header) {
247 $discount_subtotal += $linetotal;
248 $nodiscount_subtotal += $nodiscount_linetotal;
251 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
253 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
254 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
255 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
257 $discount_subtotal = 0;
258 $nodiscount_subtotal = 0;
259 $subtotal_header = 0;
262 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
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 && (SL::DB::Default->get->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"} } += $amount;
945 $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
950 # update exchange rate
951 if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
952 $form->update_exchangerate($dbh, $form->{currency},
953 $form->{"datepaid_$i"},
954 $form->{"exchangerate_$i"}, 0);
958 } else { # if (!$form->{storno})
959 $form->{marge_total} *= -1;
962 IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
964 # record exchange rate differences and gains/losses
965 foreach my $accno (keys %{ $form->{fx} }) {
966 foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
967 $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
968 if ( $form->{fx}{$accno}{$transdate} != 0 ) {
971 qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
972 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
973 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
974 @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_i($project_id));
975 do_query($form, $dbh, $query, @values);
980 if ($payments_only) {
981 $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
982 do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
984 $dbh->commit if !$provided_dbh;
986 $main::lxdebug->leave_sub();
990 $amount = $netamount + $tax;
993 #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
995 $query = qq|UPDATE ar set
996 invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
997 transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
998 amount = ?, netamount = ?, paid = ?,
999 duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
1000 shipvia = ?, terms = ?, notes = ?, intnotes = ?,
1001 curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
1002 type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
1003 employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
1004 cp_id = ?, marge_total = ?, marge_percent = ?,
1005 globalproject_id = ?, delivery_customer_id = ?,
1006 transaction_description = ?, delivery_vendor_id = ?,
1007 donumber = ?, invnumber_for_credit_note = ?
1009 @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
1010 conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
1011 $amount, $netamount, $form->{"paid"},
1012 conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
1013 $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
1014 $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
1015 $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
1016 conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
1017 conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
1018 conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
1019 $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
1020 $form->{"donumber"}, $form->{"invnumber_for_credit_note"},
1021 conv_i($form->{"id"}));
1022 do_query($form, $dbh, $query, @values);
1025 if ($form->{storno}) {
1028 paid = paid + amount,
1030 intnotes = ? || intnotes
1032 do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
1033 do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
1037 $form->{name} = $form->{customer};
1038 $form->{name} =~ s/--\Q$form->{customer_id}\E//;
1040 if (!$form->{shipto_id}) {
1041 $form->add_shipto($dbh, $form->{id}, "AR");
1044 # save printed, emailed and queued
1045 $form->save_status($dbh);
1047 Common::webdav_folder($form);
1049 # Link this record to the records it was created from.
1050 RecordLinks->create_links('dbh' => $dbh,
1052 'from_table' => 'oe',
1053 'from_ids' => $form->{convert_from_oe_ids},
1055 'to_id' => $form->{id},
1057 delete $form->{convert_from_oe_ids};
1059 my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
1061 if (scalar @convert_from_do_ids) {
1062 DO->close_orders('dbh' => $dbh,
1063 'ids' => \@convert_from_do_ids);
1065 RecordLinks->create_links('dbh' => $dbh,
1067 'from_table' => 'delivery_orders',
1068 'from_ids' => \@convert_from_do_ids,
1070 'to_id' => $form->{id},
1073 delete $form->{convert_from_do_ids};
1075 ARAP->close_orders_if_billed('dbh' => $dbh,
1076 'arap_id' => $form->{id},
1079 # safety check datev export
1080 if ($::instance_conf->get_datev_check_on_sales_invoice) {
1081 my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
1082 $transdate ||= DateTime->today;
1084 my $datev = SL::DATEV->new(
1085 exporttype => DATEV_ET_BUCHUNGEN,
1086 format => DATEV_FORMAT_KNE,
1094 if ($datev->errors) {
1096 die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
1101 $dbh->commit if !$provided_dbh;
1103 $main::lxdebug->leave_sub();
1108 sub _delete_payments {
1109 $main::lxdebug->enter_sub();
1111 my ($self, $form, $dbh) = @_;
1113 my @delete_acc_trans_ids;
1115 # Delete old payment entries from acc_trans.
1117 qq|SELECT acc_trans_id
1119 WHERE (trans_id = ?) AND fx_transaction
1123 SELECT at.acc_trans_id
1125 LEFT JOIN chart c ON (at.chart_id = c.id)
1126 WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
1127 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
1130 qq|SELECT at.acc_trans_id
1132 LEFT JOIN chart c ON (at.chart_id = c.id)
1133 WHERE (trans_id = ?)
1134 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1135 ORDER BY at.acc_trans_id
1137 push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
1139 if (@delete_acc_trans_ids) {
1140 $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
1141 do_query($form, $dbh, $query);
1144 $main::lxdebug->leave_sub();
1148 $main::lxdebug->enter_sub();
1150 my ($self, $myconfig, $form, $locale) = @_;
1152 # connect to database, turn off autocommit
1153 my $dbh = $form->get_standard_dbh;
1156 my (%payments, $old_form, $row, $item, $query, %keep_vars);
1158 $old_form = save_form();
1160 # Delete all entries in acc_trans from prior payments.
1161 if (SL::DB::Default->get->payments_changeable != 0) {
1162 $self->_delete_payments($form, $dbh);
1165 # Save the new payments the user made before cleaning up $form.
1166 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 };
1168 # Clean up $form so that old content won't tamper the results.
1169 %keep_vars = map { $_, 1 } qw(login password id);
1170 map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
1172 # Retrieve the invoice from the database.
1173 $self->retrieve_invoice($myconfig, $form);
1175 # Set up the content of $form in the way that IS::post_invoice() expects.
1176 $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
1178 for $row (1 .. scalar @{ $form->{invoice_details} }) {
1179 $item = $form->{invoice_details}->[$row - 1];
1181 map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);
1183 map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
1186 $form->{rowcount} = scalar @{ $form->{invoice_details} };
1188 delete @{$form}{qw(invoice_details paidaccounts storno paid)};
1190 # Restore the payment options from the user input.
1191 map { $form->{$_} = $payments{$_} } keys %payments;
1193 # Get the AR accno (which is normally done by Form::create_links()).
1197 LEFT JOIN chart c ON (at.chart_id = c.id)
1198 WHERE (trans_id = ?)
1199 AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
1200 ORDER BY at.acc_trans_id
1203 ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
1205 # Post the new payments.
1206 $self->post_invoice($myconfig, $form, $dbh, 1);
1208 restore_form($old_form);
1210 my $rc = $dbh->commit();
1212 $main::lxdebug->leave_sub();
1217 sub process_assembly {
1218 $main::lxdebug->enter_sub();
1220 my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
1223 qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
1224 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1226 JOIN parts p ON (a.parts_id = p.id)
1228 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1230 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1234 $ref->{inventory_accno_id} *= 1;
1235 $ref->{expense_accno_id} *= 1;
1237 # multiply by number of assemblies
1238 $ref->{qty} *= $totalqty;
1240 if ($ref->{assembly}) {
1241 &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1244 if ($ref->{inventory_accno_id}) {
1245 $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
1249 # save detail record for individual assembly item in invoice table
1251 qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
1252 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1253 my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
1254 do_query($form, $dbh, $query, @values);
1260 $main::lxdebug->leave_sub();
1264 $main::lxdebug->enter_sub();
1266 # adjust allocated in table invoice according to FIFO princicple
1267 # for a certain part with part_id $id
1269 my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
1273 $form->{taxzone_id} *=1;
1274 my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
1275 my $taxzone_id = $form->{"taxzone_id"} * 1;
1277 qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
1278 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1279 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
1280 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
1281 FROM invoice i, parts p
1282 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1283 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1284 LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1285 WHERE (i.parts_id = p.id)
1286 AND (i.parts_id = ?)
1287 AND ((i.base_qty + i.allocated) < 0)
1289 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
1294 # all invoice entries of an example part:
1296 # id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
1297 # ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
1298 # 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1299 # 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
1300 # 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1301 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1302 # 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
1304 # 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
1305 # and all parts have been allocated
1307 # so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
1308 # 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
1310 # in this example there are still 4 unsold articles
1313 # search all invoice entries for the part in question, adjusting "allocated"
1314 # until the total number of sold parts has been reached
1316 # ORDER BY trans_id ensures FIFO
1319 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1320 if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
1324 # update allocated in invoice
1325 $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
1327 # total expenses and inventory
1328 # sellprice is the cost of the item
1329 my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
1331 if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
1332 # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
1333 $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
1335 $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
1336 $form->{expense_inventory} .= " " . $ref->{expense_accno};
1337 $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
1339 $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
1340 $form->{expense_inventory} .= " " . $ref->{inventory_accno};
1346 last if (($totalqty -= $qty) <= 0);
1351 $main::lxdebug->leave_sub();
1356 sub reverse_invoice {
1357 $main::lxdebug->enter_sub();
1359 my ($dbh, $form) = @_;
1361 # reverse inventory items
1363 qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
1365 JOIN parts p ON (i.parts_id = p.id)
1366 WHERE i.trans_id = ?|;
1367 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
1369 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1371 if ($ref->{inventory_accno_id}) {
1372 # de-allocated purchases
1374 qq|SELECT i.id, i.trans_id, i.allocated
1376 WHERE (i.parts_id = ?) AND (i.allocated > 0)
1377 ORDER BY i.trans_id DESC|;
1378 my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
1380 while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
1381 my $qty = $ref->{qty};
1382 if (($ref->{qty} - $inhref->{allocated}) > 0) {
1383 $qty = $inhref->{allocated};
1387 $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
1389 last if (($ref->{qty} -= $qty) <= 0);
1398 my @values = (conv_i($form->{id}));
1399 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1400 do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1401 do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1403 $main::lxdebug->leave_sub();
1406 sub delete_invoice {
1407 $main::lxdebug->enter_sub();
1409 my ($self, $myconfig, $form) = @_;
1411 # connect to database
1412 my $dbh = $form->get_standard_dbh;
1415 &reverse_invoice($dbh, $form);
1417 my @values = (conv_i($form->{id}));
1419 # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
1420 # zurückgesetzt werden. Vgl:
1421 # id | storno | storno_id | paid | amount
1422 #----+--------+-----------+---------+-----------
1423 # 18 | f | | 0.00000 | 119.00000
1425 # 18 | t | | 119.00000 | 119.00000
1427 if($form->{storno}){
1428 # storno_id auslesen und korrigieren
1429 my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
1430 do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
1433 # delete spool files
1434 my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
1437 qq|DELETE FROM status WHERE trans_id = ?|,
1438 qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1439 qq|DELETE FROM ar WHERE id = ?|,
1442 map { do_query($form, $dbh, $_, @values) } @queries;
1444 my $rc = $dbh->commit;
1447 my $spool = $::lx_office_conf{paths}->{spool};
1448 map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
1451 $main::lxdebug->leave_sub();
1456 sub retrieve_invoice {
1457 $main::lxdebug->enter_sub();
1459 my ($self, $myconfig, $form) = @_;
1461 # connect to database
1462 my $dbh = $form->get_standard_dbh;
1464 my ($sth, $ref, $query);
1466 my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
1470 (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1471 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1472 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1473 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1474 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1475 d.curr AS currencies
1479 $ref = selectfirst_hashref_query($form, $dbh, $query);
1480 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1483 my $id = conv_i($form->{id});
1486 #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
1490 a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
1491 a.orddate, a.quodate, a.globalproject_id,
1492 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
1493 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
1494 a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
1495 a.employee_id, a.salesman_id, a.payment_id,
1496 a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
1497 a.transaction_description, a.donumber, a.invnumber_for_credit_note,
1498 a.marge_total, a.marge_percent,
1501 LEFT JOIN employee e ON (e.id = a.employee_id)
1503 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1504 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1506 # remove any trailing whitespace
1507 $form->{currency} =~ s/\s*$//;
1509 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
1512 $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
1513 $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
1515 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
1517 foreach my $vc (qw(customer vendor)) {
1518 next if !$form->{"delivery_${vc}_id"};
1519 ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
1522 # get printed, emailed
1523 $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
1524 $sth = prepare_execute_query($form, $dbh, $query, $id);
1526 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1527 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1528 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1529 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1532 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1534 my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
1535 : $form->{invdate} ? $dbh->quote($form->{invdate})
1539 my $taxzone_id = $form->{taxzone_id} *= 1;
1540 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1542 # retrieve individual items
1545 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
1546 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1547 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
1550 i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1551 i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1552 i.price_factor_id, i.price_factor, i.marge_price_factor,
1553 p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1554 pr.projectnumber, pg.partsgroup, prg.pricegroup
1557 LEFT JOIN parts p ON (i.parts_id = p.id)
1558 LEFT JOIN project pr ON (i.project_id = pr.id)
1559 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1560 LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)
1562 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
1563 LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
1564 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
1566 WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
1568 $sth = prepare_execute_query($form, $dbh, $query, $id);
1570 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1571 # Retrieve custom variables.
1572 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1574 sub_module => 'invoice',
1575 trans_id => $ref->{invoice_id},
1577 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1578 delete $ref->{invoice_id};
1580 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1581 delete($ref->{"part_inventory_accno_id"});
1583 foreach my $type (qw(inventory income expense)) {
1584 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1585 my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
1586 @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1590 # get tax rates and description
1591 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1593 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
1594 LEFT JOIN chart c ON (c.id = t.chart_id)
1596 (SELECT tk.tax_id FROM taxkeys tk
1597 WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
1598 AND startdate <= date($transdate)
1599 ORDER BY startdate DESC LIMIT 1)
1601 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1602 $ref->{taxaccounts} = "";
1604 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1606 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1610 $ref->{taxaccounts} .= "$ptr->{accno} ";
1612 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1613 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1614 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1615 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1616 $form->{taxaccounts} .= "$ptr->{accno} ";
1621 $ref->{qty} *= -1 if $form->{type} eq "credit_note";
1623 chop $ref->{taxaccounts};
1624 push @{ $form->{invoice_details} }, $ref;
1629 Common::webdav_folder($form);
1632 my $rc = $dbh->commit;
1634 $main::lxdebug->leave_sub();
1640 $main::lxdebug->enter_sub();
1642 my ($self, $myconfig, $form) = @_;
1644 # connect to database
1645 my $dbh = $form->get_standard_dbh;
1647 my $dateformat = $myconfig->{dateformat};
1648 $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
1650 my (@values, $duedate, $ref, $query);
1652 if ($form->{invdate}) {
1653 $duedate = "to_date(?, '$dateformat')";
1654 push @values, $form->{invdate};
1656 $duedate = "current_date";
1659 my $cid = conv_i($form->{customer_id});
1662 if ($form->{payment_id}) {
1663 $payment_id = "(pt.id = ?) OR";
1664 push @values, conv_i($form->{payment_id});
1670 c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
1671 c.email, c.cc, c.bcc, c.language_id, c.payment_id,
1672 c.street, c.zipcode, c.city, c.country,
1673 c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
1674 c.taxincluded_checked,
1675 $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
1676 b.discount AS tradediscount, b.description AS business
1678 LEFT JOIN business b ON (b.id = c.business_id)
1679 LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
1682 $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
1684 delete $ref->{salesman_id} if !$ref->{salesman_id};
1686 map { $form->{$_} = $ref->{$_} } keys %$ref;
1688 # remove any trailing whitespace
1689 $form->{curr} =~ s/\s*$//;
1691 # use customer currency if not empty
1692 $form->{currency} = $form->{curr} if $form->{curr};
1695 qq|SELECT sum(amount - paid) AS dunning_amount
1697 WHERE (paid < amount)
1698 AND (customer_id = ?)
1699 AND (dunning_config_id IS NOT NULL)|;
1700 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1701 map { $form->{$_} = $ref->{$_} } keys %$ref;
1704 qq|SELECT dnn.dunning_description AS max_dunning_level
1705 FROM dunning_config dnn
1706 WHERE id IN (SELECT dunning_config_id
1708 WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
1709 ORDER BY dunning_level DESC LIMIT 1|;
1710 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1711 map { $form->{$_} = $ref->{$_} } keys %$ref;
1713 $form->{creditremaining} = $form->{creditlimit};
1714 $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
1715 my ($value) = selectrow_query($form, $dbh, $query, $cid);
1716 $form->{creditremaining} -= $value;
1720 (SELECT e.buy FROM exchangerate e
1721 WHERE e.curr = o.curr
1722 AND e.transdate = o.transdate)
1724 WHERE o.customer_id = ?
1725 AND o.quotation = '0'
1726 AND o.closed = '0'|;
1727 my $sth = prepare_execute_query($form, $dbh, $query, $cid);
1729 while (my ($amount, $exch) = $sth->fetchrow_array) {
1730 $exch = 1 unless $exch;
1731 $form->{creditremaining} -= $amount * $exch;
1735 # get shipto if we did not converted an order or invoice
1736 if (!$form->{shipto}) {
1737 map { delete $form->{$_} }
1738 qw(shiptoname shiptodepartment_1 shiptodepartment_2
1739 shiptostreet shiptozipcode shiptocity shiptocountry
1740 shiptocontact shiptophone shiptofax shiptoemail);
1742 $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
1743 $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
1745 map { $form->{$_} = $ref->{$_} } keys %$ref;
1748 # setup last accounts used for this customer
1749 if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
1751 qq|SELECT c.id, c.accno, c.description, c.link, c.category
1753 JOIN acc_trans ac ON (ac.chart_id = c.id)
1754 JOIN ar a ON (a.id = ac.trans_id)
1755 WHERE a.customer_id = ?
1756 AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
1757 AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
1758 $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
1761 while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
1762 if ($ref->{category} eq 'I') {
1764 $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
1766 if ($form->{initial_transdate}) {
1768 qq|SELECT tk.tax_id, t.rate
1770 LEFT JOIN tax t ON tk.tax_id = t.id
1771 WHERE (tk.chart_id = ?) AND (startdate <= date(?))
1772 ORDER BY tk.startdate DESC
1774 my ($tax_id, $rate) =
1775 selectrow_query($form, $dbh, $tax_query, $ref->{id},
1776 $form->{initial_transdate});
1777 $form->{"taxchart_$i"} = "${tax_id}--${rate}";
1780 if ($ref->{category} eq 'A') {
1781 $form->{ARselected} = $form->{AR_1} = $ref->{accno};
1785 $form->{rowcount} = $i if ($i && !$form->{type});
1788 $main::lxdebug->leave_sub();
1792 $main::lxdebug->enter_sub();
1794 my ($self, $myconfig, $form) = @_;
1796 # connect to database
1797 my $dbh = $form->get_standard_dbh;
1799 my $i = $form->{rowcount};
1801 my $where = qq|NOT p.obsolete = '1'|;
1804 foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
1805 my ($table, $field) = split m/\./, $column;
1806 next if !$form->{"${field}_${i}"};
1807 $where .= qq| AND lower(${column}) ILIKE ?|;
1808 push @values, '%' . $form->{"${field}_${i}"} . '%';
1811 #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
1812 if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
1813 $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
1814 push @values, $form->{"partnumber_$i"};
1817 # Search for part ID overrides all other criteria.
1818 if ($form->{"id_${i}"}) {
1819 $where = qq|p.id = ?|;
1820 @values = ($form->{"id_${i}"});
1823 if ($form->{"description_$i"}) {
1824 $where .= qq| ORDER BY p.description|;
1826 $where .= qq| ORDER BY p.partnumber|;
1830 if ($form->{type} eq "invoice") {
1832 $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
1833 $form->{invdate} ? $dbh->quote($form->{invdate}) :
1837 $form->{transdate} ? $dbh->quote($form->{transdate}) :
1841 my $taxzone_id = $form->{taxzone_id} * 1;
1842 $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
1846 p.id, p.partnumber, p.description, p.sellprice,
1847 p.listprice, p.inventory_accno_id, p.lastcost,
1849 c1.accno AS inventory_accno,
1850 c1.new_chart_id AS inventory_new_chart,
1851 date($transdate) - c1.valid_from AS inventory_valid,
1853 c2.accno AS income_accno,
1854 c2.new_chart_id AS income_new_chart,
1855 date($transdate) - c2.valid_from AS income_valid,
1857 c3.accno AS expense_accno,
1858 c3.new_chart_id AS expense_new_chart,
1859 date($transdate) - c3.valid_from AS expense_valid,
1861 p.unit, p.assembly, p.bin, p.onhand,
1862 p.notes AS partnotes, p.notes AS longdescription,
1863 p.not_discountable, p.formel, p.payment_id AS part_payment_id,
1866 pfac.factor AS price_factor,
1871 LEFT JOIN chart c1 ON
1872 ((SELECT inventory_accno_id
1873 FROM buchungsgruppen
1874 WHERE id = p.buchungsgruppen_id) = c1.id)
1875 LEFT JOIN chart c2 ON
1876 ((SELECT income_accno_id_${taxzone_id}
1877 FROM buchungsgruppen
1878 WHERE id = p.buchungsgruppen_id) = c2.id)
1879 LEFT JOIN chart c3 ON
1880 ((SELECT expense_accno_id_${taxzone_id}
1881 FROM buchungsgruppen
1882 WHERE id = p.buchungsgruppen_id) = c3.id)
1883 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
1884 LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
1886 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1888 my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
1890 WHERE tr.language_id = ? AND tr.parts_id = ?| ],
1891 [ qq|SELECT tr.translation, tr.longdescription
1893 WHERE tr.language_id IN
1896 WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
1899 map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
1901 while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
1903 # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
1904 # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
1905 # Buchungskonto also aus dem Ergebnis rausgenommen werden.
1906 if (!$ref->{inventory_accno_id}) {
1907 map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
1909 delete($ref->{inventory_accno_id});
1911 foreach my $type (qw(inventory income expense)) {
1912 while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
1914 qq|SELECT accno, new_chart_id, date($transdate) - valid_from
1917 ($ref->{"${type}_accno"},
1918 $ref->{"${type}_new_chart"},
1919 $ref->{"${type}_valid"})
1920 = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
1924 if ($form->{payment_id} eq "") {
1925 $form->{payment_id} = $form->{part_payment_id};
1928 # get tax rates and description
1929 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1931 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
1933 LEFT JOIN chart c ON (c.id = t.chart_id)
1937 WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
1939 ORDER BY startdate DESC
1942 @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
1943 my $stw = $dbh->prepare($query);
1944 $stw->execute(@values) || $form->dberror($query);
1946 $ref->{taxaccounts} = "";
1948 while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
1950 # if ($customertax{$ref->{accno}})
1951 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1955 $ref->{taxaccounts} .= "$ptr->{accno} ";
1957 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1958 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1959 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1960 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1961 $form->{taxaccounts} .= "$ptr->{accno} ";
1967 chop $ref->{taxaccounts};
1969 if ($form->{language_id}) {
1970 for my $spec (@translation_queries) {
1971 do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
1972 my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
1973 next unless $translation;
1974 $ref->{description} = $translation;
1975 $ref->{longdescription} = $longdescription;
1980 $ref->{onhand} *= 1;
1982 push @{ $form->{item_list} }, $ref;
1985 $_->[1]->finish for @translation_queries;
1987 foreach my $item (@{ $form->{item_list} }) {
1988 my $custom_variables = CVar->get_custom_variables(module => 'IC',
1989 trans_id => $item->{id},
1993 map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
1996 $main::lxdebug->leave_sub();
1999 ##########################
2000 # get pricegroups from database
2001 # build up selected pricegroup
2002 # if an exchange rate - change price
2005 sub get_pricegroups_for_parts {
2007 $main::lxdebug->enter_sub();
2009 my ($self, $myconfig, $form) = @_;
2011 my $dbh = $form->get_standard_dbh;
2013 $form->{"PRICES"} = {};
2017 my $all_units = AM->retrieve_units($myconfig, $form);
2018 while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
2019 $form->{"PRICES"}{$i} = [];
2021 $id = $form->{"id_$i"};
2023 if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
2024 $id = $form->{"new_id_$i"};
2027 my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
2029 my $pricegroup_old = $form->{"pricegroup_old_$i"};
2031 # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
2032 my $sellprice = $form->{"sellprice_$i"};
2033 my $pricegroup_id = $form->{"pricegroup_id_$i"};
2034 $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
2035 $form->{"old_pricegroup_$i"} = $pricegroup_old;
2037 my $price_new = $form->{"price_new_$i"};
2038 my $price_old = $form->{"price_old_$i"};
2040 if (!$form->{"unit_old_$i"}) {
2041 # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
2042 # Einheit, wie sie in den Stammdaten hinterlegt wurde.
2043 # Es sollte also angenommen werden, dass diese ausgewaehlt war.
2044 $form->{"unit_old_$i"} = $form->{"unit_$i"};
2047 # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
2048 # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
2049 $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
2051 if (!$all_units->{$form->{"selected_unit_$i"}} ||
2052 ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
2053 $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
2054 # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
2055 # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
2056 # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
2057 $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
2062 if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
2063 if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
2064 $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
2065 $basefactor = $all_units->{$form->{"selected_unit_$i"}}->{"factor"} /
2066 $all_units->{$form->{"unit_old_$i"}}->{"factor"};
2070 if (!$form->{"basefactor_$i"}) {
2071 $form->{"basefactor_$i"} = 1;
2077 sellprice AS default_sellprice,
2080 'selected' AS selected
2086 parts.sellprice AS default_sellprice,
2087 pricegroup.pricegroup,
2091 LEFT JOIN parts ON parts.id = parts_id
2092 LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
2094 ORDER BY pricegroup|;
2095 my @values = (conv_i($id), conv_i($id));
2096 my $pkq = prepare_execute_query($form, $dbh, $query, @values);
2098 while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
2100 $pkr->{selected} = '';
2102 # if there is an exchange rate change price
2103 if (($form->{exchangerate} * 1) != 0) {
2104 $pkr->{price} /= $form->{exchangerate};
2107 $pkr->{price} *= $form->{"basefactor_$i"};
2108 $pkr->{price} *= $basefactor;
2109 $pkr->{price_ufmt} = $pkr->{price};
2110 $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
2112 if (!defined $selectedpricegroup_id) {
2113 # new entries in article list, either old invoice was loaded (edit) or a new article was added
2114 # Case A: open old invoice, no pricegroup selected
2115 # Case B: add new article to invoice, no pricegroup selected
2117 # to distinguish case A and B the variable pricegroup_id_$i is used
2118 # for new articles this variable isn't defined, for loaded articles it is
2119 # sellprice can't be used, as it already has 0,00 set
2121 if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
2123 $pkr->{selected} = ' selected';
2124 } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
2125 and not defined $form->{"pricegroup_id_$i"}
2126 and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
2127 # for the case where pricegroup prices haven't been set
2129 # Case B: use default pricegroup of customer
2131 $pkr->{selected} = ' selected'; # unless $form->{selected};
2132 # no customer pricesgroup set
2133 if ($pkr->{price_unfmt} == $pkr->{default_sellprice}) {
2135 $pkr->{price} = $form->{"sellprice_$i"};
2139 # this sub should not set anything and only return. --sschoeling, 20090506
2140 # is this correct? put in again... -- grichardson 20110119
2141 $form->{"sellprice_$i"} = $pkr->{price};
2144 } elsif ($pkr->{price_unfmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
2145 $pkr->{price} = $form->{"sellprice_$i"};
2146 $pkr->{selected} = ' selected';
2150 # existing article: pricegroup or price changed
2151 if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
2152 if ($selectedpricegroup_id ne $pricegroup_old) {
2153 # pricegroup has changed
2154 if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2155 $pkr->{selected} = ' selected';
2157 } elsif ( ($form->parse_amount($myconfig, $price_new)
2158 != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
2159 and ($price_new ne 0) and defined $price_new) {
2160 # sellprice has changed
2161 # when loading existing invoices $price_new is NULL
2162 if ($pkr->{pricegroup_id} == 0) {
2163 $pkr->{price} = $form->{"sellprice_$i"};
2164 $pkr->{selected} = ' selected';
2166 } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
2167 # neither sellprice nor pricegroup changed
2168 $pkr->{selected} = ' selected';
2169 if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
2170 # $pkr->{price} = $form->{"sellprice_$i"};
2172 $pkr->{price} = $form->{"sellprice_$i"};
2176 push @{ $form->{PRICES}{$i} }, $pkr;
2179 $form->{"basefactor_$i"} *= $basefactor;
2186 $main::lxdebug->leave_sub();
2190 $main::lxdebug->enter_sub();
2192 my ($self, $myconfig, $form, $table) = @_;
2194 $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
2196 # make sure there's no funny stuff in $table
2197 # ToDO: die when this happens and throw an error
2198 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2200 my $dbh = $form->get_standard_dbh;
2202 my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
2203 my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
2205 $main::lxdebug->leave_sub();
2211 $main::lxdebug->enter_sub();
2213 my ($self, $myconfig, $form, $table, $id) = @_;
2215 $main::lxdebug->leave_sub() and return 0 unless ($id);
2217 # make sure there's no funny stuff in $table
2218 # ToDO: die when this happens and throw an error
2219 $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
2221 my $dbh = $form->get_standard_dbh;
2223 my $query = qq|SELECT storno FROM $table WHERE id = ?|;
2224 my ($result) = selectrow_query($form, $dbh, $query, $id);
2226 $main::lxdebug->leave_sub();
2231 sub get_standard_accno_current_assets {
2232 $main::lxdebug->enter_sub();
2234 my ($self, $myconfig, $form) = @_;
2236 my $dbh = $form->get_standard_dbh;
2238 my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
2239 my ($result) = selectrow_query($form, $dbh, $query);
2241 $main::lxdebug->leave_sub();