jl_tg_config_back/service/backmgr/video_product.lua

--书库
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"
local config = require "run_config"
local mysql = require "skynet.db.mysql"
local db
local mysqldtaskbx = {}

--搜索书
--tg_platform_id 平台id
--product_id 书id
--product_name 书名
function M.search_book_data(msg_body)
    local isok ,key =  tools.checkData({
    "is_top",
    "status",
    "start_publish_time",
    "end_publish_time",
    "up_or_down_publish_time",
    "min_book_word",
    "max_book_word",
    "alias_name",
    "product_name",
    "product_id",
    "tg_platform_id",
    "oce_material_id",
    "page_size",
    "page_number",
    "is_auto",
    "stat_cost","min_totalChapterNum","max_totalChapterNum"},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 is_top_param = ""
    if msg_body.is_top~="" then
        is_top_param = string.format(" AND is_top = %d ",msg_body.is_top)
    end

    local status_param = ""
    if msg_body.status~="" then
        status_param = string.format(" AND status = %d ",msg_body.status)
    end

    local date_param = ""
    if msg_body.start_publish_time~="" and msg_body.end_publish_time~="" then
        date_param = " AND DATE(publish_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_publish_time / 1000) .. ")) AND DATE(publish_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_publish_time / 1000) .. ")) "
    end

    local up_or_down_publish_time_param = ""
    if msg_body.up_or_down_publish_time~="" then
        if(msg_body.up_or_down_publish_time == 0) then
            up_or_down_publish_time_param = " publish_time ASC"
        else
            up_or_down_publish_time_param = " publish_time DESC"
        end
    end

    local word_param = ""
    if msg_body.min_book_word~="" and msg_body.max_book_word~="" then
        word_param =  string.format(" AND CAST(words AS UNSIGNED) >= %f  AND CAST(words AS UNSIGNED) <= %f ",msg_body.min_book_word,msg_body.max_book_word)
    end

    local stat_cost_param = ""
    if msg_body.stat_cost~="" then
        if(msg_body.stat_cost == 0) then
            stat_cost_param = " stat_cost ASC"
        else
            stat_cost_param = " stat_cost DESC"
        end
    end

    local totalChapterNum_param = ""
    if msg_body.min_totalChapterNum~="" and msg_body.max_totalChapterNum~="" then
        totalChapterNum_param =  string.format(" AND totalChapterNum >= %d  AND totalChapterNum <= %d ",msg_body.min_totalChapterNum,msg_body.max_totalChapterNum)
    end


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

    local product_name_param = ""
    if msg_body.product_name~="" then
        product_name_param = string.format(" AND  ( product_name LIKE CONCAT( '%%%s%%')) ",msg_body.product_name)
    end
    local tg_platform_param = ""
    if msg_body.tg_platform_id~="" then
        tg_platform_param = string.format(" AND book_platform = %d ",msg_body.tg_platform_id) 
    end
    local is_auto_param = ""
    if msg_body.is_auto~="" then
        is_auto_param = " AND is_auto = "..msg_body.is_auto.." "
    end

    local is_store_param = ""
    if msg_body.is_store~="" then
        is_store_param = " AND is_store = "..msg_body.is_store.." "
    end

    local genre_param = ""
    if msg_body.genre~="" then
        genre_param = " AND genre = "..msg_body.genre.." "
    end

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

    local param = is_top_param..status_param..date_param..totalChapterNum_param..product_param..product_name_param..tg_platform_param..is_auto_param..is_store_param..genre_param..alias_name_param..word_param;

    if stat_cost_param~="" and up_or_down_publish_time_param ~="" then
        up_or_down_publish_time_param = " , "..up_or_down_publish_time_param
    end
    local up_down_param = stat_cost_param..up_or_down_publish_time_param

    if up_down_param ~= "" then
        up_down_param = " ORDER BY " ..up_down_param
    else
        up_down_param =  "ORDER BY id DESC"
    end
    local sql = "SELECT * FROM video_product where 1=1 "..param..up_down_param..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    

    local res = mysqldtaskbx.Singleton().query(sql)

    sql =  "SELECT  COUNT(*) AS total  FROM video_product where 1=1 "..param.."ORDER BY id DESC"

    local total = mysqldtaskbx.Singleton().query(sql)

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

--一键发布搜索
function M.search_book_data_on_main(msg_body)
    local isok ,key =  tools.checkData({
    "start_publish_time",
    "end_publish_time",
    "alias_name",
    "product_name",
    "product_id",
    "tg_platform_id",
    "oce_material_id",
    "page_size",
    "page_number",
    "is_auto",
    "stat_cost",
    "min_book_word",
    "max_book_word",
    "min_stat_cost",
    "max_stat_cost"},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 date_param = ""
    if msg_body.start_publish_time~="" and msg_body.end_publish_time~="" then
        date_param = " AND DATE(publish_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_publish_time / 1000) .. ")) AND DATE(publish_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_publish_time / 1000) .. ")) "
    end

    local word_param = ""
    if msg_body.min_book_word~="" and msg_body.max_book_word~="" then
        word_param =  string.format(" AND CAST(words AS UNSIGNED) >= %d  AND CAST(words AS UNSIGNED) <= %d ",msg_body.min_book_word,msg_body.max_book_word)
    end

    local stat_cost_param = ""
    if msg_body.min_stat_cost~="" and msg_body.max_stat_cost~="" then
        stat_cost_param =  string.format(" AND stat_cost >= %f  AND stat_cost <= %f ",msg_body.min_stat_cost,msg_body.max_stat_cost)
    end

    local product_param = ""
    if msg_body.product_id~="" then
        product_param = string.format(" AND product_id = '%s' ",msg_body.product_id)
    end
    local product_name_param = ""
    if msg_body.product_name~="" then
        product_name_param = string.format(" AND  ( product_name LIKE CONCAT( '%%%s%%')) ",msg_body.product_name)
    end
    local tg_platform_param = ""
    if msg_body.tg_platform_id~="" then
        tg_platform_param = string.format(" AND book_platform = %d ",msg_body.tg_platform_id) 
    end
    local is_auto_param = ""
    if msg_body.is_auto~="" then
        is_auto_param = " AND is_auto = "..msg_body.is_auto.." "
    end

    local is_store_param = ""
    if msg_body.is_store~="" then
        is_store_param = " AND is_store = "..msg_body.is_store.." "
    end

    local genre_param = ""
    if msg_body.genre~="" then
        genre_param = " AND genre = "..msg_body.genre.." "
    end

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

    local param = date_param..stat_cost_param..word_param..product_param..product_name_param..tg_platform_param..is_auto_param..is_store_param..genre_param..alias_name_param;

    local sql =  string.format("SELECT v.* FROM video_product v JOIN ( SELECT id FROM video_product WHERE  1=1 %s ORDER BY is_top DESC , id ASC LIMIT %d OFFSET %d ) AS tmp ON v.id = tmp.id",param,page_size,offset)
    
    skynet.error("sql:",sql)
    local res = mysqldtaskbx.Singleton().query(sql)

    sql =  "SELECT  COUNT(*) AS total  FROM video_product where 1=1 "..param

    local total = mysqldtaskbx.Singleton().query(sql)

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

--设置书别名
function M.set_video_product_alias_name(msg_body)
    local isok ,key =  tools.checkData({"id","alias_name"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local sql = string.format("UPDATE  `video_product` SET alias_name = '%s'  WHERE id = %d ",
    msg_body.alias_name,msg_body.id)
    local res = mysqldtaskbx.Singleton().query(sql)
    return true,{}
end

--设置书类型（长篇，短片）
function M.set_video_product_genre(msg_body)
    local isok ,key =  tools.checkData({"genre","id_list"},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("UPDATE  video_product SET genre = %d WHERE id IN (%s) ",msg_body.genre,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置书状态
function M.set_status(msg_body)
    local isok ,key =  tools.checkData({"status","id_list"},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("UPDATE  video_product SET status = %d WHERE id IN (%s) ",msg_body.status,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置书优先级
function M.set_top(msg_body)
    local isok ,key =  tools.checkData({"expired_time","id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    -- local current_time = os.date("%Y-%m-%d %H:%M:%S")
    local sql  = ""
    local isok,res;
    for i = 1, #msg_body.id_list, 1 do
        local id = msg_body.id_list[i]
        sql = string.format("SELECT * FROM video_product where id = %d ", id)
        res = mysqldtaskbx.Singleton().query(sql)
        if #res >0 then
            local publish_time = res[1].publish_time
            local y_publish_time = res[1].y_publish_time
            if y_publish_time~=nil then
                publish_time = y_publish_time
            end
            sql = string.format("UPDATE  video_product SET is_top = 1 , expired_time = '%s' , y_publish_time = '%s' WHERE id = %d ",msg_body.expired_time,publish_time,id)
            mysqldtaskbx.Singleton().query(sql)
        end
     end
    -- local idString = table.concat(msg_body.id_list, ",")
    -- local sql = string.format("UPDATE  video_product SET is_top = 1 , expired_time = '%s' WHERE id IN (%s) ",msg_body.expired_time,idString)
    -- mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

function mysqldtaskbx.start()
    local function on_connect(db)
        db:query("set charset utf8mb4");
    end
    local conf = config.db_cnf.book_server.mysqldb_task_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