using System.Collections.Generic;
using System.Linq;
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.Missive;
using GxPress.Request.App.Missive;
using GxPress.Result.App.Missive;
using GxPress.Result.Job;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Options;
using SqlKata;
namespace GxPress.Repository.Implement.Missive
{
public class MissiveRepository : IMissiveRepository
{
private readonly Repository<Entity.Missive.Missive> _repository;
private readonly IMapper _mapper;
private readonly IDistributedCache _cache;
private readonly string _connectionString;
private readonly string _databaseTypeStr;
public MissiveRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper, IDistributedCache cache)
{
_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.Missive.Missive>(database);
_mapper = mapper;
_cache = cache;
}
public IDatabase Database => _repository.Database;
public string TableName => _repository.TableName;
public List<TableColumn> TableColumns => _repository.TableColumns;
public async Task<int> InsertAsync(Entity.Missive.Missive missive)
{
return await _repository.InsertAsync(missive);
}
public async Task<Entity.Missive.Missive> GetAsync(int id)
{
return await _repository.GetAsync(id);
}
public async Task<Entity.Missive.Missive> GetAsync(SqlKata.Query query)
{
return await _repository.GetAsync(query);
}
public async Task<bool> UpdateAsync(Entity.Missive.Missive missive)
{
return await _repository.UpdateAsync(missive);
}
public async Task<bool> DeleteAsync(Query query)
{
return await _repository.DeleteAsync(query) > 0;
}
public async Task<bool> UpdateAsync(Query query)
{
return await _repository.UpdateAsync(query) > 0;
}
public async Task<IEnumerable<Entity.Missive.Missive>> GetAllAsync(Query query)
{
return await _repository.GetAllAsync(query);
}
/// <summary>
/// 获取站内信
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<MissiveSearchResult>> GetMissiveSearchResultAsync(MissiveSearchRequest request)
{
var sb = "";
if (!string.IsNullOrEmpty(request.Key))
sb += $@" AND a.AttributeValue != 2 AND (b.Title LIKE '%{request.Key}%'
OR b.Content LIKE '%{request.Key}%'
OR c.Name LIKE '%{request.Key}%'
OR a.UserId IN (SELECT
Id
FROM
tede_user
WHERE
Id IN (SELECT
UserId
FROM
tede_addressee
WHERE SourceType={AllTypeConst.Missive.GetHashCode()} and
SourceId = a.MiddleId)
AND Name LIKE '%{request.Key}%')
OR a.CreatedDate LIKE '%{request.Key}%')";
if (request.FolderId > 0)
sb += $" AND a.ParentId = {request.FolderId}";
if (request.SearchType == 1)
sb += $" AND a.ParentId = {request.FolderId} AND a.UserId = {request.UserId} and a.IsDelete=0 and a.NoticeAddresseeType!=2";
else if (request.SearchType == 2)
sb += $" AND a.UserId = {request.UserId} and a.IsAdmin=1 and a.IsDelete=0 and a.NoticeAddresseeType!=2";
else if (request.SearchType == 3)
sb += $" AND a.UserId = {request.UserId} and a.IsAdmin=1 and a.NoticeAddresseeType=2 and a.IsDelete=0";
else if (request.SearchType == 4)
sb += $" AND a.UserId = {request.UserId} and a.IsDelete=1 AND a.AttributeValue=1";
else if (request.SearchType == 5)
sb += $" AND a.UserId = {request.UserId} and b.MissiveType=2 and a.IsDelete=0";
else
sb += $" AND a.UserId = {request.UserId} and a.IsDelete=0";
var missiveTypeValue = GxPress.EnumConst.AllTypeConst.Missive.GetHashCode();
var sql = $@"SELECT
a.*,(SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType=30 and DataSourceId=a.MiddleId) as EnjoyUser,
(SELECT
COUNT(1)
FROM
tede_middle
WHERE
ParentId = a.Id and IsDelete=0 and NoticeAddresseeType=1) as FileCount,
(SELECT
COUNT(1)
FROM
tede_addressee
WHERE
SourceType={missiveTypeValue} and
SourceId = a.MiddleId AND IsRead = 1) AS ReadCount,
(SELECT
COUNT(1)
FROM
tede_addressee
WHERE SourceType={missiveTypeValue} and
SourceId = a.MiddleId ) AS AllCount,
(SELECT
COUNT(1)
FROM
tede_addressee
WHERE
SourceType={missiveTypeValue} and
SourceId = a.MiddleId AND IsRead = 1
AND UserId = {request.UserId}
LIMIT 0 , 1) AS IsRead, b.Title, b.UserName, b.MissiveType,b.SendUserId,b.Content,b.SourceId
FROM
tede_middle a
left JOIN
tede_missive b ON a.MiddleId = b.Id INNER JOIN
tede_user c ON c.Id = a.UserId
WHERE
a.FolderType = 5 {sb} order by a.IsTop desc,a.LastModifiedDate desc,a.AttributeValue desc limit {(request.Page - 1) * request.PerPage},{request.PerPage}";
var sqlCount = $@"SELECT
count(1)
FROM
tede_middle a
left JOIN
tede_missive b ON a.MiddleId = b.Id INNER JOIN
tede_user c ON c.Id = a.UserId
WHERE
a.FolderType = {missiveTypeValue} {sb} ";
var result = new PagedList<MissiveSearchResult>();
var databaseType = _databaseTypeStr.ToEnum(DatabaseType.MySql);
var database = new Database(databaseType, _connectionString);
var connection = database.GetConnection();
result.Items =
await connection
.QueryAsync<MissiveSearchResult, Entity.Missive.Missive, MissiveSearchResult>(sql,
(missiveSearchResult, missive) =>
{
missiveSearchResult.Title = missive != null ? missive.Title : "";
missiveSearchResult.MissiveType = missive?.MissiveType ?? 0;
missiveSearchResult.Content = missive != null ? missive.Content : "[]";
missiveSearchResult.SourceId = missive != null ? missive.SourceId : 0;
missiveSearchResult.SendUserId = missive != null ? missive.SendUserId : 0;
if (missive != null) missiveSearchResult.SendName = missive.UserName;
return missiveSearchResult;
}, null, splitOn: "Title");
result.Total = await connection.ExecuteScalarAsync<int>(sqlCount);
return result;
}
public async Task<List<JobMissiveResult>> ElasticSearchMissive()
{
var sql = "select a.Id,a.UserId,a.GuId,a.CreatedDate,b.Id,b.Title,b.Content,b.CreatedDate,c.Name,c.AvatarUrl from tede_middle a inner join tede_Missive b on a.MiddleId=b.Id inner join tede_user c on c.Id=b.UserId where a.FolderType=5 and a.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 items = await connection
.QueryAsync<JobMissiveResult, Entity.Missive.Missive, User, JobMissiveResult>(sql,
(jobMissiveResult, missive, user) =>
{
jobMissiveResult.MiddleId = jobMissiveResult.Id;
jobMissiveResult.Id = missive.Id;
jobMissiveResult.Title = missive != null ? missive.Title : "";
jobMissiveResult.Content = missive != null ? missive.Content : "";
jobMissiveResult.AvatarUrl = user != null ? user.AvatarUrl : "";
jobMissiveResult.Name = user != null ? user.Name : "";
return jobMissiveResult;
},
splitOn: "Id,Id,Name");
return items.ToList();
}
}
}