ASP.NET MVC5+EF6+EasyUI 后台管理系统(81)-数据筛选(万能查询)实例

不要因为一时的失败和挫折,就忘记以往壮志满满说过要去的远方。人皆有爱生恶死之心,人皆为舍生取死之道。何也见善不明耳。

前言

听标题的名字似乎是一个非常牛X复杂的功能,但是实际上它确实是非常复杂的,我们本节将演示如何实现对数据,进行组合查询(数据筛选)

我们都知道Excel中是如何筛选数据的.就像下面一样

他是一个并的关系,我们现在要做的也是这样的效果,下面我们将利用EasyUI的DataGrid为例来扩展(就算是其他组件也是可以的,同样的实现方式!)

实现思路

1.前台通过查询组合json

2.后台通过反射拆解json

3.进行组合查询

虽然短短3点,够你写个3天天夜了

优点:需要从很多数据中得到精准的数据,通常查一些商品他们的属性异常接近的情况下使用

缺点:我实现的方式为伪查询,大量数据请使用存储过程

简单了解

从Easyui的官方扩展中了解到一个JS文件,但是实质上,这个文件BUG很多,在使用中我曾经一度认为是使用出现问题,其实他根本就不可用

所以我这里先献上修改后的整个JS代码

(function($){
 function getPluginName(target){
  if ($(target).data('treegrid')){
   return 'treegrid';
  } else {
   return 'datagrid';
  }
 }

 var autoSizeColumn1 = $.fn.datagrid.methods.autoSizeColumn;
 var loadDataMethod1 = $.fn.datagrid.methods.loadData;
 var appendMethod1 = $.fn.datagrid.methods.appendRow;
 var deleteMethod1 = $.fn.datagrid.methods.deleteRow;
 $.extend($.fn.datagrid.methods, {
  autoSizeColumn: function(jq, field){
   return jq.each(function(){
    var fc = $(this).datagrid('getPanel').find('.datagrid-header .datagrid-filter-c');
    fc.hide();
    autoSizeColumn1.call($.fn.datagrid.methods, $(this), field);
    fc.show();
    resizeFilter(this, field);
   });
  },
  loadData: function(jq, data){
   jq.each(function(){
    $.data(this, 'datagrid').filterSource = null;
   });
   return loadDataMethod1.call($.fn.datagrid.methods, jq, data);
  },
  appendRow: function(jq, row){
   var result = appendMethod1.call($.fn.datagrid.methods, jq, row);
   jq.each(function(){
    var state = $(this).data('datagrid');
    if (state.filterSource){
     state.filterSource.total++;
     if (state.filterSource.rows != state.data.rows){
      state.filterSource.rows.push(row);
     }
    }
   });
   return result;
  },
  deleteRow: function(jq, index){
   jq.each(function(){
    var state = $(this).data('datagrid');
    var opts = state.options;
    if (state.filterSource && opts.idField){
     if (state.filterSource.rows == state.data.rows){
      state.filterSource.total--;
     } else {
      for(var i=0; i<state.filterSource.rows.length; i++){
       var row = state.filterSource.rows[i];
       if (row[opts.idField] == state.data.rows[index][opts.idField]){
        state.filterSource.rows.splice(i,1);
        state.filterSource.total--;
        break;
       }
      }
     }
    }
   });
   return deleteMethod1.call($.fn.datagrid.methods, jq, index);  
  }
 });

 var loadDataMethod2 = $.fn.treegrid.methods.loadData;
 var appendMethod2 = $.fn.treegrid.methods.append;
 var insertMethod2 = $.fn.treegrid.methods.insert;
 var removeMethod2 = $.fn.treegrid.methods.remove;
 $.extend($.fn.treegrid.methods, {
  loadData: function(jq, data){
   jq.each(function(){
    $.data(this, 'treegrid').filterSource = null;
   });
   return loadDataMethod2.call($.fn.treegrid.methods, jq, data);
  },
  append: function(jq, param){
   return jq.each(function(){
    var state = $(this).data('treegrid');
    var opts = state.options;
    if (opts.oldLoadFilter){
     var rows = translateTreeData(this, param.data, param.parent);
     state.filterSource.total += rows.length;
     state.filterSource.rows = state.filterSource.rows.concat(rows);
     $(this).treegrid('loadData', state.filterSource)
    } else {
     appendMethod2($(this), param);
    }
   });
  },
  insert: function(jq, param){
   return jq.each(function(){
    var state = $(this).data('treegrid');
    var opts = state.options;
    if (opts.oldLoadFilter){
     var ref = param.before || param.after;
     var index = getNodeIndex(param.before || param.after);
     var pid = index>=0 ? state.filterSource.rows[index]._parentId : null;
     var rows = translateTreeData(this, [param.data], pid);
     var newRows = state.filterSource.rows.splice(0, index>=0 ? (param.before ? index : index+1) : (state.filterSource.rows.length));
     newRows = newRows.concat(rows);
     newRows = newRows.concat(state.filterSource.rows);
     state.filterSource.total += rows.length;
     state.filterSource.rows = newRows;
     $(this).treegrid('loadData', state.filterSource);

     function getNodeIndex(id){
      var rows = state.filterSource.rows;
      for(var i=0; i<rows.length; i++){
       if (rows[i][opts.idField] == id){
        return i;
       }
      }
      return -1;
     }
    } else {
     insertMethod2($(this), param);
    }
   });
  },
  remove: function(jq, id){
   jq.each(function(){
    var state = $(this).data('treegrid');
    if (state.filterSource){
     var opts = state.options;
     var rows = state.filterSource.rows;
     for(var i=0; i<rows.length; i++){
      if (rows[i][opts.idField] == id){
       rows.splice(i, 1);
       state.filterSource.total--;
       break;
      }
     }
    }
   });
   return removeMethod2(jq, id);
  }
 });

 var extendedOptions = {
  filterMenuIconCls: 'icon-ok',
  filterBtnIconCls: 'fa fa-filter fa-lg ',
  filterBtnPosition: 'right',
  filterPosition: 'bottom',
  remoteFilter: false,
  showFilterBar: true,
  filterDelay: 400,
  filterRules: [],
  // specify whether the filtered records need to match ALL or ANY of the applied filters
  filterMatchingType: 'all', // possible values: 'all','any'
  // filterCache: {},
  filterMatcher: function(data){
   var name = getPluginName(this);
   var dg = $(this);
   var state = $.data(this, name);
   var opts = state.options;
   if (opts.filterRules.length){
    var rows = [];
    if (name == 'treegrid'){
     var rr = {};
     $.map(data.rows, function(row){
      if (isMatch(row, row[opts.idField])){
       rr[row[opts.idField]] = row;
       row = getRow(data.rows, row._parentId);
       while(row){
        rr[row[opts.idField]] = row;
        row = getRow(data.rows, row._parentId);
       }
      }
     });
     for(var id in rr){
      rows.push(rr[id]);
     }
    } else {
     for(var i=0; i<data.rows.length; i++){
      var row = data.rows[i];
      if (isMatch(row, i)){
       rows.push(row);
      }
     }
    }
    data = {
     total: data.total - (data.rows.length - rows.length),
     rows: rows
    };
   }
   return data;
   
   function isMatch(row, index){
    var rules = opts.filterRules;
    if (!rules.length){return true;}
    for(var i=0; i<rules.length; i++){
     var rule = rules[i];
     var source = row[rule.field];
     var col = dg.datagrid('getColumnOption', rule.field);
     if (col && col.formatter){
      source = col.formatter(row[rule.field], row, index);
     }
     if (source == undefined){
      source = '';
     }
     var op = opts.operators[rule.op];
     // if (!op.isMatch(source, rule.value)){return false}
     var matched = op.isMatch(source, rule.value);
     if (opts.filterMatchingType == 'any'){
      if (matched){return true;}
     } else {
      if (!matched){return false;}
     }
    }
    return opts.filterMatchingType == 'all';
   }
   function getRow(rows, id){
    for(var i=0; i<rows.length; i++){
     var row = rows[i];
     if (row[opts.idField] == id){
      return row;
     }
    }
    return null;
   }
  },
  defaultFilterType: 'text',
  defaultFilterOperator: 'contains',
  defaultFilterOptions: {
   onInit: function(target){
    var name = getPluginName(target);
    var opts = $(target)[name]('options');
    var field = $(this).attr('name');
    var input = $(this);
    if (input.data('textbox')){
     input = input.textbox('textbox');
    }
    input.unbind('.filter').bind('keydown.filter', function (e) {
     var t = $(this);
     if (this.timer){
      clearTimeout(this.timer);
     }
     if (e.keyCode == 13) {
      _doFilter();
     }
     else {
      this.timer = setTimeout(function(){
       _modifyFilter();
      }, opts.filterDelay);
     }
    });
    function _doFilter() {
     var rule = $(target)[name]('getFilterRule', field);
     var value = input.val();
     if (value != '') {
      $(target)[name]('addFilterRule', {
       field: field,
       op: opts.defaultFilterOperator,
       value: value
      });     
     } else {
      if (rule) {
       $(target)[name]('removeFilterRule', field);
      }
     }
     $(target)[name]('doFilter');
    };

    function _modifyFilter() {
     var rule = $(target)[name]('getFilterRule', field);
     var value = input.val();
     if (value != '') {
      if ((rule && rule.value != value) || !rule) {
       $(target)[name]('addFilterRule', {
        field: field,
        op: opts.defaultFilterOperator,
        value: value
       });
      }
     }
     else {
      if (rule) {
       $(target)[name]('removeFilterRule', field);
      }
     }
    };

    
   }
  },
  filterStringify: function(data){
   return JSON.stringify(data);
  },
  onClickMenu: function(item,button){}
 };
 $.extend($.fn.datagrid.defaults, extendedOptions);
 $.extend($.fn.treegrid.defaults, extendedOptions);
 
 // filter types
 $.fn.datagrid.defaults.filters = $.extend({}, $.fn.datagrid.defaults.editors, {
  label: {
   init: function(container, options){
    return $('<span></span>').appendTo(container);
   },
   getValue: function(target){
    return $(target).html();
   },
   setValue: function(target, value){
    $(target).html(value);
   },
   resize: function(target, width){
    $(target)._outerWidth(width)._outerHeight(22);
   }
  }
 });
 $.fn.treegrid.defaults.filters = $.fn.datagrid.defaults.filters;
 
 // filter operators
 $.fn.datagrid.defaults.operators = {
  nofilter: {
   text:Lang.Nofilter //'No Filter'
  },
  contains: {
   text:Lang.Contains ,
   isMatch: function(source, value){
    source = String(source);
    value = String(value);
    return source.toLowerCase().indexOf(value.toLowerCase()) >= 0;
   }
  },
  equal: {
   text:Lang.Equal,
   isMatch: function(source, value){
    return source == value;
   }
  },
  notequal: {
   text: Lang.Notequal,
   isMatch: function(source, value){
    return source != value;
   }
  },
  beginwith: {
   text: Lang.Beginwith,
   isMatch: function(source, value){
    source = String(source);
    value = String(value);
    return source.toLowerCase().indexOf(value.toLowerCase()) == 0;
   }
  },
  endwith: {
   text:Lang.Endwith,
   isMatch: function(source, value){
    source = String(source);
    value = String(value);
    return source.toLowerCase().indexOf(value.toLowerCase(), source.length - value.length) !== -1;
   }
  },
  less: {
   text: Lang.Less,
   isMatch: function(source, value){
    return source < value;
   }
  },
  lessorequal: {
   text: Lang.Lessorequal,
   isMatch: function(source, value){
    return source <= value;
   }
  },
  greater: {
   text: Lang.Greater,
   isMatch: function(source, value){
    return source > value;
   }
  },
  greaterorequal: {
   text: Lang.Greaterorequal,
   isMatch: function(source, value){
    return source >= value;
   }
  }
 };
 $.fn.treegrid.defaults.operators = $.fn.datagrid.defaults.operators;
 
 function resizeFilter(target, field){
  var toFixColumnSize = false;
  var dg = $(target);
  var header = dg.datagrid('getPanel').find('div.datagrid-header');
  var tr = header.find('.datagrid-header-row:not(.datagrid-filter-row)');
  var ff = field ? header.find('.datagrid-filter[name="'+field+'"]') : header.find('.datagrid-filter');
  ff.each(function(){
   var name = $(this).attr('name');
   var col = dg.datagrid('getColumnOption', name);
   var cc = $(this).closest('div.datagrid-filter-c');
   var btn = cc.find('a.datagrid-filter-btn');
   var cell = tr.find('td[field="'+name+'"] .datagrid-cell');
   var cellWidth = cell._outerWidth();
   if (cellWidth != _getContentWidth(cc)){
    this.filter.resize(this, cellWidth - btn._outerWidth());
   }
   if (cc.width() > col.boxWidth+col.deltaWidth-1){
    col.boxWidth = cc.width() - col.deltaWidth + 1;
    col.width = col.boxWidth + col.deltaWidth;
    toFixColumnSize = true;
   }
  });
  if (toFixColumnSize){
   $(target).datagrid('fixColumnSize');   
  }

  function _getContentWidth(cc){
   var w = 0;
   $(cc).children(':visible').each(function(){
    w += $(this)._outerWidth();
   });
   return w;
  }
 }
 
 function getFilterComponent(target, field){
  var header = $(target).datagrid('getPanel').find('div.datagrid-header');
  return header.find('tr.datagrid-filter-row td[field="'+field+'"] .datagrid-filter');
 }
 
 /**
  * get filter rule index, return -1 if not found.
  */
 function getRuleIndex(target, field){
  var name = getPluginName(target);
  var rules = $(target)[name]('options').filterRules;
  for(var i=0; i<rules.length; i++){
   if (rules[i].field == field){
    return i;
   }
  }
  return -1;
 }

 function getFilterRule(target, field){
  var name = getPluginName(target);
  var rules = $(target)[name]('options').filterRules;
  var index = getRuleIndex(target, field);
  if (index >= 0){
   return rules[index];
  } else {
   return null;
  }
 }
 
 function addFilterRule(target, param) {
  var name = getPluginName(target);
  var opts = $(target)[name]('options');
  var rules = opts.filterRules;

  if (param.op == 'nofilter'){
   removeFilterRule(target, param.field);
  } else {
   var index = getRuleIndex(target, param.field);
   if (index >= 0){
    $.extend(rules[index], param);
   } else {
    rules.push(param);
   }
  }

  var input = getFilterComponent(target, param.field);
  if (input.length){
   if (param.op != 'nofilter'){
    input[0].filter.setValue(input, param.value);
   }
   var menu = input[0].menu;
   if (menu){
    menu.find('.'+opts.filterMenuIconCls).removeClass(opts.filterMenuIconCls);
    var item = menu.menu('findItem', opts.operators[param.op]['text']);
    menu.menu('setIcon', {
     target: item.target,
     iconCls: opts.filterMenuIconCls
    });
   }
  }
 }
 
 function removeFilterRule(target, field){
  var name = getPluginName(target);
  var dg = $(target);
  var opts = dg[name]('options');
  if (field){
   var index = getRuleIndex(target, field);
   if (index >= 0){
    opts.filterRules.splice(index, 1);
   }
   _clear([field]);
  } else {
   opts.filterRules = [];
   var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields'));
   _clear(fields);
  }
  
  function _clear(fields){
   for(var i=0; i<fields.length; i++){
    var input = getFilterComponent(target, fields[i]);
    if (input.length){
     input[0].filter.setValue(input, '');
     var menu = input[0].menu;
     if (menu){
      menu.find('.'+opts.filterMenuIconCls).removeClass(opts.filterMenuIconCls);
     }
    }
   }
  }
 }
 
 function doFilter(target){
  var name = getPluginName(target);
  var state = $.data(target, name);
  var opts = state.options;
  if (opts.remoteFilter){
   $(target)[name]('load');
  } else {
   if (opts.view.type == 'scrollview' && state.data.firstRows && state.data.firstRows.length){
    state.data.rows = state.data.firstRows;
   }
   $(target)[name]('getPager').pagination('refresh', {pageNumber:1});
   $(target)[name]('options').pageNumber = 1;
   $(target)[name]('loadData', state.filterSource || state.data);
  }
 }
 
 function translateTreeData(target, children, pid){
  var opts = $(target).treegrid('options');
  if (!children || !children.length){return []}
  var rows = [];
  $.map(children, function(item){
   item._parentId = pid;
   rows.push(item);
   rows = rows.concat(translateTreeData(target, item.children, item[opts.idField]));
  });
  $.map(rows, function(row){
   row.children = undefined;
  });
  return rows;
 }

 function myLoadFilter(data, parentId){
  var target = this;
  var name = getPluginName(target);
  var state = $.data(target, name);
  var opts = state.options;

  if (name == 'datagrid' && $.isArray(data)){
   data = {
    total: data.length,
    rows: data
   };
  } else if (name == 'treegrid' && $.isArray(data)){
   var rows = translateTreeData(target, data, parentId);
   data = {
    total: rows.length,
    rows: rows
   }
  }
  if (!opts.remoteFilter){
   if (!state.filterSource){
    state.filterSource = data;
   } else {
    if (!opts.isSorting) {
     if (name == 'datagrid'){
      state.filterSource = data;
     } else {
      state.filterSource.total += data.length;
      state.filterSource.rows = state.filterSource.rows.concat(data.rows);
      if (parentId){
       return opts.filterMatcher.call(target, data);
      }
     }
    } else {
     opts.isSorting = undefined;
    }
   }
   if (!opts.remoteSort && opts.sortName){
    var names = opts.sortName.split(',');
    var orders = opts.sortOrder.split(',');
    var dg = $(target);
    state.filterSource.rows.sort(function(r1,r2){
     var r = 0;
     for(var i=0; i<names.length; i++){
      var sn = names[i];
      var so = orders[i];
      var col = dg.datagrid('getColumnOption', sn);
      var sortFunc = col.sorter || function(a,b){
       return a==b ? 0 : (a>b?1:-1);
      };
      r = sortFunc(r1[sn], r2[sn]) * (so=='asc'?1:-1);
      if (r != 0){
       return r;
      }
     }
     return r;
    });
   }
   data = opts.filterMatcher.call(target, {
    total: state.filterSource.total,
    rows: state.filterSource.rows
   });

   if (opts.pagination){
    var dg = $(target);
    var pager = dg[name]('getPager');
    pager.pagination({
     onSelectPage:function(pageNum, pageSize){
      opts.pageNumber = pageNum;
      opts.pageSize = pageSize;
      pager.pagination('refresh',{
       pageNumber:pageNum,
       pageSize:pageSize
      });
      //dg.datagrid('loadData', state.filterSource);
      dg[name]('loadData', state.filterSource);
     },
     onBeforeRefresh:function(){
      dg[name]('reload');
      return false;
     }
    });
    if (name == 'datagrid'){
     var start = (opts.pageNumber-1)*parseInt(opts.pageSize);
     var end = start + parseInt(opts.pageSize);
     data.rows = data.rows.slice(start, end);
    } else {
     var topRows = [];
     var childRows = [];
     $.map(data.rows, function(row){
      row._parentId ? childRows.push(row) : topRows.push(row);
     });
     data.total = topRows.length;
     var start = (opts.pageNumber-1)*parseInt(opts.pageSize); 
     var end = start + parseInt(opts.pageSize); 
     data.rows = topRows.slice(start, end).concat(childRows);
    }
   }
   $.map(data.rows, function(row){
    row.children = undefined;
   });
  }
  return data;
 }
 
 function init(target, filters){
  filters = filters || [];
  var name = getPluginName(target);
  var state = $.data(target, name);
  var opts = state.options;
  if (!opts.filterRules.length){
   opts.filterRules = [];
  }
  opts.filterCache = opts.filterCache || {};
  var dgOpts = $.data(target, 'datagrid').options;
  
  var onResize = dgOpts.onResize;
  dgOpts.onResize = function(width,height){
   resizeFilter(target);
   onResize.call(this, width, height);
  }
  var onBeforeSortColumn = dgOpts.onBeforeSortColumn;
  dgOpts.onBeforeSortColumn = function(sort, order){
   var result = onBeforeSortColumn.call(this, sort, order);
   if (result != false){
    opts.isSorting = true;    
   }
   return result;
  };

  var onResizeColumn = opts.onResizeColumn;
  opts.onResizeColumn = function(field,width){
   var fc = $(this).datagrid('getPanel').find('.datagrid-header .datagrid-filter-c');
   fc.hide();
   $(target).datagrid('fitColumns');
   if (opts.fitColumns){
    resizeFilter(target);
   } else {
    resizeFilter(target, field);
   }
   fc.show();
   onResizeColumn.call(target, field, width);
  };
  var onBeforeLoad = opts.onBeforeLoad;
  opts.onBeforeLoad = function(param1, param2){
   if (param1){
    param1.filterRules = opts.filterStringify(opts.filterRules);
   }
   if (param2){
    param2.filterRules = opts.filterStringify(opts.filterRules);
   }
   var result = onBeforeLoad.call(this, param1, param2);
   if (result != false && opts.url) {
    if (name == 'datagrid'){
     state.filterSource = null;
    } else if (name == 'treegrid' && state.filterSource){
     if (param1){
      var id = param1[opts.idField]; // the id of the expanding row
      var rows = state.filterSource.rows || [];
      for(var i=0; i<rows.length; i++){
       if (id == rows[i]._parentId){ // the expanding row has children
        return false;
       }
      }
     } else {
      state.filterSource = null;
     }
    }
   }
   return result;
  };

  // opts.loadFilter = myLoadFilter;
  opts.loadFilter = function(data, parentId){
   var d = opts.oldLoadFilter.call(this, data, parentId);
   return myLoadFilter.call(this, d, parentId);
  };
  
  initCss();
  createFilter(true);
  createFilter();
  if (opts.fitColumns){
   setTimeout(function(){
    resizeFilter(target);
   }, 0);
  }

  $.map(opts.filterRules, function(rule){
   addFilterRule(target, rule);
  });
  
  function initCss(){
   if (!$('#datagrid-filter-style').length){
    $('head').append(
     '<style id="datagrid-filter-style">' +
     'a.datagrid-filter-btn{display:inline-block;width:16px;height:16px;vertical-align:top;cursor:pointer;opacity:0.6;filter:alpha(opacity=60);}' +
     'a:hover.datagrid-filter-btn{opacity:1;filter:alpha(opacity=100);}' +
     '.datagrid-filter-row .textbox,.datagrid-filter-row .textbox .textbox-text{-moz-border-radius:0;-webkit-border-radius:0;border-radius:0;height:22px;line-height:22px;padding:0px;padding-left:3px;}' +
     '.datagrid-filter-row input{margin:0;-moz-border-radius:0;-webkit-border-radius:0;border-radius:0;}' +
     '.datagrid-filter-cache{position:absolute;width:10px;height:10px;left:-99999px;}' +
     '</style>'
    );
   }
  }
  
  /**
   * create filter component
   */
  function createFilter(frozen){
   var dc = state.dc;
   var fields = $(target).datagrid('getColumnFields', frozen);
   if (frozen && opts.rownumbers){
    fields.unshift('_');
   }
   var table = (frozen?dc.header1:dc.header2).find('table.datagrid-htable');
   
   // clear the old filter component
   table.find('.datagrid-filter').each(function(){
    if (this.filter.destroy){
     this.filter.destroy(this);
    }
    if (this.menu){
     $(this.menu).menu('destroy');
    }
   });
   table.find('tr.datagrid-filter-row').remove();
   
   var tr = $('<tr class="datagrid-header-row datagrid-filter-row"></tr>');
   if (opts.filterPosition == 'bottom'){
    tr.appendTo(table.find('tbody'));
   } else {
    tr.prependTo(table.find('tbody'));
   }
   if (!opts.showFilterBar){
    tr.hide();
   }
   
   for(var i=0; i<fields.length; i++){
    var field = fields[i];
    var col = $(target).datagrid('getColumnOption', field);
    var td = $('<td></td>').attr('field', field).appendTo(tr);
    if (col && col.hidden){
     td.hide();
    }
    if (field == '_'){
     continue;
    }
    if (col && (col.checkbox || col.expander)){
     continue;
    }

    var fopts = getFilter(field);
    if (fopts){
     $(target)[name]('destroyFilter', field); // destroy the old filter component
    } else {
     fopts = $.extend({}, {
      field: field,
      type: opts.defaultFilterType,
      options: opts.defaultFilterOptions
     });
    }

    var div = opts.filterCache[field];
    if (!div){
     div = $('<div class="datagrid-filter-c"></div>').appendTo(td);
     var filter = opts.filters[fopts.type];
     var input = filter.init(div, fopts.options||{});
     input.addClass('datagrid-filter').attr('name', field);
     input[0].filter = filter;
     input[0].menu = createFilterButton(div, fopts.op);
     if (fopts.options){
      if (fopts.options.onInit){
       fopts.options.onInit.call(input[0], target);
      }
     } else {
      opts.defaultFilterOptions.onInit.call(input[0], target);
     }
     opts.filterCache[field] = div;
     resizeFilter(target, field);
    } else {
     div.appendTo(td);
    }
   }
  }
  
  function createFilterButton(container, operators){
   if (!operators){return null;}
   
   var btn = $('<a class="datagrid-filter-btn"></a>').addClass(opts.filterBtnIconCls);
   if (opts.filterBtnPosition == 'right'){
    btn.appendTo(container);
   } else {
    btn.prependTo(container);
   }

   var menu = $('<div></div>').appendTo('body');
   $.map(['nofilter'].concat(operators), function(item){
    var op = opts.operators[item];
    if (op){
     $('<div></div>').attr('name', item).html(op.text).appendTo(menu);
    }
   });
   menu.menu({
    alignTo:btn,
    onClick:function(item){
     var btn = $(this).menu('options').alignTo;
     var td = btn.closest('td[field]');
     var field = td.attr('field');
     var input = td.find('.datagrid-filter');
     var value = input[0].filter.getValue(input);
     
     if (opts.onClickMenu.call(target, item, btn, field) == false){
      return;
     }
     
     addFilterRule(target, {
      field: field,
      op: item.name,
      value: value
     });
     
     doFilter(target);
    }
   });

   btn[0].menu = menu;
   btn.bind('click', {menu:menu}, function(e){
    $(this.menu).menu('show');
    return false;
   });
   return menu;
  }
  
  function getFilter(field){
   for(var i=0; i<filters.length; i++){
    var filter = filters[i];
    if (filter.field == field){
     return filter;
    }
   }
   return null;
  }
 }
 
 $.extend($.fn.datagrid.methods, {
  enableFilter: function(jq, filters){
   return jq.each(function(){
    var name = getPluginName(this);
    var opts = $.data(this, name).options;
    if (opts.oldLoadFilter){
     if (filters){
      $(this)[name]('disableFilter');
     } else {
      return;
     }
    }
    opts.oldLoadFilter = opts.loadFilter;
    init(this, filters);
    $(this)[name]('resize');
    if (opts.filterRules.length){
     if (opts.remoteFilter){
      doFilter(this);
     } else if (opts.data){
      doFilter(this);
     }
    }
   });
  },
  disableFilter: function(jq){
   return jq.each(function(){
    var name = getPluginName(this);
    var state = $.data(this, name);
    var opts = state.options;
    var dc = $(this).data('datagrid').dc;
    var div = dc.view.children('.datagrid-filter-cache');
    if (!div.length){
     div = $('<div class="datagrid-filter-cache"></div>').appendTo(dc.view);
    }
    for(var field in opts.filterCache){
     $(opts.filterCache[field]).appendTo(div);
    }
    var data = state.data;
    if (state.filterSource){
     data = state.filterSource;
     $.map(data.rows, function(row){
      row.children = undefined;
     });
    }
    $(this)[name]({
     data: data,
     loadFilter: (opts.oldLoadFilter||undefined),
     oldLoadFilter: null
    });
   });
  },
  destroyFilter: function(jq, field){
   return jq.each(function(){
    var name = getPluginName(this);
    var state = $.data(this, name);
    var opts = state.options;
    if (field){
     _destroy(field);
    } else {
     for(var f in opts.filterCache){
      _destroy(f);
     }
     $(this).datagrid('getPanel').find('.datagrid-header .datagrid-filter-row').remove();
     $(this).data('datagrid').dc.view.children('.datagrid-filter-cache').remove();
     opts.filterCache = {};
     $(this)[name]('resize');
     $(this)[name]('disableFilter');
    }

    function _destroy(field){
     var c = $(opts.filterCache[field]);
     var input = c.find('.datagrid-filter');
     if (input.length){
      var filter = input[0].filter;
      if (filter.destroy){
       filter.destroy(input[0]);
      }
     }
     c.find('.datagrid-filter-btn').each(function(){
      $(this.menu).menu('destroy');
     });
     c.remove();
     opts.filterCache[field] = undefined;
    }
   });
  },
  getFilterRule: function(jq, field){
   return getFilterRule(jq[0], field);
  },
  addFilterRule: function(jq, param){
   return jq.each(function(){
    addFilterRule(this, param);
   });
  },
  removeFilterRule: function(jq, field){
   return jq.each(function(){
    removeFilterRule(this, field);
   });
  },
  doFilter: function(jq){
   return jq.each(function(){
    doFilter(this);
   });
  },
  getFilterComponent: function(jq, field){
   return getFilterComponent(jq[0], field);
  },
  resizeFilter: function(jq, field){
   return jq.each(function(){
    resizeFilter(this, field);
   });
  }
 });
})(jQuery);


InitDateFilter = function (dg, field, op) {
 var filter = {
  field: field,
  type: 'datebox',
  options: {
   editable: false,
   onChange: function (newValue, oldValue) {
    var curRule = dg.datagrid("getFilterRule", field);
    if (curRule != null) {
     curRule.value = newValue;
     dg.datagrid('addFilterRule', curRule);
    }
   }
  },
  op: op
 };
 return filter;
};
//Combox类型过滤
InitComboFilter = function (dg, field, data, url, valueField, textField, checkFiled, method) {
 var comboOption;
 if (url != null) {
  comboOption = {
   panelHeight: 'auto',
   url: url,
   method: method,
   valueField: valueField,
   textField: textField,
   panelMaxHeight: 200,
   onLoadSuccess: function (result) {
    data = result;
   },
   onChange: function (value) {
    DoComboFilter(dg, data, field, value, checkFiled);
   }
  };
 }
 else {
  comboOption = {
   panelHeight: 'auto',
   data: data,
   valueField: valueField,
   textField: textField,
   panelMaxHeight: 200,
   onChange: function (value) {
    DoComboFilter(dg, data, field, value, checkFiled);
   }
  };
 }
 var filter = {
  field: field,
  type: 'combobox',
  options: comboOption,
 }
 //$(".datagrid-filter-row td[field='" + field + "']").find("input").height(22);
 //console.log($(".datagrid-filter-row").html());
 return filter;
};

InitNumberFilter = function (dg, field, op) {
 var filter = {
  field: field,
  type: 'numberbox',
  options: { precision: 1 },
  op: op
 };
 return filter;
};
//启动combo过滤器
function DoComboFilter(dg, data, field, value, checkFiled) {
 if (value == "") {
  dg.datagrid('removeFilterRule', field);
  dg.datagrid('doFilter');
  return;
 }
 // if (Common.CommonHelper.lslnArray(data, value, checkFiled)) {
 dg.datagrid('addFilterRule', {
  field: field,
  op: 'equal',
  value: value
 });
 dg.datagrid('doFilter');
 // }
}

修改版datagrid-filter.js

为了实现一个目的:输入数据后按回车查询数据。

这个扩展可以集成:Easyui 90%的Form组件

1.时间

2.数字

3.下拉Combobox

4.密码框

等等.......

实际上只用到1,2,3个Combxbox一般为动态数据AJAX从后台获取

看到代码(我已经封装好了,尽情调用即可,想要了解就进入查看代码写法和逻辑)

上面的废话已经说完了!下面来说说如何调用

前端实现方式

1.引入datagrid-filter.js

<script src="~/Scripts/easyui/datagrid-filter.js"></script>

2.调用

调用之前来看看我们以前写的datagrid。这是一个普通的datagrid

 $('#List').datagrid({
   url: '@Url.Action("GetList")',
   width: SetGridWidthSub(10),
   methord: 'post',
   height: $(window).height()/2-35,
   fitColumns: true,
   sortName: 'CreateTime',
   sortOrder: 'desc',
   idField: 'Id',
   pageSize: 15,
   pageList: [15, 20, 30, 40, 50],
   pagination: true,
   striped: true, //奇偶行是否区分
   singleSelect: true,//单选模式
   remoteFilter:true,
   columns: [[
    { field: 'Id', title: 'Id', width: 80,hidden:true},
    { field: 'Name', title: '产品名称', width: 80, sortable: true },
    { field: 'Code', title: '产品代码', width: 80, sortable: true },
    { field: 'Price', title: '产品价格', width: 80, sortable: true },
    { field: 'Color', title: '产品颜色', width: 80, sortable: true },
    { field: 'Number', title: '产品数量', width: 80, sortable: true },
    {
     field: 'CategoryId', title: '类别', width: 80, sortable: true, formatter: function (value, row, index) {
      return row.ProductCategory;
     }
    },
    { field: 'ProductCategory', title: '类别', width: 80, sortable: true,hidden:true },
    { field: 'CreateTime', title: 'CreateTime', width: 80, sortable: true },
    { field: 'CreateBy', title: 'CreateBy', width: 80, sortable: true }
   ]]
  });

那么我只想告诉大家我的DataGrid用的id名称是List而已

     var dg = $('#List');
  var op = ['equal', 'notequal', 'less', 'greater'];
  var comboData=[{ value: '', text: 'All' }, { value: 'P', text: 'P' }, { value: 'N', text: 'N' }]
  dg.datagrid('enableFilter', [
   InitNumberFilter(dg, 'Price', op),
   InitNumberFilter(dg, 'Number', op),
   InitDateFilter(dg, 'CreateTime', op),
   InitComboFilter(dg, 'CategoryId', comboData, '', 'Id', 'Name', 'Name', "post")
  ]);

那么前端的效果就出来了!如此简单都是因为封装的JS帮我们做了大量的工作,效果如下:

说明一下:InitComboFilter如果是Ajax那么第4个参数传URL即可,键值分别是Id和Name

其中:var op = ['equal', 'notequal', 'less', 'greater'];是漏斗,说再多也不明白,如要深入了解需要看源码

3.回车执行过滤

回车事件在源码中的

到此,前端的调用就结束了!

后台实现方式

因为前端会传过来多一个参数,所以我们后台需要写多一个参数来接受,修改以前的GridPager就补多一个参数就好了。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Apps.Common
{
  public class GridPager
 {
  public int rows { get; set; }//每页行数
  public int page { get; set; }//当前页是第几页
  public string order { get; set; }//排序方式
  public string sort { get; set; }//排序列
  public int totalRows { get; set; }//总行数
  public int totalPages //总页数
  {
   get
   {
    return (int)Math.Ceiling((float)totalRows / (float)rows);
   }
  }
  public string filterRules { get; set; }
 }


  public class GridRows<T>
  {
   public List<T> rows { get; set; }
   public int total { get; set; }
  }
}

public string filterRules { get; set; }

所以Controller没有变化。

BLL变化如下:

using Apps.Common;
using Apps.Models;
using Apps.Models.Spl;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.Spl.BLL
{
 public partial class Spl_ProductBLL
 {
  public override List<Spl_ProductModel> GetList(ref GridPager pager, string queryStr)
  {

   IQueryable<Spl_Product> queryData = null;
   if (!string.IsNullOrWhiteSpace(queryStr))
   {
    queryData = m_Rep.GetList(
        a=>a.Id.Contains(queryStr)
        || a.Name.Contains(queryStr)
        || a.Code.Contains(queryStr)
        
        || a.Color.Contains(queryStr)
        
        || a.CategoryId.Contains(queryStr)
        
        || a.CreateBy.Contains(queryStr)
        
        );
   }
   else
   {
    queryData = m_Rep.GetList();
   }
  
   //启用通用列头过滤
   if (!string.IsNullOrWhiteSpace(pager.filterRules))
   {
    List<DataFilterModel> dataFilterList = JsonHandler.Deserialize<List<DataFilterModel>>(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList();
    queryData = LinqHelper.DataFilter<Spl_Product>(queryData, dataFilterList);
   }

   pager.totalRows = queryData.Count();
   //排序
   queryData = LinqHelper.SortingAndPaging(queryData, pager.sort, pager.order, pager.page, pager.rows);
   return CreateModelList(ref queryData);
  }
  public override List<Spl_ProductModel> CreateModelList(ref IQueryable<Spl_Product> queryData)
  {

   List<Spl_ProductModel> modelList = (from r in queryData
            select new Spl_ProductModel
            {
             Id = r.Id,
             Name = r.Name,
             Code = r.Code,
             Price = r.Price,
             Color = r.Color,
             Number = r.Number,
             CategoryId = r.CategoryId,
             CreateTime = r.CreateTime,
             CreateBy = r.CreateBy,
             CostPrice = r.CostPrice,
             ProductCategory = r.Spl_ProductCategory.Name
            }).ToList();

   return modelList;
  }

 }
}

//启用通用列头过滤
   if (!string.IsNullOrWhiteSpace(pager.filterRules))
   {
    List<DataFilterModel> dataFilterList = JsonHandler.Deserialize<List<DataFilterModel>>(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList();
    queryData = LinqHelper.DataFilter<Spl_Product>(queryData, dataFilterList);
   }

其他都不变。

后台也是做了大量大量的工作的,看LinqHelper这个类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Apps.Common
{
 public class LinqHelper
 {
  /// <summary>
  /// 排序
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="source"></param>
  /// <param name="sortExpression"></param>
  /// <param name="sortDirection"></param>
  /// <returns></returns>
  public static IQueryable<T> DataSorting<T>(IQueryable<T> source, string sortExpression, string sortDirection)
  {
   //错误查询
   if (string.IsNullOrEmpty(sortExpression) || string.IsNullOrEmpty(sortDirection))
   {
    return source;
   }
   string sortingDir = string.Empty;
   if (sortDirection.ToUpper().Trim() == "ASC")
    sortingDir = "OrderBy";
   else if (sortDirection.ToUpper().Trim() == "DESC")
    sortingDir = "OrderByDescending";
   ParameterExpression param = Expression.Parameter(typeof(T), sortExpression);
   PropertyInfo pi = typeof(T).GetProperty(sortExpression);
   Type[] types = new Type[2];
   types[0] = typeof(T);
   types[1] = pi.PropertyType;
   Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortExpression), param));
   IQueryable<T> query = source.AsQueryable().Provider.CreateQuery<T>(expr);
   return query;
  }
  /// <summary>
  /// 分页
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="source"></param>
  /// <param name="pageNumber"></param>
  /// <param name="pageSize"></param>
  /// <returns></returns>
  public static IQueryable<T> DataPaging<T>(IQueryable<T> source, int pageNumber, int pageSize)
  {
   if (pageNumber <= 1)
   {
    return source.Take(pageSize);
   }
   else
   {
    return source.Skip((pageNumber - 1) * pageSize).Take(pageSize);
   }
  }
  /// <summary>
  /// 排序并分页 
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="source"></param>
  /// <param name="sortExpression"></param>
  /// <param name="sortDirection"></param>
  /// <param name="pageNumber"></param>
  /// <param name="pageSize"></param>
  /// <returns></returns>
  public static IQueryable<T> SortingAndPaging<T>(IQueryable<T> source, string sortExpression, string sortDirection, int pageNumber, int pageSize)
  {
   IQueryable<T> query = DataSorting<T>(source, sortExpression, sortDirection);
   return DataPaging(query, pageNumber, pageSize);
  }


  ///<summary>
  ///表达式操作
  ///</summary>
  ///<param name="right"></param>
  ///<param name="left"></param>
  ///<returns></returns>
  public delegate Expression ExpressionOpretaDelegate(Expression left, Expression right);

  /*
   * if (!string.IsNullOrWhiteSpace(pager.filterRules))
   *   {
   *    IEnumerable<DataFilterModel> dataFilterList = JsonHelper.DeserializeJsonToObject<List<DataFilterModel>>(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value));
   *    queryData = LinqHelper.DataFilter<SysSample>(queryData.AsQueryable(), dataFilterList);
   *   }
   */
  ///<summary>///通用数据列表按过滤方法
  ///</summary>
  ///<typeparam name="T">过滤的数据类型</typeparam>
  ///<param name="source">过滤的数据源</param>
  ///<paramname="dataFilterList">过滤条件集合(包含,字段名,值,操作符) </param>
  ///<returns></returns>
  public static IQueryable<T> DataFilter<T>(IQueryable<T> source, IEnumerable<DataFilterModel> datas)
  {
   T obj = System.Activator.CreateInstance<T>();
   PropertyInfo[] properties = obj.GetType().GetProperties();
   foreach (var item in datas)
   {
    PropertyInfo p = properties.Where(pro => pro.Name == item.field).FirstOrDefault();
    //不进行无效过滤
    if (p == null || item.value == null)
    {
     continue;
    }
    if (p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(DateTime?))
    {
     //时间过1滤 
     source = DateDataFilter<T>(source, item, p);
    }
    else
    {
     //普通过滤
     source = OrdinaryDataFilter<T>(source, item, p);
    }
   }
   return source;
  }
  ///<summary>
  ///普通数据过滤
  ///</summary>
  ///<typeparam name="T"></typeparam>
  ///<param name="source"></param>
  ///<param name="item"></param>
  ///<param name="p"></param> 
  ///<retums></retums>
  private static IQueryable<T> OrdinaryDataFilter<T>(IQueryable<T> source, DataFilterModel item, PropertyInfo p)
  {
   //var selectvalue = Convert.
   //   ChangeType(item.value, p.PropertyType);
   var option = (DataFliterOperatorTypeEnum)
      Enum.Parse(typeof(DataFliterOperatorTypeEnum), item.op);
   switch (option)
   {
    case DataFliterOperatorTypeEnum.contains:
     {
      /* 包含, 目前只支持字符串 */
      source = ExpressionOperate(StringContains, source, p, item.value);
      break;
     }
    case DataFliterOperatorTypeEnum.equal:
     {
      /* 等于 */
      source = ExpressionOperate(Expression.Equal, source, p, item.value);
      break;
     }

    case DataFliterOperatorTypeEnum.greater:
     {
      /* 大于 */
      source = ExpressionOperate(Expression.GreaterThan, source, p, item.value);
      break;
     }

    case DataFliterOperatorTypeEnum.greaterorequal:
     {
      /* 大于等于 */
      source =
       ExpressionOperate(Expression.GreaterThanOrEqual, source, p, item.value);
      break;
     }
    case DataFliterOperatorTypeEnum.less:
     {
      /* 小于 */
      source = ExpressionOperate(Expression.LessThan, source, p, item.value);
      break;
     }
    case DataFliterOperatorTypeEnum.lessorequal:
     {
      /* 小于等于 */
      source = ExpressionOperate(Expression.LessThanOrEqual, source, p, item.value);
      break;
     }
    default: break;
   }
   return (source);
  }
  ///<summary> 
  ///时间过滤
  ///</summary>
  ///<typeparam name="T"></typeparam>
  ///<param name="source"></param>
  ///<param name="item"></param>
  ///<param name="p"></param> 
  ///<returns></returns>
  public static IQueryable<T> DateDataFilter<T>(IQueryable<T> source, DataFilterModel item, PropertyInfo p)
  {
   var selectDate= Convert.ToDateTime(item.value);
   var option= (DataFliterOperatorTypeEnum)
   Enum.Parse(typeof(DataFliterOperatorTypeEnum), item.op);
   switch(option) 
   { 
    case DataFliterOperatorTypeEnum.equal: 
    {
     //大于0时
     source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate);
     //小于后一天
     var nextDate= selectDate.AddDays(1); 
     source=ExpressionOperate(Expression.LessThan, source, p, nextDate); 
     break;
    }

    case DataFliterOperatorTypeEnum.greater:
    {
     //大于等于后一天
     selectDate= selectDate.AddDays(1);
     source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate);
     break;
    }

    case DataFliterOperatorTypeEnum.greaterorequal:
    {
     //大于等于当天
     source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate);
     break;
    }

    case DataFliterOperatorTypeEnum.less:
    {
     //小于当天
     source=ExpressionOperate(Expression. LessThan, source, p,selectDate);
     break;
    }
    case DataFliterOperatorTypeEnum.lessorequal: 
    {
     //小于第二天
     selectDate= selectDate.AddDays(1);
     source=ExpressionOperate(Expression. LessThan, source, p,selectDate);
     break;
    } 
    default: break;
   }
   return source; 
  }

  ///<summary>
  ///过滤操作
  ///</summary>
  ///<typeparam name="T"></typeparam>
  //<typeparam name="V"></typeparam>
  ///<paramname="operateExpression"></ param>
  ///<param name="source"></param>
  ///<param name="p"></param> ///<param name="value"></param>
  ///<returns></returns>
  private static IQueryable<T> ExpressionOperate<T, V>(ExpressionOpretaDelegate operateExpression, IQueryable<T> source, PropertyInfo p, V value)
  {

   
   Expression right = null;
   if (p.PropertyType == typeof(Int32))
   {
    int val = Convert.ToInt32(value);
    right = Expression.Constant(val, p.PropertyType);
   }
   else if (p.PropertyType == typeof(Decimal))
   {
    Decimal val = Convert.ToDecimal(value);
    right = Expression.Constant(val, p.PropertyType);
   }
   else if (p.PropertyType == typeof(Byte))
   {
    Byte val = Convert.ToByte(value);
    right = Expression.Constant(val, p.PropertyType);
   }
   else
   {
    right = Expression.Constant(value, p.PropertyType);
   }
   ParameterExpression param = Expression.Parameter(typeof(T), "x");
   Expression left = Expression.Property(param, p.Name);
   Expression filter = operateExpression(left, right);
   Expression<Func<T, bool>> pred = Expression.Lambda<Func<T, bool>>(filter, param);
   source = source.Where(pred);
   return source;
  }

  ///<summary>
  ///字符串包含操作
  ///</summary>
  ///<param name="left"></param>
  ///<param name="right"></param>
  ///<returns></returns>
  public static Expression StringContains(Expression left, Expression right)
  {
   Expression filter = Expression.Call(left, typeof(string).GetMethod("Contains"), right);
   return filter;
  }

 }
}

预览效果:

总结

实现一个组合查询,只需要在原来的基础上添加几行代码

后台:

 //启用通用列头过滤
   if (!string.IsNullOrWhiteSpace(pager.filterRules))
   {
    List<DataFilterModel> dataFilterList = JsonHandler.Deserialize<List<DataFilterModel>>(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList();
    queryData = LinqHelper.DataFilter<Spl_Product>(queryData, dataFilterList);
   }

前端:

 var dg = $('#List');
  var op = ['equal', 'notequal', 'less', 'greater'];
  var comboData={Category:[]}; //[{ value: '', text: 'All' }, { value: 'P', text: 'P' }, { value: 'N', text: 'N' }]
  
  dg.datagrid('enableFilter', [
   InitNumberFilter(dg, 'Price', op),
   InitNumberFilter(dg, 'Number', op),
   InitDateFilter(dg, 'CreateTime', op),
   InitComboFilter(dg, 'CategoryId', comboData, '../Spl/ProductCategory/GetComboxData', 'Id', 'Name', 'Name', "post")
  ]);

完全没有任何逻辑,谁都能用,示例代码下载

http://pan.baidu.com/s/1dF409yx

以上就是ASP.NET MVC5+EF6+EasyUI 后台管理系统(81)-数据筛选(万能查询)实例。幸福是什么?幸福就是,天亮了该睡觉,天黑了该活动。更多关于ASP.NET MVC5+EF6+EasyUI 后台管理系统(81)-数据筛选(万能查询)实例请关注haodaima.com其它相关文章!

标签: 管理系统 NET