package IC;
use Data::Dumper;
-use List::MoreUtils qw(all);
+use List::MoreUtils qw(all any uniq);
use YAML;
use SL::CVar;
use SL::DBUtils;
+use strict;
+
sub get_part {
$main::lxdebug->enter_sub();
ve = ?,
gv = ?,
ean = ?,
+ has_sernumber = ?,
not_discountable = ?,
microfiche = ?,
partsgroup_id = ?,
conv_i($form->{ve}),
conv_i($form->{gv}),
$form->{ean},
+ $form->{has_sernumber} ? 't' : 'f',
$form->{not_discountable} ? 't' : 'f',
$form->{microfiche},
conv_i($partsgroup_id),
if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
$query = qq|INSERT INTO makemodel (parts_id, make, model) | .
- qq|VALUES (?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
+ qq|VALUES (?, ?, ?)|;
+ @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
do_query($form, $dbh, $query, @values);
}
$query =
qq|INSERT INTO partstax (parts_id, chart_id)
VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
- @values = (conv_i($form->{id}), $item);
+ @values = (conv_i($form->{id}), $item);
do_query($form, $dbh, $query, @values);
}
}
if ($form->{"qty_$i"} != 0) {
$form->{"bom_$i"} *= 1;
$query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
- qq|VALUES (?, ?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
+ qq|VALUES (?, ?, ?, ?)|;
+ @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
do_query($form, $dbh, $query, @values);
}
}
#
# not working:
# onhand - as above, but masking the simple itemstatus results (doh!)
-# masking of onhand in bsooqr mode - ToDO: fixme
# warehouse onhand
+# search by overrides of description
#
# disabled sanity checks and changes:
# - searchitems = assembly will no longer disable bought
-# - searchitems = service will no longer disable make and model, although services don't have make/model, it doesn't break the query
-# - itemstatus = orphaned will no longer disable onhand short bought sold onorder ordered rfq quoted transdate[from|to]
-# - itemstatus = obsolete will no longer disable onhand, short
+# - searchitems = service will no longer disable make and model, although services don't have make/model, it doesn't break the query
+# - itemstatus = orphaned will no longer disable onhand short bought sold onorder ordered rfq quoted transdate[from|to]
+# - itemstatus = obsolete will no longer disable onhand, short
# - allow sorting by ean
# - serialnumber filter also works if l_serialnumber isn't ticked
-# - onhand doesn't get masked by it's oi or invoice counterparts atm. ToDO: fix this
# - sorting will now change sorting if the requested sorting column isn't checked and doesn't get checked as a side effect
#
sub all_parts {
my @makemodel_filters = qw(make model);
my @invoice_oi_filters = qw(serialnumber soldtotal);
my @apoe_filters = qw(transdate);
+ my @like_filters = (@simple_filters, @makemodel_filters, @invoice_oi_filters);
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
my @oe_flags = qw(bought sold onorder ordered rfq quoted);
# my @other_flags = qw(onhand); # ToDO: implement these
# my @inactive_flags = qw(l_subtotal short l_linetotal);
+ my @select_tokens = qw(id factor);
+ my @where_tokens = qw(1=1);
+ my @group_tokens = ();
+ my @bind_vars = ();
+ my %joins_needed = ();
+
my %joins = (
partsgroup => 'LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)',
makemodel => 'LEFT JOIN makemodel mm ON (mm.parts_id = p.id)',
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)',
invoice_oi =>
q|LEFT JOIN (
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, id FROM invoice UNION
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems
) AS ioi ON ioi.parts_id = p.id|,
apoe =>
q|LEFT JOIN (
- SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, 'invoice' AS ioi FROM ap UNION
- SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, 'invoice' AS ioi FROM ar UNION
- SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate, 'orderitems' AS ioi FROM oe
+ SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, 'invoice' AS ioi FROM ap UNION
+ SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, 'invoice' AS ioi FROM ar UNION
+ SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe
) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
cv =>
q|LEFT JOIN (
lastcost => ' ',
factor => 'pfac.',
'SUM(ioi.qty)' => ' ',
+ description => 'p.',
+ qty => 'ioi.',
+ serialnumber => 'ioi.',
+ quotation => 'apoe.',
+ cv => 'cv.',
+ "ioi.id" => ' ',
+ "ioi.ioi" => ' ',
+ );
+
+ # if the join condition in these blocks are met, the column
+ # of the scecified table will gently override (coalesce actually) the original value
+ # use it to conditionally coalesce values from subtables
+ my @column_override = (
+ # column name, prefix, joins_needed
+ [ 'description', 'ioi.', 'invoice_oi' ],
+ [ 'deliverydate', 'ioi.', 'invoice_oi' ],
+ [ 'transdate', 'apoe.', 'apoe' ],
+ [ 'unit', 'ioi.', 'invoice_oi' ],
+ [ 'sellprice', 'ioi.', 'invoice_oi' ],
);
+ # careful with renames. these are HARD, and any filters done on the original column will break
my %renamed_columns = (
'factor' => 'price_factor',
'SUM(ioi.qty)' => 'soldtotal',
+ 'ioi.id' => 'ioi_id',
+ 'ioi.ioi' => 'ioi',
);
- my %joins_needed;
-
if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
@simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches;
}
- #===== switches and simple filters ========#
+ my $make_token_builder = sub {
+ my $joins_needed = shift;
+ sub {
+ my ($col, $alias) = @_;
+ my @coalesce_tokens =
+ map { ($_->[1] || 'p.') . $_->[0] }
+ grep { !$_->[2] || $joins_needed->{$_->[2]} }
+ grep { $_->[0] eq $col }
+ @column_override, [ $col, $table_prefix{$col} ];
+
+ my $coalesce = scalar @coalesce_tokens > 1;
+ return ($coalesce
+ ? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
+ : shift @coalesce_tokens)
+ . ($alias && ($coalesce || $renamed_columns{$col})
+ ? " AS " . ($renamed_columns{$col} || $col)
+ : '');
+ }
+ };
- my @select_tokens = qw(id factor);
- my @where_tokens = qw(1=1);
- my @group_tokens = ();
- my @bind_vars = ();
+ #===== switches and simple filters ========#
# special case transdate
if (grep { $form->{$_} } qw(transdatefrom transdateto)) {
}
}
- my %simple_filter_table_prefix = (
- description => 'p.',
- );
-
- foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) {
+ foreach (@like_filters) {
next unless $form->{$_};
$form->{"l_$_"} = '1'; # show the column
- push @where_tokens, "$simple_filter_table_prefix{$_}$_ ILIKE ?";
+ push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";
push @bind_vars, "%$form->{$_}%";
}
LEFT JOIN parts p_lc ON (a_lc.parts_id = p_lc.id)
LEFT JOIN price_factors pfac_lc ON (p_lc.price_factor_id = pfac_lc.id)
WHERE (a_lc.id = p.id)) AS lastcost|;
-
- my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
- $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols;
-
- my $sort_order = ($form->{revers} ? ' DESC' : ' ASC');
-
- my $order_clause = " ORDER BY $form->{sort} " . ($form->{revers} ? 'DESC' : 'ASC');
+ $table_prefix{$q_assembly_lastcost} = ' ';
# special case: sorting by partnumber
# since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
#my $order_clause = " ORDER BY $form->{sort} $sort_order";
- my $limit_clause = " LIMIT 100" if $form->{top100};
+ my $limit_clause;
+ $limit_clause = " LIMIT 100" if $form->{top100};
+ $limit_clause = " LIMIT " . $form->{limit} * 1 if $form->{limit} * 1;
#=== joins and complicated filters ========#
- my $bsooqr = $form->{bought} || $form->{sold}
- || $form->{ordered} || $form->{onorder}
- || $form->{quoted} || $form->{rfq};
-
- my @bsooqr;
+ my $bsooqr = any { $form->{$_} } @oe_flags;
my @bsooqr_tokens = ();
- push @select_tokens, @qsooqr_flags if $bsooqr;
+
+ push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi' if $bsooqr;
push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate};
push @select_tokens, $q_assembly_lastcost if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost};
push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem| if $form->{bought};
push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'vendor'| if $form->{rfq};
push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr;
- $renamed_columns{onhand} = 'onhand_before_bsooqr';
- $renamed_columns{qty} = 'onhand';
-
$joins_needed{partsgroup} = 1;
$joins_needed{pfac} = 1;
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
$joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
$joins_needed{invoice_oi} = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
+ # in bsoorq, use qtys instead of onhand
+ if ($joins_needed{invoice_oi}) {
+ $renamed_columns{onhand} = 'onhand_before_bsooqr';
+ $renamed_columns{qty} = 'onhand';
+ }
+
# special case for description search.
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
# now we'd like to search also for the masked description entered in orderitems and invoice, so...
# find the old entries in of @where_tokens and @bind_vars, and adjust them
if ($joins_needed{invoice_oi}) {
for (my ($wi, $bi) = (0)x2; $wi <= $#where_tokens; $bi++ if $where_tokens[$wi++] =~ /\?/) {
- next unless $where_tokens[$wi] =~ /^description ILIKE/;
+ next unless $where_tokens[$wi] =~ /\bdescription ILIKE/;
splice @where_tokens, $wi, 1, 'p.description ILIKE ? OR ioi.description ILIKE ?';
splice @bind_vars, $bi, 0, $bind_vars[$bi];
last;
# now the master trick: soldtotal.
if ($form->{l_soldtotal}) {
- push @where_tokens, 'ioi.qty >= 0';
+ push @where_tokens, 'NOT ioi.qty = 0';
push @group_tokens, @select_tokens;
map { s/.*\sAS\s+//si } @group_tokens;
push @select_tokens, 'SUM(ioi.qty)';
#============= build query ================#
- $table_prefix{$q_assembly_lastcost} = ' ';
+ my $token_builder = $make_token_builder->(\%joins_needed);
- map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};
- map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns;
+ my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
+ $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; # sort by id if unknown or invisible column
+ my $sort_order = ($form->{revers} ? ' DESC' : ' ASC');
+ my $order_clause = " ORDER BY " . $token_builder->($form->{sort}) . ($form->{revers} ? ' DESC' : ' ASC');
- my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens;
+ my $select_clause = join ', ', map { $token_builder->($_, 1) } @select_tokens;
my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order };
my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
- my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
+ my $group_clause = ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens if scalar @group_tokens;
+
+ my %oe_flag_to_cvar = (
+ bought => 'invoice',
+ sold => 'invoice',
+ onorder => 'orderitems',
+ ordered => 'orderitems',
+ rfq => 'orderitems',
+ quoted => 'orderitems',
+ );
- my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'IC',
- 'trans_id_field' => 'p.id',
- 'filter' => $form);
+ my ($cvar_where, @cvar_values) = CVar->build_filter_query(
+ module => 'IC',
+ trans_id_field => $bsooqr ? 'ioi.id': 'p.id',
+ filter => $form,
+ sub_module => $bsooqr ? [ uniq grep { $oe_flag_to_cvar{$form->{$_}} } @oe_flags ] : undef,
+ );
if ($cvar_where) {
$where_clause .= qq| AND ($cvar_where)|;
push @bind_vars, @cvar_values;
}
- my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|;
+ my $query = <<" SQL";
+ SELECT DISTINCT $select_clause
+ FROM parts p
+ $join_clause
+ WHERE $where_clause
+ $group_clause
+ $order_clause
+ $limit_clause
+ SQL
$form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
}
$main::lxdebug->leave_sub();
+
+ return wantarray ? @{ $form->{parts} } : $form->{parts};
}
sub _create_filter_for_priceupdate {
qq| cnew.accno | .
qq|FROM chart c | .
qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
- qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
+ qq|WHERE (c.id = ?) AND NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)|;
$sth = prepare_query($form, $dbh, $query);
while (1) {
my $dbh = $form->get_standard_dbh($myconfig);
- my $query = qq|SELECT id, partnumber, description, unit FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
+ my $query = qq|SELECT * FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
my $info = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);