using Newtonsoft.Json;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Dtos.System;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Infrastructure.Tools;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.System
{
public class MessageRepository : BaseRepository<Sys_Message, MessageView>
{
private readonly SetDataRepository _setData;
public MessageRepository(SqlSugarClient sqlSugar, SetDataRepository setData)
: base(sqlSugar)
{
_setData = setData;
}
/// <summary>
/// 发布消息
/// </summary>
/// <param name="msgDto"></param>
/// <returns></returns>
public async Task<bool> AddMsg(MessageDto msgDto)
{
#region 参数处理
if (msgDto == null) { return false; }
if (string.IsNullOrEmpty(msgDto.Title)) { return false; }
if (string.IsNullOrEmpty(msgDto.Content)) { return false; }
if (msgDto.UIdList.Count <= 0) { return false; }
#endregion
_sqlSugar.BeginTran();
try
{
Sys_Message message = new Sys_Message()
{
Type = msgDto.Type,
IssuerId = msgDto.IssuerId,
Title = msgDto.Title,
Content = msgDto.Content,
ReleaseTime = msgDto.ReleaseTime,
CreateUserId = msgDto.IssuerId,
CreateTime = DateTime.Now,
DeleteUserId = null,
DeleteTime = "1990-01-01 00:00:00.000",
Remark = "",
IsDel = 0,
DiId = msgDto.DiId,
Param = msgDto.Param,
};
int? msgId = _sqlSugar.Insertable(message).ExecuteReturnIdentity();
if (!msgId.HasValue) { _sqlSugar.RollbackTran(); return false; }
List<Sys_MessageReadAuth> messageReadAuths = new List<Sys_MessageReadAuth>();
foreach (int item in msgDto.UIdList)
{
Sys_MessageReadAuth messageReadAuth = new Sys_MessageReadAuth()
{
MsgId = msgId.Value,
ReadableUId = item,
ReadTime = new DateTime(1990, 1, 1),
CreateUserId = msgDto.IssuerId,
CreateTime = DateTime.Now,
DeleteUserId = null,
DeleteTime = "1990-01-01 00:00:00.000",
Remark = "",
IsDel = 0
};
messageReadAuths.Add(messageReadAuth);
}
int? readIds = _sqlSugar.Insertable<Sys_MessageReadAuth>(messageReadAuths).ExecuteCommand();
if (!readIds.HasValue)
{
_sqlSugar.RollbackTran();
return false;
}
_sqlSugar.CommitTran();
}
catch (Exception)
{
_sqlSugar.RollbackTran();
return false;
}
return true;
}
/// <summary>
/// 获取消息列表
/// </summary>
/// <param name="uId">可读用户Id</param>
/// <returns></returns>
public async Task<Result> GetMsgList(MsgDto dto)
{
Result result = new Result() { Code = -1, Msg = "未知错误", Data = null };
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) // web/android
{
string msgSqlWhere = string.Format(@"Select sm.Id,sm.Type,sm.Title,sm.Content,sd.DepName issuerDep,su.CnName issuerUser,
sm.ReleaseTime,smra.Id AuthId,smra.ReadableUId,smra.IsRead,smra.ReadTime
From Sys_Message sm
Inner Join Sys_MessageReadAuth smra On sm.Id = smra.MsgId
Inner Join Sys_Users su On sm.IssuerId = su.Id
Inner Join Sys_Department sd On su.DepId = sd.Id
Inner Join Sys_Users suAuth On smra.ReadableUId = suAuth.Id
Where sm.IsDel = 0
And smra.IsDel = 0
And smra.ReadableUId = {0}
Order By ReleaseTime Desc ", dto.UserId);
var _readableMsgList = await _sqlSugar.SqlQueryable<ReadbleMessageView1>(msgSqlWhere).ToListAsync();
if (_readableMsgList.Count > 0)
{
int pageSize = dto.PageSize; // 每页显示的记录数量
int currentPage = dto.PageIndex; // 当前页码（从1开始）
//操作通知 OperationNotification
List<int> operationTypeList = new List<int>() {1,2,3,4,5 };
var operationNotificationData = _readableMsgList.Where(it => operationTypeList.Contains(it.Type)).OrderBy(it => it.IsRead).ToList();
int operationNotificationDataCount = operationNotificationData.Count;
// 计算起始索引和结束索引
int operationStartIndex = (currentPage - 1) * pageSize;
int operationEndIndex = Math.Min(operationStartIndex + pageSize, operationNotificationDataCount);
var operationNotificationDataView = new {
Count = operationNotificationDataCount,
UnReadCount = operationNotificationData.Where(it => it.IsRead == 0).Count(),
OperationNotificatioData = operationNotificationData.Skip(operationStartIndex).Take(pageSize).ToList()
};
//任务通知 TaskNotification
List<int> taskTypeList = new List<int>() { 6 };
var taskNotificationData = _readableMsgList.Where(it => taskTypeList.Contains(it.Type)).OrderBy(it => it.IsRead).ToList();
int taskNotificationDataCount = taskNotificationData.Count;
// 计算起始索引和结束索引
int taskStartIndex = (currentPage - 1) * pageSize;
int taskEndIndex = Math.Min(taskStartIndex + pageSize, taskNotificationDataCount);
var taskNotificationDataView = new
{
Count = taskNotificationDataCount,
UnReadCount = taskNotificationData.Where(it => it.IsRead == 0).Count(),
TaskNotificationData = taskNotificationData.Skip(taskStartIndex).Take(pageSize).ToList()
};
var _view = new {
OperationNotification = operationNotificationDataView,
TaskNotification = taskNotificationDataView
};
result.Code = 0;
result.Msg = "成功!";
result.Data = _view;
}
else
{
result.Msg = "暂无该用户的消息!";
}
}
return result;
}
/// <summary>
/// 获取消息类型列表
/// </summary>
/// <param name="uId">可读用户Id</param>
/// <returns></returns>
public async Task<Result> PotsMsgTypeData(MsgTypeDto dto)
{
Result result = new Result() { Code = -1, Msg = "未知错误", Data = null };
//userId
string msgSqlWhere = $" And smra.ReadableUId = {dto.UserId}";
//消息类型
List<NotificationTypeView> messageTypeViews = AppSettingsHelper.Get<NotificationTypeView>("MessageNotificationType");
List<int> _operationTypeList = messageTypeViews.Where(it =>it.TypeId == 1021).FirstOrDefault().MsgTypeIds.ToList();
List<int> _taskTypeList = messageTypeViews.Where(it => it.TypeId == 1020).FirstOrDefault().MsgTypeIds.ToList();
List<int> _noticeTypeList = messageTypeViews.Where(it => it.TypeId == 1022).FirstOrDefault().MsgTypeIds.ToList();
string msgSql = string.Format(@"Select * From(
Select row_number() over(order by sm.ReleaseTime Desc) as RowNumber,
sm.Id,sm.Type,sm.Title,sm.Content,sd.DepName issuerDep,su.CnName issuerUser,
sm.ReleaseTime,smra.ReadableUId,smra.IsRead,sm.DiId,sm.Param
From Sys_Message sm
Inner Join Sys_MessageReadAuth smra On sm.Id = smra.MsgId
Inner Join Sys_Users su On sm.IssuerId = su.Id
Inner Join Sys_Department sd On su.DepId = sd.Id
Inner Join Sys_Users suAuth On smra.ReadableUId = suAuth.Id
Where sm.IsDel = 0
And smra.IsDel = 0 {0}
) Temp", msgSqlWhere);
var data = await _sqlSugar.SqlQueryable<MessageListView>(msgSql).ToListAsync();
if (data.Count > 0)
{
var msgTypeResult = await _setData.GetSetDataBySTId(_setData, 77);
if (msgTypeResult.Code != 0)
{
result.Msg = "消息类型不存在!";
return result;
}
string msgTypeDataStr = JsonConvert.SerializeObject(msgTypeResult.Data);
var msgTypeData = JsonConvert.DeserializeObject<List<MessageTypeView>>(msgTypeDataStr);
//操作通知 OperationNotification
var operationNotificationData = data.Where(it => _operationTypeList.Contains(it.Type)).OrderBy(it => it.IsRead).ToList();
int operationNotificationDataCount = operationNotificationData.Where(it => it.IsRead == 0).ToList().Count;
//任务通知
var taskNotificationData = data.Where(it => _taskTypeList.Contains(it.Type)).OrderBy(it => it.IsRead).ToList();//操作通知所属类型
int taskNotificationDataCount = taskNotificationData.Where(it => it.IsRead == 0).ToList().Count; //任务通知 TaskNotification
//公告通知 notice
var noticeNotificationData = data.Where(it => _noticeTypeList.Contains(it.Type)).OrderBy(it => it.IsRead).ToList();//操作通知所属类型
int noticeNotificationDataCount = noticeNotificationData.Where(it => it.IsRead == 0).ToList().Count; //任务通知 TaskNotification
foreach (var item in msgTypeData)
{
//1021 团组操作通知 1020 任务操作通知 1022 公告通知
if (item.Id == 1020) item.UnReadCount = taskNotificationDataCount;
else if (item.Id == 1021) item.UnReadCount = operationNotificationDataCount;
else if (item.Id == 1022) item.UnReadCount = noticeNotificationDataCount;
}
if (dto.PortType == 1 || dto.PortType == 2) // web/android
{
result.Code = 0;
result.Data = msgTypeData;
}
else if (dto.PortType == 3)
{
result.Code = 0;
result.Data = new {
MsgTypeData = msgTypeData,
FirstUnreadData = data[0],
UnreadTotalCount = data.Where(it => it.IsRead == 0).ToList().Count,
};
}
}
return result;
}
/// <summary>
/// 获取消息未读消息条数
/// </summary>
/// <param name="uId">可读用户Id</param>
/// <returns></returns>
public async Task<int> GetUnReadCount(int userId)
{
int _unReadCount = 0;
string msgSqlWhere = $"Select COUNT(*) As UnreadCount From Sys_MessageReadAuth smra Left Join Sys_Message sm On sm.Id = smra.MsgId Where IsRead = 0 And smra.IsDel = 0 And sm.IsDel = 0 And ReadableUId = {userId}";
var _readableMsg = _sqlSugar.SqlQueryable<MessageUnreadTotalCountView>(msgSqlWhere).First();
_unReadCount = _readableMsg.UnreadCount;
return _unReadCount;
}
/// <summary>
/// 获取消息详细
/// </summary>
/// <param name="dto">msgInfo请求dto</param>
/// <returns></returns>
public async Task<Result> GetMsgInfo(MsgInfoDto dto)
{
Result result = new Result() { Code = -1, Msg = "未知错误", Data = null };
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)
{
string msgSqlWhere = string.Format(@"Select sm.Type,sc.CompanyName,sd.DepName,sjp.JobName,su.CnName,sm.ReleaseTime,
sm.Title,sm.Content,smra.IsRead,smra.ReadTime,sm.DiId,sm.Param
From Sys_Message sm
Inner Join Sys_MessageReadAuth smra On sm.Id = smra.MsgId
Inner Join Sys_Users su On sm.IssuerId = su.id
Inner Join Sys_JobPost sjp On su.JobPostId = sjp.Id
Inner Join Sys_Department sd On su.DepId = sd.Id
Inner Join Sys_Company sc On su.CompanyId = sc.Id
Where sm.IsDel = 0
And sm.Id = {0}
And smra.IsDel = 0
And smra.ReadableUId = {1}", dto.MsgId,dto.UserId);
var _readableMsgInfo = await _sqlSugar.SqlQueryable<MessageInfoView>(msgSqlWhere).FirstAsync();
if (_readableMsgInfo != null)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = _readableMsgInfo;
}
else
{
result.Msg = "暂无该用户的消息!";
}
}
return result;
}
/// <summary>
/// 消息设置已读
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> SetMsgRead(MsgSetReadDto dto)
{
Result result = new Result() { Code = -1, Msg = "未知错误", Data = null };
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)
{
var msgReadStatus = await _sqlSugar.Updateable<Sys_MessageReadAuth>()
.SetColumns(a => new Sys_MessageReadAuth
{
IsRead = 1,
ReadTime = DateTime.Now,
})
.Where(a => a.ReadableUId == dto.UserId && a.MsgId == dto.MsgId)
.ExecuteCommandAsync();
if (msgReadStatus > 0)
{
result.Code = 0;
result.Msg = "成功!";
}
else
{
result.Msg = "失败!";
}
}
return result;
}
/// <summary>
/// 消息设置已读
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> DelMsg(MsgDeleteDto dto)
{
Result result = new Result() { Code = -1, Msg = "未知错误", Data = null };
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)
{
var msgReadStatus = await _sqlSugar.Updateable<Sys_Message>()
.Where(a => a.Id == dto.MsgId)
.SetColumns(a => new Sys_Message
{
IsDel = 1,
DeleteUserId = dto.UserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).ExecuteCommandAsync();
if (msgReadStatus > 0)
{
result.Code = 0;
result.Msg = "成功!";
}
else
{
result.Msg = "操作失败!";
}
}
return result;
}
}
}