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(String loginId, Integer roleId, String userName);

        List<Role> getRoleCityByUserId(Integer userId);

        User getByLoginName(String loginName);

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

        List<String> getPhoneListByRole(Integer roleId, 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(Integer userId, 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 " +
                "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.web_priority desc")
        List<WebFunctionVo> listWebFunctions(Integer userId, Integer client);

        /**
         * 查询用户列表
         *
         */
        @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 " +
                "<if test=\"dto.loginName != null and dto.loginName != ''\"> " +
                "     and au.login_name like concat(#{dto.loginName}, '%') " +
                "</if> " +
                "<if test=\"dto.userName != null and dto.userName != ''\"> " +
                "     and au.user_name like concat(#{dto.userName}, '%') " +
                "</if> " +
                "<if test=\"dto.org != null and dto.org != ''\"> " +
                "     and au.org like concat('%', #{dto.org}, '%') " +
                "</if> " +
                "<if test='dto.areaId'> " +
                "     and (au.province_id = #{dto.areaId} or au.city_id = #{dto.areaId} or au.area_id = #{dto.areaId}) " +
                "</if> " +
                "</script>"
        )
        List<ListUserVo> list(Page<ListUserVo> page, ListUserDto dto);

        /**
         * 查询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}")
        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}")
        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 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 order by user_id")
        List<UserVo> baseList();
}