--审核建议列表
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"

function M.set_status(msg_body)
    local isok ,key =  tools.checkData({"id_list","status"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("SELECT * FROM promotion_audit_suggestions WHERE id IN (%s)",idString)
    local isok,res;
    res = mysqldbx.query(sql)

    for i = 1, #res, 1 do
        local id =   res[i].id
        sql = string.format("UPDATE  promotion_audit_suggestions SET status = %d WHERE id =%d ",msg_body.status,id)
        mysqldbx.query(sql)
    end

    return true,{}
end

function M.search_promotion_audit_suggestions(msg_body)
    local isok ,key =  tools.checkData({"butler_id","page_size","page_number","start_create_time","end_create_time",
    "advertiser_id","promotion_id","material_type","material_item","status"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local page_size = msg_body.page_size
    local page_number = msg_body.page_number
    local offset = (page_number - 1) * page_size

    
    local butler_id_param = ""
    if msg_body.butler_id~="" then
        butler_id_param =  " AND butler_id = "..msg_body.butler_id
    end

    local advertiser_id_param = ""
    if msg_body.advertiser_id~="" then
        advertiser_id_param =  " AND advertiser_id = "..msg_body.advertiser_id
    end

    local promotion_id_param = ""
    if msg_body.promotion_id~="" then
        promotion_id_param =  " AND promotion_id = "..tonumber(msg_body.promotion_id)
    end

    local material_type_param = ""
    if msg_body.material_type~="" then
        material_type_param =  string.format( " AND material_type = '%s' ",msg_body.material_type)
    end

    local material_item_param = ""
    if msg_body.material_item~="" then
        material_item_param =  string.format(" AND  ( material_item LIKE CONCAT( '%%%s%%')) ",msg_body.material_item)
    end

    local status_param = ""
    if msg_body.status~="" then
        status_param = " AND status = "..msg_body.status
    end

    local product_id_param = ""
    if msg_body.product_id~="" then
        product_id_param = " AND product_id = "..tonumber(msg_body.product_id)
    end

    local product_library_id_param = ""
    if msg_body.product_library_id~="" then
        product_library_id_param = " AND product_library_id = "..tonumber(msg_body.product_library_id)
    end

    local create_date_param = ""
    if msg_body.start_create_time~="" and msg_body.end_create_time~="" then
        create_date_param = " AND DATE(created_at) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_create_time / 1000) .. ")) AND DATE(created_at) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_create_time / 1000) .. "))"
    end

    local param = create_date_param..advertiser_id_param..promotion_id_param..material_type_param..material_item_param..status_param..product_id_param..product_library_id_param..butler_id_param

    local sql = "SELECT * FROM promotion_audit_suggestions WHERE 1=1 "..param.. " ORDER BY cost DESC "..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
   
    local list = mysqldbx.query(sql)

    sql = "SELECT   COUNT(*) AS total  FROM promotion_audit_suggestions WHERE 1=1  "..param

    local total = mysqldbx.query(sql)

    return true,list,total[1].total
end

return M