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 Repository<Entity.Middle.Middle> _middleRepository;
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);
_middleRepository = new Repository<Entity.Middle.Middle>(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 _middleRepository.GetAllAsync<NotePageListRequest>(request.ResultQuery);
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 _middleRepository.CountAsync(request.CountQuery);
//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 '%{request.SearchKey}%' OR a.Content LIKE '%{request.SearchKey}%')";
var sql = $@"SELECT
a.*, b.Name, b.AvatarUrl,(SELECT
GROUP_CONCAT(a.Name
SEPARATOR '、')
FROM
tede_department a
INNER JOIN
tede_department_user b ON a.Id = b.DepartmentId
WHERE
b.UserId = {request.UserId}) as DepartmentName,
(SELECT
FolderName
FROM
tede_middle
WHERE
id = a.FolderId) AS FolderName
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} order by a.LastModifiedDate desc 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;
}
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 '%{request.SearchKey}%' or a.Title like '%11%' or a.TextContent like '%{request.SearchKey}%')";
}
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.TextContent 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.TextContent 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} limit 1)) AS FolderName,
(SELECT
Id
FROM
tede_middle
WHERE
id = (SELECT
ParentId
FROM
tede_middle
WHERE
MiddleId = a.Id AND FolderType = {noteConstValue} limit 1)) 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
c.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);
}
/// <summary>
/// 清空草稿箱
/// </summary>
/// <param name="userId"></param>
/// <param name="typeValue"></param>
/// <returns></returns>
public async Task<bool> ClearDraftAsync(int userId, int typeValue)
{
var query = Q.NewQuery();
query.Where(nameof(Entity.Note.Note.UserId), userId);
query.Where(nameof(Entity.Note.Note.IsTopic), typeValue == 1);
query.Where(nameof(Entity.Note.Note.IsDraft), true);
return await _repository.DeleteAsync(query) > 0;
}
public async Task<IEnumerable<Entity.Note.Note>> GetAllAsync(SqlKata.Query query)
{
return await _repository.GetAllAsync(query);
}
}
}