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
});
}
private static IEnumerable<SortTerm> GetAllTerms(JqueryDataTablesParameters table)
{
var dtColumns = table.Columns as IList<DTColumn> ?? table.Columns.ToList();
if (dtColumns.All(x => !x.Orderable))
{
yield break;
}
foreach (var term in table.Order)
{
var column = dtColumns[term.Column];
if (!column.Orderable)
{
continue;
}
var hasNavigation = column.Data.Contains('.');
yield return new SortTerm
{
Name = column.Data,
Descending = term.Dir.Equals(DTOrderDir.DESC),
HasNavigation = hasNavigation
};
}
}
public static IQueryable <TEntity> Apply(IQueryable <TEntity> query, JqueryDataTablesParameters table)
{
var terms = GetValidTerms(table).ToArray();
if (!terms.Any())
{
return(query);
}
var modifiedQuery = query;
var useThenBy = false;
foreach (var term in terms)
{
var hasMultipleTerms = term.EntityName?.Contains(',') ?? false;
if (hasMultipleTerms)
{
var entityTerms = term.EntityName.Split(',');
foreach (var entityTerm in entityTerms)
{
term.EntityName = entityTerm;
modifiedQuery = GetSortQuery(modifiedQuery, ref useThenBy, term);
}
}
else
{
modifiedQuery = GetSortQuery(modifiedQuery, ref useThenBy, term);
}
}
return(modifiedQuery);
}
private static IEnumerable <SortTerm> GetValidTerms(JqueryDataTablesParameters table)
{
var queryTerms = GetAllTerms(table).ToArray();
if (!queryTerms.Any())
{
yield break;
}
var declaredTerms = GetTermsFromModel();
foreach (var term in queryTerms)
{
var declaredTerm =
declaredTerms.SingleOrDefault(x => x.Name.Equals(term.Name, StringComparison.OrdinalIgnoreCase));
if (declaredTerm == null)
{
continue;
}
yield return(new SortTerm {
Name = declaredTerm.Name,
EntityName = declaredTerm.EntityName,
Descending = term.Descending,
Default = declaredTerm.Default
});
}
}
public IQueryable <TEntity> Apply(IQueryable <TEntity> query, JqueryDataTablesParameters table)
{
var terms = GetValidTerms(table).ToArray();
if (!terms.Any())
{
return(query);
}
var modifiedQuery = query;
var useThenBy = false;
foreach (var term in terms)
{
var propertyInfo = ExpressionHelper
.GetPropertyInfo <TEntity>(term.EntityName ?? term.Name);
var obj = ExpressionHelper.Parameter <TEntity>();
// Build up the LINQ Expression backwards:
// query = query.OrderBy(x => x.Property);
// x => x.Property
var key = ExpressionHelper.GetPropertyExpression(obj, propertyInfo);
var keySelector = ExpressionHelper.GetLambda(typeof(TEntity), propertyInfo.PropertyType, obj, key);
// query.OrderBy/ThenBy[Descending](x => x.Property)
modifiedQuery = ExpressionHelper.CallOrderByOrThenBy(
modifiedQuery, useThenBy, term.Descending, propertyInfo.PropertyType, keySelector);
useThenBy = true;
}
return(modifiedQuery);
}
public async Task <JsonResult> GetDataAsync(IQueryable <object> dbset, JqueryDataTablesParameters param)
{
switch (dbset)
{
// case IQueryable<Chauffeur> chauffeurs:
// return await GetDataAsync<Chauffeur, ChauffeurApi>(chauffeurs, param);
default:
await Task.CompletedTask;
return(null);
}
}
public async Task <IActionResult> GetTelephoneList([FromBody] JqueryDataTablesParameters param)
{
var results = await _iTelephoneRepository.GetTelephoneList(param);
return(new JsonResult(new JqueryDataTablesResult <TelephoneAC>
{
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
}));
}
public async Task <IActionResult> LoadTable([FromBody] JqueryDataTablesParameters param)
{
try
{
HttpContext.Session.SetString(_dataTableSessionKey, JsonConvert.SerializeObject(param));
return(Json(await _demoService.GetDataAsync(param)));
} catch (Exception e)
{
Console.Write(e.Message);
return(new JsonResult(new { error = "Internal Server Error" }));
}
}
public IActionResult GetEmployeeList([FromBody] JqueryDataTablesParameters param)
{
var results = _iEmployeeRepository.GetEmployeeList(param);
return(new JsonResult(new JqueryDataTablesResult <EmployeeProfileDetailAC>
{
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
}));
}
public IActionResult GetAssignedTelephoneList([FromBody] JqueryDataTablesParameters param)
{
var results = _iTelephoneRepository.GetAssignedTelephoneList(param);
return(new JsonResult(new JqueryDataTablesResult <AssignTelePhoneAC>
{
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
}));
}
public async Task <IActionResult> LoadTable([FromBody] JqueryDataTablesParameters param)
{
try
{
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param));
var results = await _demoService.GetDataAsync(param);
return(new JsonResult(new JqueryDataTablesResult <Demo>
{
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.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" }));
}
}
public async Task <JqueryDataTablesPagedResults <TelephoneAC> > GetTelephoneList(JqueryDataTablesParameters param)
{
int skip = (param.Start / param.Length) * param.Length;
int take = param.Length;
int? sortColumnNumber = null;
string sortType = string.Empty;
if (param.Order.Length > 0)
{
sortColumnNumber = param.Order[0].Column;
sortType = param.Order[0].Dir.ToString();
}
IQueryable <Telephonenumber> query = _dbTeleBilling_V01Context.Telephonenumber.Where(x => !x.IsDelete).Include(x => x.Provider).Include(x => x.LineType).OrderByDescending(x => x.Id).AsNoTracking();
query = query.Where(x => x.Provider.Name.Contains(param.Search.Value) || x.TelephoneNumber1.Contains(param.Search.Value) || x.AccountNumber.Contains(param.Search.Value));
var size = await query.CountAsync();
var telphoneList = await query.Skip((param.Start / param.Length) *param.Length).Take(param.Length).ToArrayAsync();
List <TelephoneAC> items = _mapper.Map <List <TelephoneAC> >(telphoneList);
if (items.Any())
{
foreach (var item in items)
{
if (await _dbTeleBilling_V01Context.Telephonenumberallocation.FirstOrDefaultAsync(x => x.TelephoneNumberId == item.Id && !x.IsDelete) != null)
{
item.IsAssigned = true;
}
else
{
item.IsAssigned = false;
}
}
}
return(new JqueryDataTablesPagedResults <TelephoneAC>
{
Items = items,
TotalSize = size
});
}
public JqueryDataTablesPagedResults <AssignTelePhoneAC> GetAssignedTelephoneList(JqueryDataTablesParameters param)
{
List <AssignTelePhoneAC> assignTelePhonelist = new List <AssignTelePhoneAC>();
long skipRecord = param.Start;
int length = param.Length;
int? sortColumnNumber = null;
string sortType = string.Empty;
string searchValue = param.Search.Value;
int totalSize = 0;
if (param.Order.Length > 0)
{
sortColumnNumber = param.Order[0].Column;
sortType = param.Order[0].Dir.ToString();
}
SortedList sl = new SortedList();
sl.Add("SkipRecord", skipRecord);
sl.Add("Length", length);
sl.Add("SearchValue", searchValue);
DataSet ds = _objDalmysql.GetDataSet("usp_GetTelphoneAssignListWithPagging", sl);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0] != null)
{
assignTelePhonelist = _objDal.ConvertDataTableToGenericList <AssignTelePhoneAC>(ds.Tables[0]).ToList();
}
if (ds.Tables[1].Rows.Count > 0 && ds.Tables[1] != null)
{
totalSize = Convert.ToInt16(ds.Tables[1].Rows[0]["TotalSize"]);
}
}
return(new JqueryDataTablesPagedResults <AssignTelePhoneAC>
{
Items = assignTelePhonelist,
TotalSize = totalSize
});
}
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 ActionResult LoadOrderDatatable([FromBody] JqueryDataTablesParameters param)
{
List <Order_MasterViewModel> OrderList = new List <Order_MasterViewModel>();
try
{
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param));
DbfunctionUtility dbfunction = new DbfunctionUtility(_appSettings);
DataSet ds = dbfunction.GetDataset("Select *,NoteCount from orders left join (select Ordernum,count(*)NoteCount from ordernotes group by Ordernum ) a on orders.Ordernum = a.Ordernum order by Orderdate desc limit 50;");
OrderList = (from row in ds.Tables[0].AsEnumerable()
select new Order_MasterViewModel
{
Custnum = Convert.ToString(row["Custnum"]),
ordernum = Convert.ToString(row["ordernum"]),
Orderdate = Convert.ToDateTime(row["Orderdate"]),
Credit = Convert.ToString(row["Credit"]),
Freight = Convert.ToDecimal(row["Freight"]),
Slnum = Convert.ToString(row["Slnum"]),
Slnum2 = Convert.ToString(row["Slnum2"]),
D0 = Convert.ToString(row["D0"]),
Name = Convert.ToString(row["Name"]),
Address1 = Convert.ToString(row["Address1"]),
Address2 = Convert.ToString(row["Address2"]),
Address3 = Convert.ToString(row["Address3"]),
Shipname = Convert.ToString(row["Shipname"]),
Shipaddress1 = Convert.ToString(row["Shipaddress1"]),
Shipaddress2 = Convert.ToString(row["Shipaddress2"]),
Shipaddress3 = Convert.ToString(row["Shipaddress3"]),
Terms = Convert.ToString(row["Terms"]),
Via = Convert.ToString(row["Via"]),
Backorder = Convert.ToString(row["Backorder"]),
Ponum = Convert.ToString(row["Ponum"]),
Shippeddate = Convert.ToString(row["Shippeddate"]) == "" ? (DateTime?)null : Convert.ToDateTime(row["Shippeddate"]),
Shipdate = Convert.ToDateTime(row["Shipdate"]),
Canceldate = Convert.ToString(row["Canceldate"]) == "" ? (DateTime?)null : Convert.ToDateTime(row["Canceldate"]),
Edidate = Convert.ToDateTime(row["Edidate"]),
Custnote = Convert.ToString(row["Custnote"]),
clerk = Convert.ToString(row["clerk"]),
Poammount = Convert.ToDecimal(row["Poammount"]),
Commission = Convert.ToDecimal(row["Commission"]),
Status = Convert.ToString(row["Status"]),
D1 = Convert.ToString(row["D1"]),
D2 = Convert.ToString(row["D2"]),
Creditmemo = Convert.ToString(row["Creditmemo"]),
Storenum = Convert.ToString(row["Storenum"]),
Dept = Convert.ToString(row["Dept"]),
Ordertype = Convert.ToString(row["Ordertype"]),
WeborderId = Convert.ToString(row["WeborderId"]),
IsOpenOrder = Convert.ToString(row["IsOpenOrder"]),
NoteCount = Convert.ToString(row["NoteCount"]) == "" ?0 : Convert.ToInt32(row["NoteCount"]),
}).ToList();
return(new JsonResult(new JqueryDataTablesResult <Order_MasterViewModel>
{
Draw = param.Draw,
Data = OrderList,
RecordsFiltered = 10,
RecordsTotal = 10
}));
}
catch (Exception Ex)
{
}
return(View("List", OrderList));
}
public async Task <JqueryDataTablesExcelResult <Demo> > GetExcelDataAsync(JqueryDataTablesParameters table, bool exportAllData = false)
{
return(await JqueryDataTableHelpers.GetJqueryDataTablesExcelResultAsync <Demo, DemoEntity>(table, _context.Demos, _mappingConfiguration, exportAllData, "Demo Sheet Name", "Fingers10"));
}
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 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
{
//return new JsonResult(new { error = "Internal Server Error" });
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), System.Text.Json.JsonSerializer.Serialize(param));
DbfunctionUtility dbfunction = new DbfunctionUtility(_appSettings);
DataSet ds = dbfunction.GetDataset(@"select indSell_Compo.*,itemmaster.Description as Item_master,itemcomponent.Description as Item_component
from indSell_Compo
left join items as itemmaster on trim(indSell_ItemMaster) = itemmaster.Itemnum
left join items as itemcomponent on trim(indSell_Compo.indSell_ItemComponent) = itemcomponent.Itemnum");
IQueryable <IndsellCompoViewModel> itemComponentList = (from row in ds.Tables[0].AsEnumerable()
select new IndsellCompoViewModel
{
indSell_ItemMaster = Convert.ToString(row["indSell_ItemMaster"]),
indSell_ItemComponent = Convert.ToString(row["indSell_ItemComponent"]),
indSell_Allowed = Convert.ToInt16(row["indSell_Allowed"]),
Item_master = Convert.ToString(row["Item_master"]),
Item_component = Convert.ToString(row["Item_component"]),
}).AsQueryable();
var size = itemComponentList.Count();
if (Convert.ToString(param.AdditionalValues.FirstOrDefault()) != "")
{
var serchValue = Convert.ToString(param.AdditionalValues.FirstOrDefault()).ToLower();
itemComponentList = itemComponentList.Where(w =>
((w.indSell_ItemMaster ?? "").ToLower().Contains(serchValue) ? true :
((w.indSell_ItemComponent ?? "").ToLower().Contains(serchValue) ? true :
(w.indSell_Allowed).ToString().ToLower().Contains(serchValue) ? true :
(w.Item_master).ToString().ToLower().Contains(serchValue) ? true :
(w.Item_component).ToString().ToLower().Contains(serchValue) ? true : false)));
}
if (param.Length == -1)
{
var items = itemComponentList
.ProjectTo <IndsellCompoViewModel_Datatable>(_mappingConfiguration)
.ToArray();
var result = new JqueryDataTablesPagedResults <IndsellCompoViewModel_Datatable>
{
Items = items,
TotalSize = size
};
return(new JsonResult(new JqueryDataTablesResult <IndsellCompoViewModel_Datatable>
{
Draw = param.Draw,
Data = result.Items,
RecordsFiltered = result.TotalSize,
RecordsTotal = result.TotalSize
}));
}
else
{
var items = itemComponentList
.Skip((param.Start / param.Length) * param.Length)
.Take(param.Length)
.ProjectTo <IndsellCompoViewModel_Datatable>(_mappingConfiguration)
.ToArray();
var result = new JqueryDataTablesPagedResults <IndsellCompoViewModel_Datatable>
{
Items = items,
TotalSize = size
};
return(new JsonResult(new JqueryDataTablesResult <IndsellCompoViewModel_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> SearchPersonas([FromBody] JqueryDataTablesParameters param)
{
try
{
#region Paginacion y Ordenacion
var pageIndex = (param.Start / param.Length) + 1;
var pageSize = param.Length;
var sortedColumns = param.Order;
var colum = sortedColumns.FirstOrDefault();
var orderBy = PersonaOrderColumn.Codigo;
var orderIsAscending = true;
if (colum != null)
{
var orderByAux = (PersonaOrderColumn)colum.Column;
orderBy = orderByAux;
orderIsAscending = colum.Dir == DTOrderDir.ASC ? true : false;
}
#endregion
var optionSearch = new OptionSearchPersonasDto
{
TextSearch = param.Search?.Value,
PageIndex = pageIndex,
IsAscending = orderIsAscending,
PageSize = pageSize,
OrderBy = orderBy,
};
HttpContext.Session.SetString(nameof(OptionSearchPersonasDto), JsonSerializer.Serialize(optionSearch));
var res = await _service.BuscarAsync(optionSearch);
if (res.HasErrors)
{
return(null);
}
var list = res.Elements;
var data = list.Select(ele => new PersonaViewModel
{
Codigo = ele.Id.ToString(),
Nombres = ele.Nombres,
Apellidos = ele.Apellidos,
Oficina = ele.Oficina,
Experiencia = ele.Experiencia,
FechaInicio = ele.FechaInicio,
Salario = ele.Salario,
Cargo = ele.Cargo
}).ToList();
var recordsFiltered = res.TotalFiltered;
var recordsTotal = res.TotalElements;
return(new JsonResult(new JqueryDataTablesResult <PersonaViewModel>
{
Draw = param.Draw,
Data = data,
RecordsFiltered = recordsFiltered,
RecordsTotal = recordsTotal
}));
}
catch (Exception e)
{
Console.Write(e.Message);
return(new JsonResult(new { error = "Internal Server Error" }));
}
}
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 JqueryDataTablesPagedResults <EmployeeProfileDetailAC> GetEmployeeList(JqueryDataTablesParameters param)
{
List <EmployeeProfileDetailSP> employeelistData = new List <EmployeeProfileDetailSP>();
List <EmployeeProfileDetailAC> employeelist = new List <EmployeeProfileDetailAC>();
try
{
long skipRecord = param.Start;
int length = param.Length;
int? sortColumnNumber = null;
string sortType = string.Empty;
string searchValue = param.Search.Value;
int totalSize = 0;
if (param.Order.Length > 0)
{
sortColumnNumber = param.Order[0].Column;
sortType = param.Order[0].Dir.ToString();
}
SortedList sl = new SortedList();
sl.Add("SkipRecord", skipRecord);
sl.Add("Length", length);
sl.Add("SearchValue", searchValue);
DataSet ds = _objDalmysql.GetDataSet("usp_GetEmployeesWithPagging", sl);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0] != null)
{
employeelistData = _objDal.ConvertDataTableToGenericList <EmployeeProfileDetailSP>(ds.Tables[0]).ToList();
}
if (ds.Tables[1].Rows.Count > 0 && ds.Tables[1] != null)
{
totalSize = Convert.ToInt16(ds.Tables[1].Rows[0]["TotalSize"]);
}
}
if (employeelistData.Any())
{
employeelist = _mapper.Map <List <EmployeeProfileDetailAC> >(employeelistData);
}
return(new JqueryDataTablesPagedResults <EmployeeProfileDetailAC>
{
Items = employeelist,
TotalSize = totalSize
});
}
catch (Exception e)
{
return(null);
}
}
public async Task <IActionResult> LoadTable1([FromBody] JqueryDataTablesParameters param)
{
try
{
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param));
List <Order_MasterViewModel> list = new List <Order_MasterViewModel>();
var mySqlQuery = "select count(*) from orders";
var searchQuery = "";
DbfunctionUtility dbfunctionUtility = new DbfunctionUtility(_appSettings);
var dsResult = dbfunctionUtility.GetDataset(mySqlQuery);
var totalRecords = Convert.ToInt32(dsResult.Tables[0].Rows[0][0]);
var searchRecords = 0;
mySqlQuery = "SET @row_number = 0; SELECT t.* FROM ( SELECT *, (@row_number:=@row_number + 1) AS RowNo FROM orders )t where 1=1 ";
var searchCount = 0;
var orderNum = "";
var custnumOrName = "";
var Shipdate = "";
var i = 0;
foreach (var column in param.AdditionalValues)
{
if (i == 0)
{
orderNum = column;
}
else if (i == 1)
{
custnumOrName = column;
}
else
{
Shipdate = column;
}
i++;
}
if (!string.IsNullOrEmpty(orderNum))
{
searchCount++;
mySqlQuery += " and ordernum like '%" + orderNum + "%' ";
}
if (!string.IsNullOrEmpty(Shipdate) && (Convert.ToString(Shipdate) != "1/1/0001 12:00:00 AM"))
{
searchCount++;
mySqlQuery += " and DATE_FORMAT(Shipdate, '%m/%d/%Y') = '" + Shipdate + "' ";
}
if (!string.IsNullOrEmpty(custnumOrName))
{
searchCount++;
mySqlQuery += " and Custnum like '%" + custnumOrName + "%' ";
}
var orderByColumn = param.Columns[param.Order[0].Column];
mySqlQuery += " and RowNo > " + ((param.Start / param.Length) * param.Length) + " ";
if (orderByColumn.Data == "Action")
{
mySqlQuery += " order by Orderdate desc ";
}
else
{
mySqlQuery += " order by " + orderByColumn.Data + " " + param.Order[0].Dir + "";
}
mySqlQuery += " limit " + param.Length;
if (param.Start == 0 && searchCount == 0)
{
mySqlQuery = "select * from orders order by Orderdate desc limit 50";
}
var queryResult = _dbContext.tbl_Orders.FromSqlRaw(mySqlQuery).ToList <Order>();
if (queryResult.Count() == 0 && (!string.IsNullOrEmpty(custnumOrName)))
{
mySqlQuery = "SET @row_number = 0; SELECT t.* FROM ( SELECT *, (@row_number:=@row_number + 1) AS RowNo FROM orders )t where 1=1 ";
searchCount = 0;
if (!string.IsNullOrEmpty(orderNum))
{
searchCount++;
mySqlQuery += " and ordernum like '%" + orderNum + "%' ";
}
if (!string.IsNullOrEmpty(Shipdate))
{
searchCount++;
mySqlQuery += " and DATE_FORMAT(Shipdate, '%m/%d/%Y') = '" + Shipdate + "' ";
}
if (!string.IsNullOrEmpty(custnumOrName))
{
searchCount++;
mySqlQuery += " and Name like '%" + custnumOrName + "%' ";
}
orderByColumn = param.Columns[param.Order[0].Column];
searchQuery = mySqlQuery;
mySqlQuery += " and RowNo > " + ((param.Start / param.Length) * param.Length) + " ";
if (orderByColumn.Data == "Action")
{
mySqlQuery += " order by Orderdate desc ";
}
else
{
mySqlQuery += " order by " + orderByColumn.Data + " " + param.Order[0].Dir + "";
}
mySqlQuery += " limit " + param.Length;
}
if (searchQuery != "")
{
var searchDs = dbfunctionUtility.GetDataset(searchQuery);
try
{
searchRecords = Convert.ToInt32(searchDs.Tables[0].Rows[0][0]);
}
catch (Exception ex)
{
}
}
return(new JsonResult(new JqueryDataTablesResult <Order>
{
Draw = param.Draw,
Data = queryResult,
RecordsFiltered = (searchCount == 0 ? totalRecords : (searchRecords == 0 ? queryResult.Count() : searchRecords)),
RecordsTotal = totalRecords
}));
}
catch (Exception e)
{
Console.Write(e.Message);
return(new JsonResult(new { error = "Internal Server Error" }));
}
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
if (bindingContext == null)
{
throw new ArgumentNullException(nameof(bindingContext));
}
var allValues = bindingContext.HttpContext.Request.Query;
// Retrieve request data
var draw = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "draw").Value);
var start = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "start").Value);
var length = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "length").Value);
// Search
var search = new DTSearch {
Value = allValues.FirstOrDefault(a => a.Key == "search[value]").Value,
Regex = Convert.ToBoolean(allValues.FirstOrDefault(a => a.Key == "search[regex]").Value)
};
// Order
var o = 0;
var order = new List <DTOrder>();
while (allValues.Any(a => a.Key == "order[" + o + "][column]"))
{
Enum.TryParse(allValues.FirstOrDefault(a => a.Key == "order[" + o + "][dir]").Value,
out DTOrderDir dir);
order.Add(new DTOrder {
Column = Convert.ToInt32(allValues.FirstOrDefault(a => a.Key == "order[" + o + "][column]").Value),
Dir = dir
});
o++;
}
// Columns
var c = 0;
var columns = new List <DTColumn>();
while (allValues.Any(a => a.Key == "columns[" + c + "][name]"))
{
columns.Add(new DTColumn {
Data = allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][data]").Value,
Name = allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][name]").Value,
Orderable = Convert.ToBoolean(allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][orderable]").Value),
Searchable = Convert.ToBoolean(allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][searchable]").Value),
Search = new DTSearch {
Value = allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][search][value]").Value,
Regex = Convert.ToBoolean(allValues.FirstOrDefault(a => a.Key == "columns[" + c + "][search][regex]").Value)
}
});
c++;
}
// Additional Values
var p = 0;
var additionalValues = new List <string>();
while (allValues.Any(a => a.Key == "additionalValues[" + p + "]"))
{
additionalValues.Add(allValues.FirstOrDefault(a => a.Key == "additionalValues[" + p + "]").Value);
p++;
}
var model = new JqueryDataTablesParameters {
Draw = draw,
Start = start,
Length = length,
Search = search,
Order = order.ToArray(),
Columns = columns.ToArray(),
AdditionalValues = additionalValues.ToArray()
};
bindingContext.Result = ModelBindingResult.Success(model);
return(Task.CompletedTask);
}
public async Task <JqueryDataTablesResult <Demo> > GetDataAsync(JqueryDataTablesParameters table)
{
return(await JqueryDataTableHelpers.GetJqueryDataTablesResultAsync <Demo, DemoEntity>(table, _context.Demos, _mappingConfiguration));
}
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 JqueryDataTablesPagedResults <VendorWisePackageDetailsAC> VendorWisePackageDetailReport(JqueryDataTablesParameters param)
{
int skip = (param.Start / param.Length) * param.Length;
int take = param.Length;
int totalsize = 0;
List <VendorWisePackageDetailsAC> searchDataList = new List <VendorWisePackageDetailsAC>();
List <PackageDetailListAC> packageDetailListACs = new List <PackageDetailListAC>();
SortedList sl = new SortedList();
sl.Add("SkipRecord", skip);
sl.Add("Length", take);
sl.Add("SearchValue", param.Search.Value);
DataSet ds = _objDalmysql.GetDataSet("usp_GetVendorWisePackageReportWithPagging", sl);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0] != null)
{
packageDetailListACs = _objDal.ConvertDataTableToGenericList <PackageDetailListAC>(ds.Tables[0]).ToList();
}
if (ds.Tables[1].Rows.Count > 0 && ds.Tables[1] != null)
{
totalsize = Convert.ToInt16(ds.Tables[1].Rows[0]["TotalSize"]);
}
}
var providedrWiseGroup = packageDetailListACs.GroupBy(x => x.ProviderName);
foreach (var item in providedrWiseGroup)
{
VendorWisePackageDetailsAC vendorWisePackageDetailsAC = new VendorWisePackageDetailsAC();
vendorWisePackageDetailsAC.ProviderName = item.Key;
vendorWisePackageDetailsAC.PackageDetailListAC = new List <PackageDetailListAC>();
foreach (var subitem in item)
{
vendorWisePackageDetailsAC.PackageDetailListAC.Add(subitem);
}
searchDataList.Add(vendorWisePackageDetailsAC);
}
return(new JqueryDataTablesPagedResults <VendorWisePackageDetailsAC>
{
Items = searchDataList,
TotalSize = totalsize
});
}
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 JqueryDataTablesPagedResults <TransferDeActivatedReportAC> GetTransferredDeactivatedReport(JqueryDataTablesParameters param)
{
int totalsize = 0;
List <TransferDeActivatedReportAC> searchDataList = new List <TransferDeActivatedReportAC>();
int skip = (param.Start / param.Length) * param.Length;
int take = param.Length;
SortedList sl = new SortedList();
sl.Add("SkipRecord", skip);
sl.Add("Length", take);
sl.Add("SearchValue", param.Search.Value);
DataSet ds = _objDalmysql.GetDataSet("usp_GetTransferredDeactivatedWithPagging", sl);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0] != null)
{
searchDataList = _objDal.ConvertDataTableToGenericList <TransferDeActivatedReportAC>(ds.Tables[0]).ToList();
}
if (ds.Tables[1].Rows.Count > 0 && ds.Tables[1] != null)
{
totalsize = Convert.ToInt16(ds.Tables[1].Rows[0]["TotalSize"]);
}
}
return(new JqueryDataTablesPagedResults <TransferDeActivatedReportAC>
{
Items = searchDataList,
TotalSize = totalsize
});
}
