<?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.TraffAlarmRecordMapper">
    <update id="updateTraffAlarmRecordUrl" parameterType="com.hzjt.domain.Traffalarmrecord">
        UPDATE traffalarmrecord
        set retrytime= sysdate,
        retrycount= retrycount+1
        <trim prefix="," suffixOverrides=",">
            <if test="img1path!=null">img1path=#{img1path},</if>
            <if test="img2path!=null">img2path=#{img2path},</if>
            <if test="img3path!=null">img3path=#{img3path},</if>
            <if test="img4path!=null">img4path=#{img4path},</if>
            <if test="img5path!=null">img5path=#{img5path},</if>
            <if test="videopath!=null">videopath=#{videopath},</if>
        </trim>
        WHERE recordid=#{recordid}
    </update>

    <update id="updatePushEvent" parameterType="com.hzjt.domain.Traffalarmrecord">
        UPDATE traffalarmrecord
        set PUSHSTATUS = #{pushstatus},
            PUSHDESC = #{pushdesc},
            PUSHCOUNT= CASE WHEN PUSHCOUNT IS NULL THEN 1 ELSE PUSHCOUNT + 1 END,
            PUSHDATE= sysdate
        WHERE RECORDID = #{recordid}
    </update>

    <insert id="inserTraffAlarmRecord">
        insert into traffalarmrecord
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="recordid != null">recordid,</if>
            <if test="algotype != null">algotype,</if>
            <if test="areaid != null">areaid,</if>
            <if test="fdid != null">fdid,</if>
            <if test="channelid != null">channelid,</if>
            <if test="channeletype != null">channeletype,</if>
            <if test="channelname != null">channelname ,</if>
            <if test="recordtime != null">recordtime,</if>
            <if test="recordtype != null">recordtype,</if>
            <if test="location != null">location,</if>
            <if test="img1urlfrom != null">img1urlfrom,</if>
            <if test="img1path != null">img1path,</if>
            <if test="img2urlfrom != null">img2urlfrom,</if>
            <if test="img2path != null">img2path,</if>
            <if test="img3urlfrom != null">img3urlfrom,</if>
            <if test="img3path != null">img3path,</if>
            <if test="img4urlfrom != null">img4urlfrom,</if>
            <if test="img4path != null">img4path,</if>
            <if test="img5urlfrom != null">img5urlfrom,</if>
            <if test="img5path != null">img5path,</if>
            <if test="videourlfrom != null">videourlfrom,</if>
            <if test="videopath != null">videopath,</if>
            <if test="retrytime != null">retrytime,</if>
            <if test="retrycount != null">retrycount,</if>
            <if test="recordlevel != null">recordlevel,</if>
            <if test="checkstatus != null">checkstatus,</if>
            <if test="creator != null">creator,</if>
            <if test="createtime != null">createtime,</if>
            <if test="updator != null">updator,</if>
            <if test="updatetime != null">updatetime,</if>
            <if test="remark != null">remark,</if>
            <if test="pushstatus != null">pushstatus,</if>
            <if test="pushdesc != null">pushdesc,</if>
            <if test="processstatus != null">processstatus,</if>
            <if test="objlable != null">objlable,</if>
            <if test="rectificationtype  != null">rectificationtype ,</if>
        </trim>
        <trim prefix="select" suffix="from  dual" suffixOverrides=",">

            <if test="recordid != null">#{recordid},</if>
            <if test="algotype != null">#{algotype},</if>
            <if test="areaid != null">#{areaid},</if>
            <if test="fdid != null">#{fdid},</if>
            <if test="channelid != null">#{ channelid},</if>
            <if test="channeletype != null">#{channeletype},</if>
            <if test="channelname != null">#{ channelname },</if>
            <if test="recordtime != null">#{ recordtime},</if>
            <if test="recordtype != null">#{ recordtype},</if>
            <if test="location != null">#{ location},</if>
            <if test="img1urlfrom != null">#{img1urlfrom},</if>
            <if test="img1path != null">#{ img1path},</if>
            <if test="img2urlfrom != null">#{img2urlfrom},</if>
            <if test="img2path != null">#{ img2path},</if>
            <if test="img3urlfrom != null">#{img3urlfrom},</if>
            <if test="img3path != null">#{ img3path},</if>
            <if test="img4urlfrom != null">#{img4urlfrom},</if>
            <if test="img4path != null">#{ img4path},</if>
            <if test="img5urlfrom != null">#{img5urlfrom},</if>
            <if test="img5path != null">#{img5path},</if>
            <if test="videourlfrom != null">#{videourlfrom},</if>
            <if test="videopath != null">#{videopath},</if>
            <if test="retrytime != null">#{retrytime},</if>
            <if test="retrycount != null">#{retrycount},</if>
            <if test="recordlevel != null">#{recordlevel,jdbcType=INTEGER},</if>
            <if test="checkstatus != null">#{checkstatus,jdbcType=INTEGER},</if>
            <if test="creator != null">#{creator},</if>
            <if test="createtime != null">#{createtime},</if>
            <if test="updator != null">#{updator},</if>
            <if test="updatetime != null">#{updatetime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="pushstatus != null">#{pushstatus},</if>
            <if test="pushdesc != null">#{pushdesc},</if>
            <if test="processstatus != null">#{processstatus},</if>
            <if test="objlable != null">#{objlable},</if>
            <if test="rectificationtype != null">#{rectificationtype},</if>

        </trim>

    </insert>

    <select id="selectmax" resultType="java.lang.Integer">

        select nvl(max(recordid),0)+1 from   traffalarmrecord

    </select>

    <insert id="statisTraffalarmrecordstatByDay">
      insert into TRAFFALARMRECORDSTATE
        SELECT  A.FDID,CHANNELID,  to_char(A.RECORDTIME,'yyyy-MM-DD')countdate,count(*) totalcount,recordtype eventtype,areaid,
        (select B.TDMC from SBTDSPSR B where  A.FDID=B.SBBH and CHANNELID=B.TDBH and rownum=1)tdmc,
        (select  C.NAME from T_CODE C where C.KEY=UPPER(RECORDTYPE)and rownum=1 ) eventtypename,
        (SELECT DEPT_NAME FROM Dept d where  d.dept_id=a.areaid and rownum=1)areaname,
            sum( case when pushstatus is null or  pushstatus=9 then 1 else 0 end)nopush,
            sum( case when pushstatus =0 then 1 else 0 end)successpush,
            sum( case when pushstatus =1 then 1 else 0 end)failpush
        FROM TRAFFALARMRECORD A
        where  A.RECORDTIME>=TRUNC(SYSDATE-1)
        and   A.RECORDTIME<![CDATA[ <  ]]>TRUNC(SYSDATE)
        group  by to_char(A.RECORDTIME,'yyyy-MM-DD'),recordtype,A.FDID,CHANNELID,areaid
    </insert>
    <update id="updateTraffAlarmRectificationType" parameterType="com.hzjt.domain.Traffalarmrecord">
        UPDATE TRAFFALARMRECORD
        <set>
            <if test="processstatus != null">
                rectificationtype=1,
            </if>
        </set>
        from TRAFFALARMRECORD a,
        (select * from TRAFFALARMRECORD a,(select * from TRAFFALARMRECORD b where
        recordid=#{recordid,jdbcType=VARCHAR} )b
        where a.fdid =b.fdid and a.channelid=b.channelid
        AND ( a.recordtime<![CDATA[ <=  ]]> b.recordtime-10/1440)
        AND recordtype in ('roadworks_out_of_line','roadworks'))b
        where recordtype in('illegal_parking','no_motor_ban') and a.fdid =b.fdid and a.channelid=b.channelid

    </update>

    <!--<select id="selectExitsTraffAlarm" parameterType="com.hzjt.domain.Traffalarmrecord" resultType="java.lang.Integer">-->
    <!--select  count(1) from TRAFFALARMRECORD a,t_code b  where a.recordtype=#{recordtype,jdbcType=VARCHAR}-->
    <!--and a.recordtype=LOWER (b.key)and recordtime>=sysdate-(b.REPEATTIME)/(24*60)-->
    <!--and a.fdid=#{fdid,jdbcType=VARCHAR}-->
    <!--and a.channelid=#{channelid,jdbcType=VARCHAR}-->

    <!--</select>-->


    <select id="selectInChecktimeTraffAlarm" parameterType="com.hzjt.domain.Traffalarmrecord"   resultType="java.lang.Integer">
     select  count(1)   from videochecktime  a,t_code  b  where
       a.FDID=#{fdid,jdbcType=VARCHAR}
       and a.CHANNELID=#{channelid,jdbcType=VARCHAR}
       and a.RECORDTYPE=#{recordtype,jdbcType=VARCHAR}
       and  a.RECORDTYPE=lower(b.key)
       and  LASTCHECKTIME+(b.PUSHCHECKTIME)/1440 <![CDATA[ >= ]]>#{recordtime,jdbcType=TIMESTAMP}
       and  LASTCHECKTIME <![CDATA[ < ]]>#{recordtime,jdbcType=TIMESTAMP}

    </select>

    <update id="updateInworkRectifytimeTraffAlarm" parameterType="com.hzjt.domain.Traffalarmrecord">
   update  TRAFFALARMRECORD set  rectificationtype=1,recordtype='roadworks'
    where fdid=#{fdid,jdbcType=VARCHAR}
    and channelid=#{channelid,jdbcType=VARCHAR}
    and recordtime>=#{recordtime,jdbcType=TIMESTAMP}-(select  b.RECTIFYTIME from t_code b where  recordtype=LOWER (b.key) )/1440
    and recordtime<![CDATA[ < ]]>#{recordtime,jdbcType=TIMESTAMP}
    and recordtype in ('illegal_parking','no_motor_ban')
    </update>


</mapper>