<?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="com.its.app.mapper.CctvMapper">

    <select id="selectAll" resultType="com.its.app.vo.voIcmpCtlr">
    <![CDATA[
	    SELECT A.CCTV_MNGM_NMBR AS CTLR_NMBR,
               A.CCTV_CTLR_ID   AS CTLR_ID,
               A.ISTL_LCTN_NM   AS ISTL_LCTN_NM,
               A.CCTV_CTLR_IP   AS CTLR_IP,
               A.CCTV_TYPE      AS TYPE_CD,
               DECODE(A.CCTV_TYPE, '1', '일반_1',
                                   '2', '기타_2',
                                   '3', '기타_3',
                                        '기타_X') AS TYPE_NM,
               A.DEL_YN AS DEL_YN
          FROM TB_CCTV_CTLR A
         WHERE A.DEL_YN = 'N'
           AND A.CCTV_TYPE <> '1'
    ]]>
    </select>

    <update id="updateStts" parameterType="com.its.app.vo.voIcmpCtlrStts">
    <![CDATA[
        MERGE INTO TB_CCTV_STTS L
        USING (SELECT #{stts.CTLR_NMBR}    AS CCTV_MNGM_NMBR,
                      #{stts.UPDT_DT}      AS UPDT_DT,
                      #{stts.CMNC_STTS_CD} AS CMNC_STTS_CD
                 FROM DUAL) M
        ON (L.CCTV_MNGM_NMBR = M.CCTV_MNGM_NMBR)
        WHEN MATCHED THEN
            UPDATE SET L.UPDT_DT      = M.UPDT_DT,
                       L.CMNC_STTS_CD = M.CMNC_STTS_CD
        WHEN NOT MATCHED THEN
            INSERT (L.CCTV_MNGM_NMBR,
                    L.UPDT_DT,
                    L.CMNC_STTS_CD)
            VALUES (M.CCTV_MNGM_NMBR,
                    M.UPDT_DT,
                    M.CMNC_STTS_CD)
    ]]>
    </update>

</mapper>
