def updateToRecovered(personId): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; recoverDateValidated = False; recoverDate = 'YYYY-MM-DD'; confirmValidated = False; confirm = 0; print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\nUPDATE TO RECOVERED\n\nEnter recovery date:") while(not recoverDateValidated): try: print("\nRecovery Date\n--------------") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); recoverDate = datetime.date(year, month, day); try: cur.callproc('validate_recoveryDate', [personId, recoverDate]) recoverDateValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not confirmValidated): print("\nAre you sure you want to update person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): try: cur.callproc('infected_to_recovered_', [personId, recoverDate]) cnx.commit() print("Successfully inserted in recovered and deleted from infected!") covid19_tracker_interface.update() except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\nChange of status aborted") covid19_tracker_interface.update() elif(confirm == 2): print("\nChange of status aborted") update_status();
def update_person(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; personTable = 'SELECT * FROM person'; updatableFields = ["firstName", "lastName", "dob", "sex", "homeRegion", "currentRegion"] updateFirstName = 'UPDATE person SET firstName = %s WHERE id = %s'; updateLastName = 'UPDATE person SET lastName = %s WHERE id = %s'; updateDob = 'UPDATE person SET dob = %s WHERE id = %s'; updateSex = 'UPDATE person SET sex = %s WHERE id = %s'; updateCurrentRegion = 'UPDATE person SET currentRegion = %s WHERE id = %s'; updateHomeRegion = 'UPDATE person SET homeRegion = %s WHERE id = %s'; regionValidated2 = False; validateId = False; fNameValidated = False; lNameValidated = False; sexValidated = False; dobValidated = False; regionValidated = False; confirmValidated = False; confirm = 0; isInfected = False; personId = 0; firstName = ''; lastName = ''; sex = ''; dob = 'YYYY-MM-DD'; homeRegion = 0; currentRegion = 0; print("\nSelect id of person to update from table below: "); cur.execute(personTable) return_table(cur); while(not validateId): try: personId = int(input("Person ID: ")) if(not isinstance(personId, int)): print("Please enter a number") else: try: cur.callproc('does_personId_exist', [int(personId)]) print("ID validated!\n") validateId = True; except pymysql.Error as e: err = e.args[1] print(err + "\n") except: print("Please enter a number") print("\nEnter new fields (put in old value for fields you do not wish to update):") while(not fNameValidated): firstName = str(input("First name: ")); if(not firstName.replace(" ", "").isalpha()): print("Please enter a string") else: fNameValidated = True; while(not lNameValidated): lastName = str(input("Last name: ")); if(not lastName.replace(" ", "").isalpha()): print("Please enter a string") else: lNameValidated = True; while(not sexValidated): sex = str(input("Sex: ")).lower() if(sex == 'male' or sex == 'female' or sex == 'other'): sexValidated = True; else: print("Please enter string: male, female, or other") while(not dobValidated): try: print("Date of Birth") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); dob = datetime.date(year, month, day); try: cur.callproc('validate_dateOfBirth', [dob]) cur.callproc('validate_dob_infectDate', [dob, personId]) dobValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not regionValidated): homeRegion = str(input("Home Region: ")); currentRegion = str(input("Current Region: ")); if((not homeRegion.replace(" ", "").isalpha()) or (not currentRegion.replace(" ", "").isalpha())): print("Please enter a string") else: try: cur.callproc('verify_person_regionName', [homeRegion, currentRegion]) print("\nRegions successfully validated!") try: query = 'SELECT getRegionId(%s)'; tempHome = cur.execute(query, homeRegion) tempHome2 = cur.fetchall()[0] tempHome = getNum("%s" % str(tempHome2)) homeRegion = tempHome tempCurrent = cur.execute(query, currentRegion) tempCurrent2 = cur.fetchall()[0] tempCurrent = getNum("%s" % str(tempCurrent2)) currentRegion = tempCurrent regionValidated = True; except pymysql.Error as e: print("\n" + err + "\n") regionValidated = true; except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(regionName) FROM region'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif('Multiple instances' in err): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT id, regionName, country, latitude, longitude FROM region WHERE regionName = %s or regionName = %s'; cur.execute(query, (homeRegion, currentRegion)) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not regionValidated2): try: homeRegion = int(input("Home Region ID: ")); currentRegion = int(input("Current Region ID: ")) try: cur.callproc('does_regionId_exist', [homeRegion]) cur.callproc('does_regionId_exist', [currentRegion]) regionValidated2 = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") except Exception as e: print("Please enter a number") regionValidated = True; updateFirstName = 'UPDATE person SET firstName = %s WHERE id = %s'; updateLastName = 'UPDATE person SET lastName = %s WHERE id = %s'; updateDob = 'UPDATE person SET dob = %s WHERE id = %s'; updateSex = 'UPDATE person SET sex = %s WHERE id = %s'; updateCurrentRegion = 'UPDATE person SET currentRegion = %s WHERE id = %s'; updateHomeRegion = 'UPDATE person SET homeRegion = %s WHERE id = %s'; while(not confirmValidated): print("\nAre you sure you want to update person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): print("\nFields updated: ") try: cur.execute(updateFirstName, (str(firstName), personId)) cnx.commit(); print("firstName, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateLastName, (str(lastName), personId)) cnx.commit(); print("lastName, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateDob, (dob, personId)) cnx.commit(); print("dob, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateSex, (str(sex), personId)) cnx.commit(); print("sex, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateHomeRegion, (int(homeRegion), personId)) cnx.commit(); print("homeRegion, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.callproc('isInfected', [int(personId)]) cur.execute(updateCurrentRegion, (int(currentRegion), personId)) cnx.commit(); print("currentRegion - all fields successfully updated! ") except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\n\nSuccessfully updated!\n") covid19_tracker_interface.update(); elif(confirm == 2): print("Insert of " + firstName + " " + lastName + " aborted.") covid19_tracker_interface.update();
def update_hospital(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; hospitalTable = 'SELECT * FROM hospital'; updatableFields = ["name", "regionId"] updateName = 'UPDATE hospital SET name = %s WHERE id = %s'; updateRegion = 'UPDATE hospital SET regionId = %s WHERE id = %s'; hospitalName = ''; regionValidated = False; regionValidated2 = False; validateId = False; nameAndRegionValidated = False; nameValidated = False; confirmValidated = False; confirm = 0; nameValidated = False; hospitalId = 0; region = 0; print("\nSelect id of hospital to update from table below: "); cur.execute(hospitalTable) return_table(cur); while(not validateId): try: hospitalId = int(input("Hospital ID: ")) if(not isinstance(hospitalId, int)): print("Please enter an ID number") else: try: cur.callproc('does_hospitalId_exist', [int(hospitalId)]) print("ID validated!\n") validateId = True; except pymysql.Error as e: err = e.args[1] print(err + "\n") except Exception as e: print("Please enter an ID number") print("\nEnter new fields (put in old value for fields you do not wish to update):") while(not nameAndRegionValidated): while(not nameValidated): hospitalName = str(input("Hospital name: ")); if(not hospitalName.replace(" ", "").isalpha()): print("Please enter a string") else: nameValidated = True; while(not regionValidated): region = str(input("Region: ")); if((not region.replace(" ", "").isalpha())): print("Please enter a string") else: try: cur.callproc('verify_hospital_regionName', [region]) print("\nRegion successfully validated!") try: query = 'SELECT getRegionId(%s)'; tempHome = cur.execute(query, region) tempHome2 = cur.fetchall()[0] tempHome = getNum("%s" % str(tempHome2)) region = tempHome regionValidated = True; except pymysql.Error as e: print("\n" + err + "\n") regionValidated = true; except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(regionName) FROM region'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif('Multiple instances' in err): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT id, regionName, country, latitude, longitude FROM region WHERE regionName = %s'; cur.execute(query, (region)) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not regionValidated2): try: region = int(input("Region ID: ")); try: cur.callproc('does_regionId_exist', [region]) regionValidated2 = True; print("\nRegion successfully validated!") except pymysql.Error as e: print("\n" + e.args[1] + "\n") except Exception as e: print("Please enter a number") regionValidated = True; try: cur.callproc('verify_hospital', [hospitalName, region]) print("Successfully validated new fields!") nameAndRegionValidated = True; regionValidated = True; nameValidated = True; except pymysql.Error as e: print("\n" + e.args[1]) print("You cannot have more than one hospital with the same name of the same region.") print("\n" + "Aborting update of hospital" + "\n") regionValidated = False; nameValidated = False; covid19_tracker_interface.update() while(not confirmValidated): print("\nAre you sure you want to update hospital?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; updateName = 'UPDATE hospital SET name = %s WHERE id = %s'; updateRegion = 'UPDATE hospital SET regionId = %s WHERE id = %s'; if(confirm == 1): try: print("Fields updated: ") try: cur.execute(updateName, (str(hospitalName), hospitalId)) cnx.commit(); print("name, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateRegion, (int(region), hospitalId)) cnx.commit(); print("region - all fields successfully updated! ") except pymysql.Error as e: print(e.args[1] + "\n") print("\n\nSuccessfully updated!\n") covid19_tracker_interface.update(); except pymysql.Error as e: print("\n" + e.args[1] + "\n") covid19_tracker_interface.update() elif(confirm == 2): print("Update of " + hospitalName + " aborted.") covid19_tracker_interface.update();
def update_region(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; regionTable = 'SELECT * FROM region'; updatableFields = ["regionName", "country", "latitude", "longitude", "totalPop", "lockdown"] updateRegionName = 'UPDATE region SET regionName = %s WHERE id = %s'; updateCountry = 'UPDATE region SET country = %s WHERE id = %s'; updateLatitude = 'UPDATE region SET latitude = %s WHERE id = %s'; updateLongitude = 'UPDATE region SET longitude = %s WHERE id = %s'; updateTotalPop = 'UPDATE region SET totalPop = %s WHERE id = %s'; updateLockdown = 'UPDATE region SET lockdown = %s WHERE id = %s'; nameValidated = False; countryValidated = False; latValidated = False; longValidated = False; totalPopValidated = False; lockDownValidated = False; validateId = False; confirmValidated = False; name = ''; country = ''; lat = 0; lon = 0; totalPop = 0; lockdown = False; regionId = 0; print("\nSelect id of region to update from table below: "); cur.execute(regionTable) return_table(cur); while(not validateId): try: regionId = int(input("Region ID: ")) if(not isinstance(regionId, int)): print("Please enter a number") else: try: cur.callproc('does_regionId_exist', [int(regionId)]) print("ID validated!\n") validateId = True; except pymysql.Error as e: err = e.args[1] print(err + "\n") except: print("Please enter a number") print("\nEnter new fields (put in old value for fields you do not wish to update):") while(not nameValidated): name = str(input("Region name: ")) if(not name.replace(" ", "").isalpha()): print("Please enter a string") else: nameValidated = True; while(not countryValidated): country = str(input("Country name: ")) if(not country.replace(" ", "").isalpha()): print("Please enter a string") else: countryValidated = True; while(not latValidated): try: lat = float(input("Latitude: ")) if(not isinstance(lat, float) and not isinstance(lat, int)): print("Please enter a number") else: latValidated = True; except: print("Please enter a number") while(not longValidated): try: lon = float(input("Longitude: ")) if(not isinstance(lon, float) and not isinstance(lon, int)): print("Please enter a number") else: longValidated = True; except: print("Please enter a number") while(not totalPopValidated): try: totalPop = int(input("Total Population: ")) if(not isinstance(totalPop, int)): print("Please enter a number") else: totalPopValidated = True; except: print("Please enter a number") while(not lockDownValidated): lockdown = input(name + " on lockdown? (y/n): ") if((lockdown is not 'y') and (lockdown is not 'n')): print("Please enter y or n") else: if(lockdown == 'y'): lockdown = True; elif(lockdown == 'n'): lockdown = False; lockDownValidated = True; try: cur.callproc('verify_uniqueRegion', [name, country, lat, lon]) print("Region fields validated!") except pymysql.Error as e: print("\n" + e.args[1] + "\n"); print("Try again?\n(1) Yes\n(2) Abort insert region") tryAgain = int(input("Enter a number (1-2)")) if(tryAgain == 1): insert_region(); elif(tryAgain == 2): create(); else: print("Please enter a number 1-2") while(not confirmValidated): print("\nAre you sure you want to update region?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): try: print("Fields updated: ") try: cur.execute(updateRegionName, (str(name), regionId)) cnx.commit(); print("name, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateCountry, (str(country), regionId)) cnx.commit(); print("country, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateLatitude, (float(lat), regionId)) cnx.commit(); print("longitude, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateLongitude, (float(lon), regionId)) cnx.commit(); print("latitude, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateTotalPop, (int(totalPopValidated), regionId)) cnx.commit(); print("totalPop, ") except pymysql.Error as e: print(e.args[1] + "\n") try: cur.execute(updateLockdown, (lockdown, regionId)) cnx.commit(); print("lockdown, - all fields successfully updated!") print("\n\nSuccessfully updated!\n") covid19_tracker_interface.update(); except pymysql.Error as e: print(e.args[1] + "\n") except pymysql.Error as e: print("\n" + e.args[1] + "\n") cnx.rollback(); covid19_tracker_interface.update() elif(confirm == 2): print("Update of " + name + " aborted.") covid19_tracker_interface.update();
def updateToInfected(personId): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; infectDateValidated = False; infectDate = 'YYYY-MM-DD'; confirmValidated = False; admitHospitalValidated = False; admitToHospital = 0; considerHospital = False; hospitalValidated = False; currentRegion = 0; confirm = 0; try: curRegQuery = 'SELECT getCurrentRegion(%s)'; tempReg = cur.execute(curRegQuery, (personId)) tempReg2 = cur.fetchall()[0] tempReg = getNum("%s" % str(tempReg2)) currentRegion = tempReg considerHospital = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\nUPDATE TO INFECTED\n\nEnter infection date:") while(not infectDateValidated): try: print("\nInfection Date\n----------------") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); infectDate = datetime.date(year, month, day); try: cur.callproc('validate_InfectDate', [personId, infectDate]) infectDateValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not confirmValidated): print("\nAre you sure you want to update person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): try: cur.callproc('recovered_to_infected', [personId, infectDate]) print("Successfully inserted in dead and deleted from infected!") if(considerHospital): try: cur.callproc('check_hospital_in_region', [int(currentRegion)]); while(not admitHospitalValidated): print("\n\nAdmit to a hospital?\n(1) Yes\n(2) No") try: admitToHospital = int(input("\nEnter a number (1-2): ")); if(admitToHospital == 1): admitHospitalValidated = True; while(not hospitalValidated): hospital = str(input("Enter hospital name: ")) for x in hospital: if(x.isalpha() or x == ' '): continue; else: print("Please enter a string") break; try: cur.callproc('hospital_exists_in_region', [hospital, int(currentRegion)]) print("Hospital successfully validated!") try: query = 'SELECT getHospId(%s, %s)'; tempHospital = cur.execute(query, (hospital, currentRegion)) tempHospital2 = cur.fetchall()[0] tempHospital = getNum("%s" % str(tempHospital2)) hospital = tempHospital hospitalValidated = True; admitHospitalValidated = True; hospQuery = 'INSERT INTO hospital_visit() VALUES(DEFAULT, %s, %s, %s, DEFAULT, DEFAULT)' cur.execute(hospQuery, (int(personId), int(hospital), infectDate)); print("Person admitted to hospital.\n") except pymysql.Error as e: print("\n" + e.args[1] + "\n") except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('does not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(name) FROM hospital where regionId = %s'; cur.execute(query, (int(currentRegion))) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif(admitToHospital == 2): admitHospitalValidated = True; except: print("\nPlease enter a number 1-2") except pymysql.Error as e: admitToHospital = 0; print("\n" + e.args[1]) print("Person will not be admitted to a hospital at this time.\n") cnx.commit() covid19_tracker_interface.update() except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\nChange of status aborted") covid19_tracker_interface.update() elif(confirm == 2): print("\nChange of status aborted") update_status();
def update_status(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; personTable = 'SELECT * FROM person'; count = 0; personId = 0; numAction = 0; actionValidated = False; validateId = False; lineBreak = "\n"; print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\n(0) Exit Application\n(1) Go back"); print("\nSelect id of person to change status for from the table below: "); cur.execute(personTable) return_table(cur); while(not validateId): try: personId = int(input("Person ID: ")) if(not isinstance(personId, int)): print("Please enter a number") else: try: cur.callproc('does_personId_exist', [int(personId)]) print("ID validated!\n") validateId = True; except pymysql.Error as e: err = e.args[1] print(err + "\n") except: print("Please enter a number") while(not actionValidated): print("\nSelect a Status Change:\n-----------------------" + "\n(2) Update to infected\n(3) Update to recovered\n(4) Update to dead"); if(count > 0): lineBreak = ""; try: numAction = int(input(lineBreak + "Enter a number (0-4): ")); if(not isinstance(numAction, int)): print("\nPlease enter a number"); else: if(numAction > 5 or numAction < 0): print("\nPlease enter a number 0-4") elif(numAction == 0): print(exitMsg); actionValidated = True; os._exit(0); cnx.close(); elif(numAction == 1): covid19_tracker_interface.update() actionValidated = True; elif(numAction == 2): updateToInfected(personId); actionValidated = True; elif(numAction == 3): updateToRecovered(personId) actionValidated = True; elif(numAction == 4): updateToDead(personId) actionValidated = True; else: print("\nPlease enter a number 0-4") count += 1; actionValidated = True; except Exception as e: print("\nPlease enter a number 0-4")