jl_tg_config_back/service/backmgr/origin_data.lua

--原始数据
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"

function M.delete_origin_data_by_time(msg_body)
    local isok ,key =  tools.checkData({"start_create_time","end_create_time"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end
    local delete_time_param = " AND DATE(created_at) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_create_time / 1000) .. ")) AND DATE(created_at) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_create_time / 1000) .. "))"
end


function M.search_origin_data(msg_body)
    local isok ,key =  tools.checkData({"page_size","page_number","start_create_time","end_create_time","guajian_info","start_publish_time","end_publish_time","materialId"},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 conditions = {"1=1"}
    local params = {}

    -- 创建时间条件
    if msg_body.start_create_time ~= "" and msg_body.end_create_time ~= "" then
        table.insert(conditions, "DATE(create_time) BETWEEN DATE(FROM_UNIXTIME(?)) AND DATE(FROM_UNIXTIME(?))")
        table.insert(params, msg_body.start_create_time / 1000)
        table.insert(params, msg_body.end_create_time / 1000)
    end

    -- 发布时间条件
    if msg_body.start_publish_time ~= "" and msg_body.end_publish_time ~= "" then
        table.insert(conditions, "DATE(publish_time) BETWEEN DATE(FROM_UNIXTIME(?)) AND DATE(FROM_UNIXTIME(?))")
        table.insert(params, msg_body.start_publish_time / 1000)
        table.insert(params, msg_body.end_publish_time / 1000)
    end

    -- 挂件信息条件
    if msg_body.guajian_info ~= "" then
        table.insert(conditions, "guajian_link LIKE ?")
        table.insert(params, "%" .. msg_body.guajian_info .. "%")
    end

    -- 材料ID条件
    if msg_body.materialId ~= "" then
        table.insert(conditions, "materialId = ?")
        table.insert(params, msg_body.materialId)
    end

    -- 构建WHERE子句
    local where_clause = table.concat(conditions, " AND ")

    -- 使用子查询优化分页
    local sql = string.format([[
        SELECT * FROM (
            SELECT id FROM origin_data 
            WHERE %s 
            ORDER BY id DESC 
            LIMIT %d OFFSET %d
        ) AS tmp 
        JOIN origin_data USING(id)
    ]], where_clause, page_size, offset)

    -- 使用 COUNT(*) 优化总数查询
    local count_sql = string.format("SELECT COUNT(*) AS total FROM origin_data WHERE %s", where_clause)

    -- 执行查询
    local res = mysqldbx.query(sql, table.unpack(params))
    local total = mysqldbx.query(count_sql, table.unpack(params))

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

return M