<?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.blueland.dao.DictDao">
    <resultMap id="dictMap" type="com.blueland.bo.DictEntity">
        <result property="id" column="ID"/>
        <result property="dictCode" column="DICT_CODE"/>
        <result property="dictParentId" column="DICT_PARENT_ID"/>
        <result property="dictName" column="DICT_NAME"/>
        <result property="sort" column="SORT"/>
        <result property="status" column="STATUS"/>
        <result property="creatorId" column="CREATOR_ID"/>
        <result property="createDate" column="CREATE_DATE"/>
        <result property="updatorId" column="UPDATOR_ID"/>
        <result property="updateDate" column="UPDATE_DATE"/>
        <result property="appId" column="APP_ID"/>
        <result property="value" column="value"/>
    </resultMap>

    <sql id="dictColumns">
        ID,
        DICT_CODE,
        DICT_PARENT_ID,
        DICT_NAME,
        SORT,
        STATUS,
        CREATOR_ID,
        CREATE_DATE,
        UPDATOR_ID,
        UPDATE_DATE,
        APP_ID
    </sql>
    <sql id="dictQueryColumns">
        T1.ID,
        T1.DICT_CODE,
        T1.DICT_PARENT_ID,
        T1.DICT_NAME,
        T1.SORT,
        T1.STATUS,
        T1.CREATOR_ID,
        T1.CREATE_DATE,
        T1.UPDATOR_ID,
        T1.UPDATE_DATE,
        T1.APP_ID,
        T1.DICT_NAME as value
    </sql>

    <sql id="where">
        <where>
            <if test="@Ognl@isNotBlank(bo.dictCode)">
                T1.DICT_CODE =#{bo.dictCode}
            </if>
            <if test="@Ognl@isNotBlank(bo.dictName)">
                and T1.DICT_NAME LIKE CONCAT('%',#{bo.dictName},'%')
            </if>
            <if test="@Ognl@isNotEmpty(bo.sort)">
                and T1.SORT=#{bo.sort}
            </if>
            <if test="@Ognl@isNotEmpty(bo.status)">
                and T1.STATUS=#{bo.status}
            </if>
            <if test="@Ognl@isNotBlank(bo.dictParentId)">
                and T1.DICT_PARENT_ID=#{bo.dictParentId}
            </if>
            <if test="@Ognl@isNotBlank(bo.appId)">
                and T1.APP_ID=#{bo.appId}
            </if>
        </where>
    </sql>

    <!-- find one dict  -->
    <select id="findDictById" resultType="com.blueland.bo.DictEntity">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where T1.DICT_CODE = #{dictCode}
          and T1.STATUS = 'Y'
    </select>

    <select id="findDictById2" resultType="com.blueland.bo.DictEntity">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where T1.DICT_CODE = #{dictCode}
    </select>

    <!-- find dict by DICT_PARENT_ID and value  -->
    <select id="findDictByDictParentIdAndDictId" resultType="com.blueland.bo.DictEntity">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where T1.DICT_PARENT_ID = #{dictParentId}
          AND T1.STATUS = 'Y'
          AND T1.DICT_CODE = #{dictCode}
          and T1.APP_ID = #{appId}
    </select>

    <!-- init dict tree -->
    <select id="findInItTree" resultType="com.blueland.bo.DictEntity" parameterType="java.lang.String">
        SELECT *
        FROM t_pf_dict
        where DICT_CODE != #{dictCode} START
        WITH DICT_CODE =#{dictCode}
                CONNECT BY PRIOR DICT_CODE = DICT_PARENT_ID
    </select>

    <select id="findChildrenListById" resultType="com.blueland.bo.DictEntity" parameterType="java.lang.String">
        SELECT *
        FROM t_pf_dict
        where DICT_PARENT_ID = #{dictParentId}
          and STATUS = 'Y'
          and APP_ID = #{appId}
        order by SORT
    </select>


    <select id="findListByAppId" resultType="com.blueland.bo.DictEntity" parameterType="java.lang.String">
        SELECT *
        FROM t_pf_dict
        where STATUS = 'Y'
          and APP_ID = #{appId}
        order by SORT
    </select>


    <!-- count child node -->
    <select id="findChildrenById" parameterType="java.lang.String" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM t_pf_dict
        WHERE DICT_PARENT_ID = #{dictParentId}
          and STATUS = 'Y'
          and APP_ID = #{appId}
    </select>

    <select id="findDctList" resultType="com.blueland.bo.DictEntity">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where T1.DICT_PARENT_ID = '1'
          and T1.STATUS = 'Y'
          and APP_ID = #{appId}
        order by T1.SORT
    </select>

    <!-- find Dict list -->
    <select id="findDictPageListByParams" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        <include refid="where"/>
        <if test="@Ognl@isNotBlank(bo.orderByColumns)">
            order by ${bo.orderByColumns}
        </if>
    </select>

    <select id="findDictPagePageByParams" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        <include refid="where"/>
        <if test="@Ognl@isNotBlank(bo.orderByColumns)">
            order by ${bo.orderByColumns}
        </if>
    </select>

    <select id="findDictPageListByAppId" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
      <where>
          <if test="appId != null and appId != ''">
              T1.APP_ID=#{appId}
          </if>
      </where>
    </select>

    <!-- find Dict -->
    <select id="listPageDictByDoc" resultType="com.blueland.bo.DictEntity">
        SELECT
        <include refid="dictQueryColumns"/>
        FROM t_pf_dict T1
        WHERE T1.DICT_CODE = #{dictCode}
          and T1.DICT_PARENT_ID = #{dictParentId}
          and T1.STATUS = 'Y'
          and APP_ID = #{appId}
    </select>

    <!-- insertdict -->
    <insert id="insertDict">
        <selectKey keyProperty="id" resultType="String" order="BEFORE">
            select replace(uuid(), '-', '')
            from dual
        </selectKey>
        insert into t_pf_dict(<include refid="dictColumns"/>)
            values (#{id,jdbcType=VARCHAR},
                    #{dictCode,jdbcType=VARCHAR},
                    #{dictParentId,jdbcType=VARCHAR},
                    #{dictName,jdbcType=VARCHAR},
                    #{sort,jdbcType=INTEGER},
                    #{status,jdbcType=VARCHAR},
                    #{creatorId,jdbcType=VARCHAR},
                    #{createDate,jdbcType=TIMESTAMP},
                    #{updatorId,jdbcType=VARCHAR},
                    #{updateDate,jdbcType=TIMESTAMP},
                    #{appId,jdbcType=VARCHAR})
    </insert>

    <!-- mofify dict -->
    <update id="updateDict">
        update t_pf_dict
        <set>
            <if test="dictCode != null">
                DICT_CODE=#{dictCode,jdbcType=VARCHAR},
            </if>
            <if test="dictName != null">
                DICT_NAME=#{dictName,jdbcType=VARCHAR},
            </if>
            <if test="appId != null">
                APP_ID=#{appId,jdbcType=VARCHAR},
            </if>
            <if test="dictParentId != null">
                DICT_PARENT_ID = #{dictParentId,jdbcType=VARCHAR},
            </if>
            <if test="sort != null">
                SORT = #{sort,jdbcType=INTEGER},
            </if>
            <if test="status != null">
                STATUS = #{status,jdbcType=VARCHAR},
            </if>
            <if test="updatorId != null">
                UPDATOR_ID = #{updatorId,jdbcType=VARCHAR},
            </if>
            <if test="updateDate != null">
                UPDATE_DATE = #{updateDate,jdbcType=TIMESTAMP}
            </if>
        </set>
        where ID = #{id}
    </update>

    <!-- delete some dict DELETE FROM t_pf_dict WHERE ID=#{id,jdbcType=VARCHAR} -->
    <delete id="deleteDicts" parameterType="java.lang.String">
        UPDATE t_pf_dict
        SET STATUS = 'N'
        WHERE DICT_CODE = #{dictCode}
           OR DICT_PARENT_ID = #{dictCode}
    </delete>

    <delete id="deleteDictById" parameterType="java.lang.String">
        DELETE
        FROM t_pf_dict
        WHERE ID = #{id,jdbcType=VARCHAR}
    </delete>

    <select id="findList" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        <include refid="where"/>
        <choose>
            <when test="@Ognl@isNotEmpty(bo.sortColumns)">
                ORDER BY ${bo.sortColumns}
            </when>
            <otherwise>
                ORDER BY SORT
            </otherwise>
        </choose>
    </select>

    <select id="findPage" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        <include refid="where"/>
        <choose>
            <when test="@Ognl@isNotEmpty(bo.sortColumns)">
                ORDER BY ${bo.sortColumns}
            </when>
            <otherwise>
                ORDER BY SORT
            </otherwise>
        </choose>
    </select>


    <select id="getList" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where status='Y'
        <if test="appId != null and appId != ''" >
            and T1.app_id = #{appId}
        </if>
        order by sort
    </select>

    <delete id="deleteDictByAppId">
        delete from t_pf_dict where app_id = #{appId}
    </delete>

    <select id="getDictList" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where DICT_PARENT_ID != 1 and status='Y'
        order by sort
    </select>

    <select id="getDictListByCode" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1
        where T1.DICT_PARENT_ID != 1 and T1.status='Y'
        <if test="code != null and code != ''">
            and T1.DICT_CODE = #{code}
        </if>
        <if test="parentCode != null and parentCode!= ''" >
            and T1.DICT_PARENT_ID = #{parentCode}
        </if>
        order by T1.sort
    </select>

    <select id="findByIds" resultMap="dictMap">
        select
        <include refid="dictQueryColumns"/>
        from t_pf_dict T1 where
                id in
        <foreach collection="ids" close=")" item="id" open="(" separator=",">
            #{id}
        </foreach>
    </select>
</mapper>
