public async Task <JQDataTableResponse> GetPoCountByUsersReportAsync(DataTableAjaxModel dataTable)
{
string orderBy = dataTable.columns[dataTable.order[0].column].name;
string sortBy = dataTable.order[0].dir;
string whereClause = $"WHERE created_at BETWEEN '{dataTable.from_date}' AND '{dataTable.to_date}'";
whereClause += dataTable.search.value == null ? string.Empty
: " AND po_count LIKE '%" + dataTable.search.value + "%' OR username LIKE '%" + dataTable.search.value + "%'";
string query = @"SELECT SQL_CALC_FOUND_ROWS * FROM po_count_by_users_report " +
whereClause +
" ORDER BY " + orderBy + " " + sortBy +
" LIMIT " + dataTable.length + " OFFSET " + dataTable.start + ";" +
" SELECT FOUND_ROWS();";
(IEnumerable <PoCountByUser> list, long count)reportData = await _db.GetByMultiQuery <PoCountByUser, long>(query);
var jqGridResponse = new JQDataTableResponse
{
data = reportData.list,
draw = dataTable.draw,
recordsFiltered = reportData.count,
recordsTotal = reportData.Item2,
status = "Success"
};
return(jqGridResponse);
}
public JsonResult CustomServerSideSearchAction(DataTableAjaxModel model)
{
var searchBy = (model.search != null) ? model.search.value : null;
var whereClause = BuildDynamicWhereClause(_context, searchBy);
string sortBy = "";
bool sortDir = true;
if (String.IsNullOrEmpty(searchBy))
{
// if we have an empty search then just order the results by Id ascending
sortBy = "Id";
sortDir = true;
}
if (model.order != null)
{
// in this example we just default sort on the 1st column
sortBy = model.columns[model.order[0].column].data;
sortDir = model.order[0].dir.ToLower() == "asc";
}
var res = _context.ApplicationUser.Where(u => u.UserName != User.Identity.Name).Where(whereClause).OrderByDyn(sortBy, sortDir).GetPaged <ApplicationUser, ApplicationUserModel>(model.start, model.length);
for (int i = 0; i < res.Results.Count; i++)
{
var cur = res.Results[i];
if (string.IsNullOrWhiteSpace(cur.CreatedBy))
{
continue;
}
var user = Common.ResolveUser(_context, cur.CreatedBy);
if (user == null)
{
continue;
}
res.Results[i].CreatedByName = $"{user.FirstName} {user.LastName}";
var isOwner = res.Results[i].CreatedBy == _userHandler.User.Id;
res.Results[i].HasUpdatePermission = _userHandler.HasRequiredPermission("ApplicationUser-Update", isOwner);
res.Results[i].HasDeletePermission = _userHandler.HasRequiredPermission("ApplicationUser-Delete", isOwner);
res.Results[i].HasViewPermission = _userHandler.HasRequiredPermission("ApplicationUser-Form", isOwner);
}
return(Json(new
{
// this is what datatables wants sending back
draw = model.draw,
recordsTotal = _context.ApplicationUser.Count(),
recordsFiltered = res.RowCount,
data = res.Results
}));
}
/*
* Developer: M.Usama
* Date: 05-09-2019
* Action: Get PO Report by user
* Input: null
* output: list of report data
*/
public async Task <List <PoCountByUser> > GetPOCountByUserReportAsync(DataTableAjaxModel dataTable)
{
string procedureName = "sp_get_po_count_by_users";
int total_count = 0;
DynamicParameters sqlParameters = new DynamicParameters();
sqlParameters.Add("@page_size", dataTable.length, DbType.Int32);
sqlParameters.Add("@offset_value", dataTable.start, DbType.Int32);
sqlParameters.Add("@order_by", dataTable.columns[dataTable.order[0].column].name, DbType.String);
sqlParameters.Add("@sort", dataTable.order[0].dir, DbType.String);
sqlParameters.Add("@search", dataTable.search.value, DbType.String);
sqlParameters.Add("@total_count", total_count, DbType.Int32, ParameterDirection.Output);
var report = await _db.ExecuteProcedureAsync <PoCountByUser>(procedureName, sqlParameters);
return(report.ToList());
}
public async Task <JsonResult> GetPOCountByUser([FromForm] DataTableAjaxModel dataTable)
{
var report = await _badgerApiHelper.PostAsync <JQDataTableResponse>(dataTable, "/report/GetPOCountByUserReport");
return(Json(report));
}
public async Task <ResponseModel> GetPOCountByUserReport([FromBody] DataTableAjaxModel dataTable)
{
var report = await _reportRepo.GetPoCountByUsersReportAsync(dataTable);
return(ResponseHelper.GetResponse(report));
}
