def deal_with_history(): fight_history_list = get_fight_history_models() count = 1 for fight_history in fight_history_list: if fight_history: member = Member.sync_get_by_cid(fight_history.member_cid) if member: task_robot_analysis_reference_statistics.delay( fight_history, member) print(count, member.nick_name, fight_history.fight_datetime) if count % 20000 == 0: print('sleeping 30s...') time.sleep(45) count += 1 race_history_list = get_race_history_models() for race_history in race_history_list: if race_history: member = Member.sync_get_by_cid(race_history.member_cid) if member: task_robot_analysis_reference_statistics.delay(race_history) print(count, member.nick_name, race_history.fight_datetime) if count % 20000 == 0: print('sleeping 30s...') time.sleep(45) count += 1
def deal_with_history(): fight_history_list = get_fight_history_models() count = 1 for fight_history in fight_history_list: if fight_history: fight_datetime = fight_history.fight_datetime if fight_datetime: docking_code = _get_docking_code(fight_datetime) member = Member.sync_get_by_cid(fight_history.member_cid) if member: ds = DockingStatistics.sync_find_one(dict(docking_code=docking_code, member_cid=member.cid)) if not ds: ds = DockingStatistics(docking_code=docking_code, member_cid=member.cid) ds.sync_save() docking_statistics(fight_history, member, docking_code) print(count, fight_history.cid) count += 1 race_history_list = get_race_history_models() for race_history in race_history_list: if race_history: race_datetime = race_history.fight_datetime if race_datetime: docking_code = _get_docking_code(race_datetime) member = Member.sync_get_by_cid(race_history.member_cid) if member: ds = DockingStatistics.sync_find_one(dict(docking_code=docking_code, member_cid=member.cid)) if not ds: ds = DockingStatistics(docking_code=docking_code, member_cid=member.cid) ds.sync_save() docking_statistics(race_history, member, docking_code) print(count, race_history.cid) count += 1
def deal_with_history(): count = 1 fight_history_list = get_fight_history_models() for index, fight_history in enumerate(fight_history_list): if fight_history: member = Member.sync_get_by_cid(fight_history.member_cid) if member: RedisCache.delete('%s_%s%s' % (KEY_PREFIX_TASK_ACCURATE_STATISTICS, member.cid, fight_history.cid)) start_accurate_statistics.delay(fight_history, member) print(count, member.nick_name, fight_history.fight_datetime) if count % 20000 == 0: print('sleeping 45s...') time.sleep(45) count += 1 race_history_list = get_race_history_models() for index, race_history in enumerate(race_history_list): if race_history: member = Member.sync_get_by_cid(race_history.member_cid) if member: RedisCache.delete('%s_%s%s' % (KEY_PREFIX_TASK_ACCURATE_STATISTICS, member.cid, race_history.cid)) start_accurate_statistics.delay(race_history, member) print(count, member.nick_name, race_history.fight_datetime) if count % 20000 == 0: print('sleeping 45s...') time.sleep(45) count += 1
def do_merge_city_stat_member_quantity(province_dict: dict, choice_time, city_code_list=None): """ 合并省份统计信息 :param province_dict: :param city_code_list :param choice_time :return: """ query_dict = {} if province_dict: query_dict['province_code'] = {'$in': [code for code in province_dict.keys()]} if city_code_list: query_dict['city_code'] = {'$in': city_code_list} else: query_dict['city_code'] = {'$ne': None} if not choice_time: # 取前一天凌晨12点之前的数据 yesterday = get_yesterday() query_dict['updated_dt'] = {'$lt': yesterday} else: # 当天下一天凌晨的时候 max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999) query_dict['updated_dt'] = {'$gte': choice_time, '$lt': max_choice_time} query_dict['status'] = STATUS_USER_ACTIVE match_stage = MatchStage(query_dict) group_stage = GroupStage('city_code', quantity={'$sum': 1}, province_code={'$first': '$province_code'}) sort_stage = SortStage([('quantity', DESC)]) p_lookup_stage = LookupStage(AdministrativeDivision, 'province_code', 'post_code', 'province_list') c_lookup_stage = LookupStage(AdministrativeDivision, '_id', 'post_code', 'city_list') city_cursor = Member.sync_aggregate([match_stage, group_stage, sort_stage, p_lookup_stage, c_lookup_stage]) t_province_dict = {} t_province_dict = get_merge_city_data(city_cursor, province_dict, t_province_dict) if t_province_dict: province_dict.update(t_province_dict)
def inscription(request): if request.method == 'POST': try: member = Member.objects.get(email=request.POST['email']) except: member = Member() member_form = MemberForm(request.POST, instance=member) inscription_form = InscriptionForm(request.POST) if any(insc.is_current() for insc in Inscription.objects.filter(member=member)): info(request, 'Already inscripted on the current session!') elif member_form.is_valid() and inscription_form.is_valid(): member = member_form.save() inscription = inscription_form.save(commit=False) inscription.member = member inscription.session = Inscription.current_session() inscription.save() success(request, 'Inscription created!') else: error(request, 'Error when creating inscription!') else: member_form = MemberForm() inscription_form = InscriptionForm() return render(request, 'inscription/index.html', { 'member_form': member_form, 'inscription_form': inscription_form, })
def query(): amount = 0 miss_members = set() with open('./rdp.txt', 'r') as f: for line in f.readlines(): rid = line.replace('\n', '') box = RedPacketBox.sync_find_one({'redpkt_rid': rid}) if not box: try: race_cid, member_cid, redpacket_cid = line.split('-') box = RedPacketBox.sync_get_by_cid(redpacket_cid) if not box: print('miss box', rid) member = Member.sync_get_by_cid(member_cid) if member: miss_members.add(member.nick_name) else: print('miss member', member_cid) continue except ValueError: print('miss box', rid) continue if box.draw_status == STATUS_REDPACKET_NOT_AWARDED: box.draw_status = 0 box.sync_save() print('not draw', rid) else: print('success ', rid) amount += box.award_amount print(amount) print('-----') print(len(miss_members), miss_members)
async def do_diamond_reward(member: Member, source_diamond, diamond=None): if not isinstance(member, Member): raise ValueError('Parameter member_cid must be a type of Member.') if source_diamond not in SOURCE_MEMBER_DIAMOND_LIST: raise ValueError('Parameter source_diamond must in %s.' % str(SOURCE_MEMBER_DIAMOND_LIST)) if diamond and not isinstance(diamond, int): raise TypeError('Parameter diamond must be type of int or long.') m_diamond = diamond if diamond is None: diamond_reward = await GameDiamondReward.find_one( dict(source=source_diamond)) if diamond_reward: m_diamond = diamond_reward.quantity if not m_diamond: m_diamond = 0 member.diamond += m_diamond member.updated_dt = datetime.datetime.now() diamond_detail = MemberDiamondDetail(member_cid=member.cid, diamond=m_diamond) diamond_detail.member_cid = member.cid diamond_detail.diamond = m_diamond diamond_detail.source = source_diamond diamond_detail.reward_datetime = datetime.datetime.now() diamond_detail.content = SOURCE_MEMBER_DIAMOND_DICT.get(source_diamond) await diamond_detail.save() await member.save() return True, m_diamond
def deal_with_member_property(): member_list = Member.sync_find( dict(record_flag=1), read_preference=ReadPreference.PRIMARY).batch_size(32) count = 1 for member in member_list: if member: city_code = complete_member_city_code(member.province_code, member.city_code) mps = MemberPropertyStatistics.sync_find_one( dict(daily_code=_get_daily_code(member.register_datetime), province_code=member.province_code, city_code=city_code, district_code=member.district_code, gender=member.sex, age_group=member.age_group, education=member.education, source=member.source), read_preference=ReadPreference.PRIMARY) if not mps: mps = MemberPropertyStatistics( daily_code=_get_daily_code(member.register_datetime), province_code=member.province_code, city_code=city_code, district_code=member.district_code, gender=member.sex, age_group=member.age_group, education=member.education, source=member.source) mps.sync_save() member_property_statistics.delay(member) print(count, member.nick_name, member.register_datetime) count += 1
async def save_inspiration(self, ctx, name, url): '''Stores an inspiration for later retrieval. ''' server = ctx.guild.name author = ctx.message.author.name member_id = ctx.message.author.id session = self.interface.database.Session() try: member = self.interface.find_member(session, member_id) except Exception as e: # Create member if they do not exist in our database member = Member(id=member_id, name=author, server=server) self.interface.database.add_object(session, member) try: inspo = Inspiration(name=name, server=server, url=url, member_id=member_id) self.interface.database.add_object(session, inspo) session.commit() await ctx.send(f'Image saved as {name}') except Exception as e: await ctx.send('Could not complete your command') print(e) finally: session.close()
async def attend(self, ctx, name: str): '''Allows a user to attend an upcoming event example: ?attend party ''' author = ctx.message.author.name avatar = ctx.message.author.avatar_url id = ctx.message.author.id try: count = self.session.query(Member).filter(Member.id == id).count() event = self.session.query(Event).filter(Event.name == name).first() # Verify This event exists if not event: await self.bot.say('This event does not exist') return # Create member if they do not exist in our database if count < 1: member = Member(id=id, name=author, avatar=avatar, level = 1, experience = 0) self.session.add(member) attending = Attendance(member_id=id, event_id=event.id) self.session.add(attending) self.session.commit() await self.bot.say('Member {} is now attending event {}'.format(author, name)) except Exception as e: await self.bot.say('Could not complete your command') self.logger.error(e)
def init_member_learning_day_statistics(history): """ :param history: :return: """ try: member = Member.sync_get_by_cid(history.member_cid) if not member: print('--- no member cid, history_cid=%s' % history.cid) return # START: 会员按学习日数据统计 learning_code = _sync_get_learning_code(history) mls = MemberLearningDayStatistics.sync_find_one(dict(learning_code=learning_code, member_cid=member.cid), read_preference=ReadPreference.PRIMARY) if not mls: # 创建会员学习日数据统计记录 mls = MemberLearningDayStatistics(learning_code=learning_code, member_cid=member.cid) mls.sync_save() # 添加任务 member_learning_day_statistics(history, member, learning_code) # END: 会员按学习日数据统计 except Exception: print(traceback.format_exc())
def do_statistics_member_quantity(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) stage_list = [] if city_code_list: stage_list.append(MatchStage({'city_code': {'$in': city_code_list}})) if not choice_time: # 取前一天凌晨12点之前的数据 yesterday_time = get_yesterday() time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}}) else: # 当天下一天凌晨的时候 max_choice_time = choice_time.replace(hour=23, minute=59, second=59, microsecond=999) time_match = MatchStage({'updated_dt': {'$gte': choice_time, '$lt': max_choice_time}}) stage_list.append(time_match) stage_list.append(MatchStage({'status': STATUS_USER_ACTIVE})) group_stage = GroupStage('province_code', quantity={'$sum': 1}) lookup_stage = LookupStage(AdministrativeDivision, '_id', 'post_code', 'ad_list') sort_stage = SortStage([('quantity', DESC)]) stage_list += [group_stage, lookup_stage, sort_stage] province_cursor = Member.sync_aggregate(stage_list) province_dict = {} while True: try: province_stat = province_cursor.next() if province_stat: province_code = province_stat.id if province_stat.id else '000000' quantity = province_stat.quantity title = 'undefined' ad_list = province_stat.ad_list if ad_list: ad: FacadeO = ad_list[0] if ad: title = ad.title.replace('省', '').replace('市', '') province_dict[province_code] = { 'code': province_code, 'title': title, 'data': quantity } except StopIteration: break # 合并城市统计信息 do_merge_city_stat_member_quantity(province_dict, choice_time, city_code_list) data = [v for v in province_dict.values()] if not data: early_warning_empty("start_statistics_member_quantity", cache_key, city_code_list, '学习近况中人数数据为空,请检查!') RedisCache.set(cache_key, msgpack.packb(data))
def deal_with_history(): count = 1 fight_history_list = get_fight_history_models() for index, fight_history in enumerate(fight_history_list): if fight_history: member = Member.sync_get_by_cid(fight_history.member_cid) if member: do_calculate_member_stars_award(member, fight_history) print(count, member.nick_name, fight_history.fight_datetime) count += 1
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 quiz(request, quiz_pk=None, quiz_title=None, member_pk=None, member_email=None): if quiz_pk: quiz = get_object_or_404(Quiz, pk=quiz_pk) else: quiz = get_object_or_404(Quiz, title=quiz_title) if (quiz.start and quiz.start > timezone.now()) or ( quiz.end and quiz.end < timezone.now()): error(request, 'Oops! Quiz is closed.') return render(request, "quiz/inactive.html", {"quiz": quiz}) if member_pk: member = get_object_or_404(Member, pk=member_pk) elif member_email: member = get_object_or_404(Member, email=member_email) elif request.method == 'POST': try: member = Member.objects.get(email=request.POST['email']) except: return HttpResponseForbidden() else: member = Member() score = 0 try: instance = Submission.objects.get(quiz=quiz, member=member) if request.method == "GET": score = instance.score() submission_is_new = False except: instance = Submission(quiz=quiz, member=member) submission_is_new = True if request.method == 'POST': candidat_form = CandidatForm(request.POST, instance=member) form = QuizForm(request.POST, instance=instance, is_new=submission_is_new) if candidat_form.is_valid() and form.is_valid(): candidat_form.save() instance.save() form.save() score = instance.score() else: candidat_form = CandidatForm(instance=member) form = QuizForm(instance=instance, is_new=submission_is_new) return render(request, 'quiz/quiz.html', { 'candidat_form': candidat_form, 'form': form, 'score': score, })
def start_dashboard_report_statistics_without_delay(history): try: member = Member.sync_get_by_cid(history.member_cid) if not member: print('--- no member cid, history_cid=%s' % history.cid) return # START 自然日会员维度统计 member_daily_dimension_statistics(history, member) # END 自然日会员维度统计 except Exception: print(traceback.format_exc())
def add_notice(): # msg = """尊敬的用户您好,由于小程序商户到期重审,红包领取功能暂时无法使用,具体恢复时间另行通知。审核期间您可正常使用小程序所有功能,后台会照常发放红包,期间获得的所有红包将在审核通过后全部在“消息通知”里面直接领取。""" msg = """尊敬的用户您好,红包领取功能已经恢复正常,之前未领取的红包您可以前往“消息通知”里面直接领取。谢谢!""" cursor = Member.sync_find( {}, read_preference=ReadPreference.PRIMARY).batch_size(2048) for index, member in enumerate(cursor): print('has notice', index) notice = MemberNotice() notice.member_cid = member.cid notice.category = CATEGORY_NOTICE_SYSTEM notice.content = msg notice.sync_save()
def export_member_game_history(cond: dict): """ 获取该用户的对战历史 :param cond: :return: """ member = Member.sync_find_one(cond) if not member: return workbook = xlsxwriter.Workbook('%s的游戏历史明细.xlsx' % member.nick_name) worksheet = workbook.add_worksheet('game_history') history_list = MemberGameHistory.sync_find({ 'member_cid': member.cid, 'created_dt': { '$gte': datetime.now().replace(2019, 1, 1, 0, 0, 0, 0) } }).sort('created_dt').to_list(None) cols = ['时间', '所处段位', '状态', '钻石增减'] for col_index, col_name in enumerate(cols): worksheet.write(0, col_index + 1, col_name) print(history_list) for index, his in enumerate(history_list): cols = [ datetime2str(his.created_dt), TYPE_DAN_GRADE_DICT.get(his.dan_grade, str(his.dan_grade)), STATUS_RESULT_CHECK_POINT_DICT.get(his.status), str(his.result) ] for col_index, col_name in enumerate(cols): worksheet.write(index + 1, col_index + 1, col_name) worksheet2 = workbook.add_worksheet('diamond_detail') details = MemberDiamondDetail.sync_find({ 'member_cid': member.cid }).sort('created_dt').to_list(None) cols = ['时间', '奖励来源', '奖励类型', '钻石增减'] for col_index, col_name in enumerate(cols): worksheet2.write(0, col_index + 1, col_name) for index, detl in enumerate(details): cols = [ datetime2str(detl.reward_datetime), SOURCE_MEMBER_DIAMOND_DICT.get(detl.source), detl.content, detl.diamond ] for col_index, col_name in enumerate(cols): worksheet2.write(index + 1, col_index + 1, col_name) workbook.close()
def insert_from_member_amount_map(member_amount_map: dict, daily_code, race_cid): """ :param member_amount_map: :param daily_code: :return: """ if not member_amount_map: return [] ret_list = [] for member_cid, value in member_amount_map.items(): temp_member = Member.sync_get_by_cid(member_cid) if not temp_member: print("RedPacket未找到对应member_cid:%s" % member_cid) continue info = MemberStatisticInfo() info.daily_code = daily_code info.race_cid = race_cid # 会员信息 info.member_cid = member_cid info.nick_name = temp_member.nick_name info.open_id = temp_member.open_id info.mobile = temp_member.mobile info.first_time_login = temp_member.created_dt # race_mapping相关信息,地理位置 has_race_mapping = get_race_mapping_info(info) if not has_race_mapping: print("RedPacket未找到对应race_mapping,race_cid:%s,member_cid:%s" % (info.race_cid, info.member_cid)) continue # 是否为当日新用户 is_new_user(info) # 最后一关 is_final_pass(info) info.grant_red_packet_amount = value.get('grant_amount') info.grant_red_packet_count = value.get('grant_count') info.draw_red_packet_amount = value.get('draw_amount') info.draw_red_packet_count = value.get('draw_count') print("Success: member_cid:%s is_final_Pass:%s is_new:%s" % (info.member_cid, info.is_final_passed, info.is_new_user)) ret_list.append(info) return ret_list
def get_no_history_member(): """ 获取没有历史记录的用户 :return: """ data = Member.sync_aggregate([ MatchStage({'nick_name': '游客'}), LookupStage(MemberCheckPointHistory, 'cid', 'member_cid', 'history_list'), MatchStage({'history_list': []}), GroupStage('cid') ]).to_list(None) with open('./no-history-members.json', 'w', encoding='utf-8') as f: dist_list = json.dumps(list([d.id for d in data]), ensure_ascii=False) f.write(dist_list)
def start_accurate_statistics(self, history_model, member: Member = None): """ 会员游戏数据精确统计 :param self: 任务对象 :param history_model: 游戏历史 :param member: 会员,默认为None :return: """ result = {'code': 0} if allowed_process(): try: if not isinstance(history_model, (MemberGameHistory, MemberCheckPointHistory)): raise ValueError('"history_model" must be a instance of MemberGameHistory or MemberCheckPointHistory.') if member: if not isinstance(member, Member): raise ValueError('"member" must be a instance of member.') else: member = Member.sync_get_by_cid(history_model.member_cid) if member: stat_type = 'FIGHT' if isinstance(history_model, MemberCheckPointHistory): stat_type = 'RACE' logger.info( 'START(%s): Accurate Statistics, type=%s, history_cid=%s, member_code=%s' % ( self.request.id, stat_type, history_model.cid, member.code)) tag_key = '%s_%s%s' % (KEY_PREFIX_TASK_ACCURATE_STATISTICS, member.cid, history_model.cid) tag = RedisCache.get(tag_key) if not tag: RedisCache.set(tag_key, 1, 2 * 24 * 60 * 60) # 题目正确率 do_count_subject_accuracy(history_model, member) # 会员正确率 do_count_member_accuracy(history_model, member) result['code'] = 1 result['msg'] = 'Succeed!' else: logger.warning( 'END(%s): [Accurate Statistics] repeat executed, type=%s, history_cid=%s, member_code=%s ' % ( self.request.id, stat_type, history_model.cid, member.code)) except Exception: logger.error(traceback.format_exc()) finally: RedisCache.delete(KEY_ALLOW_PROCESS_ACCURATE_STATISTICS) return result
async def do_genete(): # _now = datetime.now() # bg_dt = _now.replace(year=2019, month=7, day=1, hour=12, minute=0, second=0) # ed_dt = _now.replace(year=2019, month=7, day=2, hour=14, minute=0, second=0) # member_list1 = await MemberGameHistory.distinct('member_cid', {'created_dt': {'$gt': bg_dt, '$lt': ed_dt}}) # member_list2 = await MemberCheckPointHistory.distinct('member_cid', {'created_dt': {'$gt': bg_dt, '$lt': ed_dt}}) # member_cids = set(member_list1).union(set(member_list2)) _now = datetime.now() index = 0 cursor = Member.find( { 'auth_address.province': '安徽省' }, read_preference=ReadPreference.PRIMARY).batch_size(128) while await cursor.fetch_next: try: member = cursor.next_object() award_value = 8000 if member.cid == '0D8A69937D498A20BDFC05881E5C4DB3': award_value = 12000 msg = '尊敬的用户您好,对于匹配超时而导致错误扣除钻石的问题,系统近期将返还%s钻石至您的账户,请注意查收。' % award_value member.diamond += award_value await member.save() notice = MemberNotice() notice.member_cid = member.cid notice.category = CATEGORY_NOTICE_SYSTEM notice.notice_datetime = _now notice.content = msg await notice.save() detail = MemberDiamondDetail() detail.member_cid = member.cid detail.diamond = award_value detail.source = SOURCE_MEMBER_DIAMOND_ADMIN_UPDATE detail.reward_datetime = _now detail.content = msg await detail.save() index += 1 print('has done %s, cid: %s, diamond: %s' % (index, member.cid, award_value)) except StopIteration: break
def init_member_star(): members = Member.sync_find({'status': STATUS_USER_ACTIVE}).batch_size(32) for index, member in enumerate(members): print(index + 1, member.nick_name) quantity = 0 if member.star_grade is None or member.star_grade == 0: if member.dan_grade: quantity = get_need_star(member.dan_grade) else: quantity = get_need_star(member.dan_grade) + member.star_grade stars_award_history = MemberStarsAwardHistory( member_cid=member.cid, dan_grade=member.dan_grade if member.dan_grade else 1, quantity=quantity, award_dt=datetime.datetime(2018, 10, 15, 0, 0, 0, 0)) stars_award_history.sync_save()
async def find_member_by_open_id(open_id): try: member = RedisCache.get(open_id) if member: member = Member().result_2_obj( from_msgpack(msgpack.unpackb(member, raw=False))) return member except Exception: pass member = await Member.find_one(dict(open_id=open_id, status=STATUS_USER_ACTIVE), read_preference=ReadPreference.PRIMARY) if member: RedisCache.set(open_id, msgpack.packb(to_msgpack(member)), 60 * 60 * 24) RedisCache.set('mid_%s' % str(member.oid), open_id, 60 * 60 * 24) return member
def register(request, event_id): event = get_object_or_404(Event, pk=event_id) if event.end and event.end < timezone.now(): return render(request, 'event/ended.html', {'event': event}) if request.method == 'POST': try: member = Member.objects.get(email=request.POST['email']) except: member = Member() member_form = MemberForm(request.POST, instance=member) inscription_form = InscriptionForm(request.POST) insc = [insc for insc in Inscription.objects.filter(member=member) if insc.is_current()] insc = insc[0] if len(insc) == 1 else None if not insc and member_form.is_valid() and inscription_form.is_valid(): member = member_form.save() insc = inscription_form.save(commit=False) insc.member = member insc.session = Inscription.current_session() insc.save() if insc: try: Register.objects.create(member=member, event=event) success(request, 'You have successfully registered') except: info(request, 'You has already registred!') else: error(request, 'Error when registering!') else: member_form = MemberForm() inscription_form = InscriptionForm() return render(request, 'event/register.html', { 'member_form': member_form, 'inscription_form': inscription_form, 'forms': [member_form, inscription_form], 'event': event, })
def start_dashboard_report_statistics_without_delay(history): """ :param history: :return: """ try: daily_code = _get_daily_code(history.fight_datetime) member = Member.sync_get_by_cid(history.member_cid) if not member: print('--- no member cid, history_cid=%s' % history.cid) mds = MemberDailyStatistics.sync_find_one( dict(daily_code=daily_code, member_cid=member.cid)) if not mds: mds = MemberDailyStatistics(daily_code=daily_code, member_cid=member.cid) mds.sync_save() if mds: member_daily_statistics(history, member, daily_code) except Exception: print(traceback.format_exc())
def deal_member_without_history(race_cid, daily_code): """ 处理报名活动但未答题的会员 :param race_cid: :param daily_code: :return: """ city_name_list, district_name_list = get_address(race_cid) checkpoint_cid, _checkpoint_map = get_checkpoint_cid_list(race_cid) start_date = str2datetime(daily_code, '%Y%m%d').replace(hour=0, minute=0, second=0, microsecond=0) end_date = start_date + datetime.timedelta(days=1) member_cid_with_history = MemberCheckPointHistory.sync_distinct( "member_cid", { 'check_point_cid': { '$in': checkpoint_cid }, 'created_dt': { '$gte': start_date, '$lt': end_date } }) race_member_match = MatchStage({ "race_cid": race_cid, 'member_cid': { '$nin': member_cid_with_history }, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": district_name_list }, 'created_dt': { '$gte': start_date, '$lt': end_date } }) member_group = GroupStage({'member_cid': '$member_cid'}, auth_address={'$first': '$auth_address'}, company_cid={'$first': '$company_cid'}, mobile={'$first': '$mobile'}, created_dt={'$first': '$created_dt'}) member_project = ProjectStage( **{ 'cid': '$cid', 'member_cid': '$_id.member_cid', 'auth_address': '$auth_address', 'mobile': '$mobile', 'created_dt': '$created_dt', 'company_cid': '$company_cid' }) member_without_history = RaceMapping.sync_aggregate( [race_member_match, member_group, member_project]).batch_size(4) member_amount_map = get_red_packet_info(race_cid, start_date, end_date) red_member_cid_list = member_amount_map.keys() member_no_history_list = [] count = 0 while True: try: stat = member_without_history.next() count += 1 if stat.member_cid in red_member_cid_list: continue # 根据member_cid查对应的member信息 temp_member = Member.sync_get_by_cid(stat.member_cid) if not temp_member: print("no history未找到对应member_cid:%s" % stat.member_cid) continue info_special = MemberStatisticInfo() info_special.is_special_user = 1 info_special.race_cid = race_cid info_special.member_cid = stat.member_cid info_special.daily_code = format(stat.created_dt, '%Y%m%d') info_special.nick_name = temp_member.nick_name info_special.open_id = temp_member.open_id if stat.mobile: info_special.mobile = stat.mobile else: info_special.mobile = temp_member.mobile info_special.first_time_login = temp_member.created_dt info_special.enter_times = 1 info_special.answer_times = 0 info_special.true_answer_times = 0 info_special.is_final_passed = 0 info_special.is_new_user = 1 info_special.grant_red_packet_amount = 0.0 info_special.grant_red_packet_count = 0 info_special.draw_red_packet_count = 0 info_special.draw_red_packet_amount = 0.0 info_special.province = stat.auth_address.get('province') info_special.city = stat.auth_address.get('city') info_special.district = stat.auth_address.get('district') info_special.town = stat.auth_address.get('town') info_special.check_point_cid = stat.race_check_point_cid if stat.race_check_point_cid: info_special.check_point_index = _checkpoint_map[ stat.race_check_point_cid] else: info_special.check_point_index = 1 if stat.company_cid: company = Company.sync_get_by_cid(stat.company_cid) info_special.company_cid = company.cid info_special.company_name = company.title member_no_history_list.append(info_special) # logger.info("Success without history: member_cid:%s is_final_Pass:%s" % (info_special.member_cid,info_special.is_final_passed)) if len(member_no_history_list) % 500 == 0: MemberStatisticInfo.sync_insert_many(member_no_history_list) member_no_history_list = [] except StopIteration: break except CursorNotFound: member_without_history = RaceMapping.sync_aggregate( [race_member_match, member_group, member_project]).skip(count).batch_size(4) except Exception as e: logger.info( "Fail: without history daily_code:%s,member_cid:%s,race_cid: %s" % (info_special.daily_code, info_special.member_cid, race_cid)) if len(member_no_history_list) > 0: MemberStatisticInfo.sync_insert_many(member_no_history_list)
def daily_member_statistic(race_cid, daily_code): """ 统计每日活动下的会员 :param race_cid: :param daily_code: :return: """ start_date = str2datetime(daily_code, '%Y%m%d').replace(hour=0, minute=0, second=0, microsecond=0) end_date = start_date + datetime.timedelta(days=1) check_point_cids, _checkpoint_map = get_checkpoint_cid_list(race_cid) # 当日答题记录 history_match = MatchStage({ 'created_dt': { '$gte': start_date, '$lt': end_date }, 'check_point_cid': { '$in': check_point_cids } }) history_group = GroupStage({'member_cid': '$member_cid'}, enter_times={'$sum': 1}, results_list={'$push': '$result'}) history_project = ProjectStage( **{ 'member_cid': '$_id.member_cid', 'results_list': '$results_list', 'enter_times': '$enter_times' }) # 红包信息 member_amount_map = get_red_packet_info(race_cid, start_date, end_date) # member_cid: amount check_point_history_cursor = MemberCheckPointHistory.sync_aggregate( [history_match, history_group, history_project], allowDiskUse=True).batch_size(4) member_statistic_list = [] count = 0 while True: try: cursor = check_point_history_cursor.next() count += 1 # 根据member_cid查对应的member信息 temp_member = Member.sync_get_by_cid(cursor.member_cid) if not temp_member: print("normal未找到对应member_cid:%s" % cursor.member_cid) continue info = MemberStatisticInfo() info.daily_code = daily_code info.race_cid = race_cid # 会员信息 info.member_cid = cursor.member_cid info.nick_name = temp_member.nick_name info.open_id = temp_member.open_id info.mobile = temp_member.mobile info.first_time_login = temp_member.created_dt info.enter_times = cursor.enter_times # 答题数量、正确数 for results in cursor.results_list: info.answer_times += len(results) info.true_answer_times += len([ result for result in results if result.get('true_answer') ]) # race_mapping相关信息,地理位置 has_race_mapping = get_race_mapping_info(info) if not has_race_mapping: print("normal未找到对应race_mapping,race_cid:%s,member_cid:%s" % (info.race_cid, info.member_cid)) continue # 是否为当日新用户 is_new_user(info) # 最后一关 is_final_pass(info) # 红包信息 try: value = member_amount_map.pop(info.member_cid) except KeyError: value = None if not value: value = { 'grant_count': 0, 'grant_amount': 0, 'draw_count': 0, 'draw_amount': 0 } info.grant_red_packet_amount = value.get('grant_amount') info.grant_red_packet_count = value.get('grant_count') info.draw_red_packet_amount = value.get('draw_amount') info.draw_red_packet_count = value.get('draw_count') # 保存记录 member_statistic_list.append(info) # logger.info("Success: member_cid:%s is_final_Pass:%s is_new:%s" % ( # info.member_cid, info.is_final_passed, info.is_new_user)) print("Success: member_cid:%s is_final_Pass:%s is_new:%s" % (info.member_cid, info.is_final_passed, info.is_new_user)) if len(member_statistic_list) % 500 == 0: MemberStatisticInfo.sync_insert_many(member_statistic_list) member_statistic_list = [] except StopIteration: break except CursorNotFound: check_point_history_cursor = MemberCheckPointHistory.sync_aggregate( [history_match, history_group, history_project]).skip(count).batch_size(4) except Exception as e: logger.error(e) logger.info("Fail: daily_code:%s,race_cid: %s" % (daily_code, race_cid)) member_statistic_list = [] member_statistic_list += insert_from_member_amount_map( member_amount_map, daily_code, race_cid) if len(member_statistic_list) > 0: MemberStatisticInfo.sync_insert_many(member_statistic_list)
step1: create QR scene = 6 step2: move online data(before 2019-03-29) to scene(6) step3: change statistics """ import traceback from datetime import datetime from db.models import Member date_value = datetime.now().replace(year=2019, month=3, day=29, hour=0, minute=0, second=0, microsecond=0) cursor = Member.sync_find({'source': 2, 'created_dt': {'$lte': date_value}}) while True: try: member = cursor.next() member.needless['old_source'] = member.source member.source = 6 member.sync_save() except StopIteration: print(traceback.format_exc()) break
async def post(self): """ :return: """ time = datetime.datetime.now() export_time = datetime2str(time, date_format='%Y-%m-%d %H:%M:%S') order = self.get_argument('order', '') chart_name = self.get_argument('chart_name', '') # 答题活跃度的数据 data_dict = self.get_argument('data', '') data_list = [] condition_title_list = [] # 没有筛选条件的总体活跃度 if data_dict: data_dict = json.loads(data_dict) condition_title_list = list(data_dict.keys()) data_list = list(data_dict.values()) # 有筛选条件的数据 if '总体活跃度' in condition_title_list: position = condition_title_list.index('总体活跃度') else: position = data_list.index(max(sum(data_list))) if len(data_list) > position: condition_title_list.remove(condition_title_list[position]) # 可管理的省份名称 manage_region_title_list = [] manage_region_code_list = self.current_user.manage_region_code_list if manage_region_code_list: for manage_region_code in manage_region_code_list: manage_region_province = await AdministrativeDivision.find_one( { 'code': manage_region_code, 'record_flag': 1, 'parent_code': None }) if manage_region_province: manage_region_title_list.append( manage_region_province.title) else: manage_region_city = await AdministrativeDivision.find_one( { 'code': manage_region_code, 'record_flag': 1 }) province = await manage_region_city.parent manage_region_title_list.append(province.title) try: output = BytesIO() workbook = Workbook(output, {'in_memory': True}) title_format = workbook.add_format({ 'font_size': 12, 'bold': '1', 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei', 'border': 1 }) data_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei', 'border': 1 }) data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei', 'border': 1 }) if order == "1" and chart_name: pass # 公民科学素质学习答题趋势统计 if order == '2' and chart_name: answer_tendency_date = self.get_argument( 'answer_tendency_date', '') answer_tendency_data = self.get_argument( 'answer_tendency_data', '') answer_tendency_data = json.loads(answer_tendency_data) answer_tendency_date = json.loads(answer_tendency_date) answer_tendency_date = deal_with_data(answer_tendency_date) if answer_tendency_date and answer_tendency_data: worksheet = workbook.add_worksheet(name=chart_name) worksheet.merge_range(1, 2, 1, 5, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 2, chart_name, cell_format=title_format) worksheet.merge_range(1, 0, 1, 1, '筛选条件', cell_format=title_format) worksheet.merge_range(2, 0, 3, 1, '总体答题次数', cell_format=title_format) worksheet.write_string(2, 2, '日期', cell_format=data_format) worksheet.write_string(3, 2, '答题次数', cell_format=data_format) answer_tendency_title = list(answer_tendency_data.keys()) answer_data_list = list(answer_tendency_data.values()) # 有筛选条件的数据 if '总体答题次数' in answer_tendency_title: position = answer_tendency_title.index('总体答题次数') else: position = answer_data_list.index( max(sum(answer_data_list))) if len(answer_data_list) > position: answer_tendency_title.remove( answer_tendency_title[position]) for index, date in enumerate(answer_tendency_date): worksheet.write_string(2, 3 + index, date) if '总体答题次数' in list(answer_tendency_data.keys()): worksheet.write_string( 3, 3 + index, str(answer_tendency_data['总体答题次数'][index]), cell_format=data_center_format) else: max_data_list = max(sum(answer_data_list)) worksheet.write_string( 3, 2 + order, max_data_list[index - 1], cell_format=data_center_format) if answer_tendency_title: # 有筛选条件得数据写入到excel for index, condition_title in enumerate( answer_tendency_title): worksheet.merge_range(2 * (index + 2) + index + 1, 0, 2 * (index + 2) + 2 + index, 1, condition_title, cell_format=title_format) worksheet.write_string(2 * (index + 2) + index + 1, 2, '日期', cell_format=data_format) worksheet.write_string(2 * (index + 2) + index + 2, 2, '答题次数', cell_format=data_format) for condition_index, data in enumerate( answer_tendency_data[condition_title]): worksheet.write_string(2 * (index + 2) + index + 2, 2 + condition_index + 1, str(data), cell_format=data_format) worksheet.write_string( 2 * (index + 2) + index + 1, 2 + condition_index + 1, answer_tendency_date[condition_index], cell_format=data_format) if order == '3' and chart_name and data_dict: # 活跃度的导出excel worksheet = workbook.add_worksheet(name=chart_name) for order in range(1, 31): worksheet.write_string(2, 2 + order, str(order), cell_format=data_center_format) if '总体活跃度' in list(data_dict.keys()): worksheet.write_string(3, 2 + order, data_dict['总体活跃度'][order - 1] + '%', cell_format=data_center_format) else: max_data_list = max(sum(data_list)) worksheet.write_string(3, 2 + order, max_data_list[order - 1] + '%', cell_format=data_center_format) worksheet.merge_range(1, 2, 1, 5, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 2, chart_name, cell_format=title_format) worksheet.merge_range(1, 0, 1, 1, '筛选条件', cell_format=title_format) worksheet.merge_range(2, 0, 3, 1, '总体活跃度(%)', cell_format=title_format) worksheet.write_string(2, 2, '活跃天数', cell_format=data_format) worksheet.write_string(3, 2, '活跃度(%)', cell_format=data_format) if condition_title_list: # 有筛选条件得数据写入到excel for index, condition_title in enumerate( condition_title_list): worksheet.merge_range(2 * (index + 2) + index + 1, 0, 2 * (index + 2) + 2 + index, 1, condition_title, cell_format=title_format) worksheet.write_string(2 * (index + 2) + index + 1, 2, '活跃天数', cell_format=data_format) for order in range(1, 31): worksheet.write_string(2 * (index + 2) + index + 1, 2 + order, str(order), cell_format=data_format) worksheet.write_string(2 * (index + 2) + index + 2, 2, '活跃度(%)', cell_format=data_format) for condition_index, data in enumerate( data_dict[condition_title]): worksheet.write_string(2 * (index + 2) + index + 2, 2 + condition_index + 1, data, cell_format=data_format) # 每日参与top5的导出数据 if order == '4' and chart_name: # 每日参与top_5的数据 stat_category = self.get_argument('stat_category', '') top_five_data_list = self.get_argument('top_five_data', '') if top_five_data_list: top_five_data_list = json.loads(top_five_data_list) date_list = self.get_argument('date', '') if date_list: date_list = json.loads(date_list) date_list = deal_with_data(date_list) if stat_category and top_five_data_list and date_list: data_series_dict, province_and_city_dict = deal_with_data_excel( date_list, top_five_data_list) # {'江苏': ['南京', '苏州‘], '浙江':['杭州']} total_data_dict = {} # 某个省下面的所有的市 报表中有数据的市 city_title_list = [] # 报表中省的列表 province_title_list = [] # 省和市的列表 total_title = [] show_name_list = [] show_data_list = [] # 需要添加undefined的省份 need_append_undifend_province_list = [] for top_five_data in top_five_data_list: temple_data = [] temple_name = [] for index, data in enumerate(top_five_data): total_title.append(data['name']) if data['name'] and data['value']: temple_name.append( {date_list[index]: data['name']}) temple_data.append( {date_list[index]: data['value']}) show_name_list.append(temple_name) show_data_list.append(temple_data) total_title = [title for title in total_title if title] for total in total_title: if ' ' in total: province_title_list.append(total.split(' ')[0]) city_title_list.append(total.split(' ')[1]) if total.split(' ')[1] == 'undefined': need_append_undifend_province_list.append( total.split(' ')[0]) province_title_list = list(set(province_title_list)) city_title_list = list( set([city for city in city_title_list if city])) for province_title in province_title_list: total_data_dict[province_title] = city_title_list province = await AdministrativeDivision.find_one({ 'title': province_title, 'parent_code': None }) if province: belong_provice_city_title_list = await AdministrativeDivision.distinct( 'title', {'parent_code': province.code}) total_data_dict[province_title] = list( set(city_title_list) & set(belong_provice_city_title_list)) total_data_dict[province_title] = list( set(city_title_list) & set(belong_provice_city_title_list)) # 各个省的市的个数 length_list = [] for index, city_title in enumerate( list(total_data_dict.values())): if list(total_data_dict.keys() )[index] in need_append_undifend_province_list: total_data_dict.get( list(total_data_dict.keys())[index]).append( 'undefined') for index, city_title in enumerate( list(total_data_dict.values())): if city_title: length_list.append(len(city_title)) province_length = sum(length_list) + len( list(total_data_dict.values())) if province_length == 0: province_length = 10 worksheet = workbook.add_worksheet(name=chart_name + '(' + stat_category + ')') worksheet.merge_range(0, 0, province_length, 0, '每日参与' + stat_category, cell_format=data_format) worksheet.merge_range(1, 1, province_length, 1, '导出时间: ' + export_time, cell_format=data_format) worksheet.merge_range(0, 2, 0, 4, '日期', cell_format=data_center_format) for index, date in enumerate(date_list): worksheet.write_string(0, 5 + index, date, cell_format=data_format) worksheet.merge_range(1, 2, province_length, 2, '省份', cell_format=data_center_format) city_map = {} province_map = {} if total_data_dict: choice_city_title_list = list(total_data_dict.values()) for index, data in enumerate(choice_city_title_list): if index == 0: worksheet.merge_range( 1, 3, 1 + len(data), 3, list(total_data_dict.keys())[index], cell_format=data_center_format) else: worksheet.merge_range( 1 + sum(length_list[:index]) + index, 3, sum(length_list[:index + 1]) + index + 1, 3, list(total_data_dict.keys())[index], cell_format=data_center_format) if index == 0: for city_index, city in enumerate(data): if city == 'undefined': city = '_' worksheet.write_string( 1, 4, list(total_data_dict.keys())[index], cell_format=data_center_format) worksheet.write_string( 2 + city_index, 4, city, cell_format=data_center_format) worksheet.write_string( 1, 5, '6666', cell_format=data_format) city_map[city] = 2 + city_index province_map[list( total_data_dict.keys())[index]] = 1 Position(city, 2 + city_index, 4) Position( list(total_data_dict.keys())[index], 1, 4) else: for city_index, city in enumerate(data): if city == 'undefined': city = '_' worksheet.write_string( sum(length_list[:index]) + index + 1, 4, list(total_data_dict.keys())[index], cell_format=data_center_format) worksheet.write_string( sum(length_list[:index]) + index + 2 + city_index, 4, city, cell_format=data_center_format) city_map[city] = sum( length_list[:index] ) + 2 + index + city_index province_map[list( total_data_dict.keys())[index]] = sum( length_list[:index]) + index + 1 Position( city, sum(length_list[:index]) + 2 + index + city_index, 4) Position( list(total_data_dict.keys())[index], sum(length_list[:index]) + index + 1, 4) for index, data in enumerate(choice_city_title_list): if index == 0: for key, value in data_series_dict.items(): if key.split(' ')[0] == 'undefined': position = Position( key.split(' ')[0], city_map['_'], 4) else: position = Position( key.split(' ')[0], city_map[key.split(' ')[0]], 4) if position: order = date_list.index( key.split(' ')[1]) worksheet.write_number( position.row, 5 + order, int(value)) else: for key, value in data_series_dict.items(): if key.split(' ')[0] == 'undefined': position = Position( key.split(' ')[0], city_map['_'], 4) else: position = Position( key.split(' ')[0], city_map[key.split(' ')[0]], 4) if position: order = date_list.index( key.split(' ')[1]) worksheet.write_number( position.row, 5 + order, int(value)) for order, date in enumerate(date_list): for index, value in enumerate( list(province_map.values())): if index != len(list( province_map.values())) - 1: first = value + 2 end = list(province_map.values())[index + 1] else: first = list( province_map.values())[index] + 2 end = province_length + 1 col = 5 + order col = convert(col) first = col + str(first) end = col + str(end) worksheet.write_formula( value, 5 + order, '=SUM(' + first + ':' + end + ')') # 学习近况的导出数据 if order == '1' and chart_name: # 取前一天凌晨12点之前的数据 time_match = get_yesterday() time_match_stage = MatchStage( {'updated_dt': { '$lt': time_match }}) province_code_list, city_code_list, _ = await do_different_administrative_division2( self.current_user.manage_region_code_list) month_stage_list = [] member_stage_list = [] accuracy_stage_list = [] if province_code_list: month_stage_list.append( MatchStage( {'province_code': { '$in': province_code_list }})) member_stage_list.append( MatchStage( {'province_code': { '$in': province_code_list }})) accuracy_stage_list.append( MatchStage( {'province_code': { '$in': province_code_list }})) if city_code_list: month_stage_list.append( MatchStage({'city_code': { '$in': city_code_list }})) member_stage_list.append( MatchStage({'city_code': { '$in': city_code_list }})) accuracy_stage_list.append( MatchStage({'city_code': { '$in': city_code_list }})) add_fields_stage = AddFieldsStage( t_accuracy={ '$cond': { 'if': { '$eq': ['$t_total', 0] }, 'then': 0, 'else': { '$divide': ['$t_correct', '$t_total'] } } }) member_stage_list.append( MatchStage({'status': STATUS_USER_ACTIVE})) month_group_stage = GroupStage( { 'province_code': '$province_code', 'created_dt': { "$dateToString": { "format": "%Y-%m", "date": "$created_dt" } } }, sum={'$sum': '$learn_times'}) lookup_stage = LookupStage(AdministrativeDivision, '_id', 'post_code', 'ad_list') member_group_stage = GroupStage( { 'province_code': '$province_code', 'created_dt': { "$dateToString": { "format": "%Y-%m", "date": "$created_dt" } } }, sum={'$sum': 1}) accuracy_group_stage = GroupStage( { 'province_code': '$province_code', 'created_dt': { "$dateToString": { "format": "%Y-%m", "date": "$created_dt" } } }, t_total={'$sum': '$total'}, t_correct={'$sum': '$correct'}) group_stage = GroupStage('province_code', t_total={'$sum': '$total'}, t_correct={'$sum': '$correct'}) month_sort_stage = SortStage([('_id.created_dt', ASC)]) # 次数 month_stage_list.extend([ time_match_stage, month_group_stage, lookup_stage, month_sort_stage ]) # 人数 member_stage_list.extend([ time_match_stage, member_group_stage, lookup_stage, month_sort_stage ]) accuracy_province_stage_list = copy.deepcopy( accuracy_stage_list) accuracy_province_stage_list.extend([ time_match_stage, group_stage, lookup_stage, add_fields_stage, month_sort_stage ]) # 省和月份共同筛选的正确率 accuracy_stage_list.extend([ time_match_stage, accuracy_group_stage, lookup_stage, add_fields_stage, month_sort_stage ]) # 只有省的正确率 month_province_list = MemberLearningDayStatistics.aggregate( month_stage_list) member_province_list = Member.aggregate(member_stage_list) accuracy_province_list = MemberSubjectStatistics.aggregate( accuracy_stage_list) total_accuracy = MemberSubjectStatistics.aggregate( accuracy_province_stage_list) month_province_dict = {} member_province_dict = {} accuracy_province_dict = {} date_list = [] province_title_list = [] province_map = {} member_date_list = [] accuracy_date_list = [] # 次数 while await month_province_list.fetch_next: month_province = month_province_list.next_object() if month_province: province_dt = month_province.id if month_province.id else '000000' province = await AdministrativeDivision.find_one({ 'code': province_dt.get('province_code'), 'record_flag': 1, 'parent_code': None }) if province_dt.get('created_dt') not in date_list: date_list.append(province_dt.get('created_dt')) province_title = '' if province: province_title = province.title province_title_list.append(province_title) province_title_list = list(set(province_title_list)) dt = province_dt.get('created_dt') month_province_dict[province_title + ' ' + dt] = month_province.sum # 人数 while await member_province_list.fetch_next: member_province = member_province_list.next_object() if member_province: member_province_id = member_province.id if member_province.id else '' province = await AdministrativeDivision.find_one({ 'code': member_province_id.get('province_code'), 'record_flag': 1, 'parent_code': None }) province_title = '' if province: province_title = province.title dt = member_province_id.get('created_dt') if member_province_id.get( 'created_dt') not in member_date_list: member_date_list.append( member_province_id.get('created_dt')) member_province_dict[province_title + ' ' + dt] = member_province.sum # 正确率 while await accuracy_province_list.fetch_next: accuracy_province = accuracy_province_list.next_object() if accuracy_province: accuracy_province_id = accuracy_province.id if accuracy_province.id else '' province = await AdministrativeDivision.find_one({ 'code': accuracy_province_id.get('province_code'), 'record_flag': 1, 'parent_code': None }) province_title = '' if province: province_title = province.title dt = accuracy_province_id.get('created_dt') if accuracy_province_id.get( 'created_dt') not in accuracy_date_list: accuracy_date_list.append( accuracy_province_id.get('created_dt')) if accuracy_province.t_total == 0: accuracy_province_dict[province_title + ' ' + dt] = 0 else: accuracy_province_dict[ province_title + ' ' + dt] = (accuracy_province.t_correct / accuracy_province.t_total) * 100 province_dict = {} # 总的题目 total_quantity_list = [] # 总的答对题目 correct_quantity_list = [] # 总的正确率 while await total_accuracy.fetch_next: province_stat = total_accuracy.next_object() if province_stat: province_code = province_stat.id if province_stat.id else '000000' total = province_stat.t_total if province_stat.t_total else 0 correct = province_stat.t_correct if province_stat.t_correct else 0 province = await AdministrativeDivision.find_one({ 'code': province_code, 'record_flag': 1, 'parent_code': None }) province_title = '' if province: province_title = province.title province_dict[province_title] = round( correct / total * 100 if total > 0 else 0, 2) total_quantity_list.append(total) correct_quantity_list.append(correct) # 次数的sheet print(date_list) worksheet = workbook.add_worksheet(name='次数') worksheet.merge_range(0, 0, 0, len(date_list) + 1, '公民参与科学素质学习状况', cell_format=title_format) worksheet.write_string(1, 0, '已累计次数', cell_format=data_center_format) worksheet.merge_range(1, 2, 1, len(date_list) + 1, '导出时间:' + export_time, cell_format=data_center_format) worksheet.merge_range(2, 0, 3, 0, '省份', cell_format=data_center_format) worksheet.merge_range(2, 1, 3, 1, '人数汇总(人)', cell_format=data_center_format) worksheet.merge_range(2, 2, 2, 6, '每月新增人数(人)', cell_format=data_center_format) insert_excel(date_list, worksheet, data_center_format, province_title_list, province_map, month_province_dict) # 人数的sheet worksheet = workbook.add_worksheet(name='人数') worksheet.merge_range(0, 0, 0, len(member_date_list) + 1, '公民参与科学素质学习状况', cell_format=title_format) worksheet.write_string(1, 0, '已累计人数', cell_format=data_center_format) worksheet.merge_range(1, 2, 1, len(member_date_list) + 1, '导出时间:' + export_time, cell_format=data_center_format) worksheet.merge_range(2, 0, 3, 0, '省份', cell_format=data_center_format) worksheet.merge_range(2, 1, 3, 1, '人数汇总(人/次)', cell_format=data_center_format) worksheet.merge_range(2, 2, 2, 6, '每月新增人数(人/次)', cell_format=data_center_format) insert_excel(member_date_list, worksheet, data_center_format, province_title_list, province_map, member_province_dict) # 正确率的sheet worksheet = workbook.add_worksheet(name='正确率') total_province_accuracy = round( sum(correct_quantity_list) / sum(total_quantity_list) * 100, 2) worksheet.merge_range(0, 0, 0, len(date_list) + 1, '公民参与科学素质学习状况', cell_format=title_format) worksheet.merge_range(1, 0, 1, 1, '总体正确率' + str(total_province_accuracy) + '%', cell_format=data_center_format) worksheet.merge_range(1, 2, 1, len(date_list) + 1, '导出时间:' + export_time, cell_format=data_center_format) worksheet.merge_range(2, 0, 3, 0, '省份', cell_format=data_center_format) worksheet.merge_range(2, 1, 3, 1, '正确率', cell_format=data_center_format) worksheet.merge_range(2, 2, 2, 6, '每月正确率波动(%)', cell_format=data_center_format) for index, date in enumerate(accuracy_date_list): worksheet.write_string(3, 2 + index, date, cell_format=data_center_format) for index, province_title in enumerate(province_title_list): worksheet.write_string(4 + index, 0, province_title, cell_format=data_center_format) worksheet.write_string(4 + index, 1, str(province_dict[province_title]), cell_format=data_center_format) province_map[province_title] = 4 + index for month_province, value in accuracy_province_dict.items(): value = round(value, 2) position = Position( month_province.split(' ')[0], province_map[month_province.split(' ')[0]], 0) order = accuracy_date_list.index( month_province.split(' ')[1]) worksheet.write_string(position.row, 2 + order, str(value)) workbook.close() self.set_header( 'Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) self.set_header( 'Content-Disposition', "attachment;filename*=utf-8''{}.xlsx".format( quote(chart_name.encode('utf-8')))) self.write(output.getvalue()) self.finish() except Exception: logger.error(traceback.format_exc())