video-tools-server/service/dbmgr/init.lua

local skynet = require "skynet"
local s = require "service"
local mysql = require "skynet.db.mysql"
local runconfig = require("run_config")
local db = nil
local cjson = require "cjson"
local tools = require "tools"

s.resp.parse_template_info = function(info,template_info)
    --片头
    local head_video = {}
    local type = 1     --	类型 1=片头视频，2=视频，3=片尾视频，4=bgm音频，5=片尾音频,6=图片
    local video_crop_time = info.video_crop_time
    local ratio = info.ratio
    local user_id = info.user_id
    -- skynet.error("(info.template_info",info.template_info)
    -- if template_info.piantou_video_data.is_random then --说明选择的是随机
    --     local folder_list = template_info.piantou_video_data.random_folder_list
    --     if folder_list ==nil or #folder_list ==0 then
    --         skynet.error("开始随机生成全部片头")
    --         --说明选择的全部随机
    --         local isOk,list = s.resp.get_random_res_by_type(user_id,1,ratio,0)
    --         if not isOk then
    --             skynet.error("缺少片头资源！")
    --         else
    --             if #list>=1 then
    --                 skynet.error("获取全部随机的一个指定片头")
    --                 table.insert(head_video,1,{video_id =list[1].id,path =cjson.decode( list[tools.getRandomIndex(list)].file_info).path,duration=list[1].duration })
    --                 tools.dump(head_video)
    --             end
    --         end 
    --     else -- 说明选择了指定文件夹随机
    --         local isOk,list = s.resp.get_random_res_by_folder_list(1,ratio,folder_list,0,user_id)
    --         if not isOk then
    --             skynet.error("缺少片头资源！")
    --         else
    --             if #list>=1 then
    --                 table.insert(head_video,1,list[tools.getRandomIndex(list)])
    --             end
    --         end 
    --     end
    -- else 
    --     if template_info.piantou_video_data.is_specified then --说明是指定了文件
    --         local isok,item = s.resp.get_res_file_by_id(template_info.piantou_video_data.specified_res_list[1].file_id)
    --         if isok then
    --             table.insert(head_video,1,item)
    --         end
    --     else
    --         skynet.error("参数错误！没有指定随机也没指定固定文件")
    --     end
    -- end

    if  template_info.piantou_video_data.select_video_file~=nil then
        local data = template_info.piantou_video_data.select_video_file
        table.insert(head_video,1,{video_id =data.info.id,path =data.info.path,duration=data.info.duration,info=data })
        -- table.insert(head_video,1,data)
    end

    if #head_video <= 0 then
        skynet.error("片头是空的")
    end

    --内容
    local content_video = {}
    local json_info = cjson.decode(info.template_info)
    -- skynet.error("json_info.subtitles_audio_duration",json_info.subtitles_audio_duration)
    -- skynet.error("head_video[1]",head_video[1].duration)
    -- tools.dump(json_info.subtitles_audio_duration)   
    -- skynet.error("head_video[1]",head_video[1].info)
    -- tools.dump(head_video[1].info)
    local need_num = math.floor((json_info.subtitles_audio_duration  - head_video[1].duration) / (video_crop_time)) +1
    if template_info.content_video_data.is_random then --说明选择的是随机
        local folder_list = template_info.content_video_data.random_folder_list
        if folder_list ==nil or #folder_list ==0 then
            --说明选择的全部随机
            local isOk,list = s.resp.get_random_res_by_type(user_id,2,ratio,(video_crop_time+5))
            if not isOk then
               return skynet.error("缺少内容资源！")
            else
                skynet.error("need_num",need_num)
                if need_num>#list then
                     skynet.error("缺少内容资源！")
                end
                local random_array = tools.getRandomArray(list)
                -- skynet.error("random_array")
                -- tools.dump(random_array)
                for i = 1, need_num, 1 do
                    local random_index = random_array[i]
                    table.insert(content_video,i,{video_id =list[random_index].id,path =cjson.decode( list[random_index].file_info).path,duration=list[random_index].duration,info = list[random_index].file_info })
                    -- table.insert(content_video,i,list[random_index].file_info)
                end
            end 
        else -- 说明选择了指定文件夹随机
            local isOk,list = s.resp.get_random_res_by_folder_list(2,ratio,folder_list,(video_crop_time+5),user_id)
            if not isOk then
                return skynet.error("缺少内容资源！")
             else
                 if need_num>#list then
                      skynet.error("缺少内容资源！")
                 end
                 local random_array = tools.getRandomArray(list)
                 for i = 1, need_num, 1 do
                    local random_index = random_array[i]
                    table.insert(content_video,i,{video_id =list[random_index].id,path =cjson.decode( list[random_index].file_info).path,duration=list[random_index].duration ,info = list[random_index].file_info})
                    -- table.insert(content_video,i,list[random_index].file_info)
                end
             end 
        end
    else 
        if template_info.content_video_data.is_specified then --说明是指定了文件
            local specified_res_list = template_info.content_video_data.specified_res_list
            if #specified_res_list<need_num  then
                skynet.error("指定文件缺少",need_num)
            else
                for i = 1, #specified_res_list, 1 do
                    local isok,item = s.resp.get_res_file_by_id(specified_res_list[1].file_id)
                    if isok then
                        -- table.insert(content_video,i, item.file_info)
                        table.insert(head_video,i,{video_id =item.id,path =cjson.decode( item.file_info).path,duration=item.duration,info =  item.file_info})
                    end
                end
            end
        else
            skynet.error("参数错误！没有指定随机也没指定固定文件")
        end
    end

    --片尾
    local end_video = {}
    -- if template_info.pianwei_video_data.is_random then --说明选择的是随机
    --     local folder_list = template_info.pianwei_video_data.random_folder_list
    --     if folder_list ==nil or #folder_list ==0 then
    --         --说明选择的全部随机
    --         local isOk,list = s.resp.get_random_res_by_type(user_id,3,ratio,0)
    --         if not isOk then
    --            return skynet.error("缺少片尾资源！")
    --         else
    --             if #list>=1 then
    --                 table.insert(end_video,1,{video_id =list[1].id,path =cjson.decode( list[tools.getRandomIndex(list)].file_info).path,duration=list[1].duration })
    --             end
    --         end 
    --     else -- 说明选择了指定文件夹随机
    --         local isOk,list = s.resp.get_random_res_by_folder_list(3,ratio,folder_list,0,user_id)
    --         if not isOk then
    --            return skynet.error("缺少片尾资源！")
    --         else
    --             if #list>=1 then
    --                 table.insert(end_video,1,{video_id =list[1].id,path =cjson.decode( list[tools.getRandomIndex(list)].file_info).path,duration=list[1].duration })
    --             end
    --         end 
    --     end
    -- else 
    --     if template_info.pianwei_video_data.is_specified then --说明是指定了文件
    --         local isok,item = s.resp.get_res_file_by_id(template_info.pianwei_video_data.specified_res_list[1].file_id)
    --         if isok then
    --             table.insert(end_video,1,item)
    --         end
    --     else
    --         skynet.error("参数错误！没有指定随机也没指定固定文件")
    --     end
    -- end
    if  template_info.pianwei_video_data.select_video_file~=nil then
        local data = template_info.pianwei_video_data.select_video_file
        table.insert(end_video,1,{video_id =data.info.id,path =data.info.path,duration=data.info.duration,info = data })
        -- table.insert(end_video,1,data)
    end
    if #end_video<=0 then
        skynet.error("添加片尾资源失败")
    end

    --BGM音效
    local bgm_audio = {}
    -- if template_info.bgm_audio_data.is_random then --说明选择的是随机
    --     local folder_list = template_info.bgm_audio_data.random_folder_list
    --     if template_info.bgm_audio_data.random_folder_list ==nil or #template_info.bgm_audio_data.random_folder_list ==0 then
    --         --说明选择的全部随机
    --         local isOk,list = s.resp.get_random_res_by_type(user_id,4,ratio,0)
    --         if not isOk then
    --             skynet.error("缺少BGM音效资源！")
    --         else
    --             if #list>=1 then
    --                 table.insert(bgm_audio,1,{video_id =list[1].id,path =cjson.decode( list[tools.getRandomIndex(list)].file_info).path,duration=list[1].duration })
    --             end
    --         end 
    --     else -- 说明选择了指定文件夹随机
    --         local isOk,list = s.resp.get_random_res_by_folder_list(4,ratio,folder_list,0,user_id)
    --         if not isOk then
    --             skynet.error("缺少BGM音效资源！")
    --         else
    --             if #list>=1 then
    --                 table.insert(bgm_audio,1,{video_id =list[1].id,path =cjson.decode( list[tools.getRandomIndex(list)].file_info).path,duration=list[1].duration })
    --             end
    --         end 
    --     end
    -- else 
    --     if template_info.bgm_audio_data.is_specified then --说明是指定了文件
    --         local isok,item = s.resp.get_res_file_by_id(template_info.bgm_audio_data.specified_res_list[1].file_id)
    --         if isok then
    --             table.insert(bgm_audio,1,item)
    --         end
    --     else
    --         skynet.error("参数错误！没有指定随机也没指定固定文件")
    --     end
    -- end
    if  template_info.bgm_audio_data.select_video_file~=nil then
        local data = template_info.bgm_audio_data.select_video_file
        table.insert(bgm_audio,1,{video_id =data.info.id,path =data.info.path,duration=data.info.duration,info = data })
        -- table.insert(bgm_audio,1,data)
    end


    --底部
    local bottom = {}

    if  template_info.bottom_text_image_data.select_video_file~=nil then
        local data = template_info.bottom_text_image_data.select_video_file
        table.insert(bottom,1,{info = data })
        -- table.insert(bgm_audio,1,data)
    end

    ---
    local corner = {}
    if  template_info.corner_mark_image_data.select_video_file~=nil then
        local data = template_info.corner_mark_image_data.select_video_file
        table.insert(corner,1,{info = data })
        -- table.insert(bgm_audio,1,data)
    end
    return head_video,content_video,end_video,bgm_audio,bottom,corner
end

--根据部门id获取部门列表
s.resp.get_group_list = function(group_id,call_back)
    skynet.fork(function()
        local sql = string.format("select * from users where group_id = %d ",group_id)
        local res = db:query(sql)
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            call_back(tab)
        else
            call_back({})
            skynet.error("No data found.")
        end
    end)
end

s.resp.getUserById = function(id)
    local sql = string.format("select * from users where id = %d ",id)
    skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

s.resp.updateUserInfoById = function(id)
    
end

s.resp.createUser = function()
    
end

s.resp.deleteUserById = function(id)
    
end

s.resp.verify = function(msg_body)
    local isok = false
    local user_data = nil
    isok,user_data = s.resp.select_user_by_account(msg_body.account)
    if not isok or user_data==nil then
        return isok,user_data
    end
    isok = false
    if msg_body.password==user_data.password then
 
        isok = true
        -- user_data = {user_id=1,user_name="小丑"}
    end
    return isok,user_data
end

s.resp.select_user_by_account = function(account) 
    local sql = string.format("select * from users where account = '%s'",account)
    skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end

    -- tools.dump(res)

end

s.resp.select_user_by_user_id = function(user_id) 
    
end

s.resp.add_new_user = function(msg_body)
    -- skynet.error("msg_body",msg_body.group_type)
   local sql = string.format("INSERT INTO users (name,account, password,permit_id,group_id) VALUES ('%s', '%s','%s',%d,%d)",msg_body.user_name,msg_body.account,msg_body.password,msg_body.permit_id,msg_body.group_id)
   local res = db:query(sql)
   return true
end
--- 新建文件夹
s.resp.new_folder = function(msg_body,fd)
    skynet.fork(function(_fd)
        local isOk,folder_info = s.resp.get_folder_by_name(msg_body)
        if isOk then
            return  tools.response_db_new_folder(_fd,false,string.format("创建失败，已存在 %s !",msg_body.folder_name))
        end
        local sql = string.format("INSERT INTO folder_list_tab (user_id,folder_name, folder_type,is_public,classification_id) VALUES (%d, '%s',%d,%d,%d)",msg_body.user_id,msg_body.folder_name,msg_body.folder_type,msg_body.is_public,msg_body.classification_id)
        -- skynet.error(sql)
        local res = db:query(sql)
        tools.response_db_new_folder(_fd,true)
    end,fd)
    return true 
end
--获取资源文件夹列表
s.resp.folder_list = function(msg_body,fd)
    skynet.fork(function(_fd)
        local sql = string.format("select * from folder_list_tab where user_id = %d AND folder_type = %d AND classification_id = %d",msg_body.user_id,msg_body.type,msg_body.classification_id)
        local res = db:query(sql)
        -- skynet.error('folder_list',sql)
        -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            -- tools.dump(tab)
            -- return true,tab
            tools.response_db_folder_list(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            tools.response_db_folder_list(_fd,msg_body,false,nil)
            -- return false,nil
        end
    end,fd)
    return true 
end

--获取文件夹列表每个文件夹的文件数量
s.resp.folder_res_list_nums = function(folder_list,fd,total_count)
    skynet.fork(function(_fd)
        local temp ={}
        for i = 1, #folder_list, 1 do
            local msg_body = folder_list[i]
            -- tools.dump(msg_body)
            local sql = string.format("select * from res_list_tab where user_id = %d AND folder_id = %d AND is_public = %d AND is_delete = 0",msg_body.user_id,msg_body.id,msg_body.is_public)
            local res = db:query(sql)
            local count = 0
            if res and #res > 0 then
                count = #res
                skynet.error("Found data:")
                -- tools.dump(tab)
                -- return true,tab
            else
                skynet.error("No data found.",msg_body.id)
            end
            temp[msg_body.id] = count
            -- table.insert(temp,msg_body.id,count)
        end
        tools.response_db_folder_list_nums(_fd,folder_list,temp,total_count)
    end,fd)
end
--获取指定文件夹内的资源列表
s.resp.folder_res_list = function(msg_body,fd,call_back)
    skynet.fork(function(_fd) 
        local sql_duration = ""
        if msg_body.duration~=nil then
            sql_duration = string.format(" AND duration >= %d",msg_body.duration)
        end
        local sql = string.format("select * from res_list_tab where user_id = %d AND folder_id = %d AND is_public = %d  AND is_delete = 0"..sql_duration,msg_body.user_id,msg_body.folder_id,msg_body.is_public)
        local res = db:query(sql)
        skynet.error('获取指定文件夹内的资源列表',sql)
        -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            if not call_back and _fd~=nil then
                tools.response_db_folder_res_list(_fd,msg_body,true,tab)
            else
                if call_back ~=nil then
                    call_back(tab)
                end
            end
           
            -- tools.dump(tab)
            -- return true,tab
        else
            skynet.error("No data found.",call_back)
            if  call_back == nil  and _fd~=nil then
                tools.response_db_folder_res_list(_fd,msg_body,false,nil)
            else
                if call_back ~=nil then
                    call_back({})
                end
            end
            
            -- return false,nil
        end
    end,fd)
    return true
end
s.resp.on_recv = function (source, msg_id, msg_body,fd,...)
    if db==nil then
        return false,nil
    end
    if s.resp[msg_id]==nil then
        return false,nil
    end
    return s.resp[msg_id](msg_body,fd,...)
end

--添加资源数据到表里
s.resp.add_res_file = function(msg_body)
    skynet.fork(function()
        local temp = '{"width": 100, "height": 100, "duration": 123}'
        temp = cjson.encode(msg_body)
        local duration = 0
        if msg_body.duration~=nil then
            duration = tonumber(msg_body.duration)
        end
        local sql = string.format("INSERT INTO res_list_tab (user_id, folder_id, file_type, file_name, is_public,file_info,duration,classification_id) VALUES (%d, %d, %d, '%s',%d,'%s',%f,%d)",msg_body.user_id,msg_body.folder_id,msg_body.stype,msg_body.file_name,msg_body.is_public,temp,duration,msg_body.classification_id)
        local res = db:query(sql)
    end)
    return true
end

--更改文件夹名
s.resp.reset_folder_name = function(msg_body)
    local isOk = s.resp.get_res_folder_by_id(msg_body.folder_id)
    if not isOk then
        return false
    end
    local sql = string.format("UPDATE folder_list_tab SET folder_name ='%s' WHERE id = %d  AND is_public = %d",msg_body.name,msg_body.folder_id,msg_body.is_public)
    db:query(sql)
    return true
end

--更改资源文件名
s.resp.reset_res_name = function(msg_body)
    local isOk = s.resp.get_res_file_by_id(msg_body.file_id)
    if not isOk then
        return false
    end
    local sql = string.format("UPDATE res_list_tab SET file_name ='%s' WHERE id = %d AND folder_id = %d AND is_public = %d  AND is_delete = 0",msg_body.name,msg_body.file_id,msg_body.folder_id,msg_body.is_public)
    db:query(sql)
    return true
end

--根据id获取资源 
s.resp.get_res_file_by_id = function(file_id)
    local sql = string.format("select * from res_list_tab where id = %d  AND is_delete = 0",file_id)
    -- skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--根据id获取文件夹 
s.resp.get_res_folder_by_id = function(folder_id)
    local sql = string.format("select * from folder_list_tab where id = %d ",folder_id)
    -- skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--搜索资源 
s.resp.search_res = function(msg_body,fd)
    skynet.fork(function(_fd)
        local sql_duration = ""
        if msg_body.duration~=nil then
            sql_duration = string.format(" AND duration >= %f",msg_body.duration)
        end
        local sql = string.format("SELECT * FROM res_list_tab WHERE file_type = %d AND file_name LIKE '%%%s%%'  AND is_delete = 0 AND classification_id = %d AND user_id = %d"..sql_duration,msg_body.type ,msg_body.search_content,msg_body.classification_id,msg_body.user_id)
        -- skynet.error("sql",sql)
        local res = db:query(sql)
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            -- tools.dump(tab)
            -- return true,tab
            tools.response_db_search_res(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            -- return false,nil
            tools.response_db_search_res(_fd,msg_body,false,nil)
        end
    end,fd)
    return true
end
--根据所有文件id查到所有文件的url并删除
local fork_get_file_list_url_and_delete = function(deleteString,call_back)
    local sql = string.format("SELECT * FROM res_list_tab WHERE id IN (%s)", deleteString)
    local res = db:query(sql)
    if res and #res > 0 then
        local tab =  {}
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab = tools.getDbResData(res)
        end
        
        skynet.error("Found data:fork_get_file_list_url_and_delete")
        local url_list = {}
        local count = 1
        for i = 1, #tab, 1 do
            if tab[i].ref_id==0 then
                local file_info =  cjson.decode(tab[i].file_info)
                table.insert(url_list,count,file_info.path)
                count = count + 1
            end
        end
        skynet.send("tools_work","lua","delete_file",url_list)
        -- tools.dump(url_list)
        -- return true,tab
    else
        skynet.error("No data found.")
        -- return false,nil
    end

    if call_back~=nil then
        call_back()
    end
   
end
--执行删除文件操作
local fork_delete_file = function(msg_body)
    -- tools.dump(msg_body)
    local listToDelete = {}
    for i = 1, #msg_body.file_id_list, 1 do
        table.insert(listToDelete,i,msg_body.file_id_list[i])
    end
    local deleteString = table.concat(listToDelete, ',')

    local sql = string.format("UPDATE res_list_tab SET is_delete = %d WHERE id IN (%s)",1,deleteString)
    db:query(sql)

    skynet.fork(fork_get_file_list_url_and_delete,deleteString,function()
        local delete_sql = string.format("DELETE FROM res_list_tab WHERE id IN (%s) AND is_public = %d AND user_id = %d", deleteString,msg_body.is_public,msg_body.user_id)
        db:query(delete_sql)
    end)
end

--执行删除文件夹操作
local fork_delete_folder =  function(msg)
    for i = 1, #msg.folder_id_list, 1 do
        local  isok = s.resp.folder_res_list({
            folder_id = msg.folder_id_list[i],
            user_id = msg.user_id,
            is_public=msg.is_public},nil,function(list)
                -- skynet.error("执行删除文件夹操作",#list)
                if #list>0 then
                    local temp = {}
                    for i = 1, #list, 1 do
                        table.insert(temp,i,list[i].id)
                    end
                    skynet.fork(fork_delete_file,{file_id_list=temp,user_id=msg.user_id,is_public=msg.is_public})
                end
            end)
    end
end

--删除资源文件
s.resp.delete_res = function(msg_body)
    skynet.fork(fork_delete_file,msg_body)
    return true
end
--删除文件夹
s.resp.delete_folder =function(msg_body)
    local listToDelete = {}
    for i = 1, #msg_body.folder_id_list, 1 do
        table.insert(listToDelete,i,msg_body.folder_id_list[i])
    end
    local deleteString = table.concat(listToDelete, ',')
    local sql = string.format("DELETE FROM folder_list_tab WHERE id IN (%s) AND is_public = %d AND user_id = %d", deleteString,msg_body.is_public,msg_body.user_id)
    db:query(sql)
    skynet.fork(fork_delete_folder,msg_body)
    return true
end
--新建模板
s.resp.create_template = function(msg_body,fd)
    local template_info = "{}"
    if msg_body.template_info~=nil and msg_body.template_info~="" then
        template_info = msg_body.template_info
    end
    local sql = string.format("INSERT INTO template_list_tab (user_id,project_name, book_name,ratio,video_stype,subtitles,subtitles_audio,video_nums,video_crop_time,template_info) VALUES (%d, '%s','%s',%d,%d,'%s','%s',%d,%d,'%s')",msg_body.user_id,msg_body.project_name,msg_body.book_name,msg_body.ratio,msg_body.video_stype,msg_body.subtitles,msg_body.subtitles_audio,msg_body.video_nums,msg_body.video_crop_time,template_info)
    local res = db:query(sql)
    -- skynet.error("sql",sql)
    return true
end
--获取模板列表
s.resp.get_template_list = function(msg_body,fd)
    skynet.fork(function(_fd)
        local sql = string.format("select * from template_list_tab where user_id = %d AND video_stype = %d",msg_body.user_id,msg_body.video_stype)
        skynet.error("sql",sql)
        local res = db:query(sql)
          -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            skynet.error("Found data:",tab)
            -- return true,tab
            tools.response_db_get_template_list(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            -- return false,nil
            tools.response_db_get_template_list(_fd,msg_body,false,nil)
        end
    end,fd)
    return true
end
--删除模板
s.resp.delete_template = function(msg_body)
    local listToDelete = {}
    for i = 1, #msg_body.template_id_list, 1 do
        table.insert(listToDelete,i,msg_body.template_id_list[i])
    end
    local deleteString = table.concat(listToDelete, ',')
    local sql = string.format("DELETE FROM template_list_tab WHERE id IN (%s) AND user_id = %d", deleteString,msg_body.user_id)
    db:query(sql)
    skynet.error(sql)
    return true
end
--保存/修改模板
s.resp.save_template = function(msg_body,fd)
    local template_info = "{}"
    if msg_body.template_info~="" and msg_body.template_info~=nil then
        template_info = cjson.decode(msg_body.template_info)
        if template_info.creat_video_list~=nil and template_info.creat_video_list~="" then
            if #template_info.creat_video_list >0 then
                for i = 1,  #template_info.creat_video_list, 1 do
                    if template_info.creat_video_list[i]~=nil then
                        if template_info.creat_video_list[i].title_html~=nil then
                            template_info.creat_video_list[i].title_html = tools.base64encode(template_info.creat_video_list[i].title_html)
                        end
                    end
                end
            end
        end
  
        template_info = cjson.encode(template_info)
    end

    local sql = string.format("UPDATE template_list_tab SET project_name = '%s' , book_name = '%s' ,ratio = %d, video_stype = %d, subtitles = '%s', subtitles_audio = '%s' ,video_nums = %d, video_crop_time = %d, template_info = '%s'  WHERE id = %d ",msg_body.project_name,msg_body.book_name,msg_body.ratio,msg_body.video_stype,msg_body.subtitles,msg_body.subtitles_audio,msg_body.video_nums,msg_body.video_crop_time,template_info,msg_body.template_id)
    -- skynet.error("sql",sql)
    db:query(sql)
    return true
end
--搜索模板
s.resp.search_template = function(msg_body,fd)
    skynet.fork(function(_fd)
        local sql = string.format("SELECT * FROM template_list_tab WHERE user_id = %d AND video_stype = %d AND book_name LIKE '%%%s%%'",msg_body.user_id ,msg_body.video_stype,msg_body.search_content)
        -- local sql = string.format("select * from template_list_tab where user_id = %d AND video_stype = %d",msg_body.user_id,msg_body.video_stype)
        skynet.error("sql",sql)
        local res = db:query(sql)
          -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            skynet.error("Found data:",tab)
            -- return true,tab
            tools.response_db_search_template(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            -- return false,nil
            tools.response_db_search_template(_fd,msg_body,false,nil)
        end
    end,fd)
    return true
end
--根据模板ID获取模板信息
s.resp.get_template_info_by_id = function(msg_body,fd,call_back) 
    skynet.fork(function(_fd)
        local sql = string.format("SELECT * FROM template_list_tab WHERE user_id = %d AND id = %d",msg_body.user_id ,msg_body.template_id)
        -- local sql = string.format("select * from template_list_tab where user_id = %d AND video_stype = %d",msg_body.user_id,msg_body.video_stype)
        skynet.error("sql",sql)
        local res = db:query(sql)
          -- 判断是否找到数据
        if res and #res > 0 then
            local tab = tools.getDbResData(res)
            skynet.error("Found data:",tab)
            -- return true,tab
            if call_back~=nil and fd ==nil then
                call_back(tab)
            else
                tools.response_db_get_template_info_by_id(_fd,msg_body,true,tab)
            end
   
        else
            skynet.error("No data found.")
            -- return false,nil
            if call_back~=nil and fd ==nil then
                call_back(nil)
            else
                tools.response_db_search_template(_fd,msg_body,false,nil)
            end
        end
    end,fd)
    return true
end
--获取指定类型所有文件
s.resp.get_file_list_by_type = function(msg_body,fd)
    skynet.fork(function(_fd,_msg_body)
        local sql_duration = ""
        if msg_body.duration~=nil then
            sql_duration = string.format(" AND duration >= %f",msg_body.duration)
        end
        local sql = string.format("SELECT * FROM res_list_tab WHERE file_type = %d AND user_id = %d AND is_delete = 0"..sql_duration,msg_body.type,msg_body.user_id)
        local res = db:query(sql)
        -- skynet.error(sql)
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            tools.response_db_get_file_list_by_type(_fd,_msg_body,true,tab)
            -- return true,tab
        else
            skynet.error("No data found.")
            -- return false,nil
            tools.response_db_get_file_list_by_type(_fd,_msg_body,false,nil)
        end
    end,fd,msg_body)
end

--随机一个类型，获取相应数量的资源列表
s.resp.get_random_res_by_type = function(user_id,file_type,classification_id,duration)
    local sql = string.format("SELECT *FROM res_list_tab WHERE file_type = %d  AND is_delete = 0 AND classification_id = %d AND duration>= %f AND user_id = %d", file_type,classification_id,duration,user_id)
    local res = db:query(sql)
    if res and #res > 0 then
        local tab =  {}
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab =  tools.getDbResData(res)
        end
        -- local test = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
        -- tools.shuffle(tab)
        -- tools.dump(test)
        -- tools.dump(tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--获取一个类型，指定在文件夹列表里随机
s.resp.get_random_res_by_folder_list = function(file_type,classification_id,folder_list,duration,user_id)
    local listToSelect = {}
    for i = 1, #folder_list, 1 do
        table.insert(listToSelect,i,folder_list[i].folder_id)
    end
    local selectString = table.concat(listToSelect, ',')
    skynet.error("file_type",file_type)
    skynet.error("classification_id",classification_id)
    skynet.error("folder_list")
    tools.dump(folder_list)
    skynet.error("user_id",user_id)
    local sql = string.format("SELECT* FROM res_list_tab WHERE file_type = %d  AND is_delete = 0 AND classification_id = %d AND duration>= %f AND user_id = %d AND folder_id in(%s)", file_type,classification_id,duration,user_id,selectString)
    local res = db:query(sql)
    if res and #res > 0 then
        local tab =  {}
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab = tools.getDbResData(res)
        end
        -- tools.dump(tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--生成滚动视频
s.resp.generate_scroll_video = function(template_tab,fd)
    local isOk,error_info = s.resp.check_template_scroll_isRight(template_tab)
    return isOk,error_info
end
--生成解压视频数据库
s.resp.generate_video = function(msg_body,fd)
    s.resp.get_template_info_by_id(msg_body,nil,function(tab)
        -- tools.dump(tab)
        if tab ~=nil then
            local isOk,error_info = s.resp.check_template_isRight(tab)
            if tab.video_stype == 1 then --解压
                if  not isOk then
                    return  tools.response(fd,200,error_info)
                end
            else
                isOk,error_info =  s.resp.generate_scroll_video(tab,fd)
                if  not isOk then
                    return  tools.response(fd,200,error_info)
                end
            end
 
            -- tools.dump(tab)
            local user_id = msg_body.user_id
            local template_id = msg_body.template_id
            local file_name = tab.book_name
            local video_stype = tab.video_stype
            --先查找是否有这个名的文件夹
            local sql = string.format("SELECT * FROM generate_video_folder_list_tab WHERE user_id = %d  AND book_name = '%s' AND video_stype = %d ",user_id,file_name,video_stype)
            local res = db:query(sql)
            local info = tools.getDbResData(res)
            local write_video_db = function(folder_id)
                skynet.error("开始生成",folder_id)
                local template_info = cjson.decode(tab.template_info)
                local gen_num = 0
                if  template_info.creat_video_list~=nil then
                    gen_num =  #template_info.creat_video_list
                end
                local custom = {}
                if template_info.custom~=nil and template_info.custom~="" then
                    custom = template_info.custom
                end
                -- skynet.error("custom")
                -- tools.dump(custom)
                for i = 1, gen_num, 1 do  -- 模拟数据每次生成5条 
                    local item_info = template_info.creat_video_list[i]
                    local head_video,content_video,end_video,bgm_audio,bottom,corner = s.resp.parse_template_info(tab,item_info)
                    -- tools.dump(head_video)
                    -- tools.dump(content_video)
                    -- tools.dump(end_video)
                    -- tools.dump(bgm_audio)
                    local is_corner_mark = item_info.is_corner_mark
                    local is_video_bottom_text = item_info.is_video_bottom_text
                    local hot_tag_type = 0
                    local item_custom = {}
                    
                    if item_info.custom~=nil and item_info.custom~="" then
                        item_custom = item_info.custom
                    end
                    

                    if is_corner_mark then
                        hot_tag_type = 1
                    end

                    local fictitious_tag_type = 0
                    if is_video_bottom_text then
                        fictitious_tag_type = 1
                    end
                    local gen_info = {user_id=0,id=0,project_name=tab.project_name,
                    book_name=tab.book_name,
                    ratio=tab.ratio,
                    video_stype=tab.video_stype,
                    crop_time=tab.video_crop_time,
                    marketing_title_info={marketing_title_top_h = 0,stroke_h=0,marketing_title=tools.base64decode(item_info.title_html)},
                    video_list = content_video,
                    subtitles = {stype=0,subtitle_font="",subtitles_path=template_info.subtitles_path},
                    subtitles_audio={path = template_info.subtitles_audio_path,duration=template_info.subtitles_audio_duration },
                    video_last_frame =end_video[1] or {},
                    video_first_frame = head_video[1] or {},
                    bgm_list = bgm_audio[1],
                    corner_mark_image = corner[1] or {},
                    bottom_text_image = bottom[1] or {},
                    hot_tag = {type=hot_tag_type,path=""}, --上边
                    fictitious_tag = {type=fictitious_tag_type,path=""}, --下边
                    custom = custom,
                    video_custom = item_custom,
                    }
                   
                    local video_name =  gen_info.book_name.."__视频"..i
                    local book_name =  gen_info.book_name
                    local ratio = gen_info.ratio
                    local video_stype = gen_info.video_stype
                    local video_url = ""
                    local generate_video_info = {}
                    generate_video_info =item_info 
                    generate_video_info.book_name = tab.book_name
                    generate_video_info.project_name = tab.project_name
                    generate_video_info.ratio = tab.ratio
                    generate_video_info.video_stype = tab.video_stype
                    generate_video_info = cjson.encode(generate_video_info)
                    local video_state = 0
                    sql = string.format("INSERT INTO generate_video_list_tab (user_id,template_id, folder_id,video_name,book_name,ratio,video_stype,video_url,video_state,generate_video_info) VALUES (%d,%d,%d,'%s','%s',%d,%d,'%s',%d,'%s')",user_id,
                    template_id,
                    folder_id,
                    video_name,
                    book_name,
                    ratio,
                    video_stype,
                    video_url,video_state,generate_video_info)
                    -- skynet.error("123sss")
                    -- tools.dump(gen_info)
                    db:query(sql)
                    -- skynet.error(sql)
                    sql = string.format("SELECT * FROM generate_video_list_tab WHERE user_id = %d and folder_id = %d and is_use = 0  LIMIT 1",user_id,folder_id)
                    res = db:query(sql)
                    if res and #res > 0 then
                        local tab =  {}
                        if #res > 0 then
                            tab = tools.getDbResData(res)
                            gen_info.id = tab.id
                            gen_info.user_id = user_id
                            sql = string.format("UPDATE generate_video_list_tab SET is_use = %d WHERE id =%d",1,gen_info.id)
                            db:query(sql)
                            skynet.send("tools_work","lua","generate_video",gen_info)
                        end
                        -- return true,tab
                    else
                        skynet.error("No data found.")
                    end
      
                end

                --获取5个未使用的生成视频文件
                tools.response_db_generate_video(fd,true)
            end
            if res and #res > 0 then       --存在这个文件夹，直接写入到这个文件夹
                local folder_id = info.id
                write_video_db(folder_id)
            else -- 没有这个文件夹则创建一个
                sql = string.format("INSERT INTO generate_video_folder_list_tab (user_id,book_name, generate_video_info,video_stype) VALUES (%d, '%s','%s' ,%d)",user_id,file_name,"{}",video_stype)
                db:query(sql)
                --查找新建的文件夹id
                sql = string.format("SELECT * FROM generate_video_folder_list_tab WHERE user_id = %d  AND book_name = '%s' AND video_stype = %d ",user_id,file_name,video_stype)
                res = db:query(sql)

                info = tools.getDbResData(res)
                local folder_id = info.id
                write_video_db(folder_id)
            end
            
        end
    end)
    return true
end

--获取生成视频文件夹列表
s.resp.get_generate_video_folder_list = function(msg_body,fd)
    skynet.fork(function(_fd,_msg_body)
        -- local count = msg_body.count
        -- local page = (msg_body.page - 1) * count
        local sql = string.format("select * from generate_video_folder_list_tab where user_id = %d AND video_stype = %d",_msg_body.user_id,_msg_body.video_stype)
        -- local sql = string.format("select * from generate_video_folder_list_tab where user_id = %d AND video_stype = %d  limit %d,%d",_msg_body.user_id,_msg_body.video_stype,page,count)
        local res = db:query(sql)
        -- skynet.error('folder_list',sql)
        -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            -- tools.dump(tab)
            -- return true,tab
            tools.response_db_generate_video_folder_list(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            tools.response_db_folder_list(_fd,msg_body,false,nil)
            -- return false,nil
        end
    end,fd,msg_body)
    return true
end

--获取文件夹内的生成视频
s.resp.get_video_list_by_folder_id = function(msg_body,fd,call_back)
    skynet.fork(function(_fd) 
        local sql = string.format("select * from generate_video_list_tab where user_id = %d AND folder_id = %d  AND is_delete = 0",msg_body.user_id,msg_body.folder_id)
        local res = db:query(sql)
        -- skynet.error('folder_res_list',sql)
        -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            if not call_back and _fd~=nil then
                tools.response_db_get_video_list_by_folder_id(_fd,msg_body,true,tab)
            else
                if call_back ~=nil then
                    call_back(tab)
                end
            end
           
            -- tools.dump(tab)
            -- return true,tab
        else
            skynet.error("No data found.",call_back)
            if  call_back == nil  and _fd~=nil then
                tools.response_db_get_video_list_by_folder_id(_fd,msg_body,false,nil)
            else
                if call_back ~=nil then
                    call_back({})
                end
            end
            
            -- return false,nil
        end
    end,fd)
    return true
end

--根据所有生成视频文件id查到所有文件的url并删除
local fork_get_gen_video_file_list_url_and_delete = function(delete_list,call_back)
    local url_list = {}
    for i = 1, #delete_list, 1 do
       table.insert(url_list,i, delete_list[i].path)
    end
    skynet.send("tools_work","lua","delete_file",url_list)
    if call_back~=nil then
        call_back()
    end
   
end
--执行删除生成视频文件操作
local fork_delete_gen_video_file = function(msg_body)
    -- tools.dump(msg_body)
    local listToDelete = {}
    for i = 1, #msg_body.file_list, 1 do
        table.insert(listToDelete,i,msg_body.file_list[i].id)
    end
    local deleteString = table.concat(listToDelete, ',')

    local sql = string.format("UPDATE generate_video_list_tab SET is_delete = %d WHERE id IN (%s)",1,deleteString)
    db:query(sql)
    skynet.fork(fork_get_gen_video_file_list_url_and_delete,msg_body.file_list,function()
        local delete_sql = string.format("DELETE FROM generate_video_list_tab WHERE id IN (%s) AND user_id = %d", deleteString,msg_body.user_id)
        db:query(delete_sql)
        skynet.error(delete_sql)
    end)
end

--执行删除生成视频文件夹操作
local fork_delete_gen_video_folder =  function(msg)
    for i = 1, #msg.folder_id_list, 1 do
        local  isok = s.resp.get_video_list_by_folder_id({
            folder_id = msg.folder_id_list[i],
            user_id = msg.user_id},nil,function(list)
                skynet.error("执行删除生成视频文件夹操作",#list)
                if #list>0 then
                    local temp = {}
                    for i = 1, #list, 1 do
                        table.insert(temp,i,list[i])
                    end
                    skynet.fork(fork_delete_gen_video_file,{file_list=temp,user_id=msg.user_id})
                end
            end)
            local delete_sql = string.format("DELETE FROM generate_video_folder_list_tab WHERE id = %d", msg.folder_id_list[i])
            db:query(delete_sql)
    end
end

--删除生成视频文件夹
s.resp.delete_generate_video_folder = function(msg_body,fd)
    skynet.fork(fork_delete_gen_video_folder,msg_body,fd)
    return true
end

--删除生成视频
s.resp.delete_generate_video = function(msg_body,fd)
    local temp = {}
    msg_body.file_list = {}
    local listToDelete = {}
    for i = 1, #msg_body.file_id_list, 1 do
        table.insert(listToDelete,i,msg_body.file_id_list[i])
    end
    local deleteString = table.concat(listToDelete, ',')
    local sql = string.format("select * from generate_video_list_tab WHERE id IN (%s) AND is_delete = 0",deleteString)
    local res = db:query(sql)
    skynet.error(sql)
    if res and #res > 0 then
        local tab =  {}
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab = tools.getDbResData(res)
        end
        for i = 1, #tab, 1 do
            table.insert(msg_body.file_list,i,tab[i])
        end
        skynet.fork(fork_delete_gen_video_file,msg_body,fd)
        skynet.error("Found data:")

    else
        skynet.error("No data found.")
    end

    return true
end

--重命名生成视频文件夹的名字
s.resp.reset_generate_video_folder_name = function(msg_body,fd)
    local sql = string.format("UPDATE generate_video_list_tab SET video_name ='%s' WHERE id = %d ",msg_body.name,msg_body.folder_id)
    db:query(sql)
    return true
end

--生成视频搜索
s.resp.search_generate_video_file = function(msg_body,fd)
    skynet.fork(function(_fd)
        -- tools.dump(msg_body)
        local sql = string.format("SELECT * FROM generate_video_list_tab WHERE   video_name LIKE '%%%s%%'  AND is_delete = 0 AND video_stype = %d AND user_id = %d" ,msg_body.search_content,msg_body.video_stype,msg_body.user_id)
        -- skynet.error("sql",sql)
        local res = db:query(sql)
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            
            skynet.error("Found data:")
            -- tools.dump(tab)
            -- return true,tab
            tools.response_db_search_generate_video_file(_fd,msg_body,true,tab)
        else
            skynet.error("No data found.")
            -- return false,nil
            tools.response_db_search_generate_video_file(_fd,msg_body,false,nil)
        end
    end,fd)
    return true
end


--获取生成视频文件夹列表每个文件夹的文件数量
s.resp.folder_gen_video_list_nums = function(folder_list,fd,total_count)
    skynet.fork(function(_fd)
        local temp ={}
        for i = 1, #folder_list, 1 do
            local msg_body = folder_list[i]
            -- tools.dump(msg_body)
            local sql = string.format("select * from generate_video_list_tab where user_id = %d AND folder_id = %d  AND is_delete = 0",msg_body.user_id,msg_body.id)
            local res = db:query(sql)
            local count = 0
            if res and #res > 0 then
                count = #res
                skynet.error("Found data:")
                -- tools.dump(tab)
                -- return true,tab
            else
                skynet.error("No data found.",msg_body.id)
            end
            temp[msg_body.id] = count
            -- table.insert(temp,msg_body.id,count)
        end
        tools.response_db_folder_generate_video_list_nums(_fd,folder_list,temp,total_count)
    end,fd)
end

--更新生成视频的状态
s.resp.update_gen_video_file_status = function(msg_body)
    local custom = "{}"
    if msg_body.custom~=nil and msg_body.custom~="" then
        custom = msg_body.custom
    end
    local sql = string.format("UPDATE generate_video_list_tab SET video_url = '%s', path = '%s' , custom = '%s', video_state = %d WHERE id = %d and user_id = %d",msg_body.surl,msg_body.path,custom,msg_body.state,msg_body.id,msg_body.user_id)
    db:query(sql)
    skynet.error(sql)
    return true
end

--检测指定类型文件是否够数量
s.resp.checkNumsByType = function(msg_body)
    local duration = 0
    if msg_body.duration~= nil then
        duration = msg_body.duration
    end
    local limit = msg_body.limit
    local type = msg_body.type
    local sql = string.format("select *from res_list_tab where duration >= %f AND file_type= %d AND user_id = %d LIMIT %d",duration,type,msg_body.user_id,limit)
    -- skynet.error("sql",sql)
    local res = db:query(sql)
    if res and #res > 0 then
        if #res >= tonumber(limit) then
            return true
        end
        return false
    else
        return false
    end
end

--修改密码
s.resp.reset_password = function(msg_body)
    local sql = string.format("UPDATE users SET password = '%s' WHERE id = %d ",msg_body.new_pw,msg_body.user_id)
    db:query(sql)
    return true
end

--检测是否有此文件
s.resp.check_have_file = function(msg_body)
    local sql = string.format("select * from res_list_tab where ref_id = %d  AND is_delete = 0 AND user_id = %d LIMIT 1",msg_body.file_id,msg_body.user_id)
    -- skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--检测是否有此文件夹
s.resp.check_have_folder = function(msg_body)
    local sql = string.format("select * from folder_list_tab where ref_id = %d AND user_id = %d LIMIT 1",msg_body.folder_id,msg_body.user_id)
    -- skynet.error("sql",sql)
    local res = db:query(sql)
      -- 判断是否找到数据
    if res and #res > 0 then
        local tab = tools.getDbResData(res)
        skynet.error("Found data:",tab)
        return true,tab
    else
        skynet.error("No data found.")
        return false,nil
    end
end

--检测滚屏模板是否正常
s.resp.check_template_scroll_isRight = function(info)
    local isok  = s.resp.checkNumsByType({
    duration = 0,
    type = 6,
    limit = 1,
    user_id = info.user_id})

    if not isok then
        return false,cjson.encode({code=10001,msg = "新建模板失败,图片资源不足！"})
    end
        
    isok  = s.resp.checkNumsByType({
        duration = 0,
        type = 4,
        limit = 1,
        user_id = info.user_id}
    )

    if not isok then
        return false,cjson.encode({code=10001,msg = "新建模板失败,BGM音频不足！"})
    end


    isok  = s.resp.checkNumsByType({
        duration = 0,
        type = 3,
        limit = 1,
        user_id = info.user_id}
    )

    if not isok then
        return false,cjson.encode({code=10001,msg = "新建模板失败,片尾资源不足！"})
    end


    return true,nil
end
--检查模板是否正常
s.resp.check_template_isRight = function(info)
 
    local isok  = s.resp.checkNumsByType({
    duration = 0,
    type = 1,
    limit = 1,
    user_id = info.user_id})

    if not isok then
        return false,cjson.encode({code=10001,msg = "新建模板失败,片头资源不足！"})
    end

    isok  = s.resp.checkNumsByType({
        duration = 0,
        type = 3,
        limit = 1,
        user_id = info.user_id}
    )

    if not isok then
        return false,cjson.encode({code=10001,msg = "新建模板失败,片尾资源不足"})
    end


    local subtitles_audio_duration = 0

    if  info.template_info ~=nil and info.template_info~="" then
       local  template_info = cjson.decode(info.template_info)
       if  template_info.subtitles_audio_duration~=nil then
         subtitles_audio_duration = tonumber(template_info.subtitles_audio_duration)
       end
    end
    
    local duration =  info.video_crop_time + 5

    local limit = math.floor(subtitles_audio_duration/duration)
    skynet.error("limit",limit)
    skynet.error("subtitles_audio_duration",subtitles_audio_duration)
    skynet.error("duration",duration)
    isok  = s.resp.checkNumsByType({
        duration = duration,
        type = 2,
        limit =limit+1 ,
        user_id = info.user_id}
    )

    if not isok then
        return false,cjson.encode({code=10001,msg = "正片资源不足"})
    end


    isok  = s.resp.checkNumsByType({
        duration = 0,
        type = 4,
        limit = 1,
        user_id = info.user_id}
    )

    if not isok then
        return false,cjson.encode({code=10001,msg = "BGM音效资源不足！"})
    end

    return true,nil
end

--接收到删除引用的文件列表信息
s.resp.recv_ref_file_list_del_info = function(msg_body)
    skynet.error("接收到删除引用的文件列表信息")
    local listToDelete = {}
    for i = 1, #msg_body.file_id_list, 1 do
        table.insert(listToDelete,i,msg_body.file_id_list[i])
    end
    local deleteString = table.concat(listToDelete, ',')

    -- local sql = string.format("UPDATE res_list_tab SET is_delete = %d WHERE ref_id IN (%s)",1,deleteString)
    -- db:query(sql)
    local delete_sql = string.format("DELETE FROM res_list_tab WHERE ref_id IN (%s)", deleteString)
    db:query(delete_sql)
    skynet.error(delete_sql)
    return true
end

--检测引用的文件夹是否有自己上传的文件
s.resp.check_ref_folder_have_self_file = function(msg_body)
    local check_user_folder = function(info) --检测用户的文件夹
        local user_id = info.user_id
        local isHave = false
        local folder_id = info.id
        local sql = string.format("select * from res_list_tab where  folder_id = %d  AND is_delete = 0 AND user_id = %d ",folder_id,user_id)
        local res = db:query(sql)
        local delete_tab = {}
        -- 判断是否找到数据
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            local count = 1
            for i = 1, #tab, 1 do
                if tab[i].ref_id==0 then
                    isHave = true
                else
                    table.insert(delete_tab,count,tab[i])
                    count = count+1
                end
            end
            return isHave,delete_tab,info
        else
            return isHave,delete_tab,info
        end
    end

    local sql = ""
    local folder_id = msg_body.folder_id
    --获取当前引用的文件夹
    sql = string.format("select * from folder_list_tab where  ref_id = %d ",folder_id)
    local res = db:query(sql)
    local tab_list = {}
    if res and #res > 0 then
        local tab =  {}
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab = tools.getDbResData(res)
        end
        for i = 1, #tab, 1 do
            local isHave,delete_tab,info = check_user_folder(tab[i])
            table.insert(tab_list,i,{isHave = isHave,delete_tab=delete_tab,info=info})
        end
        return tab_list
    else
        return tab_list
    end


   
end

--接收到删除引用的文件夹列表信息
s.resp.recv_ref_folder_list_del_info = function(msg_body)
    local delete_file_list = function(delete_tab)
        local listToDelete = {}
        for i = 1, #delete_tab, 1 do
            table.insert(listToDelete,i,delete_tab[i].ref_id)
        end
        s.resp.recv_ref_file_list_del_info({file_id_list=listToDelete})
    end
    -- skynet.error("接收到删除引用的文件夹列表信息")
    -- tools.dump(msg_body)
    for i = 1, #msg_body.folder_id_list, 1 do
        local folder_id = msg_body.folder_id_list[i]
        local  list =  s.resp.check_ref_folder_have_self_file({folder_id=folder_id})
        for i = 1, #list, 1 do
            tools.dump(list[i])
            delete_file_list(list[i].delete_tab)
            local isHave = list[i].isHave
            local folder_info = list[i].info
            if isHave then --有自定义的文件不删除这个文件夹，但是要将其引用设置为0
                skynet.error("有自定义的文件不删除这个文件夹，但是要将其引用设置为0")
                local sql = string.format("UPDATE folder_list_tab SET ref_id = %d WHERE id = %d AND user_id = %d",0,folder_info.id,folder_info.user_id)
                db:query(sql)        
                skynet.error(sql)
            else --没有自己上传的文件夹，将其文件夹删除
                skynet.error("没有自己上传的文件夹，将其文件夹删除")
                local sql = string.format("DELETE FROM folder_list_tab WHERE id  = %d AND user_id = %d ",folder_info.id,folder_info.user_id)
                db:query(sql)
                skynet.error(sql)
            end
        end
    end
    return true
end

--接收到修改引用的文件信息
s.resp.recv_ref_file_modify_info = function(msg_body)
    local file_id = msg_body.file_id
    local name = msg_body.name
    local sql = string.format("UPDATE res_list_tab SET folder_name ='%s' WHERE ref_id = %d",name,file_id)
    db:query(sql)
    return true
end

s.resp.update_generate_video_custom = function(msg_body)
    local list = msg_body.data
    skynet.fork(function()
        for i = 1, #list, 1 do
            local video_id =  list[i].video_id
            local custom = "{}"
            if list[i].custom~=nil and list[i].custom~="" then
                 custom = list[i].custom
            end
            local sql = string.format("UPDATE generate_video_list_tab SET custom = '%s' WHERE id = %d and user_id = %d",custom,video_id,msg_body.user_id)
            db:query(sql)
            -- skynet.error(sql)
         end
    end)
    -- skynet.error(sql)
    return true
end
--接收到修改引用的文件夹信息
s.resp.recv_ref_folder_modify_info = function(msg_body)
    local folder_id = msg_body.folder_id
    local name = msg_body.name
    local sql = string.format("UPDATE folder_list_tab SET folder_name ='%s' WHERE ref_id = %d",name,folder_id)
    db:query(sql)
    return true
end

--获取用户所有的文件数
s.resp.get_user_folder_list_nums = function(msg_body)
    local sql = string.format("select COUNT(user_id) AS nums from folder_list_tab where  user_id = %d ",msg_body.user_id)
    local res = db:query(sql)
    local count = 0
    if res and #res > 0 then
        count = res.nums
    end
    return count
end

--获取用户指定类型的数量
s.resp.get_user_file_count_by_type = function(msg_body)
    local sql = string.format("select COUNT(user_id) AS nums from res_list_tab where  user_id = %d  AND file_type = %d",msg_body.user_id,msg_body.file_type)
    local res = db:query(sql)
    local count = 0
    if res and #res > 0 then
        count = res.nums
    end
    return count
end

s.resp.check_file_name_list_is_repeat_by_folder_id = function(msg_body,fd,call_back)
    skynet.fork(function()
        local nameString  = ""
        for i = 1, #msg_body.file_name_list, 1 do
            nameString = nameString.."'"..msg_body.file_name_list[i].."'"
            if i<#msg_body.file_name_list then
                nameString = nameString..","
            end
        end
        local sql = string.format("SELECT file_name FROM res_list_tab WHERE file_name IN (%s) AND folder_id = %d AND user_id = %d ", 
        nameString,
        msg_body.folder_id,
        msg_body.user_id)
        local res = db:query(sql)
        local tab =  {}
        -- skynet.error("sql",sql)
        if #res == 1 then
            tab[1] = tools.getDbResData(res)
        else
            tab = tools.getDbResData(res)
        end
        -- tools.dump(tab)
        local normal_file_name_list ={}
        local repeat_file_name_list ={}
        for i = 1, #tab, 1 do
            table.insert(repeat_file_name_list,i,tab[i].file_name)
        end

        function check(file_name,list)
            for i = 1, #list, 1 do
               if list[i]==file_name then
                    return false
               end 
            end
            return true
        end
        local count = 1
        for i = 1, #msg_body.file_name_list, 1 do
            if check(msg_body.file_name_list[i],repeat_file_name_list) then
                table.insert(normal_file_name_list,count,msg_body.file_name_list[i])
                count = count+1
            end 
         end
         if call_back~=nil then
            call_back({normal_file_name_list=normal_file_name_list,repeat_file_name_list=repeat_file_name_list})
         else
            tools.response_db_check_folder_repeat_file(fd,msg_body,true,{normal_file_name_list=normal_file_name_list,repeat_file_name_list=repeat_file_name_list})
         end
       
    end)
    return true
end


--获取用户的分享用户列表
s.resp.get_share_user_list  = function(msg_body,fd)
    local isOk,user_data = s.resp.getUserById(msg_body.user_id)
    local temp = {}
    if isOk then
        local select_share =  nil
        if user_data.user_info~=nil and user_data.user_info~="" then
            local info = cjson.decode(user_data.user_info)
            select_share = info.select_share
        end
        local count = 1
        skynet.fork(function()
            function action()
                tools.response_db_get_share_user_list(fd,msg_body,true,temp)
            end
            if select_share~=nil and select_share~="" then
                for i = 1, #select_share, 1 do
                    local user_id = select_share[i]
                    local _,ud = s.resp.getUserById(user_id)
                    if _ then
                        if ud.id~=msg_body.user_id then
                            table.insert(temp,count,ud)
                            count = count +1
                        end
                    end
                end
            end
  
            if user_data.permit_id == 2 then  --说明是管理
                s.resp.get_group_list(user_data.group_id,function(list)
                    -- skynet.error("user_data.group_id",user_data.group_id)
                    -- tools.dump(list)
                    for i = 1, #list, 1 do
                        if list[i].permit_id~=2 and list[i].id~=user_data.id then
                            table.insert(temp,count,list[i])
                            count = count +1
                        end
                    end
                    action()
                end)
            else
                action()
            end

        end)
        return true
    end
    return false
end
--根据名字获取文件夹
s.resp.get_folder_by_name = function(msg_body,fd)
    local sql = string.format("SELECT * FROM folder_list_tab WHERE folder_name = '%s' AND classification_id = %d AND folder_type = %d AND user_id = %d LIMit 1", msg_body.folder_name,msg_body.classification_id,msg_body.folder_type,msg_body.user_id)
    local res = db:query(sql)
    local tab =  {}
    tab = tools.getDbResData(res)
    if #res <=0 then
        return false,nil
    end
    return true,tab
end

s.resp.get_folder_have_file_name = function(user_id,folder_id,file_name)
    local sql = string.format("SELECT * FROM res_list_tab WHERE file_name = '%s' AND user_id = %d AND folder_id = %d LIMit 1", file_name,user_id,folder_id)
    local res = db:query(sql)
    local tab =  {}
    tab = tools.getDbResData(res)
    skynet.error("#res",#res)
    if #res <=0 then
        return false,nil
    end
    return true,tab
end
--分享文件列表
s.resp.share_file_list = function(msg_body,fd)
    function insert_target(file,folder_id,target_user_id)
        local is_have,_ = s.resp.get_folder_have_file_name(target_user_id,folder_id,file.file_name)

        if is_have==true then

        else
            local sql = string.format("INSERT INTO res_list_tab (user_id, folder_id, file_type, file_name,file_info,duration,classification_id,ref_id) VALUES (%d, %d, %d, '%s','%s',%f,%d,%d)",
            target_user_id,
            folder_id,
            file.file_type,
            file.file_name,
            file.file_info,
            file.duration,
            file.classification_id,
            file.id)
            skynet.error("sql",sql)
            db:query(sql)
        end
        -- skynet.error(sql)
    end
    for j = 1, #msg_body.target_user_id_list, 1 do
        local target_user_id = msg_body.target_user_id_list[j]
        local my_user_id = msg_body.user_id
        skynet.fork(function()
            for i = 1, #msg_body.file_id_list, 1 do
                local file_id = msg_body.file_id_list[i].file_id
                local folder_name = msg_body.file_id_list[i].folder_name
                local sql = string.format("SELECT * FROM res_list_tab WHERE id = %d limit 1", file_id)
                local res = db:query(sql)
                local tab = tools.getDbResData(res)
                if #res>0 then
                    --检测被分享的是否拥有此文件夹
                    local isOk,folder_data = s.resp.get_folder_by_name({user_id=target_user_id,folder_name=folder_name,classification_id=tab.classification_id,folder_type=tab.file_type})
                    if isOk then --拥有此文件夹，直接插入
                        skynet.error("拥有此文件夹，直接插入",folder_name)
                        insert_target(tab,folder_data.id,target_user_id)
                    else -- 没有此文件夹，创建一个再插入  
                        local new_sql = string.format("INSERT INTO folder_list_tab (user_id,folder_name, folder_type,is_public,classification_id) VALUES (%d, '%s',%d,%d,%d)",
                        target_user_id,
                        folder_name,
                        tab.file_type
                        ,0
                        ,tab.classification_id)
                        -- skynet.error(sql)
                        local new_res = db:query(new_sql)
                        new_sql = string.format("select *from folder_list_tab where user_id = %d AND folder_name = '%s' AND folder_type = %d AND  classification_id = %d limit 1",
                        target_user_id,
                        folder_name,
                        tab.file_type,
                        tab.classification_id)
                        new_res = db:query(new_sql)
                        if new_res and #new_res > 0 then
                            local folder_info =  {}
                            folder_info = tools.getDbResData(new_res)
                            insert_target(tab,folder_info.id,target_user_id)
                        end
                    end 
                end
        
            end
        end)
    end
    
    return true
end
--分享文件夹列表    
s.resp.share_folder_list = function(msg_body,fd)

    function getInfoByName(name,list)
        for i = 1, #list, 1 do
            if list[i].file_name == name then
                return list[i]
            end
        end
        return nil
    end
    function insert_target_folder(target_folder_id,my_folder_id,my_user_id,target_user_id)
        local  isok = s.resp.folder_res_list({
            folder_id = my_folder_id,
            user_id =my_user_id,
            is_public=0},nil,function(list)
                skynet.error("insert_target_folder",#list)
                if #list>0 then
                    local file_name_list = {}
                    for i = 1, #list, 1 do
                        local _f = list[i]
                        table.insert(file_name_list,i,_f.file_name)
                    end
                    s.resp.check_file_name_list_is_repeat_by_folder_id({file_name_list=file_name_list,user_id=target_user_id,folder_id=target_folder_id},fd,function(data)
                        for i = 1, #data.normal_file_name_list, 1 do
                            local file = getInfoByName(data.normal_file_name_list[i],list)
                            if file~=nil then
                                local sql = string.format("INSERT INTO res_list_tab (user_id, folder_id, file_type, file_name,file_info,duration,classification_id,ref_id) VALUES (%d, %d, %d, '%s','%s',%f,%d,%d)",
                                target_user_id,
                                target_folder_id,
                                file.file_type,
                                file.file_name,
                                file.file_info,
                                file.duration,
                                file.classification_id,
                                file.id)
                                db:query(sql)
                            end
                        end
                    end)
                end
        end)
    end
    for j = 1, #msg_body.target_user_id_list, 1 do
        local target_user_id = msg_body.target_user_id_list[j]
        local my_user_id = msg_body.user_id

        skynet.fork(function()
            for i = 1, #msg_body.folder_id_list, 1 do
                local folder_id = msg_body.folder_id_list[i].folder_id
                local folder_name = msg_body.folder_id_list[i].folder_name
                local sql = string.format("SELECT * FROM folder_list_tab WHERE id = %d limit 1", folder_id)
                local res = db:query(sql)
                local tab = tools.getDbResData(res)
                if #res>0 then
                    --检测被分享的是否拥有此文件夹
                    local isOk,folder_data = s.resp.get_folder_by_name({user_id=target_user_id,folder_name=folder_name,classification_id=tab.classification_id,folder_type=tab.folder_type})
                    if isOk then --拥有此文件夹，直接插入
                        skynet.error("拥有此文件夹，直接插入 ")
                        insert_target_folder(folder_data.id,folder_id,my_user_id,target_user_id)
                    else -- 没有此文件夹，创建一个再插入  
                        local new_sql = string.format("INSERT INTO folder_list_tab (user_id,folder_name, folder_type,is_public,classification_id) VALUES (%d, '%s',%d,%d,%d)",
                        target_user_id,
                        folder_name,
                        tab.folder_type
                        ,0
                        ,tab.classification_id)
                        skynet.error("没有此文件夹，创建一个再插入 ")
                        skynet.error(sql)
                        db:query(new_sql)
        
                        new_sql = string.format("select *from folder_list_tab where user_id = %d AND folder_name = '%s' AND folder_type = %d AND  classification_id = %d limit 1",
                        target_user_id,
                        folder_name,
                        tab.folder_type,
                        tab.classification_id)
                        local new_res = db:query(new_sql)
                        if new_res and #new_res > 0 then
                            local folder_info =  {}
                            folder_info = tools.getDbResData(new_res)
                            insert_target_folder(folder_info.id,folder_id,my_user_id,target_user_id)
                        end
                    end 
                end
        
            end
        end)
    end
    return true
end

s.resp.get_folder_info = function(msg_body,fd)
    local listToFind= {}
    for i = 1, #msg_body.folder_id_list, 1 do
        table.insert(listToFind,i,msg_body.folder_id_list[i])
    end
    local findString = table.concat(listToFind, ',')
    skynet.fork(function()
        local sql = string.format("SELECT * FROM folder_list_tab WHERE id IN (%s) AND user_id = %d ", findString,msg_body.user_id)
        local res = db:query(sql)
        if res and #res > 0 then
            local tab =  {}
            if #res == 1 then
                tab[1] = tools.getDbResData(res)
            else
                tab = tools.getDbResData(res)
            end
            tools.response_db_get_folder_info(fd,msg_body,true,tab)
        else
            tools.response_db_get_folder_info(fd,msg_body,false,{})
        end
    end)
end
s.init = function()
    db=mysql.connect({
		host=runconfig.db_tost,
		port=runconfig.db_port,
		database=runconfig.db_name,
		user="root",
		password=runconfig.db_pw,
		max_packet_size = 1024 * 1024,
		on_connect = nil
    })
    if not db then
        skynet.error("failed to connect")
        skynet.exit()
    else
        skynet.error("success to connect to mysql server")    
    end
    --设置utf8字符集
    local res = db:query("set charset utf8");
end


s.start(...)