<?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.zcloud.mapper.dsno1.hiddenDangerCheckStandard.DictionaryMapper">

	<!--表名 -->
	<sql id="tableName">
		BUS_HIDDENDANGERCHECKSTANDARD_DICTIONARY
	</sql>

	<!--数据字典表名 -->
	<sql id="dicTableName">
		SYS_DICTIONARIES
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		f.DICTIONARY_NAME,
		f.PARENT_ID,
		f.PARENT_IDS,
		f.DICTIONARY_NAMES,
		f.DICTIONARY_LEVEL,
		f.DICTIONARY_SORT,
		f.DICTIONARY_SOURCE,
		f.CORPINFO_ID,
		f.ISDELETE,
		f.CREATOR_ID,
		f.CREATOR_NAME,
		f.CREATE_TIME,
		f.OPERATOR_ID,
		f.OPERATOR_NAME,
		f.OPERATE_TIME,
		f.DELETEOR_ID,
		f.DELETEOR_NAME,
		f.DELETE_TIME,
		f.DICTIONARY_ID
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		DICTIONARY_NAME,
		PARENT_ID,
		PARENT_IDS,
		DICTIONARY_NAMES,
		DICTIONARY_LEVEL,
		DICTIONARY_SORT,
		DICTIONARY_SOURCE,
		CORPINFO_ID,
		ISDELETE,
		CREATOR_ID,
		CREATOR_NAME,
		CREATE_TIME,
		OPERATOR_ID,
		OPERATOR_NAME,
		OPERATE_TIME,
		DELETEOR_ID,
		DELETEOR_NAME,
		DELETE_TIME,
		DICTIONARY_ID
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{DICTIONARY_NAME},
		#{PARENT_ID},
		#{PARENT_IDS},
		#{DICTIONARY_NAMES},
		#{DICTIONARY_LEVEL},
		#{DICTIONARY_SORT},
		#{DICTIONARY_SOURCE},
		#{CORPINFO_ID},
		#{ISDELETE},
		#{CREATOR_ID},
		#{CREATOR_NAME},
		#{CREATE_TIME},
		#{OPERATOR_ID},
		#{OPERATOR_NAME},
		#{OPERATE_TIME},
		#{DELETEOR_ID},
		#{DELETEOR_NAME},
		#{DELETE_TIME},
		#{DICTIONARY_ID}
	</sql>

	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into
	<include refid="tableName"></include>
		(
	<include refid="Field2"></include>
		) values (
	<include refid="FieldValue"></include>
		)
	</insert>

	<!-- 删除-->
	<delete id="delete" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1',
			DELETEOR_ID = #{DELETEOR_ID},
			DELETEOR_NAME = #{DELETEOR_NAME},
			DELETE_TIME = #{DELETE_TIME}
		where
			DICTIONARY_ID = #{DICTIONARY_ID}
	</delete>

	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		<if test="DICTIONARY_NAME != null and DICTIONARY_NAME != ''">
			DICTIONARY_NAME = #{DICTIONARY_NAME},
		</if>
		<if test="PARENT_ID != null and PARENT_ID != ''">
			PARENT_ID = #{PARENT_ID},
		</if>
		<if test="PARENT_IDS != null and PARENT_IDS != ''">
			PARENT_IDS = #{PARENT_IDS},
		</if>
		<if test="DICTIONARY_NAMES != null and DICTIONARY_NAMES != ''">
			DICTIONARY_NAMES = #{DICTIONARY_NAMES},
		</if>
		<if test="DICTIONARY_LEVEL != null">
			DICTIONARY_LEVEL = #{DICTIONARY_LEVEL},
		</if>
		<if test="DICTIONARY_SORT != null">
			DICTIONARY_SORT = #{DICTIONARY_SORT},
		</if>
		<if test="DICTIONARY_SOURCE != null">
			DICTIONARY_SOURCE = #{DICTIONARY_SOURCE},
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
			CORPINFO_ID = #{CORPINFO_ID},
		</if>
		<if test="ISDELETE != null and ISDELETE != ''">
			ISDELETE = #{ISDELETE},
		</if>
		<if test="CREATOR_ID != null and CREATOR_ID != ''">
			CREATOR_ID = #{CREATOR_ID},
		</if>
		<if test="CREATOR_NAME != null and CREATOR_NAME != ''">
			CREATOR_NAME = #{CREATOR_NAME},
		</if>
		<if test="CREATE_TIME != null and CREATE_TIME != ''">
			CREATE_TIME = #{CREATE_TIME},
		</if>
		<if test="OPERATOR_ID != null and OPERATOR_ID != ''">
			OPERATOR_ID = #{OPERATOR_ID},
		</if>
		<if test="OPERATOR_NAME != null and OPERATOR_NAME != ''">
			OPERATOR_NAME = #{OPERATOR_NAME},
		</if>
		<if test="OPERATE_TIME != null and OPERATE_TIME != ''">
			OPERATE_TIME = #{OPERATE_TIME},
		</if>
		<if test="DELETEOR_ID != null and DELETEOR_ID != ''">
			DELETEOR_ID = #{DELETEOR_ID},
		</if>
		<if test="DELETEOR_NAME != null and DELETEOR_NAME != ''">
			DELETEOR_NAME = #{DELETEOR_NAME},
		</if>
		<if test="DELETE_TIME != null and DELETE_TIME != ''">
			DELETE_TIME = #{DELETE_TIME},
		</if>
			DICTIONARY_ID = DICTIONARY_ID
		where
			DICTIONARY_ID = #{DICTIONARY_ID}
	</update>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where
			f.DICTIONARY_ID = #{DICTIONARY_ID}
	</select>

	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
        select
		<include refid="Field"></include>
        from
        <include refid="tableName"></include> f
		where f.ISDELETE = '0'
        <if test="pd.DICTIONARY_NAME != null and pd.DICTIONARY_NAME != ''">
			and f.DICTIONARY_NAME like CONCAT(CONCAT('%', #{pd.DICTIONARY_NAME}),'%')
		</if>
		<if test="pd.DICTIONARY_NAMES != null and pd.DICTIONARY_NAMES != ''">
			and f.DICTIONARY_NAMES like CONCAT(CONCAT('%', #{pd.DICTIONARY_NAMES}),'%')
		</if>
        order by f.DICTIONARY_SORT asc
    </select>

	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
			CASE f.DICTIONARY_LEVEL
			WHEN 1 THEN
			COUNT(cca.COMMON_ITEM_ID)+COUNT(cua.CUSTOM_ITEM_ID)
			WHEN 2 THEN
			COUNT(cci.COMMON_ITEM_ID)+COUNT(cui.CUSTOM_ITEM_ID)
			END COUNT_USE,
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		left join bus_hiddendangercheckstandard_common_item cca on cca.CHECK_CATEGORY = f.DICTIONARY_ID and cca.ISDELETE = '0'
		left join bus_hiddendangercheckstandard_common_item cci on cci.CHECK_ITEM = f.DICTIONARY_ID and cci.ISDELETE = '0'
		left join bus_hiddendangercheckstandard_custom_item cua on cua.CHECK_CATEGORY = f.DICTIONARY_ID and cua.ISDELETE = '0'
		left join bus_hiddendangercheckstandard_custom_item cui on cui.CHECK_ITEM = f.DICTIONARY_ID and cui.ISDELETE = '0'
		where f.ISDELETE = '0'
		group by f.DICTIONARY_ID
		order by f.DICTIONARY_LEVEL asc, f.DICTIONARY_SORT asc
	</select>

	<!-- 批量删除 -->
	<delete id="deleteAll" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1',
			DELETEOR_ID = #{DELETEOR_ID},
			DELETEOR_NAME = #{DELETEOR_NAME},
			DELETE_TIME = #{DELETE_TIME}
		where
			DICTIONARY_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>
	<!--	* 获取符合条件数，+1后作为排序使用-->
	<select id="getCount" parameterType="pd" resultType="Integer" >
		SELECT
		COUNT(*)
		FROM
		<include refid="tableName"></include> f
		WHERE 1=1
		<if test="DICTIONARY_LEVEL != null">
			and DICTIONARY_LEVEL = #{DICTIONARY_LEVEL}
		</if>
	</select>

	<!-- 列表(全部) -->
	<select id="listSelect" parameterType="pd" resultType="pd">
		select
			f.DICTIONARY_ID,
			f.DICTIONARY_NAME,
			f.PARENT_IDS
		from
		<include refid="tableName"></include> f
		where f.ISDELETE = '0'
		<if test="DICTIONARY_LEVEL != null and DICTIONARY_LEVEL != ''">
			and f.DICTIONARY_LEVEL = #{DICTIONARY_LEVEL}
		</if>
		<if test="COMMON_ID != null and COMMON_ID != ''">
		  <choose>
			  <when test="DICTIONARY_LEVEL == 1">
				  and exists(select 1 from BUS_HIDDENDANGERCHECKSTANDARD_COMMON_ITEM ci where ci.CHECK_CATEGORY = f.DICTIONARY_ID and
				  ci.ISDELETE = '0' and ci.COMMON_ID = #{COMMON_ID}
			  </when>
			  <when test="DICTIONARY_LEVEL == 2">
				  and exists(select 1 from BUS_HIDDENDANGERCHECKSTANDARD_COMMON_ITEM ci where ci.CHECK_ITEM = f.DICTIONARY_ID and
				  ci.ISDELETE = '0' and ci.COMMON_ID = #{COMMON_ID}
			  </when>
		  </choose>
		</if>
		order by f.DICTIONARY_SORT asc
	</select>

	<!--	* 获取符合条件数，+1后作为排序使用-->
	<select id="validateExist" parameterType="pd" resultType="Integer" >
		SELECT
		COUNT(*)
		FROM
		<include refid="tableName"></include> f
		WHERE f.ISDELETE = '0'
		<if test="DICTIONARY_LEVEL != null">
			and f.DICTIONARY_LEVEL = #{DICTIONARY_LEVEL}
		</if>
		<if test="DICTIONARY_NAME != null and DICTIONARY_NAME != ''">
			and f.DICTIONARY_NAME = #{DICTIONARY_NAME}
		</if>
		<if test="PARENT_ID != null and PARENT_ID != ''">
			and f.PARENT_ID = #{PARENT_ID}
		</if>
	</select>

</mapper>
