+ foreach (@like_filters) {
+ next unless $form->{$_};
+ $form->{"l_$_"} = '1'; # show the column
+ push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";
+ push @bind_vars, "%$form->{$_}%";
+ }
+
+ foreach (@simple_l_switches) {
+ next unless $form->{"l_$_"};
+ push @select_tokens, $_;
+ }
+
+ for ($form->{searchitems}) {
+ push @where_tokens, 'p.inventory_accno_id > 0' if /part/;
+ push @where_tokens, 'p.inventory_accno_id IS NULL' if /service/;
+ push @where_tokens, 'NOT p.assembly' if /service/;
+ push @where_tokens, ' p.assembly' if /assembly/;
+ }
+
+ for ($form->{itemstatus}) {
+ push @where_tokens, 'p.id NOT IN
+ (SELECT DISTINCT parts_id FROM invoice UNION
+ SELECT DISTINCT parts_id FROM assembly UNION
+ SELECT DISTINCT parts_id FROM orderitems)' if /orphaned/;
+ push @where_tokens, 'p.onhand = 0' if /orphaned/;
+ push @where_tokens, 'NOT p.obsolete' if /active/;
+ push @where_tokens, ' p.obsolete', if /obsolete/;
+ push @where_tokens, 'p.onhand > 0', if /onhand/;
+ push @where_tokens, 'p.onhand < p.rop', if /short/;
+ }
+
+ my $q_assembly_lastcost =
+ qq|(SELECT SUM(a_lc.qty * p_lc.lastcost / COALESCE(pfac_lc.factor, 1))
+ FROM assembly a_lc
+ 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|;
+ $table_prefix{$q_assembly_lastcost} = ' ';
+
+ # special case makemodel search
+ # all_parts is based upon the assumption that every parameter is named like the column it represents
+ # unfortunately make would have to match vendor.name which is already taken for vendor.name in bsooqr mode.
+ # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
+ if ($form->{make}) {
+ push @where_tokens, 'mv.name ILIKE ?';
+ push @bind_vars, "%$form->{make}%";
+ }
+ if ($form->{model}) {
+ push @where_tokens, 'mm.model ILIKE ?';
+ push @bind_vars, "%$form->{model}%";
+ }
+
+ # 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.
+ # and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.
+ # ToDO: implement proper functional sorting
+ # Nette Idee von Sven, gibt aber Probleme wenn die Artikelnummern groesser als 32bit sind. Korrekt waere es, dass Sort-Natural-Modul zu nehmen
+ # Ich lass das mal hier drin, damit die Idee erhalten bleibt jb 28.5.2009 bug 1018
+ #$form->{sort} = join ', ', map { push @select_tokens, $_; ($table_prefix{$_} = "substring(partnumber,'[") . $_ } qw|^[:digit:]]+') [:digit:]]+')::INTEGER|
+ # if $form->{sort} eq 'partnumber';
+
+ #my $order_clause = " ORDER BY $form->{sort} $sort_order";
+
+ 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 = any { $form->{$_} } @oe_flags;
+ my @bsooqr_tokens = ();
+
+ 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 = 'is' AND NOT ioi.assemblyitem| if $form->{sold};
+ push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};
+ push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'vendor'| if $form->{onorder};
+ push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'customer'| if $form->{quoted};
+ push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'vendor'| if $form->{rfq};
+ push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr;
+
+ $joins_needed{partsgroup} = 1;
+ $joins_needed{pfac} = 1;
+ $joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
+ $joins_needed{mv} = 1 if $joins_needed{makemodel};
+ $joins_needed{cv} = 1 if $bsooqr;
+ $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] =~ /\bdescription ILIKE/;
+ splice @where_tokens, $wi, 1, 'p.description ILIKE ? OR ioi.description ILIKE ?';
+ splice @bind_vars, $bi, 0, $bind_vars[$bi];
+ last;
+ }