QcWebServerGame/data_manage/public_analysis.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
from data_manage.operate import get_pitcher, get_user_name_by_id
from model.UserAuthUtils import super_auth
from model import UserAuthUtils

du = DateUtils()
log = logger()


def book_rank(start, end, book, stage, type, page, page_size, order_by, order):
    db = MysqlUtils()
    op1 = f" and book='{book}'" if book else ''
    op2 = f" and dt>='{start}' " if start else ''
    op3 = f" and dt<='{end}' " if end else ''
    op4 = f" order by {order_by} {order}" if order_by and order else ''
    op5 = f" and stage='{stage}'" if stage else ''
    op6 = f" and type='{type}'" if type else ''

    sql = f"""select 
concat(DATE_FORMAT(min(dt),'%Y/%m/%d') ,'~',DATE_FORMAT(max(dt),'%Y/%m/%d')) date,
pitcher,book,stage,type,channel,
sum(cost) cost,
sum(view_count) view_count,
sum(click_count) click_count,
round(sum(click_count)/sum(view_count),4) click_rate,
round(sum(cost)*1000/sum(view_count),2) thousand_view_cost,
sum(reg_order_user) reg_user,
sum(reg_order_count) reg_count,
sum(first_order_amount) first_amount,
sum(reg_order_amount) reg_amount,
round(sum(first_order_amount)/sum(cost),4) first_roi,
round(sum(reg_order_amount)/sum(cost),4) roi
from dw_channel where 1=1 {op1} {op2} {op3} {op5} {op6} GROUP BY channel,book,pitcher,stage,type
 {op4}"""
    print(sql)
    return getLimitData(db.dm, sql, page, page_size)


def book_trend(start, end, book, book_type, platform, page, page_size, order_by, order):
    op1 = f" and book='{book}'" if book else ''
    op2 = f" and type='{book_type}' " if book_type else ''
    # op3 = f" and platform='{platform}' " if platform else ''
    op4 = f" and dt>='{start}' " if start else ''
    op5 = f" and dt<='{end}' " if end else ''
    op6 = f" order by {order_by} {order}" if order_by and order else ''

    db = MysqlUtils()

    sql = f"""
        select dt,book,type,cost,first_order_amount,reg_order_amount,
            round(first_order_amount/cost,4) first_roi,
            a7,a30,
            round(a7/cost,4) roi7,
            round(a30/cost,4) roi30,
            round(reg_order_amount/cost,4) roi,
            order_amount,
reg_order_amount-cost profit,
round(click_count/view_count,4) click_rate,
round(follow_user/click_count,4) follow_rate,
round(first_order_user/click_count,4) first_order_rate,
view_count,
click_count,
follow_user,
reg_order_user,
first_order_user,
round(cost/follow_user,2) follow_cost,
round(cost/reg_order_user,2) order_cost,
round(cost/first_order_user,2) first_order_cost,
concat(a1,',',0,',',a1/cost,',',1) d1,
concat(a2-a1,',',(a2-a1)/cost,',',a2/cost,',',a2/a1) d2,
concat(a3-a2,',',(a3-a2)/cost,',',a3/cost,',',a3/a1) d3,
concat(a4-a3,',',(a4-a3)/cost,',',a4/cost,',',a4/a1) d4,
concat(a5-a4,',',(a5-a4)/cost,',',a5/cost,',',a5/a1) d5,
concat(a6-a5,',',(a6-a5)/cost,',',a6/cost,',',a6/a1) d6,
concat(a7-a6,',',(a7-a6)/cost,',',a7/cost,',',a7/a1) d7,
concat(a8-a7,',',(a8-a7)/cost,',',a8/cost,',',a8/a1) d8,
concat(a9-a8,',',(a9-a8)/cost,',',a9/cost,',',a9/a1) d9,
concat(a10-a9,',',(a10-a9)/cost,',',a10/cost,',',a10/a1) d10,
concat(a11-a10,',',(a11-a10)/cost,',',a11/cost,',',a11/a1) d11,
concat(a12-a11,',',(a12-a11)/cost,',',a12/cost,',',a12/a1) d12,
concat(a13-a12,',',(a13-a12)/cost,',',a13/cost,',',a13/a1) d13,
concat(a14-a13,',',(a14-a13)/cost,',',a14/cost,',',a14/a1) d14,
concat(a15-a14,',',(a15-a14)/cost,',',a15/cost,',',a15/a1) d15,
concat(a16-a15,',',(a16-a15)/cost,',',a16/cost,',',a16/a1) d16,
concat(a17-a16,',',(a17-a16)/cost,',',a17/cost,',',a17/a1) d17,
concat(a18-a17,',',(a18-a17)/cost,',',a18/cost,',',a18/a1) d18,
concat(a19-a18,',',(a19-a18)/cost,',',a19/cost,',',a19/a1) d19,
concat(a20-a19,',',(a20-a19)/cost,',',a20/cost,',',a20/a1) d20,
concat(a21-a20,',',(a21-a20)/cost,',',a21/cost,',',a21/a1) d21,
concat(a22-a21,',',(a22-a21)/cost,',',a22/cost,',',a22/a1) d22,
concat(a23-a22,',',(a23-a22)/cost,',',a23/cost,',',a23/a1) d23,
concat(a24-a23,',',(a24-a23)/cost,',',a24/cost,',',a24/a1) d24,
concat(a25-a24,',',(a25-a24)/cost,',',a25/cost,',',a25/a1) d25,
concat(a26-a25,',',(a26-a25)/cost,',',a26/cost,',',a26/a1) d26,
concat(a27-a26,',',(a27-a26)/cost,',',a27/cost,',',a27/a1) d27,
concat(a28-a27,',',(a28-a27)/cost,',',a28/cost,',',a28/a1) d28,
concat(a29-a28,',',(a29-a28)/cost,',',a29/cost,',',a29/a1) d29,
concat(a30-a29,',',(a30-a29)/cost,',',a30/cost,',',a30/a1) d30,
concat(a31-a30,',',(a31-a30)/cost,',',a31/cost,',',a31/a1) d31,
concat(a32-a31,',',(a32-a31)/cost,',',a32/cost,',',a32/a1) d32,
concat(a33-a32,',',(a33-a32)/cost,',',a33/cost,',',a33/a1) d33,
concat(a34-a33,',',(a34-a33)/cost,',',a34/cost,',',a34/a1) d34,
concat(a35-a34,',',(a35-a34)/cost,',',a35/cost,',',a35/a1) d35,
concat(a36-a35,',',(a36-a35)/cost,',',a36/cost,',',a36/a1) d36,
concat(a37-a36,',',(a37-a36)/cost,',',a37/cost,',',a37/a1) d37,
concat(a38-a37,',',(a38-a37)/cost,',',a38/cost,',',a38/a1) d38,
concat(a39-a38,',',(a39-a38)/cost,',',a39/cost,',',a39/a1) d39,
concat(a40-a39,',',(a40-a39)/cost,',',a40/cost,',',a40/a1) d40,
concat(a41-a40,',',(a41-a40)/cost,',',a41/cost,',',a41/a1) d41,
concat(a42-a41,',',(a42-a41)/cost,',',a42/cost,',',a42/a1) d42,
concat(a43-a42,',',(a43-a42)/cost,',',a43/cost,',',a43/a1) d43,
concat(a44-a43,',',(a44-a43)/cost,',',a44/cost,',',a44/a1) d44,
concat(a45-a44,',',(a45-a44)/cost,',',a45/cost,',',a45/a1) d45,
concat(a46-a45,',',(a46-a45)/cost,',',a46/cost,',',a46/a1) d46,
concat(a47-a46,',',(a47-a46)/cost,',',a47/cost,',',a47/a1) d47,
concat(a48-a47,',',(a48-a47)/cost,',',a48/cost,',',a48/a1) d48,
concat(a49-a48,',',(a49-a48)/cost,',',a49/cost,',',a49/a1) d49,
concat(a50-a49,',',(a50-a49)/cost,',',a50/cost,',',a50/a1) d50,
concat(a51-a50,',',(a51-a50)/cost,',',a51/cost,',',a51/a1) d51,
concat(a52-a51,',',(a52-a51)/cost,',',a52/cost,',',a52/a1) d52,
concat(a53-a52,',',(a53-a52)/cost,',',a53/cost,',',a53/a1) d53,
concat(a54-a53,',',(a54-a53)/cost,',',a54/cost,',',a54/a1) d54,
concat(a55-a54,',',(a55-a54)/cost,',',a55/cost,',',a55/a1) d55,
concat(a56-a55,',',(a56-a55)/cost,',',a56/cost,',',a56/a1) d56,
concat(a57-a56,',',(a57-a56)/cost,',',a57/cost,',',a57/a1) d57,
concat(a58-a57,',',(a58-a57)/cost,',',a58/cost,',',a58/a1) d58,
concat(a59-a58,',',(a59-a58)/cost,',',a59/cost,',',a59/a1) d59,
concat(a60-a59,',',(a60-a59)/cost,',',a60/cost,',',a60/a1) d60,
concat(m3-a60,',',(m3-a60)/cost,',',m3/cost,',',m3/a1) m3,
concat(m4-m3,',',(m4-m3)/cost,',',m4/cost,',',m4/a1) m4,
concat(m5-m4,',',(m5-m4)/cost,',',m5/cost,',',m5/a1) m5,
a1 as da1,a2 as da2,a3 as da3,a4 as da4,a5 as da5,a6 as da6,a7 as da7,
a8 as da8,a9 as da9,a10 as da10,a11 as da11,a12 as da12,a13 as da13,
a14 as da14,a15 as da15,a16 as da16,a17 as da17,a18 as da18,a19 as da19,
a20 as da20,a21 as da21,a22 as da22,a23 as da23,a24 as da24,a25 as da25,
a26 as da26,a27 as da27,a28 as da28,a29 as da29,a30 as da30,a31 as da31,
a32 as da32,a33 as da33,a34 as da34,a35 as da35,a36 as da36,a37 as da37,
a38 as da38,a39 as da39,a40 as da40,a41 as da41,a42 as da42,a43 as da43,
a44 as da44,a45 as da45,a46 as da46,a47 as da47,a48 as da48,a49 as da49,
a50 as da50,a51 as da51,a52 as da52,a53 as da53,a54 as da54,a55 as da55,
a56 as da56,a57 as da57,a58 as da58,a59 as da59,a60 as da60,
m3 as dm3,m4 as dm4,m5 as dm5

from book_trend where 1=1  {op1} {op2} {op4} {op5}  {op6} 
"""

    sum_sql = f"""select concat(date_format(min(dt),'%Y/%m/%d'),'~',date_format(max(dt),'%Y/%m/%d')) dt,
sum(cost) cost,
sum(first_order_amount) first_order_amount,
sum(reg_order_amount) reg_order_amount,
round(sum(first_order_amount)/sum(cost),4) first_roi,
round(sum(a7)/sum(cost),4) roi7,
round(sum(a30)/sum(cost),4) roi30,
round(sum(reg_order_amount)/sum(cost),4) roi,
sum(order_amount) order_amount,
sum(reg_order_amount-cost) profit,
round(sum(click_count)/sum(view_count),4) click_rate,
round(sum(follow_user)/sum(click_count),4) follow_rate,
round(sum(first_order_user)/sum(click_count),4) first_order_rate,
sum(view_count) view_count,
sum(click_count) click_count,
sum(follow_user) follow_user,
sum(reg_order_user) reg_order_user,
sum(first_order_user) first_order_user,
round(sum(cost)/sum(follow_user),2) follow_cost,
round(sum(cost)/sum(reg_order_user)) order_cost,
round(sum(cost)/sum(first_order_user)) first_order_cost,
 concat(sum(da1),',',sum(da1)/sum(cost),',', 0,',',1) d1,
                concat(sum(da2)-sum(if (da2,da1,0)),',',sum(da2)/sum(if (da2,cost,0)),',', (sum(da2)-sum(if (da2,da1,0)))/sum(if (da2,cost,0)),',',if(sum(if (da2,da1,0))=0,1,sum(da2)/sum(if (da2,da1,0))) ) d2,
                concat(sum(da3)-sum(if (da3,da2,0)),',',sum(da3)/sum(if (da3,cost,0)),',', (sum(da3)-sum(if (da3,da2,0)))/sum(if (da3,cost,0)),',',if(sum(if (da3,da1,0))=0,1,sum(da3)/sum(if (da3,da1,0))) ) d3,
                concat(sum(da4)-sum(if (da4,da3,0)),',',sum(da4)/sum(if (da4,cost,0)),',', (sum(da4)-sum(if (da4,da3,0)))/sum(if (da4,cost,0)),',',if(sum(if (da4,da1,0))=0,1,sum(da4)/sum(if (da4,da1,0))) ) d4,
                concat(sum(da5)-sum(if (da5,da4,0)),',',sum(da5)/sum(if (da5,cost,0)),',', (sum(da5)-sum(if (da5,da4,0)))/sum(if (da5,cost,0)),',',if(sum(if (da5,da1,0))=0,1,sum(da5)/sum(if (da5,da1,0))) ) d5,
                concat(sum(da6)-sum(if (da6,da5,0)),',',sum(da6)/sum(if (da6,cost,0)),',', (sum(da6)-sum(if (da6,da5,0)))/sum(if (da6,cost,0)),',',if(sum(if (da6,da1,0))=0,1,sum(da6)/sum(if (da6,da1,0))) ) d6,
                concat(sum(da7)-sum(if (da7,da6,0)),',',sum(da7)/sum(if (da7,cost,0)),',', (sum(da7)-sum(if (da7,da6,0)))/sum(if (da7,cost,0)),',',if(sum(if (da7,da1,0))=0,1,sum(da7)/sum(if (da7,da1,0))) ) d7,
                concat(sum(da8)-sum(if (da8,da7,0)),',',sum(da8)/sum(if (da8,cost,0)),',', (sum(da8)-sum(if (da8,da7,0)))/sum(if (da8,cost,0)),',',if(sum(if (da8,da1,0))=0,1,sum(da8)/sum(if (da8,da1,0))) ) d8,
                concat(sum(da9)-sum(if (da9,da8,0)),',',sum(da9)/sum(if (da9,cost,0)),',', (sum(da9)-sum(if (da9,da8,0)))/sum(if (da9,cost,0)),',',if(sum(if (da9,da1,0))=0,1,sum(da9)/sum(if (da9,da1,0))) ) d9,
                concat(sum(da10)-sum(if (da10,da9,0)),',',sum(da10)/sum(if (da10,cost,0)),',', (sum(da10)-sum(if (da10,da9,0)))/sum(if (da10,cost,0)),',',if(sum(if (da10,da1,0))=0,1,sum(da10)/sum(if (da10,da1,0))) ) d10,
                concat(sum(da11)-sum(if (da11,da10,0)),',',sum(da11)/sum(if (da11,cost,0)),',', (sum(da11)-sum(if (da11,da10,0)))/sum(if (da11,cost,0)),',',if(sum(if (da11,da1,0))=0,1,sum(da11)/sum(if (da11,da1,0))) ) d11,
                concat(sum(da12)-sum(if (da12,da11,0)),',',sum(da12)/sum(if (da12,cost,0)),',', (sum(da12)-sum(if (da12,da11,0)))/sum(if (da12,cost,0)),',',if(sum(if (da12,da1,0))=0,1,sum(da12)/sum(if (da12,da1,0))) ) d12,
                concat(sum(da13)-sum(if (da13,da12,0)),',',sum(da13)/sum(if (da13,cost,0)),',', (sum(da13)-sum(if (da13,da12,0)))/sum(if (da13,cost,0)),',',if(sum(if (da13,da1,0))=0,1,sum(da13)/sum(if (da13,da1,0))) ) d13,
                concat(sum(da14)-sum(if (da14,da13,0)),',',sum(da14)/sum(if (da14,cost,0)),',', (sum(da14)-sum(if (da14,da13,0)))/sum(if (da14,cost,0)),',',if(sum(if (da14,da1,0))=0,1,sum(da14)/sum(if (da14,da1,0))) ) d14,
                concat(sum(da15)-sum(if (da15,da14,0)),',',sum(da15)/sum(if (da15,cost,0)),',', (sum(da15)-sum(if (da15,da14,0)))/sum(if (da15,cost,0)),',',if(sum(if (da15,da1,0))=0,1,sum(da15)/sum(if (da15,da1,0))) ) d15,
                concat(sum(da16)-sum(if (da16,da15,0)),',',sum(da16)/sum(if (da16,cost,0)),',', (sum(da16)-sum(if (da16,da15,0)))/sum(if (da16,cost,0)),',',if(sum(if (da16,da1,0))=0,1,sum(da16)/sum(if (da16,da1,0))) ) d16,
                concat(sum(da17)-sum(if (da17,da16,0)),',',sum(da17)/sum(if (da17,cost,0)),',', (sum(da17)-sum(if (da17,da16,0)))/sum(if (da17,cost,0)),',',if(sum(if (da17,da1,0))=0,1,sum(da17)/sum(if (da17,da1,0))) ) d17,
                concat(sum(da18)-sum(if (da18,da17,0)),',',sum(da18)/sum(if (da18,cost,0)),',', (sum(da18)-sum(if (da18,da17,0)))/sum(if (da18,cost,0)),',',if(sum(if (da18,da1,0))=0,1,sum(da18)/sum(if (da18,da1,0))) ) d18,
                concat(sum(da19)-sum(if (da19,da18,0)),',',sum(da19)/sum(if (da19,cost,0)),',', (sum(da19)-sum(if (da19,da18,0)))/sum(if (da19,cost,0)),',',if(sum(if (da19,da1,0))=0,1,sum(da19)/sum(if (da19,da1,0))) ) d19,
                concat(sum(da20)-sum(if (da20,da19,0)),',',sum(da20)/sum(if (da20,cost,0)),',', (sum(da20)-sum(if (da20,da19,0)))/sum(if (da20,cost,0)),',',if(sum(if (da20,da1,0))=0,1,sum(da20)/sum(if (da20,da1,0))) ) d20,
                concat(sum(da21)-sum(if (da21,da20,0)),',',sum(da21)/sum(if (da21,cost,0)),',', (sum(da21)-sum(if (da21,da20,0)))/sum(if (da21,cost,0)),',',if(sum(if (da21,da1,0))=0,1,sum(da21)/sum(if (da21,da1,0))) ) d21,
                concat(sum(da22)-sum(if (da22,da21,0)),',',sum(da22)/sum(if (da22,cost,0)),',', (sum(da22)-sum(if (da22,da21,0)))/sum(if (da22,cost,0)),',',if(sum(if (da22,da1,0))=0,1,sum(da22)/sum(if (da22,da1,0))) ) d22,
                concat(sum(da23)-sum(if (da23,da22,0)),',',sum(da23)/sum(if (da23,cost,0)),',', (sum(da23)-sum(if (da23,da22,0)))/sum(if (da23,cost,0)),',',if(sum(if (da23,da1,0))=0,1,sum(da23)/sum(if (da23,da1,0))) ) d23,
                concat(sum(da24)-sum(if (da24,da23,0)),',',sum(da24)/sum(if (da24,cost,0)),',', (sum(da24)-sum(if (da24,da23,0)))/sum(if (da24,cost,0)),',',if(sum(if (da24,da1,0))=0,1,sum(da24)/sum(if (da24,da1,0))) ) d24,
                concat(sum(da25)-sum(if (da25,da24,0)),',',sum(da25)/sum(if (da25,cost,0)),',', (sum(da25)-sum(if (da25,da24,0)))/sum(if (da25,cost,0)),',',if(sum(if (da25,da1,0))=0,1,sum(da25)/sum(if (da25,da1,0))) ) d25,
                concat(sum(da26)-sum(if (da26,da25,0)),',',sum(da26)/sum(if (da26,cost,0)),',', (sum(da26)-sum(if (da26,da25,0)))/sum(if (da26,cost,0)),',',if(sum(if (da26,da1,0))=0,1,sum(da26)/sum(if (da26,da1,0))) ) d26,
                concat(sum(da27)-sum(if (da27,da26,0)),',',sum(da27)/sum(if (da27,cost,0)),',', (sum(da27)-sum(if (da27,da26,0)))/sum(if (da27,cost,0)),',',if(sum(if (da27,da1,0))=0,1,sum(da27)/sum(if (da27,da1,0))) ) d27,
                concat(sum(da28)-sum(if (da28,da27,0)),',',sum(da28)/sum(if (da28,cost,0)),',', (sum(da28)-sum(if (da28,da27,0)))/sum(if (da28,cost,0)),',',if(sum(if (da28,da1,0))=0,1,sum(da28)/sum(if (da28,da1,0))) ) d28,
                concat(sum(da29)-sum(if (da29,da28,0)),',',sum(da29)/sum(if (da29,cost,0)),',', (sum(da29)-sum(if (da29,da28,0)))/sum(if (da29,cost,0)),',',if(sum(if (da29,da1,0))=0,1,sum(da29)/sum(if (da29,da1,0))) ) d29,
                concat(sum(da30)-sum(if (da30,da29,0)),',',sum(da30)/sum(if (da30,cost,0)),',', (sum(da30)-sum(if (da30,da29,0)))/sum(if (da30,cost,0)),',',if(sum(if (da30,da1,0))=0,1,sum(da30)/sum(if (da30,da1,0))) ) d30,
                concat(sum(da31)-sum(if (da31,da30,0)),',',sum(da31)/sum(if (da31,cost,0)),',', (sum(da31)-sum(if (da31,da30,0)))/sum(if (da31,cost,0)),',',if(sum(if (da31,da1,0))=0,1,sum(da31)/sum(if (da31,da1,0))) ) d31,
                concat(sum(da32)-sum(if (da32,da31,0)),',',sum(da32)/sum(if (da32,cost,0)),',', (sum(da32)-sum(if (da32,da31,0)))/sum(if (da32,cost,0)),',',if(sum(if (da32,da1,0))=0,1,sum(da32)/sum(if (da32,da1,0))) ) d32,
                concat(sum(da33)-sum(if (da33,da32,0)),',',sum(da33)/sum(if (da33,cost,0)),',', (sum(da33)-sum(if (da33,da32,0)))/sum(if (da33,cost,0)),',',if(sum(if (da33,da1,0))=0,1,sum(da33)/sum(if (da33,da1,0))) ) d33,
                concat(sum(da34)-sum(if (da34,da33,0)),',',sum(da34)/sum(if (da34,cost,0)),',', (sum(da34)-sum(if (da34,da33,0)))/sum(if (da34,cost,0)),',',if(sum(if (da34,da1,0))=0,1,sum(da34)/sum(if (da34,da1,0))) ) d34,
                concat(sum(da35)-sum(if (da35,da34,0)),',',sum(da35)/sum(if (da35,cost,0)),',', (sum(da35)-sum(if (da35,da34,0)))/sum(if (da35,cost,0)),',',if(sum(if (da35,da1,0))=0,1,sum(da35)/sum(if (da35,da1,0))) ) d35,
                concat(sum(da36)-sum(if (da36,da35,0)),',',sum(da36)/sum(if (da36,cost,0)),',', (sum(da36)-sum(if (da36,da35,0)))/sum(if (da36,cost,0)),',',if(sum(if (da36,da1,0))=0,1,sum(da36)/sum(if (da36,da1,0))) ) d36,
                concat(sum(da37)-sum(if (da37,da36,0)),',',sum(da37)/sum(if (da37,cost,0)),',', (sum(da37)-sum(if (da37,da36,0)))/sum(if (da37,cost,0)),',',if(sum(if (da37,da1,0))=0,1,sum(da37)/sum(if (da37,da1,0))) ) d37,
                concat(sum(da38)-sum(if (da38,da37,0)),',',sum(da38)/sum(if (da38,cost,0)),',', (sum(da38)-sum(if (da38,da37,0)))/sum(if (da38,cost,0)),',',if(sum(if (da38,da1,0))=0,1,sum(da38)/sum(if (da38,da1,0))) ) d38,
                concat(sum(da39)-sum(if (da39,da38,0)),',',sum(da39)/sum(if (da39,cost,0)),',', (sum(da39)-sum(if (da39,da38,0)))/sum(if (da39,cost,0)),',',if(sum(if (da39,da1,0))=0,1,sum(da39)/sum(if (da39,da1,0))) ) d39,
                concat(sum(da40)-sum(if (da40,da39,0)),',',sum(da40)/sum(if (da40,cost,0)),',', (sum(da40)-sum(if (da40,da39,0)))/sum(if (da40,cost,0)),',',if(sum(if (da40,da1,0))=0,1,sum(da40)/sum(if (da40,da1,0))) ) d40,
                concat(sum(da41)-sum(if (da41,da40,0)),',',sum(da41)/sum(if (da41,cost,0)),',', (sum(da41)-sum(if (da41,da40,0)))/sum(if (da41,cost,0)),',',if(sum(if (da41,da1,0))=0,1,sum(da41)/sum(if (da41,da1,0))) ) d41,
                concat(sum(da42)-sum(if (da42,da41,0)),',',sum(da42)/sum(if (da42,cost,0)),',', (sum(da42)-sum(if (da42,da41,0)))/sum(if (da42,cost,0)),',',if(sum(if (da42,da1,0))=0,1,sum(da42)/sum(if (da42,da1,0))) ) d42,
                concat(sum(da43)-sum(if (da43,da42,0)),',',sum(da43)/sum(if (da43,cost,0)),',', (sum(da43)-sum(if (da43,da42,0)))/sum(if (da43,cost,0)),',',if(sum(if (da43,da1,0))=0,1,sum(da43)/sum(if (da43,da1,0))) ) d43,
                concat(sum(da44)-sum(if (da44,da43,0)),',',sum(da44)/sum(if (da44,cost,0)),',', (sum(da44)-sum(if (da44,da43,0)))/sum(if (da44,cost,0)),',',if(sum(if (da44,da1,0))=0,1,sum(da44)/sum(if (da44,da1,0))) ) d44,
                concat(sum(da45)-sum(if (da45,da44,0)),',',sum(da45)/sum(if (da45,cost,0)),',', (sum(da45)-sum(if (da45,da44,0)))/sum(if (da45,cost,0)),',',if(sum(if (da45,da1,0))=0,1,sum(da45)/sum(if (da45,da1,0))) ) d45,
                concat(sum(da46)-sum(if (da46,da45,0)),',',sum(da46)/sum(if (da46,cost,0)),',', (sum(da46)-sum(if (da46,da45,0)))/sum(if (da46,cost,0)),',',if(sum(if (da46,da1,0))=0,1,sum(da46)/sum(if (da46,da1,0))) ) d46,
                concat(sum(da47)-sum(if (da47,da46,0)),',',sum(da47)/sum(if (da47,cost,0)),',', (sum(da47)-sum(if (da47,da46,0)))/sum(if (da47,cost,0)),',',if(sum(if (da47,da1,0))=0,1,sum(da47)/sum(if (da47,da1,0))) ) d47,
                concat(sum(da48)-sum(if (da48,da47,0)),',',sum(da48)/sum(if (da48,cost,0)),',', (sum(da48)-sum(if (da48,da47,0)))/sum(if (da48,cost,0)),',',if(sum(if (da48,da1,0))=0,1,sum(da48)/sum(if (da48,da1,0))) ) d48,
                concat(sum(da49)-sum(if (da49,da48,0)),',',sum(da49)/sum(if (da49,cost,0)),',', (sum(da49)-sum(if (da49,da48,0)))/sum(if (da49,cost,0)),',',if(sum(if (da49,da1,0))=0,1,sum(da49)/sum(if (da49,da1,0))) ) d49,
                concat(sum(da50)-sum(if (da50,da49,0)),',',sum(da50)/sum(if (da50,cost,0)),',', (sum(da50)-sum(if (da50,da49,0)))/sum(if (da50,cost,0)),',',if(sum(if (da50,da1,0))=0,1,sum(da50)/sum(if (da50,da1,0))) ) d50,
                concat(sum(da51)-sum(if (da51,da50,0)),',',sum(da51)/sum(if (da51,cost,0)),',', (sum(da51)-sum(if (da51,da50,0)))/sum(if (da51,cost,0)),',',if(sum(if (da51,da1,0))=0,1,sum(da51)/sum(if (da51,da1,0))) ) d51,
                concat(sum(da52)-sum(if (da52,da51,0)),',',sum(da52)/sum(if (da52,cost,0)),',', (sum(da52)-sum(if (da52,da51,0)))/sum(if (da52,cost,0)),',',if(sum(if (da52,da1,0))=0,1,sum(da52)/sum(if (da52,da1,0))) ) d52,
                concat(sum(da53)-sum(if (da53,da52,0)),',',sum(da53)/sum(if (da53,cost,0)),',', (sum(da53)-sum(if (da53,da52,0)))/sum(if (da53,cost,0)),',',if(sum(if (da53,da1,0))=0,1,sum(da53)/sum(if (da53,da1,0))) ) d53,
                concat(sum(da54)-sum(if (da54,da53,0)),',',sum(da54)/sum(if (da54,cost,0)),',', (sum(da54)-sum(if (da54,da53,0)))/sum(if (da54,cost,0)),',',if(sum(if (da54,da1,0))=0,1,sum(da54)/sum(if (da54,da1,0))) ) d54,
                concat(sum(da55)-sum(if (da55,da54,0)),',',sum(da55)/sum(if (da55,cost,0)),',', (sum(da55)-sum(if (da55,da54,0)))/sum(if (da55,cost,0)),',',if(sum(if (da55,da1,0))=0,1,sum(da55)/sum(if (da55,da1,0))) ) d55,
                concat(sum(da56)-sum(if (da56,da55,0)),',',sum(da56)/sum(if (da56,cost,0)),',', (sum(da56)-sum(if (da56,da55,0)))/sum(if (da56,cost,0)),',',if(sum(if (da56,da1,0))=0,1,sum(da56)/sum(if (da56,da1,0))) ) d56,
                concat(sum(da57)-sum(if (da57,da56,0)),',',sum(da57)/sum(if (da57,cost,0)),',', (sum(da57)-sum(if (da57,da56,0)))/sum(if (da57,cost,0)),',',if(sum(if (da57,da1,0))=0,1,sum(da57)/sum(if (da57,da1,0))) ) d57,
                concat(sum(da58)-sum(if (da58,da57,0)),',',sum(da58)/sum(if (da58,cost,0)),',', (sum(da58)-sum(if (da58,da57,0)))/sum(if (da58,cost,0)),',',if(sum(if (da58,da1,0))=0,1,sum(da58)/sum(if (da58,da1,0))) ) d58,
                concat(sum(da59)-sum(if (da59,da58,0)),',',sum(da59)/sum(if (da59,cost,0)),',', (sum(da59)-sum(if (da59,da58,0)))/sum(if (da59,cost,0)),',',if(sum(if (da59,da1,0))=0,1,sum(da59)/sum(if (da59,da1,0))) ) d59,
                concat(sum(da60)-sum(if (da60,da59,0)),',',sum(da60)/sum(if (da60,cost,0)),',', (sum(da60)-sum(if (da60,da59,0)))/sum(if (da60,cost,0)),',',if(sum(if (da60,da1,0))=0,1,sum(da60)/sum(if (da60,da1,0))) ) d60,
                concat(sum(dm3)-sum(if (dm3,da60,0)) ,',' ,sum(dm3)/sum(if(dm3,cost,0)) ,',', (sum(dm3)-sum(if (dm3,da60,0)))/sum(if(dm3,cost,0)) ,',' ,if(sum(if(dm3,da1,0))=0,1,sum(dm3)/sum(if(dm3,da1,0))) ) m3,
                concat(sum(dm4)-sum(if (dm4,dm3,0)),',',sum(dm4)/sum(if (dm4,cost,0)),',', (sum(dm4)-sum(if (dm4,dm3,0)))/sum(if (dm4,cost,0)),',',if(sum(if (dm4,da1,0))=0,1,sum(dm4)/sum(if (dm4,da1,0))) ) m4,
                concat(sum(dm5)-sum(if (dm5,dm4,0)),',',sum(dm5)/sum(if (dm5,cost,0)),',', (sum(dm5)-sum(if (dm5,dm4,0)))/sum(if (dm5,cost,0)),',',if(sum(if (dm5,da1,0))=0,1,sum(dm5)/sum(if (dm5,da1,0))) ) m5

from ({sql}) a
"""

    data, total, total_data = getLimitSumData(db.dm, sql, sum_sql, page, page_size)

    def parse(key_str):
        if type(key_str) is not str:
            key_str = key_str.decode('utf-8')
        li = key_str.split(',')
        li[0] = round(float(li[0]), 2)
        li[1] = round(float(li[1]), 4)
        li[2] = round(float(li[2]), 4)
        li[3] = round(float(li[3]), 2)
        return dict(zip(['amount', 'add', 'roi', 'mult'], li))

    # total已经写错更改过于
    def parse_total(key_str):
        if type(key_str) is not str:
            key_str = key_str.decode('utf-8')
        li = key_str.split(',')
        li[0] = round(float(li[0]), 2)
        li[1] = round(float(li[1]), 4)
        li[2] = round(float(li[2]), 4)
        li[3] = round(float(li[3]), 2)
        return dict(zip(['amount', 'roi', 'add', 'mult'], li))

    for k, v in total_data.items():
        if k 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', 'd31', 'd32',
                 'd33', 'd34', 'd35', 'd36', 'd37', 'd38',
                 'd39', 'd40', 'd41', 'd42', 'd43', 'd44', 'd45', 'd46', 'd47', 'd48', 'd49', 'd50', 'd51', 'd52',
                 'd53', 'd54', 'd55', 'd56', 'd57', 'd58', 'd59',
                 'd60', 'm3', 'm4', 'm5']:
            total_data[k] = parse_total(v) if v else {}

    # print(data)
    for i in data:
        for k, v in i.items():
            if k 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', 'd31', 'd32',
                     'd33', 'd34', 'd35', 'd36', 'd37', 'd38',
                     'd39', 'd40', 'd41', 'd42', 'd43', 'd44', 'd45', 'd46', 'd47', 'd48', 'd49', 'd50', 'd51', 'd52',
                     'd53', 'd54', 'd55', 'd56', 'd57', 'd58', 'd59',
                     'd60', 'm3', 'm4', 'm5']:
                i[k] = parse(v) if v else {}

    return data, total, total_data


def book_overview(start, end, book, type, page, page_size, order_by, order):
    op1 = f" and book='{book}'" if book else ''
    op2 = f" and type='{type}' " if type else ''
    op3 = f" and dt>='{start}' " if start else ''
    op4 = f" and dt<='{end}' " if end else ''
    op5 = f" order by {order_by} {order}" if order_by and order else ''

    db = MysqlUtils()

    sql = f"""
            select book,type,
                DATE_FORMAT(min(dt),'%Y%m%d') start_date,
                DATE_FORMAT(max(dt),'%Y%m%d') end_date,
                sum(cost) cost,
                sum(reg_order_amount) amount,
                sum(first_order_amount) first_amount,
                round(sum(reg_order_amount)/sum(cost),4) roi,
                round(sum(first_order_amount)/sum(cost),4) first_roi,
                round(sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi7,
                round(sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),4) roi30,
                sum(reg_order_amount-cost) profit,
    round(sum(click_count)/sum(view_count),4) click_rate,
    round(sum(follow_user)/sum(click_count),4) follow_rate,
    round(sum(reg_order_user)/sum(click_count),4) order_rate,
    round(sum(first_order_user)/sum(click_count),4) first_order_rate,
    sum(view_count) view_count,
    sum(click_count) click_count,
    sum(follow_user) follow_user,
    sum(reg_order_user) order_user,
    sum(first_order_user) first_order_user,
    round(sum(cost)/sum(follow_user),2) follow_cost,
    round(sum(cost)/sum(reg_order_user),2) order_cost,
    round(sum(cost)/sum(first_order_user),2) first_order_cost,
    round(sum(reg_order_amount)/sum(reg_order_user),2) avg_amount,
    sum(ba1) td_amount,
    sum(ba2) yd_amount,
    sum(ba3) byd_amount,
    concat(sum(a1),',',0,',',sum(a1)/sum(cost),',',1) d1,
   concat(sum(a2-a1),',',sum(a2-a1)/sum(if(dt=CURRENT_DATE,0,cost)),',',sum(a2)/sum(if(dt=CURRENT_DATE,0,cost)),',',sum(a2)/sum(if(dt=CURRENT_DATE,0,a1))) d2,
concat(sum(a3-a2),',',sum(a3-a2)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,cost)),',',sum(a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,cost)),',',sum(a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 1 day),0,a1))) d3,
concat(sum(a4-a3),',',sum(a4-a3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,cost)),',',sum(a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,cost)),',',sum(a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 2 day),0,a1))) d4,
concat(sum(a5-a4),',',sum(a5-a4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,cost)),',',sum(a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,cost)),',',sum(a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 3 day),0,a1))) d5,
concat(sum(a6-a5),',',sum(a6-a5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,cost)),',',sum(a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,cost)),',',sum(a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 4 day),0,a1))) d6,
concat(sum(a7-a6),',',sum(a7-a6)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),',',sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),',',sum(a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,a1))) d7,
concat(sum(a8-a7),',',sum(a8-a7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,cost)),',',sum(a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,cost)),',',sum(a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 6 day),0,a1))) d8,
concat(sum(a9-a8),',',sum(a9-a8)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,cost)),',',sum(a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,cost)),',',sum(a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 7 day),0,a1))) d9,
concat(sum(a10-a9),',',sum(a10-a9)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,cost)),',',sum(a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,cost)),',',sum(a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 8 day),0,a1))) d10,
concat(sum(a11-a10),',',sum(a11-a10)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,cost)),',',sum(a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,cost)),',',sum(a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 9 day),0,a1))) d11,
concat(sum(a12-a11),',',sum(a12-a11)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,cost)),',',sum(a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,cost)),',',sum(a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 10 day),0,a1))) d12,
concat(sum(a13-a12),',',sum(a13-a12)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,cost)),',',sum(a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,cost)),',',sum(a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 11 day),0,a1))) d13,
concat(sum(a14-a13),',',sum(a14-a13)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,cost)),',',sum(a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,cost)),',',sum(a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 12 day),0,a1))) d14,
concat(sum(a15-a14),',',sum(a15-a14)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,cost)),',',sum(a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,cost)),',',sum(a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 13 day),0,a1))) d15,
concat(sum(a16-a15),',',sum(a16-a15)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,cost)),',',sum(a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,cost)),',',sum(a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 14 day),0,a1))) d16,
concat(sum(a17-a16),',',sum(a17-a16)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,cost)),',',sum(a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,cost)),',',sum(a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 15 day),0,a1))) d17,
concat(sum(a18-a17),',',sum(a18-a17)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,cost)),',',sum(a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,cost)),',',sum(a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 16 day),0,a1))) d18,
concat(sum(a19-a18),',',sum(a19-a18)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,cost)),',',sum(a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,cost)),',',sum(a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 17 day),0,a1))) d19,
concat(sum(a20-a19),',',sum(a20-a19)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,cost)),',',sum(a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,cost)),',',sum(a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 18 day),0,a1))) d20,
concat(sum(a21-a20),',',sum(a21-a20)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,cost)),',',sum(a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,cost)),',',sum(a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 19 day),0,a1))) d21,
concat(sum(a22-a21),',',sum(a22-a21)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,cost)),',',sum(a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,cost)),',',sum(a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 20 day),0,a1))) d22,
concat(sum(a23-a22),',',sum(a23-a22)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,cost)),',',sum(a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,cost)),',',sum(a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 21 day),0,a1))) d23,
concat(sum(a24-a23),',',sum(a24-a23)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,cost)),',',sum(a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,cost)),',',sum(a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 22 day),0,a1))) d24,
concat(sum(a25-a24),',',sum(a25-a24)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,cost)),',',sum(a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,cost)),',',sum(a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 23 day),0,a1))) d25,
concat(sum(a26-a25),',',sum(a26-a25)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,cost)),',',sum(a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,cost)),',',sum(a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 24 day),0,a1))) d26,
concat(sum(a27-a26),',',sum(a27-a26)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,cost)),',',sum(a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,cost)),',',sum(a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 25 day),0,a1))) d27,
concat(sum(a28-a27),',',sum(a28-a27)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,cost)),',',sum(a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,cost)),',',sum(a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 26 day),0,a1))) d28,
concat(sum(a29-a28),',',sum(a29-a28)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,cost)),',',sum(a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,cost)),',',sum(a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 27 day),0,a1))) d29,
concat(sum(a30-a29),',',sum(a30-a29)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),',',sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,cost)),',',sum(a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 28 day),0,a1))) d30,
concat(sum(a31-a30),',',sum(a31-a30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,cost)),',',sum(a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,cost)),',',sum(a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 29 day),0,a1))) d31,
concat(sum(a32-a31),',',sum(a32-a31)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,cost)),',',sum(a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,cost)),',',sum(a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 30 day),0,a1))) d32,
concat(sum(a33-a32),',',sum(a33-a32)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,cost)),',',sum(a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,cost)),',',sum(a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 31 day),0,a1))) d33,
concat(sum(a34-a33),',',sum(a34-a33)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,cost)),',',sum(a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,cost)),',',sum(a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 32 day),0,a1))) d34,
concat(sum(a35-a34),',',sum(a35-a34)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,cost)),',',sum(a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,cost)),',',sum(a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 33 day),0,a1))) d35,
concat(sum(a36-a35),',',sum(a36-a35)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,cost)),',',sum(a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,cost)),',',sum(a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 34 day),0,a1))) d36,
concat(sum(a37-a36),',',sum(a37-a36)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,cost)),',',sum(a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,cost)),',',sum(a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 35 day),0,a1))) d37,
concat(sum(a38-a37),',',sum(a38-a37)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,cost)),',',sum(a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,cost)),',',sum(a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 36 day),0,a1))) d38,
concat(sum(a39-a38),',',sum(a39-a38)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,cost)),',',sum(a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,cost)),',',sum(a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 37 day),0,a1))) d39,
concat(sum(a40-a39),',',sum(a40-a39)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,cost)),',',sum(a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,cost)),',',sum(a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 38 day),0,a1))) d40,
concat(sum(a41-a40),',',sum(a41-a40)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,cost)),',',sum(a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,cost)),',',sum(a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 39 day),0,a1))) d41,
concat(sum(a42-a41),',',sum(a42-a41)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,cost)),',',sum(a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,cost)),',',sum(a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 40 day),0,a1))) d42,
concat(sum(a43-a42),',',sum(a43-a42)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,cost)),',',sum(a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,cost)),',',sum(a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 41 day),0,a1))) d43,
concat(sum(a44-a43),',',sum(a44-a43)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,cost)),',',sum(a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,cost)),',',sum(a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 42 day),0,a1))) d44,
concat(sum(a45-a44),',',sum(a45-a44)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,cost)),',',sum(a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,cost)),',',sum(a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 43 day),0,a1))) d45,
concat(sum(a46-a45),',',sum(a46-a45)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,cost)),',',sum(a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,cost)),',',sum(a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 44 day),0,a1))) d46,
concat(sum(a47-a46),',',sum(a47-a46)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,cost)),',',sum(a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,cost)),',',sum(a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 45 day),0,a1))) d47,
concat(sum(a48-a47),',',sum(a48-a47)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,cost)),',',sum(a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,cost)),',',sum(a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 46 day),0,a1))) d48,
concat(sum(a49-a48),',',sum(a49-a48)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,cost)),',',sum(a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,cost)),',',sum(a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 47 day),0,a1))) d49,
concat(sum(a50-a49),',',sum(a50-a49)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,cost)),',',sum(a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,cost)),',',sum(a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 48 day),0,a1))) d50,
concat(sum(a51-a50),',',sum(a51-a50)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,cost)),',',sum(a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,cost)),',',sum(a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 49 day),0,a1))) d51,
concat(sum(a52-a51),',',sum(a52-a51)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,cost)),',',sum(a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,cost)),',',sum(a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 50 day),0,a1))) d52,
concat(sum(a53-a52),',',sum(a53-a52)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,cost)),',',sum(a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,cost)),',',sum(a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 51 day),0,a1))) d53,
concat(sum(a54-a53),',',sum(a54-a53)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,cost)),',',sum(a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,cost)),',',sum(a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 52 day),0,a1))) d54,
concat(sum(a55-a54),',',sum(a55-a54)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,cost)),',',sum(a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,cost)),',',sum(a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 53 day),0,a1))) d55,
concat(sum(a56-a55),',',sum(a56-a55)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,cost)),',',sum(a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,cost)),',',sum(a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 54 day),0,a1))) d56,
concat(sum(a57-a56),',',sum(a57-a56)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,cost)),',',sum(a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,cost)),',',sum(a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 55 day),0,a1))) d57,
concat(sum(a58-a57),',',sum(a58-a57)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,cost)),',',sum(a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,cost)),',',sum(a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 56 day),0,a1))) d58,
concat(sum(a59-a58),',',sum(a59-a58)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,cost)),',',sum(a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,cost)),',',sum(a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 57 day),0,a1))) d59,
concat(sum(a60-a59),',',sum(a60-a59)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,cost)),',',sum(a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,cost)),',',sum(a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 58 day),0,a1))) d60,
concat(sum(m3-a60),',',sum(m3-a60)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,cost)),',',sum(m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,cost)),',',sum(m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 88 day),0,a1))) m3,
concat(sum(m4-m3),',',sum(m4-m3)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,cost)),',',sum(m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,cost)),',',sum(m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 118 day),0,a1))) m4,
concat(sum(m5-m4),',',sum(m5-m4)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,cost)),',',sum(m5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,cost)),',',sum(m5)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 148 day),0,a1))) m5
    from book_trend where cost>0 {op1}  {op2} {op3} {op4}  group by book,type  {op5} 
    """
    data, total = getLimitData(db.dm, sql, page, page_size)

    def parse(str):
        li = str.decode('utf-8').split(',')
        li[0] = round(float(li[0]), 2)
        li[1] = round(float(li[1]), 4)
        li[2] = round(float(li[2]), 4)
        li[3] = round(float(li[3]), 2)
        return dict(zip(['amount', 'add', 'roi', 'mult'], li))

    # print(data)
    for i in data:
        for k, v in i.items():
            if k 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', 'd31', 'd32',
                     'd33', 'd34', 'd35', 'd36', 'd37', 'd38',
                     'd39', 'd40', 'd41', 'd42', 'd43', 'd44', 'd45', 'd46', 'd47', 'd48', 'd49', 'd50', 'd51', 'd52',
                     'd53', 'd54', 'd55', 'd56', 'd57', 'd58', 'd59',
                     'd60', 'm3', 'm4', 'm5']:
                i[k] = parse(v) if v else {}

    return data, total


def pitcher_overview(user_id, start, end, pitcher, page, page_size, order_by, order):
    if user_id in super_auth():
        op = ''
    else:
        user = tuple([i['nick_name'] for i in get_pitcher({'user_id': user_id})] + [get_user_name_by_id(user_id)])
        if len(user) == 1:
            op = f" and pitcher ='{user[0]}'"
        else:
            op = f" and pitcher in {str(user)}"
    op1 = f" and pitcher='{pitcher}' " if pitcher else ''
    op2 = f" and dt>='{start}' " if start else ''
    op3 = f" and dt<='{end}' " if end else ''
    op4 = f" order by {order_by} {order}" if order_by and order else ''

    db = MysqlUtils()

    sql = f""" select pitcher,
                DATE_FORMAT(min(dt),'%Y%m%d') start_date,
                DATE_FORMAT(max(dt),'%Y%m%d') end_date,
                sum(cost) cost,
                sum(reg_amount) amount,
                sum(first_order_amount) first_amount,
                round(sum(reg_amount)/sum(cost),4) roi,
                round(sum(first_order_amount)/sum(cost),4) first_roi,
                round(sum(d7)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi7,
                round(sum(d30)/sum(if(dt>=SUBDATE(CURRENT_DATE,INTERVAL 5 day),0,cost)),4) roi30,
                sum(reg_amount-cost) profit,
    round(sum(click_count)/sum(view_count),4) click_rate,
    round(sum(follow_user)/sum(click_count),4) follow_rate,
    round(sum(reg_order_user)/sum(click_count),4) order_rate,
    round(sum(first_order_user)/sum(click_count),4) first_order_rate,
    sum(view_count) view_count,
    sum(click_count) click_count,
    sum(follow_user) follow_user,
    sum(reg_order_user) order_user,
    sum(first_order_user) first_order_user,
    round(sum(cost)/sum(follow_user),2) follow_cost,
    round(sum(cost)/sum(reg_order_user),2) order_cost,
    round(sum(cost)/sum(first_order_user),2) first_order_cost,
    round(sum(reg_amount)/sum(reg_order_user),2) avg_amount,
    sum(ba1) td_amount,
    sum(ba2) yd_amount,
    sum(ba3) byd_amount,sum(d7) d7 ,sum(d30) d30  
    from dw_pitcher_trend where cost>0 and pitcher!='' {op} {op1} {op2} {op3} group by pitcher {op4}
    """

    sum_sql = f"""select '总计' as pitcher,
                sum(cost) cost,
                sum(amount) amount,
                sum(first_amount) first_amount,
                round(sum(amount)/sum(cost),4) roi,
                round(sum(first_amount)/sum(cost),4) first_roi,
                round(sum(d7)/sum(cost),4) roi7,
                round(sum(d30)/sum(cost),4) roi30,
                sum(amount-cost) profit,
                round(sum(click_count)/sum(view_count),4) click_rate,
                round(sum(follow_user)/sum(click_count),4) follow_rate,
                round(sum(order_user)/sum(click_count),4) order_rate,
                round(sum(first_order_user)/sum(click_count),4) first_order_rate,
                sum(view_count) view_count,
                sum(click_count) click_count,
                sum(follow_user) follow_user,
                sum(order_user) order_user,
                sum(first_order_user) first_order_user,
                round(sum(cost)/sum(follow_user),2) follow_cost,
                round(sum(cost)/sum(order_user),2) order_cost,
                round(sum(cost)/sum(first_order_user),2) first_order_cost,
                round(sum(amount)/sum(order_user),2) avg_amount,
                sum(td_amount) td_amount,
                sum(yd_amount) yd_amount,
                sum(byd_amount) byd_amount from ({sql}) a
"""

    return getLimitSumDataV2(db.dm, sql, sum_sql, page, page_size)


def boss_panel_summary(user_id, start, end, channel, pitcher, type, stage, page, page_size, order_by, order):
    if user_id in super_auth():
        op = ''
    else:
        x = UserAuthUtils.get_auth_channel(user_id)
        if len(x) == 0:
            return None, None
        elif len(x) == 1:
            op = f" and channel ='{x[0]}'"
        else:
            op = f" and channel in {str(UserAuthUtils.get_auth_channel(user_id))}"

    op1 = f" and pitcher='{pitcher}' " if pitcher else ''
    op2 = f" and dt>='{start}' " if start else ''
    op3 = f" and dt<='{end}' " if end else ''
    op4 = f" and type='{type}' " if type else ''
    op5 = f" and stage='{stage}' " if stage else ''
    op6 = f" and channel='{channel}' " if channel else ''
    op7 = f" order by {order_by} {order}" if order_by and order else 'order by dt desc'

    dm = MysqlUtils().dm

    sql = f"""select sum(order_amount) order_amount,sum(cost) cost,round(sum(reg_order_amount)/sum(cost),4) roi,dt from dw_channel                                                                                                                                                                                                                                                                                                                                             
    where 1=1 {op} {op1} {op2} {op3} {op4} {op5} {op6}
    GROUP BY dt HAVING order_amount+cost>0 {op7} """
    # print(sql)

    return getLimitData(dm, sql, page, page_size)


def image_rank(start, end, type, book, page, page_size, order_by, order):
    op1 = f" and book='{book}' " if book else ''
    op2 = f" and dt>='{start}' " if start else ''
    op3 = f" and dt<='{end}' " if end else ''
    op4 = f" and type='{type}' " if type else ''
    op5 = f" order by {order_by} {order}" if order_by and order else 'order by dt desc'

    db = MysqlUtils().quchen_text

    sql = f"""select preview_url,
            sum(cost),
            round(sum(click_count)/sum(view_count),4) ctr,
            round(sum(cost)/sum(click_count),2) cpc
            from dm_image_cost_day where preview_url!='' {op1} {op2} {op3} {op4} GROUP BY signature,preview_url
            {op5} """

    return getLimitData(db, sql, page, page_size)


"""广告排行榜"""


def advertisement_rank(user_id, start, end, type, page, page_size, order, order_by, book, channel, pitcher, has_order,
                       is_video, show_type):
    # TODO:修改为clickhouse来进行数据访问
    if user_id in super_auth():
        op1 = ''
    else:
        user = tuple([i['nick_name'] for i in get_pitcher({'user_id': user_id})] + [get_user_name_by_id(user_id)])
        if len(user) == 1:
            op1 = f" and pitcher ='{user[0]}'"
        else:
            op1 = f" and pitcher in {str(user)}"

    op4 = f" and channel='{channel}'" if channel else ''
    op5 = f" and pitcher ='{pitcher}' " if pitcher else ''
    op8 = f" and type='{type}'" if type else ''
    op10 = f" and book='{book}'" if book else ''
    op11 = f" and order_count>0" if has_order else ''
    op12 = f" and is_video" if is_video else ''
    # 公共数据,和素材库一样,个人只显示个人(小组)数据
    op_or1 = f' or (dt<date_add(now(),interval -5 day) or cost>5000) ' if show_type == 'public' else ''
    op_order = f" order by {order_by} {order}" if order_by and order else ''

    # 时间为基底限制,必须遵守
    op_time_bigger = f" and dt>='{start}' " if start else ''
    op_time_small = f" and dt<='{end}' " if end else ''

    db = MysqlUtils().dm

    sql = f"""
    select 
    campaign_id,
    dt,stage,platform,book,`type`, cost,channel,pitcher,owner,
    view_count,
    click_count,
    follow_count,
    round((cost/view_count)*1000,2) cpm, 
    round(click_count/view_count,4) ctr,
    round(cost/click_count,2) cpc,
    order_count,
    order_amount,
    round(order_count/click_count,4)  order_rate,
    round(order_amount/order_count,2) unit_price,
    round(cost/follow_count) follow_cost,
    round(cost/order_count,2) order_cost,
    round(order_amount/cost,4) roi,
    title,
    description,
    image_id,
    is_video,
    use_times,
    preview_url
    from 
     (  select 
    campaign_id,
    min(dt) as dt,
    max(stage) as stage,
    max(platform) as platform,
    max(book) as book,
    max(`type`) as type, 
    max(channel) as channel,
    max(pitcher) as pitcher,
    max(owner) as owner,
    sum(cost) as cost,
    sum(view_count) as view_count,
    sum(click_count) as click_count,
    sum(follow_count) as follow_count,
    sum(order_count) as order_count,
    sum(order_amount) as order_amount,
    sum(use_times) as use_times,
    max(title) as title,
    max(description) as description,
    max(image_id) as image_id,
    max(is_video) as is_video,
    max(preview_url) as preview_url
    from dw_image_cost_day
    where  (1=1 {op1}  {op_or1})  
    {op4} {op5} {op8} {op10} {op11}  {op12}  
    {op_time_bigger} {op_time_small}
    group by campaign_id  
) as a
    
     {op_order} 
    """

    print(sql)
    sum_sql = f"""select '总计' as pitcher,
                      sum(cost) cost,
                      sum(view_count) view_count,
                      sum(click_count) click_count,
                      sum(follow_count) follow_count,
                      round((sum(cost)/sum(view_count))*1000,4) cpm,
                      round(sum(click_count)/sum(view_count),4) ctr,
                      round(sum(cost)/sum(click_count),2) cpc,
                      sum(order_count),
                      sum(order_amount),
                      round(sum(order_count)/sum(click_count),4) order_rate,
                      round(sum(order_amount)/sum(order_count),2) unit_price,
                      round(sum(cost)/sum(follow_count),2) follow_cost,
                      round(sum(cost)/sum(order_count),2) order_cost,
                      sum(use_times) use_times,
                      round(sum(order_amount)/sum(cost),4) ROI
                      from ({sql}) a 
      """
    return getLimitSumData(db, sql, sum_sql, page, page_size)


"""创意展示"""


def idea_rank(user_id, start, end, page, page_size, order, order_by, book, channel, is_singleimg,
              is_video, data_type, campaign_ids):
    # TODO:修改为clickhouse来进行数据访问

    # 时间的归因-----获取到所有这段时间内的记录,并进行聚合(聚合周末再做,先把数据拿出来)
    # 认为素材消耗的数据，已经是一条数据的所有归因直接根据dt来就可以

    if user_id in super_auth():
        op1 = ''
    else:
        user = tuple([i['nick_name'] for i in get_pitcher({'user_id': user_id})] + [get_user_name_by_id(user_id)])
        if len(user) == 1:
            op1 = f" and pitcher ='{user[0]}'"
        else:
            op1 = f" and pitcher in {str(user)}"

    channel = channel.replace('朋友圈信息流', 'MP')
    channel = channel.replace('广点通', 'GDT')
    channel = "','".join(channel.split(','))
    channel = "'{}'".format(channel)
    op4 = f" and type in ({channel}) " if channel else ''

    op10 = f" and book='{book}'" if book else ''
    op11 = f" and image_id like '%,%' " if not is_singleimg else ''
    op12 = f" and is_video" if is_video == 2 else ' and not is_video'  # 进行对应修改1----图片

    if campaign_ids:
        campaign_ids = campaign_ids.split(',')
        campaign_ids = "','".join([str(i) for i in campaign_ids])
        campaign_ids = "'" + campaign_ids + "'"
    op_campaign_id = f'and campaign_id in ({campaign_ids})' if campaign_ids else ''

    # 公共数据,和素材库一样,个人只显示个人(小组)数据
    op_or1 = f' or (dt<date_add(now(),interval -5 day) or cost>5000) ' if data_type == 'all' else ''
    # clicktimes,view_times,consume_amount,click_rate---------数据进行一一对应
    if order_by == 'click_times':
        order_by = 'click_count'
    if order_by == 'view_times':
        order_by = 'view_count'
    if order_by == 'consume_amount':
        order_by = 'cost'
    if order_by == 'click_rate':
        order_by = 'if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) '
    if order_by == 'create_time' or order_by == 'start_date':
        order_by = 'cost'

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

    # 时间为基底限制,必须遵守
    op_time_bigger = f" and dt>='{start}' " if start else ''
    op_time_small = f" and dt<='{end}' " if end else ''

    db = MysqlUtils().dm

    sql = f'''
    select 
    campaign_id as id,
    book as novels,
    dt as startDate,
    date_format( now(),'%Y-%m-%d') as endDate,
    `type` as channel,
    'all' as dataType,
    owner as creator,
    0 as delFlag,
    False as isCollected,
    download_path as downloadPath,
    height,
    width,
    preview_url as media,
    format as mediaFormat,
    size as mediaSize,
    if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) as clickRate,
    round(width/if(height,height,1),2) aspect_ratio,
    cost as consumeAmount,
    view_count as viewTimes,
    click_count as clickTimes,
    round((cost/view_count)*1000,2) cpm, 
    round(click_count/view_count,4) ctr,
    round(cost/click_count,2) cpc,
    title,
    description as article,
    date_format( now(),'%Y-%m-%d %H:%i:%S') as upateTime,
    null as updateBy,
    if(is_video,2,1) as type,
    video_bit_rate  as videoBitRate,
    null as videoFirstPage,
    video_length as videoLength,
    use_times as userTimes
    from ( select
    max(campaign_id) as campaign_id ,
    max(book) as book,
    min(dt) as dt,
    max(image_id) as image_id,
    max(`type`) as type,
    max(owner) as owner,
    max(download_path) as download_path,
    max(height) as height,
    max(width) as width,
    max(preview_url) as preview_url,
    max(format) as format,
    max(size) as size,
    sum(cost) as cost,
    sum(view_count) as view_count,
    sum(click_count) as click_count,
    max(title) as title,
    max(is_video) as is_video,
    max(description) as description,
    max(video_bit_rate) as video_bit_rate,
    max(video_length) as video_length,
    sum(use_times) as use_times
    from dw_image_cost_day
    where replace (preview_url,' ,','') !=''
     and (1=1 {op1}  {op_or1}) 
    {op4}  {op10} {op11}  {op12}  {op_campaign_id}
    {op_time_bigger} {op_time_small} 
    group by campaign_id) as foo 
        {op_order} 

'''
    print(sql)
    data, total = getLimitData(db, sql, page, page_size)
    data = {'records': data, 'total': total, 'size': page_size, 'current': page, 'pages': int(total / page_size) + 1}
    return data


def media_rank(user_id, start, end, page, page_size, order, order_by, book, channel, is_singleimg,
               is_video, data_type, campaign_ids):
    # TODO:修改为clickhouse来进行数据访问

    # 时间的归因-----获取到所有这段时间内的记录,并进行聚合(聚合周末再做,先把数据拿出来)
    # 认为素材消耗的数据，已经是一条数据的所有归因直接根据dt来就可以

    if user_id in super_auth():
        op1 = ''
    else:
        user = tuple([i['nick_name'] for i in get_pitcher({'user_id': user_id})] + [get_user_name_by_id(user_id)])
        if len(user) == 1:
            op1 = f" and pitcher ='{user[0]}'"
        else:
            op1 = f" and pitcher in {str(user)}"

    channel = channel.replace('朋友圈信息流', 'MP')
    channel = channel.replace('广点通', 'GDT')
    channel = "','".join(channel.split(','))
    channel = "'{}'".format(channel)
    op4 = f" and type in ({channel}) " if channel else ''

    op10 = f" and book='{book}'" if book else ''

    op11 = f" and image_id like '%,%' " if not is_singleimg else ''
    op12 = f" and is_video" if is_video == 2 else ' and not is_video'  # 进行对应修改1----图片

    if campaign_ids:
        campaign_ids = campaign_ids.split(',')
        campaign_ids = "','".join([str(i) for i in campaign_ids])
        campaign_ids = "'" + campaign_ids + "'"
    op_campaign_id = f'and campaign_id in ({campaign_ids})' if campaign_ids else ''

    # 公共数据,和素材库一样,个人只显示个人(小组)数据
    op_or1 = f' or (dt<date_add(now(),interval -5 day) or cost>5000) ' if data_type == 'all' else ''
    # clicktimes,view_times,consume_amount,click_rate---------数据进行一一对应
    if order_by == 'click_times':
        order_by = 'click_count'
    if order_by == 'view_times':
        order_by = 'view_count'
    if order_by == 'consume_amount':
        order_by = 'cost'
    if order_by == 'click_rate':
        order_by = 'if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) '
    if order_by == 'create_time' or order_by == 'start_date':
        order_by = 'cost'

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

    # 时间为基底限制,必须遵守
    op_time_bigger = f" and dt>='{start}' " if start else ''
    op_time_small = f" and dt<='{end}' " if end else ''

    db = MysqlUtils().dm

    sql = f'''
    select 
    campaign_id as id,
    book as novels,
    dt as startDate,
    date_format( now(),'%Y-%m-%d') as endDate,
    `type` as channel,
    'all' as dataType,
    owner as creator,
    0 as delFlag,
    False as isCollected,
    download_path as downloadPath,
    height,
    width,
    preview_url as content,
    format as mediaFormat,
    size as mediaSize,
    if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) as clickRate,
    round(width/if(height,height,1),2) aspect_ratio,
    cost as consumeAmount,
    view_count as viewTimes,
    click_count as clickTimes,
    round((cost/view_count)*1000,2) cpm, 
    round(click_count/view_count,4) ctr,
    round(cost/click_count,2) cpc,
    date_format( now(),'%Y-%m-%d %H:%i:%S') as upateTime,
    null as updateBy,
    if (image_id not like '%,%'  ,true,false) as singleImg,
    if(is_video,2,1) as type,
    video_bit_rate  as videoBitRate,
    null as videoFirstPage,
    video_length as videoLength,
    use_times as userTimes
    from 
    ( select
    max(campaign_id) as campaign_id ,
    max(book) as book,
    min(dt) as dt,
    max(image_id) as image_id,
    max(`type`) as type,
    max(owner) as owner,
    max(download_path) as download_path,
    max(height) as height,
    max(width) as width,
    max(preview_url) as preview_url,
    max(format) as format,
    max(size) as size,
    sum(cost) as cost,
    sum(view_count) as view_count,
    sum(click_count) as click_count,
    max(title) as title,
    max(is_video) as is_video,
    max(description) as description,
    max(video_bit_rate) as video_bit_rate,
    max(video_length) as video_length,
    sum(use_times) as use_times
    from dw_image_cost_day
        where replace (preview_url,' ,','') !=''
     and (1=1 {op1}  {op_or1}) 
    {op4}   {op10} {op11}  {op12}  {op_campaign_id}
    {op_time_bigger} {op_time_small} 
    group by campaign_id) as foo
        {op_order} 

'''

    print(sql)
    data, total = getLimitData(db, sql, page, page_size)
    data = {'records': data, 'total': total, 'size': page_size, 'current': page, 'pages': int(total / page_size) + 1}
    return data


def content_rank(user_id, start, end, page, page_size, order, order_by, book, channel, data_type, campaign_ids):
    # TODO:修改为clickhouse来进行数据访问

    # 时间的归因-----获取到所有这段时间内的记录,并进行聚合(聚合周末再做,先把数据拿出来)
    # 认为素材消耗的数据，已经是一条数据的所有归因直接根据dt来就可以

    if user_id in super_auth():
        op1 = ''
    else:
        user = tuple([i['nick_name'] for i in get_pitcher({'user_id': user_id})] + [get_user_name_by_id(user_id)])
        if len(user) == 1:
            op1 = f" and pitcher ='{user[0]}'"
        else:
            op1 = f" and pitcher in {str(user)}"

    channel = channel.replace('朋友圈信息流', 'MP')
    channel = channel.replace('广点通', 'GDT')
    channel = "','".join(channel.split(','))
    channel = "'{}'".format(channel)
    op4 = f" and type in ({channel}) " if channel else ''
    op10 = f" and book='{book}'" if book else ''

    if campaign_ids:
        campaign_ids = campaign_ids.split(',')
        campaign_ids = "','".join([str(i) for i in campaign_ids])
        campaign_ids = "'" + campaign_ids + "'"
    op_campaign_id = f'and campaign_id in ({campaign_ids})' if campaign_ids else ''

    # 公共数据,和素材库一样,个人只显示个人(小组)数据
    op_or1 = f' or (dt<date_add(now(),interval -5 day) or cost>5000) ' if data_type == 'all' else ''
    # clicktimes,view_times,consume_amount,click_rate---------数据进行一一对应
    if order_by == 'click_times':
        order_by = 'click_count'
    if order_by == 'view_times':
        order_by = 'view_count'
    if order_by == 'consume_amount':
        order_by = 'cost'
    if order_by == 'click_rate':
        order_by = 'if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) '
    if order_by == 'create_time' or order_by == 'start_date':
        order_by = 'cost'

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

    # 时间为基底限制,必须遵守
    op_time_bigger = f" and dt>='{start}' " if start else ''
    op_time_small = f" and dt<='{end}' " if end else ''

    db = MysqlUtils().dm

    sql = f"""
    select 
    campaign_id as id,
    book as novels,
    dt as startDate,
    date_format( now(),'%Y-%m-%d') as endDate,
    'all' as dataType,
    owner as creator,
    False as isCollected,
    if(click_count=0 or view_count =0 or view_count is null or click_count is null,0,round(click_count / view_count,2)) as clickRate,
    round(width/if(height,height,1),2) aspect_ratio,
    cost as consumeAmount,
    view_count as viewTimes,
    click_count as clickTimes,
    round((cost/view_count)*1000,2) cpm, 
    round(click_count/view_count,4) ctr,
    round(cost/click_count,2) cpc,
    REPLACE(REPLACE(description , CHAR(10), ''), CHAR(13), '') as content,
    REPLACE(REPLACE(title, CHAR(10), ''), CHAR(13), '') as title,
    date_format( now(),'%Y-%m-%d %H:%i:%S') as upateTime,
    null as updateBy,
    use_times as userTimes
    from (
     select
    max(campaign_id) as campaign_id ,
    max(book) as book,
    min(dt) as dt,
    max(image_id) as image_id,
    max(`type`) as type,
    max(owner) as owner,
    max(download_path) as download_path,
    max(height) as height,
    max(width) as width,
    max(preview_url) as preview_url,
    max(format) as format,
    max(size) as size,
    sum(cost) as cost,
    sum(view_count) as view_count,
    sum(click_count) as click_count,
    max(title) as title,
    max(is_video) as is_video,
    max(description) as description,
    max(video_bit_rate) as video_bit_rate,
    max(video_length) as video_length,
    sum(use_times) as use_times
    from dw_image_cost_day 
    where (REPLACE(REPLACE(description, CHAR(10), ''), CHAR(13), '') is not null 
    or REPLACE(REPLACE(title, CHAR(10), ''), CHAR(13), '') is not null )
    and (1=1 {op1}  {op_or1})
    {op4} {op10} {op_campaign_id}
    {op_time_bigger} {op_time_small} 
    group by campaign_id ) as foo
     {op_order}
    """

    print(sql)
    data, total = getLimitData(db, sql, page, page_size)
    data = {'records': data, 'total': total, 'size': page_size, 'current': page, 'pages': int(total / page_size) + 1}

    return data


def label_list(page, page_size):
    db = MysqlUtils().dm

    sql = '''
        select id,label,date_format( update_time,'%Y-%m-%d') as updateTime,
         date_format( create_time,'%Y-%m-%d') as createTime
         from ads_label al 
         where del_flag=0
    '''
    data = db.getData_json(sql)

    total_sql = '''
        select count(*) from ads_label al  where del_flag=0;
    '''
    data_total = db.getData(total_sql)
    total = int(data_total[0][0])

    data = {'records': data, 'total': total, 'size': page_size, 'current': page, 'pages': int(total / page_size) + 1}
    print(data)
    return data


def label_update():
    pass


def label_del(label_id, user_id):
    if label_id is None:
        return
    sql = f'''            
        update ads_label 
        set del_flag=1 ,update_by ={user_id}
        where id ='{label_id}'
    '''
    db = MysqlUtils().dm
    db.execute(sql)


def label_add(label, user_id):
    if label is None:
        return
    sql = f'''
        insert into ads_label 
    (label,create_by) values ('{label}',{user_id})
    '''
    db = MysqlUtils().dm
    db.execute(sql)


if __name__ == '__main__':
    print(get_pitcher({"user_id": 78}))