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 custom_variables
385 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
386 AND (sub_module = 'orderitems')
387 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
388 do_query($form, $dbh, $query, $form->{id});
390 $query = qq|DELETE FROM shipto | .
391 qq|WHERE trans_id = ? AND module = 'OE'|;
392 do_query($form, $dbh, $query, $form->{id});
396 $query = qq|SELECT nextval('id')|;
397 ($form->{id}) = selectrow_query($form, $dbh, $query);
399 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
400 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
417 my @processed_orderitems;
419 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
420 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
423 for my $i (1 .. $form->{rowcount}) {
425 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
427 if ($form->{"id_$i"}) {
430 $query = qq|SELECT unit FROM parts WHERE id = ?|;
431 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
434 if (defined($all_units->{$item_unit}->{factor}) &&
435 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
436 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
438 my $baseqty = $form->{"qty_$i"} * $basefactor;
440 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
441 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
443 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
445 # keep entered selling price
447 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
449 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
451 my $decimalplaces = ($dec > 2) ? $dec : 2;
453 # undo discount formatting
454 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
457 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
459 # round linetotal at least to 2 decimal places
460 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
461 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
463 $form->{"inventory_accno_$i"} *= 1;
464 $form->{"expense_accno_$i"} *= 1;
466 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
470 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
472 if ($form->{taxincluded}) {
473 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
474 $taxbase = $linetotal - $taxamount;
476 # we are not keeping a natural price, do not round
477 $form->{"sellprice_$i"} =
478 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
480 $taxamount = $linetotal * $taxrate;
481 $taxbase = $linetotal;
484 if ($form->round_amount($taxrate, 7) == 0) {
485 if ($form->{taxincluded}) {
486 foreach my $item (@taxaccounts) {
487 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
488 $taxaccounts{$item} += $taxamount;
489 $taxdiff += $taxamount;
490 $taxbase{$item} += $taxbase;
492 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
494 foreach my $item (@taxaccounts) {
495 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
496 $taxbase{$item} += $taxbase;
500 foreach my $item (@taxaccounts) {
501 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
502 $taxbase{$item} += $taxbase;
506 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
508 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
510 # Get pricegroup_id and save it. Unfortunately the interface
511 # also uses ID "0" for signalling that none is selected, but "0"
512 # must not be stored in the database. Therefore we cannot simply
514 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
516 $pricegroup_id = undef if !$pricegroup_id;
520 # save detail record in orderitems table
521 if (! $form->{"orderitems_id_$i"}) {
522 $query = qq|SELECT nextval('orderitemsid')|;
523 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
525 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
526 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
528 my $orderitems_id = $form->{"orderitems_id_$i"};
529 push @processed_orderitems, $orderitems_id;
532 UPDATE orderitems SET
533 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
534 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
535 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
536 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
537 active_price_source = ?, active_discount_source = ?,
538 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
542 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
543 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
544 $form->{"qty_$i"}, $baseqty,
545 $fxsellprice, $form->{"discount_$i"},
546 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
547 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
548 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
549 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
550 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
551 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
552 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
553 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
554 conv_i($orderitems_id),
557 do_query($form, $dbh, $query, @values);
559 $form->{"sellprice_$i"} = $fxsellprice;
560 $form->{"discount_$i"} *= 100;
562 CVar->save_custom_variables(module => 'IC',
563 sub_module => 'orderitems',
564 trans_id => $orderitems_id,
565 configs => $ic_cvar_configs,
567 name_prefix => 'ic_',
568 name_postfix => "_$i",
572 # search for orphaned ids
573 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
574 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
575 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
577 if (scalar @orphaned_ids) {
578 # clean up orderitems
579 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
580 do_query($form, $dbh, $query, @orphaned_ids);
583 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
587 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
589 $amount = $form->round_amount($netamount + $tax, 2);
590 $netamount = $form->round_amount($netamount, 2);
592 if ($form->{currency} eq $form->{defaultcurrency}) {
593 $form->{exchangerate} = 1;
595 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
598 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
600 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
602 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
607 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
608 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
609 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
610 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
611 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
612 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
613 , order_probability = ?, expected_billing_date = ?
616 @values = ($form->{ordnumber} || '', $form->{quonumber},
617 $form->{cusordnumber}, conv_date($form->{transdate}),
618 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
619 $amount, $netamount, conv_date($reqdate),
620 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
621 $form->{shipvia}, $form->{notes}, $form->{intnotes},
622 $form->{currency}, $form->{closed} ? 't' : 'f',
623 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
624 $quotation, conv_i($form->{department_id}),
625 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
626 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
627 conv_i($form->{delivery_vendor_id}),
628 conv_i($form->{delivery_customer_id}),
629 conv_i($form->{delivery_term_id}),
630 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
631 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
632 $form->{transaction_description},
633 $form->{marge_total} * 1, $form->{marge_percent} * 1,
634 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
635 conv_i($form->{id}));
636 do_query($form, $dbh, $query, @values);
638 $form->{ordtotal} = $amount;
640 $form->{name} = $form->{ $form->{vc} };
641 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
644 if (!$form->{shipto_id}) {
645 $form->add_shipto($dbh, $form->{id}, "OE");
648 # save printed, emailed, queued
649 $form->save_status($dbh);
651 # Link this record to the records it was created from.
652 $form->{convert_from_oe_ids} =~ s/^\s+//;
653 $form->{convert_from_oe_ids} =~ s/\s+$//;
654 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
655 delete $form->{convert_from_oe_ids};
657 if (scalar @convert_from_oe_ids) {
658 RecordLinks->create_links('dbh' => $dbh,
660 'from_table' => 'oe',
661 'from_ids' => \@convert_from_oe_ids,
663 'to_id' => $form->{id},
666 $self->_close_quotations_rfqs('dbh' => $dbh,
667 'from_id' => \@convert_from_oe_ids,
668 'to_id' => $form->{id});
671 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
672 if ($form->{vc} eq 'customer') {
673 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
675 if ($form->{vc} eq 'vendor') {
676 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
680 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
681 "quonumber" : "ordnumber"};
683 Common::webdav_folder($form);
685 my $rc = $dbh->commit;
687 $self->save_periodic_invoices_config(dbh => $dbh,
688 oe_id => $form->{id},
689 config_yaml => $form->{periodic_invoices_config})
690 if ($form->{type} eq 'sales_order');
692 $main::lxdebug->leave_sub();
697 sub save_periodic_invoices_config {
698 my ($self, %params) = @_;
700 return if !$params{oe_id};
702 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
703 return if 'HASH' ne ref $config;
705 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
706 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
707 $obj->update_attributes(%{ $config });
710 sub load_periodic_invoice_config {
714 delete $form->{periodic_invoices_config};
717 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
720 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
721 print printer_id copies) };
722 $form->{periodic_invoices_config} = YAML::Dump($config);
727 sub _close_quotations_rfqs {
728 $main::lxdebug->enter_sub();
733 Common::check_params(\%params, qw(from_id to_id));
735 my $myconfig = \%main::myconfig;
736 my $form = $main::form;
738 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
740 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
741 my $sth = prepare_query($form, $dbh, $query);
743 do_statement($form, $sth, $query, conv_i($params{to_id}));
745 my ($quotation) = $sth->fetchrow_array();
748 $main::lxdebug->leave_sub();
754 foreach my $from_id (@{ $params{from_id} }) {
755 $from_id = conv_i($from_id);
756 do_statement($form, $sth, $query, $from_id);
757 ($quotation) = $sth->fetchrow_array();
758 push @close_ids, $from_id if ($quotation);
763 if (scalar @close_ids) {
764 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
765 do_query($form, $dbh, $query, @close_ids);
767 $dbh->commit() unless ($params{dbh});
770 $main::lxdebug->leave_sub();
774 $main::lxdebug->enter_sub();
776 my ($self, $myconfig, $form) = @_;
778 my $rc = SL::DB::Order->new->db->with_transaction(sub {
779 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
781 SL::DB::Order->new(id => $form->{id})->delete;
783 my $spool = $::lx_office_conf{paths}->{spool};
784 unlink map { "$spool/$_" } @spoolfiles if $spool;
789 $main::lxdebug->leave_sub();
795 $main::lxdebug->enter_sub();
797 my ($self, $myconfig, $form) = @_;
799 # connect to database
800 my $dbh = $form->get_standard_dbh;
802 my ($query, $query_add, @values, @ids, $sth);
804 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
806 push @ids, $form->{"trans_id_$_"}
807 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
808 } (1 .. $form->{"rowcount"});
810 if ($form->{rowcount} && scalar @ids) {
811 $form->{convert_from_oe_ids} = join ' ', @ids;
814 # if called in multi id mode, and still only got one id, switch back to single id
815 if ($form->{"rowcount"} and $#ids == 0) {
816 $form->{"id"} = $ids[0];
820 # and remember for the rest of the function
821 my $is_collective_order = scalar @ids;
824 my $wday = (localtime(time))[6];
825 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
827 # if we have a client configured interval for sales quotation, we add this
828 $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
829 $form->{type} eq 'sales_quotation' );
831 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
834 # get default accounts
835 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
836 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
837 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
838 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
839 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
842 my $ref = selectfirst_hashref_query($form, $dbh, $query);
843 map { $form->{$_} = $ref->{$_} } keys %$ref;
845 $form->{currency} = $form->get_default_currency($myconfig);
847 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
848 # we come from invoices, feel free.
849 $form->{reqdate} = $form->{deliverydate}
850 if ( $form->{deliverydate}
851 and $form->{callback} =~ /action=ar_transactions/);
853 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
855 if ($form->{id} or @ids) {
857 # retrieve order for single id
858 # NOTE: this query is intended to fetch all information only ONCE.
859 # so if any of these infos is important (or even different) for any item,
860 # it will be killed out and then has to be fetched from the item scope query further down
862 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
863 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
864 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
865 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
866 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
867 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
868 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
869 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
870 , o.order_probability, o.expected_billing_date
872 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
873 LEFT JOIN employee e ON (o.employee_id = e.id)
874 LEFT JOIN department d ON (o.department_id = d.id) | .
877 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
879 @values = $form->{id} ? ($form->{id}) : @ids;
880 $sth = prepare_execute_query($form, $dbh, $query, @values);
882 $ref = $sth->fetchrow_hashref("NAME_lc");
885 map { $form->{$_} = $ref->{$_} } keys %$ref;
887 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
889 # set all entries for multiple ids blank that yield different information
890 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
891 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
895 # if not given, fill transdate with current_date
896 $form->{transdate} = $form->current_date($myconfig)
897 unless $form->{transdate};
901 if ($form->{delivery_customer_id}) {
902 $query = qq|SELECT name FROM customer WHERE id = ?|;
903 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
906 if ($form->{delivery_vendor_id}) {
907 $query = qq|SELECT name FROM customer WHERE id = ?|;
908 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
911 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
913 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
914 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
916 $ref = $sth->fetchrow_hashref("NAME_lc");
918 map { $form->{$_} = $ref->{$_} } keys %$ref;
921 # get printed, emailed and queued
922 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
923 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
925 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
926 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
927 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
928 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
931 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
934 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
936 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
938 # retrieve individual items
939 # this query looks up all information about the items
940 # stuff different from the whole will not be overwritten, but saved with a suffix.
942 qq|SELECT o.id AS orderitems_id,
943 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
944 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
945 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
946 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
947 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
948 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
949 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
950 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
951 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
952 pr.projectnumber, p.formel,
953 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
955 JOIN parts p ON (o.parts_id = p.id)
956 JOIN oe ON (o.trans_id = oe.id)
957 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
958 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)
959 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)
960 LEFT JOIN project pr ON (o.project_id = pr.id)
961 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
963 ? qq|WHERE o.trans_id = ?|
964 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
965 qq|ORDER BY o.trans_id, o.position|;
967 @ids = $form->{id} ? ($form->{id}) : @ids;
968 $sth = prepare_execute_query($form, $dbh, $query, @values);
970 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
971 # Retrieve custom variables.
972 my $cvars = CVar->get_custom_variables(dbh => $dbh,
974 sub_module => 'orderitems',
975 trans_id => $ref->{orderitems_id},
977 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
980 if (!$ref->{"part_inventory_accno_id"}) {
981 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
983 delete($ref->{"part_inventory_accno_id"});
985 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
986 # unless already present there
987 # remove _oe entries afterwards
988 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
989 qw|ordnumber transdate cusordnumber|
991 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
995 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
997 qq|SELECT accno AS inventory_accno, | .
998 qq| new_chart_id AS inventory_new_chart, | .
999 qq| date($transdate) - valid_from AS inventory_valid | .
1000 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1001 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1002 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1005 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1007 qq|SELECT accno AS income_accno, | .
1008 qq| new_chart_id AS income_new_chart, | .
1009 qq| date($transdate) - valid_from AS income_valid | .
1010 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1011 ($ref->{income_accno}, $ref->{income_new_chart},
1012 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1015 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1017 qq|SELECT accno AS expense_accno, | .
1018 qq| new_chart_id AS expense_new_chart, | .
1019 qq| date($transdate) - valid_from AS expense_valid | .
1020 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1021 ($ref->{expense_accno}, $ref->{expense_new_chart},
1022 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1025 # delete orderitems_id in collective orders, so that they get cloned no matter what
1026 delete $ref->{orderitems_id} if $is_collective_order;
1028 # get tax rates and description
1029 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1031 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1032 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1033 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1034 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1035 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1036 qq|ORDER BY c.accno|;
1037 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1038 $ref->{taxaccounts} = "";
1040 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1041 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1045 $ref->{taxaccounts} .= "$ptr->{accno} ";
1046 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1047 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1048 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1049 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1050 $form->{taxaccounts} .= "$ptr->{accno} ";
1055 chop $ref->{taxaccounts};
1057 push @{ $form->{form_details} }, $ref;
1064 # get last name used
1065 $form->lastname_used($dbh, $myconfig, $form->{vc})
1066 unless $form->{"$form->{vc}_id"};
1070 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1072 Common::webdav_folder($form);
1074 $self->load_periodic_invoice_config($form);
1076 my $rc = $dbh->commit;
1078 $main::lxdebug->leave_sub();
1083 sub retrieve_simple {
1084 $main::lxdebug->enter_sub();
1089 Common::check_params(\%params, qw(id));
1091 my $myconfig = \%main::myconfig;
1092 my $form = $main::form;
1094 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1096 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1097 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1099 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1100 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1102 $main::lxdebug->leave_sub();
1108 $main::lxdebug->enter_sub();
1110 my ($self, $myconfig, $form) = @_;
1112 # connect to database
1113 my $dbh = $form->get_standard_dbh;
1119 my $nodiscount_subtotal = 0;
1120 my $discount_subtotal = 0;
1123 my @partsgroup = ();
1126 my $subtotal_header = 0;
1127 my $subposition = 0;
1135 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1137 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1138 'departments' => 'ALL_DEPARTMENTS');
1141 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1142 $price_factors{$pfac->{id}} = $pfac;
1143 $pfac->{factor} *= 1;
1144 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1148 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1149 next unless $dept->{id} eq $form->{department_id};
1150 $form->{department} = $dept->{description};
1154 # sort items by partsgroup
1155 for $i (1 .. $form->{rowcount}) {
1157 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1158 $partsgroup = $form->{"partsgroup_$i"};
1160 push @partsgroup, [$i, $partsgroup];
1161 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1167 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1168 %projects_by_id = map { $_->id => $_ } @$projects;
1171 if ($projects_by_id{$form->{"globalproject_id"}}) {
1172 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1173 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1175 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1176 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1180 $form->{discount} = [];
1182 $form->{TEMPLATE_ARRAYS} = { };
1183 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1185 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1186 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1189 qw(runningnumber number description longdescription qty ship unit bin
1190 partnotes serialnumber reqdate sellprice listprice netprice
1191 discount p_discount discount_sub nodiscount_sub
1192 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1193 price_factor price_factor_name partsgroup weight lineweight);
1195 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1196 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1198 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1200 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1202 my $totalweight = 0;
1204 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1207 if ($item->[1] ne $sameitem) {
1208 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1209 $sameitem = $item->[1];
1211 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1214 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1216 if ($form->{"id_$i"} != 0) {
1218 # add number, description and qty to $form->{number}, ....
1220 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1221 $subtotal_header = $i;
1222 $position = int($position);
1225 } elsif ($subtotal_header) {
1227 $position = int($position);
1228 $position = $position.".".$subposition;
1230 $position = int($position);
1234 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1236 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1237 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1238 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1239 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1240 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1241 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1242 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1243 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1244 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1245 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1246 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1247 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1248 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1249 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1250 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1251 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1252 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1253 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1254 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1256 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1257 my ($dec) = ($sellprice =~ /\.(\d+)/);
1258 my $decimalplaces = max 2, length($dec);
1260 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1262 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1263 my $linetotal = $form->round_amount($linetotal_exact, 2);
1265 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1266 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1268 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1270 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1272 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1274 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1275 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1277 $linetotal = ($linetotal != 0) ? $linetotal : '';
1279 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1280 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1281 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1283 $form->{ordtotal} += $linetotal;
1284 $form->{nodiscount_total} += $nodiscount_linetotal;
1285 $form->{discount_total} += $discount;
1287 if ($subtotal_header) {
1288 $discount_subtotal += $linetotal;
1289 $nodiscount_subtotal += $nodiscount_linetotal;
1292 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1293 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1294 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1295 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1296 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1298 $discount_subtotal = 0;
1299 $nodiscount_subtotal = 0;
1300 $subtotal_header = 0;
1303 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1306 if (!$form->{"discount_$i"}) {
1307 $nodiscount += $linetotal;
1310 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1312 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1313 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1314 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1315 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1316 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1317 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1319 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1320 $totalweight += $lineweight;
1321 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1322 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1323 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1324 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1326 my ($taxamount, $taxbase);
1329 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1331 if ($form->{taxincluded}) {
1334 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1335 $taxbase = $linetotal / (1 + $taxrate);
1337 $taxamount = $linetotal * $taxrate;
1338 $taxbase = $linetotal;
1341 if ($taxamount != 0) {
1342 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1343 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1344 $taxbase{$accno} += $taxbase;
1348 $tax_rate = $taxrate * 100;
1349 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1351 if ($form->{"assembly_$i"}) {
1354 # get parts and push them onto the stack
1356 if ($form->{groupitems}) {
1357 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1359 $sortorder = qq|ORDER BY a.oid|;
1362 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1363 qq|pg.partsgroup | .
1364 qq|FROM assembly a | .
1365 qq| JOIN parts p ON (a.parts_id = p.id) | .
1366 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1367 qq| WHERE a.bom = '1' | .
1368 qq| AND a.id = ? | . $sortorder;
1369 @values = ($form->{"id_$i"});
1370 $sth = $dbh->prepare($query);
1371 $sth->execute(@values) || $form->dberror($query);
1373 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1374 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1375 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1376 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1377 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1380 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1381 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1386 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1387 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1388 for @{ $ic_cvar_configs };
1390 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1394 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1395 $form->{totalweight_nofmt} = $totalweight;
1396 my $defaults = AM->get_defaults();
1397 $form->{weightunit} = $defaults->{weightunit};
1400 foreach $item (sort keys %taxaccounts) {
1401 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1403 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1404 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1405 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1406 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1407 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1408 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1409 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1411 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1412 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1413 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1416 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1417 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1418 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1419 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1421 if($form->{taxincluded}) {
1422 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1423 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1425 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1426 $form->{subtotal_nofmt} = $form->{ordtotal};
1429 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1432 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1434 if ($form->{type} =~ /_quotation/) {
1435 $form->set_payment_options($myconfig, $form->{quodate});
1437 $form->set_payment_options($myconfig, $form->{orddate});
1440 $form->{username} = $myconfig->{name};
1444 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1445 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1447 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1449 $main::lxdebug->leave_sub();
1452 sub project_description {
1453 $main::lxdebug->enter_sub();
1455 my ($self, $dbh, $id) = @_;
1457 my $query = qq|SELECT description FROM project WHERE id = ?|;
1458 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1460 $main::lxdebug->leave_sub();
1471 OE.pm - Order entry module
1475 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>.
1481 =item retrieve_simple PARAMS
1483 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1485 my $order = retrieve_simple(id => 2);