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
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 id,name from db_mp.h_game
            '''
            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 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", self.thisday)

            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

            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 j.game_name is not null and d.create_time >= j.create_time '

            # 限制为自己游戏的1.自然流量，2.客服流量
            if agent_name and ('自然流量渠道' in agent_name or '客服渠道' in agent_name):
                op_game = '' + 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='{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 user_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.mem_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 
                        left join (
                                SELECT a.pitcher as pitcher ,a.name as channel ,
                                d.name as game_name, UNIX_TIMESTAMP(a.start_date) as create_time 
                                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
                                ) j on e.name = j.game_name

                        
                        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 ,'' 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