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
515 if (! $form->{"orderitems_id_$i"}) {
516 $query = qq|SELECT nextval('orderitemsid')|;
517 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
519 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
520 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
522 my $orderitems_id = $form->{"orderitems_id_$i"};
523 push @processed_orderitems, $orderitems_id;
526 UPDATE orderitems SET
527 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
528 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
529 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
530 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
531 active_price_source = ?, active_discount_source = ?,
532 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
536 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
537 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
538 $form->{"qty_$i"}, $baseqty,
539 $fxsellprice, $form->{"discount_$i"},
540 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
541 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
542 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
543 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
544 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
545 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
546 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
547 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
548 conv_i($orderitems_id),
551 do_query($form, $dbh, $query, @values);
553 $form->{"sellprice_$i"} = $fxsellprice;
554 $form->{"discount_$i"} *= 100;
556 CVar->save_custom_variables(module => 'IC',
557 sub_module => 'orderitems',
558 trans_id => $orderitems_id,
559 configs => $ic_cvar_configs,
561 name_prefix => 'ic_',
562 name_postfix => "_$i",
566 # search for orphaned ids
567 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
568 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
569 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
571 if (scalar @orphaned_ids) {
572 # clean up orderitems
573 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
574 do_query($form, $dbh, $query, @orphaned_ids);
577 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
581 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
583 $amount = $form->round_amount($netamount + $tax, 2);
584 $netamount = $form->round_amount($netamount, 2);
586 if ($form->{currency} eq $form->{defaultcurrency}) {
587 $form->{exchangerate} = 1;
589 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
592 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
594 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
596 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
601 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
602 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
603 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
604 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
605 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
606 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
607 , order_probability = ?, expected_billing_date = ?
610 @values = ($form->{ordnumber} || '', $form->{quonumber},
611 $form->{cusordnumber}, conv_date($form->{transdate}),
612 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
613 $amount, $netamount, conv_date($reqdate),
614 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
615 $form->{shipvia}, $form->{notes}, $form->{intnotes},
616 $form->{currency}, $form->{closed} ? 't' : 'f',
617 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
618 $quotation, conv_i($form->{department_id}),
619 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
620 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
621 conv_i($form->{delivery_vendor_id}),
622 conv_i($form->{delivery_customer_id}),
623 conv_i($form->{delivery_term_id}),
624 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
625 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
626 $form->{transaction_description},
627 $form->{marge_total} * 1, $form->{marge_percent} * 1,
628 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
629 conv_i($form->{id}));
630 do_query($form, $dbh, $query, @values);
632 $form->{ordtotal} = $amount;
634 $form->{name} = $form->{ $form->{vc} };
635 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
638 if (!$form->{shipto_id}) {
639 $form->add_shipto($dbh, $form->{id}, "OE");
642 # save printed, emailed, queued
643 $form->save_status($dbh);
645 # Link this record to the records it was created from.
646 $form->{convert_from_oe_ids} =~ s/^\s+//;
647 $form->{convert_from_oe_ids} =~ s/\s+$//;
648 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
649 delete $form->{convert_from_oe_ids};
651 if (scalar @convert_from_oe_ids) {
652 RecordLinks->create_links('dbh' => $dbh,
654 'from_table' => 'oe',
655 'from_ids' => \@convert_from_oe_ids,
657 'to_id' => $form->{id},
660 $self->_close_quotations_rfqs('dbh' => $dbh,
661 'from_id' => \@convert_from_oe_ids,
662 'to_id' => $form->{id});
665 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
666 if ($form->{vc} eq 'customer') {
667 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
669 if ($form->{vc} eq 'vendor') {
670 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
674 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
675 "quonumber" : "ordnumber"};
677 Common::webdav_folder($form);
679 my $rc = $dbh->commit;
681 $self->save_periodic_invoices_config(dbh => $dbh,
682 oe_id => $form->{id},
683 config_yaml => $form->{periodic_invoices_config})
684 if ($form->{type} eq 'sales_order');
686 $main::lxdebug->leave_sub();
691 sub save_periodic_invoices_config {
692 my ($self, %params) = @_;
694 return if !$params{oe_id};
696 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
697 return if 'HASH' ne ref $config;
699 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
700 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
701 $obj->update_attributes(%{ $config });
704 sub load_periodic_invoice_config {
708 delete $form->{periodic_invoices_config};
711 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
714 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
715 print printer_id copies) };
716 $form->{periodic_invoices_config} = YAML::Dump($config);
721 sub _close_quotations_rfqs {
722 $main::lxdebug->enter_sub();
727 Common::check_params(\%params, qw(from_id to_id));
729 my $myconfig = \%main::myconfig;
730 my $form = $main::form;
732 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
734 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
735 my $sth = prepare_query($form, $dbh, $query);
737 do_statement($form, $sth, $query, conv_i($params{to_id}));
739 my ($quotation) = $sth->fetchrow_array();
742 $main::lxdebug->leave_sub();
748 foreach my $from_id (@{ $params{from_id} }) {
749 $from_id = conv_i($from_id);
750 do_statement($form, $sth, $query, $from_id);
751 ($quotation) = $sth->fetchrow_array();
752 push @close_ids, $from_id if ($quotation);
757 if (scalar @close_ids) {
758 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
759 do_query($form, $dbh, $query, @close_ids);
761 $dbh->commit() unless ($params{dbh});
764 $main::lxdebug->leave_sub();
768 $main::lxdebug->enter_sub();
770 my ($self, $myconfig, $form) = @_;
772 my $rc = SL::DB::Order->new->db->with_transaction(sub {
773 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
775 SL::DB::Order->new(id => $form->{id})->delete;
777 my $spool = $::lx_office_conf{paths}->{spool};
778 unlink map { "$spool/$_" } @spoolfiles if $spool;
783 $main::lxdebug->leave_sub();
789 $main::lxdebug->enter_sub();
791 my ($self, $myconfig, $form) = @_;
793 # connect to database
794 my $dbh = $form->get_standard_dbh;
796 my ($query, $query_add, @values, @ids, $sth);
798 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
800 push @ids, $form->{"trans_id_$_"}
801 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
802 } (1 .. $form->{"rowcount"});
804 if ($form->{rowcount} && scalar @ids) {
805 $form->{convert_from_oe_ids} = join ' ', @ids;
808 # if called in multi id mode, and still only got one id, switch back to single id
809 if ($form->{"rowcount"} and $#ids == 0) {
810 $form->{"id"} = $ids[0];
814 # and remember for the rest of the function
815 my $is_collective_order = scalar @ids;
818 my $wday = (localtime(time))[6];
819 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
821 # if we have a client configured interval for sales quotation, we add this
822 $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
823 $form->{type} eq 'sales_quotation' );
825 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
828 # get default accounts
829 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
830 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
831 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
832 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
833 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
836 my $ref = selectfirst_hashref_query($form, $dbh, $query);
837 map { $form->{$_} = $ref->{$_} } keys %$ref;
839 $form->{currency} = $form->get_default_currency($myconfig);
841 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
842 # we come from invoices, feel free.
843 $form->{reqdate} = $form->{deliverydate}
844 if ( $form->{deliverydate}
845 and $form->{callback} =~ /action=ar_transactions/);
847 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
849 if ($form->{id} or @ids) {
851 # retrieve order for single id
852 # NOTE: this query is intended to fetch all information only ONCE.
853 # so if any of these infos is important (or even different) for any item,
854 # it will be killed out and then has to be fetched from the item scope query further down
856 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
857 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
858 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
859 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
860 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
861 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
862 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
863 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
864 , o.order_probability, o.expected_billing_date
866 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
867 LEFT JOIN employee e ON (o.employee_id = e.id)
868 LEFT JOIN department d ON (o.department_id = d.id) | .
871 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
873 @values = $form->{id} ? ($form->{id}) : @ids;
874 $sth = prepare_execute_query($form, $dbh, $query, @values);
876 $ref = $sth->fetchrow_hashref("NAME_lc");
879 map { $form->{$_} = $ref->{$_} } keys %$ref;
881 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
883 # set all entries for multiple ids blank that yield different information
884 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
885 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
889 # if not given, fill transdate with current_date
890 $form->{transdate} = $form->current_date($myconfig)
891 unless $form->{transdate};
895 if ($form->{delivery_customer_id}) {
896 $query = qq|SELECT name FROM customer WHERE id = ?|;
897 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
900 if ($form->{delivery_vendor_id}) {
901 $query = qq|SELECT name FROM customer WHERE id = ?|;
902 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
905 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
907 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
908 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
910 $ref = $sth->fetchrow_hashref("NAME_lc");
912 map { $form->{$_} = $ref->{$_} } keys %$ref;
915 # get printed, emailed and queued
916 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
917 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
919 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
920 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
921 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
922 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
925 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
928 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
930 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
932 # retrieve individual items
933 # this query looks up all information about the items
934 # stuff different from the whole will not be overwritten, but saved with a suffix.
936 qq|SELECT o.id AS orderitems_id,
937 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
938 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
939 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
940 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
941 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
942 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
943 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
944 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
945 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
946 pr.projectnumber, p.formel,
947 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
949 JOIN parts p ON (o.parts_id = p.id)
950 JOIN oe ON (o.trans_id = oe.id)
951 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
952 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)
953 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)
954 LEFT JOIN project pr ON (o.project_id = pr.id)
955 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
957 ? qq|WHERE o.trans_id = ?|
958 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
959 qq|ORDER BY o.trans_id, o.position|;
961 @ids = $form->{id} ? ($form->{id}) : @ids;
962 $sth = prepare_execute_query($form, $dbh, $query, @values);
964 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
965 # Retrieve custom variables.
966 my $cvars = CVar->get_custom_variables(dbh => $dbh,
968 sub_module => 'orderitems',
969 trans_id => $ref->{orderitems_id},
971 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
974 if (!$ref->{"part_inventory_accno_id"}) {
975 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
977 delete($ref->{"part_inventory_accno_id"});
979 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
980 # unless already present there
981 # remove _oe entries afterwards
982 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
983 qw|ordnumber transdate cusordnumber|
985 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
989 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
991 qq|SELECT accno AS inventory_accno, | .
992 qq| new_chart_id AS inventory_new_chart, | .
993 qq| date($transdate) - valid_from AS inventory_valid | .
994 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
995 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
996 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
999 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1001 qq|SELECT accno AS income_accno, | .
1002 qq| new_chart_id AS income_new_chart, | .
1003 qq| date($transdate) - valid_from AS income_valid | .
1004 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1005 ($ref->{income_accno}, $ref->{income_new_chart},
1006 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1009 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1011 qq|SELECT accno AS expense_accno, | .
1012 qq| new_chart_id AS expense_new_chart, | .
1013 qq| date($transdate) - valid_from AS expense_valid | .
1014 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1015 ($ref->{expense_accno}, $ref->{expense_new_chart},
1016 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1019 # delete orderitems_id in collective orders, so that they get cloned no matter what
1020 delete $ref->{orderitems_id} if $is_collective_order;
1022 # get tax rates and description
1023 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1025 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1026 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1027 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1028 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1029 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1030 qq|ORDER BY c.accno|;
1031 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1032 $ref->{taxaccounts} = "";
1034 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1035 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1039 $ref->{taxaccounts} .= "$ptr->{accno} ";
1040 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1041 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1042 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1043 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1044 $form->{taxaccounts} .= "$ptr->{accno} ";
1049 chop $ref->{taxaccounts};
1051 push @{ $form->{form_details} }, $ref;
1058 # get last name used
1059 $form->lastname_used($dbh, $myconfig, $form->{vc})
1060 unless $form->{"$form->{vc}_id"};
1064 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1066 Common::webdav_folder($form);
1068 $self->load_periodic_invoice_config($form);
1070 my $rc = $dbh->commit;
1072 $main::lxdebug->leave_sub();
1077 sub retrieve_simple {
1078 $main::lxdebug->enter_sub();
1083 Common::check_params(\%params, qw(id));
1085 my $myconfig = \%main::myconfig;
1086 my $form = $main::form;
1088 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1090 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1091 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1093 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1094 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1096 $main::lxdebug->leave_sub();
1102 $main::lxdebug->enter_sub();
1104 my ($self, $myconfig, $form) = @_;
1106 # connect to database
1107 my $dbh = $form->get_standard_dbh;
1113 my $nodiscount_subtotal = 0;
1114 my $discount_subtotal = 0;
1117 my @partsgroup = ();
1120 my $subtotal_header = 0;
1121 my $subposition = 0;
1129 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1131 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1132 'departments' => 'ALL_DEPARTMENTS');
1135 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1136 $price_factors{$pfac->{id}} = $pfac;
1137 $pfac->{factor} *= 1;
1138 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1142 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1143 next unless $dept->{id} eq $form->{department_id};
1144 $form->{department} = $dept->{description};
1148 # sort items by partsgroup
1149 for $i (1 .. $form->{rowcount}) {
1151 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1152 $partsgroup = $form->{"partsgroup_$i"};
1154 push @partsgroup, [$i, $partsgroup];
1155 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1161 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1162 %projects_by_id = map { $_->id => $_ } @$projects;
1165 if ($projects_by_id{$form->{"globalproject_id"}}) {
1166 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1167 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1169 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1170 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1174 $form->{discount} = [];
1176 $form->{TEMPLATE_ARRAYS} = { };
1177 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1179 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1180 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1183 qw(runningnumber number description longdescription qty ship unit bin
1184 partnotes serialnumber reqdate sellprice listprice netprice
1185 discount p_discount discount_sub nodiscount_sub
1186 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1187 price_factor price_factor_name partsgroup weight lineweight);
1189 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1190 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1192 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1194 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1196 my $totalweight = 0;
1198 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1201 if ($item->[1] ne $sameitem) {
1202 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1203 $sameitem = $item->[1];
1205 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1208 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1210 if ($form->{"id_$i"} != 0) {
1212 # add number, description and qty to $form->{number}, ....
1214 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1215 $subtotal_header = $i;
1216 $position = int($position);
1219 } elsif ($subtotal_header) {
1221 $position = int($position);
1222 $position = $position.".".$subposition;
1224 $position = int($position);
1228 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1230 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1231 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1232 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1233 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1234 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1235 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1236 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1237 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1238 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1239 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1240 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1241 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1242 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1243 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1244 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1245 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1246 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1247 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1248 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1250 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1251 my ($dec) = ($sellprice =~ /\.(\d+)/);
1252 my $decimalplaces = max 2, length($dec);
1254 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1256 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1257 my $linetotal = $form->round_amount($linetotal_exact, 2);
1259 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1260 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1262 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1264 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1266 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1268 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1269 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1271 $linetotal = ($linetotal != 0) ? $linetotal : '';
1273 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1274 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1275 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1277 $form->{ordtotal} += $linetotal;
1278 $form->{nodiscount_total} += $nodiscount_linetotal;
1279 $form->{discount_total} += $discount;
1281 if ($subtotal_header) {
1282 $discount_subtotal += $linetotal;
1283 $nodiscount_subtotal += $nodiscount_linetotal;
1286 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1287 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1288 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1289 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1290 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1292 $discount_subtotal = 0;
1293 $nodiscount_subtotal = 0;
1294 $subtotal_header = 0;
1297 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1300 if (!$form->{"discount_$i"}) {
1301 $nodiscount += $linetotal;
1304 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1306 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1307 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1308 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1309 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1310 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1311 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1313 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1314 $totalweight += $lineweight;
1315 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1316 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1317 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1318 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1320 my ($taxamount, $taxbase);
1323 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1325 if ($form->{taxincluded}) {
1328 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1329 $taxbase = $linetotal / (1 + $taxrate);
1331 $taxamount = $linetotal * $taxrate;
1332 $taxbase = $linetotal;
1335 if ($taxamount != 0) {
1336 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1337 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1338 $taxbase{$accno} += $taxbase;
1342 $tax_rate = $taxrate * 100;
1343 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1345 if ($form->{"assembly_$i"}) {
1348 # get parts and push them onto the stack
1350 if ($form->{groupitems}) {
1351 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1353 $sortorder = qq|ORDER BY a.oid|;
1356 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1357 qq|pg.partsgroup | .
1358 qq|FROM assembly a | .
1359 qq| JOIN parts p ON (a.parts_id = p.id) | .
1360 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1361 qq| WHERE a.bom = '1' | .
1362 qq| AND a.id = ? | . $sortorder;
1363 @values = ($form->{"id_$i"});
1364 $sth = $dbh->prepare($query);
1365 $sth->execute(@values) || $form->dberror($query);
1367 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1368 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1369 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1370 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1371 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1374 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1375 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1380 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1381 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1382 for @{ $ic_cvar_configs };
1384 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1388 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1389 $form->{totalweight_nofmt} = $totalweight;
1390 my $defaults = AM->get_defaults();
1391 $form->{weightunit} = $defaults->{weightunit};
1394 foreach $item (sort keys %taxaccounts) {
1395 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1397 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1398 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1399 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1400 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1401 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1402 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1403 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1405 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1406 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1407 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1410 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1411 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1412 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1413 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1415 if($form->{taxincluded}) {
1416 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1417 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1419 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1420 $form->{subtotal_nofmt} = $form->{ordtotal};
1423 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1426 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1428 if ($form->{type} =~ /_quotation/) {
1429 $form->set_payment_options($myconfig, $form->{quodate});
1431 $form->set_payment_options($myconfig, $form->{orddate});
1434 $form->{username} = $myconfig->{name};
1438 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1439 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1441 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1443 $main::lxdebug->leave_sub();
1446 sub project_description {
1447 $main::lxdebug->enter_sub();
1449 my ($self, $dbh, $id) = @_;
1451 my $query = qq|SELECT description FROM project WHERE id = ?|;
1452 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1454 $main::lxdebug->leave_sub();
1465 OE.pm - Order entry module
1469 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>.
1475 =item retrieve_simple PARAMS
1477 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1479 my $order = retrieve_simple(id => 2);