public MessageAttachment attachToMessage(MessageAttachment attachment, Message message)
{
try
{
_cxn.beginTransaction();
if (attachment.Id > 0)
{
attachment = updateAttachment(attachment); // could simply rely on updateAttachment function but thought this might add a level of convenience
}
else if (attachment.Id <= 0)
{
// create attachment
attachment = createAttachment(attachment.AttachmentName, attachment.SmFile, attachment.MimeType);
// update message - set attachment ID properties
message = updateMessageAttachmentFields(message, attachment.Id);
}
_cxn.commitTransaction();
return attachment;
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
public Addressee moveMessage(Message message, domain.sm.User user, Folder folder)
{
Addressee addressee = getAddressee(message.Id, user.Id);
checkValidMove(addressee.Folder, folder);
addressee.Folder = folder;
addressee.FolderId = folder.Id;
if (!addressee.Folder.SystemFolder)
{
FolderDao folderDao = new FolderDao(_cxn);
addressee.Folder = folderDao.getUserFolder(user.Id, folder.Id);
}
return moveMessage(addressee);
}
public void deleteDraft(Message message)
{
if (message == null || message.Id <= 0)
{
throw new MdoException("Invalid Message");
}
Message dbMessage = getMessageComplete(message.Id);
if (dbMessage == null || dbMessage.Id <= 0)
{
throw new MdoException("No message found with that ID");
}
if (dbMessage.SentDate.Year > 1900)
{
throw new MdoException("This message is not a valid draft - already sent");
}
if (dbMessage.MessageThread == null || dbMessage.MessageThread.Id <= 0 || dbMessage.Addressees == null ||
dbMessage.Addressees.Count != 1 || dbMessage.Addressees[0] == null || dbMessage.Addressees[0].Id <= 0)
{
throw new MdoException("Data integrity - message thread/addressee appears malformed in database");
}
if (dbMessage.Addressees[0].FolderId != (int)domain.sm.enums.SystemFolderEnum.Drafts)
{
throw new MdoException("You can only delete messages marked as DRAFT");
}
try
{
_cxn.beginTransaction();
AddresseeDao addrDao = new AddresseeDao(_cxn);
// turns out there might be multiple draft messages for a thread - they should all be deleted
domain.sm.Thread msgThread = getMessagesFromThread(dbMessage.MessageThread.Id);
foreach (Message msg in msgThread.Messages)
{
IList<domain.sm.Addressee> allAddressees = addrDao.getAddresseesForMessage(msg.Id);
if (allAddressees != null && allAddressees.Count != 1)
{
throw new MdoException("Data integrity: Invalid draft. This draft message has more than one addressee.");
}
if (msg.SentDate.Year > 1900 || msg.CompletedDate.Year > 1900 ||
allAddressees[0].Owner.Id != dbMessage.Addressees[0].Owner.Id || allAddressees[0].FolderId != (Int32)domain.sm.enums.SystemFolderEnum.Drafts)
{
throw new MdoException("Data integrity: There appears to be multiple messages associated with the thread. The data is inconsistent between them.");
}
addrDao.deleteAddressee(allAddressees[0].Id);
deleteMessage(msg.Id);
}
// see if there were any attachments for this draft message
if (dbMessage.Attachment)
{
AttachmentDao attachDao = new AttachmentDao(_cxn);
attachDao.deleteAttachment(Convert.ToInt32(dbMessage.AttachmentId));
}
deleteThread(dbMessage.MessageThread.Id);
_cxn.commitTransaction();
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
internal Message updateMessageAttachmentFields(Message message, Int32 attachmentId)
{
OracleQuery request = buildUpdateMessageQuery(message, attachmentId);
nonQuery qry = delegate() { return (Int32)request.Command.ExecuteNonQuery(); };
if ((Int32)_cxn.query(request, qry) != 1)
{
throw new MdoException("Failed to update secure message record for attachment");
}
message.Attachment = true;
message.AttachmentId = attachmentId;
message.Oplock++;
return message;
}
internal static Message getMessageFromReader(System.Data.IDataReader rdr, Dictionary<string, bool> columnTable)
{
Message msg = new Message();
if (columnTable["SECURE_MESSAGE_ID"])
{
msg.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SECURE_MESSAGE_ID")));
}
if (columnTable["CLINICIAN_STATUS"])
{
msg.Status = (domain.sm.enums.ClinicianStatusEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("CLINICIAN_STATUS")));
}
if (columnTable["COMPLETED_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("COMPLETED_DATE")))
{
msg.CompletedDate = rdr.GetDateTime(rdr.GetOrdinal("COMPLETED_DATE"));
}
}
if (columnTable["ASSIGNED_TO"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ASSIGNED_TO")))
{
if (msg.AssignedTo == null)
{
msg.AssignedTo = new Clinician();
}
msg.AssignedTo.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ASSIGNED_TO")));
}
}
if (columnTable["CHECKSUM"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("CHECKSUM")))
{
msg.Checksum = rdr.GetString(rdr.GetOrdinal("CHECKSUM"));
}
}
if (columnTable["THREAD_ID"])
{
if (msg.MessageThread == null)
{
msg.MessageThread = new Thread();
}
msg.MessageThread.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("THREAD_ID")));
}
if (columnTable["STATUS_SET_BY"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("STATUS_SET_BY")))
{
if (msg.StatusSetBy == null)
{
msg.StatusSetBy = new Clinician();
}
msg.StatusSetBy.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("STATUS_SET_BY")));
}
}
if (columnTable["SMOPLOCK"])
{
msg.Oplock = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SMOPLOCK")));
}
if (columnTable["ESCALATED"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATED")))
{
msg.EscalatedDate = rdr.GetDateTime(rdr.GetOrdinal("ESCALATED"));
}
}
if (columnTable["BODY"])
{
msg.Body = rdr.GetString(rdr.GetOrdinal("BODY"));
}
if (columnTable["SENT_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("SENT_DATE")))
{
msg.SentDate = rdr.GetDateTime(rdr.GetOrdinal("SENT_DATE"));
}
}
if (columnTable["SENDER_TYPE"])
{
msg.SenderType = (domain.sm.enums.ParticipantTypeEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SENDER_TYPE")));
}
if (columnTable["SENDER_ID"])
{
msg.SenderId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SENDER_ID")));
}
if (columnTable["SENDER_NAME"])
{
msg.SenderName = rdr.GetString(rdr.GetOrdinal("SENDER_NAME"));
}
if (columnTable["RECIPIENT_TYPE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_TYPE")))
{
msg.RecipientType = (domain.sm.enums.ParticipantTypeEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("RECIPIENT_TYPE")));
}
}
if (columnTable["RECIPIENT_ID"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_ID")))
{
msg.RecipientId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("RECIPIENT_ID")));
}
}
if (columnTable["RECIPIENT_NAME"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_NAME")))
{
msg.RecipientName = rdr.GetString(rdr.GetOrdinal("RECIPIENT_NAME"));
}
}
if (columnTable["SENT_DATE_LOCAL"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("SENT_DATE_LOCAL")))
{
msg.SentDateLocal = rdr.GetDateTime(rdr.GetOrdinal("SENT_DATE_LOCAL"));
}
}
if (columnTable["ESCALATION_NOTIFICATION_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATION_NOTIFICATION_DATE")))
{
msg.EscalationNotificationDate = rdr.GetDateTime(rdr.GetOrdinal("ESCALATION_NOTIFICATION_DATE"));
}
}
if (columnTable["ESCALATION_NOTIFICATION_TRIES"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATION_NOTIFICATION_TRIES")))
{
msg.EscalationNotificationTries = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ESCALATION_NOTIFICATION_TRIES")));
}
}
if (columnTable["READ_RECEIPT"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("READ_RECEIPT")))
{
msg.ReadReceipt = rdr.GetString(rdr.GetOrdinal("READ_RECEIPT"));
}
}
if (columnTable["HAS_ATTACHMENT"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("HAS_ATTACHMENT")))
{
msg.Attachment = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("HAS_ATTACHMENT"))) == 1;
}
}
if (columnTable["ATTACHMENT_ID"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ATTACHMENT_ID")))
{
msg.AttachmentId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ATTACHMENT_ID")));
}
}
return msg;
}
public ThreadTO sendNewMessage(string pwd, string threadSubject, Int32 groupId, Int32 messageCategory, Int32 senderId, Int32 recipientId, string messageBody)
{
ThreadTO result = new ThreadTO();
if (String.IsNullOrEmpty(threadSubject))
{
result.fault = new FaultTO("Missing thread subject");
}
else if (messageCategory >= 0 && !Enum.IsDefined(typeof(gov.va.medora.mdo.domain.sm.enums.MessageCategoryTypeEnum), messageCategory))
{
result.fault = new FaultTO("That message category is not defined");
}
else if (senderId <= 0)
{
result.fault = new FaultTO("Missing sender ID");
}
else if (recipientId <= 0)
{
result.fault = new FaultTO("Missing recipient ID");
}
else if (String.IsNullOrEmpty(messageBody))
{
result.fault = new FaultTO("Must supply a message body");
}
if (result.fault != null)
{
return result;
}
try
{
gov.va.medora.mdo.domain.sm.Thread thread = new Thread()
{
MailGroup = new TriageGroup() { Id = groupId },
MessageCategoryType = (mdo.domain.sm.enums.MessageCategoryTypeEnum)messageCategory,
Subject = threadSubject
};
gov.va.medora.mdo.domain.sm.Message message = new Message()
{
Body = messageBody,
Checksum = StringUtils.getMD5Hash(messageBody),
MessageThread = thread,
RecipientId = recipientId,
SenderId = senderId,
SentDate = DateTime.Now
};
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
Message newMsg = dao.sendNewMessage(message);
result = new ThreadTO(newMsg.MessageThread);
result.messages = new MessageTO[] { new MessageTO(newMsg) };
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
public ThreadTO saveDraft(string pwd, Int32 replyingToMessageId, string threadSubject, Int32 messageCategory,
Int32 messageId, Int32 senderId, Int32 recipientId, string messageBody, Int32 messageOplock, Int32 threadOplock)
{
ThreadTO result = new ThreadTO();
if (String.IsNullOrEmpty(pwd))
{
result.fault = new FaultTO("Missing pwd");
}
else if (messageCategory > 0 && !Enum.IsDefined(typeof(gov.va.medora.mdo.domain.sm.enums.MessageCategoryTypeEnum), messageCategory))
{
result.fault = new FaultTO("Invalid message category");
}
else if (String.IsNullOrEmpty(messageBody))
{
result.fault = new FaultTO("Missing message body");
}
else if (messageId > 0 && messageOplock < 0)
{
result.fault = new FaultTO("Invalid message ID/message oplock");
}
else if (senderId <= 0 || recipientId <= 0)
{
result.fault = new FaultTO("Invalid sender/recipient");
}
if (result.fault != null)
{
return result;
}
try
{
Message message = new Message()
{
Body = messageBody,
Checksum = StringUtils.getMD5Hash(messageBody),
Id = messageId,
MessageThread = new Thread(),
RecipientId = recipientId,
SenderId = senderId,
Oplock = messageOplock
};
message.MessageThread.Subject = threadSubject;
if (Enum.IsDefined(typeof(mdo.domain.sm.enums.MessageCategoryTypeEnum), messageCategory))
{
message.MessageThread.MessageCategoryType = (mdo.domain.sm.enums.MessageCategoryTypeEnum)messageCategory;
}
message.MessageThread.Oplock = threadOplock;
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
if (replyingToMessageId > 0)
{
Message replyingToMsg = dao.getMessage(replyingToMessageId);
if (replyingToMsg == null || replyingToMsg.Id <= 0 || replyingToMsg.MessageThread == null || replyingToMsg.MessageThread.Id <= 0)
{
throw new Exception("Invalid reply to message ID");
}
message.MessageThread.Id = replyingToMsg.MessageThread.Id;
}
gov.va.medora.mdo.domain.sm.Message savedDraft = dao.saveDraft(message);
MessageTO msg = new MessageTO(savedDraft);
result = new ThreadTO(savedDraft.MessageThread);
result.messages = new MessageTO[] { msg };
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
public MessageTO deleteDraft(string pwd, Int32 messageId)
{
MessageTO result = new MessageTO();
if (String.IsNullOrEmpty(pwd))
{
result.fault = new FaultTO("Missing pwd");
}
else if (messageId <= 0)
{
result.fault = new FaultTO("Missing message ID");
}
if (result.fault != null)
{
return result;
}
try
{
Message message = new Message() { Id = messageId };
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
dao.deleteDraft(message);
}
message.Addressees = null;
message.MessageThread = null;
message.Body = "OK";
message.Id = -1;
result = new MessageTO(message);
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
internal void addSenderToMessage(domain.sm.User sender, Message message)
{
if (message.Addressees == null)
{
message.Addressees = new List<Addressee>();
}
message.Addressees.Add(
new Addressee()
{
Message = message,
Owner = sender,
Role = domain.sm.enums.AddresseeRoleEnum.SENDER
});
if (message.SentDate.Year > 1900)
{
message.Addressees[message.Addressees.Count - 1].FolderId = (Int32)domain.sm.enums.SystemFolderEnum.Sent;
message.Addressees[message.Addressees.Count - 1].Folder = new Folder()
{
Id = (Int32)domain.sm.enums.SystemFolderEnum.Sent,
Name = Enum.GetName(typeof(domain.sm.enums.SystemFolderEnum), domain.sm.enums.SystemFolderEnum.Sent)
};
}
else
{
message.Addressees[message.Addressees.Count - 1].FolderId = (Int32)domain.sm.enums.SystemFolderEnum.Drafts;
message.Addressees[message.Addressees.Count - 1].Folder = new Folder()
{
Id = (Int32)domain.sm.enums.SystemFolderEnum.Drafts,
Name = Enum.GetName(typeof(domain.sm.enums.SystemFolderEnum), domain.sm.enums.SystemFolderEnum.Drafts)
};
}
}
internal Message prepareReply(Message original, Message reply)
{
if (original == null || original.Id <= 0 || reply == null || reply.SenderId <= 0 || String.IsNullOrEmpty(reply.Body))
{
throw new ArgumentException("Insufficient information to process request");
}
original = getMessageComplete(original.Id);
if (original == null || original.Id <= 0 || original.MessageThread == null || original.MessageThread.Id <= 0)
{
throw new MdoException("No message for that original message ID");
}
reply.MessageThread = original.MessageThread;
domain.sm.User sender = new UserDao(_cxn).getUserById(Convert.ToInt32(reply.SenderId));
if (sender == null)
{
throw new MdoException("No user exists for that user ID");
}
if (sender.ParticipantType == domain.sm.enums.ParticipantTypeEnum.PATIENT)
{
reply = preparePatientReply(original, reply, sender);
}
else if (reply.SenderType == domain.sm.enums.ParticipantTypeEnum.CLINICIAN)
{
reply = prepareProviderReply(original, reply, sender);
}
else
{
throw new NotImplementedException("Currently not allowed to send as " + Enum.GetName(typeof(domain.sm.enums.ParticipantTypeEnum), reply.SenderType));
}
return reply;
}
internal Message prepareProviderReply(Message original, Message reply, domain.sm.User sender)
{
if (sender.ParticipantType == domain.sm.enums.ParticipantTypeEnum.CLINICIAN && original.Status == domain.sm.enums.ClinicianStatusEnum.COMPLETE)
{
throw new MdoException("Message has already been completed");
}
throw new NotImplementedException("Currently only able to reply as a patient");
}
internal Message preparePatientReply(Message original, Message reply, domain.sm.User sender)
{
if (original.MessageThread == null || original.MessageThread.MailGroup == null || original.MessageThread.MailGroup.Id <= 0)
{
throw new MdoException("No message thread or triage group defined");
}
reply.SenderType = sender.ParticipantType;
reply.SenderName = sender.getName();
reply.RecipientId = original.MessageThread.MailGroup.Id;
checkValidMessageRecipientPatient(reply); // One would *think* the above line would validate this but, just in case there was some previous data integrity issue, we should check anyways. The helper function also sets other parameters (group name, etc) so not a wasted call
AddresseeDao addrDao = new AddresseeDao(_cxn);
addrDao.addSenderToMessage(sender, reply);
addrDao.addRecipientsToMessage(Convert.ToInt32(reply.RecipientId), reply);
return reply;
}
/// <summary>
/// Prepare a message for sending. Creates thread if needed. Assigns addressees. Verifies message recipient
/// </summary>
/// <param name="message"></param>
/// <returns></returns>
internal Message prepareMessage(Message message)
{
if (message.MessageThread == null)
{
throw new MdoException(MdoExceptionCode.ARGUMENT_NULL, "No thread defined");
}
if (message.MessageThread.Id <= 0 && String.IsNullOrEmpty(message.MessageThread.Subject))
{
throw new MdoException(MdoExceptionCode.ARGUMENT_NULL, "No message ID or message subject specified");
}
if (message.SenderId <= 0)
{
throw new MdoException(MdoExceptionCode.ARGUMENT_NULL_PATIENT_ID, "No user associated with message");
}
if (message.MessageThread.Id <= 0)
{
message.MessageThread = createThread(message.MessageThread); // the message.MessageThread should have the fields needed to create
}
domain.sm.User sender = new UserDao(_cxn).getUserById(Convert.ToInt32(message.SenderId));
new AddresseeDao(_cxn).addSenderToMessage(sender, message);
message.SenderName = sender.getName();
message.SenderType = sender.ParticipantType;
if (message.SentDate.Year > 1900) // if message is ready to be sent
{
if (sender.UserType == domain.sm.enums.UserTypeEnum.PATIENT) // if user is a patient, make sure recipient is ok
{
return prepareCompletedPatientMessage(message);
}
else if (sender.UserType == domain.sm.enums.UserTypeEnum.CLINICIAN)
{
return prepareCompletedProviderMessage(message);
}
else if (sender.UserType == domain.sm.enums.UserTypeEnum.ADMINISTRATOR)
{
// TBD - do these users have any restrictions???
}
}
else if (message.SentDate.Year < 1900)
{
if (sender.UserType == domain.sm.enums.UserTypeEnum.PATIENT)
{
return prepareIncompletePatientMessage(message);
}
else if (sender.UserType == domain.sm.enums.UserTypeEnum.CLINICIAN)
{
// TODO - implement some code to take care of incomplete messages
}
else if (sender.UserType == domain.sm.enums.UserTypeEnum.ADMINISTRATOR)
{
// TODO - implement some code to take care of incomplete messages
}
}
return message;
}
internal OracleQuery buildUpdateMessageQuery(Message message)
{
string sql = "UPDATE SMS.SECURE_MESSAGE SET OPLOCK = :oplockPlusOne, CLINICIAN_STATUS = :clinicianStatus, COMPLETED_DATE = :completedDate, " +
"ASSIGNED_TO = :assignedTo, CHECKSUM = :checksum, THREAD_ID = :threadId, STATUS_SET_BY = :statusSetBy, " +
"MODIFIED_DATE = :modifiedDate, ESCALATED = :escalated, BODY = :body, SENT_DATE = :sentDate, SENDER_TYPE = :senderType, " +
"SENDER_ID = :senderId, SENDER_NAME = :senderName, RECIPIENT_TYPE = :recipientType, RECIPIENT_ID = :recipientId, " +
"RECIPIENT_NAME = :recipientName, SENT_DATE_LOCAL = :sentDateLocal, ESCALATION_NOTIFICATION_DATE = :escalationNotificationDate, " +
"ESCALATION_NOTIFICATION_TRIES = :escalationNotificationTries, READ_RECEIPT = :readReceipt, HAS_ATTACHMENT = :hasAttachment, " +
"ATTACHMENT_ID = :attachmentId WHERE SECURE_MESSAGE_ID = :secureMessageId AND OPLOCK = :oplock";
OracleQuery query = new OracleQuery();
OracleCommand command = new OracleCommand(sql);
query.Command = command;
// the ordering of these is hokey because the OracleParameters collection needs to have the values bound in the order
// they appear in the SQL statement. Trying to re-use the buildMessageCommand query... could just past here but ok for now
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(message.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
buildMessageCommand(query.Command, message);
// add the id param - not in helper function above
OracleParameter idParam = new OracleParameter("secureMessageId", OracleDbType.Decimal);
idParam.Value = Convert.ToDecimal(message.Id);
query.Command.Parameters.Add(idParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(message.Oplock);
query.Command.Parameters.Add(oplockParam);
return query;
}
internal OracleQuery buildSendMessageCommand(Message message)
{
// SECURE_MESSAGE table messages with SENT_DATE = DBNULL are drafts
string sql = "INSERT INTO SMS.SECURE_MESSAGE (CLINICIAN_STATUS, COMPLETED_DATE, " +
"ASSIGNED_TO, CHECKSUM, THREAD_ID, STATUS_SET_BY, MODIFIED_DATE, " +
"ESCALATED, BODY, SENT_DATE, SENDER_TYPE, SENDER_ID, SENDER_NAME, RECIPIENT_TYPE, " +
"RECIPIENT_ID, RECIPIENT_NAME, SENT_DATE_LOCAL, ESCALATION_NOTIFICATION_DATE, " +
"ESCALATION_NOTIFICATION_TRIES, READ_RECEIPT, HAS_ATTACHMENT, ATTACHMENT_ID) VALUES (" +
":clinicianStatus, :completedDate, :assignedTo, :checksum, :threadId, :statusSetBy, " +
":modifiedDate, :escalated, :body, :sentDate, :senderType, :senderId, " +
":senderName, :recipientType, :recipientId, :recipientName, :sentDateLocal, " +
":escalationNotificationDate, :escalationNotificationTries, :readReceipt, :hasAttachment, :attachmentId) " +
"RETURNING SECURE_MESSAGE_ID INTO :outId";
OracleQuery query = new OracleQuery();
OracleCommand command = new OracleCommand(sql);
query.Command = command;
buildMessageCommand(query.Command, message);
// add out ID - not in helper function
OracleParameter outParam = new OracleParameter("outId", OracleDbType.Decimal);
outParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outParam);
return query;
}
internal OracleCommand buildMessageCommand(OracleCommand command, Message message)
{
OracleParameter clinicianStatusParam = new OracleParameter("clinicianStatus", OracleDbType.Decimal);
clinicianStatusParam.Value = message.Status;
command.Parameters.Add(clinicianStatusParam);
OracleParameter completedDateParam = new OracleParameter("completedDate", OracleDbType.Date);
if (message.CompletedDate != null && message.CompletedDate.Year > 1900)
{
completedDateParam.Value = new Oracle.DataAccess.Types.OracleDate(message.CompletedDate);
}
else
{
completedDateParam.Value = DBNull.Value;
}
command.Parameters.Add(completedDateParam);
OracleParameter assignedToParam = new OracleParameter("assignedTo", OracleDbType.Decimal);
if (message.AssignedTo != null && message.AssignedTo.Id != 0)
{
assignedToParam.Value = Convert.ToDecimal(message.AssignedTo.Id);
}
else
{
assignedToParam.Value = DBNull.Value;
}
command.Parameters.Add(assignedToParam);
OracleParameter checksumParam = new OracleParameter("checksum", OracleDbType.Char, 32);
if (!String.IsNullOrEmpty(message.Checksum))
{
checksumParam.Value = message.Checksum;
}
else if (String.IsNullOrEmpty(message.Checksum) && !String.IsNullOrEmpty(message.Body))
{
checksumParam.Value = gov.va.medora.utils.StringUtils.getMD5Hash(message.Body);
}
else
{
checksumParam.Value = DBNull.Value;
}
command.Parameters.Add(checksumParam);
OracleParameter threadIdParam = new OracleParameter("threadId", OracleDbType.Decimal);
// no longer creating thread here...
// must supply a message thread with a subject and no ID to create a new thread on the fly
//if (message.MessageThread != null && message.MessageThread.Id == 0 && !String.IsNullOrEmpty(message.MessageThread.Subject))
//{
// message.MessageThread = createThread(message.MessageThread); // create a new thread for this message
//}
if (message.MessageThread == null || message.MessageThread.Id <= 0)
{
throw new ArgumentNullException("Must supply a valid message thread for the secure message");
}
threadIdParam.Value = Convert.ToDecimal(message.MessageThread.Id);
command.Parameters.Add(threadIdParam);
OracleParameter statusSetByParam = new OracleParameter("statusSetBy", OracleDbType.Decimal);
if (message.StatusSetBy != null && message.StatusSetBy.Id > 0)
{
statusSetByParam.Value = Convert.ToDecimal(message.StatusSetBy.Id);
}
else
{
statusSetByParam.Value = DBNull.Value;
}
command.Parameters.Add(statusSetByParam);
// TODO - fix created date to use Message.CreatedDate property so we're not updating it every time. Ok now while testing...
//OracleParameter createdDateParam = new OracleParameter("createdDate", OracleDbType.Date);
//createdDateParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
//command.Parameters.Add(createdDateParam);
OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedDateParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
command.Parameters.Add(modifiedDateParam);
OracleParameter escalatedParam = new OracleParameter("escalated", OracleDbType.Date);
if (message.EscalatedDate != null && message.EscalatedDate.Year > 1900)
{
escalatedParam.Value = new Oracle.DataAccess.Types.OracleDate(message.EscalatedDate);
}
else
{
escalatedParam.Value = DBNull.Value;
}
command.Parameters.Add(escalatedParam);
OracleParameter bodyParam = new OracleParameter("body", OracleDbType.Clob);
bodyParam.Value = message.Body;
command.Parameters.Add(bodyParam);
OracleParameter sentDateParam = new OracleParameter("sentDate", OracleDbType.Date);
if (message.SentDate != null && message.SentDate.Year > 1900)
{
sentDateParam.Value = new Oracle.DataAccess.Types.OracleDate(message.SentDate);
}
else
{
sentDateParam.Value = DBNull.Value;
}
command.Parameters.Add(sentDateParam);
OracleParameter senderTypeParam = new OracleParameter("senderType", OracleDbType.Decimal);
senderTypeParam.Value = Convert.ToDecimal((Int32)message.SenderType);
command.Parameters.Add(senderTypeParam);
OracleParameter senderIdParam = new OracleParameter("senderId", OracleDbType.Decimal);
senderIdParam.Value = Convert.ToDecimal(message.SenderId);
command.Parameters.Add(senderIdParam);
OracleParameter senderNameParam = new OracleParameter("senderName", OracleDbType.Varchar2, 100);
senderNameParam.Value = message.SenderName;
command.Parameters.Add(senderNameParam);
OracleParameter recipientTypeParam = new OracleParameter("recipientType", OracleDbType.Decimal);
if (message.RecipientType != null)
{
recipientTypeParam.Value = Convert.ToDecimal((Int32)message.RecipientType);
}
else
{
recipientTypeParam.Value = DBNull.Value;
}
command.Parameters.Add(recipientTypeParam);
OracleParameter recipientIdParam = new OracleParameter("recipientId", OracleDbType.Decimal);
if (message.RecipientId > 0)
{
recipientIdParam.Value = Convert.ToDecimal(message.RecipientId);
}
else
{
recipientIdParam.Value = DBNull.Value;
}
command.Parameters.Add(recipientIdParam);
OracleParameter recipientNameParam = new OracleParameter("recipientName", OracleDbType.Varchar2, 100);
if (!String.IsNullOrEmpty(message.RecipientName))
{
recipientNameParam.Value = message.RecipientName;
}
else
{
recipientNameParam.Value = DBNull.Value;
}
command.Parameters.Add(recipientNameParam);
OracleParameter sentDateLocalParam = new OracleParameter("sentDateLocal", OracleDbType.Date);
if (message.SentDateLocal != null && message.SentDateLocal.Year > 1900)
{
sentDateLocalParam.Value = new Oracle.DataAccess.Types.OracleDate(message.SentDateLocal);
}
else
{
sentDateLocalParam.Value = DBNull.Value;
}
command.Parameters.Add(sentDateLocalParam);
OracleParameter escalationNotificationDateParam = new OracleParameter("escalationNotificationDate", OracleDbType.Date);
if (message.EscalationNotificationDate != null && message.EscalationNotificationDate.Year > 1900)
{
escalationNotificationDateParam.Value = new Oracle.DataAccess.Types.OracleDate(message.EscalationNotificationDate);
}
else
{
escalationNotificationDateParam.Value = DBNull.Value;
}
command.Parameters.Add(escalationNotificationDateParam);
OracleParameter escalationNotificationTriesParam = new OracleParameter("escalationNotificationTries", OracleDbType.Decimal);
if (message.EscalationNotificationTries > 0)
{
escalationNotificationTriesParam.Value = Convert.ToDecimal(message.EscalationNotificationTries);
}
else
{
escalationNotificationTriesParam.Value = DBNull.Value;
}
command.Parameters.Add(escalationNotificationTriesParam);
OracleParameter readReceiptParam = new OracleParameter("readReceipt", OracleDbType.Varchar2, 20);
if (!String.IsNullOrEmpty(message.ReadReceipt))
{
readReceiptParam.Value = message.ReadReceipt;
}
else
{
readReceiptParam.Value = DBNull.Value;
}
command.Parameters.Add(readReceiptParam);
OracleParameter hasAttachmentParam = new OracleParameter("hasAttachment", OracleDbType.Decimal);
hasAttachmentParam.Value = Convert.ToDecimal(message.Attachment ? 1 : 0);
command.Parameters.Add(hasAttachmentParam);
OracleParameter attachmentIdParam = new OracleParameter("attachmentId", OracleDbType.Decimal);
if (message.Attachment && message.AttachmentId > 0)
{
attachmentIdParam.Value = Convert.ToDecimal(message.AttachmentId);
}
else
{
attachmentIdParam.Value = DBNull.Value;
}
command.Parameters.Add(attachmentIdParam);
return command;
}
internal void addRecipientsToMessage(Int32 triageGroupId, Message message)
{
IList<domain.sm.User> groupMembers = new dao.oracle.mhv.sm.UserDao(_cxn).getTriageGroupMembers(triageGroupId);
if (message.Addressees == null)
{
message.Addressees = new List<Addressee>();
}
foreach (domain.sm.User user in groupMembers)
{
message.Addressees.Add(
new Addressee()
{
Folder = new Folder() { Id = (Int32)domain.sm.enums.SystemFolderEnum.Inbox, Name = Enum.GetName(typeof(domain.sm.enums.SystemFolderEnum), domain.sm.enums.SystemFolderEnum.Inbox) },
FolderId = (Int32)domain.sm.enums.SystemFolderEnum.Inbox,
Message = message,
Owner = user,
Role = domain.sm.enums.AddresseeRoleEnum.RECIPIENT
});
}
}
/// <summary>
/// The primary function for writing a message. Writes message and addressees to the database. Verifies recipients and creates thread if 'verify' is set. Also email recipients
/// if Message.SentDate has been set. The first Message.Addressees item should be the sender for correct email notifications. Uses a SQL transaction
/// </summary>
/// <param name="message"></param>
/// <returns></returns>
internal Message sendMessage(Message message, bool verify)
{
try
{
_cxn.beginTransaction();
if (verify)
{
prepareMessage(message);
}
message = createMessage(message);
new AddresseeDao(_cxn).createAddressees(message.Addressees, message.Id);
_cxn.commitTransaction();
if (message.SentDate.Year > 1900 && message.Addressees.Count > 1) // if message was sent, send email notifications to recipients
{
new ThreadedEmailer(_cxn, message.Addressees.GetRange(1, message.Addressees.Count - 1)).emailAllAsync();
}
return message;
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
internal Message toMessageBodyAndChecksum(IDataReader rdr)
{
Message msg = new Message();
if (rdr.Read())
{
msg = Message.getMessageFromReader(rdr);
}
return msg;
}
internal Message toMessageComplete(IDataReader rdr)
{
Message result = new Message();
if (rdr.Read())
{
result = Message.getMessageFromReader(rdr);
Thread t = Thread.getThreadFromReader(rdr);
result.MessageThread = t;
result.Addressees = new List<Addressee>();
Addressee a = Addressee.getAddresseeFromReader(rdr);
result.Addressees.Add(a); // need to add the Addressee from the current IDataReader.Read() before we loop through others
while (rdr.Read())
{
result.Addressees.Add(Addressee.getAddresseeFromReader(rdr));
}
}
return result;
}
public MessageTO moveMessage(string pwd, Int32 userId, Int32 messageId, Int32 newFolderId)
{
MessageTO result = new MessageTO();
try
{
Message message = new Message();
message.Id = messageId;
message.Addressees = new List<Addressee>() { new Addressee() { FolderId = newFolderId, Owner = new User() { Id = userId } } };
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
AddresseeDao dao = new AddresseeDao(cxn);
message.Addressees[0] = dao.moveMessage(new Message() { Id = messageId }, new User() { Id = userId }, new Folder() { Id = newFolderId });
result = new MessageTO(message);
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
public Message saveDraft(Message message)
{
if (message == null || String.IsNullOrEmpty(message.Body) || message.MessageThread == null || message.CompletedDate.Year > 1900)
{
throw new MdoException("Invalid message");
}
if (message.SenderId <= 0)
{
throw new MdoException("Invalid user ID");
}
//if (message.MessageThread != null && message.MessageThread.Id > 0)
//{
// throw new MdoException("Can't save more than on draft message to a thread");
//}
domain.sm.User sender = new UserDao(_cxn).getUserById(Convert.ToInt32(message.SenderId));
if (sender == null)
{
throw new MdoException("No user found with that ID");
}
// is draft new or should we update
if (message.Id > 0)
{
// get message - see if it's ripe for updating
Message dbMessage = getMessageComplete(message.Id);
if (dbMessage == null || dbMessage.Id <= 0 || dbMessage.MessageThread == null || dbMessage.MessageThread.Id <= 0 || dbMessage.Addressees == null ||
dbMessage.Addressees.Count != 1)
{
throw new MdoException("Invalid message ID");
}
if (dbMessage.SenderId != message.SenderId || dbMessage.Addressees[0].Owner.Id != message.SenderId)
{
throw new MdoException("Can't edit another user's messages");
}
if (dbMessage.SentDate.Year > 1900 || dbMessage.CompletedDate.Year > 1900)
{
throw new MdoException("This message has already been sent - not a valid draft");
}
if (dbMessage.Addressees.Count > 1)
{
throw new MdoException("Data integrity - this message has already been addressed to more than one user");
}
// need to copy over important fields before we assign dbMessage to message
dbMessage.Body = message.Body;
dbMessage.Checksum = gov.va.medora.utils.StringUtils.getMD5Hash(message.Body);
dbMessage.Oplock = message.Oplock; // need to copy this over because it came from the client
// if mail group changed
if (message.MessageThread != null && message.MessageThread.MailGroup != null && message.MessageThread.MailGroup.Id > 0 && message.MessageThread.MailGroup.Id != dbMessage.MessageThread.MailGroup.Id)
{
dbMessage.MessageThread.MailGroup = message.MessageThread.MailGroup;
}
// TODO - figure out copying over of message thread properties
dbMessage.MessageThread.MessageCategoryType = message.MessageThread.MessageCategoryType;
dbMessage.MessageThread.Subject = message.MessageThread.Subject;
dbMessage.MessageThread.Oplock = message.MessageThread.Oplock;
if (message.RecipientId > 0 && dbMessage.RecipientId != message.RecipientId)
{
checkValidMessageRecipient(message, sender);
dbMessage.RecipientId = message.RecipientId;
dbMessage.RecipientName = message.RecipientName;
dbMessage.RecipientType = message.RecipientType;
}
message = dbMessage;
try
{
_cxn.beginTransaction();
updateThread(dbMessage.MessageThread);
message = updateMessage(dbMessage);
message.MessageThread = dbMessage.MessageThread;
_cxn.commitTransaction();
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
else
{
if (message.MessageThread == null)
{
throw new MdoException("No thread defined for new draft message");
}
message.SenderId = sender.Id;
message.SenderName = sender.getName();
message.SenderType = sender.ParticipantType;
if (sender.ParticipantType == domain.sm.enums.ParticipantTypeEnum.PATIENT)
{
checkValidMessageRecipientPatient(message);
}
else if (sender.ParticipantType == domain.sm.enums.ParticipantTypeEnum.CLINICIAN)
{
checkValidMessageRecipientProvider(message);
}
try
{
_cxn.beginTransaction();
domain.sm.Thread t = null;
if (message.MessageThread.Id <= 0)
{
t = createThread(message.MessageThread);
}
else
{
t = getThread(message.MessageThread.Id);
if (t == null || t.Id <= 0)
{
throw new MdoException("No thread found for that thread ID");
}
}
message = createMessage(message);
message.MessageThread = t;
AddresseeDao addrDao = new AddresseeDao(_cxn);
addrDao.addSenderToMessage(sender, message);
addrDao.createAddressees(message.Addressees, message.Id);
_cxn.commitTransaction();
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
return message;
}
public MessageTO sendDraft(string pwd, Int32 messageId, Int32 messageOplock)
{
MessageTO result = new MessageTO();
if (String.IsNullOrEmpty(pwd))
{
result.fault = new FaultTO("Missing pwd");
}
else if (messageId <= 0)
{
result.fault = new FaultTO("Must supply a valid message ID");
}
try
{
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
gov.va.medora.mdo.domain.sm.Message msg = new Message() { Id = messageId, Oplock = messageOplock };
result = new MessageTO(dao.sendDraft(msg));
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
public Message sendDraft(Message message)
{
if (message == null || message.Id <= 0)
{
throw new MdoException("Invalid message");
}
Message dbMessage = getMessageComplete(message.Id);
if (dbMessage == null || dbMessage.Id <= 0)
{
throw new MdoException("No message found for that ID");
}
if (dbMessage.SentDate.Year > 1900)
{
throw new MdoException("This message has already been sent");
}
if (dbMessage.Addressees == null || dbMessage.Addressees.Count != 1)
{
throw new MdoException("Data integrity - message appears to be addressed to multiple recipients");
}
// set addressee properties for sender
dbMessage.Addressees[0].Folder = new Folder() { Id = (Int32)domain.sm.enums.SystemFolderEnum.Sent, Name = Enum.GetName(typeof(domain.sm.enums.SystemFolderEnum), domain.sm.enums.SystemFolderEnum.Sent) };
dbMessage.Addressees[0].FolderId = (Int32)domain.sm.enums.SystemFolderEnum.Sent;
dbMessage.Addressees[0].Role = domain.sm.enums.AddresseeRoleEnum.SENDER;
if (message.Addressees != null && message.Addressees[0] != null)
{
dbMessage.Addressees[0].Oplock = message.Addressees[0].Oplock;
}
dbMessage.SentDate = DateTime.Now;
dbMessage.Oplock = message.Oplock; // need to copy this over because it came from the client
// this function should really just update a DRAFT - not also try and update the message first
//dbMessage.Body = message.Body;
//dbMessage.Checksum = gov.va.medora.utils.StringUtils.getMD5Hash(message.Body);
//if (message.MessageThread != null && message.MessageThread.MailGroup != null && message.MessageThread.MailGroup.Id > 0 && message.MessageThread.MailGroup.Id != dbMessage.MessageThread.MailGroup.Id)
//{
// if (message.MessageThread.MessageCategoryType != dbMessage.MessageThread.MessageCategoryType)
// {
// dbMessage.MessageThread.MessageCategoryType = message.MessageThread.MessageCategoryType;
// }
// if (!String.IsNullOrEmpty(message.MessageThread.Subject) && !String.Equals(dbMessage.MessageThread.Subject, message.MessageThread.Subject))
// {
// dbMessage.MessageThread.Subject = message.MessageThread.Subject;
// }
// if (message.MessageThread.MailGroup != null && message.MessageThread.MailGroup.Id > 0 && dbMessage.MessageThread.MailGroup.Id != message.MessageThread.MailGroup.Id)
// {
// dbMessage.MessageThread.MailGroup.Id = message.MessageThread.MailGroup.Id;
// }
// dbMessage.MessageThread.Oplock = message.MessageThread.Oplock;
//}
message = prepareMessage(dbMessage);
message.Addressees.RemoveAt(1); // the prepare message function adds the sender again - we already did that with the call to getMessageComplete. Kinda hokey...
try
{
_cxn.beginTransaction();
// since not trying to update message, nothing should change in thread
//dbMessage.MessageThread = updateThread(dbMessage.MessageThread);
message = updateMessage(dbMessage);
AddresseeDao addrDao = new AddresseeDao(_cxn);
addrDao.updateAddressee(dbMessage.Addressees[0]); // update folder for sender
addrDao.createAddressees(message.Addressees.GetRange(1, message.Addressees.Count - 1), message.Id);
_cxn.commitTransaction();
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
return message;
}
public MessageTO sendReplyMessage(string pwd, Int32 replyingToMessageId, Int32 senderId, Int32 recipientId, string messageBody)
{
MessageTO result = new MessageTO();
if (replyingToMessageId <= 0)
{
result.fault = new FaultTO("Missing reply message ID");
}
else if (senderId <= 0)
{
result.fault = new FaultTO("Missing sender ID");
}
//else if (recipientId <= 0)
//{
// result.fault = new FaultTO("Missing recipient ID");
//}
else if (String.IsNullOrEmpty(messageBody))
{
result.fault = new FaultTO("Must supply a message body");
}
if (result.fault != null)
{
return result;
}
try
{
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource() { ConnectionString = pwd }))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
Message replyingTo = dao.getMessage(replyingToMessageId);
if (replyingTo == null || replyingTo.Id <= 0)
{
throw new Exception("No message found for that ID");
}
Message newReply = new Message()
{
SentDate = DateTime.Now,
SenderId = senderId,
RecipientId = recipientId,
Body = messageBody,
Checksum = StringUtils.getMD5Hash(messageBody),
MessageThread = replyingTo.MessageThread
};
result = new MessageTO(dao.sendReply(replyingTo, newReply));
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return result;
}
/// <summary>
/// Create and send new message. Creates thread if needed. Populates addressees and sends email notifications where applicable
/// </summary>
/// <param name="message"></param>
/// <returns></returns>
public Message sendNewMessage(Message message)
{
return sendMessage(message, true);
}
public ThreadTO saveDraft(string pwd, Int32 replyingToMessageId, string threadSubject, Int32 messageCategory,
Int32 messageId, Int32 senderId, Int32 recipientId, string messageBody, Int32 messageOplock, Int32 threadOplock)
{
ThreadTO result = new ThreadTO();
if (String.IsNullOrEmpty(pwd))
{
result.fault = new FaultTO("Missing pwd");
}
else if (messageCategory > 0 && !Enum.IsDefined(typeof(gov.va.medora.mdo.domain.sm.enums.MessageCategoryTypeEnum), messageCategory))
{
result.fault = new FaultTO("Invalid message category");
}
else if (String.IsNullOrEmpty(messageBody))
{
result.fault = new FaultTO("Missing message body");
}
else if (messageId > 0 && messageOplock < 0)
{
result.fault = new FaultTO("Invalid message ID/message oplock");
}
else if (senderId <= 0 || recipientId <= 0)
{
result.fault = new FaultTO("Invalid sender/recipient");
}
if (result.fault != null)
{
return(result);
}
try
{
Message message = new Message()
{
Body = messageBody,
Checksum = StringUtils.getMD5Hash(messageBody),
Id = messageId,
MessageThread = new Thread(),
RecipientId = recipientId,
SenderId = senderId,
Oplock = messageOplock
};
message.MessageThread.Subject = threadSubject;
if (Enum.IsDefined(typeof(mdo.domain.sm.enums.MessageCategoryTypeEnum), messageCategory))
{
message.MessageThread.MessageCategoryType = (mdo.domain.sm.enums.MessageCategoryTypeEnum)messageCategory;
}
message.MessageThread.Oplock = threadOplock;
using (MdoOracleConnection cxn = new MdoOracleConnection(new mdo.DataSource()
{
ConnectionString = pwd
}))
{
SecureMessageDao dao = new SecureMessageDao(cxn);
if (replyingToMessageId > 0)
{
Message replyingToMsg = dao.getMessage(replyingToMessageId);
if (replyingToMsg == null || replyingToMsg.Id <= 0 || replyingToMsg.MessageThread == null || replyingToMsg.MessageThread.Id <= 0)
{
throw new Exception("Invalid reply to message ID");
}
message.MessageThread.Id = replyingToMsg.MessageThread.Id;
}
gov.va.medora.mdo.domain.sm.Message savedDraft = dao.saveDraft(message);
MessageTO msg = new MessageTO(savedDraft);
result = new ThreadTO(savedDraft.MessageThread);
result.messages = new MessageTO[] { msg };
}
}
catch (Exception exc)
{
result.fault = new FaultTO(exc);
}
return(result);
}
internal OracleQuery buildUpdateMessageQuery(Message message, Int32 attachmentId)
{
string sql = "";
if (attachmentId > 0)
{
sql = "UPDATE SMS.SECURE_MESSAGE SET OPLOCK = :oplockPlusOne, MODIFIED_DATE = :modifiedDate, " +
"HAS_ATTACHMENT = 1, ATTACHMENT_ID = :attachmentId WHERE SECURE_MESSAGE_ID = :secureMessageId AND OPLOCK = :oplock";
}
else
{
sql = "UPDATE SMS.SECURE_MESSAGE SET OPLOCK = :oplockPlusOne, MODIFIED_DATE = :modifiedDate, " +
"HAS_ATTACHMENT = 0, ATTACHMENT_ID = :attachmentId WHERE SECURE_MESSAGE_ID = :secureMessageId AND OPLOCK = :oplock";
}
OracleQuery query = new OracleQuery();
OracleCommand command = new OracleCommand(sql);
query.Command = command;
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(message.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedParam);
OracleParameter attachmentIdParam = new OracleParameter("attachmentId", OracleDbType.Decimal);
if (attachmentId > 0)
{
attachmentIdParam.Value = Convert.ToDecimal(attachmentId);
}
else
{
attachmentIdParam.Value = DBNull.Value;
}
query.Command.Parameters.Add(attachmentIdParam);
OracleParameter idParam = new OracleParameter("secureMessageId", OracleDbType.Decimal);
idParam.Value = Convert.ToDecimal(message.Id);
query.Command.Parameters.Add(idParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(message.Oplock);
query.Command.Parameters.Add(oplockParam);
return query;
}