ASP.NET MVC5+EF6+EasyUI 后台管理系统（81）-数据筛选（万能查询）
来源：互联网 发布：风冷冷凝器设计软件 编辑：程序博客网 时间：2024/06/15 04:54
系列目录
View CodeView CodeView Code
前言
听标题的名字似乎是一个非常牛X复杂的功能，但是实际上它确实是非常复杂的，我们本节将演示如何实现对数据，进行组合查询（数据筛选）
我们都知道Excel中是如何筛选数据的.就像下面一样
他是一个并的关系，我们现在要做的也是这样的效果，下面我们将利用EasyUI的DataGrid为例来扩展（就算是其他组件也是可以的，同样的实现方式！）
实现思路
- 前台通过查询组合json
- 后台通过反射拆解json
- 进行组合查询
虽然短短3点，够你写个3天天夜了
优点：需要从很多数据中得到精准的数据，通常查一些商品他们的属性异常接近的情况下使用
缺点：我实现的方式为伪查询，大量数据请使用存储过程
简单了解
从Easyui的官方扩展中了解到一个JS文件，但是实质上，这个文件BUG很多，在使用中我曾经一度认为是使用出现问题，其实他根本就不可用
所以我这里先献上修改后的整个JS代码
修改版datagrid-filter.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'); // }}为了实现一个目的：输入数据后按回车查询数据。
这个扩展可以集成：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/1pL30drd 密码：1yrc
系列目录
阅读全文
0 0
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（81）-数据筛选（万能查询）
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（18）-权限管理系统-表数据
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（7）-MVC与EasyUI DataGrid
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（50）-Easyui 扁平化皮肤
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（52）-美化EasyUI皮肤和图标
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（83）-Easyui Datagrid 行内编辑扩展
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（19）-权限管理系统-用户登录
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（22）-权限管理系统-模块导航制作
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（86）-日程管理-fullcalendar插件用法
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（86）-日程管理-fullcalendar插件用法
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（28）-系统小结
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（34）-文章发布系统①-简要分析
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（35）-文章发布系统②-构建项目
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（36）-文章发布系统③-kindeditor使用
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（60）-系统总结
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（999）-如何使用这个系统来开发?
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（55）-Web打印
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（4）-创建项目解决方案
- 简单文章管理系统
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（80）-自由桌面
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（76）-微信公众平台开发-网页授权
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（63）-Excel导入和导出-自定义表模导入
- ASP.NET MVC5+EF6+EasyUI 后台管理系统 (源码购买说明)
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（81）-数据筛选（万能查询）
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（82）-Easyui Datagrid批量操作（编辑，删除，添加）
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（83）-Easyui Datagrid 行内编辑扩展
- 复杂进销存样例
- ASP.NET MVC5+EF6+EasyUI 后台管理系统（84）-Quartz 作业调度用法详解一
- FullCalendar 日历插件中文说明文档
- 日程管理 FullCalendar
- CSS中的时间
- SQL Server 创建表 添加主键 添加列常用SQL语句
