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;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form) = @_;
62 my $dbh = $form->get_standard_dbh;
65 my $ordnumber = 'ordnumber';
71 my ($periodic_invoices_columns, $periodic_invoices_joins);
73 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
75 if ($form->{type} =~ /_quotation$/) {
77 $ordnumber = 'quonumber';
79 } elsif ($form->{type} eq 'sales_order') {
80 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
81 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
84 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
88 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
90 SELECT from_id, ar.amount, ar.netamount FROM (
93 WHERE from_table = 'oe' AND to_table = 'ar'
95 SELECT rl1.from_id, rl2.to_id
97 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
98 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
100 LEFT JOIN ar ON ar.id = rl.to_id
102 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
103 $billed_amount{ $ref->{from_id}} += $ref->{amount};
104 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
109 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
110 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
111 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
112 qq| o.transaction_description, | .
113 qq| o.marge_total, o.marge_percent, | .
114 qq| o.itime::DATE AS insertdate, | .
115 qq| ex.$rate AS exchangerate, | .
116 qq| pt.description AS payment_terms, | .
117 qq| pr.projectnumber AS globalprojectnumber, | .
118 qq| e.name AS employee, s.name AS salesman, | .
119 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
120 qq| tz.description AS taxzone | .
121 $periodic_invoices_columns .
122 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
124 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
125 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
126 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
127 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
128 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
129 qq| AND ex.transdate = o.transdate) | .
130 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
131 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
132 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
133 qq|$periodic_invoices_joins | .
134 qq|WHERE (o.quotation = ?) |;
135 push(@values, $quotation);
137 my ($null, $split_department_id) = split /--/, $form->{department};
138 my $department_id = $form->{department_id} || $split_department_id;
139 if ($department_id) {
140 $query .= qq| AND o.department_id = ?|;
141 push(@values, $department_id);
144 if ($form->{"project_id"}) {
146 qq|AND ((globalproject_id = ?) OR EXISTS | .
147 qq| (SELECT * FROM orderitems oi | .
148 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
149 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
152 if ($form->{"projectnumber"}) {
154 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
155 SELECT * FROM orderitems oi
156 LEFT JOIN project proi ON proi.id = oi.project_id
157 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
160 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
163 if ($form->{"business_id"}) {
164 $query .= " AND ct.business_id = ?";
165 push(@values, $form->{"business_id"});
168 if ($form->{"${vc}_id"}) {
169 $query .= " AND o.${vc}_id = ?";
170 push(@values, $form->{"${vc}_id"});
172 } elsif ($form->{$vc}) {
173 $query .= " AND ct.name ILIKE ?";
174 push(@values, '%' . $form->{$vc} . '%');
177 if ($form->{"cp_name"}) {
178 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
179 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
182 if (!$main::auth->assert('sales_all_edit', 1)) {
183 $query .= " AND o.employee_id = (select id from employee where login= ?)";
184 push @values, $::myconfig{login};
186 if ($form->{employee_id}) {
187 $query .= " AND o.employee_id = ?";
188 push @values, conv_i($form->{employee_id});
191 if ($form->{salesman_id}) {
192 $query .= " AND o.salesman_id = ?";
193 push @values, conv_i($form->{salesman_id});
196 if (!$form->{open} && !$form->{closed}) {
197 $query .= " AND o.id = 0";
198 } elsif (!($form->{open} && $form->{closed})) {
199 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
202 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
203 ($form->{"notdelivered"} ne $form->{"delivered"})) {
204 $query .= $form->{"delivered"} ?
205 " AND o.delivered " : " AND NOT o.delivered";
208 if ($form->{$ordnumber}) {
209 $query .= qq| AND o.$ordnumber ILIKE ?|;
210 push(@values, '%' . $form->{$ordnumber} . '%');
213 if ($form->{cusordnumber}) {
214 $query .= qq| AND o.cusordnumber ILIKE ?|;
215 push(@values, '%' . $form->{cusordnumber} . '%');
218 if($form->{transdatefrom}) {
219 $query .= qq| AND o.transdate >= ?|;
220 push(@values, conv_date($form->{transdatefrom}));
223 if($form->{transdateto}) {
224 $query .= qq| AND o.transdate <= ?|;
225 push(@values, conv_date($form->{transdateto}));
228 if($form->{reqdatefrom}) {
229 $query .= qq| AND o.reqdate >= ?|;
230 push(@values, conv_date($form->{reqdatefrom}));
233 if($form->{reqdateto}) {
234 $query .= qq| AND o.reqdate <= ?|;
235 push(@values, conv_date($form->{reqdateto}));
238 if($form->{insertdatefrom}) {
239 $query .= qq| AND o.itime::DATE >= ?|;
240 push(@values, conv_date($form->{insertdatefrom}));
243 if($form->{insertdateto}) {
244 $query .= qq| AND o.itime::DATE <= ?|;
245 push(@values, conv_date($form->{insertdateto}));
248 if ($form->{shippingpoint}) {
249 $query .= qq| AND o.shippingpoint ILIKE ?|;
250 push(@values, '%' . $form->{shippingpoint} . '%');
253 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
254 $query .= qq| AND tz.id = ?|;
255 push(@values, $form->{taxzone_id});
258 if ($form->{transaction_description}) {
259 $query .= qq| AND o.transaction_description ILIKE ?|;
260 push(@values, '%' . $form->{transaction_description} . '%');
263 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
264 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
265 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
268 if ($form->{reqdate_unset_or_old}) {
269 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
272 if (($form->{order_probability_value} || '') ne '') {
273 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
274 $query .= qq| AND (o.order_probability ${op} ?)|;
275 push @values, $form->{order_probability_value};
278 if ($form->{expected_billing_date_from}) {
279 $query .= qq| AND (o.expected_billing_date >= ?)|;
280 push @values, conv_date($form->{expected_billing_date_from});
283 if ($form->{expected_billing_date_to}) {
284 $query .= qq| AND (o.expected_billing_date <= ?)|;
285 push @values, conv_date($form->{expected_billing_date_to});
288 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
289 'trans_id_field' => 'ct.id',
293 $query .= qq| AND ($cvar_where)|;
294 push @values, @cvar_values;
297 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
298 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
299 my %allowed_sort_columns = (
300 "transdate" => "o.transdate",
301 "reqdate" => "o.reqdate",
303 "ordnumber" => "o.ordnumber",
304 "cusordnumber" => "o.cusordnumber",
305 "quonumber" => "o.quonumber",
307 "employee" => "e.name",
308 "salesman" => "s.name",
309 "shipvia" => "o.shipvia",
310 "transaction_description" => "o.transaction_description",
311 "shippingpoint" => "o.shippingpoint",
312 "insertdate" => "o.itime",
313 "taxzone" => "tz.description",
314 "payment_terms" => "pt.description",
316 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
317 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
319 $query .= qq| ORDER by | . $sortorder;
321 my $sth = $dbh->prepare($query);
322 $sth->execute(@values) ||
323 $form->dberror($query . " (" . join(", ", @values) . ")");
327 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
328 $ref->{billed_amount} = $billed_amount{$ref->{id}};
329 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
330 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
331 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
332 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
333 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
334 $id{ $ref->{id} } = $ref->{id};
339 $main::lxdebug->leave_sub();
342 sub transactions_for_todo_list {
343 $main::lxdebug->enter_sub();
348 my $myconfig = \%main::myconfig;
349 my $form = $main::form;
351 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
353 my $query = qq|SELECT id FROM employee WHERE login = ?|;
354 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
357 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
358 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
363 LEFT JOIN customer c ON (oe.customer_id = c.id)
364 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
365 LEFT JOIN employee e ON (oe.employee_id = e.id)
366 WHERE (COALESCE(quotation, FALSE) = TRUE)
367 AND (COALESCE(closed, FALSE) = FALSE)
368 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
369 AND NOT (oe.reqdate ISNULL)
370 AND (oe.reqdate < current_date)
373 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
375 $main::lxdebug->leave_sub();
381 $main::lxdebug->enter_sub();
383 my ($self, $myconfig, $form) = @_;
385 # connect to database, turn off autocommit
386 my $dbh = $form->get_standard_dbh;
387 my $restricter = SL::HTML::Restrict->create;
389 my ($query, @values, $sth, $null);
390 my $exchangerate = 0;
392 my $all_units = AM->retrieve_units($myconfig, $form);
393 $form->{all_units} = $all_units;
395 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
398 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
399 unless ($form->{employee_id}) {
400 $form->get_employee($dbh);
403 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
405 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
406 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
407 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
410 $query = qq|DELETE FROM shipto | .
411 qq|WHERE trans_id = ? AND module = 'OE'|;
412 do_query($form, $dbh, $query, $form->{id});
416 $query = qq|SELECT nextval('id')|;
417 ($form->{id}) = selectrow_query($form, $dbh, $query);
419 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
420 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
437 my @processed_orderitems;
439 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
440 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
443 for my $i (1 .. $form->{rowcount}) {
445 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
447 if ($form->{"id_$i"}) {
450 $query = qq|SELECT unit FROM parts WHERE id = ?|;
451 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
454 if (defined($all_units->{$item_unit}->{factor}) &&
455 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
456 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
458 my $baseqty = $form->{"qty_$i"} * $basefactor;
460 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
461 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
463 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
465 # keep entered selling price
467 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
469 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
471 my $decimalplaces = ($dec > 2) ? $dec : 2;
473 # undo discount formatting
474 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
477 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
479 # round linetotal at least to 2 decimal places
480 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
481 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
483 $form->{"inventory_accno_$i"} *= 1;
484 $form->{"expense_accno_$i"} *= 1;
486 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
490 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
492 if ($form->{taxincluded}) {
493 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
494 $taxbase = $linetotal - $taxamount;
496 # we are not keeping a natural price, do not round
497 $form->{"sellprice_$i"} =
498 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
500 $taxamount = $linetotal * $taxrate;
501 $taxbase = $linetotal;
504 if ($form->round_amount($taxrate, 7) == 0) {
505 if ($form->{taxincluded}) {
506 foreach my $item (@taxaccounts) {
507 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
508 $taxaccounts{$item} += $taxamount;
509 $taxdiff += $taxamount;
510 $taxbase{$item} += $taxbase;
512 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
514 foreach my $item (@taxaccounts) {
515 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
516 $taxbase{$item} += $taxbase;
520 foreach my $item (@taxaccounts) {
521 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
522 $taxbase{$item} += $taxbase;
526 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
528 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
530 # Get pricegroup_id and save it. Unfortunately the interface
531 # also uses ID "0" for signalling that none is selected, but "0"
532 # must not be stored in the database. Therefore we cannot simply
534 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
536 $pricegroup_id = undef if !$pricegroup_id;
538 # force new project, if not set yet
539 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
540 require SL::DB::Customer;
541 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
542 die "Can't find customer" unless $customer;
543 my $new_project = SL::DB::Project->new(
544 projectnumber => $form->{ordnumber},
545 description => $customer->name,
546 customer_id => $customer->id,
548 project_type_id => $::instance_conf->get_project_type_id,
549 project_status_id => $::instance_conf->get_project_status_id,
552 $form->{"globalproject_id"} = $new_project->id;
555 CVar->get_non_editable_ic_cvars(form => $form,
558 sub_module => 'orderitems',
559 may_converted_from => ['orderitems', 'invoice']);
563 # save detail record in orderitems table
564 if (! $form->{"orderitems_id_$i"}) {
565 $query = qq|SELECT nextval('orderitemsid')|;
566 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
568 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
569 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
572 my $orderitems_id = $form->{"orderitems_id_$i"};
573 push @processed_orderitems, $orderitems_id;
576 UPDATE orderitems SET
577 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
578 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
579 pricegroup_id = ?, subtotal = ?,
580 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
581 active_price_source = ?, active_discount_source = ?,
582 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
586 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
587 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
588 $form->{"qty_$i"}, $baseqty,
589 $fxsellprice, $form->{"discount_$i"},
590 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
591 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
592 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
593 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
594 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
595 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
596 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
597 conv_i($orderitems_id),
600 do_query($form, $dbh, $query, @values);
602 $form->{"sellprice_$i"} = $fxsellprice;
603 $form->{"discount_$i"} *= 100;
605 CVar->save_custom_variables(module => 'IC',
606 sub_module => 'orderitems',
607 trans_id => $orderitems_id,
608 configs => $ic_cvar_configs,
610 name_prefix => 'ic_',
611 name_postfix => "_$i",
614 # link previous items with orderitems
615 foreach (qw(orderitems invoice)) {
616 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
617 RecordLinks->create_links('dbh' => $dbh,
620 'from_ids' => $form->{"converted_from_${_}_id_$i"},
621 'to_table' => 'orderitems',
622 'to_id' => $orderitems_id,
625 delete $form->{"converted_from_${_}_id_$i"};
630 # search for orphaned ids
631 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
632 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
633 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
635 if (scalar @orphaned_ids) {
636 # clean up orderitems
637 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
638 do_query($form, $dbh, $query, @orphaned_ids);
641 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
645 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
647 $amount = $form->round_amount($netamount + $tax, 2, 1);
648 $netamount = $form->round_amount($netamount, 2);
650 if ($form->{currency} eq $form->{defaultcurrency}) {
651 $form->{exchangerate} = 1;
653 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
656 # from inputfield (exchangerate) or hidden (forex)
657 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
659 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
661 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
663 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
668 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
669 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
670 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
671 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
672 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
673 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
674 , order_probability = ?, expected_billing_date = ?
677 @values = ($form->{ordnumber} || '', $form->{quonumber},
678 $form->{cusordnumber}, conv_date($form->{transdate}),
679 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
680 $amount, $netamount, conv_date($reqdate),
681 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
682 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
683 $form->{currency}, $form->{closed} ? 't' : 'f',
684 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
685 $quotation, conv_i($form->{department_id}),
686 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
687 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
688 conv_i($form->{delivery_vendor_id}),
689 conv_i($form->{delivery_customer_id}),
690 conv_i($form->{delivery_term_id}),
691 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
692 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
693 $form->{transaction_description},
694 $form->{marge_total} * 1, $form->{marge_percent} * 1,
695 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
696 conv_i($form->{id}));
697 do_query($form, $dbh, $query, @values);
699 $form->new_lastmtime('oe');
701 $form->{ordtotal} = $amount;
703 $form->{name} = $form->{ $form->{vc} };
704 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
707 if (!$form->{shipto_id}) {
708 $form->add_shipto($dbh, $form->{id}, "OE");
711 # save printed, emailed, queued
712 $form->save_status($dbh);
714 # Link this record to the records it was created from.
715 # check every record type we may link. i am not happy with converting the string to array back
716 # should be a array from the start (OE.pm -> retrieve).
717 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
718 $form->{convert_from_oe_ids} =~ s/^\s+//;
719 $form->{convert_from_oe_ids} =~ s/\s+$//;
720 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
721 delete $form->{convert_from_oe_ids};
722 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
723 foreach (qw(ar oe)) {
724 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
725 RecordLinks->create_links('dbh' => $dbh,
728 'from_ids' => $form->{"convert_from_${_}_ids"},
730 'to_id' => $form->{id},
732 delete $form->{"convert_from_${_}_ids"};
734 $self->_close_quotations_rfqs('dbh' => $dbh,
735 'from_id' => \@convert_from_oe_ids,
736 'to_id' => $form->{id}) if $_ eq 'oe';
739 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
740 if ($form->{vc} eq 'customer') {
741 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
743 if ($form->{vc} eq 'vendor') {
744 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
748 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
749 "quonumber" : "ordnumber"};
751 Common::webdav_folder($form);
753 my $rc = $dbh->commit;
755 $self->save_periodic_invoices_config(dbh => $dbh,
756 oe_id => $form->{id},
757 config_yaml => $form->{periodic_invoices_config})
758 if ($form->{type} eq 'sales_order');
760 $main::lxdebug->leave_sub();
765 sub save_periodic_invoices_config {
766 my ($self, %params) = @_;
768 return if !$params{oe_id};
770 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
771 return if 'HASH' ne ref $config;
773 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
774 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
775 $obj->update_attributes(%{ $config });
778 sub load_periodic_invoice_config {
782 delete $form->{periodic_invoices_config};
785 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
788 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
789 print printer_id copies direct_debit) };
790 $form->{periodic_invoices_config} = YAML::Dump($config);
795 sub _close_quotations_rfqs {
796 $main::lxdebug->enter_sub();
801 Common::check_params(\%params, qw(from_id to_id));
803 my $myconfig = \%main::myconfig;
804 my $form = $main::form;
806 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
808 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
809 my $sth = prepare_query($form, $dbh, $query);
811 do_statement($form, $sth, $query, conv_i($params{to_id}));
813 my ($quotation) = $sth->fetchrow_array();
816 $main::lxdebug->leave_sub();
822 foreach my $from_id (@{ $params{from_id} }) {
823 $from_id = conv_i($from_id);
824 do_statement($form, $sth, $query, $from_id);
825 ($quotation) = $sth->fetchrow_array();
826 push @close_ids, $from_id if ($quotation);
831 if (scalar @close_ids) {
832 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
833 do_query($form, $dbh, $query, @close_ids);
835 $dbh->commit() unless ($params{dbh});
838 $main::lxdebug->leave_sub();
842 $main::lxdebug->enter_sub();
844 my ($self, $myconfig, $form) = @_;
846 my $rc = SL::DB::Order->new->db->with_transaction(sub {
847 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
849 SL::DB::Order->new(id => $form->{id})->delete;
851 my $spool = $::lx_office_conf{paths}->{spool};
852 unlink map { "$spool/$_" } @spoolfiles if $spool;
857 $main::lxdebug->leave_sub();
863 $main::lxdebug->enter_sub();
865 my ($self, $myconfig, $form) = @_;
867 # connect to database
868 my $dbh = $form->get_standard_dbh;
870 my ($query, $query_add, @values, @ids, $sth);
872 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
874 push @ids, $form->{"trans_id_$_"}
875 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
876 } (1 .. $form->{"rowcount"});
878 if ($form->{rowcount} && scalar @ids) {
879 $form->{convert_from_oe_ids} = join ' ', @ids;
882 # if called in multi id mode, and still only got one id, switch back to single id
883 if ($form->{"rowcount"} and $#ids == 0) {
884 $form->{"id"} = $ids[0];
886 delete $form->{convert_from_oe_ids};
889 # and remember for the rest of the function
890 my $is_collective_order = scalar @ids;
892 # If collective order was created from exactly 1 order, we assume the same
893 # behaviour as a "save as new" from within an order is actually desired, i.e.
894 # the original order isn't part of a workflow where we want to remember
895 # record_links, but simply a quick way of generating a new order from an old
896 # one without having to enter everything again.
897 # Setting useasnew will prevent the creation of record_links for the items
898 # when saving the new order.
899 # This form variable is probably not necessary, could just set saveasnew instead
900 $form->{useasnew} = 1 if $is_collective_order == 1;
903 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
904 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
905 $form->{transdate} = DateTime->today_local->to_kivitendo;
908 # get default accounts
909 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
910 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
911 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
912 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
913 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
914 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
915 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
918 my $ref = selectfirst_hashref_query($form, $dbh, $query);
919 map { $form->{$_} = $ref->{$_} } keys %$ref;
921 $form->{currency} = $form->get_default_currency($myconfig);
923 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
924 # we come from invoices, feel free.
925 $form->{reqdate} = $form->{deliverydate}
926 if ( $form->{deliverydate}
927 and $form->{callback} =~ /action=ar_transactions/);
929 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
931 if ($form->{id} or @ids) {
933 # retrieve order for single id
934 # NOTE: this query is intended to fetch all information only ONCE.
935 # so if any of these infos is important (or even different) for any item,
936 # it will be killed out and then has to be fetched from the item scope query further down
938 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
939 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
940 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
941 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
942 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
944 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
945 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
946 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
947 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
949 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
950 LEFT JOIN employee e ON (o.employee_id = e.id)
951 LEFT JOIN department d ON (o.department_id = d.id) | .
954 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
956 @values = $form->{id} ? ($form->{id}) : @ids;
957 $sth = prepare_execute_query($form, $dbh, $query, @values);
959 $ref = $sth->fetchrow_hashref("NAME_lc");
962 map { $form->{$_} = $ref->{$_} } keys %$ref;
964 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
966 # set all entries for multiple ids blank that yield different information
967 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
968 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
971 $form->{mtime} ||= $form->{itime};
972 $form->{lastmtime} = $form->{mtime};
974 # if not given, fill transdate with current_date
975 $form->{transdate} = $form->current_date($myconfig)
976 unless $form->{transdate};
980 if ($form->{delivery_customer_id}) {
981 $query = qq|SELECT name FROM customer WHERE id = ?|;
982 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
985 if ($form->{delivery_vendor_id}) {
986 $query = qq|SELECT name FROM customer WHERE id = ?|;
987 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
990 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
992 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
993 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
995 $ref = $sth->fetchrow_hashref("NAME_lc");
997 map { $form->{$_} = $ref->{$_} } keys %$ref;
1000 # get printed, emailed and queued
1001 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1002 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1004 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1005 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1006 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1007 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1010 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1013 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1015 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1017 # retrieve individual items
1018 # this query looks up all information about the items
1019 # stuff different from the whole will not be overwritten, but saved with a suffix.
1021 qq|SELECT o.id AS orderitems_id,
1022 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1023 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1024 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1025 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1026 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1027 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1028 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1029 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1030 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1031 pr.projectnumber, p.formel,
1032 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1034 JOIN parts p ON (o.parts_id = p.id)
1035 JOIN oe ON (o.trans_id = oe.id)
1036 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1037 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)
1038 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)
1039 LEFT JOIN project pr ON (o.project_id = pr.id)
1040 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1042 ? qq|WHERE o.trans_id = ?|
1043 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1044 qq|ORDER BY o.trans_id, o.position|;
1046 @ids = $form->{id} ? ($form->{id}) : @ids;
1047 $sth = prepare_execute_query($form, $dbh, $query, @values);
1049 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1050 # Retrieve custom variables.
1051 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1053 sub_module => 'orderitems',
1054 trans_id => $ref->{orderitems_id},
1056 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1059 if (!$ref->{"part_inventory_accno_id"}) {
1060 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1062 delete($ref->{"part_inventory_accno_id"});
1064 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1065 # unless already present there
1066 # remove _oe entries afterwards
1067 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1068 qw|ordnumber transdate cusordnumber|
1070 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1074 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1076 qq|SELECT accno AS inventory_accno, | .
1077 qq| new_chart_id AS inventory_new_chart, | .
1078 qq| date($transdate) - valid_from AS inventory_valid | .
1079 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1080 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1081 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1084 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1086 qq|SELECT accno AS income_accno, | .
1087 qq| new_chart_id AS income_new_chart, | .
1088 qq| date($transdate) - valid_from AS income_valid | .
1089 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1090 ($ref->{income_accno}, $ref->{income_new_chart},
1091 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1094 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1096 qq|SELECT accno AS expense_accno, | .
1097 qq| new_chart_id AS expense_new_chart, | .
1098 qq| date($transdate) - valid_from AS expense_valid | .
1099 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1100 ($ref->{expense_accno}, $ref->{expense_new_chart},
1101 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1104 # delete orderitems_id in collective orders, so that they get cloned no matter what
1105 # is this correct? or is the following meant?
1106 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1107 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1109 # get tax rates and description
1110 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1112 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1113 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1114 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1115 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1116 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1117 qq|ORDER BY c.accno|;
1118 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1119 $ref->{taxaccounts} = "";
1121 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1122 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1126 $ref->{taxaccounts} .= "$ptr->{accno} ";
1127 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1128 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1129 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1130 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1131 $form->{taxaccounts} .= "$ptr->{accno} ";
1136 chop $ref->{taxaccounts};
1138 push @{ $form->{form_details} }, $ref;
1145 # get last name used
1146 $form->lastname_used($dbh, $myconfig, $form->{vc})
1147 unless $form->{"$form->{vc}_id"};
1151 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1153 Common::webdav_folder($form);
1155 $self->load_periodic_invoice_config($form);
1157 my $rc = $dbh->commit;
1159 $main::lxdebug->leave_sub();
1164 sub retrieve_simple {
1165 $main::lxdebug->enter_sub();
1170 Common::check_params(\%params, qw(id));
1172 my $myconfig = \%main::myconfig;
1173 my $form = $main::form;
1175 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1177 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1178 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1180 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1181 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1183 $main::lxdebug->leave_sub();
1189 $main::lxdebug->enter_sub();
1191 my ($self, $myconfig, $form) = @_;
1193 # connect to database
1194 my $dbh = $form->get_standard_dbh;
1200 my $nodiscount_subtotal = 0;
1201 my $discount_subtotal = 0;
1204 my @partsgroup = ();
1207 my $subtotal_header = 0;
1208 my $subposition = 0;
1216 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1218 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1219 'departments' => 'ALL_DEPARTMENTS');
1222 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1223 $price_factors{$pfac->{id}} = $pfac;
1224 $pfac->{factor} *= 1;
1225 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1229 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1230 next unless $dept->{id} eq $form->{department_id};
1231 $form->{department} = $dept->{description};
1235 # sort items by partsgroup
1236 for $i (1 .. $form->{rowcount}) {
1238 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1239 $partsgroup = $form->{"partsgroup_$i"};
1241 push @partsgroup, [$i, $partsgroup];
1242 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1248 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1249 %projects_by_id = map { $_->id => $_ } @$projects;
1252 if ($projects_by_id{$form->{"globalproject_id"}}) {
1253 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1254 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1256 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1257 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1261 $form->{discount} = [];
1263 # get some values of parts from db on store them in extra array,
1264 # so that they can be sorted in later
1265 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1266 my @prepared_arrays = keys %prepared_template_arrays;
1268 $form->{TEMPLATE_ARRAYS} = { };
1270 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1271 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1274 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1275 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1276 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1277 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1278 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1280 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1281 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1283 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1285 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1287 my $totalweight = 0;
1289 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1292 if ($item->[1] ne $sameitem) {
1293 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1294 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1295 $sameitem = $item->[1];
1297 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1300 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1302 if ($form->{"id_$i"} != 0) {
1304 # add number, description and qty to $form->{number}, ....
1306 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1307 $subtotal_header = $i;
1308 $position = int($position);
1311 } elsif ($subtotal_header) {
1313 $position = int($position);
1314 $position = $position.".".$subposition;
1316 $position = int($position);
1320 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1322 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1324 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1325 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1326 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1327 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1328 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1329 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1330 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1331 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1332 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1333 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1334 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1335 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1336 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1337 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1338 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1339 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1340 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1341 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1342 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1343 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1344 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1346 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1347 my ($dec) = ($sellprice =~ /\.(\d+)/);
1348 my $decimalplaces = max 2, length($dec);
1350 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1352 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1353 my $linetotal = $form->round_amount($linetotal_exact, 2);
1355 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1356 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1358 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1360 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1362 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1364 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1365 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1367 $linetotal = ($linetotal != 0) ? $linetotal : '';
1369 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1370 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1371 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1373 $form->{ordtotal} += $linetotal;
1374 $form->{nodiscount_total} += $nodiscount_linetotal;
1375 $form->{discount_total} += $discount;
1377 if ($subtotal_header) {
1378 $discount_subtotal += $linetotal;
1379 $nodiscount_subtotal += $nodiscount_linetotal;
1382 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1383 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1384 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1385 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1386 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1388 $discount_subtotal = 0;
1389 $nodiscount_subtotal = 0;
1390 $subtotal_header = 0;
1393 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1396 if (!$form->{"discount_$i"}) {
1397 $nodiscount += $linetotal;
1400 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1402 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1403 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1404 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1405 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1406 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1407 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1409 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1410 $totalweight += $lineweight;
1411 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1412 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1413 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1414 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1416 my ($taxamount, $taxbase);
1419 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1421 if ($form->{taxincluded}) {
1424 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1425 $taxbase = $linetotal / (1 + $taxrate);
1427 $taxamount = $linetotal * $taxrate;
1428 $taxbase = $linetotal;
1431 if ($taxamount != 0) {
1432 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1433 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1434 $taxbase{$accno} += $taxbase;
1438 $tax_rate = $taxrate * 100;
1439 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1441 if ($form->{"assembly_$i"}) {
1444 # get parts and push them onto the stack
1446 if ($form->{groupitems}) {
1447 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1449 $sortorder = qq|ORDER BY a.oid|;
1452 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1453 qq|pg.partsgroup | .
1454 qq|FROM assembly a | .
1455 qq| JOIN parts p ON (a.parts_id = p.id) | .
1456 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1457 qq| WHERE a.bom = '1' | .
1458 qq| AND a.id = ? | . $sortorder;
1459 @values = ($form->{"id_$i"});
1460 $sth = $dbh->prepare($query);
1461 $sth->execute(@values) || $form->dberror($query);
1463 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1464 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1465 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1466 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1467 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1468 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1471 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1472 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1473 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1478 CVar->get_non_editable_ic_cvars(form => $form,
1481 sub_module => 'orderitems',
1482 may_converted_from => ['orderitems', 'invoice']);
1484 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1485 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1486 for @{ $ic_cvar_configs };
1488 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1492 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1493 $form->{totalweight_nofmt} = $totalweight;
1494 my $defaults = AM->get_defaults();
1495 $form->{weightunit} = $defaults->{weightunit};
1498 foreach $item (sort keys %taxaccounts) {
1499 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1501 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1502 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1503 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1504 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1505 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1506 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1507 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1509 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1510 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1511 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1514 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1515 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1516 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1517 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1519 if($form->{taxincluded}) {
1520 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1521 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1523 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1524 $form->{subtotal_nofmt} = $form->{ordtotal};
1527 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1530 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1532 if ($form->{type} =~ /_quotation/) {
1533 $form->set_payment_options($myconfig, $form->{quodate});
1535 $form->set_payment_options($myconfig, $form->{orddate});
1538 $form->{username} = $myconfig->{name};
1542 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1543 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1545 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1547 $main::lxdebug->leave_sub();
1550 sub project_description {
1551 $main::lxdebug->enter_sub();
1553 my ($self, $dbh, $id) = @_;
1555 my $query = qq|SELECT description FROM project WHERE id = ?|;
1556 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1558 $main::lxdebug->leave_sub();
1569 OE.pm - Order entry module
1573 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>.
1579 =item retrieve_simple PARAMS
1581 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1583 my $order = retrieve_simple(id => 2);