def insert_insurance_coverage(insurance_coverage): cnx = get_connection() cursor = cnx.cursor() query = ("INSERT INTO Insurance__Coverage (Coverage_Start_Date, Coverage_End_date, Insurer_Id, Person_Id) " "VALUES(%(Coverage_Start_Date)s, %(Coverage_End_date)s, %(Insurer_Id)s, %(Person_Id)s)") cursor.execute(query, insurance_coverage) insurance_coverage['Insurance_Id_no'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def insert_other(person_id): # insert other cnx = get_connection() cursor = cnx.cursor() query = ("INSERT INTO Other (Person_Id) " "VALUES(%s)") cursor.execute(query, (person_id,)) cnx.commit() cursor.close() cnx.close()
def insert_prescription(prescription): cnx = get_connection() cursor = cnx.cursor() query = ("INSERT INTO Prescription (Diagnosis_Id, ISDN, Quantity, Prescribed_Dose, Frequency, Pharmacy_Id) " "VALUES(%(Diagnosis_Id)s, %(ISDN)s, %(Quantity)s, %(Prescribed_Dose)s, %(Frequency)s, %(Pharmacy_Id)s)") cursor.execute(query, prescription) prescription['Prescription_No'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def insert_pharmacy_designation(pharmacy_designation): cnx = get_connection() cursor = cnx.cursor() query = ("INSERT INTO Pharmacy_Designation (Patient_Id, Pharmacy_Id) " "VALUES(%(Patient_Id)s, %(Pharmacy_Id)s)") cursor.execute(query, pharmacy_designation) pharmacy_designation['Priority'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def insert_medical_condition(medical_condition): cnx = get_connection() cursor = cnx.cursor() query = ( "INSERT INTO Medical_Condition (Condition_Name) VALUES (%(Condition_Name)s)") cursor.execute(query, medical_condition) medical_condition['Condition_Id'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def insert_diagnosis(diagnosis): cnx = get_connection() cursor = cnx.cursor() query = ( "INSERT INTO Diagnosis (Description, Appointment_Id, Condition_Id) " "VALUES (%(Description)s, %(Appointment_Id)s, %(Condition_Id)s)") cursor.execute(query, diagnosis) diagnosis['Diagnosis_Id'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def get_doctor_list(): doctor_list = [] cnx = get_connection() cursor = cnx.cursor() query = ("SELECT Doctor_Id, First_Name, Last_Name FROM Doctor " "JOIN Person ON Person.Person_Id = Doctor.Doctor_Id") cursor.execute(query) for (doctor_id, first_name, last_name) in cursor: doctor_list.append({'Doctor_Id': doctor_id, 'First_Name': first_name, 'Last_Name': last_name}) cursor.close() cnx.close() return doctor_list
def get_medication_list(): medication_list = [] cnx = get_connection() cursor = cnx.cursor() query = "SELECT ISDN, Medication_Name, Medication_Strength FROM Medication " cursor.execute(query) for (isdn, medication_name, medication_strength) in cursor: medication_list.append( {'ISDN': isdn, 'Medication_Name': medication_name, 'Medication_Strength': medication_strength}) cursor.close() cnx.close() return medication_list
def insert_appointment(appointment): cnx = get_connection() cursor = cnx.cursor() query = ( "INSERT INTO Appointment (MedicalOfficeId, Doctor_Id, PatientID, `Body Temperature`, Weight, Height, `Appointment Date`, `Appointment Time`) " "VALUES (%(MedicalOfficeId)s, %(Doctor_Id)s, %(PatientID)s, %(Body Temperature)s, %(Weight)s, %(Height)s, %(Appointment Date)s, %(Appointment Time)s)" ) cursor.execute(query, appointment) appointment["Appointment_Id"] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def insert_person(person): # insert person cnx = get_connection() cursor = cnx.cursor() query = ("INSERT INTO Person (First_Name, Last_Name, Date_Of_Birth, Deceased, Primary_Physician) " "VALUES(%(First_Name)s, %(Last_Name)s, %(Date_Of_Birth)s, %(Deceased)s, %(Primary_Physician)s)") cursor.execute(query, person) # get the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement or None when there # is no such value available. person['Person_Id'] = cursor.lastrowid cnx.commit() cursor.close() cnx.close()
def select_a_timeslot(doctor): # get a list of available time slot of the doctor availability_list = [] cnx = get_connection() cursor = cnx.cursor() query = ( "SELECT Doctor_Availability_Id, Day_Of_Week, From_Time, To_Time, Organization.Name, Medical_Office.MedicalOfficeId, Doctor_Availability.Doctor_Id FROM Doctor_Availability " "JOIN Affiliation ON Affiliation.Doctor_Id = Doctor_Availability.Doctor_Id " "JOIN Medical_Office ON Medical_Office.MedicalOfficeId = Affiliation.MedicalOfficeId " "JOIN Organization ON Organization.Org_Id = Medical_Office.MedicalOfficeId " "WHERE Doctor_Availability.Doctor_Id = %(Doctor_Id)s" ) cursor.execute(query, doctor) for (availability_id, day_of_week, from_time, to_time, medical_office_name, medical_office_id, doctor_id) in cursor: availability_list.append( { "Doctor_Availability_Id": availability_id, "Day_Of_Week": day_of_week, "From_Time": from_time, "To_Time": to_time, "medical_office_name": medical_office_name, "MedicalOfficeId": medical_office_id, "Doctor_Id": doctor_id, } ) cursor.close() cnx.close() # select a time slot print "when do you want to see the doctor?" selected_availability = make_a_selection(availability_list) # delete the selected availabitlity cnx = get_connection() cursor = cnx.cursor() query = "DELETE FROM Doctor_Availability " "WHERE Doctor_Availability_Id = %(Doctor_Availability_Id)s" cursor.execute(query, selected_availability) cnx.commit() cursor.close() cnx.close() return selected_availability
def get_pharmacy_list(): pharmacy_list = [] cnx = get_connection() cursor = cnx.cursor() query = ("SELECT Pharmacy_Id, Name, Phone, Address_Line_1, City, State, Zip_Code, Org_Type_Id FROM Pharmacy " "JOIN Organization ON Organization.Org_Id = Pharmacy.Pharmacy_Id") cursor.execute(query) for (pharmacy_id, name, phone, address_line_1, city, state, zip_code, org_type_id) in cursor: pharmacy_list.append( {'Pharmacy_Id': pharmacy_id, 'Name': name, 'Phone': phone, 'Address_Line_1': address_line_1, 'City': city, 'State': state, 'Zip_Code': zip_code, 'Org_Type_Id': org_type_id}) cursor.close() cnx.close() return pharmacy_list
def display_appointments_of_a_patient(patient): # get appointment list appointment_info_list = [] cnx = get_connection() cursor = cnx.cursor() query = ("SELECT \ Appointment.Appointment_Id, \ MedicalOfficeId, \ Doctor_Id, \ `Appointment Date`, \ `Appointment Time`, \ Diagnosis.Description, \ Medical_Condition.Condition_Name, \ Prescription.ISDN, \ Medication.Medication_Name, \ Medication.Medication_Strength, \ Prescription.Quantity, \ Prescription.Prescribed_Dose, \ Prescription.Frequency, \ Prescription.Pharmacy_Id \ FROM \ Appointment \ JOIN \ Diagnosis ON Diagnosis.Appointment_Id = Appointment.Appointment_Id \ JOIN \ Medical_Condition ON Medical_Condition.Condition_Id = Diagnosis.Condition_Id \ JOIN \ Prescription ON Prescription.Diagnosis_Id = Diagnosis.Diagnosis_Id \ JOIN \ Medication ON Medication.ISDN = Prescription.ISDN \ WHERE Appointment.PatientID = %(Person_Id)s") cursor.execute(query, patient) for ( appointment_id, medical_office_id, doctor_id, appointment_date, appointment_time, diagnosis_description, medical_condition_name, isdn, medication_name, medication_strength, quantity, prescribed_dose, frequency, pharmacy_id ) in cursor: appointment_info_list.append( {'Appointment_Id': appointment_id, 'MedicalOfficeId': medical_office_id, 'Doctor_Id': doctor_id, 'Appointment Date': appointment_date, 'Appointment Time': appointment_time, 'Description': diagnosis_description, 'medical_condition_name': medical_condition_name, 'ISDN': isdn, 'Medication_Name': medication_name, 'Medication_Strength': medication_strength, 'Quantity': quantity, 'Prescribed_Dose': prescribed_dose, 'Frequency': frequency, 'Pharmacy_Id': pharmacy_id}) cursor.close() cnx.close() print_dict_list(appointment_info_list)
def display_prescription_account(): # get start date time and end date time time_period = {} while True: try: datetime_str = raw_input('specify start date time(yyyy/mm/dd HH:MM:SS) ') time_period['start_time'] = datetime.datetime.strptime(datetime_str, '%Y/%m/%d %H:%M:%S') break except ValueError as e: print e.message while True: try: datetime_str = raw_input('specify end date time(yyyy/mm/dd HH:MM:SS) ') time_period['end_time'] = datetime.datetime.strptime(datetime_str, '%Y/%m/%d %H:%M:%S') break except ValueError as e: print e.message # get prescription count prescription_count_info_list = [] cnx = get_connection() cursor = cnx.cursor() query = ("SELECT COUNT(Prescription.Prescription_No), Appointment.Doctor_Id, Medication.ISDN, " "Medication.Medication_Name, Person.First_Name, Person.Last_Name FROM Prescription \ JOIN Diagnosis ON Diagnosis.Diagnosis_Id = Prescription.Diagnosis_Id \ JOIN Appointment ON Appointment.Appointment_Id = Diagnosis.Appointment_Id \ JOIN Medication ON Medication.ISDN = Prescription.ISDN \ JOIN Person ON Person.Person_Id = Appointment.Doctor_Id \ WHERE TIMESTAMP(Appointment.`Appointment Date`, Appointment.`Appointment Time`) > %(start_time)s AND \ TIMESTAMP(Appointment.`Appointment Date`, Appointment.`Appointment Time`) < %(end_time)s \ GROUP BY Appointment.Doctor_Id, Medication.ISDN;") cursor.execute(query, time_period) for (prescription_count, doctor_id, isdn, medication_name, doctor_fname, doctor_lname) in cursor: prescription_count_info_list.append( {'prescription_count': prescription_count, 'Doctor_Id': doctor_id, 'ISDN': isdn, 'Medication_Name': medication_name, 'doctor_fname': doctor_fname, 'doctor_lname': doctor_lname}) cursor.close() cnx.close() # display schedule list print_dict_list(prescription_count_info_list)
def get_patient_list(): patient_list = [] cnx = get_connection() cursor = cnx.cursor() query = ( "SELECT Other.Person_Id, First_Name, Last_Name, Date_Of_Birth, Deceased, Primary_Physician FROM Other " "JOIN Person ON Person.Person_Id = Other.Person_Id" ) cursor.execute(query) for (person_id, first_name, last_name, date_of_birth, deceased, primary_physician) in cursor: patient_list.append( { "Person_Id": person_id, "First_Name": first_name, "Last_Name": last_name, "Date_Of_Birth": date_of_birth, "Deceased": deceased, "Primary_Physician": primary_physician, } ) cursor.close() cnx.close() return patient_list
def display_schedules(): # get schecule list schedule_list = [] cnx = get_connection() cursor = cnx.cursor() query = ("SELECT \ Doctor_Id, \ p1.First_Name AS doctor_fname, \ p1.Last_Name AS doctor_lname, \ `Appointment Date`, \ `Appointment Time`, \ MedicalOfficeId, \ Organization.`Name` AS medical_office_name, \ PatientID, \ p2.First_Name AS patient_fname, \ p2.Last_Name AS patient_lname \ FROM \ Appointment \ JOIN \ Organization ON Organization.Org_Id = Appointment.MedicalOfficeId \ JOIN \ Person AS p1 ON p1.Person_Id = Appointment.Doctor_Id \ JOIN \ Person AS p2 ON p2.Person_Id = Appointment.PatientID") cursor.execute(query) for ( doctor_id, doctor_fname, doctor_lname, appointment_date, appointment_time, medical_office_id, medical_office_name, patient_id, patient_fname, patient_lname) in cursor: schedule_list.append({'Doctor_Id': doctor_id, 'doctor_fname': doctor_fname, 'doctor_lname': doctor_lname, 'Appointment Date': appointment_date, 'Appointment Time': appointment_time, 'MedicalOfficeId': medical_office_id, 'medical_office_name': medical_office_name, 'PatientID': patient_id, 'patient_fname': patient_fname, 'patient_lname': patient_lname}) cursor.close() cnx.close() # display schedule list print_dict_list(schedule_list)