<?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="go.kr.project.system.code.mapper.CodeMapper">
    <!-- 코드 그룹 목록 조회 -->
    <select id="selectCodeGroupList" parameterType="CodeSearchVO" resultType="CodeGroupVO">
        select CD_GROUP_ID
             , CD_GROUP_NM
             , CD_GROUP_DC
             , USE_YN
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
        from tb_cd_group
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
                CD_GROUP_ID like CONCAT('%', #{searchKeyword}, '%')
                or CD_GROUP_NM like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
        <if test="sortAscending != null and sortColumn != null and sortColumn != ''">
            <choose>
                <when test="sortColumn == 'cdGroupId'">order by CD_GROUP_ID</when>
                <when test="sortColumn == 'cdGroupNm'">order by CD_GROUP_NM</when>
                <otherwise>
                    order by CD_GROUP_ID
                </otherwise>
            </choose>
            <choose>
                <when test="sortAscending == true">ASC</when>
                <when test="sortAscending == false">DESC</when>
                <otherwise>
                    ASC
                </otherwise>
            </choose>
        </if>
        <if test='pagingYn != null and pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 코드 그룹 목록 총 개수 조회 -->
    <select id="selectCodeGroupListTotalCount" parameterType="CodeSearchVO" resultType="int">
        select COUNT(*)
        from tb_cd_group
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
                CD_GROUP_ID like CONCAT('%', #{searchKeyword}, '%')
                or CD_GROUP_NM like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
    </select>

    <!-- 코드 상세 목록 조회 -->
    <select id="selectCodeDetailList" parameterType="CodeSearchVO" resultType="CodeDetailVO">
        select d.CD_GROUP_ID
             , m.CD_GROUP_NM
             , d.CD_ID
             , d.CD_NM
             , d.CD_DC
             , d.SORT_ORDR
             , d.USE_YN
             , d.ATTRIBUTE1
             , d.ATTRIBUTE2
             , d.ATTRIBUTE3
             , d.ATTRIBUTE4
             , d.ATTRIBUTE5
             , d.REG_DTTM
             , d.RGTR
             , d.MDFCN_DTTM
             , d.MDFR
        from tb_cd_detail d
        join tb_cd_group m on d.CD_GROUP_ID = m.CD_GROUP_ID
        where 1 = 1
          and d.CD_GROUP_ID = #{selectCdGroupId}
        <if test='searchUseYn != null and searchUseYn != ""'>
            and d.USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
                d.CD_ID like CONCAT('%', #{searchKeyword}, '%')
                or d.CD_NM like CONCAT('%', #{searchKeyword}, '%')
                or m.CD_GROUP_NM like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
        order by d.SORT_ORDR ASC
        <if test='pagingYn != null and pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 코드 상세 목록 총 개수 조회 -->
    <select id="selectCodeDetailListTotalCount" parameterType="CodeSearchVO" resultType="int">
        select COUNT(*)
        from tb_cd_detail d
        join tb_cd_group m on d.CD_GROUP_ID = m.CD_GROUP_ID
        where 1 = 1
        <if test='selectCdGroupId != null and selectCdGroupId != ""'>
            and d.CD_GROUP_ID = #{selectCdGroupId}
        </if>
        <if test='searchUseYn != null and searchUseYn != ""'>
            and d.USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
                d.CD_ID like CONCAT('%', #{searchKeyword}, '%')
                or d.CD_NM like CONCAT('%', #{searchKeyword}, '%')
                or m.CD_GROUP_NM like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
    </select>

    <!-- 코드 상세 등록 -->
    <insert id="insertCodeDetail" parameterType="CodeDetailVO">
        INSERT INTO tb_cd_detail (
            CD_GROUP_ID,
            CD_ID,
            CD_NM,
            CD_DC,
            SORT_ORDR,
            USE_YN,
            ATTRIBUTE1,
            ATTRIBUTE2,
            ATTRIBUTE3,
            ATTRIBUTE4,
            ATTRIBUTE5,
            REG_DTTM,
            RGTR
        ) VALUES (
            #{cdGroupId},
            #{cdId},
            #{cdNm},
            #{cdDc},
            #{sortOrdr},
            #{useYn},
            #{attribute1},
            #{attribute2},
            #{attribute3},
            #{attribute4},
            #{attribute5},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 코드 상세 수정 -->
    <update id="updateCodeDetail" parameterType="CodeDetailVO">
        UPDATE tb_cd_detail
        SET
            CD_NM = #{cdNm},
            <if test="cdDc != null">
            CD_DC = #{cdDc},
            </if>
            SORT_ORDR = #{sortOrdr},
            USE_YN = #{useYn},
            <if test="attribute1 != null">
            ATTRIBUTE1 = #{attribute1},
            </if>
            <if test="attribute2 != null">
            ATTRIBUTE2 = #{attribute2},
            </if>
            <if test="attribute3 != null">
            ATTRIBUTE3 = #{attribute3},
            </if>
            <if test="attribute4 != null">
            ATTRIBUTE4 = #{attribute4},
            </if>
            <if test="attribute5 != null">
            ATTRIBUTE5 = #{attribute5},
            </if>
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE CD_GROUP_ID = #{cdGroupId}
          AND CD_ID = #{cdId}
    </update>

    <!-- 특정 코드 상세 삭제 -->
    <delete id="deleteCodeDetail" parameterType="CodeDetailVO">
        DELETE FROM tb_cd_detail
        WHERE CD_GROUP_ID = #{cdGroupId}
          AND CD_ID = #{cdId}
    </delete>

    <!-- 코드 그룹 등록 -->
    <insert id="insertCodeGroup" parameterType="CodeGroupVO">
        INSERT INTO tb_cd_group (
            CD_GROUP_ID,
            CD_GROUP_NM,
            CD_GROUP_DC,
            USE_YN,
            REG_DTTM,
            RGTR
        ) VALUES (
            #{cdGroupId},
            #{cdGroupNm},
            #{cdGroupDc},
            #{useYn},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 코드 그룹 수정 -->
    <update id="updateCodeGroup" parameterType="CodeGroupVO">
        UPDATE tb_cd_group
        SET
            CD_GROUP_NM = #{cdGroupNm},
            <if test="cdGroupDc != null">
            CD_GROUP_DC = #{cdGroupDc},
            </if>
            USE_YN = #{useYn},
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE CD_GROUP_ID = #{cdGroupId}
    </update>

    <!-- 특정 코드 그룹 삭제 -->
    <delete id="deleteCodeGroup" parameterType="CodeGroupVO">
        DELETE FROM tb_cd_group
        WHERE CD_GROUP_ID = #{cdGroupId}
    </delete>

    <!-- 코드 상세 중복 체크 -->
    <select id="selectCodeDetailExistsCount" parameterType="CodeDetailVO" resultType="int">
        SELECT COUNT(*)
        FROM tb_cd_detail
        WHERE CD_GROUP_ID = #{cdGroupId}
          AND CD_ID = #{cdId}
    </select>
</mapper>
