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({
    "alias_name",
    "product_name",
    "product_id",
    "tg_platform_id",
    "oce_material_id",
    "page_size",
    "page_number","is_auto"},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 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 = product_param..product_name_param..tg_platform_param..is_auto_param..is_store_param..genre_param..alias_name_param;

    local sql = "SELECT * FROM video_product where 1=1 "..param.." ORDER BY id DESC "..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.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 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