def get_report():
if session.get('token_id'):
token_id = session['token_id']
device = 'web'
if 'depdate' in request.form:
dep_date = request.form['depdate']
else:
dep_date = date.today()
#dep_date = request.form.get('depdate') # Fetching the date from the admin
if dep_date != None:
#busesdet_query = "select s.scheduleid, ct.sourcecityname, s.destid, d.destname, s.busid, s.journeydate, s.starttime, s.seatsavailable, s.price from schedule s, destination d, cities ct where s.destid=d.destid and s.sourcecityid=ct.sourcecityid and journeydate = '{}'".format(str(dep_date))
travellers_query = "SELECT s.journeydate, d.destid, SUM(tk.seatsbooked) FROM schedule s, destination d, tickets tk WHERE s.scheduleid = tk.scheduleid AND s.destid=d.destid and s.journeydate = '{}' GROUP BY s.journeydate, d.destid".format(str(dep_date))
# Establishing connection
report_data = connection.connection_manager()
# Extracting buses data
#report_data.execute(busesdet_query)
report_data.execute(
"select s.scheduleid, ct.sourcecityname, s.destid, d.destname, s.busid, s.journeydate, s.starttime, s.seatsavailable, s.price from schedule s, destination d, cities ct where s.destid=d.destid and s.sourcecityid=ct.sourcecityid and journeydate = %s;",
[dep_date])
bus_res = report_data.fetchall()
print(bus_res)
all_bus_list = []
for res in bus_res:
bus_list = []
bus_list.append(str(res[0]))
bus_list.append(str(res[1]))
bus_list.append(str(res[2]))
bus_list.append(str(res[3]))
bus_list.append(str(res[4]))
bus_list.append(str(res[5]))
bus_list.append(str(res[6]))
bus_list.append(str(res[7]))
bus_list.append(str(res[8]))
all_bus_list.append(bus_list)
# Extracting travellers data
report_data.execute(travellers_query)
trav_res = report_data.fetchall()
all_trav_list = []
for res in trav_res:
trav_list = []
trav_list.append(str(res[0]))
trav_list.append(str(res[1]))
trav_list.append(str(res[2]))
print("JourneyDate: " + str(res[0]))
print("Destination ID: " + str(res[1]))
print("No. of travellers: " + str(res[2]))
all_trav_list.append(trav_list)
new_dict = {}
return json.dumps("all_bus_list":all_bus_list,"all_trav_list":all_trav_list,"dep_date":dep_date)
else:
return json.dumps("all_bus_list":all_bus_list,"all_trav_list":all_trav_list,"dep_date":dep_date)
def database_connection():
abc = connection.connection_manager()
sql = """
SELECT * FROM destination;
"""
abc.execute(sql)
print(abc.fetchall())
return render_template('index.html')
def get_schedule():
print("into get bus search")
schd = connection.connection_manager()
req = request.get_json()
sourcecityid = req['source']
destination_id = req['destination']
number_of_args = req['passengers']
journey_date = req['datepicker']
#sourcecityid = request.form['source']
#destination_id = request.form['destination']
#number_of_args = request.form['passengers']
#journey_date = request.form['datepicker']
# session['number_of_travllers'] = number_of_args
# session['journey_date'] = journey_date
print(sourcecityid, destination_id, number_of_args, journey_date)
bus_list = []
if 'destination' in request.get_json(
) and 'source' in request.get_json() and 'datepicker' in request.get_json(
) and 'passengers' in request.get_json():
schd.execute(
"SELECT s.sourcecityid, s.destid, d.destname, d.destprov, s.journeydate, s.starttime, s.seatsavailable, s.busid, s.scheduleid,s.price FROM schedule s JOIN cities cl ON s.sourcecityid = cl.sourcecityid JOIN destination d ON d.destid = s.destid WHERE s.sourcecityid = %s AND s.destid = %s AND journeydate = %s AND seatsavailable >= %s AND active_status = 'True';",
[sourcecityid, destination_id, journey_date, number_of_args])
results = schd.fetchall()
print(results)
bus_list = []
for res in results:
bus_dict = {}
source_city_name = res[0]
destination_name = res[2]
destprov = res[3]
journeydate = res[4]
starttime = res[5]
seatsavailable = res[6]
busid = res[7]
bus_dict['source_city_name'] = source_city_name
bus_dict['destination_name'] = destination_name
bus_dict['destprov'] = destprov
bus_dict['journeydate'] = str(journeydate)
bus_dict['starttime'] = starttime
bus_dict['seatsavailable'] = seatsavailable
bus_dict['busid'] = busid
bus_dict['scheduleid'] = res[8]
bus_dict['price'] = res[9]
print("Source City: " + source_city_name)
print("Destination Name: " + destination_name)
print("Destination Location: " + destprov)
print("Bus start time: " + str(starttime))
print("Seats Available: " + str(seatsavailable))
print("Bus id: " + str(busid))
bus_list.append(bus_dict)
else:
print("Error")
return json.dumps({"buslists": bus_list})
def check_destination():
if 'id' in request.args:
id = request.args['destonation_id']
abc = connection.connection_manager()
sql = """
SELECT * FROM destinations_lookup WHERE cityid = ?
"""
abc.execute(
"SELECT cityid,cityname FROM cities_lookup WHERE cityid = %s",
[id])
results = abc.fetchall()
for res in results:
cityname = res[1]
cityid = res[0]
else:
print("Error")
return render_template('index.html')
def getallcities():
print(session.get('token_id'))
if session.get('token_id'):
user = session['token_id']
else:
user = ''
destination_type = request.args['type']
device = 'web'
if 'device' in request.args:
device = request.args['device']
schd = connection.connection_manager()
sql = """SELECT * FROM cities WHERE cityid = ?"""
schd.execute("SELECT sourcecityid,sourcecityname FROM cities")
results = schd.fetchall()
source_cities = []
for res in results:
source_dict = {'cityname': '', 'cityid': ''}
source_dict['cityname'] = res[1]
source_dict['cityid'] = res[0]
source_cities.append(source_dict)
print(source_cities)
schd.execute("SELECT destid,destname FROM destination WHERE desttype = %s",
[destination_type])
results = schd.fetchall()
destination_list = []
for res in results:
destination_dict = {}
destination_dict['destname'] = res[1]
destination_dict['destid'] = res[0]
destination_list.append(destination_dict)
print(destination_list)
if device != 'mobile':
return render_template('home.html',
source_list=source_cities,
destination_list=destination_list,
user=user)
else:
return json.dumps({
"source_list": source_cities,
"destination_list": destination_list
})
def get_report():
if session.get('token_id'):
token_id = session['token_id']
device = 'web'
if 'depdate' in request.form:
dep_date = request.form['depdate']
else:
dep_date = date.today()
if dep_date != None:
#busesdet_query = "select s.scheduleid, ct.sourcecityname, s.destid, d.destname, s.busid, s.journeydate, s.starttime, s.seatsavailable, s.price from schedule s, destination d, cities ct where s.destid=d.destid and s.sourcecityid=ct.sourcecityid and journeydate = '{}'".format(str(dep_date))
# UPDATE
travellers_query = "SELECT s.journeydate, d.destname, d.destprov, SUM(tk.seatsbooked) FROM schedule s, destination d, tickets tk WHERE s.scheduleid = tk.scheduleid AND s.destid=d.destid and s.journeydate = '{}' GROUP BY s.journeydate, d.destname, d.destprov".format(
str(dep_date))
# UPDATE
# Establishing connection
report_data = connection.connection_manager()
# Extracting buses data
report_data.execute(
"select s.scheduleid, ct.sourcecityname, s.destid, d.destname, s.busid, s.journeydate, s.starttime, s.seatsavailable, s.price from schedule s, destination d, cities ct where s.destid=d.destid and s.sourcecityid=ct.sourcecityid and journeydate = %s;",
[dep_date])
bus_res = report_data.fetchall()
print(bus_res)
all_bus_list = []
for res in bus_res:
bus_list = []
bus_list.append(str(res[0]))
bus_list.append(str(res[1]))
bus_list.append(str(res[2]))
bus_list.append(str(res[3]))
bus_list.append(str(res[4]))
bus_list.append(str(res[5]))
bus_list.append(str(res[6]))
bus_list.append(str(res[7]))
bus_list.append(str(res[8]))
all_bus_list.append(bus_list)
# Extracting travellers data
report_data.execute(travellers_query)
trav_res = report_data.fetchall()
all_trav_list = []
# UPDATE
for res in trav_res:
trav_list = []
trav_list.append(str(res[0]))
trav_list.append(str(res[1]))
trav_list.append(str(res[2]))
trav_list.append(str(res[3]))
all_trav_list.append(trav_list)
# UPDATE
return render_template('report.html',
all_bus_list=all_bus_list,
all_trav_list=all_trav_list,
dep_date=dep_date)
else:
return render_template('report.html')