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| pt.description AS payment_terms, | .
115 qq| pr.projectnumber AS globalprojectnumber, | .
116 qq| e.name AS employee, s.name AS salesman, | .
117 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
118 qq| tz.description AS taxzone | .
119 $periodic_invoices_columns .
120 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
122 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
123 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
124 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
125 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
126 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
127 qq| AND ex.transdate = o.transdate) | .
128 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
129 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
130 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
131 qq|$periodic_invoices_joins | .
132 qq|WHERE (o.quotation = ?) |;
133 push(@values, $quotation);
135 my ($null, $split_department_id) = split /--/, $form->{department};
136 my $department_id = $form->{department_id} || $split_department_id;
137 if ($department_id) {
138 $query .= qq| AND o.department_id = ?|;
139 push(@values, $department_id);
142 if ($form->{"project_id"}) {
144 qq|AND ((globalproject_id = ?) OR EXISTS | .
145 qq| (SELECT * FROM orderitems oi | .
146 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
147 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
150 if ($form->{"projectnumber"}) {
152 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
153 SELECT * FROM orderitems oi
154 LEFT JOIN project proi ON proi.id = oi.project_id
155 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
158 push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
161 if ($form->{"business_id"}) {
162 $query .= " AND ct.business_id = ?";
163 push(@values, $form->{"business_id"});
166 if ($form->{"${vc}_id"}) {
167 $query .= " AND o.${vc}_id = ?";
168 push(@values, $form->{"${vc}_id"});
170 } elsif ($form->{$vc}) {
171 $query .= " AND ct.name ILIKE ?";
172 push(@values, '%' . $form->{$vc} . '%');
175 if ($form->{"cp_name"}) {
176 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
177 push(@values, ('%' . $form->{"cp_name"} . '%')x2);
180 if (!$main::auth->assert('sales_all_edit', 1)) {
181 $query .= " AND o.employee_id = (select id from employee where login= ?)";
182 push @values, $::myconfig{login};
184 if ($form->{employee_id}) {
185 $query .= " AND o.employee_id = ?";
186 push @values, conv_i($form->{employee_id});
189 if ($form->{salesman_id}) {
190 $query .= " AND o.salesman_id = ?";
191 push @values, conv_i($form->{salesman_id});
194 if (!$form->{open} && !$form->{closed}) {
195 $query .= " AND o.id = 0";
196 } elsif (!($form->{open} && $form->{closed})) {
197 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
200 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
201 ($form->{"notdelivered"} ne $form->{"delivered"})) {
202 $query .= $form->{"delivered"} ?
203 " AND o.delivered " : " AND NOT o.delivered";
206 if ($form->{$ordnumber}) {
207 $query .= qq| AND o.$ordnumber ILIKE ?|;
208 push(@values, '%' . $form->{$ordnumber} . '%');
211 if ($form->{cusordnumber}) {
212 $query .= qq| AND o.cusordnumber ILIKE ?|;
213 push(@values, '%' . $form->{cusordnumber} . '%');
216 if($form->{transdatefrom}) {
217 $query .= qq| AND o.transdate >= ?|;
218 push(@values, conv_date($form->{transdatefrom}));
221 if($form->{transdateto}) {
222 $query .= qq| AND o.transdate <= ?|;
223 push(@values, conv_date($form->{transdateto}));
226 if($form->{reqdatefrom}) {
227 $query .= qq| AND o.reqdate >= ?|;
228 push(@values, conv_date($form->{reqdatefrom}));
231 if($form->{reqdateto}) {
232 $query .= qq| AND o.reqdate <= ?|;
233 push(@values, conv_date($form->{reqdateto}));
236 if($form->{insertdatefrom}) {
237 $query .= qq| AND o.itime::DATE >= ?|;
238 push(@values, conv_date($form->{insertdatefrom}));
241 if($form->{insertdateto}) {
242 $query .= qq| AND o.itime::DATE <= ?|;
243 push(@values, conv_date($form->{insertdateto}));
246 if ($form->{shippingpoint}) {
247 $query .= qq| AND o.shippingpoint ILIKE ?|;
248 push(@values, '%' . $form->{shippingpoint} . '%');
251 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
252 $query .= qq| AND tz.id = ?|;
253 push(@values, $form->{taxzone_id});
256 if ($form->{transaction_description}) {
257 $query .= qq| AND o.transaction_description ILIKE ?|;
258 push(@values, '%' . $form->{transaction_description} . '%');
261 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
262 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
263 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
266 if ($form->{reqdate_unset_or_old}) {
267 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
270 if (($form->{order_probability_value} || '') ne '') {
271 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
272 $query .= qq| AND (o.order_probability ${op} ?)|;
273 push @values, $form->{order_probability_value};
276 if ($form->{expected_billing_date_from}) {
277 $query .= qq| AND (o.expected_billing_date >= ?)|;
278 push @values, conv_date($form->{expected_billing_date_from});
281 if ($form->{expected_billing_date_to}) {
282 $query .= qq| AND (o.expected_billing_date <= ?)|;
283 push @values, conv_date($form->{expected_billing_date_to});
286 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
287 'trans_id_field' => 'ct.id',
291 $query .= qq| AND ($cvar_where)|;
292 push @values, @cvar_values;
295 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
296 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
297 my %allowed_sort_columns = (
298 "transdate" => "o.transdate",
299 "reqdate" => "o.reqdate",
301 "ordnumber" => "o.ordnumber",
302 "cusordnumber" => "o.cusordnumber",
303 "quonumber" => "o.quonumber",
305 "employee" => "e.name",
306 "salesman" => "s.name",
307 "shipvia" => "o.shipvia",
308 "transaction_description" => "o.transaction_description",
309 "shippingpoint" => "o.shippingpoint",
310 "insertdate" => "o.itime",
311 "taxzone" => "tz.description",
312 "payment_terms" => "pt.description",
314 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
315 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
317 $query .= qq| ORDER by | . $sortorder;
319 my $sth = $dbh->prepare($query);
320 $sth->execute(@values) ||
321 $form->dberror($query . " (" . join(", ", @values) . ")");
325 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
326 $ref->{billed_amount} = $billed_amount{$ref->{id}};
327 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
328 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
329 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
330 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
331 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
332 $id{ $ref->{id} } = $ref->{id};
337 $main::lxdebug->leave_sub();
340 sub transactions_for_todo_list {
341 $main::lxdebug->enter_sub();
346 my $myconfig = \%main::myconfig;
347 my $form = $main::form;
349 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
351 my $query = qq|SELECT id FROM employee WHERE login = ?|;
352 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
355 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
356 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
361 LEFT JOIN customer c ON (oe.customer_id = c.id)
362 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
363 LEFT JOIN employee e ON (oe.employee_id = e.id)
364 WHERE (COALESCE(quotation, FALSE) = TRUE)
365 AND (COALESCE(closed, FALSE) = FALSE)
366 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
367 AND NOT (oe.reqdate ISNULL)
368 AND (oe.reqdate < current_date)
371 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
373 $main::lxdebug->leave_sub();
379 $main::lxdebug->enter_sub();
381 my ($self, $myconfig, $form) = @_;
383 # connect to database, turn off autocommit
384 my $dbh = $form->get_standard_dbh;
385 my $restricter = SL::HTML::Restrict->create;
387 my ($query, @values, $sth, $null);
388 my $exchangerate = 0;
390 my $all_units = AM->retrieve_units($myconfig, $form);
391 $form->{all_units} = $all_units;
393 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
396 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
397 unless ($form->{employee_id}) {
398 $form->get_employee($dbh);
401 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
403 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
404 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
405 $form->{$number_field} ||= $trans_number->create_unique;
408 $query = qq|DELETE FROM shipto | .
409 qq|WHERE trans_id = ? AND module = 'OE'|;
410 do_query($form, $dbh, $query, $form->{id});
414 $query = qq|SELECT nextval('id')|;
415 ($form->{id}) = selectrow_query($form, $dbh, $query);
417 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
418 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
435 my @processed_orderitems;
437 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
438 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
441 for my $i (1 .. $form->{rowcount}) {
443 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
445 if ($form->{"id_$i"}) {
448 $query = qq|SELECT unit FROM parts WHERE id = ?|;
449 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
452 if (defined($all_units->{$item_unit}->{factor}) &&
453 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
454 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
456 my $baseqty = $form->{"qty_$i"} * $basefactor;
458 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
459 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
461 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
463 # keep entered selling price
465 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
467 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
469 my $decimalplaces = ($dec > 2) ? $dec : 2;
471 # undo discount formatting
472 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
475 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
477 # round linetotal at least to 2 decimal places
478 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
479 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
481 $form->{"inventory_accno_$i"} *= 1;
482 $form->{"expense_accno_$i"} *= 1;
484 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
488 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
490 if ($form->{taxincluded}) {
491 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
492 $taxbase = $linetotal - $taxamount;
494 # we are not keeping a natural price, do not round
495 $form->{"sellprice_$i"} =
496 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
498 $taxamount = $linetotal * $taxrate;
499 $taxbase = $linetotal;
502 if ($form->round_amount($taxrate, 7) == 0) {
503 if ($form->{taxincluded}) {
504 foreach my $item (@taxaccounts) {
505 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
506 $taxaccounts{$item} += $taxamount;
507 $taxdiff += $taxamount;
508 $taxbase{$item} += $taxbase;
510 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
512 foreach my $item (@taxaccounts) {
513 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
514 $taxbase{$item} += $taxbase;
518 foreach my $item (@taxaccounts) {
519 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
520 $taxbase{$item} += $taxbase;
524 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
526 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
528 # Get pricegroup_id and save it. Unfortunately the interface
529 # also uses ID "0" for signalling that none is selected, but "0"
530 # must not be stored in the database. Therefore we cannot simply
532 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
534 $pricegroup_id = undef if !$pricegroup_id;
536 CVar->get_non_editable_ic_cvars(form => $form,
539 sub_module => 'orderitems',
540 may_converted_from => ['orderitems', 'invoice']);
544 # save detail record in orderitems table
545 if (! $form->{"orderitems_id_$i"}) {
546 $query = qq|SELECT nextval('orderitemsid')|;
547 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
549 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
550 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
553 my $orderitems_id = $form->{"orderitems_id_$i"};
554 push @processed_orderitems, $orderitems_id;
557 UPDATE orderitems SET
558 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
559 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
560 pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
561 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
562 active_price_source = ?, active_discount_source = ?,
563 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
567 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
568 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
569 $form->{"qty_$i"}, $baseqty,
570 $fxsellprice, $form->{"discount_$i"},
571 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
572 $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
573 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
574 $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
575 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
576 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
577 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
578 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
579 conv_i($orderitems_id),
582 do_query($form, $dbh, $query, @values);
584 $form->{"sellprice_$i"} = $fxsellprice;
585 $form->{"discount_$i"} *= 100;
587 CVar->save_custom_variables(module => 'IC',
588 sub_module => 'orderitems',
589 trans_id => $orderitems_id,
590 configs => $ic_cvar_configs,
592 name_prefix => 'ic_',
593 name_postfix => "_$i",
596 # link previous items with orderitems
597 foreach (qw(orderitems invoice)) {
598 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
599 RecordLinks->create_links('dbh' => $dbh,
602 'from_ids' => $form->{"converted_from_${_}_id_$i"},
603 'to_table' => 'orderitems',
604 'to_id' => $orderitems_id,
607 delete $form->{"converted_from_${_}_id_$i"};
612 # search for orphaned ids
613 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
614 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
615 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
617 if (scalar @orphaned_ids) {
618 # clean up orderitems
619 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
620 do_query($form, $dbh, $query, @orphaned_ids);
623 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
627 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
629 $amount = $form->round_amount($netamount + $tax, 2);
630 $netamount = $form->round_amount($netamount, 2);
632 if ($form->{currency} eq $form->{defaultcurrency}) {
633 $form->{exchangerate} = 1;
635 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
638 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
640 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
642 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
647 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
648 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
649 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
650 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
651 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
652 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
653 , order_probability = ?, expected_billing_date = ?
656 @values = ($form->{ordnumber} || '', $form->{quonumber},
657 $form->{cusordnumber}, conv_date($form->{transdate}),
658 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
659 $amount, $netamount, conv_date($reqdate),
660 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
661 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
662 $form->{currency}, $form->{closed} ? 't' : 'f',
663 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
664 $quotation, conv_i($form->{department_id}),
665 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
666 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
667 conv_i($form->{delivery_vendor_id}),
668 conv_i($form->{delivery_customer_id}),
669 conv_i($form->{delivery_term_id}),
670 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
671 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
672 $form->{transaction_description},
673 $form->{marge_total} * 1, $form->{marge_percent} * 1,
674 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
675 conv_i($form->{id}));
676 do_query($form, $dbh, $query, @values);
678 $form->{ordtotal} = $amount;
680 $form->{name} = $form->{ $form->{vc} };
681 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
684 if (!$form->{shipto_id}) {
685 $form->add_shipto($dbh, $form->{id}, "OE");
688 # save printed, emailed, queued
689 $form->save_status($dbh);
691 # Link this record to the records it was created from.
692 # check every record type we may link. i am not happy with converting the string to array back
693 # should be a array from the start (OE.pm -> retrieve).
694 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
695 $form->{convert_from_oe_ids} =~ s/^\s+//;
696 $form->{convert_from_oe_ids} =~ s/\s+$//;
697 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
698 delete $form->{convert_from_oe_ids};
699 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
700 foreach (qw(ar oe)) {
701 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
702 RecordLinks->create_links('dbh' => $dbh,
705 'from_ids' => $form->{"convert_from_${_}_ids"},
707 'to_id' => $form->{id},
709 delete $form->{"convert_from_${_}_ids"};
711 $self->_close_quotations_rfqs('dbh' => $dbh,
712 'from_id' => \@convert_from_oe_ids,
713 'to_id' => $form->{id}) if $_ eq 'oe';
716 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
717 if ($form->{vc} eq 'customer') {
718 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
720 if ($form->{vc} eq 'vendor') {
721 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
725 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
726 "quonumber" : "ordnumber"};
728 Common::webdav_folder($form);
730 my $rc = $dbh->commit;
732 $self->save_periodic_invoices_config(dbh => $dbh,
733 oe_id => $form->{id},
734 config_yaml => $form->{periodic_invoices_config})
735 if ($form->{type} eq 'sales_order');
737 $main::lxdebug->leave_sub();
742 sub save_periodic_invoices_config {
743 my ($self, %params) = @_;
745 return if !$params{oe_id};
747 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
748 return if 'HASH' ne ref $config;
750 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
751 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
752 $obj->update_attributes(%{ $config });
755 sub load_periodic_invoice_config {
759 delete $form->{periodic_invoices_config};
762 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
765 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
766 print printer_id copies) };
767 $form->{periodic_invoices_config} = YAML::Dump($config);
772 sub _close_quotations_rfqs {
773 $main::lxdebug->enter_sub();
778 Common::check_params(\%params, qw(from_id to_id));
780 my $myconfig = \%main::myconfig;
781 my $form = $main::form;
783 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
785 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
786 my $sth = prepare_query($form, $dbh, $query);
788 do_statement($form, $sth, $query, conv_i($params{to_id}));
790 my ($quotation) = $sth->fetchrow_array();
793 $main::lxdebug->leave_sub();
799 foreach my $from_id (@{ $params{from_id} }) {
800 $from_id = conv_i($from_id);
801 do_statement($form, $sth, $query, $from_id);
802 ($quotation) = $sth->fetchrow_array();
803 push @close_ids, $from_id if ($quotation);
808 if (scalar @close_ids) {
809 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
810 do_query($form, $dbh, $query, @close_ids);
812 $dbh->commit() unless ($params{dbh});
815 $main::lxdebug->leave_sub();
819 $main::lxdebug->enter_sub();
821 my ($self, $myconfig, $form) = @_;
823 my $rc = SL::DB::Order->new->db->with_transaction(sub {
824 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
826 SL::DB::Order->new(id => $form->{id})->delete;
828 my $spool = $::lx_office_conf{paths}->{spool};
829 unlink map { "$spool/$_" } @spoolfiles if $spool;
834 $main::lxdebug->leave_sub();
840 $main::lxdebug->enter_sub();
842 my ($self, $myconfig, $form) = @_;
844 # connect to database
845 my $dbh = $form->get_standard_dbh;
847 my ($query, $query_add, @values, @ids, $sth);
849 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
851 push @ids, $form->{"trans_id_$_"}
852 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
853 } (1 .. $form->{"rowcount"});
855 if ($form->{rowcount} && scalar @ids) {
856 $form->{convert_from_oe_ids} = join ' ', @ids;
859 # if called in multi id mode, and still only got one id, switch back to single id
860 if ($form->{"rowcount"} and $#ids == 0) {
861 $form->{"id"} = $ids[0];
863 delete $form->{convert_from_oe_ids};
866 # and remember for the rest of the function
867 my $is_collective_order = scalar @ids;
868 $form->{useasnew} = !!$is_collective_order;
871 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
872 my $next_workday = DateTime->today_local->add(days => $extra_days);
873 my $day_of_week = $next_workday->day_of_week;
875 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
877 $form->{transdate} = DateTime->today_local->to_kivitendo;
878 $form->{reqdate} = $next_workday->to_kivitendo;
881 # get default accounts
882 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
883 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
884 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
885 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
886 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
889 my $ref = selectfirst_hashref_query($form, $dbh, $query);
890 map { $form->{$_} = $ref->{$_} } keys %$ref;
892 $form->{currency} = $form->get_default_currency($myconfig);
894 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
895 # we come from invoices, feel free.
896 $form->{reqdate} = $form->{deliverydate}
897 if ( $form->{deliverydate}
898 and $form->{callback} =~ /action=ar_transactions/);
900 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
902 if ($form->{id} or @ids) {
904 # retrieve order for single id
905 # NOTE: this query is intended to fetch all information only ONCE.
906 # so if any of these infos is important (or even different) for any item,
907 # it will be killed out and then has to be fetched from the item scope query further down
909 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
910 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
911 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
912 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
913 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
914 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
915 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
916 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
917 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
919 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
920 LEFT JOIN employee e ON (o.employee_id = e.id)
921 LEFT JOIN department d ON (o.department_id = d.id) | .
924 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
926 @values = $form->{id} ? ($form->{id}) : @ids;
927 $sth = prepare_execute_query($form, $dbh, $query, @values);
929 $ref = $sth->fetchrow_hashref("NAME_lc");
932 map { $form->{$_} = $ref->{$_} } keys %$ref;
934 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
936 # set all entries for multiple ids blank that yield different information
937 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
938 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
942 # if not given, fill transdate with current_date
943 $form->{transdate} = $form->current_date($myconfig)
944 unless $form->{transdate};
948 if ($form->{delivery_customer_id}) {
949 $query = qq|SELECT name FROM customer WHERE id = ?|;
950 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
953 if ($form->{delivery_vendor_id}) {
954 $query = qq|SELECT name FROM customer WHERE id = ?|;
955 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
958 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
960 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
961 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
963 $ref = $sth->fetchrow_hashref("NAME_lc");
965 map { $form->{$_} = $ref->{$_} } keys %$ref;
968 # get printed, emailed and queued
969 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
970 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
972 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
973 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
974 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
975 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
978 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
981 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
983 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
985 # retrieve individual items
986 # this query looks up all information about the items
987 # stuff different from the whole will not be overwritten, but saved with a suffix.
989 qq|SELECT o.id AS orderitems_id,
990 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
991 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
992 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
993 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
994 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
995 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
996 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
997 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
998 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
999 pr.projectnumber, p.formel,
1000 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1002 JOIN parts p ON (o.parts_id = p.id)
1003 JOIN oe ON (o.trans_id = oe.id)
1004 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1005 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)
1006 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)
1007 LEFT JOIN project pr ON (o.project_id = pr.id)
1008 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1010 ? qq|WHERE o.trans_id = ?|
1011 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1012 qq|ORDER BY o.trans_id, o.position|;
1014 @ids = $form->{id} ? ($form->{id}) : @ids;
1015 $sth = prepare_execute_query($form, $dbh, $query, @values);
1017 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1018 # Retrieve custom variables.
1019 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1021 sub_module => 'orderitems',
1022 trans_id => $ref->{orderitems_id},
1024 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1027 if (!$ref->{"part_inventory_accno_id"}) {
1028 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1030 delete($ref->{"part_inventory_accno_id"});
1032 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1033 # unless already present there
1034 # remove _oe entries afterwards
1035 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1036 qw|ordnumber transdate cusordnumber|
1038 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1042 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1044 qq|SELECT accno AS inventory_accno, | .
1045 qq| new_chart_id AS inventory_new_chart, | .
1046 qq| date($transdate) - valid_from AS inventory_valid | .
1047 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1048 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1049 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1052 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1054 qq|SELECT accno AS income_accno, | .
1055 qq| new_chart_id AS income_new_chart, | .
1056 qq| date($transdate) - valid_from AS income_valid | .
1057 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1058 ($ref->{income_accno}, $ref->{income_new_chart},
1059 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1062 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1064 qq|SELECT accno AS expense_accno, | .
1065 qq| new_chart_id AS expense_new_chart, | .
1066 qq| date($transdate) - valid_from AS expense_valid | .
1067 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1068 ($ref->{expense_accno}, $ref->{expense_new_chart},
1069 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1072 # delete orderitems_id in collective orders, so that they get cloned no matter what
1073 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1075 # get tax rates and description
1076 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1078 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1079 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1080 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1081 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1082 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1083 qq|ORDER BY c.accno|;
1084 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1085 $ref->{taxaccounts} = "";
1087 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1088 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1092 $ref->{taxaccounts} .= "$ptr->{accno} ";
1093 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1094 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1095 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1096 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1097 $form->{taxaccounts} .= "$ptr->{accno} ";
1102 chop $ref->{taxaccounts};
1104 push @{ $form->{form_details} }, $ref;
1111 # get last name used
1112 $form->lastname_used($dbh, $myconfig, $form->{vc})
1113 unless $form->{"$form->{vc}_id"};
1117 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1119 Common::webdav_folder($form);
1121 $self->load_periodic_invoice_config($form);
1123 my $rc = $dbh->commit;
1125 $main::lxdebug->leave_sub();
1130 sub retrieve_simple {
1131 $main::lxdebug->enter_sub();
1136 Common::check_params(\%params, qw(id));
1138 my $myconfig = \%main::myconfig;
1139 my $form = $main::form;
1141 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1143 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1144 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1146 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1147 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1149 $main::lxdebug->leave_sub();
1155 $main::lxdebug->enter_sub();
1157 my ($self, $myconfig, $form) = @_;
1159 # connect to database
1160 my $dbh = $form->get_standard_dbh;
1166 my $nodiscount_subtotal = 0;
1167 my $discount_subtotal = 0;
1170 my @partsgroup = ();
1173 my $subtotal_header = 0;
1174 my $subposition = 0;
1182 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1184 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1185 'departments' => 'ALL_DEPARTMENTS');
1188 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1189 $price_factors{$pfac->{id}} = $pfac;
1190 $pfac->{factor} *= 1;
1191 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1195 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1196 next unless $dept->{id} eq $form->{department_id};
1197 $form->{department} = $dept->{description};
1201 # sort items by partsgroup
1202 for $i (1 .. $form->{rowcount}) {
1204 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1205 $partsgroup = $form->{"partsgroup_$i"};
1207 push @partsgroup, [$i, $partsgroup];
1208 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1214 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1215 %projects_by_id = map { $_->id => $_ } @$projects;
1218 if ($projects_by_id{$form->{"globalproject_id"}}) {
1219 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1220 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1222 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1223 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1227 $form->{discount} = [];
1229 $form->{TEMPLATE_ARRAYS} = { };
1230 IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1232 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1233 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1236 qw(runningnumber number description longdescription qty ship unit bin
1237 partnotes serialnumber reqdate sellprice listprice netprice
1238 discount p_discount discount_sub nodiscount_sub
1239 linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
1240 price_factor price_factor_name partsgroup weight lineweight);
1242 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1243 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1245 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1247 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
1249 my $totalweight = 0;
1251 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1254 if ($item->[1] ne $sameitem) {
1255 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1256 $sameitem = $item->[1];
1258 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1261 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1263 if ($form->{"id_$i"} != 0) {
1265 # add number, description and qty to $form->{number}, ....
1267 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1268 $subtotal_header = $i;
1269 $position = int($position);
1272 } elsif ($subtotal_header) {
1274 $position = int($position);
1275 $position = $position.".".$subposition;
1277 $position = int($position);
1281 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1283 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1284 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1285 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1286 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1287 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1288 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1289 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1290 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1291 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1292 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1293 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1294 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1295 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1296 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1297 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1298 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1299 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1300 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1301 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1302 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1304 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1305 my ($dec) = ($sellprice =~ /\.(\d+)/);
1306 my $decimalplaces = max 2, length($dec);
1308 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1310 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1311 my $linetotal = $form->round_amount($linetotal_exact, 2);
1313 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1314 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1316 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1318 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1320 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1322 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1323 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1325 $linetotal = ($linetotal != 0) ? $linetotal : '';
1327 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1328 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1329 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1331 $form->{ordtotal} += $linetotal;
1332 $form->{nodiscount_total} += $nodiscount_linetotal;
1333 $form->{discount_total} += $discount;
1335 if ($subtotal_header) {
1336 $discount_subtotal += $linetotal;
1337 $nodiscount_subtotal += $nodiscount_linetotal;
1340 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1341 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1342 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1343 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1344 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1346 $discount_subtotal = 0;
1347 $nodiscount_subtotal = 0;
1348 $subtotal_header = 0;
1351 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1354 if (!$form->{"discount_$i"}) {
1355 $nodiscount += $linetotal;
1358 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1360 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1361 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1362 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1363 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1364 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1365 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1367 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1368 $totalweight += $lineweight;
1369 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1370 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1371 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1372 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1374 my ($taxamount, $taxbase);
1377 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1379 if ($form->{taxincluded}) {
1382 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1383 $taxbase = $linetotal / (1 + $taxrate);
1385 $taxamount = $linetotal * $taxrate;
1386 $taxbase = $linetotal;
1389 if ($taxamount != 0) {
1390 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1391 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1392 $taxbase{$accno} += $taxbase;
1396 $tax_rate = $taxrate * 100;
1397 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1399 if ($form->{"assembly_$i"}) {
1402 # get parts and push them onto the stack
1404 if ($form->{groupitems}) {
1405 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1407 $sortorder = qq|ORDER BY a.oid|;
1410 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1411 qq|pg.partsgroup | .
1412 qq|FROM assembly a | .
1413 qq| JOIN parts p ON (a.parts_id = p.id) | .
1414 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1415 qq| WHERE a.bom = '1' | .
1416 qq| AND a.id = ? | . $sortorder;
1417 @values = ($form->{"id_$i"});
1418 $sth = $dbh->prepare($query);
1419 $sth->execute(@values) || $form->dberror($query);
1421 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1422 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1423 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1424 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1425 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1428 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1429 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
1434 CVar->get_non_editable_ic_cvars(form => $form,
1437 sub_module => 'orderitems',
1438 may_converted_from => ['orderitems', 'invoice']);
1440 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1441 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1442 for @{ $ic_cvar_configs };
1444 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1448 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1449 $form->{totalweight_nofmt} = $totalweight;
1450 my $defaults = AM->get_defaults();
1451 $form->{weightunit} = $defaults->{weightunit};
1454 foreach $item (sort keys %taxaccounts) {
1455 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1457 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1458 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1459 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1460 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1461 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1462 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1463 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1465 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1466 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1467 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1470 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1471 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1472 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1473 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1475 if($form->{taxincluded}) {
1476 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1477 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1479 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1480 $form->{subtotal_nofmt} = $form->{ordtotal};
1483 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1486 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1488 if ($form->{type} =~ /_quotation/) {
1489 $form->set_payment_options($myconfig, $form->{quodate});
1491 $form->set_payment_options($myconfig, $form->{orddate});
1494 $form->{username} = $myconfig->{name};
1498 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1499 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1501 $::form->{order} = SL::DB::Manager::Order->find_by(id => $::form->{id});
1503 $main::lxdebug->leave_sub();
1506 sub project_description {
1507 $main::lxdebug->enter_sub();
1509 my ($self, $dbh, $id) = @_;
1511 my $query = qq|SELECT description FROM project WHERE id = ?|;
1512 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1514 $main::lxdebug->leave_sub();
1525 OE.pm - Order entry module
1529 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>.
1535 =item retrieve_simple PARAMS
1537 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1539 my $order = retrieve_simple(id => 2);