<?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.hdkj.lt.core.sys.dao.SysUserMapper">

    <select id="listByCondition" resultType="com.hdkj.lt.core.sys.model.po.SysUser">
        SELECT u.*
        FROM SYS_USERS u
        INNER JOIN SYS_STRU s ON u.EMPLOYEE_ID = s.STRU_ID
        <if test="(params.roleIds != null and params.roleIds.size > 0) or (params.rolePermissions != null and params.rolePermissions.size > 0)">
            LEFT JOIN (
                SELECT tu.USER_ID, GROUP_CONCAT(tr.ROLE_ID) ROLE_IDS, GROUP_CONCAT(tr.ROLE_PERMISSION) ROLE_PERMISSIONS
                FROM SYS_USERS tu, SYS_USER_ROLE tur, SYS_ROLES tr
                WHERE tu.USER_ID = tur.USER_ID AND tur.GRANTED_ROLE = tr.ROLE_ID
                GROUP BY tu.USER_ID
            ) ur ON u.USER_ID = ur.USER_ID
        </if>
        <if test="params.unitOrganType != null and params.unitOrganType != ''">
            LEFT JOIN SYS_STRU ps ON s.PARENT_ID = ps.STRU_ID
        </if>
        WHERE s.STRU_TYPE = '9' AND u.ACCOUNT_STATUS = '1'
        <if test="params.userIds != null and params.userIds.size > 0">
            AND u.USER_ID IN
            <foreach collection="params.userIds" open="(" item="userId" separator="," close=")">
                #{userId, jdbcType=BIGINT}
            </foreach>
        </if>
        <if test="params.userName != null and params.userName != ''">
            AND u.USER_NAME LIKE CONCAT('%',#{params.userName},'%')
        </if>
        <if test="params.roleIds != null and params.roleIds.size > 0">
            <foreach collection="params.roleIds" open="AND (" item="roleId" separator=" AND " close=")">
                ur.ROLE_IDS LIKE CONCAT('%',#{roleId},'%')
            </foreach>
        </if>
        <if test="params.rolePermissions != null and params.rolePermissions.size > 0">
            <foreach collection="params.rolePermissions" open="AND (" item="rolePermission" separator=" AND " close=")">
                ur.ROLE_PERMISSIONS LIKE CONCAT('%',#{rolePermission},'%')
            </foreach>
        </if>
        <if test="params.unitIds != null and params.unitIds.size > 0">
            AND s.PARENT_ID IN
            <foreach collection="params.unitIds" open="(" item="unitId" separator="," close=")">
                #{unitId, jdbcType=BIGINT}
            </foreach>
        </if>
        <if test="params.unitOrganType != null and params.unitOrganType != ''">
            AND ps.STRU_TYPE = #{params.unitOrganType}
        </if>
    </select>

    <select id="queryChildDateManager" resultType="java.lang.Long">
        with recursive organList as (
            select STRU_ID,PARENT_ID,ORGAN_ALIAS,STRU_TYPE,STRU_LEVEL,IS_EMPLOYEE,ss.STRU_ORDER from SYS_STRU ss where ss.STRU_ID=#{parentId}
            union all
            select ss.STRU_ID,ss.PARENT_ID,ss.ORGAN_ALIAS,ss.STRU_TYPE,ss.STRU_LEVEL,ss.IS_EMPLOYEE,ss.STRU_ORDER
            from SYS_STRU ss inner join organList ol on ol.STRU_ID = ss.PARENT_ID
        )
        select rs.USER_ID struId
        from organList ol
        inner join SYS_USERS rs on ol.STRU_ID = rs.EMPLOYEE_ID and rs.ACCOUNT_STATUS = '1' and ol.STRU_TYPE = '9'
        inner join SYS_USER_ROLE sur on rs.USER_ID = sur.USER_ID
        inner join SYS_ROLES sr on sr.ROLE_ID = sur.GRANTED_ROLE and sr.ROLE_PERMISSION = #{roleCode}
        order by ol.STRU_ORDER
    </select>

    <select id="getUserNodeByOrgType" useCache="true" resultType="java.lang.Integer">
        <!-- 机构添加部门后（神公司添加数字化部）sql修改
        select if(t.userNode &lt; #{userNode}, #{userNode}, t.userNode) as userNode
        from (
            select case
                when t.STRU_TYPE = '1' then '0'
                when t.STRU_TYPE = '4' then '2'
                when t.STRU_TYPE = '5' then '3'
                when t.STRU_TYPE = '6' then '4'
                else #{userNode} end as userNode
            from SYS_STRU t
            where t.STRU_ID = #{unitId}
        ) t
        -->
        WITH RECURSIVE VIEW_PARENT_ORG AS (
            SELECT IF(st.STRU_TYPE in ('1','4','5','6'),null,st.PARENT_ID) P_STRU_ID, st.*
            FROM SYS_STRU st WHERE st.STRU_ID = #{unitId}
            UNION ALL
            SELECT IF(s.STRU_TYPE in ('1','4','5','6'),null,s.PARENT_ID) P_STRU_ID, s.*
            FROM SYS_STRU s INNER JOIN VIEW_PARENT_ORG p ON s.STRU_ID = p.P_STRU_ID)
        select if(t.userNode &lt; #{userNode}, #{userNode}, t.userNode) as userNode
        from (select case
                when t.STRU_TYPE = '1' then 0
                when t.STRU_TYPE = '4' then 2
                when t.STRU_TYPE = '5' then 3
                when t.STRU_TYPE = '6' then 4
                else #{userNode} end as userNode
            from VIEW_PARENT_ORG t where t.P_STRU_ID is null
        ) t
    </select>

    <select id="getLoadingUserTree">
        SELECT *
        FROM (
        SELECT
        org.ORGAN_NAME AS text,
        org.ORGAN_CODE AS code,
        str.STRU_ID AS id,
        str.IS_EMPLOYEE AS isemployee,
        '' AS employeeid,
        str.PARENT_ID AS parent,
        str.PERMISSION_STRU_ID AS permitid,
        str.ORGAN_ID AS organid,
        str.STRU_TYPE AS strutype,
        str.STRU_LEVEL AS strulevel,
        str.STRU_ORDER,
        '1' AS first_order,
        str.STRU_TYPE AS modelType,
        '' AS accountstatus,
        CASE WHEN str.IS_LEAF = 'N' THEN 'true' ELSE 'false' END AS children
        FROM SYS_STRU str
        LEFT JOIN SYS_ORGAN org ON str.ORGAN_ID = org.ORGAN_ID
        WHERE str.DEL_FLAG = '0' AND str.IS_EMPLOYEE IS NULL
        <if test="parentId != '' and parentId != null">
            AND str.PARENT_ID = #{parentId}
        </if>
        UNION ALL
        SELECT
        u.USER_NAME AS text,
        'USER' AS code,
        u.USER_ID AS id,
        '1' AS isemployee,
        u.EMPLOYEE_ID AS employeeid,
        u.DEPARTMENT_ID AS parent,
        u.PERMISSION_STRU_ID AS permitid,
        '1' AS organid,
        '1' AS strutype,
        (SELECT stru_inner.STRU_LEVEL FROM SYS_STRU stru_inner WHERE stru_inner.STRU_ID = u.EMPLOYEE_ID) AS strulevel,
        u.USER_ORDER AS stru_order,
        '2' AS first_order,
        'USER' AS type,
        u.ACCOUNT_STATUS AS accountstatus,
        'false' AS children
        FROM SYS_USERS u
        LEFT JOIN SYS_USER_ROLE r on r.USER_ID = u.USER_ID
        WHERE u.ACCOUNT_STATUS != '2' AND r.GRANTED_ROLE = '730352438871334912'
        <if test="parentId != '' and parentId != null">
            AND u.DEPARTMENT_ID = #{parentId}
        </if>
        ) t1
        ORDER BY STRULEVEL, STRU_ORDER
    </select>

    <select id="queryChildOrgan" resultType="com.hdkj.lt.core.sys.model.dto.StruDto">
        select
            <if test='type=="4"'>
                ss.STRU_ID struId,ss.STRU_ID as orgCode,ss.STRU_TYPE,
                ss1.STRU_TYPE parentStruType,count(css.STRU_ID)=0 as isLeaf,
            </if>
            <if test='type=="9"'>
                rs.USER_ID struId,rs.USER_ID orgCode,
            </if>
            ss.ORGAN_ALIAS as organName
        from SYS_STRU ss
        <if test='type=="4"'>
            inner join (select sot.ORGAN_TYPE from SYS_ORGAN_TYPE sot where sot.ORGAN_TYPE  in ('4','5','6')) sot
            on ss.STRU_TYPE = sot.ORGAN_TYPE and ss.PARENT_ID=#{parentId} and ss.DEL_FLAG='0'
            left join SYS_STRU css on ss.STRU_ID = css.PARENT_ID and css.STRU_TYPE in ('4','5','6')
            left join SYS_STRU ss1 on ss.PARENT_ID = ss1.stru_id group by ss.STRU_ID
        </if>
        <if test='type=="9"'>
            inner join (select sot.ORGAN_TYPE from SYS_ORGAN_TYPE sot where sot.ORGAN_TYPE  in ('9')) sot
            on ss.STRU_TYPE = sot.ORGAN_TYPE and ss.PARENT_ID=#{parentId} and ss.DEL_FLAG='0'
            left join (select t2.STRU_ID,t1.USER_ID from SYS_USERS t1 INNER JOIN SYS_STRU t2 on t1.EMPLOYEE_ID=t2.STRU_ID
                inner join SYS_USER_ROLE t3 on t1.USER_ID=t3.USER_ID and t3.GRANTED_ROLE=730352438871334912
            ) rs on ss.STRU_ID=rs.STRU_ID where rs.STRU_ID is not null
        </if>
        order by ss.STRU_ORDER
    </select>

    <select id="queryChildOrganNoDel" resultType="com.hdkj.lt.core.sys.model.dto.StruDto">
        select ss.STRU_ID as struId,ss.STRU_ID as orgCode,ss.STRU_TYPE as struType,
            ss1.STRU_TYPE as parentStruType,count(css.STRU_ID)=0 as isLeaf,ss.ORGAN_ALIAS as organName
        from SYS_STRU ss
        inner join (select sot.ORGAN_TYPE from SYS_ORGAN_TYPE sot where sot.ORGAN_TYPE in ('4', '5', '6')) sot
        on ss.STRU_TYPE = sot.ORGAN_TYPE and ss.PARENT_ID = #{parentId} and IFNULL(ss.DEL_FLAG, 0) != 1
        left join SYS_STRU css on ss.STRU_ID = css.PARENT_ID and css.STRU_TYPE in ('4','5','6')
        left join SYS_STRU ss1 on ss.PARENT_ID = ss1.stru_id
        group by ss.STRU_ID order by ss.STRU_ORDER
    </select>

    <select id="queryChildUser" resultType="com.hdkj.lt.core.sys.model.dto.StruDto">
        with recursive organList as (
            select STRU_ID,PARENT_ID,ORGAN_ALIAS,STRU_TYPE,STRU_LEVEL,IS_EMPLOYEE,ss.STRU_ORDER
            from SYS_STRU ss where ss.STRU_ID=#{parentId}
            union
            select ss.STRU_ID,ss.PARENT_ID,ss.ORGAN_ALIAS,ss.STRU_TYPE,ss.STRU_LEVEL,ss.IS_EMPLOYEE,ss.STRU_ORDER
            from SYS_STRU ss inner join organList ol on ol.STRU_ID=ss.PARENT_ID and ss.STRU_TYPE in ('6','9')
        ) select rs.USER_ID struId,rs.USER_ID orgCode,ol.ORGAN_ALIAS as organName,ol.STRU_TYPE as struType
        from organList ol inner join (select sot.ORGAN_TYPE
            from SYS_ORGAN_TYPE sot where sot.ORGAN_TYPE  in ('9')) sot on ol.STRU_TYPE = sot.ORGAN_TYPE
        inner join SYS_USERS rs on ol.STRU_ID=rs.EMPLOYEE_ID and rs.ACCOUNT_STATUS = '1'
        <if test="roleCode!='' and roleCode!=null">
            inner join SYS_USER_ROLE sur on rs.USER_ID = sur.USER_ID
            inner join SYS_ROLES sr on sur.GRANTED_ROLE = sr.ROLE_ID and sr.ROLE_PERMISSION = #{roleCode}
        </if>
        order by ol.STRU_ORDER
    </select>

    <select id="queryPersonnelUserInfoList" resultType="com.hdkj.lt.core.sys.model.dto.StruDto">
        SELECT DISTINCT su.USER_ID AS userId,CONCAT(su.USER_NAME,"/",su.USER_ACCOUNT) AS userName, su.EMPLOYEE_ID AS struId
        FROM SYS_USERS su
        LEFT JOIN SYS_USER_ROLE sur ON su.USER_ID = sur.USER_ID
        LEFT JOIN SYS_ROLES sr ON sur.GRANTED_ROLE = sr.ROLE_ID
        LEFT JOIN SYS_STRU ss ON su.DEPARTMENT_ID = ss.STRU_ID
        WHERE su.ACCOUNT_STATUS = '1'
        AND sr.ROLE_PERMISSION IN <foreach collection="param.rolePermissionList" open="(" item="rolePermission" separator="," close=")">#{rolePermission}</foreach>
        <if test="param.userName != null and param.userName != ''">
            AND su.USER_NAME LIKE CONCAT('%', #{param.userName}, '%')
        </if>
        <if test="param.unitId != null and param.unitId != ''">
            AND su.DEPARTMENT_ID = #{param.unitId}
        </if>
        <if test="param.userIds != null and param.userIds.size > 0">
            OR su.USER_ID IN <foreach collection="param.userIds" open="(" item="userId" separator="," close=")">#{userId}</foreach>
        </if>
        ORDER BY ss.STRU_TYPE IS NULL ASC, ss.STRU_TYPE ASC, ss.STRU_ORDER IS NULL ASC, ss.STRU_ORDER ASC
    </select>

</mapper>
