<?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.energy.manage.service.mappers.system.SysUserInfoMapper">

    <select id="getUserByName" resultType="com.energy.manage.service.domain.vo.system.UserRoleVo">
        SELECT
        u.id AS userId,
        u.user_name AS userName,
        u.user_phone AS userPhone,
        u.login_name as loginName,
        r.id AS roleId,
        r.role_description AS roleName,
        u.state AS state
        FROM sys_user_info u
        INNER JOIN sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN sys_role r ON r.id = ur.role_id
        where 1=1
        <if test="null != roleId and roleId > 0">
            and ur.role_id = #{roleId}
        </if>
        <if test="null != userName and '' != userName">
            and u.user_name LIKE CONCAT('%',#{userName,jdbcType=VARCHAR},'%')
        </if>
        ORDER BY u.id ASC
    </select>

    <select id="getUserById" resultType="com.energy.manage.service.domain.vo.system.UserRoleVo">
        SELECT
        u.id AS userId,
        u.user_name AS userName,
        u.user_phone AS userPhone,
        u.login_name as loginName,
        r.id AS roleId,
        r.role_description AS roleName,
        u.state AS state
        FROM sys_user_info u
        INNER JOIN sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN sys_role r ON r.id = ur.role_id
        WHERE u.id = #{userId}
    </select>

    <select id="getOldRoleByUserId" resultType="com.energy.manage.common.po.system.SysRolePO">
        SELECT
        id AS id,
        role_description AS roleDescription,
        role_code AS roleCode,
        role_available AS roleAvailable
        FROM
        sys_user_role ur
        LEFT JOIN sys_role r ON r.id = ur.role_id
        WHERE ur.user_id = #{userId}
    </select>

    <select id="getUserInfoByUserId" resultType="com.energy.manage.service.domain.vo.system.UserVo">
        SELECT
        u.id AS userId,
        u.user_name AS userName,
        u.user_phone AS userPhone,
        u u.login_name as loginName,
        r.id AS roleId,
        r.role_description AS roleName
        FROM
        sys_user_info u
        LEFT JOIN sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN sys_role r ON r.id = ur.role_id
        WHERE u.id = #{userId}
    </select>
    <select id="quertNonBoundTeacherUser" resultType="com.energy.manage.service.domain.vo.system.UserVo">

        SELECT
        u.id AS userId,
        u.user_name AS userName,
        u.user_phone AS userPhone
        FROM
        sys_user_info u

        <where>

            state=1 and id not in (select user_id from teacher)
            <if test="null!=userName">

                and user_name LIKE CONCAT('%',#{userName,jdbcType=VARCHAR},'%')

            </if>

        </where>
        order by create_time desc
    </select>
</mapper>
