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: db = MysqlUtils() sql = f''' select min(id),name from db_mp.h_game group by name ''' data = db.quchen_text.getData_json(sql) self.write_json(data=data) 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') db = MysqlUtils() 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 row_number() over() as id, d.user_nicename as agent_name from quchen_text.advertiser_vx a left join db_mp.mp_mp_conf b on a.name =b.wx_name left join db_mp.mp_conf_agent c on b.id = c.advertiser_conf_id left join db_mp.h_user d on c.agent_id = d.id where d.user_nicename is not null {op} group by d.user_nicename ''' data = db.quchen_text.getData_json(sql) data.insert(0, {'id': -1, 'agent_name': '自然流量渠道'}) data.insert(0, {'id': -2, 'agent_name': '客服渠道'}) self.write_json(data=data) 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}) " sql = f''' select DATE_FORMAT(dt,"%Y-%m-%d") 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,date(FROM_UNIXTIME(a.create_time)) dt, b.name ,if(a.agent_id=0,'自然流量','买量流量') as agent from db_mp.h_pay a left join db_mp.h_game b on a.app_id = b.id where a.status = 2 {op} group by date(FROM_UNIXTIME(a.create_time)) ,b.name, if(a.agent_id=0,'自然流量','买量流量') union select sum(amount) sum_amount,date(FROM_UNIXTIME(a.create_time)) dt, b.name ,'当日总量' as agent from db_mp.h_pay a left join db_mp.h_game b on a.app_id = b.id where a.status = 2 {op} group by b.name ,date(FROM_UNIXTIME(a.create_time))) a where 1=1 {op1} {op2} group by dt,name order by dt desc ''' print(sql) sumsql = '''select 1; ''' data, total, total_data = getLimitSumData(db.dm, sql, sumsql, page, page_size) 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 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() 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 >= UNIX_TIMESTAMP( h.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 >UNIX_TIMESTAMP('{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 h.pitcher ='{pitcher}' " if pitcher else '' op2 = f''' and c.wx_name in ({"'" + "','".join(channel) + "'"})''' if channel else '' op3 = f" and from_unixtime(a.create_time) >'{start}' " if start else '' op4 = f" and from_unixtime(a.create_time) <'{end}' " if end else '' op5 = f" and CONVERT (g.role_name USING utf8)='{role_name}' " if role_name else '' op6 = f" and from_unixtime(d.create_time) >'{user_start}' " if user_start else '' op7 = f" and from_unixtime(d.create_time) <'{user_end}' " if user_end else '' op8 = f" and a.order_id='{order_id}'" if order_id else '' op9 = f" and a.mem_id='{member_id}'" if member_id else '' op10 = f" and a.status={status} " if status else '' op11 = f''' and e.name in ({"'" + "','".join(game) + "'"}) ''' if game else '' # 有自然流量和客服渠道时，其他渠道进行规避 if agent_name and '自然流量渠道' in agent_name and '客服渠道' in agent_name: op12 = f" and a.agent_id in (12,0) " elif agent_name and '自然流量渠道' in agent_name: op12 = f" and a.agent_id=0 " elif agent_name and '客服渠道' in agent_name: op12 = f" and a.agent_id=12 " else: op12 = f''' and q.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 row_number() over() as id,a.order_id ,h.pitcher, if(a.agent_id =21,'客服渠道',if(a.agent_id=0,'自然流量',q.user_nicename)) as agent_name, if(a.agent_id =21,'客服渠道',if(a.agent_id=0,'自然流量',c.wx_name)) as channel, if(a.create_time is not null ,CONVERT(from_unixtime(a.create_time),char(20)),null) order_time, if(d.create_time is not null,CONVERT(from_unixtime(d.create_time),char(20)),null) user_create_time, d.id as mem_id,e.name as game ,a.amount ,a.real_amount ,g.server_name ,f.os as system_os, CONVERT (g.role_name USING utf8) as role_name,a.payway ,a.status status ,a.mg_mem_id as user_id from db_mp.h_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 db_mp.mp_conf_agent mmc group by agent_id ) a left join db_mp.mp_conf_agent 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 db_mp.mp_mp_conf c on b.advertiser_conf_id = c.id left join db_mp.h_member d on a.mem_id = d.id left join db_mp.h_game e on a.app_id =e.id left join db_mp.h_pay_ext f on a.id = f.pay_id left join db_mp.h_mg_role 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 quchen_text.advertiser_vx h on c.wx_name = h.name left join db_mp.h_user q on a.agent_id =q.id where 1=1 {op} {op1} {op2} {op3} {op4} {op5} {op6} {op7} {op8} {op9} {op10} {op11} {op12} {op15} """ print(sql) 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 """ # print(sumsql) data, total, total_data = getLimitSumData(db.dm, sql, sumsql, page, page_size) print(len(data)) return data, total, total_data def get_game_pitcher(self, user_id): sql = ''' SELECT d.name as game_name,d.id FROM quchen_text.advertiser_vx a left join db_mp.mp_mp_conf b on a.name =b.wx_name left join db_mp.mp_conf_agent c on c.advertiser_conf_id = b.id left join db_mp.h_game d on c.app_id = d.id where pitcher ='金康' group by id ''' 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") data = self.get_order_info(user_id, start, end) if args.get("download"): data = self.get_order_info_download(user_id, start, end) self.write_download(str(int(time.time())), data) else: self.write_json(data=data) def get_order_info(self, user_id, start, end): if user_id not in super_auth(): return [] db = MysqlUtils() op1 = f"and date(FROM_UNIXTIME(a.create_time))>='{start}' " if start else '' op2 = f"and date(FROM_UNIXTIME(a.create_time))<='{end}' " if end else '' sql = f''' select DATE_FORMAT(a.dt,'%Y-%m-%d'),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, a.sum_amount as channel_amount, a.human_counts as channel_human_count from (select date(FROM_UNIXTIME(a.create_time)) dt,a.app_id as app_id , b.name as h5_game_name, c.app_id as c_app_id,d.name as channel_game_name,if(c.agent_id=0,1,0) as is_natural , sum(amount) as sum_amount,count(DISTINCT(a.mem_id)) as human_counts from db_mp.h_pay a LEFT join db_mp.h_game b on a.app_id = b.id left join db_mp.h_member c on a.mem_id = c.id LEFT join db_mp.h_game d on c.app_id = d.id where a.app_id in (select id from db_mp.h_game where classify =5) and a.app_id != c.app_id and a.status = 2 {op1} {op2} group by date(FROM_UNIXTIME(a.create_time)),a.app_id , c.app_id ,d.name,if(c.agent_id=0,1,0) ) as a left join (select date(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 db_mp.h_pay a left join db_mp.h_member c on a.mem_id = c.id where a.app_id in (select id from db_mp.h_game where classify =5) {op1} {op2} and a.status=2 group by date(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 ''' df_source = db.dm.get_data_list(sql) game_info = {} for _ in df_source: print(_) _[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 [] db = MysqlUtils() op1 = f"and date(FROM_UNIXTIME(a.create_time))>='{start}' " if start else '' op2 = f"and date(FROM_UNIXTIME(a.create_time))<='{end}' " if end else '' sql = f''' select DATE_FORMAT(a.dt,'%Y-%m-%d') 日期, a.h5_game_name 游戏名称H5, b.sum_amount as 今日总充值, b.natural_amount as H5自然量充值, b.amount_rate as 充值额比例, b.human_count as 今日总充值人数, b.natural_human_count H5自然量人数, b.human_count_rate as 用户比例, a.channel_game_name as 游戏名称小程序, a.is_natural as 小程序的量是否为自然量, a.sum_amount as 小程序导入用户充值, a.human_counts as 小程序导入人数 from (select date(FROM_UNIXTIME(a.create_time)) dt,a.app_id as app_id , b.name as h5_game_name, c.app_id as c_app_id,d.name as channel_game_name,if(c.agent_id=0,1,0) as is_natural , sum(amount) as sum_amount,count(DISTINCT(a.mem_id)) as human_counts from db_mp.h_pay a LEFT join db_mp.h_game b on a.app_id = b.id left join db_mp.h_member c on a.mem_id = c.id LEFT join db_mp.h_game d on c.app_id = d.id where a.app_id in (select id from db_mp.h_game where classify =5) and a.app_id != c.app_id and a.status = 2 {op1} {op2} group by date(FROM_UNIXTIME(a.create_time)),a.app_id , c.app_id ,d.name,if(c.agent_id=0,1,0) ) as a left join (select date(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 db_mp.h_pay a left join db_mp.h_member c on a.mem_id = c.id where a.app_id in (select id from db_mp.h_game where classify =5) {op1} {op2} and a.status=2 group by date(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 ''' data = db.dm.getData_json(sql) return data 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: self.write_json(data=data) def get_order_info_download(self, user_id, start, end): if user_id not in super_auth(): return [] db = MysqlUtils() op1 = f"and CONVERT(from_unixtime(a.create_time),char(10))>='{start}' " if start else '' op2 = f"and CONVERT(from_unixtime(a.create_time),char(10))<='{end}' " if end else '' sql = f''' select row_number() over() as id,a.order_id ,h.pitcher, if(a.agent_id =21,'客服渠道',if(a.agent_id=0,'自然流量',q.user_nicename)) as agent_name, if(a.agent_id =21,'客服渠道',if(a.agent_id=0,'自然流量',c.wx_name)) as channel, if(a.create_time is not null ,CONVERT(from_unixtime(a.create_time),char(20)),null) order_time, if(d.create_time is not null,CONVERT(from_unixtime(d.create_time),char(20)),null) 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, CONVERT (g.role_name USING utf8) as role_name,a.payway ,a.status status ,a.mg_mem_id as user_id from db_mp.h_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 db_mp.mp_conf_agent mmc group by agent_id ) a left join db_mp.mp_conf_agent 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 db_mp.mp_mp_conf c on b.advertiser_conf_id = c.id left join db_mp.h_member d on a.mem_id = d.id left join db_mp.h_game e on a.app_id =e.id left join db_mp.h_game e2 on d.app_id =e2.id left join db_mp.h_pay_ext f on a.id = f.pay_id left join db_mp.h_mg_role 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 quchen_text.advertiser_vx h on c.wx_name = h.name left join db_mp.h_user q on a.agent_id =q.id where a.app_id not in(6,13,23) and a.agent_id =0 and d.app_id in(6,13,23) {op1} {op2} and a.status =2 ''' data = db.dm.getData_json(sql) return data