def ShiTi():
JD = request.args.get('JD')
FX = request.args.get('FX')
db1 = pymysql.connect(host="localhost",
user="******",
password="******",
db="ribao1",
cursorclass=pymysql.cursors.DictCursor,
charset="utf8")
cursor1 = db1.cursor()
cursor1.execute("select pid from shijuan where fx=%s and jd=%s", (FX, JD))
res = cursor1.fetchall()
str1 = ''
pid = []
for item in range(len(res)):
str1 += res[item]['pid'] + ','
pid.append(res[item]['pid'])
str1 = str1[0:-1]
pid = str1.split(",")
# cursor.execute("select * from shiti where fangxiang=%s and jieduan=%s",(FX,JD))
cursor.execute(
"select * from shiti where id=%s or id=%s or id=%s or id=%s or id=%s or id=%s or id=%s",
(pid[0], pid[1], pid[2], pid[3], pid[4], pid[5], pid[6]))
result = cursor.fetchall()
db.commit()
# print(result)
return json.dumps(result)
def seleclasses():
cursor.execute("select rinfo,rname from role")
result=cursor.fetchall()
for i in range(len(result)):
result[i]['value']=result[i].pop('rinfo')
result[i]['label'] = result[i].pop('rname')
return json.dumps(result)
def searchAllLogs():
KC = request.args.get("KC")
banji = request.args.get("banji")
times = request.args.get("date")
clickpageNum = request.args.get("pagesNow")
clickpageNum = int(clickpageNum)
# print(clickpageNum)
if (times == 'NaN-NaN-NaN NaN:NaN:NaN'):
times = ''
if (times == '1970-1-1 8:0:0'):
times = ''
seleCon = ""
if not times == '':
# times = times[0:10]
times = times.rsplit(" ")[0]
# print(times)
timecon = "where 1=1 AND date_format(logs.time,'%Y-%m-%d')='" + times + "'" if times else ""
# print(timecon,type(timecon))
if KC:
seleCon = "where logs.phone in (select phone from students where classid in (select id from classes where cid in ('" + KC + "')))"
if banji and not KC:
seleCon = "where logs.phone in (select phone from students where classid= " + banji + ")"
if banji and KC:
seleCon = "where logs.phone in (select phone from students where classid in (select id from classes where cid=%s and id=%s))" % (
KC, banji)
sql = "select logs.*,students.name as sname,classes.name as cname from logs left join students on logs.phone=students.phone left join classes on students.classid=classes.id " + seleCon + " " + timecon + " ORDER BY id LIMIT " + str(
(clickpageNum - 1) * 3) + ",3"
cursor.execute(sql)
result = cursor.fetchall()
return json.dumps(result, default=str)
def deljuese():
rid = request.args.get('rid')
cursor.execute("delete from role where rid=%s", rid)
db.commit()
cursor.execute("select * from role")
result = cursor.fetchall()
return json.dumps(result)
def deluser():
id=request.args.get('id')
cursor.execute("delete from user where id=%s",id)
db.commit()
cursor.execute("select * from user")
result = cursor.fetchall()
return json.dumps(result)
def seleJD():
tel = session.get('tel')
db1 = pymysql.connect(host="localhost",
user="******",
password="******",
db="ribaoxitong",
cursorclass=pymysql.cursors.DictCursor,
charset="utf8")
cursor1 = db1.cursor()
cursor1.execute("select * from students where phone=%s", tel)
result1 = cursor1.fetchone()
classid = result1['classid']
# print(classid)
session['classid'] = result1['classid']
db2 = pymysql.connect(host="localhost",
user="******",
password="******",
db="ribao1",
cursorclass=pymysql.cursors.DictCursor,
charset="utf8")
cursor2 = db2.cursor()
cursor2.execute("select cid from classes where id=%s", classid)
result2 = cursor2.fetchone()
cid = result2['cid']
# cid = 3
# session['classid'] = 3
session['classid'] = cid
cursor.execute("select id,step from category_info where cid=%s", cid)
result = cursor.fetchall()
for i in range(len(result)):
result[i]['value'] = result[i].pop('id')
result[i]['label'] = result[i].pop('step')
return json.dumps(result)
def seleclasses():
cursor.execute("select id,name from classes")
result = cursor.fetchall()
db.commit()
for i in range(len(result)):
result[i]['value'] = str(result[i].pop('id'))
result[i]['label'] = result[i].pop('name')
return json.dumps(result)
def selekecheng():
id = request.args.get('cid')
# cursor.execute("select * from category,category_info where category.cid=category_info.cid")
# result = cursor.fetchall()
cursor.execute("select * from category_info where cid=%s", id)
result = cursor.fetchall()
# print(result,type(result))
return json.dumps(result)
def seleKCS():
cursor.execute("select cid,cname from category")
result = cursor.fetchall()
db.commit()
for i in range(len(result)):
# print(result[i])
result[i]['value'] = str(result[i].pop('cid'))
result[i]['label'] = result[i].pop('cname')
return json.dumps(result)
def seleJD():
cid=request.args.get("cid")
cid=int(cid)
cursor.execute("select id,step from category_info where cid=%s",cid)
result=cursor.fetchall()
for i in range(len(result)):
result[i]['value']=result[i].pop('id')
result[i]['label'] = result[i].pop('step')
return json.dumps(result)
def findPro():
FX=request.args.get("FX")
JD=request.args.get("JD")
TX=request.args.get("TX")
cursor.execute("select id,tigan,xuanxiang,daan,score from shiti where types=%s and fangxiang=%s and jieduan=%s",(TX,FX,JD))
result=cursor.fetchall()
for item in range(len(result)):
if len(result[item]['daan'])>20:
result[item]['daan']=result[item]['daan'][:20]
return json.dumps(result)
def findDXid():
JD = request.args.get('JD')
FX = request.args.get('FX')
types = 1
# print(JD,FX,types)
cursor.execute(
"select id from shiti where fangxiang=%s and jieduan=%s and types=%s",
(FX, JD, types))
result = cursor.fetchall()
# print(result)
return json.dumps(result)
def seleClasses():
cid = request.args.get('cid')
cursor.execute("select * from teacher where cid=%s", cid)
result1 = cursor.fetchall()
db.commit()
for i in range(len(result1)):
id = result1[i]['classid']
cursor.execute("select name,id from classes where id=%s", id)
result2 = cursor.fetchone()
result1[i]['banjiID'] = result2['id']
result1[i]['classid'] = result2['name']
# print(result1)
return json.dumps(result1)
def deleTea():
phone = request.args.get("phone")
cursor.execute("delete from teacher where phone=%s", phone)
cursor.execute("delete from user where tel=%s", phone)
db.commit()
cid = request.args.get('cid')
cursor.execute("select * from teacher where cid=%s", cid)
result1 = cursor.fetchall()
for i in range(len(result1)):
id = result1[i]['classid']
cursor.execute("select name,id from classes where id=%s", id)
result2 = cursor.fetchone()
result1[i]['banjiID'] = result2['id']
result1[i]['classid'] = result2['name']
db.commit()
return json.dumps(result1)
def seleuser():
# cursor.execute("select * from user,role where user.rid=role.rid")
# result = cursor.fetchall()
# lis=[]
# root=[]
# for item in result:
# lis.append(item['rid'])
# for rid in lis:
# cursor.execute("select rname from role where rinfo=%s",rid)
# roots=cursor.fetchone()
# root.append(roots)
# for index in range(len(lis)):
# result[index]['rname']=root[index]['rname']
# return json.dumps(result)
cursor.execute("select * from user,role where user.rid=role.rid")
result=cursor.fetchall()
results=json.dumps(result)
return results
def selectroot():
cursor.execute("select * from role")
result = cursor.fetchall()
# print(result)
return json.dumps(result)
def seleClasses():
cid=request.args.get('cid')
cursor.execute("select * from classes where cid=%s",cid)
result=cursor.fetchall()
return json.dumps(result,default=str)
def seleKC():
cursor.execute("select * from category")
result=cursor.fetchall()
return json.dumps(result)
def selejuese():
cursor.execute("select * from role")
result = cursor.fetchall()
return json.dumps(result)
