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 jueseadd():
juese = request.args.get('name')
roots = request.args.get('roots')
cursor.execute("insert into role (rname,rinfo) values(%s,%s)",
(juese, roots))
db.commit()
return 'ok'
def upload():
# file=request.files['file']
# file.save('1.xlsx')
# book=xlrd.open_workbook("1.xlsx")
# sheet=book.sheet_by_index(0)
file_dir = os.path.join(basedir, app.config['UPLOAD_FOLDER'])
if not os.path.exists(file_dir):
os.makedirs(file_dir)
f = request.files['file'] # 从表单的file字段获取文件，file为该表单的name值
if f and allowed_file(f.filename): # 判断是否是允许上传的文件类型
# fname = secure_filename(f.filename)
# ext = fname.rsplit('.', 1)[1] # 获取文件后缀
# unix_time = int(time.time())
# new_filename = str(unix_time) + '.' + ext # 修改了上传的文件名
f.save(os.path.join(file_dir, 'kecheng.xlsx')) # 保存文件到upload目录
# token = base64.b64encode(new_filename)
book = xlrd.open_workbook("url/upload/" + 'kecheng.xlsx')
sheet = book.sheet_by_index(0)
arr = []
for item in range(1, sheet.nrows):
con = sheet.row_values(item)
cursor.execute("insert into category (cname) values (%s)", con[0])
cid = db.insert_id()
step = con[1].split("\n")
part = con[2].split("\n")
for index in range(len(step)):
arr.append((step[index], part[index], cid))
cursor.executemany(
"insert into category_info (step,part,cid) values (%s,%s,%s)",
(arr))
db.commit()
return 'ok'
else:
return jsonify({"errno": 1001, "errmsg": "上传失败"})
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 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 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 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 findsame():
phone=request.args.get('phone')
cursor.execute('select * from user where tel=%s',phone)
result=cursor.fetchone()
if(result):
return 'error'
else:
return 'ok'
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 classname():
phone = session.get("tel")
cursor.execute(
"SELECT name FROM `classes` WHERE id in (SELECT classid FROM students WHERE phone=%s)",
phone)
res = cursor.fetchone()
# print(res['name'])
return json.dumps(res['name'])
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 editjuese():
rid = request.args.get('rid')
name = request.args.get('name')
root = request.args.get('roots')
cursor.execute("update role set rname=%s,rinfo=%s where rid=%s",
(name, root, rid))
db.commit()
print(rid, root, name)
return "ok"
def insertone():
name = request.args.get('name')
start = request.args.get('start')
end = request.args.get('ends')
cid = request.args.get('cid')
name=name.upper()
cursor.execute("insert into classes (name,start,end,cid) values (%s,%s,%s,%s)",(name,start,end,cid))
db.commit()
return 'ok'
def makeShijuan():
FX=request.args.get("FX")
JD=request.args.get("JD")
TX=request.args.get("TX")
Pid=request.args.get("pid")
cursor.execute("insert into shijuan (fx,jd,tx,pid) values (%s,%s,%s,%s)",(FX,JD,TX,Pid))
db.commit()
print(FX,JD,TX,Pid)
return 'ok'
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 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 uppShijuan():
FX=request.args.get("FX")
JD=request.args.get("JD")
TX=request.args.get("TX")
Pid=request.args.get("pid")
print(FX,JD,TX,Pid)
cursor.execute("update shijuan set pid=%s where fx=%s and jd=%s and tx=%s",(Pid,FX,JD,TX))
db.commit()
return 'ok'
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 addJianda():
FX=request.args.get("FX")
JD=request.args.get("JD")
TiXing=request.args.get("TiXing")
TiGan=request.args.get("TiGan")
daan=request.args.get("daan")
score=request.args.get("score")
cursor.execute("insert into shiti (tigan,daan,types,fangxiang,jieduan,score) values(%s,%s,%s,%s,%s,%s)",(TiGan,daan,TiXing,FX,JD,score))
db.commit()
return 'ok'
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 chuli(con):
arr = []
cursor.execute("insert into category (cname) values (%s)", con[0])
cid = db.insert_id()
step = con[1].split("\n")
part = con[2].split("\n")
for index in range(len(step)):
arr.append((step[index], part[index], cid))
cursor.executemany(
"insert into category_info (step,part,cid) values (%s,%s,%s)", (arr))
db.commit()
def updateOne():
id=request.args.get('id')
name = request.args.get('name')
start = request.args.get('start')
end = request.args.get('ends')
cid = request.args.get('cid')
name = name.upper()
# print(id,name,start,end,cid)
cursor.execute("update classes set name=%s,start=%s,end=%s,cid=%s where id=%s",
(name, start, end, cid, id))
db.commit()
return 'ok'
def tijiaoribao():
ZYM = request.form['ZYM']
xinde = request.form['XinDe']
yijian = request.form['YiJian']
qita = request.form['QiTa']
con = request.form['con']
phone = '20181116996'
print(type(con), con)
cursor.execute(
"insert into logs (phone,zym,workspace,yijian,xinde,qita) values (%s,%s,%s,%s,%s,%s)",
(phone, ZYM, con, yijian, xinde, qita))
db.commit()
return 'ok'
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 insertOne():
name=request.args.get("name")
tel=request.args.get("tel")
pswd=request.args.get("pass")
md5 = hashlib.md5()
md5.update(pswd.encode('utf8'))
pswd = md5.hexdigest()
roots=request.args.get("YH")
cursor.execute('select rid from role where rinfo=%s', roots)
result = cursor.fetchone()
roots = result['rid']
cursor.execute("insert into user (name,tel,pass,rid) values(%s,%s,%s,%s)", (name, tel, pswd, roots))
db.commit()
return 'ok'
def insertOne():
name=request.args.get('name')
tel=request.args.get('tel')
school=request.args.get('school')
sex=request.args.get('sex')
banji=request.args.get('banji')
rid='9'
md5 = hashlib.md5()
md5.update(b'123456')
pswd = md5.hexdigest()
cursor.execute("insert into user (name,tel,pass,rid) values(%s,%s,%s,%s)", (name,tel,pswd,rid))
cursor.execute("insert into students (name,phone,classid,sex,school) values (%s,%s,%s,%s,%s)",(name,tel,banji,sex,school))
db.commit()
return 'ok'
def updausers():
uid = request.form["uid"]
username = request.form['uname']
usertel = request.form['utel']
userpass = request.form['upass']
md5 = hashlib.md5()
md5.update(userpass.encode('utf8'))
userpass = md5.hexdigest()
roots = request.form['roots']
cursor.execute('select rid from role where rinfo=%s', roots)
result = cursor.fetchone()
roots = result['rid']
cursor.execute("update user set name=%s,tel=%s,pass=%s,rid=%s where id=%s",(username,usertel,userpass,roots,uid))
db.commit()
return 'ok'
def insertOne():
name = request.args.get('name')
tel = request.args.get('tel')
banji = request.args.get('banji')
FX = request.args.get('FX')
name = name.upper()
rid = '10'
md5 = hashlib.md5()
md5.update(b'123456')
pswd = md5.hexdigest()
cursor.execute("insert into user (name,tel,pass,rid) values(%s,%s,%s,%s)",
(name, tel, pswd, rid))
cursor.execute(
"insert into teacher (name,phone,cid,classid) values(%s,%s,%s,%s)",
(name, tel, FX, banji))
db.commit()
return 'ok'
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
