dj_back/service/backmgr/log.lua

--日志
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"


function M.add_column( msg_body)
    local isok ,key =  tools.checkData({"column_name","column_type","default_value"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local default_param 
    if type(msg_body.default_value) == "string" then
        default_param =  string.format(" DEFAULT '%s' ",msg_body.default_value)
    elseif type(msg_body.default_value) == "number" then
        default_param =  string.format(" DEFAULT %d ",msg_body.default_value)
    end
    local sql = string.format("ALTER TABLE `%s` ADD COLUMN `%s` %s %s", 
        'log', msg_body.column_name, msg_body.column_type,default_param)
    skynet.error("sql:",sql)
    mysqldbx.query(sql)
    return true, {}
end

function M.add_log(msg_body)
    local isok ,key =  tools.checkData({"opt_type","user_id","content"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    local sql 
    sql = string.format("INSERT INTO `log` (opt_type,user_id,content,create_day)  VALUES (%d,%d,'%s','%s')",msg_body.opt_type,msg_body.user_id,cjson.encode(msg_body.content),current_time)
    skynet.error("sql:",sql)
    tools.dump(mysqldbx.query(sql)) 
    return true, {}
end


function M.diy_add_log(msg_body)
    local isok ,key =  tools.checkData({"data"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    local table_name = 'log'
    local data = msg_body.data
    local fields, values_str = {}, {}
    -- 改用 table.index 判断替代 contains
    local sql_functions = {
        ["CURRENT_TIMESTAMP"] = true,
        ["NOW()"] = true,
        ["UUID()"] = true
    }
    table.insert(fields, "`" .. "create_day" .. "`")
    table.insert(values_str,  "'" .. current_time .. "'" )
    for field, value in pairs(data) do
        -- 过滤字段名中的非法字符
        field = field:gsub("[%s`'\"]", "")
        table.insert(fields, "`" .. field .. "`")

        -- 检查是否为 SQL 函数
        if sql_functions[value] then
            table.insert(values_str, value)
        else
            -- 处理普通值
            if type(value) == "number" then
                table.insert(values_str, tostring(value))
            elseif type(value) == "string" then
                table.insert(values_str, "'" .. value:gsub("'", "''") .. "'")  -- 转义单引号
            elseif value == nil then
                table.insert(values_str, "NULL")
            elseif type(value) == "table" then
                table.insert(values_str, "'"..cjson.encode(value).."'")
            else
                error("不支持的数据类型: " .. type(value))
            end
        end
    end

    local sql = "INSERT INTO `" .. table_name .. "` (" .. table.concat(fields, ", ") .. ") " ..
                "VALUES (" .. table.concat(values_str, ", ") .. ")"
    skynet.error("sql:",sql)
    mysqldbx.query(sql)
    return true, {}

end


function M.search(msg_body)
    local isok ,key =  tools.checkData({"data","opt_type","user_id","page_size", "page_number","start_create_day","end_create_day"},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 opt_type_param = ""
    if msg_body.opt_type~="" then
        opt_type_param = string.format(" AND opt_type = %d ",msg_body.opt_type)
    end

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

    local create_day_param = ""
    if msg_body.start_create_day~="" and msg_body.end_create_day~="" then
        create_day_param = " AND DATE(create_day) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_create_day / 1000) .. ")) AND DATE(create_day) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_create_day / 1000) .. ")) "
    end


    local diy_param = ""
    if msg_body.data~="" then
        local data = msg_body.data
        local fields, values_str = {}, {}
        for field, value in pairs(data) do
            -- 过滤字段名中的非法字符
            field = field:gsub("[%s`'\"]", "")
            table.insert(fields, "`" .. field .. "`")
            local cur_param = ""
            -- 处理普通值
            if type(value) == "number" then
                -- table.insert(values_str, tostring(value))
                cur_param = " AND ".."`" .. field .. "`".." = "..value
            elseif type(value) == "string" then
                cur_param = " AND ".."`" .. field .. "`".." = ".."'" .. value:gsub("'", "''") .. "'"
                -- table.insert(values_str, "'" .. value:gsub("'", "''") .. "'")  -- 转义单引号
            else
                error("不支持的数据类型: " .. type(value))
            end

            diy_param = diy_param..cur_param
        end
     
    end
 

    local param = diy_param..opt_type_param..user_id_param..create_day_param
    
    local sql = "SELECT * FROM log WHERE 1=1 "..param.."ORDER BY id DESC"..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
    
    skynet.error("sql:",sql)
    -- 执行查询
    local res = mysqldbx.query(sql)
    -- 使用 COUNT(*) 优化总数查询
    sql =  "SELECT  COUNT(*) AS total  FROM log WHERE 1=1 "..param

    local total = mysqldbx.query(sql)

    return true, res, total[1].total
end

return M