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