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., 51 Franklin Street, Fifth Floor, Boston,
30 #======================================================================
34 #======================================================================
38 use List::Util qw(max first);
44 use SL::DB::PeriodicInvoicesConfig;
46 use SL::DB::ProjectType;
50 use SL::HTML::Restrict;
53 use SL::Util qw(trim);
61 $main::lxdebug->enter_sub();
63 my ($self, $myconfig, $form) = @_;
66 my $dbh = $form->get_standard_dbh;
69 my $ordnumber = 'ordnumber';
75 my ($periodic_invoices_columns, $periodic_invoices_joins);
77 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
79 if ($form->{type} =~ /_quotation$/) {
81 $ordnumber = 'quonumber';
83 } elsif ($form->{type} eq 'sales_order') {
84 $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;
85 $periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;
88 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
92 if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {
94 SELECT from_id, ar.amount, ar.netamount FROM (
97 WHERE from_table = 'oe' AND to_table = 'ar'
99 SELECT rl1.from_id, rl2.to_id
100 FROM record_links rl1
101 LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
102 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
104 SELECT rl1.from_id, rl3.to_id
105 FROM record_links rl1
106 JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
107 JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
108 WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'
110 LEFT JOIN ar ON ar.id = rl.to_id
112 for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {
113 $billed_amount{ $ref->{from_id}} += $ref->{amount};
114 $billed_netamount{$ref->{from_id}} += $ref->{netamount};
119 qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
120 qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
121 qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
122 qq| o.transaction_description, | .
123 qq| o.marge_total, o.marge_percent, | .
124 qq| o.itime::DATE AS insertdate, | .
125 qq| department.description as department, | .
126 qq| ex.$rate AS exchangerate, | .
127 qq| pt.description AS payment_terms, | .
128 qq| pr.projectnumber AS globalprojectnumber, | .
129 qq| e.name AS employee, s.name AS salesman, | .
130 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
131 qq| tz.description AS taxzone | .
132 $periodic_invoices_columns .
133 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
135 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
136 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
137 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
138 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
139 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
140 qq| AND ex.transdate = o.transdate) | .
141 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
142 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
143 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
144 qq|LEFT JOIN department ON (o.department_id = department.id) | .
145 qq|$periodic_invoices_joins | .
146 qq|WHERE (o.quotation = ?) |;
147 push(@values, $quotation);
149 if ($form->{department_id}) {
150 $query .= qq| AND o.department_id = ?|;
151 push(@values, $form->{department_id});
154 if ($form->{"project_id"}) {
156 qq|AND ((globalproject_id = ?) OR EXISTS | .
157 qq| (SELECT * FROM orderitems oi | .
158 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
159 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
162 if ($form->{"projectnumber"}) {
164 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
165 SELECT * FROM orderitems oi
166 LEFT JOIN project proi ON proi.id = oi.project_id
167 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
170 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
173 if ($form->{"business_id"}) {
174 $query .= " AND ct.business_id = ?";
175 push(@values, $form->{"business_id"});
178 if ($form->{"${vc}_id"}) {
179 $query .= " AND o.${vc}_id = ?";
180 push(@values, $form->{"${vc}_id"});
182 } elsif ($form->{$vc}) {
183 $query .= " AND ct.name ILIKE ?";
184 push(@values, like($form->{$vc}));
187 if ($form->{"cp_name"}) {
188 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
189 push(@values, (like($form->{"cp_name"}))x2);
192 if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {
193 $query .= " AND o.employee_id = (select id from employee where login= ?)";
194 push @values, $::myconfig{login};
196 if ($form->{employee_id}) {
197 $query .= " AND o.employee_id = ?";
198 push @values, conv_i($form->{employee_id});
201 if ($form->{salesman_id}) {
202 $query .= " AND o.salesman_id = ?";
203 push @values, conv_i($form->{salesman_id});
206 if (!$form->{open} && !$form->{closed}) {
207 $query .= " AND o.id = 0";
208 } elsif (!($form->{open} && $form->{closed})) {
209 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
212 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
213 ($form->{"notdelivered"} ne $form->{"delivered"})) {
214 $query .= $form->{"delivered"} ?
215 " AND o.delivered " : " AND NOT o.delivered";
218 if ($form->{$ordnumber}) {
219 $query .= qq| AND o.$ordnumber ILIKE ?|;
220 push(@values, like($form->{$ordnumber}));
223 if ($form->{cusordnumber}) {
224 $query .= qq| AND o.cusordnumber ILIKE ?|;
225 push(@values, like($form->{cusordnumber}));
228 if($form->{transdatefrom}) {
229 $query .= qq| AND o.transdate >= ?|;
230 push(@values, conv_date($form->{transdatefrom}));
233 if($form->{transdateto}) {
234 $query .= qq| AND o.transdate <= ?|;
235 push(@values, conv_date($form->{transdateto}));
238 if($form->{reqdatefrom}) {
239 $query .= qq| AND o.reqdate >= ?|;
240 push(@values, conv_date($form->{reqdatefrom}));
243 if($form->{reqdateto}) {
244 $query .= qq| AND o.reqdate <= ?|;
245 push(@values, conv_date($form->{reqdateto}));
248 if($form->{insertdatefrom}) {
249 $query .= qq| AND o.itime::DATE >= ?|;
250 push(@values, conv_date($form->{insertdatefrom}));
253 if($form->{insertdateto}) {
254 $query .= qq| AND o.itime::DATE <= ?|;
255 push(@values, conv_date($form->{insertdateto}));
258 if ($form->{shippingpoint}) {
259 $query .= qq| AND o.shippingpoint ILIKE ?|;
260 push(@values, like($form->{shippingpoint}));
263 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
264 $query .= qq| AND tz.id = ?|;
265 push(@values, $form->{taxzone_id});
268 if ($form->{transaction_description}) {
269 $query .= qq| AND o.transaction_description ILIKE ?|;
270 push(@values, like($form->{transaction_description}));
273 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
274 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
275 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
278 if ($form->{reqdate_unset_or_old}) {
279 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
282 if (($form->{order_probability_value} || '') ne '') {
283 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
284 $query .= qq| AND (o.order_probability ${op} ?)|;
285 push @values, trim($form->{order_probability_value});
288 if ($form->{expected_billing_date_from}) {
289 $query .= qq| AND (o.expected_billing_date >= ?)|;
290 push @values, conv_date($form->{expected_billing_date_from});
293 if ($form->{expected_billing_date_to}) {
294 $query .= qq| AND (o.expected_billing_date <= ?)|;
295 push @values, conv_date($form->{expected_billing_date_to});
298 if ($form->{parts_partnumber}) {
301 SELECT orderitems.trans_id
303 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
304 WHERE (orderitems.trans_id = o.id)
305 AND (parts.partnumber ILIKE ?)
309 push @values, like($form->{parts_partnumber});
312 if ($form->{parts_description}) {
315 SELECT orderitems.trans_id
317 WHERE (orderitems.trans_id = o.id)
318 AND (orderitems.description ILIKE ?)
322 push @values, like($form->{parts_description});
326 my @tokens = parse_line('\s+', 0, $form->{all});
327 # ordnumber quonumber customer.name vendor.name transaction_description
329 o.ordnumber ILIKE ? OR
330 o.quonumber ILIKE ? OR
332 o.transaction_description ILIKE ?
334 push @values, (like($_))x4 for @tokens;
337 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
338 'trans_id_field' => 'ct.id',
342 $query .= qq| AND ($cvar_where)|;
343 push @values, @cvar_values;
346 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
347 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
348 my %allowed_sort_columns = (
349 "transdate" => "o.transdate",
350 "reqdate" => "o.reqdate",
352 "ordnumber" => "o.ordnumber",
353 "cusordnumber" => "o.cusordnumber",
354 "quonumber" => "o.quonumber",
356 "employee" => "e.name",
357 "salesman" => "s.name",
358 "shipvia" => "o.shipvia",
359 "transaction_description" => "o.transaction_description",
360 "shippingpoint" => "o.shippingpoint",
361 "insertdate" => "o.itime",
362 "taxzone" => "tz.description",
363 "payment_terms" => "pt.description",
364 "department" => "department.description",
366 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
367 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
369 $query .= qq| ORDER by | . $sortorder;
371 my $sth = $dbh->prepare($query);
372 $sth->execute(@values) ||
373 $form->dberror($query . " (" . join(", ", @values) . ")");
377 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
378 $ref->{billed_amount} = $billed_amount{$ref->{id}};
379 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
380 if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
381 $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
382 $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
384 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
385 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
387 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
388 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
389 $id{ $ref->{id} } = $ref->{id};
394 $main::lxdebug->leave_sub();
397 sub transactions_for_todo_list {
398 $main::lxdebug->enter_sub();
403 my $myconfig = \%main::myconfig;
404 my $form = $main::form;
406 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
408 my $query = qq|SELECT id FROM employee WHERE login = ?|;
409 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
412 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
413 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
418 LEFT JOIN customer c ON (oe.customer_id = c.id)
419 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
420 LEFT JOIN employee e ON (oe.employee_id = e.id)
421 WHERE (COALESCE(quotation, FALSE) = TRUE)
422 AND (COALESCE(closed, FALSE) = FALSE)
423 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
424 AND NOT (oe.reqdate ISNULL)
425 AND (oe.reqdate < current_date)
428 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
430 $main::lxdebug->leave_sub();
436 my ($self, $myconfig, $form) = @_;
437 $main::lxdebug->enter_sub();
439 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
441 $::lxdebug->leave_sub;
447 $main::lxdebug->enter_sub();
449 my ($self, $myconfig, $form) = @_;
451 my $dbh = SL::DB->client->dbh;
452 my $restricter = SL::HTML::Restrict->create;
454 my ($query, @values, $sth, $null);
455 my $exchangerate = 0;
457 my $all_units = AM->retrieve_units($myconfig, $form);
458 $form->{all_units} = $all_units;
460 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
463 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
464 unless ($form->{employee_id}) {
465 $form->get_employee($dbh);
468 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
470 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
471 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
472 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
475 $query = qq|DELETE FROM custom_variables
476 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
477 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
478 do_query($form, $dbh, $query, $form->{id});
480 $query = qq|DELETE FROM shipto | .
481 qq|WHERE trans_id = ? AND module = 'OE'|;
482 do_query($form, $dbh, $query, $form->{id});
486 $query = qq|SELECT nextval('id')|;
487 ($form->{id}) = selectrow_query($form, $dbh, $query);
489 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
490 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
507 my @processed_orderitems;
509 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
510 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
513 for my $i (1 .. $form->{rowcount}) {
515 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
517 if ($form->{"id_$i"}) {
520 $query = qq|SELECT unit FROM parts WHERE id = ?|;
521 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
524 if (defined($all_units->{$item_unit}->{factor}) &&
525 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
526 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
528 my $baseqty = $form->{"qty_$i"} * $basefactor;
530 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
531 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
533 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
535 # keep entered selling price
537 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
539 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
541 my $decimalplaces = ($dec > 2) ? $dec : 2;
543 # undo discount formatting
544 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
547 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
549 # round linetotal at least to 2 decimal places
550 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
551 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
553 $form->{"inventory_accno_$i"} *= 1;
554 $form->{"expense_accno_$i"} *= 1;
556 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
560 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
562 if ($form->{taxincluded}) {
563 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
564 $taxbase = $linetotal - $taxamount;
566 # we are not keeping a natural price, do not round
567 $form->{"sellprice_$i"} =
568 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
570 $taxamount = $linetotal * $taxrate;
571 $taxbase = $linetotal;
574 if ($form->round_amount($taxrate, 7) == 0) {
575 if ($form->{taxincluded}) {
576 foreach my $item (@taxaccounts) {
577 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
578 $taxaccounts{$item} += $taxamount;
579 $taxdiff += $taxamount;
580 $taxbase{$item} += $taxbase;
582 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
584 foreach my $item (@taxaccounts) {
585 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
586 $taxbase{$item} += $taxbase;
590 foreach my $item (@taxaccounts) {
591 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
592 $taxbase{$item} += $taxbase;
596 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
598 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
600 # Get pricegroup_id and save it. Unfortunately the interface
601 # also uses ID "0" for signalling that none is selected, but "0"
602 # must not be stored in the database. Therefore we cannot simply
604 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
606 $pricegroup_id = undef if !$pricegroup_id;
608 # force new project, if not set yet
609 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
610 require SL::DB::Customer;
611 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
612 die "Can't find customer" unless $customer;
613 die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
614 if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
616 my $new_project = SL::DB::Project->new(
617 projectnumber => $form->{ordnumber},
618 description => $customer->name,
619 customer_id => $customer->id,
621 project_type_id => $::instance_conf->get_project_type_id,
622 project_status_id => $::instance_conf->get_project_status_id,
625 $form->{"globalproject_id"} = $new_project->id;
628 CVar->get_non_editable_ic_cvars(form => $form,
631 sub_module => 'orderitems',
632 may_converted_from => ['orderitems', 'invoice']);
636 # save detail record in orderitems table
637 if (! $form->{"orderitems_id_$i"}) {
638 $query = qq|SELECT nextval('orderitemsid')|;
639 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
641 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
642 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
645 my $orderitems_id = $form->{"orderitems_id_$i"};
646 push @processed_orderitems, $orderitems_id;
649 UPDATE orderitems SET
650 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
651 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
652 pricegroup_id = ?, subtotal = ?,
653 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
654 active_price_source = ?, active_discount_source = ?,
655 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
659 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
660 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
661 $form->{"qty_$i"}, $baseqty,
662 $fxsellprice, $form->{"discount_$i"},
663 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
664 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
665 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
666 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
667 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
668 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
669 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
670 conv_i($orderitems_id),
673 do_query($form, $dbh, $query, @values);
675 $form->{"sellprice_$i"} = $fxsellprice;
676 $form->{"discount_$i"} *= 100;
678 CVar->save_custom_variables(module => 'IC',
679 sub_module => 'orderitems',
680 trans_id => $orderitems_id,
681 configs => $ic_cvar_configs,
683 name_prefix => 'ic_',
684 name_postfix => "_$i",
687 # link previous items with orderitems
688 # assume we have a new workflow if we link from invoice or order to quotation
689 # unluckily orderitems are used for quotation and orders - therefore one more
690 # check to be sure NOT to link from order to quotation
691 foreach (qw(orderitems)) {
692 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
693 && $form->{type} !~ 'quotation') {
694 RecordLinks->create_links('dbh' => $dbh,
697 'from_ids' => $form->{"converted_from_${_}_id_$i"},
698 'to_table' => 'orderitems',
699 'to_id' => $orderitems_id,
702 delete $form->{"converted_from_${_}_id_$i"};
707 # search for orphaned ids
708 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
709 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
710 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
712 if (scalar @orphaned_ids) {
713 # clean up orderitems
714 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
715 do_query($form, $dbh, $query, @orphaned_ids);
718 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
722 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
724 $amount = $form->round_amount($netamount + $tax, 2, 1);
725 $netamount = $form->round_amount($netamount, 2);
727 if ($form->{currency} eq $form->{defaultcurrency}) {
728 $form->{exchangerate} = 1;
730 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
733 # from inputfield (exchangerate) or hidden (forex)
734 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
736 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
738 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
743 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
744 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
745 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
746 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
747 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
748 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
749 , order_probability = ?, expected_billing_date = ?
752 @values = ($form->{ordnumber} || '', $form->{quonumber},
753 $form->{cusordnumber}, conv_date($form->{transdate}),
754 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
755 $amount, $netamount, conv_date($reqdate),
756 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
757 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
758 $form->{currency}, $form->{closed} ? 't' : 'f',
759 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
760 $quotation, conv_i($form->{department_id}),
761 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
762 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
763 conv_i($form->{delivery_vendor_id}),
764 conv_i($form->{delivery_customer_id}),
765 conv_i($form->{delivery_term_id}),
766 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
767 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
768 $form->{transaction_description},
769 $form->{marge_total} * 1, $form->{marge_percent} * 1,
770 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
771 conv_i($form->{id}));
772 do_query($form, $dbh, $query, @values);
774 $form->new_lastmtime('oe');
776 $form->{ordtotal} = $amount;
778 $form->{name} = $form->{ $form->{vc} };
779 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
782 if (!$form->{shipto_id}) {
783 $form->add_shipto($dbh, $form->{id}, "OE");
786 # save printed, emailed, queued
787 $form->save_status($dbh);
789 # Link this record to the records it was created from.
790 $form->{convert_from_oe_ids} =~ s/^\s+//;
791 $form->{convert_from_oe_ids} =~ s/\s+$//;
792 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
793 delete $form->{convert_from_oe_ids};
794 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
795 RecordLinks->create_links('dbh' => $dbh,
797 'from_table' => 'oe',
798 'from_ids' => \@convert_from_oe_ids,
800 'to_id' => $form->{id},
802 $self->_close_quotations_rfqs('dbh' => $dbh,
803 'from_id' => \@convert_from_oe_ids,
804 'to_id' => $form->{id});
807 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
808 if ($form->{vc} eq 'customer') {
809 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
811 if ($form->{vc} eq 'vendor') {
812 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
816 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
817 "quonumber" : "ordnumber"};
819 Common::webdav_folder($form);
821 $self->save_periodic_invoices_config(dbh => $dbh,
822 oe_id => $form->{id},
823 config_yaml => $form->{periodic_invoices_config})
824 if ($form->{type} eq 'sales_order');
826 $main::lxdebug->leave_sub();
831 sub save_periodic_invoices_config {
832 my ($self, %params) = @_;
834 return if !$params{oe_id};
836 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
837 return if 'HASH' ne ref $config;
839 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
840 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
841 $obj->update_attributes(%{ $config });
844 sub load_periodic_invoice_config {
848 delete $form->{periodic_invoices_config};
851 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
854 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
855 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
856 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
861 sub _close_quotations_rfqs {
862 $main::lxdebug->enter_sub();
867 Common::check_params(\%params, qw(from_id to_id));
869 my $myconfig = \%main::myconfig;
870 my $form = $main::form;
872 my $dbh = $params{dbh} || SL::DB->client->dbh;
874 SL::DB->client->with_transaction(sub {
876 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
877 my $sth = prepare_query($form, $dbh, $query);
879 do_statement($form, $sth, $query, conv_i($params{to_id}));
881 my ($quotation) = $sth->fetchrow_array();
889 foreach my $from_id (@{ $params{from_id} }) {
890 $from_id = conv_i($from_id);
891 do_statement($form, $sth, $query, $from_id);
892 ($quotation) = $sth->fetchrow_array();
893 push @close_ids, $from_id if ($quotation);
898 if (scalar @close_ids) {
899 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
900 do_query($form, $dbh, $query, @close_ids);
903 }) or do { die SL::DB->client->error };
905 $main::lxdebug->leave_sub();
909 $main::lxdebug->enter_sub();
911 my ($self, $myconfig, $form) = @_;
913 my $rc = SL::DB::Order->new->db->with_transaction(sub {
914 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
916 SL::DB::Order->new(id => $form->{id})->delete;
918 my $spool = $::lx_office_conf{paths}->{spool};
919 unlink map { "$spool/$_" } @spoolfiles if $spool;
922 }) or do { die SL::DB->client->error };
924 $main::lxdebug->leave_sub();
930 my ($self, $myconfig, $form) = @_;
931 $main::lxdebug->enter_sub();
933 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
935 $::lxdebug->leave_sub;
940 my ($self, $myconfig, $form) = @_;
942 # connect to database
943 my $dbh = SL::DB->client->dbh;
945 my ($query, $query_add, @values, @ids, $sth);
947 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
949 push @ids, $form->{"trans_id_$_"}
950 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
951 } (1 .. $form->{"rowcount"});
953 if ($form->{rowcount} && scalar @ids) {
954 $form->{convert_from_oe_ids} = join ' ', @ids;
957 # if called in multi id mode, and still only got one id, switch back to single id
958 if ($form->{"rowcount"} and $#ids == 0) {
959 $form->{"id"} = $ids[0];
961 delete $form->{convert_from_oe_ids};
964 # and remember for the rest of the function
965 my $is_collective_order = scalar @ids;
967 # If collective order was created from exactly 1 order, we assume the same
968 # behaviour as a "save as new" from within an order is actually desired, i.e.
969 # the original order isn't part of a workflow where we want to remember
970 # record_links, but simply a quick way of generating a new order from an old
971 # one without having to enter everything again.
972 # Setting useasnew will prevent the creation of record_links for the items
973 # when saving the new order.
974 # This form variable is probably not necessary, could just set saveasnew instead
975 $form->{useasnew} = 1 if $is_collective_order == 1;
978 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
979 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
980 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
981 $form->{transdate} = DateTime->today_local->to_kivitendo;
984 # get default accounts
985 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
986 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
987 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
988 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
989 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
990 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
991 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
994 my $ref = selectfirst_hashref_query($form, $dbh, $query);
995 map { $form->{$_} = $ref->{$_} } keys %$ref;
997 $form->{currency} = $form->get_default_currency($myconfig);
999 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
1000 # we come from invoices, feel free.
1001 $form->{reqdate} = $form->{deliverydate}
1002 if ( $form->{deliverydate}
1003 and $form->{callback} =~ /action=ar_transactions/);
1005 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
1007 if ($form->{id} or @ids) {
1009 # retrieve order for single id
1010 # NOTE: this query is intended to fetch all information only ONCE.
1011 # so if any of these infos is important (or even different) for any item,
1012 # it will be killed out and then has to be fetched from the item scope query further down
1014 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1015 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1016 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1017 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1018 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1020 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1021 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1022 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1023 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1025 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1026 LEFT JOIN employee e ON (o.employee_id = e.id)
1027 LEFT JOIN department d ON (o.department_id = d.id) | .
1030 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1032 @values = $form->{id} ? ($form->{id}) : @ids;
1033 $sth = prepare_execute_query($form, $dbh, $query, @values);
1035 $ref = $sth->fetchrow_hashref("NAME_lc");
1038 map { $form->{$_} = $ref->{$_} } keys %$ref;
1040 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1042 # set all entries for multiple ids blank that yield different information
1043 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1044 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1047 $form->{mtime} ||= $form->{itime};
1048 $form->{lastmtime} = $form->{mtime};
1050 # if not given, fill transdate with current_date
1051 $form->{transdate} = $form->current_date($myconfig)
1052 unless $form->{transdate};
1056 if ($form->{delivery_customer_id}) {
1057 $query = qq|SELECT name FROM customer WHERE id = ?|;
1058 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1061 if ($form->{delivery_vendor_id}) {
1062 $query = qq|SELECT name FROM customer WHERE id = ?|;
1063 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1066 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1068 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1069 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1071 $ref = $sth->fetchrow_hashref("NAME_lc");
1072 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1075 if ($ref->{shipto_id}) {
1076 my $cvars = CVar->get_custom_variables(
1079 trans_id => $ref->{shipto_id},
1081 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1084 # get printed, emailed and queued
1085 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1086 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1088 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1089 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1090 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1091 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1094 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1097 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1099 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1100 unshift @values, ($form->{taxzone_id}) x 2;
1102 # retrieve individual items
1103 # this query looks up all information about the items
1104 # stuff different from the whole will not be overwritten, but saved with a suffix.
1106 qq|SELECT o.id AS orderitems_id,
1107 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1108 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1109 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1110 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1111 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1112 p.classification_id,
1113 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1114 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1115 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1116 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1117 pr.projectnumber, p.formel,
1118 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1120 JOIN parts p ON (o.parts_id = p.id)
1121 JOIN oe ON (o.trans_id = oe.id)
1122 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1123 LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
1124 LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = ? and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
1125 LEFT JOIN project pr ON (o.project_id = pr.id)
1126 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1128 ? qq|WHERE o.trans_id = ?|
1129 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1130 qq|ORDER BY o.trans_id, o.position|;
1132 @ids = $form->{id} ? ($form->{id}) : @ids;
1133 $sth = prepare_execute_query($form, $dbh, $query, @values);
1135 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1136 # Retrieve custom variables.
1137 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1139 sub_module => 'orderitems',
1140 trans_id => $ref->{orderitems_id},
1142 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1145 if (!$ref->{"part_type"} eq 'part') {
1146 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1148 # delete($ref->{"part_inventory_accno_id"});
1150 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1151 # unless already present there
1152 # remove _oe entries afterwards
1153 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1154 qw|ordnumber transdate cusordnumber|
1156 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1160 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1162 qq|SELECT accno AS inventory_accno, | .
1163 qq| new_chart_id AS inventory_new_chart, | .
1164 qq| date($transdate) - valid_from AS inventory_valid | .
1165 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1166 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1167 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1170 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1172 qq|SELECT accno AS income_accno, | .
1173 qq| new_chart_id AS income_new_chart, | .
1174 qq| date($transdate) - valid_from AS income_valid | .
1175 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1176 ($ref->{income_accno}, $ref->{income_new_chart},
1177 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1180 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1182 qq|SELECT accno AS expense_accno, | .
1183 qq| new_chart_id AS expense_new_chart, | .
1184 qq| date($transdate) - valid_from AS expense_valid | .
1185 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1186 ($ref->{expense_accno}, $ref->{expense_new_chart},
1187 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1190 # delete orderitems_id in collective orders, so that they get cloned no matter what
1191 # is this correct? or is the following meant?
1192 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1193 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1195 # get tax rates and description
1196 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1198 qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
1200 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1201 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1202 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1203 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1204 qq|ORDER BY c.accno|;
1205 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1206 $ref->{taxaccounts} = "";
1208 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1209 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1213 $ref->{taxaccounts} .= "$ptr->{accno} ";
1214 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1215 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1216 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1217 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1218 $form->{taxaccounts} .= "$ptr->{accno} ";
1223 chop $ref->{taxaccounts};
1225 push @{ $form->{form_details} }, $ref;
1232 # get last name used
1233 $form->lastname_used($dbh, $myconfig, $form->{vc})
1234 unless $form->{"$form->{vc}_id"};
1238 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1240 Common::webdav_folder($form);
1242 $self->load_periodic_invoice_config($form);
1247 sub retrieve_simple {
1248 $main::lxdebug->enter_sub();
1253 Common::check_params(\%params, qw(id));
1255 my $myconfig = \%main::myconfig;
1256 my $form = $main::form;
1258 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1260 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1261 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1263 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1264 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1266 $main::lxdebug->leave_sub();
1272 $main::lxdebug->enter_sub();
1274 my ($self, $myconfig, $form) = @_;
1276 # connect to database
1277 my $dbh = SL::DB->client->dbh;
1283 my $nodiscount_subtotal = 0;
1284 my $discount_subtotal = 0;
1287 my @partsgroup = ();
1290 my $subtotal_header = 0;
1291 my $subposition = 0;
1299 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1301 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1304 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1305 $price_factors{$pfac->{id}} = $pfac;
1306 $pfac->{factor} *= 1;
1307 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1310 # sort items by partsgroup
1311 for $i (1 .. $form->{rowcount}) {
1313 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1314 $partsgroup = $form->{"partsgroup_$i"};
1316 push @partsgroup, [$i, $partsgroup];
1317 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1323 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1324 %projects_by_id = map { $_->id => $_ } @$projects;
1327 if ($projects_by_id{$form->{"globalproject_id"}}) {
1328 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1329 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1331 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1332 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1336 $form->{discount} = [];
1338 # get some values of parts from db on store them in extra array,
1339 # so that they can be sorted in later
1340 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1341 my @prepared_arrays = keys %prepared_template_arrays;
1342 my @separate_totals = qw(non_separate_subtotal);
1344 $form->{TEMPLATE_ARRAYS} = { };
1346 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1347 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1350 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1351 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1352 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1353 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1354 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1356 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1357 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1359 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1361 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1363 my $totalweight = 0;
1365 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1368 if ($item->[1] ne $sameitem) {
1369 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1370 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1371 $sameitem = $item->[1];
1373 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1376 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1378 if ($form->{"id_$i"} != 0) {
1380 # add number, description and qty to $form->{number}, ....
1382 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1383 $subtotal_header = $i;
1384 $position = int($position);
1387 } elsif ($subtotal_header) {
1389 $position = int($position);
1390 $position = $position.".".$subposition;
1392 $position = int($position);
1396 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1398 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1400 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1401 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1402 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1403 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1404 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1405 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1406 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1407 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1408 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1409 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1410 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1411 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1412 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1413 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1414 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1415 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1416 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1417 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1418 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1419 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1420 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1422 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1423 my ($dec) = ($sellprice =~ /\.(\d+)/);
1424 my $decimalplaces = max 2, length($dec);
1426 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1428 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1429 my $linetotal = $form->round_amount($linetotal_exact, 2);
1431 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1432 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1434 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1436 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1438 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1440 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1441 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1443 $linetotal = ($linetotal != 0) ? $linetotal : '';
1445 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1446 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1447 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1449 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1450 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1451 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1452 push @separate_totals , "separate_${pabbr}_subtotal";
1453 $form->{"separate_${pabbr}_subtotal"} = 0;
1455 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1457 $form->{non_separate_subtotal} += $linetotal;
1460 $form->{ordtotal} += $linetotal;
1461 $form->{nodiscount_total} += $nodiscount_linetotal;
1462 $form->{discount_total} += $discount;
1464 if ($subtotal_header) {
1465 $discount_subtotal += $linetotal;
1466 $nodiscount_subtotal += $nodiscount_linetotal;
1469 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1470 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1471 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1472 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1473 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1475 $discount_subtotal = 0;
1476 $nodiscount_subtotal = 0;
1477 $subtotal_header = 0;
1480 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1483 if (!$form->{"discount_$i"}) {
1484 $nodiscount += $linetotal;
1487 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1489 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1490 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1491 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1492 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1493 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1494 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1496 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1497 $totalweight += $lineweight;
1498 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1499 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1500 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1501 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1503 my ($taxamount, $taxbase);
1506 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1508 if ($form->{taxincluded}) {
1511 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1512 $taxbase = $linetotal / (1 + $taxrate);
1514 $taxamount = $linetotal * $taxrate;
1515 $taxbase = $linetotal;
1518 if ($taxamount != 0) {
1519 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1520 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1521 $taxbase{$accno} += $taxbase;
1525 $tax_rate = $taxrate * 100;
1526 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1528 if ($form->{"part_type_$i"} eq 'assembly') {
1531 # get parts and push them onto the stack
1533 if ($form->{groupitems}) {
1534 $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
1536 $sortorder = qq|ORDER BY a.position|;
1539 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1540 qq|pg.partsgroup | .
1541 qq|FROM assembly a | .
1542 qq| JOIN parts p ON (a.parts_id = p.id) | .
1543 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1544 qq| WHERE a.bom = '1' | .
1545 qq| AND a.id = ? | . $sortorder;
1546 @values = ($form->{"id_$i"});
1547 $sth = $dbh->prepare($query);
1548 $sth->execute(@values) || $form->dberror($query);
1550 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1551 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1552 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1553 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1554 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1555 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1558 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1559 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1560 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1565 CVar->get_non_editable_ic_cvars(form => $form,
1568 sub_module => 'orderitems',
1569 may_converted_from => ['orderitems', 'invoice']);
1571 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1572 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1573 for @{ $ic_cvar_configs };
1575 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1579 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1580 $form->{totalweight_nofmt} = $totalweight;
1581 my $defaults = AM->get_defaults();
1582 $form->{weightunit} = $defaults->{weightunit};
1585 foreach $item (sort keys %taxaccounts) {
1586 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1588 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1589 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1590 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1591 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1592 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1593 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1594 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1596 my $tax_objs = SL::DB::Manager::Tax->get_objects_from_sql(
1597 sql => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
1598 args => [ $form->{"${item}_taxnumber"} ]
1602 $tax_obj = $tax_objs->[0];
1604 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1605 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1608 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1609 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1610 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1611 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1613 if($form->{taxincluded}) {
1614 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1615 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1617 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1618 $form->{subtotal_nofmt} = $form->{ordtotal};
1621 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1622 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1623 $form->{rounding} = $form->round_amount(
1624 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1629 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1630 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1632 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1634 $form->{username} = $myconfig->{name};
1636 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1637 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1638 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1640 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1641 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1643 $main::lxdebug->leave_sub();
1652 OE.pm - Order entry module
1656 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>.
1662 =item retrieve_simple PARAMS
1664 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1666 my $order = retrieve_simple(id => 2);