<?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.cool.store.mapper.SysRoleMapper">

    <resultMap type="com.cool.store.entity.SysRoleDO" id="sysRoleMap">
        <result property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="isInternal" column="is_internal"/>
        <result property="roleAuth" column="role_auth"/>
        <result property="source" column="source"/>
        <result property="positionType" column="position_type"/>
        <result property="appMenu" column="app_menu"/>
        <result property="synDingRoleId" column="syn_ding_role_id"/>
        <result property="priority" column="priority"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="createUser" column="create_user"/>
        <result property="updateUser" column="update_user"/>
    </resultMap>

    <resultMap id="sysRoleVo" type="com.cool.store.vo.SysRoleVO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="role_name" property="roleName" jdbcType="VARCHAR"/>
        <result column="is_internal" property="isInternal" jdbcType="BIGINT"/>
        <result column="role_auth" property="roleAuth" jdbcType="BIGINT"/>

        <collection property="enterpriseDOs" ofType="com.cool.store.entity.EnterpriseUserDO">
            <id column="cid" property="id" jdbcType="BIGINT"/>
            <result column="user_id" property="userId" jdbcType="VARCHAR"/>
            <result column="name" property="name" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="getRoleUserByRoleEnum" resultMap="sysRoleVo">
        select
        a.id ,
        a.role_name ,
        a.is_internal ,
        c.id as cid,
        c.user_id ,
        c.name
        from sys_role_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.id=b.role_id
        left join enterprise_user_${enterpriseId} c on c.user_id=b.user_id             and c.active = true
        and c.user_status= '1'
        <where>
            (c.user_id != 'a100000001' or c.user_id is null)
            and a.role_enum = #{roleEnum}
            <if test="positionType !=null and positionType!=''">
                and  a.position_type=#{positionType}
            </if>
        </where>
    </select>

    <select id="getRoleUserByRoleAuth" resultMap="sysRoleVo">
        select
        a.id ,
        a.role_name ,
        a.is_internal ,
        c.id as cid,
        c.user_id ,
        c.name
        from sys_role_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.id=b.role_id
        left join enterprise_user_${enterpriseId} c on c.user_id=b.user_id       and c.active = true
        and c.user_status = '1'
        <where>
            (c.user_id != 'a100000001' or c.user_id is null)

            <if test="roleAuth!=null">
                and role_auth = #{roleAuth, jdbcType=VARCHAR}
            </if>
--             and a.source = 'create'
            <if test="positionType !=null and positionType!=''">
                and  a.position_type=#{positionType}
            </if>
        </where>
    </select>

    <select id="getPersonNumsByRoles" resultType="integer">
        select count(a.id) as count
        from enterprise_user_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.user_id =b.user_id
        left join sys_role_${enterpriseId} c on c.id=b.role_id
        where a.active=true
--         and c.source = 'create'
        <if test="roleId!=null ">
            and c.id= #{roleId}
        </if>
    </select>

    <select id="getUserIdListByRoleIdList" resultType="com.cool.store.entity.EnterpriseUserRole">
        select
        b.role_id as roleId,
        b.user_id as userId
        from enterprise_user_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.user_id=b.user_id
        <where>
            and a.active=true
            and a.user_id != 'a100000001'
--             and c.source = 'create'
            and b.role_id in (
            <foreach collection="roleIdList" separator="," item="roleId">
                #{roleId}
            </foreach>
            )
        </where>
    </select>
    <select id="userAndRolesByUserId" resultType="com.cool.store.dto.UserRoleDTO">
        select
        a.role_id as roleId,
        a.user_id as userId,
        b.role_name as roleName,
        b.role_auth as roleAuth,
        b.role_enum as roleEnum,
        b.priority as priority
        from enterprise_user_role_${enterpriseId} a left join sys_role_${enterpriseId} b on a.role_id=b.id
        <where>
            <if test="userIdList != null and userIdList.size>0">
                <foreach collection="userIdList" item="userId" separator="," open="a.user_id in (" close=")">
                    #{userId}
                </foreach>
            </if>
--             and b.source = 'create'
        AND b.role_name IS NOT NULL
        </where>
        order by b.priority
    </select>


    <select id="getRolesByName" resultType="com.cool.store.entity.SysRoleDO">
      select
      id as id,
      role_name as roleName,
      role_auth as roleAuth
       from  sys_role_${enterpriseId}
      where role_name=#{roleName} and source ='create'

    </select>

    <select id="getRolesByNameAndSource" resultType="com.cool.store.entity.SysRoleDO">
        select
            id as id,
            role_name as roleName,
            role_auth as roleAuth
        from  sys_role_${enterpriseId}
        where role_name = #{roleName} and source = #{source}
    </select>

    <select id="getRole" resultType="com.cool.store.entity.SysRoleDO">
      select
      id as id,
      role_name as roleName,
      role_auth as roleAuth,
      is_internal as isInternal,
      `source` as source,
      priority as priority,
      app_menu as appMenu,
      position_type as positionType,
      role_enum as roleEnum
       from  sys_role_${enterpriseId}
      where id = #{roleId}
    </select>

    <select id="getRoleByRoleEnum" resultType="com.cool.store.entity.SysRoleDO">
      select
      id as id,
      role_name as roleName,
      role_auth as roleAuth,
      is_internal as isInternal,
      `source` as source,
      priority as priority,
      app_menu as appMenu,
      position_type as positionType,
      role_enum as roleEnum
       from  sys_role_${enterpriseId}
      where role_enum = #{roleEnum}
    </select>

    <select id="getRoleList" resultType="com.cool.store.entity.SysRoleDO">
      select
      id as id,
      role_name as roleName,
      role_auth as roleAuth,
      `source` as source,
      priority as priority,
      is_internal as isInternal,
      app_menu as appMenu,
      position_type as positionType
       from  sys_role_${enterpriseId}
        where 1 = 1
      -- source='create'
      <foreach collection="roleIdList" item="roleId" separator="," open="and id in (" close=")">
          #{roleId}
      </foreach>
    </select>

    <select id="getRoleByRoleIds" resultType="com.cool.store.entity.SysRoleDO">
        select
        id as id,
        role_name as roleName,
        role_auth as roleAuth,
        `source` as source,
        priority as priority,
        is_internal as isInternal,
        app_menu as appMenu,
        create_time as createTime,
        update_time as updateTime,
        position_type as positionType
        from sys_role_${enterpriseId}
        where 1 = 1
        <foreach collection="roleIdList" item="roleId" separator="," open="and id in (" close=")">
            #{roleId}
        </foreach>
    </select>

    <select id="getRoleNameList" resultType="java.lang.String">
        select
        role_name as roleName
        from  sys_role_${enterpriseId}
        where source='create'
        <foreach collection="roleIdList" item="roleId" separator="," open="and id in (" close=")">
            #{roleId}
        </foreach>
    </select>

    <select id="getHighestPrioritySysRoleDoByUserId" resultType="com.cool.store.entity.SysRoleDO">
        select
        c.id as id,
         c.role_name as roleName,
         c.role_auth as roleAuth,
         c.position_type as positionType,
         c.app_menu as appMenu,
         c.role_enum as roleEnum
        from enterprise_user_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.user_id =b.user_id
        left join sys_role_${enterpriseId}  c on c.id=b.role_id
        where  a.user_id= #{userId}
        and a.active = true
        ORDER BY c.priority is null , c.priority, position_type desc
        LIMIT 1
    </select>

    <select id="getSysRoleByUserId" resultType="com.cool.store.entity.SysRoleDO">
        select c.id        as id,
               c.role_name as roleName,
               c.role_auth as roleAuth,
               c.source    as source
        from enterprise_user_${enterpriseId} a
                 left join enterprise_user_role_${enterpriseId} b on a.user_id = b.user_id
                 left join sys_role_${enterpriseId} c on c.id = b.role_id
        where a.user_id = #{userId}
          and a.active = true
          AND c.role_name IS NOT NULL
    </select>

    <select id="listRoleByUserId" resultType="com.cool.store.entity.SysRoleDO">
        select c.id        as id,
               c.role_name as roleName,
               c.role_auth as roleAuth,
               c.source    as source,
               c.role_enum as roleEnum
        from  enterprise_user_role_${enterpriseId} b
                 left join sys_role_${enterpriseId} c on c.id = b.role_id
        where b.user_id = #{userId}

    </select>
    <select id="selectRolesByuserId" resultType="java.lang.String">
      select
      role_id
      from enterprise_user_role_${enterpriseId}
      where user_id = #{userId}
--       and sr.source = 'create'
    </select>


    <select id="getHighestPriorityRoleIdByUserId" resultType="java.lang.String">
        select
        eur.role_id
        from enterprise_user_role_${enterpriseId} eur
        left join sys_role_${enterpriseId} sr
        on eur.role_id = sr.id
        where user_id = #{userId}
        ORDER BY sr.priority is null , sr.priority
        LIMIT 1
    </select>

    <select id="getPositionUserIds" resultType="java.lang.String">
        select eu.user_id from
        enterprise_user_role_${enterpriseId} eur
        left join enterprise_user_${enterpriseId} eu USING(user_id)
        where eur.role_id in
        <foreach collection="positionIds" item="roleId" open="(" separator="," close=")">
            #{roleId}
        </foreach>
        and eu.active = true
    </select>

    <select id="selectUserByDingRole" resultType="java.lang.String">
        select b.user_id from sys_role_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.id=b.role_id
        left join enterprise_user_${enterpriseId} c on b.user_id=c.user_id
        where role_id=#{roleId} and c.active=true
    </select>

    <select id="selectUserRoleBySourceAndUserId" resultType="com.cool.store.entity.EnterpriseUserRole">
        select b.role_id as roleId,
               b.user_id as userId
        from sys_role_${enterpriseId} a
        left join enterprise_user_role_${enterpriseId} b on a.id=b.role_id
        <where>
            <if test="userIdList!=null and userIdList.size>0">
                <foreach collection="userIdList" separator="," open="and b.user_id in(" close=")" item="userId">
                    #{userId}
                </foreach>
            </if>
            <if test="source!=null and source!=''">
                and a.source = #{source}
            </if>
        </where>
    </select>

    <select id="getUserRoleNameByUserIdList" resultType="com.cool.store.dto.UserRoleDTO">
        select
        a.user_id as userId,
        group_concat(b.role_name) as roleName
        from enterprise_user_role_${enterpriseId} a left join sys_role_${enterpriseId} b on a.role_id=b.id
        where
        <foreach collection="userIdList" item="userId" separator="," open="a.user_id in (" close=")">
            #{userId}
        </foreach>
        group by a.user_id
    </select>

    <select id="selectBySynDingRoleIdAndSource" resultType="com.cool.store.entity.SysRoleDO">
        select id, role_name as roleName, is_internal as isInternal, role_auth as roleAuth, `source`,
               position_type as positionType, app_menu as appMenu, syn_ding_role_id as synDingRoleId,
               priority, create_time as createTime, update_time as updateTime,role_enum as roleEnum
        from sys_role_${enterpriseId}
        <where>
            <if test="source != null and source != ''">
                and source = #{source}
            </if>
            <if test="synDingRoleId != null">
                and syn_ding_role_id = #{synDingRoleId}
            </if>
        </where>
        ORDER BY priority
    </select>

    <select id="getRolesByNamesAndSource" resultType="com.cool.store.entity.SysRoleDO">
        select
        id as id,
        role_name as roleName,
        role_auth as roleAuth
        from  sys_role_${enterpriseId}
        where source = #{source} and role_name in
        <foreach item="roleName" collection="roleNames" open="(" separator="," close=")">
            #{roleName}
        </foreach>
    </select>
    <select id="getXFStoreManager" resultType="com.cool.store.entity.SysRoleDO">
        select
        *
        from sys_role_${enterpriseId}
        where
        (
        source = 'sync'
        or
        source = 'sync_position'
        )
        and role_name in
        <foreach collection="roleNames" item="roleName" open="(" close=")" separator=",">
            #{roleName}
        </foreach>

    </select>

</mapper>