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