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;
53 $main::lxdebug->enter_sub();
55 my ($self, $myconfig, $form) = @_;
58 my $dbh = $form->get_standard_dbh;
61 my $ordnumber = 'ordnumber';
67 my ($periodic_invoices_columns, $periodic_invoices_joins);
69 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
71 if ($form->{type} =~ /_quotation$/) {
73 $ordnumber = 'quonumber';
75 } elsif ($form->{type} eq 'sales_order') {
76 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
77 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
80 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
83 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
84 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
85 qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
86 qq| o.transaction_description, | .
87 qq| o.marge_total, o.marge_percent, | .
88 qq| ex.$rate AS exchangerate, | .
89 qq| pr.projectnumber AS globalprojectnumber, | .
90 qq| e.name AS employee, s.name AS salesman, | .
91 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
92 qq| tz.description AS taxzone | .
93 $periodic_invoices_columns .
95 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
96 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
97 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
98 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
99 qq| AND ex.transdate = o.transdate) | .
100 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
101 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
102 qq|$periodic_invoices_joins | .
103 qq|WHERE (o.quotation = ?) |;
104 push(@values, $quotation);
106 my ($null, $split_department_id) = split /--/, $form->{department};
107 my $department_id = $form->{department_id} || $split_department_id;
108 if ($department_id) {
109 $query .= qq| AND o.department_id = ?|;
110 push(@values, $department_id);
113 if ($form->{"project_id"}) {
115 qq|AND ((globalproject_id = ?) OR EXISTS | .
116 qq| (SELECT * FROM orderitems oi | .
117 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
118 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
121 if ($form->{"projectnumber"}) {
123 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
124 SELECT * FROM orderitems oi
125 LEFT JOIN project proi ON proi.id = oi.project_id
126 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
129 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
132 if ($form->{"business_id"}) {
133 $query .= " AND ct.business_id = ?";
134 push(@values, $form->{"business_id"});
137 if ($form->{"${vc}_id"}) {
138 $query .= " AND o.${vc}_id = ?";
139 push(@values, $form->{"${vc}_id"});
141 } elsif ($form->{$vc}) {
142 $query .= " AND ct.name ILIKE ?";
143 push(@values, '%' . $form->{$vc} . '%');
146 if (!$main::auth->assert('sales_all_edit', 1)) {
147 $query .= " AND o.employee_id = (select id from employee where login= ?)";
148 push @values, $form->{login};
150 if ($form->{employee_id}) {
151 $query .= " AND o.employee_id = ?";
152 push @values, conv_i($form->{employee_id});
155 if ($form->{salesman_id}) {
156 $query .= " AND o.salesman_id = ?";
157 push @values, conv_i($form->{salesman_id});
160 if (!$form->{open} && !$form->{closed}) {
161 $query .= " AND o.id = 0";
162 } elsif (!($form->{open} && $form->{closed})) {
163 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
166 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
167 ($form->{"notdelivered"} ne $form->{"delivered"})) {
168 $query .= $form->{"delivered"} ?
169 " AND o.delivered " : " AND NOT o.delivered";
172 if ($form->{$ordnumber}) {
173 $query .= qq| AND o.$ordnumber ILIKE ?|;
174 push(@values, '%' . $form->{$ordnumber} . '%');
177 if($form->{transdatefrom}) {
178 $query .= qq| AND o.transdate >= ?|;
179 push(@values, conv_date($form->{transdatefrom}));
182 if($form->{transdateto}) {
183 $query .= qq| AND o.transdate <= ?|;
184 push(@values, conv_date($form->{transdateto}));
187 if($form->{reqdatefrom}) {
188 $query .= qq| AND o.reqdate >= ?|;
189 push(@values, conv_date($form->{reqdatefrom}));
192 if($form->{reqdateto}) {
193 $query .= qq| AND o.reqdate <= ?|;
194 push(@values, conv_date($form->{reqdateto}));
197 if ($form->{shippingpoint}) {
198 $query .= qq| AND o.shippingpoint ILIKE ?|;
199 push(@values, '%' . $form->{shippingpoint} . '%');
202 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
203 $query .= qq| AND tz.id = ?|;
204 push(@values, $form->{taxzone_id});
207 if ($form->{transaction_description}) {
208 $query .= qq| AND o.transaction_description ILIKE ?|;
209 push(@values, '%' . $form->{transaction_description} . '%');
212 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
213 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
214 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
217 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
218 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
219 my %allowed_sort_columns = (
220 "transdate" => "o.transdate",
221 "reqdate" => "o.reqdate",
223 "ordnumber" => "o.ordnumber",
224 "quonumber" => "o.quonumber",
226 "employee" => "e.name",
227 "salesman" => "s.name",
228 "shipvia" => "o.shipvia",
229 "transaction_description" => "o.transaction_description",
230 "shippingpoint" => "o.shippingpoint",
231 "taxzone" => "tz.description",
233 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
234 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
236 $query .= qq| ORDER by | . $sortorder;
238 my $sth = $dbh->prepare($query);
239 $sth->execute(@values) ||
240 $form->dberror($query . " (" . join(", ", @values) . ")");
244 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
245 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
246 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
247 $id{ $ref->{id} } = $ref->{id};
252 $main::lxdebug->leave_sub();
255 sub transactions_for_todo_list {
256 $main::lxdebug->enter_sub();
261 my $myconfig = \%main::myconfig;
262 my $form = $main::form;
264 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
266 my $query = qq|SELECT id FROM employee WHERE login = ?|;
267 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
270 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
271 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
276 LEFT JOIN customer c ON (oe.customer_id = c.id)
277 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
278 LEFT JOIN employee e ON (oe.employee_id = e.id)
279 WHERE (COALESCE(quotation, FALSE) = TRUE)
280 AND (COALESCE(closed, FALSE) = FALSE)
281 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
282 AND NOT (oe.reqdate ISNULL)
283 AND (oe.reqdate < current_date)
286 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
288 $main::lxdebug->leave_sub();
294 $main::lxdebug->enter_sub();
296 my ($self, $myconfig, $form) = @_;
298 # connect to database, turn off autocommit
299 my $dbh = $form->get_standard_dbh;
301 my ($query, @values, $sth, $null);
302 my $exchangerate = 0;
304 my $all_units = AM->retrieve_units($myconfig, $form);
305 $form->{all_units} = $all_units;
307 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
310 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
311 unless ($form->{employee_id}) {
312 $form->get_employee($dbh);
315 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
318 $query = qq|DELETE FROM custom_variables
319 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
320 AND (sub_module = 'orderitems')
321 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
322 do_query($form, $dbh, $query, $form->{id});
324 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
325 do_query($form, $dbh, $query, $form->{id});
327 $query = qq|DELETE FROM shipto | .
328 qq|WHERE trans_id = ? AND module = 'OE'|;
329 do_query($form, $dbh, $query, $form->{id});
333 $query = qq|SELECT nextval('id')|;
334 ($form->{id}) = selectrow_query($form, $dbh, $query);
336 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
337 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
355 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
356 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
359 for my $i (1 .. $form->{rowcount}) {
361 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
363 if ($form->{"id_$i"}) {
366 $query = qq|SELECT unit FROM parts WHERE id = ?|;
367 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
370 if (defined($all_units->{$item_unit}->{factor}) &&
371 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
372 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
374 my $baseqty = $form->{"qty_$i"} * $basefactor;
376 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
377 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
379 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
381 # set values to 0 if nothing entered
382 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
384 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
385 $fxsellprice = $form->{"sellprice_$i"};
387 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
389 my $decimalplaces = ($dec > 2) ? $dec : 2;
391 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
392 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
394 $form->{"inventory_accno_$i"} *= 1;
395 $form->{"expense_accno_$i"} *= 1;
397 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
398 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
400 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
404 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
406 if ($form->{taxincluded}) {
407 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
408 $taxbase = $linetotal - $taxamount;
410 # we are not keeping a natural price, do not round
411 $form->{"sellprice_$i"} =
412 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
414 $taxamount = $linetotal * $taxrate;
415 $taxbase = $linetotal;
418 if ($form->round_amount($taxrate, 7) == 0) {
419 if ($form->{taxincluded}) {
420 foreach my $item (@taxaccounts) {
421 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
422 $taxaccounts{$item} += $taxamount;
423 $taxdiff += $taxamount;
424 $taxbase{$item} += $taxbase;
426 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
428 foreach my $item (@taxaccounts) {
429 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
430 $taxbase{$item} += $taxbase;
434 foreach my $item (@taxaccounts) {
435 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
436 $taxbase{$item} += $taxbase;
440 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
442 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
444 # Get pricegroup_id and save it. Unfortunately the interface
445 # also uses ID "0" for signalling that none is selected, but "0"
446 # must not be stored in the database. Therefore we cannot simply
448 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
450 $pricegroup_id = undef if !$pricegroup_id;
452 # save detail record in orderitems table
453 my $orderitems_id = $form->{"orderitems_id_$i"};
454 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
457 $query = qq|INSERT INTO orderitems (
458 id, trans_id, parts_id, description, longdescription, qty, base_qty,
459 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
460 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
461 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
462 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
463 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
465 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
466 $form->{"description_$i"}, $form->{"longdescription_$i"},
467 $form->{"qty_$i"}, $baseqty,
468 $fxsellprice, $form->{"discount_$i"},
469 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
470 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
471 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
472 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
473 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
474 $form->{"lastcost_$i"},
475 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
476 conv_i($form->{"marge_price_factor_$i"}));
477 do_query($form, $dbh, $query, @values);
479 $form->{"sellprice_$i"} = $fxsellprice;
480 $form->{"discount_$i"} *= 100;
482 CVar->save_custom_variables(module => 'IC',
483 sub_module => 'orderitems',
484 trans_id => $orderitems_id,
485 configs => $ic_cvar_configs,
487 name_prefix => 'ic_',
488 name_postfix => "_$i",
493 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
497 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
499 $amount = $form->round_amount($netamount + $tax, 2);
500 $netamount = $form->round_amount($netamount, 2);
502 if ($form->{currency} eq $form->{defaultcurrency}) {
503 $form->{exchangerate} = 1;
505 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
508 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
510 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
512 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
517 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
518 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
519 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
520 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
521 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
522 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
525 @values = ($form->{ordnumber} || '', $form->{quonumber},
526 $form->{cusordnumber}, conv_date($form->{transdate}),
527 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
528 $amount, $netamount, conv_date($reqdate),
529 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
530 $form->{shipvia}, $form->{notes}, $form->{intnotes},
531 $form->{currency}, $form->{closed} ? 't' : 'f',
532 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
533 $quotation, conv_i($form->{department_id}),
534 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
535 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
536 conv_i($form->{delivery_vendor_id}),
537 conv_i($form->{delivery_customer_id}),
538 conv_i($form->{delivery_term_id}),
539 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
540 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
541 $form->{transaction_description},
542 $form->{marge_total} * 1, $form->{marge_percent} * 1,
543 conv_i($form->{id}));
544 do_query($form, $dbh, $query, @values);
546 $form->{ordtotal} = $amount;
549 $form->{name} = $form->{ $form->{vc} };
550 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
552 if (!$form->{shipto_id}) {
553 $form->add_shipto($dbh, $form->{id}, "OE");
556 # save printed, emailed, queued
557 $form->save_status($dbh);
559 # Link this record to the records it was created from.
560 $form->{convert_from_oe_ids} =~ s/^\s+//;
561 $form->{convert_from_oe_ids} =~ s/\s+$//;
562 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
563 delete $form->{convert_from_oe_ids};
565 if (scalar @convert_from_oe_ids) {
566 RecordLinks->create_links('dbh' => $dbh,
568 'from_table' => 'oe',
569 'from_ids' => \@convert_from_oe_ids,
571 'to_id' => $form->{id},
574 $self->_close_quotations_rfqs('dbh' => $dbh,
575 'from_id' => \@convert_from_oe_ids,
576 'to_id' => $form->{id});
579 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
580 if ($form->{vc} eq 'customer') {
581 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
583 if ($form->{vc} eq 'vendor') {
584 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
588 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
589 "quonumber" : "ordnumber"};
591 Common::webdav_folder($form);
593 my $rc = $dbh->commit;
595 $self->save_periodic_invoices_config(dbh => $dbh,
596 oe_id => $form->{id},
597 config_yaml => $form->{periodic_invoices_config})
598 if ($form->{type} eq 'sales_order');
600 $main::lxdebug->leave_sub();
605 sub save_periodic_invoices_config {
606 my ($self, %params) = @_;
608 return if !$params{oe_id};
610 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
611 return if 'HASH' ne ref $config;
613 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
614 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
615 $obj->update_attributes(%{ $config });
618 sub load_periodic_invoice_config {
622 delete $form->{periodic_invoices_config};
625 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
628 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
629 print printer_id copies) };
630 $form->{periodic_invoices_config} = YAML::Dump($config);
635 sub _close_quotations_rfqs {
636 $main::lxdebug->enter_sub();
641 Common::check_params(\%params, qw(from_id to_id));
643 my $myconfig = \%main::myconfig;
644 my $form = $main::form;
646 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
648 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
649 my $sth = prepare_query($form, $dbh, $query);
651 do_statement($form, $sth, $query, conv_i($params{to_id}));
653 my ($quotation) = $sth->fetchrow_array();
656 $main::lxdebug->leave_sub();
662 foreach my $from_id (@{ $params{from_id} }) {
663 $from_id = conv_i($from_id);
664 do_statement($form, $sth, $query, $from_id);
665 ($quotation) = $sth->fetchrow_array();
666 push @close_ids, $from_id if ($quotation);
671 if (scalar @close_ids) {
672 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
673 do_query($form, $dbh, $query, @close_ids);
675 $dbh->commit() unless ($params{dbh});
678 $main::lxdebug->leave_sub();
682 $main::lxdebug->enter_sub();
684 my ($self, $myconfig, $form) = @_;
686 my $rc = SL::DB::Order->new->db->with_transaction(sub {
687 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
689 SL::DB::Order->new(id => $form->{id})->delete;
691 my $spool = $::lx_office_conf{paths}->{spool};
692 unlink map { "$spool/$_" } @spoolfiles if $spool;
697 $main::lxdebug->leave_sub();
703 $main::lxdebug->enter_sub();
705 my ($self, $myconfig, $form) = @_;
707 # connect to database
708 my $dbh = $form->get_standard_dbh;
710 my ($query, $query_add, @values, @ids, $sth);
712 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
714 push @ids, $form->{"trans_id_$_"}
715 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
716 } (1 .. $form->{"rowcount"});
718 if ($form->{rowcount} && scalar @ids) {
719 $form->{convert_from_oe_ids} = join ' ', @ids;
722 # if called in multi id mode, and still only got one id, switch back to single id
723 if ($form->{"rowcount"} and $#ids == 0) {
724 $form->{"id"} = $ids[0];
728 # and remember for the rest of the function
729 my $is_collective_order = scalar @ids;
732 my $wday = (localtime(time))[6];
733 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
734 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
737 # get default accounts
738 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
739 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
740 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
741 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
742 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
745 my $ref = selectfirst_hashref_query($form, $dbh, $query);
746 map { $form->{$_} = $ref->{$_} } keys %$ref;
748 $form->{currency} = $form->get_default_currency($myconfig);
750 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
751 # we come from invoices, feel free.
752 $form->{reqdate} = $form->{deliverydate}
753 if ( $form->{deliverydate}
754 and $form->{callback} =~ /action=ar_transactions/);
756 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
758 if ($form->{id} or @ids) {
760 # retrieve order for single id
761 # NOTE: this query is intended to fetch all information only ONCE.
762 # so if any of these infos is important (or even different) for any item,
763 # it will be killed out and then has to be fetched from the item scope query further down
765 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
766 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
767 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
768 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
769 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
770 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
771 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
772 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
774 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
775 LEFT JOIN employee e ON (o.employee_id = e.id)
776 LEFT JOIN department d ON (o.department_id = d.id) | .
779 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
781 @values = $form->{id} ? ($form->{id}) : @ids;
782 $sth = prepare_execute_query($form, $dbh, $query, @values);
784 $ref = $sth->fetchrow_hashref("NAME_lc");
787 map { $form->{$_} = $ref->{$_} } keys %$ref;
789 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
791 # set all entries for multiple ids blank that yield different information
792 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
793 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
797 # if not given, fill transdate with current_date
798 $form->{transdate} = $form->current_date($myconfig)
799 unless $form->{transdate};
803 if ($form->{delivery_customer_id}) {
804 $query = qq|SELECT name FROM customer WHERE id = ?|;
805 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
808 if ($form->{delivery_vendor_id}) {
809 $query = qq|SELECT name FROM customer WHERE id = ?|;
810 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
813 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
815 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
816 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
818 $ref = $sth->fetchrow_hashref("NAME_lc");
820 map { $form->{$_} = $ref->{$_} } keys %$ref;
823 # get printed, emailed and queued
824 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
825 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
827 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
828 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
829 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
830 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
833 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
836 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
838 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
840 # retrieve individual items
841 # this query looks up all information about the items
842 # stuff different from the whole will not be overwritten, but saved with a suffix.
844 qq|SELECT o.id AS orderitems_id,
845 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
846 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
847 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
848 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
849 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
850 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
851 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
852 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
853 o.price_factor_id, o.price_factor, o.marge_price_factor,
854 pr.projectnumber, p.formel,
855 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
857 JOIN parts p ON (o.parts_id = p.id)
858 JOIN oe ON (o.trans_id = oe.id)
859 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
860 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
861 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
862 LEFT JOIN project pr ON (o.project_id = pr.id)
863 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
865 ? qq|WHERE o.trans_id = ?|
866 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
869 @ids = $form->{id} ? ($form->{id}) : @ids;
870 $sth = prepare_execute_query($form, $dbh, $query, @values);
872 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
873 # Retrieve custom variables.
874 my $cvars = CVar->get_custom_variables(dbh => $dbh,
876 sub_module => 'orderitems',
877 trans_id => $ref->{orderitems_id},
879 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
882 if (!$ref->{"part_inventory_accno_id"}) {
883 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
885 delete($ref->{"part_inventory_accno_id"});
887 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
888 # unless already present there
889 # remove _oe entries afterwards
890 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
891 qw|ordnumber transdate cusordnumber|
893 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
897 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
899 qq|SELECT accno AS inventory_accno, | .
900 qq| new_chart_id AS inventory_new_chart, | .
901 qq| date($transdate) - valid_from AS inventory_valid | .
902 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
903 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
904 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
907 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
909 qq|SELECT accno AS income_accno, | .
910 qq| new_chart_id AS income_new_chart, | .
911 qq| date($transdate) - valid_from AS income_valid | .
912 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
913 ($ref->{income_accno}, $ref->{income_new_chart},
914 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
917 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
919 qq|SELECT accno AS expense_accno, | .
920 qq| new_chart_id AS expense_new_chart, | .
921 qq| date($transdate) - valid_from AS expense_valid | .
922 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
923 ($ref->{expense_accno}, $ref->{expense_new_chart},
924 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
927 # delete orderitems_id in collective orders, so that they get cloned no matter what
928 delete $ref->{orderitems_id} if (@ids);
930 # get tax rates and description
931 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
933 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
934 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
935 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
936 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
937 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
938 qq|ORDER BY c.accno|;
939 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
940 $ref->{taxaccounts} = "";
942 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
943 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
947 $ref->{taxaccounts} .= "$ptr->{accno} ";
948 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
949 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
950 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
951 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
952 $form->{taxaccounts} .= "$ptr->{accno} ";
957 chop $ref->{taxaccounts};
959 push @{ $form->{form_details} }, $ref;
967 $form->lastname_used($dbh, $myconfig, $form->{vc})
968 unless $form->{"$form->{vc}_id"};
972 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
974 Common::webdav_folder($form);
976 $self->load_periodic_invoice_config($form);
978 my $rc = $dbh->commit;
980 $main::lxdebug->leave_sub();
985 sub retrieve_simple {
986 $main::lxdebug->enter_sub();
991 Common::check_params(\%params, qw(id));
993 my $myconfig = \%main::myconfig;
994 my $form = $main::form;
996 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
998 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
999 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1001 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1002 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1004 $main::lxdebug->leave_sub();
1010 $main::lxdebug->enter_sub();
1012 my ($self, $myconfig, $form) = @_;
1014 # connect to database
1015 my $dbh = $form->get_standard_dbh;
1021 my $nodiscount_subtotal = 0;
1022 my $discount_subtotal = 0;
1025 my @partsgroup = ();
1028 my $subtotal_header = 0;
1029 my $subposition = 0;
1035 my (@project_ids, %projectnumbers, %projectdescriptions);
1037 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1039 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1040 'departments' => 'ALL_DEPARTMENTS');
1043 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1044 $price_factors{$pfac->{id}} = $pfac;
1045 $pfac->{factor} *= 1;
1046 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1050 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1051 next unless $dept->{id} eq $form->{department_id};
1052 $form->{department} = $dept->{description};
1056 # sort items by partsgroup
1057 for $i (1 .. $form->{rowcount}) {
1059 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1060 $partsgroup = $form->{"partsgroup_$i"};
1062 push @partsgroup, [$i, $partsgroup];
1063 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1067 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1068 join(", ", map("?", @project_ids)) . ")";
1069 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1070 while (my $ref = $sth->fetchrow_hashref()) {
1071 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1072 $projectdescriptions{$ref->{id}} = $ref->{description};
1077 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1078 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1080 $form->{discount} = [];
1082 $form->{TEMPLATE_ARRAYS} = { };
1083 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1085 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1088 qw(runningnumber number description longdescription qty ship unit bin
1089 partnotes serialnumber reqdate sellprice listprice netprice
1090 discount p_discount discount_sub nodiscount_sub
1091 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1092 price_factor price_factor_name partsgroup weight lineweight);
1094 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1096 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1098 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1100 my $totalweight = 0;
1102 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1105 if ($item->[1] ne $sameitem) {
1106 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1107 $sameitem = $item->[1];
1109 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1112 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1114 if ($form->{"id_$i"} != 0) {
1116 # add number, description and qty to $form->{number}, ....
1118 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1119 $subtotal_header = $i;
1120 $position = int($position);
1123 } elsif ($subtotal_header) {
1125 $position = int($position);
1126 $position = $position.".".$subposition;
1128 $position = int($position);
1132 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1134 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1135 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1136 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1137 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1138 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1139 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1140 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1141 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1142 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1143 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1144 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1145 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1146 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1147 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1148 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1149 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1150 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1151 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1152 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1154 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1155 my ($dec) = ($sellprice =~ /\.(\d+)/);
1156 my $decimalplaces = max 2, length($dec);
1158 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1160 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1161 my $linetotal = $form->round_amount($linetotal_exact, 2);
1163 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1164 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1166 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1168 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1170 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1172 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1173 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1175 $linetotal = ($linetotal != 0) ? $linetotal : '';
1177 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1178 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1179 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1181 $form->{ordtotal} += $linetotal;
1182 $form->{nodiscount_total} += $nodiscount_linetotal;
1183 $form->{discount_total} += $discount;
1185 if ($subtotal_header) {
1186 $discount_subtotal += $linetotal;
1187 $nodiscount_subtotal += $nodiscount_linetotal;
1190 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1191 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1192 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1193 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1194 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1196 $discount_subtotal = 0;
1197 $nodiscount_subtotal = 0;
1198 $subtotal_header = 0;
1201 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1204 if (!$form->{"discount_$i"}) {
1205 $nodiscount += $linetotal;
1208 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1209 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1210 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1211 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1212 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1213 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1215 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1216 $totalweight += $lineweight;
1217 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1218 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1219 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1220 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1222 my ($taxamount, $taxbase);
1225 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1227 if ($form->{taxincluded}) {
1230 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1231 $taxbase = $linetotal / (1 + $taxrate);
1233 $taxamount = $linetotal * $taxrate;
1234 $taxbase = $linetotal;
1237 if ($taxamount != 0) {
1238 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1239 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1240 $taxbase{$accno} += $taxbase;
1244 $tax_rate = $taxrate * 100;
1245 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1247 if ($form->{"assembly_$i"}) {
1250 # get parts and push them onto the stack
1252 if ($form->{groupitems}) {
1253 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1255 $sortorder = qq|ORDER BY a.oid|;
1258 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1259 qq|pg.partsgroup | .
1260 qq|FROM assembly a | .
1261 qq| JOIN parts p ON (a.parts_id = p.id) | .
1262 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1263 qq| WHERE a.bom = '1' | .
1264 qq| AND a.id = ? | . $sortorder;
1265 @values = ($form->{"id_$i"});
1266 $sth = $dbh->prepare($query);
1267 $sth->execute(@values) || $form->dberror($query);
1269 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1270 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1271 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1272 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1273 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1276 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1277 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1282 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1283 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1284 for @{ $ic_cvar_configs };
1288 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1289 $form->{totalweight_nofmt} = $totalweight;
1290 my $defaults = AM->get_defaults();
1291 $form->{weightunit} = $defaults->{weightunit};
1294 foreach $item (sort keys %taxaccounts) {
1295 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1297 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1298 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1299 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1300 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1301 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1302 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1303 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1305 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1306 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1307 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1310 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1311 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1312 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1313 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1315 if($form->{taxincluded}) {
1316 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1317 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1319 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1320 $form->{subtotal_nofmt} = $form->{ordtotal};
1323 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1326 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1328 if ($form->{type} =~ /_quotation/) {
1329 $form->set_payment_options($myconfig, $form->{quodate});
1331 $form->set_payment_options($myconfig, $form->{orddate});
1334 $form->{username} = $myconfig->{name};
1338 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1339 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1341 $main::lxdebug->leave_sub();
1344 sub project_description {
1345 $main::lxdebug->enter_sub();
1347 my ($self, $dbh, $id) = @_;
1349 my $query = qq|SELECT description FROM project WHERE id = ?|;
1350 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1352 $main::lxdebug->leave_sub();
1363 OE.pm - Order entry module
1367 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>.
1373 =item retrieve_simple PARAMS
1375 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1377 my $order = retrieve_simple(id => 2);