<?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.fs.doctor.mapper.DoctorLevelApplyMapper">
    
    <resultMap type="DoctorLevelApply" id="DoctorLevelApplyResult">
        <result property="id"    column="id"    />
        <result property="doctorId"    column="doctor_id"    />
        <result property="doctorName"              column="doctor_name" />
        <result property="companyId"    column="company_id"    />
        <result property="companyName"    column="company_name"    />
        <result property="academicTitle"    column="academic_title"    />
        <result property="academicPosition"    column="academic_position"    />
        <result property="internationalPapers"    column="international_papers"    />
        <result property="nationalPapers"    column="national_papers"    />
        <result property="academicResearch"    column="academic_research"    />
        <result property="clinicalExperienceYears"    column="clinical_experience_years"    />
        <result property="degree"    column="degree"    />
        <result property="applyLevel"    column="apply_level"    />
        <result property="status"    column="status"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleted"    column="deleted"    />
        <result property="auditInstanceId"    column="audit_instance_id"    />
        <result property="formId"            column="form_id" />   <!-- ✅ 新增 -->

    </resultMap>
    <sql id="selectDoctorLevelApplyVo">
        select
        a.id,
        a.doctor_id,
        d.doctor_name        as doctor_name,
        a.audit_instance_id,
        a.company_id,
        c.company_name       as company_name,
        a.academic_title,
        a.academic_position,
        a.international_papers,
        a.national_papers,
        a.academic_research,
        a.clinical_experience_years,
        a.degree,
        a.apply_level,
        a.status,
        a.create_time,
        a.update_time,
        a.form_id,                    -- ✅ 新增
        a.deleted
        from doctor_level_apply a
        left join company c
        on c.company_id = a.company_id
        left join doctor d
        on d.doctor_id = a.doctor_id
    </sql>



    <select id="selectDoctorLevelApplyList" parameterType="DoctorLevelApply" resultMap="DoctorLevelApplyResult">
        <include refid="selectDoctorLevelApplyVo"/>
        <where>
            <if test="doctorId != null and doctorId != ''"> and a.doctor_id = #{doctorId}</if>
            <if test="companyId != null"> and a.company_id = #{companyId}</if>
            <if test="academicTitle != null and academicTitle != ''"> and a.academic_title = #{academicTitle}</if>
            <if test="academicPosition != null and academicPosition != ''"> and a.academic_position = #{academicPosition}</if>
            <if test="internationalPapers != null"> and a.international_papers = #{internationalPapers}</if>
            <if test="nationalPapers != null"> and a.national_papers = #{nationalPapers}</if>
            <if test="academicResearch != null and academicResearch != ''"> and a.academic_research = #{academicResearch}</if>
            <if test="clinicalExperienceYears != null"> and a.clinical_experience_years = #{clinicalExperienceYears}</if>
            <if test="degree != null and degree != ''"> and a.degree = #{degree}</if>
            <if test="applyLevel != null and applyLevel != ''"> and a.apply_level = #{applyLevel}</if>
            <if test="status != null"> and a.status = #{status}</if>
            <if test="deleted != null"> and a.deleted = #{deleted}</if>
        </where>
        order by a.create_time desc

    </select>

    <select id="selectDoctorLevelApplyById" parameterType="Long" resultMap="DoctorLevelApplyResult">
        <include refid="selectDoctorLevelApplyVo"/>
        where a.id = #{id}
    </select>
        
    <insert id="insertDoctorLevelApply" parameterType="DoctorLevelApply" useGeneratedKeys="true" keyProperty="id">
        insert into doctor_level_apply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id,</if>
            <if test="companyId != null">company_id,</if>
            <if test="academicTitle != null">academic_title,</if>
            <if test="academicPosition != null">academic_position,</if>
            <if test="internationalPapers != null">international_papers,</if>
            <if test="nationalPapers != null">national_papers,</if>
            <if test="academicResearch != null">academic_research,</if>
            <if test="clinicalExperienceYears != null">clinical_experience_years,</if>
            <if test="degree != null">degree,</if>
            <if test="applyLevel != null">apply_level,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleted != null">deleted,</if>
            <if test="auditInstanceId != null">audit_instance_id,</if>
            <if test="formId != null">form_id,</if>          <!-- ✅ 新增 -->

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">#{doctorId},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="academicTitle != null">#{academicTitle},</if>
            <if test="academicPosition != null">#{academicPosition},</if>
            <if test="internationalPapers != null">#{internationalPapers},</if>
            <if test="nationalPapers != null">#{nationalPapers},</if>
            <if test="academicResearch != null">#{academicResearch},</if>
            <if test="clinicalExperienceYears != null">#{clinicalExperienceYears},</if>
            <if test="degree != null">#{degree},</if>
            <if test="applyLevel != null">#{applyLevel},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="auditInstanceId != null">#{auditInstanceId},</if>
            <if test="formId != null">#{formId},</if>        <!-- ✅ 新增 -->
        </trim>
    </insert>

    <update id="updateDoctorLevelApply" parameterType="DoctorLevelApply">
        update doctor_level_apply
        <trim prefix="SET" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id = #{doctorId},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="academicTitle != null">academic_title = #{academicTitle},</if>
            <if test="academicPosition != null">academic_position = #{academicPosition},</if>
            <if test="internationalPapers != null">international_papers = #{internationalPapers},</if>
            <if test="nationalPapers != null">national_papers = #{nationalPapers},</if>
            <if test="academicResearch != null">academic_research = #{academicResearch},</if>
            <if test="clinicalExperienceYears != null">clinical_experience_years = #{clinicalExperienceYears},</if>
            <if test="degree != null">degree = #{degree},</if>
            <if test="applyLevel != null">apply_level = #{applyLevel},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
            <if test="auditInstanceId != null">audit_instance_id = #{auditInstanceId},</if>
            <if test="formId != null">form_id = #{formId},</if>   <!-- ✅ 新增 -->

        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDoctorLevelApplyById" parameterType="Long">
        delete from doctor_level_apply where id = #{id}
    </delete>

    <delete id="deleteDoctorLevelApplyByIds" parameterType="String">
        delete from doctor_level_apply where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 在 DoctorLevelApplyMapper.xml 中 -->
    <select id="checkPendingApplyCount" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM doctor_level_apply
        WHERE doctor_id = #{doctorId}
        AND status = 0
        AND deleted = 0
    </select>




</mapper>