<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cokr.xit.fims.crdn.receive.eqpmnt.dao.CrdnEqpmntMapper">

<select id="selectLinkFileLayoutMetadataList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataObject">
/* 연계파일 레이아웃 목록 조회(crdnEqpmntMapper.selectLinkFileLayoutMetadataList) */
<include refid="utility.paging-prefix"/>
SELECT A.FILE_LAYOUT_ID
     , GET_CODE_NM('FIM054', A.TASK_SE_CD) AS TASK_SE_NM
     , (SELECT DEPT_NM FROM TB_DEPT WHERE DEPT_CD = A.DEPT_CD) AS DEPT_NM
     , (CASE A.DATA_FILE_SE 
        WHEN 'TXT'
        THEN '텍스트파일 포함'
        WHEN 'JPG'
        THEN '텍스트파일 미포함'
        WHEN 'BIN'
        THEN '단일 바이너리 파일'
        ELSE '' END
       ) AS DATA_FILE_SE_NM
     , A.ENT_NM
     , B.LINK_ENT_NM
  FROM TB_CRDN_FILE_LAYOUT A
  LEFT OUTER JOIN TB_EQPMNT_CRDN_FTP B ON (A.FILE_LAYOUT_ID = B.FILE_LAYOUT_ID)
 WHERE A.USE_YN = 'Y'
   AND A.TASK_SE_CD = #{taskSeCd}
   AND A.SGG_CD = #{sggCd}
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix"/>
</select>

<select id="selectLinkFileLayoutMetadataInfo" parameterType="string" resultType="dataObject">
/* 연계파일 레이아웃 상세 조회(crdnEqpmntMapper.selectLinkFileLayoutMetadataInfo) */
SELECT A.FILE_LAYOUT_ID
     , A.SGG_CD
     , A.TASK_SE_CD
     , A.INST_CD
     , A.DEPT_CD
     , A.DATA_FILE_SE
     , A.FILE_NM_SZ
     , A.FILE_NM_SE_TYPE
     , A.FILE_NM_LAYOUT
     , A.DATA_SE_TYPE1
     , A.DATA_LAYOUT1
     , A.DATA_SE_TYPE2
     , A.DATA_LAYOUT2
     , A.FILE_TNOCS
     , A.LINK_FILE_PATH
     , A.ENT_NM
     , A.SAME_ITEMS
     , A.INCREASE_ITEM
     , A.INCREASE_TYPE
     , B.FILE_LAYOUT_ID
     , B.REMOTE_IP
     , B.REMOTE_PORT
     , B.REMOTE_ID
     , B.REMOTE_PASSWORD
     , B.REMOTE_OS
     , B.REMOTE_WORK_PATH
     , B.LINK_ENT_NM
  FROM TB_CRDN_FILE_LAYOUT A
  LEFT OUTER JOIN TB_EQPMNT_CRDN_FTP B ON (A.FILE_LAYOUT_ID = B.FILE_LAYOUT_ID)
 WHERE A.USE_YN = 'Y'
   AND A.FILE_LAYOUT_ID = #{fileLayoutId}
</select>

<insert id="insertCrdnFileLayout" parameterType="map">
/* 연계파일 레이아웃 등록(crdnEqpmntMapper.insertCrdnFileLayout) */
<selectKey resultType="string" keyProperty="layoutDescriptor.fileLayoutId" keyColumn="NEW_ID" order="BEFORE">
	<include refid="numbering.key">
		<property name="TABLE_NAME" value="TB_CRDN_FILE_LAYOUT" />
		<property name="TABLE_KEY" value="FILE_LAYOUT_ID" />
		<property name="pad" value="10" />
	</include>
</selectKey>
INSERT 
  INTO TB_CRDN_FILE_LAYOUT (
       FILE_LAYOUT_ID
     , SGG_CD
     , TASK_SE_CD
     , INST_CD
     , DEPT_CD
     , DATA_FILE_SE
     , FILE_NM_SZ
     , FILE_NM_SE_TYPE
     , FILE_NM_LAYOUT
     , DATA_SE_TYPE1
     , DATA_LAYOUT1
     , DATA_SE_TYPE2
     , DATA_LAYOUT2
     , FILE_TNOCS
     , LINK_FILE_PATH
     , ENT_NM
     , SAME_ITEMS
     , INCREASE_ITEM
     , INCREASE_TYPE
     , USE_YN
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
  ) VALUES (
       #{layoutDescriptor.fileLayoutId}
     , #{layoutDescriptor.sggCd}
     , #{layoutDescriptor.taskSeCd}
     , #{layoutDescriptor.instCd}
     , #{layoutDescriptor.deptCd}
     , #{layoutDescriptor.fileGroup}
     , #{layoutDescriptor.fileNameLength}
     , #{layoutDescriptor.fileNameSeperator}
     , #{layoutDescriptor.fileNameItmes}
     , #{layoutDescriptor.contentSeperator}
     , #{layoutDescriptor.contentItems}
     , #{layoutDescriptor.contentSecondSeperator}
     , #{layoutDescriptor.secondSeperatorStarterItems}
     , #{layoutDescriptor.countPerFileGroup}
     , #{layoutDescriptor.linkFileLocation}
     , #{layoutDescriptor.enterpriseName}
     , #{layoutDescriptor.sameItemsInFileGroup}
     , #{layoutDescriptor.increaseItemInFileGroup}
     , #{layoutDescriptor.increaseTypeInFileGroup}
     , 'Y'
     , <include refid="utility.now" />
     , #{currentUser.id}
     , <include refid="utility.now" />
     , #{currentUser.id}
  )
</insert>

<insert id="insertFtpInfo" parameterType="map">
/* 파일연계 원격지 정보 등록(crdnEqpmntMapper.insertFtpInfo) */
INSERT 
  INTO TB_EQPMNT_CRDN_FTP (
       FILE_LAYOUT_ID
     , REMOTE_IP
     , REMOTE_PORT
     , REMOTE_ID
     , REMOTE_PASSWORD
     , REMOTE_OS
     , REMOTE_WORK_PATH
     , LINK_ENT_NM
  ) VALUES (
       #{layoutDescriptor.fileLayoutId}
     , #{remoteSystemInfo.ip}
    <if test="remoteSystemInfo.port != null and remoteSystemInfo.port != ''">, #{remoteSystemInfo.port}</if>
    <if test="remoteSystemInfo.port == null or remoteSystemInfo.port == ''">, NULL</if>
     , #{remoteSystemInfo.id}
     , #{remoteSystemInfo.pw}
     , #{remoteSystemInfo.osType}
     , #{remoteSystemInfo.workPath}
     , #{layoutDescriptor.linkEnterpriseName}
  )
</insert>

<update id="updateCrdnFileLayout" parameterType="map">
/* 연계파일 레이아웃 수정(crdnEqpmntMapper.updateCrdnFileLayout) */
UPDATE TB_CRDN_FILE_LAYOUT
   SET SGG_CD          = #{layoutDescriptor.sggCd}
     , TASK_SE_CD      = #{layoutDescriptor.taskSeCd}
     , INST_CD         = #{layoutDescriptor.instCd}
     , DEPT_CD         = #{layoutDescriptor.deptCd}
     , DATA_FILE_SE    = #{layoutDescriptor.fileGroup}
     , FILE_NM_SZ      = #{layoutDescriptor.fileNameLength}
     , FILE_NM_SE_TYPE = #{layoutDescriptor.fileNameSeperator}
     , FILE_NM_LAYOUT  = #{layoutDescriptor.fileNameItmes}
     , DATA_SE_TYPE1   = #{layoutDescriptor.contentSeperator}
     , DATA_LAYOUT1    = #{layoutDescriptor.contentItems}
     , DATA_SE_TYPE2   = #{layoutDescriptor.contentSecondSeperator}
     , DATA_LAYOUT2    = #{layoutDescriptor.secondSeperatorStarterItems}
     , FILE_TNOCS      = #{layoutDescriptor.countPerFileGroup}
     , LINK_FILE_PATH  = #{layoutDescriptor.linkFileLocation}
     , ENT_NM          = #{layoutDescriptor.enterpriseName}
     , SAME_ITEMS      = #{layoutDescriptor.sameItemsInFileGroup}
     , INCREASE_ITEM   = #{layoutDescriptor.increaseItemInFileGroup}
     , INCREASE_TYPE   = #{layoutDescriptor.increaseTypeInFileGroup}
     , MDFCN_DT        = <include refid="utility.now" />
     , MDFR            = #{currentUser.id}
 WHERE FILE_LAYOUT_ID = #{layoutDescriptor.fileLayoutId}
</update>

<update id="updateFtpInfo" parameterType="map">
/* 파일연계 원격지 정보 수정(crdnEqpmntMapper.updateFtpInfo) */
UPDATE TB_EQPMNT_CRDN_FTP
   SET REMOTE_IP        = #{remoteSystemInfo.ip}
<if test='remoteSystemInfo.port != null and remoteSystemInfo.port != ""'>    , REMOTE_PORT      = #{remoteSystemInfo.port}</if>
     , REMOTE_ID        = #{remoteSystemInfo.id}
     , REMOTE_PASSWORD  = #{remoteSystemInfo.pw}
     , REMOTE_OS        = #{remoteSystemInfo.osType}
     , REMOTE_WORK_PATH = #{remoteSystemInfo.workPath}
     , LINK_ENT_NM      = #{layoutDescriptor.linkEnterpriseName}
 WHERE FILE_LAYOUT_ID = #{layoutDescriptor.fileLayoutId}
</update>

<update id="deleteCrdnFileLayout" parameterType="map">
/* 연계파일 레이아웃 삭제(crdnEqpmntMapper.deleteCrdnFileLayout) */
UPDATE TB_CRDN_FILE_LAYOUT
   SET USE_YN = 'N'
     , MDFCN_DT = <include refid="utility.now" />
     , MDFR = #{currentUser.id}
 WHERE FILE_LAYOUT_ID = #{fileLayoutId}
</update>

<resultMap id="descriptorRow" type="cokr.xit.fims.crdn.receive.eqpmnt.LayoutDescriptor">
	<result property="fileLayoutId"					column="FILE_LAYOUT_ID" />	<!-- 파일 레이아웃 ID -->
	<result property="sggCd"						column="SGG_CD" />			<!-- 시군구 코드 -->
	<result property="taskSeCd"						column="TASK_SE_CD" />		<!-- 업무 구분 코드 -->
	<result property="instCd"						column="INST_CD" />			<!-- 기관 코드 -->
	<result property="deptCd"						column="DEPT_CD" />			<!-- 부서 코드 -->
	<result property="linkFileLocation"				column="LINK_FILE_PATH" />	<!-- 연계 파일 경로 -->
	<result property="fileGroup"					column="DATA_FILE_SE" />	<!-- 자료 파일 구분 -->
	<result property="countPerFileGroup"			column="FILE_TNOCS" />		<!-- 파일 총 건수 -->
	<result property="sameItemsInFileGroup"			column="SAME_ITEMS" />		<!-- 동일 항목 -->
	<result property="increaseItemInFileGroup"		column="INCREASE_ITEM" />	<!-- 증가 항목 -->
	<result property="increaseTypeInFileGroup"		column="INCREASE_TYPE" />	<!-- 증가 형식 -->
	<result property="fileNameLength"				column="FILE_NM_SZ" />		<!-- 파일 명 크기 -->
	<result property="fileNameItmes"				column="FILE_NM_LAYOUT" />	<!-- 파일 명 형식 -->
	<result property="fileNameSeperator"			column="FILE_NM_SE_TYPE" />	<!-- 파일 명 구분 유형 -->
	<result property="contentItems"					column="DATA_LAYOUT1" />	<!-- 자료 형식1 -->
	<result property="contentSeperator"				column="DATA_SE_TYPE1" />	<!-- 자료 구분 유형1 -->
	<result property="contentSecondSeperator"		column="DATA_SE_TYPE2" />	<!-- 자료 구분 유형2 -->
	<result property="secondSeperatorStarterItems"	column="DATA_LAYOUT2" />	<!-- 자료 형식2 -->
	<result property="enterpriseName"				column="ENT_NM" />          <!-- 레이아웃 업체명 -->
	<result property="linkEnterpriseName"			column="LINK_ENT_NM" />		<!-- 연계 업체 명 -->
</resultMap>

<select id="selectLinkFileLayoutMetadata" parameterType="map" resultMap="descriptorRow">
/* 연계파일 레이아웃 정보 조회(crdnEqpmntMapper.selectLinkFileLayoutMetadata) */
SELECT A.FILE_LAYOUT_ID
     , A.SGG_CD
     , A.TASK_SE_CD
     , A.INST_CD
     , A.DEPT_CD
     , A.DATA_FILE_SE
     , A.FILE_NM_SZ
     , A.FILE_NM_SE_TYPE
     , A.FILE_NM_LAYOUT
     , A.DATA_SE_TYPE1
     , A.DATA_LAYOUT1
     , A.DATA_SE_TYPE2
     , A.DATA_LAYOUT2
     , A.FILE_TNOCS
     , A.LINK_FILE_PATH
     , A.ENT_NM
     , A.SAME_ITEMS
     , A.INCREASE_ITEM
     , A.INCREASE_TYPE
     , B.LINK_ENT_NM
  FROM TB_CRDN_FILE_LAYOUT A
  LEFT OUTER JOIN TB_EQPMNT_CRDN_FTP B ON (A.FILE_LAYOUT_ID = B.FILE_LAYOUT_ID)
 WHERE A.USE_YN = 'Y'
 <choose>
   <when test="fileLayoutId != null and fileLayoutId != '' ">
   AND A.FILE_LAYOUT_ID = #{fileLayoutId}
   </when>
   <otherwise>
   AND A.SGG_CD = #{sggCd}
   AND A.TASK_SE_CD = #{taskSeCd}
   AND A.INST_CD = #{instCd}
   AND A.DEPT_CD = #{deptCd}
   </otherwise>
 </choose>

</select>

<select id="selectRemoteInfo" parameterType="string" resultType="dataobject">
/* 연계파일 ftp정보 조회(crdnEqpmntMapper.selectRemoteInfo) */
SELECT FILE_LAYOUT_ID
     , REMOTE_IP
     , REMOTE_PORT
     , REMOTE_ID
     , REMOTE_PASSWORD
     , REMOTE_OS
     , REMOTE_WORK_PATH
  FROM TB_EQPMNT_CRDN_FTP
 WHERE FILE_LAYOUT_ID = #{fileLayoutId}
</select>

</mapper>