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
--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
-- `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({"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` (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,%d,'%s',%d,%d,'%s',%d,'%s','%s',%d,%d,%d,%d,'%s',%d,%d,%d,'%s')",
    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)
    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={list=need_create_link_list,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({"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 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.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,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,
    msg_body.daily_new_limit,msg_body.valid_time,cjson.encode(msg_body.tg_link_config),msg_body.group_id,msg_body.id)
    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"},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.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 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