def Sources(source): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans and source != "sources": cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id where source=%s order by destination desc""", (source, )) source = cursor.fetchall() print(source) cursor.close() return jsonify(source) elif ans and source == "sources": cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" ) sources = cursor.fetchall() print(sources) cursor.close() return jsonify(sources) else: return ({"message": "No Sources"})
def Destination(destination): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans and destination != "destinations": cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id where destination=%s order by destination desc""", (destination, )) destination = cursor.fetchall() print(destination) cursor.close() return jsonify(destination) elif ans and destination == "destinations": cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" ) destination = cursor.fetchall() print(destination) cursor.close() return jsonify(destination) else: return ({"message": "No destination"})
def pagination(): print("coming") if request.method == "POST": token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: total_items = [] cursor = mysql.connection.cursor() cursor.execute( # """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" """ select place.id,info.id as stat_id, country,city,population,income from place join info on info.place_id=place.id order by country asc""" ) total_items = cursor.fetchall() # count_pages=math.floor(total_items/request.json["dataPerPage"]) cursor.connection.commit() cursor.close() total_pages = math.ceil( len(total_items) / request.json["dataPerPage"]) prev_page_end = (request.json["activePage"] - 1) * request.json["dataPerPage"] curr_page_end = request.json["activePage"] * request.json[ "dataPerPage"] curr_page_items = total_items[prev_page_end:curr_page_end] print(curr_page_items) return { "total_pages": total_pages, "curr_page_items": curr_page_items } else: return ({"message": "trying pagination"})
def addCity(): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == "POST": if ans: cursor = mysql.connection.cursor() print(request.json["country"], request.json["city"]) cursor.execute( """SELECT * from place where country=%s and city=%s""", (request.json["country"], request.json["city"])) available = cursor.fetchall() cursor.connection.commit() cursor.close() print(available) if (not available): cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO place(id,country,city) values(NULL,%s,%s)""", (request.json["country"], request.json["city"])) cursor.connection.commit() cursor.close() cursor = mysql.connection.cursor() cursor.execute( """SELECT id from place where country=%s and city=%s""", (request.json["country"], request.json["city"])) idx = cursor.fetchall() cursor.connection.commit() cursor.close() print(idx[0]["id"]) cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO info(id,place_id,population,income)values(NULL,%s,%s,%s)""", (int(idx[0]["id"]), request.json["population"], request.json["income"])) cursor.connection.commit() cursor.close() else: cursor = mysql.connection.cursor() cursor.execute( """SELECT id from place where country=%s and city=%s""", (request.json["place"], request.json["city"])) idx = cursor.fetchall() cursor.connection.commit() cursor.close() print(idx) cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO info(id,place_id,population,income)values(NULL,%s,%s,%s)""", (int(idx[0]["id"]), request.json["population"], request.json["income"])) cursor.connection.commit() cursor.close() return ({"message": "checked"})
def addBus(): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == "POST": if ans: cursor = mysql.connection.cursor() print(request.json["source"], request.json["destination"]) cursor.execute( """SELECT * from terminal where source=%s and destination=%s""", (request.json["source"], request.json["destination"])) available = cursor.fetchall() cursor.connection.commit() cursor.close() print(available) if (not available): cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO terminal(id,source,destination) values(NULL,%s,%s)""", (request.json["source"], request.json["destination"])) cursor.connection.commit() cursor.close() cursor = mysql.connection.cursor() cursor.execute( """SELECT id from terminal where source=%s and destination=%s""", (request.json["source"], request.json["destination"])) idx = cursor.fetchall() cursor.connection.commit() cursor.close() print(idx[0]["id"]) cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO bus(id,terminal_id,bus_no,schedule)values(NULL,%s,%s,%s)""", (int(idx[0]["id"]), request.json["number"], request.json["schedule"])) cursor.connection.commit() cursor.close() else: cursor = mysql.connection.cursor() cursor.execute( """SELECT id from terminal where source=%s and destination=%s""", (request.json["source"], request.json["destination"])) idx = cursor.fetchall() cursor.connection.commit() cursor.close() print(idx) cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO bus(id,terminal_id,bus_no,schedule)values(NULL,%s,%s,%s)""", (int(idx[0]["id"]), request.json["number"], request.json["schedule"])) cursor.connection.commit() cursor.close() return ({"message": "checked"})
def deleteTask(idx): if request.method == 'GET': token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""DELETE FROM tasks where id=%s and user_id=%s""", (idx, ans["id"])) cursor.connection.commit() cursor.close() return ({"message": "deleted"})
def getCountries(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""SELECT distinct(name) from countries """) countries = cursor.fetchall() print(countries) cursor.close() return jsonify(countries) else: return ({"message": "No Country"})
def getDestination(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""SELECT distinct(destination) from terminal """) destination = cursor.fetchall() print(destination) cursor.close() return jsonify(destination) else: return ({"message": "No Sources"})
def editTask(idx): if request.method == 'POST': token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: comment = request.json["comment"] cursor = mysql.connection.cursor() cursor.execute( """UPDATE tasks SET name=%s where id=%s and user_id=%s""", (comment, idx, ans["id"])) cursor.connection.commit() cursor.close() return ({"message": "success"})
def updateTaskList(idx): if request.method == 'POST': token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: task = request.json["task"] cursor = mysql.connection.cursor() cursor.execute( """UPDATE tasklist SET name=%s where id=%s and user_id=%s""", ( task, idx, ans["id"]) ) cursor.connection.commit() cursor.close() return({"message": "updated"})
def getTable(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" ) table = cursor.fetchall() print(table) cursor.close() return jsonify(table) else: return ({"message": "No Cities"})
def addTask(idx): if request.method == 'POST': token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: name = request.json["add"] date = request.json["date"] cursor = mysql.connection.cursor() cursor.execute( """INSERT INTO tasks(id,task_id,user_id,name,date)values(NULL,%s,%s,%s,%s)""", (idx, ans["id"], name, date)) cursor.connection.commit() cursor.close() return ({"message": "deleted"})
def addCountry(): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == "POST": if ans: print(request.json["country"]) cursor = mysql.connection.cursor() cursor.execute("""INSERT INTO countries(id,name)values(NULL,%s)""", (request.json["country"], )) cursor.connection.commit() cursor.close() return {"message": "Added country"} else: return ({"message": "No Country"})
def getStatDetails(idx, stat_id): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute(""" SELECT * from info where id=%s and place_id=%s""", (stat_id, idx)) detail = cursor.fetchall() print(detail) cursor.connection.commit() cursor.close() return jsonify(detail) else: return ({"message": "Bus not available"})
def updateBusDetails(idx, bus_id): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == "POST": if ans: cursor = mysql.connection.cursor() print(request.json["bus_no"], request.json["schedule"]) cursor.execute( """ Update bus set bus_no=%s, schedule=%s where id=%s and terminal_id=%s""", (request.json["bus_no"], request.json["schedule"], bus_id, idx)) cursor.connection.commit() cursor.close() return ({"message": "Updated Successfully"}) else: return ({"message": "No Cities"})
def updateCityDetails(idx, stat_id): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == "POST": if ans: cursor = mysql.connection.cursor() print(request.json["population"], request.json["income"]) cursor.execute( """ Update info set population=%s, income=%s where id=%s and place_id=%s""", (request.json["population"], request.json["income"], stat_id, idx)) cursor.connection.commit() cursor.close() return ({"message": "Updated Successfully"}) else: return ({"message": "No Cities"})
def team(idx): token = request.headers.get("Authorization") ans = loggedPerson(token) if request.method == 'GET' and ans: try: cursor = mysql.connection.cursor() cursor.execute("""SELECT * from team where id=%s""", (idx, )) result = cursor.fetchall() cursor.connection.commit() return jsonify(result) except Exception as e: return {"message": str(e)} finally: cursor.close() else: return {"message": "You need to Login"}
def deleteBus(idx, bus_no): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""DELETE FROM bus where id=%s""", (bus_no, )) cursor.connection.commit() cursor.close() cursor = mysql.connection.cursor() cursor.execute("""DELETE FROM terminal where id=%s""", (idx, )) cursor.connection.commit() cursor.close() return ({"message": "Deleted"}) else: return ({"message": "Not authorized"})
def user(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: print(ans["id"]) cursor = mysql.connection.cursor() cursor.execute("""SELECT * FROM registration where id=%s""", (ans["id"], )) user = cursor.fetchall() print(user) cursor.close() if user: return jsonify(user) else: return {"message": "No Data Available"} else: return {"message": "Invalid User"}
def deleteCountry(idx, stat_id): print("here") token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""DELETE FROM info where id=%s""", (stat_id, )) cursor.connection.commit() cursor.close() print("query exceuted") cursor = mysql.connection.cursor() cursor.execute("""DELETE FROM place where id=%s""", (idx, )) cursor.connection.commit() cursor.close() return ({"message": "Deleted"}) else: return ({"message": "Not authorized"})
def oneTasklist(idx): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute("""SELECT * FROM tasklist where id=%s and user_id=%s""", (idx, ans["id"])) results = cursor.fetchall() print(results, "results") if results: items = [] for item in results: items.append(item) return {"items": items} else: return {"message": "No Data Available"} else: return {"message": "Invalid User"}
def allTasklists(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: cursor = mysql.connection.cursor() cursor.execute( """ select tasklist.name,tasklist.id,tasklist.date,count(task_id)as pending from tasklist left join tasks on tasklist.id=tasks.task_id where tasklist.user_id =%s group by tasklist.id;""", (ans["id"], )) results = cursor.fetchall() print(results, "results") if results: items = [] for item in results: items.append(item) return {"items": items} else: return {"message": "No Data Available"} else: return {"message": "Invalid User"}
def getPageCount(): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: total_items = [] cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" ) total_items = cursor.fetchall() cursor.connection.commit() cursor.close() curr_page = 1 per_page = 5 print(len(total_items), "page count") total_pages = math.ceil(len(total_items) / per_page) return jsonify(total_pages) else: return ({"message": "trying pagination"})
def deleteTasklists(idx): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: print(idx,ans["id"]) cursor=mysql.connection.cursor() cursor.execute( """DELETE FROM tasks where task_id=%s """,(idx,) ) cursor.connection.commit() cursor.close() cursor = mysql.connection.cursor() cursor.execute( """DELETE FROM tasklist where id=%s """, ( idx,) ) cursor.connection.commit() cursor.close() return({"message": "deleted"})
def pagination(data): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans: total_items = [] cursor = mysql.connection.cursor() cursor.execute( """ select terminal.id,bus.id as bus_id, source,destination,bus_no,schedule from terminal join bus on bus.terminal_id=terminal.id order by destination desc""" ) total_items = cursor.fetchall() cursor.connection.commit() cursor.close() curr_page = data per_page = 5 total_pages = math.ceil(len(total_items) / per_page) prev_page_end = (curr_page - 1) * per_page curr_page_end = curr_page * per_page curr_page_items = total_items[prev_page_end:curr_page_end] print(curr_page_items) return jsonify(curr_page_items) else: return ({"message": "trying pagination"})
def income(val): token = request.headers.get("Authorization") ans = loggedPerson(token) if ans and val !="All": cursor = mysql.connection.cursor() cursor.execute( """ select place.id,info.id as stat_id, country,city,population,income from place join info on info.place_id=place.id where income <=%s order by country asc""",(int(val),) ) income = cursor.fetchall() cursor.close() return jsonify(income) elif ans and val =="All": cursor = mysql.connection.cursor() cursor.execute( """ select place.id,info.id as stat_id, country,city,population,income from place join info on info.place_id=place.id order by country asc""" ) income = cursor.fetchall() cursor.close() return jsonify(income) else: return({"message":"No income"})