public IActionResult Export([FromQuery] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; string orderCriteria; bool orderAscendingDirection; if (dtParameters.Order != null) { orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { orderCriteria = "lastFunctionalityTestDate"; orderAscendingDirection = true; } var result = _proxyService.GetProxiesForExport(orderCriteria, orderAscendingDirection, searchBy); try { StringBuilder stringBuilder = new StringBuilder(); foreach (var proxy in result) { stringBuilder.AppendLine($"{proxy.Ip}:{ proxy.Port}"); } return(File(Encoding.UTF8.GetBytes(stringBuilder.ToString()), "text/csv", "proxies.csv")); } catch { return(Json(HttpStatusCode.ServiceUnavailable)); } }
public IActionResult LoadTable([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; string orderCriteria; bool orderAscendingDirection; if (dtParameters.Order != null) { orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { orderCriteria = "lastFunctionalityTestDate"; orderAscendingDirection = true; } var result = _proxyService.GetPagedProxies(dtParameters.Start, dtParameters.Length, orderCriteria, orderAscendingDirection, searchBy); return(Json(new { recordsTotal = result.Total, recordsFiltered = result.FilteredCount, data = result.Result })); }
public async Task <DtResult <TVm> > Query <TE>(IQueryable <TE> queryable, DtParameters parameters) where TE : class, IEntity { EnsureValidColumns(parameters); var result = new DtResult <TVm> { Draw = parameters.Draw }; var query = queryable; result.RecordsTotal = await query.CountAsync(); result.RecordsFiltered = result.RecordsTotal; query = ChainOrder(query, parameters); query = ChainFilter(query, parameters, out var isFiltered); if (isFiltered) { result.RecordsFiltered = await query.CountAsync(); } query = ChainPagination(query, parameters); var entities = await query.ToListAsync(); result.Data = _mapper.Map <List <TVm> >(entities); return(result); }
private IQueryable <TE> ChainOrder <TE>(IQueryable <TE> query, DtParameters parameters) where TE : class, IEntity { if (parameters.Order.Count == 0) { if (!AlreadyOrdered) { query = query.OrderBy(e => e.Updated); } } else { foreach (var dtOrder in parameters.Order) { var dbColumn = GetDbColumn(parameters.Columns[dtOrder.Column]); var qStr = $"x.{dbColumn.DbColumn}"; if (!string.IsNullOrEmpty(dbColumn.DbFuncFormat)) { qStr = string.Format(dbColumn.DbFuncFormat, qStr); } query = dtOrder.Dir == DtOrderDir.Asc ? query.OrderByDynamic(x => qStr) : query.OrderByDescendingDynamic(x => qStr); } } return(query); }
internal static string GetOrderStringFromDtParams(string defaultOrder, DtParameters dtParameters, Dictionary <string, string> replacementProperties = null) { string order = defaultOrder; if (dtParameters.Order != null && dtParameters.Order.Length > 0) { StringBuilder sb = new StringBuilder(); foreach (var dtOrder in dtParameters.Order) { string fieldName = dtParameters.Columns[dtOrder.Column].Data; if (replacementProperties != null && replacementProperties.Any(r => r.Key.ToLower() == fieldName.ToLower())) { fieldName = replacementProperties.First(r => r.Key.ToLower() == fieldName.ToLower()).Value; } sb.Append($"{fieldName} {dtOrder.Dir},"); } //order = sb.ToString(); if (order.EndsWith(",")) { order = order.Substring(0, order.Length - 1); } } return(order); }
public async Task <IActionResult> LoadTable([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; // if we have an empty search then just order the results by Id ascending var orderCriteria = "Id"; var orderAscendingDirection = DtOrderDir.Asc; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc" ? DtOrderDir.Asc : DtOrderDir.Desc; } var result = _context.TestRegisters .WhereDynamic(searchBy) .OrderByDynamic(orderCriteria, orderAscendingDirection); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var totalResultsCount = await _context.TestRegisters.CountAsync(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); }
public async Task <IActionResult> GetAllCustomerByFilter(DtParameters dtParameters) { var sortColumnName = "CustomerId"; var orderAscendingDirection = true; if (dtParameters.Order != null) { sortColumnName = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } var firstName = Request.Form["columns[1][search][value]"].FirstOrDefault(); var lastName = Request.Form["columns[2][search][value]"].FirstOrDefault(); var fullName = Request.Form["columns[3][search][value]"].FirstOrDefault(); var mobile = Request.Form["columns[4][search][value]"].FirstOrDefault(); var customersResult = await _uow.Customers.GetAllCustomerAsync(dtParameters.Start, dtParameters.Length, sortColumnName, orderAscendingDirection, firstName, lastName, fullName, mobile); var customersModel = _mapper.Map <List <CustomerForListViewModel> >(customersResult.Data); return(Json(new DtResult <CustomerForListViewModel> { Data = customersModel, Draw = dtParameters.Draw, RecordsFiltered = customersResult.TotalFilteredCount, RecordsTotal = customersResult.TotalCount })); }
public IActionResult LoadUserList([FromBody] DtParameters parameters) { var searchBy = parameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (parameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = "CreatedAt"; orderAscendingDirection = parameters.Order[0].Dir.ToString().ToLower() != "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "CreatedAt"; orderAscendingDirection = false; } var result = _user.Users.Where(x => x.IsAdmin == false); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) || r.Email != null && r.Email.ToUpper().Contains(searchBy.ToUpper()) ); } result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc) : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var cntdb = _user.Users.Where(x => x.IsAdmin == false); var totalResultsCount = cntdb.Count(); return(Json(new { draw = parameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(parameters.Start) .Take(parameters.Length) .AsEnumerable().Select(x => new ApplicationUser() { Id = x.Id, Name = x.Name, Email = x.Email, PhoneNumber = x.PhoneNumber, UserName = x.UserName, gender = x.gender, age = x.age, createdAt = x.Id.CreationTime }).ToList() })); }
public async Task <IActionResult> ExportTable([FromQuery] string format, [FromForm] string dtParametersJson) { var dtParameters = new DtParameters(); if (!string.IsNullOrEmpty(dtParametersJson)) { dtParameters = JsonConvert.DeserializeObject <DtParameters>(dtParametersJson); } var searchBy = dtParameters.Search?.Value; // if we have an empty search then just order the results by Id ascending var orderCriteria = "Id"; var orderAscendingDirection = true; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } var result = _context.TestRegisters.AsQueryable(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) || r.FirstSurname != null && r.FirstSurname.ToUpper().Contains(searchBy.ToUpper()) || r.SecondSurname != null && r.SecondSurname.ToUpper().Contains(searchBy.ToUpper()) || r.Street != null && r.Street.ToUpper().Contains(searchBy.ToUpper()) || r.Phone != null && r.Phone.ToUpper().Contains(searchBy.ToUpper()) || r.ZipCode != null && r.ZipCode.ToUpper().Contains(searchBy.ToUpper()) || r.Country != null && r.Country.ToUpper().Contains(searchBy.ToUpper()) || r.Notes != null && r.Notes.ToUpper().Contains(searchBy.ToUpper())); } result = orderAscendingDirection ? result.OrderByDynamic(orderCriteria, DtOrderDir.Asc) : result.OrderByDynamic(orderCriteria, DtOrderDir.Desc); var resultList = await result.ToListAsync(); byte[] dataByteArray; switch (format) { case ExportFormat.Excel: dataByteArray = await _exportService.ExportToExcel(resultList); return(File( dataByteArray, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "data.xlsx")); } return(null); }
public async Task <IActionResult> LoadLogs([FromBody] DtParameters parameters) { var searchBy = parameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (parameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = "CreatedAt"; orderAscendingDirection = parameters.Order[0].Dir.ToString().ToLower() != "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "CreatedAt"; orderAscendingDirection = false; } var result = HttpContext.User.IsInRole("Admin") ? _log.AsQueryable() : _log.AsQueryable().Where(x => x.userId == ObjectId.Parse(HttpContext.User.Identity.Name)); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.events != null && r.events.ToUpper().Contains(searchBy.ToUpper()) || r.message != null && r.message.ToUpper().Contains(searchBy.ToUpper()) ); } result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc) : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var cntdb = HttpContext.User.IsInRole("Admin") ? _log.AsQueryable() : _log.AsQueryable().Where(x => x.userId == ObjectId.Parse(HttpContext.User.Identity.Name)); var u = _user.Users.AsEnumerable(); var totalResultsCount = cntdb.Count(); return(Json(new { draw = parameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(parameters.Start) .Take(parameters.Length) .AsEnumerable().Select(x => new LogModel() { userId = u.SingleOrDefault(s => s.Id == x.userId).UserName, message = x.message, events = x.events, createdAt = x.Id.CreationTime }).ToList() })); }
public virtual async Task <ActionResult <DtResult <TVm> > > DtQuery([FromBody][Required] DtParameters model) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } var result = await QueryService.Query(Repo, model); return(Ok(result)); }
public async Task <IActionResult> GetAllTransactionByFilter(DtParameters dtParameters, string fullName) { var sortColumnName = "TransactionId"; var orderAscendingDirection = true; if (dtParameters.Order != null) { sortColumnName = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } var firstName = Request.Form["columns[1][search][value]"].FirstOrDefault(); var lastName = Request.Form["columns[2][search][value]"].FirstOrDefault(); if (!string.IsNullOrEmpty(Request.Form["columns[3][search][value]"].FirstOrDefault())) { fullName = Request.Form["columns[3][search][value]"].FirstOrDefault(); } var mobile = Request.Form["columns[4][search][value]"].FirstOrDefault(); var fromDate = Request.Form["columns[5][search][value]"].FirstOrDefault(); var toDate = Request.Form["columns[6][search][value]"].FirstOrDefault(); DateTime?vFromDate = null; DateTime?vToDate = null; if (!string.IsNullOrEmpty(fromDate) && string.IsNullOrEmpty(toDate)) { vFromDate = Convert.ToDateTime(fromDate).AddHours(00).AddMinutes(00).AddSeconds(00); vToDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59); } else if (string.IsNullOrEmpty(fromDate) && !string.IsNullOrEmpty(toDate)) { vFromDate = new DateTime(DateTime.Now.Year, 01, 01, 00, 00, 00); vToDate = Convert.ToDateTime(toDate).AddHours(23).AddMinutes(59).AddSeconds(59); } else if (!string.IsNullOrEmpty(fromDate) && !string.IsNullOrEmpty(toDate)) { vFromDate = Convert.ToDateTime(fromDate).AddHours(00).AddMinutes(00).AddSeconds(00); vToDate = Convert.ToDateTime(toDate).AddHours(23).AddMinutes(59).AddSeconds(59); } var customerTransactionsResult = await _uow.CustomerTransactions.GetAllCustomerTransactionAsync(dtParameters.Start, dtParameters.Length, sortColumnName, orderAscendingDirection, firstName, lastName, fullName, mobile, vFromDate, vToDate); var customerTransactionsModel = _mapper.Map <List <CustomerTransactionForListViewModel> >(customerTransactionsResult.Data); return(Json(new DtResult <CustomerTransactionForListViewModel> { Data = customerTransactionsModel, Draw = dtParameters.Draw, RecordsFiltered = customerTransactionsResult.TotalFilteredCount, RecordsTotal = customerTransactionsResult.TotalCount })); }
private IQueryable <TE> ChainFilter <TE>(IQueryable <TE> query, DtParameters parameters, out bool isFiltered) { isFiltered = false; var cols = parameters.Columns.Where(IsServerSideFilterable).ToList(); if (parameters.Search.HasValue) { var globalSearchCols = cols.Where(IsGlobalServerSideFilterable) .Select(c => c.CloneForGlobalSearch(parameters.Search)).ToList(); var gFilters = BuildFilters(globalSearchCols); if (gFilters.Any()) { var qStr = string.Join(" || ", gFilters.Select(gf => gf.Query)); try { query = query.WhereDynamic(x => qStr, gFilters.First().Params); _logger.LogInformation("queryStr= {Query}\nparams={Params}", qStr, JsonConvert.SerializeObject(gFilters.First().Params)); } catch { _logger.LogError("queryStr: {Query}", qStr); _logger.LogError("params: {Params}", JsonConvert.SerializeObject(gFilters.First().Params)); throw; } isFiltered = true; } } var filters = BuildFilters(cols.Where(col => col?.Search?.HasValue == true)); foreach (var qCond in filters) { try { query = query.WhereDynamic(x => qCond.Query, qCond.Params); _logger.LogInformation("queryStr= {Query}\nparams={Params}", qCond.Query, JsonConvert.SerializeObject(qCond.Params)); } catch { _logger.LogError("queryStr: {Query}", qCond.Query); _logger.LogError("params: {Params}", JsonConvert.SerializeObject(qCond.Params)); throw; } } isFiltered = isFiltered || filters.Any(); return(query); }
public string GetTests() { //get the parameters from the Datatable var dtParams = new DtParameters(Request); var entities = _rep.GetTests(); int totalRecords = entities.Count(); if (dtParams.IsBeingSearched) { entities = entities.Where(e => e.Name.ToLower().Contains(dtParams.SearchValue) || e.TestType.ShortName.ToLower().Contains(dtParams.SearchValue) || e.TestCategory.Name.ToLower().Contains(dtParams.SearchValue)); } int filteredRecords = entities.Count(); if (dtParams.IsBeingFiltered) { entities = Filter(entities, dtParams.FilterColIndex, dtParams.FilterAsc); } else { //defaultOrder entities = entities.OrderBy(c => c.TestCategory.Name) .ThenBy(c => c.TestType.ShortName) .ThenBy(c => c.Name) .ThenBy(c => c.AcredetationLevel.Level); } var data = entities.Skip(dtParams.Skip).Take(dtParams.PageSize) .ToList().Select(t => new TestVM { TestType = t.TestType.ShortName, Name = t.Name, Level = t.AcredetationLevel.Level, UnitName = t.UnitName, Temperature = t.Temperature, Category = t.TestCategory.Name, Id = t.Id }); var jsonResult = new JqueryListResult <TestVM>( data, dtParams.Draw, filteredRecords, totalRecords); var json = JsonConvert.SerializeObject(jsonResult); return(json); }
public async Task <IActionResult> LoadTable([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "Id"; orderAscendingDirection = true; } var result = await _context.TestRegisters.ToListAsync(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) || r.FirstSurname != null && r.FirstSurname.ToUpper().Contains(searchBy.ToUpper()) || r.SecondSurname != null && r.SecondSurname.ToUpper().Contains(searchBy.ToUpper()) || r.Street != null && r.Street.ToUpper().Contains(searchBy.ToUpper()) || r.Phone != null && r.Phone.ToUpper().Contains(searchBy.ToUpper()) || r.ZipCode != null && r.ZipCode.ToUpper().Contains(searchBy.ToUpper()) || r.Country != null && r.Country.ToUpper().Contains(searchBy.ToUpper()) || r.Notes != null && r.Notes.ToUpper().Contains(searchBy.ToUpper())) .ToList(); } result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList(); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var totalResultsCount = await _context.TestRegisters.CountAsync(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); }
public virtual async Task <ActionResult <DtResult <UserViewModel> > > DtQuery( [FromBody][Required] DtParameters model) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } Repo.ChainQueryable(q => q.Include(u => u.UserRoles).ThenInclude(ur => ur.Role)); var result = await QueryService.Query(Repo, model); return(Ok(result)); }
public async Task <IActionResult> Listar([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "nombres"; orderAscendingDirection = true; } var result = await _usuarioBaseBL.ObtenerObjetos(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.Nombres != null && r.Nombres.ToUpper().Contains(searchBy.ToUpper()) || r.Apellidos != null && r.Apellidos.ToUpper().Contains(searchBy.ToUpper()) || r.Dui != null && r.Dui.ToUpper().Contains(searchBy.ToUpper()) || r.Nit != null && r.Nit.ToUpper().Contains(searchBy.ToUpper()) || r.Isss != null && r.Isss.ToUpper().Contains(searchBy.ToUpper())).ToList(); } //result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList(); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var totalResultsCount = result.Count(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); }
private IQueryable <TE> ChainPagination <TE>(IQueryable <TE> query, DtParameters parameters) where TE : class, IEntity { if (parameters.Start > 0) { query = query.Skip(parameters.Start); } if (parameters.Length > 0) { query = query.Take(parameters.Length); } return(query); }
public async Task <IActionResult> LoadTable([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (dtParameters.Order != null) { orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { orderCriteria = "Id"; orderAscendingDirection = true; } var result = await _context.Employees.ToListAsync(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) || r.FirstSurname != null && r.FirstSurname.ToUpper().Contains(searchBy.ToUpper()) || r.SecondSurname != null && r.SecondSurname.ToUpper().Contains(searchBy.ToUpper()) || r.Street != null && r.Street.ToUpper().Contains(searchBy.ToUpper()) || r.Phone != null && r.Phone.ToUpper().Contains(searchBy.ToUpper()) || r.ZipCode != null && r.ZipCode.ToUpper().Contains(searchBy.ToUpper()) || r.Country != null && r.Country.ToUpper().Contains(searchBy.ToUpper()) || r.Notes != null && r.Notes.ToUpper().Contains(searchBy.ToUpper())) .ToList(); } result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList(); var filteredResultsCount = result.Count(); var totalResultsCount = await _context.Employees.CountAsync(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); }
/// <summary> /// Get all notes /// </summary> /// <returns></returns> public async Task <ActionResult> LoadNotes(DtParameters dtParameters) { try { var searchBy = dtParameters.Search?.Value; var orderCriteria = "NotificationId"; var orderAscendingDirection = false; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } var notes = await _masterService.GetAllNotes(Convert.ToInt32(SessionHelper.UserId), Convert.ToInt32(SessionHelper.UserTypeId)); var totalResultsCount = notes.Count(); if (!string.IsNullOrEmpty(searchBy)) { notes = notes.Where(r => r.Message != null && r.Message.ToUpper().Contains(searchBy.ToUpper())); } notes = orderAscendingDirection ? notes.OrderByDynamic(orderCriteria.Replace("String", ""), DtOrderDir.Asc) : notes.OrderByDynamic(orderCriteria.Replace("String", ""), DtOrderDir.Desc); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = notes.Count(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = notes .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); } catch { throw; } }
public async Task <IActionResult> LoadMedicalData([FromBody] DtParameters parameters) { var searchBy = parameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (parameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = "CreatedAt"; orderAscendingDirection = parameters.Order[0].Dir.ToString().ToLower() != "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "CreatedAt"; orderAscendingDirection = false; } var result = _device.AsQueryable(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.name != null && r.name.ToUpper().Contains(searchBy.ToUpper()) || r.userId != null && r.userId.ToUpper().Contains(searchBy.ToUpper()) ); } result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc) : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); var cntdb = _device.AsQueryable(); var totalResultsCount = cntdb.Count(); return(Json(new { draw = parameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(parameters.Start) .Take(parameters.Length).ToList() })); }
public string GetClients() { //get the parameters from the Datatable var dtParams = new DtParameters(Request); var entities = _rep.GetClients(); int totalRecords = entities.Count(); if (dtParams.IsBeingSearched) { entities = entities.Where(e => e.Name.ToLower().Contains(dtParams.SearchValue) || (e.Mobile != null ? e.Mobile.ToLower().Contains(dtParams.SearchValue) : false)); } int filteredRecords = entities.Count(); if (dtParams.IsBeingFiltered) { entities = Filter(entities, dtParams.FilterColIndex, dtParams.FilterAsc); } else { //defaultOrder entities = entities.OrderBy(e => e.Name); } var data = entities.Skip(dtParams.Skip).Take(dtParams.PageSize) .ToList().Select(c => new ClientW { Name = c.Name, Mobile = c.Mobile, Id = c.Id }); var jsonResult = new JqueryListResult <ClientW>( data, dtParams.Draw, filteredRecords, totalRecords); var json = JsonConvert.SerializeObject(jsonResult); return(json); }
protected void EnsureValidColumns(DtParameters parameters) { foreach (var dtColumn in parameters.Columns.Where(dtc => dtc.Searchable)) { if (dtColumn.Search.HasValue && !IsServerSideFilterable(dtColumn)) { throw new KnownException($"Invalid search operation on column '{dtColumn.Data}'"); } } foreach (var dtOrder in parameters.Order) { if (dtOrder.Column >= parameters.Columns.Count || !IsServerSideSortable(parameters.Columns[dtOrder.Column])) { throw new KnownException($"Invalid order operation on column '{dtOrder.Column}'"); } } }
public async Task <IActionResult> LoadListData([FromBody] DtParameters dtParameters) { var searchBy = dtParameters.Search?.Value; // if we have an empty search then just order the results by Id ascending var orderCriteria = "OrderId"; var orderAscendingDirection = true; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } InputParameter param = new InputParameter(); param.SearchValue = searchBy; param.PageNo = (dtParameters.Start / dtParameters.Length) + 1; param.PageSize = dtParameters.Length; param.SortColumn = orderCriteria; param.SortOrder = dtParameters.Order[0].Dir.ToString().ToLower(); param.FromDate = dtParameters.FromDate.HasValue ? dtParameters.FromDate.Value.ToString("yyyy-MM-dd") : string.Empty; param.ToDate = dtParameters.ToDate.HasValue ? dtParameters.ToDate.Value.ToString("yyyy-MM-dd") : string.Empty; param.OrderStatusType = dtParameters.Type.HasValue ? dtParameters.Type.Value : 1; List <DtOrderModel> orderList = orderService.GetOrderListBySP(param); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering int totalResultsCount = 0; int filteredResultsCount = 0; if (orderList.Count > 0) { totalResultsCount = orderList[0].TotalCount; filteredResultsCount = orderList[0].TotalCount; } return(Json(new DtResult <DtOrderModel> { Draw = Converters.ConvertInt(dtParameters.Draw), RecordsTotal = totalResultsCount, RecordsFiltered = filteredResultsCount, Data = orderList })); }
public SortOptionArgs ComposeSort(DtParameters dtParameters) { var sort = new SortOptionArgs(); var translatableProperty = dtParameters.SortColumn.Split('.'); if (translatableProperty.Length == 2) { sort.ParentPropertyName = translatableProperty[0]; sort.PropertyName = translatableProperty[1]; } else { sort.PropertyName = dtParameters.SortColumn; } sort.SortOrder = string.IsNullOrWhiteSpace(dtParameters.SortOrder) || dtParameters.SortOrder == "asc" ? SortOrder.Ascending : SortOrder.Descending; return(sort); }
public async Task <IActionResult> GetProducts([FromBody] DtParameters dtParameters, int categoryId) { var orderString = DataTablesHelper.GetOrderStringFromDtParams(nameof(ProductViewModel.Id), dtParameters, new Dictionary <string, string>() { { nameof(ProductViewModel.Id), nameof(ProductViewModel.Id) } }); int totalCount = 0; var products = _productService.GetProducts(categoryId, Constants.PageSize, dtParameters.Start, orderString, out totalCount); var productsViewModelList = new List <ProductViewModel>(); products.ForEach(p => { var viewModel = mapper.Map <Product, ProductViewModel>(p); viewModel.CategoryNameEn = p.Category.NameEn; viewModel.CategoryNameAr = p.Category.NameAr; productsViewModelList.Add(viewModel); }); var data = new DataTablesResponseViewModel(dtParameters.Draw, productsViewModelList, totalCount, totalCount); return(Json(data)); }
public async Task <IActionResult> SearchProductsAsync([FromBody] DtParameters dtParameters) { var searchArgs = new SearchArgs { Offset = dtParameters.Start, Limit = dtParameters.Length, SearchText = dtParameters.Search?.Value, FiltersCriteria = dtParameters.FiltersCriteria, SortOptions = new List <SortOptionArgs>() { ComposeSort(dtParameters) } }; var products = await _productSearchService.SearchProducts(searchArgs); return(new JsonResult(new DtResult <SearchableProduct> { Draw = dtParameters.Draw, RecordsTotal = products.Count, RecordsFiltered = products.Count, Data = products.Values })); }
public async Task <ActionResult> LoadTask(DtParameters dtParameters) { try { var searchBy = dtParameters.Search?.Value; // if we have an empty search then just order the results by Id ascending var orderCriteria = "TaskId"; var orderAscendingDirection = false; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } var result = await _taskService.GetAll(); var totalResultsCount = result.Count(); if (!string.IsNullOrEmpty(searchBy)) { result = result.Where(r => r.UserComment != null && r.UserComment.ToUpper().Contains(searchBy.ToUpper()) || r.Description != null && r.Description.ToUpper().Contains(searchBy.ToUpper()) || r.TaskName != null && r.TaskName.ToUpper().Contains(searchBy.ToUpper()) || //r.DueDate != null && r.DueDate.Value.ToString("dd/MM/yyyy").Contains(searchBy.ToUpper()) || //r.CreatedBy != null && r.CreatedByString.ToUpper().Contains(searchBy.ToUpper()) || //r.CreatedOn != null && r.CreatedOn.ToString("dd/MM/yyyy").Contains(searchBy.ToUpper()) || r.UserName != null && r.UserName.ToUpper().Contains(searchBy.ToUpper()) || //r.PrioritiesString != null && r.PrioritiesString.ToUpper().Contains(searchBy.ToUpper()) || r.ClientName != null && r.ClientName.ToUpper().Contains(searchBy.ToUpper()) || r.ChargeAmount != null && r.ChargeAmount.Value.ToString().Contains(searchBy.ToUpper()) || r.IsChargeble.ToString().Contains(searchBy.ToUpper()) //r.StatusString != null && r.StatusString.ToUpper().Contains(searchBy.ToUpper()) ); } result = orderAscendingDirection ? result.OrderByDynamic(orderCriteria.Replace("String", ""), DtOrderDir.Asc) : result.OrderByDynamic(orderCriteria.Replace("String", ""), DtOrderDir.Desc); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = result.Count(); return(Json(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = result .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList() })); //return Json(new DtResult<TaskDTO> //{ // Draw = dtParameters.Draw, // RecordsTotal = totalResultsCount, // RecordsFiltered = filteredResultsCount, // Data = result // .Skip(dtParameters.Start) // .Take(dtParameters.Length) // .ToList() //}); } catch (Exception ex) { return(Json(new { draw = 0, recordsTotal = 0, recordsFiltered = 0, data = new List <Tasks>() }, JsonRequestBehavior.AllowGet)); } }
public ActionResult <IEnumerable <Log> > getLogPage( [FromQuery] string pasta, [FromQuery] string contrato, [FromQuery] string tipoContrato, [FromBody] DtParameters dtParameters, [FromQuery] int pageSize = 10, [FromQuery] int pageNumber = 0, [FromQuery] int draw = 1, [FromQuery] bool getAll = false, [FromQuery] bool recuperacaoJudicial = false) { if (dtParameters.Order != null) { IQueryable <Log> _logListByParameter = _context.LogItems; var searchBy = dtParameters.Search?.Value; var orderCriteria = string.Empty; var orderAscendingDirection = true; if (dtParameters.Order != null) { // in this example we just default sort on the 1st column orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data; orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc"; } else { // if we have an empty search then just order the results by Id ascending orderCriteria = "Id"; orderAscendingDirection = true; } if (!string.IsNullOrEmpty(searchBy)) { _logListByParameter = _logListByParameter.Where(r => r.Id >= 0 && r.Id.Equals(searchBy.ToUpper()) || r.data != null && r.data.Equals(searchBy.ToUpper()) || r.usuario != null && r.usuario.ToUpper().Contains(searchBy.ToUpper()) || r.pasta != null && r.pasta.ToUpper().Contains(searchBy.ToUpper()) || r.contrato != null && r.contrato.ToUpper().Contains(searchBy.ToUpper()) || r.tipoContrato != null && r.tipoContrato.ToUpper().Contains(searchBy.ToUpper()) || r.dataSimulacao != null && r.dataSimulacao.ToUpper().Contains(searchBy.ToUpper()) || r.infoTabela != null && r.infoTabela.ToUpper().Contains(searchBy.ToUpper()) || r.acao != null && r.acao.ToUpper().Contains(searchBy.ToUpper()) || r.modulo != null && r.modulo.ToUpper().Contains(searchBy.ToUpper()) || r.recuperacaoJudicial == true && r.recuperacaoJudicial.Equals(searchBy.ToUpper()) || r.recuperacaoJudicial == false && r.recuperacaoJudicial.Equals(searchBy.ToUpper()) ); } var data2 = orderAscendingDirection ? _logListByParameter.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : _logListByParameter.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList(); // now just get the count of items (without the skip and take) - eg how many could be returned with filtering var filteredResultsCount = data2.Count(); var totalResultsCount = _context.LogItems.Count(); if (string.IsNullOrEmpty(searchBy)) { _logListByParameter.Where(item => item.pasta == pasta && item.contrato == contrato && item.tipoContrato == tipoContrato && item.recuperacaoJudicial == recuperacaoJudicial) .OrderByDescending(x => x.data); var data3 = _logListByParameter.ToList(); int recordsTotal = _logListByParameter.Count(); } if (getAll) { return(_logListByParameter.ToList()); } else { var data = _logListByParameter .Skip(dtParameters.Start) .Take(dtParameters.Length) .ToList(); return(new ObjectResult(new { draw = dtParameters.Draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data })); } } else { return(this.GetLogItems(pasta, contrato, tipoContrato, pageSize, pageNumber, draw, getAll, recuperacaoJudicial)); } }
public Task <DtResult <TVm> > Query <TE>(IRepository <TE> repo, DtParameters parameters) where TE : class, IEntity { return(Query(repo.Queryable, parameters)); }