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();
}
}
/// <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 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 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 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();
}
}
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();
}
}
public DataSet GetPersonnelTypes()
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[1];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_cur";
Parameters[0].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[0].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("get_personnel_type", Parameters);
}
catch
{
throw;
}
finally
{
database.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 GetOpenClosedCounts(string from, string to, int service, int dod_status, int dod_component)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[6];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "P_FROM_DATE";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = from;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "P_TO_DATE";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = to;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "P_SERVICE";
Parameters[2].OracleDbType = NMOOracleDbType.Int32;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = service;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_DOD_STATUS";
Parameters[3].OracleDbType = NMOOracleDbType.Int32;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = dod_status;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_DOD_COMPONENT";
Parameters[4].OracleDbType = NMOOracleDbType.Int32;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = dod_component;
Parameters[5] = new NMOOracleParameter();
Parameters[5].ParameterName = "P_CUR";
Parameters[5].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[5].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("GET_OPENCLOSED", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public DataSet GetOtherPriorities(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_cur";
Parameters[1].OracleDbType = NMOOracleDbType.RefCursor;
Parameters[1].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
return database.GetDataSet("LKUP_OTHER_PRIORITIES", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public DataSet GetLetterComponents(String component)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDB"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_component";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = component;
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_LETTER_COMPONENTS", Parameters);
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
public String GetifUserRegistered(string _edipi)
{
NMOOracleDatabase database = new NMOOracleDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["IncapDevDb"].ToString());
NMOOracleParameter[] Parameters = new NMOOracleParameter[2];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_edipi";
Parameters[0].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = _edipi;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_result";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Output;
Parameters[1].Size = 256;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("check_if_user_exist", Parameters);
logger.Debug("GetifUserRegistered for " + _edipi + " Result: " + Parameters[1].Value.ToString());
return Parameters[1].Value.ToString();
}
catch
{
throw;
}
finally
{
database.CloseConnection();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal List<ActionLog> getActionLog(int days)
{
NMOOracleDatabase database = new NMOOracleDatabase(dbConn);
string spname = "PKG_MM_LOGS.GET_ACTION_LOGS";
NMOParameterDirection pin = NMOParameterDirection.Input;
NMOParameterDirection pout = NMOParameterDirection.Output;
using (var conn = new OracleConnection(dbConn))
using (var command = new OracleCommand(spname, conn)
{
CommandType = CommandType.StoredProcedure
})
{
NMOOracleParameter[] parameters = new NMOOracleParameter[2];
parameters[0] = new NMOOracleParameter();
parameters[0].ParameterName = "P_ACTIONDATE";
parameters[0].OracleDbType = NMOOracleDbType.Date;
parameters[0].Direction = pin;// NMOParameterDirection.Input;
parameters[0].Value = DateTime.Now.AddDays(days);
parameters[1] = new NMOOracleParameter();
parameters[1].ParameterName = "P_ACTIONDATE";
parameters[1].OracleDbType = NMOOracleDbType.RefCursor;
parameters[1].Direction = pout;// NMOParameterDirection.Output;
// conn.Open();
DataSet ds = dbmm.GetDataSet(spname, parameters);
List<ActionLog> actionLogList = new List<ActionLog>();
IEnumerable<DataRow> zz = ds.Tables[0].AsEnumerable();
//List<ActionLog> rows = ds.Tables[0].Rows.Cast<ActionLog>().ToList();
foreach (DataRow dr in zz)
{
ActionLog al = new ActionLog();
al.action_id = Convert.ToInt32(dr["ACTION_ID"]);
al.action_note = dr["ACTION_NOTE"].ToString();
al.action_type = dr["ACTION_TYPE"].ToString();
DateTime dtCreated = DateTime.Parse(dr["DT_CREATED"].ToString());
al.dt_created = dtCreated.ToString("yyy/M/dd HHmm");
// al.dt_created = dr["DT_CREATED"].ToString();
al.user_id = dr["USER_ID"].ToString();
al.actionBy = dr["ACTIONBY"].ToString();
al.actionFor = dr["ACTIONFOR"].ToString();
al.midnId = dr["MIDN_ID"].ToString();
actionLogList.Add(al);
}
return actionLogList;
}
}
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 getPrpOrSrpID(int caseID, string type)
{
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.Int32;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = caseID;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_type";
Parameters[1].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = type;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_id";
Parameters[2].OracleDbType = NMOOracleDbType.Int32;
Parameters[2].Direction = NMOParameterDirection.Output;
try
{
database.OpenConnection();
Parameters = database.ExecuteStoredProcedure("get_prp_or_srp_by_id", Parameters);
return Parameters[2].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 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();
}
}
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 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();
}
}
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();
}
}
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();
}
}
/*
* 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();
}
}
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>
/// <returns></returns>
internal Boolean isIcmAvailable()
{
NMOOracleDatabase dbsdw1 = new NMOOracleDatabase(dbConnSdw);
try
{
if (!dbsdw1.ConnectionOpen)
dbsdw1.OpenConnection();
return true;
}
catch (OracleException ex)
{
logger.Error("Exception caught in SpecDutyBase:isIcmAvailable: ",ex);// + ex.Message);
return false;
}
finally
{
dbsdw1.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 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();
}
}
}
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();
}
}
/// <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();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="user_id"></param>
/// <param name="action_id"></param>
/// <param name="actionNote"></param>
/// <param name="actionType"></param>
/// <param name="midnId"></param>
internal void addActionLog(long user_id, long action_id, string actionNote, string actionType, long midnId)
{
String result = "";
NMOOracleParameter[] Parameters = new NMOOracleParameter[5];
Parameters[0] = new NMOOracleParameter();
Parameters[0].ParameterName = "p_user_id";
Parameters[0].OracleDbType = NMOOracleDbType.Int64;
Parameters[0].Direction = NMOParameterDirection.Input;
Parameters[0].Value = user_id;
Parameters[1] = new NMOOracleParameter();
Parameters[1].ParameterName = "p_action_id";
Parameters[1].OracleDbType = NMOOracleDbType.Int64;
Parameters[1].Direction = NMOParameterDirection.Input;
Parameters[1].Value = action_id;
Parameters[2] = new NMOOracleParameter();
Parameters[2].ParameterName = "p_action_note";
Parameters[2].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[2].Direction = NMOParameterDirection.Input;
Parameters[2].Value = actionNote;
Parameters[3] = new NMOOracleParameter();
Parameters[3].ParameterName = "P_ACTION_TYPE";
Parameters[3].OracleDbType = NMOOracleDbType.Varchar2;
Parameters[3].Direction = NMOParameterDirection.Input;
Parameters[3].Value = actionType;
Parameters[4] = new NMOOracleParameter();
Parameters[4].ParameterName = "P_MIDN_ID";
Parameters[4].OracleDbType = NMOOracleDbType.Int64;
Parameters[4].Direction = NMOParameterDirection.Input;
Parameters[4].Value = midnId;
NMOOracleDatabase database = new NMOOracleDatabase(dbConn);
try
{
Parameters = database.ExecuteStoredProcedure("PKG_MM_LOGS.LOG_ACTION", Parameters);
// result = Parameters[3].Value.ToString();
logger.Debug("AddActionLog Result: " + result);
}
catch (Exception aclE)
{
logger.Error("Exception caught in AddActionLog : ", aclE );// + aclE.Message + aclE.StackTrace);
}
}
