<?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.usky.iot.mapper.DmpDeviceStatusMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.usky.iot.domain.DmpDeviceStatus">
        <id column="id" property="id" />
        <result column="device_id" property="deviceId" />
        <result column="product_id" property="productId" />
        <result column="device_status" property="deviceStatus" />
        <result column="last_online_time" property="lastOnlineTime" />
        <result column="last_offline_time" property="lastOfflineTime" />
        <result column="product_code" property="productCode" />
    </resultMap>

    <select id="getAllCollect" resultType="com.usky.iot.service.vo.DmpDataOverviewVO">
        select 0 as tenantId,
        count(*) as deviceCount,
        COUNT(to_days(last_online_time) = to_days(now()) or null) as hyCount,
        count(service_status = 2 or null) as jhCount,
        count(service_status) as zcCount,
        count(device_status = 1 or null) as dzcCount,
        count(device_status = 2 or null) as dlxCount
        from dmp_device ddi
        left join dmp_device_status dds
        on ddi.device_id  = dds.device_id and ddi.product_code = dds.product_code
        <where>
            <if test="startTime != null and endTime != null">
                and  ddi.created_time between #{startTime}
                and #{endTime}
            </if>
            <if test="1 == 1">
                and ddi.delete_flag = 0
            </if>
        </where>
    </select>
    <select id="getMonthHyCollect" resultType="com.usky.iot.service.vo.DmpDataOverviewVO">
        select ddi.tenant_id as tenantId,
        count(*) as hyCount
        from dmp_device ddi
        left join dmp_device_status dds
        on ddi.device_id  = dds.device_id and ddi.product_code = dds.product_code
        <where>
            <if test="startTime != null and endTime != null">
                and  dds.last_online_time between #{startTime}
                and #{endTime}
            </if>
            <if test="1 == 1">
                and ddi.delete_flag = 0
            </if>
        </where>
        group by tenantId
    </select>
    <select id="getAllMonthHyCollect" resultType="com.usky.iot.service.vo.DmpDataOverviewVO">
        select 0 as tenantId,
        count(*) as hyCount
        from dmp_device ddi
        left join dmp_device_status dds
        on ddi.device_id  = dds.device_id and ddi.product_code = dds.product_code
        <where>
            <if test="startTime != null and endTime != null">
                and  dds.last_online_time between #{startTime}
                and #{endTime}
            </if>
            <if test="1 == 1">
                and ddi.delete_flag = 0
            </if>
        </where>
    </select>
    <select id="getDeviceStatusCollect" resultType="com.usky.iot.service.vo.DmpDataOverviewVO">
        select ddi.tenant_id as tenantId,
        count(*) as deviceCount,
        COUNT(to_days(last_online_time) = to_days(now()) or null) as hyCount,
        count(service_status = 2 or null) as jhCount,
        count(service_status) as zcCount,
        count(device_status = 1 or null) as dzcCount,
        count(device_status = 2 or null) as dlxCount
        from dmp_device ddi
        left join dmp_device_status dds
        on ddi.device_id  = dds.device_id and ddi.product_code = dds.product_code
        <where>
            <if test="startTime != null and endTime != null">
                and  ddi.created_time between #{startTime}
                and #{endTime}
            </if>
            <if test="1 == 1">
                and ddi.delete_flag = 0
            </if>
        </where>
        group by tenantId
    </select>

</mapper>
