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, "%" . $form->{"projectnumber"} . "%", "%" . $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, '%' . trim($form->{$vc}) . '%');
179 if ($form->{"cp_name"}) {
180 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
181 push(@values, ('%' . trim($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, '%' . trim($form->{$ordnumber}) . '%');
215 if ($form->{cusordnumber}) {
216 $query .= qq| AND o.cusordnumber ILIKE ?|;
217 push(@values, '%' . trim($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, '%' . trim($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, '%' . trim($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});
291 my @tokens = parse_line('\s+', 0, $form->{all});
292 # ordnumber quonumber customer.name vendor.name transaction_description
294 o.ordnumber ILIKE ? OR
295 o.quonumber ILIKE ? OR
297 o.transaction_description ILIKE ?
299 push @values, ("%$_%")x4 for @tokens;
302 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
303 'trans_id_field' => 'ct.id',
307 $query .= qq| AND ($cvar_where)|;
308 push @values, @cvar_values;
311 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
312 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
313 my %allowed_sort_columns = (
314 "transdate" => "o.transdate",
315 "reqdate" => "o.reqdate",
317 "ordnumber" => "o.ordnumber",
318 "cusordnumber" => "o.cusordnumber",
319 "quonumber" => "o.quonumber",
321 "employee" => "e.name",
322 "salesman" => "s.name",
323 "shipvia" => "o.shipvia",
324 "transaction_description" => "o.transaction_description",
325 "shippingpoint" => "o.shippingpoint",
326 "insertdate" => "o.itime",
327 "taxzone" => "tz.description",
328 "payment_terms" => "pt.description",
330 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
331 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
333 $query .= qq| ORDER by | . $sortorder;
335 my $sth = $dbh->prepare($query);
336 $sth->execute(@values) ||
337 $form->dberror($query . " (" . join(", ", @values) . ")");
341 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
342 $ref->{billed_amount} = $billed_amount{$ref->{id}};
343 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
344 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
345 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
346 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
347 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
348 $id{ $ref->{id} } = $ref->{id};
353 $main::lxdebug->leave_sub();
356 sub transactions_for_todo_list {
357 $main::lxdebug->enter_sub();
362 my $myconfig = \%main::myconfig;
363 my $form = $main::form;
365 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
367 my $query = qq|SELECT id FROM employee WHERE login = ?|;
368 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
371 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
372 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
377 LEFT JOIN customer c ON (oe.customer_id = c.id)
378 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
379 LEFT JOIN employee e ON (oe.employee_id = e.id)
380 WHERE (COALESCE(quotation, FALSE) = TRUE)
381 AND (COALESCE(closed, FALSE) = FALSE)
382 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
383 AND NOT (oe.reqdate ISNULL)
384 AND (oe.reqdate < current_date)
387 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
389 $main::lxdebug->leave_sub();
395 $main::lxdebug->enter_sub();
397 my ($self, $myconfig, $form) = @_;
399 # connect to database, turn off autocommit
400 my $dbh = $form->get_standard_dbh;
401 my $restricter = SL::HTML::Restrict->create;
403 my ($query, @values, $sth, $null);
404 my $exchangerate = 0;
406 my $all_units = AM->retrieve_units($myconfig, $form);
407 $form->{all_units} = $all_units;
409 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
412 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
413 unless ($form->{employee_id}) {
414 $form->get_employee($dbh);
417 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
419 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
420 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
421 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
424 $query = qq|DELETE FROM shipto | .
425 qq|WHERE trans_id = ? AND module = 'OE'|;
426 do_query($form, $dbh, $query, $form->{id});
430 $query = qq|SELECT nextval('id')|;
431 ($form->{id}) = selectrow_query($form, $dbh, $query);
433 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
434 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
451 my @processed_orderitems;
453 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
454 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
457 for my $i (1 .. $form->{rowcount}) {
459 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
461 if ($form->{"id_$i"}) {
464 $query = qq|SELECT unit FROM parts WHERE id = ?|;
465 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
468 if (defined($all_units->{$item_unit}->{factor}) &&
469 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
470 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
472 my $baseqty = $form->{"qty_$i"} * $basefactor;
474 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
475 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
477 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
479 # keep entered selling price
481 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
483 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
485 my $decimalplaces = ($dec > 2) ? $dec : 2;
487 # undo discount formatting
488 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
491 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
493 # round linetotal at least to 2 decimal places
494 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
495 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
497 $form->{"inventory_accno_$i"} *= 1;
498 $form->{"expense_accno_$i"} *= 1;
500 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
504 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
506 if ($form->{taxincluded}) {
507 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
508 $taxbase = $linetotal - $taxamount;
510 # we are not keeping a natural price, do not round
511 $form->{"sellprice_$i"} =
512 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
514 $taxamount = $linetotal * $taxrate;
515 $taxbase = $linetotal;
518 if ($form->round_amount($taxrate, 7) == 0) {
519 if ($form->{taxincluded}) {
520 foreach my $item (@taxaccounts) {
521 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
522 $taxaccounts{$item} += $taxamount;
523 $taxdiff += $taxamount;
524 $taxbase{$item} += $taxbase;
526 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
528 foreach my $item (@taxaccounts) {
529 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
530 $taxbase{$item} += $taxbase;
534 foreach my $item (@taxaccounts) {
535 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
536 $taxbase{$item} += $taxbase;
540 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
542 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
544 # Get pricegroup_id and save it. Unfortunately the interface
545 # also uses ID "0" for signalling that none is selected, but "0"
546 # must not be stored in the database. Therefore we cannot simply
548 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
550 $pricegroup_id = undef if !$pricegroup_id;
552 # force new project, if not set yet
553 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
554 require SL::DB::Customer;
555 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
556 die "Can't find customer" unless $customer;
557 my $new_project = SL::DB::Project->new(
558 projectnumber => $form->{ordnumber},
559 description => $customer->name,
560 customer_id => $customer->id,
562 project_type_id => $::instance_conf->get_project_type_id,
563 project_status_id => $::instance_conf->get_project_status_id,
566 $form->{"globalproject_id"} = $new_project->id;
569 CVar->get_non_editable_ic_cvars(form => $form,
572 sub_module => 'orderitems',
573 may_converted_from => ['orderitems', 'invoice']);
577 # save detail record in orderitems table
578 if (! $form->{"orderitems_id_$i"}) {
579 $query = qq|SELECT nextval('orderitemsid')|;
580 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
582 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
583 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
586 my $orderitems_id = $form->{"orderitems_id_$i"};
587 push @processed_orderitems, $orderitems_id;
590 UPDATE orderitems SET
591 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
592 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
593 pricegroup_id = ?, subtotal = ?,
594 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
595 active_price_source = ?, active_discount_source = ?,
596 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
600 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
601 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
602 $form->{"qty_$i"}, $baseqty,
603 $fxsellprice, $form->{"discount_$i"},
604 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
605 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
606 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
607 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
608 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
609 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
610 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
611 conv_i($orderitems_id),
614 do_query($form, $dbh, $query, @values);
616 $form->{"sellprice_$i"} = $fxsellprice;
617 $form->{"discount_$i"} *= 100;
619 CVar->save_custom_variables(module => 'IC',
620 sub_module => 'orderitems',
621 trans_id => $orderitems_id,
622 configs => $ic_cvar_configs,
624 name_prefix => 'ic_',
625 name_postfix => "_$i",
628 # link previous items with orderitems
629 foreach (qw(orderitems invoice)) {
630 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
631 RecordLinks->create_links('dbh' => $dbh,
634 'from_ids' => $form->{"converted_from_${_}_id_$i"},
635 'to_table' => 'orderitems',
636 'to_id' => $orderitems_id,
639 delete $form->{"converted_from_${_}_id_$i"};
644 # search for orphaned ids
645 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
646 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
647 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
649 if (scalar @orphaned_ids) {
650 # clean up orderitems
651 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
652 do_query($form, $dbh, $query, @orphaned_ids);
655 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
659 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
661 $amount = $form->round_amount($netamount + $tax, 2, 1);
662 $netamount = $form->round_amount($netamount, 2);
664 if ($form->{currency} eq $form->{defaultcurrency}) {
665 $form->{exchangerate} = 1;
667 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
670 # from inputfield (exchangerate) or hidden (forex)
671 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
673 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
675 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
677 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
682 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
683 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
684 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
685 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
686 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
687 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
688 , order_probability = ?, expected_billing_date = ?
691 @values = ($form->{ordnumber} || '', $form->{quonumber},
692 $form->{cusordnumber}, conv_date($form->{transdate}),
693 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
694 $amount, $netamount, conv_date($reqdate),
695 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
696 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
697 $form->{currency}, $form->{closed} ? 't' : 'f',
698 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
699 $quotation, conv_i($form->{department_id}),
700 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
701 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
702 conv_i($form->{delivery_vendor_id}),
703 conv_i($form->{delivery_customer_id}),
704 conv_i($form->{delivery_term_id}),
705 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
706 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
707 $form->{transaction_description},
708 $form->{marge_total} * 1, $form->{marge_percent} * 1,
709 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
710 conv_i($form->{id}));
711 do_query($form, $dbh, $query, @values);
713 $form->new_lastmtime('oe');
715 $form->{ordtotal} = $amount;
717 $form->{name} = $form->{ $form->{vc} };
718 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
721 if (!$form->{shipto_id}) {
722 $form->add_shipto($dbh, $form->{id}, "OE");
725 # save printed, emailed, queued
726 $form->save_status($dbh);
728 # Link this record to the records it was created from.
729 # check every record type we may link. i am not happy with converting the string to array back
730 # should be a array from the start (OE.pm -> retrieve).
731 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
732 $form->{convert_from_oe_ids} =~ s/^\s+//;
733 $form->{convert_from_oe_ids} =~ s/\s+$//;
734 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
735 delete $form->{convert_from_oe_ids};
736 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
737 foreach (qw(ar oe)) {
738 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
739 RecordLinks->create_links('dbh' => $dbh,
742 'from_ids' => $form->{"convert_from_${_}_ids"},
744 'to_id' => $form->{id},
746 delete $form->{"convert_from_${_}_ids"};
748 $self->_close_quotations_rfqs('dbh' => $dbh,
749 'from_id' => \@convert_from_oe_ids,
750 'to_id' => $form->{id}) if $_ eq 'oe';
753 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
754 if ($form->{vc} eq 'customer') {
755 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
757 if ($form->{vc} eq 'vendor') {
758 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
762 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
763 "quonumber" : "ordnumber"};
765 Common::webdav_folder($form);
767 my $rc = $dbh->commit;
769 $self->save_periodic_invoices_config(dbh => $dbh,
770 oe_id => $form->{id},
771 config_yaml => $form->{periodic_invoices_config})
772 if ($form->{type} eq 'sales_order');
774 $main::lxdebug->leave_sub();
779 sub save_periodic_invoices_config {
780 my ($self, %params) = @_;
782 return if !$params{oe_id};
784 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
785 return if 'HASH' ne ref $config;
787 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
788 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
789 $obj->update_attributes(%{ $config });
792 sub load_periodic_invoice_config {
796 delete $form->{periodic_invoices_config};
799 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
802 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
803 print printer_id copies direct_debit) };
804 $form->{periodic_invoices_config} = YAML::Dump($config);
809 sub _close_quotations_rfqs {
810 $main::lxdebug->enter_sub();
815 Common::check_params(\%params, qw(from_id to_id));
817 my $myconfig = \%main::myconfig;
818 my $form = $main::form;
820 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
822 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
823 my $sth = prepare_query($form, $dbh, $query);
825 do_statement($form, $sth, $query, conv_i($params{to_id}));
827 my ($quotation) = $sth->fetchrow_array();
830 $main::lxdebug->leave_sub();
836 foreach my $from_id (@{ $params{from_id} }) {
837 $from_id = conv_i($from_id);
838 do_statement($form, $sth, $query, $from_id);
839 ($quotation) = $sth->fetchrow_array();
840 push @close_ids, $from_id if ($quotation);
845 if (scalar @close_ids) {
846 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
847 do_query($form, $dbh, $query, @close_ids);
849 $dbh->commit() unless ($params{dbh});
852 $main::lxdebug->leave_sub();
856 $main::lxdebug->enter_sub();
858 my ($self, $myconfig, $form) = @_;
860 my $rc = SL::DB::Order->new->db->with_transaction(sub {
861 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
863 SL::DB::Order->new(id => $form->{id})->delete;
865 my $spool = $::lx_office_conf{paths}->{spool};
866 unlink map { "$spool/$_" } @spoolfiles if $spool;
871 $main::lxdebug->leave_sub();
877 $main::lxdebug->enter_sub();
879 my ($self, $myconfig, $form) = @_;
881 # connect to database
882 my $dbh = $form->get_standard_dbh;
884 my ($query, $query_add, @values, @ids, $sth);
886 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
888 push @ids, $form->{"trans_id_$_"}
889 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
890 } (1 .. $form->{"rowcount"});
892 if ($form->{rowcount} && scalar @ids) {
893 $form->{convert_from_oe_ids} = join ' ', @ids;
896 # if called in multi id mode, and still only got one id, switch back to single id
897 if ($form->{"rowcount"} and $#ids == 0) {
898 $form->{"id"} = $ids[0];
900 delete $form->{convert_from_oe_ids};
903 # and remember for the rest of the function
904 my $is_collective_order = scalar @ids;
906 # If collective order was created from exactly 1 order, we assume the same
907 # behaviour as a "save as new" from within an order is actually desired, i.e.
908 # the original order isn't part of a workflow where we want to remember
909 # record_links, but simply a quick way of generating a new order from an old
910 # one without having to enter everything again.
911 # Setting useasnew will prevent the creation of record_links for the items
912 # when saving the new order.
913 # This form variable is probably not necessary, could just set saveasnew instead
914 $form->{useasnew} = 1 if $is_collective_order == 1;
917 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
918 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
919 $form->{transdate} = DateTime->today_local->to_kivitendo;
922 # get default accounts
923 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
924 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
925 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
926 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
927 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
928 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
929 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
932 my $ref = selectfirst_hashref_query($form, $dbh, $query);
933 map { $form->{$_} = $ref->{$_} } keys %$ref;
935 $form->{currency} = $form->get_default_currency($myconfig);
937 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
938 # we come from invoices, feel free.
939 $form->{reqdate} = $form->{deliverydate}
940 if ( $form->{deliverydate}
941 and $form->{callback} =~ /action=ar_transactions/);
943 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
945 if ($form->{id} or @ids) {
947 # retrieve order for single id
948 # NOTE: this query is intended to fetch all information only ONCE.
949 # so if any of these infos is important (or even different) for any item,
950 # it will be killed out and then has to be fetched from the item scope query further down
952 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
953 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
954 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
955 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
956 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
958 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
959 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
960 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
961 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
963 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
964 LEFT JOIN employee e ON (o.employee_id = e.id)
965 LEFT JOIN department d ON (o.department_id = d.id) | .
968 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
970 @values = $form->{id} ? ($form->{id}) : @ids;
971 $sth = prepare_execute_query($form, $dbh, $query, @values);
973 $ref = $sth->fetchrow_hashref("NAME_lc");
976 map { $form->{$_} = $ref->{$_} } keys %$ref;
978 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
980 # set all entries for multiple ids blank that yield different information
981 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
982 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
985 $form->{mtime} ||= $form->{itime};
986 $form->{lastmtime} = $form->{mtime};
988 # if not given, fill transdate with current_date
989 $form->{transdate} = $form->current_date($myconfig)
990 unless $form->{transdate};
994 if ($form->{delivery_customer_id}) {
995 $query = qq|SELECT name FROM customer WHERE id = ?|;
996 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
999 if ($form->{delivery_vendor_id}) {
1000 $query = qq|SELECT name FROM customer WHERE id = ?|;
1001 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1004 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1006 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1007 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1009 $ref = $sth->fetchrow_hashref("NAME_lc");
1011 map { $form->{$_} = $ref->{$_} } keys %$ref;
1014 # get printed, emailed and queued
1015 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1016 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1018 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1019 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1020 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1021 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1024 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1027 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1029 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1031 # retrieve individual items
1032 # this query looks up all information about the items
1033 # stuff different from the whole will not be overwritten, but saved with a suffix.
1035 qq|SELECT o.id AS orderitems_id,
1036 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1037 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1038 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1039 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1040 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1041 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1042 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1043 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1044 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1045 pr.projectnumber, p.formel,
1046 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1048 JOIN parts p ON (o.parts_id = p.id)
1049 JOIN oe ON (o.trans_id = oe.id)
1050 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1051 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)
1052 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)
1053 LEFT JOIN project pr ON (o.project_id = pr.id)
1054 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1056 ? qq|WHERE o.trans_id = ?|
1057 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1058 qq|ORDER BY o.trans_id, o.position|;
1060 @ids = $form->{id} ? ($form->{id}) : @ids;
1061 $sth = prepare_execute_query($form, $dbh, $query, @values);
1063 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1064 # Retrieve custom variables.
1065 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1067 sub_module => 'orderitems',
1068 trans_id => $ref->{orderitems_id},
1070 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1073 if (!$ref->{"part_inventory_accno_id"}) {
1074 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1076 delete($ref->{"part_inventory_accno_id"});
1078 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1079 # unless already present there
1080 # remove _oe entries afterwards
1081 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1082 qw|ordnumber transdate cusordnumber|
1084 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1088 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1090 qq|SELECT accno AS inventory_accno, | .
1091 qq| new_chart_id AS inventory_new_chart, | .
1092 qq| date($transdate) - valid_from AS inventory_valid | .
1093 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1094 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1095 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1098 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1100 qq|SELECT accno AS income_accno, | .
1101 qq| new_chart_id AS income_new_chart, | .
1102 qq| date($transdate) - valid_from AS income_valid | .
1103 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1104 ($ref->{income_accno}, $ref->{income_new_chart},
1105 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1108 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1110 qq|SELECT accno AS expense_accno, | .
1111 qq| new_chart_id AS expense_new_chart, | .
1112 qq| date($transdate) - valid_from AS expense_valid | .
1113 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1114 ($ref->{expense_accno}, $ref->{expense_new_chart},
1115 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1118 # delete orderitems_id in collective orders, so that they get cloned no matter what
1119 # is this correct? or is the following meant?
1120 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1121 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1123 # get tax rates and description
1124 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1126 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1127 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1128 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1129 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1130 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1131 qq|ORDER BY c.accno|;
1132 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1133 $ref->{taxaccounts} = "";
1135 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1136 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1140 $ref->{taxaccounts} .= "$ptr->{accno} ";
1141 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1142 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1143 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1144 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1145 $form->{taxaccounts} .= "$ptr->{accno} ";
1150 chop $ref->{taxaccounts};
1152 push @{ $form->{form_details} }, $ref;
1159 # get last name used
1160 $form->lastname_used($dbh, $myconfig, $form->{vc})
1161 unless $form->{"$form->{vc}_id"};
1165 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1167 Common::webdav_folder($form);
1169 $self->load_periodic_invoice_config($form);
1171 my $rc = $dbh->commit;
1173 $main::lxdebug->leave_sub();
1178 sub retrieve_simple {
1179 $main::lxdebug->enter_sub();
1184 Common::check_params(\%params, qw(id));
1186 my $myconfig = \%main::myconfig;
1187 my $form = $main::form;
1189 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1191 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1192 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1194 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1195 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1197 $main::lxdebug->leave_sub();
1203 $main::lxdebug->enter_sub();
1205 my ($self, $myconfig, $form) = @_;
1207 # connect to database
1208 my $dbh = $form->get_standard_dbh;
1214 my $nodiscount_subtotal = 0;
1215 my $discount_subtotal = 0;
1218 my @partsgroup = ();
1221 my $subtotal_header = 0;
1222 my $subposition = 0;
1230 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1232 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1233 'departments' => 'ALL_DEPARTMENTS');
1236 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1237 $price_factors{$pfac->{id}} = $pfac;
1238 $pfac->{factor} *= 1;
1239 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1243 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1244 next unless $dept->{id} eq $form->{department_id};
1245 $form->{department} = $dept->{description};
1249 # sort items by partsgroup
1250 for $i (1 .. $form->{rowcount}) {
1252 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1253 $partsgroup = $form->{"partsgroup_$i"};
1255 push @partsgroup, [$i, $partsgroup];
1256 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1262 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1263 %projects_by_id = map { $_->id => $_ } @$projects;
1266 if ($projects_by_id{$form->{"globalproject_id"}}) {
1267 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1268 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1270 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1271 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1275 $form->{discount} = [];
1277 # get some values of parts from db on store them in extra array,
1278 # so that they can be sorted in later
1279 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1280 my @prepared_arrays = keys %prepared_template_arrays;
1282 $form->{TEMPLATE_ARRAYS} = { };
1284 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1285 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1288 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1289 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1290 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1291 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1292 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1294 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1295 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1297 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1299 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1301 my $totalweight = 0;
1303 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1306 if ($item->[1] ne $sameitem) {
1307 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1308 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1309 $sameitem = $item->[1];
1311 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1314 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1316 if ($form->{"id_$i"} != 0) {
1318 # add number, description and qty to $form->{number}, ....
1320 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1321 $subtotal_header = $i;
1322 $position = int($position);
1325 } elsif ($subtotal_header) {
1327 $position = int($position);
1328 $position = $position.".".$subposition;
1330 $position = int($position);
1334 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1336 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1338 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1339 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1340 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1341 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1342 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1343 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1344 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1345 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1346 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1347 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1348 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1349 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1350 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1351 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1352 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1353 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1354 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1355 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1356 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1357 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1358 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1360 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1361 my ($dec) = ($sellprice =~ /\.(\d+)/);
1362 my $decimalplaces = max 2, length($dec);
1364 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1366 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1367 my $linetotal = $form->round_amount($linetotal_exact, 2);
1369 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1370 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1372 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1374 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1376 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1378 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1379 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1381 $linetotal = ($linetotal != 0) ? $linetotal : '';
1383 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1384 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1385 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1387 $form->{ordtotal} += $linetotal;
1388 $form->{nodiscount_total} += $nodiscount_linetotal;
1389 $form->{discount_total} += $discount;
1391 if ($subtotal_header) {
1392 $discount_subtotal += $linetotal;
1393 $nodiscount_subtotal += $nodiscount_linetotal;
1396 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1397 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1398 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1399 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1400 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1402 $discount_subtotal = 0;
1403 $nodiscount_subtotal = 0;
1404 $subtotal_header = 0;
1407 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1410 if (!$form->{"discount_$i"}) {
1411 $nodiscount += $linetotal;
1414 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1416 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1417 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1418 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1419 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1420 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1421 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1423 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1424 $totalweight += $lineweight;
1425 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1426 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1427 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1428 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1430 my ($taxamount, $taxbase);
1433 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1435 if ($form->{taxincluded}) {
1438 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1439 $taxbase = $linetotal / (1 + $taxrate);
1441 $taxamount = $linetotal * $taxrate;
1442 $taxbase = $linetotal;
1445 if ($taxamount != 0) {
1446 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1447 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1448 $taxbase{$accno} += $taxbase;
1452 $tax_rate = $taxrate * 100;
1453 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1455 if ($form->{"assembly_$i"}) {
1458 # get parts and push them onto the stack
1460 if ($form->{groupitems}) {
1461 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1463 $sortorder = qq|ORDER BY a.oid|;
1466 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1467 qq|pg.partsgroup | .
1468 qq|FROM assembly a | .
1469 qq| JOIN parts p ON (a.parts_id = p.id) | .
1470 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1471 qq| WHERE a.bom = '1' | .
1472 qq| AND a.id = ? | . $sortorder;
1473 @values = ($form->{"id_$i"});
1474 $sth = $dbh->prepare($query);
1475 $sth->execute(@values) || $form->dberror($query);
1477 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1478 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1479 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1480 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1481 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1482 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1485 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1486 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1487 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1492 CVar->get_non_editable_ic_cvars(form => $form,
1495 sub_module => 'orderitems',
1496 may_converted_from => ['orderitems', 'invoice']);
1498 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1499 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1500 for @{ $ic_cvar_configs };
1502 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1506 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1507 $form->{totalweight_nofmt} = $totalweight;
1508 my $defaults = AM->get_defaults();
1509 $form->{weightunit} = $defaults->{weightunit};
1512 foreach $item (sort keys %taxaccounts) {
1513 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1515 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1516 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1517 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1518 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1519 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1520 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1521 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1523 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1524 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1525 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1528 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1529 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1530 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1531 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1533 if($form->{taxincluded}) {
1534 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1535 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1537 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1538 $form->{subtotal_nofmt} = $form->{ordtotal};
1541 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1544 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1546 if ($form->{type} =~ /_quotation/) {
1547 $form->set_payment_options($myconfig, $form->{quodate});
1549 $form->set_payment_options($myconfig, $form->{orddate});
1552 $form->{username} = $myconfig->{name};
1556 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1557 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1559 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1561 $main::lxdebug->leave_sub();
1564 sub project_description {
1565 $main::lxdebug->enter_sub();
1567 my ($self, $dbh, $id) = @_;
1569 my $query = qq|SELECT description FROM project WHERE id = ?|;
1570 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1572 $main::lxdebug->leave_sub();
1583 OE.pm - Order entry module
1587 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>.
1593 =item retrieve_simple PARAMS
1595 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1597 my $order = retrieve_simple(id => 2);