jl_tg_config_back/service/backmgr/video_applet_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.check_table_exists(table_name)
    local sql = string.format("SHOW TABLES LIKE '%s'", table_name)
    local res =  mysqldtaskbx.Singleton().query(sql)
    return res and #res > 0
end

function M.add_applet_product(msg_body)

    local isok ,key =  tools.checkData({"butler_id","product_name","product_id","book_platform","dy_small_program_start","dy_small_program_start_data","dy_small_applet_app_id","check_url","main_id","status"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    local sql = string.format("INSERT INTO `%s` (product_name,product_id,book_platform,dy_small_program_start,dy_small_program_start_data,dy_small_applet_app_id,check_url,main_id,status,wait_status)  VALUES ('%s','%s',%d,'%s','%s','%s','%s',%d,%d,%d)",
    table_name,msg_body.product_name,msg_body.product_id,msg_body.book_platform,msg_body.dy_small_program_start,msg_body.dy_small_program_start_data,msg_body.dy_small_applet_app_id,msg_body.check_url,msg_body.main_id,msg_body.status,2)
    mysqldtaskbx.Singleton().query(sql)
    return true, {}
end

--开启小程序书籍任务
function M.open_app_book_task(msg_body)
    
    local isok ,key =  tools.checkData({"butler_id","id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    for i = 1, #msg_body.id_list, 1 do
        local res,sql,id;
        id = msg_body.id_list[i]
        sql = string.format("UPDATE  %s SET status = 0 ,  wait_status = 0 ,is_close_execution = 1  WHERE id = %d ",table_name,id)
        res = mysqldtaskbx.Singleton().query(sql)
    end
    return true,{}
end


--开启小程序书籍任务
function M.open_app_book_task(msg_body)
    local isok ,key =  tools.checkData({"butler_id","id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    for i = 1, #msg_body.id_list, 1 do
        local res,sql,id;
        id = msg_body.id_list[i]
        sql = string.format("UPDATE  %s SET status = 0 ,  wait_status = 0 ,is_close_execution = 1  WHERE id = %d ",table_name,id)
        res = mysqldtaskbx.Singleton().query(sql)
    end
    return true,{}
end

--开启检测链接任务
function M.open_check_url_task(msg_body)
    local isok ,key =  tools.checkData({"butler_id","id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("UPDATE  %s SET status = 0 ,  wait_status = 1   WHERE id IN (%s) ",table_name,idString)
    skynet.error("sql:",sql)
    mysqldtaskbx.Singleton().query(sql)
    return true,{}
end

--设置任务权重
function M.set_task_weight(msg_body)
    local isok ,key =  tools.checkData({"butler_id","id_list","weight"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("UPDATE  %s SET weight = %d    WHERE id IN (%s) ",table_name,msg_body.weight,idString)
    skynet.error("sql:",sql)
    mysqldtaskbx.Singleton().query(sql)
    return true,{}
end

function  M.delete(msg_body)
    local isok ,key =  tools.checkData({"butler_id","id_list"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("DELETE FROM %s  WHERE id IN (%s) ",table_name,idString)
    skynet.error("sql:",sql)
    mysqldtaskbx.Singleton().query(sql)
    return true,{}
end

--搜索小程序书
--tg_platform_id 平台id
--product_id 书id
--product_name 书名
--main_id 主体ID
--dy_small_applet_app_id 小程序ID
function M.search_app_book_data(msg_body)
    local isok ,key =  tools.checkData({
    "error_type",
    "start_first_time",
    "end_first_time",
    "error_info",
    "status",
    "product_name",
    "product_id",
    "tg_platform_id",
    "main_id",
    "app_id",
    "page_size",
    "page_number","butler_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local table_name = "video_applet_product_"..msg_body.butler_id

    if msg_body.isOrigin == 1  then
        table_name = "video_applet_product"
    else
        if not M.check_table_exists(table_name) then
            return false,string.format("不存在  %s 这个数据.", table_name)
        end
    end

    local page_size = msg_body.page_size
    local page_number = msg_body.page_number
    local offset = (page_number - 1) * page_size

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

    local date_param = ""
    if msg_body.start_first_time~="" and msg_body.end_first_time~="" then
        date_param = " AND DATE(first_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_first_time / 1000) .. ")) AND DATE(first_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_first_time / 1000) .. ")) "
    end

    local error_info_param = ""
    if msg_body.error_info~="" then
        local searchTerm = string.gsub(msg_body.error_info, "%%", "\\%%")
        error_info_param = string.format("  AND error_info LIKE CONCAT('%%', '%s', '%%')  ",searchTerm)
    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 = '%s' ",msg_body.product_name)
    end
    local tg_platform_param = ""
    if msg_body.tg_platform_id~="" then
        tg_platform_param = "AND book_platform = "..msg_body.tg_platform_id.." "
    end
    local main_param = ""
    if msg_body.main_id~="" then
        main_param = "AND main_id = "..msg_body.main_id.." "
    end
    local app_param = ""
    if msg_body.app_id~="" then
        app_param = string.format("AND dy_small_applet_app_id = '%s' ",msg_body.app_id) 
    end

    local status_param = ""

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

    local param = error_type_param..date_param..error_info_param..product_param..product_name_param..tg_platform_param..main_param..app_param..status_param;


    local sql = "SELECT * FROM "..table_name.." WHERE 1=1 "..param.." ORDER BY id DESC "..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    local list = mysqldtaskbx.Singleton().query(sql)

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

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

    return true,list,total[1].total
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