from model.DateUtils import DateUtils from model.DataBaseUtils import * from model.log import logger from model.CommonUtils import * du = DateUtils() ck = CkUtils() db = MysqlUtils() log = logger() def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order): sql=f"""select channel,stage,platform,book, formatDateTime(dt,'%Y-%m-%d') as date, cost, first_order_amount, toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi, first_order_user,first_order_count, toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost, view_count,click_count,follow_user, toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate, toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost, total_cost, toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate from dw_daily_channel where dt>='{start}' and dt<='{end}' """ if pitcher!='all': sql += f" and pitcher='{pitcher}' " if channel!='': sql += f" and channel='{channel}' " sql += f" order by {order_by} {order} limit {page},{page_size} " print(sql) data=ck.execute(sql) key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user', 'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost', 'total_cost','back_rate'] return get_dict_list(key,data) def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order): sql=f""" select formatDateTime(dt,'%Y-%m-%d') date ,pitcher, 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) key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_cost','total_amount','total_profit','total_roi'] return get_dict_list(key,data) def get_pitcher_panel_overview(pitcher): sql=f"""select pitcher, toDecimal32(cost,2) cost, toDecimal32(amount,2) amount, toDecimal32(roi,4) roi, channel_count,on_channel_count, off_channel_count, toDecimal32(this_month_cost,2) this_month_cost, toDecimal32(this_month_amount,2) this_month_amount, toDecimal32(this_month_roi,4) this_month_roi, toDecimal32(last_month_cost,2) last_month_cost, toDecimal32(last_month_amount,2) last_month_amount, toDecimal32(last_month_roi,4) last_month_roi, toDecimal32(last_month_far_amount,2) last_month_far_amount, follow_user, 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}'" 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))) def get_channel_again_order_trend(channel,date): sql=f"""select toString(dt) date, channel,book, round(cost,2) cost, round(reg_order_amount,2) reg_amount, round(reg_order_amount/cost,4) roi, follow_user new_follow, round(cost/follow_user,2) new_follow_per_cost, reg_order_count reg_count,reg_order_user reg_user, round(cost/reg_order_user,2) cost_per_user, round(reg_order_amount/reg_order_user,2) avg_order_amount, round(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) 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) 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) 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) 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) 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) 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) 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,start,end,page,page_size,order_by,order): print(channel,) 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(cost/reg_order_user1),',',toString(reg_order_user1/order_user)), concat(toString(reg_order_user2),',',toString(cost/reg_order_user2),',',toString(reg_order_user2/order_user)), concat(toString(reg_order_user3),',',toString(cost/reg_order_user3),',',toString(reg_order_user3/order_user)), concat(toString(reg_order_user4),',',toString(cost/reg_order_user4),',',toString(reg_order_user4/order_user)), concat(toString(reg_order_user5),',',toString(cost/reg_order_user5),',',toString(reg_order_user5/order_user)), concat(toString(reg_order_user6),',',toString(cost/reg_order_user6),',',toString(reg_order_user6/order_user)), concat(toString(reg_order_user7),',',toString(cost/reg_order_user7),',',toString(reg_order_user7/order_user)), concat(toString(reg_order_user8),',',toString(cost/reg_order_user8),',',toString(reg_order_user8/order_user)), concat(toString(reg_order_user9),',',toString(cost/reg_order_user9),',',toString(reg_order_user9/order_user)), concat(toString(reg_order_user10),',',toString(cost/reg_order_user10),',',toString(reg_order_user10/order_user)), concat(toString(reg_order_user11),',',toString(cost/reg_order_user11),',',toString(reg_order_user11/order_user)), concat(toString(reg_order_user12),',',toString(cost/reg_order_user12),',',toString(reg_order_user12/order_user)), concat(toString(reg_order_user13),',',toString(cost/reg_order_user13),',',toString(reg_order_user13/order_user)), concat(toString(reg_order_user14),',',toString(cost/reg_order_user14),',',toString(reg_order_user14/order_user)), concat(toString(reg_order_user15),',',toString(cost/reg_order_user15),',',toString(reg_order_user15/order_user)), concat(toString(reg_order_user16),',',toString(cost/reg_order_user16),',',toString(reg_order_user16/order_user)), concat(toString(reg_order_user17),',',toString(cost/reg_order_user17),',',toString(reg_order_user17/order_user)), concat(toString(reg_order_user18),',',toString(cost/reg_order_user18),',',toString(reg_order_user18/order_user)), concat(toString(reg_order_user19),',',toString(cost/reg_order_user19),',',toString(reg_order_user19/order_user)), concat(toString(reg_order_user20),',',toString(cost/reg_order_user20),',',toString(reg_order_user20/order_user)), concat(toString(reg_order_user21),',',toString(cost/reg_order_user21),',',toString(reg_order_user21/order_user)), concat(toString(reg_order_user22),',',toString(cost/reg_order_user22),',',toString(reg_order_user22/order_user)), concat(toString(reg_order_user23),',',toString(cost/reg_order_user23),',',toString(reg_order_user23/order_user)), concat(toString(reg_order_user24),',',toString(cost/reg_order_user24),',',toString(reg_order_user24/order_user)), concat(toString(reg_order_user25),',',toString(cost/reg_order_user25),',',toString(reg_order_user25/order_user)), concat(toString(reg_order_user26),',',toString(cost/reg_order_user26),',',toString(reg_order_user26/order_user)), concat(toString(reg_order_user27),',',toString(cost/reg_order_user27),',',toString(reg_order_user27/order_user)), concat(toString(reg_order_user28),',',toString(cost/reg_order_user28),',',toString(reg_order_user28/order_user)), concat(toString(reg_order_user29),',',toString(cost/reg_order_user29),',',toString(reg_order_user29/order_user)), concat(toString(reg_order_user30),',',toString(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 order by {order_by} {order} limit {page},{page_size} """ print(sql) 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 def get_channel_order_trend(channel,start,end): a=[{ 'date': '2020-12-01', 'channel': '宝珠书屋', 'book': '魏汝稳', 'cost': 234.11, 'reg_amount': 123.11, 'roi': 0.45, 'new_follow_user': 11, 'new_follow_per_cost': 234.11, 'order_user': 222, 'order_count': 12312, 'order_user_per_cost': 123.11, 'd1':{ 'order':232.11, 'roi':0.2341, 'add':0.4511, 'mult':1.12}, 'd2': { 'order': 232.11, 'roi': 0.2341, 'add': 0.4511, 'mult': 1.12}, 'd3': { 'order': 232.11, 'roi': 0.2341, 'add': 0.4511, 'mult': 1.12}, }] return a def get_channel_summary(channel,pitcher,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}'" 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))) 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)