def deal_with_history():
fight_history_list = get_fight_history_models()
count = 1
for fight_history in fight_history_list:
if fight_history:
member = Member.sync_get_by_cid(fight_history.member_cid)
if member:
task_robot_analysis_reference_statistics.delay(
fight_history, member)
print(count, member.nick_name, fight_history.fight_datetime)
if count % 20000 == 0:
print('sleeping 30s...')
time.sleep(45)
count += 1
race_history_list = get_race_history_models()
for race_history in race_history_list:
if race_history:
member = Member.sync_get_by_cid(race_history.member_cid)
if member:
task_robot_analysis_reference_statistics.delay(race_history)
print(count, member.nick_name, race_history.fight_datetime)
if count % 20000 == 0:
print('sleeping 30s...')
time.sleep(45)
count += 1
def deal_with_history():
fight_history_list = get_fight_history_models()
count = 1
for fight_history in fight_history_list:
if fight_history:
fight_datetime = fight_history.fight_datetime
if fight_datetime:
docking_code = _get_docking_code(fight_datetime)
member = Member.sync_get_by_cid(fight_history.member_cid)
if member:
ds = DockingStatistics.sync_find_one(dict(docking_code=docking_code, member_cid=member.cid))
if not ds:
ds = DockingStatistics(docking_code=docking_code, member_cid=member.cid)
ds.sync_save()
docking_statistics(fight_history, member, docking_code)
print(count, fight_history.cid)
count += 1
race_history_list = get_race_history_models()
for race_history in race_history_list:
if race_history:
race_datetime = race_history.fight_datetime
if race_datetime:
docking_code = _get_docking_code(race_datetime)
member = Member.sync_get_by_cid(race_history.member_cid)
if member:
ds = DockingStatistics.sync_find_one(dict(docking_code=docking_code, member_cid=member.cid))
if not ds:
ds = DockingStatistics(docking_code=docking_code, member_cid=member.cid)
ds.sync_save()
docking_statistics(race_history, member, docking_code)
print(count, race_history.cid)
count += 1
def deal_with_history():
count = 1
fight_history_list = get_fight_history_models()
for index, fight_history in enumerate(fight_history_list):
if fight_history:
member = Member.sync_get_by_cid(fight_history.member_cid)
if member:
RedisCache.delete('%s_%s%s' %
(KEY_PREFIX_TASK_ACCURATE_STATISTICS,
member.cid, fight_history.cid))
start_accurate_statistics.delay(fight_history, member)
print(count, member.nick_name, fight_history.fight_datetime)
if count % 20000 == 0:
print('sleeping 45s...')
time.sleep(45)
count += 1
race_history_list = get_race_history_models()
for index, race_history in enumerate(race_history_list):
if race_history:
member = Member.sync_get_by_cid(race_history.member_cid)
if member:
RedisCache.delete('%s_%s%s' %
(KEY_PREFIX_TASK_ACCURATE_STATISTICS,
member.cid, race_history.cid))
start_accurate_statistics.delay(race_history, member)
print(count, member.nick_name, race_history.fight_datetime)
if count % 20000 == 0:
print('sleeping 45s...')
time.sleep(45)
count += 1
def do_merge_city_stat_member_quantity(province_dict: dict, choice_time, city_code_list=None):
"""
合并省份统计信息
:param province_dict:
:param city_code_list
:param choice_time
:return:
"""
query_dict = {}
if province_dict:
query_dict['province_code'] = {'$in': [code for code in province_dict.keys()]}
if city_code_list:
query_dict['city_code'] = {'$in': city_code_list}
else:
query_dict['city_code'] = {'$ne': None}
if not choice_time:
# 取前一天凌晨12点之前的数据
yesterday = get_yesterday()
query_dict['updated_dt'] = {'$lt': yesterday}
else:
# 当天下一天凌晨的时候
max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999)
query_dict['updated_dt'] = {'$gte': choice_time, '$lt': max_choice_time}
query_dict['status'] = STATUS_USER_ACTIVE
match_stage = MatchStage(query_dict)
group_stage = GroupStage('city_code', quantity={'$sum': 1}, province_code={'$first': '$province_code'})
sort_stage = SortStage([('quantity', DESC)])
p_lookup_stage = LookupStage(AdministrativeDivision, 'province_code', 'post_code', 'province_list')
c_lookup_stage = LookupStage(AdministrativeDivision, '_id', 'post_code', 'city_list')
city_cursor = Member.sync_aggregate([match_stage, group_stage, sort_stage, p_lookup_stage, c_lookup_stage])
t_province_dict = {}
t_province_dict = get_merge_city_data(city_cursor, province_dict, t_province_dict)
if t_province_dict:
province_dict.update(t_province_dict)
def inscription(request):
if request.method == 'POST':
try:
member = Member.objects.get(email=request.POST['email'])
except:
member = Member()
member_form = MemberForm(request.POST, instance=member)
inscription_form = InscriptionForm(request.POST)
if any(insc.is_current()
for insc in Inscription.objects.filter(member=member)):
info(request, 'Already inscripted on the current session!')
elif member_form.is_valid() and inscription_form.is_valid():
member = member_form.save()
inscription = inscription_form.save(commit=False)
inscription.member = member
inscription.session = Inscription.current_session()
inscription.save()
success(request, 'Inscription created!')
else:
error(request, 'Error when creating inscription!')
else:
member_form = MemberForm()
inscription_form = InscriptionForm()
return render(request, 'inscription/index.html', {
'member_form': member_form,
'inscription_form': inscription_form,
})
def query():
amount = 0
miss_members = set()
with open('./rdp.txt', 'r') as f:
for line in f.readlines():
rid = line.replace('\n', '')
box = RedPacketBox.sync_find_one({'redpkt_rid': rid})
if not box:
try:
race_cid, member_cid, redpacket_cid = line.split('-')
box = RedPacketBox.sync_get_by_cid(redpacket_cid)
if not box:
print('miss box', rid)
member = Member.sync_get_by_cid(member_cid)
if member:
miss_members.add(member.nick_name)
else:
print('miss member', member_cid)
continue
except ValueError:
print('miss box', rid)
continue
if box.draw_status == STATUS_REDPACKET_NOT_AWARDED:
box.draw_status = 0
box.sync_save()
print('not draw', rid)
else:
print('success ', rid)
amount += box.award_amount
print(amount)
print('-----')
print(len(miss_members), miss_members)
async def do_diamond_reward(member: Member, source_diamond, diamond=None):
if not isinstance(member, Member):
raise ValueError('Parameter member_cid must be a type of Member.')
if source_diamond not in SOURCE_MEMBER_DIAMOND_LIST:
raise ValueError('Parameter source_diamond must in %s.' %
str(SOURCE_MEMBER_DIAMOND_LIST))
if diamond and not isinstance(diamond, int):
raise TypeError('Parameter diamond must be type of int or long.')
m_diamond = diamond
if diamond is None:
diamond_reward = await GameDiamondReward.find_one(
dict(source=source_diamond))
if diamond_reward:
m_diamond = diamond_reward.quantity
if not m_diamond:
m_diamond = 0
member.diamond += m_diamond
member.updated_dt = datetime.datetime.now()
diamond_detail = MemberDiamondDetail(member_cid=member.cid,
diamond=m_diamond)
diamond_detail.member_cid = member.cid
diamond_detail.diamond = m_diamond
diamond_detail.source = source_diamond
diamond_detail.reward_datetime = datetime.datetime.now()
diamond_detail.content = SOURCE_MEMBER_DIAMOND_DICT.get(source_diamond)
await diamond_detail.save()
await member.save()
return True, m_diamond
def deal_with_member_property():
member_list = Member.sync_find(
dict(record_flag=1),
read_preference=ReadPreference.PRIMARY).batch_size(32)
count = 1
for member in member_list:
if member:
city_code = complete_member_city_code(member.province_code,
member.city_code)
mps = MemberPropertyStatistics.sync_find_one(
dict(daily_code=_get_daily_code(member.register_datetime),
province_code=member.province_code,
city_code=city_code,
district_code=member.district_code,
gender=member.sex,
age_group=member.age_group,
education=member.education,
source=member.source),
read_preference=ReadPreference.PRIMARY)
if not mps:
mps = MemberPropertyStatistics(
daily_code=_get_daily_code(member.register_datetime),
province_code=member.province_code,
city_code=city_code,
district_code=member.district_code,
gender=member.sex,
age_group=member.age_group,
education=member.education,
source=member.source)
mps.sync_save()
member_property_statistics.delay(member)
print(count, member.nick_name, member.register_datetime)
count += 1
async def save_inspiration(self, ctx, name, url):
'''Stores an inspiration for later retrieval.
'''
server = ctx.guild.name
author = ctx.message.author.name
member_id = ctx.message.author.id
session = self.interface.database.Session()
try:
member = self.interface.find_member(session, member_id)
except Exception as e:
# Create member if they do not exist in our database
member = Member(id=member_id, name=author, server=server)
self.interface.database.add_object(session, member)
try:
inspo = Inspiration(name=name,
server=server,
url=url,
member_id=member_id)
self.interface.database.add_object(session, inspo)
session.commit()
await ctx.send(f'Image saved as {name}')
except Exception as e:
await ctx.send('Could not complete your command')
print(e)
finally:
session.close()
async def attend(self, ctx, name: str):
'''Allows a user to attend an upcoming event
example: ?attend party
'''
author = ctx.message.author.name
avatar = ctx.message.author.avatar_url
id = ctx.message.author.id
try:
count = self.session.query(Member).filter(Member.id == id).count()
event = self.session.query(Event).filter(Event.name == name).first()
# Verify This event exists
if not event:
await self.bot.say('This event does not exist')
return
# Create member if they do not exist in our database
if count < 1:
member = Member(id=id, name=author, avatar=avatar, level = 1, experience = 0)
self.session.add(member)
attending = Attendance(member_id=id, event_id=event.id)
self.session.add(attending)
self.session.commit()
await self.bot.say('Member {} is now attending event {}'.format(author, name))
except Exception as e:
await self.bot.say('Could not complete your command')
self.logger.error(e)
def init_member_learning_day_statistics(history):
"""
:param history:
:return:
"""
try:
member = Member.sync_get_by_cid(history.member_cid)
if not member:
print('--- no member cid, history_cid=%s' % history.cid)
return
# START: 会员按学习日数据统计
learning_code = _sync_get_learning_code(history)
mls = MemberLearningDayStatistics.sync_find_one(dict(learning_code=learning_code, member_cid=member.cid),
read_preference=ReadPreference.PRIMARY)
if not mls:
# 创建会员学习日数据统计记录
mls = MemberLearningDayStatistics(learning_code=learning_code, member_cid=member.cid)
mls.sync_save()
# 添加任务
member_learning_day_statistics(history, member, learning_code)
# END: 会员按学习日数据统计
except Exception:
print(traceback.format_exc())
def do_statistics_member_quantity(cache_key, city_code_list, choice_time):
"""开始统计
:param cache_key:
:param city_code_list:
:param choice_time
:return:
"""
RedisCache.set(cache_key, KEY_CACHE_REPORT_DOING_NOW, 5 * 60)
stage_list = []
if city_code_list:
stage_list.append(MatchStage({'city_code': {'$in': city_code_list}}))
if not choice_time:
# 取前一天凌晨12点之前的数据
yesterday_time = get_yesterday()
time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}})
else:
# 当天下一天凌晨的时候
max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999)
time_match = MatchStage({'updated_dt': {'$gte': choice_time, '$lt': max_choice_time}})
stage_list.append(time_match)
stage_list.append(MatchStage({'status': STATUS_USER_ACTIVE}))
group_stage = GroupStage('province_code', quantity={'$sum': 1})
lookup_stage = LookupStage(AdministrativeDivision, '_id', 'post_code', 'ad_list')
sort_stage = SortStage([('quantity', DESC)])
stage_list += [group_stage, lookup_stage, sort_stage]
province_cursor = Member.sync_aggregate(stage_list)
province_dict = {}
while True:
try:
province_stat = province_cursor.next()
if province_stat:
province_code = province_stat.id if province_stat.id else '000000'
quantity = province_stat.quantity
title = 'undefined'
ad_list = province_stat.ad_list
if ad_list:
ad: FacadeO = ad_list[0]
if ad:
title = ad.title.replace('省', '').replace('市', '')
province_dict[province_code] = {
'code': province_code,
'title': title,
'data': quantity
}
except StopIteration:
break
# 合并城市统计信息
do_merge_city_stat_member_quantity(province_dict, choice_time, city_code_list)
data = [v for v in province_dict.values()]
if not data:
early_warning_empty("start_statistics_member_quantity", cache_key, city_code_list, '学习近况中人数数据为空，请检查！')
RedisCache.set(cache_key, msgpack.packb(data))
def deal_with_history():
count = 1
fight_history_list = get_fight_history_models()
for index, fight_history in enumerate(fight_history_list):
if fight_history:
member = Member.sync_get_by_cid(fight_history.member_cid)
if member:
do_calculate_member_stars_award(member, fight_history)
print(count, member.nick_name, fight_history.fight_datetime)
count += 1
def check(race_cid: str):
"""
检查多少人参与活动但是没有答过题目的
:param race_cid:
:return:
"""
race = Race.sync_get_by_cid(race_cid)
# 市级活动，如六安市，扬州市
if race.city_code:
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'code': race.city_code})
# 该活动的所属城市范围
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'code': race.city_code})
else:
prov = AdministrativeDivision.sync_find_one(
{'code': race.province_code})
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
print(city_name_list, '1')
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city_code})
quantity = 0
member_cid_list = RaceMapping.sync_distinct(
"member_cid", {
'race_cid': race_cid,
'auth_address.city': {
'$in': city_name_list
},
'auth_address.province': {
'$ne': None
},
'auth_address.district': {
'$in': dist_list
}
})
check_point_cid = RaceGameCheckPoint.sync_distinct("cid",
{"race_cid": race_cid})
for i in member_cid_list:
member = Member.sync_get_by_cid(i)
if member:
history = MemberCheckPointHistory.sync_find_one({
'check_point_cid': {
'$in': check_point_cid
},
"member_cid": i
})
if not history:
quantity += 1
print(quantity)
def quiz(request,
quiz_pk=None,
quiz_title=None,
member_pk=None,
member_email=None):
if quiz_pk:
quiz = get_object_or_404(Quiz, pk=quiz_pk)
else:
quiz = get_object_or_404(Quiz, title=quiz_title)
if (quiz.start and quiz.start > timezone.now()) or (
quiz.end and quiz.end < timezone.now()):
error(request, 'Oops! Quiz is closed.')
return render(request, "quiz/inactive.html", {"quiz": quiz})
if member_pk:
member = get_object_or_404(Member, pk=member_pk)
elif member_email:
member = get_object_or_404(Member, email=member_email)
elif request.method == 'POST':
try:
member = Member.objects.get(email=request.POST['email'])
except:
return HttpResponseForbidden()
else:
member = Member()
score = 0
try:
instance = Submission.objects.get(quiz=quiz, member=member)
if request.method == "GET":
score = instance.score()
submission_is_new = False
except:
instance = Submission(quiz=quiz, member=member)
submission_is_new = True
if request.method == 'POST':
candidat_form = CandidatForm(request.POST, instance=member)
form = QuizForm(request.POST,
instance=instance,
is_new=submission_is_new)
if candidat_form.is_valid() and form.is_valid():
candidat_form.save()
instance.save()
form.save()
score = instance.score()
else:
candidat_form = CandidatForm(instance=member)
form = QuizForm(instance=instance, is_new=submission_is_new)
return render(request, 'quiz/quiz.html', {
'candidat_form': candidat_form,
'form': form,
'score': score,
})
def start_dashboard_report_statistics_without_delay(history):
try:
member = Member.sync_get_by_cid(history.member_cid)
if not member:
print('--- no member cid, history_cid=%s' % history.cid)
return
# START 自然日会员维度统计
member_daily_dimension_statistics(history, member)
# END 自然日会员维度统计
except Exception:
print(traceback.format_exc())
def add_notice():
# msg = """尊敬的用户您好，由于小程序商户到期重审，红包领取功能暂时无法使用，具体恢复时间另行通知。审核期间您可正常使用小程序所有功能，后台会照常发放红包，期间获得的所有红包将在审核通过后全部在“消息通知”里面直接领取。"""
msg = """尊敬的用户您好，红包领取功能已经恢复正常，之前未领取的红包您可以前往“消息通知”里面直接领取。谢谢！"""
cursor = Member.sync_find(
{}, read_preference=ReadPreference.PRIMARY).batch_size(2048)
for index, member in enumerate(cursor):
print('has notice', index)
notice = MemberNotice()
notice.member_cid = member.cid
notice.category = CATEGORY_NOTICE_SYSTEM
notice.content = msg
notice.sync_save()
def export_member_game_history(cond: dict):
"""
获取该用户的对战历史
:param cond:
:return:
"""
member = Member.sync_find_one(cond)
if not member:
return
workbook = xlsxwriter.Workbook('%s的游戏历史明细.xlsx' % member.nick_name)
worksheet = workbook.add_worksheet('game_history')
history_list = MemberGameHistory.sync_find({
'member_cid': member.cid,
'created_dt': {
'$gte': datetime.now().replace(2019, 1, 1, 0, 0, 0, 0)
}
}).sort('created_dt').to_list(None)
cols = ['时间', '所处段位', '状态', '钻石增减']
for col_index, col_name in enumerate(cols):
worksheet.write(0, col_index + 1, col_name)
print(history_list)
for index, his in enumerate(history_list):
cols = [
datetime2str(his.created_dt),
TYPE_DAN_GRADE_DICT.get(his.dan_grade, str(his.dan_grade)),
STATUS_RESULT_CHECK_POINT_DICT.get(his.status),
str(his.result)
]
for col_index, col_name in enumerate(cols):
worksheet.write(index + 1, col_index + 1, col_name)
worksheet2 = workbook.add_worksheet('diamond_detail')
details = MemberDiamondDetail.sync_find({
'member_cid': member.cid
}).sort('created_dt').to_list(None)
cols = ['时间', '奖励来源', '奖励类型', '钻石增减']
for col_index, col_name in enumerate(cols):
worksheet2.write(0, col_index + 1, col_name)
for index, detl in enumerate(details):
cols = [
datetime2str(detl.reward_datetime),
SOURCE_MEMBER_DIAMOND_DICT.get(detl.source), detl.content,
detl.diamond
]
for col_index, col_name in enumerate(cols):
worksheet2.write(index + 1, col_index + 1, col_name)
workbook.close()
def insert_from_member_amount_map(member_amount_map: dict, daily_code,
race_cid):
"""
:param member_amount_map:
:param daily_code:
:return:
"""
if not member_amount_map:
return []
ret_list = []
for member_cid, value in member_amount_map.items():
temp_member = Member.sync_get_by_cid(member_cid)
if not temp_member:
print("RedPacket未找到对应member_cid:%s" % member_cid)
continue
info = MemberStatisticInfo()
info.daily_code = daily_code
info.race_cid = race_cid
# 会员信息
info.member_cid = member_cid
info.nick_name = temp_member.nick_name
info.open_id = temp_member.open_id
info.mobile = temp_member.mobile
info.first_time_login = temp_member.created_dt
# race_mapping相关信息,地理位置
has_race_mapping = get_race_mapping_info(info)
if not has_race_mapping:
print("RedPacket未找到对应race_mapping,race_cid:%s,member_cid:%s" %
(info.race_cid, info.member_cid))
continue
# 是否为当日新用户
is_new_user(info)
# 最后一关
is_final_pass(info)
info.grant_red_packet_amount = value.get('grant_amount')
info.grant_red_packet_count = value.get('grant_count')
info.draw_red_packet_amount = value.get('draw_amount')
info.draw_red_packet_count = value.get('draw_count')
print("Success: member_cid:%s is_final_Pass:%s is_new:%s" %
(info.member_cid, info.is_final_passed, info.is_new_user))
ret_list.append(info)
return ret_list
def get_no_history_member():
"""
获取没有历史记录的用户
:return:
"""
data = Member.sync_aggregate([
MatchStage({'nick_name': '游客'}),
LookupStage(MemberCheckPointHistory, 'cid', 'member_cid',
'history_list'),
MatchStage({'history_list': []}),
GroupStage('cid')
]).to_list(None)
with open('./no-history-members.json', 'w', encoding='utf-8') as f:
dist_list = json.dumps(list([d.id for d in data]), ensure_ascii=False)
f.write(dist_list)
def start_accurate_statistics(self, history_model, member: Member = None):
"""
会员游戏数据精确统计
:param self: 任务对象
:param history_model: 游戏历史
:param member: 会员，默认为None
:return:
"""
result = {'code': 0}
if allowed_process():
try:
if not isinstance(history_model, (MemberGameHistory, MemberCheckPointHistory)):
raise ValueError('"history_model" must be a instance of MemberGameHistory or MemberCheckPointHistory.')
if member:
if not isinstance(member, Member):
raise ValueError('"member" must be a instance of member.')
else:
member = Member.sync_get_by_cid(history_model.member_cid)
if member:
stat_type = 'FIGHT'
if isinstance(history_model, MemberCheckPointHistory):
stat_type = 'RACE'
logger.info(
'START(%s): Accurate Statistics, type=%s, history_cid=%s, member_code=%s' % (
self.request.id, stat_type, history_model.cid, member.code))
tag_key = '%s_%s%s' % (KEY_PREFIX_TASK_ACCURATE_STATISTICS, member.cid, history_model.cid)
tag = RedisCache.get(tag_key)
if not tag:
RedisCache.set(tag_key, 1, 2 * 24 * 60 * 60)
# 题目正确率
do_count_subject_accuracy(history_model, member)
# 会员正确率
do_count_member_accuracy(history_model, member)
result['code'] = 1
result['msg'] = 'Succeed!'
else:
logger.warning(
'END(%s): [Accurate Statistics] repeat executed, type=%s, history_cid=%s, member_code=%s ' % (
self.request.id, stat_type, history_model.cid, member.code))
except Exception:
logger.error(traceback.format_exc())
finally:
RedisCache.delete(KEY_ALLOW_PROCESS_ACCURATE_STATISTICS)
return result
async def do_genete():
# _now = datetime.now()
# bg_dt = _now.replace(year=2019, month=7, day=1, hour=12, minute=0, second=0)
# ed_dt = _now.replace(year=2019, month=7, day=2, hour=14, minute=0, second=0)
# member_list1 = await MemberGameHistory.distinct('member_cid', {'created_dt': {'$gt': bg_dt, '$lt': ed_dt}})
# member_list2 = await MemberCheckPointHistory.distinct('member_cid', {'created_dt': {'$gt': bg_dt, '$lt': ed_dt}})
# member_cids = set(member_list1).union(set(member_list2))
_now = datetime.now()
index = 0
cursor = Member.find(
{
'auth_address.province': '安徽省'
},
read_preference=ReadPreference.PRIMARY).batch_size(128)
while await cursor.fetch_next:
try:
member = cursor.next_object()
award_value = 8000
if member.cid == '0D8A69937D498A20BDFC05881E5C4DB3':
award_value = 12000
msg = '尊敬的用户您好，对于匹配超时而导致错误扣除钻石的问题，系统近期将返还%s钻石至您的账户，请注意查收。' % award_value
member.diamond += award_value
await member.save()
notice = MemberNotice()
notice.member_cid = member.cid
notice.category = CATEGORY_NOTICE_SYSTEM
notice.notice_datetime = _now
notice.content = msg
await notice.save()
detail = MemberDiamondDetail()
detail.member_cid = member.cid
detail.diamond = award_value
detail.source = SOURCE_MEMBER_DIAMOND_ADMIN_UPDATE
detail.reward_datetime = _now
detail.content = msg
await detail.save()
index += 1
print('has done %s, cid: %s, diamond: %s' %
(index, member.cid, award_value))
except StopIteration:
break
def init_member_star():
members = Member.sync_find({'status': STATUS_USER_ACTIVE}).batch_size(32)
for index, member in enumerate(members):
print(index + 1, member.nick_name)
quantity = 0
if member.star_grade is None or member.star_grade == 0:
if member.dan_grade:
quantity = get_need_star(member.dan_grade)
else:
quantity = get_need_star(member.dan_grade) + member.star_grade
stars_award_history = MemberStarsAwardHistory(
member_cid=member.cid,
dan_grade=member.dan_grade if member.dan_grade else 1,
quantity=quantity,
award_dt=datetime.datetime(2018, 10, 15, 0, 0, 0, 0))
stars_award_history.sync_save()
async def find_member_by_open_id(open_id):
try:
member = RedisCache.get(open_id)
if member:
member = Member().result_2_obj(
from_msgpack(msgpack.unpackb(member, raw=False)))
return member
except Exception:
pass
member = await Member.find_one(dict(open_id=open_id,
status=STATUS_USER_ACTIVE),
read_preference=ReadPreference.PRIMARY)
if member:
RedisCache.set(open_id, msgpack.packb(to_msgpack(member)),
60 * 60 * 24)
RedisCache.set('mid_%s' % str(member.oid), open_id, 60 * 60 * 24)
return member
def register(request, event_id):
event = get_object_or_404(Event, pk=event_id)
if event.end and event.end < timezone.now():
return render(request, 'event/ended.html', {'event': event})
if request.method == 'POST':
try:
member = Member.objects.get(email=request.POST['email'])
except:
member = Member()
member_form = MemberForm(request.POST, instance=member)
inscription_form = InscriptionForm(request.POST)
insc = [insc for insc in Inscription.objects.filter(member=member)
if insc.is_current()]
insc = insc[0] if len(insc) == 1 else None
if not insc and member_form.is_valid() and inscription_form.is_valid():
member = member_form.save()
insc = inscription_form.save(commit=False)
insc.member = member
insc.session = Inscription.current_session()
insc.save()
if insc:
try:
Register.objects.create(member=member, event=event)
success(request, 'You have successfully registered')
except:
info(request, 'You has already registred!')
else:
error(request, 'Error when registering!')
else:
member_form = MemberForm()
inscription_form = InscriptionForm()
return render(request, 'event/register.html', {
'member_form': member_form,
'inscription_form': inscription_form,
'forms': [member_form, inscription_form],
'event': event,
})
def start_dashboard_report_statistics_without_delay(history):
"""
:param history:
:return:
"""
try:
daily_code = _get_daily_code(history.fight_datetime)
member = Member.sync_get_by_cid(history.member_cid)
if not member:
print('--- no member cid, history_cid=%s' % history.cid)
mds = MemberDailyStatistics.sync_find_one(
dict(daily_code=daily_code, member_cid=member.cid))
if not mds:
mds = MemberDailyStatistics(daily_code=daily_code,
member_cid=member.cid)
mds.sync_save()
if mds:
member_daily_statistics(history, member, daily_code)
except Exception:
print(traceback.format_exc())
def deal_member_without_history(race_cid, daily_code):
"""
处理报名活动但未答题的会员
:param race_cid:
:param daily_code:
:return:
"""
city_name_list, district_name_list = get_address(race_cid)
checkpoint_cid, _checkpoint_map = get_checkpoint_cid_list(race_cid)
start_date = str2datetime(daily_code, '%Y%m%d').replace(hour=0,
minute=0,
second=0,
microsecond=0)
end_date = start_date + datetime.timedelta(days=1)
member_cid_with_history = MemberCheckPointHistory.sync_distinct(
"member_cid", {
'check_point_cid': {
'$in': checkpoint_cid
},
'created_dt': {
'$gte': start_date,
'$lt': end_date
}
})
race_member_match = MatchStage({
"race_cid": race_cid,
'member_cid': {
'$nin': member_cid_with_history
},
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": district_name_list
},
'created_dt': {
'$gte': start_date,
'$lt': end_date
}
})
member_group = GroupStage({'member_cid': '$member_cid'},
auth_address={'$first': '$auth_address'},
company_cid={'$first': '$company_cid'},
mobile={'$first': '$mobile'},
created_dt={'$first': '$created_dt'})
member_project = ProjectStage(
**{
'cid': '$cid',
'member_cid': '$_id.member_cid',
'auth_address': '$auth_address',
'mobile': '$mobile',
'created_dt': '$created_dt',
'company_cid': '$company_cid'
})
member_without_history = RaceMapping.sync_aggregate(
[race_member_match, member_group, member_project]).batch_size(4)
member_amount_map = get_red_packet_info(race_cid, start_date, end_date)
red_member_cid_list = member_amount_map.keys()
member_no_history_list = []
count = 0
while True:
try:
stat = member_without_history.next()
count += 1
if stat.member_cid in red_member_cid_list:
continue
# 根据member_cid查对应的member信息
temp_member = Member.sync_get_by_cid(stat.member_cid)
if not temp_member:
print("no history未找到对应member_cid:%s" % stat.member_cid)
continue
info_special = MemberStatisticInfo()
info_special.is_special_user = 1
info_special.race_cid = race_cid
info_special.member_cid = stat.member_cid
info_special.daily_code = format(stat.created_dt, '%Y%m%d')
info_special.nick_name = temp_member.nick_name
info_special.open_id = temp_member.open_id
if stat.mobile:
info_special.mobile = stat.mobile
else:
info_special.mobile = temp_member.mobile
info_special.first_time_login = temp_member.created_dt
info_special.enter_times = 1
info_special.answer_times = 0
info_special.true_answer_times = 0
info_special.is_final_passed = 0
info_special.is_new_user = 1
info_special.grant_red_packet_amount = 0.0
info_special.grant_red_packet_count = 0
info_special.draw_red_packet_count = 0
info_special.draw_red_packet_amount = 0.0
info_special.province = stat.auth_address.get('province')
info_special.city = stat.auth_address.get('city')
info_special.district = stat.auth_address.get('district')
info_special.town = stat.auth_address.get('town')
info_special.check_point_cid = stat.race_check_point_cid
if stat.race_check_point_cid:
info_special.check_point_index = _checkpoint_map[
stat.race_check_point_cid]
else:
info_special.check_point_index = 1
if stat.company_cid:
company = Company.sync_get_by_cid(stat.company_cid)
info_special.company_cid = company.cid
info_special.company_name = company.title
member_no_history_list.append(info_special)
# logger.info("Success without history: member_cid:%s is_final_Pass:%s" % (info_special.member_cid,info_special.is_final_passed))
if len(member_no_history_list) % 500 == 0:
MemberStatisticInfo.sync_insert_many(member_no_history_list)
member_no_history_list = []
except StopIteration:
break
except CursorNotFound:
member_without_history = RaceMapping.sync_aggregate(
[race_member_match, member_group,
member_project]).skip(count).batch_size(4)
except Exception as e:
logger.info(
"Fail: without history daily_code:%s,member_cid:%s,race_cid: %s"
% (info_special.daily_code, info_special.member_cid, race_cid))
if len(member_no_history_list) > 0:
MemberStatisticInfo.sync_insert_many(member_no_history_list)
def daily_member_statistic(race_cid, daily_code):
"""
统计每日活动下的会员
:param race_cid:
:param daily_code:
:return:
"""
start_date = str2datetime(daily_code, '%Y%m%d').replace(hour=0,
minute=0,
second=0,
microsecond=0)
end_date = start_date + datetime.timedelta(days=1)
check_point_cids, _checkpoint_map = get_checkpoint_cid_list(race_cid)
# 当日答题记录
history_match = MatchStage({
'created_dt': {
'$gte': start_date,
'$lt': end_date
},
'check_point_cid': {
'$in': check_point_cids
}
})
history_group = GroupStage({'member_cid': '$member_cid'},
enter_times={'$sum': 1},
results_list={'$push': '$result'})
history_project = ProjectStage(
**{
'member_cid': '$_id.member_cid',
'results_list': '$results_list',
'enter_times': '$enter_times'
})
# 红包信息
member_amount_map = get_red_packet_info(race_cid, start_date, end_date)
# member_cid: amount
check_point_history_cursor = MemberCheckPointHistory.sync_aggregate(
[history_match, history_group, history_project],
allowDiskUse=True).batch_size(4)
member_statistic_list = []
count = 0
while True:
try:
cursor = check_point_history_cursor.next()
count += 1
# 根据member_cid查对应的member信息
temp_member = Member.sync_get_by_cid(cursor.member_cid)
if not temp_member:
print("normal未找到对应member_cid:%s" % cursor.member_cid)
continue
info = MemberStatisticInfo()
info.daily_code = daily_code
info.race_cid = race_cid
# 会员信息
info.member_cid = cursor.member_cid
info.nick_name = temp_member.nick_name
info.open_id = temp_member.open_id
info.mobile = temp_member.mobile
info.first_time_login = temp_member.created_dt
info.enter_times = cursor.enter_times
# 答题数量、正确数
for results in cursor.results_list:
info.answer_times += len(results)
info.true_answer_times += len([
result for result in results if result.get('true_answer')
])
# race_mapping相关信息,地理位置
has_race_mapping = get_race_mapping_info(info)
if not has_race_mapping:
print("normal未找到对应race_mapping,race_cid:%s,member_cid:%s" %
(info.race_cid, info.member_cid))
continue
# 是否为当日新用户
is_new_user(info)
# 最后一关
is_final_pass(info)
# 红包信息
try:
value = member_amount_map.pop(info.member_cid)
except KeyError:
value = None
if not value:
value = {
'grant_count': 0,
'grant_amount': 0,
'draw_count': 0,
'draw_amount': 0
}
info.grant_red_packet_amount = value.get('grant_amount')
info.grant_red_packet_count = value.get('grant_count')
info.draw_red_packet_amount = value.get('draw_amount')
info.draw_red_packet_count = value.get('draw_count')
# 保存记录
member_statistic_list.append(info)
# logger.info("Success: member_cid:%s is_final_Pass:%s is_new:%s" % (
# info.member_cid, info.is_final_passed, info.is_new_user))
print("Success: member_cid:%s is_final_Pass:%s is_new:%s" %
(info.member_cid, info.is_final_passed, info.is_new_user))
if len(member_statistic_list) % 500 == 0:
MemberStatisticInfo.sync_insert_many(member_statistic_list)
member_statistic_list = []
except StopIteration:
break
except CursorNotFound:
check_point_history_cursor = MemberCheckPointHistory.sync_aggregate(
[history_match, history_group,
history_project]).skip(count).batch_size(4)
except Exception as e:
logger.error(e)
logger.info("Fail: daily_code:%s,race_cid: %s" %
(daily_code, race_cid))
member_statistic_list = []
member_statistic_list += insert_from_member_amount_map(
member_amount_map, daily_code, race_cid)
if len(member_statistic_list) > 0:
MemberStatisticInfo.sync_insert_many(member_statistic_list)
step1： create QR scene = 6
step2: move online data(before 2019-03-29) to scene(6)
step3: change statistics
"""
import traceback
from datetime import datetime
from db.models import Member
date_value = datetime.now().replace(year=2019,
month=3,
day=29,
hour=0,
minute=0,
second=0,
microsecond=0)
cursor = Member.sync_find({'source': 2, 'created_dt': {'$lte': date_value}})
while True:
try:
member = cursor.next()
member.needless['old_source'] = member.source
member.source = 6
member.sync_save()
except StopIteration:
print(traceback.format_exc())
break
async def post(self):
"""
:return:
"""
time = datetime.datetime.now()
export_time = datetime2str(time, date_format='%Y-%m-%d %H:%M:%S')
order = self.get_argument('order', '')
chart_name = self.get_argument('chart_name', '')
# 答题活跃度的数据
data_dict = self.get_argument('data', '')
data_list = []
condition_title_list = []
# 没有筛选条件的总体活跃度
if data_dict:
data_dict = json.loads(data_dict)
condition_title_list = list(data_dict.keys())
data_list = list(data_dict.values())
# 有筛选条件的数据
if '总体活跃度' in condition_title_list:
position = condition_title_list.index('总体活跃度')
else:
position = data_list.index(max(sum(data_list)))
if len(data_list) > position:
condition_title_list.remove(condition_title_list[position])
# 可管理的省份名称
manage_region_title_list = []
manage_region_code_list = self.current_user.manage_region_code_list
if manage_region_code_list:
for manage_region_code in manage_region_code_list:
manage_region_province = await AdministrativeDivision.find_one(
{
'code': manage_region_code,
'record_flag': 1,
'parent_code': None
})
if manage_region_province:
manage_region_title_list.append(
manage_region_province.title)
else:
manage_region_city = await AdministrativeDivision.find_one(
{
'code': manage_region_code,
'record_flag': 1
})
province = await manage_region_city.parent
manage_region_title_list.append(province.title)
try:
output = BytesIO()
workbook = Workbook(output, {'in_memory': True})
title_format = workbook.add_format({
'font_size': 12,
'bold': '1',
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei',
'border': 1
})
data_format = workbook.add_format({
'valign': 'vcenter',
'align': 'left',
'font_name': 'Microsoft YaHei',
'border': 1
})
data_center_format = workbook.add_format({
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei',
'border': 1
})
if order == "1" and chart_name:
pass
# 公民科学素质学习答题趋势统计
if order == '2' and chart_name:
answer_tendency_date = self.get_argument(
'answer_tendency_date', '')
answer_tendency_data = self.get_argument(
'answer_tendency_data', '')
answer_tendency_data = json.loads(answer_tendency_data)
answer_tendency_date = json.loads(answer_tendency_date)
answer_tendency_date = deal_with_data(answer_tendency_date)
if answer_tendency_date and answer_tendency_data:
worksheet = workbook.add_worksheet(name=chart_name)
worksheet.merge_range(1,
2,
1,
5,
'导出时间' + export_time,
cell_format=title_format)
worksheet.merge_range(0,
0,
0,
2,
chart_name,
cell_format=title_format)
worksheet.merge_range(1,
0,
1,
1,
'筛选条件',
cell_format=title_format)
worksheet.merge_range(2,
0,
3,
1,
'总体答题次数',
cell_format=title_format)
worksheet.write_string(2, 2, '日期', cell_format=data_format)
worksheet.write_string(3,
2,
'答题次数',
cell_format=data_format)
answer_tendency_title = list(answer_tendency_data.keys())
answer_data_list = list(answer_tendency_data.values())
# 有筛选条件的数据
if '总体答题次数' in answer_tendency_title:
position = answer_tendency_title.index('总体答题次数')
else:
position = answer_data_list.index(
max(sum(answer_data_list)))
if len(answer_data_list) > position:
answer_tendency_title.remove(
answer_tendency_title[position])
for index, date in enumerate(answer_tendency_date):
worksheet.write_string(2, 3 + index, date)
if '总体答题次数' in list(answer_tendency_data.keys()):
worksheet.write_string(
3,
3 + index,
str(answer_tendency_data['总体答题次数'][index]),
cell_format=data_center_format)
else:
max_data_list = max(sum(answer_data_list))
worksheet.write_string(
3,
2 + order,
max_data_list[index - 1],
cell_format=data_center_format)
if answer_tendency_title:
# 有筛选条件得数据写入到excel
for index, condition_title in enumerate(
answer_tendency_title):
worksheet.merge_range(2 * (index + 2) + index + 1,
0,
2 * (index + 2) + 2 + index,
1,
condition_title,
cell_format=title_format)
worksheet.write_string(2 * (index + 2) + index + 1,
2,
'日期',
cell_format=data_format)
worksheet.write_string(2 * (index + 2) + index + 2,
2,
'答题次数',
cell_format=data_format)
for condition_index, data in enumerate(
answer_tendency_data[condition_title]):
worksheet.write_string(2 * (index + 2) +
index + 2,
2 + condition_index + 1,
str(data),
cell_format=data_format)
worksheet.write_string(
2 * (index + 2) + index + 1,
2 + condition_index + 1,
answer_tendency_date[condition_index],
cell_format=data_format)
if order == '3' and chart_name and data_dict:
# 活跃度的导出excel
worksheet = workbook.add_worksheet(name=chart_name)
for order in range(1, 31):
worksheet.write_string(2,
2 + order,
str(order),
cell_format=data_center_format)
if '总体活跃度' in list(data_dict.keys()):
worksheet.write_string(3,
2 + order,
data_dict['总体活跃度'][order - 1] +
'%',
cell_format=data_center_format)
else:
max_data_list = max(sum(data_list))
worksheet.write_string(3,
2 + order,
max_data_list[order - 1] + '%',
cell_format=data_center_format)
worksheet.merge_range(1,
2,
1,
5,
'导出时间' + export_time,
cell_format=title_format)
worksheet.merge_range(0,
0,
0,
2,
chart_name,
cell_format=title_format)
worksheet.merge_range(1,
0,
1,
1,
'筛选条件',
cell_format=title_format)
worksheet.merge_range(2,
0,
3,
1,
'总体活跃度(%)',
cell_format=title_format)
worksheet.write_string(2, 2, '活跃天数', cell_format=data_format)
worksheet.write_string(3, 2, '活跃度(%)', cell_format=data_format)
if condition_title_list:
# 有筛选条件得数据写入到excel
for index, condition_title in enumerate(
condition_title_list):
worksheet.merge_range(2 * (index + 2) + index + 1,
0,
2 * (index + 2) + 2 + index,
1,
condition_title,
cell_format=title_format)
worksheet.write_string(2 * (index + 2) + index + 1,
2,
'活跃天数',
cell_format=data_format)
for order in range(1, 31):
worksheet.write_string(2 * (index + 2) + index + 1,
2 + order,
str(order),
cell_format=data_format)
worksheet.write_string(2 * (index + 2) + index + 2,
2,
'活跃度(%)',
cell_format=data_format)
for condition_index, data in enumerate(
data_dict[condition_title]):
worksheet.write_string(2 * (index + 2) + index + 2,
2 + condition_index + 1,
data,
cell_format=data_format)
# 每日参与top5的导出数据
if order == '4' and chart_name:
# 每日参与top_5的数据
stat_category = self.get_argument('stat_category', '')
top_five_data_list = self.get_argument('top_five_data', '')
if top_five_data_list:
top_five_data_list = json.loads(top_five_data_list)
date_list = self.get_argument('date', '')
if date_list:
date_list = json.loads(date_list)
date_list = deal_with_data(date_list)
if stat_category and top_five_data_list and date_list:
data_series_dict, province_and_city_dict = deal_with_data_excel(
date_list, top_five_data_list)
# {'江苏': ['南京', '苏州‘], '浙江':['杭州']}
total_data_dict = {}
# 某个省下面的所有的市 报表中有数据的市
city_title_list = []
# 报表中省的列表
province_title_list = []
# 省和市的列表
total_title = []
show_name_list = []
show_data_list = []
# 需要添加undefined的省份
need_append_undifend_province_list = []
for top_five_data in top_five_data_list:
temple_data = []
temple_name = []
for index, data in enumerate(top_five_data):
total_title.append(data['name'])
if data['name'] and data['value']:
temple_name.append(
{date_list[index]: data['name']})
temple_data.append(
{date_list[index]: data['value']})
show_name_list.append(temple_name)
show_data_list.append(temple_data)
total_title = [title for title in total_title if title]
for total in total_title:
if ' ' in total:
province_title_list.append(total.split(' ')[0])
city_title_list.append(total.split(' ')[1])
if total.split(' ')[1] == 'undefined':
need_append_undifend_province_list.append(
total.split(' ')[0])
province_title_list = list(set(province_title_list))
city_title_list = list(
set([city for city in city_title_list if city]))
for province_title in province_title_list:
total_data_dict[province_title] = city_title_list
province = await AdministrativeDivision.find_one({
'title':
province_title,
'parent_code':
None
})
if province:
belong_provice_city_title_list = await AdministrativeDivision.distinct(
'title', {'parent_code': province.code})
total_data_dict[province_title] = list(
set(city_title_list)
& set(belong_provice_city_title_list))
total_data_dict[province_title] = list(
set(city_title_list)
& set(belong_provice_city_title_list))
# 各个省的市的个数
length_list = []
for index, city_title in enumerate(
list(total_data_dict.values())):
if list(total_data_dict.keys()
)[index] in need_append_undifend_province_list:
total_data_dict.get(
list(total_data_dict.keys())[index]).append(
'undefined')
for index, city_title in enumerate(
list(total_data_dict.values())):
if city_title:
length_list.append(len(city_title))
province_length = sum(length_list) + len(
list(total_data_dict.values()))
if province_length == 0:
province_length = 10
worksheet = workbook.add_worksheet(name=chart_name + '(' +
stat_category + ')')
worksheet.merge_range(0,
0,
province_length,
0,
'每日参与' + stat_category,
cell_format=data_format)
worksheet.merge_range(1,
1,
province_length,
1,
'导出时间: ' + export_time,
cell_format=data_format)
worksheet.merge_range(0,
2,
0,
4,
'日期',
cell_format=data_center_format)
for index, date in enumerate(date_list):
worksheet.write_string(0,
5 + index,
date,
cell_format=data_format)
worksheet.merge_range(1,
2,
province_length,
2,
'省份',
cell_format=data_center_format)
city_map = {}
province_map = {}
if total_data_dict:
choice_city_title_list = list(total_data_dict.values())
for index, data in enumerate(choice_city_title_list):
if index == 0:
worksheet.merge_range(
1,
3,
1 + len(data),
3,
list(total_data_dict.keys())[index],
cell_format=data_center_format)
else:
worksheet.merge_range(
1 + sum(length_list[:index]) + index,
3,
sum(length_list[:index + 1]) + index + 1,
3,
list(total_data_dict.keys())[index],
cell_format=data_center_format)
if index == 0:
for city_index, city in enumerate(data):
if city == 'undefined':
city = '_'
worksheet.write_string(
1,
4,
list(total_data_dict.keys())[index],
cell_format=data_center_format)
worksheet.write_string(
2 + city_index,
4,
city,
cell_format=data_center_format)
worksheet.write_string(
1, 5, '6666', cell_format=data_format)
city_map[city] = 2 + city_index
province_map[list(
total_data_dict.keys())[index]] = 1
Position(city, 2 + city_index, 4)
Position(
list(total_data_dict.keys())[index], 1,
4)
else:
for city_index, city in enumerate(data):
if city == 'undefined':
city = '_'
worksheet.write_string(
sum(length_list[:index]) + index + 1,
4,
list(total_data_dict.keys())[index],
cell_format=data_center_format)
worksheet.write_string(
sum(length_list[:index]) + index + 2 +
city_index,
4,
city,
cell_format=data_center_format)
city_map[city] = sum(
length_list[:index]
) + 2 + index + city_index
province_map[list(
total_data_dict.keys())[index]] = sum(
length_list[:index]) + index + 1
Position(
city,
sum(length_list[:index]) + 2 + index +
city_index, 4)
Position(
list(total_data_dict.keys())[index],
sum(length_list[:index]) + index + 1,
4)
for index, data in enumerate(choice_city_title_list):
if index == 0:
for key, value in data_series_dict.items():
if key.split(' ')[0] == 'undefined':
position = Position(
key.split(' ')[0], city_map['_'],
4)
else:
position = Position(
key.split(' ')[0],
city_map[key.split(' ')[0]], 4)
if position:
order = date_list.index(
key.split(' ')[1])
worksheet.write_number(
position.row, 5 + order,
int(value))
else:
for key, value in data_series_dict.items():
if key.split(' ')[0] == 'undefined':
position = Position(
key.split(' ')[0], city_map['_'],
4)
else:
position = Position(
key.split(' ')[0],
city_map[key.split(' ')[0]], 4)
if position:
order = date_list.index(
key.split(' ')[1])
worksheet.write_number(
position.row, 5 + order,
int(value))
for order, date in enumerate(date_list):
for index, value in enumerate(
list(province_map.values())):
if index != len(list(
province_map.values())) - 1:
first = value + 2
end = list(province_map.values())[index +
1]
else:
first = list(
province_map.values())[index] + 2
end = province_length + 1
col = 5 + order
col = convert(col)
first = col + str(first)
end = col + str(end)
worksheet.write_formula(
value, 5 + order,
'=SUM(' + first + ':' + end + ')')
# 学习近况的导出数据
if order == '1' and chart_name:
# 取前一天凌晨12点之前的数据
time_match = get_yesterday()
time_match_stage = MatchStage(
{'updated_dt': {
'$lt': time_match
}})
province_code_list, city_code_list, _ = await do_different_administrative_division2(
self.current_user.manage_region_code_list)
month_stage_list = []
member_stage_list = []
accuracy_stage_list = []
if province_code_list:
month_stage_list.append(
MatchStage(
{'province_code': {
'$in': province_code_list
}}))
member_stage_list.append(
MatchStage(
{'province_code': {
'$in': province_code_list
}}))
accuracy_stage_list.append(
MatchStage(
{'province_code': {
'$in': province_code_list
}}))
if city_code_list:
month_stage_list.append(
MatchStage({'city_code': {
'$in': city_code_list
}}))
member_stage_list.append(
MatchStage({'city_code': {
'$in': city_code_list
}}))
accuracy_stage_list.append(
MatchStage({'city_code': {
'$in': city_code_list
}}))
add_fields_stage = AddFieldsStage(
t_accuracy={
'$cond': {
'if': {
'$eq': ['$t_total', 0]
},
'then': 0,
'else': {
'$divide': ['$t_correct', '$t_total']
}
}
})
member_stage_list.append(
MatchStage({'status': STATUS_USER_ACTIVE}))
month_group_stage = GroupStage(
{
'province_code': '$province_code',
'created_dt': {
"$dateToString": {
"format": "%Y-%m",
"date": "$created_dt"
}
}
},
sum={'$sum': '$learn_times'})
lookup_stage = LookupStage(AdministrativeDivision, '_id',
'post_code', 'ad_list')
member_group_stage = GroupStage(
{
'province_code': '$province_code',
'created_dt': {
"$dateToString": {
"format": "%Y-%m",
"date": "$created_dt"
}
}
},
sum={'$sum': 1})
accuracy_group_stage = GroupStage(
{
'province_code': '$province_code',
'created_dt': {
"$dateToString": {
"format": "%Y-%m",
"date": "$created_dt"
}
}
},
t_total={'$sum': '$total'},
t_correct={'$sum': '$correct'})
group_stage = GroupStage('province_code',
t_total={'$sum': '$total'},
t_correct={'$sum': '$correct'})
month_sort_stage = SortStage([('_id.created_dt', ASC)])
# 次数
month_stage_list.extend([
time_match_stage, month_group_stage, lookup_stage,
month_sort_stage
])
# 人数
member_stage_list.extend([
time_match_stage, member_group_stage, lookup_stage,
month_sort_stage
])
accuracy_province_stage_list = copy.deepcopy(
accuracy_stage_list)
accuracy_province_stage_list.extend([
time_match_stage, group_stage, lookup_stage,
add_fields_stage, month_sort_stage
])
# 省和月份共同筛选的正确率
accuracy_stage_list.extend([
time_match_stage, accuracy_group_stage, lookup_stage,
add_fields_stage, month_sort_stage
])
# 只有省的正确率
month_province_list = MemberLearningDayStatistics.aggregate(
month_stage_list)
member_province_list = Member.aggregate(member_stage_list)
accuracy_province_list = MemberSubjectStatistics.aggregate(
accuracy_stage_list)
total_accuracy = MemberSubjectStatistics.aggregate(
accuracy_province_stage_list)
month_province_dict = {}
member_province_dict = {}
accuracy_province_dict = {}
date_list = []
province_title_list = []
province_map = {}
member_date_list = []
accuracy_date_list = []
# 次数
while await month_province_list.fetch_next:
month_province = month_province_list.next_object()
if month_province:
province_dt = month_province.id if month_province.id else '000000'
province = await AdministrativeDivision.find_one({
'code':
province_dt.get('province_code'),
'record_flag':
1,
'parent_code':
None
})
if province_dt.get('created_dt') not in date_list:
date_list.append(province_dt.get('created_dt'))
province_title = ''
if province:
province_title = province.title
province_title_list.append(province_title)
province_title_list = list(set(province_title_list))
dt = province_dt.get('created_dt')
month_province_dict[province_title + ' ' +
dt] = month_province.sum
# 人数
while await member_province_list.fetch_next:
member_province = member_province_list.next_object()
if member_province:
member_province_id = member_province.id if member_province.id else ''
province = await AdministrativeDivision.find_one({
'code':
member_province_id.get('province_code'),
'record_flag':
1,
'parent_code':
None
})
province_title = ''
if province:
province_title = province.title
dt = member_province_id.get('created_dt')
if member_province_id.get(
'created_dt') not in member_date_list:
member_date_list.append(
member_province_id.get('created_dt'))
member_province_dict[province_title + ' ' +
dt] = member_province.sum
# 正确率
while await accuracy_province_list.fetch_next:
accuracy_province = accuracy_province_list.next_object()
if accuracy_province:
accuracy_province_id = accuracy_province.id if accuracy_province.id else ''
province = await AdministrativeDivision.find_one({
'code':
accuracy_province_id.get('province_code'),
'record_flag':
1,
'parent_code':
None
})
province_title = ''
if province:
province_title = province.title
dt = accuracy_province_id.get('created_dt')
if accuracy_province_id.get(
'created_dt') not in accuracy_date_list:
accuracy_date_list.append(
accuracy_province_id.get('created_dt'))
if accuracy_province.t_total == 0:
accuracy_province_dict[province_title + ' ' +
dt] = 0
else:
accuracy_province_dict[
province_title + ' ' +
dt] = (accuracy_province.t_correct /
accuracy_province.t_total) * 100
province_dict = {}
# 总的题目
total_quantity_list = []
# 总的答对题目
correct_quantity_list = []
# 总的正确率
while await total_accuracy.fetch_next:
province_stat = total_accuracy.next_object()
if province_stat:
province_code = province_stat.id if province_stat.id else '000000'
total = province_stat.t_total if province_stat.t_total else 0
correct = province_stat.t_correct if province_stat.t_correct else 0
province = await AdministrativeDivision.find_one({
'code':
province_code,
'record_flag':
1,
'parent_code':
None
})
province_title = ''
if province:
province_title = province.title
province_dict[province_title] = round(
correct / total * 100 if total > 0 else 0, 2)
total_quantity_list.append(total)
correct_quantity_list.append(correct)
# 次数的sheet
print(date_list)
worksheet = workbook.add_worksheet(name='次数')
worksheet.merge_range(0,
0,
0,
len(date_list) + 1,
'公民参与科学素质学习状况',
cell_format=title_format)
worksheet.write_string(1,
0,
'已累计次数',
cell_format=data_center_format)
worksheet.merge_range(1,
2,
1,
len(date_list) + 1,
'导出时间:' + export_time,
cell_format=data_center_format)
worksheet.merge_range(2,
0,
3,
0,
'省份',
cell_format=data_center_format)
worksheet.merge_range(2,
1,
3,
1,
'人数汇总(人)',
cell_format=data_center_format)
worksheet.merge_range(2,
2,
2,
6,
'每月新增人数(人)',
cell_format=data_center_format)
insert_excel(date_list, worksheet, data_center_format,
province_title_list, province_map,
month_province_dict)
# 人数的sheet
worksheet = workbook.add_worksheet(name='人数')
worksheet.merge_range(0,
0,
0,
len(member_date_list) + 1,
'公民参与科学素质学习状况',
cell_format=title_format)
worksheet.write_string(1,
0,
'已累计人数',
cell_format=data_center_format)
worksheet.merge_range(1,
2,
1,
len(member_date_list) + 1,
'导出时间：' + export_time,
cell_format=data_center_format)
worksheet.merge_range(2,
0,
3,
0,
'省份',
cell_format=data_center_format)
worksheet.merge_range(2,
1,
3,
1,
'人数汇总(人/次)',
cell_format=data_center_format)
worksheet.merge_range(2,
2,
2,
6,
'每月新增人数(人/次)',
cell_format=data_center_format)
insert_excel(member_date_list, worksheet, data_center_format,
province_title_list, province_map,
member_province_dict)
# 正确率的sheet
worksheet = workbook.add_worksheet(name='正确率')
total_province_accuracy = round(
sum(correct_quantity_list) / sum(total_quantity_list) *
100, 2)
worksheet.merge_range(0,
0,
0,
len(date_list) + 1,
'公民参与科学素质学习状况',
cell_format=title_format)
worksheet.merge_range(1,
0,
1,
1,
'总体正确率' + str(total_province_accuracy) +
'%',
cell_format=data_center_format)
worksheet.merge_range(1,
2,
1,
len(date_list) + 1,
'导出时间:' + export_time,
cell_format=data_center_format)
worksheet.merge_range(2,
0,
3,
0,
'省份',
cell_format=data_center_format)
worksheet.merge_range(2,
1,
3,
1,
'正确率',
cell_format=data_center_format)
worksheet.merge_range(2,
2,
2,
6,
'每月正确率波动(%)',
cell_format=data_center_format)
for index, date in enumerate(accuracy_date_list):
worksheet.write_string(3,
2 + index,
date,
cell_format=data_center_format)
for index, province_title in enumerate(province_title_list):
worksheet.write_string(4 + index,
0,
province_title,
cell_format=data_center_format)
worksheet.write_string(4 + index,
1,
str(province_dict[province_title]),
cell_format=data_center_format)
province_map[province_title] = 4 + index
for month_province, value in accuracy_province_dict.items():
value = round(value, 2)
position = Position(
month_province.split(' ')[0],
province_map[month_province.split(' ')[0]], 0)
order = accuracy_date_list.index(
month_province.split(' ')[1])
worksheet.write_string(position.row, 2 + order, str(value))
workbook.close()
self.set_header(
'Content-Type',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
self.set_header(
'Content-Disposition',
"attachment;filename*=utf-8''{}.xlsx".format(
quote(chart_name.encode('utf-8'))))
self.write(output.getvalue())
self.finish()
except Exception:
logger.error(traceback.format_exc())
