public static IEnumerable <UserBE> GetUsersByQuery(DreamContext context, uint?groupId, out uint totalCount, out uint queryCount)
{
uint limit, offset;
SortDirection sortDir;
string sortFieldString;
Utils.GetOffsetAndCountFromRequest(context, 100, out limit, out offset, out sortDir, out sortFieldString);
// Attempt to read the sort field. If a parsing error occurs, default to undefined.
UsersSortField sortField = UsersSortField.UNDEFINED;
if (!String.IsNullOrEmpty(sortFieldString))
{
try { sortField = SysUtil.ChangeType <UsersSortField>(sortFieldString.Replace('.', '_')); } catch { }
}
uint?serviceid = context.GetParam <uint>("authprovider", 0);
if ((serviceid ?? 0) == 0)
{
serviceid = null;
}
string usernamefilter = context.GetParam("usernamefilter", null);
string realnamefilter = context.GetParam("fullnamefilter", null);
string usernameemailfilter = context.GetParam("usernameemailfilter", null);
string rolefilter = context.GetParam("rolefilter", null);
bool? activatedfilter = null;
bool parsedActivatedFilter;
if (bool.TryParse(context.GetParam("activatedfilter", null), out parsedActivatedFilter))
{
activatedfilter = parsedActivatedFilter;
}
bool?seatFilter = null;
var seatFilterParam = context.GetParam("seatfilter", null);
switch (seatFilterParam)
{
case null:
break;
case "seated":
case "true":
seatFilter = true;
break;
case "unseated":
case "false":
seatFilter = false;
break;
case "recommended":
return(DekiContext.Current.LicenseManager.GetSeatRecommendations(offset, limit, out totalCount, out queryCount));
default:
throw new ArgumentException("invalid value for seatfilter");
}
return(DbUtils.CurrentSession.Users_GetByQuery(usernamefilter, realnamefilter, usernameemailfilter, rolefilter, activatedfilter, groupId, serviceid, seatFilter, sortDir, sortField, offset, limit, out totalCount, out queryCount));
}
public IEnumerable<UserBE> Users_GetByQuery(string usernamefilter, string realnamefilter, string usernameemailfilter, string rolefilter, bool? activatedfilter, uint? groupId, uint? serviceIdFilter, bool? seatFilter, SortDirection sortDir, UsersSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) {
List<UserBE> users = new List<UserBE>();
uint totalCountTemp = 0;
uint queryCountTemp = 0;
StringBuilder joinQuery = new StringBuilder();
string sortFieldString;
USERS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if(!string.IsNullOrEmpty(rolefilter) || (sortFieldString ?? string.Empty).StartsWith("roles.")) {
joinQuery.Append(@"
left join roles
on users.user_role_id = roles.role_id");
}
if((sortFieldString ?? string.Empty).StartsWith("services.")) {
joinQuery.AppendFormat(@"
left join services
on users.user_service_id = services.service_id");
}
if(groupId != null) {
joinQuery.AppendFormat(@"
join groups
on groups.group_id = {0}
join user_groups
on user_groups.group_id = groups.group_id", groupId.Value);
}
StringBuilder whereQuery = new StringBuilder(" where 1=1");
if(groupId != null) {
whereQuery.AppendFormat(" AND users.user_id = user_groups.user_id");
}
if(!string.IsNullOrEmpty(usernamefilter) && !string.IsNullOrEmpty(realnamefilter)) {
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_real_name like '{1}%')", DataCommand.MakeSqlSafe(usernamefilter), DataCommand.MakeSqlSafe(realnamefilter));
} else if(!string.IsNullOrEmpty(usernamefilter)) {
whereQuery.AppendFormat(" AND user_name like '{0}%'", DataCommand.MakeSqlSafe(usernamefilter));
} else if(!string.IsNullOrEmpty(realnamefilter)) {
whereQuery.AppendFormat(" AND user_real_name like '{0}%'", DataCommand.MakeSqlSafe(realnamefilter));
}
if(!string.IsNullOrEmpty(usernameemailfilter)) {
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_email like '{0}%')", DataCommand.MakeSqlSafe(usernameemailfilter));
}
if(activatedfilter != null) {
whereQuery.AppendFormat(" AND user_active = {0}", activatedfilter.Value ? "1" : "0");
}
if(!string.IsNullOrEmpty(rolefilter)) {
whereQuery.AppendFormat(" AND role_name = '{0}'", DataCommand.MakeSqlSafe(rolefilter));
}
if(serviceIdFilter != null) {
whereQuery.AppendFormat(" AND user_service_id = {0}", serviceIdFilter.Value);
}
if(seatFilter != null) {
whereQuery.AppendFormat(" AND user_seat = {0}", seatFilter.Value ? 1 : 0);
}
StringBuilder sortLimitQuery = new StringBuilder();
if(!string.IsNullOrEmpty(sortFieldString)) {
sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString);
if(sortDir != SortDirection.UNDEFINED) {
sortLimitQuery.Append(sortDir.ToString());
}
}
if(limit != null || offset != null) {
sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0);
}
string query = string.Format(@" /* Users_GetByQuery */
select *
from users
{0}
{1}
{2};
select count(*) as totalcount from users {0} {3};
select count(*) as querycount from users {0} {1};",
joinQuery,
whereQuery,
sortLimitQuery,
groupId == null ? string.Empty : "where users.user_id = user_groups.user_id");
Catalog.NewQuery(query)
.Execute(delegate(IDataReader dr) {
while(dr.Read()) {
UserBE u = Users_Populate(dr);
users.Add(u);
}
if(dr.NextResult() && dr.Read()) {
totalCountTemp = DbUtils.Convert.To<uint>(dr["totalcount"], 0);
}
if(dr.NextResult() && dr.Read()) {
queryCountTemp = DbUtils.Convert.To<uint>(dr["querycount"], 0);
}
});
totalCount = totalCountTemp;
queryCount = queryCountTemp;
return users;
}
public IEnumerable <UserBE> Users_GetByQuery(string usernamefilter, string realnamefilter, string usernameemailfilter, string rolefilter, bool?activatedfilter, uint?groupId, uint?serviceIdFilter, bool?seatFilter, SortDirection sortDir, UsersSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount)
{
List <UserBE> users = new List <UserBE>();
uint totalCountTemp = 0;
uint queryCountTemp = 0;
StringBuilder joinQuery = new StringBuilder();
string sortFieldString;
USERS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if (!string.IsNullOrEmpty(rolefilter) || (sortFieldString ?? string.Empty).StartsWith("roles."))
{
joinQuery.Append(@"
left join roles
on users.user_role_id = roles.role_id");
}
if ((sortFieldString ?? string.Empty).StartsWith("services."))
{
joinQuery.AppendFormat(@"
left join services
on users.user_service_id = services.service_id");
}
if (groupId != null)
{
joinQuery.AppendFormat(@"
join groups
on groups.group_id = {0}
join user_groups
on user_groups.group_id = groups.group_id", groupId.Value);
}
StringBuilder whereQuery = new StringBuilder(" where 1=1");
if (groupId != null)
{
whereQuery.AppendFormat(" AND users.user_id = user_groups.user_id");
}
if (!string.IsNullOrEmpty(usernamefilter) && !string.IsNullOrEmpty(realnamefilter))
{
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_real_name like '{1}%')", DataCommand.MakeSqlSafe(usernamefilter), DataCommand.MakeSqlSafe(realnamefilter));
}
else if (!string.IsNullOrEmpty(usernamefilter))
{
whereQuery.AppendFormat(" AND user_name like '{0}%'", DataCommand.MakeSqlSafe(usernamefilter));
}
else if (!string.IsNullOrEmpty(realnamefilter))
{
whereQuery.AppendFormat(" AND user_real_name like '{0}%'", DataCommand.MakeSqlSafe(realnamefilter));
}
if (!string.IsNullOrEmpty(usernameemailfilter))
{
whereQuery.AppendFormat(" AND (user_name like '{0}%' OR user_email like '{0}%')", DataCommand.MakeSqlSafe(usernameemailfilter));
}
if (activatedfilter != null)
{
whereQuery.AppendFormat(" AND user_active = {0}", activatedfilter.Value ? "1" : "0");
}
if (!string.IsNullOrEmpty(rolefilter))
{
whereQuery.AppendFormat(" AND role_name = '{0}'", DataCommand.MakeSqlSafe(rolefilter));
}
if (serviceIdFilter != null)
{
whereQuery.AppendFormat(" AND user_service_id = {0}", serviceIdFilter.Value);
}
if (seatFilter != null)
{
whereQuery.AppendFormat(" AND user_seat = {0}", seatFilter.Value ? 1 : 0);
}
StringBuilder sortLimitQuery = new StringBuilder();
if (!string.IsNullOrEmpty(sortFieldString))
{
sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED)
{
sortLimitQuery.Append(sortDir.ToString());
}
}
if (limit != null || offset != null)
{
sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0);
}
string query = string.Format(@" /* Users_GetByQuery */
select *
from users
{0}
{1}
{2};
select count(*) as totalcount from users {0} {3};
select count(*) as querycount from users {0} {1};",
joinQuery,
whereQuery,
sortLimitQuery,
groupId == null ? string.Empty : "where users.user_id = user_groups.user_id");
Catalog.NewQuery(query)
.Execute(delegate(IDataReader dr) {
while (dr.Read())
{
UserBE u = Users_Populate(dr);
users.Add(u);
}
if (dr.NextResult() && dr.Read())
{
totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0);
}
if (dr.NextResult() && dr.Read())
{
queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0);
}
});
totalCount = totalCountTemp;
queryCount = queryCountTemp;
return(users);
}
public IEnumerable<UserBE> Users_GetByQuery(string userNameFilter, string realNameFilter, string userNameEmailFilter, string roleFilter, bool? activatedFilter, uint? groupId, uint? serviceIdFilter, bool? seatFilter, SortDirection sortDir, UsersSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) {
Stopwatch sw = Stopwatch.StartNew();
var ret = _next.Users_GetByQuery(userNameFilter, realNameFilter, userNameEmailFilter, roleFilter, activatedFilter, groupId, serviceIdFilter, seatFilter, sortDir, sortField, offset, limit, out totalCount, out queryCount);
LogQuery(CATEGORY_USER, "Users_GetByQuery", sw, "userNameFilter", userNameFilter, "realNameFilter", realNameFilter, "userNameEmailFilter", userNameEmailFilter, "roleFilter", roleFilter, "activatedFilter", activatedFilter, "groupId", groupId, "serviceIdFilter", serviceIdFilter, "seatFilter", seatFilter, "sortDir", sortDir, "sortField", sortField, "offset", offset, "limit", limit);
return ret;
}
