public IQueryable <TEntity> Apply(IQueryable <TEntity> query)
{
// query = query.Where(x => x.Name == "Oxford Suite");
var processor = new SearchOptionsProcessor <T, TEntity>(Search);
return(processor.Apply(query));
}
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 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 static IQueryable <TEntity> ApplySearching <TModel, TEntity>(
this IQueryable <TEntity> query,
QueryParameters <TModel> queryParams
)
{
var processor = new SearchOptionsProcessor <TModel>(queryParams.SearchOptions.SearchTerms);
return(processor.Apply <TEntity>(query));
}
public void GetAllTerms_NullSearchQuery_ShouldReturnEmptyList()
{
// Arrange
var processor = new SearchOptionsProcessor <Recipe>(null);
// Act
var terms = processor.GetAllTerms();
// Assert
Assert.Empty(terms);
}
public IEnumerable <ValidationResult> Validate(ValidationContext validationContext)
{
var processor = new SearchOptionsProcessor <T, TEntity>(Search);
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 search term {term}", new[] { nameof(Search) }));
}
}
public IEnumerable <ValidationResult> Validate(ValidationContext validationContext)
{
var processor = new SearchOptionsProcessor <T, TEntity>(Search);
var validTerms = processor.GetValidTerms().Select(x => x.Name);
var invalidTerms = processor.GetAllTerms().Select(x => x.Name).Except(validTerms, StringComparer.CurrentCultureIgnoreCase);
foreach (var term in invalidTerms)
{
yield return(new ValidationResult($"Invalid search term '{term}'.", new[] { nameof(Search) }));
}
}
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 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);
}
private IEnumerable <ValidationResult> ValidateSearchOptions()
{
if (Search == null)
{
yield break;
}
var processor = new SearchOptionsProcessor <T>(Search);
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 search term '{term}'", new[] { nameof(Search) }));
}
}
public void GetAllTerms_NoSpaces_ShouldAssignToNames()
{
// Arrange
var elements = new[] { "abc", "def", "G_hi_jkl", "m_desc" };
var processor = new SearchOptionsProcessor <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);
Assert.True(string.IsNullOrEmpty(term.Operator));
Assert.True(string.IsNullOrEmpty(term.Value));
Assert.False(term.ValidSyntax, "Syntax is actually valid");
}
}
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 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));
}
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" }));
}
}
public IQueryable <TEntity> Apply(IQueryable <TEntity> query)
{
var processor = new SearchOptionsProcessor <T, TEntity>(Search);
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 void ParseQuery(string parameter)
{
var processor = new SearchOptionsProcessor <T>(SearchTerms);
SearchTerms = processor.ParseAllTerms(parameter);
}
