<?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.AclPractitionerSubordinateSystemMapper">
  <resultMap id="BaseResultMap" type="com.yd.dal.entity.customer.AclPractitionerSubordinateSystem">
    <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_code" jdbcType="VARCHAR" property="subordinateSystemCode" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="name_en" jdbcType="VARCHAR" property="nameEn" />
    <result column="owner_practitioner_id" jdbcType="BIGINT" property="ownerPractitionerId" />
    <result column="contact_name" jdbcType="VARCHAR" property="contactName" />
    <result column="contact_no" jdbcType="VARCHAR" property="contactNo" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="is_active" jdbcType="INTEGER" property="isActive" />
    <result column="parent_id" jdbcType="BIGINT" property="parentId" />
    <result column="system_ype" jdbcType="VARCHAR" property="systemType" />
    <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" />
  </resultMap>
  <sql id="Base_Column_List">
    id, insurer_id, insurer_branch_id, dept_id, subordinate_system_code, `name`, name_en, 
    owner_practitioner_id, contact_name, contact_no, remark, is_active, created_at, created_by, 
    updated_at, updated_by
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from ag_acl_practitioner_subordinate_system
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from ag_acl_practitioner_subordinate_system
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.customer.AclPractitionerSubordinateSystem" useGeneratedKeys="true">
    insert into ag_acl_practitioner_subordinate_system (insurer_id, insurer_branch_id, dept_id, 
      subordinate_system_code, `name`, name_en, 
      owner_practitioner_id, contact_name, contact_no, 
      remark, is_active, created_at, 
      created_by, updated_at, updated_by
      )
    values (#{insurerId,jdbcType=BIGINT}, #{insurerBranchId,jdbcType=BIGINT}, #{deptId,jdbcType=BIGINT}, 
      #{subordinateSystemCode,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{nameEn,jdbcType=VARCHAR}, 
      #{ownerPractitionerId,jdbcType=BIGINT}, #{contactName,jdbcType=VARCHAR}, #{contactNo,jdbcType=VARCHAR}, 
      #{remark,jdbcType=VARCHAR}, #{isActive,jdbcType=INTEGER}, #{createdAt,jdbcType=TIMESTAMP}, 
      #{createdBy,jdbcType=BIGINT}, #{updatedAt,jdbcType=TIMESTAMP}, #{updatedBy,jdbcType=BIGINT}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.customer.AclPractitionerSubordinateSystem" useGeneratedKeys="true">
    insert into ag_acl_practitioner_subordinate_system
    <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="subordinateSystemCode != null">
        subordinate_system_code,
      </if>
      <if test="name != null">
        `name`,
      </if>
      <if test="nameEn != null">
        name_en,
      </if>
      <if test="ownerPractitionerId != null">
        owner_practitioner_id,
      </if>
      <if test="contactName != null">
        contact_name,
      </if>
      <if test="contactNo != null">
        contact_no,
      </if>
      <if test="remark != null">
        remark,
      </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>
    </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="subordinateSystemCode != null">
        #{subordinateSystemCode,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="nameEn != null">
        #{nameEn,jdbcType=VARCHAR},
      </if>
      <if test="ownerPractitionerId != null">
        #{ownerPractitionerId,jdbcType=BIGINT},
      </if>
      <if test="contactName != null">
        #{contactName,jdbcType=VARCHAR},
      </if>
      <if test="contactNo != null">
        #{contactNo,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        #{remark,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>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.customer.AclPractitionerSubordinateSystem">
    update ag_acl_practitioner_subordinate_system
    <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="subordinateSystemCode != null">
        subordinate_system_code = #{subordinateSystemCode,jdbcType=VARCHAR},
      </if>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      </if>
      <if test="nameEn != null">
        name_en = #{nameEn,jdbcType=VARCHAR},
      </if>
      <if test="ownerPractitionerId != null">
        owner_practitioner_id = #{ownerPractitionerId,jdbcType=BIGINT},
      </if>
      <if test="contactName != null">
        contact_name = #{contactName,jdbcType=VARCHAR},
      </if>
      <if test="contactNo != null">
        contact_no = #{contactNo,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        remark = #{remark,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>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.customer.AclPractitionerSubordinateSystem">
    update ag_acl_practitioner_subordinate_system
    set insurer_id = #{insurerId,jdbcType=BIGINT},
      insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT},
      dept_id = #{deptId,jdbcType=BIGINT},
      subordinate_system_code = #{subordinateSystemCode,jdbcType=VARCHAR},
      `name` = #{name,jdbcType=VARCHAR},
      name_en = #{nameEn,jdbcType=VARCHAR},
      owner_practitioner_id = #{ownerPractitionerId,jdbcType=BIGINT},
      contact_name = #{contactName,jdbcType=VARCHAR},
      contact_no = #{contactNo,jdbcType=VARCHAR},
      remark = #{remark,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}
    where id = #{id,jdbcType=BIGINT}
  </update>

  <resultMap id="practitioner_subordinate_map" type="com.yd.dal.entity.customer.practitioner.PractitionerSubordinateInfo">
    <result column="subordinateId" property="subordinateId"/>
    <result column="subordinateName" property="subordinateName"/>
    <result column="subordinateCode" property="subordinateCode"/>
    <result column="ownerId" property="ownerId"/>
    <result column="subordinateLeader" property="subordinateLeader"/>
    <result column="contactName" property="contactName"/>
    <result column="contactNo" property="contactNo"/>
    <result column="remark" property="remark"/>
  </resultMap>

  <select id="findSubordinateInfo" resultMap="practitioner_subordinate_map">
    SELECT
      s.id as subordinateId,
      s.name as subordinateName,
      s.subordinate_system_code as subordinateCode,
      s.owner_practitioner_id as ownerId,
      p.name as subordinateLeader,
      s.contact_name as contactName,
      s.contact_no as contactNo,
      s.remark as remark
    FROM ag_acl_practitioner_subordinate_system s left join ag_acl_practitioner p on s.owner_practitioner_id = p.id
    where s.is_active = 1
      and s.id = #{subordinateId}

  </select>
  <select id="findByPractitionerId" resultMap="BaseResultMap">
    select s.*
    from ag_acl_practitioner_subordinate_system s
    left join ag_acl_practitioner p on p.subordinate_system_id = s.id
    where p.id = #{practitionerId,jdbcType=BIGINT}
  </select>
  <select id="findByOwnerPractitionerId" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner_subordinate_system
    where owner_practitioner_id = #{practitionerId,jdbcType=BIGINT}
  </select>
  <select id="findByOwner" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner_subordinate_system
    where owner_practitioner_id = #{practitionerId,jdbcType=BIGINT} and is_active = 1
  </select>
    <select id="findByOwnerE" resultType="com.yd.dal.entity.customer.practitioner.PractitionerSubordinateInfo">
      SELECT
        p.SPC_DIV_NAME as subordinateName,
        p.SPC_DIV_ID as subordinateCode,
        p.FK_DIV_BOSS_ID as subordinateLeader
      FROM spc004 p
      where p.FK_DIV_BOSS_ID = #{practitionerId,jdbcType=VARCHAR}
    </select>
  <select id="findStatisticsE"
          resultType="com.yd.dal.entity.customer.practitioner.SubordinateSystemMemberInfoE">
    SELECT
    p.SAL_MST_NAME as name ,-- 姓名
    p.SAL_MST_ID_TYPE as titleCode,-- 职称code
    p.SAL_MST_OFFICIAL_TITLE as titleName,-- 职称名称
    p.SAL_MST_MOBILE as mobileNo,
    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
    WHERE p.FK_SPC_DIV_ID = #{subordinateIdForEGolden,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>
    group by p.SAL_MST_ID;
  </select>

  <select id="findStatisticsEBymobileList" resultType="com.yd.dal.entity.customer.practitioner.SubordinateSystemMemberInfoE">
    SELECT
      p.SAL_MST_NAME as name ,
      p.SAL_MST_ID_TYPE as titleCode,
      p.SAL_MST_OFFICIAL_TITLE as titleName,
      p.SAL_MST_MOBILE as mobileNo,
      ifnull(count(distinct policy.INS_MST_ID),0) as count,
      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
    WHERE 1=1
    and p.SAL_MST_MOBILE in
    <foreach collection="list" item="mobile" index="index" open="(" close=")" separator=",">
      #{mobile,jdbcType=BIGINT}
    </foreach>
    <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="findByDeptId" resultMap="BaseResultMap">
    SELECT t.* FROM ag_acl_practitioner_subordinate_system t WHERE t.dept_id =#{deptId,jdbcType=BIGINT}   and t.is_active = 1
  </select>
  <select id="findByInsurerBranchId" resultMap="BaseResultMap">
    SELECT t.* FROM ag_acl_practitioner_subordinate_system t WHERE t.insurer_branch_id =#{insurerBranchId,jdbcType=BIGINT} and t.dept_id is null  and t.is_active = 1
  </select>
  <select id="findDeptIdAndInsureBranchIdNull" resultMap="BaseResultMap">
    SELECT t.* FROM ag_acl_practitioner_subordinate_system t
               WHERE t.insurer_branch_id is null and t.dept_id is null and t.is_active = 1 and t.insurer_id= #{insurerId,jdbcType=BIGINT}
    union all
    SELECT t.* FROM ag_acl_practitioner_subordinate_system t
               WHERE t.insurer_branch_id is null and t.dept_id is not null and t.is_active = 1 and t.insurer_id= #{insurerId,jdbcType=BIGINT}
  </select>
  <select id="findDeptIdNotNull" resultMap="BaseResultMap">
    SELECT t.* FROM ag_acl_practitioner_subordinate_system t WHERE t.insurer_branch_id is  null and t.dept_id  is not null and t.is_active = 1 and t.insurer_id=  #{insurerId,jdbcType=BIGINT}
  </select>

  <select id="findAll" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_acl_practitioner_subordinate_system
  </select>



</mapper>
