//=========================
// Saves data related to Report Details section of the Report Request Details
public void saveDetailRepReqData(DetailsReportRequest_Model DetRepReqDataIn, ref string feedbackMssg)
{
feedbackMssg = "2";
DBOps dbManager_sD = new DBOps();
List <OracleParameter> Params_sD = new List <OracleParameter>()
{
new OracleParameter("p_report_desc", OracleDbType.Varchar2, 8000, (string)DetRepReqDataIn.ReportDescription, ParameterDirection.Input),
new OracleParameter("p_created_by_nm", OracleDbType.Varchar2, 120, (string)DetRepReqDataIn.User_NM, ParameterDirection.Input),
new OracleParameter("p_foia", OracleDbType.Varchar2, 4, (string)DetRepReqDataIn.FOIA_yes, ParameterDirection.Input),
new OracleParameter("p_user_report_loc", OracleDbType.Varchar2, 400, (string)DetRepReqDataIn.UserReportLocation, ParameterDirection.Input),
new OracleParameter("p_report_name", OracleDbType.Varchar2, 200, (string)DetRepReqDataIn.ReportName, ParameterDirection.Input),
new OracleParameter("p_report_type_id", OracleDbType.Varchar2, 80, (string)DetRepReqDataIn.ReportType, ParameterDirection.Input), // left as is since Stored Procedure signature and report type doesn't change
new OracleParameter("p_request_id", OracleDbType.Int64, int.Parse(DetRepReqDataIn.RequestID), ParameterDirection.Input)
};
try
{
dbManager_sD.ExecSPNonQuery("APP1.APP1_SEND_REQUEST_API.ins_report", Params_sD);
}
catch (Exception ex)
{
feedbackMssg = "Error associated with the database while saving Requested Report Details, please contact your administrator";
throw new CustomExceptions.CustomException(ex.Message + " " + ex.StackTrace);
//string exceptionEmailText = "Error Saving Report Details using APP1.APP1_SEND_REQUEST_API.ins_report, error: " + ex.Message;
//sendExceptionEmail(exceptionEmailText);
}
}
//=========================
// Saves data related to Admin Related Details section of the Report Request Details
public void updateAdmin(DetailsReportRequest_Model DetRepReqDataIn, ref string feedbackMssg)
#region Saving Details
{
feedbackMssg = "1";
string nullValidator = string.IsNullOrEmpty(DetRepReqDataIn.UpdateECTDays) ? "0" : DetRepReqDataIn.UpdateECTDays;
DetRepReqDataIn.UpdateECTDays = nullValidator;
nullValidator = string.IsNullOrEmpty(DetRepReqDataIn.UpdateECTHours) ? "0" : DetRepReqDataIn.UpdateECTHours;
DetRepReqDataIn.UpdateECTHours = nullValidator;
DBOps dbManager_sARD = new DBOps();
List <OracleParameter> Params_sARD = new List <OracleParameter>()
{
new OracleParameter("p_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.RequestID, ParameterDirection.Input),
new OracleParameter("p_staff_priority", OracleDbType.Varchar2, (string)DetRepReqDataIn.StaffPriorityLevel, ParameterDirection.Input),
new OracleParameter("p_staff_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.ReportStaff, ParameterDirection.Input),
new OracleParameter("p_status_id", OracleDbType.Varchar2, (string)DetRepReqDataIn.AdminStatus, ParameterDirection.Input),
new OracleParameter("p_est_day", OracleDbType.Int64, int.Parse(DetRepReqDataIn.UpdateECTDays), ParameterDirection.Input),
new OracleParameter("p_est_hr", OracleDbType.Int64, int.Parse(DetRepReqDataIn.UpdateECTHours), ParameterDirection.Input),
//new OracleParameter("p_est_comp_time", OracleDbType.Varchar2, (string)DetRepReqDataIn.EstimCompletionTime ,ParameterDirection.Input),
new OracleParameter("p_staff_comments", OracleDbType.Varchar2, (string)DetRepReqDataIn.StaffComments, ParameterDirection.Input),
new OracleParameter("p_notes_to_user", OracleDbType.Varchar2, (string)DetRepReqDataIn.NotesToRequester, ParameterDirection.Input),
new OracleParameter("p_modified_by", OracleDbType.Varchar2, (string)DetRepReqDataIn.UserID, ParameterDirection.Input)
//new OracleParameter("p_output", OracleDbType.RefCursor,ParameterDirection.Output)
};
try
{
//using (OracleDataReader dr = dbManager_sARD.ExecSP("APP1.APP1_SEND_REQUEST_API.upd_admin_section", Params_sARD));
dbManager_sARD.ExecSPNonQuery("APP1.APP1_SEND_REQUEST_API.upd_admin_section", Params_sARD);
}
catch (Exception ex)
{
feedbackMssg = "Error associated with the database while saving Requested Report Details, please contact your administrator";
throw new CustomExceptions.CustomException(ex.Message + " " + ex.StackTrace);
//string exceptionEmailText = "Error retrieving User's APP1.APP1_SEND_REQUEST_API.upd_admin_section, error: " + ex.Message;
//sendExceptionEmail(exceptionEmailText);
}
}
public DetailsReportRequest_Model retrieveRepDetailData(DetailsReportRequest_Model detailsRepReqData, string feedbackMssg)
#region Retrieves Report Detail Data
{
//=========================
// Retrieves data to populate Add Report Details for update
DBOps dbManager_AddRD = new DBOps();
List <OracleParameter> Params_AddRD = new List <OracleParameter>()
{
new OracleParameter("p_id", OracleDbType.Int64, int.Parse(detailsRepReqData.RequestID), ParameterDirection.Input),
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output)
};
try
{
using (OracleDataReader dr = dbManager_AddRD.ExecSP("APP1.APP1_SEND_REQUEST_API.sel_report", Params_AddRD))
{
while (dr.Read())
{
detailsRepReqData.ReportName = dr["report_name"].ToString();
detailsRepReqData.ReportDescription = dr["report_desc"].ToString();
detailsRepReqData.FOIA_yes = dr["foia"].ToString();
detailsRepReqData.ReportType_Adding = dr["report_type_id"].ToString();
detailsRepReqData.UserReportLocation = dr["user_report_loc"].ToString();
}
}
}
catch (Exception)
{
throw new Exception("Database transaction or connection was unsuccessful for Add Report Details, please contact your administrator");
//string exceptionEmailText = "Error retrieving User's data using APP1.APP1_SEND_REQUEST_API.sel_report, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
//detailsRepReqData.ReportType_List = reportTypeList;
return(detailsRepReqData);
}
} // end of retrieveDetRepReqData
#endregion
private static void retrieveListsDetRepReqData(DetailsReportRequest_Model detailsRepReqData, string requestID, string userNM, ref string feedbackMssg)
#region Retrieves List
{
// Retrieves data to populate Staff Priority Level
List <SelectListItem> staffPriorList = new List <SelectListItem>();
//itemSelected = detailsRepReqData. .ToString();
DBOps dbManagerSP = new DBOps();
List <OracleParameter> ParamsSP = new List <OracleParameter>()
{
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output),
};
try
{
using (OracleDataReader dr = dbManagerSP.ExecSP("SCH1.APP1_API.sel_priority", ParamsSP))
{
while (dr.Read())
{
staffPriorList.Add(new SelectListItem()
{
Value = dr["ID"].ToString(),
Text = dr["DESCRIPTION"].ToString(),
// Selected = dr["ID"].ToString() == itemSelected ? true : false
Selected = dr["ID"].ToString() == "L" ? true : false
});
}
}
}
catch (Exception)
{
throw new Exception("Database transaction or connection was unsuccessful for Staff Priority Level, please contact your administrator");
//string exceptionEmailText = "Error retrieving User's data using APP1_READ_API.sel_user_info, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
// Adds the default text for null selection
detailsRepReqData.StaffPrioLevel_List = staffPriorList;
//=========================
// Retrieves data to populate Report Staff
List <SelectListItem> reportStaffList = new List <SelectListItem>();
var itemSelectedUserID = detailsRepReqData.UserID;
DBOps dbManager_RS = new DBOps();
List <OracleParameter> Params_RS = new List <OracleParameter>()
{
//new OracleParameter("p_user_nm", OracleDbType.Varchar2, userNM, ParameterDirection.Input),
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output)
};
try
{
//using (OracleDataReader dr = dbManager_RS.ExecSP("APP1.APP1_USERINFO_API.sel_all_user_info", Params_RS))
using (OracleDataReader dr = dbManager_RS.ExecSP("SCH1.APP1_API.sel_report_staff", Params_RS))
{
while (dr.Read())
{
reportStaffList.Add(new SelectListItem()
{
Value = dr["ID"].ToString(),
Text = dr["FIRSTNAME"].ToString() + " " + dr["LASTNAME"].ToString(),
//Selected = dr["ID"].ToString() == itemSelectedUserID ? true : false
});
}
}
}
catch (Exception)
{
throw new Exception("Database transaction or connection was unsuccessful for Report Staff, please contact your administrator");
//string exceptionEmailText = "Error retrieving User's data using APP1_READ_API.sel_user_info, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
detailsRepReqData.ReportStaff_List = reportStaffList;
//=========================
// Retrieves data to populate Admin Status
List <SelectListItem> admStatusList = new List <SelectListItem>();
//itemSelected = detailsRepReqData. .ToString();
DBOps dbManager_AmS = new DBOps();
List <OracleParameter> Params_AmS = new List <OracleParameter>()
{
new OracleParameter("p_status_id", OracleDbType.Varchar2, detailsRepReqData.StatusIDReportType, ParameterDirection.Input),
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output)
};
try
{
using (OracleDataReader dr = dbManager_AmS.ExecSP("APP1.APP1_REQUEST_DETAILS_API.sel_admin_status", Params_AmS))
{
while (dr.Read())
{
admStatusList.Add(new SelectListItem()
{
Value = dr["status_id_sel"].ToString(),
Text = dr["description"].ToString(),
// Selected = dr["ID"].ToString() == itemSelected ? true : false
//Selected = dr["status_id_sel"].ToString() == "A" ? true : false
});
}
}
}
catch (Exception)
{
throw new Exception("Database transaction or connection was unsuccessful for Admin Status, please contact your administrator");
//string exceptionEmailText = "Error retrieving User's data using APP1_READ_API.sel_user_info, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
detailsRepReqData.AdminStatus_List = admStatusList;
#region Report Type - Retieves
//=========================
// Retrieves data to populate Report Type
// List<SelectListItem> reportTypeList = new List<SelectListItem>();
//itemSelected = detailsRepReqData. .ToString();
// DBOps dbManager_RT = new DBOps();
// List<OracleParameter> Params_RT = new List<OracleParameter>()
//{
// new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output)
//};
// try
// {
// using (OracleDataReader dr = dbManager_RT.ExecSP("SCH1.APP1_API.sel_report_type", Params_RT))
// {
// while (dr.Read())
// {
// reportTypeList.Add(new SelectListItem()
// {
// Value = dr["ID"].ToString(),
// Text = dr["DESCRIPTION"].ToString()
// // Selected = dr["ID"].ToString() == itemSelected ? true : false
// });
// }
// }
// }
// catch (Exception)
// {
// throw new Exception("Database transaction or connection was unsuccessful for Report Type, please contact your administrator");
// //string exceptionEmailText = "Error retrieving User's data using APP1_READ_API.sel_user_info, error: " + err.Message;
// //sendExceptionEmail(exceptionEmailText);
// }
// finally
// {
// }
// detailsRepReqData.ReportType_List = reportTypeList;
#endregion
//return detailsRepReqData;
}
// } // end of the class
#endregion
#region Model's Methods
public DetailsReportRequest_Model retrieveDetRepReqData(string requestID, string userNM, ref string feedbackMssg)
#region Retrieves Report Details
{
feedbackMssg = "1"; // no exception present in the transaction
DetailsReportRequest_Model detailsRepReqData = new DetailsReportRequest_Model();
detailsRepReqData.RequestID = requestID; // loads request ID
detailsRepReqData.User_NM = userNM; // loads ID of the logged in User
DBOps dbManager = new DBOps();
List <OracleParameter> Params = new List <OracleParameter>()
{
new OracleParameter("p_request_id", OracleDbType.Int64, int.Parse(requestID), ParameterDirection.Input),
new OracleParameter("V_OUTPUT", OracleDbType.RefCursor, ParameterDirection.Output),
};
try
{
using (OracleDataReader dr = dbManager.ExecSP("APP1.APP1_REQUEST_DETAILS_API.view_request_details", Params))
{
while (dr.Read())
{
detailsRepReqData.UserName = dr["users_firstname"].ToString() + " " + dr["users_lastname"].ToString();
detailsRepReqData.TimeStamp = dr["time_stamp"].ToString();
detailsRepReqData.ReportType = dr["report_type_description"].ToString();
detailsRepReqData.ReportStatus = dr["status_description"].ToString();
detailsRepReqData.RepNeededBy = dr["needed_by"].ToString();
detailsRepReqData.CurrDutyStation = dr["location_description"].ToString();
detailsRepReqData.RunFrequency = dr["frequency_description"].ToString();
detailsRepReqData.WhatYouNeed = dr["report_criteria"].ToString();
detailsRepReqData.WhyYouNeed = dr["report_objective"].ToString();
detailsRepReqData.FieldsRequired = dr["required_fields"].ToString();
detailsRepReqData.SortDispCriteria = dr["sort_criteria"].ToString();
//detailsRepReqData.DisasterType = dr[""].ToString();
detailsRepReqData.DisasterNo = dr["disaster_no"].ToString();
//detailsRepReqData.PrefDelivery = dr["delivery_option_note"].ToString();
detailsRepReqData.PrefDelivery = dr["delivery_option"].ToString();
detailsRepReqData.UserComments = dr["user_comments"].ToString();
detailsRepReqData.StaffPriorityLevel = dr["staff_prority"].ToString();
detailsRepReqData.ReportStaff = dr["staff_id"].ToString();
detailsRepReqData.AdminStatus = dr["status_id"].ToString();
detailsRepReqData.EstimCompletionTime = dr["est_compl_time"].ToString();
detailsRepReqData.StaffComments = dr["staff_comments"].ToString();
detailsRepReqData.NotesToRequester = dr["notes_to_user"].ToString();
detailsRepReqData.UserID = dr["user_id"].ToString();
detailsRepReqData.UserEmail = dr["users_email"].ToString();
detailsRepReqData.UserPhoneNumber = dr["user_phone_area_code"].ToString() + "-" + dr["user_prefix"].ToString() + "-" + dr["user_phone_number"].ToString() + "Ext." + dr["user_phone_ext"].ToString();
detailsRepReqData.UserDateCreated = dr["user_date_created"].ToString();
detailsRepReqData.StatusIDReportType = dr["status_id"].ToString();
detailsRepReqData.StaffPrioLevelDesc = dr["staff_priority_description"].ToString();
detailsRepReqData.ReportStaffName = dr["assigned_to"].ToString();
}
}
}
catch (Exception ex)
{
feedbackMssg = "Database transaction or connection was unsuccessful, please contact your administrator";
throw new CustomExceptions.CustomException(ex.Message + " " + ex.StackTrace);
//string exceptionEmailText = "Error retrieving details of Report Requested data using APP1.APP1_REQUEST_DETAILS_API.view_request_details, error: " + err.Message;
//sendExceptionEmail(exceptionEmailText);
}
finally
{
}
retrieveListsDetRepReqData(detailsRepReqData, requestID, userNM, ref feedbackMssg);
return(detailsRepReqData);
} // end of retrieveDetRepReqData