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 _get_daily_code(dt: datetime.datetime = None): """ 获取对接标识 :return: """ if not dt: dt = datetime.datetime.now() return datetime2str(dt, date_format='%Y%m%d000000')
def generate_cache_key(category): """ 生成当日缓存key :param category: :return: """ today = datetime2str(datetime.datetime.now(), date_format='%Y%m%d') return "{today}-{category}".format(today=today, category=category)
def transform_time_format(export_dt): """ 把时间日期格式得数据转换成指定得字符串形式 :param export_dt: :return: """ time_str = datetime2str(export_dt, date_format='%Y%m%d') return time_str
def get_cache_key(race_cid, category): """ 生成当日缓存key :param category: :return: """ today = datetime2str(datetime.datetime.now(), date_format='%Y%m%d') return "{race_cid}-{today}-{category}".format(race_cid=race_cid, today=today, category=category)
def get_special_cache_key(race_cid, category): """ 生成安徽报告缓存key :param race_cid :param category: :return: """ today = datetime2str(datetime.datetime.now(), date_format='%Y%m%d') return "{race_cid}_{today}_{category}".format(race_cid=race_cid, today=today, category=category)
def early_warning_empty(task_name, cache_key, param, error_msg): """ :param task_name: :param cache_key: :param param: :param error_msg: :return: """ content = 'host=%s,[ERROR] task_name=(%s), time=(%s): cache_key=%s, param=%s\n %s' % ( settings.SERVER_HOST, task_name, datetime2str( datetime.datetime.now()), cache_key, str(param), error_msg) send_instant_mail( mail_to=['*****@*****.**', '*****@*****.**'], subject='学习之旅报表任务失败', content=content)
async def upload_scrawl(self): """ 上传涂鸦 :return: 新文件名,原始文件名 """ scrawl_name = '' base64_img_data = self.get_argument( self.ue_config.get('scrawlFieldName')) if base64_img_data: img_data = base64decode(base64_img_data) if img_data: try: scrawl_name = '%s.png' % md5( datetime2str(datetime.datetime.now(), '%Y%m%d%H%M%S%f')) img_path = os.path.join(settings.STATIC_PATH, 'files', scrawl_name) with open(img_path, 'wb') as img: img.write(img_data) except Exception: logger.error(traceback.format_exc()) finally: if img: img.close() if scrawl_name: file_code = get_increase_code(KEY_INCREASE_UPLOAD_FILE) uf = UploadFiles(code=file_code, title=scrawl_name, source_title=scrawl_name, category=CATEGORY_CERTIFICATE_UE, content_type='image/png') uf.size = os.path.getsize( os.path.join(settings.STATIC_PATH, 'files', scrawl_name)) await uf.save() return scrawl_name
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())
def _get_daily_code(dt: datetime.datetime): """ 获取自然日标识 :return: """ return datetime2str(dt, date_format='%Y%m%d000000')
def _get_docking_code(dt: datetime.datetime): """ 获取对接标识 :return: """ return datetime2str(dt, date_format='%Y%m%d000000')
def get_daily_code(dt): """ :return: """ return datetime2str(dt, date_format='%Y%m%d000000')
def start_statistics_learning_situation(self, cache_key, chart_type=None, m_city_code_list=None, gender_list=None, province_code_list=None, city_code_list=None, age_group_list=None, education_list=None, dimension=None, time_range=None, dimension_code=None): """ 学习效果任务LEARNING_SITUATION_STATISTICS :param self: :param cache_key: :param chart_type: :param m_city_code_list: :param gender_list: :param province_code_list: :param city_code_list: :param age_group_list: :param education_list: :param dimension: :param time_range: :param dimension_code: :return: """ logger.info('[START] LEARNING_SITUATION_STATISTICS(%s), cache_key=%s' % (self.request.id, cache_key)) try: save_cache_condition('start_statistics_learning_situation', cache_key=cache_key, chart_type=chart_type, m_city_code_list=m_city_code_list, gender_list=gender_list, province_code_list=province_code_list, city_code_list=city_code_list, age_group_list=age_group_list, education_list=education_list, dimension=dimension, time_range=time_range, dimension_code=dimension_code) do_statistics_learning_situation(cache_key=cache_key, chart_type=chart_type, m_city_code_list=m_city_code_list, gender_list=gender_list, province_code_list=province_code_list, city_code_list=city_code_list, age_group_list=age_group_list, education_list=education_list, dimension=dimension, time_range=time_range, dimension_code=dimension_code) except Exception: logger.error('[ERROR] locals_variable: %s \n %s' % (str(locals()), traceback.format_exc())) content = 'host=%s,[ERROR] LEARNING_SITUATION_STATISTICS(%s), time=%s,locals_variable: %s \n %s' % ( settings.SERVER_HOST, self.request.id, datetime2str(datetime.datetime.now()), str( locals()), traceback.format_exc()) send_instant_mail( mail_to=['*****@*****.**', '*****@*****.**'], subject='学习效果报表任务失败', content=content) logger.info('[ END ] LEARNING_SITUATION_STATISTICS(%s)' % self.request.id)
async def post(self): res_code = {'code': 0, 'msg': ''} subject_dimension_list = await SubjectDimension.aggregate([ MatchStage({'parent_cid': None}), SortStage([('ordered', ASC)]), LookupStage(SubjectDimension, 'cid', 'parent_cid', 'sub_list') ]).to_list(None) search_arguments = {} match_dict = {} m_province_code_list, m_city_code_list, _ = await do_different_administrative_division2( self.current_user.manage_region_code_list) if m_province_code_list: match_dict['province_code'] = {'$in': m_province_code_list} if m_city_code_list: match_dict['city_code'] = {'$in': m_city_code_list} # 维度信息 dimension_dict = {} for dimension in subject_dimension_list: t_dimension = self.get_argument(dimension.cid, '') if t_dimension: dimension_dict['%s' % dimension.cid] = t_dimension search_arguments[dimension.cid] = t_dimension query_params = {} s_province = self.get_argument('province', '') if s_province: query_params['province_code'] = s_province search_arguments['province'] = s_province s_city = self.get_argument('city', '') if s_city: query_params['city_code'] = s_city search_arguments['city'] = s_city s_age_group = self.get_argument('age_group', '') if s_age_group: query_params['age_group'] = int(s_age_group) search_arguments['age_group'] = s_age_group s_gender = self.get_argument('gender', '') if s_gender: query_params['gender'] = int(s_gender) search_arguments['gender'] = s_gender s_education = self.get_argument('education', '') if s_education: query_params['education'] = int(s_education) search_arguments['education'] = s_education try: category = parse_search_params(query_params) category_list = await ReportSubjectStatisticsMiddle.distinct('category') result = RedisCache.hget(KEY_CACHE_REPORT_CONDITION, str(category)) logger.info('CATEGORY: %s, result is %s' % (str(category), result)) if result is None: res_code['msg'] = '数据截至日期为:当前时间' start_split_subject_stat_task.delay(category, datetime.datetime.now()) param_dict = await self.do_paging_from_member_subject_statistics(match_dict, query_params, dimension_dict, search_arguments) else: result = int(result.decode()) if result == STATUS_SUBJECT_STATISTICS_END: param_dict = await self.do_paging_from_report_subject_statistics_middle(category, match_dict, query_params, dimension_dict, search_arguments) item = param_dict.get('paging').page_items if item: res_code['msg'] = '数据截至日期为:%s' % datetime2str(item[0].task_dt) else: res_code['msg'] = '数据截至日期为:当前时间' param_dict = await self.do_paging_from_member_subject_statistics(match_dict, query_params, dimension_dict, search_arguments) param_dict.update(locals()) param_dict.pop('self') html = self.render_string('backoffice/reports/subject_analysis_table_list.html', **param_dict).decode( 'utf-8') res_code['code'] = 1 res_code['html'] = self.render_string('backoffice/reports/subject_analysis_table_list.html', **param_dict).decode('utf-8') except Exception: logger.error(traceback.format_exc()) return res_code
def export_lottery_detail(race_cid: str): """ :param race_cid: :return: """ race = Race.sync_find_one({'cid': race_cid}) workbook = xlsxwriter.Workbook('%s中奖人员名单.xlsx' % race.title) worksheet = workbook.add_worksheet() _b_dt = datetime.now().replace(2019, 5, 20, 0, 0, 0, 0) _e_dt = datetime.now() cursor = RedPacketBox.sync_aggregate( [ MatchStage({ 'draw_dt': { '$gt': _b_dt, '$lt': _e_dt }, 'draw_status': 0, 'race_cid': race_cid }), LookupStage(Member, 'member_cid', 'cid', 'member_list'), # LookupStage(RaceMapping, 'member_cid', 'member_cid', 'map_list'), # LookupStage(RedPacketItemSetting, 'award_cid', 'cid', 'item_list') ], read_preference=ReadPreference.PRIMARY).batch_size(128) cols = [ '序号', '时间', '用户OPENID', '微信昵称', '奖品', '奖励金额', '领取状态', '省', '市', '区', '领取时间', '关卡序号', '获奖时间', '答题时间', '红包id' ] for col_index, col_name in enumerate(cols): worksheet.write(0, col_index, col_name) _row = 1 while True: try: box = cursor.next() worksheet.write_number(_row, 0, _row) worksheet.write_string(_row, 1, datetime2str(box.draw_dt)) worksheet.write_string( _row, 2, box.member_list[0].open_id if box.member_list else '未知') worksheet.write_string( _row, 3, box.member_list[0].nick_name if box.member_list else '未知') # worksheet.write_string(_row, 4, box.item_list[0].title if box.item_list else '未知') worksheet.write_number(_row, 5, box.award_amount) worksheet.write_string( _row, 6, STATUS_REDPACKET_AWARD_DICT.get(box.draw_status)) # if box.map_list: # worksheet.write_string(_row, 7, box.map_list[0].auth_address.get('province', '')) # worksheet.write_string(_row, 8, box.map_list[0].auth_address.get('city', '')) # worksheet.write_string(_row, 9, box.map_list[0].auth_address.get('district', '')) worksheet.write_string(_row, 10, datetime2str(box.request_dt)) checkpt = RaceGameCheckPoint.sync_get_by_cid(box.checkpoint_cid) if checkpt: worksheet.write_number(_row, 11, checkpt.index) worksheet.write_string(_row, 12, datetime2str(box.draw_dt)) # his = MemberCheckPointHistory.sync_find_one( # {'member_cid': box.member_cid, 'check_point_cid': box.checkpoint_cid}) # if his: # worksheet.write_string(_row, 13, datetime2str(his.created_dt)) worksheet.write_string(_row, 14, box.cid) print('has exec', _row) _row += 1 except StopIteration: break workbook.close()
def do_push_action_template_msg(survey_action): if survey_action: pushed_open_id_list = [] pushed_list = survey_action.pushed_member_cid_list all_member_cid_list = survey_action.all_member_cid_list if pushed_list is None: pushed_list = [] if all_member_cid_list is None: all_member_cid_list = [] questionnaire = Questionnaire.sync_find_one({'code': survey_action.q_cid}) if questionnaire: title = survey_action.title content = survey_action.content if survey_action.content is not None else '' start_date = '-' end_date = '-' surplus_times = '-' try: start_date = datetime2str(survey_action.get('start_datetime'), date_format='%Y-%m-%d') end_date = datetime2str(survey_action.get('end_datetime'), date_format='%Y-%m-%d') start_datetime = survey_action.start_datetime.replace(hour=0, minute=0, second=0, microsecond=0) end_datetime = survey_action.end_datetime.replace(hour=23, minute=59, second=59, microsecond=999999) days = (end_datetime - start_datetime).days seconds = (end_datetime - start_datetime).seconds if seconds > 0: hours = int(seconds / (60 * 60)) seconds = seconds % (60 * 60) minutes = int(seconds / 60) seconds = seconds % 60 if seconds > 0: surplus_times = u'%s 秒' % seconds if minutes > 0: surplus_times = u'%s 分' % minutes + surplus_times if hours > 0: surplus_times = u'%s 小时' % hours + surplus_times if days > 0: surplus_times = u'%s 天' % days + surplus_times else: surplus_times = u'0 秒' except Exception: logger.error(traceback.format_exc()) vehicle_code = survey_action.vehicle_code if vehicle_code: member_list = Member.sync_find({'vehicle_code': vehicle_code, 'status': STATUS_USER_ACTIVE}) for member in member_list: if member and member.cid not in pushed_list and member.cid in all_member_cid_list: open_id = member.open_id if open_id: name = member.name msg_content = get_template_msg_content(title, content, name, start_date, end_date, surplus_times) result = push_active_template_message(MSG_TEMPLATE_ID, open_id, msg_content, to_url=questionnaire.get('url')) if result.get('errcode') == 0: pushed_open_id_list.append(open_id) if pushed_open_id_list: code_list = Member.sync_distinct('code', {'open_id': {'$in': pushed_open_id_list}}) pushed_list.extend(code_list) if not survey_action.push_datetime: survey_action.push_datetime = datetime.datetime.now() survey_action.pushed_member_cid_list = pushed_list # 判断推送成功和推送失败 if len(pushed_list) == len(all_member_cid_list): survey_action.status = STATUS_PULL_ACTION_PUSHED_SUCCESS elif len(pushed_list) < len(all_member_cid_list): survey_action.status = STATUS_PULL_ACTION_PUSHED_FAIL else: survey_action.status = STATUS_PULL_ACTION_PUSHED survey_action.updated_dt = datetime.datetime.now() survey_action.sync_save() # 更新活动SurveyActivity中的推送数量 survey_activity = SurveyActivity.sync_find_one({'cid': survey_action.activity_cid}) if survey_activity: pushed_amount = survey_activity.pushed_amount if pushed_amount is None: pushed_amount = 0 survey_activity.pushed_amount = pushed_amount + len(pushed_list) survey_activity.updated_dt = datetime.datetime.now() survey_activity.sync_save() else: if not survey_action.push_datetime: survey_action.push_datetime = datetime.datetime.now() survey_action.pushed_member_cid_list = [] survey_action.status = STATUS_PULL_ACTION_PUSHED_FAIL survey_action.updated_dt = datetime.datetime.now() survey_action.sync_save()
'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) worksheet.write_number(row, 7, len(history.result)) correct_count = 0 for r in history.result: if r.get('true_answer'): correct_count += 1 worksheet.write_number(row, 8, correct_count) worksheet.write_string(row, 9, datetime2str(history.created_dt)) except StopIteration: break workbook.close()
def datetime_format(self, dt, dt_format='%Y-%m-%d %H:%M:%S', default='-'): if isinstance(dt, datetime.datetime): return datetime2str(dt, dt_format) if not dt: return default return dt
def do_statistics_member_top_n(cache_key, m_city_code_list, stat_type, top_n, time_range): """ :param cache_key: :param m_city_code_list: :param stat_type: :param top_n: :param time_range: :return: """ RedisCache.set(cache_key, KEY_CACHE_REPORT_DOING_NOW, 5 * 60) stage_list = [] # 取前一天凌晨12点之前的数据 time_match = get_yesterday() stage_list.append(MatchStage({'updated_dt': {'$lt': time_match}})) if m_city_code_list: stage_list.append(MatchStage({'city_code': {'$in': m_city_code_list}})) s_code = '' e_code = '' if time_range: suffix = time_range[-1:] range_num = int(time_range.replace(suffix, '')) delta = None if suffix.upper() == 'D': delta = datetime.timedelta(days=range_num) elif suffix.upper() == 'M': delta = datetime.timedelta(days=range_num * 30) elif suffix.upper() == 'Y': delta = datetime.timedelta(days=range_num * 365) if delta: s_code = datetime2str(datetime.datetime.now() - delta, date_format='%Y%m%d000000') e_code = datetime2str(datetime.datetime.now(), date_format='%Y%m%d000000') if s_code and e_code: stage_list.append( MatchStage({'daily_code': { '$gte': s_code, '$lte': e_code }})) stage_list.extend([ GroupStage({ 'daily_code': '$daily_code', 'member_cid': '$member_cid', 'province_code': '$province_code' } if stat_type == 1 else { 'daily_code': '$daily_code', 'member_cid': '$member_cid', 'province_code': '$province_code', 'city_code': '$city_code' }, learn_times={'$sum': '$learn_times'}), GroupStage({ 'daily_code': '$_id.daily_code', 'province_code': '$_id.province_code' } if stat_type == 1 else { 'daily_code': '$_id.daily_code', 'province_code': '$_id.province_code', 'city_code': '$_id.city_code' }, count={'$sum': 1}, times={'$sum': '$learn_times'}), LookupStage(AdministrativeDivision, '_id.province_code', 'post_code', 'province_list'), LookupStage(AdministrativeDivision, '_id.city_code', 'post_code', 'city_list'), ProjectStage( **{ '_id': False, 'daily_code': '$_id.daily_code', 'count': '$count', 'times': '$times', 'province_code': '$_id.province_code', 'province_title': '$province_list.title', 'ad_code': '$_id.province_code' if stat_type == 1 else '$_id.city_code', 'ad_title': '$province_list.title' if stat_type == 1 else '$city_list.title' }), SortStage([('daily_code', ASC), ('count', DESC)]) ]) # 检索数据 data = [] stat_cursor = MemberDailyStatistics.sync_aggregate(stage_list) t_code, t_list = None, None top_n_found = False while True: try: daily_stat = stat_cursor.next() if daily_stat: daily_code = daily_stat.daily_code if not daily_code == t_code: t_code = daily_code t_list = [] top_n_found = False print(t_code) if len(t_list) < top_n: t_list.append({ 'date': daily_code[:8], 'ad_code': daily_stat.ad_code, 'province_title': daily_stat.province_title[0] if daily_stat.province_title and stat_type == 2 else '', 'title': daily_stat.ad_title[0] if daily_stat.ad_title else 'undefined', 'quantity': daily_stat.count, 'times': daily_stat.times }) elif not top_n_found: if t_code is not None: data.append(t_list) top_n_found = True except StopIteration: break if not data: early_warning_empty("start_statistics_member_top_n", cache_key, locals(), '每日参与TOP5统计数据为空,请检查!') RedisCache.set(cache_key, msgpack.packb(data))
async def post(self): order = self.get_argument('order', '') chart_name = self.get_argument('chart_name', '') time = datetime.datetime.now() export_time = datetime2str(time, date_format='%Y-%m-%d %H:%M:%S') 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: # 答题正确分布的数据字典 subject_quantity_data_dict = self.get_argument('answer_subject_quantity_data', '') # 横坐标题目数量的列表 answer_subject_xAxis_list = self.get_argument('answer_subject_xAxis_list', '') if subject_quantity_data_dict and answer_subject_xAxis_list: worksheet = workbook.add_worksheet(name=chart_name) worksheet.merge_range(1, 2, 1, 10, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 10, 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) subject_quantity_data_dict = json.loads(subject_quantity_data_dict) answer_subject_xAxis_list = json.loads(answer_subject_xAxis_list) subject_quantity_title = list(subject_quantity_data_dict.keys()) subject_quantity_title = [subject_quantity for subject_quantity in subject_quantity_title if subject_quantity] subject_quantity_data = list(subject_quantity_data_dict.values()) # 有筛选条件的数据 if '全体人群答对题目分布' in subject_quantity_title: position = subject_quantity_title.index('全体人群答对题目分布') else: position = subject_quantity_data.index(max(sum(subject_quantity_data))) if len(subject_quantity_data) > position: subject_quantity_title.remove(subject_quantity_title[position]) for index, subject_quantity in enumerate(answer_subject_xAxis_list): worksheet.write_string(2, 3 + index, subject_quantity) if '全体人群答对题目分布' in list(subject_quantity_data_dict.keys()): worksheet.write_string(3, 3 + index, str(subject_quantity_data_dict['全体人群答对题目分布'][index]), cell_format=data_center_format) else: max_data_list = max(sum(subject_quantity_data)) worksheet.write_string(3, 2 + order, max_data_list[index - 1], cell_format=data_center_format) if subject_quantity_title: # 有筛选条件得数据写入到excel for index, condition_title in enumerate(subject_quantity_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(subject_quantity_data_dict[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_subject_xAxis_list[condition_index], cell_format=data_format) # 公民科学素质雷达图 if order == "2" and chart_name: # 公民科学素质雷达图的数据列表 radar_title_list = self.get_argument('radar_title_list', '') # 横坐标题目数量的列表 radar_data_list = self.get_argument('radar_data_list', '') # 雷达图展示的文本 text_list = self.get_argument('text_list', '') if radar_title_list and radar_data_list and text_list: worksheet = workbook.add_worksheet(name=chart_name) worksheet.merge_range(1, 2, 1, 10, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 10, 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) radar_title_list = json.loads(radar_title_list) radar_data_list = json.loads(radar_data_list) text_list = json.loads(text_list) first_radar_data = radar_data_list[0] # 有筛选条件的数据 if '全部正确率' in radar_title_list: position = radar_title_list.index('全部正确率') else: sum_list = [] for radar_data in radar_data_list: radar_data = [float(radar) for radar in radar_data] sum_list.append(sum(radar_data)) position = sum_list.index(max(sum_list)) if len(radar_data_list) > position: radar_title_list.remove(radar_title_list[position]) first_radar_data = radar_data_list[position] radar_data_list.remove(radar_data_list[position]) for order, text in enumerate(text_list): worksheet.write_string(2, 3 + order, text, cell_format=data_center_format) worksheet.write_string(3, 3 + order, first_radar_data[order], cell_format=data_center_format) # 有筛选条件的 for index, condition_title in enumerate(radar_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, text in enumerate(text_list): worksheet.write_string(2 * (index + 2) + index + 1, 3 + order, text_list[order], cell_format=data_format) worksheet.write_string(2 * (index + 2) + index + 2, 2, '正确率(%)', cell_format=data_format) worksheet.write_string(2 * (index + 2) + index + 2, 2, '正确率(%)', cell_format=data_format) for order, radar_data in enumerate(radar_data_list[index]): worksheet.write_string(2 * (index + 2) + index + 2, 3 + order, radar_data, cell_format=data_format) # 统计——科学素质题库参数比较分析 if order == "3" and chart_name: # 难度的标题 difficulty_title = self.get_argument('difficulty_title', '') # 难度的数据 difficulty_param_dict = self.get_argument('difficulty_param_dict', '') difficulty_xAxis = self.get_argument('difficulty_xAxis', '') # 维度 knowledge_title = self.get_argument('knowledge_title', '') knowledge_param_dict = self.get_argument('knowledge_param_dict', '') knowledge_xAxis = self.get_argument('knowledge_xAxis', '') # 难度和维度混合的数据 difficulty_knowledge_title = self.get_argument('difficulty_knowledge_title', '') difficulty_knowledge_dict = self.get_argument('difficulty_knowledge_dict', '') if difficulty_title and difficulty_param_dict and difficulty_xAxis and knowledge_title and knowledge_param_dict and knowledge_xAxis and difficulty_knowledge_title and difficulty_knowledge_dict: worksheet = workbook.add_worksheet(name=difficulty_title) worksheet.merge_range(1, 2, 1, 7, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 7, 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) difficulty_param_dict = json.loads(difficulty_param_dict) difficulty_param_title = list(difficulty_param_dict.keys()) difficulty_param_data = list(difficulty_param_dict.values()) difficulty_xAxis = json.loads(difficulty_xAxis) # 有筛选条件的数据 if '总体正确率' in difficulty_param_title: position = difficulty_param_title.index('总体正确率') else: position = difficulty_param_data.index(max(sum(difficulty_param_data))) if len(difficulty_param_data) > position: difficulty_param_title.remove(difficulty_param_title[position]) for index, title in enumerate(difficulty_xAxis): worksheet.write_string(2, 3 + index, title) if '总体正确率' in list(difficulty_param_dict.keys()): worksheet.write_string(3, 3 + index, difficulty_param_dict['总体正确率'][index], cell_format=data_center_format) else: max_data_list = max(sum(difficulty_param_data)) worksheet.write_string(3, 2 + order, max_data_list[index - 1], cell_format=data_center_format) if difficulty_param_title: # 有筛选条件得数据写入到excel for index, condition_title in enumerate(difficulty_param_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(difficulty_param_dict[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, difficulty_xAxis[condition_index], cell_format=data_format) worksheet = workbook.add_worksheet(name=knowledge_title) worksheet.merge_range(1, 2, 1, 5, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 5, 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) knowledge_param_dict = json.loads(knowledge_param_dict) knowledge_param_title = list(knowledge_param_dict.keys()) knowledge_param_data = list(knowledge_param_dict.values()) knowledge_xAxis = json.loads(knowledge_xAxis) # 有筛选条件的数据 if '总体正确率' in knowledge_param_title: position = knowledge_param_title.index('总体正确率') else: position = knowledge_param_data.index(max(sum(knowledge_param_data))) if len(knowledge_param_data) > position: knowledge_param_title.remove(knowledge_param_title[position]) for index, title in enumerate(knowledge_xAxis): worksheet.write_string(2, 3 + index, title) if '总体正确率' in list(knowledge_param_dict.keys()): worksheet.write_string(3, 3 + index, knowledge_param_dict['总体正确率'][index], cell_format=data_center_format) else: max_data_list = max(sum(knowledge_param_data)) worksheet.write_string(3, 2 + order, max_data_list[index - 1], cell_format=data_center_format) if knowledge_param_title: # 有筛选条件得数据写入到excel for index, condition_title in enumerate(knowledge_param_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(knowledge_param_dict[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, knowledge_xAxis[condition_index], cell_format=data_format) # 难度和维度混合的数据 worksheet = workbook.add_worksheet(name=difficulty_knowledge_title) worksheet.merge_range(1, 2, 1, 5, '导出时间' + export_time, cell_format=title_format) worksheet.merge_range(0, 0, 0, 5, chart_name, cell_format=title_format) worksheet.merge_range(1, 0, 1, 1, '筛选条件', cell_format=title_format) worksheet.write_string(2, 0, '知识维度', cell_format=data_center_format) worksheet.write_string(2, 1, '难度', cell_format=data_center_format) difficulty_knowledge_dict = json.loads(difficulty_knowledge_dict) title_list = list(difficulty_knowledge_dict.keys()) # 题目难度的title difficulty_title_list = list(list(difficulty_knowledge_dict.values())[0].keys()) total_data_list = [] for index, title in enumerate(title_list): total_data_list.append(list(list(difficulty_knowledge_dict.values())[index].values())) worksheet.merge_range(3 + index, 0, 3 + index, 1, title, cell_format=data_center_format) for index, difficulty_title in enumerate(difficulty_title_list): worksheet.write_string(2, 2 + index, difficulty_title, cell_format=data_center_format) for index, data_list in enumerate(total_data_list): for order, data in enumerate(data_list): worksheet.write_string(3 + index, 2 + order, str(data) + '%', cell_format=data_center_format) 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())
def export_race_enter_position(race_cid: str, title): """ 导出活动下面参与情况 :return: """ if not isinstance(race_cid, str): raise ValueError("race_cid is not str") now = datetime.datetime.now() export_time_list = [ now + datetime.timedelta(days=-n) for n in (range(1, 8)) ] export_time_list.sort() export_time = [ transform_time_format(export_dt) for export_dt in export_time_list ] export_time.sort() workbook = xlsxwriter.Workbook(title + today + ".xlsx") head_list = [ "新增人数", "参与人数", "参与次数", "通关人数", "红包发放数", "红包领取数量", "红包发放金额", "红包领取金额" ] sheet = workbook.add_worksheet(title + "一周数据") data_center_format = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei' }) sheet.merge_range(0, 0, 1, 0, "城市", data_center_format) sheet.merge_range(0, 1, 1, 1, "区县", data_center_format) sheet.write_string(1, 2, "累计人数") sheet.write_string(1, 3, "累计参与次数") for i in range(1, 8): sheet.merge_range(0, 7 * (i - 1) + 4 + i - 1, 0, 7 * (i - 1) + 4 + i - 1 + 7, export_time[i - 1], data_center_format) for head in range(7 * (i - 1) + 4 + i - 1, 7 * (i - 1) + 4 + i - 1 + 7 + 1): index = head - 8 * (i - 1) - 4 sheet.write_string(1, head, head_list[index]) midnight = (datetime.datetime.now()).replace(hour=0, minute=0, second=0, microsecond=0) race = Race.sync_get_by_cid(race_cid) # 市级活动,如六安市,扬州市 if race.city_code: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'code': race.city_code}) # 该活动的所属城市范围 city_name_list = AdministrativeDivision.sync_distinct( 'title', {'code': race.city_code}) else: city_code_list = AdministrativeDivision.sync_distinct( 'code', {'parent_code': race.province_code}) city_name_list = AdministrativeDivision.sync_distinct( 'title', {'parent_code': race.province_code}) dist_list = [] for city_code in city_code_list: # 该活动区县的范围 dist_list += AdministrativeDivision.sync_distinct( 'title', {'parent_code': city_code}) # 最基本的人数match base_quantity_match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } # 最基本的次数match base_count_match = { 'race_cid': race_cid, 'province': { '$ne': None }, "record_$pushflag": 1, 'city': { "$in": city_name_list }, 'district': { "$in": dist_list } } # 最基本的参与人数match base_enter_quantity_match = { 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list } } # 累计人数 quantity_match_stage = MatchStage({ 'race_cid': race_cid, 'auth_address.province': { '$ne': None }, "record_flag": 1, 'auth_address.city': { "$in": city_name_list }, 'auth_address.district': { "$in": dist_list }, 'created_dt': { '$lte': midnight } }) # 累计次数 daily_code = datetime2str(datetime.datetime.now(), date_format='%Y%m%d') count_match = { 'race_cid': race_cid, 'province': { '$ne': None }, 'city': { '$in': city_name_list }, 'district': { '$in': dist_list }, 'daily_code': { '$lt': daily_code } } lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list') address_sort = SortStage([('_id.city', ASC), ('_id.district', ASC)]) quantity_list = RaceMapping.sync_aggregate([ quantity_match_stage, lookup_stage, ProjectStage(**{ 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) count_list = ReportRacePeopleStatistics.sync_aggregate([ MatchStage(count_match), GroupStage({ 'city': '$city', 'district': '$district' }, sum={'$sum': "$total_num"}) ]) dis_map = {} quantity = 0 for index, address in enumerate(quantity_list): quantity += address.sum sheet.write_string(index + 2, 0, address.id.get('city')) sheet.write_string(index + 2, 1, address.id.get('district')) sheet.write_number(index + 2, 2, address.sum) if address.id.get('district') not in dis_map: dis_map[address.id.get('district')] = index + 2 else: dis_map[address.id.get('district')] += index + 2 count_map = {} for count in count_list: district = count.id.get('district') if district not in count_map: count_map[district] = count.sum print(count_map, 'count') print(dis_map, 'dis_map') for k, v in count_map.items(): position = dis_map.get(k) if position: sheet.write_number(position, 3, v) # 有答题次数,没有人数的情况,跳过 else: continue # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} quantity_time_dict = {} # 一个星期的人数的数据 base_quantity_match['created_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } one_week_quantity_list = RaceMapping.sync_aggregate([ MatchStage(base_quantity_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$created_dt" } }, 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { "daily_code": "$daily_code", 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) for quantity_data in one_week_quantity_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in quantity_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum quantity_time_dict[daily] = temp_dict else: quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # 每日参与人数一周的数据 # 时间与地区人数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} base_enter_quantity_match['updated_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } enter_quantity_time_dict = {} one_week_enter_quantity_list = RaceMapping.sync_aggregate([ MatchStage(base_enter_quantity_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$updated_dt" } }, 'auth_address': "$auth_address", "member_list": "$member_list" }), MatchStage({'member_list': { '$ne': [] }}), GroupStage( { "daily_code": "$daily_code", 'district': '$auth_address.district', 'city': '$auth_address.city' }, sum={'$sum': 1}), address_sort ]) for quantity_data in one_week_enter_quantity_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in enter_quantity_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum enter_quantity_time_dict[daily] = temp_dict else: enter_quantity_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # print(enter_quantity_time_dict, 'enter_quantity') # 每日新增参与次数一周的数据 base_count_match['created_dt'] = { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } one_week_count_list = ReportRacePeopleStatistics.sync_aggregate([ MatchStage(base_count_match), GroupStage( { 'city': '$city', 'district': '$district', 'daily_code': '$daily_code' }, sum={'$sum': "$total_num"}) ]) # 时间与地区次数的字典 {'20190702': {'六安市-叶集区': 20}, '20190703': {'六安市-舒城县': 30}} count_time_dict = {} for quantity_data in one_week_count_list: daily = quantity_data.id.get('daily_code') city = quantity_data.id.get('city') district = quantity_data.id.get('district') if city and district: if daily not in count_time_dict: temp_dict = {} temp_dict["{city}-{district}".format( city=city, district=district)] = quantity_data.sum count_time_dict[daily] = temp_dict else: count_time_dict[daily].update({ "{city}-{district}".format(city=city, district=district): quantity_data.sum }) else: continue # 一周通关的人数 # {'20190702': {'六安市-叶集区': 20, "六安市-舒城县": 15}, '20190703': {'六安市-舒城县': 30}} pass_quantity_time_dict = {} last_checkpoint_cid, _, _, _ = get_export_param(race_cid) # 拿到最后一关的cid pass_match_stage = MatchStage({ 'check_point_cid': last_checkpoint_cid, 'status': 1, 'record_flag': 1, 'created_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } }) check_point_cursor = MemberCheckPointHistory.sync_aggregate( [pass_match_stage, lookup_stage]) member_cid_list = [] while True: try: check_point = check_point_cursor.next() if check_point.member_list: member = check_point.member_list[0] race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': member.cid }) if race_mapping: if race_mapping.auth_address and race_mapping.auth_address.get( 'province'): if check_point.member_cid not in member_cid_list: district = race_mapping.auth_address.get( 'district') city = race_mapping.auth_address.get('city') if district and city: member_cid_list.append(check_point.member_cid) created_time = format(check_point.created_dt, "%Y%m%d") if created_time not in pass_quantity_time_dict: pass_quantity_time_dict[created_time] = { "{city}-{district}".format(city=city, district=district): 1 } else: city_district = "{city}-{district}".format( city=city, district=district) v_dict = pass_quantity_time_dict.get( created_time) if city_district in v_dict: v_dict[city_district] += 1 else: v_dict[city_district] = 1 else: continue except StopIteration: break except Exception as e: raise e # 每日新增人数在excel的位置 quantity_increase_position_list = [4 + 8 * (i - 1) for i in range(1, 8)] # 每日参与人数在excel的位置 quantity_enter_position_list = [5 + 8 * (i - 1) for i in range(1, 8)] # 每日参与次数在excel的位置 count_enter_position_list = [6 + 8 * (i - 1) for i in range(1, 8)] # 每日通关次数在excel的位置 pass_enter_position_list = [7 + 8 * (i - 1) for i in range(1, 8)] # 红包发放数量在excel的位置 red_give_position_list = [8 + 8 * (i - 1) for i in range(1, 8)] # 红包发放金额在excel的位置 red_give_amount_list = [10 + 8 * (i - 1) for i in range(1, 8)] # 红包领取数量在excel的位置 red_receive_position_list = [9 + 8 * (i - 1) for i in range(1, 8)] # 红包领取金额在excel的位置 red_receive_amount_list = [11 + 8 * (i - 1) for i in range(1, 8)] print(quantity_increase_position_list) print(dis_map, 'dis_map6') print(quantity, 'quan') print(quantity_time_dict, 'quantity_time') # 填充每日新增人数 write_excel_data(sheet, quantity_time_dict, dis_map, export_time, quantity_increase_position_list, save=None) # excel 填充每日参与人数 write_excel_data(sheet, enter_quantity_time_dict, dis_map, export_time, quantity_enter_position_list, save=None) # excel 填充每日参与次数 write_excel_data(sheet, count_time_dict, dis_map, export_time, count_enter_position_list, save=None) # excel 填充每日通关人数 write_excel_data(sheet, pass_quantity_time_dict, dis_map, export_time, pass_enter_position_list, save=None) red_give_dict = {} red_give_amount_dict = {} # 红包发放个数 red_give_out_match = { "race_cid": race_cid, 'record_flag': 1, "award_cid": { '$ne': None }, 'member_cid': { '$ne': None }, 'draw_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } } red_give_out_cursor = RedPacketBox.sync_aggregate([ MatchStage(red_give_out_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$draw_dt" } }, "member_cid": '$member_cid', "member_list": "$member_list", "award_amount": '$award_amount', }), MatchStage({'member_list': { '$ne': [] }}), GroupStage({"daily_code": "$daily_code"}, amount_list={'$push': '$award_amount'}, cid_list={'$push': '$member_cid'}, sum={'$sum': 1}) ]) while True: try: red_packet = red_give_out_cursor.next() if red_packet and red_packet.cid_list: cid_list = red_packet.cid_list amount_list = red_packet.amount_list print(len(amount_list), 'len_m') print(len(cid_list), 'len') daily = red_packet.id.get('daily_code') for cid, amount in zip(cid_list, amount_list): race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.district': { '$in': dist_list } }) if race_mapping: city = race_mapping.auth_address.get('city') district = race_mapping.auth_address.get('district') city_district = "{city}-{district}".format( city=city, district=district) if daily not in red_give_dict: red_give_dict[daily] = {city_district: 1} else: v_dict = red_give_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = 1 else: v_dict[city_district] += 1 if daily not in red_give_amount_dict: red_give_amount_dict[daily] = { city_district: amount } else: v_dict = red_give_amount_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = amount else: v_dict[city_district] += amount except StopIteration: break except Exception as e: raise e print(red_give_dict, 'dict2') print(red_give_amount_dict, 'amount-dict2') # excel 填充每日发放个数 write_excel_data(sheet, red_give_dict, dis_map, export_time, red_give_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_give_amount_dict, dis_map, export_time, red_give_amount_list, save=2) # 红包领取个数 red_receive_dict = {} red_receive_amount_dict = {} red_receive_match = { "race_cid": race_cid, 'record_flag': 1, "award_cid": { '$ne': None }, 'draw_status': 0, 'member_cid': { '$ne': None }, 'draw_dt': { '$gte': export_time_list[0].replace(hour=0, minute=0, second=0), '$lte': (export_time_list[-1] + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0) } } red_receive_cursor = RedPacketBox.sync_aggregate([ MatchStage(red_receive_match), lookup_stage, ProjectStage( **{ 'daily_code': { '$dateToString': { 'format': "%Y%m%d", 'date': "$draw_dt" } }, "member_cid": '$member_cid', "member_list": "$member_list", "award_amount": '$award_amount', }), MatchStage({'member_list': { '$ne': [] }}), GroupStage({"daily_code": "$daily_code"}, receive_amount_list={'$push': '$award_amount'}, cid_list={'$push': '$member_cid'}, sum={'$sum': 1}) ]) while True: try: red_packet = red_receive_cursor.next() if red_packet and red_packet.cid_list: amount_list = red_packet.receive_amount_list cid_list = list(set(red_packet.cid_list)) print(len(cid_list), 'len') daily = red_packet.id.get('daily_code') for cid, amount in zip(cid_list, amount_list): race_mapping = RaceMapping.sync_find_one({ 'race_cid': race_cid, 'member_cid': cid, 'auth_address.city': { '$in': city_name_list }, 'auth_address.district': { '$in': dist_list } }) if race_mapping: city = race_mapping.auth_address.get('city') district = race_mapping.auth_address.get('district') city_district = "{city}-{district}".format( city=city, district=district) if daily not in red_receive_dict: red_receive_dict[daily] = {city_district: 1} else: v_dict = red_receive_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = 1 else: v_dict[city_district] += 1 if daily not in red_receive_amount_dict: red_receive_amount_dict[daily] = { city_district: amount } else: v_dict = red_receive_amount_dict.get(daily) if city_district not in v_dict: v_dict[city_district] = amount else: v_dict[city_district] += amount except StopIteration: break except Exception as e: raise e print(red_receive_dict, 'receive_cursor') print(red_receive_amount_dict, 'rece_amount') # excel 填充每日领取个数 write_excel_data(sheet, red_receive_dict, dis_map, export_time, red_receive_position_list, save=None) # excel 填充每日领取金额 write_excel_data(sheet, red_receive_amount_dict, dis_map, export_time, red_receive_amount_list, save=2) workbook.close()
def __get_daily_code(dt): """ 获取对接标识 :return: """ return datetime2str(dt, date_format='%Y%m%d')