/// <summary>
///
/// </summary>
///
/// <param name="view_cases_title"></param>
/// <returns></returns>
/// use
internal List<WaiverStatus> getWaiverStatusViewForExt_using_viewcases(string view_cases_title)
{
OracleDataReader reader = null;
List<WaiverStatus> wvStatList = new List<WaiverStatus>();
String spName = "PKG_ICM_CASES.VIEW_CASES";
String spTitle = view_cases_title;
// "VIEW_CLOSED_EXT" VIEW_NEW_EXT view_routine_ext, view_urgent_ext;
Int64 userId = SpecDutyBase.gv.WW_USERLONG;
//Int64 branchId = WW_BRANCHID;// 0;
Int64 edipiInt = SpecDutyBase.gv.MEDMID_INTERFACE_EDIPI;
String userIdString = edipiInt.ToString();
String lastLogin = getLastLoginDate(userIdString);
ParameterDirection inpd = ParameterDirection.Input;
ParameterDirection outpd = ParameterDirection.Output;
OracleDbType VCHAR2 = OracleDbType.Varchar2;
OracleDbType INT64 = OracleDbType.Int64;
using (var conn = new OracleConnection(dbConnSdw))
using (var command = new OracleCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
command.BindByName = true;
command.Parameters.Add("P_TITLE", VCHAR2, inpd).Value = spTitle;
command.Parameters.Add("P_USER_ID", INT64, inpd).Value = userId;
command.Parameters.Add("P_CUR", OracleDbType.RefCursor, outpd);
command.Parameters.Add("P_LASTLOGIN", VCHAR2, inpd).Value = lastLogin;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
WaiverStatus wvStat = new WaiverStatus();
wvStat.caseStatus = String.Format("{0}", reader["OFFICIAL_CASE_STATUS"]);
wvStat.system = Convert.ToString(reader["SYSTEM"]);
wvStat.waiverCtrlNum = reader["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(reader["CASE_ID"]);
wvStat.stepName = reader["STATE"].ToString();
// wvStat.dodStatus = "xDOD_STATUS";
// wvStat.dodComponent = "xDOD_COMPONENT";
wvStat.priority = reader["PRIORITY"].ToString();
wvStat.assignedSrp = reader["ASSIGNED_SRP"].ToString();
wvStat.assignedPrp = reader["ASSIGNED_PRP"].ToString();
// wvStat.updateStatus = "xUPDATE_STATUS";
wvStat.service = reader["SERVICE"].ToString();
// wvStat.status = "xSTATUS";
// wvStat.determination = "xDETERMINATION";
wvStat.createDate = Convert.ToDateTime(reader["CREATE_DATE"]);
wvStat.createDateStr = wvStat.createDate.ToString("s");
wvStat.closedDateStr = "";
if ( !wvStat.stepName.Equals("DELETED") && spTitle.Equals("VIEW_CLOSED_EXT") )// && reader["DATE_CLOSED"] != System.DBNull.Value)
{
var incap = reader["DATE_INCAP_CLOSED"].ToString();
var ww = reader["DATE_WW_CLOSED"].ToString();
var reab = reader["DATE_REAB_CLOSED"].ToString();
var sdw = reader["DATE_SDW_CLOSED"].ToString();
var dt = reader["DATE_CLOSED"].ToString();
var del = reader["DATE_DELETED"].ToString();
string incapClosedDate = string.IsNullOrEmpty(incap)?null:incap;
string wwClosedDate = string.IsNullOrEmpty(ww)?null:ww;
string reabClosedDate = string.IsNullOrEmpty(reab)?null:reab;
string sdwClosedDate = string.IsNullOrEmpty(sdw)?null:sdw;
logger.Debug("Closed: Incap- " + incap + " ww- " + ww + " reab-" + reab + " sdw-" + sdw + " dt_closed-" + dt + "dt_deleted" + del);
if ( wvStat.stepName.Equals("CANCELLED") )
{
wvStat.closedDate = Convert.ToDateTime(reader["DATE_CLOSED"]);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
}else{
if (wwClosedDate != null) //(wvStat.system.Equals("WW"))
{
wvStat.closedDate = Convert.ToDateTime(wwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.wwClosedDateStr = "WW- "+ wvStat.closedDate.ToString("s");
}
if (sdwClosedDate != null)// (wvStat.system.Equals("SDW"))
{
wvStat.closedDate = Convert.ToDateTime(sdwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.sdwClosedDateStr = "SDW- "+ wvStat.closedDate.ToString("s");
}
if (reabClosedDate != null) //(wvStat.system.Equals("REAB"))
{
wvStat.closedDate = Convert.ToDateTime(reabClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.reabClosedDateStr = "REAB- "+ wvStat.closedDate.ToString("s");
}
}
}
//sponsor
wvStat.sponSsn = Convert.ToString(reader["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(reader["SSN"]);
wvStat.sponName = Convert.ToString(reader["SPON_LNAME"]) + ", " + Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponFname = Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponLname = Convert.ToString(reader["SPON_LNAME"]);
wvStat.reportWaivers = "xREPORT_WAIVERS";
if (!wvStat.caseStatus.Equals("DELETED"))
{
wvStatList.Add(wvStat);
}
}
}
catch (Exception e)
{
logger.Fatal("SpecDutyBase.getWaiverStatusViewNewExt exception: " + e.Message , e);
throw;
}
conn.Close();
}
return wvStatList;
// List<WaiverStatus> newCaseStatus = getCaseStatusForExt(view_cases_title);
// return newCaseStatus;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal List<WaiverStatus> getWaiverStatusCreatedByMedMids()
{
String spTitle = "VIEW_NEW_EXT";
spTitle = "VIEW_CLOSED_EXT";
// return getWaiverStatusViewNewExt();
return getWaiverStatusViewForExt(spTitle);
//ctrlNum not used..refactor to remove
List<WaiverStatus> wvStatList = new List<WaiverStatus>();
string title = "VIEW_CASES_VIA_CREATED_BY_ID";
Int64 caseId = 0; // Convert.ToInt64(GetCaseIDviaCTRL(ctrlNum));
Int64 userID = 1056; // WW_USERLONG;
String edipi = "";
Int64 branchId = 0;
Int64 relationshipID = 0;
DataSet singleCaseStatus = GetInfo2(title, caseId, userID, edipi, branchId, relationshipID);
//WaiverStatus wvStat = new WaiverStatus();
int count = singleCaseStatus.Tables[0].Rows.Count;
for (int i = 0; i < count; i++)
{
DataRow r = singleCaseStatus.Tables[0].Rows[i];
WaiverStatus wvStat = new WaiverStatus();
wvStat.system = Convert.ToString(r["SYSTEM"]);
wvStat.waiverCtrlNum = r["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(r["CASE_ID"]);
wvStat.stepName = r["STEP_NAME"].ToString();
wvStat.dodStatus = r["DOD_STATUS"].ToString();
wvStat.dodComponent = r["DOD_COMPONENT"].ToString();
wvStat.priority = r["PRIORITY"].ToString();
wvStat.assignedSrp = r["ASSIGNED_SRP"].ToString();
wvStat.assignedPrp = r["ASSIGNED_PRP"].ToString();
wvStat.updateStatus = r["UPDATE_STATUS"].ToString();
wvStat.service = r["SERVICE"].ToString();
wvStat.caseStatus = r["STATUS"].ToString();
// wvStat.determination = r["DETERMINATION"].ToString();
//dates
wvStat.createDate = Convert.ToDateTime(r["CREATE_DATE"]);
wvStat.dueDate = Convert.ToDateTime(r["DUE_DATE"]);
wvStat.updateDate = Convert.ToDateTime(r["UPDATE_DATE"]);
// wvStat.closedDate = Convert.ToDateTime(r["UPDATE_DATE"]);
//sponsor
wvStat.sponSsn = Convert.ToString(r["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(r["FULL_SPON_SSN"]);
wvStat.sponName = Convert.ToString(r["SPONSOR_NAME"]);
wvStat.reportWaivers = Convert.ToString(r["REPORT_WAIVERS"]);
wvStatList.Add(wvStat);
}
return wvStatList;
}
/// <summary>
/// Uses Pkg_Icm_Cases.Getcases instead of pkg_icm_cases.view_cases
/// </summary>
/// <param name="view_cases_title"></param>
/// <returns></returns>
/// was getCaseStatusForExt()
internal List<WaiverStatus> getWaiverStatusViewForExt(string get_cases_title)
{
//ww_orgid
//ww_parentid
//New view_case_titles are 'EXT CLOSED' 'EXT NEW' 'EXT ROUTINE' 'EXT URGENT'
String newTitle = "";
switch (get_cases_title)
{
// case "VIEW_INFO_NEEDED_EXT": this is legacy medmids only. they close and change to remedial.
// newTitle = "EXT INFO NEEDED";
// break;
case "VIEW_CLOSED_EXT":
newTitle = "EXT CLOSED";
break;
case "VIEW_NEW_EXT":
newTitle = "EXT NEW";
break;
case "VIEW_ROUTINE_EXT":
newTitle = "EXT ROUTINE";
break;
case "VIEW_URGENT_EXT":
newTitle = "EXT URGENT";
break;
default:
break;
}
String spTitle = newTitle;
OracleDataReader reader = null;
List<WaiverStatus> wvStatList = new List<WaiverStatus>();
String spName = "PKG_ICM_CASES.GETCASES";
Int64 userId = SpecDutyBase.gv.WW_USERLONG;
Int64 profId = SpecDutyBase.gv.WW_PROFID;
Int64 edipiInt = SpecDutyBase.gv.MEDMID_INTERFACE_EDIPI;
Int64 branchId = SpecDutyBase.gv.WW_ORGID;
Int64 parentId = SpecDutyBase.gv.WW_PARENTID;
String userIdString = edipiInt.ToString();
String lastLogin = ""; // getLastLoginDate(userIdString);
lastLogin ="******";
logger.Debug("sptitle: "+spTitle+" spname: "+spName +" userId: "+userId+ " profId: "+profId + " branchId: "+branchId+" parentId: "+parentId);
logger.Debug("lastLogin: "******"P_TITLE", VCHAR2, inpd).Value = spTitle;
command.Parameters.Add("P_USER_ID", INT64, inpd).Value = userId;
command.Parameters.Add("P_PROF_ID", INT64, inpd).Value = profId;
command.Parameters.Add("P_LASTLOGIN", VCHAR2, inpd).Value = lastLogin;
command.Parameters.Add("P_HASINCAPRIGHTS", VCHAR2, inpd).Value = "F";
command.Parameters.Add("P_HASREABRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_HASSDWRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_HASWWRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_BRANCH_ID", INT64, inpd).Value = branchId;
command.Parameters.Add("P_PARENT_ID", INT64, inpd).Value = parentId;
command.Parameters.Add("P_CUR", OracleDbType.RefCursor, outpd);
command.Parameters.Add("P_WHICH", VCHAR2, inpd).Value = "all";
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
WaiverStatus wvStat = new WaiverStatus();
wvStat.caseStatus = String.Format("{0}", reader["OFFICIAL_CASE_STATUS"]);
wvStat.system = Convert.ToString(reader["SYSTEM"]);
wvStat.waiverCtrlNum = reader["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(reader["CASE_ID"]);
wvStat.stepName = reader["STATE"].ToString();
// wvStat.dodStatus = "xDOD_STATUS";
// wvStat.dodComponent = "xDOD_COMPONENT";
wvStat.priority = reader["PRIORITY"].ToString();
wvStat.assignedSrp = reader["ASSIGNED_SRP"].ToString();
wvStat.assignedPrp = reader["ASSIGNED_PRP"].ToString();
// wvStat.updateStatus = "xUPDATE_STATUS";
wvStat.service = reader["SERVICE"].ToString();
// wvStat.status = "xSTATUS";
// wvStat.determination = "xDETERMINATION";
wvStat.createDate = Convert.ToDateTime(reader["CREATE_DATE"]);
wvStat.createDateStr = wvStat.createDate.ToString("s");
wvStat.closedDateStr = "";
// logger.Debug("found case");
if (!wvStat.stepName.Equals("DELETED") && spTitle.Equals("EXT CLOSED"))// && reader["DATE_CLOSED"] != System.DBNull.Value)
{
logger.Debug("not deleted and title is ext closed");
var incap = reader["DATE_INCAP_CLOSED"].ToString();
var ww = reader["DATE_WW_CLOSED"].ToString();
var reab = reader["DATE_REAB_CLOSED"].ToString();
var sdw = reader["DATE_SDW_CLOSED"].ToString();
var dt = reader["DATE_CLOSED"].ToString();
var del = reader["DATE_DELETED"].ToString();
string incapClosedDate = string.IsNullOrEmpty(incap) ? null : incap;
string wwClosedDate = string.IsNullOrEmpty(ww) ? null : ww;
string reabClosedDate = string.IsNullOrEmpty(reab) ? null : reab;
string sdwClosedDate = string.IsNullOrEmpty(sdw) ? null : sdw;
logger.Debug("Closed: Incap- " + incap + " ww- " + ww + " reab-" + reab + " sdw-" + sdw + " dt_closed-" + dt + "dt_deleted" + del);
if (wvStat.stepName.Equals("CANCELLED"))
{
wvStat.closedDate = Convert.ToDateTime(reader["DATE_CLOSED"]);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
}
else
{
if (wwClosedDate != null) //(wvStat.system.Equals("WW"))
{
wvStat.closedDate = Convert.ToDateTime(wwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.wwClosedDateStr = "WW- " + wvStat.closedDate.ToString("s");
}
if (sdwClosedDate != null)// (wvStat.system.Equals("SDW"))
{
wvStat.closedDate = Convert.ToDateTime(sdwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.sdwClosedDateStr = "SDW- " + wvStat.closedDate.ToString("s");
}
if (reabClosedDate != null) //(wvStat.system.Equals("REAB"))
{
wvStat.closedDate = Convert.ToDateTime(reabClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.reabClosedDateStr = "REAB- " + wvStat.closedDate.ToString("s");
}
}
}
//sponsor
wvStat.sponSsn = Convert.ToString(reader["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(reader["SSN"]);
wvStat.sponName = Convert.ToString(reader["SPON_LNAME"]) + ", " + Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponFname = Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponLname = Convert.ToString(reader["SPON_LNAME"]);
wvStat.reportWaivers = "xREPORT_WAIVERS";
if (!wvStat.caseStatus.Equals("DELETED"))
{
wvStatList.Add(wvStat);
}
}
}
catch (Exception e)
{
logger.Fatal("SpecDutyBase.getCaseStatusForExt exception: " + e.Message , e);
throw;
}
logger.Debug("closing connection. state " );
conn.Close();
// conn.Dispose();
if (conn.State == ConnectionState.Open)
{
isOpen = true;
}
}
if (isOpen)
{
logger.Debug("Connection is still open.");
}
if (newTitle.Equals("EXT CLOSED"))
{
List<String> yrs = (List<String>)getActiv4Years();
string y0 = yrs.Count>0 && yrs[0] != null ? yrs[0] : null;
string y1 = yrs.Count > 1 && yrs[1] != null ? yrs[1] : null;
string y2 = yrs.Count > 2 && yrs[2] != null ? yrs[2] : null;
string y3 = yrs.Count > 3 && yrs[3] != null ? yrs[3] : null;
string y4 = yrs.Count > 4 && yrs[4] != null ? yrs[4] : null;
List<WaiverStatus> archivedCases = getArchWaiverStatusViewForExt(y0,y1,y2,y3,y4);
wvStatList.AddRange(archivedCases);
}
return wvStatList;
}
/// <summary>
/// Not used
/// </summary>
/// <param name="ctrlNum"></param>
/// <returns></returns>
internal WaiverStatus getWaiverStatusByCtrlNumNew(string ctrlNum)
{
var connectionString = dbConnSdw;
String spName = "GET_INFO";
WaiverStatus wvStat = new WaiverStatus();
WaiverNewReqRes reqResult = new WaiverNewReqRes();
ParameterDirection inpd = ParameterDirection.Input;
ParameterDirection outpd = ParameterDirection.Output;
OracleDbType VCHAR2 = OracleDbType.Varchar2;
OracleDbType INT64 = OracleDbType.Int64;
// OracleDbType DATE1 = OracleDbType.Date;
string sdMedMidUid = getUserId(SpecDutyBase.gv.MEDMID_INTERFACE_EDIPI);
Int64 caseId = Convert.ToInt64(GetCaseIDviaCTRL(ctrlNum));
using (var conn = new OracleConnection(connectionString))
using (var command = new OracleCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
command.BindByName = true;
command.Parameters.Add("P_TITLE", VCHAR2, inpd).Value = "VIEW_SINGLE_CASE_VIA_CASE_ID";
command.Parameters.Add("P_ID", INT64, inpd).Value = caseId;
command.Parameters.Add("P_CUR", OracleDbType.RefCursor, outpd);
// Execute command
// var data = db.Database.SqlQuery<Appointment>(sql, new OracleParameter(":i", id));
// return dbsdw1.GetDataSet("get_info", Parameters);
OracleDataReader reader;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
wvStat.dodStatus = String.Format("{0}", reader[28]);
// wvStat.waiverCaseId= (String.Format("{0}", reader[0]));
wvStat.updateStatus = String.Format("{0}", reader[49]);
wvStat.priority = String.Format("{0}", reader[33]);
}
}
catch (Exception e)
{
logger.Fatal("SpecDutyBase.getWaiverStatusByCtrlNumNew exception: " + e.Message + e.StackTrace);
throw;
}
conn.Close();
}
return wvStat; //create a waiverstatus obj
}
//
//
internal WaiverStatus getTheWaiverStatusByCtrlNum(String ctrlNum)
{
string title = "VIEW_SINGLE_CASE_VIA_CASE_ID";
Int64 caseId = Convert.ToInt64(GetCaseIDviaCTRL(ctrlNum));
Int64 userID = SpecDutyBase.gv.WW_USERLONG;
String edipi = "";
Int64 branchId = 0;
Int64 relationshipID = 0;
//GetInfo2
DataSet singleCaseStatus = GetInfo2(title, caseId, userID, edipi, branchId, relationshipID);
if (singleCaseStatus == null)
{
return null;
}
int count = singleCaseStatus.Tables[0].Rows.Count;
WaiverStatus wvStat = new WaiverStatus();
if (count == 1)
{
DataRow r = singleCaseStatus.Tables[0].Rows[0];
wvStat.system = Convert.ToString(r["SYSTEM"]);
wvStat.waiverCtrlNum = r["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(r["CASE_ID"]);
wvStat.stepName = r["STEP_NAME"].ToString();
wvStat.dodStatus = "xDOD_STATUS";// r["DOD_STATUS"].ToString();
wvStat.dodComponent = r["DOD_COMPONENT"].ToString();
wvStat.priority = r["PRIORITY"].ToString();
wvStat.assignedSrp = r["ASSIGNED_SRP"].ToString();
wvStat.assignedPrp = r["ASSIGNED_PRP"].ToString();
wvStat.updateStatus = r["UPDATE_STATUS"].ToString();
wvStat.service = r["SERVICE"].ToString();
wvStat.caseStatus = r["STATUS"].ToString();
// wvStat.determination = r["DETERMINATION"].ToString();
//dates
wvStat.createDate = Convert.ToDateTime(r["CREATE_DATE"]);
wvStat.dueDate = Convert.ToDateTime(r["DUE_DATE"]);
wvStat.updateDate = Convert.ToDateTime(r["UPDATE_DATE"]);
//sponsor
wvStat.sponSsn = Convert.ToString(r["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(r["FULL_SPON_SSN"]);
wvStat.sponName = Convert.ToString(r["SPONSOR_NAME"]);
wvStat.reportWaivers = Convert.ToString(r["REPORT_WAIVERS"]);
}
return wvStat;
}
internal List<WaiverStatus> getArchWaiverStatusViewForExt(string year1,string year2,string year3,string year4,string year5)
{
OracleDataReader reader = null;
List<WaiverStatus> archivedCases = new List<WaiverStatus>();
String spName = "PKG_ICM_CASES.getmedmidsarchivedcases";
ParameterDirection inpd = ParameterDirection.Input;
ParameterDirection outpd = ParameterDirection.Output;
OracleDbType VCHAR2 = OracleDbType.Varchar2;
using (var conn = new OracleConnection(dbConnSdw))
using (var command = new OracleCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
command.BindByName = true;
command.Parameters.Add("P_CUR", OracleDbType.RefCursor, outpd);
command.Parameters.Add("pYear1", VCHAR2, inpd).Value = year1;
command.Parameters.Add("pYear2", VCHAR2, inpd).Value = year2;
command.Parameters.Add("pYear3", VCHAR2, inpd).Value = year3;
command.Parameters.Add("pYear4", VCHAR2, inpd).Value = year4;
command.Parameters.Add("pYear5", VCHAR2, inpd).Value = year5;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
WaiverStatus wvStat = new WaiverStatus();
wvStat.caseStatus = String.Format("{0}", reader["OFFICIAL_CASE_STATUS"]);
wvStat.system = Convert.ToString(reader["SYSTEM"]);
wvStat.waiverCtrlNum = reader["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(reader["CASE_ID"]);
if ( ! string.IsNullOrEmpty(reader["DATE_CLOSED"].ToString()) )
{
wvStat.closedDate = Convert.ToDateTime(reader["DATE_CLOSED"]);
}
else
{
wvStat.closedDate = DateTime.Today;
}
// wvStat.caseStatus = reader["STATE"].ToString();
wvStat.stepName = reader["STATE"].ToString();
var incap = reader["DATE_INCAP_CLOSED"].ToString();
var ww = reader["DATE_WW_CLOSED"].ToString();
var reab = reader["DATE_REAB_CLOSED"].ToString();
var sdw = reader["DATE_SDW_CLOSED"].ToString();
var dt = reader["DATE_CLOSED"].ToString();
var del = reader["DATE_DELETED"].ToString();
string incapClosedDate = string.IsNullOrEmpty(incap) ? null : incap;
string wwClosedDate = string.IsNullOrEmpty(ww) ? null : ww;
string reabClosedDate = string.IsNullOrEmpty(reab) ? null : reab;
string sdwClosedDate = string.IsNullOrEmpty(sdw) ? null : sdw;
if (wwClosedDate != null) //(wvStat.system.Equals("WW"))
{
wvStat.closedDate = Convert.ToDateTime(wwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.wwClosedDateStr = "WW- " + wvStat.closedDate.ToString("s");
}
if (sdwClosedDate != null)// (wvStat.system.Equals("SDW"))
{
wvStat.closedDate = Convert.ToDateTime(sdwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.sdwClosedDateStr = "SDW- " + wvStat.closedDate.ToString("s");
}
if (reabClosedDate != null) //(wvStat.system.Equals("REAB"))
{
wvStat.closedDate = Convert.ToDateTime(reabClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.reabClosedDateStr = "REAB- " + wvStat.closedDate.ToString("s");
}
//sponsor
wvStat.sponSsn = Convert.ToString(reader["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(reader["SSN"]);
wvStat.sponName = Convert.ToString(reader["SPON_LNAME"]) + ", " + Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponFname = Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponLname = Convert.ToString(reader["SPON_LNAME"]);
archivedCases.Add(wvStat);
}
}
catch (Exception e)
{
logger.Error(e.GetBaseException());
}
}
return archivedCases;
}
/// <summary>
/// Uses Pkg_Icm_Cases.Getcases instead of pkg_icm_cases.view_cases
/// </summary>
/// <param name="view_cases_title"></param>
/// <returns></returns>
/// was getCaseStatusForExt()
internal List<WaiverStatus> getWaiverStatusViewForExt(string get_cases_title)
{
//ww_orgid
//ww_parentid
//New view_case_titles are 'EXT CLOSED' 'EXT NEW' 'EXT ROUTINE' 'EXT URGENT'
String newTitle = "";
switch (get_cases_title)
{
case "VIEW_CLOSED_EXT":
newTitle = "EXT CLOSED";
break;
case "VIEW_NEW_EXT":
newTitle = "EXT NEW";
break;
case "VIEW_ROUTINE_EXT":
newTitle = "EXT ROUTINE";
break;
case "VIEW_URGENT_EXT":
newTitle = "EXT URGENT";
break;
default:
break;
}
String spTitle = newTitle;
/**
* P_TITLE VARCHAR2(200);
* P_USER_ID NUMBER;
P_PROF_ID NUMBER;
* P_LASTLOGIN VARCHAR2(200);
* // select Pkg_Icm_Users.extUserHasSystemRights(55,'SDW') from dual;
* P_HASINCAPRIGHTS VARCHAR2(200); default 'F'
* P_HASREABRIGHTS VARCHAR2(200); default 'F'
* P_HASSDWRIGHTS VARCHAR2(200); default 'F'
* P_HASWWRIGHTS VARCHAR2(200); default 'F'
* P_BRANCH_ID NUMBER;
* P_PARENT_ID NUMBER;
* P_CUR SYS_REFCURSOR;
P_WHICH VARCHAR2(200); default 31
*/
OracleDataReader reader = null;
List<WaiverStatus> wvStatList = new List<WaiverStatus>();
String spName = "PKG_ICM_CASES.GETCASES";
Int64 userId = SpecDutyBase.gv.WW_USERLONG;
Int64 profId = SpecDutyBase.gv.WW_PROFID;
Int64 edipiInt = SpecDutyBase.gv.MEDMID_INTERFACE_EDIPI;
Int64 branchId = SpecDutyBase.gv.WW_ORGID;
Int64 parentId = SpecDutyBase.gv.WW_PARENTID;
String userIdString = edipiInt.ToString();
String lastLogin = getLastLoginDate(userIdString);
ParameterDirection inpd = ParameterDirection.Input;
ParameterDirection outpd = ParameterDirection.Output;
OracleDbType VCHAR2 = OracleDbType.Varchar2;
OracleDbType INT64 = OracleDbType.Int64;
using (OracleConnection conn = new OracleConnection(dbConnSdw))
{
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
conn.Open();
/**
*# case_id,
*# official_case_status,
* owner,
*# system,
*# state,
*# ctrl_number,
*# priority
*# spon_lname,spon_fname,
* spon_mname,
* spon_suffix
* dependent_name
*# create_date,
* lateststatus
*# service,
*# assigned_prp, assigned_srp,
* sponsor_dob, islegacy,dependent_dob
* creator, personneltype,
* tycom_bypassed, istycom,parentistycom, spon_rank
* organization
* dependent_edipi, sponsor_edipi,
*# spon_ssn, ssn,
*# date_incap_closed, date_ww_closed, date_reab_closed, date_closed, date_sdw_closed, date_deleted
* skip 10 fields..
*/
command.Parameters.Add("P_TITLE", VCHAR2, inpd).Value = spTitle;
command.Parameters.Add("P_USER_ID", INT64, inpd).Value = userId;
command.Parameters.Add("P_PROF_ID", INT64, inpd).Value = profId;
command.Parameters.Add("P_LASTLOGIN", VCHAR2, inpd).Value = lastLogin;
command.Parameters.Add("P_HASINCAPRIGHTS", VCHAR2, inpd).Value = "F";
command.Parameters.Add("P_HASREABRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_HASSDWRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_HASWWRIGHTS",VCHAR2,inpd).Value="T";
command.Parameters.Add("P_BRANCH_ID", INT64, inpd).Value = branchId;
command.Parameters.Add("P_PARENT_ID", INT64, inpd).Value = parentId;
command.Parameters.Add("P_CUR", OracleDbType.RefCursor, outpd);
command.Parameters.Add("P_WHICH", VCHAR2, inpd).Value = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
WaiverStatus wvStat = new WaiverStatus();
wvStat.caseStatus = String.Format("{0}", reader["OFFICIAL_CASE_STATUS"]);
wvStat.system = Convert.ToString(reader["SYSTEM"]);
wvStat.waiverCtrlNum = reader["CTRL_NUMBER"].ToString();
wvStat.waiverCaseId = Convert.ToInt64(reader["CASE_ID"]);
wvStat.stepName = reader["STATE"].ToString();
// wvStat.dodStatus = "xDOD_STATUS";
// wvStat.dodComponent = "xDOD_COMPONENT";
wvStat.priority = reader["PRIORITY"].ToString();
wvStat.assignedSrp = reader["ASSIGNED_SRP"].ToString();
wvStat.assignedPrp = reader["ASSIGNED_PRP"].ToString();
// wvStat.updateStatus = "xUPDATE_STATUS";
wvStat.service = reader["SERVICE"].ToString();
// wvStat.status = "xSTATUS";
// wvStat.determination = "xDETERMINATION";
wvStat.createDate = Convert.ToDateTime(reader["CREATE_DATE"]);
wvStat.createDateStr = wvStat.createDate.ToString("s");
wvStat.closedDateStr = "";
if (!wvStat.stepName.Equals("DELETED") && spTitle.Equals("EXT CLOSED"))// && reader["DATE_CLOSED"] != System.DBNull.Value)
{
var incap = reader["DATE_INCAP_CLOSED"].ToString();
var ww = reader["DATE_WW_CLOSED"].ToString();
var reab = reader["DATE_REAB_CLOSED"].ToString();
var sdw = reader["DATE_SDW_CLOSED"].ToString();
var dt = reader["DATE_CLOSED"].ToString();
var del = reader["DATE_DELETED"].ToString();
string incapClosedDate = string.IsNullOrEmpty(incap) ? null : incap;
string wwClosedDate = string.IsNullOrEmpty(ww) ? null : ww;
string reabClosedDate = string.IsNullOrEmpty(reab) ? null : reab;
string sdwClosedDate = string.IsNullOrEmpty(sdw) ? null : sdw;
logger.Debug("Closed: Incap- " + incap + " ww- " + ww + " reab-" + reab + " sdw-" + sdw + " dt_closed-" + dt + "dt_deleted" + del);
if (wvStat.stepName.Equals("CANCELLED"))
{
wvStat.closedDate = Convert.ToDateTime(reader["DATE_CLOSED"]);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
}
else
{
if (wwClosedDate != null) //(wvStat.system.Equals("WW"))
{
wvStat.closedDate = Convert.ToDateTime(wwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.wwClosedDateStr = "WW- " + wvStat.closedDate.ToString("s");
}
if (sdwClosedDate != null)// (wvStat.system.Equals("SDW"))
{
wvStat.closedDate = Convert.ToDateTime(sdwClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.sdwClosedDateStr = "SDW- " + wvStat.closedDate.ToString("s");
}
if (reabClosedDate != null) //(wvStat.system.Equals("REAB"))
{
wvStat.closedDate = Convert.ToDateTime(reabClosedDate);
wvStat.closedDateStr = wvStat.closedDate.ToString("s");
wvStat.reabClosedDateStr = "REAB- " + wvStat.closedDate.ToString("s");
}
}
}
//sponsor
wvStat.sponSsn = Convert.ToString(reader["SPON_SSN"]);
wvStat.sponFullSsn = Convert.ToString(reader["SSN"]);
wvStat.sponName = Convert.ToString(reader["SPON_LNAME"]) + ", " + Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponFname = Convert.ToString(reader["SPON_FNAME"]);
wvStat.sponLname = Convert.ToString(reader["SPON_LNAME"]);
wvStat.reportWaivers = "xREPORT_WAIVERS";
if (!wvStat.caseStatus.Equals("DELETED"))
{
wvStatList.Add(wvStat);
}
}
}
catch (Exception e)
{
logger.Fatal("SpecDutyBase.getCaseStatusForExt exception: " + e.Message , e);
throw;
}
// reader.Close();
if (conn.State == ConnectionState.Open) {
conn.Dispose();
}
}
return wvStatList;
}
/// <summary>
///
/// </summary>
/// <param name="uid"></param>
/// <param name="wvStatc"></param>
internal void updateIcmWaiverStat(long uid, WaiverStatus wvStatc)
{
bool insertNewStatus = false;
bool hasInitialStatus=false;
if (wvStatc.midn.Id == null)
{
// bool x = false;
}
IEnumerable<WaiverStatusLatest> midnMMStatmidn = getWaiverStatusById(wvStatc.midn.Id.ToString() );
WaiverStatusLatest f = getWaiverStatusByCaseId(wvStatc.waiverCaseId);
foreach (WaiverStatusLatest wsl in midnMMStatmidn)
{
if (wsl.case_Id == 0 && !wsl.dateCreatedString.Equals(""))
{
hasInitialStatus = true;
}
}
if (f.case_Id == 0 && !hasInitialStatus )
{
insertNewStatus = true;
}
// WaiverStatusLatest f = midnMMStat.First();
long origCid = f.case_Id;
Boolean hasMmStatus = f.midn_Id != 0 || hasInitialStatus;
//SPECWAR: Special Operations Duty
//DIVE: Diving Duty
//SUB: Submarine Duty
//NUKE: Nuclear Field Work
//SWO: Precommissioning for Navy
//USMC: Precommissioning for Marine Corps
//UL: Precommissioning for Navy Unrestricted Line
//RL: Precommissioning for Navy Restricted/MC Precommissioning for Navy Restricted Line/Staff Corps
//SNFO,SNA
//Ionizing Radiation Work (REAB only)
string userId = uid.ToString();
long cid = wvStatc.waiverCaseId;
long midnId = (long)wvStatc.midn.Id;
Hashtable hashtable = wvStatc.wvRecommendationMap;
string wv_Value="";
string entry_Key = "";
foreach (Decision finalDecision in wvStatc.wvoRecommendations)
{
logger.Debug("Each decision in wvStatc" + finalDecision.caseId );
logger.Debug("Each decision in wvStatc.wgroup.description" + finalDecision.wgroup_description );
string decisionSystem= finalDecision.wgroup_description; //sdw
if ( hashtable.Contains(finalDecision.waiver_type) ){ //spec ops duty
logger.Debug( hashtable[finalDecision.waiver_type ] );
entry_Key = finalDecision.waiver_type;
wv_Value = hashtable[finalDecision.waiver_type].ToString();
}
logger.Debug("Calling update_waiver_status_ctrlNum: "+wvStatc.waiverCtrlNum +":"+midnId+":"+cid+".");
update_waiver_status_ctrlNum( wvStatc.waiverCtrlNum, midnId, cid );
string wt = finalDecision.waiver_type;
logger.Debug("final decision waivertype" + wt +":"+ wv_Value);
bool dontskip = ( ! string.IsNullOrEmpty(wv_Value) && decisionSystem.Equals("SDW Full Requests") );
logger.Debug("DontSkip: " + wv_Value +" "+ decisionSystem + "---"+dontskip);
int res = 0;
logger.Debug("before SDW wv_value:" + wv_Value + "decision:" +decisionSystem);
// SDW Full Requests
if ( ! string.IsNullOrEmpty(wv_Value) && !wv_Value.Equals("null") && decisionSystem.Equals("SDW Full Requests") )
{
switch (wt)
{
case "Diving Duty":
if (f.dt_dive == null || wvStatc.closedDate > f.dt_dive)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_dive", "dive_setby", "dive", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_dive", "dive_setby", "dive", wv_Value, midnId, wvStatc.closedDate, cid, userId);
if (res == 1) { origCid = cid; }
hasMmStatus = true;
if (res == 1) { origCid = cid; }
}
addActionLog(uid, cid, "Updating SDW Diving Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Submarine Duty":
if (f.dt_sub == null || wvStatc.closedDate > f.dt_sub)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_sub", "sub_setby", "sub", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_sub", "sub_setby", "sub", wv_Value, midnId, wvStatc.closedDate, cid, userId);
hasMmStatus = true;
if (res == 1) { origCid = cid; }
}
addActionLog(uid, cid, "Updating SDW Sub Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Special Operations Duty":
if (f.dt_specwar == null || wvStatc.closedDate > f.dt_specwar)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_specwar", "specwar_setby", "specwar", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_specwar","specwar_setby", "specwar", wv_Value, midnId, wvStatc.closedDate, cid, userId);
hasMmStatus = true;
if (res == 1) { origCid = cid; }
}
addActionLog(uid, cid, "Updating SDW SpecWar Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Nuclear Field Work":
if (f.dt_nuke == null || wvStatc.closedDate > f.dt_nuke)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_nuke", "nuke_setby", "nuke", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_nuke","nuke_setby", "nuke", wv_Value, midnId, wvStatc.closedDate, cid, userId);
hasMmStatus = true;
if (res == 1) { origCid = cid; }
}
addActionLog(uid, cid, "Updating SDW Nuke Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
default:
addActionLog(uid, cid, "Updating SDW def Waiver Status for : " + midnId + ":" + entry_Key + wv_Value, "WAIVER STATUS", midnId);
break;
}
}
logger.Debug("before USNA wv_value" + wv_Value);
// USNA (WW)
if (!string.IsNullOrEmpty(wv_Value) && !wv_Value.Equals("null") && decisionSystem.Equals("USNA"))//"WW"))
{
if (wvStatc.closedDate >= f.dt_updated_ww)
{
switch (wt)
{
case "Precommissioning for Navy":
if ( f.dt_swo == null || wvStatc.closedDate >= f.dt_swo)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_swo", "swo_setby", "swo", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_swo", "swo_setby", "swo", wv_Value, midnId, wvStatc.closedDate, cid, userId);
if (res == 1) { origCid = cid; }
hasMmStatus = true;
}
addActionLog(uid, cid, "Updating WW Precommisioning for Navy Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Precommissioning for Marine Corps":
if (f.dt_usmc == null || wvStatc.closedDate >= f.dt_usmc)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_usmc", "usmc_setby", "usmc", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_usmc", "usmc_setby", "usmc", wv_Value, midnId, wvStatc.closedDate, cid, userId);
if (res == 1) { origCid = cid; }
hasMmStatus = true;
}
addActionLog(uid, cid, "Updating WW USMC Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Precommissioning for Navy Unrestricted Line":
if (f.dt_ul == null || wvStatc.closedDate >= f.dt_ul)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_ul", "ul_setby", "ul", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_ul", "ul_setby", "ul", wv_Value, midnId, wvStatc.closedDate, cid, userId);
if (res == 1) { origCid = cid; }
hasMmStatus = true;
}
addActionLog(uid, cid, "Updating WW UL Waiver Status(" + res + ") for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
case "Precommissioning for Navy Restricted Line/Staff Corps": //"Precommissioning for Navy Restricted/MC":
//Don't add or update if the current date set is not older than new closedDate
if (f.dt_rl == null || wvStatc.closedDate >= f.dt_rl)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_rl", "rl_setby", "rl", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
}
else
{
res = new_waiver_status("dt_rl", "rl_setby", "rl", wv_Value, midnId, wvStatc.closedDate, cid, userId);
if (res == 1) { origCid = cid; }
hasMmStatus = true;
}
addActionLog(uid, cid, "Updating WW RL Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
}
break;
default:
addActionLog(uid, cid, "Updating WW def Waiver Status for : " + midnId + ":" + wv_Value, "WAIVER STATUS", midnId);
break;
}
}
}
logger.Debug("before REAB wv_value:" + wv_Value);
// REAB
if (!string.IsNullOrEmpty(wv_Value) && !wv_Value.Equals("null") && decisionSystem.Equals("REAB"))
{
if (wvStatc.closedDate >= f.dt_updated_reab)
{
if (f.dt_reab == null || wvStatc.closedDate >= f.dt_reab)
{
if (hasMmStatus)
{
res = update_waiver_status("dt_reab", "reab_setby", "reab", wv_Value, midnId, wvStatc.closedDate, cid, origCid);
if (res == 1) { origCid = cid; }
addActionLog(uid, cid, "Updating REAB Waiver Status for : " + midnId + ":" + entry_Key + wv_Value, "WAIVER STATUS", midnId);
}
else
{
res = new_waiver_status("dt_reab", "reab_setby", "reab", wv_Value, midnId, wvStatc.closedDate, cid, userId);
hasMmStatus = true;
}
}
}
}
}
logger.Debug("Finished foreach (Decision finalDecision");
}
