<?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.testor.module.emergency.dao.TEmergencyGoodsDao">

    <!--级联查询父菜单-->
    <select id="exportGoods" resultType="com.testor.module.emergency.model.dto.ExportGoodsDTO">
        select (@i:=@i+1) AS serialNumber,a.*
        from (
        SELECT teg.id,
        tso.org_name,
        teg.org_id,
        teg.goods_type_key AS goodsTypeName,
        teg.name_key AS goodsName,
        specification,
        reserve_num,
        reserve_date,
        position,
        CONCAT('待维修/待更换数量：', IFNULL(awaiting_repair_num, 0)) as goodCondition,
        des,
        tss.name as spaceName ,
        teg.cycle,
        teg.next_check_time AS nextCheckTime
        FROM t_emergency_goods as teg
        left join t_emergency_goods_position as tegp on teg.id = tegp.goods_id
        left join t_sys_org as tso on teg.org_id = tso.org_id
        left join t_safe_space as tss on tegp.space_id = tss.id
        <where>
            <if test="dto.orgId != null and dto.orgId.size != 0">
                AND tso.org_id IN
                <foreach collection="dto.orgId" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="dto.goodsTypeKey != null and dto.goodsTypeKey != ''">
               and goods_type_key = #{dto.goodsTypeKey}
            </if>
            <if test="dto.nameKey != null and dto.nameKey != ''">
               and name_key = #{dto.nameKey}
            </if>
            <if test="dto.validityStartDate != null and dto.validityStartDate != '' and dto.validityEndDate != null and dto.validityEndDate != ''">
                and validity_end_date between #{dto.validityStartDate} and #{dto.validityEndDate}
            </if>
            <if test="dto.isLate != null and dto.isLate == 0  ">
                and teg.next_check_time >= NOW()
            </if>

            <if test="dto.isLate != null and dto.isLate == 1  ">
                and teg.next_check_time &lt;= NOW()
            </if>

        </where>
        order by teg.create_date desc
        ) as a, (SELECT @i := 0) AS i
    </select>


</mapper>