jl_tg_config_back/common/dbproxy/tg_zhanghu.lua

--账户
local M = {}

local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local httpc = require "http.httpc"
--page_size  是你想要在每页中显示的记录数。
--page_number 页数
--获取所有账户
function M.getZhangHuList(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number"},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 sql = string.format("SELECT * FROM advertiser ORDER BY id LIMIT %d OFFSET %d", page_size, offset)
    local isok,res;
    res = mysqldbx.query(sql)
    if #res <= 0 then
        return true ,{}
    end
    return true, res
end

function M.getTotal()
    local sql = "SELECT COUNT(*) AS total FROM advertiser"
    local res = mysqldbx.query(sql)
    return true,res[1]
end


function M.advertiserListOfMain(msg_body)
    local isok ,key =  tools.checkData({"id_list","main_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local fail_list = {}
    for i = 1, #msg_body.id_list, 1 do
       local id = msg_body.id_list[i]
       local sql = string.format("SELECT * FROM advertiser where advertiser_id = '%s' and main_id = %d ", id,msg_body.main_id)
       local isok,res;
       res = mysqldbx.query(sql)
       if #res <1 then
            table.insert(fail_list,#fail_list+1,id)
       end
    end
    return true,fail_list
end


function M.setZhanghuOfMain(msg_body)
    local isok ,key =  tools.checkData({"id_list","main_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
    for i = 1, #msg_body.id_list, 1 do
        local id = msg_body.id_list[i]
        local sql = string.format("UPDATE `advertiser` SET main_id = %d  WHERE id = %d ",
        msg_body.main_id,id)
        skynet.error(sql)
        mysqldbx.query(sql)
    end
    return true,{}
end

function M.searchZhanghu(msg_body)
    local isok ,key =  tools.checkData({"advertiser_id"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local sql = string.format("SELECT * FROM advertiser  WHERE advertiser_id = '%s'  LIMIT 1 ", tostring(msg_body.advertiser_id))
    local res;
    res = mysqldbx.query(sql)
    local info = {}
    if #res>0 then
        info = res[1]
    end
    return true,info
end

function M.searchName(msg_body)
    local isok ,key =  tools.checkData({"content","page_size","page_number"},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 sql = string.format(" SELECT COUNT(*) AS total FROM advertiser  advertiser_name LIKE '%%%s%%' ",  msg_body.content)
    local total = mysqldbx.query(sql)

    sql = string.format("SELECT * FROM advertiser WHERE advertiser_name LIKE '%%%s%%'  ORDER BY id LIMIT %d OFFSET %d ", msg_body.content, page_size, offset)
    local res;
    res = mysqldbx.query(sql)
    return true,res,total[1].total
end

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

    for i = 1, #msg_body.id_list, 1 do
        local id = msg_body.id_list[i]
        local sql = string.format("UPDATE `advertiser` SET status = %d  WHERE id = %d ",
        msg_body.status,id)
        local res;
        res = mysqldbx.query(sql)
    end
    return true,{}
end
function M.mainFilter(msg_body)
    local isok ,key =  tools.checkData({"content","main_id","page_size","page_number"},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 main_id_param = ""
    if msg_body.main_id ~= "" then
        main_id_param = " AND main_id = "..msg_body.main_id.." "
    end

    local content_param = ""
    if msg_body.content ~= "" then
        content_param = string.format(" AND  (advertiser_name LIKE CONCAT( '%%%s%%')) OR (advertiser_id LIKE CONCAT('%%%s%%'))",msg_body.content,msg_body.content)
    end
    local sql = "SELECT COUNT(*) AS total FROM advertiser  WHERE 1=1 "..main_id_param..content_param
    local total = mysqldbx.query(sql)

    sql = "SELECT * FROM advertiser  WHERE 1=1 "..main_id_param..content_param..string.format(" ORDER BY id LIMIT %d OFFSET %d ",page_size, offset)
    local res;
    res = mysqldbx.query(sql)
    return true,res,total[1].total
end

function M.search(msg_body)
    local isok ,key =  tools.checkData({"advertiser_grant_balance_valid","stat_cost","advertiser_id_list","avatar_status","property_status","main_id","content","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 offset = (page_number - 1) * page_size
    

    local advertiser_grant_balance_valid_param = ""
    if msg_body.advertiser_grant_balance_valid~="" then
        if msg_body.advertiser_grant_balance_valid>0 then
            advertiser_grant_balance_valid_param = string.format(" advertiser_grant_balance_valid DESC ")
        else
            advertiser_grant_balance_valid_param = string.format(" advertiser_grant_balance_valid ASC ")
        end
    end

    local stat_cost_param = ""
    if msg_body.stat_cost~="" then
        if msg_body.stat_cost>0 then
            stat_cost_param = string.format(" stat_cost DESC ")
        else
            stat_cost_param = string.format(" stat_cost ASC ")
        end
    end

    local advertiser_id_list_param = ""
    if msg_body.advertiser_id_list~="" then
        local idString = table.concat(msg_body.advertiser_id_list, ",")
        advertiser_id_list_param = string.format(" AND advertiser_id IN (%s) ",idString)
    end


    local property_status_param = ""
    if msg_body.property_status~="" then
        property_status_param = "AND property_status = "..msg_body.property_status.." "
    end

    local avatar_status_param = ""
    if msg_body.avatar_status~="" then
        avatar_status_param = "AND avatar_status = "..msg_body.avatar_status.." "
    end

    local statuss_param = ""
    if msg_body.status~="" then
        statuss_param = "AND status = "..msg_body.status.." "
    end

    local main_id_param = ""
    if msg_body.main_id ~= "" then
        main_id_param = " AND main_id = "..msg_body.main_id.." "
    end

    local content_param = ""
    if msg_body.content ~= "" then
        content_param = string.format(" AND  (advertiser_name LIKE CONCAT( '%%%s%%')) OR (advertiser_id LIKE CONCAT('%%%s%%'))",msg_body.content,msg_body.content)
    end

    local order_param = advertiser_grant_balance_valid_param..stat_cost_param
    if order_param == "" then
        order_param = " id DESC "
    end
    local param = advertiser_id_list_param..avatar_status_param..property_status_param..statuss_param..main_id_param..content_param
    local sql = "SELECT COUNT(*) AS total FROM advertiser  WHERE 1=1 "..param
    local total = mysqldbx.query(sql)

    sql = "SELECT * FROM advertiser  WHERE 1=1 "..param..string.format(" ORDER BY %s LIMIT %d OFFSET %d ",order_param,page_size, offset)
    local res;
    skynet.error(sql)
    res = mysqldbx.query(sql)
    return true,res,total[1].total
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 temp = {}
    for i = 1, #msg_body.query_list, 1 do
        local zhang_hu_id = msg_body.query_list[i].zhang_hu_id..""
        local key = msg_body.query_list[i].id
        local sql = string.format("SELECT * FROM advertiser WHERE advertiser_id = '%s' LIMIT 1",zhang_hu_id)
        local res = mysqldbx.query(sql)
        if #res >0 then
            table.insert(temp,#temp+1,res[1])
        end
    end
    return true,temp
end

function M.update_zhanghu(msg_body)
    httpc.dns() -- set dns server
    httpc.timeout = 100 -- set timeout 1 second
    local status, body = httpc.get('https://clipvideoup.s6kuwan.com/adoce/advertiser/sync_center_advertiser','',{})
    if status == 200 then
        return true,{}
    else
        return false,{}
    end
    return true,{}
end
function searchZhanghuName(name,id)
    local sql = string.format("SELECT * FROM advertiser WHERE advertiser_name LIKE '%%%s%%'", name)
    local res;
    res = mysqldbx.query(sql)
    local id_list = {}
    for i = 1, #res, 1 do
        table.insert(id_list,i,res[i].id)
    end
    if #id_list> 0 then
        M.setZhanghuOfMain({id_list=id_list,main_id=id})
    end
end
function M.init()
    local sql = string.format("select * from `tg_main` ")
    local isok,res;
    res = mysqldbx.query(sql)
    if #res > 0 then
        tools.dump(res)
        for i = 1, #res, 1 do
            local name = res[i].main_name
            searchZhanghuName(name,res[i].id)
        end
    end
    return true,{}
end
return M