public async Task <JqueryDataTablesPagedResults <Demo> > GetDataAsync(JqueryDataTablesParameters table) { IQueryable <DemoEntity> query = _context.Demos .AsNoTracking() .Include(x => x.DemoNestedLevelOne) .ThenInclude(y => y.DemoNestedLevelTwo); query = new SearchOptionsProcessor <Demo, DemoEntity>().Apply(query, table.Columns); query = new SortOptionsProcessor <Demo, DemoEntity>().Apply(query, table); var size = await query.CountAsync(); var items = await query .AsNoTracking() .Skip((table.Start / table.Length) * table.Length) .Take(table.Length) .ProjectTo <Demo>(_mappingConfiguration) .ToArrayAsync(); return(new JqueryDataTablesPagedResults <Demo> { Items = items, TotalSize = size }); }
public async Task <JqueryDataTablesPagedResults <ViewModels.Todo> > GetDataAsync(JqueryDataTablesParameters table) { ViewModels.Todo[] items = null; IQueryable <Todo> query = _context.Todos.Where(d => d.DeletedDate == null).OrderByDescending(d => d.CreatedDate).AsNoTracking(); query = SearchOptionsProcessor <ViewModels.Todo, Todo> .Apply(query, table.Columns); query = SortOptionsProcessor <ViewModels.Todo, Todo> .Apply(query, table); var size = await query.CountAsync(); if (table.Length > 0) { items = await query .Skip((table.Start / table.Length) *table.Length) .Take(table.Length) .ProjectTo <ViewModels.Todo>(_mappingConfiguration) .ToArrayAsync(); } else { items = await query .ProjectTo <ViewModels.Todo>(_mappingConfiguration) .ToArrayAsync(); } return(new JqueryDataTablesPagedResults <ViewModels.Todo> { Items = items, TotalSize = size }); }
public static IQueryable <TEntity> ApplySorting <TModel, TEntity>( this IQueryable <TEntity> query, QueryParameters <TModel> queryParams ) { var processor = new SortOptionsProcessor <TModel>(queryParams.SortOptions.SortTerms); return(processor.Apply <TEntity>(query)); }
/// <summary> /// Service code will call this to apply these sort options to a database query /// </summary> /// <param name="query"></param> /// <returns></returns> public IQueryable <TEntity> Apply(IQueryable <TEntity> query) { //adding sort to a query with linq is simple as // query.OrderBy().ThenByDescending() - this only works if you know the sort criteria at compile time, we need to apply //different sort criteria depending on the query string of the request which is trickier var processor = new SortOptionsProcessor <T, TEntity>(OrderBy); return(processor.Apply(query)); }
public void GetAllTerms_NullOrderBy_ShouldReturnEmptyList() { // Arrange var processor = new SortOptionsProcessor <Recipe>(null); // Types don't matter for this // Act var terms = processor.GetAllTerms(); // Assert Assert.Empty(terms); }
public void ParseQuery(string parameter) { if (parameter == null) { return; } var processor = new SortOptionsProcessor <T>(SortTerms); SortTerms = processor.ParseAllTerms(parameter); }
// ASP.NET Core calls this to validate incoming parameters. public IEnumerable <ValidationResult> Validate(ValidationContext validationContext) { var processor = new SortOptionsProcessor <T, TEntity>(OrderBy); var validTerms = processor.GetValidTerms().Select(x => x.Name); var invalidTerms = processor.GetAllTerms().Select(x => x.Name).Except(validTerms, StringComparer.OrdinalIgnoreCase); foreach (var invalidTerm in invalidTerms) { yield return(new ValidationResult($"Invalid sort term: '{invalidTerm}'", new[] { nameof(OrderBy) })); } }
public async Task <Notification[]> GetDataAsync(DTParameters table) { IQueryable <NotificationEntity> query = _context.Notifications; query = new SearchOptionsProcessor <Notification, NotificationEntity>().Apply(query, table.Columns); query = new SortOptionsProcessor <Notification, NotificationEntity>().Apply(query, table); return(await query .AsNoTracking() .Skip(table.Start) .Take(table.Length) .ProjectTo <Notification>(_mappingConfiguration) .ToArrayAsync()); }
//public SortOptions( AutoMapper.IConfigurationProvider mappingConfig ) //{ // _mappingConfig = mappingConfig; //} public IEnumerable <ValidationResult> Validate(ValidationContext validationContext) { var processor = new SortOptionsProcessor <T, TEntity>(OrderBy); var validTerms = processor.GetValidTerms().Select(x => x.Name); var invalidTerms = processor.GetAllTerms() .Where(x => !validTerms.Any(v => v.Equals(x.Name, StringComparison.OrdinalIgnoreCase))) .Select(x => x.Name); foreach (var term in invalidTerms) { yield return(new ValidationResult($"Invalid sort term {term}", new[] { nameof(OrderBy) })); } }
public async Task <UserViewModel[]> GetUsersAsync(DTParameters tableParams) { IQueryable <UserModel> query = _context.Users; query = new SearchOptionsProcessor <UserViewModel, UserModel>().Apply(query, tableParams.Columns); query = new SortOptionsProcessor <UserViewModel, UserModel>().Apply(query, tableParams); UserViewModel[] users = await query .AsNoTracking() .Skip(tableParams.Start - 1 * tableParams.Length) .Take(tableParams.Length) .ProjectTo <UserViewModel>(_mappingConfiguration) .ToArrayAsync(); return(users); }
public async Task <Demo[]> GetDataAsync(DTParameters table) { IQueryable <DemoEntity> query = _context.Demos; query = new SearchOptionsProcessor <Demo, DemoEntity>().Apply(query, table.Columns); query = new SortOptionsProcessor <Demo, DemoEntity>().Apply(query, table); var items = await query .AsNoTracking() .Skip(table.Start - 1 * table.Length) .Take(table.Length) .ProjectTo <Demo>(_mappingConfiguration) .ToArrayAsync(); return(items); }
public void GetAllTerms_NoSpaces_ShouldAssignToNames() { // Arrange var elements = new[] { "abc", "def", "G_hi_jkl", "m_desc" }; var processor = new SortOptionsProcessor <Recipe>(elements); // Act var terms = processor.GetAllTerms().ToArray(); // Assert Assert.Equal(elements.Length, terms.Length); foreach (var(element, term) in elements.Zip(terms, Tuple.Create)) { Assert.Equal(element, term.Name); } }
private IEnumerable <ValidationResult> ValidateSortOptions() { if (OrderBy == null) { yield break; } var processor = new SortOptionsProcessor <T>(OrderBy); var validTerms = processor.GetValidTerms().Select(x => x.Name); var invalidTerms = processor.GetAllTerms().Select(x => x.Name) .Except(validTerms, StringComparer.OrdinalIgnoreCase); foreach (var term in invalidTerms) { yield return(new ValidationResult($"Invalid sort term '{term}'", new[] { nameof(OrderBy) })); } }
public static async Task <JqueryDataTablesResult <T> > GetJqueryDataTablesResultAsync <T, TEntity>(JqueryDataTablesParameters @params, IQueryable <TEntity> query, IConfigurationProvider configurationProvider) { if (@params == null) { throw new ArgumentNullException($"{nameof(@params)} cannot be null!"); } if (query == null) { throw new ArgumentNullException($"{nameof(query)} cannot be null!"); } query = new SearchOptionsProcessor <T, TEntity>().Apply(query, @params.Columns); query = new SortOptionsProcessor <T, TEntity>().Apply(query, @params); var size = await query.CountAsync(); if (@params.Length <= 0) { @params.Length = size; } var items = await query .Skip((@params.Start / @params.Length) * @params.Length) .Take(@params.Length) .ProjectTo <T>(configurationProvider) .ToArrayAsync(); var results = new JqueryDataTablesPagedResults <T> { Items = items, TotalSize = size }; return(new JqueryDataTablesResult <T> { Draw = @params.Draw, Data = results.Items, RecordsFiltered = results.TotalSize, RecordsTotal = results.TotalSize }); }
public async Task <JsonResult> GetDataAsync <TEntity, TEntityApi>(IQueryable <TEntity> query, JqueryDataTablesParameters param) where TEntity : class { query = SearchOptionsProcessor <TEntityApi, TEntity> .Apply(query, param.Columns); query = SortOptionsProcessor <TEntityApi, TEntity> .Apply(query, param); var items = await query .AsNoTracking() .Skip(param.Start) .Take(param.Length) .ProjectTo <TEntityApi>(_mappingConfiguration) .ToArrayAsync(); return(new JsonResult(new JqueryDataTablesResult <TEntityApi> { Draw = param.Draw, Data = items, RecordsFiltered = items.Length, RecordsTotal = items.Length })); }
public IEnumerable <ValidationResult> Validate(ValidationContext validationContext) { if (SortTerms == null) { yield break; } var processor = new SortOptionsProcessor <T>(SortTerms); var validTerms = processor.GetValidTerms().Select(x => x.Name); var invalidTerms = SortTerms.Select(x => x.Name).Except(validTerms, StringComparer.OrdinalIgnoreCase); foreach (var term in invalidTerms) { yield return(new ValidationResult( $"Invalid sort term '{term}'.", new[] { nameof(SortTerms) } )); } }
public static async Task <JqueryDataTablesExcelResult <T> > GetJqueryDataTablesExcelResultAsync <T, TEntity>(JqueryDataTablesParameters @params, IQueryable <TEntity> query, IConfigurationProvider configurationProvider, bool convertAllData = false, string sheetName = "sheetName", string fileName = "fileName") { if (@params == null) { throw new ArgumentNullException($"{nameof(@params)} cannot be null!"); } if (query == null) { throw new ArgumentNullException($"{nameof(query)} cannot be null!"); } var results = new JqueryDataTablesPagedResults <T>(); if (convertAllData) { var items = await query .ProjectTo <T>(configurationProvider) .ToArrayAsync(); results.Items = items; } else { query = new SearchOptionsProcessor <T, TEntity>().Apply(query, @params.Columns); query = new SortOptionsProcessor <T, TEntity>().Apply(query, @params); var items = await query .Skip((@params.Start / @params.Length) * @params.Length) .Take(@params.Length) .ProjectTo <T>(configurationProvider) .ToArrayAsync(); results.Items = items; } return(new JqueryDataTablesExcelResult <T>(results.Items, sheetName, fileName)); }
// The service code will call this to apply these sort options to a database query public IQueryable <TEntity> Apply(IQueryable <TEntity> query) { var processor = new SortOptionsProcessor <T, TEntity>(OrderBy); return(processor.Apply(query)); }
public async Task <IActionResult> LoadTable([FromBody] JqueryDataTablesParameters param) { try { HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param)); List <Order_MasterViewModel> list = new List <Order_MasterViewModel>(); var showAll = param.AdditionalValues.Last(); string queryDef = ""; if (showAll == "true") { queryDef = @"SELECT Custnum,orders.ordernum,Orderdate,Credit,Freight, Slnum, Slnum2, D0, NAME, Address1, Address2, Address3, Shipname, Shipaddress1, Shipaddress2, Shipaddress3, Terms, Via, Backorder,Tax,Terminal, Ponum,Shippeddate, Shipdate, Canceldate, Edidate, Custnote, clerk, Poammount, Commission, STATUS, D1, D2, Creditmemo, Storenum, Dept, Ordertype, WeborderId, IsOpenOrder, NoteCount,'a' AS ACTION FROM orders LEFT JOIN (SELECT Ordernum,COUNT(*)NoteCount,YEAR FROM ordernotes GROUP BY Ordernum,YEAR ) a ON (SUBSTRING((orders.Ordernum ),1,6)= a.Ordernum AND SUBSTRING((orders.Ordernum ),8,2)= a.Year) "; } else { queryDef = @"SELECT Custnum,orders.ordernum,Orderdate,Credit,Freight, Slnum, Slnum2, D0, NAME, Address1, Address2, Address3, Shipname, Shipaddress1, Shipaddress2, Shipaddress3, Terms, Via, Backorder,Tax,Terminal, Ponum,Shippeddate, Shipdate, Canceldate, Edidate, Custnote, clerk, Poammount, Commission, STATUS, D1, D2, Creditmemo, Storenum, Dept, Ordertype, WeborderId, IsOpenOrder, NoteCount,'a' AS ACTION FROM orders LEFT JOIN (SELECT Ordernum,COUNT(*)NoteCount,YEAR FROM ordernotes GROUP BY Ordernum,YEAR ) a ON (SUBSTRING((orders.Ordernum ),1,6)= a.Ordernum AND SUBSTRING((orders.Ordernum ),8,2)= a.Year) WHERE IsOpenOrder =1 "; } IQueryable <Order> query = _dbContext.tbl_Orders.FromSqlRaw(queryDef).AsNoTracking(); query = SearchOptionsProcessor <Order_MasterViewModel, Order> .Apply(query, param.Columns); query = SortOptionsProcessor <Order_MasterViewModel, Order> .Apply(query, param); var size = await query.CountAsync(); var orderNum = ""; var custnumOrName = ""; var Shipdate = ""; DateTime ShipDateD = DateTime.Now; var i = 0; var additionalValues = param.AdditionalValues.SkipLast(1); foreach (var column in additionalValues) { if (i == 0) { orderNum = column; } else if (i == 1) { custnumOrName = column; } else { Shipdate = column; if (Convert.ToString(Shipdate) == "1/1/0001 12:00:00 AM") { Shipdate = ""; } if (Shipdate != "") { DateTime.TryParse(Shipdate, out ShipDateD); } } i++; } if (orderNum != "" || custnumOrName != "" || Shipdate != "") { query = query.Where(w => ((orderNum == "" ? true : w.ordernum.Contains(orderNum))) && ((custnumOrName == "" ? true : (w.Custnum.Contains(custnumOrName) ? true : (w.Name.Contains(custnumOrName))))) && ((Shipdate == "" ? true : w.Shipdate == ShipDateD))); } foreach (var column in param.Columns) { if (column.Data == "ordernum") { column.Searchable = true; column.Search.Value = orderNum; } if (column.Data == "Shipdate") { column.Searchable = true; column.Search.Value = orderNum; } if (column.Data == "Shipdate") { column.Searchable = true; column.Search.Value = orderNum; } } var items = query //.AsNoTracking() .Skip((param.Start / param.Length) * param.Length) .Take(param.Length) .ProjectTo <Order_MasterViewModel>(_mappingConfiguration) .ToArray(); var result = new JqueryDataTablesPagedResults <Order_MasterViewModel> { Items = items, TotalSize = size }; return(new JsonResult(new JqueryDataTablesResult <Order_MasterViewModel> { Draw = param.Draw, Data = result.Items, RecordsFiltered = result.TotalSize, RecordsTotal = result.TotalSize })); } catch (Exception e) { Console.Write(e.Message); return(new JsonResult(new { error = "Internal Server Error" })); } }
public async Task <IActionResult> LoadTable([FromBody] JqueryDataTablesParameters param) { try { HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param)); IQueryable <EmployeeViewModel_datatable> employees; string queryDef = "call get_careGiverList()"; DbfunctionUtility dbfunction = new DbfunctionUtility(_appSettings); CommanUtility _commanUtility = new CommanUtility(_appSettings); DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); int i = 0; bool isDateFilter = false; DataSet ds = new DataSet(); foreach (var column in param.AdditionalValues) { if (Convert.ToString(column) != "") { isDateFilter = true; var dateParts = column.Split("/"); // var date = dateParts[1] + "/" + dateParts[0] + "/" + dateParts[2]; if (i == 0) { DateTime.TryParse(column, out startDate); } else if (i == 1) { DateTime.TryParse(column, out endDate); } } i++; } if (isDateFilter == false) { ds = dbfunction.GetDataset(@"call get_careGiverList()"); } else { ds = dbfunction.GetDataset(@"call get_careGiverListByDate('" + startDate.ToString("yyyy/MM/dd") + "','" + endDate.ToString("yyyy/MM/dd") + "') "); } employees = (from row in ds.Tables[0].AsEnumerable() select new EmployeeViewModel_datatable { EmployeeId = Convert.ToInt32(row["Id"]), FirstName = Convert.ToString(row["FirstName"]), MiddleName = Convert.ToString(row["MiddleName"]), LastName = Convert.ToString(row["LastName"]), Email = Convert.ToString(row["Email"]), EmployeeNo = Convert.ToString(row["EmployeeNo"]), UserId = Convert.ToInt32(row["UserId"]), HrGroupName = Convert.ToString(row["HrGroup"]), HrGroupId = Convert.ToInt32(row["HrGroupId"]), PassedTest = Convert.ToInt32(row["PassedTest"]), Totaltest = Convert.ToInt32(row["total_tests_1"]), VideoDuration = Convert.ToString(row["VideoDuration"]) == "" ? (Decimal?)null : Convert.ToDecimal(row["VideoDuration"]), ExamDate = Convert.ToString(row["ExamDate"]) == "" ? (DateTime?)null : Convert.ToDateTime(row["ExamDate"]), ValidEmail = _commanUtility.CheckValidEmail(Convert.ToString(row["Email"])) }).AsQueryable(); if (_rolename.ToLower() == "hr") { employees = employees.Where(w => _HrGroupId.Contains("/" + w.HrGroupId.ToString() + "/")).AsQueryable(); } employees = SearchOptionsProcessor <EmployeeViewModel_datatable, EmployeeViewModel_datatable> .Apply(employees, param.Columns); employees = SortOptionsProcessor <EmployeeViewModel_datatable, EmployeeViewModel_datatable> .Apply(employees, param); var size = employees.Count(); if (Convert.ToString(param.Search?.Value) != "") { var serchValue = param.Search?.Value.ToLower(); employees = employees.Where(w => (w.FirstName.ToLower().Contains(serchValue) ? true : (w.MiddleName.ToLower().Contains(serchValue) ? true : ((w.LastName.ToLower().Contains(serchValue) ? true : ((w.EmployeeNo.ToLower().Contains(serchValue) ? true : ((w.Email.ToLower().Contains(serchValue) ? true : ((w.HrGroupName.ToLower().Contains(serchValue) ? true : false)))))))) ))); } var items = employees .Skip((param.Start / param.Length) * param.Length) .Take(param.Length) .ProjectTo <EmployeeViewModel_datatable>(_mappingConfiguration) .ToArray(); var result = new JqueryDataTablesPagedResults <EmployeeViewModel_datatable> { Items = items, TotalSize = size }; return(new JsonResult(new JqueryDataTablesResult <EmployeeViewModel_datatable> { Draw = param.Draw, Data = result.Items, RecordsFiltered = result.TotalSize, RecordsTotal = result.TotalSize })); } catch (Exception e) { Console.Write(e.Message); return(new JsonResult(new { error = "Internal Server Error" })); } }
public async Task <IActionResult> LoadTable([FromBody] JqueryDataTablesParameters param) { try { HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param)); List <Order_MasterViewModel> list = new List <Order_MasterViewModel>(); IQueryable <Invoice> query = _dbContext.tbl_Invoice.FromSqlRaw("select custNum ,invoiceNum , cast( releaseDate as char) releaseDate,freight ,salesManNum ,salesManNum2 ,billName ,billAddress1 ,billAddress2 ,billAddress3 ,shipName ,shipAddress1,shipAddress2,shipAddress3,termId ,carrierCode ,d2 ,poNum , cast( orderDate as char) orderDate, cast( shipDate as char) shipDate, CAST( cancelDate AS CHAR) cancelDate,cast( enterDate as char) enterDate,terminal ,custNote ,clerk ,netTotal ,commision ,d4 ,d6 ,storeNum ,dept ,orderType ,bolNum ,cast( bolDate as char) bolDate ,tracking1 ,tracking2 ,orderNum ,cast( invoiceDate as char) invoiceDate ,isCreditMemo ,isCreditHold ,isFreightCollect from v_Invoice").AsNoTracking(); query = SearchOptionsProcessor <InvoiceViewModel, Invoice> .Apply(query, param.Columns); query = SortOptionsProcessor <InvoiceViewModel, Invoice> .Apply(query, param); var size = await query.CountAsync(); var InvoiceNum = ""; var OrderNum = ""; var BillName = ""; var i = 0; foreach (var column in param.AdditionalValues) { if (i == 0) { InvoiceNum = column; } else if (i == 1) { OrderNum = column; } else { BillName = column; } i++; } if (InvoiceNum != "" || OrderNum != "" || BillName != "") { query = query.Where(w => ((InvoiceNum == "" ? true : w.InvoiceNum.Contains(InvoiceNum))) && ((OrderNum == "" ? true : w.InvoiceNum.Contains(OrderNum))) && ((BillName == "" ? true : w.BillName.Contains(BillName)))); } foreach (var column in param.Columns) { if (column.Data == "InvoiceNum") { column.Searchable = true; column.Search.Value = InvoiceNum; } if (column.Data == "OrderNum") { column.Searchable = true; column.Search.Value = OrderNum; } if (column.Data == "BillName") { column.Searchable = true; column.Search.Value = BillName; } } var items = await query .AsNoTracking() .Skip((param.Start / param.Length) * param.Length) .Take(param.Length) .ProjectTo <InvoiceViewModel>(_mappingConfiguration) .ToArrayAsync(); var result = new JqueryDataTablesPagedResults <InvoiceViewModel> { Items = items, TotalSize = size }; return(new JsonResult(new JqueryDataTablesResult <InvoiceViewModel> { Draw = param.Draw, Data = result.Items, RecordsFiltered = result.TotalSize, RecordsTotal = result.TotalSize })); } catch (Exception e) { Console.Write(e.Message); return(new JsonResult(new { error = "Internal Server Error" })); } }