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) 1999-2003
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 #======================================================================
33 #======================================================================
37 use List::Util qw(max first);
46 OE.pm - Order entry module
50 OE.pm is part of the OE module. OE is responsible for sales and purchase orders, as well as sales quotations and purchase requests. This file abstracts the database tables C<oe> and C<orderitems>.
59 $main::lxdebug->enter_sub();
61 my ($self, $myconfig, $form) = @_;
64 my $dbh = $form->dbconnect($myconfig);
67 my $ordnumber = 'ordnumber';
73 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
75 if ($form->{type} =~ /_quotation$/) {
77 $ordnumber = 'quonumber';
80 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
83 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
84 qq| o.amount, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
85 qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
86 qq| o.transaction_description, | .
87 qq| o.marge_total, o.marge_percent, | .
88 qq| ex.$rate AS exchangerate, | .
89 qq| pr.projectnumber AS globalprojectnumber, | .
90 qq| e.name AS employee, s.name AS salesman, | .
91 qq| ct.country, ct.ustid | .
93 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
94 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
95 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
96 qq|LEFT JOIN exchangerate ex ON (ex.curr = o.curr | .
97 qq| AND ex.transdate = o.transdate) | .
98 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
99 qq|WHERE (o.quotation = ?) |;
100 push(@values, $quotation);
102 my ($null, $split_department_id) = split /--/, $form->{department};
103 my $department_id = $form->{department_id} || $split_department_id;
104 if ($department_id) {
105 $query .= qq| AND o.department_id = ?|;
106 push(@values, $department_id);
109 if ($form->{"project_id"}) {
111 qq|AND ((globalproject_id = ?) OR EXISTS | .
112 qq| (SELECT * FROM orderitems oi | .
113 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
114 push(@values, $form->{"project_id"}, $form->{"project_id"});
117 if ($form->{"${vc}_id"}) {
118 $query .= " AND o.${vc}_id = ?";
119 push(@values, $form->{"${vc}_id"});
121 } elsif ($form->{$vc}) {
122 $query .= " AND ct.name ILIKE ?";
123 push(@values, '%' . $form->{$vc} . '%');
126 if ($form->{employee_id}) {
127 $query .= " AND o.employee_id = ?";
128 push @values, conv_i($form->{employee_id});
131 if ($form->{salesman_id}) {
132 $query .= " AND o.salesman_id = ?";
133 push @values, conv_i($form->{salesman_id});
136 if (!$form->{open} && !$form->{closed}) {
137 $query .= " AND o.id = 0";
138 } elsif (!($form->{open} && $form->{closed})) {
139 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
142 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
143 ($form->{"notdelivered"} ne $form->{"delivered"})) {
144 $query .= $form->{"delivered"} ?
145 " AND o.delivered " : " AND NOT o.delivered";
148 if ($form->{$ordnumber}) {
149 $query .= qq| AND o.$ordnumber ILIKE ?|;
150 push(@values, '%' . $form->{$ordnumber} . '%');
153 if($form->{transdatefrom}) {
154 $query .= qq| AND o.transdate >= ?|;
155 push(@values, conv_date($form->{transdatefrom}));
158 if($form->{transdateto}) {
159 $query .= qq| AND o.transdate <= ?|;
160 push(@values, conv_date($form->{transdateto}));
163 if($form->{reqdatefrom}) {
164 $query .= qq| AND o.reqdate >= ?|;
165 push(@values, conv_date($form->{reqdatefrom}));
168 if($form->{reqdateto}) {
169 $query .= qq| AND o.reqdate <= ?|;
170 push(@values, conv_date($form->{reqdateto}));
173 if ($form->{transaction_description}) {
174 $query .= qq| AND o.transaction_description ILIKE ?|;
175 push(@values, '%' . $form->{transaction_description} . '%');
178 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
179 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
180 my %allowed_sort_columns = (
181 "transdate" => "o.transdate",
182 "reqdate" => "o.reqdate",
184 "ordnumber" => "o.ordnumber",
185 "quonumber" => "o.quonumber",
187 "employee" => "e.name",
188 "salesman" => "e.name",
189 "shipvia" => "o.shipvia",
190 "transaction_description" => "o.transaction_description"
192 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
193 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
195 $query .= qq| ORDER by | . $sortorder;
197 my $sth = $dbh->prepare($query);
198 $sth->execute(@values) ||
199 $form->dberror($query . " (" . join(", ", @values) . ")");
203 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
204 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
205 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
206 $id{ $ref->{id} } = $ref->{id};
212 $main::lxdebug->leave_sub();
215 sub transactions_for_todo_list {
216 $main::lxdebug->enter_sub();
221 my $myconfig = \%main::myconfig;
222 my $form = $main::form;
224 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
226 my $query = qq|SELECT id FROM employee WHERE login = ?|;
227 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
230 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
231 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
236 LEFT JOIN customer c ON (oe.customer_id = c.id)
237 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
238 LEFT JOIN employee e ON (oe.employee_id = e.id)
239 WHERE (COALESCE(quotation, FALSE) = TRUE)
240 AND (COALESCE(closed, FALSE) = FALSE)
241 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
242 AND NOT (oe.reqdate ISNULL)
243 AND (oe.reqdate < current_date)
246 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
248 $main::lxdebug->leave_sub();
254 $main::lxdebug->enter_sub();
256 my ($self, $myconfig, $form) = @_;
258 # connect to database, turn off autocommit
259 my $dbh = $form->dbconnect_noauto($myconfig);
261 my ($query, @values, $sth, $null);
262 my $exchangerate = 0;
264 my $all_units = AM->retrieve_units($myconfig, $form);
265 $form->{all_units} = $all_units;
267 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
270 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
271 unless ($form->{employee_id}) {
272 $form->get_employee($dbh);
275 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
278 $query = qq|DELETE FROM custom_variables
279 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
280 AND (sub_module = 'orderitems')
281 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
282 do_query($form, $dbh, $query, $form->{id});
284 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
285 do_query($form, $dbh, $query, $form->{id});
287 $query = qq|DELETE FROM shipto | .
288 qq|WHERE trans_id = ? AND module = 'OE'|;
289 do_query($form, $dbh, $query, $form->{id});
293 $query = qq|SELECT nextval('id')|;
294 ($form->{id}) = selectrow_query($form, $dbh, $query);
296 $query = qq|INSERT INTO oe (id, ordnumber, employee_id) VALUES (?, '', ?)|;
297 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
315 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
316 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
319 for my $i (1 .. $form->{rowcount}) {
321 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
323 if ($form->{"id_$i"}) {
326 $query = qq|SELECT unit FROM parts WHERE id = ?|;
327 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
330 if (defined($all_units->{$item_unit}->{factor}) &&
331 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
332 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
334 my $baseqty = $form->{"qty_$i"} * $basefactor;
336 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
337 $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
338 $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
340 # set values to 0 if nothing entered
341 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
343 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
344 $fxsellprice = $form->{"sellprice_$i"};
346 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
348 my $decimalplaces = ($dec > 2) ? $dec : 2;
350 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
351 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
353 $form->{"inventory_accno_$i"} *= 1;
354 $form->{"expense_accno_$i"} *= 1;
356 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
357 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
359 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
363 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
365 if ($form->{taxincluded}) {
366 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
367 $taxbase = $linetotal - $taxamount;
369 # we are not keeping a natural price, do not round
370 $form->{"sellprice_$i"} =
371 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
373 $taxamount = $linetotal * $taxrate;
374 $taxbase = $linetotal;
377 if ($form->round_amount($taxrate, 7) == 0) {
378 if ($form->{taxincluded}) {
379 foreach my $item (@taxaccounts) {
380 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
381 $taxaccounts{$item} += $taxamount;
382 $taxdiff += $taxamount;
383 $taxbase{$item} += $taxbase;
385 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
387 foreach my $item (@taxaccounts) {
388 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
389 $taxbase{$item} += $taxbase;
393 foreach my $item (@taxaccounts) {
394 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
395 $taxbase{$item} += $taxbase;
399 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
401 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
403 # get pricegroup_id and save ist
404 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
407 # save detail record in orderitems table
408 my $orderitems_id = $form->{"orderitems_id_$i"};
409 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
412 $query = qq|INSERT INTO orderitems (
413 id, trans_id, parts_id, description, longdescription, qty, base_qty,
414 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
415 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
416 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
417 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
418 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
420 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
421 $form->{"description_$i"}, $form->{"longdescription_$i"},
422 $form->{"qty_$i"}, $baseqty,
423 $fxsellprice, $form->{"discount_$i"},
424 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
425 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id),
426 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
427 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
428 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
429 $form->{"lastcost_$i"},
430 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
431 conv_i($form->{"marge_price_factor_$i"}));
432 do_query($form, $dbh, $query, @values);
434 $form->{"sellprice_$i"} = $fxsellprice;
435 $form->{"discount_$i"} *= 100;
437 CVar->save_custom_variables(module => 'IC',
438 sub_module => 'orderitems',
439 trans_id => $orderitems_id,
440 configs => $ic_cvar_configs,
442 name_prefix => 'ic_',
443 name_postfix => "_$i",
448 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
452 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
454 $amount = $form->round_amount($netamount + $tax, 2);
455 $netamount = $form->round_amount($netamount, 2);
457 if ($form->{currency} eq $form->{defaultcurrency}) {
458 $form->{exchangerate} = 1;
460 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
463 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
465 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
467 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
472 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
473 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
474 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = ?, closed = ?,
475 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
476 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
477 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
480 @values = ($form->{ordnumber} || '', $form->{quonumber},
481 $form->{cusordnumber}, conv_date($form->{transdate}),
482 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
483 $amount, $netamount, conv_date($reqdate),
484 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
485 $form->{shipvia}, $form->{notes}, $form->{intnotes},
486 substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f',
487 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
488 $quotation, conv_i($form->{department_id}),
489 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
490 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
491 conv_i($form->{delivery_vendor_id}),
492 conv_i($form->{delivery_customer_id}),
493 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
494 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
495 $form->{transaction_description},
496 $form->{marge_total} * 1, $form->{marge_percent} * 1,
497 conv_i($form->{id}));
498 do_query($form, $dbh, $query, @values);
500 $form->{ordtotal} = $amount;
503 $form->{name} = $form->{ $form->{vc} };
504 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
506 if (!$form->{shipto_id}) {
507 $form->add_shipto($dbh, $form->{id}, "OE");
510 # save printed, emailed, queued
511 $form->save_status($dbh);
513 # Link this record to the records it was created from.
514 $form->{convert_from_oe_ids} =~ s/^\s+//;
515 $form->{convert_from_oe_ids} =~ s/\s+$//;
516 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
517 delete $form->{convert_from_oe_ids};
519 if (scalar @convert_from_oe_ids) {
520 RecordLinks->create_links('dbh' => $dbh,
522 'from_table' => 'oe',
523 'from_ids' => \@convert_from_oe_ids,
525 'to_id' => $form->{id},
528 $self->_close_quotations_rfqs('dbh' => $dbh,
529 'from_id' => \@convert_from_oe_ids,
530 'to_id' => $form->{id});
533 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
534 if ($form->{vc} eq 'customer') {
535 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
537 if ($form->{vc} eq 'vendor') {
538 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
542 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
543 "quonumber" : "ordnumber"};
545 Common::webdav_folder($form) if ($main::webdav);
547 my $rc = $dbh->commit;
550 $main::lxdebug->leave_sub();
555 sub _close_quotations_rfqs {
556 $main::lxdebug->enter_sub();
561 Common::check_params(\%params, qw(from_id to_id));
563 my $myconfig = \%main::myconfig;
564 my $form = $main::form;
566 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
568 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
569 my $sth = prepare_query($form, $dbh, $query);
571 do_statement($form, $sth, $query, conv_i($params{to_id}));
573 my ($quotation) = $sth->fetchrow_array();
576 $main::lxdebug->leave_sub();
582 foreach my $from_id (@{ $params{from_id} }) {
583 $from_id = conv_i($from_id);
584 do_statement($form, $sth, $query, $from_id);
585 ($quotation) = $sth->fetchrow_array();
586 push @close_ids, $from_id if ($quotation);
591 if (scalar @close_ids) {
592 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
593 do_query($form, $dbh, $query, @close_ids);
595 $dbh->commit() unless ($params{dbh});
598 $main::lxdebug->leave_sub();
602 $main::lxdebug->enter_sub();
604 my ($self, $myconfig, $form, $spool) = @_;
606 # connect to database
607 my $dbh = $form->dbconnect_noauto($myconfig);
610 my $query = qq|SELECT s.spoolfile FROM status s | .
611 qq|WHERE s.trans_id = ?|;
612 my @values = (conv_i($form->{id}));
613 my $sth = $dbh->prepare($query);
614 $sth->execute(@values) || $self->dberror($query);
619 while (($spoolfile) = $sth->fetchrow_array) {
620 push @spoolfiles, $spoolfile;
625 @values = (conv_i($form->{id}));
627 # delete status entries
628 $query = qq|DELETE FROM status | .
629 qq|WHERE trans_id = ?|;
630 do_query($form, $dbh, $query, @values);
633 $query = qq|DELETE FROM oe | .
635 do_query($form, $dbh, $query, @values);
637 # delete individual entries
638 $query = qq|DELETE FROM orderitems | .
639 qq|WHERE trans_id = ?|;
640 do_query($form, $dbh, $query, @values);
642 $query = qq|DELETE FROM shipto | .
643 qq|WHERE trans_id = ? AND module = 'OE'|;
644 do_query($form, $dbh, $query, @values);
646 my $rc = $dbh->commit;
650 foreach $spoolfile (@spoolfiles) {
651 unlink "$spool/$spoolfile" if $spoolfile;
655 $main::lxdebug->leave_sub();
661 $main::lxdebug->enter_sub();
663 my ($self, $myconfig, $form) = @_;
665 # connect to database
666 my $dbh = $form->dbconnect_noauto($myconfig);
668 my ($query, $query_add, @values, @ids, $sth);
670 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
673 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
675 push @ids, $form->{"trans_id_$_"}
676 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
677 } (1 .. $form->{"rowcount"});
679 if ($form->{rowcount} && scalar @ids) {
680 $form->{convert_from_oe_ids} = join ' ', @ids;
683 # if called in multi id mode, and still only got one id, switch back to single id
684 if ($form->{"rowcount"} and $#ids == 0) {
685 $form->{"id"} = $ids[0];
689 # and remember for the rest of the function
690 my $is_collective_order = scalar @ids;
693 my $wday = (localtime(time))[6];
694 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
695 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
698 # get default accounts
699 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
700 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
701 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
702 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
703 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
707 my $ref = selectfirst_hashref_query($form, $dbh, $query);
708 map { $form->{$_} = $ref->{$_} } keys %$ref;
710 ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency});
712 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
713 # we come from invoices, feel free.
714 $form->{reqdate} = $form->{deliverydate}
715 if ( $form->{deliverydate}
716 and $form->{callback} =~ /action=ar_transactions/);
718 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
720 if ($form->{id} or @ids) {
722 # retrieve order for single id
723 # NOTE: this query is intended to fetch all information only ONCE.
724 # so if any of these infos is important (or even different) for any item,
725 # it will be killed out and then has to be fetched from the item scope query further down
727 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
728 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
729 o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id,
730 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
731 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
732 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
733 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
734 o.globalproject_id, o.delivered, o.transaction_description
736 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
737 LEFT JOIN employee e ON (o.employee_id = e.id)
738 LEFT JOIN department d ON (o.department_id = d.id) | .
741 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
743 @values = $form->{id} ? ($form->{id}) : @ids;
744 $sth = prepare_execute_query($form, $dbh, $query, @values);
746 $ref = $sth->fetchrow_hashref("NAME_lc");
747 map { $form->{$_} = $ref->{$_} } keys %$ref;
749 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
750 "quonumber" : "ordnumber"};
752 # set all entries for multiple ids blank that yield different information
753 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
754 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
757 # if not given, fill transdate with current_date
758 $form->{transdate} = $form->current_date($myconfig)
759 unless $form->{transdate};
763 if ($form->{delivery_customer_id}) {
764 $query = qq|SELECT name FROM customer WHERE id = ?|;
765 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
768 if ($form->{delivery_vendor_id}) {
769 $query = qq|SELECT name FROM customer WHERE id = ?|;
770 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
773 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
775 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
776 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
778 $ref = $sth->fetchrow_hashref("NAME_lc");
780 map { $form->{$_} = $ref->{$_} } keys %$ref;
783 # get printed, emailed and queued
784 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
785 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
787 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
788 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
789 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
790 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
793 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
796 my %oid = ('Pg' => 'oid',
797 'Oracle' => 'rowid');
799 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
801 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
803 # retrieve individual items
804 # this query looks up all information about the items
805 # stuff different from the whole will not be overwritten, but saved with a suffix.
807 qq|SELECT o.id AS orderitems_id,
808 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
809 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
810 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
811 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
812 p.partnumber, p.assembly, o.description, o.qty,
813 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
814 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
815 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
816 o.price_factor_id, o.price_factor, o.marge_price_factor,
817 pr.projectnumber, p.formel,
818 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
820 JOIN parts p ON (o.parts_id = p.id)
821 JOIN oe ON (o.trans_id = oe.id)
822 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
823 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
824 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
825 LEFT JOIN project pr ON (o.project_id = pr.id)
826 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
828 ? qq|WHERE o.trans_id = ?|
829 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
830 qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
832 @ids = $form->{id} ? ($form->{id}) : @ids;
833 $sth = prepare_execute_query($form, $dbh, $query, @values);
835 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
836 # Retrieve custom variables.
837 my $cvars = CVar->get_custom_variables(dbh => $dbh,
839 sub_module => 'orderitems',
840 trans_id => $ref->{orderitems_id},
842 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
845 if (!$ref->{"part_inventory_accno_id"}) {
846 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
848 delete($ref->{"part_inventory_accno_id"});
850 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
851 # unless already present there
852 # remove _oe entries afterwards
853 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
854 qw|ordnumber transdate cusordnumber|
856 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
860 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
862 qq|SELECT accno AS inventory_accno, | .
863 qq| new_chart_id AS inventory_new_chart, | .
864 qq| date($transdate) - valid_from AS inventory_valid | .
865 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
866 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
867 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
870 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
872 qq|SELECT accno AS income_accno, | .
873 qq| new_chart_id AS income_new_chart, | .
874 qq| date($transdate) - valid_from AS income_valid | .
875 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
876 ($ref->{income_accno}, $ref->{income_new_chart},
877 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
880 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
882 qq|SELECT accno AS expense_accno, | .
883 qq| new_chart_id AS expense_new_chart, | .
884 qq| date($transdate) - valid_from AS expense_valid | .
885 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
886 ($ref->{expense_accno}, $ref->{expense_new_chart},
887 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
890 # delete orderitems_id in collective orders, so that they get cloned no matter what
891 delete $ref->{orderitems_id} if (@ids);
893 # get tax rates and description
894 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
896 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
897 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
898 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
899 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
900 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
901 qq|ORDER BY c.accno|;
902 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
903 $ref->{taxaccounts} = "";
905 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
906 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
910 $ref->{taxaccounts} .= "$ptr->{accno} ";
911 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
912 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
913 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
914 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
915 $form->{taxaccounts} .= "$ptr->{accno} ";
920 chop $ref->{taxaccounts};
922 push @{ $form->{form_details} }, $ref;
930 $form->lastname_used($dbh, $myconfig, $form->{vc})
931 unless $form->{"$form->{vc}_id"};
935 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
937 Common::webdav_folder($form) if ($main::webdav);
939 my $rc = $dbh->commit;
942 $main::lxdebug->leave_sub();
947 =item retrieve_simple PARAMS
949 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
951 my $order = retrieve_simple(id => 2);
963 sub retrieve_simple {
964 $main::lxdebug->enter_sub();
969 Common::check_params(\%params, qw(id));
971 my $myconfig = \%main::myconfig;
972 my $form = $main::form;
974 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
976 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
977 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
979 my ($order) = selectall_array_query($form, $dbh, $oe_query, conv_i($params{id}));
980 $order->{orderitems} = selectall_array_query($form, $dbh, $oi_query, conv_i($params{id}));
982 $main::lxdebug->leave_sub();
988 $main::lxdebug->enter_sub();
990 my ($self, $myconfig, $form) = @_;
992 # connect to database
993 my $dbh = $form->dbconnect($myconfig);
999 my $nodiscount_subtotal = 0;
1000 my $discount_subtotal = 0;
1003 my @partsgroup = ();
1006 my $subtotal_header = 0;
1007 my $subposition = 0;
1014 my %oid = ('Pg' => 'oid',
1015 'Oracle' => 'rowid');
1017 my (@project_ids, %projectnumbers);
1019 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1021 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1022 'departments' => 'ALL_DEPARTMENTS');
1025 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1026 $price_factors{$pfac->{id}} = $pfac;
1027 $pfac->{factor} *= 1;
1028 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1032 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1033 next unless $dept->{id} eq $form->{department_id};
1034 $form->{department} = $dept->{description};
1038 # sort items by partsgroup
1039 for $i (1 .. $form->{rowcount}) {
1041 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1042 $partsgroup = $form->{"partsgroup_$i"};
1044 push @partsgroup, [$i, $partsgroup];
1045 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1049 $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
1050 join(", ", map("?", @project_ids)) . ")";
1051 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1052 while (my $ref = $sth->fetchrow_hashref()) {
1053 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1058 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1060 $form->{discount} = [];
1062 $form->{TEMPLATE_ARRAYS} = { };
1063 IC->prepare_parts_for_printing();
1065 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1068 qw(runningnumber number description longdescription qty ship unit bin
1069 partnotes serialnumber reqdate sellprice listprice netprice
1070 discount p_discount discount_sub nodiscount_sub
1071 linetotal nodiscount_linetotal tax_rate projectnumber
1072 price_factor price_factor_name partsgroup);
1074 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1076 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1078 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1081 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1084 if ($item->[1] ne $sameitem) {
1085 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1086 $sameitem = $item->[1];
1088 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1091 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1093 if ($form->{"id_$i"} != 0) {
1095 # add number, description and qty to $form->{number}, ....
1097 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1098 $subtotal_header = $i;
1099 $position = int($position);
1102 } elsif ($subtotal_header) {
1104 $position = int($position);
1105 $position = $position.".".$subposition;
1107 $position = int($position);
1111 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1113 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1114 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1115 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1116 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1117 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1118 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1119 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1120 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1121 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1122 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1123 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1124 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1125 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1126 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1127 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1128 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1130 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1131 my ($dec) = ($sellprice =~ /\.(\d+)/);
1132 my $decimalplaces = max 2, length($dec);
1134 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1135 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1136 my $linetotal = $form->round_amount($linetotal_exact, 2);
1137 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
1139 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
1140 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1142 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1144 $linetotal = ($linetotal != 0) ? $linetotal : '';
1146 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1147 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1149 $form->{ordtotal} += $linetotal;
1150 $form->{nodiscount_total} += $nodiscount_linetotal;
1151 $form->{discount_total} += $discount;
1153 if ($subtotal_header) {
1154 $discount_subtotal += $linetotal;
1155 $nodiscount_subtotal += $nodiscount_linetotal;
1158 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1159 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1160 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1162 $discount_subtotal = 0;
1163 $nodiscount_subtotal = 0;
1164 $subtotal_header = 0;
1167 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
1168 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
1171 if (!$form->{"discount_$i"}) {
1172 $nodiscount += $linetotal;
1175 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1176 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1178 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1180 my ($taxamount, $taxbase);
1183 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1185 if ($form->{taxincluded}) {
1188 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1189 $taxbase = $linetotal / (1 + $taxrate);
1191 $taxamount = $linetotal * $taxrate;
1192 $taxbase = $linetotal;
1195 if ($taxamount != 0) {
1196 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1197 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1198 $taxbase{$accno} += $taxbase;
1202 $tax_rate = $taxrate * 100;
1203 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1205 if ($form->{"assembly_$i"}) {
1208 # get parts and push them onto the stack
1210 if ($form->{groupitems}) {
1211 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
1213 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
1216 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1217 qq|pg.partsgroup | .
1218 qq|FROM assembly a | .
1219 qq| JOIN parts p ON (a.parts_id = p.id) | .
1220 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1221 qq| WHERE a.bom = '1' | .
1222 qq| AND a.id = ? | . $sortorder;
1223 @values = ($form->{"id_$i"});
1224 $sth = $dbh->prepare($query);
1225 $sth->execute(@values) || $form->dberror($query);
1227 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1228 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1229 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1230 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1231 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1234 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1235 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1240 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
1245 foreach $item (sort keys %taxaccounts) {
1246 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1248 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1249 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1250 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1251 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1252 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1255 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1256 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1257 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1258 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1260 if($form->{taxincluded}) {
1261 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1263 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1266 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1269 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1271 if ($form->{type} =~ /_quotation/) {
1272 $form->set_payment_options($myconfig, $form->{quodate});
1274 $form->set_payment_options($myconfig, $form->{orddate});
1277 $form->{username} = $myconfig->{name};
1281 $main::lxdebug->leave_sub();
1284 sub project_description {
1285 $main::lxdebug->enter_sub();
1287 my ($self, $dbh, $id) = @_;
1289 my $query = qq|SELECT description FROM project WHERE id = ?|;
1290 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1292 $main::lxdebug->leave_sub();