<?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.sems.mapper.TReportYlgdMapper">

    <resultMap type="TReportYlgd" id="TReportYlgdResult">
        <result property="id" column="id"/>
        <result property="useno" column="useno"/>
        <result property="regno" column="regno"/>
        <result property="devId" column="dev_id"/>
        <result property="plantCode" column="plant_code"/>
        <result property="unit" column="unit"/>
        <result property="devname" column="devname"/>
        <result property="devno" column="devno"/>
        <result property="grade" column="grade"/>
        <result property="medium" column="medium"/>
        <result property="desPressure" column="des_pressure"/>
        <result property="desTemp" column="des_temp"/>
        <result property="optPressure" column="opt_pressure"/>
        <result property="optTemp" column="opt_temp"/>
        <result property="safeClass" column="safe_class"/>
        <result property="nextWarnDate" column="next_warn_date"/>
        <result property="yearReportNo" column="year_report_no"/>
        <result property="pj1" column="pj1"/>
        <result property="pj2" column="pj2"/>
        <result property="pj3" column="pj3"/>
        <result property="pj4" column="pj4"/>
        <result property="pj5" column="pj5"/>
        <result property="pj6" column="pj6"/>
        <result property="pj7" column="pj7"/>
        <result property="pj8" column="pj8"/>
        <result property="pj9" column="pj9"/>
        <result property="pj10" column="pj10"/>
        <result property="pj11" column="pj11"/>
        <result property="pj12" column="pj12"/>
        <result property="pj13" column="pj13"/>
        <result property="pj14" column="pj14"/>
        <result property="pj15" column="pj15"/>
        <result property="pj16" column="pj16"/>
        <result property="pj17" column="pj17"/>
        <result property="remarks" column="remarks"/>
        <result property="checkDate" column="check_date"/>
        <result property="nextCheckDate" column="next_check_date"/>
        <result property="problem" column="problem"/>
        <result property="con" column="con"/>
        <result property="approveStatus" column="approve_status"/>
        <result property="approveTime" column="approve_time"/>
        <result property="deptId" column="dept_id"/>
        <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="deptName" column="dept_name"/>
    </resultMap>

    <sql id="selectTReportYlgdVo">
        select d.id,
               d.dev_id,
               y.plant_code,
               y.unit,
               y.devname,
               y.devno,
               y.grade,
               y.USENO,
               y.REGNO,
               d.medium,
               d.des_pressure,
               d.des_temp,
               d.opt_pressure,
               d.opt_temp,
               y.safe_class,
               y.next_warn_date,
               d.year_report_no,
               d.pj1,
               d.pj2,
               d.pj3,
               d.pj4,
               d.pj5,
               d.pj6,
               d.pj7,
               d.pj8,
               d.pj9,
               d.pj10,
               d.pj11,
               d.pj12,
               d.pj13,
               d.pj14,
               d.pj15,
               d.pj16,
               d.pj17,
               d.remarks,
               d.check_date,
               d.next_check_date,
               d.problem,
               d.con,
               d.approve_status,
               d.approve_time,
               d.dept_id,
               d.del_flag,
               d.creater_code,
               d.createdate,
               d.updater_code,
               d.updatedate,
               s.dept_name
        from t_report_ylgd d
                 left join sys_dept s on s.dept_id = d.dept_id
                 left join t_specdev_ylgd y on y.id = d.dev_id
    </sql>

    <select id="selectTReportYlgdList" parameterType="TReportYlgd" resultMap="TReportYlgdResult">
        <include refid="selectTReportYlgdVo"/>
        <where>
            <if test="plantCode != null  and plantCode != ''">and y.plant_code like concat(concat('%', #{plantCode}),
                '%')
            </if>
            <if test="approveStatus != null  and approveStatus != '' or approveStatus== 0">and d.approve_status =
                #{approveStatus}
            </if>
            <if test="unit != null and unit != ''">and y.unit like concat(concat('%', #{unit}), '%')</if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScopePlant}
        order by y.plant_code ,y.unit
    </select>

    <select id="queryNeedInsertIds" resultType="Long">
        SELECT b.id
        FROM t_specdev_ylgd b
        where b.del_flag = 0
          and b.status = 1
            MINUS
        SELECT a.id
        FROM t_report_ylgd a
    </select>

    <update id="updateForImport">
        <selectKey keyProperty='id' resultType='Long' order='BEFORE'>
            SELECT
            (select id
            from t_specdev_ylgd
            where devno = #{devno}
            and useno = #{useno}
            and regno = #{regno}
            and rownum=1 and del_flag = 0
            ) id
            from DUAL
        </selectKey>
        update t_report_ylgd
        <trim prefix="SET" suffixOverrides=",">
            <if test="pj1 != null">pj1 = #{pj1},</if>
            <if test="pj2 != null">pj2 = #{pj2},</if>
            <if test="pj3 != null">pj3 = #{pj3},</if>
            <if test="pj4 != null">pj4 = #{pj4},</if>
            <if test="pj5 != null">pj5 = #{pj5},</if>
            <if test="pj6 != null">pj6 = #{pj6},</if>
            <if test="pj7 != null">pj7 = #{pj7},</if>
            <if test="pj8 != null">pj8 = #{pj8},</if>
            <if test="pj9 != null">pj9 = #{pj9},</if>
            <if test="pj10 != null">pj10 = #{pj10},</if>
            <if test="pj11 != null">pj11 = #{pj11},</if>
            <if test="pj12 != null">pj12 = #{pj12},</if>
            <if test="pj13 != null">pj13 = #{pj13},</if>
            <if test="pj14 != null">pj14 = #{pj14},</if>
            <if test="pj15 != null">pj15 = #{pj15},</if>
            <if test="pj16 != null">pj16 = #{pj16},</if>
            <if test="pj17 != null">pj17 = #{pj17},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="checkDate != null">check_date = #{checkDate},</if>
            <if test="nextCheckDate != null">next_check_date = #{nextCheckDate},</if>
            <if test="problem != null">problem = #{problem},</if>
            <if test="con != null">con = #{con},</if>
            <if test="yearReportNo != null">year_report_no = #{yearReportNo},</if>
        </trim>
        <!--2023年1月5日修改-->
        <!--由于部分t_report_ylgd.dev_id与t_specdev_ylgd.id不一致，导致update失效-->
        <!--因此，取消del_flag过滤条件，改为根据所有符合条件的dev_id修改t_report_ylgd-->
        where dev_id in (
        select id
        from t_specdev_ylgd
        where devno = #{devno}
        and useno = #{useno}
        and regno = #{regno}
        and del_flag = 0
        )
        <!-- where dev_id = ( -->
        <!-- select id -->
        <!-- from t_specdev_ylgd -->
        <!-- where devno = #{devno} -->
        <!-- and useno = #{useno} -->
        <!-- and regno = #{regno} -->
        <!-- and del_flag = 0 -->
        <!-- ) -->
    </update>

    <select id="queryNeedUpdateIds" resultType="Long">
        SELECT b.id
        FROM t_specdev_ylgd b
        WHERE b.del_flag = 0
          and b.status = 1
        INTERSECT
        SELECT a.id
        FROM t_report_ylgd a
    </select>


    <select id="queryNeedDeleteIds" resultType="Long">
        SELECT a.id
        FROM t_report_ylgd a
        where a.del_flag = 0
            MINUS
        SELECT b.id
        FROM t_specdev_ylgd b
        where b.del_flag = 0
          and b.status = 1
    </select>

    <!-- 新增同步台账下数据 -->
    <insert id="insertByTSpecdevYlgd">
        insert into t_report_ylgd
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="id != null">dev_id,</if>
            <if test="medium != null">medium,</if>
            <if test="desPressure != null">des_pressure,</if>
            <if test="desTemp != null">des_temp,</if>
            <if test="optPressure != null">opt_pressure,</if>
            <if test="optTemp != null">opt_temp,</if>
            <if test="yearReportNo != null">year_report_no,</if>
            <if test="remarks != null">remarks,</if>
            <if test="deptId != null">dept_id,</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="yearWarnDate != null">check_date,</if>
            <if test="yearNextWarnDate != null">next_check_date,</if>
            pj1,pj2,pj3,pj4,pj5,pj6,pj7,pj8,pj9,pj10,pj11,pj12,pj13,con
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="id != null">#{id},</if>
            <if test="medium != null">#{medium},</if>
            <if test="desPressure != null">#{desPressure},</if>
            <if test="desTemp != null">#{desTemp},</if>
            <if test="optPressure != null">#{optPressure},</if>
            <if test="optTemp != null">#{optTemp},</if>
            <if test="yearReportNo != null">#{yearReportNo},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="deptId != null">#{deptId},</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="yearWarnDate != null">#{yearWarnDate},</if>
            <if test="yearNextWarnDate != null">#{yearNextWarnDate},</if>
            <![CDATA[
            '√','√','√','√','√','√','√','√','√','√','√','√','√','1']]>
        </trim>
    </insert>

    <update id="updateByTSpecdevYlgd">
        update t_report_ylgd
        <trim prefix="SET" suffixOverrides=",">
            <if test="id != null">dev_id = #{id},</if>
            <if test="medium != null">medium = #{medium},</if>
            <if test="desPressure != null">des_pressure = #{desPressure},</if>
            <if test="desTemp != null">des_temp = #{desTemp},</if>
            <if test="optPressure != null">opt_pressure = #{optPressure},</if>
            <if test="optTemp != null">opt_temp = #{optTemp},</if>
            <if test="yearReportNo != null">year_report_no = #{yearReportNo},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="deptId != null">dept_id = #{deptId},</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="yearWarnDate != null">check_date = #{yearWarnDate},</if>
            <if test="yearNextWarnDate != null">next_check_date = #{yearNextWarnDate},</if>
        </trim>
        where id = #{id}
    </update>

    <select id="selectTReportYlgdById" parameterType="Long" resultMap="TReportYlgdResult">
        <include refid="selectTReportYlgdVo"/>
        where d.id = #{id}
    </select>

    <insert id="insertTReportYlgd" parameterType="TReportYlgd">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT seq_t_report_ylgd.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_report_ylgd
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="devId != null">dev_id,</if>
            <if test="medium != null">medium,</if>
            <if test="desPressure != null">des_pressure,</if>
            <if test="desTemp != null">des_temp,</if>
            <if test="optPressure != null">opt_pressure,</if>
            <if test="optTemp != null">opt_temp,</if>
            <if test="yearReportNo != null">year_report_no,</if>
            <if test="pj1 != null">pj1,</if>
            <if test="pj2 != null">pj2,</if>
            <if test="pj3 != null">pj3,</if>
            <if test="pj4 != null">pj4,</if>
            <if test="pj5 != null">pj5,</if>
            <if test="pj6 != null">pj6,</if>
            <if test="pj7 != null">pj7,</if>
            <if test="pj8 != null">pj8,</if>
            <if test="pj9 != null">pj9,</if>
            <if test="pj10 != null">pj10,</if>
            <if test="pj11 != null">pj11,</if>
            <if test="pj12 != null">pj12,</if>
            <if test="pj13 != null">pj13,</if>
            <if test="pj14 != null">pj14,</if>
            <if test="pj15 != null">pj15,</if>
            <if test="pj16 != null">pj16,</if>
            <if test="pj17 != null">pj17,</if>
            <if test="remarks != null">remarks,</if>
            <if test="checkDate != null">check_date,</if>
            <if test="nextCheckDate != null">next_check_date,</if>
            <if test="problem != null">problem,</if>
            <if test="con != null">con,</if>
            <if test="approveStatus != null">approve_status,</if>
            <if test="approveTime != null">approve_time,</if>
            <if test="deptId != null">dept_id,</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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="devId != null">#{devId},</if>
            <if test="medium != null">#{medium},</if>
            <if test="desPressure != null">#{desPressure},</if>
            <if test="desTemp != null">#{desTemp},</if>
            <if test="optPressure != null">#{optPressure},</if>
            <if test="optTemp != null">#{optTemp},</if>
            <if test="yearReportNo != null">#{yearReportNo},</if>
            <if test="pj1 != null">#{pj1},</if>
            <if test="pj2 != null">#{pj2},</if>
            <if test="pj3 != null">#{pj3},</if>
            <if test="pj4 != null">#{pj4},</if>
            <if test="pj5 != null">#{pj5},</if>
            <if test="pj6 != null">#{pj6},</if>
            <if test="pj7 != null">#{pj7},</if>
            <if test="pj8 != null">#{pj8},</if>
            <if test="pj9 != null">#{pj9},</if>
            <if test="pj10 != null">#{pj10},</if>
            <if test="pj11 != null">#{pj11},</if>
            <if test="pj12 != null">#{pj12},</if>
            <if test="pj13 != null">#{pj13},</if>
            <if test="pj14 != null">#{pj14},</if>
            <if test="pj15 != null">#{pj15},</if>
            <if test="pj16 != null">#{pj16},</if>
            <if test="pj17 != null">#{pj17},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="checkDate != null">#{checkDate},</if>
            <if test="nextCheckDate != null">#{nextCheckDate},</if>
            <if test="problem != null">#{problem},</if>
            <if test="con != null">#{con},</if>
            <if test="approveStatus != null">#{approveStatus},</if>
            <if test="approveTime != null">#{approveTime},</if>
            <if test="deptId != null">#{deptId},</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>
        </trim>
    </insert>

    <update id="updateTReportYlgd" parameterType="TReportYlgd">
        update t_report_ylgd
        <trim prefix="SET" suffixOverrides=",">
            <if test="devId != null">dev_id = #{devId},</if>
            <if test="medium != null">medium = #{medium},</if>
            <if test="desPressure != null">des_pressure = #{desPressure},</if>
            <if test="desTemp != null">des_temp = #{desTemp},</if>
            <if test="optPressure != null">opt_pressure = #{optPressure},</if>
            <if test="optTemp != null">opt_temp = #{optTemp},</if>
            <if test="yearReportNo != null">year_report_no = #{yearReportNo},</if>
            <if test="pj1 != null">pj1 = #{pj1},</if>
            <if test="pj2 != null">pj2 = #{pj2},</if>
            <if test="pj3 != null">pj3 = #{pj3},</if>
            <if test="pj4 != null">pj4 = #{pj4},</if>
            <if test="pj5 != null">pj5 = #{pj5},</if>
            <if test="pj6 != null">pj6 = #{pj6},</if>
            <if test="pj7 != null">pj7 = #{pj7},</if>
            <if test="pj8 != null">pj8 = #{pj8},</if>
            <if test="pj9 != null">pj9 = #{pj9},</if>
            <if test="pj10 != null">pj10 = #{pj10},</if>
            <if test="pj11 != null">pj11 = #{pj11},</if>
            <if test="pj12 != null">pj12 = #{pj12},</if>
            <if test="pj13 != null">pj13 = #{pj13},</if>
            <if test="pj14 != null">pj14 = #{pj14},</if>
            <if test="pj15 != null">pj15 = #{pj15},</if>
            <if test="pj16 != null">pj16 = #{pj16},</if>
            <if test="pj17 != null">pj17 = #{pj17},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="checkDate != null">check_date = #{checkDate},</if>
            <if test="nextCheckDate != null">next_check_date = #{nextCheckDate},</if>
            <if test="problem != null">problem = #{problem},</if>
            <if test="con != null">con = #{con},</if>
            <if test="approveStatus != null">approve_status = #{approveStatus},</if>
            <if test="approveTime != null">approve_time = #{approveTime},</if>
            <if test="deptId != null">dept_id = #{deptId},</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>
        </trim>
        where id = #{id}
    </update>


    <update id="sysnReport" parameterType="TReportYlgd">
        update t_specdev_ylgd
        <trim prefix="SET" suffixOverrides=",">
            <if test="yearReportNo != null">year_report_no = #{yearReportNo},</if>
            <if test="checkDate != null">year_warn_date = #{checkDate},</if>
            <if test="nextCheckDate != null">year_next_warn_date = #{nextCheckDate},</if>
            <if test="con != null">check_conclusion = #{con},</if>
        </trim>
        where id = #{id}
    </update>

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

    <delete id="deleteTReportYlgdByIds" parameterType="String">
        delete from t_report_ylgd where id in
        <foreach item="id" collection="array" open="(" close=")" index="index">
            <if test="index != 0">
                <choose>
                    <when test="index % 1000 == 999">) OR id in (</when>
                    <otherwise>,</otherwise>
                </choose>
            </if>
            #{id}
        </foreach>
    </delete>

</mapper>
