QcWebServerGame/handlers/OrderHandler.py

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

            # 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()

        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 >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" where pitcher ='{pitcher}' " if pitcher else ''
        op2 = f''' where 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" where 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 (12,0) "
        elif agent_name and '自然流量渠道' in agent_name:
            op12_pay = f" and agent_id=0 "
        elif agent_name and '客服渠道' in agent_name:
            op12_pay = f" and agent_id=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,
		h.pitcher 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
                        	{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 {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 {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 {op}
              {op15}
            """
        limit_start = (page - 1) * page_size
        limit_sql = sql + '\n' + f''' limit {limit_start},{page_size} '''
        print(limit_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
                """
        totalsql = f'''
        select count(1) from ({sql}) a
        '''

        print(sumsql)
        print(totalsql)
        ck = CkUtils()
        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

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

            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 []
        # todo: sql中使用min有问题，因为有重复数据
        db = MysqlUtils()
        op1 = f"and toDate(FROM_UNIXTIME(a.create_time))>='{start}' " if start else ''
        op2 = f"and toDate(FROM_UNIXTIME(a.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'),1,0) as is_natural ,
            sum(amount) as sum_amount,count(DISTINCT(a.mem_id)) as human_counts
            from GameDataSum.h_pay_sum a
            LEFT  join GameDataSum.h_game_sum b on a.app_id = b.id
            left join GameDataSum.h_member_sum 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 a.status = 2
            {op1} {op2}
            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'),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 GameDataSum.h_pay_sum a
            left join GameDataSum.h_member_sum c on a.mem_id = c.id 
            where a.app_id in (select id from GameDataSum.h_game_sum 
                    where classify =5) 
             {op1} {op2}
              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)
        ck = CkUtils()
        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 []

        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