/// <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>
/// 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>
/// GetMFAUsers method implementation
/// </summary>
public override MFAUserList GetMFAUsers(DataFilterObject filter, DataOrderObject order, DataPagingObject paging)
{
return(QueryBuilder(filter, order, paging));
}
/// <summary>
/// GetUserRegistrations method implementation
/// </summary>
internal static MFAUserList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging)
{
EnsureService();
return(RuntimeRepository.GetMFAUsers(Config, filter, order, paging));
}
/// <summary>
/// GetAllUserRegistrations method implementation
/// </summary>
internal static MFAUserList GetAllUserRegistrations(DataOrderObject order, bool enabledonly = false)
{
EnsureService();
return(RuntimeRepository.GetAllMFAUsers(Config, order, enabledonly));
}
/// <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();
}
}
/// <summary>
/// GetAllUserRegistrations method implementation
/// </summary>
public override RegistrationList GetAllUserRegistrations(DataOrderObject order, int maxrows = 20000, bool enabledonly = false)
{
string request = string.Empty;
if (enabledonly)
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS WHERE ENABLED=1";
}
else
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, PIN, ENABLED, METHOD, OVERRIDE FROM REGISTRATIONS";
}
switch (order.Column)
{
case DataOrderField.UserName:
request += " ORDER BY UPN";
break;
case DataOrderField.Email:
request += " ORDER BY MAILADDRESS";
break;
case DataOrderField.Phone:
request += " ORDER BY PHONENUMBER";
break;
default:
request += " ORDER BY ID";
break;
}
if (order.Direction == SortDirection.Descending)
{
request += " DESC";
}
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
RegistrationList regs = new RegistrationList();
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
while (rd.Read())
{
Registration reg = new Registration();
reg.ID = rd.GetInt64(0).ToString();
reg.UPN = rd.GetString(1);
if (!rd.IsDBNull(2))
{
reg.MailAddress = rd.GetString(2);
}
if (!rd.IsDBNull(3))
{
reg.PhoneNumber = rd.GetString(3);
}
reg.PIN = rd.GetInt32(4);
reg.Enabled = rd.GetBoolean(5);
reg.PreferredMethod = (PreferredMethod)rd.GetInt32(6);
reg.OverrideMethod = rd.GetString(7);
reg.IsRegistered = true;
regs.Add(reg);
}
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 GetAllUserRegistrations(DataOrderObject order, bool enabledonly = false);
public abstract RegistrationList GetUserRegistrations(DataFilterObject filter, DataOrderObject order, DataPagingObject paging, int maxrows = 20000);
/// <summary>
/// GetAllUserRegistrations method implementation
/// </summary>
internal static RegistrationList GetAllUserRegistrations(DataOrderObject order, int maxrows = 20000, bool enabledonly = false)
{
EnsureService();
return(RuntimeRepository.GetAllUserRegistrations(Config, order, maxrows, enabledonly));
}
/// <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 MFAUserList GetMFAUsersAll(DataOrderObject order, bool enabledonly = false);
public abstract MFAUserList GetMFAUsers(DataFilterObject filter, DataOrderObject order, DataPagingObject paging);
