<?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.TWorklicenseMapper">
    
    <resultMap type="TWorklicense" id="TWorklicenseResult">
        <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="post"    column="post"    />
        <result property="crackingIdnum"    column="cracking_idnum"    />
        <result property="separationIdnum"    column="separation_idnum"    />
        <result property="compressionIdnum"    column="compression_idnum"    />
        <result property="pguaeuIdnum"    column="pguaeu_idnum"    />
        <result property="separationDate"    column="separation_date"    />
        <result property="compressionDate"    column="compression_date"    />
        <result property="pguaeuDate"    column="pguaeu_date"    />
        <result property="crackingDate"    column="cracking_date"    />
        <result property="idnum"    column="idnum"    />
        <result property="boiler"    column="boiler"    />
        <result property="cracking"    column="cracking"    />
        <result property="hotarea"    column="hotarea"    />
        <result property="coldarea"    column="coldarea"    />
        <result property="recertificatedate"    column="recertificatedate"    />
        <result property="validity"    column="validity"    />
        <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="selectTWorklicenseVo">
        select d.id, d.plant_code, d.name, d.employeeid, d.classes, d.post, d.idtype, d.cracking_idnum, d.boiler, d.cracking, d.hotarea, d.coldarea, d.aromatic, d.recertificatedate, d.validity, d.del_flag, d.creater_code, d.createdate, d.updater_code, d.updatedate, d.remarks, d.dept_id, d.separation_idnum, d.compression_idnum, d.pguaeu_idnum, d.separation_date, d.compression_date, d.pguaeu_date, d.cracking_date ,s.dept_name from t_worklicense d
      left join sys_dept s on s.dept_id = d.dept_id
    </sql>

    <select id="selectTWorklicenseList" parameterType="TWorklicense" resultMap="TWorklicenseResult">
        <include refid="selectTWorklicenseVo"/>
        <where>
            <if test="employeeid != null "> and d.employeeid = #{employeeid}</if>
            <if test="name != null  and name != ''"> and name like concat(concat('%', #{name}), '%')</if>
            <if test="classes != null  and classes != ''"> and classes = #{classes}</if>
            <if test="boiler == 'true'"> and boiler = 1</if>
            <if test="cracking == 'true'"> and cracking = 1</if>
            <if test="hotarea == 'true'"> and hotarea = 1</if>
            <if test="coldarea == 'true'"> and coldarea = 1</if>
            <if test="notHave == 'true'"> and boiler = 0 and cracking = 0 and hotarea = 0 and coldarea = 0</if>
            <if test="expiring == 'true'">and validity <![CDATA[<=]]> #{expiringDate}</if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        order by d.classes asc
    </select>

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

    <select id="selectTWorklicenseByEmployeeid" parameterType="String" resultMap="TWorklicenseResult">
        <include refid="selectTWorklicenseVo"/>
        where employeeid = #{employeeid}
    </select>
        
    <insert id="insertTWorklicense" parameterType="TWorklicense">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT seq_t_worklicense.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_worklicense
        <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="post != null">post,</if>
            <if test="idtype != null">idtype,</if>
            <if test="crackingIdnum != null">cracking_idnum,</if>
            <if test="separationIdnum != null">separation_idnum,</if>
            <if test="compressionIdnum != null">compression_idnum,</if>
            <if test="pguaeuIdnum != null">pguaeu_idnum,</if>
            <if test="boiler == 'true'">boiler,</if>
            <if test="cracking == 'true'">cracking,</if>
            <if test="hotarea == 'true'">hotarea,</if>
            <if test="coldarea == 'true'">coldarea,</if>
            <if test="boiler == 'false'">boiler,</if>
            <if test="cracking == 'false'">cracking,</if>
            <if test="hotarea == 'false'">hotarea,</if>
            <if test="coldarea == 'false'">coldarea,</if>
            <if test="recertificatedate != null">recertificatedate,</if>
            <if test="validity != null">validity,</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>
            <if test="separationDate != null">separation_date,</if>
            <if test="compressionDate != null">compression_date,</if>
            <if test="pguaeuDate != null">pguaeu_date,</if>
            <if test="crackingDate != null">cracking_date,</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="post != null">#{post},</if>
            <if test="idtype != null">#{idtype},</if>
            <if test="crackingIdnum != null">#{crackingIdnum},</if>
            <if test="separationIdnum != null">#{separationIdnum},</if>
            <if test="compressionIdnum != null">#{compressionIdnum},</if>
            <if test="pguaeuIdnum != null">#{pguaeuIdnum},</if>
            <if test="boiler == 'true'">1,</if>
            <if test="cracking == 'true'">1,</if>
            <if test="hotarea == 'true'">1,</if>
            <if test="coldarea == 'true'">1,</if>
            <if test="boiler == 'true'">0,</if>
            <if test="cracking == 'true'">0,</if>
            <if test="hotarea == 'true'">0,</if>
            <if test="coldarea == 'true'">0,</if>
            <if test="recertificatedate != null">#{recertificatedate},</if>
            <if test="validity != null">#{validity},</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>
            <if test="separationDate != null">#{separationDate},</if>
            <if test="compressionDate != null">#{compressionDate},</if>
            <if test="pguaeuDate != null">#{pguaeuDate},</if>
            <if test="crackingDate != null">#{crackingDate},</if>
         </trim>
    </insert>

    <update id="updateTWorklicense" parameterType="TWorklicense">
        update t_worklicense
        <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="post != null">post = #{post},</if>
            <if test="idtype != null">idtype = #{idtype},</if>
            <if test="crackingIdnum != null">cracking_idnum = #{crackingIdnum},</if>
            <if test="separationIdnum != null">separation_idnum = #{separationIdnum},</if>
            <if test="compressionIdnum != null">compression_idnum = #{compressionIdnum},</if>
            <if test="pguaeuIdnum != null">pguaeu_idnum = #{pguaeuIdnum},</if>
            <if test="boiler == 'true'">boiler = 1,</if>
            <if test="cracking == 'true'">cracking = 1,</if>
            <if test="hotarea == 'true'">hotarea = 1,</if>
            <if test="coldarea == 'true'">coldarea = 1,</if>
            <if test="boiler == 'false'">boiler = 0,</if>
            <if test="cracking == 'false'">cracking = 0,</if>
            <if test="hotarea == 'false'">hotarea = 0,</if>
            <if test="coldarea == 'false'">coldarea = 0,</if>
            <if test="recertificatedate != null">recertificatedate = #{recertificatedate},</if>
            <if test="validity != null">validity = #{validity},</if>
            <if test="recertificatedate == null">recertificatedate = NULL,</if>
            <if test="validity == null">validity = NULL,</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>
            <if test="separationDate != null">separation_date = #{separationDate},</if>
            <if test="compressionDate != null">compression_date = #{compressionDate},</if>
            <if test="pguaeuDate != null">pguaeu_date = #{pguaeuDate},</if>
            <if test="crackingDate != null">cracking_date = #{crackingDate},</if>
            <if test="separationDate == null">separation_date = NULL,</if>
            <if test="compressionDate == null">compression_date = NULL,</if>
            <if test="pguaeuDate == null">pguaeu_date = NULL,</if>
            <if test="crackingDate == null">cracking_date = NULL,</if>
        </trim>
        where id = #{id}
    </update>

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

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

    <select id="selectLicenseData" resultType="com.ruoyi.project.common.domain.DataEntity">
        SELECT count(1) as dataNum from t_worklicense
        <where>
            <if test="type == 1">
                and boiler = 1
            </if>
            <if test="type == 2">
                and cracking = 1
            </if>
            <if test="type == 3">
                and hotarea = 1
            </if>
            <if test="type == 4">
                and coldarea = 1
            </if>
        </where>
    </select>

    
</mapper>