<?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.vds.dao.mapper.VdsCtlrMapper">

    <select id="selectAll" resultType="com.its.vds.entity.TbVdsCtlr" fetchSize="100">
    <![CDATA[
        select t1.vds_ctlr_nmbr         AS vdsCtlrNmbr,
               t1.vds_ctlr_id           AS vdsCtlrId,
               t1.vds_nm                AS vdsNm,
               t1.vds_ctlr_ip           AS vdsCtlrIp,
               t1.vds_ctlr_port         AS vdsCtlrPort,
               t1.vds_ctlr_local_no     AS vdsCtlrLocalNo,
               t1.vds_type_cd           AS vdsTypeCd,
               t1.istl_lctn_nm          AS istlLctnNm,
               nvl(t1.group_no, 0)      AS groupNo,
               nvl(t1.fan_mode, 0)      AS fanMode,
               nvl(t1.fan_run_tmpr, 30) AS fanRunTmpr,
               nvl(t1.hetr_mode, 0)     AS hetrMode,
               nvl(t1.hetr_run_tmpr, 0) AS hetrRunTmpr,
               nvl(t1.detect_lanes, 0)  AS detectLanes,
               t1.traf_clct_cycl        AS trafClctCycl,
               t1.stts_clct_cycl        AS sttsClctCycl,
               t1.istl_lctn_addr        AS istlLctnAddr,
               'Y'                      AS validYn,
               t1.del_yn                AS delYn
        from tb_vds_ctlr t1
        where t1.del_yn = 'N'
        order by t1.vds_ctlr_nmbr
    ]]>
    </select>

    <select id="selectVdsCtlrStts" resultType="com.its.vds.entity.TbVdsCtlrStts" fetchSize="100">
    <![CDATA[
        select t1.vds_ctlr_nmbr      AS vdsCtlrNmbr,
               t1.updt_dt            AS updtDt,
               t1.cmnc_stts_cd       AS cmncSttsCd,
               t1.cbox_door_stts_cd  AS cboxDoorSttsCd,
               t1.fan_stts_cd        AS fanSttsCd,
               t1.hetr_stts_cd       AS hetrSttsCd,
               t1.cbox_tmpr          AS cboxTmpr,
               t1.front_door_stts_cd AS frontDoorSttsCd,
               t1.back_door_stts_cd  AS backDoorSttsCd,
               t1.video_input        AS videoInput
        from tb_vds_ctlr_stts t1
        inner join tb_vds_ctlr t2
        on t1.vds_ctlr_nmbr = t2.vds_ctlr_nmbr
        ]]>
    </select>

    <update id="updateVdsCtlrStts" parameterType="com.its.vds.entity.TbVdsCtlrStts">
    <![CDATA[
        merge into tb_vds_ctlr_stts L
            using (select
                       #{obj.vdsCtlrNmbr}     AS vds_ctlr_nmbr,
                       #{obj.updtDt}          AS updt_dt,
                       #{obj.cmncSttsCd}      AS cmnc_stts_cd,
                       #{obj.cboxDoorSttsCd}  AS cbox_door_stts_cd,
                       #{obj.fanSttsCd}       AS fan_stts_cd,
                       #{obj.hetrSttsCd}      AS hetr_stts_cd,
                       #{obj.cboxTmpr}        AS cbox_tmpr,
                       #{obj.frontDoorSttsCd} AS front_door_stts_cd,
                       #{obj.backDoorSttsCd}  AS back_door_stts_cd,
                       #{obj.videoInput}      AS video_input
                   from dual) M
            on (    L.vds_ctlr_nmbr = M.vds_ctlr_nmbr)
            when matched then
                update set
                    L.updt_dt            = M.updt_dt,
                    L.cmnc_stts_cd       = M.cmnc_stts_cd,
                    L.cbox_door_stts_cd  = M.cbox_door_stts_cd,
                    L.fan_stts_cd        = M.fan_stts_cd,
                    L.hetr_stts_cd       = M.hetr_stts_cd,
                    L.cbox_tmpr          = M.cbox_tmpr,
                    L.front_door_stts_cd = M.front_door_stts_cd,
                    L.back_door_stts_cd  = M.back_door_stts_cd,
                    L.video_input        = M.video_input
            when not matched then
                insert (
                        vds_ctlr_nmbr,
                        updt_dt,
                        cmnc_stts_cd,
                        cbox_door_stts_cd,
                        fan_stts_cd,
                        hetr_stts_cd,
                        cbox_tmpr,
                        front_door_stts_cd,
                        back_door_stts_cd,
                        video_input )
                values (
                       M.vds_ctlr_nmbr,
                       M.updt_dt,
                       M.cmnc_stts_cd,
                       M.cbox_door_stts_cd,
                       M.fan_stts_cd,
                       M.hetr_stts_cd,
                       M.cbox_tmpr,
                       M.front_door_stts_cd,
                       M.back_door_stts_cd,
                       M.video_input )
        ]]>
    </update>

    <update id="batchUpdateVdsCtlrStts" parameterType="java.util.Map">
    <![CDATA[
        merge into tb_vds_ctlr_stts L
            using (select
                       #{vdsCtlrNmbr}     AS vds_ctlr_nmbr,
                       #{updtDt}          AS updt_dt,
                       #{cmncSttsCd}      AS cmnc_stts_cd,
                       #{cboxDoorSttsCd}  AS cbox_door_stts_cd,
                       #{fanSttsCd}       AS fan_stts_cd,
                       #{hetrSttsCd}      AS hetr_stts_cd,
                       #{cboxTmpr}        AS cbox_tmpr,
                       #{frontDoorSttsCd} AS front_door_stts_cd,
                       #{backDoorSttsCd}  AS back_door_stts_cd,
                       #{videoInput}      AS video_input
                   from dual) M
            on (    L.vds_ctlr_nmbr = M.vds_ctlr_nmbr)
            when matched then
                update set
                    L.updt_dt            = M.updt_dt,
                    L.cmnc_stts_cd       = M.cmnc_stts_cd,
                    L.cbox_door_stts_cd  = M.cbox_door_stts_cd,
                    L.fan_stts_cd        = M.fan_stts_cd,
                    L.hetr_stts_cd       = M.hetr_stts_cd,
                    L.cbox_tmpr          = M.cbox_tmpr,
                    L.front_door_stts_cd = M.front_door_stts_cd,
                    L.back_door_stts_cd  = M.back_door_stts_cd,
                    L.video_input        = M.video_input
            when not matched then
                insert (
                        vds_ctlr_nmbr,
                        updt_dt,
                        cmnc_stts_cd,
                        cbox_door_stts_cd,
                        fan_stts_cd,
                        hetr_stts_cd,
                        cbox_tmpr,
                        front_door_stts_cd,
                        back_door_stts_cd,
                        video_input )
                values (
                       M.vds_ctlr_nmbr,
                       M.updt_dt,
                       M.cmnc_stts_cd,
                       M.cbox_door_stts_cd,
                       M.fan_stts_cd,
                       M.hetr_stts_cd,
                       M.cbox_tmpr,
                       M.front_door_stts_cd,
                       M.back_door_stts_cd,
                       M.video_input )
        ]]>
    </update>

    <insert id="insertVdsCtlrSttsHs" parameterType="com.its.vds.entity.TbVdsCtlrStts">
    <![CDATA[
        insert into tb_vds_ctlr_stts_hs (
            crtn_dt,
            vds_ctlr_nmbr,
            cmnc_stts_cd,
            cbox_door_stts_cd,
            fan_stts_cd,
            hetr_stts_cd,
            cbox_tmpr,
            front_door_stts_cd,
            back_door_stts_cd,
            video_input )
        values (
           #{obj.crtnDt},
           #{obj.vdsCtlrNmbr},
           #{obj.cmncSttsCd},
           #{obj.cboxDoorSttsCd},
           #{obj.fanSttsCd},
           #{obj.hetrSttsCd},
           #{obj.cboxTmpr},
           #{obj.frontDoorSttsCd},
           #{obj.backDoorSttsCd},
           #{obj.videoInput}
           )
        ]]>
    </insert>

    <insert id="batchInsertVdsCtlrSttsHs" parameterType="java.util.Map">
    <![CDATA[
        insert into tb_vds_ctlr_stts_hs (
            crtn_dt,
            vds_ctlr_nmbr,
            cmnc_stts_cd,
            cbox_door_stts_cd,
            fan_stts_cd,
            hetr_stts_cd,
            cbox_tmpr,
            front_door_stts_cd,
            back_door_stts_cd,
            video_input )
        values (
           #{crtnDt},
           #{vdsCtlrNmbr},
           #{cmncSttsCd},
           #{cboxDoorSttsCd},
           #{fanSttsCd},
           #{hetrSttsCd},
           #{cboxTmpr},
           #{frontDoorSttsCd},
           #{backDoorSttsCd},
           #{videoInput}
           )
        ]]>
    </insert>

</mapper>
