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, | .
126 qq| department.description as department, | .
127 qq| ex.$rate AS daily_exchangerate, | .
128 qq| pt.description AS payment_terms, | .
129 qq| pr.projectnumber AS globalprojectnumber, | .
130 qq| e.name AS employee, s.name AS salesman, | .
131 qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
132 qq| tz.description AS taxzone | .
133 $periodic_invoices_columns .
134 qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
136 qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
137 qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
138 qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
139 qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
140 qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
141 qq| AND ex.transdate = o.transdate) | .
142 qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
143 qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
144 qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
145 qq|LEFT JOIN department ON (o.department_id = department.id) | .
146 qq|$periodic_invoices_joins | .
147 qq|WHERE (o.quotation = ?) |;
148 push(@values, $quotation);
150 if ($form->{department_id}) {
151 $query .= qq| AND o.department_id = ?|;
152 push(@values, $form->{department_id});
155 if ($form->{"project_id"}) {
157 qq|AND ((globalproject_id = ?) OR EXISTS | .
158 qq| (SELECT * FROM orderitems oi | .
159 qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
160 push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
163 if ($form->{"projectnumber"}) {
165 AND ((pr.projectnumber ILIKE ?) OR EXISTS (
166 SELECT * FROM orderitems oi
167 LEFT JOIN project proi ON proi.id = oi.project_id
168 WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
171 push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
174 if ($form->{"business_id"}) {
175 $query .= " AND ct.business_id = ?";
176 push(@values, $form->{"business_id"});
179 if ($form->{"${vc}_id"}) {
180 $query .= " AND o.${vc}_id = ?";
181 push(@values, $form->{"${vc}_id"});
183 } elsif ($form->{$vc}) {
184 $query .= " AND ct.name ILIKE ?";
185 push(@values, like($form->{$vc}));
188 if ($form->{"cp_name"}) {
189 $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
190 push(@values, (like($form->{"cp_name"}))x2);
193 if ( !(($vc eq 'customer' && $main::auth->assert('sales_all_edit', 1)) || ($vc eq 'vendor' && $main::auth->assert('purchase_all_edit', 1))) ) {
194 $query .= " AND o.employee_id = (select id from employee where login= ?)";
195 push @values, $::myconfig{login};
197 if ($form->{employee_id}) {
198 $query .= " AND o.employee_id = ?";
199 push @values, conv_i($form->{employee_id});
202 if ($form->{salesman_id}) {
203 $query .= " AND o.salesman_id = ?";
204 push @values, conv_i($form->{salesman_id});
207 if (!$form->{open} && !$form->{closed}) {
208 $query .= " AND o.id = 0";
209 } elsif (!($form->{open} && $form->{closed})) {
210 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
213 if (($form->{"notdelivered"} || $form->{"delivered"}) &&
214 ($form->{"notdelivered"} ne $form->{"delivered"})) {
215 $query .= $form->{"delivered"} ?
216 " AND o.delivered " : " AND NOT o.delivered";
219 if ($form->{$ordnumber}) {
220 $query .= qq| AND o.$ordnumber ILIKE ?|;
221 push(@values, like($form->{$ordnumber}));
224 if ($form->{cusordnumber}) {
225 $query .= qq| AND o.cusordnumber ILIKE ?|;
226 push(@values, like($form->{cusordnumber}));
229 if($form->{transdatefrom}) {
230 $query .= qq| AND o.transdate >= ?|;
231 push(@values, conv_date($form->{transdatefrom}));
234 if($form->{transdateto}) {
235 $query .= qq| AND o.transdate <= ?|;
236 push(@values, conv_date($form->{transdateto}));
239 if($form->{reqdatefrom}) {
240 $query .= qq| AND o.reqdate >= ?|;
241 push(@values, conv_date($form->{reqdatefrom}));
244 if($form->{reqdateto}) {
245 $query .= qq| AND o.reqdate <= ?|;
246 push(@values, conv_date($form->{reqdateto}));
249 if($form->{insertdatefrom}) {
250 $query .= qq| AND o.itime::DATE >= ?|;
251 push(@values, conv_date($form->{insertdatefrom}));
254 if($form->{insertdateto}) {
255 $query .= qq| AND o.itime::DATE <= ?|;
256 push(@values, conv_date($form->{insertdateto}));
259 if ($form->{shippingpoint}) {
260 $query .= qq| AND o.shippingpoint ILIKE ?|;
261 push(@values, like($form->{shippingpoint}));
264 if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
265 $query .= qq| AND tz.id = ?|;
266 push(@values, $form->{taxzone_id});
269 if ($form->{transaction_description}) {
270 $query .= qq| AND o.transaction_description ILIKE ?|;
271 push(@values, like($form->{transaction_description}));
274 if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
275 my $not = $form->{periodic_invoices_inactive} ? 'NOT' : '';
276 $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
279 if ($form->{reqdate_unset_or_old}) {
280 $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;
283 if (($form->{order_probability_value} || '') ne '') {
284 my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
285 $query .= qq| AND (o.order_probability ${op} ?)|;
286 push @values, trim($form->{order_probability_value});
289 if ($form->{expected_billing_date_from}) {
290 $query .= qq| AND (o.expected_billing_date >= ?)|;
291 push @values, conv_date($form->{expected_billing_date_from});
294 if ($form->{expected_billing_date_to}) {
295 $query .= qq| AND (o.expected_billing_date <= ?)|;
296 push @values, conv_date($form->{expected_billing_date_to});
299 if ($form->{parts_partnumber}) {
302 SELECT orderitems.trans_id
304 LEFT JOIN parts ON (orderitems.parts_id = parts.id)
305 WHERE (orderitems.trans_id = o.id)
306 AND (parts.partnumber ILIKE ?)
310 push @values, like($form->{parts_partnumber});
313 if ($form->{parts_description}) {
316 SELECT orderitems.trans_id
318 WHERE (orderitems.trans_id = o.id)
319 AND (orderitems.description ILIKE ?)
323 push @values, like($form->{parts_description});
327 my @tokens = parse_line('\s+', 0, $form->{all});
328 # ordnumber quonumber customer.name vendor.name transaction_description
330 o.ordnumber ILIKE ? OR
331 o.quonumber ILIKE ? OR
333 o.transaction_description ILIKE ?
335 push @values, (like($_))x4 for @tokens;
338 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
339 'trans_id_field' => 'ct.id',
343 $query .= qq| AND ($cvar_where)|;
344 push @values, @cvar_values;
347 my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
348 my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
349 my %allowed_sort_columns = (
350 "transdate" => "o.transdate",
351 "reqdate" => "o.reqdate",
353 "ordnumber" => "o.ordnumber",
354 "cusordnumber" => "o.cusordnumber",
355 "quonumber" => "o.quonumber",
357 "employee" => "e.name",
358 "salesman" => "s.name",
359 "shipvia" => "o.shipvia",
360 "transaction_description" => "o.transaction_description",
361 "shippingpoint" => "o.shippingpoint",
362 "insertdate" => "o.itime",
363 "taxzone" => "tz.description",
364 "payment_terms" => "pt.description",
365 "department" => "department.description",
367 if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
368 $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}" . ", o.itime ${sortdir}";
370 $query .= qq| ORDER by | . $sortorder;
372 my $sth = $dbh->prepare($query);
373 $sth->execute(@values) ||
374 $form->dberror($query . " (" . join(", ", @values) . ")");
378 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
379 $ref->{billed_amount} = $billed_amount{$ref->{id}};
380 $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
381 if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
382 $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
383 $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
385 $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
386 $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
388 $ref->{exchangerate} ||= $ref->{daily_exchangerate};
389 $ref->{exchangerate} ||= 1;
390 push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
391 $id{ $ref->{id} } = $ref->{id};
396 $main::lxdebug->leave_sub();
399 sub transactions_for_todo_list {
400 $main::lxdebug->enter_sub();
405 my $myconfig = \%main::myconfig;
406 my $form = $main::form;
408 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
410 my $query = qq|SELECT id FROM employee WHERE login = ?|;
411 my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
414 qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
415 CASE WHEN (COALESCE(oe.customer_id, 0) = 0) THEN 'vendor' ELSE 'customer' END AS vc,
420 LEFT JOIN customer c ON (oe.customer_id = c.id)
421 LEFT JOIN vendor v ON (oe.vendor_id = v.id)
422 LEFT JOIN employee e ON (oe.employee_id = e.id)
423 WHERE (COALESCE(quotation, FALSE) = TRUE)
424 AND (COALESCE(closed, FALSE) = FALSE)
425 AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))
426 AND NOT (oe.reqdate ISNULL)
427 AND (oe.reqdate < current_date)
430 my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);
432 $main::lxdebug->leave_sub();
438 my ($self, $myconfig, $form) = @_;
439 $main::lxdebug->enter_sub();
441 my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
443 $::lxdebug->leave_sub;
449 $main::lxdebug->enter_sub();
451 my ($self, $myconfig, $form) = @_;
453 my $dbh = SL::DB->client->dbh;
454 my $restricter = SL::HTML::Restrict->create;
456 my ($query, @values, $sth, $null);
457 my $exchangerate = 0;
459 my $all_units = AM->retrieve_units($myconfig, $form);
460 $form->{all_units} = $all_units;
462 my $ic_cvar_configs = CVar->get_configs(module => 'IC',
465 $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id};
466 unless ($form->{employee_id}) {
467 $form->get_employee($dbh);
470 my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
472 my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber';
473 my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id});
474 $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
477 $query = qq|DELETE FROM custom_variables
478 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
479 AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|;
480 do_query($form, $dbh, $query, $form->{id});
482 $query = qq|DELETE FROM shipto | .
483 qq|WHERE trans_id = ? AND module = 'OE'|;
484 do_query($form, $dbh, $query, $form->{id});
488 $query = qq|SELECT nextval('id')|;
489 ($form->{id}) = selectrow_query($form, $dbh, $query);
491 $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id, taxzone_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults), ?)|;
492 do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{taxzone_id});
509 my @processed_orderitems;
511 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
512 my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
515 for my $i (1 .. $form->{rowcount}) {
517 map({ $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
519 if ($form->{"id_$i"}) {
522 $query = qq|SELECT unit FROM parts WHERE id = ?|;
523 my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
526 if (defined($all_units->{$item_unit}->{factor}) &&
527 (($all_units->{$item_unit}->{factor} * 1) != 0)) {
528 $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
530 my $baseqty = $form->{"qty_$i"} * $basefactor;
532 $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
533 $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
535 $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
537 # keep entered selling price
539 $form->parse_amount($myconfig, $form->{"sellprice_$i"});
541 my ($dec) = ($fxsellprice =~ /\.(\d+)/);
543 my $decimalplaces = ($dec > 2) ? $dec : 2;
545 # undo discount formatting
546 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
549 $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
551 # round linetotal at least to 2 decimal places
552 $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
553 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
555 $form->{"inventory_accno_$i"} *= 1;
556 $form->{"expense_accno_$i"} *= 1;
558 @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
562 map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
564 if ($form->{taxincluded}) {
565 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
566 $taxbase = $linetotal - $taxamount;
568 # we are not keeping a natural price, do not round
569 $form->{"sellprice_$i"} =
570 $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
572 $taxamount = $linetotal * $taxrate;
573 $taxbase = $linetotal;
576 if ($form->round_amount($taxrate, 7) == 0) {
577 if ($form->{taxincluded}) {
578 foreach my $item (@taxaccounts) {
579 $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
580 $taxaccounts{$item} += $taxamount;
581 $taxdiff += $taxamount;
582 $taxbase{$item} += $taxbase;
584 $taxaccounts{ $taxaccounts[0] } += $taxdiff;
586 foreach my $item (@taxaccounts) {
587 $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
588 $taxbase{$item} += $taxbase;
592 foreach my $item (@taxaccounts) {
593 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
594 $taxbase{$item} += $taxbase;
598 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor;
600 $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
602 # Get pricegroup_id and save it. Unfortunately the interface
603 # also uses ID "0" for signalling that none is selected, but "0"
604 # must not be stored in the database. Therefore we cannot simply
606 ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
608 $pricegroup_id = undef if !$pricegroup_id;
610 # force new project, if not set yet
611 if ($::instance_conf->get_order_always_project && !$form->{"globalproject_id"} && ($form->{type} eq 'sales_order')) {
612 require SL::DB::Customer;
613 my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
614 die "Can't find customer" unless $customer;
615 die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
616 if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
618 my $new_project = SL::DB::Project->new(
619 projectnumber => $form->{ordnumber},
620 description => $customer->name,
621 customer_id => $customer->id,
623 project_type_id => $::instance_conf->get_project_type_id,
624 project_status_id => $::instance_conf->get_project_status_id,
627 $form->{"globalproject_id"} = $new_project->id;
630 CVar->get_non_editable_ic_cvars(form => $form,
633 sub_module => 'orderitems',
634 may_converted_from => ['orderitems', 'invoice']);
638 # save detail record in orderitems table
639 if (! $form->{"orderitems_id_$i"}) {
640 $query = qq|SELECT nextval('orderitemsid')|;
641 ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
643 $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
644 do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
647 my $orderitems_id = $form->{"orderitems_id_$i"};
648 push @processed_orderitems, $orderitems_id;
651 UPDATE orderitems SET
652 trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
653 sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
654 pricegroup_id = ?, subtotal = ?,
655 marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
656 active_price_source = ?, active_discount_source = ?,
657 price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
661 conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
662 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
663 $form->{"qty_$i"}, $baseqty,
664 $fxsellprice, $form->{"discount_$i"},
665 $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
666 $form->{"serialnumber_$i"}, $form->{"ship_$i"},
667 $pricegroup_id, $form->{"subtotal_$i"} ? 't' : 'f',
668 $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
669 $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
670 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
671 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
672 conv_i($orderitems_id),
675 do_query($form, $dbh, $query, @values);
677 $form->{"sellprice_$i"} = $fxsellprice;
678 $form->{"discount_$i"} *= 100;
680 CVar->save_custom_variables(module => 'IC',
681 sub_module => 'orderitems',
682 trans_id => $orderitems_id,
683 configs => $ic_cvar_configs,
685 name_prefix => 'ic_',
686 name_postfix => "_$i",
689 # link previous items with orderitems
690 # assume we have a new workflow if we link from invoice or order to quotation
691 # unluckily orderitems are used for quotation and orders - therefore one more
692 # check to be sure NOT to link from order to quotation
693 foreach (qw(orderitems)) {
694 if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}
695 && $form->{type} !~ 'quotation') {
696 RecordLinks->create_links('dbh' => $dbh,
699 'from_ids' => $form->{"converted_from_${_}_id_$i"},
700 'to_table' => 'orderitems',
701 'to_id' => $orderitems_id,
704 delete $form->{"converted_from_${_}_id_$i"};
709 # search for orphaned ids
710 $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
711 @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
712 my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
714 if (scalar @orphaned_ids) {
715 # clean up orderitems
716 $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
717 do_query($form, $dbh, $query, @orphaned_ids);
720 $reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
724 map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
726 $amount = $form->round_amount($netamount + $tax, 2, 1);
727 $netamount = $form->round_amount($netamount, 2);
729 if ($form->{currency} eq $form->{defaultcurrency}) {
730 $form->{exchangerate} = 1;
732 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
735 # from inputfield (exchangerate) or hidden (forex)
736 my $exchangerate_from_form = $form->{forex} || $form->parse_amount($myconfig, $form->{exchangerate});
738 $form->{exchangerate} = $exchangerate || $exchangerate_from_form;
740 my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't';
745 ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
746 customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
747 shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
748 delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
749 taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
750 globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
751 , order_probability = ?, expected_billing_date = ?
754 @values = ($form->{ordnumber} || '', $form->{quonumber},
755 $form->{cusordnumber}, conv_date($form->{transdate}),
756 conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
757 $amount, $netamount, conv_date($reqdate),
758 $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
759 $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
760 $form->{currency}, $form->{closed} ? 't' : 'f',
761 $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
762 $quotation, conv_i($form->{department_id}),
763 conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
764 conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
765 conv_i($form->{delivery_vendor_id}),
766 conv_i($form->{delivery_customer_id}),
767 conv_i($form->{delivery_term_id}),
768 conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
769 conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
770 $form->{transaction_description},
771 $form->{marge_total} * 1, $form->{marge_percent} * 1,
772 $form->{order_probability} * 1, conv_date($form->{expected_billing_date}),
773 conv_i($form->{id}));
774 do_query($form, $dbh, $query, @values);
776 $form->new_lastmtime('oe');
778 $form->{ordtotal} = $amount;
780 $form->{name} = $form->{ $form->{vc} };
781 $form->{name} =~ s/--\Q$form->{"$form->{vc}_id"}\E//;
784 if (!$form->{shipto_id}) {
785 $form->add_shipto($dbh, $form->{id}, "OE");
788 # save printed, emailed, queued
789 $form->save_status($dbh);
791 # Link this record to the records it was created from.
792 $form->{convert_from_oe_ids} =~ s/^\s+//;
793 $form->{convert_from_oe_ids} =~ s/\s+$//;
794 my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
795 delete $form->{convert_from_oe_ids};
796 if (!$form->{useasnew} && scalar @convert_from_oe_ids) {
797 RecordLinks->create_links('dbh' => $dbh,
799 'from_table' => 'oe',
800 'from_ids' => \@convert_from_oe_ids,
802 'to_id' => $form->{id},
804 $self->_close_quotations_rfqs('dbh' => $dbh,
805 'from_id' => \@convert_from_oe_ids,
806 'to_id' => $form->{id});
809 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
810 if ($form->{vc} eq 'customer') {
811 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0);
813 if ($form->{vc} eq 'vendor') {
814 $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
818 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
819 "quonumber" : "ordnumber"};
821 Common::webdav_folder($form);
823 $self->save_periodic_invoices_config(dbh => $dbh,
824 oe_id => $form->{id},
825 config_yaml => $form->{periodic_invoices_config})
826 if ($form->{type} eq 'sales_order');
828 $main::lxdebug->leave_sub();
833 sub save_periodic_invoices_config {
834 my ($self, %params) = @_;
836 return if !$params{oe_id};
838 my $config = $params{config_yaml} ? SL::YAML::Load($params{config_yaml}) : undef;
839 return if 'HASH' ne ref $config;
841 my $obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $params{oe_id})
842 || SL::DB::PeriodicInvoicesConfig->new(oe_id => $params{oe_id});
843 $obj->update_attributes(%{ $config });
846 sub load_periodic_invoice_config {
850 delete $form->{periodic_invoices_config};
853 my $config_obj = SL::DB::Manager::PeriodicInvoicesConfig->find_by(oe_id => $form->{id});
856 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
857 print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
858 $form->{periodic_invoices_config} = SL::YAML::Dump($config);
863 sub _close_quotations_rfqs {
864 $main::lxdebug->enter_sub();
869 Common::check_params(\%params, qw(from_id to_id));
871 my $myconfig = \%main::myconfig;
872 my $form = $main::form;
874 my $dbh = $params{dbh} || SL::DB->client->dbh;
876 SL::DB->client->with_transaction(sub {
878 my $query = qq|SELECT quotation FROM oe WHERE id = ?|;
879 my $sth = prepare_query($form, $dbh, $query);
881 do_statement($form, $sth, $query, conv_i($params{to_id}));
883 my ($quotation) = $sth->fetchrow_array();
891 foreach my $from_id (@{ $params{from_id} }) {
892 $from_id = conv_i($from_id);
893 do_statement($form, $sth, $query, $from_id);
894 ($quotation) = $sth->fetchrow_array();
895 push @close_ids, $from_id if ($quotation);
900 if (scalar @close_ids) {
901 $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|;
902 do_query($form, $dbh, $query, @close_ids);
905 }) or do { die SL::DB->client->error };
907 $main::lxdebug->leave_sub();
911 $main::lxdebug->enter_sub();
913 my ($self, $myconfig, $form) = @_;
915 my $rc = SL::DB::Order->new->db->with_transaction(sub {
916 my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) };
918 SL::DB::Order->new(id => $form->{id})->delete;
920 my $spool = $::lx_office_conf{paths}->{spool};
921 unlink map { "$spool/$_" } @spoolfiles if $spool;
924 }) or do { die SL::DB->client->error };
926 $main::lxdebug->leave_sub();
932 my ($self, $myconfig, $form) = @_;
933 $main::lxdebug->enter_sub();
935 my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form);
937 $::lxdebug->leave_sub;
942 my ($self, $myconfig, $form) = @_;
944 # connect to database
945 my $dbh = SL::DB->client->dbh;
947 my ($query, $query_add, @values, @ids, $sth);
949 # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
951 push @ids, $form->{"trans_id_$_"}
952 if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
953 } (1 .. $form->{"rowcount"});
955 if ($form->{rowcount} && scalar @ids) {
956 $form->{convert_from_oe_ids} = join ' ', @ids;
959 # if called in multi id mode, and still only got one id, switch back to single id
960 if ($form->{"rowcount"} and $#ids == 0) {
961 $form->{"id"} = $ids[0];
963 delete $form->{convert_from_oe_ids};
966 # and remember for the rest of the function
967 my $is_collective_order = scalar @ids;
969 # If collective order was created from exactly 1 order, we assume the same
970 # behaviour as a "save as new" from within an order is actually desired, i.e.
971 # the original order isn't part of a workflow where we want to remember
972 # record_links, but simply a quick way of generating a new order from an old
973 # one without having to enter everything again.
974 # Setting useasnew will prevent the creation of record_links for the items
975 # when saving the new order.
976 # This form variable is probably not necessary, could just set saveasnew instead
977 $form->{useasnew} = 1 if $is_collective_order == 1;
980 my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval :
981 $form->{type} eq 'sales_order' ? $::instance_conf->get_delivery_date_interval : 1;
982 $form->{reqdate} = DateTime->today_local->next_workday(extra_days => $extra_days)->to_kivitendo;
983 $form->{transdate} = DateTime->today_local->to_kivitendo;
986 # get default accounts
987 $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
988 (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
989 (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
990 (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
991 (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
992 (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
993 (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
996 my $ref = selectfirst_hashref_query($form, $dbh, $query);
997 map { $form->{$_} = $ref->{$_} } keys %$ref;
999 $form->{currency} = $form->get_default_currency($myconfig);
1001 # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
1002 # we come from invoices, feel free.
1003 $form->{reqdate} = $form->{deliverydate}
1004 if ( $form->{deliverydate}
1005 and $form->{callback} =~ /action=ar_transactions/);
1007 my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
1009 if ($form->{id} or @ids) {
1011 # retrieve order for single id
1012 # NOTE: this query is intended to fetch all information only ONCE.
1013 # so if any of these infos is important (or even different) for any item,
1014 # it will be killed out and then has to be fetched from the item scope query further down
1016 qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
1017 o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
1018 (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
1019 o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
1020 o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
1022 d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
1023 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
1024 o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
1025 o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
1027 JOIN ${vc} cv ON (o.${vc}_id = cv.id)
1028 LEFT JOIN employee e ON (o.employee_id = e.id)
1029 LEFT JOIN department d ON (o.department_id = d.id) | .
1032 : "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
1034 @values = $form->{id} ? ($form->{id}) : @ids;
1035 $sth = prepare_execute_query($form, $dbh, $query, @values);
1037 $ref = $sth->fetchrow_hashref("NAME_lc");
1040 map { $form->{$_} = $ref->{$_} } keys %$ref;
1042 $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
1044 # set all entries for multiple ids blank that yield different information
1045 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1046 map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
1049 $form->{mtime} ||= $form->{itime};
1050 $form->{lastmtime} = $form->{mtime};
1052 # if not given, fill transdate with current_date
1053 $form->{transdate} = $form->current_date($myconfig)
1054 unless $form->{transdate};
1058 if ($form->{delivery_customer_id}) {
1059 $query = qq|SELECT name FROM customer WHERE id = ?|;
1060 ($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
1063 if ($form->{delivery_vendor_id}) {
1064 $query = qq|SELECT name FROM customer WHERE id = ?|;
1065 ($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
1068 # shipto and pinted/mailed/queued status makes only sense for single id retrieve
1070 $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
1071 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1073 $ref = $sth->fetchrow_hashref("NAME_lc");
1074 $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
1077 if ($ref->{shipto_id}) {
1078 my $cvars = CVar->get_custom_variables(
1081 trans_id => $ref->{shipto_id},
1083 $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
1086 # get printed, emailed and queued
1087 $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
1088 $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
1090 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1091 $form->{printed} .= "$ref->{formname} " if $ref->{printed};
1092 $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
1093 $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
1096 map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
1099 my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
1101 $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
1102 unshift @values, ($form->{taxzone_id}) x 2;
1104 # retrieve individual items
1105 # this query looks up all information about the items
1106 # stuff different from the whole will not be overwritten, but saved with a suffix.
1108 qq|SELECT o.id AS orderitems_id,
1109 c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
1110 c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
1111 c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
1112 oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
1113 p.partnumber, p.part_type, p.listprice, o.description, o.qty,
1114 p.classification_id,
1115 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type,
1116 o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
1117 o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
1118 o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
1119 pr.projectnumber, p.formel,
1120 pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
1122 JOIN parts p ON (o.parts_id = p.id)
1123 JOIN oe ON (o.trans_id = oe.id)
1124 LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id)
1125 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)
1126 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)
1127 LEFT JOIN project pr ON (o.project_id = pr.id)
1128 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1130 ? qq|WHERE o.trans_id = ?|
1131 : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
1132 qq|ORDER BY o.trans_id, o.position|;
1134 @ids = $form->{id} ? ($form->{id}) : @ids;
1135 $sth = prepare_execute_query($form, $dbh, $query, @values);
1137 while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
1138 # Retrieve custom variables.
1139 my $cvars = CVar->get_custom_variables(dbh => $dbh,
1141 sub_module => 'orderitems',
1142 trans_id => $ref->{orderitems_id},
1144 map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1147 if (!$ref->{"part_type"} eq 'part') {
1148 map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
1150 # delete($ref->{"part_inventory_accno_id"});
1152 # in collective order, copy global ordnumber, transdate, cusordnumber into item scope
1153 # unless already present there
1154 # remove _oe entries afterwards
1155 map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') }
1156 qw|ordnumber transdate cusordnumber|
1158 map { delete $ref->{$_} } qw|ordnumber_oe transdate_oe cusordnumber_oe|;
1162 while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
1164 qq|SELECT accno AS inventory_accno, | .
1165 qq| new_chart_id AS inventory_new_chart, | .
1166 qq| date($transdate) - valid_from AS inventory_valid | .
1167 qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
1168 ($ref->{inventory_accno}, $ref->{inventory_new_chart},
1169 $ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
1172 while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
1174 qq|SELECT accno AS income_accno, | .
1175 qq| new_chart_id AS income_new_chart, | .
1176 qq| date($transdate) - valid_from AS income_valid | .
1177 qq|FROM chart WHERE id = $ref->{income_new_chart}|;
1178 ($ref->{income_accno}, $ref->{income_new_chart},
1179 $ref->{income_valid}) = selectrow_query($form, $dbh, $query);
1182 while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
1184 qq|SELECT accno AS expense_accno, | .
1185 qq| new_chart_id AS expense_new_chart, | .
1186 qq| date($transdate) - valid_from AS expense_valid | .
1187 qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
1188 ($ref->{expense_accno}, $ref->{expense_new_chart},
1189 $ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
1192 # delete orderitems_id in collective orders, so that they get cloned no matter what
1193 # is this correct? or is the following meant?
1194 # remember orderitems_ids in converted_from_orderitems_ids, so that they may be linked
1195 $ref->{converted_from_orderitems_id} = delete $ref->{orderitems_id} if $is_collective_order;
1197 # get tax rates and description
1198 my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
1200 qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
1202 qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
1203 qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
1204 qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
1205 qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
1206 qq|ORDER BY c.accno|;
1207 my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
1208 $ref->{taxaccounts} = "";
1210 while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
1211 if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
1215 $ref->{taxaccounts} .= "$ptr->{accno} ";
1216 if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
1217 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
1218 $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
1219 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
1220 $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
1221 $form->{taxaccounts} .= "$ptr->{accno} ";
1226 chop $ref->{taxaccounts};
1228 push @{ $form->{form_details} }, $ref;
1235 # get last name used
1236 $form->lastname_used($dbh, $myconfig, $form->{vc})
1237 unless $form->{"$form->{vc}_id"};
1241 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
1243 Common::webdav_folder($form);
1245 $self->load_periodic_invoice_config($form);
1250 sub retrieve_simple {
1251 $main::lxdebug->enter_sub();
1256 Common::check_params(\%params, qw(id));
1258 my $myconfig = \%main::myconfig;
1259 my $form = $main::form;
1261 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
1263 my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
1264 my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
1266 my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
1267 $order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
1269 $main::lxdebug->leave_sub();
1275 $main::lxdebug->enter_sub();
1277 my ($self, $myconfig, $form) = @_;
1279 # connect to database
1280 my $dbh = SL::DB->client->dbh;
1286 my $nodiscount_subtotal = 0;
1287 my $discount_subtotal = 0;
1290 my @partsgroup = ();
1293 my $subtotal_header = 0;
1294 my $subposition = 0;
1302 push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
1304 $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
1307 foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
1308 $price_factors{$pfac->{id}} = $pfac;
1309 $pfac->{factor} *= 1;
1310 $pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
1313 # sort items by partsgroup
1314 for $i (1 .. $form->{rowcount}) {
1316 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
1317 $partsgroup = $form->{"partsgroup_$i"};
1319 push @partsgroup, [$i, $partsgroup];
1320 push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
1326 $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
1327 %projects_by_id = map { $_->id => $_ } @$projects;
1330 if ($projects_by_id{$form->{"globalproject_id"}}) {
1331 $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
1332 $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
1334 for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
1335 $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
1339 $form->{discount} = [];
1341 # get some values of parts from db on store them in extra array,
1342 # so that they can be sorted in later
1343 my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
1344 my @prepared_arrays = keys %prepared_template_arrays;
1345 my @separate_totals = qw(non_separate_subtotal);
1347 $form->{TEMPLATE_ARRAYS} = { };
1349 my $ic_cvar_configs = CVar->get_configs(module => 'IC');
1350 my $project_cvar_configs = CVar->get_configs(module => 'Projects');
1353 qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
1354 partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
1355 discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
1356 linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
1357 price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
1359 push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
1360 push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
1362 my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
1364 map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @prepared_arrays);
1366 my $totalweight = 0;
1368 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
1371 if ($item->[1] ne $sameitem) {
1372 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup');
1373 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
1374 $sameitem = $item->[1];
1376 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1379 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
1381 if ($form->{"id_$i"} != 0) {
1383 # add number, description and qty to $form->{number}, ....
1385 if ($form->{"subtotal_$i"} && !$subtotal_header) {
1386 $subtotal_header = $i;
1387 $position = int($position);
1390 } elsif ($subtotal_header) {
1392 $position = int($position);
1393 $position = $position.".".$subposition;
1395 $position = int($position);
1399 my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
1401 push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays;
1403 push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal';
1404 push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
1405 push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
1406 push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
1407 push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
1408 push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
1409 push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
1410 push @{ $form->{TEMPLATE_ARRAYS}->{ship} }, $form->format_amount($myconfig, $form->{"ship_$i"});
1411 push @{ $form->{TEMPLATE_ARRAYS}->{ship_nofmt} }, $form->{"ship_$i"};
1412 push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"};
1413 push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
1414 push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
1415 push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
1416 push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
1417 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
1418 push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1419 push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
1420 push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
1421 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
1422 push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
1423 push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
1425 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
1426 my ($dec) = ($sellprice =~ /\.(\d+)/);
1427 my $decimalplaces = max 2, length($dec);
1429 my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
1431 my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
1432 my $linetotal = $form->round_amount($linetotal_exact, 2);
1434 my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
1435 my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
1437 my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
1439 my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
1441 $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
1443 push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
1444 push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
1446 $linetotal = ($linetotal != 0) ? $linetotal : '';
1448 push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
1449 push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
1450 push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
1452 if ( $prepared_template_arrays{separate}[$i - 1] ) {
1453 my $pabbr = $prepared_template_arrays{separate}[$i - 1];
1454 if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
1455 push @separate_totals , "separate_${pabbr}_subtotal";
1456 $form->{"separate_${pabbr}_subtotal"} = 0;
1458 $form->{"separate_${pabbr}_subtotal"} += $linetotal;
1460 $form->{non_separate_subtotal} += $linetotal;
1463 $form->{ordtotal} += $linetotal;
1464 $form->{nodiscount_total} += $nodiscount_linetotal;
1465 $form->{discount_total} += $discount;
1467 if ($subtotal_header) {
1468 $discount_subtotal += $linetotal;
1469 $nodiscount_subtotal += $nodiscount_linetotal;
1472 if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
1473 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
1474 push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
1475 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
1476 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
1478 $discount_subtotal = 0;
1479 $nodiscount_subtotal = 0;
1480 $subtotal_header = 0;
1483 push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
1486 if (!$form->{"discount_$i"}) {
1487 $nodiscount += $linetotal;
1490 my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
1492 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
1493 push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
1494 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
1495 push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
1496 push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
1497 push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
1499 my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
1500 $totalweight += $lineweight;
1501 push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
1502 push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
1503 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
1504 push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
1506 my ($taxamount, $taxbase);
1509 map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
1511 if ($form->{taxincluded}) {
1514 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
1515 $taxbase = $linetotal / (1 + $taxrate);
1517 $taxamount = $linetotal * $taxrate;
1518 $taxbase = $linetotal;
1521 if ($taxamount != 0) {
1522 foreach my $accno (split / /, $form->{"taxaccounts_$i"}) {
1523 $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
1524 $taxbase{$accno} += $taxbase;
1528 $tax_rate = $taxrate * 100;
1529 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
1531 if ($form->{"part_type_$i"} eq 'assembly') {
1534 # get parts and push them onto the stack
1536 if ($form->{groupitems}) {
1537 $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
1539 $sortorder = qq|ORDER BY a.position|;
1542 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
1543 qq|pg.partsgroup | .
1544 qq|FROM assembly a | .
1545 qq| JOIN parts p ON (a.parts_id = p.id) | .
1546 qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
1547 qq| WHERE a.bom = '1' | .
1548 qq| AND a.id = ? | . $sortorder;
1549 @values = ($form->{"id_$i"});
1550 $sth = $dbh->prepare($query);
1551 $sth->execute(@values) || $form->dberror($query);
1553 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1554 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
1555 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1556 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
1557 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup');
1558 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
1561 push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item');
1562 push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
1563 map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays)));
1568 CVar->get_non_editable_ic_cvars(form => $form,
1571 sub_module => 'orderitems',
1572 may_converted_from => ['orderitems', 'invoice']);
1574 push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
1575 CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
1576 for @{ $ic_cvar_configs };
1578 push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
1582 $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
1583 $form->{totalweight_nofmt} = $totalweight;
1584 my $defaults = AM->get_defaults();
1585 $form->{weightunit} = $defaults->{weightunit};
1588 foreach $item (sort keys %taxaccounts) {
1589 $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
1591 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
1592 push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
1593 push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
1594 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount);
1595 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
1596 push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
1597 push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
1598 push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} }, $form->{"${item}_tax_id"});
1600 if ( $form->{"${item}_tax_id"} ) {
1601 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"};
1602 my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
1603 push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
1607 $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
1608 $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
1609 $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
1610 $form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
1612 if($form->{taxincluded}) {
1613 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal} - $tax, 2);
1614 $form->{subtotal_nofmt} = $form->{ordtotal} - $tax;
1616 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1617 $form->{subtotal_nofmt} = $form->{ordtotal};
1620 my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
1621 $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1);
1622 $form->{rounding} = $form->round_amount(
1623 $form->{ordtotal} - $form->round_amount($grossamount, 2),
1628 $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
1629 $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
1631 $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type});
1633 $form->{username} = $myconfig->{name};
1635 $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
1636 $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
1637 $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
1639 $form->{order} = SL::DB::Manager::Order->find_by(id => $form->{id}) if $form->{id};
1640 $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
1642 $main::lxdebug->leave_sub();
1651 OE.pm - Order entry module
1655 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>.
1661 =item retrieve_simple PARAMS
1663 simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
1665 my $order = retrieve_simple(id => 2);