public static IList <GroupBE> GetGroupsByQuery(DreamContext context, 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.
GroupsSortField sortField = GroupsSortField.UNDEFINED;
if (!String.IsNullOrEmpty(sortFieldString))
{
try { sortField = SysUtil.ChangeType <GroupsSortField>(sortFieldString); } catch { }
}
string groupnamefilter = context.GetParam("groupnamefilter", null);
uint? serviceid = context.GetParam <uint>("authprovider", 0);
if ((serviceid ?? 0) == 0)
{
serviceid = null;
}
return(DbUtils.CurrentSession.Groups_GetByQuery(groupnamefilter, serviceid, sortDir, sortField, offset, limit, out totalCount, out queryCount));
}
public IList<GroupBE> Groups_GetByQuery(string groupNameFilter, uint? serviceIdFilter, SortDirection sortDir, GroupsSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) {
List<GroupBE> result = new List<GroupBE>();
StringBuilder query = new StringBuilder();
if (groupNameFilter != null) {
groupNameFilter = "%" + DataCommand.MakeSqlSafe(groupNameFilter) + "%";
}
string sortFieldString = null;
GROUPS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if ((sortFieldString ?? string.Empty).StartsWith("roles.")) {
query.Append(@"
left join roles
on groups.group_role_id = roles.role_id");
}
if ((sortFieldString ?? string.Empty).StartsWith("services.")) {
query.AppendFormat(@"
left join services
on groups.group_service_id = services.service_id");
}
if (!string.IsNullOrEmpty(groupNameFilter) || serviceIdFilter != null) {
query.Append(" where (1=1)");
if (serviceIdFilter != null) {
query.AppendFormat(" AND group_service_id = {0}", serviceIdFilter.Value);
}
if (!string.IsNullOrEmpty(groupNameFilter)) {
query.AppendFormat(" AND group_name like '{0}'", groupNameFilter);
}
}
if (!string.IsNullOrEmpty(sortFieldString)) {
query.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED) {
query.Append(sortDir.ToString());
}
}
return Groups_GetInternal(query.ToString(), "Groups_GetByQuery", true, limit, offset, out totalCount, out queryCount);
}
public IList <GroupBE> Groups_GetByQuery(string groupNameFilter, uint?serviceIdFilter, SortDirection sortDir, GroupsSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount)
{
List <GroupBE> result = new List <GroupBE>();
StringBuilder query = new StringBuilder();
if (groupNameFilter != null)
{
groupNameFilter = "%" + DataCommand.MakeSqlSafe(groupNameFilter) + "%";
}
string sortFieldString = null;
GROUPS_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString);
if ((sortFieldString ?? string.Empty).StartsWith("roles."))
{
query.Append(@"
left join roles
on groups.group_role_id = roles.role_id");
}
if ((sortFieldString ?? string.Empty).StartsWith("services."))
{
query.AppendFormat(@"
left join services
on groups.group_service_id = services.service_id");
}
if (!string.IsNullOrEmpty(groupNameFilter) || serviceIdFilter != null)
{
query.Append(" where (1=1)");
if (serviceIdFilter != null)
{
query.AppendFormat(" AND group_service_id = {0}", serviceIdFilter.Value);
}
if (!string.IsNullOrEmpty(groupNameFilter))
{
query.AppendFormat(" AND group_name like '{0}'", groupNameFilter);
}
}
if (!string.IsNullOrEmpty(sortFieldString))
{
query.AppendFormat(" order by {0} ", sortFieldString);
if (sortDir != SortDirection.UNDEFINED)
{
query.Append(sortDir.ToString());
}
}
return(Groups_GetInternal(query.ToString(), "Groups_GetByQuery", true, limit, offset, out totalCount, out queryCount));
}
public IList<GroupBE> Groups_GetByQuery(string groupNameFilter, uint? serviceIdFilter, SortDirection sortDir, GroupsSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) {
Stopwatch sw = Stopwatch.StartNew();
var ret = _next.Groups_GetByQuery(groupNameFilter, serviceIdFilter, sortDir, sortField, offset, limit, out totalCount, out queryCount);
LogQuery(CATEGORY_GROUPS, "Groups_GetByQuery", sw, "groupnamefilter", groupNameFilter, "serviceIdFilter", serviceIdFilter, "sortDir", sortDir, "sortField", sortField, "offset", offset, "limit", limit);
return ret;
}
