async def post(self):
res_dict = {'code': 0}
race_cid = self.get_argument('race_cid')
# 0代表人数，1代表人次
stat_type = int(self.get_argument('stat_type', 0))
# race = Race.sync_get_by_cid(race_cid)
try:
cache_key = get_cache_key(race_cid, stat_type)
data = RedisCache.get(cache_key)
if not data:
match = {'race_cid': race_cid}
# 安徽特殊处理，需整合六安数据
# 弃用，不整合数据
# if race.province_code == '340000':
# match = {'race_cid': {'$in': [race_cid, CITY_RACE_CID]}}
match_stage = MatchStage(match)
# 活动分组,具体到市,increase_enter_count人数,enter_times_sum次数
if stat_type == 0:
# 人数
group_stage = GroupStage(
{
'province': '$province',
'city': '$city'
},
sum={'$sum': '$increase_enter_count'})
else:
group_stage = GroupStage(
{
'province': '$province',
'city': '$city'
},
sum={'$sum': '$enter_times'})
cursor = RaceMemberEnterInfoStatistic.aggregate(
[match_stage, group_stage,
SortStage([('sum', DESC)])])
res_dict['code'] = 1
res_dict['data'] = await do_init_map_data(cursor)
logger_cache.info('cache_key: %s' % cache_key)
RedisCache.set(cache_key, msgpack.packb(res_dict['data']),
23 * 60 * 60)
else:
res_dict['data'] = msgpack.unpackb(data, raw=False)
res_dict['code'] = 1
return res_dict
except Exception:
logger.error(traceback.format_exc())
res_dict['code'] = -1
return res_dict
async def do_stat_member_times(race_cid: str,
time_match: MatchStage,
group_id='district',
name_match=MatchStage({}),
district_title="",
name="",
time_num="",
is_integrate=""):
"""
统计参赛人次
:param race_cid:
:param time_match:
:param group_id:
:param name_match
:param district_title
:param name
:param time_num
:param is_integrate
:return:
"""
if not race_cid:
return
cache_key = get_cache_key(
race_cid,
'member_times_{district}_{name}_{time_num}_{district_title}_{is_integrate}'
.format(district=group_id,
name=name,
time_num=time_num,
district_title=district_title,
is_integrate=is_integrate))
member_times_data = RedisCache.get(cache_key)
data_cache = ''
if member_times_data:
data_cache = msgpack.unpackb(member_times_data, raw=False)
if not member_times_data or not data_cache:
# race = await Race.get_by_cid(race_cid)
all_match = {'race_cid': race_cid}
# 安徽特殊处理，需整合六安数据(弃用)
if is_integrate:
all_match = {'race_cid': {'$in': [race_cid, CITY_RACE_CID]}}
district_match = MatchStage({})
if district_title:
district_match = MatchStage({'district': district_title})
all_match['town'] = {'$ne': None}
cursor = RaceMemberEnterInfoStatistic.aggregate([
MatchStage(all_match), district_match, time_match, name_match,
GroupStage(group_id, sum={'$sum': '$enter_times'}),
SortStage([('sum', DESC)])
])
times_data = await stat_data(cursor)
logger_cache.info('cache_key: %s' % cache_key)
RedisCache.set(cache_key, msgpack.packb(times_data), 23 * 60 * 60)
return times_data
return msgpack.unpackb(member_times_data, raw=False)
def get_member_info(thread_num, race_member_list, checkPointCidList, lastCheckPoint, checkPointMap):
exportedMemberList = []
for race_member in race_member_list:
# raceMapping = race_member.race_list[0]
raceMapping = race_member
race_cid = raceMapping.race_cid
member = race_member.member_list[0]
memberCid = member.cid
red_match = MatchStage(
{'race_cid': race_cid, 'member_cid': memberCid, 'draw_status': 0, 'draw_dt': {'$ne': None},
'award_cid': {'$ne': None}, 'record_flag': 1})
red_project = ProjectStage(**{"member_cid": 1, "award_amount": 1})
red_group = GroupStage('member_cid', count={'$sum': 1}, amount={'$sum': '$award_amount'})
# redPacketsOfMember = RedPacketBox.sync_find(
# {'race_cid': race_cid, 'member_cid': memberCid, 'award_cid': {'$ne': None}, 'record_flag': 1})
# eachAmountOfRedPacket = [redPacket.award_amount for redPacket in redPacketsOfMember]
redPacketsOfMemberCursor = RedPacketBox.sync_aggregate([red_match, red_project, red_group]).batch_size(50)
exportedMember = MemberInfoExportedModel()
exportedMember.open_id = member.open_id
exportedMember.member_cid = memberCid
exportedMember.nick = member.nick_name
exportedMember.firstTimeOfEnroll = member.created_dt
city = raceMapping.auth_address.get('city', '')
exportedMember.city = city if not city is None else ''
district = raceMapping.auth_address.get('district', '')
exportedMember.district = district if not district is None else ''
mobile = getattr(raceMapping, 'mobile', '')
if mobile is None:
exportedMember.mobile = member.mobile
else:
exportedMember.mobile = mobile
check_point_cid = getattr(raceMapping, 'race_check_point_cid', None)
if check_point_cid is None:
exportedMember.currentCheckPoint = "1"
elif check_point_cid == lastCheckPoint:
exportedMember.currentCheckPoint = "已通关"
else:
exportedMember.currentCheckPoint = str(checkPointMap[check_point_cid])
answerTimes = MemberCheckPointHistory.sync_find(
{'member_cid': memberCid, 'check_point_cid': {'$in': checkPointCidList}, 'record_flag': 1}).to_list(None)
exportedMember.answerTimes = len(answerTimes)
try:
redPacketsOfMember = redPacketsOfMemberCursor.next()
if redPacketsOfMember:
exportedMember.totalNumOfRedPacket = redPacketsOfMember.count
exportedMember.totalAmountOfRedPacket = round(redPacketsOfMember.amount, 2)
except StopIteration:
pass
exportedMemberList.append(exportedMember)
print(thread_num, member.cid, exportedMember.nick, exportedMember.city, exportedMember.district,
exportedMember.mobile,
exportedMember.currentCheckPoint, exportedMember.answerTimes, exportedMember.totalNumOfRedPacket,
exportedMember.totalAmountOfRedPacket)
return exportedMemberList
def export_new_info(workbook, race_cid, s_daily_code='', e_daily_code=''):
"""
新增信息
:param workbook:
:param race_cid:
:param s_daily_code:
:param e_daily_code:
:return:
"""
match = MatchStage({'race_cid': race_cid})
if s_daily_code and e_daily_code:
match['daily_code'] = {'$gte': s_daily_code, '$lte': e_daily_code}
group = GroupStage(
{
'daily_code': '$daily_code',
'race_cid': '$race_cid'
},
enter_times={'$sum': '$enter_times'},
draw_red_packet_count={'$sum': '$draw_red_packet_count'},
draw_red_packet_amount={'$sum': '$draw_red_packet_amount'},
new_user_count={'$sum': '$is_new_user'})
sort = SortStage([('_id.daily_code', ASC)])
total_user_count = 0
total_enter_times = 0
total_draw_red_packet_count = 0
total_draw_red_packet_amount = 0
exported_member_list = MemberStatisticInfo.sync_aggregate(
[match, group, sort]).to_list(None)
sheet = workbook.add_worksheet("新增信息")
sheet.merge_range(0, 0, 0, 1, '日期')
sheet.merge_range(0, 2, 0, 3, '新增会员数')
sheet.merge_range(0, 4, 0, 5, '新增答题次数')
sheet.merge_range(0, 6, 0, 7, '新增红包领取数')
sheet.merge_range(0, 8, 0, 9, '新增红包领取金额')
sheet.merge_range(0, 10, 0, 11, '总会员数')
sheet.merge_range(0, 12, 0, 13, '总答题次数')
sheet.merge_range(0, 14, 0, 15, '总红包领取数')
sheet.merge_range(0, 16, 0, 17, '总红包领取金额')
for index, info in enumerate(exported_member_list):
row = index + 1
sheet.merge_range(row, 0, row, 1, info.id.get('daily_code'))
sheet.merge_range(row, 2, row, 3, info.new_user_count)
sheet.merge_range(row, 4, row, 5, info.enter_times)
sheet.merge_range(row, 6, row, 7, info.draw_red_packet_count)
sheet.merge_range(row, 8, row, 9, info.draw_red_packet_amount)
total_user_count += info.new_user_count
total_enter_times += info.enter_times
total_draw_red_packet_count += info.draw_red_packet_count
total_draw_red_packet_amount += info.draw_red_packet_amount
sheet.merge_range(row, 10, row, 11, total_user_count)
sheet.merge_range(row, 12, row, 13, total_enter_times)
sheet.merge_range(row, 14, row, 15, total_draw_red_packet_count)
sheet.merge_range(row, 16, row, 17, total_draw_red_packet_amount)
def get_red_packet_info(race_cid, start_date, end_date):
"""
统计当日该会员参加活动时，红包领取数量、领取金额、发放数量
:param race_cid:
:param start_date:
:param end_date:
:return:
"""
cursor = RedPacketBox.sync_aggregate([
MatchStage({
'draw_dt': {
"$gte": start_date,
"$lt": end_date
},
'race_cid': race_cid,
'award_cid': {
'$ne': None
}
}),
GroupStage({
'member_cid': '$member_cid',
'draw_status': '$draw_status'
},
amount={'$sum': '$award_amount'},
sum={"$sum": 1})
])
ret = {}
while True:
try:
data = cursor.next()
_member = data.id.get('member_cid')
_status = data.id.get('draw_status')
_value = ret.get(_member)
if not _value:
_value = {
'grant_count': 0,
'grant_amount': 0,
'draw_count': 0,
'draw_amount': 0
}
_value['grant_count'] += data.sum
_value['grant_amount'] += data.amount
if _status == 0:
_value['draw_count'] += data.sum
_value['draw_amount'] += data.amount
ret[_member] = _value
except StopIteration:
break
return ret
def test3():
cursor = RaceMapping.sync_aggregate([
MatchStage(
{'race_cid': "CA755167DEA9AA89650D11C10FAA5413",
'member_cid':'9809FD6ED3F9534F87252A48018BE965','auth_address.province': {'$ne': None}, "record_flag": 1,}),
GroupStage({'member_cid': '$member_cid'}, race_list={'$push': '$$ROOT'}),
LookupStage(Member, '_id.member_cid', 'cid', 'member_list'),
MatchStage({'member_list': {'$ne': []}})
], allowDiskUse=True)
for race_member in cursor:
print(len(race_member.race_list))
for race in race_member.race_list:
print(race.cid,'---',race.mobile)
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 change_duplicate_race_mapping(race_cid: str):
print("race_cid:%s" % race_cid)
match_stage = MatchStage({'race_cid': race_cid, 'record_flag': 1})
project_stage = ProjectStage(**{
"race_cid": 1,
"member_cid": 1,
"race_check_point_cid": 1
})
group_stage = GroupStage({'_id': '$member_cid'},
count={'$sum': 1},
duplicate_list={'$push': '$$ROOT'})
match_stage_count = MatchStage({'count': {'$gt': 1}})
project_stage_1 = ProjectStage(**{'duplicate_list': 1})
duplicate_race_mappings = RaceMapping.sync_aggregate([
match_stage, project_stage, group_stage, match_stage_count,
project_stage_1
]).to_list(None)
count = 1
if len(duplicate_race_mappings) > 0:
for duplicate_race_mapping in duplicate_race_mappings:
print('第%d个:' % count)
print(duplicate_race_mapping.duplicate_list)
duplicate_record_ids = [
x._id for x in duplicate_race_mapping.duplicate_list
]
not_need_index = 0 # 确定record_flag为1的元素
for index, value in enumerate(
duplicate_race_mapping.duplicate_list):
if value.race_check_point_cid:
not_need_index = index
duplicate_record_ids.pop(not_need_index)
print("record_flag需置为0的记录Id:")
print(duplicate_record_ids)
update_requests = []
for object_id in duplicate_record_ids:
update_requests.append(
UpdateOne({'_id': object_id}, {'$set': {
'record_flag': 0
}}))
RaceMapping.sync_update_many(update_requests)
print("-------END:record_flag已置为0---------------")
count += 1
else:
print("-------未找到member_cid重复的记录-------")
print("-------结束处理活动-------")
def test2(race_cid):
total = MemberStatisticInfo.sync_count({
'race_cid': race_cid,
'is_new_user': 1
})
cursor = MemberStatisticInfo.sync_aggregate([
MatchStage({
'race_cid': race_cid,
'is_new_user': 1
}),
GroupStage('member_cid', count={'$sum': 1}),
MatchStage({'count': {
'$gt': 1
}})
])
for stat in cursor:
print(stat.id, stat.count)
cursor1 = MemberStatisticInfo.sync_find({'member_cid': stat.id})
for s in cursor1:
print(s.daily_code, s.is_new_user, s.is_special_user)
print(total)
async def post(self):
res_dict = {'code': 0}
race_cid = '3040737C97F7C7669B04BC39A660065D'
# 0代表人数，1代表人次
stat_type = int(self.get_argument('stat_type', 0))
user = self.get_current_user()
region = RedisCache.get(user.login_name)
# region 'province' 报表展示市得数据, 'city': 报表展示得是区得数据
if not region:
region_code_list = user.manage_region_code_list
for region_code in region_code_list:
city_list = await AdministrativeDivision.find({
'parent_code':
'340000'
}).to_list(None)
total_code_list = [city.code for city in city_list]
total_code_list.append("340000")
if region_code in total_code_list:
region_code = region_code
RedisCache.set(user.login_name,
region_code,
timeout=24 * 60 * 60)
region = region_code
break
try:
early_morning = datetime.now().replace(hour=0,
minute=0,
second=0,
microsecond=0)
end_daily_code = format(early_morning, '%Y%m%d')
cache_key = get_special_cache_key(race_cid, stat_type)
data = RedisCache.get(cache_key)
if region == "340000":
if not data:
match = {'race_cid': race_cid}
match_stage = MatchStage(match)
# 活动分组,具体到市,increase_enter_count人数,enter_times_sum次数
name = "六安市"
city = await AdministrativeDivision.find_one(
{"title": name})
district_title_list = await AdministrativeDivision.distinct(
'title', {'parent_code': city.code})
district_match = MatchStage(
{"district": {
'$in': district_title_list
}})
if stat_type == 0:
# 人数
group_stage = GroupStage(
{
'province': '$province',
'city': '$city'
},
sum={'$sum': '$increase_enter_count'})
city_data_dict = await do_stat_member_count(
CITY_RACE_CID,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
district_match=district_match,
name=name)
else:
group_stage = GroupStage(
{
'province': '$province',
'city': '$city'
},
sum={'$sum': '$enter_times'})
city_data_dict = await do_stat_member_times(
CITY_RACE_CID,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
name=name)
cursor = RaceMemberEnterInfoStatistic.aggregate(
[match_stage, group_stage,
SortStage([('sum', DESC)])])
city_total_num = sum(list(city_data_dict.values()))
res_dict['code'] = 1
res_dict['area'] = 'province'
prov_data_dict = await do_init_map_data(cursor)
city_list = prov_data_dict[0].get('city_list')
for index, city_dict in enumerate(city_list):
if '六安市' == city_dict.get('title'):
data = city_dict['data']
data += city_total_num
temp = {'title': '六安市', 'data': data}
city_list.remove(city_dict)
city_list.insert(index, temp)
break
prov_data_dict[0]['city_list'] = city_list
res_dict['data'] = prov_data_dict
logger_cache.info('cache_key: %s' % cache_key)
RedisCache.set(cache_key, msgpack.packb(res_dict['data']),
23 * 60 * 60)
else:
res_dict['data'] = msgpack.unpackb(data, raw=False)
res_dict['area'] = 'province'
res_dict['code'] = 1
return res_dict
else:
_city = await AdministrativeDivision.find_one({'code': region})
if stat_type == 1:
name = _city.title
data_dict = await do_stat_member_times(
race_cid,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
name=name)
if name == "六安市":
city_count_data_dict = await do_stat_member_times(
CITY_RACE_CID,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
name=name)
total_times_dict = {}
for key, value in data_dict.items():
if key not in total_times_dict:
total_times_dict[key] = value
else:
total_times_dict[key] += value
for key, value in city_count_data_dict.items():
if key not in total_times_dict:
total_times_dict[key] = value
else:
total_times_dict[key] += value
data_dict = total_times_dict
data_list, total = await get_drill_district_data(
data_dict, name)
res_dict['data'] = [{
'title': name,
"data": total,
"city_list": data_list
}]
res_dict['code'] = 1
res_dict['area'] = 'city'
else:
name = _city.title
city = await AdministrativeDivision.find_one(
{"title": name})
district_title_list = await AdministrativeDivision.distinct(
'title', {'parent_code': city.code})
district_match = MatchStage(
{"district": {
'$in': district_title_list
}})
data_dict = await do_stat_member_count(
race_cid,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
district_match=district_match,
name=name)
if name == "六安市":
city_data_dict = await do_stat_member_count(
CITY_RACE_CID,
time_match=MatchStage(
{'daily_code': {
'$lt': end_daily_code
}}),
group_id="district",
name_match=MatchStage({'city': name}),
district_match=district_match,
name=name)
total_dict = {}
for key, value in data_dict.items():
if key not in total_dict:
total_dict[key] = value
else:
total_dict[key] += value
for key, value in city_data_dict.items():
if key not in total_dict:
total_dict[key] = value
else:
total_dict[key] += value
data_dict = total_dict
data_list, total = await get_drill_district_data(
data_dict, name)
res_dict['data'] = [{
'title': name,
"data": total,
"city_list": data_list
}]
res_dict['code'] = 1
res_dict['area'] = 'city'
return res_dict
except Exception:
logger.error(traceback.format_exc())
res_dict['code'] = -1
return res_dict
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 statistic_member_info(race_cid: str, province_code, yesterday):
"""
会员信息导入，只根据race_mapping表中race_cid删选,没有过滤address为空的
:param race_cid:
:return:
"""
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': province_code})
district_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': {
'$in': city_code_list
}})
# race_member_match = {"race_cid": race_cid, 'record_flag': 1, 'auth_address.province': {'$ne': None},
# 'auth_address.city': {"$in": city_name_list},
# 'auth_address.district': {"$in": district_name_list},
# 'created_dt': {'$lte': datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)}}
# race_member_match = {"race_cid": race_cid, 'record_flag': 1, 'auth_address.province': {'$ne': None},
# 'auth_address.city': {"$in": city_name_list},
# 'auth_address.district': {"$in": district_name_list},
# 'created_dt': {'$lte': yesterday}}
match = {"race_cid": race_cid, 'record_flag': 1}
# 当前活动参与会员
# memberCidList = RaceMapping.sync_distinct("member_cid", race_member_match)
cursor = RaceMapping.sync_aggregate([
MatchStage({
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
"record_flag": 1,
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": district_name_list
},
'created_dt': {
'$lte': yesterday
}
}),
GroupStage({'member_cid': '$member_cid'},
race_list={'$push': '$$ROOT'}),
LookupStage(Member, '_id.member_cid', 'cid', 'member_list'),
MatchStage({'member_list': {
'$ne': []
}})
],
allowDiskUse=True)
# memberCidList.sort()
# 当前活动关卡cid
checkPointList = RaceGameCheckPoint.sync_aggregate([MatchStage(match)])
checkPointMap = {}
lastCheckPoint = None
maxCheckPointIndex = 0
for check_point in checkPointList:
checkPointMap[check_point.cid] = check_point.index
if check_point.index > maxCheckPointIndex:
maxCheckPointIndex = check_point.index
lastCheckPoint = check_point.cid
checkPointCidList = list(checkPointMap.keys())
# print("该活动下的会员", len(memberCidList), memberCidList)
# print("该活动下的会员数", len(set(memberCidList)))
# print("该活动下的关卡", len(checkPointCidList), checkPointCidList)
race_member_list = []
for race_member in cursor:
race_member_list.append(race_member)
print("会员总人数", len(race_member_list))
temp_list = splitList(race_member_list, 10000)
threads = []
thread_num = 1
for my_list in temp_list:
print("开启个线程处理会员数", len(my_list))
t = MyThread(get_member_info,
args=(thread_num, my_list, checkPointCidList,
lastCheckPoint, checkPointMap))
threads.append(t)
thread_num += 1
# break
for my_thread in threads:
my_thread.start()
results = []
for each_thread in threads:
each_thread.join()
result = each_thread.get_result()
if len(result) > 0:
results.extend(result)
return results
def repair_records(race_cid):
"""
单个关卡中存在多个抽奖的记录
:param race_cid:
:return:
"""
cursor = RedPacketBox.sync_aggregate([
MatchStage({'race_cid': race_cid, 'member_cid': {'$ne': None}}),
GroupStage({'member_cid': '$member_cid', 'checkpoint_cid': '$checkpoint_cid'}, count={'$sum': 1},
box_list={'$push': '$cid'}),
MatchStage({'count': {'$gte': 2}})
], read_preference=ReadPreference.PRIMARY, allowDiskUse=True).batch_size(256)
with open('./no-history-members.json', 'r') as f:
member_list = json.load(f)
index = count = 0
ck_member_map = {}
fail_list = []
while True:
try:
data = cursor.next()
box_list = RedPacketBox.sync_find({'cid': {'$in': data.box_list}}).to_list(None)
yes_list = list(filter(lambda x: x.award_cid is not None, box_list)) # draw_status --> award_cid
noo_list = list(filter(lambda x: x.award_cid is None, box_list))
# 对于多余的记录，直接改给没有历史记录的游客
if len(yes_list) == 0:
noo_list.pop()
elif len(yes_list) == 1:
pass
elif len(yes_list) >= 2:
noo_list += list(filter(lambda x: x.draw_status == 1, yes_list))
for noo_box in noo_list:
try:
_m_list = ck_member_map.get(noo_box.checkpoint_cid)
if _m_list == 'empty':
fail_list.append(noo_box.cid)
continue
if not _m_list:
_m_list = copy.deepcopy(member_list)
count += 1
while True:
try:
fit_cid = _m_list.pop()
if RedPacketBox.sync_count(
{'checkpoint_cid': noo_box.checkpoint_cid, 'member_cid': fit_cid},
read_preference=ReadPreference.PRIMARY) == 0:
break
except IndexError:
_m_list = 'empty'
print('empty: %s' % noo_box.checkpoint_cid)
break
ck_member_map[noo_box.checkpoint_cid] = _m_list
if _m_list == 'empty':
continue
if not fit_cid:
fail_list.append(noo_box.cid)
continue
noo_box.member_cid = fit_cid
noo_box.award_cid = None
noo_box.award_amount = 0
noo_box.award_msg = None
noo_box.needless['repair_flag'] = 1
noo_box.sync_save()
index += 1
print('has done', index)
except IndexError:
fail_list.append(noo_box.cid)
except StopIteration:
break
print(index, count)
assert fail_list == [], 'fail list not empty, length: %s' % len(fail_list)
async def do_stat_member_accuracy(race_cid: str,
time_match: MatchStage,
group_id,
district_title="",
time_num=""):
"""
统计参赛正确率
:param race_cid:
:param time_match:
:param group_id:
:param district_title
:return:
"""
if not race_cid:
return
cache_key = get_cache_key(
race_cid,
'member_accuracy_{district}_{time_num}_{district_title}'.format(
district=group_id,
time_num=time_num,
district_title=district_title))
member_accuracy_data = RedisCache.get(cache_key)
if member_accuracy_data:
data_cache = msgpack.unpackb(member_accuracy_data, raw=False)
if not member_accuracy_data or not data_cache:
district_match = MatchStage({})
if district_title:
district_match = MatchStage({'district': district_title})
# 安徽特殊处理，需整合六安数据(弃用)
# race = await Race.get_by_cid(race_cid)
all_match = {'race_cid': race_cid}
# if race.province_code == '340000':
# all_match = {'race_cid': {'$in': [race_cid, CITY_RACE_CID]}}
cursor = RaceMemberEnterInfoStatistic.aggregate([
MatchStage(all_match), time_match, district_match,
GroupStage(group_id,
total_correct={'$sum': "$true_answer_times"},
total_count={'$sum': '$answer_times'}),
ProjectStage(
**{
'city': "$city",
'sum': {
'$cond': {
'if': {
'$eq': ['$total_count', 0]
},
'then': 0,
'else': {
'$divide': ['$total_correct', '$total_count']
}
}
}
})
])
accuracy_data = await stat_data(cursor)
logger_cache.info('cache_key: %s' % cache_key)
RedisCache.set(cache_key, msgpack.packb(accuracy_data), 23 * 60 * 60)
return accuracy_data
return msgpack.unpackb(member_accuracy_data, raw=False)
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)
def export_race_enter_position(race_cid: str, title):
"""
导出活动下面参与情况
:return:
"""
if not isinstance(race_cid, str):
raise ValueError("race_cid is not str")
now = datetime.datetime.now()
export_time_list = [
now + datetime.timedelta(days=-n) for n in (range(1, 8))
]
export_time_list.sort()
export_time = [
transform_time_format(export_dt) for export_dt in export_time_list
]
export_time.sort()
workbook = xlsxwriter.Workbook(title + today + ".xlsx")
head_list = [
"新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额"
]
sheet = workbook.add_worksheet(title + "一周数据")
data_center_format = workbook.add_format({
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei'
})
sheet.merge_range(0, 0, 1, 0, "城市", data_center_format)
sheet.merge_range(0, 1, 1, 1, "区县", data_center_format)
sheet.write_string(1, 2, "累计人数")
sheet.write_string(1, 3, "累计参与次数")
for i in range(1, 8):
sheet.merge_range(0, 7 * (i - 1) + 4 + i - 1, 0,
7 * (i - 1) + 4 + i - 1 + 7, export_time[i - 1],
data_center_format)
for head in range(7 * (i - 1) + 4 + i - 1,
7 * (i - 1) + 4 + i - 1 + 7 + 1):
index = head - 8 * (i - 1) - 4
sheet.write_string(1, head, head_list[index])
midnight = (datetime.datetime.now()).replace(hour=0,
minute=0,
second=0,
microsecond=0)
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:
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 = []
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city_code})
# 最基本的人数match
base_quantity_match = {
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
"record_flag": 1,
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": dist_list
}
}
# 最基本的次数match
base_count_match = {
'race_cid': race_cid,
'province': {
'$ne': None
},
"record_$pushflag": 1,
'city': {
"$in": city_name_list
},
'district': {
"$in": dist_list
}
}
# 最基本的参与人数match
base_enter_quantity_match = {
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
"record_flag": 1,
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": dist_list
}
}
# 累计人数
quantity_match_stage = MatchStage({
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
"record_flag": 1,
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": dist_list
},
'created_dt': {
'$lte': midnight
}
})
# 累计次数
daily_code = datetime2str(datetime.datetime.now(), date_format='%Y%m%d')
count_match = {
'race_cid': race_cid,
'province': {
'$ne': None
},
'city': {
'$in': city_name_list
},
'district': {
'$in': dist_list
},
'daily_code': {
'$lt': daily_code
}
}
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)])
quantity_list = RaceMapping.sync_aggregate([
quantity_match_stage, lookup_stage,
ProjectStage(**{
'auth_address': "$auth_address",
"member_list": "$member_list"
}),
MatchStage({'member_list': {
'$ne': []
}}),
GroupStage(
{
'district': '$auth_address.district',
'city': '$auth_address.city'
},
sum={'$sum': 1}), address_sort
])
count_list = ReportRacePeopleStatistics.sync_aggregate([
MatchStage(count_match),
GroupStage({
'city': '$city',
'district': '$district'
},
sum={'$sum': "$total_num"})
])
dis_map = {}
quantity = 0
for index, address in enumerate(quantity_list):
quantity += address.sum
sheet.write_string(index + 2, 0, address.id.get('city'))
sheet.write_string(index + 2, 1, address.id.get('district'))
sheet.write_number(index + 2, 2, address.sum)
if address.id.get('district') not in dis_map:
dis_map[address.id.get('district')] = index + 2
else:
dis_map[address.id.get('district')] += index + 2
count_map = {}
for count in count_list:
district = count.id.get('district')
if district not in count_map:
count_map[district] = count.sum
print(count_map, 'count')
print(dis_map, 'dis_map')
for k, v in count_map.items():
position = dis_map.get(k)
if position:
sheet.write_number(position, 3, v)
# 有答题次数，没有人数的情况，跳过
else:
continue
# 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
quantity_time_dict = {}
# 一个星期的人数的数据
base_quantity_match['created_dt'] = {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte':
(export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
one_week_quantity_list = RaceMapping.sync_aggregate([
MatchStage(base_quantity_match), lookup_stage,
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$created_dt"
}
},
'auth_address': "$auth_address",
"member_list": "$member_list"
}),
MatchStage({'member_list': {
'$ne': []
}}),
GroupStage(
{
"daily_code": "$daily_code",
'district': '$auth_address.district',
'city': '$auth_address.city'
},
sum={'$sum': 1}), address_sort
])
for quantity_data in one_week_quantity_list:
daily = quantity_data.id.get('daily_code')
city = quantity_data.id.get('city')
district = quantity_data.id.get('district')
if city and district:
if daily not in quantity_time_dict:
temp_dict = {}
temp_dict["{city}-{district}".format(
city=city, district=district)] = quantity_data.sum
quantity_time_dict[daily] = temp_dict
else:
quantity_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
quantity_data.sum
})
else:
continue
# 每日参与人数一周的数据
# 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
base_enter_quantity_match['updated_dt'] = {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte':
(export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
enter_quantity_time_dict = {}
one_week_enter_quantity_list = RaceMapping.sync_aggregate([
MatchStage(base_enter_quantity_match), lookup_stage,
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$updated_dt"
}
},
'auth_address': "$auth_address",
"member_list": "$member_list"
}),
MatchStage({'member_list': {
'$ne': []
}}),
GroupStage(
{
"daily_code": "$daily_code",
'district': '$auth_address.district',
'city': '$auth_address.city'
},
sum={'$sum': 1}), address_sort
])
for quantity_data in one_week_enter_quantity_list:
daily = quantity_data.id.get('daily_code')
city = quantity_data.id.get('city')
district = quantity_data.id.get('district')
if city and district:
if daily not in enter_quantity_time_dict:
temp_dict = {}
temp_dict["{city}-{district}".format(
city=city, district=district)] = quantity_data.sum
enter_quantity_time_dict[daily] = temp_dict
else:
enter_quantity_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
quantity_data.sum
})
else:
continue
# print(enter_quantity_time_dict, 'enter_quantity')
# 每日新增参与次数一周的数据
base_count_match['created_dt'] = {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte':
(export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
one_week_count_list = ReportRacePeopleStatistics.sync_aggregate([
MatchStage(base_count_match),
GroupStage(
{
'city': '$city',
'district': '$district',
'daily_code': '$daily_code'
},
sum={'$sum': "$total_num"})
])
# 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
count_time_dict = {}
for quantity_data in one_week_count_list:
daily = quantity_data.id.get('daily_code')
city = quantity_data.id.get('city')
district = quantity_data.id.get('district')
if city and district:
if daily not in count_time_dict:
temp_dict = {}
temp_dict["{city}-{district}".format(
city=city, district=district)] = quantity_data.sum
count_time_dict[daily] = temp_dict
else:
count_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
quantity_data.sum
})
else:
continue
# 一周通关的人数
# {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}}
pass_quantity_time_dict = {}
last_checkpoint_cid, _, _, _ = get_export_param(race_cid) # 拿到最后一关的cid
pass_match_stage = MatchStage({
'check_point_cid': last_checkpoint_cid,
'status': 1,
'record_flag': 1,
'created_dt': {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte': (export_time_list[-1] +
datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
})
check_point_cursor = MemberCheckPointHistory.sync_aggregate(
[pass_match_stage, lookup_stage])
member_cid_list = []
while True:
try:
check_point = check_point_cursor.next()
if check_point.member_list:
member = check_point.member_list[0]
race_mapping = RaceMapping.sync_find_one({
'race_cid':
race_cid,
'member_cid':
member.cid
})
if race_mapping:
if race_mapping.auth_address and race_mapping.auth_address.get(
'province'):
if check_point.member_cid not in member_cid_list:
district = race_mapping.auth_address.get(
'district')
city = race_mapping.auth_address.get('city')
if district and city:
member_cid_list.append(check_point.member_cid)
created_time = format(check_point.created_dt,
"%Y%m%d")
if created_time not in pass_quantity_time_dict:
pass_quantity_time_dict[created_time] = {
"{city}-{district}".format(city=city,
district=district):
1
}
else:
city_district = "{city}-{district}".format(
city=city, district=district)
v_dict = pass_quantity_time_dict.get(
created_time)
if city_district in v_dict:
v_dict[city_district] += 1
else:
v_dict[city_district] = 1
else:
continue
except StopIteration:
break
except Exception as e:
raise e
# 每日新增人数在excel的位置
quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, 8)]
# 每日参与人数在excel的位置
quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, 8)]
# 每日参与次数在excel的位置
count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, 8)]
# 每日通关次数在excel的位置
pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, 8)]
# 红包发放数量在excel的位置
red_give_position_list = [8 + 8 * (i - 1) for i in range(1, 8)]
# 红包发放金额在excel的位置
red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, 8)]
# 红包领取数量在excel的位置
red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, 8)]
# 红包领取金额在excel的位置
red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, 8)]
print(quantity_increase_position_list)
print(dis_map, 'dis_map6')
print(quantity, 'quan')
print(quantity_time_dict, 'quantity_time')
# 填充每日新增人数
write_excel_data(sheet,
quantity_time_dict,
dis_map,
export_time,
quantity_increase_position_list,
save=None)
# excel 填充每日参与人数
write_excel_data(sheet,
enter_quantity_time_dict,
dis_map,
export_time,
quantity_enter_position_list,
save=None)
# excel 填充每日参与次数
write_excel_data(sheet,
count_time_dict,
dis_map,
export_time,
count_enter_position_list,
save=None)
# excel 填充每日通关人数
write_excel_data(sheet,
pass_quantity_time_dict,
dis_map,
export_time,
pass_enter_position_list,
save=None)
red_give_dict = {}
red_give_amount_dict = {}
# 红包发放个数
red_give_out_match = {
"race_cid": race_cid,
'record_flag': 1,
"award_cid": {
'$ne': None
},
'member_cid': {
'$ne': None
},
'draw_dt': {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte': (export_time_list[-1] +
datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
}
red_give_out_cursor = RedPacketBox.sync_aggregate([
MatchStage(red_give_out_match), lookup_stage,
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$draw_dt"
}
},
"member_cid": '$member_cid',
"member_list": "$member_list",
"award_amount": '$award_amount',
}),
MatchStage({'member_list': {
'$ne': []
}}),
GroupStage({"daily_code": "$daily_code"},
amount_list={'$push': '$award_amount'},
cid_list={'$push': '$member_cid'},
sum={'$sum': 1})
])
while True:
try:
red_packet = red_give_out_cursor.next()
if red_packet and red_packet.cid_list:
cid_list = red_packet.cid_list
amount_list = red_packet.amount_list
print(len(amount_list), 'len_m')
print(len(cid_list), 'len')
daily = red_packet.id.get('daily_code')
for cid, amount in zip(cid_list, amount_list):
race_mapping = RaceMapping.sync_find_one({
'race_cid': race_cid,
'member_cid': cid,
'auth_address.city': {
'$in': city_name_list
},
'auth_address.district': {
'$in': dist_list
}
})
if race_mapping:
city = race_mapping.auth_address.get('city')
district = race_mapping.auth_address.get('district')
city_district = "{city}-{district}".format(
city=city, district=district)
if daily not in red_give_dict:
red_give_dict[daily] = {city_district: 1}
else:
v_dict = red_give_dict.get(daily)
if city_district not in v_dict:
v_dict[city_district] = 1
else:
v_dict[city_district] += 1
if daily not in red_give_amount_dict:
red_give_amount_dict[daily] = {
city_district: amount
}
else:
v_dict = red_give_amount_dict.get(daily)
if city_district not in v_dict:
v_dict[city_district] = amount
else:
v_dict[city_district] += amount
except StopIteration:
break
except Exception as e:
raise e
print(red_give_dict, 'dict2')
print(red_give_amount_dict, 'amount-dict2')
# excel 填充每日发放个数
write_excel_data(sheet,
red_give_dict,
dis_map,
export_time,
red_give_position_list,
save=None)
# excel 填充每日领取金额
write_excel_data(sheet,
red_give_amount_dict,
dis_map,
export_time,
red_give_amount_list,
save=2)
# 红包领取个数
red_receive_dict = {}
red_receive_amount_dict = {}
red_receive_match = {
"race_cid": race_cid,
'record_flag': 1,
"award_cid": {
'$ne': None
},
'draw_status': 0,
'member_cid': {
'$ne': None
},
'draw_dt': {
'$gte':
export_time_list[0].replace(hour=0, minute=0, second=0),
'$lte': (export_time_list[-1] +
datetime.timedelta(days=1)).replace(hour=0,
minute=0,
second=0)
}
}
red_receive_cursor = RedPacketBox.sync_aggregate([
MatchStage(red_receive_match), lookup_stage,
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$draw_dt"
}
},
"member_cid": '$member_cid',
"member_list": "$member_list",
"award_amount": '$award_amount',
}),
MatchStage({'member_list': {
'$ne': []
}}),
GroupStage({"daily_code": "$daily_code"},
receive_amount_list={'$push': '$award_amount'},
cid_list={'$push': '$member_cid'},
sum={'$sum': 1})
])
while True:
try:
red_packet = red_receive_cursor.next()
if red_packet and red_packet.cid_list:
amount_list = red_packet.receive_amount_list
cid_list = list(set(red_packet.cid_list))
print(len(cid_list), 'len')
daily = red_packet.id.get('daily_code')
for cid, amount in zip(cid_list, amount_list):
race_mapping = RaceMapping.sync_find_one({
'race_cid': race_cid,
'member_cid': cid,
'auth_address.city': {
'$in': city_name_list
},
'auth_address.district': {
'$in': dist_list
}
})
if race_mapping:
city = race_mapping.auth_address.get('city')
district = race_mapping.auth_address.get('district')
city_district = "{city}-{district}".format(
city=city, district=district)
if daily not in red_receive_dict:
red_receive_dict[daily] = {city_district: 1}
else:
v_dict = red_receive_dict.get(daily)
if city_district not in v_dict:
v_dict[city_district] = 1
else:
v_dict[city_district] += 1
if daily not in red_receive_amount_dict:
red_receive_amount_dict[daily] = {
city_district: amount
}
else:
v_dict = red_receive_amount_dict.get(daily)
if city_district not in v_dict:
v_dict[city_district] = amount
else:
v_dict[city_district] += amount
except StopIteration:
break
except Exception as e:
raise e
print(red_receive_dict, 'receive_cursor')
print(red_receive_amount_dict, 'rece_amount')
# excel 填充每日领取个数
write_excel_data(sheet,
red_receive_dict,
dis_map,
export_time,
red_receive_position_list,
save=None)
# excel 填充每日领取金额
write_excel_data(sheet,
red_receive_amount_dict,
dis_map,
export_time,
red_receive_amount_list,
save=2)
workbook.close()
def export_member_to_excel(workbook,
race_cid,
s_daily_code=None,
e_daily_code=None):
"""
会员信息导入
:param workbook:
:param race_cid:
:param s_daily_code:
:param e_daily_code:
:return:
"""
match = MatchStage({'race_cid': race_cid})
if s_daily_code and e_daily_code:
match['daily_code'] = {'$gte': s_daily_code, '$lte': e_daily_code}
group = GroupStage(
{
'member_cid': '$member_cid',
'race_cid': '$race_cid'
},
nick_name={'$first': '$nick_name'},
open_id={'$first': '$open_id'},
province={'$first': '$province'},
city={'$first': '$city'},
district={'$first': '$district'},
mobile={'$first': '$mobile'},
check_point_index={'$max': '$check_point_index'},
is_final_passed={'$max': '$is_final_passed'},
first_time_login={'$first': '$first_time_login'},
enter_times={'$sum': '$enter_times'},
draw_red_packet_count={'$sum': '$draw_red_packet_count'},
draw_red_packet_amount={'$sum': '$draw_red_packet_amount'},
)
sort_stage = SortStage([('first_time_login', ASC)])
exported_member_list = MemberStatisticInfo.sync_aggregate(
[match, group, sort_stage], allowDiskUse=True).to_list(None)
sheet = workbook.add_worksheet("会员信息")
sheet.merge_range(0, 0, 0, 1, '昵称')
sheet.merge_range(0, 2, 0, 3, 'open_id')
sheet.merge_range(0, 4, 0, 5, '城市')
sheet.merge_range(0, 6, 0, 7, '区县')
sheet.merge_range(0, 8, 0, 9, '手机号码')
sheet.merge_range(0, 10, 0, 11, '答题次数')
sheet.merge_range(0, 12, 0, 13, '当前关卡数')
sheet.merge_range(0, 14, 0, 15, '第一次进入小程序时间')
sheet.merge_range(0, 16, 0, 17, '领取红包数')
sheet.merge_range(0, 18, 0, 19, '领取红包金额')
for index, member_info in enumerate(exported_member_list):
row = index + 1
sheet.merge_range(row, 0, row, 1, member_info.nick_name)
sheet.merge_range(row, 2, row, 3, member_info.open_id)
sheet.merge_range(row, 4, row, 5, member_info.city)
sheet.merge_range(row, 6, row, 7, member_info.district)
sheet.merge_range(row, 8, row, 9,
member_info.mobile if member_info.mobile else '')
sheet.merge_range(row, 10, row, 11, member_info.enter_times)
if member_info.is_final_passed == 1:
sheet.merge_range(row, 12, row, 13, '已通关')
else:
sheet.merge_range(row, 12, row, 13,
str(member_info.check_point_index))
sheet.merge_range(row, 14, row, 15,
member_info.first_time_login.strftime("%Y-%m-%d"))
sheet.merge_range(row, 16, row, 17, member_info.draw_red_packet_count)
sheet.merge_range(row, 18, row, 19, member_info.draw_red_packet_amount)
def export_race_enter_position(race_cid: str, title, n):
"""
导出活动下面参与情况
:return:
"""
if not isinstance(race_cid, str):
raise ValueError("race_cid is not str")
current = datetime.datetime.now()
export_time_list = [
(current + datetime.timedelta(days=-n)).replace(hour=0,
minute=0,
second=0)
for n in (range(1, n))
]
export_time_list.sort()
export_time = [
transform_time_format(export_dt) for export_dt in export_time_list
]
export_time.sort()
workbook = xlsxwriter.Workbook("{title}{today}.xlsx".format(title=title,
today=today))
excel_name = "{title}{today}.xlsx".format(title=title, today=today)
head_list = [
"新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额"
]
sheet = workbook.add_worksheet("{title}一周数据".format(title=title))
data_center_format = workbook.add_format({
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei'
})
sheet.merge_range(0, 0, 1, 0, "城市", data_center_format)
sheet.merge_range(0, 1, 1, 1, "区县", data_center_format)
sheet.write_string(1, 2, "累计人数")
sheet.write_string(1, 3, "累计参与次数")
sheet.write_string(1, 4, "累计红包发放总数")
# excel填充一周日期
for i in range(1, n):
sheet.merge_range(0, 7 * (i - 1) + 5 + i - 1, 0,
7 * (i - 1) + 5 + i - 1 + 7, export_time[i - 1],
data_center_format)
for head in range(7 * (i - 1) + 5 + i - 1,
7 * (i - 1) + 5 + i - 1 + 7 + 1):
index = head - 8 * (i - 1) - 5
sheet.write_string(1, head, head_list[index])
prov_match = MatchStage({'province': {'$ne': None}})
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})
prov_match = MatchStage({'province': prov.title})
dist_list = []
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city_code})
address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)])
match = {
"race_cid": race_cid,
'record_flag': 1,
'city': {
'$in': city_name_list
},
'district': {
'$in': dist_list
}
}
address_list = RaceMemberEnterInfoStatistic.sync_aggregate([
MatchStage(match), prov_match,
ProjectStage(**{
'province': "$province",
"city": "$city",
"district": "$district"
}),
GroupStage({
'district': '$district',
'city': '$city'
}, sum={'$sum': 1}), address_sort
])
# 拿到该活动下的所有区县和城市对应字典表
dis_map = get_race_district_mapping(sheet, address_list)
# 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
quantity_time_dict = {}
# 每日参与人数一周的数据 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
enter_quantity_time_dict = {}
# 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}}
count_time_dict = {}
# 一周通关的人数 {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}}
pass_quantity_time_dict = {}
# 一周红包发放数量和金额的字典
red_give_dict = {}
red_give_amount_dict = {}
# 一周红包领取数量和金额的字典
red_receive_dict = {}
red_receive_amount_dict = {}
base_match = {
'race_cid': race_cid,
'city': {
'$in': city_name_list
},
'district': {
'$in': dist_list
},
'daily_code': {
'$gte':
export_time[0],
'$lte':
transform_time_format(
(export_time_list[-1] + datetime.timedelta(days=1)))
}
}
total_match = copy.deepcopy(base_match)
del total_match['daily_code']
total_district_count_dict = {}
total_district_times_dict = {}
total_send_red_packet_dict = {}
race_total_info_cursor = RaceMemberEnterInfoStatistic.sync_aggregate([
MatchStage(total_match),
GroupStage(
{
'city': '$city',
'district': '$district'
},
count_sum={'$sum': '$increase_enter_count'},
enter_times_sum={'$sum': '$enter_times'},
grant_red_packet_count={'$sum': "$grant_red_packet_count"},
)
])
while True:
try:
data = race_total_info_cursor.next()
district = data.id.get('district')
if district not in total_district_count_dict:
total_district_count_dict[district] = data.count_sum
if district not in total_district_times_dict:
total_district_times_dict[district] = data.enter_times_sum
if district not in total_send_red_packet_dict:
total_send_red_packet_dict[
district] = data.grant_red_packet_count
except StopIteration:
break
except Exception as e:
raise e
# 每日参与人数，总计
for district, data in total_district_count_dict.items():
_col = dis_map.get(district)
sheet.write_number(_col, 2, data)
# 每日参与次数，总计
for district, data in total_district_times_dict.items():
_col = dis_map.get(district)
sheet.write_number(_col, 3, data)
# 每日累计发放红包数量
for district, data in total_send_red_packet_dict.items():
_col = dis_map.get(district)
sheet.write_number(_col, 4, data)
race_member_info_list = RaceMemberEnterInfoStatistic.sync_aggregate([
MatchStage(base_match),
GroupStage(
{
"daily_code": "$daily_code",
'district': '$district',
'city': '$city'
},
increase_quantity_sum={'$sum': '$increase_enter_count'},
enter_count_sum={'$sum': '$enter_count'},
pass_count_sum={'$sum': '$pass_count'},
enter_times_sum={'$sum': '$enter_times'},
grant_red_packet_count_sum={'$sum': '$grant_red_packet_count'},
grant_red_packet_amount_sum={'$sum': '$grant_red_packet_amount'},
draw_red_packet_count_sum={'$sum': '$draw_red_packet_count'},
draw_red_packet_amount_sum={'$sum': '$draw_red_packet_amount'},
), address_sort
])
for race_member_info in race_member_info_list:
daily = race_member_info.id.get('daily_code')
city = race_member_info.id.get('city')
district = race_member_info.id.get('district')
if city and district:
if daily not in quantity_time_dict:
# 每日新增参与人数
quantity_time_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.increase_quantity_sum
}
else:
quantity_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.increase_quantity_sum
})
if daily not in enter_quantity_time_dict:
# 每日参与人数
enter_quantity_time_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.enter_count_sum
}
else:
enter_quantity_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.enter_count_sum
})
if daily not in count_time_dict:
# 每日参与次数
count_time_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.enter_times_sum
}
else:
count_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.enter_times_sum
})
if daily not in pass_quantity_time_dict:
# 每日通关人数
pass_quantity_time_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.pass_count_sum
}
else:
pass_quantity_time_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.pass_count_sum
})
if daily not in red_give_dict:
# 每日红包发放数量
red_give_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.grant_red_packet_count_sum
}
else:
red_give_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.grant_red_packet_count_sum
})
if daily not in red_give_amount_dict:
# 每日红包发放金额
red_give_amount_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.grant_red_packet_amount_sum
}
else:
red_give_amount_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.grant_red_packet_amount_sum
})
if daily not in red_receive_dict:
# 每日红包领取数量
red_receive_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.draw_red_packet_count_sum
}
else:
red_receive_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.draw_red_packet_count_sum
})
if daily not in red_receive_amount_dict:
# 每日红包领取金额
red_receive_amount_dict[daily] = {
"{city}-{district}".format(city=city, district=district):
race_member_info.draw_red_packet_amount_sum
}
else:
red_receive_amount_dict[daily].update({
"{city}-{district}".format(city=city, district=district):
race_member_info.draw_red_packet_amount_sum
})
else:
continue
# 每日新增人数在excel的位置
quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, n)]
# 每日参与人数在excel的位置
quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, n)]
# 每日参与次数在excel的位置
count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, n)]
# 每日通关次数在excel的位置
pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, n)]
# 红包发放数量在excel的位置
red_give_position_list = [8 + 8 * (i - 1) for i in range(1, n)]
# 红包发放金额在excel的位置
red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, n)]
# 红包领取数量在excel的位置
red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, n)]
# 红包领取金额在excel的位置
red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, n)]
# 填充每日新增人数
write_excel_data(sheet,
quantity_time_dict,
dis_map,
export_time,
quantity_increase_position_list,
save=None)
# excel 填充每日参与人数
write_excel_data(sheet,
enter_quantity_time_dict,
dis_map,
export_time,
quantity_enter_position_list,
save=None)
# excel 填充每日参与次数
write_excel_data(sheet,
count_time_dict,
dis_map,
export_time,
count_enter_position_list,
save=None)
# excel 填充每日通关人数
write_excel_data(sheet,
pass_quantity_time_dict,
dis_map,
export_time,
pass_enter_position_list,
save=None)
# excel 填充每日发放个数
write_excel_data(sheet,
red_give_dict,
dis_map,
export_time,
red_give_position_list,
save=None)
# excel 填充每日领取金额
write_excel_data(sheet,
red_give_amount_dict,
dis_map,
export_time,
red_give_amount_list,
save=2)
# excel 填充每日领取个数
write_excel_data(sheet,
red_receive_dict,
dis_map,
export_time,
red_receive_position_list,
save=None)
# excel 填充每日领取金额
write_excel_data(sheet,
red_receive_amount_dict,
dis_map,
export_time,
red_receive_amount_list,
save=2)
workbook.close()
return excel_name
def generate_race_middle(race_cid: str, start):
"""
生成中间表
:return:
"""
if not isinstance(race_cid, str):
raise ValueError("race_cid is not str")
prov_match = MatchStage({'province': {'$ne': None}})
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})
prov_match = MatchStage({'province': prov.title})
dist_list = []
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct('title', {'parent_code': city_code})
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
has_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list},
'member_cid': {'$ne': None}, 'town': {'$ne': None}}
no_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list},
'member_cid': {'$ne': None}, 'town': {'$eq': None}}
if start != "total":
start_time = transform_time_format(start)
end_time = transform_time_format(start + datetime.timedelta(days=1))
has_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time}
no_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time}
cursor = MemberStatisticInfo.sync_aggregate(
[
MatchStage(has_town_match),
lookup_stage,
prov_match,
MatchStage({'member_list': {'$ne': []}}),
GroupStage(
{"daily_code": "$daily_code", 'district': '$district', 'city': '$city', 'town': '$town'},
province={"$first": "$province"}, town={"$last": "$town"},
status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'},
enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"},
amount_sum={'$sum': "$grant_red_packet_amount"},
receive_count_sum={'$sum': "$draw_red_packet_count"},
receive_amount_sum={'$sum': "$draw_red_packet_amount"},
answer_times={'$sum': '$answer_times'},
true_answer_times={'$sum': '$true_answer_times'},
sum={'$sum': 1}),
]
)
no_town_cursor = MemberStatisticInfo.sync_aggregate(
[
MatchStage(no_town_match),
lookup_stage,
prov_match,
MatchStage({'member_list': {'$ne': []}}),
GroupStage(
{"daily_code": "$daily_code", 'district': '$district', 'city': '$city'},
province={"$first": "$province"}, town={"$last": "$town"},
status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'},
enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"},
amount_sum={'$sum': "$grant_red_packet_amount"},
receive_count_sum={'$sum': "$draw_red_packet_count"},
receive_amount_sum={'$sum': "$draw_red_packet_amount"},
answer_times={'$sum': '$answer_times'},
true_answer_times={'$sum': '$true_answer_times'},
sum={'$sum': 1}),
]
)
while True:
try:
race_member_info = cursor.next()
daily_code = race_member_info.id.get('daily_code')
status_list = race_member_info.status_list
pass_status_list = race_member_info.pass_status_list
district = race_member_info.id.get('district')
city = race_member_info.id.get('city')
if district and city:
race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid)
race_enter_info.province = race_member_info.province
race_enter_info.city = city
race_enter_info.district = district
race_enter_info.town = race_member_info.town
race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None
race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None
race_enter_info.increase_enter_count = status_list.count(1) # 每日新增
race_enter_info.enter_count = race_member_info.sum # 每日参与
race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数
race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数
race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量
race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额
race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量
race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额
race_enter_info.correct_percent = round(
race_member_info.true_answer_times / race_member_info.answer_times,
2) if race_member_info.answer_times != 0 else 0
race_enter_info.answer_times = race_member_info.answer_times # 总答题数量
race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量
race_enter_info.sync_save()
else:
continue
except StopIteration:
break
except Exception as e:
raise e
while True:
try:
race_member_info = no_town_cursor.next()
daily_code = race_member_info.id.get('daily_code')
status_list = race_member_info.status_list
pass_status_list = race_member_info.pass_status_list
district = race_member_info.id.get('district')
city = race_member_info.id.get('city')
if district and city:
race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid)
race_enter_info.province = race_member_info.province
race_enter_info.city = city
race_enter_info.district = district
race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None
race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None
race_enter_info.increase_enter_count = status_list.count(1) # 每日新增
race_enter_info.enter_count = race_member_info.sum # 每日参与
race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数
race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数
race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量
race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额
race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量
race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额
race_enter_info.correct_percent = round(
race_member_info.true_answer_times / race_member_info.answer_times,
2) if race_member_info.answer_times != 0 else 0 # 正确率
race_enter_info.answer_times = race_member_info.answer_times # 总答题数量
race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量
race_enter_info.sync_save()
else:
continue
except StopIteration:
break
except Exception as e:
raise e
async def do_rank_statistic(race_cid: str,
time_match: MatchStage,
group_id='district',
name_match=MatchStage({}),
district_title="",
name="",
time_num=""):
"""
统计活动信息
:param race_cid:
:param time_match:
:param group_id:
:param name_match
:param district_title
:param
:return:
"""
if not race_cid:
return
cache_key = generate_cache_key(
'member_times_{district}_{name}_{time_num}_{district_title}'.format(
district=group_id,
name=name,
time_num=time_num,
district_title=district_title))
member_times_data = RedisCache.hget(race_cid, cache_key)
if not member_times_data:
race = await Race.get_by_cid(race_cid)
city_list = await AdministrativeDivision.distinct(
'code', {'parent_code': race.province_code})
city_name_list = await AdministrativeDivision.distinct(
'title', {'parent_code': race.province_code})
dist_list = []
for city in city_list:
dist_list += await AdministrativeDivision.distinct(
'title', {'parent_code': city})
district_match = MatchStage({})
all_match = {
'race_cid': race_cid,
'province': {
'$ne': None
},
'district': {
'$in': dist_list
},
'city': {
'$in': city_name_list
}
}
if district_title:
district_match = MatchStage({'district': district_title})
all_match['city'] = {'$in': city_name_list}
all_match['town'] = {'$ne': None}
cursor = RaceMemberEnterInfoStatistic.aggregate([
MatchStage(all_match), district_match, time_match, name_match,
GroupStage(group_id,
enter_times_sum={'$sum': '$enter_times'},
people_sum={'$sum': '$increase_enter_count'},
true_answer_times_sum={'$sum': '$true_answer_times'},
answer_times_sum={'$sum': '$answer_times'}),
SortStage([('enter_times_sum', DESC)])
])
times_data = await stat_data(cursor)
logger_cache.info('cache_key: %s' % cache_key)
RedisCache.hset(race_cid, cache_key, msgpack.packb(times_data))
return times_data
return msgpack.unpackb(member_times_data, raw=False)
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)
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
def export_race_data(workbook, race_cid: str, sheet_name):
"""
导出每日参与人数，每日新增人数，每日新增人次
:param race_cid:
:return:
"""
# yesterday_time = get_yesterday()
# time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}})
race = Race.sync_get_by_cid(race_cid)
city_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
for city in city_list:
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city})
match = {
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": dist_list
}
}
sheet = workbook.add_worksheet(sheet_name)
cursor = RaceMapping.sync_aggregate([
MatchStage(match),
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$created_dt"
}
},
'auth_address': "$auth_address",
'member_cid': "$member_cid"
}),
GroupStage(
{
'daily_code': '$daily_code',
'district': '$auth_address.district'
},
sum={'$sum': 1},
auth_address={'$first': '$auth_address'}),
SortStage([('_id.daily_code', ASC)])
])
sheet.write_string(0, 0, '城市')
sheet.write_string(0, 1, '区县')
daily_list = []
prize_list = []
county_map = {}
v_list = list()
_max_row = 0
while True:
try:
data = cursor.next()
_current_row = None
title = data.id.get('district')
if title is None:
title = '未知'
if title not in prize_list:
prize_list.append(title)
_current_row = len(prize_list)
city = data.auth_address.get('city')
if not city:
continue
sheet.write_string(_current_row, 0, city)
ad_city = AdministrativeDivision.sync_find_one({'title': city})
_county = AdministrativeDivision.sync_distinct(
'title', {'parent_code': ad_city.code})
for _c in _county:
county_map[_c] = city
sheet.write_string(_current_row, 1, title)
else:
_current_row = prize_list.index(title) + 1
daily_code = data.id.get('daily_code')
if not daily_code:
daily_code = '未知'
if daily_code not in daily_list:
daily_list.append(daily_code)
_current_col = len(daily_list) + 1
sheet.write_string(0, _current_col, daily_code)
else:
_current_col = daily_list.index(daily_code) + 2
sheet.write_number(_current_row, _current_col, data.sum)
v_list.append(data.sum)
if _current_row >= _max_row:
_max_row = _current_row
except StopIteration:
break
except Exception as e:
raise e
for k, v in county_map.items():
if k not in prize_list:
_max_row += 1
sheet.write_string(_max_row, 0, v)
sheet.write_string(_max_row, 1, k)
if _max_row:
sheet.write_string(_max_row + 1, 0, '总和')
sheet.write_number(_max_row + 1, 1, sum(v_list))
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)
async def deal_race(race, province, city):
"""
:param race:
:param province:
:param city
:return:
"""
race_dict = {
'race_cid': race.cid,
'status': 1,
'title_img_url': '',
'area': '',
'is_local': True
}
if race.start_datetime > datetime.now():
# 未开始
race_dict['status'] = 0
if race.end_datetime < datetime.now():
# 已结束
race_dict['status'] = 2
if (race.city_code and race.city_code != city.code) or (
race.province_code != province.code):
race_dict['is_local'] = False
if race.image_list:
upload_file = race.image_list[0]
title_img_url = '%s://%s%s%s%s' % (SERVER_PROTOCOL, SERVER_HOST,
STATIC_URL_PREFIX, 'files/',
upload_file.title)
race_dict['title_img_url'] = title_img_url
if race.province_list:
race_dict['area'] = race.province_list[0].title
if race.city_list:
race_dict['area'] += '-' + race.city_list[0].title
# 去除脏数据
if not race.city_code:
# 省级活动
prov = AdministrativeDivision.sync_find_one(
{'code': race.province_code})
city_list = AdministrativeDivision.sync_find({
'parent_code':
prov.code
}).to_list(None)
city_title_list = [city.title for city in city_list]
city_code_list = [city.code for city in city_list]
district_title_list = AdministrativeDivision.sync_distinct(
"title", {'parent_code': {
'$in': city_code_list
}})
else:
city = AdministrativeDivision.sync_find_one(
{'code': race.city_code})
city_title_list = [city.title]
district_title_list = AdministrativeDivision.sync_distinct(
"title", {'parent_code': city.code})
start_daily_code = format(race.start_datetime, '%Y%m%d')
if race.cid != "DF1EDC30F120AEE93351A005DC97B5C1":
cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid)
data = RedisCache.get(cache_key)
if data:
race_dict['people_count'] = int(data)
else:
cursor = RaceMemberEnterInfoStatistic.aggregate([
MatchStage({
'race_cid': race.cid,
'daily_code': {
'$gte': start_daily_code
},
'city': {
'$in': city_title_list
},
'district': {
'$in': district_title_list
}
}),
GroupStage('race_cid',
sum={'$sum': '$increase_enter_count'})
])
count = 0
while await cursor.fetch_next:
try:
info = cursor.next_object()
count = info.sum
except StopIteration:
break
race_dict['people_count'] = count
RedisCache.set(cache_key, count, 60 * 60)
else:
# 扬州显示次数
cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid)
data = RedisCache.get(cache_key)
if data:
race_dict['people_count'] = int(data)
else:
cursor = RaceMemberEnterInfoStatistic.aggregate([
MatchStage({
'race_cid': race.cid,
'daily_code': {
'$gte': start_daily_code
},
'city': {
'$in': city_title_list
},
'district': {
'$in': district_title_list
}
}),
GroupStage('race_cid', sum={'$sum': '$enter_times'})
])
count = 0
while await cursor.fetch_next:
try:
info = cursor.next_object()
count = info.sum
except StopIteration:
break
race_dict['people_count'] = count
RedisCache.set(cache_key, count, 60 * 60)
race_dict['show_play_quantity'] = race.play_quantity_enabled
race_dict['title'] = race.title
return race_dict
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
def write_sheet_enter_data(book,
race_cid,
sheet_name,
pre_match={},
count_type='$people_num'):
"""
总共参与人数/人次
:param book:
:param sheet_name:
:param race_cid
:param pre_match:
:param count_type:
:return:
"""
match = {'race_cid': race_cid}
if pre_match:
match.update(pre_match)
race = Race.sync_get_by_cid(race_cid)
match.update(get_region_match(race))
sheet = book.add_worksheet(sheet_name)
group_stage = GroupStage(
{
'daily_code': '$daily_code',
'district': '$district'
},
sum={'$sum': count_type},
city={'$first': '$city'})
daily_list = ReportRacePeopleStatistics.sync_distinct('daily_code', match)
daily_list.sort()
sheet.write_string(0, 0, '城市')
sheet.write_string(0, 1, '区县')
daily_map = {}
for index, daily_code in enumerate(daily_list):
sheet.write_string(0, index + 2, daily_code)
daily_map[daily_code] = index + 2
district_list = ReportRacePeopleStatistics.sync_distinct('district', match)
district_map = {}
for index, district in enumerate(district_list):
sheet.write_string(index + 1, 1, district if district else '其他')
district_map[district if district else '其他'] = index + 1
cursor = ReportRacePeopleStatistics.sync_aggregate(
[MatchStage(match), group_stage])
county_map = dict()
v_list = list()
_max_row = 0
while True:
try:
stat = cursor.next()
city = stat.city if stat.city else '其他'
district = stat.id.get('district')
daily_code = stat.id.get('daily_code')
_row = district_map.get(district if district else '其他')
sheet.write_string(_row, 0, city)
ad_city = AdministrativeDivision.sync_find_one({'title': city})
_county = AdministrativeDivision.sync_distinct(
'title', {'parent_code': ad_city.code})
for _c in _county:
county_map[_c] = city
sheet.write_number(_row, daily_map.get(daily_code), stat.sum)
if _row > _max_row:
_max_row = _row
v_list.append(stat.sum)
except StopIteration:
break
except Exception as e:
raise e
for k, v in county_map.items():
if k not in district_map:
_max_row += 1
sheet.write_string(_max_row, 0, v)
sheet.write_string(_max_row, 1, k)
sheet.write_string(_max_row + 1, 0, '总和')
sheet.write_number(_max_row + 1, 1, sum(v_list))