Exemple #1
0
        /// <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);
        }
Exemple #2
0
        /// <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);
        }
Exemple #3
0
        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);
        }
Exemple #4
0
        /// <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);
        }
Exemple #5
0
        /// <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);
            }
        }
Exemple #6
0
        /// <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);
        }
Exemple #7
0
        /// <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);
        }
Exemple #8
0
        /// <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);
        }
Exemple #9
0
        /// <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);
        }
Exemple #10
0
        /// <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);
        }
Exemple #11
0
        /// <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);
        }
Exemple #12
0
        /// <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);
        }
Exemple #13
0
        /// <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);
        }
Exemple #14
0
        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);
        }
Exemple #15
0
        /// <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);
        }
Exemple #16
0
        /// <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);
            }
        }
Exemple #17
0
        /// <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);
        }
Exemple #18
0
        /// <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);
        }