guangxi/GxPress/Repository/GxPress.Repository.Implement/UserRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using GxPress.Common.Extensions;
using GxPress.Common.AppOptions;
using GxPress.Common.Exceptions;
using GxPress.Common.Page;
using GxPress.Common.Tools;
using GxPress.Entity;
using GxPress.EnumConst;
using GxPress.Repository.Interface;
using GxPress.Request.User;
using GxPress.Result.User;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using SqlKata;
using SS.Mail.Core;
using Datory;
using Microsoft.Extensions.Caching.Distributed;
using Dapper;
using GxPress.Result.App.User;
using GxPress.Result.Job;
using GxPress.Common.Http;
using Newtonsoft.Json.Linq;
using Microsoft.Extensions.Logging;

namespace GxPress.Repository.Implement
{
    public class UserRepository : IUserRepository
    {
        private readonly Repository<User> _repository;
        private readonly Repository<Department> _departmentRepository;
        private readonly Repository<Role> _roleRepository;
        private readonly IMapper _mapper;
        private readonly IWebHostEnvironment _environment;

        private readonly ILogger<UserRepository> _logger;
        private readonly IDistributedCache _cache;
        //private readonly string _connectionString;
        //private readonly string _databaseTypestr;
        private readonly string _connectionString;
        private readonly string _databaseTypeStr;
        public UserRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper,
            IWebHostEnvironment environment, IDistributedCache cache, ILogger<UserRepository> logger)
        {
            _databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType;
            _connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
            // _databaseType
            // str = dbOptionsAccessor.CurrentValue.DatabaseType;
            var databaseType =
                StringUtils.ToEnum<DatabaseType>(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql);
            //_connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
            var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString);
            _repository = new Repository<User>(database);
            _environment = environment;
            _departmentRepository = new Repository<Department>(database);
            _roleRepository = new Repository<Role>(database);
            _mapper = mapper;
            _cache = cache;
            _logger = logger;
        }

        public IDatabase Database => _repository.Database;
        public string TableName => _repository.TableName;
        public List<TableColumn> TableColumns => _repository.TableColumns;

        /// <summary>
        /// 根据id集合查询用户
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<IEnumerable<User>> UserListInsAsync(List<int> ids)
        {
            return await _repository.GetAllAsync(Q.WhereIn(nameof(User.Id), ids));
        }

        /// <summary>
        /// 根据姓名查询用户
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public async Task<IEnumerable<UserInfoResult>> UserByNameAsync(string name)
        {
            var result = await _repository.GetAllAsync(Q.WhereLike(nameof(User.Name), $"%{name}%"));
            foreach (var item in result)
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            return result.Select(n => _mapper.Map<UserInfoResult>(n));
        }
        public async Task<UserDetail> GetDetailAsync(int id)
        {
            var user = await _repository.GetAsync(id);
            user.QRCoder = StringUtils.AddDomain(user.QRCoder);
            user.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
            var userDetail = _mapper.Map<UserDetail>(user);
            if (user.DepartmentId > 0)
            {
                var department = await _departmentRepository.GetAsync(user.DepartmentId);
                userDetail.DepartmentName = department.Name;
            }
            return userDetail;
        }

        public async Task<User> GetAsync(int id)
        {
            var user = await _repository.GetAsync(id);
            return user;
        }
        public async Task<User> GetAsync(SqlKata.Query query)
        {
            var user = await _repository.GetAsync(query);
            return user;
        }
        public async Task<string> GetNameAsync(int id)
        {
            if (id <= 0) return string.Empty;

            return await _repository.GetAsync<string>(Q
                .Select(nameof(User.Name))
                .Where(nameof(User.Id), id)
            );
        }

        public async Task<string> GetAvatarUrlAsync(int id)
        {
            if (id <= 0) return string.Empty;

            var avatarUrl = await _repository.GetAsync<string>(Q
                .Select(nameof(User.AvatarUrl))
                .Where(nameof(User.Id), id)
            );
            return StringUtils.AddDomainMin(avatarUrl);
        }

        public string GetAvatarUrl(User user)
        {
            if (user == null) return string.Empty;

            return StringUtils.AddDomainMin(user.AvatarUrl);
        }

        public async Task<User> GetByPhoneAsync(string phone)
        {
            return await _repository.GetAsync(Q.Where(nameof(User.Phone), phone));
        }
        /// <summary>
        /// openId微信认证
        /// </summary>
        /// <param name="openId"></param>
        /// /// <returns></returns>
        public async Task<User> GetByOpenIdAsync(string openId)
        {
            return await _repository.GetAsync(Q.Where(nameof(User.OpenId), openId));
        }
        /// <summary>
        /// 绑定微信
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateByOpendIdAsync(UserSignInRequest request)
        {
            var key = $"login:{request.Phone}";
            var user = new User();
            var code = _cache.GetString(key).Replace("\"", "");
            if (string.IsNullOrEmpty(code)) throw new BusinessException("验证码已过期");
            if (!request.Code.Equals(code, StringComparison.OrdinalIgnoreCase)) throw new BusinessException("验证码错误");
            user = await GetByPhoneAsync(request.Phone);
            //用户不存在,自动注册
            if (user == null)
                throw new BusinessException("用户不存在");

            return await _repository.UpdateAsync(Q.Where(nameof(User.Phone), request.Phone).Set(nameof(User.OpenId), request.OpenId)) > 0;
        }
        public async Task<bool> DeleteUsersAsync(IEnumerable<int> userIds)
        {
            return await _repository.DeleteAsync(Q.WhereIn(nameof(User.Id), userIds)) > 0;
        }

        public async Task<bool> ChangeRoleAsync(IEnumerable<int> userIds, int roleId)
        {
            return await _repository.UpdateAsync(Q
                       .Set(nameof(User.RoleId), roleId)
                       .WhereIn(nameof(User.Id), userIds)
                   ) > 0;
        }

        public async Task<bool> ChangeDepartmentAsync(IEnumerable<int> userIds, int departmentId)
        {
            return await _repository.UpdateAsync(Q
                       .Set(nameof(User.DepartmentId), departmentId)
                       .WhereIn(nameof(User.Id), userIds)
                   ) > 0;
        }

        public async Task<bool> UpdateAsync(User user)
        {
            return await _repository.UpdateAsync(user);
        }

        public async Task<int> GetCountAsync(int departmentId, int roleId, string keyword)
        {
            var query = Q.NewQuery();
            if (departmentId > 0)
            {
                query.Where(nameof(User.DepartmentId), departmentId);
            }

            if (roleId > 0)
            {
                query.Where(nameof(User.RoleId), roleId);
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                keyword = "%" + keyword + "%";
                query.Where(q =>
                    q.WhereLike(nameof(User.Name), keyword)
                        .OrWhereLike(nameof(User.Email), keyword)
                        .OrWhereLike(nameof(User.Phone), keyword)
                );
            }

            return await _repository.CountAsync(query);
        }

        /// <summary>
        /// Set the limit and offset for a given page.
        /// </summary>
        /// <param name="page"></param>
        /// <param name="perPage"></param>
        /// <param name="departmentId"></param>
        /// <param name="roleId"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public async Task<IEnumerable<User>> GetPageListAsync(int page, int perPage, int departmentId, int roleId,
            string keyword)
        {
            var query = Q.ForPage(page, perPage);
            if (departmentId > 0)
            {
                query.Where(nameof(User.DepartmentId), departmentId);
            }

            if (roleId > 0)
            {
                query.Where(nameof(User.RoleId), roleId);
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                keyword = "%" + keyword + "%";
                query.Where(q =>
                    q.WhereLike(nameof(User.Name), keyword)
                        .OrWhereLike(nameof(User.Email), keyword)
                        .OrWhereLike(nameof(User.Phone), keyword)
                );
            }

            return await _repository.GetAllAsync(query);
        }

        public async Task<IEnumerable<User>> GetListAsync(int departmentId, int roleId, string keyword)
        {
            var query = Q.NewQuery();
            if (departmentId > 0)
            {
                query.Where(nameof(User.DepartmentId), departmentId);
            }

            if (roleId > 0)
            {
                query.Where(nameof(User.RoleId), roleId);
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                query.Where(q =>
                    q.WhereLike(nameof(User.Name), keyword)
                        .OrWhereLike(nameof(User.Email), keyword)
                        .OrWhereLike(nameof(User.Phone), keyword)
                );
            }

            return await _repository.GetAllAsync(query);
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<UserSignInResult> SignInAsync(UserSignInRequest request)
        {
            var result = new UserSignInResult();
            var key = $"login:{request.Phone}";
            try
            {
                var user = new User();
                if (string.IsNullOrWhiteSpace(request.OpenId))
                {
                    //var code = _cache.GetString(key).Replace("\"", "");
                    var dictionaryKeyValue = new Dictionary<string, string>();
                    dictionaryKeyValue.Add("19977191891", "202088");
                    dictionaryKeyValue.Add("17776699684", "202088");
                    dictionaryKeyValue.Add("18978939733", "202088");
                    if (dictionaryKeyValue.Any(n => n.Key == request.Phone) && dictionaryKeyValue.Any(n => n.Value == request.Code))
                    {
                        user = await GetByPhoneAsync(request.Phone);
                    }
                    else
                    {
                        //var code = "123456";
                        var code = _cache.GetString(key).Replace("\"", "");
                        if (string.IsNullOrEmpty(code)) throw new BusinessException("验证码已过期");
                        if (!request.Code.Equals(code, StringComparison.OrdinalIgnoreCase)) throw new BusinessException("验证码错误");
                        user = await GetByPhoneAsync(request.Phone);
                    }
                    //用户不存在,自动注册
                    if (user == null)
                    {
                        // user = new User();
                        // user.Name = request.Phone;
                        // user.Phone = request.Phone;
                        // user.Gender = "Male";
                        // user.Id = await InsertAsync(user);
                        // result.UserId = user.Id;
                        // result.DepartmentId = user.DepartmentId;
                        // result.User = _mapper.Map<UserResult>(user);
                        // result.IsAddUser = true;
                        // result.UserEntity = user;
                        // return result;
                        throw new BusinessException("验证码或手机号错误");
                    }
                }
                else
                    user = await GetByOpenIdAsync(request.OpenId);
                result.UserId = user.Id;
                result.DepartmentId = user.DepartmentId;
                result.User = _mapper.Map<UserResult>(user);
                return result;
            }
            catch (System.Exception)
            {
                throw new BusinessException("验证码或手机号错误");
            }
        }
        public async Task<int> InsertAsync(User user)
        {
            if (user == null) throw new Exception("用户不能为空");
            if (string.IsNullOrEmpty(user.Name)) throw new Exception("用户不能为空");

            if (!string.IsNullOrEmpty(user.Phone) &&
                await _repository.ExistsAsync(Q.Where(nameof(User.Phone), user.Phone)))
            {
                throw new Exception("用户手机号已存在，请更换手机号");
            }
            //头像处理
            user.AvatarUrl = StringUtils.RemoveDomain(user.AvatarUrl);
            user.IsUpload = false;
            user.Id = await _repository.InsertAsync(user);
            user = await _repository.GetAsync(user.Id);
            //创建二维码
            var qrCodeData = new { key = QRCodeTypeConst.UserInfo, value = user.Guid };
            var resultPath =
                FileHelper.SaveQRCodeFile(StringUtils.GetWebRootPath(_environment.WebRootPath),
                    JsonConvert.SerializeObject(qrCodeData));
            user.QRCoder = resultPath.RelativePath;
            await _repository.UpdateAsync(user);
            return user.Id;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(int id)
        {
            var user = await GetAsync(id);
            if (user == null) throw new BusinessException("该用户不存在");
            return await DeleteAsync(id);
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(int id, User request)
        {
            var user = await GetAsync(id);
            if (user == null) throw new BusinessException("该用户不存在");
            if (request.DepartmentId > 0)
                user.DepartmentId = request.DepartmentId;
            if (!string.IsNullOrEmpty(request.Nick))
                user.Nick = request.Nick;
            if (!string.IsNullOrEmpty(request.Description))
                user.Description = request.Description;
            if (!string.IsNullOrEmpty(request.Gender))
                user.Gender = request.Gender;
            if (!string.IsNullOrEmpty(request.Name))
                user.Name = request.Name;
            if (!string.IsNullOrEmpty(request.Position))
                user.Position = request.Position;
            if (request.RoleId > 0)
                user.RoleId = request.RoleId;
            if (!string.IsNullOrEmpty(request.Signature))
                user.Signature = request.Signature;
            //用户头像
            if (!string.IsNullOrEmpty(request.AvatarUrl))
                user.AvatarUrl = StringUtils.RemoveDomain(request.AvatarUrl);
            else
                user.AvatarUrl = StringUtils.RemoveDomain(user.AvatarUrl);
            user.Email = request.Email;
            if (!string.IsNullOrWhiteSpace(request.Phone))
                user.Phone = request.Phone;
            user.QRCoder = StringUtils.RemoveDomain(user.QRCoder);
            return await UpdateAsync(user);
        }

        /// <summary>
        /// 用户分页列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<UserResult>> GetPagedListAsync(UserPageSearchRequest request)
        {
            var pagedList = new PagedList<UserResult>
            {
                Total = await GetCountAsync(request.DepartmentId, request.RoleId, request.Keyword)
            };
            var list = await GetPageListAsync(request.Page, request.PerPage, request.DepartmentId, request.RoleId,
                request.Keyword);
            var items = list.Select(user => _mapper.Map<UserResult>(user)).ToList();
            foreach (var item in items)
            {
                //获取部门
                var department = await _departmentRepository.GetAsync(item.DepartmentId);
                if (department != null)
                    item.DepartmentName = department.Name;
                var role = await _roleRepository.GetAsync(item.RoleId);
                if (role != null)
                    item.RoleName = role.Name;
            }

            pagedList.Items = items;
            return pagedList;
        }

        public async Task<List<UserResult>> GetListAsync(ArticleSearchRequest request)
        {
            var list = await GetListAsync(request.DepartmentId, request.RoleId, request.Keyword);
            var allList = new List<UserResult>();
            foreach (var user in list)
            {
                var result = _mapper.Map<UserResult>(user);
                allList.Add(result);
            }

            return allList;
        }

        /// <summary>
        /// 更新excel用户数据
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserExcelAsync(List<User> request)
        {
            try
            {
                foreach (var item in request)
                {
                    var user = await _repository.GetAsync(Q.Where(nameof(User.Phone), item.Phone));
                    if (user != null)
                    {
                        continue;
                    }

                    var userId = await _repository.InsertAsync(item);
                    //
                    user = await _repository.GetAsync(userId);
                    var resultPath =
                        FileHelper.SaveQRCodeFile(StringUtils.GetWebRootPath(_environment.WebRootPath), item.Guid);
                    user.QRCoder = resultPath.RelativePath;
                    await _repository.UpdateAsync(user);
                    await CreateMiUserAsync(item);
                }
            }
            catch (Exception e)
            {
                throw new BusinessException(e.Message);
            }

            return true;
        }

        /// <summary>
        /// 私信
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserPrivateLetterAsync(UserPrivateLetterRequest request)
        {
            var user = await _repository.GetAsync(request.Id);
            if (user == null)
                throw new BusinessException("该用户不存在");
            user.IsPrivateLetter = !user.IsPrivateLetter;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 通知
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserNoticeAsync(UserNoticeRequest request)
        {
            var user = await _repository.GetAsync(request.Id);
            if (user == null)
                throw new BusinessException("该用户不存在");
            user.IsNotice = !user.IsNotice;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 回复
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserReplyAsync(UserReplyRequest request)
        {
            var user = await _repository.GetAsync(request.Id);
            if (user == null)
                throw new BusinessException("该用户不存在");
            user.IsReply = !user.IsReply;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 静音
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserMuteAsync(UserMuteRequest request)
        {
            var user = await _repository.GetAsync(request.Id);
            if (user == null)
                throw new BusinessException("该用户不存在");
            user.IsMute = !user.IsMute;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 震动
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserShakeAsync(UserShakeRequest request)
        {
            var user = await _repository.GetAsync(request.Id);
            if (user == null)
                throw new BusinessException("该用户不存在");
            user.IsShake = !user.IsShake;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 用户修改密码
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserPhoneAsync(UserUpdatePhoneRequest request)
        {
            var key = $"login:{request.Phone}";   
            var code = _cache.GetString(key).Replace("\"", "");
            if (string.IsNullOrEmpty(code)) throw new BusinessException("验证码已过期");
            if (!request.Code.Equals(code, StringComparison.OrdinalIgnoreCase)) throw new BusinessException("验证码错误");

            var user = await GetAsync(request.UserId);

            //用户不存在,自动注册
            if (user == null)
            {
                throw new BusinessException("该用户不存在");
            }

            user.Phone = request.Phone;

            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 用户邮箱验证
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> SendEmailVerifyCodeAsync(UserEmailVerifyCodeRequest request)
        {
            //默认配置
            ConfigInfo configInfo = new ConfigInfo();
            var code = RandomGenerator.GetNumberString(6);
            var key = $"email:{request.Email}";
            //if (await RedisHelper.ExistsAsync(key)) throw new BusinessException("发送太频繁");
            MailManager.Send(configInfo, request.Email, "邮箱验证", "邮箱验证", $"验证号码:{code}", out var message);
            if (!string.IsNullOrEmpty(message))
            {
                throw new BusinessException(message);
            }

            //保存验证码
            await _cache.SetAsync(key, code, new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(300)
            });

            return true;
        }


        /// <summary>
        /// 用户修改密码
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUserEmailAsync(UserUpdateEmailRequest request)
        {
            var key = $"email:{request.Email}";
            var code = await _cache.GetStringAsync(key);
            if (string.IsNullOrEmpty(code)) throw new BusinessException("验证码已过期");
            if (!request.Code.Equals(code, StringComparison.OrdinalIgnoreCase)) throw new BusinessException("验证码错误");
            var user = await _repository.GetAsync(request.UserId);
            //用户不存在,自动注册
            if (user == null)
            {
                throw new BusinessException("该用户不存在");
            }

            user.Email = request.Email;
            return await _repository.UpdateAsync(user);
        }

        /// <summary>
        /// 根据部门ID获取用户
        /// </summary>
        /// <param name="departmentId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<User>> GetUserByDepartmentIdAsync(int departmentId)
        {
            return await _repository.GetAllAsync(Q.Where(nameof(User.DepartmentId), departmentId));
        }

        public async Task<IEnumerable<int>> GetUserIdListByDepartmentIdAsync(int departmentId)
        {
            return await _repository.GetAllAsync<int>(Q
                .Select(nameof(User.Id))
                .Where(nameof(User.DepartmentId), departmentId)
            );
        }

        public async Task<IEnumerable<int>> GetUserIdListByRoleIdAsync(int roleId)
        {
            return await _repository.GetAllAsync<int>(Q
                .Select(nameof(User.Id))
                .Where(nameof(User.RoleId), roleId)
            );
        }

        /// <summary>
        /// 创建环信用户
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> CreateMiUserAsync(User request)
        {
            try
            {
                var imServiceUrl = ConfigHelper.GetValue("ServiceAddress:ImUrl");
                string result = HttpClientHelper.GetResponseData("{\"username\":\"" + request.Phone + "\", \"password\": \"123456\",\"nickname\":\"" + request.Name + "\"}", $"{imServiceUrl}/users");
                if (!string.IsNullOrEmpty(result))
                {
                    _logger.LogDebug(result);
                    _logger.LogError(result);
                    var model = JsonConvert.DeserializeObject<dynamic>(result);
                    var entities = model["entities"];
                    var jtokenEntities = (JToken)entities;
                    request.Phone = jtokenEntities[0]["username"].ToString();
                    //成功后修改用户
                    var userEntity = await GetByPhoneAsync(request.Phone);
                    userEntity.ImId = request.Phone;
                    await _repository.UpdateAsync(userEntity);
                }
            }
            catch (Exception e)
            {
                throw new BusinessException(e.Message);
            }


            return true;
        }

        /// <summary>
        /// 用户模糊查询
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<UserInfoResult>> SearchUserNameAsync(SearchUserNameRequest request)
        {
            var departments = new List<Department>();
            var notInUserIds = "";
            if (request.UserIds.Count > 0)
            {
                if (!request.IsShow)
                {
                    notInUserIds = "and Id not in(";
                    if (request.SearchType == 2 && !string.IsNullOrWhiteSpace(request.Key) && request.DepartmentId > 0)
                        notInUserIds = "and a.Id not in(";
                    if (request.SearchType == 1 && !string.IsNullOrWhiteSpace(request.Key) && request.DepartmentId > 0)
                        notInUserIds = "and Id not in(";
                    foreach (var userId in request.UserIds)
                    {
                        notInUserIds += $"{userId},";
                    }
                    notInUserIds = notInUserIds.Remove(notInUserIds.Length - 1, 1);
                    notInUserIds += ")";
                }
            }
            var sql = $"select * from ccpph.tede_user  where  (Name like '%{request.Key}%' or Phone like '%{request.Key}%' or Email like '%{request.Key}%') {notInUserIds}";
            if (request.SearchType == 2 && !string.IsNullOrWhiteSpace(request.Key) && request.DepartmentId > 0)
                sql = $"select a.* from ccpph.tede_user a inner join ccpph.tede_address_book_group_user c on a.Id=c.UserId where c.AddressBookGroupId={request.DepartmentId} and (a.Name like '%{request.Key}%' or a.Phone like '%{request.Key}%' or a.Email like '%{request.Key}%') {notInUserIds}";
            if (request.SearchType == 1 && !string.IsNullOrWhiteSpace(request.Key) && request.DepartmentId > 0)
            {
                //递归获取部门Id
                departments = await GetDepartmentAsync(request.DepartmentId, departments);
                var departmentValue = $"({request.DepartmentId},";
                foreach (var item in departments)
                    departmentValue += $"{item.Id},";
                departmentValue = departmentValue.Remove(departmentValue.Length - 1, 1);
                departmentValue += ")";
                sql = $"select * from ccpph.tede_user where DepartmentId in {departmentValue} and (Name like '%{request.Key}%' or Phone like '%{request.Key}%' or Email like '%{request.Key}%') {notInUserIds}";
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<UserInfoResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                //获取上级
                departments = new List<Department>();
                departments = await GetUpDepartmentAsync(item.DepartmentId, departments);
                foreach (var department in departments)
                    item.DepartmentName = "-" + department.Name + item.DepartmentName;
                if (!string.IsNullOrWhiteSpace(item.DepartmentName))
                    item.DepartmentName = item.DepartmentName.Remove(0, 1);
                if (request.IsShow)
                    if (request.UserIds.Contains(item.Id))
                        item.IsDisable = true;
            }
            return result;
        }
        public async Task<List<Entity.Department>> GetUpDepartmentAsync(int id, List<Department> departments)
        {
            var sql = $"select * from ccpph.tede_department where Id={id}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryFirstOrDefaultAsync<Department>(sql);
            if (result == null)
                return departments;
            departments.Add(result);
            await GetUpDepartmentAsync(result.ParentId, departments);
            return departments;
        }
        public async Task<List<Entity.Department>> GetDepartmentAsync(int pid, List<Department> departments)
        {
            var sql = $"select * from ccpph.tede_department where ParentId={pid}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<Department>(sql);
            if (result.Count() == 0)
                return departments;
            departments.AddRange(result);
            foreach (var item in result)
                await GetDepartmentAsync(item.Id, departments);
            return departments;
        }
        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<User>> FindAllUserAsync(SqlKata.Query query)
        {
            return await _repository.GetAllAsync(query);
        }

        /// <summary>
        /// 根据Guid获取用户数据
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public async Task<UserDetail> GetGuidAsync(string guid)
        {
            var user = await _repository.GetAsync(Q.Where(nameof(User.Guid), guid));
            user.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
            user.QRCoder = StringUtils.AddDomain(user.QRCoder);
            return _mapper.Map<UserDetail>(user);
        }
        public async Task<IEnumerable<Entity.User>> GetAllUserIm()
        {
            string sql = "SELECT * FROM ccpph.tede_user where ImId is null or ImId=''";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            return await connection.QueryAsync<User>(sql);
        }
        public async Task<int> CountAsync(Query query)
        {
            return await _repository.CountAsync(query);
        }

        public async Task<IEnumerable<User>> GetAllAsync(Query query)
        {
            return await _repository.GetAllAsync(query);
        }
        /// <summary>
        /// 根据用户ID获取哦手机号码
        /// </summary>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetUserIdsAsync(List<int> userIds)
        {
            return await _repository.GetAllAsync<string>(Q.Select(nameof(User.Phone)).WhereIn(nameof(User.Id), userIds));
        }

        public Task<IEnumerable<User>> FindAllUserAsync()
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 获取用户未读数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<UserCountResult> GetUserCountAsync(int userId)
        {
            var result = new UserCountResult();
            string sql = $"select count(1) from ccpph.tede_middle where FolderType=1 and UserId={userId} and MiddleId in (select NoticeId from ccpph.tede_notice_addressee where UserId={userId} and IsRead=0 and IsDelete=0)";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var noticeCount = await connection.ExecuteScalarAsync<int>(sql);
            sql = $"select count(1) from ccpph.tede_middle where FolderType=5 and UserId={userId} and MiddleId in (select MissiveId from ccpph.tede_missive_addressee where UserId={userId} and IsDelete=0 and IsRead=0)";
            var missiveCount = await connection.ExecuteScalarAsync<int>(sql);
            sql = $"SELECT count(1) FROM ccpph.tede_admin_verify where AdminId={userId} and DisposeType=0";
            noticeCount += await connection.ExecuteScalarAsync<int>(sql);
            result.noticeCount = noticeCount;
            result.missiveCount = missiveCount;
            return result;
        }
        /// <summary>
        /// 获取工作用户选择审核和抄送人
        /// </summary>
        /// <param name="userIds"></param>
        /// <param name="flowId"></param>
        /// <returns></returns>
        public async Task<List<Entity.User>> GeFlowUsers(List<int> userIds, int flowId, string type)
        {
            string strSql = "";
            if (userIds.Count > 0)
            {
                strSql = "and UserId not in(";
                foreach (var item in userIds)
                {
                    strSql += $"{item},";
                }
                strSql = strSql.Remove(strSql.Length - 1, 1);
                strSql += ")";
            }
            string sql = $" select * from ccpph.tede_user where Id in( SELECT UserId FROM ccpph.tede_flow_todo where FlowId={flowId} and Type='{type}' {strSql})";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<User>(sql);
            foreach (var item in result)
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            return result.ToList();
        }

        public async Task<List<JobUserResult>> ElasticSearchUser()
        {
            var sql = "select * from ccpph.tede_user where IsUpload=0 limit 0,100";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var items = await connection.QueryAsync<JobUserResult>(sql);
            return items.ToList();
        }

        public async Task<bool> UpdateAsync(SqlKata.Query query)
        {
            return await _repository.UpdateAsync(query) > 0;
        }
        //public async Task<UserDetail> GetUserDetailJoinAsync(int userId)
        //{

        //    var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypestr, DatabaseType.MySql);
        //    var database = new Database(databaseType, _connectionString);
        //    var connection = database.GetConnection();

        //    var result = connection.Query<UserDetail, Department, UserDetail>(
        //        "SELECT a.*,b.Name FROM ccpph.tede_user a inner join ccpph.tede_department b on a.DepartmentId=b.Id where a.id=@Id;",
        //        (userDetail,department) =>
        //        {
        //            userDetail.DepartmentName = department.Name;
        //            return userDetail;
        //        }, new {Id = userId}, splitOn: "Name");

        //    return result.ToList()[0];
        //}
    }
}