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;
+ CVar->get_non_editable_ic_cvars(form => $form,
+ dbh => $dbh,
+ row => $i,
+ sub_module => 'orderitems',
+ may_converted_from => ['orderitems', 'invoice']);
+
+ my $position = $i;
+
# save detail record in orderitems table
+ 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));
+ }
+
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(orderitems invoice)) {
+ if (!$form->{saveasnew} && $form->{"converted_from_${_}_id_$i"}) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_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;
# add up the tax
$form->save_status($dbh);
# Link this record to the records it was created from.
+ # check every record type we may link. i am not happy with converting the string to array back
+ # should be a array from the start (OE.pm -> retrieve).
+ # and that i need the local array ref for close_quotation_rfqs. better ideas welcome
$form->{convert_from_oe_ids} =~ s/^\s+//;
$form->{convert_from_oe_ids} =~ s/\s+$//;
my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
delete $form->{convert_from_oe_ids};
-
- if (scalar @convert_from_oe_ids) {
- RecordLinks->create_links('dbh' => $dbh,
- 'mode' => 'ids',
- 'from_table' => 'oe',
- 'from_ids' => \@convert_from_oe_ids,
- 'to_table' => 'oe',
- 'to_id' => $form->{id},
- );
-
+ @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
+ foreach (qw(ar oe)) {
+ if ($form->{"convert_from_${_}_ids"}) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => $_,
+ 'from_ids' => $form->{"convert_from_${_}_ids"},
+ 'to_table' => 'oe',
+ 'to_id' => $form->{id},
+ );
+ delete $form->{"convert_from_${_}_ids"};
+ }
$self->_close_quotations_rfqs('dbh' => $dbh,
'from_id' => \@convert_from_oe_ids,
- 'to_id' => $form->{id});
+ 'to_id' => $form->{id}) if $_ eq 'oe';
}
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
my $is_collective_order = scalar @ids;
if (!$form->{id}) {
- my $wday = (localtime(time))[6];
- my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
- $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
+ my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
+ my $next_workday = DateTime->today_local->add(days => $extra_days);
+ my $day_of_week = $next_workday->day_of_week;
+
+ $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
+
+ $form->{transdate} = DateTime->today_local->to_kivitendo;
+ $form->{reqdate} = $next_workday->to_kivitendo;
}
# get default accounts
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;
}
+ CVar->get_non_editable_ic_cvars(form => $form,
+ dbh => $dbh,
+ row => $i,
+ sub_module => 'orderitems',
+ may_converted_from => ['orderitems', 'invoice']);
+
push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
for @{ $ic_cvar_configs };
projects / kivitendo-erp.git / blobdiff