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 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 <Demo> > GetDataAsync(JqueryDataTablesParameters table)
{
Demo[] items = null;
IQueryable <DemoEntity> query = _context.Demos
.AsNoTracking()
.Include(x => x.DemoNestedLevelOne)
.ThenInclude(y => y.DemoNestedLevelTwo);
query = SearchOptionsProcessor <Demo, DemoEntity> .Apply(query, table.Columns);
query = SortOptionsProcessor <Demo, DemoEntity> .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 <Demo>(_mappingConfiguration)
.ToArrayAsync();
}
else
{
items = await query
.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 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 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>();
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 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>();
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" }));
}
}