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 # keep entered selling price
420 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
422 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
424 my $decimalplaces = ($dec > 2) ? $dec : 2;
426 # undo discount formatting
427 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
430 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
432 # round linetotal at least to 2 decimal places
433 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
434 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
436 $form->{"inventory_accno_$i"} *= 1;
437 $form->{"expense_accno_$i"} *= 1;
439 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
443 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
445 if ($form->{taxincluded}) {
446 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
447 $taxbase = $linetotal - $taxamount;
449 # we are not keeping a natural price, do not round
450 $form->{"sellprice_$i"} =
451 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
453 $taxamount = $linetotal * $taxrate;
454 $taxbase = $linetotal;
457 if ($form->round_amount($taxrate, 7) == 0) {
458 if ($form->{taxincluded}) {
459 foreach my $item (@taxaccounts) {
460 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
461 $taxaccounts{$item} += $taxamount;
462 $taxdiff += $taxamount;
463 $taxbase{$item} += $taxbase;
465 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
467 foreach my $item (@taxaccounts) {
468 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
469 $taxbase{$item} += $taxbase;
473 foreach my $item (@taxaccounts) {
474 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
475 $taxbase{$item} += $taxbase;
479 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
481 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
483 # Get pricegroup_id and save it. Unfortunately the interface
484 # also uses ID "0" for signalling that none is selected, but "0"
485 # must not be stored in the database. Therefore we cannot simply
487 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
489 $pricegroup_id = undef if !$pricegroup_id;
491 # save detail record in orderitems table
492 my $orderitems_id = $form->{"orderitems_id_$i"};
493 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
496 $query = qq|INSERT INTO orderitems (
497 id, trans_id, parts_id, description, longdescription, qty, base_qty,
498 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
499 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
500 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
501 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
502 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
504 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
505 $form->{"description_$i"}, $form->{"longdescription_$i"},
506 $form->{"qty_$i"}, $baseqty,
507 $fxsellprice, $form->{"discount_$i"},
508 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
509 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
510 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
511 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
512 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
513 $form->{"lastcost_$i"},
514 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
515 conv_i($form->{"marge_price_factor_$i"}));
516 do_query($form, $dbh, $query, @values);
518 $form->{"sellprice_$i"} = $fxsellprice;
519 $form->{"discount_$i"} *= 100;
521 CVar->save_custom_variables(module => 'IC',
522 sub_module => 'orderitems',
523 trans_id => $orderitems_id,
524 configs => $ic_cvar_configs,
526 name_prefix => 'ic_',
527 name_postfix => "_$i",
532 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
536 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
538 $amount = $form->round_amount($netamount + $tax, 2);
539 $netamount = $form->round_amount($netamount, 2);
541 if ($form->{currency} eq $form->{defaultcurrency}) {
542 $form->{exchangerate} = 1;
544 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
547 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
549 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
551 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
556 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
557 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
558 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
559 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
560 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
561 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
564 @values = ($form->{ordnumber} || '', $form->{quonumber},
565 $form->{cusordnumber}, conv_date($form->{transdate}),
566 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
567 $amount, $netamount, conv_date($reqdate),
568 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
569 $form->{shipvia}, $form->{notes}, $form->{intnotes},
570 $form->{currency}, $form->{closed} ? 't' : 'f',
571 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
572 $quotation, conv_i($form->{department_id}),
573 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
574 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
575 conv_i($form->{delivery_vendor_id}),
576 conv_i($form->{delivery_customer_id}),
577 conv_i($form->{delivery_term_id}),
578 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
579 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
580 $form->{transaction_description},
581 $form->{marge_total} * 1, $form->{marge_percent} * 1,
582 conv_i($form->{id}));
583 do_query($form, $dbh, $query, @values);
585 $form->{ordtotal} = $amount;
588 $form->{name} = $form->{ $form->{vc} };
589 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
591 if (!$form->{shipto_id}) {
592 $form->add_shipto($dbh, $form->{id}, "OE");
595 # save printed, emailed, queued
596 $form->save_status($dbh);
598 # Link this record to the records it was created from.
599 $form->{convert_from_oe_ids} =~ s/^\s+//;
600 $form->{convert_from_oe_ids} =~ s/\s+$//;
601 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
602 delete $form->{convert_from_oe_ids};
604 if (scalar @convert_from_oe_ids) {
605 RecordLinks->create_links('dbh' => $dbh,
607 'from_table' => 'oe',
608 'from_ids' => \@convert_from_oe_ids,
610 'to_id' => $form->{id},
613 $self->_close_quotations_rfqs('dbh' => $dbh,
614 'from_id' => \@convert_from_oe_ids,
615 'to_id' => $form->{id});
618 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
619 if ($form->{vc} eq 'customer') {
620 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
622 if ($form->{vc} eq 'vendor') {
623 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
627 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
628 "quonumber" : "ordnumber"};
630 Common::webdav_folder($form);
632 my $rc = $dbh->commit;
634 $self->save_periodic_invoices_config(dbh => $dbh,
635 oe_id => $form->{id},
636 config_yaml => $form->{periodic_invoices_config})
637 if ($form->{type} eq 'sales_order');
639 $main::lxdebug->leave_sub();
644 sub save_periodic_invoices_config {
645 my ($self, %params) = @_;
647 return if !$params{oe_id};
649 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
650 return if 'HASH' ne ref $config;
652 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
653 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
654 $obj->update_attributes(%{ $config });
657 sub load_periodic_invoice_config {
661 delete $form->{periodic_invoices_config};
664 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
667 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
668 print printer_id copies) };
669 $form->{periodic_invoices_config} = YAML::Dump($config);
674 sub _close_quotations_rfqs {
675 $main::lxdebug->enter_sub();
680 Common::check_params(\%params, qw(from_id to_id));
682 my $myconfig = \%main::myconfig;
683 my $form = $main::form;
685 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
687 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
688 my $sth = prepare_query($form, $dbh, $query);
690 do_statement($form, $sth, $query, conv_i($params{to_id}));
692 my ($quotation) = $sth->fetchrow_array();
695 $main::lxdebug->leave_sub();
701 foreach my $from_id (@{ $params{from_id} }) {
702 $from_id = conv_i($from_id);
703 do_statement($form, $sth, $query, $from_id);
704 ($quotation) = $sth->fetchrow_array();
705 push @close_ids, $from_id if ($quotation);
710 if (scalar @close_ids) {
711 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
712 do_query($form, $dbh, $query, @close_ids);
714 $dbh->commit() unless ($params{dbh});
717 $main::lxdebug->leave_sub();
721 $main::lxdebug->enter_sub();
723 my ($self, $myconfig, $form) = @_;
725 my $rc = SL::DB::Order->new->db->with_transaction(sub {
726 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
728 SL::DB::Order->new(id => $form->{id})->delete;
730 my $spool = $::lx_office_conf{paths}->{spool};
731 unlink map { "$spool/$_" } @spoolfiles if $spool;
736 $main::lxdebug->leave_sub();
742 $main::lxdebug->enter_sub();
744 my ($self, $myconfig, $form) = @_;
746 # connect to database
747 my $dbh = $form->get_standard_dbh;
749 my ($query, $query_add, @values, @ids, $sth);
751 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
753 push @ids, $form->{"trans_id_$_"}
754 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
755 } (1 .. $form->{"rowcount"});
757 if ($form->{rowcount} && scalar @ids) {
758 $form->{convert_from_oe_ids} = join ' ', @ids;
761 # if called in multi id mode, and still only got one id, switch back to single id
762 if ($form->{"rowcount"} and $#ids == 0) {
763 $form->{"id"} = $ids[0];
767 # and remember for the rest of the function
768 my $is_collective_order = scalar @ids;
771 my $wday = (localtime(time))[6];
772 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
773 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
776 # get default accounts
777 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
778 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
779 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
780 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
781 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
784 my $ref = selectfirst_hashref_query($form, $dbh, $query);
785 map { $form->{$_} = $ref->{$_} } keys %$ref;
787 $form->{currency} = $form->get_default_currency($myconfig);
789 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
790 # we come from invoices, feel free.
791 $form->{reqdate} = $form->{deliverydate}
792 if ( $form->{deliverydate}
793 and $form->{callback} =~ /action=ar_transactions/);
795 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
797 if ($form->{id} or @ids) {
799 # retrieve order for single id
800 # NOTE: this query is intended to fetch all information only ONCE.
801 # so if any of these infos is important (or even different) for any item,
802 # it will be killed out and then has to be fetched from the item scope query further down
804 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
805 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
806 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
807 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
808 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
809 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
810 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
811 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
813 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
814 LEFT JOIN employee e ON (o.employee_id = e.id)
815 LEFT JOIN department d ON (o.department_id = d.id) | .
818 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
820 @values = $form->{id} ? ($form->{id}) : @ids;
821 $sth = prepare_execute_query($form, $dbh, $query, @values);
823 $ref = $sth->fetchrow_hashref("NAME_lc");
826 map { $form->{$_} = $ref->{$_} } keys %$ref;
828 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
830 # set all entries for multiple ids blank that yield different information
831 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
832 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
836 # if not given, fill transdate with current_date
837 $form->{transdate} = $form->current_date($myconfig)
838 unless $form->{transdate};
842 if ($form->{delivery_customer_id}) {
843 $query = qq|SELECT name FROM customer WHERE id = ?|;
844 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
847 if ($form->{delivery_vendor_id}) {
848 $query = qq|SELECT name FROM customer WHERE id = ?|;
849 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
852 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
854 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
855 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
857 $ref = $sth->fetchrow_hashref("NAME_lc");
859 map { $form->{$_} = $ref->{$_} } keys %$ref;
862 # get printed, emailed and queued
863 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
864 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
866 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
867 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
868 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
869 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
872 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
875 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
877 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
879 # retrieve individual items
880 # this query looks up all information about the items
881 # stuff different from the whole will not be overwritten, but saved with a suffix.
883 qq|SELECT o.id AS orderitems_id,
884 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
885 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
886 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
887 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
888 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
889 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
890 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
891 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
892 o.price_factor_id, o.price_factor, o.marge_price_factor,
893 pr.projectnumber, p.formel,
894 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
896 JOIN parts p ON (o.parts_id = p.id)
897 JOIN oe ON (o.trans_id = oe.id)
898 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
899 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
900 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
901 LEFT JOIN project pr ON (o.project_id = pr.id)
902 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
904 ? qq|WHERE o.trans_id = ?|
905 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
908 @ids = $form->{id} ? ($form->{id}) : @ids;
909 $sth = prepare_execute_query($form, $dbh, $query, @values);
911 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
912 # Retrieve custom variables.
913 my $cvars = CVar->get_custom_variables(dbh => $dbh,
915 sub_module => 'orderitems',
916 trans_id => $ref->{orderitems_id},
918 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
921 if (!$ref->{"part_inventory_accno_id"}) {
922 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
924 delete($ref->{"part_inventory_accno_id"});
926 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
927 # unless already present there
928 # remove _oe entries afterwards
929 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
930 qw|ordnumber transdate cusordnumber|
932 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
936 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
938 qq|SELECT accno AS inventory_accno, | .
939 qq| new_chart_id AS inventory_new_chart, | .
940 qq| date($transdate) - valid_from AS inventory_valid | .
941 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
942 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
943 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
946 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
948 qq|SELECT accno AS income_accno, | .
949 qq| new_chart_id AS income_new_chart, | .
950 qq| date($transdate) - valid_from AS income_valid | .
951 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
952 ($ref->{income_accno}, $ref->{income_new_chart},
953 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
956 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
958 qq|SELECT accno AS expense_accno, | .
959 qq| new_chart_id AS expense_new_chart, | .
960 qq| date($transdate) - valid_from AS expense_valid | .
961 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
962 ($ref->{expense_accno}, $ref->{expense_new_chart},
963 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
966 # delete orderitems_id in collective orders, so that they get cloned no matter what
967 delete $ref->{orderitems_id} if (@ids);
969 # get tax rates and description
970 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
972 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
973 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
974 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
975 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
976 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
977 qq|ORDER BY c.accno|;
978 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
979 $ref->{taxaccounts} = "";
981 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
982 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
986 $ref->{taxaccounts} .= "$ptr->{accno} ";
987 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
988 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
989 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
990 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
991 $form->{taxaccounts} .= "$ptr->{accno} ";
996 chop $ref->{taxaccounts};
998 push @{ $form->{form_details} }, $ref;
1005 # get last name used
1006 $form->lastname_used($dbh, $myconfig, $form->{vc})
1007 unless $form->{"$form->{vc}_id"};
1011 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1013 Common::webdav_folder($form);
1015 $self->load_periodic_invoice_config($form);
1017 my $rc = $dbh->commit;
1019 $main::lxdebug->leave_sub();
1024 sub retrieve_simple {
1025 $main::lxdebug->enter_sub();
1030 Common::check_params(\%params, qw(id));
1032 my $myconfig = \%main::myconfig;
1033 my $form = $main::form;
1035 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1037 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1038 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1040 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1041 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1043 $main::lxdebug->leave_sub();
1049 $main::lxdebug->enter_sub();
1051 my ($self, $myconfig, $form) = @_;
1053 # connect to database
1054 my $dbh = $form->get_standard_dbh;
1060 my $nodiscount_subtotal = 0;
1061 my $discount_subtotal = 0;
1064 my @partsgroup = ();
1067 my $subtotal_header = 0;
1068 my $subposition = 0;
1076 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1078 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1079 'departments' => 'ALL_DEPARTMENTS');
1082 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1083 $price_factors{$pfac->{id}} = $pfac;
1084 $pfac->{factor} *= 1;
1085 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1089 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1090 next unless $dept->{id} eq $form->{department_id};
1091 $form->{department} = $dept->{description};
1095 # sort items by partsgroup
1096 for $i (1 .. $form->{rowcount}) {
1098 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1099 $partsgroup = $form->{"partsgroup_$i"};
1101 push @partsgroup, [$i, $partsgroup];
1102 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1108 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1109 %projects_by_id = map { $_->id => $_ } @$projects;
1112 if ($projects_by_id{$form->{"globalproject_id"}}) {
1113 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1114 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1116 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1117 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1121 $form->{discount} = [];
1123 $form->{TEMPLATE_ARRAYS} = { };
1124 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1126 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1127 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1130 qw(runningnumber number description longdescription qty ship unit bin
1131 partnotes serialnumber reqdate sellprice listprice netprice
1132 discount p_discount discount_sub nodiscount_sub
1133 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1134 price_factor price_factor_name partsgroup weight lineweight);
1136 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1137 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1139 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1141 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1143 my $totalweight = 0;
1145 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1148 if ($item->[1] ne $sameitem) {
1149 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1150 $sameitem = $item->[1];
1152 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1155 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1157 if ($form->{"id_$i"} != 0) {
1159 # add number, description and qty to $form->{number}, ....
1161 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1162 $subtotal_header = $i;
1163 $position = int($position);
1166 } elsif ($subtotal_header) {
1168 $position = int($position);
1169 $position = $position.".".$subposition;
1171 $position = int($position);
1175 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1177 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1178 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1179 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1180 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1181 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1182 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1183 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1184 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1185 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1186 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1187 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1188 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1189 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1190 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1191 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1192 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1193 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1194 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1195 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1197 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1198 my ($dec) = ($sellprice =~ /\.(\d+)/);
1199 my $decimalplaces = max 2, length($dec);
1201 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1203 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1204 my $linetotal = $form->round_amount($linetotal_exact, 2);
1206 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1207 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1209 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1211 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1213 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1215 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1216 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1218 $linetotal = ($linetotal != 0) ? $linetotal : '';
1220 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1221 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1222 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1224 $form->{ordtotal} += $linetotal;
1225 $form->{nodiscount_total} += $nodiscount_linetotal;
1226 $form->{discount_total} += $discount;
1228 if ($subtotal_header) {
1229 $discount_subtotal += $linetotal;
1230 $nodiscount_subtotal += $nodiscount_linetotal;
1233 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1234 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1235 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1236 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1237 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1239 $discount_subtotal = 0;
1240 $nodiscount_subtotal = 0;
1241 $subtotal_header = 0;
1244 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1247 if (!$form->{"discount_$i"}) {
1248 $nodiscount += $linetotal;
1251 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1253 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1254 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1255 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1256 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1257 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1258 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1260 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1261 $totalweight += $lineweight;
1262 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1263 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1264 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1265 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1267 my ($taxamount, $taxbase);
1270 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1272 if ($form->{taxincluded}) {
1275 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1276 $taxbase = $linetotal / (1 + $taxrate);
1278 $taxamount = $linetotal * $taxrate;
1279 $taxbase = $linetotal;
1282 if ($taxamount != 0) {
1283 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1284 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1285 $taxbase{$accno} += $taxbase;
1289 $tax_rate = $taxrate * 100;
1290 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1292 if ($form->{"assembly_$i"}) {
1295 # get parts and push them onto the stack
1297 if ($form->{groupitems}) {
1298 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1300 $sortorder = qq|ORDER BY a.oid|;
1303 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1304 qq|pg.partsgroup | .
1305 qq|FROM assembly a | .
1306 qq| JOIN parts p ON (a.parts_id = p.id) | .
1307 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1308 qq| WHERE a.bom = '1' | .
1309 qq| AND a.id = ? | . $sortorder;
1310 @values = ($form->{"id_$i"});
1311 $sth = $dbh->prepare($query);
1312 $sth->execute(@values) || $form->dberror($query);
1314 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1315 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1316 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1317 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1318 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1321 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1322 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1327 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1328 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1329 for @{ $ic_cvar_configs };
1331 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1335 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1336 $form->{totalweight_nofmt} = $totalweight;
1337 my $defaults = AM->get_defaults();
1338 $form->{weightunit} = $defaults->{weightunit};
1341 foreach $item (sort keys %taxaccounts) {
1342 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1344 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1345 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1346 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1347 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1348 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1349 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1350 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1352 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1353 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1354 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1357 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1358 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1359 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1360 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1362 if($form->{taxincluded}) {
1363 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1364 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1366 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1367 $form->{subtotal_nofmt} = $form->{ordtotal};
1370 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1373 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1375 if ($form->{type} =~ /_quotation/) {
1376 $form->set_payment_options($myconfig, $form->{quodate});
1378 $form->set_payment_options($myconfig, $form->{orddate});
1381 $form->{username} = $myconfig->{name};
1385 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1386 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1388 $main::lxdebug->leave_sub();
1391 sub project_description {
1392 $main::lxdebug->enter_sub();
1394 my ($self, $dbh, $id) = @_;
1396 my $query = qq|SELECT description FROM project WHERE id = ?|;
1397 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1399 $main::lxdebug->leave_sub();
1410 OE.pm - Order entry module
1414 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>.
1420 =item retrieve_simple PARAMS
1422 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1424 my $order = retrieve_simple(id => 2);