/// <summary>
/// This Method is not used anymore because we switched to a GUID keys
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static long GetSingleTableKey(string TableName)
{
SQLAccess da = new SQLAccess();
long KeyValue = -1;
StringBuilder SQLi9Table = new StringBuilder();
SQLi9Table.AppendLine(" BEGIN TRAN T1 ");
SQLi9Table.AppendLine(" Update i9TableKey set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" SELECT KeyValue FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" COMMIT TRAN T1 ");
string SQL = SQLi9Table.ToString();
DataTable dt = da.GetDataTable(SQLi9Table.ToString(), "Results");
if (dt.Rows.Count > 0)
{
if (DBNull.Value != dt.Rows[0]["KeyValue"])
{
if (String.IsNullOrEmpty(dt.Rows[0]["KeyValue"].ToString()) == false)
{
KeyValue = (long)dt.Rows[0]["KeyValue"];
}
}
}
return(KeyValue);
}
public static string GetReportNumber(string i9ModuleID)
{
SQLAccess da = new SQLAccess();
string ReportNumber = "";
//need a transaction
StringBuilder SQLReportNumber = new StringBuilder();
SQLReportNumber.AppendLine(" BEGIN TRAN T1 ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and ResetReportNumber = 'DAY' ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and CONVERT(date, LastUpdate) != CONVERT(date, getdate()) ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" ELSE ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and year(LastUpdate) != year(getdate()) ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" Update i9ModuleReportNumber set ReportNumber = ReportNumber + 1 WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" select * from i9ModuleReportNumber ");
SQLReportNumber.AppendLine(" COMMIT TRAN T1 ");
string SQL = SQLReportNumber.ToString();
DataTable dt = da.GetDataTable(SQLReportNumber.ToString(), "Results");
if (dt.Rows.Count > 0)
{
ReportNumber = dt.Rows[0]["ReportNumber"].ToString();
if (DBNull.Value != dt.Rows[0]["NumberPrefix"])
if (String.IsNullOrEmpty(dt.Rows[0]["NumberPrefix"].ToString()) == false)
ReportNumber = DateTime.Now.ToString(dt.Rows[0]["NumberPrefix"].ToString()) + ReportNumber;
if (DBNull.Value != dt.Rows[0]["NumberSubFix"])
if (String.IsNullOrEmpty(dt.Rows[0]["NumberSubFix"].ToString()) == false)
ReportNumber = ReportNumber + DateTime.Now.ToString(dt.Rows[0]["NumberSubFix"].ToString());
}
return ReportNumber;
}
/// <summary>
/// This Method is not used anymore because we switched to a GUID keys
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static long GetSingleTableKey(string TableName)
{
SQLAccess da = new SQLAccess();
long KeyValue = -1;
StringBuilder SQLi9Table = new StringBuilder();
SQLi9Table.AppendLine(" BEGIN TRAN T1 ");
SQLi9Table.AppendLine(" Update i9TableKey set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" SELECT KeyValue FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" COMMIT TRAN T1 ");
string SQL = SQLi9Table.ToString();
DataTable dt = da.GetDataTable(SQLi9Table.ToString(), "Results");
if (dt.Rows.Count > 0)
{
if (DBNull.Value != dt.Rows[0]["KeyValue"])
if (String.IsNullOrEmpty(dt.Rows[0]["KeyValue"].ToString()) == false)
KeyValue = (long) dt.Rows[0]["KeyValue"] ;
}
return KeyValue;
}
public i9Message Login(i9Message RequestMessage)
{
i9Message response = new i9Message();
LoginMessage oLoginMessage = (LoginMessage)i9Message.XMLDeserializeMessage(typeof(LoginMessage), RequestMessage.MsgBody);
SQLAccess da = new SQLAccess();
string SQLLogin = "******" + SQLUtility.SQLString(oLoginMessage.UserName) + " and Password = "******"i9SysPersonnel");
if (dt.Rows.Count <= 0)
{
//Just send back an empty table
return response;
}
string i9SysPersonnelID = dt.Rows[0]["i9SysPersonnelID"].ToString();
//Give an activision code to the login user
string UpdateSql = "UPDATE i9SysPersonnel SET ActivationGuid = '" + Guid.NewGuid() + "' WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID);
if (da.ExecuteSQL(UpdateSql) <= 0)
{
//Nothing was updated.
}
//SyncModuleManagerToDB();
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysPersonnel"},
{"Table1", "xxSecurityGroupTask"},
{"Table2", "xxSecurityGroupModule"},
};
string SQL = "SELECT * FROM i9SysPersonnel WHERE i9SysPersonnelID = @i9SysPersonnelID";
SQL = SQL + Environment.NewLine + @"
SELECT sgt.TaskName, sp.BadgeNumber
FROM i9SecurityGroup sg
inner join i9SecurityGroupTask sgt
on sgt.SecurityGroupName = sg.SecurityGroupName
inner join i9SecurityGroupPersonnel sgp
on sgp.i9SecurityGroupID = sg.i9SecurityGroupID
inner join i9SysPersonnel sp
on sp.i9SysPersonnelID = sgp.i9SysPersonnelID
WHERE sp.i9SysPersonnelID = @i9SysPersonnelID
Group by TaskName, BadgeNumber
order by BadgeNumber, TaskName
";
SQL = SQL + Environment.NewLine + @"
SELECT sgm.ModuleName, sp.BadgeNumber
FROM i9SecurityGroup sg
inner join i9SecurityGroupModule sgm
on sgm.SecurityGroupName = sg.SecurityGroupName
inner join i9SecurityGroupPersonnel sgp
on sgp.i9SecurityGroupID = sg.i9SecurityGroupID
inner join i9SysPersonnel sp
on sp.i9SysPersonnelID = sgp.i9SysPersonnelID
WHERE sp.i9SysPersonnelID = @i9SysPersonnelID
Group by ModuleName, BadgeNumber
order by BadgeNumber, ModuleName
";
SQL = SQL.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID) );
response.MsgBodyDataSet = response.MsgBodyDataSet = da.GetDataSet(SQL, tableMapping);
response.ErrorStatus.IsError = false;
return response;
}
/// <summary>
/// Sync Module Manager To DB
/// </summary>
private void SyncModuleManagerToDB()
{
SQLAccess da = new SQLAccess();
//DataTable dt = da.GetDataTable("SELECT * FROM i9Module WHERE ModuleKey = " + SQLUtility.SQLString(mi.i9ModuleID), "i9Module");
DataTable dt = da.GetDataTable("SELECT * FROM i9Module", "i9Module");
for (int i = 0; i < ModuleManager.Instance.Modules.Length - 1; i++)
{
ModuleItem mi = ModuleManager.Instance.Modules[i];
bool found = false;
if (mi.ModuleName.ToUpper() == "CRIME WATCH")
{
Console.Write("");
}
if (mi.ModuleName.ToUpper() == "LOGIN" ||
mi.ModuleName.ToUpper() == "MAIN" ||
mi.ModuleName.ToUpper() == "MAIN MENU" ||
mi.ModuleName.ToUpper() == "SPLASH SCREEN")
{
continue;
}
else
{
foreach (DataRow ModuleRow in dt.Rows)
{
if (ModuleRow["ModuleName"].ToString().ToUpper() == mi.ModuleName.ToUpper())
{
found = true;
break;
}
}
if (found == false)
{
//Why am I adding a row if the table is blank this is strange
DataRow NewModuleRow = dt.NewRow();
NewModuleRow["ClassName"] = mi.ClassName;
NewModuleRow["ModuleName"] = mi.ModuleName;
NewModuleRow["Section"] = mi.Section;
NewModuleRow["PopupPage"] = mi.PopupPage;
NewModuleRow["DesktopEnabled"] = mi.DesktopEnabled;
NewModuleRow["MobileEnabled"] = mi.MobileEnabled;
NewModuleRow["ModuleType"] = mi.ModuleType;
NewModuleRow["ModuleKey"] = mi.i9ModuleID;
NewModuleRow["i9ModuleID"] = Guid.NewGuid();
NewModuleRow["FileName"] = mi.FileName;
dt.Rows.Add(NewModuleRow);
}
}
}
if (dt.DataSet.HasChanges())
{
da.SaveDataTable(dt);
}
}