<?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.regime.dao.TRegimeDataDao">

    <select id="findDepositoryList" resultType="com.testor.module.regime.model.dto.TRegimeDataListVo">
        SELECT
            a.id,
            a.org_id,
            a.materials_name,
            a.release_unit,
            a.level,
            a.one_materials_type,
            a.sen_materials_type,
            a.update_date,
            IFNULL( b.nature, b.tree_level ) AS company_nature,
            a.regime_data_type
        FROM
            t_regime_data a
                LEFT JOIN t_sys_org b ON a.release_unit_id = b.org_id
        where a.status = 0
        <if test="param.materialsName!=null and param.materialsName!=''">
            AND  a.materials_name   like concat('%', #{param.materialsName},'%')
        </if>

        <if test="param.startTime!=null and param.startTime!=''">
            AND a.update_date  &gt;= #{param.startTime}
        </if>
        <if test="param.endTime!=null and param.endTime!=''">
            AND a.update_date  &lt;=  #{param.endTime}
        </if>

        <if test="param.level!=null and param.level!='' and param.level != 1 ">
            AND   a.level  &gt; 1
        </if>
        <if test="param.level!=null and param.level!='' and param.level == 1 ">
            AND   a.level  = 1
        </if>
        <if test="param.companyNature!=null and param.companyNature!=''">
            AND IFNULL( b.nature, b.tree_level ) =  #{param.companyNature}
        </if>

        <if test="param.regimeDataType!=null and param.regimeDataType!=''">
            AND  a.regime_data_type  = #{param.regimeDataType}
        </if>
        <if test="param.releaseUnit!=null and param.releaseUnit!=''">
            AND  a.release_unit  = #{param.releaseUnit}
        </if>
        <if test="param.oneMaterialsType!=null and param.oneMaterialsType!='' and param.senMaterialsType!=null and param.senMaterialsType!=''">
            AND  a.one_materials_type  = #{param.oneMaterialsType}   AND  a.sen_materials_type  = #{param.senMaterialsType}
        </if>

        <if test=" (param.oneMaterialsType =='an' or param.oneMaterialsType =='jie' ) and (param.senMaterialsType == null or param.senMaterialsType=='')">
            AND ( a.one_materials_type in
            <foreach open="(" close=")" separator="," collection="param.oneMaterialsTypeList" item="item" index="index">
                #{item}
            </foreach>
            OR
            a.sen_materials_type in
            <foreach open="(" close=")" separator="," collection="param.senMaterialsTypeList" item="item" index="index">
                #{item}
            </foreach>
            )
        </if>
        <if test=" (param.oneMaterialsType =='jc_zhgl' or param.oneMaterialsType =='jc_5sgl' ) and (param.senMaterialsType == null or param.senMaterialsType=='')">
            AND  a.sen_materials_type in
            <foreach open="(" close=")" separator="," collection="param.senMaterialsTypeList" item="item" index="index">
                #{item}
            </foreach>

        </if>
        <if test=" (param.oneMaterialsType =='yij' ) and (param.senMaterialsType == null or param.senMaterialsType=='')">
            AND  a.one_materials_type  = #{param.oneMaterialsType}
        </if>

        ORDER BY company_nature ASC, a.update_date DESC
    </select>
</mapper>
