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 orderitems WHERE trans_id = ?|;
391 do_query($form, $dbh, $query, $form->{id});
393 $query = qq|DELETE FROM shipto | .
394 qq|WHERE trans_id = ? AND module = 'OE'|;
395 do_query($form, $dbh, $query, $form->{id});
399 $query = qq|SELECT nextval('id')|;
400 ($form->{id}) = selectrow_query($form, $dbh, $query);
402 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
403 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
421 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
422 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
425 for my $i (1 .. $form->{rowcount}) {
427 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
429 if ($form->{"id_$i"}) {
432 $query = qq|SELECT unit FROM parts WHERE id = ?|;
433 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
436 if (defined($all_units->{$item_unit}->{factor}) &&
437 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
438 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
440 my $baseqty = $form->{"qty_$i"} * $basefactor;
442 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
443 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
445 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
447 # keep entered selling price
449 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
451 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
453 my $decimalplaces = ($dec > 2) ? $dec : 2;
455 # undo discount formatting
456 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
459 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
461 # round linetotal at least to 2 decimal places
462 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
463 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
465 $form->{"inventory_accno_$i"} *= 1;
466 $form->{"expense_accno_$i"} *= 1;
468 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
472 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
474 if ($form->{taxincluded}) {
475 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
476 $taxbase = $linetotal - $taxamount;
478 # we are not keeping a natural price, do not round
479 $form->{"sellprice_$i"} =
480 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
482 $taxamount = $linetotal * $taxrate;
483 $taxbase = $linetotal;
486 if ($form->round_amount($taxrate, 7) == 0) {
487 if ($form->{taxincluded}) {
488 foreach my $item (@taxaccounts) {
489 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
490 $taxaccounts{$item} += $taxamount;
491 $taxdiff += $taxamount;
492 $taxbase{$item} += $taxbase;
494 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
496 foreach my $item (@taxaccounts) {
497 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
498 $taxbase{$item} += $taxbase;
502 foreach my $item (@taxaccounts) {
503 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
504 $taxbase{$item} += $taxbase;
508 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
510 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
512 # Get pricegroup_id and save it. Unfortunately the interface
513 # also uses ID "0" for signalling that none is selected, but "0"
514 # must not be stored in the database. Therefore we cannot simply
516 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
518 $pricegroup_id = undef if !$pricegroup_id;
520 # save detail record in orderitems table
521 my $orderitems_id = $form->{"orderitems_id_$i"};
522 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
525 $query = qq|INSERT INTO orderitems (
526 id, trans_id, parts_id, description, longdescription, qty, base_qty,
527 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
528 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
529 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
530 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
531 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
533 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
534 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
535 $form->{"qty_$i"}, $baseqty,
536 $fxsellprice, $form->{"discount_$i"},
537 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
538 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
539 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
540 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
541 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
542 $form->{"lastcost_$i"},
543 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
544 conv_i($form->{"marge_price_factor_$i"}));
545 do_query($form, $dbh, $query, @values);
547 $form->{"sellprice_$i"} = $fxsellprice;
548 $form->{"discount_$i"} *= 100;
550 CVar->save_custom_variables(module => 'IC',
551 sub_module => 'orderitems',
552 trans_id => $orderitems_id,
553 configs => $ic_cvar_configs,
555 name_prefix => 'ic_',
556 name_postfix => "_$i",
561 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
565 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
567 $amount = $form->round_amount($netamount + $tax, 2);
568 $netamount = $form->round_amount($netamount, 2);
570 if ($form->{currency} eq $form->{defaultcurrency}) {
571 $form->{exchangerate} = 1;
573 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
576 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
578 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
580 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
585 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
586 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
587 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
588 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
589 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
590 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
591 , order_probability = ?, expected_billing_date = ?
594 @values = ($form->{ordnumber} || '', $form->{quonumber},
595 $form->{cusordnumber}, conv_date($form->{transdate}),
596 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
597 $amount, $netamount, conv_date($reqdate),
598 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
599 $form->{shipvia}, $form->{notes}, $form->{intnotes},
600 $form->{currency}, $form->{closed} ? 't' : 'f',
601 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
602 $quotation, conv_i($form->{department_id}),
603 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
604 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
605 conv_i($form->{delivery_vendor_id}),
606 conv_i($form->{delivery_customer_id}),
607 conv_i($form->{delivery_term_id}),
608 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
609 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
610 $form->{transaction_description},
611 $form->{marge_total} * 1, $form->{marge_percent} * 1,
612 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
613 conv_i($form->{id}));
614 do_query($form, $dbh, $query, @values);
616 $form->{ordtotal} = $amount;
618 $form->{name} = $form->{ $form->{vc} };
619 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
622 if (!$form->{shipto_id}) {
623 $form->add_shipto($dbh, $form->{id}, "OE");
626 # save printed, emailed, queued
627 $form->save_status($dbh);
629 # Link this record to the records it was created from.
630 $form->{convert_from_oe_ids} =~ s/^\s+//;
631 $form->{convert_from_oe_ids} =~ s/\s+$//;
632 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
633 delete $form->{convert_from_oe_ids};
635 if (scalar @convert_from_oe_ids) {
636 RecordLinks->create_links('dbh' => $dbh,
638 'from_table' => 'oe',
639 'from_ids' => \@convert_from_oe_ids,
641 'to_id' => $form->{id},
644 $self->_close_quotations_rfqs('dbh' => $dbh,
645 'from_id' => \@convert_from_oe_ids,
646 'to_id' => $form->{id});
649 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
650 if ($form->{vc} eq 'customer') {
651 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
653 if ($form->{vc} eq 'vendor') {
654 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
658 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
659 "quonumber" : "ordnumber"};
661 Common::webdav_folder($form);
663 my $rc = $dbh->commit;
665 $self->save_periodic_invoices_config(dbh => $dbh,
666 oe_id => $form->{id},
667 config_yaml => $form->{periodic_invoices_config})
668 if ($form->{type} eq 'sales_order');
670 $main::lxdebug->leave_sub();
675 sub save_periodic_invoices_config {
676 my ($self, %params) = @_;
678 return if !$params{oe_id};
680 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
681 return if 'HASH' ne ref $config;
683 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
684 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
685 $obj->update_attributes(%{ $config });
688 sub load_periodic_invoice_config {
692 delete $form->{periodic_invoices_config};
695 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
698 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
699 print printer_id copies) };
700 $form->{periodic_invoices_config} = YAML::Dump($config);
705 sub _close_quotations_rfqs {
706 $main::lxdebug->enter_sub();
711 Common::check_params(\%params, qw(from_id to_id));
713 my $myconfig = \%main::myconfig;
714 my $form = $main::form;
716 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
718 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
719 my $sth = prepare_query($form, $dbh, $query);
721 do_statement($form, $sth, $query, conv_i($params{to_id}));
723 my ($quotation) = $sth->fetchrow_array();
726 $main::lxdebug->leave_sub();
732 foreach my $from_id (@{ $params{from_id} }) {
733 $from_id = conv_i($from_id);
734 do_statement($form, $sth, $query, $from_id);
735 ($quotation) = $sth->fetchrow_array();
736 push @close_ids, $from_id if ($quotation);
741 if (scalar @close_ids) {
742 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
743 do_query($form, $dbh, $query, @close_ids);
745 $dbh->commit() unless ($params{dbh});
748 $main::lxdebug->leave_sub();
752 $main::lxdebug->enter_sub();
754 my ($self, $myconfig, $form) = @_;
756 my $rc = SL::DB::Order->new->db->with_transaction(sub {
757 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
759 SL::DB::Order->new(id => $form->{id})->delete;
761 my $spool = $::lx_office_conf{paths}->{spool};
762 unlink map { "$spool/$_" } @spoolfiles if $spool;
767 $main::lxdebug->leave_sub();
773 $main::lxdebug->enter_sub();
775 my ($self, $myconfig, $form) = @_;
777 # connect to database
778 my $dbh = $form->get_standard_dbh;
780 my ($query, $query_add, @values, @ids, $sth);
782 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
784 push @ids, $form->{"trans_id_$_"}
785 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
786 } (1 .. $form->{"rowcount"});
788 if ($form->{rowcount} && scalar @ids) {
789 $form->{convert_from_oe_ids} = join ' ', @ids;
792 # if called in multi id mode, and still only got one id, switch back to single id
793 if ($form->{"rowcount"} and $#ids == 0) {
794 $form->{"id"} = $ids[0];
798 # and remember for the rest of the function
799 my $is_collective_order = scalar @ids;
802 my $wday = (localtime(time))[6];
803 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
805 # if we have a client configured interval for sales quotation, we add this
806 $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
807 $form->{type} eq 'sales_quotation' );
809 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
812 # get default accounts
813 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
814 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
815 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
816 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
817 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
820 my $ref = selectfirst_hashref_query($form, $dbh, $query);
821 map { $form->{$_} = $ref->{$_} } keys %$ref;
823 $form->{currency} = $form->get_default_currency($myconfig);
825 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
826 # we come from invoices, feel free.
827 $form->{reqdate} = $form->{deliverydate}
828 if ( $form->{deliverydate}
829 and $form->{callback} =~ /action=ar_transactions/);
831 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
833 if ($form->{id} or @ids) {
835 # retrieve order for single id
836 # NOTE: this query is intended to fetch all information only ONCE.
837 # so if any of these infos is important (or even different) for any item,
838 # it will be killed out and then has to be fetched from the item scope query further down
840 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
841 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
842 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
843 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
844 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
845 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
846 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
847 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
848 , o.order_probability, o.expected_billing_date
850 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
851 LEFT JOIN employee e ON (o.employee_id = e.id)
852 LEFT JOIN department d ON (o.department_id = d.id) | .
855 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
857 @values = $form->{id} ? ($form->{id}) : @ids;
858 $sth = prepare_execute_query($form, $dbh, $query, @values);
860 $ref = $sth->fetchrow_hashref("NAME_lc");
863 map { $form->{$_} = $ref->{$_} } keys %$ref;
865 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
867 # set all entries for multiple ids blank that yield different information
868 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
869 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
873 # if not given, fill transdate with current_date
874 $form->{transdate} = $form->current_date($myconfig)
875 unless $form->{transdate};
879 if ($form->{delivery_customer_id}) {
880 $query = qq|SELECT name FROM customer WHERE id = ?|;
881 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
884 if ($form->{delivery_vendor_id}) {
885 $query = qq|SELECT name FROM customer WHERE id = ?|;
886 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
889 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
891 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
892 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
894 $ref = $sth->fetchrow_hashref("NAME_lc");
896 map { $form->{$_} = $ref->{$_} } keys %$ref;
899 # get printed, emailed and queued
900 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
901 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
903 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
904 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
905 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
906 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
909 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
912 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
914 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
916 # retrieve individual items
917 # this query looks up all information about the items
918 # stuff different from the whole will not be overwritten, but saved with a suffix.
920 qq|SELECT o.id AS orderitems_id,
921 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
922 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
923 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
924 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
925 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
926 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
927 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
928 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
929 o.price_factor_id, o.price_factor, o.marge_price_factor,
930 pr.projectnumber, p.formel,
931 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
933 JOIN parts p ON (o.parts_id = p.id)
934 JOIN oe ON (o.trans_id = oe.id)
935 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
936 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)
937 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)
938 LEFT JOIN project pr ON (o.project_id = pr.id)
939 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
941 ? qq|WHERE o.trans_id = ?|
942 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
945 @ids = $form->{id} ? ($form->{id}) : @ids;
946 $sth = prepare_execute_query($form, $dbh, $query, @values);
948 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
949 # Retrieve custom variables.
950 my $cvars = CVar->get_custom_variables(dbh => $dbh,
952 sub_module => 'orderitems',
953 trans_id => $ref->{orderitems_id},
955 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
958 if (!$ref->{"part_inventory_accno_id"}) {
959 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
961 delete($ref->{"part_inventory_accno_id"});
963 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
964 # unless already present there
965 # remove _oe entries afterwards
966 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
967 qw|ordnumber transdate cusordnumber|
969 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
973 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
975 qq|SELECT accno AS inventory_accno, | .
976 qq| new_chart_id AS inventory_new_chart, | .
977 qq| date($transdate) - valid_from AS inventory_valid | .
978 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
979 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
980 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
983 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
985 qq|SELECT accno AS income_accno, | .
986 qq| new_chart_id AS income_new_chart, | .
987 qq| date($transdate) - valid_from AS income_valid | .
988 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
989 ($ref->{income_accno}, $ref->{income_new_chart},
990 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
993 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
995 qq|SELECT accno AS expense_accno, | .
996 qq| new_chart_id AS expense_new_chart, | .
997 qq| date($transdate) - valid_from AS expense_valid | .
998 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
999 ($ref->{expense_accno}, $ref->{expense_new_chart},
1000 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1003 # delete orderitems_id in collective orders, so that they get cloned no matter what
1004 delete $ref->{orderitems_id} if (@ids);
1006 # get tax rates and description
1007 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1009 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1010 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1011 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1012 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1013 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1014 qq|ORDER BY c.accno|;
1015 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1016 $ref->{taxaccounts} = "";
1018 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1019 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1023 $ref->{taxaccounts} .= "$ptr->{accno} ";
1024 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1025 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1026 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1027 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1028 $form->{taxaccounts} .= "$ptr->{accno} ";
1033 chop $ref->{taxaccounts};
1035 push @{ $form->{form_details} }, $ref;
1042 # get last name used
1043 $form->lastname_used($dbh, $myconfig, $form->{vc})
1044 unless $form->{"$form->{vc}_id"};
1048 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1050 Common::webdav_folder($form);
1052 $self->load_periodic_invoice_config($form);
1054 my $rc = $dbh->commit;
1056 $main::lxdebug->leave_sub();
1061 sub retrieve_simple {
1062 $main::lxdebug->enter_sub();
1067 Common::check_params(\%params, qw(id));
1069 my $myconfig = \%main::myconfig;
1070 my $form = $main::form;
1072 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1074 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1075 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1077 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1078 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1080 $main::lxdebug->leave_sub();
1086 $main::lxdebug->enter_sub();
1088 my ($self, $myconfig, $form) = @_;
1090 # connect to database
1091 my $dbh = $form->get_standard_dbh;
1097 my $nodiscount_subtotal = 0;
1098 my $discount_subtotal = 0;
1101 my @partsgroup = ();
1104 my $subtotal_header = 0;
1105 my $subposition = 0;
1113 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1115 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1116 'departments' => 'ALL_DEPARTMENTS');
1119 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1120 $price_factors{$pfac->{id}} = $pfac;
1121 $pfac->{factor} *= 1;
1122 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1126 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1127 next unless $dept->{id} eq $form->{department_id};
1128 $form->{department} = $dept->{description};
1132 # sort items by partsgroup
1133 for $i (1 .. $form->{rowcount}) {
1135 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1136 $partsgroup = $form->{"partsgroup_$i"};
1138 push @partsgroup, [$i, $partsgroup];
1139 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1145 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1146 %projects_by_id = map { $_->id => $_ } @$projects;
1149 if ($projects_by_id{$form->{"globalproject_id"}}) {
1150 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1151 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1153 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1154 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1158 $form->{discount} = [];
1160 $form->{TEMPLATE_ARRAYS} = { };
1161 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1163 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1164 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1167 qw(runningnumber number description longdescription qty ship unit bin
1168 partnotes serialnumber reqdate sellprice listprice netprice
1169 discount p_discount discount_sub nodiscount_sub
1170 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1171 price_factor price_factor_name partsgroup weight lineweight);
1173 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1174 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1176 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1178 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1180 my $totalweight = 0;
1182 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1185 if ($item->[1] ne $sameitem) {
1186 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1187 $sameitem = $item->[1];
1189 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1192 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1194 if ($form->{"id_$i"} != 0) {
1196 # add number, description and qty to $form->{number}, ....
1198 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1199 $subtotal_header = $i;
1200 $position = int($position);
1203 } elsif ($subtotal_header) {
1205 $position = int($position);
1206 $position = $position.".".$subposition;
1208 $position = int($position);
1212 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1214 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1215 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1216 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1217 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1218 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1219 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1220 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1221 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1222 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1223 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1224 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1225 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1226 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1227 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1228 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1229 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1230 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1231 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1232 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1234 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1235 my ($dec) = ($sellprice =~ /\.(\d+)/);
1236 my $decimalplaces = max 2, length($dec);
1238 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1240 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1241 my $linetotal = $form->round_amount($linetotal_exact, 2);
1243 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1244 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1246 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1248 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1250 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1252 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1253 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1255 $linetotal = ($linetotal != 0) ? $linetotal : '';
1257 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1258 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1259 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1261 $form->{ordtotal} += $linetotal;
1262 $form->{nodiscount_total} += $nodiscount_linetotal;
1263 $form->{discount_total} += $discount;
1265 if ($subtotal_header) {
1266 $discount_subtotal += $linetotal;
1267 $nodiscount_subtotal += $nodiscount_linetotal;
1270 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1271 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1272 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1273 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1274 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1276 $discount_subtotal = 0;
1277 $nodiscount_subtotal = 0;
1278 $subtotal_header = 0;
1281 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1284 if (!$form->{"discount_$i"}) {
1285 $nodiscount += $linetotal;
1288 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1290 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1291 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1292 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1293 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1294 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1295 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1297 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1298 $totalweight += $lineweight;
1299 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1300 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1301 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1302 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1304 my ($taxamount, $taxbase);
1307 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1309 if ($form->{taxincluded}) {
1312 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1313 $taxbase = $linetotal / (1 + $taxrate);
1315 $taxamount = $linetotal * $taxrate;
1316 $taxbase = $linetotal;
1319 if ($taxamount != 0) {
1320 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1321 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1322 $taxbase{$accno} += $taxbase;
1326 $tax_rate = $taxrate * 100;
1327 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1329 if ($form->{"assembly_$i"}) {
1332 # get parts and push them onto the stack
1334 if ($form->{groupitems}) {
1335 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1337 $sortorder = qq|ORDER BY a.oid|;
1340 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1341 qq|pg.partsgroup | .
1342 qq|FROM assembly a | .
1343 qq| JOIN parts p ON (a.parts_id = p.id) | .
1344 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1345 qq| WHERE a.bom = '1' | .
1346 qq| AND a.id = ? | . $sortorder;
1347 @values = ($form->{"id_$i"});
1348 $sth = $dbh->prepare($query);
1349 $sth->execute(@values) || $form->dberror($query);
1351 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1352 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1353 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1354 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1355 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1358 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1359 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1364 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1365 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1366 for @{ $ic_cvar_configs };
1368 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1372 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1373 $form->{totalweight_nofmt} = $totalweight;
1374 my $defaults = AM->get_defaults();
1375 $form->{weightunit} = $defaults->{weightunit};
1378 foreach $item (sort keys %taxaccounts) {
1379 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1381 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1382 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1383 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1384 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1386 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1387 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1389 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1390 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1391 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1394 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1395 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1396 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1397 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1399 if($form->{taxincluded}) {
1400 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1401 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1403 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1404 $form->{subtotal_nofmt} = $form->{ordtotal};
1407 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1410 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1412 if ($form->{type} =~ /_quotation/) {
1413 $form->set_payment_options($myconfig, $form->{quodate});
1415 $form->set_payment_options($myconfig, $form->{orddate});
1418 $form->{username} = $myconfig->{name};
1422 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1423 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1425 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1427 $main::lxdebug->leave_sub();
1430 sub project_description {
1431 $main::lxdebug->enter_sub();
1433 my ($self, $dbh, $id) = @_;
1435 my $query = qq|SELECT description FROM project WHERE id = ?|;
1436 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1438 $main::lxdebug->leave_sub();
1449 OE.pm - Order entry module
1453 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>.
1459 =item retrieve_simple PARAMS
1461 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1463 my $order = retrieve_simple(id => 2);