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;
48 use SL::HTML::Restrict;
55 $main::lxdebug->enter_sub();
57 my ($self, $myconfig, $form) = @_;
60 my $dbh = $form->get_standard_dbh;
63 my $ordnumber = 'ordnumber';
69 my ($periodic_invoices_columns, $periodic_invoices_joins);
71 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
73 if ($form->{type} =~ /_quotation$/) {
75 $ordnumber = 'quonumber';
77 } elsif ($form->{type} eq 'sales_order') {
78 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
79 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
82 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
86 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
88 SELECT from_id, ar.amount, ar.netamount FROM (
91 WHERE from_table = 'oe' AND to_table = 'ar'
93 SELECT rl1.from_id, rl2.to_id
95 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
96 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
98 LEFT JOIN ar ON ar.id = rl.to_id
100 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
101 $billed_amount{ $ref->{from_id}} += $ref->{amount};
102 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
107 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
108 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
109 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
110 qq| o.transaction_description, | .
111 qq| o.marge_total, o.marge_percent, | .
112 qq| o.itime::DATE AS insertdate, | .
113 qq| ex.$rate AS exchangerate, | .
114 qq| pr.projectnumber AS globalprojectnumber, | .
115 qq| e.name AS employee, s.name AS salesman, | .
116 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
117 qq| tz.description AS taxzone | .
118 $periodic_invoices_columns .
119 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
121 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
122 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
123 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
124 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
125 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
126 qq| AND ex.transdate = o.transdate) | .
127 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
128 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
129 qq|$periodic_invoices_joins | .
130 qq|WHERE (o.quotation = ?) |;
131 push(@values, $quotation);
133 my ($null, $split_department_id) = split /--/, $form->{department};
134 my $department_id = $form->{department_id} || $split_department_id;
135 if ($department_id) {
136 $query .= qq| AND o.department_id = ?|;
137 push(@values, $department_id);
140 if ($form->{"project_id"}) {
142 qq|AND ((globalproject_id = ?) OR EXISTS | .
143 qq| (SELECT * FROM orderitems oi | .
144 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
145 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
148 if ($form->{"projectnumber"}) {
150 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
151 SELECT * FROM orderitems oi
152 LEFT JOIN project proi ON proi.id = oi.project_id
153 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
156 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
159 if ($form->{"business_id"}) {
160 $query .= " AND ct.business_id = ?";
161 push(@values, $form->{"business_id"});
164 if ($form->{"${vc}_id"}) {
165 $query .= " AND o.${vc}_id = ?";
166 push(@values, $form->{"${vc}_id"});
168 } elsif ($form->{$vc}) {
169 $query .= " AND ct.name ILIKE ?";
170 push(@values, '%' . $form->{$vc} . '%');
173 if ($form->{"cp_name"}) {
174 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
175 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
178 if (!$main::auth->assert('sales_all_edit', 1)) {
179 $query .= " AND o.employee_id = (select id from employee where login= ?)";
180 push @values, $::myconfig{login};
182 if ($form->{employee_id}) {
183 $query .= " AND o.employee_id = ?";
184 push @values, conv_i($form->{employee_id});
187 if ($form->{salesman_id}) {
188 $query .= " AND o.salesman_id = ?";
189 push @values, conv_i($form->{salesman_id});
192 if (!$form->{open} && !$form->{closed}) {
193 $query .= " AND o.id = 0";
194 } elsif (!($form->{open} && $form->{closed})) {
195 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
198 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
199 ($form->{"notdelivered"} ne $form->{"delivered"})) {
200 $query .= $form->{"delivered"} ?
201 " AND o.delivered " : " AND NOT o.delivered";
204 if ($form->{$ordnumber}) {
205 $query .= qq| AND o.$ordnumber ILIKE ?|;
206 push(@values, '%' . $form->{$ordnumber} . '%');
209 if ($form->{cusordnumber}) {
210 $query .= qq| AND o.cusordnumber ILIKE ?|;
211 push(@values, '%' . $form->{cusordnumber} . '%');
214 if($form->{transdatefrom}) {
215 $query .= qq| AND o.transdate >= ?|;
216 push(@values, conv_date($form->{transdatefrom}));
219 if($form->{transdateto}) {
220 $query .= qq| AND o.transdate <= ?|;
221 push(@values, conv_date($form->{transdateto}));
224 if($form->{reqdatefrom}) {
225 $query .= qq| AND o.reqdate >= ?|;
226 push(@values, conv_date($form->{reqdatefrom}));
229 if($form->{reqdateto}) {
230 $query .= qq| AND o.reqdate <= ?|;
231 push(@values, conv_date($form->{reqdateto}));
234 if($form->{insertdatefrom}) {
235 $query .= qq| AND o.itime::DATE >= ?|;
236 push(@values, conv_date($form->{insertdatefrom}));
239 if($form->{insertdateto}) {
240 $query .= qq| AND o.itime::DATE <= ?|;
241 push(@values, conv_date($form->{insertdateto}));
244 if ($form->{shippingpoint}) {
245 $query .= qq| AND o.shippingpoint ILIKE ?|;
246 push(@values, '%' . $form->{shippingpoint} . '%');
249 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
250 $query .= qq| AND tz.id = ?|;
251 push(@values, $form->{taxzone_id});
254 if ($form->{transaction_description}) {
255 $query .= qq| AND o.transaction_description ILIKE ?|;
256 push(@values, '%' . $form->{transaction_description} . '%');
259 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
260 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
261 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
264 if ($form->{reqdate_unset_or_old}) {
265 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
268 if (($form->{order_probability_value} || '') ne '') {
269 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
270 $query .= qq| AND (o.order_probability ${op} ?)|;
271 push @values, $form->{order_probability_value};
274 if ($form->{expected_billing_date_from}) {
275 $query .= qq| AND (o.expected_billing_date >= ?)|;
276 push @values, conv_date($form->{expected_billing_date_from});
279 if ($form->{expected_billing_date_to}) {
280 $query .= qq| AND (o.expected_billing_date <= ?)|;
281 push @values, conv_date($form->{expected_billing_date_to});
284 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
285 'trans_id_field' => 'ct.id',
289 $query .= qq| AND ($cvar_where)|;
290 push @values, @cvar_values;
293 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
294 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
295 my %allowed_sort_columns = (
296 "transdate" => "o.transdate",
297 "reqdate" => "o.reqdate",
299 "ordnumber" => "o.ordnumber",
300 "cusordnumber" => "o.cusordnumber",
301 "quonumber" => "o.quonumber",
303 "employee" => "e.name",
304 "salesman" => "s.name",
305 "shipvia" => "o.shipvia",
306 "transaction_description" => "o.transaction_description",
307 "shippingpoint" => "o.shippingpoint",
308 "insertdate" => "o.itime",
309 "taxzone" => "tz.description",
311 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
312 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
314 $query .= qq| ORDER by | . $sortorder;
316 my $sth = $dbh->prepare($query);
317 $sth->execute(@values) ||
318 $form->dberror($query . " (" . join(", ", @values) . ")");
322 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
323 $ref->{billed_amount} = $billed_amount{$ref->{id}};
324 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
325 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
326 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
327 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
328 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
329 $id{ $ref->{id} } = $ref->{id};
334 $main::lxdebug->leave_sub();
337 sub transactions_for_todo_list {
338 $main::lxdebug->enter_sub();
343 my $myconfig = \%main::myconfig;
344 my $form = $main::form;
346 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
348 my $query = qq|SELECT id FROM employee WHERE login = ?|;
349 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
352 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
353 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
358 LEFT JOIN customer c ON (oe.customer_id = c.id)
359 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
360 LEFT JOIN employee e ON (oe.employee_id = e.id)
361 WHERE (COALESCE(quotation, FALSE) = TRUE)
362 AND (COALESCE(closed, FALSE) = FALSE)
363 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
364 AND NOT (oe.reqdate ISNULL)
365 AND (oe.reqdate < current_date)
368 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
370 $main::lxdebug->leave_sub();
376 $main::lxdebug->enter_sub();
378 my ($self, $myconfig, $form) = @_;
380 # connect to database, turn off autocommit
381 my $dbh = $form->get_standard_dbh;
382 my $restricter = SL::HTML::Restrict->create;
384 my ($query, @values, $sth, $null);
385 my $exchangerate = 0;
387 my $all_units = AM->retrieve_units($myconfig, $form);
388 $form->{all_units} = $all_units;
390 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
393 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
394 unless ($form->{employee_id}) {
395 $form->get_employee($dbh);
398 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
400 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
401 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
402 $form->{$number_field} ||= $trans_number->create_unique;
405 $query = qq|DELETE FROM shipto | .
406 qq|WHERE trans_id = ? AND module = 'OE'|;
407 do_query($form, $dbh, $query, $form->{id});
411 $query = qq|SELECT nextval('id')|;
412 ($form->{id}) = selectrow_query($form, $dbh, $query);
414 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
415 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
432 my @processed_orderitems;
434 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
435 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
438 for my $i (1 .. $form->{rowcount}) {
440 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
442 if ($form->{"id_$i"}) {
445 $query = qq|SELECT unit FROM parts WHERE id = ?|;
446 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
449 if (defined($all_units->{$item_unit}->{factor}) &&
450 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
451 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
453 my $baseqty = $form->{"qty_$i"} * $basefactor;
455 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
456 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
458 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
460 # keep entered selling price
462 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
464 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
466 my $decimalplaces = ($dec > 2) ? $dec : 2;
468 # undo discount formatting
469 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
472 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
474 # round linetotal at least to 2 decimal places
475 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
476 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
478 $form->{"inventory_accno_$i"} *= 1;
479 $form->{"expense_accno_$i"} *= 1;
481 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
485 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
487 if ($form->{taxincluded}) {
488 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
489 $taxbase = $linetotal - $taxamount;
491 # we are not keeping a natural price, do not round
492 $form->{"sellprice_$i"} =
493 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
495 $taxamount = $linetotal * $taxrate;
496 $taxbase = $linetotal;
499 if ($form->round_amount($taxrate, 7) == 0) {
500 if ($form->{taxincluded}) {
501 foreach my $item (@taxaccounts) {
502 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
503 $taxaccounts{$item} += $taxamount;
504 $taxdiff += $taxamount;
505 $taxbase{$item} += $taxbase;
507 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
509 foreach my $item (@taxaccounts) {
510 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
511 $taxbase{$item} += $taxbase;
515 foreach my $item (@taxaccounts) {
516 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
517 $taxbase{$item} += $taxbase;
521 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
523 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
525 # Get pricegroup_id and save it. Unfortunately the interface
526 # also uses ID "0" for signalling that none is selected, but "0"
527 # must not be stored in the database. Therefore we cannot simply
529 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
531 $pricegroup_id = undef if !$pricegroup_id;
533 CVar->get_non_editable_ic_cvars(form => $form,
536 sub_module => 'orderitems',
537 may_converted_from => ['orderitems', 'invoice']);
541 # save detail record in orderitems table
542 if (! $form->{"orderitems_id_$i"}) {
543 $query = qq|SELECT nextval('orderitemsid')|;
544 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
546 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
547 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
550 my $orderitems_id = $form->{"orderitems_id_$i"};
551 push @processed_orderitems, $orderitems_id;
554 UPDATE orderitems SET
555 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
556 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
557 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
558 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
559 active_price_source = ?, active_discount_source = ?,
560 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
564 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
565 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
566 $form->{"qty_$i"}, $baseqty,
567 $fxsellprice, $form->{"discount_$i"},
568 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
569 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
570 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
571 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
572 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
573 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
574 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
575 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
576 conv_i($orderitems_id),
579 do_query($form, $dbh, $query, @values);
581 $form->{"sellprice_$i"} = $fxsellprice;
582 $form->{"discount_$i"} *= 100;
584 CVar->save_custom_variables(module => 'IC',
585 sub_module => 'orderitems',
586 trans_id => $orderitems_id,
587 configs => $ic_cvar_configs,
589 name_prefix => 'ic_',
590 name_postfix => "_$i",
593 # link previous items with orderitems
594 foreach (qw(orderitems invoice)) {
595 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
596 RecordLinks->create_links('dbh' => $dbh,
599 'from_ids' => $form->{"converted_from_${_}_id_$i"},
600 'to_table' => 'orderitems',
601 'to_id' => $orderitems_id,
604 delete $form->{"converted_from_${_}_id_$i"};
609 # search for orphaned ids
610 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
611 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
612 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
614 if (scalar @orphaned_ids) {
615 # clean up orderitems
616 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
617 do_query($form, $dbh, $query, @orphaned_ids);
620 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
624 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
626 $amount = $form->round_amount($netamount + $tax, 2);
627 $netamount = $form->round_amount($netamount, 2);
629 if ($form->{currency} eq $form->{defaultcurrency}) {
630 $form->{exchangerate} = 1;
632 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
635 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
637 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
639 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
644 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
645 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
646 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
647 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
648 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
649 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
650 , order_probability = ?, expected_billing_date = ?
653 @values = ($form->{ordnumber} || '', $form->{quonumber},
654 $form->{cusordnumber}, conv_date($form->{transdate}),
655 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
656 $amount, $netamount, conv_date($reqdate),
657 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
658 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
659 $form->{currency}, $form->{closed} ? 't' : 'f',
660 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
661 $quotation, conv_i($form->{department_id}),
662 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
663 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
664 conv_i($form->{delivery_vendor_id}),
665 conv_i($form->{delivery_customer_id}),
666 conv_i($form->{delivery_term_id}),
667 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
668 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
669 $form->{transaction_description},
670 $form->{marge_total} * 1, $form->{marge_percent} * 1,
671 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
672 conv_i($form->{id}));
673 do_query($form, $dbh, $query, @values);
675 $form->{ordtotal} = $amount;
677 $form->{name} = $form->{ $form->{vc} };
678 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
681 if (!$form->{shipto_id}) {
682 $form->add_shipto($dbh, $form->{id}, "OE");
685 # save printed, emailed, queued
686 $form->save_status($dbh);
688 # Link this record to the records it was created from.
689 # check every record type we may link. i am not happy with converting the string to array back
690 # should be a array from the start (OE.pm -> retrieve).
691 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
692 $form->{convert_from_oe_ids} =~ s/^\s+//;
693 $form->{convert_from_oe_ids} =~ s/\s+$//;
694 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
695 delete $form->{convert_from_oe_ids};
696 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
697 foreach (qw(ar oe)) {
698 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
699 RecordLinks->create_links('dbh' => $dbh,
702 'from_ids' => $form->{"convert_from_${_}_ids"},
704 'to_id' => $form->{id},
706 delete $form->{"convert_from_${_}_ids"};
708 $self->_close_quotations_rfqs('dbh' => $dbh,
709 'from_id' => \@convert_from_oe_ids,
710 'to_id' => $form->{id}) if $_ eq 'oe';
713 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
714 if ($form->{vc} eq 'customer') {
715 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
717 if ($form->{vc} eq 'vendor') {
718 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
722 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
723 "quonumber" : "ordnumber"};
725 Common::webdav_folder($form);
727 my $rc = $dbh->commit;
729 $self->save_periodic_invoices_config(dbh => $dbh,
730 oe_id => $form->{id},
731 config_yaml => $form->{periodic_invoices_config})
732 if ($form->{type} eq 'sales_order');
734 $main::lxdebug->leave_sub();
739 sub save_periodic_invoices_config {
740 my ($self, %params) = @_;
742 return if !$params{oe_id};
744 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
745 return if 'HASH' ne ref $config;
747 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
748 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
749 $obj->update_attributes(%{ $config });
752 sub load_periodic_invoice_config {
756 delete $form->{periodic_invoices_config};
759 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
762 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
763 print printer_id copies) };
764 $form->{periodic_invoices_config} = YAML::Dump($config);
769 sub _close_quotations_rfqs {
770 $main::lxdebug->enter_sub();
775 Common::check_params(\%params, qw(from_id to_id));
777 my $myconfig = \%main::myconfig;
778 my $form = $main::form;
780 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
782 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
783 my $sth = prepare_query($form, $dbh, $query);
785 do_statement($form, $sth, $query, conv_i($params{to_id}));
787 my ($quotation) = $sth->fetchrow_array();
790 $main::lxdebug->leave_sub();
796 foreach my $from_id (@{ $params{from_id} }) {
797 $from_id = conv_i($from_id);
798 do_statement($form, $sth, $query, $from_id);
799 ($quotation) = $sth->fetchrow_array();
800 push @close_ids, $from_id if ($quotation);
805 if (scalar @close_ids) {
806 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
807 do_query($form, $dbh, $query, @close_ids);
809 $dbh->commit() unless ($params{dbh});
812 $main::lxdebug->leave_sub();
816 $main::lxdebug->enter_sub();
818 my ($self, $myconfig, $form) = @_;
820 my $rc = SL::DB::Order->new->db->with_transaction(sub {
821 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
823 SL::DB::Order->new(id => $form->{id})->delete;
825 my $spool = $::lx_office_conf{paths}->{spool};
826 unlink map { "$spool/$_" } @spoolfiles if $spool;
831 $main::lxdebug->leave_sub();
837 $main::lxdebug->enter_sub();
839 my ($self, $myconfig, $form) = @_;
841 # connect to database
842 my $dbh = $form->get_standard_dbh;
844 my ($query, $query_add, @values, @ids, $sth);
846 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
848 push @ids, $form->{"trans_id_$_"}
849 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
850 } (1 .. $form->{"rowcount"});
852 if ($form->{rowcount} && scalar @ids) {
853 $form->{convert_from_oe_ids} = join ' ', @ids;
856 # if called in multi id mode, and still only got one id, switch back to single id
857 if ($form->{"rowcount"} and $#ids == 0) {
858 $form->{"id"} = $ids[0];
860 delete $form->{convert_from_oe_ids};
863 # and remember for the rest of the function
864 my $is_collective_order = scalar @ids;
865 $form->{useasnew} = !!$is_collective_order;
868 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
869 my $next_workday = DateTime->today_local->add(days => $extra_days);
870 my $day_of_week = $next_workday->day_of_week;
872 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
874 $form->{transdate} = DateTime->today_local->to_kivitendo;
875 $form->{reqdate} = $next_workday->to_kivitendo;
878 # get default accounts
879 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
880 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
881 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
882 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
883 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
886 my $ref = selectfirst_hashref_query($form, $dbh, $query);
887 map { $form->{$_} = $ref->{$_} } keys %$ref;
889 $form->{currency} = $form->get_default_currency($myconfig);
891 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
892 # we come from invoices, feel free.
893 $form->{reqdate} = $form->{deliverydate}
894 if ( $form->{deliverydate}
895 and $form->{callback} =~ /action=ar_transactions/);
897 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
899 if ($form->{id} or @ids) {
901 # retrieve order for single id
902 # NOTE: this query is intended to fetch all information only ONCE.
903 # so if any of these infos is important (or even different) for any item,
904 # it will be killed out and then has to be fetched from the item scope query further down
906 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
907 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
908 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
909 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
910 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
911 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
912 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
913 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
914 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
916 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
917 LEFT JOIN employee e ON (o.employee_id = e.id)
918 LEFT JOIN department d ON (o.department_id = d.id) | .
921 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
923 @values = $form->{id} ? ($form->{id}) : @ids;
924 $sth = prepare_execute_query($form, $dbh, $query, @values);
926 $ref = $sth->fetchrow_hashref("NAME_lc");
929 map { $form->{$_} = $ref->{$_} } keys %$ref;
931 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
933 # set all entries for multiple ids blank that yield different information
934 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
935 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
939 # if not given, fill transdate with current_date
940 $form->{transdate} = $form->current_date($myconfig)
941 unless $form->{transdate};
945 if ($form->{delivery_customer_id}) {
946 $query = qq|SELECT name FROM customer WHERE id = ?|;
947 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
950 if ($form->{delivery_vendor_id}) {
951 $query = qq|SELECT name FROM customer WHERE id = ?|;
952 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
955 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
957 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
958 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
960 $ref = $sth->fetchrow_hashref("NAME_lc");
962 map { $form->{$_} = $ref->{$_} } keys %$ref;
965 # get printed, emailed and queued
966 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
967 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
969 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
970 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
971 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
972 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
975 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
978 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
980 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
982 # retrieve individual items
983 # this query looks up all information about the items
984 # stuff different from the whole will not be overwritten, but saved with a suffix.
986 qq|SELECT o.id AS orderitems_id,
987 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
988 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
989 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
990 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
991 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
992 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
993 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
994 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
995 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
996 pr.projectnumber, p.formel,
997 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
999 JOIN parts p ON (o.parts_id = p.id)
1000 JOIN oe ON (o.trans_id = oe.id)
1001 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1002 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)
1003 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)
1004 LEFT JOIN project pr ON (o.project_id = pr.id)
1005 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1007 ? qq|WHERE o.trans_id = ?|
1008 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1009 qq|ORDER BY o.trans_id, o.position|;
1011 @ids = $form->{id} ? ($form->{id}) : @ids;
1012 $sth = prepare_execute_query($form, $dbh, $query, @values);
1014 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1015 # Retrieve custom variables.
1016 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1018 sub_module => 'orderitems',
1019 trans_id => $ref->{orderitems_id},
1021 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1024 if (!$ref->{"part_inventory_accno_id"}) {
1025 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1027 delete($ref->{"part_inventory_accno_id"});
1029 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1030 # unless already present there
1031 # remove _oe entries afterwards
1032 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1033 qw|ordnumber transdate cusordnumber|
1035 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1039 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1041 qq|SELECT accno AS inventory_accno, | .
1042 qq| new_chart_id AS inventory_new_chart, | .
1043 qq| date($transdate) - valid_from AS inventory_valid | .
1044 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1045 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1046 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1049 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1051 qq|SELECT accno AS income_accno, | .
1052 qq| new_chart_id AS income_new_chart, | .
1053 qq| date($transdate) - valid_from AS income_valid | .
1054 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1055 ($ref->{income_accno}, $ref->{income_new_chart},
1056 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1059 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1061 qq|SELECT accno AS expense_accno, | .
1062 qq| new_chart_id AS expense_new_chart, | .
1063 qq| date($transdate) - valid_from AS expense_valid | .
1064 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1065 ($ref->{expense_accno}, $ref->{expense_new_chart},
1066 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1069 # delete orderitems_id in collective orders, so that they get cloned no matter what
1070 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1072 # get tax rates and description
1073 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1075 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1076 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1077 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1078 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1079 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1080 qq|ORDER BY c.accno|;
1081 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1082 $ref->{taxaccounts} = "";
1084 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1085 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1089 $ref->{taxaccounts} .= "$ptr->{accno} ";
1090 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1091 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1092 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1093 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1094 $form->{taxaccounts} .= "$ptr->{accno} ";
1099 chop $ref->{taxaccounts};
1101 push @{ $form->{form_details} }, $ref;
1108 # get last name used
1109 $form->lastname_used($dbh, $myconfig, $form->{vc})
1110 unless $form->{"$form->{vc}_id"};
1114 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1116 Common::webdav_folder($form);
1118 $self->load_periodic_invoice_config($form);
1120 my $rc = $dbh->commit;
1122 $main::lxdebug->leave_sub();
1127 sub retrieve_simple {
1128 $main::lxdebug->enter_sub();
1133 Common::check_params(\%params, qw(id));
1135 my $myconfig = \%main::myconfig;
1136 my $form = $main::form;
1138 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1140 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1141 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1143 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1144 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1146 $main::lxdebug->leave_sub();
1152 $main::lxdebug->enter_sub();
1154 my ($self, $myconfig, $form) = @_;
1156 # connect to database
1157 my $dbh = $form->get_standard_dbh;
1163 my $nodiscount_subtotal = 0;
1164 my $discount_subtotal = 0;
1167 my @partsgroup = ();
1170 my $subtotal_header = 0;
1171 my $subposition = 0;
1179 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1181 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1182 'departments' => 'ALL_DEPARTMENTS');
1185 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1186 $price_factors{$pfac->{id}} = $pfac;
1187 $pfac->{factor} *= 1;
1188 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1192 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1193 next unless $dept->{id} eq $form->{department_id};
1194 $form->{department} = $dept->{description};
1198 # sort items by partsgroup
1199 for $i (1 .. $form->{rowcount}) {
1201 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1202 $partsgroup = $form->{"partsgroup_$i"};
1204 push @partsgroup, [$i, $partsgroup];
1205 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1211 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1212 %projects_by_id = map { $_->id => $_ } @$projects;
1215 if ($projects_by_id{$form->{"globalproject_id"}}) {
1216 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1217 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1219 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1220 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1224 $form->{discount} = [];
1226 $form->{TEMPLATE_ARRAYS} = { };
1227 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1229 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1230 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1233 qw(runningnumber number description longdescription qty ship unit bin
1234 partnotes serialnumber reqdate sellprice listprice netprice
1235 discount p_discount discount_sub nodiscount_sub
1236 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1237 price_factor price_factor_name partsgroup weight lineweight);
1239 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1240 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1242 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1244 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1246 my $totalweight = 0;
1248 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1251 if ($item->[1] ne $sameitem) {
1252 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1253 $sameitem = $item->[1];
1255 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1258 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1260 if ($form->{"id_$i"} != 0) {
1262 # add number, description and qty to $form->{number}, ....
1264 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1265 $subtotal_header = $i;
1266 $position = int($position);
1269 } elsif ($subtotal_header) {
1271 $position = int($position);
1272 $position = $position.".".$subposition;
1274 $position = int($position);
1278 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1280 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1281 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1282 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1283 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1284 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1285 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1286 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1287 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1288 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1289 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1290 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1291 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1292 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1293 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1294 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1295 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
1296 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1297 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1298 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1300 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1301 my ($dec) = ($sellprice =~ /\.(\d+)/);
1302 my $decimalplaces = max 2, length($dec);
1304 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1306 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1307 my $linetotal = $form->round_amount($linetotal_exact, 2);
1309 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1310 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1312 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1314 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1316 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1318 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1319 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1321 $linetotal = ($linetotal != 0) ? $linetotal : '';
1323 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1324 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1325 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1327 $form->{ordtotal} += $linetotal;
1328 $form->{nodiscount_total} += $nodiscount_linetotal;
1329 $form->{discount_total} += $discount;
1331 if ($subtotal_header) {
1332 $discount_subtotal += $linetotal;
1333 $nodiscount_subtotal += $nodiscount_linetotal;
1336 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1337 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1338 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1339 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1340 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1342 $discount_subtotal = 0;
1343 $nodiscount_subtotal = 0;
1344 $subtotal_header = 0;
1347 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1350 if (!$form->{"discount_$i"}) {
1351 $nodiscount += $linetotal;
1354 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1356 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1357 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1358 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1359 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1360 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1361 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1363 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1364 $totalweight += $lineweight;
1365 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1366 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1367 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1368 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1370 my ($taxamount, $taxbase);
1373 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1375 if ($form->{taxincluded}) {
1378 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1379 $taxbase = $linetotal / (1 + $taxrate);
1381 $taxamount = $linetotal * $taxrate;
1382 $taxbase = $linetotal;
1385 if ($taxamount != 0) {
1386 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1387 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1388 $taxbase{$accno} += $taxbase;
1392 $tax_rate = $taxrate * 100;
1393 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1395 if ($form->{"assembly_$i"}) {
1398 # get parts and push them onto the stack
1400 if ($form->{groupitems}) {
1401 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1403 $sortorder = qq|ORDER BY a.oid|;
1406 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1407 qq|pg.partsgroup | .
1408 qq|FROM assembly a | .
1409 qq| JOIN parts p ON (a.parts_id = p.id) | .
1410 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1411 qq| WHERE a.bom = '1' | .
1412 qq| AND a.id = ? | . $sortorder;
1413 @values = ($form->{"id_$i"});
1414 $sth = $dbh->prepare($query);
1415 $sth->execute(@values) || $form->dberror($query);
1417 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1418 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1419 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1420 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1421 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1424 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1425 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1430 CVar->get_non_editable_ic_cvars(form => $form,
1433 sub_module => 'orderitems',
1434 may_converted_from => ['orderitems', 'invoice']);
1436 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1437 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1438 for @{ $ic_cvar_configs };
1440 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1444 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1445 $form->{totalweight_nofmt} = $totalweight;
1446 my $defaults = AM->get_defaults();
1447 $form->{weightunit} = $defaults->{weightunit};
1450 foreach $item (sort keys %taxaccounts) {
1451 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1453 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1454 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1455 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1456 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1457 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1458 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1459 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1461 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1462 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1463 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1466 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1467 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1468 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1469 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1471 if($form->{taxincluded}) {
1472 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1473 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1475 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1476 $form->{subtotal_nofmt} = $form->{ordtotal};
1479 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1482 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1484 if ($form->{type} =~ /_quotation/) {
1485 $form->set_payment_options($myconfig, $form->{quodate});
1487 $form->set_payment_options($myconfig, $form->{orddate});
1490 $form->{username} = $myconfig->{name};
1494 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1495 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1497 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1499 $main::lxdebug->leave_sub();
1502 sub project_description {
1503 $main::lxdebug->enter_sub();
1505 my ($self, $dbh, $id) = @_;
1507 my $query = qq|SELECT description FROM project WHERE id = ?|;
1508 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1510 $main::lxdebug->leave_sub();
1521 OE.pm - Order entry module
1525 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>.
1531 =item retrieve_simple PARAMS
1533 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1535 my $order = retrieve_simple(id => 2);