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