Revision 5b0450c3
Von Sven Schöling vor fast 13 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 792 | 792 |
$form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
|
| 793 | 793 |
|
| 794 | 794 |
my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand); |
| 795 |
my @project_filters = qw(projectnumber projectdescription); |
|
| 795 | 796 |
my @makemodel_filters = qw(make model); |
| 796 | 797 |
my @invoice_oi_filters = qw(serialnumber soldtotal); |
| 797 | 798 |
my @apoe_filters = qw(transdate); |
| 798 | 799 |
my @like_filters = (@simple_filters, @invoice_oi_filters); |
| 799 |
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber)); |
|
| 800 |
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
|
|
| 800 | 801 |
my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image)); |
| 801 | 802 |
my @oe_flags = qw(bought sold onorder ordered rfq quoted); |
| 802 | 803 |
my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty); |
| ... | ... | |
| 816 | 817 |
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)', |
| 817 | 818 |
invoice_oi => |
| 818 | 819 |
q|LEFT JOIN ( |
| 819 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, id FROM invoice UNION |
|
| 820 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems |
|
| 820 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, project_id, id FROM invoice UNION
|
|
| 821 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, project_id, id FROM orderitems
|
|
| 821 | 822 |
) AS ioi ON ioi.parts_id = p.id|, |
| 822 | 823 |
apoe => |
| 823 | 824 |
q|LEFT JOIN ( |
| 824 |
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 |
|
| 825 |
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 |
|
| 826 |
SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe |
|
| 825 |
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
|
|
| 826 |
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
|
|
| 827 |
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
|
|
| 827 | 828 |
) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|, |
| 828 | 829 |
cv => |
| 829 | 830 |
q|LEFT JOIN ( |
| ... | ... | |
| 831 | 832 |
SELECT id, name, 'vendor' AS cv FROM vendor |
| 832 | 833 |
) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|, |
| 833 | 834 |
mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make', |
| 835 |
project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)', |
|
| 834 | 836 |
); |
| 835 |
my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac); |
|
| 837 |
my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
|
|
| 836 | 838 |
|
| 837 | 839 |
my %table_prefix = ( |
| 838 | 840 |
deliverydate => 'apoe.', serialnumber => 'ioi.', |
| ... | ... | |
| 842 | 844 |
quonumber => 'apoe.', model => 'mm.', |
| 843 | 845 |
invnumber => 'apoe.', partsgroup => 'pg.', |
| 844 | 846 |
lastcost => 'p.', , soldtotal => ' ', |
| 845 |
factor => 'pfac.', |
|
| 846 |
'SUM(ioi.qty)' => ' ', |
|
| 847 |
factor => 'pfac.', projectnumber => 'pj.',
|
|
| 848 |
'SUM(ioi.qty)' => ' ', projectdescription => 'pj.',
|
|
| 847 | 849 |
description => 'p.', |
| 848 | 850 |
qty => 'ioi.', |
| 849 | 851 |
serialnumber => 'ioi.', |
| ... | ... | |
| 857 | 859 |
# of the scecified table will gently override (coalesce actually) the original value |
| 858 | 860 |
# use it to conditionally coalesce values from subtables |
| 859 | 861 |
my @column_override = ( |
| 860 |
# column name, prefix, joins_needed |
|
| 862 |
# column name, prefix, joins_needed, nick name (in case column is named like another)
|
|
| 861 | 863 |
[ 'description', 'ioi.', 'invoice_oi' ], |
| 862 | 864 |
[ 'deliverydate', 'ioi.', 'invoice_oi' ], |
| 863 | 865 |
[ 'transdate', 'apoe.', 'apoe' ], |
| ... | ... | |
| 871 | 873 |
'SUM(ioi.qty)' => 'soldtotal', |
| 872 | 874 |
'ioi.id' => 'ioi_id', |
| 873 | 875 |
'ioi.ioi' => 'ioi', |
| 876 |
'projectdescription' => 'projectdescription', |
|
| 877 |
); |
|
| 878 |
|
|
| 879 |
my %real_column = ( |
|
| 880 |
projectdescription => 'description', |
|
| 874 | 881 |
); |
| 875 | 882 |
|
| 876 | 883 |
if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
|
| ... | ... | |
| 880 | 887 |
my $make_token_builder = sub {
|
| 881 | 888 |
my $joins_needed = shift; |
| 882 | 889 |
sub {
|
| 883 |
my ($col, $alias) = @_; |
|
| 890 |
my ($nick, $alias) = @_; |
|
| 891 |
my ($col) = $real_column{$nick} || $nick;
|
|
| 884 | 892 |
my @coalesce_tokens = |
| 885 | 893 |
map { ($_->[1] || 'p.') . $_->[0] }
|
| 886 | 894 |
grep { !$_->[2] || $joins_needed->{$_->[2]} }
|
| 887 |
grep { $_->[0] eq $col }
|
|
| 888 |
@column_override, [ $col, $table_prefix{$col} ];
|
|
| 895 |
grep { ($_->[3] || $_->[0]) eq $nick }
|
|
| 896 |
@column_override, [ $col, $table_prefix{$nick}, undef , $nick ];
|
|
| 889 | 897 |
|
| 890 | 898 |
my $coalesce = scalar @coalesce_tokens > 1; |
| 891 | 899 |
return ($coalesce |
| 892 | 900 |
? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens |
| 893 | 901 |
: shift @coalesce_tokens) |
| 894 |
. ($alias && ($coalesce || $renamed_columns{$col})
|
|
| 895 |
? " AS " . ($renamed_columns{$col} || $col)
|
|
| 902 |
. ($alias && ($coalesce || $renamed_columns{$nick})
|
|
| 903 |
? " AS " . ($renamed_columns{$nick} || $nick)
|
|
| 896 | 904 |
: ''); |
| 897 | 905 |
} |
| 898 | 906 |
}; |
| ... | ... | |
| 1001 | 1009 |
|
| 1002 | 1010 |
$joins_needed{partsgroup} = 1;
|
| 1003 | 1011 |
$joins_needed{pfac} = 1;
|
| 1012 |
$joins_needed{project} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
|
|
| 1004 | 1013 |
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
|
| 1005 | 1014 |
$joins_needed{mv} = 1 if $joins_needed{makemodel};
|
| 1006 | 1015 |
$joins_needed{cv} = 1 if $bsooqr;
|
| 1007 | 1016 |
$joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
|
| 1008 |
$joins_needed{invoice_oi} = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
|
|
| 1017 |
$joins_needed{invoice_oi} = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
|
|
| 1009 | 1018 |
|
| 1010 | 1019 |
# special case for description search. |
| 1011 | 1020 |
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
|
Auch abrufbar als: Unified diff
Projekte im Warenbericht