<?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.thinkgem.jeesite.modules.sys.dao.DictDao">
    
	<select id="get" resultType="Dict">
		SELECT 
			* 
		FROM sys_dict 
		WHERE id = #{id}
	</select>
	
	<select id="findList" resultType="Dict">
		SELECT 
			* 
		FROM sys_dict 
		WHERE del_flag = #{DEL_FLAG_NORMAL} 
		<if test="type != null and type != ''">
			AND type = #{type}
		</if>
		<if test="label != null and label != ''">
			AND label LIKE 
					<if test="dbName == 'oracle'">'%'||#{label}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT('%', #{label}, '%')</if>
		</if>
		<if test="description != null and description != ''">
			AND description LIKE 
					<if test="dbName == 'oracle'">'%'||#{description}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT('%', #{description}, '%')</if>
		</if>
		ORDER BY type, sort, update_date DESC
	</select>
	
	<select id="findAllList" resultType="Dict">
		SELECT 
			* 
		FROM sys_dict 
		WHERE del_flag = #{DEL_FLAG_NORMAL} 
		ORDER BY type, sort, update_date DESC
	</select>
	
	<select id="findTypeList" resultType="string">
		SELECT 
			type 
		FROM sys_dict 
		WHERE del_flag = #{DEL_FLAG_NORMAL} 
		GROUP BY type
		ORDER BY type
	</select>
	
<!-- 	<select id="findTdytList" resultType="string"> -->
<!-- 		SELECT  -->
<!-- 			label  -->
<!-- 		FROM sys_dict  -->
<!-- 		<where> -->
<!-- 			del_flag = #{DEL_FLAG_NORMAL}  -->
<!-- 			AND TYPE = #{type}  -->
<!-- 			<if test="value != null and value != ''"> -->
<!-- 				<if test="value == 3 or value == 7"> -->
<!-- 					AND (value like '5%' or value like '6%' or value like '7%'  -->
<!-- 						or value like '8%' or value like '9%' or value like '10%' -->
<!-- 						or value like '20%' or value = '110' or value = '113'  -->
<!-- 						or value = '115' or value = '118' or value = '120' or value = '121') -->
<!-- 					AND value not like '104' -->
<!-- 				</if> -->
<!-- 				<if test="value == 5"> -->
<!-- 					AND (value = '70' or value = '72' or value like '20%') -->
<!-- 				</if> -->
<!-- 				<if test="value == 9"> -->
<!-- 					AND (value = '10' or value = '11' or value = '12' or value = '13' or value = '20' -->
<!-- 					 or value = '21' or value = '22' or value = '23' or value = '40' or value = '41' -->
<!-- 					  or value = '42' or value = '43' or value = '90' or value = '110' or value = '111' -->
<!-- 					   or value = '112' or value = '113' or value = '114' or value = '117' or value = '120' -->
<!-- 					    or value = '121' or value = '122' or value = '123' or value = '124' or value = '125' or value = '126' -->
<!-- 					     or value = '127') -->
<!-- 				</if> -->
<!-- 			</if> -->
<!-- 			<if test="remarks == null or remarks == '' or remarks != 2"> -->
<!-- 					AND value not like '73' -->
<!-- 			</if> -->
<!-- 		</where> -->
<!-- 		order by to_number(value) asc -->
<!-- 	</select> -->
	
	<!-- 修改后,字典调整后可用 -->
	<select id="findTdytList" resultType="string">
		SELECT 
			label 
		FROM sys_dict 
		<where>
			del_flag = #{DEL_FLAG_NORMAL} 
			AND TYPE = #{type} 
			<if test="value != null and value != ''">
				<if test="value == 3 or value == 7">
					AND (value like '05%' or value like '06%' or value like '07%' 
						or value like '08%' or value like '09%' or value like '10%'
						or value like '20%' or value = '113' 
						or value = '115' or value = '118' or value = '121')
					AND value not like '104'
				</if>
				<if test="value == 5">
					AND (value = '072' or value like '20%')
				</if>
				<if test="value == 9">
				AND (value = '11' or value = '12' or value = '13'
					 or value = '21' or value = '22' or value = '23'or value = '41'
					  or value = '42' or value = '43' or value = '111'
					   or value = '112' or value = '113' or value = '114' or value = '117'
					    or value = '121' or value = '122' or value = '123' or value = '124' or value = '125' or value = '126'
					     or value = '127')
				</if>
			</if>
			<if test="remarks == null or remarks == '' or remarks != 2">
					AND value not like '73'
			</if>
		</where>
		order by to_number(value) asc
	</select>
	
	<select id="findDjxlList" resultType="Dict">
		SELECT *
		FROM sys_dict 
		WHERE del_flag = '0' AND type = 'reg_bus_djxl'
		ORDER BY value 
	</select>
	
	<select id="findDjxlListmohu" resultType="Dict">
		SELECT *
		FROM sys_dict 
		<where>
			del_flag = '0' 
			<if test="type != null and type != ''">
			  AND type = #{type}
		    </if>
		    <if test="value != null and value != ''">
			  AND value LIKE 
					<if test="dbName == 'oracle'">#{value}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT(#{value}, '%')</if>
		    </if>
		</where>
	</select>
	
	<select id="findLabelList" resultType="Dict">
		SELECT 
			* 
		FROM sys_dict 
		WHERE del_flag = #{DEL_FLAG_NORMAL} 
		<if test="label != null and label != ''">
			AND label LIKE 
					<if test="dbName == 'oracle'">'%'||#{label}||'%'</if>
					<if test="dbName == 'mysql'">CONCAT('%', #{label}, '%')</if>
		</if>
	</select>
	
	<insert id="insert">
		INSERT INTO sys_dict(
			id, 
			value, 
			label, 
			type, 
			description, 
			sort, 
			create_by, 
			create_date, 
			update_by, 
			update_date, 
			remarks, 
			del_flag
		) VALUES (
			#{id}, 
			#{value}, 
			#{label}, 
			#{type}, 
			#{description}, 
			#{sort}, 
			#{createBy.id}, 
			#{createDate}, 
			#{updateBy.id}, 
			#{updateDate}, 
			#{remarks}, 
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE sys_dict SET 
			value = #{value}, 
			label = #{label}, 
			type = #{type}, 
			description = #{description}, 
			sort = #{sort}, 
			update_by = #{updateBy.id}, 
			update_date = #{updateDate}, 
			remarks = #{remarks}
		WHERE id = #{id}
	</update>
	
	<update id="delete">
		UPDATE sys_dict SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
</mapper>