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.exchangerate, | .
125 qq| o.itime::DATE AS insertdate, | .
127 qq| department.description as department, | .
128 qq| ex.$rate AS daily_exchangerate, | .
129 qq| pt.description AS payment_terms, | .
130 qq| pr.projectnumber AS globalprojectnumber, | .
131 qq| e.name AS employee, s.name AS salesman, | .
132 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
133 qq| tz.description AS taxzone | .
134 $periodic_invoices_columns .
135 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
137 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
138 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
139 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
140 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
141 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
142 qq| AND ex.transdate = o.transdate) | .
143 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
144 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
145 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
146 qq|LEFT JOIN department ON (o.department_id = department.id) | .
147 qq|$periodic_invoices_joins | .
148 qq|WHERE (o.quotation = ?) |;
149 push(@values, $quotation);
151 if ($form->{department_id}) {
152 $query .= qq| AND o.department_id = ?|;
153 push(@values, $form->{department_id});
156 if ($form->{"project_id"}) {
158 qq|AND ((globalproject_id = ?) OR EXISTS | .
159 qq| (SELECT * FROM orderitems oi | .
160 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
161 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
164 if ($form->{"projectnumber"}) {
166 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
167 SELECT * FROM orderitems oi
168 LEFT JOIN project proi ON proi.id = oi.project_id
169 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
172 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
175 if ($form->{"business_id"}) {
176 $query .= " AND ct.business_id = ?";
177 push(@values, $form->{"business_id"});
180 if ($form->{"${vc}_id"}) {
181 $query .= " AND o.${vc}_id = ?";
182 push(@values, $form->{"${vc}_id"});
184 } elsif ($form->{$vc}) {
185 $query .= " AND ct.name ILIKE ?";
186 push(@values, like($form->{$vc}));
189 if ($form->{"cp_name"}) {
190 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
191 push(@values, (like($form->{"cp_name"}))x2);
194 if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {
195 $query .= " AND o.employee_id = (select id from employee where login= ?)";
196 push @values, $::myconfig{login};
198 if ($form->{employee_id}) {
199 $query .= " AND o.employee_id = ?";
200 push @values, conv_i($form->{employee_id});
203 if ($form->{salesman_id}) {
204 $query .= " AND o.salesman_id = ?";
205 push @values, conv_i($form->{salesman_id});
208 if (!$form->{open} && !$form->{closed}) {
209 $query .= " AND o.id = 0";
210 } elsif (!($form->{open} && $form->{closed})) {
211 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
214 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
215 ($form->{"notdelivered"} ne $form->{"delivered"})) {
216 $query .= $form->{"delivered"} ?
217 " AND o.delivered " : " AND NOT o.delivered";
220 if ($form->{$ordnumber}) {
221 $query .= qq| AND o.$ordnumber ILIKE ?|;
222 push(@values, like($form->{$ordnumber}));
225 if ($form->{cusordnumber}) {
226 $query .= qq| AND o.cusordnumber ILIKE ?|;
227 push(@values, like($form->{cusordnumber}));
230 if($form->{transdatefrom}) {
231 $query .= qq| AND o.transdate >= ?|;
232 push(@values, conv_date($form->{transdatefrom}));
235 if($form->{transdateto}) {
236 $query .= qq| AND o.transdate <= ?|;
237 push(@values, conv_date($form->{transdateto}));
240 if($form->{reqdatefrom}) {
241 $query .= qq| AND o.reqdate >= ?|;
242 push(@values, conv_date($form->{reqdatefrom}));
245 if($form->{reqdateto}) {
246 $query .= qq| AND o.reqdate <= ?|;
247 push(@values, conv_date($form->{reqdateto}));
250 if($form->{insertdatefrom}) {
251 $query .= qq| AND o.itime::DATE >= ?|;
252 push(@values, conv_date($form->{insertdatefrom}));
255 if($form->{insertdateto}) {
256 $query .= qq| AND o.itime::DATE <= ?|;
257 push(@values, conv_date($form->{insertdateto}));
260 if ($form->{shippingpoint}) {
261 $query .= qq| AND o.shippingpoint ILIKE ?|;
262 push(@values, like($form->{shippingpoint}));
265 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
266 $query .= qq| AND tz.id = ?|;
267 push(@values, $form->{taxzone_id});
270 if ($form->{transaction_description}) {
271 $query .= qq| AND o.transaction_description ILIKE ?|;
272 push(@values, like($form->{transaction_description}));
275 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
276 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
277 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
280 if ($form->{reqdate_unset_or_old}) {
281 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
284 if (($form->{order_probability_value} || '') ne '') {
285 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
286 $query .= qq| AND (o.order_probability ${op} ?)|;
287 push @values, trim($form->{order_probability_value});
290 if ($form->{expected_billing_date_from}) {
291 $query .= qq| AND (o.expected_billing_date >= ?)|;
292 push @values, conv_date($form->{expected_billing_date_from});
295 if ($form->{expected_billing_date_to}) {
296 $query .= qq| AND (o.expected_billing_date <= ?)|;
297 push @values, conv_date($form->{expected_billing_date_to});
300 if ($form->{intnotes}) {
301 $query .= qq| AND o.intnotes ILIKE ?|;
302 push(@values, like($form->{intnotes}));
305 if ($form->{parts_partnumber}) {
308 SELECT orderitems.trans_id
310 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
311 WHERE (orderitems.trans_id = o.id)
312 AND (parts.partnumber ILIKE ?)
316 push @values, like($form->{parts_partnumber});
319 if ($form->{parts_description}) {
322 SELECT orderitems.trans_id
324 WHERE (orderitems.trans_id = o.id)
325 AND (orderitems.description ILIKE ?)
329 push @values, like($form->{parts_description});
333 my @tokens = parse_line('\s+', 0, $form->{all});
334 # ordnumber quonumber customer.name vendor.name transaction_description
336 o.ordnumber ILIKE ? OR
337 o.quonumber ILIKE ? OR
339 o.transaction_description ILIKE ?
341 push @values, (like($_))x4 for @tokens;
344 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
345 'trans_id_field' => 'ct.id',
349 $query .= qq| AND ($cvar_where)|;
350 push @values, @cvar_values;
353 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
354 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
355 my %allowed_sort_columns = (
356 "transdate" => "o.transdate",
357 "reqdate" => "o.reqdate",
359 "ordnumber" => "o.ordnumber",
360 "cusordnumber" => "o.cusordnumber",
361 "quonumber" => "o.quonumber",
363 "employee" => "e.name",
364 "salesman" => "s.name",
365 "shipvia" => "o.shipvia",
366 "transaction_description" => "o.transaction_description",
367 "shippingpoint" => "o.shippingpoint",
368 "insertdate" => "o.itime",
369 "taxzone" => "tz.description",
370 "payment_terms" => "pt.description",
371 "department" => "department.description",
372 "intnotes" => "o.intnotes",
374 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
375 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
377 $query .= qq| ORDER by | . $sortorder;
379 my $sth = $dbh->prepare($query);
380 $sth->execute(@values) ||
381 $form->dberror($query . " (" . join(", ", @values) . ")");
385 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
386 $ref->{billed_amount} = $billed_amount{$ref->{id}};
387 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
388 if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
389 $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
390 $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
392 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
393 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
395 $ref->{exchangerate} ||= $ref->{daily_exchangerate};
396 $ref->{exchangerate} ||= 1;
397 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
398 $id{ $ref->{id} } = $ref->{id};
403 $main::lxdebug->leave_sub();
406 sub transactions_for_todo_list {
407 $main::lxdebug->enter_sub();
412 my $myconfig = \%main::myconfig;
413 my $form = $main::form;
415 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
417 my $query = qq|SELECT id FROM employee WHERE login = ?|;
418 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
421 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
422 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
427 LEFT JOIN customer c ON (oe.customer_id = c.id)
428 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
429 LEFT JOIN employee e ON (oe.employee_id = e.id)
430 WHERE (COALESCE(quotation, FALSE) = TRUE)
431 AND (COALESCE(closed, FALSE) = FALSE)
432 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
433 AND NOT (oe.reqdate ISNULL)
434 AND (oe.reqdate < current_date)
437 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
439 $main::lxdebug->leave_sub();
445 my ($self, $myconfig, $form) = @_;
446 $main::lxdebug->enter_sub();
448 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
450 $::lxdebug->leave_sub;
456 $main::lxdebug->enter_sub();
458 my ($self, $myconfig, $form) = @_;
460 my $dbh = SL::DB->client->dbh;
461 my $restricter = SL::HTML::Restrict->create;
463 my ($query, @values, $sth, $null);
464 my $exchangerate = 0;
466 my $all_units = AM->retrieve_units($myconfig, $form);
467 $form->{all_units} = $all_units;
469 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
472 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
473 unless ($form->{employee_id}) {
474 $form->get_employee($dbh);
477 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
479 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
480 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
481 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
484 $query = qq|DELETE FROM custom_variables
485 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
486 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
487 do_query($form, $dbh, $query, $form->{id});
489 $query = qq|DELETE FROM shipto | .
490 qq|WHERE trans_id = ? AND module = 'OE'|;
491 do_query($form, $dbh, $query, $form->{id});
495 $query = qq|SELECT nextval('id')|;
496 ($form->{id}) = selectrow_query($form, $dbh, $query);
498 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
499 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
516 my @processed_orderitems;
518 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
519 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
522 for my $i (1 .. $form->{rowcount}) {
524 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
526 if ($form->{"id_$i"}) {
529 $query = qq|SELECT unit FROM parts WHERE id = ?|;
530 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
533 if (defined($all_units->{$item_unit}->{factor}) &&
534 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
535 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
537 my $baseqty = $form->{"qty_$i"} * $basefactor;
539 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
540 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
542 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
544 # keep entered selling price
546 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
548 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
550 my $decimalplaces = ($dec > 2) ? $dec : 2;
552 # undo discount formatting
553 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
556 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
558 # round linetotal at least to 2 decimal places
559 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
560 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
562 $form->{"inventory_accno_$i"} *= 1;
563 $form->{"expense_accno_$i"} *= 1;
565 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
569 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
571 if ($form->{taxincluded}) {
572 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
573 $taxbase = $linetotal - $taxamount;
575 # we are not keeping a natural price, do not round
576 $form->{"sellprice_$i"} =
577 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
579 $taxamount = $linetotal * $taxrate;
580 $taxbase = $linetotal;
583 if ($form->round_amount($taxrate, 7) == 0) {
584 if ($form->{taxincluded}) {
585 foreach my $item (@taxaccounts) {
586 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
587 $taxaccounts{$item} += $taxamount;
588 $taxdiff += $taxamount;
589 $taxbase{$item} += $taxbase;
591 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
593 foreach my $item (@taxaccounts) {
594 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
595 $taxbase{$item} += $taxbase;
599 foreach my $item (@taxaccounts) {
600 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
601 $taxbase{$item} += $taxbase;
605 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
607 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
609 # Get pricegroup_id and save it. Unfortunately the interface
610 # also uses ID "0" for signalling that none is selected, but "0"
611 # must not be stored in the database. Therefore we cannot simply
613 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
615 $pricegroup_id = undef if !$pricegroup_id;
617 # force new project, if not set yet
618 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
619 require SL::DB::Customer;
620 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
621 die "Can't find customer" unless $customer;
622 die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
623 if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
625 my $new_project = SL::DB::Project->new(
626 projectnumber => $form->{ordnumber},
627 description => $customer->name,
628 customer_id => $customer->id,
630 project_type_id => $::instance_conf->get_project_type_id,
631 project_status_id => $::instance_conf->get_project_status_id,
634 $form->{"globalproject_id"} = $new_project->id;
637 CVar->get_non_editable_ic_cvars(form => $form,
640 sub_module => 'orderitems',
641 may_converted_from => ['orderitems', 'invoice']);
645 # save detail record in orderitems table
646 if (! $form->{"orderitems_id_$i"}) {
647 $query = qq|SELECT nextval('orderitemsid')|;
648 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
650 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
651 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
654 my $orderitems_id = $form->{"orderitems_id_$i"};
655 push @processed_orderitems, $orderitems_id;
658 UPDATE orderitems SET
659 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
660 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
661 pricegroup_id = ?, subtotal = ?,
662 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
663 active_price_source = ?, active_discount_source = ?,
664 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
668 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
669 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
670 $form->{"qty_$i"}, $baseqty,
671 $fxsellprice, $form->{"discount_$i"},
672 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
673 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
674 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
675 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
676 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
677 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
678 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
679 conv_i($orderitems_id),
682 do_query($form, $dbh, $query, @values);
684 $form->{"sellprice_$i"} = $fxsellprice;
685 $form->{"discount_$i"} *= 100;
687 CVar->save_custom_variables(module => 'IC',
688 sub_module => 'orderitems',
689 trans_id => $orderitems_id,
690 configs => $ic_cvar_configs,
692 name_prefix => 'ic_',
693 name_postfix => "_$i",
696 # link previous items with orderitems
697 # assume we have a new workflow if we link from invoice or order to quotation
698 # unluckily orderitems are used for quotation and orders - therefore one more
699 # check to be sure NOT to link from order to quotation
700 foreach (qw(orderitems)) {
701 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
702 && $form->{type} !~ 'quotation') {
703 RecordLinks->create_links('dbh' => $dbh,
706 'from_ids' => $form->{"converted_from_${_}_id_$i"},
707 'to_table' => 'orderitems',
708 'to_id' => $orderitems_id,
711 delete $form->{"converted_from_${_}_id_$i"};
716 # search for orphaned ids
717 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
718 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
719 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
721 if (scalar @orphaned_ids) {
722 # clean up orderitems
723 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
724 do_query($form, $dbh, $query, @orphaned_ids);
727 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
731 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
733 $amount = $form->round_amount($netamount + $tax, 2, 1);
734 $netamount = $form->round_amount($netamount, 2);
736 if ($form->{currency} eq $form->{defaultcurrency}) {
737 $form->{exchangerate} = 1;
739 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
742 # from inputfield (exchangerate) or hidden (forex)
743 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
745 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
747 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
752 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
753 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, tax_point = ?, taxincluded = ?,
754 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
755 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
756 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
757 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
758 , order_probability = ?, expected_billing_date = ?
761 @values = ($form->{ordnumber} || '', $form->{quonumber},
762 $form->{cusordnumber}, conv_date($form->{transdate}),
763 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
764 $amount, $netamount, conv_date($reqdate), conv_date($form->{tax_point}),
765 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
766 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
767 $form->{currency}, $form->{closed} ? 't' : 'f',
768 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
769 $quotation, conv_i($form->{department_id}),
770 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
771 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
772 conv_i($form->{delivery_vendor_id}),
773 conv_i($form->{delivery_customer_id}),
774 conv_i($form->{delivery_term_id}),
775 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
776 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
777 $form->{transaction_description},
778 $form->{marge_total} * 1, $form->{marge_percent} * 1,
779 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
780 conv_i($form->{id}));
781 do_query($form, $dbh, $query, @values);
783 $form->new_lastmtime('oe');
785 $form->{ordtotal} = $amount;
787 $form->{name} = $form->{ $form->{vc} };
788 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
791 if (!$form->{shipto_id}) {
792 $form->add_shipto($dbh, $form->{id}, "OE");
795 # save printed, emailed, queued
796 $form->save_status($dbh);
798 # Link this record to the records it was created from.
799 $form->{convert_from_oe_ids} =~ s/^\s+//;
800 $form->{convert_from_oe_ids} =~ s/\s+$//;
801 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
802 delete $form->{convert_from_oe_ids};
803 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
804 RecordLinks->create_links('dbh' => $dbh,
806 'from_table' => 'oe',
807 'from_ids' => \@convert_from_oe_ids,
809 'to_id' => $form->{id},
811 $self->_close_quotations_rfqs('dbh' => $dbh,
812 'from_id' => \@convert_from_oe_ids,
813 'to_id' => $form->{id});
816 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
817 if ($form->{vc} eq 'customer') {
818 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
820 if ($form->{vc} eq 'vendor') {
821 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
825 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
826 "quonumber" : "ordnumber"};
828 Common::webdav_folder($form);
830 $self->save_periodic_invoices_config(dbh => $dbh,
831 oe_id => $form->{id},
832 config_yaml => $form->{periodic_invoices_config})
833 if ($form->{type} eq 'sales_order');
835 $main::lxdebug->leave_sub();
840 sub save_periodic_invoices_config {
841 my ($self, %params) = @_;
843 return if !$params{oe_id};
845 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
846 return if 'HASH' ne ref $config;
848 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
849 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
850 $obj->update_attributes(%{ $config });
853 sub load_periodic_invoice_config {
857 delete $form->{periodic_invoices_config};
860 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
863 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
864 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
865 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
870 sub _close_quotations_rfqs {
871 $main::lxdebug->enter_sub();
876 Common::check_params(\%params, qw(from_id to_id));
878 my $myconfig = \%main::myconfig;
879 my $form = $main::form;
881 my $dbh = $params{dbh} || SL::DB->client->dbh;
883 SL::DB->client->with_transaction(sub {
885 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
886 my $sth = prepare_query($form, $dbh, $query);
888 do_statement($form, $sth, $query, conv_i($params{to_id}));
890 my ($quotation) = $sth->fetchrow_array();
898 foreach my $from_id (@{ $params{from_id} }) {
899 $from_id = conv_i($from_id);
900 do_statement($form, $sth, $query, $from_id);
901 ($quotation) = $sth->fetchrow_array();
902 push @close_ids, $from_id if ($quotation);
907 if (scalar @close_ids) {
908 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
909 do_query($form, $dbh, $query, @close_ids);
912 }) or do { die SL::DB->client->error };
914 $main::lxdebug->leave_sub();
918 $main::lxdebug->enter_sub();
920 my ($self, $myconfig, $form) = @_;
922 my $rc = SL::DB::Order->new->db->with_transaction(sub {
923 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
925 SL::DB::Order->new(id => $form->{id})->delete;
927 my $spool = $::lx_office_conf{paths}->{spool};
928 unlink map { "$spool/$_" } @spoolfiles if $spool;
931 }) or do { die SL::DB->client->error };
933 $main::lxdebug->leave_sub();
939 my ($self, $myconfig, $form) = @_;
940 $main::lxdebug->enter_sub();
942 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
944 $::lxdebug->leave_sub;
949 my ($self, $myconfig, $form) = @_;
951 # connect to database
952 my $dbh = SL::DB->client->dbh;
954 my ($query, $query_add, @values, @ids, $sth);
956 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
958 push @ids, $form->{"trans_id_$_"}
959 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
960 } (1 .. $form->{"rowcount"});
962 if ($form->{rowcount} && scalar @ids) {
963 $form->{convert_from_oe_ids} = join ' ', @ids;
966 # if called in multi id mode, and still only got one id, switch back to single id
967 if ($form->{"rowcount"} and $#ids == 0) {
968 $form->{"id"} = $ids[0];
970 delete $form->{convert_from_oe_ids};
973 # and remember for the rest of the function
974 my $is_collective_order = scalar @ids;
976 # If collective order was created from exactly 1 order, we assume the same
977 # behaviour as a "save as new" from within an order is actually desired, i.e.
978 # the original order isn't part of a workflow where we want to remember
979 # record_links, but simply a quick way of generating a new order from an old
980 # one without having to enter everything again.
981 # Setting useasnew will prevent the creation of record_links for the items
982 # when saving the new order.
983 # This form variable is probably not necessary, could just set saveasnew instead
984 $form->{useasnew} = 1 if $is_collective_order == 1;
987 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
988 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
989 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
990 $form->{transdate} = DateTime->today_local->to_kivitendo;
993 # get default accounts
994 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
995 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
996 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
997 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
998 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
999 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
1000 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
1003 my $ref = selectfirst_hashref_query($form, $dbh, $query);
1004 map { $form->{$_} = $ref->{$_} } keys %$ref;
1006 $form->{currency} = $form->get_default_currency($myconfig);
1008 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
1009 # we come from invoices, feel free.
1010 $form->{reqdate} = $form->{deliverydate}
1011 if ( $form->{deliverydate}
1012 and $form->{callback} =~ /action=ar_transactions/);
1014 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
1016 if ($form->{id} or @ids) {
1018 # retrieve order for single id
1019 # NOTE: this query is intended to fetch all information only ONCE.
1020 # so if any of these infos is important (or even different) for any item,
1021 # it will be killed out and then has to be fetched from the item scope query further down
1023 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1024 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1025 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1026 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1027 o.closed, o.reqdate, o.tax_point, o.quonumber, o.department_id, o.cusordnumber,
1029 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1030 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1031 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1032 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1034 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1035 LEFT JOIN employee e ON (o.employee_id = e.id)
1036 LEFT JOIN department d ON (o.department_id = d.id) | .
1039 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1041 @values = $form->{id} ? ($form->{id}) : @ids;
1042 $sth = prepare_execute_query($form, $dbh, $query, @values);
1044 $ref = $sth->fetchrow_hashref("NAME_lc");
1047 map { $form->{$_} = $ref->{$_} } keys %$ref;
1049 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1051 # set all entries for multiple ids blank that yield different information
1052 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1053 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1056 $form->{mtime} ||= $form->{itime};
1057 $form->{lastmtime} = $form->{mtime};
1059 # if not given, fill transdate with current_date
1060 $form->{transdate} = $form->current_date($myconfig)
1061 unless $form->{transdate};
1065 if ($form->{delivery_customer_id}) {
1066 $query = qq|SELECT name FROM customer WHERE id = ?|;
1067 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1070 if ($form->{delivery_vendor_id}) {
1071 $query = qq|SELECT name FROM customer WHERE id = ?|;
1072 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1075 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1077 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1078 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1080 $ref = $sth->fetchrow_hashref("NAME_lc");
1081 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1084 if ($ref->{shipto_id}) {
1085 my $cvars = CVar->get_custom_variables(
1088 trans_id => $ref->{shipto_id},
1090 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1093 # get printed, emailed and queued
1094 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1095 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1097 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1098 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1099 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1100 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1103 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1106 my $transdate = $form->{tax_point} ? $dbh->quote($form->{tax_point}) : $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1108 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1109 unshift @values, ($form->{taxzone_id}) x 2;
1111 # retrieve individual items
1112 # this query looks up all information about the items
1113 # stuff different from the whole will not be overwritten, but saved with a suffix.
1115 qq|SELECT o.id AS orderitems_id,
1116 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1117 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1118 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1119 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1120 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1121 p.classification_id,
1122 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1123 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1124 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1125 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1126 pr.projectnumber, p.formel,
1127 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1129 JOIN parts p ON (o.parts_id = p.id)
1130 JOIN oe ON (o.trans_id = oe.id)
1131 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1132 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)
1133 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)
1134 LEFT JOIN project pr ON (o.project_id = pr.id)
1135 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1137 ? qq|WHERE o.trans_id = ?|
1138 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1139 qq|ORDER BY o.trans_id, o.position|;
1141 @ids = $form->{id} ? ($form->{id}) : @ids;
1142 $sth = prepare_execute_query($form, $dbh, $query, @values);
1144 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1145 # Retrieve custom variables.
1146 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1148 sub_module => 'orderitems',
1149 trans_id => $ref->{orderitems_id},
1151 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1154 if (!$ref->{"part_type"} eq 'part') {
1155 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1157 # delete($ref->{"part_inventory_accno_id"});
1159 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1160 # unless already present there
1161 # remove _oe entries afterwards
1162 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1163 qw|ordnumber transdate cusordnumber|
1165 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1169 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1171 qq|SELECT accno AS inventory_accno, | .
1172 qq| new_chart_id AS inventory_new_chart, | .
1173 qq| date($transdate) - valid_from AS inventory_valid | .
1174 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1175 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1176 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1179 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1181 qq|SELECT accno AS income_accno, | .
1182 qq| new_chart_id AS income_new_chart, | .
1183 qq| date($transdate) - valid_from AS income_valid | .
1184 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1185 ($ref->{income_accno}, $ref->{income_new_chart},
1186 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1189 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1191 qq|SELECT accno AS expense_accno, | .
1192 qq| new_chart_id AS expense_new_chart, | .
1193 qq| date($transdate) - valid_from AS expense_valid | .
1194 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1195 ($ref->{expense_accno}, $ref->{expense_new_chart},
1196 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1199 # delete orderitems_id in collective orders, so that they get cloned no matter what
1200 # is this correct? or is the following meant?
1201 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1202 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1204 # get tax rates and description
1205 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1207 qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
1209 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1210 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1211 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1212 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1213 qq|ORDER BY c.accno|;
1214 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1215 $ref->{taxaccounts} = "";
1217 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1218 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1222 $ref->{taxaccounts} .= "$ptr->{accno} ";
1223 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1224 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1225 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1226 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1227 $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
1228 $form->{taxaccounts} .= "$ptr->{accno} ";
1233 chop $ref->{taxaccounts};
1235 push @{ $form->{form_details} }, $ref;
1242 # get last name used
1243 $form->lastname_used($dbh, $myconfig, $form->{vc})
1244 unless $form->{"$form->{vc}_id"};
1248 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1250 Common::webdav_folder($form);
1252 $self->load_periodic_invoice_config($form);
1257 sub retrieve_simple {
1258 $main::lxdebug->enter_sub();
1263 Common::check_params(\%params, qw(id));
1265 my $myconfig = \%main::myconfig;
1266 my $form = $main::form;
1268 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1270 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1271 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1273 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1274 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1276 $main::lxdebug->leave_sub();
1282 $main::lxdebug->enter_sub();
1284 my ($self, $myconfig, $form) = @_;
1286 # connect to database
1287 my $dbh = SL::DB->client->dbh;
1293 my $nodiscount_subtotal = 0;
1294 my $discount_subtotal = 0;
1297 my @partsgroup = ();
1300 my $subtotal_header = 0;
1301 my $subposition = 0;
1309 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1311 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1314 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1315 $price_factors{$pfac->{id}} = $pfac;
1316 $pfac->{factor} *= 1;
1317 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1320 # sort items by partsgroup
1321 for $i (1 .. $form->{rowcount}) {
1323 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1324 $partsgroup = $form->{"partsgroup_$i"};
1326 push @partsgroup, [$i, $partsgroup];
1327 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1333 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1334 %projects_by_id = map { $_->id => $_ } @$projects;
1337 if ($projects_by_id{$form->{"globalproject_id"}}) {
1338 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1339 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1341 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1342 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1346 $form->{discount} = [];
1348 # get some values of parts from db on store them in extra array,
1349 # so that they can be sorted in later
1350 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1351 my @prepared_arrays = keys %prepared_template_arrays;
1352 my @separate_totals = qw(non_separate_subtotal);
1354 $form->{TEMPLATE_ARRAYS} = { };
1356 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1357 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1360 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1361 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1362 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1363 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1364 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1366 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1367 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1369 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1371 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1373 my $totalweight = 0;
1375 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1378 if ($item->[1] ne $sameitem) {
1379 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1380 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1381 $sameitem = $item->[1];
1383 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1386 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1388 if ($form->{"id_$i"} != 0) {
1390 # add number, description and qty to $form->{number}, ....
1392 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1393 $subtotal_header = $i;
1394 $position = int($position);
1397 } elsif ($subtotal_header) {
1399 $position = int($position);
1400 $position = $position.".".$subposition;
1402 $position = int($position);
1406 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1408 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1410 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1411 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1412 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1413 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1414 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1415 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1416 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1417 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1418 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1419 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1420 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1421 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1422 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1423 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1424 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1425 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1426 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1427 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1428 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1429 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1430 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1432 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1433 my ($dec) = ($sellprice =~ /\.(\d+)/);
1434 my $decimalplaces = max 2, length($dec);
1436 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1438 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1439 my $linetotal = $form->round_amount($linetotal_exact, 2);
1441 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1442 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1444 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1446 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1448 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1450 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1451 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1453 $linetotal = ($linetotal != 0) ? $linetotal : '';
1455 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1456 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1457 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1459 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1460 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1461 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1462 push @separate_totals , "separate_${pabbr}_subtotal";
1463 $form->{"separate_${pabbr}_subtotal"} = 0;
1465 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1467 $form->{non_separate_subtotal} += $linetotal;
1470 $form->{ordtotal} += $linetotal;
1471 $form->{nodiscount_total} += $nodiscount_linetotal;
1472 $form->{discount_total} += $discount;
1474 if ($subtotal_header) {
1475 $discount_subtotal += $linetotal;
1476 $nodiscount_subtotal += $nodiscount_linetotal;
1479 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1480 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1481 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1482 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1483 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1485 $discount_subtotal = 0;
1486 $nodiscount_subtotal = 0;
1487 $subtotal_header = 0;
1490 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1493 if (!$form->{"discount_$i"}) {
1494 $nodiscount += $linetotal;
1497 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1499 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1500 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1501 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1502 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1503 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1504 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1506 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1507 $totalweight += $lineweight;
1508 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1509 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1510 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1511 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1513 my ($taxamount, $taxbase);
1516 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1518 if ($form->{taxincluded}) {
1521 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1522 $taxbase = $linetotal / (1 + $taxrate);
1524 $taxamount = $linetotal * $taxrate;
1525 $taxbase = $linetotal;
1528 if ($taxamount != 0) {
1529 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1530 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1531 $taxbase{$accno} += $taxbase;
1535 $tax_rate = $taxrate * 100;
1536 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1538 if ($form->{"part_type_$i"} eq 'assembly') {
1541 # get parts and push them onto the stack
1543 if ($form->{groupitems}) {
1544 $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
1546 $sortorder = qq|ORDER BY a.position|;
1549 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1550 qq|pg.partsgroup | .
1551 qq|FROM assembly a | .
1552 qq| JOIN parts p ON (a.parts_id = p.id) | .
1553 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1554 qq| WHERE a.bom = '1' | .
1555 qq| AND a.id = ? | . $sortorder;
1556 @values = ($form->{"id_$i"});
1557 $sth = $dbh->prepare($query);
1558 $sth->execute(@values) || $form->dberror($query);
1560 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1561 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1562 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1563 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1564 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1565 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1568 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1569 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1570 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1575 CVar->get_non_editable_ic_cvars(form => $form,
1578 sub_module => 'orderitems',
1579 may_converted_from => ['orderitems', 'invoice']);
1581 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1582 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1583 for @{ $ic_cvar_configs };
1585 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1589 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1590 $form->{totalweight_nofmt} = $totalweight;
1591 my $defaults = AM->get_defaults();
1592 $form->{weightunit} = $defaults->{weightunit};
1595 foreach $item (sort keys %taxaccounts) {
1596 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1598 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1599 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1600 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1601 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1602 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1603 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1604 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1605 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} }, $form->{"${item}_tax_id"});
1607 if ( $form->{"${item}_tax_id"} ) {
1608 my $tax_obj = SL::DB::Manager::Tax->find_by(id => $form->{"${item}_tax_id"}) or die "Can't find tax with id " . $form->{"${item}_tax_id"};
1609 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1610 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1614 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1615 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1616 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1617 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1619 if($form->{taxincluded}) {
1620 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1621 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1623 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1624 $form->{subtotal_nofmt} = $form->{ordtotal};
1627 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1628 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1629 $form->{rounding} = $form->round_amount(
1630 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1635 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1636 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1638 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1640 $form->{username} = $myconfig->{name};
1642 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1643 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1644 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1646 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1647 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1649 $main::lxdebug->leave_sub();
1658 OE.pm - Order entry module
1662 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>.
1668 =item retrieve_simple PARAMS
1670 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1672 my $order = retrieve_simple(id => 2);