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