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