<?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.EnterpriseUserRoleMapper">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.cool.store.entity.EnterpriseUserRole" id="enterpriseUserRoleMap">
        <result property="id" column="id"/>
        <result property="roleId" column="role_id"/>
        <result property="userId" column="user_id"/>
		<result property="createTime" column="create_time"/>
		<result property="updateTime" column="update_time"/>
    </resultMap>

	<select id="selectByPrimaryKey" resultMap="enterpriseUserRoleMap">
		select * from enterprise_user_role_${enterpriseId} where id = #{id}
	</select>

	<select id="selectDingRoleMappingByUserId" resultMap="enterpriseUserRoleMap">
		SELECT
			eur.*
		FROM
			enterprise_user_role_${enterpriseId} eur
				LEFT JOIN sys_role_${enterpriseId} sr on eur.role_id = sr.id
		WHERE
			user_id = #{userId}
		  and (sr.source = 'sync' OR sr.id IS NULL)
	</select>

	<select id="listsUserRoleByUserIdListAndSource" resultMap="enterpriseUserRoleMap">
		SELECT
			eur.*
		FROM
			enterprise_user_role_${enterpriseId} eur
				LEFT JOIN sys_role_${enterpriseId} sr on eur.role_id = sr.id
		where user_id in
		<foreach item="userId" collection="userIds" open="(" separator="," close=")">
			#{userId}
		</foreach>
		  and (sr.source = #{source} OR sr.id IS NULL)
	</select>

	<select id="selectMdtRoleMappingByUserId" resultMap="enterpriseUserRoleMap">
		SELECT
			eur.*
		FROM
			enterprise_user_role_${enterpriseId} eur
				LEFT JOIN sys_role_${enterpriseId} sr on eur.role_id = sr.id
		WHERE
			user_id = #{userId}
		  and (sr.syn_ding_role_id is not null OR sr.id IS NULL)
	</select>

	<select id="selectByRoleId" resultMap="enterpriseUserRoleMap">
		select * from enterprise_user_role_${enterpriseId} where role_id = #{roleId}
	</select>


	<select id="selectIdsByUserId" resultType="java.lang.Long">
		select id from enterprise_user_role_${enterpriseId} where user_id = #{userId}
	</select>

	<select id="selectIdsByUserIds" resultType="java.lang.Long">
		select id from enterprise_user_role_${enterpriseId} where user_id in
		<foreach item="userId" collection="userIds" open="(" separator="," close=")">
			#{userId}
		</foreach>
	</select>

	<select id="selectByUserIdList" resultMap="enterpriseUserRoleMap">
		select id, user_id, role_id
		from enterprise_user_role_${enterpriseId}
		where user_id in
		<foreach item="userId" collection="userIds" open="(" separator="," close=")">
			#{userId}
		</foreach>
	</select>

	<select id="selectByUserIdAndRoleId" resultMap="enterpriseUserRoleMap">
		select * from enterprise_user_role_${enterpriseId}
		where user_id = #{userId} and role_id = #{roleId}
		order by id asc
      	limit 1
	</select>

	<select id="selectUserIdByRoleId" resultType="string">
		select
			user_id
		from
			enterprise_user_role_${enterpriseId}
		where role_id = #{roleId} and user_id in
		<foreach item="userId" collection="userIds" open="(" separator="," close=")">
			#{userId}
		</foreach>
	</select>
	<select id="selectRoleIdsByUserId" resultType="java.lang.Long">
		select role_id as roleId from enterprise_user_role_${enterpriseId} where user_id = #{userId}
	</select>

	<select id="selectUserIdsByRoleId" resultType="java.lang.String">
		select
			user_id
		from
			enterprise_user_role_${enterpriseId}
		where role_id = #{roleId}
	</select>
	<select id="selectUserIdsByRoleIdList" resultType="java.lang.String">
		select
			DISTINCT a.user_id
		from
			enterprise_user_role_${enterpriseId} a left join enterprise_user_${enterpriseId} b on a.user_id=b.user_id
		where
        b.user_id != 'a100000001'
        and b.user_status = '1'
        and b.active = true
        and a.role_id in
			<foreach collection="roleIds" item="roleId" open="(" separator="," close=")">
				#{roleId}
			</foreach>
	</select>

	<select id="getUserIdsByRoleIds" resultType="string">
		select
			user_id
		from
			enterprise_user_role_${enterpriseId}
		where
			role_id in <foreach collection="roleIds" open="(" close=")" separator="," item="roleId">#{roleId}</foreach>
		and
			user_id in <foreach collection="userIds" open="(" close=")" separator="," item="userId">#{userId}</foreach>
	</select>

	<select id="getUserRoleIds" resultType="long">
		select role_id from enterprise_user_role_${enterpriseId} where user_id = #{userId}
	</select>

	<select id="getUserIdsByRoleIdList" resultType="java.lang.String">
		select user_id
		from enterprise_user_role_${enterpriseId}
		where role_id in (
		<foreach collection="roleIdList" item="id" separator=",">
			#{id}
		</foreach>
		)
	</select>

</mapper>