def addNewProtocol():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
protocolName = request.args.get('protocolName')
type = request.args.get('type')
initialInstruction = request.args.get('initialInstruction')
mapOfBuildingIds = json.loads(request.args.get('mapOfBuildingIds'))
print(mapOfBuildingIds)
insertQuery = """
Insert Into Protocols(schoolName, protocolName, type, initialInstruction)
values ("UIUC", %s, %s, %s)
"""
cursor = cnx.cursor()
cursor.execute(insertQuery, (protocolName, type, initialInstruction))
cnx.commit()
query = """
Select id from Protocols Where schoolName = %s AND protocolName = %s
"""
cursor = cnx.cursor()
cursor.execute(query, ("UIUC", protocolName))
result = cursor.fetchall()
print("ID of protocol ", result[0][0])
updateTables(result[0][0], mapOfBuildingIds)
return jsonify(True)
def getAllBuildings():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
allItems = []
query = """
Select idBuilding, latitude, longitude, xTile, yTile from GeoFeatures where schoolName = "UIUC" Order By created_at;
"""
cursor = cnx.cursor()
cursor.execute(query)
result = cursor.fetchall()
retList = []
for res in result:
retList.append({
'buildingId': res[0],
'latitude': res[1],
'longitude': res[2],
"xTile": res[3],
"yTile": res[4]
})
return retList
def getProtocols():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
query = """
Select * from Protocols Where schoolName = "UIUC"
"""
cursor = cnx.cursor()
cursor.execute(query)
result = cursor.fetchall()
retList = []
for res in result:
retList.append({
"id": res[0],
"schoolName": res[1],
"protocolName": res[2],
"initialInstruction": res[3],
"type": res[4]
})
return retList
def getSingleBuilding():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
buildingId = request.args.get('buildingId')
query = """
Select idBuilding, latitude, longitude, xTile, yTile from GeoFeatures where idBuilding = %s %s Order By created_at;
"""
cursor = cnx.cursor()
cursor.execute(query, (buildingId, ""))
result = cursor.fetchall()
retList = []
for res in result:
retList.append({
'buildingId': res[0],
'latitude': res[1],
'longitude': res[2],
"xTile": res[3],
"yTile": res[4]
})
return jsonify(retList)
def getActiveBuildings():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
allItems = []
query = """
Select distinct ProtocolStatusCurrent.buildingId, latitude, longitude, buildingStatus from ProtocolStatusCurrent join GeoFeatures on ProtocolStatusCurrent.buildingID = GeoFeatures.idBuilding;
"""
cursor = cnx.cursor()
cursor.execute(query)
result = cursor.fetchall()
retList = []
for res in result:
retList.append({
'buildingId': res[0],
'latitude': res[1],
'longitude': res[2],
"color": res[3]
})
return jsonify(retList)
def getAllMessages():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
query = """
Select fullName, phoneNumber, message, created_at
From People, MessageBoard
Where People.id = MessageBoard.personId and schoolName = "UIUC" and fullName is not NULL
Order By created_at DESC;
"""
cursor = cnx.cursor()
cursor.execute(query)
result = cursor.fetchall()
retList = []
for res in result:
retList.append({
"fullName": res[0],
"phoneNumber": res[1],
"message": res[2]
})
print(result)
return jsonify(retList)
def addAllGeo(zoneTypes, zonePoints, tileCoords):
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
assert len(zoneTypes) == len(zonePoints) == len(tileCoords)
idSet = []
for i in range(len(zoneTypes)):
idQuery = """
Select MAX(idBuilding) + 1
From
GeoFeatures;
"""
cursor = cnx.cursor()
cursor.execute(idQuery)
result = cursor.fetchall()
buildingID = result[0][0]
print(buildingID)
idSet.append(buildingID)
currentZoneName = zoneTypes[i]
print(currentZoneName)
for j in range(len(zonePoints[i])):
lat = zonePoints[i][j][0]
lon = zonePoints[i][j][1]
xCoord = tileCoords[i][j][0]
yCoord = tileCoords[i][j][1]
print(xCoord)
print(yCoord)
print(lat)
print(lon)
queryInsert = 'Insert Into GeoFeatures(idBuilding, latitude, longitude, xTile, yTile, schoolName, structureType) values (%s, %s,%s,%s,%s,"UIUC", %s); '
cursor = cnx.cursor()
print()
cursor.execute(
queryInsert,
(buildingID, lat, lon, xCoord, yCoord, currentZoneName))
cnx.commit()
return idSet
def sendMessage():
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
pid = request.args.get('pid')
message = request.args.get('message')
school = request.args.get('school')
result = getAdminPhoneNums()
sid = get_file_contents('sid.txt')
authToken = get_file_contents('TwilioAuth.txt')
client = Client(sid, authToken)
for res in result:
mes = client.messages.create(
to=str(res[1]),
from_='15087318632',
body="This is an automated message from: " + str(pid) + " \n " +
str(message))
print(mes.sid)
try:
queryInsert = 'Insert Into MessageBoard(personId, message, schoolName) values (%s, %s, %s);'
cursor = cnx.cursor()
cursor.execute(queryInsert, (pid, str(message), school))
cnx.commit()
return jsonify(True)
except Exception as e:
print(e)
return jsonify({"passed": False, "error": str(e)})
def endEmergency():
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
insertQuery = """
Delete From ProtocolStatusCurrent Where ProtocolStatusCurrent.schoolName = "UIUC"
"""
cursor = cnx.cursor()
cursor.execute(insertQuery)
cnx.commit()
return render_template("NoEmergency.html")
def getInitialBuildings():
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
protocolID = request.args.get('protocolID')
query = getQueryBuildingInit()
cursor = cnx.cursor()
cursor.execute(query, (protocolID, "UIUC"))
result = cursor.fetchall()
buildingPoints = []
for res in result:
buildingPoints.append({"protocolID": res[0], "buildingID": res[1], "latitude": res[2], "longitude": res[3], "schoolName": res[4], "buildingStatus": res[5]})
return jsonify(buildingPoints)
def removeBuilding():
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
buildingId = request.args.get('buildingId')
insertQuery = """
Delete From ProtocolStatusCurrent Where ProtocolStatusCurrent.buildingID = %s AND ProtocolStatusCurrent.schoolName = %s
"""
cursor = cnx.cursor()
cursor.execute(insertQuery, (buildingId, "UIUC"))
cnx.commit()
return jsonify(True)
def updateTables(protocolID, mapOfBuildingIds):
host_name = get_file_contents("HostDB")
cnx = mysql.connector.connect(user='******',
password='******',
host=host_name,
database='innodb')
for key in mapOfBuildingIds.keys():
status = mapOfBuildingIds[key]
insertQuery = """ Insert Into ProtocolToBuilding(protocolID, buildingID) values (%s, %s) """
protocolStatusInitial = """ Insert Into ProtocolStatusInitial(protocolName, buildingID, buildingStatus) values (%s, %s, %s) """
cursor = cnx.cursor()
cursor.execute(insertQuery, (protocolID, key))
cursor.execute(protocolStatusInitial, (protocolID, key, status))
cnx.commit()
def addBuilding():
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
buildingId = request.args.get('buildingId')
status = request.args.get('status')
insertQuery = """
Insert Into ProtocolStatusCurrent(buildingID, buildingStatus, schoolName) values(%s, %s, %s);
"""
cursor = cnx.cursor()
cursor.execute(insertQuery, (buildingId, status, "UIUC"))
cnx.commit()
return jsonify(True)
def isActiveEmergency():
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
query = """
Select * from ProtocolStatusCurrent Where schoolName = %s %s;
"""
cursor = cnx.cursor()
cursor.execute(query, ("UIUC", ""))
result = cursor.fetchall()
print(len(result))
if len(result) > 0:
return jsonify(True)
return jsonify(False)
def startEmergency():
protocolID = request.args.get('protocolID')
sendEmergencyInfoToTwilioSubs(protocolID)
sendEmergencyPortalToAdmins(protocolID)
host_name = get_file_contents("HostDB");
cnx = mysql.connector.connect(user='******', password='******',
host=host_name,
database='innodb')
query = """
Select Distinct ProtocolSynthesis.buildingID, buildingStatus
From (
Select ProtocolToBuilding.protocolID, ProtocolStatusInitial.buildingID, buildingStatus
From ProtocolToBuilding, ProtocolStatusInitial
Where ProtocolToBuilding.protocolID = %s and ProtocolStatusInitial.buildingID = ProtocolToBuilding.buildingID
) as ProtocolSynthesis Join GeoFeatures
on buildingID = GeoFeatures.idBuilding AND schoolName = %s
order by created_at;
"""
cursor = cnx.cursor()
cursor.execute(query, (protocolID, "UIUC"))
result = cursor.fetchall()
for res in result:
insertQuery = """
Insert Into ProtocolStatusCurrent(buildingID, buildingStatus, schoolName) values(%s, %s, %s)
"""
cursor = cnx.cursor()
cursor.execute(insertQuery, (res[0], res[1], "UIUC"))
cnx.commit()
return redirect("/emergency", code=302)
