目 录
摘 要 I
Abstract II
第1章 绪论 1
1.1 研究背景及意义 1
1.1.1 研究背景 1
1.1.2 研究意义 1
1.2 国内外研究现状及发展趋势 2
1.2.1 国内研究现状 2
1.2.2 国外研究现状 2
1.2.3 发展趋势 3
1.3 研究内容及论文结构 3
1.3.1 研究内容 3
1.3.2 论文结构 4
第2章 相关理论与技术基础 5
2.1 知识图谱 5
2.2 学习路径推荐相关技术 5
2.3 个性化推荐算法 5
2.4 系统开发与实现关键技术 6
第3章 系统分析 7
3.1 总体需求分析 7
3.2 功能需求分析 7
3.2.1学生数据采集模块功能需求分析 7
3.2.2在线课程管理模块功能需求分析 8
3.2.3个性化学习路径推荐模块功能需求分析 9
3.3 基于知识图谱的非功能需求分析 9
3.4 基于知识图谱的系统可行性分析 10
第4章 系统设计 11
4.1 系统功能设计 11
4.1.1 用户模块设计 11
4.1.2 用户学习路径采集模块设计 12
4.1.3 个性化学习路径推荐模块设计 13
4.1.4 后台管理模块设计 14
4.2 数据库设计 15
4.2.1 数据库概念设计 15
4.2.2 数据表设计 16
第5章 系统实现 19
5.1 系统开发环境 19
5.2 用户模块实现 19
5.2.1 注册 19
5.2.2 登录 21
5.2.3 查看/修改个人信息 23
5.2.4 修改密码 25
5.3 用户学习路径采集模块实现 25
5.3.1 数据预处理 25
5.3.2 相似度计算 28
5.4 个性化学习路径推荐模块实现 29
5.5 后台管理模块实现 30
5.5.1 登录 30
5.5.2 管理个人/用户信息 31
5.5.3 学习课程管理 32
5.5.4 用户学习路径管理 32
5.5.5 学习课程推荐管理 32
第6章 系统测试 34
6.1 系统测试环境 34
6.2 用户模块测试 34
6.3 用户学习路径采集模块测试 35
6.4 个性化学习路径推荐模块测试 35
6.5 后台管理模块测试 36
第7章 总结与展望 38
参考文献 39
致 谢 40
1.3 研究内容及论文结构
1.3.1 研究内容
本研究旨在设计并实现一个面向本科教育的在线课程推荐系统，该系统基于知识图谱技术，旨在为学生提供个性化的学习路径推荐。主要研究内容包括：
（1）知识图谱的构建：通过对在线教育平台中的课程资源进行梳理和分析，提取知识点之间的关联关系，构建结构化的知识图谱。
（2）学习路径的采集与分析：通过收集学生在平台上的学习行为数据，分析学生的学习习惯、兴趣偏好以及知识掌握情况，从而确定学生的学习路径。
（3）个性化推荐算法的设计：基于知识图谱和学习路径分析，设计能够精准匹配学生需求的个性化推荐算法，为学生提供符合其学习路径和兴趣的课程推荐。
（4）系统的开发与实现：利用MySQL作为后台数据库，HTML技术实现系统前端页面的设计，结合Python后台技术和Django框架，完成整个系统的界面显示和数据交互。
（5）系统测试与评估：对系统进行功能测试和性能测试，确保系统的稳定性和可靠性；同时，通过用户调研和数据分析，评估系统的推荐效果和用户满意度。
1.3.2 论文结构
本论文将按照以下结构进行组织：
第一章为绪论，介绍研究的背景、意义、国内外研究现状及发展趋势，以及研究内容和论文结构。
第二章为相关理论与技术基础，介绍知识图谱、个性化推荐算法以及系统开发与实现所需的关键技术。
第三章为系统需求分析，对目标用户群体进行分析，明确系统的功能需求和非功能需求。
第四章为系统设计，包括系统整体架构设计、数据库设计、界面设计以及推荐算法设计等内容。
第五章为系统实现与测试，详细描述系统的开发过程、实现细节以及测试方法和结果。
第六章为系统评估与效果分析，通过用户调研和数据分析，评估系统的推荐效果和用户满意度。
第七章为结论与展望，总结研究成果和贡献，指出研究中存在的不足和未来的改进方向。
通过以上章节的展开，本论文将全面阐述基于知识图谱的个性化学习路径推荐系统的研究过程、实现方法以及评估效果，为相关领域的研究和实践提供有益的参考和借鉴。
#coding:utf-8
__author__ = "ila"
import base64, copy, logging, os, sys, time, xlrd, json, datetime, configparser
from django.http import JsonResponse
from django.apps import apps
from django.db.models.aggregates import Count,Sum
from .models import yonghu
from util.codes import *
from util.auth import Auth
from util.common import Common
import util.message as mes
from django.db import connection
import random
from django.core.mail import send_mail
from django.conf import settings
from django.shortcuts import redirect
from django.db.models import Q
from util.baidubce_api import BaiDuBce
from .config_model import config
def yonghu_register(request):
if request.method in ["POST", "GET"]:
msg = {'code': normal_code, "msg": mes.normal_code}
req_dict = request.session.get("req_dict")
error = yonghu.createbyreq(yonghu, yonghu, req_dict)
if error != None:
msg['code'] = crud_error_code
msg['msg'] = "用户已存在,请勿重复注册!"
return JsonResponse(msg)
def yonghu_login(request):
if request.method in ["POST", "GET"]:
msg = {'code': normal_code, "msg": mes.normal_code}
req_dict = request.session.get("req_dict")
datas = yonghu.getbyparams(yonghu, yonghu, req_dict)
if not datas:
msg['code'] = password_error_code
msg['msg'] = mes.password_error_code
return JsonResponse(msg)
try:
__sfsh__= yonghu.__sfsh__
except:
__sfsh__=None
if __sfsh__=='是':
if datas[0].get('sfsh')!='是':
msg['code']=other_code
msg['msg'] = "账号已锁定，请联系管理员审核!"
return JsonResponse(msg)
req_dict['id'] = datas[0].get('id')
return Auth.authenticate(Auth, yonghu, req_dict)
def yonghu_logout(request):
if request.method in ["POST", "GET"]:
msg = {
"msg": "登出成功",
"code": 0
}
return JsonResponse(msg)
def yonghu_resetPass(request):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code}
req_dict = request.session.get("req_dict")
columns= yonghu.getallcolumn( yonghu, yonghu)
try:
__loginUserColumn__= yonghu.__loginUserColumn__
except:
__loginUserColumn__=None
username=req_dict.get(list(req_dict.keys())[0])
if __loginUserColumn__:
username_str=__loginUserColumn__
else:
username_str=username
if 'mima' in columns:
password_str='mima'
else:
password_str='password'
init_pwd = '123456'
recordsParam = {}
recordsParam[username_str] = req_dict.get("username")
records=yonghu.getbyparams(yonghu, yonghu, recordsParam)
if len(records)<1:
msg['code'] = 400
msg['msg'] = '用户不存在'
return JsonResponse(msg)
eval('''yonghu.objects.filter({}='{}').update({}='{}')'''.format(username_str,username,password_str,init_pwd))
return JsonResponse(msg)
def yonghu_session(request):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code,"msg": mes.normal_code, "data": {}}
req_dict={"id":request.session.get('params').get("id")}
msg['data'] = yonghu.getbyparams(yonghu, yonghu, req_dict)[0]
return JsonResponse(msg)
def yonghu_default(request):
if request.method in ["POST", "GET"]:
msg = {"code": normal_code,"msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
req_dict.update({"isdefault":"是"})
data=yonghu.getbyparams(yonghu, yonghu, req_dict)
if len(data)>0:
msg['data'] = data[0]
else:
msg['data'] = {}
return JsonResponse(msg)
def yonghu_page(request):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data":{"currPage":1,"totalPage":1,"total":1,"pageSize":10,"list":[]}}
req_dict = request.session.get("req_dict")
#获取全部列名
columns= yonghu.getallcolumn( yonghu, yonghu)
#当前登录用户所在表
tablename = request.session.get("tablename")
#authColumn=list(__authTables__.keys())[0]
#authTable=__authTables__.get(authColumn)
# if authTable==tablename:
#params = request.session.get("params")
#req_dict[authColumn]=params.get(authColumn)
'''__authSeparate__此属性为真，params添加userid，后台只查询个人数据'''
try:
__authSeparate__=yonghu.__authSeparate__
except:
__authSeparate__=None
if __authSeparate__=="是":
tablename=request.session.get("tablename")
if tablename!="users" and 'userid' in columns:
try:
req_dict['userid']=request.session.get("params").get("id")
except:
pass
#当项目属性hasMessage为”是”，生成系统自动生成留言板的表messages，同时该表的表属性hasMessage也被设置为”是”,字段包括userid（用户id），username(用户名)，content（留言内容），reply（回复）
#接口page需要区分权限，普通用户查看自己的留言和回复记录，管理员查看所有的留言和回复记录
try:
__hasMessage__=yonghu.__hasMessage__
except:
__hasMessage__=None
if __hasMessage__=="是":
tablename=request.session.get("tablename")
if tablename!="users":
req_dict["userid"]=request.session.get("params").get("id")
# 判断当前表的表属性isAdmin,为真则是管理员表
# 当表属性isAdmin=”是”,刷出来的用户表也是管理员，即page和list可以查看所有人的考试记录(同时应用于其他表)
__isAdmin__ = None
allModels = apps.get_app_config('main').get_models()
for m in allModels:
if m.__tablename__==tablename:
try:
__isAdmin__ = m.__isAdmin__
except:
__isAdmin__ = None
break
# 当前表也是有管理员权限的表
if __isAdmin__ == "是" and 'yonghu' != 'forum':
if req_dict.get("userid") and 'yonghu' != 'chat':
del req_dict["userid"]
else:
#非管理员权限的表,判断当前表字段名是否有userid
if tablename!="users" and 'yonghu'[:7]!='discuss'and "userid" in yonghu.getallcolumn(yonghu,yonghu):
req_dict["userid"] = request.session.get("params").get("id")
#当列属性authTable有值(某个用户表)[该列的列名必须和该用户表的登陆字段名一致]，则对应的表有个隐藏属性authTable为”是”，那么该用户查看该表信息时，只能查看自己的
try:
__authTables__=yonghu.__authTables__
except:
__authTables__=None
if __authTables__!=None and __authTables__!={}:
try:
del req_dict['userid']
# tablename=request.session.get("tablename")
# if tablename=="users":
# del req_dict['userid']
except:
pass
for authColumn,authTable in __authTables__.items():
if authTable==tablename:
params = request.session.get("params")
req_dict[authColumn]=params.get(authColumn)
username=params.get(authColumn)
break
q = Q()
msg['data']['list'], msg['data']['currPage'], msg['data']['totalPage'], msg['data']['total'], \
msg['data']['pageSize'] =yonghu.page(yonghu, yonghu, req_dict, request, q)
return JsonResponse(msg)
def yonghu_autoSort(request):
'''
．智能推荐功能(表属性：[intelRecom（是/否）],新增clicktime[前端不显示该字段]字段（调用info/detail接口的时候更新），按clicktime排序查询)
主要信息列表（如商品列表，新闻列表）中使用，显示最近点击的或最新添加的5条记录就行
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data":{"currPage":1,"totalPage":1,"total":1,"pageSize":10,"list":[]}}
req_dict = request.session.get("req_dict")
if "clicknum" in yonghu.getallcolumn(yonghu,yonghu):
req_dict['sort']='clicknum'
elif "browseduration" in yonghu.getallcolumn(yonghu,yonghu):
req_dict['sort']='browseduration'
else:
req_dict['sort']='clicktime'
req_dict['order']='desc'
msg['data']['list'], msg['data']['currPage'], msg['data']['totalPage'], msg['data']['total'], \
msg['data']['pageSize'] = yonghu.page(yonghu,yonghu, req_dict)
return JsonResponse(msg)
def yonghu_list(request):
'''
前台分页
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data":{"currPage":1,"totalPage":1,"total":1,"pageSize":10,"list":[]}}
req_dict = request.session.get("req_dict")
if req_dict.__contains__('vipread'):
del req_dict['vipread']
#获取全部列名
columns= yonghu.getallcolumn( yonghu, yonghu)
#表属性[foreEndList]前台list:和后台默认的list列表页相似,只是摆在前台,否:指没有此页,是:表示有此页(不需要登陆即可查看),前要登:表示有此页且需要登陆后才能查看
try:
__foreEndList__=yonghu.__foreEndList__
except:
__foreEndList__=None
if __foreEndList__=="前要登":
tablename=request.session.get("tablename")
if tablename!="users" and 'userid' in columns:
try:
req_dict['userid']=request.session.get("params").get("id")
except:
pass
#forrEndListAuth
try:
__foreEndListAuth__=yonghu.__foreEndListAuth__
except:
__foreEndListAuth__=None
#authSeparate
try:
__authSeparate__=yonghu.__authSeparate__
except:
__authSeparate__=None
if __foreEndListAuth__ =="是" and __authSeparate__=="是":
tablename=request.session.get("tablename")
if tablename!="users":
req_dict['userid']=request.session.get("params",{"id":0}).get("id")
tablename = request.session.get("tablename")
if tablename == "users" and req_dict.get("userid") != None:#判断是否存在userid列名
del req_dict["userid"]
else:
__isAdmin__ = None
allModels = apps.get_app_config('main').get_models()
for m in allModels:
if m.__tablename__==tablename:
try:
__isAdmin__ = m.__isAdmin__
except:
__isAdmin__ = None
break
if __isAdmin__ == "是":
if req_dict.get("userid"):
# del req_dict["userid"]
pass
else:
#非管理员权限的表,判断当前表字段名是否有userid
if "userid" in columns:
try:
pass
except:
pass
#当列属性authTable有值(某个用户表)[该列的列名必须和该用户表的登陆字段名一致]，则对应的表有个隐藏属性authTable为”是”，那么该用户查看该表信息时，只能查看自己的
try:
__authTables__=yonghu.__authTables__
except:
__authTables__=None
if __authTables__!=None and __authTables__!={} and __foreEndListAuth__=="是":
try:
del req_dict['userid']
except:
pass
for authColumn,authTable in __authTables__.items():
if authTable==tablename:
params = request.session.get("params")
req_dict[authColumn]=params.get(authColumn)
username=params.get(authColumn)
break
if yonghu.__tablename__[:7]=="discuss":
try:
del req_dict['userid']
except:
pass
q = Q()
msg['data']['list'], msg['data']['currPage'], msg['data']['totalPage'], msg['data']['total'], \
msg['data']['pageSize'] = yonghu.page(yonghu, yonghu, req_dict, request, q)
return JsonResponse(msg)
def yonghu_save(request):
'''
后台新增
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
if 'clicktime' in req_dict.keys():
del req_dict['clicktime']
tablename=request.session.get("tablename")
__isAdmin__ = None
allModels = apps.get_app_config('main').get_models()
for m in allModels:
if m.__tablename__==tablename:
try:
__isAdmin__ = m.__isAdmin__
except:
__isAdmin__ = None
break
#获取全部列名
columns= yonghu.getallcolumn( yonghu, yonghu)
if tablename!='users' and req_dict.get("userid")!=None and 'userid' in columns and __isAdmin__!='是':
params=request.session.get("params")
req_dict['userid']=params.get('id')
if 'addtime' in req_dict.keys():
del req_dict['addtime']
error= yonghu.createbyreq(yonghu,yonghu, req_dict)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_add(request):
'''
前台新增
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
#获取全部列名
columns= yonghu.getallcolumn( yonghu, yonghu)
try:
__authSeparate__=yonghu.__authSeparate__
except:
__authSeparate__=None
if __authSeparate__=="是":
tablename=request.session.get("tablename")
if tablename!="users" and 'userid' in columns:
try:
req_dict['userid']=request.session.get("params").get("id")
except:
pass
try:
__foreEndListAuth__=yonghu.__foreEndListAuth__
except:
__foreEndListAuth__=None
if __foreEndListAuth__ and __foreEndListAuth__!="否":
tablename=request.session.get("tablename")
if tablename!="users":
req_dict['userid']=request.session.get("params").get("id")
if 'addtime' in req_dict.keys():
del req_dict['addtime']
error= yonghu.createbyreq(yonghu,yonghu, req_dict)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_thumbsup(request,id_):
'''
点赞：表属性thumbsUp[是/否]，刷表新增thumbsupnum赞和crazilynum踩字段，
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
id_=int(id_)
type_=int(req_dict.get("type",0))
rets=yonghu.getbyid(yonghu,yonghu,id_)
update_dict={
"id":id_,
}
if type_==1:#赞
update_dict["thumbsupnum"]=int(rets[0].get('thumbsupnum'))+1
elif type_==2:#踩
update_dict["crazilynum"]=int(rets[0].get('crazilynum'))+1
error = yonghu.updatebyparams(yonghu,yonghu, update_dict)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_info(request,id_):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
data = yonghu.getbyid(yonghu,yonghu, int(id_))
if len(data)>0:
msg['data']=data[0]
if msg['data'].__contains__("reversetime"):
msg['data']['reversetime'] = msg['data']['reversetime'].strftime("%Y-%m-%d %H:%M:%S")
#浏览点击次数
try:
__browseClick__= yonghu.__browseClick__
except:
__browseClick__=None
if __browseClick__=="是" and "clicknum" in yonghu.getallcolumn(yonghu,yonghu):
try:
clicknum=int(data[0].get("clicknum",0))+1
except:
clicknum=0+1
click_dict={"id":int(id_),"clicknum":clicknum}
ret=yonghu.updatebyparams(yonghu,yonghu,click_dict)
if ret!=None:
msg['code'] = crud_error_code
msg['msg'] = ret
return JsonResponse(msg)
def yonghu_detail(request,id_):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
data =yonghu.getbyid(yonghu,yonghu, int(id_))
if len(data)>0:
msg['data']=data[0]
if msg['data'].__contains__("reversetime"):
msg['data']['reversetime'] = msg['data']['reversetime'].strftime("%Y-%m-%d %H:%M:%S")
#浏览点击次数
try:
__browseClick__= yonghu.__browseClick__
except:
__browseClick__=None
if __browseClick__=="是" and "clicknum" in yonghu.getallcolumn(yonghu,yonghu):
try:
clicknum=int(data[0].get("clicknum",0))+1
except:
clicknum=0+1
click_dict={"id":int(id_),"clicknum":clicknum}
ret=yonghu.updatebyparams(yonghu,yonghu,click_dict)
if ret!=None:
msg['code'] = crud_error_code
msg['msg'] = retfo
return JsonResponse(msg)
def yonghu_update(request):
'''
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
if req_dict.get("mima") and "mima" not in yonghu.getallcolumn(yonghu,yonghu) :
del req_dict["mima"]
if req_dict.get("password") and "password" not in yonghu.getallcolumn(yonghu,yonghu) :
del req_dict["password"]
try:
del req_dict["clicknum"]
except:
pass
error = yonghu.updatebyparams(yonghu, yonghu, req_dict)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_delete(request):
'''
批量删除
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code, "data": {}}
req_dict = request.session.get("req_dict")
error=yonghu.deletes(yonghu,
yonghu,
req_dict.get("ids")
)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_vote(request,id_):
'''
浏览点击次数（表属性[browseClick:是/否]，点击字段（clicknum），调用info/detail接口的时候后端自动+1）、投票功能（表属性[vote:是/否]，投票字段（votenum）,调用vote接口后端votenum+1）
统计商品或新闻的点击次数；提供新闻的投票功能
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": mes.normal_code}
data= yonghu.getbyid(yonghu, yonghu, int(id_))
for i in data:
votenum=i.get('votenum')
if votenum!=None:
params={"id":int(id_),"votenum":votenum+1}
error=yonghu.updatebyparams(yonghu,yonghu,params)
if error!=None:
msg['code'] = crud_error_code
msg['msg'] = error
return JsonResponse(msg)
def yonghu_importExcel(request):
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
excel_file = request.FILES.get("file", "")
file_type = excel_file.name.split('.')[1]
if file_type in ['xlsx', 'xls']:
data = xlrd.open_workbook(filename=None, file_contents=excel_file.read())
table = data.sheets()[0]
rows = table.nrows
try:
for row in range(1, rows):
row_values = table.row_values(row)
req_dict = {}
yonghu.createbyreq(yonghu, yonghu, req_dict)
except:
pass
else:
msg = {
"msg": "文件类型错误",
"code": 500
}
return JsonResponse(msg)
def yonghu_sendemail(request):
if request.method in ["POST", "GET"]:
req_dict = request.session.get("req_dict")
code = random.sample(['0', '1', '2', '3', '4', '5', '6', '7', '8', '9'], 4)
to = []
to.append(req_dict['email'])
send_mail('用户注册', '您的注册验证码是【'+''.join(code)+'】，请不要把验证码泄漏给其他人，如非本人请勿操作。', 'yclw9@qq.com', to, fail_silently = False)
cursor = connection.cursor()
cursor.execute("insert into emailregistercode(email,role,code) values('"+req_dict['email']+"','用户','"+''.join(code)+"')")
msg = {
"msg": "发送成功",
"code": 0
}
return JsonResponse(msg)
# 推荐算法接口
def yonghu_autoSort2(request):
if request.method in ["POST", "GET"]:
req_dict = request.session.get("req_dict")
cursor = connection.cursor()
leixing = set()
try:
cursor.execute("select inteltype from storeup where userid = %d"%(request.session.get("params").get("id"))+" and tablename = 'yonghu' order by addtime desc")
rows = cursor.fetchall()
for row in rows:
for item in row:
if item != None:
leixing.add(item)
except:
leixing = set()
L = []
cursor.execute("select * from yonghu where $intelRecomColumn in ('%s"%("','").join(leixing)+"') union all select * from yonghu where $intelRecomColumn not in('%s"%("','").join(leixing)+"')")
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
for key in online_dict:
if 'datetime.datetime' in str(type(online_dict[key])):
online_dict[key] = online_dict[key].strftime(
"%Y-%m-%d %H:%M:%S")
else:
pass
L.append(online_dict)
return JsonResponse({"code": 0, "msg": '', "data":{"currPage":1,"totalPage":1,"total":1,"pageSize":5,"list": L[0:int(req_dict["limit"])]}})
def yonghu_value(request, xColumnName, yColumnName, timeStatType):
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
where = ' where 1 = 1 '
sql = ''
if timeStatType == '日':
sql = "SELECT DATE_FORMAT({0}, '%Y-%m-%d') {0}, sum({1}) total FROM yonghu {2} GROUP BY DATE_FORMAT({0}, '%Y-%m-%d') LIMIT 10".format(xColumnName, yColumnName, where, '%Y-%m-%d')
if timeStatType == '月':
sql = "SELECT DATE_FORMAT({0}, '%Y-%m') {0}, sum({1}) total FROM yonghu {2} GROUP BY DATE_FORMAT({0}, '%Y-%m') LIMIT 10".format(xColumnName, yColumnName, where, '%Y-%m')
if timeStatType == '年':
sql = "SELECT DATE_FORMAT({0}, '%Y') {0}, sum({1}) total FROM yonghu {2} GROUP BY DATE_FORMAT({0}, '%Y') LIMIT 10".format(xColumnName, yColumnName, where, '%Y')
L = []
cursor = connection.cursor()
cursor.execute(sql)
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
for key in online_dict:
if 'datetime.datetime' in str(type(online_dict[key])):
online_dict[key] = online_dict[key].strftime(
"%Y-%m-%d %H:%M:%S")
else:
pass
L.append(online_dict)
msg['data'] = L
return JsonResponse(msg)
def yonghu_o_value(request, xColumnName, yColumnName):
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
where = ' where 1 = 1 '
sql = "SELECT {0}, sum({1}) AS total FROM yonghu {2} GROUP BY {0} LIMIT 10".format(xColumnName, yColumnName, where)
L = []
cursor = connection.cursor()
cursor.execute(sql)
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
for key in online_dict:
if 'datetime.datetime' in str(type(online_dict[key])):
online_dict[key] = online_dict[key].strftime(
"%Y-%m-%d %H:%M:%S")
else:
pass
L.append(online_dict)
msg['data'] = L
return JsonResponse(msg)
def yonghu_count(request):
'''
总数接口
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
req_dict = request.session.get("req_dict")
where = ' where 1 = 1 '
for key in req_dict:
if req_dict[key] != None:
where = where + " and key like '{0}'".format(req_dict[key])
sql = "SELECT count(*) AS count FROM yonghu {0}".format(where)
count = 0
cursor = connection.cursor()
cursor.execute(sql)
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
count = online_dict['count']
msg['data'] = count
return JsonResponse(msg)
def yonghu_group(request, columnName):
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
where = ' where 1 = 1 '
sql = "SELECT COUNT(*) AS total, " + columnName + " FROM yonghu " + where + " GROUP BY " + columnName + " LIMIT 10"
L = []
cursor = connection.cursor()
cursor.execute(sql)
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
for key in online_dict:
if 'datetime.datetime' in str(type(online_dict[key])):
online_dict[key] = online_dict[key].strftime("%Y-%m-%d")
else:
pass
L.append(online_dict)
msg['data'] = L
return JsonResponse(msg)
def yonghu_sectionStat_nianling(request):
'''
新增分段统计接口
'''
if request.method in ["POST", "GET"]:
msg = {"code": normal_code, "msg": "成功", "data": {}}
where = ' where 1 = 1 '
token = request.META.get('HTTP_TOKEN')
decode_str = eval(base64.b64decode(token).decode("utf8"))
sql = """
SELECT '20岁以下' as nianling,case when t.20岁以下 is null then 0 else t.20岁以下 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '20至25岁' as nianling,case when t.20至25岁 is null then 0 else t.20至25岁 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '26至30岁' as nianling,case when t.26至30岁 is null then 0 else t.26至30岁 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '31至35岁' as nianling,case when t.31至35岁 is null then 0 else t.31至35岁 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '36至42岁' as nianling,case when t.36至42岁 is null then 0 else t.36至42岁 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '43至50岁' as nianling,case when t.43至50岁 is null then 0 else t.43至50岁 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
union all
SELECT '50岁以上' as nianling,case when t.50岁以上 is null then 0 else t.50岁以上 end total
from
(select
sum(case when nianling <= 19 then 1 else 0 end) as 20岁以下, sum(case when nianling >= 20 and nianling <= 25 then 1 else 0 end) as 20至25岁, sum(case when nianling >= 26 and nianling <= 30 then 1 else 0 end) as 26至30岁, sum(case when nianling >= 31 and nianling <= 35 then 1 else 0 end) as 31至35岁, sum(case when nianling >= 36 and nianling <= 42 then 1 else 0 end) as 36至42岁, sum(case when nianling >= 43 and nianling <= 50 then 1 else 0 end) as 43至50岁, sum(case when nianling >= 51 then 1 else 0 end) as 50岁以上 from yonghu
{where}
) t
""".format(where=where)
L = []
cursor = connection.cursor()
cursor.execute(sql)
desc = cursor.description
data_dict = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
for online_dict in data_dict:
for key in online_dict:
if 'datetime.datetime' in str(type(online_dict[key])):
online_dict[key] = online_dict[key].strftime(
"%Y-%m-%d %H:%M:%S")
else:
pass
L.append(online_dict)
msg['data'] = L
return JsonResponse(msg)
393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
