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;
48 use SL::HTML::Restrict;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form) = @_;
60 my $dbh = $form->get_standard_dbh;
63 my $ordnumber = 'ordnumber';
69 my ($periodic_invoices_columns, $periodic_invoices_joins);
71 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
73 if ($form->{type} =~ /_quotation$/) {
75 $ordnumber = 'quonumber';
77 } elsif ($form->{type} eq 'sales_order') {
78 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
79 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
82 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
86 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
88 SELECT from_id, ar.amount, ar.netamount FROM (
91 WHERE from_table = 'oe' AND to_table = 'ar'
93 SELECT rl1.from_id, rl2.to_id
95 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
96 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
98 LEFT JOIN ar ON ar.id = rl.to_id
100 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
101 $billed_amount{ $ref->{from_id}} += $ref->{amount};
102 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
107 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
108 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
109 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
110 qq| o.transaction_description, | .
111 qq| o.marge_total, o.marge_percent, | .
112 qq| ex.$rate AS exchangerate, | .
113 qq| pr.projectnumber AS globalprojectnumber, | .
114 qq| e.name AS employee, s.name AS salesman, | .
115 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
116 qq| tz.description AS taxzone | .
117 $periodic_invoices_columns .
118 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
120 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
121 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
122 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
123 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
124 qq| AND ex.transdate = o.transdate) | .
125 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
126 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
127 qq|$periodic_invoices_joins | .
128 qq|WHERE (o.quotation = ?) |;
129 push(@values, $quotation);
131 my ($null, $split_department_id) = split /--/, $form->{department};
132 my $department_id = $form->{department_id} || $split_department_id;
133 if ($department_id) {
134 $query .= qq| AND o.department_id = ?|;
135 push(@values, $department_id);
138 if ($form->{"project_id"}) {
140 qq|AND ((globalproject_id = ?) OR EXISTS | .
141 qq| (SELECT * FROM orderitems oi | .
142 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
143 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
146 if ($form->{"projectnumber"}) {
148 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
149 SELECT * FROM orderitems oi
150 LEFT JOIN project proi ON proi.id = oi.project_id
151 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
154 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
157 if ($form->{"business_id"}) {
158 $query .= " AND ct.business_id = ?";
159 push(@values, $form->{"business_id"});
162 if ($form->{"${vc}_id"}) {
163 $query .= " AND o.${vc}_id = ?";
164 push(@values, $form->{"${vc}_id"});
166 } elsif ($form->{$vc}) {
167 $query .= " AND ct.name ILIKE ?";
168 push(@values, '%' . $form->{$vc} . '%');
171 if (!$main::auth->assert('sales_all_edit', 1)) {
172 $query .= " AND o.employee_id = (select id from employee where login= ?)";
173 push @values, $form->{login};
175 if ($form->{employee_id}) {
176 $query .= " AND o.employee_id = ?";
177 push @values, conv_i($form->{employee_id});
180 if ($form->{salesman_id}) {
181 $query .= " AND o.salesman_id = ?";
182 push @values, conv_i($form->{salesman_id});
185 if (!$form->{open} && !$form->{closed}) {
186 $query .= " AND o.id = 0";
187 } elsif (!($form->{open} && $form->{closed})) {
188 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
191 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
192 ($form->{"notdelivered"} ne $form->{"delivered"})) {
193 $query .= $form->{"delivered"} ?
194 " AND o.delivered " : " AND NOT o.delivered";
197 if ($form->{$ordnumber}) {
198 $query .= qq| AND o.$ordnumber ILIKE ?|;
199 push(@values, '%' . $form->{$ordnumber} . '%');
202 if ($form->{cusordnumber}) {
203 $query .= qq| AND o.cusordnumber ILIKE ?|;
204 push(@values, '%' . $form->{cusordnumber} . '%');
207 if($form->{transdatefrom}) {
208 $query .= qq| AND o.transdate >= ?|;
209 push(@values, conv_date($form->{transdatefrom}));
212 if($form->{transdateto}) {
213 $query .= qq| AND o.transdate <= ?|;
214 push(@values, conv_date($form->{transdateto}));
217 if($form->{reqdatefrom}) {
218 $query .= qq| AND o.reqdate >= ?|;
219 push(@values, conv_date($form->{reqdatefrom}));
222 if($form->{reqdateto}) {
223 $query .= qq| AND o.reqdate <= ?|;
224 push(@values, conv_date($form->{reqdateto}));
227 if ($form->{shippingpoint}) {
228 $query .= qq| AND o.shippingpoint ILIKE ?|;
229 push(@values, '%' . $form->{shippingpoint} . '%');
232 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
233 $query .= qq| AND tz.id = ?|;
234 push(@values, $form->{taxzone_id});
237 if ($form->{transaction_description}) {
238 $query .= qq| AND o.transaction_description ILIKE ?|;
239 push(@values, '%' . $form->{transaction_description} . '%');
242 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
243 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
244 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
247 if ($form->{reqdate_unset_or_old}) {
248 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
251 if (($form->{order_probability_value} || '') ne '') {
252 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
253 $query .= qq| AND (o.order_probability ${op} ?)|;
254 push @values, $form->{order_probability_value};
257 if ($form->{expected_billing_date_from}) {
258 $query .= qq| AND (o.expected_billing_date >= ?)|;
259 push @values, conv_date($form->{expected_billing_date_from});
262 if ($form->{expected_billing_date_to}) {
263 $query .= qq| AND (o.expected_billing_date <= ?)|;
264 push @values, conv_date($form->{expected_billing_date_to});
267 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
268 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
269 my %allowed_sort_columns = (
270 "transdate" => "o.transdate",
271 "reqdate" => "o.reqdate",
273 "ordnumber" => "o.ordnumber",
274 "cusordnumber" => "o.cusordnumber",
275 "quonumber" => "o.quonumber",
277 "employee" => "e.name",
278 "salesman" => "s.name",
279 "shipvia" => "o.shipvia",
280 "transaction_description" => "o.transaction_description",
281 "shippingpoint" => "o.shippingpoint",
282 "taxzone" => "tz.description",
284 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
285 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
287 $query .= qq| ORDER by | . $sortorder;
289 my $sth = $dbh->prepare($query);
290 $sth->execute(@values) ||
291 $form->dberror($query . " (" . join(", ", @values) . ")");
295 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
296 $ref->{billed_amount} = $billed_amount{$ref->{id}};
297 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
298 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
299 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
300 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
301 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
302 $id{ $ref->{id} } = $ref->{id};
307 $main::lxdebug->leave_sub();
310 sub transactions_for_todo_list {
311 $main::lxdebug->enter_sub();
316 my $myconfig = \%main::myconfig;
317 my $form = $main::form;
319 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
321 my $query = qq|SELECT id FROM employee WHERE login = ?|;
322 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
325 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
326 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
331 LEFT JOIN customer c ON (oe.customer_id = c.id)
332 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
333 LEFT JOIN employee e ON (oe.employee_id = e.id)
334 WHERE (COALESCE(quotation, FALSE) = TRUE)
335 AND (COALESCE(closed, FALSE) = FALSE)
336 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
337 AND NOT (oe.reqdate ISNULL)
338 AND (oe.reqdate < current_date)
341 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
343 $main::lxdebug->leave_sub();
349 $main::lxdebug->enter_sub();
351 my ($self, $myconfig, $form) = @_;
353 # connect to database, turn off autocommit
354 my $dbh = $form->get_standard_dbh;
355 my $restricter = SL::HTML::Restrict->create;
357 my ($query, @values, $sth, $null);
358 my $exchangerate = 0;
360 my $all_units = AM->retrieve_units($myconfig, $form);
361 $form->{all_units} = $all_units;
363 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
366 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
367 unless ($form->{employee_id}) {
368 $form->get_employee($dbh);
371 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
373 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
374 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
375 $form->{$number_field} ||= $trans_number->create_unique;
378 $query = qq|DELETE FROM custom_variables
379 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
380 AND (sub_module = 'orderitems')
381 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
382 do_query($form, $dbh, $query, $form->{id});
384 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
385 do_query($form, $dbh, $query, $form->{id});
387 $query = qq|DELETE FROM shipto | .
388 qq|WHERE trans_id = ? AND module = 'OE'|;
389 do_query($form, $dbh, $query, $form->{id});
393 $query = qq|SELECT nextval('id')|;
394 ($form->{id}) = selectrow_query($form, $dbh, $query);
396 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
397 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
415 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
416 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
419 for my $i (1 .. $form->{rowcount}) {
421 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
423 if ($form->{"id_$i"}) {
426 $query = qq|SELECT unit FROM parts WHERE id = ?|;
427 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
430 if (defined($all_units->{$item_unit}->{factor}) &&
431 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
432 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
434 my $baseqty = $form->{"qty_$i"} * $basefactor;
436 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
437 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
439 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
441 # keep entered selling price
443 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
445 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
447 my $decimalplaces = ($dec > 2) ? $dec : 2;
449 # undo discount formatting
450 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
453 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
455 # round linetotal at least to 2 decimal places
456 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
457 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
459 $form->{"inventory_accno_$i"} *= 1;
460 $form->{"expense_accno_$i"} *= 1;
462 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
466 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
468 if ($form->{taxincluded}) {
469 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
470 $taxbase = $linetotal - $taxamount;
472 # we are not keeping a natural price, do not round
473 $form->{"sellprice_$i"} =
474 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
476 $taxamount = $linetotal * $taxrate;
477 $taxbase = $linetotal;
480 if ($form->round_amount($taxrate, 7) == 0) {
481 if ($form->{taxincluded}) {
482 foreach my $item (@taxaccounts) {
483 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
484 $taxaccounts{$item} += $taxamount;
485 $taxdiff += $taxamount;
486 $taxbase{$item} += $taxbase;
488 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
490 foreach my $item (@taxaccounts) {
491 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
492 $taxbase{$item} += $taxbase;
496 foreach my $item (@taxaccounts) {
497 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
498 $taxbase{$item} += $taxbase;
502 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
504 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
506 # Get pricegroup_id and save it. Unfortunately the interface
507 # also uses ID "0" for signalling that none is selected, but "0"
508 # must not be stored in the database. Therefore we cannot simply
510 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
512 $pricegroup_id = undef if !$pricegroup_id;
514 # save detail record in orderitems table
515 my $orderitems_id = $form->{"orderitems_id_$i"};
516 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
519 $query = qq|INSERT INTO orderitems (
520 id, trans_id, parts_id, description, longdescription, qty, base_qty,
521 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
522 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
523 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
524 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
525 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
527 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
528 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
529 $form->{"qty_$i"}, $baseqty,
530 $fxsellprice, $form->{"discount_$i"},
531 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
532 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
533 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
534 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
535 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
536 $form->{"lastcost_$i"},
537 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
538 conv_i($form->{"marge_price_factor_$i"}));
539 do_query($form, $dbh, $query, @values);
541 $form->{"sellprice_$i"} = $fxsellprice;
542 $form->{"discount_$i"} *= 100;
544 CVar->save_custom_variables(module => 'IC',
545 sub_module => 'orderitems',
546 trans_id => $orderitems_id,
547 configs => $ic_cvar_configs,
549 name_prefix => 'ic_',
550 name_postfix => "_$i",
555 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
559 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
561 $amount = $form->round_amount($netamount + $tax, 2);
562 $netamount = $form->round_amount($netamount, 2);
564 if ($form->{currency} eq $form->{defaultcurrency}) {
565 $form->{exchangerate} = 1;
567 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
570 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
572 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
574 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
579 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
580 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
581 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
582 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
583 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
584 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
585 , order_probability = ?, expected_billing_date = ?
588 @values = ($form->{ordnumber} || '', $form->{quonumber},
589 $form->{cusordnumber}, conv_date($form->{transdate}),
590 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
591 $amount, $netamount, conv_date($reqdate),
592 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
593 $form->{shipvia}, $form->{notes}, $form->{intnotes},
594 $form->{currency}, $form->{closed} ? 't' : 'f',
595 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
596 $quotation, conv_i($form->{department_id}),
597 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
598 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
599 conv_i($form->{delivery_vendor_id}),
600 conv_i($form->{delivery_customer_id}),
601 conv_i($form->{delivery_term_id}),
602 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
603 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
604 $form->{transaction_description},
605 $form->{marge_total} * 1, $form->{marge_percent} * 1,
606 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
607 conv_i($form->{id}));
608 do_query($form, $dbh, $query, @values);
610 $form->{ordtotal} = $amount;
612 $form->{name} = $form->{ $form->{vc} };
613 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
616 if (!$form->{shipto_id}) {
617 $form->add_shipto($dbh, $form->{id}, "OE");
620 # save printed, emailed, queued
621 $form->save_status($dbh);
623 # Link this record to the records it was created from.
624 $form->{convert_from_oe_ids} =~ s/^\s+//;
625 $form->{convert_from_oe_ids} =~ s/\s+$//;
626 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
627 delete $form->{convert_from_oe_ids};
629 if (scalar @convert_from_oe_ids) {
630 RecordLinks->create_links('dbh' => $dbh,
632 'from_table' => 'oe',
633 'from_ids' => \@convert_from_oe_ids,
635 'to_id' => $form->{id},
638 $self->_close_quotations_rfqs('dbh' => $dbh,
639 'from_id' => \@convert_from_oe_ids,
640 'to_id' => $form->{id});
643 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
644 if ($form->{vc} eq 'customer') {
645 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
647 if ($form->{vc} eq 'vendor') {
648 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
652 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
653 "quonumber" : "ordnumber"};
655 Common::webdav_folder($form);
657 my $rc = $dbh->commit;
659 $self->save_periodic_invoices_config(dbh => $dbh,
660 oe_id => $form->{id},
661 config_yaml => $form->{periodic_invoices_config})
662 if ($form->{type} eq 'sales_order');
664 $main::lxdebug->leave_sub();
669 sub save_periodic_invoices_config {
670 my ($self, %params) = @_;
672 return if !$params{oe_id};
674 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
675 return if 'HASH' ne ref $config;
677 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
678 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
679 $obj->update_attributes(%{ $config });
682 sub load_periodic_invoice_config {
686 delete $form->{periodic_invoices_config};
689 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
692 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
693 print printer_id copies) };
694 $form->{periodic_invoices_config} = YAML::Dump($config);
699 sub _close_quotations_rfqs {
700 $main::lxdebug->enter_sub();
705 Common::check_params(\%params, qw(from_id to_id));
707 my $myconfig = \%main::myconfig;
708 my $form = $main::form;
710 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
712 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
713 my $sth = prepare_query($form, $dbh, $query);
715 do_statement($form, $sth, $query, conv_i($params{to_id}));
717 my ($quotation) = $sth->fetchrow_array();
720 $main::lxdebug->leave_sub();
726 foreach my $from_id (@{ $params{from_id} }) {
727 $from_id = conv_i($from_id);
728 do_statement($form, $sth, $query, $from_id);
729 ($quotation) = $sth->fetchrow_array();
730 push @close_ids, $from_id if ($quotation);
735 if (scalar @close_ids) {
736 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
737 do_query($form, $dbh, $query, @close_ids);
739 $dbh->commit() unless ($params{dbh});
742 $main::lxdebug->leave_sub();
746 $main::lxdebug->enter_sub();
748 my ($self, $myconfig, $form) = @_;
750 my $rc = SL::DB::Order->new->db->with_transaction(sub {
751 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
753 SL::DB::Order->new(id => $form->{id})->delete;
755 my $spool = $::lx_office_conf{paths}->{spool};
756 unlink map { "$spool/$_" } @spoolfiles if $spool;
761 $main::lxdebug->leave_sub();
767 $main::lxdebug->enter_sub();
769 my ($self, $myconfig, $form) = @_;
771 # connect to database
772 my $dbh = $form->get_standard_dbh;
774 my ($query, $query_add, @values, @ids, $sth);
776 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
778 push @ids, $form->{"trans_id_$_"}
779 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
780 } (1 .. $form->{"rowcount"});
782 if ($form->{rowcount} && scalar @ids) {
783 $form->{convert_from_oe_ids} = join ' ', @ids;
786 # if called in multi id mode, and still only got one id, switch back to single id
787 if ($form->{"rowcount"} and $#ids == 0) {
788 $form->{"id"} = $ids[0];
792 # and remember for the rest of the function
793 my $is_collective_order = scalar @ids;
796 my $wday = (localtime(time))[6];
797 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
799 # if we have a client configured interval for sales quotation, we add this
800 $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
801 $form->{type} eq 'sales_quotation' );
803 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
806 # get default accounts
807 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
808 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
809 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
810 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
811 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
814 my $ref = selectfirst_hashref_query($form, $dbh, $query);
815 map { $form->{$_} = $ref->{$_} } keys %$ref;
817 $form->{currency} = $form->get_default_currency($myconfig);
819 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
820 # we come from invoices, feel free.
821 $form->{reqdate} = $form->{deliverydate}
822 if ( $form->{deliverydate}
823 and $form->{callback} =~ /action=ar_transactions/);
825 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
827 if ($form->{id} or @ids) {
829 # retrieve order for single id
830 # NOTE: this query is intended to fetch all information only ONCE.
831 # so if any of these infos is important (or even different) for any item,
832 # it will be killed out and then has to be fetched from the item scope query further down
834 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
835 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
836 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
837 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
838 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
839 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
840 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
841 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
842 , o.order_probability, o.expected_billing_date
844 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
845 LEFT JOIN employee e ON (o.employee_id = e.id)
846 LEFT JOIN department d ON (o.department_id = d.id) | .
849 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
851 @values = $form->{id} ? ($form->{id}) : @ids;
852 $sth = prepare_execute_query($form, $dbh, $query, @values);
854 $ref = $sth->fetchrow_hashref("NAME_lc");
857 map { $form->{$_} = $ref->{$_} } keys %$ref;
859 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
861 # set all entries for multiple ids blank that yield different information
862 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
863 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
867 # if not given, fill transdate with current_date
868 $form->{transdate} = $form->current_date($myconfig)
869 unless $form->{transdate};
873 if ($form->{delivery_customer_id}) {
874 $query = qq|SELECT name FROM customer WHERE id = ?|;
875 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
878 if ($form->{delivery_vendor_id}) {
879 $query = qq|SELECT name FROM customer WHERE id = ?|;
880 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
883 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
885 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
886 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
888 $ref = $sth->fetchrow_hashref("NAME_lc");
890 map { $form->{$_} = $ref->{$_} } keys %$ref;
893 # get printed, emailed and queued
894 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
895 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
897 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
898 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
899 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
900 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
903 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
906 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
908 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
910 # retrieve individual items
911 # this query looks up all information about the items
912 # stuff different from the whole will not be overwritten, but saved with a suffix.
914 qq|SELECT o.id AS orderitems_id,
915 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
916 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
917 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
918 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
919 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
920 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
921 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
922 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
923 o.price_factor_id, o.price_factor, o.marge_price_factor,
924 pr.projectnumber, p.formel,
925 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
927 JOIN parts p ON (o.parts_id = p.id)
928 JOIN oe ON (o.trans_id = oe.id)
929 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
930 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)
931 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)
932 LEFT JOIN project pr ON (o.project_id = pr.id)
933 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
935 ? qq|WHERE o.trans_id = ?|
936 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
939 @ids = $form->{id} ? ($form->{id}) : @ids;
940 $sth = prepare_execute_query($form, $dbh, $query, @values);
942 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
943 # Retrieve custom variables.
944 my $cvars = CVar->get_custom_variables(dbh => $dbh,
946 sub_module => 'orderitems',
947 trans_id => $ref->{orderitems_id},
949 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
952 if (!$ref->{"part_inventory_accno_id"}) {
953 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
955 delete($ref->{"part_inventory_accno_id"});
957 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
958 # unless already present there
959 # remove _oe entries afterwards
960 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
961 qw|ordnumber transdate cusordnumber|
963 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
967 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
969 qq|SELECT accno AS inventory_accno, | .
970 qq| new_chart_id AS inventory_new_chart, | .
971 qq| date($transdate) - valid_from AS inventory_valid | .
972 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
973 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
974 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
977 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
979 qq|SELECT accno AS income_accno, | .
980 qq| new_chart_id AS income_new_chart, | .
981 qq| date($transdate) - valid_from AS income_valid | .
982 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
983 ($ref->{income_accno}, $ref->{income_new_chart},
984 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
987 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
989 qq|SELECT accno AS expense_accno, | .
990 qq| new_chart_id AS expense_new_chart, | .
991 qq| date($transdate) - valid_from AS expense_valid | .
992 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
993 ($ref->{expense_accno}, $ref->{expense_new_chart},
994 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
997 # delete orderitems_id in collective orders, so that they get cloned no matter what
998 delete $ref->{orderitems_id} if (@ids);
1000 # get tax rates and description
1001 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1003 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1004 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1005 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1006 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1007 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1008 qq|ORDER BY c.accno|;
1009 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1010 $ref->{taxaccounts} = "";
1012 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1013 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1017 $ref->{taxaccounts} .= "$ptr->{accno} ";
1018 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1019 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1020 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1021 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1022 $form->{taxaccounts} .= "$ptr->{accno} ";
1027 chop $ref->{taxaccounts};
1029 push @{ $form->{form_details} }, $ref;
1036 # get last name used
1037 $form->lastname_used($dbh, $myconfig, $form->{vc})
1038 unless $form->{"$form->{vc}_id"};
1042 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1044 Common::webdav_folder($form);
1046 $self->load_periodic_invoice_config($form);
1048 my $rc = $dbh->commit;
1050 $main::lxdebug->leave_sub();
1055 sub retrieve_simple {
1056 $main::lxdebug->enter_sub();
1061 Common::check_params(\%params, qw(id));
1063 my $myconfig = \%main::myconfig;
1064 my $form = $main::form;
1066 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1068 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1069 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1071 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1072 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1074 $main::lxdebug->leave_sub();
1080 $main::lxdebug->enter_sub();
1082 my ($self, $myconfig, $form) = @_;
1084 # connect to database
1085 my $dbh = $form->get_standard_dbh;
1091 my $nodiscount_subtotal = 0;
1092 my $discount_subtotal = 0;
1095 my @partsgroup = ();
1098 my $subtotal_header = 0;
1099 my $subposition = 0;
1107 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1109 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1110 'departments' => 'ALL_DEPARTMENTS');
1113 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1114 $price_factors{$pfac->{id}} = $pfac;
1115 $pfac->{factor} *= 1;
1116 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1120 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1121 next unless $dept->{id} eq $form->{department_id};
1122 $form->{department} = $dept->{description};
1126 # sort items by partsgroup
1127 for $i (1 .. $form->{rowcount}) {
1129 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1130 $partsgroup = $form->{"partsgroup_$i"};
1132 push @partsgroup, [$i, $partsgroup];
1133 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1139 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1140 %projects_by_id = map { $_->id => $_ } @$projects;
1143 if ($projects_by_id{$form->{"globalproject_id"}}) {
1144 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1145 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1147 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1148 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1152 $form->{discount} = [];
1154 $form->{TEMPLATE_ARRAYS} = { };
1155 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1157 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1158 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1161 qw(runningnumber number description longdescription qty ship unit bin
1162 partnotes serialnumber reqdate sellprice listprice netprice
1163 discount p_discount discount_sub nodiscount_sub
1164 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1165 price_factor price_factor_name partsgroup weight lineweight);
1167 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1168 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1170 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1172 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1174 my $totalweight = 0;
1176 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1179 if ($item->[1] ne $sameitem) {
1180 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1181 $sameitem = $item->[1];
1183 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1186 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1188 if ($form->{"id_$i"} != 0) {
1190 # add number, description and qty to $form->{number}, ....
1192 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1193 $subtotal_header = $i;
1194 $position = int($position);
1197 } elsif ($subtotal_header) {
1199 $position = int($position);
1200 $position = $position.".".$subposition;
1202 $position = int($position);
1206 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1208 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1209 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1210 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1211 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1212 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1213 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1214 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1215 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1216 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1217 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1218 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1219 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1220 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1221 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1222 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1223 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1224 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1225 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1226 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1228 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1229 my ($dec) = ($sellprice =~ /\.(\d+)/);
1230 my $decimalplaces = max 2, length($dec);
1232 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1234 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1235 my $linetotal = $form->round_amount($linetotal_exact, 2);
1237 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1238 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1240 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1242 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1244 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1246 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1247 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1249 $linetotal = ($linetotal != 0) ? $linetotal : '';
1251 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1252 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1253 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1255 $form->{ordtotal} += $linetotal;
1256 $form->{nodiscount_total} += $nodiscount_linetotal;
1257 $form->{discount_total} += $discount;
1259 if ($subtotal_header) {
1260 $discount_subtotal += $linetotal;
1261 $nodiscount_subtotal += $nodiscount_linetotal;
1264 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1265 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1266 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1267 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1268 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1270 $discount_subtotal = 0;
1271 $nodiscount_subtotal = 0;
1272 $subtotal_header = 0;
1275 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1278 if (!$form->{"discount_$i"}) {
1279 $nodiscount += $linetotal;
1282 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1284 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1285 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1286 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1287 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1288 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1289 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1291 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1292 $totalweight += $lineweight;
1293 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1294 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1295 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1296 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1298 my ($taxamount, $taxbase);
1301 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1303 if ($form->{taxincluded}) {
1306 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1307 $taxbase = $linetotal / (1 + $taxrate);
1309 $taxamount = $linetotal * $taxrate;
1310 $taxbase = $linetotal;
1313 if ($taxamount != 0) {
1314 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1315 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1316 $taxbase{$accno} += $taxbase;
1320 $tax_rate = $taxrate * 100;
1321 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1323 if ($form->{"assembly_$i"}) {
1326 # get parts and push them onto the stack
1328 if ($form->{groupitems}) {
1329 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1331 $sortorder = qq|ORDER BY a.oid|;
1334 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1335 qq|pg.partsgroup | .
1336 qq|FROM assembly a | .
1337 qq| JOIN parts p ON (a.parts_id = p.id) | .
1338 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1339 qq| WHERE a.bom = '1' | .
1340 qq| AND a.id = ? | . $sortorder;
1341 @values = ($form->{"id_$i"});
1342 $sth = $dbh->prepare($query);
1343 $sth->execute(@values) || $form->dberror($query);
1345 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1346 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1347 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1348 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1349 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1352 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1353 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1358 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1359 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1360 for @{ $ic_cvar_configs };
1362 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1366 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1367 $form->{totalweight_nofmt} = $totalweight;
1368 my $defaults = AM->get_defaults();
1369 $form->{weightunit} = $defaults->{weightunit};
1372 foreach $item (sort keys %taxaccounts) {
1373 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1375 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1377 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1378 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1379 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1380 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1381 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1383 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1384 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1385 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1388 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1389 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1390 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1391 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1393 if($form->{taxincluded}) {
1394 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1395 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1397 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1398 $form->{subtotal_nofmt} = $form->{ordtotal};
1401 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1404 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1406 if ($form->{type} =~ /_quotation/) {
1407 $form->set_payment_options($myconfig, $form->{quodate});
1409 $form->set_payment_options($myconfig, $form->{orddate});
1412 $form->{username} = $myconfig->{name};
1416 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1417 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1419 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1421 $main::lxdebug->leave_sub();
1424 sub project_description {
1425 $main::lxdebug->enter_sub();
1427 my ($self, $dbh, $id) = @_;
1429 my $query = qq|SELECT description FROM project WHERE id = ?|;
1430 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1432 $main::lxdebug->leave_sub();
1443 OE.pm - Order entry module
1447 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>.
1453 =item retrieve_simple PARAMS
1455 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1457 my $order = retrieve_simple(id => 2);