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);
59 $main::lxdebug->enter_sub();
61 my ($self, $myconfig, $form) = @_;
64 my $dbh = $form->get_standard_dbh;
67 my $ordnumber = 'ordnumber';
73 my ($periodic_invoices_columns, $periodic_invoices_joins);
75 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
77 if ($form->{type} =~ /_quotation$/) {
79 $ordnumber = 'quonumber';
81 } elsif ($form->{type} eq 'sales_order') {
82 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
83 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
86 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
90 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
92 SELECT from_id, ar.amount, ar.netamount FROM (
95 WHERE from_table = 'oe' AND to_table = 'ar'
97 SELECT rl1.from_id, rl2.to_id
99 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
100 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
102 LEFT JOIN ar ON ar.id = rl.to_id
104 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
105 $billed_amount{ $ref->{from_id}} += $ref->{amount};
106 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
111 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
112 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
113 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
114 qq| o.transaction_description, | .
115 qq| o.marge_total, o.marge_percent, | .
116 qq| o.itime::DATE AS insertdate, | .
117 qq| ex.$rate AS exchangerate, | .
118 qq| pt.description AS payment_terms, | .
119 qq| pr.projectnumber AS globalprojectnumber, | .
120 qq| e.name AS employee, s.name AS salesman, | .
121 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
122 qq| tz.description AS taxzone | .
123 $periodic_invoices_columns .
124 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
126 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
127 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
128 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
129 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
130 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
131 qq| AND ex.transdate = o.transdate) | .
132 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
133 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
134 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
135 qq|$periodic_invoices_joins | .
136 qq|WHERE (o.quotation = ?) |;
137 push(@values, $quotation);
139 my ($null, $split_department_id) = split /--/, $form->{department};
140 my $department_id = $form->{department_id} || $split_department_id;
141 if ($department_id) {
142 $query .= qq| AND o.department_id = ?|;
143 push(@values, $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 $main::lxdebug->enter_sub();
424 my ($self, $myconfig, $form) = @_;
426 # connect to database, turn off autocommit
427 my $dbh = $form->get_standard_dbh;
428 my $restricter = SL::HTML::Restrict->create;
430 my ($query, @values, $sth, $null);
431 my $exchangerate = 0;
433 my $all_units = AM->retrieve_units($myconfig, $form);
434 $form->{all_units} = $all_units;
436 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
439 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
440 unless ($form->{employee_id}) {
441 $form->get_employee($dbh);
444 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
446 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
447 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
448 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
451 $query = qq|DELETE FROM custom_variables
452 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
453 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
454 do_query($form, $dbh, $query, $form->{id});
456 $query = qq|DELETE FROM shipto | .
457 qq|WHERE trans_id = ? AND module = 'OE'|;
458 do_query($form, $dbh, $query, $form->{id});
462 $query = qq|SELECT nextval('id')|;
463 ($form->{id}) = selectrow_query($form, $dbh, $query);
465 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
466 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
483 my @processed_orderitems;
485 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
486 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
489 for my $i (1 .. $form->{rowcount}) {
491 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
493 if ($form->{"id_$i"}) {
496 $query = qq|SELECT unit FROM parts WHERE id = ?|;
497 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
500 if (defined($all_units->{$item_unit}->{factor}) &&
501 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
502 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
504 my $baseqty = $form->{"qty_$i"} * $basefactor;
506 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
507 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
509 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
511 # keep entered selling price
513 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
515 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
517 my $decimalplaces = ($dec > 2) ? $dec : 2;
519 # undo discount formatting
520 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
523 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
525 # round linetotal at least to 2 decimal places
526 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
527 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
529 $form->{"inventory_accno_$i"} *= 1;
530 $form->{"expense_accno_$i"} *= 1;
532 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
536 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
538 if ($form->{taxincluded}) {
539 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
540 $taxbase = $linetotal - $taxamount;
542 # we are not keeping a natural price, do not round
543 $form->{"sellprice_$i"} =
544 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
546 $taxamount = $linetotal * $taxrate;
547 $taxbase = $linetotal;
550 if ($form->round_amount($taxrate, 7) == 0) {
551 if ($form->{taxincluded}) {
552 foreach my $item (@taxaccounts) {
553 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
554 $taxaccounts{$item} += $taxamount;
555 $taxdiff += $taxamount;
556 $taxbase{$item} += $taxbase;
558 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
560 foreach my $item (@taxaccounts) {
561 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
562 $taxbase{$item} += $taxbase;
566 foreach my $item (@taxaccounts) {
567 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
568 $taxbase{$item} += $taxbase;
572 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
574 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
576 # Get pricegroup_id and save it. Unfortunately the interface
577 # also uses ID "0" for signalling that none is selected, but "0"
578 # must not be stored in the database. Therefore we cannot simply
580 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
582 $pricegroup_id = undef if !$pricegroup_id;
584 # force new project, if not set yet
585 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
586 require SL::DB::Customer;
587 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
588 die "Can't find customer" unless $customer;
589 my $new_project = SL::DB::Project->new(
590 projectnumber => $form->{ordnumber},
591 description => $customer->name,
592 customer_id => $customer->id,
594 project_type_id => $::instance_conf->get_project_type_id,
595 project_status_id => $::instance_conf->get_project_status_id,
598 $form->{"globalproject_id"} = $new_project->id;
601 CVar->get_non_editable_ic_cvars(form => $form,
604 sub_module => 'orderitems',
605 may_converted_from => ['orderitems', 'invoice']);
609 # save detail record in orderitems table
610 if (! $form->{"orderitems_id_$i"}) {
611 $query = qq|SELECT nextval('orderitemsid')|;
612 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
614 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
615 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
618 my $orderitems_id = $form->{"orderitems_id_$i"};
619 push @processed_orderitems, $orderitems_id;
622 UPDATE orderitems SET
623 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
624 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
625 pricegroup_id = ?, subtotal = ?,
626 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
627 active_price_source = ?, active_discount_source = ?,
628 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
632 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
633 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
634 $form->{"qty_$i"}, $baseqty,
635 $fxsellprice, $form->{"discount_$i"},
636 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
637 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
638 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
639 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
640 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
641 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
642 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
643 conv_i($orderitems_id),
646 do_query($form, $dbh, $query, @values);
648 $form->{"sellprice_$i"} = $fxsellprice;
649 $form->{"discount_$i"} *= 100;
651 CVar->save_custom_variables(module => 'IC',
652 sub_module => 'orderitems',
653 trans_id => $orderitems_id,
654 configs => $ic_cvar_configs,
656 name_prefix => 'ic_',
657 name_postfix => "_$i",
660 # link previous items with orderitems
661 # assume we have a new workflow if we link from invoice or order to quotation
662 # unluckily orderitems are used for quotation and orders - therefore one more
663 # check to be sure NOT to link from order to quotation
664 foreach (qw(orderitems)) {
665 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
666 && $form->{type} !~ 'quotation') {
667 RecordLinks->create_links('dbh' => $dbh,
670 'from_ids' => $form->{"converted_from_${_}_id_$i"},
671 'to_table' => 'orderitems',
672 'to_id' => $orderitems_id,
675 delete $form->{"converted_from_${_}_id_$i"};
680 # search for orphaned ids
681 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
682 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
683 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
685 if (scalar @orphaned_ids) {
686 # clean up orderitems
687 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
688 do_query($form, $dbh, $query, @orphaned_ids);
691 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
695 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
697 $amount = $form->round_amount($netamount + $tax, 2, 1);
698 $netamount = $form->round_amount($netamount, 2);
700 if ($form->{currency} eq $form->{defaultcurrency}) {
701 $form->{exchangerate} = 1;
703 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
706 # from inputfield (exchangerate) or hidden (forex)
707 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
709 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
711 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
713 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
718 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
719 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
720 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
721 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
722 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
723 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
724 , order_probability = ?, expected_billing_date = ?
727 @values = ($form->{ordnumber} || '', $form->{quonumber},
728 $form->{cusordnumber}, conv_date($form->{transdate}),
729 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
730 $amount, $netamount, conv_date($reqdate),
731 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
732 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
733 $form->{currency}, $form->{closed} ? 't' : 'f',
734 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
735 $quotation, conv_i($form->{department_id}),
736 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
737 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
738 conv_i($form->{delivery_vendor_id}),
739 conv_i($form->{delivery_customer_id}),
740 conv_i($form->{delivery_term_id}),
741 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
742 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
743 $form->{transaction_description},
744 $form->{marge_total} * 1, $form->{marge_percent} * 1,
745 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
746 conv_i($form->{id}));
747 do_query($form, $dbh, $query, @values);
749 $form->new_lastmtime('oe');
751 $form->{ordtotal} = $amount;
753 $form->{name} = $form->{ $form->{vc} };
754 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
757 if (!$form->{shipto_id}) {
758 $form->add_shipto($dbh, $form->{id}, "OE");
761 # save printed, emailed, queued
762 $form->save_status($dbh);
764 # Link this record to the records it was created from.
765 $form->{convert_from_oe_ids} =~ s/^\s+//;
766 $form->{convert_from_oe_ids} =~ s/\s+$//;
767 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
768 delete $form->{convert_from_oe_ids};
769 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
770 RecordLinks->create_links('dbh' => $dbh,
772 'from_table' => 'oe',
773 'from_ids' => \@convert_from_oe_ids,
775 'to_id' => $form->{id},
777 $self->_close_quotations_rfqs('dbh' => $dbh,
778 'from_id' => \@convert_from_oe_ids,
779 'to_id' => $form->{id});
782 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
783 if ($form->{vc} eq 'customer') {
784 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
786 if ($form->{vc} eq 'vendor') {
787 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
791 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
792 "quonumber" : "ordnumber"};
794 Common::webdav_folder($form);
796 my $rc = $dbh->commit;
798 $self->save_periodic_invoices_config(dbh => $dbh,
799 oe_id => $form->{id},
800 config_yaml => $form->{periodic_invoices_config})
801 if ($form->{type} eq 'sales_order');
803 $main::lxdebug->leave_sub();
808 sub save_periodic_invoices_config {
809 my ($self, %params) = @_;
811 return if !$params{oe_id};
813 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
814 return if 'HASH' ne ref $config;
816 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
817 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
818 $obj->update_attributes(%{ $config });
821 sub load_periodic_invoice_config {
825 delete $form->{periodic_invoices_config};
828 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
831 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
832 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
833 $form->{periodic_invoices_config} = YAML::Dump($config);
838 sub _close_quotations_rfqs {
839 $main::lxdebug->enter_sub();
844 Common::check_params(\%params, qw(from_id to_id));
846 my $myconfig = \%main::myconfig;
847 my $form = $main::form;
849 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
851 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
852 my $sth = prepare_query($form, $dbh, $query);
854 do_statement($form, $sth, $query, conv_i($params{to_id}));
856 my ($quotation) = $sth->fetchrow_array();
859 $main::lxdebug->leave_sub();
865 foreach my $from_id (@{ $params{from_id} }) {
866 $from_id = conv_i($from_id);
867 do_statement($form, $sth, $query, $from_id);
868 ($quotation) = $sth->fetchrow_array();
869 push @close_ids, $from_id if ($quotation);
874 if (scalar @close_ids) {
875 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
876 do_query($form, $dbh, $query, @close_ids);
878 $dbh->commit() unless ($params{dbh});
881 $main::lxdebug->leave_sub();
885 $main::lxdebug->enter_sub();
887 my ($self, $myconfig, $form) = @_;
889 my $rc = SL::DB::Order->new->db->with_transaction(sub {
890 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
892 SL::DB::Order->new(id => $form->{id})->delete;
894 my $spool = $::lx_office_conf{paths}->{spool};
895 unlink map { "$spool/$_" } @spoolfiles if $spool;
900 $main::lxdebug->leave_sub();
906 $main::lxdebug->enter_sub();
908 my ($self, $myconfig, $form) = @_;
910 # connect to database
911 my $dbh = $form->get_standard_dbh;
913 my ($query, $query_add, @values, @ids, $sth);
915 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
917 push @ids, $form->{"trans_id_$_"}
918 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
919 } (1 .. $form->{"rowcount"});
921 if ($form->{rowcount} && scalar @ids) {
922 $form->{convert_from_oe_ids} = join ' ', @ids;
925 # if called in multi id mode, and still only got one id, switch back to single id
926 if ($form->{"rowcount"} and $#ids == 0) {
927 $form->{"id"} = $ids[0];
929 delete $form->{convert_from_oe_ids};
932 # and remember for the rest of the function
933 my $is_collective_order = scalar @ids;
935 # If collective order was created from exactly 1 order, we assume the same
936 # behaviour as a "save as new" from within an order is actually desired, i.e.
937 # the original order isn't part of a workflow where we want to remember
938 # record_links, but simply a quick way of generating a new order from an old
939 # one without having to enter everything again.
940 # Setting useasnew will prevent the creation of record_links for the items
941 # when saving the new order.
942 # This form variable is probably not necessary, could just set saveasnew instead
943 $form->{useasnew} = 1 if $is_collective_order == 1;
946 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
947 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
948 $form->{transdate} = DateTime->today_local->to_kivitendo;
951 # get default accounts
952 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
953 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
954 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
955 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
956 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
957 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
958 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
961 my $ref = selectfirst_hashref_query($form, $dbh, $query);
962 map { $form->{$_} = $ref->{$_} } keys %$ref;
964 $form->{currency} = $form->get_default_currency($myconfig);
966 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
967 # we come from invoices, feel free.
968 $form->{reqdate} = $form->{deliverydate}
969 if ( $form->{deliverydate}
970 and $form->{callback} =~ /action=ar_transactions/);
972 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
974 if ($form->{id} or @ids) {
976 # retrieve order for single id
977 # NOTE: this query is intended to fetch all information only ONCE.
978 # so if any of these infos is important (or even different) for any item,
979 # it will be killed out and then has to be fetched from the item scope query further down
981 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
982 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
983 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
984 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
985 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
987 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
988 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
989 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
990 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
992 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
993 LEFT JOIN employee e ON (o.employee_id = e.id)
994 LEFT JOIN department d ON (o.department_id = d.id) | .
997 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
999 @values = $form->{id} ? ($form->{id}) : @ids;
1000 $sth = prepare_execute_query($form, $dbh, $query, @values);
1002 $ref = $sth->fetchrow_hashref("NAME_lc");
1005 map { $form->{$_} = $ref->{$_} } keys %$ref;
1007 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1009 # set all entries for multiple ids blank that yield different information
1010 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1011 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1014 $form->{mtime} ||= $form->{itime};
1015 $form->{lastmtime} = $form->{mtime};
1017 # if not given, fill transdate with current_date
1018 $form->{transdate} = $form->current_date($myconfig)
1019 unless $form->{transdate};
1023 if ($form->{delivery_customer_id}) {
1024 $query = qq|SELECT name FROM customer WHERE id = ?|;
1025 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1028 if ($form->{delivery_vendor_id}) {
1029 $query = qq|SELECT name FROM customer WHERE id = ?|;
1030 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1033 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1035 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1036 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1038 $ref = $sth->fetchrow_hashref("NAME_lc");
1040 map { $form->{$_} = $ref->{$_} } keys %$ref;
1043 if ($form->{shipto_id}) {
1044 my $cvars = CVar->get_custom_variables(
1047 trans_id => $form->{shipto_id},
1049 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1052 # get printed, emailed and queued
1053 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1054 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1056 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1057 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1058 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1059 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1062 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1065 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1067 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1069 # retrieve individual items
1070 # this query looks up all information about the items
1071 # stuff different from the whole will not be overwritten, but saved with a suffix.
1073 qq|SELECT o.id AS orderitems_id,
1074 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1075 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1076 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1077 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1078 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1079 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1080 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1081 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1082 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1083 pr.projectnumber, p.formel,
1084 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1086 JOIN parts p ON (o.parts_id = p.id)
1087 JOIN oe ON (o.trans_id = oe.id)
1088 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1089 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)
1090 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)
1091 LEFT JOIN project pr ON (o.project_id = pr.id)
1092 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1094 ? qq|WHERE o.trans_id = ?|
1095 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1096 qq|ORDER BY o.trans_id, o.position|;
1098 @ids = $form->{id} ? ($form->{id}) : @ids;
1099 $sth = prepare_execute_query($form, $dbh, $query, @values);
1101 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1102 # Retrieve custom variables.
1103 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1105 sub_module => 'orderitems',
1106 trans_id => $ref->{orderitems_id},
1108 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1111 if (!$ref->{"part_inventory_accno_id"}) {
1112 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1114 delete($ref->{"part_inventory_accno_id"});
1116 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1117 # unless already present there
1118 # remove _oe entries afterwards
1119 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1120 qw|ordnumber transdate cusordnumber|
1122 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1126 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1128 qq|SELECT accno AS inventory_accno, | .
1129 qq| new_chart_id AS inventory_new_chart, | .
1130 qq| date($transdate) - valid_from AS inventory_valid | .
1131 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1132 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1133 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1136 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1138 qq|SELECT accno AS income_accno, | .
1139 qq| new_chart_id AS income_new_chart, | .
1140 qq| date($transdate) - valid_from AS income_valid | .
1141 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1142 ($ref->{income_accno}, $ref->{income_new_chart},
1143 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1146 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1148 qq|SELECT accno AS expense_accno, | .
1149 qq| new_chart_id AS expense_new_chart, | .
1150 qq| date($transdate) - valid_from AS expense_valid | .
1151 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1152 ($ref->{expense_accno}, $ref->{expense_new_chart},
1153 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1156 # delete orderitems_id in collective orders, so that they get cloned no matter what
1157 # is this correct? or is the following meant?
1158 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1159 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1161 # get tax rates and description
1162 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1164 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1165 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1166 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1167 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1168 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1169 qq|ORDER BY c.accno|;
1170 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1171 $ref->{taxaccounts} = "";
1173 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1174 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1178 $ref->{taxaccounts} .= "$ptr->{accno} ";
1179 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1180 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1181 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1182 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1183 $form->{taxaccounts} .= "$ptr->{accno} ";
1188 chop $ref->{taxaccounts};
1190 push @{ $form->{form_details} }, $ref;
1197 # get last name used
1198 $form->lastname_used($dbh, $myconfig, $form->{vc})
1199 unless $form->{"$form->{vc}_id"};
1203 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1205 Common::webdav_folder($form);
1207 $self->load_periodic_invoice_config($form);
1209 my $rc = $dbh->commit;
1211 $main::lxdebug->leave_sub();
1216 sub retrieve_simple {
1217 $main::lxdebug->enter_sub();
1222 Common::check_params(\%params, qw(id));
1224 my $myconfig = \%main::myconfig;
1225 my $form = $main::form;
1227 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1229 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1230 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1232 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1233 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1235 $main::lxdebug->leave_sub();
1241 $main::lxdebug->enter_sub();
1243 my ($self, $myconfig, $form) = @_;
1245 # connect to database
1246 my $dbh = $form->get_standard_dbh;
1252 my $nodiscount_subtotal = 0;
1253 my $discount_subtotal = 0;
1256 my @partsgroup = ();
1259 my $subtotal_header = 0;
1260 my $subposition = 0;
1268 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1270 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1271 'departments' => 'ALL_DEPARTMENTS');
1274 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1275 $price_factors{$pfac->{id}} = $pfac;
1276 $pfac->{factor} *= 1;
1277 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1281 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1282 next unless $dept->{id} eq $form->{department_id};
1283 $form->{department} = $dept->{description};
1287 # sort items by partsgroup
1288 for $i (1 .. $form->{rowcount}) {
1290 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1291 $partsgroup = $form->{"partsgroup_$i"};
1293 push @partsgroup, [$i, $partsgroup];
1294 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1300 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1301 %projects_by_id = map { $_->id => $_ } @$projects;
1304 if ($projects_by_id{$form->{"globalproject_id"}}) {
1305 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1306 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1308 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1309 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1313 $form->{discount} = [];
1315 # get some values of parts from db on store them in extra array,
1316 # so that they can be sorted in later
1317 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1318 my @prepared_arrays = keys %prepared_template_arrays;
1320 $form->{TEMPLATE_ARRAYS} = { };
1322 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1323 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1326 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1327 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1328 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1329 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1330 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1332 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1333 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1335 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1337 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1339 my $totalweight = 0;
1341 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1344 if ($item->[1] ne $sameitem) {
1345 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1346 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1347 $sameitem = $item->[1];
1349 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1352 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1354 if ($form->{"id_$i"} != 0) {
1356 # add number, description and qty to $form->{number}, ....
1358 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1359 $subtotal_header = $i;
1360 $position = int($position);
1363 } elsif ($subtotal_header) {
1365 $position = int($position);
1366 $position = $position.".".$subposition;
1368 $position = int($position);
1372 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1374 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1376 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1377 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1378 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1379 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1380 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1381 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1382 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1383 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1384 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1385 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1386 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1387 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1388 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1389 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1390 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1391 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1392 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1393 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1394 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1395 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1396 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1398 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1399 my ($dec) = ($sellprice =~ /\.(\d+)/);
1400 my $decimalplaces = max 2, length($dec);
1402 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1404 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1405 my $linetotal = $form->round_amount($linetotal_exact, 2);
1407 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1408 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1410 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1412 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1414 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1416 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1417 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1419 $linetotal = ($linetotal != 0) ? $linetotal : '';
1421 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1422 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1423 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1425 $form->{ordtotal} += $linetotal;
1426 $form->{nodiscount_total} += $nodiscount_linetotal;
1427 $form->{discount_total} += $discount;
1429 if ($subtotal_header) {
1430 $discount_subtotal += $linetotal;
1431 $nodiscount_subtotal += $nodiscount_linetotal;
1434 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1435 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1436 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1437 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1438 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1440 $discount_subtotal = 0;
1441 $nodiscount_subtotal = 0;
1442 $subtotal_header = 0;
1445 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1448 if (!$form->{"discount_$i"}) {
1449 $nodiscount += $linetotal;
1452 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1454 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1455 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1456 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1457 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1458 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1459 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1461 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1462 $totalweight += $lineweight;
1463 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1464 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1465 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1466 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1468 my ($taxamount, $taxbase);
1471 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1473 if ($form->{taxincluded}) {
1476 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1477 $taxbase = $linetotal / (1 + $taxrate);
1479 $taxamount = $linetotal * $taxrate;
1480 $taxbase = $linetotal;
1483 if ($taxamount != 0) {
1484 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1485 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1486 $taxbase{$accno} += $taxbase;
1490 $tax_rate = $taxrate * 100;
1491 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1493 if ($form->{"assembly_$i"}) {
1496 # get parts and push them onto the stack
1498 if ($form->{groupitems}) {
1499 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1501 $sortorder = qq|ORDER BY a.oid|;
1504 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1505 qq|pg.partsgroup | .
1506 qq|FROM assembly a | .
1507 qq| JOIN parts p ON (a.parts_id = p.id) | .
1508 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1509 qq| WHERE a.bom = '1' | .
1510 qq| AND a.id = ? | . $sortorder;
1511 @values = ($form->{"id_$i"});
1512 $sth = $dbh->prepare($query);
1513 $sth->execute(@values) || $form->dberror($query);
1515 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1516 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1517 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1518 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1519 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1520 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1523 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1524 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1525 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1530 CVar->get_non_editable_ic_cvars(form => $form,
1533 sub_module => 'orderitems',
1534 may_converted_from => ['orderitems', 'invoice']);
1536 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1537 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1538 for @{ $ic_cvar_configs };
1540 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1544 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1545 $form->{totalweight_nofmt} = $totalweight;
1546 my $defaults = AM->get_defaults();
1547 $form->{weightunit} = $defaults->{weightunit};
1550 foreach $item (sort keys %taxaccounts) {
1551 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1553 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1554 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1555 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1556 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1557 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1558 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1559 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1561 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1562 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1563 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1566 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1567 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1568 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1569 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1571 if($form->{taxincluded}) {
1572 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1573 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1575 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1576 $form->{subtotal_nofmt} = $form->{ordtotal};
1579 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1582 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1584 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1586 $form->{username} = $myconfig->{name};
1590 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1591 if ($form->{delivery_term} && $form->{language_id}) {
1592 $form->{delivery_term}->description_long( $form->{delivery_term}->translated_attribute('description_long', $form->{language_id}));
1593 $form->{delivery_term}->description_long_invoice($form->{delivery_term}->translated_attribute('description_long_invoice', $form->{language_id}));
1596 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1598 $main::lxdebug->leave_sub();
1601 sub project_description {
1602 $main::lxdebug->enter_sub();
1604 my ($self, $dbh, $id) = @_;
1606 my $query = qq|SELECT description FROM project WHERE id = ?|;
1607 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1609 $main::lxdebug->leave_sub();
1620 OE.pm - Order entry module
1624 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>.
1630 =item retrieve_simple PARAMS
1632 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1634 my $order = retrieve_simple(id => 2);