async def do_repair():
s_dt = datetime.now().replace(year=2019,
month=7,
day=3,
hour=9,
minute=58,
second=59,
microsecond=858)
e_dt = datetime.now().replace(year=2019,
month=7,
day=10,
hour=16,
minute=0,
second=0,
microsecond=0)
index = 0
cursor = MemberGameHistory.find(
{
'created_dt': {
"$gte": s_dt,
"$lte": e_dt
}
},
read_preference=ReadPreference.PRIMARY).skip(index).batch_size(256)
while await cursor.fetch_next:
try:
history = cursor.next_object()
await start_dashboard_report_statistics(history)
index += 1
print('has repair', index, history.cid)
await sleep(0.5)
except StopIteration:
break
except CursorNotFound:
cursor = MemberGameHistory.find(
{
'created_dt': {
"$gte": s_dt,
"$lte": e_dt
}
},
read_preference=ReadPreference.PRIMARY).skip(index).batch_size(
256)
def get_fight_history_models():
# 获取对战历史
return MemberGameHistory.sync_find(
{
'status': STATUS_RESULT_GAME_PK_WIN,
'fight_datetime': {
'$ne': None
}
},
read_preference=ReadPreference.PRIMARY).sort([('fight_datetime', ASC)
]).batch_size(32)
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()
async def _get_learning_code(history):
"""
获取学习日编码
:param member_cid: 会员CID
:return:
"""
if history:
l_code = RedisCache.get('LEARNING_STATISTICS_CODE_%s' % history.cid)
if not l_code:
prev_datetime = copy.deepcopy(history.fight_datetime).replace(
hour=23, minute=59, second=59,
microsecond=999999) - datetime.timedelta(days=1)
match_stage = MatchStage({
'member_cid': history.member_cid,
'fight_datetime': {
'$lte': prev_datetime
}
})
project_stage = ProjectStage(date={
'$dateToString': {
'format': '%Y%m%d',
'date': '$fight_datetime'
}
})
group_stage = GroupStage('date')
mgh_cursor = MemberGameHistory.aggregate(
[match_stage, project_stage, group_stage])
# mch_cursor = MemberCheckPointHistory.aggregate([match_stage, project_stage, group_stage])
tmp_dict = {}
while await mgh_cursor.fetch_next:
mgh = mgh_cursor.next_object()
if mgh:
tmp_dict[mgh.id] = int(mgh.id)
# while await mch_cursor.fetch_next:
# mch = mch_cursor.next_object()
# if mch:
# tmp_dict[mch.id] = int(mch.id)
l_code = 1
if tmp_dict:
l_code = len(tmp_dict.keys()) + 1
remain_seconds = get_day_remain_seconds()
if remain_seconds:
RedisCache.set(
'LEARNING_STATISTICS_CODE_%s' % history.member_cid, l_code,
remain_seconds)
else:
l_code = int(l_code)
return l_code
return None
async def get_fight_history_models():
begin_dt = datetime.datetime.now().replace(year=2019,
month=5,
day=16,
hour=0,
minute=0,
second=0,
microsecond=0)
end_dt = datetime.datetime.now().replace(year=2019,
month=6,
day=14,
hour=0,
minute=0,
second=0,
microsecond=0)
# 获取对战历史
return MemberGameHistory.find(
dict(record_flag=1, created_dt={
"$gte": begin_dt,
"$lte": end_dt
}),
read_preference=ReadPreference.PRIMARY).batch_size(32)
def do_init_member_stat(pre_match: dict = {},
skip_num=0,
limit_num=0,
just_return_count=False):
"""
:param pre_match:
:param skip_num:
:param limit_num:
:param just_return_count:
:return:
"""
if not isinstance(just_return_count, bool):
raise Exception('check params(just_return_count)')
stage_list = []
if pre_match:
stage_list.append(MatchStage(pre_match))
stage_list.extend([
UnwindStage("result"),
LookupStage(Member, 'member_cid', 'cid', 'member_list'),
MatchStage({'member_list': {
'$ne': list()
}}),
ProjectStage(
**{
'subject_cid': '$result.subject_cid',
'province_code': {
'$arrayElemAt': ['$member_list.province_code', 0]
},
'city_code': {
'$arrayElemAt': ['$member_list.city_code', 0]
},
'district_code': {
'$arrayElemAt': ['$member_list.district_code', 0]
},
'sex': {
'$arrayElemAt': ['$member_list.sex', 0]
},
'age_group': {
'$arrayElemAt': ['$member_list.age_group', 0]
},
'category': {
'$arrayElemAt': ['$member_list.category', 0]
},
'education': {
'$arrayElemAt': ['$member_list.education', 0]
},
'true_answer': {
'$cond': {
'if': {
'$eq': ['$result.true_answer', True]
},
'then': True,
'else': False
}
},
'created_dt': '$created_dt'
}),
GroupStage(
{
'subject_cid': '$subject_cid',
'province_code': '$province_code',
'city_code': '$city_code',
'district_code': '$district_code',
'sex': '$sex',
'age_group': '$age_group',
'category': '$category',
'education': '$education',
},
answers_list={'$push': '$true_answer'},
created_dt={'$first': '$created_dt'}),
])
if just_return_count:
stage_list.append(CountStage())
data = MemberGameHistory.sync_aggregate(
stage_list,
read_preference=ReadPreference.PRIMARY,
allowDiskUse=True).to_list(1)
return data[0].count
stage_list.append(SortStage([('created_dt', ASC)]))
if skip_num:
stage_list.append(SkipStage(skip_num))
if limit_num:
stage_list.append(LimitStage(limit_num))
cursor = MemberGameHistory.sync_aggregate(
stage_list, read_preference=ReadPreference.PRIMARY,
allowDiskUse=True).batch_size(256)
index = 0
_subject_map = {}
while True:
try:
data = cursor.next()
subject_cid = data.id.get('subject_cid')
subject = _subject_map.get(subject_cid)
if not subject:
subject = Subject.sync_get_by_cid(subject_cid)
_subject_map[subject_cid] = subject
write_member_subject_stat(data, subject)
index += 1
print('has exec %s' % index)
except StopIteration:
break
except Exception:
print(traceback.format_exc())
continue
import os
import sys
from db.models import MemberGameHistory
count = MemberGameHistory.sync_count({})
script_num = int(sys.argv[1])
skip_num = int(count / script_num)
limit_num, other = divmod(count, script_num)
if other:
script_num += 1
print(count, script_num)
for i in range(script_num):
os.system(
'nohup python initialize/report/daily/init_sub.py %s %s %s > daily_init_sub_%s.log 2>&1 &'
% (i, skip_num * i, limit_num, i))
def do_stat(model):
stage_list = [
UnwindStage('result'),
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': '%Y%m%d',
'date': '$created_dt'
}
},
'member_cid': '$member_cid',
'subject_cid': '$result.subject_cid',
'true_answer': '$result.true_answer'
}),
LookupStage(Subject, 'subject_cid', 'cid', 'subject_list'),
ProjectStage(
**{
'daily_code': 1,
'member_cid': 1,
'true_answer': 1,
'dimension': {
'$arrayElemAt': ['$subject_list.dimension_dict', 0]
}
}),
GroupStage(
{
'daily_code': '$daily_code',
'member_cid': '$member_cid',
'dimension': '$dimension'
},
answer_list={'$push': '$true_answer'}),
ProjectStage(
**{
'daily_code': '$_id.daily_code',
'member_cid': '$_id.member_cid',
'dimension': '$_id.dimension',
'correct': {
'$size': {
'$filter': {
'input': '$answer_list',
'as': 'item',
'cond': {
'$and': [{
'$eq': ['$$item', True]
}]
}
}
}
},
'total': {
"$size": "$answer_list"
}
}),
GroupStage({
'member_cid': '$member_cid',
'dimension': '$dimension'
},
daily_list={'$push': '$daily_code'},
correct_list={'$push': '$correct'},
total_list={'$push': '$total'}),
]
cursor = MemberGameHistory.sync_aggregate(
stage_list, allowDiskUse=True, read_preference=ReadPreference.PRIMARY)
t1 = time.time()
index = 0
insert_list = []
while True:
try:
data = cursor.next()
member = Member.sync_get_by_cid(data.id.get('member_cid'))
if not member:
continue
dimension = data.id.get('dimension')
if not dimension:
continue
for i, day in enumerate(data.daily_list):
param = {
'learning_code': get_learning_code(day, data.daily_list),
'member_cid': member.cid,
'dimension': dimension,
'province_code': member.province_code,
'city_code': member.city_code,
'district_code': member.district_code,
'gender': member.sex,
'age_group': member.age_group,
'education': member.education,
'subject_total_quantity': data.total_list[i],
'subject_correct_quantity': data.correct_list[i]
}
insert_list.append(model(**param))
except StopIteration:
break
if len(insert_list) > 5000:
model.sync_insert_many(insert_list)
insert_list = []
index += 1
print('has exec', index)
t2 = time.time()
print('cost:', t2 - t1)
model.sync_insert_many(insert_list)
t3 = time.time()
print('insert', t3 - t2)
city_code = complete_member_city_code(member.province_code, member.city_code)
mls.city_code = city_code
mls.district_code = member.district_code
mls.gender = member.sex
mls.age_group = member.age_group
mls.education = member.education
mls.category = member.category
# 学习总次数
mls.learn_times += 1
# 答题正确数
correct_quantity = _do_count_subject_correct_quantity(mls, history)
# 统计答题正确数的次数
_do_count_correct_quantity(mls, correct_quantity)
# 获取题目详情
subject_list, subject_answer_dict = _do_get_subjects_detail(history)
# 统计维度详情
_do_count_dimension_detail(mls, subject_list, subject_answer_dict)
mls.created_dt = history.created_dt
mls.updated_dt = history.updated_dt
mls.sync_save()
except ValueError:
trace_i = traceback.format_exc()
print(trace_i)
if __name__ == '__main__':
history_list = MemberGameHistory.sync_find(read_preference=ReadPreference.PRIMARY).batch_size(128)
for index, history in enumerate(history_list):
init_member_learning_day_statistics(history)
print(index + 1, history.cid)
mdds.updated_dt = history.updated_dt
if mdds not in insert_mdds_list:
mdds.sync_save()
if insert_mdds_list:
MemberDailyDimensionStatistics.sync_insert_many(insert_mdds_list)
except ValueError:
trace_i = traceback.format_exc()
result['msg'] = trace_i
print(trace_i)
def _get_daily_code(dt: datetime.datetime = None):
"""
获取对接标识
:return:
"""
if not dt:
dt = datetime.datetime.now()
return datetime2str(dt, date_format='%Y%m%d000000')
if __name__ == '__main__':
t1 = datetime.datetime.now().replace(year=2019, month=5, day=5, hour=12, minute=0, second=0, microsecond=0)
t2 = datetime.datetime.now().replace(year=2019, month=5, day=17, hour=12, minute=0, second=0, microsecond=0)
history_list = MemberGameHistory.sync_find({'created_dt': {'$gte': t1, '$lte': t2}},
read_preference=ReadPreference.PRIMARY).batch_size(128)
for index, history in enumerate(history_list):
start_dashboard_report_statistics_without_delay(history)
print(index + 1, history.cid)
model.sync_insert_many(insert_mdds_list)
except ValueError:
trace_i = traceback.format_exc()
result['msg'] = trace_i
print(trace_i)
def _get_daily_code(dt: datetime.datetime = None):
"""
获取对接标识
:return:
"""
if not dt:
dt = datetime.datetime.now()
return datetime2str(dt, date_format='%Y%m%d000000')
if __name__ == '__main__':
index = int(sys.argv[1])
model = create_model(MemberDailyDimensionStatistics, index)
skip_num = int(sys.argv[2])
limit_num = int(sys.argv[3])
print(index, skip_num, limit_num)
history_list = MemberGameHistory.sync_find(read_preference=ReadPreference.PRIMARY).skip(skip_num).limit(
limit_num).batch_size(128)
for index, history in enumerate(history_list):
start_dashboard_report_statistics_without_delay(history, model)
print(index + 1, history.cid)
from commons.common_utils import datetime2str
from db import SEX_DICT, TYPE_AGE_GROUP_DICT, TYPE_EDUCATION_DICT
from db.models import MemberGameHistory, Member, AdministrativeDivision
from pymongo import ReadPreference
from xlsxwriter import Workbook
six_month_ago = datetime.now().replace(year=2018,
month=10,
day=1,
hour=0,
minute=0,
second=0,
microsecond=0)
cursor = MemberGameHistory.sync_find({'created_dt': {
'$gte': six_month_ago
}},
read_preference=ReadPreference.PRIMARY)
workbook = Workbook('近6个月的游戏数据.xlsx')
worksheet = workbook.add_worksheet()
menu = ['序号', '昵称', '性别', '年龄段', '受教育程度', '省份', '城市', '总题数', '答对题目数量', '答题时间']
for index, m in enumerate(menu):
worksheet.write_string(0, index, m)
post_code_map = {}
row = 0
while True:
row += 1
try:
history = cursor.next()
worksheet.write_number(row, 0, row)
def get_fight_history_models():
# 获取对战历史
return MemberGameHistory.sync_find(
dict(record_flag=1),
read_preference=ReadPreference.PRIMARY).batch_size(32)
def do_init(model):
"""
:return:
"""
b_dt = datetime.now().replace(month=5, day=18, hour=0, minute=0, second=0)
e_dt = datetime.now().replace(month=6, day=13, hour=0, minute=0, second=0)
cursor = MemberGameHistory.sync_aggregate(
[
MatchStage({'created_dt': {
'$gte': b_dt,
'$lte': e_dt
}}),
UnwindStage("result"),
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': '%Y%m%d000000',
'date': '$created_dt'
}
},
'member_cid': 1,
'subject_cid': '$result.subject_cid',
'true_answer': "$result.true_answer",
'created_dt': 1
}),
GroupStage(
{
'daily_code': '$daily_code',
'member_cid': '$member_cid',
'subject_cid': '$subject_cid'
},
answer_list={'$push': '$true_answer'},
created_dt={'$first': '$created_dt'}),
LookupStage(Member, '_id.member_cid', 'cid', 'member_list'),
LookupStage(Subject, '_id.subject_cid', 'cid', 'subject_list'),
MatchStage({
'member_list': {
'$ne': []
},
'subject_list': {
'$ne': []
}
}),
ProjectStage(
**{
'member_cid': '$_id.member_cid',
'dimension': {
"$arrayElemAt": ['$subject_list.dimension_dict', 0]
},
'daily_code': '$_id.daily_code',
'province_code': {
"$arrayElemAt": ['$member_list.province_code', 0]
},
'city_code': {
"$arrayElemAt": ['$member_list.city_code', 0]
},
'district_code': {
"$arrayElemAt": ['$member_list.district_code', 0]
},
'gender': {
"$arrayElemAt": ['$member_list.sex', 0]
},
'age_group': {
"$arrayElemAt": ['$member_list.age_group', 0]
},
'education': {
"$arrayElemAt": ['$member_list.education', 0]
},
'correct': {
'$size': {
'$filter': {
'input': '$answer_list',
'as': 'item',
'cond': {
'$and': [{
'$eq': ['$$item', True]
}]
}
}
}
},
'total': {
"$size": "$answer_list"
},
'created_dt': 1
}),
GroupStage(
{
'daily_code': '$daily_code',
'member_cid': '$member_cid',
'dimension': '$dimension'
},
province_code={'$first': "$province_code"},
city_code={'$first': "$city_code"},
district_code={'$first': "$district_code"},
gender={'$first': "$gender"},
age_group={'$first': "$age_group"},
education={'$first': "$education"},
correct={'$sum': "$correct"},
total={'$sum': '$total'})
],
allowDiskUse=True,
read_preference=ReadPreference.PRIMARY)
index = 0
while True:
try:
data = cursor.next()
param = {
'daily_code': data.id.get('daily_code'),
'member_cid': data.id.get('member_cid'),
'dimension': data.id.get('dimension'),
'province_code': data.province_code,
'city_code': data.city_code,
'district_code': data.district_code,
'gender': data.gender,
'age_group': data.age_group,
'education': data.education,
'subject_total_quantity': data.total,
'subject_correct_quantity': data.correct,
'created_dt': data.created_dt
}
model(**param).sync_save()
index += 1
print('has exec', index)
except StopIteration:
break
except AttributeError as e:
print(e)
continue
print('done.')
def get_fight_history_models():
# 获取对战历史
return MemberGameHistory.sync_find(dict(record_flag=1)).batch_size(32)
def do_init(model, skip_num, limit_num):
"""
:param model:
:param skip_num:
:param limit_num:
:return:
"""
stage_list = [
MatchStage({
'created_dt': {
"$gte":
datetime.now().replace(day=14, hour=18, minute=15, second=00),
"$lte":
datetime.now().replace(day=17, hour=9, minute=22, second=00)
}
}),
ProjectStage(
**{
'daily_code': {
"$dateToString": {
'format': '%Y%m%d000000',
'date': "$created_dt"
}
},
'member_cid': 1,
'correct': {
'$size': {
'$filter': {
'input': '$result.true_answer',
'as': 'item',
'cond': {
'$and': [{
'$eq': ['$$item', True]
}]
}
}
}
},
'total': {
'$size': '$result'
},
'result': 1,
'created_dt': 1
}),
GroupStage({
'daily_code': '$daily_code',
'member_cid': '$member_cid'
},
correct_list={'$push': "$correct"},
result_list={"$push": "$result"},
created_dt={'$first': "$created_dt"},
correct={'$sum': '$correct'},
total={'$sum': '$total'},
learn_times={'$sum': 1}),
LookupStage(Member, '_id.member_cid', 'cid', 'member_list'),
MatchStage({'member_list': {
'$ne': []
}}),
ProjectStage(
**{
'daily_code': "$_id.daily_code",
'member_cid': '$_id.member_cid',
'correct': '$correct',
'total': "$total",
'correct_list': "$correct_list",
'learn_times': '$learn_times',
'province_code': {
"$arrayElemAt": ['$member_list.province_code', 0]
},
'city_code': {
"$arrayElemAt": ['$member_list.city_code', 0]
},
'district_code': {
"$arrayElemAt": ['$member_list.district_code', 0]
},
'gender': {
"$arrayElemAt": ['$member_list.sex', 0]
},
'age_group': {
"$arrayElemAt": ['$member_list.age_group', 0]
},
'education': {
"$arrayElemAt": ['$member_list.education', 0]
},
'result_list': 1,
'created_dt': 1
}),
SortStage([('daily_code', ASC), ('member_cid', ASC)]),
]
if skip_num:
stage_list.append(SkipStage(skip_num))
if limit_num:
stage_list.append(LimitStage(limit_num))
cursor = MemberGameHistory.sync_aggregate(
stage_list, allowDiskUse=True, read_preference=ReadPreference.PRIMARY)
index = 0
while True:
try:
index += 1
print('has exec %s.' % index)
data = cursor.next()
param = {
'daily_code':
data.daily_code,
'member_cid':
data.member_cid,
'province_code':
data.province_code,
'city_code':
data.city_code,
'district_code':
data.district_code,
'gender':
data.gender,
'age_group':
data.age_group,
'education':
data.education,
'learn_times':
data.learn_times,
'subject_total_quantity':
data.total,
'subject_correct_quantity':
data.correct,
'quantity_detail':
dict(Counter(map(lambda x: str(x), data.correct_list))),
'dimension_detail':
get_dimension_detail(data.result_list),
'created_dt':
data.created_dt,
'updated_dt':
data.created_dt
}
model(**param).sync_save()
except StopIteration:
break
except AttributeError as e:
print(e)
continue
#! /usr/bin/python3
from datetime import datetime
from db.models import MemberGameHistory, Member
member_game_history_list = MemberGameHistory.sync_find({
'updated_dt': {
'$gte': datetime.now().replace(2019, 1, 29, 16, 30, 0, 0)
}
})
i, j = 1, 1
for his in member_game_history_list:
# step1: 将段位减1
if his.dan_grade == 12:
# step2: 恢复原有钻石
member = Member.sync_find_one({'cid': his.member_cid})
member.diamond += 6000
member.sync_save()
j += 1
his.dan_grade -= 1
his.sync_save()
i += 1
print('dan_grade changed count: %s' % i)
print('diamond changed count: %s' % j)