Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 5f783ffe

Von Sven Schöling vor mehr als 15 Jahren hinzugefügt

  • ID 5f783ffe9c431bf8bf4eed4a23192368aaf8341d
  • Vorgänger 451843cf
  • Nachfolger 66b4e9c9

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.

Unterschiede anzeigen:

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