QcWebServerGame/handlers/OrderHandler.py

from handlers.HandlerBase import BaseHandler
import time
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)
                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 ,
                        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 
                    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
        '''