<?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.bonus.app.mapper.CraneMonitorMapper">
    <!--添加近电感应设备配置属性-->
    <insert id="addJdConfigData">
        INSERT INTO tb_jd_config(id,dev_id,config_type,voltage_level,distance) VALUES
        <foreach collection="configItems" separator="," item="item">
            (
            null,#{devId},#{item.configType},#{item.voltageLevel},#{item.distance}
            )
        </foreach>
    </insert>
    <!--删除近电设备配置项-->
    <delete id="delJdConfig">
        DELETE FROM tb_jd_config WHERE dev_id = #{devId}
    </delete>

    <!--查询班组领用的吊装预警设备-->
    <select id="getDzWarnList" resultType="com.bonus.common.entity.app.vo.DevInfoVo">
        SELECT twt.team_leader AS teamLeader,
               twt.leader_phone AS leaderPhone,
               td.dev_code AS devCode,
               td.id AS devId,
               td.dev_index AS devIndex,
               td.dev_name AS devName
        FROM tb_device td
        LEFT JOIN tb_dev_ly tdl ON td.id = tdl.dev_id
        LEFT JOIN t_work_team twt ON tdl.team_id = twt.team_id AND twt.del_flag = 0
        WHERE tdl.team_id = #{id} AND td.dev_type = #{devType} AND td.del_flag = 0
        ORDER BY tdl.ly_time DESC
    </select>
    <!--查询班组领用的近电感应设备-->
    <select id="getJdList" resultType="com.bonus.common.entity.app.vo.DevInfoVo">
        SELECT td.dev_code AS devCode,
               td.id AS devId,
               td.dev_index AS devIndex,
               td.dev_name AS devName
        FROM tb_device td
        LEFT JOIN tb_dev_ly tdl ON td.id = tdl.dev_id
        WHERE tdl.team_id = #{id} AND td.dev_type = #{devType} AND td.del_flag = 0
    </select>
    <!--查询近电感应设备的电量-->
    <select id="getElectricQuantity" resultType="java.lang.String">
        SELECT CONCAT(data_val,data_unit) FROM tb_dev_details WHERE dev_id = #{devId} AND data_code = #{dataCode} AND dev_type = 1 LIMIT 1
    </select>
    <!--查询近电感应设备预警信息-->
    <select id="getJdWarnList" resultType="com.bonus.common.entity.app.vo.DevWarnVo">
        SELECT td.dev_name AS devName,
               tw.warn_time AS warnTime,
               tw.warn_content AS warnContent
        FROM tb_warn tw
        LEFT JOIN tb_device td ON tw.dev_id = td.id
        WHERE tw.team_id = #{id} AND td.dev_type = #{devType}
        <if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''">
           AND DATE_FORMAT(tw.warn_time, '%Y-%m-%d') BETWEEN #{startTime} AND #{endTime}
        </if>
        AND tw.dev_type = 1 AND tw.del_flag = 0
    </select>
    <!--查询近电感应设备配置属性 交流/直流-->
    <select id="getConfigJdDevice" resultType="com.bonus.common.entity.app.vo.JdConfigVo$ConfigItems">
        SELECT * FROM (
          SELECT tdc.id,
                 tdc.config_type AS configType,
                 sdt.dict_name AS configTypeName,
                 tdc.distance AS distance,
                 tdc.voltage_level AS voltageLevel,
                 sdd.dict_label AS voltageLevelName
          FROM tb_jd_config tdc
          LEFT JOIN sys_dict_type sdt ON tdc.config_type = sdt.dict_type AND sdt.status = '0'
          LEFT JOIN sys_dict_data sdd ON tdc.voltage_level = sdd.dict_value AND sdd.status = '0' AND sdd.dict_type = 'communication'
          WHERE tdc.dev_id = #{devId} AND tdc.config_type = 'communication'
          UNION ALL
          SELECT tdc.id,
                 tdc.config_type AS configType,
                 sdt.dict_name AS configTypeName,
                 tdc.distance AS distance,
                 tdc.voltage_level AS voltageLevel,
                 sdd.dict_label AS voltageLevelName
          FROM tb_jd_config tdc
          LEFT JOIN sys_dict_type sdt ON tdc.config_type = sdt.dict_type AND sdt.status = '0'
          LEFT JOIN sys_dict_data sdd ON tdc.voltage_level = sdd.dict_value AND sdd.status = '0' AND sdd.dict_type = 'direct_current'
          WHERE tdc.dev_id = #{devId} AND  tdc.config_type = 'direct_current'
    ) a
    ORDER BY a.id
    </select>
    <!--获取吊车监控的工程-->
    <select id="getAppDzProList" resultType="java.util.Map">
        SELECT DISTINCT tp.id,tp.pro_name AS name
        FROM tb_project tp
        LEFT JOIN t_work_team twt ON tp.id = twt.pro_id AND twt.del_flag = 0
        WHERE tp.del_flag = 0
        <if test="roleCode=='depart'">
            AND tp.depart_id = #{departId}
        </if>
        <if test="roleCode=='team'">
            AND twt.team_id = #{teamId}
        </if>
    </select>
    <!--获取吊车监控的杆塔-->
    <select id="getAppDzTowerList" resultType="java.util.Map">
        SELECT DISTINCT tpp.id,tpp.power_name AS name
        FROM tb_project_power tpp
        LEFT JOIN tb_dev_ly tdl ON tpp.id = tdl.gt_id
        WHERE tpp.pro_id = #{id} AND tpp.del_flag = 0
        <if test="roleCode=='team'">
            AND tdl.team_id = #{teamId}
        </if>
    </select>
    <!--获取吊车监控的班组-->
    <select id="getAppDzTeamList" resultType="java.util.Map">
        SELECT DISTINCT twt.team_id AS id,twt.team_name AS name
        FROM t_work_team twt
        LEFT JOIN tb_dev_ly tdl ON twt.team_id = tdl.team_id
        WHERE tdl.gt_id = #{id}
        <if test="roleCode=='team'">
            AND tdl.team_id = #{teamId}
        </if>
    </select>
</mapper>