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_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 ScheduleAppointment(self, CaregiverSlotID, VaccineAppointmentID,
Vaccine, cursor):
'''update the Patient’s VaccineStatus from "Queued for first dose" to 1st Dose Scheduled
maintain the Vaccine inventory'''
from COVID19_vaccine import COVID19Vaccine as CovidVaccine
from vaccine_reservation_scheduler import VaccineReservationScheduler as VaccScheduler
#======================================================================
#Maintain vaccine inventory
#Reserve 2 doses
CovidVaccine.reserve_doses(getattr(Vaccine, 'vaccine_name'), cursor)
#======================================================================
#Update caregiver slot status from hold to scheduled
#Update vaccineappointment slot status from hold to scheduled
sqlUpdateSlotStatuses = VaccScheduler().ScheduleAppointmentSlot(
CaregiverSlotID, VaccineAppointmentID, cursor)
#======================================================================
#Update patient appointment status: Queued for 1st dose -> Scheduled for first dose
sqlUpdatePatientStatus = "UPDATE Patients SET VaccineStatus = 2 "
sqlUpdatePatientStatus += "WHERE PatientId = "
sqlUpdatePatientStatus += str(self.patientId)
try:
cursor.execute(sqlUpdatePatientStatus)
print(
'Query executed successfully. Patient status updated using patientID = '
+ str(self.patientId))
except pymssql.Error as db_err:
print(
"Database Programming Error in SQL Query processing for Patients! "
)
print("Exception code: " + str(db_err.args[0]))
if len(db_err.args) > 1:
print("Exception message: " + db_err.args[1])
print("SQL text that resulted in an Error: " +
sqlUpdatePatientStatus)
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 ScheduleAppointment(self, CaregiverSchedulingID, cursor): #Vaccine, cursor):
'''
Mark Appointments as Scheduled, update the PatientVaccine Status Field, Maintain the Vaccine Inventory
Update the Caregiver Scheduler Table
'''
self.CaregiverSchedulingID = CaregiverSchedulingID
try:
# check if enough doses to schedule
_AvailableDoses = covid.ReserveDoses(self = self, cursor = cursor)
print('output reservedoses: ', _AvailableDoses)
# 1st: Update the appointment status from on hold to scheduled
_sqlCheckApptStatus = "SELECT * FROM CareGiverSchedule WHERE CaregiverSlotSchedulingId = " + str(CaregiverSchedulingID)
cursor.execute(_sqlCheckApptStatus)
appt_row = cursor.fetchone()
_CaregiverId = appt_row.get('CaregiverId')
_SlotStatus = appt_row.get('SlotStatus')
if _SlotStatus == 1: # if SlotStatus = "On Hold", update to scheduled
_sqlUpdate = "UPDATE CareGiverSchedule SET SlotStatus = " + str(2) + "WHERE SlotStatus = " + str(1) + "AND CaregiverSlotSchedulingId = " + str(CaregiverSchedulingID)
cursor.execute(_sqlUpdate)
cursor.connection.commit()
_sqlGetApptInfo = "SELECT * FROM VaccineAppointments WHERE PatientId = " + str(self.PatientId)
_sqlGetApptInfo += " AND CaregiverId = " + str(_CaregiverId) #+ "AND DoseNumber = " + str(_DoseNumber)
cursor.execute(_sqlGetApptInfo)
apptID_row = cursor.fetchone()
_ApptId = apptID_row.get('VaccineAppointmentId')
if _ApptId >= 0 and _AvailableDoses >=2:
# update patient status
_sqlUpdateVaccineSchedule = "UPDATE VaccineAppointments SET SlotStatus = " + str(2) + " WHERE VaccineAppointmentId = " + str(_ApptId) #+ "AND SlotStatus = " + str(1)
cursor.execute(_sqlUpdateVaccineSchedule)
cursor.connection.commit()
# 2nd: get patient details, update patient vaccine status field
_sqlCheckPatientStatus = "SELECT * FROM Patients WHERE PatientName = '" + str(self.PatientName) + "'" # check if right call
cursor.execute(_sqlCheckPatientStatus)
patient_row = cursor.fetchone()
_VaccineStatus = patient_row.get('VaccineStatus')
# check if queued for first dose
if _VaccineStatus == 1: # NEED TO CHECK IF TWO DOSES REQUIRED !!!!!
# store 1st appointment id (for vrs!!!)
_sqlGetApptInfo = "SELECT * FROM VaccineAppointments WHERE PatientId = " + str(self.PatientId)
_sqlGetApptInfo += " AND CaregiverId = " + str(_CaregiverId) #+ "AND DoseNumber = " + str(_DoseNumber)
cursor.execute(_sqlGetApptInfo)
apptID_row = cursor.fetchone()
_ApptId = apptID_row.get('VaccineAppointmentId')
_UpdatedVaccineStatus = 2 # "1st dose scheduled"
# update patient status
_sqlUpdatePatientStatus = "UPDATE Patients SET VaccineStatus = " + str(_UpdatedVaccineStatus) + " WHERE PatientId = " + str(self.PatientId)
cursor.execute(_sqlUpdatePatientStatus)
cursor.connection.commit()
# print('All queries in ScheduleAppointments passed!!!!!!')
# add provision for dose 2
except pymssql.Error as db_err:
cursor.connection.rollback()
print("Database Programming Error in SQL Query processing for Vaccine Patient class!")
print("Exception code: " + str(db_err.args[0]))
if len(db_err.args) > 1:
print("Exception message: " + db_err.args[1])
print("SQL text that resulted in an Error: " + _sqlUpdatePatientStatus)
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:
# Schedule the patients
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)