<?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.securitycontrol.screen.mapper.ConstructionQualityMapper">

    <!--施工质量-检测记录对比-->
    <select id="getByMonth" resultType="com.securitycontrol.entity.screen.vo.ConstructionQualityVo">
        SELECT
            COUNT(*) as detectionNumber,
            DATE_FORMAT(thdv.create_time,'%Y-%m') as month,
            SUM(CASE WHEN thdv.is_warn = 1 THEN 1 ELSE 0 END) as qualificationNumber
        FROM tb_his_device_value thdv
                 JOIN tb_device_detail tdd ON thdv.id = tdd.id
                 JOIN tb_bd_device tbd ON tdd.device_id = tbd.device_id
                 JOIN tb_pro_bd tpb ON tbd.bd_id = tpb.id
        WHERE tpb.bid_code = #{bidCode} and DATE_FORMAT(thdv.create_time,'%Y-%m') = #{month}
        GROUP BY DATE_FORMAT(thdv.create_time,'%Y-%m')
    </select>
    <!--施工质量-告警内容-->
    <select id="getConstructionAlarm" resultType="com.securitycontrol.entity.screen.vo.ConstructionQualityVo">
        SELECT
#             tw.warn_id AS warningId,
            tw.warn_content AS warningContent
        FROM tb_warn tw
        where tw.bid_code = #{bidCode}
    </select>

    <!--施工质量-质量检测设备-->
    <select id="getQualityInspection" resultType="com.securitycontrol.entity.screen.vo.ConstructionQualityVo">
        SELECT
            tbd.device_id as deviceId,
            tbd.device_name as deviceName,
            CASE
                WHEN tbd.`status` = '1' AND tdv.is_warn = '1' THEN '2'
                WHEN tbd.`status` = '1' AND tdv.is_warn = '0' THEN '1'
                WHEN tbd.`status` = '0' THEN '0'
                END as status
        FROM
            tb_bd_device tbd
                LEFT JOIN tb_pro_bd tpb ON tpb.id = tbd.bd_id
                LEFT JOIN tb_device_detail tdd ON  tdd.device_id = tbd.device_id
                LEFT JOIN tb_device_value tdv ON tdd.id = tdv.attribute_id
        WHERE
            tbd.devic_type = 1806 and  tpb.bid_code = #{bidCode}

    </select>
    <select id="getConstructionQualityList"
            resultType="com.securitycontrol.entity.screen.vo.ConstructionQualityVo">
        SELECT
            thdv.val as val,
            thdv.create_time as createTime,
            thdv.is_warn as isWarn,
            tdd.max_value as maxVal,
            tbd.devic_type as devicType,
            tbd.device_name as deviceName,
            ta.area_name as areaName
            FROM tb_his_device_value thdv
                 JOIN tb_device_detail tdd ON thdv.id = tdd.id
                 JOIN tb_bd_device tbd ON tdd.device_id = tbd.device_id
                 JOIN tb_pro_bd tpb ON tbd.bd_id = tpb.id
                JOIN tb_area ta ON tbd.area_id = ta.area_id
        WHERE
            tpb.bid_code = #{bidCode}
    </select>


</mapper>