Exemplo n.º 1
0
        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));
        }
Exemplo n.º 2
0
        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;
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
 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;
 }