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 do_stat_in_history(history_model, city_code_list, choice_time, ad_map={}):
"""
:param history_model:
:param city_code_list:
:param ad_map:
:param choice_time
:return:
"""
# 取前一天凌晨12点之前的数据
time_match = get_yesterday()
if not choice_time:
match_stage = MatchStage({'updated_dt': {'$lt': time_match}})
else:
# 当天下一天凌晨的时候
max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999)
match_stage = MatchStage({'updated_dt': {'$gte': choice_time, '$lt': max_choice_time}})
cursor = history_model.sync_aggregate([
match_stage,
GroupStage('member_cid', quantity={"$sum": 1}),
LookupStage(Member, '_id', 'cid', 'member_list'),
ProjectStage(**{
'province_code': {'$arrayElemAt': ['$member_list.province_code', 0]},
'city_code': {'$arrayElemAt': ['$member_list.city_code', 0]},
'quantity': '$quantity'
}),
MatchStage({'city_code': {'$in': city_code_list}}),
GroupStage('city_code', quantity={'$sum': "$quantity"}, province_code={'$first': '$province_code'}),
SortStage([('quantity', DESC)])
])
data = {}
while True:
try:
his = cursor.next()
city_data = data.get(his.province_code, {})
city = ad_map.get(his.id)
if not city:
city = AdministrativeDivision.sync_find_one({'code': his.id, 'parent_code': {'$ne': None}})
ad_map[city.code] = city
city_data[city.title] = his.quantity
data[his.province_code] = city_data
except StopIteration:
break
except Exception as e:
logger.error(str(e))
continue
return data, ad_map
def do_statistics_member_time(cache_key, city_code_list, choice_time):
"""开始统计
:param cache_key:
:param city_code_list:
:param choice_time:
:return:
"""
RedisCache.set(cache_key, KEY_CACHE_REPORT_DOING_NOW, 5 * 60)
ad_map = {}
game_data, ad_map = do_stat_in_history(MemberGameHistory, city_code_list, choice_time, ad_map)
ckpt_data, ad_map = do_stat_in_history(MemberCheckPointHistory, city_code_list, choice_time, ad_map)
# 对学习之旅和科协答题历史记录进行数据整合
for k, city_dict in game_data.items():
if k not in ckpt_data:
ckpt_data[k] = city_dict
continue
# loop city_list
for c_name, c_data in city_dict.items():
try:
# try to merge
ckpt_data[k][c_name] += c_data
except KeyError:
ckpt_data[k][c_name] = c_data
ret_data = []
for prov_code, city_data in ckpt_data.items():
prov = ad_map.get(prov_code)
if not prov:
prov = AdministrativeDivision.sync_find_one({'code': prov_code, 'parent_code': None})
city_list = [{'title': _k, 'data': _v} for _k, _v in city_data.items()]
_ds = [_.get('data') for _ in city_list]
city_list.sort(key=lambda x: -x.get('data'))
ret_data.append(
{'title': prov.title.replace('省', '').replace('市', ''), 'data': sum(_ds), 'city_list': city_list})
ret_data.sort(key=lambda x: -x.get('data'))
if not ret_data:
early_warning_empty("start_statistics_member_time", cache_key, city_code_list, '学习近况中次数数据为空，请检查！')
RedisCache.set(cache_key, msgpack.packb(ret_data))
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))
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 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))
worksheet.write_string(row, 2,
SEX_DICT.get(member.sex) if member else '-')
worksheet.write_string(
row, 3,
TYPE_AGE_GROUP_DICT.get(member.age_group) if member else '-')
worksheet.write_string(
row, 4,
TYPE_EDUCATION_DICT.get(member.education) if member else '-')
prov = city = '-'
if member.province_code and member.city_code:
if member.province_code in post_code_map:
prov = post_code_map[member.province_code]
else:
prov = AdministrativeDivision.sync_find_one({
'code':
member.province_code
}).title
if member.city_code in post_code_map:
city = post_code_map[member.city_code]
else:
city = AdministrativeDivision.sync_find_one({
'code':
member.city_code
}).title
post_code_map[member.province_code] = prov
post_code_map[member.city_code] = city
worksheet.write_string(row, 5, prov)
worksheet.write_string(row, 6, city)
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
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
