public static IQueryable<vw_Users_Role_Org> PrepareQuery(IQueryable<vw_Users_Role_Org> userQuery, vw_Users_Role_Org usr)
{
#region Append WHERE clause if applicable
if (!string.IsNullOrEmpty(usr.UserName)) userQuery = userQuery.Where(o => SqlMethods.Like(o.UserName.ToUpper(), usr.UserName.ToUpper()));
//userQuery.Where(o => o.Name.ToUpper().Contains(userName));
if (usr.RoleID > 0) userQuery = userQuery.Where(o => o.RoleID == usr.RoleID);
else if (!string.IsNullOrEmpty(usr.RoleName))
userQuery = userQuery.Where(o => SqlMethods.Like(o.RoleName.ToLower(), usr.RoleName.ToLower()));
//if (usr.OrgID > 0) userQuery = userQuery.Where(o => o.OrgID == usr.OrgID);
if (!string.IsNullOrEmpty(usr.Email))
userQuery = userQuery.Where(o => SqlMethods.Like(o.Email.ToUpper(), usr.Email.ToUpper()));
if (!string.IsNullOrEmpty(usr.OrgName))
userQuery = userQuery.Where(o => SqlMethods.Like(o.OrgName.ToUpper(), usr.OrgName.ToUpper()));
if (!string.IsNullOrEmpty(usr.Country))
userQuery = userQuery.Where(o => SqlMethods.Like(o.Country.ToUpper(), usr.Country.ToUpper()));
if (!string.IsNullOrEmpty(usr.SalespersonCode))
userQuery = userQuery.Where(o => SqlMethods.Like(o.SalespersonCode.ToUpper(), usr.SalespersonCode.ToUpper()));
#endregion
return userQuery;
}
public UserNav(string NavString)
{
Alias = new string[]
{
"user","users","usersearch","user search","userlist","user list"
};
filter = new vw_Users_Role_Org();
Data = NavString;
}
public List<vw_Users_Role_Org> SearchUser(string orderBy, vw_Users_Role_Org usr)
{
orderBy = string.IsNullOrEmpty(orderBy) ? userSortOn : orderBy;
using (dbc)
{
IQueryable<vw_Users_Role_Org> userQuery = (from vw_u in dbc.vw_Users_Role_Orgs select vw_u);
//Get filters - if any
userQuery = UserService.PrepareQuery(userQuery, usr);
// Apply Sorting, Pagination and return PagedList
return userQuery.OrderBy(orderBy).ToList();
}
}
public List<UserRpt.UserRole> GetRoleUserCount(vw_Users_Role_Org usr)
{
List<vw_Users_Role_Org> usrList = SearchUser(userSortOn, usr);
List<UserRpt.UserRole> usrCountLst = new List<UserRpt.UserRole>(usrList.Count);
//Get grouped data
var grouped = from ul in usrList
group ul by new { ul.RoleID, ul.RoleName } into g
orderby g.Key.RoleName
select new { RoleID = g.Key.RoleID, Role = g.Key.RoleName, Count = g.Count() };
//Populate UserCount list
foreach (var u in grouped)
usrCountLst.Add(new UserRpt.UserRole() { RoleID = u.RoleID, Role = u.Role, Count = u.Count });
return usrCountLst;
}
public List<UserRpt.UserOrgType> GetOrgTypeUserCount(vw_Users_Role_Org usr)
{
List<vw_Users_Role_Org> usrList = SearchUser(userSortOn, usr);
List<UserRpt.UserOrgType> usrCountLst = new List<UserRpt.UserOrgType>(usrList.Count);
//Get grouped data
var grouped = from ul in usrList
group ul by new { ul.OrgTypeId, ul.OrgType} into g
orderby g.Key.OrgType
select new { OrgTypeID = g.Key.OrgTypeId, OrgType = g.Key.OrgType, Count = g.Count() };
//Populate UserCount list
foreach (var u in grouped)
usrCountLst.Add(new UserRpt.UserOrgType() { OrgTypeID = u.OrgTypeID.Value, OrgType = u.OrgType, Count = u.Count });
return usrCountLst;
}
public JsonResult UserList(vw_Users_Role_Org searchObj, string doReset, string qData)
{
searchOpts = (doReset == "on") ? new vw_Users_Role_Org() : searchObj; // Set or Reset Search-options
populateData(false);// Populate ddl Viewdata
return Json(true);// WE just need to set it in the session
}
public JsonResult UserList(int? index, string qData, bool? fetchAll)
{
base.SetTempDataSort(ref index);// Set TempDate, Sort & index
//Make sure searchOpts is assigned to set ViewState
vw_Users_Role_Org oldSearchOpts = (vw_Users_Role_Org)searchOpts;
searchOpts = new vw_Users_Role_Org();
populateData(false);
index = (index > 0) ? index + 1 : index; // paging starts with 2
var result = from vw_u in new UserService().SearchKO(sortExpr, index, gridPageSize * 2, (vw_Users_Role_Org)searchOpts, fetchAll ?? false)
select new
{
ID = vw_u.ID,
Email = vw_u.Email,
OrgID = vw_u.OrgID,
OrgName = vw_u.OrgName,
OrgType = vw_u.OrgType,
OrgTypeId = vw_u.OrgTypeId,
RoleID = vw_u.RoleID,
RoleName = vw_u.RoleName,
SalespersonCode = vw_u.SalespersonCode,
UserName = vw_u.UserName
};
//var result = new UserService().SearchKO(sortExpr, index, gridPageSize * 2, (vw_Users_Role_Org)searchOpts, fetchAll ?? false);
return Json(new { records = result, search = oldSearchOpts }, JsonRequestBehavior.AllowGet);
}
public List<vw_Users_Role_Org> SearchKO(string orderBy, int? pgIndex, int pageSize, vw_Users_Role_Org usr, bool fetchAll)
{
orderBy = string.IsNullOrEmpty(orderBy) ? sortOn : orderBy;
using (dbc)
{
IQueryable<vw_Users_Role_Org> userQuery = (from vw_u in dbc.vw_Users_Role_Orgs select vw_u);
//Get filters - if any
userQuery = PrepareQuery(userQuery, usr);
// Apply Sorting
userQuery = userQuery.OrderBy(orderBy);
// Apply pagination and return
if (fetchAll)
return userQuery.ToList<vw_Users_Role_Org>();
else
return userQuery.Skip(pgIndex.Value).Take(pageSize).ToList<vw_Users_Role_Org>();
}
}
public PagedList<vw_Users_Role_Org> Search(string orderBy, int? pgIndex, int pageSize, vw_Users_Role_Org usr)
{
orderBy = string.IsNullOrEmpty(orderBy) ? sortOn : orderBy;
using (dbc)
{
IQueryable<vw_Users_Role_Org> userQuery = (from vw_u in dbc.vw_Users_Role_Orgs select vw_u);
//Get filters - if any
userQuery = PrepareQuery(userQuery, usr);
// Apply Sorting, Pagination and return PagedList
return userQuery.OrderBy(orderBy).ToPagedList(pgIndex ?? 1, pageSize);
/* Apply pagination and return
return userQuery.Skip(startRow).Take(pageSize).ToList<vw_User_Org_UserRole>();
*/
}
}
public static void setUserSession(vw_Users_Role_Org data)
{
ID = data.ID;
RoleID = data.RoleID;
OrgID = data.OrgID;
SalespersonCode = data.SalespersonCode;
UserName = data.UserName;
Email = data.Email;
RoleName = data.RoleName;
OrgTypeId = data.OrgTypeId??Defaults.Integer;
OrgType = data.OrgType;
OrgName = data.OrgName;
Country = data.Country;
}