<?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">
    <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="VARCHAR" property="remark" />
    <result column="customer_id" jdbcType="BIGINT" property="customerId" />
    <result column="employee_no" jdbcType="VARCHAR" property="employeeNo" />
    <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="VARCHAR" property="bioIntro" />
    <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="mentor_id" jdbcType="BIGINT" property="mentorId" />
    <result column="introducer_id" jdbcType="BIGINT" property="introducerId" />
    <result column="gender" jdbcType="INTEGER" property="gender" />
  </resultMap>
  <sql id="Base_Column_List">
    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, is_active, created_at, created_by, updated_at, updated_by, province_id, 
    province_name, city_id, city_name, cert_list, bio_intro, wechat_id, qq_id, is_profile_show, 
    is_name_show, is_mobile_show, education_level, mentor_id, introducer_id, gender
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from ag_acl_practitioner
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    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">
    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, is_active, created_at, 
      created_by, updated_at, updated_by, 
      province_id, province_name, city_id, 
      city_name, cert_list, bio_intro, 
      wechat_id, qq_id, is_profile_show, 
      is_name_show, is_mobile_show, education_level, 
      mentor_id, introducer_id, gender
      )
    values (#{insurerId,jdbcType=BIGINT}, #{insurerBranchId,jdbcType=BIGINT}, #{deptId,jdbcType=BIGINT}, 
      #{subordinateSystemId,jdbcType=BIGINT}, #{practitionerCode,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{mobileNo,jdbcType=VARCHAR}, #{idTypeId,jdbcType=BIGINT}, #{idType,jdbcType=VARCHAR}, 
      #{idNo,jdbcType=VARCHAR}, #{practitionerBirthdate,jdbcType=DATE}, #{practitionerRegNo,jdbcType=VARCHAR}, 
      #{practitionerRegCompany,jdbcType=VARCHAR}, #{effectiveStartDate,jdbcType=DATE}, 
      #{effectiveEndDate,jdbcType=DATE}, #{remark,jdbcType=VARCHAR}, #{customerId,jdbcType=BIGINT}, 
      #{employeeNo,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=VARCHAR}, 
      #{wechatId,jdbcType=VARCHAR}, #{qqId,jdbcType=VARCHAR}, #{isProfileShow,jdbcType=INTEGER}, 
      #{isNameShow,jdbcType=INTEGER}, #{isMobileShow,jdbcType=INTEGER}, #{educationLevel,jdbcType=VARCHAR}, 
      #{mentorId,jdbcType=BIGINT}, #{introducerId,jdbcType=BIGINT}, #{gender,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.customer.AclPractitioner" useGeneratedKeys="true">
    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="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="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="mentorId != null">
        mentor_id,
      </if>
      <if test="introducerId != null">
        introducer_id,
      </if>
      <if test="gender != null">
        gender,
      </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=VARCHAR},
      </if>
      <if test="customerId != null">
        #{customerId,jdbcType=BIGINT},
      </if>
      <if test="employeeNo != null">
        #{employeeNo,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=VARCHAR},
      </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="mentorId != null">
        #{mentorId,jdbcType=BIGINT},
      </if>
      <if test="introducerId != null">
        #{introducerId,jdbcType=BIGINT},
      </if>
      <if test="gender != null">
        #{gender,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.customer.AclPractitioner">
    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=VARCHAR},
      </if>
      <if test="customerId != null">
        customer_id = #{customerId,jdbcType=BIGINT},
      </if>
      <if test="employeeNo != null">
        employee_no = #{employeeNo,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=VARCHAR},
      </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="mentorId != null">
        mentor_id = #{mentorId,jdbcType=BIGINT},
      </if>
      <if test="introducerId != null">
        introducer_id = #{introducerId,jdbcType=BIGINT},
      </if>
      <if test="gender != null">
        gender = #{gender,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.customer.AclPractitioner">
    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=VARCHAR},
      customer_id = #{customerId,jdbcType=BIGINT},
      employee_no = #{employeeNo,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=VARCHAR},
      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},
      mentor_id = #{mentorId,jdbcType=BIGINT},
      introducer_id = #{introducerId,jdbcType=BIGINT},
      gender = #{gender,jdbcType=INTEGER}
    where id = #{id,jdbcType=BIGINT}
  </update>

  <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" />
    <!--        <result column="mobile_no" property="titleCode" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>-->
    <result column="titleName" property="titleName"/>
    <result column="gender" property="gender"/>
  </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}
  </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
    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
                           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>

<!--  <select id="getPractitionerRankInfoForOffline" resultMap="practitioner_rank_map" resultType="java.lang.Integer">-->
<!--    SELECT-->
<!--    p.SAL_MST_ID as practitionerId ,-->
<!--    p.SAL_MST_NAME as name ,&#45;&#45; 姓名-->
<!--    md.DRP_TITLE_LEVEL as titleCode,&#45;&#45; 职称code-->
<!--    md.DRP_TITLE_NAME as titleName,&#45;&#45; 职称名称-->
<!--    spc.SPC_DIV_NAME as spcDivName,&#45;&#45; 体系id-->
<!--    ifnull(count(m.MON025_002),0) as count,&#45;&#45; 保单id-->
<!--    ifnull(sum(m.MON025_401),0) as fyp,&#45;&#45; 保费-->
<!--    ifnull(sum(policy.INS001_FYCTOTAL),0) as fyc  &#45;&#45; 应发佣金-->
<!--    FROM MON025 m INNER JOIN sal001 p on m.MON025_007 = p.SAL_MST_ID &#45;&#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 &#45;&#45; 保单id-->
<!--    inner join spc004 spc on m.MON025_006 = spc.SPC_DIV_ID &#45;&#45; 体系-->
<!--    WHERE m.MON025_109 IN (1,2,3,4,7) AND m.MON025_303 = 1 AND m.mon025_108 = 'S01'-->
<!--    <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>-->
<!--    group by p.SAL_MST_ID;-->
<!--  </select>-->

    <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
    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 (select t.* from ag_acl_customer_fortune t where t.drop_option_code = "S01" group by t.order_id ) f INNER JOIN ag_po_order o ON f.order_id = o.id and o.order_price > 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
    <if test="practitionerTypeId != null">
      and s.practitioner_type_id = #{practitionerTypeId}
    </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>
    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 > 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 f.drop_option_code = "S01"
    and c.login in
    <foreach collection="mobileSpecials" index="index" item="item" open="(" separator="," close=")">
      #{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>
    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="weChatId" property="weChatId"/>
    <result column="qqId" property="qqId"/>
    <result column="practitionerRegNo" property="practitionerRegNo"/>
  </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
    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}
  </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"/>
  </resultMap>
  <select id="ownOpportunityQuery" resultMap="opportunityQuery">
    SELECT
    a.customer_id opportunityId,
    c.NAME name,
    c.mobile_no 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
    FROM
    ag_mkt_leads_assigneds a
    left join ag_mkt_leads_expert_request r left join ag_mkt_leads_expert_assign ass on ass.leads_expert_request_id = r.id on r.customer_id = a.customer_id
    LEFT JOIN ag_mkt_leads_pool p ON p.customer_id = a.customer_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 collection="practitionerIdList" index="index" item="item" open="(" separator="," close=")">
      #{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 subordinate_system_id = #{subordinateSystemId,jdbcType=BIGINT} and is_active = 1
    </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}),'%')
  </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 (select t.* from ag_acl_customer_fortune t group by t.order_id ) f INNER JOIN ag_po_order o ON f.order_id = o.id and o.status = 3 and o.order_price > 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 = "S01"
    <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>
  </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
    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 subordinate_system_id =
          (select subordinate_system_id from ag_acl_practitioner where id = #{practitionerId,jdbcType=BIGINT})
  </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>

</mapper>