+ 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|;
+
+ 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');
+
+ # 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 100" if $form->{top100};
+
+ #=== joins and complicated filters ========#
+
+ my $bsooqr = $form->{bought} || $form->{sold}
+ || $form->{ordered} || $form->{onorder}
+ || $form->{quoted} || $form->{rfq};
+
+ my @bsooqr;
+ push @select_tokens, @qsooqr_flags 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{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;
+
+ # 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/;
+ splice @where_tokens, $wi, 1, 'p.description ILIKE ? OR ioi.description ILIKE ?';
+ splice @bind_vars, $bi, 0, $bind_vars[$bi];
+ last;
+ }