def test_schedule_appointment(self):
'''
schedule_appointment maintains vaccine inventory, update slot status in
caregiverschedule and appointments table, update patient appointment status
'''
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
with sqlClient.cursor(as_dict=True) as cursor:
# initalize objects
vp = patient('Anushna Prakash', 1, cursor)
vc = caregiver('Carrie Nation', cursor)
vaccine = covid('Pfizer', 'Pfizer-BioNTech', 10, 10, 21, 2,
cursor)
#Insert appointment in appointments table
vacc_appt_insert_sql = "INSERT INTO VaccineAppointments("
vacc_appt_insert_sql += "VaccineName, PatientId, CaregiverId, ReservationDate, "
vacc_appt_insert_sql += "ReservationStartHour, ReservationStartMinute, AppointmentDuration, "
vacc_appt_insert_sql += "SlotStatus, DateAdministered, DoseNumber) VALUES "
vacc_appt_insert_sql += "('Pfizer', 1, 1, NULL, 10, 15, 15, 1, NULL, 1)"
cursor.execute(vacc_appt_insert_sql)
#Schedule the appointment
vp.ScheduleAppointment(1, 0, vaccine, cursor)
#check VaccineAppointments has exactly 1 rows
check_appointments_sql = "SELECT * FROM VaccineAppointments"
cursor.execute(check_appointments_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 1)
self.assertTrue(rows[0]['SlotStatus'] == 2)
#check vaccines inventory updated
check_inventory_sql = "SELECT * FROM Vaccines"
cursor.execute(check_inventory_sql)
row = cursor.fetchone()
self.assertTrue(row['AvailableDoses'] == 8)
self.assertTrue(row['ReservedDoses'] == 12)
#check slot statuses in caregiver schedule updated
check_slot_stat2_sql = "SELECT * FROM CaregiverSchedule "
check_slot_stat2_sql += "WHERE CaregiverSlotSchedulingId = 1"
cursor.execute(check_slot_stat2_sql)
row = cursor.fetchone()
self.assertTrue(row['SlotStatus'] == 2)
#check patient vaccine status updated
check_patient_sql = "SELECT * FROM Patients"
cursor.execute(check_patient_sql)
row = cursor.fetchone()
self.assertTrue(row['VaccineStatus'] == 2)
clear_tables(sqlClient)
def test_reserve_appointment(self):
'''
reserve_appointment changes patient status from new to queued
adds row to VaccineAppointments table with slot status 'hold'
returns appointmentid
'''
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
with sqlClient.cursor(as_dict=True) as cursor:
# initalize objects
vp = patient('Dwight Sablan', 0, cursor)
vc = caregiver('Carrie Nation', cursor)
vaccine = covid('Pfizer', 'Pfizer-BioNTech', 10, 10, 21, 2,
cursor)
hold_first_cgslot_sql = "UPDATE CaregiverSchedule "
hold_first_cgslot_sql += "SET SlotStatus = 1 "
hold_first_cgslot_sql += "WHERE CaregiverSlotSchedulingId = 1"
cursor.execute(hold_first_cgslot_sql)
appt = vp.ReserveAppointment(1, vaccine, cursor)
self.assertEqual(appt, 0)
# check VaccineAppointments has exactly 2 rows
check_appointments_sql = "SELECT * FROM VaccineAppointments"
cursor.execute(check_appointments_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 2)
# check each row
for row in rows:
self.assertTrue(row['VaccineName'] == 'Pfizer')
self.assertTrue(row['PatientId'] == 1)
self.assertTrue(row['CaregiverId'] == 1)
self.assertTrue(row['SlotStatus'] == 1)
# check caregiverschedule has vaccineappointmentid = 1
check_caregivers_sql = "SELECT SlotStatus, VaccineAppointmentId FROM CareGiverSchedule "
check_caregivers_sql += "WHERE SlotStatus = 1"
cursor.execute(check_caregivers_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 2)
# check patient status is updated
check_patient_sql = "SELECT * FROM Patients"
cursor.execute(check_patient_sql)
row = cursor.fetchone()
self.assertTrue(row['VaccineStatus'] == 1)
clear_tables(sqlClient)
def test_reserve_appointment_raises_Exception(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
with sqlClient.cursor(as_dict=True) as cursor:
# initalize objects
vp = patient('Dwight Sablan', 0, cursor)
vaccine = covid('Pfizer', 'Pfizer-BioNTech', 10, 10, 21, 2,
cursor)
self.assertRaises(InvalidCaregiverSchedulingSlotId,
vp.ReserveAppointment, -1, vaccine, cursor)
self.assertRaises(InvalidCaregiverSchedulingSlotId,
vp.ReserveAppointment, -2, vaccine, cursor)
def test_init(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
with sqlClient.cursor(as_dict=True) as cursor:
vp = patient('Dwight Sablan', 0, cursor)
get_patient_sql = "SELECT * FROM Patients"
cursor.execute(get_patient_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 1)
clear_tables(sqlClient)
# Initalize the caregivers, patients & vaccine supply
caregiversList = []
caregiversList.append(VaccineCaregiver('Carrie Nation', dbcursor))
caregiversList.append(VaccineCaregiver('Clare Barton', dbcursor))
caregivers = {}
for cg in caregiversList:
cgid = cg.caregiverId
caregivers[cgid] = cg
# Add a vaccine and Add doses to inventory of the vaccine
vaccine = covid('Moderna', 'Moderna', 0, 0, 28, 2, dbcursor)
covid.add_doses('Moderna', 5, dbcursor)
# Add patients
patientList = []
patientList.append(patient('Spongebob Squarepants', 0, dbcursor))
patientList.append(patient('Sandy Cheeks', 0, dbcursor))
patientList.append(patient('Squidward', 0, dbcursor))
patientList.append(patient('Patrick Star', 0, dbcursor))
patientList.append(patient('Mr. Krabs', 0, dbcursor))
patients = {}
for pt in patientList:
ptid = pt.patientId
patients[ptid] = pt
with sqlClient.cursor(as_dict=True) as dbcursor:
# Schedule the patients
for p in patientList:
try:
# PutHoldOn
cg_first_slot = vrs.PutHoldOnAppointment1(dbcursor)
def test_main_five_patients(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
with sqlClient.cursor(as_dict=True) as dbcursor:
vrs = scheduler()
caregiversList = []
caregiversList.append(
VaccineCaregiver('Carrie Nation', dbcursor))
caregiversList.append(
VaccineCaregiver('Clare Barton', dbcursor))
vaccine = covid('Moderna', 'Moderna', 0, 0, 28, 2, dbcursor)
covid.add_doses('Moderna', 5, dbcursor)
# Add patients
patientList = []
patientList.append(
patient('Spongebob Squarepants', 0, dbcursor))
patientList.append(patient('Sandy Cheeks', 0, dbcursor))
patientList.append(patient('Squidward', 0, dbcursor))
patientList.append(patient('Patrick Star', 0, dbcursor))
patientList.append(patient('Mr. Krabs', 0, dbcursor))
# Schedule the patients
for p in patientList:
try:
# PutHoldOn
cg_first_slot = vrs.PutHoldOnAppointment1(dbcursor)
# Reserve appointment
first_appt = p.ReserveAppointment(
cg_first_slot, vaccine, dbcursor)
# Schedule appointment
p.ScheduleAppointment(cg_first_slot, first_appt,
vaccine, dbcursor)
# commit
dbcursor.connection.commit()
except Exception as e:
err_str = "Oops! An exception occurred. The transaction for patient "
err_str += str(p.patientId) + ": " + str(
p.patientName) + " "
err_str += "was rolled back."
print(err_str)
print(e)
dbcursor.connection.rollback()
# Check that transactions went through
check_patients = "SELECT * FROM Patients"
check_vaccines = "SELECT * FROM Vaccines"
check_cgs = "SELECT CaregiverSlotSchedulingId, CaregiverId, WorkDay, SlotStatus, VaccineAppointmentId "
check_cgs += "FROM CaregiverSchedule where SlotStatus IN (1, 2)"
check_appts = "SELECT * FROM VaccineAppointments"
# exactly 5 patients
dbcursor.execute(check_patients)
rows = dbcursor.fetchall()
self.assertEqual(len(rows), 5)
# exactly 1 vaccine, 4 reserved doses, 2 per successful patient
dbcursor.execute(check_vaccines)
rows = dbcursor.fetchall()
self.assertEqual(len(rows), 1) # exactly 1 vaccine
self.assertEqual(rows[0]['ReservedDoses'], 4)
# 4 caregiver slots with slotstatus 1 or 2
dbcursor.execute(check_cgs)
rows = dbcursor.fetchall()
self.assertEqual(len(rows), 4)
# 4 vaccine appointments
dbcursor.execute(check_appts)
rows = dbcursor.fetchall()
self.assertEqual(len(rows), 4)
clear_tables(sqlClient)