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 $self->save_periodic_invoices_config(dbh => $dbh,
563 oe_id => $form->{id},
564 config_yaml => $form->{periodic_invoices_config})
565 if ($form->{type} eq 'sales_order');
567 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
568 "quonumber" : "ordnumber"};
570 Common::webdav_folder($form) if ($main::webdav);
572 my $rc = $dbh->commit;
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, $spool) = @_;
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 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");
805 map { $form->{$_} = $ref->{$_} } keys %$ref;
807 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
808 "quonumber" : "ordnumber"};
810 # set all entries for multiple ids blank that yield different information
811 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
812 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
815 # if not given, fill transdate with current_date
816 $form->{transdate} = $form->current_date($myconfig)
817 unless $form->{transdate};
821 if ($form->{delivery_customer_id}) {
822 $query = qq|SELECT name FROM customer WHERE id = ?|;
823 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
826 if ($form->{delivery_vendor_id}) {
827 $query = qq|SELECT name FROM customer WHERE id = ?|;
828 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
831 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
833 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
834 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
836 $ref = $sth->fetchrow_hashref("NAME_lc");
838 map { $form->{$_} = $ref->{$_} } keys %$ref;
841 # get printed, emailed and queued
842 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
843 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
845 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
846 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
847 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
848 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
851 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
854 my %oid = ('Pg' => 'oid',
855 'Oracle' => 'rowid');
857 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
859 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
861 # retrieve individual items
862 # this query looks up all information about the items
863 # stuff different from the whole will not be overwritten, but saved with a suffix.
865 qq|SELECT o.id AS orderitems_id,
866 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
867 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
868 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
869 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
870 p.partnumber, p.assembly, o.description, o.qty,
871 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,
872 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
873 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
874 o.price_factor_id, o.price_factor, o.marge_price_factor,
875 pr.projectnumber, p.formel,
876 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
878 JOIN parts p ON (o.parts_id = p.id)
879 JOIN oe ON (o.trans_id = oe.id)
880 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
881 LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
882 LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
883 LEFT JOIN project pr ON (o.project_id = pr.id)
884 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
886 ? qq|WHERE o.trans_id = ?|
887 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
888 qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
890 @ids = $form->{id} ? ($form->{id}) : @ids;
891 $sth = prepare_execute_query($form, $dbh, $query, @values);
893 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
894 # Retrieve custom variables.
895 my $cvars = CVar->get_custom_variables(dbh => $dbh,
897 sub_module => 'orderitems',
898 trans_id => $ref->{orderitems_id},
900 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
903 if (!$ref->{"part_inventory_accno_id"}) {
904 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
906 delete($ref->{"part_inventory_accno_id"});
908 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
909 # unless already present there
910 # remove _oe entries afterwards
911 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
912 qw|ordnumber transdate cusordnumber|
914 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
918 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
920 qq|SELECT accno AS inventory_accno, | .
921 qq| new_chart_id AS inventory_new_chart, | .
922 qq| date($transdate) - valid_from AS inventory_valid | .
923 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
924 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
925 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
928 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
930 qq|SELECT accno AS income_accno, | .
931 qq| new_chart_id AS income_new_chart, | .
932 qq| date($transdate) - valid_from AS income_valid | .
933 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
934 ($ref->{income_accno}, $ref->{income_new_chart},
935 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
938 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
940 qq|SELECT accno AS expense_accno, | .
941 qq| new_chart_id AS expense_new_chart, | .
942 qq| date($transdate) - valid_from AS expense_valid | .
943 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
944 ($ref->{expense_accno}, $ref->{expense_new_chart},
945 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
948 # delete orderitems_id in collective orders, so that they get cloned no matter what
949 delete $ref->{orderitems_id} if (@ids);
951 # get tax rates and description
952 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
954 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
955 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
956 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
957 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
958 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
959 qq|ORDER BY c.accno|;
960 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
961 $ref->{taxaccounts} = "";
963 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
964 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
968 $ref->{taxaccounts} .= "$ptr->{accno} ";
969 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
970 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
971 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
972 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
973 $form->{taxaccounts} .= "$ptr->{accno} ";
978 chop $ref->{taxaccounts};
980 push @{ $form->{form_details} }, $ref;
988 $form->lastname_used($dbh, $myconfig, $form->{vc})
989 unless $form->{"$form->{vc}_id"};
993 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
995 Common::webdav_folder($form) if ($main::webdav);
997 $self->load_periodic_invoice_config($form);
999 my $rc = $dbh->commit;
1001 $main::lxdebug->leave_sub();
1006 sub retrieve_simple {
1007 $main::lxdebug->enter_sub();
1012 Common::check_params(\%params, qw(id));
1014 my $myconfig = \%main::myconfig;
1015 my $form = $main::form;
1017 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1019 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1020 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
1022 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1023 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1025 $main::lxdebug->leave_sub();
1031 $main::lxdebug->enter_sub();
1033 my ($self, $myconfig, $form) = @_;
1035 # connect to database
1036 my $dbh = $form->dbconnect($myconfig);
1042 my $nodiscount_subtotal = 0;
1043 my $discount_subtotal = 0;
1046 my @partsgroup = ();
1049 my $subtotal_header = 0;
1050 my $subposition = 0;
1057 my %oid = ('Pg' => 'oid',
1058 'Oracle' => 'rowid');
1060 my (@project_ids, %projectnumbers, %projectdescriptions);
1062 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1064 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1065 'departments' => 'ALL_DEPARTMENTS');
1068 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1069 $price_factors{$pfac->{id}} = $pfac;
1070 $pfac->{factor} *= 1;
1071 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1075 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1076 next unless $dept->{id} eq $form->{department_id};
1077 $form->{department} = $dept->{description};
1081 # sort items by partsgroup
1082 for $i (1 .. $form->{rowcount}) {
1084 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1085 $partsgroup = $form->{"partsgroup_$i"};
1087 push @partsgroup, [$i, $partsgroup];
1088 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1092 $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" .
1093 join(", ", map("?", @project_ids)) . ")";
1094 $sth = prepare_execute_query($form, $dbh, $query, @project_ids);
1095 while (my $ref = $sth->fetchrow_hashref()) {
1096 $projectnumbers{$ref->{id}} = $ref->{projectnumber};
1097 $projectdescriptions{$ref->{id}} = $ref->{description};
1102 $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}};
1103 $form->{"globalprojectdescription"} = $projectdescriptions{$form->{"globalproject_id"}};
1105 $form->{discount} = [];
1107 $form->{TEMPLATE_ARRAYS} = { };
1108 IC->prepare_parts_for_printing();
1110 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1113 qw(runningnumber number description longdescription qty ship unit bin
1114 partnotes serialnumber reqdate sellprice listprice netprice
1115 discount p_discount discount_sub nodiscount_sub
1116 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1117 price_factor price_factor_name partsgroup);
1119 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1121 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1123 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1126 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1129 if ($item->[1] ne $sameitem) {
1130 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1131 $sameitem = $item->[1];
1133 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1136 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1138 if ($form->{"id_$i"} != 0) {
1140 # add number, description and qty to $form->{number}, ....
1142 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1143 $subtotal_header = $i;
1144 $position = int($position);
1147 } elsif ($subtotal_header) {
1149 $position = int($position);
1150 $position = $position.".".$subposition;
1152 $position = int($position);
1156 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1158 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1159 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1160 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1161 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1162 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1163 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1164 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1165 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1166 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1167 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1168 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1169 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1170 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1171 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1172 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1173 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1175 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1176 my ($dec) = ($sellprice =~ /\.(\d+)/);
1177 my $decimalplaces = max 2, length($dec);
1179 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1180 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1181 my $linetotal = $form->round_amount($linetotal_exact, 2);
1182 my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
1184 my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
1185 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
1187 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1189 $linetotal = ($linetotal != 0) ? $linetotal : '';
1191 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1192 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1194 $form->{ordtotal} += $linetotal;
1195 $form->{nodiscount_total} += $nodiscount_linetotal;
1196 $form->{discount_total} += $discount;
1198 if ($subtotal_header) {
1199 $discount_subtotal += $linetotal;
1200 $nodiscount_subtotal += $nodiscount_linetotal;
1203 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1204 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1205 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1207 $discount_subtotal = 0;
1208 $nodiscount_subtotal = 0;
1209 $subtotal_header = 0;
1212 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
1213 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
1216 if (!$form->{"discount_$i"}) {
1217 $nodiscount += $linetotal;
1220 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1221 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1223 push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
1224 push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
1226 my ($taxamount, $taxbase);
1229 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1231 if ($form->{taxincluded}) {
1234 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1235 $taxbase = $linetotal / (1 + $taxrate);
1237 $taxamount = $linetotal * $taxrate;
1238 $taxbase = $linetotal;
1241 if ($taxamount != 0) {
1242 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1243 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1244 $taxbase{$accno} += $taxbase;
1248 $tax_rate = $taxrate * 100;
1249 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1251 if ($form->{"assembly_$i"}) {
1254 # get parts and push them onto the stack
1256 if ($form->{groupitems}) {
1257 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
1259 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
1262 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1263 qq|pg.partsgroup | .
1264 qq|FROM assembly a | .
1265 qq| JOIN parts p ON (a.parts_id = p.id) | .
1266 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1267 qq| WHERE a.bom = '1' | .
1268 qq| AND a.id = ? | . $sortorder;
1269 @values = ($form->{"id_$i"});
1270 $sth = $dbh->prepare($query);
1271 $sth->execute(@values) || $form->dberror($query);
1273 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1274 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1275 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1276 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1277 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1280 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1281 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1286 map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
1291 foreach $item (sort keys %taxaccounts) {
1292 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1294 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1295 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1296 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1297 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1298 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1301 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1302 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1303 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1304 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1306 if($form->{taxincluded}) {
1307 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1309 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1312 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1315 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1317 if ($form->{type} =~ /_quotation/) {
1318 $form->set_payment_options($myconfig, $form->{quodate});
1320 $form->set_payment_options($myconfig, $form->{orddate});
1323 $form->{username} = $myconfig->{name};
1327 $main::lxdebug->leave_sub();
1330 sub project_description {
1331 $main::lxdebug->enter_sub();
1333 my ($self, $dbh, $id) = @_;
1335 my $query = qq|SELECT description FROM project WHERE id = ?|;
1336 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1338 $main::lxdebug->leave_sub();
1349 OE.pm - Order entry module
1353 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>.
1359 =item retrieve_simple PARAMS
1361 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1363 my $order = retrieve_simple(id => 2);