<?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.BraceletMapper">
    <!--手环列表-->
    <select id="getBraceletLists" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select * from (
        select tb.id as shId,tb.sh_code as shCode,
        case
        WHEN  tb.peopel_type=0 then tpe.name
        WHEN  tb.peopel_type=1 then tlu.name
        ELSE ''
        END AS shPersonName,
        case
        WHEN  (tb.peopel_type=0 and (tpe.name is not null and tpe.name!='')) then tb.sh_status=0 and tb.sh_status
        WHEN  (tb.peopel_type=1 and (tlu.name is not null and tlu.name!='')) then tb.sh_status=0 and tb.sh_status
        ELSE 1
        END AS shStatus,
        case
        WHEN  (tsb.box_name is null or tsb.box_name='') then '未绑定'
        WHEN  (tsb.box_name is not null and tsb.box_name!='') then tsb.box_name
        ELSE '未绑定'
        END AS shboxName,
        tsb.box_code as shboxCode,tb.peopel_type as shPersonType,tb.bid_id as shPersonId,tsb.id as shboxId
        from tb_bracelet tb
        left join tb_sh_box tsb on tb.box_id = tsb.id and tsb.del_flag = 0
        left join tb_people tpe on tb.bid_id=tpe.id and tb.peopel_type=0
        left join tb_ls_user tlu on tb.bid_id=tlu.id and tb.peopel_type=1
        where tb.del_flag = 0
<!--        <if test="shPersonName != null and shPersonName!=''">-->
<!--            AND (tpe.name=#{shPersonName} or tlu.name=#{shPersonName})-->
<!--        </if>-->
        ) a
        <where>
            <if test="shCode != null and shCode!=''">
                AND INSTR(a.shCode,#{shCode}) > 0
            </if>
            <if test="shboxName != null and shboxName!='' and shboxName != '未绑定'">
                AND INSTR(a.shboxName,#{shboxName}) > 0
            </if>
            <if test="shboxName == '未绑定'">
                AND (a.shboxName is null or a.shboxName='')
            </if>
            <if test="shStatus != null">
                AND a.shStatus = #{shStatus}
            </if>
            <if test="shPersonName != null and shPersonName!=''">
                AND INSTR(a.shPersonName,#{shPersonName}) >0
            </if>
        </where>
        order by a.shId ASC
    </select>

    <!--手环箱绑定手环列表-->
    <select id="getShBindLists" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select * from (
        select tb.id as shId,tb.sh_code as shCode,
        case
        WHEN tb.peopel_type=0 then tpe.name
        WHEN tb.peopel_type=1 then tlu.name
        ELSE ''
        END AS shPersonName,
        tb.peopel_type as shPersonType,tb.bid_id as shPersonId
        from tb_bracelet tb
        left join tb_people tpe on tb.bid_id=tpe.id and tb.peopel_type=0
        left join tb_ls_user tlu on tb.bid_id=tlu.id and tb.peopel_type=1
        where tb.del_flag = 0 and tb.box_id = #{shboxId}
        ) a
        <where>
            <if test="shCode != null and shCode!=''">
                AND INSTR(a.shCode,#{shCode}) > 0
            </if>
            <if test="shPersonName != null and shPersonName!=''">
                AND INSTR(a.shPersonName,#{shPersonName}) > 0
            </if>
        </where>
        order by a.shId ASC
    </select>

    <!--未绑定手环列表-->
    <select id="getShBindNoLists" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select id as shId,sh_code as shCode
        from tb_bracelet
        where box_id is NULL and del_flag = 0
        order by id ASC
    </select>


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

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


    <!--手环箱列表-->
    <select id="getShboxLists" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select tsb.id as shboxId, tsb.box_name as shboxName,tsb.box_code as shboxCode,tsb.box_capacity as shboxCapacity,count(tb.id) as shboxBindNum
        from tb_sh_box tsb
        left join tb_bracelet tb on tsb.id = tb.box_id and tb.del_flag = 0
        where tsb.del_flag = 0
        group by tsb.id
        order by tsb.id ASC
    </select>

    <!--手环箱管理页面手环箱列表-->
    <select id="getShboxallLists" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select * from (
        select tsb.id as shboxId,tsb.box_code as shboxCode,tsb.box_name as shboxName,count(tb.id) as shboxBindNum,
        tsb.box_capacity as shboxCapacity,twt.team_leader as lyName,
        case
        when twt.team_leader is not null and twt.team_leader != '' then tsb.team_id = 0 and tsb.team_id
        else 1
        end as shboxStatus
        from tb_sh_box tsb
        left join t_work_team twt on tsb.team_id = twt.team_id and twt.del_flag = 0
        left join tb_bracelet tb on tsb.id = tb.box_id and tb.del_flag = 0
        where tsb.del_flag = 0
        group by tsb.id
        ) a
        <where>
            <if test="shboxName != null and shboxName!=''">
                AND INSTR(a.shboxName,#{shboxName}) > 0
            </if>
            <if test="shboxCode != null and shboxCode!=''">
                AND INSTR(a.shboxCode,#{shboxCode}) > 0
            </if>
            <if test="shboxStatus != null">
                AND a.shboxStatus = #{shboxStatus}
            </if>
        </where>
        order by a.shboxId ASC
    </select>

    <!--手环绑定选择列表-->
    <select id="getShboxBindLists" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select tsb.id as shboxId, tsb.box_name as shboxName,tsb.box_code as shboxCode,tsb.box_capacity as shboxCapacity,count(tb.id) as shboxBindNum
        from tb_sh_box tsb
        left join tb_bracelet tb on tsb.id = tb.box_id and tb.del_flag = 0
        where tsb.del_flag = 0
        <if test="keyword != null and keyword!=''">
            AND (INSTR(tsb.box_name,#{keyword}) > 0 or
                INSTR(tsb.box_code,#{keyword}) > 0 )
        </if>
        group by tsb.id
        order by tsb.id ASC
    </select>


    <update id="updateSh">
        UPDATE tb_bracelet tb
       set
           box_id = #{shboxId},
            sh_status = #{shStatus},bid_time = #{bidTime},sh_code = #{shCode},update_time = #{updateTime},update_user = #{updateUser}
        where id = #{shId}
    </update>

    <update id="updateShbox">
        UPDATE tb_sh_box
        set
            box_name = #{shboxName},
            box_code = #{shboxCode},box_capacity = #{shboxCapacity},update_time = #{updateTime},update_user = #{updateUser}
        where id = #{shboxId}
    </update>

    <!--判断新增时手环编号是否存在-->
    <select id="isCodeExist" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select id as shId
        from tb_bracelet tb
        where tb.sh_code = #{shCode} and del_flag = 0
    </select>

    <!--判断修改时手环编号是否存在-->
    <select id="isCodeEditExist" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select id as shId
        from tb_bracelet tb
        where tb.sh_code = #{shCode} and tb.id != #{shId} and del_flag = 0
    </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 = #{shId} and data_code = #{dataCode} and dev_type = 0
    </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 = #{shId} and data_code = #{dataCode} and id != #{id} and dev_type = 0
    </select>

    <!--判断修改时手环箱编号是否存在-->
    <select id="isBoxCodeExist" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select id as shboxId
        from tb_sh_box tsb
        where box_code = #{shboxCode} and id != #{shboxId} and del_flag = 0
    </select>

    <!--判断修改时手环箱名称是否存在-->
    <select id="isBoxNameExist" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select id as shboxId
        from tb_sh_box tsb
        where box_name = #{shboxName} and id != #{shboxId} and del_flag = 0
    </select>

    <!--判断新增时手环箱编号是否存在-->
    <select id="isBoxCodeExistTemp" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select id as shboxId
        from tb_sh_box tsb
        where box_code = #{shboxCode}  and del_flag = 0
    </select>

    <!--判断新增时手环箱名称是否存在-->
    <select id="isBoxNameExistTemp" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select id as shboxId
        from tb_sh_box tsb
        where box_name = #{shboxName}  and del_flag = 0
    </select>


    <!--新增手环信息-->
    <insert id="addSh" parameterType="com.bonus.common.entity.bracelet.vo.ShboxVo" >
        insert into tb_bracelet(
        <if test="shboxId != null">box_id,</if>
        <if test="shCode != null and shCode != ''">sh_code,</if>
        <if test="shStatus != null ">sh_status,</if>
        create_time,
        create_user,
        update_time,
        update_user,
        del_flag
        )values(
        <if test=" shboxId != null">#{shboxId},</if>
        <if test="shCode != null and shCode != ''">#{shCode},</if>
        <if test="shStatus != null ">#{shStatus},</if>
        #{createTime},
        #{createUser},
        #{updateTime},
        #{updateUser},
        0
        )
    </insert>

    <!--新增设备属性信息-->
    <insert id="addAttribute" parameterType="com.bonus.common.entity.bracelet.vo.AttributeVo" useGeneratedKeys="true" keyProperty="id">
        insert into tb_dev_details(
        <if test="shId != 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="shId != null">#{shId},</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>-->
        0,0
        )
    </insert>

    <!--修改设备属性信息-->
    <update id="editAttribute" parameterType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        UPDATE tb_dev_details
        <set>
            <if test="shId != null">dev_id = #{shId},</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="addBox" parameterType="com.bonus.common.entity.bracelet.vo.ShboxVo" >
        insert into tb_sh_box(
        <if test="shboxName != null and shboxName != ''">box_name,</if>
        <if test="shboxCode != null and shboxCode != ''">box_code,</if>
        <if test="shboxCapacity != null ">box_capacity,</if>
        create_time,
        create_user,
        update_time,
        update_user,
        del_flag
        )values(
        <if test="shboxName != null and shboxName != ''">#{shboxName},</if>
        <if test="shboxCode != null and shboxCode != ''">#{shboxCode},</if>
        <if test="shboxCapacity != null ">#{shboxCapacity},</if>
        #{createTime},
        #{createUser},
        #{updateTime},
        #{updateUser},
        0
        )
    </insert>

    <!--手环绑定选择页面绑定手环信息-->
    <update id="addShBind">
        UPDATE tb_bracelet
        SET box_id = #{shboxId},bid_time = now(),sh_status = 0,update_time = #{updateTime},update_user = #{updateUser}
        where id = #{shId}
    </update>

    <!--解除绑定-->
    <update id="delSh">
        UPDATE tb_bracelet
        SET box_id = null,sh_status = 1,bid_time = null,update_time = #{updateTime},update_user = #{updateUser}
        where id = #{shId}
    </update>

    <update id="delShById">
        UPDATE tb_bracelet
        SET del_flag = 1,sh_status = 1
        where id = #{shId}
    </update>

    <update id="delShboxById">
        UPDATE tb_sh_box
        SET del_flag = 1
        where id = #{shboxId}
    </update>


    <!--根据手环箱id获得详细信息-->
    <select id="selectBoxById" parameterType="int" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select tsb.id as shboxId,tsb.box_code as shboxCode,tsb.box_name as shboxName,tsb.box_capacity as shboxCapacity,count(tb.id) as shboxBindNum
        from tb_sh_box tsb
        left join tb_bracelet tb on tsb.id = tb.box_id and tb.del_flag = 0
        where  tsb.id = #{shboxId} and tsb.del_flag = 0
    </select>

    <!--根据手环箱id获得刷新详细信息-->
    <select id="selectBoxNewById" parameterType="int" resultType="com.bonus.common.entity.bracelet.vo.ShboxVo">
        select tsb.id as shboxId, tsb.box_name as shboxName,tsb.box_code as shboxCode,tsb.box_capacity as shboxCapacity,count(tb.id) as shboxBindNum
        from tb_sh_box tsb
        left join tb_bracelet tb on tsb.id = tb.box_id and tb.del_flag = 0
        where tsb.del_flag = 0 and tsb.id = #{shboxId}
        group by tsb.id
        order by tsb.id ASC
    </select>

    <!--绑定-->
    <update id="bindSh">
        UPDATE tb_bracelet
        SET box_id = #{shboxId},sh_status = 0,bid_time = now()
        where id = #{shId}
    </update>

    <!--设备属性列表-->
    <select id="getDeviceAttributeLists" resultType="com.bonus.common.entity.bracelet.vo.AttributeVo">
        select tdd.id as id,tdd.dev_id as shId,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 = 0 and tdd.del_flag = 0
            <if test="shId != null and shId!=''">
                AND tdd.dev_id = #{shId}
            </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 shId,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="getShSomeLists" resultType="com.bonus.common.entity.bracelet.vo.BraceletVo">
        select tb.id as shId,tb.sh_code as shCode,sh_status as shStatus,tsb.box_name as shboxName,
        tsb.box_code as shboxCode,tb.peopel_type as shPersonType,tb.bid_id as shPersonId,tsb.id as shboxId
        from tb_bracelet tb
        left join tb_sh_box tsb on tb.box_id = tsb.id and tsb.del_flag = 0
        where tb.del_flag = 0
        and tb.id in
        <foreach collection="array" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
        order by tb.id ASC
    </select>
</mapper>