dj_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 = {}

function M.search_xia_jia(msg_body)
    local isok ,key =  tools.checkData({"id_list","page_size",
    "page_number",},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 idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("SELECT * FROM video_product WHERE  product_id IN (%s)",idString)..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    local isok,res;
    skynet.error("sql:",sql)
    local res = mysqldtaskbx.Singleton().query(sql)
    sql = string.format("SELECT  COUNT(*) AS total FROM video_product WHERE  product_id IN (%s)",idString)
    skynet.error("sql:",sql)
    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_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 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..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.."ORDER BY id DESC"

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

    return true,res,total[1].total
end
--搜索书
--tg_platform_id 平台id
--product_id 书id
--product_name 书名
function M.search_book_data(msg_body)
    local isok ,key =  tools.checkData({
        "is_album_link",
        "start_create_at",
        "end_create_at",
        "is_top",
        "status",
        "start_publish_time",
        "end_publish_time",
        "up_or_down_publish_time",
        "alias_name",
        "product_name",
        "product_id",
        "tg_platform_id",
        "oce_material_id",
        "page_size",
        "page_number",
        "is_auto",
        "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 is_album_link_param = ""
        if msg_body.is_album_link~="" then
            if msg_body.is_album_link == 1 then
                is_album_link_param = string.format(" AND  album_link IS NOT NULL  ").."AND album_link!=''  " 
            else
                is_album_link_param = string.format("  AND ( album_link IS NULL OR album_link=''  ) ")
            end
        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 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 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 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_album_link_param..create_date_param..is_top_param..status_param..date_param..product_param..product_name_param..tg_platform_param..is_auto_param..is_store_param..genre_param..alias_name_param;
    

        local up_down_param = ""

        
        if up_or_down_publish_time_param ~= "" then
            up_down_param = up_or_down_publish_time_param
        end

        if stat_cost_param ~= "" then
            if (up_down_param~="") then
                up_down_param = up_down_param.." , "..stat_cost_param
            else
                up_down_param = stat_cost_param
            end
            
        end

        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)
        
        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_video_product_start_chapter(msg_body)
    local isok ,key =  tools.checkData({"start_chapter","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 start_chapter = %d WHERE id IN (%s) ",msg_body.start_chapter,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_seq_num(msg_body)
    local isok ,key =  tools.checkData({"seq_num","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 seq_num = %d WHERE id IN (%s) ",msg_body.seq_num,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.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

--设置书优先级
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.zy_match_album_link_by_name(msg_body)
    local isok ,key =  tools.checkData({"list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = ""
    local res = nil
    local notFindList = {}
    for i = 1, #msg_body.list, 1 do
        local item = msg_body.list[i]
        local name = item.name
        local album_link = item.album_link
        sql = string.format("SELECT * FROM video_product where product_name = '%s' AND book_platform = 7  LIMIT 1 ",name)
        res = mysqldtaskbx.Singleton().query(sql)
        if #res>0 then
            sql = string.format("UPDATE  video_product SET album_link = '%s' WHERE id = %d ",album_link,res[1].id)
            mysqldtaskbx.Singleton().query(sql)
        else
            table.insert(notFindList,#notFindList+1,name)
        end
    end
    return true, notFindList
end

--添加书籍数据
function M.add_book_data(msg_body)
    local isok ,key =  tools.checkData({"list_time","free_time","album_link","fee_mode","episode_price","seq_num","status","tg_platform_id","product_id","product_name"
    ,"genre","is_store","start_chapter","publish_time"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql ;
    local isok,res;
    local sql = string.format("SELECT * 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,free_time,album_link,fee_mode,episode_price,seq_num,status,book_platform,product_id, product_name, genre,is_store,is_auto,start_chapter,publish_time)  VALUES ('%s','%s','%s',%d,%d,%d,%d,%d,'%s','%s',%d,%d,0,%d,'%s')",
    msg_body.list_time,
    msg_body.free_time,
    msg_body.album_link,
    msg_body.fee_mode,
    msg_body.episode_price,
    msg_body.seq_num,
    msg_body.status,
    msg_body.tg_platform_id,
    msg_body.product_id,
    msg_body.product_name,
    msg_body.genre,msg_body.is_store,msg_body.start_chapter,msg_body.publish_time)

    res = mysqldtaskbx.Singleton().query(sql)
    tools.dump(res)
    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