<?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="Arrest">

	<!-- 중국어선 나포처리현황 -->

	<resultMap id="arrestResult" type="arrestVO">
		<result property="arrestId" column="ID" />
		<result property="arrestDate" column="ARST_DATE" />
		<result property="latD" column="LAT_D" />
		<result property="latM" column="LAT_M" />
		<result property="latS" column="LAT_S" />
		<result property="lonD" column="LON_D" />
		<result property="lonM" column="LON_M" />
		<result property="lonS" column="LON_S" />
		<result property="addrArea" column="ADDR_AREA" />
		<result property="addrCity" column="ADDR_CITY" />
		<result property="addrDetail" column="ADDR_DTL" />
		<result property="violateType" column="VLTN_TYPE" />
		<result property="violateDetail" column="VLTN_DTL" />
		<result property="invasionType" column="IVSN_TYPE" />
		<result property="specBatlYn" column="SPEC_BATL_YN" />
		<result property="nllYn" column="NLL_YN" />
		<result property="agencyType" column="AGENCY_TYPE" />
		<result property="agencyGuard" column="AGENCY_GUARD" />
		<result property="agencyVessel" column="AGENCY_VESSEL" />
		<result property="shipName" column="SHIP_NAME" />
		<result property="shipTonnage" column="SHIP_TONNAGE" />
		<result property="shipCrewCnt" column="SHIP_CREW_CNT" />
		<result property="shipQuality" column="SHIP_QLTY" />
		<result property="shipMent" column="SHIP_MENT" />
		<result property="shipPortName" column="SHIP_PORT_NAME" />
		<result property="capName" column="CAP_NAME" />
		<result property="capChnName" column="CAP_CHN_NAME" />
		<result property="capAge" column="CAP_AGE" />
		<result property="shipType" column="SHIP_TYPE" />
		<result property="vioFish" column="VIO_FISH" />
		<result property="vioCnt" column="VIO_CNT" />
		<result property="vioPriatedAmt" column="VIO_PRTD_AMT" />
		<result property="procCondition" column="PROC_COND" />
		<result property="marginPaymentYn" column="MRGN_PYMT_YN" />
		<result property="marginPaymentAmt" column="MRGN_PYMT_AMT" />
		<result property="marginPaymentDate" column="MRGN_PYMT_DATE" />
		<result property="shipProcType" column="SHIP_PROC_TYPE" />
		<result property="commissionCost" column="SHIP_CMSN_COST" />
		<result property="captainCount" column="DTTN_CAP_CNT" />
		<result property="mateCount" column="DTTN_MATE_CNT" />
		<result property="engineerCount" column="DTTN_ENG_CNT" />
		<result property="crewCount" column="DTTN_CREW_CNT" />
		<result property="woDetentionType" column="WO_DTTN_TYPE" />
		<result property="fieldArrestDate" column="FIELD_ARST_DATE" />
		<result property="fieldReleaseDate" column="FIELD_RLS_DATE" />
		<result property="leadDistance" column="LEAD_DISTANCE" />
		<result property="leadHour" column="LEAD_HOUR" />
		<result property="leadMinute" column="LEAD_MINUTE" />
		<result property="gearCast" column="GEAR_CAST" />
		<result property="gearWidth" column="GEAR_WIDTH" />
		<result property="gearEtc" column="GEAR_ETC" />
		<result property="status" column="STATUS" />
		<result property="delYn" column="DEL_YN" />
		<result property="regDate" column="REG_DATE" />
		<result property="regUser" column="REG_USER" />
		<result property="regAgency" column="REG_AGENCY" />
		<result property="agencyGuardStr" column="AGENCY_GUARD_STR" />
		<result property="shipMentStr" column="SHIP_MENT_STR" />
		<result property="shipQualityStr" column="SHIP_QLTY_STR" />
		<result property="shipTypeStr" column="SHIP_TYPE_STR" />
		<result property="woDetentionStr" column="WO_DTTN_STR" />
		<result property="addrStr" column="ADDR_STR" />
		<result property="invasionType1" column="IVSN_TYPE1" />
		<result property="invasionType2" column="IVSN_TYPE2" />
		<result property="invasionType3" column="IVSN_TYPE3" />
		<result property="invasionType4" column="IVSN_TYPE4" />
		<result property="invasionType5" column="IVSN_TYPE5" />
		<result property="shipProcType1" column="SHIP_PROC_TYPE1" />
		<result property="shipProcType2" column="SHIP_PROC_TYPE2" />
		<result property="shipProcType3" column="SHIP_PROC_TYPE3" />
		<result property="shipProcType4" column="SHIP_PROC_TYPE4" />
		<result property="shipProcType5" column="SHIP_PROC_TYPE5" />		
		<result property="fieldCnt" column="FIELD_CNT" />
		<result property="invasion1" column="IVSN_TYPE1" />
		<result property="invasion2" column="IVSN_TYPE2" />
		<result property="invasion3" column="IVSN_TYPE3" />
		<result property="invasion4" column="IVSN_TYPE4" />
		<result property="procCondY" column="PROC_COND_Y" />
		<result property="procCondN" column="PROC_COND_N" />
		<result property="noMarginPaymentAmt" column="NO_MRGN_PYMT_AMT" />
		<result property="notPayment" column="NOT_PAYMENT" />
		<result property="procCondNCnt" column="PROC_COND1" />
		<result property="procCondYCnt" column="PROC_COND2" />
		<result property="shipProcTypeA" column="SHIP_PROC_TYPE_A" />
		<result property="shipProcTypeB" column="SHIP_PROC_TYPE_B" />
		<result property="shipProcTypeC" column="SHIP_PROC_TYPE_C" />
		<result property="shipProcTypeD" column="SHIP_PROC_TYPE_D" />
		<result property="shipProcTypeE" column="SHIP_PROC_TYPE_E" />
		<result property="detentionTotalCnt" column="DTTN_TOTAL_CNT" />
		<result property="noPaymentCnt" column="PYMT_N_CNT" />
		<result property="totalCnt" column="TOTAL_CNT" />
		<result property="marginPaymentAmt" column="PYMT_Y_AMT" />
		<result property="noMarginPaymentAmt" column="PYMT_N_AMT" />
		<result property="specBatlCnt" column="SPEC_BATL_CNT" />
		<result property="nllCnt" column="NLL_CNT" />
		<result property="addrAreaStr" column="ADDR_AREA_STR" />
	</resultMap>

	<resultMap id="arrestCountResult" type="arrestCountVO">
		<result property="particularBanCnt" column="IVSN_TYPE1" />
		<result property="withoutLicenseCnt" column="IVSN_TYPE2" />
		<result property="territWatersCnt" column="IVSN_TYPE3" />
		<result property="eezCnt" column="IVSN_TYPE4" />
		<result property="specBatlCnt" column="SPEC_BATL_CNT" />
		<result property="nllCnt" column="NLL_CNT" />
		<result property="shipCrewCnt" column="SHIP_CREW_CNT" />
		<result property="vioCnt" column="VIO_CNT" />
		<result property="priatedAmt" column="VIO_PRTD_AMT" />
		<result property="procContNCnt" column="PROC_COND1" />
		<result property="procContYCnt" column="PROC_COND2" />
		<result property="paymentAmt" column="PYMT_Y_AMT" />
		<result property="noPaymentAmt" column="PYMT_N_AMT" />
		<result property="noPaymentCnt" column="PYMT_N_CNT" />
		<result property="shipProcTypeACnt" column="SHIP_PROC_TYPE_A" />
		<result property="shipProcTypeBCnt" column="SHIP_PROC_TYPE_B" />
		<result property="shipProcTypeCCnt" column="SHIP_PROC_TYPE_C" />
		<result property="shipProcTypeDCnt" column="SHIP_PROC_TYPE_D" />
		<result property="shipProcTypeECnt" column="SHIP_PROC_TYPE_E" />
		<result property="captainCount" column="DTTN_CAP_CNT" />
		<result property="engineerCount" column="DTTN_ENG_CNT" />
		<result property="mateCount" column="DTTN_MATE_CNT" />
		<result property="crewCount" column="DTTN_CREW_CNT" />
		<result property="commissionCost" column="SHIP_CMSN_COST" />
		<result property="gearCast" column="GEAR_CAST" />
		<result property="gearWidth" column="GEAR_WIDTH" />
		<result property="fieldCnt" column="FIELD_CNT" />
		<result property="totalCnt" column="TOTAL_CNT" />
		
		
	</resultMap>

	<!-- 코드 -->
	<resultMap id="codeResult" type="CodeVO">
		<result property="code1" column="CODE1" />
		<result property="code2" column="CODE2" />
		<result property="codenm" column="CODENM" />
		<result property="codenmYak" column="CODENM_YAK" />
		<result property="codeUseYN" column="CODE_USE_YN" />
	</resultMap>



	<select id="Arrest.getAddressAreaList" parameterType="String"
		resultMap="codeResult">
	<![CDATA[
		SELECT 
        	CODE AS CODE1, UP_CODE AS CODE2, CODE_NM AS CODENM, CODE_NM AS CODENM_YAK, USE_YN AS CODE_USE_YN
	    FROM
	    	CD_CODE
	    WHERE
	    	GP_CODE = 'A001' AND UP_CODE = #{area}
	    		
	    ORDER BY
	    	UPPER(CODE)
	]]>
	</select>






	<select id="getArrestListAll" parameterType="arrestVO" resultMap="arrestResult">
	<![CDATA[
		SELECT
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	,
			A.LAT_M	,
			A.LAT_S	,
			A.LON_D	,
			A.LON_M	,
			A.LON_S	,
			A.ADDR_AREA	,
			A.ADDR_CITY	,
      		C.CODE_NM ADDR_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			A.IVSN_TYPE	,
			A.SPEC_BATL_YN	,
			A.NLL_YN	,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			G.CODENM AGENCY_GUARD_STR ,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			A.SHIP_MENT	,      
      D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME	,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
			A.MRGN_PYMT_YN	,
			A.MRGN_PYMT_AMT	,
			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD hh24:mi') MRGN_PYMT_DATE	,
			A.SHIP_PROC_TYPE	,
			A.SHIP_CMSN_COST	,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			DECODE(A.FIELD_ARST_DATE, NULL, 0, 1) FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM T_CHN_ARREST_INFO A,
           TCODE B ,  -- 위반내용
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,  -- 시군구
           TCODE D ,  -- 선적
           TCODE E ,  -- 선종
           TCODE F ,  -- 무혐의불구속
           TCODE G
		 WHERE DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND (A.WO_DTTN_TYPE = F.CODE2 AND F.CODE1 = 'T005')
       AND (A.AGENCY_GUARD = G.CODE2 AND G.CODE1 = 'C001')
       AND A.NLL_YN = 'Y'
       AND A.SPEC_BATL_YN = 'Y'
		]]>
	</select>


	<select id="getArrestList" parameterType="arrestSearchVO" resultMap="arrestResult">
	<![CDATA[
		SELECT /*+IDX_T_CHN_ARREST_INFO*/
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	,
			A.LAT_M	,
			A.LAT_S	,
			A.LON_D	,
			A.LON_M	,
			A.LON_S	,
			A.ADDR_AREA	,
			A.ADDR_CITY	,
      			C.CODE_NM ADDR_STR,
			I.CODE_NM ADDR_AREA_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			case A.IVSN_TYPE when 'YRB2' then '1' else '' end IVSN_TYPE1,
			case A.IVSN_TYPE when 'YRD1' then '1' else '' end IVSN_TYPE2,
			case A.IVSN_TYPE when 'YRA1' then '1' else '' end IVSN_TYPE3,
			case A.IVSN_TYPE when 'YRB1' then '1' else '' end IVSN_TYPE4,
			case A.SPEC_BATL_YN when 'Y' then '1' else '' end SPEC_BATL_YN,
			case A.NLL_YN when 'Y' then '1' else '' end  NLL_YN,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			G.CODENM AGENCY_GUARD_STR ,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			H.CODENM SHIP_QLTY_STR,
			A.SHIP_MENT	,      
      D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME  CAP_NAME,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
		    case A.PROC_COND when '02' then '1' else '' end PROC_COND_Y,
		    case A.PROC_COND when '01' then '1' else '' end PROC_COND_N,
			A.MRGN_PYMT_YN	,
		    case when A.MRGN_PYMT_YN != null then 'Y' end MRGN_PYMT_AMT,
			case when A.MRGN_PYMT_AMT != null then '' end MRGN_PYMT_AMT,
			case when A.MRGN_PYMT_YN != null then 'N' end NO_MRGN_PYMT_AMT,
			case when A.MRGN_PYMT_AMT != null then '' end NO_MRGN_PYMT_AMT,
			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD hh24:mi') MRGN_PYMT_DATE ,
			case A.MRGN_PYMT_YN when 'N' then '1' else '' end NOT_PAYMENT,
			A.SHIP_PROC_TYPE ,
			case A.SHIP_PROC_TYPE when '01' then '1' else '' end SHIP_PROC_TYPE_A,
			case A.SHIP_PROC_TYPE when '02' then '1' else '' end SHIP_PROC_TYPE_B,
			case A.SHIP_PROC_TYPE when '03' then '1' else '' end SHIP_PROC_TYPE_C,
			case A.SHIP_PROC_TYPE when '04' then '1' else '' end SHIP_PROC_TYPE_D,
			case A.SHIP_PROC_TYPE when '05' then '1' else '' end SHIP_PROC_TYPE_E,

			A.SHIP_CMSN_COST,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.DTTN_CAP_CNT + A.DTTN_MATE_CNT + A.DTTN_ENG_CNT + A.DTTN_CREW_CNT DTTN_TOTAL_CNT,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			case A.FIELD_ARST_DATE when NULL then '' else '1' end FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM
			T_CHN_ARREST_INFO A
			right outer join
			(SELECT CODE1, CODE2, CODENM
			FROM TCODE WHERE CODE1 = 'C001') G
			on g.code2 = a.agency_guard,
           TCODE B ,
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,
           TCODE D ,
           TCODE E ,
           (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'T005' ) F ,
	   (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'T004' ) H ,
	   (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' AND UP_CODE ='0000' ) I
      WHERE A.DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND A.WO_DTTN_TYPE = F.CODE2 
       AND A.SHIP_QLTY = H.CODE2
       AND A.ADDR_AREA = I.CODE
		]]>
		<if test="startDate != null and startDate != ''"><![CDATA[
			AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="endDate != null and endDate != ''"><![CDATA[
			AND A.ARST_DATE <= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="agencyType != null and agencyType != ''"><![CDATA[
			AND A.AGENCY_TYPE = #{agencyType}
		]]></if>
		<if test="agencyGuard != null and agencyGuard != ''"><![CDATA[
			AND A.AGENCY_GUARD = #{agencyGuard}
		]]></if>
		<if test="agencyVessel != null and agencyVessel != ''"><![CDATA[
			AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
		]]></if>
		<if test="addrArea != null and addrArea != ''"><![CDATA[
			AND A.ADDR_AREA = #{addrArea}
		]]></if>
		<if test="addrCity != null and addrCity != ''"><![CDATA[
			AND A.ADDR_CITY = #{addrCity}
		]]></if>
		<if test="violateType != null and violateType != ''"><![CDATA[
			AND A.VLTN_TYPE = #{violateType}
		]]></if>
		<if test="shipName != null and shipName != ''"><![CDATA[
			AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
		]]></if>
		
		<if test="specBatlYn != null and specBatlYn.length > 0">
			AND A.SPEC_BATL_YN IN
			<foreach collection="specBatlYn" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>		
		<if test="invasionType != null and invasionType.length > 0">
			AND A.IVSN_TYPE IN
			<foreach collection="invasionType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="invasionType == null or invasionType.length == 0">
			AND 1 = 0
		</if>
		<if test="shipType != null and shipType.length > 0">
			AND A.SHIP_TYPE IN
			<foreach collection="shipType" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>		
		<if test="shipType == null or shipType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="procCondition != null and procCondition.length > 0">
			AND A.PROC_COND IN
			<foreach collection="procCondition" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="procCondition == null or procCondition.length == 0">
			AND 1 = 0
		</if>
		
		<if test="arrShipTonnage != null and arrShipTonnage.length > 0">
			<choose>
				<when test="arrShipTonnage.length == 1 and arrShipTonnage[0] == '01'"><![CDATA[
					AND A.SHIP_TONNAGE < 50
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')"><![CDATA[
					AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE < 100
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')"><![CDATA[
					AND A.SHIP_TONNAGE >= 100
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR A.SHIP_TONNAGE >= 100 )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<otherwise>
					AND 1 = 1
				</otherwise>
			</choose>
		</if>
		<if test="arrShipTonnage == null or arrShipTonnage.length == 0">
			AND 1 = 0
		</if>
		
		<if test="marginPayment != null and marginPayment.length > 0">
			AND A.MRGN_PYMT_YN IN
			<foreach collection="marginPayment" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="marginPayment == null or marginPayment.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipProcType != null and shipProcType.length > 0">
			AND A.SHIP_PROC_TYPE IN
			<foreach collection="shipProcType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipProcType == null or shipProcType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipQuality != null and shipQuality.length > 0">
			AND A.SHIP_QLTY IN
			<foreach collection="shipQuality" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipQuality == null or shipQuality.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipMent != null and shipMent.length > 0">
			AND A.SHIP_MENT IN
			<foreach collection="shipMent" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipMent == null or shipMent.length == 0">
			AND 1 = 0
		</if>		
	</select>

	<select id="getArrestListExcel" parameterType="arrestSearchVO" resultMap="arrestResult">
	<![CDATA[
		SELECT /*+IDX_T_CHN_ARREST_INFO*/
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	|| '˚ ' || A.LAT_M ||'´ ' ||  A.LAT_S||'´ ' || A.LON_D	|| '˚ ' || A.LON_M ||'´ ' ||  A.LON_S||'´ ' || '(' || I.CODE_NM || ' ' || C.CODE_NM || ') ' || ADDR_DTL ADDR_STR,			
      			C.CODE_NM ADDR_STR,			
			I.CODE_NM ADDR_AREA_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			DECODE(A.IVSN_TYPE, 'YRB2', '1', '') IVSN_TYPE1,
			DECODE(A.IVSN_TYPE, 'YRD1', '1', '') IVSN_TYPE2,
			DECODE(A.IVSN_TYPE, 'YRA1', '1', '') IVSN_TYPE3,
			DECODE(A.IVSN_TYPE, 'YRB1', '1', '') IVSN_TYPE4,



			DECODE(A.SPEC_BATL_YN, 'Y', '1' , '') SPEC_BATL_YN,
			DECODE(A.NLL_YN, 'Y', '1' , '') NLL_YN,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			DECODE(A.AGENCY_TYPE, '01', '해경', '02', '해수부', '해군') || ' ' || G.CODENM AGENCY_GUARD_STR,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			H.CODENM SHIP_QLTY_STR,
			A.SHIP_MENT	,      
      D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME || '(' || A.CAP_AGE || ')' CAP_NAME,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
			DECODE(A.PROC_COND, '02', '1', '') PROC_COND_Y,
			DECODE(A.PROC_COND, '01', '1', '') PROC_COND_N,
			A.MRGN_PYMT_YN	,
					
			DECODE(A.MRGN_PYMT_YN, 'Y', A.MRGN_PYMT_AMT, '' ) MRGN_PYMT_AMT,
			DECODE(A.MRGN_PYMT_YN, 'N', A.MRGN_PYMT_AMT, '' ) NO_MRGN_PYMT_AMT,
			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD hh24:mi') MRGN_PYMT_DATE	,
			DECODE(A.MRGN_PYMT_YN, 'N', '1', '') NOT_PAYMENT,
			A.SHIP_PROC_TYPE	,
			DECODE(A.SHIP_PROC_TYPE, '01', '1', '') SHIP_PROC_TYPE_A,
			DECODE(A.SHIP_PROC_TYPE, '02', '1', '') SHIP_PROC_TYPE_B,
			DECODE(A.SHIP_PROC_TYPE, '03', '1', '') SHIP_PROC_TYPE_C,
			DECODE(A.SHIP_PROC_TYPE, '04', '1', '') SHIP_PROC_TYPE_D,
			DECODE(A.SHIP_PROC_TYPE, '05', '1', '') SHIP_PROC_TYPE_E,
			
			A.SHIP_CMSN_COST,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.DTTN_CAP_CNT + A.DTTN_MATE_CNT + A.DTTN_ENG_CNT + A.DTTN_CREW_CNT DTTN_TOTAL_CNT,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			DECODE(A.FIELD_ARST_DATE, NULL, '', '1') FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM T_CHN_ARREST_INFO A,
           TCODE B ,  -- 위반내용
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,  -- 시군구
           TCODE D ,  -- 선적
           TCODE E ,  -- 선종
           (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'T005' ) F , -- 무혐의불구속
           (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'C001' ) G ,   --
	   (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'T004' ) H ,    -- 선질
	   (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' AND UP_CODE ='0000' ) I  -- 특별광역시도
      WHERE A.DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND A.WO_DTTN_TYPE = F.CODE2 
       AND G.CODE2(+) = A.AGENCY_GUARD
       AND A.SHIP_QLTY = H.CODE2
       AND A.ADDR_AREA = I.CODE
		]]>
		<if test="startDate != null and startDate != ''"><![CDATA[
			AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="endDate != null and endDate != ''"><![CDATA[
			AND A.ARST_DATE <= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="agencyType != null and agencyType != ''"><![CDATA[
			AND A.AGENCY_TYPE = #{agencyType}
		]]></if>
		<if test="agencyGuard != null and agencyGuard != ''"><![CDATA[
			AND A.AGENCY_GUARD = #{agencyGuard}
		]]></if>
		<if test="agencyVessel != null and agencyVessel != ''"><![CDATA[
			AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
		]]></if>
		<if test="addrArea != null and addrArea != ''"><![CDATA[
			AND A.ADDR_AREA = #{addrArea}
		]]></if>
		<if test="addrCity != null and addrCity != ''"><![CDATA[
			AND A.ADDR_CITY = #{addrCity}
		]]></if>
		<if test="violateType != null and violateType != ''"><![CDATA[
			AND A.VLTN_TYPE = #{violateType}
		]]></if>
		<if test="shipName != null and shipName != ''"><![CDATA[
			AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
		]]></if>
		<if test="nllYn != null and nllYn.length > 0">
			AND A.NLL_YN IN
			<foreach collection="nllYn" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="specBatlYn != null and specBatlYn.length > 0">
			AND A.SPEC_BATL_YN IN
			<foreach collection="specBatlYn" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="invasionType != null and invasionType.length > 0">
			AND A.IVSN_TYPE IN
			<foreach collection="invasionType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="invasionType == null or invasionType.length == 0">
			AND 1 = 0
		</if>
		
		
		
		<if test="shipType != null and shipType.length > 0">
			AND A.SHIP_TYPE IN
			<foreach collection="shipType" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>		
		<if test="shipType == null or shipType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="procCondition != null and procCondition.length > 0">
			AND A.PROC_COND IN
			<foreach collection="procCondition" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="procCondition == null or procCondition.length == 0">
			AND 1 = 0
		</if>
		
		<if test="arrShipTonnage != null and arrShipTonnage.length > 0">
			<choose>
				<when test="arrShipTonnage.length == 1 and arrShipTonnage[0] == '01'"><![CDATA[
					AND A.SHIP_TONNAGE < 50
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')"><![CDATA[
					AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE < 100
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')"><![CDATA[
					AND A.SHIP_TONNAGE >= 100
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR A.SHIP_TONNAGE >= 100 )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<otherwise>
					AND 1 = 1
				</otherwise>
			</choose>
		</if>
		<if test="arrShipTonnage == null or arrShipTonnage.length == 0">
			AND 1 = 0
		</if>
		
		<if test="marginPayment != null and marginPayment.length > 0">
			AND A.MRGN_PYMT_YN IN
			<foreach collection="marginPayment" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="marginPayment == null or marginPayment.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipProcType != null and shipProcType.length > 0">
			AND A.SHIP_PROC_TYPE IN
			<foreach collection="shipProcType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipProcType == null or shipProcType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipQuality != null and shipQuality.length > 0">
			AND A.SHIP_QLTY IN
			<foreach collection="shipQuality" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipQuality == null or shipQuality.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipMent != null and shipMent.length > 0">
			AND A.SHIP_MENT IN
			<foreach collection="shipMent" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipMent == null or shipMent.length == 0">
			AND 1 = 0
		</if>		
	</select>
	
	

	<select id="getArrestListAllCount" parameterType="arrestVO"
		resultMap="arrestCountResult">
	<![CDATA[
	SELECT SUM(IVSN_TYPE1) IVSN_TYPE1,
		   SUM(IVSN_TYPE2) IVSN_TYPE2,
		   SUM(IVSN_TYPE3) IVSN_TYPE3,
		   SUM(IVSN_TYPE4) IVSN_TYPE4,
		   SUM(SPEC_BATL) SPEC_BATL_YN,
		   SUM(NLL) NLL_YN,
		   SUM(SHIP_CREW_CNT) SHIP_CREW_CNT,
		   SUM(VIO_CNT) VIO_CNT,
		   SUM(VIO_PRTD_AMT) VIO_PRTD_AMT,
		   
		   SUM(PROC_COND1) PROC_COND1,
		   SUM(PROC_COND2) PROC_COND2,
		   
		   SUM(PYMT_Y_AMT) PYMT_Y_AMT,
		   SUM(PYMT_N_AMT) PYMT_N_AMT, 
		   SUM(PYMT_N_CNT) PYMT_N_CNT,
		   
		   SUM(SHIP_PROC_TYPE_A) SHIP_PROC_TYPE_A,
		   SUM(SHIP_PROC_TYPE_B) SHIP_PROC_TYPE_B,
		   SUM(SHIP_PROC_TYPE_C) SHIP_PROC_TYPE_C,
		   SUM(SHIP_PROC_TYPE_D) SHIP_PROC_TYPE_D,
		   SUM(SHIP_PROC_TYPE_E) SHIP_PROC_TYPE_E,
		   
		   SUM(DTTN_CAP_CNT) DTTN_CAP_CNT,
		   SUM(DTTN_ENG_CNT) DTTN_ENG_CNT,
		   SUM(DTTN_MATE_CNT) DTTN_MATE_CNT,
		   SUM(DTTN_CREW_CNT) DTTN_CREW_CNT,
		   
		   SUM(SHIP_CMSN_COST) SHIP_CMSN_COST,
		   SUM(GEAR_CAST) GEAR_CAST,
		   SUM(GEAR_WIDTH) GEAR_WIDTH,
		   SUM(FIELD_CNT) FIELD_CNT,
		   COUNT(*) TOTAL_CNT
	 FROM	   
	   (
		SELECT
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	,
			A.LAT_M	,
			A.LAT_S	,
			A.LON_D	,
			A.LON_M	,
			A.LON_S	,
			A.ADDR_AREA	,
			A.ADDR_CITY	,
      		C.CODE_NM ADDR_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			A.IVSN_TYPE	,
			DECODE(A.IVSN_TYPE, 'YRB2', 1, 0) IVSN_TYPE1,
			DECODE(A.IVSN_TYPE, 'YRD1', 1, 0) IVSN_TYPE2,
			DECODE(A.IVSN_TYPE, 'YRA1', 1, 0) IVSN_TYPE3,
			DECODE(A.IVSN_TYPE, 'YRB1', 1, 0) IVSN_TYPE4,
			
			DECODE(A.SPEC_BATL_YN , 'Y', 1, 0) SPEC_BATL,
			DECODE(A.NLL_YN	, 'Y', 1, 0) NLL,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			G.CODENM AGENCY_GUARD_STR ,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			A.SHIP_MENT	,      
      		D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME	,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      		E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
			DECODE(A.PROC_COND, '01', 1, 0) PROC_COND1,
			DECODE(A.PROC_COND, '02', 1, 0) PROC_COND2,
			A.MRGN_PYMT_YN	,
			DECODE(A.MRGN_PYMT_YN, 'N', 1, 0) PYMT_N_CNT,
			A.MRGN_PYMT_AMT	,
			DECODE(MRGN_PYMT_YN, 'Y', MRGN_PYMT_AMT, 0) PYMT_Y_AMT,
			DECODE(MRGN_PYMT_YN, 'N', MRGN_PYMT_AMT, 0) PYMT_N_AMT,
			
			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD') MRGN_PYMT_DATE	,
			A.SHIP_PROC_TYPE	,
			DECODE(A.SHIP_PROC_TYPE, '01', 1, 0) SHIP_PROC_TYPE_A,
			DECODE(A.SHIP_PROC_TYPE, '02', 1, 0) SHIP_PROC_TYPE_B,
			DECODE(A.SHIP_PROC_TYPE, '03', 1, 0) SHIP_PROC_TYPE_C,
			DECODE(A.SHIP_PROC_TYPE, '04', 1, 0) SHIP_PROC_TYPE_D,
			DECODE(A.SHIP_PROC_TYPE, '05', 1, 0) SHIP_PROC_TYPE_E,
			
			A.SHIP_CMSN_COST	,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			DECODE(A.FIELD_ARST_DATE, NULL, 0, 1) FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM T_CHN_ARREST_INFO A,
           TCODE B ,  -- 위반내용
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,  -- 시군구
           TCODE D ,  -- 선적
           TCODE E ,  -- 선종
           TCODE F ,  -- 무혐의불구속
           TCODE G
		 WHERE DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND (A.WO_DTTN_TYPE = F.CODE2 AND F.CODE1 = 'T005')
       AND (A.AGENCY_GUARD = G.CODE2 AND G.CODE1 = 'C001')
       AND A.NLL_YN = 'Y'
       AND A.SPEC_BATL_YN = 'Y'
       )
		]]>
	</select>
	
	<select id="getArrestListCount" parameterType="arrestSearchVO"
		resultMap="arrestResult">
	<![CDATA[
	SELECT /*+IDX_T_CHN_ARREST_INFO*/
	       SUM(IVSN_TYPE1) IVSN_TYPE1,	 
		   SUM(IVSN_TYPE2) IVSN_TYPE2,
		   SUM(IVSN_TYPE3) IVSN_TYPE3,
		   SUM(IVSN_TYPE4) IVSN_TYPE4,
		   SUM(SPEC_BATL) SPEC_BATL_CNT,
		   SUM(NLL) NLL_CNT,
		   SUM(SHIP_CREW_CNT) SHIP_CREW_CNT,
		   SUM(VIO_CNT) VIO_CNT,
		   SUM(VIO_PRTD_AMT) VIO_PRTD_AMT,
		   
		   SUM(PROC_COND1) PROC_COND_N,
		   SUM(PROC_COND2) PROC_COND_Y,
		   
		   SUM(MRGN_PYMT_AMT) PYMT_Y_AMT,
		   SUM(NO_MRGN_PYMT_AMT) PYMT_N_AMT, 
		   SUM(PYMT_N_CNT) NOT_PAYMENT,
		   
		   SUM(SHIP_PROC_TYPE_A) SHIP_PROC_TYPE_A,
		   SUM(SHIP_PROC_TYPE_B) SHIP_PROC_TYPE_B,
		   SUM(SHIP_PROC_TYPE_C) SHIP_PROC_TYPE_C,
		   SUM(SHIP_PROC_TYPE_D) SHIP_PROC_TYPE_D,
		   SUM(SHIP_PROC_TYPE_E) SHIP_PROC_TYPE_E,
		   
		   SUM(DTTN_CAP_CNT) DTTN_CAP_CNT,
		   SUM(DTTN_ENG_CNT) DTTN_ENG_CNT,
		   SUM(DTTN_MATE_CNT) DTTN_MATE_CNT,
		   SUM(DTTN_CREW_CNT) DTTN_CREW_CNT,
		   
		   
		   
		   SUM(SHIP_CMSN_COST) SHIP_CMSN_COST,
		   SUM(GEAR_CAST) GEAR_CAST,
		   SUM(GEAR_WIDTH) GEAR_WIDTH,
		   SUM(FIELD_CNT) FIELD_CNT,
		   COUNT(*) TOTAL_CNT
	 FROM	   
	   (
		SELECT
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	,
			A.LAT_M	,
			A.LAT_S	,
			A.LON_D	,
			A.LON_M	,
			A.LON_S	,
			A.ADDR_AREA	,
			A.ADDR_CITY	,
      		C.CODE_NM ADDR_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			A.IVSN_TYPE	,
			case A.IVSN_TYPE when 'YRB2' then 1 else 0 end IVSN_TYPE1,
			case A.IVSN_TYPE when 'YRD1' then 1 else 0 end IVSN_TYPE2,
			case A.IVSN_TYPE when 'YRA1' then 1 else 0 end IVSN_TYPE3,
			case A.IVSN_TYPE when 'YRB1' then 1 else 0 end IVSN_TYPE4,
			case A.SPEC_BATL_YN when 'Y' then 1 else 0 end SPEC_BATL,
			case A.NLL_YN when 'Y' then 1 else 0 end  NLL,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			G.CODENM AGENCY_GUARD_STR ,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			A.SHIP_MENT	,      
      		D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME	,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      		E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
			case A.PROC_COND when '01' then 1 else 0 end PROC_COND1,
			case A.PROC_COND when '02' then 1 else 0 end PROC_COND2,
			A.MRGN_PYMT_YN	,
			case MRGN_PYMT_YN
				when 'Y' then MRGN_PYMT_AMT
				else 0
			end MRGN_PYMT_AMT,
			case MRGN_PYMT_YN
				when 'N' then MRGN_PYMT_AMT
				else 0
			end NO_MRGN_PYMT_AMT,
			case A.MRGN_PYMT_YN when 'N' then 1 else 0 end PYMT_N_CNT,

			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD') MRGN_PYMT_DATE	,
			A.SHIP_PROC_TYPE	,
			case A.SHIP_PROC_TYPE when '01' then 1 else 0 end SHIP_PROC_TYPE_A,
			case A.SHIP_PROC_TYPE when '02' then 1 else 0 end SHIP_PROC_TYPE_B,
			case A.SHIP_PROC_TYPE when '03' then 1 else 0 end SHIP_PROC_TYPE_C,
			case A.SHIP_PROC_TYPE when '04' then 1 else 0 end SHIP_PROC_TYPE_D,
			case A.SHIP_PROC_TYPE when '05' then 1 else 0 end SHIP_PROC_TYPE_E,
			
			A.SHIP_CMSN_COST	,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			case A.FIELD_ARST_DATE when NULL then 0 else 1 end FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM T_CHN_ARREST_INFO A
			right outer join
			(SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'C001' ) G on G.CODE2 = A.AGENCY_GUARD,
           TCODE B ,  -- 위반내용
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,  -- 시군구
           TCODE D ,  -- 선적
           TCODE E ,  -- 선종
           (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'T005' ) F -- 무혐의 불구속

		 WHERE A.DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND A.WO_DTTN_TYPE = F.CODE2
		]]>
		<if test="startDate != null and startDate != ''"><![CDATA[
			AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="endDate != null and endDate != ''"><![CDATA[
			AND A.ARST_DATE <= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="agencyType != null and agencyType != ''"><![CDATA[
			AND A.AGENCY_TYPE = #{agencyType}
		]]></if>
		<if test="agencyGuard != null and agencyGuard != ''"><![CDATA[
			AND A.AGENCY_GUARD = #{agencyGuard}
		]]></if>
		<if test="agencyVessel != null and agencyVessel != ''"><![CDATA[
			AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
		]]></if>
		<if test="addrArea != null and addrArea != ''"><![CDATA[
			AND A.ADDR_AREA = #{addrArea}
		]]></if>
		<if test="addrCity != null and addrCity != ''"><![CDATA[
			AND A.ADDR_CITY = #{addrCity}
		]]></if>
		<if test="violateType != null and violateType != ''"><![CDATA[
			AND A.VLTN_TYPE = #{violateType}
		]]></if>
		<if test="shipName != null and shipName != ''"><![CDATA[
			AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
		]]></if><if test="nllYn != null and nllYn.length > 0">
			AND A.NLL_YN IN
			<foreach collection="nllYn" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="specBatlYn != null and specBatlYn.length > 0">
			AND A.SPEC_BATL_YN IN
			<foreach collection="specBatlYn" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>	<if test="invasionType != null and invasionType.length > 0">
			AND A.IVSN_TYPE IN
			<foreach collection="invasionType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="invasionType == null or invasionType.length == 0">
			AND 1 = 0
		</if>
		
		
		
		<if test="shipType != null and shipType.length > 0">
			AND A.SHIP_TYPE IN
			<foreach collection="shipType" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>		
		<if test="shipType == null or shipType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="procCondition != null and procCondition.length > 0">
			AND A.PROC_COND IN
			<foreach collection="procCondition" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="procCondition == null or procCondition.length == 0">
			AND 1 = 0
		</if>
		
		<if test="arrShipTonnage != null and arrShipTonnage.length > 0">
			<choose>
				<when test="arrShipTonnage.length == 1 and arrShipTonnage[0] == '01'"><![CDATA[
					AND A.SHIP_TONNAGE < 50
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')"><![CDATA[
					AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE < 100
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')"><![CDATA[
					AND A.SHIP_TONNAGE >= 100
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR A.SHIP_TONNAGE >= 100 )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<otherwise>
					AND 1 = 1
				</otherwise>
			</choose>
		</if>
		<if test="arrShipTonnage == null or arrShipTonnage.length == 0">
			AND 1 = 0
		</if>
		
		<if test="marginPayment != null and marginPayment.length > 0">
			AND A.MRGN_PYMT_YN IN
			<foreach collection="marginPayment" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="marginPayment == null or marginPayment.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipProcType != null and shipProcType.length > 0">
			AND A.SHIP_PROC_TYPE IN
			<foreach collection="shipProcType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipProcType == null or shipProcType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipQuality != null and shipQuality.length > 0">
			AND A.SHIP_QLTY IN
			<foreach collection="shipQuality" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipQuality == null or shipQuality.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipMent != null and shipMent.length > 0">
			AND A.SHIP_MENT IN
			<foreach collection="shipMent" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipMent == null or shipMent.length == 0">
			AND 1 = 0
		</if>		
		<![CDATA[
		) list
		]]>
	</select>
	
	<select id="getArrestListCountExcel" parameterType="arrestSearchVO"
		resultMap="arrestResult">
	<![CDATA[
	SELECT /*+IDX_T_CHN_ARREST_INFO*/
	       SUM(IVSN_TYPE1) IVSN_TYPE1,	 
		   SUM(IVSN_TYPE2) IVSN_TYPE2,
		   SUM(IVSN_TYPE3) IVSN_TYPE3,
		   SUM(IVSN_TYPE4) IVSN_TYPE4,
		   SUM(SPEC_BATL) SPEC_BATL_YN,
		   SUM(NLL) NLL_YN,
		   SUM(SHIP_CREW_CNT) SHIP_CREW_CNT,
		   SUM(VIO_CNT) VIO_CNT,
		   SUM(VIO_PRTD_AMT) VIO_PRTD_AMT,
		   
		   SUM(PROC_COND1) PROC_COND_N,
		   SUM(PROC_COND2) PROC_COND_Y,
		   '' LEAD_DISTANCE,
		   '' SHIP_TONNAGE,
		   SUM(PYMT_Y_AMT) MRGN_PYMT_AMT,
		   SUM(PYMT_N_AMT) NO_MRGN_PYMT_AMT, 
		   SUM(PYMT_N_CNT) PYMT_N_CNT,
		   
		   SUM(SHIP_PROC_TYPE_A) SHIP_PROC_TYPE_A,
		   SUM(SHIP_PROC_TYPE_B) SHIP_PROC_TYPE_B,
		   SUM(SHIP_PROC_TYPE_C) SHIP_PROC_TYPE_C,
		   SUM(SHIP_PROC_TYPE_D) SHIP_PROC_TYPE_D,
		   SUM(SHIP_PROC_TYPE_E) SHIP_PROC_TYPE_E,
		   
		   SUM(DTTN_CAP_CNT) DTTN_CAP_CNT,
		   SUM(DTTN_ENG_CNT) DTTN_ENG_CNT,
		   SUM(DTTN_MATE_CNT) DTTN_MATE_CNT,
		   SUM(DTTN_CREW_CNT) DTTN_CREW_CNT,
		   SUM(DTTN_CAP_CNT) + SUM(DTTN_ENG_CNT) + SUM(DTTN_MATE_CNT) + SUM(DTTN_CREW_CNT) DTTN_TOTAL_CNT,
		   SUM(SHIP_CMSN_COST) SHIP_CMSN_COST,
		   SUM(GEAR_CAST) GEAR_CAST,
		   SUM(GEAR_WIDTH) GEAR_WIDTH,
		   SUM(FIELD_CNT) FIELD_CNT,
		   COUNT(*) TOTAL_CNT
	 FROM	   
	   (
		SELECT
		    A.ID	,
			TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
			A.LAT_D	,
			A.LAT_M	,
			A.LAT_S	,
			A.LON_D	,
			A.LON_M	,
			A.LON_S	,
			A.ADDR_AREA	,
			A.ADDR_CITY	,
      		C.CODE_NM ADDR_STR,
			A.ADDR_DTL	,
			A.VLTN_TYPE	,
      		B.CODENM  || ' (' || A.VLTN_DTL || ')'	VLTN_DTL,
			A.IVSN_TYPE	,
			DECODE(A.IVSN_TYPE, 'YRB2', 1, 0) IVSN_TYPE1,
			DECODE(A.IVSN_TYPE, 'YRD1', 1, 0) IVSN_TYPE2,
			DECODE(A.IVSN_TYPE, 'YRA1', 1, 0) IVSN_TYPE3,
			DECODE(A.IVSN_TYPE, 'YRB1', 1, 0) IVSN_TYPE4,
			
			DECODE(A.SPEC_BATL_YN , 'Y', 1, 0) SPEC_BATL,
			DECODE(A.NLL_YN	, 'Y', 1, 0) NLL,
			A.AGENCY_TYPE	,
			A.AGENCY_GUARD	,
			G.CODENM AGENCY_GUARD_STR ,
			A.AGENCY_VESSEL	,
			A.SHIP_NAME	,
			A.SHIP_TONNAGE	,
			A.SHIP_CREW_CNT	,
			A.SHIP_QLTY	,
			A.SHIP_MENT	,      
      		D.CODENM SHIP_MENT_STR,        
			A.SHIP_PORT_NAME	,
			A.CAP_NAME	,
			A.CAP_CHN_NAME	,
			A.CAP_AGE	,
			A.SHIP_TYPE	,
      		E.CODENM SHIP_TYPE_STR,
			A.VIO_FISH	,
			A.VIO_CNT	,
			A.VIO_PRTD_AMT	,
			A.PROC_COND	,
			DECODE(A.PROC_COND, '01', 1, 0) PROC_COND1,
			DECODE(A.PROC_COND, '02', 1, 0) PROC_COND2,
			A.MRGN_PYMT_YN	,
			DECODE(A.MRGN_PYMT_YN, 'N', 1, 0) PYMT_N_CNT,
			A.MRGN_PYMT_AMT	,
			DECODE(MRGN_PYMT_YN, 'Y', MRGN_PYMT_AMT, 0) PYMT_Y_AMT,
			DECODE(MRGN_PYMT_YN, 'N', MRGN_PYMT_AMT, 0) PYMT_N_AMT,
			
			TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD') MRGN_PYMT_DATE	,
			A.SHIP_PROC_TYPE	,
			DECODE(A.SHIP_PROC_TYPE, '01', 1, 0) SHIP_PROC_TYPE_A,
			DECODE(A.SHIP_PROC_TYPE, '02', 1, 0) SHIP_PROC_TYPE_B,
			DECODE(A.SHIP_PROC_TYPE, '03', 1, 0) SHIP_PROC_TYPE_C,
			DECODE(A.SHIP_PROC_TYPE, '04', 1, 0) SHIP_PROC_TYPE_D,
			DECODE(A.SHIP_PROC_TYPE, '05', 1, 0) SHIP_PROC_TYPE_E,
			
			A.SHIP_CMSN_COST	,
			A.DTTN_CAP_CNT	,
			A.DTTN_MATE_CNT	,
			A.DTTN_ENG_CNT	,
			A.DTTN_CREW_CNT	,
			A.WO_DTTN_TYPE	,
      		F.CODENM WO_DTTN_STR,
			TO_CHAR(A.FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
			TO_CHAR(A.FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE	,
			DECODE(A.FIELD_ARST_DATE, NULL, 0, 1) FIELD_CNT,
			A.LEAD_DISTANCE	,
			A.LEAD_HOUR	,
			A.LEAD_MINUTE	,
			A.GEAR_CAST	,
			A.GEAR_WIDTH	,
			A.GEAR_ETC	,
			A.STATUS	,
			A.DEL_YN	,
			A.REG_DATE	,
			A.REG_USER	,
			A.REG_AGENCY	 
		  FROM T_CHN_ARREST_INFO A,
           TCODE B ,  -- 위반내용
           (SELECT CODE, UP_CODE, CODE_NM FROM CD_CODE C WHERE GP_CODE = 'A001' ) C,  -- 시군구
           TCODE D ,  -- 선적
           TCODE E ,  -- 선종
           TCODE F ,  -- 무혐의불구속
           (SELECT CODE1, CODE2, CODENM FROM TCODE WHERE CODE1 = 'C001' ) G
		 WHERE DEL_YN = 'N'
       AND (A.VLTN_TYPE = B.CODE2 AND B.CODE1 = 'T003')
       AND (A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE)
       AND (A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR')
       AND (A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81')
       AND (A.WO_DTTN_TYPE = F.CODE2 AND F.CODE1 = 'T005')
       AND G.CODE2(+) = A.AGENCY_GUARD
		]]>
		<if test="startDate != null and startDate != ''"><![CDATA[
			AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="endDate != null and endDate != ''"><![CDATA[
			AND A.ARST_DATE <= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
		]]></if>
		<if test="agencyType != null and agencyType != ''"><![CDATA[
			AND A.AGENCY_TYPE = #{agencyType}
		]]></if>
		<if test="agencyGuard != null and agencyGuard != ''"><![CDATA[
			AND A.AGENCY_GUARD = #{agencyGuard}
		]]></if>
		<if test="agencyVessel != null and agencyVessel != ''"><![CDATA[
			AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
		]]></if>
		<if test="addrArea != null and addrArea != ''"><![CDATA[
			AND A.ADDR_AREA = #{addrArea}
		]]></if>
		<if test="addrCity != null and addrCity != ''"><![CDATA[
			AND A.ADDR_CITY = #{addrCity}
		]]></if>
		<if test="violateType != null and violateType != ''"><![CDATA[
			AND A.VLTN_TYPE = #{violateType}
		]]></if>
		<if test="shipName != null and shipName != ''"><![CDATA[
			AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
		]]></if><![CDATA[
		
			AND A.SPEC_BATL_YN = DECODE(#{specBatlYn}, 'Y', 'Y', 'N')
			AND A.NLL_YN = DECODE(#{nllYn}, 'Y', 'Y', 'N')
		
		]]><if test="invasionType != null and invasionType.length > 0">
			AND A.IVSN_TYPE IN
			<foreach collection="invasionType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="invasionType == null or invasionType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipType != null and shipType.length > 0">
			AND A.SHIP_TYPE IN
			<foreach collection="shipType" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>		
		<if test="shipType == null or shipType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="procCondition != null and procCondition.length > 0">
			AND A.PROC_COND IN
			<foreach collection="procCondition" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="procCondition == null or procCondition.length == 0">
			AND 1 = 0
		</if>
		
		<if test="arrShipTonnage != null and arrShipTonnage.length > 0">
			<choose>
				<when test="arrShipTonnage.length == 1 and arrShipTonnage[0] == '01'"><![CDATA[
					AND A.SHIP_TONNAGE < 50
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')"><![CDATA[
					AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE < 100
				]]></when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')"><![CDATA[
					AND A.SHIP_TONNAGE >= 100
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE < 50 OR A.SHIP_TONNAGE >= 100 )
				]]></when>
				<when
					test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')"><![CDATA[
					AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE < 100 ) )
				]]></when>
				<otherwise>
					AND 1 = 1
				</otherwise>
			</choose>
		</if>
		<if test="arrShipTonnage == null or arrShipTonnage.length == 0">
			AND 1 = 0
		</if>
		
		<if test="marginPayment != null and marginPayment.length > 0">
			AND A.MRGN_PYMT_YN IN
			<foreach collection="marginPayment" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="marginPayment == null or marginPayment.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipProcType != null and shipProcType.length > 0">
			AND A.SHIP_PROC_TYPE IN
			<foreach collection="shipProcType" item="type" open="("
				close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipProcType == null or shipProcType.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipQuality != null and shipQuality.length > 0">
			AND A.SHIP_QLTY IN
			<foreach collection="shipQuality" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipQuality == null or shipQuality.length == 0">
			AND 1 = 0
		</if>
		
		<if test="shipMent != null and shipMent.length > 0">
			AND A.SHIP_MENT IN
			<foreach collection="shipMent" item="type" open="(" close=")"
				separator=",">
				#{type}
			</foreach>
		</if>
		<if test="shipMent == null or shipMent.length == 0">
			AND 1 = 0
		</if>
		)
	</select>


	<insert id="Arrest.insertArrest" parameterType="arrestVO">
	<![CDATA[	
		INSERT INTO
			T_CHN_ARREST_INFO (
				ID	,
				ARST_DATE	,
				AGENCY_TYPE	,
				AGENCY_GUARD	,
				AGENCY_VESSEL	,
				PROC_COND	,
				VLTN_TYPE	,
				VLTN_DTL	,
				IVSN_TYPE	,
				SPEC_BATL_YN	,
				NLL_YN	,
				WO_DTTN_TYPE	,
				FIELD_ARST_DATE	,
				FIELD_RLS_DATE	,
				LEAD_DISTANCE	,
				LEAD_HOUR	,
				LEAD_MINUTE	,
				DTTN_CAP_CNT	,
				DTTN_MATE_CNT	,
				DTTN_ENG_CNT	,
				DTTN_CREW_CNT	,
				SHIP_PROC_TYPE	,
				SHIP_CMSN_COST	,
				GEAR_CAST	,
				GEAR_WIDTH	,
				GEAR_ETC	,
				ADDR_AREA	,
				ADDR_CITY	,
				ADDR_DTL	,
				LAT_D	,
				LAT_M	,
				LAT_S	,
				LON_D	,
				LON_M	,
				LON_S	,
				SHIP_NAME	,
				SHIP_TONNAGE	,
				SHIP_CREW_CNT	,
				SHIP_QLTY	,
				SHIP_MENT	,
				SHIP_PORT_NAME	,
				SHIP_TYPE	,
				CAP_NAME	,
				CAP_CHN_NAME	,
				CAP_AGE	,
				VIO_FISH	,
				VIO_CNT	,
				VIO_PRTD_AMT	,
				MRGN_PYMT_YN	,
				MRGN_PYMT_AMT	,
				MRGN_PYMT_DATE	,
				STATUS	,
				DEL_YN	,
				REG_DATE	,
				REG_USER	,
				REG_AGENCY	

		) VALUES (
			nextval('chn_arrest_bd_seq'),
			TO_DATE(#{arrestDate}, 'YYYY-MM-DD hh24:mi'),
			#{agencyType},
			#{agencyGuard},
			#{agencyVessel},
			#{procCondition},
			#{violateType},
			#{violateDetail},
			#{invasionType},
			COALESCE(#{specBatlYn}, 'N'),
			COALESCE(#{nllYn}, 'N'),
			#{woDetentionType},
			TO_DATE(#{fieldArrestDate}, 'YYYY-MM-DD hh24:mi'),   
			TO_DATE(#{fieldReleaseDate}, 'YYYY-MM-DD hh24:mi'),   
			COALESCE(#{leadDistance}, 0),
			COALESCE(#{leadHour}, '0'),
			COALESCE(#{leadMinute}, '0'),
			COALESCE(#{captainCount}, 0),
			COALESCE(#{mateCount}, 0),
			COALESCE(#{engineerCount}, 0),
			COALESCE(#{crewCount}, 0),
			#{shipProcType},
			#{commissionCost},
			#{gearCast},
			#{gearWidth},
			#{gearEtc},
			#{addrArea},
			#{addrCity},
			#{addrDetail},
			#{latD},
			#{latM},
			#{latS},
			#{lonD},
			#{lonM},
			#{lonS},
			#{shipName},
			#{shipTonnage},
			#{shipCrewCnt},
			#{shipQuality},
			#{shipMent},
			#{shipPortName},
			#{shipType},
			#{capName},
			#{capChnName},
			#{capAge},
			#{vioFish},
			#{vioCnt},
			#{vioPriatedAmt},
			#{marginPaymentYn},
			#{marginPaymentAmt},
			TO_DATE(#{marginPaymentDate}, 'YYYY-MM-DD hh24:mi'),
			#{status},
			#{delYn},
    		NOW(),
			#{regUser},
			#{regAgency}

		)
	]]>
	</insert>

	<select id="Arrest.getArrest" parameterType="arrestVO"
		resultMap="arrestResult">
		SELECT
		ID ,
		TO_CHAR(ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
		LAT_D ,
		LAT_M ,
		LAT_S ,
		LON_D ,
		LON_M ,
		LON_S ,
		ADDR_AREA ,
		ADDR_CITY ,
		ADDR_DTL ,
		VLTN_TYPE ,
		VLTN_DTL ,
		IVSN_TYPE ,
		SPEC_BATL_YN ,
		NLL_YN ,
		AGENCY_TYPE ,
		AGENCY_GUARD ,
		AGENCY_VESSEL ,
		SHIP_NAME ,
		SHIP_TONNAGE ,
		SHIP_CREW_CNT ,
		SHIP_QLTY ,
		SHIP_MENT ,
		SHIP_PORT_NAME ,
		CAP_NAME ,
		CAP_CHN_NAME ,
		CAP_AGE ,
		SHIP_TYPE ,
		VIO_FISH ,
		VIO_CNT ,
		VIO_PRTD_AMT ,
		PROC_COND ,
		MRGN_PYMT_YN ,
		MRGN_PYMT_AMT ,
		TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD') MRGN_PYMT_DATE ,
		SHIP_PROC_TYPE ,
		SHIP_CMSN_COST ,
		DTTN_CAP_CNT ,
		DTTN_MATE_CNT ,
		DTTN_ENG_CNT ,
		DTTN_CREW_CNT ,
		WO_DTTN_TYPE ,
		TO_CHAR(FIELD_ARST_DATE, 'YYYY-MM-DD hh24:mi') FIELD_ARST_DATE,
		TO_CHAR(FIELD_RLS_DATE, 'YYYY-MM-DD hh24:mi') FIELD_RLS_DATE ,
		LEAD_DISTANCE ,
		LEAD_HOUR ,
		LEAD_MINUTE ,
		GEAR_CAST ,
		GEAR_WIDTH ,
		GEAR_ETC ,
		STATUS ,
		DEL_YN ,
		REG_DATE ,
		REG_USER ,
		REG_AGENCY

		FROM
		T_CHN_ARREST_INFO
		WHERE
		ID = #{arrestId}
	</select>

	<update id="Arrest.updateArrest" parameterType="arrestVO">
	<![CDATA[
		UPDATE
			T_CHN_ARREST_INFO
		SET
			ID=#{arrestId},			
			ARST_DATE=TO_DATE(#{arrestDate}, 'YYYY-MM-DD hh24:mi'),
			LAT_D=#{latD},
			LAT_M=#{latM},
			LAT_S=#{latS},
			LON_D=#{lonD},
			LON_M=#{lonM},
			LON_S=#{lonS},
			ADDR_AREA=#{addrArea},
			ADDR_CITY=#{addrCity},
			ADDR_DTL=#{addrDetail},
			VLTN_TYPE=#{violateType},
			VLTN_DTL=#{violateDetail},
			IVSN_TYPE=#{invasionType},
			SPEC_BATL_YN=COALESCE(#{specBatlYn}, 'N'),			
			NLL_YN=COALESCE(#{nllYn}, 'N'),
			AGENCY_TYPE=#{agencyType},
			AGENCY_GUARD=#{agencyGuard},
			AGENCY_VESSEL=#{agencyVessel},
			SHIP_NAME=#{shipName},
			SHIP_TONNAGE=#{shipTonnage},
			SHIP_CREW_CNT=#{shipCrewCnt},
			SHIP_QLTY=#{shipQuality},
			SHIP_MENT=#{shipMent},
			SHIP_PORT_NAME=#{shipPortName},
			CAP_NAME=#{capName},
			CAP_CHN_NAME=#{capChnName},
			CAP_AGE=#{capAge},
			SHIP_TYPE=#{shipType},
			VIO_FISH=#{vioFish},
			VIO_CNT=#{vioCnt},
			VIO_PRTD_AMT=#{vioPriatedAmt},
			PROC_COND=#{procCondition},
			MRGN_PYMT_YN=#{marginPaymentYn},
			MRGN_PYMT_AMT=#{marginPaymentAmt},			
			MRGN_PYMT_DATE=  DECODE(#{marginPaymentDate}, NULL,    NULL, TO_DATE(#{marginPaymentDate}, 'YYYY-MM-DD hh24:mi')),
			SHIP_PROC_TYPE=#{shipProcType},
			SHIP_CMSN_COST=#{commissionCost},
			DTTN_CAP_CNT=#{captainCount},
			DTTN_MATE_CNT=#{mateCount},
			DTTN_ENG_CNT=#{engineerCount},
			DTTN_CREW_CNT=#{crewCount},
			WO_DTTN_TYPE=#{woDetentionType},
			FIELD_ARST_DATE=TO_DATE(#{fieldArrestDate}, 'YYYY-MM-DD hh24:mi'),			
			FIELD_RLS_DATE=TO_DATE(#{fieldReleaseDate}, 'YYYY-MM-DD hh24:mi'),
			LEAD_DISTANCE=#{leadDistance},
			LEAD_HOUR=#{leadHour},
			LEAD_MINUTE=#{leadMinute},
			GEAR_CAST=#{gearCast},
			GEAR_WIDTH=#{gearWidth},
			GEAR_ETC=#{gearEtc},
			STATUS=#{status}
			
		WHERE
			ID = #{arrestId}
			
			
			
	]]>
	</update>

	<update id="deleteArrest" parameterType="arrestVO">
	<![CDATA[
		UPDATE T_CHN_ARREST_INFO
		   SET DEL_YN = 'Y'
		 WHERE ID = #{arrestId}
	]]>
	</update>
</mapper>

