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