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) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
397 do_query($form, $dbh, $query, $form->{id}, $form->{employee_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;
798 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
801 # get default accounts
802 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
803 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
804 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
805 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
806 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
809 my $ref = selectfirst_hashref_query($form, $dbh, $query);
810 map { $form->{$_} = $ref->{$_} } keys %$ref;
812 $form->{currency} = $form->get_default_currency($myconfig);
814 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
815 # we come from invoices, feel free.
816 $form->{reqdate} = $form->{deliverydate}
817 if ( $form->{deliverydate}
818 and $form->{callback} =~ /action=ar_transactions/);
820 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
822 if ($form->{id} or @ids) {
824 # retrieve order for single id
825 # NOTE: this query is intended to fetch all information only ONCE.
826 # so if any of these infos is important (or even different) for any item,
827 # it will be killed out and then has to be fetched from the item scope query further down
829 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
830 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
831 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
832 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
833 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
834 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
835 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
836 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
837 , o.order_probability, o.expected_billing_date
839 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
840 LEFT JOIN employee e ON (o.employee_id = e.id)
841 LEFT JOIN department d ON (o.department_id = d.id) | .
844 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
846 @values = $form->{id} ? ($form->{id}) : @ids;
847 $sth = prepare_execute_query($form, $dbh, $query, @values);
849 $ref = $sth->fetchrow_hashref("NAME_lc");
852 map { $form->{$_} = $ref->{$_} } keys %$ref;
854 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
856 # set all entries for multiple ids blank that yield different information
857 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
858 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
862 # if not given, fill transdate with current_date
863 $form->{transdate} = $form->current_date($myconfig)
864 unless $form->{transdate};
868 if ($form->{delivery_customer_id}) {
869 $query = qq|SELECT name FROM customer WHERE id = ?|;
870 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
873 if ($form->{delivery_vendor_id}) {
874 $query = qq|SELECT name FROM customer WHERE id = ?|;
875 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
878 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
880 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
881 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
883 $ref = $sth->fetchrow_hashref("NAME_lc");
885 map { $form->{$_} = $ref->{$_} } keys %$ref;
888 # get printed, emailed and queued
889 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
890 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
892 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
893 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
894 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
895 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
898 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
901 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
903 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
905 # retrieve individual items
906 # this query looks up all information about the items
907 # stuff different from the whole will not be overwritten, but saved with a suffix.
909 qq|SELECT o.id AS orderitems_id,
910 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
911 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
912 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
913 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
914 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
915 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
916 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
917 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
918 o.price_factor_id, o.price_factor, o.marge_price_factor,
919 pr.projectnumber, p.formel,
920 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
922 JOIN parts p ON (o.parts_id = p.id)
923 JOIN oe ON (o.trans_id = oe.id)
924 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
925 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
926 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
927 LEFT JOIN project pr ON (o.project_id = pr.id)
928 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
930 ? qq|WHERE o.trans_id = ?|
931 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
934 @ids = $form->{id} ? ($form->{id}) : @ids;
935 $sth = prepare_execute_query($form, $dbh, $query, @values);
937 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
938 # Retrieve custom variables.
939 my $cvars = CVar->get_custom_variables(dbh => $dbh,
941 sub_module => 'orderitems',
942 trans_id => $ref->{orderitems_id},
944 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
947 if (!$ref->{"part_inventory_accno_id"}) {
948 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
950 delete($ref->{"part_inventory_accno_id"});
952 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
953 # unless already present there
954 # remove _oe entries afterwards
955 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
956 qw|ordnumber transdate cusordnumber|
958 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
962 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
964 qq|SELECT accno AS inventory_accno, | .
965 qq| new_chart_id AS inventory_new_chart, | .
966 qq| date($transdate) - valid_from AS inventory_valid | .
967 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
968 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
969 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
972 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
974 qq|SELECT accno AS income_accno, | .
975 qq| new_chart_id AS income_new_chart, | .
976 qq| date($transdate) - valid_from AS income_valid | .
977 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
978 ($ref->{income_accno}, $ref->{income_new_chart},
979 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
982 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
984 qq|SELECT accno AS expense_accno, | .
985 qq| new_chart_id AS expense_new_chart, | .
986 qq| date($transdate) - valid_from AS expense_valid | .
987 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
988 ($ref->{expense_accno}, $ref->{expense_new_chart},
989 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
992 # delete orderitems_id in collective orders, so that they get cloned no matter what
993 delete $ref->{orderitems_id} if (@ids);
995 # get tax rates and description
996 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
998 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
999 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1000 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1001 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1002 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1003 qq|ORDER BY c.accno|;
1004 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1005 $ref->{taxaccounts} = "";
1007 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1008 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1012 $ref->{taxaccounts} .= "$ptr->{accno} ";
1013 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1014 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1015 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1016 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1017 $form->{taxaccounts} .= "$ptr->{accno} ";
1022 chop $ref->{taxaccounts};
1024 push @{ $form->{form_details} }, $ref;
1031 # get last name used
1032 $form->lastname_used($dbh, $myconfig, $form->{vc})
1033 unless $form->{"$form->{vc}_id"};
1037 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1039 Common::webdav_folder($form);
1041 $self->load_periodic_invoice_config($form);
1043 my $rc = $dbh->commit;
1045 $main::lxdebug->leave_sub();
1050 sub retrieve_simple {
1051 $main::lxdebug->enter_sub();
1056 Common::check_params(\%params, qw(id));
1058 my $myconfig = \%main::myconfig;
1059 my $form = $main::form;
1061 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1063 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1064 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1066 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1067 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1069 $main::lxdebug->leave_sub();
1075 $main::lxdebug->enter_sub();
1077 my ($self, $myconfig, $form) = @_;
1079 # connect to database
1080 my $dbh = $form->get_standard_dbh;
1086 my $nodiscount_subtotal = 0;
1087 my $discount_subtotal = 0;
1090 my @partsgroup = ();
1093 my $subtotal_header = 0;
1094 my $subposition = 0;
1102 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1104 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1105 'departments' => 'ALL_DEPARTMENTS');
1108 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1109 $price_factors{$pfac->{id}} = $pfac;
1110 $pfac->{factor} *= 1;
1111 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1115 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1116 next unless $dept->{id} eq $form->{department_id};
1117 $form->{department} = $dept->{description};
1121 # sort items by partsgroup
1122 for $i (1 .. $form->{rowcount}) {
1124 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1125 $partsgroup = $form->{"partsgroup_$i"};
1127 push @partsgroup, [$i, $partsgroup];
1128 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1134 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1135 %projects_by_id = map { $_->id => $_ } @$projects;
1138 if ($projects_by_id{$form->{"globalproject_id"}}) {
1139 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1140 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1142 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1143 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1147 $form->{discount} = [];
1149 $form->{TEMPLATE_ARRAYS} = { };
1150 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1152 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1153 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1156 qw(runningnumber number description longdescription qty ship unit bin
1157 partnotes serialnumber reqdate sellprice listprice netprice
1158 discount p_discount discount_sub nodiscount_sub
1159 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1160 price_factor price_factor_name partsgroup weight lineweight);
1162 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1163 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1165 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1167 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1169 my $totalweight = 0;
1171 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1174 if ($item->[1] ne $sameitem) {
1175 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1176 $sameitem = $item->[1];
1178 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1181 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1183 if ($form->{"id_$i"} != 0) {
1185 # add number, description and qty to $form->{number}, ....
1187 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1188 $subtotal_header = $i;
1189 $position = int($position);
1192 } elsif ($subtotal_header) {
1194 $position = int($position);
1195 $position = $position.".".$subposition;
1197 $position = int($position);
1201 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1203 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1204 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1205 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1206 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1207 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1208 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1209 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1210 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1211 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1212 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1213 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1214 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1215 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1216 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1217 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1218 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1219 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1220 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1221 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1223 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1224 my ($dec) = ($sellprice =~ /\.(\d+)/);
1225 my $decimalplaces = max 2, length($dec);
1227 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1229 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1230 my $linetotal = $form->round_amount($linetotal_exact, 2);
1232 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1233 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1235 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1237 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1239 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1241 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1242 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1244 $linetotal = ($linetotal != 0) ? $linetotal : '';
1246 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1247 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1248 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1250 $form->{ordtotal} += $linetotal;
1251 $form->{nodiscount_total} += $nodiscount_linetotal;
1252 $form->{discount_total} += $discount;
1254 if ($subtotal_header) {
1255 $discount_subtotal += $linetotal;
1256 $nodiscount_subtotal += $nodiscount_linetotal;
1259 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1260 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1261 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1262 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1263 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1265 $discount_subtotal = 0;
1266 $nodiscount_subtotal = 0;
1267 $subtotal_header = 0;
1270 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1273 if (!$form->{"discount_$i"}) {
1274 $nodiscount += $linetotal;
1277 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1279 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1280 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1281 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1282 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1283 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1284 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1286 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1287 $totalweight += $lineweight;
1288 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1289 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1290 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1291 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1293 my ($taxamount, $taxbase);
1296 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1298 if ($form->{taxincluded}) {
1301 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1302 $taxbase = $linetotal / (1 + $taxrate);
1304 $taxamount = $linetotal * $taxrate;
1305 $taxbase = $linetotal;
1308 if ($taxamount != 0) {
1309 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1310 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1311 $taxbase{$accno} += $taxbase;
1315 $tax_rate = $taxrate * 100;
1316 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1318 if ($form->{"assembly_$i"}) {
1321 # get parts and push them onto the stack
1323 if ($form->{groupitems}) {
1324 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1326 $sortorder = qq|ORDER BY a.oid|;
1329 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1330 qq|pg.partsgroup | .
1331 qq|FROM assembly a | .
1332 qq| JOIN parts p ON (a.parts_id = p.id) | .
1333 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1334 qq| WHERE a.bom = '1' | .
1335 qq| AND a.id = ? | . $sortorder;
1336 @values = ($form->{"id_$i"});
1337 $sth = $dbh->prepare($query);
1338 $sth->execute(@values) || $form->dberror($query);
1340 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1341 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1342 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1343 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1344 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1347 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1348 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1353 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1354 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1355 for @{ $ic_cvar_configs };
1357 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1361 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1362 $form->{totalweight_nofmt} = $totalweight;
1363 my $defaults = AM->get_defaults();
1364 $form->{weightunit} = $defaults->{weightunit};
1367 foreach $item (sort keys %taxaccounts) {
1368 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1370 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1371 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1372 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1373 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1374 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1375 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1376 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1378 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1379 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1380 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1383 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1384 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1385 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1386 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1388 if($form->{taxincluded}) {
1389 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1390 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1392 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1393 $form->{subtotal_nofmt} = $form->{ordtotal};
1396 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1399 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1401 if ($form->{type} =~ /_quotation/) {
1402 $form->set_payment_options($myconfig, $form->{quodate});
1404 $form->set_payment_options($myconfig, $form->{orddate});
1407 $form->{username} = $myconfig->{name};
1411 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1412 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1414 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1416 $main::lxdebug->leave_sub();
1419 sub project_description {
1420 $main::lxdebug->enter_sub();
1422 my ($self, $dbh, $id) = @_;
1424 my $query = qq|SELECT description FROM project WHERE id = ?|;
1425 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1427 $main::lxdebug->leave_sub();
1438 OE.pm - Order entry module
1442 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>.
1448 =item retrieve_simple PARAMS
1450 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1452 my $order = retrieve_simple(id => 2);