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"})
