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| o.itime::DATE AS insertdate, | .
113 qq| ex.$rate AS exchangerate, | .
114 qq| pr.projectnumber AS globalprojectnumber, | .
115 qq| e.name AS employee, s.name AS salesman, | .
116 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
117 qq| tz.description AS taxzone | .
118 $periodic_invoices_columns .
119 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
121 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
122 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
123 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
124 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
125 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
126 qq| AND ex.transdate = o.transdate) | .
127 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
128 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
129 qq|$periodic_invoices_joins | .
130 qq|WHERE (o.quotation = ?) |;
131 push(@values, $quotation);
133 my ($null, $split_department_id) = split /--/, $form->{department};
134 my $department_id = $form->{department_id} || $split_department_id;
135 if ($department_id) {
136 $query .= qq| AND o.department_id = ?|;
137 push(@values, $department_id);
140 if ($form->{"project_id"}) {
142 qq|AND ((globalproject_id = ?) OR EXISTS | .
143 qq| (SELECT * FROM orderitems oi | .
144 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
145 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
148 if ($form->{"projectnumber"}) {
150 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
151 SELECT * FROM orderitems oi
152 LEFT JOIN project proi ON proi.id = oi.project_id
153 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
156 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
159 if ($form->{"business_id"}) {
160 $query .= " AND ct.business_id = ?";
161 push(@values, $form->{"business_id"});
164 if ($form->{"${vc}_id"}) {
165 $query .= " AND o.${vc}_id = ?";
166 push(@values, $form->{"${vc}_id"});
168 } elsif ($form->{$vc}) {
169 $query .= " AND ct.name ILIKE ?";
170 push(@values, '%' . $form->{$vc} . '%');
173 if ($form->{"cp_name"}) {
174 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
175 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
178 if (!$main::auth->assert('sales_all_edit', 1)) {
179 $query .= " AND o.employee_id = (select id from employee where login= ?)";
180 push @values, $::myconfig{login};
182 if ($form->{employee_id}) {
183 $query .= " AND o.employee_id = ?";
184 push @values, conv_i($form->{employee_id});
187 if ($form->{salesman_id}) {
188 $query .= " AND o.salesman_id = ?";
189 push @values, conv_i($form->{salesman_id});
192 if (!$form->{open} && !$form->{closed}) {
193 $query .= " AND o.id = 0";
194 } elsif (!($form->{open} && $form->{closed})) {
195 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
198 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
199 ($form->{"notdelivered"} ne $form->{"delivered"})) {
200 $query .= $form->{"delivered"} ?
201 " AND o.delivered " : " AND NOT o.delivered";
204 if ($form->{$ordnumber}) {
205 $query .= qq| AND o.$ordnumber ILIKE ?|;
206 push(@values, '%' . $form->{$ordnumber} . '%');
209 if ($form->{cusordnumber}) {
210 $query .= qq| AND o.cusordnumber ILIKE ?|;
211 push(@values, '%' . $form->{cusordnumber} . '%');
214 if($form->{transdatefrom}) {
215 $query .= qq| AND o.transdate >= ?|;
216 push(@values, conv_date($form->{transdatefrom}));
219 if($form->{transdateto}) {
220 $query .= qq| AND o.transdate <= ?|;
221 push(@values, conv_date($form->{transdateto}));
224 if($form->{reqdatefrom}) {
225 $query .= qq| AND o.reqdate >= ?|;
226 push(@values, conv_date($form->{reqdatefrom}));
229 if($form->{reqdateto}) {
230 $query .= qq| AND o.reqdate <= ?|;
231 push(@values, conv_date($form->{reqdateto}));
234 if($form->{insertdatefrom}) {
235 $query .= qq| AND o.itime::DATE >= ?|;
236 push(@values, conv_date($form->{insertdatefrom}));
239 if($form->{insertdateto}) {
240 $query .= qq| AND o.itime::DATE <= ?|;
241 push(@values, conv_date($form->{insertdateto}));
244 if ($form->{shippingpoint}) {
245 $query .= qq| AND o.shippingpoint ILIKE ?|;
246 push(@values, '%' . $form->{shippingpoint} . '%');
249 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
250 $query .= qq| AND tz.id = ?|;
251 push(@values, $form->{taxzone_id});
254 if ($form->{transaction_description}) {
255 $query .= qq| AND o.transaction_description ILIKE ?|;
256 push(@values, '%' . $form->{transaction_description} . '%');
259 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
260 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
261 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
264 if ($form->{reqdate_unset_or_old}) {
265 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
268 if (($form->{order_probability_value} || '') ne '') {
269 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
270 $query .= qq| AND (o.order_probability ${op} ?)|;
271 push @values, $form->{order_probability_value};
274 if ($form->{expected_billing_date_from}) {
275 $query .= qq| AND (o.expected_billing_date >= ?)|;
276 push @values, conv_date($form->{expected_billing_date_from});
279 if ($form->{expected_billing_date_to}) {
280 $query .= qq| AND (o.expected_billing_date <= ?)|;
281 push @values, conv_date($form->{expected_billing_date_to});
284 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
285 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
286 my %allowed_sort_columns = (
287 "transdate" => "o.transdate",
288 "reqdate" => "o.reqdate",
290 "ordnumber" => "o.ordnumber",
291 "cusordnumber" => "o.cusordnumber",
292 "quonumber" => "o.quonumber",
294 "employee" => "e.name",
295 "salesman" => "s.name",
296 "shipvia" => "o.shipvia",
297 "transaction_description" => "o.transaction_description",
298 "shippingpoint" => "o.shippingpoint",
299 "insertdate" => "o.itime",
300 "taxzone" => "tz.description",
302 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
303 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
305 $query .= qq| ORDER by | . $sortorder;
307 my $sth = $dbh->prepare($query);
308 $sth->execute(@values) ||
309 $form->dberror($query . " (" . join(", ", @values) . ")");
313 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
314 $ref->{billed_amount} = $billed_amount{$ref->{id}};
315 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
316 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
317 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
318 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
319 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
320 $id{ $ref->{id} } = $ref->{id};
325 $main::lxdebug->leave_sub();
328 sub transactions_for_todo_list {
329 $main::lxdebug->enter_sub();
334 my $myconfig = \%main::myconfig;
335 my $form = $main::form;
337 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
339 my $query = qq|SELECT id FROM employee WHERE login = ?|;
340 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
343 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
344 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
349 LEFT JOIN customer c ON (oe.customer_id = c.id)
350 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
351 LEFT JOIN employee e ON (oe.employee_id = e.id)
352 WHERE (COALESCE(quotation, FALSE) = TRUE)
353 AND (COALESCE(closed, FALSE) = FALSE)
354 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
355 AND NOT (oe.reqdate ISNULL)
356 AND (oe.reqdate < current_date)
359 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
361 $main::lxdebug->leave_sub();
367 $main::lxdebug->enter_sub();
369 my ($self, $myconfig, $form) = @_;
371 # connect to database, turn off autocommit
372 my $dbh = $form->get_standard_dbh;
373 my $restricter = SL::HTML::Restrict->create;
375 my ($query, @values, $sth, $null);
376 my $exchangerate = 0;
378 my $all_units = AM->retrieve_units($myconfig, $form);
379 $form->{all_units} = $all_units;
381 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
384 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
385 unless ($form->{employee_id}) {
386 $form->get_employee($dbh);
389 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
391 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
392 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
393 $form->{$number_field} ||= $trans_number->create_unique;
396 $query = qq|DELETE FROM shipto | .
397 qq|WHERE trans_id = ? AND module = 'OE'|;
398 do_query($form, $dbh, $query, $form->{id});
402 $query = qq|SELECT nextval('id')|;
403 ($form->{id}) = selectrow_query($form, $dbh, $query);
405 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
406 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
423 my @processed_orderitems;
425 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
426 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
429 for my $i (1 .. $form->{rowcount}) {
431 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
433 if ($form->{"id_$i"}) {
436 $query = qq|SELECT unit FROM parts WHERE id = ?|;
437 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
440 if (defined($all_units->{$item_unit}->{factor}) &&
441 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
442 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
444 my $baseqty = $form->{"qty_$i"} * $basefactor;
446 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
447 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
449 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
451 # keep entered selling price
453 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
455 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
457 my $decimalplaces = ($dec > 2) ? $dec : 2;
459 # undo discount formatting
460 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
463 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
465 # round linetotal at least to 2 decimal places
466 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
467 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
469 $form->{"inventory_accno_$i"} *= 1;
470 $form->{"expense_accno_$i"} *= 1;
472 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
476 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
478 if ($form->{taxincluded}) {
479 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
480 $taxbase = $linetotal - $taxamount;
482 # we are not keeping a natural price, do not round
483 $form->{"sellprice_$i"} =
484 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
486 $taxamount = $linetotal * $taxrate;
487 $taxbase = $linetotal;
490 if ($form->round_amount($taxrate, 7) == 0) {
491 if ($form->{taxincluded}) {
492 foreach my $item (@taxaccounts) {
493 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
494 $taxaccounts{$item} += $taxamount;
495 $taxdiff += $taxamount;
496 $taxbase{$item} += $taxbase;
498 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
500 foreach my $item (@taxaccounts) {
501 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
502 $taxbase{$item} += $taxbase;
506 foreach my $item (@taxaccounts) {
507 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
508 $taxbase{$item} += $taxbase;
512 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
514 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
516 # Get pricegroup_id and save it. Unfortunately the interface
517 # also uses ID "0" for signalling that none is selected, but "0"
518 # must not be stored in the database. Therefore we cannot simply
520 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
522 $pricegroup_id = undef if !$pricegroup_id;
524 CVar->get_non_editable_ic_cvars(form => $form,
527 sub_module => 'orderitems',
528 may_converted_from => ['orderitems', 'invoice']);
532 # save detail record in orderitems table
533 if (! $form->{"orderitems_id_$i"}) {
534 $query = qq|SELECT nextval('orderitemsid')|;
535 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
537 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
538 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
541 my $orderitems_id = $form->{"orderitems_id_$i"};
542 push @processed_orderitems, $orderitems_id;
545 UPDATE orderitems SET
546 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
547 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
548 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
549 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
550 active_price_source = ?, active_discount_source = ?,
551 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
555 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
556 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
557 $form->{"qty_$i"}, $baseqty,
558 $fxsellprice, $form->{"discount_$i"},
559 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
560 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
561 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
562 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
563 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
564 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
565 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
566 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
567 conv_i($orderitems_id),
570 do_query($form, $dbh, $query, @values);
572 $form->{"sellprice_$i"} = $fxsellprice;
573 $form->{"discount_$i"} *= 100;
575 CVar->save_custom_variables(module => 'IC',
576 sub_module => 'orderitems',
577 trans_id => $orderitems_id,
578 configs => $ic_cvar_configs,
580 name_prefix => 'ic_',
581 name_postfix => "_$i",
584 # link previous items with orderitems
585 foreach (qw(orderitems invoice)) {
586 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
587 RecordLinks->create_links('dbh' => $dbh,
590 'from_ids' => $form->{"converted_from_${_}_id_$i"},
591 'to_table' => 'orderitems',
592 'to_id' => $orderitems_id,
595 delete $form->{"converted_from_${_}_id_$i"};
600 # search for orphaned ids
601 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
602 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
603 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
605 if (scalar @orphaned_ids) {
606 # clean up orderitems
607 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
608 do_query($form, $dbh, $query, @orphaned_ids);
611 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
615 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
617 $amount = $form->round_amount($netamount + $tax, 2);
618 $netamount = $form->round_amount($netamount, 2);
620 if ($form->{currency} eq $form->{defaultcurrency}) {
621 $form->{exchangerate} = 1;
623 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
626 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
628 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
630 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
635 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
636 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
637 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
638 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
639 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
640 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
641 , order_probability = ?, expected_billing_date = ?
644 @values = ($form->{ordnumber} || '', $form->{quonumber},
645 $form->{cusordnumber}, conv_date($form->{transdate}),
646 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
647 $amount, $netamount, conv_date($reqdate),
648 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
649 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
650 $form->{currency}, $form->{closed} ? 't' : 'f',
651 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
652 $quotation, conv_i($form->{department_id}),
653 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
654 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
655 conv_i($form->{delivery_vendor_id}),
656 conv_i($form->{delivery_customer_id}),
657 conv_i($form->{delivery_term_id}),
658 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
659 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
660 $form->{transaction_description},
661 $form->{marge_total} * 1, $form->{marge_percent} * 1,
662 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
663 conv_i($form->{id}));
664 do_query($form, $dbh, $query, @values);
666 $form->{ordtotal} = $amount;
668 $form->{name} = $form->{ $form->{vc} };
669 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
672 if (!$form->{shipto_id}) {
673 $form->add_shipto($dbh, $form->{id}, "OE");
676 # save printed, emailed, queued
677 $form->save_status($dbh);
679 # Link this record to the records it was created from.
680 # check every record type we may link. i am not happy with converting the string to array back
681 # should be a array from the start (OE.pm -> retrieve).
682 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
683 $form->{convert_from_oe_ids} =~ s/^\s+//;
684 $form->{convert_from_oe_ids} =~ s/\s+$//;
685 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
686 delete $form->{convert_from_oe_ids};
687 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
688 foreach (qw(ar oe)) {
689 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
690 RecordLinks->create_links('dbh' => $dbh,
693 'from_ids' => $form->{"convert_from_${_}_ids"},
695 'to_id' => $form->{id},
697 delete $form->{"convert_from_${_}_ids"};
699 $self->_close_quotations_rfqs('dbh' => $dbh,
700 'from_id' => \@convert_from_oe_ids,
701 'to_id' => $form->{id}) if $_ eq 'oe';
704 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
705 if ($form->{vc} eq 'customer') {
706 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
708 if ($form->{vc} eq 'vendor') {
709 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
713 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
714 "quonumber" : "ordnumber"};
716 Common::webdav_folder($form);
718 my $rc = $dbh->commit;
720 $self->save_periodic_invoices_config(dbh => $dbh,
721 oe_id => $form->{id},
722 config_yaml => $form->{periodic_invoices_config})
723 if ($form->{type} eq 'sales_order');
725 $main::lxdebug->leave_sub();
730 sub save_periodic_invoices_config {
731 my ($self, %params) = @_;
733 return if !$params{oe_id};
735 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
736 return if 'HASH' ne ref $config;
738 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
739 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
740 $obj->update_attributes(%{ $config });
743 sub load_periodic_invoice_config {
747 delete $form->{periodic_invoices_config};
750 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
753 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity order_value_periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
754 print printer_id copies) };
755 $form->{periodic_invoices_config} = YAML::Dump($config);
760 sub _close_quotations_rfqs {
761 $main::lxdebug->enter_sub();
766 Common::check_params(\%params, qw(from_id to_id));
768 my $myconfig = \%main::myconfig;
769 my $form = $main::form;
771 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
773 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
774 my $sth = prepare_query($form, $dbh, $query);
776 do_statement($form, $sth, $query, conv_i($params{to_id}));
778 my ($quotation) = $sth->fetchrow_array();
781 $main::lxdebug->leave_sub();
787 foreach my $from_id (@{ $params{from_id} }) {
788 $from_id = conv_i($from_id);
789 do_statement($form, $sth, $query, $from_id);
790 ($quotation) = $sth->fetchrow_array();
791 push @close_ids, $from_id if ($quotation);
796 if (scalar @close_ids) {
797 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
798 do_query($form, $dbh, $query, @close_ids);
800 $dbh->commit() unless ($params{dbh});
803 $main::lxdebug->leave_sub();
807 $main::lxdebug->enter_sub();
809 my ($self, $myconfig, $form) = @_;
811 my $rc = SL::DB::Order->new->db->with_transaction(sub {
812 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
814 SL::DB::Order->new(id => $form->{id})->delete;
816 my $spool = $::lx_office_conf{paths}->{spool};
817 unlink map { "$spool/$_" } @spoolfiles if $spool;
822 $main::lxdebug->leave_sub();
828 $main::lxdebug->enter_sub();
830 my ($self, $myconfig, $form) = @_;
832 # connect to database
833 my $dbh = $form->get_standard_dbh;
835 my ($query, $query_add, @values, @ids, $sth);
837 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
839 push @ids, $form->{"trans_id_$_"}
840 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
841 } (1 .. $form->{"rowcount"});
843 if ($form->{rowcount} && scalar @ids) {
844 $form->{convert_from_oe_ids} = join ' ', @ids;
847 # if called in multi id mode, and still only got one id, switch back to single id
848 if ($form->{"rowcount"} and $#ids == 0) {
849 $form->{"id"} = $ids[0];
851 delete $form->{convert_from_oe_ids};
854 # and remember for the rest of the function
855 my $is_collective_order = scalar @ids;
856 $form->{useasnew} = !!$is_collective_order;
859 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
860 my $next_workday = DateTime->today_local->add(days => $extra_days);
861 my $day_of_week = $next_workday->day_of_week;
863 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
865 $form->{transdate} = DateTime->today_local->to_kivitendo;
866 $form->{reqdate} = $next_workday->to_kivitendo;
869 # get default accounts
870 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
871 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
872 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
873 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
874 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
877 my $ref = selectfirst_hashref_query($form, $dbh, $query);
878 map { $form->{$_} = $ref->{$_} } keys %$ref;
880 $form->{currency} = $form->get_default_currency($myconfig);
882 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
883 # we come from invoices, feel free.
884 $form->{reqdate} = $form->{deliverydate}
885 if ( $form->{deliverydate}
886 and $form->{callback} =~ /action=ar_transactions/);
888 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
890 if ($form->{id} or @ids) {
892 # retrieve order for single id
893 # NOTE: this query is intended to fetch all information only ONCE.
894 # so if any of these infos is important (or even different) for any item,
895 # it will be killed out and then has to be fetched from the item scope query further down
897 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
898 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
899 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
900 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
901 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
902 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
903 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
904 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
905 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
907 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
908 LEFT JOIN employee e ON (o.employee_id = e.id)
909 LEFT JOIN department d ON (o.department_id = d.id) | .
912 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
914 @values = $form->{id} ? ($form->{id}) : @ids;
915 $sth = prepare_execute_query($form, $dbh, $query, @values);
917 $ref = $sth->fetchrow_hashref("NAME_lc");
920 map { $form->{$_} = $ref->{$_} } keys %$ref;
922 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
924 # set all entries for multiple ids blank that yield different information
925 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
926 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
930 # if not given, fill transdate with current_date
931 $form->{transdate} = $form->current_date($myconfig)
932 unless $form->{transdate};
936 if ($form->{delivery_customer_id}) {
937 $query = qq|SELECT name FROM customer WHERE id = ?|;
938 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
941 if ($form->{delivery_vendor_id}) {
942 $query = qq|SELECT name FROM customer WHERE id = ?|;
943 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
946 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
948 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
949 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
951 $ref = $sth->fetchrow_hashref("NAME_lc");
953 map { $form->{$_} = $ref->{$_} } keys %$ref;
956 # get printed, emailed and queued
957 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
958 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
960 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
961 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
962 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
963 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
966 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
969 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
971 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
973 # retrieve individual items
974 # this query looks up all information about the items
975 # stuff different from the whole will not be overwritten, but saved with a suffix.
977 qq|SELECT o.id AS orderitems_id,
978 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
979 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
980 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
981 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
982 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
983 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
984 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
985 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
986 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
987 pr.projectnumber, p.formel,
988 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
990 JOIN parts p ON (o.parts_id = p.id)
991 JOIN oe ON (o.trans_id = oe.id)
992 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
993 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)
994 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)
995 LEFT JOIN project pr ON (o.project_id = pr.id)
996 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
998 ? qq|WHERE o.trans_id = ?|
999 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1000 qq|ORDER BY o.trans_id, o.position|;
1002 @ids = $form->{id} ? ($form->{id}) : @ids;
1003 $sth = prepare_execute_query($form, $dbh, $query, @values);
1005 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1006 # Retrieve custom variables.
1007 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1009 sub_module => 'orderitems',
1010 trans_id => $ref->{orderitems_id},
1012 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1015 if (!$ref->{"part_inventory_accno_id"}) {
1016 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1018 delete($ref->{"part_inventory_accno_id"});
1020 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1021 # unless already present there
1022 # remove _oe entries afterwards
1023 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1024 qw|ordnumber transdate cusordnumber|
1026 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1030 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1032 qq|SELECT accno AS inventory_accno, | .
1033 qq| new_chart_id AS inventory_new_chart, | .
1034 qq| date($transdate) - valid_from AS inventory_valid | .
1035 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1036 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1037 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1040 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1042 qq|SELECT accno AS income_accno, | .
1043 qq| new_chart_id AS income_new_chart, | .
1044 qq| date($transdate) - valid_from AS income_valid | .
1045 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1046 ($ref->{income_accno}, $ref->{income_new_chart},
1047 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1050 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1052 qq|SELECT accno AS expense_accno, | .
1053 qq| new_chart_id AS expense_new_chart, | .
1054 qq| date($transdate) - valid_from AS expense_valid | .
1055 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1056 ($ref->{expense_accno}, $ref->{expense_new_chart},
1057 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1060 # delete orderitems_id in collective orders, so that they get cloned no matter what
1061 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1063 # get tax rates and description
1064 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1066 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1067 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1068 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1069 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1070 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1071 qq|ORDER BY c.accno|;
1072 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1073 $ref->{taxaccounts} = "";
1075 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1076 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1080 $ref->{taxaccounts} .= "$ptr->{accno} ";
1081 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1082 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1083 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1084 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1085 $form->{taxaccounts} .= "$ptr->{accno} ";
1090 chop $ref->{taxaccounts};
1092 push @{ $form->{form_details} }, $ref;
1099 # get last name used
1100 $form->lastname_used($dbh, $myconfig, $form->{vc})
1101 unless $form->{"$form->{vc}_id"};
1105 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1107 Common::webdav_folder($form);
1109 $self->load_periodic_invoice_config($form);
1111 my $rc = $dbh->commit;
1113 $main::lxdebug->leave_sub();
1118 sub retrieve_simple {
1119 $main::lxdebug->enter_sub();
1124 Common::check_params(\%params, qw(id));
1126 my $myconfig = \%main::myconfig;
1127 my $form = $main::form;
1129 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1131 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1132 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1134 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1135 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1137 $main::lxdebug->leave_sub();
1143 $main::lxdebug->enter_sub();
1145 my ($self, $myconfig, $form) = @_;
1147 # connect to database
1148 my $dbh = $form->get_standard_dbh;
1154 my $nodiscount_subtotal = 0;
1155 my $discount_subtotal = 0;
1158 my @partsgroup = ();
1161 my $subtotal_header = 0;
1162 my $subposition = 0;
1170 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1172 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1173 'departments' => 'ALL_DEPARTMENTS');
1176 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1177 $price_factors{$pfac->{id}} = $pfac;
1178 $pfac->{factor} *= 1;
1179 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1183 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1184 next unless $dept->{id} eq $form->{department_id};
1185 $form->{department} = $dept->{description};
1189 # sort items by partsgroup
1190 for $i (1 .. $form->{rowcount}) {
1192 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1193 $partsgroup = $form->{"partsgroup_$i"};
1195 push @partsgroup, [$i, $partsgroup];
1196 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1202 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1203 %projects_by_id = map { $_->id => $_ } @$projects;
1206 if ($projects_by_id{$form->{"globalproject_id"}}) {
1207 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1208 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1210 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1211 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1215 $form->{discount} = [];
1217 $form->{TEMPLATE_ARRAYS} = { };
1218 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1220 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1221 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1224 qw(runningnumber number description longdescription qty ship unit bin
1225 partnotes serialnumber reqdate sellprice listprice netprice
1226 discount p_discount discount_sub nodiscount_sub
1227 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1228 price_factor price_factor_name partsgroup weight lineweight);
1230 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1231 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1233 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1235 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1237 my $totalweight = 0;
1239 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1242 if ($item->[1] ne $sameitem) {
1243 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1244 $sameitem = $item->[1];
1246 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1249 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1251 if ($form->{"id_$i"} != 0) {
1253 # add number, description and qty to $form->{number}, ....
1255 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1256 $subtotal_header = $i;
1257 $position = int($position);
1260 } elsif ($subtotal_header) {
1262 $position = int($position);
1263 $position = $position.".".$subposition;
1265 $position = int($position);
1269 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1271 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1272 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1273 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1274 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1275 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1276 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1277 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1278 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1279 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1280 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1281 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1282 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1283 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1284 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1285 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1286 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1287 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1288 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1289 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1291 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1292 my ($dec) = ($sellprice =~ /\.(\d+)/);
1293 my $decimalplaces = max 2, length($dec);
1295 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1297 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1298 my $linetotal = $form->round_amount($linetotal_exact, 2);
1300 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1301 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1303 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1305 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1307 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1309 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1310 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1312 $linetotal = ($linetotal != 0) ? $linetotal : '';
1314 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1315 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1316 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1318 $form->{ordtotal} += $linetotal;
1319 $form->{nodiscount_total} += $nodiscount_linetotal;
1320 $form->{discount_total} += $discount;
1322 if ($subtotal_header) {
1323 $discount_subtotal += $linetotal;
1324 $nodiscount_subtotal += $nodiscount_linetotal;
1327 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1328 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1329 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1330 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1331 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1333 $discount_subtotal = 0;
1334 $nodiscount_subtotal = 0;
1335 $subtotal_header = 0;
1338 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1341 if (!$form->{"discount_$i"}) {
1342 $nodiscount += $linetotal;
1345 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1347 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1348 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1349 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1350 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1351 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1352 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1354 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1355 $totalweight += $lineweight;
1356 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1357 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1358 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1359 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1361 my ($taxamount, $taxbase);
1364 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1366 if ($form->{taxincluded}) {
1369 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1370 $taxbase = $linetotal / (1 + $taxrate);
1372 $taxamount = $linetotal * $taxrate;
1373 $taxbase = $linetotal;
1376 if ($taxamount != 0) {
1377 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1378 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1379 $taxbase{$accno} += $taxbase;
1383 $tax_rate = $taxrate * 100;
1384 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1386 if ($form->{"assembly_$i"}) {
1389 # get parts and push them onto the stack
1391 if ($form->{groupitems}) {
1392 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1394 $sortorder = qq|ORDER BY a.oid|;
1397 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1398 qq|pg.partsgroup | .
1399 qq|FROM assembly a | .
1400 qq| JOIN parts p ON (a.parts_id = p.id) | .
1401 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1402 qq| WHERE a.bom = '1' | .
1403 qq| AND a.id = ? | . $sortorder;
1404 @values = ($form->{"id_$i"});
1405 $sth = $dbh->prepare($query);
1406 $sth->execute(@values) || $form->dberror($query);
1408 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1409 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1410 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1411 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1412 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1415 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1416 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1421 CVar->get_non_editable_ic_cvars(form => $form,
1424 sub_module => 'orderitems',
1425 may_converted_from => ['orderitems', 'invoice']);
1427 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1428 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1429 for @{ $ic_cvar_configs };
1431 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1435 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1436 $form->{totalweight_nofmt} = $totalweight;
1437 my $defaults = AM->get_defaults();
1438 $form->{weightunit} = $defaults->{weightunit};
1441 foreach $item (sort keys %taxaccounts) {
1442 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1444 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1445 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1446 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1447 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1448 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1449 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1450 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1452 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1453 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1454 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1457 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1458 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1459 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1460 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1462 if($form->{taxincluded}) {
1463 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1464 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1466 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1467 $form->{subtotal_nofmt} = $form->{ordtotal};
1470 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1473 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1475 if ($form->{type} =~ /_quotation/) {
1476 $form->set_payment_options($myconfig, $form->{quodate});
1478 $form->set_payment_options($myconfig, $form->{orddate});
1481 $form->{username} = $myconfig->{name};
1485 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1486 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1488 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1490 $main::lxdebug->leave_sub();
1493 sub project_description {
1494 $main::lxdebug->enter_sub();
1496 my ($self, $dbh, $id) = @_;
1498 my $query = qq|SELECT description FROM project WHERE id = ?|;
1499 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1501 $main::lxdebug->leave_sub();
1512 OE.pm - Order entry module
1516 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>.
1522 =item retrieve_simple PARAMS
1524 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1526 my $order = retrieve_simple(id => 2);