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.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->{transdatefrom}) {
179 $query .= qq| AND o.transdate >= ?|;
180 push(@values, conv_date($form->{transdatefrom}));
183 if($form->{transdateto}) {
184 $query .= qq| AND o.transdate <= ?|;
185 push(@values, conv_date($form->{transdateto}));
188 if($form->{reqdatefrom}) {
189 $query .= qq| AND o.reqdate >= ?|;
190 push(@values, conv_date($form->{reqdatefrom}));
193 if($form->{reqdateto}) {
194 $query .= qq| AND o.reqdate <= ?|;
195 push(@values, conv_date($form->{reqdateto}));
198 if ($form->{shippingpoint}) {
199 $query .= qq| AND o.shippingpoint ILIKE ?|;
200 push(@values, '%' . $form->{shippingpoint} . '%');
203 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
204 $query .= qq| AND tz.id = ?|;
205 push(@values, $form->{taxzone_id});
208 if ($form->{transaction_description}) {
209 $query .= qq| AND o.transaction_description ILIKE ?|;
210 push(@values, '%' . $form->{transaction_description} . '%');
213 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
214 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
215 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
218 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
219 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
220 my %allowed_sort_columns = (
221 "transdate" => "o.transdate",
222 "reqdate" => "o.reqdate",
224 "ordnumber" => "o.ordnumber",
225 "quonumber" => "o.quonumber",
227 "employee" => "e.name",
228 "salesman" => "s.name",
229 "shipvia" => "o.shipvia",
230 "transaction_description" => "o.transaction_description",
231 "shippingpoint" => "o.shippingpoint",
232 "taxzone" => "tz.description",
234 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
235 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
237 $query .= qq| ORDER by | . $sortorder;
239 my $sth = $dbh->prepare($query);
240 $sth->execute(@values) ||
241 $form->dberror($query . " (" . join(", ", @values) . ")");
245 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
246 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
247 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
248 $id{ $ref->{id} } = $ref->{id};
253 $main::lxdebug->leave_sub();
256 sub transactions_for_todo_list {
257 $main::lxdebug->enter_sub();
262 my $myconfig = \%main::myconfig;
263 my $form = $main::form;
265 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
267 my $query = qq|SELECT id FROM employee WHERE login = ?|;
268 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
271 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
272 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
277 LEFT JOIN customer c ON (oe.customer_id = c.id)
278 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
279 LEFT JOIN employee e ON (oe.employee_id = e.id)
280 WHERE (COALESCE(quotation, FALSE) = TRUE)
281 AND (COALESCE(closed, FALSE) = FALSE)
282 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
283 AND NOT (oe.reqdate ISNULL)
284 AND (oe.reqdate < current_date)
287 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
289 $main::lxdebug->leave_sub();
295 $main::lxdebug->enter_sub();
297 my ($self, $myconfig, $form) = @_;
299 # connect to database, turn off autocommit
300 my $dbh = $form->get_standard_dbh;
302 my ($query, @values, $sth, $null);
303 my $exchangerate = 0;
305 my $all_units = AM->retrieve_units($myconfig, $form);
306 $form->{all_units} = $all_units;
308 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
311 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
312 unless ($form->{employee_id}) {
313 $form->get_employee($dbh);
316 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
318 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
319 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
320 $form->{$number_field} ||= $trans_number->create_unique;
323 $query = qq|DELETE FROM custom_variables
324 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
325 AND (sub_module = 'orderitems')
326 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
327 do_query($form, $dbh, $query, $form->{id});
329 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
330 do_query($form, $dbh, $query, $form->{id});
332 $query = qq|DELETE FROM shipto | .
333 qq|WHERE trans_id = ? AND module = 'OE'|;
334 do_query($form, $dbh, $query, $form->{id});
338 $query = qq|SELECT nextval('id')|;
339 ($form->{id}) = selectrow_query($form, $dbh, $query);
341 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
342 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
360 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
361 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
364 for my $i (1 .. $form->{rowcount}) {
366 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
368 if ($form->{"id_$i"}) {
371 $query = qq|SELECT unit FROM parts WHERE id = ?|;
372 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
375 if (defined($all_units->{$item_unit}->{factor}) &&
376 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
377 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
379 my $baseqty = $form->{"qty_$i"} * $basefactor;
381 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
382 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
384 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
386 # set values to 0 if nothing entered
387 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
389 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
390 $fxsellprice = $form->{"sellprice_$i"};
392 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
394 my $decimalplaces = ($dec > 2) ? $dec : 2;
396 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
397 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
399 $form->{"inventory_accno_$i"} *= 1;
400 $form->{"expense_accno_$i"} *= 1;
402 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
403 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
405 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
409 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
411 if ($form->{taxincluded}) {
412 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
413 $taxbase = $linetotal - $taxamount;
415 # we are not keeping a natural price, do not round
416 $form->{"sellprice_$i"} =
417 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
419 $taxamount = $linetotal * $taxrate;
420 $taxbase = $linetotal;
423 if ($form->round_amount($taxrate, 7) == 0) {
424 if ($form->{taxincluded}) {
425 foreach my $item (@taxaccounts) {
426 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
427 $taxaccounts{$item} += $taxamount;
428 $taxdiff += $taxamount;
429 $taxbase{$item} += $taxbase;
431 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
433 foreach my $item (@taxaccounts) {
434 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
435 $taxbase{$item} += $taxbase;
439 foreach my $item (@taxaccounts) {
440 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
441 $taxbase{$item} += $taxbase;
445 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
447 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
449 # Get pricegroup_id and save it. Unfortunately the interface
450 # also uses ID "0" for signalling that none is selected, but "0"
451 # must not be stored in the database. Therefore we cannot simply
453 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
455 $pricegroup_id = undef if !$pricegroup_id;
457 # save detail record in orderitems table
458 my $orderitems_id = $form->{"orderitems_id_$i"};
459 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
462 $query = qq|INSERT INTO orderitems (
463 id, trans_id, parts_id, description, longdescription, qty, base_qty,
464 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
465 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
466 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
467 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
468 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
470 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
471 $form->{"description_$i"}, $form->{"longdescription_$i"},
472 $form->{"qty_$i"}, $baseqty,
473 $fxsellprice, $form->{"discount_$i"},
474 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
475 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
476 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
477 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
478 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
479 $form->{"lastcost_$i"},
480 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
481 conv_i($form->{"marge_price_factor_$i"}));
482 do_query($form, $dbh, $query, @values);
484 $form->{"sellprice_$i"} = $fxsellprice;
485 $form->{"discount_$i"} *= 100;
487 CVar->save_custom_variables(module => 'IC',
488 sub_module => 'orderitems',
489 trans_id => $orderitems_id,
490 configs => $ic_cvar_configs,
492 name_prefix => 'ic_',
493 name_postfix => "_$i",
498 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
502 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
504 $amount = $form->round_amount($netamount + $tax, 2);
505 $netamount = $form->round_amount($netamount, 2);
507 if ($form->{currency} eq $form->{defaultcurrency}) {
508 $form->{exchangerate} = 1;
510 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
513 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
515 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
517 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
522 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
523 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
524 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
525 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
526 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
527 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
530 @values = ($form->{ordnumber} || '', $form->{quonumber},
531 $form->{cusordnumber}, conv_date($form->{transdate}),
532 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
533 $amount, $netamount, conv_date($reqdate),
534 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
535 $form->{shipvia}, $form->{notes}, $form->{intnotes},
536 $form->{currency}, $form->{closed} ? 't' : 'f',
537 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
538 $quotation, conv_i($form->{department_id}),
539 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
540 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
541 conv_i($form->{delivery_vendor_id}),
542 conv_i($form->{delivery_customer_id}),
543 conv_i($form->{delivery_term_id}),
544 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
545 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
546 $form->{transaction_description},
547 $form->{marge_total} * 1, $form->{marge_percent} * 1,
548 conv_i($form->{id}));
549 do_query($form, $dbh, $query, @values);
551 $form->{ordtotal} = $amount;
554 $form->{name} = $form->{ $form->{vc} };
555 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
557 if (!$form->{shipto_id}) {
558 $form->add_shipto($dbh, $form->{id}, "OE");
561 # save printed, emailed, queued
562 $form->save_status($dbh);
564 # Link this record to the records it was created from.
565 $form->{convert_from_oe_ids} =~ s/^\s+//;
566 $form->{convert_from_oe_ids} =~ s/\s+$//;
567 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
568 delete $form->{convert_from_oe_ids};
570 if (scalar @convert_from_oe_ids) {
571 RecordLinks->create_links('dbh' => $dbh,
573 'from_table' => 'oe',
574 'from_ids' => \@convert_from_oe_ids,
576 'to_id' => $form->{id},
579 $self->_close_quotations_rfqs('dbh' => $dbh,
580 'from_id' => \@convert_from_oe_ids,
581 'to_id' => $form->{id});
584 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
585 if ($form->{vc} eq 'customer') {
586 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
588 if ($form->{vc} eq 'vendor') {
589 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
593 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
594 "quonumber" : "ordnumber"};
596 Common::webdav_folder($form);
598 my $rc = $dbh->commit;
600 $self->save_periodic_invoices_config(dbh => $dbh,
601 oe_id => $form->{id},
602 config_yaml => $form->{periodic_invoices_config})
603 if ($form->{type} eq 'sales_order');
605 $main::lxdebug->leave_sub();
610 sub save_periodic_invoices_config {
611 my ($self, %params) = @_;
613 return if !$params{oe_id};
615 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
616 return if 'HASH' ne ref $config;
618 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
619 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
620 $obj->update_attributes(%{ $config });
623 sub load_periodic_invoice_config {
627 delete $form->{periodic_invoices_config};
630 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
633 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
634 print printer_id copies) };
635 $form->{periodic_invoices_config} = YAML::Dump($config);
640 sub _close_quotations_rfqs {
641 $main::lxdebug->enter_sub();
646 Common::check_params(\%params, qw(from_id to_id));
648 my $myconfig = \%main::myconfig;
649 my $form = $main::form;
651 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
653 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
654 my $sth = prepare_query($form, $dbh, $query);
656 do_statement($form, $sth, $query, conv_i($params{to_id}));
658 my ($quotation) = $sth->fetchrow_array();
661 $main::lxdebug->leave_sub();
667 foreach my $from_id (@{ $params{from_id} }) {
668 $from_id = conv_i($from_id);
669 do_statement($form, $sth, $query, $from_id);
670 ($quotation) = $sth->fetchrow_array();
671 push @close_ids, $from_id if ($quotation);
676 if (scalar @close_ids) {
677 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
678 do_query($form, $dbh, $query, @close_ids);
680 $dbh->commit() unless ($params{dbh});
683 $main::lxdebug->leave_sub();
687 $main::lxdebug->enter_sub();
689 my ($self, $myconfig, $form) = @_;
691 my $rc = SL::DB::Order->new->db->with_transaction(sub {
692 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
694 SL::DB::Order->new(id => $form->{id})->delete;
696 my $spool = $::lx_office_conf{paths}->{spool};
697 unlink map { "$spool/$_" } @spoolfiles if $spool;
702 $main::lxdebug->leave_sub();
708 $main::lxdebug->enter_sub();
710 my ($self, $myconfig, $form) = @_;
712 # connect to database
713 my $dbh = $form->get_standard_dbh;
715 my ($query, $query_add, @values, @ids, $sth);
717 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
719 push @ids, $form->{"trans_id_$_"}
720 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
721 } (1 .. $form->{"rowcount"});
723 if ($form->{rowcount} && scalar @ids) {
724 $form->{convert_from_oe_ids} = join ' ', @ids;
727 # if called in multi id mode, and still only got one id, switch back to single id
728 if ($form->{"rowcount"} and $#ids == 0) {
729 $form->{"id"} = $ids[0];
733 # and remember for the rest of the function
734 my $is_collective_order = scalar @ids;
737 my $wday = (localtime(time))[6];
738 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
739 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
742 # get default accounts
743 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
744 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
745 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
746 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
747 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
750 my $ref = selectfirst_hashref_query($form, $dbh, $query);
751 map { $form->{$_} = $ref->{$_} } keys %$ref;
753 $form->{currency} = $form->get_default_currency($myconfig);
755 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
756 # we come from invoices, feel free.
757 $form->{reqdate} = $form->{deliverydate}
758 if ( $form->{deliverydate}
759 and $form->{callback} =~ /action=ar_transactions/);
761 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
763 if ($form->{id} or @ids) {
765 # retrieve order for single id
766 # NOTE: this query is intended to fetch all information only ONCE.
767 # so if any of these infos is important (or even different) for any item,
768 # it will be killed out and then has to be fetched from the item scope query further down
770 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
771 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
772 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
773 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
774 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
775 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
776 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
777 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
779 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
780 LEFT JOIN employee e ON (o.employee_id = e.id)
781 LEFT JOIN department d ON (o.department_id = d.id) | .
784 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
786 @values = $form->{id} ? ($form->{id}) : @ids;
787 $sth = prepare_execute_query($form, $dbh, $query, @values);
789 $ref = $sth->fetchrow_hashref("NAME_lc");
792 map { $form->{$_} = $ref->{$_} } keys %$ref;
794 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
796 # set all entries for multiple ids blank that yield different information
797 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
798 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
802 # if not given, fill transdate with current_date
803 $form->{transdate} = $form->current_date($myconfig)
804 unless $form->{transdate};
808 if ($form->{delivery_customer_id}) {
809 $query = qq|SELECT name FROM customer WHERE id = ?|;
810 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
813 if ($form->{delivery_vendor_id}) {
814 $query = qq|SELECT name FROM customer WHERE id = ?|;
815 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
818 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
820 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
821 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
823 $ref = $sth->fetchrow_hashref("NAME_lc");
825 map { $form->{$_} = $ref->{$_} } keys %$ref;
828 # get printed, emailed and queued
829 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
830 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
832 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
833 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
834 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
835 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
838 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
841 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
843 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
845 # retrieve individual items
846 # this query looks up all information about the items
847 # stuff different from the whole will not be overwritten, but saved with a suffix.
849 qq|SELECT o.id AS orderitems_id,
850 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
851 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
852 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
853 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
854 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
855 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
856 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
857 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
858 o.price_factor_id, o.price_factor, o.marge_price_factor,
859 pr.projectnumber, p.formel,
860 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
862 JOIN parts p ON (o.parts_id = p.id)
863 JOIN oe ON (o.trans_id = oe.id)
864 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
865 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
866 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
867 LEFT JOIN project pr ON (o.project_id = pr.id)
868 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
870 ? qq|WHERE o.trans_id = ?|
871 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
874 @ids = $form->{id} ? ($form->{id}) : @ids;
875 $sth = prepare_execute_query($form, $dbh, $query, @values);
877 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
878 # Retrieve custom variables.
879 my $cvars = CVar->get_custom_variables(dbh => $dbh,
881 sub_module => 'orderitems',
882 trans_id => $ref->{orderitems_id},
884 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
887 if (!$ref->{"part_inventory_accno_id"}) {
888 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
890 delete($ref->{"part_inventory_accno_id"});
892 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
893 # unless already present there
894 # remove _oe entries afterwards
895 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
896 qw|ordnumber transdate cusordnumber|
898 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
902 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
904 qq|SELECT accno AS inventory_accno, | .
905 qq| new_chart_id AS inventory_new_chart, | .
906 qq| date($transdate) - valid_from AS inventory_valid | .
907 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
908 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
909 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
912 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
914 qq|SELECT accno AS income_accno, | .
915 qq| new_chart_id AS income_new_chart, | .
916 qq| date($transdate) - valid_from AS income_valid | .
917 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
918 ($ref->{income_accno}, $ref->{income_new_chart},
919 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
922 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
924 qq|SELECT accno AS expense_accno, | .
925 qq| new_chart_id AS expense_new_chart, | .
926 qq| date($transdate) - valid_from AS expense_valid | .
927 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
928 ($ref->{expense_accno}, $ref->{expense_new_chart},
929 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
932 # delete orderitems_id in collective orders, so that they get cloned no matter what
933 delete $ref->{orderitems_id} if (@ids);
935 # get tax rates and description
936 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
938 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
939 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
940 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
941 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
942 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
943 qq|ORDER BY c.accno|;
944 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
945 $ref->{taxaccounts} = "";
947 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
948 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
952 $ref->{taxaccounts} .= "$ptr->{accno} ";
953 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
954 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
955 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
956 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
957 $form->{taxaccounts} .= "$ptr->{accno} ";
962 chop $ref->{taxaccounts};
964 push @{ $form->{form_details} }, $ref;
972 $form->lastname_used($dbh, $myconfig, $form->{vc})
973 unless $form->{"$form->{vc}_id"};
977 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
979 Common::webdav_folder($form);
981 $self->load_periodic_invoice_config($form);
983 my $rc = $dbh->commit;
985 $main::lxdebug->leave_sub();
990 sub retrieve_simple {
991 $main::lxdebug->enter_sub();
996 Common::check_params(\%params, qw(id));
998 my $myconfig = \%main::myconfig;
999 my $form = $main::form;
1001 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1003 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1004 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1006 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1007 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1009 $main::lxdebug->leave_sub();
1015 $main::lxdebug->enter_sub();
1017 my ($self, $myconfig, $form) = @_;
1019 # connect to database
1020 my $dbh = $form->get_standard_dbh;
1026 my $nodiscount_subtotal = 0;
1027 my $discount_subtotal = 0;
1030 my @partsgroup = ();
1033 my $subtotal_header = 0;
1034 my $subposition = 0;
1042 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1044 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1045 'departments' => 'ALL_DEPARTMENTS');
1048 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1049 $price_factors{$pfac->{id}} = $pfac;
1050 $pfac->{factor} *= 1;
1051 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1055 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1056 next unless $dept->{id} eq $form->{department_id};
1057 $form->{department} = $dept->{description};
1061 # sort items by partsgroup
1062 for $i (1 .. $form->{rowcount}) {
1064 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1065 $partsgroup = $form->{"partsgroup_$i"};
1067 push @partsgroup, [$i, $partsgroup];
1068 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1074 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1075 %projects_by_id = map { $_->id => $_ } @$projects;
1078 if ($projects_by_id{$form->{"globalproject_id"}}) {
1079 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1080 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1082 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1083 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1087 $form->{discount} = [];
1089 $form->{TEMPLATE_ARRAYS} = { };
1090 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1092 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1093 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1096 qw(runningnumber number description longdescription qty ship unit bin
1097 partnotes serialnumber reqdate sellprice listprice netprice
1098 discount p_discount discount_sub nodiscount_sub
1099 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1100 price_factor price_factor_name partsgroup weight lineweight);
1102 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1103 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1105 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1107 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1109 my $totalweight = 0;
1111 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1114 if ($item->[1] ne $sameitem) {
1115 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1116 $sameitem = $item->[1];
1118 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1121 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1123 if ($form->{"id_$i"} != 0) {
1125 # add number, description and qty to $form->{number}, ....
1127 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1128 $subtotal_header = $i;
1129 $position = int($position);
1132 } elsif ($subtotal_header) {
1134 $position = int($position);
1135 $position = $position.".".$subposition;
1137 $position = int($position);
1141 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1143 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1144 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1145 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1146 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1147 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1148 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1149 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1150 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1151 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1152 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1153 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1154 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1155 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1156 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1157 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1158 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1159 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1160 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1161 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1163 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1164 my ($dec) = ($sellprice =~ /\.(\d+)/);
1165 my $decimalplaces = max 2, length($dec);
1167 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1169 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1170 my $linetotal = $form->round_amount($linetotal_exact, 2);
1172 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1173 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1175 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1177 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1179 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1181 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1182 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1184 $linetotal = ($linetotal != 0) ? $linetotal : '';
1186 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1187 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1188 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1190 $form->{ordtotal} += $linetotal;
1191 $form->{nodiscount_total} += $nodiscount_linetotal;
1192 $form->{discount_total} += $discount;
1194 if ($subtotal_header) {
1195 $discount_subtotal += $linetotal;
1196 $nodiscount_subtotal += $nodiscount_linetotal;
1199 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1200 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1201 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1202 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1203 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1205 $discount_subtotal = 0;
1206 $nodiscount_subtotal = 0;
1207 $subtotal_header = 0;
1210 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1213 if (!$form->{"discount_$i"}) {
1214 $nodiscount += $linetotal;
1217 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1219 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1220 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1221 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1222 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1223 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1224 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1226 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1227 $totalweight += $lineweight;
1228 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1229 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1230 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1231 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1233 my ($taxamount, $taxbase);
1236 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1238 if ($form->{taxincluded}) {
1241 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1242 $taxbase = $linetotal / (1 + $taxrate);
1244 $taxamount = $linetotal * $taxrate;
1245 $taxbase = $linetotal;
1248 if ($taxamount != 0) {
1249 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1250 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1251 $taxbase{$accno} += $taxbase;
1255 $tax_rate = $taxrate * 100;
1256 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1258 if ($form->{"assembly_$i"}) {
1261 # get parts and push them onto the stack
1263 if ($form->{groupitems}) {
1264 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1266 $sortorder = qq|ORDER BY a.oid|;
1269 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1270 qq|pg.partsgroup | .
1271 qq|FROM assembly a | .
1272 qq| JOIN parts p ON (a.parts_id = p.id) | .
1273 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1274 qq| WHERE a.bom = '1' | .
1275 qq| AND a.id = ? | . $sortorder;
1276 @values = ($form->{"id_$i"});
1277 $sth = $dbh->prepare($query);
1278 $sth->execute(@values) || $form->dberror($query);
1280 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1281 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1282 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1283 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1284 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1287 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1288 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1293 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1294 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1295 for @{ $ic_cvar_configs };
1297 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1301 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1302 $form->{totalweight_nofmt} = $totalweight;
1303 my $defaults = AM->get_defaults();
1304 $form->{weightunit} = $defaults->{weightunit};
1307 foreach $item (sort keys %taxaccounts) {
1308 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1310 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1311 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1312 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1313 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1314 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1315 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1316 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1318 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1319 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1320 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1323 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1324 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1325 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1326 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1328 if($form->{taxincluded}) {
1329 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1330 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1332 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1333 $form->{subtotal_nofmt} = $form->{ordtotal};
1336 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1339 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1341 if ($form->{type} =~ /_quotation/) {
1342 $form->set_payment_options($myconfig, $form->{quodate});
1344 $form->set_payment_options($myconfig, $form->{orddate});
1347 $form->{username} = $myconfig->{name};
1351 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1352 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1354 $main::lxdebug->leave_sub();
1357 sub project_description {
1358 $main::lxdebug->enter_sub();
1360 my ($self, $dbh, $id) = @_;
1362 my $query = qq|SELECT description FROM project WHERE id = ?|;
1363 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1365 $main::lxdebug->leave_sub();
1376 OE.pm - Order entry module
1380 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>.
1386 =item retrieve_simple PARAMS
1388 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1390 my $order = retrieve_simple(id => 2);