<?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="cn.cecep.talroad.mapper.ElectricityMeterMapper">

    <select id="selectDeviceSiteList"  resultType="cn.cecep.talroad.domain.vo.MonElecRealVO">
        SELECT
            A.*,
            mer.active_power,
            mer.active_power_quantity,
            mer.data_time,
            CASE
               WHEN mer.active_power IS NULL
                   OR A.powerRating IS NULL
                   OR (
                    CAST(COALESCE(NULLIF(mer.active_power, '')::decimal, 0) AS decimal) &lt;
                    (  A.powerRating * 0.01 )
               ) THEN 0 ELSE 1 END isRun
        FROM
            (
            SELECT
                bds.ID AS device_site_id,
                bds.is_stat,
                bds.TYPE,
                bds.NAME,
                bd.device_id AS deviceId,
                bd."name" AS deviceName,
                bd."type" AS deviceType,
                pf.ID AS factoryId,
                pf.factory_name factoryName,
                CAST(COALESCE(NULLIF(bds.normal_value, '')::decimal, 0) AS decimal) AS powerRating
        FROM
                bas_device_site bds
                INNER JOIN bas_device bd ON bd.device_id = bds.device_id
                INNER JOIN pc_factory pf ON pf.ID = bd.factory_id
        WHERE
                bd.type = 'ELEC' AND bd.device_type = #{deviceType}
            ) A
        LEFT JOIN(
            SELECT
                d.active_power,
                d.active_power_quantity,
                d.data_time ,
                d.device_site_id
            FROM
            mon_elec_real d
            INNER JOIN ( SELECT device_site_id, MAX ( data_time ) AS data_time FROM mon_elec_real WHERE data_time &gt;= #{dateTime}  AND data_time &lt;= #{endTime} GROUP BY device_site_id ) b ON d.device_site_id = b.device_site_id
            AND d.data_time = b.data_time WHERE d.data_time &gt;= #{dateTime}  AND d.data_time &lt;= #{endTime}
            ) mer on  A.device_site_id = mer.device_site_id
        WHERE A.is_stat = '1'
    </select>

    <select id="selectChartData" resultType="java.util.Map">
        select
            problem_type_group AS "problemTypeGroup",
            problem_type AS "problemType",
            count(*) AS "count"
        from m_am_problem_fi
        <where>
            <if test="factoryId != null and factoryId != ''">
                AND factory_id = #{factoryId}
            </if>
            <if test="problemType != null and problemType != ''">
                AND problem_type = #{problemType}
            </if>

            <!-- 查询报警时间范围与传入的时间范围有交集的 -->
            <choose>
                <when test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                    AND (
                        (problem_start_time >= #{startTime} AND problem_start_time &lt;= #{endTime})
                        OR
                        (problem_end_time >= #{startTime} AND problem_end_time &lt;= #{endTime})
                    )
                </when>
                <when test="startTime != null and startTime != ''">
                    AND (problem_start_time >= #{startTime} OR problem_end_time >= #{startTime})
                </when>
                <when test="endTime != null and endTime != ''">
                    AND (problem_start_time &lt;= #{endTime} OR problem_end_time &lt;= #{endTime})
                </when>
            </choose>
        </where>
        group by problem_type_group, problem_type
    </select>

    <select id="selectChartDataByIndustry" resultType="java.util.Map">
        select
            problem_type_group AS "problemTypeGroup",
            mapf.problem_type AS "problemType",
            count(mapf.*) AS "count"
        from m_am_problem_fi mapf
        LEFT JOIN pc_factory pf ON pf.id = mapf.factory_id
        WHERE pf.industry_category = #{industry}
        group by mapf.problem_type_group, mapf.problem_type
    </select>

    <update id="updateBatchStatus">
        UPDATE bas_device_site
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when id = #{item.deviceSiteId,jdbcType=VARCHAR} then
                    <choose>
                        <when test="item.isRun"> '正常' </when>
                        <otherwise> '停运' </otherwise>
                    </choose>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.deviceSiteId,jdbcType=VARCHAR}
        </foreach>
    </update>
</mapper>
