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, toDecimal32(sum(cost),2) cost, toDecimal32(sum(first_order_amount),2) first_order_amount, toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi , toDecimal32(sum(order_amount),2) order_amount, toDecimal32(if(cost=0,0,sum(reg_order_amount)/cost),4) today_roi, toDecimal32(sum(total_cost),2) total_cost, toDecimal32(sum(total_amount),2) total_amount, toDecimal32(total_amount-total_cost,2) total_profit, toDecimal32(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 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'] return get_dict_list(key,data) def get_channel_overview(channel,start,end): a =[{'date':'2020-12-30', 'channel':'宝珠书屋', 'view_count':2, 'click_count':2, 'click_rate':0.23, 'follow_user':2, 'follow_rate':0.23, 'follow_per_cost':2.12, 'order_rate':0.12, 'order_per_cost':3.21, 'cost':343.12, 'first_order_count':23, 'first_order_user':23, 'first_order_amount':122.23, 'order_count':21, 'order_user':12, 'order_amount':234.12, 'old_order_amount':234.12, 'first_amount_per_user':232, 'amount_per_follow':123.12, 'first_cost_per_user':234.12, 'new_user_order_rate':0.22, 'reg_user_amount':212.1, 'total_cost':2342.11, 'total_amount':123.11, 'day_roi':0.87, 'all_roi':23.12, 'avg_new_order_rate':0.23, 'old_user_once_order_rate':0.23 }] return a def get_channel_again_order_trend(channel,date): a=[{'date':'2020-12-01', 'channel':'宝珠书屋', 'book':'熊出没', 'cost':23.23, 'reg_amount':23421.11, 'roi':0.23, 'new_follow':23, 'new_follow_per_cost':234.11, 'reg_user':111, 'reg_count':123, 'cost_per_user':123, 'avg_again_order_rate':0.24, 'avg_order_amount':1231.11, 'order_count':11, 'data':[{'user_order_count' :1, 'd1':{'origin':12, 'new':1231, 'move':12, 'now':2134, 'follow_order_rate':0.12}, 'd2': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd3': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd4': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd5': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd6': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd7': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12} } ] }] return a def get_channel_active(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, 'day7_avg_act_rate':0.14, 'day7_avg_act_per_cost':5.23, 'day30_avg_act_rate':0.9334, 'day30_avg_act_rate':5.23, 'act_per_cost':43.11, 'd1':{ 'act_user':12, 'act_cost_per_cost':23.11, 'act_rate':0.4512}, 'd2': { 'act_user': 12, 'act_cost_per_cost': 23.11, 'act_rate': 0.4512}, 'd3': { 'act_user': 12, 'act_cost_per_cost': 23.11, 'act_rate': 0.4512} }] return a 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