<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzjt.mapper.TraffFlowMapper">


    <insert id="insertlist"  parameterType="com.hzjt.domain.Vehiclesdetail" >
            INSERT INTO vehiclesdetail (obj_id,  vehiclesid, type,  direction,classification_confidence,ruletag  )
            values( #{obj_id,jdbcType=VARCHAR},
            #{vehiclesid,jdbcType=VARCHAR},
            #{type,jdbcType=VARCHAR},
            #{direction,jdbcType=NUMERIC},
            #{classification_confidence,jdbcType=NUMERIC},
            #{ruleTag,jdbcType=VARCHAR})
    </insert>

    <insert id="add" parameterType="com.hzjt.domain.Vehicles">
        insert into vehicles ( id,type,video_id,ts )
        values(#{id,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR},
         #{video_id,jdbcType=VARCHAR},#{ts,jdbcType=VARCHAR})
    </insert>
    <select id="selectFiveAndDayFlow"  resultType="java.util.HashMap">
        select
        NVL(sum(case when ts>=to_char( (SYSDATE-1-5/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char (SYSDATE-1,'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)tbfiveflow,
        NVL(sum(case when ts>=to_char( (SYSDATE-10/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)hbfiveflow,
        NVL(sum(case when ts>=to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)fiveflow,
        NVL(sum(case when ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss') then 1 else 0 end),0)dayflow,
        'all' name
        from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
        and a.video_id=#{videoid}
        union  all
        select
        NVL(sum(case when ts>=to_char( (SYSDATE-1-5/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char (SYSDATE-1,'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)tbfiveflow,
        NVL(sum(case when ts>=to_char( (SYSDATE-10/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)hbfiveflow,
        NVL(sum(case when ts>=to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)fiveflow,
        NVL( sum(case when ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss') then 1 else 0 end),0)dayflow,
        'up' name
        from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
        and a.video_id=#{videoid}   and ruletag ='1'
        union  all
        select
         NVL(sum(case when ts>=to_char( (SYSDATE-1-5/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char (SYSDATE-1,'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)tbfiveflow,
         NVL(sum(case when ts>=to_char( (SYSDATE-10/1440),'yyyy-MM-dd HH24:mi:ss') and ts<![CDATA[ <=  ]]>to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)hbfiveflow,
         NVL(sum(case when ts>=to_char( (SYSDATE-5/1440),'yyyy-MM-dd HH24:mi:ss') then 1 else 0 end),0)fiveflow,
         NVL(sum(case when ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss') then 1 else 0 end),0)dayflow,
         'down' name
        from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
        and a.video_id=#{videoid} and  ruletag ='2'

    </select>

    <select id="selectFiveAndTypeDayFlow" resultType="java.util.HashMap">
         select 'all' name, NVL(sum(case when b.type='SEDAN' then 1 else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then 1 else 0 end),0)goodsvan,
            NVL(sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then 1 else 0 end),0)coach,
            NVL( 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),0)other
            from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
            and a.video_id=#{videoid}
            and ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss')

            union  all

            select 'up' name, NVL(sum(case when b.type='SEDAN' then 1 else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then 1 else 0 end),0)goodsvan,
            NVL(sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then 1 else 0 end),0)coach,
           NVL( 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),0)other
           from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
            and a.video_id=#{videoid}
            and ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss') and  ruletag ='1'

             union  all

            select 'down' name, NVL(sum(case when b.type='SEDAN' then 1 else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then 1 else 0 end),0)goodsvan,
            NVL(sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then 1 else 0 end),0)coach,
           NVL( 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),0)other
           from vehicles a,vehiclesdetail  b where  a.id = b.vehiclesid
            and a.video_id=#{videoid}
            and ts>=To_char(Trunc(SYSDATE), 'yyyy-mm-dd hh24:mi:ss') and  ruletag ='2'


    </select>

</mapper>