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., 51 Franklin Street, Fifth Floor, Boston,
30 #======================================================================
34 #======================================================================
38 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
46 use SL::DB::ProjectType;
50 use SL::HTML::Restrict;
53 use SL::Util qw(trim);
61 $main::lxdebug->enter_sub();
63 my ($self, $myconfig, $form) = @_;
66 my $dbh = $form->get_standard_dbh;
69 my $ordnumber = 'ordnumber';
75 my ($periodic_invoices_columns, $periodic_invoices_joins);
77 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
79 if ($form->{type} =~ /_quotation$/) {
81 $ordnumber = 'quonumber';
83 } elsif ($form->{type} eq 'sales_order') {
84 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
85 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
88 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
92 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
94 SELECT from_id, ar.amount, ar.netamount FROM (
97 WHERE from_table = 'oe' AND to_table = 'ar'
99 SELECT rl1.from_id, rl2.to_id
100 FROM record_links rl1
101 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
102 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
104 LEFT JOIN ar ON ar.id = rl.to_id
106 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
107 $billed_amount{ $ref->{from_id}} += $ref->{amount};
108 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
113 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
114 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
115 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
116 qq| o.transaction_description, | .
117 qq| o.marge_total, o.marge_percent, | .
118 qq| o.itime::DATE AS insertdate, | .
119 qq| department.description as department, | .
120 qq| ex.$rate AS exchangerate, | .
121 qq| pt.description AS payment_terms, | .
122 qq| pr.projectnumber AS globalprojectnumber, | .
123 qq| e.name AS employee, s.name AS salesman, | .
124 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
125 qq| tz.description AS taxzone | .
126 $periodic_invoices_columns .
127 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
129 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
130 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
131 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
132 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
133 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
134 qq| AND ex.transdate = o.transdate) | .
135 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
136 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
137 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
138 qq|LEFT JOIN department ON (o.department_id = department.id) | .
139 qq|$periodic_invoices_joins | .
140 qq|WHERE (o.quotation = ?) |;
141 push(@values, $quotation);
143 if ($form->{department_id}) {
144 $query .= qq| AND o.department_id = ?|;
145 push(@values, $form->{department_id});
148 if ($form->{"project_id"}) {
150 qq|AND ((globalproject_id = ?) OR EXISTS | .
151 qq| (SELECT * FROM orderitems oi | .
152 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
153 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
156 if ($form->{"projectnumber"}) {
158 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
159 SELECT * FROM orderitems oi
160 LEFT JOIN project proi ON proi.id = oi.project_id
161 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
164 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
167 if ($form->{"business_id"}) {
168 $query .= " AND ct.business_id = ?";
169 push(@values, $form->{"business_id"});
172 if ($form->{"${vc}_id"}) {
173 $query .= " AND o.${vc}_id = ?";
174 push(@values, $form->{"${vc}_id"});
176 } elsif ($form->{$vc}) {
177 $query .= " AND ct.name ILIKE ?";
178 push(@values, like($form->{$vc}));
181 if ($form->{"cp_name"}) {
182 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
183 push(@values, (like($form->{"cp_name"}))x2);
186 if (!$main::auth->assert('sales_all_edit', 1)) {
187 $query .= " AND o.employee_id = (select id from employee where login= ?)";
188 push @values, $::myconfig{login};
190 if ($form->{employee_id}) {
191 $query .= " AND o.employee_id = ?";
192 push @values, conv_i($form->{employee_id});
195 if ($form->{salesman_id}) {
196 $query .= " AND o.salesman_id = ?";
197 push @values, conv_i($form->{salesman_id});
200 if (!$form->{open} && !$form->{closed}) {
201 $query .= " AND o.id = 0";
202 } elsif (!($form->{open} && $form->{closed})) {
203 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
206 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
207 ($form->{"notdelivered"} ne $form->{"delivered"})) {
208 $query .= $form->{"delivered"} ?
209 " AND o.delivered " : " AND NOT o.delivered";
212 if ($form->{$ordnumber}) {
213 $query .= qq| AND o.$ordnumber ILIKE ?|;
214 push(@values, like($form->{$ordnumber}));
217 if ($form->{cusordnumber}) {
218 $query .= qq| AND o.cusordnumber ILIKE ?|;
219 push(@values, like($form->{cusordnumber}));
222 if($form->{transdatefrom}) {
223 $query .= qq| AND o.transdate >= ?|;
224 push(@values, conv_date($form->{transdatefrom}));
227 if($form->{transdateto}) {
228 $query .= qq| AND o.transdate <= ?|;
229 push(@values, conv_date($form->{transdateto}));
232 if($form->{reqdatefrom}) {
233 $query .= qq| AND o.reqdate >= ?|;
234 push(@values, conv_date($form->{reqdatefrom}));
237 if($form->{reqdateto}) {
238 $query .= qq| AND o.reqdate <= ?|;
239 push(@values, conv_date($form->{reqdateto}));
242 if($form->{insertdatefrom}) {
243 $query .= qq| AND o.itime::DATE >= ?|;
244 push(@values, conv_date($form->{insertdatefrom}));
247 if($form->{insertdateto}) {
248 $query .= qq| AND o.itime::DATE <= ?|;
249 push(@values, conv_date($form->{insertdateto}));
252 if ($form->{shippingpoint}) {
253 $query .= qq| AND o.shippingpoint ILIKE ?|;
254 push(@values, like($form->{shippingpoint}));
257 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
258 $query .= qq| AND tz.id = ?|;
259 push(@values, $form->{taxzone_id});
262 if ($form->{transaction_description}) {
263 $query .= qq| AND o.transaction_description ILIKE ?|;
264 push(@values, like($form->{transaction_description}));
267 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
268 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
269 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
272 if ($form->{reqdate_unset_or_old}) {
273 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
276 if (($form->{order_probability_value} || '') ne '') {
277 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
278 $query .= qq| AND (o.order_probability ${op} ?)|;
279 push @values, trim($form->{order_probability_value});
282 if ($form->{expected_billing_date_from}) {
283 $query .= qq| AND (o.expected_billing_date >= ?)|;
284 push @values, conv_date($form->{expected_billing_date_from});
287 if ($form->{expected_billing_date_to}) {
288 $query .= qq| AND (o.expected_billing_date <= ?)|;
289 push @values, conv_date($form->{expected_billing_date_to});
292 if ($form->{parts_partnumber}) {
295 SELECT orderitems.trans_id
297 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
298 WHERE (orderitems.trans_id = o.id)
299 AND (parts.partnumber ILIKE ?)
303 push @values, like($form->{parts_partnumber});
306 if ($form->{parts_description}) {
309 SELECT orderitems.trans_id
311 WHERE (orderitems.trans_id = o.id)
312 AND (orderitems.description ILIKE ?)
316 push @values, like($form->{parts_description});
320 my @tokens = parse_line('\s+', 0, $form->{all});
321 # ordnumber quonumber customer.name vendor.name transaction_description
323 o.ordnumber ILIKE ? OR
324 o.quonumber ILIKE ? OR
326 o.transaction_description ILIKE ?
328 push @values, (like($_))x4 for @tokens;
331 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
332 'trans_id_field' => 'ct.id',
336 $query .= qq| AND ($cvar_where)|;
337 push @values, @cvar_values;
340 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
341 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
342 my %allowed_sort_columns = (
343 "transdate" => "o.transdate",
344 "reqdate" => "o.reqdate",
346 "ordnumber" => "o.ordnumber",
347 "cusordnumber" => "o.cusordnumber",
348 "quonumber" => "o.quonumber",
350 "employee" => "e.name",
351 "salesman" => "s.name",
352 "shipvia" => "o.shipvia",
353 "transaction_description" => "o.transaction_description",
354 "shippingpoint" => "o.shippingpoint",
355 "insertdate" => "o.itime",
356 "taxzone" => "tz.description",
357 "payment_terms" => "pt.description",
358 "department" => "department.description",
360 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
361 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
363 $query .= qq| ORDER by | . $sortorder;
365 my $sth = $dbh->prepare($query);
366 $sth->execute(@values) ||
367 $form->dberror($query . " (" . join(", ", @values) . ")");
371 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
372 $ref->{billed_amount} = $billed_amount{$ref->{id}};
373 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
374 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
375 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
376 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
377 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
378 $id{ $ref->{id} } = $ref->{id};
383 $main::lxdebug->leave_sub();
386 sub transactions_for_todo_list {
387 $main::lxdebug->enter_sub();
392 my $myconfig = \%main::myconfig;
393 my $form = $main::form;
395 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
397 my $query = qq|SELECT id FROM employee WHERE login = ?|;
398 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
401 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
402 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
407 LEFT JOIN customer c ON (oe.customer_id = c.id)
408 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
409 LEFT JOIN employee e ON (oe.employee_id = e.id)
410 WHERE (COALESCE(quotation, FALSE) = TRUE)
411 AND (COALESCE(closed, FALSE) = FALSE)
412 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
413 AND NOT (oe.reqdate ISNULL)
414 AND (oe.reqdate < current_date)
417 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
419 $main::lxdebug->leave_sub();
425 my ($self, $myconfig, $form) = @_;
426 $main::lxdebug->enter_sub();
428 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
430 $::lxdebug->leave_sub;
436 $main::lxdebug->enter_sub();
438 my ($self, $myconfig, $form) = @_;
440 my $dbh = SL::DB->client->dbh;
441 my $restricter = SL::HTML::Restrict->create;
443 my ($query, @values, $sth, $null);
444 my $exchangerate = 0;
446 my $all_units = AM->retrieve_units($myconfig, $form);
447 $form->{all_units} = $all_units;
449 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
452 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
453 unless ($form->{employee_id}) {
454 $form->get_employee($dbh);
457 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
459 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
460 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
461 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
464 $query = qq|DELETE FROM custom_variables
465 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
466 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
467 do_query($form, $dbh, $query, $form->{id});
469 $query = qq|DELETE FROM shipto | .
470 qq|WHERE trans_id = ? AND module = 'OE'|;
471 do_query($form, $dbh, $query, $form->{id});
475 $query = qq|SELECT nextval('id')|;
476 ($form->{id}) = selectrow_query($form, $dbh, $query);
478 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
479 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
496 my @processed_orderitems;
498 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
499 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
502 for my $i (1 .. $form->{rowcount}) {
504 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
506 if ($form->{"id_$i"}) {
509 $query = qq|SELECT unit FROM parts WHERE id = ?|;
510 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
513 if (defined($all_units->{$item_unit}->{factor}) &&
514 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
515 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
517 my $baseqty = $form->{"qty_$i"} * $basefactor;
519 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
520 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
522 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
524 # keep entered selling price
526 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
528 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
530 my $decimalplaces = ($dec > 2) ? $dec : 2;
532 # undo discount formatting
533 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
536 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
538 # round linetotal at least to 2 decimal places
539 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
540 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
542 $form->{"inventory_accno_$i"} *= 1;
543 $form->{"expense_accno_$i"} *= 1;
545 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
549 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
551 if ($form->{taxincluded}) {
552 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
553 $taxbase = $linetotal - $taxamount;
555 # we are not keeping a natural price, do not round
556 $form->{"sellprice_$i"} =
557 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
559 $taxamount = $linetotal * $taxrate;
560 $taxbase = $linetotal;
563 if ($form->round_amount($taxrate, 7) == 0) {
564 if ($form->{taxincluded}) {
565 foreach my $item (@taxaccounts) {
566 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
567 $taxaccounts{$item} += $taxamount;
568 $taxdiff += $taxamount;
569 $taxbase{$item} += $taxbase;
571 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
573 foreach my $item (@taxaccounts) {
574 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
575 $taxbase{$item} += $taxbase;
579 foreach my $item (@taxaccounts) {
580 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
581 $taxbase{$item} += $taxbase;
585 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
587 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
589 # Get pricegroup_id and save it. Unfortunately the interface
590 # also uses ID "0" for signalling that none is selected, but "0"
591 # must not be stored in the database. Therefore we cannot simply
593 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
595 $pricegroup_id = undef if !$pricegroup_id;
597 # force new project, if not set yet
598 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
599 require SL::DB::Customer;
600 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
601 die "Can't find customer" unless $customer;
602 my $new_project = SL::DB::Project->new(
603 projectnumber => $form->{ordnumber},
604 description => $customer->name,
605 customer_id => $customer->id,
607 project_type_id => $::instance_conf->get_project_type_id,
608 project_status_id => $::instance_conf->get_project_status_id,
611 $form->{"globalproject_id"} = $new_project->id;
614 CVar->get_non_editable_ic_cvars(form => $form,
617 sub_module => 'orderitems',
618 may_converted_from => ['orderitems', 'invoice']);
622 # save detail record in orderitems table
623 if (! $form->{"orderitems_id_$i"}) {
624 $query = qq|SELECT nextval('orderitemsid')|;
625 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
627 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
628 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
631 my $orderitems_id = $form->{"orderitems_id_$i"};
632 push @processed_orderitems, $orderitems_id;
635 UPDATE orderitems SET
636 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
637 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
638 pricegroup_id = ?, subtotal = ?,
639 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
640 active_price_source = ?, active_discount_source = ?,
641 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
645 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
646 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
647 $form->{"qty_$i"}, $baseqty,
648 $fxsellprice, $form->{"discount_$i"},
649 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
650 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
651 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
652 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
653 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
654 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
655 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
656 conv_i($orderitems_id),
659 do_query($form, $dbh, $query, @values);
661 $form->{"sellprice_$i"} = $fxsellprice;
662 $form->{"discount_$i"} *= 100;
664 CVar->save_custom_variables(module => 'IC',
665 sub_module => 'orderitems',
666 trans_id => $orderitems_id,
667 configs => $ic_cvar_configs,
669 name_prefix => 'ic_',
670 name_postfix => "_$i",
673 # link previous items with orderitems
674 # assume we have a new workflow if we link from invoice or order to quotation
675 # unluckily orderitems are used for quotation and orders - therefore one more
676 # check to be sure NOT to link from order to quotation
677 foreach (qw(orderitems)) {
678 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
679 && $form->{type} !~ 'quotation') {
680 RecordLinks->create_links('dbh' => $dbh,
683 'from_ids' => $form->{"converted_from_${_}_id_$i"},
684 'to_table' => 'orderitems',
685 'to_id' => $orderitems_id,
688 delete $form->{"converted_from_${_}_id_$i"};
693 # search for orphaned ids
694 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
695 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
696 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
698 if (scalar @orphaned_ids) {
699 # clean up orderitems
700 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
701 do_query($form, $dbh, $query, @orphaned_ids);
704 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
708 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
710 $amount = $form->round_amount($netamount + $tax, 2, 1);
711 $netamount = $form->round_amount($netamount, 2);
713 if ($form->{currency} eq $form->{defaultcurrency}) {
714 $form->{exchangerate} = 1;
716 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
719 # from inputfield (exchangerate) or hidden (forex)
720 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
722 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
724 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
729 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
730 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
731 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
732 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
733 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
734 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
735 , order_probability = ?, expected_billing_date = ?
738 @values = ($form->{ordnumber} || '', $form->{quonumber},
739 $form->{cusordnumber}, conv_date($form->{transdate}),
740 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
741 $amount, $netamount, conv_date($reqdate),
742 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
743 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
744 $form->{currency}, $form->{closed} ? 't' : 'f',
745 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
746 $quotation, conv_i($form->{department_id}),
747 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
748 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
749 conv_i($form->{delivery_vendor_id}),
750 conv_i($form->{delivery_customer_id}),
751 conv_i($form->{delivery_term_id}),
752 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
753 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
754 $form->{transaction_description},
755 $form->{marge_total} * 1, $form->{marge_percent} * 1,
756 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
757 conv_i($form->{id}));
758 do_query($form, $dbh, $query, @values);
760 $form->new_lastmtime('oe');
762 $form->{ordtotal} = $amount;
764 $form->{name} = $form->{ $form->{vc} };
765 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
768 if (!$form->{shipto_id}) {
769 $form->add_shipto($dbh, $form->{id}, "OE");
772 # save printed, emailed, queued
773 $form->save_status($dbh);
775 # Link this record to the records it was created from.
776 $form->{convert_from_oe_ids} =~ s/^\s+//;
777 $form->{convert_from_oe_ids} =~ s/\s+$//;
778 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
779 delete $form->{convert_from_oe_ids};
780 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
781 RecordLinks->create_links('dbh' => $dbh,
783 'from_table' => 'oe',
784 'from_ids' => \@convert_from_oe_ids,
786 'to_id' => $form->{id},
788 $self->_close_quotations_rfqs('dbh' => $dbh,
789 'from_id' => \@convert_from_oe_ids,
790 'to_id' => $form->{id});
793 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
794 if ($form->{vc} eq 'customer') {
795 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
797 if ($form->{vc} eq 'vendor') {
798 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
802 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
803 "quonumber" : "ordnumber"};
805 Common::webdav_folder($form);
807 $self->save_periodic_invoices_config(dbh => $dbh,
808 oe_id => $form->{id},
809 config_yaml => $form->{periodic_invoices_config})
810 if ($form->{type} eq 'sales_order');
812 $main::lxdebug->leave_sub();
817 sub save_periodic_invoices_config {
818 my ($self, %params) = @_;
820 return if !$params{oe_id};
822 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
823 return if 'HASH' ne ref $config;
825 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
826 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
827 $obj->update_attributes(%{ $config });
830 sub load_periodic_invoice_config {
834 delete $form->{periodic_invoices_config};
837 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
840 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity order_value_periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
841 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
842 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
847 sub _close_quotations_rfqs {
848 $main::lxdebug->enter_sub();
853 Common::check_params(\%params, qw(from_id to_id));
855 my $myconfig = \%main::myconfig;
856 my $form = $main::form;
858 my $dbh = $params{dbh} || SL::DB->client->dbh;
860 SL::DB->client->with_transaction(sub {
862 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
863 my $sth = prepare_query($form, $dbh, $query);
865 do_statement($form, $sth, $query, conv_i($params{to_id}));
867 my ($quotation) = $sth->fetchrow_array();
875 foreach my $from_id (@{ $params{from_id} }) {
876 $from_id = conv_i($from_id);
877 do_statement($form, $sth, $query, $from_id);
878 ($quotation) = $sth->fetchrow_array();
879 push @close_ids, $from_id if ($quotation);
884 if (scalar @close_ids) {
885 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
886 do_query($form, $dbh, $query, @close_ids);
889 }) or do { die SL::DB->client->error };
891 $main::lxdebug->leave_sub();
895 $main::lxdebug->enter_sub();
897 my ($self, $myconfig, $form) = @_;
899 my $rc = SL::DB::Order->new->db->with_transaction(sub {
900 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
902 SL::DB::Order->new(id => $form->{id})->delete;
904 my $spool = $::lx_office_conf{paths}->{spool};
905 unlink map { "$spool/$_" } @spoolfiles if $spool;
908 }) or do { die SL::DB->client->error };
910 $main::lxdebug->leave_sub();
916 my ($self, $myconfig, $form) = @_;
917 $main::lxdebug->enter_sub();
919 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
921 $::lxdebug->leave_sub;
926 my ($self, $myconfig, $form) = @_;
928 # connect to database
929 my $dbh = SL::DB->client->dbh;
931 my ($query, $query_add, @values, @ids, $sth);
933 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
935 push @ids, $form->{"trans_id_$_"}
936 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
937 } (1 .. $form->{"rowcount"});
939 if ($form->{rowcount} && scalar @ids) {
940 $form->{convert_from_oe_ids} = join ' ', @ids;
943 # if called in multi id mode, and still only got one id, switch back to single id
944 if ($form->{"rowcount"} and $#ids == 0) {
945 $form->{"id"} = $ids[0];
947 delete $form->{convert_from_oe_ids};
950 # and remember for the rest of the function
951 my $is_collective_order = scalar @ids;
953 # If collective order was created from exactly 1 order, we assume the same
954 # behaviour as a "save as new" from within an order is actually desired, i.e.
955 # the original order isn't part of a workflow where we want to remember
956 # record_links, but simply a quick way of generating a new order from an old
957 # one without having to enter everything again.
958 # Setting useasnew will prevent the creation of record_links for the items
959 # when saving the new order.
960 # This form variable is probably not necessary, could just set saveasnew instead
961 $form->{useasnew} = 1 if $is_collective_order == 1;
964 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
965 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
966 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
967 $form->{transdate} = DateTime->today_local->to_kivitendo;
970 # get default accounts
971 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
972 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
973 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
974 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
975 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
976 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
977 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
980 my $ref = selectfirst_hashref_query($form, $dbh, $query);
981 map { $form->{$_} = $ref->{$_} } keys %$ref;
983 $form->{currency} = $form->get_default_currency($myconfig);
985 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
986 # we come from invoices, feel free.
987 $form->{reqdate} = $form->{deliverydate}
988 if ( $form->{deliverydate}
989 and $form->{callback} =~ /action=ar_transactions/);
991 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
993 if ($form->{id} or @ids) {
995 # retrieve order for single id
996 # NOTE: this query is intended to fetch all information only ONCE.
997 # so if any of these infos is important (or even different) for any item,
998 # it will be killed out and then has to be fetched from the item scope query further down
1000 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1001 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1002 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1003 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1004 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1006 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1007 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1008 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1009 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1011 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1012 LEFT JOIN employee e ON (o.employee_id = e.id)
1013 LEFT JOIN department d ON (o.department_id = d.id) | .
1016 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1018 @values = $form->{id} ? ($form->{id}) : @ids;
1019 $sth = prepare_execute_query($form, $dbh, $query, @values);
1021 $ref = $sth->fetchrow_hashref("NAME_lc");
1024 map { $form->{$_} = $ref->{$_} } keys %$ref;
1026 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1028 # set all entries for multiple ids blank that yield different information
1029 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1030 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1033 $form->{mtime} ||= $form->{itime};
1034 $form->{lastmtime} = $form->{mtime};
1036 # if not given, fill transdate with current_date
1037 $form->{transdate} = $form->current_date($myconfig)
1038 unless $form->{transdate};
1042 if ($form->{delivery_customer_id}) {
1043 $query = qq|SELECT name FROM customer WHERE id = ?|;
1044 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1047 if ($form->{delivery_vendor_id}) {
1048 $query = qq|SELECT name FROM customer WHERE id = ?|;
1049 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1052 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1054 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1055 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1057 $ref = $sth->fetchrow_hashref("NAME_lc");
1058 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1061 if ($ref->{shipto_id}) {
1062 my $cvars = CVar->get_custom_variables(
1065 trans_id => $ref->{shipto_id},
1067 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1070 # get printed, emailed and queued
1071 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1072 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1074 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1075 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1076 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1077 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1080 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1083 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1085 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1087 # retrieve individual items
1088 # this query looks up all information about the items
1089 # stuff different from the whole will not be overwritten, but saved with a suffix.
1091 qq|SELECT o.id AS orderitems_id,
1092 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1093 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1094 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1095 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1096 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1097 p.classification_id,
1098 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1099 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1100 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1101 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1102 pr.projectnumber, p.formel,
1103 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1105 JOIN parts p ON (o.parts_id = p.id)
1106 JOIN oe ON (o.trans_id = oe.id)
1107 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1108 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)
1109 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)
1110 LEFT JOIN project pr ON (o.project_id = pr.id)
1111 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1113 ? qq|WHERE o.trans_id = ?|
1114 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1115 qq|ORDER BY o.trans_id, o.position|;
1117 @ids = $form->{id} ? ($form->{id}) : @ids;
1118 $sth = prepare_execute_query($form, $dbh, $query, @values);
1120 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1121 # Retrieve custom variables.
1122 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1124 sub_module => 'orderitems',
1125 trans_id => $ref->{orderitems_id},
1127 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1130 if (!$ref->{"part_type"} eq 'part') {
1131 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1133 # delete($ref->{"part_inventory_accno_id"});
1135 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1136 # unless already present there
1137 # remove _oe entries afterwards
1138 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1139 qw|ordnumber transdate cusordnumber|
1141 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1145 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1147 qq|SELECT accno AS inventory_accno, | .
1148 qq| new_chart_id AS inventory_new_chart, | .
1149 qq| date($transdate) - valid_from AS inventory_valid | .
1150 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1151 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1152 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1155 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1157 qq|SELECT accno AS income_accno, | .
1158 qq| new_chart_id AS income_new_chart, | .
1159 qq| date($transdate) - valid_from AS income_valid | .
1160 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1161 ($ref->{income_accno}, $ref->{income_new_chart},
1162 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1165 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1167 qq|SELECT accno AS expense_accno, | .
1168 qq| new_chart_id AS expense_new_chart, | .
1169 qq| date($transdate) - valid_from AS expense_valid | .
1170 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1171 ($ref->{expense_accno}, $ref->{expense_new_chart},
1172 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1175 # delete orderitems_id in collective orders, so that they get cloned no matter what
1176 # is this correct? or is the following meant?
1177 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1178 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1180 # get tax rates and description
1181 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1183 qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
1185 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1186 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1187 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1188 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1189 qq|ORDER BY c.accno|;
1190 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1191 $ref->{taxaccounts} = "";
1193 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1194 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1198 $ref->{taxaccounts} .= "$ptr->{accno} ";
1199 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1200 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1201 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1202 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1203 $form->{taxaccounts} .= "$ptr->{accno} ";
1208 chop $ref->{taxaccounts};
1210 push @{ $form->{form_details} }, $ref;
1217 # get last name used
1218 $form->lastname_used($dbh, $myconfig, $form->{vc})
1219 unless $form->{"$form->{vc}_id"};
1223 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1225 Common::webdav_folder($form);
1227 $self->load_periodic_invoice_config($form);
1232 sub retrieve_simple {
1233 $main::lxdebug->enter_sub();
1238 Common::check_params(\%params, qw(id));
1240 my $myconfig = \%main::myconfig;
1241 my $form = $main::form;
1243 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1245 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1246 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1248 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1249 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1251 $main::lxdebug->leave_sub();
1257 $main::lxdebug->enter_sub();
1259 my ($self, $myconfig, $form) = @_;
1261 # connect to database
1262 my $dbh = SL::DB->client->dbh;
1268 my $nodiscount_subtotal = 0;
1269 my $discount_subtotal = 0;
1272 my @partsgroup = ();
1275 my $subtotal_header = 0;
1276 my $subposition = 0;
1284 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1286 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1289 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1290 $price_factors{$pfac->{id}} = $pfac;
1291 $pfac->{factor} *= 1;
1292 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1295 # sort items by partsgroup
1296 for $i (1 .. $form->{rowcount}) {
1298 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1299 $partsgroup = $form->{"partsgroup_$i"};
1301 push @partsgroup, [$i, $partsgroup];
1302 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1308 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1309 %projects_by_id = map { $_->id => $_ } @$projects;
1312 if ($projects_by_id{$form->{"globalproject_id"}}) {
1313 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1314 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1316 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1317 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1321 $form->{discount} = [];
1323 # get some values of parts from db on store them in extra array,
1324 # so that they can be sorted in later
1325 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1326 my @prepared_arrays = keys %prepared_template_arrays;
1327 my @separate_totals = qw(non_separate_subtotal);
1329 $form->{TEMPLATE_ARRAYS} = { };
1331 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1332 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1335 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1336 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1337 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1338 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1339 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1341 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1342 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1344 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1346 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1348 my $totalweight = 0;
1350 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1353 if ($item->[1] ne $sameitem) {
1354 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1355 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1356 $sameitem = $item->[1];
1358 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1361 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1363 if ($form->{"id_$i"} != 0) {
1365 # add number, description and qty to $form->{number}, ....
1367 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1368 $subtotal_header = $i;
1369 $position = int($position);
1372 } elsif ($subtotal_header) {
1374 $position = int($position);
1375 $position = $position.".".$subposition;
1377 $position = int($position);
1381 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1383 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1385 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1386 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1387 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1388 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1389 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1390 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1391 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1392 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1393 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1394 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1395 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1396 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1397 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1398 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1399 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1400 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1401 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1402 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1403 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1404 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1405 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1407 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1408 my ($dec) = ($sellprice =~ /\.(\d+)/);
1409 my $decimalplaces = max 2, length($dec);
1411 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1413 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1414 my $linetotal = $form->round_amount($linetotal_exact, 2);
1416 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1417 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1419 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1421 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1423 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1425 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1426 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1428 $linetotal = ($linetotal != 0) ? $linetotal : '';
1430 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1431 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1432 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1434 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1435 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1436 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1437 push @separate_totals , "separate_${pabbr}_subtotal";
1438 $form->{"separate_${pabbr}_subtotal"} = 0;
1440 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1442 $form->{non_separate_subtotal} += $linetotal;
1445 $form->{ordtotal} += $linetotal;
1446 $form->{nodiscount_total} += $nodiscount_linetotal;
1447 $form->{discount_total} += $discount;
1449 if ($subtotal_header) {
1450 $discount_subtotal += $linetotal;
1451 $nodiscount_subtotal += $nodiscount_linetotal;
1454 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1455 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1456 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1457 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1458 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1460 $discount_subtotal = 0;
1461 $nodiscount_subtotal = 0;
1462 $subtotal_header = 0;
1465 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1468 if (!$form->{"discount_$i"}) {
1469 $nodiscount += $linetotal;
1472 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1474 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1475 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1476 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1477 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1478 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1479 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1481 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1482 $totalweight += $lineweight;
1483 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1484 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1485 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1486 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1488 my ($taxamount, $taxbase);
1491 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1493 if ($form->{taxincluded}) {
1496 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1497 $taxbase = $linetotal / (1 + $taxrate);
1499 $taxamount = $linetotal * $taxrate;
1500 $taxbase = $linetotal;
1503 if ($taxamount != 0) {
1504 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1505 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1506 $taxbase{$accno} += $taxbase;
1510 $tax_rate = $taxrate * 100;
1511 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1513 if ($form->{"part_type_$i"} eq 'assembly') {
1516 # get parts and push them onto the stack
1518 if ($form->{groupitems}) {
1519 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1521 $sortorder = qq|ORDER BY a.oid|;
1524 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1525 qq|pg.partsgroup | .
1526 qq|FROM assembly a | .
1527 qq| JOIN parts p ON (a.parts_id = p.id) | .
1528 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1529 qq| WHERE a.bom = '1' | .
1530 qq| AND a.id = ? | . $sortorder;
1531 @values = ($form->{"id_$i"});
1532 $sth = $dbh->prepare($query);
1533 $sth->execute(@values) || $form->dberror($query);
1535 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1536 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1537 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1538 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1539 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1540 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1543 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1544 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1545 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1550 CVar->get_non_editable_ic_cvars(form => $form,
1553 sub_module => 'orderitems',
1554 may_converted_from => ['orderitems', 'invoice']);
1556 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1557 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1558 for @{ $ic_cvar_configs };
1560 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1564 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1565 $form->{totalweight_nofmt} = $totalweight;
1566 my $defaults = AM->get_defaults();
1567 $form->{weightunit} = $defaults->{weightunit};
1570 foreach $item (sort keys %taxaccounts) {
1571 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1573 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1574 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1575 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1576 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1577 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1578 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1579 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1581 my $tax_objs = SL::DB::Manager::Tax->get_objects_from_sql(
1582 sql => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
1583 args => [ $form->{"${item}_taxnumber"} ]
1587 $tax_obj = $tax_objs->[0];
1589 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1590 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1593 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1594 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1595 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1596 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1598 if($form->{taxincluded}) {
1599 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1600 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1602 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1603 $form->{subtotal_nofmt} = $form->{ordtotal};
1606 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1607 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1608 $form->{rounding} = $form->round_amount(
1609 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1614 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1615 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1617 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1619 $form->{username} = $myconfig->{name};
1621 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1622 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1623 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1625 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1626 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1628 $main::lxdebug->leave_sub();
1637 OE.pm - Order entry module
1641 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>.
1647 =item retrieve_simple PARAMS
1649 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1651 my $order = retrieve_simple(id => 2);