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)
