<?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.xzzn.ems.mapper.EmsBatteryClusterMapper">

    <resultMap type="EmsBatteryCluster" id="EmsBatteryClusterResult">
        <result property="id"    column="id"    />
        <result property="dataUpdateTime"    column="data_update_time"    />
        <result property="workStatus"    column="work_status"    />
        <result property="pcsCommunicationStatus"    column="pcs_communication_status"    />
        <result property="emsCommunicationStatus"    column="ems_communication_status"    />
        <result property="clusterVoltage"    column="cluster_voltage"    />
        <result property="chargeableCapacity"    column="chargeable_capacity"    />
        <result property="totalChargedCapacity"    column="total_charged_capacity"    />
        <result property="clusterCurrent"    column="cluster_current"    />
        <result property="dischargeableCapacity"    column="dischargeable_capacity"    />
        <result property="totalDischargedCapacity"    column="total_discharged_capacity"    />
        <result property="soh"    column="soh"    />
        <result property="averageTemperature"    column="average_temperature"    />
        <result property="insulationResistance"    column="insulation_resistance"    />
        <result property="currentSoc"    column="current_soc"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="siteId"    column="site_id"    />
        <result property="deviceId"    column="device_id"    />
        <result property="stackDeviceId"    column="stack_device_id"    />
        <result property="maxAllowedChargePower"    column="max_allowed_charge_power"    />
        <result property="maxAllowedDischargePower"    column="max_allowed_discharge_power"    />
        <result property="maxAllowedChargeVoltage"    column="max_allowed_charge_voltage"    />
        <result property="maxAllowedDischargeVoltage"    column="max_allowed_discharge_voltage"    />
        <result property="maxAllowedChargeCurrent"    column="max_allowed_charge_current"    />
        <result property="maxAllowedDischargeCurrent"    column="max_allowed_discharge_current"    />
        <result property="batteryPackVoltage"    column="battery_pack_voltage"    />
        <result property="batteryPackCurrent"    column="battery_pack_current"    />
        <result property="batteryPackTemp"    column="battery_pack_temp"    />
        <result property="batteryPackSoc"    column="battery_pack_soc"    />
        <result property="batteryPackSoh"    column="battery_pack_soh"    />
        <result property="batteryPackInsulationResistance"    column="battery_pack_insulation_resistance"    />
        <result property="avgCellVoltage"    column="avg_cell_voltage"    />
        <result property="avgCellTemp"    column="avg_cell_temp"    />
        <result property="maxCellVoltage"    column="max_cell_voltage"    />
        <result property="maxCellVoltageId"    column="max_cell_voltage_id"    />
        <result property="minCellVoltage"    column="min_cell_voltage"    />
        <result property="minCellVoltageId"    column="min_cell_voltage_id"    />
        <result property="maxCellTemp"    column="max_cell_temp"    />
        <result property="maxCellTempId"    column="max_cell_temp_id"    />
        <result property="minCellTemp"    column="min_cell_temp"    />
        <result property="minCellTempId"    column="min_cell_temp_id"    />
        <result property="maxCellSoc"    column="max_cell_soc"    />
        <result property="maxCellSocId"    column="max_cell_soc_id"    />
        <result property="minCellSoc"    column="min_cell_soc"    />
        <result property="minCellSocId"    column="min_cell_soc_id"    />
        <result property="maxCellSoh"    column="max_cell_soh"    />
        <result property="maxCellSohId"    column="max_cell_soh_id"    />
        <result property="minCellSoh"    column="min_cell_soh"    />
        <result property="minCellSohId"    column="min_cell_soh_id"    />
        <result property="totalChargeEnergy"    column="total_charge_energy"    />
        <result property="totalDischargeEnergy"    column="total_discharge_energy"    />
    </resultMap>

    <sql id="selectEmsBatteryClusterVo">
        select id, work_status, pcs_communication_status, ems_communication_status, cluster_voltage, chargeable_capacity, total_charged_capacity, cluster_current, dischargeable_capacity, total_discharged_capacity, soh, average_temperature, insulation_resistance, current_soc, create_by, create_time, update_by, update_time, remark, site_id, device_id, stack_device_id, max_allowed_charge_power, max_allowed_discharge_power, max_allowed_charge_voltage, max_allowed_discharge_voltage, max_allowed_charge_current, max_allowed_discharge_current, battery_pack_voltage, battery_pack_current, battery_pack_temp, battery_pack_soc, battery_pack_soh, battery_pack_insulation_resistance, avg_cell_voltage, avg_cell_temp, max_cell_voltage, max_cell_voltage_id, min_cell_voltage, min_cell_voltage_id, max_cell_temp, max_cell_temp_id, min_cell_temp, min_cell_temp_id, max_cell_soc, max_cell_soc_id, min_cell_soc, min_cell_soc_id, max_cell_soh, max_cell_soh_id, min_cell_soh, min_cell_soh_id, total_charge_energy, total_discharge_energy from ems_battery_cluster
    </sql>

    <select id="selectEmsBatteryClusterList" parameterType="EmsBatteryCluster" resultMap="EmsBatteryClusterResult">
        <include refid="selectEmsBatteryClusterVo"/>
        <where>
            <if test="dataUpdateTime != null "> and data_update_time = #{dataUpdateTime}</if>
            <if test="workStatus != null  and workStatus != ''"> and work_status = #{workStatus}</if>
            <if test="pcsCommunicationStatus != null  and pcsCommunicationStatus != ''"> and pcs_communication_status = #{pcsCommunicationStatus}</if>
            <if test="emsCommunicationStatus != null  and emsCommunicationStatus != ''"> and ems_communication_status = #{emsCommunicationStatus}</if>
            <if test="clusterVoltage != null "> and cluster_voltage = #{clusterVoltage}</if>
            <if test="chargeableCapacity != null "> and chargeable_capacity = #{chargeableCapacity}</if>
            <if test="totalChargedCapacity != null "> and total_charged_capacity = #{totalChargedCapacity}</if>
            <if test="clusterCurrent != null "> and cluster_current = #{clusterCurrent}</if>
            <if test="dischargeableCapacity != null "> and dischargeable_capacity = #{dischargeableCapacity}</if>
            <if test="totalDischargedCapacity != null "> and total_discharged_capacity = #{totalDischargedCapacity}</if>
            <if test="soh != null "> and soh = #{soh}</if>
            <if test="averageTemperature != null "> and average_temperature = #{averageTemperature}</if>
            <if test="insulationResistance != null "> and insulation_resistance = #{insulationResistance}</if>
            <if test="currentSoc != null "> and current_soc = #{currentSoc}</if>
            <if test="siteId != null  and siteId != ''"> and site_id = #{siteId}</if>
            <if test="deviceId != null  and deviceId != ''"> and device_id = #{deviceId}</if>
            <if test="stackDeviceId != null  and stackDeviceId != ''"> and stack_device_id = #{stackDeviceId}</if>
            <if test="maxAllowedChargePower != null "> and max_allowed_charge_power = #{maxAllowedChargePower}</if>
            <if test="maxAllowedDischargePower != null "> and max_allowed_discharge_power = #{maxAllowedDischargePower}</if>
            <if test="maxAllowedChargeVoltage != null "> and max_allowed_charge_voltage = #{maxAllowedChargeVoltage}</if>
            <if test="maxAllowedDischargeVoltage != null "> and max_allowed_discharge_voltage = #{maxAllowedDischargeVoltage}</if>
            <if test="maxAllowedChargeCurrent != null "> and max_allowed_charge_current = #{maxAllowedChargeCurrent}</if>
            <if test="maxAllowedDischargeCurrent != null "> and max_allowed_discharge_current = #{maxAllowedDischargeCurrent}</if>
            <if test="batteryPackVoltage != null "> and battery_pack_voltage = #{batteryPackVoltage}</if>
            <if test="batteryPackCurrent != null "> and battery_pack_current = #{batteryPackCurrent}</if>
            <if test="batteryPackTemp != null "> and battery_pack_temp = #{batteryPackTemp}</if>
            <if test="batteryPackSoc != null "> and battery_pack_soc = #{batteryPackSoc}</if>
            <if test="batteryPackSoh != null "> and battery_pack_soh = #{batteryPackSoh}</if>
            <if test="batteryPackInsulationResistance != null "> and battery_pack_insulation_resistance = #{batteryPackInsulationResistance}</if>
            <if test="avgCellVoltage != null "> and avg_cell_voltage = #{avgCellVoltage}</if>
            <if test="avgCellTemp != null "> and avg_cell_temp = #{avgCellTemp}</if>
            <if test="maxCellVoltage != null "> and max_cell_voltage = #{maxCellVoltage}</if>
            <if test="maxCellVoltageId != null "> and max_cell_voltage_id = #{maxCellVoltageId}</if>
            <if test="minCellVoltage != null "> and min_cell_voltage = #{minCellVoltage}</if>
            <if test="minCellVoltageId != null "> and min_cell_voltage_id = #{minCellVoltageId}</if>
            <if test="maxCellTemp != null "> and max_cell_temp = #{maxCellTemp}</if>
            <if test="maxCellTempId != null "> and max_cell_temp_id = #{maxCellTempId}</if>
            <if test="minCellTemp != null "> and min_cell_temp = #{minCellTemp}</if>
            <if test="minCellTempId != null "> and min_cell_temp_id = #{minCellTempId}</if>
            <if test="maxCellSoc != null "> and max_cell_soc = #{maxCellSoc}</if>
            <if test="maxCellSocId != null "> and max_cell_soc_id = #{maxCellSocId}</if>
            <if test="minCellSoc != null "> and min_cell_soc = #{minCellSoc}</if>
            <if test="minCellSocId != null "> and min_cell_soc_id = #{minCellSocId}</if>
            <if test="maxCellSoh != null "> and max_cell_soh = #{maxCellSoh}</if>
            <if test="maxCellSohId != null "> and max_cell_soh_id = #{maxCellSohId}</if>
            <if test="minCellSoh != null "> and min_cell_soh = #{minCellSoh}</if>
            <if test="minCellSohId != null "> and min_cell_soh_id = #{minCellSohId}</if>
            <if test="totalChargeEnergy != null "> and total_charge_energy = #{totalChargeEnergy}</if>
            <if test="totalDischargeEnergy != null "> and total_discharge_energy = #{totalDischargeEnergy}</if>
        </where>
    </select>

    <select id="selectEmsBatteryClusterById" parameterType="Long" resultMap="EmsBatteryClusterResult">
        <include refid="selectEmsBatteryClusterVo"/>
        where id = #{id}
    </select>

    <insert id="insertEmsBatteryCluster" parameterType="EmsBatteryCluster" useGeneratedKeys="true" keyProperty="id">
        insert into ems_battery_cluster
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="dataUpdateTime != null">data_update_time,</if>
            <if test="workStatus != null">work_status,</if>
            <if test="pcsCommunicationStatus != null">pcs_communication_status,</if>
            <if test="emsCommunicationStatus != null">ems_communication_status,</if>
            <if test="clusterVoltage != null">cluster_voltage,</if>
            <if test="chargeableCapacity != null">chargeable_capacity,</if>
            <if test="totalChargedCapacity != null">total_charged_capacity,</if>
            <if test="clusterCurrent != null">cluster_current,</if>
            <if test="dischargeableCapacity != null">dischargeable_capacity,</if>
            <if test="totalDischargedCapacity != null">total_discharged_capacity,</if>
            <if test="soh != null">soh,</if>
            <if test="averageTemperature != null">average_temperature,</if>
            <if test="insulationResistance != null">insulation_resistance,</if>
            <if test="currentSoc != null">current_soc,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="siteId != null">site_id,</if>
            <if test="deviceId != null and deviceId != ''">device_id,</if>
            <if test="stackDeviceId != null and stackDeviceId != ''">stack_device_id,</if>
            <if test="maxAllowedChargePower != null">max_allowed_charge_power,</if>
            <if test="maxAllowedDischargePower != null">max_allowed_discharge_power,</if>
            <if test="maxAllowedChargeVoltage != null">max_allowed_charge_voltage,</if>
            <if test="maxAllowedDischargeVoltage != null">max_allowed_discharge_voltage,</if>
            <if test="maxAllowedChargeCurrent != null">max_allowed_charge_current,</if>
            <if test="maxAllowedDischargeCurrent != null">max_allowed_discharge_current,</if>
            <if test="batteryPackVoltage != null">battery_pack_voltage,</if>
            <if test="batteryPackCurrent != null">battery_pack_current,</if>
            <if test="batteryPackTemp != null">battery_pack_temp,</if>
            <if test="batteryPackSoc != null">battery_pack_soc,</if>
            <if test="batteryPackSoh != null">battery_pack_soh,</if>
            <if test="batteryPackInsulationResistance != null">battery_pack_insulation_resistance,</if>
            <if test="avgCellVoltage != null">avg_cell_voltage,</if>
            <if test="avgCellTemp != null">avg_cell_temp,</if>
            <if test="maxCellVoltage != null">max_cell_voltage,</if>
            <if test="maxCellVoltageId != null">max_cell_voltage_id,</if>
            <if test="minCellVoltage != null">min_cell_voltage,</if>
            <if test="minCellVoltageId != null">min_cell_voltage_id,</if>
            <if test="maxCellTemp != null">max_cell_temp,</if>
            <if test="maxCellTempId != null">max_cell_temp_id,</if>
            <if test="minCellTemp != null">min_cell_temp,</if>
            <if test="minCellTempId != null">min_cell_temp_id,</if>
            <if test="maxCellSoc != null">max_cell_soc,</if>
            <if test="maxCellSocId != null">max_cell_soc_id,</if>
            <if test="minCellSoc != null">min_cell_soc,</if>
            <if test="minCellSocId != null">min_cell_soc_id,</if>
            <if test="maxCellSoh != null">max_cell_soh,</if>
            <if test="maxCellSohId != null">max_cell_soh_id,</if>
            <if test="minCellSoh != null">min_cell_soh,</if>
            <if test="minCellSohId != null">min_cell_soh_id,</if>
            <if test="totalChargeEnergy != null">total_charge_energy,</if>
            <if test="totalDischargeEnergy != null">total_discharge_energy,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="dataUpdateTime != null">#{dataUpdateTime},</if>
            <if test="workStatus != null">#{workStatus},</if>
            <if test="pcsCommunicationStatus != null">#{pcsCommunicationStatus},</if>
            <if test="emsCommunicationStatus != null">#{emsCommunicationStatus},</if>
            <if test="clusterVoltage != null">#{clusterVoltage},</if>
            <if test="chargeableCapacity != null">#{chargeableCapacity},</if>
            <if test="totalChargedCapacity != null">#{totalChargedCapacity},</if>
            <if test="clusterCurrent != null">#{clusterCurrent},</if>
            <if test="dischargeableCapacity != null">#{dischargeableCapacity},</if>
            <if test="totalDischargedCapacity != null">#{totalDischargedCapacity},</if>
            <if test="soh != null">#{soh},</if>
            <if test="averageTemperature != null">#{averageTemperature},</if>
            <if test="insulationResistance != null">#{insulationResistance},</if>
            <if test="currentSoc != null">#{currentSoc},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="siteId != null">#{siteId},</if>
            <if test="deviceId != null and deviceId != ''">#{deviceId},</if>
            <if test="stackDeviceId != null and stackDeviceId != ''">#{stackDeviceId},</if>
            <if test="maxAllowedChargePower != null">#{maxAllowedChargePower},</if>
            <if test="maxAllowedDischargePower != null">#{maxAllowedDischargePower},</if>
            <if test="maxAllowedChargeVoltage != null">#{maxAllowedChargeVoltage},</if>
            <if test="maxAllowedDischargeVoltage != null">#{maxAllowedDischargeVoltage},</if>
            <if test="maxAllowedChargeCurrent != null">#{maxAllowedChargeCurrent},</if>
            <if test="maxAllowedDischargeCurrent != null">#{maxAllowedDischargeCurrent},</if>
            <if test="batteryPackVoltage != null">#{batteryPackVoltage},</if>
            <if test="batteryPackCurrent != null">#{batteryPackCurrent},</if>
            <if test="batteryPackTemp != null">#{batteryPackTemp},</if>
            <if test="batteryPackSoc != null">#{batteryPackSoc},</if>
            <if test="batteryPackSoh != null">#{batteryPackSoh},</if>
            <if test="batteryPackInsulationResistance != null">#{batteryPackInsulationResistance},</if>
            <if test="avgCellVoltage != null">#{avgCellVoltage},</if>
            <if test="avgCellTemp != null">#{avgCellTemp},</if>
            <if test="maxCellVoltage != null">#{maxCellVoltage},</if>
            <if test="maxCellVoltageId != null">#{maxCellVoltageId},</if>
            <if test="minCellVoltage != null">#{minCellVoltage},</if>
            <if test="minCellVoltageId != null">#{minCellVoltageId},</if>
            <if test="maxCellTemp != null">#{maxCellTemp},</if>
            <if test="maxCellTempId != null">#{maxCellTempId},</if>
            <if test="minCellTemp != null">#{minCellTemp},</if>
            <if test="minCellTempId != null">#{minCellTempId},</if>
            <if test="maxCellSoc != null">#{maxCellSoc},</if>
            <if test="maxCellSocId != null">#{maxCellSocId},</if>
            <if test="minCellSoc != null">#{minCellSoc},</if>
            <if test="minCellSocId != null">#{minCellSocId},</if>
            <if test="maxCellSoh != null">#{maxCellSoh},</if>
            <if test="maxCellSohId != null">#{maxCellSohId},</if>
            <if test="minCellSoh != null">#{minCellSoh},</if>
            <if test="minCellSohId != null">#{minCellSohId},</if>
            <if test="totalChargeEnergy != null">#{totalChargeEnergy},</if>
            <if test="totalDischargeEnergy != null">#{totalDischargeEnergy},</if>
        </trim>
    </insert>

    <update id="updateEmsBatteryCluster" parameterType="EmsBatteryCluster">
        update ems_battery_cluster
        <trim prefix="SET" suffixOverrides=",">
            <if test="dataUpdateTime != null">data_update_time = #{dataUpdateTime},</if>
            <if test="workStatus != null">work_status = #{workStatus},</if>
            <if test="pcsCommunicationStatus != null">pcs_communication_status = #{pcsCommunicationStatus},</if>
            <if test="emsCommunicationStatus != null">ems_communication_status = #{emsCommunicationStatus},</if>
            <if test="clusterVoltage != null">cluster_voltage = #{clusterVoltage},</if>
            <if test="chargeableCapacity != null">chargeable_capacity = #{chargeableCapacity},</if>
            <if test="totalChargedCapacity != null">total_charged_capacity = #{totalChargedCapacity},</if>
            <if test="clusterCurrent != null">cluster_current = #{clusterCurrent},</if>
            <if test="dischargeableCapacity != null">dischargeable_capacity = #{dischargeableCapacity},</if>
            <if test="totalDischargedCapacity != null">total_discharged_capacity = #{totalDischargedCapacity},</if>
            <if test="soh != null">soh = #{soh},</if>
            <if test="averageTemperature != null">average_temperature = #{averageTemperature},</if>
            <if test="insulationResistance != null">insulation_resistance = #{insulationResistance},</if>
            <if test="currentSoc != null">current_soc = #{currentSoc},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="siteId != null">site_id = #{siteId},</if>
            <if test="deviceId != null and deviceId != ''">device_id = #{deviceId},</if>
            <if test="stackDeviceId != null and stackDeviceId != ''">stack_device_id = #{stackDeviceId},</if>
            <if test="maxAllowedChargePower != null">max_allowed_charge_power = #{maxAllowedChargePower},</if>
            <if test="maxAllowedDischargePower != null">max_allowed_discharge_power = #{maxAllowedDischargePower},</if>
            <if test="maxAllowedChargeVoltage != null">max_allowed_charge_voltage = #{maxAllowedChargeVoltage},</if>
            <if test="maxAllowedDischargeVoltage != null">max_allowed_discharge_voltage = #{maxAllowedDischargeVoltage},</if>
            <if test="maxAllowedChargeCurrent != null">max_allowed_charge_current = #{maxAllowedChargeCurrent},</if>
            <if test="maxAllowedDischargeCurrent != null">max_allowed_discharge_current = #{maxAllowedDischargeCurrent},</if>
            <if test="batteryPackVoltage != null">battery_pack_voltage = #{batteryPackVoltage},</if>
            <if test="batteryPackCurrent != null">battery_pack_current = #{batteryPackCurrent},</if>
            <if test="batteryPackTemp != null">battery_pack_temp = #{batteryPackTemp},</if>
            <if test="batteryPackSoc != null">battery_pack_soc = #{batteryPackSoc},</if>
            <if test="batteryPackSoh != null">battery_pack_soh = #{batteryPackSoh},</if>
            <if test="batteryPackInsulationResistance != null">battery_pack_insulation_resistance = #{batteryPackInsulationResistance},</if>
            <if test="avgCellVoltage != null">avg_cell_voltage = #{avgCellVoltage},</if>
            <if test="avgCellTemp != null">avg_cell_temp = #{avgCellTemp},</if>
            <if test="maxCellVoltage != null">max_cell_voltage = #{maxCellVoltage},</if>
            <if test="maxCellVoltageId != null">max_cell_voltage_id = #{maxCellVoltageId},</if>
            <if test="minCellVoltage != null">min_cell_voltage = #{minCellVoltage},</if>
            <if test="minCellVoltageId != null">min_cell_voltage_id = #{minCellVoltageId},</if>
            <if test="maxCellTemp != null">max_cell_temp = #{maxCellTemp},</if>
            <if test="maxCellTempId != null">max_cell_temp_id = #{maxCellTempId},</if>
            <if test="minCellTemp != null">min_cell_temp = #{minCellTemp},</if>
            <if test="minCellTempId != null">min_cell_temp_id = #{minCellTempId},</if>
            <if test="maxCellSoc != null">max_cell_soc = #{maxCellSoc},</if>
            <if test="maxCellSocId != null">max_cell_soc_id = #{maxCellSocId},</if>
            <if test="minCellSoc != null">min_cell_soc = #{minCellSoc},</if>
            <if test="minCellSocId != null">min_cell_soc_id = #{minCellSocId},</if>
            <if test="maxCellSoh != null">max_cell_soh = #{maxCellSoh},</if>
            <if test="maxCellSohId != null">max_cell_soh_id = #{maxCellSohId},</if>
            <if test="minCellSoh != null">min_cell_soh = #{minCellSoh},</if>
            <if test="minCellSohId != null">min_cell_soh_id = #{minCellSohId},</if>
            <if test="totalChargeEnergy != null">total_charge_energy = #{totalChargeEnergy},</if>
            <if test="totalDischargeEnergy != null">total_discharge_energy = #{totalDischargeEnergy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteEmsBatteryClusterById" parameterType="Long">
        delete from ems_battery_cluster where id = #{id}
    </delete>

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

    <select id="getBmsBatteryData" resultType="com.xzzn.ems.domain.vo.BMSBatteryDataList">
        SELECT t.cluster_voltage as clusterVoltage,t.cluster_current as clusterCurrent,
               t.current_soc as currentSoc,t.site_id as siteId,t.device_id as clusterId,
               t.max_cell_voltage as maxVoltage,t.min_cell_voltage as minVoltage,
               t.max_cell_temp as maxTemperature,t.min_cell_temp as minTemperature
        FROM ems_battery_cluster t
        INNER JOIN (
                SELECT p.site_id,  p.device_id, MAX(p.update_time) AS max_update_time
                FROM ems_battery_cluster p
                WHERE p.site_id = #{siteId} and p.stack_device_id = #{stackDeviceId}
                GROUP BY p.site_id,p.device_id
                ) latest on t.device_id = latest.device_id and t.update_time = latest.max_update_time
        where t.site_id = #{siteId} and t.stack_device_id = #{stackDeviceId}
                 t.max_cell_temp,t.min_cell_temp
    </select>

    <select id="getBatteryAveTempList" resultType="com.xzzn.ems.domain.vo.BatteryAveTempVo">
        WITH ranked AS (
        SELECT
        *,
        date_format(create_time, '%Y-%m-%d %H:00:00') + INTERVAL FLOOR(MINUTE(create_time) / 5) * 5 MINUTE AS group_time,
        ROW_NUMBER() OVER (PARTITION BY date_format(create_time, '%Y-%m-%d %H:00:00'), FLOOR(MINUTE(create_time) / 5) ORDER BY create_time) as rn
        FROM
        ems_battery_cluster
        where site_id = #{siteId}
        and create_time &gt;= #{startDate}
        and create_time &lt; DATE_ADD(#{endDate}, INTERVAL 1 DAY)
        and avg_cell_temp is not null
        )
        SELECT
        site_id,
        round(avg(avg_cell_temp)) as batteryTemp,
        DATE_FORMAT(group_time, '%Y-%m-%d') as dateDay,
        DATE_FORMAT(group_time, '%H:%i:00') as createDate,
        group_time as groupTime
        FROM
        ranked
        GROUP BY site_id,groupTime

    </select>

    <select id="getClusterDataByHour" resultType="com.xzzn.ems.domain.vo.ClusterStatisListVo">
        SELECT  latest.statisDate,
                t.max_cell_temp as maxTemp,
                t.max_cell_temp_id as maxTempId,
                t.min_cell_temp as minTemp,
                t.min_cell_temp_id as minTempId,
                t.max_cell_voltage as maxVoltage,
                t.max_cell_voltage_id as maxVoltageId,
                t.min_cell_voltage as minVoltage,
                t.min_cell_voltage_id as minVoltageId
        FROM ( SELECT p.site_id, p.device_id, DATE_FORMAT( p.update_time, '%H' ) AS statisDate, MAX(p.update_time) AS max_update_time
                FROM ems_battery_cluster p
                WHERE p.site_id = #{siteId}
                AND p.stack_device_id = #{stackId}
                AND p.device_id = #{clusterId}
                AND p.update_time &gt;= #{dateTime}
                AND p.update_time &lt; DATE_ADD(#{dateTime}, INTERVAL 1 DAY)
                GROUP BY p.site_id, p.device_id, statisDate
        ) latest
        INNER JOIN ems_battery_cluster t ON latest.site_id = t.site_id
        AND latest.device_id = t.device_id
        AND latest.max_update_time = t.update_time
        ORDER BY statisDate
    </select>

    <sql id="statisCommonFilter">
        WHERE p.site_id = #{siteId}
        <if test="startDate != null">
            AND p.update_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND p.update_time &lt; #{endDate}
        </if>
    </sql>

    <select id="getAvgTempByHour" parameterType="com.xzzn.ems.domain.vo.DateSearchRequest" resultType="java.util.Map">
        SELECT  latest.statisDate, avg(t.avg_cell_temp) as avgTemp
        FROM ( SELECT p.site_id, p.device_id, DATE_FORMAT( p.update_time, '%Y-%m-%d %H:00') AS statisDate, MAX(p.update_time) AS max_update_time
               FROM ems_battery_cluster p
               <include refid="statisCommonFilter"/>
               GROUP BY p.site_id, p.device_id, statisDate
         ) latest INNER JOIN ems_battery_cluster t ON latest.site_id = t.site_id
        AND latest.device_id = t.device_id
        AND latest.max_update_time = t.update_time
        GROUP BY statisDate
        ORDER BY statisDate
    </select>

    <select id="getAvgTempByDay" parameterType="com.xzzn.ems.domain.vo.DateSearchRequest" resultType="java.util.Map">
        SELECT  latest.statisDate, avg(t.avg_cell_temp) as avgTemp
        FROM ( SELECT p.site_id, p.device_id, DATE( p.update_time) AS statisDate, MAX(p.update_time) AS max_update_time
                FROM ems_battery_cluster p
                <include refid="statisCommonFilter"/>
                GROUP BY p.site_id, p.device_id, statisDate
        ) latest INNER JOIN ems_battery_cluster t ON latest.site_id = t.site_id
        AND latest.device_id = t.device_id
        AND latest.max_update_time = t.update_time
        GROUP BY statisDate
        ORDER BY statisDate
    </select>

    <select id="getAvgTempByMonth" parameterType="com.xzzn.ems.domain.vo.DateSearchRequest" resultType="java.util.Map">
        SELECT  latest.statisDate, avg(t.avg_cell_temp) as avgTemp
        FROM ( SELECT p.site_id, p.device_id, DATE_FORMAT(p.update_time, '%Y-%m') AS statisDate, MAX(p.update_time) AS max_update_time
                FROM ems_battery_cluster p
                <include refid="statisCommonFilter"/>
                GROUP BY p.site_id, p.device_id, statisDate
        ) latest INNER JOIN ems_battery_cluster t ON latest.site_id = t.site_id
        AND latest.device_id = t.device_id
        AND latest.max_update_time = t.update_time
        GROUP BY statisDate
        ORDER BY statisDate
    </select>

    <select id="getAvgTempByMinute" parameterType="com.xzzn.ems.domain.vo.DateSearchRequest" resultType="java.util.Map">
        WITH ranked AS (
        SELECT
        *,
        DATE_FORMAT(DATE_ADD(DATE_FORMAT(p.update_time, '%Y-%m-%d %H:00:00'), INTERVAL CEIL(MINUTE(p.update_time) / 5) * 5 MINUTE)
        , '%Y-%m-%d %H:%i:%s') AS group_time,
        ROW_NUMBER() OVER (PARTITION BY p.device_id, date_format(p.update_time, '%Y-%m-%d %H:00:00'), CEIL(MINUTE(p.update_time) / 5) ORDER BY p.data_update_time DESC) as rn
        FROM
        ems_battery_cluster p
        <include refid="statisCommonFilter"/>
        )
        SELECT
        t.site_id,
        DATE_FORMAT(t.group_time, '%Y-%m-%d %H:%i:00') as statisDate,
        avg(t.avg_cell_temp) as avgTemp
        FROM
        ranked as t
        GROUP BY t.site_id, t.group_time
    </select>
</mapper>