jl_tg_config_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({"oce_product_id","oce_product_platform_id","delivery_mode","material_platform_id","wx_miniapp_create_status","genre","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 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 = 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..genre_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)

    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({"hy_accountId","oce_product_id","oce_product_platform_id","material_platform_id","delivery_mode","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"},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` (hy_accountId,oce_product_id,oce_product_platform_id,material_platform_id,delivery_mode,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 ('%s','%s','%s',%d,'%s','%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.hy_accountId,
    msg_body.oce_product_id,
    msg_body.oce_product_platform_id,
    msg_body.material_platform_id,
    msg_body.delivery_mode,
    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,
    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"},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)
    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
function M.modifyMain(msg_body)
    local isok ,key =  tools.checkData({"hy_accountId","oce_product_id","oce_product_platform_id","material_platform_id","delivery_mode","advertiser_id","wx_miniapp_create_status","genre","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 sql = string.format("UPDATE  `tg_main` SET hy_accountId = '%s' , oce_product_id = '%s' , oce_product_platform_id = '%s' , material_platform_id = %d , delivery_mode = '%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.hy_accountId,msg_body.oce_product_id,msg_body.oce_product_platform_id,msg_body.material_platform_id,msg_body.delivery_mode,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