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;
57 $main::lxdebug->enter_sub();
59 my ($self, $myconfig, $form) = @_;
62 my $dbh = $form->get_standard_dbh;
65 my $ordnumber = 'ordnumber';
71 my ($periodic_invoices_columns, $periodic_invoices_joins);
73 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
75 if ($form->{type} =~ /_quotation$/) {
77 $ordnumber = 'quonumber';
79 } elsif ($form->{type} eq 'sales_order') {
80 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
81 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
84 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
88 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
90 SELECT from_id, ar.amount, ar.netamount FROM (
93 WHERE from_table = 'oe' AND to_table = 'ar'
95 SELECT rl1.from_id, rl2.to_id
97 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
98 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
100 LEFT JOIN ar ON ar.id = rl.to_id
102 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
103 $billed_amount{ $ref->{from_id}} += $ref->{amount};
104 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
109 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
110 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
111 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
112 qq| o.transaction_description, | .
113 qq| o.marge_total, o.marge_percent, | .
114 qq| o.itime::DATE AS insertdate, | .
115 qq| ex.$rate AS exchangerate, | .
116 qq| pt.description AS payment_terms, | .
117 qq| pr.projectnumber AS globalprojectnumber, | .
118 qq| e.name AS employee, s.name AS salesman, | .
119 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
120 qq| tz.description AS taxzone | .
121 $periodic_invoices_columns .
122 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
124 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
125 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
126 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
127 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
128 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
129 qq| AND ex.transdate = o.transdate) | .
130 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
131 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
132 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
133 qq|$periodic_invoices_joins | .
134 qq|WHERE (o.quotation = ?) |;
135 push(@values, $quotation);
137 my ($null, $split_department_id) = split /--/, $form->{department};
138 my $department_id = $form->{department_id} || $split_department_id;
139 if ($department_id) {
140 $query .= qq| AND o.department_id = ?|;
141 push(@values, $department_id);
144 if ($form->{"project_id"}) {
146 qq|AND ((globalproject_id = ?) OR EXISTS | .
147 qq| (SELECT * FROM orderitems oi | .
148 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
149 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
152 if ($form->{"projectnumber"}) {
154 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
155 SELECT * FROM orderitems oi
156 LEFT JOIN project proi ON proi.id = oi.project_id
157 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
160 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
163 if ($form->{"business_id"}) {
164 $query .= " AND ct.business_id = ?";
165 push(@values, $form->{"business_id"});
168 if ($form->{"${vc}_id"}) {
169 $query .= " AND o.${vc}_id = ?";
170 push(@values, $form->{"${vc}_id"});
172 } elsif ($form->{$vc}) {
173 $query .= " AND ct.name ILIKE ?";
174 push(@values, '%' . $form->{$vc} . '%');
177 if ($form->{"cp_name"}) {
178 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
179 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
182 if (!$main::auth->assert('sales_all_edit', 1)) {
183 $query .= " AND o.employee_id = (select id from employee where login= ?)";
184 push @values, $::myconfig{login};
186 if ($form->{employee_id}) {
187 $query .= " AND o.employee_id = ?";
188 push @values, conv_i($form->{employee_id});
191 if ($form->{salesman_id}) {
192 $query .= " AND o.salesman_id = ?";
193 push @values, conv_i($form->{salesman_id});
196 if (!$form->{open} && !$form->{closed}) {
197 $query .= " AND o.id = 0";
198 } elsif (!($form->{open} && $form->{closed})) {
199 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
202 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
203 ($form->{"notdelivered"} ne $form->{"delivered"})) {
204 $query .= $form->{"delivered"} ?
205 " AND o.delivered " : " AND NOT o.delivered";
208 if ($form->{$ordnumber}) {
209 $query .= qq| AND o.$ordnumber ILIKE ?|;
210 push(@values, '%' . $form->{$ordnumber} . '%');
213 if ($form->{cusordnumber}) {
214 $query .= qq| AND o.cusordnumber ILIKE ?|;
215 push(@values, '%' . $form->{cusordnumber} . '%');
218 if($form->{transdatefrom}) {
219 $query .= qq| AND o.transdate >= ?|;
220 push(@values, conv_date($form->{transdatefrom}));
223 if($form->{transdateto}) {
224 $query .= qq| AND o.transdate <= ?|;
225 push(@values, conv_date($form->{transdateto}));
228 if($form->{reqdatefrom}) {
229 $query .= qq| AND o.reqdate >= ?|;
230 push(@values, conv_date($form->{reqdatefrom}));
233 if($form->{reqdateto}) {
234 $query .= qq| AND o.reqdate <= ?|;
235 push(@values, conv_date($form->{reqdateto}));
238 if($form->{insertdatefrom}) {
239 $query .= qq| AND o.itime::DATE >= ?|;
240 push(@values, conv_date($form->{insertdatefrom}));
243 if($form->{insertdateto}) {
244 $query .= qq| AND o.itime::DATE <= ?|;
245 push(@values, conv_date($form->{insertdateto}));
248 if ($form->{shippingpoint}) {
249 $query .= qq| AND o.shippingpoint ILIKE ?|;
250 push(@values, '%' . $form->{shippingpoint} . '%');
253 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
254 $query .= qq| AND tz.id = ?|;
255 push(@values, $form->{taxzone_id});
258 if ($form->{transaction_description}) {
259 $query .= qq| AND o.transaction_description ILIKE ?|;
260 push(@values, '%' . $form->{transaction_description} . '%');
263 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
264 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
265 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
268 if ($form->{reqdate_unset_or_old}) {
269 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
272 if (($form->{order_probability_value} || '') ne '') {
273 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
274 $query .= qq| AND (o.order_probability ${op} ?)|;
275 push @values, $form->{order_probability_value};
278 if ($form->{expected_billing_date_from}) {
279 $query .= qq| AND (o.expected_billing_date >= ?)|;
280 push @values, conv_date($form->{expected_billing_date_from});
283 if ($form->{expected_billing_date_to}) {
284 $query .= qq| AND (o.expected_billing_date <= ?)|;
285 push @values, conv_date($form->{expected_billing_date_to});
288 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
289 'trans_id_field' => 'ct.id',
293 $query .= qq| AND ($cvar_where)|;
294 push @values, @cvar_values;
297 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
298 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
299 my %allowed_sort_columns = (
300 "transdate" => "o.transdate",
301 "reqdate" => "o.reqdate",
303 "ordnumber" => "o.ordnumber",
304 "cusordnumber" => "o.cusordnumber",
305 "quonumber" => "o.quonumber",
307 "employee" => "e.name",
308 "salesman" => "s.name",
309 "shipvia" => "o.shipvia",
310 "transaction_description" => "o.transaction_description",
311 "shippingpoint" => "o.shippingpoint",
312 "insertdate" => "o.itime",
313 "taxzone" => "tz.description",
314 "payment_terms" => "pt.description",
316 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
317 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
319 $query .= qq| ORDER by | . $sortorder;
321 my $sth = $dbh->prepare($query);
322 $sth->execute(@values) ||
323 $form->dberror($query . " (" . join(", ", @values) . ")");
327 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
328 $ref->{billed_amount} = $billed_amount{$ref->{id}};
329 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
330 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
331 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
332 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
333 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
334 $id{ $ref->{id} } = $ref->{id};
339 $main::lxdebug->leave_sub();
342 sub transactions_for_todo_list {
343 $main::lxdebug->enter_sub();
348 my $myconfig = \%main::myconfig;
349 my $form = $main::form;
351 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
353 my $query = qq|SELECT id FROM employee WHERE login = ?|;
354 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
357 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
358 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
363 LEFT JOIN customer c ON (oe.customer_id = c.id)
364 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
365 LEFT JOIN employee e ON (oe.employee_id = e.id)
366 WHERE (COALESCE(quotation, FALSE) = TRUE)
367 AND (COALESCE(closed, FALSE) = FALSE)
368 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
369 AND NOT (oe.reqdate ISNULL)
370 AND (oe.reqdate < current_date)
373 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
375 $main::lxdebug->leave_sub();
381 $main::lxdebug->enter_sub();
383 my ($self, $myconfig, $form) = @_;
385 # connect to database, turn off autocommit
386 my $dbh = $form->get_standard_dbh;
387 my $restricter = SL::HTML::Restrict->create;
389 my ($query, @values, $sth, $null);
390 my $exchangerate = 0;
392 my $all_units = AM->retrieve_units($myconfig, $form);
393 $form->{all_units} = $all_units;
395 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
398 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
399 unless ($form->{employee_id}) {
400 $form->get_employee($dbh);
403 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
405 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
406 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
407 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
410 $query = qq|DELETE FROM shipto | .
411 qq|WHERE trans_id = ? AND module = 'OE'|;
412 do_query($form, $dbh, $query, $form->{id});
416 $query = qq|SELECT nextval('id')|;
417 ($form->{id}) = selectrow_query($form, $dbh, $query);
419 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
420 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
437 my @processed_orderitems;
439 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
440 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
443 for my $i (1 .. $form->{rowcount}) {
445 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
447 if ($form->{"id_$i"}) {
450 $query = qq|SELECT unit FROM parts WHERE id = ?|;
451 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
454 if (defined($all_units->{$item_unit}->{factor}) &&
455 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
456 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
458 my $baseqty = $form->{"qty_$i"} * $basefactor;
460 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
461 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
463 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
465 # keep entered selling price
467 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
469 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
471 my $decimalplaces = ($dec > 2) ? $dec : 2;
473 # undo discount formatting
474 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
477 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
479 # round linetotal at least to 2 decimal places
480 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
481 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
483 $form->{"inventory_accno_$i"} *= 1;
484 $form->{"expense_accno_$i"} *= 1;
486 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
490 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
492 if ($form->{taxincluded}) {
493 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
494 $taxbase = $linetotal - $taxamount;
496 # we are not keeping a natural price, do not round
497 $form->{"sellprice_$i"} =
498 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
500 $taxamount = $linetotal * $taxrate;
501 $taxbase = $linetotal;
504 if ($form->round_amount($taxrate, 7) == 0) {
505 if ($form->{taxincluded}) {
506 foreach my $item (@taxaccounts) {
507 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
508 $taxaccounts{$item} += $taxamount;
509 $taxdiff += $taxamount;
510 $taxbase{$item} += $taxbase;
512 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
514 foreach my $item (@taxaccounts) {
515 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
516 $taxbase{$item} += $taxbase;
520 foreach my $item (@taxaccounts) {
521 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
522 $taxbase{$item} += $taxbase;
526 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
528 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
530 # Get pricegroup_id and save it. Unfortunately the interface
531 # also uses ID "0" for signalling that none is selected, but "0"
532 # must not be stored in the database. Therefore we cannot simply
534 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
536 $pricegroup_id = undef if !$pricegroup_id;
538 if ( $::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ( $form->{type} eq 'sales_order' ) ) {
539 my $new_project = SL::DB::Project->new(
540 projectnumber => $form->{ordnumber},
541 description => $form->{customer},
543 project_type_id => $::instance_conf->get_project_type_id,
544 project_status_id => $::instance_conf->get_project_status_id,
545 # id => $form->{globalproject_id}
548 $form->{"globalproject_id"} = $new_project->id;
551 CVar->get_non_editable_ic_cvars(form => $form,
554 sub_module => 'orderitems',
555 may_converted_from => ['orderitems', 'invoice']);
559 # save detail record in orderitems table
560 if (! $form->{"orderitems_id_$i"}) {
561 $query = qq|SELECT nextval('orderitemsid')|;
562 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
564 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
565 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
568 my $orderitems_id = $form->{"orderitems_id_$i"};
569 push @processed_orderitems, $orderitems_id;
572 UPDATE orderitems SET
573 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
574 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
575 pricegroup_id = ?, subtotal = ?,
576 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
577 active_price_source = ?, active_discount_source = ?,
578 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
582 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
583 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
584 $form->{"qty_$i"}, $baseqty,
585 $fxsellprice, $form->{"discount_$i"},
586 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
587 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
588 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
589 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
590 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
591 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
592 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
593 conv_i($orderitems_id),
596 do_query($form, $dbh, $query, @values);
598 $form->{"sellprice_$i"} = $fxsellprice;
599 $form->{"discount_$i"} *= 100;
601 CVar->save_custom_variables(module => 'IC',
602 sub_module => 'orderitems',
603 trans_id => $orderitems_id,
604 configs => $ic_cvar_configs,
606 name_prefix => 'ic_',
607 name_postfix => "_$i",
610 # link previous items with orderitems
611 foreach (qw(orderitems invoice)) {
612 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
613 RecordLinks->create_links('dbh' => $dbh,
616 'from_ids' => $form->{"converted_from_${_}_id_$i"},
617 'to_table' => 'orderitems',
618 'to_id' => $orderitems_id,
621 delete $form->{"converted_from_${_}_id_$i"};
626 # search for orphaned ids
627 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
628 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
629 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
631 if (scalar @orphaned_ids) {
632 # clean up orderitems
633 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
634 do_query($form, $dbh, $query, @orphaned_ids);
637 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
641 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
643 $amount = $form->round_amount($netamount + $tax, 2);
644 $netamount = $form->round_amount($netamount, 2);
646 if ($form->{currency} eq $form->{defaultcurrency}) {
647 $form->{exchangerate} = 1;
649 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
652 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
654 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
656 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
661 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
662 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
663 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
664 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
665 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
666 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
667 , order_probability = ?, expected_billing_date = ?
670 @values = ($form->{ordnumber} || '', $form->{quonumber},
671 $form->{cusordnumber}, conv_date($form->{transdate}),
672 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
673 $amount, $netamount, conv_date($reqdate),
674 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
675 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
676 $form->{currency}, $form->{closed} ? 't' : 'f',
677 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
678 $quotation, conv_i($form->{department_id}),
679 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
680 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
681 conv_i($form->{delivery_vendor_id}),
682 conv_i($form->{delivery_customer_id}),
683 conv_i($form->{delivery_term_id}),
684 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
685 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
686 $form->{transaction_description},
687 $form->{marge_total} * 1, $form->{marge_percent} * 1,
688 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
689 conv_i($form->{id}));
690 do_query($form, $dbh, $query, @values);
692 $form->{ordtotal} = $amount;
694 $form->{name} = $form->{ $form->{vc} };
695 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
698 if (!$form->{shipto_id}) {
699 $form->add_shipto($dbh, $form->{id}, "OE");
702 # save printed, emailed, queued
703 $form->save_status($dbh);
705 # Link this record to the records it was created from.
706 # check every record type we may link. i am not happy with converting the string to array back
707 # should be a array from the start (OE.pm -> retrieve).
708 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
709 $form->{convert_from_oe_ids} =~ s/^\s+//;
710 $form->{convert_from_oe_ids} =~ s/\s+$//;
711 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
712 delete $form->{convert_from_oe_ids};
713 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
714 foreach (qw(ar oe)) {
715 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
716 RecordLinks->create_links('dbh' => $dbh,
719 'from_ids' => $form->{"convert_from_${_}_ids"},
721 'to_id' => $form->{id},
723 delete $form->{"convert_from_${_}_ids"};
725 $self->_close_quotations_rfqs('dbh' => $dbh,
726 'from_id' => \@convert_from_oe_ids,
727 'to_id' => $form->{id}) if $_ eq 'oe';
730 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
731 if ($form->{vc} eq 'customer') {
732 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
734 if ($form->{vc} eq 'vendor') {
735 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
739 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
740 "quonumber" : "ordnumber"};
742 Common::webdav_folder($form);
744 my $rc = $dbh->commit;
746 $self->save_periodic_invoices_config(dbh => $dbh,
747 oe_id => $form->{id},
748 config_yaml => $form->{periodic_invoices_config})
749 if ($form->{type} eq 'sales_order');
751 $main::lxdebug->leave_sub();
756 sub save_periodic_invoices_config {
757 my ($self, %params) = @_;
759 return if !$params{oe_id};
761 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
762 return if 'HASH' ne ref $config;
764 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
765 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
766 $obj->update_attributes(%{ $config });
769 sub load_periodic_invoice_config {
773 delete $form->{periodic_invoices_config};
776 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
779 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
780 print printer_id copies direct_debit) };
781 $form->{periodic_invoices_config} = YAML::Dump($config);
786 sub _close_quotations_rfqs {
787 $main::lxdebug->enter_sub();
792 Common::check_params(\%params, qw(from_id to_id));
794 my $myconfig = \%main::myconfig;
795 my $form = $main::form;
797 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
799 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
800 my $sth = prepare_query($form, $dbh, $query);
802 do_statement($form, $sth, $query, conv_i($params{to_id}));
804 my ($quotation) = $sth->fetchrow_array();
807 $main::lxdebug->leave_sub();
813 foreach my $from_id (@{ $params{from_id} }) {
814 $from_id = conv_i($from_id);
815 do_statement($form, $sth, $query, $from_id);
816 ($quotation) = $sth->fetchrow_array();
817 push @close_ids, $from_id if ($quotation);
822 if (scalar @close_ids) {
823 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
824 do_query($form, $dbh, $query, @close_ids);
826 $dbh->commit() unless ($params{dbh});
829 $main::lxdebug->leave_sub();
833 $main::lxdebug->enter_sub();
835 my ($self, $myconfig, $form) = @_;
837 my $rc = SL::DB::Order->new->db->with_transaction(sub {
838 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
840 SL::DB::Order->new(id => $form->{id})->delete;
842 my $spool = $::lx_office_conf{paths}->{spool};
843 unlink map { "$spool/$_" } @spoolfiles if $spool;
848 $main::lxdebug->leave_sub();
854 $main::lxdebug->enter_sub();
856 my ($self, $myconfig, $form) = @_;
858 # connect to database
859 my $dbh = $form->get_standard_dbh;
861 my ($query, $query_add, @values, @ids, $sth);
863 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
865 push @ids, $form->{"trans_id_$_"}
866 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
867 } (1 .. $form->{"rowcount"});
869 if ($form->{rowcount} && scalar @ids) {
870 $form->{convert_from_oe_ids} = join ' ', @ids;
873 # if called in multi id mode, and still only got one id, switch back to single id
874 if ($form->{"rowcount"} and $#ids == 0) {
875 $form->{"id"} = $ids[0];
877 delete $form->{convert_from_oe_ids};
880 # and remember for the rest of the function
881 my $is_collective_order = scalar @ids;
883 # If collective order was created from exactly 1 order, we assume the same
884 # behaviour as a "save as new" from within an order is actually desired, i.e.
885 # the original order isn't part of a workflow where we want to remember
886 # record_links, but simply a quick way of generating a new order from an old
887 # one without having to enter everything again.
888 # Setting useasnew will prevent the creation of record_links for the items
889 # when saving the new order.
890 # This form variable is probably not necessary, could just set saveasnew instead
891 $form->{useasnew} = 1 if $is_collective_order == 1;
894 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
895 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
896 $form->{transdate} = DateTime->today_local->to_kivitendo;
899 # get default accounts
900 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
901 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
902 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
903 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
904 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
907 my $ref = selectfirst_hashref_query($form, $dbh, $query);
908 map { $form->{$_} = $ref->{$_} } keys %$ref;
910 $form->{currency} = $form->get_default_currency($myconfig);
912 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
913 # we come from invoices, feel free.
914 $form->{reqdate} = $form->{deliverydate}
915 if ( $form->{deliverydate}
916 and $form->{callback} =~ /action=ar_transactions/);
918 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
920 if ($form->{id} or @ids) {
922 # retrieve order for single id
923 # NOTE: this query is intended to fetch all information only ONCE.
924 # so if any of these infos is important (or even different) for any item,
925 # it will be killed out and then has to be fetched from the item scope query further down
927 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
928 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
929 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
930 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
931 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
933 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
934 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
935 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
936 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
938 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
939 LEFT JOIN employee e ON (o.employee_id = e.id)
940 LEFT JOIN department d ON (o.department_id = d.id) | .
943 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
945 @values = $form->{id} ? ($form->{id}) : @ids;
946 $sth = prepare_execute_query($form, $dbh, $query, @values);
948 $ref = $sth->fetchrow_hashref("NAME_lc");
951 map { $form->{$_} = $ref->{$_} } keys %$ref;
953 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
955 # set all entries for multiple ids blank that yield different information
956 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
957 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
960 $form->{mtime} ||= $form->{itime};
961 $form->{lastmtime} = $form->{mtime};
963 # if not given, fill transdate with current_date
964 $form->{transdate} = $form->current_date($myconfig)
965 unless $form->{transdate};
969 if ($form->{delivery_customer_id}) {
970 $query = qq|SELECT name FROM customer WHERE id = ?|;
971 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
974 if ($form->{delivery_vendor_id}) {
975 $query = qq|SELECT name FROM customer WHERE id = ?|;
976 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
979 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
981 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
982 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
984 $ref = $sth->fetchrow_hashref("NAME_lc");
986 map { $form->{$_} = $ref->{$_} } keys %$ref;
989 # get printed, emailed and queued
990 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
991 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
993 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
994 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
995 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
996 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
999 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1002 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1004 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1006 # retrieve individual items
1007 # this query looks up all information about the items
1008 # stuff different from the whole will not be overwritten, but saved with a suffix.
1010 qq|SELECT o.id AS orderitems_id,
1011 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1012 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1013 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1014 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1015 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1016 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1017 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1018 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1019 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1020 pr.projectnumber, p.formel,
1021 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1023 JOIN parts p ON (o.parts_id = p.id)
1024 JOIN oe ON (o.trans_id = oe.id)
1025 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1026 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)
1027 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)
1028 LEFT JOIN project pr ON (o.project_id = pr.id)
1029 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1031 ? qq|WHERE o.trans_id = ?|
1032 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1033 qq|ORDER BY o.trans_id, o.position|;
1035 @ids = $form->{id} ? ($form->{id}) : @ids;
1036 $sth = prepare_execute_query($form, $dbh, $query, @values);
1038 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1039 # Retrieve custom variables.
1040 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1042 sub_module => 'orderitems',
1043 trans_id => $ref->{orderitems_id},
1045 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1048 if (!$ref->{"part_inventory_accno_id"}) {
1049 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1051 delete($ref->{"part_inventory_accno_id"});
1053 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1054 # unless already present there
1055 # remove _oe entries afterwards
1056 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1057 qw|ordnumber transdate cusordnumber|
1059 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1063 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1065 qq|SELECT accno AS inventory_accno, | .
1066 qq| new_chart_id AS inventory_new_chart, | .
1067 qq| date($transdate) - valid_from AS inventory_valid | .
1068 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1069 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1070 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1073 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1075 qq|SELECT accno AS income_accno, | .
1076 qq| new_chart_id AS income_new_chart, | .
1077 qq| date($transdate) - valid_from AS income_valid | .
1078 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1079 ($ref->{income_accno}, $ref->{income_new_chart},
1080 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1083 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1085 qq|SELECT accno AS expense_accno, | .
1086 qq| new_chart_id AS expense_new_chart, | .
1087 qq| date($transdate) - valid_from AS expense_valid | .
1088 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1089 ($ref->{expense_accno}, $ref->{expense_new_chart},
1090 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1093 # delete orderitems_id in collective orders, so that they get cloned no matter what
1094 # is this correct? or is the following meant?
1095 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1096 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1098 # get tax rates and description
1099 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1101 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1102 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1103 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1104 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1105 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1106 qq|ORDER BY c.accno|;
1107 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1108 $ref->{taxaccounts} = "";
1110 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1111 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1115 $ref->{taxaccounts} .= "$ptr->{accno} ";
1116 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1117 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1118 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1119 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1120 $form->{taxaccounts} .= "$ptr->{accno} ";
1125 chop $ref->{taxaccounts};
1127 push @{ $form->{form_details} }, $ref;
1134 # get last name used
1135 $form->lastname_used($dbh, $myconfig, $form->{vc})
1136 unless $form->{"$form->{vc}_id"};
1140 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1142 Common::webdav_folder($form);
1144 $self->load_periodic_invoice_config($form);
1146 my $rc = $dbh->commit;
1148 $main::lxdebug->leave_sub();
1153 sub retrieve_simple {
1154 $main::lxdebug->enter_sub();
1159 Common::check_params(\%params, qw(id));
1161 my $myconfig = \%main::myconfig;
1162 my $form = $main::form;
1164 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1166 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1167 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1169 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1170 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1172 $main::lxdebug->leave_sub();
1178 $main::lxdebug->enter_sub();
1180 my ($self, $myconfig, $form) = @_;
1182 # connect to database
1183 my $dbh = $form->get_standard_dbh;
1189 my $nodiscount_subtotal = 0;
1190 my $discount_subtotal = 0;
1193 my @partsgroup = ();
1196 my $subtotal_header = 0;
1197 my $subposition = 0;
1205 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1207 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1208 'departments' => 'ALL_DEPARTMENTS');
1211 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1212 $price_factors{$pfac->{id}} = $pfac;
1213 $pfac->{factor} *= 1;
1214 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1218 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1219 next unless $dept->{id} eq $form->{department_id};
1220 $form->{department} = $dept->{description};
1224 # sort items by partsgroup
1225 for $i (1 .. $form->{rowcount}) {
1227 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1228 $partsgroup = $form->{"partsgroup_$i"};
1230 push @partsgroup, [$i, $partsgroup];
1231 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1237 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1238 %projects_by_id = map { $_->id => $_ } @$projects;
1241 if ($projects_by_id{$form->{"globalproject_id"}}) {
1242 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1243 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1245 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1246 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1250 $form->{discount} = [];
1252 # get some values of parts from db on store them in extra array,
1253 # so that they can be sorted in later
1254 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1255 my @prepared_arrays = keys %prepared_template_arrays;
1257 $form->{TEMPLATE_ARRAYS} = { };
1259 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1260 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1263 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1264 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1265 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1266 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1267 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1269 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1270 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1272 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1274 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1276 my $totalweight = 0;
1278 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1281 if ($item->[1] ne $sameitem) {
1282 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1283 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1284 $sameitem = $item->[1];
1286 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1289 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1291 if ($form->{"id_$i"} != 0) {
1293 # add number, description and qty to $form->{number}, ....
1295 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1296 $subtotal_header = $i;
1297 $position = int($position);
1300 } elsif ($subtotal_header) {
1302 $position = int($position);
1303 $position = $position.".".$subposition;
1305 $position = int($position);
1309 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1311 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1313 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1314 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1315 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1316 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1317 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1318 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1319 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1320 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1321 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1322 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1323 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1324 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1325 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1326 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1327 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1328 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1329 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1330 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1331 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1332 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1333 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1335 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1336 my ($dec) = ($sellprice =~ /\.(\d+)/);
1337 my $decimalplaces = max 2, length($dec);
1339 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1341 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1342 my $linetotal = $form->round_amount($linetotal_exact, 2);
1344 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1345 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1347 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1349 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1351 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1353 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1354 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1356 $linetotal = ($linetotal != 0) ? $linetotal : '';
1358 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1359 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1360 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1362 $form->{ordtotal} += $linetotal;
1363 $form->{nodiscount_total} += $nodiscount_linetotal;
1364 $form->{discount_total} += $discount;
1366 if ($subtotal_header) {
1367 $discount_subtotal += $linetotal;
1368 $nodiscount_subtotal += $nodiscount_linetotal;
1371 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1372 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1373 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1374 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1375 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1377 $discount_subtotal = 0;
1378 $nodiscount_subtotal = 0;
1379 $subtotal_header = 0;
1382 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1385 if (!$form->{"discount_$i"}) {
1386 $nodiscount += $linetotal;
1389 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1391 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1392 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1393 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1394 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1395 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1396 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1398 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1399 $totalweight += $lineweight;
1400 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1401 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1402 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1403 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1405 my ($taxamount, $taxbase);
1408 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1410 if ($form->{taxincluded}) {
1413 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1414 $taxbase = $linetotal / (1 + $taxrate);
1416 $taxamount = $linetotal * $taxrate;
1417 $taxbase = $linetotal;
1420 if ($taxamount != 0) {
1421 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1422 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1423 $taxbase{$accno} += $taxbase;
1427 $tax_rate = $taxrate * 100;
1428 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1430 if ($form->{"assembly_$i"}) {
1433 # get parts and push them onto the stack
1435 if ($form->{groupitems}) {
1436 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1438 $sortorder = qq|ORDER BY a.oid|;
1441 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1442 qq|pg.partsgroup | .
1443 qq|FROM assembly a | .
1444 qq| JOIN parts p ON (a.parts_id = p.id) | .
1445 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1446 qq| WHERE a.bom = '1' | .
1447 qq| AND a.id = ? | . $sortorder;
1448 @values = ($form->{"id_$i"});
1449 $sth = $dbh->prepare($query);
1450 $sth->execute(@values) || $form->dberror($query);
1452 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1453 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1454 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1455 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1456 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1457 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1460 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1461 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1462 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1467 CVar->get_non_editable_ic_cvars(form => $form,
1470 sub_module => 'orderitems',
1471 may_converted_from => ['orderitems', 'invoice']);
1473 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1474 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1475 for @{ $ic_cvar_configs };
1477 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1481 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1482 $form->{totalweight_nofmt} = $totalweight;
1483 my $defaults = AM->get_defaults();
1484 $form->{weightunit} = $defaults->{weightunit};
1487 foreach $item (sort keys %taxaccounts) {
1488 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1490 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1491 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1492 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1493 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1494 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1495 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1496 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1498 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1499 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1500 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1503 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1504 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1505 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1506 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1508 if($form->{taxincluded}) {
1509 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1510 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1512 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1513 $form->{subtotal_nofmt} = $form->{ordtotal};
1516 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1519 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1521 if ($form->{type} =~ /_quotation/) {
1522 $form->set_payment_options($myconfig, $form->{quodate});
1524 $form->set_payment_options($myconfig, $form->{orddate});
1527 $form->{username} = $myconfig->{name};
1531 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1532 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1534 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1536 $main::lxdebug->leave_sub();
1539 sub project_description {
1540 $main::lxdebug->enter_sub();
1542 my ($self, $dbh, $id) = @_;
1544 my $query = qq|SELECT description FROM project WHERE id = ?|;
1545 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1547 $main::lxdebug->leave_sub();
1558 OE.pm - Order entry module
1562 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>.
1568 =item retrieve_simple PARAMS
1570 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1572 my $order = retrieve_simple(id => 2);