<?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.ym.mec.biz.dal.dao.SysEmployeePositionDao">
	
	<resultMap type="com.ym.mec.biz.dal.entity.SysEmployeePosition" id="SysEmployeePosition">
		<result column="user_id_" property="userId" />
		<result column="role_id_" property="roleId" />
		<result column="tenant_id_" property="tenantId" />
	</resultMap>
		
	<resultMap type="com.ym.mec.biz.dal.dto.SysEmployeePositionDto" id="SysEmployeePositionDto" extends="SysEmployeePosition">
		<result column="position_name_" property="positionName" />
		<result column="position_code_" property="positionCode" />
	</resultMap>
	
	<!-- 根据主键查询一条记录 -->
	<select id="get" resultMap="SysEmployeePosition" parameterType="java.util.Map">
		SELECT * FROM sys_employee_position
		WHERE user_id_ = #{userId} AND role_id_ = #{roleId}
	</select>
	
	<!-- 全查询 -->
	<select id="findAll" resultMap="SysEmployeePosition">
		SELECT * FROM sys_employee_position ORDER BY user_id_,role_id_
	</select>
	
	<!-- 向数据库增加一条记录 -->
	<insert id="insert" parameterType="com.ym.mec.biz.dal.entity.SysEmployeePosition" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
		INSERT INTO sys_employee_position (user_id_,role_id_,tenant_id_)
		VALUES(#{userId},#{roleId},#{tenantId})
	</insert>
	<insert id="batchAddPosition">
		INSERT INTO sys_employee_position(user_id_,role_id_,tenant_id_) VALUES
		<foreach collection="positionIds" item="item" index="index" separator=",">
			(#{userId},#{item},#{tenantId})
		</foreach>
	</insert>

	<!-- 根据主键查询一条记录 -->
	<update id="update" parameterType="com.ym.mec.biz.dal.entity.SysEmployeePosition">
		UPDATE sys_employee_position <set>
		<if test="userId != null">
		user_id_ = #{userId},
		</if>
		<if test="tenantId != null">
		tenant_id_ = #{tenantId},
		</if>
		<if test="roleId != null">
		role_id_ = #{roleId},
		</if>
		</set> WHERE user_id_ = #{userId} AND role_id_ = #{roleId}
	</update>
	
	<!-- 根据主键删除一条记录 -->
	<delete id="delete" parameterType="java.util.Map">
		DELETE FROM sys_employee_position WHERE user_id_ = #{userId} AND role_id_ = #{roleId} 
	</delete>

	<delete id="delPosition">
		DELETE FROM sys_employee_position WHERE user_id_ = #{userId} AND tenant_id_ = #{tenantId}
	</delete>

	<!-- 分页查询 -->
	<select id="queryPage" resultMap="SysEmployeePosition" parameterType="map">
		SELECT * FROM sys_employee_position ORDER BY user_id_,role_id_ <include refid="global.limit"/>
	</select>
	
	<!-- 查询当前表的总记录数 -->
	<select id="queryCount" resultType="int">
		SELECT COUNT(*) FROM sys_employee_position
	</select>
	<select id="queryPositionMap" resultType="java.util.Map">
		SELECT user_id_ 'key',GROUP_CONCAT(role_id_) 'value' FROM sys_employee_position
		WHERE tenant_id_ = #{tenantId} AND user_id_ IN
		<foreach collection="userIds" separator="," open="(" item="userId" close=")">
			#{userId}
		</foreach>
		GROUP BY user_id_
	</select>
	<select id="queryPositionNameMap" resultType="java.util.Map">
		SELECT sep.user_id_ 'key',GROUP_CONCAT(sp.role_name_) 'value'  FROM sys_employee_position sep
		LEFT JOIN sys_position sp ON sp.id_ = sep.role_id_
		WHERE sep.tenant_id_ = #{tenantId} AND sep.user_id_ IN
		<foreach collection="userIds" separator="," open="(" item="userId" close=")">
			#{userId}
		</foreach>
		GROUP BY sep.user_id_
	</select>
	<select id="findOrganPosition" resultType="com.ym.mec.biz.dal.dto.SimpleUserDto">
		SELECT su.id_ userId,su.real_name_ userName,su.avatar_ avatar,e.organ_id_list_ organIds,sep.role_id_ positionId
		FROM sys_employee_position sep
		LEFT JOIN sys_user su ON su.id_ = sep.user_id_
		LEFT JOIN employee e ON e.user_id_ = su.id_
		WHERE su.lock_flag_ = 0 AND su.del_flag_ = 0 AND sep.tenant_id_ = #{tenantId}
		<if test="organIds!=null and organIds!=''">
			AND INTE_ARRAY(e.organ_id_list_,#{organIds})
		</if>
	</select>
	<select id="queryPositionList" resultType="com.ym.mec.biz.dal.enums.SysUserRoleEnum">
		SELECT sp.role_code_ FROM sys_employee_position sep
		LEFT JOIN sys_position sp ON sp.id_ = sep.role_id_
		WHERE sep.user_id_ = #{userId} AND sep.tenant_id_ = #{tenantId}
	</select>
	<select id="queryUserIdByRoleId" resultType="java.lang.Integer">
		SELECT DISTINCT sep.user_id_ FROM sys_employee_position sep
		LEFT JOIN sys_position sp ON sp.id_ = sep.role_id_
		LEFT JOIN employee e ON e.user_id_ = sep.user_id_
		WHERE sep.tenant_id_ = #{tenantId} AND FIND_IN_SET(#{organId},e.organ_id_list_) AND sp.role_code_ IN
		<foreach collection="roleArr" item="role" open="(" close=")" separator=",">
			#{role}
		</foreach>
	</select>
	
	<select id="queryByUserId" resultMap="SysEmployeePositionDto">
		SELECT sep.*,sp.role_name_ position_name_,sp.role_code_ position_code_ FROM sys_employee_position sep
		LEFT JOIN sys_position sp ON sp.id_ = sep.role_id_
		WHERE sep.user_id_ = #{userId}
	</select>
</mapper>
