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;
        private readonly Repository<Entity.Middle.Middle> middleRepository;
        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);
            middleRepository = new Repository<Entity.Middle.Middle>(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;
            groupDto.Sort = id;
            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 (request.GroupCategroyId > 0)
                group.GroupCategroyId = request.GroupCategroyId;
            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;
            if (request.Sort > 0)
                group.Sort = request.Sort;
            return await _repository.UpdateAsync(group);
        }
        /// <summary>
        /// 修改小组
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(Entity.Group group)
        {
            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<IEnumerable<GroupUserFindResult>> FindGroupByGroupFolderId(GroupSearchRequest request)
        {
            var result = new List<GroupUserFindResult>();
            result.AddRange(await GetGroupUserFindResult(true, request));
            result.AddRange(await GetGroupUserFindResult(false, request));
            foreach (var item in result)
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            return result;
        }
        public async Task<IEnumerable<GroupUserFindResult>> GetGroupUserFindResult(bool IsTop, GroupSearchRequest request)
        {
            var groupConst = AllTypeConst.Group.GetHashCode();
            var query = new SqlKata.Query("tede_Middle");
            query.LeftJoin("tede_group", "tede_group.Id", "tede_Middle.MiddleId");
            if (request.GroupFolderId > 0)
                query.Where("tede_Middle.ParentId", request.GroupFolderId);
            if (!string.IsNullOrEmpty(request.Key))
                query.Where(n => n.OrWhereLike("tede_Middle.FolderName", $"%request.Key%").OrWhereLike("tede_group.Name", $"%{request.Key}%"));
            if (string.IsNullOrEmpty(request.Key) && request.GroupFolderId == 0)
                query.Where("tede_Middle.ParentId", request.GroupFolderId);
            query.Where("tede_Middle.UserId", request.UserId);
            query.Where("tede_Middle.IsDelete", false);
            query.Where("tede_Middle.FolderType", groupConst);
            query.Where("tede_Middle.IsTop", IsTop);
            if (IsTop)
            {
                query.OrderByDesc("tede_Middle.IsTop");
                query.OrderByDesc("tede_Middle.LastModifiedDate");
            }
            else
                query.OrderByDesc("tede_group.LastModifiedDate");
            var topicSharingCountQuery = new SqlKata.Query("tede_group_user");
            topicSharingCountQuery.WhereColumns("tede_group_user.GroupId", "=", "tede_Middle.MiddleId");
            topicSharingCountQuery.AsCount();
            query.Select(topicSharingCountQuery, "TopicSharingCount");
            var topicAllCountQuery = new SqlKata.Query("tede_topic");
            topicAllCountQuery.WhereColumns("tede_topic.GroupId", "=", "tede_Middle.MiddleId");
            topicAllCountQuery.Where("tede_topic.IsDraft", false);
            topicAllCountQuery.AsCount();
            query.Select(topicAllCountQuery, "TopicAllCount");
            var groupCountQuery = new SqlKata.Query("tede_middle as a");
            groupCountQuery.WhereColumns("a.parentId", "=", "tede_Middle.Id");
            groupCountQuery.AsCount();
            query.Select(groupCountQuery, "GroupCount");
            var uReadTopicCountQuery = new SqlKata.Query("tede_topic");
            uReadTopicCountQuery.WhereColumns("tede_topic.groupId", "=", "tede_group.Id");
            var groupUserQuery = new SqlKata.Query("tede_group_user");
            groupUserQuery.WhereColumns("tede_group_user.groupId", "=", "tede_group.Id");
            groupUserQuery.Where("tede_group_user.userId", request.UserId);
            groupUserQuery.Select("ReadDate");
            uReadTopicCountQuery.Where("tede_topic.LastModifiedDate", ">", groupUserQuery);
            uReadTopicCountQuery.AsCount();
            query.Select(uReadTopicCountQuery, "UReadTopicCount");
            var categoryNameQuery = new SqlKata.Query("tede_middle as a");
            categoryNameQuery.WhereColumns("a.Id", "=", "tede_Middle.ParentId");
            categoryNameQuery.Select("a.FolderName");
            query.Select(categoryNameQuery, "CategoryName");
            var fileCountQuery = new SqlKata.Query("tede_group");
            var middleQuery = new SqlKata.Query("tede_Middle as a");
            middleQuery.WhereColumns("a.ParentId", "=", "tede_Middle.Id");
            middleQuery.Select("a.MiddleId");
            fileCountQuery.WhereIn("tede_group.Id", middleQuery);
            fileCountQuery.AsCount();
            query.Select(fileCountQuery, "FileCount");
            query.Select("tede_Middle.{Id,IsTop,FolderName,AttributeValue}",
                          "tede_group.{Id as GroupId,AvatarUrl,Name as GroupName}");
            return await middleRepository.GetAllAsync<GroupUserFindResult>(query);
        }
        /// <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.Id,a.GroupUserRoleId,a.CreatedDate,a.GroupId,b.Id as UserId,b.Name,b.AvatarUrl,b.Email,b.ImId,c.Name as DepartmentName  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} 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>(sql);
            foreach (var item in result)
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            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.IsCreator = 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);
            if (groupDetailResult.GroupUserListResult != null && groupDetailResult.GroupUserListResult.Count() > 0)
            {
                var roleIds = new List<GroupUserRoleTyeConst> { GroupUserRoleTyeConst.Admin, GroupUserRoleTyeConst.SuperAdmin };
                groupDetailResult.MainMemberListResult = groupDetailResult.GroupUserListResult.Where(n => roleIds.Contains(n.GroupUserRoleId));
                groupDetailResult.MainMemberCount = groupDetailResult.MainMemberListResult.Count();
                groupDetailResult.CommonMemberListResult = groupDetailResult.GroupUserListResult.Where(n => !roleIds.Contains(n.GroupUserRoleId));
                groupDetailResult.CommonMemberCount = groupDetailResult.CommonMemberListResult.Count();
            }
            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<SearchGroupDetailResult>> GetAllAsync(int categoryId, int userId)
        {
            var sql = $@"SELECT 
                            a.Id,
                            a.Name,
                            a.AvatarUrl,
                            a.Introduce,
                            a.IsAdmin,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_group_user
                                WHERE
                                    GroupId = a.Id) AS UserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_topic
                                WHERE
                                    GroupId = a.Id) AS TopicCount
                        FROM
                            tede_group a
                        WHERE
                            a.GroupCategroyId = {categoryId} and a.IsShow=1 order by a.Sort desc";
            if (userId > 0)
            {
                sql = $@"SELECT 
                            a.Id,
                            a.Name,
                            a.AvatarUrl,
                            a.Introduce,
                            a.IsAdmin,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_group_user
                                    WHERE
                                        GroupId = a.Id) AS UserCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_topic
                                    WHERE
                                    GroupId = a.Id) AS TopicCount,
                                (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 where a.GroupCategroyId = {categoryId} and a.IsShow=1 order by a.Sort desc";
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<SearchGroupDetailResult>(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 b.FolderType = {AllTypeConst.Group.GetHashCode()}
                                    AND b.IsDelete = 0
                                    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);
                item.TypeValue = UserlinkConst.MyGroup.GetHashCode();
                item.TypeId = UserlinkConst.System.GetHashCode();
            }
            return items;
        }
        /// <summary>
        /// 小组列表 搜索专用
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<UserGroupResult>> GetUserGroupListAsync(int userId)
        {
            var result = new List<UserGroupResult>();
            string sql = $@"SELECT 
                                b.*
                            FROM
                                tede_group_user a
                                    INNER JOIN
                                tede_group b ON a.GroupId = b.Id
                            WHERE
                                a.UserId = {userId}
                            ORDER BY b.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<Entity.Group>(sql);
            result.Add(new UserGroupResult
            {
                Id = -1,
                Name = "全网公开"
            });
            result.Add(new UserGroupResult
            {
                Id = -2,
                Name = "我的小组"
            });
            foreach (var item in items)
            {
                result.Add(new UserGroupResult
                {
                    Id = item.Id,
                    Name = item.Name
                });
            }
            return result;
        }
        /// <summary>
        /// 获取小组列表
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<GroupDetailResult>> GetGroupDetailResults(string keyWord)
        {
            string sqlStr = string.Empty;
            if (!string.IsNullOrWhiteSpace(keyWord))
                sqlStr += $" and a.Name like '%{keyWord}%'";
            string sql = $@"SELECT 
                                a.*, b.PiazzaName AS CategroyName, c.Name AS UserName
                            FROM
                                tede_group a
                                    INNER JOIN
                                tede_user c ON a.UserId = c.Id
                                    LEFT JOIN
                                tede_group_piazza b ON a.GroupCategroyId = b.Id Where 1=1 {sqlStr}
                            ORDER BY a.Sort DESC , a.CreatedDate DESC";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection
                .QueryAsync<GroupDetailResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.QRCoder = StringUtils.AddDomainMin(item.QRCoder);
            }
            return result;
        }
        /// <summary>
        /// 小组广场搜索
        /// </summary>
        /// <param name="keyWord"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<SearchGroupDetailResult>> GetSearchGroupAsync(string keyWord, int userId)
        {
            string sqlStr = string.Empty;
            if (!string.IsNullOrWhiteSpace(keyWord))
                sqlStr += $" and a.Name like '%{keyWord}%'";
            string sql = $@"SELECT 
                            a.Id,
                            a.Name,
                            a.AvatarUrl,
                            a.Introduce,
                            a.IsAdmin,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_group_user
                                WHERE
                                    GroupId = a.Id) AS UserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_topic
                                WHERE
                                    GroupId = a.Id) as TopicCount,
                                    (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
                        WHERE
                            a.IsShow = 1 {sqlStr}
                        ORDER BY a.Sort DESC";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection
                .QueryAsync<SearchGroupDetailResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.Introduce = string.IsNullOrWhiteSpace(item.Introduce) ? string.Empty : item.Introduce;
            }
            return result;
        }

    }
}