qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
qq|FROM oe o | .
qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
+ qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
push(@values, '%' . $form->{$vc} . '%');
}
+ if ($form->{"cp_name"}) {
+ $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
+ push(@values, ('%' . $form->{"cp_name"} . '%')x2);
+ }
+
if (!$main::auth->assert('sales_all_edit', 1)) {
$query .= " AND o.employee_id = (select id from employee where login= ?)";
push @values, $form->{login};
$form->{$number_field} ||= $trans_number->create_unique;
if ($form->{id}) {
- $query = qq|DELETE FROM custom_variables
- WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
- AND (sub_module = 'orderitems')
- AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
- do_query($form, $dbh, $query, $form->{id});
-
- $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
$query = qq|DELETE FROM shipto | .
qq|WHERE trans_id = ? AND module = 'OE'|;
do_query($form, $dbh, $query, $form->{id});
my @taxaccounts;
my %taxaccounts;
my $netamount = 0;
+ my @processed_orderitems;
$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
$pricegroup_id *= 1;
$pricegroup_id = undef if !$pricegroup_id;
+ my $position = $i;
+
# save detail record in orderitems table
+ my $cvars;
+ if (! $form->{"orderitems_id_$i"}) {
+ $query = qq|SELECT nextval('orderitemsid')|;
+ ($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
+
+ $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
+ do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
+
+ # get values for CVars from master data for new items
+ $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ trans_id => $form->{"id_$i"},
+ );
+ } else {
+ # get values for CVars from custom_variables for existing items
+ $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'orderitems',
+ trans_id => $form->{"orderitems_id_$i"},
+ );
+ }
+ # map only non-editable CVars to form (editable ones are already there)
+ map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
+
my $orderitems_id = $form->{"orderitems_id_$i"};
- ($orderitems_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('orderitemsid')|) if (!$orderitems_id);
-
- @values = ();
- $query = qq|INSERT INTO orderitems (
- id, trans_id, parts_id, description, longdescription, qty, base_qty,
- sellprice, discount, unit, reqdate, project_id, serialnumber, ship,
- pricegroup_id, ordnumber, transdate, cusordnumber, subtotal,
- marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
- (SELECT factor FROM price_factors WHERE id = ?), ?)|;
- push(@values,
- conv_i($orderitems_id), conv_i($form->{id}), conv_i($form->{"id_$i"}),
+ push @processed_orderitems, $orderitems_id;
+
+ $query = <<SQL;
+ UPDATE orderitems SET
+ trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
+ sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
+ pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
+ marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
+ active_price_source = ?, active_discount_source = ?,
+ price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
+ WHERE id = ?
+SQL
+ @values = (
+ conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
$form->{"qty_$i"}, $baseqty,
$fxsellprice, $form->{"discount_$i"},
$form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
$form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
$form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
- $form->{"lastcost_$i"},
- conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
- conv_i($form->{"marge_price_factor_$i"}));
+ $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}),
+ $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
+ conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
+ conv_i($orderitems_id),
+ );
+
do_query($form, $dbh, $query, @values);
$form->{"sellprice_$i"} = $fxsellprice;
name_prefix => 'ic_',
name_postfix => "_$i",
dbh => $dbh);
+ # link previous items with orderitems
+ foreach (qw(quotation_orderitems orderitems invoice)) {
+ if ($form->{"converted_from_${_}_id_$i"}) {
+ my $table = $_;
+ $table = 'orderitems' if $table eq 'quotation_orderitems';
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => $table,
+ 'from_ids' => $form->{"converted_from_${_}_id_$i"},
+ 'to_table' => 'orderitems',
+ 'to_id' => $orderitems_id,
+ );
+ delete $form->{"converted_from_${_}_id_$i"};
+ }
+ }
}
}
+ # search for orphaned ids
+ $query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
+ @values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
+ my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
+
+ if (scalar @orphaned_ids) {
+ # clean up orderitems
+ $query = sprintf 'DELETE FROM orderitems WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
+ do_query($form, $dbh, $query, @orphaned_ids);
+ }
$reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
if (!$form->{id}) {
my $wday = (localtime(time))[6];
my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
+
+ # if we have a client configured interval for sales quotation, we add this
+ $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
+ $form->{type} eq 'sales_quotation' );
+
$query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
}
o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
- o.price_factor_id, o.price_factor, o.marge_price_factor,
+ o.price_factor_id, o.price_factor, o.marge_price_factor, o.active_price_source, o.active_discount_source,
pr.projectnumber, p.formel,
pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup
FROM orderitems o
($form->{id}
? qq|WHERE o.trans_id = ?|
: qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
- qq|ORDER BY o.oid|;
+ qq|ORDER BY o.trans_id, o.position|;
@ids = $form->{id} ? ($form->{id}) : @ids;
$sth = prepare_execute_query($form, $dbh, $query, @values);
}
# delete orderitems_id in collective orders, so that they get cloned no matter what
- delete $ref->{orderitems_id} if (@ids);
+ delete $ref->{orderitems_id} if $is_collective_order;
# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
- my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
+ my $oi_query = qq|SELECT * FROM orderitems WHERE trans_id = ? ORDER BY position|;
my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
$order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
- $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
+ $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
$sth->finish;
}
+ my $cvars;
+ if (! $form->{"orderitems_id_$i"}) {
+ # get values for CVars from master data for new items
+ $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ trans_id => $form->{"id_$i"},
+ );
+ } else {
+ # get values for CVars from custom_variables for existing items
+ $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'orderitems',
+ trans_id => $form->{"orderitems_id_$i"},
+ );
+ }
+ # map only non-editable CVars to form (editable ones are already there)
+ map { $form->{"ic_cvar_$_->{name}_$i"} = $_->{value} unless $_->{flag_editable} } @{ $cvars };
+
push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
for @{ $ic_cvar_configs };