dj_back/service/backmgr/video_product_material.lua

--下载队列
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"
local mysql = require "skynet.db.mysql"
local config = require "run_config"
local db
local mysqldtaskbx = {}
function M.set_status(msg_body)
    local isok ,key =  tools.checkData({"id_list","is_download"},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 video_product_material WHERE id IN (%s)",idString)
    local isok,res;
    res = mysqldtaskbx.Singleton().query(sql)

    for i = 1, #res, 1 do
        local id =   res[i].id
        sql = string.format("UPDATE  video_product_material SET is_download = %d  WHERE id =%d ",msg_body.is_download,id)
        mysqldtaskbx.Singleton().query(sql)
    end

    return true,{}
end

function M.set_d_z_number(msg_body)
    local isok ,key =  tools.checkData({"id_list","d_z_number"},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 video_product_material WHERE id IN (%s)",idString)
    local isok,res;
    res = mysqldtaskbx.Singleton().query(sql)

    for i = 1, #res, 1 do
        local id =   res[i].id
        sql = string.format("UPDATE  video_product_material SET  d_z_number = %d WHERE id =%d ",msg_body.d_z_number,id)
        mysqldtaskbx.Singleton().query(sql)
    end

    return true,{}
end

function M.search_video_product_material(msg_body)
    local isok ,key =  tools.checkData({
    "start_publish_time",
    "end_publish_time",
    "up_or_down_publish_time",
    "page_size",
    "page_number",
    "start_create_time",
    "end_create_time",
    "dy_id",
    "product_id",
    "product_name",
    "book_platform",
    "title",
    "is_download",
    "signature",
    "cleaning_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 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 dy_id_param = ""
    if msg_body.dy_id~="" then
        dy_id_param =  " AND dy_id = "..msg_body.dy_id
    end

    local product_id_param = ""
    if msg_body.product_id~="" then
        product_id_param =  " AND product_id = "..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 book_platform_param = ""
    if msg_body.book_platform~="" then
        book_platform_param =  " AND book_platform = "..msg_body.book_platform
    end

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

    local is_download_param = ""
    if msg_body.is_download~="" then
        is_download_param =   " AND is_download = "..msg_body.is_download
    end


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

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

    local cleaning_status_param = ""
    if msg_body.cleaning_status~="" then
        cleaning_status_param = " AND cleaning_status = "..msg_body.cleaning_status
    end


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

    local param = date_param..dy_id_param..product_id_param..product_name_param..book_platform_param..title_param..is_download_param..status_param..create_date_param..cleaning_status_param

    local up_down_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  update_time DESC "
    end

    local sql = "SELECT * FROM video_product_material WHERE dy_id!=0  "..param.. up_down_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_product_material  WHERE dy_id!=0  "..param

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

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

function M.delete_video_product_material(msg_body)
    local isok ,key =  tools.checkData({"id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    for i = 1, #msg_body.id_list, 1 do
        local id = msg_body.id_list[i]
        local sql = string.format("DELETE FROM video_product_material WHERE id = %d ",id)
        mysqldtaskbx.Singleton().query(sql)
    end
    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