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.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.curr = o.curr | .
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" => "e.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) VALUES (?, '', ?)|;
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 ist
423 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
426 # save detail record in orderitems table
427 my $orderitems_id = $form->{"orderitems_id_$i"};
428 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
431 $query = qq|INSERT INTO orderitems (
432 id, trans_id, parts_id, description, longdescription, qty, base_qty,
433 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
434 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
435 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
436 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
437 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
439 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
440 $form->{"description_$i"}, $form->{"longdescription_$i"},
441 $form->{"qty_$i"}, $baseqty,
442 $fxsellprice, $form->{"discount_$i"},
443 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
444 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id),
445 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
446 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
447 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
448 $form->{"lastcost_$i"},
449 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
450 conv_i($form->{"marge_price_factor_$i"}));
451 do_query($form, $dbh, $query, @values);
453 $form->{"sellprice_$i"} = $fxsellprice;
454 $form->{"discount_$i"} *= 100;
456 CVar->save_custom_variables(module => 'IC',
457 sub_module => 'orderitems',
458 trans_id => $orderitems_id,
459 configs => $ic_cvar_configs,
461 name_prefix => 'ic_',
462 name_postfix => "_$i",
467 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
471 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
473 $amount = $form->round_amount($netamount + $tax, 2);
474 $netamount = $form->round_amount($netamount, 2);
476 if ($form->{currency} eq $form->{defaultcurrency}) {
477 $form->{exchangerate} = 1;
479 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
482 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
484 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
486 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
491 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
492 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
493 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = ?, closed = ?,
494 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
495 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
496 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
499 @values = ($form->{ordnumber} || '', $form->{quonumber},
500 $form->{cusordnumber}, conv_date($form->{transdate}),
501 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
502 $amount, $netamount, conv_date($reqdate),
503 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
504 $form->{shipvia}, $form->{notes}, $form->{intnotes},
505 substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f',
506 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
507 $quotation, conv_i($form->{department_id}),
508 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
509 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
510 conv_i($form->{delivery_vendor_id}),
511 conv_i($form->{delivery_customer_id}),
512 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
513 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
514 $form->{transaction_description},
515 $form->{marge_total} * 1, $form->{marge_percent} * 1,
516 conv_i($form->{id}));
517 do_query($form, $dbh, $query, @values);
519 $form->{ordtotal} = $amount;
522 $form->{name} = $form->{ $form->{vc} };
523 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
525 if (!$form->{shipto_id}) {
526 $form->add_shipto($dbh, $form->{id}, "OE");
529 # save printed, emailed, queued
530 $form->save_status($dbh);
532 # Link this record to the records it was created from.
533 $form->{convert_from_oe_ids} =~ s/^\s+//;
534 $form->{convert_from_oe_ids} =~ s/\s+$//;
535 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
536 delete $form->{convert_from_oe_ids};
538 if (scalar @convert_from_oe_ids) {
539 RecordLinks->create_links('dbh' => $dbh,
541 'from_table' => 'oe',
542 'from_ids' => \@convert_from_oe_ids,
544 'to_id' => $form->{id},
547 $self->_close_quotations_rfqs('dbh' => $dbh,
548 'from_id' => \@convert_from_oe_ids,
549 'to_id' => $form->{id});
552 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
553 if ($form->{vc} eq 'customer') {
554 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
556 if ($form->{vc} eq 'vendor') {
557 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
561 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
562 "quonumber" : "ordnumber"};
564 Common::webdav_folder($form);
566 my $rc = $dbh->commit;
568 $self->save_periodic_invoices_config(dbh => $dbh,
569 oe_id => $form->{id},
570 config_yaml => $form->{periodic_invoices_config})
571 if ($form->{type} eq 'sales_order');
573 $main::lxdebug->leave_sub();
578 sub save_periodic_invoices_config {
579 my ($self, %params) = @_;
581 return if !$params{oe_id};
583 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
584 return if 'HASH' ne ref $config;
586 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
587 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
588 $obj->update_attributes(%{ $config });
591 sub load_periodic_invoice_config {
595 delete $form->{periodic_invoices_config};
598 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
601 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
602 print printer_id copies) };
603 $form->{periodic_invoices_config} = YAML::Dump($config);
608 sub _close_quotations_rfqs {
609 $main::lxdebug->enter_sub();
614 Common::check_params(\%params, qw(from_id to_id));
616 my $myconfig = \%main::myconfig;
617 my $form = $main::form;
619 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
621 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
622 my $sth = prepare_query($form, $dbh, $query);
624 do_statement($form, $sth, $query, conv_i($params{to_id}));
626 my ($quotation) = $sth->fetchrow_array();
629 $main::lxdebug->leave_sub();
635 foreach my $from_id (@{ $params{from_id} }) {
636 $from_id = conv_i($from_id);
637 do_statement($form, $sth, $query, $from_id);
638 ($quotation) = $sth->fetchrow_array();
639 push @close_ids, $from_id if ($quotation);
644 if (scalar @close_ids) {
645 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
646 do_query($form, $dbh, $query, @close_ids);
648 $dbh->commit() unless ($params{dbh});
651 $main::lxdebug->leave_sub();
655 $main::lxdebug->enter_sub();
657 my ($self, $myconfig, $form) = @_;
659 # connect to database
660 my $dbh = $form->get_standard_dbh;
664 my $query = qq|SELECT s.spoolfile FROM status s | .
665 qq|WHERE s.trans_id = ?|;
666 my @values = (conv_i($form->{id}));
667 my $sth = $dbh->prepare($query);
668 $sth->execute(@values) || $self->dberror($query);
673 while (($spoolfile) = $sth->fetchrow_array) {
674 push @spoolfiles, $spoolfile;
679 @values = (conv_i($form->{id}));
681 # periodic invoices and their configuration
682 do_query($form, $dbh, qq|DELETE FROM periodic_invoices WHERE config_id IN (SELECT id FROM periodic_invoices_configs WHERE oe_id = ?)|, @values);
683 do_query($form, $dbh, qq|DELETE FROM periodic_invoices_configs WHERE oe_id = ?|, @values);
685 # delete status entries
686 $query = qq|DELETE FROM status | .
687 qq|WHERE trans_id = ?|;
688 do_query($form, $dbh, $query, @values);
691 $query = qq|DELETE FROM oe | .
693 do_query($form, $dbh, $query, @values);
695 # delete individual entries
696 $query = qq|DELETE FROM orderitems | .
697 qq|WHERE trans_id = ?|;
698 do_query($form, $dbh, $query, @values);
700 $query = qq|DELETE FROM shipto | .
701 qq|WHERE trans_id = ? AND module = 'OE'|;
702 do_query($form, $dbh, $query, @values);
704 my $rc = $dbh->commit;
707 my $spool = $::lx_office_conf{paths}->{spool};
708 foreach $spoolfile (@spoolfiles) {
709 unlink "$spool/$spoolfile" if $spoolfile;
713 $main::lxdebug->leave_sub();
719 $main::lxdebug->enter_sub();
721 my ($self, $myconfig, $form) = @_;
723 # connect to database
724 my $dbh = $form->get_standard_dbh;
726 my ($query, $query_add, @values, @ids, $sth);
728 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
731 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
733 push @ids, $form->{"trans_id_$_"}
734 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
735 } (1 .. $form->{"rowcount"});
737 if ($form->{rowcount} && scalar @ids) {
738 $form->{convert_from_oe_ids} = join ' ', @ids;
741 # if called in multi id mode, and still only got one id, switch back to single id
742 if ($form->{"rowcount"} and $#ids == 0) {
743 $form->{"id"} = $ids[0];
747 # and remember for the rest of the function
748 my $is_collective_order = scalar @ids;
751 my $wday = (localtime(time))[6];
752 my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
753 $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
756 # get default accounts
757 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
758 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
759 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
760 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
761 (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}) = split(/:/, $form->{currencies}) unless ($form->{currency});
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 o.curr 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 # remove any trailing whitespace
810 $form->{currency} =~ s/\s*$//;
812 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
814 # set all entries for multiple ids blank that yield different information
815 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
816 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
820 # if not given, fill transdate with current_date
821 $form->{transdate} = $form->current_date($myconfig)
822 unless $form->{transdate};
826 if ($form->{delivery_customer_id}) {
827 $query = qq|SELECT name FROM customer WHERE id = ?|;
828 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
831 if ($form->{delivery_vendor_id}) {
832 $query = qq|SELECT name FROM customer WHERE id = ?|;
833 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
836 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
838 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
839 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
841 $ref = $sth->fetchrow_hashref("NAME_lc");
843 map { $form->{$_} = $ref->{$_} } keys %$ref;
846 # get printed, emailed and queued
847 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
848 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
850 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
851 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
852 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
853 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
856 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
859 my %oid = ('Pg' => 'oid',
860 'Oracle' => 'rowid');
862 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
864 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
866 # retrieve individual items
867 # this query looks up all information about the items
868 # stuff different from the whole will not be overwritten, but saved with a suffix.
870 qq|SELECT o.id AS orderitems_id,
871 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
872 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
873 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
874 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
875 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
876 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
877 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
878 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
879 o.price_factor_id, o.price_factor, o.marge_price_factor,
880 pr.projectnumber, p.formel,
881 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
883 JOIN parts p ON (o.parts_id = p.id)
884 JOIN oe ON (o.trans_id = oe.id)
885 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
886 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
887 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
888 LEFT JOIN project pr ON (o.project_id = pr.id)
889 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
891 ? qq|WHERE o.trans_id = ?|
892 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
893 qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
895 @ids = $form->{id} ? ($form->{id}) : @ids;
896 $sth = prepare_execute_query($form, $dbh, $query, @values);
898 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
899 # Retrieve custom variables.
900 my $cvars = CVar->get_custom_variables(dbh => $dbh,
902 sub_module => 'orderitems',
903 trans_id => $ref->{orderitems_id},
905 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
908 if (!$ref->{"part_inventory_accno_id"}) {
909 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
911 delete($ref->{"part_inventory_accno_id"});
913 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
914 # unless already present there
915 # remove _oe entries afterwards
916 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
917 qw|ordnumber transdate cusordnumber|
919 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
923 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
925 qq|SELECT accno AS inventory_accno, | .
926 qq| new_chart_id AS inventory_new_chart, | .
927 qq| date($transdate) - valid_from AS inventory_valid | .
928 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
929 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
930 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
933 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
935 qq|SELECT accno AS income_accno, | .
936 qq| new_chart_id AS income_new_chart, | .
937 qq| date($transdate) - valid_from AS income_valid | .
938 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
939 ($ref->{income_accno}, $ref->{income_new_chart},
940 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
943 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
945 qq|SELECT accno AS expense_accno, | .
946 qq| new_chart_id AS expense_new_chart, | .
947 qq| date($transdate) - valid_from AS expense_valid | .
948 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
949 ($ref->{expense_accno}, $ref->{expense_new_chart},
950 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
953 # delete orderitems_id in collective orders, so that they get cloned no matter what
954 delete $ref->{orderitems_id} if (@ids);
956 # get tax rates and description
957 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
959 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
960 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
961 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
962 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
963 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
964 qq|ORDER BY c.accno|;
965 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
966 $ref->{taxaccounts} = "";
968 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
969 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
973 $ref->{taxaccounts} .= "$ptr->{accno} ";
974 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
975 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
976 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
977 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
978 $form->{taxaccounts} .= "$ptr->{accno} ";
983 chop $ref->{taxaccounts};
985 push @{ $form->{form_details} }, $ref;
993 $form->lastname_used($dbh, $myconfig, $form->{vc})
994 unless $form->{"$form->{vc}_id"};
998 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1000 Common::webdav_folder($form);
1002 $self->load_periodic_invoice_config($form);
1004 my $rc = $dbh->commit;
1006 $main::lxdebug->leave_sub();
1011 sub retrieve_simple {
1012 $main::lxdebug->enter_sub();
1017 Common::check_params(\%params, qw(id));
1019 my $myconfig = \%main::myconfig;
1020 my $form = $main::form;
1022 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1024 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1025 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1027 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1028 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1030 $main::lxdebug->leave_sub();
1036 $main::lxdebug->enter_sub();
1038 my ($self, $myconfig, $form) = @_;
1040 # connect to database
1041 my $dbh = $form->get_standard_dbh;
1047 my $nodiscount_subtotal = 0;
1048 my $discount_subtotal = 0;
1051 my @partsgroup = ();
1054 my $subtotal_header = 0;
1055 my $subposition = 0;
1062 my %oid = ('Pg' => 'oid',
1063 'Oracle' => 'rowid');
1065 my (@project_ids, %projectnumbers, %projectdescriptions);
1067 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1069 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1070 'departments' => 'ALL_DEPARTMENTS');
1073 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1074 $price_factors{$pfac->{id}} = $pfac;
1075 $pfac->{factor} *= 1;
1076 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1080 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1081 next unless $dept->{id} eq $form->{department_id};
1082 $form->{department} = $dept->{description};
1086 # sort items by partsgroup
1087 for $i (1 .. $form->{rowcount}) {
1089 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1090 $partsgroup = $form->{"partsgroup_$i"};
1092 push @partsgroup, [$i, $partsgroup];
1093 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1097 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1098 join(", ", map("?", @project_ids)) . ")";
1099 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1100 while (my $ref = $sth->fetchrow_hashref()) {
1101 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1102 $projectdescriptions{$ref->{id}} = $ref->{description};
1107 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1108 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1110 $form->{discount} = [];
1112 $form->{TEMPLATE_ARRAYS} = { };
1113 IC->prepare_parts_for_printing();
1115 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1118 qw(runningnumber number description longdescription qty ship unit bin
1119 partnotes serialnumber reqdate sellprice listprice netprice
1120 discount p_discount discount_sub nodiscount_sub
1121 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1122 price_factor price_factor_name partsgroup);
1124 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1126 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1128 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1131 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1134 if ($item->[1] ne $sameitem) {
1135 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1136 $sameitem = $item->[1];
1138 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1141 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1143 if ($form->{"id_$i"} != 0) {
1145 # add number, description and qty to $form->{number}, ....
1147 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1148 $subtotal_header = $i;
1149 $position = int($position);
1152 } elsif ($subtotal_header) {
1154 $position = int($position);
1155 $position = $position.".".$subposition;
1157 $position = int($position);
1161 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1163 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1164 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1165 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1166 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1167 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1168 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1169 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1170 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1171 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1172 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1173 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1174 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1175 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1176 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1177 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1178 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1179 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1180 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1181 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1183 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1184 my ($dec) = ($sellprice =~ /\.(\d+)/);
1185 my $decimalplaces = max 2, length($dec);
1187 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1188 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1189 my $linetotal = $form->round_amount($linetotal_exact, 2);
1190 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
1192 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
1193 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1195 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1196 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1198 $linetotal = ($linetotal != 0) ? $linetotal : '';
1200 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1201 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1202 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1204 $form->{ordtotal} += $linetotal;
1205 $form->{nodiscount_total} += $nodiscount_linetotal;
1206 $form->{discount_total} += $discount;
1208 if ($subtotal_header) {
1209 $discount_subtotal += $linetotal;
1210 $nodiscount_subtotal += $nodiscount_linetotal;
1213 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1214 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1215 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1216 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1217 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1219 $discount_subtotal = 0;
1220 $nodiscount_subtotal = 0;
1221 $subtotal_header = 0;
1224 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
1225 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
1228 if (!$form->{"discount_$i"}) {
1229 $nodiscount += $linetotal;
1232 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1233 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1234 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1235 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1236 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1237 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1239 my ($taxamount, $taxbase);
1242 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1244 if ($form->{taxincluded}) {
1247 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1248 $taxbase = $linetotal / (1 + $taxrate);
1250 $taxamount = $linetotal * $taxrate;
1251 $taxbase = $linetotal;
1254 if ($taxamount != 0) {
1255 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1256 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1257 $taxbase{$accno} += $taxbase;
1261 $tax_rate = $taxrate * 100;
1262 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1264 if ($form->{"assembly_$i"}) {
1267 # get parts and push them onto the stack
1269 if ($form->{groupitems}) {
1270 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
1272 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
1275 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1276 qq|pg.partsgroup | .
1277 qq|FROM assembly a | .
1278 qq| JOIN parts p ON (a.parts_id = p.id) | .
1279 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1280 qq| WHERE a.bom = '1' | .
1281 qq| AND a.id = ? | . $sortorder;
1282 @values = ($form->{"id_$i"});
1283 $sth = $dbh->prepare($query);
1284 $sth->execute(@values) || $form->dberror($query);
1286 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1287 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1288 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1289 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1290 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1293 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1294 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1299 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1300 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1301 for @{ $ic_cvar_configs };
1306 foreach $item (sort keys %taxaccounts) {
1307 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1309 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1310 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1311 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1312 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1313 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1314 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1315 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1316 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1319 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1320 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1321 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1322 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1324 if($form->{taxincluded}) {
1325 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1326 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1328 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1329 $form->{subtotal_nofmt} = $form->{ordtotal};
1332 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1335 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1337 if ($form->{type} =~ /_quotation/) {
1338 $form->set_payment_options($myconfig, $form->{quodate});
1340 $form->set_payment_options($myconfig, $form->{orddate});
1343 $form->{username} = $myconfig->{name};
1347 $main::lxdebug->leave_sub();
1350 sub project_description {
1351 $main::lxdebug->enter_sub();
1353 my ($self, $dbh, $id) = @_;
1355 my $query = qq|SELECT description FROM project WHERE id = ?|;
1356 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1358 $main::lxdebug->leave_sub();
1369 OE.pm - Order entry module
1373 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>.
1379 =item retrieve_simple PARAMS
1381 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1383 my $order = retrieve_simple(id => 2);