<?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.lantone.security.mapper.UserMapper">

    <select id="getalle" resultType="com.lantone.security.entity.User">

        select * from sys_user
    </select>

    <select id="getUserOrganizeByUserID" resultType="com.lantone.security.entity.Hospital">
        SELECT
            organize.*
        FROM
            sys_user u,
            sys_user_hospital uh,
            sys_hospital h
        WHERE
            u.id = uh.user_id
        AND h.id = uh.hospital_id
        AND u.is_deleted = 'N'
        AND uh.is_deleted = 'N'
        AND h.is_deleted = 'N'
        <if test="userID != null ">
            AND u.id = #{userID}
        </if>
    </select>
    <select id="getUserRoleByUserID" resultType="com.lantone.security.dto.RoleDTO">
        SELECT
        r.*
        FROM
        sys_user u,
        sys_user_role sr,
        sys_role r
        WHERE
        u.id = sr.user_id
        AND r.id = sr.role_id
        AND u.is_deleted = 'N'
        AND sr.is_deleted = 'N'
        AND r.is_deleted = 'N'
        <if test="userID != null ">
            AND u.id = #{userId}
        </if>
        <if test="organizeId != null ">
            AND r.organize_id = #{organizeId}
        </if>
    </select>
    <select id="getUserSoftwareByUId" resultType="com.lantone.security.dto.SoftwareDTO">
        SELECT DISTINCT
            s.id,
            s.`name`
        FROM
            sys_user u,
            sys_hospital_user hu,
            sys_hospital h,
            sys_user_role ur,
            sys_role r,
            sys_role_software_menu rsm,
            sys_software_menu sm,
            sys_software s
        WHERE
            u.id = hu.user_id
        AND hu.hospital_id = h.id
        AND u.id = ur.user_id
        AND r.id = ur.role_id
        AND rsm.software_menu_id = sm.id
        AND r.id = rsm.role_id
        AND sm.software_id = s.id
        AND r.is_deleted = 'N'
        AND rsm.is_deleted = 'N'
        AND sm.is_deleted = 'N'
        AND s.is_deleted = 'N'
        AND u.id = #{userID}
    </select>
    <select id="getUserSoftwareHospitals" resultType="com.lantone.security.dto.GetUserHospitalsDTO">
        SELECT DISTINCT
            h.id hospitalId,
            h.parent_id parentId,
            h.type,
            h.`name` hospitalName
        FROM
            sys_user u,
            sys_hospital_user hu,
            sys_hospital h,
            sys_user_role ur,
            sys_role r,
            sys_role_software_menu rsm,
            sys_software_menu sm,
            sys_software s
        WHERE
            u.id = hu.user_id
        AND hu.hospital_id = h.id
        AND u.id = ur.user_id
        AND r.id = ur.role_id
        AND rsm.software_menu_id = sm.id
        AND r.id = rsm.role_id
        AND sm.software_id = s.id
        AND r.is_deleted = 'N'
        AND rsm.is_deleted = 'N'
        AND sm.is_deleted = 'N'
        AND s.is_deleted = 'N'
        AND u.id = #{userID}
        AND s.id = #{softwareID}
    </select>
    <select id="getUserByHospitalId" resultType="java.lang.Long">
        SELECT
            id
        FROM
            sys_user u
        WHERE
            u.is_deleted = 'N'
        AND u.`status` = '1'
        AND u.remark = 'Administrators'
        AND u.id IN (
            SELECT
                hu.user_id
            FROM
                sys_hospital_user hu
            WHERE
                hu.is_deleted = 'N'
            AND hu.hospital_id = #{hospitalId}
        )
    </select>
</mapper>