/// <summary>
/// Check if Group Name already exists in the database
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private bool CheckIfNewGroupNameIsValidInSystem(string groupName)
{
bool isValid = true;
string sql = "";
if (groupName == null)
{
Logger.LogError(5, "Cannot check if a NULL groupname already exists in the system!");
return(false);
}
if (groupName == String.Empty)
{
Logger.LogError(5, "Cannot check if an empty groupname already exists in the system!");
return(false);
}
string groupNameGiven = DatabaseHelper.SQL_INJECTION_CHECK_PARAMETER(true, groupName);
try {
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
sql = GroupQB.GetCheckIfAGroupISPresentSql(groupNameGiven);
IDataReader reader = DbInfo.RunSqlReader(sql);
while (reader.Read())
{
string groupNameFromDB = "";
if (reader[GroupQB.GROUP_NAME_COL] != System.DBNull.Value)
{
groupNameFromDB = (string)reader[GroupQB.GROUP_NAME_COL];
}
else
{
Logger.LogError(5, "Got Null Group Name using Sql: " + sql);
return(false);
}
groupNameFromDB = DatabaseHelper.SQL_INJECTION_CHECK_PARAMETER(true, groupNameFromDB);
if (groupNameFromDB.Equals(groupNameGiven, StringComparison.CurrentCultureIgnoreCase))
{
isValid = false;
}
else
{
isValid = true;
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error checking if a group name already exists in the system at: " + ex);
isValid = false;
} finally {
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isValid);
}
public bool ChangeUserToGroupAssociation(MGGroup group, List <int> usersIDs, AssociationTypes associationType)
{
bool ISChanged = true;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
string partMSG = "'" + associationType + "ing' (" + usersIDs.Count + ") users to Group '" + group.Name + "'";
try {
Logger.Log("Start " + partMSG);
DbInfo.Connect();
foreach (int userID in usersIDs)
{
if (associationType == AssociationTypes.Assign)
{
sql = GroupQB.GetAssignGroupForUserSql(userID, group.ID);
}
else
{
sql = GroupQB.GetUnAssignGroupForUserSql(userID, group.ID);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
ISChanged = false;
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error " + partMSG + " at: " + ex);
return(false);
} finally {
if (ISChanged)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(ISChanged);
}
/// <summary>
/// Given a MG Group, Add to database
/// </summary>
/// <param name="group">Group to add</param>
/// <returns>Return true if success, false otherwidr</returns>
public bool AddGroup(MGGroup groupToAdd, out string message)
{
bool isAddSuccess = false;
message = string.Empty;
try {
DbInfo = new DatabaseWrapper(Lcf);
//Check if group can be added
if (CheckIfGroupCanBeAdded(groupToAdd, out message))
{
//Insert
string sql = GroupQB.GetInsertGroupSql(groupToAdd);
DbInfo.Connect();
bool success = false;
if (DbInfo.ExecuteSQL(sql, ref success) == 1)
{
isAddSuccess = true;
message = "Successfully added a group: '" + groupToAdd.Name + "'";
}
else
{
message = "Failed to add a group: '" + groupToAdd.Name + "'";
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error adding a group at " + ex);
message = "Error adding a Group " + groupToAdd.Name + ". Contact MGL.";
isAddSuccess = false;
} finally {
if (isAddSuccess)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isAddSuccess);
}
public bool EditGroup(MGGroup newGroup, out string message)
{
bool isAddSuccess = false;
message = string.Empty;
try {
DbInfo = new DatabaseWrapper(Lcf);
if (CheckIfGroupCanBeEdited(newGroup, out message))
{
//Edit
string sql = GroupQB.GetEditGroupSql(newGroup);
DbInfo.Connect();
bool success = false;
if (DbInfo.ExecuteSQL(sql, ref success) == 1)
{
isAddSuccess = true;
message = "Successfully edited group: '" + newGroup.Name + "'";
}
else
{
message = "Failed to edit group: '" + newGroup.Name + "'";
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error editing a group at " + ex);
message = "Error editing a Group " + newGroup.Name + ". Contact MGL.";
isAddSuccess = false;
} finally {
if (isAddSuccess)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isAddSuccess);
}
private List <int> GetDefaultGroupIDs()
{
List <int> defaultGroupIDs = null;
string sql = "";
try {
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
if (!DbInfo.ColumnExists(GroupQB.GROUP_TBLE_NAME, GroupQB.GROUP_DEFAULT_COL))
{
Logger.LogError(5, "Column " + GroupQB.GROUP_DEFAULT_COL + " does not exist in table " + GroupQB.GROUP_TBLE_NAME + ". Cannot get default Group IDs!");
return(null);
}
sql = GroupQB.GetSelectDefaultGroupIdsSql();
defaultGroupIDs = DbInfo.GetIntegerList(sql);
if (defaultGroupIDs == null)
{
Logger.LogError(5, "Failed to get default group IDs!");
return(null);
}
if (defaultGroupIDs.Count == 0)
{
Logger.Log("No default group is found in the system when using SQL " + sql);
}
} catch (Exception ex) {
Logger.LogError(5, "Failed to get default group IDs at: " + ex);
defaultGroupIDs = null;
} finally {
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(defaultGroupIDs);
}
/// <summary>
/// Get Users for a given Group. It populate only (3) three User Information (UserName, JobTitle, Email)
/// </summary>
/// <param name="group">Group for which to find users.</param>
/// <param name="associationTypes">Assigned and Unassigned user to group.</param>
/// <returns></returns>
public List <MGUser> GetUsersForAGroup(MGGroup group, string searchString, AssociationTypes associationTypes)
{
List <MGUser> result = null;
IDataReader reader = null;
string strUserID = null;
int userID = -1;
string sql = "";
string msgPart = "getting users which are '" + associationTypes + "ed' to Group '" + group.Name + "'";
bool isLockAcquired = Monitor.TryEnter(UserAdministration.USER_ADMIN_LOCK_OBJ, UserAdministration.USER_ADMIN_LOCK_TIMEOUT);
if (isLockAcquired)
{
try {
Logger.Log("Start " + msgPart);
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
sql = GroupQB.GetSelectUsersForAGroupSql(group.ID, searchString, associationTypes);
reader = DbInfo.RunSqlReader(sql);
if (reader == null)
{
Logger.LogError(5, "Quitting, failed " + msgPart + " with sql : " + sql);
return(null);
}
result = new List <MGUser>();
while (reader.Read())
{
strUserID = null;
userID = -1;
MGUser user = new MGUser();
//Get USER ID
if (reader[GroupQB.USER_ID_GENERAL_COL] != System.DBNull.Value)
{
strUserID = reader[GroupQB.USER_ID_GENERAL_COL].ToString();
if (!int.TryParse(strUserID, out userID))
{
userID = -1;
Logger.LogError(5, "Error parsing user ID into integer. Quitting");
return(null);
}
}
user.ID = userID;
//Get User Name
if (reader[GroupQB.USER_NAME_COL] != System.DBNull.Value)
{
user.Username = SecureStringWrapper.Encrypt((string)reader[GroupQB.USER_NAME_COL]);
}
else
{
Logger.LogWarning("Null or empty User is found for ID =" + user.ID + ". Please check the database!");
user.Username = SecureStringWrapper.Encrypt("");
}
//Get User EMAIL
if (reader[GroupQB.USER_EMAIL_COL] != System.DBNull.Value)
{
user.Email = SecureStringWrapper.Encrypt((string)reader[GroupQB.USER_EMAIL_COL]);
}
else
{
Logger.LogWarning("Null or empty Email is found for ID =" + user.ID + ". Please check the database!");
user.Email = SecureStringWrapper.Encrypt("");
}
//Get User Job Title
if (reader[GroupQB.USER_JOBTITLE_COL] != System.DBNull.Value)
{
user.JobTitle = SecureStringWrapper.Encrypt((string)reader[GroupQB.USER_JOBTITLE_COL]);
}
else
{
//Logger.LogWarning("Null or empty job title is found for ID =" + user.ID + ". Please check the database!");
user.JobTitle = SecureStringWrapper.Encrypt("");
}
result.Add(user);
}
} catch (Exception ex) {
Logger.LogError(5, "Error " + msgPart + " at: " + ex);
return(null);
} finally {
Monitor.Exit(UserAdministration.USER_ADMIN_LOCK_OBJ);
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
}
else
{
Logger.LogError(5, "Failed to get exclusive lock in GetUsersForAGroup when " + msgPart);
return(null);
}
return(result);
}