--素材审核表
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local mysql = require "skynet.db.mysql"
local config = require "run_config"
local cjson = require "cjson"
local db
local mysqldtaskbx = {}

function M.search(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number","material_id","oce_material_id",
    "is_sexual_inducement_content","is_bad_value_view","start_time_date","end_time_date"},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 material_id_param = ""
    if msg_body.material_id~="" then
        material_id_param =  string.format(" AND material_id = %d ",tonumber(msg_body.material_id)) 
    end

    local oce_material_param = ""
    if msg_body.oce_material_id~="" then
        oce_material_param =string.format("AND oce_material_id =  %d ",tonumber(msg_body.oce_material_id))
    end

    local is_sexual_inducement_content_param = ""
    if msg_body.is_sexual_inducement_content~="" then
        is_sexual_inducement_content_param = "AND is_sexual_inducement_content = "..msg_body.is_sexual_inducement_content.." "
    end

    local is_bad_value_view_param = ""
    if msg_body.is_bad_value_view~="" then
        is_bad_value_view_param = "AND is_bad_value_view = "..msg_body.is_bad_value_view.." "
    end

    local date_param = ""
    if msg_body.start_time_date~="" and msg_body.end_time_date~="" then
        date_param = " AND DATE(create_at) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_time_date / 1000) .. ")) AND DATE(create_at) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_time_date / 1000) .. "))"
    end

    local param = material_id_param..oce_material_param..is_sexual_inducement_content_param..is_bad_value_view_param..date_param

    local sql = "SELECT * FROM video_material_review where 1=1 "..param..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    skynet.error("sql:",sql)
    local list = mysqldtaskbx.Singleton().query(sql)
    sql = "SELECT   COUNT(*) AS total  FROM video_material_review where 1=1 "..param
    local total = mysqldtaskbx.Singleton().query(sql)
    return true,list,total[1].total
end

function mysqldtaskbx.start()
    local function on_connect(db)
        db:query("set charset utf8mb4");
    end
    local conf = config.db_cnf.book_server.mysqldb_material_review_cnf
    db = mysql.connect{
        host=conf.ip,
        port=conf.port,
        database=conf.db,
        user=conf.user,
        password=conf.password,
        charset="utf8mb4",
        max_packet_size = 1024 * 1024,
        on_connect = on_connect
    }
    if not db then
       skynet.error("mysql connect fail")
    end
end

function mysqldtaskbx.Singleton()
    if db == nil then
        mysqldtaskbx.start()
    end
    return mysqldtaskbx
end
function mysqldtaskbx.query(sql)
    return db:query(sql)
end

return M