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","user_id"},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)
    local current_time = os.date("%Y-%m-%d %H:%M:%S")
    for i = 1, #res, 1 do
        local id =   res[i].id
        sql = string.format("UPDATE  video_material SET opt_time = '%s' ,artificial_status = %d , exe_review_person_id = %d WHERE id =%d ",current_time,msg_body.artificial_status,msg_body.user_id,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({
    "material_platform",
    "bl_status",
    "start_publish_time",
    "end_publish_time",
    "start_yun_fabu_time",
    "end_yun_fabu_time",
    "start_opt_time",
    "end_opt_time",
    "exe_review_person_id","min_book_word","max_book_word","genre","end_time_date",
    "start_time_date",
    "sync_status",
    "product_id",
    "tg_platform_id",
    "oce_material_id_list",
    "product_name",
    "material_id",
    "page_size",
    "page_number",
    "dy_id",
    "d_z",
    "comment",
    "collect",
    "forward",
    "status",
    "signature",
    "dy_id_1","artificial_status","machine_review_status","is_sexual_inducement_content","is_bad_value_view","user_id"},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 material_platform_param = ""
    if msg_body.material_platform~="" then
        material_platform_param = string.format(" AND material_platform = %d ",tonumber(msg_body.material_platform)) 
    end

    local bl_status_param = ""
    if msg_body.bl_status~="" then
        bl_status_param = string.format(" AND bl_status = %d ",tonumber(msg_body.bl_status)) 
    end

    local publish_time_param = ""
    if msg_body.start_publish_time~="" and msg_body.end_publish_time~="" then
        publish_time_param = " AND DATE(publish_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_publish_time / 1000) .. ")) AND DATE(publish_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_publish_time / 1000) .. ")) "
    end

    local yun_fabu_time_param = ""
    if msg_body.start_yun_fabu_time~="" and msg_body.end_yun_fabu_time~="" then
        yun_fabu_time_param = " AND DATE(yun_fabu_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_yun_fabu_time / 1000) .. ")) AND DATE(yun_fabu_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_yun_fabu_time / 1000) .. "))"
    end

    local opt_time_param = ""
    if msg_body.start_opt_time~="" and msg_body.end_opt_time~="" then
        opt_time_param = " AND DATE(opt_time) >= DATE(FROM_UNIXTIME(" .. (msg_body.start_opt_time / 1000) .. ")) AND DATE(opt_time) <= DATE(FROM_UNIXTIME(" .. (msg_body.end_opt_time / 1000) .. "))"
    end

    local exe_review_person_id_param = ""
    if msg_body.exe_review_person_id~="" then
        exe_review_person_id_param = string.format(" AND exe_review_person_id = %d ",tonumber(msg_body.exe_review_person_id)) 
    end


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

    local word_param = ""
    if msg_body.min_book_word~="" and msg_body.max_book_word~="" then
        word_param =  string.format(" AND CAST(words AS UNSIGNED) >= %f  AND CAST(words AS UNSIGNED) <= %f ",msg_body.min_book_word,msg_body.max_book_word)
    end

    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 genre_param = ""
    if msg_body.genre~="" then
        genre_param =  string.format(" AND genre = %d ",tonumber(msg_body.genre)) 
    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_id_list_param = ""
    if msg_body.oce_material_id_list~="" then
        local idString = table.concat(msg_body.oce_material_id_list, ",")
        oce_material_id_list_param =string.format(" AND oce_material_id IN (%s) ",idString)
    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 machine_review_status_param = ""
    if msg_body.machine_review_status~="" then
        machine_review_status_param = " AND machine_review_status = "..msg_body.machine_review_status.." "
    end

    local is_sexual_inducement_content_param = ""
    if msg_body.is_sexual_inducement_content~="" then
        is_sexual_inducement_content_param = " AND is_sexual_inducement_content = "..msg_body.is_sexual_inducement_content.." "
    end

    local is_bad_value_view_param = ""
    if msg_body.is_bad_value_view~="" then
        is_bad_value_view_param = " AND is_bad_value_view = "..msg_body.is_bad_value_view.." "
    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 = material_platform_param..bl_status_param..publish_time_param..yun_fabu_time_param..opt_time_param..exe_review_person_id_param..word_param..genre_param..date_param..product_param..oce_material_id_list_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..machine_review_status_param..is_sexual_inducement_content_param..is_bad_value_view_param..user_id_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