private IEnumerable <SelectListItem> GetValidOriginalIDs()
{
string query_string = "SELECT originalID FROM Patient WHERE surgeonID = @p1";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
Command.Parameters.AddWithValue("@p1", Session["surgeonID"].ToString());
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Patient", "originalID", "Select");
var userTypes = new List <SelectListItem>();
while (reader.Read())
{
userTypes.Add(new SelectListItem
{
Value = reader["originalID"].ToString(),
Text = reader["originalID"].ToString()
});
}
db.Close();
return(userTypes);
}
private IEnumerable <SelectListItem> GetEndographBrands()
{
string query_string = "SELECT * FROM Brand";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Brand", null, "Select");
var userTypes = new List <SelectListItem>();
while (reader.Read())
{
userTypes.Add(new SelectListItem
{
Value = reader["brandID"].ToString(),
Text = reader["brandName"].ToString()
});
}
db.Close();
return(userTypes);
}
private Study InsertStudy(Study s)
{
if (s.originalStudyID == null)
{
s.originalStudyID = -1;
}
if (s.studyDescription == null)
{
s.studyDescription = "";
}
if (s.studyDate == null || s.studyDate.Year < 1900 || s.studyDate.Year > 9999)
{
s.studyDate = new DateTime(1900, 1, 1, 0, 0, 0);
}
string query_string = "INSERT INTO Study(originalStudyID, studyDescription, studyDate, delay, patientID) VALUES (@p1, @p2, @p3, @p4, @p5) SET @ID = SCOPE_IDENTITY();";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string, db);
Command.Parameters.AddWithValue("@p1", s.originalStudyID);
Command.Parameters.AddWithValue("@p2", s.studyDescription);
Command.Parameters.AddWithValue("@p3", s.studyDate);
Command.Parameters.AddWithValue("@p4", 0);
Command.Parameters.AddWithValue("@p5", s.patientID);
Command.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
db.Open();
Command.ExecuteNonQuery();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Study", null, "Insert");
s.studyID = Convert.ToInt32(Command.Parameters["@ID"].Value.ToString());
db.Close();
return(s);
}
private Image InsertImage(Image i)
{
string query_string = "INSERT INTO Image(imageOrder, imageFilename, sliceThickness, seriesID) VALUES (@p1, @p2, @p3, @p4) SET @ID = SCOPE_IDENTITY();";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string, db);
Command.Parameters.AddWithValue("@p1", i.imageOrder);
Command.Parameters.AddWithValue("@p2", i.imageFilename);
if (i.sliceThickness != null)
{
Command.Parameters.AddWithValue("@p3", i.sliceThickness);
}
else
{
Command.Parameters.AddWithValue("@p3", DBNull.Value);
}
Command.Parameters.AddWithValue("@p4", i.seriesID);
Command.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
db.Open();
Command.ExecuteNonQuery();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Image", null, "Insert");
i.imageID = Convert.ToInt32(Command.Parameters["@ID"].Value.ToString());
db.Close();
return(i);
}
public ActionResult Testimonial()
{
string userIDforAudit = Request.IsAuthenticated ? Session["userID"].ToString() : null;
string usernameforAudit = Request.IsAuthenticated ? Session["username"].ToString() : null;
AuditController.CreateAuditEntry(userIDforAudit, usernameforAudit, "Testimonial", null, "Select");
return(View(_TestimonialDB.TestimonialViews.ToList()));
}
public ActionResult LogOn(LogOnModel model, string returnUrl)
{
if (ModelState.IsValid)
{
string query_string = "SELECT * FROM Users WHERE Username = @parameter0 AND Password = @parameter1";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
Command.Parameters.AddWithValue("@parameter0", model.UserName);
Command.Parameters.AddWithValue("@parameter1", model.Password);
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(null, model.UserName, "Users", null, "Select");
if (reader.HasRows) //If login credentials return results.
{
reader.Read();
Session["username"] = model.UserName;
Session["userID"] = reader["userID"].ToString();
Session["typeID"] = reader["typeID"].ToString();
CheckAndSetSurgeonID();
FormsService.SignIn(model.UserName, model.RememberMe);
if (!String.IsNullOrEmpty(returnUrl))
{
db.Close();
return(Redirect(returnUrl));
}
else
{
db.Close();
return(RedirectToAction("Index", "Home"));
}
}
else
{
db.Close();
ModelState.AddModelError("", "The user name or password provided is incorrect.");
}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
private int GetPatientIDFromOriginalID(int originalID)
{
string query_string = "SELECT patientID FROM Patient WHERE originalID = @p1 AND surgeonID = @p2;";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string, db);
Command.Parameters.AddWithValue("@p1", originalID);
Command.Parameters.AddWithValue("@p2", Session["surgeonID"].ToString());
db.Open();
SqlDataReader reader = Command.ExecuteReader();
reader.Read();
int patientID = Convert.ToInt32(reader["patientID"].ToString());
db.Close();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Patient", null, "Select");
return(patientID);
}
public ActionResult NewPatient(Patient pt)
{
if (ModelState.IsValid)
{
string insert_string = "INSERT INTO Patient (originalID,sex,age,entryDate,surgeonID) VALUES (@p1,@p2,@p3,CURRENT_TIMESTAMP,@p4);"; //insert statement
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand command = new SqlCommand(insert_string, db);
command.Parameters.AddWithValue("@p1", pt.originalID);
command.Parameters.AddWithValue("@p2", pt.sex);
command.Parameters.AddWithValue("@p3", pt.age);
command.Parameters.AddWithValue("@p4", Session["surgeonID"].ToString());
db.Open();
command.ExecuteNonQuery();
db.Close();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Patient", null, "Insert");
}
return(View());
}
public ActionResult Testimonial(FormCollection postData)
{
if (Request.Form["Search"].ToString().Length > 0)
{
string searchVal = Request.Form["Search"];
if (searchVal.EndsWith(",Search"))
{
searchVal = searchVal.Substring(0, searchVal.LastIndexOf(","));
}
searchVal = searchVal.Replace("--", " ").Replace("'", " ");
var results = from t in _TestimonialDB.TestimonialViews
where t.content.ToLower().Contains(searchVal)
select t;
string userIDforAudit = Request.IsAuthenticated ? Session["userID"].ToString() : null;
string usernameforAudit = Request.IsAuthenticated ? Session["username"].ToString() : null;
AuditController.CreateAuditEntry(userIDforAudit, usernameforAudit, "Testimonial", "content", "Select");
return(View(results.ToList()));
}
else if (Request.Form["Add"].ToString().Length > 0)
{
string addVal = Request.Form["Add"];
if (addVal.EndsWith(",Add"))
{
addVal = addVal.Substring(0, addVal.LastIndexOf(","));
}
string insert_string = "INSERT INTO Testimonial (content,date,surgeonID) VALUES (@p1,CURRENT_TIMESTAMP,@p2);"; //insert statement
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand command = new SqlCommand(insert_string, db);
command.Parameters.AddWithValue("@p1", addVal);
command.Parameters.AddWithValue("@p2", Convert.ToString(Session["surgeonID"]));
db.Open();
command.ExecuteNonQuery();
db.Close();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Testimonial", null, "Insert");
}
return(View(_TestimonialDB.TestimonialViews.ToList()));
}
private void CheckAndSetSurgeonID()
{
if (Session["username"] != null)
{
string query_string = "SELECT * FROM Surgeon WHERE Username = @parameter0";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
Command.Parameters.AddWithValue("@parameter0", Session["username"].ToString());
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Surgeon", "username", "Select");
if (reader.HasRows)
{
reader.Read();
Session["surgeonID"] = reader["surgeonID"];
}
db.Close();
}
}
private IEnumerable <SelectListItem> GetStudySeries(AnalysisModel m)
{
if (m.originalID > 0)
{
string query_string = "SELECT Series.seriesDescription, Study.studyDate FROM Patient INNER JOIN Study ON Patient.patientID = Study.patientID INNER JOIN Series ON Study.studyID = Series.studyID WHERE Patient.originalID = @p1";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
Command.Parameters.AddWithValue("@p1", m.originalID.ToString());
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Patient", "originalID", "Select");
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Study", null, "Select");
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Series", null, "Select");
var ss = new List <SelectListItem>();
while (reader.Read())
{
ss.Add(new SelectListItem
{
Value = reader["studyDate"] + " - " + reader["seriesDescription"],
Text = reader["studyDate"] + " - " + reader["seriesDescription"]
});
}
db.Close();
if (ss.Count < 1)
{
ss.Add(new SelectListItem
{
Value = "No data",
Text = "No data"
}
);
}
return(ss);
}
return(new List <SelectListItem>());
}
public ActionResult Register(RegisterModel model)
{
model.UserTypes = GetAllUserTypes();
model.Institutions = GetAllInstitutions();
if (ModelState.IsValid)
{
// Attempt to register the user
string query_string = "SELECT * FROM Users WHERE Username = @parameter0 OR Email = @parameter1";
SqlConnection db = new SqlConnection(@"Data Source="",1044;Initial Catalog="";User ID="";Password=""");
SqlCommand Command = new SqlCommand(query_string);
Command.Connection = db;
Command.Parameters.AddWithValue("@parameter0", model.UserName);
Command.Parameters.AddWithValue("@parameter1", model.Email);
db.Open();
SqlDataReader reader = Command.ExecuteReader();
AuditController.CreateAuditEntry(null, model.UserName, "Users", null, "Select");
if (reader.HasRows) //User already exists.
{
Alert("Username and/or Email Address already in use.");
db.Close();
reader.Close();
}
else //Work on registering new user.
{
if (model.InstitutionID == null) //If institution does not exist add into database and get reader results after.
{
query_string = "Insert INTO Institution (institutionName, institutionLocation) VALUES (@p0, @p1) SET @ID = SCOPE_IDENTITY();";
Command.CommandText = query_string;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@p0", model.InstitutionName);
Command.Parameters.AddWithValue("@p1", model.InstitutionLocation);
Command.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
reader.Close();
Command.ExecuteNonQuery();
AuditController.CreateAuditEntry(null, model.UserName, "Institution", null, "Insert");
model.InstitutionID = Command.Parameters["@ID"].Value.ToString();
}
string institutionID = model.InstitutionID;
//Get user type ID
string userTypeID = model.UserType;
//Add user to database.
string add_user_insert_string = "INSERT INTO Users(firstname,lastname,username,password,email,typeID) VALUES (@p0,@p1,@p2,@p3,@p4,@p5) SET @ID = SCOPE_IDENTITY();";
Command.CommandText = add_user_insert_string;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@p0", model.FirstName);
Command.Parameters.AddWithValue("@p1", model.LastName);
Command.Parameters.AddWithValue("@p2", model.UserName);
Command.Parameters.AddWithValue("@p3", model.Password);
Command.Parameters.AddWithValue("@p4", model.Email);
Command.Parameters.AddWithValue("@p5", userTypeID);
Command.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
reader.Close();
Command.ExecuteNonQuery();
string userID = Command.Parameters["@ID"].Value.ToString();
AuditController.CreateAuditEntry(userID, model.UserName, "Users", null, "Insert");
//if a Surgeon, add to Surgeon Table as well
if (Convert.ToInt32(userTypeID) == 3)
{
query_string = "Insert INTO Surgeon (firstname,lastname,username,password,email,institutionID) VALUES (@p0,@p1,@p2,@p3,@p4,@p5);";
Command.CommandText = query_string;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@p0", model.FirstName);
Command.Parameters.AddWithValue("@p1", model.LastName);
Command.Parameters.AddWithValue("@p2", model.UserName);
Command.Parameters.AddWithValue("@p3", model.Password);
Command.Parameters.AddWithValue("@p4", model.Email);
Command.Parameters.AddWithValue("@p5", institutionID);
reader.Close();
Command.ExecuteNonQuery();
AuditController.CreateAuditEntry(userID, model.UserName, "Surgeon", null, "Insert");
}
db.Close();
//Sign in.
Session["username"] = model.UserName;
Session["userID"] = userID;
Session["typeID"] = userTypeID;
CheckAndSetSurgeonID();
FormsService.SignIn(model.UserName, false /* createPersistentCookie */);
return(RedirectToAction("Index", "Home"));
}
}
// If we got this far, something failed, redisplay form
ViewData["PasswordLength"] = MembershipService.MinPasswordLength;
return(View(model));
}
public ActionResult Audit()
{
AuditController.CreateAuditEntry(Session["userID"].ToString(), Session["username"].ToString(), "Audit", null, "Select");
return(View(_AuditDB.Audits.ToList()));
}