/// <summary>
/// Gets List of Patients
/// </summary>
/// <returns>List of Patient Objects</returns>
public static List<Patient> GetPatientList()
{
List<Patient> patientList = new List<Patient>();
SqlConnection connection = MedassistDB.GetConnection();
String selectStatement = "SELECT PatientID, SSN, FirstName, MInit, LastName, DOB, Gender, " +
"StreetAddress1, StreetAddress2, Phone, City, State, ZipCode FROM Patients";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Patient patient = new Patient();
patient.PatientID = (int)reader["PatientID"];
patient.SSN = reader["SSN"].ToString();
patient.FirstName = reader["FirstName"].ToString();
patient.Phone = reader["Phone"].ToString();
patient.MInit = reader["MInit"].ToString();
patient.LastName = reader["LastName"].ToString();
patient.DOB = (DateTime)reader["DOB"];
patient.Gender = reader["Gender"].ToString()[0];
patient.StreetAddr1 = reader["StreetAddress1"].ToString();
patient.StreetAddr2 = reader["StreetAddress2"].ToString();
patient.City = reader["City"].ToString();
patient.State = reader["State"].ToString();
patient.ZipCode = reader["ZipCode"].ToString();
patientList.Add(patient);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
return patientList;
}
public static List<Visit> GetListVisitDates(string firstName, string lastName)
{
List<Visit> visitList = new List<Visit>();
SqlConnection connection = MedassistDB.GetConnection();
String selectStatement = "SELECT VisitID, VisitDate " +
"FROM Visits JOIN Patients ON Visits.PatientID = Patients.PatientID " +
"WHERE Patients.FirstName = @FirstName AND Patients.LastName = @LastName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
selectCommand.Parameters.AddWithValue("@FirstName", firstName);
selectCommand.Parameters.AddWithValue("@LastName", lastName);
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Patient patient = new Patient();
Visit visit = new Visit();
visit.VisitID = (int)reader["VisitID"];
visit.VisitDate = (DateTime)reader["VisitDate"];
visitList.Add(visit);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
return visitList;
}
/// <summary>
/// submits new patient info
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSubmit_Click(object sender, EventArgs e)
{
if (IsValidData())
{
patient = new Patient();
PutPatientData(patient);
try
{
patient.PatientID = PatientController.CreatePatient(patient);
DialogResult = DialogResult.OK;
MessageBox.Show("Patient Accepted");
Close();
}
catch (Exception ex)
{
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
/// <summary>
/// Query to add patients
/// </summary>
/// <param name=patient"></param>
/// <returns>incident added</returns>
public static int AddPatient(Patient patient)
{
SqlConnection connection = MedassistDB.GetConnection();
string insertStatement =
"Insert Patients " +
"(FirstName, MInit, LastName, SSN, StreetAddress1, StreetAddress2, City, State, ZipCode, Phone, DOB, Gender) " +
"Values (@FirstName, @MInit, @LastName, @SSN, @StreetAddress1, @StreetAddress2, @City, @State, @ZipCode, @Phone, @DOB, @Gender)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@SSN", patient.SSN);
insertCommand.Parameters.AddWithValue("@FirstName", patient.FirstName);
insertCommand.Parameters.AddWithValue("@LastName", patient.LastName);
insertCommand.Parameters.AddWithValue("@MInit", patient.MInit);
insertCommand.Parameters.AddWithValue("@StreetAddress1", patient.StreetAddr1);
insertCommand.Parameters.AddWithValue("@StreetAddress2", patient.StreetAddr2);
insertCommand.Parameters.AddWithValue("@City", patient.City);
insertCommand.Parameters.AddWithValue("@State", patient.State);
insertCommand.Parameters.AddWithValue("@ZipCode", patient.ZipCode);
insertCommand.Parameters.AddWithValue("@Phone", patient.Phone);
insertCommand.Parameters.AddWithValue("@DOB", patient.DOB);
insertCommand.Parameters.AddWithValue("@Gender", patient.Gender);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"Select Ident_Current('Patients') FROM Patients";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int patientID = Convert.ToInt32(selectCommand.ExecuteScalar());
return patientID;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Update Patient Info
/// </summary>
/// <param name="oldPatient"></param>
/// <param name="newPatient"></param>
/// <returns>If the update is successful or not</returns>
public static bool UpdatePatient(Patient oldPatient, Patient newPatient)
{
SqlConnection connection = MedassistDB.GetConnection();
string updateStatement =
"UPDATE Patients SET " +
"FirstName = @NewFirstName, " +
"LastName = @NewLastName, " +
"MInit = @NewMInit, " +
"StreetAddress1 = @NewStreetAddress1, " +
"StreetAddress2 = @NewStreetAddress2, " +
"Phone = @NewPhone, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode " +
"WHERE PatientID = @OldPatientID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewFirstName", newPatient.FirstName);
updateCommand.Parameters.AddWithValue("@NewLastName", newPatient.LastName);
updateCommand.Parameters.AddWithValue("@NewMInit", newPatient.MInit);
updateCommand.Parameters.AddWithValue("@NewStreetAddress1", newPatient.StreetAddr1);
updateCommand.Parameters.AddWithValue("@NewStreetAddress2", newPatient.StreetAddr2);
updateCommand.Parameters.AddWithValue("@NewPhone", newPatient.Phone);
updateCommand.Parameters.AddWithValue("@NewCity", newPatient.City);
updateCommand.Parameters.AddWithValue("@NewState", newPatient.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newPatient.ZipCode);
updateCommand.Parameters.AddWithValue("@OldPatientID", oldPatient.PatientID);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
}
}
/// <summary>
/// Gets List of visits for patients by there first and last name
/// Added by Greene
/// </summary>
/// <returns></returns>
public static List<Patient> GetVisitForPatientWithDobAndLName(string lName, DateTime patientDob)
{
List<Patient> patientList = new List<Patient>();
SqlConnection connection = MedassistDB.GetConnection();
var selectStatement = @"
SELECT
Visits.VisitID
,Visits.VisitDate
,Visits.PatientID AS VPatientID
,Visits.Diagnosis
,Visits.Systolic
,Visits.Diastolic
,Visits.Temperature
,Visits.RespirationRate
,Visits.HeartRate
,Visits.Symptoms
,Patients.PatientID
,Patients.FirstName
,Patients.MInit
,Patients.LastName
,Patients.DOB
,Orders.Result
,Orders.TestID
,Tests.TestName
FROM Patients
LEFT JOIN Visits
ON Visits.PatientID = Patients.PatientID
LEFT JOIN Orders ON Visits.VisitID = Orders.VisitID
LEFT JOIN Tests ON Orders.TestID = Tests.TestID
WHERE
Patients.LastName = @lName
AND Patients.DOB = @PatientDob";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
selectCommand.Parameters.AddWithValue("@Lname", lName);
selectCommand.Parameters.AddWithValue("@PatientDob", patientDob);
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Patient patient = new Patient();
if (reader["VisitID"] == DBNull.Value)
{
int visitID = (reader["VisitID"] as int?) ?? 0;
}
else
{
patient.VisitID = (int?)reader["VisitID"];
}
if (reader["VisitDate"] == DBNull.Value)
{
DateTime? visitDate = (reader["VisitDate"] as DateTime?) ?? null;
}
else
{
patient.VisitDate = (DateTime?)reader["VisitDate"];
}
patient.PatientID = (int)reader["PatientID"];
patient.FirstName = reader["FirstName"].ToString();
if (reader["MINit"].ToString() != null)
{
patient.MInit = reader["MINit"].ToString();
}
else
{
patient.MInit = null;
}
patient.LastName = (string)reader["LastName"];
patient.DOB = (DateTime)reader["DOB"];
if (reader["Systolic"] == DBNull.Value)
{
int systolic = (reader["Systolic"] as int?) ?? 0;
}
else
{
patient.Systolic = (int)reader["Systolic"];
}
if (reader["Diastolic"] == DBNull.Value)
{
int diastolic = (reader["Diastolic"] as int?) ?? 0;
}
else
{
patient.Diastolic = (int)reader["Diastolic"];
}
if (reader["Temperature"] == DBNull.Value)
{
decimal temperature = (reader["Temperature"] as decimal?) ?? 0;
}
else
{
patient.Temperature = (decimal?)reader["Temperature"];
}
if (reader["RespirationRate"] == DBNull.Value)
{
int respirationRate = (reader["RespirationRate"] as int?) ?? 0;
}
else
{
patient.RespirationRate = (int)reader["RespirationRate"];
}
if (reader["HeartRate"] == DBNull.Value)
{
int heartRate = (reader["HeartRate"] as int?) ?? 0;
}
else
{
patient.HeartRate = (int)reader["HeartRate"];
}
if (reader["Symptoms"].ToString() != null)
{
patient.Symptoms = reader["Symptoms"].ToString();
}
else
{
patient.Symptoms = null;
}
if (reader["Result"].ToString() != null)
{
patient.Result = reader["Result"].ToString();
}
else
{
patient.Result = null;
}
if (reader["TestID"] == DBNull.Value)
{
int testID = (reader["TestID"] as int?) ?? 0;
}
else
{
patient.TestID = (int)reader["TestID"];
}
if (reader["TestName"].ToString() != null)
{
patient.TestName = reader["TestName"].ToString();
}
else
{
patient.TestName = null;
}
if (reader["Diagnosis"].ToString() != null)
{
patient.Diagnosis = reader["Diagnosis"].ToString();
}
else
{
patient.Diagnosis = null;
}
patientList.Add(patient);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
return patientList;
}
/// <summary>
/// Returns a patient based on their PatientID
/// </summary>
/// <param name="patientID"></param>
/// <returns>The PatientID</returns>
public static Patient GetPatientWithID(int patientID)
{
Patient patient = new Patient();
SqlConnection connection = MedassistDB.GetConnection();
string selectStatement =
"SELECT PatientID, FirstName, MInit, DOB, Gender, SSN, LastName, StreetAddress1, StreetAddress2, City, State, ZipCode, Phone " +
"FROM Patients " +
"WHERE PatientID = @PatientID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
selectCommand.Parameters.AddWithValue("@PatientID", patientID);
try
{
connection.Open();
reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
patient.PatientID = (int)reader["PatientID"];
patient.FirstName = reader["FirstName"].ToString();
patient.LastName = reader["LastName"].ToString();
patient.MInit = reader["MInit"].ToString();
patient.DOB = (DateTime)reader["DOB"];
patient.Gender = Convert.ToChar(reader["Gender"]);
patient.SSN = reader["SSN"].ToString();
patient.StreetAddr1 = reader["StreetAddress1"].ToString();
patient.StreetAddr2 = reader["StreetAddress2"].ToString();
patient.City = reader["City"].ToString();
patient.State = reader["State"].ToString();
patient.ZipCode = reader["ZipCode"].ToString();
patient.Phone = reader["Phone"].ToString();
}
else
{
patient = null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
return patient;
}
/// <summary>
/// Creates a patient
/// </summary>
/// <param name="patient">patient</param>
/// <returns>the patient id added</returns>
public static int CreatePatient(Patient patient)
{
return PatientDAL.AddPatient(patient);
}
/// <summary>
/// Sets the patients attributesbfrom the fields in the form
/// </summary>
/// <param name="patient">the Patient</param>
private void PutPatientData(Patient patient)
{
txtMiddleInit.MaxLength = 1;
patient.FirstName = txtFirstName.Text;
patient.LastName = txtLastName.Text;
if (txtMiddleInit.Text != " ")
{
patient.MInit = txtMiddleInit.Text;
}
patient.StreetAddr1 = txtAddress1.Text;
patient.StreetAddr2 = txtAddress2.Text;
patient.City = txtCity.Text;
patient.State = cboState.Text;
patient.Phone = txtPhone.Text;
patient.ZipCode = txtZip.Text;
}
/// <summary>
/// Gets patients with no id
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="DOB"></param>
private void GetPatientWithNoID(string firstName, string lastName, DateTime DOB)
{
try
{
patient = PatientDAL.GetPatientToUpdateWithNoID(firstName, lastName, DOB);
if (firstName == null)
MessageBox.Show("First Name is required " +
"Please try again.", "Patient Not Found");
else if (lastName == null)
MessageBox.Show("Last Name is required " +
"Please try again.", "Patient Not Found");
else if (DOB == null)
MessageBox.Show("Date of Birth is required " +
"Please try again.", "Patient Not Found");
else if (patient == null)
MessageBox.Show("No Patient found. " +
"Please try again.", "Patient Not Found");
else
{
this.DisplayPatient();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
/// <summary>
/// Gets a Patient using their ID and displays their info.
/// </summary>
/// <param name="patientID"></param>
private void GetPatient(int patientID)
{
try
{
patient = PatientDAL.GetPatientToUpdate(patientID);
if (patient == null)
MessageBox.Show("No Patient found with this ID. " +
"Please try again.", "Patient Not Found");
else
{
this.DisplayPatient();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
/// <summary>
/// Designates the actions carried out when the Submit button is clicked.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSubmit_Click(object sender, EventArgs e)
{
Patient newPatient = new Patient();
if (IsValidData())
{
newPatient.PatientID = patient.PatientID;
this.PutPatientData(newPatient);
try
{
if (!PatientDAL.UpdatePatient(patient, newPatient))
{
MessageBox.Show("Another user has updated or " +
"deleted that patient.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
else
{
patient = newPatient;
this.DialogResult = DialogResult.OK;
MessageBox.Show("Patient Updated");
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
/// <summary>
/// gets patient data from form
/// </summary>
/// <param name="patient"></param>
private void PutPatientData(Patient patient)
{
try
{
patient.FirstName = txtFirstName.Text;
patient.LastName = txtLastName.Text;
patient.MInit = txtMiddleInit.Text;
patient.StreetAddr1 = txtAddress1.Text;
patient.StreetAddr2 = txtAddress2.Text;
patient.City = txtCity.Text;
patient.State = cboState.Text;
patient.SSN = txtSSN.Text;
patient.Phone = txtPhone.Text;
patient.Gender = cboGender.Text[0];
patient.ZipCode = txtZip.Text;
patient.DOB = dateTimePickerDOB.Value.Date;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
