<?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.bcxin.identify.business.dao.ComVideoRoomMapper">

    <!-- 新增对象 -->
    <insert id="insertComVideoRoom" parameterType="com.bcxin.identify.business.domain.ysp.ComVideoRoom">
        insert into com_video_room
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="comVideoRoomId != null">
                comVideoRoomId,
            </if>
            <if test="comId != null">
                comId,
            </if>
            <if test="roomNo != null">
                roomNo,
            </if>
            <if test="startTime != null">
                startTime,
            </if>
            <if test="endTime != null">
                endTime,
            </if>
            <if test="yardmanName != null">
                yardmanName,
            </if>
            <if test="yardmanType != null">
                yardmanType,
            </if>
            <if test="yardmanTaskName != null">
                yardmanTaskName,
            </if>
            <if test="yardmanTaskExplain != null">
                yardmanTaskExplain,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="comVideoRoomId != null">
                #{comVideoRoomId},
            </if>
            <if test="comId != null">
                #{comId},
            </if>
            <if test="roomNo != null">
                #{roomNo},
            </if>
            <if test="startTime != null">
                #{startTime},
            </if>
            <if test="endTime != null">
                #{endTime},
            </if>
            <if test="yardmanName != null">
                #{yardmanName},
            </if>
            <if test="yardmanType != null">
                #{yardmanType},
            </if>
            <if test="yardmanTaskName != null">
                #{yardmanTaskName},
            </if>
            <if test="yardmanTaskExplain != null">
                #{yardmanTaskExplain},
            </if>
        </trim>
    </insert>

    <!-- 修改对象 -->
    <update id="updateEndTime" parameterType="com.bcxin.identify.business.domain.ysp.ComVideoRoom">
        UPDATE com_video_room SET endTime=#{endTime}
        WHERE comVideoRoomId = #{comVideoRoomId}
    </update>

    <!-- 返回详情信息 -->
    <select id="get" resultType="com.bcxin.identify.business.domain.ysp.ComVideoRoom">
        SELECT
        CONCAT(comVideoRoomId,'') AS comVideoRoomId
        ,comId
        ,roomNo
        ,DATE_FORMAT(startTime,'%Y-%m-%d %H:%i:%s') AS startTime
        ,DATE_FORMAT(endTime,'%Y-%m-%d %H:%i:%s') AS endTime
        ,yardmanName
        ,yardmanType
        ,yardmanTaskName
        ,yardmanTaskExplain
        FROM com_video_room
        WHERE comVideoRoomId = #{comVideoRoomId}
    </select>

    <select id="getByRoomNo" resultType="com.bcxin.identify.business.domain.ysp.ComVideoRoom">
        SELECT
        CONCAT(comVideoRoomId,'') AS comVideoRoomId
        ,comId
        ,roomNo
        ,DATE_FORMAT(startTime,'%Y-%m-%d %H:%i:%s') AS startTime
        ,DATE_FORMAT(endTime,'%Y-%m-%d %H:%i:%s') AS endTime
        ,yardmanName
        ,yardmanType
        ,yardmanTaskName
        ,yardmanTaskExplain
        FROM com_video_room
        WHERE roomNo = #{roomNo}
    </select>

    <select id="findComVideoRoomPage" resultType="java.util.Map" parameterType="com.bcxin.identify.business.domain.ysp.ComVideoRoom" >
        SELECT
        CONCAT('',t1.comVideoRoomId) AS comVideoRoomId,
        t1.comId,
        t1.roomNo,
        t1.yardmanName,
        t1.yardmanType,
        DATE_FORMAT(t1.startTime,'%Y-%m-%d %H:%i') AS startTime,
        DATE_FORMAT(t1.endTime,'%Y-%m-%d %H:%i') AS endTime,
        t1.yardmanTaskName,
        (SELECT COUNT(1) FROM per_video_room WHERE comVideoRoomId = t1.comVideoRoomId) AS perNum,
        (SELECT GROUP_CONCAT(videoPath) FROM per_video_room WHERE comVideoRoomId = t1.comVideoRoomId AND videoPath IS NOT NULL AND videoPath !='') AS paths,
        t1.yardmanTaskExplain
        FROM com_video_room t1
        WHERE t1.comId =#{comId}
        <if test="yardmanTaskName != null and yardmanTaskName != ''">
            AND (
            instr(t1.yardmanTaskName,#{yardmanTaskName})
            )
        </if>
        ORDER BY t1.startTime DESC
    </select>

</mapper>