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});
291 my @tokens = parse_line('\s+', 0, $form->{all});
292 # ordnumber quonumber customer.name vendor.name transaction_description
294 o.ordnumber ILIKE ? OR
295 o.quonumber ILIKE ? OR
297 o.transaction_description ILIKE ?
299 push @values, (like($_))x4 for @tokens;
302 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
303 'trans_id_field' => 'ct.id',
307 $query .= qq| AND ($cvar_where)|;
308 push @values, @cvar_values;
311 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
312 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
313 my %allowed_sort_columns = (
314 "transdate" => "o.transdate",
315 "reqdate" => "o.reqdate",
317 "ordnumber" => "o.ordnumber",
318 "cusordnumber" => "o.cusordnumber",
319 "quonumber" => "o.quonumber",
321 "employee" => "e.name",
322 "salesman" => "s.name",
323 "shipvia" => "o.shipvia",
324 "transaction_description" => "o.transaction_description",
325 "shippingpoint" => "o.shippingpoint",
326 "insertdate" => "o.itime",
327 "taxzone" => "tz.description",
328 "payment_terms" => "pt.description",
330 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
331 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
333 $query .= qq| ORDER by | . $sortorder;
335 my $sth = $dbh->prepare($query);
336 $sth->execute(@values) ||
337 $form->dberror($query . " (" . join(", ", @values) . ")");
341 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
342 $ref->{billed_amount} = $billed_amount{$ref->{id}};
343 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
344 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
345 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
346 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
347 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
348 $id{ $ref->{id} } = $ref->{id};
353 $main::lxdebug->leave_sub();
356 sub transactions_for_todo_list {
357 $main::lxdebug->enter_sub();
362 my $myconfig = \%main::myconfig;
363 my $form = $main::form;
365 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
367 my $query = qq|SELECT id FROM employee WHERE login = ?|;
368 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
371 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
372 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
377 LEFT JOIN customer c ON (oe.customer_id = c.id)
378 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
379 LEFT JOIN employee e ON (oe.employee_id = e.id)
380 WHERE (COALESCE(quotation, FALSE) = TRUE)
381 AND (COALESCE(closed, FALSE) = FALSE)
382 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
383 AND NOT (oe.reqdate ISNULL)
384 AND (oe.reqdate < current_date)
387 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
389 $main::lxdebug->leave_sub();
395 $main::lxdebug->enter_sub();
397 my ($self, $myconfig, $form) = @_;
399 # connect to database, turn off autocommit
400 my $dbh = $form->get_standard_dbh;
401 my $restricter = SL::HTML::Restrict->create;
403 my ($query, @values, $sth, $null);
404 my $exchangerate = 0;
406 my $all_units = AM->retrieve_units($myconfig, $form);
407 $form->{all_units} = $all_units;
409 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
412 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
413 unless ($form->{employee_id}) {
414 $form->get_employee($dbh);
417 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
419 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
420 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
421 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
424 $query = qq|DELETE FROM custom_variables
425 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
426 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
427 do_query($form, $dbh, $query, $form->{id});
429 $query = qq|DELETE FROM shipto | .
430 qq|WHERE trans_id = ? AND module = 'OE'|;
431 do_query($form, $dbh, $query, $form->{id});
435 $query = qq|SELECT nextval('id')|;
436 ($form->{id}) = selectrow_query($form, $dbh, $query);
438 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
439 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
456 my @processed_orderitems;
458 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
459 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
462 for my $i (1 .. $form->{rowcount}) {
464 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
466 if ($form->{"id_$i"}) {
469 $query = qq|SELECT unit FROM parts WHERE id = ?|;
470 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
473 if (defined($all_units->{$item_unit}->{factor}) &&
474 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
475 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
477 my $baseqty = $form->{"qty_$i"} * $basefactor;
479 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
480 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
482 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
484 # keep entered selling price
486 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
488 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
490 my $decimalplaces = ($dec > 2) ? $dec : 2;
492 # undo discount formatting
493 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
496 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
498 # round linetotal at least to 2 decimal places
499 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
500 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
502 $form->{"inventory_accno_$i"} *= 1;
503 $form->{"expense_accno_$i"} *= 1;
505 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
509 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
511 if ($form->{taxincluded}) {
512 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
513 $taxbase = $linetotal - $taxamount;
515 # we are not keeping a natural price, do not round
516 $form->{"sellprice_$i"} =
517 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
519 $taxamount = $linetotal * $taxrate;
520 $taxbase = $linetotal;
523 if ($form->round_amount($taxrate, 7) == 0) {
524 if ($form->{taxincluded}) {
525 foreach my $item (@taxaccounts) {
526 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
527 $taxaccounts{$item} += $taxamount;
528 $taxdiff += $taxamount;
529 $taxbase{$item} += $taxbase;
531 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
533 foreach my $item (@taxaccounts) {
534 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
535 $taxbase{$item} += $taxbase;
539 foreach my $item (@taxaccounts) {
540 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
541 $taxbase{$item} += $taxbase;
545 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
547 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
549 # Get pricegroup_id and save it. Unfortunately the interface
550 # also uses ID "0" for signalling that none is selected, but "0"
551 # must not be stored in the database. Therefore we cannot simply
553 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
555 $pricegroup_id = undef if !$pricegroup_id;
557 # force new project, if not set yet
558 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
559 require SL::DB::Customer;
560 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
561 die "Can't find customer" unless $customer;
562 my $new_project = SL::DB::Project->new(
563 projectnumber => $form->{ordnumber},
564 description => $customer->name,
565 customer_id => $customer->id,
567 project_type_id => $::instance_conf->get_project_type_id,
568 project_status_id => $::instance_conf->get_project_status_id,
571 $form->{"globalproject_id"} = $new_project->id;
574 CVar->get_non_editable_ic_cvars(form => $form,
577 sub_module => 'orderitems',
578 may_converted_from => ['orderitems', 'invoice']);
582 # save detail record in orderitems table
583 if (! $form->{"orderitems_id_$i"}) {
584 $query = qq|SELECT nextval('orderitemsid')|;
585 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
587 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
588 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
591 my $orderitems_id = $form->{"orderitems_id_$i"};
592 push @processed_orderitems, $orderitems_id;
595 UPDATE orderitems SET
596 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
597 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
598 pricegroup_id = ?, subtotal = ?,
599 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
600 active_price_source = ?, active_discount_source = ?,
601 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
605 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
606 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
607 $form->{"qty_$i"}, $baseqty,
608 $fxsellprice, $form->{"discount_$i"},
609 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
610 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
611 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
612 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
613 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
614 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
615 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
616 conv_i($orderitems_id),
619 do_query($form, $dbh, $query, @values);
621 $form->{"sellprice_$i"} = $fxsellprice;
622 $form->{"discount_$i"} *= 100;
624 CVar->save_custom_variables(module => 'IC',
625 sub_module => 'orderitems',
626 trans_id => $orderitems_id,
627 configs => $ic_cvar_configs,
629 name_prefix => 'ic_',
630 name_postfix => "_$i",
633 # link previous items with orderitems
634 foreach (qw(orderitems invoice)) {
635 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
636 RecordLinks->create_links('dbh' => $dbh,
639 'from_ids' => $form->{"converted_from_${_}_id_$i"},
640 'to_table' => 'orderitems',
641 'to_id' => $orderitems_id,
644 delete $form->{"converted_from_${_}_id_$i"};
649 # search for orphaned ids
650 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
651 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
652 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
654 if (scalar @orphaned_ids) {
655 # clean up orderitems
656 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
657 do_query($form, $dbh, $query, @orphaned_ids);
660 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
664 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
666 $amount = $form->round_amount($netamount + $tax, 2, 1);
667 $netamount = $form->round_amount($netamount, 2);
669 if ($form->{currency} eq $form->{defaultcurrency}) {
670 $form->{exchangerate} = 1;
672 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
675 # from inputfield (exchangerate) or hidden (forex)
676 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
678 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
680 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
682 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
687 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
688 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
689 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
690 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
691 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
692 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
693 , order_probability = ?, expected_billing_date = ?
696 @values = ($form->{ordnumber} || '', $form->{quonumber},
697 $form->{cusordnumber}, conv_date($form->{transdate}),
698 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
699 $amount, $netamount, conv_date($reqdate),
700 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
701 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
702 $form->{currency}, $form->{closed} ? 't' : 'f',
703 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
704 $quotation, conv_i($form->{department_id}),
705 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
706 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
707 conv_i($form->{delivery_vendor_id}),
708 conv_i($form->{delivery_customer_id}),
709 conv_i($form->{delivery_term_id}),
710 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
711 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
712 $form->{transaction_description},
713 $form->{marge_total} * 1, $form->{marge_percent} * 1,
714 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
715 conv_i($form->{id}));
716 do_query($form, $dbh, $query, @values);
718 $form->new_lastmtime('oe');
720 $form->{ordtotal} = $amount;
722 $form->{name} = $form->{ $form->{vc} };
723 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
726 if (!$form->{shipto_id}) {
727 $form->add_shipto($dbh, $form->{id}, "OE");
730 # save printed, emailed, queued
731 $form->save_status($dbh);
733 # Link this record to the records it was created from.
734 # check every record type we may link. i am not happy with converting the string to array back
735 # should be a array from the start (OE.pm -> retrieve).
736 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
737 $form->{convert_from_oe_ids} =~ s/^\s+//;
738 $form->{convert_from_oe_ids} =~ s/\s+$//;
739 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
740 delete $form->{convert_from_oe_ids};
741 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
742 foreach (qw(ar oe)) {
743 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
744 RecordLinks->create_links('dbh' => $dbh,
747 'from_ids' => $form->{"convert_from_${_}_ids"},
749 'to_id' => $form->{id},
751 delete $form->{"convert_from_${_}_ids"};
753 $self->_close_quotations_rfqs('dbh' => $dbh,
754 'from_id' => \@convert_from_oe_ids,
755 'to_id' => $form->{id}) if $_ eq 'oe';
758 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
759 if ($form->{vc} eq 'customer') {
760 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
762 if ($form->{vc} eq 'vendor') {
763 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
767 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
768 "quonumber" : "ordnumber"};
770 Common::webdav_folder($form);
772 my $rc = $dbh->commit;
774 $self->save_periodic_invoices_config(dbh => $dbh,
775 oe_id => $form->{id},
776 config_yaml => $form->{periodic_invoices_config})
777 if ($form->{type} eq 'sales_order');
779 $main::lxdebug->leave_sub();
784 sub save_periodic_invoices_config {
785 my ($self, %params) = @_;
787 return if !$params{oe_id};
789 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
790 return if 'HASH' ne ref $config;
792 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
793 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
794 $obj->update_attributes(%{ $config });
797 sub load_periodic_invoice_config {
801 delete $form->{periodic_invoices_config};
804 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
807 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
808 print printer_id copies direct_debit) };
809 $form->{periodic_invoices_config} = YAML::Dump($config);
814 sub _close_quotations_rfqs {
815 $main::lxdebug->enter_sub();
820 Common::check_params(\%params, qw(from_id to_id));
822 my $myconfig = \%main::myconfig;
823 my $form = $main::form;
825 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
827 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
828 my $sth = prepare_query($form, $dbh, $query);
830 do_statement($form, $sth, $query, conv_i($params{to_id}));
832 my ($quotation) = $sth->fetchrow_array();
835 $main::lxdebug->leave_sub();
841 foreach my $from_id (@{ $params{from_id} }) {
842 $from_id = conv_i($from_id);
843 do_statement($form, $sth, $query, $from_id);
844 ($quotation) = $sth->fetchrow_array();
845 push @close_ids, $from_id if ($quotation);
850 if (scalar @close_ids) {
851 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
852 do_query($form, $dbh, $query, @close_ids);
854 $dbh->commit() unless ($params{dbh});
857 $main::lxdebug->leave_sub();
861 $main::lxdebug->enter_sub();
863 my ($self, $myconfig, $form) = @_;
865 my $rc = SL::DB::Order->new->db->with_transaction(sub {
866 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
868 SL::DB::Order->new(id => $form->{id})->delete;
870 my $spool = $::lx_office_conf{paths}->{spool};
871 unlink map { "$spool/$_" } @spoolfiles if $spool;
876 $main::lxdebug->leave_sub();
882 $main::lxdebug->enter_sub();
884 my ($self, $myconfig, $form) = @_;
886 # connect to database
887 my $dbh = $form->get_standard_dbh;
889 my ($query, $query_add, @values, @ids, $sth);
891 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
893 push @ids, $form->{"trans_id_$_"}
894 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
895 } (1 .. $form->{"rowcount"});
897 if ($form->{rowcount} && scalar @ids) {
898 $form->{convert_from_oe_ids} = join ' ', @ids;
901 # if called in multi id mode, and still only got one id, switch back to single id
902 if ($form->{"rowcount"} and $#ids == 0) {
903 $form->{"id"} = $ids[0];
905 delete $form->{convert_from_oe_ids};
908 # and remember for the rest of the function
909 my $is_collective_order = scalar @ids;
911 # If collective order was created from exactly 1 order, we assume the same
912 # behaviour as a "save as new" from within an order is actually desired, i.e.
913 # the original order isn't part of a workflow where we want to remember
914 # record_links, but simply a quick way of generating a new order from an old
915 # one without having to enter everything again.
916 # Setting useasnew will prevent the creation of record_links for the items
917 # when saving the new order.
918 # This form variable is probably not necessary, could just set saveasnew instead
919 $form->{useasnew} = 1 if $is_collective_order == 1;
922 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
923 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
924 $form->{transdate} = DateTime->today_local->to_kivitendo;
927 # get default accounts
928 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
929 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
930 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
931 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
932 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
933 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
934 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
937 my $ref = selectfirst_hashref_query($form, $dbh, $query);
938 map { $form->{$_} = $ref->{$_} } keys %$ref;
940 $form->{currency} = $form->get_default_currency($myconfig);
942 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
943 # we come from invoices, feel free.
944 $form->{reqdate} = $form->{deliverydate}
945 if ( $form->{deliverydate}
946 and $form->{callback} =~ /action=ar_transactions/);
948 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
950 if ($form->{id} or @ids) {
952 # retrieve order for single id
953 # NOTE: this query is intended to fetch all information only ONCE.
954 # so if any of these infos is important (or even different) for any item,
955 # it will be killed out and then has to be fetched from the item scope query further down
957 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
958 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
959 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
960 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
961 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
963 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
964 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
965 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
966 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
968 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
969 LEFT JOIN employee e ON (o.employee_id = e.id)
970 LEFT JOIN department d ON (o.department_id = d.id) | .
973 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
975 @values = $form->{id} ? ($form->{id}) : @ids;
976 $sth = prepare_execute_query($form, $dbh, $query, @values);
978 $ref = $sth->fetchrow_hashref("NAME_lc");
981 map { $form->{$_} = $ref->{$_} } keys %$ref;
983 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
985 # set all entries for multiple ids blank that yield different information
986 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
987 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
990 $form->{mtime} ||= $form->{itime};
991 $form->{lastmtime} = $form->{mtime};
993 # if not given, fill transdate with current_date
994 $form->{transdate} = $form->current_date($myconfig)
995 unless $form->{transdate};
999 if ($form->{delivery_customer_id}) {
1000 $query = qq|SELECT name FROM customer WHERE id = ?|;
1001 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1004 if ($form->{delivery_vendor_id}) {
1005 $query = qq|SELECT name FROM customer WHERE id = ?|;
1006 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1009 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1011 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1012 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1014 $ref = $sth->fetchrow_hashref("NAME_lc");
1016 map { $form->{$_} = $ref->{$_} } keys %$ref;
1019 if ($form->{shipto_id}) {
1020 my $cvars = CVar->get_custom_variables(
1023 trans_id => $form->{shipto_id},
1025 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1028 # get printed, emailed and queued
1029 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1030 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1032 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1033 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1034 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1035 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1038 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1041 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1043 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1045 # retrieve individual items
1046 # this query looks up all information about the items
1047 # stuff different from the whole will not be overwritten, but saved with a suffix.
1049 qq|SELECT o.id AS orderitems_id,
1050 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1051 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1052 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1053 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1054 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1055 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1056 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1057 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1058 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1059 pr.projectnumber, p.formel,
1060 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1062 JOIN parts p ON (o.parts_id = p.id)
1063 JOIN oe ON (o.trans_id = oe.id)
1064 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1065 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)
1066 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)
1067 LEFT JOIN project pr ON (o.project_id = pr.id)
1068 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1070 ? qq|WHERE o.trans_id = ?|
1071 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1072 qq|ORDER BY o.trans_id, o.position|;
1074 @ids = $form->{id} ? ($form->{id}) : @ids;
1075 $sth = prepare_execute_query($form, $dbh, $query, @values);
1077 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1078 # Retrieve custom variables.
1079 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1081 sub_module => 'orderitems',
1082 trans_id => $ref->{orderitems_id},
1084 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1087 if (!$ref->{"part_inventory_accno_id"}) {
1088 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1090 delete($ref->{"part_inventory_accno_id"});
1092 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1093 # unless already present there
1094 # remove _oe entries afterwards
1095 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1096 qw|ordnumber transdate cusordnumber|
1098 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1102 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1104 qq|SELECT accno AS inventory_accno, | .
1105 qq| new_chart_id AS inventory_new_chart, | .
1106 qq| date($transdate) - valid_from AS inventory_valid | .
1107 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1108 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1109 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1112 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1114 qq|SELECT accno AS income_accno, | .
1115 qq| new_chart_id AS income_new_chart, | .
1116 qq| date($transdate) - valid_from AS income_valid | .
1117 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1118 ($ref->{income_accno}, $ref->{income_new_chart},
1119 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1122 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1124 qq|SELECT accno AS expense_accno, | .
1125 qq| new_chart_id AS expense_new_chart, | .
1126 qq| date($transdate) - valid_from AS expense_valid | .
1127 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1128 ($ref->{expense_accno}, $ref->{expense_new_chart},
1129 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1132 # delete orderitems_id in collective orders, so that they get cloned no matter what
1133 # is this correct? or is the following meant?
1134 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1135 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1137 # get tax rates and description
1138 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1140 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1141 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1142 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1143 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1144 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1145 qq|ORDER BY c.accno|;
1146 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1147 $ref->{taxaccounts} = "";
1149 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1150 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1154 $ref->{taxaccounts} .= "$ptr->{accno} ";
1155 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1156 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1157 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1158 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1159 $form->{taxaccounts} .= "$ptr->{accno} ";
1164 chop $ref->{taxaccounts};
1166 push @{ $form->{form_details} }, $ref;
1173 # get last name used
1174 $form->lastname_used($dbh, $myconfig, $form->{vc})
1175 unless $form->{"$form->{vc}_id"};
1179 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1181 Common::webdav_folder($form);
1183 $self->load_periodic_invoice_config($form);
1185 my $rc = $dbh->commit;
1187 $main::lxdebug->leave_sub();
1192 sub retrieve_simple {
1193 $main::lxdebug->enter_sub();
1198 Common::check_params(\%params, qw(id));
1200 my $myconfig = \%main::myconfig;
1201 my $form = $main::form;
1203 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1205 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1206 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1208 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1209 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1211 $main::lxdebug->leave_sub();
1217 $main::lxdebug->enter_sub();
1219 my ($self, $myconfig, $form) = @_;
1221 # connect to database
1222 my $dbh = $form->get_standard_dbh;
1228 my $nodiscount_subtotal = 0;
1229 my $discount_subtotal = 0;
1232 my @partsgroup = ();
1235 my $subtotal_header = 0;
1236 my $subposition = 0;
1244 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1246 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1247 'departments' => 'ALL_DEPARTMENTS');
1250 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1251 $price_factors{$pfac->{id}} = $pfac;
1252 $pfac->{factor} *= 1;
1253 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1257 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1258 next unless $dept->{id} eq $form->{department_id};
1259 $form->{department} = $dept->{description};
1263 # sort items by partsgroup
1264 for $i (1 .. $form->{rowcount}) {
1266 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1267 $partsgroup = $form->{"partsgroup_$i"};
1269 push @partsgroup, [$i, $partsgroup];
1270 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1276 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1277 %projects_by_id = map { $_->id => $_ } @$projects;
1280 if ($projects_by_id{$form->{"globalproject_id"}}) {
1281 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1282 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1284 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1285 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1289 $form->{discount} = [];
1291 # get some values of parts from db on store them in extra array,
1292 # so that they can be sorted in later
1293 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1294 my @prepared_arrays = keys %prepared_template_arrays;
1296 $form->{TEMPLATE_ARRAYS} = { };
1298 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1299 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1302 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1303 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1304 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1305 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1306 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1308 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1309 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1311 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1313 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1315 my $totalweight = 0;
1317 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1320 if ($item->[1] ne $sameitem) {
1321 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1322 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1323 $sameitem = $item->[1];
1325 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1328 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1330 if ($form->{"id_$i"} != 0) {
1332 # add number, description and qty to $form->{number}, ....
1334 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1335 $subtotal_header = $i;
1336 $position = int($position);
1339 } elsif ($subtotal_header) {
1341 $position = int($position);
1342 $position = $position.".".$subposition;
1344 $position = int($position);
1348 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1350 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1352 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1353 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1354 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1355 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1356 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1357 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1358 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1359 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1360 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1361 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1362 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1363 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1364 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1365 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1366 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1367 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1368 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1369 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1370 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1371 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1372 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1374 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1375 my ($dec) = ($sellprice =~ /\.(\d+)/);
1376 my $decimalplaces = max 2, length($dec);
1378 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1380 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1381 my $linetotal = $form->round_amount($linetotal_exact, 2);
1383 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1384 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1386 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1388 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1390 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1392 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1393 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1395 $linetotal = ($linetotal != 0) ? $linetotal : '';
1397 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1398 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1399 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1401 $form->{ordtotal} += $linetotal;
1402 $form->{nodiscount_total} += $nodiscount_linetotal;
1403 $form->{discount_total} += $discount;
1405 if ($subtotal_header) {
1406 $discount_subtotal += $linetotal;
1407 $nodiscount_subtotal += $nodiscount_linetotal;
1410 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1411 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1412 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1413 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1414 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1416 $discount_subtotal = 0;
1417 $nodiscount_subtotal = 0;
1418 $subtotal_header = 0;
1421 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1424 if (!$form->{"discount_$i"}) {
1425 $nodiscount += $linetotal;
1428 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1430 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1431 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1432 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1433 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1434 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1435 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1437 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1438 $totalweight += $lineweight;
1439 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1440 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1441 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1442 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1444 my ($taxamount, $taxbase);
1447 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1449 if ($form->{taxincluded}) {
1452 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1453 $taxbase = $linetotal / (1 + $taxrate);
1455 $taxamount = $linetotal * $taxrate;
1456 $taxbase = $linetotal;
1459 if ($taxamount != 0) {
1460 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1461 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1462 $taxbase{$accno} += $taxbase;
1466 $tax_rate = $taxrate * 100;
1467 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1469 if ($form->{"assembly_$i"}) {
1472 # get parts and push them onto the stack
1474 if ($form->{groupitems}) {
1475 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1477 $sortorder = qq|ORDER BY a.oid|;
1480 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1481 qq|pg.partsgroup | .
1482 qq|FROM assembly a | .
1483 qq| JOIN parts p ON (a.parts_id = p.id) | .
1484 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1485 qq| WHERE a.bom = '1' | .
1486 qq| AND a.id = ? | . $sortorder;
1487 @values = ($form->{"id_$i"});
1488 $sth = $dbh->prepare($query);
1489 $sth->execute(@values) || $form->dberror($query);
1491 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1492 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1493 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1494 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1495 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1496 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1499 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1500 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1501 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1506 CVar->get_non_editable_ic_cvars(form => $form,
1509 sub_module => 'orderitems',
1510 may_converted_from => ['orderitems', 'invoice']);
1512 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1513 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1514 for @{ $ic_cvar_configs };
1516 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1520 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1521 $form->{totalweight_nofmt} = $totalweight;
1522 my $defaults = AM->get_defaults();
1523 $form->{weightunit} = $defaults->{weightunit};
1526 foreach $item (sort keys %taxaccounts) {
1527 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1529 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1530 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1531 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1532 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1533 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1534 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1535 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1537 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1538 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1539 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1542 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1543 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1544 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1545 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1547 if($form->{taxincluded}) {
1548 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1549 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1551 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1552 $form->{subtotal_nofmt} = $form->{ordtotal};
1555 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1558 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1560 if ($form->{type} =~ /_quotation/) {
1561 $form->set_payment_options($myconfig, $form->{quodate});
1563 $form->set_payment_options($myconfig, $form->{orddate});
1566 $form->{username} = $myconfig->{name};
1570 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1571 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1573 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1575 $main::lxdebug->leave_sub();
1578 sub project_description {
1579 $main::lxdebug->enter_sub();
1581 my ($self, $dbh, $id) = @_;
1583 my $query = qq|SELECT description FROM project WHERE id = ?|;
1584 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1586 $main::lxdebug->leave_sub();
1597 OE.pm - Order entry module
1601 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>.
1607 =item retrieve_simple PARAMS
1609 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1611 my $order = retrieve_simple(id => 2);