public ActionResult saveList(string relation, string Suspects)
{
JArray relationList = JArray.Parse(relation) as JArray;
JArray SuspectsList = JArray.Parse(Suspects) as JArray;
using (SISS_Context db = new SISS_Context())
{
try {
foreach (JObject item in SuspectsList)
{
string query1 = "INSERT INTO Suspects ([FullName],[NameWithInitials],[NickName],[Address],[CurrentCity],[ContactNumber],[NIC],[BirthDay],[AddBy]) VALUES('" + item["FullName"] + "','" + item["Initials"] + "','" + item["Nick"] + "','" + item["Address"] + "','" + item["NICNum"] + "'," + Int32.Parse(item["Telephone"].ToString()) + ",'" + item["CurrentCity"] + "','" + item["BirthDay"] + "','" + Session["userID"] + "') SELECT SCOPE_IDENTITY() AS [SuspectID]";
var chart1Data = db.Database.SqlQuery <Temp.tempClass1>(query1).ToList();
NewSuspectID = Int32.Parse(chart1Data[0].SuspectID.ToString());
}
foreach (JObject item in relationList)
{
string query = "INSERT INTO Relations ([FullName],[RelationShip],[Addres],[NIC],[ContactNumber],[Discription],[SuspectID]) VALUES('" + item["name"] + "','" + item["relation"] + "','" + item["address"] + "','" + item["nic"] + "','" + item["contact"] + "','" + item["discription"] + "'," + NewSuspectID + ") ";
db.Database.ExecuteSqlCommand(query);
}
}
catch (Exception e) {
return(this.Json(e, JsonRequestBehavior.AllowGet));
}
}
return(Json("List Saved!", JsonRequestBehavior.AllowGet));
}
public ActionResult Authorize(SISS.Models.Login login)
{
using (SISS_Context db = new SISS_Context())
{
var userDetails = db.Login.Where(x => x.userName == login.userName && x.password == login.password).FirstOrDefault();
if (userDetails == null)
{
login.LoginErrorMessage = "*Wrong Employee Number or Password.";//show login erroe message
return(View("Index", login));
}
else
{
Session["userName"] = userDetails.userName.Trim();//retrive USerName of login user
Session["Role"] = userDetails.Role.Trim();
Session["Name"] = db.User.Where(x => x.UserEmployeeNumber == userDetails.UserEmployeeNumber).FirstOrDefault().officerName;
Session["userID"] = db.User.Where(x => x.UserEmployeeNumber == userDetails.UserEmployeeNumber).FirstOrDefault().UserEmployeeNumber;
string username = Session["userName"].ToString();
string role = userDetails.Role.ToString().Trim();//retrive the user role
// if (role.Equals("supervisor"))//if user is supervisor goto the supervisor page
// {
return(RedirectToAction("Index", "Home"));
//}
//else
// return RedirectToAction("Index", "Login");
}
}
}
public ActionResult UploadLocationFiles()
{
for (int i = 0; i < Request.Files.Count; i++)
{
var file = Request.Files[i];
var fileName = Path.GetFileName(file.FileName);
var path = Path.Combine(Server.MapPath("~/Content/Images/Suspects/"), fileName);
file.SaveAs(path);
var dbPath = "../Content/Images/Locations/" + fileName;
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "INSERT INTO LocationImages ([LocationimagePath],[InvestigationId]) VALUES('" + dbPath + "'," + InvestigationId + ")";
db.Database.ExecuteSqlCommand(query);
}
catch (Exception e)
{
return(Json(e.ToString(), JsonRequestBehavior.AllowGet));
}
}
}
return(Json("Successfully Data Added", JsonRequestBehavior.AllowGet));
}
public ActionResult AddUser(User user)
{
try
{
string role = null;
if (user.officerDesignation == "DIG")
{
role = "Administrator";
}
else if (user.officerDesignation == "OIC")
{
role = "Senior Officer";
}
else
{
role = "Normal Officer";
}
using (SISS_Context db = new SISS_Context())
{
string query = "INSERT INTO Users ([officerName],[officerFullName],[officerGender],[officerBirthday],[officerDesignation],[officerJoindDate],[officerNIC],[officerPoliceStation],[officerTelephone],[officerMarrageStatus],[UserEmployeeNumber]) VALUES('" + user.officerName + "','" + user.officerFullName + "','" + user.officerGender + "','" + user.officerBirthday + "','" + user.officerDesignation + "','" + user.officerJoindDate + "','" + user.officerNIC + "','" + user.officerPoliceStation + "','" + user.officerTelephone + "','" + user.officerMarrageStatus + "','" + user.UserEmployeeNumber + "') ";
string query2 = "INSERT INTO Logins([password], [userName], [Role], [UserEmployeeNumber]) VALUES('" + user.UserEmployeeNumber + "','" + user.UserEmployeeNumber + "','" + role + "','" + user.UserEmployeeNumber + "') ";;
db.Database.ExecuteSqlCommand(query);
db.Database.ExecuteSqlCommand(query2);
}
user.UserSuccessMessage = "Sucesfully Saved";//show login erroe message
return(RedirectToAction("addOfficer", "Officer"));
}
catch (Exception e)
{
user.UserErrorMessage = e.ToString();//show login erroe message
return(RedirectToAction("addOfficer", "Officer"));
}
}
// GET: Complain
public ActionResult AddCrimeRecord()
{
SISS_Context db = new SISS_Context();
List <User> userList = db.User.ToList();
ViewBag.userList = userList;
return(View());
}
public ActionResult EditCrimeRepot()
{
SISS_Context db = new SISS_Context();
List <User> userList = db.User.ToList();
List <Crime> crimeList = db.Crime.ToList();
ViewBag.userList = userList;
ViewBag.crimeList = crimeList;
return(View());
}
public ActionResult myCrimes(string userid)
{
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "SELECT * FROM Crimes as c INNER JOIN Investigations i on c.CrimeId=i.CrimeId INNER JOIN CrimeOfficers co on c.CrimeId=co.CrimeId INNER JOIN CrimeImages ci on c.CrimeId=ci.CrimeId INNER JOIN Witnesses w on i.InvestigationId=w.InvestigationId INNER JOIN CrimeSuspects cs on i.InvestigationId=cs.InvestigationId INNER JOIN LocationImages li on li.InvestigationId=i.InvestigationId WHERE co.UserEmployeeNumber=" + userid + "";
var searchData = db.Database.SqlQuery <Temp.tempClass4>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e.ToString(), JsonRequestBehavior.AllowGet));
}
}
}
public ActionResult deleteSuspect(int suspectId)
{
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "DELETE FROM Suspects WHERE SuspectID=" + suspectId;
db.Database.ExecuteSqlCommand(query);
}
catch (Exception e)
{
return(this.Json(e, JsonRequestBehavior.AllowGet));
}
}
return(Json("Suspect Data Deleted!", JsonRequestBehavior.AllowGet));
}
public ActionResult deleteUser(int userId)
{
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "DELETE FROM Users WHERE UserEmployeeNumber='" + userId + "'";
db.Database.ExecuteSqlCommand(query);
}
catch (Exception e)
{
return(this.Json(e, JsonRequestBehavior.AllowGet));
}
}
return(Json("User Data Deleted!", JsonRequestBehavior.AllowGet));
}
public ActionResult findsuspectRecord(int suspectId)
{
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "SELECT * FROM Suspects WHERE SuspectID=" + suspectId;
var searchData = db.Database.SqlQuery <Suspect>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult findOfficer(String KeyWord)
{
using (SISS_Context db = new SISS_Context())
{
string x = KeyWord.Replace("\"", "");
try {
string query = "SELECT * FROM Users WHERE CONCAT(officerFullName,officerBirthday,officerDesignation,officerJoindDate,officerGender,officerMarrageStatus,officerPoliceStation,officerName,officerNIC,UserEmployeeNumber) LIKE '%" + x + "%' ORDER BY officerFullName ASC";
var searchData = db.Database.SqlQuery <User>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult findUser(string userid)
{
String str2 = userid.Replace("\"", "");
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "SELECT * FROM Users WHERE UserEmployeeNumber='" + str2 + "'";
var searchData = db.Database.SqlQuery <User>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult findsuspect(String KeyWord)
{
using (SISS_Context db = new SISS_Context())
{
string x = KeyWord.Replace("\"", "");
try
{
string query = "SELECT * FROM Suspects WHERE CONCAT(FullName,NickName,Address,AddBy,CurrentCity,BirthDay,NIC,ContactNumber,NameWithInitials) LIKE '%" + x + "%' ORDER BY FullName ASC";
var searchData = db.Database.SqlQuery <Suspect>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult findCrimeDetailsforReports(string fromDate, string toDate)
{
string rep1 = fromDate;
string rep2 = toDate;
using (SISS_Context db = new SISS_Context())
{
try
{
string query = "SELECT Crimes.CrimeId, Crimes.CrimeName, Crimes.policeStation, Crimes.crimeLocation, Crimes.ComplaineDate, Investigations.InvestigationStatus FROM Crimes INNER JOIN Investigations ON Crimes.crimeID=Investigations.crimeID WHERE InvestigationStartDate BETWEEN'" + rep1 + "'AND'" + rep2 + "'";
var searchData = db.Database.SqlQuery <Temp.tempClass5>(query).ToList();
return(Json(searchData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult findCrime(string Key)
{
using (SISS_Context db = new SISS_Context())
{
int x = Int32.Parse(Key);
try
{
string query = "SELECT * FROM Crimes as c INNER JOIN Investigations i on c.CrimeId=i.CrimeId INNER JOIN CrimeOfficers co on c.CrimeId=co.CrimeId INNER JOIN CrimeImages ci on c.CrimeId=ci.CrimeId INNER JOIN Witnesses w on i.InvestigationId=w.InvestigationId INNER JOIN CrimeSuspects cs on i.InvestigationId=cs.InvestigationId INNER JOIN LocationImages li on li.InvestigationId=i.InvestigationId INNER JOIN SpecialReports sr on i.InvestigationId=sr.InvestigationId INNER JOIN InvestigationResults ir on i.InvestigationId=ir.InvestigationId WHERE co.CrimeId=" + x;
var CrimeData = db.Database.SqlQuery <Temp.tempClass4>(query).ToList();
return(Json(CrimeData));
}
catch (Exception e)
{
return(Json(e));
}
}
}
public ActionResult updateSuspect(string suspect)
{
JArray suspectList = JArray.Parse(suspect) as JArray;
using (SISS_Context db = new SISS_Context())
{
try
{
foreach (JObject item in suspectList)
{
string query = "UPDATE Suspects SET FullName = '" + item["FullName"] + "', NickName = '" + item["nic"] + "', BirthDay = '" + item["dob"] + "',Address = '" + item["address"] + "',CurrentCity = '" + item["city"] + "',NameWithInitials = '" + item["initial"] + "',NIC = '" + item["nicNum"] + "',ContactNumber = '" + Int32.Parse(item["telephone"].ToString()) + "' WHERE SuspectID='" + item["id"] + "'";
db.Database.ExecuteSqlCommand(query);
}
}
catch (Exception e)
{
return(this.Json(e, JsonRequestBehavior.AllowGet));
}
}
return(Json("Suspect Data Updated!", JsonRequestBehavior.AllowGet));
}
public ActionResult updateUser(string user)
{
JArray userList = JArray.Parse(user) as JArray;
using (SISS_Context db = new SISS_Context())
{
try
{
foreach (JObject item in userList)
{
string query = "UPDATE Users SET officerFullName = '" + item["FullName"] + "', officerName = '" + item["name"] + "', officerBirthday = '" + item["dob"] + "',officerDesignation = '" + item["designation"] + "',officerGender = '" + item["gender"] + "',officerJoindDate = '" + item["joindDate"] + "',officerMarrageStatus = '" + item["marageStatus"] + "',officerPoliceStation = '" + item["station"] + "',officerNIC = '" + item["nic"] + "',officerTelephone = '" + Int32.Parse(item["telephone"].ToString()) + "' WHERE UserEmployeeNumber='" + item["id"] + "'";
db.Database.ExecuteSqlCommand(query);
}
}
catch (Exception e)
{
return(this.Json(e, JsonRequestBehavior.AllowGet));
}
}
return(Json("User Data Updated!", JsonRequestBehavior.AllowGet));
}
public ActionResult saveData(string Courtobj, string SpecialDataobj, string SuspectDataobj, string Investigationobj, string OfficerDataobj, string Complaneobj, string Witnessobj)
{
JArray CourtList = JArray.Parse(Courtobj) as JArray;
JArray SpecialList = JArray.Parse(SpecialDataobj) as JArray;
JArray SuspectList = JArray.Parse(SuspectDataobj) as JArray;
JArray InvestigationList = JArray.Parse(Investigationobj) as JArray;
JArray WitnessList = JArray.Parse(Witnessobj) as JArray;
JArray OfficerList = JArray.Parse(OfficerDataobj) as JArray;
JArray ComplaneList = JArray.Parse(Complaneobj) as JArray;
using (SISS_Context db = new SISS_Context())
{
try
{
foreach (JObject item in ComplaneList)
{
string query1 = "INSERT INTO Crimes ([ComplaineDate],[ComplaineTime],[ComplainantName],[ComplainAddress],[ComplainContactNo],[ComplainTitle],[Statment],[policeStation],[CrimeName],[CrimeLocation],[CrimeType]) VALUES('" + item["ComplaneDate"] + "','" + item["ComplaneTime"] + "','" + item["ComplaniantName"] + "','" + item["ComplaniantAddress"] + "','" + item["ComplaniantContact"] + "','" + item["ComplaniantTitle"] + "','" + item["Statement"] + "','" + item["station"] + "','" + item["ComplaneName"] + "','" + item["location"] + "','" + item["crimeType"] + "') SELECT SCOPE_IDENTITY() AS [CrimeId]";
var query1Data = db.Database.SqlQuery <Temp.tempClass2>(query1).ToList();
CrimeID = Int32.Parse(query1Data[0].CrimeId.ToString());
}
foreach (JObject item in InvestigationList)
{
string query2 = "INSERT INTO Investigations ([InvestigationStartDate],[CrimeLocationInvestigationDetails],[CrimeId],[InvestigationStatus]) VALUES('" + item["startDate"] + "','" + item["locationDetails"] + "'," + CrimeID + ",'PENDING') SELECT SCOPE_IDENTITY() AS [InvestigationId]";
var query1Data = db.Database.SqlQuery <Temp.tempClass3>(query2).ToList();
InvestigationId = Int32.Parse(query1Data[0].InvestigationId.ToString());
}
foreach (JObject item in SuspectList)
{
string query3 = "INSERT INTO CrimeSuspects ([FullName],[Address],[NIC],[ContactNumber],[SuspectStatus],[InvestigationId]) VALUES('" + item["SuspectName"] + "','" + item["Address"] + "','" + item["NIC"] + "','" + item["Contact"] + "','" + item["Status"] + "'," + InvestigationId + ")";
db.Database.ExecuteSqlCommand(query3);
}
foreach (JObject item in SpecialList)
{
string query4 = "INSERT INTO SpecialReports ([ReportType],[SpecialistName],[Designation],[Discription],[InvestigationId]) VALUES('" + item["ReportType"] + "','" + item["SpecialistName"] + "','" + item["Designation"] + "','" + item["Discription"] + "'," + InvestigationId + ")";
db.Database.ExecuteSqlCommand(query4);
}
foreach (JObject item in WitnessList)
{
string query5 = "INSERT INTO Witnesses ([WitnessName],[WitnessAddress],[WitnessNIC],[WitnessTelephone],[WitnessDiscription],[InvestigationId]) VALUES('" + item["FullName"] + "','" + item["Address"] + "','" + item["NIC"] + "','" + item["ContactNumber"] + "','" + item["Discription"] + "'," + InvestigationId + ")";
db.Database.ExecuteSqlCommand(query5);
}
foreach (JObject item in OfficerList)
{
string query6 = "INSERT INTO CrimeOfficers ([UserEmployeeNumber],[CrimeId],[oder]) VALUES('" + item["Officername"] + "'," + CrimeID + "," + 0 + ")";
db.Database.ExecuteSqlCommand(query6);
}
foreach (JObject item in CourtList)
{
string query7 = "INSERT INTO InvestigationResults ([CourtDecision],[Punishment],[Note],[InvestigationId]) VALUES('" + item["CourtDecision"] + "','" + item["Punishment"] + "','" + item["Note"] + "'," + InvestigationId + ")";
db.Database.ExecuteSqlCommand(query7);
}
}
catch (Exception e)
{
return(Json(e.ToString(), JsonRequestBehavior.AllowGet));
}
}
return(Json("done!", JsonRequestBehavior.AllowGet));
}
