/// <summary> /// GetMFAUsersAll method implementation /// </summary> public override MFAUserList GetMFAUsersAll(DataOrderObject order, bool enabledonly = false) { DataFilterObject filter = new DataFilterObject(); filter.EnabledOnly = enabledonly; return(QueryBuilder(filter, order, null)); }
/// <summary> /// GetMFAUsersCount method implementation /// </summary> public override int GetMFAUsersCount(DataFilterObject filter) { if (filter != null) { return(QueryBuilder(filter, null, null).Count()); } else { List <MFAUser> _lst = _mfausers.GetData(); return(_lst.Count()); } }
/// <summary> /// QueryBuilder method implementation /// </summary> private MFAUserList QueryBuilder(DataFilterObject filter, DataOrderObject order, DataPagingObject paging) { var conditions = LinqFilter.True <MFAUser>(); if (filter != null) { if (filter.FilterisActive) { switch (filter.FilterField) { case DataFilterField.UserName: switch (filter.FilterOperator) { case DataFilterOperator.Equal: conditions = conditions.And(e => e.UPN.Equals(filter.FilterValue)); break; case DataFilterOperator.NotEqual: conditions = conditions.And(e => !e.UPN.Equals(filter.FilterValue)); break; case DataFilterOperator.Contains: conditions = conditions.And(e => e.UPN.Contains(filter.FilterValue)); break; case DataFilterOperator.NotContains: conditions = conditions.And(e => !e.UPN.Contains(filter.FilterValue)); break; case DataFilterOperator.StartWith: conditions = conditions.And(e => e.UPN.StartsWith(filter.FilterValue)); break; case DataFilterOperator.EndsWith: conditions = conditions.And(e => e.UPN.EndsWith(filter.FilterValue)); break; } break; case DataFilterField.Email: switch (filter.FilterOperator) { case DataFilterOperator.Equal: conditions = conditions.And(e => e.MailAddress.Equals(filter.FilterValue)); break; case DataFilterOperator.NotEqual: conditions = conditions.And(e => !e.MailAddress.Equals(filter.FilterValue)); break; case DataFilterOperator.Contains: conditions = conditions.And(e => e.MailAddress.Contains(filter.FilterValue)); break; case DataFilterOperator.NotContains: conditions = conditions.And(e => !e.MailAddress.Contains(filter.FilterValue)); break; case DataFilterOperator.StartWith: conditions = conditions.And(e => e.MailAddress.StartsWith(filter.FilterValue)); break; case DataFilterOperator.EndsWith: conditions = conditions.And(e => e.MailAddress.EndsWith(filter.FilterValue)); break; } break; case DataFilterField.PhoneNumber: switch (filter.FilterOperator) { case DataFilterOperator.Equal: conditions = conditions.And(e => e.PhoneNumber.Equals(filter.FilterValue)); break; case DataFilterOperator.NotEqual: conditions = conditions.And(e => !e.PhoneNumber.Equals(filter.FilterValue)); break; case DataFilterOperator.Contains: conditions = conditions.And(e => e.PhoneNumber.Contains(filter.FilterValue)); break; case DataFilterOperator.NotContains: conditions = conditions.And(e => !e.PhoneNumber.Contains(filter.FilterValue)); break; case DataFilterOperator.StartWith: conditions = conditions.And(e => e.PhoneNumber.StartsWith(filter.FilterValue)); break; case DataFilterOperator.EndsWith: conditions = conditions.And(e => e.PhoneNumber.EndsWith(filter.FilterValue)); break; } break; } if (filter.EnabledOnly) { conditions = conditions.And(e => e.Enabled); } if (filter.FilterMethod != PreferredMethod.None) { conditions = conditions.And(e => !e.PreferredMethod.Equals(filter.FilterMethod)); } } List <MFAUser> _res = null; List <MFAUser> _lst = _mfausers.GetData(); if (order != null) { switch (order.Column) { case DataOrderField.ID: _res = _lst.AsQueryable().Where(conditions).OrderBy(e => e.ID).ToList(); break; case DataOrderField.UserName: _res = _lst.AsQueryable().Where(conditions).OrderBy(e => e.UPN).ToList(); break; case DataOrderField.Email: _res = _lst.AsQueryable().Where(conditions).OrderBy(e => e.MailAddress).ToList(); break; case DataOrderField.Phone: _res = _lst.AsQueryable().Where(conditions).OrderBy(e => e.PhoneNumber).ToList(); break; default: _res = _lst.AsQueryable().Where(conditions).ToList(); break; } } else { _res = _lst.AsQueryable().Where(conditions).ToList(); } if (paging != null) { if (paging.IsActive) { int toprow = paging.PageSize * (paging.CurrentPage - 1); _res = _res.Skip(toprow).Take(paging.PageSize).ToList(); } } return(new MFAUserList(_res)); } else { return(null); } }
/// <summary> /// GetUserRegistrationsCount method implementation /// </summary> internal static int GetUserRegistrationsCount(DataFilterObject filter) { EnsureService(); return(RuntimeRepository.GetMFAUsersCount(Config, filter)); }
/// <summary> /// GetMFAUsers method implementation /// </summary> public override MFAUserList GetMFAUsers(DataFilterObject filter, DataOrderObject order, DataPagingObject paging) { return(QueryBuilder(filter, order, paging)); }
/// <summary> /// GetUserRegistrationsCount method implmentation /// </summary> public override int GetUserRegistrationsCount(DataFilterObject filter) { Dictionary <int, string> fliedlsvalues = new Dictionary <int, string> { { 0, " UPN " }, { 1, " MAILADDRESS " }, { 2, " PHONENUMBER " } }; Dictionary <int, string> operatorsvalues = new Dictionary <int, string> { { 0, " = @FILTERVALUE " }, { 1, " LIKE @FILTERVALUE +'%' " }, { 2, " LIKE '%'+ @FILTERVALUE +'%' " }, { 3, " <> @FILTERVALUE " }, { 4, " LIKE '%'+ @FILTERVALUE " }, { 5, " NOT LIKE '%' + @FILTERVALUE +'%' " } }; Dictionary <int, string> nulloperatorsvalues = new Dictionary <int, string> { { 0, " IS NULL " }, { 1, " IS NULL " }, { 2, " IS NULL " }, { 3, " IS NOT NULL " }, { 4, " IS NOT NULL " }, { 5, " IS NOT NULL " } }; Dictionary <int, string> methodvalues = new Dictionary <int, string> { { 0, " METHOD = 0 " }, { 1, " METHOD = 1 " }, { 2, " METHOD = 2 " }, { 3, " METHOD = 3 " } }; string request = "SELECT COUNT(ID) FROM REGISTRATIONS"; bool hasparameter = (string.Empty != filter.FilterValue); bool hasmethod = false; if (filter.FilterisActive) { if (hasparameter) { request += " WHERE"; string strfields = string.Empty; string stroperator = string.Empty; fliedlsvalues.TryGetValue((int)filter.FilterField, out strfields); request += strfields; if (filter.FilterValue != null) { operatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } else { nulloperatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } request += stroperator; } if (filter.FilterMethod != PreferredMethod.None) { string strmethod = string.Empty; methodvalues.TryGetValue((int)filter.FilterMethod, out strmethod); if (hasparameter) { request += " AND " + strmethod; } else { request += " WHERE " + strmethod; } hasmethod = true; } if (filter.EnabledOnly) { if ((hasparameter) || (hasmethod)) { request += " AND ENABLED=1"; } else { request += " WHERE ENABLED=1"; } } } SqlConnection con = new SqlConnection(_connectionstring); SqlCommand sql = new SqlCommand(request, con); if ((hasparameter) && (filter.FilterValue != null)) { SqlParameter prm = new SqlParameter("@FILTERVALUE", SqlDbType.VarChar); sql.Parameters.Add(prm); prm.Value = filter.FilterValue; } con.Open(); try { return((int)sql.ExecuteScalar()); } catch (Exception ex) { DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000); throw new Exception(ex.Message); } finally { con.Close(); } }
/// <summary> /// GetUserRegistrations method implementation /// </summary> internal static MFAUserList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging) { EnsureService(); return(RuntimeRepository.GetMFAUsers(Config, filter, order, paging)); }
public abstract int GetUserRegistrationsCount(DataFilterObject filter);
/// <summary> /// GetUserRegistrations method implementation /// </summary> public override RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging, int maxrows = 20000) { Dictionary <int, string> fliedlsvalues = new Dictionary <int, string> { { 0, " UPN " }, { 1, " MAILADDRESS " }, { 2, " PHONENUMBER " } }; Dictionary <int, string> operatorsvalues = new Dictionary <int, string> { { 0, " = @FILTERVALUE " }, { 1, " LIKE @FILTERVALUE +'%' " }, { 2, " LIKE '%'+ @FILTERVALUE +'%' " }, { 3, " <> @FILTERVALUE " }, { 4, " LIKE '%'+ @FILTERVALUE " }, { 5, " NOT LIKE '%' + @FILTERVALUE +'%' " } }; Dictionary <int, string> nulloperatorsvalues = new Dictionary <int, string> { { 0, " IS NULL " }, { 1, " IS NULL " }, { 2, " IS NULL " }, { 3, " IS NOT NULL " }, { 4, " IS NOT NULL " }, { 5, " IS NOT NULL " } }; Dictionary <int, string> methodvalues = new Dictionary <int, string> { { 0, " METHOD = 0 " }, { 1, " METHOD = 1 " }, { 2, " METHOD = 2 " }, { 3, " METHOD = 3 " } }; string request = string.Empty; switch (order.Column) { case DataOrderField.UserName: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } break; case DataOrderField.Email: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } break; case DataOrderField.Phone: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } break; default: if (order.Direction == SortDirection.Ascending) { request = "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } else { request = "SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS"; } break; } bool hasparameter = (string.Empty != filter.FilterValue); bool hasmethod = false; if (filter.FilterisActive) { if (hasparameter) { request += " WHERE"; string strfields = string.Empty; string stroperator = string.Empty; fliedlsvalues.TryGetValue((int)filter.FilterField, out strfields); request += strfields; if (filter.FilterValue != null) { operatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } else { nulloperatorsvalues.TryGetValue((int)filter.FilterOperator, out stroperator); } request += stroperator; } if (filter.FilterMethod != PreferredMethod.None) { string strmethod = string.Empty; methodvalues.TryGetValue((int)filter.FilterMethod, out strmethod); if (hasparameter) { request += " AND " + strmethod; } else { request += " WHERE " + strmethod; } hasmethod = true; } if (filter.EnabledOnly) { if ((hasparameter) || (hasmethod)) { request += " AND ENABLED=1"; } else { request += " WHERE ENABLED=1"; } } } if (paging.isActive) { request = "SELECT TOP " + maxrows.ToString() + " NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM (" + request; request += ") AS TBL WHERE NUMBER BETWEEN " + ((paging.CurrentPage - 1) * paging.PageSize + 1) + " AND " + (paging.CurrentPage) * paging.PageSize; } SqlConnection con = new SqlConnection(_connectionstring); SqlCommand sql = new SqlCommand(request, con); if ((hasparameter) && (filter.FilterValue != null)) { SqlParameter prm = new SqlParameter("@FILTERVALUE", SqlDbType.VarChar); sql.Parameters.Add(prm); prm.Value = filter.FilterValue; } RegistrationList regs = new RegistrationList(); con.Open(); try { int i = 0; SqlDataReader rd = sql.ExecuteReader(); while (rd.Read()) { Registration reg = new Registration(); reg.ID = rd.GetInt64(1).ToString(); reg.UPN = rd.GetString(2); if (!rd.IsDBNull(3)) { reg.MailAddress = rd.GetString(3); } if (!rd.IsDBNull(4)) { reg.PhoneNumber = rd.GetString(4); } reg.PIN = rd.GetInt32(5); reg.Enabled = rd.GetBoolean(6); reg.PreferredMethod = (PreferredMethod)rd.GetInt32(7); reg.OverrideMethod = rd.GetString(8); reg.IsRegistered = true; regs.Add(reg); i++; } return(regs); } catch (Exception ex) { DataLog.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000); throw new Exception(ex.Message); } finally { con.Close(); } }
public abstract RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging);
public abstract RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging, int maxrows = 20000);
/// <summary> /// GetUserRegistrations method implementation /// </summary> internal static RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging, int maxrows = 20000) { EnsureService(); return(RuntimeRepository.GetUserRegistrations(Config, filter, order, paging, maxrows)); }
public abstract int GetMFAUsersCount(DataFilterObject filter);
public abstract MFAUserList GetMFAUsers(DataFilterObject filter, DataOrderObject order, DataPagingObject paging);