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_match",
    "start_create_at",
    "end_create_at",
    "product_parent_id",
    "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_match_param = ""
    if msg_body.is_match~="" then
        if msg_body.is_match == 1 then
            is_match_param = string.format(" AND match_book != 'NONE' ")
        else
            is_match_param = string.format(" AND match_book='NONE' ")
        end
        
    end

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

    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 create_date_param = ""
    if msg_body.start_create_at~="" and msg_body.end_create_at~="" then
        create_date_param = " AND DATE(create_at) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_create_at / 1000) .. ")) AND DATE(create_at) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_create_at / 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_match_param..create_date_param..product_parent_id_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 status_param = string.format(" AND status = %d ",1)

    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 = status_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 M.set_default_pay_section(msg_body)
    local isok ,key =  tools.checkData({"default_pay_section","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 default_pay_section = %d WHERE id IN (%s) ",msg_body.default_pay_section,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置价格
function M.set_default_default_price(msg_body)
    local isok ,key =  tools.checkData({"default_price","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 default_price = %f WHERE id IN (%s) ",msg_body.default_price,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置书籍定价方式
function M.set_fee_unit(msg_body)
    local isok ,key =  tools.checkData({"fee_unit","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 fee_unit = %d WHERE id IN (%s) ",msg_body.fee_unit,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置上架时间
function M.set_list_time(msg_body)
    local isok ,key =  tools.checkData({"list_time","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 list_time = '%s' WHERE id IN (%s) ",msg_body.list_time,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--设置 专辑链接 album_link
function M.set_album_link(msg_body)
    local isok ,key =  tools.checkData({"album_link","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 album_link = '%s' WHERE id IN (%s) ",msg_body.album_link,idString)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--同步匹配书籍
function M.sync_match_book_id(msg_body)
    local isok ,key =  tools.checkData({"match_book","product_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = ""
    sql = string.format("SELECT id FROM video_product where product_id = '%s'  LIMIT 1 ",msg_body.match_book)
    local res = mysqldtaskbx.Singleton().query(sql)
    if #res<=0 then
        return true,{status=0,msg="请先创建对应的匹配书籍："..msg_body.match_book}
    end
    sql = string.format("UPDATE  video_product SET match_book = '%s' WHERE product_id = '%s' ",msg_body.match_book,msg_body.product_id)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--添加书籍数据
function M.add_book_data(msg_body)
    local isok ,key =  tools.checkData({"list_time","words","author","fee_unit","default_price","default_pay_section","publish_time","status","product_parent_id","tg_platform_id","product_id","product_name"
    ,"genre","is_store"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local album_link = ""
    if msg_body.album_link~=nil then
        album_link = msg_body.album_link
    end

    local match_book = "NONE"
    if msg_body.match_book~=nil then
        match_book = msg_body.match_book
    end

    local totalChapterNum = 0
    if msg_body.totalChapterNum~=nil then
        totalChapterNum = msg_body.totalChapterNum
    end
    local sql ;
    local isok,res;
    local sql = string.format("SELECT id FROM video_product where product_id = '%s'  LIMIT 1 ",msg_body.product_id)
    res = mysqldtaskbx.Singleton().query(sql)

    if #res>0 then
        return false,"书籍已存在！"
    end

    sql = string.format("INSERT INTO `video_product` (list_time,totalChapterNum,match_book,words,author,album_link,fee_unit,default_price,default_pay_section,publish_time,status,product_parent_id,book_platform,product_id, product_name, genre,is_store,is_auto)  VALUES ('%s',%d,'%s','%s','%s','%s',%d,%f,%d,'%s',%d,'%s',%d,'%s','%s',%d,%d,0)",
        msg_body.list_time,
        totalChapterNum,
        match_book,
        msg_body.words,
        msg_body.author,
        album_link,
        msg_body.fee_unit,
        msg_body.default_price,
        msg_body.default_pay_section,
        msg_body.publish_time,
        msg_body.status,
        msg_body.product_parent_id,
        msg_body.tg_platform_id,
        msg_body.product_id,
        msg_body.product_name,
        msg_body.genre,msg_body.is_store)

    res = 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