QcWebServerGame/data_manage/pitcher_panel.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
from model import UserAuthUtils
from model.UserAuthUtils import super_auth

du = DateUtils()

from data_manage.operate import get_pitcher

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):
    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(user_id, channel, pitcher, stage, book, start, end, page, page_size, order_by, order):
    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 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 ''
    op6 = f" and stage='{stage}'" if stage else ''
    op7 = f" and book='{book}'" if book else ''

    sql = f"""select channel,dt date,reg_num,
           round(if(reg_num=0,0,cost/reg_num),2) reg_per_cost,
        create_user_num,
       round(if(create_user_num=0,0,cost/create_user_num),2) create_user_per_cost,        
       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,
       game_user_sum,
       third_stay_rate,
       second_stay_rate,
       today_active_user_rate,
       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/reg_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,type,
        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 where 1=1 {op}  {op1} {op2} {op3} {op4} {op6} {op7} {op5} """

    sumsql = f"""select '总计' date,
    sum(reg_num) reg_num,
    
    sum(game_user_sum) game_user_sum,
    round(if(sum(reg_num)=0,0,sum(reg_num*third_stay_rate)/sum(reg_num)),2) third_stay_rate,
    round(if(sum(reg_num)=0,0,sum(reg_num*second_stay_rate)/sum(reg_num)),2) second_stay_rate,
    round(if(sum(game_user_sum)=0,0,sum(game_user_sum*today_active_user_rate)/sum(game_user_sum)),2) today_active_user_rate,
    
    
    round(if(sum(reg_num)=0,0,sum(cost)/sum(reg_num)),2) reg_per_cost,
    sum(create_user_num) create_user_num,
    round(if(sum(create_user_num)=0,0,sum(cost)/sum(create_user_num)),2) create_user_per_cost,   
                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):
    ck = CkUtils()
    db = MysqlUtils()

    sql = f"""select dt date,
       channel,book,pitcher,stage,
       cost,
       reg_order_amount 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,
       if(reg_order_user=0,0,order_count/reg_order_user) order_count

        from dw_channel where channel='{channel}' and dt='{date}'
       """
    # print(sql)
    j = db.dm.getData_json(sql)

    if len(j) == 0:
        return None
    else:
        json1 = j[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 status=2
            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 status=2
            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 status=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 status=2
            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 status=2
            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 status=2
            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 status=2
            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(user_id, channel, pitcher, start, end, page, page_size, order_by, order):
    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 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,book,stage,pitcher,cost,
    reg_order_amount reg_amount,
    dc7,dc30,
    round(reg_order_amount/cost,4) roi,
    follow_user new_follow_user,
    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,   
round(dc7/follow_user,4) day7_avg_act_rate,
round(cost/dc7,2) day7_avg_act_per_cost,
round(dc30/follow_user,4) day30_avg_act_rate,
round(cost/dc30,2)   day30_avg_act_cost,
round(cost/reg_order_user,2) act_per_cost,
concat(dc1,',',cost/dc1,',',dc1/reg_order_user) d1,
concat(dc2-dc1,',',cost/dc2,',',(dc2-dc1)/reg_order_user) d2,
concat(dc3-dc2,',',cost/dc3,',',(dc3-dc2)/reg_order_user) d3,
concat(dc4-dc3,',',cost/dc4,',',(dc4-dc3)/reg_order_user) d4,
concat(dc5-dc4,',',cost/dc5,',',(dc5-dc4)/reg_order_user) d5,
concat(dc6-dc5,',',cost/dc6,',',(dc6-dc5)/reg_order_user) d6,
concat(dc7-dc6,',',cost/dc7,',',(dc7-dc6)/reg_order_user) d7,
concat(dc8-dc7,',',cost/dc8,',',(dc8-dc7)/reg_order_user) d8,
concat(dc9-dc8,',',cost/dc9,',',(dc9-dc8)/reg_order_user) d9,
concat(dc10-dc9,',',cost/dc10,',',(dc10-dc9)/reg_order_user) d10,
concat(dc11-dc10,',',cost/dc11,',',(dc11-dc10)/reg_order_user) d11,
concat(dc12-dc11,',',cost/dc12,',',(dc12-dc11)/reg_order_user) d12,
concat(dc13-dc12,',',cost/dc13,',',(dc13-dc12)/reg_order_user) d13,
concat(dc14-dc13,',',cost/dc14,',',(dc14-dc13)/reg_order_user) d14,
concat(dc15-dc14,',',cost/dc15,',',(dc15-dc14)/reg_order_user) d15,
concat(dc16-dc15,',',cost/dc16,',',(dc16-dc15)/reg_order_user) d16,
concat(dc17-dc16,',',cost/dc17,',',(dc17-dc16)/reg_order_user) d17,
concat(dc18-dc17,',',cost/dc18,',',(dc18-dc17)/reg_order_user) d18,
concat(dc19-dc18,',',cost/dc19,',',(dc19-dc18)/reg_order_user) d19,
concat(dc20-dc19,',',cost/dc20,',',(dc20-dc19)/reg_order_user) d20,
concat(dc21-dc20,',',cost/dc21,',',(dc21-dc20)/reg_order_user) d21,
concat(dc22-dc21,',',cost/dc22,',',(dc22-dc21)/reg_order_user) d22,
concat(dc23-dc22,',',cost/dc23,',',(dc23-dc22)/reg_order_user) d23,
concat(dc24-dc23,',',cost/dc24,',',(dc24-dc23)/reg_order_user) d24,
concat(dc25-dc24,',',cost/dc25,',',(dc25-dc24)/reg_order_user) d25,
concat(dc26-dc25,',',cost/dc26,',',(dc26-dc25)/reg_order_user) d26,
concat(dc27-dc26,',',cost/dc27,',',(dc27-dc26)/reg_order_user) d27,
concat(dc28-dc27,',',cost/dc28,',',(dc28-dc27)/reg_order_user) d28,
concat(dc29-dc28,',',cost/dc29,',',(dc29-dc28)/reg_order_user) d29,
concat(dc30-dc29,',',cost/dc30,',',(dc30-dc29)/reg_order_user) d30 
from (select * from dw_channel where cost>0 {op} {op1} {op2} {op3} {op4}) a 
left join dw_channel_user_daily b using(dt,channel) {op5}
"""

    data, total = getLimitData(db.dm, sql, page, page_size)

    def parse(str):
        li = str.split(',')
        li[0] = round(float(li[0]), 0)
        li[1] = round(float(li[1]), 2)
        li[2] = round(float(li[2]), 4)
        return dict(zip(['act_user', 'act_cost_per_cost', 'act_rate'], 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']:
                i[x] = parse(i[x]) if i[x] is not None else {}

    return data, total


def get_channel_order_trend(user_id, channel, pitcher, start, end, page, page_size, order_by, order):
    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 ''
    if not channel:
        op2 = ''
    elif type(channel) is str:
        op2 = f" and channel='{channel}'" if channel else ''
    elif len(channel) > 1:
        op2 = f" and  channel in {str(tuple(channel))}"
    else:
        op2 = f" and channel='{channel[0]}'" 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 ''

    base_sql_multi_channel = f'''
            ( select a.dt,a.create_user_num,a.reg_num,group_concat(a.channel) channel ,group_concat(a.pitcher ) pitcher ,
        group_concat(a.stage ) stage ,group_concat(a.platform) platform ,
        group_concat(a.book) book, group_concat(a.type) type,sum(a.order_count ) order_count ,
        sum(a.order_user ) order_user ,sum(a.order_amount ) order_amount ,
        sum(a.reg_order_amount) reg_order_amount,
        sum(a.first_order_amount ) first_order_amount ,sum(a.view_count) view_count,
        sum(a.click_count) click_count ,sum(a.follow_user) follow_user ,
        sum(game_user_sum) game_user_sum,
        round(if(sum(reg_num)=0,0,sum(reg_num*third_stay_rate)/sum(reg_num)),2) third_stay_rate,
        round(if(sum(reg_num)=0,0,sum(reg_num*second_stay_rate)/sum(reg_num)),2) second_stay_rate,
        round(if(sum(game_user_sum)=0,0,sum(game_user_sum*today_active_user_rate)/sum(game_user_sum)),2) today_active_user_rate,
        sum(a.cost) cost,sum(a.reg_order_count) reg_order_count,
        sum(a.reg_order_user) reg_order_user ,sum(a.web_view_count) web_view_count ,
        sum(a.platform_view_count) platform_view_count ,sum(a.web_order_count ) web_order_count ,
        sum(a.reg_order_user_again) reg_order_user_again ,sum(a.reg_order_user_again3 ) reg_order_user_again3 ,
        sum(a.reg_order_user_again4) reg_order_user_again4,sum(a.reg_order_user_again5) reg_order_user_again5,
        sum(a.reg_order_user_again6) reg_order_user_again6,sum(b.da1)  as da1,
        sum(b.da2)  as da2,
        sum(b.da3)  as da3,
        sum(b.da4)  as da4,
        sum(b.da5)  as da5,
        sum(b.da6)  as da6,
        sum(b.da7)  as da7,
        sum(b.da8)  as da8,
        sum(b.da9)  as da9,
        sum(b.da10)  as da10,
        sum(b.da11)  as da11,
        sum(b.da12)  as da12,
        sum(b.da13)  as da13,
        sum(b.da14)  as da14,
        sum(b.da15)  as da15,
        sum(b.da16)  as da16,
        sum(b.da17)  as da17,
        sum(b.da18)  as da18,
        sum(b.da19)  as da19,
        sum(b.da20)  as da20,
        sum(b.da21)  as da21,
        sum(b.da22)  as da22,
        sum(b.da23)  as da23,
        sum(b.da24)  as da24,
        sum(b.da25)  as da25,
        sum(b.da26)  as da26,
        sum(b.da27)  as da27,
        sum(b.da28)  as da28,
        sum(b.da29)  as da29,
        sum(b.da30)  as da30,
        sum(b.da31)  as da31,
        sum(b.da32)  as da32,
        sum(b.da33)  as da33,
        sum(b.da34)  as da34,
        sum(b.da35)  as da35,
        sum(b.da36)  as da36,
        sum(b.da37)  as da37,
        sum(b.da38)  as da38,
        sum(b.da39)  as da39,
        sum(b.da40)  as da40,
        sum(b.da41)  as da41,
        sum(b.da42)  as da42,
        sum(b.da43)  as da43,
        sum(b.da44)  as da44,
        sum(b.da45)  as da45,
        sum(b.da46)  as da46,
        sum(b.da47)  as da47,
        sum(b.da48)  as da48,
        sum(b.da49)  as da49,
        sum(b.da50)  as da50,
        sum(b.da51)  as da51,
        sum(b.da52)  as da52,
        sum(b.da53)  as da53,
        sum(b.da54)  as da54,
        sum(b.da55)  as da55,
        sum(b.da56)  as da56,
        sum(b.da57)  as da57,
        sum(b.da58)  as da58,
        sum(b.da59)  as da59,
        sum(b.da60)  as da60,
        sum(b.dm3)  as dm3,
        sum(b.dm4)  as dm4,
        sum(b.dm5)  as dm5,
        sum(c.node) as node,
        group_concat(annual_mult) as annual_mult
        from 
         ( select * from  dw_channel where cost+reg_order_amount>0
         {op} {op1} {op2} {op3} {op4}   ) a
            left join  dw_channel_amount_daily b on a.channel=b.channel 
            and a.dt=b.dt 
            left join src_book_info c on a.dt=c.dt and a.book=c.book
            and a.type=c.type and a.platform=c.platform
        group by a.dt ) a
        '''

    base_sql_singel_channel = f'''
        ( select * from  dw_channel where cost+reg_order_amount>0  {op} {op1} {op2} {op3} {op4} ) a
        left join  dw_channel_amount_daily b on a.channel=b.channel and a.dt=b.dt 
        left join src_book_info c on a.dt=c.dt and a.book=c.book  and a.type=c.type and a.platform=c.platform
        '''
    base_sql = base_sql_multi_channel if channel and len(channel) > 1 else base_sql_singel_channel

    sql = f"""select row_number() over() as id,
                stage,pitcher,a.channel,a.dt date,a.book,cost,
                a.type,
                a.reg_num,
                 round(if(a.reg_num=0,0,cost/a.reg_num),2) reg_per_cost,
                a.create_user_num,
               round(if( a.create_user_num=0,0,cost/ a.create_user_num),2) create_user_per_cost,                 
                node/100 require_roi,round(100/node,2) require_mult,
                round(first_order_amount*100/node-cost,2) expect_profit,
                annual_mult,
                round(first_order_amount*annual_mult -cost,2) annual_expect_profit,
                first_order_amount first_amount,
                reg_order_amount reg_amount,
                reg_order_amount-cost profit,
                follow_user new_follow_user,
                game_user_sum,
                third_stay_rate,
                second_stay_rate,
                today_active_user_rate,
                round(reg_order_amount/cost,4) roi,
                round(first_order_amount/cost,4) 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,',',if(da1=0,1,da2/da1) ) d2,
               concat(da3-da2,',',da3/cost,',', (da3-da2)/cost,',',if(da1=0,1,da3/da1)) d3,
               concat(da4-da3,',',da4/cost,',', (da4-da3)/cost,',',if(da1=0,1,da4/da1)) d4,
               concat(da5-da4,',',da5/cost,',', (da5-da4)/cost,',',if(da1=0,1,da5/da1)) d5,
               concat(da6-da5,',',da6/cost,',', (da6-da5)/cost,',',if(da1=0,1,da6/da1)) d6,
               concat(da7-da6,',',da7/cost,',', (da7-da6)/cost,',',if(da1=0,1,da7/da1)) d7,
               concat(da8-da7,',',da8/cost,',', (da8-da7)/cost,',',if(da1=0,1,da8/da1)) d8,
               concat(da9-da8,',',da9/cost,',', (da9-da8)/cost,',',if(da1=0,1,da9/da1)) d9,
        concat(da10-da9 ,',' ,da10/cost ,',', (da10-da9)/cost ,',' ,if(da1=0,1,da10/da1)) d10,
        concat(da11-da10 ,',' ,da11/cost ,',', (da11-da10)/cost ,',' ,if(da1=0,1,da11/da1)) d11,
        concat(da12-da11 ,',' ,da12/cost ,',', (da12-da11)/cost ,',' ,if(da1=0,1,da12/da1)) d12,
        concat(da13-da12 ,',' ,da13/cost ,',', (da13-da12)/cost ,',' ,if(da1=0,1,da13/da1)) d13,
        concat(da14-da13 ,',' ,da14/cost ,',', (da14-da13)/cost ,',' ,if(da1=0,1,da14/da1)) d14,
        concat(da15-da14 ,',' ,da15/cost ,',', (da15-da14)/cost ,',' ,if(da1=0,1,da15/da1)) d15,
        concat(da16-da15 ,',' ,da16/cost ,',', (da16-da15)/cost ,',' ,if(da1=0,1,da16/da1)) d16,
        concat(da17-da16 ,',' ,da17/cost ,',', (da17-da16)/cost ,',' ,if(da1=0,1,da17/da1)) d17,
        concat(da18-da17 ,',' ,da18/cost ,',', (da18-da17)/cost ,',' ,if(da1=0,1,da18/da1)) d18,
        concat(da19-da18 ,',' ,da19/cost ,',', (da19-da18)/cost ,',' ,if(da1=0,1,da19/da1)) d19,
        concat(da20-da19 ,',' ,da20/cost ,',', (da20-da19)/cost ,',' ,if(da1=0,1,da20/da1)) d20,
        concat(da21-da20 ,',' ,da21/cost ,',', (da21-da20)/cost ,',' ,if(da1=0,1,da21/da1)) d21,
        concat(da22-da21 ,',' ,da22/cost ,',', (da22-da21)/cost ,',' ,if(da1=0,1,da22/da1)) d22,
        concat(da23-da22 ,',' ,da23/cost ,',', (da23-da22)/cost ,',' ,if(da1=0,1,da23/da1)) d23,
        concat(da24-da23 ,',' ,da24/cost ,',', (da24-da23)/cost ,',' ,if(da1=0,1,da24/da1)) d24,
        concat(da25-da24 ,',' ,da25/cost ,',', (da25-da24)/cost ,',' ,if(da1=0,1,da25/da1)) d25,
        concat(da26-da25 ,',' ,da26/cost ,',', (da26-da25)/cost ,',' ,if(da1=0,1,da26/da1)) d26,
        concat(da27-da26 ,',' ,da27/cost ,',', (da27-da26)/cost ,',' ,if(da1=0,1,da27/da1)) d27,
        concat(da28-da27 ,',' ,da28/cost ,',', (da28-da27)/cost ,',' ,if(da1=0,1,da28/da1)) d28,
        concat(da29-da28 ,',' ,da29/cost ,',', (da29-da28)/cost ,',' ,if(da1=0,1,da29/da1)) d29,
        concat(da30-da29 ,',' ,da30/cost ,',', (da30-da29)/cost ,',' ,if(da1=0,1,da30/da1)) d30,
        concat(da31-da30 ,',' ,da31/cost ,',', (da31-da30)/cost ,',' ,if(da1=0,1,da31/da1)) d31,
        concat(da32-da31 ,',' ,da32/cost ,',', (da32-da31)/cost ,',' ,if(da1=0,1,da32/da1)) d32,
        concat(da33-da32 ,',' ,da33/cost ,',', (da33-da32)/cost ,',' ,if(da1=0,1,da33/da1)) d33,
        concat(da34-da33 ,',' ,da34/cost ,',', (da34-da33)/cost ,',' ,if(da1=0,1,da34/da1)) d34,
        concat(da35-da34 ,',' ,da35/cost ,',', (da35-da34)/cost ,',' ,if(da1=0,1,da35/da1)) d35,
        concat(da36-da35 ,',' ,da36/cost ,',', (da36-da35)/cost ,',' ,if(da1=0,1,da36/da1)) d36,
        concat(da37-da36 ,',' ,da37/cost ,',', (da37-da36)/cost ,',' ,if(da1=0,1,da37/da1)) d37,
        concat(da38-da37 ,',' ,da38/cost ,',', (da38-da37)/cost ,',' ,if(da1=0,1,da38/da1)) d38,
        concat(da39-da38 ,',' ,da39/cost ,',', (da39-da38)/cost ,',' ,if(da1=0,1,da39/da1)) d39,
        concat(da40-da39 ,',' ,da40/cost ,',', (da40-da39)/cost ,',' ,if(da1=0,1,da40/da1)) d40,
        concat(da41-da40 ,',' ,da41/cost ,',', (da41-da40)/cost ,',' ,if(da1=0,1,da41/da1)) d41,
        concat(da42-da41 ,',' ,da42/cost ,',', (da42-da41)/cost ,',' ,if(da1=0,1,da42/da1)) d42,
        concat(da43-da42 ,',' ,da43/cost ,',', (da43-da42)/cost ,',' ,if(da1=0,1,da43/da1)) d43,
        concat(da44-da43 ,',' ,da44/cost ,',', (da44-da43)/cost ,',' ,if(da1=0,1,da44/da1)) d44,
        concat(da45-da44 ,',' ,da45/cost ,',', (da45-da44)/cost ,',' ,if(da1=0,1,da45/da1)) d45,
        concat(da46-da45 ,',' ,da46/cost ,',', (da46-da45)/cost ,',' ,if(da1=0,1,da46/da1)) d46,
        concat(da47-da46 ,',' ,da47/cost ,',', (da47-da46)/cost ,',' ,if(da1=0,1,da47/da1)) d47,
        concat(da48-da47 ,',' ,da48/cost ,',', (da48-da47)/cost ,',' ,if(da1=0,1,da48/da1)) d48,
        concat(da49-da48 ,',' ,da49/cost ,',', (da49-da48)/cost ,',' ,if(da1=0,1,da49/da1)) d49,
        concat(da50-da49 ,',' ,da50/cost ,',', (da50-da49)/cost ,',' ,if(da1=0,1,da50/da1)) d50,
        concat(da51-da50 ,',' ,da51/cost ,',', (da51-da50)/cost ,',' ,if(da1=0,1,da51/da1)) d51,
        concat(da52-da51 ,',' ,da52/cost ,',', (da52-da51)/cost ,',' ,if(da1=0,1,da52/da1)) d52,
        concat(da53-da52 ,',' ,da53/cost ,',', (da53-da52)/cost ,',' ,if(da1=0,1,da53/da1)) d53,
        concat(da54-da53 ,',' ,da54/cost ,',', (da54-da53)/cost ,',' ,if(da1=0,1,da54/da1)) d54,
        concat(da55-da54 ,',' ,da55/cost ,',', (da55-da54)/cost ,',' ,if(da1=0,1,da55/da1)) d55,
        concat(da56-da55 ,',' ,da56/cost ,',', (da56-da55)/cost ,',' ,if(da1=0,1,da56/da1)) d56,
        concat(da57-da56 ,',' ,da57/cost ,',', (da57-da56)/cost ,',' ,if(da1=0,1,da57/da1)) d57,
        concat(da58-da57 ,',' ,da58/cost ,',', (da58-da57)/cost ,',' ,if(da1=0,1,da58/da1)) d58,
        concat(da59-da58 ,',' ,da59/cost ,',', (da59-da58)/cost ,',' ,if(da1=0,1,da59/da1)) d59,
        concat(da60-da59 ,',' ,da60/cost ,',', (da60-da59)/cost ,',' ,if(da1=0,1,da60/da1)) d60,
        concat(dm3-da60 ,',' ,dm3/cost ,',', (dm3-da60)/cost ,',' ,if(da1=0,1,dm3/da1)) m3,
        concat(dm4-dm3 ,',' ,dm4/cost ,',', (dm4-dm3)/cost ,',' ,if(da1=0,1,dm4/da1)) m4,
        concat(dm5-dm4 ,',' ,dm5/cost ,',', (dm5-dm4)/cost ,',' ,if(da1=0,1,dm5/da1)) m5,
        da1,da2,da3,da4,da5,da6,da7,da8,da9,da10,da11,da12,da13,da14,da15,da16,da17,da18,da19,da20,
        da21,da22,da23,da24,da25,da26,da27,da28,da29,da30,da31,da32,da33,da34,da35,da36,da37,da38,da39,
        da40,da41,da42,da43,da44,da45,da46,da47,da48,da49,da50,da51,da52,da53,da54,da55,da56,da57,da58,
        da59,da60,dm3,dm4,dm5

        from {base_sql}

         {op5}
        """

    sumsql = f"""select concat(date_format(min(date),'%Y/%m/%d'),'~',date_format(max(date),'%Y/%m/%d')) date,
               sum(create_user_num) create_user_num,
               round(if( sum(create_user_num)=0,0,sum(cost)/ sum(create_user_num)),2) create_user_per_cost,                
               sum(reg_num) reg_num,
                round(if(sum(reg_num)=0,0,sum(cost)/sum(reg_num)),2) reg_per_cost,
               sum(cost) cost,
               sum(reg_amount)  reg_amount,
               sum(first_amount)  first_amount,
                sum(profit) profit,
                sum(new_follow_user) new_follow_user,
                sum(game_user_sum) game_user_sum,
                round(if(sum(reg_num)=0,0,sum(reg_num*third_stay_rate)/sum(reg_num)),2) third_stay_rate,
                round(if(sum(reg_num)=0,0,sum(reg_num*second_stay_rate)/sum(reg_num)),2) second_stay_rate,
                round(if(sum(game_user_sum)=0,0,sum(game_user_sum*today_active_user_rate)/sum(game_user_sum)),2) today_active_user_rate,
                round(sum(reg_amount)/sum(cost),4) roi,
                round(sum(first_amount)/sum(cost),4) first_roi,
                round(sum(cost)/sum(new_follow_user),2) new_follow_per_cost,
                sum(order_user) order_user,
                sum(order_count) order_count,
                round(sum(cost)/sum(order_user),2) order_user_per_cost,
                round(avg(require_roi),4) require_roi,
                round(avg(require_mult),2) require_mult,
                round(sum(expect_profit),2) expect_profit,
                round(sum(annual_expect_profit),2) annual_expect_profit,
                concat(sum(da1),',',sum(da1)/sum(cost),',', 0,',',1) d1,
                concat(sum(da2)-sum(if (da2,da1,0)),',',sum(da2)/sum(if (da2,cost,0)),',', (sum(da2)-sum(if (da2,da1,0)))/sum(if (da2,cost,0)),',',if(sum(if (da2,da1,0))=0,1,sum(da2)/sum(if (da2,da1,0))) ) d2,
                concat(sum(da3)-sum(if (da3,da2,0)),',',sum(da3)/sum(if (da3,cost,0)),',', (sum(da3)-sum(if (da3,da2,0)))/sum(if (da3,cost,0)),',',if(sum(if (da3,da1,0))=0,1,sum(da3)/sum(if (da3,da1,0))) ) d3,
                concat(sum(da4)-sum(if (da4,da3,0)),',',sum(da4)/sum(if (da4,cost,0)),',', (sum(da4)-sum(if (da4,da3,0)))/sum(if (da4,cost,0)),',',if(sum(if (da4,da1,0))=0,1,sum(da4)/sum(if (da4,da1,0))) ) d4,
                concat(sum(da5)-sum(if (da5,da4,0)),',',sum(da5)/sum(if (da5,cost,0)),',', (sum(da5)-sum(if (da5,da4,0)))/sum(if (da5,cost,0)),',',if(sum(if (da5,da1,0))=0,1,sum(da5)/sum(if (da5,da1,0))) ) d5,
                concat(sum(da6)-sum(if (da6,da5,0)),',',sum(da6)/sum(if (da6,cost,0)),',', (sum(da6)-sum(if (da6,da5,0)))/sum(if (da6,cost,0)),',',if(sum(if (da6,da1,0))=0,1,sum(da6)/sum(if (da6,da1,0))) ) d6,
                concat(sum(da7)-sum(if (da7,da6,0)),',',sum(da7)/sum(if (da7,cost,0)),',', (sum(da7)-sum(if (da7,da6,0)))/sum(if (da7,cost,0)),',',if(sum(if (da7,da1,0))=0,1,sum(da7)/sum(if (da7,da1,0))) ) d7,
                concat(sum(da8)-sum(if (da8,da7,0)),',',sum(da8)/sum(if (da8,cost,0)),',', (sum(da8)-sum(if (da8,da7,0)))/sum(if (da8,cost,0)),',',if(sum(if (da8,da1,0))=0,1,sum(da8)/sum(if (da8,da1,0))) ) d8,
                concat(sum(da9)-sum(if (da9,da8,0)),',',sum(da9)/sum(if (da9,cost,0)),',', (sum(da9)-sum(if (da9,da8,0)))/sum(if (da9,cost,0)),',',if(sum(if (da9,da1,0))=0,1,sum(da9)/sum(if (da9,da1,0))) ) d9,
                concat(sum(da10)-sum(if (da10,da9,0)),',',sum(da10)/sum(if (da10,cost,0)),',', (sum(da10)-sum(if (da10,da9,0)))/sum(if (da10,cost,0)),',',if(sum(if (da10,da1,0))=0,1,sum(da10)/sum(if (da10,da1,0))) ) d10,
                concat(sum(da11)-sum(if (da11,da10,0)),',',sum(da11)/sum(if (da11,cost,0)),',', (sum(da11)-sum(if (da11,da10,0)))/sum(if (da11,cost,0)),',',if(sum(if (da11,da1,0))=0,1,sum(da11)/sum(if (da11,da1,0))) ) d11,
                concat(sum(da12)-sum(if (da12,da11,0)),',',sum(da12)/sum(if (da12,cost,0)),',', (sum(da12)-sum(if (da12,da11,0)))/sum(if (da12,cost,0)),',',if(sum(if (da12,da1,0))=0,1,sum(da12)/sum(if (da12,da1,0))) ) d12,
                concat(sum(da13)-sum(if (da13,da12,0)),',',sum(da13)/sum(if (da13,cost,0)),',', (sum(da13)-sum(if (da13,da12,0)))/sum(if (da13,cost,0)),',',if(sum(if (da13,da1,0))=0,1,sum(da13)/sum(if (da13,da1,0))) ) d13,
                concat(sum(da14)-sum(if (da14,da13,0)),',',sum(da14)/sum(if (da14,cost,0)),',', (sum(da14)-sum(if (da14,da13,0)))/sum(if (da14,cost,0)),',',if(sum(if (da14,da1,0))=0,1,sum(da14)/sum(if (da14,da1,0))) ) d14,
                concat(sum(da15)-sum(if (da15,da14,0)),',',sum(da15)/sum(if (da15,cost,0)),',', (sum(da15)-sum(if (da15,da14,0)))/sum(if (da15,cost,0)),',',if(sum(if (da15,da1,0))=0,1,sum(da15)/sum(if (da15,da1,0))) ) d15,
                concat(sum(da16)-sum(if (da16,da15,0)),',',sum(da16)/sum(if (da16,cost,0)),',', (sum(da16)-sum(if (da16,da15,0)))/sum(if (da16,cost,0)),',',if(sum(if (da16,da1,0))=0,1,sum(da16)/sum(if (da16,da1,0))) ) d16,
                concat(sum(da17)-sum(if (da17,da16,0)),',',sum(da17)/sum(if (da17,cost,0)),',', (sum(da17)-sum(if (da17,da16,0)))/sum(if (da17,cost,0)),',',if(sum(if (da17,da1,0))=0,1,sum(da17)/sum(if (da17,da1,0))) ) d17,
                concat(sum(da18)-sum(if (da18,da17,0)),',',sum(da18)/sum(if (da18,cost,0)),',', (sum(da18)-sum(if (da18,da17,0)))/sum(if (da18,cost,0)),',',if(sum(if (da18,da1,0))=0,1,sum(da18)/sum(if (da18,da1,0))) ) d18,
                concat(sum(da19)-sum(if (da19,da18,0)),',',sum(da19)/sum(if (da19,cost,0)),',', (sum(da19)-sum(if (da19,da18,0)))/sum(if (da19,cost,0)),',',if(sum(if (da19,da1,0))=0,1,sum(da19)/sum(if (da19,da1,0))) ) d19,
                concat(sum(da20)-sum(if (da20,da19,0)),',',sum(da20)/sum(if (da20,cost,0)),',', (sum(da20)-sum(if (da20,da19,0)))/sum(if (da20,cost,0)),',',if(sum(if (da20,da1,0))=0,1,sum(da20)/sum(if (da20,da1,0))) ) d20,
                concat(sum(da21)-sum(if (da21,da20,0)),',',sum(da21)/sum(if (da21,cost,0)),',', (sum(da21)-sum(if (da21,da20,0)))/sum(if (da21,cost,0)),',',if(sum(if (da21,da1,0))=0,1,sum(da21)/sum(if (da21,da1,0))) ) d21,
                concat(sum(da22)-sum(if (da22,da21,0)),',',sum(da22)/sum(if (da22,cost,0)),',', (sum(da22)-sum(if (da22,da21,0)))/sum(if (da22,cost,0)),',',if(sum(if (da22,da1,0))=0,1,sum(da22)/sum(if (da22,da1,0))) ) d22,
                concat(sum(da23)-sum(if (da23,da22,0)),',',sum(da23)/sum(if (da23,cost,0)),',', (sum(da23)-sum(if (da23,da22,0)))/sum(if (da23,cost,0)),',',if(sum(if (da23,da1,0))=0,1,sum(da23)/sum(if (da23,da1,0))) ) d23,
                concat(sum(da24)-sum(if (da24,da23,0)),',',sum(da24)/sum(if (da24,cost,0)),',', (sum(da24)-sum(if (da24,da23,0)))/sum(if (da24,cost,0)),',',if(sum(if (da24,da1,0))=0,1,sum(da24)/sum(if (da24,da1,0))) ) d24,
                concat(sum(da25)-sum(if (da25,da24,0)),',',sum(da25)/sum(if (da25,cost,0)),',', (sum(da25)-sum(if (da25,da24,0)))/sum(if (da25,cost,0)),',',if(sum(if (da25,da1,0))=0,1,sum(da25)/sum(if (da25,da1,0))) ) d25,
                concat(sum(da26)-sum(if (da26,da25,0)),',',sum(da26)/sum(if (da26,cost,0)),',', (sum(da26)-sum(if (da26,da25,0)))/sum(if (da26,cost,0)),',',if(sum(if (da26,da1,0))=0,1,sum(da26)/sum(if (da26,da1,0))) ) d26,
                concat(sum(da27)-sum(if (da27,da26,0)),',',sum(da27)/sum(if (da27,cost,0)),',', (sum(da27)-sum(if (da27,da26,0)))/sum(if (da27,cost,0)),',',if(sum(if (da27,da1,0))=0,1,sum(da27)/sum(if (da27,da1,0))) ) d27,
                concat(sum(da28)-sum(if (da28,da27,0)),',',sum(da28)/sum(if (da28,cost,0)),',', (sum(da28)-sum(if (da28,da27,0)))/sum(if (da28,cost,0)),',',if(sum(if (da28,da1,0))=0,1,sum(da28)/sum(if (da28,da1,0))) ) d28,
                concat(sum(da29)-sum(if (da29,da28,0)),',',sum(da29)/sum(if (da29,cost,0)),',', (sum(da29)-sum(if (da29,da28,0)))/sum(if (da29,cost,0)),',',if(sum(if (da29,da1,0))=0,1,sum(da29)/sum(if (da29,da1,0))) ) d29,
                concat(sum(da30)-sum(if (da30,da29,0)),',',sum(da30)/sum(if (da30,cost,0)),',', (sum(da30)-sum(if (da30,da29,0)))/sum(if (da30,cost,0)),',',if(sum(if (da30,da1,0))=0,1,sum(da30)/sum(if (da30,da1,0))) ) d30,
                concat(sum(da31)-sum(if (da31,da30,0)),',',sum(da31)/sum(if (da31,cost,0)),',', (sum(da31)-sum(if (da31,da30,0)))/sum(if (da31,cost,0)),',',if(sum(if (da31,da1,0))=0,1,sum(da31)/sum(if (da31,da1,0))) ) d31,
                concat(sum(da32)-sum(if (da32,da31,0)),',',sum(da32)/sum(if (da32,cost,0)),',', (sum(da32)-sum(if (da32,da31,0)))/sum(if (da32,cost,0)),',',if(sum(if (da32,da1,0))=0,1,sum(da32)/sum(if (da32,da1,0))) ) d32,
                concat(sum(da33)-sum(if (da33,da32,0)),',',sum(da33)/sum(if (da33,cost,0)),',', (sum(da33)-sum(if (da33,da32,0)))/sum(if (da33,cost,0)),',',if(sum(if (da33,da1,0))=0,1,sum(da33)/sum(if (da33,da1,0))) ) d33,
                concat(sum(da34)-sum(if (da34,da33,0)),',',sum(da34)/sum(if (da34,cost,0)),',', (sum(da34)-sum(if (da34,da33,0)))/sum(if (da34,cost,0)),',',if(sum(if (da34,da1,0))=0,1,sum(da34)/sum(if (da34,da1,0))) ) d34,
                concat(sum(da35)-sum(if (da35,da34,0)),',',sum(da35)/sum(if (da35,cost,0)),',', (sum(da35)-sum(if (da35,da34,0)))/sum(if (da35,cost,0)),',',if(sum(if (da35,da1,0))=0,1,sum(da35)/sum(if (da35,da1,0))) ) d35,
                concat(sum(da36)-sum(if (da36,da35,0)),',',sum(da36)/sum(if (da36,cost,0)),',', (sum(da36)-sum(if (da36,da35,0)))/sum(if (da36,cost,0)),',',if(sum(if (da36,da1,0))=0,1,sum(da36)/sum(if (da36,da1,0))) ) d36,
                concat(sum(da37)-sum(if (da37,da36,0)),',',sum(da37)/sum(if (da37,cost,0)),',', (sum(da37)-sum(if (da37,da36,0)))/sum(if (da37,cost,0)),',',if(sum(if (da37,da1,0))=0,1,sum(da37)/sum(if (da37,da1,0))) ) d37,
                concat(sum(da38)-sum(if (da38,da37,0)),',',sum(da38)/sum(if (da38,cost,0)),',', (sum(da38)-sum(if (da38,da37,0)))/sum(if (da38,cost,0)),',',if(sum(if (da38,da1,0))=0,1,sum(da38)/sum(if (da38,da1,0))) ) d38,
                concat(sum(da39)-sum(if (da39,da38,0)),',',sum(da39)/sum(if (da39,cost,0)),',', (sum(da39)-sum(if (da39,da38,0)))/sum(if (da39,cost,0)),',',if(sum(if (da39,da1,0))=0,1,sum(da39)/sum(if (da39,da1,0))) ) d39,
                concat(sum(da40)-sum(if (da40,da39,0)),',',sum(da40)/sum(if (da40,cost,0)),',', (sum(da40)-sum(if (da40,da39,0)))/sum(if (da40,cost,0)),',',if(sum(if (da40,da1,0))=0,1,sum(da40)/sum(if (da40,da1,0))) ) d40,
                concat(sum(da41)-sum(if (da41,da40,0)),',',sum(da41)/sum(if (da41,cost,0)),',', (sum(da41)-sum(if (da41,da40,0)))/sum(if (da41,cost,0)),',',if(sum(if (da41,da1,0))=0,1,sum(da41)/sum(if (da41,da1,0))) ) d41,
                concat(sum(da42)-sum(if (da42,da41,0)),',',sum(da42)/sum(if (da42,cost,0)),',', (sum(da42)-sum(if (da42,da41,0)))/sum(if (da42,cost,0)),',',if(sum(if (da42,da1,0))=0,1,sum(da42)/sum(if (da42,da1,0))) ) d42,
                concat(sum(da43)-sum(if (da43,da42,0)),',',sum(da43)/sum(if (da43,cost,0)),',', (sum(da43)-sum(if (da43,da42,0)))/sum(if (da43,cost,0)),',',if(sum(if (da43,da1,0))=0,1,sum(da43)/sum(if (da43,da1,0))) ) d43,
                concat(sum(da44)-sum(if (da44,da43,0)),',',sum(da44)/sum(if (da44,cost,0)),',', (sum(da44)-sum(if (da44,da43,0)))/sum(if (da44,cost,0)),',',if(sum(if (da44,da1,0))=0,1,sum(da44)/sum(if (da44,da1,0))) ) d44,
                concat(sum(da45)-sum(if (da45,da44,0)),',',sum(da45)/sum(if (da45,cost,0)),',', (sum(da45)-sum(if (da45,da44,0)))/sum(if (da45,cost,0)),',',if(sum(if (da45,da1,0))=0,1,sum(da45)/sum(if (da45,da1,0))) ) d45,
                concat(sum(da46)-sum(if (da46,da45,0)),',',sum(da46)/sum(if (da46,cost,0)),',', (sum(da46)-sum(if (da46,da45,0)))/sum(if (da46,cost,0)),',',if(sum(if (da46,da1,0))=0,1,sum(da46)/sum(if (da46,da1,0))) ) d46,
                concat(sum(da47)-sum(if (da47,da46,0)),',',sum(da47)/sum(if (da47,cost,0)),',', (sum(da47)-sum(if (da47,da46,0)))/sum(if (da47,cost,0)),',',if(sum(if (da47,da1,0))=0,1,sum(da47)/sum(if (da47,da1,0))) ) d47,
                concat(sum(da48)-sum(if (da48,da47,0)),',',sum(da48)/sum(if (da48,cost,0)),',', (sum(da48)-sum(if (da48,da47,0)))/sum(if (da48,cost,0)),',',if(sum(if (da48,da1,0))=0,1,sum(da48)/sum(if (da48,da1,0))) ) d48,
                concat(sum(da49)-sum(if (da49,da48,0)),',',sum(da49)/sum(if (da49,cost,0)),',', (sum(da49)-sum(if (da49,da48,0)))/sum(if (da49,cost,0)),',',if(sum(if (da49,da1,0))=0,1,sum(da49)/sum(if (da49,da1,0))) ) d49,
                concat(sum(da50)-sum(if (da50,da49,0)),',',sum(da50)/sum(if (da50,cost,0)),',', (sum(da50)-sum(if (da50,da49,0)))/sum(if (da50,cost,0)),',',if(sum(if (da50,da1,0))=0,1,sum(da50)/sum(if (da50,da1,0))) ) d50,
                concat(sum(da51)-sum(if (da51,da50,0)),',',sum(da51)/sum(if (da51,cost,0)),',', (sum(da51)-sum(if (da51,da50,0)))/sum(if (da51,cost,0)),',',if(sum(if (da51,da1,0))=0,1,sum(da51)/sum(if (da51,da1,0))) ) d51,
                concat(sum(da52)-sum(if (da52,da51,0)),',',sum(da52)/sum(if (da52,cost,0)),',', (sum(da52)-sum(if (da52,da51,0)))/sum(if (da52,cost,0)),',',if(sum(if (da52,da1,0))=0,1,sum(da52)/sum(if (da52,da1,0))) ) d52,
                concat(sum(da53)-sum(if (da53,da52,0)),',',sum(da53)/sum(if (da53,cost,0)),',', (sum(da53)-sum(if (da53,da52,0)))/sum(if (da53,cost,0)),',',if(sum(if (da53,da1,0))=0,1,sum(da53)/sum(if (da53,da1,0))) ) d53,
                concat(sum(da54)-sum(if (da54,da53,0)),',',sum(da54)/sum(if (da54,cost,0)),',', (sum(da54)-sum(if (da54,da53,0)))/sum(if (da54,cost,0)),',',if(sum(if (da54,da1,0))=0,1,sum(da54)/sum(if (da54,da1,0))) ) d54,
                concat(sum(da55)-sum(if (da55,da54,0)),',',sum(da55)/sum(if (da55,cost,0)),',', (sum(da55)-sum(if (da55,da54,0)))/sum(if (da55,cost,0)),',',if(sum(if (da55,da1,0))=0,1,sum(da55)/sum(if (da55,da1,0))) ) d55,
                concat(sum(da56)-sum(if (da56,da55,0)),',',sum(da56)/sum(if (da56,cost,0)),',', (sum(da56)-sum(if (da56,da55,0)))/sum(if (da56,cost,0)),',',if(sum(if (da56,da1,0))=0,1,sum(da56)/sum(if (da56,da1,0))) ) d56,
                concat(sum(da57)-sum(if (da57,da56,0)),',',sum(da57)/sum(if (da57,cost,0)),',', (sum(da57)-sum(if (da57,da56,0)))/sum(if (da57,cost,0)),',',if(sum(if (da57,da1,0))=0,1,sum(da57)/sum(if (da57,da1,0))) ) d57,
                concat(sum(da58)-sum(if (da58,da57,0)),',',sum(da58)/sum(if (da58,cost,0)),',', (sum(da58)-sum(if (da58,da57,0)))/sum(if (da58,cost,0)),',',if(sum(if (da58,da1,0))=0,1,sum(da58)/sum(if (da58,da1,0))) ) d58,
                concat(sum(da59)-sum(if (da59,da58,0)),',',sum(da59)/sum(if (da59,cost,0)),',', (sum(da59)-sum(if (da59,da58,0)))/sum(if (da59,cost,0)),',',if(sum(if (da59,da1,0))=0,1,sum(da59)/sum(if (da59,da1,0))) ) d59,
                concat(sum(da60)-sum(if (da60,da59,0)),',',sum(da60)/sum(if (da60,cost,0)),',', (sum(da60)-sum(if (da60,da59,0)))/sum(if (da60,cost,0)),',',if(sum(if (da60,da1,0))=0,1,sum(da60)/sum(if (da60,da1,0))) ) d60,
                concat(sum(dm3)-sum(if (dm3,da60,0)) ,',' ,sum(dm3)/sum(if(dm3,cost,0)) ,',', (sum(dm3)-sum(if (dm3,da60,0)))/sum(if(dm3,cost,0)) ,',' ,if(sum(if(dm3,da1,0))=0,1,sum(dm3)/sum(if(dm3,da1,0))) ) m3,
                concat(sum(dm4)-sum(if (dm4,dm3,0)),',',sum(dm4)/sum(if (dm4,cost,0)),',', (sum(dm4)-sum(if (dm4,dm3,0)))/sum(if (dm4,cost,0)),',',if(sum(if (dm4,da1,0))=0,1,sum(dm4)/sum(if (dm4,da1,0))) ) m4,
                concat(sum(dm5)-sum(if (dm5,dm4,0)),',',sum(dm5)/sum(if (dm5,cost,0)),',', (sum(dm5)-sum(if (dm5,dm4,0)))/sum(if (dm5,cost,0)),',',if(sum(if (dm5,da1,0))=0,1,sum(dm5)/sum(if (dm5,da1,0))) ) m5

                from ({sql}) a
                """
    print(sumsql)
    data, total, total_data = getLimitSumData(db.dm, sql, sumsql, page, page_size)

    def parse(key_str):
        if type(key_str) is not str:
            key_str = key_str.decode('utf-8')
        li = key_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 x in total_data.keys():
        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']:
            total_data[x] = parse(total_data[x]) if total_data[x] is not None else {}

    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(user_id, channel, pitcher, page, page_size, order_by, order, state, location, start, end):
    db = MysqlUtils()

    if user_id in super_auth():  # 超级数据权限
        op = ''
    else:
        channel_li = UserAuthUtils.get_auth_channel(user_id)
        print(channel_li.__len__())
        if len(channel_li) == 0:
            return None, None, None
        elif len(channel_li) == 1:
            op = f" and a.channel = '{channel_li[0]}'"
        else:
            op = f" and a.channel in {tuple(channel_li)}"

    op1 = f" and a.channel='{channel}'" if channel else ''
    op2 = f" and pitcher='{pitcher}'" if pitcher else ''
    op5 = f" and state='{state}'" if state else ''
    op6 = f" and a.dt>='{start}'" if start else ''
    op7 = f" and a.dt<='{end}'" if end else ''

    sql = f"""SELECT channel,
                if(end>date_sub(now(),interval 10 day),'在投','停投') state,
                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,
                td_amount,yd_amount,byd_amount
            FROM
                (select
                    channel,
                    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,
                    sum(ba1) td_amount,sum(ba2) yd_amount,sum(ba3) byd_amount
            
               from  dw_channel  a 
                left join dw_channel_amount_daily_reverse b using (dt,channel)
                left join quchen_text.advertiser_vx c on a.channel = c.name 
                left join quchen_text.advertiser_vx d on b.channel = d.name 
                where 1=1 and a.dt>=c.start_date  and b.dt>=d.start_date 
                {op} {op1} {op2}  {op6} {op7} GROUP BY a.channel) x
                having 1=1   {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 ,
            sum(td_amount) td_amount,sum(yd_amount) yd_amount,sum(byd_amount) byd_amount
            from ({sql}) a
            
    """

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


def get_pitcher_channel_summary(user_id, channel, pitcher, page, page_size, order_by, order, state, location, start,
                                end):
    """投手投放号"""

    db = MysqlUtils()

    if user_id in super_auth():  # 超级数据权限
        op = ''
    else:
        user_name_li = UserAuthUtils.get_auth_user(user_id)
        if len(user_name_li) == 1:
            op = f" and pitcher='{user_name_li[0]}'"
        else:
            op = f" and pitcher in {tuple(user_name_li)}"

    op1 = f" and a.channel='{channel}'" if channel else ''
    op2 = f" and pitcher='{pitcher}'" if pitcher else ''
    op4 = f" and location='{location}' " if location else ''
    op5 = f" and state='{state}'" if state else ''
    op6 = f" and a.dt>='{start}'" if start else ''
    op7 = f" and a.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,td_amount,yd_amount,byd_amount
            FROM
                (select
                    channel,pitcher,stage,
                    type  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,
                    sum(ba1) td_amount,sum(ba2) yd_amount,sum(ba3) byd_amount

               from  dw_channel  a 
                left join dw_channel_amount_daily_reverse b using (dt,channel)
                where 1=1 {op} {op1} {op2}  {op6} {op7} GROUP BY a.channel,type,pitcher,stage) x
                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 ,
            sum(td_amount) td_amount,sum(yd_amount) yd_amount,sum(byd_amount) byd_amount
            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,
            reg_num,create_user_num,
            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,
            annual_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,
            d1 as da1,d2 as da2,d3 as da3,d4 as da4,d5 as da5,d6 as da6,d7 as da7,d8 as da8,d9 as da9,d10 as da10,
            d11 as da11,d12 as da12,d13 as da13,d14 as da14,d15 as da15,d16 as da16,d17 as da17,d18 as da18,d19 as da19,
            d20 as da20,d21 as da21,d22 as da22,d23 as da23,d24 as da24,d25 as da25,d26 as da26,d27 as da27,d28 as da28,
            d29 as da29,d30 as da30

            from dw_pitcher_trend  where 1=1 {op1} {op2} {op3} {op4}
            """

    sumSql = f"""select concat(date_format(min(dt),'%Y/%m/%d'),'~',date_format(max(dt),'%Y/%m/%d'))  dt,sum(cost) cost,
    sum(reg_num) reg_num,
    sum(create_user_num) create_user_num,
    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,
    sum(first_amount) first_amount,
    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,
    sum(annual_expect_profit) annual_expect_profit,
    concat(sum(da1),',',sum(da1)/sum(cost),',', 0,',',1) d1,
    concat(sum(da2)-sum(if (da2,da1,0)),',',sum(da2)/sum(if (da2,cost,0)),',', (sum(da2)-sum(if (da2,da1,0)))/sum(if (da2,cost,0)),',',if(sum(if (da2,da1,0))=0,1,sum(da2)/sum(if (da2,da1,0))) ) d2,
    concat(sum(da3)-sum(if (da3,da2,0)),',',sum(da3)/sum(if (da3,cost,0)),',', (sum(da3)-sum(if (da3,da2,0)))/sum(if (da3,cost,0)),',',if(sum(if (da3,da1,0))=0,1,sum(da3)/sum(if (da3,da1,0))) ) d3,
    concat(sum(da4)-sum(if (da4,da3,0)),',',sum(da4)/sum(if (da4,cost,0)),',', (sum(da4)-sum(if (da4,da3,0)))/sum(if (da4,cost,0)),',',if(sum(if (da4,da1,0))=0,1,sum(da4)/sum(if (da4,da1,0))) ) d4,
    concat(sum(da5)-sum(if (da5,da4,0)),',',sum(da5)/sum(if (da5,cost,0)),',', (sum(da5)-sum(if (da5,da4,0)))/sum(if (da5,cost,0)),',',if(sum(if (da5,da1,0))=0,1,sum(da5)/sum(if (da5,da1,0))) ) d5,
    concat(sum(da6)-sum(if (da6,da5,0)),',',sum(da6)/sum(if (da6,cost,0)),',', (sum(da6)-sum(if (da6,da5,0)))/sum(if (da6,cost,0)),',',if(sum(if (da6,da1,0))=0,1,sum(da6)/sum(if (da6,da1,0))) ) d6,
    concat(sum(da7)-sum(if (da7,da6,0)),',',sum(da7)/sum(if (da7,cost,0)),',', (sum(da7)-sum(if (da7,da6,0)))/sum(if (da7,cost,0)),',',if(sum(if (da7,da1,0))=0,1,sum(da7)/sum(if (da7,da1,0))) ) d7,
    concat(sum(da8)-sum(if (da8,da7,0)),',',sum(da8)/sum(if (da8,cost,0)),',', (sum(da8)-sum(if (da8,da7,0)))/sum(if (da8,cost,0)),',',if(sum(if (da8,da1,0))=0,1,sum(da8)/sum(if (da8,da1,0))) ) d8,
    concat(sum(da9)-sum(if (da9,da8,0)),',',sum(da9)/sum(if (da9,cost,0)),',', (sum(da9)-sum(if (da9,da8,0)))/sum(if (da9,cost,0)),',',if(sum(if (da9,da1,0))=0,1,sum(da9)/sum(if (da9,da1,0))) ) d9,
    concat(sum(da10)-sum(if (da10,da9,0)),',',sum(da10)/sum(if (da10,cost,0)),',', (sum(da10)-sum(if (da10,da9,0)))/sum(if (da10,cost,0)),',',if(sum(if (da10,da1,0))=0,1,sum(da10)/sum(if (da10,da1,0))) ) d10,
    concat(sum(da11)-sum(if (da11,da10,0)),',',sum(da11)/sum(if (da11,cost,0)),',', (sum(da11)-sum(if (da11,da10,0)))/sum(if (da11,cost,0)),',',if(sum(if (da11,da1,0))=0,1,sum(da11)/sum(if (da11,da1,0))) ) d11,
    concat(sum(da12)-sum(if (da12,da11,0)),',',sum(da12)/sum(if (da12,cost,0)),',', (sum(da12)-sum(if (da12,da11,0)))/sum(if (da12,cost,0)),',',if(sum(if (da12,da1,0))=0,1,sum(da12)/sum(if (da12,da1,0))) ) d12,
    concat(sum(da13)-sum(if (da13,da12,0)),',',sum(da13)/sum(if (da13,cost,0)),',', (sum(da13)-sum(if (da13,da12,0)))/sum(if (da13,cost,0)),',',if(sum(if (da13,da1,0))=0,1,sum(da13)/sum(if (da13,da1,0))) ) d13,
    concat(sum(da14)-sum(if (da14,da13,0)),',',sum(da14)/sum(if (da14,cost,0)),',', (sum(da14)-sum(if (da14,da13,0)))/sum(if (da14,cost,0)),',',if(sum(if (da14,da1,0))=0,1,sum(da14)/sum(if (da14,da1,0))) ) d14,
    concat(sum(da15)-sum(if (da15,da14,0)),',',sum(da15)/sum(if (da15,cost,0)),',', (sum(da15)-sum(if (da15,da14,0)))/sum(if (da15,cost,0)),',',if(sum(if (da15,da1,0))=0,1,sum(da15)/sum(if (da15,da1,0))) ) d15,
    concat(sum(da16)-sum(if (da16,da15,0)),',',sum(da16)/sum(if (da16,cost,0)),',', (sum(da16)-sum(if (da16,da15,0)))/sum(if (da16,cost,0)),',',if(sum(if (da16,da1,0))=0,1,sum(da16)/sum(if (da16,da1,0))) ) d16,
    concat(sum(da17)-sum(if (da17,da16,0)),',',sum(da17)/sum(if (da17,cost,0)),',', (sum(da17)-sum(if (da17,da16,0)))/sum(if (da17,cost,0)),',',if(sum(if (da17,da1,0))=0,1,sum(da17)/sum(if (da17,da1,0))) ) d17,
    concat(sum(da18)-sum(if (da18,da17,0)),',',sum(da18)/sum(if (da18,cost,0)),',', (sum(da18)-sum(if (da18,da17,0)))/sum(if (da18,cost,0)),',',if(sum(if (da18,da1,0))=0,1,sum(da18)/sum(if (da18,da1,0))) ) d18,
    concat(sum(da19)-sum(if (da19,da18,0)),',',sum(da19)/sum(if (da19,cost,0)),',', (sum(da19)-sum(if (da19,da18,0)))/sum(if (da19,cost,0)),',',if(sum(if (da19,da1,0))=0,1,sum(da19)/sum(if (da19,da1,0))) ) d19,
    concat(sum(da20)-sum(if (da20,da19,0)),',',sum(da20)/sum(if (da20,cost,0)),',', (sum(da20)-sum(if (da20,da19,0)))/sum(if (da20,cost,0)),',',if(sum(if (da20,da1,0))=0,1,sum(da20)/sum(if (da20,da1,0))) ) d20,
    concat(sum(da21)-sum(if (da21,da20,0)),',',sum(da21)/sum(if (da21,cost,0)),',', (sum(da21)-sum(if (da21,da20,0)))/sum(if (da21,cost,0)),',',if(sum(if (da21,da1,0))=0,1,sum(da21)/sum(if (da21,da1,0))) ) d21,
    concat(sum(da22)-sum(if (da22,da21,0)),',',sum(da22)/sum(if (da22,cost,0)),',', (sum(da22)-sum(if (da22,da21,0)))/sum(if (da22,cost,0)),',',if(sum(if (da22,da1,0))=0,1,sum(da22)/sum(if (da22,da1,0))) ) d22,
    concat(sum(da23)-sum(if (da23,da22,0)),',',sum(da23)/sum(if (da23,cost,0)),',', (sum(da23)-sum(if (da23,da22,0)))/sum(if (da23,cost,0)),',',if(sum(if (da23,da1,0))=0,1,sum(da23)/sum(if (da23,da1,0))) ) d23,
    concat(sum(da24)-sum(if (da24,da23,0)),',',sum(da24)/sum(if (da24,cost,0)),',', (sum(da24)-sum(if (da24,da23,0)))/sum(if (da24,cost,0)),',',if(sum(if (da24,da1,0))=0,1,sum(da24)/sum(if (da24,da1,0))) ) d24,
    concat(sum(da25)-sum(if (da25,da24,0)),',',sum(da25)/sum(if (da25,cost,0)),',', (sum(da25)-sum(if (da25,da24,0)))/sum(if (da25,cost,0)),',',if(sum(if (da25,da1,0))=0,1,sum(da25)/sum(if (da25,da1,0))) ) d25,
    concat(sum(da26)-sum(if (da26,da25,0)),',',sum(da26)/sum(if (da26,cost,0)),',', (sum(da26)-sum(if (da26,da25,0)))/sum(if (da26,cost,0)),',',if(sum(if (da26,da1,0))=0,1,sum(da26)/sum(if (da26,da1,0))) ) d26,
    concat(sum(da27)-sum(if (da27,da26,0)),',',sum(da27)/sum(if (da27,cost,0)),',', (sum(da27)-sum(if (da27,da26,0)))/sum(if (da27,cost,0)),',',if(sum(if (da27,da1,0))=0,1,sum(da27)/sum(if (da27,da1,0))) ) d27,
    concat(sum(da28)-sum(if (da28,da27,0)),',',sum(da28)/sum(if (da28,cost,0)),',', (sum(da28)-sum(if (da28,da27,0)))/sum(if (da28,cost,0)),',',if(sum(if (da28,da1,0))=0,1,sum(da28)/sum(if (da28,da1,0))) ) d28,
    concat(sum(da29)-sum(if (da29,da28,0)),',',sum(da29)/sum(if (da29,cost,0)),',', (sum(da29)-sum(if (da29,da28,0)))/sum(if (da29,cost,0)),',',if(sum(if (da29,da1,0))=0,1,sum(da29)/sum(if (da29,da1,0))) ) d29,
    concat(sum(da30)-sum(if (da30,da29,0)),',',sum(da30)/sum(if (da30,cost,0)),',', (sum(da30)-sum(if (da30,da29,0)))/sum(if (da30,cost,0)),',',if(sum(if (da30,da1,0))=0,1,sum(da30)/sum(if (da30,da1,0))) ) d30

    
    from ({sql}) a
    """

    data, total, total_data = getLimitSumData(db.dm, sql, sumSql, page, page_size)
    print(data)

    def parse(key_str, is_total=False):
        if type(key_str) is not str:
            key_str = key_str.decode('utf-8')
        li = key_str.split(',')
        li[0] = round(float(li[0]), 2)
        li[1] = round(float(li[1]), 4)
        li[2] = round(float(li[2]), 4)
        if is_total:
            return dict(zip(['amount', 'roi', 'add'], li))
        else:
            return dict(zip(['amount', 'add', 'roi'], li))

    for k, v in total_data.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']:
            total_data[k] = parse(v, is_total=True) if v else {}

    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 v else {}

    return data, total, total_data


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