<?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.yd.dal.mapper.practitioner.PractitionerHiringMapper">
    <resultMap id="HiringListInfo" type="com.yd.dal.entity.practitioner.hiring.HiringListInfo">
        <id column="hiringBasicInfoId" jdbcType="VARCHAR" property="hiringBasicInfoId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="mobileNo" jdbcType="VARCHAR" property="mobileNo"
                typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="practitionerLevel" jdbcType="VARCHAR" property="practitionerLevel"/>
        <result column="mentor" jdbcType="VARCHAR" property="mentor"/>
        <result column="applicationTime" jdbcType="VARCHAR" property="applicationTime"/>
        <result column="s1PractitionerId" jdbcType="BIGINT" property="s1PractitionerId"/>
        <result column="s2PractitionerId" jdbcType="BIGINT" property="s2PractitionerId"/>
        <result column="s3PractitionerId" jdbcType="BIGINT" property="s3PractitionerId"/>
    </resultMap>
    <select id="findHiringList" resultMap="HiringListInfo">
        select b.id                                                                       hiringBasicInfoId,
               b.name                                                                     name,
               b.mobile_no                                                                mobileNo,
               m.md_drop_option_name                                                      practitionerLevel,
               m.mentor                                                                   mentor,
               date_format(b.created_at, '%Y-%m-%d %H:%i:%s')                             applicationTime,
               if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT} and
                  m.subsystem_owner_id = #{practitionerId,jdbcType=BIGINT}, 0,
                  if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT}, 1, 2)) approvalIdentity
        from ag_acl_practitioner_hiring_basic_info b
        left join ag_acl_practitioner_hiring_membership m on b.id = m.hiring_basic_info_id
        left join
                 (
                    select
                    a.hiring_basic_info_id, a.approving_status, a.created_at
                    from
                    ag_acl_practitioner_hiring_approve_records a
                    left join
                    (
                    select
                    hiring_basic_info_id, max(created_at) created_at
                    from
                    ag_acl_practitioner_hiring_approve_records
                    group by hiring_basic_info_id
                    ) b
                    on a.hiring_basic_info_id=b.hiring_basic_info_id and a.created_at=b.created_at
                 ) r
                 on b.id = r.hiring_basic_info_id
        where (m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT} or
               m.subsystem_owner_id = #{practitionerId,jdbcType=BIGINT})
        <if test="approvingStatus != null">
            <choose>
                <when test='approvingStatus == "0" '>
                    and r.approving_status is null
                </when>
                <when test='approvingStatus == "1" '>
                    and r.approving_status = 1
                </when>
                <when test='approvingStatus == "2" '>
                    and r.approving_status = 0
                </when>
            </choose>
        </if>
          and m.md_drop_option_id != (select id
                                      from ag_md_drop_options
                                      where drop_option_code = 'S2'
                                        and drop_master_id = (select id
                                                              from ag_md_drop_master
                                                              where scenario_code = 'practitioner_level'))
    </select>

    <resultMap id="hiringBasicInfo" type="com.yd.dal.entity.practitioner.hiring.HiringBasicInfo">
        <result column="inviteePractitionerName" jdbcType="VARCHAR" property="inviteePractitionerName"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="mobileNo" jdbcType="VARCHAR" property="mobileNo"
                typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="idType" jdbcType="VARCHAR" property="idType"/>
        <result column="idNo" jdbcType="VARCHAR" property="idNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="gender" jdbcType="VARCHAR" property="gender"/>
        <result column="practitionerBirthdate" jdbcType="VARCHAR" property="practitionerBirthdate"/>
        <result column="provinceName" jdbcType="VARCHAR" property="provinceName"/>
        <result column="cityName" jdbcType="VARCHAR" property="cityName"/>
        <result column="residentAddress" jdbcType="VARCHAR" property="residentAddress"/>
        <result column="wechatId" jdbcType="VARCHAR" property="wechatId"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="personerPictureOssPath" jdbcType="VARCHAR" property="personerPictureOssPath"/>
        <result column="idFrontPageOssPath" jdbcType="VARCHAR" property="idFrontPageOssPath"/>
        <result column="idBackPageOssPath" jdbcType="VARCHAR" property="idBackPageOssPath"/>
        <result column="lastGraduateGrade" jdbcType="VARCHAR" property="lastGraduateGrade"/>
        <result column="lastGraduateSchool" jdbcType="VARCHAR" property="lastGraduateSchool"/>
        <result column="lastGraduateCertificationOssPath" jdbcType="VARCHAR"
                property="lastGraduateCertificationOssPath"/>
        <result column="bankAccountOpening" jdbcType="VARCHAR" property="bankAccountOpening"/>
        <result column="bankAccountId" jdbcType="VARCHAR" property="bankAccountId"/>
        <result column="personalSignOssPath" jdbcType="VARCHAR" property="personalSignOssPath"/>
        <result column="payroll_oss_path" jdbcType="VARCHAR" property="payrollOssPath" />
        <result column="approveStatus" jdbcType="VARCHAR" property="approveStatus" />
    </resultMap>
    <select id="findHiringBasicInfo" resultMap="hiringBasicInfo">
        select p.name                                                 inviteePractitionerName,
               b.approve_status                                       approveStatus,
               b.name                                                 name,
               b.mobile_no                                            mobileNo,
               b.id_type                                              idType,
               b.id_no                                                idNo,
               if(b.gender is null, null, if(b.gender = 1, '男', '女')) gender,
               DATE_FORMAT(b.practitioner_birthdate, '%Y%m%d')        practitionerBirthdate,
               b.province_name                                        provinceName,
               b.city_name                                            cityName,
               b.resident_address                                     residentAddress,
               b.wechat_id                                            wechatId,
               b.email                                                email,
               b.personer_picture_oss_path                            personerPictureOssPath,
               b.id_front_page_oss_path                               idFrontPageOssPath,
               b.id_back_page_oss_path                                idBackPageOssPath,
               b.last_graduate_grade                                  lastGraduateGrade,
               b.last_graduate_school                                 lastGraduateSchool,
               b.last_graduate_certification_oss_path                 lastGraduateCertificationOssPath,
               b.bank_account_opening                                 bankAccountOpening,
               b.bank_account_id                                      bankAccountId,
               b.payroll_oss_path                                     payrollOssPath,
               b.personal_sign_oss_path                               personalSignOssPath
        from ag_acl_practitioner_hiring_basic_info b
                     left join ag_acl_practitioner p on b.invitee_practitioner_id = p.id
        where b.id = #{hiringBasicInfoId,jdbcType=BIGINT}
          and b.is_active = 1
    </select>

    <select id="findHiringMemberShip"
            resultType="com.yd.dal.entity.practitioner.hiring.HiringMemberShip">
        select md_drop_option_name practitionerLevel,
               md_drop_option_id   practitionerLevelId,
               mentor              mentor,
               introducer          introducer,
               subsystem           subsystem,
               (select name from ag_acl_practitioner where id = approved_by)     subsystemOwner,
               branch              branch
        from ag_acl_practitioner_hiring_membership
        where hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
          and is_active = 1
    </select>

    <select id="findHiringWorkingExperienceList"
            resultType="com.yd.dal.entity.practitioner.hiring.HiringWorkingExperience">
        select working_company                      workingCompany,
               position                             position,
               DATE_FORMAT(working_start, '%Y%m%d') workingStart,
               DATE_FORMAT(working_end, '%Y%m%d')   workingEnd
        from ag_acl_practitioner_hiring_working_experience
        where is_active = 1
          and hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
        order by seq
    </select>

    <select id="findHiringPersonalStatementsList"
            resultType="com.yd.dal.entity.practitioner.hiring.HiringPersonalStatements">
        select md_drop_option_name                                personalStatements,
               if(status is null, null, if(status = 1, '有', '无')) status
        from ag_acl_practitioner_hiring_personal_statements
        where is_active = 1
          and hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
    </select>

    <select id="findHiringContractTermsConfirmsList"
            resultType="com.yd.dal.entity.practitioner.hiring.HiringContractTermsConfirms">
        select m.term_name                                                                contractTerm,
               if(c.confirm_status is null, null, if(c.confirm_status = 1, '已确认', '未确认')) confirmStatus
        from ag_acl_practitioner_hiring_contract_terms_confirms c
                     left join ag_md_practitioner_hiring_contract_terms m on c.contract_term_id = m.id
        where c.is_active = 1
          and hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
    </select>

    <select id="findHiringApproveRecordsList"
            resultType="com.yd.dal.entity.practitioner.hiring.HiringApproveRecords">
        select r.approving_practitioner_id                                                  approvingPractitionerId,
               p.name                                                                       approvingPractitionerName,
               r.approve_step_id                                                            approveStepId,
               r.approve_step_name                                                          approveStepName,
               r.approving_status                                                           approvingStatusId,
               if(r.approving_status is null, null, if(r.approving_status = 1, '通过', '驳回')) approvingStatus,
               interview_assessment                                                         interviewAssessment,
               r.reject_note                                                                rejectNote,
               date_format(r.created_at, '%Y-%m-%d %H:%i:%s')                               hiringAt
        from ag_acl_practitioner_hiring_approve_records r
                     left join ag_acl_practitioner p on p.id = r.approving_practitioner_id
        where r.is_active = 1
          and r.hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
        order by r.id
    </select>

    <resultMap id="hiringContractTerms" type="com.yd.api.practitioner.vo.hiring.PractitionerHiringContractTerms">
        <result column="id" jdbcType="BIGINT" property="id"/>
        <result column="term_name" jdbcType="VARCHAR" property="termName"/>
        <result column="term_note" jdbcType="VARCHAR" property="termNote"/>
        <result column="display_seq" jdbcType="INTEGER" property="displaySeq"/>
    </resultMap>
    <select id="queryContractTerms" resultMap="hiringContractTerms">
        select id, term_name, term_note, display_seq
        from  ag_md_practitioner_hiring_contract_terms c
        where c.is_active = 1 and c.system_type = '0' ORDER BY c.display_seq
    </select>

    <select id="queryHandledList" resultMap="HiringListInfo">
        select  b.id                                                                       hiringBasicInfoId,
                b.name                                                                     name,
                b.mobile_no                                                                mobileNo,
                m.md_drop_option_name                                                      practitionerLevel,
                m.mentor                                                                   mentor,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s')                             applicationTime,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT} and
                m.subsystem_owner_id = #{practitionerId,jdbcType=BIGINT}, 0,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT}, 1, 2))    approvalIdentity

        from ag_acl_practitioner_hiring_basic_info b
        left join ag_acl_practitioner_hiring_membership m on b.id = m.hiring_basic_info_id

        where 1=1
        and b.id in (select DISTINCT t.hiring_basic_info_id
                     from
		                ag_acl_practitioner_hiring_approve_records t
                     where t.approving_practitioner_id = #{practitionerId,jdbcType=BIGINT}
                        <if test="approvingStatus != null">
                            <choose>
                                <when test='approvingStatus == "1" '>
                                    and (b.approve_status >= t.approve_step_id OR b.approve_status = 0)
                                </when>
                                <when test='approvingStatus == "2" '>
                                    and ((b.approve_status != 0 and b.approve_status &lt; t.approve_step_id-1) or b.approve_status is null)
                                    and t.id &lt;= (select max(a.id) from ag_acl_practitioner_hiring_approve_records a where a.hiring_basic_info_id = t.hiring_basic_info_id and a.approving_status=0)
<!--                                     and (t.approving_status = 0 or b.approve_status < t.approve_step_id or b.approve_status is null) -->
                                </when>
                            </choose>
                        </if>
                    )
    </select>

    <select id="queryMentorUnhandledList" resultMap="HiringListInfo">
        select  b.id                                                                       hiringBasicInfoId,
                b.name                                                                     name,
                b.mobile_no                                                                mobileNo,
                m.md_drop_option_name                                                      practitionerLevel,
                m.mentor                                                                   mentor,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s')                             applicationTime,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT} and
                m.subsystem_owner_id = #{practitionerId,jdbcType=BIGINT}, 0,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT}, 1, 2))    approvalIdentity
        from ag_acl_practitioner_hiring_basic_info b
        left join ag_acl_practitioner_hiring_membership m on b.id = m.hiring_basic_info_id
        where 1=1
        and m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT}
        and b.approve_status = '-2'
        and m.md_drop_option_id != (select id
                                    from ag_md_drop_options
                                    where drop_option_code = 'S2'
                                      and drop_master_id = (select id
                                                            from ag_md_drop_master
                                                            where scenario_code = 'practitioner_level'))
    </select>

    <select id="querySystemOwnerUnhandledList" resultMap="HiringListInfo">
        select  b.id                                                                       hiringBasicInfoId,
                b.name                                                                     name,
                b.mobile_no                                                                mobileNo,
                m.md_drop_option_name                                                      practitionerLevel,
                m.mentor                                                                   mentor,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s')                             applicationTime,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT} and
                m.subsystem_owner_id = #{practitionerId,jdbcType=BIGINT}, 0,
                if(m.mentor_practitioner_id = #{practitionerId,jdbcType=BIGINT}, 1, 2))    approvalIdentity
        from ag_acl_practitioner_hiring_basic_info b
        left join ag_acl_practitioner_hiring_membership m on b.id = m.hiring_basic_info_id
        where 1=1
        and m.approved_by = #{practitionerId,jdbcType=BIGINT}
        and b.approve_status = '1'
        and m.md_drop_option_id != (select id
                                    from ag_md_drop_options
                                    where drop_option_code = 'S2'
                                      and drop_master_id = (select id
                                                            from ag_md_drop_master
                                                            where scenario_code = 'practitioner_level'))
    </select>

    <select id="getNextContractNo" resultType="java.lang.String" statementType="CALLABLE">
        select f_getNextContractNo()
    </select>

    <select id="queryS1S2S3" resultType="Map">
        SELECT
            (select owner_practitioner_id from ag_acl_practitioner_subordinate_system where id=p.s1_id) as s1PractitionerId,
            (select owner_practitioner_id from ag_acl_practitioner_subordinate_system where id=p.s2_id) as s2PractitionerId,
            (select owner_practitioner_id from ag_acl_practitioner_subordinate_system where id=p.s3_id) as s3PractitionerId
        from ag_acl_practitioner p
        where p.id = #{practitionerId,jdbcType=BIGINT}
    </select>

    <select id="queryBasicInfoIdList" resultType="java.lang.Long">
        select  b.id
        from ag_acl_practitioner_hiring_basic_info b
        where 1=1
        and b.id in <foreach collection="list" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
        and b.approve_status = '1'
    </select>
</mapper>