<?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="cn.ey.biz.module.system.dal.mapper.category.CategoryDataMapper">

    <select id="selectLists" resultType="cn.ey.biz.module.system.controller.admin.category.vo.CategoryDataSimpleRespVO">
        SELECT scd.category_code AS `dictType`,
               scd.data_code     AS `value`,
               scd.data_value    AS `dataValue`,
               scd.data_id       AS `id`,
               scd.data_pid      AS `pid`,
               scd.sort          AS `sort`,
               i.`label`         AS `label`
        FROM system_category sc,
             system_category_app sca,
             system_category_data scd
                 LEFT JOIN infra_i18n_dict i
                           ON i.`key` = scd.data_id AND i.deleted = 0 AND i.lang = #{acceptLanguage}
        WHERE scd.category_code = sc.category_code
          AND sc.category_code = sca.category_id
          AND sc.deleted = 0
          AND scd.deleted = 0
          AND sca.deleted = 0
          AND sca.app = #{app}
        ORDER BY scd.category_code, scd.sort DESC
    </select>

    <!-- 自定义ORM映射规则 -->
    <resultMap id="CategoryDataDO" type="cn.ey.biz.module.system.controller.admin.category.vo.CategoryDataRespVO">
        <result column="data_id" property="id"/>
        <result column="data_pid" property="pid"/>
        <result column="category_code" property="categoryCode"/>
        <result column="data_code" property="code"/>
        <result column="data_name" property="name"/>
        <result column="data_value" property="value"/>
        <result column="has_children" property="hasChildren"/>
        <result column="data_status" property="status"/>
        <result column="data_remark" property="remark"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <!-- 当关联属性为集合时用collection标签来描 ofType是关联属性集合中装的实体类的全类名 -->
        <collection property="langs" ofType="cn.ey.biz.module.system.controller.admin.i18n.vo.I18nRespVO">
            <result property="label" column="label"/>
        </collection>
    </resultMap>
    <select id="selectPage" resultMap="CategoryDataDO">
        SELECT scd.data_id,
               scd.data_pid,
               scd.data_parent_identifier,
               scd.category_code,
               scd.data_code,
               scd.data_identifier,
               scd.data_value,
               scd.has_children,
               scd.data_status,
               scd.category_version,
               scd.data_version,
               scd.new_version,
               scd.sort,
               scd.create_time,
               scd.update_time,
               scd.data_remark,
               i.label as data_name
        FROM system_category_data scd
                 LEFT JOIN infra_i18n_dict i ON i.`key` = scd.data_id AND i.deleted = 0 AND
                                                i.lang = #{c.acceptLanguage}
        WHERE scd.deleted = 0
          AND scd.category_code = #{c.categoryCode}
        <if test="c.name != null and c.name != ''">
          AND i.label like concat('%', #{c.name},'%')
        </if>
          AND scd.data_pid = 0
          AND scd.new_version = 1
        ORDER BY scd.sort DESC
    </select>

    <select id="checkNameRepetition"
            resultType="cn.ey.biz.module.system.controller.admin.category.vo.CategoryDataRespVO">
        SELECT
        scd.data_pid AS pid,
        scd.data_parent_identifier as dataParentIdentifier,
        scd.category_code ,
        scd.data_code AS code,
        scd.data_identifier as dataIdentifier,
        scd.data_value AS value ,
        scd.has_children,
        scd.data_status AS status,
        i.*
        FROM
        system_category_data scd
        LEFT JOIN infra_i18n_dict i ON i.`key` = scd.data_id
        WHERE
        scd.deleted = 0
        AND i.deleted = 0
        AND scd.category_code = #{c.categoryCode}
        AND scd.data_pid = #{c.pid}
        AND i.lang = #{i.lang}
        AND i.label = #{i.label}
        <if test="id != null and id != ''">
            AND i.`key` != #{id}
        </if>
    </select>

    <select id="getCategoryDataKey" resultType="cn.ey.biz.module.system.dal.dataobject.category.CategoryDataDO">
        SELECT scd.data_value AS value
        FROM system_category sc,
             system_category_app sca,
             system_category_data scd
        WHERE scd.category_code = sc.category_code
          AND sc.category_code = sca.category_id
          AND sc.deleted = 0
          AND sc.is_new_version = 1
          AND sca.deleted = 0
          AND scd.deleted = 0
          AND sca.app = #{app}
          AND scd.category_code = #{category}
          AND scd.data_code = #{key}
          AND scd.new_version = 1
    </select>


    <sql id="baseColumns">
        T1.data_id,
        T1.data_pid,
        T1.data_identifier,
        T1.category_code,
        T1.data_code,
        T1.data_parent_identifier,
        T1.data_value,
        T1.has_children,
        T1.data_status,
        T1.data_remark,
        T1.category_version,
        T1.data_version,
        T1.new_version,
        T1.sort,
        T1.update_time
    </sql>

    <select id="getCategoryDataV2" parameterType="cn.ey.biz.module.system.api.category.dto.CategoryDataV2ReqDTO"
            resultType="cn.ey.biz.module.system.api.category.dto.CategoryDataV2RespDTO">
        SELECT
        <include refid="baseColumns"/>,
        T2.label as `data_name`,
        T2.label as `label`
        FROM system_category_data T1
        JOIN system_category T0 ON T0.category_code = T1.category_code
        AND T0.category_version = T1.category_version AND T0.category_status = 0 AND T0.deleted = 0
        LEFT JOIN infra_i18n_dict T2 ON T2.`key` = T1.data_id AND T2.deleted = 0 AND T2.lang = #{req.acceptLanguage}
        WHERE
        T1.deleted = 0
        <if test="req.status != null ">
            AND T1.data_status = #{req.status, jdbcType=TINYINT}
        </if>
        <if test="req.categoryCode != null and req.categoryCode != ''">
            AND T1.category_code = #{req.categoryCode}
        </if>
        <choose>
            <when test="req.categoryVersion != null and req.categoryVersion != ''">
                AND T1.category_version = #{req.categoryVersion}
            </when>
            <when test="req.dataVersion != null and req.dataVersion != ''">
                AND T1.data_version = #{req.dataVersion}
            </when>
            <otherwise>
                AND T1.new_version = 1
            </otherwise>
        </choose>
        <if test="req.hasChildren != null and req.hasChildren != ''">
            AND T1.has_children = #{req.hasChildren}
        </if>
        <if test="req.dataCode != null and req.dataCode != ''">
            AND T1.data_code = #{req.dataCode}
        </if>
        <if test="req.dataPid != null and req.dataPid != ''">
            AND T1.data_pid = #{req.dataPid}
        </if>
        ORDER BY T1.sort
    </select>

    <select id="selectListsV2"
            resultType="cn.ey.biz.module.system.controller.admin.category.vo.CategoryDataSimpleRespVO">
        SELECT T1.category_code AS `dictType`,
        T1.data_code AS `value`,
        T1.data_identifier AS `dataIdentifier`,
        T1.data_value AS `dataValue`,
        T1.data_id AS `id`,
        T1.data_pid AS `pid`,
        T1.data_parent_identifier AS `dataParentIdentifier`,
        T1.sort AS `sort`,
        T1.has_children AS `hasChildren`,
        T1.data_status AS `status`,
        T2.label AS `label`
        FROM system_category T0
        JOIN system_category_data T1 ON T0.category_code = T1.category_code
        AND T0.category_version = T1.category_version
        <if test="status != null  and status != 3">
            AND T1.data_status = #{status, jdbcType=TINYINT}
        </if>
        AND T1.deleted = 0
        AND T1.new_version = 1
        JOIN system_category_app T3
        on T0.category_code = T3.category_id and T3.app = #{app} and T3.deleted = 0
        LEFT JOIN infra_i18n_dict T2
        ON T2.`key` = T1.data_id AND T2.deleted = 0 AND T2.lang = #{acceptLanguage}
        WHERE T0.deleted = 0
        AND T0.category_status = 0
        AND T0.is_new_version = 1
        ORDER BY T1.category_code, T1.sort DESC
    </select>


</mapper>

