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

<select id="selectTeamList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 단속팀 목록 조회(crdnTeamMapper.selectTeamList) */
SELECT A.TEAM_ID
     , A.TEAM_NM
     , A.CRDN_SE_CD
     , GET_CODE_NM('FIM002', A.CRDN_SE_CD) AS CRDN_SE_NM
     , A.TEAMER_1
     , A.TEAMER_2
     , A.TEAMER_3
     , A.TEAMER_4
     , A.ETC_CN
     , A.REG_DT
  FROM TB_TEAM A
 WHERE A.USE_YN = 'Y'
   AND A.SGG_CD = #{sggCd}
   AND A.TASK_SE_CD = #{taskSeCd}
 ORDER BY A.TEAM_NM
</select>

<select id="selectRemovedTeamList" parameterType="cokr.xit.fims.crdn.CrdnQuery" resultType="dataobject">
/* 비사용 단속팀 목록 조회(crdnTeamMapper.selectRemovedTeamList) */
SELECT A.TEAM_ID
     , A.TEAM_NM
     , A.CRDN_SE_CD
     , GET_CODE_NM('FIM002', A.CRDN_SE_CD) AS CRDN_SE_NM
     , A.TEAMER_1
     , A.TEAMER_2
     , A.TEAMER_3
     , A.TEAMER_4
     , A.ETC_CN
     , A.REG_DT
     , A.MDFCN_DT
  FROM TB_TEAM A
 WHERE A.USE_YN = 'N'
   AND A.SGG_CD = #{sggCd}
   AND A.TASK_SE_CD = #{taskSeCd}
   AND A.TEAM_NM = #{teamNm}
 ORDER BY A.MDFCN_DT
</select>

<update id="deleteTeamInfo" parameterType="cokr.xit.fims.crdn.Team">
/* 단속팀 정보 삭제(crdnTeamMapper.deleteTeamInfo) */
UPDATE TB_TEAM
   SET USE_YN   = 'N'
     , MDFCN_DT = <include refid="utility.now" />
     , MDFR     = #{removedBy}
 WHERE TEAM_ID = #{teamId}
</update>

<insert id="insertTeamInfo" parameterType="cokr.xit.fims.crdn.Team">
/* 단속팀 정보 등록(crdnTeamMapper.insertTeamInfo) */
<selectKey resultType="string" keyProperty="teamId" keyColumn="NEW_ID" order="BEFORE">
	<include refid="numbering.key">
		<property name="TABLE_NAME" value="TB_TEAM" />
		<property name="TABLE_KEY" value="TEAM_ID" />
		<property name="pad" value="10" />
	</include>
</selectKey>
INSERT
  INTO TB_TEAM (
       TEAM_ID
     , SGG_CD
     , TASK_SE_CD
     , TEAM_NM
     , CRDN_SE_CD
     , TEAMER_1
     , TEAMER_2
     , TEAMER_3
     , TEAMER_4
     , ETC_CN
     , USE_YN
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
  ) VALUES (
       #{teamId}
     , #{sggCd}
     , #{taskSeCd}
     , #{teamNm}
     , #{crdnSeCd}
     , #{teamer1}
     , #{teamer2}
     , #{teamer3}
     , #{teamer4}
     , #{etcCn}
     , 'Y'
     , <include refid="utility.now" />
     , #{createdBy}
     , <include refid="utility.now" />
     , #{modifiedBy}
  )
</insert>

<update id="updateTeamInfo" parameterType="cokr.xit.fims.crdn.Team">
/* 단속팀 정보 수정(crdnTeamMapper.updateTeamInfo) */
UPDATE TB_TEAM
   SET ETC_CN   = #{etcCn}
     , MDFCN_DT = <include refid="utility.now" />
     , MDFR     = #{modifiedBy}
 WHERE TEAM_ID = #{teamId}
</update>

<select id="selectTeamInfoByName" parameterType="cokr.xit.fims.crdn.Team" resultType="dataobject">
/* 단속팀명으로 단속팀 정보 조회(crdnTeamMapper.selectTeamInfoByName) */
SELECT TEAM_ID
     , SGG_CD
     , TASK_SE_CD
     , TEAM_NM
     , CRDN_SE_CD
     , TEAMER_1
     , TEAMER_2
     , TEAMER_3
     , TEAMER_4
     , ETC_CN
  FROM TB_TEAM
 WHERE TEAM_NM = #{teamNm}
   AND SGG_CD = #{sggCd}
   AND TASK_SE_CD = #{taskSeCd}
   AND USE_YN = 'Y'
</select>

<select id="selectTeamInfo" parameterType="string" resultType="dataobject">
/* 단속팀 정보 조회(crdnTeamMapper.selectTeamInfo) */
SELECT TEAM_ID
     , SGG_CD
     , TASK_SE_CD
     , TEAM_NM
     , CRDN_SE_CD
     , TEAMER_1
     , TEAMER_2
     , TEAMER_3
     , TEAMER_4
     , ETC_CN
  FROM TB_TEAM
 WHERE TEAM_ID = #{teamId}
</select>

</mapper>