def check(race_cid: str): """ 检查多少人参与活动但是没有答过题目的 :param race_cid: :return: """ race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] print(city_name_list, '1') for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) quantity = 0 member_cid_list = RaceMapping.sync_distinct( "member_cid", { 'race_cid': race_cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.province': { '$ne': None }, 'auth_address.district': { '$in': dist_list } }) check_point_cid = RaceGameCheckPoint.sync_distinct("cid", {"race_cid": race_cid}) for i in member_cid_list: member = Member.sync_get_by_cid(i) if member: history = MemberCheckPointHistory.sync_find_one({ 'check_point_cid': { '$in': check_point_cid }, "member_cid": i }) if not history: quantity += 1 print(quantity)
def do_stat_in_history(history_model, city_code_list, choice_time, ad_map={}): """ :param history_model: :param city_code_list: :param ad_map: :param choice_time :return: """ # 取前一天凌晨12点之前的数据 time_match = get_yesterday() if not choice_time: match_stage = MatchStage({'updated_dt': {'$lt': time_match}}) else: # 当天下一天凌晨的时候 max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999) match_stage = MatchStage({'updated_dt': {'$gte': choice_time, '$lt': max_choice_time}}) cursor = history_model.sync_aggregate([ match_stage, GroupStage('member_cid', quantity={"$sum": 1}), LookupStage(Member, '_id', 'cid', 'member_list'), ProjectStage(**{ 'province_code': {'$arrayElemAt': ['$member_list.province_code', 0]}, 'city_code': {'$arrayElemAt': ['$member_list.city_code', 0]}, 'quantity': '$quantity' }), MatchStage({'city_code': {'$in': city_code_list}}), GroupStage('city_code', quantity={'$sum': "$quantity"}, province_code={'$first': '$province_code'}), SortStage([('quantity', DESC)]) ]) data = {} while True: try: his = cursor.next() city_data = data.get(his.province_code, {}) city = ad_map.get(his.id) if not city: city = AdministrativeDivision.sync_find_one({'code': his.id, 'parent_code': {'$ne': None}}) ad_map[city.code] = city city_data[city.title] = his.quantity data[his.province_code] = city_data except StopIteration: break except Exception as e: logger.error(str(e)) continue return data, ad_map
def do_statistics_member_time(cache_key, city_code_list, choice_time): """开始统计 :param cache_key: :param city_code_list: :param choice_time: :return: """ RedisCache.set(cache_key, KEY_CACHE_REPORT_DOING_NOW, 5 * 60) ad_map = {} game_data, ad_map = do_stat_in_history(MemberGameHistory, city_code_list, choice_time, ad_map) ckpt_data, ad_map = do_stat_in_history(MemberCheckPointHistory, city_code_list, choice_time, ad_map) # 对学习之旅和科协答题历史记录进行数据整合 for k, city_dict in game_data.items(): if k not in ckpt_data: ckpt_data[k] = city_dict continue # loop city_list for c_name, c_data in city_dict.items(): try: # try to merge ckpt_data[k][c_name] += c_data except KeyError: ckpt_data[k][c_name] = c_data ret_data = [] for prov_code, city_data in ckpt_data.items(): prov = ad_map.get(prov_code) if not prov: prov = AdministrativeDivision.sync_find_one({'code': prov_code, 'parent_code': None}) city_list = [{'title': _k, 'data': _v} for _k, _v in city_data.items()] _ds = [_.get('data') for _ in city_list] city_list.sort(key=lambda x: -x.get('data')) ret_data.append( {'title': prov.title.replace('省', '').replace('市', ''), 'data': sum(_ds), 'city_list': city_list}) ret_data.sort(key=lambda x: -x.get('data')) if not ret_data: early_warning_empty("start_statistics_member_time", cache_key, city_code_list, '学习近况中次数数据为空,请检查!') RedisCache.set(cache_key, msgpack.packb(ret_data))
def export_race_data(workbook, race_cid: str, sheet_name): """ 导出每日参与人数,每日新增人数,每日新增人次 :param race_cid: :return: """ # yesterday_time = get_yesterday() # time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}}) race = Race.sync_get_by_cid(race_cid) city_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city in city_list: dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city}) match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } sheet = workbook.add_worksheet(sheet_name) cursor = RaceMapping.sync_aggregate([ MatchStage(match), ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$created_dt" } }, 'auth_address': "$auth_address", 'member_cid': "$member_cid" }), GroupStage( { 'daily_code': '$daily_code', 'district': '$auth_address.district' }, sum={'$sum': 1}, auth_address={'$first': '$auth_address'}), SortStage([('_id.daily_code', ASC)]) ]) sheet.write_string(0, 0, '城市') sheet.write_string(0, 1, '区县') daily_list = [] prize_list = [] county_map = {} v_list = list() _max_row = 0 while True: try: data = cursor.next() _current_row = None title = data.id.get('district') if title is None: title = '未知' if title not in prize_list: prize_list.append(title) _current_row = len(prize_list) city = data.auth_address.get('city') if not city: continue sheet.write_string(_current_row, 0, city) ad_city = AdministrativeDivision.sync_find_one({'title': city}) _county = AdministrativeDivision.sync_distinct( 'title', {'parent_code': ad_city.code}) for _c in _county: county_map[_c] = city sheet.write_string(_current_row, 1, title) else: _current_row = prize_list.index(title) + 1 daily_code = data.id.get('daily_code') if not daily_code: daily_code = '未知' if daily_code not in daily_list: daily_list.append(daily_code) _current_col = len(daily_list) + 1 sheet.write_string(0, _current_col, daily_code) else: _current_col = daily_list.index(daily_code) + 2 sheet.write_number(_current_row, _current_col, data.sum) v_list.append(data.sum) if _current_row >= _max_row: _max_row = _current_row except StopIteration: break except Exception as e: raise e for k, v in county_map.items(): if k not in prize_list: _max_row += 1 sheet.write_string(_max_row, 0, v) sheet.write_string(_max_row, 1, k) if _max_row: sheet.write_string(_max_row + 1, 0, '总和') sheet.write_number(_max_row + 1, 1, sum(v_list))
async def deal_race(race, province, city): """ :param race: :param province: :param city :return: """ race_dict = { 'race_cid': race.cid, 'status': 1, 'title_img_url': '', 'area': '', 'is_local': True } if race.start_datetime > datetime.now(): # 未开始 race_dict['status'] = 0 if race.end_datetime < datetime.now(): # 已结束 race_dict['status'] = 2 if (race.city_code and race.city_code != city.code) or ( race.province_code != province.code): race_dict['is_local'] = False if race.image_list: upload_file = race.image_list[0] title_img_url = '%s://%s%s%s%s' % (SERVER_PROTOCOL, SERVER_HOST, STATIC_URL_PREFIX, 'files/', upload_file.title) race_dict['title_img_url'] = title_img_url if race.province_list: race_dict['area'] = race.province_list[0].title if race.city_list: race_dict['area'] += '-' + race.city_list[0].title # 去除脏数据 if not race.city_code: # 省级活动 prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_list = AdministrativeDivision.sync_find({ 'parent_code': prov.code }).to_list(None) city_title_list = [city.title for city in city_list] city_code_list = [city.code for city in city_list] district_title_list = AdministrativeDivision.sync_distinct( "title", {'parent_code': { '$in': city_code_list }}) else: city = AdministrativeDivision.sync_find_one( {'code': race.city_code}) city_title_list = [city.title] district_title_list = AdministrativeDivision.sync_distinct( "title", {'parent_code': city.code}) start_daily_code = format(race.start_datetime, '%Y%m%d') if race.cid != "DF1EDC30F120AEE93351A005DC97B5C1": cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid) data = RedisCache.get(cache_key) if data: race_dict['people_count'] = int(data) else: cursor = RaceMemberEnterInfoStatistic.aggregate([ MatchStage({ 'race_cid': race.cid, 'daily_code': { '$gte': start_daily_code }, 'city': { '$in': city_title_list }, 'district': { '$in': district_title_list } }), GroupStage('race_cid', sum={'$sum': '$increase_enter_count'}) ]) count = 0 while await cursor.fetch_next: try: info = cursor.next_object() count = info.sum except StopIteration: break race_dict['people_count'] = count RedisCache.set(cache_key, count, 60 * 60) else: # 扬州显示次数 cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid) data = RedisCache.get(cache_key) if data: race_dict['people_count'] = int(data) else: cursor = RaceMemberEnterInfoStatistic.aggregate([ MatchStage({ 'race_cid': race.cid, 'daily_code': { '$gte': start_daily_code }, 'city': { '$in': city_title_list }, 'district': { '$in': district_title_list } }), GroupStage('race_cid', sum={'$sum': '$enter_times'}) ]) count = 0 while await cursor.fetch_next: try: info = cursor.next_object() count = info.sum except StopIteration: break race_dict['people_count'] = count RedisCache.set(cache_key, count, 60 * 60) race_dict['show_play_quantity'] = race.play_quantity_enabled race_dict['title'] = race.title return race_dict
def write_sheet_enter_data(book, race_cid, sheet_name, pre_match={}, count_type='$people_num'): """ 总共参与人数/人次 :param book: :param sheet_name: :param race_cid :param pre_match: :param count_type: :return: """ match = {'race_cid': race_cid} if pre_match: match.update(pre_match) race = Race.sync_get_by_cid(race_cid) match.update(get_region_match(race)) sheet = book.add_worksheet(sheet_name) group_stage = GroupStage( { 'daily_code': '$daily_code', 'district': '$district' }, sum={'$sum': count_type}, city={'$first': '$city'}) daily_list = ReportRacePeopleStatistics.sync_distinct('daily_code', match) daily_list.sort() sheet.write_string(0, 0, '城市') sheet.write_string(0, 1, '区县') daily_map = {} for index, daily_code in enumerate(daily_list): sheet.write_string(0, index + 2, daily_code) daily_map[daily_code] = index + 2 district_list = ReportRacePeopleStatistics.sync_distinct('district', match) district_map = {} for index, district in enumerate(district_list): sheet.write_string(index + 1, 1, district if district else '其他') district_map[district if district else '其他'] = index + 1 cursor = ReportRacePeopleStatistics.sync_aggregate( [MatchStage(match), group_stage]) county_map = dict() v_list = list() _max_row = 0 while True: try: stat = cursor.next() city = stat.city if stat.city else '其他' district = stat.id.get('district') daily_code = stat.id.get('daily_code') _row = district_map.get(district if district else '其他') sheet.write_string(_row, 0, city) ad_city = AdministrativeDivision.sync_find_one({'title': city}) _county = AdministrativeDivision.sync_distinct( 'title', {'parent_code': ad_city.code}) for _c in _county: county_map[_c] = city sheet.write_number(_row, daily_map.get(daily_code), stat.sum) if _row > _max_row: _max_row = _row v_list.append(stat.sum) except StopIteration: break except Exception as e: raise e for k, v in county_map.items(): if k not in district_map: _max_row += 1 sheet.write_string(_max_row, 0, v) sheet.write_string(_max_row, 1, k) sheet.write_string(_max_row + 1, 0, '总和') sheet.write_number(_max_row + 1, 1, sum(v_list))
worksheet.write_string(row, 2, SEX_DICT.get(member.sex) if member else '-') worksheet.write_string( row, 3, TYPE_AGE_GROUP_DICT.get(member.age_group) if member else '-') worksheet.write_string( row, 4, TYPE_EDUCATION_DICT.get(member.education) if member else '-') prov = city = '-' if member.province_code and member.city_code: if member.province_code in post_code_map: prov = post_code_map[member.province_code] else: prov = AdministrativeDivision.sync_find_one({ 'code': member.province_code }).title if member.city_code in post_code_map: city = post_code_map[member.city_code] else: city = AdministrativeDivision.sync_find_one({ 'code': member.city_code }).title post_code_map[member.province_code] = prov post_code_map[member.city_code] = city worksheet.write_string(row, 5, prov) worksheet.write_string(row, 6, city)
def export_race_enter_position(race_cid: str, title, n): """ 导出活动下面参与情况 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") current = datetime.datetime.now() export_time_list = [ (current + datetime.timedelta(days=-n)).replace(hour=0, minute=0, second=0) for n in (range(1, n)) ] export_time_list.sort() export_time = [ transform_time_format(export_dt) for export_dt in export_time_list ] export_time.sort() workbook = xlsxwriter.Workbook("{title}{today}.xlsx".format(title=title, today=today)) excel_name = "{title}{today}.xlsx".format(title=title, today=today) head_list = [ "新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额" ] sheet = workbook.add_worksheet("{title}一周数据".format(title=title)) data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei' }) sheet.merge_range(0, 0, 1, 0, "城市", data_center_format) sheet.merge_range(0, 1, 1, 1, "区县", data_center_format) sheet.write_string(1, 2, "累计人数") sheet.write_string(1, 3, "累计参与次数") sheet.write_string(1, 4, "累计红包发放总数") # excel填充一周日期 for i in range(1, n): sheet.merge_range(0, 7 * (i - 1) + 5 + i - 1, 0, 7 * (i - 1) + 5 + i - 1 + 7, export_time[i - 1], data_center_format) for head in range(7 * (i - 1) + 5 + i - 1, 7 * (i - 1) + 5 + i - 1 + 7 + 1): index = head - 8 * (i - 1) - 5 sheet.write_string(1, head, head_list[index]) prov_match = MatchStage({'province': {'$ne': None}}) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) prov_match = MatchStage({'province': prov.title}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)]) match = { "race_cid": race_cid, 'record_flag': 1, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list } } address_list = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(match), prov_match, ProjectStage(**{ 'province': "$province", "city": "$city", "district": "$district" }), GroupStage({ 'district': '$district', 'city': '$city' }, sum={'$sum': 1}), address_sort ]) # 拿到该活动下的所有区县和城市对应字典表 dis_map = get_race_district_mapping(sheet, address_list) # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} quantity_time_dict = {} # 每日参与人数一周的数据 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} enter_quantity_time_dict = {} # 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} count_time_dict = {} # 一周通关的人数 {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}} pass_quantity_time_dict = {} # 一周红包发放数量和金额的字典 red_give_dict = {} red_give_amount_dict = {} # 一周红包领取数量和金额的字典 red_receive_dict = {} red_receive_amount_dict = {} base_match = { 'race_cid': race_cid, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list }, 'daily_code': { '$gte': export_time[0], '$lte': transform_time_format( (export_time_list[-1] + datetime.timedelta(days=1))) } } total_match = copy.deepcopy(base_match) del total_match['daily_code'] total_district_count_dict = {} total_district_times_dict = {} total_send_red_packet_dict = {} race_total_info_cursor = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(total_match), GroupStage( { 'city': '$city', 'district': '$district' }, count_sum={'$sum': '$increase_enter_count'}, enter_times_sum={'$sum': '$enter_times'}, grant_red_packet_count={'$sum': "$grant_red_packet_count"}, ) ]) while True: try: data = race_total_info_cursor.next() district = data.id.get('district') if district not in total_district_count_dict: total_district_count_dict[district] = data.count_sum if district not in total_district_times_dict: total_district_times_dict[district] = data.enter_times_sum if district not in total_send_red_packet_dict: total_send_red_packet_dict[ district] = data.grant_red_packet_count except StopIteration: break except Exception as e: raise e # 每日参与人数,总计 for district, data in total_district_count_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 2, data) # 每日参与次数,总计 for district, data in total_district_times_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 3, data) # 每日累计发放红包数量 for district, data in total_send_red_packet_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 4, data) race_member_info_list = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(base_match), GroupStage( { "daily_code": "$daily_code", 'district': '$district', 'city': '$city' }, increase_quantity_sum={'$sum': '$increase_enter_count'}, enter_count_sum={'$sum': '$enter_count'}, pass_count_sum={'$sum': '$pass_count'}, enter_times_sum={'$sum': '$enter_times'}, grant_red_packet_count_sum={'$sum': '$grant_red_packet_count'}, grant_red_packet_amount_sum={'$sum': '$grant_red_packet_amount'}, draw_red_packet_count_sum={'$sum': '$draw_red_packet_count'}, draw_red_packet_amount_sum={'$sum': '$draw_red_packet_amount'}, ), address_sort ]) for race_member_info in race_member_info_list: daily = race_member_info.id.get('daily_code') city = race_member_info.id.get('city') district = race_member_info.id.get('district') if city and district: if daily not in quantity_time_dict: # 每日新增参与人数 quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.increase_quantity_sum } else: quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.increase_quantity_sum }) if daily not in enter_quantity_time_dict: # 每日参与人数 enter_quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.enter_count_sum } else: enter_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.enter_count_sum }) if daily not in count_time_dict: # 每日参与次数 count_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.enter_times_sum } else: count_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.enter_times_sum }) if daily not in pass_quantity_time_dict: # 每日通关人数 pass_quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.pass_count_sum } else: pass_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.pass_count_sum }) if daily not in red_give_dict: # 每日红包发放数量 red_give_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_count_sum } else: red_give_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_count_sum }) if daily not in red_give_amount_dict: # 每日红包发放金额 red_give_amount_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_amount_sum } else: red_give_amount_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_amount_sum }) if daily not in red_receive_dict: # 每日红包领取数量 red_receive_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_count_sum } else: red_receive_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_count_sum }) if daily not in red_receive_amount_dict: # 每日红包领取金额 red_receive_amount_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_amount_sum } else: red_receive_amount_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_amount_sum }) else: continue # 每日新增人数在excel的位置 quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, n)] # 每日参与人数在excel的位置 quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, n)] # 每日参与次数在excel的位置 count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, n)] # 每日通关次数在excel的位置 pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, n)] # 红包发放数量在excel的位置 red_give_position_list = [8 + 8 * (i - 1) for i in range(1, n)] # 红包发放金额在excel的位置 red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, n)] # 红包领取数量在excel的位置 red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, n)] # 红包领取金额在excel的位置 red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, n)] # 填充每日新增人数 write_excel_data(sheet, quantity_time_dict, dis_map, export_time, quantity_increase_position_list, save=None) # excel 填充每日参与人数 write_excel_data(sheet, enter_quantity_time_dict, dis_map, export_time, quantity_enter_position_list, save=None) # excel 填充每日参与次数 write_excel_data(sheet, count_time_dict, dis_map, export_time, count_enter_position_list, save=None) # excel 填充每日通关人数 write_excel_data(sheet, pass_quantity_time_dict, dis_map, export_time, pass_enter_position_list, save=None) # excel 填充每日发放个数 write_excel_data(sheet, red_give_dict, dis_map, export_time, red_give_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_give_amount_dict, dis_map, export_time, red_give_amount_list, save=2) # excel 填充每日领取个数 write_excel_data(sheet, red_receive_dict, dis_map, export_time, red_receive_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_receive_amount_dict, dis_map, export_time, red_receive_amount_list, save=2) workbook.close() return excel_name
def generate_race_middle(race_cid: str, start): """ 生成中间表 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") prov_match = MatchStage({'province': {'$ne': None}}) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct('code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct('title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one({'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct('code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct('title', {'parent_code': race.province_code}) prov_match = MatchStage({'province': prov.title}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct('title', {'parent_code': city_code}) lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') has_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list}, 'member_cid': {'$ne': None}, 'town': {'$ne': None}} no_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list}, 'member_cid': {'$ne': None}, 'town': {'$eq': None}} if start != "total": start_time = transform_time_format(start) end_time = transform_time_format(start + datetime.timedelta(days=1)) has_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time} no_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time} cursor = MemberStatisticInfo.sync_aggregate( [ MatchStage(has_town_match), lookup_stage, prov_match, MatchStage({'member_list': {'$ne': []}}), GroupStage( {"daily_code": "$daily_code", 'district': '$district', 'city': '$city', 'town': '$town'}, province={"$first": "$province"}, town={"$last": "$town"}, status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'}, enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"}, amount_sum={'$sum': "$grant_red_packet_amount"}, receive_count_sum={'$sum': "$draw_red_packet_count"}, receive_amount_sum={'$sum': "$draw_red_packet_amount"}, answer_times={'$sum': '$answer_times'}, true_answer_times={'$sum': '$true_answer_times'}, sum={'$sum': 1}), ] ) no_town_cursor = MemberStatisticInfo.sync_aggregate( [ MatchStage(no_town_match), lookup_stage, prov_match, MatchStage({'member_list': {'$ne': []}}), GroupStage( {"daily_code": "$daily_code", 'district': '$district', 'city': '$city'}, province={"$first": "$province"}, town={"$last": "$town"}, status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'}, enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"}, amount_sum={'$sum': "$grant_red_packet_amount"}, receive_count_sum={'$sum': "$draw_red_packet_count"}, receive_amount_sum={'$sum': "$draw_red_packet_amount"}, answer_times={'$sum': '$answer_times'}, true_answer_times={'$sum': '$true_answer_times'}, sum={'$sum': 1}), ] ) while True: try: race_member_info = cursor.next() daily_code = race_member_info.id.get('daily_code') status_list = race_member_info.status_list pass_status_list = race_member_info.pass_status_list district = race_member_info.id.get('district') city = race_member_info.id.get('city') if district and city: race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid) race_enter_info.province = race_member_info.province race_enter_info.city = city race_enter_info.district = district race_enter_info.town = race_member_info.town race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None race_enter_info.increase_enter_count = status_list.count(1) # 每日新增 race_enter_info.enter_count = race_member_info.sum # 每日参与 race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数 race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数 race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量 race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额 race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量 race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额 race_enter_info.correct_percent = round( race_member_info.true_answer_times / race_member_info.answer_times, 2) if race_member_info.answer_times != 0 else 0 race_enter_info.answer_times = race_member_info.answer_times # 总答题数量 race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量 race_enter_info.sync_save() else: continue except StopIteration: break except Exception as e: raise e while True: try: race_member_info = no_town_cursor.next() daily_code = race_member_info.id.get('daily_code') status_list = race_member_info.status_list pass_status_list = race_member_info.pass_status_list district = race_member_info.id.get('district') city = race_member_info.id.get('city') if district and city: race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid) race_enter_info.province = race_member_info.province race_enter_info.city = city race_enter_info.district = district race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None race_enter_info.increase_enter_count = status_list.count(1) # 每日新增 race_enter_info.enter_count = race_member_info.sum # 每日参与 race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数 race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数 race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量 race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额 race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量 race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额 race_enter_info.correct_percent = round( race_member_info.true_answer_times / race_member_info.answer_times, 2) if race_member_info.answer_times != 0 else 0 # 正确率 race_enter_info.answer_times = race_member_info.answer_times # 总答题数量 race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量 race_enter_info.sync_save() else: continue except StopIteration: break except Exception as e: raise e
def wash_data(): """ 清洗统计数据 :return: """ race_map = {} prov_map = {} cursor = ReportRacePeopleStatistics.sync_find() while True: try: stat = cursor.next() if not (stat.province and stat.city and stat.district): print('delete') stat.sync_delete() continue if race_map.get(stat.race_cid): race = race_map.get(stat.race_cid) else: race = Race.sync_find_one({'cid': stat.race_cid}) race_map[stat.race_cid] = race prov = prov_map.get(stat.province) if not prov: prov = AdministrativeDivision.sync_find_one({ 'title': { '$regex': stat.province }, 'parent_code': None }) prov_map[stat.province] = prov if prov.code != race.province_code: print('xxxxx') stat.sync_delete() except StopIteration: break except Exception as e: pdb.set_trace() cursor = RaceMapping.sync_find() while True: try: _p = cursor.next() if not _p.auth_address or not _p.auth_address.get('district'): member = Member.sync_find_one({'cid': _p.member_cid}) if not member or not member.auth_address or not member.auth_address.get( 'district'): _p.sync_delete() continue _p.auth_address = member.auth_address _p.sync_save() print('update mapping') if race_map.get(_p.race_cid): race = race_map.get(_p.race_cid) else: race = Race.sync_find_one({'cid': _p.race_cid}) race_map[_p.race_cid] = race province = _p.auth_address.get('province') if province: prov = prov_map.get(province) if not prov: prov = AdministrativeDivision.sync_find_one({ 'title': { '$regex': province }, 'parent_code': None }) prov_map[province] = prov.code if prov.code != race.province_code: print(' mapping delete ') _p.sync_delete() except StopIteration: break except Exception as e: print(e) raise e