def get_QuestionDetail():
# 查看题目详情
data = request.args.get("question_no")
sql = "SELECT T1.question_diff, T1.question_type, T1.question_content, T1.question_answer, T1.question_analy, figure_url, paper_info, know_no \
FROM (SELECT * from question where question_no = {question_no}) AS T1 LEFT JOIN figure on figure.question_no = T1.question_no \
INNER JOIN paper ON paper.paper_no = T1.paper_no".format(question_no=data)
# return jsonify(sql)
db = get_db()
db_res = excute_select(db, sql)
code = 0
# return jsonify(sql)
if db_res == ():
code = -1
else:
db_res = [list(item) for item in list(db_res)]
db_res = db_res[0]
# return jsonify(db_res[7])
know_list = get_parent_name_list("know", str(db_res[7]))
# return jsonify(know_list)
# 查看有没有子题目
sql = "SELECT little_question_no, little_question_type, little_question_content, \
little_question_answer, little_question_analy \
FROM little_question where super_question_no = {question_no} \
order by question_no".format(question_no=data)
res = excute_select(db, sql)
result = []
for item in res:
item = list(item)
dic = {"little_question_no":str(item[0]),
"little_question_type":str(types.index(item[1])),
"little_question_content":item[2],
"little_question_answer":item[3],
"little_question_analy":item[4]
}
result.append(dic)
dic = { "code":code ,
"question_diff":db_res[0],
"question_type":str(types.index(db_res[1])),
"question_content":db_res[2],
"question_answer":db_res[3],
"question_analy":db_res[4],
"figure_url":db_res[5],
"paper_name":db_res[6],
"knowledge_point":know_list,
"little_question":result,
}
return jsonify(dic)
return jsonify({"code":code})
def login():
# Log in a registered user by adding the user id to the session.
# code 0 成功 -1 不成功
data = request.get_data()
data = json.loads(data)
db = get_db()
sql = "SELECT user_pwd,user_auth FROM user WHERE user_name = '{username}'".format(
username=data["user_name"])
db_res = excute_select(db, sql)
code = 0
if db_res == ():
code = -1
else:
db_res = [list(item) for item in list(db_res)]
if db_res[0][0] != data["user_pwd"]:
code = -1
if code == 0:
# store the user id in a new session
session.clear()
session['user_name'] = data["user_name"]
session['user_auth'] = db_res[0][1]
return jsonify({
"code": code,
"user_name": data["user_name"],
"user_auth": db_res[0][1]
})
return jsonify({"code": code})
def get_all_sub(sql): # 返回字典，所有知识点/source对应的一级子知识点/source(列表)，学科/上海的父节点为"0" db = get_db() res = excute_select(db, sql) res = [list(item) for item in list(res)] dic = dict() for item in res: super_no = "0" if item[1] == None else str(item[1]) if super_no not in dic: dic[super_no] = [] if str(item[0]) not in dic: dic[str(item[0])] = [] dic[super_no].append(str(item[0])) return dic
def get_UsersMsg():
# 获取用户表所有记录
db = get_db()
sql = "SELECT user_name, user_pwd, user_auth FROM user"
db_res = excute_select(db, sql)
result = []
for item in db_res:
item = list(item)
dic = {
"userName": item[0],
"password": item[1],
"permission": item[2],
"infoChangeSeen": False,
"changeButton": "编辑",
"lastUserName": "******"
}
result.append(dic)
return jsonify(result)
def add_user():
#code 0 成功 -1 不成功
data = request.get_data()
data = json.loads(data)
db = get_db()
sql = "SELECT * FROM user WHERE user_name = '{username}'".format(
username=data["user_name"])
db_res = excute_select(db, sql)
code = 0
if db_res != ():
code = -1
else:
sql = "INSERT INTO user(user_name, user_pwd, user_auth)\
VALUES ('{name}', '{pwd}','{auth}')".format(name=data["user_name"],
pwd=data["user_pwd"],
auth=data["user_auth"])
db_res = excute_insert(db, sql)
if db_res == "Error":
code = 1
return jsonify({"code": code})
def get_parent_no_list(flag, point): # 给定一个知识点/source，返回知识点/source列表，父、子、子、最后当前知识点/source sql = "" if flag == "know": sql = "SELECT know_no, super_no from know" else: sql = "SELECT school_no, super_no from school" db = get_db() res = excute_select(db, sql) res = [list(item) for item in list(res)] dic = dict() for item in res: dic[str(item[0])] = "0" if item[1] == None else str(item[1]) know_list = [point] item = dic[point] while item != "0": know_list.append(item) item = dic[item] know_list.reverse() return know_list
def get_parent_name_list(flag, point):
# 给定一个知识点/source，返回知识点/source列表，父、子、子、最后当前知识点/source
sql = ""
if flag == "know":
sql = "SELECT know_no, know_name, super_no from know"
else:
sql = "SELECT school_no, school_name, super_no from school"
db = get_db()
res = excute_select(db, sql)
res = [list(item) for item in list(res)]
dic = dict()
for item in res:
dic[str(item[0])] = {"super_no": "0", "name":item[1]} if item[2] == None else {"super_no":str(item[2]), "name":item[1]}
# return jsonify(dic)
know_list = [dic[point]["name"]]
item = dic[point]["super_no"]
while item != "0":
know_list.append(dic[item]["name"])
item = dic[item]["super_no"]
know_list.reverse()
return know_list
def get_SourcesNo():
# 获取所有试卷来源, 按市-区-学校
sql = "SELECT T1.school_no, T1.school_name, T1.super_no, T2.school_name\
FROM school AS T1 LEFT JOIN school AS T2 ON T1.super_no = T2.school_no"
db = get_db()
db_res = excute_select(db, sql)
db_res = [list(item) for item in list(db_res)]
# return jsonify(db_res)
dic = dict()
for item in db_res:
super_no = "0" if item[2] == None else str(item[2])
super_name = "0" if item[3] == None else item[3]
if super_no not in dic:
dic[super_no] = {"no": super_no, "label": super_name, "children" : []}
if str(item[0]) not in dic:
dic[str(item[0])] = {"no": str(item[0]), "label": item[1]}
if "children" not in dic[super_no]:
dic[super_no]["children"] = []
dic[super_no]["children"].append(str(item[0]))
# return jsonify(dic)
dic = dfs_children(dic, "0")
res = dic["0"]["children"]
return jsonify(res)
def update_pwd():
#code 0 成功 -1 旧密码不对 -2 两次密码不一致
data = request.get_data()
data = json.loads(data)
if (data["new_pwd"] != data["repeat_pwd"]):
return jsonify({"code": -2})
db = get_db()
sql = "SELECT * FROM user WHERE user_name = '{username}'".format(
username=data["user_name"])
db_res = excute_select(db, sql)
code = 0
if db_res == ():
code = -1
else:
db_res = [list(item) for item in list(db_res)]
if db_res[0][1] != data["old_pwd"]:
code = -1
else:
sql = "UPDATE user SET user_pwd = '{new_pwd}' WHERE user_name = '{username}'".format(
new_pwd=data["new_pwd"], username=data["user_name"])
db_res = excute_update(db, sql)
if db_res == "Error":
code = -1
return jsonify({"code": code})
def upload_Paper():
# 上传试卷试题 code 0成功 -1不成功
data = request.get_data()
data = json.loads(data)
# return jsonify(1)
paperForm = data["paperInfoForm"]
questionsForm = data["questionsForm"]
# 定义数据库连接
db = get_db()
# 插入试卷
sql = "INSERT INTO paper(paper_subject, grade, paper_info, source)\
VALUES ({subject},{grade},'{info}',{source})".format(
subject=paperForm["paper_subject"],
grade=paperForm["paper_grade"],
info=paperForm["paper_year"] + paperForm["paper_name"],
source=paperForm["paper_source"])
db_res = excute_insert(db, sql)
if db_res == 'Error':
return jsonify({"code": -1})
# 查看试卷号
sql = "SELECT paper_no FROM paper WHERE paper_subject={subject} AND grade={grade} \
AND paper_info='{info}' AND source={source}".format(
subject=paperForm["paper_subject"],
grade=paperForm["paper_grade"],
info=paperForm["paper_year"] + paperForm["paper_name"],
source=paperForm["paper_source"])
db_res = excute_select(db, sql)
if db_res == ():
return jsonify({"code": -1})
paper_no = db_res[0][0]
# return jsonify(paper_no)
# 插入题目
code = 0
error_result = []
for i in range(0, len(questionsForm)):
item = questionsForm[i]
if item["question_type"] != 5:
sql = "INSERT INTO question(paper_no, question_type, question_diff, \
question_content, question_answer, question_analy, know_no) \
VALUES ({paper},'{type}',{diff},'{content}','{answer}','{analy}',{know_no})".format(
paper=paper_no,
type=types[item["question_type"]],
diff=item["question_diff"],
content=item["question_content"],
answer=item["question_answer"],
analy=item["question_analy"],
know_no=item["knowledge_point"])
db_res = excute_insert(db, sql)
# return jsonify(db_res)
if db_res == 'Error':
code = -1
error_result.append(i)
else:
sql = "INSERT INTO question(paper_no, question_type, question_diff, \
question_content, question_analy, know_no) \
VALUES ({paper},'{type}',{diff},'{content}','{analy}',{know_no})".format(
paper=paper_no,
type=types[item["question_type"]],
diff=item["question_diff"],
content=item["question_content"],
analy=item["question_analy"],
know_no=item["knowledge_point"])
# return jsonify(sql)
db_res = excute_insert(db, sql)
# return jsonify(db_res)
if db_res == 'Error':
code = -1
error_result.append(i)
# 查看刚插入的编号
sql = "SELECT question_no FROM question WHERE paper_no={paper} AND \
question_type='{type}' AND question_diff={diff} AND question_content='{content}' \
AND question_analy='{analy}' AND know_no={know_no}".format(
paper=paper_no,
type=types[item["question_type"]],
diff=item["question_diff"],
content=item["question_content"],
analy=item["question_analy"],
know_no=item["knowledge_point"])
db_res = excute_select(db, sql)
# return jsonify(db_res)
if db_res == ():
code = -1
else:
super_question_no = db_res[0][0]
# return jsonify(db_res[0][0])
for subitem in item["question_answer"]:
# return jsonify(subitem)
sql = "INSERT INTO little_question(little_question_type, \
little_question_content, little_question_answer, \
super_question_no) VALUES ('{type}','{content}','{answer}',\
{super_no})".format(type=types[subitem["question_type"]],
content=subitem["question_content"],
answer=subitem["question_answer"],
super_no=super_question_no)
# return jsonify(sql)
db_res = excute_insert(db, sql)
if db_res == 'Error':
code = -1
error_result.append(i)
if code == 0:
return jsonify({"code": 0})
return jsonify({"code": -1, "error_result": error_result})
def get_Question():
#试题库获取每页题目
data = request.get_data()
data = json.loads(data)
paper_grade = [str(item) for item in data["conditionForm"]["paper_grade"]]
paper_grade = ", ".join(paper_grade) # "10, 11, 12"
paper_subject = ""
if data["conditionForm"]["paper_subject"] != 0:
paper_subject = "AND paper_subject = {0}".format(data["conditionForm"]["paper_subject"])
paper_source = ""
if data["conditionForm"]["paper_source"] != 0:
source = get_child_list("source", str(data["conditionForm"]["paper_source"]))
paper_source = "AND source in ({0})".format(", ".join(source))
# return jsonify(paper_source)
paper_nature = ""
if data["conditionForm"]["paper_nature"] != 0:
paper_nature = "AND source in (SELECT school_no FROM school \
WHERE school_nature = '{0}')".format(natures[data["conditionForm"]["paper_nature"]])
# return jsonify(paper_nature)
knowledge_point = ""
if data["conditionForm"]["knowledge_point"] != 0:
know = get_child_list("know", str(data["conditionForm"]["knowledge_point"]))
know = ", ".join(know)
knowledge_point = "AND know_no in (%s)" % know
question_type = ""
if data["conditionForm"]["question_type"] != 0:
question_type = "AND question_type = '{0}'".format(types[data["conditionForm"]["question_type"]])
question_diff = ""
if data["conditionForm"]["question_diff"] != 0:
question_diff = "AND question_diff = {0}".format(data["conditionForm"]["question_diff"])
# return jsonify(question_diff)
sql = "SELECT T2.question_no, T2.question_content, T1.paper_info FROM \
(SELECT paper_info, paper_no FROM paper WHERE grade in ({grade}) \
{subject} {source} {nature}) AS T1 \
INNER JOIN (SELECT paper_no, question_no, question_content FROM question \
WHERE Locate('{keyword}' ,question_content) > 0 {know_point} {ques_type} \
{ques_diff}) AS T2 ON T1.paper_no=T2.paper_no".format(grade=paper_grade, subject=paper_subject,
source=paper_source, nature=paper_nature,
keyword=data["conditionForm"]["keyword"],
know_point=knowledge_point, ques_type=question_type,
ques_diff=question_diff)
# return jsonify(sql)
db = get_db()
db_res = excute_select(db, sql)
# return jsonify(db_res)
length = min(data["questionNum"] * (data["questionOffset"] + 1), len(db_res))
result = []
for i in range(data["questionNum"] * data["questionOffset"], length):
item = list(db_res[i])
dic = {"question_no": str(item[0]),
"question_content": item[1],
"paper_name": item[2],
# "figure_url": item[3]
}
result.append(dic)
return jsonify({"quantity":len(db_res), "questions": result})