<?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.BraceletMapper">
    <insert id="insertLsUser" useGeneratedKeys="true" keyProperty="id">
        insert into tb_ls_user (
            name,
            id_card,
            phone,
            sex,
            create_time,
            team_id,
            pro_id
        )values (#{userName},#{idCard},#{phone},#{sex},now(),#{teamId},#{proId})
    </insert>
    <update id="updateBoxNum">
            UPDATE  tb_sh_box
            SET box_num=#{boxNum}
              where id=#{boxId}
    </update>
    <update id="updateBoxBraceletInfo">
        UPDATE tb_bracelet
            SET face_status=0,peopel_type=NULL,bid_id=NULL,bid_time=NULL,sh_status=0
                WHERE id=#{devId}
    </update>
    <select id="getBoxListById" resultType="com.bonus.app.entity.BoxEntity" >
        select id boxId,box_name boxName
        FROM tb_sh_box
        WHERE team_id is not null  and del_flag=0
        <if test="roleCode!=null and roleCode!='' and roleCode=='team'">
            and  team_id=#{teamId}
        </if>
    </select>
    <select id="getBoxInfo" resultType="com.bonus.app.entity.BraceletEntity">
        select blt.sh_code shCode,blt.id devId,peopel_type userType, blt.bid_id userId,
               tpp.lat sgLat ,tpp.lon  sgLon,team.team_name teamName,
               case
                   WHEN  peopel_type=0 then tpe.name
                   WHEN  peopel_type=1 then tlu.name
                   ELSE ''
                   END AS userName  ,
               case
                   WHEN  peopel_type=0 then tpe.phone
                   WHEN  peopel_type=1 then tlu.phone
                   ELSE ''
                   END AS phone ,
               case
                   WHEN  peopel_type=0 then tpe.id_card
                   WHEN  peopel_type=1 then tlu.id_card
                   ELSE ''
                   END AS idCard
        FROM tb_bracelet blt
         left join tb_sh_box sbx on blt.box_id=sbx.id
         left join t_work_team  team on team.team_id=sbx.team_id
         left join tb_project_power tpp on tpp.id=sbx.gt_id
         left join tb_people tpe on blt.bid_id=tpe.id and peopel_type=0
         left join tb_ls_user tlu on blt.bid_id=tlu.id and peopel_type=1
        where  blt.del_flag=0
        <if test="boxId!=null and boxId!=''">
            and  blt.box_id=#{boxId}
        </if>
        <if test="teamName!=null and teamName!=''">
            and team.team_name like concat('%',#{teamName},'%')
        </if>
        <if test="shCode!=null and shCode!=''">
            and  blt.sh_code like  concat('%',#{shCode},'%')
        </if>
        <if test="userName!=null and userName!=''">
            and (
                tpe.name like concat('%',#{userName},'%') or
                tlu.name like concat('%',#{userName},'%')
            )
        </if>
        <if test="roleCode!=null and roleCode!='' and roleCode=='team'">
             and  team_id=#{teamId}
        </if>
    </select>
    <select id="getBoxInfoBid" resultType="com.bonus.app.entity.BraceletEntity">
        select blt.sh_code shCode,blt.id devId,peopel_type userType, blt.bid_id userId,
        tpp.lat sgLat ,tpp.lon  sgLon,
        case
        WHEN  peopel_type=0 then tpe.name
        WHEN  peopel_type=1 then tlu.name
        ELSE ''
        END AS userName  ,
        case
        WHEN  peopel_type=0 then tpe.phone
        WHEN  peopel_type=1 then tlu.phone
        ELSE ''
        END AS phone ,
        case
        WHEN  peopel_type=0 then tpe.id_card
        WHEN  peopel_type=1 then tlu.id_card
        ELSE ''
        END AS idCard
        FROM tb_bracelet blt
        left join tb_sh_box sbx on blt.box_id=sbx.id
        left join tb_project_power tpp on tpp.id=sbx.gt_id
        left join tb_people tpe on blt.bid_id=tpe.id and peopel_type=0
        left join tb_ls_user tlu on blt.bid_id=tlu.id and peopel_type=1
        where  blt.del_flag=0 AND  blt.bid_id is not null and blt.bid_id!=''
        <if test="boxId!=null and boxId!=''">
            and  blt.box_id=#{boxId}
        </if>
        <if test="roleCode!=null and roleCode!='' and roleCode=='team'">
            and  team_id=#{teamId}
        </if>
        <if test="userName!=null and userName!=''">
            and (
            tpe.name like concat('%',#{userName},'%') or
            tlu.name like concat('%',#{userName},'%')
            )
        </if>
    </select>

    <select id="getBoxInfoDetails" resultType="com.bonus.app.entity.BraceletInfoEntity">
        select id ,data_name dataName,data_val dataVal,data_unit dataUnit,data_code dataCode,data_time dataTime
        from tb_dev_details
        where dev_id=#{devId}
    </select>
    <!--高空作业证-->
    <select id="getCertificate" resultType="java.lang.Integer">
        select count(1)
        from	tb_people_certificate
        where certificate_type=1   and
            people_id=#{userId}
    </select>
    <!--手环发放的数量-->
    <select id="getBoxFfNum" resultType="java.lang.Integer">
        select box_num
          from tb_sh_box
           where id=#{boxId}
    </select>
    <select id="getShUsed" resultType="java.lang.Integer">
        SELECT count(1)
         FROM  tb_bracelet blt
        where blt.box_id=#{boxId} and   blt.bid_id is not null and  blt.bid_id!='' and blt.del_flag=0
    </select>
    <select id="getBoxShNum" resultType="java.lang.Integer">
        SELECT count(1)
            FROM  tb_bracelet blt
            where blt.box_id=#{boxId} and blt.del_flag=0
    </select>
    <select id="getBoxBraceletNum" resultType="com.bonus.app.entity.BoxEntity">
        select box.id  boxId,count(1) shNum,box.box_num boxNum
        from tb_sh_box box
        left join tb_bracelet bt  on bt.box_id=box.id
        where bt.id  in(
        <foreach collection="list" item="devId" separator=",">
            #{devId}
        </foreach>
        )
        GROUP BY  box.id,box.box_num
    </select>

    <select id="getLsUserNum" resultType="java.lang.Integer">
        select  count(1)
        from  tb_ls_user
        where id_card=#{idCard}
    </select>
    <select id="getPeopleNum" resultType="java.lang.Integer">
        select  count(1)
        from  tb_people
        where id_card=#{idCard} and del_flag=0

    </select>
    <select id="getProIdByTeamId" resultType="java.lang.String">
        select  pro_id
        from t_work_team
                where team_id=#{teamId}
    </select>
</mapper>