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

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

	<resultMap id="arrestResult" type="kcg.faics.inter.vo.InternationalVO">
		<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="leadNumber" column="LEAD_NUMBER" />
		<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" />
		<result property="foreignCrew" column="FOREIGN_CREW" />
		<result property="transportation" column="TRAMSPOR_TATION" />
		<result property="itemTest" column="ITEM_TEST" />
		<result property="remarksList" column="REMARKS_LIST" />
		
		<result property="aviolentcrime" column="AVIOLENT_CRIME" />
		<result property="violentcrime" column="VIOLENT_CRIME" />
		<result property="intelligentcrime" column="INTELLIGENT_CRIME" />
		<result property="theftcrime" column="THEFT_CRIME" />
		<result property="speciallaw" column="SPECIAL_LAW" />
		
		<result property="theftcrime" column="THEFT_CRIME" />
		<result property="speciallaw" column="SPECIAL_LAW" />
		<result property="arrangeTransport" column="ARRANGE_TRANSPORT" />
		<result property="illegalMover" column="ILLENGAL_MOVE" />
		<result property="transportationCar" column="TRAN_SPORTATION" />
		<result property="quantity" column="QUANTITY_CAP" />
		<result property="priceMoney" column="PRICE_MONEY" />
		<result property="targetCountry" column="TARGET_COUNTRY" />
		
		
		<result property="escapeChina" column="ESCAPE_CHINA" />
		<result property="employmentChina" column="EMPLOYMENT_CHINA" />
		<result property="etcChina" column="ETC_CHINA" />
		<result property="escapeJapan" column="ESCAPE_JAPAN" />
		<result property="employmentJapan" column="EMPLYMENT_JAPAN" />
		<result property="etcJapan" column="ETC_JAPAN" />
		<result property="escapeCountries" column="ESCAPE_COUNTRIES" />
		<result property="employmentCountries" column="EMPLOYMENT_COUNTRIES" />
		<result property="etcCountries" column="ETC_COUNTRIES" />
		
		<result property="smugglingName" column="SMUGGLING_NAME" />
		<result property="smugglingChn" column="SMUGGLING_CHN" />
		<result property="smugglingAge" column="SMUGGLING_AGE" />
		<result property="smugglingWorld" column="SMUGGLING_WORLD" />
		
		<result property="spilltarget" column="SPILL_TARGET" />
		<result property="amountofcompensation" column="AMOUNTOF_COMPENSATION" />
		
		<result property="intelProperty" column="INTEL_PROPERTY" />
		
		<result property="quenching" column="QUEN_CHING" />
		<result property="foreignCurrency" column="FOREIGN_CURRENCY" />
		<result property="foreigncurrencyamount" column="FOREIGNCURRENCYA_MOUNT" />
		
		<result property="noInsurance" column="NOIN_SURANCE" />
		<result property="alienDamage" column="ALLEN_DAMAGE" />
		
		<result property="originIndicationViolation" column="ORIGIN_VIOLATION" />
		
		<result property="inhibitionOfSocialSafety" column="INHIBITION_SOCIALSAFETY" />
		
		<result property="sailorPeople" column="SAIL_PEOPLE" />
		<result property="seamanOutside" column="SAIL_OUTSIDE" />
		
		
		<result property="drugadministration" column="DRUG_ADMINISTRATION" />
		<result property="carryonofdrugs" column="CARRYON_OFDRUGS" />
		<result property="drugsale" column="DRUG_SALE" />
		<result property="drugitem" column="DRUG_ITEM" />
		<result property="drugquantity" column="DRUG_GUANTITY" />
		<result property="drugamount" column="DRUG_AMOUNT" />
		
		<result property="otherMeansOfTransport" column="OMOF_TRANSPORT" />
		
		<result property="directSmuggling" column="DIRECT_SMUGGLING" />
		<result property="otherMeansOfDirect" column="OTHERMENSOF_DIRECT" />
		
		
	</resultMap>

	<resultMap id="arrestCountResult" type="kcg.faics.inter.vo.InternationalCountVO">
		<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="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="International.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="kcg.faics.inter.vo.InternationalVO" 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	,
      		A.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_NUMBER	,
			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 ,
			A.TRAMSPOR_TATION,
			A.FOREIGN_CREW,
			A.ITEM_TEST,
			A.REMARKS_LIST,
			A.AVIOLENT_CRIME,
			A.VIOLENT_CRIME,
			A.INTELLIGENT_CRIME,
			A.THEFT_CRIME,
			A.SPECIAL_LAW,
			A.ARRANGE_TRANSPORT,
			A.ILLENGAL_MOVE,
			A.TRAN_SPORTATION,
			A.QUANTITY_CAP,
			A.PRICE_MONEY,
			A.TARGET_COUNTRY,
			A.ESCAPE_CHINA,
			A.EMPLOYMENT_CHINA,
			A.ETC_CHINA,
			A.ESCAPE_JAPAN,
			A.EMPLYMENT_JAPAN,
			A.ETC_JAPAN,
			A.ESCAPE_COUNTRIES,
			A.EMPLOYMENT_COUNTRIES,
			A.ETC_COUNTRIES,
			A.SMUGGLING_NAME,
			A.SMUGGLING_CHN,
			A.SMUGGLING_AGE,
			A.SMUGGLING_WORLD,
			A.SPILL_TARGET,
			A.AMOUNTOF_COMPENSATION,
			A.INTEL_PROPERTY,
			A.QUEN_CHING,
			A.FOREIGN_CURRENCY,
			A.FOREIGNCURRENCYA_MOUNT,
			A.NOIN_SURANCE,
			A.ALLEN_DAMAGE,
			A.ORIGIN_VIOLATION,
			A.INHIBITION_SOCIALSAFETY,
			A.SAIL_PEOPLE,
			A.SAIL_OUTSIDE,
			A.DRUG_ADMINISTRATION,
			A.CARRYON_OFDRUGS,
			A.DRUG_SALE,
			A.DRUG_ITEM,
			A.DRUG_GUANTITY,
			A.DRUG_AMOUNT,
			A.OMOF_TRANSPORT,
			A.DIRECT_SMUGGLING,
			A.OTHERMENSOF_DIRECT
		  FROM T_ITN_ARREST_INFO A,
           (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.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">
    SELECT
      A.*	,
      (A.DTTN_CAP_CNT::int + A.DTTN_MATE_CNT::int + A.DTTN_ENG_CNT::int + A.DTTN_CREW_CNT::int) DTTN_TOTAL_CNT,
      TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
      TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD hh24:mi') MRGN_PYMT_DATE	,
      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	,
      C.CODE_NM ADDR_STR,
      D.CODENM SHIP_MENT_STR,
      E.CODENM SHIP_TYPE_STR,
      F.CODENM WO_DTTN_STR,
      G.CODENM AGENCY_GUARD_STR ,
      H.CODENM SHIP_QLTY_STR,
      I.CODE_NM ADDR_AREA_STR,
      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.PROC_COND
        when '02' then '1'
        else ''
      end PROC_COND_Y,
      case A.PROC_COND
        when '01' then '1'
        else ''
      end PROC_COND_N,
      case A.MRGN_PYMT_YN
        when 'Y' then A.MRGN_PYMT_AMT
        else 0
      end MRGN_PYMT_AMT,
      case A.MRGN_PYMT_YN
        when 'N' then A.MRGN_PYMT_AMT
        else 0
      end NO_MRGN_PYMT_AMT,
      case A.MRGN_PYMT_YN
        when 'N' then '1'
        else ''
      end NOT_PAYMENT,
      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,
      case A.FIELD_ARST_DATE
        when null then ''
        else '1'
      end FIELD_CNT
    FROM T_ITN_ARREST_INFO A
           inner join CD_CODE C on C.GP_CODE = 'A001' AND A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE  -- 시군구
           inner join TCODE D on A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR'  -- 선적
           inner join TCODE E on A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81'  -- 선종          
           inner join TCODE F on F.CODE1 = 'T005' AND A.WO_DTTN_TYPE = F.CODE2 -- 무혐의불구속
           left outer join TCODE G on G.CODE1 = 'C001' and G.CODE2 = A.AGENCY_GUARD -- 
           inner join TCODE H on H.CODE1 = 'T004' AND A.SHIP_QLTY = H.CODE2 -- 선질
           inner join CD_CODE I on I.GP_CODE = 'A001' AND I.UP_CODE ='0000' AND A.ADDR_AREA = I.CODE -- 특별광역시도
    WHERE A.DEL_YN = 'N'
      <if test="startDate != null and startDate != ''">
        AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
      </if>
      <if test="endDate != null and endDate != ''">
        AND A.ARST_DATE &lt;= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
      </if>
      <if test="agencyType != null and agencyType != ''">
        AND A.AGENCY_TYPE = #{agencyType}
      </if>
      <if test="agencyGuard != null and agencyGuard != ''">
        AND A.AGENCY_GUARD = #{agencyGuard}
      </if>
      <if test="agencyVessel != null and agencyVessel != ''">
        AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
      </if>
      <if test="addrArea != null and addrArea != ''">
        AND A.ADDR_AREA = #{addrArea}
      </if>
      <if test="addrCity != null and addrCity != ''">
        AND A.ADDR_CITY = #{addrCity}
      </if>
      <if test="violateType != null and violateType != ''">
        AND A.VLTN_TYPE = #{violateType}
      </if>
      <if test="shipName != null and shipName != ''">
        AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
      </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'">
            AND A.SHIP_TONNAGE &lt; 50
          </when>
          <when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')">
            AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE &lt; 100
          </when>
          <when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')">
            AND A.SHIP_TONNAGE >= 100
          </when>
          <when
            test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')">
            AND ( A.SHIP_TONNAGE &lt; 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE &lt; 100 ) )
          </when>
          <when
            test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')">
            AND ( A.SHIP_TONNAGE &lt; 50 OR A.SHIP_TONNAGE >= 100 )
          </when>
          <when
            test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')">
            AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE &lt; 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 LIKE '%'
      </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">
    SELECT
        A.*	,
        (A.DTTN_CAP_CNT::int + A.DTTN_MATE_CNT::int + A.DTTN_ENG_CNT::int + A.DTTN_CREW_CNT::int) DTTN_TOTAL_CNT,
        TO_CHAR(A.ARST_DATE, 'YYYY-MM-DD hh24:mi') ARST_DATE,
        TO_CHAR(MRGN_PYMT_DATE, 'YYYY-MM-DD hh24:mi') MRGN_PYMT_DATE	,
        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	,
        C.CODE_NM ADDR_STR,
        D.CODENM SHIP_MENT_STR,
        E.CODENM SHIP_TYPE_STR,
        F.CODENM WO_DTTN_STR,
        G.CODENM AGENCY_GUARD_STR ,
        H.CODENM SHIP_QLTY_STR,
        I.CODE_NM ADDR_AREA_STR,
        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.PROC_COND
          when '02' then '1'
          else ''
        end PROC_COND_Y,
        case A.PROC_COND
          when '01' then '1'
          else ''
        end PROC_COND_N,
        case A.MRGN_PYMT_YN
          when 'Y' then A.MRGN_PYMT_AMT
          else 0
        end MRGN_PYMT_AMT,
        case A.MRGN_PYMT_YN
          when 'N' then A.MRGN_PYMT_AMT
          else 0
        end NO_MRGN_PYMT_AMT,
        case A.MRGN_PYMT_YN
          when 'N' then '1'
          else ''
        end NOT_PAYMENT,
        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,
        case A.FIELD_ARST_DATE
          when null then ''
          else '1'
        end FIELD_CNT
    FROM T_ITN_ARREST_INFO A
        inner join CD_CODE C on C.GP_CODE = 'A001' AND A.ADDR_CITY = C.CODE AND A.ADDR_AREA = C.UP_CODE  -- 시군구
        inner join TCODE D on A.SHIP_MENT = D.CODE2 AND D.CODE1 = 'SSRR'  -- 선적
        inner join TCODE E on A.SHIP_TYPE = E.CODE2 AND E.CODE1 = 'FA81'  -- 선종
        inner join TCODE F on F.CODE1 = 'T005' AND A.WO_DTTN_TYPE = F.CODE2 -- 무혐의불구속
        left outer join TCODE G on G.CODE1 = 'C001' and G.CODE2 = A.AGENCY_GUARD --
        inner join TCODE H on H.CODE1 = 'T004' AND A.SHIP_QLTY = H.CODE2 -- 선질
        inner join CD_CODE I on I.GP_CODE = 'A001' AND I.UP_CODE ='0000' AND A.ADDR_AREA = I.CODE -- 특별광역시도
    WHERE A.DEL_YN = 'N'
		<if test="startDate != null and startDate != ''">
			AND A.ARST_DATE >= TO_DATE( #{startDate} || ' 00:00', 'YYYY-MM-DD hh24:mi')
		</if>
		<if test="endDate != null and endDate != ''">
			AND A.ARST_DATE &lt;= TO_DATE( #{endDate} || ' 23:59', 'YYYY-MM-DD hh24:mi')
		</if>
		<if test="agencyType != null and agencyType != ''">
			AND A.AGENCY_TYPE = #{agencyType}
		</if>
		<if test="agencyGuard != null and agencyGuard != ''">
			AND A.AGENCY_GUARD = #{agencyGuard}
		</if>
		<if test="agencyVessel != null and agencyVessel != ''">
			AND A.AGENCY_VESSEL like '%' ||  #{agencyVessel} || '%'
		</if>
		<if test="addrArea != null and addrArea != ''">
			AND A.ADDR_AREA = #{addrArea}
		</if>
		<if test="addrCity != null and addrCity != ''">
			AND A.ADDR_CITY = #{addrCity}
		</if>
		<if test="violateType != null and violateType != ''">
			AND A.VLTN_TYPE = #{violateType}
		</if>
		<if test="shipName != null and shipName != ''">
			AND A.SHIP_NAME LIKE '%' ||  #{shipName} || '%'
		</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="procCondition != null and procCondition.length > 0">
			AND A.PROC_COND IN
			<foreach collection="procCondition" item="type" open="(" close=")" separator=",">
				#{type}
			</foreach>
		</if>
		<if test="arrShipTonnage != null and arrShipTonnage.length > 0">
			<choose>
				<when test="arrShipTonnage.length == 1 and arrShipTonnage[0] == '01'">
					AND A.SHIP_TONNAGE &lt; 50
				</when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('02')">
					AND A.SHIP_TONNAGE >= 50 AND A.SHIP_TONNAGE &lt; 100
				</when>
				<when test="arrShipTonnage.length.equals(1) and arrShipTonnage[0].equals('03')">
					AND A.SHIP_TONNAGE >= 100
				</when>
				<when test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('02')">
					AND ( A.SHIP_TONNAGE &lt; 50 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE &lt; 100 ) )
				</when>
				<when test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('01') and arrShipTonnage[1].equals('03')">
					AND ( A.SHIP_TONNAGE &lt; 50 OR A.SHIP_TONNAGE >= 100 )
				</when>
				<when test="arrShipTonnage.length.equals(2) and arrShipTonnage[0].equals('02') and arrShipTonnage[1].equals('03')">
					AND ( A.SHIP_TONNAGE >= 100 OR ( A.SHIP_TONNAGE  >= 50 AND A.SHIP_TONNAGE &lt; 100 ) )
				</when>
			</choose>
		</if>
		<if test="marginPayment != null and marginPayment.length > 0">
			AND A.MRGN_PYMT_YN LIKE '%'
		</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="shipQuality != null and shipQuality.length > 0">
			AND A.SHIP_QLTY IN
			<foreach collection="shipQuality" item="type" open="(" close=")" separator=",">
				#{type}
			</foreach>
		</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>
	</select>
	
	

	<select id="getArrestListAllCount" parameterType="kcg.faics.inter.vo.InternationalVO" 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(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	,
      		A.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_NUMBER	,
			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,
			A.TRAMSPOR_TATION,
			A.FOREIGN_CREW,
			A.ITEM_TEST,
			A.REMARKS_LIST,
			A.AVIOLENT_CRIME,
			A.VIOLENT_CRIME,
			A.INTELLIGENT_CRIME,
			A.THEFT_CRIME,
			A.SPECIAL_LAW,
			A.ARRANGE_TRANSPORT,
			A.ILLENGAL_MOVE,
			A.TRAN_SPORTATION,
			A.QUANTITY_CAP,
			A.PRICE_MONEY,
			A.TARGET_COUNTRY,
			A.ESCAPE_CHINA,
			A.EMPLOYMENT_CHINA,
			A.ETC_CHINA,
			A.ESCAPE_JAPAN,
			A.EMPLYMENT_JAPAN,
			A.ETC_JAPAN,
			A.ESCAPE_COUNTRIES,
			A.EMPLOYMENT_COUNTRIES,
			A.ETC_COUNTRIES,
			A.SMUGGLING_NAME,
			A.SMUGGLING_CHN,
			A.SMUGGLING_AGE,
			A.SMUGGLING_WORLD,
			A.SPILL_TARGET,
			A.AMOUNTOF_COMPENSATION,
			A.INTEL_PROPERTY,
			A.QUEN_CHING,
			A.FOREIGN_CURRENCY,
			A.FOREIGNCURRENCYA_MOUNT,
			A.NOIN_SURANCE,
			A.ALLEN_DAMAGE,
			A.ORIGIN_VIOLATION,
			A.INHIBITION_SOCIALSAFETY,
			A.SAIL_PEOPLE,
			A.SAIL_OUTSIDE,
			A.DRUG_ADMINISTRATION,
			A.CARRYON_OFDRUGS,
			A.DRUG_SALE,
			A.DRUG_ITEM,
			A.DRUG_GUANTITY,
			A.DRUG_AMOUNT,
			A.OMOF_TRANSPORT,
			A.DIRECT_SMUGGLING,
			A.OTHERMENSOF_DIRECT		 	 
		  FROM T_ITN_ARREST_INFO A,
           (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.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_ITN_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(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	,
      		A.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) MRGN_PYMT_AMT,
			DECODE(MRGN_PYMT_YN, 'N', MRGN_PYMT_AMT, 0) NO_MRGN_PYMT_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_NUMBER	,
			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,
			A.TRAMSPOR_TATION,
			A.FOREIGN_CREW,
			A.ITEM_TEST,
			A.REMARKS_LIST,
			A.AVIOLENT_CRIME,
			A.VIOLENT_CRIME,
			A.INTELLIGENT_CRIME,
			A.THEFT_CRIME,
			A.SPECIAL_LAW,
			A.ARRANGE_TRANSPORT,
			A.ILLENGAL_MOVE,
			A.TRAN_SPORTATION,
			A.QUANTITY_CAP,
			A.PRICE_MONEY,
			A.TARGET_COUNTRY,
			A.ESCAPE_CHINA,
			A.EMPLOYMENT_CHINA,
			A.ETC_CHINA,
			A.ESCAPE_JAPAN,
			A.EMPLYMENT_JAPAN,
			A.ETC_JAPAN,
			A.ESCAPE_COUNTRIES,
			A.EMPLOYMENT_COUNTRIES,
			A.ETC_COUNTRIES,
			A.SMUGGLING_NAME,
			A.SMUGGLING_CHN,
			A.SMUGGLING_AGE,
			A.SMUGGLING_WORLD,
			A.SPILL_TARGET,
			A.AMOUNTOF_COMPENSATION,
			A.INTEL_PROPERTY,
			A.QUEN_CHING,
			A.FOREIGN_CURRENCY,
			A.FOREIGNCURRENCYA_MOUNT,
			A.NOIN_SURANCE,
			A.ALLEN_DAMAGE,
			A.ORIGIN_VIOLATION,
			A.INHIBITION_SOCIALSAFETY,
			A.SAIL_PEOPLE,
			A.SAIL_OUTSIDE,
			A.DRUG_ADMINISTRATION,
			A.CARRYON_OFDRUGS,
			A.DRUG_SALE,
			A.DRUG_ITEM,
			A.DRUG_GUANTITY,
			A.DRUG_AMOUNT,
			A.OMOF_TRANSPORT,
			A.DIRECT_SMUGGLING,
			A.OTHERMENSOF_DIRECT
		  FROM T_ITN_ARREST_INFO A,
           (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
		 WHERE A.DEL_YN = 'N'
       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
		]]>
		<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="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 LIKE '%'
		</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[
		)
		]]>
	</select>
	
	<select id="getArrestListCountExcel" parameterType="arrestSearchVO"
		resultMap="arrestResult">
	<![CDATA[
	SELECT /*+IDX_T_ITN_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(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	,
      		A.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,
			A.NLL_YN 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.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_NUMBER	,
			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,
			A.TRAMSPOR_TATION,
			A.FOREIGN_CREW,
			A.ITEM_TEST,
			A.REMARKS_LIST,
			A.AVIOLENT_CRIME,
			A.VIOLENT_CRIME,
			A.INTELLIGENT_CRIME,
			A.THEFT_CRIME,
			A.SPECIAL_LAW,
			A.ARRANGE_TRANSPORT,
			A.ILLENGAL_MOVE,
			A.TRAN_SPORTATION,
			A.QUANTITY_CAP,
			A.PRICE_MONEY,
			A.TARGET_COUNTRY,
			A.ESCAPE_CHINA,
			A.EMPLOYMENT_CHINA,
			A.ETC_CHINA,
			A.ESCAPE_JAPAN,
			A.EMPLYMENT_JAPAN,
			A.ETC_JAPAN,
			A.ESCAPE_COUNTRIES,
			A.EMPLOYMENT_COUNTRIES,
			A.ETC_COUNTRIES,
			A.SMUGGLING_NAME,
			A.SMUGGLING_CHN,
			A.SMUGGLING_AGE,
			A.SMUGGLING_WORLD,
			A.SPILL_TARGET,
			A.AMOUNTOF_COMPENSATION,
			A.INTEL_PROPERTY,
			A.QUEN_CHING,
			A.FOREIGN_CURRENCY,
			A.FOREIGNCURRENCYA_MOUNT,
			A.NOIN_SURANCE,
			A.ALLEN_DAMAGE,
			A.ORIGIN_VIOLATION,
			A.INHIBITION_SOCIALSAFETY,
			A.SAIL_PEOPLE,
			A.SAIL_OUTSIDE,
			A.DRUG_ADMINISTRATION,
			A.CARRYON_OFDRUGS,
			A.DRUG_SALE,
			A.DRUG_ITEM,
			A.DRUG_GUANTITY,
			A.DRUG_AMOUNT,
			A.OMOF_TRANSPORT,
			A.DIRECT_SMUGGLING,
			A.OTHERMENSOF_DIRECT
		  FROM T_ITN_ARREST_INFO A,
           (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.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>
		
		
		<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 LIKE '%'
		</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="International.insertArrest" parameterType="kcg.faics.inter.vo.InternationalVO">
	<![CDATA[	
		INSERT INTO T_ITN_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_NUMBER ,
				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,	
				FOREIGN_CREW,
				TRAMSPOR_TATION,
				ITEM_TEST,
				REMARKS_LIST,
				AVIOLENT_CRIME,
				VIOLENT_CRIME,
				INTELLIGENT_CRIME,
				THEFT_CRIME,
				SPECIAL_LAW,
				ARRANGE_TRANSPORT,
				ILLENGAL_MOVE,
				TRAN_SPORTATION,
				QUANTITY_CAP,
				PRICE_MONEY,
				TARGET_COUNTRY,
				ESCAPE_CHINA,
				EMPLOYMENT_CHINA,
				ETC_CHINA,
				ESCAPE_JAPAN,
				EMPLYMENT_JAPAN,
				ETC_JAPAN,
				ESCAPE_COUNTRIES,
				EMPLOYMENT_COUNTRIES,
				ETC_COUNTRIES,
			    SMUGGLING_NAME,
				SMUGGLING_CHN,
				SMUGGLING_AGE,
				SMUGGLING_WORLD,
				SPILL_TARGET,
				AMOUNTOF_COMPENSATION,
				INTEL_PROPERTY,
				QUEN_CHING,
				FOREIGN_CURRENCY,
				FOREIGNCURRENCYA_MOUNT,
				NOIN_SURANCE,
				ALLEN_DAMAGE,
				ORIGIN_VIOLATION,
				INHIBITION_SOCIALSAFETY,
				SAIL_PEOPLE,
				SAIL_OUTSIDE,
				DRUG_ADMINISTRATION,
				CARRYON_OFDRUGS,
				DRUG_SALE,
				DRUG_ITEM,
				DRUG_GUANTITY,
				DRUG_AMOUNT,
				OMOF_TRANSPORT,
				DIRECT_SMUGGLING,
				OTHERMENSOF_DIRECT
		) VALUES (
			T_ITN_ARREST_INFO_ID_SEQ.NEXTVAL,
			TO_DATE(#{arrestDate}, 'YYYY-MM-DD hh24:mi'), 
			#{agencyType},
			'PS00',
			#{agencyVessel},
			#{procCondition},
			#{violateType},
			#{violateDetail},
			#{invasionType},
			COALESCE(#{specBatlYn}, 'N'),
			#{nllYn},
			'01',
			TO_DATE(#{fieldArrestDate}, 'YYYY-MM-DD hh24:mi'),   
			TO_DATE(#{fieldReleaseDate}, 'YYYY-MM-DD hh24:mi'),   
			#{leadDistance},
			COALESCE(#{leadHour}, 0),
			COALESCE(#{leadNumber}, 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},
			'01',
			'SRAA',
			#{shipPortName},
			'01',
			#{capName},
			#{capChnName},
			#{capAge},
			#{vioFish},
			#{vioCnt},
			#{vioPriatedAmt},
			#{marginPaymentYn},
			#{marginPaymentAmt},
			TO_DATE(#{marginPaymentDate}, 'YYYY-MM-DD hh24:mi'),
			'1',
			#{delYn},
      NOW(),
			#{regUser},
			#{regAgency},
			#{foreignCrew},
			#{transportation},
			#{itemTest},
			#{remarksList},
			#{aviolentcrime},
			#{violentcrime},
			#{intelligentcrime},
			#{theftcrime},
			#{speciallaw},
			#{arrangeTransport},
			#{illegalMover},
			#{transportationCar},
			#{quantity},
			#{priceMoney},
			#{targetCountry},
			#{escapeChina},
			#{employmentChina},
			#{etcChina},
			#{escapeJapan},
			#{employmentJapan},
			#{etcJapan},
			#{escapeCountries},
			#{employmentCountries},
			#{etcCountries},
			#{smugglingName},
			#{smugglingChn},
			#{smugglingAge},
			#{smugglingWorld},
			#{spilltarget},
			#{amountofcompensation},
			#{intelProperty},
			#{quenching},
			#{foreignCurrency},
			#{foreigncurrencyamount},
			#{noInsurance},
			#{alienDamage},
			#{originIndicationViolation},
			#{inhibitionOfSocialSafety},
			#{sailorPeople},
			#{seamanOutside},
			#{drugadministration},
			#{carryonofdrugs},
			#{drugsale},
			#{drugitem},
			#{drugquantity},
			#{drugamount},
			#{otherMeansOfTransport},
			#{directSmuggling},
			#{otherMeansOfDirect}
		)
	]]>
	</insert>

	<select id="International.getArrest" parameterType="kcg.faics.inter.vo.InternationalVO"
		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_NUMBER ,
		LEAD_MINUTE ,
		GEAR_CAST ,
		GEAR_WIDTH ,
		GEAR_ETC ,
		STATUS ,
		DEL_YN ,
		REG_DATE ,
		REG_USER ,
		REG_AGENCY,
		FOREIGN_CREW,
		TRAMSPOR_TATION,
		ITEM_TEST,
		REMARKS_LIST,
		AVIOLENT_CRIME,
		VIOLENT_CRIME,
		INTELLIGENT_CRIME,
		THEFT_CRIME,
		SPECIAL_LAW,
		ARRANGE_TRANSPORT,
		ILLENGAL_MOVE,
		TRAN_SPORTATION,
		QUANTITY_CAP,
		PRICE_MONEY,
		TARGET_COUNTRY,
		ESCAPE_CHINA,
		EMPLOYMENT_CHINA,
		ETC_CHINA,
		ESCAPE_JAPAN,
		EMPLYMENT_JAPAN,
		ETC_JAPAN,
		ESCAPE_COUNTRIES,
		EMPLOYMENT_COUNTRIES,
		ETC_COUNTRIES,
		SMUGGLING_NAME,
		SMUGGLING_CHN,
		SMUGGLING_AGE,
		SMUGGLING_WORLD,
		SPILL_TARGET,
		AMOUNTOF_COMPENSATION,
		INTEL_PROPERTY,
		QUEN_CHING,
		FOREIGN_CURRENCY,
		FOREIGNCURRENCYA_MOUNT,
		NOIN_SURANCE,
		ALLEN_DAMAGE,
		ORIGIN_VIOLATION,
		INHIBITION_SOCIALSAFETY,
		SAIL_PEOPLE,
		SAIL_OUTSIDE,
		DRUG_ADMINISTRATION,
		CARRYON_OFDRUGS,
		DRUG_SALE,
		DRUG_ITEM,
		DRUG_GUANTITY,
		DRUG_AMOUNT,
		OMOF_TRANSPORT,
		DIRECT_SMUGGLING,
		OTHERMENSOF_DIRECT

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

	<update id="International.updateArrest" parameterType="kcg.faics.inter.vo.InternationalVO">
	<![CDATA[
		UPDATE
			T_ITN_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=#{nllYn},
			AGENCY_TYPE=#{agencyType},
			AGENCY_GUARD='PS00',
			AGENCY_VESSEL=#{agencyVessel},
			SHIP_NAME=#{shipName},
			SHIP_TONNAGE=#{shipTonnage},
			SHIP_CREW_CNT=#{shipCrewCnt},
			SHIP_PORT_NAME=#{shipPortName},
			CAP_NAME=#{capName},
			CAP_CHN_NAME=#{capChnName},
			CAP_AGE=#{capAge},
			SHIP_TYPE='01',
			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='01',
			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_NUMBER=#{leadNumber},
			LEAD_MINUTE=#{leadMinute},
			GEAR_CAST=#{gearCast},
			GEAR_WIDTH=#{gearWidth},
			GEAR_ETC=#{gearEtc},
			STATUS=#{status},
			FOREIGN_CREW=#{foreignCrew},
			TRAMSPOR_TATION=#{transportation},
			ITEM_TEST=#{itemTest},
			REMARKS_LIST=#{remarksList},
			AVIOLENT_CRIME=#{aviolentcrime},
			VIOLENT_CRIME=#{violentcrime},
			INTELLIGENT_CRIME=#{intelligentcrime},
			THEFT_CRIME=#{theftcrime},
			SPECIAL_LAW=#{speciallaw},
			ARRANGE_TRANSPORT=#{arrangeTransport},
			ILLENGAL_MOVE=#{illegalMover},
			TRAN_SPORTATION=#{transportationCar},
			QUANTITY_CAP=#{quantity},
			PRICE_MONEY=#{priceMoney},
			TARGET_COUNTRY=#{targetCountry},
			ESCAPE_CHINA=#{escapeChina},
			EMPLOYMENT_CHINA=#{employmentChina},
			ETC_CHINA=#{etcChina},
			ESCAPE_JAPAN=#{escapeJapan},
			EMPLYMENT_JAPAN=#{employmentJapan},
			ETC_JAPAN=#{etcJapan},
			ESCAPE_COUNTRIES=#{escapeCountries},
			EMPLOYMENT_COUNTRIES=#{employmentCountries},
			ETC_COUNTRIES=#{etcCountries},
			SMUGGLING_NAME=#{smugglingName},
			SMUGGLING_CHN=#{smugglingChn},
			SMUGGLING_AGE=#{smugglingAge},
			SMUGGLING_WORLD=#{smugglingWorld},
			SPILL_TARGET=#{spilltarget},
			AMOUNTOF_COMPENSATION=#{amountofcompensation},
			INTEL_PROPERTY=#{intelProperty},
			QUEN_CHING=#{quenching},
			FOREIGN_CURRENCY=#{foreignCurrency},
			FOREIGNCURRENCYA_MOUNT=#{foreigncurrencyamount},
			NOIN_SURANCE=#{noInsurance},
			ALLEN_DAMAGE=#{alienDamage},
			ORIGIN_VIOLATION=#{originIndicationViolation},
			INHIBITION_SOCIALSAFETY=#{inhibitionOfSocialSafety},
			SAIL_PEOPLE=#{sailorPeople},
			SAIL_OUTSIDE=#{seamanOutside},
			DRUG_ADMINISTRATION=#{drugadministration},
			CARRYON_OFDRUGS=#{carryonofdrugs},
			DRUG_SALE=#{drugsale},
			DRUG_ITEM=#{drugitem},
			DRUG_GUANTITY=#{drugquantity},
			DRUG_AMOUNT=#{drugamount},
			OMOF_TRANSPORT=#{otherMeansOfTransport},
			DIRECT_SMUGGLING=#{directSmuggling},
			OTHERMENSOF_DIRECT=#{otherMeansOfDirect}
		WHERE
			ID = #{arrestId}
			
			
			
	]]>
	</update>

	<update id="deleteArrest" parameterType="kcg.faics.inter.vo.InternationalVO">
	<![CDATA[
		UPDATE T_ITN_ARREST_INFO
		   SET DEL_YN = 'Y'
		 WHERE ID = #{arrestId}
	]]>
	</update>
</mapper>

