1 #====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1999-2003
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
33 #======================================================================
37 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
48 use SL::HTML::Restrict;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form) = @_;
60 my $dbh = $form->get_standard_dbh;
63 my $ordnumber = 'ordnumber';
69 my ($periodic_invoices_columns, $periodic_invoices_joins);
71 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
73 if ($form->{type} =~ /_quotation$/) {
75 $ordnumber = 'quonumber';
77 } elsif ($form->{type} eq 'sales_order') {
78 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
79 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
82 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
86 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
88 SELECT from_id, ar.amount, ar.netamount FROM (
91 WHERE from_table = 'oe' AND to_table = 'ar'
93 SELECT rl1.from_id, rl2.to_id
95 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
96 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
98 LEFT JOIN ar ON ar.id = rl.to_id
100 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
101 $billed_amount{ $ref->{from_id}} += $ref->{amount};
102 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
107 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
108 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
109 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
110 qq| o.transaction_description, | .
111 qq| o.marge_total, o.marge_percent, | .
112 qq| ex.$rate AS exchangerate, | .
113 qq| pr.projectnumber AS globalprojectnumber, | .
114 qq| e.name AS employee, s.name AS salesman, | .
115 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
116 qq| tz.description AS taxzone | .
117 $periodic_invoices_columns .
118 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
120 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
121 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
122 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
123 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
124 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
125 qq| AND ex.transdate = o.transdate) | .
126 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
127 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
128 qq|$periodic_invoices_joins | .
129 qq|WHERE (o.quotation = ?) |;
130 push(@values, $quotation);
132 my ($null, $split_department_id) = split /--/, $form->{department};
133 my $department_id = $form->{department_id} || $split_department_id;
134 if ($department_id) {
135 $query .= qq| AND o.department_id = ?|;
136 push(@values, $department_id);
139 if ($form->{"project_id"}) {
141 qq|AND ((globalproject_id = ?) OR EXISTS | .
142 qq| (SELECT * FROM orderitems oi | .
143 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
144 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
147 if ($form->{"projectnumber"}) {
149 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
150 SELECT * FROM orderitems oi
151 LEFT JOIN project proi ON proi.id = oi.project_id
152 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
155 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
158 if ($form->{"business_id"}) {
159 $query .= " AND ct.business_id = ?";
160 push(@values, $form->{"business_id"});
163 if ($form->{"${vc}_id"}) {
164 $query .= " AND o.${vc}_id = ?";
165 push(@values, $form->{"${vc}_id"});
167 } elsif ($form->{$vc}) {
168 $query .= " AND ct.name ILIKE ?";
169 push(@values, '%' . $form->{$vc} . '%');
172 if ($form->{"cp_name"}) {
173 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
174 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
177 if (!$main::auth->assert('sales_all_edit', 1)) {
178 $query .= " AND o.employee_id = (select id from employee where login= ?)";
179 push @values, $form->{login};
181 if ($form->{employee_id}) {
182 $query .= " AND o.employee_id = ?";
183 push @values, conv_i($form->{employee_id});
186 if ($form->{salesman_id}) {
187 $query .= " AND o.salesman_id = ?";
188 push @values, conv_i($form->{salesman_id});
191 if (!$form->{open} && !$form->{closed}) {
192 $query .= " AND o.id = 0";
193 } elsif (!($form->{open} && $form->{closed})) {
194 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
197 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
198 ($form->{"notdelivered"} ne $form->{"delivered"})) {
199 $query .= $form->{"delivered"} ?
200 " AND o.delivered " : " AND NOT o.delivered";
203 if ($form->{$ordnumber}) {
204 $query .= qq| AND o.$ordnumber ILIKE ?|;
205 push(@values, '%' . $form->{$ordnumber} . '%');
208 if ($form->{cusordnumber}) {
209 $query .= qq| AND o.cusordnumber ILIKE ?|;
210 push(@values, '%' . $form->{cusordnumber} . '%');
213 if($form->{transdatefrom}) {
214 $query .= qq| AND o.transdate >= ?|;
215 push(@values, conv_date($form->{transdatefrom}));
218 if($form->{transdateto}) {
219 $query .= qq| AND o.transdate <= ?|;
220 push(@values, conv_date($form->{transdateto}));
223 if($form->{reqdatefrom}) {
224 $query .= qq| AND o.reqdate >= ?|;
225 push(@values, conv_date($form->{reqdatefrom}));
228 if($form->{reqdateto}) {
229 $query .= qq| AND o.reqdate <= ?|;
230 push(@values, conv_date($form->{reqdateto}));
233 if ($form->{shippingpoint}) {
234 $query .= qq| AND o.shippingpoint ILIKE ?|;
235 push(@values, '%' . $form->{shippingpoint} . '%');
238 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
239 $query .= qq| AND tz.id = ?|;
240 push(@values, $form->{taxzone_id});
243 if ($form->{transaction_description}) {
244 $query .= qq| AND o.transaction_description ILIKE ?|;
245 push(@values, '%' . $form->{transaction_description} . '%');
248 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
249 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
250 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
253 if ($form->{reqdate_unset_or_old}) {
254 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
257 if (($form->{order_probability_value} || '') ne '') {
258 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
259 $query .= qq| AND (o.order_probability ${op} ?)|;
260 push @values, $form->{order_probability_value};
263 if ($form->{expected_billing_date_from}) {
264 $query .= qq| AND (o.expected_billing_date >= ?)|;
265 push @values, conv_date($form->{expected_billing_date_from});
268 if ($form->{expected_billing_date_to}) {
269 $query .= qq| AND (o.expected_billing_date <= ?)|;
270 push @values, conv_date($form->{expected_billing_date_to});
273 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
274 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
275 my %allowed_sort_columns = (
276 "transdate" => "o.transdate",
277 "reqdate" => "o.reqdate",
279 "ordnumber" => "o.ordnumber",
280 "cusordnumber" => "o.cusordnumber",
281 "quonumber" => "o.quonumber",
283 "employee" => "e.name",
284 "salesman" => "s.name",
285 "shipvia" => "o.shipvia",
286 "transaction_description" => "o.transaction_description",
287 "shippingpoint" => "o.shippingpoint",
288 "taxzone" => "tz.description",
290 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
291 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
293 $query .= qq| ORDER by | . $sortorder;
295 my $sth = $dbh->prepare($query);
296 $sth->execute(@values) ||
297 $form->dberror($query . " (" . join(", ", @values) . ")");
301 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
302 $ref->{billed_amount} = $billed_amount{$ref->{id}};
303 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
304 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
305 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
306 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
307 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
308 $id{ $ref->{id} } = $ref->{id};
313 $main::lxdebug->leave_sub();
316 sub transactions_for_todo_list {
317 $main::lxdebug->enter_sub();
322 my $myconfig = \%main::myconfig;
323 my $form = $main::form;
325 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
327 my $query = qq|SELECT id FROM employee WHERE login = ?|;
328 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
331 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
332 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
337 LEFT JOIN customer c ON (oe.customer_id = c.id)
338 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
339 LEFT JOIN employee e ON (oe.employee_id = e.id)
340 WHERE (COALESCE(quotation, FALSE) = TRUE)
341 AND (COALESCE(closed, FALSE) = FALSE)
342 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
343 AND NOT (oe.reqdate ISNULL)
344 AND (oe.reqdate < current_date)
347 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
349 $main::lxdebug->leave_sub();
355 $main::lxdebug->enter_sub();
357 my ($self, $myconfig, $form) = @_;
359 # connect to database, turn off autocommit
360 my $dbh = $form->get_standard_dbh;
361 my $restricter = SL::HTML::Restrict->create;
363 my ($query, @values, $sth, $null);
364 my $exchangerate = 0;
366 my $all_units = AM->retrieve_units($myconfig, $form);
367 $form->{all_units} = $all_units;
369 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
372 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
373 unless ($form->{employee_id}) {
374 $form->get_employee($dbh);
377 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
379 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
380 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
381 $form->{$number_field} ||= $trans_number->create_unique;
384 $query = qq|DELETE FROM shipto | .
385 qq|WHERE trans_id = ? AND module = 'OE'|;
386 do_query($form, $dbh, $query, $form->{id});
390 $query = qq|SELECT nextval('id')|;
391 ($form->{id}) = selectrow_query($form, $dbh, $query);
393 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
394 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
411 my @processed_orderitems;
413 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
414 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
417 for my $i (1 .. $form->{rowcount}) {
419 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
421 if ($form->{"id_$i"}) {
424 $query = qq|SELECT unit FROM parts WHERE id = ?|;
425 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
428 if (defined($all_units->{$item_unit}->{factor}) &&
429 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
430 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
432 my $baseqty = $form->{"qty_$i"} * $basefactor;
434 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
435 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
437 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
439 # keep entered selling price
441 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
443 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
445 my $decimalplaces = ($dec > 2) ? $dec : 2;
447 # undo discount formatting
448 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
451 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
453 # round linetotal at least to 2 decimal places
454 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
455 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
457 $form->{"inventory_accno_$i"} *= 1;
458 $form->{"expense_accno_$i"} *= 1;
460 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
464 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
466 if ($form->{taxincluded}) {
467 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
468 $taxbase = $linetotal - $taxamount;
470 # we are not keeping a natural price, do not round
471 $form->{"sellprice_$i"} =
472 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
474 $taxamount = $linetotal * $taxrate;
475 $taxbase = $linetotal;
478 if ($form->round_amount($taxrate, 7) == 0) {
479 if ($form->{taxincluded}) {
480 foreach my $item (@taxaccounts) {
481 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
482 $taxaccounts{$item} += $taxamount;
483 $taxdiff += $taxamount;
484 $taxbase{$item} += $taxbase;
486 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
488 foreach my $item (@taxaccounts) {
489 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
490 $taxbase{$item} += $taxbase;
494 foreach my $item (@taxaccounts) {
495 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
496 $taxbase{$item} += $taxbase;
500 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
502 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
504 # Get pricegroup_id and save it. Unfortunately the interface
505 # also uses ID "0" for signalling that none is selected, but "0"
506 # must not be stored in the database. Therefore we cannot simply
508 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
510 $pricegroup_id = undef if !$pricegroup_id;
514 # save detail record in orderitems table
516 if (! $form->{"orderitems_id_$i"}) {
517 $query = qq|SELECT nextval('orderitemsid')|;
518 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
520 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
521 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
523 # get values for CVars from master data for new items
524 $cvars = CVar->get_custom_variables(dbh => $dbh,
526 trans_id => $form->{"id_$i"},
529 # get values for CVars from custom_variables for existing items
530 $cvars = CVar->get_custom_variables(dbh => $dbh,
532 sub_module => 'orderitems',
533 trans_id => $form->{"orderitems_id_$i"},
536 # map only non-editable CVars to form (editable ones are already there)
537 map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
539 my $orderitems_id = $form->{"orderitems_id_$i"};
540 push @processed_orderitems, $orderitems_id;
543 UPDATE orderitems SET
544 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
545 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
546 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
547 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
548 active_price_source = ?, active_discount_source = ?,
549 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
553 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
554 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
555 $form->{"qty_$i"}, $baseqty,
556 $fxsellprice, $form->{"discount_$i"},
557 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
558 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
559 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
560 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
561 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
562 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
563 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
564 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
565 conv_i($orderitems_id),
568 do_query($form, $dbh, $query, @values);
570 $form->{"sellprice_$i"} = $fxsellprice;
571 $form->{"discount_$i"} *= 100;
573 CVar->save_custom_variables(module => 'IC',
574 sub_module => 'orderitems',
575 trans_id => $orderitems_id,
576 configs => $ic_cvar_configs,
578 name_prefix => 'ic_',
579 name_postfix => "_$i",
581 # link previous items with orderitems
582 foreach (qw(orderitems invoice)) {
583 if ($form->{"converted_from_${_}_id_$i"}) {
584 RecordLinks->create_links('dbh' => $dbh,
587 'from_ids' => $form->{"converted_from_${_}_id_$i"},
588 'to_table' => 'orderitems',
589 'to_id' => $orderitems_id,
591 delete $form->{"converted_from_${_}_id_$i"};
596 # search for orphaned ids
597 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
598 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
599 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
601 if (scalar @orphaned_ids) {
602 # clean up orderitems
603 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
604 do_query($form, $dbh, $query, @orphaned_ids);
607 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
611 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
613 $amount = $form->round_amount($netamount + $tax, 2);
614 $netamount = $form->round_amount($netamount, 2);
616 if ($form->{currency} eq $form->{defaultcurrency}) {
617 $form->{exchangerate} = 1;
619 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
622 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
624 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
626 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
631 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
632 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
633 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
634 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
635 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
636 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
637 , order_probability = ?, expected_billing_date = ?
640 @values = ($form->{ordnumber} || '', $form->{quonumber},
641 $form->{cusordnumber}, conv_date($form->{transdate}),
642 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
643 $amount, $netamount, conv_date($reqdate),
644 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
645 $form->{shipvia}, $form->{notes}, $form->{intnotes},
646 $form->{currency}, $form->{closed} ? 't' : 'f',
647 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
648 $quotation, conv_i($form->{department_id}),
649 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
650 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
651 conv_i($form->{delivery_vendor_id}),
652 conv_i($form->{delivery_customer_id}),
653 conv_i($form->{delivery_term_id}),
654 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
655 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
656 $form->{transaction_description},
657 $form->{marge_total} * 1, $form->{marge_percent} * 1,
658 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
659 conv_i($form->{id}));
660 do_query($form, $dbh, $query, @values);
662 $form->{ordtotal} = $amount;
664 $form->{name} = $form->{ $form->{vc} };
665 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
668 if (!$form->{shipto_id}) {
669 $form->add_shipto($dbh, $form->{id}, "OE");
672 # save printed, emailed, queued
673 $form->save_status($dbh);
675 # Link this record to the records it was created from.
676 # check every record type we may link. i am not happy with converting the string to array back
677 # should be a array from the start (OE.pm -> retrieve).
678 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
679 $form->{convert_from_oe_ids} =~ s/^\s+//;
680 $form->{convert_from_oe_ids} =~ s/\s+$//;
681 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
682 delete $form->{convert_from_oe_ids};
683 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
684 foreach (qw(ar oe)) {
685 if ($form->{"convert_from_${_}_ids"}) {
686 RecordLinks->create_links('dbh' => $dbh,
689 'from_ids' => $form->{"convert_from_${_}_ids"},
691 'to_id' => $form->{id},
693 delete $form->{"convert_from_${_}_ids"};
695 $self->_close_quotations_rfqs('dbh' => $dbh,
696 'from_id' => \@convert_from_oe_ids,
697 'to_id' => $form->{id}) if $_ eq 'oe';
700 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
701 if ($form->{vc} eq 'customer') {
702 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
704 if ($form->{vc} eq 'vendor') {
705 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
709 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
710 "quonumber" : "ordnumber"};
712 Common::webdav_folder($form);
714 my $rc = $dbh->commit;
716 $self->save_periodic_invoices_config(dbh => $dbh,
717 oe_id => $form->{id},
718 config_yaml => $form->{periodic_invoices_config})
719 if ($form->{type} eq 'sales_order');
721 $main::lxdebug->leave_sub();
726 sub save_periodic_invoices_config {
727 my ($self, %params) = @_;
729 return if !$params{oe_id};
731 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
732 return if 'HASH' ne ref $config;
734 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
735 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
736 $obj->update_attributes(%{ $config });
739 sub load_periodic_invoice_config {
743 delete $form->{periodic_invoices_config};
746 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
749 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
750 print printer_id copies) };
751 $form->{periodic_invoices_config} = YAML::Dump($config);
756 sub _close_quotations_rfqs {
757 $main::lxdebug->enter_sub();
762 Common::check_params(\%params, qw(from_id to_id));
764 my $myconfig = \%main::myconfig;
765 my $form = $main::form;
767 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
769 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
770 my $sth = prepare_query($form, $dbh, $query);
772 do_statement($form, $sth, $query, conv_i($params{to_id}));
774 my ($quotation) = $sth->fetchrow_array();
777 $main::lxdebug->leave_sub();
783 foreach my $from_id (@{ $params{from_id} }) {
784 $from_id = conv_i($from_id);
785 do_statement($form, $sth, $query, $from_id);
786 ($quotation) = $sth->fetchrow_array();
787 push @close_ids, $from_id if ($quotation);
792 if (scalar @close_ids) {
793 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
794 do_query($form, $dbh, $query, @close_ids);
796 $dbh->commit() unless ($params{dbh});
799 $main::lxdebug->leave_sub();
803 $main::lxdebug->enter_sub();
805 my ($self, $myconfig, $form) = @_;
807 my $rc = SL::DB::Order->new->db->with_transaction(sub {
808 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
810 SL::DB::Order->new(id => $form->{id})->delete;
812 my $spool = $::lx_office_conf{paths}->{spool};
813 unlink map { "$spool/$_" } @spoolfiles if $spool;
818 $main::lxdebug->leave_sub();
824 $main::lxdebug->enter_sub();
826 my ($self, $myconfig, $form) = @_;
828 # connect to database
829 my $dbh = $form->get_standard_dbh;
831 my ($query, $query_add, @values, @ids, $sth);
833 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
835 push @ids, $form->{"trans_id_$_"}
836 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
837 } (1 .. $form->{"rowcount"});
839 if ($form->{rowcount} && scalar @ids) {
840 $form->{convert_from_oe_ids} = join ' ', @ids;
843 # if called in multi id mode, and still only got one id, switch back to single id
844 if ($form->{"rowcount"} and $#ids == 0) {
845 $form->{"id"} = $ids[0];
849 # and remember for the rest of the function
850 my $is_collective_order = scalar @ids;
853 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
854 my $next_workday = DateTime->today_local->add(days => $extra_days);
855 my $day_of_week = $next_workday->day_of_week;
857 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
859 $form->{transdate} = DateTime->today_local->to_kivitendo;
860 $form->{reqdate} = $next_workday->to_kivitendo;
863 # get default accounts
864 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
865 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
866 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
867 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
868 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
871 my $ref = selectfirst_hashref_query($form, $dbh, $query);
872 map { $form->{$_} = $ref->{$_} } keys %$ref;
874 $form->{currency} = $form->get_default_currency($myconfig);
876 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
877 # we come from invoices, feel free.
878 $form->{reqdate} = $form->{deliverydate}
879 if ( $form->{deliverydate}
880 and $form->{callback} =~ /action=ar_transactions/);
882 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
884 if ($form->{id} or @ids) {
886 # retrieve order for single id
887 # NOTE: this query is intended to fetch all information only ONCE.
888 # so if any of these infos is important (or even different) for any item,
889 # it will be killed out and then has to be fetched from the item scope query further down
891 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
892 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
893 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
894 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
895 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
896 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
897 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
898 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
899 , o.order_probability, o.expected_billing_date
901 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
902 LEFT JOIN employee e ON (o.employee_id = e.id)
903 LEFT JOIN department d ON (o.department_id = d.id) | .
906 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
908 @values = $form->{id} ? ($form->{id}) : @ids;
909 $sth = prepare_execute_query($form, $dbh, $query, @values);
911 $ref = $sth->fetchrow_hashref("NAME_lc");
914 map { $form->{$_} = $ref->{$_} } keys %$ref;
916 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
918 # set all entries for multiple ids blank that yield different information
919 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
920 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
924 # if not given, fill transdate with current_date
925 $form->{transdate} = $form->current_date($myconfig)
926 unless $form->{transdate};
930 if ($form->{delivery_customer_id}) {
931 $query = qq|SELECT name FROM customer WHERE id = ?|;
932 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
935 if ($form->{delivery_vendor_id}) {
936 $query = qq|SELECT name FROM customer WHERE id = ?|;
937 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
940 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
942 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
943 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
945 $ref = $sth->fetchrow_hashref("NAME_lc");
947 map { $form->{$_} = $ref->{$_} } keys %$ref;
950 # get printed, emailed and queued
951 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
952 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
954 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
955 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
956 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
957 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
960 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
963 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
965 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
967 # retrieve individual items
968 # this query looks up all information about the items
969 # stuff different from the whole will not be overwritten, but saved with a suffix.
971 qq|SELECT o.id AS orderitems_id,
972 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
973 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
974 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
975 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
976 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
977 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
978 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
979 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
980 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
981 pr.projectnumber, p.formel,
982 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
984 JOIN parts p ON (o.parts_id = p.id)
985 JOIN oe ON (o.trans_id = oe.id)
986 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
987 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)
988 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)
989 LEFT JOIN project pr ON (o.project_id = pr.id)
990 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
992 ? qq|WHERE o.trans_id = ?|
993 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
994 qq|ORDER BY o.trans_id, o.position|;
996 @ids = $form->{id} ? ($form->{id}) : @ids;
997 $sth = prepare_execute_query($form, $dbh, $query, @values);
999 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1000 # Retrieve custom variables.
1001 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1003 sub_module => 'orderitems',
1004 trans_id => $ref->{orderitems_id},
1006 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1009 if (!$ref->{"part_inventory_accno_id"}) {
1010 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1012 delete($ref->{"part_inventory_accno_id"});
1014 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1015 # unless already present there
1016 # remove _oe entries afterwards
1017 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1018 qw|ordnumber transdate cusordnumber|
1020 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1024 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1026 qq|SELECT accno AS inventory_accno, | .
1027 qq| new_chart_id AS inventory_new_chart, | .
1028 qq| date($transdate) - valid_from AS inventory_valid | .
1029 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1030 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1031 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1034 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1036 qq|SELECT accno AS income_accno, | .
1037 qq| new_chart_id AS income_new_chart, | .
1038 qq| date($transdate) - valid_from AS income_valid | .
1039 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1040 ($ref->{income_accno}, $ref->{income_new_chart},
1041 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1044 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1046 qq|SELECT accno AS expense_accno, | .
1047 qq| new_chart_id AS expense_new_chart, | .
1048 qq| date($transdate) - valid_from AS expense_valid | .
1049 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1050 ($ref->{expense_accno}, $ref->{expense_new_chart},
1051 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1054 # delete orderitems_id in collective orders, so that they get cloned no matter what
1055 delete $ref->{orderitems_id} if $is_collective_order;
1057 # get tax rates and description
1058 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1060 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1061 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1062 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1063 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1064 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1065 qq|ORDER BY c.accno|;
1066 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1067 $ref->{taxaccounts} = "";
1069 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1070 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1074 $ref->{taxaccounts} .= "$ptr->{accno} ";
1075 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1076 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1077 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1078 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1079 $form->{taxaccounts} .= "$ptr->{accno} ";
1084 chop $ref->{taxaccounts};
1086 push @{ $form->{form_details} }, $ref;
1093 # get last name used
1094 $form->lastname_used($dbh, $myconfig, $form->{vc})
1095 unless $form->{"$form->{vc}_id"};
1099 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1101 Common::webdav_folder($form);
1103 $self->load_periodic_invoice_config($form);
1105 my $rc = $dbh->commit;
1107 $main::lxdebug->leave_sub();
1112 sub retrieve_simple {
1113 $main::lxdebug->enter_sub();
1118 Common::check_params(\%params, qw(id));
1120 my $myconfig = \%main::myconfig;
1121 my $form = $main::form;
1123 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1125 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1126 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1128 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1129 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1131 $main::lxdebug->leave_sub();
1137 $main::lxdebug->enter_sub();
1139 my ($self, $myconfig, $form) = @_;
1141 # connect to database
1142 my $dbh = $form->get_standard_dbh;
1148 my $nodiscount_subtotal = 0;
1149 my $discount_subtotal = 0;
1152 my @partsgroup = ();
1155 my $subtotal_header = 0;
1156 my $subposition = 0;
1164 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1166 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1167 'departments' => 'ALL_DEPARTMENTS');
1170 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1171 $price_factors{$pfac->{id}} = $pfac;
1172 $pfac->{factor} *= 1;
1173 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1177 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1178 next unless $dept->{id} eq $form->{department_id};
1179 $form->{department} = $dept->{description};
1183 # sort items by partsgroup
1184 for $i (1 .. $form->{rowcount}) {
1186 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1187 $partsgroup = $form->{"partsgroup_$i"};
1189 push @partsgroup, [$i, $partsgroup];
1190 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1196 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1197 %projects_by_id = map { $_->id => $_ } @$projects;
1200 if ($projects_by_id{$form->{"globalproject_id"}}) {
1201 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1202 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1204 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1205 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1209 $form->{discount} = [];
1211 $form->{TEMPLATE_ARRAYS} = { };
1212 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1214 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1215 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1218 qw(runningnumber number description longdescription qty ship unit bin
1219 partnotes serialnumber reqdate sellprice listprice netprice
1220 discount p_discount discount_sub nodiscount_sub
1221 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1222 price_factor price_factor_name partsgroup weight lineweight);
1224 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1225 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1227 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1229 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1231 my $totalweight = 0;
1233 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1236 if ($item->[1] ne $sameitem) {
1237 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1238 $sameitem = $item->[1];
1240 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1243 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1245 if ($form->{"id_$i"} != 0) {
1247 # add number, description and qty to $form->{number}, ....
1249 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1250 $subtotal_header = $i;
1251 $position = int($position);
1254 } elsif ($subtotal_header) {
1256 $position = int($position);
1257 $position = $position.".".$subposition;
1259 $position = int($position);
1263 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1265 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1266 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1267 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1268 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1269 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1270 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1271 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1272 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1273 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1274 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1275 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1276 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1277 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1278 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1279 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1280 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1281 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1282 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1283 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1285 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1286 my ($dec) = ($sellprice =~ /\.(\d+)/);
1287 my $decimalplaces = max 2, length($dec);
1289 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1291 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1292 my $linetotal = $form->round_amount($linetotal_exact, 2);
1294 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1295 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1297 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1299 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1301 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1303 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1304 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1306 $linetotal = ($linetotal != 0) ? $linetotal : '';
1308 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1309 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1310 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1312 $form->{ordtotal} += $linetotal;
1313 $form->{nodiscount_total} += $nodiscount_linetotal;
1314 $form->{discount_total} += $discount;
1316 if ($subtotal_header) {
1317 $discount_subtotal += $linetotal;
1318 $nodiscount_subtotal += $nodiscount_linetotal;
1321 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1322 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1323 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1324 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1325 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1327 $discount_subtotal = 0;
1328 $nodiscount_subtotal = 0;
1329 $subtotal_header = 0;
1332 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1335 if (!$form->{"discount_$i"}) {
1336 $nodiscount += $linetotal;
1339 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1341 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1342 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1343 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1344 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1345 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1346 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1348 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1349 $totalweight += $lineweight;
1350 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1351 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1352 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1353 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1355 my ($taxamount, $taxbase);
1358 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1360 if ($form->{taxincluded}) {
1363 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1364 $taxbase = $linetotal / (1 + $taxrate);
1366 $taxamount = $linetotal * $taxrate;
1367 $taxbase = $linetotal;
1370 if ($taxamount != 0) {
1371 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1372 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1373 $taxbase{$accno} += $taxbase;
1377 $tax_rate = $taxrate * 100;
1378 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1380 if ($form->{"assembly_$i"}) {
1383 # get parts and push them onto the stack
1385 if ($form->{groupitems}) {
1386 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1388 $sortorder = qq|ORDER BY a.oid|;
1391 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1392 qq|pg.partsgroup | .
1393 qq|FROM assembly a | .
1394 qq| JOIN parts p ON (a.parts_id = p.id) | .
1395 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1396 qq| WHERE a.bom = '1' | .
1397 qq| AND a.id = ? | . $sortorder;
1398 @values = ($form->{"id_$i"});
1399 $sth = $dbh->prepare($query);
1400 $sth->execute(@values) || $form->dberror($query);
1402 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1403 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1404 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1405 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1406 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1409 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1410 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1416 if (! $form->{"orderitems_id_$i"}) {
1417 # get values for CVars from master data for new items
1418 $cvars = CVar->get_custom_variables(dbh => $dbh,
1420 trans_id => $form->{"id_$i"},
1423 # get values for CVars from custom_variables for existing items
1424 $cvars = CVar->get_custom_variables(dbh => $dbh,
1426 sub_module => 'orderitems',
1427 trans_id => $form->{"orderitems_id_$i"},
1430 # map only non-editable CVars to form (editable ones are already there)
1431 map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
1433 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1434 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1435 for @{ $ic_cvar_configs };
1437 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1441 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1442 $form->{totalweight_nofmt} = $totalweight;
1443 my $defaults = AM->get_defaults();
1444 $form->{weightunit} = $defaults->{weightunit};
1447 foreach $item (sort keys %taxaccounts) {
1448 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1450 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1451 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1452 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1453 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1454 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1455 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1456 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1458 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1459 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1460 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1463 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1464 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1465 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1466 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1468 if($form->{taxincluded}) {
1469 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1470 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1472 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1473 $form->{subtotal_nofmt} = $form->{ordtotal};
1476 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1479 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1481 if ($form->{type} =~ /_quotation/) {
1482 $form->set_payment_options($myconfig, $form->{quodate});
1484 $form->set_payment_options($myconfig, $form->{orddate});
1487 $form->{username} = $myconfig->{name};
1491 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1492 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1494 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1496 $main::lxdebug->leave_sub();
1499 sub project_description {
1500 $main::lxdebug->enter_sub();
1502 my ($self, $dbh, $id) = @_;
1504 my $query = qq|SELECT description FROM project WHERE id = ?|;
1505 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1507 $main::lxdebug->leave_sub();
1518 OE.pm - Order entry module
1522 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>.
1528 =item retrieve_simple PARAMS
1530 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1532 my $order = retrieve_simple(id => 2);