<?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="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"/>
    </resultMap>
    <select id="findPractitionerHiringBasicInfo" resultMap="practitionerHiringBasicInfo">
        select p.name                                                 inviteePractitionerName,
               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.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,
               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,
               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="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="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"/>
    </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
        from ag_acl_practitioner_hiring_basic_info b
                     left join ag_acl_practitioner_hiring_membership m on b.id = m.hiring_basic_info_id
    </select>
</mapper>