internal OracleQuery buildCreateFolderQuery(domain.sm.Folder folder)
{
string sql = "INSERT INTO SMS.FOLDER (USER_ID, FOLDER_NAME) VALUES (:userId, :folderName) RETURNING FOLDER_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = folder.Owner.Id;
query.Command.Parameters.Add(userIdParam);
OracleParameter folderNameParam = new OracleParameter("folderName", OracleDbType.Varchar2, 50);
folderNameParam.Value = folder.Name;
query.Command.Parameters.Add(folderNameParam);
OracleParameter outIdParam = new OracleParameter("outId", OracleDbType.Decimal);
outIdParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outIdParam);
return(query);
}
internal OracleQuery buildUpdateLastEmailNotificationQuery(domain.sm.User user)
{
string sql = "UPDATE SMS.SMS_USER SET LAST_EMAIL_NOTIFICATION = SYSDATE, OPLOCK=:oplockPlusOne WHERE USER_ID=:userId and OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
//OracleParameter lastEmailNotificationParam = new OracleParameter("lastEmailNotification", OracleDbType.Date);
//lastEmailNotificationParam.Value = (Oracle.DataAccess.Types.OracleDate)DateTime.Now;
//query.Command.Parameters.Add(lastEmailNotificationParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = user.Oplock + 1;
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = Convert.ToDecimal(user.Id);
query.Command.Parameters.Add(userIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(user.Oplock);
query.Command.Parameters.Add(oplockParam);
return(query);
}
public void testBuildCreateAddresseeSqlStatementExpectedException()
{
AddresseeDao dao = new AddresseeDao(_cxn);
OracleQuery query = dao.buildCreateAddresseeQuery(new domain.sm.Addressee(), 1);
Assert.Fail("Previous line should have thrown exception");
}
internal OracleQuery buildUpdateFolderQuery(domain.sm.Folder folder)
{
string sql = "UPDATE SMS.FOLDER SET FOLDER_NAME=:folderName, OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate WHERE FOLDER_ID=:folderId and OPLOCK=:oplock AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter folderNameParam = new OracleParameter("folderName", OracleDbType.Varchar2, 50);
folderNameParam.Value = folder.Name;
query.Command.Parameters.Add(folderNameParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = folder.Oplock + 1;
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedParam.Value = new OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedParam);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = folder.Id;
query.Command.Parameters.Add(folderIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = folder.Oplock;
query.Command.Parameters.Add(oplockParam);
return(query);
}
internal OracleQuery buildCreateAttachmentQuery(string attachmentName, byte[] attachment, string mimeType)
{
string sql = "INSERT INTO SMS.MESSAGE_ATTACHMENT (ATTACHMENT_NAME, ATTACHMENT, MIME_TYPE, CREATED_DATE, ACTIVE) VALUES " +
"(:attachmentName, :attachment, :mimeType, SYSTIMESTAMP, 1) RETURNING ATTACHMENT_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter attachmentNameParam = new OracleParameter("attachmentName", OracleDbType.Varchar2, 80);
attachmentNameParam.Value = attachmentName;
query.Command.Parameters.Add(attachmentNameParam);
OracleParameter attachmentParam = new OracleParameter("attachment", OracleDbType.Blob);
attachmentParam.Value = attachment;
query.Command.Parameters.Add(attachmentParam);
OracleParameter mimeTypeParam = new OracleParameter("mimeType", OracleDbType.Varchar2, 100);
mimeTypeParam.Value = mimeType;
query.Command.Parameters.Add(mimeTypeParam);
OracleParameter outIdParam = new OracleParameter("outId", OracleDbType.Decimal);
outIdParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outIdParam);
return(query);
}
public Message deleteAttachmentFromMessage(Int32 messageId)
{
SecureMessageDao smDao = new SecureMessageDao(_cxn);
Message dbMsg = smDao.getMessage(messageId);
if (dbMsg == null || dbMsg.Id <= 0 || !dbMsg.Attachment || dbMsg.AttachmentId <= 0)
{
throw new MdoException("Not a valid message ID");
}
try
{
_cxn.beginTransaction();
OracleQuery request = buildUpdateMessageQuery(dbMsg, -1);
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");
}
deleteAttachment(Convert.ToInt32(dbMsg.AttachmentId));
dbMsg.AttachmentId = 0;
dbMsg.Attachment = false;
dbMsg.Oplock++;
_cxn.commitTransaction();
return(dbMsg);
}
catch (Exception)
{
_cxn.rollbackTransaction();
throw;
}
}
public void testGetTriageGroupMemberIdsSqlStatement()
{
UserDao dao = new UserDao(_cxn);
OracleQuery query = dao.buildGetTriageGroupMembersQuery(1);
Assert.IsTrue(String.Equals(query.Command.CommandText, "SELECT CTM.USER_ID, USR.EMAIL_ADDRESS, USR.OPLOCK, USR.EMAIL_NOTIFICATION, USR.LAST_EMAIL_NOTIFICATION FROM SMS.CLINICIAN_TRIAGE_MAP CTM JOIN SMS.SMS_USER USR ON CTM.USER_ID=USR.USER_ID WHERE CTM.TRIAGE_GROUP_ID=:groupId AND CTM.ACTIVE=1"));
}
public void testGetUserByIcnSqlStatement()
{
UserDao dao = new UserDao(_cxn);
OracleQuery query = dao.buildGetUserByIcnQuery("1");
Assert.IsTrue(String.Equals(query.Command.CommandText, "SELECT USER_ID, FIRST_NAME, LAST_NAME, USER_TYPE, STATUS, EMAIL_ADDRESS, OPLOCK, ACTIVE, DOB, ICN, SSN, STATION_NO, DUZ, EMAIL_NOTIFICATION, DEFAULT_MESSAGE_FILTER, LAST_EMAIL_NOTIFICATION, NSSN, PROVIDER, EXTERNAL_USER_NAME FROM SMS.sms_user WHERE ICN = :icn"));
}
public void testGetValidRecipientsForPatientSqlStatement()
{
UserDao dao = new UserDao(_cxn);
OracleQuery query = dao.buildGetValidRecipientsForPatientQuery(1);
Assert.IsTrue(String.Equals(query.Command.CommandText, "SELECT TG.TRIAGE_GROUP_ID, TG.TRIAGE_GROUP_NAME, TG.DESCRIPTION FROM SMS.PATIENT_TRIAGE_MAP PTM JOIN SMS.TRIAGE_RELATION TR ON PTM.RELATION_ID=TR.RELATION_ID JOIN SMS.TRIAGE_GROUP TG ON TG.TRIAGE_GROUP_ID=TR.TRIAGE_GROUP_ID WHERE PTM.USER_ID = :userId AND PTM.ACTIVE=1"));
}
internal OracleQuery buildUpdateAddresseeQuery(Addressee addressee)
{
string sql = "UPDATE SMS.ADDRESSEE SET OPLOCK=:oplockPlusOne, MODIFIED_DATE=SYSDATE, FOLDER_ID=:folderId, READ_DATE=:readDate, " +
"REMINDER_DATE=:reminderDate WHERE ADDRESSEE_ID=:addresseeId AND OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(addressee.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
//OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
//modifiedDateParam.Value = new OracleDate(DateTime.Now);
//query.Command.Parameters.Add(modifiedDateParam);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = Convert.ToDecimal(addressee.FolderId);
query.Command.Parameters.Add(folderIdParam);
OracleParameter readDateParam = new OracleParameter("readDate", OracleDbType.Date);
if (addressee.ReadDate.Year > 1900)
{
readDateParam.Value = new OracleDate(addressee.ReadDate);
}
else
{
readDateParam.Value = DBNull.Value;
}
query.Command.Parameters.Add(readDateParam);
OracleParameter reminderDateParam = new OracleParameter("reminderDate", OracleDbType.Date);
if (addressee.ReminderDate.Year > 1900)
{
reminderDateParam.Value = new OracleDate(addressee.ReminderDate);
}
else
{
reminderDateParam.Value = DBNull.Value;
}
query.Command.Parameters.Add(reminderDateParam);
OracleParameter addresseeIdParam = new OracleParameter("addresseeId", OracleDbType.Decimal);
addresseeIdParam.Value = Convert.ToDecimal(addressee.Id);
query.Command.Parameters.Add(addresseeIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(addressee.Oplock);
query.Command.Parameters.Add(oplockParam);
return(query);
}
public domain.sm.MessageAttachment getAttachment(Int32 attachmentId)
{
OracleQuery request = buildGetAttachmentQuery(attachmentId);
reader rdr = delegate() { return((OracleDataReader)request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, rdr);
return(toAttachment(response));
}
public IList <Clinician> getUsersSurrogates(Int32 userId)
{
OracleQuery query = buildGetUsersSurrogatesQuery(userId);
reader executeReader = delegate() { return(query.Command.ExecuteReader()); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
return(toSurrogates(reader));
}
internal Addressee getAddressee(int messageId, int userId)
{
OracleQuery request = buildGetAddresseeForMessageQuery(messageId, userId);
reader requestRdr = delegate() { return(request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, requestRdr);
return(toAddressee(response));
}
internal IList <domain.sm.User> getTriageGroupMembers(Int32 groupId)
{
OracleQuery query = buildGetTriageGroupMembersQuery(groupId);
reader executeReader = delegate() { return(query.Command.ExecuteReader()); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
return(toTriageGroupMembers(reader));
}
internal IList <TriageGroup> getValidRecipientsForPatient(Int32 userId)
{
OracleQuery query = buildGetValidRecipientsForPatientQuery(userId);
reader executeReader = delegate() { return(query.Command.ExecuteReader()); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
return(toTriageGroupsFromReader(reader));
}
public domain.sm.Folder getFolder(Int32 folderId)
{
OracleQuery request = buildGetFolderQuery(folderId);
reader rdr = delegate() { return(request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, rdr);
return(toFolder(response));
}
public domain.sm.User getUserById(Int32 userId)
{
OracleQuery query = buildGetUserByIdQuery(userId);
reader executeReader = delegate() { return(query.Command.ExecuteReader()); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
return(toUserFromDataReader(reader));
}
public domain.sm.User getUserByIcn(string icn)
{
OracleQuery query = buildGetUserByIcnQuery(icn);
reader executeReader = delegate() { return(query.Command.ExecuteReader()); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
return(toUserFromDataReader(reader));
}
public Addressee getAddressee(Int32 addresseeId)
{
OracleQuery request = buildGetAddresseeQuery(addresseeId);
reader requestRdr = delegate() { return(request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, requestRdr);
return(toAddressee(response));
}
internal IList <domain.sm.Folder> getUserFolders(Int32 userId)
{
OracleQuery request = buildGetUserFoldersQuery(userId);
reader rdr = delegate() { return(request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, rdr);
return(toFolders(response));
}
public IList <Addressee> getAddresseesForMessage(Int32 messageId)
{
OracleQuery request = buildGetAddresseesForMessageQuery(messageId);
reader requestRdr = delegate() { return(request.Command.ExecuteReader()); };
OracleDataReader response = (OracleDataReader)_cxn.query(request, requestRdr);
return(toAddressees(response));
}
protected void SavePPL_Click(object sender, EventArgs e)
{
string poscode = poscodeLabel.Text;
string Name = NameLabel.Text;
string posname = posnameLabel.Text;
string unitname = unitnameLabel.Text;
string company_id = companyLabel.Text;
string MOBILE = TelLabel.Text;
string group_in = GroupIDINLabel.Text;
string unitcode = unitcodeLabel.Text;
string code = codeLabel.Text;
OracleQuery cc = new OracleQuery();
OracleQuery2 cc2 = new OracleQuery2();
var s = cl.Items.Cast <ListItem>()
.Where(item => item.Selected)
.Aggregate("", (current, item) => current + (item.Text + ", "));
string hh = s.TrimEnd(new[] { ',', ' ' });
if (hh == "")
{
NoResult.Visible = true;
errorlabel.Text = "กรุณาเลือกกลุ่มอย่างน้อย 1 กลุ่ม";
return;
}
DataTable dt_tempChk = ViewState["ALLPPL"] as DataTable;
dt_tempChk.DefaultView.RowFilter = "[code] = '" + code + "'";
DataTable dtOutput = dt_tempChk.DefaultView.ToTable();
if (dtOutput.Rows.Count > 0)
{
SavePPL.Enabled = false;
NoResult.Visible = true;
errorlabel.Text = "มีชื่อในระบบ";
resultppl.Visible = false;
return;
}
int index = cl.Items.Count;
string loginName = Session["ID"].ToString();
for (int i = 0; i < index; i++)
{
string id = cl.Items[i].Value;
if (cl.Items[i].Selected)
{
cc2.InsertPTTBService(id, code);
}
}
string result = cc2.InsertPTTBPPL(poscode, MOBILE, loginName, unitcode, code);
if (result != "0")
{
cc2.UpdatePTTBPPL(code, MOBILE, loginName);
}
grpPanel.Visible = false;
AddPPLPanel.Visible = false;
BindPPLDetail();
}
internal MessageActivity createMessageActivity(MessageActivity activity)
{
OracleQuery query = buildCreateMessageActivityQuery(activity);
nonQuery insertQuery = delegate() { return(query.Command.ExecuteNonQuery()); };
_cxn.query(query, insertQuery);
activity.Id = ((Oracle.DataAccess.Types.OracleDecimal)query.Command.Parameters["outId"].Value).ToInt32();
return(activity);
}
internal Addressee createAddressee(Addressee addressee, Int32 messageId)
{
OracleQuery query = buildCreateAddresseeQuery(addressee, messageId);
nonQuery insertQuery = delegate() { return(query.Command.ExecuteNonQuery()); };
_cxn.query(query, insertQuery);
addressee.Id = ((Oracle.DataAccess.Types.OracleDecimal)query.Command.Parameters["outId"].Value).ToInt32();
return(addressee);
}
/// <summary>
/// Obtiene bloqueId, y barcode, no procesa informacion de inspeccion
/// </summary>
/// <returns></returns>
private List <Bloque> GetBloquesFromOracle()
{
List <Bloque> list = new List <Bloque>();
if (pcbInfo.bloques == 1)
{
string blockId = "1";
List <int> posibleBlockId = detailList.Select(o => o.bloqueId).Distinct().ToList();
if (posibleBlockId.Count > 0)
{
blockId = posibleBlockId.First().ToString();
}
Bloque b = new Bloque(barcode);
b.bloqueId = int.Parse(blockId);
list.Add(b);
}
else
{
if (pcbInfo.bloques > 1)
{
string query = OracleQuery.ListBlockBarcode(barcode);
DataTable dt = _oracle.Query(query);
int totalRows = dt.Rows.Count;
if (totalRows > 0)
{
#region CREATE_BLOCKBARCODE_OBJECT
foreach (DataRow r in dt.Rows)
{
Bloque b = new Bloque(r["block_barcode"].ToString());
b.bloqueId = int.Parse(r["bloque"].ToString());
list.Add(b);
}
#endregion
}
}
}
// Encontre barcodes con etiqueta en los bloques?!
// Si no encontre... genero bloques virtuales
if (list.Count == 0)
{
#region CREATE_BLOCKBARCODE_OBJECT
for (int i = 1; i <= pcbInfo.bloques; i++)
{
Bloque b = new Bloque(barcode + "-" + i);
b.bloqueId = i;
list.Add(b);
}
#endregion
}
return(list);
}
public void testBuildCreateThreadQuerySqlStatement()
{
SecureMessageDao dao = new SecureMessageDao(_cxn);
domain.sm.Thread thread = new domain.sm.Thread();
thread.MailGroup = new domain.sm.TriageGroup();
OracleQuery query = dao.buildCreateThreadQuery(thread);
Assert.IsTrue(String.Equals(query.Command.CommandText, "INSERT INTO SMS.MESSAGE_THREAD (SUBJECT, TRIAGE_GROUP_ID, CREATED_DATE, MODIFIED_DATE, CATEGORY_TYPE) VALUES (:subject, :triageGroupId, :createdDate, :modifiedDate, :categoryType) RETURNING THREAD_ID INTO :outId"));
}
public void testBuildCreateAddresseeSqlStatement()
{
AddresseeDao dao = new AddresseeDao(_cxn);
OracleQuery query = dao.buildCreateAddresseeQuery(new domain.sm.Addressee()
{
Folder = new domain.sm.Folder(), Owner = new domain.sm.User()
}, 1);
Assert.IsTrue(String.Equals(query.Command.CommandText, "INSERT INTO SMS.ADDRESSEE (ADDRESSEE_ROLE, SECURE_MESSAGE_ID, USER_ID, FOLDER_ID) VALUES (:addresseeRole, :smId, :userId, :folderId) RETURNING ADDRESSEE_ID INTO :outId"));
}
public void deleteAttachment(Int32 attachmentId)
{
OracleQuery request = buildDeleteAttachmentQuery(attachmentId);
nonQuery qry = delegate() { return((Int32)request.Command.ExecuteNonQuery()); };
if ((Int32)_cxn.query(request, qry) != 1)
{
throw new MdoException("Unable to delete message attachment");
}
}
protected void SavePPL_Click(object sender, EventArgs e)
{
// string EmployeeID = EmployeeIDLabel.Text;
string Name = NameLabel.Text;
string posname = posnameLabel.Text;
// string unitname = unitnameLabel.Text;
string company_id = unitnameList.SelectedValue;
string MOBILE = TelLabel.Text;
string group_in = GroupIDINLabel.Text;
OracleQuery cc = new OracleQuery();
OracleQuery2 cc2 = new OracleQuery2();
var s = cl.Items.Cast <ListItem>()
.Where(item => item.Selected)
.Aggregate("", (current, item) => current + (item.Text + ", "));
string hh = s.TrimEnd(new[] { ',', ' ' });
if (hh == "")
{
NoResult.Visible = true;
errorlabel.Text = "กรุณาเลือกกลุ่มอย่างน้อย 1 กลุ่ม";
return;
}
DataTable dt_tempChk = ViewState["ALLPPL"] as DataTable;
//dt_tempChk.DefaultView.RowFilter = "[EMPLOYEE_ID] = '" + EmployeeID + "'";
//DataTable dtOutput = dt_tempChk.DefaultView.ToTable();
//if (dtOutput.Rows.Count > 0)
//{
// SavePPL.Enabled = false;
// NoResult.Visible = true;
// errorlabel.Text = "มีชื่อในระบบ";
// resultppl.Visible = false;
// return;
//}
DataView dv = dt_tempChk.DefaultView;
dv.Sort = "EMPLOYEE_ID desc";
DataTable sortedDT = dv.ToTable();
string EmployeeID = (Convert.ToDouble(sortedDT.Rows[0]["EMPLOYEE_ID"].ToString()) + 1).ToString();
int index = cl.Items.Count;
string loginName = Session["ID"].ToString();
cc2.InsertGCPPL(EmployeeID, Name, company_id, MOBILE, posname, loginName);
for (int i = 0; i < index; i++)
{
string id = cl.Items[i].Value;
if (cl.Items[i].Selected)
{
cc2.InsertGCService(id, EmployeeID, company_id);
}
}
grpPanel.Visible = false;
AddPPLPanel.Visible = false;
BindPPLDetail();
}
public void testBuildReadMessageSqlStatementWithDate()
{
AddresseeDao dao = new AddresseeDao(_cxn);
OracleQuery query = dao.buildReadMessageRequest(new domain.sm.Addressee()
{
ReadDate = DateTime.Now
});
Assert.IsTrue(String.Equals(query.Command.CommandText, "UPDATE SMS.ADDRESSEE SET READ_DATE=:readDate, OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate WHERE ADDRESSEE_ID=:addresseeId AND OPLOCK=:oplock RETURNING SECURE_MESSAGE_ID INTO :outId"));
Assert.IsTrue(((Oracle.DataAccess.Types.OracleDate)query.Command.Parameters["readDate"].Value).Year > 1900, "The read date should be set");
}
internal OracleQuery buildDeleteAddresseeQuery(Int32 addresseeId)
{
string sql = "DELETE FROM SMS.ADDRESSEE WHERE ADDRESSEE_ID=:addresseeId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter addresseeIdParam = new OracleParameter("addresseeId", OracleDbType.Decimal);
addresseeIdParam.Value = Convert.ToDecimal(addresseeId);
query.Command.Parameters.Add(addresseeIdParam);
return query;
}
internal OracleQuery buildCreateMessageQuery(domain.sm.Message message)
{
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 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 buildGetMessageCompleteQuery(Int32 messageId)
{
string sql = "SELECT SM.SECURE_MESSAGE_ID, SM.CLINICIAN_STATUS, SM.COMPLETED_DATE, SM.ASSIGNED_TO, SM.OPLOCK AS SMOPLOCK, SM.ESCALATED, SM.SENT_DATE, SM.SENDER_TYPE, " +
"SM.SENDER_ID, SM.SENDER_NAME, SM.RECIPIENT_ID, SM.RECIPIENT_TYPE, SM.RECIPIENT_ID, SM.RECIPIENT_NAME, SM.HAS_ATTACHMENT, SM.ATTACHMENT_ID, " +
"MT.THREAD_ID, MT.SUBJECT, MT.TRIAGE_GROUP_ID, MT.OPLOCK AS MTOPLOCK, MT.CATEGORY_TYPE, " +
"ADDR.ADDRESSEE_ID, ADDR.ADDRESSEE_ROLE, ADDR.USER_ID, ADDR.OPLOCK AS ADDROPLOCK, ADDR.FOLDER_ID, ADDR.READ_DATE, ADDR.REMINDER_DATE FROM SMS.SECURE_MESSAGE SM JOIN SMS.MESSAGE_THREAD MT ON " +
"SM.THREAD_ID=MT.THREAD_ID RIGHT JOIN SMS.ADDRESSEE ADDR ON SM.SECURE_MESSAGE_ID=ADDR.SECURE_MESSAGE_ID WHERE SM.SECURE_MESSAGE_ID=:messageId AND SM.ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter messageIdParam = new OracleParameter("messageId", OracleDbType.Decimal);
messageIdParam.Value = messageId;
query.Command.Parameters.Add(messageIdParam);
return query;
}
internal OracleQuery buildDeleteMessageQuery(Int32 messageId, bool inactivate)
{
string sql = "DELETE FROM SMS.SECURE_MESSAGE WHERE SECURE_MESSAGE_ID=:secureMessageId";
if (inactivate)
{
// TBD - which is the correct way? delete the record or set to inactive?
sql = "UPDATE SMS.SECURE_MESSAGE SET ACTIVE=0 WHERE SECURE_MESSAGE_ID=:secureMessageId";
}
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter msgIdParam = new OracleParameter("secureMessageId", OracleDbType.Decimal);
msgIdParam.Value = Convert.ToDecimal(messageId);
query.Command.Parameters.Add(msgIdParam);
return query;
}
// GOOD TO GO
internal OracleQuery buildGetValidRecipientsForPatientQuery(Int32 userId)
{
string sql = "SELECT TG.TRIAGE_GROUP_ID, TG.TRIAGE_GROUP_NAME, TG.DESCRIPTION " +
"FROM SMS.PATIENT_TRIAGE_MAP PTM JOIN SMS.TRIAGE_RELATION TR " +
"ON PTM.RELATION_ID=TR.RELATION_ID " +
"JOIN SMS.TRIAGE_GROUP TG ON TG.TRIAGE_GROUP_ID=TR.TRIAGE_GROUP_ID " +
"WHERE PTM.USER_ID = :userId AND PTM.ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = userId;
query.Command.Parameters.Add(userIdParam);
return query;
}
internal OracleQuery buildUpdateLastEmailNotificationQuery(domain.sm.User user)
{
string sql = "UPDATE SMS.SMS_USER SET LAST_EMAIL_NOTIFICATION=:lastEmailNotification, OPLOCK=:oplockPlusOne WHERE USER_ID=:userId and OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter lastEmailNotificationParam = new OracleParameter("lastEmailNotification", OracleDbType.Date);
lastEmailNotificationParam.Value = (Oracle.DataAccess.Types.OracleDate)DateTime.Now;
query.Command.Parameters.Add(lastEmailNotificationParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = user.Oplock + 1;
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = Convert.ToDecimal(user.Id);
query.Command.Parameters.Add(userIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(user.Oplock);
query.Command.Parameters.Add(oplockParam);
return query;
}
internal OracleQuery buildGetUserFoldersQuery(Int32 userId)
{
string sql = "SELECT FOLDER_ID, FOLDER_NAME, OPLOCK AS FOLDOPLOCK FROM SMS.FOLDER WHERE USER_ID=:userId AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = Convert.ToDecimal(userId);
query.Command.Parameters.Add(userIdParam);
return query;
}
internal OracleQuery buildGetFolderQuery(Int32 folderId)
{
string sql = "SELECT FOLDER_ID, USER_ID, FOLDER_NAME, OPLOCK AS FOLDOPLOCK FROM SMS.FOLDER WHERE FOLDER_ID=:folderId AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = folderId;
query.Command.Parameters.Add(folderIdParam);
return query;
}
internal OracleQuery buildUpdateAddresseeQuery(Addressee addressee)
{
string sql = "UPDATE SMS.ADDRESSEE SET OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate, FOLDER_ID=:folderId, READ_DATE=:readDate, " +
"REMINDER_DATE=:reminderDate WHERE ADDRESSEE_ID=:addresseeId AND OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(addressee.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedDateParam.Value = new OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedDateParam);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = Convert.ToDecimal(addressee.FolderId);
query.Command.Parameters.Add(folderIdParam);
OracleParameter readDateParam = new OracleParameter("readDate", OracleDbType.Date);
if (addressee.ReadDate.Year > 1900)
{
readDateParam.Value = new OracleDate(addressee.ReadDate);
}
else
{
readDateParam.Value = DBNull.Value;
}
query.Command.Parameters.Add(readDateParam);
OracleParameter reminderDateParam = new OracleParameter("reminderDate", OracleDbType.Date);
if (addressee.ReminderDate.Year > 1900)
{
reminderDateParam.Value = new OracleDate(addressee.ReminderDate);
}
else
{
reminderDateParam.Value = DBNull.Value;
}
query.Command.Parameters.Add(reminderDateParam);
OracleParameter addresseeIdParam = new OracleParameter("addresseeId", OracleDbType.Decimal);
addresseeIdParam.Value = Convert.ToDecimal(addressee.Id);
query.Command.Parameters.Add(addresseeIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(addressee.Oplock);
query.Command.Parameters.Add(oplockParam);
return query;
}
/// <summary>
/// Fetch a VADIR formatted MOS report for a patient given the EDIPI or Name, SSN and DOB
/// </summary>
/// <param name="patient"></param>
/// <returns>TextReport with report text set to VADIR report</returns>
public TextReport getMOSReport(Patient patient)
{
if (!isValidMosPatient(patient))
{
throw new ArgumentException("Invalid patient. Need name, SSN and DOB or EDIPI");
}
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand();
query.Command.CommandText = "BLUE_BUTTON.FETCHREPORT";
query.Command.CommandType = System.Data.CommandType.StoredProcedure;
//OracleParameter idParam = new System.Data.OracleClient.OracleParameter("VA_ID_IN", OracleType.Number);
OracleParameter idParam = new OracleParameter("VA_ID_IN", OracleDbType.Decimal);
idParam.Direction = System.Data.ParameterDirection.Input;
idParam.Value = Convert.ToDecimal(patient.EDIPI);
query.Command.Parameters.Add(idParam);
//OracleParameter lNameParam = new System.Data.OracleClient.OracleParameter("LNAME_IN", OracleType.VarChar, 26);
OracleParameter lNameParam = new OracleParameter("LNAME_IN", OracleDbType.Varchar2, 26);
lNameParam.Direction = System.Data.ParameterDirection.Input;
lNameParam.Value = "";
query.Command.Parameters.Add(lNameParam);
//OracleParameter ssnParam = new System.Data.OracleClient.OracleParameter("SSN_IN", OracleType.VarChar, 9);
OracleParameter ssnParam = new OracleParameter("SSN_IN", OracleDbType.Varchar2, 9);
ssnParam.Direction = System.Data.ParameterDirection.Input;
ssnParam.Value = "";
query.Command.Parameters.Add(ssnParam);
//OracleParameter dobParam = new System.Data.OracleClient.OracleParameter("DOB_IN", OracleType.DateTime);
OracleParameter dobParam = new OracleParameter("DOB_IN", OracleDbType.Date);
dobParam.Direction = System.Data.ParameterDirection.Input;
dobParam.Value = DBNull.Value;
query.Command.Parameters.Add(dobParam);
//OracleParameter returnParam = new OracleParameter("v_Return", OracleType.Clob);
OracleParameter returnParam = new OracleParameter("v_Return", OracleDbType.Clob);
returnParam.Direction = System.Data.ParameterDirection.ReturnValue;
query.Command.Parameters.Add(returnParam);
using (_cxn)
{
_cxn.connect();
executeReader executeReader = delegate() { return query.Command.ExecuteReader(); };
OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
if (query.Command.Parameters["v_Return"] == null || query.Command.Parameters["v_Return"].Value == DBNull.Value)
{
return null;
}
string text = ((Oracle.DataAccess.Types.OracleClob)query.Command.Parameters["v_Return"].Value).Value.ToString();
return new TextReport() { Text = text };
}
}
internal OracleQuery buildReadMessageRequest(Addressee addressee)
{
string sql = "UPDATE SMS.ADDRESSEE SET READ_DATE=:readDate, OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate " +
"WHERE ADDRESSEE_ID=:addresseeId AND OPLOCK=:oplock RETURNING SECURE_MESSAGE_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter readDateParam = new OracleParameter("readDate", OracleDbType.Date);
readDateParam.Value = new OracleDate(addressee.ReadDate = DateTime.Now);
query.Command.Parameters.Add(readDateParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(addressee.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedDateParam.Value = new OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedDateParam);
OracleParameter addresseeIdParam = new OracleParameter("addresseeId", OracleDbType.Decimal);
addresseeIdParam.Value = Convert.ToDecimal(addressee.Id);
query.Command.Parameters.Add(addresseeIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(addressee.Oplock);
query.Command.Parameters.Add(oplockParam);
OracleParameter outParam = new OracleParameter("outId", OracleDbType.Decimal);
outParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outParam);
return query;
}
internal OracleQuery buildMoveMessageQuery(Addressee addressee)
{
string sql = "UPDATE SMS.ADDRESSEE SET FOLDER_ID=:folderId, OPLOCK=:oplockPlusOne WHERE ADDRESSEE_ID=:addresseeId and OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = addressee.FolderId;
query.Command.Parameters.Add(folderIdParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = addressee.Oplock + 1;
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter addresseeParam = new OracleParameter("addresseeId", OracleDbType.Decimal);
addresseeParam.Value = addressee.Id;
query.Command.Parameters.Add(addresseeParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = addressee.Oplock;
query.Command.Parameters.Add(oplockParam);
return query;
}
internal OracleQuery buildGetAddresseesForMessageQuery(int messageId)
{
string sql = "SELECT ADDRESSEE_ID, ADDRESSEE_ROLE, SECURE_MESSAGE_ID, USER_ID, OPLOCK AS ADDROPLOCK, FOLDER_ID, READ_DATE, REMINDER_DATE " +
"FROM SMS.ADDRESSEE WHERE SECURE_MESSAGE_ID=:messageId AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter messageIdParam = new OracleParameter("messageId", OracleDbType.Decimal);
messageIdParam.Value = messageId;
query.Command.Parameters.Add(messageIdParam);
return query;
}
internal OracleQuery buildGetUserByIcnQuery(string icn)
{
string sql = "SELECT USER_ID, FIRST_NAME, LAST_NAME, USER_TYPE, STATUS, EMAIL_ADDRESS, OPLOCK, ACTIVE, " +
"DOB, ICN, SSN, STATION_NO, DUZ, EMAIL_NOTIFICATION, DEFAULT_MESSAGE_FILTER, LAST_EMAIL_NOTIFICATION, " +
"NSSN, PROVIDER, EXTERNAL_USER_NAME FROM SMS.sms_user WHERE ICN = :icn";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
//query.Command.Parameters = new OracleParameterCollection();
OracleParameter icnParam = new OracleParameter("icn", OracleDbType.Varchar2, 50);
icnParam.Value = icn;
query.Command.Parameters.Add(icnParam);
return query;
}
internal OracleQuery buildDeleteFolderQuery(Int32 folderId)
{
string sql = "DELETE FROM SMS.FOLDER WHERE FOLDER_ID=:folderId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = folderId;
query.Command.Parameters.Add(folderIdParam);
return query;
}
internal OracleQuery buildGetUserByIdQuery(Int32 userId)
{
string sql = "SELECT USER_ID, FIRST_NAME, LAST_NAME, USER_TYPE, STATUS, EMAIL_ADDRESS, OPLOCK, ACTIVE, " +
"DOB, ICN, SSN, STATION_NO, DUZ, EMAIL_NOTIFICATION, DEFAULT_MESSAGE_FILTER, LAST_EMAIL_NOTIFICATION, " +
"NSSN, PROVIDER, EXTERNAL_USER_NAME FROM SMS.sms_user WHERE USER_ID = :userId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
//query.Command.Parameters = new OracleParameterCollection();
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = userId;
query.Command.Parameters.Add(userIdParam);
return query;
}
internal OracleQuery buildCreateFolderQuery(domain.sm.Folder folder)
{
string sql = "INSERT INTO SMS.FOLDER (USER_ID, FOLDER_NAME) VALUES (:userId, :folderName) RETURNING FOLDER_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = folder.Owner.Id;
query.Command.Parameters.Add(userIdParam);
OracleParameter folderNameParam = new OracleParameter("folderName", OracleDbType.Varchar2, 50);
folderNameParam.Value = folder.Name;
query.Command.Parameters.Add(folderNameParam);
OracleParameter outIdParam = new OracleParameter("outId", OracleDbType.Decimal);
outIdParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outIdParam);
return query;
}
// need to finish
internal OracleQuery buildGetValidRecipientsForProviderQuery(Int32 userId)
{
string sql = "SELECT TG.TRIAGE_GROUP_ID, TG.TRIAGE_GROUP_NAME, TG.DESCRIPTION " +
"FROM SMS.CLINICIAN_TRIAGE_MAP CTM JOIN SMS.TRIAGE_GROUP TG " +
"ON CTM.TRIAGE_GROUP_ID=TG.TRIAGE_GROUP_ID " +
"JOIN SMS.PATIENT_TRIAGE_MAP USR ON TRIAGE_GROUP.TRIAGE_GROUP_ID = USR. " +
"WHERE CTM.USER_ID = :userId AND TG.ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = userId;
query.Command.Parameters.Add(userIdParam);
return query;
}
internal OracleQuery buildGetThreadQuery(int threadId)
{
string sql = "SELECT * FROM SMS.MESSAGE_THREAD WHERE THREAD_ID=:threadId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter threadIdParam = new OracleParameter("threadId", OracleDbType.Decimal);
threadIdParam.Value = Convert.ToDecimal(threadId);
query.Command.Parameters.Add(threadIdParam);
return query;
}
internal OracleQuery buildGetMessageQuery(int messageId)
{
string sql = "SELECT SECURE_MESSAGE_ID, CLINICIAN_STATUS, COMPLETED_DATE, ASSIGNED_TO, CHECKSUM, THREAD_ID, STATUS_SET_BY, " +
"OPLOCK AS SMOPLOCK, ESCALATED, 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 " +
"FROM SMS.SECURE_MESSAGE WHERE SECURE_MESSAGE_ID=:messageId AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter messageIdParam = new OracleParameter("messageId", OracleDbType.Decimal);
messageIdParam.Value = Convert.ToDecimal(messageId);
query.Command.Parameters.Add(messageIdParam);
return query;
}
/// <remarks>
/// This SQL statement should work for all SMS users (patients, providers, etc).
/// </remarks>
internal OracleQuery buildGetSecureMessagesQuery(Int32 userId, int pageStart, int pageSize)
{
// not currently fetching body inline - should be able to add "SM.BODY" to statement below and begin receiving inline
StringBuilder sb = new StringBuilder("SELECT ADDR.ADDRESSEE_ID, ADDR.ADDRESSEE_ROLE, ADDR.OPLOCK AS ADDROPLOCK, ADDR.ACTIVE AS ADDRACTIVE, ");
sb.Append("ADDR.USER_ID, ADDR.CREATED_DATE AS ADDRCREATEDDATE, ADDR.MODIFIED_DATE AS ADDRMODIFIEDDATE, ADDR.FOLDER_ID, ");
sb.Append("ADDR.READ_DATE, ADDR.REMINDER_DATE, FOLD.FOLDER_NAME, FOLD.ACTIVE AS FOLDACTIVE, FOLD.OPLOCK AS FOLDOPLOCK, ");
sb.Append("SM.SECURE_MESSAGE_ID, SM.CLINICIAN_STATUS, SM.COMPLETED_DATE, SM.ASSIGNED_TO, ");
sb.Append("SM.CHECKSUM, SM.THREAD_ID, SM.STATUS_SET_BY, SM.OPLOCK AS SMOPLOCK, SM.ACTIVE, SM.CREATED_DATE, SM.MODIFIED_DATE, ");
sb.Append("SM.ESCALATED, SM.SENT_DATE, SM.SENDER_TYPE, SM.SENDER_ID, SM.SENDER_NAME, ");
sb.Append("SM.RECIPIENT_TYPE, SM.RECIPIENT_ID, SM.RECIPIENT_NAME, ");
sb.Append("SM.ESCALATION_NOTIFICATION_DATE, SM.ESCALATION_NOTIFICATION_TRIES, SM.READ_RECEIPT, ");
sb.Append("SM.HAS_ATTACHMENT, SM.ATTACHMENT_ID, MT.SUBJECT, MT.TRIAGE_GROUP_ID, MT.CATEGORY_TYPE, MT.OPLOCK AS MTOPLOCK ");
sb.Append("FROM SMS.ADDRESSEE ADDR ");
sb.Append("JOIN SMS.SECURE_MESSAGE SM ON ADDR.SECURE_MESSAGE_ID=SM.SECURE_MESSAGE_ID ");
sb.Append("JOIN SMS.MESSAGE_THREAD MT ON SM.THREAD_ID=MT.THREAD_ID ");
sb.Append("LEFT JOIN SMS.FOLDER FOLD ON ADDR.FOLDER_ID=FOLD.FOLDER_ID ");
sb.Append("WHERE ADDR.USER_ID = :userId AND ADDR.ACTIVE = 1 AND ROWNUM >= :pageStart AND ROWNUM <= :pageSize ");
sb.Append("ORDER BY SM.SENT_DATE DESC");
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sb.ToString());
query.Command.InitialLOBFetchSize = -1; // setting this to -1 causes the SM.BODY column to be fetched inline - 10X performance increase
OracleParameter idParam = new OracleParameter("userId", OracleDbType.Decimal);
idParam.Value = Convert.ToDecimal(userId);
query.Command.Parameters.Add(idParam);
OracleParameter pageStartParam = new OracleParameter("pageStart", OracleDbType.Decimal);
pageStartParam.Value = Convert.ToDecimal(pageStart);
query.Command.Parameters.Add(pageStartParam);
OracleParameter pageSizeParam = new OracleParameter("pageSize", OracleDbType.Decimal);
if (pageSize == 0)
{
pageSize = 25; // set default to 25
}
pageSizeParam.Value = Convert.ToDecimal(pageStart + pageSize);
query.Command.Parameters.Add(pageSizeParam);
return query;
}
internal OracleQuery buildDeleteThreadQuery(Int32 threadId, bool inactivate)
{
string sql = "DELETE FROM SMS.MESSAGE_THREAD WHERE THREAD_ID=:threadId";
if (inactivate)
{
// TBD - which is the correct way? delete the record or set to inactive?
sql = "UPDATE SMS.MESSAGE_THREAD SET ACTIVE=0 WHERE THREAD_ID=:threadId";
}
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter threadIdParam = new OracleParameter("threadId", OracleDbType.Decimal);
threadIdParam.Value = Convert.ToDecimal(threadId);
query.Command.Parameters.Add(threadIdParam);
return query;
}
internal OracleQuery buildGetSecureMessageBodyQuery(Int32 messageId)
{
//string sql = "SELECT SM.SECURE_MESSAGE_ID, SM.CLINICIAN_STATUS, SM.COMPLETED_DATE, SM.ASSIGNED_TO, " +
// "SM.CHECKSUM, SM.THREAD_ID, SM.STATUS_SET_BY, SM.ACTIVE, SM.CREATED_DATE, SM.MODIFIED_DATE, " +
// "SM.ESCALATED, SM.SENT_DATE, SM.SENDER_TYPE, SM.SENDER_ID, SM.SENDER_NAME, " +
// "SM.RECIPIENT_TYPE, SM.RECIPIENT_ID, SM.RECIPIENT_NAME, SM.BODY, " +
// "SM.ESCALATION_NOTIFICATION_DATE, SM.ESCALATION_NOTIFICATION_TRIES, SM.READ_RECEIPT, " +
// "SM.HAS_ATTACHMENT, SM.ATTACHMENT_ID, MT.SUBJECT, MT.TRIAGE_GROUP_ID, MT.CATEGORY_TYPE " +
// "FROM SMS.secure_message SM JOIN SMS.message_thread MT ON " +
// "SM.THREAD_ID=MT.THREAD_ID WHERE SM.SECURE_MESSAGE_ID = :secureMessageId";
string sql = "SELECT SM.CHECKSUM, SM.BODY FROM SMS.SECURE_MESSAGE SM WHERE SM.SECURE_MESSAGE_ID = :secureMessageId AND SM.ACTIVE = 1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
query.Command.InitialLOBFetchSize = -1; // setting this to -1 causes the SM.BODY column to be fetched inline - 10X performance increase
OracleParameter idParam = new OracleParameter("secureMessageId", OracleDbType.Decimal);
idParam.Value = Convert.ToDecimal(messageId);
query.Command.Parameters.Add(idParam);
return query;
}
// GOOD TO GO
internal OracleQuery buildCreateThreadQuery(domain.sm.Thread thread)
{
string sql = "INSERT INTO SMS.MESSAGE_THREAD (SUBJECT, TRIAGE_GROUP_ID, CREATED_DATE, MODIFIED_DATE, " +
"CATEGORY_TYPE) VALUES (:subject, :triageGroupId, :createdDate, :modifiedDate, :categoryType) " +
"RETURNING THREAD_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter subjectParam = new OracleParameter("subject", OracleDbType.Varchar2, 512);
subjectParam.Value = thread.Subject;
query.Command.Parameters.Add(subjectParam);
OracleParameter triageGroupParam = new OracleParameter("triageGroupId", OracleDbType.Decimal);
if (thread.MailGroup == null || thread.MailGroup.Id <= 0)
{
triageGroupParam.Value = DBNull.Value;
}
else
{
triageGroupParam.Value = thread.MailGroup.Id;
}
query.Command.Parameters.Add(triageGroupParam);
OracleParameter createdDateParam = new OracleParameter("createdDate", OracleDbType.Date);
createdDateParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
query.Command.Parameters.Add(createdDateParam);
OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedDateParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedDateParam);
OracleParameter categoryTypeParam = new OracleParameter("categoryType", OracleDbType.Decimal);
categoryTypeParam.Value = (Int32)thread.MessageCategoryType;
query.Command.Parameters.Add(categoryTypeParam);
OracleParameter outParam = new OracleParameter("outId", OracleDbType.Decimal);
outParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outParam);
return query;
}
internal OracleQuery buildGetMessagesFromThreadQuery(Int32 threadId)
{
string sql = "SELECT SM.SECURE_MESSAGE_ID, SM.CLINICIAN_STATUS, SM.COMPLETED_DATE, SM.ASSIGNED_TO, SM.OPLOCK AS SMOPLOCK, " +
"SM.SENT_DATE, SM.SENDER_ID, SM.RECIPIENT_ID, MT.SUBJECT, MT.TRIAGE_GROUP_ID, MT.OPLOCK AS MTOPLOCK, MT.CATEGORY_TYPE " +
"FROM SMS.SECURE_MESSAGE SM JOIN SMS.MESSAGE_THREAD MT ON SM.THREAD_ID=MT.THREAD_ID WHERE SM.THREAD_ID=:threadId AND SM.ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter threadIdParam = new OracleParameter("threadId", OracleDbType.Decimal);
threadIdParam.Value = threadId;
query.Command.Parameters.Add(threadIdParam);
return query;
}
internal OracleQuery buildUpdateThreadQuery(domain.sm.Thread thread)
{
string sql = "UPDATE SMS.MESSAGE_THREAD SET SUBJECT=:subject, TRIAGE_GROUP_ID=:triageGroupId, OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate, " +
"CATEGORY_TYPE=:categoryType WHERE THREAD_ID=:threadId AND OPLOCK=:oplock";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter subjectParam = new OracleParameter("subject", OracleDbType.Varchar2, 512);
subjectParam.Value = thread.Subject;
query.Command.Parameters.Add(subjectParam);
OracleParameter triageGroupParam = new OracleParameter("triageGroupId", OracleDbType.Decimal);
if (thread.MailGroup == null || thread.MailGroup.Id <= 0)
{
triageGroupParam.Value = DBNull.Value;
}
else
{
triageGroupParam.Value = thread.MailGroup.Id;
}
query.Command.Parameters.Add(triageGroupParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = Convert.ToDecimal(thread.Oplock + 1);
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedDateParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedDateParam.Value = new Oracle.DataAccess.Types.OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedDateParam);
OracleParameter categoryTypeParam = new OracleParameter("categoryType", OracleDbType.Decimal);
categoryTypeParam.Value = (Int32)thread.MessageCategoryType;
query.Command.Parameters.Add(categoryTypeParam);
OracleParameter threadIdParam = new OracleParameter("threadId", OracleDbType.Decimal);
threadIdParam.Value = Convert.ToDecimal(thread.Id);
query.Command.Parameters.Add(threadIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = Convert.ToDecimal(thread.Oplock);
query.Command.Parameters.Add(oplockParam);
return query;
}
internal OracleQuery buildGetTriageGroupMembersQuery(Int32 groupId)
{
//string sql = "SELECT USER_ID, EMAIL_ADDRESS, OPLOCK, EMAIL_NOTIFICATION, LAST_EMAIL_NOTIFICATION FROM SMS.CLINICIAN_TRIAGE_MAP WHERE TRIAGE_GROUP_ID = :groupId and ACTIVE = 1";
string sql = "SELECT CTM.USER_ID, USR.EMAIL_ADDRESS, USR.OPLOCK, USR.EMAIL_NOTIFICATION, USR.LAST_EMAIL_NOTIFICATION " +
"FROM SMS.CLINICIAN_TRIAGE_MAP CTM JOIN SMS.SMS_USER USR ON CTM.USER_ID=USR.USER_ID " +
"WHERE CTM.TRIAGE_GROUP_ID=:groupId AND CTM.ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter groupIdParam = new OracleParameter("groupId", OracleDbType.Decimal);
groupIdParam.Value = Convert.ToDecimal(groupId);
query.Command.Parameters.Add(groupIdParam);
return query;
}
internal OracleQuery buildCreateAddresseeQuery(domain.sm.Addressee addressee, Int32 messageId)
{
string sql = "INSERT INTO SMS.ADDRESSEE (ADDRESSEE_ROLE, SECURE_MESSAGE_ID, USER_ID, FOLDER_ID) VALUES (:addresseeRole, :smId, :userId, :folderId) "+
"RETURNING ADDRESSEE_ID INTO :outId";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter addresseeRoleParam = new OracleParameter("addresseeRole", OracleDbType.Decimal);
addresseeRoleParam.Value = Convert.ToDecimal((Int32)addressee.Role);
query.Command.Parameters.Add(addresseeRoleParam);
OracleParameter smIdParam = new OracleParameter("smId", OracleDbType.Decimal);
smIdParam.Value = Convert.ToDecimal(messageId);
query.Command.Parameters.Add(smIdParam);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = Convert.ToDecimal(addressee.Owner.Id);
query.Command.Parameters.Add(userIdParam);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = Convert.ToDecimal(addressee.Folder.Id);
query.Command.Parameters.Add(folderIdParam);
OracleParameter outParam = new OracleParameter("outId", OracleDbType.Decimal);
outParam.Direction = ParameterDirection.Output;
query.Command.Parameters.Add(outParam);
return query;
}
internal OracleQuery buildUpdateFolderQuery(domain.sm.Folder folder)
{
string sql = "UPDATE SMS.FOLDER SET FOLDER_NAME=:folderName, OPLOCK=:oplockPlusOne, MODIFIED_DATE=:modifiedDate WHERE FOLDER_ID=:folderId and OPLOCK=:oplock AND ACTIVE=1";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter folderNameParam = new OracleParameter("folderName", OracleDbType.Varchar2, 50);
folderNameParam.Value = folder.Name;
query.Command.Parameters.Add(folderNameParam);
OracleParameter oplockPlusOneParam = new OracleParameter("oplockPlusOne", OracleDbType.Decimal);
oplockPlusOneParam.Value = folder.Oplock + 1;
query.Command.Parameters.Add(oplockPlusOneParam);
OracleParameter modifiedParam = new OracleParameter("modifiedDate", OracleDbType.Date);
modifiedParam.Value = new OracleDate(DateTime.Now);
query.Command.Parameters.Add(modifiedParam);
OracleParameter folderIdParam = new OracleParameter("folderId", OracleDbType.Decimal);
folderIdParam.Value = folder.Id;
query.Command.Parameters.Add(folderIdParam);
OracleParameter oplockParam = new OracleParameter("oplock", OracleDbType.Decimal);
oplockParam.Value = folder.Oplock;
query.Command.Parameters.Add(oplockParam);
return query;
}
