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 = '' op_sup1 = '' op_sup2 = '' op_sup3 = '' else: # 限制为自己游戏的1.自然流量，2.客服流量 # op_sup1 = f' or a.agent_id=0 or a.agent_id=21 ' op_sup1 = '' # 限制为自己公众号开始时候的数据 op_sup2 = f' and i.start_date is not null and d.create_time >= unix_timestamp(i.start_date) ' # 限制为自己的游戏 op_sup3 = f'' 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 c.wx_name = '{UserAuthUtils.get_auth_channel(user_id)[0]}' ''' else: op = f" and c.wx_name in {str(UserAuthUtils.get_auth_channel(user_id))}" op1 = f" and h.pitcher ='{pitcher}' " if pitcher else '' print(type(channel)) 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 '' print(agent_name) if agent_name and agent_name == '自然流量渠道': op12 = f" and a.agent_id=0 " elif agent_name and 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 quchen_text.advertiser_vx i on c.wx_name =i.name where 1=1 {op} {op_sup1} {op_sup2} {op_sup3} {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