def is_new_user(info: MemberStatisticInfo):
"""
判断是否为当日新用户
:param info:
:return:
"""
race_cid = info.race_cid
member_cid = info.member_cid
current_date = str2datetime(info.daily_code, '%Y%m%d').replace(hour=0, minute=0, second=0, microsecond=0)
checkpoint_cid_list, _checkpoint_map = get_checkpoint_cid_list(race_cid)
has_history = MemberCheckPointHistory.sync_find_one(
{'member_cid': member_cid, 'check_point_cid': {'$in': checkpoint_cid_list},
'created_dt': {'$lt': current_date}})
if has_history:
race_mapping = RaceMapping.sync_find_one({'race_cid': race_cid, 'member_cid': member_cid})
if format(race_mapping.created_dt, "%Y%m%d") == info.daily_code:
info.is_new_user = 1
else:
info.is_new_user = 0
else:
# 之前报名活动，但未答题的情况
race_mapping = RaceMapping.sync_find_one({'race_cid': race_cid, 'member_cid': member_cid})
if format(race_mapping.created_dt, "%Y%m%d") == info.daily_code:
info.is_new_user = 1
else:
info.is_new_user = 0
def get_race_mapping_info(info: MemberStatisticInfo):
"""
获取行政区域信息、公司单位、当前关卡
:param info:
:return:
"""
race_cid = info.race_cid
member_cid = info.member_cid
city_name_list, district_name_list = get_address(race_cid)
check_point_cids, _checkpoint_map = get_checkpoint_cid_list(race_cid)
race_mapping = RaceMapping.sync_find_one({'race_cid': race_cid, 'member_cid': member_cid,
'auth_address.province': {'$ne': None},
'auth_address.city': {"$in": city_name_list},
'auth_address.district': {"$in": district_name_list}})
if race_mapping:
info.province = race_mapping.auth_address.get('province')
info.city = race_mapping.auth_address.get('city')
info.district = race_mapping.auth_address.get('district')
info.town = race_mapping.auth_address.get('town')
info.mobile = race_mapping.mobile
info.check_point_cid = race_mapping.race_check_point_cid
if race_mapping.race_check_point_cid:
info.check_point_index = _checkpoint_map[race_mapping.race_check_point_cid]
else:
info.check_point_index = 1
# 单位信息
if race_mapping.company_cid:
company = Company.sync_get_by_cid(race_mapping.company_cid)
info.company_cid = company.cid
info.company_name = company.title
return True
else:
return False
def adjust_race_report_accuracy(race_cid):
"""
活动报表线上数据填充正确率数据
:param race_cid:
:return:
"""
race = Race.sync_find_one({'cid': race_cid})
if not race:
print("活动不存在, 请检查！")
return
# 找到该活动下面所有的关卡
check_point_cid_list = RaceGameCheckPoint.sync_distinct("cid", {'race_cid': race_cid})
print(check_point_cid_list)
if not check_point_cid_list:
print("该活动下面没有关卡")
return
# 给race_mapping从历史记录统计正确率
try:
for check_point_cid in check_point_cid_list:
check_point_history_list = MemberCheckPointHistory.sync_find({"check_point_cid": check_point_cid}).to_list(
None)
for check_point_history in check_point_history_list:
race_mapping = RaceMapping.sync_find_one(
{'member_cid': check_point_history.member_cid, "race_cid": race_cid},
read_preference=ReadPreference.PRIMARY)
race_mapping.total_count += len(check_point_history.result)
num = 0
for result in check_point_history.result:
if result.get("true_answer"):
num += 1
race_mapping.total_correct += num
race_mapping.sync_save()
except Exception as e:
print(str(e))
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 check_data():
day = (datetime.datetime.now() + datetime.timedelta(days=-18)).replace(
hour=0, minute=0, second=0)
race_mapping_list = RaceMapping.sync_find({
"race_cid": "CA755167DEA9AA89650D11C10FAA5413",
'created_dt': {
'$lt': day
}
}).to_list(None)
print(len(race_mapping_list))
def check(race_cid: str):
"""
检查多少人参与活动但是没有答过题目的
:param race_cid:
:return:
"""
race = Race.sync_get_by_cid(race_cid)
# 市级活动，如六安市，扬州市
if race.city_code:
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'code': race.city_code})
# 该活动的所属城市范围
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'code': race.city_code})
else:
prov = AdministrativeDivision.sync_find_one(
{'code': race.province_code})
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
print(city_name_list, '1')
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city_code})
quantity = 0
member_cid_list = RaceMapping.sync_distinct(
"member_cid", {
'race_cid': race_cid,
'auth_address.city': {
'$in': city_name_list
},
'auth_address.province': {
'$ne': None
},
'auth_address.district': {
'$in': dist_list
}
})
check_point_cid = RaceGameCheckPoint.sync_distinct("cid",
{"race_cid": race_cid})
for i in member_cid_list:
member = Member.sync_get_by_cid(i)
if member:
history = MemberCheckPointHistory.sync_find_one({
'check_point_cid': {
'$in': check_point_cid
},
"member_cid": i
})
if not history:
quantity += 1
print(quantity)
def write_sheet_race_member_information(race_cid: str, post_code):
"""
导出该活动下面得会员信息，主要是手机号码
:param race_cid:
:param post_code
:return: excel
"""
workbook = xlsxwriter.Workbook('黄山市会员信息统计.xlsx')
sheet = workbook.add_worksheet('黄山市会员信息')
sheet.merge_range(0, 0, 0, 1, '昵称')
sheet.write_string(0, 2, '地区')
sheet.merge_range(0, 3, 0, 4, '手机号码')
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
district_lookup_stage = LookupStage(AdministrativeDivision,
'district_code', 'post_code',
'district_list')
match_stage = MatchStage({
'race_cid': race_cid,
'record_flag': 1,
'city_code': post_code
})
# 该市下面所有得区
district_title_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': post_code})
race_mapping_cursor = RaceMapping.sync_aggregate(
[match_stage, lookup_stage, district_lookup_stage])
data_center_format = workbook.add_format({
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei',
'border': 1
})
num = 1
while True:
try:
race_mapping = race_mapping_cursor.next()
if race_mapping:
if race_mapping.member_list and race_mapping.district_list:
member = race_mapping.member_list[0]
district = race_mapping.district_list[0]
if district.title in district_title_list:
sheet.merge_range(num, 0, num, 1, member.nick_name,
data_center_format)
sheet.write_string(num, 2, district.title,
data_center_format)
sheet.merge_range(num, 3, num, 4,
str(race_mapping.mobile),
data_center_format)
num += 1
else:
continue
except StopIteration:
break
workbook.close()
def test(race_cid):
member_cid_list = MemberStatisticInfo.sync_distinct(
"member_cid", {
'race_cid': race_cid,
'is_new_user': 1
})
list2 = MemberStatisticInfo.sync_distinct('member_cid',
{'race_cid': race_cid})
cursor2 = MemberStatisticInfo.sync_find({'race_cid': race_cid})
print(len(member_cid_list))
print(len(list2))
count = 0
count1 = 0
dailiy = set()
for member in cursor2:
count += 1
if member.member_cid not in member_cid_list:
count1 += 1
print('-----------------------')
dailiy.add(member.daily_code)
print(member.daily_code, member.member_cid, member.is_new_user,
member.draw_red_packet_amount)
race_mapping = RaceMapping.sync_find_one({
'race_cid':
race_cid,
'member_cid':
member.member_cid
})
print(11, race_mapping.cid,
format(race_mapping.created_dt, "%Y%m%d"),
race_mapping.updated_dt)
current_date = str2datetime(member.daily_code,
'%Y%m%d').replace(hour=0,
minute=0,
second=0,
microsecond=0)
has_history = MemberCheckPointHistory.sync_find_one({
'member_cid':
member.member_cid,
'created_dt': {
'$lt': current_date
}
})
if has_history:
print(222, has_history)
else:
print(None)
print('total', count)
print('no', count1)
temp = list(dailiy)
temp.sort()
print(temp)
return temp
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)
async def post(self):
r_dict = {'code': 0}
open_id = self.get_i_argument('open_id', None)
race_cid = self.get_i_argument('race_cid', None)
mobile = self.get_i_argument('mobile', None)
v_code = self.get_i_argument('v_code', '')
if not race_cid:
r_dict['code'] = 1001
return r_dict
member = await find_member_by_open_id(open_id)
if not member:
r_dict['code'] = 1002
return r_dict
if not mobile:
r_dict['code'] = 1003
return r_dict
try:
if check_digit_verify_code(mobile, v_code):
r_dict['code'] = 1000
else:
r_dict['code'] = 1004
return r_dict
mapping = await RaceMapping.find_one({
'member_cid': member.cid,
'race_cid': race_cid,
'record_flag': 1
})
if not mapping:
mapping = RaceMapping(race_cid=race_cid, member_cid=member.cid)
mapping.province_code = member.province_code
mapping.city_code = member.city_code
mapping.district_code = member.district_code
mapping.mobile = mobile
else:
mapping.mobile = mobile
mapping.auth_address = member.auth_address
await mapping.save()
member.mobile = mobile
await member.save()
except Exception:
logger.error(traceback.format_exc())
return r_dict
async def post(self):
r_dict = {'code': 0}
open_id = self.get_i_argument('open_id', '')
race_cid = self.get_i_argument('race_cid', '')
if not open_id:
r_dict['code'] = 1001
return r_dict
try:
member = await find_member_by_open_id(open_id)
if not member or not member.auth_address:
r_dict['code'] = 1002
return r_dict
# 判断用户的授权地理位置信息和活动位置是否相符
r_dict = await check_enter_race(member, race_cid)
if not r_dict['enter_race']:
r_dict['code'] = 0
return r_dict
mapping = await RaceMapping.find_one(
{
'race_cid': race_cid,
'member_cid': member.cid,
'record_flag': 1
},
read_preference=ReadPreference.PRIMARY)
if not mapping:
mapping = RaceMapping(race_cid=race_cid, member_cid=member.cid)
mapping.province_code = member.province_code
mapping.city_code = member.city_code
mapping.district_code = member.district_code
mapping.age_group = member.age_group
mapping.sex = member.sex
mapping.category = member.category
mapping.education = member.education
if not mapping.auth_address:
mapping.auth_address = member.auth_address
await mapping.save()
race_checkpoints = await RaceGameCheckPoint.aggregate(stage_list=[
MatchStage({
'status': STATUS_GAME_CHECK_POINT_ACTIVE,
'race_cid': race_cid
}),
LookupStage(RedPacketRule, 'redpkt_rule_cid', 'cid',
'rule_list'),
SortStage([('index', DESC)])
]).to_list(None)
if race_checkpoints:
is_end = False
checkpoint_history = await MemberCheckPointHistory.find_one(
filtered={
'member_cid': member.cid,
'check_point_cid': race_checkpoints[0].cid,
'status': STATUS_RESULT_CHECK_POINT_WIN,
'record_flag': 1
})
if checkpoint_history:
is_end = True
race_checkpoint_cids = [c.cid for c in race_checkpoints]
if not mapping.race_check_point_cid or mapping.race_check_point_cid not in race_checkpoint_cids:
mapping.race_check_point_cid = race_checkpoint_cids[-1]
await member.save()
cur_game_checkpoint_cid = race_checkpoint_cids[-1]
elif is_end:
cur_game_checkpoint_cid = race_checkpoint_cids[0]
else:
index = race_checkpoint_cids.index(
mapping.race_check_point_cid)
cur_game_checkpoint_cid = race_checkpoint_cids[index]
game_checkpoint_list = [{
'cid':
checkpoint.cid,
'index':
checkpoint.index,
'alias':
checkpoint.alias,
'unlock_quantity':
checkpoint.unlock_quantity,
'has_redpkt':
await has_redpkt(race_cid, checkpoint, member.cid)
} for checkpoint in race_checkpoints]
r_dict = {
'race_cid': race_cid,
'code': 1000,
'is_end': is_end,
'avatar': member.avatar,
'nick_name': member.nick_name,
'cur_game_checkpoint_cid': cur_game_checkpoint_cid,
'game_checkpoint_list': game_checkpoint_list
}
else:
r_dict['code'] = 1000
r_dict['game_checkpoint_list'] = []
except Exception:
logger.error(traceback.format_exc())
return r_dict
def init_race_stat_data():
"""
初始该活动数据
:return:
"""
cp_map = get_all_race_checkpoint_map()
last_map = get_all_last_checkpoint()
cursor = MemberCheckPointHistory.sync_find(
{
'check_point_cid': {
'$in': list(cp_map.keys())
}
},
read_preference=ReadPreference.PRIMARY).sort('created_dt').limit(
600000)
cache_key = 'race_report_script'
RedisCache.delete(cache_key)
index = 1
while True:
try:
his = cursor.next()
mapping = RaceMapping.sync_find_one({
'member_cid':
his.member_cid,
'race_cid':
cp_map.get(his.check_point_cid)
})
member = Member.sync_find_one({'cid': his.member_cid})
auth_address = mapping.auth_address if mapping else None
# if not auth_address:
# auth_address = member.auth_address
#
if not auth_address:
continue
race_cid = cp_map[his.check_point_cid]
daily_code = __get_daily_code(his.created_dt)
param = {
'race_cid': race_cid,
'province': auth_address.get('province'),
'city': auth_address.get('city'),
'district': auth_address.get('district'),
'sex': member.sex,
'education': member.education,
'category': member.category,
'daily_code': daily_code
}
stat = ReportRacePeopleStatistics.sync_find_one(
param, read_preference=ReadPreference.PRIMARY)
if not stat:
stat = ReportRacePeopleStatistics(**param)
stat.created_dt = his.created_dt
stat.total_num += 1
# 初次通关
if his.check_point_cid == last_map[
race_cid] and his.status == STATUS_RESULT_CHECK_POINT_WIN and RedisCache.hget(
cache_key, member.cid) is None:
stat.pass_num += 1
RedisCache.hset(cache_key, member.cid, 1)
# 当日人数
day_member_string = md5(daily_code + member.cid)
if RedisCache.hget(cache_key, day_member_string) is None:
RedisCache.hset(cache_key, day_member_string, 1)
stat.people_num += 1
# # 当日新增人数
# old_his = MemberCheckPointHistory.sync_find_one({'member_cid': member.cid, 'created_dt': {
# '$lt': his.updated_dt.replace(hour=0, minute=0, second=0, microsecond=0)}})
# if not old_his:
# stat.incre_people += 1
stat.updated_dt = his.updated_dt
stat.sync_save()
print('has exec %s' % index)
index += 1
except StopIteration:
break
except CursorNotFound:
cursor = MemberCheckPointHistory.sync_find({'check_point_cid': {'$in': list(cp_map.keys())}},
read_preference=ReadPreference.PRIMARY). \
sort('created_dt').skip(index).limit(600000 - index)
RedisCache.delete(cache_key)
def deal_member_without_history(race_cid, daily_code):
"""
处理报名活动但未答题的会员
:param race_cid:
:param daily_code:
:return:
"""
city_name_list, district_name_list = get_address(race_cid)
checkpoint_cid, _checkpoint_map = get_checkpoint_cid_list(race_cid)
start_date = str2datetime(daily_code, '%Y%m%d').replace(hour=0,
minute=0,
second=0,
microsecond=0)
end_date = start_date + datetime.timedelta(days=1)
member_cid_with_history = MemberCheckPointHistory.sync_distinct(
"member_cid", {
'check_point_cid': {
'$in': checkpoint_cid
},
'created_dt': {
'$gte': start_date,
'$lt': end_date
}
})
race_member_match = MatchStage({
"race_cid": race_cid,
'member_cid': {
'$nin': member_cid_with_history
},
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": district_name_list
},
'created_dt': {
'$gte': start_date,
'$lt': end_date
}
})
member_group = GroupStage({'member_cid': '$member_cid'},
auth_address={'$first': '$auth_address'},
company_cid={'$first': '$company_cid'},
mobile={'$first': '$mobile'},
created_dt={'$first': '$created_dt'})
member_project = ProjectStage(
**{
'cid': '$cid',
'member_cid': '$_id.member_cid',
'auth_address': '$auth_address',
'mobile': '$mobile',
'created_dt': '$created_dt',
'company_cid': '$company_cid'
})
member_without_history = RaceMapping.sync_aggregate(
[race_member_match, member_group, member_project]).batch_size(4)
member_amount_map = get_red_packet_info(race_cid, start_date, end_date)
red_member_cid_list = member_amount_map.keys()
member_no_history_list = []
count = 0
while True:
try:
stat = member_without_history.next()
count += 1
if stat.member_cid in red_member_cid_list:
continue
# 根据member_cid查对应的member信息
temp_member = Member.sync_get_by_cid(stat.member_cid)
if not temp_member:
print("no history未找到对应member_cid:%s" % stat.member_cid)
continue
info_special = MemberStatisticInfo()
info_special.is_special_user = 1
info_special.race_cid = race_cid
info_special.member_cid = stat.member_cid
info_special.daily_code = format(stat.created_dt, '%Y%m%d')
info_special.nick_name = temp_member.nick_name
info_special.open_id = temp_member.open_id
if stat.mobile:
info_special.mobile = stat.mobile
else:
info_special.mobile = temp_member.mobile
info_special.first_time_login = temp_member.created_dt
info_special.enter_times = 1
info_special.answer_times = 0
info_special.true_answer_times = 0
info_special.is_final_passed = 0
info_special.is_new_user = 1
info_special.grant_red_packet_amount = 0.0
info_special.grant_red_packet_count = 0
info_special.draw_red_packet_count = 0
info_special.draw_red_packet_amount = 0.0
info_special.province = stat.auth_address.get('province')
info_special.city = stat.auth_address.get('city')
info_special.district = stat.auth_address.get('district')
info_special.town = stat.auth_address.get('town')
info_special.check_point_cid = stat.race_check_point_cid
if stat.race_check_point_cid:
info_special.check_point_index = _checkpoint_map[
stat.race_check_point_cid]
else:
info_special.check_point_index = 1
if stat.company_cid:
company = Company.sync_get_by_cid(stat.company_cid)
info_special.company_cid = company.cid
info_special.company_name = company.title
member_no_history_list.append(info_special)
# logger.info("Success without history: member_cid:%s is_final_Pass:%s" % (info_special.member_cid,info_special.is_final_passed))
if len(member_no_history_list) % 500 == 0:
MemberStatisticInfo.sync_insert_many(member_no_history_list)
member_no_history_list = []
except StopIteration:
break
except CursorNotFound:
member_without_history = RaceMapping.sync_aggregate(
[race_member_match, member_group,
member_project]).skip(count).batch_size(4)
except Exception as e:
logger.info(
"Fail: without history daily_code:%s,member_cid:%s,race_cid: %s"
% (info_special.daily_code, info_special.member_cid, race_cid))
if len(member_no_history_list) > 0:
MemberStatisticInfo.sync_insert_many(member_no_history_list)
def export_race_member_base_information(prov_match, title,
district_title_list):
"""
导出参与人数，通关人数，正确率top100的个人信息(手机号码)
:param workbook:
:return:
"""
now = format(datetime.datetime.now(), "%Y-%m-%d%H:%M")
excel_title = "六安市活动统计{time}.xlsx".format(time=now)
workbook = xlsxwriter.Workbook(excel_title)
sheet = workbook.add_worksheet("{title}会员信息统计".format(title=title))
data_center_format = workbook.add_format({
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei',
'border': 1
})
# sheet = generate_excel_head(sheet, data_center_format)
sheet.merge_range(0, 0, 0, 1, '昵称', data_center_format)
sheet.write_string(0, 2, '城市', data_center_format)
sheet.write_string(0, 3, '地区', data_center_format)
sheet.merge_range(0, 4, 0, 5, '手机号码', data_center_format)
sheet.write_string(0, 6, "正确率", data_center_format)
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
add_fields_stage = AddFieldsStage(
t_accuracy={
'$cond': {
'if': {
'$eq': ['$total_count', 0]
},
'then': 0,
'else': {
'$divide': ['$total_correct', '$total_count']
}
}
})
prov_race_mapping_cursor = RaceMapping.sync_aggregate([
MatchStage(prov_match), lookup_stage, add_fields_stage,
MatchStage({'member_list': {
"$ne": []
}})
])
num = 1
# total_member_list = []
sheet.write_string(0, 7, '单位/乡镇')
while True:
try:
race_mapping = prov_race_mapping_cursor.next()
if race_mapping:
if race_mapping.member_list and race_mapping.auth_address.get(
'city') == "六安市" and race_mapping.auth_address.get(
'district') in district_title_list:
member = race_mapping.member_list[0]
# if member.cid not in total_member_list:
# total_member_list.append(member.cid)
# if race_mapping.auth_address.get('district') in district_title_list:
sheet.merge_range(num, 0, num, 1, member.nick_name,
data_center_format)
sheet.write_string(num, 2,
race_mapping.auth_address.get('city'),
data_center_format)
sheet.write_string(
num, 3, race_mapping.auth_address.get('district'),
data_center_format)
sheet.merge_range(num, 4, num, 5, str(race_mapping.mobile),
data_center_format)
sheet.write_number(num, 6, round(race_mapping.t_accuracy,
2), data_center_format)
if race_mapping.company_cid:
company = Company.sync_find_one(
{'cid': race_mapping.company_cid})
if company:
sheet.write_string(num, 7, company.title)
elif race_mapping.auth_address.get('town'):
sheet.write_string(
num, 7, race_mapping.auth_address.get('town'))
num += 1
else:
continue
except StopIteration:
break
# 六安市的最后一关关卡
city_last_checkpoint_cid, _, _, _ = get_export_param(
"CA755167DEA9AA89650D11C10FAA5413")
print(city_last_checkpoint_cid)
pass_sheet = workbook.add_worksheet("{title}通关会员信息统计".format(title=title))
# pass_sheet = generate_excel_head(pass_sheet, data_center_format)
pass_sheet.merge_range(0, 0, 0, 1, '昵称', data_center_format)
pass_sheet.write_string(0, 2, '城市', data_center_format)
pass_sheet.write_string(0, 3, '地区', data_center_format)
pass_sheet.merge_range(0, 4, 0, 5, '手机号码', data_center_format)
pass_sheet.write_string(0, 6, "正确率", data_center_format)
pass_sheet.write_string(0, 7, "单位或乡镇", data_center_format)
check_point_lookup = LookupStage(
MemberCheckPointHistory,
let={'primary_cid': '$member_cid'},
as_list_name='history_list',
pipeline=[
{
'$match': {
'$expr': {
'$and': [{
'$eq': ['$member_cid', '$$primary_cid']
}, {
'$eq': ['$status', 1]
}, {
'$eq':
['$check_point_cid', city_last_checkpoint_cid]
}]
}
}
},
])
add_fields_stage = AddFieldsStage(
t_accuracy={
'$cond': {
'if': {
'$eq': ['$total_count', 0]
},
'then': 0,
'else': {
'$divide': ['$total_correct', '$total_count']
}
}
})
match_checkpoint_stage = MatchStage({'history_list': {'$ne': []}})
pass_race_mapping_cursor = RaceMapping.sync_aggregate([
MatchStage(prov_match), check_point_lookup, match_checkpoint_stage,
lookup_stage, add_fields_stage
])
position = 1
_total_pass_list = []
while True:
try:
pass_race_mapping = pass_race_mapping_cursor.next()
if pass_race_mapping:
if pass_race_mapping.member_list:
member = pass_race_mapping.member_list[0]
if member.cid not in _total_pass_list:
_total_pass_list.append(member.cid)
else:
continue
if pass_race_mapping.auth_address.get(
'district'
) in district_title_list and pass_race_mapping.auth_address.get(
'city'
) == "六安市" and pass_race_mapping.auth_address.get(
'district') in district_title_list:
pass_sheet.merge_range(position, 0, position, 1,
member.nick_name,
data_center_format)
pass_sheet.write_string(
position, 2,
pass_race_mapping.auth_address.get('city'),
data_center_format)
pass_sheet.write_string(
position, 3,
pass_race_mapping.auth_address.get('district'),
data_center_format)
pass_sheet.merge_range(position, 4, position, 5,
str(pass_race_mapping.mobile),
data_center_format)
pass_sheet.write_number(
position, 6, round(pass_race_mapping.t_accuracy,
2), data_center_format)
if pass_race_mapping.company_cid:
company = Company.sync_find_one(
{'cid': pass_race_mapping.company_cid})
if company:
pass_sheet.write_string(
position, 7, company.title)
elif pass_race_mapping.auth_address.get('town'):
sheet.write_string(
num, 7,
pass_race_mapping.auth_address.get('town'))
position += 1
else:
continue
except StopIteration:
break
print("information calc end ------------------")
workbook.close()
def get_member_info(thread_index, memberCidList, race_cid, checkPointCidList,
lastCheckPoint, checkPointMap):
print(thread_index, "号线程开始处理")
# print(memberCidList)
exportedMemberList = []
for memberCid in memberCidList:
member = Member.sync_find_one({'cid': memberCid})
if member is None:
print("未找到member_cid:{}的基础信息".format(memberCid))
continue
raceMapping = RaceMapping.sync_find_one({
'race_cid': race_cid,
'member_cid': memberCid,
'record_flag': 1,
'auth_address.province': {
'$ne': None
}
})
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 = ''
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 = 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_index, member.cid, exportedMember.nick,
exportedMember.city, exportedMember.district,
exportedMember.mobile, exportedMember.currentCheckPoint,
exportedMember.answerTimes, exportedMember.totalNumOfRedPacket,
exportedMember.totalAmountOfRedPacket)
return exportedMemberList
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)
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.alias
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)
temp_list = splitList(memberCidList, 2000)
threads = []
thread_num = 1
for my_list in temp_list:
# print(my_list)
t = MyThread(get_member_info,
args=(thread_num, my_list, race_cid, checkPointCidList,
lastCheckPoint, checkPointMap))
threads.append(t)
t.start()
thread_num += 1
results = []
for each_thread in threads:
each_thread.join()
result = each_thread.get_result()
if len(result) > 0:
results.extend(result)
return results
async def post(self):
r_dict = {'code': 0}
open_id = self.get_i_argument('open_id', None)
race_cid = self.get_i_argument('race_cid', None)
company_cid = self.get_i_argument('company_cid', None)
if not race_cid:
r_dict['code'] = 1001
return r_dict
member = await find_member_by_open_id(open_id)
if not member:
r_dict['code'] = 1002
return r_dict
company = await Company.find_one({
'cid': company_cid,
'record_flag': 1
})
if not company:
r_dict['code'] = 1003
return r_dict
try:
mapping = await RaceMapping.find_one({
'member_cid': member.cid,
'race_cid': race_cid,
'record_flag': 1
})
if not mapping:
mapping = RaceMapping(race_cid=race_cid, member_cid=member.cid)
mapping.province_code = member.province_code
mapping.city_code = member.city_code
mapping.district_code = member.district_code
mapping.age_group = member.age_group
mapping.sex = member.sex
mapping.category = member.category
mapping.education = member.education
mapping.company_cid = company_cid
else:
mapping.company_cid = company_cid
if not mapping.auth_address:
mapping.auth_address = member.auth_address
await mapping.save()
r_dict['code'] = 1000
except Exception:
logger.error(traceback.format_exc())
return r_dict
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 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()
async def post(self):
res_dict = {'code': 0}
try:
open_id = self.get_i_argument("open_id")
race_cid = self.get_i_argument("race_cid")
if not (open_id and race_cid):
res_dict['code'] = 1001
return res_dict
member = await find_member_by_open_id(open_id)
if not member:
res_dict['code'] = 1002
return res_dict
if not member.auth_address:
res_dict['code'] = 1003
return res_dict
town = self.get_i_argument('town')
if not town:
res_dict['code'] = 1005
return res_dict
district = self.get_i_argument('district')
if not district:
res_dict['code'] = 1006
return res_dict
mapping = await RaceMapping.find_one({
'member_cid': member.cid,
'race_cid': race_cid,
'record_flag': 1
})
if not mapping:
mapping = RaceMapping(race_cid=race_cid, member_cid=member.cid)
mapping.province_code = member.province_code
mapping.city_code = member.city_code
mapping.district_code = member.district_code
mapping.age_group = member.age_group
mapping.sex = member.sex
mapping.category = member.category
mapping.education = member.education
if not mapping.auth_address:
mapping.auth_address = member.auth_address
mapping.auth_address['town'] = town
mapping.auth_address['district'] = district
await mapping.save()
res_dict['code'] = 1000
return res_dict
except Exception:
logger.error(traceback.format_exc())
def wash_data():
"""
清洗统计数据
:return:
"""
race_map = {}
prov_map = {}
cursor = ReportRacePeopleStatistics.sync_find()
while True:
try:
stat = cursor.next()
if not (stat.province and stat.city and stat.district):
print('delete')
stat.sync_delete()
continue
if race_map.get(stat.race_cid):
race = race_map.get(stat.race_cid)
else:
race = Race.sync_find_one({'cid': stat.race_cid})
race_map[stat.race_cid] = race
prov = prov_map.get(stat.province)
if not prov:
prov = AdministrativeDivision.sync_find_one({
'title': {
'$regex': stat.province
},
'parent_code': None
})
prov_map[stat.province] = prov
if prov.code != race.province_code:
print('xxxxx')
stat.sync_delete()
except StopIteration:
break
except Exception as e:
pdb.set_trace()
cursor = RaceMapping.sync_find()
while True:
try:
_p = cursor.next()
if not _p.auth_address or not _p.auth_address.get('district'):
member = Member.sync_find_one({'cid': _p.member_cid})
if not member or not member.auth_address or not member.auth_address.get(
'district'):
_p.sync_delete()
continue
_p.auth_address = member.auth_address
_p.sync_save()
print('update mapping')
if race_map.get(_p.race_cid):
race = race_map.get(_p.race_cid)
else:
race = Race.sync_find_one({'cid': _p.race_cid})
race_map[_p.race_cid] = race
province = _p.auth_address.get('province')
if province:
prov = prov_map.get(province)
if not prov:
prov = AdministrativeDivision.sync_find_one({
'title': {
'$regex': province
},
'parent_code':
None
})
prov_map[province] = prov.code
if prov.code != race.province_code:
print(' mapping delete ')
_p.sync_delete()
except StopIteration:
break
except Exception as e:
print(e)
raise e
def do_init():
"""
初始该活动数据
:return:
"""
cache_key = 'race_report_script'
RedisCache.delete(cache_key)
cp_map = get_all_race_checkpoint_map()
last_map = get_all_last_checkpoint()
with open('./chekpt_history.csv', encoding='utf-8') as f:
csv_reader = csv.reader(f)
for index, line in enumerate(csv_reader):
try:
if index == 0:
continue
# member_cid,check_point_cid,status,created_dt
his = MemberCheckPointHistory()
his.member_cid = line[0]
his.check_point_cid = line[1]
his.status = line[2]
line3 = line[3].replace('T', ' ').split('.')[0]
c_dt = str2datetime(line3)
his.created_dt = c_dt
mapping = RaceMapping.sync_find_one({
'member_cid':
his.member_cid,
'race_cid':
cp_map.get(his.check_point_cid)
})
member = Member.sync_get_by_cid(his.member_cid)
auth_address = mapping.auth_address if mapping else None
if not auth_address:
continue
race_cid = cp_map[his.check_point_cid]
daily_code = __get_daily_code(his.created_dt)
param = {
'race_cid': race_cid,
'province': auth_address.get('province'),
'city': auth_address.get('city'),
'district': auth_address.get('district'),
'town': auth_address.get('town'),
'sex': member.sex,
'education': member.education,
'category': member.category,
'daily_code': daily_code,
'company_cid': mapping.company_cid
}
stat = ReportRacePeopleStatisticsTemp.sync_find_one(
param, read_preference=ReadPreference.PRIMARY)
if not stat:
stat = ReportRacePeopleStatisticsTemp(**param)
stat.created_dt = his.created_dt
stat.total_num += 1
# 初次通关
if his.check_point_cid == last_map[
race_cid] and his.status == STATUS_RESULT_CHECK_POINT_WIN and RedisCache.hget(
cache_key, member.cid) is None:
stat.pass_num += 1
RedisCache.hset(cache_key, member.cid, 1)
# 当日人数
day_member_string = md5(daily_code + member.cid)
if RedisCache.hget(cache_key, day_member_string) is None:
RedisCache.hset(cache_key, day_member_string, 1)
stat.people_num += 1
# # 当日新增人数
# old_his = MemberCheckPointHistory.sync_find_one({'member_cid': member.cid, 'created_dt': {
# '$lt': his.updated_dt.replace(hour=0, minute=0, second=0, microsecond=0)}})
# if not old_his:
# stat.incre_people += 1
stat.updated_dt = his.created_dt
stat.sync_save()
print('has exec %s' % index)
except Exception:
print(traceback.format_exc())
