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;
50 use SL::HTML::Restrict;
53 use SL::Util qw(trim);
61 $main::lxdebug->enter_sub();
63 my ($self, $myconfig, $form) = @_;
66 my $dbh = $form->get_standard_dbh;
69 my $ordnumber = 'ordnumber';
75 my ($periodic_invoices_columns, $periodic_invoices_joins);
77 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
79 if ($form->{type} =~ /_quotation$/) {
81 $ordnumber = 'quonumber';
83 } elsif ($form->{type} eq 'sales_order') {
84 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
85 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
88 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
92 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
94 SELECT from_id, ar.amount, ar.netamount FROM (
97 WHERE from_table = 'oe' AND to_table = 'ar'
99 SELECT rl1.from_id, rl2.to_id
100 FROM record_links rl1
101 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
102 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
104 SELECT rl1.from_id, rl3.to_id
105 FROM record_links rl1
106 JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
107 JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
108 WHERE (rl1.from_table = 'oe') AND rl2.to_table = 'ar'
110 LEFT JOIN ar ON ar.id = rl.to_id
112 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
113 $billed_amount{ $ref->{from_id}} += $ref->{amount};
114 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
119 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
120 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
121 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
122 qq| o.transaction_description, | .
123 qq| o.marge_total, o.marge_percent, | .
124 qq| o.itime::DATE AS insertdate, | .
125 qq| department.description as department, | .
126 qq| ex.$rate AS exchangerate, | .
127 qq| pt.description AS payment_terms, | .
128 qq| pr.projectnumber AS globalprojectnumber, | .
129 qq| e.name AS employee, s.name AS salesman, | .
130 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
131 qq| tz.description AS taxzone | .
132 $periodic_invoices_columns .
133 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
135 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
136 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
137 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
138 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
139 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
140 qq| AND ex.transdate = o.transdate) | .
141 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
142 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
143 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
144 qq|LEFT JOIN department ON (o.department_id = department.id) | .
145 qq|$periodic_invoices_joins | .
146 qq|WHERE (o.quotation = ?) |;
147 push(@values, $quotation);
149 if ($form->{department_id}) {
150 $query .= qq| AND o.department_id = ?|;
151 push(@values, $form->{department_id});
154 if ($form->{"project_id"}) {
156 qq|AND ((globalproject_id = ?) OR EXISTS | .
157 qq| (SELECT * FROM orderitems oi | .
158 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
159 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
162 if ($form->{"projectnumber"}) {
164 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
165 SELECT * FROM orderitems oi
166 LEFT JOIN project proi ON proi.id = oi.project_id
167 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
170 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
173 if ($form->{"business_id"}) {
174 $query .= " AND ct.business_id = ?";
175 push(@values, $form->{"business_id"});
178 if ($form->{"${vc}_id"}) {
179 $query .= " AND o.${vc}_id = ?";
180 push(@values, $form->{"${vc}_id"});
182 } elsif ($form->{$vc}) {
183 $query .= " AND ct.name ILIKE ?";
184 push(@values, like($form->{$vc}));
187 if ($form->{"cp_name"}) {
188 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
189 push(@values, (like($form->{"cp_name"}))x2);
192 if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {
193 $query .= " AND o.employee_id = (select id from employee where login= ?)";
194 push @values, $::myconfig{login};
196 if ($form->{employee_id}) {
197 $query .= " AND o.employee_id = ?";
198 push @values, conv_i($form->{employee_id});
201 if ($form->{salesman_id}) {
202 $query .= " AND o.salesman_id = ?";
203 push @values, conv_i($form->{salesman_id});
206 if (!$form->{open} && !$form->{closed}) {
207 $query .= " AND o.id = 0";
208 } elsif (!($form->{open} && $form->{closed})) {
209 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
212 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
213 ($form->{"notdelivered"} ne $form->{"delivered"})) {
214 $query .= $form->{"delivered"} ?
215 " AND o.delivered " : " AND NOT o.delivered";
218 if ($form->{$ordnumber}) {
219 $query .= qq| AND o.$ordnumber ILIKE ?|;
220 push(@values, like($form->{$ordnumber}));
223 if ($form->{cusordnumber}) {
224 $query .= qq| AND o.cusordnumber ILIKE ?|;
225 push(@values, like($form->{cusordnumber}));
228 if($form->{transdatefrom}) {
229 $query .= qq| AND o.transdate >= ?|;
230 push(@values, conv_date($form->{transdatefrom}));
233 if($form->{transdateto}) {
234 $query .= qq| AND o.transdate <= ?|;
235 push(@values, conv_date($form->{transdateto}));
238 if($form->{reqdatefrom}) {
239 $query .= qq| AND o.reqdate >= ?|;
240 push(@values, conv_date($form->{reqdatefrom}));
243 if($form->{reqdateto}) {
244 $query .= qq| AND o.reqdate <= ?|;
245 push(@values, conv_date($form->{reqdateto}));
248 if($form->{insertdatefrom}) {
249 $query .= qq| AND o.itime::DATE >= ?|;
250 push(@values, conv_date($form->{insertdatefrom}));
253 if($form->{insertdateto}) {
254 $query .= qq| AND o.itime::DATE <= ?|;
255 push(@values, conv_date($form->{insertdateto}));
258 if ($form->{shippingpoint}) {
259 $query .= qq| AND o.shippingpoint ILIKE ?|;
260 push(@values, like($form->{shippingpoint}));
263 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
264 $query .= qq| AND tz.id = ?|;
265 push(@values, $form->{taxzone_id});
268 if ($form->{transaction_description}) {
269 $query .= qq| AND o.transaction_description ILIKE ?|;
270 push(@values, like($form->{transaction_description}));
273 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
274 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
275 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
278 if ($form->{reqdate_unset_or_old}) {
279 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
282 if (($form->{order_probability_value} || '') ne '') {
283 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
284 $query .= qq| AND (o.order_probability ${op} ?)|;
285 push @values, trim($form->{order_probability_value});
288 if ($form->{expected_billing_date_from}) {
289 $query .= qq| AND (o.expected_billing_date >= ?)|;
290 push @values, conv_date($form->{expected_billing_date_from});
293 if ($form->{expected_billing_date_to}) {
294 $query .= qq| AND (o.expected_billing_date <= ?)|;
295 push @values, conv_date($form->{expected_billing_date_to});
298 if ($form->{parts_partnumber}) {
301 SELECT orderitems.trans_id
303 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
304 WHERE (orderitems.trans_id = o.id)
305 AND (parts.partnumber ILIKE ?)
309 push @values, like($form->{parts_partnumber});
312 if ($form->{parts_description}) {
315 SELECT orderitems.trans_id
317 WHERE (orderitems.trans_id = o.id)
318 AND (orderitems.description ILIKE ?)
322 push @values, like($form->{parts_description});
326 my @tokens = parse_line('\s+', 0, $form->{all});
327 # ordnumber quonumber customer.name vendor.name transaction_description
329 o.ordnumber ILIKE ? OR
330 o.quonumber ILIKE ? OR
332 o.transaction_description ILIKE ?
334 push @values, (like($_))x4 for @tokens;
337 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
338 'trans_id_field' => 'ct.id',
342 $query .= qq| AND ($cvar_where)|;
343 push @values, @cvar_values;
346 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
347 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
348 my %allowed_sort_columns = (
349 "transdate" => "o.transdate",
350 "reqdate" => "o.reqdate",
352 "ordnumber" => "o.ordnumber",
353 "cusordnumber" => "o.cusordnumber",
354 "quonumber" => "o.quonumber",
356 "employee" => "e.name",
357 "salesman" => "s.name",
358 "shipvia" => "o.shipvia",
359 "transaction_description" => "o.transaction_description",
360 "shippingpoint" => "o.shippingpoint",
361 "insertdate" => "o.itime",
362 "taxzone" => "tz.description",
363 "payment_terms" => "pt.description",
364 "department" => "department.description",
366 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
367 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
369 $query .= qq| ORDER by | . $sortorder;
371 my $sth = $dbh->prepare($query);
372 $sth->execute(@values) ||
373 $form->dberror($query . " (" . join(", ", @values) . ")");
377 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
378 $ref->{billed_amount} = $billed_amount{$ref->{id}};
379 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
380 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
381 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
382 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
383 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
384 $id{ $ref->{id} } = $ref->{id};
389 $main::lxdebug->leave_sub();
392 sub transactions_for_todo_list {
393 $main::lxdebug->enter_sub();
398 my $myconfig = \%main::myconfig;
399 my $form = $main::form;
401 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
403 my $query = qq|SELECT id FROM employee WHERE login = ?|;
404 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
407 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
408 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
413 LEFT JOIN customer c ON (oe.customer_id = c.id)
414 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
415 LEFT JOIN employee e ON (oe.employee_id = e.id)
416 WHERE (COALESCE(quotation, FALSE) = TRUE)
417 AND (COALESCE(closed, FALSE) = FALSE)
418 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
419 AND NOT (oe.reqdate ISNULL)
420 AND (oe.reqdate < current_date)
423 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
425 $main::lxdebug->leave_sub();
431 my ($self, $myconfig, $form) = @_;
432 $main::lxdebug->enter_sub();
434 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
436 $::lxdebug->leave_sub;
442 $main::lxdebug->enter_sub();
444 my ($self, $myconfig, $form) = @_;
446 my $dbh = SL::DB->client->dbh;
447 my $restricter = SL::HTML::Restrict->create;
449 my ($query, @values, $sth, $null);
450 my $exchangerate = 0;
452 my $all_units = AM->retrieve_units($myconfig, $form);
453 $form->{all_units} = $all_units;
455 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
458 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
459 unless ($form->{employee_id}) {
460 $form->get_employee($dbh);
463 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
465 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
466 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
467 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
470 $query = qq|DELETE FROM custom_variables
471 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
472 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
473 do_query($form, $dbh, $query, $form->{id});
475 $query = qq|DELETE FROM shipto | .
476 qq|WHERE trans_id = ? AND module = 'OE'|;
477 do_query($form, $dbh, $query, $form->{id});
481 $query = qq|SELECT nextval('id')|;
482 ($form->{id}) = selectrow_query($form, $dbh, $query);
484 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
485 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
502 my @processed_orderitems;
504 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
505 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
508 for my $i (1 .. $form->{rowcount}) {
510 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
512 if ($form->{"id_$i"}) {
515 $query = qq|SELECT unit FROM parts WHERE id = ?|;
516 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
519 if (defined($all_units->{$item_unit}->{factor}) &&
520 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
521 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
523 my $baseqty = $form->{"qty_$i"} * $basefactor;
525 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
526 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
528 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
530 # keep entered selling price
532 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
534 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
536 my $decimalplaces = ($dec > 2) ? $dec : 2;
538 # undo discount formatting
539 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
542 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
544 # round linetotal at least to 2 decimal places
545 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
546 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
548 $form->{"inventory_accno_$i"} *= 1;
549 $form->{"expense_accno_$i"} *= 1;
551 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
555 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
557 if ($form->{taxincluded}) {
558 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
559 $taxbase = $linetotal - $taxamount;
561 # we are not keeping a natural price, do not round
562 $form->{"sellprice_$i"} =
563 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
565 $taxamount = $linetotal * $taxrate;
566 $taxbase = $linetotal;
569 if ($form->round_amount($taxrate, 7) == 0) {
570 if ($form->{taxincluded}) {
571 foreach my $item (@taxaccounts) {
572 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
573 $taxaccounts{$item} += $taxamount;
574 $taxdiff += $taxamount;
575 $taxbase{$item} += $taxbase;
577 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
579 foreach my $item (@taxaccounts) {
580 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
581 $taxbase{$item} += $taxbase;
585 foreach my $item (@taxaccounts) {
586 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
587 $taxbase{$item} += $taxbase;
591 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
593 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
595 # Get pricegroup_id and save it. Unfortunately the interface
596 # also uses ID "0" for signalling that none is selected, but "0"
597 # must not be stored in the database. Therefore we cannot simply
599 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
601 $pricegroup_id = undef if !$pricegroup_id;
603 # force new project, if not set yet
604 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
605 require SL::DB::Customer;
606 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
607 die "Can't find customer" unless $customer;
608 my $new_project = SL::DB::Project->new(
609 projectnumber => $form->{ordnumber},
610 description => $customer->name,
611 customer_id => $customer->id,
613 project_type_id => $::instance_conf->get_project_type_id,
614 project_status_id => $::instance_conf->get_project_status_id,
617 $form->{"globalproject_id"} = $new_project->id;
620 CVar->get_non_editable_ic_cvars(form => $form,
623 sub_module => 'orderitems',
624 may_converted_from => ['orderitems', 'invoice']);
628 # save detail record in orderitems table
629 if (! $form->{"orderitems_id_$i"}) {
630 $query = qq|SELECT nextval('orderitemsid')|;
631 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
633 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
634 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
637 my $orderitems_id = $form->{"orderitems_id_$i"};
638 push @processed_orderitems, $orderitems_id;
641 UPDATE orderitems SET
642 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
643 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
644 pricegroup_id = ?, subtotal = ?,
645 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
646 active_price_source = ?, active_discount_source = ?,
647 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
651 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
652 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
653 $form->{"qty_$i"}, $baseqty,
654 $fxsellprice, $form->{"discount_$i"},
655 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
656 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
657 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
658 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
659 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
660 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
661 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
662 conv_i($orderitems_id),
665 do_query($form, $dbh, $query, @values);
667 $form->{"sellprice_$i"} = $fxsellprice;
668 $form->{"discount_$i"} *= 100;
670 CVar->save_custom_variables(module => 'IC',
671 sub_module => 'orderitems',
672 trans_id => $orderitems_id,
673 configs => $ic_cvar_configs,
675 name_prefix => 'ic_',
676 name_postfix => "_$i",
679 # link previous items with orderitems
680 # assume we have a new workflow if we link from invoice or order to quotation
681 # unluckily orderitems are used for quotation and orders - therefore one more
682 # check to be sure NOT to link from order to quotation
683 foreach (qw(orderitems)) {
684 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
685 && $form->{type} !~ 'quotation') {
686 RecordLinks->create_links('dbh' => $dbh,
689 'from_ids' => $form->{"converted_from_${_}_id_$i"},
690 'to_table' => 'orderitems',
691 'to_id' => $orderitems_id,
694 delete $form->{"converted_from_${_}_id_$i"};
699 # search for orphaned ids
700 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
701 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
702 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
704 if (scalar @orphaned_ids) {
705 # clean up orderitems
706 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
707 do_query($form, $dbh, $query, @orphaned_ids);
710 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
714 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
716 $amount = $form->round_amount($netamount + $tax, 2, 1);
717 $netamount = $form->round_amount($netamount, 2);
719 if ($form->{currency} eq $form->{defaultcurrency}) {
720 $form->{exchangerate} = 1;
722 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
725 # from inputfield (exchangerate) or hidden (forex)
726 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
728 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
730 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
735 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
736 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
737 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
738 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
739 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
740 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
741 , order_probability = ?, expected_billing_date = ?
744 @values = ($form->{ordnumber} || '', $form->{quonumber},
745 $form->{cusordnumber}, conv_date($form->{transdate}),
746 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
747 $amount, $netamount, conv_date($reqdate),
748 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
749 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
750 $form->{currency}, $form->{closed} ? 't' : 'f',
751 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
752 $quotation, conv_i($form->{department_id}),
753 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
754 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
755 conv_i($form->{delivery_vendor_id}),
756 conv_i($form->{delivery_customer_id}),
757 conv_i($form->{delivery_term_id}),
758 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
759 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
760 $form->{transaction_description},
761 $form->{marge_total} * 1, $form->{marge_percent} * 1,
762 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
763 conv_i($form->{id}));
764 do_query($form, $dbh, $query, @values);
766 $form->new_lastmtime('oe');
768 $form->{ordtotal} = $amount;
770 $form->{name} = $form->{ $form->{vc} };
771 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
774 if (!$form->{shipto_id}) {
775 $form->add_shipto($dbh, $form->{id}, "OE");
778 # save printed, emailed, queued
779 $form->save_status($dbh);
781 # Link this record to the records it was created from.
782 $form->{convert_from_oe_ids} =~ s/^\s+//;
783 $form->{convert_from_oe_ids} =~ s/\s+$//;
784 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
785 delete $form->{convert_from_oe_ids};
786 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
787 RecordLinks->create_links('dbh' => $dbh,
789 'from_table' => 'oe',
790 'from_ids' => \@convert_from_oe_ids,
792 'to_id' => $form->{id},
794 $self->_close_quotations_rfqs('dbh' => $dbh,
795 'from_id' => \@convert_from_oe_ids,
796 'to_id' => $form->{id});
799 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
800 if ($form->{vc} eq 'customer') {
801 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
803 if ($form->{vc} eq 'vendor') {
804 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
808 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
809 "quonumber" : "ordnumber"};
811 Common::webdav_folder($form);
813 $self->save_periodic_invoices_config(dbh => $dbh,
814 oe_id => $form->{id},
815 config_yaml => $form->{periodic_invoices_config})
816 if ($form->{type} eq 'sales_order');
818 $main::lxdebug->leave_sub();
823 sub save_periodic_invoices_config {
824 my ($self, %params) = @_;
826 return if !$params{oe_id};
828 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
829 return if 'HASH' ne ref $config;
831 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
832 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
833 $obj->update_attributes(%{ $config });
836 sub load_periodic_invoice_config {
840 delete $form->{periodic_invoices_config};
843 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
846 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
847 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
848 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
853 sub _close_quotations_rfqs {
854 $main::lxdebug->enter_sub();
859 Common::check_params(\%params, qw(from_id to_id));
861 my $myconfig = \%main::myconfig;
862 my $form = $main::form;
864 my $dbh = $params{dbh} || SL::DB->client->dbh;
866 SL::DB->client->with_transaction(sub {
868 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
869 my $sth = prepare_query($form, $dbh, $query);
871 do_statement($form, $sth, $query, conv_i($params{to_id}));
873 my ($quotation) = $sth->fetchrow_array();
881 foreach my $from_id (@{ $params{from_id} }) {
882 $from_id = conv_i($from_id);
883 do_statement($form, $sth, $query, $from_id);
884 ($quotation) = $sth->fetchrow_array();
885 push @close_ids, $from_id if ($quotation);
890 if (scalar @close_ids) {
891 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
892 do_query($form, $dbh, $query, @close_ids);
895 }) or do { die SL::DB->client->error };
897 $main::lxdebug->leave_sub();
901 $main::lxdebug->enter_sub();
903 my ($self, $myconfig, $form) = @_;
905 my $rc = SL::DB::Order->new->db->with_transaction(sub {
906 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
908 SL::DB::Order->new(id => $form->{id})->delete;
910 my $spool = $::lx_office_conf{paths}->{spool};
911 unlink map { "$spool/$_" } @spoolfiles if $spool;
914 }) or do { die SL::DB->client->error };
916 $main::lxdebug->leave_sub();
922 my ($self, $myconfig, $form) = @_;
923 $main::lxdebug->enter_sub();
925 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
927 $::lxdebug->leave_sub;
932 my ($self, $myconfig, $form) = @_;
934 # connect to database
935 my $dbh = SL::DB->client->dbh;
937 my ($query, $query_add, @values, @ids, $sth);
939 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
941 push @ids, $form->{"trans_id_$_"}
942 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
943 } (1 .. $form->{"rowcount"});
945 if ($form->{rowcount} && scalar @ids) {
946 $form->{convert_from_oe_ids} = join ' ', @ids;
949 # if called in multi id mode, and still only got one id, switch back to single id
950 if ($form->{"rowcount"} and $#ids == 0) {
951 $form->{"id"} = $ids[0];
953 delete $form->{convert_from_oe_ids};
956 # and remember for the rest of the function
957 my $is_collective_order = scalar @ids;
959 # If collective order was created from exactly 1 order, we assume the same
960 # behaviour as a "save as new" from within an order is actually desired, i.e.
961 # the original order isn't part of a workflow where we want to remember
962 # record_links, but simply a quick way of generating a new order from an old
963 # one without having to enter everything again.
964 # Setting useasnew will prevent the creation of record_links for the items
965 # when saving the new order.
966 # This form variable is probably not necessary, could just set saveasnew instead
967 $form->{useasnew} = 1 if $is_collective_order == 1;
970 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
971 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
972 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
973 $form->{transdate} = DateTime->today_local->to_kivitendo;
976 # get default accounts
977 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
978 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
979 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
980 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
981 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
982 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
983 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
986 my $ref = selectfirst_hashref_query($form, $dbh, $query);
987 map { $form->{$_} = $ref->{$_} } keys %$ref;
989 $form->{currency} = $form->get_default_currency($myconfig);
991 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
992 # we come from invoices, feel free.
993 $form->{reqdate} = $form->{deliverydate}
994 if ( $form->{deliverydate}
995 and $form->{callback} =~ /action=ar_transactions/);
997 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
999 if ($form->{id} or @ids) {
1001 # retrieve order for single id
1002 # NOTE: this query is intended to fetch all information only ONCE.
1003 # so if any of these infos is important (or even different) for any item,
1004 # it will be killed out and then has to be fetched from the item scope query further down
1006 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1007 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1008 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1009 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1010 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1012 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1013 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1014 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1015 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1017 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1018 LEFT JOIN employee e ON (o.employee_id = e.id)
1019 LEFT JOIN department d ON (o.department_id = d.id) | .
1022 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1024 @values = $form->{id} ? ($form->{id}) : @ids;
1025 $sth = prepare_execute_query($form, $dbh, $query, @values);
1027 $ref = $sth->fetchrow_hashref("NAME_lc");
1030 map { $form->{$_} = $ref->{$_} } keys %$ref;
1032 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1034 # set all entries for multiple ids blank that yield different information
1035 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1036 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1039 $form->{mtime} ||= $form->{itime};
1040 $form->{lastmtime} = $form->{mtime};
1042 # if not given, fill transdate with current_date
1043 $form->{transdate} = $form->current_date($myconfig)
1044 unless $form->{transdate};
1048 if ($form->{delivery_customer_id}) {
1049 $query = qq|SELECT name FROM customer WHERE id = ?|;
1050 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1053 if ($form->{delivery_vendor_id}) {
1054 $query = qq|SELECT name FROM customer WHERE id = ?|;
1055 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1058 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1060 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1061 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1063 $ref = $sth->fetchrow_hashref("NAME_lc");
1064 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1067 if ($ref->{shipto_id}) {
1068 my $cvars = CVar->get_custom_variables(
1071 trans_id => $ref->{shipto_id},
1073 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1076 # get printed, emailed and queued
1077 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1078 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1080 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1081 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1082 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1083 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1086 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1089 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1091 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1092 unshift @values, ($form->{taxzone_id}) x 2;
1094 # retrieve individual items
1095 # this query looks up all information about the items
1096 # stuff different from the whole will not be overwritten, but saved with a suffix.
1098 qq|SELECT o.id AS orderitems_id,
1099 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1100 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1101 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1102 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1103 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1104 p.classification_id,
1105 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1106 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1107 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1108 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1109 pr.projectnumber, p.formel,
1110 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1112 JOIN parts p ON (o.parts_id = p.id)
1113 JOIN oe ON (o.trans_id = oe.id)
1114 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1115 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)
1116 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)
1117 LEFT JOIN project pr ON (o.project_id = pr.id)
1118 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1120 ? qq|WHERE o.trans_id = ?|
1121 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1122 qq|ORDER BY o.trans_id, o.position|;
1124 @ids = $form->{id} ? ($form->{id}) : @ids;
1125 $sth = prepare_execute_query($form, $dbh, $query, @values);
1127 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1128 # Retrieve custom variables.
1129 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1131 sub_module => 'orderitems',
1132 trans_id => $ref->{orderitems_id},
1134 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1137 if (!$ref->{"part_type"} eq 'part') {
1138 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1140 # delete($ref->{"part_inventory_accno_id"});
1142 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1143 # unless already present there
1144 # remove _oe entries afterwards
1145 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1146 qw|ordnumber transdate cusordnumber|
1148 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1152 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1154 qq|SELECT accno AS inventory_accno, | .
1155 qq| new_chart_id AS inventory_new_chart, | .
1156 qq| date($transdate) - valid_from AS inventory_valid | .
1157 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1158 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1159 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1162 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1164 qq|SELECT accno AS income_accno, | .
1165 qq| new_chart_id AS income_new_chart, | .
1166 qq| date($transdate) - valid_from AS income_valid | .
1167 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1168 ($ref->{income_accno}, $ref->{income_new_chart},
1169 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1172 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1174 qq|SELECT accno AS expense_accno, | .
1175 qq| new_chart_id AS expense_new_chart, | .
1176 qq| date($transdate) - valid_from AS expense_valid | .
1177 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1178 ($ref->{expense_accno}, $ref->{expense_new_chart},
1179 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1182 # delete orderitems_id in collective orders, so that they get cloned no matter what
1183 # is this correct? or is the following meant?
1184 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1185 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1187 # get tax rates and description
1188 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1190 qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
1192 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1193 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1194 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1195 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1196 qq|ORDER BY c.accno|;
1197 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1198 $ref->{taxaccounts} = "";
1200 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1201 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1205 $ref->{taxaccounts} .= "$ptr->{accno} ";
1206 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1207 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1208 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1209 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1210 $form->{taxaccounts} .= "$ptr->{accno} ";
1215 chop $ref->{taxaccounts};
1217 push @{ $form->{form_details} }, $ref;
1224 # get last name used
1225 $form->lastname_used($dbh, $myconfig, $form->{vc})
1226 unless $form->{"$form->{vc}_id"};
1230 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1232 Common::webdav_folder($form);
1234 $self->load_periodic_invoice_config($form);
1239 sub retrieve_simple {
1240 $main::lxdebug->enter_sub();
1245 Common::check_params(\%params, qw(id));
1247 my $myconfig = \%main::myconfig;
1248 my $form = $main::form;
1250 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1252 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1253 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1255 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1256 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1258 $main::lxdebug->leave_sub();
1264 $main::lxdebug->enter_sub();
1266 my ($self, $myconfig, $form) = @_;
1268 # connect to database
1269 my $dbh = SL::DB->client->dbh;
1275 my $nodiscount_subtotal = 0;
1276 my $discount_subtotal = 0;
1279 my @partsgroup = ();
1282 my $subtotal_header = 0;
1283 my $subposition = 0;
1291 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1293 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1296 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1297 $price_factors{$pfac->{id}} = $pfac;
1298 $pfac->{factor} *= 1;
1299 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1302 # sort items by partsgroup
1303 for $i (1 .. $form->{rowcount}) {
1305 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1306 $partsgroup = $form->{"partsgroup_$i"};
1308 push @partsgroup, [$i, $partsgroup];
1309 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1315 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1316 %projects_by_id = map { $_->id => $_ } @$projects;
1319 if ($projects_by_id{$form->{"globalproject_id"}}) {
1320 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1321 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1323 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1324 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1328 $form->{discount} = [];
1330 # get some values of parts from db on store them in extra array,
1331 # so that they can be sorted in later
1332 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1333 my @prepared_arrays = keys %prepared_template_arrays;
1334 my @separate_totals = qw(non_separate_subtotal);
1336 $form->{TEMPLATE_ARRAYS} = { };
1338 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1339 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1342 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1343 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1344 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1345 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1346 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1348 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1349 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1351 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1353 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1355 my $totalweight = 0;
1357 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1360 if ($item->[1] ne $sameitem) {
1361 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1362 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1363 $sameitem = $item->[1];
1365 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1368 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1370 if ($form->{"id_$i"} != 0) {
1372 # add number, description and qty to $form->{number}, ....
1374 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1375 $subtotal_header = $i;
1376 $position = int($position);
1379 } elsif ($subtotal_header) {
1381 $position = int($position);
1382 $position = $position.".".$subposition;
1384 $position = int($position);
1388 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1390 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1392 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1393 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1394 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1395 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1396 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1397 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1398 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1399 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1400 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1401 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1402 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1403 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1404 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1405 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1406 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1407 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1408 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1409 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1410 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1411 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1412 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1414 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1415 my ($dec) = ($sellprice =~ /\.(\d+)/);
1416 my $decimalplaces = max 2, length($dec);
1418 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1420 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1421 my $linetotal = $form->round_amount($linetotal_exact, 2);
1423 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1424 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1426 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1428 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1430 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1432 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1433 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1435 $linetotal = ($linetotal != 0) ? $linetotal : '';
1437 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1438 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1439 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1441 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1442 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1443 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1444 push @separate_totals , "separate_${pabbr}_subtotal";
1445 $form->{"separate_${pabbr}_subtotal"} = 0;
1447 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1449 $form->{non_separate_subtotal} += $linetotal;
1452 $form->{ordtotal} += $linetotal;
1453 $form->{nodiscount_total} += $nodiscount_linetotal;
1454 $form->{discount_total} += $discount;
1456 if ($subtotal_header) {
1457 $discount_subtotal += $linetotal;
1458 $nodiscount_subtotal += $nodiscount_linetotal;
1461 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1462 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1463 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1464 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1465 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1467 $discount_subtotal = 0;
1468 $nodiscount_subtotal = 0;
1469 $subtotal_header = 0;
1472 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1475 if (!$form->{"discount_$i"}) {
1476 $nodiscount += $linetotal;
1479 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1481 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1482 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1483 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1484 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1485 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1486 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1488 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1489 $totalweight += $lineweight;
1490 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1491 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1492 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1493 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1495 my ($taxamount, $taxbase);
1498 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1500 if ($form->{taxincluded}) {
1503 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1504 $taxbase = $linetotal / (1 + $taxrate);
1506 $taxamount = $linetotal * $taxrate;
1507 $taxbase = $linetotal;
1510 if ($taxamount != 0) {
1511 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1512 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1513 $taxbase{$accno} += $taxbase;
1517 $tax_rate = $taxrate * 100;
1518 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1520 if ($form->{"part_type_$i"} eq 'assembly') {
1523 # get parts and push them onto the stack
1525 if ($form->{groupitems}) {
1526 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1528 $sortorder = qq|ORDER BY a.oid|;
1531 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1532 qq|pg.partsgroup | .
1533 qq|FROM assembly a | .
1534 qq| JOIN parts p ON (a.parts_id = p.id) | .
1535 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1536 qq| WHERE a.bom = '1' | .
1537 qq| AND a.id = ? | . $sortorder;
1538 @values = ($form->{"id_$i"});
1539 $sth = $dbh->prepare($query);
1540 $sth->execute(@values) || $form->dberror($query);
1542 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1543 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1544 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1545 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1546 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1547 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1550 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1551 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1552 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1557 CVar->get_non_editable_ic_cvars(form => $form,
1560 sub_module => 'orderitems',
1561 may_converted_from => ['orderitems', 'invoice']);
1563 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1564 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1565 for @{ $ic_cvar_configs };
1567 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1571 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1572 $form->{totalweight_nofmt} = $totalweight;
1573 my $defaults = AM->get_defaults();
1574 $form->{weightunit} = $defaults->{weightunit};
1577 foreach $item (sort keys %taxaccounts) {
1578 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1580 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1581 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1582 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1583 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1584 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1585 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1586 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1588 my $tax_objs = SL::DB::Manager::Tax->get_objects_from_sql(
1589 sql => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
1590 args => [ $form->{"${item}_taxnumber"} ]
1594 $tax_obj = $tax_objs->[0];
1596 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1597 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1600 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1601 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1602 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1603 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1605 if($form->{taxincluded}) {
1606 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1607 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1609 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1610 $form->{subtotal_nofmt} = $form->{ordtotal};
1613 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1614 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1615 $form->{rounding} = $form->round_amount(
1616 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1621 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1622 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1624 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1626 $form->{username} = $myconfig->{name};
1628 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1629 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1630 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1632 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1633 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1635 $main::lxdebug->leave_sub();
1644 OE.pm - Order entry module
1648 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>.
1654 =item retrieve_simple PARAMS
1656 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1658 my $order = retrieve_simple(id => 2);