1 #====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1999-2003
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
33 #======================================================================
37 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
46 use SL::DB::ProjectType;
50 use SL::HTML::Restrict;
53 use SL::Util qw(trim);
59 $main::lxdebug->enter_sub();
61 my ($self, $myconfig, $form) = @_;
64 my $dbh = $form->get_standard_dbh;
67 my $ordnumber = 'ordnumber';
73 my ($periodic_invoices_columns, $periodic_invoices_joins);
75 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
77 if ($form->{type} =~ /_quotation$/) {
79 $ordnumber = 'quonumber';
81 } elsif ($form->{type} eq 'sales_order') {
82 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
83 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
86 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
90 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
92 SELECT from_id, ar.amount, ar.netamount FROM (
95 WHERE from_table = 'oe' AND to_table = 'ar'
97 SELECT rl1.from_id, rl2.to_id
99 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
100 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
102 LEFT JOIN ar ON ar.id = rl.to_id
104 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
105 $billed_amount{ $ref->{from_id}} += $ref->{amount};
106 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
111 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
112 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
113 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
114 qq| o.transaction_description, | .
115 qq| o.marge_total, o.marge_percent, | .
116 qq| o.itime::DATE AS insertdate, | .
117 qq| ex.$rate AS exchangerate, | .
118 qq| pt.description AS payment_terms, | .
119 qq| pr.projectnumber AS globalprojectnumber, | .
120 qq| e.name AS employee, s.name AS salesman, | .
121 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
122 qq| tz.description AS taxzone | .
123 $periodic_invoices_columns .
124 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
126 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
127 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
128 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
129 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
130 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
131 qq| AND ex.transdate = o.transdate) | .
132 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
133 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
134 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
135 qq|$periodic_invoices_joins | .
136 qq|WHERE (o.quotation = ?) |;
137 push(@values, $quotation);
139 my ($null, $split_department_id) = split /--/, $form->{department};
140 my $department_id = $form->{department_id} || $split_department_id;
141 if ($department_id) {
142 $query .= qq| AND o.department_id = ?|;
143 push(@values, $department_id);
146 if ($form->{"project_id"}) {
148 qq|AND ((globalproject_id = ?) OR EXISTS | .
149 qq| (SELECT * FROM orderitems oi | .
150 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
151 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
154 if ($form->{"projectnumber"}) {
156 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
157 SELECT * FROM orderitems oi
158 LEFT JOIN project proi ON proi.id = oi.project_id
159 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
162 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
165 if ($form->{"business_id"}) {
166 $query .= " AND ct.business_id = ?";
167 push(@values, $form->{"business_id"});
170 if ($form->{"${vc}_id"}) {
171 $query .= " AND o.${vc}_id = ?";
172 push(@values, $form->{"${vc}_id"});
174 } elsif ($form->{$vc}) {
175 $query .= " AND ct.name ILIKE ?";
176 push(@values, like($form->{$vc}));
179 if ($form->{"cp_name"}) {
180 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
181 push(@values, (like($form->{"cp_name"}))x2);
184 if (!$main::auth->assert('sales_all_edit', 1)) {
185 $query .= " AND o.employee_id = (select id from employee where login= ?)";
186 push @values, $::myconfig{login};
188 if ($form->{employee_id}) {
189 $query .= " AND o.employee_id = ?";
190 push @values, conv_i($form->{employee_id});
193 if ($form->{salesman_id}) {
194 $query .= " AND o.salesman_id = ?";
195 push @values, conv_i($form->{salesman_id});
198 if (!$form->{open} && !$form->{closed}) {
199 $query .= " AND o.id = 0";
200 } elsif (!($form->{open} && $form->{closed})) {
201 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
204 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
205 ($form->{"notdelivered"} ne $form->{"delivered"})) {
206 $query .= $form->{"delivered"} ?
207 " AND o.delivered " : " AND NOT o.delivered";
210 if ($form->{$ordnumber}) {
211 $query .= qq| AND o.$ordnumber ILIKE ?|;
212 push(@values, like($form->{$ordnumber}));
215 if ($form->{cusordnumber}) {
216 $query .= qq| AND o.cusordnumber ILIKE ?|;
217 push(@values, like($form->{cusordnumber}));
220 if($form->{transdatefrom}) {
221 $query .= qq| AND o.transdate >= ?|;
222 push(@values, conv_date($form->{transdatefrom}));
225 if($form->{transdateto}) {
226 $query .= qq| AND o.transdate <= ?|;
227 push(@values, conv_date($form->{transdateto}));
230 if($form->{reqdatefrom}) {
231 $query .= qq| AND o.reqdate >= ?|;
232 push(@values, conv_date($form->{reqdatefrom}));
235 if($form->{reqdateto}) {
236 $query .= qq| AND o.reqdate <= ?|;
237 push(@values, conv_date($form->{reqdateto}));
240 if($form->{insertdatefrom}) {
241 $query .= qq| AND o.itime::DATE >= ?|;
242 push(@values, conv_date($form->{insertdatefrom}));
245 if($form->{insertdateto}) {
246 $query .= qq| AND o.itime::DATE <= ?|;
247 push(@values, conv_date($form->{insertdateto}));
250 if ($form->{shippingpoint}) {
251 $query .= qq| AND o.shippingpoint ILIKE ?|;
252 push(@values, like($form->{shippingpoint}));
255 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
256 $query .= qq| AND tz.id = ?|;
257 push(@values, $form->{taxzone_id});
260 if ($form->{transaction_description}) {
261 $query .= qq| AND o.transaction_description ILIKE ?|;
262 push(@values, like($form->{transaction_description}));
265 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
266 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
267 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
270 if ($form->{reqdate_unset_or_old}) {
271 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
274 if (($form->{order_probability_value} || '') ne '') {
275 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
276 $query .= qq| AND (o.order_probability ${op} ?)|;
277 push @values, trim($form->{order_probability_value});
280 if ($form->{expected_billing_date_from}) {
281 $query .= qq| AND (o.expected_billing_date >= ?)|;
282 push @values, conv_date($form->{expected_billing_date_from});
285 if ($form->{expected_billing_date_to}) {
286 $query .= qq| AND (o.expected_billing_date <= ?)|;
287 push @values, conv_date($form->{expected_billing_date_to});
290 if ($form->{parts_partnumber}) {
293 SELECT orderitems.trans_id
295 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
296 WHERE (orderitems.trans_id = o.id)
297 AND (parts.partnumber ILIKE ?)
301 push @values, like($form->{parts_partnumber});
304 if ($form->{parts_description}) {
307 SELECT orderitems.trans_id
309 WHERE (orderitems.trans_id = o.id)
310 AND (orderitems.description ILIKE ?)
314 push @values, like($form->{parts_description});
318 my @tokens = parse_line('\s+', 0, $form->{all});
319 # ordnumber quonumber customer.name vendor.name transaction_description
321 o.ordnumber ILIKE ? OR
322 o.quonumber ILIKE ? OR
324 o.transaction_description ILIKE ?
326 push @values, (like($_))x4 for @tokens;
329 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
330 'trans_id_field' => 'ct.id',
334 $query .= qq| AND ($cvar_where)|;
335 push @values, @cvar_values;
338 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
339 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
340 my %allowed_sort_columns = (
341 "transdate" => "o.transdate",
342 "reqdate" => "o.reqdate",
344 "ordnumber" => "o.ordnumber",
345 "cusordnumber" => "o.cusordnumber",
346 "quonumber" => "o.quonumber",
348 "employee" => "e.name",
349 "salesman" => "s.name",
350 "shipvia" => "o.shipvia",
351 "transaction_description" => "o.transaction_description",
352 "shippingpoint" => "o.shippingpoint",
353 "insertdate" => "o.itime",
354 "taxzone" => "tz.description",
355 "payment_terms" => "pt.description",
357 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
358 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
360 $query .= qq| ORDER by | . $sortorder;
362 my $sth = $dbh->prepare($query);
363 $sth->execute(@values) ||
364 $form->dberror($query . " (" . join(", ", @values) . ")");
368 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
369 $ref->{billed_amount} = $billed_amount{$ref->{id}};
370 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
371 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
372 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
373 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
374 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
375 $id{ $ref->{id} } = $ref->{id};
380 $main::lxdebug->leave_sub();
383 sub transactions_for_todo_list {
384 $main::lxdebug->enter_sub();
389 my $myconfig = \%main::myconfig;
390 my $form = $main::form;
392 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
394 my $query = qq|SELECT id FROM employee WHERE login = ?|;
395 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
398 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
399 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
404 LEFT JOIN customer c ON (oe.customer_id = c.id)
405 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
406 LEFT JOIN employee e ON (oe.employee_id = e.id)
407 WHERE (COALESCE(quotation, FALSE) = TRUE)
408 AND (COALESCE(closed, FALSE) = FALSE)
409 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
410 AND NOT (oe.reqdate ISNULL)
411 AND (oe.reqdate < current_date)
414 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
416 $main::lxdebug->leave_sub();
422 $main::lxdebug->enter_sub();
424 my ($self, $myconfig, $form) = @_;
426 # connect to database, turn off autocommit
427 my $dbh = $form->get_standard_dbh;
428 my $restricter = SL::HTML::Restrict->create;
430 my ($query, @values, $sth, $null);
431 my $exchangerate = 0;
433 my $all_units = AM->retrieve_units($myconfig, $form);
434 $form->{all_units} = $all_units;
436 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
439 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
440 unless ($form->{employee_id}) {
441 $form->get_employee($dbh);
444 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
446 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
447 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
448 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
451 $query = qq|DELETE FROM custom_variables
452 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
453 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
454 do_query($form, $dbh, $query, $form->{id});
456 $query = qq|DELETE FROM shipto | .
457 qq|WHERE trans_id = ? AND module = 'OE'|;
458 do_query($form, $dbh, $query, $form->{id});
462 $query = qq|SELECT nextval('id')|;
463 ($form->{id}) = selectrow_query($form, $dbh, $query);
465 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
466 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
483 my @processed_orderitems;
485 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
486 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
489 for my $i (1 .. $form->{rowcount}) {
491 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
493 if ($form->{"id_$i"}) {
496 $query = qq|SELECT unit FROM parts WHERE id = ?|;
497 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
500 if (defined($all_units->{$item_unit}->{factor}) &&
501 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
502 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
504 my $baseqty = $form->{"qty_$i"} * $basefactor;
506 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
507 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
509 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
511 # keep entered selling price
513 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
515 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
517 my $decimalplaces = ($dec > 2) ? $dec : 2;
519 # undo discount formatting
520 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
523 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
525 # round linetotal at least to 2 decimal places
526 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
527 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
529 $form->{"inventory_accno_$i"} *= 1;
530 $form->{"expense_accno_$i"} *= 1;
532 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
536 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
538 if ($form->{taxincluded}) {
539 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
540 $taxbase = $linetotal - $taxamount;
542 # we are not keeping a natural price, do not round
543 $form->{"sellprice_$i"} =
544 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
546 $taxamount = $linetotal * $taxrate;
547 $taxbase = $linetotal;
550 if ($form->round_amount($taxrate, 7) == 0) {
551 if ($form->{taxincluded}) {
552 foreach my $item (@taxaccounts) {
553 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
554 $taxaccounts{$item} += $taxamount;
555 $taxdiff += $taxamount;
556 $taxbase{$item} += $taxbase;
558 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
560 foreach my $item (@taxaccounts) {
561 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
562 $taxbase{$item} += $taxbase;
566 foreach my $item (@taxaccounts) {
567 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
568 $taxbase{$item} += $taxbase;
572 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
574 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
576 # Get pricegroup_id and save it. Unfortunately the interface
577 # also uses ID "0" for signalling that none is selected, but "0"
578 # must not be stored in the database. Therefore we cannot simply
580 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
582 $pricegroup_id = undef if !$pricegroup_id;
584 # force new project, if not set yet
585 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
586 require SL::DB::Customer;
587 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
588 die "Can't find customer" unless $customer;
589 my $new_project = SL::DB::Project->new(
590 projectnumber => $form->{ordnumber},
591 description => $customer->name,
592 customer_id => $customer->id,
594 project_type_id => $::instance_conf->get_project_type_id,
595 project_status_id => $::instance_conf->get_project_status_id,
598 $form->{"globalproject_id"} = $new_project->id;
601 CVar->get_non_editable_ic_cvars(form => $form,
604 sub_module => 'orderitems',
605 may_converted_from => ['orderitems', 'invoice']);
609 # save detail record in orderitems table
610 if (! $form->{"orderitems_id_$i"}) {
611 $query = qq|SELECT nextval('orderitemsid')|;
612 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
614 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
615 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
618 my $orderitems_id = $form->{"orderitems_id_$i"};
619 push @processed_orderitems, $orderitems_id;
622 UPDATE orderitems SET
623 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
624 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
625 pricegroup_id = ?, subtotal = ?,
626 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
627 active_price_source = ?, active_discount_source = ?,
628 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
632 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
633 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
634 $form->{"qty_$i"}, $baseqty,
635 $fxsellprice, $form->{"discount_$i"},
636 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
637 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
638 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
639 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
640 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
641 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
642 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
643 conv_i($orderitems_id),
646 do_query($form, $dbh, $query, @values);
648 $form->{"sellprice_$i"} = $fxsellprice;
649 $form->{"discount_$i"} *= 100;
651 CVar->save_custom_variables(module => 'IC',
652 sub_module => 'orderitems',
653 trans_id => $orderitems_id,
654 configs => $ic_cvar_configs,
656 name_prefix => 'ic_',
657 name_postfix => "_$i",
660 # link previous items with orderitems
661 foreach (qw(orderitems invoice)) {
662 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
663 RecordLinks->create_links('dbh' => $dbh,
666 'from_ids' => $form->{"converted_from_${_}_id_$i"},
667 'to_table' => 'orderitems',
668 'to_id' => $orderitems_id,
671 delete $form->{"converted_from_${_}_id_$i"};
676 # search for orphaned ids
677 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
678 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
679 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
681 if (scalar @orphaned_ids) {
682 # clean up orderitems
683 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
684 do_query($form, $dbh, $query, @orphaned_ids);
687 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
691 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
693 $amount = $form->round_amount($netamount + $tax, 2, 1);
694 $netamount = $form->round_amount($netamount, 2);
696 if ($form->{currency} eq $form->{defaultcurrency}) {
697 $form->{exchangerate} = 1;
699 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
702 # from inputfield (exchangerate) or hidden (forex)
703 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
705 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
707 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
709 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
714 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
715 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
716 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
717 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
718 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
719 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
720 , order_probability = ?, expected_billing_date = ?
723 @values = ($form->{ordnumber} || '', $form->{quonumber},
724 $form->{cusordnumber}, conv_date($form->{transdate}),
725 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
726 $amount, $netamount, conv_date($reqdate),
727 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
728 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
729 $form->{currency}, $form->{closed} ? 't' : 'f',
730 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
731 $quotation, conv_i($form->{department_id}),
732 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
733 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
734 conv_i($form->{delivery_vendor_id}),
735 conv_i($form->{delivery_customer_id}),
736 conv_i($form->{delivery_term_id}),
737 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
738 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
739 $form->{transaction_description},
740 $form->{marge_total} * 1, $form->{marge_percent} * 1,
741 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
742 conv_i($form->{id}));
743 do_query($form, $dbh, $query, @values);
745 $form->new_lastmtime('oe');
747 $form->{ordtotal} = $amount;
749 $form->{name} = $form->{ $form->{vc} };
750 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
753 if (!$form->{shipto_id}) {
754 $form->add_shipto($dbh, $form->{id}, "OE");
757 # save printed, emailed, queued
758 $form->save_status($dbh);
760 # Link this record to the records it was created from.
761 $form->{convert_from_oe_ids} =~ s/^\s+//;
762 $form->{convert_from_oe_ids} =~ s/\s+$//;
763 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
764 delete $form->{convert_from_oe_ids};
765 if (scalar @convert_from_oe_ids) {
766 RecordLinks->create_links('dbh' => $dbh,
768 'from_table' => 'oe',
769 'from_ids' => \@convert_from_oe_ids,
771 'to_id' => $form->{id},
773 $self->_close_quotations_rfqs('dbh' => $dbh,
774 'from_id' => \@convert_from_oe_ids,
775 'to_id' => $form->{id});
778 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
779 if ($form->{vc} eq 'customer') {
780 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
782 if ($form->{vc} eq 'vendor') {
783 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
787 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
788 "quonumber" : "ordnumber"};
790 Common::webdav_folder($form);
792 my $rc = $dbh->commit;
794 $self->save_periodic_invoices_config(dbh => $dbh,
795 oe_id => $form->{id},
796 config_yaml => $form->{periodic_invoices_config})
797 if ($form->{type} eq 'sales_order');
799 $main::lxdebug->leave_sub();
804 sub save_periodic_invoices_config {
805 my ($self, %params) = @_;
807 return if !$params{oe_id};
809 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
810 return if 'HASH' ne ref $config;
812 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
813 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
814 $obj->update_attributes(%{ $config });
817 sub load_periodic_invoice_config {
821 delete $form->{periodic_invoices_config};
824 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
827 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
828 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
829 $form->{periodic_invoices_config} = YAML::Dump($config);
834 sub _close_quotations_rfqs {
835 $main::lxdebug->enter_sub();
840 Common::check_params(\%params, qw(from_id to_id));
842 my $myconfig = \%main::myconfig;
843 my $form = $main::form;
845 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
847 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
848 my $sth = prepare_query($form, $dbh, $query);
850 do_statement($form, $sth, $query, conv_i($params{to_id}));
852 my ($quotation) = $sth->fetchrow_array();
855 $main::lxdebug->leave_sub();
861 foreach my $from_id (@{ $params{from_id} }) {
862 $from_id = conv_i($from_id);
863 do_statement($form, $sth, $query, $from_id);
864 ($quotation) = $sth->fetchrow_array();
865 push @close_ids, $from_id if ($quotation);
870 if (scalar @close_ids) {
871 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
872 do_query($form, $dbh, $query, @close_ids);
874 $dbh->commit() unless ($params{dbh});
877 $main::lxdebug->leave_sub();
881 $main::lxdebug->enter_sub();
883 my ($self, $myconfig, $form) = @_;
885 my $rc = SL::DB::Order->new->db->with_transaction(sub {
886 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
888 SL::DB::Order->new(id => $form->{id})->delete;
890 my $spool = $::lx_office_conf{paths}->{spool};
891 unlink map { "$spool/$_" } @spoolfiles if $spool;
896 $main::lxdebug->leave_sub();
902 $main::lxdebug->enter_sub();
904 my ($self, $myconfig, $form) = @_;
906 # connect to database
907 my $dbh = $form->get_standard_dbh;
909 my ($query, $query_add, @values, @ids, $sth);
911 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
913 push @ids, $form->{"trans_id_$_"}
914 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
915 } (1 .. $form->{"rowcount"});
917 if ($form->{rowcount} && scalar @ids) {
918 $form->{convert_from_oe_ids} = join ' ', @ids;
921 # if called in multi id mode, and still only got one id, switch back to single id
922 if ($form->{"rowcount"} and $#ids == 0) {
923 $form->{"id"} = $ids[0];
925 delete $form->{convert_from_oe_ids};
928 # and remember for the rest of the function
929 my $is_collective_order = scalar @ids;
931 # If collective order was created from exactly 1 order, we assume the same
932 # behaviour as a "save as new" from within an order is actually desired, i.e.
933 # the original order isn't part of a workflow where we want to remember
934 # record_links, but simply a quick way of generating a new order from an old
935 # one without having to enter everything again.
936 # Setting useasnew will prevent the creation of record_links for the items
937 # when saving the new order.
938 # This form variable is probably not necessary, could just set saveasnew instead
939 $form->{useasnew} = 1 if $is_collective_order == 1;
942 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
943 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
944 $form->{transdate} = DateTime->today_local->to_kivitendo;
947 # get default accounts
948 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
949 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
950 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
951 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
952 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
953 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
954 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
957 my $ref = selectfirst_hashref_query($form, $dbh, $query);
958 map { $form->{$_} = $ref->{$_} } keys %$ref;
960 $form->{currency} = $form->get_default_currency($myconfig);
962 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
963 # we come from invoices, feel free.
964 $form->{reqdate} = $form->{deliverydate}
965 if ( $form->{deliverydate}
966 and $form->{callback} =~ /action=ar_transactions/);
968 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
970 if ($form->{id} or @ids) {
972 # retrieve order for single id
973 # NOTE: this query is intended to fetch all information only ONCE.
974 # so if any of these infos is important (or even different) for any item,
975 # it will be killed out and then has to be fetched from the item scope query further down
977 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
978 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
979 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
980 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
981 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
983 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
984 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
985 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
986 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
988 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
989 LEFT JOIN employee e ON (o.employee_id = e.id)
990 LEFT JOIN department d ON (o.department_id = d.id) | .
993 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
995 @values = $form->{id} ? ($form->{id}) : @ids;
996 $sth = prepare_execute_query($form, $dbh, $query, @values);
998 $ref = $sth->fetchrow_hashref("NAME_lc");
1001 map { $form->{$_} = $ref->{$_} } keys %$ref;
1003 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1005 # set all entries for multiple ids blank that yield different information
1006 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1007 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1010 $form->{mtime} ||= $form->{itime};
1011 $form->{lastmtime} = $form->{mtime};
1013 # if not given, fill transdate with current_date
1014 $form->{transdate} = $form->current_date($myconfig)
1015 unless $form->{transdate};
1019 if ($form->{delivery_customer_id}) {
1020 $query = qq|SELECT name FROM customer WHERE id = ?|;
1021 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1024 if ($form->{delivery_vendor_id}) {
1025 $query = qq|SELECT name FROM customer WHERE id = ?|;
1026 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1029 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1031 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1032 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1034 $ref = $sth->fetchrow_hashref("NAME_lc");
1036 map { $form->{$_} = $ref->{$_} } keys %$ref;
1039 if ($form->{shipto_id}) {
1040 my $cvars = CVar->get_custom_variables(
1043 trans_id => $form->{shipto_id},
1045 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1048 # get printed, emailed and queued
1049 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1050 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1052 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1053 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1054 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1055 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1058 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1061 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1063 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1065 # retrieve individual items
1066 # this query looks up all information about the items
1067 # stuff different from the whole will not be overwritten, but saved with a suffix.
1069 qq|SELECT o.id AS orderitems_id,
1070 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1071 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1072 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1073 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1074 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1075 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1076 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1077 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1078 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1079 pr.projectnumber, p.formel,
1080 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1082 JOIN parts p ON (o.parts_id = p.id)
1083 JOIN oe ON (o.trans_id = oe.id)
1084 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1085 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
1086 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
1087 LEFT JOIN project pr ON (o.project_id = pr.id)
1088 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1090 ? qq|WHERE o.trans_id = ?|
1091 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1092 qq|ORDER BY o.trans_id, o.position|;
1094 @ids = $form->{id} ? ($form->{id}) : @ids;
1095 $sth = prepare_execute_query($form, $dbh, $query, @values);
1097 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1098 # Retrieve custom variables.
1099 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1101 sub_module => 'orderitems',
1102 trans_id => $ref->{orderitems_id},
1104 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1107 if (!$ref->{"part_inventory_accno_id"}) {
1108 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1110 delete($ref->{"part_inventory_accno_id"});
1112 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1113 # unless already present there
1114 # remove _oe entries afterwards
1115 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1116 qw|ordnumber transdate cusordnumber|
1118 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1122 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1124 qq|SELECT accno AS inventory_accno, | .
1125 qq| new_chart_id AS inventory_new_chart, | .
1126 qq| date($transdate) - valid_from AS inventory_valid | .
1127 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1128 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1129 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1132 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1134 qq|SELECT accno AS income_accno, | .
1135 qq| new_chart_id AS income_new_chart, | .
1136 qq| date($transdate) - valid_from AS income_valid | .
1137 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1138 ($ref->{income_accno}, $ref->{income_new_chart},
1139 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1142 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1144 qq|SELECT accno AS expense_accno, | .
1145 qq| new_chart_id AS expense_new_chart, | .
1146 qq| date($transdate) - valid_from AS expense_valid | .
1147 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1148 ($ref->{expense_accno}, $ref->{expense_new_chart},
1149 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1152 # delete orderitems_id in collective orders, so that they get cloned no matter what
1153 # is this correct? or is the following meant?
1154 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1155 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1157 # get tax rates and description
1158 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1160 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1161 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1162 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1163 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1164 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1165 qq|ORDER BY c.accno|;
1166 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1167 $ref->{taxaccounts} = "";
1169 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1170 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1174 $ref->{taxaccounts} .= "$ptr->{accno} ";
1175 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1176 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1177 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1178 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1179 $form->{taxaccounts} .= "$ptr->{accno} ";
1184 chop $ref->{taxaccounts};
1186 push @{ $form->{form_details} }, $ref;
1193 # get last name used
1194 $form->lastname_used($dbh, $myconfig, $form->{vc})
1195 unless $form->{"$form->{vc}_id"};
1199 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1201 Common::webdav_folder($form);
1203 $self->load_periodic_invoice_config($form);
1205 my $rc = $dbh->commit;
1207 $main::lxdebug->leave_sub();
1212 sub retrieve_simple {
1213 $main::lxdebug->enter_sub();
1218 Common::check_params(\%params, qw(id));
1220 my $myconfig = \%main::myconfig;
1221 my $form = $main::form;
1223 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1225 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1226 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1228 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1229 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1231 $main::lxdebug->leave_sub();
1237 $main::lxdebug->enter_sub();
1239 my ($self, $myconfig, $form) = @_;
1241 # connect to database
1242 my $dbh = $form->get_standard_dbh;
1248 my $nodiscount_subtotal = 0;
1249 my $discount_subtotal = 0;
1252 my @partsgroup = ();
1255 my $subtotal_header = 0;
1256 my $subposition = 0;
1264 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1266 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1267 'departments' => 'ALL_DEPARTMENTS');
1270 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1271 $price_factors{$pfac->{id}} = $pfac;
1272 $pfac->{factor} *= 1;
1273 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1277 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1278 next unless $dept->{id} eq $form->{department_id};
1279 $form->{department} = $dept->{description};
1283 # sort items by partsgroup
1284 for $i (1 .. $form->{rowcount}) {
1286 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1287 $partsgroup = $form->{"partsgroup_$i"};
1289 push @partsgroup, [$i, $partsgroup];
1290 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1296 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1297 %projects_by_id = map { $_->id => $_ } @$projects;
1300 if ($projects_by_id{$form->{"globalproject_id"}}) {
1301 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1302 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1304 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1305 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1309 $form->{discount} = [];
1311 # get some values of parts from db on store them in extra array,
1312 # so that they can be sorted in later
1313 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1314 my @prepared_arrays = keys %prepared_template_arrays;
1316 $form->{TEMPLATE_ARRAYS} = { };
1318 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1319 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1322 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1323 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1324 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1325 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1326 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1328 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1329 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1331 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1333 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1335 my $totalweight = 0;
1337 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1340 if ($item->[1] ne $sameitem) {
1341 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1342 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1343 $sameitem = $item->[1];
1345 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1348 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1350 if ($form->{"id_$i"} != 0) {
1352 # add number, description and qty to $form->{number}, ....
1354 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1355 $subtotal_header = $i;
1356 $position = int($position);
1359 } elsif ($subtotal_header) {
1361 $position = int($position);
1362 $position = $position.".".$subposition;
1364 $position = int($position);
1368 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1370 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1372 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1373 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1374 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1375 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1376 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1377 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1378 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1379 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1380 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1381 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1382 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1383 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1384 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1385 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1386 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1387 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1388 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1389 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1390 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1391 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1392 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1394 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1395 my ($dec) = ($sellprice =~ /\.(\d+)/);
1396 my $decimalplaces = max 2, length($dec);
1398 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1400 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1401 my $linetotal = $form->round_amount($linetotal_exact, 2);
1403 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1404 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1406 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1408 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1410 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1412 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1413 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1415 $linetotal = ($linetotal != 0) ? $linetotal : '';
1417 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1418 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1419 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1421 $form->{ordtotal} += $linetotal;
1422 $form->{nodiscount_total} += $nodiscount_linetotal;
1423 $form->{discount_total} += $discount;
1425 if ($subtotal_header) {
1426 $discount_subtotal += $linetotal;
1427 $nodiscount_subtotal += $nodiscount_linetotal;
1430 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1431 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1432 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1433 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1434 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1436 $discount_subtotal = 0;
1437 $nodiscount_subtotal = 0;
1438 $subtotal_header = 0;
1441 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1444 if (!$form->{"discount_$i"}) {
1445 $nodiscount += $linetotal;
1448 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1450 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1451 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1452 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1453 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1454 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1455 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1457 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1458 $totalweight += $lineweight;
1459 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1460 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1461 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1462 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1464 my ($taxamount, $taxbase);
1467 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1469 if ($form->{taxincluded}) {
1472 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1473 $taxbase = $linetotal / (1 + $taxrate);
1475 $taxamount = $linetotal * $taxrate;
1476 $taxbase = $linetotal;
1479 if ($taxamount != 0) {
1480 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1481 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1482 $taxbase{$accno} += $taxbase;
1486 $tax_rate = $taxrate * 100;
1487 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1489 if ($form->{"assembly_$i"}) {
1492 # get parts and push them onto the stack
1494 if ($form->{groupitems}) {
1495 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1497 $sortorder = qq|ORDER BY a.oid|;
1500 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1501 qq|pg.partsgroup | .
1502 qq|FROM assembly a | .
1503 qq| JOIN parts p ON (a.parts_id = p.id) | .
1504 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1505 qq| WHERE a.bom = '1' | .
1506 qq| AND a.id = ? | . $sortorder;
1507 @values = ($form->{"id_$i"});
1508 $sth = $dbh->prepare($query);
1509 $sth->execute(@values) || $form->dberror($query);
1511 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1512 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1513 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1514 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1515 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1516 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1519 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1520 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1521 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1526 CVar->get_non_editable_ic_cvars(form => $form,
1529 sub_module => 'orderitems',
1530 may_converted_from => ['orderitems', 'invoice']);
1532 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1533 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1534 for @{ $ic_cvar_configs };
1536 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1540 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1541 $form->{totalweight_nofmt} = $totalweight;
1542 my $defaults = AM->get_defaults();
1543 $form->{weightunit} = $defaults->{weightunit};
1546 foreach $item (sort keys %taxaccounts) {
1547 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1549 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1550 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1551 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1552 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1553 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1554 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1555 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1557 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1558 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1559 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1562 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1563 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1564 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1565 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1567 if($form->{taxincluded}) {
1568 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1569 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1571 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1572 $form->{subtotal_nofmt} = $form->{ordtotal};
1575 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1578 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1580 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1582 $form->{username} = $myconfig->{name};
1586 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1587 if ($form->{delivery_term} && $form->{language_id}) {
1588 $form->{delivery_term}->description_long( $form->{delivery_term}->translated_attribute('description_long', $form->{language_id}));
1589 $form->{delivery_term}->description_long_invoice($form->{delivery_term}->translated_attribute('description_long_invoice', $form->{language_id}));
1592 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1594 $main::lxdebug->leave_sub();
1597 sub project_description {
1598 $main::lxdebug->enter_sub();
1600 my ($self, $dbh, $id) = @_;
1602 my $query = qq|SELECT description FROM project WHERE id = ?|;
1603 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1605 $main::lxdebug->leave_sub();
1616 OE.pm - Order entry module
1620 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>.
1626 =item retrieve_simple PARAMS
1628 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1630 my $order = retrieve_simple(id => 2);