def fetch_hospital_data():
hospitals_temp = []
cursor = get_cursor()
cursor.execute("SELECT * FROM Hospital")
rows = cursor.fetchall()
for row in rows:
hospitals_temp.append(list(row))
hospitals.clear()
hospitals.extend(hospitals_temp)
def fetch_ambulance_data():
ambulances_temp = []
cursor = get_cursor()
cursor.execute("SELECT * FROM Ambulance WHERE AVAILABLE = TRUE")
rows = cursor.fetchall()
for row in rows:
ambulances_temp.append(list(row))
ambulances.clear()
ambulances.extend(ambulances_temp)
def get_ambulance_and_time(best_accident, filtered_ambulances):
predicted_time, ambulance = find_best_resource(best_accident, filtered_ambulances, 2, 1)
if ambulance is None:
print("There was no support vehicle available to assign for this accident")
else:
cursor = get_cursor()
cursor.execute("UPDATE Ambulance SET AVAILABLE = FALSE WHERE ID = " + str(ambulance[0]))
cursor.connection.commit()
ambulances.remove(ambulance)
return predicted_time, ambulance
def fetch_accident_data():
cursor = get_cursor()
cursor.execute(
"SELECT * FROM Accident WHERE Time <= NOW() AND Processed IS NULL")
rows = cursor.fetchall()
for row in rows:
accidents.append(list(row))
cursor.execute("UPDATE Accident SET Processed = 0 WHERE ID = " +
str(row[0]))
cursor.connection.commit()
def save_trip(accident, hospital, ambulance, ambulance_to_scene, scene_to_hospital):
# the estimated time it will take the ambulance to complete the trip
avg_scene_time = 15 * 60
predicted_trip_duration = ambulance_to_scene + scene_to_hospital + avg_scene_time
cursor = get_cursor()
trip_insert_query = """INSERT INTO Trip (DEPARTURE_TIME, ACCIDENT_ID, AMBULANCE_ID, HOSPITAL_ID,
EST_SCENE_ARRIVAL_TIME, EST_HOSPITAL_ARRIVAL_TIME)
VALUES
( UNIX_TIMESTAMP(),""" + str(accident[0]) + "," + str(ambulance[0]) + "," \
+ str(hospital[0]) + "," + "UNIX_TIMESTAMP() +" + str(ambulance_to_scene) + "," + \
"UNIX_TIMESTAMP() +" + str(predicted_trip_duration) + ")"""
print(trip_insert_query)
cursor.execute(trip_insert_query)
cursor.connection.commit()
close_connection(cursor)
return "Trip record for accident with ID: " + str(accident[0]) + "successfully added to database"
def count_ambulances():
cursor = get_cursor()
cursor.execute("SELECT COUNT(*) FROM AMBULANCE")
global ambulance_count
ambulance_count = cursor.fetchone()[0]
def finish_processing(best_accident):
cursor = get_cursor()
cursor.execute("UPDATE ACCIDENT SET PROCESSED = 1 WHERE ID = " + str(best_accident[0]))
accidents.remove(best_accident)
cursor.connection.commit()
close_connection(cursor)
import re
from functools import reduce
import nltk
import gensim
from main.DatabaseUtil import get_cursor
# minimum cosine similarity value between two words
threshold = 0.52
nltk.download('stopwords')
nltk.download('punkt')
cursor = get_cursor()
model = gensim.models.KeyedVectors.load_word2vec_format(
'GoogleNews-vectors-negative300-SLIM.bin', binary=True)
# fetch keywords stored in database and save them as a part of different data structures
# @return list of keywords, dictionary of keyword to keyword group, negation dictionary that stores mappings of
# negated keywords to keywords and keywords to negated keywords, set of negative keywords
# TODO: Add major and minor burns
def fetch_keywords_from_db():
keywords_list = []
keywords_dictionary = {}
negation_dictionary = {}
negated_keywords_set = set()
cursor.execute("SELECT * FROM KEYWORD")
rows = cursor.fetchall()
def update_ambulance_availability():
while True:
cursor = get_cursor()
ambulances_en_route = []
cursor.execute("""
SELECT TRIP.AMBULANCE_ID, LONGITUDE, LATITUDE, EST_HOSPITAL_ARRIVAL_TIME
from (
SELECT AMBULANCE_ID AS AMBULANCE_ID, MAX(EST_HOSPITAL_ARRIVAL_TIME) as MAX
FROM TRIP,
HOSPITAL
WHERE DEPARTURE_TIME > UNIX_TIMESTAMP() - 3600 * 5
and HOSPITAL_ID = HOSPITAL.ID
GROUP BY AMBULANCE_ID
having MAX < UNIX_TIMESTAMP()
) as AIM,
TRIP,
HOSPITAL
where TRIP.AMBULANCE_ID = AIM.AMBULANCE_ID
and MAX = EST_HOSPITAL_ARRIVAL_TIME
and HOSPITAL_ID = HOSPITAL.ID
""")
rows = cursor.fetchall()
for row in rows:
hospital_lat = row[1]
hospital_lon = row[2]
row[1], row[2] = find_points_within_radius(hospital_lat, hospital_lon)
pred_time = get_time_between_points(
hospital_lat,
hospital_lon,
row[1],
row[2],
euclidean_dist(hospital_lat, hospital_lon, row[1], row[2]))
transfer_complete_time = datetime.fromtimestamp(row[3] + pred_time)
ambulances_en_route.append((row, transfer_complete_time))
cursor.execute("""UPDATE AMBULANCE, (
SELECT TRIP.AMBULANCE_ID, TRUE, LONGITUDE, LATITUDE
from (
SELECT AMBULANCE_ID AS AMBULANCE_ID, MAX(EST_HOSPITAL_ARRIVAL_TIME) as MAX
FROM TRIP,
HOSPITAL
WHERE DEPARTURE_TIME > UNIX_TIMESTAMP() - 3600 * 5
and HOSPITAL_ID = HOSPITAL.ID
GROUP BY AMBULANCE_ID
having MAX < UNIX_TIMESTAMP()
) as AIM,
TRIP,
HOSPITAL
where TRIP.AMBULANCE_ID = AIM.AMBULANCE_ID
and MAX = EST_HOSPITAL_ARRIVAL_TIME
and HOSPITAL_ID = HOSPITAL.ID
) as DAT
SET AMBULANCE.AVAILABLE = true,
AMBULANCE.Longitude = DAT.LONGITUDE,
AMBULANCE.Latitude = DAT.LATITUDE
where AMBULANCE_ID = ID;""")
for ambulance in ambulances_en_route:
if ambulance[1] < datetime.now():
cursor.execute("UPDATE AMBULANCE SET LONGITUDE = " + ambulance[0][2] + " AND LATITUDE = " +
ambulance[0][1] + " WHERE ID = " + ambulance[0][0])
cursor.connection.commit()
close_connection(cursor)
time.sleep(5.0)