zmrqaj/code/SxajService/Service/Message/MessageDataManager.cs

using Model.Enum;
using Model.Notice;
using ZmajService.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TF.Common.Tools;
using TF.Logger;

namespace ZmajService.Service.Message
{
    class MessageDataManager
    {
        /// <summary>
        /// 将上报的消息写入数据库
        /// </summary>
        /// <param name="models"></param>
        /// <returns></returns>
        public async Task<MessageResultModel> Add(UploadMessageModel models)
        {
            OracleHelper orcl = new OracleHelper();
            if (models == null || models.ReceiveUser == null || models.ReceiveUser.Count == 0)
            {
                return null;
            }
            List<string> sqls = new List<string>();
            List<MessageModel> results = new List<MessageModel>();
            for (int i = 0; i < models.ReceiveUser.Count; i++)
            {
                string id = Guid.NewGuid().ToString("N");
                string sid = models.SourceId;
                ReceiveUser receiveUser = models.ReceiveUser[i];
                if (receiveUser == null || string.IsNullOrEmpty(receiveUser.ReceiveUserId))
                {
                    throw new Exception($"Receice user is null, index is {i}");
                }
                string receiveType = models.ReceiveUserType.ToString();
                if (receiveType == ReceiveUserType.Group.ToString())
                {
                    if (string.IsNullOrEmpty(receiveUser.ReceiveUserId) || string.IsNullOrEmpty(receiveUser.ReceiveCompanyId))
                    {
                        throw new Exception($"If receive type is group, receive user and receive com can not null, index is {i}");
                    }
                }
                string messageType = models.MessageType.ToString();
                string content = models.Content;
                if (string.IsNullOrEmpty(content))
                {
                    throw new Exception($"Content is null, index is {i}");
                }
                string custum = models.Custum;
                string url = models.Url;
                string urlname = models.UrlName;
                int startTime = models.StartTime;
                int stopTime = models.StopTime;
                int blankTime = models.BlankTime;
                int isNoticeApp = models.IsNoticeApp ? 1 : 0;
                string createUser = models.CreateUser;
                int createTime = TimeUtil.Timestamp();
                if (string.IsNullOrEmpty(createUser))
                {
                    throw new Exception($"CreateUser is null, index is {i}");
                }
                string insertSql = $@"insert into sys_msg
                              (id,sourceid, receiveuser,receivecompany, receiveusertype, messagetype, content, custum, url, starttime, stoptime, blanktime, isactive, isnoticeapp, createuser,createtime,urlname)
                            values
                              ('{id}', '{sid}','{receiveUser.ReceiveUserId}', '{receiveUser.ReceiveCompanyId}', '{receiveType}', '{messageType}', '{content}', '{custum}', '{url}', {startTime}, {stopTime}, {blankTime}, 1, {isNoticeApp}, '{createUser}',{createTime},'{urlname}')";
                sqls.Add(insertSql);
                if (models.ReceiveUserType == ReceiveUserType.Company)
                {
                    string companySql = $"select ocode from sys_operators where ossgs = '{receiveUser.ReceiveUserId}'";
                    DataTable comDt = await orcl.Query(companySql);
                    if (comDt.Rows.Count <= 0)
                    {
                        continue;
                    }
                    foreach (DataRow item in comDt.Rows)
                    {
                        string comUid = item["ocode"].ToString();
                        if (string.IsNullOrEmpty(comUid))
                        {
                            continue;
                        }
                        string itemId = Guid.NewGuid().ToString("N");
                        string stateSql = $@"insert into sys_msg_state
                                          (id, sourceid, userid, mid, lasttime, nexttime, isread, iscancel, canceltime, appstate)
                                        values
                                          ('{itemId}', '{sid}', '{comUid}', '{id}', 0, 0, 0, 0, 0, 0)";
                        results.Add(new MessageModel()
                        {
                            Id = itemId,
                        });
                        sqls.Add(stateSql);
                    }
                }
                else if (models.ReceiveUserType == ReceiveUserType.Group)
                {
                    string groupSql = $"select ocode from sys_operators where ossbm='{receiveUser.ReceiveUserId}' and ossgs = '{receiveUser.ReceiveCompanyId}'";
                    DataTable groupDt = await orcl.Query(groupSql);
                    if (groupDt.Rows.Count <= 0)
                    {
                        continue;
                    }
                    foreach (DataRow item in groupDt.Rows)
                    {
                        string groupUid = item["ocode"].ToString();
                        if (string.IsNullOrEmpty(groupUid))
                        {
                            continue;
                        }
                        string itemId = Guid.NewGuid().ToString("N");
                        string stateSql = $@"insert into sys_msg_state
                                          (id, sourceid, userid, mid, lasttime, nexttime, isread, iscancel, canceltime, appstate)
                                        values
                                          ('{itemId}', '{sid}', '{groupUid}', '{id}', 0, 0, 0, 0, 0, 0)";
                        results.Add(new MessageModel()
                        {
                            Id = itemId,
                        });
                        sqls.Add(stateSql);
                    }
                }
                else
                {
                    string itemId = Guid.NewGuid().ToString("N");
                    string stateSql = $@"insert into sys_msg_state
                                          (id, sourceid, userid, mid, lasttime, nexttime, isread, iscancel, canceltime, appstate)
                                        values
                                          ('{itemId}', '{sid}', '{receiveUser.ReceiveUserId}', '{id}', 0, 0, 0, 0, 0, 0)";
                    results.Add(new MessageModel()
                    {
                        Id = itemId,
                    });
                    sqls.Add(stateSql);
                }
            }
            try
            {

                if (await orcl.ExecuteSqlTran(sqls))
                {
                    return new MessageResultModel()
                    {
                        Code = 0,
                        Message = "Success",
                        Data = results,
                    };
                }
                else
                {
                    throw new Exception("Save models to db failed.");
                }
            }
            catch (Exception ex)
            {
                Log.Error($"Save data failed.{ex.Message}", ex);
                throw new Exception($"Save data failed.{ex.Message}");
            }

        }

        /// <summary>
        /// 获取全部待发送的消息
        /// </summary>
        /// <returns></returns>
        public async Task<List<MessageModel>> GetAllMessage()
        {
            try
            {
                int nowTime = TimeUtil.Timestamp();
                string sql = $@"select state.id,
                                      msg.sourceid,
                                      msg.receiveuser,
                                      msg.receivecompany,
                                      msg.receiveusertype,
                                      msg.messagetype,
                                      msg.content,
                                      msg.custum,
                                      msg.url,
                                      msg.starttime,
                                      msg.stoptime,
                                      msg.isactive,
                                      msg.isnoticeapp,
                                      msg.createuser,
                                      msg.blanktime,
                                      msg.createtime,
                                      state.userid,
                                      state.lasttime,
                                      state.nexttime,
                                      state.isread,
                                      state.iscancel,
                                      state.appstate
                                 from sys_msg msg
                                 left join sys_msg_state state
                                   on msg.id = state.mid
                                where msg.starttime <= {nowTime}
                                  and (msg.stoptime >= {nowTime} or msg.stoptime = 0)
                                  and msg.isactive = 1
                                  and msg.blanktime != 0
                                  and state.nexttime <= {nowTime}
                                  and state.iscancel = 0";
                OracleHelper orcl = new OracleHelper();
                DataTable dt = await orcl.Query(sql);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                List<MessageModel> models = new List<MessageModel>();
                foreach (DataRow row in dt.Rows)
                {
                    MessageModel model = new MessageModel()
                    {
                        Id = row["ID"].ToString(),
                        SourceId = row["SOURCEID"].ToString(),
                        ReceiveUserId = row["USERID"].ToString(),
                        ReceiveUserType = Enum.Parse<ReceiveUserType>(row["RECEIVEUSERTYPE"].ToString()),
                        MessageType = Enum.Parse<MessageType>(row["MESSAGETYPE"].ToString()),
                        Content = row["CONTENT"].ToString(),
                        Custum = row["CUSTUM"].ToString(),
                        Url = row["URL"].ToString(),
                        StartTime = Convert.ToInt32(row["STARTTIME"]),
                        StopTime = Convert.ToInt32(row["STOPTIME"]),
                        LastTime = Convert.ToInt32(row["LASTTIME"]),
                        BlankTime = Convert.ToInt32(row["BLANKTIME"]),
                        NextTime = Convert.ToInt32(row["NEXTTIME"]),
                        IsActive = row["ISACTIVE"].ToString() == "1" ? true : false,
                        IsCancel = row["ISCANCEL"].ToString() == "1" ? true : false,
                        IsRead = row["ISREAD"].ToString() == "1" ? true : false,
                        IsNoticeApp = row["ISNOTICEAPP"].ToString() == "1" ? true : false,
                        UserId = row["USERID"].ToString(),
                        CreateUser = row["CREATEUSER"].ToString(),
                        CreateTime = Convert.ToInt32(row["CREATETIME"]),
                        AppState = row["APPSTATE"].ToString() == "1" ? true : false,
                    };
                    models.Add(model);
                }
                return models;
            }
            catch (Exception ex)
            {
                Log.Error($"Get data failed.{ex.Message}", ex);
                return null;
            }
        }

        /// <summary>
        /// 获取登录用户待发送消息
        /// </summary>
        /// <returns></returns>
        public async Task<List<MessageModel>> GetMessageByUid(string userid)
        {
            try
            {
                int nowTime = TimeUtil.Timestamp();
                string sql = $@"select state.id,
                                      msg.sourceid,
                                      msg.receiveuser,
                                      msg.receivecompany,
                                      msg.receiveusertype,
                                      msg.messagetype,
                                      msg.content,
                                      msg.custum,
                                      msg.url,
                                      msg.starttime,
                                      msg.stoptime,
                                      msg.isactive,
                                      msg.isnoticeapp,
                                      msg.createuser,
                                      msg.blanktime,
                                      msg.createtime,
                                      state.userid,
                                      state.lasttime,
                                      state.nexttime,
                                      state.isread,
                                      state.iscancel,
                                      state.appstate
                                 from sys_msg msg
                                 left join sys_msg_state state
                                   on msg.id = state.mid
                                where msg.starttime <= {nowTime}
                                  and (msg.stoptime >= {nowTime} or msg.stoptime = 0)
                                  and msg.isactive = 1
                                  and state.iscancel = 0
                                  and state.nexttime <= {nowTime}
                                  and state.userid = '{userid}'";
                OracleHelper orcl = new OracleHelper();
                DataTable dt = await orcl.Query(sql);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                List<MessageModel> models = new List<MessageModel>();
                foreach (DataRow row in dt.Rows)
                {
                    MessageModel model = new MessageModel()
                    {
                        Id = row["ID"].ToString(),
                        SourceId = row["SOURCEID"].ToString(),
                        ReceiveUserId = row["USERID"].ToString(),
                        ReceiveUserType = Enum.Parse<ReceiveUserType>(row["RECEIVEUSERTYPE"].ToString()),
                        MessageType = Enum.Parse<MessageType>(row["MESSAGETYPE"].ToString()),
                        Content = row["CONTENT"].ToString(),
                        Custum = row["CUSTUM"].ToString(),
                        Url = row["URL"].ToString(),
                        StartTime = Convert.ToInt32(row["STARTTIME"]),
                        StopTime = Convert.ToInt32(row["STOPTIME"]),
                        LastTime = Convert.ToInt32(row["LASTTIME"]),
                        BlankTime = Convert.ToInt32(row["BLANKTIME"]),
                        NextTime = Convert.ToInt32(row["NEXTTIME"]),
                        IsActive = row["ISACTIVE"].ToString() == "1" ? true : false,
                        IsCancel = row["ISCANCEL"].ToString() == "1" ? true : false,
                        IsRead = row["ISREAD"].ToString() == "1" ? true : false,
                        IsNoticeApp = row["ISNOTICEAPP"].ToString() == "1" ? true : false,
                        UserId = row["USERID"].ToString(),
                        CreateUser = row["CREATEUSER"].ToString(),
                        CreateTime = Convert.ToInt32(row["CREATETIME"]),
                        AppState = row["APPSTATE"].ToString() == "1" ? true : false,
                    };
                    models.Add(model);
                }
                return models;
            }
            catch (Exception ex)
            {
                Log.Error($"Get data failed.{ex.Message}", ex);
                return null;
            }
        }

        /// <summary>
        /// 更具消息ID获取消息
        /// </summary>
        /// <returns></returns>
        public async Task<List<MessageModel>> GetMessageByReceiveUser(List<MessageModel> receiveIdList)
        {
            try
            {
                if (receiveIdList == null || receiveIdList.Count == 0)
                {
                    return null;
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("(");
                for (int i = 0; i < receiveIdList.Count; i++)
                {
                    if (i == receiveIdList.Count - 1)
                    {
                        sb.Append($"'{receiveIdList[i].Id}'");
                    }
                    else
                    {
                        sb.Append($"'{receiveIdList[i].Id}',");
                    }
                }
                sb.Append(")");
                int nowTime = TimeUtil.Timestamp() + 3;
                OracleHelper orcl = new OracleHelper();
                List<MessageModel> models = new List<MessageModel>();
                string sql = $@"select state.id,
                                      msg.sourceid,
                                      msg.receiveuser,
                                      msg.receivecompany,
                                      msg.receiveusertype,
                                      msg.messagetype,
                                      msg.content,
                                      msg.custum,
                                      msg.url,
                                      msg.starttime,
                                      msg.stoptime,
                                      msg.isactive,
                                      msg.isnoticeapp,
                                      msg.createuser,
                                      msg.blanktime,
                                      msg.createtime,
                                      state.userid,
                                      state.lasttime,
                                      state.nexttime,
                                      state.isread,
                                      state.iscancel,
                                      state.appstate
                                 from sys_msg msg
                                 left join sys_msg_state state
                                   on msg.id = state.mid
                                where msg.starttime <= {nowTime}
                                  and (msg.stoptime >= {nowTime}  or msg.stoptime = 0)
                                  and msg.isactive = 1
                                  and state.iscancel = 0
                                  and state.id in {sb.ToString()}";
                DataTable dt = await orcl.Query(sql);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                foreach (DataRow row in dt.Rows)
                {
                    MessageModel model = new MessageModel()
                    {
                        Id = row["ID"].ToString(),
                        SourceId = row["SOURCEID"].ToString(),
                        ReceiveUserId = row["USERID"].ToString(),
                        ReceiveUserType = Enum.Parse<ReceiveUserType>(row["RECEIVEUSERTYPE"].ToString()),
                        MessageType = Enum.Parse<MessageType>(row["MESSAGETYPE"].ToString()),
                        Content = row["CONTENT"].ToString(),
                        Custum = row["CUSTUM"].ToString(),
                        Url = row["URL"].ToString(),
                        StartTime = Convert.ToInt32(row["STARTTIME"]),
                        StopTime = Convert.ToInt32(row["STOPTIME"]),
                        LastTime = Convert.ToInt32(row["LASTTIME"]),
                        BlankTime = Convert.ToInt32(row["BLANKTIME"]),
                        NextTime = Convert.ToInt32(row["NEXTTIME"]),
                        IsActive = row["ISACTIVE"].ToString() == "1" ? true : false,
                        IsCancel = row["ISCANCEL"].ToString() == "1" ? true : false,
                        IsRead = row["ISREAD"].ToString() == "1" ? true : false,
                        IsNoticeApp = row["ISNOTICEAPP"].ToString() == "1" ? true : false,
                        UserId = row["USERID"].ToString(),
                        CreateUser = row["CREATEUSER"].ToString(),
                        CreateTime = Convert.ToInt32(row["CREATETIME"]),
                        AppState = row["APPSTATE"].ToString() == "1" ? true : false,
                    };
                    models.Add(model);
                }
                return models;
            }
            catch (Exception ex)
            {
                Log.Error($"Get data failed.{ex.Message}", ex);
                return null;
            }
        }

        /// <summary>
        /// 取消发送某条消息
        /// </summary>
        /// <returns></returns>
        public async Task<bool> Cancel(CancelMeesageModel cancel)
        {
            try
            {
                string messageId = cancel.MessageId;
                string cancelSql = $"update sys_msg_state set iscancel = 1,canceltime = {TimeUtil.Timestamp()} where id = '{messageId}'";
                OracleHelper orcl = new OracleHelper();
                int cout = await orcl.ExecuteNonQuery(cancelSql);
                if (cout > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.Error($"Cancel data failed.{ex.Message}", ex);
                return false;
            }
        }

        /// <summary>
        /// 取消发送某条消息
        /// </summary>
        /// <returns></returns>
        public async Task<bool> Read(ReadMeesageModel read)
        {
            try
            {
                string messageId = read.MessageId;
                string readSql = $"update sys_msg_state set isread = 1 where id = '{messageId}'";
                OracleHelper orcl = new OracleHelper();
                int cout = await orcl.ExecuteNonQuery(readSql);
                if (cout > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.Error($"Read data failed.{ex.Message}", ex);
                return false;
            }
        }

        /// <summary>
        /// 设置消息发送状态
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<bool> SetPublishState(MessageModel model)
        {
            if (model == null)
            {
                return false;
            }
            try
            {
                string updateSql = $@"update sys_msg_state
                                        set lasttime = {model.LastTime},
                                            nexttime = {model.NextTime}
                                      where id = '{model.Id}'";
                OracleHelper orcl = new OracleHelper();
                int cout = await orcl.ExecuteNonQuery(updateSql);
                if (cout > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.Error($"Update publish state data failed.{ex.Message}", ex);
                return false;
            }
        }

        /// <summary>
        /// 设置消息发送状态
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<bool> SetAppPublishState(MessageModel model)
        {
            if (model == null)
            {
                return false;
            }
            try
            {
                string updateSql = $@"update sys_msg_state set appstate = 1 where id = '{model.Id}'";
                OracleHelper orcl = new OracleHelper();
                int cout = await orcl.ExecuteNonQuery(updateSql);
                if (cout > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.Error($"Update app publish state data failed.{ex.Message}", ex);
                return false;
            }
        }
    }
}