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

            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"):
                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,0))) 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,0)))/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,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