from handlers.HandlerBase import BaseHandler
import time, json
from model.DataBaseUtils import *
from model.CommonUtils import *
from model import UserAuthUtils
from model.UserAuthUtils import super_auth, get_auth_game_info
from model.DateUtils import DateUtils
from datetime import datetime, timedelta
class OrderInfoGame(BaseHandler):
def get(self):
if not self._au:
self.write_fail(msg='auth error')
else:
sql = f'''
select min(id),name from GameDataSum.h_game_sum hgs
group by name
'''
ck = CkUtils()
ck.execute(sql)
data_res = ck.execute(sql)
data_res_dict = []
for _ in data_res:
tmp_dict = {'id': _[0],
'name': _[1]
}
data_res_dict.append(tmp_dict)
self.write_json(data=data_res_dict)
class OrderInfoAgent(BaseHandler):
def post(self):
if not self._au:
self.write_fail(msg='auth error')
else:
args = self.get_args()
user_id = args.get('user_id')
if user_id in super_auth():
op = ''
else:
if len(UserAuthUtils.get_auth_channel(user_id)) == 0:
return None, None, None
elif len(UserAuthUtils.get_auth_channel(user_id)) == 1:
op = f" and b.wx_name = '{UserAuthUtils.get_auth_channel(user_id)[0]}'"
else:
op = f" and b.wx_name in {str(UserAuthUtils.get_auth_channel(user_id))}"
sql = f'''
select rowNumberInAllBlocks() id,
d.user_nicename as agent_name
from CostSourceData.advertiser_vx a
left join GameDataSum.mp_mp_conf_sum b on a.name =b.wx_name
left join GameDataSum.mp_conf_agent_sum c on b.id = c.advertiser_conf_id
left join GameDataSum.h_user_sum d on c.agent_id = d.id
where d.user_nicename is not null
{op}
group by d.user_nicename
'''
ck = CkUtils()
data_res = ck.execute(sql)
data_res_dict = []
for _ in data_res:
tmp_dict = {'id': _[0],
'agent_name': _[1]
}
data_res_dict.append(tmp_dict)
data_res_dict.insert(0, {'id': -1, 'agent_name': '自然流量渠道'})
data_res_dict.insert(0, {'id': -2, 'agent_name': '客服渠道'})
self.write_json(data=data_res_dict)
class OrderInfoNobody(BaseHandler):
def post(self):
if not self._au:
self.write_fail(msg='auth error')
else:
args = self.get_args()
user_id = args.get('user_id')
start = args.get("start", self.thisday)
end = args.get("end", self.thisday)
game = args.get('game')
page = args.get("page")
page_size = args.get("page_size")
db = MysqlUtils()
op = ''
is_over = False
if user_id in super_auth():
op = ''
else:
if len(UserAuthUtils.get_auth_game_name(user_id)) == 0:
self.write_json(None)
is_over = True
else:
game_name_list = UserAuthUtils.get_auth_game_name(user_id)
game_name_list = [_[0] for _ in game_name_list if _[0]]
game_str = "'" + "','".join(game_name_list) + "'"
op = f" and name in ({game_str}) "
if not is_over:
op1 = f''' and dt >= '{start}' and dt <= '{end}' '''
op2 = ''
if game:
game_str = "'" + "','".join(game) + "'"
op2 = f" and name in ({game_str}) "
limit_start = (page - 1) * page_size
sql = f'''
select dt,name game,
sum(if(agent='自然流量',sum_amount,0)) as nobody_order,
sum(if(agent='买量流量',sum_amount,0)) as body_order,
sum(if(agent='当日总量',sum_amount,0)) as all_order
from
(select sum(amount) sum_amount,toDate(FROM_UNIXTIME(a.create_time)) dt,
b.name ,if(a.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0'),'自然流量','买量流量') as agent
from GameDataSum.h_pay_sum a
left join GameDataSum.h_game_sum b on a.app_id = b.id
where a.status = 2 {op}
group by toDate(FROM_UNIXTIME(a.create_time)) ,b.name,
if(a.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0') ,'自然流量','买量流量')
union all
select sum(amount) sum_amount,toDate(FROM_UNIXTIME(a.create_time)) dt,
b.name ,'当日总量' as agent
from GameDataSum.h_pay_sum a
left join GameDataSum.h_game_sum b on a.app_id = b.id
where a.status = 2 {op}
group by b.name ,toDate(FROM_UNIXTIME(a.create_time))) a
where 1=1 {op1} {op2}
group by dt,name
order by dt desc
limit {limit_start},{page_size}
'''
print(sql)
ck = CkUtils()
data_res = ck.execute(sql)
data_res_dict = []
for _ in data_res:
tmp_dict = {'dt': _[0].strftime('%Y-%m-%d'),
'game': _[1],
'nobody_order': float(_[2]),
'body_order': float(_[3]),
'all_order': float(_[4])
}
data_res_dict.append(tmp_dict)
data = data_res_dict
if args.get("download"):
self.write_download(str(int(time.time())), data)
else:
self.write_json(data=data)
class OrderInfo(BaseHandler):
def post(self):
if not self._au:
self.write_fail(msg='auth error')
else:
args = self.get_args()
user_id = args.get('user_id')
# 渠道表
channel = args.get("channel")
pitcher = args.get("pitcher")
# role 表
role_name = args.get("role_name")
start = args.get("start")
if args.get("end"):
tmp_end = args.get("end")
tmp_end = datetime.strptime(tmp_end, '%Y-%m-%d')
tmp_end = tmp_end + timedelta(days=1)
end = tmp_end.strftime('%Y-%m-%d')
else:
end = None
user_start = args.get("user_start")
if args.get("user_end"):
tmp_end = args.get("user_end")
tmp_end = datetime.strptime(tmp_end, '%Y-%m-%d')
tmp_end = tmp_end + timedelta(days=1)
user_end = tmp_end.strftime('%Y-%m-%d')
else:
user_end = None
if not start and not user_start:
start = self.thisday
# start end 转timestamp
start = None if not start else int(datetime.strptime(start, '%Y-%m-%d').timestamp())
end = None if not end else int(datetime.strptime(end, '%Y-%m-%d').timestamp())
user_start = None if not user_start else int(datetime.strptime(user_start, '%Y-%m-%d').timestamp())
user_end = None if not user_end else int(datetime.strptime(user_end, '%Y-%m-%d').timestamp())
order_id = args.get('order_id')
member_id = args.get('member_id')
status = (args.get('status'))
game = (args.get('game'))
agent_name = (args.get('agent_name'))
page = args.get("page", 1)
page_size = args.get("page_size", 20)
order_by = args.get("order_by", 'order_date')
order_ = args.get("order", 'desc')
data, total, total_data = self.get_order_info(user_id, channel, pitcher, role_name, start, end,
user_end, user_start, order_id, member_id, status,
game, agent_name,
page, page_size, order_, order_by)
if args.get("download"):
data, total, total_data = self.get_order_info(user_id, channel, pitcher, role_name, start, end,
user_end, user_start, order_id, member_id, status,
game, agent_name,
1, 100000000, order_, order_by)
self.write_download(str(int(time.time())), data)
else:
self.write_json(data=data, total=total, total_data=total_data)
def get_order_info(self, user_id, channel, pitcher, role_name, start, end,
user_end, user_start, order_id, member_id, status,
game, agent_name,
page, page_size, order_, order_by):
db = MysqlUtils()
# todo：订单之后写个专门的clickhouse订单表,clickhouse 一小时同步一次
if user_id in super_auth():
op_channel = ''
op_game = ''
else:
op_game = ''
op_base = f' and if( h.start_date is not null,d.create_time >= toUnixTimestamp(toDateTime(start_date)),1) '
# 限制为自己游戏的1.自然流量，2.客服流量
if agent_name and ('自然流量渠道' in agent_name or '客服渠道' in agent_name):
game_ids = get_auth_game_info(user_id)
print(game_ids)
op_game = ''
for game_id, game_name, game_timestamp in game_ids:
op_game = op_game + f'''or (e.id = '{game_id}' and a.create_time >{game_timestamp} ) '''
op_game = ' and ' + '(' + op_game[2:] + ')' + op_base
# 限制为自己公众号开始时候的数据
if len(UserAuthUtils.get_auth_channel(user_id)) == 0:
return None, None, None
elif len(UserAuthUtils.get_auth_channel(user_id)) == 1:
op_channel = f''' and c.wx_name = '{UserAuthUtils.get_auth_channel(user_id)[0]}'
''' + op_base
else:
op_channel = f'''
and c.wx_name in {str(UserAuthUtils.get_auth_channel(user_id))}
''' + op_base
op = op_game if agent_name and ('自然流量渠道' in agent_name or '客服渠道' in agent_name) else op_channel
op1 = f" and pitcher ='{pitcher}' " if pitcher else ''
op2 = f''' and wx_name in ({"'" + "','".join(channel) + "'"})''' if channel else ''
op3 = f" and create_time >{start} " if start else ''
op4 = f" and create_time <{end} " if end else ''
op5 = f" and role_name ='{role_name}' " if role_name else ''
op6 = f" and create_time >{user_start} " if user_start else ''
op7 = f" and create_time <{user_end} " if user_end else ''
op8 = f" and order_id like '%_{order_id}'" if order_id else ''
op9 = f" and mem_id like '%_{member_id}'" if member_id else ''
op10 = f" and status={status} " if status else ''
op11 = f''' where name in ({"'" + "','".join(game) + "'"}) ''' if game else ''
op12_pay = ''
op12_user = ''
# 有自然流量和客服渠道时，其他渠道进行规避
if agent_name and '自然流量渠道' in agent_name and '客服渠道' in agent_name:
op12_pay = f" and agent_id in ('sdk_one_12','sdk_two_12','sdk_three_12','sdk_one_0','sdk_two_0','sdk_three_0') "
elif agent_name and '自然流量渠道' in agent_name:
op12_pay = f" and agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0') "
elif agent_name and '客服渠道' in agent_name:
op12_pay = f" and agent_id in ('sdk_one_12','sdk_two_12','sdk_three_12') "
else:
op12_user = f''' where user_nicename in ({"'" + "','".join(agent_name) + "'"}) ''' if agent_name else ''
# 选项:order_date：订单时间,user_date：用户时间,amount：金额,game：游戏名字,payway：支付方式，agent_name:渠道
if order_by == 'agent_name':
order_by = 'q.user_nicename'
elif order_by == 'user_date':
order_by = 'd.create_time'
elif order_by == 'amount':
order_by = 'amount'
elif order_by == 'game':
order_by = 'e.name'
elif order_by == 'payway':
order_by = 'a.payway'
else:
order_by = 'a.create_time'
op15 = f" order by {order_by} {order_}" if order_by and order_ else ''
sql = f"""select
rowNumberInAllBlocks() as id,
a.order_id order_id,
if(a.agent_id like '%_21','',if(a.agent_id like '%_0','',h.pitcher)) as pitcher,
if(a.agent_id like '%_21','客服渠道',if(a.agent_id like '%_0','自然流量',q.user_nicename)) as agent_name,
if(a.agent_id like '%_21','客服渠道',if(a.agent_id like '%_0','自然流量',c.wx_name)) as channel,
FROM_UNIXTIME(a.create_time) order_time,
FROM_UNIXTIME(d.create_time) user_create_time,
d.id as mem_id,
e.name as game ,
a.amount amount,
a.real_amount real_amount,
g.server_name server_name,
f.os as system_os,
g.role_name role_name,
a.payway payway,a.status status ,a.mg_mem_id as user_id
from (
select * from GameDataSum.h_pay_sum
where 1=1 {op3} {op4} {op8} {op9} {op10} {op12_pay}
) a
left join
(select b.agent_id as agent_id,b.advertiser_conf_id from
(select agent_id,max(update_time) as update_time
from GameDataSum.mp_conf_agent_sum mmc
group by agent_id ) a
left join GameDataSum.mp_conf_agent_sum b on a.agent_id=b.agent_id
and a.update_time = b.update_time) b on a.agent_id =b.agent_id
left join
(select id,wx_name from GameDataSum.mp_mp_conf_sum
where 1=1 {op2} ) c on b.advertiser_conf_id = c.id
left join (select id,create_time from GameDataSum.h_member_sum
where 1=1 {op6} {op7} ) d on a.mem_id = d.id
left join ( select * from GameDataSum.h_game_sum {op11} ) e on a.app_id =e.id
left join GameDataSum.h_pay_ext_sum f on a.id = f.pay_id
left join ( select * from GameDataSum.h_mg_role_sum where 1=1 {op5} ) g
on g.role_id = f.role_id and g.server_id = f.server_id
and g.app_id = a.app_id and g.mg_mem_id =a.mg_mem_id
left join (select name,pitcher ,start_date
from CostSourceData.advertiser_vx where 1=1 {op1}
) h on c.wx_name = h.name
left join (select * from GameDataSum.h_user_sum {op12_user} ) q on a.agent_id =q.id
where 1=1
and mem_id is not null and game is not null
{op} {op1} {op5}
{op15}
"""
limit_start = (page - 1) * page_size
limit_sql = sql + '\n' + f''' limit {limit_start},{page_size} '''
sumsql = f""" select '' as id,'' as order_id ,'' as pitcher,'' as agent_name, '' as channel,
'' as order_time,'' as user_create_time,'' as user_id,
'' as game,sum(amount) as amount ,sum(real_amount) as real_amount ,'' as server_name,
'' as mem_id , '' as role_name,'' as payway ,'' as status ,'' as system_os
from ({sql}) a
"""
totalsql = f'''
select count(1) from ({sql}) a
'''
print(sumsql)
print(totalsql)
ck = CkUtils()
print(limit_sql)
data_res = ck.execute(limit_sql)
data_res_dict = []
data_sum_res = ck.execute(sumsql)
data_sum_res_dict = []
data_total = ck.execute(totalsql)[0][0]
for _ in data_res:
tmp_dict = {'id': _[0],
'order_id': _[1],
'pitcher': _[2],
'agent_name': _[3],
'channel': _[4],
'order_time': datetime.strftime(_[5], '%Y-%m-%d %H:%M:%S'),
'user_create_time': datetime.strftime(_[6], '%Y-%m-%d %H:%M:%S'),
'mem_id': _[7],
'game': _[8],
'amount': float(_[9]),
'real_amount': float(_[10]),
'server_name': _[11],
'system_os': _[12],
'role_name': _[13],
'payway': _[14],
'status': _[15],
'user_id': _[16],
}
data_res_dict.append(tmp_dict)
for _ in data_sum_res:
tmp_dict = {'id': _[0],
'order_id': _[1],
'pitcher': _[2],
'agent_name': _[3],
'channel': _[4],
'order_time': '',
'user_create_time': '',
'mem_id': _[7],
'game': _[8],
'amount': float(_[9]),
'real_amount': float(_[10]),
'server_name': _[11],
'system_os': _[12],
'role_name': _[13],
'payway': _[14],
'status': _[15],
'user_id': _[16],
}
data_sum_res_dict.append(tmp_dict)
data, total, total_data = data_res_dict, data_total, data_sum_res_dict[0]
return data, total, total_data
class OrderH5Info(BaseHandler):
def post(self):
if not self._au:
self.write_fail(msg='auth error')
else:
args = self.get_args()
user_id = args.get('user_id')
# role 表
start = args.get("start", self.thisday)
end = args.get("end")
if args.get("download"):
data = self.get_order_info_download(user_id, start, end)
self.write_download(str(int(time.time())), data)
else:
data = self.get_order_info(user_id, start, end)
self.write_json(data=data)
def get_order_info(self, user_id, start, end):
ck = CkUtils()
du = DateUtils()
if user_id not in super_auth():
return []
# todo: 定时跑数据汇总也还没有写好，写好
# start end 转变为Unix_time
end = du.getLastDays(end, -1) if end else None # end 往后面一天
start = None if not start else int(datetime.strptime(start, '%Y-%m-%d').timestamp())
end = None if not end else int(datetime.strptime(end, '%Y-%m-%d').timestamp())
op1 = f" and create_time>={start} " if start else ''
op2 = f" and create_time<={end} " if end else ''
sql = f'''
select a.dt,a.h5_game_name,
b.sum_amount as sum_amount,
b.natural_amount as natural_amount,
b.amount_rate as amount_rate,
b.human_count as human_count,
b.natural_human_count as natural_human_count,
b.human_count_rate as human_count_rate,
a.channel_game_name as channel_game_name,
a.is_natural as is_natural,
toFloat64(a.sum_amount) as channel_amount,
toFloat64(a.human_counts) as channel_human_count
from
(select toDate(FROM_UNIXTIME(a.create_time)) dt,a.app_id as app_id ,
min(b.name) as h5_game_name,
min(c.app_id) as c_app_id,d.name as channel_game_name,
if(min(c.agent_id) in ('sdk_one_0','sdk_two_0','sdk_three_0'),1,0) as is_natural ,
sum(amount) as sum_amount,count(DISTINCT(a.mem_id)) as human_counts
from (select * from GameDataSum.h_pay_sum where 1=1 {op1} {op2} ) a
LEFT join GameDataSum.h_game_sum b on a.app_id = b.id
left join (select * from GameDataSum.h_member_sum where 1=1 {op2} ) c on a.mem_id = c.id
LEFT join GameDataSum.h_game_sum d on c.app_id = d.id
where a.app_id in (select id from GameDataSum.h_game_sum
where classify =5)
and a.app_id != c.app_id
and c.id is not null
and a.status = 2
group by toDate(FROM_UNIXTIME(a.create_time)),a.app_id ,
c.app_id ,d.name,if(c.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0'),1,0) ) as a
left join
(select toDate(FROM_UNIXTIME(a.create_time)) as dt,a.app_id ,
sum(a.amount) sum_amount,
sum(if(a.app_id=c.app_id,a.amount,0)) natural_amount,
count(DISTINCT(a.mem_id)) as human_count,
count(DISTINCT(if(a.app_id=c.app_id,a.mem_id,null))) natural_human_count,
ROUND(if(sum(a.amount)>0,sum(if(a.app_id=c.app_id,a.amount,0)) /sum(a.amount),0) ,2) amount_rate,
ROUND( if(count(DISTINCT(a.mem_id))>0,count(DISTINCT(if(a.app_id=c.app_id,a.mem_id,null)))/count(DISTINCT(a.mem_id)),0),2) human_count_rate
from (select * from GameDataSum.h_pay_sum where 1=1 {op1} {op2} ) a
left join (select * from GameDataSum.h_member_sum where 1=1 {op2} ) c on a.mem_id = c.id
where a.app_id in (select id from GameDataSum.h_game_sum
where classify =5)
and a.status=2
and c.id is not null
group by toDate(FROM_UNIXTIME(a.create_time)),a.app_id ) b
on a.dt=b.dt and a.app_id = b.app_id
order by a.dt desc,a.app_id,c_app_id
'''
print(sql)
df_source = ck.execute(sql)
game_info = {}
for _ in df_source:
_ = list(_)
_[0] = datetime.strftime(_[0], '%Y-%m-%d')
_[2] = float(_[2])
_[3] = float(_[3])
_[4] = float(_[4])
_[7] = float(_[7])
_[10] = float(_[10])
h_game = (_[0], _[1], _[2], _[3], _[4], _[5], _[6], _[7])
if h_game in game_info.keys():
game_info[h_game].append(_)
else:
game_info[h_game] = [_]
res_game_info = []
for k, v in game_info.items():
# print(k, v)
channel_game_list_natural = []
channel_game_list_unnatural = []
for _ in v:
if _[9]:
channel_game_list_natural.append({'channel_game_name': _[8],
'channel_game_amount': _[10],
'channel_game_human_count': _[11]})
else:
channel_game_list_unnatural.append({'channel_game_name': _[8],
'channel_game_amount': _[10],
'channel_game_human_count': _[11]})
if len(channel_game_list_natural) != len(channel_game_list_unnatural):
game_name_natural = set()
game_name_unnatural = set()
for _ in channel_game_list_natural:
game_name_natural.add(_['channel_game_name'])
for _ in channel_game_list_unnatural:
game_name_unnatural.add(_['channel_game_name'])
for channel_game_name in game_name_unnatural - game_name_natural:
channel_game_list_natural.append({'channel_game_name': channel_game_name,
'channel_game_amount': 0,
'channel_game_human_count': 0})
for channel_game_name in game_name_natural - game_name_unnatural:
channel_game_list_unnatural.append({'channel_game_name': channel_game_name,
'channel_game_amount': 0,
'channel_game_human_count': 0})
channel_game_list_unnatural.sort(key=lambda x: str(x['channel_game_name']))
channel_game_list_natural.sort(key=lambda x: str(x['channel_game_name']))
tmp = {'dt': k[0], 'game_name': k[1], 'sum_amount': k[2], 'natural_amount': k[3],
'natural_amount_rate': k[4], 'human_count': k[5], 'natural_human_count': k[6],
'natural_human_count_rate': k[7], 'channel_game_natural_list': channel_game_list_natural,
'channel_game_unnatural_list': channel_game_list_unnatural
}
res_game_info.append(tmp)
return res_game_info
def get_order_info_download(self, user_id, start, end):
if user_id not in super_auth():
return []
ck = CkUtils()
du = DateUtils()
# start end 转变为Unix_time
end = du.getLastDays(end, -1) if end else None # end 往后面一天
start = None if not start else int(datetime.strptime(start, '%Y-%m-%d').timestamp())
end = None if not end else int(datetime.strptime(end, '%Y-%m-%d').timestamp())
op1 = f" and create_time>={start} " if start else ''
op2 = f" and create_time<={end} " if end else ''
sql = f'''
select a.dt dt, --日期
a.h5_game_name h5_game_name, --游戏名称H5,
b.sum_amount sum_amount_today,--as 今日总充值,
b.natural_amount natural_amount,-- as H5自然量充值,
b.amount_rate amount_rate, -- as 充值额比例,
b.human_count human_count, -- as 今日总充值人数,
b.natural_human_count natural_human_count, -- H5自然量人数,
b.human_count_rate human_count_rate, -- as 用户比例,
a.channel_game_name channel_game_name, -- as 游戏名称小程序,
a.is_natural is_natural, -- as 小程序的量是否为自然量,
a.sum_amount sum_amount_small_app, -- as 小程序导入用户充值,
a.human_counts human_counts -- as 小程序导入人数
from
(select toDate(FROM_UNIXTIME(a.create_time)) dt,a.app_id as app_id ,
min(b.name) as h5_game_name,
min(c.app_id) as c_app_id,d.name as channel_game_name,
if(min(c.agent_id) in ('sdk_one_0','sdk_two_0','sdk_three_0'),1,0) as is_natural ,
sum(amount) as sum_amount,count(DISTINCT(a.mem_id)) as human_counts
from (select * from GameDataSum.h_pay_sum where 1=1 {op1} {op2} ) a
LEFT join GameDataSum.h_game_sum b on a.app_id = b.id
left join (select * from GameDataSum.h_member_sum where 1=1 {op2} ) c on a.mem_id = c.id
LEFT join GameDataSum.h_game_sum d on c.app_id = d.id
where a.app_id in (select id from GameDataSum.h_game_sum
where classify =5)
and c.id is not null
and a.app_id != c.app_id
and a.status = 2
group by toDate(FROM_UNIXTIME(a.create_time)),a.app_id ,
c.app_id ,d.name,if(c.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0'),1,0) ) as a
left join
(select toDate(FROM_UNIXTIME(a.create_time)) as dt,a.app_id ,
sum(a.amount) sum_amount,
sum(if(a.app_id=c.app_id,a.amount,0)) natural_amount,
count(DISTINCT(a.mem_id)) as human_count,
count(DISTINCT(if(a.app_id=c.app_id,a.mem_id,null))) natural_human_count,
ROUND(if(sum(a.amount)>0,sum(if(a.app_id=c.app_id,a.amount,0)) /sum(a.amount),0) ,2) amount_rate,
ROUND( if(count(DISTINCT(a.mem_id))>0,count(DISTINCT(if(a.app_id=c.app_id,a.mem_id,null)))/count(DISTINCT(a.mem_id)),0),2) human_count_rate
from (select * from GameDataSum.h_pay_sum where 1=1 {op1} {op2} ) a
left join (select * from GameDataSum.h_member_sum where 1=1 {op2} ) c on a.mem_id = c.id
where a.app_id in (select id from GameDataSum.h_game_sum
where classify =5)
and c.id is not null
and a.status=2
group by toDate(FROM_UNIXTIME(a.create_time)),a.app_id ) b
on a.dt=b.dt and a.app_id = b.app_id
order by a.dt desc,a.app_id,c_app_id
'''
print(sql)
data = ck.execute(sql)
data_res = []
for _ in data:
tmp = {}
tmp['日期'] = _[0]
tmp['游戏名称H5'] = _[1]
tmp['今日总充值'] = _[2]
tmp['H5自然量充值'] = _[3]
tmp['充值额比例'] = _[4]
tmp['今日总充值人数'] = _[5]
tmp['H5自然量人数'] = _[6]
tmp['用户比例'] = _[7]
tmp['游戏名称小程序'] = _[8]
tmp['小程序的量是否为自然量'] = _[9]
tmp['小程序导入用户充值'] = _[10]
tmp['小程序导入人数'] = _[11]
data_res.append(tmp)
return data_res
class OrderH5InfoSpecial(BaseHandler):
def post(self):
if not self._au:
self.write_fail(msg='auth error')
else:
args = self.get_args()
user_id = args.get('user_id')
# role 表
start = args.get("start", self.thisday)
end = args.get("end")
data = []
if args.get("download"):
data = self.get_order_info_download(user_id, start, end)
self.write_download(str(int(time.time())), data)
else:
data = self.get_order_info_download(user_id, start, end)
self.write_json(data=data)
def get_order_info_download(self, user_id, start, end):
if user_id not in super_auth():
return []
ck = CkUtils()
du = DateUtils()
end = du.getLastDays(end, -1) if end else None # end 往后面一天
start = None if not start else int(datetime.strptime(start, '%Y-%m-%d').timestamp())
end = None if not end else int(datetime.strptime(end, '%Y-%m-%d').timestamp())
op1 = f" and create_time>={start} " if start else ''
op2 = f" and create_time<={end} " if end else ''
sql = f'''
select a.order_id ,h.pitcher,
if(a.agent_id in ('sdk_one_21','sdk_two_21','sdk_three_21'),
'客服渠道',if(a.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0'),'自然流量',q.user_nicename)) as agent_name,
if(a.agent_id in ('sdk_one_21','sdk_two_21','sdk_three_21'),
'客服渠道',if(a.agent_id in ('sdk_one_0','sdk_two_0','sdk_three_0'),'自然流量',c.wx_name)) as channel,
formatDateTime(FROM_UNIXTIME(a.create_time),'%Y-%m-%d %H:%M:%S') order_time,
formatDateTime(FROM_UNIXTIME(d.create_time),'%Y-%m-%d %H:%M:%S') user_create_time,
e2.name as source_game,
d.id as mem_id,e.name as game ,a.amount ,a.real_amount ,g.server_name ,f.os as system_os,
g.role_name as role_name,a.payway ,a.status status ,a.mg_mem_id as user_id
from (select * from GameDataSum.h_pay_sum where 1=1 {op1} {op2} ) a
left join
(select b.agent_id as agent_id,b.advertiser_conf_id from
(select agent_id,max(update_time) as update_time
from GameDataSum.mp_conf_agent_sum mmc
group by agent_id ) a
left join GameDataSum.mp_conf_agent_sum b on a.agent_id=b.agent_id
and a.update_time = b.update_time) b on a.agent_id =b.agent_id
left join GameDataSum.mp_mp_conf_sum c on b.advertiser_conf_id = c.id
left join (select * from GameDataSum.h_member_sum where 1=1 {op2} ) d
on a.mem_id = d.id
left join GameDataSum.h_game_sum e on a.app_id =e.id
left join GameDataSum.h_game_sum e2 on d.app_id =e2.id
left join GameDataSum.h_pay_ext_sum f on a.id = f.pay_id
left join (select * from GameDataSum.h_mg_role_sum where 1=1 {op2} ) g
on g.role_id = f.role_id and g.server_id = f.server_id
and g.app_id = a.app_id and g.mg_mem_id =a.mg_mem_id
left join CostSourceData.advertiser_vx h on c.wx_name = h.name
left join GameDataSum.h_user_sum q on a.agent_id =q.id
where
a.app_id not in('sdk_one_6','sdk_one_13','sdk_one_23',
'sdk_two_6','sdk_two_13','sdk_two_23',
'sdk_three_6','sdk_three_13','sdk_three_23')
and a.agent_id in( 'sdk_two_0','sdk_one_0','sdk_three_0')
and d.app_id in('sdk_one_6','sdk_one_13','sdk_one_23',
'sdk_two_6','sdk_two_13','sdk_two_23',
'sdk_three_6','sdk_three_13','sdk_three_23')
and a.status =2
and d.id is not null and g.role_name is not null
'''
print(sql)
data = ck.execute(sql)
data_res = []
for _ in data:
tmp = {}
tmp['订单id'] = _[0]
tmp['投手'] = _[1]
tmp['渠道'] = _[2]
tmp['公众号名'] = _[3]
tmp['订单时间'] = _[4]
tmp['用户注册时间'] = _[5]
tmp['小程序游戏'] = _[6]
tmp['用户id'] = _[7]
tmp['H5游戏'] = _[8]
tmp['订单金额'] = _[9]
tmp['订单实际金额'] = _[10]
tmp['游戏服'] = _[11]
tmp['硬件系统'] = _[12]
tmp['角色名'] = _[13]
tmp['付款方式'] = _[14]
tmp['是否成功'] = _[15]
tmp['用户id'] = _[16]
data_res.append(tmp)
return data_res