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->dbconnect($myconfig);
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 = 'NOT' if ($form->{periodic_invoices_inactive});
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};
231 $main::lxdebug->leave_sub();
234 sub transactions_for_todo_list {
235 $main::lxdebug->enter_sub();
240 my $myconfig = \%main::myconfig;
241 my $form = $main::form;
243 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
245 my $query = qq|SELECT id FROM employee WHERE login = ?|;
246 my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
249 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
250 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
255 LEFT JOIN customer c ON (oe.customer_id = c.id)
256 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
257 LEFT JOIN employee e ON (oe.employee_id = e.id)
258 WHERE (COALESCE(quotation, FALSE) = TRUE)
259 AND (COALESCE(closed, FALSE) = FALSE)
260 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
261 AND NOT (oe.reqdate ISNULL)
262 AND (oe.reqdate < current_date)
265 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
267 $main::lxdebug->leave_sub();
273 $main::lxdebug->enter_sub();
275 my ($self, $myconfig, $form) = @_;
277 # connect to database, turn off autocommit
278 my $dbh = $form->get_standard_dbh;
280 my ($query, @values, $sth, $null);
281 my $exchangerate = 0;
283 my $all_units = AM->retrieve_units($myconfig, $form);
284 $form->{all_units} = $all_units;
286 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
289 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
290 unless ($form->{employee_id}) {
291 $form->get_employee($dbh);
294 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
297 $query = qq|DELETE FROM custom_variables
298 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
299 AND (sub_module = 'orderitems')
300 AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
301 do_query($form, $dbh, $query, $form->{id});
303 $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
304 do_query($form, $dbh, $query, $form->{id});
306 $query = qq|DELETE FROM shipto | .
307 qq|WHERE trans_id = ? AND module = 'OE'|;
308 do_query($form, $dbh, $query, $form->{id});
312 $query = qq|SELECT nextval('id')|;
313 ($form->{id}) = selectrow_query($form, $dbh, $query);
315 $query = qq|INSERT INTO oe (id, ordnumber, employee_id) VALUES (?, '', ?)|;
316 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
334 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
335 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
338 for my $i (1 .. $form->{rowcount}) {
340 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
342 if ($form->{"id_$i"}) {
345 $query = qq|SELECT unit FROM parts WHERE id = ?|;
346 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
349 if (defined($all_units->{$item_unit}->{factor}) &&
350 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
351 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
353 my $baseqty = $form->{"qty_$i"} * $basefactor;
355 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
356 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
358 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
360 # set values to 0 if nothing entered
361 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
363 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
364 $fxsellprice = $form->{"sellprice_$i"};
366 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
368 my $decimalplaces = ($dec > 2) ? $dec : 2;
370 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
371 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
373 $form->{"inventory_accno_$i"} *= 1;
374 $form->{"expense_accno_$i"} *= 1;
376 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
377 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
379 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
383 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
385 if ($form->{taxincluded}) {
386 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
387 $taxbase = $linetotal - $taxamount;
389 # we are not keeping a natural price, do not round
390 $form->{"sellprice_$i"} =
391 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
393 $taxamount = $linetotal * $taxrate;
394 $taxbase = $linetotal;
397 if ($form->round_amount($taxrate, 7) == 0) {
398 if ($form->{taxincluded}) {
399 foreach my $item (@taxaccounts) {
400 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
401 $taxaccounts{$item} += $taxamount;
402 $taxdiff += $taxamount;
403 $taxbase{$item} += $taxbase;
405 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
407 foreach my $item (@taxaccounts) {
408 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
409 $taxbase{$item} += $taxbase;
413 foreach my $item (@taxaccounts) {
414 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
415 $taxbase{$item} += $taxbase;
419 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
421 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
423 # get pricegroup_id and save ist
424 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
427 # save detail record in orderitems table
428 my $orderitems_id = $form->{"orderitems_id_$i"};
429 ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
432 $query = qq|INSERT INTO orderitems (
433 id, trans_id, parts_id, description, longdescription, qty, base_qty,
434 sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
435 pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
436 marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
437 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
438 (SELECT factor FROM price_factors WHERE id = ?), ?)|;
440 conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
441 $form->{"description_$i"}, $form->{"longdescription_$i"},
442 $form->{"qty_$i"}, $baseqty,
443 $fxsellprice, $form->{"discount_$i"},
444 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
445 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id),
446 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
447 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
448 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
449 $form->{"lastcost_$i"},
450 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
451 conv_i($form->{"marge_price_factor_$i"}));
452 do_query($form, $dbh, $query, @values);
454 $form->{"sellprice_$i"} = $fxsellprice;
455 $form->{"discount_$i"} *= 100;
457 CVar->save_custom_variables(module => 'IC',
458 sub_module => 'orderitems',
459 trans_id => $orderitems_id,
460 configs => $ic_cvar_configs,
462 name_prefix => 'ic_',
463 name_postfix => "_$i",
468 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
472 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
474 $amount = $form->round_amount($netamount + $tax, 2);
475 $netamount = $form->round_amount($netamount, 2);
477 if ($form->{currency} eq $form->{defaultcurrency}) {
478 $form->{exchangerate} = 1;
480 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
483 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
485 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
487 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
492 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
493 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
494 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = ?, closed = ?,
495 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
496 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
497 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
500 @values = ($form->{ordnumber} || '', $form->{quonumber},
501 $form->{cusordnumber}, conv_date($form->{transdate}),
502 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
503 $amount, $netamount, conv_date($reqdate),
504 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
505 $form->{shipvia}, $form->{notes}, $form->{intnotes},
506 substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f',
507 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
508 $quotation, conv_i($form->{department_id}),
509 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
510 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
511 conv_i($form->{delivery_vendor_id}),
512 conv_i($form->{delivery_customer_id}),
513 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
514 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
515 $form->{transaction_description},
516 $form->{marge_total} * 1, $form->{marge_percent} * 1,
517 conv_i($form->{id}));
518 do_query($form, $dbh, $query, @values);
520 $form->{ordtotal} = $amount;
523 $form->{name} = $form->{ $form->{vc} };
524 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
526 if (!$form->{shipto_id}) {
527 $form->add_shipto($dbh, $form->{id}, "OE");
530 # save printed, emailed, queued
531 $form->save_status($dbh);
533 # Link this record to the records it was created from.
534 $form->{convert_from_oe_ids} =~ s/^\s+//;
535 $form->{convert_from_oe_ids} =~ s/\s+$//;
536 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
537 delete $form->{convert_from_oe_ids};
539 if (scalar @convert_from_oe_ids) {
540 RecordLinks->create_links('dbh' => $dbh,
542 'from_table' => 'oe',
543 'from_ids' => \@convert_from_oe_ids,
545 'to_id' => $form->{id},
548 $self->_close_quotations_rfqs('dbh' => $dbh,
549 'from_id' => \@convert_from_oe_ids,
550 'to_id' => $form->{id});
553 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
554 if ($form->{vc} eq 'customer') {
555 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
557 if ($form->{vc} eq 'vendor') {
558 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
562 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
563 "quonumber" : "ordnumber"};
565 Common::webdav_folder($form);
567 my $rc = $dbh->commit;
569 $self->save_periodic_invoices_config(dbh => $dbh,
570 oe_id => $form->{id},
571 config_yaml => $form->{periodic_invoices_config})
572 if ($form->{type} eq 'sales_order');
574 $main::lxdebug->leave_sub();
579 sub save_periodic_invoices_config {
580 my ($self, %params) = @_;
582 return if !$params{oe_id};
584 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
585 return if 'HASH' ne ref $config;
587 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
588 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
589 $obj->update_attributes(%{ $config });
592 sub load_periodic_invoice_config {
596 delete $form->{periodic_invoices_config};
599 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
602 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity start_date_as_date end_date_as_date extend_automatically_by ar_chart_id
603 print printer_id copies) };
604 $form->{periodic_invoices_config} = YAML::Dump($config);
609 sub _close_quotations_rfqs {
610 $main::lxdebug->enter_sub();
615 Common::check_params(\%params, qw(from_id to_id));
617 my $myconfig = \%main::myconfig;
618 my $form = $main::form;
620 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
622 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
623 my $sth = prepare_query($form, $dbh, $query);
625 do_statement($form, $sth, $query, conv_i($params{to_id}));
627 my ($quotation) = $sth->fetchrow_array();
630 $main::lxdebug->leave_sub();
636 foreach my $from_id (@{ $params{from_id} }) {
637 $from_id = conv_i($from_id);
638 do_statement($form, $sth, $query, $from_id);
639 ($quotation) = $sth->fetchrow_array();
640 push @close_ids, $from_id if ($quotation);
645 if (scalar @close_ids) {
646 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
647 do_query($form, $dbh, $query, @close_ids);
649 $dbh->commit() unless ($params{dbh});
652 $main::lxdebug->leave_sub();
656 $main::lxdebug->enter_sub();
658 my ($self, $myconfig, $form) = @_;
660 # connect to database
661 my $dbh = $form->dbconnect_noauto($myconfig);
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;
708 my $spool = $::lx_office_conf{paths}->{spool};
709 foreach $spoolfile (@spoolfiles) {
710 unlink "$spool/$spoolfile" if $spoolfile;
714 $main::lxdebug->leave_sub();
720 $main::lxdebug->enter_sub();
722 my ($self, $myconfig, $form) = @_;
724 # connect to database
725 my $dbh = $form->get_standard_dbh;
727 my ($query, $query_add, @values, @ids, $sth);
729 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
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,
766 my $ref = selectfirst_hashref_query($form, $dbh, $query);
767 map { $form->{$_} = $ref->{$_} } keys %$ref;
769 ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency});
771 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
772 # we come from invoices, feel free.
773 $form->{reqdate} = $form->{deliverydate}
774 if ( $form->{deliverydate}
775 and $form->{callback} =~ /action=ar_transactions/);
777 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
779 if ($form->{id} or @ids) {
781 # retrieve order for single id
782 # NOTE: this query is intended to fetch all information only ONCE.
783 # so if any of these infos is important (or even different) for any item,
784 # it will be killed out and then has to be fetched from the item scope query further down
786 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
787 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
788 o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id,
789 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
790 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
791 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
792 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
793 o.globalproject_id, o.delivered, o.transaction_description
795 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
796 LEFT JOIN employee e ON (o.employee_id = e.id)
797 LEFT JOIN department d ON (o.department_id = d.id) | .
800 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
802 @values = $form->{id} ? ($form->{id}) : @ids;
803 $sth = prepare_execute_query($form, $dbh, $query, @values);
805 $ref = $sth->fetchrow_hashref("NAME_lc");
806 map { $form->{$_} = $ref->{$_} } keys %$ref;
808 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
809 "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;
816 # if not given, fill transdate with current_date
817 $form->{transdate} = $form->current_date($myconfig)
818 unless $form->{transdate};
822 if ($form->{delivery_customer_id}) {
823 $query = qq|SELECT name FROM customer WHERE id = ?|;
824 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
827 if ($form->{delivery_vendor_id}) {
828 $query = qq|SELECT name FROM customer WHERE id = ?|;
829 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
832 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
834 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
835 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
837 $ref = $sth->fetchrow_hashref("NAME_lc");
839 map { $form->{$_} = $ref->{$_} } keys %$ref;
842 # get printed, emailed and queued
843 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
844 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
846 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
847 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
848 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
849 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
852 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
855 my %oid = ('Pg' => 'oid',
856 'Oracle' => 'rowid');
858 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
860 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
862 # retrieve individual items
863 # this query looks up all information about the items
864 # stuff different from the whole will not be overwritten, but saved with a suffix.
866 qq|SELECT o.id AS orderitems_id,
867 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
868 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
869 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
870 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
871 p.partnumber, p.assembly, o.description, o.qty,
872 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,
873 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
874 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
875 o.price_factor_id, o.price_factor, o.marge_price_factor,
876 pr.projectnumber, p.formel,
877 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
879 JOIN parts p ON (o.parts_id = p.id)
880 JOIN oe ON (o.trans_id = oe.id)
881 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
882 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
883 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
884 LEFT JOIN project pr ON (o.project_id = pr.id)
885 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
887 ? qq|WHERE o.trans_id = ?|
888 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
889 qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
891 @ids = $form->{id} ? ($form->{id}) : @ids;
892 $sth = prepare_execute_query($form, $dbh, $query, @values);
894 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
895 # Retrieve custom variables.
896 my $cvars = CVar->get_custom_variables(dbh => $dbh,
898 sub_module => 'orderitems',
899 trans_id => $ref->{orderitems_id},
901 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
904 if (!$ref->{"part_inventory_accno_id"}) {
905 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
907 delete($ref->{"part_inventory_accno_id"});
909 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
910 # unless already present there
911 # remove _oe entries afterwards
912 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
913 qw|ordnumber transdate cusordnumber|
915 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
919 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
921 qq|SELECT accno AS inventory_accno, | .
922 qq| new_chart_id AS inventory_new_chart, | .
923 qq| date($transdate) - valid_from AS inventory_valid | .
924 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
925 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
926 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
929 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
931 qq|SELECT accno AS income_accno, | .
932 qq| new_chart_id AS income_new_chart, | .
933 qq| date($transdate) - valid_from AS income_valid | .
934 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
935 ($ref->{income_accno}, $ref->{income_new_chart},
936 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
939 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
941 qq|SELECT accno AS expense_accno, | .
942 qq| new_chart_id AS expense_new_chart, | .
943 qq| date($transdate) - valid_from AS expense_valid | .
944 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
945 ($ref->{expense_accno}, $ref->{expense_new_chart},
946 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
949 # delete orderitems_id in collective orders, so that they get cloned no matter what
950 delete $ref->{orderitems_id} if (@ids);
952 # get tax rates and description
953 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
955 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
956 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
957 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
958 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
959 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
960 qq|ORDER BY c.accno|;
961 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
962 $ref->{taxaccounts} = "";
964 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
965 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
969 $ref->{taxaccounts} .= "$ptr->{accno} ";
970 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
971 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
972 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
973 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
974 $form->{taxaccounts} .= "$ptr->{accno} ";
979 chop $ref->{taxaccounts};
981 push @{ $form->{form_details} }, $ref;
989 $form->lastname_used($dbh, $myconfig, $form->{vc})
990 unless $form->{"$form->{vc}_id"};
994 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
996 Common::webdav_folder($form);
998 $self->load_periodic_invoice_config($form);
1000 my $rc = $dbh->commit;
1002 $main::lxdebug->leave_sub();
1007 sub retrieve_simple {
1008 $main::lxdebug->enter_sub();
1013 Common::check_params(\%params, qw(id));
1015 my $myconfig = \%main::myconfig;
1016 my $form = $main::form;
1018 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1020 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1021 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1023 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1024 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1026 $main::lxdebug->leave_sub();
1032 $main::lxdebug->enter_sub();
1034 my ($self, $myconfig, $form) = @_;
1036 # connect to database
1037 my $dbh = $form->dbconnect($myconfig);
1043 my $nodiscount_subtotal = 0;
1044 my $discount_subtotal = 0;
1047 my @partsgroup = ();
1050 my $subtotal_header = 0;
1051 my $subposition = 0;
1058 my %oid = ('Pg' => 'oid',
1059 'Oracle' => 'rowid');
1061 my (@project_ids, %projectnumbers, %projectdescriptions);
1063 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1065 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1066 'departments' => 'ALL_DEPARTMENTS');
1069 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1070 $price_factors{$pfac->{id}} = $pfac;
1071 $pfac->{factor} *= 1;
1072 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1076 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1077 next unless $dept->{id} eq $form->{department_id};
1078 $form->{department} = $dept->{description};
1082 # sort items by partsgroup
1083 for $i (1 .. $form->{rowcount}) {
1085 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1086 $partsgroup = $form->{"partsgroup_$i"};
1088 push @partsgroup, [$i, $partsgroup];
1089 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1093 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1094 join(", ", map("?", @project_ids)) . ")";
1095 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1096 while (my $ref = $sth->fetchrow_hashref()) {
1097 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1098 $projectdescriptions{$ref->{id}} = $ref->{description};
1103 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1104 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1106 $form->{discount} = [];
1108 $form->{TEMPLATE_ARRAYS} = { };
1109 IC->prepare_parts_for_printing();
1111 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1114 qw(runningnumber number description longdescription qty ship unit bin
1115 partnotes serialnumber reqdate sellprice listprice netprice
1116 discount p_discount discount_sub nodiscount_sub
1117 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1118 price_factor price_factor_name partsgroup);
1120 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1122 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1124 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1127 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1130 if ($item->[1] ne $sameitem) {
1131 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1132 $sameitem = $item->[1];
1134 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1137 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1139 if ($form->{"id_$i"} != 0) {
1141 # add number, description and qty to $form->{number}, ....
1143 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1144 $subtotal_header = $i;
1145 $position = int($position);
1148 } elsif ($subtotal_header) {
1150 $position = int($position);
1151 $position = $position.".".$subposition;
1153 $position = int($position);
1157 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1159 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1160 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1161 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1162 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1163 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1164 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1165 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1166 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1167 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1168 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1169 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1170 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1171 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1172 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1173 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1174 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1175 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1176 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1177 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1179 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1180 my ($dec) = ($sellprice =~ /\.(\d+)/);
1181 my $decimalplaces = max 2, length($dec);
1183 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1184 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1185 my $linetotal = $form->round_amount($linetotal_exact, 2);
1186 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
1188 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
1189 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1191 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1192 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1194 $linetotal = ($linetotal != 0) ? $linetotal : '';
1196 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1197 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1198 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1200 $form->{ordtotal} += $linetotal;
1201 $form->{nodiscount_total} += $nodiscount_linetotal;
1202 $form->{discount_total} += $discount;
1204 if ($subtotal_header) {
1205 $discount_subtotal += $linetotal;
1206 $nodiscount_subtotal += $nodiscount_linetotal;
1209 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1210 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1211 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1212 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1213 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1215 $discount_subtotal = 0;
1216 $nodiscount_subtotal = 0;
1217 $subtotal_header = 0;
1220 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
1221 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
1224 if (!$form->{"discount_$i"}) {
1225 $nodiscount += $linetotal;
1228 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1229 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1230 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1231 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1232 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1233 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1235 my ($taxamount, $taxbase);
1238 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1240 if ($form->{taxincluded}) {
1243 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1244 $taxbase = $linetotal / (1 + $taxrate);
1246 $taxamount = $linetotal * $taxrate;
1247 $taxbase = $linetotal;
1250 if ($taxamount != 0) {
1251 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1252 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1253 $taxbase{$accno} += $taxbase;
1257 $tax_rate = $taxrate * 100;
1258 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1260 if ($form->{"assembly_$i"}) {
1263 # get parts and push them onto the stack
1265 if ($form->{groupitems}) {
1266 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
1268 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
1271 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1272 qq|pg.partsgroup | .
1273 qq|FROM assembly a | .
1274 qq| JOIN parts p ON (a.parts_id = p.id) | .
1275 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1276 qq| WHERE a.bom = '1' | .
1277 qq| AND a.id = ? | . $sortorder;
1278 @values = ($form->{"id_$i"});
1279 $sth = $dbh->prepare($query);
1280 $sth->execute(@values) || $form->dberror($query);
1282 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1283 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1284 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1285 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1286 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1289 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1290 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1295 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
1300 foreach $item (sort keys %taxaccounts) {
1301 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1303 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1304 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1305 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1306 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1307 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1308 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1309 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1310 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1313 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1314 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1315 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1316 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1318 if($form->{taxincluded}) {
1319 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1320 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1322 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1323 $form->{subtotal_nofmt} = $form->{ordtotal};
1326 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1329 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1331 if ($form->{type} =~ /_quotation/) {
1332 $form->set_payment_options($myconfig, $form->{quodate});
1334 $form->set_payment_options($myconfig, $form->{orddate});
1337 $form->{username} = $myconfig->{name};
1341 $main::lxdebug->leave_sub();
1344 sub project_description {
1345 $main::lxdebug->enter_sub();
1347 my ($self, $dbh, $id) = @_;
1349 my $query = qq|SELECT description FROM project WHERE id = ?|;
1350 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1352 $main::lxdebug->leave_sub();
1363 OE.pm - Order entry module
1367 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>.
1373 =item retrieve_simple PARAMS
1375 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1377 my $order = retrieve_simple(id => 2);