<?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.training.mapper.TWorkcertificateMapper">
    
    <resultMap type="TWorkcertificate" id="TWorkcertificateResult">
        <result property="id"    column="id"    />
        <result property="plantCode"    column="plant_code"    />
        <result property="name"    column="name"    />
        <result property="employeeid"    column="employeeid"    />
        <result property="classes"    column="classes"    />
        <result property="container"    column="container"    />
        <result property="pipe"    column="pipe"    />
        <result property="idnum"    column="idnum"    />
        <result property="reviewdate"    column="reviewdate"    />
        <result property="pipevalidity"    column="pipevalidity"    />
        <result property="boiler"    column="boiler"    />
        <result property="boilvalidity"    column="boilvalidity"    />
        <result property="hydrogenation"    column="hydrogenation"    />
        <result property="hydvalidity"    column="hydvalidity"    />
        <result property="hydnextreview"    column="hydnextreview"    />
        <result property="cracking"    column="cracking"    />
        <result property="crackingvalidity"    column="crackingvalidity"    />
        <result property="crackingreview"    column="crackingreview"    />
        <result property="foremantrain"    column="foremantrain"    />
        <result property="foremanreview"    column="foremanreview"    />
        <result property="foremannextreview"    column="foremannextreview"    />
        <result property="waterdate"    column="waterdate"    />
        <result property="energydate"    column="energydate"    />
        <result property="refrigerationdate"    column="refrigerationdate"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createrCode"    column="creater_code"    />
        <result property="createdate"    column="createdate"    />
        <result property="updaterCode"    column="updater_code"    />
        <result property="updatedate"    column="updatedate"    />
        <result property="remarks"    column="remarks"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName" column="dept_name" />
    </resultMap>

    <sql id="selectTWorkcertificateVo">
        select d.id, d.plant_code, d.name, d.employeeid, d.classes, d.container, d.pipe, d.idnum, d.reviewdate, d.pipevalidity, d.boiler, d.boilvalidity, d.hydrogenation, d.hydvalidity, d.hydnextreview, d.cracking, d.crackingvalidity, d.crackingreview, d.foremantrain, d.foremanreview, d.foremannextreview, d.waterdate, d.energydate, d.refrigerationdate, d.del_flag, d.creater_code, d.createdate, d.updater_code, d.updatedate, d.remarks, d.dept_id ,s.dept_name from t_workcertificate d
      left join sys_dept s on s.dept_id = d.dept_id
    </sql>

    <select id="selectTWorkcertificateList" parameterType="TWorkcertificate" resultMap="TWorkcertificateResult">
        <include refid="selectTWorkcertificateVo"/>
        <where>  
            <if test="classes != null  and classes != ''"> and classes = #{classes}</if>
            <!-- 王子文 2022年8月15日 -->
            <!-- 过滤掉锅炉证、加氢工艺、裂化工艺字段中值为（一个或多个）空格的数据 -->
            <if test="boiler == 'true'">and LTRIM(boiler) is not null</if>
            <if test="hydrogenation == 'true'"> and LTRIM(hydrogenation) IS NOT NULL</if>
            <if test="cracking == 'true'"> and LTRIM(cracking) IS NOT NULL</if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        order by d.id desc
    </select>

    <select id="selectList" parameterType="TWorkcertificate" resultMap="TWorkcertificateResult">
        <include refid="selectTWorkcertificateVo"/>
        <where>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        order by d.id desc
    </select>
    
    <select id="selectTWorkcertificateById" parameterType="Long" resultMap="TWorkcertificateResult">
        <include refid="selectTWorkcertificateVo"/>
        where id = #{id}
    </select>

    <select id="selectTWorkcertificateByEmployeeid" parameterType="String" resultMap="TWorkcertificateResult">
        <include refid="selectTWorkcertificateVo"/>
        where employeeid = #{employeeid}
    </select>
        
    <insert id="insertTWorkcertificate" parameterType="TWorkcertificate">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT seq_t_workcertificate.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_workcertificate
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="plantCode != null and plantCode != ''">plant_code,</if>
            <if test="name != null">name,</if>
            <if test="employeeid != null">employeeid,</if>
            <if test="classes != null">classes,</if>
            <if test="container != null">container,</if>
            <if test="pipe != null">pipe,</if>
            <if test="idnum != null">idnum,</if>
            <if test="reviewdate != null">reviewdate,</if>
            <if test="pipevalidity != null">pipevalidity,</if>
            <if test="boiler != null">boiler,</if>
            <if test="boilvalidity != null">boilvalidity,</if>
            <if test="hydrogenation != null">hydrogenation,</if>
            <if test="hydvalidity != null">hydvalidity,</if>
            <if test="hydnextreview != null">hydnextreview,</if>
            <if test="cracking != null">cracking,</if>
            <if test="crackingvalidity != null">crackingvalidity,</if>
            <if test="crackingreview != null">crackingreview,</if>
            <if test="foremantrain != null">foremantrain,</if>
            <if test="foremanreview != null">foremanreview,</if>
            <if test="foremannextreview != null">foremannextreview,</if>
            <if test="waterdate != null">waterdate,</if>
            <if test="energydate != null">energydate,</if>
            <if test="refrigerationdate != null">refrigerationdate,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="createrCode != null">creater_code,</if>
            <if test="createdate != null">createdate,</if>
            <if test="updaterCode != null">updater_code,</if>
            <if test="updatedate != null">updatedate,</if>
            <if test="remarks != null">remarks,</if>
            <if test="deptId != null">dept_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="plantCode != null and plantCode != ''">#{plantCode},</if>
            <if test="name != null">#{name},</if>
            <if test="employeeid != null">#{employeeid},</if>
            <if test="classes != null">#{classes},</if>
            <if test="container != null">#{container},</if>
            <if test="pipe != null">#{pipe},</if>
            <if test="idnum != null">#{idnum},</if>
            <if test="reviewdate != null">#{reviewdate},</if>
            <if test="pipevalidity != null">#{pipevalidity},</if>
            <if test="boiler != null">#{boiler},</if>
            <if test="boilvalidity != null">#{boilvalidity},</if>
            <if test="hydrogenation != null">#{hydrogenation},</if>
            <if test="hydvalidity != null">#{hydvalidity},</if>
            <if test="hydnextreview != null">#{hydnextreview},</if>
            <if test="cracking != null">#{cracking},</if>
            <if test="crackingvalidity != null">#{crackingvalidity},</if>
            <if test="crackingreview != null">#{crackingreview},</if>
            <if test="foremantrain != null">#{foremantrain},</if>
            <if test="foremanreview != null">#{foremanreview},</if>
            <if test="foremannextreview != null">#{foremannextreview},</if>
            <if test="waterdate != null">#{waterdate},</if>
            <if test="energydate != null">#{energydate},</if>
            <if test="refrigerationdate != null">#{refrigerationdate},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="createrCode != null">#{createrCode},</if>
            <if test="createdate != null">#{createdate},</if>
            <if test="updaterCode != null">#{updaterCode},</if>
            <if test="updatedate != null">#{updatedate},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="deptId != null">#{deptId},</if>
         </trim>
    </insert>

    <update id="updateTWorkcertificate" parameterType="TWorkcertificate">
        update t_workcertificate
        <trim prefix="SET" suffixOverrides=",">
            <if test="plantCode != null and plantCode != ''">plant_code = #{plantCode},</if>
            <if test="name != null">name = #{name},</if>
            <if test="employeeid != null">employeeid = #{employeeid},</if>
            <if test="classes != null">classes = #{classes},</if>
            <if test="container != null">container = #{container},</if>
            <if test="pipe != null">pipe = #{pipe},</if>
            <if test="idnum != null">idnum = #{idnum},</if>
            <if test="reviewdate != null">reviewdate = #{reviewdate},</if>
            <if test="pipevalidity != null">pipevalidity = #{pipevalidity},</if>
            <if test="boiler != null">boiler = #{boiler},</if>
            <if test="boilvalidity != null">boilvalidity = #{boilvalidity},</if>
            <if test="hydrogenation != null">hydrogenation = #{hydrogenation},</if>
            <if test="hydvalidity != null">hydvalidity = #{hydvalidity},</if>
            <if test="hydnextreview != null">hydnextreview = #{hydnextreview},</if>
            <if test="cracking != null">cracking = #{cracking},</if>
            <if test="crackingvalidity != null">crackingvalidity = #{crackingvalidity},</if>
            <if test="crackingreview != null">crackingreview = #{crackingreview},</if>
            <if test="foremantrain != null">foremantrain = #{foremantrain},</if>
            <if test="foremanreview != null">foremanreview = #{foremanreview},</if>
            <if test="foremannextreview != null">foremannextreview = #{foremannextreview},</if>
            <if test="waterdate != null">waterdate = #{waterdate},</if>
            <if test="energydate != null">energydate = #{energydate},</if>
            <if test="refrigerationdate != null">refrigerationdate = #{refrigerationdate},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createrCode != null">creater_code = #{createrCode},</if>
            <if test="createdate != null">createdate = #{createdate},</if>
            <if test="updaterCode != null">updater_code = #{updaterCode},</if>
            <if test="updatedate != null">updatedate = #{updatedate},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateTWorkcertificateByEmployeeid" parameterType="TWorkcertificate">
        update t_workcertificate
        <trim prefix="SET" suffixOverrides=",">
            <if test="plantCode != null and plantCode != ''">plant_code = #{plantCode},</if>
            <if test="name != null">name = #{name},</if>
            <if test="employeeid != null">employeeid = #{employeeid},</if>
            <if test="classes != null">classes = #{classes},</if>
            <if test="container != null">container = #{container},</if>
            <if test="pipe != null">pipe = #{pipe},</if>
            <if test="idnum != null">idnum = #{idnum},</if>
            <if test="reviewdate != null">reviewdate = #{reviewdate},</if>
            <if test="pipevalidity != null">pipevalidity = #{pipevalidity},</if>
            <if test="boiler != null">boiler = #{boiler},</if>
            <if test="boilvalidity != null">boilvalidity = #{boilvalidity},</if>
            <if test="hydrogenation != null">hydrogenation = #{hydrogenation},</if>
            <if test="hydvalidity != null">hydvalidity = #{hydvalidity},</if>
            <if test="hydnextreview != null">hydnextreview = #{hydnextreview},</if>
            <if test="cracking != null">cracking = #{cracking},</if>
            <if test="crackingvalidity != null">crackingvalidity = #{crackingvalidity},</if>
            <if test="crackingreview != null">crackingreview = #{crackingreview},</if>
            <if test="foremantrain != null">foremantrain = #{foremantrain},</if>
            <if test="foremanreview != null">foremanreview = #{foremanreview},</if>
            <if test="foremannextreview != null">foremannextreview = #{foremannextreview},</if>
            <if test="waterdate != null">waterdate = #{waterdate},</if>
            <if test="energydate != null">energydate = #{energydate},</if>
            <if test="refrigerationdate != null">refrigerationdate = #{refrigerationdate},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createrCode != null">creater_code = #{createrCode},</if>
            <if test="createdate != null">createdate = #{createdate},</if>
            <if test="updaterCode != null">updater_code = #{updaterCode},</if>
            <if test="updatedate != null">updatedate = #{updatedate},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
        </trim>
        where employeeid = #{employeeid}
    </update>


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

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

    <select id="selectCerData" resultType="com.ruoyi.project.common.domain.DataEntity">
       SELECT count(1) as dataNum from T_WORKCERTIFICATE
       <where>
           <if test="cert == 1">
        and BOILER is not null and TRIM(BOILER) != ' '
           </if>
           <if test="cert == 2">
               and HYDROGENATION is not null and TRIM(HYDROGENATION) != ' '
           </if>
           <if test="cert == 3">
               and CRACKING is not null and TRIM(CRACKING) != ' '
           </if>
       </where>
    </select>

</mapper>