<?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.DeviceMapper">
    <!--设备列表-->
    <select id="getDeviceLists" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select * from (
        select td.id as deviceId,td.dev_type as deviceType,td.dev_name as deviceName,td.dev_code as deviceCode,
               tdl.ly_user as lyId,tdl.user_type as lyType,tdd.dict_label AS deviceTypeName,td.dev_index as deviceIndex,
        case
        WHEN  tdl.user_type=0 then tpe.name
        WHEN  tdl.user_type=1 then tlu.name
        ELSE ''
        END AS lyName,
        case
        WHEN  (tdl.user_type=0 and (tpe.name is not null and tpe.name!='')) then td.dev_status=0 and td.dev_status
        WHEN  (tdl.user_type=1 and (tlu.name is not null and tlu.name!='')) then td.dev_status=0 and td.dev_status
        ELSE 1
        END AS deviceStatus
        from tb_device td
        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
        left join tb_people tpe on tdl.ly_user=tpe.id and tdl.user_type=0
        left join tb_ls_user tlu on tdl.ly_user=tlu.id and tdl.user_type=1
        where td.del_flag = 0
        ) a
        <where>
        <if test="deviceType != null and deviceType!=''">
            AND INSTR(a.deviceType,#{deviceType}) > 0
        </if>
        <if test="deviceName != null and deviceName!=''">
            AND INSTR(a.deviceName,#{deviceName}) > 0
        </if>
        <if test="deviceCode != null and deviceCode!=''">
            AND INSTR(a.deviceCode,#{deviceCode}) > 0
        </if>
        <if test="deviceStatus != null ">
            AND a.deviceStatus = #{deviceStatus}
        </if>
        </where>
        order by a.deviceId ASC
    </select>

    <!--获取人员管理表中领用人名称-->
    <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>

    <select id="selectDeviceById" parameterType="int" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select td.id as deviceId,td.dev_type as deviceType,tdd.dict_label AS deviceTypeName,td.dev_name as deviceName,td.dev_code as deviceCode,td.dev_status as deviceStatus,td.dev_index as deviceIndex
        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 td.id = #{deviceId}  and td.del_flag = 0
    </select>



    <!--判断设备名称是否存在-->
    <select id="isExist" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select td.id as deviceId,td.dev_type as deviceType,td.dev_name as deviceName,td.dev_code as deviceCode,td.dev_status as deviceStatus
        from tb_device td
        where td.dev_name = #{deviceName}  and td.id != #{deviceId} and del_flag = 0
    </select>

    <!--判断设备编码是否存在-->
    <select id="isExistCode" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select td.id as deviceId,td.dev_type as deviceType,td.dev_name as deviceName,td.dev_code as deviceCode,td.dev_status as deviceStatus
        from tb_device td
        where td.dev_code = #{deviceCode}  and td.id != #{deviceId} and del_flag = 0
    </select>

    <update id="updateDevice">
        UPDATE tb_device td
        SET dev_type = #{deviceType},dev_name = #{deviceName},dev_code = #{deviceCode},dev_index = #{deviceIndex},update_time = #{updateTime},update_user = #{updateUser}
        where id = #{deviceId}
    </update>

    <!--判断新增时设备名称是否存在-->
    <select id="isNameExist" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select td.id as deviceId,td.dev_type as deviceType,td.dev_name as deviceName,td.dev_code as deviceCode,td.dev_status as deviceStatus
        from tb_device td
        where td.dev_name = #{deviceName}  and del_flag = 0
    </select>

    <!--判断新增时设备编码是否存在-->
    <select id="isCodeExist" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select td.id as deviceId,td.dev_type as deviceType,td.dev_name as deviceName,td.dev_code as deviceCode,td.dev_status as deviceStatus
        from tb_device td
        where td.dev_code = #{deviceCode}  and del_flag = 0
    </select>

    <!--判断新增时设备领用表是否存在-->
    <select id="getDeviceLy" resultType="java.lang.Integer">
        select count(dev_id) as count
        from tb_dev_ly
        where dev_id = #{deviceId}
    </select>

    <!--判断新增时级联编码是否存在-->
    <select id="isDataCodeExist" resultType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        select dev_id as devId,data_name as dataName,data_val as dataVal,data_unit as dataUnit,data_code as dataCode
        from tb_dev_details
        where  dev_id = #{deviceId} and  data_code = #{dataCode} and dev_type = 1
    </select>

    <!--判断修改时级联编码是否存在-->
    <select id="isEditDataCodeExist" resultType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        select dev_id as devId,data_name as dataName,data_val as dataVal,data_unit as dataUnit,data_code as dataCode
        from tb_dev_details
        where dev_id = #{deviceId} and  data_code = #{dataCode} and id != #{id} and dev_type = 1
    </select>

    <!--新增设备信息-->
    <insert id="addDevice" parameterType="com.bonus.common.entity.bracelet.vo.DeviceVo" useGeneratedKeys="true" keyProperty="deviceId">
        insert into tb_device(
        <if test="deviceType != null and deviceType != ''">dev_type,</if>
        <if test="deviceName != null and deviceName != ''">dev_name,</if>
        <if test="deviceCode != null and deviceCode != ''">dev_code,</if>
        <if test="deviceIndex != null ">dev_index,</if>
        create_time,
        create_user,
        update_time,
        update_user,
        dev_status,del_flag
        )values(
        <if test="deviceType != null and deviceType != ''">#{deviceType},</if>
        <if test="deviceName != null and deviceName != ''">#{deviceName},</if>
        <if test="deviceCode != null and deviceCode != ''">#{deviceCode},</if>
        <if test="deviceIndex != null ">#{deviceIndex},</if>
        #{createTime},
        #{createUser},
        #{updateTime},
        #{updateUser},
        1,0
        )
    </insert>

    <!--新增设备属性信息-->
    <insert id="addAttribute" parameterType="com.bonus.common.entity.bracelet.vo.AttributeVo" useGeneratedKeys="true" keyProperty="id">
        insert into tb_dev_details(
        <if test="deviceId != null">dev_id,</if>
        <if test="dataName != null and dataName != ''">data_name,</if>
<!--        <if test="dataVal != null and dataVal != ''">data_val,</if>-->
        <if test="dataUnit != null and dataUnit != ''">data_unit,</if>
        <if test="dataCode != null and dataCode != ''">data_code,</if>
        <if test="dataType != null and dataType != ''">data_type,</if>
<!--        <if test="dataTime != null ">data_time,</if>-->
        dev_type,del_flag
        )values(
        <if test="deviceId != null">#{deviceId},</if>
        <if test="dataName != null and dataName != ''">#{dataName},</if>
<!--        <if test="dataVal != null and dataVal != ''">#{dataVal},</if>-->
        <if test="dataUnit != null and dataUnit != ''">#{dataUnit},</if>
        <if test="dataCode != null and dataCode != ''">#{dataCode},</if>
        <if test="dataType != null and dataType != ''">#{dataType},</if>
<!--        <if test="dataTime != null ">#{dataTime},</if>-->
        1,0
        )
    </insert>

    <!--修改设备属性信息-->
    <update id="editAttribute" parameterType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        UPDATE tb_dev_details
        <set>
            <if test="deviceId != null">dev_id = #{deviceId},</if>
            <if test="dataName != null and dataName != ''">data_name = #{dataName},</if>
<!--            <if test="dataVal != null and dataVal != ''">data_val = #{dataVal},</if>-->
            <if test="dataUnit != null and dataUnit != ''">data_unit = #{dataUnit},</if>
            <if test="dataCode != null and dataCode != ''">data_code = #{dataCode},</if>
            <if test="dataType != null and dataType != ''">data_type = #{dataType},</if>
<!--            <if test="dataTime != null ">data_time = #{dataTime},</if>-->
            del_flag = 0
        </set>
        where id = #{id}
    </update>

    <!--设置filePath-->
    <update id="setFilePath">
        UPDATE tb_dev_details
        SET data_logo = #{filePath}
        where id = #{id}
    </update>

    <!--清除filePath-->
    <update id="editFilePath">
        UPDATE tb_dev_details
        SET data_logo = null
        where id = #{id}
    </update>

    <!--新增设备领用信息-->
    <insert id="addDeviceLy">
        insert into tb_dev_ly(
        dev_id,ly_time
        )values(
        #{deviceId},now()
        )
    </insert>

    <!--删除设备信息-->
    <update id="deleteDeviceById">
        UPDATE tb_device
        SET del_flag = 1
        where id = #{deviceId}
    </update>

    <!--删除设备领用信息-->
    <delete id="deleteDeviceLy">
        delete from tb_dev_ly where
            dev_id = #{deviceId}
    </delete>

    <!--设备属性列表-->
    <select id="getDeviceAttributeLists" resultType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        select tdd.id as id,tdd.dev_id as deviceId,tdd.data_name as dataName,tdd.data_val as dataVal,tdd.data_unit as dataUnit,
        tdd.data_code as dataCode,tdd.data_type as dataType,tdd.data_time as dataTime,sfs.file_path AS filePath,sfs.id AS fileId
        from tb_dev_details tdd
        left join sys_file_source sfs on tdd.id = sfs.source_id AND sfs.source_type = 7 AND sfs.del_flag = 0
        <where>
            tdd.dev_type = 1 and tdd.del_flag = 0
            <if test="deviceId != null and deviceId!=''">
                AND tdd.dev_id = #{deviceId}
            </if>
            <if test="dataCode != null and dataCode!=''">
                AND INSTR(tdd.data_code,#{dataCode}) > 0
            </if>
        </where>
        order by tdd.id ASC
    </select>

    <!--根据id获取设备属性-->
    <select id="getAttributeInfo" resultType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        select tdd.id as id,tdd.dev_id as deviceId,tdd.data_name as dataName,tdd.data_val as dataVal,tdd.data_unit as dataUnit,
               tdd.data_code as dataCode,tdd.data_type as dataType,tdd.data_time as dataTime,sfs.file_path AS filePath,sfs.id AS fileId
        from tb_dev_details tdd
        left join sys_file_source sfs on tdd.id = sfs.source_id AND sfs.source_type = 7 AND sfs.del_flag = 0
        WHERE tdd.id = #{id} AND tdd.del_flag = 0
    </select>

    <update id="deleteFileId" parameterType="String">
        update sys_file_source
        set	del_flag = 1
        where file_path = #{filePath} and source_type = 7 and del_flag = 0
    </update>

    <update id="deleteFile" >
        update tb_dev_details
        set	del_flag = 1
        where id = #{id} and del_flag = 0
    </update>

    <select id="selectFilePathById" resultType="java.lang.String">
        select file_path as filePath
        from sys_file_source
        where source_id = #{id} and source_type = 7 and del_flag = 0
    </select>

    <select id="getFile" resultType="int">
        select count(*)
        from sys_file_source
        where source_id = #{id} and source_type = 7 and del_flag = 0
    </select>

    <update id="deleFileSource" >
        update sys_file_source
        set	del_flag = 1
        where source_id = #{id} and source_type = 7 and del_flag = 0
    </update>

    <!--批量导出-->
    <select id="getDeviceSomeLists" resultType="com.bonus.common.entity.bracelet.vo.DeviceVo">
        select 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,tdd.dict_label AS deviceTypeName,td.dev_index as deviceIndex
        from tb_device td
        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 td.del_flag = 0
        and td.dev_code in
        <foreach collection="array" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
        order by td.id ASC
    </select>
</mapper>