<?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.customer.AclPractitionerMapper">
  <resultMap id="BaseResultMap" type="com.yd.dal.entity.customer.AclPractitioner">
    <!--@mbg.generated-->
    <!--@Table ag_acl_practitioner-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="insurer_id" jdbcType="BIGINT" property="insurerId" />
    <result column="insurer_branch_id" jdbcType="BIGINT" property="insurerBranchId" />
    <result column="dept_id" jdbcType="BIGINT" property="deptId" />
    <result column="subordinate_system_id" jdbcType="BIGINT" property="subordinateSystemId" />
    <result column="practitioner_code" jdbcType="VARCHAR" property="practitionerCode" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
    <result column="id_type_id" jdbcType="BIGINT" property="idTypeId" />
    <result column="id_type" jdbcType="VARCHAR" property="idType" />
    <result column="id_no" jdbcType="VARCHAR" property="idNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
    <result column="practitioner_birthdate" jdbcType="DATE" property="practitionerBirthdate" />
    <result column="practitioner_reg_no" jdbcType="VARCHAR" property="practitionerRegNo" />
    <result column="practitioner_reg_company" jdbcType="VARCHAR" property="practitionerRegCompany" />
    <result column="effective_start_date" jdbcType="DATE" property="effectiveStartDate" />
    <result column="effective_end_date" jdbcType="DATE" property="effectiveEndDate" />
    <result column="remark" jdbcType="LONGVARCHAR" property="remark" />
    <result column="customer_id" jdbcType="BIGINT" property="customerId" />
    <result column="employee_no" jdbcType="VARCHAR" property="employeeNo" />
    <result column="contract_no" jdbcType="VARCHAR" property="contractNo" />
    <result column="is_active" jdbcType="INTEGER" property="isActive" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="created_by" jdbcType="BIGINT" property="createdBy" />
    <result column="updated_at" jdbcType="TIMESTAMP" property="updatedAt" />
    <result column="updated_by" jdbcType="BIGINT" property="updatedBy" />
    <result column="province_id" jdbcType="BIGINT" property="provinceId" />
    <result column="province_name" jdbcType="VARCHAR" property="provinceName" />
    <result column="city_id" jdbcType="BIGINT" property="cityId" />
    <result column="city_name" jdbcType="VARCHAR" property="cityName" />
    <result column="cert_list" jdbcType="VARCHAR" property="certList" />
    <result column="bio_intro" jdbcType="LONGVARCHAR" property="bioIntro" />
    <result column="bio_intro_two" jdbcType="LONGVARCHAR" property="bioIntroTwo" />
    <result column="wechat_id" jdbcType="VARCHAR" property="wechatId" />
    <result column="qq_id" jdbcType="VARCHAR" property="qqId" />
    <result column="is_profile_show" jdbcType="INTEGER" property="isProfileShow" />
    <result column="is_name_show" jdbcType="INTEGER" property="isNameShow" />
    <result column="is_mobile_show" jdbcType="INTEGER" property="isMobileShow" />
    <result column="education_level" jdbcType="VARCHAR" property="educationLevel" />
    <result column="graduated_university" jdbcType="VARCHAR" property="graduatedUniversity" />
    <result column="mentor_id" jdbcType="BIGINT" property="mentorId" />
    <result column="in_mentor_id" jdbcType="BIGINT" property="inMentorId" />
    <result column="introducer_id" jdbcType="BIGINT" property="introducerId" />
    <result column="gender" jdbcType="INTEGER" property="gender" />
    <result column="contract_oss_path" jdbcType="VARCHAR" property="contractOssPath" />
    <result column="hiring_basic_info_id" jdbcType="BIGINT" property="hiringBasicInfoId" />
    <result column="can_login_dyd" jdbcType="BIGINT" property="canLoginDyd" />
    <result column="can_see_salary_list" jdbcType="BIGINT" property="canSeeSalaryList" />
    <result column="can_see_yd_college" jdbcType="VARCHAR" property="canSeeYdCollege" />
    <result column="raise_id" jdbcType="BIGINT" property="raiseId" />

    <result column="s1_id" jdbcType="BIGINT" property="s1Id" />
    <result column="s2_id" jdbcType="BIGINT" property="s2Id" />
    <result column="s3_id" jdbcType="BIGINT" property="s3Id" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="email_is_active" jdbcType="INTEGER" property="emailIsActive" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, insurer_id, insurer_branch_id, dept_id, subordinate_system_id, practitioner_code,
    `name`, mobile_no, id_type_id, id_type, id_no, practitioner_birthdate, practitioner_reg_no, 
    practitioner_reg_company, effective_start_date, effective_end_date, remark, customer_id, 
    employee_no, contract_no, is_active, created_at, created_by, updated_at, updated_by,
    province_id, province_name, city_id, city_name, cert_list, bio_intro, bio_intro_two, wechat_id,
    qq_id, is_profile_show, is_name_show, is_mobile_show, education_level, graduated_university,
    mentor_id,in_mentor_id, introducer_id, gender, contract_oss_path, hiring_basic_info_id, can_login_dyd,
    can_see_salary_list,can_see_yd_college,raise_id,s1_id,s2_id,s3_id,email,email_is_active
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    <!--@mbg.generated-->
    delete from ag_acl_practitioner
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.customer.AclPractitioner" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ag_acl_practitioner (insurer_id, insurer_branch_id, dept_id,
      subordinate_system_id, practitioner_code, `name`, 
      mobile_no, id_type_id, id_type, 
      id_no, practitioner_birthdate, practitioner_reg_no, 
      practitioner_reg_company, effective_start_date, 
      effective_end_date, remark, customer_id, 
      employee_no, contract_no, is_active,
      created_at, created_by, updated_at,
      updated_by, province_id, province_name,
      city_id, city_name, cert_list,
      bio_intro,bio_intro_two, wechat_id, qq_id,
      is_profile_show, is_name_show, is_mobile_show,
      education_level, graduated_university, mentor_id, in_mentor_id,
      introducer_id, gender, contract_oss_path,
      hiring_basic_info_id, can_login_dyd, can_see_salary_list,political_outlook,
      detail_address,account_bank,bank_card_no,email,s1_id,s2_id,s3_id
      )
    values (#{insurerId,jdbcType=BIGINT}, #{insurerBranchId,jdbcType=BIGINT}, #{deptId,jdbcType=BIGINT}, 
      #{subordinateSystemId,jdbcType=BIGINT}, #{practitionerCode,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},
    #{mobileNo,jdbcType=VARCHAR,typeHandler=com.yd.util.deshandler.DESTypeHandler}, #{idTypeId,jdbcType=BIGINT}, #{idType,jdbcType=VARCHAR},
    #{idNo,jdbcType=VARCHAR,typeHandler=com.yd.util.deshandler.DESTypeHandler}, #{practitionerBirthdate,jdbcType=DATE}, #{practitionerRegNo,jdbcType=VARCHAR},
    #{practitionerRegCompany,jdbcType=VARCHAR}, #{effectiveStartDate,jdbcType=DATE},
      #{effectiveEndDate,jdbcType=DATE}, #{remark,jdbcType=LONGVARCHAR}, #{customerId,jdbcType=BIGINT},
      #{employeeNo,jdbcType=VARCHAR}, #{contractNo,jdbcType=VARCHAR}, #{isActive,jdbcType=INTEGER},
      #{createdAt,jdbcType=TIMESTAMP}, #{createdBy,jdbcType=BIGINT}, #{updatedAt,jdbcType=TIMESTAMP},
      #{updatedBy,jdbcType=BIGINT}, #{provinceId,jdbcType=BIGINT}, #{provinceName,jdbcType=VARCHAR},
      #{cityId,jdbcType=BIGINT}, #{cityName,jdbcType=VARCHAR}, #{certList,jdbcType=VARCHAR},
      #{bioIntro,jdbcType=LONGVARCHAR},#{bioIntroTwo,jdbcType=LONGVARCHAR}, #{wechatId,jdbcType=VARCHAR}, #{qqId,jdbcType=VARCHAR},
      #{isProfileShow,jdbcType=INTEGER}, #{isNameShow,jdbcType=INTEGER}, #{isMobileShow,jdbcType=INTEGER},
      #{educationLevel,jdbcType=VARCHAR}, #{graduatedUniversity,jdbcType=VARCHAR}, #{mentorId,jdbcType=BIGINT}, #{inMentorId,jdbcType=BIGINT},
      #{introducerId,jdbcType=BIGINT}, #{gender,jdbcType=INTEGER}, #{contractOssPath,jdbcType=VARCHAR},
      #{hiringBasicInfoId,jdbcType=BIGINT}, #{canLoginDyd,jdbcType=BIGINT}, #{canSeeSalaryList,jdbcType=BIGINT},#{politicalOutlook,jdbcType=VARCHAR},
      #{detailAddress},#{accountBank,jdbcType=VARCHAR}, #{bankCardNo,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR},
      #{s1Id,jdbcType=BIGINT},#{s2Id,jdbcType=BIGINT},#{s3Id,jdbcType=BIGINT}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.customer.AclPractitioner" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ag_acl_practitioner
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="insurerId != null">
        insurer_id,
      </if>
      <if test="insurerBranchId != null">
        insurer_branch_id,
      </if>
      <if test="deptId != null">
        dept_id,
      </if>
      <if test="subordinateSystemId != null">
        subordinate_system_id,
      </if>
      <if test="practitionerCode != null">
        practitioner_code,
      </if>
      <if test="name != null">
        `name`,
      </if>
      <if test="mobileNo != null">
        mobile_no,
      </if>
      <if test="idTypeId != null">
        id_type_id,
      </if>
      <if test="idType != null">
        id_type,
      </if>
      <if test="idNo != null">
        id_no,
      </if>
      <if test="practitionerBirthdate != null">
        practitioner_birthdate,
      </if>
      <if test="practitionerRegNo != null">
        practitioner_reg_no,
      </if>
      <if test="practitionerRegCompany != null">
        practitioner_reg_company,
      </if>
      <if test="effectiveStartDate != null">
        effective_start_date,
      </if>
      <if test="effectiveEndDate != null">
        effective_end_date,
      </if>
      <if test="remark != null">
        remark,
      </if>
      <if test="customerId != null">
        customer_id,
      </if>
      <if test="employeeNo != null">
        employee_no,
      </if>
      <if test="contractNo != null">
        contract_no,
      </if>
      <if test="isActive != null">
        is_active,
      </if>
      <if test="createdAt != null">
        created_at,
      </if>
      <if test="createdBy != null">
        created_by,
      </if>
      <if test="updatedAt != null">
        updated_at,
      </if>
      <if test="updatedBy != null">
        updated_by,
      </if>
      <if test="provinceId != null">
        province_id,
      </if>
      <if test="provinceName != null">
        province_name,
      </if>
      <if test="cityId != null">
        city_id,
      </if>
      <if test="cityName != null">
        city_name,
      </if>
      <if test="certList != null">
        cert_list,
      </if>
      <if test="bioIntro != null">
        bio_intro,
      </if>
      <if test="bioIntroTwo != null">
        bio_intro_two,
      </if>
      <if test="wechatId != null">
        wechat_id,
      </if>
      <if test="qqId != null">
        qq_id,
      </if>
      <if test="isProfileShow != null">
        is_profile_show,
      </if>
      <if test="isNameShow != null">
        is_name_show,
      </if>
      <if test="isMobileShow != null">
        is_mobile_show,
      </if>
      <if test="educationLevel != null">
        education_level,
      </if>
      <if test="graduatedUniversity != null">
        graduated_university,
      </if>
      <if test="mentorId != null">
        mentor_id,
      </if>
      <if test="inMentorId != null">
        in_mentor_id,
      </if>
      <if test="introducerId != null">
        introducer_id,
      </if>
      <if test="gender != null">
        gender,
      </if>
      <if test="contractOssPath != null">
        contract_oss_path,
      </if>
      <if test="hiringBasicInfoId != null">
        hiring_basic_info_id,
      </if>
      <if test="canLoginDyd != null">
        can_login_dyd,
      </if>
      <if test="canSeeSalaryList != null">
        can_see_salary_list,
      </if>
      <if test="detailAddress != null">
        detail_address,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="insurerId != null">
        #{insurerId,jdbcType=BIGINT},
      </if>
      <if test="insurerBranchId != null">
        #{insurerBranchId,jdbcType=BIGINT},
      </if>
      <if test="deptId != null">
        #{deptId,jdbcType=BIGINT},
      </if>
      <if test="subordinateSystemId != null">
        #{subordinateSystemId,jdbcType=BIGINT},
      </if>
      <if test="practitionerCode != null">
        #{practitionerCode,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="mobileNo != null">
        #{mobileNo,jdbcType=VARCHAR},
      </if>
      <if test="idTypeId != null">
        #{idTypeId,jdbcType=BIGINT},
      </if>
      <if test="idType != null">
        #{idType,jdbcType=VARCHAR},
      </if>
      <if test="idNo != null">
        #{idNo,jdbcType=VARCHAR},
      </if>
      <if test="practitionerBirthdate != null">
        #{practitionerBirthdate,jdbcType=DATE},
      </if>
      <if test="practitionerRegNo != null">
        #{practitionerRegNo,jdbcType=VARCHAR},
      </if>
      <if test="practitionerRegCompany != null">
        #{practitionerRegCompany,jdbcType=VARCHAR},
      </if>
      <if test="effectiveStartDate != null">
        #{effectiveStartDate,jdbcType=DATE},
      </if>
      <if test="effectiveEndDate != null">
        #{effectiveEndDate,jdbcType=DATE},
      </if>
      <if test="remark != null">
        #{remark,jdbcType=LONGVARCHAR},
      </if>
      <if test="customerId != null">
        #{customerId,jdbcType=BIGINT},
      </if>
      <if test="employeeNo != null">
        #{employeeNo,jdbcType=VARCHAR},
      </if>
      <if test="contractNo != null">
        #{contractNo,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        #{createdBy,jdbcType=BIGINT},
      </if>
      <if test="updatedAt != null">
        #{updatedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="updatedBy != null">
        #{updatedBy,jdbcType=BIGINT},
      </if>
      <if test="provinceId != null">
        #{provinceId,jdbcType=BIGINT},
      </if>
      <if test="provinceName != null">
        #{provinceName,jdbcType=VARCHAR},
      </if>
      <if test="cityId != null">
        #{cityId,jdbcType=BIGINT},
      </if>
      <if test="cityName != null">
        #{cityName,jdbcType=VARCHAR},
      </if>
      <if test="certList != null">
        #{certList,jdbcType=VARCHAR},
      </if>
      <if test="bioIntro != null">
        #{bioIntro,jdbcType=LONGVARCHAR},
      </if>
      <if test="bioIntroTwo != null">
        #{bioIntroTwo,jdbcType=LONGVARCHAR},
      </if>
      <if test="wechatId != null">
        #{wechatId,jdbcType=VARCHAR},
      </if>
      <if test="qqId != null">
        #{qqId,jdbcType=VARCHAR},
      </if>
      <if test="isProfileShow != null">
        #{isProfileShow,jdbcType=INTEGER},
      </if>
      <if test="isNameShow != null">
        #{isNameShow,jdbcType=INTEGER},
      </if>
      <if test="isMobileShow != null">
        #{isMobileShow,jdbcType=INTEGER},
      </if>
      <if test="educationLevel != null">
        #{educationLevel,jdbcType=VARCHAR},
      </if>
      <if test="graduatedUniversity != null">
        #{graduatedUniversity,jdbcType=VARCHAR},
      </if>
      <if test="mentorId != null">
        #{mentorId,jdbcType=BIGINT},
      </if>
      <if test="inMentorId != null">
        #{inMentorId,jdbcType=BIGINT},
      </if>
      <if test="introducerId != null">
        #{introducerId,jdbcType=BIGINT},
      </if>
      <if test="gender != null">
        #{gender,jdbcType=INTEGER},
      </if>
      <if test="contractOssPath != null">
        #{contractOssPath,jdbcType=VARCHAR},
      </if>
      <if test="hiringBasicInfoId != null">
        #{hiringBasicInfoId,jdbcType=BIGINT},
      </if>
      <if test="canLoginDyd != null">
        #{canLoginDyd,jdbcType=BIGINT},
      </if>
      <if test="canSeeSalaryList != null">
        #{canSeeSalaryList,jdbcType=BIGINT},
      </if>
      <if test="detailAddress != null">
        #{detailAddress,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.customer.AclPractitioner">
    <!--@mbg.generated-->
    update ag_acl_practitioner
    <set>
      <if test="insurerId != null">
        insurer_id = #{insurerId,jdbcType=BIGINT},
      </if>
      <if test="insurerBranchId != null">
        insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT},
      </if>
      <if test="deptId != null">
        dept_id = #{deptId,jdbcType=BIGINT},
      </if>
      <if test="subordinateSystemId != null">
        subordinate_system_id = #{subordinateSystemId,jdbcType=BIGINT},
      </if>
      <if test="practitionerCode != null">
        practitioner_code = #{practitionerCode,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      </if>
      <if test="mobileNo != null">
        mobile_no = #{mobileNo,jdbcType=VARCHAR,typeHandler=com.yd.util.deshandler.DESTypeHandler},
      </if>
      <if test="idTypeId != null">
        id_type_id = #{idTypeId,jdbcType=BIGINT},
      </if>
      <if test="idType != null">
        id_type = #{idType,jdbcType=VARCHAR},
      </if>
      <if test="idNo != null">
        id_no = #{idNo,jdbcType=VARCHAR,typeHandler=com.yd.util.deshandler.DESTypeHandler},
      </if>
      <if test="practitionerBirthdate != null">
        practitioner_birthdate = #{practitionerBirthdate,jdbcType=DATE},
      </if>
      <if test="practitionerRegNo != null">
        practitioner_reg_no = #{practitionerRegNo,jdbcType=VARCHAR},
      </if>
      <if test="practitionerRegCompany != null">
        practitioner_reg_company = #{practitionerRegCompany,jdbcType=VARCHAR},
      </if>
      <if test="effectiveStartDate != null">
        effective_start_date = #{effectiveStartDate,jdbcType=DATE},
      </if>
      <if test="effectiveEndDate != null">
        effective_end_date = #{effectiveEndDate,jdbcType=DATE},
      </if>
      <if test="remark != null">
        remark = #{remark,jdbcType=LONGVARCHAR},
      </if>
      <if test="customerId != null">
        customer_id = #{customerId,jdbcType=BIGINT},
      </if>
      <if test="employeeNo != null">
        employee_no = #{employeeNo,jdbcType=VARCHAR},
      </if>
      <if test="contractNo != null">
        contract_no = #{contractNo,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        is_active = #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        created_at = #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        created_by = #{createdBy,jdbcType=BIGINT},
      </if>
      <if test="updatedAt != null">
        updated_at = #{updatedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="updatedBy != null">
        updated_by = #{updatedBy,jdbcType=BIGINT},
      </if>
      <if test="provinceId != null">
        province_id = #{provinceId,jdbcType=BIGINT},
      </if>
      <if test="provinceName != null">
        province_name = #{provinceName,jdbcType=VARCHAR},
      </if>
      <if test="cityId != null">
        city_id = #{cityId,jdbcType=BIGINT},
      </if>
      <if test="cityName != null">
        city_name = #{cityName,jdbcType=VARCHAR},
      </if>
      <if test="certList != null">
        cert_list = #{certList,jdbcType=VARCHAR},
      </if>
      <if test="bioIntro != null">
        bio_intro = #{bioIntro,jdbcType=LONGVARCHAR},
      </if>
      <if test="bioIntroTwo != null">
        bio_intro_two = #{bioIntroTwo,jdbcType=LONGVARCHAR},
      </if>
      <if test="wechatId != null">
        wechat_id = #{wechatId,jdbcType=VARCHAR},
      </if>
      <if test="qqId != null">
        qq_id = #{qqId,jdbcType=VARCHAR},
      </if>
      <if test="isProfileShow != null">
        is_profile_show = #{isProfileShow,jdbcType=INTEGER},
      </if>
      <if test="isNameShow != null">
        is_name_show = #{isNameShow,jdbcType=INTEGER},
      </if>
      <if test="isMobileShow != null">
        is_mobile_show = #{isMobileShow,jdbcType=INTEGER},
      </if>
      <if test="educationLevel != null">
        education_level = #{educationLevel,jdbcType=VARCHAR},
      </if>
      <if test="graduatedUniversity != null">
        graduated_university = #{graduatedUniversity,jdbcType=VARCHAR},
      </if>
      <if test="mentorId != null">
        mentor_id = #{mentorId,jdbcType=BIGINT},
      </if>
      <if test="inMentorId != null">
        in_mentor_id = #{inMentorId,jdbcType=BIGINT},
      </if>
      <if test="introducerId != null">
        introducer_id = #{introducerId,jdbcType=BIGINT},
      </if>
      <if test="gender != null">
        gender = #{gender,jdbcType=INTEGER},
      </if>
      <if test="contractOssPath != null">
        contract_oss_path = #{contractOssPath,jdbcType=VARCHAR},
      </if>
      <if test="hiringBasicInfoId != null">
        hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT},
      </if>
      <if test="canLoginDyd != null">
        can_login_dyd = #{canLoginDyd,jdbcType=BIGINT},
      </if>
      <if test="canSeeSalaryList != null">
        can_see_salary_list = #{canSeeSalaryList,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.customer.AclPractitioner">
    <!--@mbg.generated-->
    update ag_acl_practitioner
    set insurer_id = #{insurerId,jdbcType=BIGINT},
      insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT},
      dept_id = #{deptId,jdbcType=BIGINT},
      subordinate_system_id = #{subordinateSystemId,jdbcType=BIGINT},
      practitioner_code = #{practitionerCode,jdbcType=VARCHAR},
      `name` = #{name,jdbcType=VARCHAR},
      mobile_no = #{mobileNo,jdbcType=VARCHAR},
      id_type_id = #{idTypeId,jdbcType=BIGINT},
      id_type = #{idType,jdbcType=VARCHAR},
      id_no = #{idNo,jdbcType=VARCHAR},
      practitioner_birthdate = #{practitionerBirthdate,jdbcType=DATE},
      practitioner_reg_no = #{practitionerRegNo,jdbcType=VARCHAR},
      practitioner_reg_company = #{practitionerRegCompany,jdbcType=VARCHAR},
      effective_start_date = #{effectiveStartDate,jdbcType=DATE},
      effective_end_date = #{effectiveEndDate,jdbcType=DATE},
      remark = #{remark,jdbcType=LONGVARCHAR},
      customer_id = #{customerId,jdbcType=BIGINT},
      employee_no = #{employeeNo,jdbcType=VARCHAR},
      contract_no = #{contractNo,jdbcType=VARCHAR},
      is_active = #{isActive,jdbcType=INTEGER},
      created_at = #{createdAt,jdbcType=TIMESTAMP},
      created_by = #{createdBy,jdbcType=BIGINT},
      updated_at = #{updatedAt,jdbcType=TIMESTAMP},
      updated_by = #{updatedBy,jdbcType=BIGINT},
      province_id = #{provinceId,jdbcType=BIGINT},
      province_name = #{provinceName,jdbcType=VARCHAR},
      city_id = #{cityId,jdbcType=BIGINT},
      city_name = #{cityName,jdbcType=VARCHAR},
      cert_list = #{certList,jdbcType=VARCHAR},
      bio_intro = #{bioIntro,jdbcType=LONGVARCHAR},
      bio_intro_two = #{bioIntroTwo,jdbcType=LONGVARCHAR},
      wechat_id = #{wechatId,jdbcType=VARCHAR},
      qq_id = #{qqId,jdbcType=VARCHAR},
      is_profile_show = #{isProfileShow,jdbcType=INTEGER},
      is_name_show = #{isNameShow,jdbcType=INTEGER},
      is_mobile_show = #{isMobileShow,jdbcType=INTEGER},
      education_level = #{educationLevel,jdbcType=VARCHAR},
      graduated_university = #{graduatedUniversity,jdbcType=VARCHAR},
      mentor_id = #{mentorId,jdbcType=BIGINT},
      in_mentor_id = #{inMentorId,jdbcType=BIGINT},
      introducer_id = #{introducerId,jdbcType=BIGINT},
      gender = #{gender,jdbcType=INTEGER},
      contract_oss_path = #{contractOssPath,jdbcType=VARCHAR},
      hiring_basic_info_id = #{hiringBasicInfoId,jdbcType=BIGINT},
      can_login_dyd = #{canLoginDyd,jdbcType=BIGINT},
      can_see_salary_list = #{canSeeSalaryList,jdbcType=BIGINT}
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateBatch" parameterType="java.util.List">
    <!--@mbg.generated-->
    update ag_acl_practitioner
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="insurer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.insurerId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="insurer_branch_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.insurerBranchId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="dept_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.deptId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="subordinate_system_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.subordinateSystemId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="practitioner_code = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerCode,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="`name` = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.name,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="mobile_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.mobileNo,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="id_type_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.idTypeId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="id_type = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.idType,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="id_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.idNo,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="practitioner_birthdate = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerBirthdate,jdbcType=DATE}
        </foreach>
      </trim>
      <trim prefix="practitioner_reg_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerRegNo,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="practitioner_reg_company = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerRegCompany,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="effective_start_date = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.effectiveStartDate,jdbcType=DATE}
        </foreach>
      </trim>
      <trim prefix="effective_end_date = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.effectiveEndDate,jdbcType=DATE}
        </foreach>
      </trim>
      <trim prefix="remark = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.remark,jdbcType=LONGVARCHAR}
        </foreach>
      </trim>
      <trim prefix="customer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.customerId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="employee_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.employeeNo,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="contract_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.contractNo,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="is_active = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.isActive,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="created_at = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.createdAt,jdbcType=TIMESTAMP}
        </foreach>
      </trim>
      <trim prefix="created_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.createdBy,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="updated_at = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.updatedAt,jdbcType=TIMESTAMP}
        </foreach>
      </trim>
      <trim prefix="updated_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.updatedBy,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="province_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.provinceId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="province_name = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.provinceName,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="city_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.cityId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="city_name = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.cityName,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="cert_list = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.certList,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="bio_intro = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.bioIntro,jdbcType=LONGVARCHAR}
        </foreach>
      </trim>
      <trim prefix="wechat_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.wechatId,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="qq_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.qqId,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="is_profile_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.isProfileShow,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="is_name_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.isNameShow,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="is_mobile_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.isMobileShow,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="education_level = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.educationLevel,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="graduated_university = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.graduatedUniversity,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="mentor_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.mentorId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="introducer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.introducerId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="gender = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.gender,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="contract_oss_path = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.contractOssPath,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="hiring_basic_info_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.hiringBasicInfoId,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="can_login_dyd = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.canLoginDyd,jdbcType=BIGINT}
        </foreach>
      </trim>
      <trim prefix="can_see_salary_list = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when id = #{item.id,jdbcType=BIGINT} then #{item.canSeeSalaryList,jdbcType=BIGINT}
        </foreach>
      </trim>
    </trim>
    where id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.id,jdbcType=BIGINT}
    </foreach>
  </update>
  <update id="updateBatchSelective" parameterType="java.util.List">
    <!--@mbg.generated-->
    update ag_acl_practitioner
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="insurer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.insurerId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.insurerId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="insurer_branch_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.insurerBranchId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.insurerBranchId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="dept_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.deptId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.deptId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="subordinate_system_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.subordinateSystemId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.subordinateSystemId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="practitioner_code = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.practitionerCode != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerCode,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="`name` = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.name != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.name,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="mobile_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.mobileNo != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.mobileNo,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="id_type_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.idTypeId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.idTypeId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="id_type = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.idType != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.idType,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="id_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.idNo != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.idNo,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="practitioner_birthdate = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.practitionerBirthdate != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerBirthdate,jdbcType=DATE}
          </if>
        </foreach>
      </trim>
      <trim prefix="practitioner_reg_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.practitionerRegNo != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerRegNo,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="practitioner_reg_company = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.practitionerRegCompany != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.practitionerRegCompany,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="effective_start_date = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.effectiveStartDate != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.effectiveStartDate,jdbcType=DATE}
          </if>
        </foreach>
      </trim>
      <trim prefix="effective_end_date = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.effectiveEndDate != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.effectiveEndDate,jdbcType=DATE}
          </if>
        </foreach>
      </trim>
      <trim prefix="remark = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.remark != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.remark,jdbcType=LONGVARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="customer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.customerId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.customerId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="employee_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.employeeNo != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.employeeNo,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="contract_no = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.contractNo != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.contractNo,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="is_active = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.isActive != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.isActive,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="created_at = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.createdAt != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.createdAt,jdbcType=TIMESTAMP}
          </if>
        </foreach>
      </trim>
      <trim prefix="created_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.createdBy != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.createdBy,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="updated_at = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.updatedAt != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.updatedAt,jdbcType=TIMESTAMP}
          </if>
        </foreach>
      </trim>
      <trim prefix="updated_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.updatedBy != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.updatedBy,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="province_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.provinceId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.provinceId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="province_name = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.provinceName != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.provinceName,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="city_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.cityId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.cityId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="city_name = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.cityName != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.cityName,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="cert_list = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.certList != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.certList,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="bio_intro = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.bioIntro != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.bioIntro,jdbcType=LONGVARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="wechat_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.wechatId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.wechatId,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="qq_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.qqId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.qqId,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="is_profile_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.isProfileShow != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.isProfileShow,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="is_name_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.isNameShow != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.isNameShow,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="is_mobile_show = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.isMobileShow != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.isMobileShow,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="education_level = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.educationLevel != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.educationLevel,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="graduated_university = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.graduatedUniversity != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.graduatedUniversity,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="mentor_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.mentorId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.mentorId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="introducer_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.introducerId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.introducerId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="gender = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.gender != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.gender,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="contract_oss_path = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.contractOssPath != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.contractOssPath,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="hiring_basic_info_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.hiringBasicInfoId != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.hiringBasicInfoId,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="can_login_dyd = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.canLoginDyd != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.canLoginDyd,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
      <trim prefix="can_see_salary_list = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.canSeeSalaryList != null">
            when id = #{item.id,jdbcType=BIGINT} then #{item.canSeeSalaryList,jdbcType=BIGINT}
          </if>
        </foreach>
      </trim>
    </trim>
    where id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.id,jdbcType=BIGINT}
    </foreach>
  </update>
  <insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ag_acl_practitioner
    (insurer_id, insurer_branch_id, dept_id, subordinate_system_id, practitioner_code,
      `name`, mobile_no, id_type_id, id_type, id_no, practitioner_birthdate, practitioner_reg_no,
      practitioner_reg_company, effective_start_date, effective_end_date, remark, customer_id,
      employee_no, contract_no, is_active, created_at, created_by, updated_at, updated_by,
      province_id, province_name, city_id, city_name, cert_list, bio_intro,bio_intro_two, wechat_id,
      qq_id, is_profile_show, is_name_show, is_mobile_show, education_level, graduated_university,
      mentor_id,in_mentor_id, introducer_id, gender, contract_oss_path, hiring_basic_info_id, can_login_dyd,
      can_see_salary_list)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.insurerId,jdbcType=BIGINT}, #{item.insurerBranchId,jdbcType=BIGINT}, #{item.deptId,jdbcType=BIGINT},
        #{item.subordinateSystemId,jdbcType=BIGINT}, #{item.practitionerCode,jdbcType=VARCHAR},
        #{item.name,jdbcType=VARCHAR}, #{item.mobileNo,jdbcType=VARCHAR}, #{item.idTypeId,jdbcType=BIGINT},
        #{item.idType,jdbcType=VARCHAR}, #{item.idNo,jdbcType=VARCHAR}, #{item.practitionerBirthdate,jdbcType=DATE},
        #{item.practitionerRegNo,jdbcType=VARCHAR}, #{item.practitionerRegCompany,jdbcType=VARCHAR},
        #{item.effectiveStartDate,jdbcType=DATE}, #{item.effectiveEndDate,jdbcType=DATE},
        #{item.remark,jdbcType=LONGVARCHAR}, #{item.customerId,jdbcType=BIGINT}, #{item.employeeNo,jdbcType=VARCHAR},
        #{item.contractNo,jdbcType=VARCHAR}, #{item.isActive,jdbcType=INTEGER}, #{item.createdAt,jdbcType=TIMESTAMP},
        #{item.createdBy,jdbcType=BIGINT}, #{item.updatedAt,jdbcType=TIMESTAMP}, #{item.updatedBy,jdbcType=BIGINT},
        #{item.provinceId,jdbcType=BIGINT}, #{item.provinceName,jdbcType=VARCHAR}, #{item.cityId,jdbcType=BIGINT},
        #{item.cityName,jdbcType=VARCHAR}, #{item.certList,jdbcType=VARCHAR}, #{item.bioIntro,jdbcType=LONGVARCHAR},#{item.bioIntroTwo,jdbcType=LONGVARCHAR},
        #{item.wechatId,jdbcType=VARCHAR}, #{item.qqId,jdbcType=VARCHAR}, #{item.isProfileShow,jdbcType=INTEGER},
        #{item.isNameShow,jdbcType=INTEGER}, #{item.isMobileShow,jdbcType=INTEGER}, #{item.educationLevel,jdbcType=VARCHAR},
        #{item.graduatedUniversity,jdbcType=VARCHAR}, #{item.mentorId,jdbcType=BIGINT},#{item.inMentorId,jdbcType=BIGINT},
        #{item.introducerId,jdbcType=BIGINT}, #{item.gender,jdbcType=INTEGER}, #{item.contractOssPath,jdbcType=VARCHAR},
        #{item.hiringBasicInfoId,jdbcType=BIGINT}, #{item.canLoginDyd,jdbcType=BIGINT},
        #{item.canSeeSalaryList,jdbcType=BIGINT})
    </foreach>
  </insert>


  <resultMap id="base_result_map_practitioner_info" type="com.yd.dal.entity.customer.practitioner.PractitionerBasicInfo">
    <result column="customerId" property="customerId" />
    <result column="practitionerIdForOnline" property="practitionerIdForOnline" />
    <result column="practitionerId" property="practitionerId" />
    <result column="name" property="name" />
    <result column="titleCode" property="titleCode" />
    <!--        &lt;result column="mobile_no" property="titleCode" typeHandler="com.yd.util.deshandler.DESTypeHandler"/&gt;-->
    <result column="titleName" property="titleName" />
    <result column="gender" property="gender" />
    <result column="spcDivName" property="spcDivName" />
  </resultMap>


  <select id="getPractitionerInfoByMobileNoForOffline" resultMap="base_result_map_practitioner_info">
    SELECT
      p.SAL_MST_ID as practitionerId,
      p.SAL_MST_NAME as name,
      p.SAL_MST_GENDER as gender,
      md.DRP_TITLE_LEVEL as titleCode,
      md.DRP_TITLE_NAME as titleName
    FROM sal001 p left join DRP003 md on md.DRP_TITLE_CODE = p.FK_DRP_TITLE_CODE
    where p.SAL_MST_MOBILE = #{mobileNo} limit 1
  </select>

  <select id="getPractitionerInfoByMobileNoForOnline" resultMap="base_result_map_practitioner_info">
    select
      p.customer_id as customerId,
      p.id as practitionerIdForOnline,
      p.name as name,
      o.drop_option_code as titleCode,
      o.drop_option_name as titleName,
      p.gender as gender,
      s.practitioner_type_id as practitionerTypeId,
      ss.name as spcDivName
    from ag_acl_customer c inner join ag_acl_practitioner p on c.id = p.customer_id
                           left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
                           left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    					   left join ag_acl_practitioner_subordinate_system ss ON p.subordinate_system_id = ss.id
                           inner join ag_md_drop_options o on s.practitioner_level = o.id
    where c.login = #{mobileNo}

  </select>


  <resultMap id="practitioner_rank_map" type="com.yd.dal.entity.customer.practitioner.PractitionerRankInfo">
    <result column="customerId" property="customerId" />
    <result column="practitionerIdForOnLine" property="practitionerIdForOnLine" />
    <result column="practitionerId" property="practitionerId" />
    <result column="name" property="name" />
    <result column="titleCode" property="titleCode" />
    <result column="titleName" property="titleName" />
    <result column="count" property="count" />
    <result column="fyp" property="fyp" />
    <result column="fyc" property="fyc" />
  </resultMap>

<!--  &lt;select id="getPractitionerRankInfoForOffline" resultMap="practitioner_rank_map" resultType="java.lang.Integer"&gt;-->
<!--    SELECT-->
<!--    p.SAL_MST_ID as practitionerId ,-->
<!--    p.SAL_MST_NAME as name ,&amp;#45;&amp;#45; 姓名-->
<!--    md.DRP_TITLE_LEVEL as titleCode,&amp;#45;&amp;#45; 职称code-->
<!--    md.DRP_TITLE_NAME as titleName,&amp;#45;&amp;#45; 职称名称-->
<!--    spc.SPC_DIV_NAME as spcDivName,&amp;#45;&amp;#45; 体系id-->
<!--    ifnull(count(m.MON025_002),0) as count,&amp;#45;&amp;#45; 保单id-->
<!--    ifnull(sum(m.MON025_401),0) as fyp,&amp;#45;&amp;#45; 保费-->
<!--    ifnull(sum(policy.INS001_FYCTOTAL),0) as fyc  &amp;#45;&amp;#45; 应发佣金-->
<!--    FROM MON025 m INNER JOIN sal001 p on m.MON025_007 = p.SAL_MST_ID &amp;#45;&amp;#45; 业务员id-->
<!--    left join DRP003 md on p.FK_DRP_TITLE_CODE = md.DRP_TITLE_CODE-->
<!--    inner JOIN ins001 policy on m.MON025_002 = policy.INS_MST_ID &amp;#45;&amp;#45; 保单id-->
<!--    inner join spc004 spc on m.MON025_006 = spc.SPC_DIV_ID &amp;#45;&amp;#45; 体系-->
<!--    WHERE m.MON025_109 IN (1,2,3,4,7) AND m.MON025_303 = 1 AND m.mon025_108 = 'S01'-->
<!--    &lt;choose&gt;-->
<!--      &lt;when test="time == 1"&gt;-->
<!--        and DATE_FORMAT(policy.INS_MST_ACCEPT_DATE, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )-->
<!--      &lt;/when&gt;-->
<!--      &lt;when test="time == 3"&gt;-->
<!--        and QUARTER(policy.INS_MST_ACCEPT_DATE) = QUARTER(NOW()) AND year(policy.INS_MST_ACCEPT_DATE)=year(now())-->
<!--      &lt;/when&gt;-->
<!--      &lt;otherwise&gt;-->
<!--        and YEAR(policy.INS_MST_ACCEPT_DATE) = YEAR(now())-->
<!--      &lt;/otherwise&gt;-->
<!--    &lt;/choose&gt;-->
<!--    group by p.SAL_MST_ID;-->
<!--  &lt;/select&gt;-->

    <select id="getPractitionerRankInfoForOffline" resultMap="practitioner_rank_map" resultType="java.lang.Integer">
      SELECT
      p.SAL_MST_ID as practitionerId ,
      p.SAL_MST_NAME as name ,-- 姓名
      p.SAL_MST_ID_TYPE as titleCode,-- 职称code
      p.SAL_MST_OFFICIAL_TITLE as titleName,-- 职称名称
      spc.SPC_DIV_NAME as spcDivName,-- 体系id
      ifnull(count(distinct policy.INS_MST_ID),0) as count,-- 保单id
      ifnull(sum(policy.INS_MST_TOTAL_PREIUM),0) as fyp,-- 保费
      ifnull(sum(policy.INS001_FYCTOTAL),0) as fyc  -- 应发佣金
      FROM
      ins001 policy INNER JOIN mon025 m on policy.INS_MST_ID = m.MON025_002 and m.MON025_109 IN (1,2,3,4,7) AND m.MON025_303 = 1 AND m.mon025_108 = 'S01'
      INNER JOIN (
      SELECT mon025_002,MAX(MON025_405) as MON025_405 FROM mon025 group by mon025_002 ORDER BY mon025_002
      ) b on m.mon025_002 = b.mon025_002 AND m.MON025_405 = b.MON025_405
      INNER JOIN sal001 p on m.MON025_007 = p.SAL_MST_ID -- 业务员id
      inner join spc004 spc on m.MON025_006 = spc.SPC_DIV_ID -- 体系
      where
      <choose>
        <when test="time == 1">
           DATE_FORMAT(policy.INS_MST_ACCEPT_DATE, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
        </when>
        <when test="time == 3">
           QUARTER(policy.INS_MST_ACCEPT_DATE) = QUARTER(NOW()) AND year(policy.INS_MST_ACCEPT_DATE)=year(now())
        </when>
        <otherwise>
           YEAR(policy.INS_MST_ACCEPT_DATE) = YEAR(now())
        </otherwise>
      </choose>
      group by p.SAL_MST_ID
    </select>

  <select id="getPractitionerRankInfoForOnline" resultMap="practitioner_rank_map">
     SELECT t.* FROM (
      SELECT
    p.customer_id as customerId,
    p.id as practitionerIdForOnLine ,
    p.name as name ,-- 姓名
    ss.name as spcDivName,-- 体系id
    ifnull(count(f.id),0) as count,-- 保单id
    ifnull(sum(f.order_price),0) as fyp,-- 保费
    ifnull(sum(f.fyc_amount),0) as fyc  -- 应发佣金
    FROM ag_acl_customer_fortune f 
    INNER JOIN ag_po_order o ON f.order_id = o.id and o.order_price &gt; 0
    inner JOIN ag_acl_practitioner p ON f.customer_id = p.customer_id
    left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    left join ag_acl_practitioner_subordinate_system ss on p.subordinate_system_id = ss.id
    left join ag_acl_customer c on p.customer_id = c.id
    WHERE o.status = 3 and o.payment_status =3 and f.drop_option_code IN('S01','C01')
    <if test="practitionerTypeId != null">
      and (s.practitioner_type_id = #{practitionerTypeId}
      	or c.login in
		    <foreach close=")" collection="mobileSpecials" index="index" item="item" open="(" separator=",">
		      #{item}
		    </foreach>
   		)
    </if>
    <choose>
      <when test="time == 1">
        and DATE_FORMAT(f.order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
      </when>
      <when test="time == 3">
        and QUARTER(f.order_date) = QUARTER(NOW()) AND year(f.order_date)=year(now())
      </when>
      <otherwise>
        and YEAR(f.order_date) = YEAR(now())
      </otherwise>
    </choose>
<!--     <choose> -->
<!--       <when test="platform == 1"> -->
<!--         and o.product_category_id != 8  -->
<!--       </when> -->
<!--       <otherwise> -->
<!--         and o.product_category_id = 8 -->
<!--       </otherwise> -->
<!--     </choose> -->
    group by p.id
    
    ) t WHERE t.count > 0 
      <choose>
      <when test="type == 2">
        ORDER BY t.fyc DESC
      </when>
      <when test="type == 3">
        ORDER BY t.count DESC
      </when>
      <otherwise>
       ORDER BY t.fyp DESC
      </otherwise>
    </choose>
      LIMIT 10
  </select>

  <select id="getPractitionerRankInfo" resultMap="practitioner_rank_map">
     SELECT
    p.customer_id as customerId,
    p.id as practitionerIdForOnLine ,
    p.name as name ,-- 姓名
    ss.name as spcDivName,-- 体系id
    ifnull(count(f.id),0) as count,-- 保单id
    ifnull(sum(f.order_price),0) as fyp,-- 保费
    ifnull(sum(f.fyc_amount),0) as fyc  -- 应发佣金
    FROM  ag_acl_customer_fortune f 
    INNER JOIN ag_po_order o ON f.order_id = o.id and o.order_price &gt; 0
    INNER JOIN ag_acl_practitioner p ON f.customer_id = p.customer_id
    left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    left join ag_acl_practitioner_subordinate_system ss on p.subordinate_system_id = ss.id
    WHERE o.status = 3 and o.payment_status =3 and f.drop_option_code IN('S01','C01')
    <if test="practitionerTypeId != null">
      and s.practitioner_type_id = #{practitionerTypeId}
    </if>
    <if test="practitionerId != null">
      and p.id = #{practitionerId}
    </if>
    <choose>
      <when test="time == 1">
        and DATE_FORMAT(f.order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
      </when>
      <when test="time == 3">
        and QUARTER(f.order_date) = QUARTER(NOW()) AND year(f.order_date)=year(now())
      </when>
      <otherwise>
        and YEAR(f.order_date) = YEAR(now())
      </otherwise>
    </choose>
<!--     <choose> -->
<!--       <when test="platform == 1"> -->
<!--         and o.product_category_id != 8  -->
<!--       </when> -->
<!--       <otherwise> -->
<!--         and o.product_category_id = 8 -->
<!--       </otherwise> -->
<!--     </choose> -->
    group by p.id
  </select>


  <select id="getPractitionerRankInfoForSpecials" resultMap="practitioner_rank_map" resultType="java.util.List">
    SELECT
    p.customer_id as customerId,
    p.id as practitionerIdForOnLine ,
    p.name as name ,-- 姓名
    ss.name as spcDivName,-- 体系id
    ifnull(count(f.id),0) as count,-- 保单id
    ifnull(sum(f.order_price),0) as fyp,-- 保费
    ifnull(sum(f.fyc_amount),0) as fyc  -- 应发佣金
    FROM ag_acl_customer_fortune f 
    INNER JOIN ag_po_order o ON f.order_id = o.id and o.order_price &gt; 0
    inner JOIN ag_acl_practitioner p ON f.customer_id = p.customer_id
    left join ag_acl_customer c on p.customer_id = c.id
    left join ag_acl_practitioner_subordinate_system ss on p.subordinate_system_id = ss.id
    WHERE o.status = 3 and o.payment_status =3 and f.drop_option_code IN('S01','C01')
    and c.login in
    <foreach close=")" collection="mobileSpecials" index="index" item="item" open="(" separator=",">
      #{item}
    </foreach>
    <choose>
      <when test="time == 1">
        and DATE_FORMAT(f.order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
      </when>
      <when test="time == 3">
        and QUARTER(f.order_date) = QUARTER(NOW()) AND year(f.order_date)=year(now())
      </when>
      <otherwise>
        and YEAR(f.order_date) = YEAR(now())
      </otherwise>
    </choose>
<!--     <choose> -->
<!--       <when test="platform == 1"> -->
<!--         and o.product_category_id = 8 -->
<!--       </when> -->
<!--       <otherwise> -->
<!--         and o.product_category_id != 8 -->
<!--       </otherwise> -->
<!--     </choose> -->
    group by p.id
  </select>



  <resultMap id="practitioner_info_map" type="com.yd.dal.entity.customer.practitioner.PractitionerInfo">
    <result column="customerId" property="customerId" />
    <result column="practitionerId" property="practitionerId" />
    <result column="name" property="name" />
    <result column="gender" property="gender" />
    <result column="practitionerType" property="practitionerType" />
    <result column="levelCode" property="levelCode" />
    <result column="levelName" property="levelName" />
    <result column="insurerBranchName" property="insurerBranchName" />
    <result column="subordinateId" property="subordinateId" />
    <result column="subsystem" property="subsystem" />
    <result column="weChatId" property="weChatId" />
    <result column="qqId" property="qqId" />
    <result column="practitionerRegNo" property="practitionerRegNo" />
    <result column="canSeeSalaryList" property="canSeeSalaryList" />
    <result column="mentor_id" property="mentorId" />
    <result column="yd_wechat_openid" property="ydWechatOpenid" />
    <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
  </resultMap>

  <select id="findPractitionerInfoByLogin" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo,
      p.can_see_salary_list as canSeeSalaryList
    from ag_acl_customer c inner join ag_acl_practitioner p on c.id = p.customer_id
                           left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
                           left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
                           left join ag_md_drop_options o on s.practitioner_level = o.id
    where c.login = #{mobileNo}
    and p.is_active = 1
    and (p.can_login_dyd != 0 OR p.can_login_dyd is null)
  </select>

  <select id="findByCustomerIdIsActive" resultType="com.yd.dal.entity.customer.AclPractitioner">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where customer_id = #{customerId,jdbcType=BIGINT}
    and is_active = #{isActive,jdbcType=INTEGER}
  </select>
  <resultMap id="opportunityQuery" type="com.yd.dal.entity.practitioner.opportunity.OwnOpportunityInfo">
    <result column="opportunityId" property="opportunityId" />
    <result column="name" property="name" />
    <result column="mobileNo" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler" />
    <result column="opportunityFrom" property="opportunityFrom" />
    <result column="opportunityDate" property="opportunityDate" />
    <result column="leadsAssignedId" property="leadsAssignedId" />
    <result column="mdDropOptionName" property="mdDropOptionName" />
    <result column="scoreDay" property="scoreDay" />
    <result column="scoreTotal" property="scoreTotal" />
    <result column="expertType" property="expertType" />
    <result column="sfpMainId" property="sfpMainId" />
    <result column="wechatNo" property="wechatNo" />
  </resultMap>
  <select id="ownOpportunityQuery" resultMap="opportunityQuery">
    SELECT
    a.customer_id opportunityId,
    IFNULL(a.customer_name,c.NAME) name,
    IFNULL(c.mobile_no,a.customer_mobile) mobileNo,
    date_format(a.created_at, '%Y-%m-%d %H:%i:%s') opportunityDate,
    if (p.source_from = 0 ,(SELECT cam.name from ag_md_mk_campaign cam where cam.id = p.mkt_campaign ),(SELECT md.drop_option_name from ag_md_drop_options md where p.source_from = md.id )) opportunityFrom,
    a.id leadsAssignedId ,
    (SELECT md.drop_option_name from ag_md_drop_options md LEFT JOIN ag_mkt_leads_assigned_track amlat ON md.id = amlat.md_drop_option_id where amlat.practitioner_id = a.assigned_practitioner_id and amlat.customer_id = a.customer_id ORDER BY amlat.created_at DESC LIMIT 1) mdDropOptionName,
    (SELECT sum(t.track_score) from ag_mkt_leads_assigned_track t where t.leads_assigned_id = a.id) scoreTotal ,
    (SELECT sum(t.track_score) from ag_mkt_leads_assigned_track t where t.leads_assigned_id = a.id and to_days(track_time) = to_days(now())) scoreDay,
    if(ass.id is null ,0,1) expertType ,
    p.remark remark,
    p.appointment_record_id appointmentRecordId,
    p.sfp_main_id sfpMainId,
    c.wechat_no wechatNo
    FROM
    ag_mkt_leads_assigneds a
    left join ag_mkt_leads_expert_request r on r.customer_id = a.customer_id and r.is_active =1
    left join ag_mkt_leads_expert_assign ass on ass.leads_expert_request_id = r.id and ass.is_active =1
    left join ag_mkt_leads_pool p ON p.customer_id = a.customer_id and p.is_active =1 and p.sfp_main_id =  a.sfp_main_id
    left join ag_acl_customer c ON c.id = a.customer_id
    where a.is_active=1
    <if test="practitionerId != null">
      and a.assigned_practitioner_id = #{practitionerId}
    </if>
    order by a.id desc
  </select>
  <select id="findByIds" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where id in
    <foreach close=")" collection="practitionerIdList" index="index" item="item" open="(" separator=",">
      #{item}
    </foreach>
  </select>
    <select id="findForSubordinateIdByPractitionerId" resultMap="BaseResultMap">
        SELECT p.*
            from ag_acl_practitioner p
            LEFT JOIN ag_acl_practitioner_subordinate_system s on p.subordinate_system_id = s.id
        where
            s.owner_practitioner_id = #{practitionerId,jdbcType=BIGINT}
            and p.is_active = 1
            and s.is_active = 1
    </select>
  <resultMap id="playerSalesActivityQuery" type="com.yd.dal.entity.practitioner.opportunity.PlayerSalesActivityInfo">
    <result column="practitionerId" property="practitionerId" />
    <result column="name" property="name" />
    <result column="scoreDay" property="scoreDay" />
    <result column="scoreWeek" property="scoreWeek" />
    <result column="scoreMonth" property="scoreMonth" />
    <result column="addOpportunityNum" property="addOpportunityNum" />
  </resultMap>
  <select id="playerSalesActivityQuery" resultMap="playerSalesActivityQuery">
    select
      p.id practitionerId,
      p.name name ,
      sum(case when to_days(t.track_time)=to_days(now()) then t.track_score  else 0 end) scoreDay,
      sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d')) = YEARWEEK(now()) then t.track_score  else 0 end) scoreWeek,
      sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then t.track_score  else 0 end) scoreMonth,
      count(DISTINCT case when YEARWEEK(date_format(a.created_at,'%Y-%m-%d')) = YEARWEEK(now()) then a.id end) addOpportunityNum
    from ag_acl_practitioner p
      left join ag_mkt_leads_assigned_track t on p.id = t.practitioner_id
      left join  ag_mkt_leads_assigneds a on a.id = t.leads_assigned_id
      left join  ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
    where
      s.owner_practitioner_id = #{practitionerId,jdbcType=BIGINT}
      and s.is_active=1
      and p.is_active=1
      and not EXISTS (select t.leads_assigned_id
        from ag_mkt_leads_assigned_track t
        where t.md_drop_option_id = #{optionsId,jdbcType=BIGINT}
        and t.practitioner_id = p.id
        and a.id = t.leads_assigned_id)
    group by p.id
    order by scoreDay desc ,scoreWeek desc ,scoreMonth desc ,addOpportunityNum desc
  </select>
    <select id="findBySubordinateSystemId" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_acl_practitioner
      where is_active = 1
      <if test="systemType == 'S2'">
        and s2_id = #{subordinateSystemId,jdbcType=BIGINT}
      </if>
      <if test="systemType == 'S1'">
        and subordinate_system_id = #{subordinateSystemId,jdbcType=BIGINT}
      </if>

    </select>
  <select id="findMobileNoByPractitionerId" resultType="java.lang.String">
    select c.mobile_no
    from ag_acl_practitioner p left join  ag_acl_customer c on p.customer_id = c.id and c.is_active = 1
    where p.id = #{practitionerId,jdbcType=BIGINT} limit 1
  </select>
  <select id="findByMobileNoE" resultType="com.yd.dal.entity.customer.practitioner.PractitionerBasicInfo">
    SELECT
      p.SAL_MST_ID as practitionerId,
      p.SAL_MST_NAME as name,
      p.SAL_MST_GENDER as gender,
      p.SAL_MST_ID_TYPE as titleCode,
      p.SAL_MST_OFFICIAL_TITLE as titleName
    FROM sal001 p
    where p.SAL_MST_MOBILE like concat(concat('%',#{mobileNo,jdbcType=VARCHAR}),'%') limit 1
  </select>
  <select id="getPractitionerRankInfoByPractitionerIdEG" resultType="com.yd.dal.entity.customer.practitioner.PractitionerRankInfo">
    SELECT
    p.SAL_MST_ID as practitionerId ,
    p.SAL_MST_NAME as name ,-- 姓名
    p.SAL_MST_ID_TYPE as titleCode,-- 职称code
    p.SAL_MST_OFFICIAL_TITLE as titleName,-- 职称名称
    spc.SPC_DIV_NAME as spcDivName,-- 体系id
    ifnull(count(distinct policy.INS_MST_ID),0) as count,-- 保单id
    ifnull(sum(policy.INS_MST_TOTAL_PREIUM),0) as fyp,-- 保费
    ifnull(sum(policy.INS001_FYCTOTAL),0) as fyc  -- 应发佣金
    FROM
    ins001 policy INNER JOIN mon025 m on policy.INS_MST_ID = m.MON025_002 and m.MON025_109 IN (1,2,3,4,7) AND m.MON025_303 = 1 AND m.mon025_108 = 'S01'
    INNER JOIN (
    SELECT mon025_002,MAX(MON025_405) as MON025_405 FROM mon025 group by mon025_002 ORDER BY mon025_002
    ) b on m.mon025_002 = b.mon025_002 AND m.MON025_405 = b.MON025_405
    INNER JOIN sal001 p on m.MON025_007 = p.SAL_MST_ID -- 业务员id
    inner join spc004 spc on m.MON025_006 = spc.SPC_DIV_ID -- 体系
    where p.SAL_MST_ID = #{practitionerIdEG,jdbcType=VARCHAR}
    <choose>
      <when test="time == 1">
        and DATE_FORMAT(policy.INS_MST_ACCEPT_DATE, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
      </when>
      <when test="time == 3">
        and QUARTER(policy.INS_MST_ACCEPT_DATE) = QUARTER(NOW()) AND year(policy.INS_MST_ACCEPT_DATE)=year(now())
      </when>
      <otherwise>
        and YEAR(policy.INS_MST_ACCEPT_DATE) = YEAR(now())
      </otherwise>
    </choose>
  </select>
  <select id="getPractitionerRankInfoByPractitionerIdYD" resultType="com.yd.dal.entity.customer.practitioner.PractitionerRankInfo">
    SELECT
    p.customer_id as customerId,
    p.id as practitionerIdForOnLine ,
    p.name as name ,-- 姓名
    ss.name as spcDivName,-- 体系id
    ifnull(count(f.id),0) as count,-- 保单id
    ifnull(sum(f.order_price),0) as fyp,-- 保费
    ifnull(sum(f.fyc_amount),0) as fyc  -- 应发佣金
    FROM ag_acl_customer_fortune f 
    INNER JOIN ag_po_order o ON f.order_id = o.id and o.status = 3 and o.order_price &gt; 0
    inner JOIN ag_acl_practitioner p ON f.customer_id = p.customer_id
    left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    left join ag_acl_practitioner_subordinate_system ss on p.subordinate_system_id = ss.id
    WHERE p.id = #{practitionerId,jdbcType=BIGINT} and f.drop_option_code IN('S01','C01') and o.payment_status =3 
    <choose>
      <when test="time == 1">
        and DATE_FORMAT(f.order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
      </when>
      <when test="time == 3">
        and QUARTER(f.order_date) = QUARTER(NOW()) AND year(f.order_date)=year(now())
      </when>
      <otherwise>
        and YEAR(f.order_date) = YEAR(now())
      </otherwise>
    </choose>
<!--     <choose> -->
<!--       <when test="platform == 1"> -->
<!--         and o.product_category_id != 8  -->
<!--       </when> -->
<!--       <otherwise> -->
<!--         and o.product_category_id = 8 -->
<!--       </otherwise> -->
<!--     </choose> -->
    
  </select>
  <select id="findPayScaleByPractitionerEG" resultType="com.yd.api.practitioner.vo.payscale.PayScaleInfo">
    select DISTINCT
      t.MON_SH_ID as monShId,
      t.FK_SAL_MST_ID as salMstId,
      t.FK_DRP_TITLE_CODE as drpTitleCode,
      t.MON_DTL_TYPE as monDtlType,
      t.MON_DTL_ITEM as monDtlItem,
      t.MON_DTL_PERIOD as monDtlPeriod,
      ifnull(t.MON_DTL_AMOUNT,0) as monDtlAmount,
      ifnull(t.MON_DTL_RAMOUNT,0) as monDtlRAmount,
      date_format(t.CREATE_DATE, '%Y-%m-%d %H:%i:%s') as createDate,
      t.CREATE_USER as createUser
    from mon011 t INNER JOIN mon025 m ON m.MON025_007 = t.FK_SAL_MST_ID
    INNER JOIN mon006 cl on t.MON_DTL_PERIOD = cl.MON_CLS_PERIOD and cl.MON_CLS_TYPE = 1
    WHERE m.MON025_109 = 7 AND t.FK_SAL_MST_ID = #{practitionerIdEG,jdbcType=VARCHAR}
  </select>

  <select id="findFilePathByMonShId" resultType="com.yd.dal.entity.practitioner.payscale.PayScaleBasicInfo">
    select
        t.MON_SH_ID as monShId,
        t.FK_SAL_MST_ID as salMstId,
        t.FK_DRP_TITLE_CODE as drpTitleCode,
        t.MON_DTL_TYPE as monDtlType,
        t.MON_DTL_ITEM as monDtlItem,
        t.MON_DTL_PERIOD as monDtlPeriod,
        ifnull(t.MON_DTL_AMOUNT,0) as monDtlAmount,
        ifnull(t.MON_DTL_RAMOUNT,0) as monDtlRAmount,
        date_format(t.CREATE_DATE, '%Y-%m-%d %H:%i:%s') as createDate,
        t.CREATE_USER as createUser,
        t.UPLOAD_PATH as uploadPath,
        t.UPLOAD_TIME as uploadTime
        from mon011 t where t.MON_SH_ID = #{monShId,jdbcType=INTEGER}
  </select>

  <select id="findByIdEG" resultMap="base_result_map_practitioner_info">
    SELECT
    p.SAL_MST_ID as practitionerId,
    p.SAL_MST_NAME as name,
    p.SAL_MST_GENDER as gender,
    p.SAL_MST_MOBILE as mobileNo
    FROM sal001 p
    where p.SAL_MST_ID = #{practitionerIdEG,jdbcType=VARCHAR} limit 1
  </select>

  <select id="findPractitionerByLeadsAssignedId" resultMap="BaseResultMap">
    select p.*
    from ag_acl_practitioner p
        left join ag_mkt_leads_assigneds a on a.assigned_practitioner_id = p.id
    where a.id = #{leadsAssignedId,jdbcType=BIGINT}
  </select>
    <select id="findAll" parameterType="java.lang.Long" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_acl_practitioner
    </select>

  <select id="queryMentoredByPractitionerId" resultType="java.lang.Long" statementType="CALLABLE">
      CALL proc_queryMentoredByPractitionerId(#{practitionerId,jdbcType=BIGINT});
  </select>

  <select id="isTeamLeader" resultType="java.lang.Integer">
      select count(1) from  ag_acl_practitioner_subordinate_system where owner_practitioner_id = #{practitionerId,jdbcType=BIGINT}
  </select>

  <select id="queryTeamAllPractitionerId" resultType="Long">
<!--      SELECT id from ag_acl_practitioner
          where is_active = 1
          and subordinate_system_id =
          (select subordinate_system_id from ag_acl_practitioner where id = #{practitionerId,jdbcType=BIGINT})
      <if test="isActive != null">
        and is_active = #{isActive,jdbcType=INTEGER}
      </if>-->
    select
           t.id
    from ag_acl_practitioner  t
        LEFT JOIN ag_acl_practitioner_subordinate_system s ON t.s2_id = s.id or t.subordinate_system_id = s.id or t.s3_id = s.id
    WHERE s.id in (select subordinate_system_id from ag_acl_practitioner where id = #{practitionerId,jdbcType=BIGINT})
      and t.is_active=1;
  </select>

  <select id="findPractitionerInfoByPractitionerId" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo
    from ag_acl_customer c inner join ag_acl_practitioner p on c.id = p.customer_id
                           left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
                           left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
                           left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.id = #{practitionerId}
  </select>

  <select id="selectByMobileNo" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where mobile_no = #{mobileNo,jdbcType=VARCHAR,typeHandler=com.yd.util.deshandler.DESTypeHandler}
  </select>

  <resultMap id="teamMemberDetail" type="com.yd.api.practitioner.vo.subordinate.TeamMemberDetail">
    <result column="id" property="practitionerId" />
    <result column="name" property="name" />
    <result column="mobile_no" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler" />
    <result column="practitionerLevel" property="practitionerLevel" />
    <result column="practitionerTitle" property="practitionerTitle" />
    <result column="branch_name" property="branch" />
    <result column="system" property="system" />
    <result column="mentor" property="mentor" />
    <result column="introducer" property="introducer" />
    <result column="gender" property="gender" />
    <result column="age" property="age" />
    <result column="practitioner_birthdate" property="birthDate" />
    <result column="educationLevel" property="educationLevel" />
    <result column="province" property="province" />
    <result column="city" property="city" />
    <result column="id_type" property="idType" />
    <result column="id_no" property="idNo" typeHandler="com.yd.util.deshandler.DESTypeHandler" />
    <result column="effective_start_date" property="effectiveStartDate" />
    <result column="practitioner_reg_no" property="practitionerRegNo" />
  </resultMap>
  <select id="queryTeamMemberDetailList" resultMap="teamMemberDetail">
      select
          p.id,
          p.name,
          p.mobile_no,
          s.drop_option_code practitionerLevel,
          s.drop_option_name practitionerTitle,
          b.branch_name,
          c.name deptName,
          sys.name system,
          (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s1_id) s1system,
          (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s2_id) s2system,
          (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s3_id) s3system,
          p1.name mentor,
          p2.name introducer,
          case when p.gender=1 then '男' else '女' end gender,
          TIMESTAMPDIFF(YEAR, p.practitioner_birthdate, CURDATE()) age,
          p.practitioner_birthdate,
          c.code_name educationLevel,
          p.province_name province,
          p.city_name city,
          p.id_type,
          p.id_no,
          p.effective_start_date,
          p.practitioner_reg_no,
          sys.system_type as systemType,sys.owner_practitioner_id as ownerPractitionerId,
          p.subordinate_system_id sId

      from
      (select
        id,name,mobile_no,gender,practitioner_birthdate,province_name,city_name,id_type,id_no,effective_start_date,
        subordinate_system_id,insurer_branch_id,mentor_id,introducer_id,practitioner_reg_no,education_level,s1_id,s3_id,s2_id,dept_id
        from ag_acl_practitioner
        where id in
        <foreach close=")" collection="list" index="index" item="item" open="(" separator=",">
            #{item}
        </foreach>
      ) p
      left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
      left join
      (select t1.practitioner_id, o.drop_option_name, o.drop_option_code
          from ag_acl_practitioner_setting t1 left join ag_md_drop_options o on t1.practitioner_level = o.id
      )s on p.id = s.practitioner_id
      left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
      left join ag_acl_insurer_branch_dept c on p.dept_id = c.id
      left join ag_acl_practitioner p1 on p.mentor_id = p1.id
      left join ag_acl_practitioner p2 on p.introducer_id = p2.id
      left join (select * from ag_md_code where code_type = 'EducationLevel') c on p.education_level = c.code_code
      where sys.is_active=1
  </select>

  <select id="findLeaveOfficeByPractitionerId" resultType="string">
    select mobile_no from ag_acl_practitioner where is_active = 0
                                                and subordinate_system_id = (select subordinate_system_id from ag_acl_practitioner where   id = #{practitionerId,jdbcType=BIGINT})
  </select>

  <select id="queryAllLifePractitioner" resultMap="BaseResultMap">
      SELECT p.*
      from ag_acl_practitioner p
      left join ag_acl_practitioner_setting s on s.practitioner_id = p.id
      where s.practitioner_type_id = 28
      and p.is_active=1
  </select>

  <select id="findPractitionerInfoByPractitionerCode" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      sys.name as subsystem,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo,
      p.mobile_no,
      p.can_see_salary_list as canSeeSalaryList
    from ag_acl_practitioner p
       left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
       left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
       left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
       left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.practitioner_code in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
    and p.is_active = 1
  </select>

  <select id="findPractitionerInfoByNameList" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      sys.name as subsystem,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo,
      p.mobile_no,
      p.can_see_salary_list as canSeeSalaryList
    from ag_acl_practitioner p
      left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
      left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
      left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
      left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.name in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
    and p.is_active = 1

  </select>

  <select id="findPractitionerInfoByMobileList" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      sys.name as subsystem,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo,
      p.mobile_no,
      p.mentor_id mentorId,
      c.yd_wechat_openid,
      p.can_see_salary_list as canSeeSalaryList
    from ag_acl_practitioner p
      left join ag_acl_customer c on c.id = p.customer_id
      left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
      left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
      left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
      left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.mobile_no in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item,typeHandler=com.yd.util.deshandler.DESTypeHandler}
    </foreach>
    and p.is_active = 1
  </select>

  <select id="findPractitionerInfoByIdList" resultMap="practitioner_info_map">
    select
      p.customer_id as customerId,
      p.id as practitionerId,
      p.name as name,
      p.gender as gender,
      s.practitioner_type_id as practitionerType,
      sys.name as subsystem,
      o.drop_option_code as levelCode,
      o.drop_option_name as levelName,
      b.branch_name as insurerBranchName,
      p.subordinate_system_id as subordinateId,
      p.wechat_id as weChatId,
      p.qq_id as qqId,
      p.practitioner_reg_no as practitionerRegNo,
      p.mobile_no,
      p.mentor_id mentorId,
      c.yd_wechat_openid,
      p.can_see_salary_list as canSeeSalaryList
    from ag_acl_practitioner p
    left join ag_acl_customer c on c.id = p.customer_id
    left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
    left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
    left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
    and p.is_active = 1
  </select>

  <select id="queryEGHistorySalaryPDFByPractitionerId" resultType="com.yd.api.practitioner.vo.payscale.PayScaleInfo">
    select  p.id,
            f.file_path pdfOssPath,
            f.yearMonth monDtlPeriod,
            o.drop_option_name drpTitleCode,
            '实发薪水' monDtlItem

    from (select f.file_name, f.file_path,
				SUBSTRING(f.file_name  ,8 ,6) yearMonth,
				SUBSTRING(f.file_name  ,15 ,9) practitionerCode
                from ag_acl_file_upload f where f.target_no = 'payScale'
              ) f
    left join  ag_acl_practitioner p on p.practitioner_code = f.practitionerCode
    left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
    left join ag_acl_practitioner_setting s on p.id = s.practitioner_id
    left join ag_md_drop_options o on s.practitioner_level = o.id
    where p.id = #{practitionerId}
  </select>


  <select id="queryTeamMemberDetailRaiseList" resultMap="teamMemberDetail">
    select
    p.id,
    p.name,
    p.mobile_no,
    s.drop_option_code practitionerLevel,
    s.drop_option_name practitionerTitle,
    b.branch_name,
    c.name deptName,
    sys.name system,
    (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s1_id) s1system,
    (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s2_id) s2system,
    (select name from ag_acl_practitioner_subordinate_system s where s.id=p.s3_id) s3system,
    p1.name mentor,
    p2.name introducer,
    case when p.gender=1 then '男' else '女' end gender,
    TIMESTAMPDIFF(YEAR, p.practitioner_birthdate, CURDATE()) age,
    p.practitioner_birthdate,
    c.code_name educationLevel,
    p.province_name province,
    p.city_name city,
    p.id_type,
    p.id_no,
    p.effective_start_date,
    p.practitioner_reg_no,
    sys.system_type as systemType,sys.owner_practitioner_id as ownerPractitionerId,
    p.subordinate_system_id sId

    from
    (select
    id,name,mobile_no,gender,practitioner_birthdate,province_name,city_name,id_type,id_no,effective_start_date,
    subordinate_system_id,insurer_branch_id,mentor_id,introducer_id,practitioner_reg_no,education_level,s1_id,s3_id,s2_id,dept_id,raise_id
    from ag_acl_practitioner
    where raise_id in
    <foreach close=")" collection="list" index="index" item="item" open="(" separator=",">
      #{item}
    </foreach>
    ) p
    left join ag_acl_practitioner_subordinate_system sys on p.subordinate_system_id = sys.id
    left join
    (select t1.practitioner_id, o.drop_option_name, o.drop_option_code
    from ag_acl_practitioner_setting t1 left join ag_md_drop_options o on t1.practitioner_level = o.id
    )s on p.id = s.practitioner_id
    left join ag_acl_insurer_branch b on p.insurer_branch_id = b.id
    left join ag_acl_insurer_branch_dept c on p.dept_id = c.id
    left join ag_acl_practitioner p1 on p.mentor_id = p1.id
    left join ag_acl_practitioner p2 on p.introducer_id = p2.id
    left join (select * from ag_md_code where code_type = 'EducationLevel') c on p.education_level = c.code_code
  </select>

  <select id="findPractitionerByRaise" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where raise_id = #{practitionerId,jdbcType=BIGINT}
  </select>
  
  <select id="findUnionSalesPractitionerByUnionSalesId" resultType="com.yd.api.practitioner.vo.unionSales.UnionSalesPractitionerInfo">
    select
        t.id as id,
        t.union_sales_id as unionSalesId,
        t.practitioner_id as practitionerId,
        t.practitioner_name as practitionerName,
        t.commission_rate as commissionRate,
        t.achievements_rate as achievementsRate,
        t.sign_status as signStatus,
        date_format(t.sign_date,'%Y-%m-%d %H:%i:%s') as signDate,
        t.sign_url as signUlr,
        t.signX as signX,
        t.signY as signY,
        t.flag as flag,
        t.is_active as isActive,
        (SELECT COUNT(a.id) FROM ag_acl_union_sales_practitioner a WHERE a.is_active = 1 AND a.sign_status ='3' AND a.union_sales_id = t.union_sales_id) as signCount,
        us.sign_url as salesSignUlr,
        us.insurer_name as insurerName,
		us.product_name as productName,
		us.proposal_no as proposalNo,
		us.policy_no as policyNo,
		us.policyholder_name as policyholderName
        from ag_acl_union_sales_practitioner t,ag_acl_union_sales us 
        where us.id = t.union_sales_id and us.is_active = 1 and t.is_active = 1 and t.union_sales_id= #{unionSalesId} order by t.id
  </select>
  
  <update id="updateUnionSalesPractitioner">
    update ag_acl_union_sales_practitioner
		set sign_url = #{ossPathImg},
			signX = #{singX},
			signY = #{singY},
			sign_status = #{signStatus},
			sign_date = NOW()
    where id = #{unionSalesPractitionerId}
  </update>
  
  <update id="updateUnionSales">
    update ag_acl_union_sales
		set sign_url = #{ossPath},
			sign_status = #{signStatus},
			sign_date = NOW()
    where id = #{unionSalesId}
  </update>
  
  <select id="businessCard" resultType="com.yd.api.practitioner.vo.businessCard.PractitionerBusinessCardResponseVO">
    select distinct
    	t.id practitionerId,t.name practitionerName,t.name_en practitionerNameEn,
		t.cert_list certList,
		ps.practitioner_level,mdo.drop_option_name levelName,mdo.drop_option_code levelCode,mdo.drop_option_remark levelNameEn,fu.file_path wxQRImgUrl,
		t.insurer_id insurerId,CONCAT(i.name,IFNULL(ib.branch_name,ibd.name)) insurerName, i.name_abbre insurerAbbre,
		ifnull(ib.address,ibd.address) insurerAddress,
		t.mobile_no mobileNo,t.email email,t.email_is_active emailIsActive
	from ag_acl_practitioner t
	left join ag_acl_insurer i on i.id=t.insurer_id
	left join ag_acl_insurer_branch ib on ib.id=t.insurer_branch_id
	left join ag_acl_insurer_branch_dept ibd on ibd.id=t.dept_id
	left join ag_acl_practitioner_setting ps on ps.practitioner_id = t.id
	left join ag_md_drop_options mdo on mdo.id = ps.practitioner_level
	left join ag_acl_file_upload fu on fu.target_type='1' and fu.target_id=t.id and fu.target_use_for='9' AND fu.is_active='1' 
	where t.id = #{practitionerId} and t.is_active='1'
  </select>
  
  <select id="findCertificate" resultType="com.yd.api.practitioner.vo.businessCard.Certificate">
    select    
      t.id id,
      SUBSTRING_INDEX(t.cert_name,'-',-1) certName,
      t.cert_abbre certNameEn,
      t.image_path imagePath
    from ag_md_certificate t
    where t.id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
    and t.is_active = 1
  </select>
  
</mapper>
