/// <summary>
/// Inserts an Address into the db
/// </summary>
/// <param name="address">The Address to insert</param>
/// <returns>The id of the inserted Address, or null if it did not go through</returns>
public static int?InsertAddress(Address address)
{
int? id = null;
String insertStatement = @"INSERT INTO Address(street, city, state, zip)
OUTPUT inserted.id
VALUES (@street, @city, @state, @zip)" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
command.Parameters.AddWithValue("@street", address.Street);
command.Parameters.AddWithValue("@city", address.City);
command.Parameters.AddWithValue("@state", address.State);
command.Parameters.AddWithValue("@zip", address.Zip);
id = (int?)command.ExecuteScalar();
}
}
return(id);
}
/// <summary>
/// Gets a list of all specialties in the db
/// </summary>
/// <returns>List of all Specialties in db</returns>
public static List <Specialty> GetSpecialties()
{
List <Specialty> specialties = new List <Specialty>();
String selectStatement = @"SELECT name FROM Specialty";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordName = reader.GetOrdinal("name");
while (reader.Read())
{
specialties.Add(new Specialty(reader.GetString(ordName)));
}
}
}
}
return(specialties);
}
public List <UserDTO> GetAvailableDoctorsForAppointmentDate(DateTime appointmentDateTime)
{
var availableDoctors = new List <UserDTO>();
string selectUserStatement = @"SELECT D.id as DoctorID
FROM dbo.Person P
inner join doctor D on P.id = D.personID
where D.active = 1
EXCEPT
SELECT doctorID
from dbo.Appointment
where appointmentDateTime = @AppointmentDate";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
if (appointmentDateTime == null)
{
selectCommand.Parameters.AddWithValue("@AppointmentDate", DBNull.Value);
}
else
{
selectCommand.Parameters.AddWithValue("@AppointmentDate", appointmentDateTime);
}
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
UserDTO doctor = new UserDTO();
doctor.DoctorId = (int)reader["DoctorID"];
availableDoctors.Add(doctor);
}
}
}
}
return(availableDoctors);
}
/// <summary>
/// Gets the Person of the Doctor associated with a Visit
///
/// Returns password as null.
/// </summary>
/// <param name="visit">The patient's Visit</param>
/// <returns>Person of the Doctor of said Visit</returns>
public static Person GetPersonOfDoctorByVisit(Visit visit)
{
Person person = null;
String selectStatement = @"SELECT DISTINCT Person.id, Person.username, Person.firstName, Person.lastName,
Person.dateOfBirth, Person.ssn, Person.gender, Person.addressID, Person.contactPhone
FROM Visit
JOIN Appointment ON Visit.appointmentID = Appointment.id
JOIN Doctor ON Appointment.doctorID = Doctor.id
JOIN Person ON Doctor.personID = Person.id
WHERE Visit.id = @visitID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
command.Parameters.AddWithValue("@visitID", visit.ID);
connection.Open();
person = GetPerson(command);
}
}
return(person);
}
/// <summary>
/// Create a Visits to the data source.
/// </summary>
/// <param name="visit"></param>
public static bool CreateVisit(Visit visit)
{
int retValue;
String insertStatement = @" INSERT INTO VISIT
(appointmentID , nurseID , visitDateTime , initialDiagnosis ,weight , systolicPressure , diastolicPressure ,
bodyTemperature , pulse , symptoms )
VALUES
( @appointmentID , @nurseID , @visitDateTime , @initialDiagnosis , @weight , @systolicPressure, @diastolicPressure,
@bodyTemperature , @pulse , @symptoms )" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
command.Parameters.AddWithValue("@appointmentID", visit.AppointmentID);
command.Parameters.AddWithValue("@nurseID", visit.NurseID);
command.Parameters.AddWithValue("@visitDateTime", visit.VisitDateTime);
command.Parameters.AddWithValue("@initialDiagnosis", visit.InitialDiagnosis);
command.Parameters.AddWithValue("@weight", visit.Weight);
command.Parameters.AddWithValue("@systolicPressure", visit.SystolicPressure);
command.Parameters.AddWithValue("@diastolicPressure", visit.DiastolicPressure);
command.Parameters.AddWithValue("@bodyTemperature", visit.BodyTemperature);
command.Parameters.AddWithValue("@pulse", visit.Pulse);
command.Parameters.AddWithValue("@symptoms", visit.Symptoms);
retValue = command.ExecuteNonQuery();
}
}
if (retValue < 1)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Gets a list of all of a doctor's specialties
/// </summary>
/// <param name="doctor">Doctor in question</param>
/// <returns>The doctor's specialties</returns>
public static List <Specialty> GetDoctorsSpecialties(Doctor doctor)
{
List <Specialty> specialties = new List <Specialty>();
String selectStatement = @"SELECT specialtyName FROM Doctor_has_Specialties WHERE doctorID = @doctorID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@doctorID", doctor.ID);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordSpecialtyName = reader.GetOrdinal("specialtyName");
while (reader.Read())
{
specialties.Add(new Specialty(reader.GetString(ordSpecialtyName)));
}
}
}
}
return(specialties);
}
/// <summary>
/// Checks to see if SSN exists in DB
/// </summary>
/// <param name="ssn"></param>
/// <returns></returns>
public bool SocialSecurityExist(string ssn)
{
bool ssnExist = false;
string selectStatement = "SELECT id FROM Person WHERE ssn = @ssn";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@ssn", ssn);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine(reader);
ssnExist = true;
}
}
}
}
return(ssnExist);
}
/// <summary>
/// Method to check if patient already has appointment for particular time
/// </summary>
/// <param name="patientId"></param>
/// <param name="appointmentTime"></param>
/// <returns></returns>
public bool PatietHasAppointment(int patientId, DateTime appointmentTime)
{
bool appointmentExist = false;
string selectStatement = @"SELECT id FROM Appointment where patientID = @PatientId and appointmentDateTime = @AppointmentDateTime";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@PatientId", patientId);
selectCommand.Parameters.AddWithValue("@AppointmentDateTime", appointmentTime);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
appointmentExist = true;
}
}
}
}
return(appointmentExist);
}
/// <summary>
/// Method to check if User exists
/// </summary>
/// <param name="username"></param>
/// <returns>true if username exists and false if user does not exist</returns>
public bool UserExist(string username)
{
bool userExist = false;
string selectStatement = "SELECT id FROM Person WHERE username = @Username";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@Username", username);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine(reader);
userExist = true;
}
}
}
}
return(userExist);
}
/// <summary>
/// Gets a list of all lab orders in the db for a visit
/// </summary>
/// <returns>List of all Lab_Orders in db</returns>
public static List <LabOrderDTO> GetLab_Orders_For_Visit(int visitID)
{
List <LabOrderDTO> orders = new List <LabOrderDTO>();
String selectStatement = @"select Visit.id as id , name ,dateOrdered , testPerformed ,
results from Visit
join Lab_Order on visit.id = Lab_Order.visitID
join Lab_Orders_have_Lab_Tests on Lab_Orders_have_Lab_Tests.labOrderID = Lab_Order.id
join Lab_Test on Lab_Test.code = Lab_Orders_have_Lab_Tests.labTestCode
where Visit.id = @visitID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordID = reader.GetOrdinal("id");
int ordName = reader.GetOrdinal("name");
int ordDateOrdered = reader.GetOrdinal("dateOrdered");
int ordTestPerformed = reader.GetOrdinal("testPerformed");
int ordResults = reader.GetOrdinal("results");
while (reader.Read())
{
LabOrderDTO labOrderDTO = new LabOrderDTO();
labOrderDTO.ID = reader.GetInt32(ordID);
labOrderDTO.Name = reader.GetString(ordName);
labOrderDTO.DateOrdered = reader.GetDateTime(ordDateOrdered);
labOrderDTO.TestPerformed = reader.GetDateTime(ordTestPerformed);
labOrderDTO.Results = reader.GetString(ordResults);
orders.Add(labOrderDTO);
}
}
}
}
return(orders);
}
/// <summary>
/// Gets a list of all Lab Tests in the db
/// </summary>
/// <returns>List of all Lab_Tests in db</returns>
public static List <Lab_Test> GetLab_Tests()
{
List <Lab_Test> tests = new List <Lab_Test>();
String selectStatement = @"SELECT code, name
FROM Lab_Test";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
int ordCode = reader.GetOrdinal("code");
int ordName = reader.GetOrdinal("name");
while (reader.Read())
{
tests.Add(new Lab_Test(reader.GetInt32(ordCode), reader.GetString(ordName)));
}
}
}
}
return(tests);
}
/// <summary>
/// Returns whether or not the Nurse is the nurse of this Visit.
///
/// Intended for use to check if the currentUser is the Nurse of the Visit
/// </summary>
/// <param name="currentUser">Nurse in question</param>
/// <param name="visit">Visit in question</param>
/// <returns>Whether or not this is the Nurse of the Visit</returns>
public static bool IsThisNurseTheNurseOfTheVisit(UserDTO currentUser, VisitDTO visit)
{
bool isThisTheNurse = false;
String selectStatement = @"SELECT Visit.nurseID as nurseID FROM Visit WHERE Visit.id = @visitID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@visitID", visit.ID);
using (SqlDataReader reader = command.ExecuteReader())
{
int nurseID = reader.GetOrdinal("nurseID");
while (reader.Read())
{
int vsitNurseID = reader.GetInt32(nurseID);
isThisTheNurse = currentUser.NurseId == vsitNurseID;
}
}
}
}
return(isThisTheNurse);
}
/// <summary>
/// Gets a patient information by patient id
/// </summary>
/// <param name="patientId"></param>
/// <returns></returns>
public static UserDTO GetPatientById(int patientId)
{
UserDTO patient = new UserDTO();
string selectStatement = @"SELECT p.id as personId, firstName, lastName, dateOfBirth, ssn, gender, addressID, contactPhone, pt.id as patientId
FROM Patient as pt
JOIN Person as p ON pt.personID = p.id
WHERE pt.active = 1
AND pt.id = @PatientId" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@PatientId", patientId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
patient.Id = (int)reader["personId"];
patient.FirstName = reader["firstName"].ToString();
patient.LastName = reader["lastName"].ToString();
patient.SSN = reader["ssn"].ToString();
patient.Gender = reader["gender"].ToString();
patient.ContactPhone = reader["contactPhone"].ToString();
patient.AddressId = reader["addressID"] != DBNull.Value ? (int)reader["addressID"] : 0;
patient.DateOfBirth = reader["dateOfBirth"] != DBNull.Value ? (DateTime)reader["dateOfBirth"] : (DateTime?)null;
patient.PatientId = (int)reader["patientId"];
patient.IsActivePatient = true;
}
;
}
}
}
return(patient);
}
public static List <VisitDTO> GetPatientsVisits(int patientId)
{
List <VisitDTO> visits = new List <VisitDTO>();
string selectStatement = @"SELECT a.reasonForVisit, a.appointmentDateTime, a.id as appointmentID,
CONCAT(d.firstName, ' ', d.lastName) as doctor,
CONCAT(p.firstName, ' ', p.lastName) as nurse,
v.bodyTemperature, v.diastolicPressure, v.id as visitID,
v.initialDiagnosis, v.pulse, v.symptoms,
v.systolicPressure, v.visitDateTime, v.weight, v.finalDiagnosis
FROM Appointment a
inner JOIN Visit v on a.id = v.appointmentID
join Doctor on a.doctorID = Doctor.id
JOIN PERSON d ON Doctor.personID = d.ID
INNER JOIN NURSE n ON v.nurseID = n.id
INNER JOIN PERSON p ON n.personID = p.ID
where a.patientID = @PatientId";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@PatientId", patientId);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
VisitDTO visitDTO = new VisitDTO();
visitDTO.AppointmentID = (long)reader["appointmentID"];
visitDTO.AppointmentDateTime = (DateTime)reader["appointmentDateTime"];
visitDTO.BodyTemperature = (decimal)reader["bodyTemperature"];
visitDTO.DiastolicPressure = (int)reader["diastolicPressure"];
string final = null;
if (!reader.IsDBNull(reader.GetOrdinal("finalDiagnosis")))
{
final = reader.GetString(reader.GetOrdinal("finalDiagnosis"));
}
visitDTO.FinalDiagnosis = final;
visitDTO.ID = (long)reader["visitID"];
visitDTO.InitialDiagnosis = reader["initialDiagnosis"].ToString();
visitDTO.Doctor = reader["doctor"].ToString();
visitDTO.Nurse = reader["nurse"].ToString();
visitDTO.Pulse = (int)reader["pulse"];
String symptoms = null;
if (!reader.IsDBNull(reader.GetOrdinal("symptoms")))
{
symptoms = reader.GetString(reader.GetOrdinal("symptoms"));
}
visitDTO.Symptoms = symptoms;
visitDTO.SystolicPressure = (int)reader["systolicPressure"];
visitDTO.VisitDateTime = (DateTime)reader["visitDateTime"];
visitDTO.VisitReason = reader["reasonForVisit"].ToString();
visitDTO.Weight = (decimal)reader["weight"];
visits.Add(visitDTO);
}
}
}
}
return(visits);
}
/// <summary>
/// Search for a user in the database
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="dateOfBirth"></param>
/// <returns>A user that matches the search criteria</returns>
public List <UserDTO> SearchPatient(string firstName, string lastName, DateTime?dateOfBirth)
{
var patients = new List <UserDTO>();
string selectUserStatement = @"
SELECT P.id as personId, username,firstName, CONCAT(firstName, ' ', lastName) as fullName,
lastName, dateOfBirth, ssn, gender,
addressID, contactPhone, U.id as PatientId,
street, city, state, zip
FROM Person P
INNER JOIN Patient U ON P.id = U.personID
INNER JOIN Address A ON P.addressID = A.id
WHERE (P.firstName = @FirstName AND P.lastname = @LastName) OR
(CAST(P.dateOfBirth AS date) = @DateOfBirth) OR
(CAST(P.dateOfBirth AS date) = @DateOfBirth and P.lastName = @LastName) ";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectUserStatement, connection))
{
if (String.IsNullOrWhiteSpace(firstName))
{
selectCommand.Parameters.AddWithValue("@FirstName", DBNull.Value);
}
else
{
selectCommand.Parameters.AddWithValue("@FirstName", firstName);
}
if (String.IsNullOrWhiteSpace(lastName))
{
selectCommand.Parameters.AddWithValue("@LastName", DBNull.Value);
}
else
{
selectCommand.Parameters.AddWithValue("@LastName", lastName);
}
if (dateOfBirth == null)
{
selectCommand.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
}
else
{
selectCommand.Parameters.AddWithValue("@DateOfBirth", dateOfBirth.Value.Date);
}
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
UserDTO patient = new UserDTO();
patient.Id = (int)reader["personId"];
patient.FullName = reader["fullName"].ToString();
patient.Username = reader["username"].ToString();
patient.FirstName = reader["firstName"].ToString();
patient.LastName = reader["lastName"].ToString();
patient.SSN = reader["ssn"].ToString();
patient.Gender = reader["gender"].ToString();
patient.ContactPhone = reader["contactPhone"].ToString();
patient.Street = reader["street"].ToString();
patient.State = reader["state"].ToString();
patient.City = reader["city"].ToString();
patient.Zip = reader["zip"].ToString();
patient.AddressId = reader["addressID"] != DBNull.Value ? (int)reader["addressID"] : 0;
patient.PatientId = reader["PatientId"] != DBNull.Value ? (int)reader["PatientId"] : 0;
patient.DateOfBirth = reader["dateOfBirth"] != DBNull.Value ? (DateTime)reader["dateOfBirth"] : (DateTime?)null;
patients.Add(patient);
}
}
}
}
return(patients);
}
/// <summary>
/// Insert a new Lab_Orders_have_Lab_Tests relation into the db
/// </summary>
/// <param name="relation">The Lab_Orders_have_Lab_Tests to insert</param>
/// <returns>Whether or not the insert succeeded</returns>
public static bool InsertLab_Orders_have_Lab_Tests(Lab_Orders_have_Lab_Tests relation)
{
Object obj = null;
String insertStatement = @"INSERT INTO Lab_Orders_have_Lab_Tests (labOrderID, labTestCode, testPerformed, results , isNormal)
VALUES (@labOrderID, @labTestCode, @testPerformed, @results ,@isNormal)" ;
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
connection.Open();
using (SqlCommand command = new SqlCommand(insertStatement, connection))
{
if (relation.LabOrderID == null)
{
command.Parameters.AddWithValue("@labOrderID", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@labOrderID", relation.LabOrderID);
}
if (relation.LabTestCode == null)
{
command.Parameters.AddWithValue("@labTestCode", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@labTestCode", relation.LabTestCode);
}
if (relation.TestPerformed == null)
{
command.Parameters.AddWithValue("@testPerformed", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@testPerformed", relation.TestPerformed);
}
if (relation.Results == null)
{
command.Parameters.AddWithValue("@results", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@results", relation.Results);
}
if (relation.IsNormal == null)
{
command.Parameters.AddWithValue("@IsNormal", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@IsNormal", relation.IsNormal);
}
obj = command.ExecuteScalar();
}
}
if (obj == null)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Returns the report 'getMostPerformedTestsDuringDates' as a Report
/// </summary>
/// <param name="startDate">Starting date for report</param>
/// <param name="endDate">Ending date for report</param>
/// <returns>Report of 'getMostPerformedTestsDuringDates'</returns>
public static List <Report> GetMostPerformedTestsDuringDates(DateTime startDate, DateTime endDate)
{
List <Report> reports = new List <Report>();
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand("getMostPerformedTestsDuringDates", connection))
{
connection.Open();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startDate", startDate.Date);
command.Parameters.AddWithValue("@endDate", endDate.Date);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordLabCode = reader.GetOrdinal("labCode");
int ordLabName = reader.GetOrdinal("labName");
int ordNumberOfTests = reader.GetOrdinal("numberOfTests");
int ordTotalTests = reader.GetOrdinal("totalTests");
int ordPercentageOfTotal = reader.GetOrdinal("percentageOfTotal");
int ordNormalResults = reader.GetOrdinal("numberOfNormalResults");
int ordAbnormalResults = reader.GetOrdinal("numberOfAbnormalResults");
int ordPercentageTwenty = reader.GetOrdinal("percentageDoneOnTwentyYearOldsDuringThisTime");
int ordPercentageThirty = reader.GetOrdinal("percentageDoneOnThirtyYearOldsDuringThisTime");
int ordPercentageOtherAge = reader.GetOrdinal("percentageDoneOnOtherAgesDuringThisTime");
while (reader.Read())
{
int labCode = 0;
if (!reader.IsDBNull(ordLabCode))
{
labCode = reader.GetInt32(ordLabCode);
}
string LabName = null;
if (!reader.IsDBNull(ordLabName))
{
LabName = reader.GetString(ordLabName);
}
int NumberOfTests = 0;
if (!reader.IsDBNull(ordNumberOfTests))
{
NumberOfTests = reader.GetInt32(ordNumberOfTests);
}
int TotalTests = 0;
if (!reader.IsDBNull(ordTotalTests))
{
TotalTests = Convert.ToInt32(reader.GetDouble(ordTotalTests));
}
decimal PercentageOfTotal = 0;
if (!reader.IsDBNull(ordPercentageOfTotal))
{
PercentageOfTotal = Convert.ToDecimal(reader.GetDouble(ordPercentageOfTotal));
}
int NumberOfNormalResults = 0;
if (!reader.IsDBNull(ordNormalResults))
{
NumberOfNormalResults = reader.GetInt32(ordNormalResults);
}
int NumberOfAbnormalResults = 0;
if (!reader.IsDBNull(ordAbnormalResults))
{
NumberOfAbnormalResults = reader.GetInt32(ordAbnormalResults);
}
decimal PercentageDoneOnTwentyYearOldsDuringThisTime = 0;
if (!reader.IsDBNull(ordPercentageTwenty))
{
PercentageDoneOnTwentyYearOldsDuringThisTime = Convert.ToDecimal(reader.GetDouble(ordPercentageTwenty));
}
decimal PercentageDoneOnThirtyYearOldsDuringThisTime = 0;
if (!reader.IsDBNull(ordPercentageThirty))
{
PercentageDoneOnThirtyYearOldsDuringThisTime = Convert.ToDecimal(reader.GetDouble(ordPercentageThirty));
}
decimal PercentageDoneOnOtherAgesOldsDuringThisTime = 0;
if (!reader.IsDBNull(ordPercentageOtherAge))
{
PercentageDoneOnOtherAgesOldsDuringThisTime = Convert.ToDecimal(reader.GetDouble(ordPercentageOtherAge));
}
var report = new Report(labCode, LabName, NumberOfTests, TotalTests, PercentageOfTotal, NumberOfNormalResults, NumberOfAbnormalResults,
PercentageDoneOnTwentyYearOldsDuringThisTime, PercentageDoneOnThirtyYearOldsDuringThisTime, PercentageDoneOnOtherAgesOldsDuringThisTime);
reports.Add(report);
}
}
}
}
return(reports);
}
/// <summary>
/// Gets a Visit by its Appointment
/// </summary>
/// <returns>The Visit</returns>
public static List <VisitDTO> GetVisitByAppointment(Appointment appointment)
{
List <VisitDTO> visits = new List <VisitDTO>();
String selectStatement = @"SELECT CAST( Visit.id as INT) as visitID, CAST(appointmentID AS INT) appointmentID,
d.firstName +' ' + d.lastName AS doctor,
n.firstName +' ' + n.lastName AS nurse ,
visitDateTime, initialDiagnosis, weight, systolicPressure, diastolicPressure,
bodyTemperature, pulse, symptoms, finalDiagnosis
FROM Visit
join Appointment on VISIT.appointmentID = Appointment.id
join Doctor on Appointment.doctorID = Doctor.id
JOIN PERSON d ON Doctor.personID = d.ID
JOIN NURSE ON VISIT.nurseID = NURSE.id
JOIN PERSON n ON NURSE.personID = n.ID
WHERE appointmentID = @appointmentID";
using (SqlConnection connection = GetSQLConnection.GetConnection())
{
using (SqlCommand command = new SqlCommand(selectStatement, connection))
{
connection.Open();
command.Parameters.AddWithValue("@appointmentID", appointment.ID);
using (SqlDataReader reader = command.ExecuteReader())
{
int ordVisitID = reader.GetOrdinal("visitID");
int ordApptID = reader.GetOrdinal("appointmentID");
int ordDoctor = reader.GetOrdinal("doctor");
int ordNurse = reader.GetOrdinal("nurse");
int ordVisitDateTime = reader.GetOrdinal("visitDateTime");
int ordInitialDiagnosis = reader.GetOrdinal("initialDiagnosis");
int ordWeight = reader.GetOrdinal("weight");
int ordSystolicPressure = reader.GetOrdinal("systolicPressure");
int ordDiastolicPressure = reader.GetOrdinal("diastolicPressure");
int ordBodyTemperature = reader.GetOrdinal("bodyTemperature");
int ordPulse = reader.GetOrdinal("pulse");
int ordSymptoms = reader.GetOrdinal("symptoms");
int ordFinalDiagnosis = reader.GetOrdinal("finalDiagnosis");
while (reader.Read())
{
string symptoms = null;
if (!reader.IsDBNull(ordSymptoms))
{
symptoms = reader.GetString(ordSymptoms);
}
string final = null;
if (!reader.IsDBNull(ordFinalDiagnosis))
{
final = reader.GetString(ordFinalDiagnosis);
}
VisitDTO visitDTO = new VisitDTO
{
ID = reader.GetInt32(ordVisitID),
AppointmentID = reader.GetInt32(ordApptID),
Doctor = reader.GetString(ordDoctor),
Nurse = reader.GetString(ordNurse),
VisitDateTime = reader.GetDateTime(ordVisitDateTime),
InitialDiagnosis = reader.GetString(ordInitialDiagnosis),
Weight = reader.GetDecimal(ordWeight),
SystolicPressure = reader.GetInt32(ordSystolicPressure),
DiastolicPressure = reader.GetInt32(ordDiastolicPressure),
BodyTemperature = reader.GetDecimal(ordBodyTemperature),
Pulse = reader.GetInt32(ordPulse),
Symptoms = symptoms,
FinalDiagnosis = final
};
visits.Add(visitDTO);
}
}
}
}
return(visits);
}