def test_main_two_patients(self):
'''
Add 2 caregivers, 2 patients, and one vaccine
Try to schedule patients in order added to db
Successfully schedule first appointment
Successfuly hold second appointment
Successfully reduce vaccine reserve by 4
'''
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))
# 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()
self.fail()
# 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 2 patients
dbcursor.execute(check_patients)
rows = dbcursor.fetchall()
self.assertEqual(len(rows), 2)
for row in rows:
self.assertEqual(row['VaccineStatus'], 2)
# exactly 1 vaccine, 4 reserved doses, 2 per 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)
def test_ScheduleAppointmentSlot(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)
# schedule appointment
self.vrs = vrs.ScheduleAppointmentSlot(slotid=1,
cursor=cursor)
# check if the patient is correctly inserted into the database
sqlQuery = '''
SELECT *
FROM CareGiverSchedule
WHERE SlotStatus = 2
'''
cursor.execute(sqlQuery)
rows = cursor.fetchall()
if len(rows) == 1 and rows[0].get('SlotStatus') == 2:
print('ScheduleAppointmentSlot worked!')
else:
self.fail('ScheduleAppointmentSlot 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(
"ScheduleAppointmentSlot 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 properly scheduled.")
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_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_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")
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")