Revision 5f783ffe
Von Sven Schöling vor mehr als 15 Jahren hinzugefügt
SL/IC.pm | ||
---|---|---|
35 | 35 |
package IC; |
36 | 36 |
|
37 | 37 |
use Data::Dumper; |
38 |
use List::MoreUtils qw(all); |
|
38 |
use List::MoreUtils qw(all any);
|
|
39 | 39 |
use YAML; |
40 | 40 |
|
41 | 41 |
use SL::CVar; |
... | ... | |
744 | 744 |
# |
745 | 745 |
# not working: |
746 | 746 |
# onhand - as above, but masking the simple itemstatus results (doh!) |
747 |
# masking of onhand in bsooqr mode - ToDO: fixme |
|
748 | 747 |
# warehouse onhand |
748 |
# search by overrides of description |
|
749 | 749 |
# |
750 | 750 |
# disabled sanity checks and changes: |
751 | 751 |
# - searchitems = assembly will no longer disable bought |
752 |
# - searchitems = service will no longer disable make and model, although services don't have make/model, it doesn't break the query |
|
753 |
# - itemstatus = orphaned will no longer disable onhand short bought sold onorder ordered rfq quoted transdate[from|to] |
|
754 |
# - itemstatus = obsolete will no longer disable onhand, short |
|
752 |
# - searchitems = service will no longer disable make and model, although services don't have make/model, it doesn't break the query
|
|
753 |
# - itemstatus = orphaned will no longer disable onhand short bought sold onorder ordered rfq quoted transdate[from|to]
|
|
754 |
# - itemstatus = obsolete will no longer disable onhand, short
|
|
755 | 755 |
# - allow sorting by ean |
756 | 756 |
# - serialnumber filter also works if l_serialnumber isn't ticked |
757 |
# - onhand doesn't get masked by it's oi or invoice counterparts atm. ToDO: fix this |
|
758 | 757 |
# - sorting will now change sorting if the requested sorting column isn't checked and doesn't get checked as a side effect |
759 | 758 |
# |
760 | 759 |
sub all_parts { |
... | ... | |
770 | 769 |
my @makemodel_filters = qw(make model); |
771 | 770 |
my @invoice_oi_filters = qw(serialnumber soldtotal); |
772 | 771 |
my @apoe_filters = qw(transdate); |
772 |
my @like_filters = (@simple_filters, @makemodel_filters, @invoice_oi_filters); |
|
773 | 773 |
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber)); |
774 | 774 |
my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image)); |
775 | 775 |
my @oe_flags = qw(bought sold onorder ordered rfq quoted); |
... | ... | |
778 | 778 |
# my @other_flags = qw(onhand); # ToDO: implement these |
779 | 779 |
# my @inactive_flags = qw(l_subtotal short l_linetotal); |
780 | 780 |
|
781 |
my @select_tokens = qw(id factor); |
|
782 |
my @where_tokens = qw(1=1); |
|
783 |
my @group_tokens = (); |
|
784 |
my @bind_vars = (); |
|
785 |
my %joins_needed = (); |
|
786 |
|
|
781 | 787 |
my %joins = ( |
782 | 788 |
partsgroup => 'LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)', |
783 | 789 |
makemodel => 'LEFT JOIN makemodel mm ON (mm.parts_id = p.id)', |
784 | 790 |
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)', |
785 | 791 |
invoice_oi => |
786 | 792 |
q|LEFT JOIN ( |
787 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION |
|
788 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems |
|
793 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi FROM invoice UNION
|
|
794 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi FROM orderitems
|
|
789 | 795 |
) AS ioi ON ioi.parts_id = p.id|, |
790 | 796 |
apoe => |
791 | 797 |
q|LEFT JOIN ( |
... | ... | |
811 | 817 |
lastcost => ' ', |
812 | 818 |
factor => 'pfac.', |
813 | 819 |
'SUM(ioi.qty)' => ' ', |
814 |
description => 'p.', |
|
820 |
description => 'p.', |
|
821 |
qty => 'ioi.', |
|
822 |
unit => 'ioi.', |
|
823 |
serialnumber => 'ioi.', |
|
815 | 824 |
); |
816 | 825 |
|
826 |
# if the join condition in these blocks are met, the column |
|
827 |
# of the scecified table will gently override (coalesce actually) the original value |
|
828 |
# use it to conditionally coalesce values from subtables |
|
829 |
my @column_override = ( |
|
830 |
# column name, prefix, joins_needed |
|
831 |
[ 'description', 'ioi.', 'invoice_oi' ], |
|
832 |
[ 'deliverydate', 'ioi.', 'invoice_oi' ], |
|
833 |
[ 'transdate' , 'apoe.', 'apoe' ], |
|
834 |
); |
|
835 |
|
|
836 |
# careful with renames. these are HARD, and any filters done on the original column will break |
|
817 | 837 |
my %renamed_columns = ( |
818 | 838 |
'factor' => 'price_factor', |
819 | 839 |
'SUM(ioi.qty)' => 'soldtotal', |
... | ... | |
823 | 843 |
@simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches; |
824 | 844 |
} |
825 | 845 |
|
826 |
#===== switches and simple filters ========# |
|
846 |
my $make_token_builder = sub { |
|
847 |
my $joins_needed = shift; |
|
848 |
sub { |
|
849 |
my ($col, $group) = @_; |
|
850 |
$renamed_columns{$col} ||= $col; |
|
851 |
|
|
852 |
my @coalesce_tokens = |
|
853 |
map { ($_->[1] || 'p.') . $_->[0] } |
|
854 |
grep { !$_->[2] || $joins_needed->{$_->[2]} } |
|
855 |
grep { $_->[0] eq $col } |
|
856 |
@column_override, |
|
857 |
[ $col, $table_prefix{$col} ]; |
|
858 |
|
|
859 |
my $coalesce = scalar @coalesce_tokens > 1; |
|
860 |
return ($coalesce |
|
861 |
? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens |
|
862 |
: shift @coalesce_tokens) |
|
863 |
. ($group && $coalesce |
|
864 |
? " AS $renamed_columns{$col}" |
|
865 |
: ''); |
|
866 |
} |
|
867 |
}; |
|
827 | 868 |
|
828 |
my @select_tokens = qw(id factor); |
|
829 |
my @where_tokens = qw(1=1); |
|
830 |
my @group_tokens = (); |
|
831 |
my @bind_vars = (); |
|
832 |
my %joins_needed = (); |
|
869 |
#===== switches and simple filters ========# |
|
833 | 870 |
|
834 | 871 |
# special case transdate |
835 | 872 |
if (grep { $form->{$_} } qw(transdatefrom transdateto)) { |
... | ... | |
842 | 879 |
} |
843 | 880 |
} |
844 | 881 |
|
845 |
foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) {
|
|
882 |
foreach (@like_filters) {
|
|
846 | 883 |
next unless $form->{$_}; |
847 | 884 |
$form->{"l_$_"} = '1'; # show the column |
848 | 885 |
push @where_tokens, "$table_prefix{$_}$_ ILIKE ?"; |
... | ... | |
879 | 916 |
LEFT JOIN parts p_lc ON (a_lc.parts_id = p_lc.id) |
880 | 917 |
LEFT JOIN price_factors pfac_lc ON (p_lc.price_factor_id = pfac_lc.id) |
881 | 918 |
WHERE (a_lc.id = p.id)) AS lastcost|; |
882 |
|
|
883 |
my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate)); |
|
884 |
$form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; |
|
885 |
|
|
886 |
my $sort_order = ($form->{revers} ? ' DESC' : ' ASC'); |
|
887 |
|
|
888 |
my $order_clause = " ORDER BY $form->{sort} " . ($form->{revers} ? 'DESC' : 'ASC'); |
|
919 |
$table_prefix{$q_assembly_lastcost} = ' '; |
|
889 | 920 |
|
890 | 921 |
# special case: sorting by partnumber |
891 | 922 |
# since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix. |
... | ... | |
902 | 933 |
|
903 | 934 |
#=== joins and complicated filters ========# |
904 | 935 |
|
905 |
my $bsooqr = $form->{bought} || $form->{sold} |
|
906 |
|| $form->{ordered} || $form->{onorder} |
|
907 |
|| $form->{quoted} || $form->{rfq}; |
|
908 |
|
|
909 |
my @bsooqr; |
|
936 |
my $bsooqr = any { $form->{$_} } @oe_flags; |
|
910 | 937 |
my @bsooqr_tokens = (); |
938 |
|
|
911 | 939 |
push @select_tokens, @qsooqr_flags if $bsooqr; |
912 | 940 |
push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate}; |
913 | 941 |
push @select_tokens, $q_assembly_lastcost if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost}; |
... | ... | |
935 | 963 |
# find the old entries in of @where_tokens and @bind_vars, and adjust them |
936 | 964 |
if ($joins_needed{invoice_oi}) { |
937 | 965 |
for (my ($wi, $bi) = (0)x2; $wi <= $#where_tokens; $bi++ if $where_tokens[$wi++] =~ /\?/) { |
938 |
next unless $where_tokens[$wi] =~ /^description ILIKE/;
|
|
966 |
next unless $where_tokens[$wi] =~ /\bdescription ILIKE/;
|
|
939 | 967 |
splice @where_tokens, $wi, 1, 'p.description ILIKE ? OR ioi.description ILIKE ?'; |
940 | 968 |
splice @bind_vars, $bi, 0, $bind_vars[$bi]; |
941 | 969 |
last; |
... | ... | |
952 | 980 |
|
953 | 981 |
#============= build query ================# |
954 | 982 |
|
955 |
$table_prefix{$q_assembly_lastcost} = ' ';
|
|
983 |
my $token_builder = $make_token_builder->(\%joins_needed);
|
|
956 | 984 |
|
957 |
map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi}; |
|
958 |
map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns; |
|
985 |
my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate)); |
|
986 |
$form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; # sort by id if unknown or invisible column |
|
987 |
my $sort_order = ($form->{revers} ? ' DESC' : ' ASC'); |
|
988 |
my $order_clause = " ORDER BY " . $token_builder->($form->{sort}) . ($form->{revers} ? ' DESC' : ' ASC'); |
|
959 | 989 |
|
960 |
my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens;
|
|
990 |
my $select_clause = join ', ', map { $token_builder->($_, 1) } @select_tokens;
|
|
961 | 991 |
my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order }; |
962 | 992 |
my $where_clause = join ' AND ', map { "($_)" } @where_tokens; |
963 |
my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
|
|
993 |
my $group_clause = ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens if scalar @group_tokens;
|
|
964 | 994 |
|
965 | 995 |
my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'IC', |
966 | 996 |
'trans_id_field' => 'p.id', |
Auch abrufbar als: Unified diff
Warensuche:
Suche von überlagerten Spalten ausgelagert.
Als direkte Konsequenz wird deliverydate von Rechnungen jetzt korrekt
berechnet, aber auch description wird jetzt darüber abgewickelt.
Siehe Dokumentation im Code für Details.