1 #====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1999-2003
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
33 #======================================================================
37 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
53 $main::lxdebug->enter_sub();
55 my ($self, $myconfig, $form) = @_;
58 my $dbh = $form->get_standard_dbh;
61 my $ordnumber = 'ordnumber';
67 my ($periodic_invoices_columns, $periodic_invoices_joins);
69 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
71 if ($form->{type} =~ /_quotation$/) {
73 $ordnumber = 'quonumber';
75 } elsif ($form->{type} eq 'sales_order') {
76 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
77 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
80 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
83 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
84 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
85 qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
86 qq| o.transaction_description, | .
87 qq| o.marge_total, o.marge_percent, | .
88 qq| ex.$rate AS exchangerate, | .
89 qq| pr.projectnumber AS globalprojectnumber, | .
90 qq| e.name AS employee, s.name AS salesman, | .
91 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id | .
92 $periodic_invoices_columns .
94 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
95 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
96 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
97 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
98 qq| AND ex.transdate = o.transdate) | .
99 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
100 qq|$periodic_invoices_joins | .
101 qq|WHERE (o.quotation = ?) |;
102 push(@values, $quotation);
104 my ($null, $split_department_id) = split /--/, $form->{department};
105 my $department_id = $form->{department_id} || $split_department_id;
106 if ($department_id) {
107 $query .= qq| AND o.department_id = ?|;
108 push(@values, $department_id);
111 if ($form->{"project_id"}) {
113 qq|AND ((globalproject_id = ?) OR EXISTS | .
114 qq| (SELECT * FROM orderitems oi | .
115 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
116 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
119 if ($form->{"projectnumber"}) {
121 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
122 SELECT * FROM orderitems oi
123 LEFT JOIN project proi ON proi.id = oi.project_id
124 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
127 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
130 if ($form->{"business_id"}) {
131 $query .= " AND ct.business_id = ?";
132 push(@values, $form->{"business_id"});
135 if ($form->{"${vc}_id"}) {
136 $query .= " AND o.${vc}_id = ?";
137 push(@values, $form->{"${vc}_id"});
139 } elsif ($form->{$vc}) {
140 $query .= " AND ct.name ILIKE ?";
141 push(@values, '%' . $form->{$vc} . '%');
144 if (!$main::auth->assert('sales_all_edit', 1)) {
145 $query .= " AND o.employee_id = (select id from employee where login= ?)";
146 push @values, $form->{login};
148 if ($form->{employee_id}) {
149 $query .= " AND o.employee_id = ?";
150 push @values, conv_i($form->{employee_id});
153 if ($form->{salesman_id}) {
154 $query .= " AND o.salesman_id = ?";
155 push @values, conv_i($form->{salesman_id});
158 if (!$form->{open} && !$form->{closed}) {
159 $query .= " AND o.id = 0";
160 } elsif (!($form->{open} && $form->{closed})) {
161 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
164 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
165 ($form->{"notdelivered"} ne $form->{"delivered"})) {
166 $query .= $form->{"delivered"} ?
167 " AND o.delivered " : " AND NOT o.delivered";
170 if ($form->{$ordnumber}) {
171 $query .= qq| AND o.$ordnumber ILIKE ?|;
172 push(@values, '%' . $form->{$ordnumber} . '%');
175 if($form->{transdatefrom}) {
176 $query .= qq| AND o.transdate >= ?|;
177 push(@values, conv_date($form->{transdatefrom}));
180 if($form->{transdateto}) {
181 $query .= qq| AND o.transdate <= ?|;
182 push(@values, conv_date($form->{transdateto}));
185 if($form->{reqdatefrom}) {
186 $query .= qq| AND o.reqdate >= ?|;
187 push(@values, conv_date($form->{reqdatefrom}));
190 if($form->{reqdateto}) {
191 $query .= qq| AND o.reqdate <= ?|;
192 push(@values, conv_date($form->{reqdateto}));
195 if ($form->{transaction_description}) {
196 $query .= qq| AND o.transaction_description ILIKE ?|;
197 push(@values, '%' . $form->{transaction_description} . '%');
200 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
201 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
202 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
205 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
206 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
207 my %allowed_sort_columns = (
208 "transdate" => "o.transdate",
209 "reqdate" => "o.reqdate",
211 "ordnumber" => "o.ordnumber",
212 "quonumber" => "o.quonumber",
214 "employee" => "e.name",
215 "salesman" => "s.name",
216 "shipvia" => "o.shipvia",
217 "transaction_description" => "o.transaction_description"
219 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
220 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
222 $query .= qq| ORDER by | . $sortorder;
224 my $sth = $dbh->prepare($query);
225 $sth->execute(@values) ||
226 $form->dberror($query . " (" . join(", ", @values) . ")");
230 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
231 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
232 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
233 $id{ $ref->{id} } = $ref->{id};
238 $main::lxdebug->leave_sub();
241 sub transactions_for_todo_list {
242 $main::lxdebug->enter_sub();
247 my $myconfig = \%main::myconfig;
248 my $form = $main::form;
250 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
252 my $query = qq|SELECT id FROM employee WHERE login = ?|;
253 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
256 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
257 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
262 LEFT JOIN customer c ON (oe.customer_id = c.id)
263 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
264 LEFT JOIN employee e ON (oe.employee_id = e.id)
265 WHERE (COALESCE(quotation, FALSE) = TRUE)
266 AND (COALESCE(closed, FALSE) = FALSE)
267 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
268 AND NOT (oe.reqdate ISNULL)
269 AND (oe.reqdate < current_date)
272 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
274 $main::lxdebug->leave_sub();
280 $main::lxdebug->enter_sub();
282 my ($self, $myconfig, $form) = @_;
284 # connect to database, turn off autocommit
285 my $dbh = $form->get_standard_dbh;
287 my ($query, @values, $sth, $null);
288 my $exchangerate = 0;
290 my $all_units = AM->retrieve_units($myconfig, $form);
291 $form->{all_units} = $all_units;
293 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
296 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
297 unless ($form->{employee_id}) {
298 $form->get_employee($dbh);
301 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
304 $query = qq|DELETE FROM custom_variables
305 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
306 AND (sub_module = 'orderitems')
307 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
308 do_query($form, $dbh, $query, $form->{id});
310 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
311 do_query($form, $dbh, $query, $form->{id});
313 $query = qq|DELETE FROM shipto | .
314 qq|WHERE trans_id = ? AND module = 'OE'|;
315 do_query($form, $dbh, $query, $form->{id});
319 $query = qq|SELECT nextval('id')|;
320 ($form->{id}) = selectrow_query($form, $dbh, $query);
322 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
323 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
341 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
342 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
345 for my $i (1 .. $form->{rowcount}) {
347 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
349 if ($form->{"id_$i"}) {
352 $query = qq|SELECT unit FROM parts WHERE id = ?|;
353 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
356 if (defined($all_units->{$item_unit}->{factor}) &&
357 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
358 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
360 my $baseqty = $form->{"qty_$i"} * $basefactor;
362 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
363 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
365 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
367 # set values to 0 if nothing entered
368 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
370 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
371 $fxsellprice = $form->{"sellprice_$i"};
373 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
375 my $decimalplaces = ($dec > 2) ? $dec : 2;
377 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
378 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
380 $form->{"inventory_accno_$i"} *= 1;
381 $form->{"expense_accno_$i"} *= 1;
383 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
384 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
386 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
390 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
392 if ($form->{taxincluded}) {
393 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
394 $taxbase = $linetotal - $taxamount;
396 # we are not keeping a natural price, do not round
397 $form->{"sellprice_$i"} =
398 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
400 $taxamount = $linetotal * $taxrate;
401 $taxbase = $linetotal;
404 if ($form->round_amount($taxrate, 7) == 0) {
405 if ($form->{taxincluded}) {
406 foreach my $item (@taxaccounts) {
407 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
408 $taxaccounts{$item} += $taxamount;
409 $taxdiff += $taxamount;
410 $taxbase{$item} += $taxbase;
412 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
414 foreach my $item (@taxaccounts) {
415 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
416 $taxbase{$item} += $taxbase;
420 foreach my $item (@taxaccounts) {
421 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
422 $taxbase{$item} += $taxbase;
426 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
428 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
430 # Get pricegroup_id and save it. Unfortunately the interface
431 # also uses ID "0" for signalling that none is selected, but "0"
432 # must not be stored in the database. Therefore we cannot simply
434 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
436 $pricegroup_id = undef if !$pricegroup_id;
438 # save detail record in orderitems table
439 my $orderitems_id = $form->{"orderitems_id_$i"};
440 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
443 $query = qq|INSERT INTO orderitems (
444 id, trans_id, parts_id, description, longdescription, qty, base_qty,
445 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
446 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
447 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
448 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
449 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
451 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
452 $form->{"description_$i"}, $form->{"longdescription_$i"},
453 $form->{"qty_$i"}, $baseqty,
454 $fxsellprice, $form->{"discount_$i"},
455 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
456 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
457 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
458 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
459 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
460 $form->{"lastcost_$i"},
461 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
462 conv_i($form->{"marge_price_factor_$i"}));
463 do_query($form, $dbh, $query, @values);
465 $form->{"sellprice_$i"} = $fxsellprice;
466 $form->{"discount_$i"} *= 100;
468 CVar->save_custom_variables(module => 'IC',
469 sub_module => 'orderitems',
470 trans_id => $orderitems_id,
471 configs => $ic_cvar_configs,
473 name_prefix => 'ic_',
474 name_postfix => "_$i",
479 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
483 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
485 $amount = $form->round_amount($netamount + $tax, 2);
486 $netamount = $form->round_amount($netamount, 2);
488 if ($form->{currency} eq $form->{defaultcurrency}) {
489 $form->{exchangerate} = 1;
491 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
494 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
496 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
498 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
503 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
504 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
505 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
506 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
507 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
508 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
511 @values = ($form->{ordnumber} || '', $form->{quonumber},
512 $form->{cusordnumber}, conv_date($form->{transdate}),
513 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
514 $amount, $netamount, conv_date($reqdate),
515 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
516 $form->{shipvia}, $form->{notes}, $form->{intnotes},
517 $form->{currency}, $form->{closed} ? 't' : 'f',
518 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
519 $quotation, conv_i($form->{department_id}),
520 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
521 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
522 conv_i($form->{delivery_vendor_id}),
523 conv_i($form->{delivery_customer_id}),
524 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
525 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
526 $form->{transaction_description},
527 $form->{marge_total} * 1, $form->{marge_percent} * 1,
528 conv_i($form->{id}));
529 do_query($form, $dbh, $query, @values);
531 $form->{ordtotal} = $amount;
534 $form->{name} = $form->{ $form->{vc} };
535 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
537 if (!$form->{shipto_id}) {
538 $form->add_shipto($dbh, $form->{id}, "OE");
541 # save printed, emailed, queued
542 $form->save_status($dbh);
544 # Link this record to the records it was created from.
545 $form->{convert_from_oe_ids} =~ s/^\s+//;
546 $form->{convert_from_oe_ids} =~ s/\s+$//;
547 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
548 delete $form->{convert_from_oe_ids};
550 if (scalar @convert_from_oe_ids) {
551 RecordLinks->create_links('dbh' => $dbh,
553 'from_table' => 'oe',
554 'from_ids' => \@convert_from_oe_ids,
556 'to_id' => $form->{id},
559 $self->_close_quotations_rfqs('dbh' => $dbh,
560 'from_id' => \@convert_from_oe_ids,
561 'to_id' => $form->{id});
564 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
565 if ($form->{vc} eq 'customer') {
566 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
568 if ($form->{vc} eq 'vendor') {
569 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
573 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
574 "quonumber" : "ordnumber"};
576 Common::webdav_folder($form);
578 my $rc = $dbh->commit;
580 $self->save_periodic_invoices_config(dbh => $dbh,
581 oe_id => $form->{id},
582 config_yaml => $form->{periodic_invoices_config})
583 if ($form->{type} eq 'sales_order');
585 $main::lxdebug->leave_sub();
590 sub save_periodic_invoices_config {
591 my ($self, %params) = @_;
593 return if !$params{oe_id};
595 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
596 return if 'HASH' ne ref $config;
598 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
599 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
600 $obj->update_attributes(%{ $config });
603 sub load_periodic_invoice_config {
607 delete $form->{periodic_invoices_config};
610 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
613 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
614 print printer_id copies) };
615 $form->{periodic_invoices_config} = YAML::Dump($config);
620 sub _close_quotations_rfqs {
621 $main::lxdebug->enter_sub();
626 Common::check_params(\%params, qw(from_id to_id));
628 my $myconfig = \%main::myconfig;
629 my $form = $main::form;
631 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
633 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
634 my $sth = prepare_query($form, $dbh, $query);
636 do_statement($form, $sth, $query, conv_i($params{to_id}));
638 my ($quotation) = $sth->fetchrow_array();
641 $main::lxdebug->leave_sub();
647 foreach my $from_id (@{ $params{from_id} }) {
648 $from_id = conv_i($from_id);
649 do_statement($form, $sth, $query, $from_id);
650 ($quotation) = $sth->fetchrow_array();
651 push @close_ids, $from_id if ($quotation);
656 if (scalar @close_ids) {
657 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
658 do_query($form, $dbh, $query, @close_ids);
660 $dbh->commit() unless ($params{dbh});
663 $main::lxdebug->leave_sub();
667 $main::lxdebug->enter_sub();
669 my ($self, $myconfig, $form) = @_;
671 my $rc = SL::DB::Order->new->db->with_transaction(sub {
672 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
674 SL::DB::Order->new(id => $form->{id})->delete;
676 my $spool = $::lx_office_conf{paths}->{spool};
677 unlink map { "$spool/$_" } @spoolfiles if $spool;
682 $main::lxdebug->leave_sub();
688 $main::lxdebug->enter_sub();
690 my ($self, $myconfig, $form) = @_;
692 # connect to database
693 my $dbh = $form->get_standard_dbh;
695 my ($query, $query_add, @values, @ids, $sth);
697 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
699 push @ids, $form->{"trans_id_$_"}
700 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
701 } (1 .. $form->{"rowcount"});
703 if ($form->{rowcount} && scalar @ids) {
704 $form->{convert_from_oe_ids} = join ' ', @ids;
707 # if called in multi id mode, and still only got one id, switch back to single id
708 if ($form->{"rowcount"} and $#ids == 0) {
709 $form->{"id"} = $ids[0];
713 # and remember for the rest of the function
714 my $is_collective_order = scalar @ids;
717 my $wday = (localtime(time))[6];
718 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
719 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
722 # get default accounts
723 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
724 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
725 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
726 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
727 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
730 my $ref = selectfirst_hashref_query($form, $dbh, $query);
731 map { $form->{$_} = $ref->{$_} } keys %$ref;
733 $form->{currency} = $form->get_default_currency($myconfig);
735 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
736 # we come from invoices, feel free.
737 $form->{reqdate} = $form->{deliverydate}
738 if ( $form->{deliverydate}
739 and $form->{callback} =~ /action=ar_transactions/);
741 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
743 if ($form->{id} or @ids) {
745 # retrieve order for single id
746 # NOTE: this query is intended to fetch all information only ONCE.
747 # so if any of these infos is important (or even different) for any item,
748 # it will be killed out and then has to be fetched from the item scope query further down
750 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
751 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
752 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
753 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
754 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
755 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
756 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
757 o.globalproject_id, o.delivered, o.transaction_description
759 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
760 LEFT JOIN employee e ON (o.employee_id = e.id)
761 LEFT JOIN department d ON (o.department_id = d.id) | .
764 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
766 @values = $form->{id} ? ($form->{id}) : @ids;
767 $sth = prepare_execute_query($form, $dbh, $query, @values);
769 $ref = $sth->fetchrow_hashref("NAME_lc");
772 map { $form->{$_} = $ref->{$_} } keys %$ref;
774 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
776 # set all entries for multiple ids blank that yield different information
777 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
778 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
782 # if not given, fill transdate with current_date
783 $form->{transdate} = $form->current_date($myconfig)
784 unless $form->{transdate};
788 if ($form->{delivery_customer_id}) {
789 $query = qq|SELECT name FROM customer WHERE id = ?|;
790 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
793 if ($form->{delivery_vendor_id}) {
794 $query = qq|SELECT name FROM customer WHERE id = ?|;
795 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
798 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
800 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
801 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
803 $ref = $sth->fetchrow_hashref("NAME_lc");
805 map { $form->{$_} = $ref->{$_} } keys %$ref;
808 # get printed, emailed and queued
809 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
810 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
812 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
813 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
814 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
815 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
818 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
821 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
823 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
825 # retrieve individual items
826 # this query looks up all information about the items
827 # stuff different from the whole will not be overwritten, but saved with a suffix.
829 qq|SELECT o.id AS orderitems_id,
830 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
831 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
832 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
833 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
834 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
835 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
836 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
837 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
838 o.price_factor_id, o.price_factor, o.marge_price_factor,
839 pr.projectnumber, p.formel,
840 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
842 JOIN parts p ON (o.parts_id = p.id)
843 JOIN oe ON (o.trans_id = oe.id)
844 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
845 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
846 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
847 LEFT JOIN project pr ON (o.project_id = pr.id)
848 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
850 ? qq|WHERE o.trans_id = ?|
851 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
854 @ids = $form->{id} ? ($form->{id}) : @ids;
855 $sth = prepare_execute_query($form, $dbh, $query, @values);
857 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
858 # Retrieve custom variables.
859 my $cvars = CVar->get_custom_variables(dbh => $dbh,
861 sub_module => 'orderitems',
862 trans_id => $ref->{orderitems_id},
864 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
867 if (!$ref->{"part_inventory_accno_id"}) {
868 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
870 delete($ref->{"part_inventory_accno_id"});
872 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
873 # unless already present there
874 # remove _oe entries afterwards
875 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
876 qw|ordnumber transdate cusordnumber|
878 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
882 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
884 qq|SELECT accno AS inventory_accno, | .
885 qq| new_chart_id AS inventory_new_chart, | .
886 qq| date($transdate) - valid_from AS inventory_valid | .
887 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
888 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
889 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
892 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
894 qq|SELECT accno AS income_accno, | .
895 qq| new_chart_id AS income_new_chart, | .
896 qq| date($transdate) - valid_from AS income_valid | .
897 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
898 ($ref->{income_accno}, $ref->{income_new_chart},
899 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
902 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
904 qq|SELECT accno AS expense_accno, | .
905 qq| new_chart_id AS expense_new_chart, | .
906 qq| date($transdate) - valid_from AS expense_valid | .
907 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
908 ($ref->{expense_accno}, $ref->{expense_new_chart},
909 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
912 # delete orderitems_id in collective orders, so that they get cloned no matter what
913 delete $ref->{orderitems_id} if (@ids);
915 # get tax rates and description
916 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
918 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
919 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
920 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
921 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
922 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
923 qq|ORDER BY c.accno|;
924 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
925 $ref->{taxaccounts} = "";
927 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
928 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
932 $ref->{taxaccounts} .= "$ptr->{accno} ";
933 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
934 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
935 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
936 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
937 $form->{taxaccounts} .= "$ptr->{accno} ";
942 chop $ref->{taxaccounts};
944 push @{ $form->{form_details} }, $ref;
952 $form->lastname_used($dbh, $myconfig, $form->{vc})
953 unless $form->{"$form->{vc}_id"};
957 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
959 Common::webdav_folder($form);
961 $self->load_periodic_invoice_config($form);
963 my $rc = $dbh->commit;
965 $main::lxdebug->leave_sub();
970 sub retrieve_simple {
971 $main::lxdebug->enter_sub();
976 Common::check_params(\%params, qw(id));
978 my $myconfig = \%main::myconfig;
979 my $form = $main::form;
981 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
983 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
984 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
986 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
987 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
989 $main::lxdebug->leave_sub();
995 $main::lxdebug->enter_sub();
997 my ($self, $myconfig, $form) = @_;
999 # connect to database
1000 my $dbh = $form->get_standard_dbh;
1006 my $nodiscount_subtotal = 0;
1007 my $discount_subtotal = 0;
1010 my @partsgroup = ();
1013 my $subtotal_header = 0;
1014 my $subposition = 0;
1020 my (@project_ids, %projectnumbers, %projectdescriptions);
1022 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1024 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1025 'departments' => 'ALL_DEPARTMENTS');
1028 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1029 $price_factors{$pfac->{id}} = $pfac;
1030 $pfac->{factor} *= 1;
1031 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1035 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1036 next unless $dept->{id} eq $form->{department_id};
1037 $form->{department} = $dept->{description};
1041 # sort items by partsgroup
1042 for $i (1 .. $form->{rowcount}) {
1044 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1045 $partsgroup = $form->{"partsgroup_$i"};
1047 push @partsgroup, [$i, $partsgroup];
1048 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1052 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1053 join(", ", map("?", @project_ids)) . ")";
1054 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1055 while (my $ref = $sth->fetchrow_hashref()) {
1056 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1057 $projectdescriptions{$ref->{id}} = $ref->{description};
1062 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1063 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1065 $form->{discount} = [];
1067 $form->{TEMPLATE_ARRAYS} = { };
1068 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1070 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1073 qw(runningnumber number description longdescription qty ship unit bin
1074 partnotes serialnumber reqdate sellprice listprice netprice
1075 discount p_discount discount_sub nodiscount_sub
1076 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1077 price_factor price_factor_name partsgroup weight lineweight);
1079 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1081 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1083 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1085 my $totalweight = 0;
1087 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1090 if ($item->[1] ne $sameitem) {
1091 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1092 $sameitem = $item->[1];
1094 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1097 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1099 if ($form->{"id_$i"} != 0) {
1101 # add number, description and qty to $form->{number}, ....
1103 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1104 $subtotal_header = $i;
1105 $position = int($position);
1108 } elsif ($subtotal_header) {
1110 $position = int($position);
1111 $position = $position.".".$subposition;
1113 $position = int($position);
1117 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1119 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1120 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1121 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1122 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1123 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1124 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1125 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1126 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1127 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1128 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1129 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1130 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1131 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1132 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1133 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1134 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1135 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1136 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1137 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1139 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1140 my ($dec) = ($sellprice =~ /\.(\d+)/);
1141 my $decimalplaces = max 2, length($dec);
1143 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1145 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1146 my $linetotal = $form->round_amount($linetotal_exact, 2);
1148 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1149 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1151 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1153 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1155 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1157 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1158 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1160 $linetotal = ($linetotal != 0) ? $linetotal : '';
1162 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1163 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1164 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1166 $form->{ordtotal} += $linetotal;
1167 $form->{nodiscount_total} += $nodiscount_linetotal;
1168 $form->{discount_total} += $discount;
1170 if ($subtotal_header) {
1171 $discount_subtotal += $linetotal;
1172 $nodiscount_subtotal += $nodiscount_linetotal;
1175 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1176 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1177 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1178 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1179 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1181 $discount_subtotal = 0;
1182 $nodiscount_subtotal = 0;
1183 $subtotal_header = 0;
1186 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1189 if (!$form->{"discount_$i"}) {
1190 $nodiscount += $linetotal;
1193 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1194 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1195 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1196 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1197 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1198 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1200 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1201 $totalweight += $lineweight;
1202 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1203 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1204 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1205 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1207 my ($taxamount, $taxbase);
1210 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1212 if ($form->{taxincluded}) {
1215 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1216 $taxbase = $linetotal / (1 + $taxrate);
1218 $taxamount = $linetotal * $taxrate;
1219 $taxbase = $linetotal;
1222 if ($taxamount != 0) {
1223 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1224 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1225 $taxbase{$accno} += $taxbase;
1229 $tax_rate = $taxrate * 100;
1230 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1232 if ($form->{"assembly_$i"}) {
1235 # get parts and push them onto the stack
1237 if ($form->{groupitems}) {
1238 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1240 $sortorder = qq|ORDER BY a.oid|;
1243 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1244 qq|pg.partsgroup | .
1245 qq|FROM assembly a | .
1246 qq| JOIN parts p ON (a.parts_id = p.id) | .
1247 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1248 qq| WHERE a.bom = '1' | .
1249 qq| AND a.id = ? | . $sortorder;
1250 @values = ($form->{"id_$i"});
1251 $sth = $dbh->prepare($query);
1252 $sth->execute(@values) || $form->dberror($query);
1254 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1255 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1256 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1257 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1258 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1261 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1262 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1267 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1268 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1269 for @{ $ic_cvar_configs };
1273 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1274 $form->{totalweight_nofmt} = $totalweight;
1275 my $defaults = AM->get_defaults();
1276 $form->{weightunit} = $defaults->{weightunit};
1279 foreach $item (sort keys %taxaccounts) {
1280 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1282 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1283 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1284 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1285 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1286 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1287 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1288 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1290 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1291 my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
1292 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1295 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1296 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1297 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1298 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1300 if($form->{taxincluded}) {
1301 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1302 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1304 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1305 $form->{subtotal_nofmt} = $form->{ordtotal};
1308 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1311 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1313 if ($form->{type} =~ /_quotation/) {
1314 $form->set_payment_options($myconfig, $form->{quodate});
1316 $form->set_payment_options($myconfig, $form->{orddate});
1319 $form->{username} = $myconfig->{name};
1323 $main::lxdebug->leave_sub();
1326 sub project_description {
1327 $main::lxdebug->enter_sub();
1329 my ($self, $dbh, $id) = @_;
1331 my $query = qq|SELECT description FROM project WHERE id = ?|;
1332 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1334 $main::lxdebug->leave_sub();
1345 OE.pm - Order entry module
1349 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>.
1355 =item retrieve_simple PARAMS
1357 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1359 my $order = retrieve_simple(id => 2);