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);
43 use SL::DB::PeriodicInvoicesConfig;
50 $main::lxdebug->enter_sub();
52 my ($self, $myconfig, $form) = @_;
55 my $dbh = $form->get_standard_dbh;
58 my $ordnumber = 'ordnumber';
64 my ($periodic_invoices_columns, $periodic_invoices_joins);
66 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
68 if ($form->{type} =~ /_quotation$/) {
70 $ordnumber = 'quonumber';
72 } elsif ($form->{type} eq 'sales_order') {
73 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
74 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
77 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
80 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
81 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
82 qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
83 qq| o.transaction_description, | .
84 qq| o.marge_total, o.marge_percent, | .
85 qq| ex.$rate AS exchangerate, | .
86 qq| pr.projectnumber AS globalprojectnumber, | .
87 qq| e.name AS employee, s.name AS salesman, | .
88 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid | .
89 $periodic_invoices_columns .
91 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
92 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
93 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
94 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
95 qq| AND ex.transdate = o.transdate) | .
96 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
97 qq|$periodic_invoices_joins | .
98 qq|WHERE (o.quotation = ?) |;
99 push(@values, $quotation);
101 my ($null, $split_department_id) = split /--/, $form->{department};
102 my $department_id = $form->{department_id} || $split_department_id;
103 if ($department_id) {
104 $query .= qq| AND o.department_id = ?|;
105 push(@values, $department_id);
108 if ($form->{"project_id"}) {
110 qq|AND ((globalproject_id = ?) OR EXISTS | .
111 qq| (SELECT * FROM orderitems oi | .
112 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
113 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
116 if ($form->{"projectnumber"}) {
118 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
119 SELECT * FROM orderitems oi
120 LEFT JOIN project proi ON proi.id = oi.project_id
121 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
124 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
127 if ($form->{"${vc}_id"}) {
128 $query .= " AND o.${vc}_id = ?";
129 push(@values, $form->{"${vc}_id"});
131 } elsif ($form->{$vc}) {
132 $query .= " AND ct.name ILIKE ?";
133 push(@values, '%' . $form->{$vc} . '%');
136 if (!$main::auth->assert('sales_all_edit', 1)) {
137 $query .= " AND o.employee_id = (select id from employee where login= ?)";
138 push @values, $form->{login};
140 if ($form->{employee_id}) {
141 $query .= " AND o.employee_id = ?";
142 push @values, conv_i($form->{employee_id});
145 if ($form->{salesman_id}) {
146 $query .= " AND o.salesman_id = ?";
147 push @values, conv_i($form->{salesman_id});
150 if (!$form->{open} && !$form->{closed}) {
151 $query .= " AND o.id = 0";
152 } elsif (!($form->{open} && $form->{closed})) {
153 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
156 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
157 ($form->{"notdelivered"} ne $form->{"delivered"})) {
158 $query .= $form->{"delivered"} ?
159 " AND o.delivered " : " AND NOT o.delivered";
162 if ($form->{$ordnumber}) {
163 $query .= qq| AND o.$ordnumber ILIKE ?|;
164 push(@values, '%' . $form->{$ordnumber} . '%');
167 if($form->{transdatefrom}) {
168 $query .= qq| AND o.transdate >= ?|;
169 push(@values, conv_date($form->{transdatefrom}));
172 if($form->{transdateto}) {
173 $query .= qq| AND o.transdate <= ?|;
174 push(@values, conv_date($form->{transdateto}));
177 if($form->{reqdatefrom}) {
178 $query .= qq| AND o.reqdate >= ?|;
179 push(@values, conv_date($form->{reqdatefrom}));
182 if($form->{reqdateto}) {
183 $query .= qq| AND o.reqdate <= ?|;
184 push(@values, conv_date($form->{reqdateto}));
187 if ($form->{transaction_description}) {
188 $query .= qq| AND o.transaction_description ILIKE ?|;
189 push(@values, '%' . $form->{transaction_description} . '%');
192 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
193 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
194 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
197 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
198 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
199 my %allowed_sort_columns = (
200 "transdate" => "o.transdate",
201 "reqdate" => "o.reqdate",
203 "ordnumber" => "o.ordnumber",
204 "quonumber" => "o.quonumber",
206 "employee" => "e.name",
207 "salesman" => "s.name",
208 "shipvia" => "o.shipvia",
209 "transaction_description" => "o.transaction_description"
211 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
212 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}";
214 $query .= qq| ORDER by | . $sortorder;
216 my $sth = $dbh->prepare($query);
217 $sth->execute(@values) ||
218 $form->dberror($query . " (" . join(", ", @values) . ")");
222 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
223 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
224 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
225 $id{ $ref->{id} } = $ref->{id};
230 $main::lxdebug->leave_sub();
233 sub transactions_for_todo_list {
234 $main::lxdebug->enter_sub();
239 my $myconfig = \%main::myconfig;
240 my $form = $main::form;
242 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
244 my $query = qq|SELECT id FROM employee WHERE login = ?|;
245 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
248 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
249 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
254 LEFT JOIN customer c ON (oe.customer_id = c.id)
255 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
256 LEFT JOIN employee e ON (oe.employee_id = e.id)
257 WHERE (COALESCE(quotation, FALSE) = TRUE)
258 AND (COALESCE(closed, FALSE) = FALSE)
259 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
260 AND NOT (oe.reqdate ISNULL)
261 AND (oe.reqdate < current_date)
264 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
266 $main::lxdebug->leave_sub();
272 $main::lxdebug->enter_sub();
274 my ($self, $myconfig, $form) = @_;
276 # connect to database, turn off autocommit
277 my $dbh = $form->get_standard_dbh;
279 my ($query, @values, $sth, $null);
280 my $exchangerate = 0;
282 my $all_units = AM->retrieve_units($myconfig, $form);
283 $form->{all_units} = $all_units;
285 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
288 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
289 unless ($form->{employee_id}) {
290 $form->get_employee($dbh);
293 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
296 $query = qq|DELETE FROM custom_variables
297 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
298 AND (sub_module = 'orderitems')
299 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
300 do_query($form, $dbh, $query, $form->{id});
302 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
303 do_query($form, $dbh, $query, $form->{id});
305 $query = qq|DELETE FROM shipto | .
306 qq|WHERE trans_id = ? AND module = 'OE'|;
307 do_query($form, $dbh, $query, $form->{id});
311 $query = qq|SELECT nextval('id')|;
312 ($form->{id}) = selectrow_query($form, $dbh, $query);
314 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
315 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
333 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
334 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
337 for my $i (1 .. $form->{rowcount}) {
339 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
341 if ($form->{"id_$i"}) {
344 $query = qq|SELECT unit FROM parts WHERE id = ?|;
345 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
348 if (defined($all_units->{$item_unit}->{factor}) &&
349 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
350 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
352 my $baseqty = $form->{"qty_$i"} * $basefactor;
354 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
355 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
357 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
359 # set values to 0 if nothing entered
360 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
362 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
363 $fxsellprice = $form->{"sellprice_$i"};
365 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
367 my $decimalplaces = ($dec > 2) ? $dec : 2;
369 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
370 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
372 $form->{"inventory_accno_$i"} *= 1;
373 $form->{"expense_accno_$i"} *= 1;
375 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
376 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
378 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
382 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
384 if ($form->{taxincluded}) {
385 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
386 $taxbase = $linetotal - $taxamount;
388 # we are not keeping a natural price, do not round
389 $form->{"sellprice_$i"} =
390 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
392 $taxamount = $linetotal * $taxrate;
393 $taxbase = $linetotal;
396 if ($form->round_amount($taxrate, 7) == 0) {
397 if ($form->{taxincluded}) {
398 foreach my $item (@taxaccounts) {
399 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
400 $taxaccounts{$item} += $taxamount;
401 $taxdiff += $taxamount;
402 $taxbase{$item} += $taxbase;
404 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
406 foreach my $item (@taxaccounts) {
407 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
408 $taxbase{$item} += $taxbase;
412 foreach my $item (@taxaccounts) {
413 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
414 $taxbase{$item} += $taxbase;
418 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
420 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
422 # Get pricegroup_id and save it. Unfortunately the interface
423 # also uses ID "0" for signalling that none is selected, but "0"
424 # must not be stored in the database. Therefore we cannot simply
426 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
428 $pricegroup_id = undef if !$pricegroup_id;
430 # save detail record in orderitems table
431 my $orderitems_id = $form->{"orderitems_id_$i"};
432 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
435 $query = qq|INSERT INTO orderitems (
436 id, trans_id, parts_id, description, longdescription, qty, base_qty,
437 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
438 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
439 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
440 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
441 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
443 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
444 $form->{"description_$i"}, $form->{"longdescription_$i"},
445 $form->{"qty_$i"}, $baseqty,
446 $fxsellprice, $form->{"discount_$i"},
447 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
448 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
449 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
450 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
451 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
452 $form->{"lastcost_$i"},
453 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
454 conv_i($form->{"marge_price_factor_$i"}));
455 do_query($form, $dbh, $query, @values);
457 $form->{"sellprice_$i"} = $fxsellprice;
458 $form->{"discount_$i"} *= 100;
460 CVar->save_custom_variables(module => 'IC',
461 sub_module => 'orderitems',
462 trans_id => $orderitems_id,
463 configs => $ic_cvar_configs,
465 name_prefix => 'ic_',
466 name_postfix => "_$i",
471 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
475 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
477 $amount = $form->round_amount($netamount + $tax, 2);
478 $netamount = $form->round_amount($netamount, 2);
480 if ($form->{currency} eq $form->{defaultcurrency}) {
481 $form->{exchangerate} = 1;
483 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
486 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
488 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
490 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
495 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
496 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
497 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
498 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
499 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
500 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
503 @values = ($form->{ordnumber} || '', $form->{quonumber},
504 $form->{cusordnumber}, conv_date($form->{transdate}),
505 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
506 $amount, $netamount, conv_date($reqdate),
507 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
508 $form->{shipvia}, $form->{notes}, $form->{intnotes},
509 $form->{currency}, $form->{closed} ? 't' : 'f',
510 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
511 $quotation, conv_i($form->{department_id}),
512 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
513 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
514 conv_i($form->{delivery_vendor_id}),
515 conv_i($form->{delivery_customer_id}),
516 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
517 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
518 $form->{transaction_description},
519 $form->{marge_total} * 1, $form->{marge_percent} * 1,
520 conv_i($form->{id}));
521 do_query($form, $dbh, $query, @values);
523 $form->{ordtotal} = $amount;
526 $form->{name} = $form->{ $form->{vc} };
527 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
529 if (!$form->{shipto_id}) {
530 $form->add_shipto($dbh, $form->{id}, "OE");
533 # save printed, emailed, queued
534 $form->save_status($dbh);
536 # Link this record to the records it was created from.
537 $form->{convert_from_oe_ids} =~ s/^\s+//;
538 $form->{convert_from_oe_ids} =~ s/\s+$//;
539 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
540 delete $form->{convert_from_oe_ids};
542 if (scalar @convert_from_oe_ids) {
543 RecordLinks->create_links('dbh' => $dbh,
545 'from_table' => 'oe',
546 'from_ids' => \@convert_from_oe_ids,
548 'to_id' => $form->{id},
551 $self->_close_quotations_rfqs('dbh' => $dbh,
552 'from_id' => \@convert_from_oe_ids,
553 'to_id' => $form->{id});
556 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
557 if ($form->{vc} eq 'customer') {
558 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
560 if ($form->{vc} eq 'vendor') {
561 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
565 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
566 "quonumber" : "ordnumber"};
568 Common::webdav_folder($form);
570 my $rc = $dbh->commit;
572 $self->save_periodic_invoices_config(dbh => $dbh,
573 oe_id => $form->{id},
574 config_yaml => $form->{periodic_invoices_config})
575 if ($form->{type} eq 'sales_order');
577 $main::lxdebug->leave_sub();
582 sub save_periodic_invoices_config {
583 my ($self, %params) = @_;
585 return if !$params{oe_id};
587 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
588 return if 'HASH' ne ref $config;
590 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
591 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
592 $obj->update_attributes(%{ $config });
595 sub load_periodic_invoice_config {
599 delete $form->{periodic_invoices_config};
602 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
605 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
606 print printer_id copies) };
607 $form->{periodic_invoices_config} = YAML::Dump($config);
612 sub _close_quotations_rfqs {
613 $main::lxdebug->enter_sub();
618 Common::check_params(\%params, qw(from_id to_id));
620 my $myconfig = \%main::myconfig;
621 my $form = $main::form;
623 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
625 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
626 my $sth = prepare_query($form, $dbh, $query);
628 do_statement($form, $sth, $query, conv_i($params{to_id}));
630 my ($quotation) = $sth->fetchrow_array();
633 $main::lxdebug->leave_sub();
639 foreach my $from_id (@{ $params{from_id} }) {
640 $from_id = conv_i($from_id);
641 do_statement($form, $sth, $query, $from_id);
642 ($quotation) = $sth->fetchrow_array();
643 push @close_ids, $from_id if ($quotation);
648 if (scalar @close_ids) {
649 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
650 do_query($form, $dbh, $query, @close_ids);
652 $dbh->commit() unless ($params{dbh});
655 $main::lxdebug->leave_sub();
659 $main::lxdebug->enter_sub();
661 my ($self, $myconfig, $form) = @_;
663 # connect to database
664 my $dbh = $form->get_standard_dbh;
668 my $query = qq|SELECT s.spoolfile FROM status s | .
669 qq|WHERE s.trans_id = ?|;
670 my @values = (conv_i($form->{id}));
671 my $sth = $dbh->prepare($query);
672 $sth->execute(@values) || $self->dberror($query);
677 while (($spoolfile) = $sth->fetchrow_array) {
678 push @spoolfiles, $spoolfile;
683 @values = (conv_i($form->{id}));
685 # periodic invoices and their configuration
686 do_query($form, $dbh, qq|DELETE FROM periodic_invoices WHERE config_id IN (SELECT id FROM periodic_invoices_configs WHERE oe_id = ?)|, @values);
687 do_query($form, $dbh, qq|DELETE FROM periodic_invoices_configs WHERE oe_id = ?|, @values);
689 # delete status entries
690 $query = qq|DELETE FROM status | .
691 qq|WHERE trans_id = ?|;
692 do_query($form, $dbh, $query, @values);
694 # delete individual entries
695 $query = qq|DELETE FROM orderitems | .
696 qq|WHERE trans_id = ?|;
697 do_query($form, $dbh, $query, @values);
699 $query = qq|DELETE FROM shipto | .
700 qq|WHERE trans_id = ? AND module = 'OE'|;
701 do_query($form, $dbh, $query, @values);
704 $query = qq|DELETE FROM oe | .
706 do_query($form, $dbh, $query, @values);
708 my $rc = $dbh->commit;
711 my $spool = $::lx_office_conf{paths}->{spool};
712 foreach $spoolfile (@spoolfiles) {
713 unlink "$spool/$spoolfile" if $spoolfile;
717 $main::lxdebug->leave_sub();
723 $main::lxdebug->enter_sub();
725 my ($self, $myconfig, $form) = @_;
727 # connect to database
728 my $dbh = $form->get_standard_dbh;
730 my ($query, $query_add, @values, @ids, $sth);
732 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
734 push @ids, $form->{"trans_id_$_"}
735 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
736 } (1 .. $form->{"rowcount"});
738 if ($form->{rowcount} && scalar @ids) {
739 $form->{convert_from_oe_ids} = join ' ', @ids;
742 # if called in multi id mode, and still only got one id, switch back to single id
743 if ($form->{"rowcount"} and $#ids == 0) {
744 $form->{"id"} = $ids[0];
748 # and remember for the rest of the function
749 my $is_collective_order = scalar @ids;
752 my $wday = (localtime(time))[6];
753 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
754 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
757 # get default accounts
758 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
759 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
760 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
761 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
762 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
765 my $ref = selectfirst_hashref_query($form, $dbh, $query);
766 map { $form->{$_} = $ref->{$_} } keys %$ref;
768 $form->{currency} = $form->get_default_currency($myconfig);
770 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
771 # we come from invoices, feel free.
772 $form->{reqdate} = $form->{deliverydate}
773 if ( $form->{deliverydate}
774 and $form->{callback} =~ /action=ar_transactions/);
776 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
778 if ($form->{id} or @ids) {
780 # retrieve order for single id
781 # NOTE: this query is intended to fetch all information only ONCE.
782 # so if any of these infos is important (or even different) for any item,
783 # it will be killed out and then has to be fetched from the item scope query further down
785 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
786 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
787 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
788 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
789 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
790 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
791 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
792 o.globalproject_id, o.delivered, o.transaction_description
794 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
795 LEFT JOIN employee e ON (o.employee_id = e.id)
796 LEFT JOIN department d ON (o.department_id = d.id) | .
799 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
801 @values = $form->{id} ? ($form->{id}) : @ids;
802 $sth = prepare_execute_query($form, $dbh, $query, @values);
804 $ref = $sth->fetchrow_hashref("NAME_lc");
807 map { $form->{$_} = $ref->{$_} } keys %$ref;
809 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
811 # set all entries for multiple ids blank that yield different information
812 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
813 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
817 # if not given, fill transdate with current_date
818 $form->{transdate} = $form->current_date($myconfig)
819 unless $form->{transdate};
823 if ($form->{delivery_customer_id}) {
824 $query = qq|SELECT name FROM customer WHERE id = ?|;
825 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
828 if ($form->{delivery_vendor_id}) {
829 $query = qq|SELECT name FROM customer WHERE id = ?|;
830 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
833 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
835 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
836 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
838 $ref = $sth->fetchrow_hashref("NAME_lc");
840 map { $form->{$_} = $ref->{$_} } keys %$ref;
843 # get printed, emailed and queued
844 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
845 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
847 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
848 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
849 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
850 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
853 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
856 my %oid = ('Pg' => 'oid',
857 'Oracle' => 'rowid');
859 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
861 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
863 # retrieve individual items
864 # this query looks up all information about the items
865 # stuff different from the whole will not be overwritten, but saved with a suffix.
867 qq|SELECT o.id AS orderitems_id,
868 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
869 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
870 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
871 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
872 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
873 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
874 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
875 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
876 o.price_factor_id, o.price_factor, o.marge_price_factor,
877 pr.projectnumber, p.formel,
878 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
880 JOIN parts p ON (o.parts_id = p.id)
881 JOIN oe ON (o.trans_id = oe.id)
882 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
883 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
884 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
885 LEFT JOIN project pr ON (o.project_id = pr.id)
886 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
888 ? qq|WHERE o.trans_id = ?|
889 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
890 qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
892 @ids = $form->{id} ? ($form->{id}) : @ids;
893 $sth = prepare_execute_query($form, $dbh, $query, @values);
895 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
896 # Retrieve custom variables.
897 my $cvars = CVar->get_custom_variables(dbh => $dbh,
899 sub_module => 'orderitems',
900 trans_id => $ref->{orderitems_id},
902 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
905 if (!$ref->{"part_inventory_accno_id"}) {
906 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
908 delete($ref->{"part_inventory_accno_id"});
910 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
911 # unless already present there
912 # remove _oe entries afterwards
913 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
914 qw|ordnumber transdate cusordnumber|
916 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
920 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
922 qq|SELECT accno AS inventory_accno, | .
923 qq| new_chart_id AS inventory_new_chart, | .
924 qq| date($transdate) - valid_from AS inventory_valid | .
925 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
926 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
927 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
930 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
932 qq|SELECT accno AS income_accno, | .
933 qq| new_chart_id AS income_new_chart, | .
934 qq| date($transdate) - valid_from AS income_valid | .
935 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
936 ($ref->{income_accno}, $ref->{income_new_chart},
937 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
940 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
942 qq|SELECT accno AS expense_accno, | .
943 qq| new_chart_id AS expense_new_chart, | .
944 qq| date($transdate) - valid_from AS expense_valid | .
945 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
946 ($ref->{expense_accno}, $ref->{expense_new_chart},
947 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
950 # delete orderitems_id in collective orders, so that they get cloned no matter what
951 delete $ref->{orderitems_id} if (@ids);
953 # get tax rates and description
954 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
956 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
957 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
958 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
959 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
960 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
961 qq|ORDER BY c.accno|;
962 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
963 $ref->{taxaccounts} = "";
965 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
966 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
970 $ref->{taxaccounts} .= "$ptr->{accno} ";
971 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
972 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
973 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
974 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
975 $form->{taxaccounts} .= "$ptr->{accno} ";
980 chop $ref->{taxaccounts};
982 push @{ $form->{form_details} }, $ref;
990 $form->lastname_used($dbh, $myconfig, $form->{vc})
991 unless $form->{"$form->{vc}_id"};
995 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
997 Common::webdav_folder($form);
999 $self->load_periodic_invoice_config($form);
1001 my $rc = $dbh->commit;
1003 $main::lxdebug->leave_sub();
1008 sub retrieve_simple {
1009 $main::lxdebug->enter_sub();
1014 Common::check_params(\%params, qw(id));
1016 my $myconfig = \%main::myconfig;
1017 my $form = $main::form;
1019 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1021 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1022 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1024 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1025 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1027 $main::lxdebug->leave_sub();
1033 $main::lxdebug->enter_sub();
1035 my ($self, $myconfig, $form) = @_;
1037 # connect to database
1038 my $dbh = $form->get_standard_dbh;
1044 my $nodiscount_subtotal = 0;
1045 my $discount_subtotal = 0;
1048 my @partsgroup = ();
1051 my $subtotal_header = 0;
1052 my $subposition = 0;
1059 my %oid = ('Pg' => 'oid',
1060 'Oracle' => 'rowid');
1062 my (@project_ids, %projectnumbers, %projectdescriptions);
1064 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1066 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1067 'departments' => 'ALL_DEPARTMENTS');
1070 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1071 $price_factors{$pfac->{id}} = $pfac;
1072 $pfac->{factor} *= 1;
1073 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1077 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1078 next unless $dept->{id} eq $form->{department_id};
1079 $form->{department} = $dept->{description};
1083 # sort items by partsgroup
1084 for $i (1 .. $form->{rowcount}) {
1086 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1087 $partsgroup = $form->{"partsgroup_$i"};
1089 push @partsgroup, [$i, $partsgroup];
1090 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1094 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1095 join(", ", map("?", @project_ids)) . ")";
1096 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1097 while (my $ref = $sth->fetchrow_hashref()) {
1098 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1099 $projectdescriptions{$ref->{id}} = $ref->{description};
1104 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1105 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1107 $form->{discount} = [];
1109 $form->{TEMPLATE_ARRAYS} = { };
1110 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1112 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1115 qw(runningnumber number description longdescription qty ship unit bin
1116 partnotes serialnumber reqdate sellprice listprice netprice
1117 discount p_discount discount_sub nodiscount_sub
1118 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1119 price_factor price_factor_name partsgroup weight lineweight);
1121 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1123 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1125 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1127 my $totalweight = 0;
1129 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1132 if ($item->[1] ne $sameitem) {
1133 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1134 $sameitem = $item->[1];
1136 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1139 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1141 if ($form->{"id_$i"} != 0) {
1143 # add number, description and qty to $form->{number}, ....
1145 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1146 $subtotal_header = $i;
1147 $position = int($position);
1150 } elsif ($subtotal_header) {
1152 $position = int($position);
1153 $position = $position.".".$subposition;
1155 $position = int($position);
1159 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1161 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1162 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1163 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1164 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1165 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1166 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1167 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1168 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1169 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1170 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1171 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1172 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1173 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1174 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1175 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1176 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1177 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1178 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1179 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1181 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1182 my ($dec) = ($sellprice =~ /\.(\d+)/);
1183 my $decimalplaces = max 2, length($dec);
1185 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1187 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1188 my $linetotal = $form->round_amount($linetotal_exact, 2);
1190 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1191 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1193 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1195 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1197 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1199 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1200 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1202 $linetotal = ($linetotal != 0) ? $linetotal : '';
1204 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1205 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1206 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1208 $form->{ordtotal} += $linetotal;
1209 $form->{nodiscount_total} += $nodiscount_linetotal;
1210 $form->{discount_total} += $discount;
1212 if ($subtotal_header) {
1213 $discount_subtotal += $linetotal;
1214 $nodiscount_subtotal += $nodiscount_linetotal;
1217 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1218 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1219 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1220 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1221 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1223 $discount_subtotal = 0;
1224 $nodiscount_subtotal = 0;
1225 $subtotal_header = 0;
1228 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1231 if (!$form->{"discount_$i"}) {
1232 $nodiscount += $linetotal;
1235 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1236 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1237 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1238 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1239 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1240 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1242 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1243 $totalweight += $lineweight;
1244 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1245 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1246 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1247 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1249 my ($taxamount, $taxbase);
1252 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1254 if ($form->{taxincluded}) {
1257 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1258 $taxbase = $linetotal / (1 + $taxrate);
1260 $taxamount = $linetotal * $taxrate;
1261 $taxbase = $linetotal;
1264 if ($taxamount != 0) {
1265 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1266 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1267 $taxbase{$accno} += $taxbase;
1271 $tax_rate = $taxrate * 100;
1272 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1274 if ($form->{"assembly_$i"}) {
1277 # get parts and push them onto the stack
1279 if ($form->{groupitems}) {
1280 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
1282 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
1285 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1286 qq|pg.partsgroup | .
1287 qq|FROM assembly a | .
1288 qq| JOIN parts p ON (a.parts_id = p.id) | .
1289 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1290 qq| WHERE a.bom = '1' | .
1291 qq| AND a.id = ? | . $sortorder;
1292 @values = ($form->{"id_$i"});
1293 $sth = $dbh->prepare($query);
1294 $sth->execute(@values) || $form->dberror($query);
1296 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1297 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1298 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1299 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1300 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1303 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1304 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1309 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1310 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1311 for @{ $ic_cvar_configs };
1315 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1316 $form->{totalweight_nofmt} = $totalweight;
1317 my $defaults = AM->get_defaults();
1318 $form->{weightunit} = $defaults->{weightunit};
1321 foreach $item (sort keys %taxaccounts) {
1322 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1324 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1325 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1326 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1327 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1328 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1329 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1330 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1331 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1334 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1335 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1336 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1337 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1339 if($form->{taxincluded}) {
1340 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1341 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1343 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1344 $form->{subtotal_nofmt} = $form->{ordtotal};
1347 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1350 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1352 if ($form->{type} =~ /_quotation/) {
1353 $form->set_payment_options($myconfig, $form->{quodate});
1355 $form->set_payment_options($myconfig, $form->{orddate});
1358 $form->{username} = $myconfig->{name};
1362 $main::lxdebug->leave_sub();
1365 sub project_description {
1366 $main::lxdebug->enter_sub();
1368 my ($self, $dbh, $id) = @_;
1370 my $query = qq|SELECT description FROM project WHERE id = ?|;
1371 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1373 $main::lxdebug->leave_sub();
1384 OE.pm - Order entry module
1388 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>.
1394 =item retrieve_simple PARAMS
1396 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1398 my $order = retrieve_simple(id => 2);