private int GetRecordsForAGroupFromXref(int groupID, string tbleName)
{
int count = -1;
// string sql = "";
string whereClause = GroupQB.GROUP_ID_COL_XREFTBL + " = " + groupID;
try {
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
count = DbInfo.GetCount(tbleName, whereClause);
if (count == -1)
{
Logger.LogError(5, "Error getting count from table " + tbleName + " Where " + whereClause);
}
} catch (Exception ex) {
Logger.LogError(5, "Error getting count of xref entires for a group with ID = " + groupID + " from table" + tbleName + "' at :" + ex);
count = -1;
} finally {
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(count);
}
/// <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);
}
//-----------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 15-Jul-15 - Alternative to Captcha ... check that:
/// The page has already been requested in the SAME Session
/// A reasonable amount of time ago i.e. not immediately and not for ever -
/// responseSpeed - 1 is default: 5 seconds is about the fastest people could fill the form in and up to 5 minutes is reasonable to kick em out ...
/// 2 is faster between 1 seconds and 5 mins
/// </summary>
public static bool IsHuman(ConfigurationInfo ci, string uniqueSessionID, string pageName, int responseSpeed)
{
bool isHuman = false;
DatabaseWrapper dbInfo = null;
try {
//_____ Connect to the database ...
dbInfo = new DatabaseWrapper(ci);
dbInfo.Connect();
//_____ Create the table if it does not already exist
string selectSQL = "SELECT Page_Request_Date FROM " + logTN
+ " WHERE Session_ID_Unique=" + DataUtilities.Quote(uniqueSessionID)
+ " AND Page_Name=" + DataUtilities.Quote(pageName) + " ORDER BY Page_Request_Date DESC LIMIT 1;";
string[] vals = dbInfo.ReadLine(selectSQL);
if (vals == null || vals.Length != 1)
{
Logger.LogError(111, "Possible Spambot - Session postback but first time session requested this page (PageRequestDate not available when checking IsHuman).");
}
else
{
DateTime origRequest = DateTimeInformation.NullDate;
DateTime.TryParse(vals[0], out origRequest);
TimeSpan t = DateTime.Now.Subtract(origRequest);
// more than 12 seconds is a Loooong time for a bot. But still quite fast for a human.
if (responseSpeed == 1 && t.TotalSeconds > 5 && t.TotalSeconds < 300)
{
isHuman = true;
}
else if (responseSpeed == 2 && t.TotalMilliseconds > 1000 && t.TotalSeconds < 300)
{
isHuman = true;
}
else
{
Logger.LogError(112, "Possible Spambot - The response time in the IsHuman check looks ... inhuman! Page: " + pageName
+ " and Unique Sesh ID: " + uniqueSessionID + ". The total seconds recorded was: " + t.TotalSeconds);
}
}
} catch (Exception ex) {
Logger.LogError(7, "Error checking whether the response is human or not: " + ex.ToString());
} finally {
if (dbInfo != null)
{
dbInfo.Disconnect();
}
}
return(isHuman);
}
private bool DeleteGroupEntryFromXrefTable(int groupID, GroupDeleteTableType groupDeleteTableType)
{
bool isDeleted = false;
int count = -1;
int countDeleted = -1;
string tableName = GetTableNameForGroup(groupDeleteTableType);
string sql = "";
Logger.Log("Start deleting records from table '" + tableName + "' for group id " + groupID);
try {
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
Logger.Log("Getting records from table '" + tableName + "' for group id " + groupID);
count = GetRecordsForAGroupFromXref(groupID, tableName);
if (count > 0)
{
Logger.Log("Start deleting records from '" + tableName + "' for group id = " + groupID);
sql = GroupQB.GetDelteGroupSql(groupID, tableName, false);
bool success = false;
countDeleted = DbInfo.ExecuteSQL(sql, ref success);
if (count == countDeleted)
{
Logger.Log("Successfully deleted " + count + " records from " + tableName + " for group id = " + groupID);
isDeleted = true;
}
else
{
Logger.Log("Failed to delte " + count + " records from " + tableName + " for group id = " + groupID);
}
}
else if (count == 0)
{
Logger.Log("No records was found in table '" + tableName + "' for group id " + groupID);
return(true);
}
} catch (Exception ex) {
Logger.Log("Error deleting records from table '" + tableName + "' for group id " + groupID + " at: " + ex);
return(false);
} finally {
if (isDeleted)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isDeleted);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// Removes all xref records that link the specified user to any groups.
/// 30-Jan-2015 - added the bool toggle on whether or not to use the session wrapper. This causes an issue with threaded applications as in the
/// worker thread the session wrappers are not available.
/// </summary>
/// <param name="userID">The ID of the user to remove from all groups.</param>
/// <returns>True if successfull, false otherwise.</returns>
public bool UnassignAllGroupsFromUser(int userID, bool recordModificationInSessionWrapper)
{
if (userID < 1)
{
Logger.LogError(5, "Cannot UnassignAllGroupsFromUser where userID is not specified!");
return(false);
}
bool isSuccess = false;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
try {
DbInfo.Connect();
sql = GroupQB.GetDeleteUserFromAllGroupsSql(userID);
if (sql == null)
{
Logger.LogError(5, "Failed to get SQL to delete user from all groups! Abandoning UnassignAllGroupsFromUser ...");
return(false);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isSuccess = false;
}
else
{
isSuccess = true;
}
} catch (Exception ex) {
Logger.LogError(5, "Error in changing group association at: " + ex);
return(false);
} finally {
if (isSuccess && recordModificationInSessionWrapper)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isSuccess);
}
//-------------------------------------------------------------------------------------------------------------------------------------------------------------
public DataTable GetUserDetailSQL(int userID)
{
DataTable dataTable = new DataTable();
bool isLockAcquired = Monitor.TryEnter(USER_ADMIN_LOCK_OBJ, USER_ADMIN_LOCK_TIMEOUT);
if (isLockAcquired)
{
DatabaseWrapper db = null;
try {
db = new DatabaseWrapper(AppSecurityContext.MainDbLcf);
db.Connect();
string sql = @"SELECT
ID as UserID,
UserName,
'User' as UserType,
Password,
LastLoginDate as LastLogin,
JobTitle,
Organisation,
Telephone,
Email,
NumberOfIncorrectLogins
FROM " + BaseSecurityOperations.tnUsers + " where ID =" + userID;
IDataReader myReader = db.RunSqlReader(sql);
dataTable.Load(myReader);
} catch (Exception ex) {
Logger.LogError(8, "Problem getting user detail DataTable at " + ex);
return(dataTable);
} finally {
Monitor.Exit(USER_ADMIN_LOCK_OBJ);
if (db != null)
{
db.Disconnect();
}
}
}
else
{
Logger.LogError(8, "Failed to get exclusive lock in GetUserDetailSQL to read the Users table!");
return(dataTable);
}
return(dataTable);
}
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>
/// Change the group association for a given user.
/// It can assign the groups to a user and also can un assign groups linked to a user.
/// 30-Jan-2015 - added the bool toggle on whether or not to use the session wrapper. This causes an issue with threaded applications as in the
/// worker thread the session wrappers are not available.
/// </summary>
/// <param name="groupsIDs">Group Ids to Assign or UnAssign</param>
/// <param name="associationType">Assign or UnAssign</param>
/// <returns>True if successfull, false other wise</returns>
public bool ChangeGroupToUserAssociation(int userID, List <int> groupsIDs, AssociationTypes associationType, bool recordModificationInSessionWrapper)
{
bool isChangeSuccess = true;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
try {
DbInfo.Connect();
foreach (int groupID in groupsIDs)
{
if (associationType == AssociationTypes.Assign)
{
sql = GroupQB.GetAssignGroupForUserSql(userID, groupID);
}
else
{
sql = GroupQB.GetUnAssignGroupForUserSql(userID, groupID);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isChangeSuccess = false;
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error in changing group association at: " + ex);
isChangeSuccess = false;
} finally {
if (isChangeSuccess && recordModificationInSessionWrapper)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
}
return(isChangeSuccess);
}
/// <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);
}
private bool DeleteGroupFromMain(int groupID)
{
bool isDeleted = false;
string sql = "";
string tableName = "";
try {
Logger.Log("Trying to delete the group entry from main table '" + GroupQB.GROUP_TBLE_NAME + "'");
tableName = GetTableNameForGroup(GroupDeleteTableType.Main);
sql = GroupQB.GetDelteGroupSql(groupID, tableName, true);
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isDeleted = false;
}
else
{
isDeleted = true;
}
} catch (Exception ex) {
Logger.LogError(5, "Error deleting the group entry from main table '" + GroupQB.GROUP_TBLE_NAME + "' at: " + ex);
return(false);
} finally {
if (isDeleted)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isDeleted);
}
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);
}
public Dictionary <int, int> GetZoomLevelIdGeoTypeIdLookup()
{
Dictionary <int, int> zoomLevelIdGeoTypeIdLookup = null;
DatabaseWrapper db = null;
IDataReader reader = null;
try
{
Logger.Log("Getting zoom level ID -> geogTypeID lookup ...");
db = new DatabaseWrapper(Lcf);
db.Connect();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT svgTableSIZL_ID, dlg_ID FROM dl_geographies GROUP BY svgTableSIZL_ID;");
string sql = builder.ToString();
reader = db.RunSqlReader(sql);
if (reader == null)
{
Logger.LogError(5, "Error getting reader using SQL " + sql);
}
string svgTableSIZL_ID = null;
string dlg_ID = null;
int intSvgTableSIZL_ID = -1;
int intDlg_ID = -1;
while (reader.Read())
{
svgTableSIZL_ID = null;
dlg_ID = null;
intSvgTableSIZL_ID = -1;
intDlg_ID = -1;
if (zoomLevelIdGeoTypeIdLookup == null)
{
zoomLevelIdGeoTypeIdLookup = new Dictionary <int, int>();
}
if (reader["svgTableSIZL_ID"] != System.DBNull.Value)
{
svgTableSIZL_ID = reader["svgTableSIZL_ID"].ToString();
if (!int.TryParse(svgTableSIZL_ID, out intSvgTableSIZL_ID))
{
Logger.LogError(5, "Non-integer svgTableSIZL_ID read from dl_geographies!");
continue;
}
}
else
{
Logger.LogError(5, "NULL svgTableSIZL_ID read from dl_geographies!");
continue;
}
if (reader["dlg_ID"] != System.DBNull.Value)
{
dlg_ID = reader["dlg_ID"].ToString();
if (!int.TryParse(dlg_ID, out intDlg_ID))
{
Logger.LogError(5, "Non-integer dlg_ID read from dl_geographies!");
continue;
}
}
else
{
Logger.LogError(5, "NULL dlg_ID read from dl_geographies!");
continue;
}
if (!zoomLevelIdGeoTypeIdLookup.ContainsKey(intSvgTableSIZL_ID))
{
zoomLevelIdGeoTypeIdLookup.Add(intSvgTableSIZL_ID, intDlg_ID);
}
}
}
catch (Exception ex)
{
Logger.LogError(5, "Problem in GetZoomLevelIdGeoTypeIdLookup at " + ex);
return(null);
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (db != null)
{
db.Disconnect();
}
}
if (zoomLevelIdGeoTypeIdLookup.Count == 0)
{
Logger.LogError(5, "zoomLevelIdGeoTypeIdLookup was populated with zero records!");
}
return(zoomLevelIdGeoTypeIdLookup);
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
public static void PerformanceTestMySQLWrite()
{
Logger.LogSubHeading("Testing MySQL!");
bool success = false;
// Now lets connect to the MySQL db
Logger.Log("Connecting to the database...");
DatabaseWrapper dbInfo = new DatabaseWrapper(new DatabaseConnectionInfo("localhost", "mysql", "redis_comparison",
SecureStringWrapper.Encrypt("forum"), SecureStringWrapper.Encrypt(TestParameters.RedisAuth), 3306));
dbInfo.Connect();
if (TestParameters.DoWrite == true)
{
bool tnExists = dbInfo.TableExists(TestObj.ObjectName);
// Drop the table if it exists already ...
bool tnDeleted = dbInfo.DeleteTable(TestObj.ObjectName);
// And then recreate it ...
// 22-Aug-2016 - Remove the boolean index to mirror the Redis implementation - Index TestBool(TestBool),
bool tnCreated = dbInfo.CreateTable(TestObj.ObjectName, @"
ID bigint unsigned, Primary Key(ID),
TestInt int, Index TestInt(TestInt),
TestLong bigint, Index TestLong(TestLong),
TestDouble double, Index TestDouble(TestDouble),
TestBool bool,
TestStr Varchar(20), Index TestStr(TestStr),
TestDT DateTime, Index TestDT(TestDT)
", false);
}
Logger.Log("Writing data");
DateTime writeStart = DateTime.Now;
if (TestParameters.DoWrite == true)
{
int writeCount = 0;
foreach (object o in TestParameters.RandomObjects)
{
TestObj rto = (TestObj)o;
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO " + TestObj.ObjectName + " (ID, TestInt, TestLong, TestDouble, TestBool, TestStr, TestDT) VALUES (");
sql.Append(rto.ID + ",");
sql.Append(rto.TestInt + ",");
sql.Append(rto.TestLong + ",");
sql.Append(rto.TestDouble + ",");
sql.Append(rto.TestBool + ",");
sql.Append(DataUtilities.Quote(rto.TestStr) + ",");
sql.Append(DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(rto.TestDT, true, true)));
sql.Append(");");
dbInfo.ExecuteSQL(sql.ToString(), ref success);
Logger.Log(++writeCount, 100, TestParameters.RandomObjects.Count);
}
}
Logger.Log("");
Logger.Log("Writing completed.");
TestParameters.WriteMySQL = DateTime.Now.Subtract(writeStart);
Logger.Log("Disconnect from the database.");
dbInfo.Disconnect();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// So this loads the full object for each search match found ... but does not persist those as that would likely kill the memory allocation
/// </summary>
/// <param name="chunkSize"></param>
/// <param name="startIndex"></param>
public static void PerformanceTestMySQLThreadBlock(int chunkSize, int startIndex)
{
Logger.LogSubHeading("Testing MySQL!");
//bool success = false;
int i = startIndex;
if ((startIndex + chunkSize) >= TestParameters.PatternsToSearchFor.Count)
{
chunkSize = TestParameters.PatternsToSearchFor.Count - startIndex;
}
Logger.Log("Starting chunk with index " + startIndex + " and size " + chunkSize + " ...");
// HashSet<ulong> ids = new HashSet<ulong>();
// Now lets connect to the MySQL db
Logger.Log("Connecting to the database...");
DatabaseWrapper dbInfo = new DatabaseWrapper(new DatabaseConnectionInfo("localhost", "mysql", "redis_comparison",
SecureStringWrapper.Encrypt("forum"), SecureStringWrapper.Encrypt(TestParameters.RedisAuth), 3306));
dbInfo.Connect();
Logger.Log("Reading data");
StringBuilder log = new StringBuilder();
StringBuilder queryTxt = new StringBuilder();
//long searchIndex = startIndex;
for (i = startIndex; i < (startIndex + chunkSize); i++)
{
List <DatabaseSearchPattern> rsps = TestParameters.PatternsToSearchFor[i];
/////////////
//bool storeQuery = false;
StringBuilder sql = new StringBuilder();
StringBuilder searchParams = new StringBuilder();
// And build the query here ...
sql.Append("SELECT ID, TestInt, TestLong, TestDouble, TestBool, TestStr, TestDT FROM " + TestObj.ObjectName + " WHERE ");
foreach (DatabaseSearchPattern rsp in rsps)
{
if (searchParams.Length > 0)
{
searchParams.Append(" AND ");
}
if (rsp.SearchType == SearchType.PrimaryKey)
{
searchParams.Append(" " + rsp.Parameter + "=" + rsp.Score);
}
else if (rsp.SearchType == SearchType.Text)
{
searchParams.Append(" " + rsp.Parameter + " LIKE " + DataUtilities.Quote(rsp.PatternStartsWith(RedisWrapper.TextIndexLength) + "%"));
//searchParams.Append(" " + rsp.Parameter + " LIKE " + DataUtilities.Quote(rsp.Pattern.Substring(0, 1) + "%"));
}
else if (rsp.SearchType == SearchType.Bool)
{
searchParams.Append(" " + rsp.Parameter + "=" + rsp.Score);
}
else if (rsp.SearchType == SearchType.DateTime)
{
// could also add switches on ==, >= and <=
if (rsp.SearchComparisonExpression == SearchComparisonExpression.Equivalent)
{
//if (rsp.ScoreMin == rsp.ScoreMax) { // an exact date and time
searchParams.Append(rsp.Parameter + " = "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime(rsp.Score), true, true)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeLessThanOrEqualTo)
{
//} else if (rsp.ScoreMin == DateTime.MinValue.Ticks) {
// less than or equal to ...
// searchParams.Append(rsp.Parameter + " <= "
// + DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMax)), true, true)));
// Note that we want to search inclusively. Which means we need to search less than the specified max day PLUS A DAY
// equivalent to <= 2016-04-20 23:59:59 to ensure that the day itself is included ....
//DateTime dt = new DateTime((long)Math.Round(rsp.ScoreMax));
DateTime dt = new DateTime((long)rsp.ScoreMax);
searchParams.Append(rsp.Parameter + " <= "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(dt, true, true)));
/////////////////////////////////////
//string checkTHIS = "";
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeGreaterThanOrEqualTo)
{
//} else if (rsp.ScoreMax == DateTime.MaxValue.Ticks) { // greater than or equal to ...
//DateTime dt = new DateTime((long)Math.Round(rsp.ScoreMin));
DateTime dt = new DateTime((long)rsp.ScoreMin);
searchParams.Append(rsp.Parameter + " >= "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(dt, true, true)));
/////////////////////////////////////
//string checkTHIS = rsp.AsText();
//checkTHIS = rsp.AsText();
//storeQuery = true;
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeBetween)
{
// } else { // Must be a real between
// Note that we want to search inclusively. Which means we need to search less than the specified max day PLUS A DAY
// equivalent to <= 2016-04-20 23:59:59 to ensure that the day itself is included ....
searchParams.Append(rsp.Parameter + " BETWEEN "
//+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMin)), true, true))
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)rsp.ScoreMin), true, true))
+ " AND "
//+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMax)), true, true)));
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)rsp.ScoreMax), true, true)));
}
else
{
string ohFuck = "";
}
}
else if (rsp.SearchType == SearchType.Score)
{
if (rsp.SearchComparisonExpression == SearchComparisonExpression.Equivalent)
{
//if (rsp.ScoreMin == rsp.ScoreMax) { // an exact value
searchParams.Append(rsp.Parameter + " = " + rsp.ScoreMin.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeLessThanOrEqualTo)
{
//} else if (rsp.ScoreMin == Double.MinValue) { // less than or equal to ...
searchParams.Append(rsp.Parameter + " <= " + rsp.ScoreMax.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeGreaterThanOrEqualTo)
{
//} else if (rsp.ScoreMax == Double.MaxValue) { // greater than or equal to ...
searchParams.Append(rsp.Parameter + " >= " + rsp.ScoreMin.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeBetween)
{
//} else { // Must be a real between
searchParams.Append(rsp.Parameter + " BETWEEN "
+ rsp.ScoreMin.ToString("0." + new string('#', 339)) + " AND "
+ rsp.ScoreMax.ToString("0." + new string('#', 339)));
}
else
{
string ohFuck = "";
}
}
}
sql.Append(searchParams);
sql.Append(";");
List <string[]> sqlData = dbInfo.GetDataList(sql.ToString());
int objCount = 0;
if (sqlData != null)
{
objCount = sqlData.Count;
foreach (string[] row in sqlData)
{
ulong id = 0;
ulong.TryParse(row[0], out id);
int testInt = 0;
int.TryParse(row[1], out testInt);
long testLong = 0;
long.TryParse(row[2], out testLong);
double testDouble = 0;
double.TryParse(row[3], out testDouble);
bool testBool = (row[4] != null && row[4].Equals("1") == true) ? true : false;
// string is obvs good to go!!
DateTime testDT = DateTimeInformation.FormatDateTime(row[6]);
TestObj rto = new TestObj(id, testInt, testLong, testDouble, testBool, row[5], testDT);
// Keep adding the objects, as long as they have not already been added and the MaxNumObjects is not exceeded - probably 1m
// Now append the ids to the global list ...
lock (IDs) {
if (IDs.Contains((uint)rto.ID) == false)
{
IDs.Add((uint)rto.ID);
TestObjects.Add(rto);
}
}
}
}
// Do the printing of all the specific queries only if DoDebug is set to true ....
if (RedisWrapper.DoDebug == true)
{
// 19-Aug-2016 - F**k a duck - DO NOT SORT THE QUERIES HERE
// We need to make sure not to sort the master list as it potentially confuses the calculations in the search objects method ...
queryTxt.Clear();
foreach (DatabaseSearchPattern rsp in rsps)
{
queryTxt.Append(" " + rsp.AsText());
}
log.Append("\n" + "Search " + i + " found " + objCount + " objects. Query Text: " + queryTxt);
}
//foreach (RedisSearchPattern rsp in rsps) {
// queryTxt.Append(" " + rsp.AsText());
//}
////log.Append("\n" + "Search " + (++searchIndex) + " found " + objCount + " objects. Query Text: " + queryTxt);
//log.Append("\n" + "Search " + (++searchIndex) + " found " + objCount + " objects. Query Text: " + queryTxt + "\t" + sql);
////////////////////////////
//if (storeQuery == true && (sqlData == null || sqlData.Count == 0)) {
// log.Append("\t" + sql);
//}
if (TestParameters.UseThreading == true)
{
DNThreadManager.IncrementNumIterationsCompleted();
}
else
{
Logger.Log(i, 10, TestParameters.PatternsToSearchFor.Count);
}
}
Logger.Log("");
Logger.Log(log.ToString());
// And lastly, lets disconnect from the database!
dbInfo.Disconnect();
}
/// <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);
}
