public static string GetEncryptionKey()
{
WEBeSettings settings = new WEBeSettings();
OracleDataAccess.OracleCommandData data = new OracleDataAccess.OracleCommandData();
try
{
data._CommandType = CommandType.Text;
data.CommandText = "select encryptionkey from WEBESETTINGS";
data.OpenWithOutTrans();
//Executing Query
DataSet ds = data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
{
settings.dt = ds.Tables[0];
settings.isValid = true;
}
}
catch (Exception ex)
{
settings.Error = ex.Message.ToString();
//throw;
}
finally
{
data.Close();
}
return(settings.dt.Rows[0]["encryptionkey"].ToString());
}
public PatientData SearchPatientDocument(string SearchClause = "")
{
PatientData data = new PatientData();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "SELECT * FROM PATIENT_DOCUMENT WHERE " + SearchClause + " order by Patient_Document_ID DESC";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
data.dt = _ds.Tables[0];
data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(data);
}
public PatientData GetDocumentCategory()
{
PatientData data = new PatientData();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "SELECT * FROM DOC_Category";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
data.dt = _ds.Tables[0];
data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(data);
}
public PatientData GetPatientDocumentByCategoryID(Int64 CategoryID, string PatientID)
{
PatientData data = new PatientData();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "SELECT * FROM PATIENT_DOCUMENT where trim(DOC_CATEGORY_ID)=" + CategoryID + " AND trim(Patient_ID)='" + PatientID + "' order by Patient_Document_ID DESC";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
data.dt = _ds.Tables[0];
data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(data);
}
public int DeletePatientDocument(Int64 PatientDocumentID, Int64 CategoryID)
{
int returnValue = 0;
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "DELETE FROM PATIENT_DOCUMENT WHERE PATIENT_DOCUMENT_ID=" + PatientDocumentID + " AND DOC_CATEGORY_ID=" + CategoryID + " ";
// _data.CommandText = " ";
_data.OpenWithOutTrans();
//Executing Query
object obj = _data.Execute(OracleDataAccess.ExecutionType.ExecuteNonQuery);
returnValue = Convert.ToInt16(obj);
}
catch (Exception ex)
{
//data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(returnValue);
}
public PatientData GetCategoryRecordFromPatientDocument(Int64 Doc_Category_ID)
{
PatientData data = new PatientData();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "Select * from Patient_Document where Doc_Category_ID in( Select Doc_Category_ID from DOC_Category where Doc_Category_ID =" + Doc_Category_ID + " OR Parent_ID = " + Doc_Category_ID + ")";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
data.dt = _ds.Tables[0];
data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(data);
}
public int DeleteDocumentCategoryByCategoryID(Int64 DocumentCategoryID)
{
int returnValue = 0;
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "DELETE FROM DOC_CATEGORY WHERE DOC_CATEGORY_ID=" + DocumentCategoryID + "";
_data.OpenWithOutTrans();
//Executing Query
object obj = _data.Execute(OracleDataAccess.ExecutionType.ExecuteNonQuery);
returnValue = Convert.ToInt16(obj);
// data.dt = _ds.Tables[0];
// data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
//data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(returnValue);
}
public static string GetDecryptedStringWithID(string ID, string Type)
{
WEBeSettings settings = new WEBeSettings();
OracleDataAccess.OracleCommandData data = new OracleDataAccess.OracleCommandData();
string DecryptedString = string.Empty;
try
{
data._CommandType = CommandType.Text;
if (Type.ToLower() == "master")
{
data.CommandText = "select note, hash from events where event_id =" + ID;
}
else if (Type.ToLower() == "detail")
{
data.CommandText = "select note, hash from EVENTS_DATA where note_id =" + ID;
}
data.OpenWithOutTrans();
//Executing Query
DataSet ds = data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
{
string EncryptedString = ds.Tables[0].Rows[0]["note"].ToString();
string Hash = ds.Tables[0].Rows[0]["hash"].ToString();
DecryptedString = DecryptAESCalculateHash(EncryptedString, Hash);
}
if (string.IsNullOrWhiteSpace((DecryptedString)))
{
DecryptedString = "Error: Message integrity has been lost.";
}
}
catch (Exception ex)
{
settings.Error = ex.Message.ToString();
//throw;
}
finally
{
data.Close();
}
return(DecryptedString);
}
public int UpdatePatientDocument(Int64 CategoryID, paraPatientDocument para)
{
int returnValue = 0;
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
// _data.CommandText = "UPDATE PATIENT_DOCUMENT SET NAME='" + para.Name + "',TITLE='" + para.Title + "',DESCRIPTION='" + para.Description + "',DOC_CATEGORY_ID=" + para.CategoryID + ",ELECTRONIC_LINK='" + para.ElectronicLink + "',PATIENT_ID='" + para.PatientID + "' WHERE PATIENT_DOCUMENT_ID=" + para.PatientDocumentID + " AND DOC_CATEGORY_ID=" + CategoryID + " ";
_data.CommandText = "UPDATE PATIENT_DOCUMENT SET NAME='" + para.Name + "',TITLE='" + para.Title + "',DESCRIPTION='" + para.Description + "',DOC_CATEGORY_ID=" + para.CategoryID + ",ELECTRONIC_LINK='" + para.ElectronicLink + "',PATIENT_ID='" + para.PatientID + "' WHERE PATIENT_DOCUMENT_ID=" + para.PatientDocumentID + "";
// _data.CommandText = " ";
_data.OpenWithOutTrans();
//Executing Query
object obj = _data.Execute(OracleDataAccess.ExecutionType.ExecuteNonQuery);
returnValue = Convert.ToInt16(obj);
//if (returnValue > 0)
//{
// string FileType = para.Path.ToString();
// int posn = FileType.IndexOf(".");
// if (posn > 0)
// FileType = FileType.Substring(posn + 1, FileType.Length - posn - 1);
// else
// FileType = "";
// string FileName = para.Title.Trim() + "." + FileType;
// FileHelper.CheckOrCreateDirectory("C:\\inetpub\\wwwroot\\Upload");
// // FileHelper.BytesToDisk(para.attachement, "C:\\inetpub\\wwwroot\\Upload" + "\\" + FileName);
// FileHelper.BytesToDisk(para.attachement, para.Path.ToString());
//}
}
catch (Exception ex)
{
//data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(returnValue);
}
public int AddPatientDocument(Int64 CategoryID, paraPatientDocument para)
{
int returnValue = 0;
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "INSERT INTO PATIENT_DOCUMENT (PATIENT_DOCUMENT_ID,NAME,TITLE,DESCRIPTION,DOC_CATEGORY_ID,ELECTRONIC_LINK,EXTENSION,PATH,PATIENT_ID)" +
" values(PATIENT_DOCUMENT_SEQ.NEXTVAL,'" + para.Name + "','" + para.Title + "','" + para.Description + "'," + para.CategoryID + ",'" + para.ElectronicLink + "','" + para.Extension + "','" + para.Path + "','" + para.PatientID + "')";
_data.OpenWithOutTrans();
//Executing Query
object obj = _data.Execute(OracleDataAccess.ExecutionType.ExecuteNonQuery);
returnValue = Convert.ToInt16(obj);
if (returnValue > 0)
{
string FileType = para.Path.ToString();
int posn = FileType.IndexOf(".");
if (posn > 0)
{
FileType = FileType.Substring(posn + 1, FileType.Length - posn - 1);
}
else
{
FileType = "";
}
string FileName = para.Title.Trim() + "." + FileType;
FileHelper.CheckOrCreateDirectory("C:\\inetpub\\wwwroot\\Upload\\PHICDocument");
//FileHelper.BytesToDisk(para.attachement, "C:\\inetpub\\wwwroot\\Upload" + "\\" + FileName);
FileHelper.BytesToDisk(para.attachement, para.Path.ToString());
}
}
catch (Exception ex)
{
//data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(returnValue);
}
public paraPatientDocument GetPatientDocumentByPatientDocumentID(Int64 PatientDocumentID)
{
paraPatientDocument data = new paraPatientDocument();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "SELECT * FROM PATIENT_DOCUMENT where Patient_Document_ID=" + PatientDocumentID + "";// trim(DOC_CATEGORY_ID)=" + CategoryID + " AND trim(Patient_ID)='" + PatientID + "'";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in _ds.Tables[0].Rows)
{
data.Name = Path.GetFileName(item["Path"].ToString());
string _path = Server.MapPath("/Upload/PHICDocument/" + data.Name);
byte[] fileBytes = System.IO.File.ReadAllBytes(_path);
data.attachement = fileBytes;
// return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, FileName);
}
}
// data.dt = _ds.Tables[0];
// data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
// data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(data);
}
public int SaveDocumentCategory(paraDocumentCategory _par, string Type)
{
int returnValue = 0;
string _querystring = string.Empty;
if (Type == "EDIT")
{
_querystring = "Update DOC_CATEGORY SET NAME='" + _par.Name.ToString().Trim() + "',Description='" + _par.Description.ToString() + "' WHERE DOC_CATEGORY_ID=" + _par.DOC_CATEGORY_ID + " AND PARENT_ID=" + _par.Parent_ID + "";
}
else if (Type == "SIBLING")
{
// _data.CommandText = "Update DOC_CATEGORY SET NAME='" + _par.Name.ToString().Trim() + "',Description='" + _par.Description.ToString() + "' WHERE DOC_CATEGORY_ID=" + _par.DOC_CATEGORY_ID + " AND PARENT_ID=" + _par.Parent_ID + "";
_querystring = "INSERT INTO DOC_CATEGORY (DOC_CATEGORY_ID,NAME,DESCRIPTION,PARENT_ID) VALUES(DOC_CATEGORYSEQ.NEXTVAL,'" + _par.Name + "','" + _par.Description + "'," + _par.Parent_ID + ")";
}
else if (Type == "SUBCATEGORY")
{
_querystring = "INSERT INTO DOC_CATEGORY (DOC_CATEGORY_ID,NAME,DESCRIPTION,PARENT_ID) VALUES(DOC_CATEGORYSEQ.NEXTVAL,'" + _par.Name + "','" + _par.Description + "'," + _par.DOC_CATEGORY_ID + ")";
}
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = _querystring;
_data.OpenWithOutTrans();
//Executing Query
object obj = _data.Execute(OracleDataAccess.ExecutionType.ExecuteNonQuery);
returnValue = Convert.ToInt16(obj);
// data.dt = _ds.Tables[0];
// data.isValid = true;
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
//data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(returnValue);
}
public int DeletePatientDocumentByPatientDocumentID(Int64 PatientDocumentID)
{
int _ReturnValue = 0;
PatientData data = new PatientData();
OracleDataAccess.OracleCommandData _data = new OracleDataAccess.OracleCommandData();
try
{
_data._CommandType = CommandType.Text;
_data.CommandText = "SELECT * FROM PATIENT_DOCUMENT where Patient_Document_ID=" + PatientDocumentID + "";// trim(DOC_CATEGORY_ID)=" + CategoryID + " AND trim(Patient_ID)='" + PatientID + "'";
_data.OpenWithOutTrans();
//Executing Query
DataSet _ds = _data.Execute(OracleDataAccess.ExecutionType.ExecuteDataSet) as DataSet;
data.dt = _ds.Tables[0];
data.isValid = true;
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in _ds.Tables[0].Rows)
{
FileHelper.DeleteFile(item["Path"].ToString());
_ReturnValue = 1;
}
}
//string sQuery = "SELECT * FROM PatientTable ";
// data.dt= new DBAction().ExecuteDataSetInline(sQuery).Tables[0];
}
catch (Exception ex)
{
data.Error = ex.Message.ToString();
//throw;
}
finally
{
_data.Close();
}
return(_ReturnValue);
}
