+ # 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',
+ );
+
+ if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
+ @simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches;
+ }
+
+ 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)
+ : '');
+ }
+ };
+
+ #===== switches and simple filters ========#