<?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="im.zhaojun.system.mapper.TrafficStatisticsMapper">
    <resultMap id="AlarmResultMap" type="im.zhaojun.system.model.Alarm">
        <id column="operator_id" jdbcType="INTEGER" property="operatorId"/>
        <result column="id" jdbcType="VARCHAR" property="id"/>
        <result column="type" jdbcType="VARCHAR" property="type"/>
        <result column="video_id" jdbcType="VARCHAR" property="videoid"/>
        <result column="ts" jdbcType="VARCHAR" property="ts"/>
        <result column="incident_type" jdbcType="VARCHAR" property="incident_type"/>
        <result column="img_urls" jdbcType="VARCHAR" property="img_urls"/>
        <result column="video_record_url" jdbcType="VARCHAR" property="video_record_url"/>
        <result column="x" jdbcType="NUMERIC" property="x" javaType="String"/>
        <result column="y" jdbcType="NUMERIC" property="y" javaType="String"/>
        <result column="width" jdbcType="NUMERIC" property="width" javaType="String"/>
        <result column="height" jdbcType="NUMERIC" property="height" javaType="String"/>
        <result column="img_base64" jdbcType="NUMERIC" property="img_base64"/>
        <result column="videoname" jdbcType="VARCHAR" property="videoname"/>
        <result column="deptname" jdbcType="VARCHAR" property="deptname"/>
    </resultMap>


    <select id="selectVehicles" resultType="java.util.HashMap">
        select nvl(sum(data),0) "data", b.name "hour" from (
        select data,
        case when hour ='00' or hour='01' then '02:00'
        when hour ='02' or hour='03' then '04:00'
        when hour ='04' or hour='05' then '06:00'
        when hour ='06' or hour='07' then '08:00'
        when hour ='08' or hour='09' then '10:00'
        when hour ='10' or hour='11' then '12:00'
        when hour ='12' or hour='13' then '14:00'
        when hour ='14' or hour='15' then '16:00'
        when hour ='16' or hour='17' then '18:00'
        when hour ='18' or hour='19' then '20:00'
        when hour ='20' or hour='21' then '22:00'
        when hour ='22' or hour='23' then '24:00'
        end hour
        from (
        select count(*)data,
        date_format(create_time,'%T') AS hour
        from vehicle a
        <where>
            <if test='deptId !="" and deptId != null and deptId!="-1" '>
                and video_id in(
                select b.videoid
                from deptdefaultvideo b
                where xzbh=#{deptId}
                )
            </if>
            <if test="starttime != '' and starttime != null ">
                and CREATE_TIME >=STR_TO_DATE( #{starttime}, '%Y-%m-%d' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE(#{endtime}, '%Y-%m-%d' )
            </if>

        </where>
        group by CREATE_TIME
        )) a right join t_code b
        on a.hour = b.name
        where b.type = 2
        group by b.name
        order by b.name
    </select>
    <select id="selectFlowStatus" resultType="java.util.HashMap">
        select flow_status "flow_status",substr(a.ts , 12, 2) "hour"
        from flowstatus a
        right join flowstatusdetail b
        on a.id = b.flowstatus_id
        where b.video_id in(
        select b.videoid from deptdefaultvideo b where xzbh=#{deptId}
        )
        <if test="starttime != '' and starttime != null ">
            and ts >= #{starttime}
        </if>
        <if test="endtime != '' and endtime != null ">
            and ts <![CDATA[ <=  ]]>#{endtime
        </if>

    </select>
    <select id="selectVehiclesTypes" resultType="java.util.HashMap">
        select count(b.type)"data",b.type "type",
        (select name from t_code c where b.type=c.key and c.type=3 limit 1)"typename"
        from vehicletodaystatistic b
        where video_id in(
        select b.videoid from deptdefaultvideo b where xzbh=#{deptId}
        ) and b.type not in ('tricycle','cart')
        <if test="starttime != '' and starttime != null ">
            and ts >= substr( #{starttime},1,10)
        </if>
        <if test="endtime != '' and endtime != null ">
            and ts <![CDATA[ <=  ]]> substr( #{endtime},1,10)
        </if>
        group by b.type
    </select>

    <select id="selectAlarms" resultType="java.util.HashMap">
        select decode(sum(data), '', 0, sum(data)) "data" , b.name "hour" from (
        select data,
        case when hour =0 or hour=1 then '02:00'
        when hour =2 or hour=3 then '04:00'
        when hour =4 or hour=5 then '06:00'
        when hour =6 or hour=7 then '08:00'
        when hour =8 or hour=9 then '10:00'
        when hour =10 or hour=11 then '12:00'
        when hour =12 or hour=13 then '14:00'
        when hour =14 or hour=15 then '16:00'
        when hour =16 or hour=17 then '18:00'
        when hour =18 or hour=19 then '20:00'
        when hour =20 or hour=21 then '22:00'
        when hour =22 or hour=23 then '24:00'
        end hour
        from (select count(recordid )data, date_format(recordtime,'%T') hour
        from traffalarmrecord
        where fdid||'_'||channelid in(
        select b.videoid
        from deptdefaultvideo b
        <if test="deptId != '' and deptId != null and  deptId!='-1' ">
            where xzbh=#{deptId}
        </if>
        )
        <if test="starttime != '' and starttime != null ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and recordtime <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        group by recordid , recordtime
        ))a right join t_code b
        on a.hour = b.name
        where b.type = 2
        group by b.name
        order by b.name
    </select>
    <select id="selectAlarmsTypes" resultType="java.util.HashMap">
        select count(recordtype)"data",
        recordtype "type",
        name "typename"
        from traffalarmrecord a,t_code b
        where upper(a.recordtype)=b.key and b.type=2 and fdid||'_'||channelid in(
        select b.videoid
        from deptdefaultvideo b
        <if test="deptId != '' and deptId != null and  deptId!='-1' ">
            where xzbh=#{deptId}
        </if>
        )
        <if test="starttime != '' and starttime != null ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        group by recordtype,name
    </select>
    <select id="selectAlarmsByVideoName" resultType="java.util.HashMap">
        SELECT * FROM (
        select count(*)"data",b.sbbh||'_'||b.tdbh "videoid",b.tdmc "videoname"
        from traffalarmrecord a ,sbtdspsr b where
        a.fdid =b.sbbh and a.channelid=b.tdbh
        <if test="starttime != '' and starttime != null ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="videoname != '' and videoname != null ">
            and b.tdmc like '%${videoname}%'
        </if>
        group by b.sbbh||'_'||b.tdbh, b.tdmc
        order by count(*) desc
        ),(SELECT @rownum:=0) r where (@rownum := @rownum + 1)  <![CDATA[ <= ]]>#{topnum,jdbcType=INTEGER}
    </select>
    <resultMap id="statisticMap" type="im.zhaojun.system.model.VehiclesStatistic">
        <id column="videoid" jdbcType="VARCHAR" property="videoid"/>
        <result column="videoname" jdbcType="VARCHAR" property="videoname"/>
        <result column="tbdata" jdbcType="VARCHAR" property="tbdata"/>
        <result column="hbdata" jdbcType="VARCHAR" property="hbdata"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="data" jdbcType="INTEGER" property="data"/>
    </resultMap>
    <resultMap id="statisticresultMap" type="im.zhaojun.system.model.VehiclesStatisticResult">
        <result column="videoid" jdbcType="VARCHAR" property="videoid"/>
        <result column="videoname" jdbcType="VARCHAR" property="videoname"/>
        <result column="sedan" jdbcType="VARCHAR" property="sedan"/>
        <result column="goodsvan" jdbcType="VARCHAR" property="goodsvan"/>
        <result column="coach" jdbcType="VARCHAR" property="coach"/>
        <result column="fiveflow" jdbcType="VARCHAR" property="fiveflow"/>
        <result column="alarm" jdbcType="VARCHAR" property="alarm"/>
        <result column="tbsedan" jdbcType="VARCHAR" property="tbsedan"/>
        <result column="tbgoodsvan" jdbcType="VARCHAR" property="tbgoodsvan"/>
        <result column="tbcoach" jdbcType="VARCHAR" property="tbcoach"/>
        <result column="tbfiveflow" jdbcType="VARCHAR" property="tbfiveflow"/>
        <result column="hbsedan" jdbcType="VARCHAR" property="hbsedan"/>
        <result column="hbgoodsvan" jdbcType="VARCHAR" property="hbgoodsvan"/>
        <result column="hbcoach" jdbcType="VARCHAR" property="hbcoach"/>
        <result column="hbfiveflow" jdbcType="VARCHAR" property="hbfiveflow"/>
        <result column="hbalarm" jdbcType="VARCHAR" property="hbalarm"/>
        <result column="total" jdbcType="VARCHAR" property="total"/>
        <result column="other" jdbcType="VARCHAR" property="other"/>
        <result column="tbtotal" jdbcType="VARCHAR" property="tbtotal"/>
        <result column="tbother" jdbcType="VARCHAR" property="tbother"/>
        <result column="hbtotal" jdbcType="VARCHAR" property="hbtotal"/>
        <result column="hbother" jdbcType="VARCHAR" property="hbother"/>
    </resultMap>

    <resultMap id="baseResultMap" type="im.zhaojun.system.model.VehiclesStatisticResult">
        <result column="videoid" jdbcType="VARCHAR" property="videoid"/>
        <result column="videoname" jdbcType="VARCHAR" property="videoname"/>
        <result column="sedan" jdbcType="VARCHAR" property="sedan"/>
        <result column="goodsvan" jdbcType="VARCHAR" property="goodsvan"/>
        <result column="coach" jdbcType="VARCHAR" property="coach"/>
        <result column="fiveflow" jdbcType="VARCHAR" property="fiveflow"/>
        <result column="alarm" jdbcType="VARCHAR" property="alarm"/>
        <result column="total" jdbcType="VARCHAR" property="total"/>
        <result column="other" jdbcType="VARCHAR" property="other"/>
    </resultMap>


    <select id="selectStatisVehiclesByVideoIdAndType" parameterType="Map" resultMap="statisticresultMap">
        select sum(total) total, a.videoid ,videoname, sum(b.alarm) alarm ,
        sum(sedan)sedan,
        sum(goodsvan)goodsvan,
        sum(coach)coach,
        sum(other)other,
        sum(fiveflow)fiveflow from (
        select sum(total) total, video_id videoid,tdmc videoname,
        sum(case when b.type='SEDAN' then total else 0 end)sedan,
        sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total else 0 end)goodsvan,
        sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then total else 0 end)coach,
        sum(case when b.type!='COACH'and b.type!='MEDIUM_COACH' and b.type!='SEDAN'and b.type!='HEAVY_GOODS_VAN' and
        b.type!='LIGHT_GOODS_VAN' then total else 0 end)other,
        sum(b.five) fiveflow
        from vehicletodaystatistic b,sbtdspsr c
        where b.video_id=c.sbbh||'_'||c.tdbh

        <if test="starttime != '' and starttime != null ">
            and ts=substr(#{starttime},0,10)
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        <if test="ruletag != '' and ruletag != null ">
            and b.ruletag =#{ruletag}
        </if>

        <if test="deptId != '' and deptId != null ">
            and video_id in (
            select a.sbbh||'_'||a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>
        <if test="videoids!= null and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (video_id= #{item} )
            </foreach>
        </if>
        group by b.video_id ,c.tdmc
        union all
        select sum(total)total, b.video_id videoid,tdmc videoname,
        sum(case when b.type='SEDAN' then total else 0 end)sedan,
        sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total else 0 end)goodsvan,
        sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then total else 0 end)coach,
        sum(case when b.type!='COACH'and b.type!='MEDIUM_COACH' and b.type!='SEDAN'and b.type!='HEAVY_GOODS_VAN' and
        b.type!='LIGHT_GOODS_VAN' then total else 0 end)other,
        0 fiveflow
        from VEHICLESTATISTIC b,sbtdspsr c
        where b.video_id=c.sbbh||'_'||c.tdbh
        <if test="starttime != '' and starttime != null ">
            and ts >=substr( #{starttime},1,10)
        </if>
        <if test="endtime != '' and endtime != null ">
            and ts <![CDATA[ <=  ]]>substr(#{endtime},1,10)
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        <if test="ruletag != '' and ruletag != null ">
            and b.ruletag =#{ruletag}
        </if>

        <if test="deptId != '' and deptId != null ">
            and b.video_id in (
            select a.sbbh||'_'||a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>
        <if test="videoids!= null and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (video_id= #{item} )
            </foreach>
        </if>
        and not EXISTS (select video_id,ts,TYPE from vehicletodaystatistic t where t.video_id=b.video_id
        and t.ts=b.ts and t.type=b.type)
        group by b.video_id ,c.tdmc

        )a left join
        (select count(*) alarm ,c.fdid || '_' || c.channelid videoid
        from traffalarmrecord c ,sbtdspsr b where c.fdid=b.sbbh and c.channelid=b.tdbh
        <if test="statis != '' and statis != null ">
            and recordtime>=Trunc(now())
        </if>
        <if test="starttime != '' and starttime != null ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="videoname != '' and videoname != null ">
            and b.tdmc like '%${videoname}%'
        </if>

        <if test="deptId != '' and deptId != null ">
            and (fdid,channelid)in (
            select a.sbbh,a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>

        <if test="videoids!= null  and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (fdid || '_'|| channelid = #{item} )
            </foreach>
        </if>
        group by c.fdid || '_' || c.channelid
        union all
        select sum(totalcount) alarm ,c.fdid || '_' || c.channelid videoid
        from traffalarmrecordSTATE c
        ,sbtdspsr b where c.fdid=b.sbbh and c.channelid=b.tdbh
        <if test="starttime != '' and starttime != null ">
            and countdate >=substr(#{starttime},1,10)
        </if>
        <if test="endtime != '' and endtime != null ">

            and countdate  <![CDATA[ <=  ]]>substr(#{endtime},1,10)
        </if>
        <if test="videoname != '' and videoname != null ">
            and b.tdmc like '%${videoname}%'
        </if>
        <if test="deptId != '' and deptId != null ">
            and (fdid,channelid)in (
            select a.sbbh,a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>

        <if test="videoids!= null and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (fdid || '_'|| channelid = #{item} )
            </foreach>
        </if>
        group by c.fdid || '_' || c.channelid

        ) b on a.videoid=b.videoid
        group by a.videoid ,videoname
        order by total desc
    </select>


    <select id="selectVehiclesStatisByVideoIdAndType" parameterType="Map" resultMap="statisticresultMap">
        select sum(total) total, a.videoid ,videoname, sum(b.alarm) alarm ,
        sum(sedan)sedan,
        sum(goodsvan)goodsvan,
        sum(coach)coach,
        sum(other)other,
        sum(fiveflow)fiveflow
        from (
        select sum(total)total, b.video_id videoid,tdmc videoname,
        sum(case when b.type='SEDAN' then total else 0 end)sedan,
        sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total else 0 end)goodsvan,
        sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then total else 0 end)coach,
        sum(case when b.type!='COACH'and b.type!='MEDIUM_COACH' and b.type!='SEDAN'and b.type!='HEAVY_GOODS_VAN' and
        b.type!='LIGHT_GOODS_VAN' then total else 0 end)other,
        0 fiveflow
        from VEHICLESTATISTIC b,sbtdspsr c
        where b.video_id=c.sbbh||'_'||c.tdbh
        <if test="starttime != '' and starttime != null ">
            and ts >= substr(#{starttime},1,10)
        </if>
        <if test="endtime != '' and endtime != null ">
            and ts <![CDATA[ <= ]]>substr(#{endtime},1,10)
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        <if test="ruletag != '' and ruletag != null ">
            and b.ruletag =#{ruletag}
        </if>

        <if test="deptId != '' and deptId != null ">
            and b.video_id in (
            select a.sbbh||'_'||a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>
        <if test="videoids!= null and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (video_id= #{item} )
            </foreach>
        </if>
        group by b.video_id ,c.tdmc
        )a left join
        (
        select sum(totalcount) alarm ,c.fdid || '_' || c.channelid videoid
        from traffalarmrecordSTATE c
        ,sbtdspsr b where c.fdid=b.sbbh and c.channelid=b.tdbh
        <if test="starttime != '' and starttime != null ">
            and countdate >=substr(#{starttime},1,10)
        </if>
        <if test="endtime != '' and endtime != null ">

            and countdate  <![CDATA[ <=  ]]>substr(#{endtime},1,10)
        </if>
        <if test="videoname != '' and videoname != null ">
            and b.tdmc like '%${videoname}%'
        </if>
        <if test="deptId != '' and deptId != null ">
            and (fdid,channelid)in (
            select a.sbbh,a.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>

        <if test="videoids!= null and videoids.size>0">
            AND
            <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                (fdid || '_'|| channelid = #{item} )
            </foreach>
        </if>
        group by c.fdid || '_' || c.channelid
        ) b on a.videoid=b.videoid
        group by a.videoid ,videoname
        order by total desc
    </select>


    <select id="selectVehiclesByMutiParam" parameterType="Map" resultMap="baseResultMap">
        select a.* ,b.fiveflow,c.alarm from (select video_id videoid, c.tdmc videoname ,
        sum(case when b.type='SEDAN' then 1 else 0 end)sedan,
        sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then 1 else 0 end)goodsvan,
        sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then 1 else 0 end)coach,
        sum(case when b.type!='COACH'and b.type!='MEDIUM_COACH' and b.type!='SEDAN'and b.type!='HEAVY_GOODS_VAN' and
        b.type!='LIGHT_GOODS_VAN' then 1 else 0 end)other,
        count(*) total
        from vehicle b, sbtdspsr c
        where video_id = c.sbbh||'_'||tdbh
        <if test="starttime != '' and starttime != null ">
            and CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        group by b.video_id, c.tdmc) a,( select count(*) fiveflow,
        b.video_id videoid,
        c.tdmc videoname
        from vehicle b, sbtdspsr c
        where b.video_id = c.sbbh || '_' || c.tdbh
        <if test="starttime != '' and starttime != null ">
            and CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        group by b.video_id, c.tdmc)b ,( select count(*) alarm,
        c.sbbh || '_' || c.tdbh videoid,
        c.tdmc "video_name"
        from traffalarmrecord a, sbtdspsr c
        where a.fdid = c.sbbh
        and a.channelid = c.tdbh
        <if test="starttime != null  and starttime != '' ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != null  and  endtime != ''">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="videoname != '' and videoname != null ">
            and c.tdmc like '%${videoname}%'
        </if>
        group by c.sbbh || '_' || c.tdbh, c.tdmc) c where
        a.videoid=b.videoid and b.videoid=c.videoid
        and a.videoid in (
        select a.sbbh||'_'||a.tdbh
        from sbtdspsr a,
        (SELECT dept_id
        FROM dept
        START WITH dept_id =#{deptId}
        CONNECT BY parent_id = PRIOR dept_id) b
        where a.xzbh = b.dept_id
        )order by a.total desc
        group by a.videoid, videoname
    </select>


    <select id="selectAlarmsByTime" resultMap="AlarmResultMap">
        select a.*,b.name "typename",c.tdmc videoname,c.qymc deptname
        from traffalarmrecord a,t_code b,sbtdspsr c
        where a.recordtype=b.key and  b.type=1  and a.fdid =c.sbbh and a.channelid=c.tdbh
        <if test="starttime != null  and starttime != '' ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != null  and  endtime != ''">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="type != null  and type != ''">
            and b.key =#{type}
        </if>
        <if test="videoname != null and  videoname != '' ">
            and c.tdmc like '%${videoname}%'
        </if>
        <if test="deptId != null ">
            and c.sbbh||'_'||c.tdbh in(
            select c.sbbh||'_'||c.tdbh
            from sbtdspsr a,
            (SELECT dept_id
            FROM dept
            START WITH dept_id =#{deptId}
            CONNECT BY parent_id = PRIOR dept_id) b
            where a.xzbh = b.dept_id
            )
        </if>
        order by video_id


    </select>
    <resultMap id="onMonthVehiclesResultMap" type="im.zhaojun.system.model.VehiclesStatisticResult">
        <result column="time" jdbcType="VARCHAR" property="recordtime"/>
        <result column="sedan" jdbcType="VARCHAR" property="sedan"/>
        <result column="goodsvan" jdbcType="VARCHAR" property="goodsvan"/>
        <result column="coach" jdbcType="VARCHAR" property="coach"/>
        <result column="total" jdbcType="VARCHAR" property="total"/>
        <result column="other" jdbcType="VARCHAR" property="other"/>
    </resultMap>
    <resultMap id="CodeResultMap" type="im.zhaojun.system.model.CodeData">
        <id column="key" jdbcType="VARCHAR" property="key"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="type" jdbcType="VARCHAR" property="type"/>
        <result column="alarmlevel" jdbcType="VARCHAR" property="alarmlevel"/>
    </resultMap>

    <resultMap id="onMonthtraffRecordsResultMap" type="im.zhaojun.system.model.RecordResult">
        <result column="total" jdbcType="VARCHAR" property="total"/>
        <result column="time" jdbcType="VARCHAR" property="recordtime"/>
        <result column="illegalparking" jdbcType="VARCHAR" property="illegalparking"/>
        <result column="congestion" jdbcType="VARCHAR" property="congestion"/>
        <result column="notcongestion" jdbcType="VARCHAR" property="notcongestion"/>
        <result column="vehicleban" jdbcType="VARCHAR" property="vehicleban"/>
        <result column="nomotorban" jdbcType="VARCHAR" property="nomotorban"/>
        <result column="slowdeive" jdbcType="VARCHAR" property="slowdeive"/>
        <result column="roadworks" jdbcType="VARCHAR" property="roadworks"/>
        <result column="notslowdeive" jdbcType="VARCHAR" property="notslowdeive"/>
        <result column="wrongdirection" jdbcType="VARCHAR" property="wrongdirection"/>
        <result column="severeweather" jdbcType="VARCHAR" property="severeweather"/>
    </resultMap>
    <select id="selectCodeByCodeid" resultMap="CodeResultMap">
        select * from t_code where type=#{codeid}
        <if test="level != '' and level != null ">
            and alarmlevel = #{level}
        </if>
    </select>


    <select id="selectvideoVehiclesOrAlarms" resultType="java.util.HashMap">

        select count(*) "data" ,'total' "name", video_id "videoid" , c.tdmc "videoname"
        from vehicle a,sbtdspsr c
        where a.video_id = c.sbbh||'_'||c.tdbh
        <if test="starttime != '' and starttime != null ">
            and CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="deptId != '' and deptId != null ">
            and (sbbh,tdbh) in (
            select sbbh,tdbh
            from sbtdspsr a
            where xzbh in (select t1.dept_id
            from dept t1
            left join dept t2
            on t1.parent_id = t2.dept_id
            where t1.parent_id =#{deptId}
            or t1.dept_id = #{deptId}
            or t2.parent_id =#{deptId}
            or t2.dept_id = #{deptId}
            )

            )
        </if>

        group by a.video_id,c.video_name

        union all
        select count(*) "data",'alarm' "name", c.sbbh||'_'||c.tdbh "videoid",
        c.tdmc "videoname"
        from traffalarmrecord a,sbtdspsr c
        where a.fdid = c.sbbh and a.channelid=c.tdbh
        <if test="starttime != '' and starttime != null ">
            and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
        </if>
        <if test="endtime != '' and endtime != null ">
            and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
        </if>

        <if test="deptId != '' and deptId != null ">
            and (c.sbbh, c.tdbh) in (
            select sbbh,tdbh
            from sbtdspsr a
            where xzbh in (select t1.dept_id
            from dept t1
            left join dept t2
            on t1.parent_id = t2.dept_id
            where t1.parent_id =#{deptId}
            or t1.dept_id =#{deptId})
            or t2.parent_id =#{deptId}
            or t2.dept_id = #{deptId}
            )
        </if>
        group by c.sbbh||'_'||c.tdbh,c.tdmc

    </select>


    <select id="queryTraffalarmrecordByPage" resultType="im.zhaojun.system.model.TraffpictureParam">
        SELECT TA.*,TA.createtime recordtime, A.tdmc,
        (select dept_name from dept b where b.dept_id=TA.areaid limit 1 )xzmc,
        b.name recordname, b.alarmlevel recordlevel
        FROM traffpicture TA
        inner join (select b.videoid, lower(recordtype)recordtype
        from videoerecordtype b
        inner join role_videoerecordtype c
        on b.id = c.videorecordtypeid   inner join user_role d
        on c.roleid = d.role_id
        and d.user_id =#{userId} ) b
        on TA.fdid || '_' || TA.channelid = b.videoid  and  TA.recordtype=b.recordtype
        LEFT JOIN sbtdspsr A ON TA.fdid = A.sbbh AND TA.channelid = A.tdbh
        LEFT JOIN t_code b ON TA.recordtype =LOWER(b.key) and b.type=1

        <where>
            <if test="recordtype!=null and recordtype != ''">
                AND TA.recordtype = #{recordtype}
            </if>

            <if test="starttime!=null and starttime != ''">
                AND TA.createtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime!=null and endtime != ''">
                AND TA.createtime &lt;= STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>

            <if test="checkstatus!=null and checkstatus!=19">
                AND TA.checkstatus = #{checkstatus}
            </if>
            <if test="checkstatus!=null and checkstatus==19">
                AND ( TA.checkstatus = 9 OR TA.checkstatus = 1 )
            </if>

            <if test="processstatus!= null and processstatus.size>0">
                AND
                <foreach collection="processstatus" open="(" close=")" separator=" OR " item="item">
                    (processstatus= #{item} )
                </foreach>
            </if>

            <if test="construction!=null and construction==1">
                AND TA.recordtype in ('roadworks_out_of_line','roadworks')
            </if>
            <if test="construction!=null and construction==2">
                AND TA.recordtype not in ('roadworks_out_of_line','roadworks')
            </if>
            <!--<if test='rectificationtype !=null and rectificationtype=="1"'>-->
                <!--AND rectificationtype='1'-->
            <!--</if>-->

            <!--<if test='rectificationtype !=null and rectificationtype=="0"'>-->
                <!--AND (rectificationtype is null or rectificationtype='')-->
            <!--</if>-->

            <!--<if test='pushstatus !=null and pushstatus=="1"'>-->
            <!--AND (manualstatus =1)-->
            <!--</if>-->
            <!--<if  test='pushstatus !=null and pushstatus=="0"'>-->
            <!--AND (manualstatus is null or manualstatus=0)-->
            <!--</if>-->


            <if test="deptid!=null and deptid != ''">
                AND TA.areaid in (
                select t1.dept_id
                from dept t1
                left join dept t2
                on t1.parent_id = t2.dept_id
                where t1.parent_id =#{deptid}
                or t1.dept_id =#{deptid}
                or
                t2.parent_id = #{deptid}
                or t2.dept_id = #{deptid}
                )
            </if>

            <if test="videoids!= null  and videoids.size>0">
                AND
                <foreach collection="videoids" open="(" close=")" separator=" OR " item="item">
                    ( TA.fdid || '_'|| TA.channelid = #{item} )
                </foreach>
            </if>


            <!--<if test="objlables!=null and objlables.size>0">-->
                <!--AND-->
                <!--<foreach collection="objlables" open="(" close=")" separator=" OR " item="item">-->
                    <!--( objlable= #{item} )-->
                <!--</foreach>-->
            <!--</if>-->
        </where>
        ORDER BY TA.createtime DESC
    </select>


    <update id="updateTraffalarmrecordById" parameterType="java.util.List">
                update traffpicture      set  processstatus=
                <foreach collection="list" item="item" index="index" open="case id" close="end" separator=" ">
                    when  #{item.id,jdbcType=BIGINT} then  #{item.processstatus,jdbcType=VARCHAR}
                </foreach>
                where id in
           <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
                #{item.id,jdbcType=BIGINT}
           </foreach>
    </update>


    <update id="updateVideochecktime" parameterType="java.util.List">
        MERGE INTO videochecktime T1
        USING(
        select fdid,channelid,recordtype,max(recordtime)recordtime
        from (
        <foreach collection="list" item="item" index="index" separator="UNION" open="(" close=")">
            SELECT #{item.fdid,jdbcType=VARCHAR} FDID,
            #{item.channelid,jdbcType=VARCHAR} CHANNELID,
            #{item.recordtime,jdbcType=TIMESTAMP} RECORDTIME,
            #{item.recordtype,jdbcType=VARCHAR} RECORDTYPE
            from dual
        </foreach>
        )group by FDID,CHANNELID,RECORDTYPE
        ) T
        ON (T1.FDID = T.FDID and T1.CHANNELID = T.CHANNELID and T1.recordtype=T.recordtype )
        WHEN MATCHED THEN
        UPDATE SET T1.LASTCHECKTIME = T.recordtime
        WHEN NOT MATCHED THEN
        INSERT
        (fdid, channelid, recordtype, lastchecktime)
        VALUES
        (T.fdid, T.channelid, T.recordtype, T.recordtime)
    </update>

    <select id="todayvehiclesByVideoId" resultMap="statisticresultMap">
        select b.video_id videoid,
        (select tdmc from sbtdspsr t where t.sbbh || '_' || t.tdbh = video_id
        limit 1) videoname,
        sum(case
        when b.type = 'SEDAN' then
        1
        else
        0
        end) sedan,
        sum(case
        when b.type = 'HEAVY_GOODS_VAN' or
        b.type = 'LIGHT_GOODS_VAN' then
        1
        else
        0
        end) goodsvan,
        sum(case
        when b.type = 'COACH' or b.type = 'MEDIUM_COACH' then
        1
        else
        0
        end) coach,
        sum(case
        when b.type != 'COACH' and b.type != 'MEDIUM_COACH' and
        b.type != 'SEDAN' and
        b.type != 'HEAVY_GOODS_VAN' and
        b.type != 'LIGHT_GOODS_VAN' then
        1
        else
        0
        end) other,
        count(*) total
        from vehicle b
        <where>
            <if test="starttime != '' and starttime != null ">
                CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and video_id =#{videoId}
            </if>
        </where>
        group by video_id
        order by total desc

    </select>


    <select id="todayvehiclesByeveryhour" resultMap="onMonthVehiclesResultMap">
        select a.bb || ':00' time,
        nvl( b.sedan,0)sedan,
        nvl(b.goodsvan,0)goodsvan,
        nvl( b.coach,0)coach,
        nvl( b.other,0)other
        from (SELECT bb
        FROM (SELECT LPAD((@rownum := @rownum + 1) - 1, 2, 0) bb
        FROM DUAL,(SELECT @rownum:=0) r
        CONNECT BY LEVEL <![CDATA[ <=  ]]>  24)) a
        left join (select STR_TO_DATE(CREATE_TIME,'%T') hour,
        sum(case when c.type='SEDAN' then 1 else 0 end)sedan,
        sum(case when c.type='HEAVY_GOODS_VAN' or c.type= 'LIGHT_GOODS_VAN' then 1 else 0 end)goodsvan,
        sum(case when c.type='COACH'or c.type='MEDIUM_COACH' then 1 else 0 end)coach,
        sum(case when c.type!='COACH'and c.type!='MEDIUM_COACH'
        and c.type!='SEDAN'and c.type!='HEAVY_GOODS_VAN' and c.type!='LIGHT_GOODS_VAN' then 1 else 0 end)other
        from vehicle c
        <where>
            <if test="starttime != '' and starttime != null ">
                and CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and video_id =#{videoId}
            </if>
        </where>
        group by DATE_FORMAT(CREATE_TIME,'%T') )b
        on a.bb = b.hour
        group by a.bb,
        b.sedan,
        b.goodsvan,
        b.coach,
        b.other
        order by time
    </select>

    <select id="todaytraffRecordByeveryhour" resultMap="onMonthtraffRecordsResultMap">
        select a.bb || ':00' time,count(*)total,
        nvl( b.illegalparking,0)illegalparking,
        nvl(b.congestion,0)congestion,
        nvl( b.notcongestion,0)notcongestion,
        nvl( b.vehicleban,0)vehicleban,
        nvl( b.nomotorban,0)nomotorban,
        nvl( b.slowdeive,0)slowdeive,
        nvl( b.roadworks,0)roadworks,
        nvl(b.notslowdeive,0)notslowdeive,
        nvl( b.wrongdirection,0)wrongdirection,
        nvl( b.severeweather,0)severeweather
        from (SELECT bb
        FROM (SELECT LPAD((@rownum := @rownum + 1) - 1, 2, 0) bb
        FROM DUAL
        CONNECT BY LEVEL <![CDATA[ <=  ]]>  24)) a
        left join (select DATE_FORMAT(b.RECORDTIME, '%T') hour,
        sum(case
        when b.recordtype = 'illegal_parking' then
        1
        else
        0
        end) illegalparking,
        sum(case
        when b.recordtype = 'congestion' then
        1
        else
        0
        end) congestion,
        sum(case
        when b.recordtype = 'not_congestion' then
        1
        else
        0
        end) notcongestion,
        sum(case
        when b.recordtype = 'vehicle_ban' then
        1
        else
        0
        end) vehicleban,
        sum(case
        when b.recordtype = 'no_motor_ban' then
        1
        else
        0
        end) nomotorban,
        sum(case
        when b.recordtype = 'slow_deive' then
        1
        else
        0
        end) slowdeive,
        sum(case
        when b.recordtype = 'roadworks' then
        1
        else
        0
        end) roadworks,
        sum(case
        when b.recordtype = 'not_slow_deive' then
        1
        else
        0
        end) notslowdeive,
        sum(case
        when b.recordtype = 'wrong_direction' then
        1
        else
        0
        end) wrongdirection,
        sum(case
        when b.recordtype = 'severe_weather' then
        1
        else
        0
        end) severeweather,
        count(*) data
        from traffalarmrecord b
        <where>
            <if test="starttime != '' and starttime != null ">
                and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and fdid || '_'||channelid =#{videoId}
            </if>

        </where>

        group by DATE_FORMAT(b.RECORDTIME, '%T')) b
        on a.bb = b.hour
        group by a.bb,
        b.illegalparking,
        b.congestion,
        b.notcongestion,
        b.vehicleban,
        b.nomotorban,
        b.slowdeive,
        b.roadworks,
        b.notslowdeive,
        b.wrongdirection,
        b.severeweather
        order by time
    </select>


    <resultMap id="todaytraffRecordsResultMap" type="im.zhaojun.system.model.RecordResult">
        <result column="total" jdbcType="VARCHAR" property="total"/>
        <result column="illegalparking" jdbcType="VARCHAR" property="illegalparking"/>
        <result column="congestion" jdbcType="VARCHAR" property="congestion"/>
        <result column="notcongestion" jdbcType="VARCHAR" property="notcongestion"/>
        <result column="vehicleban" jdbcType="VARCHAR" property="vehicleban"/>
        <result column="nomotorban" jdbcType="VARCHAR" property="nomotorban"/>
        <result column="slowdeive" jdbcType="VARCHAR" property="slowdeive"/>
        <result column="roadworks" jdbcType="VARCHAR" property="roadworks"/>
        <result column="notslowdeive" jdbcType="VARCHAR" property="notslowdeive"/>
        <result column="wrongdirection" jdbcType="VARCHAR" property="wrongdirection"/>
        <result column="severeweather" jdbcType="VARCHAR" property="severeweather"/>
    </resultMap>


    <select id="todaytraffRecords" resultMap="todaytraffRecordsResultMap">
        select count(*) total,
        sum(case
        when b.recordtype = 'illegal_parking' then
        1
        else
        0
        end) illegalparking,
        sum(case
        when b.recordtype = 'congestion' then
        1
        else
        0
        end) congestion,
        sum(case
        when b.recordtype = 'not_congestion' then
        1
        else
        0
        end) notcongestion,
        sum(case
        when b.recordtype = 'vehicle_ban' then
        1
        else
        0
        end) vehicleban,
        sum(case
        when b.recordtype = 'no_motor_ban' then
        1
        else
        0
        end) nomotorban,
        sum(case
        when b.recordtype = 'slow_deive' then
        1
        else
        0
        end) slowdeive,
        sum(case
        when b.recordtype = 'roadworks' then
        1
        else
        0
        end) roadworks,
        sum(case
        when b.recordtype = 'not_slow_deive' then
        1
        else
        0
        end) notslowdeive,
        sum(case
        when b.recordtype = 'wrong_direction' then
        1
        else
        0
        end) wrongdirection,
        sum(case
        when b.recordtype = 'severe_weather' then
        1
        else
        0
        end) severeweather
        from traffalarmrecord b
        <where>
            <if test="starttime != '' and starttime != null ">
                and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and fdid || '_'||channelid =#{videoId}
            </if>

        </where>
        group by fdid || '_'||channelid

    </select>

    <select id="todaythbtraffRecords" resultType="java.util.HashMap">
        select count(*) total
        from traffalarmrecord b
        <where>
            <if test="starttime != '' and starttime != null ">
                and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and recordtime  <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and fdid || '_'||channelid =#{videoId}
            </if>
        </where>
        group by fdid || '_'||channelid
    </select>

    <select id="selecthistorytraffRecords" resultMap="onMonthtraffRecordsResultMap">
        select a.days time,b.* from (SELECT
        TO_CHAR(STR_TO_DATE(#{starttime}, 'YYYY-MM-DD') + (@rownum := @rownum + 1) - 1, 'MM-DD') days
        FROM
        DUAL,(SELECT @rownum:=0) r CONNECT BY (@rownum := @rownum + 1) <![CDATA[ <=  ]]> STR_TO_DATE(#{endtime}, 'YYYY-MM-DD') - STR_TO_DATE(#{starttime}, 'YYYY-MM-DD')
        + 1)a

        left join (select count(*) total,DATE_FORMAT(recordtime,'%m-%d')recordtime,
        sum(case
        when b.recordtype = 'illegal_parking' then
        1
        else
        0
        end) illegalparking,
        sum(case
        when b.recordtype = 'congestion' then
        1
        else
        0
        end) congestion,
        sum(case
        when b.recordtype = 'not_congestion' then
        1
        else
        0
        end) notcongestion,
        sum(case
        when b.recordtype = 'vehicle_ban' then
        1
        else
        0
        end) vehicleban,
        sum(case
        when b.recordtype = 'no_motor_ban' then
        1
        else
        0
        end) nomotorban,
        sum(case
        when b.recordtype = 'slow_deive' then
        1
        else
        0
        end) slowdeive,
        sum(case
        when b.recordtype = 'roadworks' then
        1
        else
        0
        end) roadworks,
        sum(case
        when b.recordtype = 'not_slow_deive' then
        1
        else
        0
        end) notslowdeive,
        sum(case
        when b.recordtype = 'wrong_direction' then
        1
        else
        0
        end) wrongdirection,
        sum(case
        when b.recordtype = 'severe_weather' then
        1
        else
        0
        end) severeweather
        from traffalarmrecord b
        <where>
            <if test="starttime != '' and starttime != null ">
                and recordtime >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>

            <if test="endtime != '' and endtime != null ">
                and recordtime <![CDATA[ <=  ]]> STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and fdid || '_'||channelid =#{videoId}
            </if>

        </where>
        group by DATE_FORMAT(recordtime,'"%m-%d'))b on a.days=b.recordtime
        order by days
    </select>


    <select id="selecthistoryvehicles" resultMap="onMonthVehiclesResultMap">
        select a.days time,b.* from (SELECT
        TO_CHAR(STR_TO_DATE(#{starttime}, 'YYYY-MM-DD') + (@rownum := @rownum + 1) - 1, 'MM-DD') days
        FROM
        DUAL ,(SELECT @rownum:=0) r CONNECT BY (@rownum := @rownum + 1) <![CDATA[ <=  ]]> TO_DATE(#{endtime}, 'YYYY-MM-DD') - STR_TO_DATE(#{starttime}, 'YYYY-MM-DD')
        + 1)a

        left join (select count(*) total,DATE_FORMAT(CREATE_TIME,'%m-%d')recordtime,
        sum(case when c.type='SEDAN' then 1 else 0 end)sedan,
        sum(case when c.type='HEAVY_GOODS_VAN' or c.type= 'LIGHT_GOODS_VAN' then 1 else 0 end)goodsvan,
        sum(case when c.type='COACH'or c.type='MEDIUM_COACH' then 1 else 0 end)coach,
        sum(case when c.type!='COACH'and c.type!='MEDIUM_COACH'
        and c.type!='SEDAN'and c.type!='HEAVY_GOODS_VAN' and c.type!='LIGHT_GOODS_VAN' then 1 else 0 end)other
        from vehicle c
        <where>
            <if test="starttime != '' and starttime != null ">
                and CREATE_TIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                and CREATE_TIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="videoId != '' and videoId != null ">
                and c.video_id =#{videoId}
            </if>
        </where>
        group by DATE_FORMAT(CREATE_TIME,'%m-%d'))b on a.days=b.recordtime
        order by days
    </select>


    <select id="selectPushRecordsBypage" resultType="im.zhaojun.system.model.TraffalarmrecordResult">
        SELECT A.*,B.TDMC TDMC,C.NAME RECORDNAME,
        (SELECT dept_NAME FROM dept B WHERE B.dept_ID=A.AREAID limit 1 )XZMC
        FROM traffalarmrecord A
        LEFT JOIN SBTDSPSR B ON A.FDID=B.SBBH AND A.CHANNELID=B.TDBH
        LEFT JOIN T_CODE C ON C.KEY=UPPER(RECORDTYPE)
        <where>
            <if test="starttime != '' and starttime != null ">
                AND RECORDTIME >= STR_TO_DATE( #{starttime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endtime != '' and endtime != null ">
                AND RECORDTIME <![CDATA[ <=  ]]>STR_TO_DATE( #{endtime}, '%Y-%m-%d %H:%i:%s' )
            </if>

            <if test="pushstatus!=null and pushstatus!=9">
                AND PUSHSTATUS = #{pushstatus}
            </if>
            <if test="pushstatus!=null and pushstatus==9">
                AND (PUSHSTATUS is null OR PUSHSTATUS=9)
            </if>
            <if test="deptid!=null and deptid!=''">
                AND areaid in (SELECT dept_id
                FROM dept
                START WITH dept_id =#{deptid}
                CONNECT BY parent_id = PRIOR dept_id)
            </if>

            <if test="construction!=null and construction==1">
                AND recordtype in ('roadworks_out_of_line','roadworks')
            </if>
            <if test="construction!=null and construction==2">
                AND recordtype not in ('roadworks_out_of_line','roadworks')
            </if>
        </where>
        ORDER BY RECORDTIME DESC
    </select>

    <select id="selecteventresultBypage" parameterType="im.zhaojun.system.model.TraffalarmrecordstatParams"
            resultType="im.zhaojun.system.model.Traffalarmrecordstate">
        <if test="type!=null and type ==1">
            WITH A AS (
            SELECT DATE_FORMAT(STR_TO_DATE(#{starttime},'%Y-%m-%d')+((@rownum := @rownum + 1)-1),'yyyy-mm-dd') COUNTDATE
            FROM dual ,(SELECT @rownum:=0) r CONNECT BY (@rownum := @rownum + 1) &lt;= STR_TO_DATE(#{endtime},'yyyy-mm-dd') - STR_TO_DATE(#{starttime},'yyyy-mm-dd')+1
            ),B AS (
            SELECT COUNTDATE, NVL(SUM(TOTALCOUNT),0) TOTALCOUNT,
            NVL(SUM(NOPUSH),0) NOPUSHCOUNT,
            NVL(SUM(FAILPUSH),0) FAILPUSHCOUNT,
            NVL(SUM(SUCCESSPUSH),0)SUCCESSPUSHCOUNT from traffalarmrecordSTAT TA
            <where>
                <if test="starttime!=null">
                    AND TA.COUNTDATE >= #{starttime}
                </if>
                <if test="endtime!=null">
                    AND TA.COUNTDATE &lt;= #{endtime}
                </if>
                <if test="areaid!=null and areaid != ''">
                    AND TA.AREAID in (SELECT dept_id
                    FROM dept
                    START WITH dept_id =#{areaid}
                    CONNECT BY parent_id = PRIOR dept_id)
                </if>
                <if test="eventtype!=null and eventtype != ''">
                    AND TA.EVENTTYPE = #{eventtype}
                </if>
            </where>
            GROUP BY COUNTDATE
            )
            SELECT A.*, B.TOTALCOUNT,B.NOPUSHCOUNT,B.FAILPUSHCOUNT,B.SUCCESSPUSHCOUNT,(select dept_name from dept where
            dept_id=#{areaid} )AREANAME
            FROM A LEFT JOIN B ON A.COUNTDATE=B.COUNTDATE
            ORDER BY A.COUNTDATE DESC
        </if>
        <if test="type!=null and type ==2">
            WITH A AS (
            SELECT DATE_FORMAT(ADD_MONTHS(STR_TO_DATE(SUBSTR(#{starttime}, 1, 7),'%Y-%m'),(@rownum := @rownum + 1)-1),'yyyy-mm') COUNTDATE
            FROM dual ,(SELECT @rownum:=0) r CONNECT BY (@rownum := @rownum + 1) &lt;= MONTHS_BETWEEN(STR_TO_DATE(SUBSTR(#{endtime}, 1,
            7),'yyyy-mm'),STR_TO_DATE(SUBSTR(#{starttime}, 1, 7),'yyyy-mm'))+1
            ) ,B AS (
            SELECT (SUBSTR(COUNTDATE, 1, 7)) COUNTDATE,
            NVL(SUM(TOTALCOUNT),0) TOTALCOUNT,
            NVL(SUM(NOPUSH),0) NOPUSHCOUNT,
            NVL(SUM(FAILPUSH),0) FAILPUSHCOUNT,
            NVL(SUM(SUCCESSPUSH),0)SUCCESSPUSHCOUNT
            from traffalarmrecordSTAT TA
            <where>
                <if test="starttime!=null">
                    AND TA.COUNTDATE >= #{starttime} ||'-01'
                </if>
                <if test="endtime!=null">
                    AND TA.COUNTDATE &lt;= #{endtime} || '-31'
                </if>
                <if test="areaid!=null and areaid != ''">
                    AND TA.AREAID in (SELECT dept_id
                    FROM dept
                    START WITH dept_id =#{areaid}
                    CONNECT BY parent_id = PRIOR dept_id)
                </if>
                <if test="eventtype!=null and eventtype != ''">
                    AND TA.EVENTTYPE = #{eventtype}
                </if>
            </where>
            GROUP BY (SUBSTR(COUNTDATE, 1, 7))
            )
            SELECT A.*, B.TOTALCOUNT,B.NOPUSHCOUNT,B.FAILPUSHCOUNT,B.SUCCESSPUSHCOUNT,(select dept_name from dept where
            dept_id=#{areaid} )AREANAME
            FROM A LEFT JOIN B ON A.COUNTDATE=B.COUNTDATE
            ORDER BY A.COUNTDATE DESC
        </if>
        <if test="type==null or type ==0">
            SELECT * from traffalarmrecordSTAT
        </if>


    </select>

    <delete id="deleteTraffalarmrecordById">
            delete  from  traffalarmrecord where  recordid= #{recordid}
    </delete>


    <update id="updateTraffalarmrecordPushStatusById">
        update  traffalarmrecord set manualstatus=1 where  recordid= #{recordid,jdbcType=VARCHAR}
    </update>

    <select id="queryTraffPedeDetail" resultType="im.zhaojun.system.model.Pedestrian">
        select
        (select  name from  t_code t where  type='GenderType' and t.key=gender ) gender,
        (select  name from  t_code t where  type='AgeType' and t.key=age ) age,
        (select  name from  t_code t where  type='AngleType' and t.key=angle )angle,
        hasbackpack,
        hasglasses,
        hascarrybag,
        hasumbrella,
        (select  name  from  t_code t where t.type='CoatType' and t.key=coatlength)coatlength,
        coatcolornums,
        (select  group_concat(name separator ',') name from  t_code t where  type='ObjectColorType' and  instr( coatcolor,t.key)>0 ) coatcolor,
       (select  name  from  t_code  t  where  t.type='TrousersType' and t.key=trouserslength) trouserslength,
        trouserscolornums,
        (select  group_concat(name separator ',') name from  t_code t where  type='ObjectColorType' and  instr( trouserscolor,t.key)>0 ) trouserscolor,
        headx 'HeadBoundingBox.x',
        heady 'HeadBoundingBox.y',
        headw 'HeadBoundingBox.w',
        headh 'HeadBoundingBox.h',
        upperx  'UpperBoundingBox.x',
        uppery  'UpperBoundingBox.y',
        upperw  'UpperBoundingBox.w',
        upperh  'UpperBoundingBox.h',
        lowerx  'LowerBoundingBox.x',
        lowery 'LowerBoundingBox.y',
        lowerw 'LowerBoundingBox.w',
        lowerh 'LowerBoundingBox.h',
        facex   'FaceBoundingBox.x',
        facey 'FaceBoundingBox.y',
        facew  'FaceBoundingBox.w',
        faceh 'FaceBoundingBox.h',
        hashat,
        hasmask,
        (select  name from  t_code t where  type='HairStyle' and t.key=hairstyle )    hairstyle,
        (select  name from  t_code  t where  type='CoatTexture' and t.key=coattexture )    coattexture    ,
        (select  name from  t_code  t where  type='TrousersTexture' and t.key=trouserstexture )    trouserstexture    ,        hastrolley,
        hasluggage,
        (select  group_concat(name separator ',') name from  t_code t where  type='ObjectColorType' and  instr( luggagecolornums,t.key)>0 ) luggagecolornums,
        (select  group_concat(name separator ',') name from  t_code t where  type='ObjectColorType' and t.key=luggagecolor )    luggagecolor  ,
        hasknife,
        objectx   'ObjectBoundingBox.x',
        objecty   'ObjectBoundingBox.y',
        objectw 'ObjectBoundingBox.w',
        objecth 'ObjectBoundingBox.h'
        from  pedestrian where  id=#{id}
    </select>


    <select id="queryTrafficDetail" resultType="im.zhaojun.system.model.Traffic">
        select  id,type,
        objectx 'ObjectBoundingBox.x',
        objecty 'ObjectBoundingBox.y',
        objectw 'ObjectBoundingBox.w',
        objecth 'ObjectBoundingBox.h',
       (select  name  from  t_code  t  where  t.type='VehicleClassType' and  t.key=vehicleclass) vehicleclass ,
        vehiclecolornums,
        (select  group_concat(name separator ',') name from  t_code t where  type='ObjectColorType' and  instr( vehiclecolor,t.key)>0 ) vehiclecolor,
        vehiclebrand,
        mainbrandname,
        subbrandname,
        yearname,
        hasplate,
        (select  name  from  t_code  t  where  t.type='PlateClassType' and  t.key=plateclass) plateclass ,
        (select  name  from  t_code  t  where  t.type='ObjectColorType' and  t.key=platecolor) platecolor ,
        plateno,
        (select  name  from  t_code  t  where  t.type='PlateNeatnessType' and  t.key=plateneatness) plateneatness ,
        (select  name  from  t_code  t  where  t.type='AngleType' and  t.key=angle) angle,
        sunvisor,
        paper,
        decoration,
        trafficdrop,
        tag,
        maindriver 'SafetyBelt.maindriver',
        codriver 'SafetyBelt.codriver',
        hascall,
        hascrash,
        hasdanger,
        hasskylight,
        hasbaggage,
        hasaerial  from  traffic where  id=#{id}

    </select>
    
    <select id="queryTraffFaceDetail" resultType="im.zhaojun.system.model.Face">
        select  id,
        facex 'FaceBoundingBox.x',
        facey 'FaceBoundingBox.y',
        facew 'FaceBoundingBox.w',
        faceh 'FaceBoundingBox.h',
        headx 'HeadBoundingBox.x',
        heady 'HeadBoundingBox.y',
        headw 'HeadBoundingBox.w',
        headh 'HeadBoundingBox.h',
        (select  name  from  t_code  t  where  t.type='GenderType' and  t.key=gender) gender ,
        (select  name  from  t_code  t  where  t.type='AgeType' and  t.key=age) age,
        hasglasses,
        hashat,
        hasmask,
        (select  name  from  t_code  t  where  t.type='HairStyle' and  t.key=hairstyle) hairstyle
        from  face
        where id=#{id}
    </select>

    <select id="queryTraffPeopleRideBicycDetail" resultType="im.zhaojun.system.model.PeopleRideBicyc">
       select id,
        type,
        objectx 'ObjectBoundingBox.x',
        objecty 'ObjectBoundingBox.y',
        obectw 'ObjectBoundingBox.w',
        objecth 'ObjectBoundingBox.h',
        (select  name  from  t_code  t  where  t.type='BikeClassType' and  t.key=bikeclass) bikeclass   ,
        (select  name  from  t_code  t  where  t.type='GenderType' and  t.key=gender) gender,
        age,
        (select  name  from  t_code  t  where  t.type='AngleType' and  t.key=angle) angle   ,
        hasbackpack,
        hasglasses,
        hasmask,
        hascarrybag,
        hasumbrella,
        coatlength,
        hasplate,
        plateno,
        hashelmet,
        (select  name  from  t_code  t  where  t.type='ObjectColorType' and  t.key=helmetcolor) helmetcolor ,
        coatcolornums,
        (select  name  from  t_code  t  where  t.type='CoatTexture' and  t.key=coattexture)    coattexture,
        facex 'FaceBoundingBox.x',
        facey 'FaceBoundingBox.y',
        facew 'FaceBoundingBox.w',
        faceh 'FaceBoundingBox.h',
        (select  name  from  t_code  t  where  t.type='SocialAttributeType' and  t.key=socialattribute)    socialattribute,
        (select  name  from  t_code  t  where  t.type='EnterpriseType' and  t.key=enterprise)    enterprise,
        haspassenger from  peopleridebicyc where  id=#{id}
    </select>
</mapper>