<?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.sprt.dao.IntegrationSearchMapper">

<select id="selectIntegrationDataList" parameterType="map" resultType="dataobject">
/* 통합 자료 목록 조회(integrationSearchMapper.selectIntegrationDataList) */
    SELECT C.CRDN_ID            <!-- 단속 ID -->
         , C.SGG_CD             <!--  시군구 코드 -->
         , (SELECT SGG_NM FROM TB_SGG WHERE SGG_CD = C.SGG_CD) AS SGG_NM
         , C.TASK_SE_CD         <!-- 업무 구분 코드 -->
         , (GET_CODE_NM('FIM054', C.TASK_SE_CD)) AS TASK_SE_NM
         , C.CRDN_INPT_SE_CD    <!-- 단속 입력 구분 코드 -->
         , (GET_CODE_NM('FIM003', C.CRDN_INPT_SE_CD)) AS CRDN_INPT_SE_NM
         , C.CRDN_REG_SE_CD     <!-- 단속 등록 구분 코드 -->
         , (GET_CODE_NM('FIM026', C.CRDN_REG_SE_CD)) AS CRDN_REG_SE_NM
         , CA.CRDN_SE_CD        <!-- 단속 구분 코드 -->
         , (GET_CODE_NM('FIM002', CA.CRDN_SE_CD)) AS CRDN_SE_NM
         , C.CVLCPT_LINK_YN     <!-- 민원 연계 여부 -->
         , C.CVLCPT_LINK_TBL_NM <!-- 민원 연계 테이블 명 -->
         , C.CVLCPT_LINK_ID     <!-- 민원 연계 ID -->
         , C.CRDN_YMD           <!-- 단속 일자 -->
         , C.CRDN_TM            <!-- 단속 시각 -->
         , CONCAT(C.CRDN_YMD, C.CRDN_TM) AS CRDN_YMD_TM
         , C.VHRNO              <!-- 차량번호 -->
         , L.RPM_SZR_VHRNO      <!-- 대체차량번호 -->
         , (GET_CODE_NM('FIM009', CA.FFNLG_CARMDL_CD)) AS FFNLG_CARMDL_NM
         , C.ATCH_FILE_CNT      <!-- 첨부 파일 수 -->
         , CA.PRK_PSBLTY_RSLT_CD    <!-- 주차 가능 결과 코드 -->
         , (GET_CODE_NM('FIM034', CA.PRK_PSBLTY_RSLT_CD)) AS PRK_PSBLTY_RSLT_NM
         , P.RTPYR_ID           <!-- 납부자 ID -->
         , P.RTPYR_INPT_SE_CD   <!-- 납부자 입력 구분 코드 -->
         , P.RTPYR_SE_CD        <!-- 납부자 구분 코드 -->
         , (GET_CODE_NM('FIM011', P.RTPYR_SE_CD)) AS RTPYR_SE_NM
         , P.RTPYR_NO           <!-- 납부자 번호 -->
         , P.RTPYR_NM           <!-- 납부자 명 -->
         , P.RTPYR_TELNO        <!-- 납부자 전화번호 -->
         , P.RTPYR_MBL_TELNO    <!-- 납부자 휴대 전화번호 -->
         , P.RTPYR_EML          <!-- 납부자 이메일 -->
         , P.RTPYR_STTS_CD      <!-- 납부자 상태 코드 -->
         , (GET_CODE_NM('FIM014', P.RTPYR_STTS_CD)) AS RTPYR_STTS_NM
         , PA.ADDR_SE_CD        <!-- 주소 구분 코드 -->
         , (GET_CODE_NM('FIM013', PA.ADDR_SE_CD)) AS ADDR_SE_NM
         , PA.ZIP       AS RTPYR_ZIP        <!-- 우편번호 -->
         , PA.ADDR      AS RTPYR_ADDR       <!-- 주소 -->
         , PA.DTL_ADDR  AS RTPYR_DTL_ADDR   <!-- 상세 주소 -->
         , CONCAT(PA.ADDR,' ',PA.DTL_ADDR)  AS RTPYR_FULL_ADDR
         , C.ETC_CN         <!-- 기타 내용 -->
         , C.CRDN_STTS_CD   <!-- 단속 상태 코드 -->
         , (GET_CODE_NM('FIM010', C.CRDN_STTS_CD)) AS CRDN_STTS_NM
         , C.CRDN_STDG_NM   <!-- 단속 법정동 명 -->
         , C.CRDN_ROAD_NM   <!-- 단속 도로 명 -->
         , C.CRDN_PLC       <!-- 단속 장소 -->
         , CA.DTL_CRDN_PLC  <!-- 상세 단속 장소 -->
         , C.VLTN_ID        <!-- 위반 ID -->
         , V.VLTN_ARTCL     <!-- 위반 항목 -->
         , V.VLTN_LAW_NM    <!-- 위반 법 명 -->
         , IFNULL(CA.TOWNG_YN,'N') AS TOWNG_YN
         , (GET_CODE_NM('CMN006', IFNULL(CA.TOWNG_YN,'N'))) AS TOWNG_YN_NM
         , C.CRDN_STTS_CHG_DT   <!-- 단속 상태 변경 일시 -->
         , SUBSTR(C.CRDN_STTS_CHG_DT,1,8)   AS CRDN_STTS_CHG_YMD
         , C.FFNLG_CRDN_AMT <!-- 과태료 단속 금액 -->
         , C.FFNLG_RDCRT    <!-- 과태료 감경율 -->
         , C.FFNLG_AMT      <!-- 과태료 금액 -->
         , C.ADVNTCE_BGNG_YMD   <!-- 사전통지 시작 일자 -->
         , C.ADVNTCE_DUDT_YMD   <!-- 사전통지 납기 일자 -->
         , C.ADVNTCE_AMT    <!-- 사전통지 금액 -->
         , IFNULL(C.OPNN_SBMSN_YN,'N') AS OPNN_SBMSN_YN
         , (GET_CODE_NM('CMN006', IFNULL(C.OPNN_SBMSN_YN,'N'))) AS OPNN_SBMSN_YN_NM
         , L.LEVY_ID        <!-- 부과 ID -->
         , L.INST_CD        <!-- 기관 코드 -->
         , L.DEPT_CD        <!-- 부서 코드 -->
         , L.SPCL_BIZ_CD    <!-- 특별회계 사업 코드 -->
         , L.FYR            <!-- 회계 연도 -->
         , L.ACNTG_SE_CD    <!-- 회계 구분 -->
         , L.TXITM_CD       <!-- 세목 코드 -->
         , L.LEVY_NO        <!-- 부과 번호 -->
         , L.ISPY_SN        <!-- 분납 일련번호 -->
         , CONCAT(L.FYR, '-', L.LEVY_NO) AS GOJI_NO
         , L.LEVY_SE_CD     <!-- 부과 구분 코드 -->
         , (GET_CODE_NM('FIM040', L.LEVY_SE_CD)) AS LEVY_SE_NM
         , L.RDCT_SE_CD     <!-- 감경 구분 코드 -->
         , (GET_CODE_NM('FIM046', L.RDCT_SE_CD)) AS RDCT_SE_NM
         , L.LEVY_PCPTAX    <!-- 부과 본세 -->
         , L.LEVY_ADAMT     <!-- 부과 가산금 -->
         , L.RCVMT_PCPTAX   <!-- 수납 본세 -->
         , L.RCVMT_ADAMT    <!-- 수납 가산금 -->
         , L.RDAMT_PCPTAX   <!-- 감액 본세 -->
         , L.RDAMT_ADAMT    <!-- 감액 가산금 -->
         , L.LAST_AMT       <!-- 최종 금액 -->
         , (L.LEVY_PCPTAX   + L.LEVY_ADAMT)  AS LEVY_AMT    <!-- 부과 금액 -->
         , (L.RCVMT_PCPTAX  + L.RCVMT_ADAMT) AS RCVMT_AMT   <!-- 수납 금액 -->
         , (L.RDAMT_PCPTAX  + L.RDAMT_ADAMT) AS REDUC_AMT   <!-- 감액 금액 -->
         , GET_MINAP_AMT(C.CRDN_ID)          AS MINAP_AMT   <!-- 미납 금액 -->
         , L.LEVY_YMD       <!-- 부과 일자 -->
         , L.FRST_DUDT_YMD  <!-- 최초 납기 일자 -->
         , L.DUDT_YMD       <!-- 납기 일자 -->
         , L.DUDT_AFTR_YMD  <!-- 납기후 금액 -->
         , L.DUDT_AFTR_AMT  <!-- 납기후 일자 -->
         , L.RCVMT_YMD      <!-- 수납 일자 -->
         , L.PAY_MTHD_SE_CD <!-- 납부 방법 구분 코드 -->
         , (GET_CODE_NM('FIM045', L.PAY_MTHD_SE_CD)) AS PAY_MTHD_SE_NM
         , L.EPAYNO         <!-- 전자납부번호 -->
         , L.BANK_NM        <!-- 은행명 -->
         , L.VR_ACTNO       <!-- 가상계좌번호 -->
         , L.SZR_YMD        <!-- 압류 일자 -->
         , L.SZR_RMV_YMD    <!-- 압류 해제 일자 -->
         , V.TXITM_NM       <!-- 세목 명 -->
         , (CASE WHEN C.CRDN_STTS_CD IN ('71','72','73','74','75','76','77','80','81','82','83','84','85','86','87','88')
                 THEN 'Y'
                 ELSE 'N'
            END) AS RCVMT_ETC_YN    <!-- 수납,기타 여부 -->
         , (CASE
            WHEN C.CRDN_STTS_CD IN ('71','72','73','74','75','76','77','80','81','82','83','84','85','86','87','88')
            THEN
                 CASE WHEN C.CRDN_STTS_CD IN ('88')                     THEN '법원이송'
                      WHEN C.CRDN_STTS_CD IN ('71','72','73','74','75') THEN '수납'
                      WHEN C.CRDN_STTS_CD IN ('76','77')                THEN '결손'
                      ELSE '기타'
                  END
            ELSE
                 CASE WHEN C.CRDN_STTS_CD IN ('55')             THEN '압류'
                      WHEN C.CRDN_STTS_CD IN ('53','54')        THEN '압류예정'
                      WHEN C.CRDN_STTS_CD IN ('52')             THEN '독촉'
                      WHEN C.CRDN_STTS_CD IN ('51','61')        THEN '부과'
                      WHEN C.CRDN_STTS_CD IN ('41','42','43')   THEN '자납부과'
                      WHEN C.CRDN_STTS_CD IN ('31')             THEN '의견진술접수'
                      ELSE '단속'
                  END
            END) AS TAB_GROUP   <!-- 탭 그룹 -->
      FROM TB_CRDN             C                                <!-- 단속 대장 -->
     INNER JOIN TB_CRDN_ADI    CA  ON (C.CRDN_ID  = CA.CRDN_ID) <!-- 단속 부가 정보 -->
     INNER JOIN TB_VLTN        V   ON (C.VLTN_ID  = V.VLTN_ID)  <!-- 위반 정보 -->
LEFT OUTER JOIN TB_PAYER       P   ON (C.RTPYR_ID = P.RTPYR_ID) <!-- 납부자 대장 -->
LEFT OUTER JOIN TB_PAYER_ADDR  PA  ON (C.RTPYR_ID = PA.RTPYR_ID AND C.ADDR_SN = PA.ADDR_SN) <!-- 납부자 주소 대장 -->
LEFT OUTER JOIN TB_LEVY        L   ON (C.CRDN_ID  = L.CRDN_ID AND L.DEL_YN = 'N')   <!-- 부과 대장 -->
     WHERE C.DEL_YN     =  'N'
<if test='crdnIds != null'>
       AND C.CRDN_ID    IN (<foreach collection="crdnIds" item="CRDN_ID" separator=",">#{CRDN_ID}</foreach>)
</if>
<if test='sggCd != null'>
       AND C.SGG_CD     =  #{sggCd}
</if>
<if test="taskSeCd != null">
       AND C.TASK_SE_CD =  #{taskSeCd}
</if>

<!-- 통합조회 키워드 -->
<if test="ischKeywordSet != null">
  <foreach collection="ischKeywordSet" item="item">
    <choose>
      <when test="item.name == 'vhrno'">
       AND (C.VHRNO LIKE CONCAT ('%' , #{item.value}, '%') OR L.RPM_SZR_VHRNO LIKE CONCAT ('%' , #{item.value}, '%'))
      </when>
      <when test="item.name == 'rpmSzrVhrno'">
        <choose>
          <when test="item.similar != null">
       AND L.RPM_SZR_VHRNO LIKE CONCAT ('%' , #{item.value} , '%')
          </when>
          <otherwise>
       AND L.RPM_SZR_VHRNO = #{item.value}
          </otherwise>
        </choose>
      </when>
      <when test="item.name == 'crdnVhrno'">
        <choose>
          <when test="item.similar != null">
       AND C.VHRNO LIKE CONCAT ('%' , #{item.value} , '%')
          </when>
          <otherwise>
       AND C.VHRNO = #{item.value}
          </otherwise>
        </choose>
      </when>
      <when test="item.name == 'rtpyrNm'">
        <choose>
          <when test="item.similar != null">
       AND P.RTPYR_NM LIKE CONCAT ('%' , #{item.value} , '%')
          </when>
          <otherwise>
       AND P.RTPYR_NM = #{item.value}
          </otherwise>
        </choose>
      </when>
      <when test="item.name == 'rtpyrNo'">
       AND P.RTPYR_NO = #{item.value}
      </when>
      <when test="item.name == 'rtpyrBrdt'">
       AND P.RTPYR_BRDT = #{item.value} AND P.RTPYR_SE_CD = '01'
      </when>
      <when test="item.name == 'crdnYmd'">
        <if test="item.from != null">
       AND C.CRDN_YMD <![CDATA[ >= ]]> #{item.from}
        </if>
        <if test="item.to != null">
       AND C.CRDN_YMD <![CDATA[ <= ]]> #{item.to}
        </if>
      </when>
      <when test="item.name == 'crdnTm'">
        <if test="item.from != null">
       AND C.CRDN_TM <![CDATA[ >= ]]> #{item.from}
        </if>
        <if test="item.to != null">
       AND C.CRDN_TM <![CDATA[ <= ]]> #{item.to}
        </if>
      </when>
      <when test="item.name == 'rtpyrAddr'">
       AND PA.ADDR LIKE CONCAT ('%', #{item.value}, '%')
      </when>
      <when test="item.name == 'rtpyrDtlAddr'">
       AND PA.DTL_ADDR LIKE CONCAT ('%', #{item.value}, '%')
      </when>
      <when test="item.name == 'gojiNo'">
       AND CONCAT(L.FYR, L.LEVY_NO) = REPLACE(#{item.value}, '-', '')
      </when>
      <when test="item.name == 'epayno'">
       AND L.EPAYNO = #{item.value}
      </when>
      <when test="item.name == 'vltnCd'">
       AND V.VLTN_CD = #{item.value}
      </when>
      <when test="item.name == 'crdnSttsCd'">
       AND C.CRDN_STTS_CD = #{item.value}
      </when>
      <when test="item.name == 'crdnRegSeCd'">
       AND C.CRDN_REG_SE_CD = #{item.value}
      </when>
      <when test="item.name == 'crdnInptSeCd'">
       AND C.CRDN_INPT_SE_CD = #{item.value}
      </when>
      <when test="item.name == 'crdnStdgNm'">
       AND C.CRDN_STDG_NM = #{item.value}
      </when>
      <when test="item.name == 'crdnPlc'">
       AND C.CRDN_PLC LIKE CONCAT ('%' , #{item.value} , '%')
      </when>
    </choose>
  </foreach>
</if>

<!-- 특정자료만 조회 -->
<if test="ischOnlyDataSet != null">
  <foreach collection="ischOnlyDataSet" item="item">
    <choose>
      <!-- 미납자료만 조회 -->
      <when test="item == 'minap'">
       AND C.CRDN_STTS_CD BETWEEN '01' AND '61'
      </when>

    </choose>
  </foreach>
</if>

<!-- 특정자료 제외 -->
<if test="ischExclDataSet != null">
  <foreach collection="ischExclDataSet" item="item">

  </foreach>
</if>

<!-- 동적 검색 -->
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(by) and @org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(term)">
    <choose>
        <when test="mainOption == 'codeValue' or mainOption == 'match' or mainOption == 'ymd'">
            <include refid="dynamicSearch.start" />
            <choose>
                <when test="by == 'taskSeCd'">C.TASE_SE_CD</when>
                <when test="by == 'crdnInptSeCd'">C.CRDN_INPT_SE_CD</when>
                <when test="by == 'crdnYmd'">C.CRDN_YMD</when>
                <when test="by == 'vhrno'">C.VHRNO</when>
                <when test="by == 'rpmSzrVhrno'">L.RPM_SZR_VHRNO</when>
                <when test="by == 'atchFileCnt'">C.ATCH_FILE_CNT</when>
                <when test="by == 'prkPsbltyRsltCd'">CA.PRK_PSBLTY_RSLT_CD</when>
                <when test="by == 'rtpyrNm'">P.RTPYR_NM</when>
                <when test="by == 'rtPyrNo'">P.RTPYR_NO</when>
                <when test="by == 'crdnSttsCd'">C.CRDN_STTS_CD</when>
                <when test="by == 'etcCn'">C.ETC_CN</when>
                <when test="by == 'rdctSeCd'">L.RDCT_SE_CD</when>
                <when test="by == 'gojiNo'">CONCAT(L.FYR, L.LEVY_NO)</when>
                <when test="by == 'crdnSttsChgDt'">C.CRDN_STTS_CHG_YMD</when>
                <when test="by == 'crdnPlc'">C.CRDN_PLC</when>
                <when test="by == 'crdnSeCd'">C.CRDN_SE_CD</when>
                <when test="by == 'opnnSbmsnYn'">IFNULL(C.OPNN_SBMSN_YN,'N')</when>
                <when test="by == 'advntceDudtYmd'">C.ADVNTCE_DUDT_YMD</when>
                <when test="by == 'towngYn'">IFNULL(CA.TOWNG_YN,'N')</when>
                <when test="by == 'szrYmd'">L.SZR_YMD</when>
                <when test="by == 'szrRmvYmd'">L.SZR_RMV_YMD</when>
                <when test="by == 'lastAmt'">L.LAST_AMT</when>
                <when test="by == 'minapAmt'">GET_MINAP_AMT(C.CRDN_ID)</when>
                <when test="by == 'levyAmt'">(L.LEVY_PCPTAX+L.LEVY_ADAMT)</when>
                <when test="by == 'levyPcptax'">L.LEVY_PCPTAX</when>
                <when test="by == 'levyAdamt'">L.LEVY_ADAMT</when>
                <when test="by == 'dudtYmd'">L.DUDT_YMD</when>
                <when test="by == 'dudtAftrYmd'">L.DUDT_AFTR_YMD</when>
                <when test="by == 'dudtAftrAmt'">L.DUDT_AFTR_AMT</when>
                <when test="by == 'vrActno'">L.VR_ACTNO</when>
                <when test="by == 'epayno'">L.EPAYNO</when>
                <when test="by == 'payMthdSeCd'">L.PAY_MTHD_SE_CD</when>
                <when test="by == 'rcvmtYmd'">L.RCVMT_YMD</when>
                <when test="by == 'rcvmtAmt'">(L.RCVMT_PCPTAX+L.RCVMT_ADAMT)</when>
                <when test="by == 'rcvmtPcptax'">L.RCVMT_PCPTAX</when>
                <when test="by == 'rcvmtAdamt'">L.RCVMT_ADAMT</when>
                <when test="by == 'crdnRegSeCd'">C.CRDN_REG_SE_CD</when>
                <when test="by == 'rtpyrFullAddr'">CONCAT(PA.ADDR,' ',PA.DTL_ADDR)</when>
                <when test="by == 'rtpyrSttsCd'">P.RTPYR_STTS_CD</when>
            </choose>
            <include refid="dynamicSearch.center" />#{term}<include refid="dynamicSearch.end" />
        </when>
        <otherwise>
            <if test="by == 'sggCd'"> AND (SELECT COUNT(*) FROM TB_SGG WHERE SGG_NM = #{term}) = 1 </if>
        </otherwise>
    </choose>
</if>

<include refid="utility.orderBy" />

</select>

<select id="selectLatestSndngDtlInfo" parameterType="string" resultType="dataObject">
/* 최근 발송 상세 정보 조회(integrationSearchMapper.selectLatestSndngDtlInfo) */
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY SD.SNDNG_DTL_ID) AS ROWNUM
      , SD.SNDNG_DTL_ID
      , SD.SNDNG_YMD
      , SD.RG_NO
      , SD.CRDN_ID
  FROM TB_SNDNG_DTL SD
 WHERE SD.CRDN_ID = #{crdnId}
   AND SD.DEL_YN = 'N'
) SD2
WHERE SD2.ROWNUM=1
</select>

</mapper>