def get_address(race_cid):
"""
获取活动的城市列表、区域列表,并缓存
:param race_cid:
:return:
"""
city_list_str_key = '%s_CITY_LIST_STR' % race_cid
district_list_str_key = '%s_DISTRICT_LIST_STR' % race_cid
city_name_list_str = RedisCache.get(city_list_str_key)
district_name_list_str = RedisCache.get(district_list_str_key)
if not city_name_list_str or not district_name_list_str:
# race_province_code,race_city_code缓存,若city_code不为空，则为市级活动
pro_code_key = '%s_province_code' % race_cid
city_code_key = '%s_city_code' % race_cid
province_code = RedisCache.get(pro_code_key)
city_code = RedisCache.get(city_code_key)
if not province_code or not city_code:
race = Race.sync_get_by_cid(race_cid)
RedisCache.set(pro_code_key, race.province_code, 12 * 60 * 60)
RedisCache.set(city_code_key, race.city_code, 12 * 60 * 60)
if city_code:
city_code_list = AdministrativeDivision.sync_distinct('code', {'code': city_code})
city_name_list = AdministrativeDivision.sync_distinct('title', {'code': city_code})
else:
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}})
RedisCache.set(','.join(city_name_list), 12 * 60 * 60)
RedisCache.set(','.join(district_name_list), 12 * 60 * 60)
else:
city_name_list = city_name_list_str.split(',')
district_name_list = district_name_list_str.split(',')
return city_name_list, district_name_list
def get_region_match(race):
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})
return {'city': {'$in': city_name_list}, 'district': {'$in': dist_list}}
async def get(self):
race_cid = self.get_argument('race_cid', '')
menu_list = await get_menu(self, 'report', race_cid, tag=1)
region_code_list = []
manage_region_code_list = self.current_user.manage_region_code_list
ad_cursor = AdministrativeDivision.find(
{'code': {
'$in': self.current_user.manage_region_code_list
}})
while await ad_cursor.fetch_next:
ad = ad_cursor.next_object()
if ad:
if ad.level == 'P':
region_code_list.append('[p]%s' % ad.code)
elif ad.level == 'C':
region_code_list.append('[c]%s' % ad.code)
elif ad.level == 'D':
region_code_list.append('[d]%s' % ad.code)
subject_dimension_list = await SubjectDimension.aggregate([
MatchStage(
dict(status=STATUS_SUBJECT_DIMENSION_ACTIVE, parent_cid=None)),
LookupStage(SubjectDimension, 'cid', 'parent_cid',
'sub_subject_dimension_list')
]).to_list(None)
return locals()
async def do_different_administrative_division2(ad_code_list: list):
"""
区分行政区划等级
:param ad_code_list: 行政区划编码列表
:return: (省份, 城市, 区/县)
"""
province_code_list, city_code_list, district_code_list = [], [], []
if not ad_code_list:
raise ValueError('There are no manageable areas for this account.')
ad_cursor = AdministrativeDivision.find({'code': {'$in': ad_code_list}})
while await ad_cursor.fetch_next:
ad: AdministrativeDivision = ad_cursor.next_object()
if ad:
if ad.level == 'P':
province_code_list.append(ad.code)
city_code_list += await AdministrativeDivision.distinct(
'code', {'parent_code': ad.code})
elif ad.level == 'C':
city_code_list.append(ad.code)
province_code_list.append(ad.parent_code)
elif ad.level == 'D':
district_code_list.append(ad.code)
province_code_list.sort()
city_code_list.sort()
district_code_list.sort()
return list(set(province_code_list)), list(set(city_code_list)), list(
set(district_code_list))
async def get(self):
dark_skin = self.get_argument('dark_skin')
dark_skin = True if dark_skin == 'True' else False
region_code_list = []
manage_region_code_list = self.current_user.manage_region_code_list
ad_cursor = AdministrativeDivision.find(
{'code': {
'$in': self.current_user.manage_region_code_list
}})
while await ad_cursor.fetch_next:
ad = ad_cursor.next_object()
if ad:
if ad.level == 'P':
region_code_list.append('[p]%s' % ad.code)
elif ad.level == 'C':
region_code_list.append('[c]%s' % ad.code)
elif ad.level == 'D':
region_code_list.append('[d]%s' % ad.code)
source_list = await GameMemberSource.find({
'status':
STATUS_MEMBER_SOURCE_ACTIVE
}).sort('code').to_list(None)
source_dict = {source.code: source.title for source in source_list}
# fix: http://code.wenjuan.com/WolvesAU/CRSPN/issues/13
source_dict = adjust_source(source_dict)
return locals()
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 get_city_and_district(race_cid):
"""
得到安徽下面的所有市和区
:param race_cid:
:return:
"""
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
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})
return city_name_list, dist_list, lookup_stage
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
async def get(self):
dark_skin = self.get_argument('dark_skin')
dark_skin = True if dark_skin == 'True' else False
region_code_list = []
manage_region_code_list = self.current_user.manage_region_code_list
ad_cursor = AdministrativeDivision.find({'code': {'$in': self.current_user.manage_region_code_list}})
while await ad_cursor.fetch_next:
ad = ad_cursor.next_object()
if ad:
if ad.level == 'P':
region_code_list.append('[p]%s' % ad.code)
elif ad.level == 'C':
region_code_list.append('[c]%s' % ad.code)
elif ad.level == 'D':
region_code_list.append('[d]%s' % ad.code)
return locals()
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_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))
async def do_different_administrative_division(ad_code_list: list):
"""
区分行政区划等级
:param ad_code_list: 行政区划编码列表
:return: (省份, 城市, 区/县)
"""
province_code_list, city_code_list, district_code_list = [], [], []
if ad_code_list:
ad_cursor = AdministrativeDivision.find(
{'code': {
'$in': ad_code_list
}})
while await ad_cursor.fetch_next:
ad: AdministrativeDivision = ad_cursor.next_object()
if ad:
if ad.level == 'P':
province_code_list.append(ad.code)
elif ad.level == 'C':
city_code_list.append(ad.code)
elif ad.level == 'D':
district_code_list.append(ad.code)
return province_code_list, city_code_list, district_code_list
def generate_ad_models(division_list, parent_code=None, model_list=None):
if division_list:
for dd in division_list:
post_code = dd['code']
name = dd['name']
en_name = dd['en_name']
level = dd['level']
if not name == '县' and not name == '市辖区':
ad = AdministrativeDivision()
ad.code = post_code
ad.title = name
ad.en_title = en_name
ad.level = level
ad.parent_code = parent_code
if model_list is None:
model_list = []
model_list.append(ad)
c_division_list = dd.get('cell')
if c_division_list:
generate_ad_models(c_division_list, post_code, model_list)
async def get(self, user_id):
res = {'code': 0}
user = await User.get_by_id(user_id)
if user:
data = []
ad_cursor = AdministrativeDivision.find(
{'code': {
'$in': user.manage_region_code_list
}})
while await ad_cursor.fetch_next:
ad = ad_cursor.next_object()
if ad:
if ad.level == 'P':
data.append('[p]%s' % ad.code)
elif ad.level == 'C':
data.append('[c]%s' % ad.code)
elif ad.level == 'D':
data.append('[d]%s' % ad.code)
res['data'] = data
res['code'] = 1
else:
res['code'] = -1
return res
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))
# # cid_lsit = [check_point.cid for check_point in check_point_list]
# # his_list = MemberCheckPointHistory.sync_find({'check_point_cid': {'$in': cid_lsit}, "updated_dt":{'$gt': yes_day, '$lt': tom_day}}).to_list(None)
# # print(len(his_list), '999999')
#
# # 测试通关的
# city_history_list = MemberCheckPointHistory.sync_distinct("member_cid", {'check_point_cid': "AFC97D7D354C1D44BF64A5DDAB76E8D7", 'status': 1})
# race_mapping_list = RaceMapping.sync_distinct("member_cid", {'race_cid': 'CA755167DEA9AA89650D11C10FAA5413', "auth_address.city"})
# print(len(city_history_list))
# prov_history_list = MemberCheckPointHistory.sync_distinct("member_cid", {'check_point_cid': "05528C744EE42E838F7EE9D69070E6AA", 'status': 1})
# print(len(prov_history_list))
import datetime
import random
from db.models import AdministrativeDivision, RaceMapping, MemberCheckPointHistory, Member, Race
district_title_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': "341500"})
# match = {"race_cid": "3040737C97F7C7669B04BC39A660065D", "auth_address.city": "六安市", "auth_address.province": {'$ne': None},"auth_address.district": {'$in': ['金安区', '裕安区', '叶集区', '霍邱县', '舒城县', '金寨县', '霍山县', '开发区']}}
# city_match = {"race_cid": "CA755167DEA9AA89650D11C10FAA5413", "auth_address.city": "六安市","auth_address.province": {'$ne': None},"auth_address.district": {'$in': ['金安区', '裕安区', '叶集区', '霍邱县', '舒城县', '金寨县', '霍山县', '开发区']}}
#
# member_list = RaceMapping.sync_distinct("member_cid", match)
# print(len(member_list))
#
# city_member_list = RaceMapping.sync_distinct("member_cid", city_match)
# print(len(city_member_list))
# total_list = []
# total_list += member_list
# total_list += city_member_list
# print(len(list(total_list)))
# now = datetime.datetime.now()
num, 7,
pass_race_mapping.auth_address.get('town'))
position += 1
else:
continue
except StopIteration:
break
print("information calc end ------------------")
workbook.close()
if __name__ == '__main__':
# 安徽得code 340000
city_list = AdministrativeDivision.sync_find({
"parent_code": "341500"
}).to_list(None)
city_name_list = [city.title for city in city_list]
print(city_name_list)
dis_list = []
for city in city_list:
district_list = AdministrativeDivision.sync_distinct(
"title", {"parent_code": city.code})
for title in district_list:
if title not in dis_list:
dis_list.append(title)
print(dis_list, '9999')
prov_match = {
'race_cid': "CA755167DEA9AA89650D11C10FAA5413",
"auth_address.province": {
'$ne': None
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 get_administrative_division():
ad_data = RedisCache.get(KEY_ADMINISTRATIVE_DIVISION)
if ad_data:
return msgpack.unpackb(ad_data, raw=False)
def ad_2_dict(ad):
result = {}
if ad:
if isinstance(ad, (FacadeO, AdministrativeDivision)):
try:
result['code'] = ad.code
except Exception:
result['code'] = ad.post_code
if not ad.parent_code:
result['name'] = ad.title.replace('省', '').replace('市', '').replace('自治区', ''). \
replace('壮族', '').replace('回族', '').replace('维吾尔', '')
else:
result['name'] = ad.title
if ad.parent_code:
result['parent_code'] = ad.parent_code
result['sub'] = []
if ad.sub_list:
for su_ad in ad.sub_list:
if su_ad:
result['sub'].append(ad_2_dict(su_ad))
else:
result['code'] = ad.get('post_code')
parent_code = ad.get('parent_code')
if not parent_code:
result['name'] = ad.get('title').replace('省', '').replace('市', '').replace('自治区', ''). \
replace('壮族', '').replace('回族', '').replace('维吾尔', '')
else:
result['name'] = ad.get('title')
if parent_code:
result['parent_code'] = parent_code
result['sub'] = []
sub_list = ad.get('sub_list')
if sub_list:
for su_ad in sub_list:
if su_ad:
result['sub'].append(ad_2_dict(su_ad))
return result
ad_cursor = AdministrativeDivision.aggregate([
MatchStage(dict(parent_code=None)),
LookupStage(AdministrativeDivision,
as_list_name='sub_list',
let=dict(city_parent_code='$post_code'),
pipeline=[
MatchStage({
'$expr': {
'$and': [{
'$eq':
['$parent_code', '$$city_parent_code']
}]
}
}),
SortStage([('post_code', ASC)]),
LookupStage(AdministrativeDivision,
as_list_name='sub_list',
let=dict(area_parent_code='$post_code'),
pipeline=[
MatchStage({
'$expr': {
'$and': [{
'$eq': [
"$parent_code",
"$$area_parent_code"
]
}]
}
}),
SortStage([('post_code', ASC)])
])
]),
SortStage([('post_code', ASC)])
])
ad_list = []
while await ad_cursor.fetch_next:
ad_dict = ad_2_dict(ad_cursor.next_object())
if ad_dict:
ad_list.append(ad_dict)
if ad_list:
RedisCache.set(KEY_ADMINISTRATIVE_DIVISION, msgpack.packb(ad_list))
return ad_list
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
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 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 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))
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
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)
