+ my ($self, $myconfig, $form) = @_;
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ $form->{parts} = +{ };
+ $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
+
+ my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand);
+ my @project_filters = qw(projectnumber projectdescription);
+ my @makemodel_filters = qw(make model);
+ my @invoice_oi_filters = qw(serialnumber soldtotal);
+ my @apoe_filters = qw(transdate);
+ my @like_filters = (@simple_filters, @invoice_oi_filters);
+ my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
+ my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop insertdate));
+ my @oe_flags = qw(bought sold onorder ordered rfq quoted);
+ my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty);
+ my @deliverydate_flags = qw(deliverydate);
+# my @other_flags = qw(onhand); # ToDO: implement these
+# my @inactive_flags = qw(l_subtotal short l_linetotal);
+
+ my @select_tokens = qw(id factor part_type classification_id);
+ 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, deliverydate, 'invoice' AS ioi, project_id, id FROM invoice UNION
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, project_id, 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, globalproject_id, '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, globalproject_id, '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, globalproject_id, 'orderitems' AS ioi FROM oe
+ ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
+ cv =>
+ q|LEFT JOIN (
+ SELECT id, name, 'customer' AS cv FROM customer UNION
+ SELECT id, name, 'vendor' AS cv FROM vendor
+ ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
+ mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
+ project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
+ );
+ my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
+
+ my %table_prefix = (
+ deliverydate => 'apoe.', serialnumber => 'ioi.',
+ transdate => 'apoe.', trans_id => 'ioi.',
+ module => 'apoe.', name => 'cv.',
+ ordnumber => 'apoe.', make => 'mm.',
+ quonumber => 'apoe.', model => 'mm.',
+ invnumber => 'apoe.', partsgroup => 'pg.',
+ lastcost => 'p.', , soldtotal => ' ',
+ factor => 'pfac.', projectnumber => 'pj.',
+ 'SUM(ioi.qty)' => ' ', projectdescription => 'pj.',
+ 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, nick name (in case column is named like another)
+ [ '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',
+ 'projectdescription' => 'projectdescription',
+ 'insertdate' => 'insertdate',
+ );
+
+ my %real_column = (
+ projectdescription => 'description',
+ insertdate => 'itime::DATE',
+ );
+
+ if ($form->{l_assembly} && $form->{l_lastcost}) {
+ @simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches;
+ }
+
+ my $make_token_builder = sub {
+ my $joins_needed = shift;
+ sub {
+ my ($nick, $alias) = @_;
+ my ($col) = $real_column{$nick} || $nick;
+ my @coalesce_tokens =
+ map { ($_->[1] || 'p.') . $_->[0] }
+ grep { !$_->[2] || $joins_needed->{$_->[2]} }
+ grep { ($_->[3] || $_->[0]) eq $nick }
+ @column_override, [ $col, $table_prefix{$nick}, undef , $nick ];
+
+ my $coalesce = scalar @coalesce_tokens > 1;
+ return ($coalesce
+ ? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
+ : shift @coalesce_tokens)
+ . ($alias && ($coalesce || $renamed_columns{$nick})
+ ? " AS " . ($renamed_columns{$nick} || $nick)
+ : '');