QcWebServerGame/data_manage/pitcher_panel.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
du = DateUtils()
ck = CkUtils()
db = MysqlUtils()
log = logger()


def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order):
    sql=f"""select channel,stage,platform,book,
            formatDateTime(dt,'%Y-%m-%d') as date,
            cost,
            first_order_amount,
           toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi,
           first_order_user,first_order_count,
           toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost,
           view_count,click_count,follow_user,
           toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate,
           toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
            total_cost,
           toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate
    
           from dw_daily_channel where dt>='{start}' and dt<='{end}'  """
    if pitcher!='all':
        sql += f" and pitcher='{pitcher}' "

    if channel!='':
        sql += f" and channel='{channel}' "

    sql += f" order by {order_by} {order} limit {page},{page_size} "

    print(sql)
    data=ck.execute(sql)
    key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user',
         'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost',
         'total_cost','back_rate']

    return get_dict_list(key, data)


def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
    sql=f"""
        select formatDateTime(dt,'%Y-%m-%d') date ,pitcher,
               toDecimal32(sum(cost),2) cost,
               toDecimal32(sum(first_order_amount),2) first_order_amount,
               toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi ,
               toDecimal32(sum(order_amount),2) order_amount,
               toDecimal32(if(cost=0,0,sum(reg_order_amount)/cost),4)  today_roi,
               toDecimal32(sum(total_cost),2) total_cost,
               toDecimal32(sum(total_amount),2) total_amount,
               toDecimal32(total_amount-total_cost,2) total_profit,
               toDecimal32(if(total_cost=0,0,total_amount/total_cost),4) total_roi
               from dw_daily_channel  where dt>='{start}' and dt<='{end}' """

    if pitcher != 'all':
        sql += f" and pitcher='{pitcher}' "

    sql += f" group by date,pitcher order by {order_by} {order} limit {page},{page_size} "
    print(sql)
    data = ck.execute(sql)
    key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_cost','total_amount','total_profit','total_roi']
    return get_dict_list(key,data)


def get_pitcher_panel_overview(pitcher):

    sql=f"""select  pitcher,
        toDecimal32(cost,2) cost,
        toDecimal32(amount,2) amount,
        toDecimal32(roi,4) roi, 
       channel_count,on_channel_count,
    off_channel_count,
    toDecimal32(this_month_cost,2) this_month_cost,
    toDecimal32(this_month_amount,2) this_month_amount,
    toDecimal32(this_month_roi,4) this_month_roi,
    toDecimal32(last_month_cost,2) last_month_cost,
    toDecimal32(last_month_amount,2) last_month_amount,
    toDecimal32(last_month_roi,4) last_month_roi,
    toDecimal32(last_month_far_amount,2) last_month_far_amount,
       follow_user
       from  dm_pitcher_daily_page_total where  dt='{du.get_n_days(-1)}'"""

    if pitcher != 'all':
        sql += f" and pitcher='{pitcher}' "

    print(sql)
    data=ck.execute(sql)
    print(data)

    key=['pitcher','cost','amount','roi','channel_count','on_channel_count','off_channel_count','this_month_cost','this_month_amount','this_month_roi',
         'last_month_cost','last_month_amount','last_month_roi','last_month_far_amount','follow_user']
    return get_dict_list(key,data)





def get_channel_overview(channel,start,end,page,page_size,order_by,order):
    sql="""select channel,toString(dt) date,
       view_count,click_count,
       round(if(view_count=0,0,click_count/view_count),4) click_rate,
       follow_user,
       round(if(click_count=0,0,follow_user/click_count),4) follow_rate,
       round(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
       round(if(click_count=0,0,first_order_count/click_count),4) order_rate,
       round(if(first_order_user=0,0,cost/first_order_user),2) order_per_cost,
       round(cost,2) cost,
       first_order_count,first_order_user,
       round(first_order_amount,2) first_order_amount,order_count,order_user,
       round(order_amount,2) order_amount,
       round(order_amount-first_order_amount,2) old_order_amount,
       round(if(first_order_user=0,0,first_order_amount/first_order_user),2) first_amount_per_user,
       round(if(follow_user=0,0,first_order_amount/follow_user),2) amount_per_follow,
       round(if(first_order_user=0,0,cost/first_order_user),2) first_cost_per_user,
       round(if(follow_user=0,0,first_order_user/follow_user),4) new_user_order_rate,
       round(reg_order_amount,2) reg_user_amount,
       round(total_cost,2) total_cost,
       round(total_amount,2) total_amount,
       round(if(cost=0,0,first_order_amount/cost),4) day_roi,
       round(if(cost=0,0,reg_order_amount/cost),4) all_roi,
       0 avg_new_order_rate,
       0 old_user_once_order_rate from dw_daily_channel where 1=1 """
    if channel!='':
        sql+=f" and channel='{channel}'"
    if start!='':
        sql+=f" and start>='{start}'"
    if end!='':
        sql+=f" and end<='{end}'"

    sql += f" order by {order_by} {order} limit {page},{page_size} "
    print(sql)

    return ck.execute(sql)



def get_channel_again_order_trend(channel,date):
    a=[{'date':'2020-12-01',
       'channel':'宝珠书屋',
       'book':'熊出没',
       'cost':23.23,
       'reg_amount':23421.11,
       'roi':0.23,
       'new_follow':23,
       'new_follow_per_cost':234.11,
       'reg_user':111,
       'reg_count':123,
       'cost_per_user':123,
       'avg_again_order_rate':0.24,
       'avg_order_amount':1231.11,
       'order_count':11,
       'data':[{'user_order_count' :1,
                               'd1':{'origin':12,
                                     'new':1231,
                                     'move':12,
                                     'now':2134,
                                     'follow_order_rate':0.12},
                               'd2': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12},
                               'd3': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12},
                               'd4': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12},
                               'd5': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12},
                               'd6': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12},
                               'd7': {'origin': 12,
                                      'new': 1231,
                                      'move': 12,
                                      'now': 2134,
                                      'follow_order_rate': 0.12}
                }
               ]


        }]
    return a

def get_channel_active(channel,start,end):

    a=[{
        'date':'2020-12-01',
        'channel':'宝珠书屋',
        'book':'魏汝稳',
        'cost':234.11,
        'reg_amount':123.11,
        'roi':0.45,
        'new_follow_user':11,
        'new_follow_per_cost':234.11,
        'order_user':222,
        'order_count':12312,
        'order_user_per_cost':123.11,
        'day7_avg_act_rate':0.14,
        'day7_avg_act_per_cost':5.23,
        'day30_avg_act_rate':0.9334,
        'day30_avg_act_rate':5.23,
        'act_per_cost':43.11,
        'd1':{
            'act_user':12,
            'act_cost_per_cost':23.11,
            'act_rate':0.4512},
        'd2': {
            'act_user': 12,
            'act_cost_per_cost': 23.11,
            'act_rate': 0.4512},
        'd3': {
            'act_user': 12,
            'act_cost_per_cost': 23.11,
            'act_rate': 0.4512}
    }]
    return a


def get_channel_order_trend(channel,start,end):
    a=[{
        'date': '2020-12-01',
        'channel': '宝珠书屋',
        'book': '魏汝稳',
        'cost': 234.11,
        'reg_amount': 123.11,
        'roi': 0.45,
        'new_follow_user': 11,
        'new_follow_per_cost': 234.11,
        'order_user': 222,
        'order_count': 12312,
        'order_user_per_cost': 123.11,
        'd1':{
            'order':232.11,
            'roi':0.2341,
            'add':0.4511,
            'mult':1.12},
        'd2': {
            'order': 232.11,
            'roi': 0.2341,
            'add': 0.4511,
            'mult': 1.12},
        'd3': {
            'order': 232.11,
            'roi': 0.2341,
            'add': 0.4511,
            'mult': 1.12},
    }]
    return a


def get_channel_summary(channel,pitcher):
    sql = """select a.channel,state,'朋友圈' location,toString(start),toString(end),total_cost,total_amount,
                        profit,roi,follow_user,follow_per_cost,order_user,
                if(follow_user=0,0,order_user/follow_user) order_tran_rate,if(order_user=0,0,total_cost/order_user) order_tran_cost
                from
                (
                select channel,if(max(dt)>subtractDays(today(),10),'在投','停投') state,
                       min(dt) start,max(dt) end
                       from dw_daily_channel_cost where cost>0 {} group by channel) a

                left outer join(
                select channel,sum(follow_user) follow_user,
                       if(follow_user=0,0,sum(cost)/follow_user) follow_per_cost
                       from dw_daily_channel group by channel) b on a.channel=b.channel

                left outer join(
                select sum(reg_order_user) order_user,channel from dw_daily_channel where dt=subtractDays(today(),1) group by channel) c on a.channel=c.channel

                left outer join (
                    select total_cost,
                       total_amount,
                       channel,
                       total_amount-total_cost profit,
                       if(total_cost=0,0,total_amount/total_cost) roi
                from dw_channel_daily_total where dt=subtractDays(today(),1)) d on a.channel=d.channel 
                 where 1=1 """

    if pitcher!='':
        sql=sql.format(f" and pitcher='{pitcher}'")
    else:
        sql=sql.format('')
    if channel!='':
        sql+=f" and channel='{channel}'"
    return ck.execute(sql)



if __name__ == '__main__':
    # a=get_channel_overview('','','',1,10,'date','desc')
    a=get_channel_summary('','')
    print(a)