<?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.DeviceLyRecordMapper">
    <!--设备领用列表-->
    <select id="getEquipmentReqLists" resultType="com.bonus.common.entity.bracelet.vo.EquipmentReqVo">
        SELECT * FROM (
            SELECT tdub.id,
            tdub.dev_id AS devId,
            tdub.dev_type AS devType,
            tdu.remarks,
            td.dev_code AS devCode,
            td.dev_name AS devName,
            tdub.ly_time AS lyTime,
            tdub.gh_time AS ghTime,
            sdd.dict_label AS deviceTypeName,
            twt.team_name AS teamName,
            twt.team_leader AS teamLeader,
            twt.leader_phone AS leaderPhone,
            tpd.depart_name AS departName,
            tp.pro_name AS proName
            FROM tb_dev_use tdu
            LEFT JOIN t_work_team twt ON tdu.team_id = twt.team_id AND twt.del_flag = 0
            LEFT JOIN tb_project tp ON tp.id = tdu.pro_id AND tp.del_flag = 0
            LEFT JOIN tb_project_depart tpd ON tpd.id = tp.depart_id AND tpd.del_flag = 0
            LEFT JOIN tb_dev_use_bid tdub ON tdu.id = tdub.id
            LEFT JOIN tb_device td ON tdub.dev_id = td.id AND td.del_flag = 0
            LEFT JOIN sys_dict_data sdd ON td.dev_type = sdd.dict_value AND sdd.dict_type = 'sys_device_type'
            WHERE tdu.del_flag = 0 AND tdub.dev_type NOT IN ('shx')
            <if test="devType!=null and devType!=''">
                AND td.dev_type = #{devType}
            </if>
        UNION ALL
            SELECT tdub.id,
            tdub.dev_id AS devId,
            tdub.dev_type AS devType,
            tdu.remarks,
            tsb.box_code AS devCode,
            tsb.box_name AS devName,
            tdub.ly_time AS lyTime,
            tdub.gh_time AS ghTime,
            '手环箱' AS deviceTypeName,
            twt.team_name AS teamName,
            twt.team_leader AS teamLeader,
            twt.leader_phone AS leaderPhone,
            tpd.depart_name AS departName,
            tp.pro_name AS proName
            FROM tb_dev_use tdu
            LEFT JOIN t_work_team twt ON tdu.team_id = twt.team_id AND twt.del_flag = 0
            LEFT JOIN tb_project tp ON tp.id = tdu.pro_id AND tp.del_flag = 0
            LEFT JOIN tb_project_depart tpd ON tpd.id = tp.depart_id AND tpd.del_flag = 0
            LEFT JOIN tb_dev_use_bid tdub ON tdu.id = tdub.id
            LEFT JOIN tb_sh_box tsb ON tdub.dev_id = tsb.id AND tsb.del_flag = 0
            WHERE tdu.del_flag = 0 AND tdub.dev_type = 'shx'
            <if test="devType!=null and devType!='' and devType != 'shx'">
                AND tsb.id = -1
            </if>
        ) a
        <where>
            <if test="proName!=null and proName!=''">
                AND INSTR(a.proName,#{proName}) > 0
            </if>
            <if test="departName!=null and departName!=''">
                AND INSTR(a.departName,#{departName}) > 0
            </if>
            <if test="teamName!=null and teamName!=''">
                AND INSTR(a.teamName,#{teamName}) > 0
            </if>
            <if test="devName!=null and devName!=''">
                AND INSTR(a.devName,#{devName}) > 0
            </if>
            <if test="devCode!=null and devCode!=''">
                AND INSTR(a.devCode,#{devCode}) > 0
            </if>
            <if test="lyTime!=null ">
                AND date_format(a.lyTime,'%y%m%d') = date_format(#{lyTime},'%y%m%d')
            </if>
            <if test="ghTime!=null ">
                AND date_format(a.ghTime,'%y%m%d') = date_format(#{ghTime},'%y%m%d')
            </if>
        </where>
    </select>
</mapper>