acl_tousu/src/main/java/com/nokia/dao/UserDao.java

package com.nokia.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.nokia.pojo.Role;
import com.nokia.pojo.User;
import com.nokia.vo.*;
import com.nokia.vo.flow.FlowRoleVo;
import com.nokia.vo.flow.FlowUserVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserDao extends BaseMapper<User> {

    /**
     * 支持username模糊查询
     */
    @Select("select distinct r.role_id, r.role_name"
            + " from sqmdb_rpt.acl_user u, sqmdb_rpt.acl_role r, sqmdb_rpt.acl_user_role_city ur"
            + " where u.user_id = ur.user_id"
            + " and r.role_id = ur.role_id"
            + " and r.system='flow'"
            + " and u.user_name like concat('%',#{username},'%')")
    List<FlowRoleVo> findRoleList(String username);

    /**
     * 查询全部
     */
    @Select("select role_id, role_name from sqmdb_rpt.acl_role where system = 'flow'")
    List<FlowRoleVo> findRoleList2();

    /**
     * 查询全部用户（流程所需），未分页
     */
    @Results({
            @Result(column = "org_id", property = "org.orgId"),
            @Result(column = "org_name", property = "org.orgName"),
    })
    @Select("select a.user_id as user_id, a.login_name as login_id, a.user_name as user_name, a.phone as mobile,"
            + " a.email as email, a.city_id as org_id, c1.area_name as org_name, a.area_id as area"
            + " from sqmdb_rpt.acl_user a, sqmdb_rpt.acl_area c1"
            + " where a.city_id = c1.area_id"
            + " and a.deleted = 0")
    List<FlowUserVo> findToPage();

    /**
     * 查询登录用户信息
     */
    @Results({
            @Result(column = "org_id", property = "org.orgId"),
            @Result(column = "org_name", property = "org.orgName"),
    })
    @Select("select a.user_id as user_id, a.login_name as login_id, a.user_name as user_name, a.phone as mobile,"
            + " a.email as email, a.city_id as org_id, c.area_name as org_name, a.area_id as area"
            + " from sqmdb_rpt.acl_user a, sqmdb_rpt.acl_area c"
            + " where a.city_id = c.area_id and a.login_name=#{loginId} and a.deleted = 0")
    FlowUserVo getFlowUserVoByLoginId(String loginId);

    /**
     * 根据用户ID获取用户接口
     */
    @Results({
            @Result(column = "org_id", property = "org.orgId"),
            @Result(column = "org_name", property = "org.orgName"),
    })
    @Select("select a.user_id as user_id, a.login_name as login_id, a.user_name as user_name, a.phone as mobile,"
            + " a.email as email, a.city_id as org_id, c.area_name as org_name, a.area_id as area"
            + " from sqmdb_rpt.acl_user a, sqmdb_rpt.acl_area c"
            + " where a.city_id = c.area_id and user_id=#{userId} and a.deleted = 0")
    FlowUserVo getFlowUserVoByUserId(Integer userId);

    @Select("select r.role_id from  sqmdb_rpt.acl_role r, sqmdb_rpt.acl_user_role_city ur"
            + " where r.role_id = ur.role_id and r.system='flow' and ur.user_id=#{userId}")
    List<Integer> findRoleIdByUserId(Integer userId);

    @Select("select u.user_id from sqmdb_rpt.acl_user u, sqmdb_rpt.acl_user_role_city ur"
            + " where u.user_id = ur.user_id and ur.role_id=#{roleId} and u.deleted = 0")
    List<Integer> findUserIdByRoleId(Integer roleId);

    @Results({
            @Result(column = "org_id", property = "org.orgId"),
            @Result(column = "org_name", property = "org.orgName"),
    })
    @Select("<script>"
            + " select a.user_id as user_id, a.login_name as login_id, a.user_name as user_name, a.phone as mobile,"
            + " a.email as email, a.city_id as org_id, c.area_name as org_name, a.area_id as area"
            + " from sqmdb_rpt.acl_user a, sqmdb_rpt.acl_area c, sqmdb_rpt.acl_user_role_city ur"
            + " where a.user_id = ur.user_id and a.city_id = c.area_id and a.deleted = 0"
            + " <if test=\"loginId != null and loginId !=''\">"
            + " and a.login_name=#{loginId}"
            + " </if>"
            + " <if test=\"roleId != null\">"
            + " and ur.role_id=#{roleId}"
            + " </if>"
            + " <if test=\"userName != null and userName !=''\">"
            + " and a.user_name like concat('%',#{userName},'%')"
            + " </if>"
            + " </script>")
    List<FlowUserVo> findAuthorizedUser(@Param("loginId") String loginId, @Param("roleId") Integer roleId,
                                        @Param("userName") String userName);

    List<Role> getRoleCityByUserId(Integer userId);

    User getByLoginName(String loginName);

    List<User> getByRoleIds(@Param("roleIds") List<Integer> roleIds, @Param("cityIds") List<Integer> cityIds);

    List<String> getPhoneListByRole(@Param("roleId") Integer roleId, @Param("cityId") Integer cityId);

    /**
     * 查询用户的app功能列表
     *
     * @param userId 用户id
     * @param client 客户端
     * @return {@link List}<{@link AppVerificationVo}>
     */
    @Select("select af.id, af.\"name\", af.app_url as url, af.app_icon, af.app_priority as priority"
            + " from sqmdb_rpt.acl_function af"
            + " inner join sqmdb_rpt.acl_user_function auf"
            + " on af.id = auf.function_id"
            + " where auf.user_id = #{userId}"
            + " and (af.client = 0 or af.client = #{client})"
            + " order by af.app_priority desc")
    List<AppVerificationVo> listAppFunctions(@Param("userId") Integer userId, @Param("client") Integer client);

    /**
     * 查询用户的web功能列表
     *
     * @param userId 用户id
     * @param client 客户端
     * @return {@link List}<{@link WebFunctionVo}>
     */
    @Select("select af.id, af.\"name\", af.web_url as url, af.web_icon, af.web_priority as priority, af.\"system\","
            + " as2.system_name"
            + " from sqmdb_rpt.acl_function af"
            + " inner join sqmdb_rpt.acl_user_function auf on af.id = auf.function_id"
            + " inner join sqmdb_rpt.acl_system as2 on af.\"system\" = as2.\"system\""
            + " where auf.user_id = #{userId}"
            + " and (af.client = 0 or af.client = #{client})"
            + " order by af.web_priority desc")
    List<WebFunctionVo> listWebFunctions(@Param("userId") Integer userId, @Param("client") Integer client);

    /**
     * 判断用户是否有权限
     */
    @Select("select exists (select 1 from sqmdb_rpt.acl_user_role_city where role_id = #{roleId} and user_id = #{userId})")
    boolean hasRole(@Param("roleId") Integer roleId, @Param("userId") Integer userId);

    /**
     * 查询用户列表
     */
    @Select("<script>"
            + " select au.*, aa1.area_name as province_name, aa2.area_name as city_name, aa3.area_name as area_name"
            + " from sqmdb_rpt.acl_user au"
            + " left join sqmdb_rpt.acl_area aa1 on au.province_id = aa1.area_id"
            + " left join sqmdb_rpt.acl_area aa2 on au.city_id = aa2.area_id"
            + " left join sqmdb_rpt.acl_area aa3 on au.area_id = aa3.area_id"
            + " where au.deleted = 0 and au.test_user = 0"
            + " <if test=\"loginName != null and loginName != ''\">"
            + "      and au.login_name like concat(#{loginName}, '%')"
            + " </if>"
            + " <if test=\"userName != null and userName != ''\">"
            + "      and au.user_name like concat(#{userName}, '%')"
            + " </if>"
            + " <if test=\"org != null and org != ''\">"
            + "      and au.org like concat('%', #{org}, '%')"
            + " </if>"
            + " <if test='areaId'>"
            + "      and (au.province_id = #{areaId} or au.city_id = #{areaId} or au.area_id = #{areaId})"
            + " </if>"
            + " </script>"
    )
    List<ListUserVo> list(Page<ListUserVo> page, @Param("loginName") String loginName,
                          @Param("userName") String userName, @Param("org") String org,
                          @Param("areaId") Integer areaId);

    /**
     * 查询top用户信息
     *
     * @param loginName 登录名
     * @return {@link TopUserVo}
     */
    @Select("select atu.*, aa1.area_name as province_name, aa2.area_name as city_name, aa3.area_name as area_name"
            + " from sqmdb_rpt.acl_top_user atu"
            + " left join sqmdb_rpt.acl_area aa1 on atu.province_id = aa1.area_id"
            + " left join sqmdb_rpt.acl_area aa2 on atu.city_id = aa2.area_id"
            + " left join sqmdb_rpt.acl_area aa3 on atu.area_id = aa3.area_id"
            + " where login_name = #{loginName}")
    TopUserVo getTopUserByLoginName(String loginName);

    /**
     * 判断账号是否存在且状态正常
     *
     * @param loginName 登录名
     * @return {@link Object}
     */
    @Select("select 1 from sqmdb_rpt.acl_user where deleted = 0 and login_name = #{loginName}")
    Object hasLoginName(String loginName);

    /**
     * 获取用户详细
     *
     * @param userId 用户id
     * @return {@link GetUserDetailVo}
     */
    @Select("select au.*, au.org as org_name, aa1.area_name as province_name, aa2.area_name as city_name,"
            + " aa3.area_name as area_name"
            + " from sqmdb_rpt.acl_user au"
            + " left join sqmdb_rpt.acl_area aa1 on au.province_id = aa1.area_id"
            + " left join sqmdb_rpt.acl_area aa2 on au.city_id = aa2.area_id"
            + " left join sqmdb_rpt.acl_area aa3 on au.area_id = aa3.area_id"
            + " where au.user_id = #{userId}")
    GetUserDetailVo getUserDetail(Integer userId);

    /**
     * 获取用户详细角色
     *
     * @param userId 用户id
     * @return {@link List}<{@link GetUserDetailRoleVo}>
     */
    @Select("select aurc.role_id, aurc.city_id, ar.role_name, ar.\"system\", ar.function_id,"
            + " af.\"name\" as function_name, as2.system_name, aa.area_name as city_name"
            + " from sqmdb_rpt.acl_user_role_city aurc"
            + " inner join sqmdb_rpt.acl_role ar on aurc.role_id = ar.role_id"
            + " inner join sqmdb_rpt.acl_area aa on aurc.city_id = aa.area_id"
            + " inner join sqmdb_rpt.acl_system as2 on ar.\"system\" = as2.\"system\""
            + " inner join sqmdb_rpt.acl_function af on af.id = ar.function_id"
            + " where aurc.user_id = #{userId}"
            + " order by af.web_priority desc")
    List<GetUserDetailRoleVo> getUserDetailRole(Integer userId);

    /**
     * 获取账号角色
     *
     * @param loginName 登录名
     * @return {@link List}<{@link GetRoleByLoginNameVo}>
     */
    @Select("select aurc.role_id, aurc.city_id, ar.role_name, ar.\"system\", ar.function_id,"
            + " af.\"name\" as function_name, as2.system_name, aa.area_name as city_name"
            + " from sqmdb_rpt.acl_user_role_city aurc"
            + " inner join sqmdb_rpt.acl_user au on aurc.user_id = au.user_id"
            + " inner join sqmdb_rpt.acl_role ar on aurc.role_id = ar.role_id"
            + " inner join sqmdb_rpt.acl_system as2 on ar.\"system\" = as2.\"system\""
            + " inner join sqmdb_rpt.acl_area aa on aurc.city_id = aa.area_id"
            + " inner join sqmdb_rpt.acl_function af on af.id = ar.function_id"
            + " where au.login_name = #{loginName}"
            + " order by af.web_priority desc")
    List<GetRoleByLoginNameVo> getRoleByLoginName(String loginName);

    /**
     * 根据账号查询用户信息
     *
     * @param loginName 登录名
     * @return {@link User}
     */
    @Select("select * from sqmdb_rpt.acl_user where login_name = #{loginName}")
    User selectByLoginName(String loginName);

    /**
     * 存在其他地市用户
     *
     * @param cityId 地市id
     * @param list   用户id列表
     * @return {@link Object}
     */
    @Select("<script>"
            + " select 1 from sqmdb_rpt.acl_user"
            + " where city_id != #{cityId} and user_id in"
            + " <foreach open=\"(\" close=\")\" collection=\"list\" item=\"item\" separator=\",\">"
            + "   #{item}"
            + " </foreach>"
            + " limit 1"
            + " </script>")
    Object exceptCityUser(Integer cityId, List<Integer> list);

    /**
     * 通过用户id获取地市id
     *
     * @param list 列表
     * @return {@link List}<{@link Integer}>
     */
    @Select("<script>"
            + " select distinct city_id from sqmdb_rpt.acl_user"
            + " where user_id in"
            + " <foreach open=\"(\" close=\")\" collection=\"list\" item=\"item\" separator=\",\">"
            + "   #{item}"
            + " </foreach>"
            + " </script>")
    List<Integer> getCityIdsByUserIds(List<Integer> list);

    /**
     * 查询所有用户，过滤已删除和测试用户
     *
     * @return {@link List}<{@link UserVo}>
     */
    @Select("select * from sqmdb_rpt.acl_user where deleted = 0 and test_user = 0 order by user_id")
    List<UserVo> baseList();

    /**
     * 查询地市管理者
     * @param cityId 地市id
     */
    @Select("select\n"
            + "    *\n"
            + "from\n"
            + "    sqmdb_rpt.acl_user a\n"
            + "where\n"
            + "    test_user = 0\n"
            + "    and deleted = 0\n"
            + "    and exists (\n"
            + "    select\n"
            + "        1\n"
            + "    from\n"
            + "        sqmdb_rpt.acl_user_role_city b\n"
            + "    where\n"
            + "        b.role_id = -1\n"
            + "        and a.user_id = b.user_id)\n"
            + "    and city_id = #{cityId}")
    List<User> getManagersByCityId(@Param("cityId") Integer cityId);

    @Select("select city_id from sqmdb_rpt.acl_top_user where login_name = #{loginName}")
    Integer getAclTopUserCityId(@Param("loginName") String loginName);
}