QcWebServerGame/data_manage/analysis_select.py

from model.UserAuthUtils import super_auth,get_auth_channel
from model.CommonUtils import getLimitSumData,get_dict_list
from model.DataBaseUtils import CkUtils


def channel_analysis_select(arg):
    """公众号订单跟踪查询"""
    ck = CkUtils()
    start = arg.get("start")
    end = arg.get("end")
    st = arg.get("st")
    et = arg.get("et")
    channel = arg.get("channel")
    user_id = arg.get('user_id')
    page = arg.get("page")
    page_size = arg.get("page_size")
    order_by = arg.get("order_by", 'cost')
    order = arg.get("order", 'desc')

    if not (start and end and st and et):
        return {}
    if user_id in super_auth():
        op = ''
    else:
        if len(get_auth_channel(user_id)) == 1:
            op = f" and channel = '{get_auth_channel(user_id)[0]}'"
        else:
            op = f' and channel in {tuple(get_auth_channel(user_id))}'

    op1 = f" and channel in {tuple(channel.split(','))}" if channel else ''

    op2 = f" order by {order_by} {order}" if order_by and order else ''

    op3 = f" limit {(page - 1) * page_size},{page_size} " if (page and page_size) else ''



    sql = f"""
            select a.channel,cost,amount from
            (select channel,round(sum(cost),2) cost  from dw_daily_channel_cost
                where  dt>='{start}' and dt<='{end}' {op} {op1} group by channel) a
                left join
            (select channel,round(sum(amount),2) amount from `order` where  date>='{st}' and date<='{et}' and status=2  and
            formatDateTime(reg_time,'%Y-%m-%d')>='{start}' and formatDateTime(reg_time,'%Y-%m-%d')<='{end}' {op} {op1}
            group by channel) b
            using (channel) {op2} {op3}"""
    print(sql)
    data = get_dict_list(['channel','cost','amount'],ck.getDataList(sql))
    sum_sql = f"""select round(sum(cost),2) cost,round(sum(amount),2) amount from ({sql}) a"""
    total_data = get_dict_list(['cost','amount'],ck.getDataList(sum_sql))[0]
    total_sql = f"""select count(1) from ({sql}) a"""
    total = ck.getDataOne(total_sql)
    # print(total_data,total)
    return {"data":data,"total_data":total_data,"total":total}


def channel_analysis_select_daily(arg):
    ck = CkUtils()
    start = arg.get("start")
    end = arg.get("end")
    st = arg.get("st")
    et = arg.get("et")
    channel = arg.get("channel")
    if not (start and end and st and et and channel):
        return {}


    sql = f"""select formatDateTime(date,'%Y-%m-%d'),round(sum(amount),2) from `order` where formatDateTime(reg_time,'%Y-%m-%d')>='{start}' and formatDateTime(reg_time,'%Y-%m-%d')<='{end}'
            and date>='{st}' and date<='{et}' and channel = '{channel}' and status=2 
            group by date order by date desc limit 30"""

    data = get_dict_list(['dt','amount'],ck.getDataList(sql))
    # print(data)
    return {"data": data}

if __name__ == '__main__':
    arg={"channel":"白雁阅读",'start':'2021-03-01','end':'2021-03-10','st':'2021-04-01','et':'2021-04-10'}
    channel_analysis_select_daily(arg)