<?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.spec.mapper.TStFeedbackMapper">
    
    <resultMap type="TStFeedback" id="TStFeedbackResult">
        <result property="id"    column="id"    />
        <result property="feedbackType"    column="feedback_type"    />
        <result property="mentorId"    column="mentor_id"    />
        <result property="mentorName"    column="mentor_name"    />
        <result property="successorId"    column="successor_id"    />
        <result property="successorName"    column="successor_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="planId"    column="plan_id"    />
        <result property="feedbackYear"    column="feedback_year"    />
        <result property="feedbackSeason"    column="feedback_season"    />
        <result property="feedbackMonth"    column="feedback_month"    />
        <result property="successorFeedback"    column="successor_feedback"    />
        <result property="mentorFeedback"    column="mentor_feedback"    />
        <result property="feedbackScore"    column="feedback_score"    />
        <result property="overallScore"    column="overall_score"    />
        <result property="feedbackStatus"    column="feedback_status"    />
        <result property="meetingDate"    column="meeting_date"    />
        <result property="deptName" column="dept_name" />
    </resultMap>

    <sql id="selectTStFeedbackVo">
        select
            d.id,
            d.feedback_type,
            d.mentor_id,
            (select nick_name from sys_user t where t.staffid = d.mentor_id) as mentor_name,
            d.successor_id,
            u.nick_name as successor_name,
            d.parent_id,
            d.plan_id,
            d.feedback_year,
            d.feedback_season,
            d.feedback_month,
            d.successor_feedback,
            d.mentor_feedback,
            d.feedback_score,
            d.overall_score,
            d.feedback_status,
            d.meeting_date
        from
            t_st_feedback d
        left join sys_user u on d.successor_id = u.staffid
        left join T_STAFFMGR s on d.SUCCESSOR_ID = s.staffid
    </sql>

    <select id="selectInvitedSuccessorList" parameterType="TStFeedback" resultMap="TStFeedbackResult">
        <include refid="selectTStFeedbackVo"/>
        <where>
            <if test="mentorId != null  and mentorId != ''"> and mentor_id = #{mentorId}</if>
            and parent_id is not null
            and d.del_flag = 0
            and s.del_flag != 9
        </where>
    </select>

    <select id="selectTStFeedbackList" parameterType="TStFeedback" resultMap="TStFeedbackResult">
        <include refid="selectTStFeedbackVo"/>
        <where>  
            <if test="feedbackType != null "> and feedback_type = #{feedbackType}</if>
            <if test="mentorId != null  and mentorId != ''"> and mentor_id = #{mentorId}</if>
            <if test="successorId != null  and successorId != ''"> and successor_id = #{successorId}</if>
            <if test="parentId != null "> and parent_id = #{parentId}</if>
            <if test="planId != null "> and plan_id = #{planId}</if>
            <if test="feedbackYear != null  and feedbackYear != ''"> and feedback_year = #{feedbackYear}</if>
            <if test="feedbackSeason != null  and feedbackSeason != ''"> and feedback_season = #{feedbackSeason}</if>
            <if test="feedbackMonth != null  and feedbackMonth != ''"> and feedback_month = #{feedbackMonth}</if>
            <if test="successorFeedback != null  and successorFeedback != ''"> and successor_feedback = #{successorFeedback}</if>
            <if test="mentorFeedback != null  and mentorFeedback != ''"> and mentor_feedback = #{mentorFeedback}</if>
            <if test="feedbackScore != null  and feedbackScore != ''"> and feedback_score = #{feedbackScore}</if>
            <if test="feedbackStatus != null "> and feedback_status = #{feedbackStatus}</if>
            <if test="meetingDate != null "> and meeting_date = #{meetingDate}</if>
            and d.del_flag = 0
        </where>
        order by d.successor_id asc, d.feedback_year desc, d.feedback_season,
        to_number(replace(d.feedback_month,',','')) asc
    </select>

    <select id="selectTStFeedbackByParams" parameterType="TStFeedback" resultMap="TStFeedbackResult">
        <include refid="selectTStFeedbackVo"/>
        <where>
            <if test="feedbackType != null "> and feedback_type = #{feedbackType}</if>
            <if test="mentorId != null  and mentorId != ''"> and mentor_id = #{mentorId}</if>
            <if test="successorId != null  and successorId != ''"> and successor_id = #{successorId}</if>
            <if test="parentId != null "> and parent_id = #{parentId}</if>
            <if test="planId != null "> and plan_id = #{planId}</if>
            <if test="feedbackYear != null  and feedbackYear != ''"> and feedback_year = #{feedbackYear}</if>
            <if test="feedbackSeason != null  and feedbackSeason != ''"> and feedback_season = #{feedbackSeason}</if>
            <if test="feedbackMonth != null  and feedbackMonth != ''"> and feedback_month = #{feedbackMonth}</if>
            <if test="successorFeedback != null  and successorFeedback != ''"> and successor_feedback = #{successorFeedback}</if>
            <if test="mentorFeedback != null  and mentorFeedback != ''"> and mentor_feedback = #{mentorFeedback}</if>
            <if test="feedbackScore != null  and feedbackScore != ''"> and feedback_score = #{feedbackScore}</if>
            <if test="feedbackStatus != null "> and feedback_status = #{feedbackStatus}</if>
            <if test="meetingDate != null "> and meeting_date = #{meetingDate}</if>
            <if test="parentId != null and parentId !=''"> and parent_id = #{parentId} </if>
            <if test="parentId == null or parentId == ''"> and parent_id is null </if>
            and d.del_flag = 0
        </where>
    </select>

    <select id="selectTStFeedbackByPlanId" parameterType="Long" resultMap="TStFeedbackResult">
        <include refid="selectTStFeedbackVo"/>
        where plan_id = #{planId}
        and d.del_flag = 0
    </select>

    <select id="selectTStFeedbackById" parameterType="Long" resultMap="TStFeedbackResult">
        <include refid="selectTStFeedbackVo"/>
        where d.id = #{id}
        and d.del_flag = 0
    </select>
        
    <insert id="insertTStFeedback" parameterType="TStFeedback">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT seq_t_st_feedback.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_st_feedback
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="feedbackType != null">feedback_type,</if>
            <if test="mentorId != null">mentor_id,</if>
            <if test="successorId != null">successor_id,</if>
            <if test="parentId != null">parent_id,</if>
            <if test="planId != null">plan_id,</if>
            <if test="feedbackYear != null">feedback_year,</if>
            <if test="feedbackSeason != null">feedback_season,</if>
            <if test="feedbackMonth != null">feedback_month,</if>
            <if test="successorFeedback != null">successor_feedback,</if>
            <if test="mentorFeedback != null">mentor_feedback,</if>
            <if test="feedbackScore != null">feedback_score,</if>
            <if test="feedbackStatus != null">feedback_status,</if>
            <if test="meetingDate != null">meeting_date,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="feedbackType != null">#{feedbackType},</if>
            <if test="mentorId != null">#{mentorId},</if>
            <if test="successorId != null">#{successorId},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="planId != null">#{planId},</if>
            <if test="feedbackYear != null">#{feedbackYear},</if>
            <if test="feedbackSeason != null">#{feedbackSeason},</if>
            <if test="feedbackMonth != null">#{feedbackMonth},</if>
            <if test="successorFeedback != null">#{successorFeedback},</if>
            <if test="mentorFeedback != null">#{mentorFeedback},</if>
            <if test="feedbackScore != null">#{feedbackScore},</if>
            <if test="feedbackStatus != null">#{feedbackStatus},</if>
            <if test="meetingDate != null">#{meetingDate},</if>
         </trim>
    </insert>

    <update id="updateTStFeedback" parameterType="TStFeedback">
        update t_st_feedback
        <trim prefix="SET" suffixOverrides=",">
            <if test="feedbackType != null">feedback_type = #{feedbackType},</if>
            <if test="mentorId != null">mentor_id = #{mentorId},</if>
            <if test="successorId != null">successor_id = #{successorId},</if>
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="planId != null">plan_id = #{planId},</if>
            <if test="feedbackYear != null">feedback_year = #{feedbackYear},</if>
            <if test="feedbackSeason != null">feedback_season = #{feedbackSeason},</if>
            <if test="feedbackMonth != null">feedback_month = #{feedbackMonth},</if>
            <if test="successorFeedback != null">successor_feedback = #{successorFeedback},</if>
            <if test="mentorFeedback != null">mentor_feedback = #{mentorFeedback},</if>
            <if test="feedbackScore != null">feedback_score = #{feedbackScore},</if>
            <if test="overallScore != null and overallScore != ''">overall_score = #{overallScore},</if>
            <if test="feedbackStatus != null">feedback_status = #{feedbackStatus},</if>
            <if test="meetingDate != null">meeting_date = #{meetingDate},</if>
        </trim>
        where id = #{id}
    </update>

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

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