<?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.HospitalUserMapper">
    <select id="getUserPage" resultType="com.lantone.security.dto.GetUserPageDTO">
        SELECT
        u.id userId,
        u.`name`,
        u.username,
        h.id hospitalId,
        h.`name` hospitalName,
        u.job_no jobNo,
        IF (
        u.`status` = 1,
        "启用",
        "禁用"
        ) statusName,
        u.`status`
        FROM
        sys_user u,
        sys_hospital_user hu,
        sys_hospital h
        WHERE
        u.is_deleted = 'N'
        AND hu.is_deleted = 'N'
        AND h.is_deleted = 'N'
        AND u.id = hu.user_id
        AND h.id = hu.hospital_id
        AND (u.remark != 'Administrators' OR u.remark IS NULL)
        <if test="getUserPageVO.localUserID !=null ">
            AND u.creator = #{getUserPageVO.localUserID}
        </if>
        <if test="getUserPageVO.status !=null and getUserPageVO.status !=''">
            AND u.`status` = #{getUserPageVO.status}
        </if>
        <if test="getUserPageVO.username !=null and getUserPageVO.username !=''">
            AND u.username LIKE concat('%',#{getUserPageVO.username},'%')
        </if>
        <if test="getUserPageVO.name !=null and getUserPageVO.name !=''">
            AND u.`name` LIKE concat('%',#{getUserPageVO.name},'%')
        </if>
    </select>
    <select id="getHospitalUserById" resultType="java.lang.Long">
        SELECT
        u.id
        FROM
        sys_user u,
        sys_hospital_user hu
        WHERE
        u.is_deleted = 'N'
        AND hu.is_deleted = 'N'
        AND u.remark = 'Administrators'
        AND u.id = hu.user_id
        AND hu.hospital_id = #{hospitalId}
        <if test="name !=null and name !=''">
            AND u.`name` = #{name}
        </if>
    </select>
    <select id="getUserHospitaDeptById" resultType="com.lantone.security.dto.GetUserHospitalDeptDTO">
        SELECT DISTINCT
            d.hospital_id hospitalId,
            du.dept_id deptId
        FROM
            `sys_dept` d
        JOIN sys_dept_user du ON d.id = du.dept_id
        AND du.is_deleted = 'N'
        WHERE
            d.is_deleted = 'N'
        AND d.`status` = '1'
        AND du.user_id = #{userId}
    </select>
    <select id="getHospitalUser" resultType="com.lantone.security.dto.GetHospitalUserPageDTO">
        SELECT DISTINCT
        u.id,
        u.mobile_phone mobilePhone,
        u.`name`,
        hu.hospitalId,
        hu.hospitalName
        FROM
        `sys_user` u
        LEFT OUTER JOIN (
        SELECT
        hu.hospital_id hospitalId,
        h.`name` hospitalName,
        hu.user_id
        FROM
        sys_hospital_user hu
        JOIN sys_hospital h ON h.id = hu.hospital_id
        ) hu ON u.id = hu.user_id
        LEFT OUTER JOIN (
        SELECT
        d.hospital_id,
        d.id deptId,
        du.user_id
        FROM
        sys_dept_user du
        JOIN sys_dept d ON d.id = du.dept_id
        ) du ON du.hospital_id = hu.hospitalId
        AND hu.user_id = du.user_id
        WHERE
        u.is_deleted = 'N'
        AND u.`status` = '1'
        <if test="getHospitalUserVO.name != null and getHospitalUserVO.name != ''">
            AND u.`name` LIKE concat('%',#{getHospitalUserVO.name},'%')
        </if>
        <if test="getHospitalUserVO.mobilePhone != null and getHospitalUserVO.mobilePhone != ''">
            AND u.mobile_phone LIKE concat('%',#{getHospitalUserVO.mobilePhone},'%')
        </if>
        <if test="getHospitalUserVO.hospitals !=null and getHospitalUserVO.hospitals.size!=0
         and (getHospitalUserVO.depts ==null or getHospitalUserVO.depts.size ==0)">
            AND hu.hospitalId IN
            <foreach collection="getHospitalUserVO.hospitals" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        <if test="getHospitalUserVO.depts !=null and getHospitalUserVO.depts.size!=0
         and (getHospitalUserVO.hospitals ==null or getHospitalUserVO.hospitals.size ==0)">
            AND du.deptId IN
            <foreach collection="getHospitalUserVO.depts" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        <if test="getHospitalUserVO.depts !=null and getHospitalUserVO.depts.size!=0
             and getHospitalUserVO.hospitals !=null and getHospitalUserVO.hospitals.size !=0">
            AND (hu.hospitalId IN
            <foreach collection="getHospitalUserVO.hospitals" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
            OR du.deptId IN
            <foreach collection="getHospitalUserVO.depts" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
            )
        </if>
    </select>
</mapper>