public string GetCTRLviaCaseID(int _CaseID)
{
NMOOracleDatabase database = new NMOOracleDatabase(dbConn);
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_CASE_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _CaseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_CTRL_NUM";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 10;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("CONVERT_CASE_ID_TO_CTRL", Parameters);
logger.Debug("GetCTRLviaCaseID Result: " + Parameters[1].Value.ToString());
return Parameters[1].Value.ToString();
}
catch (Exception e)
{
logger.Error("GetCTRLviaCaseID threw error: ControlId not found "+ e.GetBaseException());
return "00000";
}
finally
{
database.CloseConnection();
}
}
//FILE_NAME fileName, USER_ID userId, DOC_TYPE docType, DOC_TYPE_ID docTypeId, FILE_SIZE size, 'mimetype', 'waiver_doc',document_id id,
//PATH , FILE_EXTENSION fileExtension, UPLOAD_DATE, dtCreated, 'waiver_doc',
//
//
//
public string addInternalNotification(Int64 caseID, Int64 userID, Int64 statusID, Int64 toUserID, string notice)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[6];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_CASE_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_USER_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = userID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_STATUS_ID";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = statusID;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_NOTICE";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = notice;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_USER_ID_TO";
Parameters[4].OracleDbType = NMOOracleDbType.Int64;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = toUserID;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "P_RESULT";
Parameters[5].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[5].Direction = NMOParameterDirection.Output;
Parameters[5].Size = 4000;
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_NOTIFICATIONS.ADD_NOTIFICATION", Parameters);
return Parameters[5].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("Notifications.addInternalNotification exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <param name="titleOfTableDesired"></param>
/// <returns></returns>
internal DataSet getDocsForUser(String title)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[3];
//LKUP_PRIORITY
//DOC_TYPES_FOR_EXT_USE' , DOC_TYPES_FOR_WW_EXT_USE
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_title";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = title;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_user_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = SpecDutyBase.gv.WW_USERLONG;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_cur";
Parameters[2].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[2].Direction = NMOParameterDirection.Output;
try
{
dbsdw1.OpenConnection();
return dbsdw1.GetDataSet("PKG_ICM_DOCUMENTS.getdocsforuser", Parameters);
}
catch (Exception e)
{
logger.Fatal("BaseClass.getdocsforuser exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
//
public string UpdatePriority(Int64 caseID, Int64 userId, string priority, String justification)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_caseid";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_user_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = userId;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_priority";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = priority;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_JUSTIFICATION";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = justification;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_result";
Parameters[4].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[4].Size = 4000;
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.update_priority", Parameters);
logger.Debug("UpdatePriority Result: " + Parameters[4].Value.ToString());
return Parameters[4].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("CaseClass.UpdatePriority exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
public String UpdateDefaultOption(int user_id, int type_id, string value)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[4];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_user_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = user_id;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_option_type_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = type_id;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_option_value";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = value;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_result";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 128;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("update_default_option", Parameters);
logger.Debug("UpdateDefaultOption for " + type_id + " Result: " + Parameters[3].Value.ToString());
return Parameters[3].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
//
//
//
public DataSet getCaseWaiverGroupsRequested(Int64 caseID, Int64 userID)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[3];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_case_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_user_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = userID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_cur";
Parameters[2].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[2].Direction = NMOParameterDirection.Output;
using (NMOOracleDatabase dbsdw1 = new NMOOracleDatabase(dbConnSdw))
{
try
{
// dbsdw1.OpenConnection();
return dbsdw1.GetDataSet("PKG_ICM_CASES.getCaseWaiverGroupsRequested", Parameters);
}
catch (Exception e)
{
logger.Fatal("CaseClass.getCaseWaiverGroupsRequested exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="caseID"></param>
/// <returns></returns>
public String getNoteToExtUser(Int64 caseID)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_case_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_result";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 4000;
using (NMOOracleDatabase dbsdw1 = new NMOOracleDatabase(dbConnSdw))
{
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.getNoteToExtUser", Parameters);
return Parameters[1].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("BaseClass.getNoteToExtUser exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
}
/**
*
*/
public string CaseAction(string type, Int64 case_id, Int64 user_id)
{
String resultResubmit = "";
// case_id = GetCaseIDviaCTRL(caseCtrlNumber);
NMOOracleParameter[] Parameters = new NMOOracleParameter[4];
//type = "RESUBMIT";
//user_id=WW_USERLONG
//case_id long
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_type";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = type;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_case_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = case_id;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_user_id";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = user_id;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_result";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 4000;
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.case_action", Parameters);
resultResubmit = Parameters[3].Value.ToString();
logger.Debug("CaseAction Result: " + resultResubmit);
if (resultResubmit.Equals("SUCCESS"))
{
addCaseLog(case_id, user_id);
}
return Parameters[3].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("SpecDutyBase.CaseAction 1 exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
//
//
//
public String findRecommendation_deprecated(Int64 caseID, String system, String waiverType, String wvGrpDescr)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_case_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_system";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = system;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_WAIVER_GROUP";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = wvGrpDescr;// "REAB";
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_waiver_type";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = waiverType;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_result";
Parameters[4].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[4].Size = 4000;
try
{
NMOOracleDatabase dbsdw1 = new NMOOracleDatabase(dbConnSdw);
dbsdw1.OpenConnection();
// Parameters = dbsdw1.ExecuteStoredProcedure("findRecommendation", Parameters);
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.findDecision", Parameters);
// Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.findRecommendation", Parameters);
if (!Parameters[3].Value.ToString().StartsWith("ORA-"))
{
string decision = Parameters[4].Value.ToString();
return decision.Equals("null") ? "" : decision;
// return Parameters[4].Value.ToString();
}
else
{
return "";
}
}
catch (Exception e)
{
logger.Fatal("findRecommendation result: " + Parameters[3].Value.ToString());
logger.Fatal("SpecdutyBase.findRecommendation exception: " + e.Message , e);
return "";
}
finally
{
dbsdw1.CloseConnection();
}
}
public DataSet GetAReport(string _ReportName, int _UserID, string _FromDate, string _ToDate, int service, int dod_status,
int dod_component)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[9];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_REPORT_NAME";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _ReportName;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_USER_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = _UserID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_DATE_FROM";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = _FromDate;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_DATE_TO";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = _ToDate;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_cur";
Parameters[4].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_err";
Parameters[5].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[5].Direction = NMOParameterDirection.Output;
Parameters[5].Size = 4000;
Parameters[6] = new NMOOracleParameter();
Parameters[6].ParameterName = "P_SERVICE";
Parameters[6].OracleDbType = NMOOracleDbType.Int32;
Parameters[6].Direction = NMOParameterDirection.Input;
Parameters[6].Value = service;
Parameters[7] = new NMOOracleParameter();
Parameters[7].ParameterName = "P_DOD_STATUS";
Parameters[7].OracleDbType = NMOOracleDbType.Int32;
Parameters[7].Direction = NMOParameterDirection.Input;
Parameters[7].Value = dod_status;
Parameters[8] = new NMOOracleParameter();
Parameters[8].ParameterName = "P_DOD_COMPONENT";
Parameters[8].OracleDbType = NMOOracleDbType.Int32;
Parameters[8].Direction = NMOParameterDirection.Input;
Parameters[8].Value = dod_component;
try
{
database.OpenConnection();
return database.GetDataSet("GET_A_REPORT", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public DataSet GetAvgTimeReport(string _ReportName, int _UserID, string _FromDate, string _ToDate)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[6];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_REPORT_NAME";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _ReportName;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_USER_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = _UserID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_DATE_FROM";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = _FromDate;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_DATE_TO";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = _ToDate;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_cur";
Parameters[4].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_err";
Parameters[5].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[5].Direction = NMOParameterDirection.Output;
Parameters[5].Size = 4000;
try
{
database.OpenConnection();
return database.GetDataSet("GET_AVG_TIME_REPORT", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public String AddLetterComponent(String component, int sortOrder, String description, String value)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDB"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_field";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = component;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_sortorder";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = sortOrder;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_description";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = description;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_value";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = value;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_result";
Parameters[4].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[4].Size = 4000;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("ADD_LETTER_COMPONENT", Parameters);
logger.Debug("ADD_LETTER_COMPONENT Result: " + Parameters[4].Value.ToString());
return Parameters[4].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public string GetAccess(int _CaseID, int _UserID)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[3];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_Case_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _CaseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_USER_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = _UserID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_ACCESS";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Output;
Parameters[2].Size = 4000;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("GET_IF_ACCESS", Parameters);
logger.Debug("GetAccess Result for case " + _CaseID + " and user " + _UserID + " is " + Parameters[2].Value.ToString());
return Parameters[2].Value.ToString();
}
catch
{
return "false";
}
finally
{
database.CloseConnection();
}
}
public String AddLetter(int ltrID, int caseID, String headerImage, int toID, int subjectID,
int enclID, int para1ID, int para2ID, int para3ID,
int para4ID, int para5ID, int para6ID, int para7ID,
int para8ID, int para9ID, int para10ID, int para11ID,
int para12ID, int copyToID, String toText, String subject,
String reference, String enclosure, String paragraph1,
String paragraph2, String paragraph3, String paragraph4,
String paragraph5, String paragraph6, String paragraph7,
String paragraph8, String paragraph9, String paragraph10,
String paragraph11, String paragraph12, String copyTo,
String sigPath, String closed)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDB"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[39];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_ltr_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = ltrID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_caseID";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = caseID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_headerImage";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = headerImage;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_toID";
Parameters[3].OracleDbType = NMOOracleDbType.Int32;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = toID;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_subjectID";
Parameters[4].OracleDbType = NMOOracleDbType.Int32;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = subjectID;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_enclID";
Parameters[5].OracleDbType = NMOOracleDbType.Int32;
Parameters[5].Direction = NMOParameterDirection.Input;
Parameters[5].Value = enclID;
Parameters[6] = new NMOOracleParameter();
Parameters[6].ParameterName = "p_para1ID";
Parameters[6].OracleDbType = NMOOracleDbType.Int32;
Parameters[6].Direction = NMOParameterDirection.Input;
Parameters[6].Value = para1ID;
Parameters[7] = new NMOOracleParameter();
Parameters[7].ParameterName = "p_para2ID";
Parameters[7].OracleDbType = NMOOracleDbType.Int32;
Parameters[7].Direction = NMOParameterDirection.Input;
Parameters[7].Value = para2ID;
Parameters[8] = new NMOOracleParameter();
Parameters[8].ParameterName = "p_para3ID";
Parameters[8].OracleDbType = NMOOracleDbType.Int32;
Parameters[8].Direction = NMOParameterDirection.Input;
Parameters[8].Value = para3ID;
Parameters[9] = new NMOOracleParameter();
Parameters[9].ParameterName = "p_para4ID";
Parameters[9].OracleDbType = NMOOracleDbType.Int32;
Parameters[9].Direction = NMOParameterDirection.Input;
Parameters[9].Value = para4ID;
Parameters[10] = new NMOOracleParameter();
Parameters[10].ParameterName = "p_para5ID";
Parameters[10].OracleDbType = NMOOracleDbType.Int32;
Parameters[10].Direction = NMOParameterDirection.Input;
Parameters[10].Value = para5ID;
Parameters[11] = new NMOOracleParameter();
Parameters[11].ParameterName = "p_para6ID";
Parameters[11].OracleDbType = NMOOracleDbType.Int32;
Parameters[11].Direction = NMOParameterDirection.Input;
Parameters[11].Value = para6ID;
Parameters[12] = new NMOOracleParameter();
Parameters[12].ParameterName = "p_para7ID";
Parameters[12].OracleDbType = NMOOracleDbType.Int32;
Parameters[12].Direction = NMOParameterDirection.Input;
Parameters[12].Value = para7ID;
Parameters[13] = new NMOOracleParameter();
Parameters[13].ParameterName = "p_para8ID";
Parameters[13].OracleDbType = NMOOracleDbType.Int32;
Parameters[13].Direction = NMOParameterDirection.Input;
Parameters[13].Value = para8ID;
Parameters[14] = new NMOOracleParameter();
Parameters[14].ParameterName = "p_para9ID";
Parameters[14].OracleDbType = NMOOracleDbType.Int32;
Parameters[14].Direction = NMOParameterDirection.Input;
Parameters[14].Value = para9ID;
Parameters[15] = new NMOOracleParameter();
Parameters[15].ParameterName = "p_para10ID";
Parameters[15].OracleDbType = NMOOracleDbType.Int32;
Parameters[15].Direction = NMOParameterDirection.Input;
Parameters[15].Value = para10ID;
Parameters[16] = new NMOOracleParameter();
Parameters[16].ParameterName = "p_para11ID";
Parameters[16].OracleDbType = NMOOracleDbType.Int32;
Parameters[16].Direction = NMOParameterDirection.Input;
Parameters[16].Value = para11ID;
Parameters[17] = new NMOOracleParameter();
Parameters[17].ParameterName = "p_para12ID";
Parameters[17].OracleDbType = NMOOracleDbType.Int32;
Parameters[17].Direction = NMOParameterDirection.Input;
Parameters[17].Value = para12ID;
Parameters[18] = new NMOOracleParameter();
Parameters[18].ParameterName = "p_copyToID";
Parameters[18].OracleDbType = NMOOracleDbType.Int32;
Parameters[18].Direction = NMOParameterDirection.Input;
Parameters[18].Value = copyToID;
Parameters[19] = new NMOOracleParameter();
Parameters[19].ParameterName = "p_to";
Parameters[19].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[19].Direction = NMOParameterDirection.Input;
Parameters[19].Value = toText;
Parameters[20] = new NMOOracleParameter();
Parameters[20].ParameterName = "p_subject";
Parameters[20].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[20].Direction = NMOParameterDirection.Input;
Parameters[20].Value = subject;
Parameters[21] = new NMOOracleParameter();
Parameters[21].ParameterName = "p_reference";
Parameters[21].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[21].Direction = NMOParameterDirection.Input;
Parameters[21].Value = reference;
Parameters[22] = new NMOOracleParameter();
Parameters[22].ParameterName = "p_enclosure";
Parameters[22].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[22].Direction = NMOParameterDirection.Input;
Parameters[22].Value = enclosure;
Parameters[23] = new NMOOracleParameter();
Parameters[23].ParameterName = "p_paragraph1";
Parameters[23].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[23].Direction = NMOParameterDirection.Input;
Parameters[23].Value = paragraph1;
Parameters[24] = new NMOOracleParameter();
Parameters[24].ParameterName = "p_paragraph2";
Parameters[24].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[24].Direction = NMOParameterDirection.Input;
Parameters[24].Value = paragraph2;
Parameters[25] = new NMOOracleParameter();
Parameters[25].ParameterName = "p_paragraph3";
Parameters[25].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[25].Direction = NMOParameterDirection.Input;
Parameters[25].Value = paragraph3;
Parameters[26] = new NMOOracleParameter();
Parameters[26].ParameterName = "p_paragraph4";
Parameters[26].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[26].Direction = NMOParameterDirection.Input;
Parameters[26].Value = paragraph4;
Parameters[27] = new NMOOracleParameter();
Parameters[27].ParameterName = "p_paragraph5";
Parameters[27].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[27].Direction = NMOParameterDirection.Input;
Parameters[27].Value = paragraph5;
Parameters[28] = new NMOOracleParameter();
Parameters[28].ParameterName = "p_paragraph6";
Parameters[28].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[28].Direction = NMOParameterDirection.Input;
Parameters[28].Value = paragraph6;
Parameters[29] = new NMOOracleParameter();
Parameters[29].ParameterName = "p_paragraph7";
Parameters[29].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[29].Direction = NMOParameterDirection.Input;
Parameters[29].Value = paragraph7;
Parameters[30] = new NMOOracleParameter();
Parameters[30].ParameterName = "p_paragraph8";
Parameters[30].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[30].Direction = NMOParameterDirection.Input;
Parameters[30].Value = paragraph8;
Parameters[31] = new NMOOracleParameter();
Parameters[31].ParameterName = "p_paragraph9";
Parameters[31].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[31].Direction = NMOParameterDirection.Input;
Parameters[31].Value = paragraph9;
Parameters[32] = new NMOOracleParameter();
Parameters[32].ParameterName = "p_paragraph10";
Parameters[32].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[32].Direction = NMOParameterDirection.Input;
Parameters[32].Value = paragraph10;
Parameters[33] = new NMOOracleParameter();
Parameters[33].ParameterName = "p_paragraph11";
Parameters[33].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[33].Direction = NMOParameterDirection.Input;
Parameters[33].Value = paragraph11;
Parameters[34] = new NMOOracleParameter();
Parameters[34].ParameterName = "p_paragraph12";
Parameters[34].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[34].Direction = NMOParameterDirection.Input;
Parameters[34].Value = paragraph12;
Parameters[35] = new NMOOracleParameter();
Parameters[35].ParameterName = "p_copyTo";
Parameters[35].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[35].Direction = NMOParameterDirection.Input;
Parameters[35].Value = copyTo;
Parameters[36] = new NMOOracleParameter();
Parameters[36].ParameterName = "p_sigPath";
Parameters[36].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[36].Direction = NMOParameterDirection.Input;
Parameters[36].Value = sigPath;
Parameters[37] = new NMOOracleParameter();
Parameters[37].ParameterName = "p_closed";
Parameters[37].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[37].Direction = NMOParameterDirection.Input;
Parameters[37].Value = closed;
Parameters[38] = new NMOOracleParameter();
Parameters[38].ParameterName = "p_result";
Parameters[38].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[38].Direction = NMOParameterDirection.Output;
Parameters[38].Size = 4000;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("UPDATE_LETTER", Parameters);
logger.Debug("UPDATE_LETTER Result: " + Parameters[38].Value.ToString());
return Parameters[38].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public String AddCaseLog(int caseID, int userID, int statusID)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[4];
try
{
database.OpenConnection();
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_CASE_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_USER_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = userID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_STATUS_ID";
Parameters[2].OracleDbType = NMOOracleDbType.Int32;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = statusID;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_result";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 128;
Parameters = database.ExecuteStoredProcedure("ADD_CASE_LOG", Parameters);
logger.Debug("AddCaseLog Result: " + Parameters[3].Value.ToString());
return Parameters[3].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public String AddBranch(string shortname, string longname)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[3];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_short_name";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = shortname;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_long_name";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = longname;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_result";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Output;
Parameters[2].Size = 128;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("add_branch", Parameters);
logger.Debug("AddBranch Result: " + Parameters[2].Value.ToString());
return Parameters[2].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <param name="edipi"></param>
/// <param name="titleOfTableDesired"></param>
/// <param name="uid"></param>
/// <returns></returns>
internal DataSet GetInfo(Int64 edipi, String titleOfTableDesired, Int64 uid)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[7];
//title table
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_title";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = titleOfTableDesired;
//case_id
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_case_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = 0;
//user_id
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_user_id";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = uid;
//edipi
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_edipi";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = edipi.ToString();
//branch_id
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_branch_id";
Parameters[4].OracleDbType = NMOOracleDbType.Int64;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = 0;
//relationship_id
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_relationship_id";
Parameters[5].OracleDbType = NMOOracleDbType.Int64;
Parameters[5].Direction = NMOParameterDirection.Input;
Parameters[5].Value = 0;
Parameters[6] = new NMOOracleParameter();
Parameters[6].ParameterName = "p_cur";
Parameters[6].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[6].Direction = NMOParameterDirection.Output;
try
{
dbsdw1.OpenConnection();
return dbsdw1.GetDataSet("PKG_ICM_UTILS.GET_INFO2", Parameters);
}
catch (Exception e)
{
logger.Fatal("BaseClass.GetInfo exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
public string GetCTRLviaCaseID(int _CaseID)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_CASE_ID";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _CaseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_CTRL_NUM";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 10;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("CONVERT_CASE_ID_TO_CTRL", Parameters);
logger.Debug("GetCTRLviaCaseID Result: " + Parameters[1].Value.ToString());
return Parameters[1].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
/// Overloaded to include a justification for writing to a note
/// </summary>
/// <param name="type"></param>
/// <param name="case_id"></param>
/// <param name="user_id"></param>
/// <param name="justification"></param>
/// <returns></returns>
public string CaseAction(string type, Int64 case_id, Int64 user_id, String justification)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_type";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = type;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_case_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = case_id;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_user_id";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = user_id;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_result";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 4000;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_JUSTIFICATION";
Parameters[4].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = justification;
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.case_action", Parameters);
logger.Debug("CaseAction Result: " + Parameters[3].Value.ToString());
return Parameters[3].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("CaseClass.CaseAction 2 exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
public DataSet GetData(string titleOfTableDesired)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_title";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = titleOfTableDesired;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_cur";
Parameters[1].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[1].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("get_data", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
private void addCaseLog(long case_id, long user_id)
{
String result = "";
NMOOracleParameter[] Parameters = new NMOOracleParameter[4];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_case_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = case_id;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_user_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = user_id;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_STATUS_ID";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = SpecDutyBase.gv.WW_ACCESSED_STATUS_ID;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_RESULT";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 4000;
try
{
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.ADD_CASE_LOG", Parameters);
result = Parameters[3].Value.ToString();
logger.Debug("AddCaseLog Result: " + result);
}
catch (Exception aclE)
{
logger.Error("AddCaseLog FAILED: " + aclE.Message + aclE.StackTrace);
}
}
public String GetDefaultOptions(string titleOfOptionDesired)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_option";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = titleOfOptionDesired;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_option_value";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 256;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("get_option", Parameters);
logger.Debug("GetDefaultOptions for " + titleOfOptionDesired + " Result: " + Parameters[1].Value.ToString());
return Parameters[1].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <param name="_CTRLNumber"></param>
/// <returns></returns>
public String GetCaseIDviaCTRL(String _CTRLNumber)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_CTRL_NUM";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _CTRLNumber;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_CASE_ID";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Output;
try
{
dbsdw1.OpenConnection();
Parameters = dbsdw1.ExecuteStoredProcedure("PKG_ICM_CASES.CONVERT_CTRL_TO_CASE_ID", Parameters);
logger.Debug("GetCaseIDviaCTRL Result: " + Parameters[1].Value.ToString());
return Parameters[1].Value.ToString();
}
catch (Exception e)
{
logger.Fatal("BaseClass.GetCaseIDviaCTRL exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
/*
* We have to notify a PRP or SRP occassionally, so we need to look up individual
* e-mail addresses.
*/
public string getEmailAddress(int userID)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_user_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = userID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_response";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 255;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("GET_EMAIL_ADDRESS", Parameters);
return Parameters[1].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <param name="titleOfTableDesired"></param>
/// <param name="caseID"></param>
/// <param name="userID"></param>
/// <param name="edipi"></param>
/// <param name="branchID"></param>
/// <param name="relationshipID"></param>
/// <returns></returns>
public DataSet GetInfo2(String titleOfTableDesired, Int64 caseID, Int64 userID, String edipi, Int64 branchID, Int64 relationshipID)
{
DataSet test= GetInfo2_NEW( titleOfTableDesired, caseID, userID, edipi, branchID, relationshipID);
return test;
NMOOracleParameter[] Parameters = new NMOOracleParameter[7];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_title";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = titleOfTableDesired;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_case_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = caseID;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_user_id";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = userID;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_edipi";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = edipi;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_branch_id";
Parameters[4].OracleDbType = NMOOracleDbType.Int64;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = branchID;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_relationship_id";
Parameters[5].OracleDbType = NMOOracleDbType.Int64;
Parameters[5].Direction = NMOParameterDirection.Input;
Parameters[5].Value = relationshipID;
Parameters[6] = new NMOOracleParameter();
Parameters[6].ParameterName = "p_cur";
Parameters[6].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[6].Direction = NMOParameterDirection.Output;
try
{
NMOOracleDatabase dbsdw1 = new NMOOracleDatabase(dbConnSdw);
dbsdw1.OpenConnection();
DataSet ds = dbsdw1.GetDataSet("PKG_ICM_UTILS.GET_INFO2", Parameters);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow r = ds.Tables[0].Rows[0];
DataRow r2 = ds.Tables[0].Rows[0];
return ds;
}
else
{
return null;
}
}
catch (Exception e)
{
logger.Fatal("BaseClass.GetInfo2 exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
public DataSet GetRecommendationsReport(string reportMonth, int service, int dod_status, int dod_component)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_MONTH";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = reportMonth;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_SERVICE";
Parameters[1].OracleDbType = NMOOracleDbType.Int32;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = service;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_DOD_STATUS";
Parameters[2].OracleDbType = NMOOracleDbType.Int32;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = dod_status;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_DOD_COMPONENT";
Parameters[3].OracleDbType = NMOOracleDbType.Int32;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = dod_component;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_CUR";
Parameters[4].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[4].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("GET_RECOMMENDATIONS", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
/// Not Referenced.using findRecommendation
/// </summary>
/// <param name="reportMonth"></param>
/// <param name="service"></param>
/// <param name="dod_status"></param>
/// <param name="dod_component"></param>
/// <returns></returns>
public DataSet GetRecommendationsReport(String reportMonth, Int64 service, Int64 dod_status, Int64 dod_component)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_MONTH";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = reportMonth;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_SERVICE";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = service;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_DOD_STATUS";
Parameters[2].OracleDbType = NMOOracleDbType.Int64;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = dod_status;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_DOD_COMPONENT";
Parameters[3].OracleDbType = NMOOracleDbType.Int64;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = dod_component;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_CUR";
Parameters[4].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[4].Direction = NMOParameterDirection.Output;
try
{
dbsdw1.OpenConnection();
return dbsdw1.GetDataSet("PKG_ICM_REPORTS.GET_RECOMMENDATIONS", Parameters);
}
catch (Exception e)
{
logger.Fatal("BaseClass.GetRecommendationsReport exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <param name="titleOfTableDesired"></param>
/// <returns></returns>
internal DataSet GetData(String titleOfTableDesired)
{
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
//LKUP_PRIORITY
//DOC_TYPES_FOR_EXT_USE' , DOC_TYPES_FOR_WW_EXT_USE
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_title";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = titleOfTableDesired;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_cur";
Parameters[1].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[1].Direction = NMOParameterDirection.Output;
try
{
dbsdw1.OpenConnection();
return dbsdw1.GetDataSet("PKG_ICM_UTILS.GET_DATA", Parameters);
}
catch (Exception e)
{
logger.Fatal("BaseClass.GetData exception: " + e.Message + e.StackTrace);
throw;
}
finally
{
dbsdw1.CloseConnection();
}
}
public DataSet GetExternalDocTypes(int userID)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_user_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = userID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_cur";
Parameters[1].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[1].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("get_extDocTypes", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public DataSet GetSearch(string searchType, string searchTerm)
{
searchTerm = searchTerm.Replace("'", "''");
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[6];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_search_type";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = searchType;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_search_term";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = searchTerm;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_cur_dependent";
Parameters[2].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[2].Direction = NMOParameterDirection.Output;
Parameters[2].Size = 128;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "p_cur_sponsor";
Parameters[3].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[3].Direction = NMOParameterDirection.Output;
Parameters[3].Size = 128;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "p_cur_user";
Parameters[4].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[4].Direction = NMOParameterDirection.Output;
Parameters[4].Size = 128;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "p_cur_case";
Parameters[5].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[5].Direction = NMOParameterDirection.Output;
Parameters[5].Size = 128;
try
{
database.OpenConnection();
return database.GetDataSet("get_search", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
