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.EnumConst;
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="noteId"></param>
        /// <returns></returns>
        public async Task<bool> SetIsTopAsync(int noteId)
        {
            var note = await _repository.GetAsync(noteId);
            note.IsTop = !note.IsTop;
            return await _repository.UpdateAsync(note);
        }
       
        /// <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>(GetCollectionSql(request));
            foreach (var item in result)
            {
                if (string.IsNullOrEmpty(item.FolderName))
                {
                    item.FolderName = item.FolderParentName;
                    item.FolderId = item.ParentId;
                }
                else
                    item.FolderId = item.Id;
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            }
            pagedList.Items = result;
            pagedList.Total = await NotePageListCountAsync(GetCollectionSqlCount(request));
            return pagedList;
        }
        /// <summary>
        /// 获取笔记/话题草稿分页
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<NoteNotFolderPageResult>> NoteTopicDraftPageListAsync(NoteSearchPageListRequest request)
        {
            request.TypeValue = request.TypeValue == 1 ? 1 : 0;
            var pagedList = new PagedList<NoteNotFolderPageResult>();
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypestr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var sqlStr = string.Empty;
            if (!string.IsNullOrEmpty(request.SearchKey))
                sqlStr = $@"AND (a.Title LIKE '%1%' OR a.Content LIKE '%1%')";
            var sql = $@"SELECT 
                            a.*, b.Name, b.AvatarUrl
                        FROM
                            tede_note a
                                INNER JOIN
                            tede_user b ON a.UserId = b.Id
                        WHERE
                            a.UserId ={request.UserId} AND a.IsDraft = 1 and a.IsTopic={request.TypeValue}
                            {sqlStr} limit {(request.Page - 1) * request.PerPage},{request.PerPage}";
            var result =
                await connection
                    .QueryAsync<NoteNotFolderPageResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
            }
            pagedList.Items = result;
            sql = $@"SELECT 
                            count(1)
                        FROM
                            tede_note a
                                INNER JOIN
                            tede_user b ON a.UserId = b.Id
                        WHERE
                            a.UserId ={request.UserId} AND a.IsDraft = 1 and a.IsTopic={ request.TypeValue}
                            {sqlStr}";
            pagedList.Total = await NotePageListCountAsync(sql);
            return pagedList;
        }

        /// <summary>
        /// 集合
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public string GetCollectionSql(NoteSearchPageListRequest request)
        {
            string sql = "";
            var noteTypeId = request.TypeValue == 0 ? AllTypeConst.Note.GetHashCode() : AllTypeConst.TopicNote.GetHashCode();
            if (request.VisitUserId > 0)
            {
                var sqlStr = $"a.Id";
                if (request.FolderId > 0)
                    sqlStr = "a.ParentId";
                sql =
                   $@"SELECT  a.Id,a.MiddleId,a.RoleId,a.ParentId,a.AttributeValue,a.UserId,
                      a.CreatedDate,a.IsTop,a.FolderName,a.FolderType,
                    (SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType={noteTypeId} 
                    and DataSourceId=a.MiddleSonId) as EnjoyUser,
                    (SELECT 
                            COUNT(1)
                        FROM
                            tede_analyze
                        WHERE
                            UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                AND SourceId = a.MiddleId
                                AND AnalyzeType = 1) AS PraiseCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                    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 = {noteTypeId}) AS CommentCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 4) AS RetransmissionCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 4
                            LIMIT 0 , 1) AS IsRetransmission,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 3
                            LIMIT 0 , 1) AS IsCollect,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 3) 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 AS DepartmentName,
                        (SELECT 
                                FolderName
                            FROM
                                tede_middle
                            WHERE
                                id = (SELECT 
                                        ParentId
                                    FROM
                                        tede_middle
                                    WHERE
                                        MiddleId = a.Id AND FolderType ={noteTypeId})) AS FolderParentName,
                        (SELECT 
                                Id
                            FROM
                                tede_middle
                            WHERE
                                id = (SELECT 
                                        ParentId
                                    FROM
                                        tede_middle
                                    WHERE
                    MiddleId = a.Id AND FolderType = {noteTypeId})) AS FolderId
                    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.FolderType = {noteTypeId} AND a.userId = {request.VisitUserId}
                        AND a.IsDelete = 0
                        AND (a.UserId IN (SELECT 
                            Id
                        FROM
                            tede_user
                        WHERE
                            1 = a.RoleId)
                         OR a.UserId IN (SELECT 
                            Id
                        FROM
                            tede_user
                        WHERE
                            0 = a.RoleId)
                        OR {sqlStr} IN (SELECT 
                            MiddleId
                        FROM
                            tede_folder_user
                        WHERE
                        UserId = {request.UserId} AND MiddleId = {sqlStr}))";
                if (request.TypeValue == 0)
                    sql += "and  (a.RoleId in(2) or a.AttributeValue=1)";
                else
                {
                    if (request.FolderId != 0)
                        sql += "and  (a.RoleId in(1,3) or a.AttributeValue=1)";
                    else
                        sql += "and  (a.RoleId in(1,3))";
                }
                if (request.FolderId == 0 && !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 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}%')";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                sql +=
                    $@" ORDER BY a.IsSystemDefault desc,a.AttributeValue desc,a.IsTop DESC,a.Sort DESC , a.LastModifiedDate DESC";
                if (request.Page > 0)
                    sql += $@" limit {(request.Page - 1) * request.PerPage},{request.PerPage}";
            }
            else
            {
                sql =
                    $@"SELECT  a.Id,a.MiddleId,a.RoleId,a.ParentId,a.AttributeValue,a.UserId,
                      a.CreatedDate,a.IsTop,a.FolderName,a.FolderType,
                    (SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType={noteTypeId} 
                    and DataSourceId=a.MiddleSonId) as EnjoyUser,
                    (SELECT 
                            COUNT(1)
                        FROM
                            tede_analyze
                        WHERE
                            UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                AND SourceId = a.MiddleId
                                AND AnalyzeType = 1) AS PraiseCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                    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 = {noteTypeId}) AS CommentCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 4) AS RetransmissionCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 4
                            LIMIT 0 , 1) AS IsRetransmission,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 3
                            LIMIT 0 , 1) AS IsCollect,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_analyze
                            WHERE
                                UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                    AND SourceId = a.MiddleId
                                    AND AnalyzeType = 3) 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 AS DepartmentName,
                        (SELECT 
                                FolderName
                            FROM
                                tede_middle
                            WHERE
                                id = (SELECT 
                                        ParentId
                                    FROM
                                        tede_middle
                                    WHERE
                                        MiddleId = a.Id AND FolderType ={noteTypeId})) AS FolderParentName,
                        (SELECT 
                                Id
                            FROM
                                tede_middle
                            WHERE
                                id = (SELECT 
                                        ParentId
                                    FROM
                                        tede_middle
                                    WHERE
                    MiddleId = a.Id AND FolderType = {noteTypeId})) AS FolderId
                    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 = {noteTypeId} AND a.IsDelete = 0 ";
                if (request.TypeValue == 0)
                    sql += "and  (a.RoleId in(2) or a.AttributeValue=1)";
                else
                {
                    if (request.FolderId != 0)
                        sql += "and  (a.RoleId in(1,3) or a.AttributeValue=1)";
                    else
                        sql += "and  (a.RoleId in(1,3))";
                }
                if (request.FolderId == 0 && !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 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}%')";
                else
                    sql += $" and a.ParentId={request.FolderId}";
                sql +=
                    $@" ORDER BY a.AttributeValue desc,a.IsTop DESC,a.Sort 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)
        {
            var noteTypeId = request.TypeValue == 0 ? AllTypeConst.Note.GetHashCode() : AllTypeConst.TopicNote.GetHashCode();
            string sql = "";

            if (request.VisitUserId > 0)
            {
                var sqlStr = $"a.Id";
                if (request.FolderId > 0)
                    sqlStr = "a.ParentId";
                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.FolderType = {noteTypeId} AND a.userId = {request.VisitUserId}
                        AND a.IsDelete = 0
                        AND (a.UserId IN (SELECT 
                            Id
                        FROM
                            tede_user
                        WHERE
                            1 = a.RoleId) 
                         OR a.UserId IN (SELECT 
                                Id
                            FROM
                                tede_user
                            WHERE
                                0 = a.RoleId)
                        OR {sqlStr} IN (SELECT 
                            MiddleId
                        FROM
                            tede_folder_user
                        WHERE
                        UserId = {request.UserId} AND MiddleId = {sqlStr}))";
                if (request.TypeValue == 0)
                    sql += "and  (a.RoleId in(2) or a.AttributeValue=1)";
                else
                {
                    if (request.FolderId != 0)
                        sql += "and  (a.RoleId in(1,3) or a.AttributeValue=1)";
                    else
                        sql += "and  (a.RoleId in(1,3))";
                }
                if (request.FolderId == 0 && !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 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}%')";
                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 ={noteTypeId} AND a.IsDelete = 0";
                if (request.TypeValue == 0)
                    sql += " and  (a.RoleId in(2) or a.AttributeValue=1)";
                else
                {
                    if (request.FolderId != 0)
                        sql += " and  (a.RoleId in(1,3) or a.AttributeValue=1)";
                    else
                        sql += " and  (a.RoleId in(1,3))";
                }
                if (request.FolderId == 0 && !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 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 noteTypeId = GxPress.EnumConst.AllTypeConst.Note.GetHashCode();
            var sql = $@"SELECT 
                                a.Id,
                                a.Title,
                                a.Content,
                                a.HtmlContent,
                                a.CreatedDate,
                                b.Id as UserId,
                                b.Name,
                                b.AvatarUrl,
                                c.Id,
                                c.FolderName,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        TypeValue ={noteTypeId} AND SourceId = a.Id
                                            AND AnalyzeType = 1) AS PraiseCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1
                                    LIMIT 0 , 1) AS IsPraise,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_comment
                                    WHERE
                                        ArticleId = a.Id AND TypeValue = {noteTypeId}
                                            AND Pid = 0) AS CommentCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4) AS RetransmissionCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue ={noteTypeId}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4
                                    LIMIT 0 , 1) AS IsRetransmission,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteTypeId}
                                            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;
                            noteTopicResult.FolderName = middle == null ? "" : middle.FolderName;
                            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;
        }
        /// <summary>
        /// 笔记没有文件夹分页
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<PagedList<NoteNotFolderPageResult>> GetNoteNotFolderPageResult(NoteSearchPageListRequest request)
        {
            var sqlValue = "";
            if (!string.IsNullOrEmpty(request.SearchKey))
            {
                sqlValue += $" and (a.Title like '%{request.SearchKey}%' or a.Content like '%{request.SearchKey}%')";
            }
            var noteConstValue = GxPress.EnumConst.AllTypeConst.Note.GetHashCode();
            var sql = $@"SELECT 
                                a.Id,
                                a.Title,
                                a.Content,
                                a.UserId,
                                a.CreatedDate,
                                a.MediaId,
                                a.ReadCount,
                                a.IsTop,
                                (SELECT 
                                        FolderName
                                    FROM
                                        tede_middle
                                    WHERE
                                        id = (SELECT 
                                                ParentId
                                            FROM
                                                tede_middle
                                            WHERE
                                                MiddleId = a.Id AND FolderType = {noteConstValue})) AS FolderName,
                                (SELECT 
                                        Id
                                    FROM
                                        tede_middle
                                    WHERE
                                        id = (SELECT 
                                                ParentId
                                            FROM
                                                tede_middle
                                            WHERE
                                                MiddleId = a.Id AND FolderType = {noteConstValue})) AS FolderId,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1) AS PraiseCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 1
                                    LIMIT 0 , 1) AS IsPraise,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_comment
                                    WHERE
                                        UserId = {request.UserId} AND ArticleId = a.Id
                                            AND TypeValue = {noteConstValue}) AS CommentCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue ={noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = {noteConstValue}) AS RetransmissionCount,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 4
                                    LIMIT 0 , 1) AS IsRetransmission,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                         UserId = {request.UserId} AND TypeValue = {noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 3
                                    LIMIT 0 , 1) AS IsCollect,
                                (SELECT 
                                        COUNT(1)
                                    FROM
                                        tede_analyze
                                    WHERE
                                        UserId = {request.UserId} AND TypeValue = {noteConstValue}
                                            AND SourceId = a.Id
                                            AND AnalyzeType = 3) AS IsCollect,
                                b.Name,
                                b.AvatarUrl,c.Id,c.MiddleId
                            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
                                a.IsDelete = 0 and a.IsTopic=0 AND a.UserId = {request.UserId}  and c.FolderType={noteConstValue} {sqlValue}
                            ORDER BY a.IsTop desc ,a.LastModifiedDate DESC
                            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<NoteNotFolderPageResult>();
            result.Items =
                  await connection
                      .QueryAsync<NoteNotFolderPageResult, User, Entity.Middle.Middle, NoteNotFolderPageResult>(sql,
                        (result, user, middle) =>
                        {
                            result.MiddleId = middle.MiddleId;
                            result.Id = middle.Id;
                            result.Name = user == null ? "" : user.Name;
                            result.AvatarUrl = user == null ? "" : StringUtils.AddDomainMin(user.AvatarUrl);
                            return result;
                        }, splitOn: "Id,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.MiddleId=a.Id
                            WHERE
                                a.IsDelete = 0 and a.IsTopic=0  AND a.UserId = {request.UserId}  and c.FolderType={noteConstValue} {sqlValue}";
            result.Total = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
        /// <summary>
        /// 获取用户最近使用的文件夹
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<int>> GetLatelyFolderIdAsync(int userId, bool isTopic)
        {
            var query = Q.NewQuery();
            query.Select(nameof(Entity.Note.Note.FolderId));
            query.Where(nameof(Entity.Note.Note.UserId), userId);
            query.Where(nameof(Entity.Note.Note.IsTopic), isTopic);
            query.Where(nameof(Entity.Note.Note.FolderId), ">", 0);
            query.OrderByDesc(nameof(Entity.Note.Note.CreatedDate));
            return await _repository.GetAllAsync<int>(query);
        }
        /// <summary>
        /// 获取用户是否存在草稿
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<bool> IsExistsDraftAsync(int userId, bool isTopic)
        {
            var query = Q.NewQuery();
            query.Where(nameof(Entity.Note.Note.UserId), userId);
            query.Where(nameof(Entity.Note.Note.IsTopic), isTopic);
            query.Where(nameof(Entity.Note.Note.IsDraft), true);
            return await _repository.ExistsAsync(query);
        }
        /// <summary>
        /// 获取用户最新的草稿Id
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<int> GetNoteIdByDraftAsync(int userId, bool isTopic)
        {
            var query = Q.NewQuery();
            query.Where(nameof(Entity.Note.Note.UserId), userId);
            query.Where(nameof(Entity.Note.Note.IsTopic), isTopic);
            query.Where(nameof(Entity.Note.Note.IsDraft), true);
            query.OrderByDesc(nameof(Entity.Note.Note.CreatedDate));
            query.Select(nameof(Entity.Note.Note.Id));
            return await _repository.GetAsync<int>(query);
        }
        /// <summary>
        /// 获取用户最新的草稿Id
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<int> GetDraftCountAsync(int userId, bool isTopic)
        {
            var query = Q.NewQuery();
            query.Where(nameof(Entity.Note.Note.UserId), userId);
            query.Where(nameof(Entity.Note.Note.IsTopic), isTopic);
            query.Where(nameof(Entity.Note.Note.IsDraft), true);
            return await _repository.CountAsync(query);
        }
    }
}