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