QcWebServerGame/data_manage/pitcher_panel.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
du = DateUtils()
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,
           if(cost=0,0,round(first_order_amount/cost,4)) first_roi,
           first_order_user,first_order_count,
           if(first_order_user=0,0,round(cost/first_order_user,2)) first_per_cost,
           view_count,click_count,follow_user,
           if(click_count=0,0,round(follow_user/click_count,4)) follow_rate,
           if(follow_user=0,0,round(cost/follow_user,2)) follow_per_cost,
            total_cost,
           if(total_cost=0,0,round(total_amount/total_cost,4)) 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,
               if(cost=0,0,round(first_order_amount/cost,4)) first_roi ,
               round(sum(order_amount),2) order_amount,
               if(cost=0,0,round(sum(reg_order_amount)/cost,4))  today_roi,
               round(sum(total_cost),2) total_cost,
               round(sum(total_amount),2) total_amount,
               total_amount-total_cost total_profit,
               if(total_cost=0,0,round(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,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
       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)