def single_race_endpoint(id):
"""GET end point to return a single race's information"""
auth = Auth(app)
if not auth.authenticate_request():
return auth.unauthorized_response()
race = Race()
single_race_query = race.get_race(id)
single_race_participants = RaceParticipants(
).get_race_participants_race_id(id)
if single_race_query:
json = []
snails_id_list = []
for row in single_race_participants:
snails_id_list.append(row.id_snail)
json.append({
"id": single_race_query.id,
"date": single_race_query.date,
"status": single_race_query.status,
"id_round": single_race_query.id_round,
"id_snails": snails_id_list
})
return json
return {
'status': 'Failed',
'message': 'Race not found'
}, status.HTTP_404_NOT_FOUND
def single_result_json(id):
race = Race()
race_participants = RaceParticipants()
race_results = RaceResult()
race_query = race.get_race(id)
if race_query:
json = []
snails_results_list = []
race_participants_by_id = race_participants.get_race_participants_race_id(
id)
for row in race_participants_by_id:
race_results_snail = race_results.get_race_result(row.id)
snails_results_list.append({
"id_snail": row.id_snail,
"position_snail": race_results_snail.position,
"time_snail": race_results_snail.time_to_finish,
"DNF": race_results_snail.did_not_finish
})
json.append({"id_race": int(id), "snails": snails_results_list})
return json
return {
'status': 'Failed',
'message': 'Results not found'
}, status.HTTP_404_NOT_FOUND
def rounds_endpoint():
"""GET end point to return rounds information"""
auth = Auth(app)
if not auth.authenticate_request():
return auth.unauthorized_response()
round_model = Round()
query_response = round_model.get_all_rounds()
if query_response:
json = []
for row in query_response:
race_model = Race()
race_ids = race_model.get_round_race_ids(row.id)
json.append({
"id": row.id,
"name": row.name,
"start_date": row.start_date,
"end_date": row.end_date,
"races": [race_id.id for race_id in race_ids]
})
return json
return {
'status': 'Failed',
'message': 'Rounds not found'
}, status.HTTP_404_NOT_FOUND
def races_endpoint():
"""GET end point to return races information"""
auth = Auth(app)
if not auth.authenticate_request():
return auth.unauthorized_response()
race = Race()
race_query = race.get_all_races()
all_race_participants = RaceParticipants()
if race_query:
json = []
for each_race in race_query:
race_participants = all_race_participants.get_race_participants_race_id(
each_race.id)
snails_id_list = []
for row in race_participants:
snails_id_list.append(row.id_snail)
json.append({
"id": each_race.id,
"date": each_race.date,
"status": each_race.status,
"id_round": each_race.id_round,
"id_snails": snails_id_list
})
return json
return {
'status': 'Failed',
'message': 'Races not found'
}, status.HTTP_404_NOT_FOUND
def adjust_race_report_accuracy(race_cid):
"""
活动报表线上数据填充正确率数据
:param race_cid:
:return:
"""
race = Race.sync_find_one({'cid': race_cid})
if not race:
print("活动不存在, 请检查！")
return
# 找到该活动下面所有的关卡
check_point_cid_list = RaceGameCheckPoint.sync_distinct("cid", {'race_cid': race_cid})
print(check_point_cid_list)
if not check_point_cid_list:
print("该活动下面没有关卡")
return
# 给race_mapping从历史记录统计正确率
try:
for check_point_cid in check_point_cid_list:
check_point_history_list = MemberCheckPointHistory.sync_find({"check_point_cid": check_point_cid}).to_list(
None)
for check_point_history in check_point_history_list:
race_mapping = RaceMapping.sync_find_one(
{'member_cid': check_point_history.member_cid, "race_cid": race_cid},
read_preference=ReadPreference.PRIMARY)
race_mapping.total_count += len(check_point_history.result)
num = 0
for result in check_point_history.result:
if result.get("true_answer"):
num += 1
race_mapping.total_correct += num
race_mapping.sync_save()
except Exception as e:
print(str(e))
def get_address(race_cid):
"""
获取活动的城市列表、区域列表,并缓存
:param race_cid:
:return:
"""
city_list_str_key = '%s_CITY_LIST_STR' % race_cid
district_list_str_key = '%s_DISTRICT_LIST_STR' % race_cid
city_name_list_str = RedisCache.get(city_list_str_key)
district_name_list_str = RedisCache.get(district_list_str_key)
if not city_name_list_str or not district_name_list_str:
# race_province_code,race_city_code缓存,若city_code不为空，则为市级活动
pro_code_key = '%s_province_code' % race_cid
city_code_key = '%s_city_code' % race_cid
province_code = RedisCache.get(pro_code_key)
city_code = RedisCache.get(city_code_key)
if not province_code or not city_code:
race = Race.sync_get_by_cid(race_cid)
RedisCache.set(pro_code_key, race.province_code, 12 * 60 * 60)
RedisCache.set(city_code_key, race.city_code, 12 * 60 * 60)
if city_code:
city_code_list = AdministrativeDivision.sync_distinct('code', {'code': city_code})
city_name_list = AdministrativeDivision.sync_distinct('title', {'code': city_code})
else:
city_code_list = AdministrativeDivision.sync_distinct('code', {'parent_code': province_code})
city_name_list = AdministrativeDivision.sync_distinct('title', {'parent_code': province_code})
district_name_list = AdministrativeDivision.sync_distinct('title',
{'parent_code': {'$in': city_code_list}})
RedisCache.set(','.join(city_name_list), 12 * 60 * 60)
RedisCache.set(','.join(district_name_list), 12 * 60 * 60)
else:
city_name_list = city_name_list_str.split(',')
district_name_list = district_name_list_str.split(',')
return city_name_list, district_name_list
def race(round_id, race_id):
if not current_user.is_authenticated:
return redirect(url_for('login.login'))
race_results_form = RaceResultsForm()
race = Race().get_race(race_id)
participants = RaceParticipants().get_participants_snails_race_id(race_id)
unresulted_participants = [
participant for participant, snail, result in participants
if not result
]
if race_results_form.validate_on_submit():
result_row = RaceResult().get_race_result(
race_results_form.id_race_participants.data)
if not result_row:
times = []
for participant, snail, result in participants:
time = RaceResult().get_time_to_finish(participant.id)
if time:
times.append(time[0])
time_to_finish = race_results_form.time_to_finish.data
new_times = calc_new_positions(times, time_to_finish)
new_result_position = update_existing_positions(
participants, new_times, time_to_finish)
db.session.add(
RaceResult(
position=new_result_position,
time_to_finish=race_results_form.time_to_finish.data,
did_not_finish=race_results_form.did_not_finish.data,
id_race_participants=race_results_form.
id_race_participants.data))
db.session.commit()
flash("Race Result recorded for Race Participant ID {}.".format(
race_results_form.id_race_participants.data))
else:
flash("Race Result Failed. Race Result already exists for Race "
"Participant ID {}.".format(
race_results_form.id_race_participants.data))
return redirect(
url_for('result.race', round_id=round_id, race_id=race_id))
elif race_results_form.errors.items():
flash("Form submission not valid. Please resubmit.")
return redirect(
url_for('result.race', round_id=round_id, race_id=race_id))
return render_template('results.html',
race=race,
participants=participants,
unresulted_participants=unresulted_participants,
race_results_form=race_results_form)
def add_race(round_id):
if not current_user.is_authenticated:
return redirect_to('login.login')
form = AddRaceForm()
round_ = Round().get_round(round_id)
if form.validate_on_submit():
races = Race().get_races_by_round(round_id)
if len(races) >= 5:
flash("Can't add race to round with 5 or more races.")
return redirect_to('rounds.rounds')
race_date, race_status = form.race_date.data, form.race_status.data
current_round = Round().get_round(round_id)
if parser.parse(race_date) < current_round.start_date or parser.parse(
race_date) > current_round.end_date:
flash("Can't add race that doesn't take place within round dates.")
return redirect_to('rounds.rounds')
for race in races:
if race.date == parser.parse(race_date):
flash("Two races cannot start at the same time.")
return redirect_to('rounds.rounds')
add_race_to_db(race_date, race_status, round_id)
return redirect_to('rounds.rounds')
return render_template('add_race.html', form=form, round_=round_)
def test2():
daily_code = '2019-07-08'
today = transform_date('20190708')
yesterday = today.replace(hour=0, minute=0, second=0, microsecond=0)
print(yesterday)
# 六安 CA755167DEA9AA89650D11C10FAA5413 贵州 F742E0C7CA5F7E175844478D74484C29
# 安徽 3040737C97F7C7669B04BC39A660065D
info = RaceStatisticInfo.sync_find_one(
{'race_cid': '3040737C97F7C7669B04BC39A660065D', 'daily_code': '2019-07-09'})
print("处理前总数", info.daily_code, '人数', info.total_member_count, '答题数', info.total_answer_times, '红包',
info.total_red_packet_count, '金额', info.total_red_packet_amount)
print("处理前新增", info.daily_code, '人数', info.add_member_count, '答题数', info.add_answer_times, '红包',
info.add_red_packet_count, '金额', info.add_red_packet_amount)
race = Race.sync_find_one({'cid': info.race_cid})
exportedMemberList = statistic_member_info(race.cid, race.province_code, yesterday)
member_count = len(exportedMemberList)
answer_times = 0
red_packet_count = 0
red_packet_amount = 0
for exported_member in exportedMemberList:
answer_times += exported_member.answerTimes
red_packet_count += exported_member.totalNumOfRedPacket
red_packet_amount += exported_member.totalAmountOfRedPacket
print("8号之前:",'人数',member_count,'答题数',answer_times,'红包',red_packet_count,'金额',red_packet_amount)
info.add_member_count = info.total_member_count - member_count
info.add_answer_times = info.total_answer_times - answer_times
info.add_red_packet_count = info.total_red_packet_count - red_packet_count
info.add_red_packet_amount = info.total_red_packet_amount - red_packet_amount
print("处理后总数", info.daily_code, '人数', info.total_member_count, '答题数', info.total_answer_times, '红包',
info.total_red_packet_count, '金额', info.total_red_packet_amount)
print("处理后新增", info.daily_code, '人数', info.add_member_count, '答题数', info.add_answer_times, '红包',
info.add_red_packet_count, '金额', info.add_red_packet_amount)
info.sync_save()
def generate_total(export_time):
race_cid_list = Race.sync_distinct('cid', {'status': 1})
for cid in race_cid_list:
if cid == '160631F26D00F7A2DC56DAE2A0C4AF12':
continue
RaceMemberEnterInfoStatistic.sync_delete_many(
{'race_cid': cid, 'daily_code': transform_time_format(export_time)})
generate_race_middle(cid, export_time)
def validate_snail_in_same_round(round_id, race_id, snail_id):
races_in_round = Race().get_races_by_round(round_id)
for race in races_in_round:
race_participants = RaceParticipants().get_race_participants_race_id(
race.id)
for snail in race_participants:
if int(snail.id_snail) == int(snail_id):
return True
return False
def export_race(race_cid):
today = datetime.datetime.now()
today_code = format(today, "%Y-%m-%d")
race = Race.sync_find_one({'cid': race_cid})
title = '{}_会员信息_{}.xlsx'.format(race.title, today_code)
workbook = xlsxwriter.Workbook(title)
export_member_to_excel(workbook, race.cid)
export_new_info(workbook, race.cid)
workbook.close()
return title
def check(race_cid: str):
"""
检查多少人参与活动但是没有答过题目的
:param race_cid:
:return:
"""
race = Race.sync_get_by_cid(race_cid)
# 市级活动，如六安市，扬州市
if race.city_code:
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'code': race.city_code})
# 该活动的所属城市范围
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'code': race.city_code})
else:
prov = AdministrativeDivision.sync_find_one(
{'code': race.province_code})
city_code_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
print(city_name_list, '1')
for city_code in city_code_list:
# 该活动区县的范围
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city_code})
quantity = 0
member_cid_list = RaceMapping.sync_distinct(
"member_cid", {
'race_cid': race_cid,
'auth_address.city': {
'$in': city_name_list
},
'auth_address.province': {
'$ne': None
},
'auth_address.district': {
'$in': dist_list
}
})
check_point_cid = RaceGameCheckPoint.sync_distinct("cid",
{"race_cid": race_cid})
for i in member_cid_list:
member = Member.sync_get_by_cid(i)
if member:
history = MemberCheckPointHistory.sync_find_one({
'check_point_cid': {
'$in': check_point_cid
},
"member_cid": i
})
if not history:
quantity += 1
print(quantity)
def races(round_id):
if not current_user.is_authenticated:
return redirect(url_for('login.login'))
races = Race().get_races_by_round(round_id)
current_round_toggle = validate_current_round_not_started(round_id)
return render_template('races.html',
races=races,
round_id=round_id,
current_round_toggle=current_round_toggle,
now=time_now())
def deal_history_data(race_cid):
"""
用于生成中间表处理历史数据(截止到昨天)
:return:
"""
race = Race.sync_find_one({'cid': race_cid})
daily_code_list = get_date_range(race.start_datetime)
print(daily_code_list)
clear_redis_cache(race_cid)
for daily_code in daily_code_list:
start_single_member_statistics.delay(race_cid, daily_code)
def deal_history_data_no_task(race_cid, daily_code_list):
"""
用于生成中间表处理历史数据(截止到昨天)
:return:
"""
race = Race.sync_find_one({'cid': race_cid})
if not daily_code_list:
daily_code_list = get_date_range(race.start_datetime)
print(daily_code_list)
clear_redis_cache(race_cid)
for daily_code in daily_code_list:
task_one_day(race_cid, daily_code)
def export_all_race_member():
today = datetime.now()
today_code = format(today, "%Y-%m-%d")
yesterday = today.replace(hour=0, minute=0, second=0, microsecond=0)
race_cursor = Race.sync_find()
for race in race_cursor:
print('Start', race.title)
workbook = xlsxwriter.Workbook('{}{}.xlsx'.format(
race.title, today_code))
export_member_to_excel(workbook, race.cid, race.title, today_code,
race.province_code, yesterday)
workbook.close()
def export_all_race_member():
today = datetime.now()
today_code = format(today, "%Y-%m-%d")
yesterday = today.replace(hour=0, minute=0, second=0, microsecond=0)
race_cursor = Race.sync_find({'status': 1})
for race in race_cursor:
# 六安 CA755167DEA9AA89650D11C10FAA5413 贵州 F742E0C7CA5F7E175844478D74484C29
# 安徽 3040737C97F7C7669B04BC39A660065D
if race.cid == '3040737C97F7C7669B04BC39A660065D':
print('Start', race.title)
workbook = xlsxwriter.Workbook('{}_会员信息_{}.xlsx'.format(race.title, today_code))
export_member_to_excel(workbook, race.cid, race.title, today_code, race.province_code, yesterday)
workbook.close()
def get_all_last_checkpoint():
"""
获取每个活动的最后一关
:return:
"""
_lp = {}
race_list = Race.sync_find().to_list(None)
for race in race_list:
checkpoint = RaceGameCheckPoint.sync_find({'race_cid': race.cid}).sort([('index', ASC)]).to_list(None)
if checkpoint:
_lp[race.cid] = checkpoint[-1].cid
return _lp
def start(daily_code_list=None, one_daily_code=None):
"""
统计会员信息，主方法
:param daily_code_list:
:param one_daily_code:
:return:
"""
race_cid_list = Race.sync_distinct('cid', {'status': 1})
for race_cid in race_cid_list:
logger.info('START RACE : %s' % race_cid)
if daily_code_list:
task_many_day(race_cid, daily_code_list)
else:
task_one_day(race_cid, one_daily_code)
logger.info('END RACE : %s' % race_cid)
def snails_endpoint(id):
"""Endpoint to get information for a round with specified id.
Retrieves the round information in json format for the specified id.
Args:
id (int): The id of the requested round.
Returns:
JSON: The json representation of the round with specified id.
"""
auth = Auth(app)
if not auth.authenticate_request():
return auth.unauthorized_response()
round_model = Round()
query_response = round_model.get_round(id)
if query_response:
race_model = Race()
race_ids = race_model.get_round_race_ids(query_response.id)
return {
"id": query_response.id,
"name": query_response.name,
"start_date": query_response.start_date,
"end_date": query_response.end_date,
"races": [race_id.id for race_id in race_ids]
}
return {
'status': 'Failed',
'message': 'Round id: %s not found' % id
}, status.HTTP_404_NOT_FOUND
def start_member_statistics_schedule(self):
"""
定时启动每日会员统计
:param self:
:return:
"""
mail_send_to = ['*****@*****.**', '*****@*****.**', '*****@*****.**',
'*****@*****.**', '*****@*****.**', '*****@*****.**']
copy_to = ['*****@*****.**']
task_start_dt = datetime.datetime.now()
pre_date = task_start_dt - datetime.timedelta(days=1)
daily_code = format(pre_date, '%Y%m%d')
logger.info('START(%s) at %s' % (self.request.id, task_start_dt))
try:
race_cid_list = Race.sync_distinct('cid', {'status': 1})
for race_cid in race_cid_list:
if race_cid == '160631F26D00F7A2DC56DAE2A0C4AF12':
continue
logger.info('START RACE : %s' % race_cid)
clear_redis_cache(race_cid)
task_one_day(race_cid, daily_code)
logger.info('END RACE : %s' % race_cid)
file_list = []
for race_cid in race_cid_list:
if race_cid == '160631F26D00F7A2DC56DAE2A0C4AF12':
continue
title = export_race(race_cid)
file_list.append(os.path.join(SITE_ROOT, title))
send_instant_mail(mail_to=mail_send_to, subject='会员信息%s' % daily_code, copy_to=copy_to,
content='上面的附件是截止到%s的会员导出数据,请注意查收！' % daily_code,
attachments=file_list)
# 中间表B表
generate_total(pre_date)
excel_title_list = export_race_one_week_excel()
logger.info(excel_title_list)
send_instant_mail(mail_to=mail_send_to, subject='一周数据', copy_to=copy_to,
content='上面的附件是正在举行的四个活动截止到%s的一周数据，请各位查收！' % daily_code,
attachments=excel_title_list)
except Exception:
logger.error(str(traceback.format_exc()))
task_end_dt = datetime.datetime.now()
logger.info('END(%s) at %s' % (self.request.id, task_end_dt))
logger.info('Cost Of Task: %s' % (task_end_dt - task_start_dt))
def export_race_one_week_excel():
excel_title_list = []
race_dict = {
"CA755167DEA9AA89650D11C10FAA5413": "六安市",
"3040737C97F7C7669B04BC39A660065D": "安徽省",
"F742E0C7CA5F7E175844478D74484C29": "贵州省",
"DF1EDC30F120AEE93351A005DC97B5C1": "扬州市"
}
race_list = Race.sync_find({'status': 1})
for race in race_list:
cid = race.cid
title = race.title
if race.cid == '160631F26D00F7A2DC56DAE2A0C4AF12':
continue
excel_name = export_race_enter_position(
cid, '{title}一周数据'.format(title=title), 8)
excel_title_list.append(os.path.join(SITE_ROOT, excel_name))
return excel_title_list
def get_city_and_district(race_cid):
"""
得到安徽下面的所有市和区
:param race_cid:
:return:
"""
lookup_stage = LookupStage(Member, 'member_cid', 'cid', 'member_list')
race = Race.sync_get_by_cid(race_cid)
city_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
for city in city_list:
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city})
return city_name_list, dist_list, lookup_stage
def write_sheet_enter_data(book,
race_cid,
sheet_name,
pre_match={},
count_type='$people_num'):
"""
总共参与人数/人次
:param book:
:param sheet_name:
:param race_cid
:param pre_match:
:param count_type:
:return:
"""
match = {'race_cid': race_cid}
if pre_match:
match.update(pre_match)
race = Race.sync_get_by_cid(race_cid)
match.update(get_region_match(race))
sheet = book.add_worksheet(sheet_name)
group_stage = GroupStage(
{
'daily_code': '$daily_code',
'district': '$district'
},
sum={'$sum': count_type},
city={'$first': '$city'})
daily_list = ReportRacePeopleStatistics.sync_distinct('daily_code', match)
daily_list.sort()
sheet.write_string(0, 0, '城市')
sheet.write_string(0, 1, '区县')
daily_map = {}
for index, daily_code in enumerate(daily_list):
sheet.write_string(0, index + 2, daily_code)
daily_map[daily_code] = index + 2
district_list = ReportRacePeopleStatistics.sync_distinct('district', match)
district_map = {}
for index, district in enumerate(district_list):
sheet.write_string(index + 1, 1, district if district else '其他')
district_map[district if district else '其他'] = index + 1
cursor = ReportRacePeopleStatistics.sync_aggregate(
[MatchStage(match), group_stage])
county_map = dict()
v_list = list()
_max_row = 0
while True:
try:
stat = cursor.next()
city = stat.city if stat.city else '其他'
district = stat.id.get('district')
daily_code = stat.id.get('daily_code')
_row = district_map.get(district if district else '其他')
sheet.write_string(_row, 0, city)
ad_city = AdministrativeDivision.sync_find_one({'title': city})
_county = AdministrativeDivision.sync_distinct(
'title', {'parent_code': ad_city.code})
for _c in _county:
county_map[_c] = city
sheet.write_number(_row, daily_map.get(daily_code), stat.sum)
if _row > _max_row:
_max_row = _row
v_list.append(stat.sum)
except StopIteration:
break
except Exception as e:
raise e
for k, v in county_map.items():
if k not in district_map:
_max_row += 1
sheet.write_string(_max_row, 0, v)
sheet.write_string(_max_row, 1, k)
sheet.write_string(_max_row + 1, 0, '总和')
sheet.write_number(_max_row + 1, 1, sum(v_list))
def add_race_to_db(race_date, race_status, round_id):
race = Race(date=race_date, status=race_status, id_round=round_id)
db.session.add(race)
db.session.commit()
def export_to_excel(race_cid):
"""
excel导出
:param race_cid:
:return:
"""
race = Race.sync_find_one({"cid": race_cid})
daily_codes = get_date_range(race.start_datetime)
race_awards = statistic_award_of_race(race_cid, daily_codes)
daily_code = format(datetime.datetime.now(), '%Y%m%d')
workbook = xlsxwriter.Workbook('{}关卡红包情况_{}.xlsx'.format(
race.title, daily_code))
sheet = workbook.add_worksheet(race.title)
# excel行名
second_row_num = 4
sheet.merge_range(0, 0, 1, 0, '关卡名')
for index, daily_code in enumerate(daily_codes):
start_col_index = index * second_row_num + 1
sheet.merge_range(0, start_col_index, 0,
start_col_index + second_row_num - 1, daily_code)
sheet.write_string(1, start_col_index, '参与人次')
sheet.write_string(1, start_col_index + 1, '抽奖人数')
sheet.write_string(1, start_col_index + 2, '中奖人数')
sheet.write_string(1, start_col_index + 3, '中奖率')
if index == len(daily_codes) - 1:
total_col_index = start_col_index + 3
sheet.merge_range(0, total_col_index, 0,
total_col_index + second_row_num - 1, "总计")
sheet.write_string(1, total_col_index, '参与人次')
sheet.write_string(1, total_col_index + 1, '抽奖人数')
sheet.write_string(1, total_col_index + 2, '中奖人数')
sheet.write_string(1, start_col_index + 3, '中奖率')
for row_index, check_point_award in enumerate(race_awards):
name = check_point_award.check_point.alias
daily_awards = check_point_award.award_static.daily_awards
row_num = row_index + 2
sheet.write_string(row_num, 0, name)
if daily_awards and len(daily_awards) > 0:
for col_index, daily_award in enumerate(daily_awards):
col_num = col_index * second_row_num + 1
sheet.write_number(row_num, col_num, daily_award.attend_count)
sheet.write_number(row_num, col_num + 1,
daily_award.lottery_count)
sheet.write_number(row_num, col_num + 2, daily_award.win_count)
sheet.write_string(row_num, col_num + 3, daily_award.win_rate)
if col_index == len(daily_awards) - 1:
sheet.write_number(
row_num, col_num + 3,
check_point_award.award_static.attend_total_count)
sheet.write_number(
row_num, col_num + 4,
check_point_award.award_static.lottery_total_count)
sheet.write_number(
row_num, col_num + 5,
check_point_award.award_static.win_total_count)
sheet.write_string(
row_num, col_num + 6,
check_point_award.award_static.total_win_rate)
_last_row = len(race_awards) + 2
_last_col = (len(daily_codes) + 1) * 3
sheet.write_string(_last_row, 0, '总计')
for i in range(1, _last_col + 1):
col_name = convert(i)
formula = "=SUM({}{}:{}{})".format(col_name, 3, col_name, _last_row)
sheet.write_formula(_last_row, i, formula)
workbook.close()
def do_query(race_cid):
cursor = RedPacketBox.sync_find({
'member_cid': {
'$ne': None
},
'award_cid': {
'$ne': None
},
'draw_status': 1,
'race_cid': race_cid
}).batch_size(128)
index = 0
while True:
try:
box: RedPacketBox = cursor.next()
race = Race.sync_get_by_cid(box.race_cid)
rid = generate_rid(box.race_cid, box.member_cid, box.cid)
json_data = json.dumps(
deal_param(REDPACKET_PLATFORM_QUERY_RESULT_URL,
redpkt_account=race.redpkt_account,
rid=rid))
res = requests.get(REDPACKET_PLATFORM_HOST +
REDPACKET_PLATFORM_QUERY_RESULT_URL,
data=json_data)
res = res.json()
if res.get('data'):
status = res.get('data')[0].get('status')
else:
print('member has not click pick_url')
continue
if status == 2:
print('[RedPacket Drawing] the redpacket info: '
'member_cid: %s, checkpoint_cid: %s.' %
(box.member_cid, box.checkpoint_cid))
if status == 3:
box.draw_status = STATUS_REDPACKET_AWARDED
box.issue_status = STATUS_READPACKET_ISSUE_SUCCESS
box.sync_save()
notice_list = MemberNotice.sync_find({
'member_cid': box.member_cid,
'category': CATEGORY_NOTICE_AWARD,
'status': STATUS_NOTICE_UNREAD,
'checkpoint_cid': box.checkpoint_cid,
'record_flag': 1
}).to_list(None)
for notice in notice_list:
notice.status = STATUS_NOTICE_READ
notice.sync_save()
print(
'[RedPacket Drew] the redpacket info: member_cid: %s, checkpoint_cid: %s.'
% (box.member_cid, box.checkpoint_cid))
break
if status == 5:
box.status = STATUS_REDPACKET_AWARD_FAILED
box.issue_status = STATUS_READPACKET_ISSUE_FAIL
box.sync_save()
break
if status == 6:
logger.warning(
'[RedPacket Waiting] the redpacket info: member_cid: %s, checkpoint_cid: %s.'
% (box.member_cid, box.checkpoint_cid))
if status == 8:
pass
index += 1
print('has query', index)
except StopIteration:
break
def export_race_data(workbook, race_cid: str, sheet_name):
"""
导出每日参与人数，每日新增人数，每日新增人次
:param race_cid:
:return:
"""
# yesterday_time = get_yesterday()
# time_match = MatchStage({'updated_dt': {'$lt': yesterday_time}})
race = Race.sync_get_by_cid(race_cid)
city_list = AdministrativeDivision.sync_distinct(
'code', {'parent_code': race.province_code})
city_name_list = AdministrativeDivision.sync_distinct(
'title', {'parent_code': race.province_code})
dist_list = []
for city in city_list:
dist_list += AdministrativeDivision.sync_distinct(
'title', {'parent_code': city})
match = {
'race_cid': race_cid,
'auth_address.province': {
'$ne': None
},
'auth_address.city': {
"$in": city_name_list
},
'auth_address.district': {
"$in": dist_list
}
}
sheet = workbook.add_worksheet(sheet_name)
cursor = RaceMapping.sync_aggregate([
MatchStage(match),
ProjectStage(
**{
'daily_code': {
'$dateToString': {
'format': "%Y%m%d",
'date': "$created_dt"
}
},
'auth_address': "$auth_address",
'member_cid': "$member_cid"
}),
GroupStage(
{
'daily_code': '$daily_code',
'district': '$auth_address.district'
},
sum={'$sum': 1},
auth_address={'$first': '$auth_address'}),
SortStage([('_id.daily_code', ASC)])
])
sheet.write_string(0, 0, '城市')
sheet.write_string(0, 1, '区县')
daily_list = []
prize_list = []
county_map = {}
v_list = list()
_max_row = 0
while True:
try:
data = cursor.next()
_current_row = None
title = data.id.get('district')
if title is None:
title = '未知'
if title not in prize_list:
prize_list.append(title)
_current_row = len(prize_list)
city = data.auth_address.get('city')
if not city:
continue
sheet.write_string(_current_row, 0, city)
ad_city = AdministrativeDivision.sync_find_one({'title': city})
_county = AdministrativeDivision.sync_distinct(
'title', {'parent_code': ad_city.code})
for _c in _county:
county_map[_c] = city
sheet.write_string(_current_row, 1, title)
else:
_current_row = prize_list.index(title) + 1
daily_code = data.id.get('daily_code')
if not daily_code:
daily_code = '未知'
if daily_code not in daily_list:
daily_list.append(daily_code)
_current_col = len(daily_list) + 1
sheet.write_string(0, _current_col, daily_code)
else:
_current_col = daily_list.index(daily_code) + 2
sheet.write_number(_current_row, _current_col, data.sum)
v_list.append(data.sum)
if _current_row >= _max_row:
_max_row = _current_row
except StopIteration:
break
except Exception as e:
raise e
for k, v in county_map.items():
if k not in prize_list:
_max_row += 1
sheet.write_string(_max_row, 0, v)
sheet.write_string(_max_row, 1, k)
if _max_row:
sheet.write_string(_max_row + 1, 0, '总和')
sheet.write_number(_max_row + 1, 1, sum(v_list))
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()