/// <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>
/// 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 Security_GetSecurityGroups(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9SecurityGroup order by SecurityGroupName ASC" + Environment.NewLine +
" Select * FROM i9Agency order by AgencyName " + Environment.NewLine +
" Select * FROM i9Module order by Section ASC, ModuleName ASC " + Environment.NewLine +
" Select * FROM i9SecurityTask Order By TaskName ASC " + Environment.NewLine +
" Select * FROM i9SecurityGroupModule " + Environment.NewLine +
" Select * FROM i9SecurityGroupTask ";
Dictionary <string, string> tableMapping = new Dictionary <string, string>()
{
{ "Table", "i9SecurityGroup" },
{ "Table1", "i9Agency" },
{ "Table2", "i9Module" },
{ "Table3", "i9SecurityTask" },
{ "Table4", "i9SecurityGroupModule" },
{ "Table5", "i9SecurityGroupTask" },
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return(response);
}
public DataSet GetAllUser(string connectionstr)
{
sqlacc = new SQLAccess();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_tb_User";//Name of Stored Procedure
cmd.Parameters.AddWithValue("@Mode", 1);
return(sqlacc.GetDataSet(connectionstr, cmd));
}
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);
}
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);
}
private i9Message GetList(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select * from i9Agency order by AgencyName ";
Dictionary <string, string> tableMapping = new Dictionary <string, string>()
{
{ "Table", "i9Agency" },
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return(response);
}
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);
}
/// <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 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 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));
}
/// <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);
}
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>
/// 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 Security_GetPersonnelGroups(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9SecurityGroup order by SecurityGroupName ASC" + Environment.NewLine +
" Select * FROM i9Agency order by AgencyName " + Environment.NewLine +
" Select * FROM i9SecurityGroupPersonnel " + Environment.NewLine +
" Select * FROM i9SysPersonnel ORDER BY LastName, FirstName";
Dictionary <string, string> tableMapping = new Dictionary <string, string>()
{
{ "Table", "i9SecurityGroup" },
{ "Table1", "i9Agency" },
{ "Table2", "i9SecurityGroupPersonnel" },
{ "Table3", "i9SysPersonnel" },
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
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 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);
}
private i9Message EditIncident(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
LawIncidentMessage lim = (LawIncidentMessage)i9Message.XMLDeserializeMessage(typeof(LawIncidentMessage), requestMessage.MsgBody);
string i9EventID = lim.i9EventID;
string SQLIncident = " SELECT * FROM i9Event WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9LawIncident WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9Person WHERE i9ModuleSectionID = '" + i9ModuleSection.LawIncidentPerson.ToString() + "' AND i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9Vehicle WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9Location WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9Property WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) + Environment.NewLine +
" SELECT * FROM i9Offense WHERE 1=2 " + Environment.NewLine +
" SELECT * FROM i9CADServiceCall WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9VehicleRecovery WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9VehicleTowed WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9AttachmentLink WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9Attachment WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9AttachmentData WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9Narrative WHERE i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9Person WHERE i9ModuleSectionID = '" + i9ModuleSection.LawIncidentPersonAKA.ToString() + "' AND i9EventID = " + SQLUtility.SQLString(i9EventID) +
" SELECT * FROM i9PersonSMT WHERE i9EventID = " + SQLUtility.SQLString(i9EventID);
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", "i9VehicleRecovery" },
{ "Table9", "i9VehicleTowed" },
{ "Table10", "i9AttachmentLink" },
{ "Table11", "i9Attachment" },
{ "Table12", "i9AttachmentData" },
{ "Table13", "i9Narrative" },
{ "Table14", "i9PersonAKA" },
{ "Table15", "i9PersonSMT" }
};
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
response.MsgBodyDataSet = da.GetDataSet(SQLIncident, tableMapping);
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.MsgBodyDataSet.AcceptChanges();
response.ErrorStatus.IsError = false;
//===============================================================================
//Retro fit older Events:
// Add i9Narrative to law incident if it does not exist
//===============================================================================
if (response.MsgBodyDataSet.Tables["i9Narrative"].Rows.Count <= 0)
{
DataRow dr = response.MsgBodyDataSet.Tables["i9Narrative"].NewRow();
dr["i9EventID"] = response.MsgBodyDataSet.Tables["i9Event"].Rows[0]["i9EventID"];
dr["i9NarrativeID"] = Guid.NewGuid();
response.MsgBodyDataSet.Tables["i9Narrative"].Rows.Add(dr);
}
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);
}
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);
}