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 employee e ON (o.employee_id = e.id) | .
122 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
123 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
124 qq| AND ex.transdate = o.transdate) | .
125 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
126 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
127 qq|$periodic_invoices_joins | .
128 qq|WHERE (o.quotation = ?) |;
129 push(@values, $quotation);
131 my ($null, $split_department_id) = split /--/, $form->{department};
132 my $department_id = $form->{department_id} || $split_department_id;
133 if ($department_id) {
134 $query .= qq| AND o.department_id = ?|;
135 push(@values, $department_id);
138 if ($form->{"project_id"}) {
140 qq|AND ((globalproject_id = ?) OR EXISTS | .
141 qq| (SELECT * FROM orderitems oi | .
142 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
143 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
146 if ($form->{"projectnumber"}) {
148 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
149 SELECT * FROM orderitems oi
150 LEFT JOIN project proi ON proi.id = oi.project_id
151 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
154 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
157 if ($form->{"business_id"}) {
158 $query .= " AND ct.business_id = ?";
159 push(@values, $form->{"business_id"});
162 if ($form->{"${vc}_id"}) {
163 $query .= " AND o.${vc}_id = ?";
164 push(@values, $form->{"${vc}_id"});
166 } elsif ($form->{$vc}) {
167 $query .= " AND ct.name ILIKE ?";
168 push(@values, '%' . $form->{$vc} . '%');
171 if (!$main::auth->assert('sales_all_edit', 1)) {
172 $query .= " AND o.employee_id = (select id from employee where login= ?)";
173 push @values, $form->{login};
175 if ($form->{employee_id}) {
176 $query .= " AND o.employee_id = ?";
177 push @values, conv_i($form->{employee_id});
180 if ($form->{salesman_id}) {
181 $query .= " AND o.salesman_id = ?";
182 push @values, conv_i($form->{salesman_id});
185 if (!$form->{open} && !$form->{closed}) {
186 $query .= " AND o.id = 0";
187 } elsif (!($form->{open} && $form->{closed})) {
188 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
191 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
192 ($form->{"notdelivered"} ne $form->{"delivered"})) {
193 $query .= $form->{"delivered"} ?
194 " AND o.delivered " : " AND NOT o.delivered";
197 if ($form->{$ordnumber}) {
198 $query .= qq| AND o.$ordnumber ILIKE ?|;
199 push(@values, '%' . $form->{$ordnumber} . '%');
202 if ($form->{cusordnumber}) {
203 $query .= qq| AND o.cusordnumber ILIKE ?|;
204 push(@values, '%' . $form->{cusordnumber} . '%');
207 if($form->{transdatefrom}) {
208 $query .= qq| AND o.transdate >= ?|;
209 push(@values, conv_date($form->{transdatefrom}));
212 if($form->{transdateto}) {
213 $query .= qq| AND o.transdate <= ?|;
214 push(@values, conv_date($form->{transdateto}));
217 if($form->{reqdatefrom}) {
218 $query .= qq| AND o.reqdate >= ?|;
219 push(@values, conv_date($form->{reqdatefrom}));
222 if($form->{reqdateto}) {
223 $query .= qq| AND o.reqdate <= ?|;
224 push(@values, conv_date($form->{reqdateto}));
227 if ($form->{shippingpoint}) {
228 $query .= qq| AND o.shippingpoint ILIKE ?|;
229 push(@values, '%' . $form->{shippingpoint} . '%');
232 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
233 $query .= qq| AND tz.id = ?|;
234 push(@values, $form->{taxzone_id});
237 if ($form->{transaction_description}) {
238 $query .= qq| AND o.transaction_description ILIKE ?|;
239 push(@values, '%' . $form->{transaction_description} . '%');
242 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
243 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
244 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
247 if (($form->{order_probability_value} || '') ne '') {
248 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
249 $query .= qq| AND (o.order_probability ${op} ?)|;
250 push @values, $form->{order_probability_value};
253 if ($form->{expected_billing_date_from}) {
254 $query .= qq| AND (o.expected_billing_date >= ?)|;
255 push @values, conv_date($form->{expected_billing_date_from});
258 if ($form->{expected_billing_date_to}) {
259 $query .= qq| AND (o.expected_billing_date <= ?)|;
260 push @values, conv_date($form->{expected_billing_date_to});
263 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
264 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
265 my %allowed_sort_columns = (
266 "transdate" => "o.transdate",
267 "reqdate" => "o.reqdate",
269 "ordnumber" => "o.ordnumber",
270 "cusordnumber" => "o.cusordnumber",
271 "quonumber" => "o.quonumber",
273 "employee" => "e.name",
274 "salesman" => "s.name",
275 "shipvia" => "o.shipvia",
276 "transaction_description" => "o.transaction_description",
277 "shippingpoint" => "o.shippingpoint",
278 "taxzone" => "tz.description",
280 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
281 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
283 $query .= qq| ORDER by | . $sortorder;
285 my $sth = $dbh->prepare($query);
286 $sth->execute(@values) ||
287 $form->dberror($query . " (" . join(", ", @values) . ")");
291 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
292 $ref->{billed_amount} = $billed_amount{$ref->{id}};
293 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
294 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
295 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
296 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
297 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
298 $id{ $ref->{id} } = $ref->{id};
303 $main::lxdebug->leave_sub();
306 sub transactions_for_todo_list {
307 $main::lxdebug->enter_sub();
312 my $myconfig = \%main::myconfig;
313 my $form = $main::form;
315 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
317 my $query = qq|SELECT id FROM employee WHERE login = ?|;
318 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
321 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
322 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
327 LEFT JOIN customer c ON (oe.customer_id = c.id)
328 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
329 LEFT JOIN employee e ON (oe.employee_id = e.id)
330 WHERE (COALESCE(quotation, FALSE) = TRUE)
331 AND (COALESCE(closed, FALSE) = FALSE)
332 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
333 AND NOT (oe.reqdate ISNULL)
334 AND (oe.reqdate < current_date)
337 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
339 $main::lxdebug->leave_sub();
345 $main::lxdebug->enter_sub();
347 my ($self, $myconfig, $form) = @_;
349 # connect to database, turn off autocommit
350 my $dbh = $form->get_standard_dbh;
351 my $restricter = SL::HTML::Restrict->create;
353 my ($query, @values, $sth, $null);
354 my $exchangerate = 0;
356 my $all_units = AM->retrieve_units($myconfig, $form);
357 $form->{all_units} = $all_units;
359 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
362 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
363 unless ($form->{employee_id}) {
364 $form->get_employee($dbh);
367 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
369 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
370 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
371 $form->{$number_field} ||= $trans_number->create_unique;
374 $query = qq|DELETE FROM custom_variables
375 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
376 AND (sub_module = 'orderitems')
377 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
378 do_query($form, $dbh, $query, $form->{id});
380 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
381 do_query($form, $dbh, $query, $form->{id});
383 $query = qq|DELETE FROM shipto | .
384 qq|WHERE trans_id = ? AND module = 'OE'|;
385 do_query($form, $dbh, $query, $form->{id});
389 $query = qq|SELECT nextval('id')|;
390 ($form->{id}) = selectrow_query($form, $dbh, $query);
392 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
393 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
411 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
412 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
415 for my $i (1 .. $form->{rowcount}) {
417 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
419 if ($form->{"id_$i"}) {
422 $query = qq|SELECT unit FROM parts WHERE id = ?|;
423 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
426 if (defined($all_units->{$item_unit}->{factor}) &&
427 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
428 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
430 my $baseqty = $form->{"qty_$i"} * $basefactor;
432 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
433 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
435 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
437 # keep entered selling price
439 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
441 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
443 my $decimalplaces = ($dec > 2) ? $dec : 2;
445 # undo discount formatting
446 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
449 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
451 # round linetotal at least to 2 decimal places
452 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
453 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
455 $form->{"inventory_accno_$i"} *= 1;
456 $form->{"expense_accno_$i"} *= 1;
458 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
462 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
464 if ($form->{taxincluded}) {
465 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
466 $taxbase = $linetotal - $taxamount;
468 # we are not keeping a natural price, do not round
469 $form->{"sellprice_$i"} =
470 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
472 $taxamount = $linetotal * $taxrate;
473 $taxbase = $linetotal;
476 if ($form->round_amount($taxrate, 7) == 0) {
477 if ($form->{taxincluded}) {
478 foreach my $item (@taxaccounts) {
479 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
480 $taxaccounts{$item} += $taxamount;
481 $taxdiff += $taxamount;
482 $taxbase{$item} += $taxbase;
484 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
486 foreach my $item (@taxaccounts) {
487 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
488 $taxbase{$item} += $taxbase;
492 foreach my $item (@taxaccounts) {
493 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
494 $taxbase{$item} += $taxbase;
498 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
500 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
502 # Get pricegroup_id and save it. Unfortunately the interface
503 # also uses ID "0" for signalling that none is selected, but "0"
504 # must not be stored in the database. Therefore we cannot simply
506 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
508 $pricegroup_id = undef if !$pricegroup_id;
510 # save detail record in orderitems table
511 my $orderitems_id = $form->{"orderitems_id_$i"};
512 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
515 $query = qq|INSERT INTO orderitems (
516 id, trans_id, parts_id, description, longdescription, qty, base_qty,
517 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
518 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
519 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
520 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
521 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
523 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
524 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
525 $form->{"qty_$i"}, $baseqty,
526 $fxsellprice, $form->{"discount_$i"},
527 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
528 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
529 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
530 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
531 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
532 $form->{"lastcost_$i"},
533 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
534 conv_i($form->{"marge_price_factor_$i"}));
535 do_query($form, $dbh, $query, @values);
537 $form->{"sellprice_$i"} = $fxsellprice;
538 $form->{"discount_$i"} *= 100;
540 CVar->save_custom_variables(module => 'IC',
541 sub_module => 'orderitems',
542 trans_id => $orderitems_id,
543 configs => $ic_cvar_configs,
545 name_prefix => 'ic_',
546 name_postfix => "_$i",
551 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
555 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
557 $amount = $form->round_amount($netamount + $tax, 2);
558 $netamount = $form->round_amount($netamount, 2);
560 if ($form->{currency} eq $form->{defaultcurrency}) {
561 $form->{exchangerate} = 1;
563 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
566 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
568 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
570 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
575 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
576 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
577 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
578 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
579 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
580 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
581 , order_probability = ?, expected_billing_date = ?
584 @values = ($form->{ordnumber} || '', $form->{quonumber},
585 $form->{cusordnumber}, conv_date($form->{transdate}),
586 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
587 $amount, $netamount, conv_date($reqdate),
588 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
589 $form->{shipvia}, $form->{notes}, $form->{intnotes},
590 $form->{currency}, $form->{closed} ? 't' : 'f',
591 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
592 $quotation, conv_i($form->{department_id}),
593 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
594 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
595 conv_i($form->{delivery_vendor_id}),
596 conv_i($form->{delivery_customer_id}),
597 conv_i($form->{delivery_term_id}),
598 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
599 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
600 $form->{transaction_description},
601 $form->{marge_total} * 1, $form->{marge_percent} * 1,
602 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
603 conv_i($form->{id}));
604 do_query($form, $dbh, $query, @values);
606 $form->{ordtotal} = $amount;
609 $form->{name} = $form->{ $form->{vc} };
610 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
612 if (!$form->{shipto_id}) {
613 $form->add_shipto($dbh, $form->{id}, "OE");
616 # save printed, emailed, queued
617 $form->save_status($dbh);
619 # Link this record to the records it was created from.
620 $form->{convert_from_oe_ids} =~ s/^\s+//;
621 $form->{convert_from_oe_ids} =~ s/\s+$//;
622 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
623 delete $form->{convert_from_oe_ids};
625 if (scalar @convert_from_oe_ids) {
626 RecordLinks->create_links('dbh' => $dbh,
628 'from_table' => 'oe',
629 'from_ids' => \@convert_from_oe_ids,
631 'to_id' => $form->{id},
634 $self->_close_quotations_rfqs('dbh' => $dbh,
635 'from_id' => \@convert_from_oe_ids,
636 'to_id' => $form->{id});
639 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
640 if ($form->{vc} eq 'customer') {
641 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
643 if ($form->{vc} eq 'vendor') {
644 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
648 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
649 "quonumber" : "ordnumber"};
651 Common::webdav_folder($form);
653 my $rc = $dbh->commit;
655 $self->save_periodic_invoices_config(dbh => $dbh,
656 oe_id => $form->{id},
657 config_yaml => $form->{periodic_invoices_config})
658 if ($form->{type} eq 'sales_order');
660 $main::lxdebug->leave_sub();
665 sub save_periodic_invoices_config {
666 my ($self, %params) = @_;
668 return if !$params{oe_id};
670 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
671 return if 'HASH' ne ref $config;
673 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
674 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
675 $obj->update_attributes(%{ $config });
678 sub load_periodic_invoice_config {
682 delete $form->{periodic_invoices_config};
685 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
688 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
689 print printer_id copies) };
690 $form->{periodic_invoices_config} = YAML::Dump($config);
695 sub _close_quotations_rfqs {
696 $main::lxdebug->enter_sub();
701 Common::check_params(\%params, qw(from_id to_id));
703 my $myconfig = \%main::myconfig;
704 my $form = $main::form;
706 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
708 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
709 my $sth = prepare_query($form, $dbh, $query);
711 do_statement($form, $sth, $query, conv_i($params{to_id}));
713 my ($quotation) = $sth->fetchrow_array();
716 $main::lxdebug->leave_sub();
722 foreach my $from_id (@{ $params{from_id} }) {
723 $from_id = conv_i($from_id);
724 do_statement($form, $sth, $query, $from_id);
725 ($quotation) = $sth->fetchrow_array();
726 push @close_ids, $from_id if ($quotation);
731 if (scalar @close_ids) {
732 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
733 do_query($form, $dbh, $query, @close_ids);
735 $dbh->commit() unless ($params{dbh});
738 $main::lxdebug->leave_sub();
742 $main::lxdebug->enter_sub();
744 my ($self, $myconfig, $form) = @_;
746 my $rc = SL::DB::Order->new->db->with_transaction(sub {
747 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
749 SL::DB::Order->new(id => $form->{id})->delete;
751 my $spool = $::lx_office_conf{paths}->{spool};
752 unlink map { "$spool/$_" } @spoolfiles if $spool;
757 $main::lxdebug->leave_sub();
763 $main::lxdebug->enter_sub();
765 my ($self, $myconfig, $form) = @_;
767 # connect to database
768 my $dbh = $form->get_standard_dbh;
770 my ($query, $query_add, @values, @ids, $sth);
772 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
774 push @ids, $form->{"trans_id_$_"}
775 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
776 } (1 .. $form->{"rowcount"});
778 if ($form->{rowcount} && scalar @ids) {
779 $form->{convert_from_oe_ids} = join ' ', @ids;
782 # if called in multi id mode, and still only got one id, switch back to single id
783 if ($form->{"rowcount"} and $#ids == 0) {
784 $form->{"id"} = $ids[0];
788 # and remember for the rest of the function
789 my $is_collective_order = scalar @ids;
792 my $wday = (localtime(time))[6];
793 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
794 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
797 # get default accounts
798 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
799 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
800 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
801 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
802 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
805 my $ref = selectfirst_hashref_query($form, $dbh, $query);
806 map { $form->{$_} = $ref->{$_} } keys %$ref;
808 $form->{currency} = $form->get_default_currency($myconfig);
810 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
811 # we come from invoices, feel free.
812 $form->{reqdate} = $form->{deliverydate}
813 if ( $form->{deliverydate}
814 and $form->{callback} =~ /action=ar_transactions/);
816 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
818 if ($form->{id} or @ids) {
820 # retrieve order for single id
821 # NOTE: this query is intended to fetch all information only ONCE.
822 # so if any of these infos is important (or even different) for any item,
823 # it will be killed out and then has to be fetched from the item scope query further down
825 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
826 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
827 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
828 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
829 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
830 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
831 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
832 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
833 , o.order_probability, o.expected_billing_date
835 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
836 LEFT JOIN employee e ON (o.employee_id = e.id)
837 LEFT JOIN department d ON (o.department_id = d.id) | .
840 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
842 @values = $form->{id} ? ($form->{id}) : @ids;
843 $sth = prepare_execute_query($form, $dbh, $query, @values);
845 $ref = $sth->fetchrow_hashref("NAME_lc");
848 map { $form->{$_} = $ref->{$_} } keys %$ref;
850 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
852 # set all entries for multiple ids blank that yield different information
853 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
854 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
858 # if not given, fill transdate with current_date
859 $form->{transdate} = $form->current_date($myconfig)
860 unless $form->{transdate};
864 if ($form->{delivery_customer_id}) {
865 $query = qq|SELECT name FROM customer WHERE id = ?|;
866 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
869 if ($form->{delivery_vendor_id}) {
870 $query = qq|SELECT name FROM customer WHERE id = ?|;
871 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
874 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
876 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
877 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
879 $ref = $sth->fetchrow_hashref("NAME_lc");
881 map { $form->{$_} = $ref->{$_} } keys %$ref;
884 # get printed, emailed and queued
885 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
886 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
888 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
889 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
890 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
891 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
894 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
897 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
899 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
901 # retrieve individual items
902 # this query looks up all information about the items
903 # stuff different from the whole will not be overwritten, but saved with a suffix.
905 qq|SELECT o.id AS orderitems_id,
906 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
907 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
908 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
909 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
910 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
911 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
912 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
913 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
914 o.price_factor_id, o.price_factor, o.marge_price_factor,
915 pr.projectnumber, p.formel,
916 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
918 JOIN parts p ON (o.parts_id = p.id)
919 JOIN oe ON (o.trans_id = oe.id)
920 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
921 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
922 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
923 LEFT JOIN project pr ON (o.project_id = pr.id)
924 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
926 ? qq|WHERE o.trans_id = ?|
927 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
930 @ids = $form->{id} ? ($form->{id}) : @ids;
931 $sth = prepare_execute_query($form, $dbh, $query, @values);
933 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
934 # Retrieve custom variables.
935 my $cvars = CVar->get_custom_variables(dbh => $dbh,
937 sub_module => 'orderitems',
938 trans_id => $ref->{orderitems_id},
940 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
943 if (!$ref->{"part_inventory_accno_id"}) {
944 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
946 delete($ref->{"part_inventory_accno_id"});
948 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
949 # unless already present there
950 # remove _oe entries afterwards
951 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
952 qw|ordnumber transdate cusordnumber|
954 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
958 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
960 qq|SELECT accno AS inventory_accno, | .
961 qq| new_chart_id AS inventory_new_chart, | .
962 qq| date($transdate) - valid_from AS inventory_valid | .
963 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
964 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
965 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
968 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
970 qq|SELECT accno AS income_accno, | .
971 qq| new_chart_id AS income_new_chart, | .
972 qq| date($transdate) - valid_from AS income_valid | .
973 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
974 ($ref->{income_accno}, $ref->{income_new_chart},
975 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
978 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
980 qq|SELECT accno AS expense_accno, | .
981 qq| new_chart_id AS expense_new_chart, | .
982 qq| date($transdate) - valid_from AS expense_valid | .
983 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
984 ($ref->{expense_accno}, $ref->{expense_new_chart},
985 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
988 # delete orderitems_id in collective orders, so that they get cloned no matter what
989 delete $ref->{orderitems_id} if (@ids);
991 # get tax rates and description
992 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
994 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
995 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
996 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
997 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
998 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
999 qq|ORDER BY c.accno|;
1000 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1001 $ref->{taxaccounts} = "";
1003 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1004 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1008 $ref->{taxaccounts} .= "$ptr->{accno} ";
1009 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1010 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1011 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1012 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1013 $form->{taxaccounts} .= "$ptr->{accno} ";
1018 chop $ref->{taxaccounts};
1020 push @{ $form->{form_details} }, $ref;
1027 # get last name used
1028 $form->lastname_used($dbh, $myconfig, $form->{vc})
1029 unless $form->{"$form->{vc}_id"};
1033 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1035 Common::webdav_folder($form);
1037 $self->load_periodic_invoice_config($form);
1039 my $rc = $dbh->commit;
1041 $main::lxdebug->leave_sub();
1046 sub retrieve_simple {
1047 $main::lxdebug->enter_sub();
1052 Common::check_params(\%params, qw(id));
1054 my $myconfig = \%main::myconfig;
1055 my $form = $main::form;
1057 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1059 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1060 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1062 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1063 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1065 $main::lxdebug->leave_sub();
1071 $main::lxdebug->enter_sub();
1073 my ($self, $myconfig, $form) = @_;
1075 # connect to database
1076 my $dbh = $form->get_standard_dbh;
1082 my $nodiscount_subtotal = 0;
1083 my $discount_subtotal = 0;
1086 my @partsgroup = ();
1089 my $subtotal_header = 0;
1090 my $subposition = 0;
1098 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1100 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1101 'departments' => 'ALL_DEPARTMENTS');
1104 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1105 $price_factors{$pfac->{id}} = $pfac;
1106 $pfac->{factor} *= 1;
1107 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1111 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1112 next unless $dept->{id} eq $form->{department_id};
1113 $form->{department} = $dept->{description};
1117 # sort items by partsgroup
1118 for $i (1 .. $form->{rowcount}) {
1120 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1121 $partsgroup = $form->{"partsgroup_$i"};
1123 push @partsgroup, [$i, $partsgroup];
1124 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1130 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1131 %projects_by_id = map { $_->id => $_ } @$projects;
1134 if ($projects_by_id{$form->{"globalproject_id"}}) {
1135 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1136 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1138 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1139 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1143 $form->{discount} = [];
1145 $form->{TEMPLATE_ARRAYS} = { };
1146 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1148 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1149 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1152 qw(runningnumber number description longdescription qty ship unit bin
1153 partnotes serialnumber reqdate sellprice listprice netprice
1154 discount p_discount discount_sub nodiscount_sub
1155 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1156 price_factor price_factor_name partsgroup weight lineweight);
1158 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1159 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1161 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1163 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1165 my $totalweight = 0;
1167 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1170 if ($item->[1] ne $sameitem) {
1171 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1172 $sameitem = $item->[1];
1174 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1177 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1179 if ($form->{"id_$i"} != 0) {
1181 # add number, description and qty to $form->{number}, ....
1183 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1184 $subtotal_header = $i;
1185 $position = int($position);
1188 } elsif ($subtotal_header) {
1190 $position = int($position);
1191 $position = $position.".".$subposition;
1193 $position = int($position);
1197 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1199 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1200 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1201 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1202 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1203 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1204 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1205 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1206 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1207 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1208 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1209 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1210 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1211 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1212 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1213 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1214 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1215 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1216 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1217 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1219 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1220 my ($dec) = ($sellprice =~ /\.(\d+)/);
1221 my $decimalplaces = max 2, length($dec);
1223 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1225 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1226 my $linetotal = $form->round_amount($linetotal_exact, 2);
1228 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1229 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1231 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1233 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1235 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1237 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1238 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1240 $linetotal = ($linetotal != 0) ? $linetotal : '';
1242 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1243 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1244 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1246 $form->{ordtotal} += $linetotal;
1247 $form->{nodiscount_total} += $nodiscount_linetotal;
1248 $form->{discount_total} += $discount;
1250 if ($subtotal_header) {
1251 $discount_subtotal += $linetotal;
1252 $nodiscount_subtotal += $nodiscount_linetotal;
1255 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1256 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1257 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1258 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1259 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1261 $discount_subtotal = 0;
1262 $nodiscount_subtotal = 0;
1263 $subtotal_header = 0;
1266 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1269 if (!$form->{"discount_$i"}) {
1270 $nodiscount += $linetotal;
1273 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1275 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1276 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1277 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1278 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1279 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1280 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1282 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1283 $totalweight += $lineweight;
1284 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1285 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1286 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1287 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1289 my ($taxamount, $taxbase);
1292 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1294 if ($form->{taxincluded}) {
1297 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1298 $taxbase = $linetotal / (1 + $taxrate);
1300 $taxamount = $linetotal * $taxrate;
1301 $taxbase = $linetotal;
1304 if ($taxamount != 0) {
1305 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1306 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1307 $taxbase{$accno} += $taxbase;
1311 $tax_rate = $taxrate * 100;
1312 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1314 if ($form->{"assembly_$i"}) {
1317 # get parts and push them onto the stack
1319 if ($form->{groupitems}) {
1320 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1322 $sortorder = qq|ORDER BY a.oid|;
1325 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1326 qq|pg.partsgroup | .
1327 qq|FROM assembly a | .
1328 qq| JOIN parts p ON (a.parts_id = p.id) | .
1329 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1330 qq| WHERE a.bom = '1' | .
1331 qq| AND a.id = ? | . $sortorder;
1332 @values = ($form->{"id_$i"});
1333 $sth = $dbh->prepare($query);
1334 $sth->execute(@values) || $form->dberror($query);
1336 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1337 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1338 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1339 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1340 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1343 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1344 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1349 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1350 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1351 for @{ $ic_cvar_configs };
1353 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1357 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1358 $form->{totalweight_nofmt} = $totalweight;
1359 my $defaults = AM->get_defaults();
1360 $form->{weightunit} = $defaults->{weightunit};
1363 foreach $item (sort keys %taxaccounts) {
1364 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1366 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1367 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1368 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1369 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1371 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1372 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1374 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1375 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1379 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1380 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1381 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1382 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1384 if($form->{taxincluded}) {
1385 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1386 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1388 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1389 $form->{subtotal_nofmt} = $form->{ordtotal};
1392 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1395 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1397 if ($form->{type} =~ /_quotation/) {
1398 $form->set_payment_options($myconfig, $form->{quodate});
1400 $form->set_payment_options($myconfig, $form->{orddate});
1403 $form->{username} = $myconfig->{name};
1407 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1408 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1410 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1412 $main::lxdebug->leave_sub();
1415 sub project_description {
1416 $main::lxdebug->enter_sub();
1418 my ($self, $dbh, $id) = @_;
1420 my $query = qq|SELECT description FROM project WHERE id = ?|;
1421 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1423 $main::lxdebug->leave_sub();
1434 OE.pm - Order entry module
1438 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>.
1444 =item retrieve_simple PARAMS
1446 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1448 my $order = retrieve_simple(id => 2);