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 if ( ($form->{type} eq 'sales_order' && !$::instance_conf->get_deliverydate_on)
990 || ($form->{type} eq 'sales_quotation' && !$::instance_conf->get_reqdate_on)) {
991 $form->{reqdate} = '';
993 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
995 $form->{transdate} = DateTime->today_local->to_kivitendo;
998 # get default accounts
999 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
1000 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
1001 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
1002 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
1003 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
1004 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
1005 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
1008 my $ref = selectfirst_hashref_query($form, $dbh, $query);
1009 map { $form->{$_} = $ref->{$_} } keys %$ref;
1011 $form->{currency} = $form->get_default_currency($myconfig);
1013 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
1014 # we come from invoices, feel free.
1015 $form->{reqdate} = $form->{deliverydate}
1016 if ( $form->{deliverydate}
1017 and $form->{callback} =~ /action=ar_transactions/);
1019 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
1021 if ($form->{id} or @ids) {
1023 # retrieve order for single id
1024 # NOTE: this query is intended to fetch all information only ONCE.
1025 # so if any of these infos is important (or even different) for any item,
1026 # it will be killed out and then has to be fetched from the item scope query further down
1028 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1029 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1030 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1031 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1032 o.closed, o.reqdate, o.tax_point, o.quonumber, o.department_id, o.cusordnumber,
1034 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1035 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1036 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1037 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1039 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1040 LEFT JOIN employee e ON (o.employee_id = e.id)
1041 LEFT JOIN department d ON (o.department_id = d.id) | .
1044 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1046 @values = $form->{id} ? ($form->{id}) : @ids;
1047 $sth = prepare_execute_query($form, $dbh, $query, @values);
1049 $ref = $sth->fetchrow_hashref("NAME_lc");
1052 map { $form->{$_} = $ref->{$_} } keys %$ref;
1054 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1056 # set all entries for multiple ids blank that yield different information
1057 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1058 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1061 $form->{mtime} ||= $form->{itime};
1062 $form->{lastmtime} = $form->{mtime};
1064 # if not given, fill transdate with current_date
1065 $form->{transdate} = $form->current_date($myconfig)
1066 unless $form->{transdate};
1070 if ($form->{delivery_customer_id}) {
1071 $query = qq|SELECT name FROM customer WHERE id = ?|;
1072 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1075 if ($form->{delivery_vendor_id}) {
1076 $query = qq|SELECT name FROM customer WHERE id = ?|;
1077 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1080 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1082 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1083 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1085 $ref = $sth->fetchrow_hashref("NAME_lc");
1086 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1089 if ($ref->{shipto_id}) {
1090 my $cvars = CVar->get_custom_variables(
1093 trans_id => $ref->{shipto_id},
1095 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1098 # get printed, emailed and queued
1099 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1100 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1102 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1103 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1104 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1105 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1108 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1111 my $transdate = $form->{tax_point} ? $dbh->quote($form->{tax_point}) : $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1113 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1114 unshift @values, ($form->{taxzone_id}) x 2;
1116 # retrieve individual items
1117 # this query looks up all information about the items
1118 # stuff different from the whole will not be overwritten, but saved with a suffix.
1120 qq|SELECT o.id AS orderitems_id,
1121 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1122 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1123 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1124 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1125 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1126 p.classification_id,
1127 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1128 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1129 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1130 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1131 pr.projectnumber, p.formel,
1132 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1134 JOIN parts p ON (o.parts_id = p.id)
1135 JOIN oe ON (o.trans_id = oe.id)
1136 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1137 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)
1138 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)
1139 LEFT JOIN project pr ON (o.project_id = pr.id)
1140 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1142 ? qq|WHERE o.trans_id = ?|
1143 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1144 qq|ORDER BY o.trans_id, o.position|;
1146 @ids = $form->{id} ? ($form->{id}) : @ids;
1147 $sth = prepare_execute_query($form, $dbh, $query, @values);
1149 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1150 # Retrieve custom variables.
1151 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1153 sub_module => 'orderitems',
1154 trans_id => $ref->{orderitems_id},
1156 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1159 if (!$ref->{"part_type"} eq 'part') {
1160 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1162 # delete($ref->{"part_inventory_accno_id"});
1164 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1165 # unless already present there
1166 # remove _oe entries afterwards
1167 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1168 qw|ordnumber transdate cusordnumber|
1170 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1174 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1176 qq|SELECT accno AS inventory_accno, | .
1177 qq| new_chart_id AS inventory_new_chart, | .
1178 qq| date($transdate) - valid_from AS inventory_valid | .
1179 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1180 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1181 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1184 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1186 qq|SELECT accno AS income_accno, | .
1187 qq| new_chart_id AS income_new_chart, | .
1188 qq| date($transdate) - valid_from AS income_valid | .
1189 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1190 ($ref->{income_accno}, $ref->{income_new_chart},
1191 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1194 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1196 qq|SELECT accno AS expense_accno, | .
1197 qq| new_chart_id AS expense_new_chart, | .
1198 qq| date($transdate) - valid_from AS expense_valid | .
1199 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1200 ($ref->{expense_accno}, $ref->{expense_new_chart},
1201 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1204 # delete orderitems_id in collective orders, so that they get cloned no matter what
1205 # is this correct? or is the following meant?
1206 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1207 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1209 # get tax rates and description
1210 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1212 qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
1214 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1215 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1216 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1217 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1218 qq|ORDER BY c.accno|;
1219 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1220 $ref->{taxaccounts} = "";
1222 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1223 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1227 $ref->{taxaccounts} .= "$ptr->{accno} ";
1228 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1229 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1230 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1231 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1232 $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
1233 $form->{taxaccounts} .= "$ptr->{accno} ";
1238 chop $ref->{taxaccounts};
1240 push @{ $form->{form_details} }, $ref;
1247 # get last name used
1248 $form->lastname_used($dbh, $myconfig, $form->{vc})
1249 unless $form->{"$form->{vc}_id"};
1253 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1255 Common::webdav_folder($form);
1257 $self->load_periodic_invoice_config($form);
1262 sub retrieve_simple {
1263 $main::lxdebug->enter_sub();
1268 Common::check_params(\%params, qw(id));
1270 my $myconfig = \%main::myconfig;
1271 my $form = $main::form;
1273 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1275 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1276 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1278 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1279 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1281 $main::lxdebug->leave_sub();
1287 $main::lxdebug->enter_sub();
1289 my ($self, $myconfig, $form) = @_;
1291 # connect to database
1292 my $dbh = SL::DB->client->dbh;
1298 my $nodiscount_subtotal = 0;
1299 my $discount_subtotal = 0;
1302 my @partsgroup = ();
1305 my $subtotal_header = 0;
1306 my $subposition = 0;
1314 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1316 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1319 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1320 $price_factors{$pfac->{id}} = $pfac;
1321 $pfac->{factor} *= 1;
1322 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1325 # sort items by partsgroup
1326 for $i (1 .. $form->{rowcount}) {
1328 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1329 $partsgroup = $form->{"partsgroup_$i"};
1331 push @partsgroup, [$i, $partsgroup];
1332 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1338 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1339 %projects_by_id = map { $_->id => $_ } @$projects;
1342 if ($projects_by_id{$form->{"globalproject_id"}}) {
1343 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1344 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1346 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1347 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1351 $form->{discount} = [];
1353 # get some values of parts from db on store them in extra array,
1354 # so that they can be sorted in later
1355 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1356 my @prepared_arrays = keys %prepared_template_arrays;
1357 my @separate_totals = qw(non_separate_subtotal);
1359 $form->{TEMPLATE_ARRAYS} = { };
1361 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1362 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1365 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1366 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1367 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1368 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1369 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt optional);
1371 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1372 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1374 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1376 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1378 my $totalweight = 0;
1380 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1383 if ($item->[1] ne $sameitem) {
1384 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1385 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1386 $sameitem = $item->[1];
1388 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1391 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1393 if ($form->{"id_$i"} != 0) {
1395 # add number, description and qty to $form->{number}, ....
1397 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1398 $subtotal_header = $i;
1399 $position = int($position);
1402 } elsif ($subtotal_header) {
1404 $position = int($position);
1405 $position = $position.".".$subposition;
1407 $position = int($position);
1411 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1413 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1415 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1416 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1417 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1418 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1419 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1420 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1421 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1422 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1423 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1424 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1425 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1426 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1427 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1428 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1429 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1430 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1431 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1432 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1433 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1434 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1435 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1436 push @{ $form->{TEMPLATE_ARRAYS}->{optional} }, $form->{"optional_$i"};
1438 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1439 my ($dec) = ($sellprice =~ /\.(\d+)/);
1440 my $decimalplaces = max 2, length($dec);
1442 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1444 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1445 my $linetotal = $form->round_amount($linetotal_exact, 2);
1447 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1448 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1450 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1452 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1454 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1456 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1457 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1459 $linetotal = ($linetotal != 0) ? $linetotal : '';
1461 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1462 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1463 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1465 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1466 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1467 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1468 push @separate_totals , "separate_${pabbr}_subtotal";
1469 $form->{"separate_${pabbr}_subtotal"} = 0;
1471 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1473 $form->{non_separate_subtotal} += $linetotal;
1476 $form->{ordtotal} += $linetotal unless $form->{"optional_$i"};
1477 $form->{nodiscount_total} += $nodiscount_linetotal;
1478 $form->{discount_total} += $discount;
1480 if ($subtotal_header) {
1481 $discount_subtotal += $linetotal;
1482 $nodiscount_subtotal += $nodiscount_linetotal;
1485 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1486 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1487 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1488 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1489 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1491 $discount_subtotal = 0;
1492 $nodiscount_subtotal = 0;
1493 $subtotal_header = 0;
1496 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1499 if (!$form->{"discount_$i"}) {
1500 $nodiscount += $linetotal;
1503 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1505 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1506 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1507 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1508 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1509 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1510 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1512 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1513 $totalweight += $lineweight;
1514 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1515 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1516 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1517 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1519 my ($taxamount, $taxbase);
1522 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1524 unless ($form->{"optional_$i"}) {
1525 if ($form->{taxincluded}) {
1528 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1529 $taxbase = $linetotal / (1 + $taxrate);
1531 $taxamount = $linetotal * $taxrate;
1532 $taxbase = $linetotal;
1536 if ($taxamount != 0) {
1537 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1538 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1539 $taxbase{$accno} += $taxbase;
1543 $tax_rate = $taxrate * 100;
1544 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1546 if ($form->{"part_type_$i"} eq 'assembly') {
1549 # get parts and push them onto the stack
1551 if ($form->{groupitems}) {
1552 $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
1554 $sortorder = qq|ORDER BY a.position|;
1557 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1558 qq|pg.partsgroup | .
1559 qq|FROM assembly a | .
1560 qq| JOIN parts p ON (a.parts_id = p.id) | .
1561 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1562 qq| WHERE a.bom = '1' | .
1563 qq| AND a.id = ? | . $sortorder;
1564 @values = ($form->{"id_$i"});
1565 $sth = $dbh->prepare($query);
1566 $sth->execute(@values) || $form->dberror($query);
1568 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1569 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1570 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1571 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1572 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1573 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1576 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1577 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1578 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1583 CVar->get_non_editable_ic_cvars(form => $form,
1586 sub_module => 'orderitems',
1587 may_converted_from => ['orderitems', 'invoice']);
1589 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1590 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1591 for @{ $ic_cvar_configs };
1593 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1597 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1598 $form->{totalweight_nofmt} = $totalweight;
1599 my $defaults = AM->get_defaults();
1600 $form->{weightunit} = $defaults->{weightunit};
1603 foreach $item (sort keys %taxaccounts) {
1604 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1606 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1607 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1608 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1609 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1610 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1611 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1612 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1613 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} }, $form->{"${item}_tax_id"});
1615 if ( $form->{"${item}_tax_id"} ) {
1616 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"};
1617 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1618 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1622 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1623 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1624 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1625 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1627 if($form->{taxincluded}) {
1628 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1629 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1631 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1632 $form->{subtotal_nofmt} = $form->{ordtotal};
1635 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1636 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1637 $form->{rounding} = $form->round_amount(
1638 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1643 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1644 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1646 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1648 $form->{username} = $myconfig->{name};
1650 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1651 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1652 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1654 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1655 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1657 $main::lxdebug->leave_sub();
1666 OE.pm - Order entry module
1670 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>.
1676 =item retrieve_simple PARAMS
1678 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1680 my $order = retrieve_simple(id => 2);