<?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.ruoyi.project.ehs.mapper.TMsdsMapper">

    <resultMap type="TMsds" id="TMsdsResult">
        <result property="id"    column="id"    />
        <result property="item"    column="item"    />
        <result property="productcategory"    column="productcategory"    />
        <result property="enName"    column="en_name"    />
        <result property="cnName"    column="cn_name"    />
        <result property="casNo"    column="cas_no"    />
        <result property="deptId"    column="dept_id"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="hazardPhrases"    column="hazard_phrases"    />
        <result property="remarks"    column="remarks"    />
        <result property="cmr"    column="cmr"    />
        <result property="ghsPictogram"    column="ghs_pictogram"    />
        <result property="createrCode"    column="creater_code"    />
        <result property="holdup"    column="holdup"    />
        <result property="createdate"    column="createdate"    />
        <result property="updaterCode"    column="updater_code"    />
        <result property="workArea"    column="work_area"    />
        <result property="updatedate"    column="updatedate"    />
        <result property="form"    column="form"    />
        <result property="explosionLimit"    column="explosion_limit"    />
        <result property="meltingPoint"    column="melting_point"    />
        <result property="boilingPoint"    column="boiling_point"    />
        <result property="density"    column="density"    />
        <result property="purity"    column="purity"    />
        <result property="responsibleCompany"    column="responsible_company"    />
        <result property="issueTime"    column="issue_time"    />
        <result property="issuetimeTwo"    column="issuetime_two"    />
        <result property="issuetimeThree"    column="issuetime_three"    />
        <result property="version"    column="version"    />
        <result property="reviewer"    column="reviewer"    />
        <result property="reviewerName"    column="name"    />
        <result property="reviewdate"    column="reviewdate"    />
        <result property="reviewState"    column="review_state"    />
        <result property="nextReviewdate"    column="next_reviewdate"    />
        <result property="deptName" column="dept_name" />
    </resultMap>

    <sql id="selectTMsdsVo">
        select d.id, d.item, d.productcategory, d.en_name, d.cn_name, d.cas_no, d.dept_id, d.del_flag, d.hazard_phrases, d.cmr, d.remarks, d.creater_code, d.ghs_pictogram, d.createdate, d.holdup, d.updater_code, d.work_area, d.form, d.updatedate, d.explosion_limit, d.melting_point, d.boiling_point, d.density, d.purity, d.responsible_company, d.issue_time, d.version, d.reviewer, t.name, d.reviewdate, d.review_state, d.next_reviewdate, d.issuetime_two, d.issuetime_three ,s.dept_name from t_msds d
      left join sys_dept s on s.dept_id = d.dept_id
      left join t_staffmgr t on t.staffid = d.reviewer and t.del_flag = 0
    </sql>

    <select id="selectTMsdsList" parameterType="TMsds" resultMap="TMsdsResult">
        <include refid="selectTMsdsVo"/>
        <where>
            <if test="cnName != null and cnName != ''"> and cn_name like concat(concat('%', #{cnName}), '%')</if>
            <!--<if test="cmr != null  and cmr != ''"> and cmr like concat(concat('%', #{cmr}), '%')</if>-->
            <if test="cmrs != null  and cmrs != ''">
                and
                    <foreach collection="cmrs.split(',')" item="item" index="index"
                             open="" close="" separator="and">
                       instr(cmr ,#{item}) >0
                    </foreach>
            </if>
            <if test="issueTime != null "> and issue_time = #{issueTime}</if>
            <if test="productcategory != null and productcategory != ''"> and productcategory = #{productcategory}</if>
            <if test="issueTimeYear != null "> and to_char(d.issue_time,'yyyy') = #{issueTimeYear}</if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        order by d.item asc
    </select>

    <select id="selectTMsdsById" parameterType="Long" resultMap="TMsdsResult">
        <include refid="selectTMsdsVo"/>
        where d.id = #{id}
    </select>

    <insert id="insertTMsds" parameterType="TMsds">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT seq_t_msds.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_msds
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="item != null">item,</if>
            <if test="productcategory != null">productcategory,</if>
            <if test="enName != null">en_name,</if>
            <if test="cnName != null">cn_name,</if>
            <if test="casNo != null">cas_no,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="hazardPhrases != null">hazard_phrases,</if>
            <if test="remarks != null">remarks,</if>
            <if test="cmr != null">cmr,</if>
            <if test="ghsPictogram != null">ghs_pictogram,</if>
            <if test="createrCode != null">creater_code,</if>
            <if test="holdup != null">holdup,</if>
            <if test="createdate != null">createdate,</if>
            <if test="updaterCode != null">updater_code,</if>
            <if test="workArea != null">work_area,</if>
            <if test="updatedate != null">updatedate,</if>
            <if test="form != null">form,</if>
            <if test="explosionLimit != null">explosion_limit,</if>
            <if test="meltingPoint != null">melting_point,</if>
            <if test="boilingPoint != null">boiling_point,</if>
            <if test="density != null">density,</if>
            <if test="purity != null">purity,</if>
            <if test="responsibleCompany != null">responsible_company,</if>
            <if test="issueTime != null">issue_time,</if>
            <if test="issuetimeTwo != null">issuetime_two,</if>
            <if test="issuetimeThree != null">issuetime_three,</if>
            <if test="version != null">version,</if>
            <if test="reviewer != null">reviewer,</if>
            <if test="reviewdate != null">reviewdate,</if>
            <if test="reviewState != null">review_state,</if>
            <if test="nextReviewdate != null">next_reviewdate,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="item != null">#{item},</if>
            <if test="productcategory != null">#{productcategory},</if>
            <if test="enName != null">#{enName},</if>
            <if test="cnName != null">#{cnName},</if>
            <if test="casNo != null">#{casNo},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="hazardPhrases != null">#{hazardPhrases},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="cmr != null">#{cmr},</if>
            <if test="ghsPictogram != null">#{ghsPictogram},</if>
            <if test="createrCode != null">#{createrCode},</if>
            <if test="holdup != null">#{holdup},</if>
            <if test="createdate != null">#{createdate},</if>
            <if test="updaterCode != null">#{updaterCode},</if>
            <if test="workArea != null">#{workArea},</if>
            <if test="updatedate != null">#{updatedate},</if>
            <if test="form != null">#{form},</if>
            <if test="explosionLimit != null">#{explosionLimit},</if>
            <if test="meltingPoint != null">#{meltingPoint},</if>
            <if test="boilingPoint != null">#{boilingPoint},</if>
            <if test="density != null">#{density},</if>
            <if test="purity != null">#{purity},</if>
            <if test="responsibleCompany != null">#{responsibleCompany},</if>
            <if test="issueTime != null">#{issueTime},</if>
            <if test="issuetimeTwo != null">#{issuetimeTwo},</if>
            <if test="issuetimeThree != null">#{issuetimeThree},</if>
            <if test="version != null">#{version},</if>
            <if test="reviewer != null">#{reviewer},</if>
            <if test="reviewdate != null">#{reviewdate},</if>
            <if test="reviewState != null">#{reviewState},</if>
            <if test="nextReviewdate != null">#{nextReviewdate},</if>
        </trim>
    </insert>

    <update id="updateTMsds" parameterType="TMsds">
        update t_msds
        <trim prefix="SET" suffixOverrides=",">
            <if test="item != null">item = #{item},</if>
            <if test="productcategory != null">productcategory = #{productcategory},</if>
            <if test="enName != null">en_name = #{enName},</if>
            <if test="cnName != null">cn_name = #{cnName},</if>
            <if test="casNo != null">cas_no = #{casNo},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="hazardPhrases != null">hazard_phrases = #{hazardPhrases},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="cmr != null">cmr = #{cmr},</if>
            <if test="ghsPictogram != null">ghs_pictogram = #{ghsPictogram},</if>
            <if test="createrCode != null">creater_code = #{createrCode},</if>
            <if test="holdup != null">holdup = #{holdup},</if>
            <if test="createdate != null">createdate = #{createdate},</if>
            <if test="updaterCode != null">updater_code = #{updaterCode},</if>
            <if test="workArea != null">work_area = #{workArea},</if>
            <if test="updatedate != null">updatedate = #{updatedate},</if>
            <if test="form != null">form = #{form},</if>
            <if test="explosionLimit != null">explosion_limit = #{explosionLimit},</if>
            <if test="meltingPoint != null">melting_point = #{meltingPoint},</if>
            <if test="boilingPoint != null">boiling_point = #{boilingPoint},</if>
            <if test="density != null">density = #{density},</if>
            <if test="purity != null">purity = #{purity},</if>
            <if test="responsibleCompany != null">responsible_company = #{responsibleCompany},</if>
            <if test="issueTime != null">issue_time = #{issueTime},</if>
            <if test="issuetimeTwo != null">issuetime_two = #{issuetimeTwo},</if>
            <if test="issuetimeThree != null">issuetime_three = #{issuetimeThree},</if>
            <if test="issueTime == null">issue_time = null,</if>
            <if test="issuetimeTwo == null">issuetime_two = null,</if>
            <if test="issuetimeThree == null">issuetime_three = null,</if>
            <if test="version != null">version = #{version},</if>
            <if test="reviewer != null">reviewer = #{reviewer},</if>
            <if test="reviewdate != null">reviewdate = #{reviewdate},</if>
            <if test="reviewState != null">review_state = #{reviewState},</if>
            <if test="nextReviewdate != null">next_reviewdate = #{nextReviewdate},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteTMsdsById" parameterType="Long">
        update t_msds set del_flag = 2 where id = #{id}
    </update>

    <update id="deleteTMsdsByIds" parameterType="String">
        update t_msds set del_flag = 2 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

</mapper>
