def showSelectCourse(usr, term, isstu=True):
'''
展示某学生已选修课程 在e表中进行课程查询
或者是某老师开设的所有课程 在o表中进行查询
'''
if isstu:
sql = "select kh, gh from e where xh='%s' and xq = '%s'" % (usr, term)
flag, res = op_mysql(sql)
if flag == False or len(res) == 0:
return []
data = []
for x in res:
tmp = []
tmp.append(x[0]) #课号
tmp.append(searchOnC(iskh=False, iskm=True, kh=x[0])[0][0]) #课程名
tmp.append(x[1]) #工号
tmp.append(searchOnT(isgh=False, isxm=True, gh=x[1])[0][0]) #教师名
tmp.append(searchOnO(iskh=False, isgh=False, issksj=True, xq=term, kh=x[0], gh=x[1])[0][0]) # 上课时间
data.append(tmp)
return data
else:
sql = "select kh from o where gh='%s' and xq='%s'" % (usr, term)
flag, res = op_mysql(sql)
if flag == False or len(res) == 0:
return []
data = []
for x in res:
tmp = []
tmp.append(x[0]) #课号
tmp.append(searchOnC(iskh=False, iskm=True, kh=x[0])[0][0]) #课程名
tmp.append(searchOnO(iskh=False, isgh=False, issksj=True, xq=term, kh=x[0], gh=usr)[0][0]) # 上课时间
data.append(tmp)
return data
def dropSelectCourse(usr, term, kh, gh):
'''
退课
'''
sql = "delete from e where xh='%s' and xq = '%s' and kh='%s' and gh='%s'" % (usr, term, kh, gh)
flag, _ = op_mysql(sql)
return flag
def updateStuScore(xh, xq, kh, gh, pscj, kscj, zpcj):
'''
更新学生的成绩
'''
sql = '''update e set pscj=%s, kscj=%s, zpcj=%s
where xh='%s' and xq='%s' and kh='%s' and gh='%s'
''' % (pscj, kscj, zpcj, xh, xq, kh, gh)
flag, e = op_mysql(sql)
return flag, e
def getClassLists(xq, kh, gh):
'''
在选课表e中获取某个老师某门课程的班级信息
'''
sql = "select xh, pscj, kscj, zpcj from e where xq='%s' and kh='%s' and gh='%s'" %(xq, kh, gh)
flag, res = op_mysql(sql)
if flag and len(res) == 0:
return []
elif flag == False:
return []
data = []
for x in res:
tmp = []
tmp.append(x[0])
sql = "select xm from s where xh = '%s'" % (x[0])
_, y = op_mysql(sql)
tmp.append(y[0][0])
for i in range(1, 4):
if x[i] != None:
tmp.append(str(x[i]))
else:
tmp.append('null')
data.append(tmp)
return data
def selectCourse(usr, xq, kh, gh):
'''
选课
return: True / False
'''
res = searchOnE(0, 0, 1, 0, xh=usr, xq=xq, kh=kh)
if len(res):
return False
time1 = searchOnO(0, 0, 0, 1, xq=xq, kh=kh, gh=gh)[0][0]
res = searchOnE(0, 0, 1, 1, xh=usr, xq=xq)
for x in res:
time2 = searchOnO(0, 0, 0, 1, xq=xq, kh=x[0], gh=x[1])[0][0]
if judgeclasstime(time1, time2) == False:
return False
sql = "insert into e values('%s', '%s', '%s', '%s', null, null, null)" % (usr, xq, kh, gh)
flag, _ = op_mysql(sql)
return flag
def delOpenCourse(xq, kh, gh, sksj):
'''
删除已经开设的课程
'''
sql = "SET FOREIGN_KEY_CHECKS=0"
op_mysql(sql)
sql = "delete from e where xq = '%s' and gh = '%s' and kh = '%s'" % (xq, gh, kh)
op_mysql(sql)
sql = "delete from o where xq = '%s' and kh = '%s' and gh = '%s' and sksj = '%s'" % (xq, kh, gh, sksj)
flag, e = op_mysql(sql)
sql = "SET FOREIGN_KEY_CHECKS=1"
op_mysql(sql)
return flag, e
def finishedCourse(usr):
'''
查询某学生已经修过的课程
定义: 总分大于60才能算修过
'''
sql = "select xq, kh, gh from e where xh = '%s' and (zpcj >= 60)" % (usr)
flag, res = op_mysql(sql)
if flag == False or len(res) == 0:
return []
data = []
for x in res:
tmp = []
tmp.append(x[0]) #学期
tmp.append(x[1]) #课号
tmp.append(searchOnC(iskh=False, iskm=True, kh=x[1])[0][0]) #课程名
tmp.append(x[2]) #工号
tmp.append(searchOnT(isgh=False, isxm=True, gh=x[2])[0][0]) #教师名
tmp.append(searchOnO(iskh=False, isgh=False, issksj=True, xq=x[0], kh=x[1], gh=x[2])[0][0]) # 上课时间
data.append(tmp)
return data
def getScore(xh, xq):
sql = "select kh, gh, zpcj from e where xh = '%s' and xq = '%s' and zpcj > 0" % (xh, xq)
flag, res = op_mysql(sql)
if flag and (len(res) == 0):
return []
elif flag == False:
return []
data = [] # 课程号', '课程名', '学分', '教师', '总分', '评定']
for x in res:
tmp = []
tmp.append(x[0]) # kh
tmp.append(searchOnC(iskh=False, iskm=True, kh=x[0])[0][0]) # km
tmp.append(searchOnC(0, 0, 1, 0, 0, kh=x[0])[0][0]) # xf
tmp.append(searchOnT(isgh=False, isxm=True, gh=x[1])[0][0]) # js
tmp.append(x[2]) # zpcj
tmp.append(tranScore(int(x[2])))
data.append(tmp)
return data
def openCourse(usr, term, cname, ctime):
'''
往开课表中增加数据
老师开课()
'''
time1 = ctime
res = searchOnO(0, 0, 0, 1, xq=term, gh=usr)
for x in res:
time2 = x[0]
if judgeclasstime(time1, time2) == False:
return 2 # 课时冲突
kh = searchOnC(1, 0, 0, 0, 0, km=cname)[0][0]
if kh is None:
return 3 # 课号不存在
sql = "insert into O values('%s', '%s', '%s', '%s')" % (term, kh, usr, ctime)
flag, e = op_mysql(sql)
if flag:
return 1 # 开课成功
else:
print(e)
return 0 # 数据库意外