<?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.rongwei.trainingcommon.sys.dao.TraningCommonDao">
    <select id="getEmpInfo" resultType="com.rongwei.training.vo.WordEmpInfoVo">
        SELECT
        t.id AS empId,
        t.ACCOUNT AS account,
        t.`NAME` AS empName,
        t.mobile AS mobile,
        GROUP_CONCAT( DISTINCT t4.FULLNAME ORDER BY t4.`LEVEL`, t4.SORT SEPARATOR ',' ) AS orgName,
        GROUP_CONCAT( DISTINCT t3.`NAME` ORDER BY t3.`LEVEL` DESC, t3.SORT DESC SEPARATOR ',' ) AS roleName
        FROM
        sys_user t
        LEFT JOIN sys_user_role t1 ON t1.USERID = t.id
        LEFT JOIN sys_user_org t2 ON t2.USERID = t.id
        LEFT JOIN sys_role t3 ON t1.ROLEID = t3.ID
        LEFT JOIN sys_organization t4 ON t2.ORGID = t4.ID
        <where>
            t.DELETED = '0'
            AND t1.DELETED = '0'
            AND t2.DELETED = '0'
            AND t3.DELETED = '0'
            AND t4.DELETED = '0'
            <if test="empIds != null and empIds.size()>0">
                and t.ID in
                <foreach collection="empIds" item="empId" open="(" close=")" separator=",">
                    #{empId}
                </foreach>
            </if>
        </where>
        GROUP BY t.id
    </select>

    <select id="getAllUserIdAndNameByOrgId" resultType="String">
        with org_id as (select DISTINCT ID from sys_organization
        <where>
            DELETED='0'
            AND
            <foreach collection="orgIds" open="(" close=")" separator="or" item="orgId">
                FIND_IN_SET(#{orgId},CONCAT(FULLPID,',',ID))
            </foreach>
        </where>
        )
        SELECT
        DISTINCT CONCAT(su.ID,'-;-',su.NAME)
        FROM
        sys_user su
        LEFT JOIN sys_user_org suo ON suo.USERID = su.ID
        AND suo.DELETED = '0'
        where su.DELETED='0'
        AND suo.ORGID IN (select ID from org_id)
    </select>
    <select id="getDemandUserInfo" resultType="com.rongwei.training.vo.DemandUserVo">
        SELECT
            su.ID as id ,MAX(su.`NAME`) as name,GROUP_CONCAT(DISTINCT sr.`NAME`)as roleName,so.FULLNAME as companyName
        FROM
            sys_user su
                LEFT JOIN sys_user_role sur on su.ID =sur.USERID AND sur.DELETED='0'
                left join sys_role sr on sr.ID=sur.ROLEID and sr.DELETED='0'
                left join sys_organization so on su.TENANTID= so.ID and so.DELETED='0'
        <where>
        su.id in
            <foreach collection="userIds" open="(" close=")" item="user" separator=",">
                #{user}
            </foreach>
        </where>
        group by su.ID
    </select>
</mapper>
