MEMBER 查看会员 SQL

<?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.zhong.member.dal.dao.member.slave.SlaveOperatorMapper">
  <resultMap id="OperatorTableMap" type="com.zhong.member.dal.Do.member.OperatorTableInfo">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="parent_oper_key" property="parentOperKey" jdbcType="BIGINT" />
    <result column="dm_role_type" jdbcType="TINYINT" property="dmRoleType" />
    <result column="fin_role_type" jdbcType="TINYINT" property="finRoleType" />
    <result column="biz_type" property="bizType" jdbcType="VARCHAR" />
    <result column="oper_company" jdbcType="VARCHAR" property="operCompany" />
    <result column="oper_department" jdbcType="VARCHAR" property="operDepartment" />
    <result column="oper_position" jdbcType="VARCHAR" property="operPosition" />
    <result column="oper_mobile" jdbcType="VARCHAR" property="operMobile" />
    <result column="oper_name" jdbcType="VARCHAR" property="operName" />
    <result column="lock_status" jdbcType="CHAR" property="lockStatus" />
    <result column="login_account" jdbcType="VARCHAR" property="loginAccount" />
  </resultMap>
  
  <resultMap id="DetailResultMap" type="com.zhong.member.dal.domain.member.Operator">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="parent_oper_key" property="parentOperKey" jdbcType="BIGINT" />
    <result column="org_key" jdbcType="BIGINT" property="orgKey" />
    <result column="dm_role_type" jdbcType="TINYINT" property="dmRoleType" />
    <result column="fin_role_type" jdbcType="TINYINT" property="finRoleType" />
    <result column="biz_type" property="bizType" jdbcType="VARCHAR" />
    <result column="oper_name" jdbcType="VARCHAR" property="operName" />
    <result column="oper_company" jdbcType="VARCHAR" property="operCompany" />
    <result column="oper_department" jdbcType="VARCHAR" property="operDepartment" />
    <result column="oper_position" jdbcType="VARCHAR" property="operPosition" />
    <result column="oper_employee_id" jdbcType="VARCHAR" property="operEmployeeId" />
    <result column="oper_business_card" jdbcType="VARCHAR" property="operBusinessCard" />
    <result column="oper_business_card_name" jdbcType="VARCHAR" property="operBusinessCardName" />
    <result column="oper_mobile" jdbcType="VARCHAR" property="operMobile" />
    <result column="oper_mail" jdbcType="VARCHAR" property="operMail" />
    <result column="card_type" jdbcType="CHAR" property="cardType" />
    <result column="card_no" jdbcType="VARCHAR" property="cardNo" />
    <result column="password_hash" jdbcType="VARCHAR" property="passwordHash" />
    <result column="lock_status" jdbcType="CHAR" property="lockStatus" />
    <result column="last_login_ip" jdbcType="BIGINT" property="lastLoginIp" />
    <result column="version" jdbcType="INTEGER" property="version" />
    <result column="dm_unlock_role_type" jdbcType="TINYINT" property="dmUnlockRoleType" />
    <result column="fin_unlock_role_type" jdbcType="TINYINT" property="finUnlockRoleType" />
    <result column="create_oper_id" jdbcType="BIGINT" property="createOperId" />
    <result column="create_oper_name" jdbcType="VARCHAR" property="createOperName" />
    <result column="edit_oper_id" jdbcType="BIGINT" property="editOperId" />
    <result column="edit_oper_name" jdbcType="VARCHAR" property="editOperName" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>
  
  <sql id="Base_Column_List">
    ID, parent_oper_key, org_key, dm_role_type, fin_role_type, biz_type, oper_name, oper_company, oper_department, 
    oper_position, oper_employee_id, oper_business_card, oper_business_card_name, oper_mobile, 
    oper_mail, card_type, card_no, password_hash, lock_status, last_login_ip, version, dm_unlock_role_type,fin_unlock_role_type,
    create_oper_id, create_oper_name, edit_oper_id, edit_oper_name, create_time, update_time
  </sql>
  
  <select id="findOperatorInfoList" parameterType="java.util.Map" resultMap="OperatorTableMap">
select 
a.ID, a.parent_oper_key, a.dm_role_type, a.fin_role_type, a.biz_type, a.oper_company, a.oper_department, 
a.oper_position, a.oper_mobile, a.oper_name, a.lock_status, b.login_account
from org_operator a 
left join org_oper_login b
on a.id=b.oper_key
left join org_structure_oper_ref c
on a.id=c.oper_key
where a.org_key=#{orgKey,jdbcType=BIGINT}
<if test="operMobile != null and operMobile != ''" >
and a.oper_mobile=#{operMobile,jdbcType=VARCHAR}
</if>
<if test="operName != null and operName != ''" >
and a.oper_name like CONCAT('%',#{operName},'%')
</if>
<if test="lockStatus != null and lockStatus != ''" >
and a.lock_status=#{lockStatus,jdbcType=CHAR}
</if>
<if test="structureKey != null" >
and c.structure_key=#{structureKey,jdbcType=BIGINT}
</if>
<if test="dmRoleTypeList != null and dmRoleTypeList != ''" >
and a.dm_role_type in 
<foreach collection="dmRoleTypeList" index="index" item="item" open="(" separator="," close=")" >
       #{item,jdbcType=VARCHAR}
   </foreach>
</if>
<if test="finRoleTypeList != null and finRoleTypeList != ''" >
and a.fin_role_type in
<foreach collection="finRoleTypeList" index="index" item="item" open="(" separator="," close=")" >
       #{item,jdbcType=VARCHAR}
   </foreach>
</if>
<if test="withoutOperKeys != null" >
and a.ID not in 
<foreach collection="withoutOperKeys" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
  </select>
  
  
  
  <select id="selectOperWithCommonOrAdmin" parameterType="java.util.Map" resultMap="OperatorTableMap">
  SELECT
   DISTINCT(T1.ID),
 T1.ID,
 T1.parent_oper_key,
 T1.dm_role_type,
 T1.fin_role_type,
 T1.biz_type,
 T1.oper_company,
 T1.oper_department,
 T1.oper_position,
 T1.oper_mobile,
 T1.oper_name,
 T1.lock_status,
 T2.login_account
FROM spof_member.org_operator T1
 JOIN spof_member.org_oper_login T2
   ON T1.ID = T2.oper_key AND T1.org_key = T2.org_key
   WHERE 1=1
     <if test="parentOperKey != null" >
AND T1.parent_oper_key = #{parentOperKey}
 </if>
     <if test="orgKey !=null " >
AND T1.org_key = #{orgKey}
 </if>
 <if test="dmRoleType != null" >
AND T1.dm_role_type = #{dmRoleType}
 </if>
 <if test="dmRoleType == null" >
  <choose>
  <when test="loginRoleTypeFlag == 'O'.toString()">
  AND (T1.dm_role_type = 1 or T1.dm_role_type = 2 OR T1.dm_role_type IS NULL)
  </when>
  <when test="loginRoleTypeFlag == 'A'.toString()">
  AND (T1.dm_role_type = 4 or T1.dm_role_type = 8 OR T1.dm_role_type IS NULL)
  </when>
  </choose>
 </if>
 <if test="finRoleType != null" >
AND T1.fin_role_type = #{finRoleType}
 </if>
 <if test="finRoleType == null" >
  <choose>
  <when test="loginRoleTypeFlag == 'O'.toString()">
  AND (T1.fin_role_type = 1 or T1.fin_role_type = 2 OR T1.fin_role_type IS NULL)
  </when>
  <when test="loginRoleTypeFlag == 'A'.toString()">
  AND (T1.fin_role_type = 4 or T1.fin_role_type = 8 OR T1.fin_role_type IS NULL)
  </when>
  </choose>
 </if>
 <if test="operMobile != null" >
AND T1.oper_mobile = #{operMobile}
 </if>
 <if test="operName != null" >
AND T1.oper_name LIKE CONCAT('%',#{operName},'%')
 </if>
 <if test="withoutOperKeys != null" >
AND T1.ID not in 
<foreach collection="withoutOperKeys" item="item" separator="," open="(" close=")">
#{item}
</foreach>
 </if>
  </select>
  
<select id="selectOperUnderSameDeptWithManager" parameterType="java.util.Map" resultMap="OperatorTableMap">
  SELECT
   DISTINCT(T1.ID),
 T1.ID,
 T1.parent_oper_key,
 T1.dm_role_type,
 T1.fin_role_type,
 T1.biz_type,
 T1.oper_company,
 T1.oper_department,
 T1.oper_position,
 T1.oper_mobile,
 T1.oper_name,
 T1.lock_status,
 T2.login_account
FROM spof_member.org_operator T1
 JOIN spof_member.org_oper_login T2
   ON T1.ID = T2.oper_key AND T1.org_key = T2.org_key
 JOIN spof_member.org_structure_oper_ref T3 
   ON T1.ID = T3.oper_key
 WHERE 1=1
     <if test="orgKey !=null " >
AND T1.org_key = #{orgKey}
 </if>
 <if test="dmRoleType != null" >
  <choose>
  <when test="dmRoleType == '8'.toString()"><!-- 查询经理 -->
  AND T1.dm_role_type = #{dmRoleType} AND T1.ID = #{loginOperKey}
  </when>
  <when test="dmRoleType == '1'.toString() or dmRoleType == '2'.toString()"><!-- 查询经办、复合 -->
  AND T1.dm_role_type = #{dmRoleType} AND T3.structure_key 
  IN (SELECT T4.structure_key FROM spof_member.org_structure_oper_ref T4 WHERE T4.oper_key = #{loginOperKey})
  </when>
  </choose>
 </if>
 <if test="dmRoleType == null" >
  AND ( (
  (T1.dm_role_type = 1 or T1.dm_role_type = 2 OR T1.dm_role_type IS NULL) AND T3.structure_key 
  IN (SELECT T4.structure_key FROM spof_member.org_structure_oper_ref T4 WHERE T4.oper_key = #{loginOperKey})
    ) or (
    T1.dm_role_type = 8 AND T1.ID = #{loginOperKey}
    ) 
  )
 </if>
 <if test="finRoleType != null" >
  <choose>
  <when test="finRoleType == '8'.toString()"><!-- 查询经理 -->
  AND T1.fin_role_type = #{finRoleType} AND T1.ID = #{loginOperKey}
  </when>
  <when test="finRoleType == '1'.toString() or finRoleType == '2'.toString()"><!-- 查询经办、复合 -->
  AND T1.fin_role_type = #{finRoleType} AND T3.structure_key 
  IN (SELECT T4.structure_key FROM spof_member.org_structure_oper_ref T4 WHERE T4.oper_key = #{loginOperKey})
  </when>
  </choose>
 </if>
 <if test="finRoleType == null" >
AND ( (
  (T1.fin_role_type = 1 or T1.fin_role_type = 2 OR T1.fin_role_type IS NULL) AND T3.structure_key 
  IN (SELECT T4.structure_key FROM spof_member.org_structure_oper_ref T4 WHERE T4.oper_key = #{loginOperKey})
    ) or (
    T1.fin_role_type = 8 AND T1.ID = #{loginOperKey}
    ) 
  )
 </if>
 <if test="operMobile != null" >
AND T1.oper_mobile = #{operMobile}
 </if>
 <if test="operName != null" >
AND T1.oper_name LIKE CONCAT('%',#{operName},'%')
 </if>
 <if test="withoutOperKeys != null" >
AND T1.ID not in 
<foreach collection="withoutOperKeys" item="item" separator="," open="(" close=")">
#{item}
</foreach>
 </if>
  </select>
  
<select id="countUnderManagerPersons" parameterType="java.util.List" resultType="com.zhong.member.dal.domain.member.CountUnderManagerDto">
SELECT
 T1.parent_oper_key AS operId,
 COUNT(1) AS underCount
FROM spof_member.org_operator T1
WHERE  1=1 
<if test='list != null and list.size() > 0'>
AND T1.parent_oper_key in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}
        </foreach>
   </if>
GROUP BY T1.parent_oper_key
  </select>
  
  
</mapper>