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


class OrderInfo(BaseHandler):

    def post(self):
        # 0.通过公众号-----需要表进行join
        # 0.5需要是否自然量
        # 显示----需要多一个订单时间
        # 1.通过订单号获取
        # 通过渠道
        # 1.通过玩家id
        # 1.通过玩家账号
        # 1.通过角色名称
        # 1.通过操作系统

        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)
            end = args.get("end")

            user_start = args.get("user_start")
            user_end = args.get("user_end")

            order_id = args.get('order_id')
            member_id = args.get('member_id')

            page = args.get("page",1)
            page_size = args.get("page_size",20)
            order_by = args.get("order_by", 'a.create_time ')
            order_arg = 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, page,
                                                          page_size, order_arg, order_by)
            if args.get("download"):
                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, page, page_size, order_arg, order_by):
        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 channel = '{UserAuthUtils.get_auth_channel(user_id)[0]}'"

            else:
                op = f" and channel in {str(UserAuthUtils.get_auth_channel(user_id))}"

        op1 = f" and pitcher ='{pitcher}' " if pitcher else ''
        op2 = f" and channel='{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 ''

        op15 = f" order by {order_by} {order_arg}" if order_by and order_arg else ''



        sql = f"""select a.order_id ,h.pitcher,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 user_name ,a.amount ,a.real_amount ,g.server_name ,
                        CONVERT (g.role_name USING utf8) as role_name,a.payway ,a.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_mem_game f on a.id = f.mem_id 
                        left join db_mp.h_mg_role g on g.mg_mem_id = f.id
                        left join quchen_text.advertiser_vx h on c.wx_name =h.name 

                        
                        where 1=1 and a.status=2 {op} {op1} {op2} {op3} {op4} {op5}  {op6} {op7} {op8} {op9} 
                        {op15}
            """
        # print(sql)
        sumsql = f""" select '' as order_id ,'' as pitcher, '' as channel, 
		            '' as order_time,'' as user_create_time,'' as user_id,
			        '' as user_name,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(data)
        print(len(data))
        return data, total, total_data