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);
45 use SL::DB::PeriodicInvoicesConfig;
47 use SL::DB::ProjectType;
51 use SL::HTML::Restrict;
54 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| ex.$rate AS exchangerate, | .
120 qq| pt.description AS payment_terms, | .
121 qq| pr.projectnumber AS globalprojectnumber, | .
122 qq| e.name AS employee, s.name AS salesman, | .
123 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
124 qq| tz.description AS taxzone | .
125 $periodic_invoices_columns .
126 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
128 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
129 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
130 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
131 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
132 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
133 qq| AND ex.transdate = o.transdate) | .
134 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
135 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
136 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
137 qq|$periodic_invoices_joins | .
138 qq|WHERE (o.quotation = ?) |;
139 push(@values, $quotation);
141 if ($form->{department_id}) {
142 $query .= qq| AND o.department_id = ?|;
143 push(@values, $form->{department_id});
146 if ($form->{"project_id"}) {
148 qq|AND ((globalproject_id = ?) OR EXISTS | .
149 qq| (SELECT * FROM orderitems oi | .
150 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
151 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
154 if ($form->{"projectnumber"}) {
156 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
157 SELECT * FROM orderitems oi
158 LEFT JOIN project proi ON proi.id = oi.project_id
159 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
162 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
165 if ($form->{"business_id"}) {
166 $query .= " AND ct.business_id = ?";
167 push(@values, $form->{"business_id"});
170 if ($form->{"${vc}_id"}) {
171 $query .= " AND o.${vc}_id = ?";
172 push(@values, $form->{"${vc}_id"});
174 } elsif ($form->{$vc}) {
175 $query .= " AND ct.name ILIKE ?";
176 push(@values, like($form->{$vc}));
179 if ($form->{"cp_name"}) {
180 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
181 push(@values, (like($form->{"cp_name"}))x2);
184 if (!$main::auth->assert('sales_all_edit', 1)) {
185 $query .= " AND o.employee_id = (select id from employee where login= ?)";
186 push @values, $::myconfig{login};
188 if ($form->{employee_id}) {
189 $query .= " AND o.employee_id = ?";
190 push @values, conv_i($form->{employee_id});
193 if ($form->{salesman_id}) {
194 $query .= " AND o.salesman_id = ?";
195 push @values, conv_i($form->{salesman_id});
198 if (!$form->{open} && !$form->{closed}) {
199 $query .= " AND o.id = 0";
200 } elsif (!($form->{open} && $form->{closed})) {
201 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
204 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
205 ($form->{"notdelivered"} ne $form->{"delivered"})) {
206 $query .= $form->{"delivered"} ?
207 " AND o.delivered " : " AND NOT o.delivered";
210 if ($form->{$ordnumber}) {
211 $query .= qq| AND o.$ordnumber ILIKE ?|;
212 push(@values, like($form->{$ordnumber}));
215 if ($form->{cusordnumber}) {
216 $query .= qq| AND o.cusordnumber ILIKE ?|;
217 push(@values, like($form->{cusordnumber}));
220 if($form->{transdatefrom}) {
221 $query .= qq| AND o.transdate >= ?|;
222 push(@values, conv_date($form->{transdatefrom}));
225 if($form->{transdateto}) {
226 $query .= qq| AND o.transdate <= ?|;
227 push(@values, conv_date($form->{transdateto}));
230 if($form->{reqdatefrom}) {
231 $query .= qq| AND o.reqdate >= ?|;
232 push(@values, conv_date($form->{reqdatefrom}));
235 if($form->{reqdateto}) {
236 $query .= qq| AND o.reqdate <= ?|;
237 push(@values, conv_date($form->{reqdateto}));
240 if($form->{insertdatefrom}) {
241 $query .= qq| AND o.itime::DATE >= ?|;
242 push(@values, conv_date($form->{insertdatefrom}));
245 if($form->{insertdateto}) {
246 $query .= qq| AND o.itime::DATE <= ?|;
247 push(@values, conv_date($form->{insertdateto}));
250 if ($form->{shippingpoint}) {
251 $query .= qq| AND o.shippingpoint ILIKE ?|;
252 push(@values, like($form->{shippingpoint}));
255 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
256 $query .= qq| AND tz.id = ?|;
257 push(@values, $form->{taxzone_id});
260 if ($form->{transaction_description}) {
261 $query .= qq| AND o.transaction_description ILIKE ?|;
262 push(@values, like($form->{transaction_description}));
265 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
266 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
267 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
270 if ($form->{reqdate_unset_or_old}) {
271 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
274 if (($form->{order_probability_value} || '') ne '') {
275 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
276 $query .= qq| AND (o.order_probability ${op} ?)|;
277 push @values, trim($form->{order_probability_value});
280 if ($form->{expected_billing_date_from}) {
281 $query .= qq| AND (o.expected_billing_date >= ?)|;
282 push @values, conv_date($form->{expected_billing_date_from});
285 if ($form->{expected_billing_date_to}) {
286 $query .= qq| AND (o.expected_billing_date <= ?)|;
287 push @values, conv_date($form->{expected_billing_date_to});
290 if ($form->{parts_partnumber}) {
293 SELECT orderitems.trans_id
295 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
296 WHERE (orderitems.trans_id = o.id)
297 AND (parts.partnumber ILIKE ?)
301 push @values, like($form->{parts_partnumber});
304 if ($form->{parts_description}) {
307 SELECT orderitems.trans_id
309 WHERE (orderitems.trans_id = o.id)
310 AND (orderitems.description ILIKE ?)
314 push @values, like($form->{parts_description});
318 my @tokens = parse_line('\s+', 0, $form->{all});
319 # ordnumber quonumber customer.name vendor.name transaction_description
321 o.ordnumber ILIKE ? OR
322 o.quonumber ILIKE ? OR
324 o.transaction_description ILIKE ?
326 push @values, (like($_))x4 for @tokens;
329 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
330 'trans_id_field' => 'ct.id',
334 $query .= qq| AND ($cvar_where)|;
335 push @values, @cvar_values;
338 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
339 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
340 my %allowed_sort_columns = (
341 "transdate" => "o.transdate",
342 "reqdate" => "o.reqdate",
344 "ordnumber" => "o.ordnumber",
345 "cusordnumber" => "o.cusordnumber",
346 "quonumber" => "o.quonumber",
348 "employee" => "e.name",
349 "salesman" => "s.name",
350 "shipvia" => "o.shipvia",
351 "transaction_description" => "o.transaction_description",
352 "shippingpoint" => "o.shippingpoint",
353 "insertdate" => "o.itime",
354 "taxzone" => "tz.description",
355 "payment_terms" => "pt.description",
357 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
358 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
360 $query .= qq| ORDER by | . $sortorder;
362 my $sth = $dbh->prepare($query);
363 $sth->execute(@values) ||
364 $form->dberror($query . " (" . join(", ", @values) . ")");
368 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
369 $ref->{billed_amount} = $billed_amount{$ref->{id}};
370 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
371 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
372 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
373 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
374 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
375 $id{ $ref->{id} } = $ref->{id};
380 $main::lxdebug->leave_sub();
383 sub transactions_for_todo_list {
384 $main::lxdebug->enter_sub();
389 my $myconfig = \%main::myconfig;
390 my $form = $main::form;
392 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
394 my $query = qq|SELECT id FROM employee WHERE login = ?|;
395 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
398 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
399 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
404 LEFT JOIN customer c ON (oe.customer_id = c.id)
405 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
406 LEFT JOIN employee e ON (oe.employee_id = e.id)
407 WHERE (COALESCE(quotation, FALSE) = TRUE)
408 AND (COALESCE(closed, FALSE) = FALSE)
409 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
410 AND NOT (oe.reqdate ISNULL)
411 AND (oe.reqdate < current_date)
414 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
416 $main::lxdebug->leave_sub();
422 my ($self, $myconfig, $form) = @_;
423 $main::lxdebug->enter_sub();
425 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
427 $::lxdebug->leave_sub;
433 $main::lxdebug->enter_sub();
435 my ($self, $myconfig, $form) = @_;
437 my $dbh = SL::DB->client->dbh;
438 my $restricter = SL::HTML::Restrict->create;
440 my ($query, @values, $sth, $null);
441 my $exchangerate = 0;
443 my $all_units = AM->retrieve_units($myconfig, $form);
444 $form->{all_units} = $all_units;
446 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
449 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
450 unless ($form->{employee_id}) {
451 $form->get_employee($dbh);
454 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
456 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
457 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
458 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
461 $query = qq|DELETE FROM custom_variables
462 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
463 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
464 do_query($form, $dbh, $query, $form->{id});
466 $query = qq|DELETE FROM shipto | .
467 qq|WHERE trans_id = ? AND module = 'OE'|;
468 do_query($form, $dbh, $query, $form->{id});
472 $query = qq|SELECT nextval('id')|;
473 ($form->{id}) = selectrow_query($form, $dbh, $query);
475 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
476 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
493 my @processed_orderitems;
495 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
496 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
499 for my $i (1 .. $form->{rowcount}) {
501 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
503 if ($form->{"id_$i"}) {
506 $query = qq|SELECT unit FROM parts WHERE id = ?|;
507 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
510 if (defined($all_units->{$item_unit}->{factor}) &&
511 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
512 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
514 my $baseqty = $form->{"qty_$i"} * $basefactor;
516 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
517 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
519 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
521 # keep entered selling price
523 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
525 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
527 my $decimalplaces = ($dec > 2) ? $dec : 2;
529 # undo discount formatting
530 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
533 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
535 # round linetotal at least to 2 decimal places
536 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
537 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
539 $form->{"inventory_accno_$i"} *= 1;
540 $form->{"expense_accno_$i"} *= 1;
542 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
546 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
548 if ($form->{taxincluded}) {
549 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
550 $taxbase = $linetotal - $taxamount;
552 # we are not keeping a natural price, do not round
553 $form->{"sellprice_$i"} =
554 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
556 $taxamount = $linetotal * $taxrate;
557 $taxbase = $linetotal;
560 if ($form->round_amount($taxrate, 7) == 0) {
561 if ($form->{taxincluded}) {
562 foreach my $item (@taxaccounts) {
563 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
564 $taxaccounts{$item} += $taxamount;
565 $taxdiff += $taxamount;
566 $taxbase{$item} += $taxbase;
568 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
570 foreach my $item (@taxaccounts) {
571 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
572 $taxbase{$item} += $taxbase;
576 foreach my $item (@taxaccounts) {
577 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
578 $taxbase{$item} += $taxbase;
582 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
584 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
586 # Get pricegroup_id and save it. Unfortunately the interface
587 # also uses ID "0" for signalling that none is selected, but "0"
588 # must not be stored in the database. Therefore we cannot simply
590 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
592 $pricegroup_id = undef if !$pricegroup_id;
594 # force new project, if not set yet
595 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
596 require SL::DB::Customer;
597 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
598 die "Can't find customer" unless $customer;
599 my $new_project = SL::DB::Project->new(
600 projectnumber => $form->{ordnumber},
601 description => $customer->name,
602 customer_id => $customer->id,
604 project_type_id => $::instance_conf->get_project_type_id,
605 project_status_id => $::instance_conf->get_project_status_id,
608 $form->{"globalproject_id"} = $new_project->id;
611 CVar->get_non_editable_ic_cvars(form => $form,
614 sub_module => 'orderitems',
615 may_converted_from => ['orderitems', 'invoice']);
619 # save detail record in orderitems table
620 if (! $form->{"orderitems_id_$i"}) {
621 $query = qq|SELECT nextval('orderitemsid')|;
622 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
624 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
625 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
628 my $orderitems_id = $form->{"orderitems_id_$i"};
629 push @processed_orderitems, $orderitems_id;
632 UPDATE orderitems SET
633 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
634 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
635 pricegroup_id = ?, subtotal = ?,
636 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
637 active_price_source = ?, active_discount_source = ?,
638 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
642 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
643 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
644 $form->{"qty_$i"}, $baseqty,
645 $fxsellprice, $form->{"discount_$i"},
646 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
647 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
648 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
649 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
650 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
651 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
652 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
653 conv_i($orderitems_id),
656 do_query($form, $dbh, $query, @values);
658 $form->{"sellprice_$i"} = $fxsellprice;
659 $form->{"discount_$i"} *= 100;
661 CVar->save_custom_variables(module => 'IC',
662 sub_module => 'orderitems',
663 trans_id => $orderitems_id,
664 configs => $ic_cvar_configs,
666 name_prefix => 'ic_',
667 name_postfix => "_$i",
670 # link previous items with orderitems
671 # assume we have a new workflow if we link from invoice or order to quotation
672 # unluckily orderitems are used for quotation and orders - therefore one more
673 # check to be sure NOT to link from order to quotation
674 foreach (qw(orderitems)) {
675 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
676 && $form->{type} !~ 'quotation') {
677 RecordLinks->create_links('dbh' => $dbh,
680 'from_ids' => $form->{"converted_from_${_}_id_$i"},
681 'to_table' => 'orderitems',
682 'to_id' => $orderitems_id,
685 delete $form->{"converted_from_${_}_id_$i"};
690 # search for orphaned ids
691 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
692 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
693 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
695 if (scalar @orphaned_ids) {
696 # clean up orderitems
697 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
698 do_query($form, $dbh, $query, @orphaned_ids);
701 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
705 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
707 $amount = $form->round_amount($netamount + $tax, 2, 1);
708 $netamount = $form->round_amount($netamount, 2);
710 if ($form->{currency} eq $form->{defaultcurrency}) {
711 $form->{exchangerate} = 1;
713 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
716 # from inputfield (exchangerate) or hidden (forex)
717 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
719 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
721 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
726 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
727 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
728 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
729 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
730 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
731 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
732 , order_probability = ?, expected_billing_date = ?
735 @values = ($form->{ordnumber} || '', $form->{quonumber},
736 $form->{cusordnumber}, conv_date($form->{transdate}),
737 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
738 $amount, $netamount, conv_date($reqdate),
739 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
740 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
741 $form->{currency}, $form->{closed} ? 't' : 'f',
742 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
743 $quotation, conv_i($form->{department_id}),
744 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
745 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
746 conv_i($form->{delivery_vendor_id}),
747 conv_i($form->{delivery_customer_id}),
748 conv_i($form->{delivery_term_id}),
749 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
750 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
751 $form->{transaction_description},
752 $form->{marge_total} * 1, $form->{marge_percent} * 1,
753 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
754 conv_i($form->{id}));
755 do_query($form, $dbh, $query, @values);
757 $form->new_lastmtime('oe');
759 $form->{ordtotal} = $amount;
761 $form->{name} = $form->{ $form->{vc} };
762 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
765 if (!$form->{shipto_id}) {
766 $form->add_shipto($dbh, $form->{id}, "OE");
769 # save printed, emailed, queued
770 $form->save_status($dbh);
772 # Link this record to the records it was created from.
773 $form->{convert_from_oe_ids} =~ s/^\s+//;
774 $form->{convert_from_oe_ids} =~ s/\s+$//;
775 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
776 delete $form->{convert_from_oe_ids};
777 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
778 RecordLinks->create_links('dbh' => $dbh,
780 'from_table' => 'oe',
781 'from_ids' => \@convert_from_oe_ids,
783 'to_id' => $form->{id},
785 $self->_close_quotations_rfqs('dbh' => $dbh,
786 'from_id' => \@convert_from_oe_ids,
787 'to_id' => $form->{id});
790 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
791 if ($form->{vc} eq 'customer') {
792 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
794 if ($form->{vc} eq 'vendor') {
795 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
799 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
800 "quonumber" : "ordnumber"};
802 Common::webdav_folder($form);
804 $self->save_periodic_invoices_config(dbh => $dbh,
805 oe_id => $form->{id},
806 config_yaml => $form->{periodic_invoices_config})
807 if ($form->{type} eq 'sales_order');
809 $main::lxdebug->leave_sub();
814 sub save_periodic_invoices_config {
815 my ($self, %params) = @_;
817 return if !$params{oe_id};
819 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
820 return if 'HASH' ne ref $config;
822 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
823 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
824 $obj->update_attributes(%{ $config });
827 sub load_periodic_invoice_config {
831 delete $form->{periodic_invoices_config};
834 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
837 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
838 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
839 $form->{periodic_invoices_config} = YAML::Dump($config);
844 sub _close_quotations_rfqs {
845 $main::lxdebug->enter_sub();
850 Common::check_params(\%params, qw(from_id to_id));
852 my $myconfig = \%main::myconfig;
853 my $form = $main::form;
855 my $dbh = $params{dbh} || SL::DB->client->dbh;
857 SL::DB->client->with_transaction(sub {
859 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
860 my $sth = prepare_query($form, $dbh, $query);
862 do_statement($form, $sth, $query, conv_i($params{to_id}));
864 my ($quotation) = $sth->fetchrow_array();
872 foreach my $from_id (@{ $params{from_id} }) {
873 $from_id = conv_i($from_id);
874 do_statement($form, $sth, $query, $from_id);
875 ($quotation) = $sth->fetchrow_array();
876 push @close_ids, $from_id if ($quotation);
881 if (scalar @close_ids) {
882 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
883 do_query($form, $dbh, $query, @close_ids);
886 }) or do { die SL::DB->client->error };
888 $main::lxdebug->leave_sub();
892 $main::lxdebug->enter_sub();
894 my ($self, $myconfig, $form) = @_;
896 my $rc = SL::DB::Order->new->db->with_transaction(sub {
897 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
899 SL::DB::Order->new(id => $form->{id})->delete;
901 my $spool = $::lx_office_conf{paths}->{spool};
902 unlink map { "$spool/$_" } @spoolfiles if $spool;
905 }) or do { die SL::DB->client->error };
907 $main::lxdebug->leave_sub();
913 my ($self, $myconfig, $form) = @_;
914 $main::lxdebug->enter_sub();
916 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
918 $::lxdebug->leave_sub;
923 my ($self, $myconfig, $form) = @_;
925 # connect to database
926 my $dbh = SL::DB->client->dbh;
928 my ($query, $query_add, @values, @ids, $sth);
930 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
932 push @ids, $form->{"trans_id_$_"}
933 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
934 } (1 .. $form->{"rowcount"});
936 if ($form->{rowcount} && scalar @ids) {
937 $form->{convert_from_oe_ids} = join ' ', @ids;
940 # if called in multi id mode, and still only got one id, switch back to single id
941 if ($form->{"rowcount"} and $#ids == 0) {
942 $form->{"id"} = $ids[0];
944 delete $form->{convert_from_oe_ids};
947 # and remember for the rest of the function
948 my $is_collective_order = scalar @ids;
950 # If collective order was created from exactly 1 order, we assume the same
951 # behaviour as a "save as new" from within an order is actually desired, i.e.
952 # the original order isn't part of a workflow where we want to remember
953 # record_links, but simply a quick way of generating a new order from an old
954 # one without having to enter everything again.
955 # Setting useasnew will prevent the creation of record_links for the items
956 # when saving the new order.
957 # This form variable is probably not necessary, could just set saveasnew instead
958 $form->{useasnew} = 1 if $is_collective_order == 1;
961 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
962 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
963 $form->{transdate} = DateTime->today_local->to_kivitendo;
966 # get default accounts
967 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
968 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
969 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
970 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
971 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
972 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
973 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
976 my $ref = selectfirst_hashref_query($form, $dbh, $query);
977 map { $form->{$_} = $ref->{$_} } keys %$ref;
979 $form->{currency} = $form->get_default_currency($myconfig);
981 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
982 # we come from invoices, feel free.
983 $form->{reqdate} = $form->{deliverydate}
984 if ( $form->{deliverydate}
985 and $form->{callback} =~ /action=ar_transactions/);
987 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
989 if ($form->{id} or @ids) {
991 # retrieve order for single id
992 # NOTE: this query is intended to fetch all information only ONCE.
993 # so if any of these infos is important (or even different) for any item,
994 # it will be killed out and then has to be fetched from the item scope query further down
996 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
997 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
998 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
999 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1000 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1002 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1003 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1004 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1005 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1007 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1008 LEFT JOIN employee e ON (o.employee_id = e.id)
1009 LEFT JOIN department d ON (o.department_id = d.id) | .
1012 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1014 @values = $form->{id} ? ($form->{id}) : @ids;
1015 $sth = prepare_execute_query($form, $dbh, $query, @values);
1017 $ref = $sth->fetchrow_hashref("NAME_lc");
1020 map { $form->{$_} = $ref->{$_} } keys %$ref;
1022 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1024 # set all entries for multiple ids blank that yield different information
1025 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1026 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1029 $form->{mtime} ||= $form->{itime};
1030 $form->{lastmtime} = $form->{mtime};
1032 # if not given, fill transdate with current_date
1033 $form->{transdate} = $form->current_date($myconfig)
1034 unless $form->{transdate};
1038 if ($form->{delivery_customer_id}) {
1039 $query = qq|SELECT name FROM customer WHERE id = ?|;
1040 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1043 if ($form->{delivery_vendor_id}) {
1044 $query = qq|SELECT name FROM customer WHERE id = ?|;
1045 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1048 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1050 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1051 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1053 $ref = $sth->fetchrow_hashref("NAME_lc");
1054 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1057 if ($ref->{shipto_id}) {
1058 my $cvars = CVar->get_custom_variables(
1061 trans_id => $ref->{shipto_id},
1063 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1066 # get printed, emailed and queued
1067 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1068 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1070 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1071 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1072 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1073 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1076 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1079 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1081 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1083 # retrieve individual items
1084 # this query looks up all information about the items
1085 # stuff different from the whole will not be overwritten, but saved with a suffix.
1087 qq|SELECT o.id AS orderitems_id,
1088 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1089 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1090 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1091 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1092 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1093 p.classification_id,
1094 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1095 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1096 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1097 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1098 pr.projectnumber, p.formel,
1099 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1101 JOIN parts p ON (o.parts_id = p.id)
1102 JOIN oe ON (o.trans_id = oe.id)
1103 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1104 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)
1105 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)
1106 LEFT JOIN project pr ON (o.project_id = pr.id)
1107 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1109 ? qq|WHERE o.trans_id = ?|
1110 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1111 qq|ORDER BY o.trans_id, o.position|;
1113 @ids = $form->{id} ? ($form->{id}) : @ids;
1114 $sth = prepare_execute_query($form, $dbh, $query, @values);
1116 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1117 # Retrieve custom variables.
1118 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1120 sub_module => 'orderitems',
1121 trans_id => $ref->{orderitems_id},
1123 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1126 if (!$ref->{"part_type"} eq 'part') {
1127 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1129 # delete($ref->{"part_inventory_accno_id"});
1131 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1132 # unless already present there
1133 # remove _oe entries afterwards
1134 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1135 qw|ordnumber transdate cusordnumber|
1137 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1141 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1143 qq|SELECT accno AS inventory_accno, | .
1144 qq| new_chart_id AS inventory_new_chart, | .
1145 qq| date($transdate) - valid_from AS inventory_valid | .
1146 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1147 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1148 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1151 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1153 qq|SELECT accno AS income_accno, | .
1154 qq| new_chart_id AS income_new_chart, | .
1155 qq| date($transdate) - valid_from AS income_valid | .
1156 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1157 ($ref->{income_accno}, $ref->{income_new_chart},
1158 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1161 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1163 qq|SELECT accno AS expense_accno, | .
1164 qq| new_chart_id AS expense_new_chart, | .
1165 qq| date($transdate) - valid_from AS expense_valid | .
1166 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1167 ($ref->{expense_accno}, $ref->{expense_new_chart},
1168 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1171 # delete orderitems_id in collective orders, so that they get cloned no matter what
1172 # is this correct? or is the following meant?
1173 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1174 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1176 # get tax rates and description
1177 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1179 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1180 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1181 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1182 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1183 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1184 qq|ORDER BY c.accno|;
1185 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1186 $ref->{taxaccounts} = "";
1188 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1189 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1193 $ref->{taxaccounts} .= "$ptr->{accno} ";
1194 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1195 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1196 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1197 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1198 $form->{taxaccounts} .= "$ptr->{accno} ";
1203 chop $ref->{taxaccounts};
1205 push @{ $form->{form_details} }, $ref;
1212 # get last name used
1213 $form->lastname_used($dbh, $myconfig, $form->{vc})
1214 unless $form->{"$form->{vc}_id"};
1218 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1220 Common::webdav_folder($form);
1222 $self->load_periodic_invoice_config($form);
1227 sub retrieve_simple {
1228 $main::lxdebug->enter_sub();
1233 Common::check_params(\%params, qw(id));
1235 my $myconfig = \%main::myconfig;
1236 my $form = $main::form;
1238 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1240 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1241 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1243 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1244 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1246 $main::lxdebug->leave_sub();
1252 $main::lxdebug->enter_sub();
1254 my ($self, $myconfig, $form) = @_;
1256 # connect to database
1257 my $dbh = SL::DB->client->dbh;
1263 my $nodiscount_subtotal = 0;
1264 my $discount_subtotal = 0;
1267 my @partsgroup = ();
1270 my $subtotal_header = 0;
1271 my $subposition = 0;
1279 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1281 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1282 $form->{ALL_DEPARTMENTS} = SL::DB::Manager::Department->get_all;
1285 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1286 $price_factors{$pfac->{id}} = $pfac;
1287 $pfac->{factor} *= 1;
1288 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1291 # sort items by partsgroup
1292 for $i (1 .. $form->{rowcount}) {
1294 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1295 $partsgroup = $form->{"partsgroup_$i"};
1297 push @partsgroup, [$i, $partsgroup];
1298 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1304 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1305 %projects_by_id = map { $_->id => $_ } @$projects;
1308 if ($projects_by_id{$form->{"globalproject_id"}}) {
1309 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1310 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1312 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1313 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1317 $form->{discount} = [];
1319 # get some values of parts from db on store them in extra array,
1320 # so that they can be sorted in later
1321 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1322 my @prepared_arrays = keys %prepared_template_arrays;
1323 my @separate_totals = qw(non_separate_subtotal);
1325 $form->{TEMPLATE_ARRAYS} = { };
1327 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1328 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1331 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1332 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1333 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1334 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1335 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1337 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1338 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1340 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1342 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1344 my $totalweight = 0;
1346 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1349 if ($item->[1] ne $sameitem) {
1350 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1351 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1352 $sameitem = $item->[1];
1354 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1357 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1359 if ($form->{"id_$i"} != 0) {
1361 # add number, description and qty to $form->{number}, ....
1363 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1364 $subtotal_header = $i;
1365 $position = int($position);
1368 } elsif ($subtotal_header) {
1370 $position = int($position);
1371 $position = $position.".".$subposition;
1373 $position = int($position);
1377 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1379 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1381 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1382 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1383 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1384 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1385 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1386 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1387 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1388 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1389 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1390 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1391 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1392 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1393 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1394 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1395 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1396 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1397 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1398 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1399 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1400 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1401 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1403 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1404 my ($dec) = ($sellprice =~ /\.(\d+)/);
1405 my $decimalplaces = max 2, length($dec);
1407 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1409 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1410 my $linetotal = $form->round_amount($linetotal_exact, 2);
1412 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1413 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1415 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1417 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1419 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1421 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1422 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1424 $linetotal = ($linetotal != 0) ? $linetotal : '';
1426 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1427 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1428 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1430 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1431 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1432 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1433 push @separate_totals , "separate_${pabbr}_subtotal";
1434 $form->{"separate_${pabbr}_subtotal"} = 0;
1436 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1438 $form->{non_separate_subtotal} += $linetotal;
1441 $form->{ordtotal} += $linetotal;
1442 $form->{nodiscount_total} += $nodiscount_linetotal;
1443 $form->{discount_total} += $discount;
1445 if ($subtotal_header) {
1446 $discount_subtotal += $linetotal;
1447 $nodiscount_subtotal += $nodiscount_linetotal;
1450 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1451 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1452 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1453 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1454 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1456 $discount_subtotal = 0;
1457 $nodiscount_subtotal = 0;
1458 $subtotal_header = 0;
1461 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1464 if (!$form->{"discount_$i"}) {
1465 $nodiscount += $linetotal;
1468 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1470 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1471 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1472 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1473 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1474 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1475 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1477 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1478 $totalweight += $lineweight;
1479 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1480 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1481 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1482 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1484 my ($taxamount, $taxbase);
1487 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1489 if ($form->{taxincluded}) {
1492 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1493 $taxbase = $linetotal / (1 + $taxrate);
1495 $taxamount = $linetotal * $taxrate;
1496 $taxbase = $linetotal;
1499 if ($taxamount != 0) {
1500 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1501 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1502 $taxbase{$accno} += $taxbase;
1506 $tax_rate = $taxrate * 100;
1507 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1509 if ($form->{"assembly_$i"}) {
1512 # get parts and push them onto the stack
1514 if ($form->{groupitems}) {
1515 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1517 $sortorder = qq|ORDER BY a.oid|;
1520 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1521 qq|pg.partsgroup | .
1522 qq|FROM assembly a | .
1523 qq| JOIN parts p ON (a.parts_id = p.id) | .
1524 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1525 qq| WHERE a.bom = '1' | .
1526 qq| AND a.id = ? | . $sortorder;
1527 @values = ($form->{"id_$i"});
1528 $sth = $dbh->prepare($query);
1529 $sth->execute(@values) || $form->dberror($query);
1531 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1532 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1533 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1534 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1535 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1536 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1539 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1540 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1541 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1546 CVar->get_non_editable_ic_cvars(form => $form,
1549 sub_module => 'orderitems',
1550 may_converted_from => ['orderitems', 'invoice']);
1552 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1553 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1554 for @{ $ic_cvar_configs };
1556 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1560 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1561 $form->{totalweight_nofmt} = $totalweight;
1562 my $defaults = AM->get_defaults();
1563 $form->{weightunit} = $defaults->{weightunit};
1566 foreach $item (sort keys %taxaccounts) {
1567 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1569 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1570 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1571 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1572 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1573 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1574 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1575 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1577 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1578 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1579 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1582 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1583 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1584 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1585 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1587 if($form->{taxincluded}) {
1588 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1589 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1591 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1592 $form->{subtotal_nofmt} = $form->{ordtotal};
1595 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1596 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1597 $form->{rounding} = $form->round_amount(
1598 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1603 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1604 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1606 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1608 $form->{username} = $myconfig->{name};
1610 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1611 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1613 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1614 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1616 $main::lxdebug->leave_sub();
1619 sub project_description {
1620 $main::lxdebug->enter_sub();
1622 my ($self, $dbh, $id) = @_;
1624 my $query = qq|SELECT description FROM project WHERE id = ?|;
1625 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1627 $main::lxdebug->leave_sub();
1638 OE.pm - Order entry module
1642 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>.
1648 =item retrieve_simple PARAMS
1650 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1652 my $order = retrieve_simple(id => 2);