def integral_list(request, p, user_type, is_class):
"""
學生端全省積分作品排名
:param p: 分頁
:param user_id: 用戶id
:param user_type: 用戶类型参与用户类型 0 全部 1 学生 3 教师
:param is_class: 0 全省列表，其他，班级id，
:return:
"""
if p == 0:
p = 1
p_begin = (p - 1) * 5
if user_type == 1:
id = 4
elif user_type == 3:
id = 5
else:
return
if is_class == 0:
sql = "select ch_Code,b.user_id,b.json_info,b.ballot_num from active_score_user a inner join (select c.user_id,c.id as record_id, c.ch_Code,c.json_info, c.ballot_num,count(distinct c.user_id) from yw_recording_hd as c where ballot_num = (select max(ballot_num) from yw_recording_hd where c.user_id=user_id) group by c.user_id) b on a.user_id =b.user_id where a.active_id=%s ORDER BY a.score desc ,a.user_id limit %s,5" % (
id, p_begin)
# sql_count="select count(t.counts) from (select id,count(*) counts from yw_recording_hd group by user_id) t "
sql_count = "select count(*)from active_score_user a inner join (select c.user_id,c.id as record_id, c.ch_Code,c.json_info, c.ballot_num,count(distinct c.user_id) from yw_recording_hd as c where ballot_num = (select max(ballot_num) from yw_recording_hd where c.user_id=user_id) group by c.user_id) b on a.user_id =b.user_id where a.active_id=%s ORDER BY a.score desc ,a.user_id " % id
else:
sql_count = "select count(*) from active_score_user where user_id in (select user_id from yw_recording_hd GROUP BY user_id) AND active_id=%s AND unit_id=%s" % (
id, is_class)
sql = """select b.ballot_num,b.json_info,ch_Code,b.user_id from active_score_user a
inner join tbkt_ketang.mobile_order_region as d on a.user_id =d.user_id
inner join
(select c.user_id,c.id as record_id, c.ch_Code,c.json_info, c.ballot_num,count(distinct c.user_id) from yw_recording_hd as c where ballot_num = (select max(ballot_num) from yw_recording_hd where c.user_id=user_id) group by c.user_id) b on a.user_id =b.user_id
where a.active_id=%s and d.unit_class_id =%s ORDER BY a.score desc ,a.user_id limit %s,5""" % (
id, is_class, p_begin)
active = db.tbkt_active_slave.fetchall_dict(sql)
if active:
active_count = db.tbkt_active_slave.fetchone(sql_count)
user_ids = [data.user_id for data in active]
ch_code_list = []
for is_data in active:
ch_code_list.append(is_data.ch_Code)
ch_name = db.tbkt_yw_slave.yw_chapter.filter(ch_Code__in=ch_code_list,
ch_IsContent=1,
enable_flag=0).select(
"ch_Code", "ch_Name")
ch_name_dict = {
int(is_data.ch_Code): is_data.ch_Name
for is_data in ch_name
}
school_dict = com_user.users_info(user_ids)
for is_data in active:
is_data['ch_name'] = ch_name_dict.get(is_data.ch_Code, "")
is_data['school_dict'] = school_dict.get(is_data.user_id, "")
page_count = divmod(active_count[0], 5)
page_count = page_count[0] if page_count[1] == 0 else page_count[0] + 1
return {
'active': active,
'page_count': page_count,
'data_count': active_count[0]
}
def get_all_award_detail(app_id, is_smx, p=1):
"""
全部 获奖详情
:param app_id:
:param is_smx:
:param p:
:return:
"""
begin = int((p - 1) * 10)
award_type = 5
city = 'a.city<>411200'
if is_smx:
city = 'a.city=411200'
if app_id in TEA_APP_IDS:
award_type = 6
sql = """
select (@rowNum:=@rowNum+1) as num ,a.user_name ,a.award_name, a.user_id from active_award a ,(select (@rowNum :=%s)) z
where a.active_id=%s and award_type<%s and %s and award_name<>'谢谢参与' order by a.award_type ,add_time DESC limit %s ,%s
""" % (begin, app_id, award_type, city, begin, 10)
award_detail = db.tbkt_web.fetchall_dict(sql)
user_ids = list(set([a.user_id for a in award_detail]))
user_info = com_user.users_info(user_ids)
for a in award_detail:
info = user_info.get(a.user_id)
if info:
a.update(info)
rank = sorted(award_detail, key=lambda x: x.num)
sql = """select count(1) num from active_award a where active_id=%s and award_type<%s and %s and award_name<>'谢谢参与'""" % (
app_id, award_type, city)
total = db.tbkt_web.fetchone_dict(sql)
return rank, total.num
def get_rank(app_id, page=1, is_smx=False, num=10):
# 判断是否为三门峡
if page < 0: page = 1
smx = 's.city <> 411200' if not is_smx else 's.city=411200'
sql = """SELECT (@rowNum:=@rowNum+1) AS num,z.* from(
select s.user_id,s.score FROM score_user s
WHERE app_id=%s AND score<>0 AND %s
ORDER BY score DESC,s.user_id limit %s, %s
)z,(SELECT (@rowNum :=%s)) n
""" % (app_id, smx, (page - 1) * num, num, (page - 1) * num)
rank = db.slave.fetchall_dict(sql)
rank_dict = {r.user_id: r for r in rank}
user_info = com_user.users_info(rank_dict.keys())
for user_id in rank_dict.keys():
info = user_info.get(user_id, {})
rank_dict[user_id].update(info)
rank = rank_dict.values()
rank = sorted(rank, key=lambda x: int(x.num))
sql = """SELECT count(1) num FROM score_user s WHERE app_id = %s AND score <> 0 AND %s
ORDER BY score DESC,user_id """ % (app_id, smx)
row = db.slave.fetchone_dict(sql)
total = row.num if row else 0
return rank, total
def p_rank(request):
"""
@api {post} /huodong/com/rank [公共] 分页返回活动积分排行
@apiGroup com
@apiParamExample {json} 请求示例
{
grade_id：1 按年级排行时需要传入
unit_id:123 按班级排行时需要传入，p=-1 返回全班
active_id:6 活动id
p:1 页码
page_num:10 每页排行数量 ，不传时默认20
}
@apiSuccessExample {json} 成功返回
{
"message": "",
"next": "",
"data": {
"rank_info": [
{
"portrait": "https://file.m.tbkt.cn/upload_media/portrait/2017/03/06/20170306102941170594.png",
"score": 190,
"user_id": 2465828,
"school_name": "创恒中学",
"real_name": "小张"
}
]
},
"response": "ok",
"error": ""
}
@apiSuccessExample {json} 失败返回
{"message": "", "error": "no_app_id", "data": "", "response": "fail", "next": ""}
"""
args = request.QUERY.casts(grade_id=int, active_id=int, unit_id=int, page_num=int)
grade_id = args.grade_id
active_id = args.active_id
unit_id = args.unit_id
p = int(request.QUERY.get('p', 1))
p = p or 1
page_num = args.page_num or PAGE_COUNT
if active_id not in APP_ID_ALL:
return ajax_json.jsonp_fail(request, message=u'缺少参数')
# 分年级返回
if grade_id:
rank = common.get_active_rank(active_id, p, page_num, grade_id=grade_id)
# 分班级返回
elif unit_id:
rank = common.get_active_rank(active_id, p, page_num, unit_id=unit_id)
else:
rank = common.get_active_rank(active_id, p, page_num)
user_dict = com_user.users_info([int(r.user_id) for r in rank])
for r in rank:
r.real_name = user_dict.get(r.user_id).get("real_name")
r.school_name = user_dict.get(r.user_id).get("school_name")
r.portrait = format_url(user_dict.get(r.user_id).get("portrait"))
return ajax_json.jsonp_ok(request, {"rank": rank})
def all_recording(p, user_id):
"""
學生端全省積分作品排名
:param p: 分頁
:param user_id: 用戶id
:return:
"""
#gt > ; gte >=;lt <;lte <=
p_begin = (p - 1) * 10
p_end = p * 10
now_time = time.time()
active = db.tbkt_active_slave.active.get(subject_id=51,
user_type=1,
begin_time__lte=now_time,
end_time__gte=now_time)
if active:
id = active.id
sql = "select user_id from active_score_user where active_id= %s ORDER BY score desc limit %s,%s" % (
id, p_begin, p_end)
active = db.tbkt_active_slave.fetchall_dict(sql)
if active:
user_ids = [data.user_id for data in active]
user_str = (",".join(str(i) for i in user_ids))
sql = "SELECT user_id, ch_Code,json_info, max(ballot_num) ballot_num FROM yw_recording_hd where user_id in (%s) GROUP BY user_id" % user_str
recording = db.tbkt_active_slave.fetchall_dict(sql)
recording_dict = {}
ch_code_list = []
for is_data in recording:
recording_dict[is_data.user_id] = is_data
ch_code_list.append(is_data.ch_Code)
ch_name = db.tbkt_yw_slave.yw_chapter.filter(
ch_Code__in=ch_code_list, ch_IsContent=1,
enable_flag=0).select("ch_Code", "ch_Name")
ch_name_dict = {
int(is_data.ch_Code): is_data.ch_Name
for is_data in ch_name
}
recording_dict = {
is_data.user_id: is_data
for is_data in recording
}
school_dict = com_user.users_info(user_ids)
for is_data in active:
is_recording_dict = recording_dict.get(is_data.user_id, "")
ch_Code = is_recording_dict.get("ch_Code", "")
is_data['ch_name'] = ch_name_dict.get(ch_Code, "")
is_data['school_dict'] = school_dict.get(is_data.user_id, "")
is_data['recording_dict'] = is_recording_dict
return active
def score_rank(active_id, page):
"""
积分排名
:return:
"""
size = 50
ranks = get_active_rank(active_id, page, size)
uids = [int(i.get('user_id')) for i in ranks]
info_map = users_info(uids)
for i in ranks:
d = info_map.get(i.get('user_id'))
if d:
i.update(d)
if active_id == 2:
return ranks[:7]
return ranks
def user_rank(request):
"""
@api {post} /huodong/yy/normal/rank [英语常态活动]用户排名
@apiGroup yy_normal
@apiParamExample {json} 请求示例
{}
@apiSuccessExample {json} 成功返回
{
"message": "",
"next": "",
"data": {
"rank_info": [
{
"portrait": "https://file.m.tbkt.cn/upload_media/portrait/2017/03/06/20170306102941170594.png",
"score": 190,
"user_id": 2465828,
"school_name": "创恒中学",
"real_name": "小张"
}
]
},
"response": "ok",
"error": ""
}
@apiSuccessExample {json} 失败返回
{"message": "", "error": "", "data": "", "response": "fail", "next": ""}
"""
"""
功能说明： 用户排名
-----------------------------------------------
修改人 修改时间
-----------------------------------------------
张帅男 2017-9-5
"""
page_no = request.QUERY.get('page_no', 1)
page_no = max(int(page_no), 1)
rank = common.get_active_rank(ACTIVE_ID, page_no, PAGE_COUNT)
user_dict = com_user.users_info([int(r.user_id) for r in rank])
for r in rank:
r.real_name = user_dict.get(r.user_id).get("real_name")
r.school_name = user_dict.get(r.user_id).get("school_name")
r.portrait = format_url(user_dict.get(r.user_id).get("portrait"))
return jsonp_ok(request, {"rank": rank})
def award_winner(active_id, page):
"""
获奖排名
:return:
"""
size = 50
res = page * size
page = (page - 1) * size
if int(active_id) == 1:
user_award = db.tbkt_active_slave.active_award.select('user_id', 'award_name') \
.filter(remark=active_id, award_type__ne=8).order_by('award_type, add_time')[page: res]
else:
user_award = db.tbkt_active_slave.active_award.select('user_id', 'award_name') \
.filter(remark=active_id, award_type__ne=11).order_by('award_type, add_time')[page: res]
uids = [i.user_id for i in user_award]
info_map = users_info(uids)
for i in user_award:
d = info_map.get(i.get('user_id'))
if d:
i.update(d)
return user_award
def get_unit_rank(units, page=1, num=10):
"""返回教师所有班级 学生排行"""
if not units:
return
unit_ids = ','.join(str(i) for i in units)
sql = """select user_id from mobile_order_region where unit_class_id in (%s) and user_type<>3 GROUP BY user_id""" % unit_ids
stu = db.ketang_slave.fetchall_dict(sql)
if not stu:
return
sql = """SELECT (@rowNum:=@rowNum+1) AS num, s.user_id,s.score from (select user_id, if(score,score,0) score from score_user
where user_id in (%s) and app_id=%s
ORDER BY score DESC
) s ,(SELECT (@rowNum :=%s)) z
""" % (','.join(str(s.user_id)
for s in stu), APP_ID_STU, (page - 1) * num)
rank = db.default.fetchall_dict(sql)
rank_dict = {r.user_id: r for r in rank}
stu_detail = {}
for s in stu:
_detail = rank_dict.get(s.user_id, {
'num': -1,
'user_id': s.user_id,
'score': 0
})
stu_detail.update({s.user_id: _detail})
user_info = com_user.users_info(stu_detail.keys())
for new in stu_detail.keys():
info = user_info.get(new, {})
stu_detail[new].update(info)
rank = stu_detail.values()
rank = sorted(rank, key=lambda x: x['num'] if x['num'] > 0 else 10000)
# 重新添加名次
rank = rank[(page - 1) * num:page * num]
for k, v in enumerate(rank):
v['num'] = (page - 1) * num + 1 + k
total = len(stu)
return rank, total
def all_award_detail(self, p=1):
"""分页返回全部获奖用户"""
begin = int((p - 1) * 10)
sql = """
select (@rowNum:=@rowNum+1) as num ,a.user_name ,a.award_name, a.user_id,a.award_type from active_award a ,
(select (@rowNum :=%s)) z
where a.active_id=%s and award_name<>'谢谢参与' order by a.award_type ,a.id DESC limit %s ,%s
""" % (begin, self.active_id, begin, 10)
detail = db.tbkt_active.fetchall_dict(sql)
if not detail:
return [], 0
user_ids = list(set([a.user_id for a in detail]))
user_info = com_user.users_info(user_ids)
gift = db.tbkt_active.active_score_gift.filter(
active_id=self.active_id, active_type=1,
del_state=0).select('img_url', 'sequence award_type')[:]
gift_url = {int(i.award_type): i.img_url for i in gift}
award_detail = []
for d in detail:
img_url = format_url(gift_url.get(int(d.award_type) or ''))
awd = Struct(time=time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(
d.add_time)).decode('utf-8'),
name=d.award_name,
url=img_url,
award_type=d.award_type)
info = user_info.get(d.user_id)
if info:
awd.update(info)
award_detail.append(awd)
rank = sorted(award_detail, key=lambda x: x.num)
sql = """
select count(1) num from active_award a where active_id=%s and award_name<>'谢谢参与' and remark='%s'
""" % (self.active_id, self.app_id)
total = db.tbkt_active.fetchone_dict(sql)
return rank, total.num
def teacher_ranking(p):
"""
教师端，老师排名
:param p: 页数
:return:
"""
p_begin = (p - 1) * 10
sql_count = "select count(*) from active_score_user where active_id= 5"
# "SELECT a.user_id,a.score FROM active_score_user as a join active_score_user as b on a.id = b.id WHERE a.active_id= 5 ORDER BY a.score desc LIMIT %s,10" % p_begin
sql = "SELECT a.user_id,a.score FROM active_score_user as a join active_score_user as b on a.id = b.id WHERE a.active_id= 5 ORDER BY a.score desc LIMIT %s,10" % p_begin
active = db.tbkt_active_slave.fetchall_dict(sql)
if active:
active_count = db.tbkt_active_slave.fetchone(sql_count)
user_ids = [data.user_id for data in active]
if user_ids:
school_dict = com_user.users_info(user_ids)
for is_data in active:
is_data['school_dict'] = school_dict.get(is_data.user_id, "")
page_count = divmod(active_count[0], 10)
page_count = page_count[0] if page_count[
1] == 0 else page_count[0] + 1
return {'active': active, 'page_count': page_count}
def end_list(user_type, is_type, p_begin):
"""
:param user_type: 4学生，5教师
:param is_type: 1，按奖品，2，按积分来
:return:
"""
p_begin = p_begin if p_begin else 0
b_end = p_begin * 10
p_begin = (p_begin - 1) * 10
new_time = time.time()
active_db = db.tbkt_active.active.filter(id=user_type).select(
'begin_time', 'end_time')
begin_time = active_db[0].begin_time
end_time = active_db[0].end_time
is_state = 1
if new_time > end_time or new_time < begin_time:
is_state = 0
if is_type == 1:
sql = "select user_id,user_name,award_name from active_award where remark= %s and award_name !='谢谢参与' ORDER BY award_type desc limit %s,10" % (
user_type, p_begin)
active_data = db.tbkt_active_slave.fetchall_dict(sql)
if not active_data:
return {"active_data": '', "is_state": is_state}
user_ids = [data.user_id for data in active_data]
school_dict = com_user.users_info(user_ids)
for active in active_data:
active['school_dict'] = school_dict.get(active.user_id, "")
elif is_type == 2:
if user_type == 4 and b_end > 50:
return
sql = "select name,start_num,end_num from active_score_gift where active_id=%s and status=1 and active_type=2" % user_type
active_data = db.tbkt_active_slave.fetchall_dict(sql)
active_data_dict = []
for is_data in active_data:
for i in range(is_data.start_num, is_data.end_num + 1):
if i != 0:
active_data_dict.append(str(is_data.name))
num = len(active_data_dict)
sql = "SELECT a.user_id FROM active_score_user as a join active_score_user as b on a.id = b.id WHERE a.active_id= %s ORDER BY a.score desc LIMIT 0,%s" % (
user_type, num)
active_data = db.tbkt_active_slave.fetchall_dict(sql)
if active_data:
user_ids = [data.user_id for data in active_data]
if user_ids:
school_dict = com_user.users_info(user_ids)
i = 0
for is_data in active_data:
is_data['school_dict'] = school_dict.get(
is_data.user_id, "")
is_data['award_name'] = active_data_dict[i]
is_data['user_name'] = is_data['school_dict']['real_name']
i += 1
# sql="select b.user_id,b.user_name,b.award_name from active_score_user a inner join active_award b on a.user_id =b.user_id where a.active_id=%s and b.remark=%s and award_name !='谢谢参与' ORDER BY a.score desc limit %s,10" % (user_type,user_type, p_begin)
# active_data = db.tbkt_active_slave.fetchall_dict(sql)
# if not active_data:
# return {"active_data": '', "is_state": is_state}
# user_ids = [data.user_id for data in active_data]
# school_dict = com_user.users_info(user_ids)
# for active in active_data:
# active.school_dict= school_dict.get(active.user_id, "")
return {
"active_data": active_data[p_begin:b_end],
"is_state": is_state
}
return {"active_data": active_data, "is_state": is_state}