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));
}
