public void ShouldFilterString()
{
// given
var gridFilter = new GridFilters <UserRM>
{
PageIndex = 1,
PageSize = 5,
SortField = "Email",
SortOrder = "asc",
Filters = new UserRM
{
Id = 69,
Email = "",
FirstName = "Mark",
LastName = "",
}
};
// when
var result = _queryHandler.Execute(gridFilter, new TeachersQueryHandler.Query());
// then
Assert.AreEqual(result.ItemsCount, 2);
CollectionAssert.AreEquivalent(
result.Data.Select(a => a.Id),
new[] { _teacher1, _teacher2 }.Select(a => a.ID)
);
}
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
GridOptions requestOptions = new GridOptions();
var requestQuery = filterContext.HttpContext.Request.QueryString;
if (requestQuery.HasKeys())
{
GridFilters filterOps = null;
string filterString = requestQuery["filters"];
if (!String.IsNullOrWhiteSpace(filterString))
{
filterOps = Newtonsoft.Json.JsonConvert.DeserializeObject <GridFilters>(filterString);
}
requestOptions.Filters = filterOps;
requestOptions.IsSearch = Boolean.Parse(requestQuery["_search"]);
requestOptions.ND = requestQuery["nd"];
requestOptions.Page = Int32.Parse(requestQuery["page"]);
requestOptions.Rows = Int32.Parse(requestQuery["rows"]);
requestOptions.SortIndex = requestQuery["sidx"];
requestOptions.SortOrder = requestQuery["sord"];
}
if (filterContext.ActionParameters.ContainsKey("options"))
{
filterContext.ActionParameters["options"] = requestOptions;
}
else
{
filterContext.ActionParameters.Add("options", requestOptions);
}
base.OnActionExecuting(filterContext);
}
public void GridFilters_RegistersDefaultFilters(Type type, String name, Type filter)
{
Type actual = new GridFilters().Table[type][name];
Type expected = filter;
Assert.Equal(expected, actual);
}
public JsonResult ReadCTKhenThuong(GridFilters filter = null)
{
try
{
IEnumerable <CTKhenThuongViewModel> dsCTKhenThuong = null;
//filter
if (filter != null && filter.Filters != null && filter.Filters.Count > 0)
{
var field = filter.Filters[0].Field;
var operate = filter.Filters[0].Operator;
var value = filter.Filters[0].Value;
int maqd;
if (field == "MaQuyetDinh" && operate == "eq" &&
int.TryParse(value, out maqd))
{
dsCTKhenThuong = _CTKhenThuongService.GetCTKhenThuongInQDKhenThuong(maqd);
}
}
if (dsCTKhenThuong == null)
{
dsCTKhenThuong = _CTKhenThuongService.GetAllCTKhenThuongVM();
}
return(Json(dsCTKhenThuong));
}
catch (Exception ex)
{
//Show Message here
return(Json(new { error = ex.Message }));
}
}
protected IQueryable <T> ApplyOrder <T>(GridFilters <TResult> filters, IQueryable <T> query)
{
string methodName;
if (filters.SortOrder?.ToLower() == "asc")
{
methodName = nameof(Queryable.OrderBy);
}
else if (filters.SortOrder?.ToLower() == "desc")
{
methodName = nameof(Queryable.OrderByDescending);
}
else
{
return(query);
}
var elementType = typeof(T);
var parameterExpression = Expression.Parameter(elementType);
var propertyExpression = Expression.Property(parameterExpression, filters.SortField);
var selector = Expression.Lambda(propertyExpression, parameterExpression);
var orderByExpression = Expression.Call(
typeof(Queryable),
methodName,
new[] { elementType, propertyExpression.Type },
query.Expression,
selector);
return(query.Provider.CreateQuery <T>(orderByExpression));
}
public override GridResult <UserRM> Execute(GridFilters <UserRM> filters, Query query)
{
var students = _dbContext.Students
.Include(a => a.AppUser);
return(Result(filters, students));
}
public override GridResult <UserRM> Execute(GridFilters <UserRM> filters, Query query)
{
var admins = _dbContext.Admins
.Include(a => a.AppUser);
return(Result(filters, admins));
}
public GridFiltersTests()
{
filters = new GridFilters();
Grid <GridModel> grid = new Grid <GridModel>(Array.Empty <GridModel>());
column = new GridColumn <GridModel, String?>(grid, model => model.Name);
}
public GridFiltersTests()
{
filters = new GridFilters();
Grid <GridModel> grid = new Grid <GridModel>(new GridModel[0]);
column = new GridColumn <GridModel, String>(grid, model => model.Name);
}
public static IEnumerable <TRow> Select <TKey, TTable, TDataContext, TRow>(
this BaseDataSourceView <TKey, TTable, TDataContext, TRow> view,
StoreReadDataEventArgs e,
BaseFilterControl <TKey, TTable, TDataContext> filter,
GridFilters gridFilter,
BaseGridColumns columns,
bool sort)
where TKey : struct
where TTable : class
where TDataContext : DataContext, new()
where TRow : BaseRow, new()
{
var dataSourceSelectArguments = new DataSourceSelectArguments(e.Start, e.Limit);
dataSourceSelectArguments.RetrieveTotalRowCount = true;
if (sort && e.Sort != null && e.Sort.Length > 0)
{
dataSourceSelectArguments.SortExpression = GetSortExpression(columns, e.Sort);
}
IEnumerable result = null;
view.Select(dataSourceSelectArguments, data => { result = data; });
e.Total = dataSourceSelectArguments.TotalRowCount;
return(result.Cast <TRow>());
}
public override GridResult <UserRM> Execute(GridFilters <UserRM> filters, Query query)
{
var teachers = _dbContext.Teachers
.Include(a => a.AppUser);
return(Result(filters, teachers));
}
public GridFiltersTests()
{
column = new GridColumn<GridModel, String>(new Grid<GridModel>(new GridModel[0]), model => model.Name);
column.Grid.Query = new QueryCollection();
column.IsMultiFilterable = true;
filters = new GridFilters();
}
public GridFiltersTests()
{
column = new GridColumn <GridModel, String>(new Grid <GridModel>(new GridModel[0]), model => model.Name);
column.Grid.Query = new QueryCollection();
column.IsMultiFilterable = true;
filters = new GridFilters();
}
public void ShouldSort(string order)
{
// given
var gridFilter = new GridFilters <UserRM>
{
PageIndex = 1,
PageSize = 5,
SortField = "Email",
SortOrder = "asc",
};
// when
var result = _queryHandler.Execute(gridFilter, new TeachersQueryHandler.Query());
// then
if (order == "asc")
{
CollectionAssert.AreEquivalent(
result.Data.Select(a => a.Id),
_teachers.OrderBy(x => x.AppUser.Email).Select(x => x.ID)
);
}
else if (order == "asc")
{
CollectionAssert.AreEquivalent(
result.Data.Select(a => a.Id),
_teachers.OrderByDescending(x => x.AppUser.Email).Select(x => x.ID)
);
}
}
public void GridFilters_RegistersDefaultFilters(Type type, String name, Type expected)
{
GridFilters filters = new GridFilters();
Type actual = filters.Table[type][name];
Assert.AreEqual(expected, actual);
}
public override GridResult <SectionRM> Execute(GridFilters <SectionRM> filters, Query query)
{
var sections = _dbContext.Sections
.Include(x => x.StudentSections)
.Where(x => x.StudentSections.Any(y => y.StudentID == query.StudentId));
return(Result(filters, sections));
}
public override GridResult <SectionRM> Execute(GridFilters <SectionRM> filters, Query query)
{
var sections = _dbContext.Sections
.Include(x => x.StudentSections)
.Where(x => x.State == Entities.SectionState.Active)
.Where(x => x.StudentSections.All(y => y.StudentID != query.StudentId));
return(Result(filters, sections));
}
public override GridResult <SectionRM> Execute(GridFilters <SectionRM> filters, Query query)
{
var sections = _dbContext.Sections.Join(
_dbContext.Topics.Where(t => t.TeacherID == query.TeacherID),
x => x.TopicID,
y => y.ID,
(sec, top) => sec);
return(Result(filters, sections));
}
public GridFiltersTests()
{
Expression<Func<GridModel, String>> expression = (model) => model.Name;
column = Substitute.For<IGridColumn<GridModel>>();
column.Grid.Query = new NameValueCollection();
column.Expression.Returns(expression);
column.IsMultiFilterable = true;
column.Grid.Name = "Grid";
column.Name = "Name";
filters = new GridFilters();
}
public static IServiceCollection AddItnGrid(this IServiceCollection services, Action <IGridFilters> configure)
{
services.Configure <MvcOptions>(options =>
{
options.ModelBinderProviders.Insert(0, new GridModelBinderProvider());
});
IGridFilters filters = new GridFilters();
configure(filters);
return(services.AddSingleton(filters));
}
public static IEnumerable <TRow> Select <TKey, TTable, TDataContext, TRow>(
this BaseDataSourceView <TKey, TTable, TDataContext, TRow> view,
StoreReadDataEventArgs e,
BaseFilterControl <TKey, TTable, TDataContext> filter,
GridFilters gridFilter,
BaseGridColumns columns)
where TKey : struct
where TTable : class
where TDataContext : DataContext, new()
where TRow : BaseRow, new()
{
return(Select(view, e, filter, gridFilter, columns, true));
}
public GridFiltersTests()
{
Expression <Func <GridModel, String> > expression = (model) => model.Name;
column = Substitute.For <IGridColumn <GridModel> >();
column.Grid.Query = new NameValueCollection();
column.Expression.Returns(expression);
column.IsMultiFilterable = true;
column.Grid.Name = "Grid";
column.Name = "Name";
filters = new GridFilters();
}
private void AddSearchFilters(GridFilters gridFilters, string search, params string[] searchFields)
{
string[] fieldsToSearch = searchFields;
if (!String.IsNullOrEmpty(search))
{
foreach (var item in fieldsToSearch)
{
gridFilters.Filters.Add(new GridFilter()
{
_value = search, Field = item.Replace("_", ".").ToString(), Operator = "LIKE"
});
}
}
}
public static void ReadData <TKey, TTable, TDataContext, TRow>(
this GridPanel gridPanel,
BaseDataSourceView <TKey, TTable, TDataContext, TRow> view,
StoreReadDataEventArgs e,
BaseFilterControl <TKey, TTable, TDataContext> filter,
GridFilters gridFilter,
BaseGridColumns columns)
where TKey : struct
where TTable : class
where TDataContext : DataContext, new()
where TRow : BaseRow, new()
{
gridPanel.GetStore().ReadData <TKey, TTable, TDataContext, TRow>(view, e, filter, gridFilter, columns);
}
public static void InitializeColumns(this GridFilters gridFilter, IEnumerable <IGridColumn> columns)
{
foreach (var column in columns)
{
if (column.HasFilter)
{
gridFilter.Filters.Add(column.CreateFilter());
}
if (column.HasChildren)
{
gridFilter.InitializeColumns(column.Children);
}
}
}
public string GetDetailGrid(int pageTemplateId, string refKeyValue)
{
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
if (pageTemplate.RefKey2 == 0)
{
return("");
}
GridFilters gridFilters = new GridFilters {
Logic = "and", Filters = new List <GridFilter> {
new GridFilter {
Field = pageTemplate.RefKey2Name, Operator = "eq", Value = refKeyValue
}
}
};
PageTemplate pageTemplate2 = SessionService.PageTemplate(pageTemplate.PageTemplateId2);
List <GridSort> gridSorts = new List <GridSort>();
//xxx fix
//if (pageTemplate2.SortColumns.Length > 2)
//{
// var sortColumns = pageTemplate2.SortColumns;
// if (sortColumns.Contains(","))
// {
// var items = sortColumns.Split(new char[',']);
// foreach (var item in items)
// {
// var columnId = item.Replace(" ", "").Replace("ASC", "").Replace("DESC", "");
// var columnName = SessionService.ColumnName(Convert.ToInt32(columnId));
// GridSort gridSort = new GridSort { Field = columnName, Dir = (item.Contains("ASC")) ? "ASC" : "DESC" };
// gridSorts.Add(gridSort);
// }
// } else
// {
// var columnId = sortColumns.Replace(" ", "").Replace("ASC", "").Replace("DESC", "");
// var columnName = SessionService.ColumnName(Convert.ToInt32(columnId));
// GridSort gridSort = new GridSort { Field = columnName, Dir = (sortColumns.Contains("ASC")) ? "ASC" : "DESC" };
// gridSorts.Add(gridSort);
// }
//}
var json = PageService.GetServerSideRecords(0, 500000, 0, 0, gridSorts, gridFilters, pageTemplate.PageTemplateId2);
return(json);
}
public IQueryable <TEntity> FilterEntity(IQueryable <TEntity> entity,
string orderByDefault,
string orderby,
string sortAscDesc,
int?limit,
int?offset,
dynamic filter,
string search,
out int totalCount,
params string[] searchFields)
{
GridFilters gridFilters = GetGridFilters(filter);
gridFilters.Logic = "and";
AddSearchFilters(gridFilters, search, searchFields);
List <GridSort> gridSort = GetSortBy(sortAscDesc, orderby);
object[] parameters;
string whereClause = GetWhereClause(gridFilters, out parameters);
if (!String.IsNullOrEmpty(whereClause))
{
entity = entity.Where(whereClause, parameters);
}
if (gridSort != null && gridSort.Count > 0)
{
foreach (var s in gridSort)
{
entity = entity.OrderBy(orderby + " " + sortAscDesc);
}
}
else
{
if (!String.IsNullOrEmpty(orderByDefault))
{
entity = entity.OrderBy(orderByDefault);
}
}
var totalEntity = entity;
totalCount = totalEntity.Select(s => 1).Count();
entity = entity.Skip(Convert.ToInt32(offset)).Take(Convert.ToInt32(limit));
return(entity);
}
protected GridResult <TResult> Result <T>(GridFilters <TResult> filters, IQueryable <T> query)
{
var props = typeof(TResult).GetProperties();
var filterExpressions = GenerateFilterExpressions(filters.Filters, props);
var qry = query.ProjectTo <TResult>(_mapperConfiguration);
foreach (var exp in filterExpressions)
{
qry = qry.Where(exp);
}
qry = ApplyOrder(filters, qry);
return(new GridResult <TResult>
{
ItemsCount = qry.Count(),
Data = ApplyPagination(filters, qry)
});
}
public static void ReadData <TKey, TTable, TDataContext, TRow>(
this Store store,
BaseDataSourceView <TKey, TTable, TDataContext, TRow> view,
StoreReadDataEventArgs e,
BaseFilterControl <TKey, TTable, TDataContext> filter,
GridFilters gridFilter,
BaseGridColumns columns)
where TKey : struct
where TTable : class
where TDataContext : DataContext, new()
where TRow : BaseRow, new()
{
if (filter.Url.UserControl == null)
{
filter.Url.UserControl = filter.GetTableName() + "Journal";
}
try
{
var data = view.Select(e, filter, gridFilter, columns, store.RemoteSort);
if (!string.IsNullOrEmpty(store.DataSourceID))
{
store.DataSourceID = null;
}
store.DataSource = data;
}
catch (SqlException exception)
{
if (exception.Number != -2)
{
throw;
}
store.DataSource = new TRow[0];
X.Msg.Show(new MessageBoxConfig
{
Title = Properties.Resources.SInformation,
Message = Properties.Resources.STimeOut,
Buttons = MessageBox.Button.OK,
Icon = MessageBox.Icon.WARNING,
});
}
}
private GridFilters GetGridFilters(dynamic filter)
{
GridFilters gridFilters = new GridFilters();
gridFilters.Filters = new List <GridFilter>();
if (filter != null)
{
var filtersParam = ((IDictionary <String, object>)filter);
if (filtersParam != null)
{
foreach (var item in filtersParam)
{
var values = ((IDictionary <String, object>)item.Value);
foreach (var itemVal in values)
{
var data = (IList <object>)itemVal.Value;
List <string> paramList = new List <string>();
foreach (var dataItem in data)
{
if (dataItem != null)
{
paramList.Add(dataItem.ToString());
}
}
if (paramList.Count > 0)
{
gridFilters.Filters.Add(new GridFilter()
{
_value = String.Join(",", paramList.ToArray()), Field = item.Key.Replace("_", ".").ToString(), Operator = "IN"
});
}
}
}
}
}
return(gridFilters);
}
private void InitComponent(GridPanelInfo panel, List <GridPanelColumnInfo> columnList)
{
if (panel.DisplayRowNumber)
{
RowNumbererColumn stt = new RowNumbererColumn();
stt.Header = "STT";
GridPanel1.ColumnModel.Columns.Add(stt);
}
if (panel.Header == false)
{
GridPanel1.Title = "";
}
else
{
GridPanel1.Title = panel.Title;
}
if (!string.IsNullOrEmpty(panel.AutoExpandColumn))
{
columnList = GridController.GetInstance().GetColumnInfo(this.ID, TableName, 1);
if (columnList.Count() != 0)
{
if (columnList.Where(p => p.ColumnName == panel.AutoExpandColumn).FirstOrDefault() != null)
{
GridPanel1.AutoExpandColumn = panel.AutoExpandColumn;
}
}
else
{
datatable = DataController.DataHandler.GetInstance().ExecuteDataTable("select top 1 * from " + TableName);
foreach (DataColumn item in datatable.Columns)
{
if (item.ColumnName == panel.AutoExpandColumn)
{
GridPanel1.AutoExpandColumn = panel.AutoExpandColumn;
break;
}
}
}
}
this.PrimaryKey = Util.GetInstance().GetPrimaryKeyOfTable(panel.TableName);
// Store1.AutoLoadParams.Add(new Ext.Net.Parameter("limit", panel.PageSize.ToString()));
//sHdfgridName.Value = this.ID.ToString();
//sHdftable.Value = this.TableName;
//sHdfPrimaryKey.Value = panel.PrimaryColumn;
//sHdfwhere.Value = panel.WhereClause.Replace("where", "");
//if (this.Height != 0)
// GridPanel1.Height = this.Height;
//else
// GridPanel1.Height = panel.Height.Value;
//if (this.Width != 0)
// GridPanel1.Width = this.Width;
//else
// GridPanel1.Width = panel.Width.Value;
GenerateListener();
//if (panel.PageSize.HasValue)
// PagingToolbar1.PageSize = panel.PageSize.Value;
//else
// PagingToolbar1.PageSize = 5;
if (!string.IsNullOrEmpty(panel.Icon))
{
GridPanel1.IconCls = panel.Icon;
}
else
{
GridPanel1.Icon = Icon.Table;
}
//set local filter
if (panel.AllowFilter)
{
GridFilters filter = new GridFilters();
filter.Local = true;
filter.FiltersText = "Lọc";
GridPanel1.Plugins.Add(filter);
datatable = DataController.DataHandler.GetInstance().ExecuteDataTable("select top 1 * from " + TableName);
foreach (DataColumn item in datatable.Columns)
{
switch (item.DataType.ToString())
{
case "System.Int32":
NumericFilter numberic = new NumericFilter();
numberic.DataIndex = item.ColumnName;
filter.Filters.Add(numberic);
break;
case "System.Boolean":
BooleanFilter boolean = new BooleanFilter();
boolean.DataIndex = item.ColumnName;
filter.Filters.Add(boolean);
break;
case "System.String":
StringFilter str = new StringFilter();
str.DataIndex = item.ColumnName;
filter.Filters.Add(str);
break;
case "System.DateTime":
DateFilter datefilter = new DateFilter();
datefilter.BeforeText = "Trước ngày";
datefilter.AfterText = "Sau ngày";
datefilter.OnText = "Vào ngày";
datefilter.DataIndex = item.ColumnName;
filter.Filters.Add(datefilter);
break;
}
}
}
}
public void GridFilters_RegistersDefaultFilters(Type type, String name, Type expected)
{
GridFilters filters = new GridFilters();
Type actual = filters.Table[type][name];
Assert.Equal(expected, actual);
}
public JsonResult StudentSearchQuery([FromBody] GridFilters <UserRM> filter) => Json(_gridQueryRunner.Run(filter, new StudentsQueryHandler.Query()));
