<?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="cokr.xit.fims.cvlc.dao.AnswerWordsMapper">

<select id="selectCvlcptAnswerBasicWordsInfo" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 민원답변기본문구 대장 객체 가져오기(answerWordsMapper.selectCvlcptAnswerBasicWordsInfo) */
SELECT A.HEADER_CN
     , A.FOOTER_CN
  FROM TB_CVLCPT_BSC_WORDS A
 WHERE A.SGG_CD                 = #{sggCd}                                                              /* 시군구 코드 */
   AND A.TASK_SE_CD             = #{taskSeCd}                                                           /* 업무 구분 코드 */
</select>

<select id="selectCvlcptAnswerWordsInfo" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 민원답변문구 대장 객체 가져오기(answerWordsMapper.selectCvlcptAnswerWordsInfo) */
SELECT A.ANS_WORDS_ID                                                                   /* 답변 문구 ID */
     , A.PRCS_RSLT_CN                                                                   /* 처리 결과 내용 */
  FROM TB_CVLCPT_ANS_WORDS A
 WHERE A.SGG_CD                 = #{sggCd}                                                              /* 시군구 코드 */
   AND A.TASK_SE_CD             = #{taskSeCd}                                                           /* 업무 구분 코드 */
   AND A.ANS_RSN_CD             = #{ansRsnCd}                                                           /* 답변 사유 코드 */
   <if test="taskSeCd == 'DPV' and ansRsnCd == '301' ">
   <choose>
       <when test='dstrbncYn == "Y"'>
   AND A.DSTRBNC_YN             = 'Y'                                                           /* 방해 여부 */
       </when>
       <otherwise>
   AND A.DSTRBNC_YN             = 'N'                                                           /* 방해 여부 */
       </otherwise>
   </choose>
   </if>
   AND A.USE_YN                 = 'Y'                                                                   /* 사용 여부 */
</select>

<select id="selectLevyCaseWordsList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 부과처리건 답변문구 목록 조회(answerWordsMapper.selectLevyCaseWordsList) */
SELECT (CASE
        WHEN B.DSTRBNC_YN = 'Y'
        THEN CONCAT(A.VLTN_CD,'D')
        ELSE A.VLTN_CD
        END) AS VLTN_CASE_ID
     , (CASE
        WHEN B.DSTRBNC_YN = 'Y'
        THEN CONCAT(A.VLTN_ARTCL,'(1면방해)')
        ELSE A.VLTN_ARTCL
        END) AS VLTN_CASE_NM
     , B.ANS_WORDS_ID
     , B.PRCS_RSLT_CN
  FROM (
       (
        SELECT SGG_CD
             , TASK_SE_CD
             , VLTN_CD
             , 'N' AS DSTRBNC_YN
             , VLTN_ARTCL
          FROM TB_VLTN
         WHERE SGG_CD = #{sggCd}
           AND TASK_SE_CD = #{taskSeCd}
       )
       <if test="taskSeCd == 'DPV'">
       UNION
       (
        SELECT SGG_CD
             , TASK_SE_CD
             , VLTN_CD
             , 'Y' AS DSTRBNC_YN
             , VLTN_ARTCL
          FROM TB_VLTN
         WHERE SGG_CD = #{sggCd}
           AND TASK_SE_CD = #{taskSeCd}
           AND VLTN_CD = '01'
       )
       </if>
  ) A
  LEFT OUTER JOIN TB_CVLCPT_ANS_WORDS B
       ON (A.SGG_CD = B.SGG_CD
       AND A.TASK_SE_CD = B.TASK_SE_CD
       AND CONCAT('3',A.VLTN_CD) = B.ANS_RSN_CD
       AND B.USE_YN = 'Y'
       AND B.ANS_SE_CD = '3'
       AND A.DSTRBNC_YN = B.DSTRBNC_YN)
 ORDER BY A.VLTN_CD
</select>

<select id="selectWarningCaseWordsList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 계도처리건 답변문구 목록 조회(answerWordsMapper.selectWarningCaseWordsList) */
SELECT A.CODE     AS LEVY_EXCL_RSN_CD
     , A.CODE_VAL AS LEVY_EXCL_RSN_NM
     , B.ANS_WORDS_ID
     , B.PRCS_RSLT_CN
  FROM TB_CMN_CODE A
  LEFT OUTER JOIN TB_CVLCPT_ANS_WORDS B
       ON (A.CODE = B.ANS_RSN_CD
       AND B.USE_YN = 'Y'
       AND B.ANS_SE_CD = '2'
       AND B.SGG_CD = #{sggCd}
       AND B.TASK_SE_CD = #{taskSeCd}
       )
 WHERE A.GRP_ID = 'FIM022'
   AND A.CODE LIKE '2%'
 ORDER BY A.CODE
</select>

<select id="selectNonlevyCaseWordsList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 미부과처리건 답변문구 목록 조회(answerWordsMapper.selectNonlevyCaseWordsList) */
SELECT A.CODE     AS LEVY_EXCL_RSN_CD
     , A.CODE_VAL AS LEVY_EXCL_RSN_NM
     , B.ANS_WORDS_ID
     , B.PRCS_RSLT_CN
     , (CASE WHEN B.USE_YN = 'Y' THEN '예' ELSE '아니오' END) AS USE_YN
  FROM TB_CMN_CODE A
  LEFT OUTER JOIN TB_CVLCPT_ANS_WORDS B
       ON (A.CODE = B.ANS_RSN_CD
       AND B.USE_YN = 'Y'
       AND B.ANS_SE_CD = '1'
       AND B.SGG_CD = #{sggCd}
       AND B.TASK_SE_CD = #{taskSeCd}
       )
 WHERE A.GRP_ID = 'FIM022'
   AND A.CODE LIKE '1%'
 ORDER BY A.CODE
</select>

<insert id="insertAnswerBasicWordsInfo" parameterType="cokr.xit.fims.cvlc.CrdnCvlcpt">
/* 기본 문구 설정 등록(answerWordsMapper.insertAnswerBasicWordsInfo) */
INSERT
  INTO TB_CVLCPT_BSC_WORDS(
       SGG_CD
     , TASK_SE_CD
     , HEADER_CN
     , FOOTER_CN
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
  )
VALUES (
       #{sggCd}
     , #{taskSeCd}
     , #{headerCn}
     , #{footerCn}
     , <include refid="utility.now" />
     , #{modifiedBy}
     , <include refid="utility.now" />
     , #{modifiedBy}
  )
</insert>

<update id="updateAnswerBasicWordsInfo" parameterType="cokr.xit.fims.cvlc.CrdnCvlcpt">
/* 기본 문구 설정 변경(answerWordsMapper.updateAnswerBasicWordsInfo) */
UPDATE TB_CVLCPT_BSC_WORDS
   SET HEADER_CN = #{headerCn}
     , FOOTER_CN = #{footerCn}
     , MDFCN_DT  = <include refid="utility.now" />
     , MDFR      = #{modifiedBy}
 WHERE SGG_CD = #{sggCd}
   AND TASK_SE_CD = #{taskSeCd}
</update>

<insert id="insertAnswerBodyWordsInfo" parameterType="cokr.xit.fims.cvlc.CrdnCvlcpt">
/* 본문 문구 설정 등록(answerWordsMapper.insertAnswerBodyWordsInfo) */
<selectKey resultType="string" keyProperty="ansWordsId" keyColumn="NEW_ID" order="BEFORE">
	<include refid="numbering.key">
		<property name="TABLE_NAME" value="TB_CVLCPT_ANS_WORDS" />
		<property name="TABLE_KEY" value="ANS_WORDS_ID" />
		<property name="pad" value="10" />
	</include>
</selectKey>
INSERT
  INTO TB_CVLCPT_ANS_WORDS(
       ANS_WORDS_ID
     , SGG_CD
     , TASK_SE_CD
     , ANS_SE_CD
     , DSTRBNC_YN
     , ANS_RSN_CD
     , ANS_RSN_NM
     , PRCS_SMRY
     , PRCS_RSLT_CN
     , USE_YN
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
  )
VALUES (
       #{ansWordsId}
     , #{sggCd}
     , #{taskSeCd}
     , SUBSTR(#{ansRsnCd},1,1)
     , IFNULL(#{dstrbncYn},'N')
     , #{ansRsnCd}
     , #{ansRsnNm}
     , #{prcsSmry}
     , #{prcsRsltCn}
     , 'Y'
     , <include refid="utility.now" />
     , #{modifiedBy}
     , <include refid="utility.now" />
     , #{modifiedBy}
  )
</insert>

<update id="updateAnswerBodyWordsInfo" parameterType="cokr.xit.fims.cvlc.CrdnCvlcpt">
/* 본문 문구 설정 변경(answerWordsMapper.updateAnswerBodyWordsInfo) */
UPDATE TB_CVLCPT_ANS_WORDS
   SET PRCS_RSLT_CN = #{prcsRsltCn}
     , MDFCN_DT  = <include refid="utility.now" />
     , MDFR      = #{modifiedBy}
 WHERE ANS_WORDS_ID = #{ansWordsId}
</update>

<update id="deleteCvlcptAnswerWordsInfo" parameterType="cokr.xit.fims.cvlc.CrdnCvlcpt">
/* 본문 문구 설정 삭제(answerWordsMapper.deleteCvlcptAnswerWordsInfo) */
UPDATE TB_CVLCPT_ANS_WORDS
   SET USE_YN = 'N'
     , MDFCN_DT  = <include refid="utility.now" />
     , MDFR      = #{modifiedBy}
 WHERE ANS_WORDS_ID = #{ansWordsId}
</update>

</mapper>