<?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.Vehicle" >
            INSERT INTO vehicle (video_id, type,
             direction,classification_confidence,ruletag,create_time)
            values(
            #{video_id,jdbcType=VARCHAR},
            #{type,jdbcType=VARCHAR},
            #{direction,jdbcType=NUMERIC},
            #{classification_confidence,jdbcType=NUMERIC},
            #{ruleTag,jdbcType=VARCHAR},#{create_time,jdbcType=TIMESTAMP})
    </insert>

    <insert id="add" parameterType="com.hzjt.domain.Vehicles">
        insert into vehicles ( id,type,video_id,ts ,create_time)
        values(#{id,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR},
         #{video_id,jdbcType=VARCHAR},#{ts,jdbcType=VARCHAR},sysdate)
    </insert>
    <select id="selectFiveAndDayFlow"  resultType="java.util.HashMap">
        select
        NVL(sum(case when CREATE_TIME>=(SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1) then 1 else 0 end),0)tbfiveflow,
        NVL(sum(case when CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]> (SYSDATE-5/1440) then 1 else 0 end),0)hbfiveflow,
        NVL(sum(case when CREATE_TIME>= (SYSDATE-5/1440) then 1 else 0 end),0)fiveflow,
        NVL(sum(case when CREATE_TIME>=Trunc(SYSDATE) then 1 else 0 end),0)dayflow,
        'all' name
        from vehicle b where b.video_id=#{videoid}
        and ( (CREATE_TIME >=(SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1))
        or(CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]> (SYSDATE-5/1440) )
        or (CREATE_TIME>=Trunc(SYSDATE)))
        union  all
        select
        NVL(sum(case when CREATE_TIME>=(SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1) then 1 else 0 end),0)tbfiveflow,
        NVL(sum(case when CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-5/1440) then 1 else 0 end),0)hbfiveflow,
        NVL(sum(case when CREATE_TIME>= (SYSDATE-5/1440) then 1 else 0 end),0)fiveflow,
        NVL( sum(case when CREATE_TIME>=Trunc(SYSDATE) then 1 else 0 end),0)dayflow,
        'up' name
        from vehicle  b where b.video_id=#{videoid}   and ruletag ='1'
        and  ( (CREATE_TIME >=(SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1))
        or(CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]> (SYSDATE-5/1440) )
        or (CREATE_TIME>=Trunc(SYSDATE)))
        union  all
        select
         NVL(sum(case when CREATE_TIME>= (SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1) then 1 else 0 end),0)tbfiveflow,
         NVL(sum(case when CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]> (SYSDATE-5/1440) then 1 else 0 end),0)hbfiveflow,
         NVL(sum(case when CREATE_TIME>= (SYSDATE-5/1440)  then 1 else 0 end),0)fiveflow,
         NVL(sum(case when CREATE_TIME>=Trunc(SYSDATE) then 1 else 0 end),0)dayflow,
         'down' name
        from vehicle  b where b.video_id=#{videoid} and  ruletag ='2'
           and ( (CREATE_TIME >=(SYSDATE-1-5/1440) and CREATE_TIME<![CDATA[ <=  ]]>(SYSDATE-1))
        or(CREATE_TIME>= (SYSDATE-10/1440) and CREATE_TIME<![CDATA[ <=  ]]> (SYSDATE-5/1440) )
        or (CREATE_TIME>=Trunc(SYSDATE)))


    </select>

    <select id="selectFiveAndTypeDayFlow" resultType="java.util.HashMap">
         select 'all' name, NVL(sum(case when b.type='SEDAN' then total else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total else 0 end),0)goodsvan,
            NVL(sum(case when b.type='COACH'or b.type='MEDIUM_COACH' then total 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 total else 0 end),0)other
            from vehicleTodaystatistic b where  video_id=#{videoid}
            and ts=to_char(SYSDATE,'yyyy-mm-dd')
            union  all

            select 'up' name, NVL(sum(case when b.type='SEDAN' then total else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total 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 total else 0 end),0)other
           from vehicleTodaystatistic  b where  b.video_id=#{videoid}
            and ts=to_char(SYSDATE,'yyyy-mm-dd') and  ruletag ='1'
             union  all

            select 'down' name, NVL(sum(case when b.type='SEDAN' then total else 0 end),0)sedan,
            NVL(sum(case when b.type='HEAVY_GOODS_VAN' or b.type= 'LIGHT_GOODS_VAN' then total 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 total else 0 end),0)other
            from vehicleTodaystatistic  b where  b.video_id=#{videoid}
            and ts=to_char(SYSDATE,'yyyy-mm-dd') and  ruletag ='2'


    </select>

    <insert id="statisVehiclesByDay" >
     insert  into vehiclestatistic
       select video_id ,
           to_char(CREATE_TIME, 'yyyy-mm-dd') ts,
           type,
           ruletag,
           count(*) total
      from vehicle b
     where CREATE_TIME>= TRUNC (SYSDATE-1)
     and CREATE_TIME<![CDATA[ <  ]]>TRUNC(SYSDATE)
     group by to_char(CREATE_TIME, 'yyyy-mm-dd'),
              type,
              ruletag,
              b.video_id
    </insert>
    <delete id="deleteBeforeTwoMonthVehiclesDetails">
    delete  from  vehicle WHERE create_time <![CDATA[ <=  ]]>sysdate-33
    </delete>

    <delete id="deleteVehiclesByDay">
        delete  from  vehicleTodaystatistic
    </delete>


    <insert id="insertOrUpdatevehicleTodaystatistic" >
        MERGE INTO vehicleTodaystatistic t1 USING (
        SELECT
        video_id,
        TO_CHAR (CREATE_TIME, 'yyyy-mm-dd') ts,
        TYPE,nvl(ruletag,0)ruletag,
        COUNT (*) total,
         NVL(sum(case when CREATE_TIME>= (SYSDATE-5/1440) then 1 else 0 end),0)five
        FROM
        vehicle
        WHERE
        (
        create_time >= TRUNC (SYSDATE)
        AND create_time <![CDATA[ <=  ]]> SYSDATE
        )
        OR (
        create_time >= TRUNC (SYSDATE - 7)
        AND create_time <![CDATA[ <=  ]]> SYSDATE - 7
        )
        OR (
        create_time >= TRUNC (ADD_MONTHS(SYSDATE ,- 1))
        AND create_time <![CDATA[ <=  ]]> ADD_MONTHS (SYSDATE ,- 1)
        )
        GROUP BY
        TO_CHAR (CREATE_TIME, 'yyyy-mm-dd'),
        TYPE,ruletag,
        video_id
        ) t2 ON (
        t1.video_id = t2.video_id
        AND t1.ts = t2.ts
        AND t1.TYPE = t2.TYPE
        AND t1.ruletag=t2.ruletag
        )
        WHEN MATCHED THEN
        UPDATE
        SET t1.total = t2.total,
        t1.five = t2.five
        WHEN NOT MATCHED THEN
        INSERT (video_id, ts, TYPE, ruletag,total,five)
        VALUES
        (
        t2.video_id,
        t2.ts,
        t2.TYPE,
        t2.ruletag,
        t2.total,
        t2.five
        )
    </insert>

    <select id="selectSbtddspsrRtspByVideoid" resultType="string">
        select   squrllj  from  sbtdspsr  where  sbbh||'_'||tdbh =#{videoid} and rownum=1

    </select>


</mapper>