tede/gx_api/GxPress/Repository/GxPress.Repository.Implement/Note/NoteRepository.cs

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

namespace GxPress.Repository.Implement.Note
{
    public class NoteRepository : INoteRepository
    {
        private readonly Repository<Entity.Note.Note> _repository;
        private readonly IMapper _mapper;
        private readonly string _connectionString;
        private readonly string _databaseTypestr;
        public NoteRepository(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.Note.Note>(database);
            _mapper = mapper;
        }

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

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

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

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

        public async Task<bool> UpdateAsync(Entity.Note.Note note)
        {
            return await _repository.UpdateAsync(note);
        }
        public async Task<bool> UpdateAsync(SqlKata.Query query)
        {
            return await _repository.UpdateAsync(query) > 0;
        }
        /// <summary>
        /// 获取笔记分页
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<NotePageListRequest>> NotePageListAsync(NoteSearchPageListRequest request)
        {
            var pagedList = new PagedList<NotePageListRequest>();
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypestr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
                await connection
                    .QueryAsync<NotePageListRequest, Entity.Note.Note, User, Department, NotePageListRequest>(GetCollectionSql(request),
                        (notePageListRequest, note, user, department) =>
                        {
                            notePageListRequest.Title = note == null ? "" : note.Title;
                            notePageListRequest.Content = note == null ? "" : note.Content;
                            notePageListRequest.MediaId = note == null ? 0 : note.MediaId;
                            notePageListRequest.CatalogId = note == null ? "" : note.CatalogId;
                             notePageListRequest.ChapterId = note == null ? "" : note.ChapterId;
                            notePageListRequest.Name = user == null ? "" : user.Name;
                            notePageListRequest.AvatarUrl = user == null ? "" : StringUtils.AddDomainMin(user.AvatarUrl);
                            notePageListRequest.DepartmentName = department == null ? "" : department.Name;
                            notePageListRequest.ReadCount = note == null ? 0 : note.ReadCount;
                            notePageListRequest.NickName = user == null ? "" : user.Nick;
                            return notePageListRequest;
                        }, splitOn: "Title,Name,Name");

            pagedList.Items = result;
            pagedList.Total = await NotePageListCountAsync(GetCollectionSqlCount(request));
            return pagedList;
        }

        /// <summary>
        /// 集合
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public string GetCollectionSql(NoteSearchPageListRequest request)
        {
            string sql = "";
            if (request.VisitUserId > 0)
            {
                sql =
                    $@"SELECT a.*,(SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType=4 and DataSourceId=a.MiddleSonId) as EnjoyUser,(SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 1) AS PraiseCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 1
                LIMIT 0 , 1) AS IsPraise,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_comment
                WHERE
                     ArticleId =  a.MiddleId and pid=0
                        AND TypeValue = 3) AS CommentCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 4) AS RetransmissionCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 4
                LIMIT 0 , 1) AS IsRetransmission,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 4
                LIMIT 0 , 1) AS IsCollect,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_analyze
                WHERE
                    UserId = {request.UserId} AND TypeValue = 3
                        AND SourceId = a.MiddleId
                        AND AnalyzeType = 4) AS CollectCount,
            (SELECT 
                    COUNT(1)
                FROM
                    tede_middle
        WHERE
            ParentId = a.Id and IsDelete=0) FileCount, b.Title, b.Content,b.ReadCount,b.MediaId,b.CatalogId,
    b.ChapterId,c.Name,c.AvatarUrl,c.Nick,d.Name FROM tede_middle a LEFT JOIN tede_note b ON a.MiddleId = b.Id left join tede_user c on c.Id=b.UserId left join tede_department d on d.Id=c.DepartmentId
                        WHERE a.UserId = {request.VisitUserId} AND a.FolderType = 4 AND a.IsDelete = 0";
                if (request.FolderId == 0)
                    sql += @" AND(a.RoleId = 1 OR a.RoleId = 3) AND a.ParentId = 0";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@"  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                sql +=
                    $@" OR a.id IN(SELECT MiddleId FROM tede_folder_user WHERE UserId = {request.UserId}) AND a.FolderType = 4 AND a.UserId = {request.VisitUserId}";
                if (request.FolderId == 0 && !string.IsNullOrEmpty(request.SearchKey))
                    sql += $@"  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@" and a.ParentId={request.FolderId}  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                sql +=
                    $@" ORDER BY a.IsTop DESC,a.AttributeValue desc, a.LastModifiedDate DESC";
                if (request.Page > 0)
                    sql += $@" limit {(request.Page - 1) * request.PerPage},{request.PerPage}";
            }
            else
            {
                sql =
                    $@"SELECT  a.*,(SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType=4 and DataSourceId=a.MiddleSonId) as EnjoyUser,(SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 1) AS PraiseCount,
    (SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 1
        LIMIT 0 , 1) AS IsPraise,
    (SELECT 
            COUNT(1)
        FROM
            tede_comment
        WHERE
            UserId = {request.UserId} AND ArticleId =  a.MiddleId
                AND TypeValue = 3) AS CommentCount,
    (SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 4) AS RetransmissionCount,
    (SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 4
        LIMIT 0 , 1) AS IsRetransmission,
    (SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 4
        LIMIT 0 , 1) AS IsCollect,
    (SELECT 
            COUNT(1)
        FROM
            tede_analyze
        WHERE
            UserId = {request.UserId} AND TypeValue = 3
                AND SourceId = a.MiddleId
                AND AnalyzeType = 4) AS IsCollect,
    (SELECT 
            COUNT(1)
        FROM
            tede_middle
        WHERE
            ParentId = a.Id and IsDelete=0) FileCount, b.Title, b.Content,b.ReadCount,b.MediaId,b.CatalogId,
    b.ChapterId,c.Name,c.AvatarUrl,d.Name FROM tede_middle a  LEFT JOIN tede_note b ON a.MiddleId = b.Id left join tede_user c on c.Id=b.UserId left join tede_department d on d.Id=c.DepartmentId WHERE a.UserId = {request.UserId} AND a.FolderType = 4 AND a.IsDelete = 0";
                if (request.FolderId == 0 && !string.IsNullOrEmpty(request.SearchKey))
                    sql += $@"  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@" and a.ParentId={request.FolderId}  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%')";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                sql +=
                    $@" ORDER BY a.IsTop DESC,a.Sort DESC ,a.AttributeValue desc , a.LastModifiedDate DESC";
                if (request.Page > 0)
                    sql += $@" limit {(request.Page - 1) * request.PerPage},{request.PerPage}";
            }
            return sql;
        }

        /// <summary>
        /// 条数
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public string GetCollectionSqlCount(NoteSearchPageListRequest request)
        {
            string sql = "";

            if (request.VisitUserId > 0)
            {
                sql =
                    $@"SELECT  count(1) FROM tede_middle a LEFT JOIN tede_note b ON a.MiddleId = b.Id WHERE a.UserId = {request.VisitUserId} AND a.FolderType = 4 AND a.IsDelete = 0";
                if (request.FolderId == 0)
                    sql += @" AND(a.RoleId = 1 OR a.RoleId = 3) AND a.ParentId = 0";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@" AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                sql +=
                    $@" OR a.id IN(SELECT MiddleId FROM tede_folder_user WHERE UserId = {request.UserId}) AND a.FolderType = 4 AND a.UserId = {request.VisitUserId}";
                if (request.FolderId == 0 && !string.IsNullOrEmpty(request.SearchKey))
                    sql += $@"  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@" and a.ParentId={request.FolderId}  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else
                    sql += $" and a.ParentId={request.FolderId}";
            }
            else
            {
                sql =
                    $@"SELECT  count(1) FROM tede_middle a LEFT JOIN tede_note b ON a.MiddleId = b.Id left join tede_user c on c.Id=b.UserId left join tede_department d on d.Id=c.DepartmentId WHERE a.UserId = {request.UserId} AND a.FolderType = 4 AND a.IsDelete = 0";
                if (request.FolderId == 0 && !string.IsNullOrEmpty(request.SearchKey))
                    sql += $@"  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else if (!string.IsNullOrEmpty(request.SearchKey))
                    sql += $@" and a.ParentId={request.FolderId}  AND(b.Title LIKE '%{request.SearchKey}%' OR a.FolderName LIKE '%{request.SearchKey}%' or b.Content LIKE '%{request.SearchKey}%' or b.UserId in (select Id from tede_user where name like '%{request.SearchKey}%'))";
                else
                    sql += $" and a.ParentId={request.FolderId}";
            }

            return sql;
        }

        public async Task<int> NotePageListCountAsync(string sql)
        {
            var pagedList = new PagedList<NotePageListRequest>();
            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="id"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(int id) => await _repository.DeleteAsync(id);

        /// <summary>
        ///查询个人摘录
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<NoteUserExtractResult>> GetAllByUserId(NoteUserRequest request)
        {
            var result = new PagedList<NoteUserExtractResult>();
            var sql = $@"SELECT 
                            a.Id,
                            a.CreatedDate,
                            a.Title,
                            a.Content,
                            a.UserId,
                            a.ReadCount,
                            a.HtmlContent,
                            a.MediaId,
                            a.CatalogId,
                            a.ChapterId,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_analyze
                                WHERE
                                    UserId = {request.UserId} AND 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 pid = 0
                                        AND TypeValue = 3) 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,
                            b.Name,b.AvatarUrl
                        FROM
                            tede_note a
                                INNER JOIN
                            tede_user b ON a.UserId = b.Id 
                        WHERE
                            UserId = {request.UserId} and a.MediaId='{request.MediaId}' 
                        ORDER BY CreatedDate DESC";

            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypestr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            result.Items =
                  await connection
                      .QueryAsync<NoteUserExtractResult>(sql);
            sql = $@"SELECT 
                          count(1)
                        FROM
                            tede_note a
                                INNER JOIN
                            tede_user b ON a.UserId = b.Id
                        WHERE
                            UserId = {request.UserId} and a.MediaId='{request.MediaId}' and   a.CatalogId='{request.CatalogId}' and   a.ChapterId='{request.ChapterId}'";
            result.Total =
                 await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }

        /// <summary>
        /// web页面查询话题
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<NoteTopicResult>> GetTopicNoteAsync(NoteSearchPageListRequest request)
        {
            var sql = $@"SELECT 
                                a.Id,
                                a.Title,
                                a.Content,
                                a.CreatedDate,
                                b.Name,
                                b.AvatarUrl,
                                c.Id,
                                c.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
                            FROM
                                tede_note a
                                    INNER JOIN
                                tede_user b ON a.UserId = b.Id
                                    INNER JOIN
                                tede_middle c ON c.Id = a.FolderId
                            WHERE
                                a.IsTopic = 1";
            if (!string.IsNullOrEmpty(request.SearchKey))
            {
                sql += $@"  and (b.Name like '%11%' or a.Title like '%11%' or a.Content like '%11%')";
            }
            sql += $@" AND (a.FolderId IN (SELECT 
                                        Id
                                    FROM
                                        tede_middle
                                    WHERE
                                        id IN (SELECT 
                                                MiddleId
                                            FROM
                                                tede_folder_user
                                            WHERE
                                                UserId = {request.UserId})
                                            AND RoleId IN (3))
                                    OR a.FolderId IN (SELECT 
                                        Id
                                    FROM
                                        tede_middle
                                    WHERE
                                        RoleId IN (1))) ";
            sql += $@" limit {(request.Page - 1) * request.PerPage},{request.PerPage}";

            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypestr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = new PagedList<NoteTopicResult>();
            result.Items =
                  await connection
                      .QueryAsync<NoteTopicResult, User, Entity.Middle.Middle, NoteTopicResult>(sql,
                        (noteTopicResult, user, middle) =>
                        {
                            noteTopicResult.UserName = user == null ? "" : user.Name;
                            noteTopicResult.AvatarUrl = user == null ? "" : StringUtils.AddDomainMin(user.AvatarUrl);
                            noteTopicResult.FolderId = middle == null ? 0 : middle.Id;
                            return noteTopicResult;
                        }, splitOn: "Id,Name,Id");
            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.Id = a.FolderId
                            WHERE
                                a.IsTopic = 1";
            if (!string.IsNullOrEmpty(request.SearchKey))
            {
                sql += $@"  and (b.Name like '%11%' or a.Title like '%11%' or a.Content like '%11%')";
            }
            sql += $@" AND (a.FolderId IN (SELECT 
                                        Id
                                    FROM
                                        tede_middle
                                    WHERE
                                        id IN (SELECT 
                                                MiddleId
                                            FROM
                                                tede_folder_user
                                            WHERE
                                                UserId = {request.UserId})
                                            AND RoleId IN (3))
                                    OR a.FolderId IN (SELECT 
                                        Id
                                    FROM
                                        tede_middle
                                    WHERE
                                        RoleId IN (1))) ";
            result.Total =
                 await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
    }

}