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 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);
}
}
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;
}
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;
}
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 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;
}
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;
}
private i9Message Security_SaveGroupItems(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
StringBuilder sbSQL = new StringBuilder();
SQLGenerator SqlGen = new SQLGenerator();
DataSet ds = requestMessage.MsgBodyDataSet;
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroup"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroupModule"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroupTask"]));
SQLAccess sqla = new SQLAccess();
string SQL = SQLUtility.WrapInTransaction(sbSQL.ToString());
sqla.ExecuteSQL(SQL);
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;
}
