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

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using Dapper;
using GxPress.Common.AppOptions;
using GxPress.Common.Page;
using GxPress.Common.Tools;
using GxPress.Entity;
using GxPress.Repository.Interface;
using GxPress.Request.App.Topic;
using GxPress.Result.App.Topic;
using Microsoft.Extensions.Options;
using Datory;
using GxPress.Entity.Topic;
using GxPress.Result;
using SqlKata;
using GxPress.Result.Job;

namespace GxPress.Repository.Implement
{
    public class TopicRepository : ITopicRepository
    {
        private readonly Repository<Entity.Topic.Topic> _repository;
        private readonly Repository<TopicAddressee> _topicAddresseeRepository;
        private readonly Repository<User> _userRepository;
        private readonly IMapper _mapper;
        private readonly string _connectionString;
        private readonly string _databaseTypeStr;
        private readonly Repository<GroupUser> _groupUserRepository;
        private readonly Repository<Entity.Visit.Visit> _visitRepository;
        public TopicRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper)
        {
            _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.Topic.Topic>(database);
            _topicAddresseeRepository = new Repository<TopicAddressee>(database);
            _userRepository = new Repository<User>(database);
            _groupUserRepository = new Repository<GroupUser>(database);
            _visitRepository = new Repository<Entity.Visit.Visit>(database);
            _mapper = mapper;
        }

        public TopicRepository()
        {
        }

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


        /// <summary>
        /// 根据userId获取话题
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<Entity.Topic.Topic>> GetTopicByUserIdAsync(TopicPageSearchRequest request)
        {
            var query = Q.NewQuery();
            if (request.UserId > 0)
            {
                query.Where(nameof(Entity.Topic.Topic.UserId), request.UserId);
            }

            if (!string.IsNullOrEmpty(request.Keyword))
            {
                var like = $"%{request.Keyword}%";
                query.WhereLike(nameof(Entity.Topic.Topic.Title), like);
            }

            var pagedList = new PagedList<Entity.Topic.Topic>
            {
                Total = await _repository.CountAsync(query)
            };
            var list = await _repository.GetAllAsync(query.ForPage(request.Page, request.PerPage));
            pagedList.Items = list;
            return pagedList;
        }

        /// <summary>
        /// APP列表显示用
        /// </summary>
        /// <param name="contentJsonDataList"></param>
        /// <returns></returns>
        public List<ContentJsonData> GetListAsync(List<ContentJsonData> contentJsonDataList)
        {
            var result = new List<ContentJsonData>();
            if (contentJsonDataList.Count > 0)
            {
                //查询文本
                var txtType = contentJsonDataList.FirstOrDefault(n => n.Type == 1);
                if (txtType != null)
                    result.Add(txtType);
                //拼接图片
                var imgType = contentJsonDataList.FindAll(n => n.Type == 2);
                if (imgType.Any())
                {
                    foreach (var item in imgType)
                    {
                        //判断图片数量
                        if (result.Count(n => n.Type == 2) > 8)
                            break;
                        result.Add(item);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 根据GroupId获取通知
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<TopicListPageResult>> FindTopicByGroupIdAsync(TopicDetailListRequest request)
        {
            //用户收件人
            var resultData = new PagedList<TopicListPageResult>();
            var showKey = $@"SELECT a.*,(SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                      TypeValue =1
                        AND SourceId = a.Id
                        AND AnalyzeType = 1) AS PraiseCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 1
                        AND SourceId = a.Id
                        AND AnalyzeType = 1
                LIMIT 0 , 1) AS IsPraise,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_comment
                WHERE
                     ArticleId =  a.Id and pid=0
                        AND TypeValue = 1) AS CommentCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 1
                        AND SourceId = a.Id
                        AND AnalyzeType = 4) AS RetransmissionCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 1
                        AND SourceId = a.Id
                        AND AnalyzeType = 4
                LIMIT 0 , 1) AS IsRetransmission,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 1
                        AND SourceId = a.Id
                        AND AnalyzeType = 4
                LIMIT 0 , 1) AS IsCollect,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 1
                        AND SourceId = a.Id
                        AND AnalyzeType = 4) AS CollectCount,b.Name,b.AvatarUrl,c.Name FROM";
            var countSql = $"SELECT count(1) FROM tede_topic a inner join tede_user b on a.UserId=b.Id inner join tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
            var sql =
                $"{showKey} tede_topic a inner join tede_user b on a.UserId=b.Id inner join tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
            var strValue = "";
            if (!string.IsNullOrEmpty(request.Key))
                strValue += $" and (a.Title like '%{request.Key}%' or a.Content like '%{request.Key}%' or b.Name like '%{request.Key}%')";
            sql += $"{strValue} order by a.CreatedDate  DESC LIMIT  @page , @pageSize";
            countSql += $"{strValue}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
                connection
                    .Query<TopicListPageResult, User, Department, TopicListPageResult>(sql,
                        (topicListPageResult, user, department) =>
                        {
                            topicListPageResult.DepartmentName = department.Name;
                            topicListPageResult.UserName = user.Name;
                            topicListPageResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
                            return topicListPageResult;
                        }, new { page = (request.Page - 1) * request.PerPage, pageSize = request.PerPage }, splitOn: "Name");
            resultData.Total = await connection.ExecuteScalarAsync<int>(countSql);
            var topicListPageResults = result as TopicListPageResult[] ?? result.ToArray();

            resultData.Items = topicListPageResults;
            return resultData;
        }



        /// <summary>
        /// 根据小组ID获取话题数量
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<int> GetGroupTopicCountAsync(int groupId)
        {
            return await _repository.CountAsync(Q.Where(nameof(Entity.Topic.Topic.GroupId), groupId));
        }

        /// <summary>
        /// 根据ID获取话题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Entity.Topic.Topic> GetAsync(int id)
        {
            return await _repository.GetAsync(id);
        }

        public async Task<int> CountAsync(Query query)
        {
            return await _repository.CountAsync(query);
        }

        public async Task<IEnumerable<Entity.Topic.Topic>> GetAllAsync(Query query)
        {
            return await _repository.GetAllAsync(query);
        }

        public async Task<int> InsertAsync(Entity.Topic.Topic topic)
        {
            return await _repository.InsertAsync(topic);
        }

        public async Task<bool> DeleteAsync(int id)
        {
            return await _repository.DeleteAsync(id);
        }

        public async Task<bool> UpdateAsync(Entity.Topic.Topic topic)
        {
            return await _repository.UpdateAsync(topic);
        }
        /// <summary>
        /// 执行话题
        /// </summary>
        /// <returns></returns>
        public async Task<List<JobTopicResult>> ExecuteTopic()
        {
            string sql = "select a.*,b.UserId,b.Id,c.Name,d.AvatarUrl from tede_topic a inner join  tede_topic_addressee b on a.Id=b.TopicId inner join tede_user c on c.Id=b.UserId inner join tede_user d on d.Id=a.UserId where b.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 result = await connection.QueryAsync<JobTopicResult, TopicAddressee, User, User, JobTopicResult>(sql, (jobTopicResult, topicAddressee, user, userDto) =>
              {
                  jobTopicResult.UserId = topicAddressee.UserId;
                  jobTopicResult.Name = user.Name;
                  jobTopicResult.AvatarUrl = userDto.AvatarUrl;
                  jobTopicResult.TopicAddresseeId = topicAddressee.Id;
                  return jobTopicResult;
              }, splitOn: "UserId,Name,AvatarUrl");
            connection.Dispose();
            return result.ToList();
        }
        /// <summary>
        /// 新版 获取换题列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<TopicListPageResult>> GetTopicPage(TopicPageSearchRequest request)
        {
            var result = new PagedList<TopicListPageResult>();
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            result.Items = await connection.QueryAsync<TopicListPageResult, User, TopicListPageResult>(AssembleSql(request), (topicListPageResult, user) =>
              {
                  topicListPageResult.UserName = user != null ? user.Name : "";
                  topicListPageResult.AvatarUrl = user != null ? StringUtils.AddDomainMin(user.AvatarUrl) : "";
                  topicListPageResult.FolderResult.Id = topicListPageResult.FolderId;
                  topicListPageResult.FolderResult.FolderName = topicListPageResult.FolderName;
                  return topicListPageResult;
              }, splitOn: "Id,Name");
            result.Total = await connection.ExecuteScalarAsync<int>(AssembleSqlCount(request));
            connection.Dispose();
            return result;
        }

        public async Task<PagedList<TopicListPageResult>> GetTopicByGroupAsync(TopicPageSearchRequest request)
        {
            var sql = $@"SELECT 
                                a.*,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        TypeValue = 3 AND SourceId = a.Id
                                            AND AnalyzeType = 1) AS PraiseCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1
                                    LIMIT 0 , 1) AS IsPraise,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_comment
                                    WHERE
                                        ArticleId = a.Id AND TypeValue = 3
                                            AND Pid = 0) AS CommentCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4) AS RetransmissionCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4
                                    LIMIT 0 , 1) AS IsRetransmission,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 3
                                    LIMIT 0 , 1) AS IsCollect,
                                b.Id as GroupId,
                                b.Name,
                                c.Name,
                                c.AvatarUrl
                            FROM
                                tede_topic a
                                    INNER JOIN
                                tede_group b ON a.GroupId = b.id
                                    INNER JOIN
                                tede_user c ON a.UserId = c.Id
                            WHERE 1=1 ";
            if (request.TopicGroupIds.Count > 0)
            {
                var groupStr = "(";
                foreach (var item in request.TopicGroupIds)
                {
                    groupStr += $"{item},";
                }
                groupStr = groupStr.Remove(groupStr.Length - 1, 1);
                groupStr += ")";
                sql += $@" and a.GroupId IN {groupStr}";
            }
            else
                sql += $@" and a.GroupId > 0";
            if (request.IsAdmin)
            {
                sql += $@" AND a.UserId={request.UserId}";
            }
            else
            {
                sql += $@" AND b.Id IN (SELECT 
                                        GroupId
                                    FROM
                                        tede_group_user
                                    WHERE
                                        UserId = {request.UserId})";
            }
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@"  AND (c.Name LIKE '%{request.Keyword}%'
                                    OR a.Title LIKE '%{request.Keyword}%'
                                    OR a.HtmlContent LIKE '%{request.Keyword}%')";
            }
            if (!string.IsNullOrEmpty(request.Sort))
                sql += $@" ORDER BY a.ReadCount DESC";
            else
                sql += $@" ORDER BY a.CreatedDate DESC";
            sql += $@" LIMIT {(request.Page - 1) * request.PerPage} , {request.PerPage}";
            var result = new PagedList<TopicListPageResult>();
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            result.Items = await connection.QueryAsync<TopicListPageResult, Entity.Group, User, TopicListPageResult>(sql, (topicListPageResult, group, user) =>
               {
                   topicListPageResult.AvatarUrl = user != null ? StringUtils.AddDomainMin(user.AvatarUrl) : "";
                   topicListPageResult.GroupName = group != null ? group.Name : "";
                   topicListPageResult.UserName = user != null ? user.Name : "";
                   return topicListPageResult;
               }, splitOn: "Id,GroupId,Name");
            sql = $@"SELECT 
                                count(1)
                            FROM
                                tede_topic a
                                    INNER JOIN
                                tede_group b ON a.GroupId = b.id
                                    INNER JOIN
                                tede_user c ON a.UserId = c.Id
                                WHERE 1=1 ";
            if (request.TopicGroupIds.Count > 0)
            {
                var groupStr = "(";
                foreach (var item in request.TopicGroupIds)
                {
                    groupStr += $"{item},";
                }
                groupStr = groupStr.Remove(groupStr.Length - 1, 1);
                groupStr += ")";
                sql += $@" and a.GroupId IN {groupStr}";
            }
            else
                sql += $@" and a.GroupId > 0";
            if (request.IsAdmin)
            {
                sql += $@" AND a.UserId={request.UserId}";
            }
            else
            {
                sql += $@" AND b.Id IN (SELECT 
                                        GroupId
                                    FROM
                                        tede_group_user
                                    WHERE
                                        UserId = {request.UserId})";
            }
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@"  AND (c.Name LIKE '%{request.Keyword}%'
                                    OR a.Title LIKE '%{request.Keyword}%'
                                    OR a.HtmlContent LIKE '%{request.Keyword}%')";
            }
            result.Total = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
        public string AssembleSql(TopicPageSearchRequest request)
        {
            var sql = $@"SELECT 
                                a.*,
                                (SELECT 
                                        FolderName
                                    FROM
                                        tede_middle
                                    WHERE
                                        id = a.FolderId) AS FolderName,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                         TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1) AS PraiseCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1
                                    LIMIT 0 , 1) AS IsPraise,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_comment
                                    WHERE
                                        ArticleId = a.Id AND TypeValue = 3
                                            AND Pid = 0) AS CommentCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4) AS RetransmissionCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4
                                    LIMIT 0 , 1) AS IsRetransmission,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = 3
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 3
                                    LIMIT 0 , 1) AS IsCollect,
                                b.Name, b.AvatarUrl
                                                    FROM
                                                        tede_note a
                                                            INNER JOIN
                                                        tede_user b ON a.UserId = b.Id
                                                            INNER JOIN
                                                        tede_middle c ON c.MiddleId = a.Id
                                                    WHERE
                                                        c.FolderType = 4 AND a.IsTopic = 1
                                                            AND (a.FolderId IN (SELECT 
                                                                MiddleId
                                                            FROM
                                                                tede_folder_user
                                                            WHERE
                                                                MiddleId = a.FolderId AND UserId = {request.UserId})
                                                            OR a.UserId IN (SELECT 
                                                                Id
                                                            FROM
                                                                tede_user
                                                            WHERE
                                                                1 = (SELECT 
                                                                        RoleId
                                                                    FROM
                                                                        tede_middle
                                                                    WHERE
                                                                        id = a.FolderId)
                                                                    AND id = {request.UserId}))";

            if (request.TopicGroupIds.Count > 0)
            {
                var topicGroupId = "";
                foreach (var item in request.TopicGroupIds)
                {
                    topicGroupId += $"{item},";
                }
                topicGroupId = topicGroupId.Remove(topicGroupId.Length - 1, 1);
                sql += $@" AND a.UserId IN (SELECT 
                                        UserId
                                    FROM
                                        tede_topic_group_user
                                    WHERE
                                        TopicGroupId IN ({topicGroupId}))";
            }
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@"  AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.Title LIKE '%{request.Keyword}%'
                                    OR a.HtmlContent LIKE '%{request.Keyword}%')";
            }
            sql += $@"  ORDER BY a.CreatedDate DESC
                        LIMIT {(request.Page - 1) * request.PerPage} , {request.PerPage}";
            return sql;
        }
        public string AssembleSqlCount(TopicPageSearchRequest request)
        {
            var sql = $@"SELECT   count(1) FROM
                                                        tede_note a
                                                            INNER JOIN
                                                        tede_user b ON a.UserId = b.Id
                                                            INNER JOIN
                                                        tede_middle c ON c.MiddleId = a.Id
                                                    WHERE
                                                        c.FolderType = 4 AND a.IsTopic = 1
                                                            AND (a.FolderId IN (SELECT 
                                                                MiddleId
                                                            FROM
                                                                tede_folder_user
                                                            WHERE
                                                                MiddleId = a.FolderId AND UserId = {request.UserId})
                                                            OR a.UserId IN (SELECT 
                                                                Id
                                                            FROM
                                                                tede_user
                                                            WHERE
                                                                1 = (SELECT 
                                                                        RoleId
                                                                    FROM
                                                                        tede_middle
                                                                    WHERE
                                                                        id = a.FolderId)
                                                                    AND id = {request.UserId}))";

            if (request.TopicGroupIds.Count > 0)
            {
                var topicGroupId = "";
                foreach (var item in request.TopicGroupIds)
                {
                    topicGroupId += $"{item},";
                }
                topicGroupId = topicGroupId.Remove(topicGroupId.Length - 1, 1);
                sql += $@" AND a.UserId IN (SELECT 
                                        UserId
                                    FROM
                                        tede_topic_group_user
                                    WHERE
                                        TopicGroupId IN ({topicGroupId}))";
            }
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@"  AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.Title LIKE '%{request.Keyword}%'
                                    OR a.HtmlContent LIKE '%{request.Keyword}%')";
            }
            return sql;
        }
    }
}