from model.DateUtils import DateUtils
from model.DataBaseUtils import *
from model.log import logger
from model.CommonUtils import *
du = DateUtils()
log = logger()

def book_rank(start,end,book,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 ''

    sql=f"""select 
concat(DATE_FORMAT(min(dt),'%Y/%m/%d') ,'~',DATE_FORMAT(max(dt),'%Y/%m/%d')) date,
pitcher,book,
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) frist_roi,
round(sum(reg_order_amount)/sum(cost),4) roi
from dw_channel where 1=1 {op1} {op2} {op3} GROUP BY channel,book,pitcher
 {op4}"""
    print(sql)
    return getLimitData(db.dm,sql,page,page_size)