jl_tg_config_back/common/dbproxy/tg_max_zhuanhua.lua

--最大转化
local M = {}
local db
local mysqldtaskbx = {}
local mysqldbx = require "mysqldbx"
require "skynet.manager"
local mysql = require "skynet.db.mysql"
local config = require "run_config"
local tools = require "tools"
local skynet = require "skynet"

--添加转化账户
function M.addZhuanHuaZhangHu(msg_body)
    local isok ,key =  tools.checkData({"advertiser_id","advertiser_name","status","is_deleted","budget"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local isok , find_data = M.findZhuangHuById({id=msg_body.advertiser_id})
    if find_data==nil then
        return false,string.format("advertiser_id 失败 ： %d.", msg_body.advertiser_id)
    end
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    msg_body.create_time = current_time
    local sql = string.format("INSERT INTO `ad_cbo_advertiser_id` (advertiser_id,advertiser_name, create_time, update_time,status,is_deleted,budget)  VALUES (%d,'%s','%s','%s',%d,%d,%d)",
    msg_body.advertiser_id,
    msg_body.advertiser_name,
    msg_body.create_time,
    msg_body.create_time,
    msg_body.status,msg_body.is_deleted,msg_body.budget)
    local res = mysqldtaskbx.Singleton().query(sql)
    skynet.error(sql)
    tools.dump(res)
    skynet.error(res)
    return true
end

--modify
function M.modifyZhuanHuaZhangHu(msg_body)
    local isok ,key =  tools.checkData({"advertiser_id","advertiser_name","status","is_deleted","budget"},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 `ad_cbo_advertiser_id` SET update_time ='%s', advertiser_id =%d ,advertiser_name ='%s' , status =%d , is_deleted =%d , budget =%d  WHERE id = %d ",
    msg_body.update_time,msg_body.advertiser_id,msg_body.advertiser_name,msg_body.status,msg_body.is_deleted,msg_body.budget,msg_body.id)
    skynet.error(sql)
    mysqldtaskbx.Singleton().query(sql)
    return true
end


--delete
function M.deleteZhuanHuaZhangHu(msg_body)
    local isok ,key =  tools.checkData({"id_list"},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
    for i = 1, #msg_body.id_list, 1 do
       local id = msg_body.id_list[i]
       local sql = string.format("UPDATE `ad_cbo_advertiser_id` SET update_time ='%s', is_deleted =%d WHERE id = %d ",
       msg_body.update_time,1,id)
       mysqldtaskbx.Singleton().query(sql)
    end
    return true
end

--获取转换列表
function M.getZhuanHuanList(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number","status"},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 status = msg_body.status --状态 1:正常 0:禁用 2:全部
    local status_cmd = "SELECT * FROM ad_cbo_advertiser_id WHERE is_deleted = 0 " ..string.format("and status = %d  ",status)
    if status == 2 then
        status_cmd = "SELECT * FROM ad_cbo_advertiser_id WHERE is_deleted = 0 "
    end
    local offset = (page_number - 1) * page_size
    local sql = status_cmd .. string.format(" ORDER BY id LIMIT %d OFFSET %d",page_size, offset)
    local isok,res;
    res = mysqldtaskbx.Singleton().query(sql)
    -- tools.dump(res)
    -- skynet.error(sql)
    if #res <= 0 then
        return true ,{}
    end
    return true, res
end

--获取转换总数
function M.getTotal()
    local sql = "SELECT COUNT(*) AS total FROM ad_cbo_advertiser_id WHERE is_deleted = 0"
    local res = mysqldtaskbx.Singleton().query(sql)
    return true,res[1]
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


--根据id获取账户
function M.findZhuangHuById(msg_body)
    local isok ,key =  tools.checkData({"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("select * from advertiser WHERE advertiser_id = '%s'  LIMIT 1",tostring(msg_body.id))
    local res = mysqldbx.query(sql)
    local info = nil
    if #res>0 then
        info = res
    end
    return true,info
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