def add_expedition():
firm_id = session.get("firm_id")
if firm_id != None:
if request.method == "GET":
return views.add_expedition()
else:
from_ = request.form["from"]
from_ter = request.form["from_ter"]
to = request.form["to"]
to_ter = request.form["to_ter"]
dep_time = request.form["dep_time"]
arr_time = request.form["arr_time"]
date = request.form["date"]
price = request.form["price"]
plane = request.form["selected_plane"]
vehicle = vehicle_db.get_vehicle(plane)
total_cap = vehicle.capacity
driver_id = request.form["driver"]
if "document" in request.files:
document = request.files["document"]
expedition_db.add_expedition_with_document(Expedition(from_, from_ter, to, to_ter, dep_time, arr_time, date, price, plane, driver_id, firm_id, total_cap, 0, document.read()))
else:
expedition_db.add_expedition(Expedition(from_, from_ter, to, to_ter, dep_time, arr_time, date, price, plane, driver_id, firm_id, total_cap, 0, None ))
return redirect(url_for('firm_page', id=firm_id))
else:
return unAuth403()
def edit_expedition(expedition_id):
firm_id = session.get("firm_id")
user_id = session.get('user_id')
user = userop.get_user(user_id)
temp_firm_id = expedition_db.get_expedition(expedition_id).firm_id
if (firm_id != None and firm_id is temp_firm_id ) or user[-1]:
if request.method == "GET":
return views.edit_expedition(expedition_id)
else:
from_ = request.form["from"]
from_ter = request.form["from_ter"]
to = request.form["to"]
to_ter = request.form["to_ter"]
dep_time = request.form["dep_time"]
arr_time = request.form["arr_time"]
date = request.form["date"]
price = request.form["price"]
plane = request.form["selected_plane"]
vehicle = vehicle_db.get_vehicle(plane)
total_cap = vehicle.capacity
driver_id = request.form["driver"]
if "document" in request.files:
document = request.files["document"]
expedition_db.update_expedition_with_document(expedition_id ,Expedition(from_, from_ter, to, to_ter, dep_time, arr_time, date, price, plane, driver_id, firm_id, total_cap, 0, document.read()))
else:
expedition_db.update_expedition(expedition_id, Expedition(from_, from_ter, to, to_ter, dep_time, arr_time, date, price, plane, driver_id, firm_id, total_cap, 0, None ))
return redirect(url_for('firm_page', id=firm_id))
else:
return unAuth403()
def get_all_valid_expeditions(self):
expeditions = []
try:
connection = dbapi2.connect(self.url)
cursor = connection.cursor()
cursor.execute("SELECT * FROM expeditions where current_cap < total_cap;")
for expedition in cursor:
_expedition = Expedition(expedition[1], expedition[2], expedition[3], expedition[4], expedition[5],
expedition[6], expedition[7], expedition[8], expedition[9], expedition[12],
expedition[13], expedition[11], expedition[10], expedition[14])
_expedition.expedition_id =expedition[0]
if dayCompare(_expedition.date):
expeditions.append((expedition[0], _expedition))
cursor.close()
except (Exception, dbapi2.DatabaseError) as error:
print(error)
finally:
if connection is not None:
connection.close()
return expeditions
def get_filtered_expeditions(self, to_city, to_ter, from_city, from_ter, firm_id, date, max_price):
expeditions = []
statement = " SELECT * FROM expeditions WHERE TRUE "
if to_city is not None:
statement += " and to_city = '" + to_city + "' "
if to_ter is not None:
statement += "and to_ter = " + str(to_ter) + " "
if from_city is not None:
statement += " and from_city = '" + from_city + "' "
if from_ter is not None:
statement += " and from_ter = " + str(from_ter) + " "
if firm_id is not None:
statement += "and firm_id = " + str(firm_id) + " "
if date is not "":
statement += "and date like '%" + date + "%' "
if max_price is not "":
statement += "and price <= " + str(max_price)
statement += "and current_cap < total_cap"
try:
connection = dbapi2.connect(self.url)
cursor = connection.cursor()
cursor.execute(statement)
for expedition in cursor:
_expedition = Expedition(expedition[1], expedition[2], expedition[3], expedition[4], expedition[5],
expedition[6], expedition[7], expedition[8], expedition[9], expedition[12],
expedition[13], expedition[11], expedition[10], expedition[14])
_expedition.expedition_id =expedition[0]
if dayCompare(_expedition.date):
expeditions.append((expedition[0], _expedition))
cursor.close()
except (Exception, dbapi2.DatabaseError) as error:
print(error)
finally:
if connection is not None:
connection.close()
return expeditions
def get_expedition(self, expedition_id):
_expedition = None
try:
connection = dbapi2.connect(self.url)
cursor = connection.cursor()
cursor.execute("SELECT * FROM expeditions WHERE expedition_id = %s", (expedition_id,))
expedition = cursor.fetchone()
if expedition is not None:
_expedition = Expedition(expedition[1], expedition[2], expedition[3], expedition[4], expedition[5],
expedition[6], expedition[7], expedition[8], expedition[9], expedition[12],
expedition[13], expedition[11], expedition[10], expedition[14])
connection.commit()
cursor.close()
except (Exception, dbapi2.DatabaseError) as error:
print(error)
finally:
if connection is not None:
connection.close()
return _expedition
def get_firms_expedition(self, firm_id):
expeditions = []
try:
connection = dbapi2.connect(self.url)
cursor = connection.cursor()
cursor.execute("SELECT * FROM expeditions WHERE firm_id = %s;", (firm_id,))
for expedition in cursor:
_expedition = Expedition(expedition[1], expedition[2], expedition[3], expedition[4], expedition[5],
expedition[6], expedition[7], expedition[8], expedition[9], expedition[12],
expedition[13], expedition[11], expedition[10], expedition[14])
expeditions.append((expedition[0], _expedition))
connection.commit()
cursor.close()
except (Exception, dbapi2.DatabaseError) as error:
print(error)
finally:
if connection is not None:
connection.close()
return expeditions
def search(self, text):
expeditions = []
to_search = "%" + text + "%"
try:
connection = dbapi2.connect(self.url)
cursor = connection.cursor()
if isInt(text):
cursor.execute("""select * from expeditions where expedition_id in (
select expedition_id
from expeditions, city as to_city, firms, city as from_city, terminal as to_ter, terminal as from_ter
where (firms.firm_id = expeditions.firm_id and expeditions.to_city = to_city.code and expeditions.from_city = from_city.code and expeditions.to_ter = to_ter.terminal_id and expeditions.from_ter = from_ter.terminal_id )
and
( (price = %s) or (LOWER(to_city.city_name) like LOWER(%s)) or ( LOWER(firms.name) like LOWER(%s) ) or ( LOWER(from_city.city_name) like LOWER(%s) ) or (LOWER(date) like LOWER(%s)) or (LOWER(dep_time) like LOWER(%s)) or (LOWER(arr_time) like LOWER(%s)) or (LOWER(from_ter.terminal_name) like LOWER(%s)) or (LOWER(to_ter.terminal_name) like LOWER(%s))))""", (int(text) ,to_search, to_search, to_search, to_search, to_search, to_search,to_search,to_search, ))
else:
cursor.execute("""select * from expeditions where expedition_id in (
select expedition_id
from expeditions, city as to_city, firms, city as from_city, terminal as to_ter, terminal as from_ter
where (firms.firm_id = expeditions.firm_id and expeditions.to_city = to_city.code and expeditions.from_city = from_city.code and expeditions.to_ter = to_ter.terminal_id and expeditions.from_ter = from_ter.terminal_id )
and
( (LOWER(to_city.city_name) like LOWER(%s)) or ( LOWER(firms.name) like LOWER(%s) ) or ( LOWER(from_city.city_name) like LOWER(%s) ) or (LOWER(date) like LOWER(%s)) or (LOWER(dep_time) like LOWER(%s)) or (LOWER(arr_time) like LOWER(%s)) or (LOWER(from_ter.terminal_name) like LOWER(%s)) or (LOWER(to_ter.terminal_name) like LOWER(%s))))""",
( to_search, to_search, to_search, to_search, to_search, to_search, to_search,
to_search,))
for expedition in cursor:
_expedition = Expedition(expedition[1], expedition[2], expedition[3], expedition[4], expedition[5],
expedition[6], expedition[7], expedition[8], expedition[9], expedition[12],
expedition[13], expedition[11], expedition[10], expedition[14])
if dayCompare(_expedition.date):
expeditions.append((expedition[0], _expedition))
connection.commit()
cursor.close()
except (Exception, dbapi2.DatabaseError) as error:
print(error)
finally:
if connection is not None:
connection.close()
return expeditions