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;
512 CVar->get_non_editable_ic_cvars(form => $form,
515 sub_module => 'orderitems',
516 may_converted_from => ['orderitems', 'invoice']);
520 # save detail record in orderitems table
521 if (! $form->{"orderitems_id_$i"}) {
522 $query = qq|SELECT nextval('orderitemsid')|;
523 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
525 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
526 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
529 my $orderitems_id = $form->{"orderitems_id_$i"};
530 push @processed_orderitems, $orderitems_id;
533 UPDATE orderitems SET
534 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
535 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
536 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
537 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
538 active_price_source = ?, active_discount_source = ?,
539 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
543 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
544 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
545 $form->{"qty_$i"}, $baseqty,
546 $fxsellprice, $form->{"discount_$i"},
547 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
548 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
549 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
550 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
551 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
552 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
553 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
554 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
555 conv_i($orderitems_id),
558 do_query($form, $dbh, $query, @values);
560 $form->{"sellprice_$i"} = $fxsellprice;
561 $form->{"discount_$i"} *= 100;
563 CVar->save_custom_variables(module => 'IC',
564 sub_module => 'orderitems',
565 trans_id => $orderitems_id,
566 configs => $ic_cvar_configs,
568 name_prefix => 'ic_',
569 name_postfix => "_$i",
571 # link previous items with orderitems
572 foreach (qw(orderitems invoice)) {
573 if ($form->{"converted_from_${_}_id_$i"}) {
574 RecordLinks->create_links('dbh' => $dbh,
577 'from_ids' => $form->{"converted_from_${_}_id_$i"},
578 'to_table' => 'orderitems',
579 'to_id' => $orderitems_id,
581 delete $form->{"converted_from_${_}_id_$i"};
586 # search for orphaned ids
587 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
588 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
589 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
591 if (scalar @orphaned_ids) {
592 # clean up orderitems
593 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
594 do_query($form, $dbh, $query, @orphaned_ids);
597 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
601 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
603 $amount = $form->round_amount($netamount + $tax, 2);
604 $netamount = $form->round_amount($netamount, 2);
606 if ($form->{currency} eq $form->{defaultcurrency}) {
607 $form->{exchangerate} = 1;
609 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
612 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
614 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
616 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
621 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
622 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
623 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
624 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
625 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
626 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
627 , order_probability = ?, expected_billing_date = ?
630 @values = ($form->{ordnumber} || '', $form->{quonumber},
631 $form->{cusordnumber}, conv_date($form->{transdate}),
632 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
633 $amount, $netamount, conv_date($reqdate),
634 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
635 $form->{shipvia}, $form->{notes}, $form->{intnotes},
636 $form->{currency}, $form->{closed} ? 't' : 'f',
637 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
638 $quotation, conv_i($form->{department_id}),
639 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
640 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
641 conv_i($form->{delivery_vendor_id}),
642 conv_i($form->{delivery_customer_id}),
643 conv_i($form->{delivery_term_id}),
644 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
645 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
646 $form->{transaction_description},
647 $form->{marge_total} * 1, $form->{marge_percent} * 1,
648 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
649 conv_i($form->{id}));
650 do_query($form, $dbh, $query, @values);
652 $form->{ordtotal} = $amount;
654 $form->{name} = $form->{ $form->{vc} };
655 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
658 if (!$form->{shipto_id}) {
659 $form->add_shipto($dbh, $form->{id}, "OE");
662 # save printed, emailed, queued
663 $form->save_status($dbh);
665 # Link this record to the records it was created from.
666 # check every record type we may link. i am not happy with converting the string to array back
667 # should be a array from the start (OE.pm -> retrieve).
668 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
669 $form->{convert_from_oe_ids} =~ s/^\s+//;
670 $form->{convert_from_oe_ids} =~ s/\s+$//;
671 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
672 delete $form->{convert_from_oe_ids};
673 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
674 foreach (qw(ar oe)) {
675 if ($form->{"convert_from_${_}_ids"}) {
676 RecordLinks->create_links('dbh' => $dbh,
679 'from_ids' => $form->{"convert_from_${_}_ids"},
681 'to_id' => $form->{id},
683 delete $form->{"convert_from_${_}_ids"};
685 $self->_close_quotations_rfqs('dbh' => $dbh,
686 'from_id' => \@convert_from_oe_ids,
687 'to_id' => $form->{id}) if $_ eq 'oe';
690 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
691 if ($form->{vc} eq 'customer') {
692 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
694 if ($form->{vc} eq 'vendor') {
695 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
699 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
700 "quonumber" : "ordnumber"};
702 Common::webdav_folder($form);
704 my $rc = $dbh->commit;
706 $self->save_periodic_invoices_config(dbh => $dbh,
707 oe_id => $form->{id},
708 config_yaml => $form->{periodic_invoices_config})
709 if ($form->{type} eq 'sales_order');
711 $main::lxdebug->leave_sub();
716 sub save_periodic_invoices_config {
717 my ($self, %params) = @_;
719 return if !$params{oe_id};
721 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
722 return if 'HASH' ne ref $config;
724 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
725 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
726 $obj->update_attributes(%{ $config });
729 sub load_periodic_invoice_config {
733 delete $form->{periodic_invoices_config};
736 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
739 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
740 print printer_id copies) };
741 $form->{periodic_invoices_config} = YAML::Dump($config);
746 sub _close_quotations_rfqs {
747 $main::lxdebug->enter_sub();
752 Common::check_params(\%params, qw(from_id to_id));
754 my $myconfig = \%main::myconfig;
755 my $form = $main::form;
757 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
759 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
760 my $sth = prepare_query($form, $dbh, $query);
762 do_statement($form, $sth, $query, conv_i($params{to_id}));
764 my ($quotation) = $sth->fetchrow_array();
767 $main::lxdebug->leave_sub();
773 foreach my $from_id (@{ $params{from_id} }) {
774 $from_id = conv_i($from_id);
775 do_statement($form, $sth, $query, $from_id);
776 ($quotation) = $sth->fetchrow_array();
777 push @close_ids, $from_id if ($quotation);
782 if (scalar @close_ids) {
783 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
784 do_query($form, $dbh, $query, @close_ids);
786 $dbh->commit() unless ($params{dbh});
789 $main::lxdebug->leave_sub();
793 $main::lxdebug->enter_sub();
795 my ($self, $myconfig, $form) = @_;
797 my $rc = SL::DB::Order->new->db->with_transaction(sub {
798 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
800 SL::DB::Order->new(id => $form->{id})->delete;
802 my $spool = $::lx_office_conf{paths}->{spool};
803 unlink map { "$spool/$_" } @spoolfiles if $spool;
808 $main::lxdebug->leave_sub();
814 $main::lxdebug->enter_sub();
816 my ($self, $myconfig, $form) = @_;
818 # connect to database
819 my $dbh = $form->get_standard_dbh;
821 my ($query, $query_add, @values, @ids, $sth);
823 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
825 push @ids, $form->{"trans_id_$_"}
826 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
827 } (1 .. $form->{"rowcount"});
829 if ($form->{rowcount} && scalar @ids) {
830 $form->{convert_from_oe_ids} = join ' ', @ids;
833 # if called in multi id mode, and still only got one id, switch back to single id
834 if ($form->{"rowcount"} and $#ids == 0) {
835 $form->{"id"} = $ids[0];
839 # and remember for the rest of the function
840 my $is_collective_order = scalar @ids;
843 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
844 my $next_workday = DateTime->today_local->add(days => $extra_days);
845 my $day_of_week = $next_workday->day_of_week;
847 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
849 $form->{transdate} = DateTime->today_local->to_kivitendo;
850 $form->{reqdate} = $next_workday->to_kivitendo;
853 # get default accounts
854 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
855 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
856 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
857 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
858 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
861 my $ref = selectfirst_hashref_query($form, $dbh, $query);
862 map { $form->{$_} = $ref->{$_} } keys %$ref;
864 $form->{currency} = $form->get_default_currency($myconfig);
866 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
867 # we come from invoices, feel free.
868 $form->{reqdate} = $form->{deliverydate}
869 if ( $form->{deliverydate}
870 and $form->{callback} =~ /action=ar_transactions/);
872 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
874 if ($form->{id} or @ids) {
876 # retrieve order for single id
877 # NOTE: this query is intended to fetch all information only ONCE.
878 # so if any of these infos is important (or even different) for any item,
879 # it will be killed out and then has to be fetched from the item scope query further down
881 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
882 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
883 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
884 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
885 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
886 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
887 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
888 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
889 , o.order_probability, o.expected_billing_date
891 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
892 LEFT JOIN employee e ON (o.employee_id = e.id)
893 LEFT JOIN department d ON (o.department_id = d.id) | .
896 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
898 @values = $form->{id} ? ($form->{id}) : @ids;
899 $sth = prepare_execute_query($form, $dbh, $query, @values);
901 $ref = $sth->fetchrow_hashref("NAME_lc");
904 map { $form->{$_} = $ref->{$_} } keys %$ref;
906 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
908 # set all entries for multiple ids blank that yield different information
909 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
910 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
914 # if not given, fill transdate with current_date
915 $form->{transdate} = $form->current_date($myconfig)
916 unless $form->{transdate};
920 if ($form->{delivery_customer_id}) {
921 $query = qq|SELECT name FROM customer WHERE id = ?|;
922 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
925 if ($form->{delivery_vendor_id}) {
926 $query = qq|SELECT name FROM customer WHERE id = ?|;
927 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
930 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
932 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
933 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
935 $ref = $sth->fetchrow_hashref("NAME_lc");
937 map { $form->{$_} = $ref->{$_} } keys %$ref;
940 # get printed, emailed and queued
941 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
942 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
944 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
945 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
946 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
947 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
950 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
953 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
955 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
957 # retrieve individual items
958 # this query looks up all information about the items
959 # stuff different from the whole will not be overwritten, but saved with a suffix.
961 qq|SELECT o.id AS orderitems_id,
962 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
963 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
964 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
965 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
966 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
967 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
968 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
969 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
970 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
971 pr.projectnumber, p.formel,
972 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
974 JOIN parts p ON (o.parts_id = p.id)
975 JOIN oe ON (o.trans_id = oe.id)
976 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
977 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)
978 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)
979 LEFT JOIN project pr ON (o.project_id = pr.id)
980 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
982 ? qq|WHERE o.trans_id = ?|
983 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
984 qq|ORDER BY o.trans_id, o.position|;
986 @ids = $form->{id} ? ($form->{id}) : @ids;
987 $sth = prepare_execute_query($form, $dbh, $query, @values);
989 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
990 # Retrieve custom variables.
991 my $cvars = CVar->get_custom_variables(dbh => $dbh,
993 sub_module => 'orderitems',
994 trans_id => $ref->{orderitems_id},
996 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
999 if (!$ref->{"part_inventory_accno_id"}) {
1000 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1002 delete($ref->{"part_inventory_accno_id"});
1004 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1005 # unless already present there
1006 # remove _oe entries afterwards
1007 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1008 qw|ordnumber transdate cusordnumber|
1010 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1014 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1016 qq|SELECT accno AS inventory_accno, | .
1017 qq| new_chart_id AS inventory_new_chart, | .
1018 qq| date($transdate) - valid_from AS inventory_valid | .
1019 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1020 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1021 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1024 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1026 qq|SELECT accno AS income_accno, | .
1027 qq| new_chart_id AS income_new_chart, | .
1028 qq| date($transdate) - valid_from AS income_valid | .
1029 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1030 ($ref->{income_accno}, $ref->{income_new_chart},
1031 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1034 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1036 qq|SELECT accno AS expense_accno, | .
1037 qq| new_chart_id AS expense_new_chart, | .
1038 qq| date($transdate) - valid_from AS expense_valid | .
1039 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1040 ($ref->{expense_accno}, $ref->{expense_new_chart},
1041 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1044 # delete orderitems_id in collective orders, so that they get cloned no matter what
1045 delete $ref->{orderitems_id} if $is_collective_order;
1047 # get tax rates and description
1048 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1050 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1051 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1052 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1053 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1054 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1055 qq|ORDER BY c.accno|;
1056 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1057 $ref->{taxaccounts} = "";
1059 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1060 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1064 $ref->{taxaccounts} .= "$ptr->{accno} ";
1065 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1066 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1067 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1068 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1069 $form->{taxaccounts} .= "$ptr->{accno} ";
1074 chop $ref->{taxaccounts};
1076 push @{ $form->{form_details} }, $ref;
1083 # get last name used
1084 $form->lastname_used($dbh, $myconfig, $form->{vc})
1085 unless $form->{"$form->{vc}_id"};
1089 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1091 Common::webdav_folder($form);
1093 $self->load_periodic_invoice_config($form);
1095 my $rc = $dbh->commit;
1097 $main::lxdebug->leave_sub();
1102 sub retrieve_simple {
1103 $main::lxdebug->enter_sub();
1108 Common::check_params(\%params, qw(id));
1110 my $myconfig = \%main::myconfig;
1111 my $form = $main::form;
1113 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1115 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1116 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1118 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1119 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1121 $main::lxdebug->leave_sub();
1127 $main::lxdebug->enter_sub();
1129 my ($self, $myconfig, $form) = @_;
1131 # connect to database
1132 my $dbh = $form->get_standard_dbh;
1138 my $nodiscount_subtotal = 0;
1139 my $discount_subtotal = 0;
1142 my @partsgroup = ();
1145 my $subtotal_header = 0;
1146 my $subposition = 0;
1154 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1156 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1157 'departments' => 'ALL_DEPARTMENTS');
1160 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1161 $price_factors{$pfac->{id}} = $pfac;
1162 $pfac->{factor} *= 1;
1163 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1167 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1168 next unless $dept->{id} eq $form->{department_id};
1169 $form->{department} = $dept->{description};
1173 # sort items by partsgroup
1174 for $i (1 .. $form->{rowcount}) {
1176 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1177 $partsgroup = $form->{"partsgroup_$i"};
1179 push @partsgroup, [$i, $partsgroup];
1180 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1186 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1187 %projects_by_id = map { $_->id => $_ } @$projects;
1190 if ($projects_by_id{$form->{"globalproject_id"}}) {
1191 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1192 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1194 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1195 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1199 $form->{discount} = [];
1201 $form->{TEMPLATE_ARRAYS} = { };
1202 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1204 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1205 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1208 qw(runningnumber number description longdescription qty ship unit bin
1209 partnotes serialnumber reqdate sellprice listprice netprice
1210 discount p_discount discount_sub nodiscount_sub
1211 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1212 price_factor price_factor_name partsgroup weight lineweight);
1214 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1215 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1217 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1219 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1221 my $totalweight = 0;
1223 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1226 if ($item->[1] ne $sameitem) {
1227 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1228 $sameitem = $item->[1];
1230 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1233 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1235 if ($form->{"id_$i"} != 0) {
1237 # add number, description and qty to $form->{number}, ....
1239 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1240 $subtotal_header = $i;
1241 $position = int($position);
1244 } elsif ($subtotal_header) {
1246 $position = int($position);
1247 $position = $position.".".$subposition;
1249 $position = int($position);
1253 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1255 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1256 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1257 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1258 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1259 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1260 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1261 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1262 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1263 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1264 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1265 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1266 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1267 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1268 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1269 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1270 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1271 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1272 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1273 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1275 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1276 my ($dec) = ($sellprice =~ /\.(\d+)/);
1277 my $decimalplaces = max 2, length($dec);
1279 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1281 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1282 my $linetotal = $form->round_amount($linetotal_exact, 2);
1284 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1285 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1287 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1289 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1291 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1293 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1294 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1296 $linetotal = ($linetotal != 0) ? $linetotal : '';
1298 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1299 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1300 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1302 $form->{ordtotal} += $linetotal;
1303 $form->{nodiscount_total} += $nodiscount_linetotal;
1304 $form->{discount_total} += $discount;
1306 if ($subtotal_header) {
1307 $discount_subtotal += $linetotal;
1308 $nodiscount_subtotal += $nodiscount_linetotal;
1311 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1312 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1313 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1314 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1315 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1317 $discount_subtotal = 0;
1318 $nodiscount_subtotal = 0;
1319 $subtotal_header = 0;
1322 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1325 if (!$form->{"discount_$i"}) {
1326 $nodiscount += $linetotal;
1329 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1331 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1332 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1333 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1334 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1335 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1336 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1338 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1339 $totalweight += $lineweight;
1340 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1341 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1342 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1343 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1345 my ($taxamount, $taxbase);
1348 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1350 if ($form->{taxincluded}) {
1353 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1354 $taxbase = $linetotal / (1 + $taxrate);
1356 $taxamount = $linetotal * $taxrate;
1357 $taxbase = $linetotal;
1360 if ($taxamount != 0) {
1361 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1362 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1363 $taxbase{$accno} += $taxbase;
1367 $tax_rate = $taxrate * 100;
1368 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1370 if ($form->{"assembly_$i"}) {
1373 # get parts and push them onto the stack
1375 if ($form->{groupitems}) {
1376 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1378 $sortorder = qq|ORDER BY a.oid|;
1381 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1382 qq|pg.partsgroup | .
1383 qq|FROM assembly a | .
1384 qq| JOIN parts p ON (a.parts_id = p.id) | .
1385 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1386 qq| WHERE a.bom = '1' | .
1387 qq| AND a.id = ? | . $sortorder;
1388 @values = ($form->{"id_$i"});
1389 $sth = $dbh->prepare($query);
1390 $sth->execute(@values) || $form->dberror($query);
1392 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1393 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1394 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1395 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1396 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1399 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1400 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1405 CVar->get_non_editable_ic_cvars(form => $form,
1408 sub_module => 'orderitems',
1409 may_converted_from => ['orderitems', 'invoice']);
1411 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1412 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1413 for @{ $ic_cvar_configs };
1415 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1419 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1420 $form->{totalweight_nofmt} = $totalweight;
1421 my $defaults = AM->get_defaults();
1422 $form->{weightunit} = $defaults->{weightunit};
1425 foreach $item (sort keys %taxaccounts) {
1426 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1428 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1429 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1430 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1431 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1432 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1433 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1434 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1436 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1437 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1438 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1441 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1442 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1443 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1444 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1446 if($form->{taxincluded}) {
1447 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1448 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1450 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1451 $form->{subtotal_nofmt} = $form->{ordtotal};
1454 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1457 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1459 if ($form->{type} =~ /_quotation/) {
1460 $form->set_payment_options($myconfig, $form->{quodate});
1462 $form->set_payment_options($myconfig, $form->{orddate});
1465 $form->{username} = $myconfig->{name};
1469 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1470 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1472 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1474 $main::lxdebug->leave_sub();
1477 sub project_description {
1478 $main::lxdebug->enter_sub();
1480 my ($self, $dbh, $id) = @_;
1482 my $query = qq|SELECT description FROM project WHERE id = ?|;
1483 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1485 $main::lxdebug->leave_sub();
1496 OE.pm - Order entry module
1500 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>.
1506 =item retrieve_simple PARAMS
1508 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1510 my $order = retrieve_simple(id => 2);