/// <summary>
///
/// </summary>
/// <param name="wvr"></param>
/// <returns></returns>
internal WaiverNewReqRes addNewCase(Waiver wvr)
{
var connectionString = dbConnSdw;
// String spName = "GETSTATUS";
String spName = "PKG_ICM_CASES.MODERNIZED_ADD_CASE";
// String spName = "ADD_USER_DOCUMENT";
// String moutput = "";
WaiverNewReqRes reqResult = new WaiverNewReqRes();
// String rctlnum = "";
//String rarea = "";
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 sdMedMidUidLong = Convert.ToInt64(sdMedMidUid);
using (var conn = new OracleConnection(connectionString))
using (var command = new OracleCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
})
{
//addcase parameters
command.Parameters.Add("P_SPONSOR_SSN", VCHAR2, inpd).Value = wvr.sponsorSSN;
command.Parameters.Add("P_LNAME", VCHAR2, inpd).Value = wvr.sponsorLName;
command.Parameters.Add("P_FNAME", VCHAR2, inpd).Value = wvr.sponsorFName;
command.Parameters.Add("P_MNAME", VCHAR2, inpd).Value = wvr.sponsorMName;
command.Parameters.Add("P_DOB", VCHAR2, inpd).Value = wvr.sponsorDOBString;
command.Parameters.Add("P_EMAIL", VCHAR2, inpd).Value = wvr.sponsorEMail;
command.Parameters.Add("P_RELATIONSHIP_ID", INT64, inpd).Value = wvr.relationshipID;
command.Parameters.Add("P_SERVICE_ID", INT64, inpd).Value = wvr.serviceID;
command.Parameters.Add("P_DOD_STATUS_ID", INT64, inpd).Value = wvr.dodStatusID;
command.Parameters.Add("P_DOD_COMPONENT_ID", INT64, inpd).Value = wvr.dodComponentID;
command.Parameters.Add("P_RANK", INT64, inpd).Value = wvr.rank;
command.Parameters.Add("P_DECEASED", VCHAR2, inpd).Value = wvr.deceased;
command.Parameters.Add("P_DOD", VCHAR2, inpd).Value = null; // wvr.dod;
command.Parameters.Add("P_SUFFIX", VCHAR2, inpd).Value = wvr.suffix;
command.Parameters.Add("P_GENDER", VCHAR2, inpd).Value = wvr.gender;
command.Parameters.Add("P_PHONE", VCHAR2, inpd).Value = wvr.phone;
command.Parameters.Add("P_DT_CREATED", DATE1, inpd).Value = wvr.created; //DateTime.Now;//
command.Parameters.Add("P_DEPENDENT_SSN", VCHAR2, inpd).Value = wvr.dependentSSN;
command.Parameters.Add("P_DEPENDENT_LNAME", VCHAR2, inpd).Value = wvr.dependentLName;
command.Parameters.Add("P_DEPENDENT_FNAME", VCHAR2, inpd).Value = wvr.dependentFName;
command.Parameters.Add("P_DEPENDENT_MNAME", VCHAR2, inpd).Value = wvr.dependentMName;
command.Parameters.Add("P_DEPENDENT_DOB", VCHAR2, inpd).Value = null;// wvr.dependentDOB;
command.Parameters.Add("P_DEPENDENT_SUFFIX", VCHAR2, inpd).Value = wvr.dependentSuffix;
command.Parameters.Add("P_DEPENDENT_GENDER", VCHAR2, inpd).Value = wvr.dependentGender;
command.Parameters.Add("P_USER_ID", INT64, inpd).Value = sdMedMidUidLong; // wvr.userID;
command.Parameters.Add("p_priority_id", INT64, inpd).Value = wvr.priorityID;
command.Parameters.Add("P_CASE_ID", INT64, wvr.caseID, outpd); //.Value = wvr.caseID; //26
command.Parameters.Add("P_ORGANIZATIONID", INT64, inpd).Value = wvr.orgID; //27
command.Parameters.Add("p_personnelType", INT64, inpd).Value = SpecDutyBase.gv.WW_PERSONNELTYPE; // wvr.personnelType;
command.Parameters.Add("p_saveCase", VCHAR2, inpd).Value = "FALSE";// wvr.saveCase;
command.Parameters.Add("p_bypass", VCHAR2, inpd).Value = "ON";//wvr.bypass;
command.Parameters.Add("P_WAIVER_REQUESTS", VCHAR2, inpd).Value = wvr.waiverRequests;
command.Parameters.Add("P_DIAGNOSIS", VCHAR2, inpd).Value = wvr.diagnosis;
command.Parameters.Add("P_ORGAN", INT64, inpd).Value = wvr.organ;
command.Parameters.Add("P_COMMENTS", VCHAR2, inpd).Value = wvr.comments;
command.Parameters.Add("P_ICD_CODE", VCHAR2, inpd).Value = wvr.icd;
command.Parameters.Add("P_NOTE", VCHAR2, inpd).Value = wvr.note;
command.Parameters.Add("P_NOTE_TYPE_ID", INT64, inpd).Value = wvr.noteTypeID;
command.Parameters.Add("P_PARENT_ID", INT64, inpd).Value = wvr.parentID;
command.Parameters.Add("P_BYPASS_NOTE", VCHAR2, inpd).Value = wvr.bypassNote;
command.Parameters.Add("P_BYPASS_NOTE_TYPE_ID", INT64, inpd).Value = wvr.bypassNoteTypeID;
command.Parameters.Add("P_BYPASS_PARENT_ID", INT64, inpd).Value = wvr.bypassParentID;
command.Parameters.Add("P_URGENT_NOTE", VCHAR2, inpd).Value = wvr.urgentNote;
command.Parameters.Add("P_URGENT_NOTE_TYPE_ID", INT64, inpd).Value = wvr.urgentNoteTypeID;
command.Parameters.Add("P_URGENT_PARENT_ID", INT64, inpd).Value = wvr.urgentParentID;
//
logger.Debug("MODERNIZED_ADD_CASE Document 1- title:"+wvr.title1+" wvr.docTypeID1: "+wvr.docTypeID1 +" filename: "+wvr.fileName1+" path:"+wvr.path1);
command.Parameters.Add("P_TITLE_1", VCHAR2, inpd).Value = wvr.title1; //45
command.Parameters.Add("P_SIZE_1", INT64, inpd).Value = wvr.size1;
command.Parameters.Add("P_PATH_1", VCHAR2, inpd).Value = wvr.path1;
command.Parameters.Add("P_EXTENSION_1", VCHAR2, inpd).Value = wvr.extension1;
command.Parameters.Add("P_DOC_TYPE_ID_1", INT64, inpd).Value = wvr.docTypeID1;
// command.Parameters.Add("P_FILE_NAME_1", VCHAR2, 4000, wvr.fileName1, outpd);//.Value = wvr.fileName1; //50
command.Parameters.Add("P_FILE_NAME_1", VCHAR2, inpd).Value = wvr.fileName1;
command.Parameters.Add("P_COMMENTS_1", VCHAR2, inpd).Value = wvr.comments1;
command.Parameters.Add("P_TITLE_2", VCHAR2, inpd).Value = wvr.title2;
command.Parameters.Add("P_SIZE_2", INT64, inpd).Value = wvr.size2;
command.Parameters.Add("P_PATH_2", VCHAR2, inpd).Value = wvr.path2;
command.Parameters.Add("P_EXTENSION_2", VCHAR2, inpd).Value = wvr.extension2;
command.Parameters.Add("P_DOC_TYPE_ID_2", INT64, inpd).Value = wvr.docTypeID2;
// command.Parameters.Add("P_FILE_NAME_2", VCHAR2, 4000, wvr.fileName2, outpd);//.Value = wvr.fileName2;
command.Parameters.Add("P_FILE_NAME_2", VCHAR2, inpd).Value = wvr.fileName2;
command.Parameters.Add("P_COMMENTS_2", VCHAR2, inpd).Value = wvr.comments2;
command.Parameters.Add("P_TITLE_3", VCHAR2, inpd).Value = wvr.title3;
command.Parameters.Add("P_SIZE_3", INT64, inpd).Value = wvr.size3; //60
command.Parameters.Add("P_PATH_3", VCHAR2, inpd).Value = wvr.path3;
command.Parameters.Add("P_EXTENSION_3", VCHAR2, inpd).Value = wvr.extension3;
command.Parameters.Add("P_DOC_TYPE_ID_3", INT64, inpd).Value = wvr.docTypeID3;
// command.Parameters.Add("P_FILE_NAME_3", VCHAR2, 4000, wvr.fileName3, outpd);//.Value = wvr.fileName3 ;
command.Parameters.Add("P_FILE_NAME_3", VCHAR2, inpd).Value = wvr.fileName3;
command.Parameters.Add("P_COMMENTS_3", VCHAR2, inpd).Value = wvr.comments3;
command.Parameters.Add("P_TITLE_4", VCHAR2, inpd).Value = wvr.title4;
command.Parameters.Add("P_SIZE_4", INT64, inpd).Value = wvr.size4;
command.Parameters.Add("P_PATH_4", VCHAR2, inpd).Value = wvr.path4;
command.Parameters.Add("P_EXTENSION_4", VCHAR2, inpd).Value = wvr.extension4;
command.Parameters.Add("P_DOC_TYPE_ID_4", INT64, inpd).Value = wvr.docTypeID4; //70
//command.Parameters.Add("P_FILE_NAME_4", VCHAR2, 4000, wvr.fileName4, outpd);//.Value = wvr.fileName4;
command.Parameters.Add("P_FILE_NAME_4", VCHAR2, inpd).Value = wvr.fileName4;
command.Parameters.Add("P_COMMENTS_4", VCHAR2, inpd).Value = wvr.comments4;
command.Parameters.Add("P_TITLE_5", VCHAR2, inpd).Value = wvr.title5;
command.Parameters.Add("P_SIZE_5", INT64, inpd).Value = wvr.size5;
command.Parameters.Add("P_PATH_5", VCHAR2, inpd).Value = wvr.path5;
command.Parameters.Add("P_EXTENSION_5", VCHAR2, inpd).Value = wvr.extension5;
command.Parameters.Add("P_DOC_TYPE_ID_5", INT64, inpd).Value = wvr.docTypeID5;
//command.Parameters.Add("P_FILE_NAME_5", VCHAR2, 4000, wvr.fileName5, outpd);//.Value = wvr.fileName5;
command.Parameters.Add("P_FILE_NAME_5", VCHAR2, inpd).Value = wvr.fileName5;
command.Parameters.Add("P_COMMENTS_5", VCHAR2, inpd).Value = wvr.comments5;
String res = "";
String ctlnum = "";
String area = "";
command.Parameters.Add("p_result", VCHAR2, 4000, res, outpd); //80
command.Parameters.Add("P_CTRL_NUMBER", VCHAR2, 4000, ctlnum, outpd);
command.Parameters.Add("p_area", VCHAR2, 4000, area, outpd);
command.Parameters.Add("P_SPONSOR_EDIPI", VCHAR2, inpd).Value = null;// wvr.sponsorEDIPI;
command.Parameters.Add("P_DEPENDENT_EDIPI", VCHAR2, inpd).Value = null;// wvr.dependentEDIPI;
command.Parameters.Add("P_IS_MEDMIDS", VCHAR2, inpd).Value = "T";
try
{
conn.Open();
command.ExecuteNonQuery();
reqResult.result = command.Parameters["p_result"].Value.ToString();
reqResult.ctrlNum = command.Parameters["P_CTRL_NUMBER"].Value.ToString();
reqResult.area = command.Parameters["p_area"].Value.ToString();
reqResult.caseId = command.Parameters["P_CASE_ID"].Value.ToString();
reqResult.docName1 = command.Parameters["P_TITLE_1"].Value.ToString();
reqResult.docName2 = command.Parameters["P_TITLE_2"].Value.ToString();
reqResult.docName3 = command.Parameters["P_TITLE_3"].Value.ToString();
reqResult.docName4 = command.Parameters["P_TITLE_4"].Value.ToString();
reqResult.docName5 = command.Parameters["P_TITLE_5"].Value.ToString();
reqResult.priority = wvr.priorityID.ToString();
conn.Close();
}
catch (SqlException e)
{
logger.Fatal("SpecDutyBase.CaseAction 1 exception: " + e.Message + e.StackTrace);
System.Diagnostics.Debug.WriteLine(e.Message);
throw new Exception(e.Message);
}
}
if (reqResult.result.Contains("unique"))
{
logger.Error("Modernized_add_case result: " + reqResult.result);
throw new Exception("Failed Unique Index. Cannot add documents with same name.");
}
return reqResult;
}
/// <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
}
