def new_course(tid, idc, year, st, en):
"""教师新增加课程"""
sql = "insert into Course (idCourse,tid,year,start_week,end_week) " \
"values ('%s','%s','%s','%s','%s');" \
% (clean(idc), clean(tid), year, st, en)
db.execute(sql)
def authNewUser(id):
"""管理员通过信用户申请"""
sql = "select * from tempuser where id='%s';" % (clean(id))
user = db.get(sql)
name = user['name']
pwd = user['pwd']
type = user['type']
email = user['email']
if type == 't':
sql = "insert into Teacher (idTeacher,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
elif type == 's':
sql = "insert into Student (idStudent,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
else:
return 'fail'
def submit_homework_vedio(idHomework, sid, content, tag):
"""学生提交作业 视频"""
sql = "insert into Homework (idHomework,cid,sid,content,tag,type) values " \
"('%s',(select cid from Student where idStudent='%s'),'%s','%s','%s','video')" \
% (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def insertIntoTempUser(id, type, name, pwd, email):
"""将新申请的用户加入数据库"""
sql = "insert into tempuser (id,type,name,pwd,email) values ('%s','%s','%s','%s','%s');" % (
clean(id), clean(type), clean(name), clean(pwd), email)
# email到底怎么防止注入呢orz 并不会啊，需要改进
db.execute(sql)
def authNewUser(id):
"""管理员通过信用户申请"""
sql = "select * from tempuser where id='%s';" % (clean(id))
user = db.get(sql)
name = user['name']
pwd = user['pwd']
type = user['type']
email = user['email']
if type == 't':
sql = "insert into Teacher (idTeacher,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
elif type == 's':
sql = "insert into Student (idStudent,name,pwd,email) values ('%s','%s','%s','%s');" \
% (clean(id), name, pwd, email)
db.execute(sql)
sql = "delete from tempuser where id='%s';" % (clean(id))
db.execute(sql)
return 'success'
else:
return 'fail'
def insertIntoTempUser(id, type, name, pwd, email):
"""将新申请的用户加入数据库"""
sql = "insert into tempuser (id,type,name,pwd,email) values ('%s','%s','%s','%s','%s');" % (
clean(id), clean(type), clean(name), clean(pwd), email)
# email到底怎么防止注入呢orz 并不会啊，需要改进
db.execute(sql)
def submit_homework_vedio(idHomework, sid, content, tag):
"""学生提交作业 视频"""
sql = "insert into Homework (idHomework,cid,sid,content,tag,type) values " \
"('%s',(select cid from Student where idStudent='%s'),'%s','%s','%s','video')" \
% (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def submit_homework(idHomework, sid, content, tag):
"""学生提交作业 三视图"""
sql = "insert into Homework " \
"(idHomework,cid,sid,content,tag) " \
"values " \
"('%s'," \
"(select cid from Student where idStudent='%s')," \
"'%s'," \
"'%s'," \
"'%s')" % (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def submit_homework(idHomework, sid, content, tag):
"""学生提交作业 三视图"""
sql = "insert into Homework " \
"(idHomework,cid,sid,content,tag) " \
"values " \
"('%s'," \
"(select cid from Student where idStudent='%s')," \
"'%s'," \
"'%s'," \
"'%s')" % (clean(idHomework), clean(sid), clean(sid), cleanLink(content), clean(tag))
return db.execute(sql)
def publish_res(tid, idInfo, detail, title):
"""教师发布课程 资源"""
if not getTeacher(tid):
return 't nt exist'
# 需要加入判断内容长度的部分，防止内容转换后过长
detail = text2Html(detail)
title = clean(title)
sql = '''insert into Info (tid,idInfo,detail,title,type) values ('%s','%s','%s','%s','%s');''' \
% (clean(tid), clean(idInfo), detail, title, 'res');
db.execute(sql)
def get_info_by_infoid_all(Iid):
"""使用信息id号查找消息 02"""
sql='''
select I.idInfo,I.title,I.date,I.type,T.name,I.tid,I.detail
from Teacher as T,Info as I
where T.idTeacher=I.tid and I.idInfo = %s
''' % clean(Iid)
return db.get(sql)
def publish_notif(tid, idInfo, detail, title):
"""教师发布课程 通知 """
if not getTeacher(tid):
return "t nt exist"
# 需要加入判断内容长度的部分，防止内容转换后过长
# detail = text2Html(detail)
title = clean(title)
sql = "insert into Info (tid,idInfo,detail,title) values ('%s','%s','%s','%s');" % (
clean(tid),
clean(idInfo),
detail,
title,
)
db.execute(sql)
def get_all_comments(stu):
"""获取教师的所有评语"""
sql = "select comment,date,tag,type,idHomework " \
"from Homework " \
"where comment!='' and sid='%s' " \
"order by date desc;" \
% (clean(stu))
return db.query(sql)
def get_teacher_homework(uid):
"""获取教师所有作业"""
# return db.Course.find({'tid': 'uid'})
sql = """
select Homework.cid,name,type,date,comment,idHomework,tag from Homework,Student
where Homework.cid in (select idCourse from Course where tid='%s') and Student.idStudent = Homework.sid
order by date desc;
""" \
% (clean(uid))
return db.query(sql)
def get_teacher_homework(uid):
"""获取教师所有作业"""
# return db.Course.find({'tid': 'uid'})
sql = """
select Homework.cid,name,type,date,comment,idHomework,tag from Homework,Student
where Homework.cid in (select idCourse from Course where tid='%s') and Student.idStudent = Homework.sid
order by date desc;
""" \
% (clean(uid))
return db.query(sql)
def update_comment(hid, comment):
"""添加/修改作业的评语"""
sql = 'update Homework set comment="%s" where idHomework="%s";' % (text2Html(comment), clean(hid))
return db.execute(sql)
def get_course(cid):
"""获取某个课序号的所有信息"""
sql = "select * from Course where idCourse='%s';" % (clean(cid))
return db.get(sql)
def get_info(idInfo):
"""按照infoId获取info内容"""
sql = "select * from Info where idInfo = '%s';" % (clean(idInfo))
return db.get(sql)
def stuLogin(uid, pwd):
"""学生登陆"""
# return db.Student.find_one({}, {'user': clean(uid), 'password': clean(pwd)})
sql = 'select * from Student where idStudent="%s" and pwd="%s"' % (
clean(uid), clean(pwd))
return db.get(sql)
def get_student_course(uid):
"""获取学生参加的课程"""
sql = 'select * from Course where idCourse in (select cid from Student where idStudent="%s");' % (clean(uid))
return db.get(sql)
def getStudent(uid):
"""获取某位学生信息"""
sql = 'select * from Student where idStudent = "%s"' % (clean(uid))
return db.get(sql)
def get_teacher_notif(uid):
"""获取教师发布的所有信息"""
sql = 'select * from Info where tid = "%s";' % (clean(uid))
return db.query(sql)
def get_my_homework(uid):
"""获取学生提交的所有作业"""
sql = 'select * from Homework where sid="%s";' % (clean(uid))
return db.query(sql)
def get_info_by_infoid(Iid):
"""使用信息id号查找消息"""
sql = "select * from Info where idInfo='%s';" % (clean(Iid))
return db.get(sql)
def teaLogin(uid, pwd):
"""教师登陆"""
# return db.Teacher.find_one({}, {'user': clean(uid), 'password': clean(pwd)})
sql = 'select * from Teacher where idTeacher="%s" and pwd="%s"' % (clean(uid), clean(pwd))
return db.get(sql)
def delete_notif(iid, tid):
"""教师删除发布的消息"""
sql = 'delete from Info where tid="%s" and idInfo="%s"' % (clean(tid), clean(iid))
db.execute(sql)
def getATempUser(uid):
"""获取某一位临时用户"""
sql = 'select * from tempuser where id="%s"' % (clean(uid))
return db.get(sql)
def getStudent(uid):
"""获取某位学生信息"""
sql = 'select * from Student where idStudent = "%s"' % (clean(uid))
return db.get(sql)
def getTeacher(uid):
"""获取某位教师信息"""
sql = "select * from Teacher where idTeacher='%s'" % (clean(uid))
return db.get(sql)
def teaLogin(uid, pwd):
"""教师登陆"""
# return db.Teacher.find_one({}, {'user': clean(uid), 'password': clean(pwd)})
sql = 'select * from Teacher where idTeacher="%s" and pwd="%s"' % (
clean(uid), clean(pwd))
return db.get(sql)
def get_teacher_notif(uid):
"""获取教师发布的所有信息"""
sql = 'select * from Info where tid = "%s";' % (clean(uid))
return db.query(sql)
def get_homework(hid):
"""根据作业id，获取某次作业"""
sql = "select * from Homework where idHomework='%s';" % (clean(hid))
return db.get(sql)
def stuLogin(uid, pwd):
"""学生登陆"""
# return db.Student.find_one({}, {'user': clean(uid), 'password': clean(pwd)})
sql = 'select * from Student where idStudent="%s" and pwd="%s"' % (clean(uid), clean(pwd))
return db.get(sql)
def delete_homework(hid,sid):
"""删除已上传的作业"""
sql= "delete from Homework where sid='%s' and idHomework='%s'" % (clean(sid),clean(hid))
db.execute(sql)
def get_my_homework(uid):
"""获取学生提交的所有作业"""
sql = 'select * from Homework where sid="%s";' % (clean(uid))
return db.query(sql)
def getTeacher(uid):
"""获取某位教师信息"""
sql = "select * from Teacher where idTeacher='%s'" % (clean(uid))
return db.get(sql)
def get_info_by_infoid(Iid):
"""使用信息id号查找消息"""
sql = "select * from Info where idInfo='%s';" % (clean(Iid))
return db.get(sql)
def getATempUser(uid):
"""获取某一位临时用户"""
sql = 'select * from tempuser where id="%s"' % (clean(uid))
return db.get(sql)
def get_my_student(uid):
"""获取老师的所有学生"""
sql = 'select * from Student where cid in (select idCourse from Course where tid="%s");' % (
clean(uid))
return db.query(sql)
def delete_notif(iid,tid):
"""教师删除发布的消息"""
sql='delete from Info where tid="%s" and idInfo="%s"' % (clean(tid),clean(iid))
db.execute(sql)
def get_homework(hid):
"""根据作业id，获取某次作业"""
sql = "select * from Homework where idHomework='%s';" % (clean(hid))
return db.get(sql)
def get_teacher_course_delete(uid):
"""获取教师可以删除的(不在开课状态的)课程"""
# return db.Course.find({'tid': clean(uid), 'period': 0})
sql = 'select * from Course where tid="%s" and state=0 ' % (clean(uid))
return db.query(sql)
def update_comment(hid, comment):
"""添加/修改作业的评语"""
sql = 'update Homework set comment="%s" where idHomework="%s";' % (
text2Html(comment), clean(hid))
return db.execute(sql)
def get_student_homework(uid):
"""获取该学生所有的作业"""
sql = "select * from Homework where sid='%s';" % (clean(uid))
return db.query(sql)
def delete_homework(hid, sid):
"""删除已上传的作业"""
sql = "delete from Homework where sid='%s' and idHomework='%s'" % (
clean(sid), clean(hid))
db.execute(sql)
def set_course(uid, cid):
"""学生录入课序号"""
sql = "update Student set cid='%s' where idStudent='%s';" % (clean(cid), clean(uid))
db.execute(sql)
def get_info(idInfo):
"""按照infoId获取info内容"""
sql = "select * from Info where idInfo = '%s';" % (clean(idInfo))
return db.get(sql)
def get_teacher_course(uid):
"""获取该教师所有的课程"""
# return db.Course.find({'tid': clean(uid)})
sql = 'select * from Course where tid= "%s";' % (clean(uid))
return db.query(sql)
def get_my_student(uid):
"""获取老师的所有学生"""
sql = 'select * from Student where cid in (select idCourse from Course where tid="%s");' % (clean(uid))
return db.query(sql)