public int SaveDataDataset(DataSet ds)
{
string SQL = new SQLGenerator().DataSetSQL(ds);
SQLAccess sqla = new SQLAccess();
return(sqla.ExecuteSQL(SQL));
}
/// <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);
}
private i9Message GetEntireTable(i9Message RequestMessage)
{
string TableName = RequestMessage.MsgBody;
i9Message response = new i9Message();
string sql = "SELECT * FROM " + TableName;
string personnelID = RequestMessage.MessageSecurity.LoginPersonnelID;
switch (TableName.ToUpper())
{
case "i9SysPersonnel":
//Leave out the password from the result set.
sql = "SELECT BadgeNumber, i9SysPersonnelID, FirstName, LastName, MiddleName, OfficerORI, Officer FROM i9SysPersonnel";
break;
//case "i9SecurityGroupTask":
// sql = "SELECT * FROM i9SecurityGroupTask WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID);
// break;
//case "i9SecurityGroupModule":
// sql = "SELECT * FROM i9SecurityGroupModule WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID);
// break;
default:
sql = "SELECT * FROM " + TableName;
break;
}
DataSet ss = new SQLAccess().GetDataSet(sql, TableName);
response.MsgBodyDataSet = ss;
return response;
}
private i9Message DeleteAgency(i9Message requestMessage)
{
string AgencyID = requestMessage.MsgBody.ToString();
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = "DELETE FROM i9Agency WHERE i9AgencyID = " + SQLUtility.SQLString(AgencyID) + " ";
int UpdatedRows = da.ExecuteSQL(SQL);
response.MsgBody = UpdatedRows.ToString();
return response;
}
public int SaveDataTable(DataTable dt)
{
int UpdateCount = 0;
if (dt.Rows.Count > 0)
{
string SQL = new SQLGenerator().DataTableSQL(dt);
SQLAccess sqla = new SQLAccess();
UpdateCount = sqla.ExecuteSQL(SQL);
}
return(UpdateCount);
}
public static void LogThis(string ErrorMessage, LogEventType LogEvent, string BadgeNumber, string AgencyName)
{
bool LogMessage = true;
switch ( LogEvent)
{
case LogEventType.Error:
break;
case LogEventType.Warning:
break;
case LogEventType.Info:
break;
case LogEventType.InMessages:
LogMessage = false;
break;
case LogEventType.OutMessages:
LogMessage = false;
break;
case LogEventType.MessagePath:
LogMessage = false;
break;
}
if (LogMessage == false)
return;
try
{
if (ErrorMessage.Length > 1000)
ErrorMessage = ErrorMessage.Substring(0, 999);
if (BadgeNumber.Length > 23)
BadgeNumber = BadgeNumber.Substring(0, 22);
if (AgencyName.Length > 60)
AgencyName = AgencyName.Substring(0, 59);
AgencyName = "";
string SQL = "INSERT INTO i9SysLog (LogDateTime, LogDescription, LogType, AgencyName, BadgeNumber) VALUES ('" + DateTime.Now.ToString() + "', " + SQLUtility.SQLString(ErrorMessage) + ", " + SQLUtility.SQLString(LogEvent.ToString()) + ", " + SQLUtility.SQLString(AgencyName) + ", " + SQLUtility.SQLString(BadgeNumber) + ")";
SQLAccess da = new SQLAccess();
da.ExecuteSQL(SQL);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private i9Message GetDetail(i9Message requestMessage)
{
string AgencyID = requestMessage.MsgBody.ToString();
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select * from i9Agency WHERE i9AgencyID = " + SQLUtility.SQLString(AgencyID) + " order by AgencyName ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9Agency"},
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
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;
}
private i9Message DeleteIncident(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
LawIncidentMessage lim = (LawIncidentMessage)i9Message.XMLDeserializeMessage(typeof(LawIncidentMessage), requestMessage.MsgBody);
string i9EventID = lim.i9EventID;
string DeleteIncidentSql =
" DELETE FROM i9LawIncident WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9PersonSMT WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Property WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Narrative WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9VehicleRecovery WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9VehicleTowed WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9AttachmentLink WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Attachment WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9AttachmentData WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Person WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Vehicle WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Location WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" DELETE FROM i9Event WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine;
//" DELETE FROM i9Offense WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
//" DELETE FROM i9CADServiceCall WHERE i9EventID = " + SQLUtility.SQLString(i9EventID);
//Stopwatch stopWatch = new Stopwatch();
//stopWatch.Start();
DeleteIncidentSql = SQLUtility.WrapInTransaction(DeleteIncidentSql);
response.MsgBody = da.ExecuteSQL(DeleteIncidentSql).ToString();
//stopWatch.Stop();
//TimeSpan ts = stopWatch.Elapsed;
//string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
//ts.Hours, ts.Minutes, ts.Seconds,
//ts.Milliseconds / 10);
//Console.WriteLine(elapsedTime);
response.ErrorStatus.IsError = false;
return response;
}
/// <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;
}
private i9Message Security_PersonnelGroupTaskGet(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string i9SysPersonnelID = requestMessage.MsgBody;
string SQL1 = @"
SELECT sgm.ModuleName
FROM i9SecurityGroupPersonnel sgp
INNER JOIN i9SecurityGroup sg ON sgp.i9SecurityGroupID = sg.i9SecurityGroupID
LEFT OUTER JOIN i9SecurityGroupModule sgm ON sgm.SecurityGroupName = sg.SecurityGroupName
LEFT OUTER JOIN i9Agency a on a.i9AgencyID = sgp.i9AgencyID
INNER JOIN i9SysPersonnel per on per.i9SysPersonnelID = sgp.i9SysPersonnelID
WHERE per.i9SysPersonnelID = @i9SysPersonnelID
GROUP BY sgm.ModuleName
ORDER BY sgm.ModuleName
";
SQL1 = SQL1.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID));
string SQL2 = @" SELECT * FROM i9SecurityGroupTask WHERE 1=2";
SQL2 = SQL2.Replace("@i9SysPersonnelID", SQLUtility.SQLString(i9SysPersonnelID));
string SQL = SQL1 + " " + Environment.NewLine + " " + SQL2;
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "ModuleName"},
{"Table1", "TaskName"},
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
/// <summary>
/// Get Next Table Key from Main Database.
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public i9Message NextTableKey(i9Message requestMessage)
{
string TableName = requestMessage.MsgBody;
//int KeyValue = 0;
i9Message response = new i9Message();
SQLAccess cda = new SQLAccess();
string sql = @" BEGIN TRAN T1 " +
" SELECT * FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine +
" Update i9TableKey Set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine +
" COMMIT TRAN T1 ";
DataSet ds = cda.GetDataSet(sql, "i9TableKey");
if (ds != null)
{
//if (ds.Tables.Count > 0)
// if (ds.Tables[0].Rows.Count > 0)
// KeyValue = Convert.ToInt32(ds.Tables["i9TableKey"].Rows[0]["KeyValue"].ToString());
response.MsgBodyDataSet = ds;
return response;
}
else
{
throw new Exception("Unable to get the next table key for: " + TableName);
}
}
/// <summary>
/// CodeSaveCodeDetailAdmin
/// </summary>
/// <param name="requestMessage"></param>
/// <returns></returns>
private i9Message CodeAdminSaveDetail(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
SQLAccess da = new SQLAccess();
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9Code"]);
ResponseMessage.ErrorStatus.IsError = false;
}
}
}
catch (Exception ex)
{
ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", "");
ResponseMessage.ErrorStatus.IsError = true;
ResponseMessage.ErrorStatus.ErrorMsg = ex.Message;
}
return ResponseMessage;
}
private i9Message SaveIncident(i9Message mMessage)
{
i9Message response = new i9Message();
DataSet ds = mMessage.MsgBodyDataSet;
StringBuilder sbSQL = new StringBuilder();
SQLGenerator SqlGen = new SQLGenerator();
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Event"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9LawIncident"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9CADServiceCall"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Offense"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Person"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Vehicle"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Location"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Property"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleRecovery"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleTowed"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Narrative"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonAKA"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonSMT"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentLink"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Attachment"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentData"]));
SQLAccess sqla = new SQLAccess();
string SQL = SQLUtility.WrapInTransaction( sbSQL.ToString());
sqla.ExecuteSQL(SQL);
return response;
}
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>
/// GetDynamicEntryAdmin
/// </summary>
/// <param name="mMessage"></param>
/// <returns></returns>
private i9Message GetDynamicEntryAdmin(i9Message mMessage)
{
SQLAccess da = new SQLAccess();
i9Message response = new i9Message();
string SQLLogin = "******" + Environment.NewLine +
" SELECT * FROM i9DynamicEntryConfig " + Environment.NewLine +
" select * from i9DynamicEntryRule " + Environment.NewLine;
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9DynamicEntry"},
{"Table1", "i9DynamicEntryConfig"},
{"Table2", "i9DynamicEntryRule"},
};
response.MsgBodyDataSet = da.GetDataSet(SQLLogin, 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);
}
}
private i9Message LogGetTop100(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " SELECT TOP 500 * FROM i9SysLog ORDER BY LogDateTime DESC " + Environment.NewLine;
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysLog"}
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
public int SaveDataTable(DataTable dt)
{
int UpdateCount = 0;
if (dt.Rows.Count > 0)
{
string SQL = new SQLGenerator().DataTableSQL(dt);
SQLAccess sqla = new SQLAccess();
UpdateCount = sqla.ExecuteSQL(SQL);
}
return UpdateCount;
}
public int SaveDataDataset(DataSet ds)
{
string SQL = new SQLGenerator().DataSetSQL(ds);
SQLAccess sqla = new SQLAccess();
return sqla.ExecuteSQL(SQL);
}
private i9Message NewIncident(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQLIncident = " SELECT * FROM i9Event WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9LawIncident WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9Person WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9Vehicle WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9Location WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9Property WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9Offense WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9CADServiceCall WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9AttachmentLink WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9Attachment WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9AttachmentData WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9VehicleRecovery WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9VehicleTowed WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9Narrative WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9Person WHERE 1=2" + Environment.NewLine +
" SELECT * FROM i9PersonSMT WHERE 1=2" + Environment.NewLine;
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9Event"},
{"Table1", "i9LawIncident"},
{"Table2", "i9Person"},
{"Table3", "i9Vehicle"},
{"Table4", "i9Location"},
{"Table5", "i9Property"},
{"Table6", "i9Offense"},
{"Table7", "i9CADServiceCall"},
{"Table8", "i9AttachmentLink"},
{"Table9", "i9Attachment"},
{"Table10", "i9AttachmentData"},
{"Table11", "i9VehicleRecovery"},
{"Table12", "i9VehicleTowed"},
{"Table13", "i9Narrative"},
{"Table14", "i9PersonAKA"},
{"Table15", "i9PersonSMT"}
};
response.MsgBodyDataSet = da.GetDataSet(SQLIncident, tableMapping);
//***************************************************************************
//* Adding standard table rows - need to move this to the common dll
//***************************************************************************
//Adding Event Information:
DataRow dri9Event = response.MsgBodyDataSet.Tables["i9Event"].NewRow();
dri9Event["i9EventID"] = ServerKeyManager.NewGuid();
dri9Event["i9EventType"] = "Incident";
dri9Event["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID;
response.MsgBodyDataSet.Tables["i9Event"].Rows.Add(dri9Event);
//Adding Incident Information:
DataRow dr = response.MsgBodyDataSet.Tables["i9LawIncident"].NewRow();
dr["i9LawIncidentID"] = ServerKeyManager.NewGuid();
dr["i9EventID"] = dri9Event["i9EventID"];
dr["IncidentNumber"] = ReportNumberManager.GetReportNumber("Incident");
dr["SupplementNumber"] = 1;
dr["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID; //This is the Login user agency (default Agency)
response.MsgBodyDataSet.Tables["i9LawIncident"].Rows.Add(dr);
//Add location incident
dr = response.MsgBodyDataSet.Tables["i9Location"].NewRow();
dr["i9EventID"] = dri9Event["i9EventID"];
dr["i9AgencyID"] = requestMessage.MessageSecurity.AgencyID;
dr["i9LocationID"] = Guid.NewGuid();
dr["i9ModuleSectionID"] = i9ModuleSection.LawIncidentLocation.ToString();
dr["LocationMVI"] = 0;
response.MsgBodyDataSet.Tables["i9Location"].Rows.Add(dr);
//Add i9Narrative to law incident
dr = response.MsgBodyDataSet.Tables["i9Narrative"].NewRow();
dr["i9EventID"] = dri9Event["i9EventID"];
dr["i9NarrativeID"] = Guid.NewGuid();
response.MsgBodyDataSet.Tables["i9Narrative"].Rows.Add(dr);
//=========================================================================
//Relations
//=========================================================================
response.MsgBodyDataSet.Relations.Add("PersonToLocation",
response.MsgBodyDataSet.Tables["i9Person"].Columns["i9PersonID"],
response.MsgBodyDataSet.Tables["i9Location"].Columns["i9PersonID"]);
//response.MsgBodyDataSet.Relations.Add("PersonLocationToPerson",
// response.MsgBodyDataSet.Tables["i9PersonLocation"].Columns["i9PersonID"],
// response.MsgBodyDataSet.Tables["i9Person"].Columns["i9PersonID"]);
//response.MsgBodyDataSet.Relations.Add("PersonLocationToLocation",
// response.MsgBodyDataSet.Tables["i9PersonLocation"].Columns["i9LocationID"],
// response.MsgBodyDataSet.Tables["i9Location"].Columns["i9LocationID"]);
//=========================================================================
//DO NOT EXCEPT CHANGES TO THE DataSet
//=========================================================================
//Because I will not know if the row is new or old.
//response.MsgBodyDataSet.AcceptChanges();
response.ErrorStatus.IsError = false;
return response;
}
/// <summary>
/// CodeGetCodeListAdmin
/// </summary>
/// <param name="requestMessage"></param>
/// <returns></returns>
private i9Message CodeGetCodeListAdmin(i9Message requestMessage)
{
//-------------------------------------------------------------------
//Testing
//SELECT CodeName, CodeValue FROM i9Code group by CodeName, CodeValue having count (*) > 1 order by CodeName
//-------------------------------------------------------------------
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = //" Select * FROM i9Code order by CodeSetName "+ Environment.NewLine +
" Select CodeSetName, i9AgencyID FROM i9Code Group By CodeSetName, i9AgencyID order by CodeSetName " + Environment.NewLine +
" Select * from i9Agency order by AgencyName ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
//{"Table", "i9Code"},
{"Table", "i9Code"},
{"Table1", "i9Agency"},
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
private i9Message PersonnelGetList(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select LastName +', ' + FirstName + ' - ' + BadgeNumber as DisplayName, * FROM i9SysPersonnel order by DisplayName " + Environment.NewLine +
" Select * from i9Agency order by AgencyName ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysPersonnel"},
{"Table1", "i9Agency"},
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
private i9Message PersonnelDelete(i9Message requestMessage)
{
i9Message response = new i9Message();
string i9SysPersonnelID = requestMessage.MsgBody;
SQLAccess da = new SQLAccess();
string SQL = " BEGIN TRAN T1 " +
" DELETE FROM i9SysPersonnelPhone WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" DELETE FROM i9SysPersonnelAddress WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" DELETE FROM i9SysPersonnelAssignment WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" DELETE FROM i9SysPersonnel WHERE i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" COMMIT TRAN T1 ";
int UpdateCount = da.ExecuteSQL(SQL);
// should check the count. if zero then that is bad
response.MsgBody = UpdateCount.ToString();
return response;
}
/// <summary>
/// DynamicEntry_GetTableColumns
/// </summary>
/// <param name="mMessage"></param>
/// <returns></returns>
private i9Message DynamicEntry_GetTableColumns(i9Message mMessage)
{
SQLAccess da = new SQLAccess();
i9Message response = new i9Message();
string ModuleSection = mMessage.MsgBody.Split(new Char[] {','})[0];
string TableName = mMessage.MsgBody.Split(new Char[] { ',' })[1];
string sql = " EXEC TableSchema_Get '" + TableName + "' " + Environment.NewLine ;
//" SELECT * FROM i9DynamicEntryConfig where ModuleSection = '" + ModuleSection + "' " + Environment.NewLine +
//" SELECT * FROM i9DynamicEntryRule der, i9DynamicEntryConfig dec WHERE der.i9DynanicEntryConfigID = dec.i9DynanicEntryConfigID AND dec.ModuleSection = '" + ModuleSection + "' " + Environment.NewLine;
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9TableSchema"},
//{"Table1", "i9DynamicEntryConfig"},
//{"Table2", "i9DynamicEntryRule"},
};
response.MsgBodyDataSet = da.GetDataSet(sql, tableMapping);
response.ErrorStatus.IsError = false;
return response;
}
private i9Message GetSyncData(string LatChanged)
{
i9Message response = new i9Message();
string sql = "";
int TableCounter = 0;
List<string> tables = DataAccessUtilities.SyncTableNames();
Dictionary<string, string> tableMapping = new Dictionary<string, string>();
tables.ForEach(delegate(String TableName)
{
sql += " SELECT * FROM " + TableName + " WHERE LastUpdate >= " + SQLUtility.SQLString(LatChanged.Trim()) + " or LastUpdate is null " + Environment.NewLine;
tableMapping.Add("Table" + (TableCounter == 0 ? "" : TableCounter.ToString()), TableName);
TableCounter++;
});
DataSet ds = new SQLAccess().GetDataSet(sql, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
private i9Message SaveDynamicEntryAdmin(i9Message mRequestMessage)
{
SQLAccess da = new SQLAccess();
i9Message response = new i9Message();
da.SaveDataTable(mRequestMessage.MsgBodyDataSet.Tables["i9DynamicEntryConfig"]);
return response;
}
private i9Message PersonnelGet(i9Message requestMessage)
{
i9Message response = new i9Message();
string i9SysPersonnelID = requestMessage.MsgBody;
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9SysPersonnel where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" Select * from i9SysPersonnelAddress where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" Select * from i9SysPersonnelAssignment where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID) + " " + Environment.NewLine +
" Select * from i9SysPersonnelPhone where i9SysPersonnelID = " + SQLUtility.SQLString(i9SysPersonnelID);
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysPersonnel"},
{"Table1", "i9SysPersonnelAddress"},
{"Table2", "i9SysPersonnelAssignment"},
{"Table3", "i9SysPersonnelPhone"}
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
private i9Message CreateUser(i9Message requestMessage)
{
i9Message response = new i9Message();
string CodeSetName = requestMessage.MsgBody;
CreateUserMessage oCreateUserMessage = (CreateUserMessage)i9Message.XMLDeserializeMessage(typeof(CreateUserMessage), requestMessage.MsgBody);
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9SysPersonnel WHERE Email = " + SQLUtility.SQLString(oCreateUserMessage.Email) + " ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysPersonnel"}
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
if (ds.Tables["i9SysPersonnel"].Rows.Count > 0)
{
response.ErrorStatus.ErrorMsg = "Email already exists in system";
response.ErrorStatus.IsError = true;
return response;
}
else
{
DataRow dr = ds.Tables["i9SysPersonnel"].NewRow();
dr["FirstName"] = oCreateUserMessage.FirstName;
dr["LastName"] = oCreateUserMessage.LastName;
dr["DateTimeInserted"] = DateTime.Now;
dr["DateTimeUpdated"] = DateTime.Now;
dr["Password"] = oCreateUserMessage.Password;
dr["Email"] = oCreateUserMessage.Email;
dr["BadgeNumber"] = oCreateUserMessage.Email;
dr["i9SysPersonnelID"] = Guid.NewGuid();
dr["ActivationGuid"] = Guid.NewGuid();
dr["Enabled"] = 1;
dr["DemoUser"] = 1;
dr["OfficerORI"] = "";
dr["Officer"] = oCreateUserMessage.FirstName + ", " + oCreateUserMessage.LastName;
if (oCreateUserMessage.i9AgencyID == Guid.Empty)
{
dr["i9AgencyID"] = "53A05F38-FC9C-4260-B939-CB1F3998C4D4";
}
else
{
dr["i9AgencyID"] = oCreateUserMessage.i9AgencyID;
}
ds.Tables["i9SysPersonnel"].Rows.Add(dr);
da.SaveDataTable(ds.Tables["i9SysPersonnel"]);
//===============================================================
// Send Email Confirmation
//===============================================================
//string EmailMessage = "";
//string EmailSubject = "Invert911 Activation Confirmation";
//string EmailFrom = "*****@*****.**";
//string EmailTo = oCreateUserMessage.Email;
//EmailUtility.SendEmail(EmailTo,EmailFrom,EmailSubject, EmailMessage);
}
response.ErrorStatus.IsError = false;
return response;
}
/// <summary>
/// CodeGetCodeDetailAdmin
/// </summary>
/// <param name="requestMessage"></param>
/// <returns></returns>
private i9Message CodeAdminGetDetail(i9Message requestMessage)
{
i9Message response = new i9Message();
string CodeSetName = requestMessage.MsgBody;
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9Code where CodeSetName = " + SQLUtility.SQLString(CodeSetName) + " order by CodeText ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9Code"}
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return response;
}
private i9Message PersonnelAdd(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string BlankSQL = " Select * FROM i9SysPersonnel where 1 = 2 ";
Dictionary<string, string> tableMapping = new Dictionary<string, string>()
{
{"Table", "i9SysPersonnel"}
};
DataSet ds = da.GetDataSet(BlankSQL, tableMapping);
DataTable PersonDT = ds.Tables["i9SysPersonnel"];
DataRow NewRD = PersonDT.NewRow();
NewRD["i9SysPersonnelID"] = Guid.NewGuid();
NewRD["FirstName"] = "@NewFirst";
NewRD["LastName"] = "@NewLast " + DateTime.Now.ToString("MM/dd/yyyy hh:mm");
NewRD["i9AgencyID"] = requestMessage.MsgBody.ToString();
NewRD["BadgeNumber"] = DateTime.Now.ToString("MMddyyyyhhmm");
NewRD["Enabled"] = 1;
PersonDT.Rows.Add(NewRD);
string i9SysPersonnelID = NewRD["i9SysPersonnelID"].ToString();
if (da.SaveDataTable(ds.Tables["i9SysPersonnel"]) <= 0)
{
//unable to save new personnel
response.MsgBody = "";
response.ErrorStatus.IsError = true;
response.ErrorStatus.ErrorMsg = "Unable to add new user";
return response;
}
requestMessage.ToBizLayerMsgType = AdminType.SysPer_PersonGet;
requestMessage.MsgBody = i9SysPersonnelID;
return PersonnelGet(requestMessage);
}
private i9Message SearchIncident(i9Message mMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQLIncident = " SELECT i.i9LawIncidentID, i.i9EventID, i.IncidentNumber, i.SupplementNumber, i.StatusCode, i.IncidentReportDate, i.ORI, 'N3,V1,P4', a.AgencyName " +
" FROM i9LawIncident i join i9Agency a on i.i9AgencyID = a.i9AgencyID ";
response.MsgBodyDataSet = da.GetDataSet(SQLIncident, "i9LawIncident");
return response;
}
public static void LogThis(string ErrorMessage, LogEventType LogEvent, string BadgeNumber, string AgencyName)
{
bool LogMessage = true;
switch (LogEvent)
{
case LogEventType.Error:
break;
case LogEventType.Warning:
break;
case LogEventType.Info:
break;
case LogEventType.InMessages:
LogMessage = false;
break;
case LogEventType.OutMessages:
LogMessage = false;
break;
case LogEventType.MessagePath:
LogMessage = false;
break;
}
if (LogMessage == false)
{
return;
}
try
{
if (ErrorMessage.Length > 1000)
{
ErrorMessage = ErrorMessage.Substring(0, 999);
}
if (BadgeNumber.Length > 23)
{
BadgeNumber = BadgeNumber.Substring(0, 22);
}
if (AgencyName.Length > 60)
{
AgencyName = AgencyName.Substring(0, 59);
}
AgencyName = "";
string SQL = "INSERT INTO i9SysLog (LogDateTime, LogDescription, LogType, AgencyName, BadgeNumber) VALUES ('" + DateTime.Now.ToString() + "', " + SQLUtility.SQLString(ErrorMessage) + ", " + SQLUtility.SQLString(LogEvent.ToString()) + ", " + SQLUtility.SQLString(AgencyName) + ", " + SQLUtility.SQLString(BadgeNumber) + ")";
SQLAccess da = new SQLAccess();
da.ExecuteSQL(SQL);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private i9Message PersonnelSave(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
SQLAccess da = new SQLAccess();
//TODO: Need to wrap this in a transaction
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnel"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAddress"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAssignment"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelPhone"]);
ResponseMessage.ErrorStatus.IsError = false;
}
}
}
catch (Exception ex)
{
ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", "");
ResponseMessage.ErrorStatus.IsError = true;
ResponseMessage.ErrorStatus.ErrorMsg = ex.Message;
}
return ResponseMessage;
}
