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
数据
MVC
系统
net
ef
后台管理
ef6
后台管理系统
easyui
mvc5
ASP
万能
asp.net mvc
