using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Transactions;
using AutoMapper;
using Dapper;
using GxPress.Common.AppOptions;
using GxPress.Common.Exceptions;
using GxPress.Common.Page;
using GxPress.Common.Tools;
using GxPress.Entity;
using GxPress.Entity.Middle;
using GxPress.EnumConst;
using GxPress.Repository.Interface;
using GxPress.Request.Notice;
using GxPress.Result.Notice;
using Microsoft.Extensions.Options;
using Datory;
using SqlKata;
using GxPress.Result.Job;
using System.Linq;
namespace GxPress.Repository.Implement
{
public class NoticeRepository : INoticeRepository
{
private readonly Repository<Notice> _repository;
private readonly Repository<Addressee> _noticeAddresseeRepository;
private readonly Repository<Cc> _noticeCcRepository;
private readonly Repository<User> _userRepository;
private readonly Repository<Middle> _middleRepository;
private readonly Repository<Entity.AdminVerify> _adminVerifyRepository;
private readonly IMapper _mapper;
private readonly string _connectionString;
private readonly string _databaseTypeStr;
public NoticeRepository(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<Notice>(database);
_noticeAddresseeRepository = new Repository<Addressee>(database);
_noticeCcRepository = new Repository<Cc>(database);
_userRepository = new Repository<User>(database);
_middleRepository = new Repository<Middle>(database);
_adminVerifyRepository = new Repository<Entity.AdminVerify>(database);
_mapper = mapper;
}
public IDatabase Database => _repository.Database;
public string TableName => _repository.TableName;
public List<TableColumn> TableColumns => _repository.TableColumns;
public async Task<Notice> GetAsync(int id)
{
return await _repository.GetAsync(id);
}
public async Task<Notice> GetAsync(SqlKata.Query query)
{
return await _repository.GetAsync(query);
}
/// <summary>
/// 添加通知
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<int> InsertAsync(NoticeInRequest request)
{
var noticeId = 0;
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
var userEntity = await _userRepository.GetAsync(request.UserId);
if (userEntity == null)
throw new BusinessException("用户不存在");
//通知
var notice = new Notice
{
NoticeType = request.NoticeType.GetHashCode() == 0 ? NoticeTypeConst.Send : request.NoticeType,
UserId = request.UserId,
TypeValue = request.TypeValue,
Content = request.Content,
Title = request.Title,
ParentId = request.ParentId,
UserName = userEntity.Name,
HtmlContent = request.HtmlContent,
SourceId = request.SoureId,
IsDelete = false,
IsTop = false,
SonId = request.SonId
};
noticeId = await _repository.InsertAsync(notice);
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new BusinessException(e.Message);
}
return noticeId;
}
/// <summary>
/// 根据用户ID查询
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<Notice>> GetNoticeByUserIdAsync(NoticePageSearchRequest request)
{
var query = Q.NewQuery();
if (request.UserId > 0)
{
query.Where(nameof(Addressee.UserId), request.UserId);
}
if (request.SearchType == 0)
{
query.Where(nameof(Addressee.UserId), request.UserId);
}
if (request.SearchType == 1)
{
query.Where(nameof(Addressee.IsAdmin), true);
}
if (request.SearchType == 2)
{
query.Where(nameof(Addressee.NoticeAddresseeType), NoticeTypeConst.Draft);
}
if (request.SearchType == 3)
{
query.Where(nameof(Addressee.IsDelete), true);
}
if (!string.IsNullOrEmpty(request.Keyword))
{
var like = $"%{request.Keyword}%";
query.WhereLike(nameof(Notice.Title), like);
}
var pagedList = new PagedList<Notice>
{
Total = await _repository.CountAsync(query)
};
var list = await _repository.GetAllAsync(query.ForPage(request.Page, request.PerPage));
pagedList.Items = list;
return pagedList;
}
/// <summary>
/// admin根据用户ID查询
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<NoticeListPageResult>> GetAdminNoticeByUserIdAsync(NoticePageSearchRequest request)
{
//用户收件人
var noticeQuery = Q.NewQuery();
if (request.SearchType == 2)
{
noticeQuery.Where(nameof(Notice.NoticeType), NoticeTypeConst.Draft);
}
noticeQuery.Where(nameof(Notice.IsDelete), request.SearchType == 3);
if (!string.IsNullOrEmpty(request.Keyword))
{
noticeQuery.WhereLike(nameof(Notice.Title), $"%{request.Keyword}%");
}
var noticeList = await _repository.GetAllAsync(noticeQuery);
var result = new PagedList<NoticeListPageResult>();
result.Total = await _repository.CountAsync(noticeQuery);
var notices = await _repository.GetAllAsync(noticeQuery.OrderByDesc(nameof(Notice.CreatedDate)).ForPage(request.Page, request.PerPage));
var noticeListPageResults = new List<NoticeListPageResult>();
foreach (var notice in notices)
{
var noticeListPageResultEntity = _mapper.Map<NoticeListPageResult>(notice);
var user = await _userRepository.GetAsync(notice.UserId);
if (user != null)
{
noticeListPageResultEntity.AvatarUrl = StringUtils.AddDomain(user.AvatarUrl);
}
//阅读数量
noticeListPageResultEntity.ReadCount = await _noticeAddresseeRepository.CountAsync(
Q.Where(nameof(Addressee.SourceId), notice.Id)
.Where(nameof(Addressee.IsRead), true).Where(nameof(Addressee.IsAdmin), false));
//收件人数量
noticeListPageResultEntity.AllCount = await _noticeAddresseeRepository.CountAsync(
Q.Where(nameof(Addressee.SourceId), notice.Id)
.Where(nameof(Addressee.IsAdmin), false));
noticeListPageResults.Add(noticeListPageResultEntity);
}
result.Items = noticeListPageResults;
return result;
}
/// <summary>
/// 管理员删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<bool> AdminDeleteAsync(int id)
{
try
{
var notice = await _repository.GetAsync(id);
if (notice == null)
throw new BusinessException("通知不存在");
using (TransactionScope transactionScope = new TransactionScope())
{
//通知
await _repository.DeleteAsync(id);
//抄送
await _noticeCcRepository.DeleteAsync(Q.Where(nameof(Cc.SourceId), id));
//收件人
await _noticeAddresseeRepository.DeleteAsync(Q.Where(nameof(Addressee.SourceId), id));
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new BusinessException(e.Message);
}
return true;
}
/// <summary>
/// 自己删除
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<bool> DeleteAsync(NoticeDeRequest request)
{
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
//var notice = await _repository.GetAsync(request.NoticeId);
//if (notice == null)
// throw new BusinessException("通知不存在");
//notice.IsDelete = true;
//await _repository.UpdateAsync(notice);
//
await _noticeAddresseeRepository.UpdateAsync(Q.Set(nameof(Addressee.IsDelete), true)
.WhereIn(nameof(Addressee.Id), request.MiddleIds));
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new BusinessException(e.Message);
}
return true;
}
/// <summary>
/// 恢复删除通知
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<bool> RecoverDeleteAsync(NoticeDeRequest request)
{
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
//
await _noticeAddresseeRepository.UpdateAsync(Q.Set(nameof(Addressee.IsDelete), false)
.Where(nameof(Addressee.Id), request.MiddleIds));
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new BusinessException(e.Message);
}
return true;
}
/// <summary>
/// 通知修改
/// </summary>
/// <param name="id"></param>
/// <param name="request"></param>
/// <returns></returns>
public async Task<bool> UpdateNoticeAsync(Notice notice)
{
return await _repository.UpdateAsync(notice);
}
/// <summary>
/// 通知修改
/// </summary>
/// <param name="id"></param>
/// <param name="request"></param>
/// <returns></returns>
public async Task<bool> UpdateAsync(SqlKata.Query query)
{
return await _repository.UpdateAsync(query) > 0;
}
/// <summary>
/// 通知修改
/// </summary>
/// <param name="id"></param>
/// <param name="request"></param>
/// <returns></returns>
public async Task<bool> UpdateAsync(Entity.Notice notice)
{
return await _repository.UpdateAsync(notice);
}
/// <summary>
/// 获取通知列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<NoticeListPageResult>> GetNoticeListPageAsync(SqlKata.Query query, SqlKata.Query countQuery)
{
// //用户收件人
// string sb = "";
// if (request.SearchType == 1)
// sb += " AND a.IsAdmin = 1 and a.NoticeAddresseeType=1 AND a.IsDelete = 0";
// else if (request.SearchType == 2)
// sb += " and a.NoticeAddresseeType=2 AND a.AttributeValue=1 AND a.IsDelete = 0";
// else if (request.SearchType == 3)
// sb += " AND a.IsDelete = 1 AND a.AttributeValue=1";
// else if (request.SearchType > 3)
// sb += $"and a.NoticeAddresseeType<>2 AND b.TypeValue={request.SearchType} AND a.IsDelete = 0";
// else
// sb += " AND a.IsDelete = 0 AND a.NoticeAddresseeType != 2";
// if (!string.IsNullOrEmpty(request.Keyword))
// {
// if (request.NoticeFolderId > 0)
// sb += $@" AND a.ParentId ={request.NoticeFolderId} AND a.AttributeValue != 2 and (b.Title LIKE '%{request.Keyword}%'
// OR b.Content LIKE '%{request.Keyword}%'
// OR c.Name LIKE '%{request.Keyword}%'
// OR d.UserName LIKE '%{request.Keyword}%'
// OR a.CreatedDate LIKE '%{request.Keyword}%' or a.MiddleId in (SELECT
// g.SourceId
// FROM
// tede_addressee g
// INNER JOIN
// tede_user h ON g.UserId = h.Id
// WHERE
// g.SourceId =a.MiddleId AND h.Name LIKE '%{request.Keyword}%'))";
// else
// sb += $@" AND a.AttributeValue != 2 AND (b.Title LIKE '%{request.Keyword}%'
// OR b.Content LIKE '%{request.Keyword}%'
// OR c.Name LIKE '%{request.Keyword}%'
// OR d.UserName LIKE '%{request.Keyword}%'
// OR a.CreatedDate LIKE '%{request.Keyword}%' or a.MiddleId in (SELECT
// g.SourceId
// FROM
// tede_addressee g
// INNER JOIN
// tede_user h ON g.UserId = h.Id
// WHERE
// g.SourceId =a.MiddleId AND h.Name LIKE '%{request.Keyword}%'))";
// }
// else
// {
// var searchList = new List<int> { 1, 2, 3 };
// if (!searchList.Contains(request.SearchType))
// sb += " AND a.ParentId = " + request.NoticeFolderId;
// }
// var noticeTypeValue = GxPress.EnumConst.AllTypeConst.Inbox.GetHashCode();
// var sql = $@"SELECT a.*,
// (SELECT GROUP_CONCAT(SourceName Separator'、') from tede_user_middle where MiddleType=10 and DataSourceId=a.MiddleId) as EnjoyUser,
// (SELECT
// GROUP_CONCAT(Name
// SEPARATOR '、')
// FROM
// tede_user
// WHERE
// Id IN (SELECT
// UserId
// FROM
// tede_flow_todo
// WHERE
// FlowId = b.SourceId
// AND type = 'ApproverCheck')) AS FlowUserName,
// (SELECT
// COUNT(1)
// FROM
// tede_flow_todo
// WHERE
// FlowId = b.SourceId
// AND type = 'ApproverCheck') AS UCheckedCount,
// (SELECT
// COUNT(1)
// FROM
// tede_flow_todo
// WHERE
// FlowId = b.SourceId
// AND type = 'ApproverCheck'
// AND IsRead = 1) AS CheckedCount,
// (SELECT
// IsRead
// FROM
// tede_flow_todo
// WHERE
// UserId = a.UserId
// AND FlowId = b.SourceId
// AND Type = 'CarbonCopy'
// LIMIT 1) AS IsFlowRead,
// (SELECT
// IsRead
// FROM
// tede_flow_todo
// WHERE
// UserId = b.UserId
// AND FlowId = b.SourceId
// AND Type = 'ApproverCheck' and IsRead=1
// LIMIT 1) AS IsFlowChecked,
// (SELECT
// IsRead
// FROM
// tede_flow
// WHERE
// id = b.SourceId AND UserId = b.UserId) AS IsMyFlowRead,
// (select IsRecall from tede_notice where id=a.MiddleId) as IsRecall,
// (SELECT
// IsDone
// FROM
// tede_flow_todo
// WHERE
// type = 'ApproverCheck'
// AND UserId = a.UserId
// LIMIT 1) AS IsDone,
// (SELECT
// IsChecked
// FROM
// tede_flow_todo
// WHERE
// type = 'ApproverCheck'
// AND UserId = a.UserId
// LIMIT 1) AS IsChecked,
// (SELECT
// DoneType
// FROM
// tede_flow_todo
// WHERE
// type = 'ApproverCheck'
// AND UserId = a.UserId
// LIMIT 1) AS DoneType,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 1) AS PraiseCount,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// UserId = {request.UserId} AND TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 1
// LIMIT 0 , 1) AS IsLaud,
// (SELECT
// COUNT(1)
// FROM
// tede_comment
// WHERE
// ArticleId = a.MiddleId and pid=0
// AND TypeValue = {noticeTypeValue}) AS CommentCount,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// UserId = {request.UserId} AND TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 4) AS RetransmissionCount,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// UserId = {request.UserId} AND TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 4
// LIMIT 0 , 1) AS IsRetransmission,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// UserId = {request.UserId} AND TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 4
// LIMIT 0 , 1) AS IsCollect,
// (SELECT
// COUNT(1)
// FROM
// tede_analyze
// WHERE
// UserId = {request.UserId} AND TypeValue = {noticeTypeValue}
// AND SourceId = a.MiddleId
// AND AnalyzeType = 4) AS CollectCount,
// (SELECT
// COUNT(1)
// FROM
// tede_middle
// WHERE
// ParentId = a.Id and IsDelete=0 and NoticeAddresseeType<>2) as FileCount,(SELECT
// COUNT(1)
// FROM
// tede_addressee
// WHERE
// SourceId = a.MiddleId AND IsRead = 1) as ReadCount,(SELECT
// COUNT(1)
// FROM
// tede_addressee
// WHERE
// SourceId = a.MiddleId ) as AllCount,(SELECT
// COUNT(1)
// FROM
// tede_addressee
// WHERE
// SourceId = a.MiddleId AND IsRead = 1 and UserId={request.UserId} limit 0,1) as IsRead, b.*,a.Id, c.AvatarUrl, d.Id as NoticeAddresseeId,d.SourceId as NoticeId,(SELECT
// Name
// FROM
// tede_user
// WHERE
// id = (SELECT
// userId
// FROM
// tede_flow
// WHERE
// id = b.SourceId)) as UserName
// ,(select count(1) from tede_flow where id=b.SourceId and UserId={request.UserId}) as IsFlowAdmin,b.UserId,a.IsTop
// FROM
// tede_middle a
// left JOIN
// tede_notice b ON a.MiddleId = b.Id
// left JOIN
// tede_user c ON b.UserId = c.Id
// left JOIN
// tede_addressee d ON d.Id = a.MiddleSonId
// WHERE a.IsRecall=0 and
// a.UserId = {request.UserId} and a.FolderType={noticeTypeValue}
// {sb}
// ORDER BY a.AttributeValue desc,a.IsTop desc,a.LastModifiedDate desc
// LIMIT {(request.Page - 1) * request.PerPage},{request.PerPage}";
// var countSql = string.Format(@"SELECT
// count(1)
// FROM
// tede_middle a
// left JOIN
// tede_notice b ON a.MiddleId = b.Id
// left JOIN
// tede_user c ON a.UserId = c.Id
// left JOIN
// tede_addressee d ON d.Id = a.MiddleSonId
// WHERE a.IsRecall=0 and
// a.UserId = {1} and a.FolderType={2}
// {0}", sb, request.UserId, noticeTypeValue);
// var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
// var database = new Database(databaseType, _connectionString);
// var connection = database.GetConnection();
// var items = await connection.QueryAsync<NoticeListPageResult>(sql);
// foreach (var item in items)
// {
// item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
// if (item.AttributeValue == 3)
// {
// var query = Q.NewQuery();
// query.Where(nameof(Entity.AdminVerify.AdminId), request.UserId);
// query.Where(nameof(Entity.AdminVerify.DisposeType), AdminVerifyTypeConst.UDispose.GetHashCode());
// item.UReadCount = await _adminVerifyRepository.CountAsync(query);
// }
// }
// PagedList<NoticeListPageResult> result = new PagedList<NoticeListPageResult>
// {
// Total = await CountAsync(countSql),
// Items = items
// };
// return result;
return await GetNewNoticeListPageAsync(query, countQuery);
}
/// <summary>
/// 获取通知列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task<PagedList<NoticeListPageResult>> GetNewNoticeListPageAsync(SqlKata.Query query, SqlKata.Query countQuery)
{
var result = new PagedList<NoticeListPageResult>();
result.Items = await _middleRepository.GetAllAsync<NoticeListPageResult>(query);
result.Total = await _middleRepository.CountAsync(countQuery);
foreach (var item in result.Items)
{
item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
item.AttributeValue = item.AttributeValue == 0 ? 1 : item.AttributeValue;
}
return result;
}
public async Task<int> CountAsync(string sql)
{
var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
var database = new Database(databaseType, _connectionString);
var connection = database.GetConnection();
return await connection.ExecuteScalarAsync<int>(sql);
}
public async Task<IEnumerable<Notice>> GetAllAsync()
{
return await _repository.GetAllAsync();
}
public async Task<IEnumerable<Notice>> GetAllAsync(Query query)
{
return await _repository.GetAllAsync(query);
}
public async Task<bool> DeleteAsync(Query query)
{
return await _repository.DeleteAsync(query) > 0;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public async Task<List<JobNoticeResult>> ElasticSearchNotice()
{
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_notice b on a.MiddleId=b.Id
inner join tede_user c on c.Id=b.UserId
where a.FolderType=1 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<JobNoticeResult, Notice, User, JobNoticeResult>(sql,
(jobNoticeResult, notice, user) =>
{
jobNoticeResult.MiddleId = jobNoticeResult.Id;
jobNoticeResult.Id = notice.Id;
jobNoticeResult.Title = notice != null ? notice.Title : "";
jobNoticeResult.Content = notice != null ? notice.Content : "";
jobNoticeResult.AvatarUrl = user != null ? user.AvatarUrl : "";
jobNoticeResult.Name = user != null ? user.Name : "";
return jobNoticeResult;
},
splitOn: "Id,Id,Name");
return items.ToList();
}
}
}