/// <summary>
/// DisableUserRegistration method implementation
/// </summary>
public MMCRegistration DisableUserRegistration(MMCRegistration reg)
{
if (!HasRegistration(reg.UPN))
{
throw new Exception("The user " + reg.UPN + " cannot be updated ! \r User not found !");
}
string request = "UPDATE REGISTRATIONS SET ENABLED=0 WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm5 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm5);
prm5.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
reg.Enabled = false;
return(reg);
}
/// <summary>
/// DeleteUserRegistration method implementation
/// </summary>
public bool DeleteUserRegistration(MMCRegistration reg)
{
if (!HasRegistration(reg.UPN))
{
throw new Exception("The user " + reg.UPN + " cannot be deleted ! \r User not found !");
}
string request = "DELETE FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
string request2 = "DELETE FROM NOTIFICATIONS WHERE REGISTRATIONID=@REGISTRATIONID";
SqlCommand sql2 = new SqlCommand(request2, con);
SqlParameter prm2 = new SqlParameter("@REGISTRATIONID", SqlDbType.BigInt);
sql2.Parameters.Add(prm2);
prm2.Value = reg.ID;
con.Open();
try
{
int res = sql2.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(true);
}
/// <summary>
/// GetUserRegistration method implementation
/// </summary>
public MMCRegistration GetUserRegistration(string upn)
{
string request = "SELECT ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm);
prm.Value = upn;
MMCRegistration reg = new MMCRegistration();
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
if (rd.Read())
{
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.Enabled = rd.GetBoolean(4);
reg.CreationDate = rd.GetDateTime(5);
reg.PreferredMethod = (RegistrationPreferredMethod)rd.GetInt32(6);
reg.IsRegistered = true;
return(reg);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// DisableUser method implmentation
/// </summary>
public static MMCRegistrationList DisableUser(MMCRegistrationList registrations)
{
EnsureService();
IAdministrationService svc = null;
if (!Config.UseActiveDirectory)
svc = new SQLAdminService(Config);
else
svc = new ADDSAdminService(Config);
MMCRegistrationList lst = new MMCRegistrationList();
foreach(MMCRegistration reg in registrations)
{
MMCRegistration res = svc.DisableUserRegistration(reg);
lst.Add(res);
}
return lst;
}
/// <summary>
/// GetUser method implementation
/// </summary>
internal static MMCRegistrationList GetUser(MMCRegistrationList registrations)
{
EnsureService();
IAdministrationService svc = null;
if (!Config.UseActiveDirectory)
svc = new SQLAdminService(Config);
else
svc = new ADDSAdminService(Config);
MMCRegistrationList lst = new MMCRegistrationList();
foreach(MMCRegistration reg in registrations)
{
MMCRegistration ret = svc.GetUserRegistration(reg.UPN);
lst.Add(ret);
}
return lst;
}
/// <summary>
/// AddUser method implmentation
/// </summary>
public static MMCRegistrationList AddUser(MMCRegistrationList registrations)
{
EnsureService();
IAdministrationService svc = null;
if (!Config.UseActiveDirectory)
svc = new SQLAdminService(Config);
else
svc = new ADDSAdminService(Config);
MMCRegistrationList lst = new MMCRegistrationList();
foreach(MMCRegistration reg in registrations)
{
KeysManager.NewKey(reg.UPN);
MMCRegistration ret = svc.AddUserRegistration(reg);
lst.Add(ret);
}
return lst;
}
/// <summary>
/// GetAllUserRegistration method implementation
/// </summary>
public MMCRegistrationList GetAllUserRegistrations(UsersOrderObject order, int maxrows = 20000, bool enabledonly = false)
{
string request = string.Empty;
if (enabledonly)
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS WHERE ENABLED=1";
}
else
{
request = "SELECT TOP " + maxrows.ToString() + " ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
switch (order.Column)
{
case UsersOrderField.UserName:
request += " ORDER BY UPN";
break;
case UsersOrderField.Email:
request += " ORDER BY MAILADDRESS";
break;
case UsersOrderField.Phone:
request += " ORDER BY PHONENUMBER";
break;
case UsersOrderField.CreationDate:
request += " ORDER BY ID";
break;
default:
request += " ORDER BY CREATIONDATE";
break;
}
if (order.Direction == SortDirection.Descending)
{
request += " DESC";
}
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
MMCRegistrationList regs = new MMCRegistrationList();
con.Open();
try
{
SqlDataReader rd = sql.ExecuteReader();
while (rd.Read())
{
MMCRegistration reg = new MMCRegistration();
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.Enabled = rd.GetBoolean(4);
reg.CreationDate = rd.GetDateTime(5);
reg.PreferredMethod = (RegistrationPreferredMethod)rd.GetInt32(6);
reg.IsRegistered = true;
regs.Add(reg);
}
return(regs);
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// GetUserRegistration method implementation
/// </summary>
public MMCRegistrationList GetUserRegistrations(UsersFilterObject filter, UsersOrderObject order, UsersPagingObject 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 UsersOrderField.UserName:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY UPN DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
break;
case UsersOrderField.Email:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY MAILADDRESS DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
break;
case UsersOrderField.Phone:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY PHONENUMBER DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
break;
case UsersOrderField.CreationDate:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY CREATIONDATE) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY CREATIONDATE DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
break;
default:
if (order.Direction == SortDirection.Ascending)
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD FROM REGISTRATIONS";
}
else
{
request = "SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS NUMBER, ID, UPN, MAILADDRESS, PHONENUMBER, ENABLED, CREATIONDATE, METHOD 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 != UsersPreferredMethod.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, ENABLED, CREATIONDATE, METHOD 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;
}
MMCRegistrationList regs = new MMCRegistrationList();
con.Open();
try
{
int i = 0;
SqlDataReader rd = sql.ExecuteReader();
while (rd.Read())
{
MMCRegistration reg = new MMCRegistration();
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.Enabled = rd.GetBoolean(5);
reg.CreationDate = rd.GetDateTime(6);
reg.PreferredMethod = (RegistrationPreferredMethod)rd.GetInt32(7);
reg.IsRegistered = true;
regs.Add(reg);
i++;
}
return(regs);
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
/// <summary>
/// AddUserRegistration method implementation
/// </summary>
public MMCRegistration AddUserRegistration(MMCRegistration reg)
{
if (HasRegistration(reg.UPN))
{
SetUserRegistration(reg);
return(GetUserRegistration(reg.UPN));
}
string request = "INSERT INTO REGISTRATIONS (UPN, MAILADDRESS, PHONENUMBER, ENABLED, METHOD) VALUES (@UPN, @MAILADDRESS, @PHONENUMBER, @ENABLED, @METHOD)";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm2 = new SqlParameter("@MAILADDRESS", SqlDbType.VarChar);
sql.Parameters.Add(prm2);
if (string.IsNullOrEmpty(reg.MailAddress))
{
prm2.Value = DBNull.Value;
}
else
{
prm2.Value = reg.MailAddress;
}
SqlParameter prm3 = new SqlParameter("@PHONENUMBER", SqlDbType.VarChar);
sql.Parameters.Add(prm3);
if (string.IsNullOrEmpty(reg.PhoneNumber))
{
prm3.Value = DBNull.Value;
}
else
{
prm3.Value = reg.PhoneNumber;
}
SqlParameter prmb = new SqlParameter("@ENABLED", SqlDbType.Bit);
sql.Parameters.Add(prmb);
prmb.Value = reg.Enabled;
SqlParameter prm4 = new SqlParameter("@METHOD", SqlDbType.Int);
sql.Parameters.Add(prm4);
prm4.Value = reg.PreferredMethod;
SqlParameter prm5 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm5);
prm5.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return(GetUserRegistration(reg.UPN));
}
/// <summary>
/// SetUserRegistration method implementation
/// </summary>
public void SetUserRegistration(MMCRegistration reg)
{
if (!HasRegistration(reg.UPN))
{
throw new Exception("The user " + reg.UPN + " cannot be updated ! \r User not found !");
}
string request = "UPDATE REGISTRATIONS SET MAILADDRESS = @MAILADDRESS, PHONENUMBER = @PHONENUMBER, ENABLED= @ENABLED, METHOD=@METHOD WHERE UPN=@UPN";
SqlConnection con = new SqlConnection(_connectionstring);
SqlCommand sql = new SqlCommand(request, con);
SqlParameter prm2 = new SqlParameter("@MAILADDRESS", SqlDbType.VarChar);
sql.Parameters.Add(prm2);
if (string.IsNullOrEmpty(reg.MailAddress))
{
prm2.Value = DBNull.Value;
}
else
{
prm2.Value = reg.MailAddress;
}
SqlParameter prm3 = new SqlParameter("@PHONENUMBER", SqlDbType.VarChar);
sql.Parameters.Add(prm3);
if (string.IsNullOrEmpty(reg.PhoneNumber))
{
prm3.Value = DBNull.Value;
}
else
{
prm3.Value = reg.PhoneNumber;
}
SqlParameter prmb = new SqlParameter("@ENABLED", SqlDbType.Bit);
sql.Parameters.Add(prmb);
prmb.Value = reg.Enabled;
SqlParameter prm4 = new SqlParameter("@METHOD", SqlDbType.Int);
sql.Parameters.Add(prm4);
prm4.Value = reg.PreferredMethod;
SqlParameter prm5 = new SqlParameter("@UPN", SqlDbType.VarChar);
sql.Parameters.Add(prm5);
prm5.Value = reg.UPN;
con.Open();
try
{
int res = sql.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.WriteEntry(ex.Message, System.Diagnostics.EventLogEntryType.Error, 5000);
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
}
