<?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.agms.AgmsHiringMapper">
    <resultMap id="practitionerHiringBasicInfo" type="com.yd.dal.entity.agms.hiring.PractitionerHiringBasicInfo">
        <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="politicalOutlook" jdbcType="VARCHAR" property="politicalOutlook"/>
        <result column="residentAddress" jdbcType="VARCHAR" property="residentAddress"/>
        <result column="wechatId" jdbcType="VARCHAR" property="wechatId"/>
        <result column="wechatQRcodeOssPath" jdbcType="VARCHAR" property="wechatQRcodeOssPath"/>
        <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="payrollOssPath" jdbcType="VARCHAR" property="payrollOssPath" />
        <result column="approveStatus" jdbcType="VARCHAR" property="approveStatus" />
    </resultMap>
    <select id="findPractitionerHiringBasicInfo" resultMap="practitionerHiringBasicInfo">
        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.political_outlook                                    politicalOutlook,
               b.resident_address                                     residentAddress,
               b.wechat_id                                            wechatId,
               b.wechat_QRcode_oss_path                               wechatQRcodeOssPath,
               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="findPractitionerHiringMemberShip"
            resultType="com.yd.dal.entity.agms.hiring.PractitionerHiringMemberShip">
        select md_drop_option_name practitionerLevel,
               md_drop_option_id   practitionerLevelId,
               mentor              mentor,
               introducer          introducer,
               subsystem           subsystem,
               subsystem_owner     subsystemOwner,
               branch              branch
        from ag_acl_practitioner_hiring_membership
        where hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT}
          and is_active = 1
    </select>

    <select id="findPractitionerHiringWorkingExperienceList"
            resultType="com.yd.dal.entity.agms.hiring.PractitionerHiringWorkingExperience">
        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="findPractitionerHiringPersonalStatementsList"
            resultType="com.yd.dal.entity.agms.hiring.PractitionerHiringPersonalStatements">
        select md_drop_option_name                                personalStatements,
               user_input userInput,
               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="findPractitionerHiringContractTermsConfirmsList"
            resultType="com.yd.dal.entity.agms.hiring.PractitionerHiringContractTermsConfirms">
        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="findPractitionerHiringApproveRecordsList"
            resultType="com.yd.dal.entity.agms.hiring.PractitionerHiringApproveRecords">
        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>

    <select id="findPractitionerIdByLoginId" resultType="java.lang.Long">
        select p.id
        from ag_acl_practitioner p
                     left join ag_acl_user u on u.mobile_no = p.mobile_no
        where u.id = #{loginId,jdbcType=BIGINT}
    </select>
    <resultMap id="practitionerHiringListInfo" type="com.yd.dal.entity.agms.hiring.PractitionerHiringListInfo">
        <id column="hiringBasicInfoId" jdbcType="BIGINT" 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="introducer" jdbcType="VARCHAR" property="introducer"/>
        <result column="subsystem" jdbcType="VARCHAR" property="subsystem"/>
        <result column="branch" jdbcType="VARCHAR" property="branch"/>
        <result column="applicationTime" jdbcType="VARCHAR" property="applicationTime"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="approvingTime" jdbcType="VARCHAR" property="approvingTime"/>
    </resultMap>
    <select id="practitionerHiringList" resultMap="practitionerHiringListInfo">
        select b.id                                           hiringBasicInfoId,
               b.name                                         name,
               b.mobile_no                                    mobileNo,
               m.md_drop_option_name                          practitionerLevel,
               m.mentor                                       mentor,
               m.introducer                                   introducer,
               m.subsystem                                    subsystem,
               m.branch                                       branch,
               date_format(b.created_at, '%Y-%m-%d %H:%i:%s') applicationTime,
               r.approving_status                             status,
               r.created_at                                   approvingTime
        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 ag_acl_practitioner_subordinate_system sys on sys.id = m.subsystem_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 1=1
        <if test="branchId != null">
            and m.branch = #{branchId}
        </if>
        <if test="deptId != null">
            and sys.dept_id = #{deptId}
        </if>
        <if test="subsystemId != null">
            and m.subsystem = #{subsystemId}
        </if>
        <if test="name != null">
            and b.name = #{name}
        </if>
        <if test="status != null">
            <choose>
                <when test='status == "0" '>
                    and r.approving_status is null
                </when>
                <when test='status == "1" '>
                    and r.approving_status = 1
                </when>
                <when test='status == "2" '>
                    and r.approving_status = 0
                </when>
            </choose>
        </if>

    </select>

    <select id="queryHandledList" resultMap="practitionerHiringListInfo">
        select  b.id                                           hiringBasicInfoId,
                b.name                                         name,
                b.mobile_no                                    mobileNo,
                m.md_drop_option_name                          practitionerLevel,
                m.mentor                                       mentor,
                m.introducer                                   introducer,
                m.subsystem                                    subsystem,
                m.branch                                       branch,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s') applicationTime,
                r.approving_status                             status,
                r.created_at                                   approvingTime
        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 ag_acl_practitioner_subordinate_system sys on sys.id = m.subsystem_id
        left join ag_acl_practitioner_hiring_approve_records r on r.hiring_basic_info_id = b.id and r.approving_practitioner_id = #{practitionerId}
        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="status != null">
                            <choose>
                                <when test='status == "1" '>
                                    and (b.approve_status >= t.approve_step_id OR b.approve_status = 0)
                                </when>
                                <when test='status == "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>
                        )
        <if test="branchId != null">
            and m.branch_id = #{branchId}
        </if>
        <if test="deptId != null">
            and sys.dept_id = #{deptId}
        </if>
     <!--   <if test="subsystemId != null">
            and m.subsystem = #{subsystemId}
        </if>-->
        <if test="subsystemIdList != null and subsystemIdList.size() > 0">
            and m.subsystem_id in
            <foreach collection="subsystemIdList" index="index" item="subsystemId" open="(" separator="," close=")">
                #{subsystemId}
            </foreach>
        </if>
        <if test="name != null">
            and b.name like #{name}
        </if>
    </select>

    <select id="queryUnhandledList" resultMap="practitionerHiringListInfo">
        select  b.id                                           hiringBasicInfoId,
                b.name                                         name,
                b.mobile_no                                    mobileNo,
                m.md_drop_option_name                          practitionerLevel,
                m.mentor                                       mentor,
                m.introducer                                   introducer,
                m.subsystem                                    subsystem,
                m.branch                                       branch,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s') applicationTime,
                null as                                        status,
                null as                                        approvingTime
        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 ag_acl_practitioner_subordinate_system sys on sys.id = m.subsystem_id

        where b.approve_status is not null
        <if test="approveStatus != null">
            and b.approve_status = #{approveStatus}
        </if>
        <if test="list != null and list.size() > 0">
            and m.md_drop_option_id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test="branchId != null">
            and m.branch_id = #{branchId}
        </if>
        <if test="deptId != null">
            and sys.dept_id = #{deptId}
        </if>
    <!--    <if test="subsystemId != null">
            and m.subsystem = #{subsystemId}
        </if>-->
        <if test="subsystemIdList != null and subsystemIdList.size() > 0">
            and m.subsystem_id in
            <foreach collection="subsystemIdList" index="index" item="subsystemId" open="(" separator="," close=")">
                #{subsystemId}
            </foreach>
        </if>
        <if test="name != null">
            and b.name like #{name}
        </if>

    </select>

    <select id="queryS2List" resultMap="practitionerHiringListInfo">
        select  b.id                                           hiringBasicInfoId,
                b.name                                         name,
                b.mobile_no                                    mobileNo,
                m.md_drop_option_name                          practitionerLevel,
                m.mentor                                       mentor,
                m.introducer                                   introducer,
                m.subsystem                                    subsystem,
                m.branch                                       branch,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s') applicationTime,
                null as                                        status,
                null as                                        approvingTime
        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.approve_status is not null
        <if test="approveStatus != null">
            and b.approve_status = #{approveStatus}
        </if>
        <if test="name != null">
            and b.name like #{name}
        </if>
        and m.md_drop_option_id
                in (select md.id
                    from ag_md_drop_options md
                    left join ag_md_drop_master ms on ms.id = md.drop_master_id
                    where 1=1
                    and ms.scenario_code = 'practitioner_level'
                    and md.drop_option_code in ('S2','S3')
                    )
    </select>

    <select id="practitionerApplyList" resultMap="practitionerHiringListInfo">
        select  b.id                                           hiringBasicInfoId,
                b.name                                         name,
                b.mobile_no                                    mobileNo,
                m.md_drop_option_name                          practitionerLevel,
                m.mentor                                       mentor,
                m.introducer                                   introducer,
                m.subsystem                                    subsystem,
                m.branch                                       branch,
                date_format(b.created_at, '%Y-%m-%d %H:%i:%s') applicationTime,
	            <if test="approveStatus != null">
		             <choose>
		                 <when test='approveStatus == "1" '>
		                    #{approveStatus} as status,
		                    (select max(r.created_at) from ag_acl_practitioner_hiring_approve_records r where r.hiring_basic_info_id = b.id ) as approvingTime
		                 </when>
		                 <when test='approveStatus == "0" '>
		                    null as status,
		                    null as approvingTime
		                 </when>
		                 <when test='approveStatus == "2" '>
		                    '0' as status,
		                    null as approvingTime
		                 </when>
		             </choose>
	        	 </if>
        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 ag_acl_practitioner_subordinate_system sys on sys.id = m.subsystem_id
        where 1 = 1
         <if test="approveStatus != null">
             <choose>
                <when test='approveStatus == "0" '>
                    and (b.approve_status not in ('0' , '-1') or b.approve_status is null)
                </when>
                 <when test='approveStatus == "1" '>
                     and b.approve_status = '0'
                 </when>
                 <when test='approveStatus == "2" '>
                     and b.approve_status = '-1'
                 </when>
             </choose>
         </if>
         <if test="startDate != null ">
            and b.created_at &gt;= #{startDate}
       	 </if>
       	 <if test="endDate != null ">
            and b.created_at &lt;= #{endDate}
       	 </if>
        
        <if test="list != null and list.size() > 0">
            and m.md_drop_option_id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="branchId != null">
            and m.branch_id = #{branchId}
        </if>
        <if test="deptId != null">
            and sys.dept_id = #{deptId}
        </if>
        <if test="subsystemIdList != null and subsystemIdList.size() > 0">
            and m.subsystem_id in
            <foreach collection="subsystemIdList" index="index" item="subsystemId" open="(" separator="," close=")">
                #{subsystemId}
            </foreach>
        </if>
        <if test="name != null">
            and b.name like #{name}
        </if>
        order by b.id desc
    </select>
    
</mapper>
