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 = ?, 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"},
573 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
574 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
575 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
576 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
577 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
578 conv_i($orderitems_id),
581 do_query($form, $dbh, $query, @values);
583 $form->{"sellprice_$i"} = $fxsellprice;
584 $form->{"discount_$i"} *= 100;
586 CVar->save_custom_variables(module => 'IC',
587 sub_module => 'orderitems',
588 trans_id => $orderitems_id,
589 configs => $ic_cvar_configs,
591 name_prefix => 'ic_',
592 name_postfix => "_$i",
595 # link previous items with orderitems
596 foreach (qw(orderitems invoice)) {
597 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
598 RecordLinks->create_links('dbh' => $dbh,
601 'from_ids' => $form->{"converted_from_${_}_id_$i"},
602 'to_table' => 'orderitems',
603 'to_id' => $orderitems_id,
606 delete $form->{"converted_from_${_}_id_$i"};
611 # search for orphaned ids
612 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
613 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
614 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
616 if (scalar @orphaned_ids) {
617 # clean up orderitems
618 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
619 do_query($form, $dbh, $query, @orphaned_ids);
622 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
626 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
628 $amount = $form->round_amount($netamount + $tax + $form->{invtotal}, 2, 1);
629 $netamount = $form->round_amount($netamount, 2);
631 if ($form->{currency} eq $form->{defaultcurrency}) {
632 $form->{exchangerate} = 1;
634 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
637 $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
639 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
641 ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department};
646 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
647 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
648 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
649 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
650 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
651 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
652 , order_probability = ?, expected_billing_date = ?
655 @values = ($form->{ordnumber} || '', $form->{quonumber},
656 $form->{cusordnumber}, conv_date($form->{transdate}),
657 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
658 $amount, $netamount, conv_date($reqdate),
659 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
660 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
661 $form->{currency}, $form->{closed} ? 't' : 'f',
662 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
663 $quotation, conv_i($form->{department_id}),
664 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
665 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
666 conv_i($form->{delivery_vendor_id}),
667 conv_i($form->{delivery_customer_id}),
668 conv_i($form->{delivery_term_id}),
669 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
670 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
671 $form->{transaction_description},
672 $form->{marge_total} * 1, $form->{marge_percent} * 1,
673 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
674 conv_i($form->{id}));
675 do_query($form, $dbh, $query, @values);
677 $form->{ordtotal} = $amount;
679 $form->{name} = $form->{ $form->{vc} };
680 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
683 if (!$form->{shipto_id}) {
684 $form->add_shipto($dbh, $form->{id}, "OE");
687 # save printed, emailed, queued
688 $form->save_status($dbh);
690 # Link this record to the records it was created from.
691 # check every record type we may link. i am not happy with converting the string to array back
692 # should be a array from the start (OE.pm -> retrieve).
693 # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
694 $form->{convert_from_oe_ids} =~ s/^\s+//;
695 $form->{convert_from_oe_ids} =~ s/\s+$//;
696 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
697 delete $form->{convert_from_oe_ids};
698 @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
699 foreach (qw(ar oe)) {
700 if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
701 RecordLinks->create_links('dbh' => $dbh,
704 'from_ids' => $form->{"convert_from_${_}_ids"},
706 'to_id' => $form->{id},
708 delete $form->{"convert_from_${_}_ids"};
710 $self->_close_quotations_rfqs('dbh' => $dbh,
711 'from_id' => \@convert_from_oe_ids,
712 'to_id' => $form->{id}) if $_ eq 'oe';
715 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
716 if ($form->{vc} eq 'customer') {
717 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
719 if ($form->{vc} eq 'vendor') {
720 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
724 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
725 "quonumber" : "ordnumber"};
727 Common::webdav_folder($form);
729 my $rc = $dbh->commit;
731 $self->save_periodic_invoices_config(dbh => $dbh,
732 oe_id => $form->{id},
733 config_yaml => $form->{periodic_invoices_config})
734 if ($form->{type} eq 'sales_order');
736 $main::lxdebug->leave_sub();
741 sub save_periodic_invoices_config {
742 my ($self, %params) = @_;
744 return if !$params{oe_id};
746 my $config = $params{config_yaml} ? YAML::Load($params{config_yaml}) : undef;
747 return if 'HASH' ne ref $config;
749 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
750 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
751 $obj->update_attributes(%{ $config });
754 sub load_periodic_invoice_config {
758 delete $form->{periodic_invoices_config};
761 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
764 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
765 print printer_id copies direct_debit) };
766 $form->{periodic_invoices_config} = YAML::Dump($config);
771 sub _close_quotations_rfqs {
772 $main::lxdebug->enter_sub();
777 Common::check_params(\%params, qw(from_id to_id));
779 my $myconfig = \%main::myconfig;
780 my $form = $main::form;
782 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
784 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
785 my $sth = prepare_query($form, $dbh, $query);
787 do_statement($form, $sth, $query, conv_i($params{to_id}));
789 my ($quotation) = $sth->fetchrow_array();
792 $main::lxdebug->leave_sub();
798 foreach my $from_id (@{ $params{from_id} }) {
799 $from_id = conv_i($from_id);
800 do_statement($form, $sth, $query, $from_id);
801 ($quotation) = $sth->fetchrow_array();
802 push @close_ids, $from_id if ($quotation);
807 if (scalar @close_ids) {
808 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
809 do_query($form, $dbh, $query, @close_ids);
811 $dbh->commit() unless ($params{dbh});
814 $main::lxdebug->leave_sub();
818 $main::lxdebug->enter_sub();
820 my ($self, $myconfig, $form) = @_;
822 my $rc = SL::DB::Order->new->db->with_transaction(sub {
823 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
825 SL::DB::Order->new(id => $form->{id})->delete;
827 my $spool = $::lx_office_conf{paths}->{spool};
828 unlink map { "$spool/$_" } @spoolfiles if $spool;
833 $main::lxdebug->leave_sub();
839 $main::lxdebug->enter_sub();
841 my ($self, $myconfig, $form) = @_;
843 # connect to database
844 my $dbh = $form->get_standard_dbh;
846 my ($query, $query_add, @values, @ids, $sth);
848 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
850 push @ids, $form->{"trans_id_$_"}
851 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
852 } (1 .. $form->{"rowcount"});
854 if ($form->{rowcount} && scalar @ids) {
855 $form->{convert_from_oe_ids} = join ' ', @ids;
858 # if called in multi id mode, and still only got one id, switch back to single id
859 if ($form->{"rowcount"} and $#ids == 0) {
860 $form->{"id"} = $ids[0];
862 delete $form->{convert_from_oe_ids};
865 # and remember for the rest of the function
866 my $is_collective_order = scalar @ids;
868 # If collective order was created from exactly 1 order, we assume the same
869 # behaviour as a "save as new" from within an order is actually desired, i.e.
870 # the original order isn't part of a workflow where we want to remember
871 # record_links, but simply a quick way of generating a new order from an old
872 # one without having to enter everything again.
873 # Setting useasnew will prevent the creation of record_links for the items
874 # when saving the new order.
875 # This form variable is probably not necessary, could just set saveasnew instead
876 $form->{useasnew} = 1 if $is_collective_order == 1;
879 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
880 my $next_workday = DateTime->today_local->add(days => $extra_days);
881 my $day_of_week = $next_workday->day_of_week;
883 $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
885 $form->{transdate} = DateTime->today_local->to_kivitendo;
886 $form->{reqdate} = $next_workday->to_kivitendo;
889 # get default accounts
890 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
891 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
892 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
893 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
894 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
895 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
896 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
899 my $ref = selectfirst_hashref_query($form, $dbh, $query);
900 map { $form->{$_} = $ref->{$_} } keys %$ref;
902 $form->{currency} = $form->get_default_currency($myconfig);
904 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
905 # we come from invoices, feel free.
906 $form->{reqdate} = $form->{deliverydate}
907 if ( $form->{deliverydate}
908 and $form->{callback} =~ /action=ar_transactions/);
910 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
912 if ($form->{id} or @ids) {
914 # retrieve order for single id
915 # NOTE: this query is intended to fetch all information only ONCE.
916 # so if any of these infos is important (or even different) for any item,
917 # it will be killed out and then has to be fetched from the item scope query further down
919 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
920 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
921 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
922 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
923 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
925 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
926 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
927 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
928 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
930 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
931 LEFT JOIN employee e ON (o.employee_id = e.id)
932 LEFT JOIN department d ON (o.department_id = d.id) | .
935 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
937 @values = $form->{id} ? ($form->{id}) : @ids;
938 $sth = prepare_execute_query($form, $dbh, $query, @values);
940 $ref = $sth->fetchrow_hashref("NAME_lc");
943 map { $form->{$_} = $ref->{$_} } keys %$ref;
945 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
947 # set all entries for multiple ids blank that yield different information
948 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
949 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
952 $form->{mtime} ||= $form->{itime};
953 $form->{lastmtime} = $form->{mtime};
955 # if not given, fill transdate with current_date
956 $form->{transdate} = $form->current_date($myconfig)
957 unless $form->{transdate};
961 if ($form->{delivery_customer_id}) {
962 $query = qq|SELECT name FROM customer WHERE id = ?|;
963 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
966 if ($form->{delivery_vendor_id}) {
967 $query = qq|SELECT name FROM customer WHERE id = ?|;
968 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
971 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
973 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
974 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
976 $ref = $sth->fetchrow_hashref("NAME_lc");
978 map { $form->{$_} = $ref->{$_} } keys %$ref;
981 # get printed, emailed and queued
982 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
983 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
985 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
986 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
987 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
988 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
991 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
994 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
996 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
998 # retrieve individual items
999 # this query looks up all information about the items
1000 # stuff different from the whole will not be overwritten, but saved with a suffix.
1002 qq|SELECT o.id AS orderitems_id,
1003 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1004 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1005 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1006 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1007 p.partnumber, p.assembly, p.listprice, o.description, o.qty,
1008 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
1009 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1010 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1011 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1012 pr.projectnumber, p.formel,
1013 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1015 JOIN parts p ON (o.parts_id = p.id)
1016 JOIN oe ON (o.trans_id = oe.id)
1017 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1018 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)
1019 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)
1020 LEFT JOIN project pr ON (o.project_id = pr.id)
1021 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1023 ? qq|WHERE o.trans_id = ?|
1024 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1025 qq|ORDER BY o.trans_id, o.position|;
1027 @ids = $form->{id} ? ($form->{id}) : @ids;
1028 $sth = prepare_execute_query($form, $dbh, $query, @values);
1030 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1031 # Retrieve custom variables.
1032 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1034 sub_module => 'orderitems',
1035 trans_id => $ref->{orderitems_id},
1037 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1040 if (!$ref->{"part_inventory_accno_id"}) {
1041 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1043 delete($ref->{"part_inventory_accno_id"});
1045 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1046 # unless already present there
1047 # remove _oe entries afterwards
1048 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1049 qw|ordnumber transdate cusordnumber|
1051 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1055 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1057 qq|SELECT accno AS inventory_accno, | .
1058 qq| new_chart_id AS inventory_new_chart, | .
1059 qq| date($transdate) - valid_from AS inventory_valid | .
1060 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1061 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1062 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1065 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1067 qq|SELECT accno AS income_accno, | .
1068 qq| new_chart_id AS income_new_chart, | .
1069 qq| date($transdate) - valid_from AS income_valid | .
1070 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1071 ($ref->{income_accno}, $ref->{income_new_chart},
1072 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1075 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1077 qq|SELECT accno AS expense_accno, | .
1078 qq| new_chart_id AS expense_new_chart, | .
1079 qq| date($transdate) - valid_from AS expense_valid | .
1080 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1081 ($ref->{expense_accno}, $ref->{expense_new_chart},
1082 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1085 # delete orderitems_id in collective orders, so that they get cloned no matter what
1086 # is this correct? or is the following meant?
1087 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1088 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1090 # get tax rates and description
1091 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1093 qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
1094 qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
1095 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1096 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1097 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1098 qq|ORDER BY c.accno|;
1099 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1100 $ref->{taxaccounts} = "";
1102 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1103 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1107 $ref->{taxaccounts} .= "$ptr->{accno} ";
1108 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1109 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1110 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1111 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1112 $form->{taxaccounts} .= "$ptr->{accno} ";
1117 chop $ref->{taxaccounts};
1119 push @{ $form->{form_details} }, $ref;
1126 # get last name used
1127 $form->lastname_used($dbh, $myconfig, $form->{vc})
1128 unless $form->{"$form->{vc}_id"};
1132 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1134 Common::webdav_folder($form);
1136 $self->load_periodic_invoice_config($form);
1138 my $rc = $dbh->commit;
1140 $main::lxdebug->leave_sub();
1145 sub retrieve_simple {
1146 $main::lxdebug->enter_sub();
1151 Common::check_params(\%params, qw(id));
1153 my $myconfig = \%main::myconfig;
1154 my $form = $main::form;
1156 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1158 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1159 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1161 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1162 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1164 $main::lxdebug->leave_sub();
1170 $main::lxdebug->enter_sub();
1172 my ($self, $myconfig, $form) = @_;
1174 # connect to database
1175 my $dbh = $form->get_standard_dbh;
1181 my $nodiscount_subtotal = 0;
1182 my $discount_subtotal = 0;
1185 my @partsgroup = ();
1188 my $subtotal_header = 0;
1189 my $subposition = 0;
1197 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1199 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS',
1200 'departments' => 'ALL_DEPARTMENTS');
1203 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1204 $price_factors{$pfac->{id}} = $pfac;
1205 $pfac->{factor} *= 1;
1206 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1210 foreach my $dept (@{ $form->{ALL_DEPARTMENTS} }) {
1211 next unless $dept->{id} eq $form->{department_id};
1212 $form->{department} = $dept->{description};
1216 # sort items by partsgroup
1217 for $i (1 .. $form->{rowcount}) {
1219 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1220 $partsgroup = $form->{"partsgroup_$i"};
1222 push @partsgroup, [$i, $partsgroup];
1223 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1229 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1230 %projects_by_id = map { $_->id => $_ } @$projects;
1233 if ($projects_by_id{$form->{"globalproject_id"}}) {
1234 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1235 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1237 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1238 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1242 $form->{discount} = [];
1244 # get some values of parts from db on store them in extra array,
1245 # so that they can be sorted in later
1246 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1247 my @prepared_arrays = keys %prepared_template_arrays;
1249 $form->{TEMPLATE_ARRAYS} = { };
1251 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1252 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1255 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1256 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1257 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1258 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1259 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1261 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1262 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1264 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1266 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1268 my $totalweight = 0;
1270 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1273 if ($item->[1] ne $sameitem) {
1274 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1275 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1276 $sameitem = $item->[1];
1278 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1281 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1283 if ($form->{"id_$i"} != 0) {
1285 # add number, description and qty to $form->{number}, ....
1287 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1288 $subtotal_header = $i;
1289 $position = int($position);
1292 } elsif ($subtotal_header) {
1294 $position = int($position);
1295 $position = $position.".".$subposition;
1297 $position = int($position);
1301 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1303 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1305 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1306 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1307 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1308 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1309 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1310 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1311 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1312 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1313 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1314 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1315 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1316 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1317 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1318 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1319 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1320 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1321 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1322 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1323 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1324 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1325 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1327 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1328 my ($dec) = ($sellprice =~ /\.(\d+)/);
1329 my $decimalplaces = max 2, length($dec);
1331 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1333 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1334 my $linetotal = $form->round_amount($linetotal_exact, 2);
1336 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1337 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1339 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1341 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1343 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1345 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1346 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1348 $linetotal = ($linetotal != 0) ? $linetotal : '';
1350 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1351 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1352 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1354 $form->{ordtotal} += $linetotal;
1355 $form->{nodiscount_total} += $nodiscount_linetotal;
1356 $form->{discount_total} += $discount;
1358 if ($subtotal_header) {
1359 $discount_subtotal += $linetotal;
1360 $nodiscount_subtotal += $nodiscount_linetotal;
1363 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1364 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1365 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1366 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1367 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1369 $discount_subtotal = 0;
1370 $nodiscount_subtotal = 0;
1371 $subtotal_header = 0;
1374 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1377 if (!$form->{"discount_$i"}) {
1378 $nodiscount += $linetotal;
1381 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1383 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1384 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1385 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1386 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1387 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1388 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1390 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1391 $totalweight += $lineweight;
1392 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1393 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1394 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1395 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1397 my ($taxamount, $taxbase);
1400 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1402 if ($form->{taxincluded}) {
1405 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1406 $taxbase = $linetotal / (1 + $taxrate);
1408 $taxamount = $linetotal * $taxrate;
1409 $taxbase = $linetotal;
1412 if ($taxamount != 0) {
1413 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1414 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1415 $taxbase{$accno} += $taxbase;
1419 $tax_rate = $taxrate * 100;
1420 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1422 if ($form->{"assembly_$i"}) {
1425 # get parts and push them onto the stack
1427 if ($form->{groupitems}) {
1428 $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
1430 $sortorder = qq|ORDER BY a.oid|;
1433 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1434 qq|pg.partsgroup | .
1435 qq|FROM assembly a | .
1436 qq| JOIN parts p ON (a.parts_id = p.id) | .
1437 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1438 qq| WHERE a.bom = '1' | .
1439 qq| AND a.id = ? | . $sortorder;
1440 @values = ($form->{"id_$i"});
1441 $sth = $dbh->prepare($query);
1442 $sth->execute(@values) || $form->dberror($query);
1444 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1445 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1446 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1447 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1448 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1449 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1452 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1453 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1454 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1459 CVar->get_non_editable_ic_cvars(form => $form,
1462 sub_module => 'orderitems',
1463 may_converted_from => ['orderitems', 'invoice']);
1465 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1466 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1467 for @{ $ic_cvar_configs };
1469 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1473 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1474 $form->{totalweight_nofmt} = $totalweight;
1475 my $defaults = AM->get_defaults();
1476 $form->{weightunit} = $defaults->{weightunit};
1479 foreach $item (sort keys %taxaccounts) {
1480 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1482 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1483 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1484 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1485 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1486 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1487 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1488 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1490 my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
1491 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1492 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1495 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1496 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1497 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1498 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1500 if($form->{taxincluded}) {
1501 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1502 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1504 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1505 $form->{subtotal_nofmt} = $form->{ordtotal};
1508 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1511 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1513 if ($form->{type} =~ /_quotation/) {
1514 $form->set_payment_options($myconfig, $form->{quodate});
1516 $form->set_payment_options($myconfig, $form->{orddate});
1519 $form->{username} = $myconfig->{name};
1523 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1524 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1526 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1528 $main::lxdebug->leave_sub();
1531 sub project_description {
1532 $main::lxdebug->enter_sub();
1534 my ($self, $dbh, $id) = @_;
1536 my $query = qq|SELECT description FROM project WHERE id = ?|;
1537 my ($value) = selectrow_query($main::form, $dbh, $query, $id);
1539 $main::lxdebug->leave_sub();
1550 OE.pm - Order entry module
1554 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>.
1560 =item retrieve_simple PARAMS
1562 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1564 my $order = retrieve_simple(id => 2);