<?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.crdn.dao.CrdnListMapper">

<select id="selectCrackdownList" parameterType="map" resultType="dataobject">
/* 단속자료 목록 조회(crdnListMapper.selectCrackdownList) */
<include refid="utility.paging-prefix"/>
SELECT C.CRDN_ID               <!-- 단속 ID -->
     , C.SGG_CD                <!-- 시군구 코드 -->
     , S.SGG_NM
     , C.TASK_SE_CD            <!-- 업무 구분 코드 -->
     , CD1.CODE_VAL TASK_SE_NM
     , C.CRDN_REG_SE_CD        <!-- 단속 등록 구분 코드 -->
     , CD2.CODE_VAL CRDN_REG_SE_NM     
     , C.CRDN_INPT_SE_CD       <!-- 단속 입력 구분 코드 -->
     , CD3.CODE_VAL CRDN_INPT_SE_NM
     , C.CVLCPT_LINK_TBL_NM    <!-- 민원 연계 테이블 명 -->
     , C.RTPYR_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         <!-- 대체차량번호 -->
     , C.CRDN_STDG_NM          <!-- 단속 법정동 명 -->
     , C.CRDN_ROAD_NM          <!-- 단속 도로 명 -->
     , C.CRDN_PLC              <!-- 단속 장소 -->
     , C.VLTN_ID               <!-- 위반 ID -->
     , VLTN_ARTCL
     , C.GPS_X                 <!-- GPS X -->
     , C.GPS_Y                 <!-- GPS Y -->
     , C.ATCH_FILE_CNT         <!-- 첨부 파일 수 -->
     , C.VIN                   <!-- 차대번호 -->
     , C.VHCL_NM               <!-- 차량 명 -->
     , C.VHCL_COLR             <!-- 차량 색상 -->
     , C.VHCL_CPCT             <!-- 차량 승차정원 -->
     , C.FFNLG_CRDN_AMT        <!-- 과태료 단속 금액 -->
     , C.FFNLG_RDCRT           <!-- 과태료 감경율 -->
     , C.FFNLG_AMT             <!-- 과태료 금액 -->
     , C.ADVNTCE_BGNG_YMD      <!-- 사전통지 시작 일자 -->
     , C.ADVNTCE_DUDT_YMD      <!-- 사전통지 납기 일자 -->
     , C.ADVNTCE_AMT           <!-- 사전통지 금액 -->
     , C.OPNN_SBMSN_YN         <!-- 의견 제출 여부 -->
     , L.LEVY_ID               <!-- 부과 ID -->
     , (CONCAT(L.FYR, '-', L.LEVY_NO)) AS GOJI_NO <!-- 고지 번호 -->
     , L.EPAYNO                                   <!-- 전자납부번호 -->
     , L.VR_ACTNO                                 <!-- 가상계좌번호 -->
     , (L.LEVY_PCPTAX + L.LEVY_ADAMT) AS LEVY_AMT <!-- 부과 금액 -->
     , L.DUDT_YMD
     , L.RCVMT_YMD
     , C.CRDN_STTS_CD          <!-- 단속 상태 코드 -->
     , CD4.CODE_VAL CRDN_STTS_NM
     , C.CRDN_STTS_CHG_DT      <!-- 단속 상태 변경 일시 -->
     , C.ADTN_AMT              <!-- 가산 금액 -->
     , C.MINUS_AMT             <!-- 감경 금액 -->
     , C.RCVMT_AMT             <!-- 수납 금액 -->
     , C.BFR_CRDN_ID           <!-- 이전 단속 ID -->
	 , CA.CRDN_SE_CD           <!-- 단속 구분 코드 -->
     , CD5.CODE_VAL CRDN_SE_NM
     , CA.DTL_CRDN_PLC         <!-- 상세 단속 장소 -->
     , CA.TEAM_ID              <!-- 조 ID -->
     , T.TEAM_NM               <!-- 조 명 -->
     , CA.CRDN_SPAREA_CD       <!-- 단속 특별구역 코드 -->
     , CD6.CODE_VAL  CRDN_SPAREA_NM
     , CA.CRDN_BGNG_TM         <!-- 단속 시작 시각 -->
     , CA.CRDN_END_TM          <!-- 단속 종료 시간 -->
     , CA.CRDN_SN              <!-- 단속 일련번호 -->
     , CA.MOSC_X               <!-- 모자이크 X -->
     , CA.MOSC_Y               <!-- 모자이크 Y -->
     , CA.TOWNG_YN             <!-- 견인 여부 -->
     , CA.USE_FUEL_CD          <!-- 사용 연료 코드 -->
     , CA.FFNLG_CARMDL_CD      <!-- 과태료 차종 코드 -->
     , CA.PRK_PSBLTY_RSLT_CD   <!-- 주차 가능 결과 코드 -->
     , CD7.CODE_VAL PRK_PSBLTY_RSLT_NM
     , CA.VLTN_NMTM             <!-- 위반 횟수 -->
     , CA.OVTM_YN               <!-- 시간외 여부 -->
	 , CC.CVLCPT_LINK_ID        <!-- 민원 연계 ID -->
     , CC.DSTRBNC_YN            <!-- 방해 여부 -->
     , CC.CVLCPT_RCPT_YMD       <!-- 민원 접수 일자 -->
     , CC.CVLCPT_APLY_SE_CD     <!-- 민원 신청 구분 코드 -->
     , CC.CVLCPT_APLY_NO        <!-- 민원 신청 번호 -->
     , CC.CVLCPT_RCPT_NO        <!-- 민원 접수 번호 -->
     , CC.CVLCPT_LIST_NO        <!-- 민원 목록 번호 -->
     , CC.CVLCPT_APLCNT_NM      <!-- 민원 신청인 명 -->
     , CC.CVLCPT_APLY_DT        <!-- 민원 신청 일시 -->
     , CC.CVLCPT_PRCS_PIC_NM    <!-- 민원 처리 담당자 명 -->
     , CC.CVLCPT_PRCS_PRNMNT_DT <!-- 민원 처리 예정 일시 -->
     , CC.CVLCPT_PRCS_CD        <!-- 민원 처리 코드 -->
     , CC.CVLCPT_PRCS_SMRY      <!-- 민원 처리 요약 -->
     , CC.CVLCPT_PRCS_CMPTN_DT  <!-- 민원 처리 완료 일시 -->
     , CC.CVLCPT_PRCS_PIC       <!-- 민원 처리 담당자 -->
     , CC.CVLCPT_TRSM_DT        <!-- 민원 전송 일시 -->
     , CC.CVLCPT_TRSM_CD        <!-- 민원 전송 코드 -->
     , LE.LEVY_EXCL_RSN_CD      <!-- 부과 제외 사유 코드 -->
     , CD8.CODE_VAL LEVY_EXCL_RSN_NM
     , LE.LEVY_EXCL_YMD         <!-- 부과 제외 일자 -->
     , LE.ETC_CN AS LEVY_EXCL_ETC_CN <!-- 부과 제외 기타 내용 -->
     , P.RTPYR_NM               <!-- 납부자명 -->
     , P.RTPYR_NO               <!-- 납부자번호 -->
     , P.RTPYR_SE_CD
     , PA.WHOL_ADDR AS RTPYR_FULL_ADDR <!-- 납부자주소 -->
     , PA.ZIP       AS RTPYR_ZIP       <!-- 납부자우편번호 -->
     , PA.ADDR      AS RTPYR_ADDR
     , PA.DTL_ADDR  AS RTPYR_DTL_ADDR
     , C.DEL_YN
     , C.RGTR
	 , C.REG_DT
     , C.MDFR
     , C.MDFCN_DT
     , C.DLTR
     , C.DEL_DT
  FROM TB_CRDN C
INNER JOIN      TB_SGG         S   ON (C.SGG_CD = S.SGG_CD)
INNER JOIN      TB_VLTN        V   ON (C.VLTN_ID = V.VLTN_ID)
INNER JOIN      TB_CRDN_ADI    CA  ON (C.CRDN_ID = CA.CRDN_ID)
LEFT OUTER JOIN TB_TEAM        T   ON (CA.TEAM_ID = T.TEAM_ID)
LEFT OUTER JOIN TB_CRDN_CVLCPT CC  ON (C.CVLCPT_LINK_YN = 'Y' AND C.CVLCPT_LINK_ID = CC.CVLCPT_LINK_ID)
LEFT OUTER JOIN TB_LEVY_EXCL   LE  ON (C.CRDN_ID = LE.CRDN_ID AND LE.DEL_YN = 'N')
LEFT OUTER JOIN TB_LEVY        L   ON (C.CRDN_ID = L.CRDN_ID AND L.DEL_YN = 'N')
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)
INNER JOIN      TB_CMN_CODE    CD1 ON (CD1.GRP_ID = 'FIM054' AND CD1.CODE = C.TASK_SE_CD)
INNER JOIN      TB_CMN_CODE    CD2 ON (CD2.GRP_ID = 'FIM026' AND CD2.CODE = C.CRDN_REG_SE_CD)
INNER JOIN      TB_CMN_CODE    CD3 ON (CD3.GRP_ID = 'FIM003' AND CD3.CODE = C.CRDN_INPT_SE_CD)
INNER JOIN      TB_CMN_CODE    CD4 ON (CD4.GRP_ID = 'FIM010' AND CD4.CODE = C.CRDN_STTS_CD)
LEFT OUTER JOIN TB_CMN_CODE    CD5 ON (CD5.GRP_ID = 'FIM002' AND CD5.CODE = CA.CRDN_SE_CD)
LEFT OUTER JOIN TB_CMN_CODE    CD6 ON (CD6.GRP_ID = 'FIM007' AND CD6.CODE = CA.CRDN_SPAREA_CD)
LEFT OUTER JOIN TB_CMN_CODE    CD7 ON (CD7.GRP_ID = 'FIM034' AND CD7.CODE = CA.PRK_PSBLTY_RSLT_CD)
LEFT OUTER JOIN TB_CMN_CODE    CD8 ON (CD8.GRP_ID = 'FIM022' AND CD8.CODE = LE.LEVY_EXCL_RSN_CD)
<where>
<if test="sggCd != null">   AND C.SGG_CD = #{sggCd}</if>
<if test="crdnId != null">   AND C.CRDN_ID = #{crdnId}</if>
<if test="crdnIds != null">   AND C.CRDN_ID IN (<foreach collection="crdnIds" item="CRDN_ID" separator=",">#{CRDN_ID}</foreach>)</if>
<if test="taskSeCd != null">   AND C.TASK_SE_CD = #{taskSeCd}</if>
<if test="vhrno != null">   AND C.VHRNO LIKE CONCAT('%', #{vhrno}, '%')</if>
<if test="crdnInptSeCd != null">   AND C.CRDN_INPT_SE_CD = #{crdnInptSeCd}</if>
<if test="schCrdnYmdFrom != null or schCrdnYmdTo != null">
   <if test="schCrdnYmdFrom != null">AND C.CRDN_YMD <![CDATA[ >= ]]> #{schCrdnYmdFrom} </if>
   <if test="schCrdnYmdTo != null">AND C.CRDN_YMD <![CDATA[ <= ]]> #{schCrdnYmdTo} </if>
</if>
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(crdnSttsCd)">  AND C.CRDN_STTS_CD = #{crdnSttsCd}</if>
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(towngYn)">  AND CA.TOWNG_YN = #{towngYn}</if>
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(epayno)">  AND L.EPAYNO = #{epayno}</if>
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(crdnYmd)">  AND C.CRDN_YMD = #{crdnYmd}</if>
<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(crdnTm)">  AND C.CRDN_TM = #{crdnTm}</if>
<if test="schRegDateFrom != null or schRegDateTo != null">
	<if test="schRegDateOpt == 'regDt'">
		<if test="schRegDateFrom != null"> AND C.REG_DT <![CDATA[>=]]> CONCAT(#{schRegDateFrom},'000000') </if>
		<if test="schRegDateTo != null"> AND C.REG_DT <![CDATA[<=]]> CONCAT(#{schRegDateTo},'235959') </if>
	</if>
	<if test="schRegDateOpt == 'mdfcnDt'">
		<if test="schRegDateFrom != null"> AND C.MDFCN_DT <![CDATA[ >= ]]> CONCAT(#{schRegDateFrom},'000000') </if>
		<if test="schRegDateTo != null"> AND C.MDFCN_DT <![CDATA[ <= ]]> CONCAT(#{schRegDateTo},'235959') </if>
	</if>
</if>
<if test="schRgtrCd != null">
    <if test="schRgtrOpt == 'rgtr'">
        <if test="schRgtrCd != null"> AND C.RGTR = #{schRgtrCd} </if>
 	</if>
    <if test="schRgtrOpt == 'mdfr'">
        <if test="schRgtrCd != null"> AND C.MDFR = #{schRgtrCd} </if>
 	</if>
</if>
<if test="nxrpSendStts != null">
    <if test="nxrpSendStts == '01'">
    AND C.NXRP_CRDN_LINK_YN = 'N' AND C.ATCH_FILE_CNT != 0 AND C.ATCH_FILE_CNT IS NOT NULL
    </if>
    <if test="nxrpSendStts == '02'">
    AND C.NXRP_CRDN_LINK_YN = 'Y'
    </if>
    <if test="nxrpSendStts == '03'">
    AND C.NXRP_CRDN_LINK_YN = 'N' AND (C.ATCH_FILE_CNT = 0 OR C.ATCH_FILE_CNT IS NULL)
    </if>
</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 == 'crdnInptSeCd'">C.CRDN_INPT_SE_CD</when>
				<when test="by == 'vhrno'">C.VHRNO</when>
				<when test="by == 'crdnYmd'">C.CRDN_YMD</when>
				<when test="by == 'crdnPlc'">C.CRDN_PLC</when>
				<when test="by == 'atchFileCnt'">C.ATCH_FILE_CNT</when>
				<when test="by == 'crdnSn'">CA.CRDN_SN</when>
				<when test="by == 'prkPsbltyRsltCd'">CA.PRK_PSBLTY_RSLT_CD</when>
				<when test="by == 'crdnSttsCd'">C.CRDN_STTS_CD</when>
				<when test="by == 'levyExclRsnCd'">LE.LEVY_EXCL_RSN_CD</when>
				<when test="by == 'levyExclYmd'">LE.LEVY_EXCL_YMD</when>
				<when test="by == 'levyExclEtcCn'">LE.ETC_CN</when>
			</choose>
			<include refid="dynamicSearch.center" />#{term}<include refid="dynamicSearch.end" />
		</when>
		<otherwise>
		</otherwise>
	</choose>
</if>
   AND C.DEL_YN = 'N'
</where>
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>

<select id="selectTodayCrackdownList" parameterType="map" resultType="dataobject">
/* 금일 단속자료 목록 조회(crdnListMapper.selectTodayCrackdownList) */
<include refid="utility.paging-prefix"/>
SELECT C.CRDN_ID               <!-- 단속 ID -->
     , C.SGG_CD                <!-- 시군구 코드 -->
     , C.TASK_SE_CD            <!-- 업무 구분 코드 -->
     , C.CRDN_REG_SE_CD        <!-- 단속 등록 구분 코드 -->
     , GET_CODE_NM('FIM026', C.CRDN_REG_SE_CD) AS CRDN_REG_SE_NM
     , C.CRDN_INPT_SE_CD       <!-- 단속 입력 구분 코드 -->
     , GET_CODE_NM('FIM003', C.CRDN_INPT_SE_CD) AS CRDN_INPT_SE_NM
     , CONCAT(C.CRDN_YMD,'', C.CRDN_TM) AS CRDN_YMD_TM <!-- 단속 일시 -->
     , C.VHRNO                 <!-- 차량번호 -->
     , C.CRDN_PLC              <!-- 단속 장소 -->
     , (SELECT VLTN_ARTCL FROM TB_VLTN WHERE VLTN_ID = C.VLTN_ID) AS VLTN_ARTCL
     , C.DEL_YN
     , C.RGTR
	 , C.REG_DT
     , C.MDFR
     , C.MDFCN_DT
     , C.DLTR
     , C.DEL_DT
  FROM TB_CRDN C
 WHERE C.DEL_YN = 'N'
   AND C.SGG_CD = #{sggCd}
   AND C.TASK_SE_CD = #{taskSeCd}
   AND C.RGTR = #{currentUserId}
   AND SUBSTR(C.REG_DT,1,8) = <include refid="utility.today" />
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>


<select id="selectPhotoInspectionDataList" parameterType="map" resultType="dataobject">
/* 단속사진 검사 자료 목록 조회(crdnListMapper.selectPhotoInspectionDataList) */
<include refid="utility.paging-prefix"/>
SELECT C.CRDN_ID                                      <!-- 단속 ID -->
     , C.SGG_CD                                       <!-- 시군구 코드 -->
     , C.TASK_SE_CD                                   <!-- 업무 구분 코드 -->
     , CONCAT(C.CRDN_YMD,'',C.CRDN_TM) AS CRDN_YMD_TM <!-- 단속 일시 -->
     , C.VHRNO                                        <!-- 차량번호 -->
     , CASE WHEN EV.EXMPTN_VHCL_ID IS NOT NULL THEN 'Y' ELSE 'N' END EXMPTN <!-- 면제차량 여부 -->
     , COUNT(*) OVER (PARTITION BY C.VHRNO, C.CRDN_PLC) AS CRDN_CNT <!-- 위반건수 -->
     , '' AS INSP_RSLT
     , '' AS PRCS_MTHD
     , C.CRDN_STDG_NM                                 <!-- 단속 법정동 명 -->
     , C.CRDN_PLC
     , C.ATCH_FILE_CNT                                <!-- 사진건수 -->
     , C.CRDN_STTS_CD                                 <!-- 단속 상태 코드 -->
     , (SELECT GET_CODE_NM('FIM010', C.CRDN_STTS_CD) FROM DUAL) AS CRDN_STTS_NM
     , C.FFNLG_CRDN_AMT                               <!-- 원금액 -->
	 , C.ADVNTCE_AMT                                  <!-- 감경금액 -->
     , C.CRDN_INPT_SE_CD                              <!-- 자료출처 -->
     , (SELECT GET_CODE_NM('FIM003', C.CRDN_INPT_SE_CD) FROM DUAL) AS CRDN_INPT_SE_NM <!-- 자료출처 -->
  FROM TB_CRDN C
 INNER JOIN TB_CRDN_ADI CA ON (C.CRDN_ID = CA.CRDN_ID)
  LEFT OUTER JOIN TB_EXMPTN_VHCL EV ON C.SGG_CD = EV.SGG_CD AND C.VHRNO = EV.VHRNO AND C.CRDN_YMD BETWEEN EV.EXMPTN_BGNG_YMD AND EV.EXMPTN_END_YMD AND EV.DEL_YN = 'N'
 WHERE C.DEL_YN = 'N'
   AND C.SGG_CD = #{sggCd}
   AND C.TASK_SE_CD = #{taskSeCd}
<if test="schCrdnYmdFrom != null or schCrdnYmdTo != null">
	<if test="schCrdnYmdFrom != null">AND C.CRDN_YMD <![CDATA[ >= ]]> #{schCrdnYmdFrom} </if>
	<if test="schCrdnYmdTo != null">AND C.CRDN_YMD <![CDATA[ <= ]]> #{schCrdnYmdTo} </if>
</if>
<if test="crdnInptSeCd != null">AND C.CRDN_INPT_SE_CD = #{crdnInptSeCd}</if>
<if test="vhrno != null">AND C.VHRNO LIKE CONCAT('%', #{vhrno}, '%')</if>
<if test="schExclEnd == 'on'">AND C.CRDN_STTS_CD != '81'</if>
<if test="schOnlyUnpay == 'on'">
<!-- TODO : 미납만 조회 -->
</if>
<if test="taskSeCd == 'PVS'">
    <if test="schExclOvtime == 'on'">
    	AND IFNULL(CA.OVTM_YN,'N') = 'N'
    	AND IFNULL(CA.OVTM_PRTTN_YN,'N') = 'N'
    </if>
</if>
<if test="schRegDateFrom != null or schRegDateTo != null">
	<if test="schRegDateOpt == 'regDt'">
		<if test="schRegDateFrom != null"> AND C.REG_DT <![CDATA[>=]]> CONCAT(#{schRegDateFrom},'000000') </if>
		<if test="schRegDateTo != null"> AND C.REG_DT <![CDATA[<=]]> CONCAT(#{schRegDateTo},'235959') </if>
	</if>
	<if test="schRegDateOpt == 'mdfcnDt'">
		<if test="schRegDateFrom != null"> AND C.MDFCN_DT <![CDATA[ >= ]]> CONCAT(#{schRegDateFrom},'000000') </if>
		<if test="schRegDateTo != null"> AND C.MDFCN_DT <![CDATA[ <= ]]> CONCAT(#{schRegDateTo},'235959') </if>
	</if>
</if>
<if test="schRgtrCd != null">
    <if test="schRgtrOpt == 'rgtr'">
        <if test="schRgtrCd != null"> AND C.RGTR = #{schRgtrCd} </if>
 	</if>
    <if test="schRgtrOpt == 'mdfr'">
        <if test="schRgtrCd != null"> AND C.MDFR = #{schRgtrCd} </if>
 	</if>
</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 == 'crdnYmd'">C.CRDN_YMD</when>
				<when test="by == 'vhrno'">C.VHRNO</when>
				<when test="by == 'crdnStdgNm'">C.CRDN_STDG_NM</when>
				<when test="by == 'crdnPlc'">C.CRDN_PLC</when>
				<when test="by == 'atchFileCnt'">C.ATCH_FILE_CNT</when>
				<when test="by == 'crdnSttsCd'">C.CRDN_STTS_CD</when>
				<when test="by == 'ffnlgCrdnAmt'">C.FFNLG_CRDN_AMT</when>
				<when test="by == 'advntceAmt'">C.ADVNTCE_AMT</when>
				<when test="by == 'crdnInptSeCd'">C.CRDN_INPT_SE_CD</when>
			</choose>
			<include refid="dynamicSearch.center" />#{term}<include refid="dynamicSearch.end" />
		</when>
		<otherwise>
		</otherwise>
	</choose>
</if>
<if test="crdnSttsCd != null">
AND C.CRDN_STTS_CD = #{crdnSttsCd}
</if>
<if test="schDateFrom != null or schDateTo != null">
	<if test="schDateOpt == 'regDt'">
		<if test="schDateFrom != null">AND SUBSTR(C.REG_DT,1,8) <![CDATA[ >= ]]> #{schDateFrom} </if>
		<if test="schDateTo != null">AND SUBSTR(C.REG_DT,1,8) <![CDATA[ <= ]]> #{schDateTo} </if>
	</if>
	<if test="schDateOpt == 'mdfcnDt'">
		<if test="schDateFrom != null">AND SUBSTR(C.MDFCN_DT,1,8) <![CDATA[ >= ]]> #{schDateFrom} </if>
		<if test="schDateTo != null">AND SUBSTR(C.MDFCN_DT,1,8) <![CDATA[ <= ]]> #{schDateTo} </if>
	</if>
</if>
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>

<select id="selectSameVehicleMainList" parameterType="map" resultType="dataobject">
/* 동일 차량 단속 그룹 목록 조회(crdnListMapper.selectSameVehicleMainList) */
<include refid="utility.paging-prefix"/>
SELECT GROUP_CONCAT(C.CRDN_ID) AS CRDN_ID_LIST        <!-- 단속 ID -->
     , C.VHRNO                                        <!-- 차량번호 -->
     , C.RTPYR_ID                                     <!-- 납부자 ID -->
     , C.VLTN_ID                                      <!-- 위반 ID -->
     <choose>
     <when test="schOnlySamePlace == 'on'">
     , C.CRDN_STDG_NM                                 <!-- 단속 법정동 명 -->
     </when>
     <otherwise>
     , '' AS CRDN_STDG_NM
     </otherwise>
     </choose>
     , COUNT(*) AS CRDN_CNT                           <!-- 단속건수 -->
  FROM TB_CRDN C
  INNER JOIN TB_CRDN_ADI CA ON (C.CRDN_ID = CA.CRDN_ID)
  LEFT OUTER JOIN TB_TEAM T ON (CA.TEAM_ID = T.TEAM_ID)
  LEFT OUTER JOIN TB_PAYER P ON (C.RTPYR_ID = P.RTPYR_ID)
 WHERE C.DEL_YN = 'N'
   AND C.SGG_CD = #{sggCd}
   AND C.TASK_SE_CD = #{taskSeCd}
   AND C.VHRNO != ''
<if test="schOnlySamePlace == 'on'">
   AND C.CRDN_STDG_NM != ''
</if>
<if test="schCrdnYmdFrom != null or schCrdnYmdTo != null">
	<if test="schCrdnYmdFrom != null">AND C.CRDN_YMD <![CDATA[ >= ]]> #{schCrdnYmdFrom} </if>
	<if test="schCrdnYmdTo != null">AND C.CRDN_YMD <![CDATA[ <= ]]> #{schCrdnYmdTo} </if>
</if>
<if test="crdnInptSeCd != null">AND C.CRDN_INPT_SE_CD = #{crdnInptSeCd}</if>
<if test="vhrno != null">AND C.VHRNO LIKE CONCAT('%', #{vhrno}, '%')</if>
<if test="schExclEnd == 'on'">AND C.CRDN_STTS_CD != '81'</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 == 'crdnYmd'">C.CRDN_YMD</when>
				<when test="by == 'crdnInptSeCd'">C.CRDN_INPT_SE_CD</when>
				<when test="by == 'teamNm'">T.TEAM_NM</when>
				<when test="by == 'vhrno'">C.VHRNO</when>
				<when test="by == 'crdnStdgNm'">C.CRDN_STDG_NM</when>
				<when test="by == 'crdnPlc'">C.CRDN_PLC</when>
				<when test="by == 'crdnSttsCd'">C.CRDN_STTS_CD</when>
				<when test="by == 'ffnlgCrdnAmt'">C.FFNLG_CRDN_AMT</when>
				<when test="by == 'rtpyrNm'">P.RTPYR_NM</when>
			</choose>
			<include refid="dynamicSearch.center" />#{term}<include refid="dynamicSearch.end" />
		</when>
		<otherwise>
		</otherwise>
	</choose>
</if>
<if test="crdnSttsCd != null">
AND C.CRDN_STTS_CD = #{crdnSttsCd}
</if>
<if test="schDateFrom != null or schDateTo != null">
	<if test="schDateOpt == 'regDt'">
		<if test="schDateFrom != null">AND SUBSTR(C.REG_DT,1,8) <![CDATA[ >= ]]> #{schDateFrom} </if>
		<if test="schDateTo != null">AND SUBSTR(C.REG_DT,1,8) <![CDATA[ <= ]]> #{schDateTo} </if>
	</if>
	<if test="schDateOpt == 'mdfcnDt'">
		<if test="schDateFrom != null">AND SUBSTR(C.MDFCN_DT,1,8) <![CDATA[ >= ]]> #{schDateFrom} </if>
		<if test="schDateTo != null">AND SUBSTR(C.MDFCN_DT,1,8) <![CDATA[ <= ]]> #{schDateTo} </if>
	</if>
</if>
GROUP BY C.VHRNO<if test="schOnlySamePlace == 'on'">, C.CRDN_STDG_NM </if>
HAVING COUNT(*) <![CDATA[ >= ]]>
<choose>
<when test="schCrdnCntFrom != null"> #{schCrdnCntFrom} </when>
<otherwise> 2 </otherwise>
</choose>
<if test="schCrdnCntTo != null"> AND COUNT(*) <![CDATA[ <= ]]> #{schCrdnCntTo} </if>

<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>

<select id="selectSameVehicleSubList" parameterType="map" resultType="dataobject">
/* 동일 차량 단속 자료 목록 조회(crdnListMapper.selectSameVehicleSubList) */
SELECT C.CRDN_ID                                      <!-- 단속 ID -->
     , C.SGG_CD                                       <!-- 시군구 코드 -->
     , C.TASK_SE_CD                                   <!-- 업무 구분 코드 -->
     , CONCAT(C.CRDN_YMD,'',C.CRDN_TM) AS CRDN_YMD_TM <!-- 단속일시 -->
     , C.CRDN_INPT_SE_CD                              <!-- 자료출처 -->
     , (SELECT GET_CODE_NM('FIM003', C.CRDN_INPT_SE_CD) FROM DUAL) AS CRDN_INPT_SE_NM
     , T.TEAM_NM                                      <!-- 단속조 -->
     , C.VHRNO                                        <!-- 차량번호 -->
     , C.RTPYR_ID                                     <!-- 납부자 ID -->
     , C.VLTN_ID                                      <!-- 위반 ID -->
     , C.CRDN_STDG_NM                                 <!-- 단속 법정동 명 -->
     , C.CRDN_PLC                                     <!-- 단속장소 -->
     , C.CRDN_STTS_CD                                 <!-- 처리상태 -->
     , (SELECT GET_CODE_NM('FIM010', C.CRDN_STTS_CD) FROM DUAL) AS CRDN_STTS_NM
     , C.FFNLG_CRDN_AMT                               <!-- 금액 -->
     , P.RTPYR_NM                                     <!-- 성명 -->
     , P.RTPYR_NO                                     <!-- 주민번호 -->
  FROM TB_CRDN C
  INNER JOIN TB_CRDN_ADI CA ON (C.CRDN_ID = CA.CRDN_ID)
  LEFT OUTER JOIN TB_TEAM T ON (CA.TEAM_ID = T.TEAM_ID)
  LEFT OUTER JOIN TB_PAYER P ON (C.RTPYR_ID = P.RTPYR_ID)
 WHERE C.DEL_YN = 'N'
   AND C.SGG_CD = #{sggCd}
   AND C.TASK_SE_CD = #{taskSeCd}
   AND C.CRDN_ID IN (<foreach collection="crdnIds" item="CRDN_ID" separator=",">#{CRDN_ID}</foreach>)
<include refid="utility.orderBy" />
</select>

<select id="selectTagInformationUndefinedDataList" parameterType="map" resultType="dataobject">
/* 표지정보 미확인 자료 목록 조회(crdnListMapper.selectTagInformationUndefinedDataList) */
<include refid="utility.paging-prefix"/>
SELECT C.CRDN_ID                                      <!-- 단속 ID -->
     , C.SGG_CD                                       <!-- 시군구 코드 -->
     , C.TASK_SE_CD                                   <!-- 업무 구분 코드 -->
     , C.CRDN_INPT_SE_CD                              <!-- 단속 입력 구분 코드 -->
     , (SELECT GET_CODE_NM('FIM003', C.CRDN_INPT_SE_CD) FROM DUAL) AS CRDN_INPT_SE_NM
     , CONCAT(C.CRDN_YMD,'',C.CRDN_TM) AS CRDN_YMD_TM <!-- 단속 일시 -->
     , C.VHRNO                                        <!-- 차량번호 -->
     , C.CRDN_STDG_NM                                 <!-- 단속 법정동 명 -->
	 , C.CRDN_STTS_CD                                 <!-- 처리상태 -->
     , C.FFNLG_AMT                                    <!-- 과태료금액 -->
     , C.ADVNTCE_AMT                                  <!-- 사전통지금액 -->
     , C.MINUS_AMT                                    <!-- 감경금액 -->
     , C.ADTN_AMT                                     <!-- 가산금액 -->
     , C.RCVMT_AMT                                    <!-- 수납금액 -->
     , C.ATCH_FILE_CNT                                <!-- 사진건수 -->
     , CA.CRDN_SN                                     <!-- 단속 일련번호 -->
     , 0 AS BLNC                                      <!-- 잔액 -->
  FROM TB_CRDN C
  INNER JOIN TB_CRDN_ADI CA ON (C.CRDN_ID = CA.CRDN_ID)
  LEFT OUTER JOIN TB_TEAM T ON (CA.TEAM_ID = T.TEAM_ID)
  LEFT OUTER JOIN TB_CRDN_CVLCPT CC ON (C.CVLCPT_LINK_YN = 'Y' AND C.CVLCPT_LINK_ID = CC.CVLCPT_LINK_ID)
 WHERE C.DEL_YN = 'N'
   AND C.SGG_CD = #{sggCd}
   AND C.TASK_SE_CD = 'DPV'
   AND CA.PRK_PSBLTY_RSLT_CD = '0'
   AND C.CRDN_STTS_CD = '21'
<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 == '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 == 'crdnStdgNm'">C.CRDN_STDG_NM</when>
				<when test="by == 'atchFileCnt'">C.ATCH_FILE_CNT</when>
				<when test="by == 'crdnSn'">CA.CRDN_SN</when>
			</choose>
			<include refid="dynamicSearch.center" />#{term}<include refid="dynamicSearch.end" />
		</when>
		<otherwise>
		</otherwise>
	</choose>
</if>
<if test="crdnId != null">AND C.CRDN_ID = #{crdnId}</if>
<if test="schCrdnYmdFrom != null or schCrdnYmdTo != null">
	<if test="schCrdnYmdFrom != null">AND C.CRDN_YMD <![CDATA[ >= ]]> #{schCrdnYmdFrom} </if>
	<if test="schCrdnYmdTo != null">AND C.CRDN_YMD <![CDATA[ <= ]]> #{schCrdnYmdTo} </if>
</if>
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>

<select id="countCrdn" parameterType="map" resultType="int">
/* 단속 건수 조회(crdnListMapper.countCrdn) */
    SELECT COUNT(*)
      FROM TB_CRDN C
LEFT OUTER JOIN TB_PAYER P ON (C.RTPYR_ID = P.RTPYR_ID)
     WHERE C.DEL_YN  = 'N'
       AND C.SGG_CD = #{sggCd}
       AND C.CRDN_STTS_CD != '01'
       AND C.TASK_SE_CD = #{taskSeCd}
       AND C.VHRNO = #{vhrno}
</select>

</mapper>