public JsonResult ListUserData([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
{
var defaultSortBy = Constants.SortField.Username;
var pagingParameter = MVCExtensionMethods.GetPagingParametersII(requestModel, defaultSortBy, Constants.SortOrder.Ascending.ToLower());
UserListingResponse userList = _securityService.GetUserList(pagingParameter);
var data = userList.UserListingResult;
return(Json(new DataTablesResponse((int)requestModel.Draw, data, userList.PagerResource.ResultCount, userList.PagerResource.ResultCount), JsonRequestBehavior.AllowGet));
}
public UserListingResponse GetUserList(PagerItemsII parameter)
{
//Better paging query which works for SQL Server 2012 or higher
//SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
//FROM TB_EXAMPLE
//ORDER BY ID_EXAMPLE
//OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
//FETCH NEXT @RowspPage ROWS ONLY;
var result = new UserListingResponse()
{
PagerResource = new PagerItems()
};
var orderByField = string.Empty;
var sql = new StringBuilder();
sql.AppendLine("SELECT * FROM (");
sql.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY ");
var sortSql = new StringBuilder();
foreach (var column in parameter.SortColumns)
{
sortSql.Append(sortSql.Length > 0 ? "," : "");
if (column.Data == Constants.SortField.Username)
{
sql.Append("u.Username ");
sortSql.Append("Username ");
}
else if (column.Data == Constants.SortField.Role)
{
sql.Append("r.RoleName ");
sortSql.Append("RoleName ");
}
else if (column.Data == Constants.SortField.FirstName)
{
sql.Append("FirstName ");
sortSql.Append("FirstName ");
}
else if (column.Data == Constants.SortField.LastName)
{
sql.Append("LastName ");
sortSql.Append("LastName ");
}
else if (column.Data == Constants.SortField.Email)
{
sql.Append("Email ");
sortSql.Append("Email ");
}
sql.Append(column.SortDirection == 0 ? " asc" : " desc");
sortSql.Append(column.SortDirection == 0 ? " asc" : " desc");
}
var whereClause = string.Empty;
var filter = string.Empty;
if (!string.IsNullOrEmpty(parameter.siteSearch))
{
filter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
filter = string.Format("%{0}%", filter);
whereClause = (" and " +
"((u.Username LIKE @SearchFilter) OR" +
" (FirstName LIKE @SearchFilter) " +
"OR (LastName LIKE @SearchFilter) OR" +
" ( r.RoleName LIKE @SearchFilter) OR" +
" (Email LIKE @SearchFilter)) ");
}
sql.AppendLine(") AS NUMBER, u.Username, FirstName, LastName, Email, ApprovalStatus, IsLockedOut, InitiatedBy, u.IsDeleted, ");
sql.AppendLine("r.RoleId as UserRoleID, r.RoleName as UserRole ");
sql.AppendLine("From [User] u inner join UsersInRoles ur on ur.Username = u.Username inner join Role r on ur.RoleID = r.RoleID ");
sql.AppendFormat("WHERE (u.ApprovalStatus = 'Pending' or (u.ApprovalStatus = 'Approved' and u.IsDeleted = 0)) {0}) AS TBL ", whereClause);
sql.AppendLine();
sql.AppendLine("WHERE NUMBER BETWEEN @StartPage AND @EndPage ");
sql.AppendFormat("ORDER BY {0} ", sortSql.ToString());
//if (!string.IsNullOrEmpty(parameter.siteSearch))
//{
// filter = parameter.siteSearch.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]");
// filter = string.Format("%{0}%", filter);
// whereClause = (" WHERE ((u.Username LIKE @SearchFilter) OR (FirstName LIKE @SearchFilter) OR (LastName LIKE @SearchFilter) OR ( r.RoleName LIKE @SearchFilter) OR (Email LIKE @SearchFilter)) ");
//}
//sql.AppendLine(") AS NUMBER, u.Username, FirstName, LastName, Email, ApprovalStatus, IsLockedOut, ");
//sql.AppendLine("r.RoleId as UserRoleID, r.RoleName as UserRole ");
//sql.AppendLine("From [User] u inner join UsersInRoles ur on ur.Username = u.Username inner join Role r on ur.RoleID = r.RoleID ");
//sql.AppendFormat("{0}) AS TBL ", whereClause);
//sql.AppendLine();
//sql.AppendLine("WHERE NUMBER BETWEEN @StartPage AND @EndPage ");
//sql.AppendFormat("ORDER BY {0} ", sortSql.ToString());
result.PagerResource.ResultCount = (int)context.Query <Int64>(
string.Format("Select Count(u.Username) From [User] u INNER JOIN UsersInRoles ur on ur.Username = u.Username INNER JOIN Role r on ur.RoleID = r.RoleID {0} WHERE (u.ApprovalStatus = 'Pending' or (u.ApprovalStatus = 'Approved' and u.IsDeleted = 0))", whereClause),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
SearchFilter = filter
}).First();
result.UserListingResult = context.Query <UserListingDto>(sql.ToString(),
new
{
StartPage = ((parameter.PageNumber - 1) * parameter.PageSize) + 1,
EndPage = (parameter.PageNumber * parameter.PageSize),
SearchFilter = filter
}).ToList();
return(result);
}
public ActionResult ListUser()
{
var userList = new UserListingResponse();
return(View(userList));
}