def test_db_connection(self):
try:
self.connection_manager = SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password"))
self.conn = self.connection_manager.Connect()
except Exception:
self.fail("Connection to database 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_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_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:
try:
# clear the tables before testing
clear_tables(sqlClient)
# create a new VaccineCaregiver object
self.caregiver_a = VaccineCaregiver(name="Steve Ma",
cursor=cursor)
# create a new Patient object
self.patient_a = patient(name='dj', cursor=cursor)
# See what vaccines are available
#create a reservation
self.reservation_a = VaccineReservationScheduler()
self.reservedId = self.reservation_a.PutHoldOnAppointmentSlot(
cursor=cursor)
self.patient_a.ReserveAppointment(self.reservedId,
'Pfizer', cursor)
except Exception:
# clear the tables if an exception occurred
clear_tables(sqlClient)
self.fail("Reservation failed")
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 Patient object
self.patient_a = patient(name='dj', cursor=cursor)
sqlQuery = '''
SELECT *
FROM Patients
WHERE PatientName = 'dj'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) < 1:
self.fail("Patientnot found")
# clear the tables after testing, just in-case
clear_tables(sqlClient)
print(rows[0])
except Exception:
# clear the tables if an exception occurred
# clear_tables(sqlClient)
self.fail("Creating Patient failed")
def test_verify_schedule(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 VaccineCaregiver object
self.caregiver_a = VaccineCaregiver(name="Steve Ma",
cursor=cursor)
# check if schedule has been correctly inserted into CareGiverSchedule
sqlQuery = '''
SELECT *
FROM Caregivers, CareGiverSchedule
WHERE Caregivers.CaregiverName = 'Steve Ma'
AND Caregivers.CaregiverId = CareGiverSchedule.CaregiverId
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
hoursToSchedlue = [10, 11]
minutesToSchedlue = [0, 15, 30, 45]
for row in rows:
slot_hour = row["SlotHour"]
slot_minute = row["SlotMinute"]
if slot_hour not in hoursToSchedlue or slot_minute not in minutesToSchedlue:
self.fail("CareGiverSchedule 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("CareGiverSchedule verification failed")
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_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 VaccineCaregiver object
self.caregiver_a = VaccineCaregiver(name="Steve Ma",
cursor=cursor)
# check if the patient is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM Caregivers
WHERE CaregiverName = 'Steve Ma'
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) < 1:
self.fail("Creating caregiver 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 caregiver failed")
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_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_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_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_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_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)
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_allocate2caregivers(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 caretaker object
caregiversList = []
caregiversList.append(
VaccineCaregiver(
'Carrie Nation',
cursor)) # allocates at least 2 caregivers
caregiversList.append(
VaccineCaregiver('Clare Barton', cursor))
caregivers = {}
for cg in caregiversList:
cgid = cg.caregiverId
caregivers[cgid] = cg
# check two caregivers have been created
sqlQuery = '''
SELECT *
FROM Caregivers
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 2:
print('Two caregivers were created!')
else:
self.fail('Failed to create two caregivers.')
# 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 caregivers failed due to exception.")
def test_put_on_hold1_returns_neg2(self):
"""PutOnHold returns -2"""
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
# get a cursor from the SQL connection
with sqlClient.cursor(as_dict=True) as cursor:
self.assertEqual(scheduler().PutHoldOnAppointment1(cursor), -2)
# check no updates made to table
get_schedule_sql = "SELECT * FROM CareGiverSchedule WHERE SlotStatus = 1"
cursor.execute(get_schedule_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) < 1)
clear_tables(sqlClient)
def test_put_on_hold2(self):
"""PutOnHold2 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)
# get a cursor from the SQL connection
with sqlClient.cursor(as_dict=True) as cursor:
vc = VaccineCaregiver('Carrie Nation', cursor)
scheduler().PutHoldOnAppointment2(1, 21, cursor)
# check 1 update made to table
get_schedule_sql = "SELECT * FROM CareGiverSchedule WHERE SlotStatus = 1"
cursor.execute(get_schedule_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) == 1)
clear_tables(sqlClient)
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_returns_neg2(self):
"""ScheduleAppointmentSlot returns -2"""
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)
self.assertEqual(
scheduler().ScheduleAppointmentSlot(-1, 1, cursor), -2)
self.assertEqual(
scheduler().ScheduleAppointmentSlot(
"Not a valid id", 1, cursor), -2)
get_schedule_sql = "SELECT * FROM CareGiverSchedule WHERE SlotStatus = 2"
cursor.execute(get_schedule_sql)
rows = cursor.fetchall()
self.assertTrue(len(rows) < 1)
clear_tables(sqlClient)
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_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 VaccineCaregiver object
self.caregiver_a = VaccineCaregiver(name="Steve Ma",
cursor=cursor)
#create a reservation
self.reservation_a = VaccineReservationScheduler()
self.reservedId = self.reservation_a.PutHoldOnAppointmentSlot(
cursor=cursor)
sqlQuery = '''
SELECT *
FROM CareGiverSchedule
WHERE CaregiverSlotSchedulingId = {id}
'''.format(id=self.reservedId)
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) < 1:
self.fail("No slot Available")
if rows[0]['SlotStatus'] != 1:
self.fail("Slot wasn't reserved")
# clear the tables after testing, just in-case
# clear_tables(sqlClient)
print(rows[0])
except Exception:
# clear the tables if an exception occurred
# clear_tables(sqlClient)
self.fail("Creating caregiver failed")
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_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_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_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")
return self.slotSchedulingId
except pymssql.Error as db_err:
cursor.connection.rollback()
print("Database Programming Error in SQL Query processing! ")
print("Exception code: " + db_err.args[0])
if len(db_err.args) > 1:
print("Exception message: " + str(db_err.args[1]))
print("SQL text that resulted in an Error: " +
self.getAppointmentSQL)
return -1
if __name__ == '__main__':
with SqlConnectionManager(Server=os.getenv("Server"),
DBname=os.getenv("DBName"),
UserId=os.getenv("UserID"),
Password=os.getenv("Password")) as sqlClient:
clear_tables(sqlClient)
vrs = VaccineReservationScheduler(
) # use to call PutHoldOnAppointmentSlot
# get a cursor from the SQL connection
dbcursor = sqlClient.cursor(
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
