dj_back/common/dbproxy/tg_main.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 mysqldtaskbx = {}
local db

function M.getOpenMainList()
    local sql = string.format("select * from `tg_main` where running_status = 1 ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true , {}
    end
    return true, res
end

function M.getCloseMainList()
    local sql = string.format("select * from `tg_main` where running_status = 0 ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true , {}
    end
    return true, res
end


function M.getOpenCreateLinkMainList()
    local sql = string.format("select * from `tg_main` where is_open_create_link = 1 ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true , {}
    end
    return true, res
end

function M.getCloseCreateLinkMainList()
    local sql = string.format("select * from `tg_main` where is_open_create_link = 0 ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true , {}
    end
    return true, res
end
--page_size  是你想要在每页中显示的记录数。
--page_number 页数
--获取所有主体
function M.getMainList()
    local sql = string.format("select * from `tg_main` ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true , {}
    end
    return true, res
end

function M.search(msg_body)

    local isok ,key =  tools.checkData({"tg_day","id","ads_operation_status","statistics_platform_id","converted_time_duration","cbo_status","project_type","oce_product_id",
    "oce_product_platform_id",
    "delivery_mode","material_platform_id","wx_miniapp_create_status","template_bid","cpa_bid","page_size","page_number","main_name","running_status","custom_budget","pay_type","tg_platform_id","butler_id","tg_app_index","is_open_create_link","is_delete"},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 tg_day_param = ""
    if msg_body.tg_day~="" then
        tg_day_param =  " AND tg_day = "..msg_body.tg_day
    end

    local id_param = ""
    if msg_body.id~="" then
        id_param =  " AND id = "..msg_body.id
    end

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

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

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

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

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

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

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

    local oce_product_id_param = ""
    if msg_body.oce_product_id~="" and msg_body.oce_product_id~=nil then
        oce_product_id_param = string.format(" AND oce_product_id =  '%s' ",msg_body.oce_product_id) 
    end

    local oce_product_platform_id_param = ""
    if msg_body.oce_product_platform_id~="" and msg_body.oce_oce_product_platform_idproduct_id~=nil then
        oce_product_platform_id_param = string.format(" AND oce_product_platform_id =  '%s' ",msg_body.oce_product_platform_id) 
    end

    local delivery_mode_param = ""
    if msg_body.delivery_mode~="" and msg_body.delivery_mode~=nil then
        delivery_mode_param = string.format(" AND delivery_mode =  '%s' ",msg_body.delivery_mode) 
    end

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

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

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

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

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

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

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

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

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


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

    local custom_budget_param = ""
    if msg_body.custom_budget~="" then
        custom_budget_param = string.format(" AND custom_budget LIKE '%%%d%%' ", msg_body.custom_budget)
    end

    local cpa_bid_param = ""
    if msg_body.cpa_bid~="" then
        cpa_bid_param = string.format(" AND cpa_bid LIKE '%%%d%%' ", msg_body.cpa_bid)
    end

    local template_bid_param = ""
    if msg_body.template_bid~="" then
        template_bid_param = string.format(" AND template_bid LIKE '%%%d%%' ", msg_body.template_bid)
    end

    -- local genre_param = ""
    -- if msg_body.genre~="" then
    --     genre_param =  string.format(" AND genre = %d ",msg_body.genre) 
    -- end

    local param = tg_day_param..id_param..ads_operation_status_param..statistics_platform_id_param..converted_time_duration_param..infra_status_param..cbo_script_status_param..cbo_status_param..project_type_param..oce_product_platform_id_param..oce_product_id_param..delivery_mode_param..main_name_param..tg_platform_param..running_status_param..pay_type_param..butler_id_param..tg_app_index_param..is_open_create_link_param..is_delete_param..custom_budget_param..cpa_bid_param..template_bid_param..wx_miniapp_create_status_param..material_platform_id_param;
    local sql = "SELECT * FROM tg_main where 1=1 "..param..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    local res = mysqldbx.query(sql)
    skynet.error("sql:",sql)
    sql =  "SELECT  COUNT(*) AS total  FROM tg_main where 1=1 "..param

    local total = mysqldbx.query(sql)

    return true,res,total[1].total

end
-- `main_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主体名称',
-- `app_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '小程序ID',
-- `running_status` tinyint(1) DEFAULT '1' COMMENT '运行状态 0-停止 1-运行中',
-- `ad_quantity` int NOT NULL DEFAULT '40' COMMENT '广告数量',
-- `cpa_bid` bigint NOT NULL DEFAULT '1750' COMMENT 'CPA出价(分), 默认17.5元',
-- `bid_type` tinyint(1) DEFAULT '1' COMMENT '出价方式 0-常规 1-最大化 2 两种存储',
-- `bid_ratio` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '1' COMMENT '出价方式比例(如 1:2)',
-- `budget` bigint NOT NULL DEFAULT '9000000' COMMENT '预算金额(分), 默认9万元',
-- `tg_platform_id` int NOT NULL DEFAULT '0' COMMENT '平台',
-- `daily_new_limit` int DEFAULT '0' COMMENT '今天新上数量上限',

function M.addMain(msg_body)
    local isok ,key =  tools.checkData({"valid_timer_like_num","tg_day","ads_operation_status","statistics_platform_id","hide_if_converted","converted_time_duration","cbo_script_time","cbo_script_status","cbo_status","project_type","tf_status","tf_start_time","stat_cost","oce_product_id","oce_product_platform_id","material_platform_id","strategy_config","delivery_mode","price","pack_strategy_status","start_chapter","advertiser_id","wx_miniapp_create_status","ad_act","template_bid","zh_id","roi_goal","ql_bid","infra_status","pay_type","qm_id","ldy_template_site_id","yw_id","parent_platform_id","platform_shared_id","distributor_id","is_delete","ad_convert_type","aweme_id","tg_app_index","butler_id","mini_program_platform_id","landing_page","valid_timer_time","valid_timer_number","channelId","no_bid_budget","max_ad_quantity","tg_link_config","tg_platform_id","app_id","main_name","running_status","ad_quantity","cpa_bid","bid_type","bid_ratio","custom_budget","daily_new_limit","valid_time","group_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    msg_body.create_time = current_time
    local sql = string.format("INSERT INTO `tg_main` (valid_timer_like_num,tg_day,ads_operation_status,statistics_platform_id,hide_if_converted,converted_time_duration,cbo_script_time,cbo_script_status,cbo_status,project_type,tf_status,tf_start_time,stat_cost,oce_product_id,oce_product_platform_id,material_platform_id,strategy_config,delivery_mode,price,pack_strategy_status,start_chapter,advertiser_id,wx_miniapp_create_status,genre,ad_act,template_bid,zh_id,roi_goal,ql_bid,infra_status,pay_type,qm_id,ldy_template_site_id,yw_id,parent_platform_id,platform_shared_id,distributor_id,is_delete,ad_convert_type,aweme_id,tg_app_index,butler_id,mini_program_platform_id,landing_page,valid_timer_time,valid_timer_number,channelId,no_bid_budget,max_ad_quantity,tg_link_config,tg_platform_id,app_id,main_name,running_status,ad_quantity,cpa_bid,bid_type,bid_ratio,custom_budget,daily_new_limit,valid_time,group_id)  VALUES (%d,%d,'%s',%d, '%s','%s','%s',%d,%d,%d,%d,'%s',%d,'%s','%s',%d,'%s','%s',%d,%d,%d,%s,%d,%d,%d,%d,'%s',%d,%d,%d,%d,'%s','%s','%s',%d,%d,'%s',%d,'%s','%s', %d, %d, %d,'%s', %d, '%s', '%s',%d, %d, '%s', %d, '%s', '%s', %d, %d, %d,%d, '%s', %d, %d, '%s', '%s')",
    msg_body.valid_timer_like_num,
    msg_body.tg_day,
    msg_body.ads_operation_status,
    msg_body.statistics_platform_id,
    msg_body.hide_if_converted,
    msg_body.converted_time_duration,
    msg_body.cbo_script_time,
    msg_body.cbo_script_status,
    msg_body.cbo_status,
    msg_body.project_type,
    msg_body.tf_status,
    msg_body.tf_start_time,
    msg_body.stat_cost,
    msg_body.oce_product_id,
    msg_body.oce_product_platform_id,
    msg_body.material_platform_id,
    msg_body.strategy_config,
    msg_body.delivery_mode,
    msg_body.price,
    msg_body.pack_strategy_status,
    msg_body.start_chapter,
    msg_body.advertiser_id,
    msg_body.wx_miniapp_create_status,
    0,
    msg_body.ad_act,
    msg_body.template_bid,
    msg_body.zh_id,
    msg_body.roi_goal,
    msg_body.ql_bid,
    msg_body.infra_status,
    msg_body.pay_type,
    msg_body.qm_id,
    msg_body.ldy_template_site_id,
    msg_body.yw_id,
    msg_body.parent_platform_id,
    msg_body.platform_shared_id,
    msg_body.distributor_id,
    msg_body.is_delete,
    msg_body.ad_convert_type,
    msg_body.aweme_id,
    msg_body.tg_app_index,
    msg_body.butler_id,
    msg_body.mini_program_platform_id,
    msg_body.landing_page,
    msg_body.valid_timer_time,
    msg_body.valid_timer_number,
    msg_body.channelId,
    msg_body.no_bid_budget,
    msg_body.max_ad_quantity,
    cjson.encode(msg_body.tg_link_config),
    msg_body.tg_platform_id,
    msg_body.app_id,
    msg_body.main_name,
    msg_body.running_status,
    msg_body.ad_quantity,
    msg_body.cpa_bid,
    msg_body.bid_type,
    msg_body.bid_ratio,
    msg_body.custom_budget,
    msg_body.daily_new_limit,
    msg_body.valid_time,
    msg_body.group_id)
    skynet.error(sql)
    tools.dump(mysqldbx.query(sql))
    skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="updateMainConfig"}))
    -- pushAddMainMsg(msg_body)
    return true
end
--此主体将书库的书都发布到小程序书籍
function M.syncMain(msg_body)
    local isok ,key =  tools.checkData({"id","min_stat_cost","max_stat_cost"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("select * from `tg_main` where id = %d LIMIT 1 ",msg_body.id)
    local res = mysqldbx.query(sql)
    res[1].min_stat_cost = msg_body.min_stat_cost
    res[1].max_stat_cost = msg_body.max_stat_cost
    syncMainAppletProduct(res[1])
    return true,{}
end

function syncMainAppletProduct(main_info)
    -- local tg_platform_id = main_info.tg_platform_id
    -- local app_id = main_info.app_id
    -- local sql = string.format("SELECT * FROM video_product where book_platform = %d ",tg_platform_id)
    -- local video_product_list = mysqldtaskbx.Singleton().query(sql)


    -- function isHaveAppletProdut(product_id)
    --     local s = string.format("SELECT * FROM video_applet_product where dy_small_applet_app_id = '%s' and product_id = '%s' and main_id = %d and book_platform = %d LIMIT 1",app_id,product_id,main_info.id,tg_platform_id)
    --     local r = mysqldtaskbx.Singleton().query(sql)
    --     return #r>0
    -- end

    -- local need_create_link_list = {}
    -- local verify_list = {}
    -- for i = 1, #video_product_list, 1 do
    --     local product = video_product_list[i]
    --     if not isHaveAppletProdut(product.product_id) and not verify_list[product.product_id] then
    --         verify_list[product.product_id] = true
    --         table.insert(need_create_link_list,#need_create_link_list+1,{product_name=product.product_name,product_id=product.product_id,book_platform=product.book_platform})
    --     end
    -- end
    skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="syncMain",data={main_info=main_info}}))
end

function pushAddMainMsg(msg_body)
    local sql = string.format("select * from `tg_main` where main_name = '%s' and app_id = '%s'  LIMIT 1 ",msg_body.main_name,msg_body.app_id)
    local isok,res;
    res = mysqldbx.query(sql)
    skynet.error("sql:",sql)
    tools.dump(res)
    if #res > 0 then
        local main_info =  res[1]
        skynet.fork(function ()
        
            -- local getMainInfoByAppid = function(app_id)
            --     local temp_list = {}
            --     for i = 1, #main_list, 1 do
            --         local main_item = main_list[i]
            --         if (main_item.app_id==app_id) then
            --             table.insert(temp_list,#temp_list+1,main_item)
            --         end
            --     end
            --     return temp_list
            -- end
            --获取书籍小程序列表
            sql = string.format("SELECT * FROM video_applet_product ")
    
            res = mysqldtaskbx.Singleton().query(sql)
    
            local video_applet_product_list = {}
    
            if #res>0 then
                for i = 1, #res, 1 do
                    table.insert(video_applet_product_list,i,res[i])
                end
            end
    
            local need_create_link_list = {}
    
            -- local isCreateLinkByMainIdAndProductId = function(main_id,product_id)
            --     local isCreate = false
            --     for i = 1, #video_applet_product_list, 1 do
            --         local item = video_applet_product_list[i]
            --         if (item.product_id==product_id and item.main_id == main_id) then
            --             isCreate = true
            --             break
            --         end
            --     end
            --     return isCreate
            -- end
            --根据小程序id 筛选出所有需要创建的书籍
            local video_applet_product_of_main_list = {}
            local verify_list = {}
            for i = 1, #video_applet_product_list, 1 do
                local applet_item = video_applet_product_list[i]
                if verify_list[applet_item.product_id]==nil then
                    if applet_item.status == 1 and applet_item.dy_small_applet_app_id == main_info.app_id then
                        verify_list[applet_item.product_id] = true
                        table.insert(video_applet_product_of_main_list,#video_applet_product_of_main_list+1,{product_name=applet_item.product_name,product_id=applet_item.product_id,book_platform=applet_item.book_platform,dy_small_applet_app_id=applet_item.dy_small_applet_app_id})
                    end
                end
            end
            skynet.error("找到所有小程序！",#video_applet_product_of_main_list)
            skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="addMain",data={list=video_applet_product_of_main_list,main_info=main_info}}))
        end)
    end
   
end

local function combine_query_and_params(query, params)
    -- 将 params 中的值替换到 query 中的占位符 ?
    local combined_query = query:gsub("?", function()
        local param = table.remove(params, 1) -- 从 params 中取出第一个参数
        if type(param) == "string" then
            return "'" .. param .. "'" -- 如果是字符串，用单引号包裹
        else
            return tostring(param) -- 如果是数字或其他类型，直接转换为字符串
        end
    end)
    return combined_query
end
function M.modifyMain(msg_body)
    local isok ,key =  tools.checkData({"valid_timer_like_num","tg_day","ads_operation_status" ,"statistics_platform_id","hide_if_converted","converted_time_duration","cbo_script_time","cbo_script_status","cbo_status","project_type","tf_status","tf_start_time","stat_cost","oce_product_id","oce_product_platform_id","material_platform_id","strategy_config","delivery_mode","price","pack_strategy_status","start_chapter","advertiser_id","wx_miniapp_create_status","ad_act","template_bid","zh_id","roi_goal","ql_bid","infra_status","pay_type","pay_type","qm_id","ldy_template_site_id","yw_id","parent_platform_id","platform_shared_id","distributor_id","is_delete","ad_convert_type","aweme_id","tg_app_index","butler_id","mini_program_platform_id","landing_page","valid_timer_time","valid_timer_number","channelId","no_bid_budget","max_ad_quantity","tg_link_config","id","tg_platform_id","app_id","main_name","running_status","ad_quantity","cpa_bid","bid_type","bid_ratio","custom_budget","daily_new_limit","valid_time","group_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    msg_body.update_time = current_time


    local query = [[
    UPDATE `tg_main` SET
        valid_timer_like_num = ?,
        tg_day = ?,
        ads_operation_status = ?,
        statistics_platform_id = ?,
        hide_if_converted = ?,
        converted_time_duration = ?,
        cbo_script_time = ?,
        cbo_script_status = ?,
        cbo_status = ?,
        project_type = ?,
        tf_status = ?,
        tf_start_time = ?,
        stat_cost = ?,
        oce_product_id = ?,
        oce_product_platform_id = ?,
        material_platform_id = ?,
        strategy_config = ?,
        delivery_mode = ?,
        price = ?,
        pack_strategy_status = ?,
        start_chapter = ?,
        advertiser_id = ?,
        wx_miniapp_create_status = ?,
        genre = ?,
        ad_act = ?,
        template_bid = ?,
        zh_id = ?,
        roi_goal = ?,
        ql_bid = ?,
        infra_status = ?,
        pay_type = ?,
        qm_id = ?,
        ldy_template_site_id = ?,
        yw_id = ?,
        parent_platform_id = ?,
        platform_shared_id = ?,
        distributor_id = ?,
        is_delete = ?,
        ad_convert_type = ?,
        aweme_id = ?,
        tg_app_index = ?,
        butler_id = ?,
        mini_program_platform_id = ?,
        landing_page = ?,
        valid_timer_time = ?,
        valid_timer_number = ?,
        channelId = ?,
        no_bid_budget = ?,
        max_ad_quantity = ?,
        tg_platform_id = ?,
        app_id = ?,
        main_name = ?,
        update_time = ?,
        running_status = ?,
        ad_quantity = ?,
        cpa_bid = ?,
        bid_type = ?,
        bid_ratio = ?,
        custom_budget = ?,
        daily_new_limit = ?,
        valid_time = ?,
        tg_link_config = ?,
        group_id = ?
    WHERE id = ?
]]

local params = {
    msg_body.valid_timer_like_num,
    msg_body.tg_day,
    msg_body.ads_operation_status,
    msg_body.statistics_platform_id,
    msg_body.hide_if_converted,
    msg_body.converted_time_duration,
    msg_body.cbo_script_time,
    msg_body.cbo_script_status,
    msg_body.cbo_status,
    msg_body.project_type,
    msg_body.tf_status,
    msg_body.tf_start_time,
    msg_body.stat_cost,
    msg_body.oce_product_id,
    msg_body.oce_product_platform_id,
    msg_body.material_platform_id,
    msg_body.strategy_config,
    msg_body.delivery_mode,
    msg_body.price,
    msg_body.pack_strategy_status,
    msg_body.start_chapter,
    msg_body.advertiser_id,
    msg_body.wx_miniapp_create_status,
    0,
    msg_body.ad_act,
    msg_body.template_bid,
    msg_body.zh_id,
    msg_body.roi_goal,
    msg_body.ql_bid,
    msg_body.infra_status,
    msg_body.pay_type,
    msg_body.qm_id,
    msg_body.ldy_template_site_id,
    msg_body.yw_id,
    msg_body.parent_platform_id,
    msg_body.platform_shared_id,
    msg_body.distributor_id,
    msg_body.is_delete,
    msg_body.ad_convert_type,
    msg_body.aweme_id,
    msg_body.tg_app_index,
    msg_body.butler_id,
    msg_body.mini_program_platform_id,
    msg_body.landing_page,
    tonumber(msg_body.valid_timer_time),
    tonumber(msg_body.valid_timer_number),
    msg_body.channelId,
    msg_body.no_bid_budget,
    msg_body.max_ad_quantity,
    msg_body.tg_platform_id,
    msg_body.app_id,
    msg_body.main_name,
    msg_body.update_time,
    msg_body.running_status,
    msg_body.ad_quantity,
    msg_body.cpa_bid,
    msg_body.bid_type,
    msg_body.bid_ratio,
    msg_body.custom_budget,
    tonumber(msg_body.daily_new_limit),
    tonumber(msg_body.valid_time),
    cjson.encode(msg_body.tg_link_config),
    msg_body.group_id,
    msg_body.id
}

    local sql = combine_query_and_params(query,params)
    skynet.error("sql:",sql)
    -- local sql = string.format("UPDATE  `tg_main` SET strategy_config = '%s' , delivery_mode = '%s' , price = %d , pack_strategy_status = %d ,  start_chapter = %d , advertiser_id = '%s' ,wx_miniapp_create_status = %d , genre = %d , ad_act = %d ,template_bid = %d , zh_id = '%s', roi_goal = %d , ql_bid = %d, infra_status = %d , pay_type = %d ,qm_id = '%s' , ldy_template_site_id = '%s' , yw_id = '%s' , parent_platform_id = %d , platform_shared_id = %d ,distributor_id = '%s' , is_delete = %d , ad_convert_type = '%s', aweme_id = '%s' ,tg_app_index = %d , butler_id = %d , mini_program_platform_id = %d , landing_page = '%s' , valid_timer_time = %d , valid_timer_number = %d ,channelId = '%s' , no_bid_budget = %d , max_ad_quantity = %d ,tg_platform_id = %d , app_id = '%s' ,main_name = '%s' , update_time = '%s' , running_status = %d ,ad_quantity = %d ,cpa_bid = %d ,bid_type = %d ,bid_ratio = '%s' ,custom_budget = %d ,daily_new_limit = %d ,valid_time = %d ,tg_link_config = '%s' ,group_id = '%s' ,advertiser_id = '%s' ,wx_miniapp_create_status = %d , genre = %d , ad_act = %d ,template_bid = %d , zh_id = '%s', roi_goal = %d , ql_bid = %d, infra_status = %d , pay_type = %d ,qm_id = '%s' , ldy_template_site_id = '%s' , yw_id = '%s' , parent_platform_id = %d , platform_shared_id = %d ,distributor_id = '%s' , is_delete = %d , ad_convert_type = '%s', aweme_id = '%s' ,tg_app_index = %d , butler_id = %d , mini_program_platform_id = %d , landing_page = '%s' , valid_timer_time = %d , valid_timer_number = %d ,channelId = '%s' , no_bid_budget = %d , max_ad_quantity = %d ,tg_platform_id = %d , app_id = '%s' ,main_name = '%s' , update_time = '%s' , running_status = %d ,ad_quantity = %d ,cpa_bid = %d ,bid_type = %d ,bid_ratio = '%s' ,custom_budget = %d ,daily_new_limit = %d ,valid_time = %d ,tg_link_config = '%s' ,group_id = '%s' WHERE id = %d ",
    -- msg_body.strategy_config,msg_body.delivery_mode,msg_body.price,msg_body.pack_strategy_status,msg_body.start_chapter,msg_body.advertiser_id,msg_body.wx_miniapp_create_status,msg_body.genre,msg_body.ad_act,msg_body.template_bid,msg_body.zh_id,msg_body.roi_goal,msg_body.ql_bid,msg_body.infra_status,msg_body.pay_type,msg_body.qm_id,msg_body.ldy_template_site_id,msg_body.yw_id,msg_body.parent_platform_id,msg_body.platform_shared_id,msg_body.distributor_id,msg_body.is_delete,msg_body.ad_convert_type,msg_body.aweme_id,msg_body.tg_app_index,msg_body.butler_id,msg_body.mini_program_platform_id,msg_body.landing_page,tonumber(msg_body.valid_timer_time),tonumber(msg_body.valid_timer_number),msg_body.channelId,msg_body.no_bid_budget,msg_body.max_ad_quantity,msg_body.tg_platform_id,msg_body.app_id,msg_body.main_name,msg_body.update_time,msg_body.running_status,msg_body.ad_quantity,msg_body.cpa_bid,msg_body.bid_type,msg_body.bid_ratio,msg_body.custom_budget,
    -- tonumber(msg_body.daily_new_limit),tonumber(msg_body.valid_time),cjson.encode(msg_body.tg_link_config),msg_body.group_id,msg_body.id)
    tools.dump(mysqldbx.query(sql))
    skynet.send("backmgr","lua","on_recv",nil,"ws_push_msg",cjson.encode({cmd="updateMainConfig"}))
    return true
end

--配置主体推广链接
function M.tgLinkConfig(msg_body)
    local isok ,key =  tools.checkData({"id","is_open","huichuan_id","chongzhi_id","kadian_id","f_chongzhi_id","site_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("select tg_link_config from `tg_main` WHERE id = %d ",msg_body.id)
    local res = mysqldbx.query(sql)
    sql = string.format("UPDATE `tg_main` SET  tg_link_config = '%s'  WHERE id = %d ",
    cjson.encode(msg_body),msg_body.id)
    mysqldbx.query(sql)
    return true, {}
end

--设置创建链接状态
function M.set_open_create_link_status(msg_body)
    local isok ,key =  tools.checkData({"id","is_open_create_link"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("UPDATE `tg_main` SET  is_open_create_link = %d  WHERE id = %d ",
    msg_body.is_open_create_link,msg_body.id)
    mysqldbx.query(sql)
    return true, {}
end

--获取主体下的运行总数
function M.get_main_ad_run_number(msg_body)
    local isok ,key =  tools.checkData({"main_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql ="SELECT * FROM advertiser WHERE main_id = " .. msg_body.main_id;
    local res = mysqldbx.query(sql)
    local all_run_number = 0
    for i = 1, #res, 1 do
        local run_number = res[i].ad_run_number
        all_run_number = all_run_number + run_number
    end
    local nums = #res
    sql = string.format("select * from `tg_main` where id = %d LIMIT 1 ",msg_body.main_id)
    res = mysqldbx.query(sql)
    local max = nums * res[1].max_ad_quantity
    return true,{all_run_number=all_run_number,max=max}
end


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

    local idString = table.concat(msg_body.query_list, ",")
    local sql = string.format("SELECT * FROM tg_main WHERE id IN (%s)",idString)
    local res = mysqldbx.query(sql)
    return true,res
end


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

    local idString = table.concat(msg_body.query_list, ",")
    local sql = string.format("SELECT * FROM tg_main WHERE tg_app_index IN (%s)",idString)
    local res = mysqldbx.query(sql)
    return true,res
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
        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