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;
58 $main::lxdebug->enter_sub();
60 my ($self, $myconfig, $form) = @_;
63 my $dbh = $form->get_standard_dbh;
66 my $ordnumber = 'ordnumber';
72 my ($periodic_invoices_columns, $periodic_invoices_joins);
74 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
76 if ($form->{type} =~ /_quotation$/) {
78 $ordnumber = 'quonumber';
80 } elsif ($form->{type} eq 'sales_order') {
81 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
82 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
85 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
89 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
91 SELECT from_id, ar.amount, ar.netamount FROM (
94 WHERE from_table = 'oe' AND to_table = 'ar'
96 SELECT rl1.from_id, rl2.to_id
98 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
99 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
101 LEFT JOIN ar ON ar.id = rl.to_id
103 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
104 $billed_amount{ $ref->{from_id}} += $ref->{amount};
105 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
110 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
111 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
112 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
113 qq| o.transaction_description, | .
114 qq| o.marge_total, o.marge_percent, | .
115 qq| o.itime::DATE AS insertdate, | .
116 qq| ex.$rate AS exchangerate, | .
117 qq| pt.description AS payment_terms, | .
118 qq| pr.projectnumber AS globalprojectnumber, | .
119 qq| e.name AS employee, s.name AS salesman, | .
120 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
121 qq| tz.description AS taxzone | .
122 $periodic_invoices_columns .
123 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
125 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
126 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
127 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
128 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
129 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
130 qq| AND ex.transdate = o.transdate) | .
131 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
132 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
133 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
134 qq|$periodic_invoices_joins | .
135 qq|WHERE (o.quotation = ?) |;
136 push(@values, $quotation);
138 my ($null, $split_department_id) = split /--/, $form->{department};
139 my $department_id = $form->{department_id} || $split_department_id;
140 if ($department_id) {
141 $query .= qq| AND o.department_id = ?|;
142 push(@values, $department_id);
145 if ($form->{"project_id"}) {
147 qq|AND ((globalproject_id = ?) OR EXISTS | .
148 qq| (SELECT * FROM orderitems oi | .
149 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
150 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
153 if ($form->{"projectnumber"}) {
155 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
156 SELECT * FROM orderitems oi
157 LEFT JOIN project proi ON proi.id = oi.project_id
158 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
161 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
164 if ($form->{"business_id"}) {
165 $query .= " AND ct.business_id = ?";
166 push(@values, $form->{"business_id"});
169 if ($form->{"${vc}_id"}) {
170 $query .= " AND o.${vc}_id = ?";
171 push(@values, $form->{"${vc}_id"});
173 } elsif ($form->{$vc}) {
174 $query .= " AND ct.name ILIKE ?";
175 push(@values, '%' . $form->{$vc} . '%');
178 if ($form->{"cp_name"}) {
179 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
180 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
183 if (!$main::auth->assert('sales_all_edit', 1)) {
184 $query .= " AND o.employee_id = (select id from employee where login= ?)";
185 push @values, $::myconfig{login};
187 if ($form->{employee_id}) {
188 $query .= " AND o.employee_id = ?";
189 push @values, conv_i($form->{employee_id});
192 if ($form->{salesman_id}) {
193 $query .= " AND o.salesman_id = ?";
194 push @values, conv_i($form->{salesman_id});
197 if (!$form->{open} && !$form->{closed}) {
198 $query .= " AND o.id = 0";
199 } elsif (!($form->{open} && $form->{closed})) {
200 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
203 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
204 ($form->{"notdelivered"} ne $form->{"delivered"})) {
205 $query .= $form->{"delivered"} ?
206 " AND o.delivered " : " AND NOT o.delivered";
209 if ($form->{$ordnumber}) {
210 $query .= qq| AND o.$ordnumber ILIKE ?|;
211 push(@values, '%' . $form->{$ordnumber} . '%');
214 if ($form->{cusordnumber}) {
215 $query .= qq| AND o.cusordnumber ILIKE ?|;
216 push(@values, '%' . $form->{cusordnumber} . '%');
219 if($form->{transdatefrom}) {
220 $query .= qq| AND o.transdate >= ?|;
221 push(@values, conv_date($form->{transdatefrom}));
224 if($form->{transdateto}) {
225 $query .= qq| AND o.transdate <= ?|;
226 push(@values, conv_date($form->{transdateto}));
229 if($form->{reqdatefrom}) {
230 $query .= qq| AND o.reqdate >= ?|;
231 push(@values, conv_date($form->{reqdatefrom}));
234 if($form->{reqdateto}) {
235 $query .= qq| AND o.reqdate <= ?|;
236 push(@values, conv_date($form->{reqdateto}));
239 if($form->{insertdatefrom}) {
240 $query .= qq| AND o.itime::DATE >= ?|;
241 push(@values, conv_date($form->{insertdatefrom}));
244 if($form->{insertdateto}) {
245 $query .= qq| AND o.itime::DATE <= ?|;
246 push(@values, conv_date($form->{insertdateto}));
249 if ($form->{shippingpoint}) {
250 $query .= qq| AND o.shippingpoint ILIKE ?|;
251 push(@values, '%' . $form->{shippingpoint} . '%');
254 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
255 $query .= qq| AND tz.id = ?|;
256 push(@values, $form->{taxzone_id});
259 if ($form->{transaction_description}) {
260 $query .= qq| AND o.transaction_description ILIKE ?|;
261 push(@values, '%' . $form->{transaction_description} . '%');
264 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
265 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
266 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
269 if ($form->{reqdate_unset_or_old}) {
270 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
273 if (($form->{order_probability_value} || '') ne '') {
274 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
275 $query .= qq| AND (o.order_probability ${op} ?)|;
276 push @values, $form->{order_probability_value};
279 if ($form->{expected_billing_date_from}) {
280 $query .= qq| AND (o.expected_billing_date >= ?)|;
281 push @values, conv_date($form->{expected_billing_date_from});
284 if ($form->{expected_billing_date_to}) {
285 $query .= qq| AND (o.expected_billing_date <= ?)|;
286 push @values, conv_date($form->{expected_billing_date_to});
290 my @tokens = parse_line('\s+', 0, $form->{all});
291 # ordnumber quonumber customer.name vendor.name transaction_description
293 o.ordnumber ILIKE ? OR
294 o.quonumber ILIKE ? OR
296 o.transaction_description ILIKE ?
298 push @values, ("%$_%")x4 for @tokens;
301 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
302 'trans_id_field' => 'ct.id',
306 $query .= qq| AND ($cvar_where)|;
307 push @values, @cvar_values;
310 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
311 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
312 my %allowed_sort_columns = (
313 "transdate" => "o.transdate",
314 "reqdate" => "o.reqdate",
316 "ordnumber" => "o.ordnumber",
317 "cusordnumber" => "o.cusordnumber",
318 "quonumber" => "o.quonumber",
320 "employee" => "e.name",
321 "salesman" => "s.name",
322 "shipvia" => "o.shipvia",
323 "transaction_description" => "o.transaction_description",
324 "shippingpoint" => "o.shippingpoint",
325 "insertdate" => "o.itime",
326 "taxzone" => "tz.description",
327 "payment_terms" => "pt.description",
329 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
330 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
332 $query .= qq| ORDER by | . $sortorder;
334 my $sth = $dbh->prepare($query);
335 $sth->execute(@values) ||
336 $form->dberror($query . " (" . join(", ", @values) . ")");
340 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
341 $ref->{billed_amount} = $billed_amount{$ref->{id}};
342 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
343 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
344 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
345 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
346 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
347 $id{ $ref->{id} } = $ref->{id};
352 $main::lxdebug->leave_sub();
355 sub transactions_for_todo_list {
356 $main::lxdebug->enter_sub();
361 my $myconfig = \%main::myconfig;
362 my $form = $main::form;
364 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
366 my $query = qq|SELECT id FROM employee WHERE login = ?|;
367 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
370 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
371 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
376 LEFT JOIN customer c ON (oe.customer_id = c.id)
377 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
378 LEFT JOIN employee e ON (oe.employee_id = e.id)
379 WHERE (COALESCE(quotation, FALSE) = TRUE)
380 AND (COALESCE(closed, FALSE) = FALSE)
381 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
382 AND NOT (oe.reqdate ISNULL)
383 AND (oe.reqdate < current_date)
386 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
388 $main::lxdebug->leave_sub();
394 $main::lxdebug->enter_sub();
396 my ($self, $myconfig, $form) = @_;
398 # connect to database, turn off autocommit
399 my $dbh = $form->get_standard_dbh;
400 my $restricter = SL::HTML::Restrict->create;
402 my ($query, @values, $sth, $null);
403 my $exchangerate = 0;
405 my $all_units = AM->retrieve_units($myconfig, $form);
406 $form->{all_units} = $all_units;
408 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
411 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
412 unless ($form->{employee_id}) {
413 $form->get_employee($dbh);
416 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
418 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
419 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
420 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
423 $query = qq|DELETE FROM shipto | .
424 qq|WHERE trans_id = ? AND module = 'OE'|;
425 do_query($form, $dbh, $query, $form->{id});
429 $query = qq|SELECT nextval('id')|;
430 ($form->{id}) = selectrow_query($form, $dbh, $query);
432 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
433 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
450 my @processed_orderitems;
452 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
453 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
456 for my $i (1 .. $form->{rowcount}) {
458 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
460 if ($form->{"id_$i"}) {
463 $query = qq|SELECT unit FROM parts WHERE id = ?|;
464 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
467 if (defined($all_units->{$item_unit}->{factor}) &&
468 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
469 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
471 my $baseqty = $form->{"qty_$i"} * $basefactor;
473 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
474 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
476 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
478 # keep entered selling price
480 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
482 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
484 my $decimalplaces = ($dec > 2) ? $dec : 2;
486 # undo discount formatting
487 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
490 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
492 # round linetotal at least to 2 decimal places
493 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
494 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
496 $form->{"inventory_accno_$i"} *= 1;
497 $form->{"expense_accno_$i"} *= 1;
499 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
503 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
505 if ($form->{taxincluded}) {
506 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
507 $taxbase = $linetotal - $taxamount;
509 # we are not keeping a natural price, do not round
510 $form->{"sellprice_$i"} =
511 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
513 $taxamount = $linetotal * $taxrate;
514 $taxbase = $linetotal;
517 if ($form->round_amount($taxrate, 7) == 0) {
518 if ($form->{taxincluded}) {
519 foreach my $item (@taxaccounts) {
520 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
521 $taxaccounts{$item} += $taxamount;
522 $taxdiff += $taxamount;
523 $taxbase{$item} += $taxbase;
525 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
527 foreach my $item (@taxaccounts) {
528 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
529 $taxbase{$item} += $taxbase;
533 foreach my $item (@taxaccounts) {
534 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
535 $taxbase{$item} += $taxbase;
539 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
541 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
543 # Get pricegroup_id and save it. Unfortunately the interface
544 # also uses ID "0" for signalling that none is selected, but "0"
545 # must not be stored in the database. Therefore we cannot simply
547 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
549 $pricegroup_id = undef if !$pricegroup_id;
551 # force new project, if not set yet
552 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
553 require SL::DB::Customer;
554 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
555 die "Can't find customer" unless $customer;
556 my $new_project = SL::DB::Project->new(
557 projectnumber => $form->{ordnumber},
558 description => $customer->name,
559 customer_id => $customer->id,
561 project_type_id => $::instance_conf->get_project_type_id,
562 project_status_id => $::instance_conf->get_project_status_id,
565 $form->{"globalproject_id"} = $new_project->id;
568 CVar->get_non_editable_ic_cvars(form => $form,
571 sub_module => 'orderitems',
572 may_converted_from => ['orderitems', 'invoice']);
576 # save detail record in orderitems table
577 if (! $form->{"orderitems_id_$i"}) {
578 $query = qq|SELECT nextval('orderitemsid')|;
579 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
581 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
582 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
585 my $orderitems_id = $form->{"orderitems_id_$i"};
586 push @processed_orderitems, $orderitems_id;
589 UPDATE orderitems SET
590 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
591 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
592 pricegroup_id = ?, subtotal = ?,
593 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
594 active_price_source = ?, active_discount_source = ?,
595 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
599 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
600 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
601 $form->{"qty_$i"}, $baseqty,
602 $fxsellprice, $form->{"discount_$i"},
603 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
604 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
605 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
606 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
607 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
608 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
609 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
610 conv_i($orderitems_id),
613 do_query($form, $dbh, $query, @values);
615 $form->{"sellprice_$i"} = $fxsellprice;
616 $form->{"discount_$i"} *= 100;
618 CVar->save_custom_variables(module => 'IC',
619 sub_module => 'orderitems',
620 trans_id => $orderitems_id,
621 configs => $ic_cvar_configs,
623 name_prefix => 'ic_',
624 name_postfix => "_$i",
627 # link previous items with orderitems
628 foreach (qw(orderitems invoice)) {
629 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
630 RecordLinks->create_links('dbh' => $dbh,
633 'from_ids' => $form->{"converted_from_${_}_id_$i"},
634 'to_table' => 'orderitems',
635 'to_id' => $orderitems_id,
638 delete $form->{"converted_from_${_}_id_$i"};
643 # search for orphaned ids
644 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
645 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
646 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
648 if (scalar @orphaned_ids) {
649 # clean up orderitems
650 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
651 do_query($form, $dbh, $query, @orphaned_ids);
654 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
658 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
660 $amount = $form->round_amount($netamount + $tax, 2, 1);
661 $netamount = $form->round_amount($netamount, 2);
663 if ($form->{currency} eq $form->{defaultcurrency}) {
664 $form->{exchangerate} = 1;
666 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
669 # from inputfield (exchangerate) or hidden (forex)
670 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
672 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
674 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
676 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
681 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
682 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
683 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
684 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
685 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
686 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
687 , order_probability = ?, expected_billing_date = ?
690 @values = ($form->{ordnumber} || '', $form->{quonumber},
691 $form->{cusordnumber}, conv_date($form->{transdate}),
692 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
693 $amount, $netamount, conv_date($reqdate),
694 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
695 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
696 $form->{currency}, $form->{closed} ? 't' : 'f',
697 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
698 $quotation, conv_i($form->{department_id}),
699 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
700 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
701 conv_i($form->{delivery_vendor_id}),
702 conv_i($form->{delivery_customer_id}),
703 conv_i($form->{delivery_term_id}),
704 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
705 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
706 $form->{transaction_description},
707 $form->{marge_total} * 1, $form->{marge_percent} * 1,
708 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
709 conv_i($form->{id}));
710 do_query($form, $dbh, $query, @values);
712 $form->new_lastmtime('oe');
714 $form->{ordtotal} = $amount;
716 $form->{name} = $form->{ $form->{vc} };
717 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
720 if (!$form->{shipto_id}) {
721 $form->add_shipto($dbh, $form->{id}, "OE");
724 # save printed, emailed, queued
725 $form->save_status($dbh);
727 # Link this record to the records it was created from.
728 # check every record type we may link. i am not happy with converting the string to array back
729 # should be a array from the start (OE.pm -> retrieve).
730 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
731 $form->{convert_from_oe_ids} =~ s/^\s+//;
732 $form->{convert_from_oe_ids} =~ s/\s+$//;
733 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
734 delete $form->{convert_from_oe_ids};
735 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
736 foreach (qw(ar oe)) {
737 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
738 RecordLinks->create_links('dbh' => $dbh,
741 'from_ids' => $form->{"convert_from_${_}_ids"},
743 'to_id' => $form->{id},
745 delete $form->{"convert_from_${_}_ids"};
747 $self->_close_quotations_rfqs('dbh' => $dbh,
748 'from_id' => \@convert_from_oe_ids,
749 'to_id' => $form->{id}) if $_ eq 'oe';
752 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
753 if ($form->{vc} eq 'customer') {
754 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
756 if ($form->{vc} eq 'vendor') {
757 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
761 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
762 "quonumber" : "ordnumber"};
764 Common::webdav_folder($form);
766 my $rc = $dbh->commit;
768 $self->save_periodic_invoices_config(dbh => $dbh,
769 oe_id => $form->{id},
770 config_yaml => $form->{periodic_invoices_config})
771 if ($form->{type} eq 'sales_order');
773 $main::lxdebug->leave_sub();
778 sub save_periodic_invoices_config {
779 my ($self, %params) = @_;
781 return if !$params{oe_id};
783 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
784 return if 'HASH' ne ref $config;
786 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
787 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
788 $obj->update_attributes(%{ $config });
791 sub load_periodic_invoice_config {
795 delete $form->{periodic_invoices_config};
798 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
801 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
802 print printer_id copies direct_debit) };
803 $form->{periodic_invoices_config} = YAML::Dump($config);
808 sub _close_quotations_rfqs {
809 $main::lxdebug->enter_sub();
814 Common::check_params(\%params, qw(from_id to_id));
816 my $myconfig = \%main::myconfig;
817 my $form = $main::form;
819 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
821 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
822 my $sth = prepare_query($form, $dbh, $query);
824 do_statement($form, $sth, $query, conv_i($params{to_id}));
826 my ($quotation) = $sth->fetchrow_array();
829 $main::lxdebug->leave_sub();
835 foreach my $from_id (@{ $params{from_id} }) {
836 $from_id = conv_i($from_id);
837 do_statement($form, $sth, $query, $from_id);
838 ($quotation) = $sth->fetchrow_array();
839 push @close_ids, $from_id if ($quotation);
844 if (scalar @close_ids) {
845 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
846 do_query($form, $dbh, $query, @close_ids);
848 $dbh->commit() unless ($params{dbh});
851 $main::lxdebug->leave_sub();
855 $main::lxdebug->enter_sub();
857 my ($self, $myconfig, $form) = @_;
859 my $rc = SL::DB::Order->new->db->with_transaction(sub {
860 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
862 SL::DB::Order->new(id => $form->{id})->delete;
864 my $spool = $::lx_office_conf{paths}->{spool};
865 unlink map { "$spool/$_" } @spoolfiles if $spool;
870 $main::lxdebug->leave_sub();
876 $main::lxdebug->enter_sub();
878 my ($self, $myconfig, $form) = @_;
880 # connect to database
881 my $dbh = $form->get_standard_dbh;
883 my ($query, $query_add, @values, @ids, $sth);
885 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
887 push @ids, $form->{"trans_id_$_"}
888 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
889 } (1 .. $form->{"rowcount"});
891 if ($form->{rowcount} && scalar @ids) {
892 $form->{convert_from_oe_ids} = join ' ', @ids;
895 # if called in multi id mode, and still only got one id, switch back to single id
896 if ($form->{"rowcount"} and $#ids == 0) {
897 $form->{"id"} = $ids[0];
899 delete $form->{convert_from_oe_ids};
902 # and remember for the rest of the function
903 my $is_collective_order = scalar @ids;
905 # If collective order was created from exactly 1 order, we assume the same
906 # behaviour as a "save as new" from within an order is actually desired, i.e.
907 # the original order isn't part of a workflow where we want to remember
908 # record_links, but simply a quick way of generating a new order from an old
909 # one without having to enter everything again.
910 # Setting useasnew will prevent the creation of record_links for the items
911 # when saving the new order.
912 # This form variable is probably not necessary, could just set saveasnew instead
913 $form->{useasnew} = 1 if $is_collective_order == 1;
916 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
917 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
918 $form->{transdate} = DateTime->today_local->to_kivitendo;
921 # get default accounts
922 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
923 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
924 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
925 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
926 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
927 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
928 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
931 my $ref = selectfirst_hashref_query($form, $dbh, $query);
932 map { $form->{$_} = $ref->{$_} } keys %$ref;
934 $form->{currency} = $form->get_default_currency($myconfig);
936 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
937 # we come from invoices, feel free.
938 $form->{reqdate} = $form->{deliverydate}
939 if ( $form->{deliverydate}
940 and $form->{callback} =~ /action=ar_transactions/);
942 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
944 if ($form->{id} or @ids) {
946 # retrieve order for single id
947 # NOTE: this query is intended to fetch all information only ONCE.
948 # so if any of these infos is important (or even different) for any item,
949 # it will be killed out and then has to be fetched from the item scope query further down
951 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
952 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
953 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
954 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
955 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
957 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
958 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
959 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
960 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
962 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
963 LEFT JOIN employee e ON (o.employee_id = e.id)
964 LEFT JOIN department d ON (o.department_id = d.id) | .
967 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
969 @values = $form->{id} ? ($form->{id}) : @ids;
970 $sth = prepare_execute_query($form, $dbh, $query, @values);
972 $ref = $sth->fetchrow_hashref("NAME_lc");
975 map { $form->{$_} = $ref->{$_} } keys %$ref;
977 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
979 # set all entries for multiple ids blank that yield different information
980 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
981 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
984 $form->{mtime} ||= $form->{itime};
985 $form->{lastmtime} = $form->{mtime};
987 # if not given, fill transdate with current_date
988 $form->{transdate} = $form->current_date($myconfig)
989 unless $form->{transdate};
993 if ($form->{delivery_customer_id}) {
994 $query = qq|SELECT name FROM customer WHERE id = ?|;
995 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
998 if ($form->{delivery_vendor_id}) {
999 $query = qq|SELECT name FROM customer WHERE id = ?|;
1000 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1003 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1005 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1006 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1008 $ref = $sth->fetchrow_hashref("NAME_lc");
1010 map { $form->{$_} = $ref->{$_} } keys %$ref;
1013 # get printed, emailed and queued
1014 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1015 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1017 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1018 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1019 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1020 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1023 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1026 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1028 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1030 # retrieve individual items
1031 # this query looks up all information about the items
1032 # stuff different from the whole will not be overwritten, but saved with a suffix.
1034 qq|SELECT o.id AS orderitems_id,
1035 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1036 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1037 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1038 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1039 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1040 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1041 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1042 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1043 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1044 pr.projectnumber, p.formel,
1045 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1047 JOIN parts p ON (o.parts_id = p.id)
1048 JOIN oe ON (o.trans_id = oe.id)
1049 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1050 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)
1051 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)
1052 LEFT JOIN project pr ON (o.project_id = pr.id)
1053 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1055 ? qq|WHERE o.trans_id = ?|
1056 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1057 qq|ORDER BY o.trans_id, o.position|;
1059 @ids = $form->{id} ? ($form->{id}) : @ids;
1060 $sth = prepare_execute_query($form, $dbh, $query, @values);
1062 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1063 # Retrieve custom variables.
1064 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1066 sub_module => 'orderitems',
1067 trans_id => $ref->{orderitems_id},
1069 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1072 if (!$ref->{"part_inventory_accno_id"}) {
1073 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1075 delete($ref->{"part_inventory_accno_id"});
1077 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1078 # unless already present there
1079 # remove _oe entries afterwards
1080 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1081 qw|ordnumber transdate cusordnumber|
1083 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1087 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1089 qq|SELECT accno AS inventory_accno, | .
1090 qq| new_chart_id AS inventory_new_chart, | .
1091 qq| date($transdate) - valid_from AS inventory_valid | .
1092 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1093 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1094 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1097 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1099 qq|SELECT accno AS income_accno, | .
1100 qq| new_chart_id AS income_new_chart, | .
1101 qq| date($transdate) - valid_from AS income_valid | .
1102 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1103 ($ref->{income_accno}, $ref->{income_new_chart},
1104 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1107 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1109 qq|SELECT accno AS expense_accno, | .
1110 qq| new_chart_id AS expense_new_chart, | .
1111 qq| date($transdate) - valid_from AS expense_valid | .
1112 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1113 ($ref->{expense_accno}, $ref->{expense_new_chart},
1114 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1117 # delete orderitems_id in collective orders, so that they get cloned no matter what
1118 # is this correct? or is the following meant?
1119 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1120 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1122 # get tax rates and description
1123 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1125 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1126 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1127 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1128 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1129 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1130 qq|ORDER BY c.accno|;
1131 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1132 $ref->{taxaccounts} = "";
1134 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1135 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1139 $ref->{taxaccounts} .= "$ptr->{accno} ";
1140 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1141 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1142 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1143 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1144 $form->{taxaccounts} .= "$ptr->{accno} ";
1149 chop $ref->{taxaccounts};
1151 push @{ $form->{form_details} }, $ref;
1158 # get last name used
1159 $form->lastname_used($dbh, $myconfig, $form->{vc})
1160 unless $form->{"$form->{vc}_id"};
1164 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1166 Common::webdav_folder($form);
1168 $self->load_periodic_invoice_config($form);
1170 my $rc = $dbh->commit;
1172 $main::lxdebug->leave_sub();
1177 sub retrieve_simple {
1178 $main::lxdebug->enter_sub();
1183 Common::check_params(\%params, qw(id));
1185 my $myconfig = \%main::myconfig;
1186 my $form = $main::form;
1188 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1190 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1191 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1193 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1194 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1196 $main::lxdebug->leave_sub();
1202 $main::lxdebug->enter_sub();
1204 my ($self, $myconfig, $form) = @_;
1206 # connect to database
1207 my $dbh = $form->get_standard_dbh;
1213 my $nodiscount_subtotal = 0;
1214 my $discount_subtotal = 0;
1217 my @partsgroup = ();
1220 my $subtotal_header = 0;
1221 my $subposition = 0;
1229 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1231 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1232 'departments' => 'ALL_DEPARTMENTS');
1235 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1236 $price_factors{$pfac->{id}} = $pfac;
1237 $pfac->{factor} *= 1;
1238 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1242 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1243 next unless $dept->{id} eq $form->{department_id};
1244 $form->{department} = $dept->{description};
1248 # sort items by partsgroup
1249 for $i (1 .. $form->{rowcount}) {
1251 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1252 $partsgroup = $form->{"partsgroup_$i"};
1254 push @partsgroup, [$i, $partsgroup];
1255 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1261 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1262 %projects_by_id = map { $_->id => $_ } @$projects;
1265 if ($projects_by_id{$form->{"globalproject_id"}}) {
1266 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1267 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1269 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1270 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1274 $form->{discount} = [];
1276 # get some values of parts from db on store them in extra array,
1277 # so that they can be sorted in later
1278 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1279 my @prepared_arrays = keys %prepared_template_arrays;
1281 $form->{TEMPLATE_ARRAYS} = { };
1283 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1284 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1287 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1288 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1289 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1290 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1291 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1293 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1294 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1296 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1298 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1300 my $totalweight = 0;
1302 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1305 if ($item->[1] ne $sameitem) {
1306 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1307 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1308 $sameitem = $item->[1];
1310 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1313 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1315 if ($form->{"id_$i"} != 0) {
1317 # add number, description and qty to $form->{number}, ....
1319 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1320 $subtotal_header = $i;
1321 $position = int($position);
1324 } elsif ($subtotal_header) {
1326 $position = int($position);
1327 $position = $position.".".$subposition;
1329 $position = int($position);
1333 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1335 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1337 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1338 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1339 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1340 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1341 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1342 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1343 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1344 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1345 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1346 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1347 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1348 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1349 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1350 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1351 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1352 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1353 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1354 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1355 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1356 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1357 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1359 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1360 my ($dec) = ($sellprice =~ /\.(\d+)/);
1361 my $decimalplaces = max 2, length($dec);
1363 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1365 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1366 my $linetotal = $form->round_amount($linetotal_exact, 2);
1368 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1369 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1371 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1373 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1375 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1377 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1378 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1380 $linetotal = ($linetotal != 0) ? $linetotal : '';
1382 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1383 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1384 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1386 $form->{ordtotal} += $linetotal;
1387 $form->{nodiscount_total} += $nodiscount_linetotal;
1388 $form->{discount_total} += $discount;
1390 if ($subtotal_header) {
1391 $discount_subtotal += $linetotal;
1392 $nodiscount_subtotal += $nodiscount_linetotal;
1395 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1396 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1397 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1398 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1399 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1401 $discount_subtotal = 0;
1402 $nodiscount_subtotal = 0;
1403 $subtotal_header = 0;
1406 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1409 if (!$form->{"discount_$i"}) {
1410 $nodiscount += $linetotal;
1413 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1415 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1416 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1417 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1418 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1419 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1420 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1422 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1423 $totalweight += $lineweight;
1424 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1425 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1426 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1427 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1429 my ($taxamount, $taxbase);
1432 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1434 if ($form->{taxincluded}) {
1437 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1438 $taxbase = $linetotal / (1 + $taxrate);
1440 $taxamount = $linetotal * $taxrate;
1441 $taxbase = $linetotal;
1444 if ($taxamount != 0) {
1445 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1446 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1447 $taxbase{$accno} += $taxbase;
1451 $tax_rate = $taxrate * 100;
1452 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1454 if ($form->{"assembly_$i"}) {
1457 # get parts and push them onto the stack
1459 if ($form->{groupitems}) {
1460 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1462 $sortorder = qq|ORDER BY a.oid|;
1465 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1466 qq|pg.partsgroup | .
1467 qq|FROM assembly a | .
1468 qq| JOIN parts p ON (a.parts_id = p.id) | .
1469 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1470 qq| WHERE a.bom = '1' | .
1471 qq| AND a.id = ? | . $sortorder;
1472 @values = ($form->{"id_$i"});
1473 $sth = $dbh->prepare($query);
1474 $sth->execute(@values) || $form->dberror($query);
1476 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1477 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1478 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1479 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1480 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1481 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1484 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1485 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1486 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1491 CVar->get_non_editable_ic_cvars(form => $form,
1494 sub_module => 'orderitems',
1495 may_converted_from => ['orderitems', 'invoice']);
1497 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1498 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1499 for @{ $ic_cvar_configs };
1501 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1505 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1506 $form->{totalweight_nofmt} = $totalweight;
1507 my $defaults = AM->get_defaults();
1508 $form->{weightunit} = $defaults->{weightunit};
1511 foreach $item (sort keys %taxaccounts) {
1512 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1514 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1515 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1516 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1517 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1518 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1519 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1520 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1522 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1523 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1524 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1527 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1528 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1529 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1530 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1532 if($form->{taxincluded}) {
1533 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1534 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1536 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1537 $form->{subtotal_nofmt} = $form->{ordtotal};
1540 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1543 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1545 if ($form->{type} =~ /_quotation/) {
1546 $form->set_payment_options($myconfig, $form->{quodate});
1548 $form->set_payment_options($myconfig, $form->{orddate});
1551 $form->{username} = $myconfig->{name};
1555 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1556 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1558 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1560 $main::lxdebug->leave_sub();
1563 sub project_description {
1564 $main::lxdebug->enter_sub();
1566 my ($self, $dbh, $id) = @_;
1568 my $query = qq|SELECT description FROM project WHERE id = ?|;
1569 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1571 $main::lxdebug->leave_sub();
1582 OE.pm - Order entry module
1586 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>.
1592 =item retrieve_simple PARAMS
1594 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1596 my $order = retrieve_simple(id => 2);