def clearTeacherGeifen():
s = mysql.Sql()
s.sqlstr("DELETE FROM t_geifen where t_idfrom='%s'" %
session['user']['t_id'])
s.sqlstr("UPDATE teacher SET count = 1 where t_id='%s'" %
session['user']['t_id'])
return "提交完成"
def getTeacherinfo():
s = mysql.Sql()
r = s.search("SELECT count FROM TEACHER WHERE t_id='%s'" %
session['user']['t_id'])
if(r[0]['count'] == 0):
return jsonify([{'t_name': "已评分或无权限"}])
# -----------------------------------------------------------------------申静-------------------------------------------------------
sj_ids = [1944, 1963]
sjid = 1034
if(session['user']['t_id'] in sj_ids):
r = s.search('''
SELECT t_name,t_id,order1,zu_id FROM TEACHER WHERE t_id='%s'
''' % sjid)
elif(session['user']['kind'] == 1):
r = s.search('''
SELECT t_name,t_id,order1,zu_id FROM TEACHER WHERE (kind=2 or kind=3) and bumen_id=%s
''' % session['user']['bumen_id'])
else:
r = s.search('''
SELECT t_name,t_id,order1,zu_id FROM TEACHER WHERE (kind=2 or kind=3)
''')
for i in r:
if(i['zu_id']==session['user']['zu_id'] and session['user']['kind']==4):
i['order1']-=200
return jsonify(r)
def addBumen():
if request.method == 'GET':
return render_template('addBumen.html')
try:
f = request.files['file']
f.save(secure_filename(f.filename))
d = excel.get_bumen(f.filename)
s = mysql.Sql()
# 清空表
s.sqlstr("truncate table bumen")
s.sqlstr("truncate table bu_geifen")
for i in d:
sql_str = '''
INSERT INTO BUMEN(
bumen_id,t_id,bumen_name,order1)
VALUES (%s,"%s","%s",%s)
''' % (
i,
d[i][0],
d[i][1],
d[i][2]
)
sql_str = sql_str.replace("None", "NULL")
s.sqlstr(sql_str)
except:
print(traceback.format_exc())
return traceback.format_exc()
return "提交完成"
def checkBumenCount():
s = mysql.Sql()
counts = s.search("SELECT t_id,count_bu FROM TEACHER")
l = list()
for i in counts:
if(i['count_bu'] == 1):
l.append(i['t_id'])
return jsonify(l)
def changePwd():
if(request.method == "GET"):
return render_template('changePwd.html')
pwd = request.form['pwd']
s = mysql.Sql()
r = s.sqlstr('''
UPDATE TEACHER SET t_password = %s where t_id='%s'
''' % (pwd, session['user']['t_id']))
return '提交完成'
def clearBumenGeifen():
if(session['user']['kind'] != 3 and session['user']['kind'] != 4):
return "无权限"
s = mysql.Sql()
s.sqlstr("DELETE FROM bu_geifen where t_id='%s'" %
session['user']['t_id'])
s.sqlstr("UPDATE teacher SET count_bu = 1 where t_id='%s'" %
session['user']['t_id'])
return "提交完成"
def addTeacher():
# id 姓名 密码 部门id 组id 类型（1普通2副处3正处4校级） 次数
if request.method == 'GET':
return render_template('addTeacher.html')
try:
f = request.files['file']
f.save(secure_filename(f.filename))
d = excel.get_teachers(f.filename)
s = mysql.Sql()
# 保存admin pwd
r = s.search("SELECT * FROM TEACHER WHERE kind=0")
# 清空表
s.sqlstr("truncate table t_geifen")
s.sqlstr("truncate table t_defen")
s.sqlstr("truncate table bu_geifen")
s.sqlstr("truncate table bu_defen")
s.sqlstr("SET FOREIGN_KEY_CHECKS=0")
s.sqlstr("truncate table teacher")
s.sqlstr("SET FOREIGN_KEY_CHECKS=1")
# 存入admin
for i in r:
s.sqlstr(
"""
INSERT INTO TEACHER(t_id, t_name, t_password, bumen_id, zu_id, kind, count, count_bu)
VALUES("%s", "%s", "%s", %s, %s, %s, %s, %s)
""" % (
i['t_id'], i['t_name'], i['t_password'], i['bumen_id'], i['zu_id'], i['kind'], i['count'], i['count_bu']
))
for i in d:
sql_str = '''
INSERT INTO TEACHER(
t_id,t_name,t_password, bumen_id, zu_id, kind,count,count_bu,order1
)
VALUES ("%s","%s", "%s", %s, %s ,%s,%s,%s,%s)
''' % (
i,
d[i][0],
d[i][1],
d[i][2],
d[i][3],
d[i][4],
d[i][5],
d[i][6],
d[i][7]
)
sql_str = sql_str.replace("None", "NULL")
s.sqlstr(sql_str)
except:
print(traceback.format_exc())
return traceback.format_exc()
return "提交完成"
def getBumenAllinfo():
if request.method == 'GET':
return render_template('getBumenAllinfo.html')
s = mysql.Sql()
r = s.search(
'''
SELECT * FROM BUMEN
'''
)
return jsonify(r)
def getTeacherDefen():
if request.method == 'GET':
return render_template('getTeacherDefen.html')
s = mysql.Sql()
r = s.search(
'''
SELECT t_name,part1_score,part2_score,part3_score,score FROM teacher,t_defen WHERE t_defen.t_id = teacher.t_id
'''
)
return jsonify(r)
def getTeacherAllinfo():
if request.method == 'GET':
return render_template('getTeacherAllinfo.html')
s = mysql.Sql()
r = s.search(
'''
SELECT * FROM TEACHER
'''
)
return jsonify(r)
def index():
if request.method == 'GET':
return render_template('index.html')
s = mysql.Sql()
r = s.search(
'''
SELECT * FROM FORM
'''
)
return jsonify(r)
def getBumenDefen():
if request.method == 'GET':
return render_template('getBumenDefen.html')
s = mysql.Sql()
r = s.search(
'''
SELECT bumen_name,part1_score,part2_score,score FROM bumen,bu_defen WHERE bumen.bumen_id=bu_defen.bumen_id
'''
)
return jsonify(r)
def getBumenGeifen():
if request.method == 'GET':
return render_template('getBumenGeifen.html')
s = mysql.Sql()
r = s.search(
'''
SELECT * FROM bu_geifen
'''
)
return jsonify(r)
def getBumeninfo():
s = mysql.Sql()
r = s.search("SELECT count_bu FROM TEACHER WHERE t_id='%s'" %
session['user']['t_id'])
if(r[0]['count_bu'] == 0):
return jsonify([{'bumen_name': "已评分或无权限"}])
r = s.search('''
SELECT * FROM bumen
''')
return jsonify(r)
def postTeacherScore():
# t_idfrom t_idto num1 num2 num3 num4 num5
j = request.form['json']
d = json.loads(j)
# {"11":"E","13":"E","14":"E","21":"B"}
d_num = {'A': 9, 'B': 8, 'C': 7, 'D': 5}
d2 = dict()
for i in d:
if(i[1:] not in d2):
d2[i[1:]] = dict()
d2[i[1:]][i[0]] = d_num[d[i]]
s = mysql.Sql()
# 查询总人数
if(session['user']['kind'] == 1):
count_t = s.search("SELECT COUNT(*) FROM TEACHER WHERE (kind=2 or kind=3) and bumen_id=%s"
% session['user']['bumen_id'])[0]['COUNT(*)']
else:
count_t = s.search(
"SELECT COUNT(*) FROM TEACHER WHERE kind=2 or kind=3")[0]['COUNT(*)']
if len(d2) != int(count_t):
return "提交出错！请检查是否有空项"
for i in d2:
if(len(d2[i]) != 5):
return "提交出错！请检查是否有空项"
for i in d2:
sql_str = '''
INSERT INTO t_geifen(
t_idfrom,t_idto, t_num1,t_num2,t_num3,t_num4,t_num5
)
VALUES ("%s", "%s", %s, %s,%s,%s,%s)
''' % (
session['user']['t_id'],
i,
d2[i]['1'],
d2[i]['2'],
d2[i]['3'],
d2[i]['4'],
d2[i]['5']
)
s.sqlstr(sql_str)
# 更改次数标志
s.sqlstr("UPDATE TEACHER SET count = 0 WHERE t_id='%s'" %
session['user']['t_id'])
return "提交完成"
def admin():
if request.method == 'GET':
return render_template('login.html')
t_id = request.form['t_id']
password = request.form['password']
s = mysql.Sql()
r = s.search('''
SELECT * FROM ADMIN WHERE username='******' and password='******'
''' % (t_id, password))
if(len(r) == 0):
return "账号或密码错误"
else:
session["user"] = r[0]
# 接入管理员端
return "success"
def login():
if request.method == 'GET':
return render_template('login.html')
t_id = request.form['t_id']
password = request.form['password']
s = mysql.Sql()
r = s.search('''
SELECT * FROM TEACHER WHERE t_id='%s' and t_password='******'
''' % (t_id, password))
if(len(r) == 0):
return "工号或密码错误"
session["user"] = r[0]
# 接入管理员端
if(r[0]['kind'] == 0):
return redirect("/addTeacher")
return redirect("/index")
def postBumenScore():
# bumen_id t_id num1 num2 num3 num4
j = request.form['json']
d = json.loads(j)
# d_num = {'A': 9, 'B': 8, 'C': 7, 'D': 5, }
d2 = dict()
for i in d:
if(i[1:] not in d2):
d2[i[1:]] = dict()
if(d[i] == ''):
return "提交出错！请检查是否有空项"
d2[i[1:]][i[0]] = float(d[i])
s = mysql.Sql()
# 查询正处级校级总人数
# count_t = s.search("SELECT COUNT(*) FROM TEACHER WHERE kind=3 or kind=4 and bumen_id=%s"
# % session['user']['bumen_id'])[0]['COUNT(*)']
# 部门数
count_bumen = s.search("SELECT COUNT(*) FROM bumen ")[0]['COUNT(*)']
if len(d2) != int(count_bumen):
return "提交出错！请检查是否有空项"
for i in d2:
if(len(d2[i]) != 4):
return "提交出错！请检查是否有空项"
for i in d2:
sql_str = '''
INSERT INTO bu_geifen(
bumen_id,t_id,num1,num2,num3,num4
)
VALUES (%s, "%s", %s, %s,%s,%s)
''' % (
i,
session['user']['t_id'],
d2[i]['1'],
d2[i]['2'],
d2[i]['3'],
d2[i]['4'],
)
s.sqlstr(sql_str)
# 更改次数标志
s.sqlstr("UPDATE TEACHER SET count_bu = 0 WHERE t_id='%s'" %
session['user']['t_id'])
return "提交完成"
def getform():
text = request.form['text']
name = request.form['name']
phonenumber = request.form['phonenumber']
email = request.form['email']
address = request.form['address']
s = mysql.Sql()
sql_str = '''
INSERT INTO form(
text, name,phonenumber,email,address
)
VALUES ("%s", '%s', '%s','%s','%s')
''' % (
text,
name,
phonenumber,
email,
address
)
s.sqlstr(sql_str)
return "success"
def outputTeacherDefen():
s = mysql.Sql()
r = s.search(
"SELECT t_name,part1_score,part2_score,part3_score,score FROM teacher,t_defen WHERE t_defen.t_id=teacher.t_id")
src = excel.output_excel(r, 'TeacherDefen')
return send_from_directory('output', src, as_attachment=True)
def collectBumenScore():
# bumen_id part1_score part2_score score
s = mysql.Sql()
bumen_ids = s.search('''
SELECT bumen_id FROM bumen
''')
d = dict()
for i in bumen_ids:
bumen_id = i['bumen_id']
r = s.search('''
SELECT * FROM bu_geifen WHERE bumen_id=%s
''' % (bumen_id)
) # r is list
d[bumen_id] = r
# 清空得分表
s.sqlstr("truncate table bu_defen")
for i in d:
if len(d[i]) == 0:
continue
zhengchu = list()
xiaoji = list()
xiaoji_zhixi = list()
for u in d[i]:
# 查询类型
r = s.search('''
SELECT bumen_id,kind FROM TEACHER WHERE t_id='%s'
''' % (u['t_id'])
)
# 类型（1普通2副处3正处4校级）
if(r[0]['kind'] == 3):
zong = u['num1']+u['num2'] + \
u['num3']+u['num4']
zhengchu.append(zong)
elif(r[0]['kind'] == 4):
zong = u['num1']+u['num2'] + \
u['num3']+u['num4']
r_bumen = r[0]['bumen_id']
bumen_id = i
if(r_bumen == bumen_id):
# 同组
xiaoji_zhixi.append(zong)
else:
xiaoji.append(zong)
zhengchu_avg = 0
xiaoji_avg = 0
xiaojizhixi_avg = 0
try:
zhengchu_avg = sum(zhengchu)/len(zhengchu)
except:
pass
try:
xiaoji_avg = sum(xiaoji)/len(xiaoji)
except:
pass
try:
xiaojizhixi_avg = sum(xiaoji_zhixi)/len(xiaoji_zhixi)
except:
pass
# 部门权重
part1_score = zhengchu_avg*0.6
# 直属0.1 非直属0.3
# part2_score = xiaojizhixi_avg*0.1+xiaoji_avg*0.3
part2_score = (xiaojizhixi_avg+xiaoji_avg)*0.4
score = part1_score+part2_score
s.sqlstr('''
INSERT INTO bu_defen(
bumen_id,part1_score,part2_score,score
)
VALUES (%s, %s, %s, %s)
''' % (
i,
part1_score,
part2_score,
score
))
return "提交完成"
def outputTeacher():
s = mysql.Sql()
r = s.search("SELECT * FROM TEACHER")
src = excel.output_excel(r, 'teacher')
return send_from_directory('output', src, as_attachment=True)
def outputBumenDefen():
s = mysql.Sql()
r = s.search(
"SELECT bumen_name,part1_score,part2_score,score FROM bumen,bu_defen WHERE bumen.bumen_id=bu_defen.bumen_id")
src = excel.output_excel(r, 'BumenDefen')
return send_from_directory('output', src, as_attachment=True)
def outputBumenGeifen():
s = mysql.Sql()
r = s.search("SELECT * FROM bu_geifen")
src = excel.output_excel(r, 'BumenGeifen')
return send_from_directory('output', src, as_attachment=True)
def collectTeacherScore():
# t_id part1_score part2_score part3_score score
s = mysql.Sql()
t_ids = s.search('''
SELECT t_id FROM TEACHER
''')
d = dict()
for i in t_ids:
t_id = i['t_id']
r = s.search('''
SELECT * FROM t_geifen WHERE t_idto='%s'
''' % (t_id)
) # r is list
d[t_id] = r
# 清空得分表
s.sqlstr("truncate table t_defen")
for i in d:
if len(d[i]) == 0:
continue
putong = list()
zhengchu = list()
fuchu = list()
xiaoji_zhixi = list()
xiaoji = list()
for u in d[i]:
# 查询类型
r = s.search('''
SELECT kind FROM TEACHER WHERE t_id='%s'
''' % (u['t_idfrom'])
)
# 类型（1普通2副处3正处4校级） 30 40 30
if(r[0]['kind'] == 1):
zong = u['t_num1']+u['t_num2'] + \
u['t_num3']+u['t_num4']+u['t_num5']
putong.append(zong)
elif(r[0]['kind'] == 2):
zong = u['t_num1']+u['t_num2'] + \
u['t_num3']+u['t_num4']+u['t_num5']
fuchu.append(zong)
elif(r[0]['kind'] == 3):
zong = u['t_num1']+u['t_num2'] + \
u['t_num3']+u['t_num4']+u['t_num5']
zhengchu.append(zong)
elif(r[0]['kind'] == 4):
zong = u['t_num1']+u['t_num2'] + \
u['t_num3']+u['t_num4']+u['t_num5']
r_from = s.search('''
SELECT zu_id FROM TEACHER WHERE t_id='%s'
''' % (u['t_idfrom'])
)
r_to = s.search('''
SELECT zu_id FROM TEACHER WHERE t_id='%s'
''' % (u['t_idto'])
)
if(r_from[0]['zu_id'] == r_to[0]['zu_id']):
# 同组
xiaoji_zhixi.append(zong)
else:
xiaoji.append(zong)
putong_avg = 0
zhengchu_avg = 0
fuchu_avg = 0
xiaoji_avg = 0
xiaojizhixi_avg = 0
try:
putong_avg = sum(putong)/len(putong)
except:
pass
try:
zhengchu_avg = sum(zhengchu)/len(zhengchu)
except:
pass
try:
fuchu_avg = sum(fuchu)/len(fuchu)
except:
pass
try:
xiaoji_avg = sum(xiaoji)/len(xiaoji)
except:
pass
try:
xiaojizhixi_avg = sum(xiaoji_zhixi)/len(xiaoji_zhixi)
except:
pass
# 权重
part1_score = putong_avg * 0.3
part2_score = (zhengchu_avg+fuchu_avg)/2*0.4
part3_score = xiaoji_avg*0.2+xiaojizhixi_avg*0.1
score = part1_score+part2_score+part3_score
s.sqlstr('''
INSERT INTO t_defen(
t_id,part1_score,part2_score,part3_score,score
)
VALUES ("%s", %s, %s, %s,%s)
''' % (
i,
part1_score,
part2_score,
part3_score,
score
))
return "提交完成"
