def get_requests(previous_day):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
requests = []
if previous_day:
yesterday = str(date.today() - timedelta(days=1))
query = "SELECT * FROM public.\"Richieste\" WHERE ora_arrivo::date = '{0}'".format(yesterday)
else:
query = 'SELECT * FROM public."Richieste"'
cursor.execute(query)
for row in cursor:
requests.append(
Request(
id_request=row[0],
departure=row[1],
lat_dep=row[2],
lon_dep=row[3],
arrival=row[4],
lat_arr=row[5],
lon_arr=row[6],
time_dep=row[7],
time_arr=row[8],
user=row[9]
)
)
connection.close()
return requests
def get_route(lat_origine, lon_origine, lat_destinazione, lon_destinazione):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM public.\"Itinerari\" WHERE lat_origine={0} and lon_origine={1} "
"and lat_destinazione={2} and lon_destinazione={3}".format(lat_origine, lon_origine,
lat_destinazione, lon_destinazione))
route = cursor.fetchone()
if route is not None:
result = {
"id_route": route[0],
"departure": route[1],
"lat_dep": route[2],
"lon_dep": route[3],
"arrival": route[4],
"lat_arr": route[5],
"lon_arr": route[6],
"distance": route[7],
"duration": route[8]
}
else:
result = None
connection.close()
return result
def insert_coordinates(license_plate, travel_date, lat, lon):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Posizioni_Mezzi\"(mezzo, data_viaggio, lat, lon) "
"VALUES ('{0}', '{1}', {2}, {3})".format(license_plate, travel_date, lat, lon))
connection.commit()
connection.close()
def sign_up(name, surname, email, username, user_password):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Utenti\"(username, email, nome, cognome, password) VALUES ("
"'{0}','{1}','{2}','{3}','{4}')".format(username, email, name, surname, user_password))
connection.commit()
connection.close()
def insert_movement(id_fitness, id_mezzo, lat, lon, luogo=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Spostamenti\"(id_fitness, id_mezzo, luogo, lat, lon) "
"VALUES ('{0}', {1}, '{2}', {3}, {4})".format(id_fitness, id_mezzo, luogo, lat, lon))
connection.commit()
connection.close()
def insert_route(lat_ori, lon_ori, lat_dest, lon_dest, distanza, durata, origine='', destinazione=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Itinerari\"(origine, lat_origine, lon_origine, destinazione,"
" lat_destinazione, lon_destinazione, distanza, durata) VALUES ('{0}',{1},{2},'{3}',{4},{5},{6},{7})"
"".format(origine, lat_ori, lon_ori, destinazione, lat_dest, lon_dest, distanza, durata))
connection.commit()
connection.close()
def get_bus_id(license_plate):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT id FROM public.\"Mezzi\" WHERE targa = '{0}'".format(license_plate))
row = cursor.fetchone()
if row is not None:
id_bus = row[0]
else:
id_bus = None
connection.close()
return id_bus
def sign_in(email, user_password):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
username = None
cursor.execute("SELECT username FROM public.\"Utenti\" WHERE email = '{0}' and password = '******'"
"".format(email, user_password))
row = cursor.fetchone()
if row is not None:
username = row[0]
connection.close()
return username
def get_bus_stops():
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
result = []
cursor.execute("SELECT luogo, lat, lon FROM public.\"Fermate\"")
bus_stops = cursor.fetchall()
if bus_stops is not None:
for row in bus_stops:
result.append({"place": row[0], "lat": row[1], "lon": row[2]})
connection.close()
return result
def insert_request(lat_departure, lon_departure, lat_arrival, lon_arrival, time_departure, time_arrival,
request_user, departure='', arrival=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Richieste\"(origine, lat_origine, lon_origine, destinazione, "
"lat_destinazione, lon_destinazione, ora_partenza, ora_arrivo, utente) "
"VALUES('{0}', {1}, {2}, '{3}', {4}, {5}, '{6}', '{7}', '{8}') RETURNING id".format(departure,
lat_departure, lon_departure, arrival, lat_arrival, lon_arrival, time_departure, time_arrival,
request_user))
request_inserted_id = cursor.fetchone()[0]
connection.commit()
connection.close()
return request_inserted_id
def check_user(username, email):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
result = {"username": None, "email": None}
cursor.execute("SELECT username FROM public.\"Utenti\" WHERE username = '******'".format(username))
row = cursor.fetchone()
if row is not None:
result["username"] = row[0]
cursor.execute("SELECT email FROM public.\"Utenti\" WHERE email = '{0}'".format(email))
row = cursor.fetchone()
if row is not None:
result["email"] = row[0]
connection.close()
return result
def get_coordiantes(license_plate, travel_date):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
date_from = "{0} 00:00:00".format(travel_date)
date_to = "{0} 23:59:59".format(travel_date)
cursor.execute("SELECT lat, lon FROM public.\"Posizioni_Mezzi\" WHERE mezzo = '{0}' AND "
"(data_viaggio, data_viaggio) OVERLAPS ('{1}', '{2}')".format(license_plate, date_from, date_to))
data = cursor.fetchall()
coordinates = []
for row in data:
coordinates.append([row[0], row[1]])
connection.commit()
connection.close()
return coordinates
def get_buses():
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
buses = []
cursor.execute('SELECT * FROM public."Mezzi"')
for row in cursor:
buses.append(
Bus(
id_bus=row[0],
license_plate=row[1],
seats=row[2],
place=row[3],
lat=row[4],
lon=row[5]
)
)
connection.close()
return buses
def get_movements(id_fitness, id_mezzo):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM public.\"Spostamenti\" WHERE id_fitness='{0}' and id_mezzo='{1}'"
"ORDER BY id".format(id_fitness, id_mezzo))
spostamenti = cursor.fetchall()
result = []
for spostamento in spostamenti:
result.append({
"id_movement": spostamento[0],
"id_fitness": spostamento[1],
"id_bus": spostamento[2],
"place": spostamento[3],
"lat": spostamento[4],
"lon": spostamento[5]
})
connection.close()
return result