<?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.hw.ems.record.mapper.RecordWaterInstantMapper">

    <resultMap type="RecordWaterInstant" id="RecordWaterInstantResult">
        <result property="objid"    column="objid"    />
        <result property="monitorId"    column="monitor_id"    />
        <result property="monitorName"    column="monitor_name"    />
        <result property="collectTime"    column="collect_time"    />
        <result property="fluxFlow"    column="flux_flow"    />
        <result property="waterFlow"    column="water_flow"    />
        <result property="recordTime"    column="record_time"    />
        <result property="collectType"    column="collect_type"    />
        <result property="deptId"    column="dept_id"    />
        <result property="userId"    column="user_id"    />
        <result property="address"    column="address"    />
        <result property="consumption"    column="consumption"    />
        <result property="onlineState"    column="onlineState"    />
    </resultMap>

    <sql id="selectRecordWaterInstantVo">
        select record_water_instant.objid, record_water_instant.monitor_id,m.monitor_name, collect_time,
               flux_flow, water_flow, record_time, collect_type, record_water_instant.dept_id, record_water_instant.user_id from record_water_instant
        left join base_monitor_info m on m.monitor_id = record_water_instant.monitor_id
    </sql>

    <select id="selectRecordWaterInstantList" parameterType="RecordWaterInstant" resultMap="RecordWaterInstantResult">
        <include refid="selectRecordWaterInstantVo"/>
        <where>
            <if test="monitorId != null  and monitorId != ''"> and record_water_instant.monitor_id = #{monitorId}</if>
            <if test="params.beginCollectTime != null and params.beginCollectTime != '' and params.endCollectTime != null and params.endCollectTime != ''"> and collect_time between #{params.beginCollectTime} and #{params.endCollectTime}</if>
            <if test="monitorSubset != null  and monitorSubset != ''">
                and (record_water_instant.monitor_id = #{monitorSubset} OR record_water_instant.monitor_id IN
                (SELECT t.monitor_id FROM base_monitor_info t WHERE FIND_IN_SET(#{monitorSubset}, ancestors)))
            </if>
            <if test="fluxFlow != null "> and flux_flow = #{fluxFlow}</if>
            <if test="waterFlow != null "> and water_flow = #{waterFlow}</if>
            <if test="recordTime != null "> and record_water_instant.record_time = #{recordTime}</if>
            <if test="collectType != null "> and record_water_instant.collect_type = #{collectType}</if>
            <if test="deptId != null "> and record_water_instant.dept_id = #{deptId}</if>
            <if test="userId != null "> and record_water_instant.user_id = #{userId}</if>
        </where>
        order by record_water_instant.collect_time desc
    </select>

    <select id="selectRecordWaterInstantByObjid" parameterType="Long" resultMap="RecordWaterInstantResult">
        <include refid="selectRecordWaterInstantVo"/>
        where record_water_instant.objid = #{objid}
    </select>
    <select id="instantList" parameterType="RecordWaterInstant" resultMap="RecordWaterInstantResult">
        select d.objid,d.monitor_id, m.monitor_name,monitor_addr address,
        d.collect_time,d.flux_flow, d.water_flow, d.record_time,d.collect_type, pd.consumption consumption,bcdi.online_state onlineState
        from record_water_instant d
        join (
        select rdi.monitor_id, max(rdi.collect_time) collect_time
        from record_water_instant rdi
        <where>
            <if test="monitorId != null  and monitorId != ''"> and rdi.monitor_id = #{monitorId}</if>
            <if test="monitorSubset != null  and monitorSubset != ''">
                and (rdi.monitor_id = #{monitorSubset} OR rdi.monitor_id IN
                (SELECT t.monitor_id FROM base_monitor_info t WHERE FIND_IN_SET(#{monitorSubset}, ancestors)))
            </if>
            <if test="params.monitorIdList != null  and params.monitorIdList.size > 0">
                and rdi.monitor_id IN
                <foreach item="monitorId" collection="params.monitorIdList" open="(" separator="," close=")">
                    #{monitorId}
                </foreach>
            </if>
        </where>
        group by rdi.monitor_id) md on md.monitor_id = d.monitor_id and md.collect_time = d.collect_time
        left join base_monitor_info m on m.monitor_id = d.monitor_id
        left join (select rpd.monitor_id, ifnull(sum(rpd.expend), 0) consumption
        from report_point_dnb rpd
        <if test="params.beginCollectTime != null and params.beginCollectTime != '' and params.endCollectTime != null and params.endCollectTime != ''">
            where rpd.begin_time between #{params.beginCollectTime} and #{params.endCollectTime}
        </if>
        group by rpd.monitor_id) pd on pd.monitor_id = m.monitor_id
        left join base_collect_device_info bcdi on bcdi.collect_device_id = m.collect_device_id
        order by d.monitor_id
    </select>
    <select id="selectRealTimeParentList" parameterType="RecordWaterInstant" resultMap="RecordWaterInstantResult">
        select bmi.monitor_id,bmi.monitor_name,rdi.collect_time,rdi.flux_flow,rdi.water_flow
        from base_monitor_info bmi
        left join (
        SELECT monitor_id, MAX(collect_time) as collectTime FROM record_water_instant group by monitor_id
        ) t1 on bmi.monitor_id = t1.monitor_id
        left join record_water_instant rdi on rdi.monitor_id = t1.monitor_id and rdi.collect_time = t1.collectTime
        <where>
            <if test="monitorId != null  and monitorId != ''"> and bmi.monitor_id = #{monitorId}</if>
            <if test="monitorSubset != null  and monitorSubset != ''">
                and (bmi.monitor_id = #{monitorSubset} OR bmi.monitor_id IN
                (SELECT t.monitor_id FROM base_monitor_info t WHERE FIND_IN_SET(#{monitorSubset}, ancestors)))
            </if>
        </where>
    </select>

    <select id="fluxFlowAlarm" parameterType="RecordWaterInstant" resultMap="RecordWaterInstantResult">
        SELECT wt.monitor_id FROM base_water_threshold wt
        LEFT JOIN record_water_instant wi ON wt.monitor_id = wi.monitor_id
        WHERE (wi.flux_flow > wt.max_traffic OR wt.min_traffic > wi.flux_flow)
        AND wi.collect_time > DATE_SUB(SYSDATE(), INTERVAL 3 MINUTE)
        GROUP BY wt.monitor_id
    </select>

    <insert id="insertRecordWaterInstant" parameterType="RecordWaterInstant">
        insert into record_water_instant
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="objid != null">objid,</if>
            <if test="monitorId != null">monitor_id,</if>
            <if test="collectTime != null">collect_time,</if>
            <if test="fluxFlow != null">flux_flow,</if>
            <if test="waterFlow != null">water_flow,</if>
            <if test="recordTime != null">record_time,</if>
            <if test="collectType != null">collect_type,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="userId != null">user_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="objid != null">#{objid},</if>
            <if test="monitorId != null">#{monitorId},</if>
            <if test="collectTime != null">#{collectTime},</if>
            <if test="fluxFlow != null">#{fluxFlow},</if>
            <if test="waterFlow != null">#{waterFlow},</if>
            <if test="recordTime != null">#{recordTime},</if>
            <if test="collectType != null">#{collectType},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userId != null">#{userId},</if>
        </trim>
    </insert>

    <update id="updateRecordWaterInstant" parameterType="RecordWaterInstant">
        update record_water_instant
        <trim prefix="SET" suffixOverrides=",">
            <if test="monitorId != null">monitor_id = #{monitorId},</if>
            <if test="collectTime != null">collect_time = #{collectTime},</if>
            <if test="fluxFlow != null">flux_flow = #{fluxFlow},</if>
            <if test="waterFlow != null">water_flow = #{waterFlow},</if>
            <if test="recordTime != null">record_time = #{recordTime},</if>
            <if test="collectType != null">collect_type = #{collectType},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="userId != null">user_id = #{userId},</if>
        </trim>
        where objid = #{objid}
    </update>

    <delete id="deleteRecordWaterInstantByObjid" parameterType="Long">
        delete from record_water_instant where objid = #{objid}
    </delete>

    <delete id="deleteRecordWaterInstantByObjids" parameterType="String">
        delete from record_water_instant where objid in
        <foreach item="objid" collection="array" open="(" separator="," close=")">
            #{objid}
        </foreach>
    </delete>

</mapper>
