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_channel_belong_pitcher(channel):
    """根据公众号获取投手"""
    sql = f"select pitcher,channel from dw_daily_channel_cost where dt='{du.get_n_days(-1)}' and pitcher!='' and channel!=''"
    data=ck.execute(sql)
    di={}
    for i in data:
        di[i[1]]=i[0]

    return di.get(channel,'')



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}' "

    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    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,get_round(data)),total


def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
    sql=f"""
        select formatDateTime(dt,'%Y-%m-%d') date ,pitcher,
               round(sum(cost),2) cost,
               round(sum(first_order_amount),2) first_order_amount,
               round(if(cost=0,0,first_order_amount/cost),4) first_roi ,
               round(sum(order_amount),2) order_amount,
               round(if(cost=0,0,sum(reg_order_amount)/cost),4)  today_roi,
               round(sum(total_cost),2) total_cost,
               round(sum(total_amount),2) total_amount,
               round(total_amount-total_cost,2) total_profit,
               round(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)
    total=ck.execute(f"select count(1) from ({sql}) a")[0][0]

    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),total


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,
    toDecimal32(last_month_far_roi,2) last_month_far_roi
       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','last_month_far_roi']
    return get_dict_list(key,data)


def get_channel_overview(channel,pitcher,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}'"
    if pitcher!='':
        sql+=f" and pitcher='{pitcher}'"
    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    sql += f" order by {order_by} {order} limit {page},{page_size} "
    print(sql)

    key=['channel','date','view_count','click_count','click_rate','follow_user','follow_rate','follow_per_cost','order_rate','order_per_cost','cost',
         'first_order_count','first_order_user','first_order_amount','order_count','order_user','order_amount','old_order_amount','first_amount_per_user',
         'new_user_order_rate','reg_user_amount','total_cost','total_amount','day_roi','all_roi','avg_new_order_rate','old_user_once_order_rate']

    return get_dict_list(key,get_round(ck.execute(sql))),total


def get_channel_again_order_trend(channel,date,pitcher):
    if get_channel_belong_pitcher(channel)!=pitcher:
        return []

    sql=f"""select toString(dt) date,
       channel,book,
       round(cost,2) cost,
       round(reg_order_amount,2) reg_amount,
       round(if(cost=0,0,reg_order_amount/cost),4) roi,
       follow_user new_follow,
       round(if(follow_user=0,0,cost/follow_user),2) new_follow_per_cost,
       reg_order_count reg_count,reg_order_user reg_user,
       round(if(reg_order_user=0,0,cost/reg_order_user),2) cost_per_user,
       round(if(reg_order_user=0,0,reg_order_amount/reg_order_user),2) avg_order_amount,
       round(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) avg_again_order_rate,
       order_count

        from dw_daily_channel where channel='{channel}' and dt='{date}'
       """
    print(sql)
    data=ck.execute(sql)
    print(data)
    key1=['date','channel','book','cost','reg_amount','roi','new_follow','new_follow_per_cost',
         'reg_count','reg_user','cost_per_user','avg_order_amount','avg_again_order_rate','order_count']
    json1=get_dict_list(key1,get_round(data))[0]
    # print(json1)
    sql2=f"""select
               dateDiff(day,toDate('{date}'),date)+1 ddf,
               count(1) c1,
               sum(if(count>1,1,0)) c2,
               sum(if(count>2,1,0)) c3,
               sum(if(count>3,1,0)) c4,
               sum(if(count>4,1,0)) c5,
               sum(if(count>5,1,0)) c6
        
        from (
        
        select count(1) count,date
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),6)
                         and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id,date
            ) a group by date"""

    df=ck.execute(sql2)

    # 补全
    xx=[i[0] for i in df]
    for i in range(1,8):
        if i not in xx:
            df.append((i,0,0,0,0,0,0))
    # 排序
    import operator
    df.sort(key=operator.itemgetter(0))

    reg_user=json1["reg_user"]
    li=[]
    for i in range(1,6):
        print(i)
        d = {}
        d["user_order_count"] = i
        d1={}
        d1["origin"]=df[0][i]
        d1["new"]=0
        d1["move"]=df[0][i+1]
        d1["now"]=d1["origin"]+d1['new']-d1["move"]
        d1["follow_order_rate"]=round(d1["now"]/reg_user,2) if reg_user!=0 else 0
        d['d1'] = d1

        d2={}
        d2["origin"] = d1["now"]
        d2["new"] = df[1][i]
        d2["move"] = df[1][i+1]
        d2["now"] = d2["origin"] +d2['new']- d2["move"]
        d2["follow_order_rate"] = round(d2["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d2'] = d2

        d3={}
        d3["origin"] = d2["now"]
        d3["new"] = df[2][i]
        d3["move"] =df[2][i+1]
        d3["now"] = d3["origin"] +d3['new']- d3["move"]
        d3["follow_order_rate"] = round(d3["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d3'] = d3

        d4={}
        d4["origin"] = d3["now"]
        d4["new"] = df[3][i]
        d4["move"] = df[3][i+1]
        d4["now"] = d4["origin"] +d4['new']- d4["move"]
        d4["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d4'] = d4

        d5={}
        d5["origin"] = d4["now"]
        d5["new"] = df[4][i]
        d5["move"] = df[4][i+1]
        d5["now"] = d5["origin"] +d5['new']- d5["move"]
        d5["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d5'] = d5

        d6 = {}
        d6["origin"] = d5["now"]
        d6["new"] = df[5][i]
        d6["move"] = df[5][i+1]
        d6["now"] = d6["origin"] +d6['new']- d6["move"]
        d6["follow_order_rate"] = round(d6["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d6'] = d6

        d7 = {}
        d7["origin"] = d6["now"]
        d7["new"] = df[6][i]
        d7["move"] = df[6][i+1]
        d7["now"] = d7["origin"] +d7['new']- d7["move"]
        d7["follow_order_rate"] = round(d7["now"] / reg_user, 2) if reg_user!=0 else 0
        d['d7'] = d7

        li.append(d)

    json1['data']=li
    print([json1])
    return [json1]


def get_channel_active(channel,pitcher,start,end,page,page_size,order_by,order):
    if get_channel_belong_pitcher(channel)!=pitcher:
        return [],0

    sql=f"""select formatDateTime(a.dt,'%Y-%m-%d') date, '{channel}' channel,book,cost,reg_amount,roi,new_follow_user,new_follow_per_cost,order_user,order_count,
       order_user_per_cost,day7_avg_act_rate,day7_avg_act_per_cost,day30_avg_act_rate,ay30_avg_act_cost,
       act_per_cost,
       concat(toString(reg_order_user1),',',toString(if(reg_order_user1=0,0,cost/reg_order_user1)),',',toString(if(order_user=0,0,reg_order_user1/order_user))),
       concat(toString(reg_order_user2),',',toString(if(reg_order_user2=0,0,cost/reg_order_user2)),',',toString(if(order_user=0,0,reg_order_user2/order_user))),
       concat(toString(reg_order_user3),',',toString(if(reg_order_user3=0,0,cost/reg_order_user3)),',',toString(if(order_user=0,0,reg_order_user3/order_user))),
       concat(toString(reg_order_user4),',',toString(if(reg_order_user4=0,0, cost/reg_order_user4)),',',toString(if(order_user=0,0,reg_order_user4/order_user))),
       concat(toString(reg_order_user5),',',toString(if(reg_order_user5=0,0 ,cost/reg_order_user5)),',',toString(if(order_user=0,0,reg_order_user5/order_user))),
       concat(toString(reg_order_user6),',',toString(if(reg_order_user6=0,0,cost/reg_order_user6)),',',toString(if(order_user=0,0,reg_order_user6/order_user))),
       concat(toString(reg_order_user7),',',toString(if(reg_order_user7=0,0,cost/reg_order_user7)),',',toString(if(order_user=0,0,reg_order_user7/order_user))),
       concat(toString(reg_order_user8),',',toString(if(reg_order_user8=0,0,cost/reg_order_user8)),',',toString(if(order_user=0,0,reg_order_user8/order_user))),
       concat(toString(reg_order_user9),',',toString(if(reg_order_user9=0,0,cost/reg_order_user9)),',',toString(if(order_user=0,0,reg_order_user9/order_user))),
       concat(toString(reg_order_user10),',',toString(if(reg_order_user10=0,0,cost/reg_order_user10)),',',toString(if(order_user=0,0,reg_order_user10/order_user))),
       concat(toString(reg_order_user11),',',toString(if(reg_order_user11=0,0,cost/reg_order_user11)),',',toString(if(order_user=0,0,reg_order_user11/order_user))),
       concat(toString(reg_order_user12),',',toString(if(reg_order_user12=0,0,cost/reg_order_user12)),',',toString(if(order_user=0,0,reg_order_user12/order_user))),
       concat(toString(reg_order_user13),',',toString(if(reg_order_user13=0,0,cost/reg_order_user13)),',',toString(if(order_user=0,0,reg_order_user13/order_user))),
       concat(toString(reg_order_user14),',',toString(if(reg_order_user14=0,0,cost/reg_order_user14)),',',toString(if(order_user=0,0,reg_order_user14/order_user))),
       concat(toString(reg_order_user15),',',toString(if(reg_order_user15=0,0,cost/reg_order_user15)),',',toString(if(order_user=0,0,reg_order_user15/order_user))),
       concat(toString(reg_order_user16),',',toString(if(reg_order_user16=0,0,cost/reg_order_user16)),',',toString(if(order_user=0,0,reg_order_user16/order_user))),
       concat(toString(reg_order_user17),',',toString(if(reg_order_user17=0,0,cost/reg_order_user17)),',',toString(if(order_user=0,0,reg_order_user17/order_user))),
       concat(toString(reg_order_user18),',',toString(if(reg_order_user18=0,0,cost/reg_order_user18)),',',toString(if(order_user=0,0,reg_order_user18/order_user))),
       concat(toString(reg_order_user19),',',toString(if(reg_order_user19=0,0,cost/reg_order_user19)),',',toString(if(order_user=0,0,reg_order_user19/order_user))),
       concat(toString(reg_order_user20),',',toString(if(reg_order_user20=0,0,cost/reg_order_user20)),',',toString(if(order_user=0,0,reg_order_user20/order_user))),
       concat(toString(reg_order_user21),',',toString(if(reg_order_user21=0,0,cost/reg_order_user21)),',',toString(if(order_user=0,0,reg_order_user21/order_user))),
       concat(toString(reg_order_user22),',',toString(if(reg_order_user22=0,0,cost/reg_order_user22)),',',toString(if(order_user=0,0,reg_order_user22/order_user))),
       concat(toString(reg_order_user23),',',toString(if(reg_order_user23=0,0,cost/reg_order_user23)),',',toString(if(order_user=0,0,reg_order_user23/order_user))),
       concat(toString(reg_order_user24),',',toString(if(reg_order_user24=0,0,cost/reg_order_user24)),',',toString(if(order_user=0,0,reg_order_user24/order_user))),
       concat(toString(reg_order_user25),',',toString(if(reg_order_user25=0,0,cost/reg_order_user25)),',',toString(if(order_user=0,0,reg_order_user25/order_user))),
       concat(toString(reg_order_user26),',',toString(if(reg_order_user26=0,0,cost/reg_order_user26)),',',toString(if(order_user=0,0,reg_order_user26/order_user))),
       concat(toString(reg_order_user27),',',toString(if(reg_order_user27=0,0,cost/reg_order_user27)),',',toString(if(order_user=0,0,reg_order_user27/order_user))),
       concat(toString(reg_order_user28),',',toString(if(reg_order_user28=0,0,cost/reg_order_user28)),',',toString(if(order_user=0,0,reg_order_user28/order_user))),
       concat(toString(reg_order_user29),',',toString(if(reg_order_user29=0,0,cost/reg_order_user29)),',',toString(if(order_user=0,0,reg_order_user29/order_user))),
       concat(toString(reg_order_user30),',',toString(if(reg_order_user30=0,0,cost/reg_order_user30)),',',toString(if(order_user=0,0,reg_order_user30/order_user)))

       from (

select dt,book,cost,
       reg_order_amount reg_amount,
       if(cost=0,0,reg_order_amount/cost) roi,
       follow_user new_follow_user,
       if(follow_user=0,0,cost/follow_user) new_follow_per_cost,
       reg_order_user order_user,
       reg_order_count order_count,
       if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost,
       if(follow_user=0,0,reg_order_user7/follow_user) day7_avg_act_rate ,
       if(reg_order_user7=0,0,cost/reg_order_user7) day7_avg_act_per_cost,
       if(follow_user=0,0,reg_order_user30/follow_user) day30_avg_act_rate,
       if(reg_order_user30=0,0,cost/reg_order_user30) ay30_avg_act_cost,
       if(reg_order_user=0,0,cost/reg_order_user) act_per_cost
       from dw_daily_channel where channel='{channel}' and dt>='{start}' and dt<='{end}') a
left outer join (

select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,
       count(distinct if(subtractDays(date, 1)>=reg_time,NULL,user_id)) reg_order_user1,
       count(distinct if(subtractDays(date, 2)>=reg_time,NULL,user_id)) reg_order_user2,
       count(distinct if(subtractDays(date, 3)>=reg_time,NULL,user_id)) reg_order_user3,
       count(distinct if(subtractDays(date, 4)>=reg_time,NULL,user_id)) reg_order_user4,
       count(distinct if(subtractDays(date, 5)>=reg_time,NULL,user_id)) reg_order_user5,
       count(distinct if(subtractDays(date, 6)>=reg_time,NULL,user_id)) reg_order_user6,
       count(distinct if(subtractDays(date, 7)>=reg_time,NULL,user_id)) reg_order_user7,
       count(distinct if(subtractDays(date, 8)>=reg_time,NULL,user_id)) reg_order_user8,
       count(distinct if(subtractDays(date, 9)>=reg_time,NULL,user_id)) reg_order_user9,
       count(distinct if(subtractDays(date, 10)>=reg_time,NULL,user_id)) reg_order_user10,
       count(distinct if(subtractDays(date, 11)>=reg_time,NULL,user_id)) reg_order_user11,
       count(distinct if(subtractDays(date, 12)>=reg_time,NULL,user_id)) reg_order_user12,
       count(distinct if(subtractDays(date, 13)>=reg_time,NULL,user_id)) reg_order_user13,
       count(distinct if(subtractDays(date, 14)>=reg_time,NULL,user_id)) reg_order_user14,
       count(distinct if(subtractDays(date, 15)>=reg_time,NULL,user_id)) reg_order_user15,
       count(distinct if(subtractDays(date, 16)>=reg_time,NULL,user_id)) reg_order_user16,
       count(distinct if(subtractDays(date, 17)>=reg_time,NULL,user_id)) reg_order_user17,
       count(distinct if(subtractDays(date, 18)>=reg_time,NULL,user_id)) reg_order_user18,
       count(distinct if(subtractDays(date, 19)>=reg_time,NULL,user_id)) reg_order_user19,
       count(distinct if(subtractDays(date, 20)>=reg_time,NULL,user_id)) reg_order_user20,
       count(distinct if(subtractDays(date, 21)>=reg_time,NULL,user_id)) reg_order_user21,
       count(distinct if(subtractDays(date, 22)>=reg_time,NULL,user_id)) reg_order_user22,
       count(distinct if(subtractDays(date, 23)>=reg_time,NULL,user_id)) reg_order_user23,
       count(distinct if(subtractDays(date, 24)>=reg_time,NULL,user_id)) reg_order_user24,
       count(distinct if(subtractDays(date, 25)>=reg_time,NULL,user_id)) reg_order_user25,
       count(distinct if(subtractDays(date, 26)>=reg_time,NULL,user_id)) reg_order_user26,
       count(distinct if(subtractDays(date, 27)>=reg_time,NULL,user_id)) reg_order_user27,
       count(distinct if(subtractDays(date, 28)>=reg_time,NULL,user_id)) reg_order_user28,
       count(distinct if(subtractDays(date, 29)>=reg_time,NULL,user_id)) reg_order_user29,
       count(distinct if(subtractDays(date, 30)>=reg_time,NULL,user_id)) reg_order_user30
       from order where  channel='{channel}' and dt>='{start}' group by formatDateTime(reg_time,'%Y-%m-%d')) b on a.dt=b.dt 
"""
    print(sql)
    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    sql+=f" order by {order_by} {order} limit {page},{page_size} "
    data = ck.execute(sql)
    print(data)

    key=['date','channel','book','cost','reg_amount','roi','new_follow_user','new_follow_per_cost','order_user','order_count','order_user_per_cost',
         'day7_avg_act_rate','day7_avg_act_per_cost','day30_avg_act_rate','day30_avg_act_cost','act_per_cost','d1','d2','d3','d4','d5','d6','d7','d8','d9','d10',
         'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']

    x=get_dict_list(key,get_round(data))
    print(x)
    li=[]

    for i in x:
        di = {}
        for j in i:
            if j in ['d1','d2','d3','d4','d5','d6','d7','d8','d9','d10',
         'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']:
                if i[j]:
                    k=i[j].split(",")
                    k[0]=round(float(k[0]),0)
                    k[1]=round(float(k[1]),2)
                    k[2]=round(float(k[2]),4)

                else:
                    k=[0,0,0]
                di[j] = dict(zip(['act_user', 'act_cost_per_cost', 'act_rate'], k))


            else:
                di[j]=i[j]
        li.append(di)
    return li,total

def get_channel_order_trend(channel,pitcher,start,end,page,page_size,order_by,order):
    if get_channel_belong_pitcher(channel)!=pitcher:
        return [],0
    sql=""

    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    a=''
    return a,total

def get_channel_summary(channel,pitcher,page,page_size,order_by,order):
    sql = """select a.channel,state,'朋友圈' location,toString(start) start,toString(end) 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}'"
    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    sql+=f' order by {order_by} {order} limit {page},{page_size} '
    print(sql)
    key=['channel','state','location','start','end','total_cost','total_amount','profit','roi',
         'follow_user','follow_per_cost','order_user','order_tran_rate','order_tran_cost']

    return get_dict_list(key,get_round(ck.execute(sql))),total



if __name__ == '__main__':
    # a=get_channel_overview('','','',1,10,'date','desc')
    # a=get_channel_summary('','')
    a=get_channel_again_order_trend('龙鳞文学','2021-01-01','宋刚')
    # a=get_channel_active('玉龙书社','2020-09-15','2020-09-20',1,10,'date','desc')
    # print(a)
    pass