<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.rongwei.bscommon.sys.dao.FsFileDataListDao">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.bsentity.domain.FsFileDataListDo">
        <id column="ID" property="id" />
        <result column="TENANTID" property="tenantid" />
        <result column="ROPTION" property="roption" />
        <result column="DELETED" property="deleted" />
        <result column="REMARK" property="remark" />
        <result column="CREATEDATE" property="createdate" />
        <result column="CREATEUSERID" property="createuserid" />
        <result column="CREATEUSERNAME" property="createusername" />
        <result column="MODIFYDATE" property="modifydate" />
        <result column="MODIFYUSERID" property="modifyuserid" />
        <result column="MODIFYUSERNAME" property="modifyusername" />
        <result column="PROCESSINSTID" property="processinstid" />
        <result column="PROCESSINSTSTATUS" property="processinststatus" />
        <result column="DOCUMENTTREEID" property="documenttreeid" />
        <result column="FILEENCODING" property="fileencoding" />
        <result column="SERIALNUMBER" property="serialnumber" />
        <result column="FILENAME" property="filename" />
        <result column="CHANGENUMBER" property="changenumber" />
        <result column="VARIETY" property="variety" />
        <result column="DEPARTMENT" property="department" />
        <result column="CATEGORY" property="category" />
        <result column="CURRENTSTATE" property="currentstate" />
        <result column="SPECIFICATION" property="specification" />
        <result column="TEMPORARYFILE" property="temporaryfile" />
        <result column="EXPIRATIONTIME" property="expirationtime" />
        <result column="FILEPDF" property="filepdf" />
        <result column="FILEWORD" property="fileword" />
        <result column="AUDITORID" property="auditorid" />
        <result column="AUDITOR" property="auditor" />
        <result column="APPROVERID" property="approverid" />
        <result column="APPROVER" property="approver" />
        <result column="CHANGECATEGORY" property="changecategory" />
        <result column="ALTERCAUSE" property="altercause" />
        <result column="BEFORECHANGE" property="beforechange" />
        <result column="AFTERCHANGE" property="afterchange" />
        <result column="RELEVANTDOCUMENT" property="relevantdocument" />
        <result column="CANCELLATION" property="cancellation" />
        <result column="CANCELLATIONCAUSE" property="cancellationcause" />
        <result column="REVIEW" property="review" />
        <result column="ISEHS" property="isehs" />
        <result column="FILESTATE" property="filestate" />
        <result column="DEPTNAME" property="deptName" />
        <result column="DEPTID" property="deptId" />
        <result column="FILETCATEGORY" property="filetCateGory" />
    </resultMap>


    <select id="getFileIds" resultType="com.bsentity.dto.FileIdDto">
        <if test="dealWithFileVO.id != null and dealWithFileVO.id != ''">
            SELECT
                ID AS formId, FILEWORD as wordId , FILEPDF as pdfId , 'master' as flag , WATERMARK ,WATERMARKDATE
            FROM
                fs_file_data_list
            <where>
                DELETED = '0'
                AND FILEPDF IS NOT NULL
                <if test="dealWithFileVO.id != null and dealWithFileVO.id != ''">
                    AND ID = #{dealWithFileVO.id}
                </if>
            </where>
            GROUP BY ID
        </if>
        <if test="dealWithFileVO.id != null and dealWithFileVO.id != '' and dealWithFileVO.historyFileId != null and dealWithFileVO.historyFileId != ''">
            UNION ALL
        </if>
        <if test="dealWithFileVO.historyFileId != null and dealWithFileVO.historyFileId != ''">
            SELECT
            ID , FILEWORD as wordId , FILEPDF as pdfId , 'history' as flag ,WATERMARK ,WATERMARKDATE
            FROM
            fs_file_data_list_record
            <where>
                DELETED = '0'
                AND FILEPDF IS NOT NULL
                <if test="dealWithFileVO.historyFileId != null and dealWithFileVO.historyFileId != ''">
                    AND ID = #{dealWithFileVO.historyFileId}
                </if>
            </where>
            GROUP BY ID
        </if>
    </select>

    <select id="getFilePath" resultType="com.bsentity.dto.FileDto">
        SELECT
        FILETYPE , FULLPATH AS fileFullPath ,ID AS fileId
        FROM
        sys_file_item
        <where>
            DELETED = '0'
            <if test="fileIds != null and fileIds.size>0">
                AND ID  IN
                <foreach collection="fileIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        GROUP BY ID
        ORDER BY FILETYPE
    </select>
    <select id="getTemporaryFilePath" resultType="com.bsentity.dto.FileIdDto">
        SELECT
            ID , FILEWORD as wordId ,  FILEPDF as pdfId
        FROM
            fs_file_data_list
        WHERE
            DELETED = '0'
            AND FILEWORD IS NOT NULL
            AND FILEPDF IS NOT NULL
            AND TEMPORARYFILE  = '0'
            AND FILESTATE = '1'
            AND CANCELLATION = '0'
            AND DATE_FORMAT(EXPIRATIONTIME,'%Y-%m-%d') &lt; DATE_FORMAT(NOW(),'%Y-%m-%d')
    </select>
    <select id="getDict" resultType="com.bsentity.dto.FileFloorDto">
        SELECT
            a.`NAME` as FNAME ,
            a.`VALUE` as FVALUE,
            IF (c.`NAME` IS NOT NULL , CONCAT(a.`NAME`,"\\",b.`NAME`)  , b.`NAME`)   AS PNAME ,
            b.`VALUE` AS PVALUE ,
            c.`NAME` AS `NAME` ,
            c.`VALUE`
        FROM
        sys_dict a
        inner JOIN sys_dict b ON a.ID = b.PID
        LEFT JOIN sys_dict c ON b.ID = c.PID
        WHERE
        a.DICTTYPE = 'VARIETY'
        AND a.`VALUE` 	&lt;&gt; 'VARIETY'
        AND a.PID = '514f742bca2446c48077e20fb0f45e56'
        ORDER BY a.SORT , b.SORT , c.SORT asc

    </select>
    <select id="getDictList" resultType="com.rongwei.rwadmincommon.system.domain.SysDictDo">
        SELECT
        *
        FROM (
        SELECT * FROM sys_dict WHERE  DICTTYPE = 'VARIETY' AND PID = "-1"
        UNION ALL
        SELECT * FROM sys_dict WHERE  DICTTYPE = 'VARIETY' AND `VALUE` &lt;&gt; 'VARIETY' AND PID = '514f742bca2446c48077e20fb0f45e56'
        UNION ALL
        SELECT b.* FROM sys_dict a INNER JOIN sys_dict b ON a.ID = b.PID WHERE  a.DICTTYPE = 'VARIETY' AND a.`VALUE` &lt;&gt; 'VARIETY' AND a.PID = '514f742bca2446c48077e20fb0f45e56' GROUP BY b.ID
        UNION ALL
        SELECT
        c.*
        FROM
        sys_dict a
        INNER JOIN sys_dict b ON a.ID = b.PID
        LEFT JOIN sys_dict c ON b.ID = c.PID
        WHERE
        a.DICTTYPE = 'VARIETY'
        AND a.`VALUE` &lt;&gt; 'VARIETY'
        AND a.PID = '514f742bca2446c48077e20fb0f45e56'
        AND c.`NAME` is not NULL  and c.`VALUE` is NOT NULL

        ) d
    </select>
</mapper>
