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 .
119 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
120 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
121 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
122 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
123 qq| AND ex.transdate = o.transdate) | .
124 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
125 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
126 qq|$periodic_invoices_joins | .
127 qq|WHERE (o.quotation = ?) |;
128 push(@values, $quotation);
130 my ($null, $split_department_id) = split /--/, $form->{department};
131 my $department_id = $form->{department_id} || $split_department_id;
132 if ($department_id) {
133 $query .= qq| AND o.department_id = ?|;
134 push(@values, $department_id);
137 if ($form->{"project_id"}) {
139 qq|AND ((globalproject_id = ?) OR EXISTS | .
140 qq| (SELECT * FROM orderitems oi | .
141 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
142 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
145 if ($form->{"projectnumber"}) {
147 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
148 SELECT * FROM orderitems oi
149 LEFT JOIN project proi ON proi.id = oi.project_id
150 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
153 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
156 if ($form->{"business_id"}) {
157 $query .= " AND ct.business_id = ?";
158 push(@values, $form->{"business_id"});
161 if ($form->{"${vc}_id"}) {
162 $query .= " AND o.${vc}_id = ?";
163 push(@values, $form->{"${vc}_id"});
165 } elsif ($form->{$vc}) {
166 $query .= " AND ct.name ILIKE ?";
167 push(@values, '%' . $form->{$vc} . '%');
170 if (!$main::auth->assert('sales_all_edit', 1)) {
171 $query .= " AND o.employee_id = (select id from employee where login= ?)";
172 push @values, $form->{login};
174 if ($form->{employee_id}) {
175 $query .= " AND o.employee_id = ?";
176 push @values, conv_i($form->{employee_id});
179 if ($form->{salesman_id}) {
180 $query .= " AND o.salesman_id = ?";
181 push @values, conv_i($form->{salesman_id});
184 if (!$form->{open} && !$form->{closed}) {
185 $query .= " AND o.id = 0";
186 } elsif (!($form->{open} && $form->{closed})) {
187 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
190 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
191 ($form->{"notdelivered"} ne $form->{"delivered"})) {
192 $query .= $form->{"delivered"} ?
193 " AND o.delivered " : " AND NOT o.delivered";
196 if ($form->{$ordnumber}) {
197 $query .= qq| AND o.$ordnumber ILIKE ?|;
198 push(@values, '%' . $form->{$ordnumber} . '%');
201 if ($form->{cusordnumber}) {
202 $query .= qq| AND o.cusordnumber ILIKE ?|;
203 push(@values, '%' . $form->{cusordnumber} . '%');
206 if($form->{transdatefrom}) {
207 $query .= qq| AND o.transdate >= ?|;
208 push(@values, conv_date($form->{transdatefrom}));
211 if($form->{transdateto}) {
212 $query .= qq| AND o.transdate <= ?|;
213 push(@values, conv_date($form->{transdateto}));
216 if($form->{reqdatefrom}) {
217 $query .= qq| AND o.reqdate >= ?|;
218 push(@values, conv_date($form->{reqdatefrom}));
221 if($form->{reqdateto}) {
222 $query .= qq| AND o.reqdate <= ?|;
223 push(@values, conv_date($form->{reqdateto}));
226 if ($form->{shippingpoint}) {
227 $query .= qq| AND o.shippingpoint ILIKE ?|;
228 push(@values, '%' . $form->{shippingpoint} . '%');
231 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
232 $query .= qq| AND tz.id = ?|;
233 push(@values, $form->{taxzone_id});
236 if ($form->{transaction_description}) {
237 $query .= qq| AND o.transaction_description ILIKE ?|;
238 push(@values, '%' . $form->{transaction_description} . '%');
241 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
242 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
243 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
246 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
247 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
248 my %allowed_sort_columns = (
249 "transdate" => "o.transdate",
250 "reqdate" => "o.reqdate",
252 "ordnumber" => "o.ordnumber",
253 "cusordnumber" => "o.cusordnumber",
254 "quonumber" => "o.quonumber",
256 "employee" => "e.name",
257 "salesman" => "s.name",
258 "shipvia" => "o.shipvia",
259 "transaction_description" => "o.transaction_description",
260 "shippingpoint" => "o.shippingpoint",
261 "taxzone" => "tz.description",
263 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
264 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
266 $query .= qq| ORDER by | . $sortorder;
268 my $sth = $dbh->prepare($query);
269 $sth->execute(@values) ||
270 $form->dberror($query . " (" . join(", ", @values) . ")");
274 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
275 $ref->{billed_amount} = $billed_amount{$ref->{id}};
276 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
277 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
278 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
279 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
280 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
281 $id{ $ref->{id} } = $ref->{id};
286 $main::lxdebug->leave_sub();
289 sub transactions_for_todo_list {
290 $main::lxdebug->enter_sub();
295 my $myconfig = \%main::myconfig;
296 my $form = $main::form;
298 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
300 my $query = qq|SELECT id FROM employee WHERE login = ?|;
301 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
304 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
305 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
310 LEFT JOIN customer c ON (oe.customer_id = c.id)
311 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
312 LEFT JOIN employee e ON (oe.employee_id = e.id)
313 WHERE (COALESCE(quotation, FALSE) = TRUE)
314 AND (COALESCE(closed, FALSE) = FALSE)
315 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
316 AND NOT (oe.reqdate ISNULL)
317 AND (oe.reqdate < current_date)
320 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
322 $main::lxdebug->leave_sub();
328 $main::lxdebug->enter_sub();
330 my ($self, $myconfig, $form) = @_;
332 # connect to database, turn off autocommit
333 my $dbh = $form->get_standard_dbh;
334 my $restricter = SL::HTML::Restrict->create;
336 my ($query, @values, $sth, $null);
337 my $exchangerate = 0;
339 my $all_units = AM->retrieve_units($myconfig, $form);
340 $form->{all_units} = $all_units;
342 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
345 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
346 unless ($form->{employee_id}) {
347 $form->get_employee($dbh);
350 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
352 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
353 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
354 $form->{$number_field} ||= $trans_number->create_unique;
357 $query = qq|DELETE FROM custom_variables
358 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
359 AND (sub_module = 'orderitems')
360 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
361 do_query($form, $dbh, $query, $form->{id});
363 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
364 do_query($form, $dbh, $query, $form->{id});
366 $query = qq|DELETE FROM shipto | .
367 qq|WHERE trans_id = ? AND module = 'OE'|;
368 do_query($form, $dbh, $query, $form->{id});
372 $query = qq|SELECT nextval('id')|;
373 ($form->{id}) = selectrow_query($form, $dbh, $query);
375 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
376 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
394 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
395 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
398 for my $i (1 .. $form->{rowcount}) {
400 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
402 if ($form->{"id_$i"}) {
405 $query = qq|SELECT unit FROM parts WHERE id = ?|;
406 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
409 if (defined($all_units->{$item_unit}->{factor}) &&
410 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
411 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
413 my $baseqty = $form->{"qty_$i"} * $basefactor;
415 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
416 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
418 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
420 # keep entered selling price
422 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
424 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
426 my $decimalplaces = ($dec > 2) ? $dec : 2;
428 # undo discount formatting
429 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
432 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
434 # round linetotal at least to 2 decimal places
435 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
436 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
438 $form->{"inventory_accno_$i"} *= 1;
439 $form->{"expense_accno_$i"} *= 1;
441 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
445 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
447 if ($form->{taxincluded}) {
448 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
449 $taxbase = $linetotal - $taxamount;
451 # we are not keeping a natural price, do not round
452 $form->{"sellprice_$i"} =
453 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
455 $taxamount = $linetotal * $taxrate;
456 $taxbase = $linetotal;
459 if ($form->round_amount($taxrate, 7) == 0) {
460 if ($form->{taxincluded}) {
461 foreach my $item (@taxaccounts) {
462 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
463 $taxaccounts{$item} += $taxamount;
464 $taxdiff += $taxamount;
465 $taxbase{$item} += $taxbase;
467 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
469 foreach my $item (@taxaccounts) {
470 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
471 $taxbase{$item} += $taxbase;
475 foreach my $item (@taxaccounts) {
476 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
477 $taxbase{$item} += $taxbase;
481 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
483 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
485 # Get pricegroup_id and save it. Unfortunately the interface
486 # also uses ID "0" for signalling that none is selected, but "0"
487 # must not be stored in the database. Therefore we cannot simply
489 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
491 $pricegroup_id = undef if !$pricegroup_id;
493 # save detail record in orderitems table
494 my $orderitems_id = $form->{"orderitems_id_$i"};
495 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
498 $query = qq|INSERT INTO orderitems (
499 id, trans_id, parts_id, description, longdescription, qty, base_qty,
500 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
501 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
502 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
503 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
504 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
506 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
507 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
508 $form->{"qty_$i"}, $baseqty,
509 $fxsellprice, $form->{"discount_$i"},
510 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
511 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
512 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
513 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
514 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
515 $form->{"lastcost_$i"},
516 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
517 conv_i($form->{"marge_price_factor_$i"}));
518 do_query($form, $dbh, $query, @values);
520 $form->{"sellprice_$i"} = $fxsellprice;
521 $form->{"discount_$i"} *= 100;
523 CVar->save_custom_variables(module => 'IC',
524 sub_module => 'orderitems',
525 trans_id => $orderitems_id,
526 configs => $ic_cvar_configs,
528 name_prefix => 'ic_',
529 name_postfix => "_$i",
534 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
538 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
540 $amount = $form->round_amount($netamount + $tax, 2);
541 $netamount = $form->round_amount($netamount, 2);
543 if ($form->{currency} eq $form->{defaultcurrency}) {
544 $form->{exchangerate} = 1;
546 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
549 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
551 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
553 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
558 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
559 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
560 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
561 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
562 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
563 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
566 @values = ($form->{ordnumber} || '', $form->{quonumber},
567 $form->{cusordnumber}, conv_date($form->{transdate}),
568 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
569 $amount, $netamount, conv_date($reqdate),
570 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
571 $form->{shipvia}, $form->{notes}, $form->{intnotes},
572 $form->{currency}, $form->{closed} ? 't' : 'f',
573 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
574 $quotation, conv_i($form->{department_id}),
575 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
576 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
577 conv_i($form->{delivery_vendor_id}),
578 conv_i($form->{delivery_customer_id}),
579 conv_i($form->{delivery_term_id}),
580 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
581 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
582 $form->{transaction_description},
583 $form->{marge_total} * 1, $form->{marge_percent} * 1,
584 conv_i($form->{id}));
585 do_query($form, $dbh, $query, @values);
587 $form->{ordtotal} = $amount;
590 $form->{name} = $form->{ $form->{vc} };
591 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
593 if (!$form->{shipto_id}) {
594 $form->add_shipto($dbh, $form->{id}, "OE");
597 # save printed, emailed, queued
598 $form->save_status($dbh);
600 # Link this record to the records it was created from.
601 $form->{convert_from_oe_ids} =~ s/^\s+//;
602 $form->{convert_from_oe_ids} =~ s/\s+$//;
603 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
604 delete $form->{convert_from_oe_ids};
606 if (scalar @convert_from_oe_ids) {
607 RecordLinks->create_links('dbh' => $dbh,
609 'from_table' => 'oe',
610 'from_ids' => \@convert_from_oe_ids,
612 'to_id' => $form->{id},
615 $self->_close_quotations_rfqs('dbh' => $dbh,
616 'from_id' => \@convert_from_oe_ids,
617 'to_id' => $form->{id});
620 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
621 if ($form->{vc} eq 'customer') {
622 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
624 if ($form->{vc} eq 'vendor') {
625 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
629 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
630 "quonumber" : "ordnumber"};
632 Common::webdav_folder($form);
634 my $rc = $dbh->commit;
636 $self->save_periodic_invoices_config(dbh => $dbh,
637 oe_id => $form->{id},
638 config_yaml => $form->{periodic_invoices_config})
639 if ($form->{type} eq 'sales_order');
641 $main::lxdebug->leave_sub();
646 sub save_periodic_invoices_config {
647 my ($self, %params) = @_;
649 return if !$params{oe_id};
651 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
652 return if 'HASH' ne ref $config;
654 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
655 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
656 $obj->update_attributes(%{ $config });
659 sub load_periodic_invoice_config {
663 delete $form->{periodic_invoices_config};
666 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
669 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
670 print printer_id copies) };
671 $form->{periodic_invoices_config} = YAML::Dump($config);
676 sub _close_quotations_rfqs {
677 $main::lxdebug->enter_sub();
682 Common::check_params(\%params, qw(from_id to_id));
684 my $myconfig = \%main::myconfig;
685 my $form = $main::form;
687 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
689 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
690 my $sth = prepare_query($form, $dbh, $query);
692 do_statement($form, $sth, $query, conv_i($params{to_id}));
694 my ($quotation) = $sth->fetchrow_array();
697 $main::lxdebug->leave_sub();
703 foreach my $from_id (@{ $params{from_id} }) {
704 $from_id = conv_i($from_id);
705 do_statement($form, $sth, $query, $from_id);
706 ($quotation) = $sth->fetchrow_array();
707 push @close_ids, $from_id if ($quotation);
712 if (scalar @close_ids) {
713 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
714 do_query($form, $dbh, $query, @close_ids);
716 $dbh->commit() unless ($params{dbh});
719 $main::lxdebug->leave_sub();
723 $main::lxdebug->enter_sub();
725 my ($self, $myconfig, $form) = @_;
727 my $rc = SL::DB::Order->new->db->with_transaction(sub {
728 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
730 SL::DB::Order->new(id => $form->{id})->delete;
732 my $spool = $::lx_office_conf{paths}->{spool};
733 unlink map { "$spool/$_" } @spoolfiles if $spool;
738 $main::lxdebug->leave_sub();
744 $main::lxdebug->enter_sub();
746 my ($self, $myconfig, $form) = @_;
748 # connect to database
749 my $dbh = $form->get_standard_dbh;
751 my ($query, $query_add, @values, @ids, $sth);
753 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
755 push @ids, $form->{"trans_id_$_"}
756 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
757 } (1 .. $form->{"rowcount"});
759 if ($form->{rowcount} && scalar @ids) {
760 $form->{convert_from_oe_ids} = join ' ', @ids;
763 # if called in multi id mode, and still only got one id, switch back to single id
764 if ($form->{"rowcount"} and $#ids == 0) {
765 $form->{"id"} = $ids[0];
769 # and remember for the rest of the function
770 my $is_collective_order = scalar @ids;
773 my $wday = (localtime(time))[6];
774 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
775 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
778 # get default accounts
779 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
780 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
781 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
782 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
783 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
786 my $ref = selectfirst_hashref_query($form, $dbh, $query);
787 map { $form->{$_} = $ref->{$_} } keys %$ref;
789 $form->{currency} = $form->get_default_currency($myconfig);
791 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
792 # we come from invoices, feel free.
793 $form->{reqdate} = $form->{deliverydate}
794 if ( $form->{deliverydate}
795 and $form->{callback} =~ /action=ar_transactions/);
797 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
799 if ($form->{id} or @ids) {
801 # retrieve order for single id
802 # NOTE: this query is intended to fetch all information only ONCE.
803 # so if any of these infos is important (or even different) for any item,
804 # it will be killed out and then has to be fetched from the item scope query further down
806 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
807 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
808 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
809 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
810 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
811 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
812 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
813 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
815 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
816 LEFT JOIN employee e ON (o.employee_id = e.id)
817 LEFT JOIN department d ON (o.department_id = d.id) | .
820 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
822 @values = $form->{id} ? ($form->{id}) : @ids;
823 $sth = prepare_execute_query($form, $dbh, $query, @values);
825 $ref = $sth->fetchrow_hashref("NAME_lc");
828 map { $form->{$_} = $ref->{$_} } keys %$ref;
830 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
832 # set all entries for multiple ids blank that yield different information
833 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
834 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
838 # if not given, fill transdate with current_date
839 $form->{transdate} = $form->current_date($myconfig)
840 unless $form->{transdate};
844 if ($form->{delivery_customer_id}) {
845 $query = qq|SELECT name FROM customer WHERE id = ?|;
846 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
849 if ($form->{delivery_vendor_id}) {
850 $query = qq|SELECT name FROM customer WHERE id = ?|;
851 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
854 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
856 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
857 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
859 $ref = $sth->fetchrow_hashref("NAME_lc");
861 map { $form->{$_} = $ref->{$_} } keys %$ref;
864 # get printed, emailed and queued
865 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
866 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
868 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
869 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
870 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
871 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
874 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
877 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
879 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
881 # retrieve individual items
882 # this query looks up all information about the items
883 # stuff different from the whole will not be overwritten, but saved with a suffix.
885 qq|SELECT o.id AS orderitems_id,
886 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
887 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
888 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
889 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
890 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
891 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
892 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
893 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
894 o.price_factor_id, o.price_factor, o.marge_price_factor,
895 pr.projectnumber, p.formel,
896 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
898 JOIN parts p ON (o.parts_id = p.id)
899 JOIN oe ON (o.trans_id = oe.id)
900 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
901 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
902 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
903 LEFT JOIN project pr ON (o.project_id = pr.id)
904 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
906 ? qq|WHERE o.trans_id = ?|
907 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
910 @ids = $form->{id} ? ($form->{id}) : @ids;
911 $sth = prepare_execute_query($form, $dbh, $query, @values);
913 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
914 # Retrieve custom variables.
915 my $cvars = CVar->get_custom_variables(dbh => $dbh,
917 sub_module => 'orderitems',
918 trans_id => $ref->{orderitems_id},
920 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
923 if (!$ref->{"part_inventory_accno_id"}) {
924 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
926 delete($ref->{"part_inventory_accno_id"});
928 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
929 # unless already present there
930 # remove _oe entries afterwards
931 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
932 qw|ordnumber transdate cusordnumber|
934 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
938 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
940 qq|SELECT accno AS inventory_accno, | .
941 qq| new_chart_id AS inventory_new_chart, | .
942 qq| date($transdate) - valid_from AS inventory_valid | .
943 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
944 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
945 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
948 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
950 qq|SELECT accno AS income_accno, | .
951 qq| new_chart_id AS income_new_chart, | .
952 qq| date($transdate) - valid_from AS income_valid | .
953 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
954 ($ref->{income_accno}, $ref->{income_new_chart},
955 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
958 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
960 qq|SELECT accno AS expense_accno, | .
961 qq| new_chart_id AS expense_new_chart, | .
962 qq| date($transdate) - valid_from AS expense_valid | .
963 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
964 ($ref->{expense_accno}, $ref->{expense_new_chart},
965 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
968 # delete orderitems_id in collective orders, so that they get cloned no matter what
969 delete $ref->{orderitems_id} if (@ids);
971 # get tax rates and description
972 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
974 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
975 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
976 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
977 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
978 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
979 qq|ORDER BY c.accno|;
980 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
981 $ref->{taxaccounts} = "";
983 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
984 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
988 $ref->{taxaccounts} .= "$ptr->{accno} ";
989 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
990 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
991 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
992 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
993 $form->{taxaccounts} .= "$ptr->{accno} ";
998 chop $ref->{taxaccounts};
1000 push @{ $form->{form_details} }, $ref;
1007 # get last name used
1008 $form->lastname_used($dbh, $myconfig, $form->{vc})
1009 unless $form->{"$form->{vc}_id"};
1013 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1015 Common::webdav_folder($form);
1017 $self->load_periodic_invoice_config($form);
1019 my $rc = $dbh->commit;
1021 $main::lxdebug->leave_sub();
1026 sub retrieve_simple {
1027 $main::lxdebug->enter_sub();
1032 Common::check_params(\%params, qw(id));
1034 my $myconfig = \%main::myconfig;
1035 my $form = $main::form;
1037 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1039 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1040 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1042 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1043 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1045 $main::lxdebug->leave_sub();
1051 $main::lxdebug->enter_sub();
1053 my ($self, $myconfig, $form) = @_;
1055 # connect to database
1056 my $dbh = $form->get_standard_dbh;
1062 my $nodiscount_subtotal = 0;
1063 my $discount_subtotal = 0;
1066 my @partsgroup = ();
1069 my $subtotal_header = 0;
1070 my $subposition = 0;
1078 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1080 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1081 'departments' => 'ALL_DEPARTMENTS');
1084 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1085 $price_factors{$pfac->{id}} = $pfac;
1086 $pfac->{factor} *= 1;
1087 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1091 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1092 next unless $dept->{id} eq $form->{department_id};
1093 $form->{department} = $dept->{description};
1097 # sort items by partsgroup
1098 for $i (1 .. $form->{rowcount}) {
1100 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1101 $partsgroup = $form->{"partsgroup_$i"};
1103 push @partsgroup, [$i, $partsgroup];
1104 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1110 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1111 %projects_by_id = map { $_->id => $_ } @$projects;
1114 if ($projects_by_id{$form->{"globalproject_id"}}) {
1115 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1116 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1118 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1119 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1123 $form->{discount} = [];
1125 $form->{TEMPLATE_ARRAYS} = { };
1126 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1128 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1129 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1132 qw(runningnumber number description longdescription qty ship unit bin
1133 partnotes serialnumber reqdate sellprice listprice netprice
1134 discount p_discount discount_sub nodiscount_sub
1135 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1136 price_factor price_factor_name partsgroup weight lineweight);
1138 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1139 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1141 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1143 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1145 my $totalweight = 0;
1147 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1150 if ($item->[1] ne $sameitem) {
1151 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1152 $sameitem = $item->[1];
1154 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1157 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1159 if ($form->{"id_$i"} != 0) {
1161 # add number, description and qty to $form->{number}, ....
1163 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1164 $subtotal_header = $i;
1165 $position = int($position);
1168 } elsif ($subtotal_header) {
1170 $position = int($position);
1171 $position = $position.".".$subposition;
1173 $position = int($position);
1177 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1179 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1180 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1181 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1182 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1183 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1184 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1185 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1186 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1187 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1188 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1189 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1190 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1191 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1192 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1193 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1194 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1195 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1196 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1197 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1199 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1200 my ($dec) = ($sellprice =~ /\.(\d+)/);
1201 my $decimalplaces = max 2, length($dec);
1203 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1205 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1206 my $linetotal = $form->round_amount($linetotal_exact, 2);
1208 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1209 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1211 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1213 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1215 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1217 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1218 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1220 $linetotal = ($linetotal != 0) ? $linetotal : '';
1222 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1223 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1224 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1226 $form->{ordtotal} += $linetotal;
1227 $form->{nodiscount_total} += $nodiscount_linetotal;
1228 $form->{discount_total} += $discount;
1230 if ($subtotal_header) {
1231 $discount_subtotal += $linetotal;
1232 $nodiscount_subtotal += $nodiscount_linetotal;
1235 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1236 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1237 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1238 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1239 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1241 $discount_subtotal = 0;
1242 $nodiscount_subtotal = 0;
1243 $subtotal_header = 0;
1246 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1249 if (!$form->{"discount_$i"}) {
1250 $nodiscount += $linetotal;
1253 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1255 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1256 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1257 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1258 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1259 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1260 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1262 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1263 $totalweight += $lineweight;
1264 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1265 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1266 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1267 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1269 my ($taxamount, $taxbase);
1272 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1274 if ($form->{taxincluded}) {
1277 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1278 $taxbase = $linetotal / (1 + $taxrate);
1280 $taxamount = $linetotal * $taxrate;
1281 $taxbase = $linetotal;
1284 if ($taxamount != 0) {
1285 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1286 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1287 $taxbase{$accno} += $taxbase;
1291 $tax_rate = $taxrate * 100;
1292 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1294 if ($form->{"assembly_$i"}) {
1297 # get parts and push them onto the stack
1299 if ($form->{groupitems}) {
1300 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1302 $sortorder = qq|ORDER BY a.oid|;
1305 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1306 qq|pg.partsgroup | .
1307 qq|FROM assembly a | .
1308 qq| JOIN parts p ON (a.parts_id = p.id) | .
1309 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1310 qq| WHERE a.bom = '1' | .
1311 qq| AND a.id = ? | . $sortorder;
1312 @values = ($form->{"id_$i"});
1313 $sth = $dbh->prepare($query);
1314 $sth->execute(@values) || $form->dberror($query);
1316 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1317 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1318 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1319 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1320 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1323 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1324 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1329 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1330 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1331 for @{ $ic_cvar_configs };
1333 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1337 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1338 $form->{totalweight_nofmt} = $totalweight;
1339 my $defaults = AM->get_defaults();
1340 $form->{weightunit} = $defaults->{weightunit};
1343 foreach $item (sort keys %taxaccounts) {
1344 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1346 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1347 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1348 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1349 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1350 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1351 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1352 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1354 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1355 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1356 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1359 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1360 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1361 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1362 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1364 if($form->{taxincluded}) {
1365 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1366 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1368 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1369 $form->{subtotal_nofmt} = $form->{ordtotal};
1372 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1375 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1377 if ($form->{type} =~ /_quotation/) {
1378 $form->set_payment_options($myconfig, $form->{quodate});
1380 $form->set_payment_options($myconfig, $form->{orddate});
1383 $form->{username} = $myconfig->{name};
1387 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1388 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1390 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1392 $main::lxdebug->leave_sub();
1395 sub project_description {
1396 $main::lxdebug->enter_sub();
1398 my ($self, $dbh, $id) = @_;
1400 my $query = qq|SELECT description FROM project WHERE id = ?|;
1401 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1403 $main::lxdebug->leave_sub();
1414 OE.pm - Order entry module
1418 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>.
1424 =item retrieve_simple PARAMS
1426 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1428 my $order = retrieve_simple(id => 2);