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')
