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;
54 $main::lxdebug->enter_sub();
56 my ($self, $myconfig, $form) = @_;
59 my $dbh = $form->get_standard_dbh;
62 my $ordnumber = 'ordnumber';
68 my ($periodic_invoices_columns, $periodic_invoices_joins);
70 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
72 if ($form->{type} =~ /_quotation$/) {
74 $ordnumber = 'quonumber';
76 } elsif ($form->{type} eq 'sales_order') {
77 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
78 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
81 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
85 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
87 SELECT from_id, ar.amount, ar.netamount FROM (
90 WHERE from_table = 'oe' AND to_table = 'ar'
92 SELECT rl1.from_id, rl2.to_id
94 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
95 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
97 LEFT JOIN ar ON ar.id = rl.to_id
99 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
100 $billed_amount{ $ref->{from_id}} += $ref->{amount};
101 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
106 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
107 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
108 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
109 qq| o.transaction_description, | .
110 qq| o.marge_total, o.marge_percent, | .
111 qq| ex.$rate AS exchangerate, | .
112 qq| pr.projectnumber AS globalprojectnumber, | .
113 qq| e.name AS employee, s.name AS salesman, | .
114 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
115 qq| tz.description AS taxzone | .
116 $periodic_invoices_columns .
118 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
119 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
120 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
121 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
122 qq| AND ex.transdate = o.transdate) | .
123 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
124 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
125 qq|$periodic_invoices_joins | .
126 qq|WHERE (o.quotation = ?) |;
127 push(@values, $quotation);
129 my ($null, $split_department_id) = split /--/, $form->{department};
130 my $department_id = $form->{department_id} || $split_department_id;
131 if ($department_id) {
132 $query .= qq| AND o.department_id = ?|;
133 push(@values, $department_id);
136 if ($form->{"project_id"}) {
138 qq|AND ((globalproject_id = ?) OR EXISTS | .
139 qq| (SELECT * FROM orderitems oi | .
140 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
141 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
144 if ($form->{"projectnumber"}) {
146 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
147 SELECT * FROM orderitems oi
148 LEFT JOIN project proi ON proi.id = oi.project_id
149 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
152 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
155 if ($form->{"business_id"}) {
156 $query .= " AND ct.business_id = ?";
157 push(@values, $form->{"business_id"});
160 if ($form->{"${vc}_id"}) {
161 $query .= " AND o.${vc}_id = ?";
162 push(@values, $form->{"${vc}_id"});
164 } elsif ($form->{$vc}) {
165 $query .= " AND ct.name ILIKE ?";
166 push(@values, '%' . $form->{$vc} . '%');
169 if (!$main::auth->assert('sales_all_edit', 1)) {
170 $query .= " AND o.employee_id = (select id from employee where login= ?)";
171 push @values, $form->{login};
173 if ($form->{employee_id}) {
174 $query .= " AND o.employee_id = ?";
175 push @values, conv_i($form->{employee_id});
178 if ($form->{salesman_id}) {
179 $query .= " AND o.salesman_id = ?";
180 push @values, conv_i($form->{salesman_id});
183 if (!$form->{open} && !$form->{closed}) {
184 $query .= " AND o.id = 0";
185 } elsif (!($form->{open} && $form->{closed})) {
186 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
189 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
190 ($form->{"notdelivered"} ne $form->{"delivered"})) {
191 $query .= $form->{"delivered"} ?
192 " AND o.delivered " : " AND NOT o.delivered";
195 if ($form->{$ordnumber}) {
196 $query .= qq| AND o.$ordnumber ILIKE ?|;
197 push(@values, '%' . $form->{$ordnumber} . '%');
200 if ($form->{cusordnumber}) {
201 $query .= qq| AND o.cusordnumber ILIKE ?|;
202 push(@values, '%' . $form->{cusordnumber} . '%');
205 if($form->{transdatefrom}) {
206 $query .= qq| AND o.transdate >= ?|;
207 push(@values, conv_date($form->{transdatefrom}));
210 if($form->{transdateto}) {
211 $query .= qq| AND o.transdate <= ?|;
212 push(@values, conv_date($form->{transdateto}));
215 if($form->{reqdatefrom}) {
216 $query .= qq| AND o.reqdate >= ?|;
217 push(@values, conv_date($form->{reqdatefrom}));
220 if($form->{reqdateto}) {
221 $query .= qq| AND o.reqdate <= ?|;
222 push(@values, conv_date($form->{reqdateto}));
225 if ($form->{shippingpoint}) {
226 $query .= qq| AND o.shippingpoint ILIKE ?|;
227 push(@values, '%' . $form->{shippingpoint} . '%');
230 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
231 $query .= qq| AND tz.id = ?|;
232 push(@values, $form->{taxzone_id});
235 if ($form->{transaction_description}) {
236 $query .= qq| AND o.transaction_description ILIKE ?|;
237 push(@values, '%' . $form->{transaction_description} . '%');
240 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
241 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
242 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
245 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
246 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
247 my %allowed_sort_columns = (
248 "transdate" => "o.transdate",
249 "reqdate" => "o.reqdate",
251 "ordnumber" => "o.ordnumber",
252 "cusordnumber" => "o.cusordnumber",
253 "quonumber" => "o.quonumber",
255 "employee" => "e.name",
256 "salesman" => "s.name",
257 "shipvia" => "o.shipvia",
258 "transaction_description" => "o.transaction_description",
259 "shippingpoint" => "o.shippingpoint",
260 "taxzone" => "tz.description",
262 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
263 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
265 $query .= qq| ORDER by | . $sortorder;
267 my $sth = $dbh->prepare($query);
268 $sth->execute(@values) ||
269 $form->dberror($query . " (" . join(", ", @values) . ")");
273 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
274 $ref->{billed_amount} = $billed_amount{$ref->{id}};
275 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
276 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
277 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
278 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
279 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
280 $id{ $ref->{id} } = $ref->{id};
285 $main::lxdebug->leave_sub();
288 sub transactions_for_todo_list {
289 $main::lxdebug->enter_sub();
294 my $myconfig = \%main::myconfig;
295 my $form = $main::form;
297 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
299 my $query = qq|SELECT id FROM employee WHERE login = ?|;
300 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
303 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
304 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
309 LEFT JOIN customer c ON (oe.customer_id = c.id)
310 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
311 LEFT JOIN employee e ON (oe.employee_id = e.id)
312 WHERE (COALESCE(quotation, FALSE) = TRUE)
313 AND (COALESCE(closed, FALSE) = FALSE)
314 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
315 AND NOT (oe.reqdate ISNULL)
316 AND (oe.reqdate < current_date)
319 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
321 $main::lxdebug->leave_sub();
327 $main::lxdebug->enter_sub();
329 my ($self, $myconfig, $form) = @_;
331 # connect to database, turn off autocommit
332 my $dbh = $form->get_standard_dbh;
334 my ($query, @values, $sth, $null);
335 my $exchangerate = 0;
337 my $all_units = AM->retrieve_units($myconfig, $form);
338 $form->{all_units} = $all_units;
340 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
343 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
344 unless ($form->{employee_id}) {
345 $form->get_employee($dbh);
348 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
350 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
351 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
352 $form->{$number_field} ||= $trans_number->create_unique;
355 $query = qq|DELETE FROM custom_variables
356 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
357 AND (sub_module = 'orderitems')
358 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
359 do_query($form, $dbh, $query, $form->{id});
361 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
362 do_query($form, $dbh, $query, $form->{id});
364 $query = qq|DELETE FROM shipto | .
365 qq|WHERE trans_id = ? AND module = 'OE'|;
366 do_query($form, $dbh, $query, $form->{id});
370 $query = qq|SELECT nextval('id')|;
371 ($form->{id}) = selectrow_query($form, $dbh, $query);
373 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
374 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
392 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
393 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
396 for my $i (1 .. $form->{rowcount}) {
398 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
400 if ($form->{"id_$i"}) {
403 $query = qq|SELECT unit FROM parts WHERE id = ?|;
404 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
407 if (defined($all_units->{$item_unit}->{factor}) &&
408 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
409 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
411 my $baseqty = $form->{"qty_$i"} * $basefactor;
413 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
414 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
416 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
418 # set values to 0 if nothing entered
419 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
421 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
422 $fxsellprice = $form->{"sellprice_$i"};
424 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
426 my $decimalplaces = ($dec > 2) ? $dec : 2;
428 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
429 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
431 $form->{"inventory_accno_$i"} *= 1;
432 $form->{"expense_accno_$i"} *= 1;
434 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
435 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
437 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
441 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
443 if ($form->{taxincluded}) {
444 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
445 $taxbase = $linetotal - $taxamount;
447 # we are not keeping a natural price, do not round
448 $form->{"sellprice_$i"} =
449 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
451 $taxamount = $linetotal * $taxrate;
452 $taxbase = $linetotal;
455 if ($form->round_amount($taxrate, 7) == 0) {
456 if ($form->{taxincluded}) {
457 foreach my $item (@taxaccounts) {
458 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
459 $taxaccounts{$item} += $taxamount;
460 $taxdiff += $taxamount;
461 $taxbase{$item} += $taxbase;
463 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
465 foreach my $item (@taxaccounts) {
466 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
467 $taxbase{$item} += $taxbase;
471 foreach my $item (@taxaccounts) {
472 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
473 $taxbase{$item} += $taxbase;
477 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
479 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
481 # Get pricegroup_id and save it. Unfortunately the interface
482 # also uses ID "0" for signalling that none is selected, but "0"
483 # must not be stored in the database. Therefore we cannot simply
485 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
487 $pricegroup_id = undef if !$pricegroup_id;
489 # save detail record in orderitems table
490 my $orderitems_id = $form->{"orderitems_id_$i"};
491 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
494 $query = qq|INSERT INTO orderitems (
495 id, trans_id, parts_id, description, longdescription, qty, base_qty,
496 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
497 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
498 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
499 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
500 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
502 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
503 $form->{"description_$i"}, $form->{"longdescription_$i"},
504 $form->{"qty_$i"}, $baseqty,
505 $fxsellprice, $form->{"discount_$i"},
506 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
507 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
508 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
509 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
510 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
511 $form->{"lastcost_$i"},
512 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
513 conv_i($form->{"marge_price_factor_$i"}));
514 do_query($form, $dbh, $query, @values);
516 $form->{"sellprice_$i"} = $fxsellprice;
517 $form->{"discount_$i"} *= 100;
519 CVar->save_custom_variables(module => 'IC',
520 sub_module => 'orderitems',
521 trans_id => $orderitems_id,
522 configs => $ic_cvar_configs,
524 name_prefix => 'ic_',
525 name_postfix => "_$i",
530 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
534 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
536 $amount = $form->round_amount($netamount + $tax, 2);
537 $netamount = $form->round_amount($netamount, 2);
539 if ($form->{currency} eq $form->{defaultcurrency}) {
540 $form->{exchangerate} = 1;
542 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
545 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
547 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
549 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
554 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
555 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
556 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
557 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
558 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
559 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
562 @values = ($form->{ordnumber} || '', $form->{quonumber},
563 $form->{cusordnumber}, conv_date($form->{transdate}),
564 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
565 $amount, $netamount, conv_date($reqdate),
566 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
567 $form->{shipvia}, $form->{notes}, $form->{intnotes},
568 $form->{currency}, $form->{closed} ? 't' : 'f',
569 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
570 $quotation, conv_i($form->{department_id}),
571 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
572 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
573 conv_i($form->{delivery_vendor_id}),
574 conv_i($form->{delivery_customer_id}),
575 conv_i($form->{delivery_term_id}),
576 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
577 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
578 $form->{transaction_description},
579 $form->{marge_total} * 1, $form->{marge_percent} * 1,
580 conv_i($form->{id}));
581 do_query($form, $dbh, $query, @values);
583 $form->{ordtotal} = $amount;
586 $form->{name} = $form->{ $form->{vc} };
587 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
589 if (!$form->{shipto_id}) {
590 $form->add_shipto($dbh, $form->{id}, "OE");
593 # save printed, emailed, queued
594 $form->save_status($dbh);
596 # Link this record to the records it was created from.
597 $form->{convert_from_oe_ids} =~ s/^\s+//;
598 $form->{convert_from_oe_ids} =~ s/\s+$//;
599 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
600 delete $form->{convert_from_oe_ids};
602 if (scalar @convert_from_oe_ids) {
603 RecordLinks->create_links('dbh' => $dbh,
605 'from_table' => 'oe',
606 'from_ids' => \@convert_from_oe_ids,
608 'to_id' => $form->{id},
611 $self->_close_quotations_rfqs('dbh' => $dbh,
612 'from_id' => \@convert_from_oe_ids,
613 'to_id' => $form->{id});
616 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
617 if ($form->{vc} eq 'customer') {
618 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
620 if ($form->{vc} eq 'vendor') {
621 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
625 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
626 "quonumber" : "ordnumber"};
628 Common::webdav_folder($form);
630 my $rc = $dbh->commit;
632 $self->save_periodic_invoices_config(dbh => $dbh,
633 oe_id => $form->{id},
634 config_yaml => $form->{periodic_invoices_config})
635 if ($form->{type} eq 'sales_order');
637 $main::lxdebug->leave_sub();
642 sub save_periodic_invoices_config {
643 my ($self, %params) = @_;
645 return if !$params{oe_id};
647 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
648 return if 'HASH' ne ref $config;
650 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
651 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
652 $obj->update_attributes(%{ $config });
655 sub load_periodic_invoice_config {
659 delete $form->{periodic_invoices_config};
662 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
665 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
666 print printer_id copies) };
667 $form->{periodic_invoices_config} = YAML::Dump($config);
672 sub _close_quotations_rfqs {
673 $main::lxdebug->enter_sub();
678 Common::check_params(\%params, qw(from_id to_id));
680 my $myconfig = \%main::myconfig;
681 my $form = $main::form;
683 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
685 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
686 my $sth = prepare_query($form, $dbh, $query);
688 do_statement($form, $sth, $query, conv_i($params{to_id}));
690 my ($quotation) = $sth->fetchrow_array();
693 $main::lxdebug->leave_sub();
699 foreach my $from_id (@{ $params{from_id} }) {
700 $from_id = conv_i($from_id);
701 do_statement($form, $sth, $query, $from_id);
702 ($quotation) = $sth->fetchrow_array();
703 push @close_ids, $from_id if ($quotation);
708 if (scalar @close_ids) {
709 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
710 do_query($form, $dbh, $query, @close_ids);
712 $dbh->commit() unless ($params{dbh});
715 $main::lxdebug->leave_sub();
719 $main::lxdebug->enter_sub();
721 my ($self, $myconfig, $form) = @_;
723 my $rc = SL::DB::Order->new->db->with_transaction(sub {
724 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
726 SL::DB::Order->new(id => $form->{id})->delete;
728 my $spool = $::lx_office_conf{paths}->{spool};
729 unlink map { "$spool/$_" } @spoolfiles if $spool;
734 $main::lxdebug->leave_sub();
740 $main::lxdebug->enter_sub();
742 my ($self, $myconfig, $form) = @_;
744 # connect to database
745 my $dbh = $form->get_standard_dbh;
747 my ($query, $query_add, @values, @ids, $sth);
749 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
751 push @ids, $form->{"trans_id_$_"}
752 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
753 } (1 .. $form->{"rowcount"});
755 if ($form->{rowcount} && scalar @ids) {
756 $form->{convert_from_oe_ids} = join ' ', @ids;
759 # if called in multi id mode, and still only got one id, switch back to single id
760 if ($form->{"rowcount"} and $#ids == 0) {
761 $form->{"id"} = $ids[0];
765 # and remember for the rest of the function
766 my $is_collective_order = scalar @ids;
769 my $wday = (localtime(time))[6];
770 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
771 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
774 # get default accounts
775 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
776 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
777 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
778 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
779 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
782 my $ref = selectfirst_hashref_query($form, $dbh, $query);
783 map { $form->{$_} = $ref->{$_} } keys %$ref;
785 $form->{currency} = $form->get_default_currency($myconfig);
787 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
788 # we come from invoices, feel free.
789 $form->{reqdate} = $form->{deliverydate}
790 if ( $form->{deliverydate}
791 and $form->{callback} =~ /action=ar_transactions/);
793 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
795 if ($form->{id} or @ids) {
797 # retrieve order for single id
798 # NOTE: this query is intended to fetch all information only ONCE.
799 # so if any of these infos is important (or even different) for any item,
800 # it will be killed out and then has to be fetched from the item scope query further down
802 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
803 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
804 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
805 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
806 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
807 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
808 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
809 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
811 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
812 LEFT JOIN employee e ON (o.employee_id = e.id)
813 LEFT JOIN department d ON (o.department_id = d.id) | .
816 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
818 @values = $form->{id} ? ($form->{id}) : @ids;
819 $sth = prepare_execute_query($form, $dbh, $query, @values);
821 $ref = $sth->fetchrow_hashref("NAME_lc");
824 map { $form->{$_} = $ref->{$_} } keys %$ref;
826 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
828 # set all entries for multiple ids blank that yield different information
829 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
830 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
834 # if not given, fill transdate with current_date
835 $form->{transdate} = $form->current_date($myconfig)
836 unless $form->{transdate};
840 if ($form->{delivery_customer_id}) {
841 $query = qq|SELECT name FROM customer WHERE id = ?|;
842 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
845 if ($form->{delivery_vendor_id}) {
846 $query = qq|SELECT name FROM customer WHERE id = ?|;
847 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
850 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
852 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
853 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
855 $ref = $sth->fetchrow_hashref("NAME_lc");
857 map { $form->{$_} = $ref->{$_} } keys %$ref;
860 # get printed, emailed and queued
861 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
862 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
864 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
865 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
866 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
867 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
870 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
873 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
875 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
877 # retrieve individual items
878 # this query looks up all information about the items
879 # stuff different from the whole will not be overwritten, but saved with a suffix.
881 qq|SELECT o.id AS orderitems_id,
882 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
883 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
884 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
885 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
886 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
887 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
888 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
889 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
890 o.price_factor_id, o.price_factor, o.marge_price_factor,
891 pr.projectnumber, p.formel,
892 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
894 JOIN parts p ON (o.parts_id = p.id)
895 JOIN oe ON (o.trans_id = oe.id)
896 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
897 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
898 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
899 LEFT JOIN project pr ON (o.project_id = pr.id)
900 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
902 ? qq|WHERE o.trans_id = ?|
903 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
906 @ids = $form->{id} ? ($form->{id}) : @ids;
907 $sth = prepare_execute_query($form, $dbh, $query, @values);
909 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
910 # Retrieve custom variables.
911 my $cvars = CVar->get_custom_variables(dbh => $dbh,
913 sub_module => 'orderitems',
914 trans_id => $ref->{orderitems_id},
916 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
919 if (!$ref->{"part_inventory_accno_id"}) {
920 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
922 delete($ref->{"part_inventory_accno_id"});
924 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
925 # unless already present there
926 # remove _oe entries afterwards
927 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
928 qw|ordnumber transdate cusordnumber|
930 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
934 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
936 qq|SELECT accno AS inventory_accno, | .
937 qq| new_chart_id AS inventory_new_chart, | .
938 qq| date($transdate) - valid_from AS inventory_valid | .
939 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
940 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
941 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
944 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
946 qq|SELECT accno AS income_accno, | .
947 qq| new_chart_id AS income_new_chart, | .
948 qq| date($transdate) - valid_from AS income_valid | .
949 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
950 ($ref->{income_accno}, $ref->{income_new_chart},
951 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
954 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
956 qq|SELECT accno AS expense_accno, | .
957 qq| new_chart_id AS expense_new_chart, | .
958 qq| date($transdate) - valid_from AS expense_valid | .
959 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
960 ($ref->{expense_accno}, $ref->{expense_new_chart},
961 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
964 # delete orderitems_id in collective orders, so that they get cloned no matter what
965 delete $ref->{orderitems_id} if (@ids);
967 # get tax rates and description
968 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
970 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
971 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
972 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
973 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
974 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
975 qq|ORDER BY c.accno|;
976 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
977 $ref->{taxaccounts} = "";
979 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
980 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
984 $ref->{taxaccounts} .= "$ptr->{accno} ";
985 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
986 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
987 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
988 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
989 $form->{taxaccounts} .= "$ptr->{accno} ";
994 chop $ref->{taxaccounts};
996 push @{ $form->{form_details} }, $ref;
1003 # get last name used
1004 $form->lastname_used($dbh, $myconfig, $form->{vc})
1005 unless $form->{"$form->{vc}_id"};
1009 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1011 Common::webdav_folder($form);
1013 $self->load_periodic_invoice_config($form);
1015 my $rc = $dbh->commit;
1017 $main::lxdebug->leave_sub();
1022 sub retrieve_simple {
1023 $main::lxdebug->enter_sub();
1028 Common::check_params(\%params, qw(id));
1030 my $myconfig = \%main::myconfig;
1031 my $form = $main::form;
1033 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1035 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1036 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1038 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1039 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1041 $main::lxdebug->leave_sub();
1047 $main::lxdebug->enter_sub();
1049 my ($self, $myconfig, $form) = @_;
1051 # connect to database
1052 my $dbh = $form->get_standard_dbh;
1058 my $nodiscount_subtotal = 0;
1059 my $discount_subtotal = 0;
1062 my @partsgroup = ();
1065 my $subtotal_header = 0;
1066 my $subposition = 0;
1074 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1076 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1077 'departments' => 'ALL_DEPARTMENTS');
1080 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1081 $price_factors{$pfac->{id}} = $pfac;
1082 $pfac->{factor} *= 1;
1083 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1087 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1088 next unless $dept->{id} eq $form->{department_id};
1089 $form->{department} = $dept->{description};
1093 # sort items by partsgroup
1094 for $i (1 .. $form->{rowcount}) {
1096 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1097 $partsgroup = $form->{"partsgroup_$i"};
1099 push @partsgroup, [$i, $partsgroup];
1100 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1106 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1107 %projects_by_id = map { $_->id => $_ } @$projects;
1110 if ($projects_by_id{$form->{"globalproject_id"}}) {
1111 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1112 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1114 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1115 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1119 $form->{discount} = [];
1121 $form->{TEMPLATE_ARRAYS} = { };
1122 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1124 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1125 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1128 qw(runningnumber number description longdescription qty ship unit bin
1129 partnotes serialnumber reqdate sellprice listprice netprice
1130 discount p_discount discount_sub nodiscount_sub
1131 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1132 price_factor price_factor_name partsgroup weight lineweight);
1134 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1135 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1137 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1139 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1141 my $totalweight = 0;
1143 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1146 if ($item->[1] ne $sameitem) {
1147 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1148 $sameitem = $item->[1];
1150 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1153 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1155 if ($form->{"id_$i"} != 0) {
1157 # add number, description and qty to $form->{number}, ....
1159 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1160 $subtotal_header = $i;
1161 $position = int($position);
1164 } elsif ($subtotal_header) {
1166 $position = int($position);
1167 $position = $position.".".$subposition;
1169 $position = int($position);
1173 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1175 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1176 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1177 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1178 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1179 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1180 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1181 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1182 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1183 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1184 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1185 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1186 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1187 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1188 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1189 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1190 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1191 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1192 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1193 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1195 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1196 my ($dec) = ($sellprice =~ /\.(\d+)/);
1197 my $decimalplaces = max 2, length($dec);
1199 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1201 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1202 my $linetotal = $form->round_amount($linetotal_exact, 2);
1204 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1205 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1207 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1209 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1211 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1213 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1214 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1216 $linetotal = ($linetotal != 0) ? $linetotal : '';
1218 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1219 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1220 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1222 $form->{ordtotal} += $linetotal;
1223 $form->{nodiscount_total} += $nodiscount_linetotal;
1224 $form->{discount_total} += $discount;
1226 if ($subtotal_header) {
1227 $discount_subtotal += $linetotal;
1228 $nodiscount_subtotal += $nodiscount_linetotal;
1231 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1232 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1233 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1234 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1235 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1237 $discount_subtotal = 0;
1238 $nodiscount_subtotal = 0;
1239 $subtotal_header = 0;
1242 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1245 if (!$form->{"discount_$i"}) {
1246 $nodiscount += $linetotal;
1249 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1251 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1252 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1253 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1254 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1255 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1256 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1258 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1259 $totalweight += $lineweight;
1260 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1261 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1262 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1263 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1265 my ($taxamount, $taxbase);
1268 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1270 if ($form->{taxincluded}) {
1273 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1274 $taxbase = $linetotal / (1 + $taxrate);
1276 $taxamount = $linetotal * $taxrate;
1277 $taxbase = $linetotal;
1280 if ($taxamount != 0) {
1281 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1282 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1283 $taxbase{$accno} += $taxbase;
1287 $tax_rate = $taxrate * 100;
1288 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1290 if ($form->{"assembly_$i"}) {
1293 # get parts and push them onto the stack
1295 if ($form->{groupitems}) {
1296 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1298 $sortorder = qq|ORDER BY a.oid|;
1301 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1302 qq|pg.partsgroup | .
1303 qq|FROM assembly a | .
1304 qq| JOIN parts p ON (a.parts_id = p.id) | .
1305 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1306 qq| WHERE a.bom = '1' | .
1307 qq| AND a.id = ? | . $sortorder;
1308 @values = ($form->{"id_$i"});
1309 $sth = $dbh->prepare($query);
1310 $sth->execute(@values) || $form->dberror($query);
1312 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1313 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1314 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1315 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1316 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1319 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1320 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1325 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1326 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1327 for @{ $ic_cvar_configs };
1329 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1333 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1334 $form->{totalweight_nofmt} = $totalweight;
1335 my $defaults = AM->get_defaults();
1336 $form->{weightunit} = $defaults->{weightunit};
1339 foreach $item (sort keys %taxaccounts) {
1340 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1342 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1343 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1344 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1345 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1346 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1347 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1348 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1350 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1351 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1352 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1355 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1356 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1357 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1358 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1360 if($form->{taxincluded}) {
1361 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1362 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1364 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1365 $form->{subtotal_nofmt} = $form->{ordtotal};
1368 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1371 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1373 if ($form->{type} =~ /_quotation/) {
1374 $form->set_payment_options($myconfig, $form->{quodate});
1376 $form->set_payment_options($myconfig, $form->{orddate});
1379 $form->{username} = $myconfig->{name};
1383 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1384 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1386 $main::lxdebug->leave_sub();
1389 sub project_description {
1390 $main::lxdebug->enter_sub();
1392 my ($self, $dbh, $id) = @_;
1394 my $query = qq|SELECT description FROM project WHERE id = ?|;
1395 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1397 $main::lxdebug->leave_sub();
1408 OE.pm - Order entry module
1412 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>.
1418 =item retrieve_simple PARAMS
1420 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1422 my $order = retrieve_simple(id => 2);