tede/gx_api/GxPress/Repository/GxPress.Repository.Implement/GroupRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using AutoMapper;
using Dapper;
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.App.Group;
using GxPress.Result.App.Group;
using GxPress.Result.App.GroupUser;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Datory;
using GxPress.Request.App.Topic;
using SqlKata;
using GxPress.Result.Job;
using System.Linq;

namespace GxPress.Repository.Implement
{
    public class GroupRepository : IGroupRepository
    {
        private readonly Repository<Entity.Group> _repository;
        private readonly IMapper _mapper;
        private readonly IWebHostEnvironment _environment;
        private readonly string _connectionString;
        private readonly string _databaseTypeStr;
        private readonly Repository<GroupUser> _groupUserRepository;

        public GroupRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper,
            IWebHostEnvironment environment)
        {
            _databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType;
            _connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
            var databaseType =
                StringUtils.ToEnum<DatabaseType>(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql);
            var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString);
            _repository = new Repository<Entity.Group>(database);
            _groupUserRepository = new Repository<GroupUser>(database);
            _environment = environment;
            _mapper = mapper;
        }

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

        public async Task<int> InsertAsync(GroupInRequest request)
        {
            if (string.IsNullOrEmpty(request.Name))
                throw new BusinessException("小组名称不能为空");
            var group = new Entity.Group
            {
                Name = request.Name,
                AvatarUrl = request.AvatarUrl,
                Introduce = request.Introduce,
                IsAdmin = false,
                IsUDisturb = false,
                QRCoder = request.QRCoder,
                IsWords = false,
                UserId = request.UserId,
                GroupCategroyId = request.GroupCategroyId,
                LabelName = request.LabelName,
                IsShow = request.IsShow
            };
            if (!string.IsNullOrEmpty(group.AvatarUrl))
                group.AvatarUrl = StringUtils.RemoveDomain(group.AvatarUrl);
            var id = await _repository.InsertAsync(group);
            //生成二维码
            var groupDto = await _repository.GetAsync(id);
            var qrCodeData = new { key = QRCodeTypeConst.GroupInfo, value = groupDto.Guid };
            var resultPath =
                FileHelper.SaveQRCodeFile(StringUtils.GetWebRootPath(_environment.WebRootPath),
                    JsonConvert.SerializeObject(qrCodeData));
            groupDto.QRCoder = resultPath.RelativePath;
            await _repository.UpdateAsync(groupDto);
            return id;
        }

        public async Task<IEnumerable<GroupUser>> GetAllAsync(SqlKata.Query query)
        {
            return await _groupUserRepository.GetAllAsync(query);
        }

        /// <summary>
        /// 修改小组
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(GroupUpRequest request)
        {
            var group = await _repository.GetAsync(request.Id);
            if (!string.IsNullOrEmpty(request.Name))
                group.Name = request.Name;
            if (!string.IsNullOrEmpty(request.AvatarUrl))
            {
                group.HistoryAvatarUrl = group.AvatarUrl;
                group.AvatarUrl = StringUtils.RemoveDomain(request.AvatarUrl);
            }
            if (!string.IsNullOrEmpty(request.Introduce))
                group.Introduce = request.Introduce;
            if (!string.IsNullOrEmpty(request.LabelName))
                group.LabelName = request.LabelName;
            if (request.IsShow > 0)
                group.IsShow = request.IsShow == 1;
            if (request.IsAdmin > 0)
                group.IsAdmin = request.IsAdmin == 1;
            if (request.IsWords > 0)
                group.IsWords = request.IsWords == 1;
            return await _repository.UpdateAsync(group);
        }

        /// <summary>
        /// 删除小组
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(int id)
        {
            return await _repository.DeleteAsync(id);
        }

        public async Task<bool> DeleteAsync(Query query)
        {
            return await _repository.DeleteAsync(query) > 0;
        }

        /// <summary>
        /// 查询小组
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<GroupUserFindResult>> FindGroupByGroupFolderId(GroupSearchRequest request)
        {
            var pagedList = new PagedList<GroupUserFindResult>();
            var groupConst = AllTypeConst.Group.GetHashCode();
            //根据文件夹ID查询成员
            var sqlValue = "";
            // if (!string.IsNullOrEmpty(request.Key))
            //     sqlValue += $"and b.Name like '%{request.Key}%'";
            //共享数量
            var topicSharingCount = "(select count(1) from tede_group_user where GroupId=a.MiddleId) as TopicSharingCount";
            //总话题数量
            var topicAllCount = "(select count(1) from tede_topic where GroupId=a.MiddleId) as TopicAllCount";
            //未读话题数量
            var uReadTopicCount = $"(select count(1) from tede_topic_addressee where TopicId in (select Id from tede_topic where GroupId=a.MiddleId) and UserId={request.UserId} and IsRead=0) as UReadTopicCount";
            var categoryName = "(select FolderName from tede_middle where id=a.ParentId) as CategoryName";
            var sql =
                $@"SELECT a.*,{topicSharingCount},{topicAllCount},{uReadTopicCount},{categoryName},
                                                    (SELECT 
                                                            COUNT(1)
                                                        FROM
                                                            tede_group
                                                        WHERE
                                                            id IN (SELECT 
                                                                    MiddleId
                                                                FROM
                                                                    tede_middle
                                                                WHERE
                    ParentId = a.Id)) AS FileCount,b.Id, b.AvatarUrl, b.Name FROM tede_Middle a LEFT JOIN tede_group b ON a.MiddleId = b.Id WHERE 1=1 ";
            if (request.GroupFolderId == 0 && string.IsNullOrEmpty(request.Key))
                sql += $"and a.ParentId = @groupFolderId ";
            else if (request.GroupFolderId > 0 && !string.IsNullOrEmpty(request.Key))
                sql += $"and a.ParentId = @groupFolderId and (a.FolderName like '%{request.Key}%' or b.Name like '%{request.Key}%')";
            else if (request.GroupFolderId > 0 && string.IsNullOrEmpty(request.Key))
                sql += $"and a.ParentId = @groupFolderId ";
            else
                sql += $" and (a.FolderName like '%{request.Key}%' or b.Name like '%{request.Key}%')";
            sql +=
                $@" and a.UserId=@userId and a.FolderType={groupConst} and a.IsDelete=0 {sqlValue} and (b.id>0 or a.AttributeValue=2) ORDER BY a.AttributeValue DESC,a.IsTop DESC, a.LastModifiedDate DESC  LIMIT  @page , @pageSize";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
                await connection
                    .QueryAsync<GroupUserFindResult, Entity.Group, GroupUserFindResult>(sql,
                        (groupUserFindResult, group) =>
                        {
                            groupUserFindResult.GroupName = group == null ? "" : group.Name;
                            groupUserFindResult.AvatarUrl = group == null ? "" : StringUtils.AddDomainMin(group.AvatarUrl);
                            groupUserFindResult.GroupId = group?.Id ?? 0;
                            return groupUserFindResult;
                        },
                        new
                        {
                            groupFolderId = request.GroupFolderId,
                            userId = request.UserId,
                            page = (request.Page - 1) * request.PerPage,
                            pageSize = request.PerPage * request.Page
                        }, splitOn: "Id");

            pagedList.Items = result;
            var sqlCount =
                @"SELECT  count(1) FROM tede_Middle a LEFT JOIN tede_group b ON a.MiddleId = b.Id  WHERE 1=1 ";
            if (request.GroupFolderId == 0 && string.IsNullOrEmpty(request.Key))
                sqlCount += $"and a.ParentId = {request.GroupFolderId} ";
            else if (request.GroupFolderId > 0 && !string.IsNullOrEmpty(request.Key))
                sqlCount += $"and a.ParentId = {request.GroupFolderId} and (a.FolderName like '%{request.Key}%' or b.Name like '%{request.Key}%')";
            else if (request.GroupFolderId > 0 && string.IsNullOrEmpty(request.Key))
                sqlCount += $"and a.ParentId = {request.GroupFolderId} ";
            else
                sql += $" and (a.FolderName like '%{request.Key}%' or b.Name like '%{request.Key}%')";
            sqlCount +=
                $" and a.UserId={request.UserId} and a.FolderType={groupConst} and a.IsDelete=0 {sqlValue} and (b.id>0 or a.AttributeValue=2)";
            pagedList.Total = await FindGroupByGroupFolderIdCountAsync(sqlCount);
            return pagedList;
        }

        /// <summary>
        /// 条数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> FindGroupByGroupFolderIdCountAsync(string sql)
        {
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }

        /// <summary>
        /// 获取小组详情
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<GroupDetailResult> GroupDetailAsync(GroupDetailRequest request)
        {
            var groupDetail = await _repository.GetAsync(request.GroupId);
            if (groupDetail == null)
                throw new BusinessException("小组不存在");
            if (!string.IsNullOrEmpty(groupDetail.AvatarUrl))
                groupDetail.AvatarUrl = StringUtils.AddDomain(groupDetail.AvatarUrl);
            if (!string.IsNullOrEmpty(groupDetail.AvatarUrl))
                groupDetail.AvatarUrl = StringUtils.AddDomain(groupDetail.AvatarUrl);
            if (!string.IsNullOrEmpty(groupDetail.HistoryAvatarUrl))
                groupDetail.HistoryAvatarUrl = StringUtils.AddDomain(groupDetail.HistoryAvatarUrl);
            var sql =
                "SELECT a.*,b.Name,b.AvatarUrl,c.Name  FROM tede_group_user a inner join tede_user b on a.UserId=b.Id inner join tede_department c on b.DepartmentId=c.Id where a.GroupId=@groupId order by a.GroupUserRoleId";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
                await connection
                    .QueryAsync<GroupUserListResult, User, Department, GroupUserListResult>(sql,
                        (groupUserListResult, user, department) =>
                        {
                            groupUserListResult.DepartmentName = department.Name;
                            groupUserListResult.Name = user.Name;
                            groupUserListResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
                            return groupUserListResult;
                        }, new { groupId = request.GroupId }, splitOn: "Name");

            var groupDetailResult = _mapper.Map<GroupDetailResult>(groupDetail);
            groupDetailResult.GroupUserListResult = result;
            var groupUser = await _groupUserRepository.GetAsync(Q.Where(nameof(GroupUser.UserId), request.UserId)
                .Where(nameof(GroupUser.GroupId), request.GroupId));
            //获取当前用户的权限
            groupDetailResult.GroupUserRoleId = groupUser != null ? groupUser.GroupUserRoleId : 0;
            groupDetailResult.IsUDisturb = groupUser != null ? groupUser.IsUDisturb : false;
            groupDetailResult.IsAdmin = groupDetail != null && groupUser != null ? groupDetail.UserId == groupUser.UserId : false;
            groupDetailResult.IsUser = groupUser != null ? groupUser.UserId == request.UserId : false;
            groupDetailResult.CreatedDate = groupDetail.CreatedDate;
            groupDetailResult.QRCoder = StringUtils.AddDomain(groupDetailResult.QRCoder);
            return groupDetailResult;
        }
        /// <summary>
        /// 搜索小组成员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<GroupUserListResult>> SearchGroupUserAsync(TopicDetailListRequest request)
        {
            string sql =
                $@"SELECT a.*, b.Name, b.AvatarUrl, c.Name FROM tede_group_user a  INNER JOIN tede_user b ON a.UserId = b.Id INNER JOIN
            tede_department c ON b.DepartmentId = c.Id
            WHERE  a.GroupId = {request.GroupId} and b.Name like '%{request.Key}%'";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
                await connection
                    .QueryAsync<GroupUserListResult, User, Department, GroupUserListResult>(sql,
                        (groupUserListResult, user, department) =>
                        {
                            groupUserListResult.DepartmentName = department.Name;
                            groupUserListResult.Name = user.Name;
                            groupUserListResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
                            return groupUserListResult;
                        }, splitOn: "Name");
            return result;
        }

        /// <summary>
        /// 获取小组详情
        /// </summary>
        /// <param name="guId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<GroupDetailResult> GroupDetailByGuIdAsync(string guId, int userId)
        {
            var groupDetail = await _repository.GetAsync(Q.Where(nameof(Entity.Group.Guid), guId));
            if (groupDetail == null)
                throw new BusinessException("小组不存在");
            if (!string.IsNullOrEmpty(groupDetail.AvatarUrl))
                groupDetail.AvatarUrl = StringUtils.AddDomainMin(groupDetail.AvatarUrl);
            if (!string.IsNullOrEmpty(groupDetail.QRCoder))
                groupDetail.QRCoder = StringUtils.AddDomain(groupDetail.QRCoder);
            var sql =
                "SELECT a.*,b.Name,b.AvatarUrl,c.Name  FROM tede_group_user a inner join tede_user b on a.UserId=b.Id inner join tede_department c on b.DepartmentId=c.Id where a.GroupId=@groupId";

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

            var result =
               await connection
                    .QueryAsync<GroupUserListResult, User, Department, GroupUserListResult>(sql,
                        (groupUserListResult, user, department) =>
                        {
                            groupUserListResult.DepartmentName = department.Name;
                            groupUserListResult.Name = user.Name;
                            groupUserListResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
                            return groupUserListResult;
                        }, new { groupId = groupDetail.Id }, splitOn: "Name");

            var groupDetailResult = _mapper.Map<GroupDetailResult>(groupDetail);
            groupDetailResult.GroupUserListResult = result;
            var groupUser = await _groupUserRepository.GetAsync(Q.Where(nameof(GroupUser.UserId), userId)
                 .Where(nameof(GroupUser.Guid), guId));
            groupDetailResult.GroupUserRoleId = groupUser?.GroupUserRoleId ?? GroupUserRoleTyeConst.General;
            //获取当前用户的权限
            return groupDetailResult;
        }
        /// <summary>
        /// 获取用户未读取话题数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<int> GetUReadCountAsync(int userId, int groupId)
        {
            string sql =
                @"select count(1) from tede_topic a inner join tede_topic_addressee b on a.Id=b.TopicId where b.UserId=@userId and b.IsRead=0 and a.GroupId=@groupId";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();

            var result = await connection.ExecuteScalarAsync<int>(sql, new { userId, groupId });
            return result;
        }
        /// <summary>
        /// 获取小组的共享话题数量
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<int> SharingCountAsync(int groupId)
        {
            var sql =
                $"select count(1) from tede_group_user where GroupId={groupId}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteScalarAsync<int>(sql, new { GroupId = groupId });
            return result;
        }

        public async Task<Entity.Group> GetAsync(int id)
        {
            return await _repository.GetAsync(id);
        }

        /// <summary>
        /// 是开启管理员同意加入
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<bool> SetGroupIsAdminAsync(int groupId)
        {
            var group = await GetAsync(groupId);
            return await _repository.UpdateAsync(Q.Set(nameof(Entity.Group.IsAdmin), !group.IsAdmin)
                       .Where(nameof(Entity.Group.Id), groupId)) > 0;
        }
        /// <summary>
        /// 是否禁言
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<bool> SetGroupIsWordsAsync(int groupId)
        {
            var group = await GetAsync(groupId);
            return await _repository.UpdateAsync(Q.Set(nameof(Entity.Group.IsWords), !group.IsWords)
                       .Where(nameof(Entity.Group.Id), groupId)) > 0;
        }

        public async Task<List<JobGroupResult>> ElasticSearchGroup()
        {
            var sql = "select a.Id,a.GuId,a.UserId,b.Id,b.Name,b.AvatarUrl,b.CreatedDate,c.Name from tede_middle a inner join tede_group b on a.MiddleId=b.Id inner join tede_user c on c.Id=b.UserId where a.FolderType=7 and a.IsUpload=0 order by a.CreatedDate desc 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<JobGroupResult, Entity.Group, User, JobGroupResult>(sql,
                    (jobGroupResult, group, user) =>
                    {
                        jobGroupResult.MiddleId = jobGroupResult.Id;
                        jobGroupResult.Id = group.Id;
                        jobGroupResult.Name = group != null ? group.Name : "";
                        jobGroupResult.AvatarUrl = group != null ? group.AvatarUrl : "";
                        return jobGroupResult;
                    },
                    splitOn: "Id,Id,Name");
            return items.ToList();
        }
        /// <summary>
        /// 根据广场类别查询小组
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<Entity.Group>> GetAllAsync(int categoryId, int userId)
        {
            var sql = $@"SELECT 
                            *,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_group_user
                                WHERE
                                    GroupId = a.Id) AS UserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_topic
                                WHERE
                                    GroupId = a.Id) AS GroupArticleCount
                        FROM
                            tede_group a
                        WHERE
                            a.GroupCategroyId = {categoryId} and a.IsShow=1";
            if (userId > 0)
            {
                sql = $@"SELECT 
                                *,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_group_user
                                    WHERE
                                        GroupId = a.Id AND UserId = {userId}
                                    LIMIT 1) AS IsUser,
                                (SELECT 
                                        count(1)
                                    FROM
                                        tede_admin_verify
                                    WHERE
                                        SourceType = 1 AND VerifyType = 3
                                            AND UserId = {userId}
                                            AND DisposeType = 0
                                            AND SourceId = a.Id limit 1) as IsApply
                            FROM
                                tede_group a";
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<Entity.Group>(sql);
            //return await _repository.GetAllAsync(Q.Where(nameof(Entity.Group.GroupCategroyId), categoryId));
            return result;
        }
        /// <summary>
        /// 用户小组列表
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<UserGroupList>> GetAllByUserIdAsync(int userId)
        {
            var groupConstValue = AllTypeConst.Group.GetHashCode();
            string sql = $@"SELECT 
                                b.*,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_group_user
                                    WHERE
                                        GroupId = a.MiddleId) AS UserCount
                            FROM
                                tede_middle a
                                    INNER JOIN
                                tede_group b ON a.MiddleId = b.Id
                            WHERE
                                a.FolderType ={groupConstValue} AND a.UserId ={userId}
                                    AND a.IsDelete = 0";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var items = await connection
                .QueryAsync<UserGroupList>(sql);
            foreach (var item in items)
                item.AvatarUrl = StringUtils.AddDomain(item.AvatarUrl);
            return items;
        }
        /// <summary>
        /// 或者最近使用的小组
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<LatelyGroupDetailResult>> GetLatelyGroupDetailResultAsync(int userId)
        {
            string sql = $@"
                        SELECT 
                            a.Id,
                            a.Name,
                            a.AvatarUrl,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_group_user
                                WHERE
                                    GroupId = a.Id) AS UserCount
                        FROM
                            tede_group a
                                INNER JOIN
                            tede_middle b ON a.Id = b.MiddleId
                        WHERE
                            b.UserId = userId
                                AND a.Id IN (SELECT 
                                    GroupId
                                FROM
                                    tede_topic
                                WHERE
                                    GroupId > 0 AND UserId = {userId}
                                ORDER BY CreatedDate DESC)";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var items = await connection
                .QueryAsync<LatelyGroupDetailResult>(sql);
            foreach (var item in items)
                item.AvatarUrl = StringUtils.AddDomain(item.AvatarUrl);
            return items;
        }
    }
}