<?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.RecordDnbInstantMapper">

    <resultMap type="RecordDnbInstant" id="RecordDnbInstantResult">
        <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="address"    column="address"    />
        <result property="vA"    column="vA"    />
        <result property="vB"    column="vB"    />
        <result property="vC"    column="vC"    />
        <result property="iA"    column="iA"    />
        <result property="iB"    column="iB"    />
        <result property="iC"    column="iC"    />
        <result property="recordTime"    column="record_time"    />
        <result property="glys"    column="glys"    />
        <result property="zxyg"    column="zxyg"    />
        <result property="activePower"    column="active_power"    />
        <result property="reactivePower"    column="reactive_power"    />
        <result property="consumption"    column="consumption"    />
        <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="onlineState"    column="onlineState"    />
    </resultMap>

    <sql id="selectRecordDnbInstantVo">
        select record_dnb_instant.objid, record_dnb_instant.monitor_id,m.monitor_name,m.monitor_addr address,  collect_time, vA, vB, vC, iA, iB, iC, record_time, glys,
               zxyg, active_power, reactive_power, consumption, collect_type, record_dnb_instant.dept_id, record_dnb_instant.user_id from record_dnb_instant
               left join base_monitor_info m on m.monitor_id = record_dnb_instant.monitor_id
    </sql>

    <select id="selectRecordDnbInstantList" parameterType="RecordDnbInstant" resultMap="RecordDnbInstantResult">
        <include refid="selectRecordDnbInstantVo"/>
        <where>
            <if test="monitorId != null  and monitorId != ''"> and record_dnb_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_dnb_instant.monitor_id = #{monitorSubset} OR record_dnb_instant.monitor_id IN
                (SELECT t.monitor_id FROM base_monitor_info t WHERE FIND_IN_SET(#{monitorSubset}, ancestors)))
            </if>
            <if test="vA != null "> and vA = #{vA}</if>
            <if test="vB != null "> and vB = #{vB}</if>
            <if test="vC != null "> and vC = #{vC}</if>
            <if test="iA != null "> and iA = #{iA}</if>
            <if test="iB != null "> and iB = #{iB}</if>
            <if test="iC != null "> and iC = #{iC}</if>
            <if test="glys != null "> and glys = #{glys}</if>
            <if test="zxyg != null "> and zxyg = #{zxyg}</if>
            <if test="activePower != null "> and active_power = #{activePower}</if>
            <if test="reactivePower != null "> and reactive_power = #{reactivePower}</if>
            <if test="consumption != null "> and consumption = #{consumption}</if>
            <if test="collectType != null "> and record_dnb_instant.collect_type = #{collectType}</if>
            <if test="deptId != null "> and record_dnb_instant.dept_id = #{deptId}</if>
            <if test="userId != null "> and record_dnb_instant.user_id = #{userId}</if>
        </where>
        order by record_dnb_instant.collect_time desc
    </select>

    <select id="selectRecordDnbInstantByObjid" parameterType="Long" resultMap="RecordDnbInstantResult">
        <include refid="selectRecordDnbInstantVo"/>
        where record_dnb_instant.objid = #{objid}
    </select>
<!--先在子查询中查询出最新的一条数据并根据monitorid分组-->
<!--    然后连表查询select * from 电实时数据 where id=子查询的id，时间=子查询的时间-->
<!-- 子查询   select monitor_id,MAX(collect_time) from record_dnb_instant GROUP BY monitor_id-->
    <select id="instantList" parameterType="RecordDnbInstant" resultMap="RecordDnbInstantResult">
        select d.monitor_id, m.monitor_name, m.monitor_addr address,
        d.collect_time, d.vA, d.vB, d.vC, d.iA, d.iB, d.iC, d.record_time, d.glys,
        d.zxyg, d.active_power, d.reactive_power,d.collect_type,bcdi.online_state onlineState,
        pd.consumption consumption
        from record_dnb_instant d
        join (
        select rdi.monitor_id, max(rdi.collect_time) collect_time
        from record_dnb_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="RecordDnbInstant" resultMap="RecordDnbInstantResult">
        select bmi.monitor_id,bmi.monitor_name,rdi.collect_time,rdi.vA,
        rdi.vB,rdi.vC,rdi.iA,rdi.iB,rdi.iC,rdi.record_time,
        rdi.glys,rdi.zxyg,rdi.active_power,rdi.reactive_power,rdi.consumption
        from base_monitor_info bmi
        left join (
        SELECT monitor_id, MAX(collect_time) as collectTime FROM record_dnb_instant group by monitor_id
        ) t1 on bmi.monitor_id = t1.monitor_id
        left join record_dnb_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>

    <insert id="insertRecordDnbInstant" parameterType="RecordDnbInstant" useGeneratedKeys="true" keyProperty="objid">
        insert into record_dnb_instant
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="monitorId != null">monitor_id,</if>
            <if test="collectTime != null">collect_time,</if>
            <if test="vA != null">vA,</if>
            <if test="vB != null">vB,</if>
            <if test="vC != null">vC,</if>
            <if test="iA != null">iA,</if>
            <if test="iB != null">iB,</if>
            <if test="iC != null">iC,</if>
            <if test="recordTime != null">record_time,</if>
            <if test="glys != null">glys,</if>
            <if test="zxyg != null">zxyg,</if>
            <if test="activePower != null">active_power,</if>
            <if test="reactivePower != null">reactive_power,</if>
            <if test="consumption != null">consumption,</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="monitorId != null">#{monitorId},</if>
            <if test="collectTime != null">#{collectTime},</if>
            <if test="vA != null">#{vA},</if>
            <if test="vB != null">#{vB},</if>
            <if test="vC != null">#{vC},</if>
            <if test="iA != null">#{iA},</if>
            <if test="iB != null">#{iB},</if>
            <if test="iC != null">#{iC},</if>
            <if test="recordTime != null">#{recordTime},</if>
            <if test="glys != null">#{glys},</if>
            <if test="zxyg != null">#{zxyg},</if>
            <if test="activePower != null">#{activePower},</if>
            <if test="reactivePower != null">#{reactivePower},</if>
            <if test="consumption != null">#{consumption},</if>
            <if test="collectType != null">#{collectType},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userId != null">#{userId},</if>
        </trim>
    </insert>

    <update id="updateRecordDnbInstant" parameterType="RecordDnbInstant">
        update record_dnb_instant
        <trim prefix="SET" suffixOverrides=",">
            <if test="monitorId != null">monitor_id = #{monitorId},</if>
            <if test="collectTime != null">collect_time = #{collectTime},</if>
            <if test="vA != null">vA = #{vA},</if>
            <if test="vB != null">vB = #{vB},</if>
            <if test="vC != null">vC = #{vC},</if>
            <if test="iA != null">iA = #{iA},</if>
            <if test="iB != null">iB = #{iB},</if>
            <if test="iC != null">iC = #{iC},</if>
            <if test="recordTime != null">record_time = #{recordTime},</if>
            <if test="glys != null">glys = #{glys},</if>
            <if test="zxyg != null">zxyg = #{zxyg},</if>
            <if test="activePower != null">active_power = #{activePower},</if>
            <if test="reactivePower != null">reactive_power = #{reactivePower},</if>
            <if test="consumption != null">consumption = #{consumption},</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="deleteRecordDnbInstantByObjid" parameterType="Long">
        delete from record_dnb_instant where objid = #{objid}
    </delete>

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

</mapper>
