<?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="org.springblade.system.mapper.FileManageMapper">
	<insert id="saveFileManage" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO record_da_ky_pro_files_contents
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id != null and id != ''">id,</if>
			<if test="proId != null and proId != ''">pro_id,</if>
			<if test="contentName != null and contentName != ''">content_name,</if>
			<if test="parentId != null and parentId != ''">parent_id,</if>
			<if test="level != null and level != ''">`level`,</if>
			<if test="sort != null">sort,</if>
			<if test="markCode != null and markCode != ''">mark_code,</if>
			<if test="term != null and term != ''">term,</if>
			<if test="major != null and major != ''">major,</if>
			<if test="classifyMark != null and classifyMark != ''">classify_mark,</if>
			<if test="unitName != null and unitName != ''">unit_name,</if>
			<if test="dataSource != null and dataSource != ''">data_source,</if>
			<if test="isUnique != null and isUnique != ''">is_unique,</if>
			<if test="integrityStatus != null and integrityStatus != ''">integrity_status,</if>
			<if test="createTime != null">create_time,</if>
			<if test="updateTime != null">update_time,</if>
			<if test="createUserId != null">create_user_id,</if>
			<if test="createUserName != null and createUserName != ''">create_user_name,</if>
			<if test="updateUserId != null">update_user_id,</if>
			<if test="updateUserName != null and updateUserName != ''">update_user_name,</if>
		</trim>
		VALUES
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id != null and id != ''">#{id},</if>
			<if test="proId != null and proId != ''">#{proId},</if>
			<if test="contentName != null and contentName != ''">#{contentName},</if>
			<if test="parentId != null and parentId != ''">#{parentId},</if>
			<if test="level != null and level != ''">#{level},</if>
			<if test="sort != null">#{sort},</if>
			<if test="markCode != null and markCode != ''">#{markCode},</if>
			<if test="term != null and term != ''">#{term},</if>
			<if test="major != null and major != ''">#{major},</if>
			<if test="classifyMark != null and classifyMark != ''">#{classifyMark},</if>
			<if test="unitName != null and unitName != ''">#{unitName},</if>
			<if test="dataSource != null and dataSource != ''">#{dataSource},</if>
			<if test="isUnique != null and isUnique != ''">#{isUnique},</if>
			<if test="integrityStatus != null and integrityStatus != ''">#{integrityStatus},</if>
			<if test="createTime != null">#{createTime},</if>
			<if test="updateTime != null">#{updateTime},</if>
			<if test="createUserId != null">#{createUserId},</if>
			<if test="createUserName != null and createUserName != ''">#{createUserName},</if>
			<if test="updateUserId != null">#{updateUserId},</if>
			<if test="updateUserName != null and updateUserName != ''">#{updateUserName},</if>
		</trim>
	</insert>
	<insert id="saveFileSource" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO da_ky_sys_file_source
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="businessId != null and businessId != ''">business_id,</if>
			<if test="filePath != null and filePath != ''">file_path,</if>
			<if test="sourceFileName != null and sourceFileName != ''">source_file_name,</if>
			<if test="fileName != null and fileName != ''">file_name,</if>
			<if test="fileType != null and fileType != ''">file_type,</if>
			<if test="suffixName != null and suffixName != ''">suffix_name,</if>
			<if test="fileSize != null and fileSize != ''">file_size,</if>
			<if test="sourceType != null and sourceType != ''">source_type,</if>
			<if test="createUserId != null">create_user_id,</if>
			<if test="createUserName != null and createUserName != ''">create_user_name,</if>
			create_time
		</trim>
		VALUES
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="businessId != null and businessId != ''">#{businessId},</if>
			<if test="filePath != null and filePath != ''">#{filePath},</if>
			<if test="sourceFileName != null and sourceFileName != ''">#{sourceFileName},</if>
			<if test="fileName != null and fileName != ''">#{fileName},</if>
			<if test="fileType != null and fileType != ''">#{fileType},</if>
			<if test="suffixName != null and suffixName != ''">#{suffixName},</if>
			<if test="fileSize != null and fileSize != ''">#{fileSize},</if>
			<if test="sourceType != null and sourceType != ''">#{sourceType},</if>
			<if test="createUserId != null">#{createUserId},</if>
			<if test="createUserName != null and createUserName != ''">#{createUserName},</if>
			now()
		</trim>
	</insert>
    <insert id="addRectification">
        INSERT INTO record_rectification_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fileId != null">
                file_id,
            </if>
            <if test="proId != null">
                pro_id,
            </if>
            <if test="description != null and description != ''">
                description,
            </if>
            <if test="createUserId != null">
                create_user_id,
            </if>
            <if test="issueTime != null and issueTime != ''">
                issue_time,
            </if>
            <if test="rectifyStatus != null and rectifyStatus != ''">
                rectify_status,
            </if>
            <if test="issueUser != null and issueUser != ''">
                issue_user,
            </if>
            create_time,
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="fileId != null">
                #{fileId},
            </if>
            <if test="proId != null">
                #{proId},
            </if>
            <if test="description != null and description != ''">
                #{description},
            </if>
            <if test="createUserId != null">
                #{createUserId},
            </if>
            <if test="issueTime != null and issueTime != ''">
                #{issueTime},
            </if>
            <if test="rectifyStatus != null and rectifyStatus != ''">
                #{rectifyStatus},
            </if>
            <if test="issueUser != null and issueUser != ''">
                #{issueUser},
            </if>
            NOW(),
        </trim>
    </insert>
    <insert id="insertRecordTransferAudit">
        insert into record_transfer_audit (pro_id,audit_dept,audit_status,create_time)
        values (#{proId},#{deptId}, 0, now())
    </insert>
    <update id="updateFileManage">
		UPDATE record_da_ky_pro_files_contents
		<set>
			<if test="contentName != null and contentName != ''">content_name = #{contentName},</if>
			<if test="parentId != null and parentId != ''">parent_id = #{parentId},</if>
			<if test="level != null and level != ''">`level` = #{level},</if>
			<if test="sort != null">sort = #{sort},</if>
			<if test="markCode != null and markCode != ''">mark_code = #{markCode},</if>
			<if test="term != null and term != ''">term = #{term},</if>
			<if test="major != null and major != ''">major = #{major},</if>
			<if test="classifyMark != null and classifyMark != ''">classify_mark = #{classifyMark},</if>
			<if test="unitName != null and unitName != ''">unit_name = #{unitName},</if>
			<if test="dataSource != null and dataSource != ''">data_source = #{dataSource},</if>
			<if test="isUnique != null and isUnique != ''">is_unique = #{isUnique},</if>
			<if test="integrityStatus != null and integrityStatus != ''">integrity_status = #{integrityStatus},</if>
			<if test="updateUserId != null">update_user_id = #{updateUserId},</if>
			<if test="updateUserName != null and updateUserName != ''">update_user_name = #{updateUserName},</if>
		    update_time = now()
		</set>
		WHERE id = #{id} and pro_id = #{proId}
	</update>
	<update id="updateFileSource">
		UPDATE da_ky_sys_file_source
		set
			file_path = #{filePath},
			source_file_name = #{sourceFileName},
			file_name = #{fileName},
			file_type = #{fileType},
			suffix_name = #{suffixName},
			file_size = #{fileSize},
			source_type = #{sourceType},
			update_user_id = #{updateUserId},
			update_user_name = #{updateUserName},
			update_time = now()
		WHERE business_id = #{id}
	</update>
	<update id="updateIntegrityStatus">
		UPDATE record_da_ky_pro_files_contents
		set
			integrity_status = '1'
		WHERE pro_id = #{proId}
	</update>
    <update id="updateisRectification">
        UPDATE da_ky_sys_file_source
        set
            is_rectification = #{isRectification}
        WHERE id = #{fileId}
    </update>
    <update id="updateRectification">
        UPDATE record_rectification_list
        set
            description = #{description},
            update_user_id = #{updateUserId},
            update_time = now()
        WHERE id = #{id}
    </update>
    <update id="issue">
        UPDATE record_rectification_list
        set
            is_issue = #{isIssue},
            issue_time = now(),
            issue_user = #{issueUser},
            rectify_status = '0'
        WHERE id = #{id}
    </update>
    <update id="agreeRectification">
        update da_ky_project set file_status = '2' where id = #{proId}
    </update>
    <update id="updateTransferAudit">
        UPDATE record_transfer_audit
        set
            audit_status = #{auditStatus}
        WHERE pro_id = #{proId} and audit_dept = #{auditDept}
    </update>
    <delete id="delFileSource">
		DELETE FROM da_ky_sys_file_source
		WHERE business_id = #{id}
	</delete>
	<delete id="delFileManage">
		DELETE FROM record_da_ky_pro_files_contents
		WHERE id = #{id}
	</delete>
    <delete id="delRectification">
        DELETE FROM record_rectification_list
        WHERE id = #{id}
    </delete>

    <select id="list" resultType="org.springblade.system.domain.DaKyProFilesContentsDto">
		SELECT
		dkp.*,
		COALESCE(dkp2.content_name, dkp.content_name) AS parentName
		FROM
        record_da_ky_pro_files_contents dkp
		LEFT JOIN record_da_ky_pro_files_contents dkp2
		ON dkp.parent_id = dkp2.id
		AND dkp2.del_flag = '1'
		AND dkp2.pro_id = #{proId}
		WHERE
		dkp.del_flag = '1'
		AND dkp.pro_id = #{proId}
		AND dkp.level IN (0,1,2,3,4)
		<if test="keyWord != null and keyWord != ''">
			AND INSTR(dkp.content_name, #{keyWord}) > 0
		</if>
		ORDER BY
		dkp.`level`, dkp.sort
	</select>
	<select id="FileManage" resultType="org.springblade.system.domain.DaKyProFilesContentsDto">
		SELECT
		dkpfc.id AS id,
		dkpfc.pro_id AS proId,
		dkpfc.content_name AS contentName,
		dkpfc.parent_id AS parentId,
		dkpfc.level AS level,
		dkpfc.sort AS sort,
		dkpfc.mark_code AS markCode,
		dkpfc.term AS term,
        bd.dept_name AS unitName,
		case when dkpfc.data_source = '1' then '本系统上传'
		when dkpfc.data_source = '2' then '智慧现场'
		else '' end AS dataSource,
		dkpfc.is_unique AS isUnique,
		dkpfc.integrity_status AS integrityStatus,
		dkfs.id AS fileId,
        dkfs.is_rectification AS isRectification,
		dkfs.file_name AS fileName,
		dkfs.create_time AS createTime,
		dkfs.source_file_name AS  sourceFileName,
		dkfs.create_user_name AS createUserName
		FROM
        record_da_ky_pro_files_contents dkpfc
		left join da_ky_sys_file_source dkfs on dkpfc.id = dkfs.business_id
		left join blade_dept bd on dkpfc.unit_name = bd.id
		WHERE
		dkpfc.del_flag = '1' and dkpfc.parent_id = #{parentId} and dkpfc.pro_id = #{proId} and dkpfc.level = 5
		<if test="contentName != null and contentName != ''">
			AND INSTR(dkpfc.content_name, #{contentName}) > 0
		</if>
		<if test="uploadTime != null and uploadTime != ''">
			and DATE(dkfs.create_time) = #{uploadTime}
		</if>
		order by dkpfc.create_time desc
	</select>
	<select id="selectFileManage" resultType="java.lang.Integer">
		SELECT
			count(*)
		FROM
        record_da_ky_pro_files_contents
		WHERE content_name = #{contentName}
		  and parent_id = #{parentId}
		  and del_flag = '1'
		<if test="id != null">
			and id != #{id}
		</if>
	</select>

	<select id="getMaxSort" resultType="java.lang.Integer">
		SELECT
			max(dkpfc.sort)
		FROM
            record_da_ky_pro_files_contents dkpfc
		WHERE
			dkpfc.del_flag = '1' and dkpfc.parent_id = #{parentId}
	</select>

	<select id="getLevelById" resultType="java.lang.Integer">
		SELECT
			dkp.level
		FROM
            record_da_ky_pro_files_contents dkp
		WHERE
			dkp.id = #{parentId} and dkp.pro_id = #{proId}
	</select>
	<select id="getSortById" resultType="java.lang.Integer">
		SELECT
			dkpfc.sort
		FROM
            record_da_ky_pro_files_contents dkpfc
		WHERE
			dkpfc.id = #{id}
	</select>
	<select id="getFilesClassifyNameStandard" resultType="org.springblade.system.domain.FilesClassifyNameStandardDto">
		SELECT
			dkfcns.standard_name AS standardName,
			dkfcns.standard_type AS standardType
		FROM
            record_da_ky_files_classify_name_standard dkfcns
		WHERE
			dkfcns.del_flag = '1'
	</select>
	<select id="getFileManageById" resultType="org.springblade.system.domain.DaKyProFilesContentsDto">
		SELECT
			dkpfc.*,
			dkfs.id AS fileId,
			dkfs.business_id AS businessId,
			dkfs.file_name AS fileName,
			CASE
				WHEN dkpfc.data_source = '1' THEN
					'本系统上传'
				WHEN dkpfc.data_source = '2' THEN
					'智慧现场' ELSE ''
				END AS dataSource,
			dksdd.dict_label AS markCode,
			dkfcm.classify_mark_name AS classifyMarkName
		FROM
            record_da_ky_pro_files_contents dkpfc
				LEFT JOIN da_ky_sys_dict_data dksdd ON dkpfc.mark_code = dksdd.dict_value
				AND dksdd.dict_type = 'mark_code'
				LEFT JOIN record_da_ky_files_classify_mark dkfcm ON dkpfc.classify_mark = dkfcm.id
				LEFT JOIN da_ky_sys_file_source dkfs ON dkpfc.id = dkfs.business_id
		WHERE
			dkpfc.id = #{id} and dkpfc.pro_id = #{proId}
	</select>
	<select id="getFileById" resultType="org.springblade.system.domain.vo.DaKyProFilesContentsVo">
		SELECT * from  da_ky_sys_file_source where  id = #{id}
	</select>
	<select id="getFilesNum" resultType="java.lang.Integer">
		SELECT
			count(dkfs.id)
		FROM
            record_da_ky_pro_files_contents dkfs
		left join da_ky_pro_files_contents dkpfc on dkfs.business_id = dkpfc.id
		WHERE dkfs.del_flag = '1' and dkpfc.level = 5 and dkpfc.pro_id = #{proId}
	</select>
	<select id="getchild" resultType="java.lang.Integer">
		SELECT
			count(dkpfc.id)
		FROM
            record_da_ky_pro_files_contents dkpfc
		left join record_da_ky_pro_files_contents dkpfc2 on dkpfc.parent_id = dkpfc2.id
		WHERE
			dkpfc.del_flag = '1' and dkpfc2.id = #{id}
	</select>
    <select id="selectRectificationList" resultType="org.springblade.system.domain.RectificationDto">
        SELECT
            rrl.id,
            rrl.file_id AS fileId,
            dkp.pro_name AS proName,
            dkp.single_pro_name AS singleProName,
            concat( dkpfc4.content_name, '/', dkpfc3.content_name, '/', dkpfc2.content_name, '/', dkpfc.content_name, '/', dksfs.file_name ) AS contentName,
            rrl.description,
            rrl.is_issue AS isIssue
        from record_rectification_list rrl
            left join da_ky_project dkp on rrl.pro_id = dkp.id
            left join da_ky_sys_file_source dksfs on rrl.file_id = dksfs.id
            left join record_da_ky_pro_files_contents dkpfc on dkpfc.id = dksfs.business_id
            left join record_da_ky_pro_files_contents dkpfc2 on dkpfc.parent_id = dkpfc2.id
            left join record_da_ky_pro_files_contents dkpfc3 on dkpfc2.parent_id = dkpfc3.id
            left join record_da_ky_pro_files_contents dkpfc4 on dkpfc3.parent_id = dkpfc4.id
        WHERE rrl.pro_id = #{proId} and dkpfc.pro_id = #{proId} and dkpfc2.pro_id = #{proId} and dkpfc3.pro_id = #{proId} and dkpfc4.pro_id = #{proId}
        <if test="contentName != null and contentName != ''">
            and concat( dkpfc4.content_name, '/', dkpfc3.content_name, '/', dkpfc2.content_name, '/', dkpfc.content_name, '/', dksfs.file_name ) like concat('%', #{contentName}, '%')
        </if>
    </select>
    <select id="getRectificationFile" resultType="java.lang.Integer">
        SELECT
            count(id)
        from record_rectification_list where pro_id = #{proId} and rectify_status != 1
    </select>
    <select id="getdeptIds" resultType="java.lang.String">
        SELECT
            unit_name
        from record_da_ky_pro_files_contents where pro_id = #{proId} and level = 5 and del_flag = '1'
    </select>
    <select id="transferAuditDetail" resultType="org.springblade.system.domain.RectificationDto">
        select bd.dept_name AS deptName,
               rta.audit_status AS auditStatus
        from record_transfer_audit rta
             left join blade_dept bd on rta.audit_dept = bd.id
        where rta.pro_id = #{proId}
    </select>
</mapper>
