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")