jl_tg_config_back/service/backmgr/video_material.lua

--推广
local M = {}
local mysqldbx = require "mysqldbx"
local tools = require "tools"
local skynet = require "skynet"
local cjson = require "cjson"
local config = require "run_config"
local db
--设置推广状态
function M.set_tui_guang_status(msg_body)
    local isok ,key =  tools.checkData({"id_list","status"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("SELECT * FROM video_material WHERE id IN (%s)",idString)
    local isok,res;
    res = mysqldbx.query(sql)

    for i = 1, #res, 1 do
        local id =   res[i].id
        local material_id = res[i].material_id
        sql = string.format("UPDATE  video_material SET status = %d WHERE id =%d ",msg_body.status,id)
        mysqldbx.query(sql)
    end
    
    return true,{}
end

--设置审核状态
function M.set_artificial_status(msg_body)
    local isok ,key =  tools.checkData({"id_list","artificial_status"},msg_body)
    if not isok then
        return false,string.format("缺少字段： %s.", key)
    end

    local idString = table.concat(msg_body.id_list, ",")
    local sql = string.format("SELECT * FROM video_material WHERE id IN (%s)",idString)
    local isok,res;
    res = mysqldbx.query(sql)

    for i = 1, #res, 1 do
        local id =   res[i].id
        sql = string.format("UPDATE  video_material SET artificial_status = %d WHERE id =%d ",msg_body.artificial_status,id)
        mysqldbx.query(sql)
    end
    
    return true,{}
end

--搜索推广数据
--oce_material_id 推广素材id
--book_platform 平台id
--product_id 书id
--sync_status 状态
function M.search(msg_body)
    local isok ,key =  tools.checkData({"end_time_date",
    "start_time_date",
    "sync_status",
    "product_id",
    "tg_platform_id",
    "oce_material_id",
    "product_name",
    "material_id",
    "page_size",
    "page_number",
    "dy_id",
"d_z","comment","collect","forward","status","signature","dy_id_1","artificial_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 product_param = ""
    if msg_body.product_id~="" then
        product_param =  string.format(" AND product_id = %d ",tonumber(msg_body.product_id))
    end


    local product_name_param = ""
    if msg_body.product_name~="" then
        product_name_param = string.format("AND product_name = '%s' ",msg_body.product_name) 
    end

    local is_auto_param = ""
    if msg_body.dy_id~="" then
        if msg_body.dy_id~=0 then
            is_auto_param = " AND dy_id != 0"
        else
            is_auto_param = " AND dy_id = 0"
        end
    end

    local material_id_param = ""
    if msg_body.material_id~="" then
        material_id_param =  string.format(" AND id = %d ",tonumber(msg_body.material_id)) 
    end

    local oce_material_param = ""
    if msg_body.oce_material_id~="" then
        oce_material_param =string.format("AND oce_material_id =  %d ",tonumber(msg_body.oce_material_id))
    end
    local tg_platform_param = ""
    if msg_body.tg_platform_id~="" then
        tg_platform_param = " AND book_platform = "..msg_body.tg_platform_id.." "
    end

    local date_param = ""
    if msg_body.start_time_date~="" and msg_body.end_time_date~="" then
        date_param = " AND DATE(create_at) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_time_date / 1000) .. ")) AND DATE(create_at) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_time_date / 1000) .. "))"
    end

    local sync_status_param = ""

    if msg_body.sync_status~="" then
        sync_status_param = " AND sync_status = "..msg_body.sync_status.." "
    end


    local status_param = ""

    if msg_body.status~="" then
        status_param = " AND status = "..msg_body.status.." "
    end

    local artificial_status_param = ""

    if msg_body.artificial_status~="" then
        artificial_status_param = " AND artificial_status = "..msg_body.artificial_status.." "
    end

    local signature_param = ""
    if msg_body.signature~="" then
        signature_param = string.format("AND signature = '%s' ",msg_body.signature)
    end

    local dy_id_1_param = ""
    if msg_body.dy_id_1~="" then
        dy_id_1_param = " AND dy_id =  "..tonumber(msg_body.dy_id_1)
    end

    local d_z_param = ""
    if msg_body.d_z~="" then
        if(msg_body.d_z == 0) then
            d_z_param = " d_z_number ASC"
        else
            d_z_param = " d_z_number DESC"
        end
    end
   
    local comment_param = ""
    if msg_body.comment~="" then
        if(msg_body.comment == 0) then
            comment_param = " comment_number ASC"
        else
            comment_param = " comment_number DESC"
        end
    end

    local forward_param = ""
    if msg_body.forward~="" then
        if(msg_body.forward == 0) then
            forward_param = " forward_number ASC"
        else
            forward_param = " forward_number DESC"
        end
    end
    
    local collect_param = ""
    if msg_body.collect~="" then
        if(msg_body.collect == 0) then
            collect_param = " collect_number ASC"
        else
            collect_param = " collect_number DESC"
        end
    end
    


    -----
    local stat_cost_param = ""
    if msg_body.stat_cost~="" then
        if(msg_body.stat_cost == 0) then
            stat_cost_param = " stat_cost ASC"
        else
            stat_cost_param = " stat_cost DESC"
        end
    end
   
    local show_cnt_param = ""
    if msg_body.show_cnt~="" then
        if(msg_body.show_cnt == 0) then
            show_cnt_param = " comment_number ASC"
        else
            show_cnt_param = " comment_number DESC"
        end
    end

    local click_cnt_param = ""
    if msg_body.click_cnt~="" then
        if(msg_body.click_cnt == 0) then
            click_cnt_param = " click_cnt ASC"
        else
            click_cnt_param = " click_cnt DESC"
        end
    end
    
    local convert_cnt_param = ""
    if msg_body.convert_cnt~="" then
        if(msg_body.convert_cnt == 0) then
            convert_cnt_param = " convert_cnt ASC"
        else
            convert_cnt_param = " convert_cnt DESC"
        end
    end


    local cvr_param = ""
    if msg_body.cvr~="" then
        if(msg_body.cvr == 0) then
            cvr_param = " cvr ASC"
        else
            cvr_param = " cvr DESC"
        end
    end
    
    local ctr_param = ""
    if msg_body.ctr~="" then
        if(msg_body.ctr == 0) then
            ctr_param = " ctr ASC"
        else
            ctr_param = " ctr DESC"
        end
    end

    local new_param = stat_cost_param..show_cnt_param..click_cnt_param..convert_cnt_param..cvr_param..ctr_param
    -----
    local param = date_param..product_param..oce_material_param..status_param..tg_platform_param..sync_status_param..product_name_param..is_auto_param..material_id_param..signature_param..dy_id_1_param..artificial_status_param

    local up_down_param = d_z_param..comment_param..forward_param..collect_param..new_param

    if up_down_param ~= "" then
        up_down_param = " ORDER BY " ..up_down_param
    else
        up_down_param = " ORDER BY id DESC "
    end

    local sql = "SELECT * FROM video_material  WHERE  1=1 "..param..up_down_param..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
   
    local res = mysqldbx.query(sql)

    skynet.error("sql:",sql)

    sql =  "SELECT COUNT(*) AS total FROM video_material  WHERE  1=1 "..param.." ORDER BY id DESC "
    
    local total = mysqldbx.query(sql)

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

return M