def get_address(race_cid): """ 获取活动的城市列表、区域列表,并缓存 :param race_cid: :return: """ city_list_str_key = '%s_CITY_LIST_STR' % race_cid district_list_str_key = '%s_DISTRICT_LIST_STR' % race_cid city_name_list_str = RedisCache.get(city_list_str_key) district_name_list_str = RedisCache.get(district_list_str_key) if not city_name_list_str or not district_name_list_str: # race_province_code,race_city_code缓存,若city_code不为空,则为市级活动 pro_code_key = '%s_province_code' % race_cid city_code_key = '%s_city_code' % race_cid province_code = RedisCache.get(pro_code_key) city_code = RedisCache.get(city_code_key) if not province_code or not city_code: race = Race.sync_get_by_cid(race_cid) RedisCache.set(pro_code_key, race.province_code, 12 * 60 * 60) RedisCache.set(city_code_key, race.city_code, 12 * 60 * 60) if city_code: city_code_list = AdministrativeDivision.sync_distinct('code', {'code': city_code}) city_name_list = AdministrativeDivision.sync_distinct('title', {'code': city_code}) else: city_code_list = AdministrativeDivision.sync_distinct('code', {'parent_code': province_code}) city_name_list = AdministrativeDivision.sync_distinct('title', {'parent_code': province_code}) district_name_list = AdministrativeDivision.sync_distinct('title', {'parent_code': {'$in': city_code_list}}) RedisCache.set(','.join(city_name_list), 12 * 60 * 60) RedisCache.set(','.join(district_name_list), 12 * 60 * 60) else: city_name_list = city_name_list_str.split(',') district_name_list = district_name_list_str.split(',') return city_name_list, district_name_list
def get_region_match(race): city_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city in city_list: dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city}) return {'city': {'$in': city_name_list}, 'district': {'$in': dist_list}}
async def get(self): race_cid = self.get_argument('race_cid', '') menu_list = await get_menu(self, 'report', race_cid, tag=1) region_code_list = [] manage_region_code_list = self.current_user.manage_region_code_list ad_cursor = AdministrativeDivision.find( {'code': { '$in': self.current_user.manage_region_code_list }}) while await ad_cursor.fetch_next: ad = ad_cursor.next_object() if ad: if ad.level == 'P': region_code_list.append('[p]%s' % ad.code) elif ad.level == 'C': region_code_list.append('[c]%s' % ad.code) elif ad.level == 'D': region_code_list.append('[d]%s' % ad.code) subject_dimension_list = await SubjectDimension.aggregate([ MatchStage( dict(status=STATUS_SUBJECT_DIMENSION_ACTIVE, parent_cid=None)), LookupStage(SubjectDimension, 'cid', 'parent_cid', 'sub_subject_dimension_list') ]).to_list(None) return locals()
async def do_different_administrative_division2(ad_code_list: list): """ 区分行政区划等级 :param ad_code_list: 行政区划编码列表 :return: (省份, 城市, 区/县) """ province_code_list, city_code_list, district_code_list = [], [], [] if not ad_code_list: raise ValueError('There are no manageable areas for this account.') ad_cursor = AdministrativeDivision.find({'code': {'$in': ad_code_list}}) while await ad_cursor.fetch_next: ad: AdministrativeDivision = ad_cursor.next_object() if ad: if ad.level == 'P': province_code_list.append(ad.code) city_code_list += await AdministrativeDivision.distinct( 'code', {'parent_code': ad.code}) elif ad.level == 'C': city_code_list.append(ad.code) province_code_list.append(ad.parent_code) elif ad.level == 'D': district_code_list.append(ad.code) province_code_list.sort() city_code_list.sort() district_code_list.sort() return list(set(province_code_list)), list(set(city_code_list)), list( set(district_code_list))
async def get(self): dark_skin = self.get_argument('dark_skin') dark_skin = True if dark_skin == 'True' else False region_code_list = [] manage_region_code_list = self.current_user.manage_region_code_list ad_cursor = AdministrativeDivision.find( {'code': { '$in': self.current_user.manage_region_code_list }}) while await ad_cursor.fetch_next: ad = ad_cursor.next_object() if ad: if ad.level == 'P': region_code_list.append('[p]%s' % ad.code) elif ad.level == 'C': region_code_list.append('[c]%s' % ad.code) elif ad.level == 'D': region_code_list.append('[d]%s' % ad.code) source_list = await GameMemberSource.find({ 'status': STATUS_MEMBER_SOURCE_ACTIVE }).sort('code').to_list(None) source_dict = {source.code: source.title for source in source_list} # fix: http://code.wenjuan.com/WolvesAU/CRSPN/issues/13 source_dict = adjust_source(source_dict) return locals()
def write_sheet_race_member_information(race_cid: str, post_code): """ 导出该活动下面得会员信息,主要是手机号码 :param race_cid: :param post_code :return: excel """ workbook = xlsxwriter.Workbook('黄山市会员信息统计.xlsx') sheet = workbook.add_worksheet('黄山市会员信息') sheet.merge_range(0, 0, 0, 1, '昵称') sheet.write_string(0, 2, '地区') sheet.merge_range(0, 3, 0, 4, '手机号码') lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') district_lookup_stage = LookupStage(AdministrativeDivision, 'district_code', 'post_code', 'district_list') match_stage = MatchStage({ 'race_cid': race_cid, 'record_flag': 1, 'city_code': post_code }) # 该市下面所有得区 district_title_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': post_code}) race_mapping_cursor = RaceMapping.sync_aggregate( [match_stage, lookup_stage, district_lookup_stage]) data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei', 'border': 1 }) num = 1 while True: try: race_mapping = race_mapping_cursor.next() if race_mapping: if race_mapping.member_list and race_mapping.district_list: member = race_mapping.member_list[0] district = race_mapping.district_list[0] if district.title in district_title_list: sheet.merge_range(num, 0, num, 1, member.nick_name, data_center_format) sheet.write_string(num, 2, district.title, data_center_format) sheet.merge_range(num, 3, num, 4, str(race_mapping.mobile), data_center_format) num += 1 else: continue except StopIteration: break workbook.close()
def get_city_and_district(race_cid): """ 得到安徽下面的所有市和区 :param race_cid: :return: """ lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') race = Race.sync_get_by_cid(race_cid) city_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city in city_list: dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city}) return city_name_list, dist_list, lookup_stage
def do_stat_in_history(history_model, city_code_list, choice_time, ad_map={}): """ :param history_model: :param city_code_list: :param ad_map: :param choice_time :return: """ # 取前一天凌晨12点之前的数据 time_match = get_yesterday() if not choice_time: match_stage = MatchStage({'updated_dt': {'$lt': time_match}}) else: # 当天下一天凌晨的时候 max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999) match_stage = MatchStage({'updated_dt': {'$gte': choice_time, '$lt': max_choice_time}}) cursor = history_model.sync_aggregate([ match_stage, GroupStage('member_cid', quantity={"$sum": 1}), LookupStage(Member, '_id', 'cid', 'member_list'), ProjectStage(**{ 'province_code': {'$arrayElemAt': ['$member_list.province_code', 0]}, 'city_code': {'$arrayElemAt': ['$member_list.city_code', 0]}, 'quantity': '$quantity' }), MatchStage({'city_code': {'$in': city_code_list}}), GroupStage('city_code', quantity={'$sum': "$quantity"}, province_code={'$first': '$province_code'}), SortStage([('quantity', DESC)]) ]) data = {} while True: try: his = cursor.next() city_data = data.get(his.province_code, {}) city = ad_map.get(his.id) if not city: city = AdministrativeDivision.sync_find_one({'code': his.id, 'parent_code': {'$ne': None}}) ad_map[city.code] = city city_data[city.title] = his.quantity data[his.province_code] = city_data except StopIteration: break except Exception as e: logger.error(str(e)) continue return data, ad_map
async def get(self): dark_skin = self.get_argument('dark_skin') dark_skin = True if dark_skin == 'True' else False region_code_list = [] manage_region_code_list = self.current_user.manage_region_code_list ad_cursor = AdministrativeDivision.find({'code': {'$in': self.current_user.manage_region_code_list}}) while await ad_cursor.fetch_next: ad = ad_cursor.next_object() if ad: if ad.level == 'P': region_code_list.append('[p]%s' % ad.code) elif ad.level == 'C': region_code_list.append('[c]%s' % ad.code) elif ad.level == 'D': region_code_list.append('[d]%s' % ad.code) return locals()
def check(race_cid: str): """ 检查多少人参与活动但是没有答过题目的 :param race_cid: :return: """ race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] print(city_name_list, '1') for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) quantity = 0 member_cid_list = RaceMapping.sync_distinct( "member_cid", { 'race_cid': race_cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.province': { '$ne': None }, 'auth_address.district': { '$in': dist_list } }) check_point_cid = RaceGameCheckPoint.sync_distinct("cid", {"race_cid": race_cid}) for i in member_cid_list: member = Member.sync_get_by_cid(i) if member: history = MemberCheckPointHistory.sync_find_one({ 'check_point_cid': { '$in': check_point_cid }, "member_cid": i }) if not history: quantity += 1 print(quantity)
def do_statistics_member_time(cache_key, city_code_list, choice_time): """开始统计 :param cache_key: :param city_code_list: :param choice_time: :return: """ RedisCache.set(cache_key, KEY_CACHE_REPORT_DOING_NOW, 5 * 60) ad_map = {} game_data, ad_map = do_stat_in_history(MemberGameHistory, city_code_list, choice_time, ad_map) ckpt_data, ad_map = do_stat_in_history(MemberCheckPointHistory, city_code_list, choice_time, ad_map) # 对学习之旅和科协答题历史记录进行数据整合 for k, city_dict in game_data.items(): if k not in ckpt_data: ckpt_data[k] = city_dict continue # loop city_list for c_name, c_data in city_dict.items(): try: # try to merge ckpt_data[k][c_name] += c_data except KeyError: ckpt_data[k][c_name] = c_data ret_data = [] for prov_code, city_data in ckpt_data.items(): prov = ad_map.get(prov_code) if not prov: prov = AdministrativeDivision.sync_find_one({'code': prov_code, 'parent_code': None}) city_list = [{'title': _k, 'data': _v} for _k, _v in city_data.items()] _ds = [_.get('data') for _ in city_list] city_list.sort(key=lambda x: -x.get('data')) ret_data.append( {'title': prov.title.replace('省', '').replace('市', ''), 'data': sum(_ds), 'city_list': city_list}) ret_data.sort(key=lambda x: -x.get('data')) if not ret_data: early_warning_empty("start_statistics_member_time", cache_key, city_code_list, '学习近况中次数数据为空,请检查!') RedisCache.set(cache_key, msgpack.packb(ret_data))
async def do_different_administrative_division(ad_code_list: list): """ 区分行政区划等级 :param ad_code_list: 行政区划编码列表 :return: (省份, 城市, 区/县) """ province_code_list, city_code_list, district_code_list = [], [], [] if ad_code_list: ad_cursor = AdministrativeDivision.find( {'code': { '$in': ad_code_list }}) while await ad_cursor.fetch_next: ad: AdministrativeDivision = ad_cursor.next_object() if ad: if ad.level == 'P': province_code_list.append(ad.code) elif ad.level == 'C': city_code_list.append(ad.code) elif ad.level == 'D': district_code_list.append(ad.code) return province_code_list, city_code_list, district_code_list
def generate_ad_models(division_list, parent_code=None, model_list=None): if division_list: for dd in division_list: post_code = dd['code'] name = dd['name'] en_name = dd['en_name'] level = dd['level'] if not name == '县' and not name == '市辖区': ad = AdministrativeDivision() ad.code = post_code ad.title = name ad.en_title = en_name ad.level = level ad.parent_code = parent_code if model_list is None: model_list = [] model_list.append(ad) c_division_list = dd.get('cell') if c_division_list: generate_ad_models(c_division_list, post_code, model_list)
async def get(self, user_id): res = {'code': 0} user = await User.get_by_id(user_id) if user: data = [] ad_cursor = AdministrativeDivision.find( {'code': { '$in': user.manage_region_code_list }}) while await ad_cursor.fetch_next: ad = ad_cursor.next_object() if ad: if ad.level == 'P': data.append('[p]%s' % ad.code) elif ad.level == 'C': data.append('[c]%s' % ad.code) elif ad.level == 'D': data.append('[d]%s' % ad.code) res['data'] = data res['code'] = 1 else: res['code'] = -1 return res
def export_race_data(workbook, race_cid: str, sheet_name): """ 导出每日参与人数,每日新增人数,每日新增人次 :param race_cid: :return: """ # yesterday_time = get_yesterday() # time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}}) race = Race.sync_get_by_cid(race_cid) city_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city in city_list: dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city}) match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } sheet = workbook.add_worksheet(sheet_name) cursor = RaceMapping.sync_aggregate([ MatchStage(match), ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$created_dt" } }, 'auth_address': "$auth_address", 'member_cid': "$member_cid" }), GroupStage( { 'daily_code': '$daily_code', 'district': '$auth_address.district' }, sum={'$sum': 1}, auth_address={'$first': '$auth_address'}), SortStage([('_id.daily_code', ASC)]) ]) sheet.write_string(0, 0, '城市') sheet.write_string(0, 1, '区县') daily_list = [] prize_list = [] county_map = {} v_list = list() _max_row = 0 while True: try: data = cursor.next() _current_row = None title = data.id.get('district') if title is None: title = '未知' if title not in prize_list: prize_list.append(title) _current_row = len(prize_list) city = data.auth_address.get('city') if not city: continue sheet.write_string(_current_row, 0, city) ad_city = AdministrativeDivision.sync_find_one({'title': city}) _county = AdministrativeDivision.sync_distinct( 'title', {'parent_code': ad_city.code}) for _c in _county: county_map[_c] = city sheet.write_string(_current_row, 1, title) else: _current_row = prize_list.index(title) + 1 daily_code = data.id.get('daily_code') if not daily_code: daily_code = '未知' if daily_code not in daily_list: daily_list.append(daily_code) _current_col = len(daily_list) + 1 sheet.write_string(0, _current_col, daily_code) else: _current_col = daily_list.index(daily_code) + 2 sheet.write_number(_current_row, _current_col, data.sum) v_list.append(data.sum) if _current_row >= _max_row: _max_row = _current_row except StopIteration: break except Exception as e: raise e for k, v in county_map.items(): if k not in prize_list: _max_row += 1 sheet.write_string(_max_row, 0, v) sheet.write_string(_max_row, 1, k) if _max_row: sheet.write_string(_max_row + 1, 0, '总和') sheet.write_number(_max_row + 1, 1, sum(v_list))
# # cid_lsit = [check_point.cid for check_point in check_point_list] # # his_list = MemberCheckPointHistory.sync_find({'check_point_cid': {'$in': cid_lsit}, "updated_dt":{'$gt': yes_day, '$lt': tom_day}}).to_list(None) # # print(len(his_list), '999999') # # # 测试通关的 # city_history_list = MemberCheckPointHistory.sync_distinct("member_cid", {'check_point_cid': "AFC97D7D354C1D44BF64A5DDAB76E8D7", 'status': 1}) # race_mapping_list = RaceMapping.sync_distinct("member_cid", {'race_cid': 'CA755167DEA9AA89650D11C10FAA5413', "auth_address.city"}) # print(len(city_history_list)) # prov_history_list = MemberCheckPointHistory.sync_distinct("member_cid", {'check_point_cid': "05528C744EE42E838F7EE9D69070E6AA", 'status': 1}) # print(len(prov_history_list)) import datetime import random from db.models import AdministrativeDivision, RaceMapping, MemberCheckPointHistory, Member, Race district_title_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': "341500"}) # match = {"race_cid": "3040737C97F7C7669B04BC39A660065D", "auth_address.city": "六安市", "auth_address.province": {'$ne': None},"auth_address.district": {'$in': ['金安区', '裕安区', '叶集区', '霍邱县', '舒城县', '金寨县', '霍山县', '开发区']}} # city_match = {"race_cid": "CA755167DEA9AA89650D11C10FAA5413", "auth_address.city": "六安市","auth_address.province": {'$ne': None},"auth_address.district": {'$in': ['金安区', '裕安区', '叶集区', '霍邱县', '舒城县', '金寨县', '霍山县', '开发区']}} # # member_list = RaceMapping.sync_distinct("member_cid", match) # print(len(member_list)) # # city_member_list = RaceMapping.sync_distinct("member_cid", city_match) # print(len(city_member_list)) # total_list = [] # total_list += member_list # total_list += city_member_list # print(len(list(total_list))) # now = datetime.datetime.now()
num, 7, pass_race_mapping.auth_address.get('town')) position += 1 else: continue except StopIteration: break print("information calc end ------------------") workbook.close() if __name__ == '__main__': # 安徽得code 340000 city_list = AdministrativeDivision.sync_find({ "parent_code": "341500" }).to_list(None) city_name_list = [city.title for city in city_list] print(city_name_list) dis_list = [] for city in city_list: district_list = AdministrativeDivision.sync_distinct( "title", {"parent_code": city.code}) for title in district_list: if title not in dis_list: dis_list.append(title) print(dis_list, '9999') prov_match = { 'race_cid': "CA755167DEA9AA89650D11C10FAA5413", "auth_address.province": { '$ne': None
def export_race_enter_position(race_cid: str, title): """ 导出活动下面参与情况 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") now = datetime.datetime.now() export_time_list = [ now + datetime.timedelta(days=-n) for n in (range(1, 8)) ] export_time_list.sort() export_time = [ transform_time_format(export_dt) for export_dt in export_time_list ] export_time.sort() workbook = xlsxwriter.Workbook(title + today + ".xlsx") head_list = [ "新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额" ] sheet = workbook.add_worksheet(title + "一周数据") data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei' }) sheet.merge_range(0, 0, 1, 0, "城市", data_center_format) sheet.merge_range(0, 1, 1, 1, "区县", data_center_format) sheet.write_string(1, 2, "累计人数") sheet.write_string(1, 3, "累计参与次数") for i in range(1, 8): sheet.merge_range(0, 7 * (i - 1) + 4 + i - 1, 0, 7 * (i - 1) + 4 + i - 1 + 7, export_time[i - 1], data_center_format) for head in range(7 * (i - 1) + 4 + i - 1, 7 * (i - 1) + 4 + i - 1 + 7 + 1): index = head - 8 * (i - 1) - 4 sheet.write_string(1, head, head_list[index]) midnight = (datetime.datetime.now()).replace(hour=0, minute=0, second=0, microsecond=0) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) # 最基本的人数match base_quantity_match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } # 最基本的次数match base_count_match = { 'race_cid': race_cid, 'province': { '$ne': None }, "record_$pushflag": 1, 'city': { "$in": city_name_list }, 'district': { "$in": dist_list } } # 最基本的参与人数match base_enter_quantity_match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } # 累计人数 quantity_match_stage = MatchStage({ 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list }, 'created_dt': { '$lte': midnight } }) # 累计次数 daily_code = datetime2str(datetime.datetime.now(), date_format='%Y%m%d') count_match = { 'race_cid': race_cid, 'province': { '$ne': None }, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list }, 'daily_code': { '$lt': daily_code } } lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)]) quantity_list = RaceMapping.sync_aggregate([ quantity_match_stage, lookup_stage, ProjectStage(**{ 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) count_list = ReportRacePeopleStatistics.sync_aggregate([ MatchStage(count_match), GroupStage({ 'city': '$city', 'district': '$district' }, sum={'$sum': "$total_num"}) ]) dis_map = {} quantity = 0 for index, address in enumerate(quantity_list): quantity += address.sum sheet.write_string(index + 2, 0, address.id.get('city')) sheet.write_string(index + 2, 1, address.id.get('district')) sheet.write_number(index + 2, 2, address.sum) if address.id.get('district') not in dis_map: dis_map[address.id.get('district')] = index + 2 else: dis_map[address.id.get('district')] += index + 2 count_map = {} for count in count_list: district = count.id.get('district') if district not in count_map: count_map[district] = count.sum print(count_map, 'count') print(dis_map, 'dis_map') for k, v in count_map.items(): position = dis_map.get(k) if position: sheet.write_number(position, 3, v) # 有答题次数,没有人数的情况,跳过 else: continue # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} quantity_time_dict = {} # 一个星期的人数的数据 base_quantity_match['created_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } one_week_quantity_list = RaceMapping.sync_aggregate([ MatchStage(base_quantity_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$created_dt" } }, 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { "daily_code": "$daily_code", 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) for quantity_data in one_week_quantity_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in quantity_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum quantity_time_dict[daily] = temp_dict else: quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # 每日参与人数一周的数据 # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} base_enter_quantity_match['updated_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } enter_quantity_time_dict = {} one_week_enter_quantity_list = RaceMapping.sync_aggregate([ MatchStage(base_enter_quantity_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$updated_dt" } }, 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { "daily_code": "$daily_code", 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) for quantity_data in one_week_enter_quantity_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in enter_quantity_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum enter_quantity_time_dict[daily] = temp_dict else: enter_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # print(enter_quantity_time_dict, 'enter_quantity') # 每日新增参与次数一周的数据 base_count_match['created_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } one_week_count_list = ReportRacePeopleStatistics.sync_aggregate([ MatchStage(base_count_match), GroupStage( { 'city': '$city', 'district': '$district', 'daily_code': '$daily_code' }, sum={'$sum': "$total_num"}) ]) # 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} count_time_dict = {} for quantity_data in one_week_count_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in count_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum count_time_dict[daily] = temp_dict else: count_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # 一周通关的人数 # {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}} pass_quantity_time_dict = {} last_checkpoint_cid, _, _, _ = get_export_param(race_cid) # 拿到最后一关的cid pass_match_stage = MatchStage({ 'check_point_cid': last_checkpoint_cid, 'status': 1, 'record_flag': 1, 'created_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } }) check_point_cursor = MemberCheckPointHistory.sync_aggregate( [pass_match_stage, lookup_stage]) member_cid_list = [] while True: try: check_point = check_point_cursor.next() if check_point.member_list: member = check_point.member_list[0] race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': member.cid }) if race_mapping: if race_mapping.auth_address and race_mapping.auth_address.get( 'province'): if check_point.member_cid not in member_cid_list: district = race_mapping.auth_address.get( 'district') city = race_mapping.auth_address.get('city') if district and city: member_cid_list.append(check_point.member_cid) created_time = format(check_point.created_dt, "%Y%m%d") if created_time not in pass_quantity_time_dict: pass_quantity_time_dict[created_time] = { "{city}-{district}".format(city=city, district=district): 1 } else: city_district = "{city}-{district}".format( city=city, district=district) v_dict = pass_quantity_time_dict.get( created_time) if city_district in v_dict: v_dict[city_district] += 1 else: v_dict[city_district] = 1 else: continue except StopIteration: break except Exception as e: raise e # 每日新增人数在excel的位置 quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, 8)] # 每日参与人数在excel的位置 quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, 8)] # 每日参与次数在excel的位置 count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, 8)] # 每日通关次数在excel的位置 pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, 8)] # 红包发放数量在excel的位置 red_give_position_list = [8 + 8 * (i - 1) for i in range(1, 8)] # 红包发放金额在excel的位置 red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, 8)] # 红包领取数量在excel的位置 red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, 8)] # 红包领取金额在excel的位置 red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, 8)] print(quantity_increase_position_list) print(dis_map, 'dis_map6') print(quantity, 'quan') print(quantity_time_dict, 'quantity_time') # 填充每日新增人数 write_excel_data(sheet, quantity_time_dict, dis_map, export_time, quantity_increase_position_list, save=None) # excel 填充每日参与人数 write_excel_data(sheet, enter_quantity_time_dict, dis_map, export_time, quantity_enter_position_list, save=None) # excel 填充每日参与次数 write_excel_data(sheet, count_time_dict, dis_map, export_time, count_enter_position_list, save=None) # excel 填充每日通关人数 write_excel_data(sheet, pass_quantity_time_dict, dis_map, export_time, pass_enter_position_list, save=None) red_give_dict = {} red_give_amount_dict = {} # 红包发放个数 red_give_out_match = { "race_cid": race_cid, 'record_flag': 1, "award_cid": { '$ne': None }, 'member_cid': { '$ne': None }, 'draw_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } } red_give_out_cursor = RedPacketBox.sync_aggregate([ MatchStage(red_give_out_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$draw_dt" } }, "member_cid": '$member_cid', "member_list": "$member_list", "award_amount": '$award_amount', }), MatchStage({'member_list': { '$ne': [] }}), GroupStage({"daily_code": "$daily_code"}, amount_list={'$push': '$award_amount'}, cid_list={'$push': '$member_cid'}, sum={'$sum': 1}) ]) while True: try: red_packet = red_give_out_cursor.next() if red_packet and red_packet.cid_list: cid_list = red_packet.cid_list amount_list = red_packet.amount_list print(len(amount_list), 'len_m') print(len(cid_list), 'len') daily = red_packet.id.get('daily_code') for cid, amount in zip(cid_list, amount_list): race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.district': { '$in': dist_list } }) if race_mapping: city = race_mapping.auth_address.get('city') district = race_mapping.auth_address.get('district') city_district = "{city}-{district}".format( city=city, district=district) if daily not in red_give_dict: red_give_dict[daily] = {city_district: 1} else: v_dict = red_give_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = 1 else: v_dict[city_district] += 1 if daily not in red_give_amount_dict: red_give_amount_dict[daily] = { city_district: amount } else: v_dict = red_give_amount_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = amount else: v_dict[city_district] += amount except StopIteration: break except Exception as e: raise e print(red_give_dict, 'dict2') print(red_give_amount_dict, 'amount-dict2') # excel 填充每日发放个数 write_excel_data(sheet, red_give_dict, dis_map, export_time, red_give_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_give_amount_dict, dis_map, export_time, red_give_amount_list, save=2) # 红包领取个数 red_receive_dict = {} red_receive_amount_dict = {} red_receive_match = { "race_cid": race_cid, 'record_flag': 1, "award_cid": { '$ne': None }, 'draw_status': 0, 'member_cid': { '$ne': None }, 'draw_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } } red_receive_cursor = RedPacketBox.sync_aggregate([ MatchStage(red_receive_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$draw_dt" } }, "member_cid": '$member_cid', "member_list": "$member_list", "award_amount": '$award_amount', }), MatchStage({'member_list': { '$ne': [] }}), GroupStage({"daily_code": "$daily_code"}, receive_amount_list={'$push': '$award_amount'}, cid_list={'$push': '$member_cid'}, sum={'$sum': 1}) ]) while True: try: red_packet = red_receive_cursor.next() if red_packet and red_packet.cid_list: amount_list = red_packet.receive_amount_list cid_list = list(set(red_packet.cid_list)) print(len(cid_list), 'len') daily = red_packet.id.get('daily_code') for cid, amount in zip(cid_list, amount_list): race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.district': { '$in': dist_list } }) if race_mapping: city = race_mapping.auth_address.get('city') district = race_mapping.auth_address.get('district') city_district = "{city}-{district}".format( city=city, district=district) if daily not in red_receive_dict: red_receive_dict[daily] = {city_district: 1} else: v_dict = red_receive_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = 1 else: v_dict[city_district] += 1 if daily not in red_receive_amount_dict: red_receive_amount_dict[daily] = { city_district: amount } else: v_dict = red_receive_amount_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = amount else: v_dict[city_district] += amount except StopIteration: break except Exception as e: raise e print(red_receive_dict, 'receive_cursor') print(red_receive_amount_dict, 'rece_amount') # excel 填充每日领取个数 write_excel_data(sheet, red_receive_dict, dis_map, export_time, red_receive_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_receive_amount_dict, dis_map, export_time, red_receive_amount_list, save=2) workbook.close()
async def get_administrative_division(): ad_data = RedisCache.get(KEY_ADMINISTRATIVE_DIVISION) if ad_data: return msgpack.unpackb(ad_data, raw=False) def ad_2_dict(ad): result = {} if ad: if isinstance(ad, (FacadeO, AdministrativeDivision)): try: result['code'] = ad.code except Exception: result['code'] = ad.post_code if not ad.parent_code: result['name'] = ad.title.replace('省', '').replace('市', '').replace('自治区', ''). \ replace('壮族', '').replace('回族', '').replace('维吾尔', '') else: result['name'] = ad.title if ad.parent_code: result['parent_code'] = ad.parent_code result['sub'] = [] if ad.sub_list: for su_ad in ad.sub_list: if su_ad: result['sub'].append(ad_2_dict(su_ad)) else: result['code'] = ad.get('post_code') parent_code = ad.get('parent_code') if not parent_code: result['name'] = ad.get('title').replace('省', '').replace('市', '').replace('自治区', ''). \ replace('壮族', '').replace('回族', '').replace('维吾尔', '') else: result['name'] = ad.get('title') if parent_code: result['parent_code'] = parent_code result['sub'] = [] sub_list = ad.get('sub_list') if sub_list: for su_ad in sub_list: if su_ad: result['sub'].append(ad_2_dict(su_ad)) return result ad_cursor = AdministrativeDivision.aggregate([ MatchStage(dict(parent_code=None)), LookupStage(AdministrativeDivision, as_list_name='sub_list', let=dict(city_parent_code='$post_code'), pipeline=[ MatchStage({ '$expr': { '$and': [{ '$eq': ['$parent_code', '$$city_parent_code'] }] } }), SortStage([('post_code', ASC)]), LookupStage(AdministrativeDivision, as_list_name='sub_list', let=dict(area_parent_code='$post_code'), pipeline=[ MatchStage({ '$expr': { '$and': [{ '$eq': [ "$parent_code", "$$area_parent_code" ] }] } }), SortStage([('post_code', ASC)]) ]) ]), SortStage([('post_code', ASC)]) ]) ad_list = [] while await ad_cursor.fetch_next: ad_dict = ad_2_dict(ad_cursor.next_object()) if ad_dict: ad_list.append(ad_dict) if ad_list: RedisCache.set(KEY_ADMINISTRATIVE_DIVISION, msgpack.packb(ad_list)) return ad_list
def statistic_member_info(race_cid: str, province_code, yesterday): """ 会员信息导入,只根据race_mapping表中race_cid删选,没有过滤address为空的 :param race_cid: :return: """ city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': province_code}) district_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': { '$in': city_code_list }}) # race_member_match = {"race_cid": race_cid, 'record_flag': 1, 'auth_address.province': {'$ne': None}, # 'auth_address.city': {"$in": city_name_list}, # 'auth_address.district': {"$in": district_name_list}, # 'created_dt': {'$lte': datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)}} race_member_match = { "race_cid": race_cid, 'record_flag': 1, 'auth_address.province': { '$ne': None }, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": district_name_list }, 'created_dt': { '$lte': yesterday } } match = {"race_cid": race_cid, 'record_flag': 1} # 当前活动参与会员 memberCidList = RaceMapping.sync_distinct("member_cid", race_member_match) memberCidList.sort() # 当前活动关卡cid checkPointList = RaceGameCheckPoint.sync_aggregate([MatchStage(match)]) checkPointMap = {} lastCheckPoint = None maxCheckPointIndex = 0 for check_point in checkPointList: checkPointMap[check_point.cid] = check_point.alias if check_point.index > maxCheckPointIndex: maxCheckPointIndex = check_point.index lastCheckPoint = check_point.cid checkPointCidList = list(checkPointMap.keys()) # print("该活动下的会员", len(memberCidList), memberCidList) # print("该活动下的会员数", len(set(memberCidList))) # print("该活动下的关卡", len(checkPointCidList), checkPointCidList) temp_list = splitList(memberCidList, 2000) threads = [] thread_num = 1 for my_list in temp_list: # print(my_list) t = MyThread(get_member_info, args=(thread_num, my_list, race_cid, checkPointCidList, lastCheckPoint, checkPointMap)) threads.append(t) t.start() thread_num += 1 results = [] for each_thread in threads: each_thread.join() result = each_thread.get_result() if len(result) > 0: results.extend(result) return results
def wash_data(): """ 清洗统计数据 :return: """ race_map = {} prov_map = {} cursor = ReportRacePeopleStatistics.sync_find() while True: try: stat = cursor.next() if not (stat.province and stat.city and stat.district): print('delete') stat.sync_delete() continue if race_map.get(stat.race_cid): race = race_map.get(stat.race_cid) else: race = Race.sync_find_one({'cid': stat.race_cid}) race_map[stat.race_cid] = race prov = prov_map.get(stat.province) if not prov: prov = AdministrativeDivision.sync_find_one({ 'title': { '$regex': stat.province }, 'parent_code': None }) prov_map[stat.province] = prov if prov.code != race.province_code: print('xxxxx') stat.sync_delete() except StopIteration: break except Exception as e: pdb.set_trace() cursor = RaceMapping.sync_find() while True: try: _p = cursor.next() if not _p.auth_address or not _p.auth_address.get('district'): member = Member.sync_find_one({'cid': _p.member_cid}) if not member or not member.auth_address or not member.auth_address.get( 'district'): _p.sync_delete() continue _p.auth_address = member.auth_address _p.sync_save() print('update mapping') if race_map.get(_p.race_cid): race = race_map.get(_p.race_cid) else: race = Race.sync_find_one({'cid': _p.race_cid}) race_map[_p.race_cid] = race province = _p.auth_address.get('province') if province: prov = prov_map.get(province) if not prov: prov = AdministrativeDivision.sync_find_one({ 'title': { '$regex': province }, 'parent_code': None }) prov_map[province] = prov.code if prov.code != race.province_code: print(' mapping delete ') _p.sync_delete() except StopIteration: break except Exception as e: print(e) raise e
def generate_race_middle(race_cid: str, start): """ 生成中间表 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") prov_match = MatchStage({'province': {'$ne': None}}) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct('code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct('title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one({'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct('code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct('title', {'parent_code': race.province_code}) prov_match = MatchStage({'province': prov.title}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct('title', {'parent_code': city_code}) lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') has_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list}, 'member_cid': {'$ne': None}, 'town': {'$ne': None}} no_town_match = {'race_cid': race_cid, 'city': {'$in': city_name_list}, 'district': {'$in': dist_list}, 'member_cid': {'$ne': None}, 'town': {'$eq': None}} if start != "total": start_time = transform_time_format(start) end_time = transform_time_format(start + datetime.timedelta(days=1)) has_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time} no_town_match['daily_code'] = {'$gte': start_time, '$lt': end_time} cursor = MemberStatisticInfo.sync_aggregate( [ MatchStage(has_town_match), lookup_stage, prov_match, MatchStage({'member_list': {'$ne': []}}), GroupStage( {"daily_code": "$daily_code", 'district': '$district', 'city': '$city', 'town': '$town'}, province={"$first": "$province"}, town={"$last": "$town"}, status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'}, enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"}, amount_sum={'$sum': "$grant_red_packet_amount"}, receive_count_sum={'$sum': "$draw_red_packet_count"}, receive_amount_sum={'$sum': "$draw_red_packet_amount"}, answer_times={'$sum': '$answer_times'}, true_answer_times={'$sum': '$true_answer_times'}, sum={'$sum': 1}), ] ) no_town_cursor = MemberStatisticInfo.sync_aggregate( [ MatchStage(no_town_match), lookup_stage, prov_match, MatchStage({'member_list': {'$ne': []}}), GroupStage( {"daily_code": "$daily_code", 'district': '$district', 'city': '$city'}, province={"$first": "$province"}, town={"$last": "$town"}, status_list={'$push': '$is_new_user'}, pass_status_list={'$push': '$is_final_passed'}, enter_times={'$sum': "$enter_times"}, count_sum={'$sum': "$grant_red_packet_count"}, amount_sum={'$sum': "$grant_red_packet_amount"}, receive_count_sum={'$sum': "$draw_red_packet_count"}, receive_amount_sum={'$sum': "$draw_red_packet_amount"}, answer_times={'$sum': '$answer_times'}, true_answer_times={'$sum': '$true_answer_times'}, sum={'$sum': 1}), ] ) while True: try: race_member_info = cursor.next() daily_code = race_member_info.id.get('daily_code') status_list = race_member_info.status_list pass_status_list = race_member_info.pass_status_list district = race_member_info.id.get('district') city = race_member_info.id.get('city') if district and city: race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid) race_enter_info.province = race_member_info.province race_enter_info.city = city race_enter_info.district = district race_enter_info.town = race_member_info.town race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None race_enter_info.increase_enter_count = status_list.count(1) # 每日新增 race_enter_info.enter_count = race_member_info.sum # 每日参与 race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数 race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数 race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量 race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额 race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量 race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额 race_enter_info.correct_percent = round( race_member_info.true_answer_times / race_member_info.answer_times, 2) if race_member_info.answer_times != 0 else 0 race_enter_info.answer_times = race_member_info.answer_times # 总答题数量 race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量 race_enter_info.sync_save() else: continue except StopIteration: break except Exception as e: raise e while True: try: race_member_info = no_town_cursor.next() daily_code = race_member_info.id.get('daily_code') status_list = race_member_info.status_list pass_status_list = race_member_info.pass_status_list district = race_member_info.id.get('district') city = race_member_info.id.get('city') if district and city: race_enter_info = RaceMemberEnterInfoStatistic(daily_code=daily_code, race_cid=race_cid) race_enter_info.province = race_member_info.province race_enter_info.city = city race_enter_info.district = district race_enter_info.company_cid = race_member_info.company_cid if race_member_info.company_cid else None race_enter_info.company_name = race_member_info.company_name if race_member_info.company_name else None race_enter_info.increase_enter_count = status_list.count(1) # 每日新增 race_enter_info.enter_count = race_member_info.sum # 每日参与 race_enter_info.pass_count = pass_status_list.count(1) # 每日通关人数 race_enter_info.enter_times = race_member_info.enter_times # 每日参与次数 race_enter_info.grant_red_packet_count = race_member_info.count_sum # 红包发放数量 race_enter_info.grant_red_packet_amount = race_member_info.amount_sum # 红包发放金额 race_enter_info.draw_red_packet_count = race_member_info.receive_count_sum # 红包领取数量 race_enter_info.draw_red_packet_amount = race_member_info.receive_amount_sum # 红包领取金额 race_enter_info.correct_percent = round( race_member_info.true_answer_times / race_member_info.answer_times, 2) if race_member_info.answer_times != 0 else 0 # 正确率 race_enter_info.answer_times = race_member_info.answer_times # 总答题数量 race_enter_info.true_answer_times = race_member_info.true_answer_times # 总答对题目数量 race_enter_info.sync_save() else: continue except StopIteration: break except Exception as e: raise e
async def deal_race(race, province, city): """ :param race: :param province: :param city :return: """ race_dict = { 'race_cid': race.cid, 'status': 1, 'title_img_url': '', 'area': '', 'is_local': True } if race.start_datetime > datetime.now(): # 未开始 race_dict['status'] = 0 if race.end_datetime < datetime.now(): # 已结束 race_dict['status'] = 2 if (race.city_code and race.city_code != city.code) or ( race.province_code != province.code): race_dict['is_local'] = False if race.image_list: upload_file = race.image_list[0] title_img_url = '%s://%s%s%s%s' % (SERVER_PROTOCOL, SERVER_HOST, STATIC_URL_PREFIX, 'files/', upload_file.title) race_dict['title_img_url'] = title_img_url if race.province_list: race_dict['area'] = race.province_list[0].title if race.city_list: race_dict['area'] += '-' + race.city_list[0].title # 去除脏数据 if not race.city_code: # 省级活动 prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_list = AdministrativeDivision.sync_find({ 'parent_code': prov.code }).to_list(None) city_title_list = [city.title for city in city_list] city_code_list = [city.code for city in city_list] district_title_list = AdministrativeDivision.sync_distinct( "title", {'parent_code': { '$in': city_code_list }}) else: city = AdministrativeDivision.sync_find_one( {'code': race.city_code}) city_title_list = [city.title] district_title_list = AdministrativeDivision.sync_distinct( "title", {'parent_code': city.code}) start_daily_code = format(race.start_datetime, '%Y%m%d') if race.cid != "DF1EDC30F120AEE93351A005DC97B5C1": cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid) data = RedisCache.get(cache_key) if data: race_dict['people_count'] = int(data) else: cursor = RaceMemberEnterInfoStatistic.aggregate([ MatchStage({ 'race_cid': race.cid, 'daily_code': { '$gte': start_daily_code }, 'city': { '$in': city_title_list }, 'district': { '$in': district_title_list } }), GroupStage('race_cid', sum={'$sum': '$increase_enter_count'}) ]) count = 0 while await cursor.fetch_next: try: info = cursor.next_object() count = info.sum except StopIteration: break race_dict['people_count'] = count RedisCache.set(cache_key, count, 60 * 60) else: # 扬州显示次数 cache_key = '{race_cid}-PEOPLE_COUNT'.format(race_cid=race.cid) data = RedisCache.get(cache_key) if data: race_dict['people_count'] = int(data) else: cursor = RaceMemberEnterInfoStatistic.aggregate([ MatchStage({ 'race_cid': race.cid, 'daily_code': { '$gte': start_daily_code }, 'city': { '$in': city_title_list }, 'district': { '$in': district_title_list } }), GroupStage('race_cid', sum={'$sum': '$enter_times'}) ]) count = 0 while await cursor.fetch_next: try: info = cursor.next_object() count = info.sum except StopIteration: break race_dict['people_count'] = count RedisCache.set(cache_key, count, 60 * 60) race_dict['show_play_quantity'] = race.play_quantity_enabled race_dict['title'] = race.title return race_dict
def write_sheet_enter_data(book, race_cid, sheet_name, pre_match={}, count_type='$people_num'): """ 总共参与人数/人次 :param book: :param sheet_name: :param race_cid :param pre_match: :param count_type: :return: """ match = {'race_cid': race_cid} if pre_match: match.update(pre_match) race = Race.sync_get_by_cid(race_cid) match.update(get_region_match(race)) sheet = book.add_worksheet(sheet_name) group_stage = GroupStage( { 'daily_code': '$daily_code', 'district': '$district' }, sum={'$sum': count_type}, city={'$first': '$city'}) daily_list = ReportRacePeopleStatistics.sync_distinct('daily_code', match) daily_list.sort() sheet.write_string(0, 0, '城市') sheet.write_string(0, 1, '区县') daily_map = {} for index, daily_code in enumerate(daily_list): sheet.write_string(0, index + 2, daily_code) daily_map[daily_code] = index + 2 district_list = ReportRacePeopleStatistics.sync_distinct('district', match) district_map = {} for index, district in enumerate(district_list): sheet.write_string(index + 1, 1, district if district else '其他') district_map[district if district else '其他'] = index + 1 cursor = ReportRacePeopleStatistics.sync_aggregate( [MatchStage(match), group_stage]) county_map = dict() v_list = list() _max_row = 0 while True: try: stat = cursor.next() city = stat.city if stat.city else '其他' district = stat.id.get('district') daily_code = stat.id.get('daily_code') _row = district_map.get(district if district else '其他') sheet.write_string(_row, 0, city) ad_city = AdministrativeDivision.sync_find_one({'title': city}) _county = AdministrativeDivision.sync_distinct( 'title', {'parent_code': ad_city.code}) for _c in _county: county_map[_c] = city sheet.write_number(_row, daily_map.get(daily_code), stat.sum) if _row > _max_row: _max_row = _row v_list.append(stat.sum) except StopIteration: break except Exception as e: raise e for k, v in county_map.items(): if k not in district_map: _max_row += 1 sheet.write_string(_max_row, 0, v) sheet.write_string(_max_row, 1, k) sheet.write_string(_max_row + 1, 0, '总和') sheet.write_number(_max_row + 1, 1, sum(v_list))
def export_race_enter_position(race_cid: str, title, n): """ 导出活动下面参与情况 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") current = datetime.datetime.now() export_time_list = [ (current + datetime.timedelta(days=-n)).replace(hour=0, minute=0, second=0) for n in (range(1, n)) ] export_time_list.sort() export_time = [ transform_time_format(export_dt) for export_dt in export_time_list ] export_time.sort() workbook = xlsxwriter.Workbook("{title}{today}.xlsx".format(title=title, today=today)) excel_name = "{title}{today}.xlsx".format(title=title, today=today) head_list = [ "新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额" ] sheet = workbook.add_worksheet("{title}一周数据".format(title=title)) data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei' }) sheet.merge_range(0, 0, 1, 0, "城市", data_center_format) sheet.merge_range(0, 1, 1, 1, "区县", data_center_format) sheet.write_string(1, 2, "累计人数") sheet.write_string(1, 3, "累计参与次数") sheet.write_string(1, 4, "累计红包发放总数") # excel填充一周日期 for i in range(1, n): sheet.merge_range(0, 7 * (i - 1) + 5 + i - 1, 0, 7 * (i - 1) + 5 + i - 1 + 7, export_time[i - 1], data_center_format) for head in range(7 * (i - 1) + 5 + i - 1, 7 * (i - 1) + 5 + i - 1 + 7 + 1): index = head - 8 * (i - 1) - 5 sheet.write_string(1, head, head_list[index]) prov_match = MatchStage({'province': {'$ne': None}}) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: prov = AdministrativeDivision.sync_find_one( {'code': race.province_code}) city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) prov_match = MatchStage({'province': prov.title}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)]) match = { "race_cid": race_cid, 'record_flag': 1, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list } } address_list = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(match), prov_match, ProjectStage(**{ 'province': "$province", "city": "$city", "district": "$district" }), GroupStage({ 'district': '$district', 'city': '$city' }, sum={'$sum': 1}), address_sort ]) # 拿到该活动下的所有区县和城市对应字典表 dis_map = get_race_district_mapping(sheet, address_list) # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} quantity_time_dict = {} # 每日参与人数一周的数据 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} enter_quantity_time_dict = {} # 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} count_time_dict = {} # 一周通关的人数 {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}} pass_quantity_time_dict = {} # 一周红包发放数量和金额的字典 red_give_dict = {} red_give_amount_dict = {} # 一周红包领取数量和金额的字典 red_receive_dict = {} red_receive_amount_dict = {} base_match = { 'race_cid': race_cid, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list }, 'daily_code': { '$gte': export_time[0], '$lte': transform_time_format( (export_time_list[-1] + datetime.timedelta(days=1))) } } total_match = copy.deepcopy(base_match) del total_match['daily_code'] total_district_count_dict = {} total_district_times_dict = {} total_send_red_packet_dict = {} race_total_info_cursor = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(total_match), GroupStage( { 'city': '$city', 'district': '$district' }, count_sum={'$sum': '$increase_enter_count'}, enter_times_sum={'$sum': '$enter_times'}, grant_red_packet_count={'$sum': "$grant_red_packet_count"}, ) ]) while True: try: data = race_total_info_cursor.next() district = data.id.get('district') if district not in total_district_count_dict: total_district_count_dict[district] = data.count_sum if district not in total_district_times_dict: total_district_times_dict[district] = data.enter_times_sum if district not in total_send_red_packet_dict: total_send_red_packet_dict[ district] = data.grant_red_packet_count except StopIteration: break except Exception as e: raise e # 每日参与人数,总计 for district, data in total_district_count_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 2, data) # 每日参与次数,总计 for district, data in total_district_times_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 3, data) # 每日累计发放红包数量 for district, data in total_send_red_packet_dict.items(): _col = dis_map.get(district) sheet.write_number(_col, 4, data) race_member_info_list = RaceMemberEnterInfoStatistic.sync_aggregate([ MatchStage(base_match), GroupStage( { "daily_code": "$daily_code", 'district': '$district', 'city': '$city' }, increase_quantity_sum={'$sum': '$increase_enter_count'}, enter_count_sum={'$sum': '$enter_count'}, pass_count_sum={'$sum': '$pass_count'}, enter_times_sum={'$sum': '$enter_times'}, grant_red_packet_count_sum={'$sum': '$grant_red_packet_count'}, grant_red_packet_amount_sum={'$sum': '$grant_red_packet_amount'}, draw_red_packet_count_sum={'$sum': '$draw_red_packet_count'}, draw_red_packet_amount_sum={'$sum': '$draw_red_packet_amount'}, ), address_sort ]) for race_member_info in race_member_info_list: daily = race_member_info.id.get('daily_code') city = race_member_info.id.get('city') district = race_member_info.id.get('district') if city and district: if daily not in quantity_time_dict: # 每日新增参与人数 quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.increase_quantity_sum } else: quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.increase_quantity_sum }) if daily not in enter_quantity_time_dict: # 每日参与人数 enter_quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.enter_count_sum } else: enter_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.enter_count_sum }) if daily not in count_time_dict: # 每日参与次数 count_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.enter_times_sum } else: count_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.enter_times_sum }) if daily not in pass_quantity_time_dict: # 每日通关人数 pass_quantity_time_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.pass_count_sum } else: pass_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.pass_count_sum }) if daily not in red_give_dict: # 每日红包发放数量 red_give_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_count_sum } else: red_give_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_count_sum }) if daily not in red_give_amount_dict: # 每日红包发放金额 red_give_amount_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_amount_sum } else: red_give_amount_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.grant_red_packet_amount_sum }) if daily not in red_receive_dict: # 每日红包领取数量 red_receive_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_count_sum } else: red_receive_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_count_sum }) if daily not in red_receive_amount_dict: # 每日红包领取金额 red_receive_amount_dict[daily] = { "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_amount_sum } else: red_receive_amount_dict[daily].update({ "{city}-{district}".format(city=city, district=district): race_member_info.draw_red_packet_amount_sum }) else: continue # 每日新增人数在excel的位置 quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, n)] # 每日参与人数在excel的位置 quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, n)] # 每日参与次数在excel的位置 count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, n)] # 每日通关次数在excel的位置 pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, n)] # 红包发放数量在excel的位置 red_give_position_list = [8 + 8 * (i - 1) for i in range(1, n)] # 红包发放金额在excel的位置 red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, n)] # 红包领取数量在excel的位置 red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, n)] # 红包领取金额在excel的位置 red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, n)] # 填充每日新增人数 write_excel_data(sheet, quantity_time_dict, dis_map, export_time, quantity_increase_position_list, save=None) # excel 填充每日参与人数 write_excel_data(sheet, enter_quantity_time_dict, dis_map, export_time, quantity_enter_position_list, save=None) # excel 填充每日参与次数 write_excel_data(sheet, count_time_dict, dis_map, export_time, count_enter_position_list, save=None) # excel 填充每日通关人数 write_excel_data(sheet, pass_quantity_time_dict, dis_map, export_time, pass_enter_position_list, save=None) # excel 填充每日发放个数 write_excel_data(sheet, red_give_dict, dis_map, export_time, red_give_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_give_amount_dict, dis_map, export_time, red_give_amount_list, save=2) # excel 填充每日领取个数 write_excel_data(sheet, red_receive_dict, dis_map, export_time, red_receive_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_receive_amount_dict, dis_map, export_time, red_receive_amount_list, save=2) workbook.close() return excel_name
worksheet.write_string(row, 2, SEX_DICT.get(member.sex) if member else '-') worksheet.write_string( row, 3, TYPE_AGE_GROUP_DICT.get(member.age_group) if member else '-') worksheet.write_string( row, 4, TYPE_EDUCATION_DICT.get(member.education) if member else '-') prov = city = '-' if member.province_code and member.city_code: if member.province_code in post_code_map: prov = post_code_map[member.province_code] else: prov = AdministrativeDivision.sync_find_one({ 'code': member.province_code }).title if member.city_code in post_code_map: city = post_code_map[member.city_code] else: city = AdministrativeDivision.sync_find_one({ 'code': member.city_code }).title post_code_map[member.province_code] = prov post_code_map[member.city_code] = city worksheet.write_string(row, 5, prov) worksheet.write_string(row, 6, city)