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;
52 $main::lxdebug->enter_sub();
54 my ($self, $myconfig, $form) = @_;
57 my $dbh = $form->get_standard_dbh;
60 my $ordnumber = 'ordnumber';
66 my ($periodic_invoices_columns, $periodic_invoices_joins);
68 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
70 if ($form->{type} =~ /_quotation$/) {
72 $ordnumber = 'quonumber';
74 } elsif ($form->{type} eq 'sales_order') {
75 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
76 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
79 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
82 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
83 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
84 qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
85 qq| o.transaction_description, | .
86 qq| o.marge_total, o.marge_percent, | .
87 qq| ex.$rate AS exchangerate, | .
88 qq| pr.projectnumber AS globalprojectnumber, | .
89 qq| e.name AS employee, s.name AS salesman, | .
90 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id | .
91 $periodic_invoices_columns .
93 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
94 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
95 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
96 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
97 qq| AND ex.transdate = o.transdate) | .
98 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
99 qq|$periodic_invoices_joins | .
100 qq|WHERE (o.quotation = ?) |;
101 push(@values, $quotation);
103 my ($null, $split_department_id) = split /--/, $form->{department};
104 my $department_id = $form->{department_id} || $split_department_id;
105 if ($department_id) {
106 $query .= qq| AND o.department_id = ?|;
107 push(@values, $department_id);
110 if ($form->{"project_id"}) {
112 qq|AND ((globalproject_id = ?) OR EXISTS | .
113 qq| (SELECT * FROM orderitems oi | .
114 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
115 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
118 if ($form->{"projectnumber"}) {
120 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
121 SELECT * FROM orderitems oi
122 LEFT JOIN project proi ON proi.id = oi.project_id
123 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
126 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
129 if ($form->{"business_id"}) {
130 $query .= " AND ct.business_id = ?";
131 push(@values, $form->{"business_id"});
134 if ($form->{"${vc}_id"}) {
135 $query .= " AND o.${vc}_id = ?";
136 push(@values, $form->{"${vc}_id"});
138 } elsif ($form->{$vc}) {
139 $query .= " AND ct.name ILIKE ?";
140 push(@values, '%' . $form->{$vc} . '%');
143 if (!$main::auth->assert('sales_all_edit', 1)) {
144 $query .= " AND o.employee_id = (select id from employee where login= ?)";
145 push @values, $form->{login};
147 if ($form->{employee_id}) {
148 $query .= " AND o.employee_id = ?";
149 push @values, conv_i($form->{employee_id});
152 if ($form->{salesman_id}) {
153 $query .= " AND o.salesman_id = ?";
154 push @values, conv_i($form->{salesman_id});
157 if (!$form->{open} && !$form->{closed}) {
158 $query .= " AND o.id = 0";
159 } elsif (!($form->{open} && $form->{closed})) {
160 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
163 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
164 ($form->{"notdelivered"} ne $form->{"delivered"})) {
165 $query .= $form->{"delivered"} ?
166 " AND o.delivered " : " AND NOT o.delivered";
169 if ($form->{$ordnumber}) {
170 $query .= qq| AND o.$ordnumber ILIKE ?|;
171 push(@values, '%' . $form->{$ordnumber} . '%');
174 if($form->{transdatefrom}) {
175 $query .= qq| AND o.transdate >= ?|;
176 push(@values, conv_date($form->{transdatefrom}));
179 if($form->{transdateto}) {
180 $query .= qq| AND o.transdate <= ?|;
181 push(@values, conv_date($form->{transdateto}));
184 if($form->{reqdatefrom}) {
185 $query .= qq| AND o.reqdate >= ?|;
186 push(@values, conv_date($form->{reqdatefrom}));
189 if($form->{reqdateto}) {
190 $query .= qq| AND o.reqdate <= ?|;
191 push(@values, conv_date($form->{reqdateto}));
194 if ($form->{transaction_description}) {
195 $query .= qq| AND o.transaction_description ILIKE ?|;
196 push(@values, '%' . $form->{transaction_description} . '%');
199 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
200 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
201 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
204 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
205 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
206 my %allowed_sort_columns = (
207 "transdate" => "o.transdate",
208 "reqdate" => "o.reqdate",
210 "ordnumber" => "o.ordnumber",
211 "quonumber" => "o.quonumber",
213 "employee" => "e.name",
214 "salesman" => "s.name",
215 "shipvia" => "o.shipvia",
216 "transaction_description" => "o.transaction_description"
218 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
219 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
221 $query .= qq| ORDER by | . $sortorder;
223 my $sth = $dbh->prepare($query);
224 $sth->execute(@values) ||
225 $form->dberror($query . " (" . join(", ", @values) . ")");
229 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
230 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
231 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
232 $id{ $ref->{id} } = $ref->{id};
237 $main::lxdebug->leave_sub();
240 sub transactions_for_todo_list {
241 $main::lxdebug->enter_sub();
246 my $myconfig = \%main::myconfig;
247 my $form = $main::form;
249 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
251 my $query = qq|SELECT id FROM employee WHERE login = ?|;
252 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
255 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
256 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
261 LEFT JOIN customer c ON (oe.customer_id = c.id)
262 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
263 LEFT JOIN employee e ON (oe.employee_id = e.id)
264 WHERE (COALESCE(quotation, FALSE) = TRUE)
265 AND (COALESCE(closed, FALSE) = FALSE)
266 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
267 AND NOT (oe.reqdate ISNULL)
268 AND (oe.reqdate < current_date)
271 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
273 $main::lxdebug->leave_sub();
279 $main::lxdebug->enter_sub();
281 my ($self, $myconfig, $form) = @_;
283 # connect to database, turn off autocommit
284 my $dbh = $form->get_standard_dbh;
286 my ($query, @values, $sth, $null);
287 my $exchangerate = 0;
289 my $all_units = AM->retrieve_units($myconfig, $form);
290 $form->{all_units} = $all_units;
292 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
295 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
296 unless ($form->{employee_id}) {
297 $form->get_employee($dbh);
300 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
303 $query = qq|DELETE FROM custom_variables
304 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
305 AND (sub_module = 'orderitems')
306 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
307 do_query($form, $dbh, $query, $form->{id});
309 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
310 do_query($form, $dbh, $query, $form->{id});
312 $query = qq|DELETE FROM shipto | .
313 qq|WHERE trans_id = ? AND module = 'OE'|;
314 do_query($form, $dbh, $query, $form->{id});
318 $query = qq|SELECT nextval('id')|;
319 ($form->{id}) = selectrow_query($form, $dbh, $query);
321 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
322 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
340 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
341 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
344 for my $i (1 .. $form->{rowcount}) {
346 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
348 if ($form->{"id_$i"}) {
351 $query = qq|SELECT unit FROM parts WHERE id = ?|;
352 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
355 if (defined($all_units->{$item_unit}->{factor}) &&
356 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
357 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
359 my $baseqty = $form->{"qty_$i"} * $basefactor;
361 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
362 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
364 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
366 # set values to 0 if nothing entered
367 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
369 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
370 $fxsellprice = $form->{"sellprice_$i"};
372 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
374 my $decimalplaces = ($dec > 2) ? $dec : 2;
376 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
377 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
379 $form->{"inventory_accno_$i"} *= 1;
380 $form->{"expense_accno_$i"} *= 1;
382 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
383 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
385 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
389 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
391 if ($form->{taxincluded}) {
392 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
393 $taxbase = $linetotal - $taxamount;
395 # we are not keeping a natural price, do not round
396 $form->{"sellprice_$i"} =
397 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
399 $taxamount = $linetotal * $taxrate;
400 $taxbase = $linetotal;
403 if ($form->round_amount($taxrate, 7) == 0) {
404 if ($form->{taxincluded}) {
405 foreach my $item (@taxaccounts) {
406 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
407 $taxaccounts{$item} += $taxamount;
408 $taxdiff += $taxamount;
409 $taxbase{$item} += $taxbase;
411 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
413 foreach my $item (@taxaccounts) {
414 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
415 $taxbase{$item} += $taxbase;
419 foreach my $item (@taxaccounts) {
420 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
421 $taxbase{$item} += $taxbase;
425 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
427 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
429 # Get pricegroup_id and save it. Unfortunately the interface
430 # also uses ID "0" for signalling that none is selected, but "0"
431 # must not be stored in the database. Therefore we cannot simply
433 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
435 $pricegroup_id = undef if !$pricegroup_id;
437 # save detail record in orderitems table
438 my $orderitems_id = $form->{"orderitems_id_$i"};
439 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
442 $query = qq|INSERT INTO orderitems (
443 id, trans_id, parts_id, description, longdescription, qty, base_qty,
444 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
445 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
446 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
447 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
448 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
450 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
451 $form->{"description_$i"}, $form->{"longdescription_$i"},
452 $form->{"qty_$i"}, $baseqty,
453 $fxsellprice, $form->{"discount_$i"},
454 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
455 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
456 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
457 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
458 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
459 $form->{"lastcost_$i"},
460 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
461 conv_i($form->{"marge_price_factor_$i"}));
462 do_query($form, $dbh, $query, @values);
464 $form->{"sellprice_$i"} = $fxsellprice;
465 $form->{"discount_$i"} *= 100;
467 CVar->save_custom_variables(module => 'IC',
468 sub_module => 'orderitems',
469 trans_id => $orderitems_id,
470 configs => $ic_cvar_configs,
472 name_prefix => 'ic_',
473 name_postfix => "_$i",
478 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
482 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
484 $amount = $form->round_amount($netamount + $tax, 2);
485 $netamount = $form->round_amount($netamount, 2);
487 if ($form->{currency} eq $form->{defaultcurrency}) {
488 $form->{exchangerate} = 1;
490 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
493 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
495 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
497 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
502 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
503 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
504 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
505 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
506 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
507 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
510 @values = ($form->{ordnumber} || '', $form->{quonumber},
511 $form->{cusordnumber}, conv_date($form->{transdate}),
512 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
513 $amount, $netamount, conv_date($reqdate),
514 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
515 $form->{shipvia}, $form->{notes}, $form->{intnotes},
516 $form->{currency}, $form->{closed} ? 't' : 'f',
517 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
518 $quotation, conv_i($form->{department_id}),
519 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
520 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
521 conv_i($form->{delivery_vendor_id}),
522 conv_i($form->{delivery_customer_id}),
523 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
524 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
525 $form->{transaction_description},
526 $form->{marge_total} * 1, $form->{marge_percent} * 1,
527 conv_i($form->{id}));
528 do_query($form, $dbh, $query, @values);
530 $form->{ordtotal} = $amount;
533 $form->{name} = $form->{ $form->{vc} };
534 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
536 if (!$form->{shipto_id}) {
537 $form->add_shipto($dbh, $form->{id}, "OE");
540 # save printed, emailed, queued
541 $form->save_status($dbh);
543 # Link this record to the records it was created from.
544 $form->{convert_from_oe_ids} =~ s/^\s+//;
545 $form->{convert_from_oe_ids} =~ s/\s+$//;
546 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
547 delete $form->{convert_from_oe_ids};
549 if (scalar @convert_from_oe_ids) {
550 RecordLinks->create_links('dbh' => $dbh,
552 'from_table' => 'oe',
553 'from_ids' => \@convert_from_oe_ids,
555 'to_id' => $form->{id},
558 $self->_close_quotations_rfqs('dbh' => $dbh,
559 'from_id' => \@convert_from_oe_ids,
560 'to_id' => $form->{id});
563 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
564 if ($form->{vc} eq 'customer') {
565 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
567 if ($form->{vc} eq 'vendor') {
568 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
572 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
573 "quonumber" : "ordnumber"};
575 Common::webdav_folder($form);
577 my $rc = $dbh->commit;
579 $self->save_periodic_invoices_config(dbh => $dbh,
580 oe_id => $form->{id},
581 config_yaml => $form->{periodic_invoices_config})
582 if ($form->{type} eq 'sales_order');
584 $main::lxdebug->leave_sub();
589 sub save_periodic_invoices_config {
590 my ($self, %params) = @_;
592 return if !$params{oe_id};
594 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
595 return if 'HASH' ne ref $config;
597 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
598 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
599 $obj->update_attributes(%{ $config });
602 sub load_periodic_invoice_config {
606 delete $form->{periodic_invoices_config};
609 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
612 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
613 print printer_id copies) };
614 $form->{periodic_invoices_config} = YAML::Dump($config);
619 sub _close_quotations_rfqs {
620 $main::lxdebug->enter_sub();
625 Common::check_params(\%params, qw(from_id to_id));
627 my $myconfig = \%main::myconfig;
628 my $form = $main::form;
630 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
632 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
633 my $sth = prepare_query($form, $dbh, $query);
635 do_statement($form, $sth, $query, conv_i($params{to_id}));
637 my ($quotation) = $sth->fetchrow_array();
640 $main::lxdebug->leave_sub();
646 foreach my $from_id (@{ $params{from_id} }) {
647 $from_id = conv_i($from_id);
648 do_statement($form, $sth, $query, $from_id);
649 ($quotation) = $sth->fetchrow_array();
650 push @close_ids, $from_id if ($quotation);
655 if (scalar @close_ids) {
656 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
657 do_query($form, $dbh, $query, @close_ids);
659 $dbh->commit() unless ($params{dbh});
662 $main::lxdebug->leave_sub();
666 $main::lxdebug->enter_sub();
668 my ($self, $myconfig, $form) = @_;
670 my $rc = SL::DB::Order->new->db->with_transaction(sub {
671 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
673 SL::DB::Order->new(id => $form->{id})->delete;
675 my $spool = $::lx_office_conf{paths}->{spool};
676 unlink map { "$spool/$_" } @spoolfiles if $spool;
681 $main::lxdebug->leave_sub();
687 $main::lxdebug->enter_sub();
689 my ($self, $myconfig, $form) = @_;
691 # connect to database
692 my $dbh = $form->get_standard_dbh;
694 my ($query, $query_add, @values, @ids, $sth);
696 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
698 push @ids, $form->{"trans_id_$_"}
699 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
700 } (1 .. $form->{"rowcount"});
702 if ($form->{rowcount} && scalar @ids) {
703 $form->{convert_from_oe_ids} = join ' ', @ids;
706 # if called in multi id mode, and still only got one id, switch back to single id
707 if ($form->{"rowcount"} and $#ids == 0) {
708 $form->{"id"} = $ids[0];
712 # and remember for the rest of the function
713 my $is_collective_order = scalar @ids;
716 my $wday = (localtime(time))[6];
717 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
718 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
721 # get default accounts
722 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
723 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
724 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
725 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
726 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
729 my $ref = selectfirst_hashref_query($form, $dbh, $query);
730 map { $form->{$_} = $ref->{$_} } keys %$ref;
732 $form->{currency} = $form->get_default_currency($myconfig);
734 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
735 # we come from invoices, feel free.
736 $form->{reqdate} = $form->{deliverydate}
737 if ( $form->{deliverydate}
738 and $form->{callback} =~ /action=ar_transactions/);
740 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
742 if ($form->{id} or @ids) {
744 # retrieve order for single id
745 # NOTE: this query is intended to fetch all information only ONCE.
746 # so if any of these infos is important (or even different) for any item,
747 # it will be killed out and then has to be fetched from the item scope query further down
749 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
750 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
751 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
752 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
753 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
754 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
755 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
756 o.globalproject_id, o.delivered, o.transaction_description
758 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
759 LEFT JOIN employee e ON (o.employee_id = e.id)
760 LEFT JOIN department d ON (o.department_id = d.id) | .
763 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
765 @values = $form->{id} ? ($form->{id}) : @ids;
766 $sth = prepare_execute_query($form, $dbh, $query, @values);
768 $ref = $sth->fetchrow_hashref("NAME_lc");
771 map { $form->{$_} = $ref->{$_} } keys %$ref;
773 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
775 # set all entries for multiple ids blank that yield different information
776 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
777 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
781 # if not given, fill transdate with current_date
782 $form->{transdate} = $form->current_date($myconfig)
783 unless $form->{transdate};
787 if ($form->{delivery_customer_id}) {
788 $query = qq|SELECT name FROM customer WHERE id = ?|;
789 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
792 if ($form->{delivery_vendor_id}) {
793 $query = qq|SELECT name FROM customer WHERE id = ?|;
794 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
797 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
799 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
800 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
802 $ref = $sth->fetchrow_hashref("NAME_lc");
804 map { $form->{$_} = $ref->{$_} } keys %$ref;
807 # get printed, emailed and queued
808 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
809 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
811 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
812 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
813 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
814 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
817 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
820 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
822 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
824 # retrieve individual items
825 # this query looks up all information about the items
826 # stuff different from the whole will not be overwritten, but saved with a suffix.
828 qq|SELECT o.id AS orderitems_id,
829 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
830 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
831 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
832 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
833 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
834 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
835 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
836 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
837 o.price_factor_id, o.price_factor, o.marge_price_factor,
838 pr.projectnumber, p.formel,
839 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
841 JOIN parts p ON (o.parts_id = p.id)
842 JOIN oe ON (o.trans_id = oe.id)
843 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
844 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
845 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
846 LEFT JOIN project pr ON (o.project_id = pr.id)
847 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
849 ? qq|WHERE o.trans_id = ?|
850 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
853 @ids = $form->{id} ? ($form->{id}) : @ids;
854 $sth = prepare_execute_query($form, $dbh, $query, @values);
856 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
857 # Retrieve custom variables.
858 my $cvars = CVar->get_custom_variables(dbh => $dbh,
860 sub_module => 'orderitems',
861 trans_id => $ref->{orderitems_id},
863 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
866 if (!$ref->{"part_inventory_accno_id"}) {
867 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
869 delete($ref->{"part_inventory_accno_id"});
871 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
872 # unless already present there
873 # remove _oe entries afterwards
874 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
875 qw|ordnumber transdate cusordnumber|
877 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
881 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
883 qq|SELECT accno AS inventory_accno, | .
884 qq| new_chart_id AS inventory_new_chart, | .
885 qq| date($transdate) - valid_from AS inventory_valid | .
886 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
887 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
888 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
891 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
893 qq|SELECT accno AS income_accno, | .
894 qq| new_chart_id AS income_new_chart, | .
895 qq| date($transdate) - valid_from AS income_valid | .
896 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
897 ($ref->{income_accno}, $ref->{income_new_chart},
898 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
901 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
903 qq|SELECT accno AS expense_accno, | .
904 qq| new_chart_id AS expense_new_chart, | .
905 qq| date($transdate) - valid_from AS expense_valid | .
906 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
907 ($ref->{expense_accno}, $ref->{expense_new_chart},
908 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
911 # delete orderitems_id in collective orders, so that they get cloned no matter what
912 delete $ref->{orderitems_id} if (@ids);
914 # get tax rates and description
915 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
917 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
918 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
919 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
920 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
921 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
922 qq|ORDER BY c.accno|;
923 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
924 $ref->{taxaccounts} = "";
926 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
927 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
931 $ref->{taxaccounts} .= "$ptr->{accno} ";
932 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
933 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
934 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
935 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
936 $form->{taxaccounts} .= "$ptr->{accno} ";
941 chop $ref->{taxaccounts};
943 push @{ $form->{form_details} }, $ref;
951 $form->lastname_used($dbh, $myconfig, $form->{vc})
952 unless $form->{"$form->{vc}_id"};
956 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
958 Common::webdav_folder($form);
960 $self->load_periodic_invoice_config($form);
962 my $rc = $dbh->commit;
964 $main::lxdebug->leave_sub();
969 sub retrieve_simple {
970 $main::lxdebug->enter_sub();
975 Common::check_params(\%params, qw(id));
977 my $myconfig = \%main::myconfig;
978 my $form = $main::form;
980 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
982 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
983 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
985 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
986 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
988 $main::lxdebug->leave_sub();
994 $main::lxdebug->enter_sub();
996 my ($self, $myconfig, $form) = @_;
998 # connect to database
999 my $dbh = $form->get_standard_dbh;
1005 my $nodiscount_subtotal = 0;
1006 my $discount_subtotal = 0;
1009 my @partsgroup = ();
1012 my $subtotal_header = 0;
1013 my $subposition = 0;
1019 my (@project_ids, %projectnumbers, %projectdescriptions);
1021 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1023 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1024 'departments' => 'ALL_DEPARTMENTS');
1027 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1028 $price_factors{$pfac->{id}} = $pfac;
1029 $pfac->{factor} *= 1;
1030 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1034 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1035 next unless $dept->{id} eq $form->{department_id};
1036 $form->{department} = $dept->{description};
1040 # sort items by partsgroup
1041 for $i (1 .. $form->{rowcount}) {
1043 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1044 $partsgroup = $form->{"partsgroup_$i"};
1046 push @partsgroup, [$i, $partsgroup];
1047 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1051 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1052 join(", ", map("?", @project_ids)) . ")";
1053 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1054 while (my $ref = $sth->fetchrow_hashref()) {
1055 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1056 $projectdescriptions{$ref->{id}} = $ref->{description};
1061 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1062 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1064 $form->{discount} = [];
1066 $form->{TEMPLATE_ARRAYS} = { };
1067 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1069 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1072 qw(runningnumber number description longdescription qty ship unit bin
1073 partnotes serialnumber reqdate sellprice listprice netprice
1074 discount p_discount discount_sub nodiscount_sub
1075 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1076 price_factor price_factor_name partsgroup weight lineweight);
1078 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1080 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1082 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1084 my $totalweight = 0;
1086 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1089 if ($item->[1] ne $sameitem) {
1090 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1091 $sameitem = $item->[1];
1093 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1096 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1098 if ($form->{"id_$i"} != 0) {
1100 # add number, description and qty to $form->{number}, ....
1102 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1103 $subtotal_header = $i;
1104 $position = int($position);
1107 } elsif ($subtotal_header) {
1109 $position = int($position);
1110 $position = $position.".".$subposition;
1112 $position = int($position);
1116 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1118 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1119 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1120 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1121 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1122 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1123 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1124 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1125 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1126 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1127 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1128 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1129 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1130 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1131 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1132 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1133 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1134 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1135 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1136 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1138 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1139 my ($dec) = ($sellprice =~ /\.(\d+)/);
1140 my $decimalplaces = max 2, length($dec);
1142 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1144 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1145 my $linetotal = $form->round_amount($linetotal_exact, 2);
1147 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1148 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1150 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1152 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1154 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1156 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1157 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1159 $linetotal = ($linetotal != 0) ? $linetotal : '';
1161 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1162 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1163 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1165 $form->{ordtotal} += $linetotal;
1166 $form->{nodiscount_total} += $nodiscount_linetotal;
1167 $form->{discount_total} += $discount;
1169 if ($subtotal_header) {
1170 $discount_subtotal += $linetotal;
1171 $nodiscount_subtotal += $nodiscount_linetotal;
1174 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1175 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1176 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1177 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1178 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1180 $discount_subtotal = 0;
1181 $nodiscount_subtotal = 0;
1182 $subtotal_header = 0;
1185 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1188 if (!$form->{"discount_$i"}) {
1189 $nodiscount += $linetotal;
1192 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1193 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1194 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1195 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1196 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1197 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1199 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1200 $totalweight += $lineweight;
1201 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1202 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1203 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1204 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1206 my ($taxamount, $taxbase);
1209 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1211 if ($form->{taxincluded}) {
1214 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1215 $taxbase = $linetotal / (1 + $taxrate);
1217 $taxamount = $linetotal * $taxrate;
1218 $taxbase = $linetotal;
1221 if ($taxamount != 0) {
1222 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1223 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1224 $taxbase{$accno} += $taxbase;
1228 $tax_rate = $taxrate * 100;
1229 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1231 if ($form->{"assembly_$i"}) {
1234 # get parts and push them onto the stack
1236 if ($form->{groupitems}) {
1237 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1239 $sortorder = qq|ORDER BY a.oid|;
1242 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1243 qq|pg.partsgroup | .
1244 qq|FROM assembly a | .
1245 qq| JOIN parts p ON (a.parts_id = p.id) | .
1246 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1247 qq| WHERE a.bom = '1' | .
1248 qq| AND a.id = ? | . $sortorder;
1249 @values = ($form->{"id_$i"});
1250 $sth = $dbh->prepare($query);
1251 $sth->execute(@values) || $form->dberror($query);
1253 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1254 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1255 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1256 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1257 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1260 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1261 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1266 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1267 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1268 for @{ $ic_cvar_configs };
1272 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1273 $form->{totalweight_nofmt} = $totalweight;
1274 my $defaults = AM->get_defaults();
1275 $form->{weightunit} = $defaults->{weightunit};
1278 foreach $item (sort keys %taxaccounts) {
1279 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1281 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1282 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1283 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1284 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1285 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1286 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1287 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1288 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1291 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1292 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1293 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1294 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1296 if($form->{taxincluded}) {
1297 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1298 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1300 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1301 $form->{subtotal_nofmt} = $form->{ordtotal};
1304 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1307 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1309 if ($form->{type} =~ /_quotation/) {
1310 $form->set_payment_options($myconfig, $form->{quodate});
1312 $form->set_payment_options($myconfig, $form->{orddate});
1315 $form->{username} = $myconfig->{name};
1319 $main::lxdebug->leave_sub();
1322 sub project_description {
1323 $main::lxdebug->enter_sub();
1325 my ($self, $dbh, $id) = @_;
1327 my $query = qq|SELECT description FROM project WHERE id = ?|;
1328 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1330 $main::lxdebug->leave_sub();
1341 OE.pm - Order entry module
1345 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>.
1351 =item retrieve_simple PARAMS
1353 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1355 my $order = retrieve_simple(id => 2);