<?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="io.dataease.base.mapper.ext.ExtUserRoleMapper">

    <resultMap id="BaseOrganizationMemberDTOMap" type="io.dataease.dto.OrganizationMemberDTO">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="status" jdbcType="VARCHAR" property="status" />
        <result column="create_time" jdbcType="BIGINT" property="createTime" />
        <result column="update_time" jdbcType="BIGINT" property="updateTime" />
        <result column="language" jdbcType="VARCHAR" property="language" />
        <collection property="roles" javaType="arraylist" ofType="io.dataease.base.domain.Role">
            <id column="rid" jdbcType="VARCHAR" property="id" />
            <result column="rname" jdbcType="VARCHAR" property="name" />
        </collection>
    </resultMap>

    <select id="getUserRoleHelpList" parameterType="java.lang.String"
            resultType="io.dataease.dto.UserRoleHelpDTO">
        SELECT
            user_role.role_id AS roleId,
            role.`name` AS roleName,
            user_role.source_id AS sourceId,

            IF(workspace.id IS NULL,IF(organization.id IS NULL,'',organization.`name`),workspace.name) AS sourceName,

            IF(workspace.id IS NULL,NULL,workspace.organization_id) AS parentId

            FROM user_role LEFT JOIN role ON role.id = user_role.role_id

            LEFT JOIN workspace ON workspace.id = user_role.source_id

            LEFT JOIN organization ON organization.id = user_role.source_id

            WHERE user_role.user_id = #{userId}
    </select>

    <select id="getMemberList" resultType="io.dataease.base.domain.User">
        SELECT DISTINCT * FROM (
        SELECT `user`.* FROM user_role JOIN `user` ON user_role.user_id = `user`.id
        WHERE user_role.source_id = #{member.workspaceId}
        <if test="member.name != null">
            AND `user`.name like CONCAT('%', #{member.name},'%')
        </if>
        order by user_role.update_time desc) temp
    </select>

    <select id="getOrgMemberList" resultType="io.dataease.base.domain.User">
        SELECT DISTINCT * FROM (
        SELECT `user`.*
        FROM user_role JOIN `user`
        ON user_role.user_id = `user`.id
        WHERE user_role.source_id in
        (
          SELECT id FROM workspace w
          WHERE w.organization_id = #{orgMember.organizationId}
          UNION
          SELECT #{orgMember.organizationId} AS id FROM dual
        )
        <if test="orgMember.name != null">
            AND `user`.name like CONCAT('%', #{orgMember.name},'%')
        </if>
        order by user_role.update_time desc) temp
    </select>

    <select id="getOrganizationMemberDTO" resultMap="BaseOrganizationMemberDTOMap">
        select u.id, u.name, u.email, u.language, u.create_time, u.update_time, u.phone,
               o.id, o.name, ur.id, r.id as rid, r.name as rname
          from organization o
          join user_role ur on o.id = ur.source_id
          join user u on ur.user_id = u.id
          join role r on r.id = ur.role_id
         where o.id = #{orgMember.organizationId}
    </select>

    <select id="getOrganizationMemberRoles" resultType="io.dataease.base.domain.Role">
        select r.id, r.name
          from organization o
          join user_role ur on o.id = ur.source_id
          join role r on r.id = ur.role_id
         where o.id = #{orgId} and ur.user_id = #{userId}
    </select>

    <select id="getBesideOrgMemberList" parameterType="java.lang.String" resultType="io.dataease.base.domain.User">
        select *
          from `user`
         where `user`.id not in (select distinct u.id
                                   from `user` u
                                   join user_role ur on u.id = ur.user_id
                                  where ur.source_id = #{orgId})
    </select>

    <select id="getWorkspaceMemberRoles" resultType="io.dataease.base.domain.Role">
        select r.id, r.name
          from workspace w
          join user_role ur on w.id = ur.source_id
          join role r on r.id = ur.role_id
         where w.id = #{workspaceId} and ur.user_id = #{userId}
    </select>
    <select id="getTestManagerAndTestUserList" resultType="io.dataease.base.domain.User">
        SELECT DISTINCT * FROM (
        SELECT `user`.* FROM user_role INNER JOIN `user` ON user_role.user_id = `user`.id
        WHERE user_role.source_id = #{request.workspaceId} And user_role.role_id in ('test_manager', 'test_user')
        <if test="request.name != null">
            AND `user`.name like CONCAT('%', #{request.name},'%')
        </if>
        order by user_role.update_time desc) temp
    </select>
</mapper>
