def get_user_data(uid):
query_string = f"""SELECT stdid, stdfname, stdlname, stddata.email, depname, facname, advisorid
FROM stddata, faculty, department
WHERE stddata.depid=department.depid
AND department.facid=faculty.facid
AND stddata.stdid={uid[1:]}"""
res = postgresql_api.get_data(query_string)
#print(res)
res = res[0]
if(len(res)==7):
data = {
"uid": uid,
"stdid": res[0],
"name": res[1] + ' ' + res[2],
"email": res[3],
"department": res[4],
"faculty": res[5],
"advisorid": res[6]
}
else:
data = {
"uid": uid,
"stdid": uid[1:],
"name": "Unregisted User",
"email": "Unknow",
"department": "Unknow",
"faculty": "Unknow",
"advisorid": "Unknow"
}
return data
def exam_tbl(data):
res = {}
tbl = data["tbl"]
int(tbl[0]['key'])
course = f"(examtbl.courseid='{tbl[0]['key']}' AND examtbl.sec={int(tbl[0]['sec'])})"
for i in range(1,len(tbl)):
int(tbl[i]['key']) #Try to error
int(tbl[i]['sec']) #Try to error too
course += f" OR (examtbl.courseid='{tbl[i]['key']}' AND examtbl.sec={int(tbl[i]['sec'])})"
query_string = """
SELECT course.courseid, coursename, sec, date, time, room FROM examtbl, course
WHERE examtbl.courseid=course.courseid
AND ("""+course+") AND mf='f' AND year=2561 AND sem=2"
#print(query_string)
exam = postgresql_api.get_data(query_string)
#print("print",exam)
for i in exam:
res[i[3]+i[4]] = {"key": i[0], #courseid
"coursename": i[1],
"sec": str(i[2]),
"date": dateconverter.dateconverter(i[3]),
"caldate": dateconverter.caldate(i[3]),
"time": dateconverter.timeconverter(i[4]),
"room": i[5]
}
return(sort_by_date(res))
def stdQuery(data):
if(check_token(data)):
stdquery_data = data['query_data']
query_string = """SELECT course.courseid, course.coursename, stdenroll.sec, examtbl.date, examtbl.time, examtbl.room
FROM examtbl, course, stdenroll
WHERE examtbl.courseid=course.courseid
AND stdenroll.courseid=course.courseid
AND stdenroll.sec=examtbl.sec
AND stdenroll.sem=examtbl.sem
AND stdenroll.year=examtbl.year
AND stdenroll.stdid BETWEEN startid AND endid
AND stdenroll.stdid="""+f"{int(data['username'][1:])} "
for i in STD_QUERY_LIST:
short_i = i[i.find('.')+1:]
if stdquery_data[short_i] != '':
query_string += f"AND {i}={type_query(stdquery_data[short_i], short_i)} "
#print(query_string)
exam = postgresql_api.get_data(query_string)
res = {}
for i in exam:
res[i[3]+i[4]] = {"key": i[0], #courseid
"coursename": i[1],
"sec": str(i[2]),
"date": dateconverter.dateconverter(i[3]),
"caldate": dateconverter.caldate(i[3]),
"time": dateconverter.timeconverter(i[4]),
"room": i[5]
}
return(sort_by_date(res))
else:
return {"status": "wrong token"}
def adminQuery(data):
if(check_token(data)):
adminquery_data = data['query_data']
query_string = """SELECT course.courseid, course.coursename, stdenroll.sec, examtbl.date, examtbl.time, examtbl.room
FROM examtbl, course, stddata, stdenroll, faculty, department, lecdata
WHERE examtbl.courseid=course.courseid
AND stddata.advisorid=lecdata.lecid
AND stddata.stdid=stdenroll.stdid
AND stddata.depid=department.depid
AND department.facid=faculty.facid
AND stdenroll.courseid=course.courseid
AND stdenroll.sec=examtbl.sec
AND stdenroll.sem=examtbl.sem
AND stdenroll.year=examtbl.year
AND stddata.stdid BETWEEN startid AND endid
"""
for i in ADMIN_QUERY_LIST:
short_i = i[i.find('.')+1:]
if adminquery_data[short_i] != '':
query_string += f"AND {i}={type_query(adminquery_data[short_i], short_i)} "
#print(query_string)
exam = postgresql_api.get_data(query_string)
res = {}
for i in exam:
res[i[3]+i[4]] = {"key": i[0], #courseid
"coursename": i[1],
"sec": str(i[2]),
"date": dateconverter.dateconverter(i[3]),
"caldate": dateconverter.caldate(i[3]),
"time": dateconverter.timeconverter(i[4]),
"room": i[5]
}
return(sort_by_date(res))
else:
return {"status": "wrong token"}
def query_data(data):
query_string = data["query_string"]#for temporary use only
if(not check_token(data)):
return {"status": "wrong token", "data": []}
res = postgresql_api.get_data(query_string)
return{"status": "ok", "data": str(res)}