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);
44 use SL::DB::PeriodicInvoicesConfig;
48 use SL::HTML::Restrict;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form) = @_;
60 my $dbh = $form->get_standard_dbh;
63 my $ordnumber = 'ordnumber';
69 my ($periodic_invoices_columns, $periodic_invoices_joins);
71 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
73 if ($form->{type} =~ /_quotation$/) {
75 $ordnumber = 'quonumber';
77 } elsif ($form->{type} eq 'sales_order') {
78 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
79 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
82 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
86 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
88 SELECT from_id, ar.amount, ar.netamount FROM (
91 WHERE from_table = 'oe' AND to_table = 'ar'
93 SELECT rl1.from_id, rl2.to_id
95 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
96 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
98 LEFT JOIN ar ON ar.id = rl.to_id
100 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
101 $billed_amount{ $ref->{from_id}} += $ref->{amount};
102 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
107 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
108 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
109 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
110 qq| o.transaction_description, | .
111 qq| o.marge_total, o.marge_percent, | .
112 qq| ex.$rate AS exchangerate, | .
113 qq| pr.projectnumber AS globalprojectnumber, | .
114 qq| e.name AS employee, s.name AS salesman, | .
115 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
116 qq| tz.description AS taxzone | .
117 $periodic_invoices_columns .
118 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
120 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
121 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
122 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
123 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
124 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
125 qq| AND ex.transdate = o.transdate) | .
126 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
127 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
128 qq|$periodic_invoices_joins | .
129 qq|WHERE (o.quotation = ?) |;
130 push(@values, $quotation);
132 my ($null, $split_department_id) = split /--/, $form->{department};
133 my $department_id = $form->{department_id} || $split_department_id;
134 if ($department_id) {
135 $query .= qq| AND o.department_id = ?|;
136 push(@values, $department_id);
139 if ($form->{"project_id"}) {
141 qq|AND ((globalproject_id = ?) OR EXISTS | .
142 qq| (SELECT * FROM orderitems oi | .
143 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
144 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
147 if ($form->{"projectnumber"}) {
149 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
150 SELECT * FROM orderitems oi
151 LEFT JOIN project proi ON proi.id = oi.project_id
152 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
155 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
158 if ($form->{"business_id"}) {
159 $query .= " AND ct.business_id = ?";
160 push(@values, $form->{"business_id"});
163 if ($form->{"${vc}_id"}) {
164 $query .= " AND o.${vc}_id = ?";
165 push(@values, $form->{"${vc}_id"});
167 } elsif ($form->{$vc}) {
168 $query .= " AND ct.name ILIKE ?";
169 push(@values, '%' . $form->{$vc} . '%');
172 if ($form->{"cp_name"}) {
173 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
174 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
177 if (!$main::auth->assert('sales_all_edit', 1)) {
178 $query .= " AND o.employee_id = (select id from employee where login= ?)";
179 push @values, $form->{login};
181 if ($form->{employee_id}) {
182 $query .= " AND o.employee_id = ?";
183 push @values, conv_i($form->{employee_id});
186 if ($form->{salesman_id}) {
187 $query .= " AND o.salesman_id = ?";
188 push @values, conv_i($form->{salesman_id});
191 if (!$form->{open} && !$form->{closed}) {
192 $query .= " AND o.id = 0";
193 } elsif (!($form->{open} && $form->{closed})) {
194 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
197 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
198 ($form->{"notdelivered"} ne $form->{"delivered"})) {
199 $query .= $form->{"delivered"} ?
200 " AND o.delivered " : " AND NOT o.delivered";
203 if ($form->{$ordnumber}) {
204 $query .= qq| AND o.$ordnumber ILIKE ?|;
205 push(@values, '%' . $form->{$ordnumber} . '%');
208 if ($form->{cusordnumber}) {
209 $query .= qq| AND o.cusordnumber ILIKE ?|;
210 push(@values, '%' . $form->{cusordnumber} . '%');
213 if($form->{transdatefrom}) {
214 $query .= qq| AND o.transdate >= ?|;
215 push(@values, conv_date($form->{transdatefrom}));
218 if($form->{transdateto}) {
219 $query .= qq| AND o.transdate <= ?|;
220 push(@values, conv_date($form->{transdateto}));
223 if($form->{reqdatefrom}) {
224 $query .= qq| AND o.reqdate >= ?|;
225 push(@values, conv_date($form->{reqdatefrom}));
228 if($form->{reqdateto}) {
229 $query .= qq| AND o.reqdate <= ?|;
230 push(@values, conv_date($form->{reqdateto}));
233 if ($form->{shippingpoint}) {
234 $query .= qq| AND o.shippingpoint ILIKE ?|;
235 push(@values, '%' . $form->{shippingpoint} . '%');
238 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
239 $query .= qq| AND tz.id = ?|;
240 push(@values, $form->{taxzone_id});
243 if ($form->{transaction_description}) {
244 $query .= qq| AND o.transaction_description ILIKE ?|;
245 push(@values, '%' . $form->{transaction_description} . '%');
248 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
249 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
250 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
253 if ($form->{reqdate_unset_or_old}) {
254 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
257 if (($form->{order_probability_value} || '') ne '') {
258 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
259 $query .= qq| AND (o.order_probability ${op} ?)|;
260 push @values, $form->{order_probability_value};
263 if ($form->{expected_billing_date_from}) {
264 $query .= qq| AND (o.expected_billing_date >= ?)|;
265 push @values, conv_date($form->{expected_billing_date_from});
268 if ($form->{expected_billing_date_to}) {
269 $query .= qq| AND (o.expected_billing_date <= ?)|;
270 push @values, conv_date($form->{expected_billing_date_to});
273 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
274 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
275 my %allowed_sort_columns = (
276 "transdate" => "o.transdate",
277 "reqdate" => "o.reqdate",
279 "ordnumber" => "o.ordnumber",
280 "cusordnumber" => "o.cusordnumber",
281 "quonumber" => "o.quonumber",
283 "employee" => "e.name",
284 "salesman" => "s.name",
285 "shipvia" => "o.shipvia",
286 "transaction_description" => "o.transaction_description",
287 "shippingpoint" => "o.shippingpoint",
288 "taxzone" => "tz.description",
290 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
291 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
293 $query .= qq| ORDER by | . $sortorder;
295 my $sth = $dbh->prepare($query);
296 $sth->execute(@values) ||
297 $form->dberror($query . " (" . join(", ", @values) . ")");
301 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
302 $ref->{billed_amount} = $billed_amount{$ref->{id}};
303 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
304 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
305 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
306 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
307 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
308 $id{ $ref->{id} } = $ref->{id};
313 $main::lxdebug->leave_sub();
316 sub transactions_for_todo_list {
317 $main::lxdebug->enter_sub();
322 my $myconfig = \%main::myconfig;
323 my $form = $main::form;
325 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
327 my $query = qq|SELECT id FROM employee WHERE login = ?|;
328 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
331 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
332 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
337 LEFT JOIN customer c ON (oe.customer_id = c.id)
338 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
339 LEFT JOIN employee e ON (oe.employee_id = e.id)
340 WHERE (COALESCE(quotation, FALSE) = TRUE)
341 AND (COALESCE(closed, FALSE) = FALSE)
342 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
343 AND NOT (oe.reqdate ISNULL)
344 AND (oe.reqdate < current_date)
347 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
349 $main::lxdebug->leave_sub();
355 $main::lxdebug->enter_sub();
357 my ($self, $myconfig, $form) = @_;
359 # connect to database, turn off autocommit
360 my $dbh = $form->get_standard_dbh;
361 my $restricter = SL::HTML::Restrict->create;
363 my ($query, @values, $sth, $null);
364 my $exchangerate = 0;
366 my $all_units = AM->retrieve_units($myconfig, $form);
367 $form->{all_units} = $all_units;
369 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
372 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
373 unless ($form->{employee_id}) {
374 $form->get_employee($dbh);
377 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
379 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
380 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
381 $form->{$number_field} ||= $trans_number->create_unique;
384 $query = qq|DELETE FROM shipto | .
385 qq|WHERE trans_id = ? AND module = 'OE'|;
386 do_query($form, $dbh, $query, $form->{id});
390 $query = qq|SELECT nextval('id')|;
391 ($form->{id}) = selectrow_query($form, $dbh, $query);
393 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
394 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
411 my @processed_orderitems;
413 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
414 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
417 for my $i (1 .. $form->{rowcount}) {
419 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
421 if ($form->{"id_$i"}) {
424 $query = qq|SELECT unit FROM parts WHERE id = ?|;
425 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
428 if (defined($all_units->{$item_unit}->{factor}) &&
429 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
430 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
432 my $baseqty = $form->{"qty_$i"} * $basefactor;
434 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
435 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
437 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
439 # keep entered selling price
441 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
443 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
445 my $decimalplaces = ($dec > 2) ? $dec : 2;
447 # undo discount formatting
448 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
451 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
453 # round linetotal at least to 2 decimal places
454 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
455 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
457 $form->{"inventory_accno_$i"} *= 1;
458 $form->{"expense_accno_$i"} *= 1;
460 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
464 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
466 if ($form->{taxincluded}) {
467 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
468 $taxbase = $linetotal - $taxamount;
470 # we are not keeping a natural price, do not round
471 $form->{"sellprice_$i"} =
472 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
474 $taxamount = $linetotal * $taxrate;
475 $taxbase = $linetotal;
478 if ($form->round_amount($taxrate, 7) == 0) {
479 if ($form->{taxincluded}) {
480 foreach my $item (@taxaccounts) {
481 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
482 $taxaccounts{$item} += $taxamount;
483 $taxdiff += $taxamount;
484 $taxbase{$item} += $taxbase;
486 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
488 foreach my $item (@taxaccounts) {
489 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
490 $taxbase{$item} += $taxbase;
494 foreach my $item (@taxaccounts) {
495 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
496 $taxbase{$item} += $taxbase;
500 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
502 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
504 # Get pricegroup_id and save it. Unfortunately the interface
505 # also uses ID "0" for signalling that none is selected, but "0"
506 # must not be stored in the database. Therefore we cannot simply
508 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
510 $pricegroup_id = undef if !$pricegroup_id;
514 # save detail record in orderitems table
516 if (! $form->{"orderitems_id_$i"}) {
517 $query = qq|SELECT nextval('orderitemsid')|;
518 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
520 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
521 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
523 # get values for CVars from master data for new items
524 $cvars = CVar->get_custom_variables(dbh => $dbh,
526 trans_id => $form->{"id_$i"},
529 # get values for CVars from custom_variables for existing items
530 $cvars = CVar->get_custom_variables(dbh => $dbh,
532 sub_module => 'orderitems',
533 trans_id => $form->{"orderitems_id_$i"},
536 # map only non-editable CVars to form (editable ones are already there)
537 map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
539 my $orderitems_id = $form->{"orderitems_id_$i"};
540 push @processed_orderitems, $orderitems_id;
543 UPDATE orderitems SET
544 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
545 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
546 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
547 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
548 active_price_source = ?, active_discount_source = ?,
549 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
553 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
554 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
555 $form->{"qty_$i"}, $baseqty,
556 $fxsellprice, $form->{"discount_$i"},
557 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
558 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
559 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
560 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
561 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
562 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
563 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
564 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
565 conv_i($orderitems_id),
568 do_query($form, $dbh, $query, @values);
570 $form->{"sellprice_$i"} = $fxsellprice;
571 $form->{"discount_$i"} *= 100;
573 CVar->save_custom_variables(module => 'IC',
574 sub_module => 'orderitems',
575 trans_id => $orderitems_id,
576 configs => $ic_cvar_configs,
578 name_prefix => 'ic_',
579 name_postfix => "_$i",
583 # search for orphaned ids
584 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
585 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
586 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
588 if (scalar @orphaned_ids) {
589 # clean up orderitems
590 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
591 do_query($form, $dbh, $query, @orphaned_ids);
594 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
598 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
600 $amount = $form->round_amount($netamount + $tax, 2);
601 $netamount = $form->round_amount($netamount, 2);
603 if ($form->{currency} eq $form->{defaultcurrency}) {
604 $form->{exchangerate} = 1;
606 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
609 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
611 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
613 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
618 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
619 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
620 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
621 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
622 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
623 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
624 , order_probability = ?, expected_billing_date = ?
627 @values = ($form->{ordnumber} || '', $form->{quonumber},
628 $form->{cusordnumber}, conv_date($form->{transdate}),
629 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
630 $amount, $netamount, conv_date($reqdate),
631 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
632 $form->{shipvia}, $form->{notes}, $form->{intnotes},
633 $form->{currency}, $form->{closed} ? 't' : 'f',
634 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
635 $quotation, conv_i($form->{department_id}),
636 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
637 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
638 conv_i($form->{delivery_vendor_id}),
639 conv_i($form->{delivery_customer_id}),
640 conv_i($form->{delivery_term_id}),
641 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
642 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
643 $form->{transaction_description},
644 $form->{marge_total} * 1, $form->{marge_percent} * 1,
645 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
646 conv_i($form->{id}));
647 do_query($form, $dbh, $query, @values);
649 $form->{ordtotal} = $amount;
651 $form->{name} = $form->{ $form->{vc} };
652 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
655 if (!$form->{shipto_id}) {
656 $form->add_shipto($dbh, $form->{id}, "OE");
659 # save printed, emailed, queued
660 $form->save_status($dbh);
662 # Link this record to the records it was created from.
663 $form->{convert_from_oe_ids} =~ s/^\s+//;
664 $form->{convert_from_oe_ids} =~ s/\s+$//;
665 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
666 delete $form->{convert_from_oe_ids};
668 if (scalar @convert_from_oe_ids) {
669 RecordLinks->create_links('dbh' => $dbh,
671 'from_table' => 'oe',
672 'from_ids' => \@convert_from_oe_ids,
674 'to_id' => $form->{id},
677 $self->_close_quotations_rfqs('dbh' => $dbh,
678 'from_id' => \@convert_from_oe_ids,
679 'to_id' => $form->{id});
682 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
683 if ($form->{vc} eq 'customer') {
684 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
686 if ($form->{vc} eq 'vendor') {
687 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
691 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
692 "quonumber" : "ordnumber"};
694 Common::webdav_folder($form);
696 my $rc = $dbh->commit;
698 $self->save_periodic_invoices_config(dbh => $dbh,
699 oe_id => $form->{id},
700 config_yaml => $form->{periodic_invoices_config})
701 if ($form->{type} eq 'sales_order');
703 $main::lxdebug->leave_sub();
708 sub save_periodic_invoices_config {
709 my ($self, %params) = @_;
711 return if !$params{oe_id};
713 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
714 return if 'HASH' ne ref $config;
716 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
717 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
718 $obj->update_attributes(%{ $config });
721 sub load_periodic_invoice_config {
725 delete $form->{periodic_invoices_config};
728 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
731 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
732 print printer_id copies) };
733 $form->{periodic_invoices_config} = YAML::Dump($config);
738 sub _close_quotations_rfqs {
739 $main::lxdebug->enter_sub();
744 Common::check_params(\%params, qw(from_id to_id));
746 my $myconfig = \%main::myconfig;
747 my $form = $main::form;
749 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
751 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
752 my $sth = prepare_query($form, $dbh, $query);
754 do_statement($form, $sth, $query, conv_i($params{to_id}));
756 my ($quotation) = $sth->fetchrow_array();
759 $main::lxdebug->leave_sub();
765 foreach my $from_id (@{ $params{from_id} }) {
766 $from_id = conv_i($from_id);
767 do_statement($form, $sth, $query, $from_id);
768 ($quotation) = $sth->fetchrow_array();
769 push @close_ids, $from_id if ($quotation);
774 if (scalar @close_ids) {
775 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
776 do_query($form, $dbh, $query, @close_ids);
778 $dbh->commit() unless ($params{dbh});
781 $main::lxdebug->leave_sub();
785 $main::lxdebug->enter_sub();
787 my ($self, $myconfig, $form) = @_;
789 my $rc = SL::DB::Order->new->db->with_transaction(sub {
790 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
792 SL::DB::Order->new(id => $form->{id})->delete;
794 my $spool = $::lx_office_conf{paths}->{spool};
795 unlink map { "$spool/$_" } @spoolfiles if $spool;
800 $main::lxdebug->leave_sub();
806 $main::lxdebug->enter_sub();
808 my ($self, $myconfig, $form) = @_;
810 # connect to database
811 my $dbh = $form->get_standard_dbh;
813 my ($query, $query_add, @values, @ids, $sth);
815 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
817 push @ids, $form->{"trans_id_$_"}
818 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
819 } (1 .. $form->{"rowcount"});
821 if ($form->{rowcount} && scalar @ids) {
822 $form->{convert_from_oe_ids} = join ' ', @ids;
825 # if called in multi id mode, and still only got one id, switch back to single id
826 if ($form->{"rowcount"} and $#ids == 0) {
827 $form->{"id"} = $ids[0];
831 # and remember for the rest of the function
832 my $is_collective_order = scalar @ids;
835 my $wday = (localtime(time))[6];
836 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
838 # if we have a client configured interval for sales quotation, we add this
839 $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
840 $form->{type} eq 'sales_quotation' );
842 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
845 # get default accounts
846 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
847 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
848 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
849 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
850 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
853 my $ref = selectfirst_hashref_query($form, $dbh, $query);
854 map { $form->{$_} = $ref->{$_} } keys %$ref;
856 $form->{currency} = $form->get_default_currency($myconfig);
858 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
859 # we come from invoices, feel free.
860 $form->{reqdate} = $form->{deliverydate}
861 if ( $form->{deliverydate}
862 and $form->{callback} =~ /action=ar_transactions/);
864 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
866 if ($form->{id} or @ids) {
868 # retrieve order for single id
869 # NOTE: this query is intended to fetch all information only ONCE.
870 # so if any of these infos is important (or even different) for any item,
871 # it will be killed out and then has to be fetched from the item scope query further down
873 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
874 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
875 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
876 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
877 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
878 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
879 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
880 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
881 , o.order_probability, o.expected_billing_date
883 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
884 LEFT JOIN employee e ON (o.employee_id = e.id)
885 LEFT JOIN department d ON (o.department_id = d.id) | .
888 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
890 @values = $form->{id} ? ($form->{id}) : @ids;
891 $sth = prepare_execute_query($form, $dbh, $query, @values);
893 $ref = $sth->fetchrow_hashref("NAME_lc");
896 map { $form->{$_} = $ref->{$_} } keys %$ref;
898 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
900 # set all entries for multiple ids blank that yield different information
901 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
902 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
906 # if not given, fill transdate with current_date
907 $form->{transdate} = $form->current_date($myconfig)
908 unless $form->{transdate};
912 if ($form->{delivery_customer_id}) {
913 $query = qq|SELECT name FROM customer WHERE id = ?|;
914 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
917 if ($form->{delivery_vendor_id}) {
918 $query = qq|SELECT name FROM customer WHERE id = ?|;
919 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
922 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
924 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
925 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
927 $ref = $sth->fetchrow_hashref("NAME_lc");
929 map { $form->{$_} = $ref->{$_} } keys %$ref;
932 # get printed, emailed and queued
933 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
934 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
936 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
937 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
938 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
939 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
942 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
945 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
947 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
949 # retrieve individual items
950 # this query looks up all information about the items
951 # stuff different from the whole will not be overwritten, but saved with a suffix.
953 qq|SELECT o.id AS orderitems_id,
954 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
955 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
956 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
957 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
958 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
959 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
960 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
961 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
962 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
963 pr.projectnumber, p.formel,
964 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
966 JOIN parts p ON (o.parts_id = p.id)
967 JOIN oe ON (o.trans_id = oe.id)
968 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
969 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
970 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
971 LEFT JOIN project pr ON (o.project_id = pr.id)
972 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
974 ? qq|WHERE o.trans_id = ?|
975 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
976 qq|ORDER BY o.trans_id, o.position|;
978 @ids = $form->{id} ? ($form->{id}) : @ids;
979 $sth = prepare_execute_query($form, $dbh, $query, @values);
981 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
982 # Retrieve custom variables.
983 my $cvars = CVar->get_custom_variables(dbh => $dbh,
985 sub_module => 'orderitems',
986 trans_id => $ref->{orderitems_id},
988 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
991 if (!$ref->{"part_inventory_accno_id"}) {
992 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
994 delete($ref->{"part_inventory_accno_id"});
996 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
997 # unless already present there
998 # remove _oe entries afterwards
999 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1000 qw|ordnumber transdate cusordnumber|
1002 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1006 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1008 qq|SELECT accno AS inventory_accno, | .
1009 qq| new_chart_id AS inventory_new_chart, | .
1010 qq| date($transdate) - valid_from AS inventory_valid | .
1011 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1012 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1013 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1016 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1018 qq|SELECT accno AS income_accno, | .
1019 qq| new_chart_id AS income_new_chart, | .
1020 qq| date($transdate) - valid_from AS income_valid | .
1021 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1022 ($ref->{income_accno}, $ref->{income_new_chart},
1023 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1026 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1028 qq|SELECT accno AS expense_accno, | .
1029 qq| new_chart_id AS expense_new_chart, | .
1030 qq| date($transdate) - valid_from AS expense_valid | .
1031 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1032 ($ref->{expense_accno}, $ref->{expense_new_chart},
1033 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1036 # delete orderitems_id in collective orders, so that they get cloned no matter what
1037 delete $ref->{orderitems_id} if $is_collective_order;
1039 # get tax rates and description
1040 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1042 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1043 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1044 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1045 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1046 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1047 qq|ORDER BY c.accno|;
1048 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1049 $ref->{taxaccounts} = "";
1051 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1052 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1056 $ref->{taxaccounts} .= "$ptr->{accno} ";
1057 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1058 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1059 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1060 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1061 $form->{taxaccounts} .= "$ptr->{accno} ";
1066 chop $ref->{taxaccounts};
1068 push @{ $form->{form_details} }, $ref;
1075 # get last name used
1076 $form->lastname_used($dbh, $myconfig, $form->{vc})
1077 unless $form->{"$form->{vc}_id"};
1081 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1083 Common::webdav_folder($form);
1085 $self->load_periodic_invoice_config($form);
1087 my $rc = $dbh->commit;
1089 $main::lxdebug->leave_sub();
1094 sub retrieve_simple {
1095 $main::lxdebug->enter_sub();
1100 Common::check_params(\%params, qw(id));
1102 my $myconfig = \%main::myconfig;
1103 my $form = $main::form;
1105 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1107 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1108 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1110 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1111 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1113 $main::lxdebug->leave_sub();
1119 $main::lxdebug->enter_sub();
1121 my ($self, $myconfig, $form) = @_;
1123 # connect to database
1124 my $dbh = $form->get_standard_dbh;
1130 my $nodiscount_subtotal = 0;
1131 my $discount_subtotal = 0;
1134 my @partsgroup = ();
1137 my $subtotal_header = 0;
1138 my $subposition = 0;
1146 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1148 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1149 'departments' => 'ALL_DEPARTMENTS');
1152 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1153 $price_factors{$pfac->{id}} = $pfac;
1154 $pfac->{factor} *= 1;
1155 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1159 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1160 next unless $dept->{id} eq $form->{department_id};
1161 $form->{department} = $dept->{description};
1165 # sort items by partsgroup
1166 for $i (1 .. $form->{rowcount}) {
1168 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1169 $partsgroup = $form->{"partsgroup_$i"};
1171 push @partsgroup, [$i, $partsgroup];
1172 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1178 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1179 %projects_by_id = map { $_->id => $_ } @$projects;
1182 if ($projects_by_id{$form->{"globalproject_id"}}) {
1183 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1184 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1186 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1187 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1191 $form->{discount} = [];
1193 $form->{TEMPLATE_ARRAYS} = { };
1194 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1196 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1197 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1200 qw(runningnumber number description longdescription qty ship unit bin
1201 partnotes serialnumber reqdate sellprice listprice netprice
1202 discount p_discount discount_sub nodiscount_sub
1203 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1204 price_factor price_factor_name partsgroup weight lineweight);
1206 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1207 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1209 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1211 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1213 my $totalweight = 0;
1215 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1218 if ($item->[1] ne $sameitem) {
1219 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1220 $sameitem = $item->[1];
1222 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1225 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1227 if ($form->{"id_$i"} != 0) {
1229 # add number, description and qty to $form->{number}, ....
1231 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1232 $subtotal_header = $i;
1233 $position = int($position);
1236 } elsif ($subtotal_header) {
1238 $position = int($position);
1239 $position = $position.".".$subposition;
1241 $position = int($position);
1245 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1247 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1248 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1249 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1250 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1251 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1252 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1253 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1254 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1255 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1256 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1257 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1258 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1259 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1260 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1261 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1262 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1263 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1264 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1265 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1267 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1268 my ($dec) = ($sellprice =~ /\.(\d+)/);
1269 my $decimalplaces = max 2, length($dec);
1271 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1273 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1274 my $linetotal = $form->round_amount($linetotal_exact, 2);
1276 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1277 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1279 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1281 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1283 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1285 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1286 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1288 $linetotal = ($linetotal != 0) ? $linetotal : '';
1290 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1291 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1292 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1294 $form->{ordtotal} += $linetotal;
1295 $form->{nodiscount_total} += $nodiscount_linetotal;
1296 $form->{discount_total} += $discount;
1298 if ($subtotal_header) {
1299 $discount_subtotal += $linetotal;
1300 $nodiscount_subtotal += $nodiscount_linetotal;
1303 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1304 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1305 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1306 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1307 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1309 $discount_subtotal = 0;
1310 $nodiscount_subtotal = 0;
1311 $subtotal_header = 0;
1314 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1317 if (!$form->{"discount_$i"}) {
1318 $nodiscount += $linetotal;
1321 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1323 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1324 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1325 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1326 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1327 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1328 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1330 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1331 $totalweight += $lineweight;
1332 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1333 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1334 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1335 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1337 my ($taxamount, $taxbase);
1340 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1342 if ($form->{taxincluded}) {
1345 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1346 $taxbase = $linetotal / (1 + $taxrate);
1348 $taxamount = $linetotal * $taxrate;
1349 $taxbase = $linetotal;
1352 if ($taxamount != 0) {
1353 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1354 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1355 $taxbase{$accno} += $taxbase;
1359 $tax_rate = $taxrate * 100;
1360 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1362 if ($form->{"assembly_$i"}) {
1365 # get parts and push them onto the stack
1367 if ($form->{groupitems}) {
1368 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1370 $sortorder = qq|ORDER BY a.oid|;
1373 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1374 qq|pg.partsgroup | .
1375 qq|FROM assembly a | .
1376 qq| JOIN parts p ON (a.parts_id = p.id) | .
1377 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1378 qq| WHERE a.bom = '1' | .
1379 qq| AND a.id = ? | . $sortorder;
1380 @values = ($form->{"id_$i"});
1381 $sth = $dbh->prepare($query);
1382 $sth->execute(@values) || $form->dberror($query);
1384 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1385 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1386 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1387 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1388 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1391 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1392 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1398 if (! $form->{"orderitems_id_$i"}) {
1399 # get values for CVars from master data for new items
1400 $cvars = CVar->get_custom_variables(dbh => $dbh,
1402 trans_id => $form->{"id_$i"},
1405 # get values for CVars from custom_variables for existing items
1406 $cvars = CVar->get_custom_variables(dbh => $dbh,
1408 sub_module => 'orderitems',
1409 trans_id => $form->{"orderitems_id_$i"},
1412 # map only non-editable CVars to form (editable ones are already there)
1413 map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
1415 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1416 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1417 for @{ $ic_cvar_configs };
1419 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1423 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1424 $form->{totalweight_nofmt} = $totalweight;
1425 my $defaults = AM->get_defaults();
1426 $form->{weightunit} = $defaults->{weightunit};
1429 foreach $item (sort keys %taxaccounts) {
1430 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1432 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1433 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1434 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1435 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1436 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1437 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1438 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1440 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1441 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1442 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1445 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1446 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1447 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1448 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1450 if($form->{taxincluded}) {
1451 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1452 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1454 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1455 $form->{subtotal_nofmt} = $form->{ordtotal};
1458 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1461 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1463 if ($form->{type} =~ /_quotation/) {
1464 $form->set_payment_options($myconfig, $form->{quodate});
1466 $form->set_payment_options($myconfig, $form->{orddate});
1469 $form->{username} = $myconfig->{name};
1473 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1474 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1476 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1478 $main::lxdebug->leave_sub();
1481 sub project_description {
1482 $main::lxdebug->enter_sub();
1484 my ($self, $dbh, $id) = @_;
1486 my $query = qq|SELECT description FROM project WHERE id = ?|;
1487 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1489 $main::lxdebug->leave_sub();
1500 OE.pm - Order entry module
1504 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>.
1510 =item retrieve_simple PARAMS
1512 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1514 my $order = retrieve_simple(id => 2);