from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
du = DateUtils()
ck = CkUtils()
db = MysqlUtils()
log = logger()
def get_channel_belong_pitcher(channel):
"""根据公众号获取投手"""
sql = f"select pitcher,channel from dw_daily_channel_cost where dt='{du.get_n_days(-1)}' and pitcher!='' and channel!=''"
data=ck.execute(sql)
di={}
for i in data:
di[i[1]]=i[0]
return di.get(channel,'')
def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order):
sql=f"""select channel,stage,platform,book,
formatDateTime(dt,'%Y-%m-%d') as date,
cost,
first_order_amount,
toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi,
first_order_user,first_order_count,
toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost,
view_count,click_count,follow_user,
toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate,
toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
total_cost,
toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate
from dw_daily_channel where dt>='{start}' and dt<='{end}' """
if pitcher!='all':
sql += f" and pitcher='{pitcher}' "
if channel!='':
sql += f" and channel='{channel}' "
total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
sql += f" order by {order_by} {order} limit {page},{page_size} "
print(sql)
data=ck.execute(sql)
key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user',
'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost',
'total_cost','back_rate']
return get_dict_list(key,get_round(data)),total
def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order):
sql=f"""
select formatDateTime(dt,'%Y-%m-%d') date ,pitcher,
round(sum(cost),2) cost,
round(sum(first_order_amount),2) first_order_amount,
round(if(cost=0,0,first_order_amount/cost),4) first_roi ,
round(sum(order_amount),2) order_amount,
round(if(cost=0,0,sum(reg_order_amount)/cost),4) today_roi,
round(sum(total_cost),2) total_cost,
round(sum(total_amount),2) total_amount,
round(total_amount-total_cost,2) total_profit,
round(if(total_cost=0,0,total_amount/total_cost),4) total_roi
from dw_daily_channel where dt>='{start}' and dt<='{end}' """
if pitcher != 'all':
sql += f" and pitcher='{pitcher}' "
sql += f" group by date,pitcher order by {order_by} {order} limit {page},{page_size} "
print(sql)
data = ck.execute(sql)
total=ck.execute(f"select count(1) from ({sql}) a")[0][0]
key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_cost','total_amount','total_profit','total_roi']
return get_dict_list(key,data),total
def get_pitcher_panel_overview(pitcher):
sql=f"""select pitcher,
toDecimal32(cost,2) cost,
toDecimal32(amount,2) amount,
toDecimal32(roi,4) roi,
channel_count,on_channel_count,
off_channel_count,
toDecimal32(this_month_cost,2) this_month_cost,
toDecimal32(this_month_amount,2) this_month_amount,
toDecimal32(this_month_roi,4) this_month_roi,
toDecimal32(last_month_cost,2) last_month_cost,
toDecimal32(last_month_amount,2) last_month_amount,
toDecimal32(last_month_roi,4) last_month_roi,
toDecimal32(last_month_far_amount,2) last_month_far_amount,
follow_user,
toDecimal32(last_month_far_roi,2) last_month_far_roi
from dm_pitcher_daily_page_total where dt='{du.get_n_days(-1)}'"""
if pitcher != 'all':
sql += f" and pitcher='{pitcher}' "
print(sql)
data=ck.execute(sql)
print(data)
key=['pitcher','cost','amount','roi','channel_count','on_channel_count','off_channel_count','this_month_cost','this_month_amount','this_month_roi',
'last_month_cost','last_month_amount','last_month_roi','last_month_far_amount','follow_user','last_month_far_roi']
return get_dict_list(key,data)
def get_channel_overview(channel,pitcher,start,end,page,page_size,order_by,order):
sql="""select channel,toString(dt) date,
view_count,click_count,
round(if(view_count=0,0,click_count/view_count),4) click_rate,
follow_user,
round(if(click_count=0,0,follow_user/click_count),4) follow_rate,
round(if(follow_user=0,0,cost/follow_user),2) follow_per_cost,
round(if(click_count=0,0,first_order_count/click_count),4) order_rate,
round(if(first_order_user=0,0,cost/first_order_user),2) order_per_cost,
round(cost,2) cost,
first_order_count,first_order_user,
round(first_order_amount,2) first_order_amount,order_count,order_user,
round(order_amount,2) order_amount,
round(order_amount-first_order_amount,2) old_order_amount,
round(if(first_order_user=0,0,first_order_amount/first_order_user),2) first_amount_per_user,
round(if(follow_user=0,0,first_order_amount/follow_user),2) amount_per_follow,
round(if(first_order_user=0,0,cost/first_order_user),2) first_cost_per_user,
round(if(follow_user=0,0,first_order_user/follow_user),4) new_user_order_rate,
round(reg_order_amount,2) reg_user_amount,
round(total_cost,2) total_cost,
round(total_amount,2) total_amount,
round(if(cost=0,0,first_order_amount/cost),4) day_roi,
round(if(cost=0,0,reg_order_amount/cost),4) all_roi,
0 avg_new_order_rate,
0 old_user_once_order_rate from dw_daily_channel where 1=1 """
if channel!='':
sql+=f" and channel='{channel}'"
if start!='':
sql+=f" and start>='{start}'"
if end!='':
sql+=f" and end<='{end}'"
if pitcher!='':
sql+=f" and pitcher='{pitcher}'"
total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
sql += f" order by {order_by} {order} limit {page},{page_size} "
print(sql)
key=['channel','date','view_count','click_count','click_rate','follow_user','follow_rate','follow_per_cost','order_rate','order_per_cost','cost',
'first_order_count','first_order_user','first_order_amount','order_count','order_user','order_amount','old_order_amount','first_amount_per_user',
'new_user_order_rate','reg_user_amount','total_cost','total_amount','day_roi','all_roi','avg_new_order_rate','old_user_once_order_rate']
return get_dict_list(key,get_round(ck.execute(sql))),total
def get_channel_again_order_trend(channel,date,pitcher):
if get_channel_belong_pitcher(channel)!=pitcher:
return []
sql=f"""select toString(dt) date,
channel,book,
round(cost,2) cost,
round(reg_order_amount,2) reg_amount,
round(if(cost=0,0,reg_order_amount/cost),4) roi,
follow_user new_follow,
round(if(follow_user=0,0,cost/follow_user),2) new_follow_per_cost,
reg_order_count reg_count,reg_order_user reg_user,
round(if(reg_order_user=0,0,cost/reg_order_user),2) cost_per_user,
round(if(reg_order_user=0,0,reg_order_amount/reg_order_user),2) avg_order_amount,
round(if(reg_order_user=0,0,reg_order_user_again/reg_order_user),4) avg_again_order_rate,
order_count
from dw_daily_channel where channel='{channel}' and dt='{date}'
"""
print(sql)
data=ck.execute(sql)
print(data)
key1=['date','channel','book','cost','reg_amount','roi','new_follow','new_follow_per_cost',
'reg_count','reg_user','cost_per_user','avg_order_amount','avg_again_order_rate','order_count']
json1=get_dict_list(key1,data)[0]
# print(json1)
sql2="""select
date ,
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
from (
select count(1) count,date
from order where channel='玉龙书社' and date>='2020-09-19' and date<=addDays(toDate('2020-09-19'),6)
and formatDateTime(reg_time,'%Y-%m-%d')='2020-09-19' group by user_id,date
) a group by date"""
df=ck.getData_pd(sql2,[['date','c1','c2','c3','c4','c5']])
reg_user=json1["reg_user"]
li=[]
for i in range(1,5):
d1={}
d1["origin"]=int(df.iat[0,i])
d1["new"]=0
d1["move"]=int(df.iat[0,i+1])
d1["now"]=int(d1["origin"]+d1['new']-d1["move"])
d1["follow_order_rate"]=round(d1["now"]/reg_user,2) if reg_user!=0 else 0
d2={}
d2["origin"] = int(d1["now"])
d2["new"] = int(df.iat[1,i])
d2["move"] = int(df.iat[1, i+1])
d2["now"] = int(d2["origin"] +d2['new']- d2["move"])
d2["follow_order_rate"] = round(d2["now"] / reg_user, 2) if reg_user!=0 else 0
d3={}
d3["origin"] = int(d2["now"])
d3["new"] = int(df.iat[2, i])
d3["move"] = int(df.iat[2, i+1])
d3["now"] = int(d3["origin"] +d3['new']- d3["move"])
d3["follow_order_rate"] = round(d3["now"] / reg_user, 2) if reg_user!=0 else 0
d4={}
d4["origin"] = int(d3["now"])
d4["new"] = int(df.iat[3, i])
d4["move"] = int(df.iat[3, i+1])
d4["now"] = int(d4["origin"] +d4['new']- d4["move"])
d4["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
d5={}
d5["origin"] = int(d4["now"])
d5["new"] = int(df.iat[4, i])
d5["move"] = int(df.iat[4, i+1])
d5["now"] = int(d5["origin"] +d5['new']- d5["move"])
d5["follow_order_rate"] = round(d4["now"] / reg_user, 2) if reg_user!=0 else 0
d6 = {}
d6["origin"] = int(d5["now"])
d6["new"] = int(df.iat[5, i])
d6["move"] = int(df.iat[5, i+1])
d6["now"] = int(d6["origin"] +d6['new']- d6["move"])
d6["follow_order_rate"] = round(d6["now"] / reg_user, 2) if reg_user!=0 else 0
d7 = {}
d7["origin"] = int(d6["now"])
d7["new"] = int(df.iat[6, i])
d7["move"] = int(df.iat[6, i+1])
d7["now"] = int(d7["origin"] +d7['new']- d7["move"])
d7["follow_order_rate"] = round(d7["now"] / reg_user, 2) if reg_user!=0 else 0
d={}
d["user_order_count"]=i
d['d1']=d1
d['d2']=d2
d['d3']=d3
d['d4']=d4
d['d5']=d5
d['d6']=d6
d['d7']=d7
li.append(d)
json1['data']=li
print([json1])
return [json1]
def get_channel_active(channel,pitcher,start,end,page,page_size,order_by,order):
if get_channel_belong_pitcher(channel)!=pitcher:
return [],0
sql=f"""select formatDateTime(a.dt,'%Y-%m-%d') date, '{channel}' channel,book,cost,reg_amount,roi,new_follow_user,new_follow_per_cost,order_user,order_count,
order_user_per_cost,day7_avg_act_rate,day7_avg_act_per_cost,day30_avg_act_rate,ay30_avg_act_cost,
act_per_cost,
concat(toString(reg_order_user1),',',toString(if(reg_order_user1=0,0,cost/reg_order_user1)),',',toString(reg_order_user1/order_user)),
concat(toString(reg_order_user2),',',toString(if(reg_order_user2=0,0,cost/reg_order_user2)),',',toString(reg_order_user2/order_user)),
concat(toString(reg_order_user3),',',toString(if(reg_order_user3=0,0,cost/reg_order_user3)),',',toString(reg_order_user3/order_user)),
concat(toString(reg_order_user4),',',toString(if(reg_order_user4=0,0, cost/reg_order_user4)),',',toString(reg_order_user4/order_user)),
concat(toString(reg_order_user5),',',toString(if(reg_order_user5=0,0 ,cost/reg_order_user5)),',',toString(reg_order_user5/order_user)),
concat(toString(reg_order_user6),',',toString(if(reg_order_user6=0,0,cost/reg_order_user6)),',',toString(reg_order_user6/order_user)),
concat(toString(reg_order_user7),',',toString(if(reg_order_user7=0,0,cost/reg_order_user7)),',',toString(reg_order_user7/order_user)),
concat(toString(reg_order_user8),',',toString(if(reg_order_user8=0,0,cost/reg_order_user8)),',',toString(reg_order_user8/order_user)),
concat(toString(reg_order_user9),',',toString(if(reg_order_user9=0,0,cost/reg_order_user9)),',',toString(reg_order_user9/order_user)),
concat(toString(reg_order_user10),',',toString(if(reg_order_user10=0,0,cost/reg_order_user10)),',',toString(reg_order_user10/order_user)),
concat(toString(reg_order_user11),',',toString(if(reg_order_user11=0,0,cost/reg_order_user11)),',',toString(reg_order_user11/order_user)),
concat(toString(reg_order_user12),',',toString(if(reg_order_user12=0,0,cost/reg_order_user12)),',',toString(reg_order_user12/order_user)),
concat(toString(reg_order_user13),',',toString(if(reg_order_user13=0,0,cost/reg_order_user13)),',',toString(reg_order_user13/order_user)),
concat(toString(reg_order_user14),',',toString(if(reg_order_user14=0,0,cost/reg_order_user14)),',',toString(reg_order_user14/order_user)),
concat(toString(reg_order_user15),',',toString(if(reg_order_user15=0,0,cost/reg_order_user15)),',',toString(reg_order_user15/order_user)),
concat(toString(reg_order_user16),',',toString(if(reg_order_user16=0,0,cost/reg_order_user16)),',',toString(reg_order_user16/order_user)),
concat(toString(reg_order_user17),',',toString(if(reg_order_user17=0,0,cost/reg_order_user17)),',',toString(reg_order_user17/order_user)),
concat(toString(reg_order_user18),',',toString(if(reg_order_user18=0,0,cost/reg_order_user18)),',',toString(reg_order_user18/order_user)),
concat(toString(reg_order_user19),',',toString(if(reg_order_user19=0,0,cost/reg_order_user19)),',',toString(reg_order_user19/order_user)),
concat(toString(reg_order_user20),',',toString(if(reg_order_user20=0,0,cost/reg_order_user20)),',',toString(reg_order_user20/order_user)),
concat(toString(reg_order_user21),',',toString(if(reg_order_user21=0,0,cost/reg_order_user21)),',',toString(reg_order_user21/order_user)),
concat(toString(reg_order_user22),',',toString(if(reg_order_user22=0,0,cost/reg_order_user22)),',',toString(reg_order_user22/order_user)),
concat(toString(reg_order_user23),',',toString(if(reg_order_user23=0,0,cost/reg_order_user23)),',',toString(reg_order_user23/order_user)),
concat(toString(reg_order_user24),',',toString(if(reg_order_user24=0,0,cost/reg_order_user24)),',',toString(reg_order_user24/order_user)),
concat(toString(reg_order_user25),',',toString(if(reg_order_user25=0,0,cost/reg_order_user25)),',',toString(reg_order_user25/order_user)),
concat(toString(reg_order_user26),',',toString(if(reg_order_user26=0,0,cost/reg_order_user26)),',',toString(reg_order_user26/order_user)),
concat(toString(reg_order_user27),',',toString(if(reg_order_user27=0,0,cost/reg_order_user27)),',',toString(reg_order_user27/order_user)),
concat(toString(reg_order_user28),',',toString(if(reg_order_user28=0,0,cost/reg_order_user28)),',',toString(reg_order_user28/order_user)),
concat(toString(reg_order_user29),',',toString(if(reg_order_user29=0,0,cost/reg_order_user29)),',',toString(reg_order_user29/order_user)),
concat(toString(reg_order_user30),',',toString(if(reg_order_user30=0,0,cost/reg_order_user30)),',',toString(reg_order_user30/order_user))
from (
select dt,book,cost,
reg_order_amount reg_amount,
if(cost=0,0,reg_order_amount/cost) roi,
follow_user new_follow_user,
if(follow_user=0,0,cost/follow_user) new_follow_per_cost,
reg_order_user order_user,
reg_order_count order_count,
if(reg_order_user=0,0,cost/reg_order_user) order_user_per_cost,
if(follow_user=0,0,reg_order_user7/follow_user) day7_avg_act_rate ,
if(reg_order_user7=0,0,cost/reg_order_user7) day7_avg_act_per_cost,
if(follow_user=0,0,reg_order_user30/follow_user) day30_avg_act_rate,
if(reg_order_user30=0,0,cost/reg_order_user30) ay30_avg_act_cost,
if(reg_order_user=0,0,cost/reg_order_user) act_per_cost
from dw_daily_channel where channel='{channel}' and dt>='{start}' and dt<='{end}') a
left outer join (
select toDate(formatDateTime(reg_time,'%Y-%m-%d')) dt,
count(distinct if(subtractDays(date, 1)>reg_time,'',user_id))-1 reg_order_user1,
count(distinct if(subtractDays(date, 2)>reg_time,'',user_id))-1 reg_order_user2,
count(distinct if(subtractDays(date, 3)>reg_time,'',user_id))-1 reg_order_user3,
count(distinct if(subtractDays(date, 4)>reg_time,'',user_id))-1 reg_order_user4,
count(distinct if(subtractDays(date, 5)>reg_time,'',user_id))-1 reg_order_user5,
count(distinct if(subtractDays(date, 6)>reg_time,'',user_id))-1 reg_order_user6,
count(distinct if(subtractDays(date, 7)>reg_time,'',user_id))-1 reg_order_user7,
count(distinct if(subtractDays(date, 8)>reg_time,'',user_id))-1 reg_order_user8,
count(distinct if(subtractDays(date, 9)>reg_time,'',user_id))-1 reg_order_user9,
count(distinct if(subtractDays(date, 10)>reg_time,'',user_id))-1 reg_order_user10,
count(distinct if(subtractDays(date, 11)>reg_time,'',user_id))-1 reg_order_user11,
count(distinct if(subtractDays(date, 12)>reg_time,'',user_id))-1 reg_order_user12,
count(distinct if(subtractDays(date, 13)>reg_time,'',user_id))-1 reg_order_user13,
count(distinct if(subtractDays(date, 14)>reg_time,'',user_id))-1 reg_order_user14,
count(distinct if(subtractDays(date, 15)>reg_time,'',user_id))-1 reg_order_user15,
count(distinct if(subtractDays(date, 16)>reg_time,'',user_id))-1 reg_order_user16,
count(distinct if(subtractDays(date, 17)>reg_time,'',user_id))-1 reg_order_user17,
count(distinct if(subtractDays(date, 18)>reg_time,'',user_id))-1 reg_order_user18,
count(distinct if(subtractDays(date, 19)>reg_time,'',user_id))-1 reg_order_user19,
count(distinct if(subtractDays(date, 20)>reg_time,'',user_id))-1 reg_order_user20,
count(distinct if(subtractDays(date, 21)>reg_time,'',user_id))-1 reg_order_user21,
count(distinct if(subtractDays(date, 22)>reg_time,'',user_id))-1 reg_order_user22,
count(distinct if(subtractDays(date, 23)>reg_time,'',user_id))-1 reg_order_user23,
count(distinct if(subtractDays(date, 24)>reg_time,'',user_id))-1 reg_order_user24,
count(distinct if(subtractDays(date, 25)>reg_time,'',user_id))-1 reg_order_user25,
count(distinct if(subtractDays(date, 26)>reg_time,'',user_id))-1 reg_order_user26,
count(distinct if(subtractDays(date, 27)>reg_time,'',user_id))-1 reg_order_user27,
count(distinct if(subtractDays(date, 28)>reg_time,'',user_id))-1 reg_order_user28,
count(distinct if(subtractDays(date, 29)>reg_time,'',user_id))-1 reg_order_user29,
count(distinct if(subtractDays(date, 30)>reg_time,'',user_id))-1 reg_order_user30
from order where channel='{channel}' and dt>='{start}' group by formatDateTime(reg_time,'%Y-%m-%d')) b on a.dt=b.dt
"""
print(sql)
total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
sql+=f" order by {order_by} {order} limit {page},{page_size} "
data = ck.execute(sql)
key=['date','channel','book','cost','reg_amount','roi','new_follow_user','new_follow_per_cost','order_user','order_count','order_user_per_cost',
'day7_avg_act_rate','day7_avg_act_per_cost','day30_avg_act_rate','day30_avg_act_cost','act_per_cost','d1','d2','d3','d4','d5','d6','d7','d8','d9','d10',
'd11','d12','d13','d14','d15','d16','d17','d18','d19','d20','d21','d22','d23','d24','d25','d26','d27','d28','d29','d30']
x=get_dict_list(key,get_round(data))
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']:
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)
di[j]= dict(zip(['act_user','act_cost_per_cost','act_rate'],k))
else:
di[j]=i[j]
li.append(di)
return li,total
def get_channel_order_trend(channel,pitcher,start,end,page,page_size,order_by,order):
if get_channel_belong_pitcher(channel)!=pitcher:
return [],0
sql=""
total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
a=''
return a,total
def get_channel_summary(channel,pitcher,page,page_size,order_by,order):
sql = """select a.channel,state,'朋友圈' location,toString(start) start,toString(end) end,total_cost,total_amount,
profit,roi,follow_user,follow_per_cost,order_user,
if(follow_user=0,0,order_user/follow_user) order_tran_rate,if(order_user=0,0,total_cost/order_user) order_tran_cost
from
(
select channel,if(max(dt)>subtractDays(today(),10),'在投','停投') state,
min(dt) start,max(dt) end
from dw_daily_channel_cost where cost>0 {} group by channel) a
left outer join(
select channel,sum(follow_user) follow_user,
if(follow_user=0,0,sum(cost)/follow_user) follow_per_cost
from dw_daily_channel group by channel) b on a.channel=b.channel
left outer join(
select sum(reg_order_user) order_user,channel from dw_daily_channel where dt=subtractDays(today(),1) group by channel) c on a.channel=c.channel
left outer join (
select total_cost,
total_amount,
channel,
total_amount-total_cost profit,
if(total_cost=0,0,total_amount/total_cost) roi
from dw_channel_daily_total where dt=subtractDays(today(),1)) d on a.channel=d.channel
where 1=1 """
if pitcher!='':
sql=sql.format(f" and pitcher='{pitcher}'")
else:
sql=sql.format('')
if channel!='':
sql+=f" and channel='{channel}'"
total = ck.execute(f"select count(1) from ({sql}) a")[0][0]
sql+=f' order by {order_by} {order} limit {page},{page_size} '
print(sql)
key=['channel','state','location','start','end','total_cost','total_amount','profit','roi',
'follow_user','follow_per_cost','order_user','order_tran_rate','order_tran_cost']
return get_dict_list(key,get_round(ck.execute(sql))),total
if __name__ == '__main__':
# a=get_channel_overview('','','',1,10,'date','desc')
# a=get_channel_summary('','')
# a=get_channel_again_order_trend('玉龙书社','2020-09-19')
# a=get_channel_active('玉龙书社','2020-09-15','2020-09-20',1,10,'date','desc')
# print(a)
pass