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 getCurrentTime():
cursor = get_db().cursor()
if request.method == 'GET':
account = request.args['account']
resource_id = request.args['resource_id']
cursor.execute(
'SELECT resource_id,cur_time FROM learning_progress WHERE account=%s AND resource_id=%s',
(account, resource_id))
data = cursor.fetchall()
current_time = 0
if data:
current_time = data[-1][1]
return jsonify({
'result': 'success',
'resource_id': resource_id,
'current_time': current_time
})
def homeworkTime():
cursor = get_db().cursor()
if request.method == 'POST':
account = request.form['account']
name = request.form['name']
course_id = request.form['course_id']
homework_id = request.form['homework_id']
submit_time = request.form['submit_time']
try:
value = (account, name, course_id, homework_id, submit_time)
cursor.execute(
'INSERT INTO homeworkTime (account,name,course_id,homework_id,submit_time) VALUES'
'(%s,%s,%s,%s,%s)', value)
get_db().commit()
except Exception as e:
get_db().rollback()
return jsonify({'result': 'failed', 'message': '操作失败'})
get_db().close()
return jsonify({'result': 'success', 'message': '操作成功'})
def learning_progress():
cursor = get_db().cursor()
credit_weight = 0.3
if request.method == 'POST':
account = request.form['account']
username = request.form['name']
course_id = request.form['course_id']
unit_id = request.form['unit_id']
resource_id = request.form['resource_id']
resource_type = request.form['resource_type']
#视频实际播放时长
cur_time = request.form['current_time']
#视频总时长
duration = request.form['duration']
#进度最大为1.0
progress = '0'
if not (int(cur_time) and int(duration)):
return jsonify({'result': 'failed', 'message': '操作失败'})
if int(cur_time) / int(duration) < 1.0:
progress = str(round(int(cur_time) / int(duration), 2))
else:
progress = "1.0"
create_time = request.form['create_time']
credit = str(int(duration) * credit_weight)
try:
value = (account, username, course_id, unit_id, resource_id,
resource_type, cur_time, duration, progress, credit,
create_time)
cursor.execute(
'INSERT INTO learning_progress (account,name,course_id,unit_id,resource_id,resource_type,cur_time,duration,progress,credit,create_time) VALUES'
'(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', value
) #我还是把learning_progress表的name字段名字改回来了...因为其他好多写完的语句是用了name，不想改了！
get_db().commit()
except Exception as e:
get_db().rollback()
print(e)
return jsonify({'result': 'failed', 'message': '操作失败'})
get_db().close()
return jsonify({'result': 'success', 'message': '操作完成'})
return '本API只接受POST请求'
def getScore():
cursor = get_db().cursor()
if request.method == 'POST':
accounts = request.form['accounts'].split(',')
course_id = request.form['course_id']
homework_id = request.form['homework_id']
scoreResult = {'result': 'success', 'score': [], 'message': ''}
scores = []
timeList = []
base_w = 50.
susp_const = 2000.
for account in accounts:
progress_fullScore = progress_score = guidance_score = discussion_score = materials_score = 0
#作业提交时间
try:
cursor.execute(
'SELECT submit_time FROM homeworkTime WHERE account=%s AND homework_id=%s',
(account, homework_id))
except Exception as e:
print("作业提交时间查询出错")
print(e)
data = cursor.fetchone()
if data:
submit_time = data[0]
timeList.append({
'account': account,
'submit_time': submit_time
})
score = {'account': account, 'sbt_score': 0., 'act_score': 0.}
#课程进度得分
#（进度*本视频价值）求和
try:
cursor.execute(
'SELECT credit,progress FROM learning_progress'
' WHERE course_id= %s AND account= %s',
(course_id, account))
#这个目前没法直接取到maxProgress，要在插入时直接更新
progress_data = cursor.fetchall()
#progress_data是tuple
for i in progress_data:
progress_score += (float(i[0]) * float(i[1]))
except Exception as e:
print("课程进度得分查询出错，详细信息：")
print(e)
#辅导资料得分
#假定每下载一个+5分
try:
cursor.execute(
'SELECT COUNT(*) FROM download_guidance'
' WHERE account=%s and course_id=%s', (account, course_id))
guidance_data = cursor.fetchall()
if guidance_data:
guidance_score = guidance_data[0][0] * 5.0
except Exception as e:
print('辅导资料得分查询出错，详细信息：')
print(e)
#资源库下载得分
#资源库中的文件每项+5分
try:
cursor.execute(
'SELECT COUNT(DISTINCT materials_id) FROM download_materials'
' WHERE account=%s AND course_id=%s', (account, course_id))
#加了distinct这个参数，重复值就不算
#其实这个加了也没用..因为建表的时候就设定了主键，不会出现重复的...
materials_data = cursor.fetchall()
if materials_data:
materials_score = materials_data[0][0] * 5.0
except Exception as e:
print('资源库得分查询出错，详细信息：')
print(e)
#讨论活跃分
#发起一个新的讨论+5，跟帖+3
try:
cursor.execute(
'SELECT discussion_id,post_id FROM discussion'
' WHERE account=%s AND course_id=%s', (account, course_id))
discussion_data = cursor.fetchall()
for data in discussion_data:
if data[1] == 'NULL':
discussion_score += 5.0
else:
discussion_score += 3.0
#首先检查discussion_id字段，空则为记录时出错；
#跟帖（postid不为空）+1.26，发起帖s's+2.55
except Exception as e:
print('讨论得分查询出错，详细信息：')
print(e)
#活跃分计算完成
#5.7修改：分数只由活跃分构成，作为课程总分的一部分
#计分规则：教师设定4个部分的占比（如50+20+15+15），score=s1*50+s2*20+....
#s1=actualScore/fullScore s2，3，4同
#fullScore1可以直接计算credit和，234是人工设定=-=
try:
cursor.execute(
'SELECT credit FROM learning_progress WHERE account= %s AND course_id= %s',
(account, course_id))
all_credit = cursor.fetchall()
for i in all_credit:
progress_fullScore += float(i[0])
'''cursor.execute('SELECT COUNT(*) FROM guidance WHERE course_id=%s',(course_id,))
all_guidance=cursor.fetchall()
guidance_fullScore=all_guidance[0][0]*5.0'''
'''cursor.execute('SELECT COUNT(*) FROM materials WHERE course_id=%s',(course_id,))
all_materials=cursor.fetchall()
materials_fullScore=all_materials[0][0]*5.0'''
#讨论的总分就直接制定上限了，挺合理的，避免灌水= =..
except:
return '查询出错，账号/课程ID输入错误'
pg_score = progress_score / progress_fullScore if (
progress_fullScore) else 0
active_score = 30 * (
pg_score) + guidance_score + materials_score + discussion_score
score['act_score'] = active_score
scores.append(score)
#按作业提交时间降序排序
#下标越大的元素在以下计算中权值越大
#即越早提交作业的人权值分越多
timeList.sort(key=(lambda x: x['submit_time']))
if timeList:
w = 1
key = {
'submit_time': timeList[0]['submit_time'],
'weight': base_w * (1 / w) #作业提交时间的权值，按[反比例函数(暂定)]递减
}
for i in timeList:
if i['submit_time'] > key['submit_time']:
#若当前i和key作业提交时间相同，视为同一层次，否则更新到下一个层次
w += 1
key['submit_time'] = i['submit_time']
key['weight'] = base_w * (1 / math.sqrt(w))
for j in scores:
if i['account'] == j['account']:
j['sbt_score'] = key['weight'] #永远获取当前层次的权值分
new_score = []
for item in scores:
total_score = 0.
if item['sbt_score'] + item['act_score']:
#计算总嫌疑值
total_score = round(
susp_const / (item['sbt_score'] + item['act_score']), 2)
new_score.append({
'account': item['account'],
'suspicionValue': total_score
})
scoreResult['score'] = new_score
return jsonify(scoreResult)
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)