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";
84 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
85 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
86 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
87 qq| o.transaction_description, | .
88 qq| o.marge_total, o.marge_percent, | .
89 qq| ex.$rate AS exchangerate, | .
90 qq| pr.projectnumber AS globalprojectnumber, | .
91 qq| e.name AS employee, s.name AS salesman, | .
92 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
93 qq| tz.description AS taxzone | .
94 $periodic_invoices_columns .
96 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
97 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
98 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
99 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
100 qq| AND ex.transdate = o.transdate) | .
101 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
102 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
103 qq|$periodic_invoices_joins | .
104 qq|WHERE (o.quotation = ?) |;
105 push(@values, $quotation);
107 my ($null, $split_department_id) = split /--/, $form->{department};
108 my $department_id = $form->{department_id} || $split_department_id;
109 if ($department_id) {
110 $query .= qq| AND o.department_id = ?|;
111 push(@values, $department_id);
114 if ($form->{"project_id"}) {
116 qq|AND ((globalproject_id = ?) OR EXISTS | .
117 qq| (SELECT * FROM orderitems oi | .
118 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
119 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
122 if ($form->{"projectnumber"}) {
124 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
125 SELECT * FROM orderitems oi
126 LEFT JOIN project proi ON proi.id = oi.project_id
127 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
130 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
133 if ($form->{"business_id"}) {
134 $query .= " AND ct.business_id = ?";
135 push(@values, $form->{"business_id"});
138 if ($form->{"${vc}_id"}) {
139 $query .= " AND o.${vc}_id = ?";
140 push(@values, $form->{"${vc}_id"});
142 } elsif ($form->{$vc}) {
143 $query .= " AND ct.name ILIKE ?";
144 push(@values, '%' . $form->{$vc} . '%');
147 if (!$main::auth->assert('sales_all_edit', 1)) {
148 $query .= " AND o.employee_id = (select id from employee where login= ?)";
149 push @values, $form->{login};
151 if ($form->{employee_id}) {
152 $query .= " AND o.employee_id = ?";
153 push @values, conv_i($form->{employee_id});
156 if ($form->{salesman_id}) {
157 $query .= " AND o.salesman_id = ?";
158 push @values, conv_i($form->{salesman_id});
161 if (!$form->{open} && !$form->{closed}) {
162 $query .= " AND o.id = 0";
163 } elsif (!($form->{open} && $form->{closed})) {
164 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
167 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
168 ($form->{"notdelivered"} ne $form->{"delivered"})) {
169 $query .= $form->{"delivered"} ?
170 " AND o.delivered " : " AND NOT o.delivered";
173 if ($form->{$ordnumber}) {
174 $query .= qq| AND o.$ordnumber ILIKE ?|;
175 push(@values, '%' . $form->{$ordnumber} . '%');
178 if ($form->{cusordnumber}) {
179 $query .= qq| AND o.cusordnumber ILIKE ?|;
180 push(@values, '%' . $form->{cusordnumber} . '%');
183 if($form->{transdatefrom}) {
184 $query .= qq| AND o.transdate >= ?|;
185 push(@values, conv_date($form->{transdatefrom}));
188 if($form->{transdateto}) {
189 $query .= qq| AND o.transdate <= ?|;
190 push(@values, conv_date($form->{transdateto}));
193 if($form->{reqdatefrom}) {
194 $query .= qq| AND o.reqdate >= ?|;
195 push(@values, conv_date($form->{reqdatefrom}));
198 if($form->{reqdateto}) {
199 $query .= qq| AND o.reqdate <= ?|;
200 push(@values, conv_date($form->{reqdateto}));
203 if ($form->{shippingpoint}) {
204 $query .= qq| AND o.shippingpoint ILIKE ?|;
205 push(@values, '%' . $form->{shippingpoint} . '%');
208 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
209 $query .= qq| AND tz.id = ?|;
210 push(@values, $form->{taxzone_id});
213 if ($form->{transaction_description}) {
214 $query .= qq| AND o.transaction_description ILIKE ?|;
215 push(@values, '%' . $form->{transaction_description} . '%');
218 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
219 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
220 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
223 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
224 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
225 my %allowed_sort_columns = (
226 "transdate" => "o.transdate",
227 "reqdate" => "o.reqdate",
229 "ordnumber" => "o.ordnumber",
230 "cusordnumber" => "o.cusordnumber",
231 "quonumber" => "o.quonumber",
233 "employee" => "e.name",
234 "salesman" => "s.name",
235 "shipvia" => "o.shipvia",
236 "transaction_description" => "o.transaction_description",
237 "shippingpoint" => "o.shippingpoint",
238 "taxzone" => "tz.description",
240 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
241 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
243 $query .= qq| ORDER by | . $sortorder;
245 my $sth = $dbh->prepare($query);
246 $sth->execute(@values) ||
247 $form->dberror($query . " (" . join(", ", @values) . ")");
251 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
252 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
253 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
254 $id{ $ref->{id} } = $ref->{id};
259 $main::lxdebug->leave_sub();
262 sub transactions_for_todo_list {
263 $main::lxdebug->enter_sub();
268 my $myconfig = \%main::myconfig;
269 my $form = $main::form;
271 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
273 my $query = qq|SELECT id FROM employee WHERE login = ?|;
274 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
277 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
278 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
283 LEFT JOIN customer c ON (oe.customer_id = c.id)
284 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
285 LEFT JOIN employee e ON (oe.employee_id = e.id)
286 WHERE (COALESCE(quotation, FALSE) = TRUE)
287 AND (COALESCE(closed, FALSE) = FALSE)
288 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
289 AND NOT (oe.reqdate ISNULL)
290 AND (oe.reqdate < current_date)
293 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
295 $main::lxdebug->leave_sub();
301 $main::lxdebug->enter_sub();
303 my ($self, $myconfig, $form) = @_;
305 # connect to database, turn off autocommit
306 my $dbh = $form->get_standard_dbh;
308 my ($query, @values, $sth, $null);
309 my $exchangerate = 0;
311 my $all_units = AM->retrieve_units($myconfig, $form);
312 $form->{all_units} = $all_units;
314 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
317 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
318 unless ($form->{employee_id}) {
319 $form->get_employee($dbh);
322 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
324 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
325 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
326 $form->{$number_field} ||= $trans_number->create_unique;
329 $query = qq|DELETE FROM custom_variables
330 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
331 AND (sub_module = 'orderitems')
332 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
333 do_query($form, $dbh, $query, $form->{id});
335 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
336 do_query($form, $dbh, $query, $form->{id});
338 $query = qq|DELETE FROM shipto | .
339 qq|WHERE trans_id = ? AND module = 'OE'|;
340 do_query($form, $dbh, $query, $form->{id});
344 $query = qq|SELECT nextval('id')|;
345 ($form->{id}) = selectrow_query($form, $dbh, $query);
347 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
348 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
366 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
367 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
370 for my $i (1 .. $form->{rowcount}) {
372 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
374 if ($form->{"id_$i"}) {
377 $query = qq|SELECT unit FROM parts WHERE id = ?|;
378 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
381 if (defined($all_units->{$item_unit}->{factor}) &&
382 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
383 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
385 my $baseqty = $form->{"qty_$i"} * $basefactor;
387 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
388 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
390 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
392 # set values to 0 if nothing entered
393 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
395 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
396 $fxsellprice = $form->{"sellprice_$i"};
398 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
400 my $decimalplaces = ($dec > 2) ? $dec : 2;
402 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
403 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
405 $form->{"inventory_accno_$i"} *= 1;
406 $form->{"expense_accno_$i"} *= 1;
408 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
409 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
411 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
415 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
417 if ($form->{taxincluded}) {
418 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
419 $taxbase = $linetotal - $taxamount;
421 # we are not keeping a natural price, do not round
422 $form->{"sellprice_$i"} =
423 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
425 $taxamount = $linetotal * $taxrate;
426 $taxbase = $linetotal;
429 if ($form->round_amount($taxrate, 7) == 0) {
430 if ($form->{taxincluded}) {
431 foreach my $item (@taxaccounts) {
432 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
433 $taxaccounts{$item} += $taxamount;
434 $taxdiff += $taxamount;
435 $taxbase{$item} += $taxbase;
437 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
439 foreach my $item (@taxaccounts) {
440 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
441 $taxbase{$item} += $taxbase;
445 foreach my $item (@taxaccounts) {
446 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
447 $taxbase{$item} += $taxbase;
451 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
453 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
455 # Get pricegroup_id and save it. Unfortunately the interface
456 # also uses ID "0" for signalling that none is selected, but "0"
457 # must not be stored in the database. Therefore we cannot simply
459 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
461 $pricegroup_id = undef if !$pricegroup_id;
463 # save detail record in orderitems table
464 my $orderitems_id = $form->{"orderitems_id_$i"};
465 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
468 $query = qq|INSERT INTO orderitems (
469 id, trans_id, parts_id, description, longdescription, qty, base_qty,
470 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
471 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
472 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
473 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
474 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
476 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
477 $form->{"description_$i"}, $form->{"longdescription_$i"},
478 $form->{"qty_$i"}, $baseqty,
479 $fxsellprice, $form->{"discount_$i"},
480 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
481 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
482 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
483 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
484 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
485 $form->{"lastcost_$i"},
486 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
487 conv_i($form->{"marge_price_factor_$i"}));
488 do_query($form, $dbh, $query, @values);
490 $form->{"sellprice_$i"} = $fxsellprice;
491 $form->{"discount_$i"} *= 100;
493 CVar->save_custom_variables(module => 'IC',
494 sub_module => 'orderitems',
495 trans_id => $orderitems_id,
496 configs => $ic_cvar_configs,
498 name_prefix => 'ic_',
499 name_postfix => "_$i",
504 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
508 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
510 $amount = $form->round_amount($netamount + $tax, 2);
511 $netamount = $form->round_amount($netamount, 2);
513 if ($form->{currency} eq $form->{defaultcurrency}) {
514 $form->{exchangerate} = 1;
516 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
519 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
521 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
523 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
528 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
529 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
530 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
531 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
532 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
533 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
536 @values = ($form->{ordnumber} || '', $form->{quonumber},
537 $form->{cusordnumber}, conv_date($form->{transdate}),
538 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
539 $amount, $netamount, conv_date($reqdate),
540 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
541 $form->{shipvia}, $form->{notes}, $form->{intnotes},
542 $form->{currency}, $form->{closed} ? 't' : 'f',
543 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
544 $quotation, conv_i($form->{department_id}),
545 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
546 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
547 conv_i($form->{delivery_vendor_id}),
548 conv_i($form->{delivery_customer_id}),
549 conv_i($form->{delivery_term_id}),
550 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
551 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
552 $form->{transaction_description},
553 $form->{marge_total} * 1, $form->{marge_percent} * 1,
554 conv_i($form->{id}));
555 do_query($form, $dbh, $query, @values);
557 $form->{ordtotal} = $amount;
560 $form->{name} = $form->{ $form->{vc} };
561 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
563 if (!$form->{shipto_id}) {
564 $form->add_shipto($dbh, $form->{id}, "OE");
567 # save printed, emailed, queued
568 $form->save_status($dbh);
570 # Link this record to the records it was created from.
571 $form->{convert_from_oe_ids} =~ s/^\s+//;
572 $form->{convert_from_oe_ids} =~ s/\s+$//;
573 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
574 delete $form->{convert_from_oe_ids};
576 if (scalar @convert_from_oe_ids) {
577 RecordLinks->create_links('dbh' => $dbh,
579 'from_table' => 'oe',
580 'from_ids' => \@convert_from_oe_ids,
582 'to_id' => $form->{id},
585 $self->_close_quotations_rfqs('dbh' => $dbh,
586 'from_id' => \@convert_from_oe_ids,
587 'to_id' => $form->{id});
590 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
591 if ($form->{vc} eq 'customer') {
592 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
594 if ($form->{vc} eq 'vendor') {
595 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
599 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
600 "quonumber" : "ordnumber"};
602 Common::webdav_folder($form);
604 my $rc = $dbh->commit;
606 $self->save_periodic_invoices_config(dbh => $dbh,
607 oe_id => $form->{id},
608 config_yaml => $form->{periodic_invoices_config})
609 if ($form->{type} eq 'sales_order');
611 $main::lxdebug->leave_sub();
616 sub save_periodic_invoices_config {
617 my ($self, %params) = @_;
619 return if !$params{oe_id};
621 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
622 return if 'HASH' ne ref $config;
624 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
625 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
626 $obj->update_attributes(%{ $config });
629 sub load_periodic_invoice_config {
633 delete $form->{periodic_invoices_config};
636 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
639 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
640 print printer_id copies) };
641 $form->{periodic_invoices_config} = YAML::Dump($config);
646 sub _close_quotations_rfqs {
647 $main::lxdebug->enter_sub();
652 Common::check_params(\%params, qw(from_id to_id));
654 my $myconfig = \%main::myconfig;
655 my $form = $main::form;
657 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
659 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
660 my $sth = prepare_query($form, $dbh, $query);
662 do_statement($form, $sth, $query, conv_i($params{to_id}));
664 my ($quotation) = $sth->fetchrow_array();
667 $main::lxdebug->leave_sub();
673 foreach my $from_id (@{ $params{from_id} }) {
674 $from_id = conv_i($from_id);
675 do_statement($form, $sth, $query, $from_id);
676 ($quotation) = $sth->fetchrow_array();
677 push @close_ids, $from_id if ($quotation);
682 if (scalar @close_ids) {
683 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
684 do_query($form, $dbh, $query, @close_ids);
686 $dbh->commit() unless ($params{dbh});
689 $main::lxdebug->leave_sub();
693 $main::lxdebug->enter_sub();
695 my ($self, $myconfig, $form) = @_;
697 my $rc = SL::DB::Order->new->db->with_transaction(sub {
698 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
700 SL::DB::Order->new(id => $form->{id})->delete;
702 my $spool = $::lx_office_conf{paths}->{spool};
703 unlink map { "$spool/$_" } @spoolfiles if $spool;
708 $main::lxdebug->leave_sub();
714 $main::lxdebug->enter_sub();
716 my ($self, $myconfig, $form) = @_;
718 # connect to database
719 my $dbh = $form->get_standard_dbh;
721 my ($query, $query_add, @values, @ids, $sth);
723 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
725 push @ids, $form->{"trans_id_$_"}
726 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
727 } (1 .. $form->{"rowcount"});
729 if ($form->{rowcount} && scalar @ids) {
730 $form->{convert_from_oe_ids} = join ' ', @ids;
733 # if called in multi id mode, and still only got one id, switch back to single id
734 if ($form->{"rowcount"} and $#ids == 0) {
735 $form->{"id"} = $ids[0];
739 # and remember for the rest of the function
740 my $is_collective_order = scalar @ids;
743 my $wday = (localtime(time))[6];
744 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
745 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
748 # get default accounts
749 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
750 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
751 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
752 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
753 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
756 my $ref = selectfirst_hashref_query($form, $dbh, $query);
757 map { $form->{$_} = $ref->{$_} } keys %$ref;
759 $form->{currency} = $form->get_default_currency($myconfig);
761 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
762 # we come from invoices, feel free.
763 $form->{reqdate} = $form->{deliverydate}
764 if ( $form->{deliverydate}
765 and $form->{callback} =~ /action=ar_transactions/);
767 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
769 if ($form->{id} or @ids) {
771 # retrieve order for single id
772 # NOTE: this query is intended to fetch all information only ONCE.
773 # so if any of these infos is important (or even different) for any item,
774 # it will be killed out and then has to be fetched from the item scope query further down
776 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
777 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
778 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
779 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
780 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
781 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
782 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
783 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
785 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
786 LEFT JOIN employee e ON (o.employee_id = e.id)
787 LEFT JOIN department d ON (o.department_id = d.id) | .
790 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
792 @values = $form->{id} ? ($form->{id}) : @ids;
793 $sth = prepare_execute_query($form, $dbh, $query, @values);
795 $ref = $sth->fetchrow_hashref("NAME_lc");
798 map { $form->{$_} = $ref->{$_} } keys %$ref;
800 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
802 # set all entries for multiple ids blank that yield different information
803 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
804 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
808 # if not given, fill transdate with current_date
809 $form->{transdate} = $form->current_date($myconfig)
810 unless $form->{transdate};
814 if ($form->{delivery_customer_id}) {
815 $query = qq|SELECT name FROM customer WHERE id = ?|;
816 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
819 if ($form->{delivery_vendor_id}) {
820 $query = qq|SELECT name FROM customer WHERE id = ?|;
821 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
824 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
826 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
827 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
829 $ref = $sth->fetchrow_hashref("NAME_lc");
831 map { $form->{$_} = $ref->{$_} } keys %$ref;
834 # get printed, emailed and queued
835 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
836 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
838 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
839 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
840 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
841 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
844 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
847 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
849 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
851 # retrieve individual items
852 # this query looks up all information about the items
853 # stuff different from the whole will not be overwritten, but saved with a suffix.
855 qq|SELECT o.id AS orderitems_id,
856 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
857 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
858 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
859 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
860 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
861 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
862 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
863 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
864 o.price_factor_id, o.price_factor, o.marge_price_factor,
865 pr.projectnumber, p.formel,
866 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
868 JOIN parts p ON (o.parts_id = p.id)
869 JOIN oe ON (o.trans_id = oe.id)
870 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
871 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
872 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
873 LEFT JOIN project pr ON (o.project_id = pr.id)
874 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
876 ? qq|WHERE o.trans_id = ?|
877 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
880 @ids = $form->{id} ? ($form->{id}) : @ids;
881 $sth = prepare_execute_query($form, $dbh, $query, @values);
883 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
884 # Retrieve custom variables.
885 my $cvars = CVar->get_custom_variables(dbh => $dbh,
887 sub_module => 'orderitems',
888 trans_id => $ref->{orderitems_id},
890 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
893 if (!$ref->{"part_inventory_accno_id"}) {
894 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
896 delete($ref->{"part_inventory_accno_id"});
898 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
899 # unless already present there
900 # remove _oe entries afterwards
901 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
902 qw|ordnumber transdate cusordnumber|
904 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
908 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
910 qq|SELECT accno AS inventory_accno, | .
911 qq| new_chart_id AS inventory_new_chart, | .
912 qq| date($transdate) - valid_from AS inventory_valid | .
913 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
914 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
915 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
918 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
920 qq|SELECT accno AS income_accno, | .
921 qq| new_chart_id AS income_new_chart, | .
922 qq| date($transdate) - valid_from AS income_valid | .
923 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
924 ($ref->{income_accno}, $ref->{income_new_chart},
925 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
928 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
930 qq|SELECT accno AS expense_accno, | .
931 qq| new_chart_id AS expense_new_chart, | .
932 qq| date($transdate) - valid_from AS expense_valid | .
933 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
934 ($ref->{expense_accno}, $ref->{expense_new_chart},
935 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
938 # delete orderitems_id in collective orders, so that they get cloned no matter what
939 delete $ref->{orderitems_id} if (@ids);
941 # get tax rates and description
942 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
944 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
945 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
946 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
947 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
948 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
949 qq|ORDER BY c.accno|;
950 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
951 $ref->{taxaccounts} = "";
953 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
954 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
958 $ref->{taxaccounts} .= "$ptr->{accno} ";
959 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
960 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
961 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
962 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
963 $form->{taxaccounts} .= "$ptr->{accno} ";
968 chop $ref->{taxaccounts};
970 push @{ $form->{form_details} }, $ref;
978 $form->lastname_used($dbh, $myconfig, $form->{vc})
979 unless $form->{"$form->{vc}_id"};
983 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
985 Common::webdav_folder($form);
987 $self->load_periodic_invoice_config($form);
989 my $rc = $dbh->commit;
991 $main::lxdebug->leave_sub();
996 sub retrieve_simple {
997 $main::lxdebug->enter_sub();
1002 Common::check_params(\%params, qw(id));
1004 my $myconfig = \%main::myconfig;
1005 my $form = $main::form;
1007 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1009 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1010 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1012 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1013 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1015 $main::lxdebug->leave_sub();
1021 $main::lxdebug->enter_sub();
1023 my ($self, $myconfig, $form) = @_;
1025 # connect to database
1026 my $dbh = $form->get_standard_dbh;
1032 my $nodiscount_subtotal = 0;
1033 my $discount_subtotal = 0;
1036 my @partsgroup = ();
1039 my $subtotal_header = 0;
1040 my $subposition = 0;
1048 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1050 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1051 'departments' => 'ALL_DEPARTMENTS');
1054 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1055 $price_factors{$pfac->{id}} = $pfac;
1056 $pfac->{factor} *= 1;
1057 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1061 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1062 next unless $dept->{id} eq $form->{department_id};
1063 $form->{department} = $dept->{description};
1067 # sort items by partsgroup
1068 for $i (1 .. $form->{rowcount}) {
1070 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1071 $partsgroup = $form->{"partsgroup_$i"};
1073 push @partsgroup, [$i, $partsgroup];
1074 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1080 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1081 %projects_by_id = map { $_->id => $_ } @$projects;
1084 if ($projects_by_id{$form->{"globalproject_id"}}) {
1085 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1086 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1088 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1089 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1093 $form->{discount} = [];
1095 $form->{TEMPLATE_ARRAYS} = { };
1096 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1098 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1099 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1102 qw(runningnumber number description longdescription qty ship unit bin
1103 partnotes serialnumber reqdate sellprice listprice netprice
1104 discount p_discount discount_sub nodiscount_sub
1105 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1106 price_factor price_factor_name partsgroup weight lineweight);
1108 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1109 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1111 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1113 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1115 my $totalweight = 0;
1117 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1120 if ($item->[1] ne $sameitem) {
1121 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1122 $sameitem = $item->[1];
1124 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1127 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1129 if ($form->{"id_$i"} != 0) {
1131 # add number, description and qty to $form->{number}, ....
1133 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1134 $subtotal_header = $i;
1135 $position = int($position);
1138 } elsif ($subtotal_header) {
1140 $position = int($position);
1141 $position = $position.".".$subposition;
1143 $position = int($position);
1147 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1149 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1150 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1151 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1152 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1153 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1154 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1155 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1156 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1157 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1158 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1159 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1160 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1161 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1162 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1163 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1164 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1165 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1166 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1167 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1169 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1170 my ($dec) = ($sellprice =~ /\.(\d+)/);
1171 my $decimalplaces = max 2, length($dec);
1173 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1175 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1176 my $linetotal = $form->round_amount($linetotal_exact, 2);
1178 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1179 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1181 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1183 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1185 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1187 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1188 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1190 $linetotal = ($linetotal != 0) ? $linetotal : '';
1192 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1193 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1194 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1196 $form->{ordtotal} += $linetotal;
1197 $form->{nodiscount_total} += $nodiscount_linetotal;
1198 $form->{discount_total} += $discount;
1200 if ($subtotal_header) {
1201 $discount_subtotal += $linetotal;
1202 $nodiscount_subtotal += $nodiscount_linetotal;
1205 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1206 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1207 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1208 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1209 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1211 $discount_subtotal = 0;
1212 $nodiscount_subtotal = 0;
1213 $subtotal_header = 0;
1216 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1219 if (!$form->{"discount_$i"}) {
1220 $nodiscount += $linetotal;
1223 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1225 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1226 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1227 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1228 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1229 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1230 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1232 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1233 $totalweight += $lineweight;
1234 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1235 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1236 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1237 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1239 my ($taxamount, $taxbase);
1242 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1244 if ($form->{taxincluded}) {
1247 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1248 $taxbase = $linetotal / (1 + $taxrate);
1250 $taxamount = $linetotal * $taxrate;
1251 $taxbase = $linetotal;
1254 if ($taxamount != 0) {
1255 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1256 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1257 $taxbase{$accno} += $taxbase;
1261 $tax_rate = $taxrate * 100;
1262 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1264 if ($form->{"assembly_$i"}) {
1267 # get parts and push them onto the stack
1269 if ($form->{groupitems}) {
1270 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1272 $sortorder = qq|ORDER BY a.oid|;
1275 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1276 qq|pg.partsgroup | .
1277 qq|FROM assembly a | .
1278 qq| JOIN parts p ON (a.parts_id = p.id) | .
1279 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1280 qq| WHERE a.bom = '1' | .
1281 qq| AND a.id = ? | . $sortorder;
1282 @values = ($form->{"id_$i"});
1283 $sth = $dbh->prepare($query);
1284 $sth->execute(@values) || $form->dberror($query);
1286 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1287 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1288 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1289 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1290 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1293 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1294 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1299 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1300 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1301 for @{ $ic_cvar_configs };
1303 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1307 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1308 $form->{totalweight_nofmt} = $totalweight;
1309 my $defaults = AM->get_defaults();
1310 $form->{weightunit} = $defaults->{weightunit};
1313 foreach $item (sort keys %taxaccounts) {
1314 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1316 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1317 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1318 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1319 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1320 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1321 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1322 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1324 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1325 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1326 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1329 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1330 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1331 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1332 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1334 if($form->{taxincluded}) {
1335 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1336 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1338 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1339 $form->{subtotal_nofmt} = $form->{ordtotal};
1342 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1345 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1347 if ($form->{type} =~ /_quotation/) {
1348 $form->set_payment_options($myconfig, $form->{quodate});
1350 $form->set_payment_options($myconfig, $form->{orddate});
1353 $form->{username} = $myconfig->{name};
1357 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1358 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1360 $main::lxdebug->leave_sub();
1363 sub project_description {
1364 $main::lxdebug->enter_sub();
1366 my ($self, $dbh, $id) = @_;
1368 my $query = qq|SELECT description FROM project WHERE id = ?|;
1369 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1371 $main::lxdebug->leave_sub();
1382 OE.pm - Order entry module
1386 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>.
1392 =item retrieve_simple PARAMS
1394 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1396 my $order = retrieve_simple(id => 2);