using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using Dapper;
using GxPress.Common.AppOptions;
using GxPress.Common.Page;
using GxPress.Common.Tools;
using GxPress.Entity;
using GxPress.Repository.Interface;
using GxPress.Request.App.Topic;
using GxPress.Result.App.Topic;
using Microsoft.Extensions.Options;
using Datory;
using GxPress.Entity.Topic;
using GxPress.Result;
using SqlKata;
using GxPress.Result.Job;
namespace GxPress.Repository.Implement
{
public class TopicRepository : ITopicRepository
{
private readonly Repository<Entity.Topic.Topic> _repository;
private readonly Repository<TopicAddressee> _topicAddresseeRepository;
private readonly Repository<User> _userRepository;
private readonly IMapper _mapper;
private readonly string _connectionString;
private readonly string _databaseTypeStr;
private readonly Repository<GroupUser> _groupUserRepository;
private readonly Repository<Entity.Visit.Visit> _visitRepository;
public TopicRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper)
{
_databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType;
_connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
var databaseType = StringUtils.ToEnum<DatabaseType>(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql);
var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString);
_repository = new Repository<Entity.Topic.Topic>(database);
_topicAddresseeRepository = new Repository<TopicAddressee>(database);
_userRepository = new Repository<User>(database);
_groupUserRepository = new Repository<GroupUser>(database);
_visitRepository = new Repository<Entity.Visit.Visit>(database);
_mapper = mapper;
}
public TopicRepository()
{
}
public IDatabase Database => _repository.Database;
public string TableName => _repository.TableName;
public List<TableColumn> TableColumns => _repository.TableColumns;
/// <summary>
/// 根据userId获取话题
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<Entity.Topic.Topic>> GetTopicByUserIdAsync(TopicPageSearchRequest request)
{
var query = Q.NewQuery();
if (request.UserId > 0)
{
query.Where(nameof(Entity.Topic.Topic.UserId), request.UserId);
}
if (!string.IsNullOrEmpty(request.Keyword))
{
var like = $"%{request.Keyword}%";
query.WhereLike(nameof(Entity.Topic.Topic.Title), like);
}
var pagedList = new PagedList<Entity.Topic.Topic>
{
Total = await _repository.CountAsync(query)
};
var list = await _repository.GetAllAsync(query.ForPage(request.Page, request.PerPage));
pagedList.Items = list;
return pagedList;
}
/// <summary>
/// APP列表显示用
/// </summary>
/// <param name="contentJsonDataList"></param>
/// <returns></returns>
public List<ContentJsonData> GetListAsync(List<ContentJsonData> contentJsonDataList)
{
var result = new List<ContentJsonData>();
if (contentJsonDataList.Count > 0)
{
//查询文本
var txtType = contentJsonDataList.FirstOrDefault(n => n.Type == 1);
if (txtType != null)
result.Add(txtType);
//拼接图片
var imgType = contentJsonDataList.FindAll(n => n.Type == 2);
if (imgType.Any())
{
foreach (var item in imgType)
{
//判断图片数量
if (result.Count(n => n.Type == 2) > 8)
break;
result.Add(item);
}
}
}
return result;
}
/// <summary>
/// 根据GroupId获取通知
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<TopicListPageResult>> FindTopicByGroupIdAsync(TopicDetailListRequest request)
{
//用户收件人
var resultData = new PagedList<TopicListPageResult>();
var showKey = $@"SELECT a.*,(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
TypeValue =1
AND SourceId = a.Id
AND AnalyzeType = 1) AS PraiseCount,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 1
AND SourceId = a.Id
AND AnalyzeType = 1
LIMIT 0 , 1) AS IsPraise,
(SELECT
COUNT(1)
FROM
ccpph.tede_comment
WHERE
ArticleId = a.Id and pid=0
AND TypeValue = 1) AS CommentCount,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 1
AND SourceId = a.Id
AND AnalyzeType = 4) AS RetransmissionCount,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 1
AND SourceId = a.Id
AND AnalyzeType = 4
LIMIT 0 , 1) AS IsRetransmission,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 1
AND SourceId = a.Id
AND AnalyzeType = 4
LIMIT 0 , 1) AS IsCollect,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 1
AND SourceId = a.Id
AND AnalyzeType = 4) AS CollectCount,b.Name,b.AvatarUrl,c.Name FROM";
var countSql = $"SELECT count(1) FROM ccpph.tede_topic a inner join ccpph.tede_user b on a.UserId=b.Id inner join ccpph.tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
var sql =
$"{showKey} ccpph.tede_topic a inner join ccpph.tede_user b on a.UserId=b.Id inner join ccpph.tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
var strValue = "";
if (!string.IsNullOrEmpty(request.Key))
strValue += $" and (a.Title like '%{request.Key}%' or a.Content like '%{request.Key}%' or b.Name like '%{request.Key}%')";
sql += $"{strValue} order by a.CreatedDate DESC LIMIT @page , @pageSize";
countSql += $"{strValue}";
var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
var database = new Database(databaseType, _connectionString);
var connection = database.GetConnection();
var result =
connection
.Query<TopicListPageResult, User, Department, TopicListPageResult>(sql,
(topicListPageResult, user, department) =>
{
topicListPageResult.DepartmentName = department.Name;
topicListPageResult.UserName = user.Name;
topicListPageResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
return topicListPageResult;
}, new { page = (request.Page - 1) * request.PerPage, pageSize = request.PerPage }, splitOn: "Name");
resultData.Total = await connection.ExecuteScalarAsync<int>(countSql);
var topicListPageResults = result as TopicListPageResult[] ?? result.ToArray();
resultData.Items = topicListPageResults;
return resultData;
}
/// <summary>
/// 根据小组ID获取话题数量
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public async Task<int> GetGroupTopicCountAsync(int groupId)
{
return await _repository.CountAsync(Q.Where(nameof(Entity.Topic.Topic.GroupId), groupId));
}
/// <summary>
/// 根据ID获取话题
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Entity.Topic.Topic> GetAsync(int id)
{
return await _repository.GetAsync(id);
}
public async Task<int> CountAsync(Query query)
{
return await _repository.CountAsync(query);
}
public async Task<IEnumerable<Entity.Topic.Topic>> GetAllAsync(Query query)
{
return await _repository.GetAllAsync(query);
}
public async Task<int> InsertAsync(Entity.Topic.Topic topic)
{
return await _repository.InsertAsync(topic);
}
public async Task<bool> DeleteAsync(int id)
{
return await _repository.DeleteAsync(id);
}
public async Task<bool> UpdateAsync(Entity.Topic.Topic topic)
{
return await _repository.UpdateAsync(topic);
}
/// <summary>
/// 执行话题
/// </summary>
/// <returns></returns>
public async Task<List<JobTopicResult>> ExecuteTopic()
{
string sql = "select a.*,b.UserId,b.Id,c.Name,d.AvatarUrl from ccpph.tede_topic a inner join ccpph.tede_topic_addressee b on a.Id=b.TopicId inner join ccpph.tede_user c on c.Id=b.UserId inner join ccpph.tede_user d on d.Id=a.UserId where b.IsUpload=0 order by a.CreatedDate desc limit 0,100";
var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
var database = new Database(databaseType, _connectionString);
var connection = database.GetConnection();
var result = await connection.QueryAsync<JobTopicResult, TopicAddressee, User, User, JobTopicResult>(sql, (jobTopicResult, topicAddressee, user, userDto) =>
{
jobTopicResult.UserId = topicAddressee.UserId;
jobTopicResult.Name = user.Name;
jobTopicResult.AvatarUrl = userDto.AvatarUrl;
jobTopicResult.TopicAddresseeId = topicAddressee.Id;
return jobTopicResult;
}, splitOn: "UserId,Name,AvatarUrl");
connection.Dispose();
return result.ToList();
}
/// <summary>
/// 新版 获取换题列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<TopicListPageResult>> GetTopicPage(TopicPageSearchRequest request)
{
var result = new PagedList<TopicListPageResult>();
var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
var database = new Database(databaseType, _connectionString);
var connection = database.GetConnection();
result.Items = await connection.QueryAsync<TopicListPageResult, User, TopicListPageResult>(AssembleSql(request), (topicListPageResult, user) =>
{
topicListPageResult.UserName = user != null ? user.Name : "";
topicListPageResult.AvatarUrl = user != null ? StringUtils.AddDomainMin(user.AvatarUrl) : "";
topicListPageResult.FolderResult.Id = topicListPageResult.FolderId;
topicListPageResult.FolderResult.FolderName = topicListPageResult.FolderName;
return topicListPageResult;
}, splitOn: "Id,Name");
result.Total = await connection.ExecuteScalarAsync<int>(AssembleSqlCount(request));
connection.Dispose();
return result;
}
public string AssembleSql(TopicPageSearchRequest request)
{
var sql = $@"SELECT
a.*,
(SELECT
FolderName
FROM
tede_middle
WHERE
id = a.FolderId) AS FolderName,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
TypeValue = 3
AND SourceId = a.Id
AND AnalyzeType = 1) AS PraiseCount,
(SELECT
COUNT(1)
FROM
ccpph.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
ccpph.tede_comment
WHERE
ArticleId = a.Id AND TypeValue = 3
AND Pid = 0) AS CommentCount,
(SELECT
COUNT(1)
FROM
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 3
AND SourceId = a.Id
AND AnalyzeType = 4) AS RetransmissionCount,
(SELECT
COUNT(1)
FROM
ccpph.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
ccpph.tede_analyze
WHERE
UserId = {request.UserId} AND TypeValue = 3
AND SourceId = a.Id
AND AnalyzeType = 3
LIMIT 0 , 1) AS IsCollect,
b.Name, b.AvatarUrl
FROM
ccpph.tede_note a
INNER JOIN
ccpph.tede_user b ON a.UserId = b.Id
INNER JOIN
ccpph.tede_middle c ON c.MiddleId = a.Id
WHERE
c.FolderType = 4 AND a.IsTopic = 1
AND (a.FolderId IN (SELECT
MiddleId
FROM
tede_folder_user
WHERE
MiddleId = a.FolderId AND UserId = {request.UserId})
OR a.UserId IN (SELECT
Id
FROM
tede_user
WHERE
1 = (SELECT
RoleId
FROM
tede_middle
WHERE
id = a.FolderId)
AND id = {request.UserId}))";
if (request.TopicGroupIds.Count > 0)
{
var topicGroupId = "";
foreach (var item in request.TopicGroupIds)
{
topicGroupId += $"{item},";
}
topicGroupId = topicGroupId.Remove(topicGroupId.Length - 1, 1);
sql += $@" AND a.UserId IN (SELECT
UserId
FROM
tede_topic_group_user
WHERE
TopicGroupId IN ({topicGroupId}))";
}
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
OR a.Title LIKE '%{request.Keyword}%'
OR a.HtmlContent LIKE '%{request.Keyword}%')";
}
sql += $@" ORDER BY a.CreatedDate DESC
LIMIT {(request.Page - 1) * request.PerPage} , {request.PerPage}";
return sql;
}
public string AssembleSqlCount(TopicPageSearchRequest request)
{
var sql = $@"SELECT count(1) FROM
ccpph.tede_note a
INNER JOIN
ccpph.tede_user b ON a.UserId = b.Id
INNER JOIN
ccpph.tede_middle c ON c.MiddleId = a.Id
WHERE
c.FolderType = 4 AND a.IsTopic = 1
AND (a.FolderId IN (SELECT
MiddleId
FROM
tede_folder_user
WHERE
MiddleId = a.FolderId AND UserId = {request.UserId})
OR a.UserId IN (SELECT
Id
FROM
tede_user
WHERE
1 = (SELECT
RoleId
FROM
tede_middle
WHERE
id = a.FolderId)
AND id = {request.UserId}))";
if (request.TopicGroupIds.Count > 0)
{
var topicGroupId = "";
foreach (var item in request.TopicGroupIds)
{
topicGroupId += $"{item},";
}
topicGroupId = topicGroupId.Remove(topicGroupId.Length - 1, 1);
sql += $@" AND a.UserId IN (SELECT
UserId
FROM
tede_topic_group_user
WHERE
TopicGroupId IN ({topicGroupId}))";
}
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
OR a.Title LIKE '%{request.Keyword}%'
OR a.HtmlContent LIKE '%{request.Keyword}%')";
}
return sql;
}
}
}