<?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.bracelet.mapper.SidebandMapper">
    <!--边带列表-->
    <select id="getSidebandLists" resultType="com.bonus.common.entity.bracelet.vo.SidebandVo">
        select ss.id as sidebandId,ss.sideband_name as sidebandName,ss.sideband_code as sidebandCode,project_name as projectName,
        gt_code as gtCode,bind_time as bindTime,(count(distinct tb.id) + count(distinct tsb.id)) as bindNum,remarks as remarks
        from sys_sideband ss
        left join tb_device tb on ss.id = tb.bind_id and tb.del_flag = 0
        left join tb_sh_box tsb on ss.id = tsb.bind_id and tsb.del_flag = 0
        where ss.del_flag = 0
        <if test="sidebandName != null and sidebandName!=''">
            AND INSTR(ss.sideband_name,#{sidebandName}) > 0
        </if>
        <if test="sidebandCode != null and sidebandCode!=''">
            AND INSTR(ss.sideband_code,#{sidebandCode}) > 0
        </if>
        <if test="projectName != null and projectName!=''">
            AND INSTR(ss.project_name,#{projectName}) > 0
        </if>
        <if test="gtCode != null and gtCode!=''">
            AND INSTR(ss.gt_code,#{gtCode}) > 0
        </if>
        <if test="bindTime != null">
            and date_format(ss.bind_time,'%y%m%d') = date_format(#{bindTime},'%y%m%d')
        </if>
        group by ss.id
        order by ss.id ASC
    </select>

    <!--杆塔绑定设备列表-->
    <select id="getDeviceLists" resultType="com.bonus.common.entity.bracelet.vo.SidebandDeviceVo">
    select * from(
        select ss.id as sidebandId,ss.sideband_name as sidebandName,td.id as deviceId,td.dev_type as
        deviceType,td.dev_name as deviceName,
        td.dev_code as deviceCode,td.dev_status as deviceStatus,tdl.ly_user as lyId,tdl.user_type as lyType,null as
        lyName,tdd.dict_label AS deviceTypeName
        from sys_sideband ss
        left join tb_device td on ss.id=td.bind_id and td.del_flag = 0
        left join sys_dict_data tdd on tdd.dict_value=td.dev_type AND tdd .dict_type='sys_device_type'
        left join tb_dev_ly tdl on td.id = tdl.dev_id
        where ss.id = #{sidebandId} and ss.del_flag = 0 and (td.id is not null or td.id = '')
        union all
        select ss.id as sidebandId,ss.sideband_name as sidebandName,tsb.id as deviceId,'shx' as deviceType,tsb.box_name
        as deviceName,tsb.box_code as deviceCode,0 as deviceStatus,null as lyId,null as lyType, twt.team_leader as lyName,
        tdd.dict_label AS deviceTypeName
        from sys_sideband ss
        left join tb_sh_box tsb on ss.id = tsb.bind_id and tsb.del_flag = 0
        left join sys_dict_data tdd on tdd.dict_value = 'shx' AND tdd .dict_type='sys_device_type'
        left join t_work_team twt on tsb.team_id = twt.team_id and twt.del_flag = 0
        where ss.id = #{sidebandId} and ss.del_flag = 0 and (tsb.id is not null or tsb.id = '')
        )a
        <where>
        <if test="keyword != null and keyword!=''">
            AND (INSTR(a.deviceTypeName,#{keyword}) > 0 or
            INSTR(a.deviceName,#{keyword}) > 0 or
            INSTR(a.deviceCode,#{keyword}) > 0 )
        </if>
        </where>
    </select>

    <!--杆塔绑定设备列表2-->
    <select id="getShLists" resultType="com.bonus.common.entity.bracelet.vo.SidebandDeviceVo">
        select ss.id as sidebandId,ss.sideband_name as sidebandName,tsb.id as deviceId,'shx' as deviceType,tsb.box_name as deviceName,
        tsb.box_code as deviceCode,twt.team_leader as lyName, tdd.dict_label AS deviceTypeName
        from sys_sideband ss
        left join tb_sh_box tsb on ss.id = tsb.bind_id and tsb.del_flag = 0
        left join sys_dict_data tdd on tdd.dict_value = 'shx' AND tdd .dict_type='sys_device_type'
        left join t_work_team twt on tsb.team_id = twt.team_id and twt.del_flag = 0
        where ss.id = #{sidebandId} and ss.del_flag = 0 and (tsb.id is not null or tsb.id = '')
        <if test="keyword != null and keyword!=''">
            AND (INSTR(tdd.dict_label,#{keyword}) > 0 or
            INSTR(tsb.box_name,#{keyword}) > 0 or
            INSTR(tsb.box_code,#{keyword}) > 0 )
        </if>
        order by ss.id ASC
    </select>

    <!--杆塔未绑定设备列表-->
    <select id="getDeviceNoLists" resultType="com.bonus.common.entity.bracelet.vo.SidebandDeviceVo">
    select * from(
        select id as deviceId,dev_type as deviceType,dev_name as deviceName,dev_code as deviceCode,tdd.dict_label AS
        deviceTypeName
        from tb_device td
        left join sys_dict_data tdd on tdd.dict_value=td.dev_type AND tdd .dict_type='sys_device_type'
        where bind_id is null and del_flag = 0
        union all
        select id as deviceId,'shx' as deviceType,box_name as deviceName,box_code as deviceCode,tdd.dict_label AS
        deviceTypeName
        from tb_sh_box tsb
        left join sys_dict_data tdd on tdd.dict_value='shx' AND tdd .dict_type='sys_device_type'
        where bind_id is null and del_flag = 0
        )a
        <where>
        <if test="keyword != null and keyword!=''">
            AND (INSTR(a.deviceTypeName,#{keyword}) > 0 or
            INSTR(a.deviceName,#{keyword}) > 0 or
            INSTR(a.deviceCode,#{keyword}) > 0 )
        </if>
        </where>
    </select>

    <!--杆塔下拉选-->
    <select id="getGtList" resultType="com.bonus.common.entity.bracelet.vo.ProjectPowerVo">
        select id,power_name as powerName
        from tb_project_power
        where del_flag = 0
    </select>

    <select id="selectSidebandById" parameterType="int" resultType="com.bonus.common.entity.bracelet.vo.SidebandVo">
        select ss.id as sidebandId,ss.sideband_name as sidebandName,ss.sideband_code as sidebandCode,project_name as projectName,
               gt_code as gtCode,bind_time as bindTime,remarks as remarks
        from sys_sideband ss
        where ss.id = #{sidebandId}
    </select>

    <select id="queryProjectName" parameterType="String" resultType="java.lang.String">
        select pro_name as name
        from tb_project
        where id = #{name}  and del_flag = 0
    </select>

    <!--判断边带名称是否存在-->
    <select id="isExist" resultType="com.bonus.common.entity.bracelet.vo.SidebandVo">
        select ss.id as sidebandId,ss.sideband_name as sidebandName,ss.sideband_code as sidebandCode,project_name as projectName,
               gt_code as gtCode,bind_time as bindTime,remarks as remarks
        from sys_sideband ss
        where ss.sideband_name = #{sidebandName}  and ss.id != #{sidebandId} and del_flag = 0
    </select>

    <update id="updateSideband">
        UPDATE sys_sideband ss
        SET sideband_name = #{sidebandName},sideband_code = #{sidebandCode},project_name = #{projectName},
            gt_code = #{gtCode},bind_time = #{bindTime},remarks = #{remarks}
        where id = #{sidebandId}
    </update>

    <!--判断新增时边带名称是否存在-->
    <select id="isNameExist" resultType="com.bonus.common.entity.bracelet.vo.SidebandVo">
        select ss.id as sidebandId,ss.sideband_name as sidebandName,ss.sideband_code as sidebandCode,project_name as projectName,
               gt_code as gtCode,bind_time as bindTime,remarks as remarks
        from sys_sideband ss
        where ss.sideband_name = #{sidebandName}  and del_flag = 0
    </select>

    <!--新增边带信息-->
    <insert id="addSideband" parameterType="com.bonus.common.entity.bracelet.vo.SidebandVo" >
        insert into sys_sideband(
        <if test="sidebandName != null and sidebandName != ''">sideband_name,</if>
        <if test="sidebandCode != null and sidebandCode != ''">sideband_code,</if>
        <if test="projectName != null and projectName != ''">project_name,</if>
        <if test="gtCode != null and gtCode != ''">gt_code,</if>
        <if test="remarks != null and remarks != ''">remarks,</if>
       del_flag
        )values(
        <if test="sidebandName != null and sidebandName != ''">#{sidebandName},</if>
        <if test="sidebandCode != null and sidebandCode != ''">#{sidebandCode},</if>
        <if test="projectName != null and projectName != ''">#{projectName},</if>
        <if test="gtCode != null and gtCode != ''">#{gtCode},</if>
        <if test="remarks != null and remarks != ''">#{remarks},</if>
        0
        )
    </insert>

    <update id="delSideband">
        UPDATE sys_sideband ss
        SET del_flag = 1
        where id = #{sidebandId}
    </update>

    <!--解除绑定-->
    <update id="delDevice">
        UPDATE tb_device
        SET bind_id = null
        where id = #{deviceId}
    </update>

    <!--解除绑定2-->
    <update id="delSh">
        UPDATE tb_sh_box
        SET bind_id = null
        where id = #{deviceId}
    </update>

    <!--获取人员管理表中领用人名称-->
    <select id="getDeviceLyName" parameterType="int" resultType="java.lang.String">
        select name
        from tb_people
        where id = #{id}  and del_flag = 0
    </select>

    <!--获取临时人员管理表中领用人名称-->
    <select id="getDeviceLyNameTemp" parameterType="int" resultType="java.lang.String">
        select name
        from tb_ls_user
        where id = #{id}
    </select>

    <!--绑定-->
    <update id="bindDevice">
        UPDATE tb_device
        SET bind_id = #{sidebandId}
        where id = #{deviceId}
    </update>

    <!--绑定-->
    <update id="bindSh">
        UPDATE tb_sh_box
        SET bind_id = #{sidebandId}
        where id = #{deviceId}
    </update>

</mapper>