def materials():
cursor = get_db().cursor()
if request.method == 'POST':
course_id = request.form['course_id']
materials_id = request.form['materials_id']
materials_name = request.form['materials_name']
try:
value = [course_id, materials_id, materials_name]
cursor.execute(
'SELECT COUNT(*) FROM materials WHERE materials_id=%s',
materials_id)
isExist = cursor.fetchall()
if isExist[0][0] != None:
if isExist[0][0] == 0:
cursor.execute(
'INSERT INTO materials (course_id,materials_id,materials_name)'
' VALUES(%s,%s,%s)', value)
else:
return jsonify('记录已存在')
else:
return jsonify('非法数据')
get_db().commit()
except:
get_db().rollback()
return jsonify('数据插入失败')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def guidance():
cursor = get_db().cursor()
if request.method == 'POST':
course_id = request.form['course_id']
unit_id = request.form['unit_id']
guidance_id = request.form['guidance_id']
guidance_name = request.form['guidance_name']
try:
value = [course_id, unit_id, guidance_id, guidance_name]
#检查本unit是否已有辅导资料
cursor.execute(
'SELECT COUNT(*) FROM guidance WHERE course_id=%s AND unit_id=%s',
(course_id, unit_id))
tempVal = cursor.fetchall()
if tempVal[0][0] != None:
if tempVal[0][0] == 0:
cursor.execute(
'INSERT INTO guidance (course_id,unit_id,guidance_id,guidance_name)'
' VALUES (%s,%s,%s,%s)', value)
else:
cursor.execute(
'UPDATE guidance SET guidance_id=%s,guidance_name=%s'
' WHERE course_id=%s AND unit_id=%s',
(guidance_id, guidance_name, course_id, unit_id))
else:
return jsonify('非法数据')
get_db().commit()
except Exception as e:
get_db().rollback()
return jsonify('数据插入失败')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def download_guidance():
cursor = get_db().cursor()
if request.method == 'POST':
account = request.form['account']
name = request.form['name']
course_id = request.form['course_id']
unit_id = request.form['unit_id']
guidance_id = request.form['guidance_id']
guidance_name = request.form['guidance_name']
create_time = request.form['create_time']
try:
value = [
account, name, course_id, unit_id, guidance_id, guidance_name,
create_time
]
cursor.execute(
'INSERT INTO download_guidance'
' VALUES (%s,%s,%s,%s,%s,%s,%s)', value)
get_db().commit()
except Exception as e:
get_db().rollback()
return jsonify('数据插入失败，此条已记录')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def resource():
cursor = get_db().cursor()
if request.method == 'POST':
course_id = request.form['course_id']
unit_id = request.form['unit_id']
unit_name = request.form['unit_name']
resource_id = request.form['resource_id']
resource_type = request.form['resource_type']
try:
value = [course_id, unit_id, unit_name, resource_id, resource_type]
cursor.execute(
'SELECT COUNT(*) FROM resource WHERE unit_id=%s and resource_type=%s ',
(unit_id, resource_type))
isExist = cursor.fetchall()
if isExist != None:
if isExist[0][0] == 0:
cursor.execute(
'INSERT INTO resource (course_id,unit_id,unit_name,resource_id,resource_type)'
'VALUES(%s,%s,%s,%s,%s)', value)
else:
cursor.execute(
'UPDATE resource SET resource_id=%s WHERE unit_id=%s AND resource_type=%s ',
(resource_id, unit_id, resource_type))
else:
return jsonify('非法数据')
get_db().commit()
except:
get_db().rollback()
return jsonify('数据插入失败')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def discussion():
cursor = get_db().cursor()
if request.method == 'POST':
account = request.form['account']
name = request.form['name']
course_id = request.form['course_id']
discussion_id = request.form['discussion_id']
post_id = request.form['post_id']
content = request.form['content']
create_time = request.form['create_time']
#重要提醒：如果是一个这是发起帖，前端构造requestBody的时候要把post_id设置成'NULL'
#补充：没有对重复帖子做过滤，但是前端控制好正常也不会有重复的情况出现吧。。
try:
value = [
account, name, course_id, discussion_id, post_id, content,
create_time
]
cursor.execute(
'INSERT INTO discussion (account,name,course_id,discussion_id,post_id,content,create_time)'
' VALUES(%s,%s,%s,%s,%s,%s,%s)', value)
get_db().commit()
except:
get_db().rollback()
return jsonify('数据插入失败')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def com_gui():
course_id = request.form['course_id']
cursor = get_db().cursor()
res_gui = {'result': 'success', 'guidance': []}
cursor.execute(
'SELECT guidance_id,guidance_name FROM guidance'
' WHERE course_id=%s', course_id)
get_db().commit()
guidance = cursor.fetchall()
for i in guidance:
each_gui = {'guidance_id': i[0], 'guidance_name': i[1]}
res_gui['guidance'].append(each_gui)
close_db()
return jsonify(res_gui)
def com_mat():
course_id = request.form['course_id']
cursor = get_db().cursor()
res_mat = {'result': 'success', 'materials': []}
cursor.execute(
'SELECT materials_id,materials_name FROM materials'
' WHERE course_id=%s', course_id)
get_db().commit()
materials = cursor.fetchall()
for i in materials:
each_mat = {'materials_id': i[0], 'materials_name': i[1]}
res_mat['materials'].append(each_mat)
close_db()
return jsonify(res_mat)
def det_guidance():
account = request.form['account']
course_id = request.form['course_id']
guidance_det = {'result': 'success', 'guidance_det': []}
cursor = get_db().cursor()
try:
cursor.execute(
'SELECT guidance_id,guidance_name FROM download_guidance'
' WHERE account=%s AND course_id=%s', (account, course_id))
guidance_down = cursor.fetchall()
for i in guidance_down:
each_gui = {'guidance_id': i[0], 'guidance_name': i[1]}
guidance_det['guidance_det'].append(each_gui)
except Exception as e:
raise
get_db().commit()
close_db()
return jsonify(guidance_det)
def det_materials():
account = request.form['account']
course_id = request.form['course_id']
materials_det = {'result': 'success', 'materials_det': []}
cursor = get_db().cursor()
try:
cursor.execute(
'SELECT materials_id,materials_name FROM download_materials'
' WHERE account=%s AND course_id=%s', (account, course_id))
materials_down = cursor.fetchall()
for i in materials_down:
each_mat = {'materials_id': i[0], 'materials_name': i[1]}
materials_det['materials_det'].append(each_mat)
except Exception as e:
raise
get_db().commit()
close_db()
return jsonify(materials_det)
def download_materials():
cursor = get_db().cursor()
if request.method == 'POST':
account = request.form['account']
name = request.form['name']
course_id = request.form['course_id']
materials_id = request.form['materials_id']
materials_name = request.form['materials_name']
create_time = request.form['create_time']
try:
value = [
account, name, course_id, materials_id, materials_name,
create_time
]
cursor.execute(
'SELECT COUNT(*) FROM download_materials WHERE account=%s AND materials_id=%s',
(account, materials_id))
isExist = cursor.fetchall()
#首先检查是否存在这个人对这个material的下载记录
if isExist[0][0] != None:
if isExist[0][0] == 0:
cursor.execute(
'INSERT INTO download_materials (account,name,course_id,materials_id,materials_name,create_time)'
' VALUES(%s,%s,%s,%s,%s,%s)', value)
else:
return jsonify('记录已存在')
else:
return jsonify('非法数据')
#如果是none，是查询出错，可能是非法数据引起的；
#如果是0就说明这是一条新纪录，执行insert操作；非0则说明已有记录
#下面的接口也是类似这样的处理
get_db().commit()
except:
get_db().rollback()
return jsonify('数据插入失败')
close_db()
return jsonify('操作完成')
return '本API只接受POST请求'
def allFactors():
course_id=request.form['course_id']
account=request.form['account']
homework_id=request.form['homework_id']
list_acc=account.split(",")
cursor=get_db().cursor()
factors_response={
'result':'success',
'progress_list':[],
'others_list':[],
'homework_list':[]
}
for i in list_acc:
each_progress={
'account' : i,
'name' : "",
'progress' : ""
}
each_others={
'account' : i,
'name': "",
'discussionCnt': 0,
'guidanceCnt': 0,
'materialsCnt': 0
}#额..突然发现这个取名有点奇怪each others...本意是每一个人的otherFactors...
each_homework={
'account': i,
'name': "",
'submitTime': ""
}
try:
#part1 计算总的unit个数
cursor.execute(
'SELECT COUNT(DISTINCT unit_id) FROM learning_progress'
' WHERE account=%s AND course_id=%s',(i,course_id)
)
totalUnit=cursor.fetchall()
totalUnit=totalUnit[0][0]
if totalUnit==0:
factors_response['progress_list'].append(each_progress)
factors_response['others_list'].append(each_others)
factors_response['homework_list'].append(each_homework)
continue
#使用continue可以跳出本次循环，break则会结束整个for
#这是为了避免因为帐号不存在而下面除以0报错
#part2 计算每个unit的进度之和
#这里为了取name用了个骚办法...因为group by unit_id了不能直接取name，
#所以用了min让它处于unit_id的作用域内
cursor.execute(
'SELECT MIN(name),unit_id,MAX(progress) FROM learning_progress'
' WHERE account=%s AND course_id=%s'
' GROUP BY unit_id',(i,course_id)
)
linshigong=cursor.fetchall()
totalProgress=0
for j in linshigong:
each_progress['name']=j[0]
totalProgress+=float(j[2])
#part3 总和/总个数=平均课程进度，然后添加到response中
finalProgress=totalProgress/totalUnit
each_progress['progress']=finalProgress
factors_response['progress_list'].append(each_progress)
except Exception as e:
print(e)
try:
#part0 获取名字 这里应该在learning_progress表取，
#因为一个人如果极端一点..可能会没有参与讨论没有下载资料...但是一定会有学习进度...
cursor.execute(
'SELECT DISTINCT name FROM learning_progress'
' WHERE account=%s AND course_id=%s',(i,course_id)
)
yourname=cursor.fetchall()
if yourname==():
factors_response['others_list'].append(each_others)
continue
else:
each_others['name']=yourname[0][0]
#part1 获取讨论次数
cursor.execute(
'SELECT COUNT(DISTINCT discussion_id) FROM discussion'
' WHERE account=%s AND course_id=%s',(i,course_id)
)
discussionCnt=cursor.fetchall()
each_others['discussionCnt']=discussionCnt[0][0]
#part2 获取guidance下载次数
cursor.execute(
'SELECT COUNT(DISTINCT guidance_id) FROM download_guidance'
' WHERE account=%s AND course_id=%s',(i,course_id)
)
guidanceCnt=cursor.fetchall()
each_others['guidanceCnt']=guidanceCnt[0][0]
#part3 获取materials下载次数
cursor.execute(
'SELECT COUNT(DISTINCT materials_id) FROM download_materials'
' WHERE account=%s AND course_id=%s',(i,course_id)
)
materialsCnt=cursor.fetchall()
each_others['materialsCnt']=materialsCnt[0][0]
factors_response['others_list'].append(each_others)
except Exception as e:
print(e)
try:
cursor.execute(
'SELECT name,submit_time FROM homeworkTime'
' WHERE account=%s AND homework_id=%s',(i,homework_id)
)
homework=cursor.fetchall()
each_homework['name']=homework[0][0]
each_homework['submitTime']=str(homework[0][1])
factors_response['homework_list'].append(each_homework)
except Exception as e:
print(e)
get_db().commit()
close_db()
return jsonify(factors_response)
def listAll():
account = request.form['account']
course_id = request.form['course_id']
homework_id = request.form['homework_id']
cursor = get_db().cursor()
listAll_res = {
'result': "success",
'userId': "",
'userName': "",
'individualBehavior': {
'submitTime': "",
'progress': [],
'discussionCnt': [],
'materialsCnt': [],
'guidanceCnt': [],
'discussionRecord': []
}
}
# 首先验证有没有输错，如果没有记录就结束本次查询
try:
cursor.execute(
'SELECT COUNT(*) FROM learning_progress'
' WHERE account=%s AND course_id=%s', (account, course_id))
aaa = cursor.fetchall()
if aaa[0][0] == 0:
return jsonify(listAll_res)
except Exception as e:
raise
#获取作业提交时间
try:
cursor.execute(
'SELECT submit_time FROM homeworkTime'
' WHERE account=%s AND homework_id=%s', (account, homework_id))
submitTime = cursor.fetchall()
if submitTime == ():
submitTime = "ERROR"
else:
submitTime = str(submitTime[0][0])
listAll_res['individualBehavior']['submitTime'] = submitTime
except Exception as e:
raise
#资源库下载次数
try:
cursor.execute(
'SELECT COUNT(materials_id),create_time FROM download_materials'
' WHERE account=%s AND course_id=%s'
' GROUP BY create_time', (account, course_id))
aaa = cursor.fetchall()
templistA = []
templistB = []
for i in aaa:
axiba = {'count': i[0], 'date': str(i[1].date())}
templistA.append(axiba)
for data in templistA:
templistB.append(data['date'])
tempDict = dict(Counter(templistB))
for (key, value) in tempDict.items():
zhongjie = dict()
zhongjie['count'] = value
zhongjie['date'] = key
listAll_res['individualBehavior']['materialsCnt'].append(zhongjie)
except Exception as e:
raise
#辅导材料下载次数
try:
cursor.execute(
'SELECT COUNT(guidance_id),create_time FROM download_guidance'
' WHERE account=%s AND course_id=%s'
' GROUP BY create_time', (account, course_id))
aaa = cursor.fetchall()
templistA = []
templistB = []
for i in aaa:
jiuminga = {'count': i[0], 'date': str(i[1].date())}
templistA.append(jiuminga)
for data in templistA:
templistB.append(data['date'])
tempDict = dict(Counter(templistB))
for (key, value) in tempDict.items():
zhongjie = dict()
zhongjie['count'] = value
zhongjie['date'] = key
listAll_res['individualBehavior']['guidanceCnt'].append(zhongjie)
except Exception as e:
raise
#讨论参与次数
try:
#通过这次开发，感觉写sql语句的能力得到了不少锻炼...顺便发掘了一下format的用法
#5.13：发掘个屁= =...会报错...这谁顶得住啊
cursor.execute(
'SELECT COUNT(DISTINCT discussion_id),create_time FROM discussion'
' WHERE account=%s AND course_id=%s'
' GROUP BY create_time', (account, course_id))
aaa = cursor.fetchall()
templistA = []
templistB = []
for i in aaa:
yaolewoba = {'count': i[0], 'date': str(i[1].date())}
templistA.append(yaolewoba)
for data in templistA:
templistB.append(data['date'])
tempDict = dict(Counter(templistB))
for (key, value) in tempDict.items():
zhongjie = dict()
zhongjie['count'] = value
zhongjie['date'] = key
listAll_res['individualBehavior']['discussionCnt'].append(zhongjie)
except Exception as e:
raise
#列出讨论的详细记录
try:
cursor.execute(
'SELECT post_id,content,create_time FROM discussion'
' WHERE account=%s AND course_id=%s', (account, course_id))
aaa = cursor.fetchall()
if aaa == ():
listAll_res['individualBehavior']['discussionRecord'].append(
'没有讨论记录')
else:
for i in aaa:
xinqingbianhao = {
'type': "",
'time': str(i[2].date()),
'content': i[1],
'zhu_conten': ""
}
if i[0] == 'NULL':
xinqingbianhao['type'] = "发起讨论"
else:
xinqingbianhao['type'] = '回复'
cursor.execute('SELECT DISTINCT content FROM discussion'
' WHERE discussion_id=%s',
i[0]) #为什么这里在试图用{}.format的时候会报错？暂时不管...
bbb = cursor.fetchall()
xinqingbianhao['zhu_conten'] = bbb[0][0]
listAll_res['individualBehavior']['discussionRecord'].append(
xinqingbianhao)
except Exception as e:
raise
#获取在这门课程的所有章节的目前进度
try:
cursor.execute(
'SELECT MIN(learning_progress.name),MIN(resource.unit_name),MAX(learning_progress.progress)'
' FROM learning_progress,resource'
' WHERE learning_progress.resource_id=resource.resource_id'
' AND learning_progress.account=%s AND learning_progress.course_id=%s'
' GROUP BY learning_progress.unit_id',
(account, course_id)) #又采用了group by之后用min取名字的写法...先不管了，先能用再说..
aaa = cursor.fetchall()
for i in aaa:
kuaiwanle = {'unit_name': i[1], 'progress': i[2]}
listAll_res['individualBehavior']['progress'].append(kuaiwanle)
except Exception as e:
raise
get_db().commit()
close_db()
return jsonify(listAll_res)