OA2023/OASystem/OASystem.Infrastructure/Repositories/System/MessageRepository.cs

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; 
        private readonly List<int> _operationTypeList = new List<int>() { 1, 2, 3, 4, 5 }; //操作通知所属类型
        private readonly List<int> _taskTypeList = new List<int>() { 6 };//任务通知 TaskNotification

        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
                };

                int? msgId = await _sqlSugar.Insertable(message).ExecuteReturnIdentityAsync();
                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 = await _sqlSugar.Insertable<Sys_MessageReadAuth>(messageReadAuths).ExecuteCommandAsync();
                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");
           
            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;

                foreach (var item in msgTypeData)
                {
                    //1021	团组操作通知 1020	任务操作通知
                    if (item.Id == 1020) item.UnReadCount = taskNotificationDataCount;
                    else if (item.Id == 1021) item.UnReadCount = operationNotificationDataCount;
                }

                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 = 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 ", userId);
            var _readableMsgList = await _sqlSugar.SqlQueryable<ReadbleMessageView1>(msgSqlWhere).ToListAsync();
            if (_readableMsgList.Count > 0)
            {
                _unReadCount = _readableMsgList.Where(it => it.IsRead == 0).Count();
            }

            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;
        }
    }
}