def test_init(self):
"""Test init of COVID19Vaccine"""
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new VaccineCaregiver object
covid(vaccine_name='Pfizer',
manufac_name='Pfizer-BioNTech',
doses_in_stock=100,
doses_reserved=0,
days_between_doses=21,
doses_per_patient=2,
cursor=cursor)
# check if the patient is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) < 1:
self.fail("Creating COVID vaccine failed")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Creating COVID vaccine failed")
def test_reserve_doses_raises_exception(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
covid('Pfizer', 'Pfizer-BioNTech', 1, 0, 21, 2, cursor)
self.assertRaises(InsufficientStock, covid.reserve_doses,
'Pfizer', cursor)
def test_reserve_doses(self):
"""Test COVID19Vaccine.reserve_doses reserves the correct num of doses"""
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
covid(vaccine_name='Pfizer',
manufac_name='Pfizer-BioNTech',
doses_in_stock=100,
doses_reserved=0,
days_between_doses=21,
doses_per_patient=2,
cursor=cursor)
# get current doses
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
current_stock = 0
current_reserved = 0
for row in rows:
current_stock += row["AvailableDoses"]
current_reserved += row["ReservedDoses"]
# add new doses and check that count changes
to_reserve = 10
covid.reserve_doses('Pfizer', cursor)
cursor.execute(sqlQuery)
rows = cursor.fetchall()
for row in rows:
check_stock = row["AvailableDoses"]
check_reserve = row["ReservedDoses"]
if ((current_stock - 2) != check_stock) & (
(current_reserved + 2) != check_reserve):
self.fail(
"Stock failed to be reserved in database: " +
str(current_reserved) + "vs. " +
str(check_reserve))
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("reserve_doses method failed")
def test_AddDoses(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# Add doses to Vaccines
self.covid = covid(VaccineName = 'Pfizer', cursor = cursor)
self.covid.AddDoses(DosesToAdd = 100, cursor = cursor)
# check if the vaccine is correctly inserted into the database
sqlQuery = '''
SELECT AvailableDoses
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if rows[0].get('AvailableDoses') == 100: # not equal to one (only 1 row per VaccineName)
print("The vaccine doses were added!")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("The doses were NOT added.")
def test_init(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new Vaccine object
self.vaccine_a = covid("Pfizer", "Biotech", 2, 21, cursor)
# check if the vaccine is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) < 1:
self.fail("Creating Vaccine failed")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Creating vaccine failed")
def test_vaccine_init_bad(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new Vaccine object
self.covid = covid(VaccineName = "Janssen", cursor = cursor)
# check if bad vaccine name has NOT been inserted into Vaccines
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Janssen'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) != 0: # not equal to one (only 1 row per VaccineName)
self.fail("Added vaccine when it should not have!")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
#self.fail("Some other exception, please check!")
print('Didn\'t add vaccine to Vaccines because it is not a supported VaccineName.')
def test_add_doses(self):
"""Test COVID19Vaccine.add_doses adds the specified num of doses"""
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
covid(vaccine_name='Moderna',
manufac_name='Moderna',
doses_in_stock=100,
doses_reserved=0,
days_between_doses=28,
doses_per_patient=2,
cursor=cursor)
# get current doses
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Moderna'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
current_stock = 0
for row in rows:
current_stock += row["AvailableDoses"]
# add new doses and check that count changes
add_doses = 10
covid.add_doses('Moderna', add_doses, cursor)
cursor.execute(sqlQuery)
rows = cursor.fetchall()
for row in rows:
check_stock = row["AvailableDoses"]
if (add_doses + current_stock) != check_stock:
self.fail("Stock failed to add to database: " +
str(add_doses + current_stock) + "vs. " +
str(check_stock))
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("add_doses method failed")
def test_PutAppointmentOnHold(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
# create caretaker object
self.caregiver = VaccineCaregiver(name="Clare Barton",
cursor=cursor)
# create patient object
self.patient = patient(PatientName='Nicole Riggio',
VaccineStatus=0,
VaccineName='Pfizer',
cursor=cursor)
# Put appointment on hold
vrs = VaccineReservationScheduler()
self.vrs = vrs.PutHoldOnAppointmentSlot(cursor=cursor)
# check if the patient is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM CareGiverSchedule
WHERE SlotStatus = 1
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get('SlotStatus') == 1:
print('PutAppointmentOnHold worked!')
else:
self.fail('PutAppointmentOnHold failed')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("PutAppointmentOnHold failed due to exception")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("The appointment was NOT put on hold.")
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_deplete_reserve(self):
"""Test COVID19Vaccine.deplete_reserve reduces reserves by 1"""
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
reserve = 2
covid(vaccine_name='Pfizer',
manufac_name='Pfizer-BioNTech',
doses_in_stock=0,
doses_reserved=reserve,
days_between_doses=21,
doses_per_patient=2,
cursor=cursor)
# reduce reserve and check that count changes
covid.deplete_reserve('Pfizer', cursor)
sqlQuery = "SELECT * FROM Vaccines WHERE "
sqlQuery += "VaccineName = 'Pfizer'"
cursor.execute(sqlQuery)
rows = cursor.fetchall()
for row in rows:
check_reserve = row["ReservedDoses"]
if (reserve - 1) != check_reserve:
self.fail(
"Stock failed to be depleted in database")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("deplete_reserve method failed")
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_patient_init(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new Vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
# create a new Patient object
self.patient = patient(PatientName='Nicole Riggio',
VaccineStatus=0,
VaccineName='Pfizer',
cursor=cursor)
# check if the patient is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM Patients
WHERE PatientName = 'Nicole Riggio'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) != 1:
self.fail("Creating patient failed")
elif len(rows) == 1:
print('Patient was added initialized in Patients!')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Creating patient failed due to exception")
def test_schedule_appointment(self):
"""ScheduleAppointmentSlot returns id"""
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:
vc = VaccineCaregiver('Carrie Nation', cursor)
vaccine = covid('Moderna', 'Moderna', 10, 10, 28, 2, cursor)
sqlCreatePatient = "INSERT INTO Patients (PatientName, VaccineStatus) VALUES ("
sqlCreatePatient += "'Patrick Render', 0)"
cursor.execute(sqlCreatePatient)
sqlCreateAppt = "INSERT INTO VaccineAppointments ("
sqlCreateAppt += "VaccineName, PatientId, CaregiverId, SlotStatus) VALUES ("
sqlCreateAppt += "'Moderna', "
sqlCreateAppt += "1, "
sqlCreateAppt += str(vc.caregiverId) + ", "
sqlCreateAppt += "1)"
cursor.execute(sqlCreateAppt)
self.assertEqual(
scheduler().ScheduleAppointmentSlot(1, 0, cursor), 1)
# Caregiverschedule is updated once
get_schedule_sql = "SELECT * FROM CareGiverSchedule WHERE SlotStatus = 2"
cursor.execute(get_schedule_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 1)
# VaccineAppointments table is updated once
get_appointments_sql = "SELECT * FROM VaccineAppointments WHERE SlotStatus = 2"
cursor.execute(get_appointments_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 1)
clear_tables(sqlClient)
def test_ReserveDoses_multiple(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# Add doses to Vaccines & reserve for 2 patients
self.covid = covid(VaccineName = 'Moderna', cursor = cursor)
self.covid.AddDoses(DosesToAdd = 10, cursor = cursor)
self.ReserveDoses = self.covid.ReserveDoses(cursor = cursor)
self.ReserveDoses = self.covid.ReserveDoses(cursor = cursor)
# check if the vaccine is correctly inserted into the database
sqlQuery = '''
SELECT ReservedDoses, AvailableDoses
FROM Vaccines
WHERE VaccineName = 'Moderna'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
# print(rows)
if rows[0].get('ReservedDoses') == 4 and rows[0].get('AvailableDoses') == 6:
print("The vaccine doses were reserved for two patients!")
else:
print('Not enough doses so (correctly) didn\'t reserve or remove from AvailableDoses!')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("The doses were NOT reserved.")
def test_addDoses2(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new Vaccine object
self.vaccine_a = covid(name="Pfizer",
supplier="Biotech",
doses_per_patient=2,
days_between_doses=21,
cursor=cursor)
self.vaccine_a.AddDoses("Pfizer", 10, cursor)
self.vaccine_a.AddDoses("Pfizer", 10, cursor)
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) > 1:
self.fail("AddDoses verification failed")
available = rows[0]["AvailableDoses"]
if available != 20:
self.fail("AddDoses verification failed")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("AddDoses verification failed")
def test_add5doses(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
self.covid.AddDoses(DosesToAdd=5, cursor=cursor)
# check if the doses were added
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Pfizer' AND AvailableDoses = 5 AND TotalDoses = 5
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1:
print('Doses were added successfully!')
else:
self.fail('Vaccine doses were not added.')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Vaccine doses failed due to exception.")
def test_vaccine_init_good(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new Vaccine object
self.covid = covid(VaccineName = "Johnson & Johnson", cursor = cursor)
# check if the vaccine is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = 'Johnson & Johnson'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) != 1: # 1 row per VaccineName
self.fail("Creating vaccine failed")
elif len(rows) == 1:
print('Vaccine was added initialized in Vaccines!')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Creating vaccine failed due to exception")
def test_ReserveAppointment(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
# create caretaker object
self.caregiver = VaccineCaregiver(name="Clare Barton",
cursor=cursor)
# create patient object
self.patient = patient(PatientName='Nicole Riggio',
VaccineStatus=0,
VaccineName='Pfizer',
cursor=cursor)
# put appointment on hold
vrs = VaccineReservationScheduler()
# self.vrs = vrs.PutHoldOnAppointmentSlot(cursor = cursor)
# reserve the appointment
self.patient.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
# check if the appointment is marked as reserved & patient status is updated
sqlQuery = '''
SELECT *
FROM VaccineAppointments
WHERE PatientId = 1
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get('SlotStatus') == 1:
# print('Appt marked as reserved!')
sqlQuery = '''
SELECT *
FROM Patients
WHERE PatientName = 'Nicole Riggio'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get(
'VaccineStatus') == 1:
print('Patient queued for vaccine dose!')
else:
self.fail('Patient status not updated.')
else:
self.fail('Slot status not updated.')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("ReserveAppointment failed due to exception")
clear_tables(sqlClient)
vrs = VaccineReservationScheduler()
# get a cursor from the SQL connection
with sqlClient.cursor(as_dict=True) as dbcursor:
# 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:
def test_ScheduleAppointment(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
# Add doses to vaccine object
self.covid.AddDoses(DosesToAdd=5, cursor=cursor)
# create caretaker object
self.caregiver = VaccineCaregiver(name="Clare Barton",
cursor=cursor)
# create patient object
self.patient = patient(PatientName='Alyson Suchodolski',
VaccineStatus=0,
VaccineName='Pfizer',
cursor=cursor)
# Schedule the appointment
vrs = VaccineReservationScheduler()
self.patient.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=1, cursor=cursor),
cursor=cursor)
# Check if the appointment was scheduled & the patient status was updated
sqlQuery = '''
SELECT *
FROM VaccineAppointments
WHERE PatientId = 1
'''
# sqlQuery = '''
# SELECT *
# FROM CaregiverSchedule
# WHERE SlotStatus = 2
# '''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get('SlotStatus') == 2:
print('Appointment Marked as Scheduled!')
sqlQuery = '''
SELECT *
FROM Patients
WHERE PatientName = 'Alyson Suchodolski'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get(
'VaccineStatus') == 2:
print('First Dose Scheduled!')
sqlQuery = '''
Select *
FROM Vaccines
WHERE VaccineName = 'Pfizer'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get(
'ReservedDoses') == 2 and rows[0].get(
'AvailableDoses') == 3:
print('Vaccine inventory has been updated!')
else:
self.fail(
'Vaccine inventory could not be updated!')
else:
self.fail('Patient status not updated!')
else:
self.fail('Slot status not updated!')
# clear the tables after testing, just in-case
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("ScheduleAppointment failed due to exception")
def test_schedule2Patients(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create vaccine object
self.covid = covid(VaccineName="Pfizer", cursor=cursor)
# Add doses to vaccine object
self.covid.AddDoses(DosesToAdd=5, cursor=cursor)
# create caretaker object
self.caregiver = VaccineCaregiver(name="Clare Barton",
cursor=cursor)
self.caregiver = VaccineCaregiver(name="Carrie Nation",
cursor=cursor)
# create patient object
self.patient1 = patient(PatientName='Alyson Suchodolski',
VaccineStatus=1,
VaccineName='Pfizer',
cursor=cursor)
self.patient2 = patient(PatientName='Nicole Riggio',
VaccineStatus=1,
VaccineName='Pfizer',
cursor=cursor)
self.patient3 = patient(PatientName='Jameson Reagan',
VaccineStatus=1,
VaccineName='Pfizer',
cursor=cursor)
self.patient4 = patient(PatientName='Arianna Pilla',
VaccineStatus=1,
VaccineName='Pfizer',
cursor=cursor)
self.patient5 = patient(PatientName='Christopher Martone',
VaccineStatus=1,
VaccineName='Pfizer',
cursor=cursor)
# reserve slots for patients, then schedule slots
vrs = VaccineReservationScheduler()
p1d1 = self.patient1.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient1.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=p1d1, cursor=cursor),
cursor=cursor)
p2d1 = self.patient2.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient2.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=p2d1, cursor=cursor),
cursor=cursor)
p3d1 = self.patient3.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient3.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=p3d1, cursor=cursor),
cursor=cursor)
p4d1 = self.patient4.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient4.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=p4d1, cursor=cursor),
cursor=cursor)
p5d1 = self.patient5.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=cursor),
cursor=cursor)
self.patient5.ScheduleAppointment(
CaregiverSchedulingID=vrs.ScheduleAppointmentSlot(
slotid=p5d1, cursor=cursor),
cursor=cursor)
# check if only two rows were updated
sqlQuery = '''
SELECT *
FROM VaccineAppointments
WHERE SlotStatus = 2
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 2:
print(
'Only 2 patients could be scheduled for appointments!'
)
else:
self.fail(
'Scheduling System Failed!: Too many or not enough appointments were made.'
)
clear_tables(sqlClient)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail(
"Scheduling Appointments failed due to exception.")
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)
as_dict=True
) # think about using multiple cursor instances here !!!!!
# Iniialize the caregivers, patients & vaccine supply
caregiversList = []
caregiversList.append(VaccineCaregiver(
'Carrie Nation', dbcursor)) # allocates at least 2 caregivers
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
vax = covid(
VaccineName='Pfizer',
cursor=dbcursor) # adds at least 5 doses of a two-dose vaccine
vax.AddDoses(DosesToAdd=5, cursor=dbcursor)
# Assign patients
# patient 1
new_patient = patient(PatientName='Nicole Riggio',
VaccineStatus=0,
VaccineName='Pfizer',
cursor=dbcursor)
p1d1 = new_patient.ReserveAppointment(
CaregiverSchedulingID=vrs.PutHoldOnAppointmentSlot(
cursor=dbcursor),
cursor=dbcursor)
new_patient.ScheduleAppointment(
def test_reservation(self):
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
with sqlClient.cursor(as_dict=True) as cursor:
cursor.connection.autocommit(False)
try:
# clear the tables before testing
clear_tables(sqlClient)
# initialize vaccines
self.vaccine_1 = covid("Pfizer", "Biotech", 2, 21, cursor)
self.vaccine_2 = covid('Moderna', 'Moderna', 2, 28, cursor)
self.vaccines = [self.vaccine_1, self.vaccine_2]
self.vaccine_1.AddDoses("Pfizer", 2, cursor)
self.vaccine_2.AddDoses("Moderna", 3, cursor)
# create a new VaccineCaregiver object
self.caregiver_a = VaccineCaregiver(name="John",
cursor=cursor)
self.caregiver_b = VaccineCaregiver(name="Steve",
cursor=cursor)
# create a new Patient object
self.patients = [
patient(name='Marc', cursor=cursor),
patient(name='Marc2', cursor=cursor),
patient(name='Marc3', cursor=cursor),
patient(name='Marc4', cursor=cursor),
patient(name='Marc5', cursor=cursor)
]
# for each patient:
for patient_a in self.patients:
# See what vaccines are available
for vaccine_a in self.vaccines:
sqlQuery = '''
SELECT *
FROM Vaccines
WHERE VaccineName = '{name}'
'''.format(name=vaccine_a.name)
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) > 0:
if rows[0]['AvailableDoses'] >= rows[0][
'DosesPerPatient']:
# if enough doses are available
# 1) create a reservation
self.reservation_a = VaccineReservationScheduler(
)
# 2) get first caregiver slot ID & reserve it & schedule it
self.reservedId = self.reservation_a.PutHoldOnAppointmentSlot(
cursor=cursor)
# if no slot is available, rollback commit
if self.reservedId in [0, -1]:
cursor.connection.rollback()
patient_a.first_VaccineAppointmentId = 0
print(
"No slots available in the next 3 weeks"
)
break
else:
patient_a.first_VaccineAppointmentId = patient_a.ReserveAppointment(
self.reservedId, vaccine_a.name,
cursor)
patient_a.vaccine_name = vaccine_a.name
# 3) get second slot & reserve it
self.reservation_a.ScheduleAppointmentSlot(
slotid=self.reservedId,
cursor=cursor)
patient_a.ScheduleAppointment(
Vaccine=vaccine_a, cursor=cursor)
days_between_doses = int(
rows[0]['DaysBetweenDoses'])
if int(rows[0]
['DosesPerPatient']) == 2:
self.reservedId = self.reservation_a.PutHoldOnAppointmentSlot(
cursor=cursor,
date=datetime.datetime.now() +
datetime.timedelta(
days=days_between_doses))
if self.reservedId in [0, -1]:
cursor.connection.rollback()
patient_a.first_VaccineAppointmentId = 0
patient_a.second_VaccineAppointmentId = 0
patient_a.vaccine_name = ''
# if second slot is not available try next vaccine
print(
"second slot not available for, cancelling first appointment & checking other vaccines",
vaccine_a.name)
continue
else:
patient_a.second_VaccineAppointmentId = patient_a.ReserveAppointment(
self.reservedId,
vaccine_a.name, cursor)
patient_a.vaccine_name = vaccine_a.name
self.reservation_a.ScheduleAppointmentSlot(
slotid=self.reservedId,
cursor=cursor)
patient_a.ScheduleAppointment(
Vaccine=vaccine_a,
cursor=cursor)
break
else:
print(vaccine_a.name,
"not enough doses available")
if patient_a.first_VaccineAppointmentId != 0:
print(
"Reservation Successful for Patient!!!!!!!!!!!",
patient_a.name)
cursor.connection.commit()
else:
print("not successful")
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Reservation failed")
