1 #====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1999-2003
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
33 #======================================================================
37 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
46 use SL::DB::ProjectType;
50 use SL::HTML::Restrict;
53 use SL::Util qw(trim);
60 $main::lxdebug->enter_sub();
62 my ($self, $myconfig, $form) = @_;
65 my $dbh = $form->get_standard_dbh;
68 my $ordnumber = 'ordnumber';
74 my ($periodic_invoices_columns, $periodic_invoices_joins);
76 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
78 if ($form->{type} =~ /_quotation$/) {
80 $ordnumber = 'quonumber';
82 } elsif ($form->{type} eq 'sales_order') {
83 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
84 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
87 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
91 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
93 SELECT from_id, ar.amount, ar.netamount FROM (
96 WHERE from_table = 'oe' AND to_table = 'ar'
98 SELECT rl1.from_id, rl2.to_id
100 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
101 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
103 LEFT JOIN ar ON ar.id = rl.to_id
105 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
106 $billed_amount{ $ref->{from_id}} += $ref->{amount};
107 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
112 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
113 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
114 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
115 qq| o.transaction_description, | .
116 qq| o.marge_total, o.marge_percent, | .
117 qq| o.itime::DATE AS insertdate, | .
118 qq| ex.$rate AS exchangerate, | .
119 qq| pt.description AS payment_terms, | .
120 qq| pr.projectnumber AS globalprojectnumber, | .
121 qq| e.name AS employee, s.name AS salesman, | .
122 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
123 qq| tz.description AS taxzone | .
124 $periodic_invoices_columns .
125 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
127 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
128 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
129 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
130 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
131 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
132 qq| AND ex.transdate = o.transdate) | .
133 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
134 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
135 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
136 qq|$periodic_invoices_joins | .
137 qq|WHERE (o.quotation = ?) |;
138 push(@values, $quotation);
140 my ($null, $split_department_id) = split /--/, $form->{department};
141 my $department_id = $form->{department_id} || $split_department_id;
142 if ($department_id) {
143 $query .= qq| AND o.department_id = ?|;
144 push(@values, $department_id);
147 if ($form->{"project_id"}) {
149 qq|AND ((globalproject_id = ?) OR EXISTS | .
150 qq| (SELECT * FROM orderitems oi | .
151 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
152 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
155 if ($form->{"projectnumber"}) {
157 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
158 SELECT * FROM orderitems oi
159 LEFT JOIN project proi ON proi.id = oi.project_id
160 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
163 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
166 if ($form->{"business_id"}) {
167 $query .= " AND ct.business_id = ?";
168 push(@values, $form->{"business_id"});
171 if ($form->{"${vc}_id"}) {
172 $query .= " AND o.${vc}_id = ?";
173 push(@values, $form->{"${vc}_id"});
175 } elsif ($form->{$vc}) {
176 $query .= " AND ct.name ILIKE ?";
177 push(@values, like($form->{$vc}));
180 if ($form->{"cp_name"}) {
181 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
182 push(@values, (like($form->{"cp_name"}))x2);
185 if (!$main::auth->assert('sales_all_edit', 1)) {
186 $query .= " AND o.employee_id = (select id from employee where login= ?)";
187 push @values, $::myconfig{login};
189 if ($form->{employee_id}) {
190 $query .= " AND o.employee_id = ?";
191 push @values, conv_i($form->{employee_id});
194 if ($form->{salesman_id}) {
195 $query .= " AND o.salesman_id = ?";
196 push @values, conv_i($form->{salesman_id});
199 if (!$form->{open} && !$form->{closed}) {
200 $query .= " AND o.id = 0";
201 } elsif (!($form->{open} && $form->{closed})) {
202 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
205 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
206 ($form->{"notdelivered"} ne $form->{"delivered"})) {
207 $query .= $form->{"delivered"} ?
208 " AND o.delivered " : " AND NOT o.delivered";
211 if ($form->{$ordnumber}) {
212 $query .= qq| AND o.$ordnumber ILIKE ?|;
213 push(@values, like($form->{$ordnumber}));
216 if ($form->{cusordnumber}) {
217 $query .= qq| AND o.cusordnumber ILIKE ?|;
218 push(@values, like($form->{cusordnumber}));
221 if($form->{transdatefrom}) {
222 $query .= qq| AND o.transdate >= ?|;
223 push(@values, conv_date($form->{transdatefrom}));
226 if($form->{transdateto}) {
227 $query .= qq| AND o.transdate <= ?|;
228 push(@values, conv_date($form->{transdateto}));
231 if($form->{reqdatefrom}) {
232 $query .= qq| AND o.reqdate >= ?|;
233 push(@values, conv_date($form->{reqdatefrom}));
236 if($form->{reqdateto}) {
237 $query .= qq| AND o.reqdate <= ?|;
238 push(@values, conv_date($form->{reqdateto}));
241 if($form->{insertdatefrom}) {
242 $query .= qq| AND o.itime::DATE >= ?|;
243 push(@values, conv_date($form->{insertdatefrom}));
246 if($form->{insertdateto}) {
247 $query .= qq| AND o.itime::DATE <= ?|;
248 push(@values, conv_date($form->{insertdateto}));
251 if ($form->{shippingpoint}) {
252 $query .= qq| AND o.shippingpoint ILIKE ?|;
253 push(@values, like($form->{shippingpoint}));
256 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
257 $query .= qq| AND tz.id = ?|;
258 push(@values, $form->{taxzone_id});
261 if ($form->{transaction_description}) {
262 $query .= qq| AND o.transaction_description ILIKE ?|;
263 push(@values, like($form->{transaction_description}));
266 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
267 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
268 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
271 if ($form->{reqdate_unset_or_old}) {
272 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
275 if (($form->{order_probability_value} || '') ne '') {
276 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
277 $query .= qq| AND (o.order_probability ${op} ?)|;
278 push @values, trim($form->{order_probability_value});
281 if ($form->{expected_billing_date_from}) {
282 $query .= qq| AND (o.expected_billing_date >= ?)|;
283 push @values, conv_date($form->{expected_billing_date_from});
286 if ($form->{expected_billing_date_to}) {
287 $query .= qq| AND (o.expected_billing_date <= ?)|;
288 push @values, conv_date($form->{expected_billing_date_to});
291 if ($form->{parts_partnumber}) {
294 SELECT orderitems.trans_id
296 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
297 WHERE (orderitems.trans_id = o.id)
298 AND (parts.partnumber ILIKE ?)
302 push @values, like($form->{parts_partnumber});
305 if ($form->{parts_description}) {
308 SELECT orderitems.trans_id
310 WHERE (orderitems.trans_id = o.id)
311 AND (orderitems.description ILIKE ?)
315 push @values, like($form->{parts_description});
319 my @tokens = parse_line('\s+', 0, $form->{all});
320 # ordnumber quonumber customer.name vendor.name transaction_description
322 o.ordnumber ILIKE ? OR
323 o.quonumber ILIKE ? OR
325 o.transaction_description ILIKE ?
327 push @values, (like($_))x4 for @tokens;
330 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
331 'trans_id_field' => 'ct.id',
335 $query .= qq| AND ($cvar_where)|;
336 push @values, @cvar_values;
339 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
340 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
341 my %allowed_sort_columns = (
342 "transdate" => "o.transdate",
343 "reqdate" => "o.reqdate",
345 "ordnumber" => "o.ordnumber",
346 "cusordnumber" => "o.cusordnumber",
347 "quonumber" => "o.quonumber",
349 "employee" => "e.name",
350 "salesman" => "s.name",
351 "shipvia" => "o.shipvia",
352 "transaction_description" => "o.transaction_description",
353 "shippingpoint" => "o.shippingpoint",
354 "insertdate" => "o.itime",
355 "taxzone" => "tz.description",
356 "payment_terms" => "pt.description",
358 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
359 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
361 $query .= qq| ORDER by | . $sortorder;
363 my $sth = $dbh->prepare($query);
364 $sth->execute(@values) ||
365 $form->dberror($query . " (" . join(", ", @values) . ")");
369 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
370 $ref->{billed_amount} = $billed_amount{$ref->{id}};
371 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
372 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
373 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
374 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
375 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
376 $id{ $ref->{id} } = $ref->{id};
381 $main::lxdebug->leave_sub();
384 sub transactions_for_todo_list {
385 $main::lxdebug->enter_sub();
390 my $myconfig = \%main::myconfig;
391 my $form = $main::form;
393 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
395 my $query = qq|SELECT id FROM employee WHERE login = ?|;
396 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
399 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
400 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
405 LEFT JOIN customer c ON (oe.customer_id = c.id)
406 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
407 LEFT JOIN employee e ON (oe.employee_id = e.id)
408 WHERE (COALESCE(quotation, FALSE) = TRUE)
409 AND (COALESCE(closed, FALSE) = FALSE)
410 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
411 AND NOT (oe.reqdate ISNULL)
412 AND (oe.reqdate < current_date)
415 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
417 $main::lxdebug->leave_sub();
423 my ($self, $myconfig, $form) = @_;
424 $main::lxdebug->enter_sub();
426 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
428 $::lxdebug->leave_sub;
434 $main::lxdebug->enter_sub();
436 my ($self, $myconfig, $form) = @_;
438 my $dbh = SL::DB->client->dbh;
439 my $restricter = SL::HTML::Restrict->create;
441 my ($query, @values, $sth, $null);
442 my $exchangerate = 0;
444 my $all_units = AM->retrieve_units($myconfig, $form);
445 $form->{all_units} = $all_units;
447 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
450 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
451 unless ($form->{employee_id}) {
452 $form->get_employee($dbh);
455 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
457 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
458 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
459 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
462 $query = qq|DELETE FROM custom_variables
463 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
464 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
465 do_query($form, $dbh, $query, $form->{id});
467 $query = qq|DELETE FROM shipto | .
468 qq|WHERE trans_id = ? AND module = 'OE'|;
469 do_query($form, $dbh, $query, $form->{id});
473 $query = qq|SELECT nextval('id')|;
474 ($form->{id}) = selectrow_query($form, $dbh, $query);
476 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
477 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
494 my @processed_orderitems;
496 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
497 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
500 for my $i (1 .. $form->{rowcount}) {
502 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
504 if ($form->{"id_$i"}) {
507 $query = qq|SELECT unit FROM parts WHERE id = ?|;
508 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
511 if (defined($all_units->{$item_unit}->{factor}) &&
512 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
513 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
515 my $baseqty = $form->{"qty_$i"} * $basefactor;
517 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
518 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
520 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
522 # keep entered selling price
524 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
526 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
528 my $decimalplaces = ($dec > 2) ? $dec : 2;
530 # undo discount formatting
531 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
534 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
536 # round linetotal at least to 2 decimal places
537 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
538 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
540 $form->{"inventory_accno_$i"} *= 1;
541 $form->{"expense_accno_$i"} *= 1;
543 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
547 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
549 if ($form->{taxincluded}) {
550 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
551 $taxbase = $linetotal - $taxamount;
553 # we are not keeping a natural price, do not round
554 $form->{"sellprice_$i"} =
555 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
557 $taxamount = $linetotal * $taxrate;
558 $taxbase = $linetotal;
561 if ($form->round_amount($taxrate, 7) == 0) {
562 if ($form->{taxincluded}) {
563 foreach my $item (@taxaccounts) {
564 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
565 $taxaccounts{$item} += $taxamount;
566 $taxdiff += $taxamount;
567 $taxbase{$item} += $taxbase;
569 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
571 foreach my $item (@taxaccounts) {
572 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
573 $taxbase{$item} += $taxbase;
577 foreach my $item (@taxaccounts) {
578 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
579 $taxbase{$item} += $taxbase;
583 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
585 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
587 # Get pricegroup_id and save it. Unfortunately the interface
588 # also uses ID "0" for signalling that none is selected, but "0"
589 # must not be stored in the database. Therefore we cannot simply
591 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
593 $pricegroup_id = undef if !$pricegroup_id;
595 # force new project, if not set yet
596 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
597 require SL::DB::Customer;
598 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
599 die "Can't find customer" unless $customer;
600 my $new_project = SL::DB::Project->new(
601 projectnumber => $form->{ordnumber},
602 description => $customer->name,
603 customer_id => $customer->id,
605 project_type_id => $::instance_conf->get_project_type_id,
606 project_status_id => $::instance_conf->get_project_status_id,
609 $form->{"globalproject_id"} = $new_project->id;
612 CVar->get_non_editable_ic_cvars(form => $form,
615 sub_module => 'orderitems',
616 may_converted_from => ['orderitems', 'invoice']);
620 # save detail record in orderitems table
621 if (! $form->{"orderitems_id_$i"}) {
622 $query = qq|SELECT nextval('orderitemsid')|;
623 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
625 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
626 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
629 my $orderitems_id = $form->{"orderitems_id_$i"};
630 push @processed_orderitems, $orderitems_id;
633 UPDATE orderitems SET
634 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
635 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
636 pricegroup_id = ?, subtotal = ?,
637 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
638 active_price_source = ?, active_discount_source = ?,
639 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
643 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
644 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
645 $form->{"qty_$i"}, $baseqty,
646 $fxsellprice, $form->{"discount_$i"},
647 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
648 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
649 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
650 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
651 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
652 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
653 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
654 conv_i($orderitems_id),
657 do_query($form, $dbh, $query, @values);
659 $form->{"sellprice_$i"} = $fxsellprice;
660 $form->{"discount_$i"} *= 100;
662 CVar->save_custom_variables(module => 'IC',
663 sub_module => 'orderitems',
664 trans_id => $orderitems_id,
665 configs => $ic_cvar_configs,
667 name_prefix => 'ic_',
668 name_postfix => "_$i",
671 # link previous items with orderitems
672 # assume we have a new workflow if we link from invoice or order to quotation
673 # unluckily orderitems are used for quotation and orders - therefore one more
674 # check to be sure NOT to link from order to quotation
675 foreach (qw(orderitems)) {
676 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
677 && $form->{type} !~ 'quotation') {
678 RecordLinks->create_links('dbh' => $dbh,
681 'from_ids' => $form->{"converted_from_${_}_id_$i"},
682 'to_table' => 'orderitems',
683 'to_id' => $orderitems_id,
686 delete $form->{"converted_from_${_}_id_$i"};
691 # search for orphaned ids
692 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
693 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
694 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
696 if (scalar @orphaned_ids) {
697 # clean up orderitems
698 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
699 do_query($form, $dbh, $query, @orphaned_ids);
702 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
706 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
708 $amount = $form->round_amount($netamount + $tax, 2, 1);
709 $netamount = $form->round_amount($netamount, 2);
711 if ($form->{currency} eq $form->{defaultcurrency}) {
712 $form->{exchangerate} = 1;
714 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
717 # from inputfield (exchangerate) or hidden (forex)
718 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
720 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
722 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
724 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
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} ? 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} = 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 : 1;
965 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
966 $form->{transdate} = DateTime->today_local->to_kivitendo;
969 # get default accounts
970 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
971 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
972 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
973 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
974 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
975 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
976 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
979 my $ref = selectfirst_hashref_query($form, $dbh, $query);
980 map { $form->{$_} = $ref->{$_} } keys %$ref;
982 $form->{currency} = $form->get_default_currency($myconfig);
984 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
985 # we come from invoices, feel free.
986 $form->{reqdate} = $form->{deliverydate}
987 if ( $form->{deliverydate}
988 and $form->{callback} =~ /action=ar_transactions/);
990 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
992 if ($form->{id} or @ids) {
994 # retrieve order for single id
995 # NOTE: this query is intended to fetch all information only ONCE.
996 # so if any of these infos is important (or even different) for any item,
997 # it will be killed out and then has to be fetched from the item scope query further down
999 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1000 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1001 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1002 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1003 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1005 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1006 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1007 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1008 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1010 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1011 LEFT JOIN employee e ON (o.employee_id = e.id)
1012 LEFT JOIN department d ON (o.department_id = d.id) | .
1015 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1017 @values = $form->{id} ? ($form->{id}) : @ids;
1018 $sth = prepare_execute_query($form, $dbh, $query, @values);
1020 $ref = $sth->fetchrow_hashref("NAME_lc");
1023 map { $form->{$_} = $ref->{$_} } keys %$ref;
1025 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1027 # set all entries for multiple ids blank that yield different information
1028 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1029 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1032 $form->{mtime} ||= $form->{itime};
1033 $form->{lastmtime} = $form->{mtime};
1035 # if not given, fill transdate with current_date
1036 $form->{transdate} = $form->current_date($myconfig)
1037 unless $form->{transdate};
1041 if ($form->{delivery_customer_id}) {
1042 $query = qq|SELECT name FROM customer WHERE id = ?|;
1043 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1046 if ($form->{delivery_vendor_id}) {
1047 $query = qq|SELECT name FROM customer WHERE id = ?|;
1048 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1051 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1053 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1054 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1056 $ref = $sth->fetchrow_hashref("NAME_lc");
1058 map { $form->{$_} = $ref->{$_} } keys %$ref;
1061 if ($form->{shipto_id}) {
1062 my $cvars = CVar->get_custom_variables(
1065 trans_id => $form->{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.assembly, p.listprice, o.description, o.qty,
1097 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1098 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1099 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1100 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1101 pr.projectnumber, p.formel,
1102 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1104 JOIN parts p ON (o.parts_id = p.id)
1105 JOIN oe ON (o.trans_id = oe.id)
1106 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1107 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)
1108 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)
1109 LEFT JOIN project pr ON (o.project_id = pr.id)
1110 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1112 ? qq|WHERE o.trans_id = ?|
1113 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1114 qq|ORDER BY o.trans_id, o.position|;
1116 @ids = $form->{id} ? ($form->{id}) : @ids;
1117 $sth = prepare_execute_query($form, $dbh, $query, @values);
1119 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1120 # Retrieve custom variables.
1121 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1123 sub_module => 'orderitems',
1124 trans_id => $ref->{orderitems_id},
1126 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1129 if (!$ref->{"part_inventory_accno_id"}) {
1130 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1132 delete($ref->{"part_inventory_accno_id"});
1134 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1135 # unless already present there
1136 # remove _oe entries afterwards
1137 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1138 qw|ordnumber transdate cusordnumber|
1140 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1144 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1146 qq|SELECT accno AS inventory_accno, | .
1147 qq| new_chart_id AS inventory_new_chart, | .
1148 qq| date($transdate) - valid_from AS inventory_valid | .
1149 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1150 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1151 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1154 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1156 qq|SELECT accno AS income_accno, | .
1157 qq| new_chart_id AS income_new_chart, | .
1158 qq| date($transdate) - valid_from AS income_valid | .
1159 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1160 ($ref->{income_accno}, $ref->{income_new_chart},
1161 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1164 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1166 qq|SELECT accno AS expense_accno, | .
1167 qq| new_chart_id AS expense_new_chart, | .
1168 qq| date($transdate) - valid_from AS expense_valid | .
1169 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1170 ($ref->{expense_accno}, $ref->{expense_new_chart},
1171 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1174 # delete orderitems_id in collective orders, so that they get cloned no matter what
1175 # is this correct? or is the following meant?
1176 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1177 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1179 # get tax rates and description
1180 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1182 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1183 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1184 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1185 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1186 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1187 qq|ORDER BY c.accno|;
1188 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1189 $ref->{taxaccounts} = "";
1191 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1192 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1196 $ref->{taxaccounts} .= "$ptr->{accno} ";
1197 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1198 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1199 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1200 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1201 $form->{taxaccounts} .= "$ptr->{accno} ";
1206 chop $ref->{taxaccounts};
1208 push @{ $form->{form_details} }, $ref;
1215 # get last name used
1216 $form->lastname_used($dbh, $myconfig, $form->{vc})
1217 unless $form->{"$form->{vc}_id"};
1221 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1223 Common::webdav_folder($form);
1225 $self->load_periodic_invoice_config($form);
1230 sub retrieve_simple {
1231 $main::lxdebug->enter_sub();
1236 Common::check_params(\%params, qw(id));
1238 my $myconfig = \%main::myconfig;
1239 my $form = $main::form;
1241 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1243 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1244 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1246 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1247 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1249 $main::lxdebug->leave_sub();
1255 $main::lxdebug->enter_sub();
1257 my ($self, $myconfig, $form) = @_;
1259 # connect to database
1260 my $dbh = SL::DB->client->dbh;
1266 my $nodiscount_subtotal = 0;
1267 my $discount_subtotal = 0;
1270 my @partsgroup = ();
1273 my $subtotal_header = 0;
1274 my $subposition = 0;
1282 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1284 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1285 'departments' => 'ALL_DEPARTMENTS');
1288 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1289 $price_factors{$pfac->{id}} = $pfac;
1290 $pfac->{factor} *= 1;
1291 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1295 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1296 next unless $dept->{id} eq $form->{department_id};
1297 $form->{department} = $dept->{description};
1301 # sort items by partsgroup
1302 for $i (1 .. $form->{rowcount}) {
1304 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1305 $partsgroup = $form->{"partsgroup_$i"};
1307 push @partsgroup, [$i, $partsgroup];
1308 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1314 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1315 %projects_by_id = map { $_->id => $_ } @$projects;
1318 if ($projects_by_id{$form->{"globalproject_id"}}) {
1319 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1320 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1322 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1323 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1327 $form->{discount} = [];
1329 # get some values of parts from db on store them in extra array,
1330 # so that they can be sorted in later
1331 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1332 my @prepared_arrays = keys %prepared_template_arrays;
1334 $form->{TEMPLATE_ARRAYS} = { };
1336 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1337 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1340 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1341 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1342 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1343 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1344 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1346 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1347 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1349 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1351 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1353 my $totalweight = 0;
1355 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1358 if ($item->[1] ne $sameitem) {
1359 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1360 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1361 $sameitem = $item->[1];
1363 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1366 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1368 if ($form->{"id_$i"} != 0) {
1370 # add number, description and qty to $form->{number}, ....
1372 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1373 $subtotal_header = $i;
1374 $position = int($position);
1377 } elsif ($subtotal_header) {
1379 $position = int($position);
1380 $position = $position.".".$subposition;
1382 $position = int($position);
1386 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1388 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1390 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1391 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1392 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1393 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1394 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1395 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1396 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1397 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1398 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1399 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1400 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1401 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1402 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1403 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1404 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1405 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1406 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1407 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1408 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1409 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1410 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1412 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1413 my ($dec) = ($sellprice =~ /\.(\d+)/);
1414 my $decimalplaces = max 2, length($dec);
1416 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1418 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1419 my $linetotal = $form->round_amount($linetotal_exact, 2);
1421 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1422 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1424 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1426 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1428 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1430 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1431 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1433 $linetotal = ($linetotal != 0) ? $linetotal : '';
1435 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1436 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1437 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1439 $form->{ordtotal} += $linetotal;
1440 $form->{nodiscount_total} += $nodiscount_linetotal;
1441 $form->{discount_total} += $discount;
1443 if ($subtotal_header) {
1444 $discount_subtotal += $linetotal;
1445 $nodiscount_subtotal += $nodiscount_linetotal;
1448 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1449 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1450 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1451 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1452 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1454 $discount_subtotal = 0;
1455 $nodiscount_subtotal = 0;
1456 $subtotal_header = 0;
1459 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1462 if (!$form->{"discount_$i"}) {
1463 $nodiscount += $linetotal;
1466 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1468 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1469 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1470 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1471 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1472 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1473 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1475 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1476 $totalweight += $lineweight;
1477 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1478 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1479 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1480 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1482 my ($taxamount, $taxbase);
1485 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1487 if ($form->{taxincluded}) {
1490 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1491 $taxbase = $linetotal / (1 + $taxrate);
1493 $taxamount = $linetotal * $taxrate;
1494 $taxbase = $linetotal;
1497 if ($taxamount != 0) {
1498 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1499 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1500 $taxbase{$accno} += $taxbase;
1504 $tax_rate = $taxrate * 100;
1505 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1507 if ($form->{"assembly_$i"}) {
1510 # get parts and push them onto the stack
1512 if ($form->{groupitems}) {
1513 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1515 $sortorder = qq|ORDER BY a.oid|;
1518 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1519 qq|pg.partsgroup | .
1520 qq|FROM assembly a | .
1521 qq| JOIN parts p ON (a.parts_id = p.id) | .
1522 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1523 qq| WHERE a.bom = '1' | .
1524 qq| AND a.id = ? | . $sortorder;
1525 @values = ($form->{"id_$i"});
1526 $sth = $dbh->prepare($query);
1527 $sth->execute(@values) || $form->dberror($query);
1529 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1530 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1531 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1532 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1533 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1534 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1537 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1538 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1539 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1544 CVar->get_non_editable_ic_cvars(form => $form,
1547 sub_module => 'orderitems',
1548 may_converted_from => ['orderitems', 'invoice']);
1550 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1551 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1552 for @{ $ic_cvar_configs };
1554 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1558 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1559 $form->{totalweight_nofmt} = $totalweight;
1560 my $defaults = AM->get_defaults();
1561 $form->{weightunit} = $defaults->{weightunit};
1564 foreach $item (sort keys %taxaccounts) {
1565 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1567 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1568 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1569 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1570 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1571 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1572 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1573 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1575 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1576 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1577 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1580 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1581 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1582 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1583 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1585 if($form->{taxincluded}) {
1586 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1587 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1589 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1590 $form->{subtotal_nofmt} = $form->{ordtotal};
1593 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1594 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1595 $form->{rounding} = $form->round_amount(
1596 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1601 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1602 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1604 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1606 $form->{username} = $myconfig->{name};
1608 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1609 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1611 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1613 $main::lxdebug->leave_sub();
1616 sub project_description {
1617 $main::lxdebug->enter_sub();
1619 my ($self, $dbh, $id) = @_;
1621 my $query = qq|SELECT description FROM project WHERE id = ?|;
1622 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1624 $main::lxdebug->leave_sub();
1635 OE.pm - Order entry module
1639 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>.
1645 =item retrieve_simple PARAMS
1647 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1649 my $order = retrieve_simple(id => 2);