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., 51 Franklin Street, Fifth Floor, Boston,
30 #======================================================================
34 #======================================================================
38 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
46 use SL::DB::ProjectType;
47 use SL::DB::RequirementSpecOrder;
51 use SL::HTML::Restrict;
54 use SL::Util qw(trim);
62 $main::lxdebug->enter_sub();
64 my ($self, $myconfig, $form) = @_;
67 my $dbh = $form->get_standard_dbh;
70 my $ordnumber = 'ordnumber';
76 my ($periodic_invoices_columns, $periodic_invoices_joins);
78 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
80 if ($form->{type} =~ /_quotation$/) {
82 $ordnumber = 'quonumber';
84 } elsif ($form->{type} eq 'sales_order') {
85 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
86 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
89 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
93 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
95 SELECT from_id, ar.amount, ar.netamount FROM (
98 WHERE from_table = 'oe' AND to_table = 'ar'
100 SELECT rl1.from_id, rl2.to_id
101 FROM record_links rl1
102 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
103 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
105 SELECT rl1.from_id, rl3.to_id
106 FROM record_links rl1
107 JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
108 JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
109 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'
111 LEFT JOIN ar ON ar.id = rl.to_id
113 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
114 $billed_amount{ $ref->{from_id}} += $ref->{amount};
115 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
119 my ($phone_notes_columns, $phone_notes_join);
120 $form->{phone_notes} = trim($form->{phone_notes});
121 if ($form->{phone_notes}) {
122 $phone_notes_columns = qq| , phone_notes.subject AS phone_notes_subject, phone_notes.body AS phone_notes_body |;
123 $phone_notes_join = qq| JOIN notes phone_notes ON (o.id = phone_notes.trans_id AND phone_notes.trans_module LIKE 'oe') |;
127 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
128 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
129 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
130 qq| o.transaction_description, | .
131 qq| o.marge_total, o.marge_percent, | .
132 qq| o.exchangerate, | .
133 qq| o.itime::DATE AS insertdate, | .
135 qq| department.description as department, | .
136 qq| ex.$rate AS daily_exchangerate, | .
137 qq| pt.description AS payment_terms, | .
138 qq| pr.projectnumber AS globalprojectnumber, | .
139 qq| e.name AS employee, s.name AS salesman, | .
140 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
141 qq| tz.description AS taxzone | .
142 $periodic_invoices_columns .
143 $phone_notes_columns .
144 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
146 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
147 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
148 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
149 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
150 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
151 qq| AND ex.transdate = o.transdate) | .
152 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
153 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
154 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
155 qq|LEFT JOIN department ON (o.department_id = department.id) | .
156 qq|$periodic_invoices_joins | .
158 qq|WHERE (o.quotation = ?) |;
159 push(@values, $quotation);
161 if ($form->{department_id}) {
162 $query .= qq| AND o.department_id = ?|;
163 push(@values, $form->{department_id});
166 if ($form->{"project_id"}) {
168 qq|AND ((globalproject_id = ?) OR EXISTS | .
169 qq| (SELECT * FROM orderitems oi | .
170 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
171 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
174 if ($form->{"projectnumber"}) {
176 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
177 SELECT * FROM orderitems oi
178 LEFT JOIN project proi ON proi.id = oi.project_id
179 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
182 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
185 if ($form->{"business_id"}) {
186 $query .= " AND ct.business_id = ?";
187 push(@values, $form->{"business_id"});
190 if ($form->{"${vc}_id"}) {
191 $query .= " AND o.${vc}_id = ?";
192 push(@values, $form->{"${vc}_id"});
194 } elsif ($form->{$vc}) {
195 $query .= " AND ct.name ILIKE ?";
196 push(@values, like($form->{$vc}));
199 if ($form->{"cp_name"}) {
200 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
201 push(@values, (like($form->{"cp_name"}))x2);
204 if ( !( ($vc eq 'customer' && ($main::auth->assert('sales_all_edit', 1) || $main::auth->assert('sales_order_view', 1)))
205 || ($vc eq 'vendor' && ($main::auth->assert('purchase_all_edit', 1) || $main::auth->assert('purchase_order_view', 1))) ) ) {
206 $query .= " AND o.employee_id = (select id from employee where login= ?)";
207 push @values, $::myconfig{login};
209 if ($form->{employee_id}) {
210 $query .= " AND o.employee_id = ?";
211 push @values, conv_i($form->{employee_id});
214 if ($form->{salesman_id}) {
215 $query .= " AND o.salesman_id = ?";
216 push @values, conv_i($form->{salesman_id});
219 if (!$form->{open} && !$form->{closed}) {
220 $query .= " AND o.id = 0";
221 } elsif (!($form->{open} && $form->{closed})) {
222 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
225 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
226 ($form->{"notdelivered"} ne $form->{"delivered"})) {
227 $query .= $form->{"delivered"} ?
228 " AND o.delivered " : " AND NOT o.delivered";
231 if ($form->{$ordnumber}) {
232 $query .= qq| AND o.$ordnumber ILIKE ?|;
233 push(@values, like($form->{$ordnumber}));
236 if ($form->{cusordnumber}) {
237 $query .= qq| AND o.cusordnumber ILIKE ?|;
238 push(@values, like($form->{cusordnumber}));
241 if($form->{transdatefrom}) {
242 $query .= qq| AND o.transdate >= ?|;
243 push(@values, conv_date($form->{transdatefrom}));
246 if($form->{transdateto}) {
247 $query .= qq| AND o.transdate <= ?|;
248 push(@values, conv_date($form->{transdateto}));
251 if($form->{reqdatefrom}) {
252 $query .= qq| AND o.reqdate >= ?|;
253 push(@values, conv_date($form->{reqdatefrom}));
256 if($form->{reqdateto}) {
257 $query .= qq| AND o.reqdate <= ?|;
258 push(@values, conv_date($form->{reqdateto}));
261 if($form->{insertdatefrom}) {
262 $query .= qq| AND o.itime::DATE >= ?|;
263 push(@values, conv_date($form->{insertdatefrom}));
266 if($form->{insertdateto}) {
267 $query .= qq| AND o.itime::DATE <= ?|;
268 push(@values, conv_date($form->{insertdateto}));
271 if ($form->{shippingpoint}) {
272 $query .= qq| AND o.shippingpoint ILIKE ?|;
273 push(@values, like($form->{shippingpoint}));
276 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
277 $query .= qq| AND tz.id = ?|;
278 push(@values, $form->{taxzone_id});
281 if ($form->{transaction_description}) {
282 $query .= qq| AND o.transaction_description ILIKE ?|;
283 push(@values, like($form->{transaction_description}));
286 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
287 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
288 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
291 if ($form->{reqdate_unset_or_old}) {
292 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
295 if (($form->{order_probability_value} || '') ne '') {
296 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
297 $query .= qq| AND (o.order_probability ${op} ?)|;
298 push @values, trim($form->{order_probability_value});
301 if ($form->{expected_billing_date_from}) {
302 $query .= qq| AND (o.expected_billing_date >= ?)|;
303 push @values, conv_date($form->{expected_billing_date_from});
306 if ($form->{expected_billing_date_to}) {
307 $query .= qq| AND (o.expected_billing_date <= ?)|;
308 push @values, conv_date($form->{expected_billing_date_to});
311 if ($form->{intnotes}) {
312 $query .= qq| AND o.intnotes ILIKE ?|;
313 push(@values, like($form->{intnotes}));
316 if ($form->{phone_notes}) {
317 $query .= qq| AND (phone_notes.subject ILIKE ? OR phone_notes.body ILIKE ?)|;
318 push(@values, like($form->{phone_notes}), like($form->{phone_notes}));
321 $form->{fulltext} = trim($form->{fulltext});
322 if ($form->{fulltext}) {
323 my @fulltext_fields = qw(o.notes
327 o.transaction_description
332 $query .= join ' ILIKE ? OR ', @fulltext_fields;
333 $query .= ' ILIKE ?';
334 $query .= qq| OR EXISTS (SELECT files.id FROM files LEFT JOIN file_full_texts ON (file_full_texts.file_id = files.id) WHERE files.object_id = o.id AND files.object_type = 'sales_order' AND file_full_texts.full_text ILIKE ?)|;
335 $query .= qq| OR EXISTS (SELECT notes.id FROM notes WHERE notes.trans_id = o.id AND notes.trans_module LIKE 'oe' AND (notes.subject ILIKE ? OR notes.body ILIKE ?))|;
336 $query .= qq| OR EXISTS (SELECT follow_up_links.id FROM follow_up_links WHERE follow_up_links.trans_id = o.id AND trans_type = 'sales_order' AND EXISTS (SELECT notes.id FROM notes WHERE trans_module LIKE 'fu' AND trans_id = follow_up_links.follow_up_id AND (notes.subject ILIKE ? OR notes.body ILIKE ?)))|;
338 push(@values, like($form->{fulltext})) for 1 .. (scalar @fulltext_fields) + 5;
341 if ($form->{parts_partnumber}) {
344 SELECT orderitems.trans_id
346 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
347 WHERE (orderitems.trans_id = o.id)
348 AND (parts.partnumber ILIKE ?)
352 push @values, like($form->{parts_partnumber});
355 if ($form->{parts_description}) {
358 SELECT orderitems.trans_id
360 WHERE (orderitems.trans_id = o.id)
361 AND (orderitems.description ILIKE ?)
365 push @values, like($form->{parts_description});
369 my @tokens = parse_line('\s+', 0, $form->{all});
370 # ordnumber quonumber customer.name vendor.name transaction_description
372 o.ordnumber ILIKE ? OR
373 o.quonumber ILIKE ? OR
375 o.transaction_description ILIKE ?
377 push @values, (like($_))x4 for @tokens;
380 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
381 'trans_id_field' => 'ct.id',
385 $query .= qq| AND ($cvar_where)|;
386 push @values, @cvar_values;
389 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
390 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
391 my %allowed_sort_columns = (
392 "transdate" => "o.transdate",
393 "reqdate" => "o.reqdate",
395 "ordnumber" => "o.ordnumber",
396 "cusordnumber" => "o.cusordnumber",
397 "quonumber" => "o.quonumber",
399 "employee" => "e.name",
400 "salesman" => "s.name",
401 "shipvia" => "o.shipvia",
402 "transaction_description" => "o.transaction_description",
403 "shippingpoint" => "o.shippingpoint",
404 "insertdate" => "o.itime",
405 "taxzone" => "tz.description",
406 "payment_terms" => "pt.description",
407 "department" => "department.description",
408 "intnotes" => "o.intnotes",
410 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
411 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
413 $query .= qq| ORDER by | . $sortorder;
415 my $sth = $dbh->prepare($query);
416 $sth->execute(@values) ||
417 $form->dberror($query . " (" . join(", ", @values) . ")");
421 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
422 $ref->{billed_amount} = $billed_amount{$ref->{id}};
423 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
424 if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
425 $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
426 $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
428 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
429 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
431 $ref->{exchangerate} ||= $ref->{daily_exchangerate};
432 $ref->{exchangerate} ||= 1;
433 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
434 $id{ $ref->{id} } = $ref->{id};
439 $main::lxdebug->leave_sub();
442 sub transactions_for_todo_list {
443 $main::lxdebug->enter_sub();
448 my $myconfig = \%main::myconfig;
449 my $form = $main::form;
451 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
453 my $query = qq|SELECT id FROM employee WHERE login = ?|;
454 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
457 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
458 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
463 LEFT JOIN customer c ON (oe.customer_id = c.id)
464 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
465 LEFT JOIN employee e ON (oe.employee_id = e.id)
466 WHERE (COALESCE(quotation, FALSE) = TRUE)
467 AND (COALESCE(closed, FALSE) = FALSE)
468 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
469 AND NOT (oe.reqdate ISNULL)
470 AND (oe.reqdate < current_date)
473 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
475 $main::lxdebug->leave_sub();
481 my ($self, $myconfig, $form) = @_;
482 $main::lxdebug->enter_sub();
484 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
486 $::lxdebug->leave_sub;
492 $main::lxdebug->enter_sub();
494 my ($self, $myconfig, $form) = @_;
496 my $dbh = SL::DB->client->dbh;
497 my $restricter = SL::HTML::Restrict->create;
499 my ($query, @values, $sth, $null);
500 my $exchangerate = 0;
502 my $all_units = AM->retrieve_units($myconfig, $form);
503 $form->{all_units} = $all_units;
505 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
508 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
509 unless ($form->{employee_id}) {
510 $form->get_employee($dbh);
513 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
515 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
516 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
517 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
518 my $is_new = !$form->{id};
521 $query = qq|DELETE FROM custom_variables
522 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
523 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
524 do_query($form, $dbh, $query, $form->{id});
526 $query = qq|DELETE FROM shipto | .
527 qq|WHERE trans_id = ? AND module = 'OE'|;
528 do_query($form, $dbh, $query, $form->{id});
532 $query = qq|SELECT nextval('id')|;
533 ($form->{id}) = selectrow_query($form, $dbh, $query);
535 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
536 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
553 my @processed_orderitems;
555 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
556 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
559 for my $i (1 .. $form->{rowcount}) {
561 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
563 if ($form->{"id_$i"}) {
566 $query = qq|SELECT unit FROM parts WHERE id = ?|;
567 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
570 if (defined($all_units->{$item_unit}->{factor}) &&
571 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
572 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
574 my $baseqty = $form->{"qty_$i"} * $basefactor;
576 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
577 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
579 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
581 # keep entered selling price
583 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
585 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
587 my $decimalplaces = ($dec > 2) ? $dec : 2;
589 # undo discount formatting
590 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
593 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
595 # round linetotal at least to 2 decimal places
596 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
597 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
599 $form->{"inventory_accno_$i"} *= 1;
600 $form->{"expense_accno_$i"} *= 1;
602 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
606 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
608 if ($form->{taxincluded}) {
609 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
610 $taxbase = $linetotal - $taxamount;
612 # we are not keeping a natural price, do not round
613 $form->{"sellprice_$i"} =
614 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
616 $taxamount = $linetotal * $taxrate;
617 $taxbase = $linetotal;
620 if ($form->round_amount($taxrate, 7) == 0) {
621 if ($form->{taxincluded}) {
622 foreach my $item (@taxaccounts) {
623 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
624 $taxaccounts{$item} += $taxamount;
625 $taxdiff += $taxamount;
626 $taxbase{$item} += $taxbase;
628 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
630 foreach my $item (@taxaccounts) {
631 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
632 $taxbase{$item} += $taxbase;
636 foreach my $item (@taxaccounts) {
637 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
638 $taxbase{$item} += $taxbase;
642 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
644 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
646 # Get pricegroup_id and save it. Unfortunately the interface
647 # also uses ID "0" for signalling that none is selected, but "0"
648 # must not be stored in the database. Therefore we cannot simply
650 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
652 $pricegroup_id = undef if !$pricegroup_id;
654 # force new project, if not set yet
655 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
656 require SL::DB::Customer;
657 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
658 die "Can't find customer" unless $customer;
659 die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
660 if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
662 my $new_project = SL::DB::Project->new(
663 projectnumber => $form->{ordnumber},
664 description => $customer->name,
665 customer_id => $customer->id,
667 project_type_id => $::instance_conf->get_project_type_id,
668 project_status_id => $::instance_conf->get_project_status_id,
671 $form->{"globalproject_id"} = $new_project->id;
674 CVar->get_non_editable_ic_cvars(form => $form,
677 sub_module => 'orderitems',
678 may_converted_from => ['orderitems', 'invoice']);
682 # save detail record in orderitems table
683 if (! $form->{"orderitems_id_$i"}) {
684 $query = qq|SELECT nextval('orderitemsid')|;
685 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
687 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
688 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
691 my $orderitems_id = $form->{"orderitems_id_$i"};
692 push @processed_orderitems, $orderitems_id;
695 UPDATE orderitems SET
696 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
697 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
698 pricegroup_id = ?, subtotal = ?,
699 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
700 active_price_source = ?, active_discount_source = ?,
701 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
705 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
706 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
707 $form->{"qty_$i"}, $baseqty,
708 $fxsellprice, $form->{"discount_$i"},
709 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
710 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
711 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
712 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
713 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
714 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
715 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
716 conv_i($orderitems_id),
719 do_query($form, $dbh, $query, @values);
721 $form->{"sellprice_$i"} = $fxsellprice;
722 $form->{"discount_$i"} *= 100;
724 CVar->save_custom_variables(module => 'IC',
725 sub_module => 'orderitems',
726 trans_id => $orderitems_id,
727 configs => $ic_cvar_configs,
729 name_prefix => 'ic_',
730 name_postfix => "_$i",
733 # link previous items with orderitems
734 # assume we have a new workflow if we link from invoice or order to quotation
735 # unluckily orderitems are used for quotation and orders - therefore one more
736 # check to be sure NOT to link from order to quotation
737 foreach (qw(orderitems)) {
738 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
739 && $form->{type} !~ 'quotation') {
740 RecordLinks->create_links('dbh' => $dbh,
743 'from_ids' => $form->{"converted_from_${_}_id_$i"},
744 'to_table' => 'orderitems',
745 'to_id' => $orderitems_id,
748 delete $form->{"converted_from_${_}_id_$i"};
753 # search for orphaned ids
754 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
755 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
756 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
758 if (scalar @orphaned_ids) {
759 # clean up orderitems
760 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
761 do_query($form, $dbh, $query, @orphaned_ids);
764 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
768 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
770 $amount = $form->round_amount($netamount + $tax, 2, 1);
771 $netamount = $form->round_amount($netamount, 2);
773 if ($form->{currency} eq $form->{defaultcurrency}) {
774 $form->{exchangerate} = 1;
776 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
779 # from inputfield (exchangerate) or hidden (forex)
780 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
782 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
784 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
789 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
790 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, tax_point = ?, taxincluded = ?,
791 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
792 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
793 taxzone_id = ?, shipto_id = ?, billing_address_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
794 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
795 , order_probability = ?, expected_billing_date = ?
798 @values = ($form->{ordnumber} || '', $form->{quonumber},
799 $form->{cusordnumber}, conv_date($form->{transdate}),
800 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
801 $amount, $netamount, conv_date($reqdate), conv_date($form->{tax_point}),
802 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
803 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
804 $form->{currency}, $form->{closed} ? 't' : 'f',
805 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
806 $quotation, conv_i($form->{department_id}),
807 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
808 conv_i($form->{shipto_id}), conv_i($form->{billing_address_id}), conv_i($form->{payment_id}),
809 conv_i($form->{delivery_vendor_id}),
810 conv_i($form->{delivery_customer_id}),
811 conv_i($form->{delivery_term_id}),
812 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
813 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
814 $form->{transaction_description},
815 $form->{marge_total} * 1, $form->{marge_percent} * 1,
816 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
817 conv_i($form->{id}));
818 do_query($form, $dbh, $query, @values);
820 $form->new_lastmtime('oe');
822 $form->{ordtotal} = $amount;
824 $form->{name} = $form->{ $form->{vc} };
825 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
828 if (!$form->{shipto_id}) {
829 $form->add_shipto($dbh, $form->{id}, "OE");
832 # save printed, emailed, queued
833 $form->save_status($dbh);
835 # Link this record to the records it was created from.
836 $form->{convert_from_oe_ids} =~ s/^\s+//;
837 $form->{convert_from_oe_ids} =~ s/\s+$//;
838 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
839 delete $form->{convert_from_oe_ids};
840 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
841 RecordLinks->create_links('dbh' => $dbh,
843 'from_table' => 'oe',
844 'from_ids' => \@convert_from_oe_ids,
846 'to_id' => $form->{id},
848 $self->_close_quotations_rfqs('dbh' => $dbh,
849 'from_id' => \@convert_from_oe_ids,
850 'to_id' => $form->{id});
853 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
854 if ($form->{vc} eq 'customer') {
855 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
857 if ($form->{vc} eq 'vendor') {
858 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
862 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
863 "quonumber" : "ordnumber"};
865 Common::webdav_folder($form);
867 $self->save_periodic_invoices_config(dbh => $dbh,
868 oe_id => $form->{id},
869 config_yaml => $form->{periodic_invoices_config})
870 if ($form->{type} eq 'sales_order');
872 $self->_link_created_sales_order_to_requirement_specs_for_sales_quotations(
873 type => $form->{type},
874 converted_from_ids => \@convert_from_oe_ids,
875 sales_order_id => $form->{id},
879 $self->_set_project_in_linked_requirement_spec(
880 type => $form->{type},
881 project_id => $form->{globalproject_id},
882 sales_order_id => $form->{id},
885 $main::lxdebug->leave_sub();
890 sub _link_created_sales_order_to_requirement_specs_for_sales_quotations {
891 my ($self, %params) = @_;
893 # If this is a sales order created from a sales quotation and if
894 # that sales quotation was created from a requirement spec document
895 # then link the newly created sales order to the requirement spec
898 return if !$params{is_new};
899 return if $params{type} ne 'sales_order';
900 return if !@{ $params{converted_from_ids} };
902 my $oe_objects = SL::DB::Manager::Order->get_all(where => [ id => $params{converted_from_ids} ]);
903 my @sales_quotations = grep { $_->is_type('sales_quotation') } @{ $oe_objects };
905 return if !@sales_quotations;
907 my $rs_orders = SL::DB::Manager::RequirementSpecOrder->get_all(where => [ order_id => [ map { $_->id } @sales_quotations ] ]);
909 return if !@{ $rs_orders };
911 $rs_orders->[0]->db->with_transaction(sub {
912 foreach my $rs_order (@{ $rs_orders }) {
913 SL::DB::RequirementSpecOrder->new(
914 order_id => $params{sales_order_id},
915 requirement_spec_id => $rs_order->requirement_spec_id,
916 version_id => $rs_order->version_id,
924 sub _set_project_in_linked_requirement_spec {
925 my ($self, %params) = @_;
927 return if $params{type} ne 'sales_order';
928 return if !$params{project_id} || !$params{sales_order_id};
931 UPDATE requirement_specs
934 SELECT so.requirement_spec_id
935 FROM requirement_spec_orders so
936 WHERE so.order_id = ?
940 do_query($::form, $::form->get_standard_dbh, $query, $params{project_id}, $params{sales_order_id});
943 sub save_periodic_invoices_config {
944 my ($self, %params) = @_;
946 return if !$params{oe_id};
948 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
949 return if 'HASH' ne ref $config;
951 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
952 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
953 $obj->update_attributes(%{ $config });
956 sub load_periodic_invoice_config {
960 delete $form->{periodic_invoices_config};
963 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
966 my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity order_value_periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
967 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
968 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
973 sub _close_quotations_rfqs {
974 $main::lxdebug->enter_sub();
979 Common::check_params(\%params, qw(from_id to_id));
981 my $myconfig = \%main::myconfig;
982 my $form = $main::form;
984 my $dbh = $params{dbh} || SL::DB->client->dbh;
986 SL::DB->client->with_transaction(sub {
988 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
989 my $sth = prepare_query($form, $dbh, $query);
991 do_statement($form, $sth, $query, conv_i($params{to_id}));
993 my ($quotation) = $sth->fetchrow_array();
1001 foreach my $from_id (@{ $params{from_id} }) {
1002 $from_id = conv_i($from_id);
1003 do_statement($form, $sth, $query, $from_id);
1004 ($quotation) = $sth->fetchrow_array();
1005 push @close_ids, $from_id if ($quotation);
1010 if (scalar @close_ids) {
1011 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
1012 do_query($form, $dbh, $query, @close_ids);
1015 }) or do { die SL::DB->client->error };
1017 $main::lxdebug->leave_sub();
1021 $main::lxdebug->enter_sub();
1023 my ($self, $myconfig, $form) = @_;
1025 my $rc = SL::DB::Order->new->db->with_transaction(sub {
1026 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
1028 SL::DB::Order->new(id => $form->{id})->delete;
1030 my $spool = $::lx_office_conf{paths}->{spool};
1031 unlink map { "$spool/$_" } @spoolfiles if $spool;
1034 }) or do { die SL::DB->client->error };
1036 $main::lxdebug->leave_sub();
1042 my ($self, $myconfig, $form) = @_;
1043 $main::lxdebug->enter_sub();
1045 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
1047 $::lxdebug->leave_sub;
1052 my ($self, $myconfig, $form) = @_;
1054 # connect to database
1055 my $dbh = SL::DB->client->dbh;
1057 my ($query, $query_add, @values, @ids, $sth);
1059 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
1061 push @ids, $form->{"trans_id_$_"}
1062 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
1063 } (1 .. $form->{"rowcount"});
1065 if ($form->{rowcount} && scalar @ids) {
1066 $form->{convert_from_oe_ids} = join ' ', @ids;
1069 # if called in multi id mode, and still only got one id, switch back to single id
1070 if ($form->{"rowcount"} and $#ids == 0) {
1071 $form->{"id"} = $ids[0];
1073 delete $form->{convert_from_oe_ids};
1076 # and remember for the rest of the function
1077 my $is_collective_order = scalar @ids;
1079 # If collective order was created from exactly 1 order, we assume the same
1080 # behaviour as a "save as new" from within an order is actually desired, i.e.
1081 # the original order isn't part of a workflow where we want to remember
1082 # record_links, but simply a quick way of generating a new order from an old
1083 # one without having to enter everything again.
1084 # Setting useasnew will prevent the creation of record_links for the items
1085 # when saving the new order.
1086 # This form variable is probably not necessary, could just set saveasnew instead
1087 $form->{useasnew} = 1 if $is_collective_order == 1;
1090 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
1091 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
1092 if ( ($form->{type} eq 'sales_order' && !$::instance_conf->get_deliverydate_on)
1093 || ($form->{type} eq 'sales_quotation' && !$::instance_conf->get_reqdate_on)) {
1094 $form->{reqdate} = '';
1096 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
1098 $form->{transdate} = DateTime->today_local->to_kivitendo;
1101 # get default accounts
1102 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1103 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1104 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1105 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1106 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1107 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
1108 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
1111 my $ref = selectfirst_hashref_query($form, $dbh, $query);
1112 map { $form->{$_} = $ref->{$_} } keys %$ref;
1114 $form->{currency} = $form->get_default_currency($myconfig);
1116 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
1117 # we come from invoices, feel free.
1118 $form->{reqdate} = $form->{deliverydate}
1119 if ( $form->{deliverydate}
1120 and $form->{callback} =~ /action=ar_transactions/);
1122 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
1124 if ($form->{id} or @ids) {
1126 # retrieve order for single id
1127 # NOTE: this query is intended to fetch all information only ONCE.
1128 # so if any of these infos is important (or even different) for any item,
1129 # it will be killed out and then has to be fetched from the item scope query further down
1131 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1132 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1133 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1134 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1135 o.closed, o.reqdate, o.tax_point, o.quonumber, o.department_id, o.cusordnumber,
1137 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1138 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, o.billing_address_id,
1139 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1140 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1142 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1143 LEFT JOIN employee e ON (o.employee_id = e.id)
1144 LEFT JOIN department d ON (o.department_id = d.id) | .
1147 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1149 @values = $form->{id} ? ($form->{id}) : @ids;
1150 $sth = prepare_execute_query($form, $dbh, $query, @values);
1152 $ref = $sth->fetchrow_hashref("NAME_lc");
1155 map { $form->{$_} = $ref->{$_} } keys %$ref;
1157 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1159 # set all entries for multiple ids blank that yield different information
1160 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1161 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1164 $form->{mtime} ||= $form->{itime};
1165 $form->{lastmtime} = $form->{mtime};
1167 # if not given, fill transdate with current_date
1168 $form->{transdate} = $form->current_date($myconfig)
1169 unless $form->{transdate};
1173 if ($form->{delivery_customer_id}) {
1174 $query = qq|SELECT name FROM customer WHERE id = ?|;
1175 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1178 if ($form->{delivery_vendor_id}) {
1179 $query = qq|SELECT name FROM customer WHERE id = ?|;
1180 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1183 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1185 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1186 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1188 $ref = $sth->fetchrow_hashref("NAME_lc");
1189 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1192 if ($ref->{shipto_id}) {
1193 my $cvars = CVar->get_custom_variables(
1196 trans_id => $ref->{shipto_id},
1198 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1201 # get printed, emailed and queued
1202 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1203 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1205 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1206 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1207 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1208 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1211 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1214 my $transdate = $form->{tax_point} ? $dbh->quote($form->{tax_point}) : $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1216 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1217 unshift @values, ($form->{taxzone_id}) x 2;
1219 # retrieve individual items
1220 # this query looks up all information about the items
1221 # stuff different from the whole will not be overwritten, but saved with a suffix.
1223 qq|SELECT o.id AS orderitems_id,
1224 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1225 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1226 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1227 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1228 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1229 p.classification_id,
1230 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1231 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1232 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1233 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1234 pr.projectnumber, p.formel,
1235 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1237 JOIN parts p ON (o.parts_id = p.id)
1238 JOIN oe ON (o.trans_id = oe.id)
1239 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1240 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
1241 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
1242 LEFT JOIN project pr ON (o.project_id = pr.id)
1243 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1245 ? qq|WHERE o.trans_id = ?|
1246 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1247 qq|ORDER BY o.trans_id, o.position|;
1249 @ids = $form->{id} ? ($form->{id}) : @ids;
1250 $sth = prepare_execute_query($form, $dbh, $query, @values);
1252 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1253 # Retrieve custom variables.
1254 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1256 sub_module => 'orderitems',
1257 trans_id => $ref->{orderitems_id},
1259 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1262 if (!$ref->{"part_type"} eq 'part') {
1263 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1265 # delete($ref->{"part_inventory_accno_id"});
1267 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1268 # unless already present there
1269 # remove _oe entries afterwards
1270 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1271 qw|ordnumber transdate cusordnumber|
1273 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1277 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1279 qq|SELECT accno AS inventory_accno, | .
1280 qq| new_chart_id AS inventory_new_chart, | .
1281 qq| date($transdate) - valid_from AS inventory_valid | .
1282 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1283 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1284 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1287 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1289 qq|SELECT accno AS income_accno, | .
1290 qq| new_chart_id AS income_new_chart, | .
1291 qq| date($transdate) - valid_from AS income_valid | .
1292 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1293 ($ref->{income_accno}, $ref->{income_new_chart},
1294 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1297 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1299 qq|SELECT accno AS expense_accno, | .
1300 qq| new_chart_id AS expense_new_chart, | .
1301 qq| date($transdate) - valid_from AS expense_valid | .
1302 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1303 ($ref->{expense_accno}, $ref->{expense_new_chart},
1304 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1307 # delete orderitems_id in collective orders, so that they get cloned no matter what
1308 # is this correct? or is the following meant?
1309 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1310 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1312 # get tax rates and description
1313 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1315 qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
1317 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1318 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1319 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1320 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1321 qq|ORDER BY c.accno|;
1322 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1323 $ref->{taxaccounts} = "";
1325 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1326 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1330 $ref->{taxaccounts} .= "$ptr->{accno} ";
1331 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1332 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1333 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1334 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1335 $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
1336 $form->{taxaccounts} .= "$ptr->{accno} ";
1341 chop $ref->{taxaccounts};
1343 push @{ $form->{form_details} }, $ref;
1350 # get last name used
1351 $form->lastname_used($dbh, $myconfig, $form->{vc})
1352 unless $form->{"$form->{vc}_id"};
1356 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1358 Common::webdav_folder($form);
1360 $self->load_periodic_invoice_config($form);
1365 sub retrieve_simple {
1366 $main::lxdebug->enter_sub();
1371 Common::check_params(\%params, qw(id));
1373 my $myconfig = \%main::myconfig;
1374 my $form = $main::form;
1376 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1378 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1379 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1381 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1382 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1384 $main::lxdebug->leave_sub();
1390 $main::lxdebug->enter_sub();
1392 my ($self, $myconfig, $form) = @_;
1394 # connect to database
1395 my $dbh = SL::DB->client->dbh;
1401 my $nodiscount_subtotal = 0;
1402 my $discount_subtotal = 0;
1405 my @partsgroup = ();
1408 my $subtotal_header = 0;
1409 my $subposition = 0;
1417 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1419 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1422 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1423 $price_factors{$pfac->{id}} = $pfac;
1424 $pfac->{factor} *= 1;
1425 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1428 # sort items by partsgroup
1429 for $i (1 .. $form->{rowcount}) {
1431 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1432 $partsgroup = $form->{"partsgroup_$i"};
1434 push @partsgroup, [$i, $partsgroup];
1435 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1441 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1442 %projects_by_id = map { $_->id => $_ } @$projects;
1445 if ($projects_by_id{$form->{"globalproject_id"}}) {
1446 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1447 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1449 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1450 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1454 $form->{discount} = [];
1456 # get some values of parts from db on store them in extra array,
1457 # so that they can be sorted in later
1458 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1459 my @prepared_arrays = keys %prepared_template_arrays;
1460 my @separate_totals = qw(non_separate_subtotal);
1462 $form->{TEMPLATE_ARRAYS} = { };
1464 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1465 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1468 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1469 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1470 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1471 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1472 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt optional);
1474 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1475 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1477 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1479 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1481 my $totalweight = 0;
1483 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1486 if ($item->[1] ne $sameitem) {
1487 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1488 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1489 $sameitem = $item->[1];
1491 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1494 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1496 if ($form->{"id_$i"} != 0) {
1498 # add number, description and qty to $form->{number}, ....
1500 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1501 $subtotal_header = $i;
1502 $position = int($position);
1505 } elsif ($subtotal_header) {
1507 $position = int($position);
1508 $position = $position.".".$subposition;
1510 $position = int($position);
1514 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1516 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1518 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1519 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1520 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1521 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1522 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1523 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1524 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1525 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1526 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1527 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1528 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1529 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1530 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1531 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1532 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1533 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1534 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1535 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1536 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1537 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1538 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1539 push @{ $form->{TEMPLATE_ARRAYS}->{optional} }, $form->{"optional_$i"};
1541 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1542 my ($dec) = ($sellprice =~ /\.(\d+)/);
1543 my $decimalplaces = max 2, length($dec);
1545 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1547 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1548 my $linetotal = $form->round_amount($linetotal_exact, 2);
1550 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1551 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1553 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1555 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1557 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1559 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1560 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1562 $linetotal = ($linetotal != 0) ? $linetotal : '';
1564 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1565 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1566 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1568 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1569 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1570 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1571 push @separate_totals , "separate_${pabbr}_subtotal";
1572 $form->{"separate_${pabbr}_subtotal"} = 0;
1574 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1576 $form->{non_separate_subtotal} += $linetotal;
1579 $form->{ordtotal} += $linetotal unless $form->{"optional_$i"};
1580 $form->{nodiscount_total} += $nodiscount_linetotal;
1581 $form->{discount_total} += $discount;
1583 if ($subtotal_header) {
1584 $discount_subtotal += $linetotal;
1585 $nodiscount_subtotal += $nodiscount_linetotal;
1588 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1589 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1590 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1591 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1592 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1594 $discount_subtotal = 0;
1595 $nodiscount_subtotal = 0;
1596 $subtotal_header = 0;
1599 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1602 if (!$form->{"discount_$i"}) {
1603 $nodiscount += $linetotal;
1606 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1608 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1609 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1610 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1611 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1612 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1613 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1615 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1616 $totalweight += $lineweight;
1617 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1618 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1619 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1620 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1622 my ($taxamount, $taxbase);
1625 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1627 unless ($form->{"optional_$i"}) {
1628 if ($form->{taxincluded}) {
1631 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1632 $taxbase = $linetotal / (1 + $taxrate);
1634 $taxamount = $linetotal * $taxrate;
1635 $taxbase = $linetotal;
1639 if ($taxamount != 0) {
1640 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1641 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1642 $taxbase{$accno} += $taxbase;
1646 $tax_rate = $taxrate * 100;
1647 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1649 if ($form->{"part_type_$i"} eq 'assembly') {
1652 # get parts and push them onto the stack
1654 if ($form->{groupitems}) {
1655 $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
1657 $sortorder = qq|ORDER BY a.position|;
1660 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1661 qq|pg.partsgroup | .
1662 qq|FROM assembly a | .
1663 qq| JOIN parts p ON (a.parts_id = p.id) | .
1664 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1665 qq| WHERE a.bom = '1' | .
1666 qq| AND a.id = ? | . $sortorder;
1667 @values = ($form->{"id_$i"});
1668 $sth = $dbh->prepare($query);
1669 $sth->execute(@values) || $form->dberror($query);
1671 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1672 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1673 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1674 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1675 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1676 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1679 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1680 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1681 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1686 CVar->get_non_editable_ic_cvars(form => $form,
1689 sub_module => 'orderitems',
1690 may_converted_from => ['orderitems', 'invoice']);
1692 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1693 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1694 for @{ $ic_cvar_configs };
1696 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1700 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1701 $form->{totalweight_nofmt} = $totalweight;
1702 my $defaults = AM->get_defaults();
1703 $form->{weightunit} = $defaults->{weightunit};
1706 foreach $item (sort keys %taxaccounts) {
1707 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1709 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1710 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1711 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1712 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1713 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1714 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1715 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1716 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} }, $form->{"${item}_tax_id"});
1718 if ( $form->{"${item}_tax_id"} ) {
1719 my $tax_obj = SL::DB::Manager::Tax->find_by(id => $form->{"${item}_tax_id"}) or die "Can't find tax with id " . $form->{"${item}_tax_id"};
1720 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1721 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1725 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1726 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1727 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1728 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1730 if($form->{taxincluded}) {
1731 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1732 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1734 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1735 $form->{subtotal_nofmt} = $form->{ordtotal};
1738 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1739 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1740 $form->{rounding} = $form->round_amount(
1741 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1746 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1747 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1749 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1751 $form->{username} = $myconfig->{name};
1753 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1754 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1755 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1757 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1758 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1760 $main::lxdebug->leave_sub();
1769 OE.pm - Order entry module
1773 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>.
1779 =item retrieve_simple PARAMS
1781 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1783 my $order = retrieve_simple(id => 2);