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()

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_channels_from_user(user):
    sql=f"""SELECT a.nick_name FROM t_mp_account a 
            left join t_sys_user b on a.oper_user_id=b.user_id where b.nick_name='{user}'"""
    return tuple(MysqlUtils().zx.getOneList(sql))



def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order):
    db=MysqlUtils()
    op1=f" and pitcher='{pitcher}'" if pitcher else ''
    op2=f" and channel='{channel}'" if channel else ''

    sql=f"""select channel,stage,platform,book,
           dt as date,cost,first_order_amount,
           if(cost=0,0,first_order_amount/cost) first_roi,
           first_order_user,first_order_count,
           round(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost,
           view_count,click_count,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,
           total_cost,
           round(if(total_cost=0,0,total_amount/total_cost),4) back_rate
           from dw_channel_daily where dt>='{start}' and dt<='{end}' {op1} {op2} 
           order by {order_by} {order}
"""
    return getLimitData(db.quchen_text,sql,page,page_size)

def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
    db=MysqlUtils()
    op1= f" and pitcher='{pitcher}'" if pitcher else ''
    op2=f" and dt>='{start}' " if start else ''
    op3=f" and dt<='{end}' " if end else ''
    op4=f" order by {order_by} {order}" if order_by and order else ''

    sql=f"""
            select dt date,
            pitcher,cost,
            first_order_amount,
            reg_amount,
            round(if(cost=0,0,first_order_amount/cost),4) first_roi,
            amount order_amount,
            round(if(cost=0,0,reg_amount/cost),4)  today_roi,
            reg_amount-cost profit
            from dw_pitcher_trend where 1=1 {op1} {op2} {op3} {op4}
"""
    print(sql)

    sumSql=f"""
           select '总计' date,sum(cost) cost,
           sum(first_order_amount) first_order_amount,sum(reg_amount)  reg_amount, 
           round(if(sum(cost)=0,0,sum(first_order_amount)/sum(cost)),4) first_roi,
           sum(order_amount) order_amount,
           round(if(sum(cost)=0,0,sum(reg_amount)/sum(cost)),4)  today_roi,
            sum(profit) profit
            from ({sql}) b  """

    print(sumSql)

    return getLimitSumData(db.dm,sql,sumSql,page,page_size)


def get_pitcher_panel_overview(pitcher):
    db=MysqlUtils()
    op1= f" and pitcher='{pitcher}'" if pitcher else ''


    sql=f"""select  pitcher,
        total_cost cost,
        total_amount amount,
        if(total_cost=0,0,total_amount/total_cost) roi, 
        channel_count,
        on_channel_count,
       off_channel_count,
       this_month_cost this_month_cost,
    this_month_amount this_month_amount,
    this_month_roi this_month_roi,
    last_month_cost last_month_cost,
    last_month_amount last_month_amount,
    last_month_roi last_month_roi,
    last_month_far_amount last_month_far_amount,
       follow_user,
    last_month_far_roi last_month_far_roi
       from  dm_pitcher_daily_overview a 
left join (
select sum(cost) total_cost,sum(reg_amount) total_amount,pitcher pitcher2 from dw_pitcher_trend group by pitcher) b on pitcher=pitcher2
where 1=1 {op1}
    """
    print(sql)
    return db.dm.getData_json(sql)


def get_channel_overview(channel,pitcher,start,end,page,page_size,order_by,order):
    db=MysqlUtils()
    if pitcher:
        op1 = f" and pitcher='{pitcher}'" if get_channels_from_user(
            pitcher).__len__() == 0 else f" and channel in {get_channels_from_user(pitcher)}"
    else:
        op1 = ''
    op2 = f" and channel='{channel}'" if channel else ''
    op3 = f" and dt>='{start}' " if start else ''
    op4 = f" and dt<='{end}' " if end else ''
    op5 = f" order by {order_by} {order}" if order_by and order else ''
    sql=f"""select channel,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,
       cost,
       first_order_count,first_order_user,
       first_order_amount,
       order_count,order_user,
       order_amount,
       order_amount-first_order_amount 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,
       reg_order_amount reg_user_amount,
       round(if(cost=0,0,first_order_amount/cost),4) day_roi,
       round(reg_order_amount/cost,4) roi,
        reg_order_user_again,
        reg_order_user,
       round(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4)  old_user_once_order_rate, 
        pitcher,stage,book,
        round(cost/first_order_user,2) first_order_user_per_cost, 
        round(reg_order_amount/reg_order_user,2) user_per_amount, 
        round(follow_user/click_count,4) click_follow_rate, 
        round(reg_order_user/follow_user,4) follow_order_rate 
from dw_channel_daily where 1=1 {op1} {op2} {op3} {op4} {op5} """

    print(sql)
    sumsql=f"""select '总计' date,
                sum(view_count) view_count,
                sum(click_count) click_count,
                round(sum(click_count)/sum(view_count),4) click_rate,
sum(follow_user) follow_user,
round(sum(follow_user)/sum(click_count),4) follow_rate,
round(sum(cost)/sum(follow_user),2) follow_per_cost,
round(sum(first_order_count)/sum(click_count),4) order_rate,
round(sum(cost)/sum(first_order_user),2) order_per_cost,
sum(cost) cost,
sum(first_order_count) first_order_count,
sum(first_order_user) first_order_user,
sum(first_order_amount) first_order_amount,
sum(order_count) order_count,
sum(order_user) order_user,
sum(order_amount) order_amount,
sum(old_order_amount) old_order_amount,
round(sum(first_order_amount)/sum(first_order_user),2) first_amount_per_user,
round(sum(first_order_amount)/sum(follow_user),2) amount_per_follow,
round(sum(cost)/sum(first_order_user),2) first_cost_per_user,
round(sum(first_order_user)/sum(follow_user),4) new_user_order_rate,
sum(reg_user_amount) reg_user_amount,
round(sum(first_order_amount)/sum(cost),4) day_roi,
round(sum(reg_user_amount)/sum(cost),4) roi,
round(sum(reg_order_user_again)/sum(reg_order_user),4)  old_user_once_order_rate,     
        round(sum(cost)/sum(first_order_user),2) first_order_user_per_cost,  
        round(sum(reg_user_amount)/sum(reg_order_user),2) user_per_amount, 
        round(sum(follow_user)/sum(click_count),4) click_follow_rate, 
        round(sum(reg_order_user)/sum(follow_user),4) follow_order_rate 


from ({sql}) a

    """


    return getLimitSumData(db.dm,sql,sumsql,page,page_size)


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

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

        from dw_daily_channel where channel='{channel}' and dt='{date}'
       """
    # print(sql)
    data=ck.execute(sql)
    # print(data)
    key1=['date','channel','book','pitcher','stage','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)
    # print(json1)
    sql2=f"""select
        1 as 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
            from order where channel='{channel}' and date='{date}'
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id,date) a
union all
select
        2 as 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
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),1)
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id) a
union all
select
        3 as 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
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),2)
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id) a
union all
select
        4 as 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
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),3)
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id) a
union all
select
        5 as 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
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),4)
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id) a
union all
select
        6 as 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
            from order where channel='{channel}' and date>='{date}' and date<=addDays(toDate('{date}'),5)
            and formatDateTime(reg_time,'%Y-%m-%d')='{date}'  group by user_id) a
union all
select
        7 as 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
            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) a"""

    df=ck.execute(sql2)
    print(df)
    # 补全
    # 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))
    print(df)

    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"]/df[0][1],2) if df[0][i]!=0 else 0
        d['d1'] = d1

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

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

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

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

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

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

        li.append(d)
    print(li)

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


def get_channel_active(channel,pitcher,start,end,page,page_size,order_by,order):
    ck = CkUtils()
    channel_op=f" and channel='{channel}' " if channel!='' else ''
    if pitcher != '赞象 admin':
        if get_channels_from_user(pitcher).__len__() == 0:
            pitcher_op = f"  and pitcher='{pitcher}'"
        else:
            pitcher_op = f" and channel in {get_channels_from_user(pitcher)}"

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

       from (

select dt,book,cost,pitcher,channel,stage,
       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 1=1 {channel_op}  and dt>='{start}' and dt<='{end}' {pitcher_op}) a
left outer join (

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

    total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
    sql+=f" order by {order_by} {order} limit {(page-1)*page_size},{page_size} "
    data = ck.execute(sql)
    print(sql)
    print(data)

    key=['stage','pitcher','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):
    db=MysqlUtils()
    if pitcher:
        op1 = f" and pitcher='{pitcher}'" if get_channels_from_user(
            pitcher).__len__() == 0 else f" and channel in {get_channels_from_user(pitcher)}"
    else:
        op1 = ''
    op2 = f" and channel='{channel}'" if channel else ''
    op3 = f" and dt>='{start}' " if start else ''
    op4 = f" and dt<='{end}' " if end else ''
    op5 = f" order by {order_by} {order}" if order_by and order else ''

    sql=f"""select stage,pitcher,a.channel,a.dt date,book,
        0 require_roi,0 require_mult,
--         round(r1*require_mult-cost,2) expect_profit,
        0 expect_profit,
        cost,
        first_order_amount first_amount,
        reg_order_amount reg_amount,
        reg_order_amount-cost profit,
        follow_user new_follow_user,
        round(reg_order_amount/cost,4) roi,
        round(first_order_amount/cost) first_roi,
        round(cost/follow_user,2) new_follow_per_cost,
        reg_order_user order_user,
       reg_order_count order_count,
       round(cost/reg_order_user ,2) order_user_per_cost,
       concat(da1,',',da1/cost,',', 0,',',1) d1,
       concat(da2-da1,',',da2/cost,',', (da2-da1)/cost,',',da2/da1) d2,
       concat(da3-da2,',',da3/cost,',', (da3-da2)/cost,',',da3/da1) d3,
       concat(da4-da3,',',da4/cost,',', (da4-da3)/cost,',',da4/da1) d4,
       concat(da5-da4,',',da5/cost,',', (da5-da4)/cost,',',da5/da1) d5,
       concat(da6-da5,',',da6/cost,',', (da6-da5)/cost,',',da6/da1) d6,
       concat(da7-da6,',',da7/cost,',', (da7-da6)/cost,',',da7/da1) d7,
       concat(da8-da7,',',da8/cost,',', (da8-da7)/cost,',',da8/da1) d8,
       concat(da9-da8,',',da9/cost,',', (da9-da8)/cost,',',da9/da1) d9,
concat(da10-da9 ,',' ,da10/cost ,',', (da10-da9)/cost ,',' ,da10/da1) d10,
concat(da11-da10 ,',' ,da11/cost ,',', (da11-da10)/cost ,',' ,da11/da1) d11,
concat(da12-da11 ,',' ,da12/cost ,',', (da12-da11)/cost ,',' ,da12/da1) d12,
concat(da13-da12 ,',' ,da13/cost ,',', (da13-da12)/cost ,',' ,da13/da1) d13,
concat(da14-da13 ,',' ,da14/cost ,',', (da14-da13)/cost ,',' ,da14/da1) d14,
concat(da15-da14 ,',' ,da15/cost ,',', (da15-da14)/cost ,',' ,da15/da1) d15,
concat(da16-da15 ,',' ,da16/cost ,',', (da16-da15)/cost ,',' ,da16/da1) d16,
concat(da17-da16 ,',' ,da17/cost ,',', (da17-da16)/cost ,',' ,da17/da1) d17,
concat(da18-da17 ,',' ,da18/cost ,',', (da18-da17)/cost ,',' ,da18/da1) d18,
concat(da19-da18 ,',' ,da19/cost ,',', (da19-da18)/cost ,',' ,da19/da1) d19,
concat(da20-da19 ,',' ,da20/cost ,',', (da20-da19)/cost ,',' ,da20/da1) d20,
concat(da21-da20 ,',' ,da21/cost ,',', (da21-da20)/cost ,',' ,da21/da1) d21,
concat(da22-da21 ,',' ,da22/cost ,',', (da22-da21)/cost ,',' ,da22/da1) d22,
concat(da23-da22 ,',' ,da23/cost ,',', (da23-da22)/cost ,',' ,da23/da1) d23,
concat(da24-da23 ,',' ,da24/cost ,',', (da24-da23)/cost ,',' ,da24/da1) d24,
concat(da25-da24 ,',' ,da25/cost ,',', (da25-da24)/cost ,',' ,da25/da1) d25,
concat(da26-da25 ,',' ,da26/cost ,',', (da26-da25)/cost ,',' ,da26/da1) d26,
concat(da27-da26 ,',' ,da27/cost ,',', (da27-da26)/cost ,',' ,da27/da1) d27,
concat(da28-da27 ,',' ,da28/cost ,',', (da28-da27)/cost ,',' ,da28/da1) d28,
concat(da29-da28 ,',' ,da29/cost ,',', (da29-da28)/cost ,',' ,da29/da1) d29,
concat(da30-da29 ,',' ,da30/cost ,',', (da30-da29)/cost ,',' ,da30/da1) d30,
concat(da31-da30 ,',' ,da31/cost ,',', (da31-da30)/cost ,',' ,da31/da1) d31,
concat(da32-da31 ,',' ,da32/cost ,',', (da32-da31)/cost ,',' ,da32/da1) d32,
concat(da33-da32 ,',' ,da33/cost ,',', (da33-da32)/cost ,',' ,da33/da1) d33,
concat(da34-da33 ,',' ,da34/cost ,',', (da34-da33)/cost ,',' ,da34/da1) d34,
concat(da35-da34 ,',' ,da35/cost ,',', (da35-da34)/cost ,',' ,da35/da1) d35,
concat(da36-da35 ,',' ,da36/cost ,',', (da36-da35)/cost ,',' ,da36/da1) d36,
concat(da37-da36 ,',' ,da37/cost ,',', (da37-da36)/cost ,',' ,da37/da1) d37,
concat(da38-da37 ,',' ,da38/cost ,',', (da38-da37)/cost ,',' ,da38/da1) d38,
concat(da39-da38 ,',' ,da39/cost ,',', (da39-da38)/cost ,',' ,da39/da1) d39,
concat(da40-da39 ,',' ,da40/cost ,',', (da40-da39)/cost ,',' ,da40/da1) d40,
concat(da41-da40 ,',' ,da41/cost ,',', (da41-da40)/cost ,',' ,da41/da1) d41,
concat(da42-da41 ,',' ,da42/cost ,',', (da42-da41)/cost ,',' ,da42/da1) d42,
concat(da43-da42 ,',' ,da43/cost ,',', (da43-da42)/cost ,',' ,da43/da1) d43,
concat(da44-da43 ,',' ,da44/cost ,',', (da44-da43)/cost ,',' ,da44/da1) d44,
concat(da45-da44 ,',' ,da45/cost ,',', (da45-da44)/cost ,',' ,da45/da1) d45,
concat(da46-da45 ,',' ,da46/cost ,',', (da46-da45)/cost ,',' ,da46/da1) d46,
concat(da47-da46 ,',' ,da47/cost ,',', (da47-da46)/cost ,',' ,da47/da1) d47,
concat(da48-da47 ,',' ,da48/cost ,',', (da48-da47)/cost ,',' ,da48/da1) d48,
concat(da49-da48 ,',' ,da49/cost ,',', (da49-da48)/cost ,',' ,da49/da1) d49,
concat(da50-da49 ,',' ,da50/cost ,',', (da50-da49)/cost ,',' ,da50/da1) d50,
concat(da51-da50 ,',' ,da51/cost ,',', (da51-da50)/cost ,',' ,da51/da1) d51,
concat(da52-da51 ,',' ,da52/cost ,',', (da52-da51)/cost ,',' ,da52/da1) d52,
concat(da53-da52 ,',' ,da53/cost ,',', (da53-da52)/cost ,',' ,da53/da1) d53,
concat(da54-da53 ,',' ,da54/cost ,',', (da54-da53)/cost ,',' ,da54/da1) d54,
concat(da55-da54 ,',' ,da55/cost ,',', (da55-da54)/cost ,',' ,da55/da1) d55,
concat(da56-da55 ,',' ,da56/cost ,',', (da56-da55)/cost ,',' ,da56/da1) d56,
concat(da57-da56 ,',' ,da57/cost ,',', (da57-da56)/cost ,',' ,da57/da1) d57,
concat(da58-da57 ,',' ,da58/cost ,',', (da58-da57)/cost ,',' ,da58/da1) d58,
concat(da59-da58 ,',' ,da59/cost ,',', (da59-da58)/cost ,',' ,da59/da1) d59,
concat(da60-da59 ,',' ,da60/cost ,',', (da60-da59)/cost ,',' ,da60/da1) d60,
concat(dm3-da60 ,',' ,dm3/cost ,',', (dm3-da60)/cost ,',' ,dm3/da1) m3,
concat(dm4-dm3 ,',' ,dm4/cost ,',', (dm4-dm3)/cost ,',' ,dm4/da1) m4,
concat(dm5-dm4 ,',' ,dm5/cost ,',', (dm5-dm4)/cost ,',' ,dm5/da1) m5 
from ( select * from  dw_channel where 1=1 {op1} {op2} {op3} {op4} ) a
left join  dw_channel_amount_daily b on a.channel=b.channel and a.dt=b.dt
 {op5}
"""

    sumsql=f"""select '总计' date,
               sum(cost) cost,
               sum(reg_amount)  reg_amount,
               sum(first_amount)  first_amount,
                sum(profit) profit,
               sum(new_follow_user) new_follow_user,
                sum(reg_amount)/sum(cost) roi,
                sum(first_amount)/sum(cost) first_roi,
                sum(cost)/sum(new_follow_user) new_follow_per_cost,
                sum(order_user) order_user,
                sum(order_count) order_count,
                sum(cost)/sum(order_count) order_user_per_cost,
                sum(expect_profit) expect_profit
                from ({sql}) a
                """
    data,total,total_data=getLimitSumData(db.dm,sql,sumsql,page,page_size)

    def parse(str):
        li=str.split(',')
        li[0]=round(float(li[0]),2)
        li[1]=round(float(li[1]),4)
        li[2]=round(float(li[2]),4)
        li[3]=round(float(li[3]),2)
        return dict(zip(['order','roi','add','mult'],li))

    for i in data:
        for x in i:
            if x 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','d31','d32','d33','d34','d35','d36','d37','d38',
         'd39','d40','d41','d42','d43','d44','d45','d46','d47','d48','d49','d50','d51','d52','d53','d54','d55','d56','d57','d58','d59',
         'd60','m3','m4','m5']:
                i[x]=parse(i[x]) if i[x] is not None else {}
    print(data)

    return data,total,total_data

def get_channel_summary(channel,pitcher,page,page_size,order_by,order,state,location,start,end):
    db=MysqlUtils()

    op1=f" and channel='{channel}'" if channel else ''

    if pitcher:
        op2=f" and pitcher='{pitcher}'" if get_channels_from_user(pitcher).__len__()==0 else f" and channel in {get_channels_from_user(pitcher)}"
    else:
        op2=''

    op3=f" and channel='{channel}'" if channel else ''
    op4=f" and location='{location}' " if location else ''
    op5=f" and state='{state}'" if state else ''
    op6=f" and dt>='{start}'" if start else ''
    op7=f" and dt<='{end}'" if end else ''


    sql = f"""SELECT channel,
                if(end>date_sub(now(),interval 10 day),'在投','停投') state,
                location,start,end,total_cost,total_amount,
                total_amount-total_cost profit,
                if(total_cost=0,0,round(total_amount/total_cost,4)) roi,
                follow_user,
                if(follow_user=0,0,round(total_cost/follow_user,2)) follow_per_cost,
                order_user,
                if(follow_user=0,0,round(order_user/follow_user,4)) order_tran_rate,
                if(order_user=0,0,round(total_cost/order_user,2))  order_tran_cost,
                pitcher,stage
            FROM
                (select
                    channel,pitcher,stage,
                    case when type ='vx' then 'MP' when type ='qq' then 'GDT' end location,
                    min(if(cost>0,dt,null)) start,
                    max(if(cost>0,dt,null)) end,
                    sum(cost) total_cost,
                    sum(reg_order_amount) total_amount,
                    sum(follow_user) follow_user,
					sum(reg_order_user) order_user
               from dw_channel_daily   where 1=1 {op1} {op2} {op3} {op6} {op7} GROUP BY channel,type,pitcher,stage) a having 1=1  {op4} {op5}  ORDER BY {order_by} {order}
                """
    print(sql)
    sumsql=f"""select '总计' channel,
            sum(total_cost) total_cost,
            sum(total_amount) total_amount,sum(profit) profit, 
            round(sum(total_amount)/sum(total_cost),4) roi,
            sum(follow_user) follow_user,
            sum(order_user) order_user,
            round(sum(total_cost)/sum(follow_user),2) follow_per_cost,
            round(sum(order_user)/sum(follow_user),4) order_tran_rate,
            round(sum(total_cost)/sum(order_user),2)  order_tran_cost 
            from ({sql}) a
            
    """

    return getLimitSumData(db.dm,sql,sumsql,page,page_size)


def get_pitcher_trend(pitcher,start=None,end=None,page=None,page_size=None,order_by=None,order=None):

    db=MysqlUtils()

    op1=f" and pitcher='{pitcher}'"
    op2 = f" and dt>='{start}' " if start else ''
    op3 = f" and dt<='{end}' " if end else ''
    op4 = f" order by {order_by} {order}" if order_by and order else ''

    sql=f"""select dt,pitcher,
            cost,
            reg_amount,
            d1 first_amount,
            round(d1/cost,4) first_roi,
            round(reg_amount/cost,4) roi,
            d7 reg_amount7,
            round(d7/cost,4) roi7,
            d30 reg_amount30,
            round(d30/cost,4) roi30,
            reg_amount-cost profit,
            inva_cost expect_profit,
            CONCAT(d1,",",0,',',round(d1/cost,4)) d1,
            CONCAT(d2-d1,",",round((d2-d1)/cost,4),',',round(d2/cost,4)) d2,
            CONCAT(d3-d2,",",round((d3-d2)/cost,4),',',round(d3/cost,4)) d3,
            CONCAT(d4-d3,",",round((d4-d3)/cost,4),',',round(d4/cost,4)) d4,
            CONCAT(d5-d4,",",round((d5-d4)/cost,4),',',round(d5/cost,4)) d5,
            CONCAT(d6-d5,",",round((d6-d5)/cost,4),',',round(d6/cost,4)) d6,
            CONCAT(d7-d6,",",round((d7-d6)/cost,4),',',round(d7/cost,4)) d7,
            CONCAT(d8-d7,",",round((d8-d7)/cost,4),',',round(d8/cost,4)) d8,
            CONCAT(d9-d8,",",round((d9-d8)/cost,4),',',round(d9/cost,4)) d9,
            CONCAT(d10-d9,",",round((d10-d9)/cost,4),',',round(d10/cost,4)) d10,
            CONCAT(d11-d10,",",round((d11-d10)/cost,4),',',round(d11/cost,4)) d11,
            CONCAT(d12-d11,",",round((d12-d11)/cost,4),',',round(d12/cost,4)) d12,
            CONCAT(d13-d12,",",round((d13-d12)/cost,4),',',round(d13/cost,4)) d13,
            CONCAT(d14-d13,",",round((d14-d13)/cost,4),',',round(d14/cost,4)) d14,
            CONCAT(d15-d14,",",round((d15-d14)/cost,4),',',round(d15/cost,4)) d15,
            CONCAT(d16-d15,",",round((d16-d15)/cost,4),',',round(d16/cost,4)) d16,
            CONCAT(d17-d16,",",round((d17-d16)/cost,4),',',round(d17/cost,4)) d17,
            CONCAT(d18-d17,",",round((d18-d17)/cost,4),',',round(d18/cost,4)) d18,
            CONCAT(d19-d18,",",round((d19-d18)/cost,4),',',round(d19/cost,4)) d19,
            CONCAT(d20-d19,",",round((d20-d19)/cost,4),',',round(d20/cost,4)) d20,
            CONCAT(d21-d20,",",round((d21-d20)/cost,4),',',round(d21/cost,4)) d21,
            CONCAT(d22-d21,",",round((d22-d21)/cost,4),',',round(d22/cost,4)) d22,
            CONCAT(d23-d22,",",round((d23-d22)/cost,4),',',round(d23/cost,4)) d23,
            CONCAT(d24-d23,",",round((d24-d23)/cost,4),',',round(d24/cost,4)) d24,
            CONCAT(d25-d24,",",round((d25-d24)/cost,4),',',round(d25/cost,4)) d25,
            CONCAT(d26-d25,",",round((d26-d25)/cost,4),',',round(d26/cost,4)) d26,
            CONCAT(d27-d26,",",round((d27-d26)/cost,4),',',round(d27/cost,4)) d27,
            CONCAT(d28-d27,",",round((d28-d27)/cost,4),',',round(d28/cost,4)) d28,
            CONCAT(d29-d28,",",round((d29-d28)/cost,4),',',round(d29/cost,4)) d29,
            CONCAT(d30-d29,",",round((d30-d29)/cost,4),',',round(d30/cost,4)) d30 
            from dw_pitcher_trend  where 1=1 {op1} {op2} {op3} {op4}
            """

    sumSql=f"""select '总计' dt,sum(cost) cost,
    sum(reg_amount) reg_amount,
    round(sum(first_amount)/sum(cost),4) first_roi,
    round(sum(reg_amount)/sum(cost),4) roi,
    sum(profit) profit,
    round(sum(reg_amount7)/sum(cost),4) roi7,
    sum(reg_amount30) reg_amount30, 
    round(sum(reg_amount30)/sum(cost),4) roi30,
    sum(expect_profit) expect_profit
    from ({sql}) a
    """

    data,total,total_data=getLimitSumData(db.dm,sql,sumSql,page,page_size)
    print(data)
    def parse(str):
        li=str.split(',')
        li[0]=round(float(li[0]),2)
        li[1]=round(float(li[1]),4)
        li[2]=round(float(li[2]),4)
        return dict(zip(['amount','add','roi'],li))

    for i in data:
        for k,v in i.items():
            if k 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']:
                i[k]= parse(v) if i["cost"]!=0 else {}

    return data,total,total_data




if __name__ == '__main__':
    get_pitcher_trend(pitcher="陈凯")