public async Task <ListResponse <Resource> > Query(string query, IEnumerable <string> requested, IEnumerable <string> excluded) { IEnumerable <Core2User> users; if (!string.IsNullOrWhiteSpace(query)) { users = new FilterUsers(this._context).FilterGen(query); } else { users = await this._context.CompleteUsers().ToListAsync().ConfigureAwait(false); } NameValueCollection keyedValues = HttpUtility.ParseQueryString(query); IEnumerable <string> keys = keyedValues.AllKeys; string countString = keyedValues[QueryKeys.Count]; string startIndex = keyedValues[QueryKeys.StartIndex]; if (startIndex == null) { startIndex = ControllerConstants.DefaultStartIndexString; } int start = int.Parse(startIndex, CultureInfo.InvariantCulture); if (start < this.defaultStartIndex) { start = this.defaultStartIndex; } int total = users.Count(); int?count = null; users = users.OrderBy(d => d.UserName).Skip(start - 1); if (countString != null) { count = int.Parse(countString, CultureInfo.CurrentCulture); users = users.Take(count.Value); } users = users.Select(u => ColumnsUtility.FilterAttributes(requested, excluded, u, this.alwaysRetuned)).ToList(); ListResponse <Resource> list = new ListResponse <Resource>() { TotalResults = total, StartIndex = users.Any() ? start : (int?)null, Resources = users, }; if (count.HasValue) { list.ItemsPerPage = count.Value; } return(list); }
public IHttpActionResult GetByFilter(FilterUsers filter) { if (filter == null) { filter = new FilterUsers(); filter.PageSize = 25; filter.CurrentPage = 1; } var response = repository.GetUsers(filter, CurrentBusinessId.Value); return(Ok <DataResponse <EntityList <EntityUsers> > >(response)); }
public DataResponse <EntityList <EntityUsers> > GetUsers(FilterUsers filter, int currentBusineId, int take = 10, int skip = 0) { var response = new DataResponse <EntityList <EntityUsers> >(); try { if (filter != null) { take = filter.Take; skip = filter.Skip; } base.DBInit(); var query = DBEntity.Users.Where(a => a.BusinessId == currentBusineId && !a.Roles.Select(y => y.Name).Contains("BusinessAdmin") && !a.Roles.Select(y => y.Name).Contains("SuperAdmin") && !a.Roles.Select(y => y.Name).Contains("MasterAdmin")); if (filter != null) { if (!String.IsNullOrEmpty(filter.KeyWords)) { query = query.Where(ua => ( ua.FirstName + " " + ua.MiddleName).ToLower().Contains(filter.KeyWords.ToLower()) || (ua.FirstName + " " + ua.LastName).ToLower().Contains(filter.KeyWords.ToLower()) || (ua.FirstName + " " + ua.MiddleName + " " + ua.LastName).ToLower().Contains(filter.KeyWords.ToLower()) || ua.FirstName.ToLower().Contains(filter.KeyWords.ToLower()) || ua.MiddleName.ToLower().Contains(filter.KeyWords.ToLower()) || ua.LastName.ToLower().Contains(filter.KeyWords.ToLower()) || ua.Email.ToLower().Contains(filter.KeyWords.ToLower()) || ua.UserName.ToLower().Contains(filter.KeyWords.ToLower()) || ua.Roles.Any(t => t.Name.ToLower().Contains(filter.KeyWords.ToLower())) || ua.Departments.Any(t => t.DepartmentName.ToLower().Contains(filter.KeyWords.ToLower())) || ua.PhoneNumber.ToLower().Contains(filter.KeyWords.ToLower())); } } var selectQuery = query.Select(a => new EntityUsers { Id = a.Id, FirstName = a.FirstName, MiddleName = a.MiddleName, LastName = a.LastName, UserName = a.UserName, Email = a.Email, IsActive = a.IsActive, PhoneNumber = a.PhoneNumber, BusinessId = a.BusinessId, RoleIds = a.Roles.Select(b => b.Id).ToList(), UserRoleNames = a.Roles.Select(p => p.Name), UserDepartmentName = a.UserDepartments2.Select(b => b.Department.DepartmentName), LastLoggedInTime = a.UserProfiles.FirstOrDefault().LastLoggedInTime, }); if (string.IsNullOrEmpty(filter.SortKey) || string.IsNullOrEmpty(filter.SortOrder)) { selectQuery = selectQuery.OrderByDescending(o => o.Id); } else { string orderBy = string.Format("{0} {1}", filter.SortKey, filter.SortOrder); selectQuery = selectQuery.OrderBy(orderBy); } response = GetList <EntityUsers>(selectQuery, skip, take); } catch (Exception ex) { ex.Log(); } finally { base.DBClose(); } return(response); }
public IHttpActionResult GetListUsersByFilter(FilterUsers filterUsers) { if (filterUsers == null) { return(Content(HttpStatusCode.NotFound, "Должен быть указан хотя бы один входной параметр!")); } if (!ModelState.IsValid) { return(BadRequest(ModelState)); } string sql = "WITH resultTable AS (SELECT row_number() OVER (ORDER BY " + ((filterUsers.FieldForSort.ToLower() == ("UserId").ToLower()) ? "u.UserId" : filterUsers.FieldForSort) + ((filterUsers.IsDesc) ? " desc" : " asc") + ") as rowNumber, u.UserId, u.UserName, u.UserMail, u.UserDateTimeRegister, count(b.UserId) as CountBulletins " + "FROM Users AS u LEFT JOIN Bulletins AS b ON (u.UserId = b.UserId) " + "WHERE (u.UserId is not null) "; List <SqlParameter> sqlParameters = new List <SqlParameter>(); string sqlCondition = ""; if (filterUsers.UserName != null) { sqlCondition += " and (UserName LIKE @pUserName)"; sqlParameters.Add(new SqlParameter("@pUserName", "%" + filterUsers.UserName + "%")); } if (filterUsers.UserMail != null) { sqlCondition += " and (UserMail LIKE @pUserMail)"; sqlParameters.Add(new SqlParameter("@pUserMail", "%" + filterUsers.UserMail + "%")); } if (filterUsers.UserDateTimeRegisterBegin != Convert.ToDateTime("01.01.1900") & filterUsers.UserDateTimeRegisterEnd != Convert.ToDateTime("01.01.1900")) { sqlCondition += " and (UserDateTimeRegister between @pUserDateTimeRegisterBegin and @pUserDateTimeRegisterEnd)"; sqlParameters.Add(new SqlParameter("@pUserDateRegisterBegin", filterUsers.UserDateTimeRegisterBegin)); sqlParameters.Add(new SqlParameter("@pUserDateRegisterEnd", filterUsers.UserDateTimeRegisterEnd)); } string sqlConditionPage = ""; if (filterUsers.Page != 0 & filterUsers.CountUsersOnPage != 0) { sqlConditionPage += " WHERE (rowNumber BETWEEN @pPage AND @pCountUsersOnPage)"; sqlParameters.Add(new SqlParameter("@pPage", filterUsers.Page * filterUsers.CountUsersOnPage - filterUsers.CountUsersOnPage + 1)); sqlParameters.Add(new SqlParameter("@pCountUsersOnPage", filterUsers.Page * filterUsers.CountUsersOnPage)); } sql += sqlCondition + " GROUP BY u.UserId, u.UserName, u.UserMail, u.UserDateTimeRegister) SELECT * FROM resultTable" + sqlConditionPage; List <ResultFilterUsers> resultFilterUsers = null; try { resultFilterUsers = db.Database.SqlQuery <ResultFilterUsers>(sql, sqlParameters.ToArray <SqlParameter>()).ToList(); } catch (SqlException) { return(BadRequest("Ошибка запроса, проверьте входные параметры !")); } int usersCount = GetCountUsersWithCondition(sqlCondition, sqlParameters); int pagesCount = 0; if (usersCount > 0) { pagesCount = (usersCount > filterUsers.CountUsersOnPage) ? usersCount / filterUsers.CountUsersOnPage + ((usersCount % filterUsers.CountUsersOnPage > 0) ? 1 : 0) : 1; } ResultContainerOfUsers resultContUsers = new ResultContainerOfUsers { CountPages = pagesCount, CountUsers = usersCount }; resultContUsers.ListUsers = (object)resultFilterUsers; return(Ok(resultContUsers)); }