public List <ErTracersbyQuestionDetails> GetLevel5Data(SearchER search)
{
var Level5Data = new List <ErTracersbyQuestionDetails>();
Level5Data = SelectLevel5Data(search);
return(Level5Data);
}
public ActionResult LoadHeatMap(SearchER search)
{
ERSearchInputService reportservice = new ERSearchInputService();
var tcList = reportservice.GetUHSTracersList(search.SelectedSiteIDs, search.ProgramIDs).TracersLists.ToList();
tcList.RemoveAt(0);
if (search.TracerListIDs != "-1")
{
var stList = search.TracerListNames.Split(',').ToList();
var tDicList = tcList.Distinct().ToDictionary(x => x.TracerCustomName, x => x.TracerCategoryID);
foreach (var tracer in tcList.Select(x => x.TracerCustomName.ToString()).ToList())
{
if (!(stList.Any(x => x.ToString().Contains(tracer.ToString()))))
{
tDicList.Remove(tracer);
}
}
ViewBag.Header = tDicList;
}
else
{
var tDicList = tcList.Distinct().ToDictionary(x => x.TracerCustomName, x => x.TracerCategoryID);
ViewBag.Header = tDicList;
}
//ViewBag.Header = tcList.Select(x=>x.TracerCustomName).Distinct().ToList();
return(PartialView("HeatMap"));
}
public ActionResult TracerComplianceSummary_Data(SearchER search, int LevelIdentifier)
{
TracersComplianceSummary reportservice = new TracersComplianceSummary();
JsonResult jr = new JsonResult();
switch (LevelIdentifier)
{
case (int)WebConstants.TracerComplianceSummaryLevels.Level1_Program:
{
List <ErTracersbyProgramData> Level1Data = new List <ErTracersbyProgramData>();
Level1Data = reportservice.GetLevel1Data(search);
jr = Json(Level1Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level2_Site:
{
List <ErTracersbySiteData> Level2Data = new List <ErTracersbySiteData>();
Level2Data = reportservice.GetLevel2Data(search);
jr = Json(Level2Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level3_Tracer:
{
List <ErTracersbyTracerData> Level3Data = new List <ErTracersbyTracerData>();
Level3Data = reportservice.GetLevel3Data(search);
jr = Json(Level3Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level4_Question:
{
List <ErTracersbyQuestionData> Level4Data = new List <ErTracersbyQuestionData>();
Level4Data = reportservice.GetLevel4Data(search);
jr = Json(Level4Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level5_QuestionDetails:
{
List <ErTracersbyQuestionDetails> Level5Data = new List <ErTracersbyQuestionDetails>();
Level5Data = reportservice.GetLevel5Data(search);
jr = Json(Level5Data, JsonRequestBehavior.AllowGet);
break;
}
}
jr.MaxJsonLength = Int32.MaxValue;
jr.RecursionLimit = 100;
return(jr);
}
public ActionResult LoadTracerDetailts(SearchER search)
{
ERSearchInputService reportservice = new ERSearchInputService();
var siteList = reportservice.GetSitesList(search.SelectedSiteIDs);
ViewBag.Header = siteList;
return(PartialView("TracerDetails"));
}
private List <ErTracersbyEPDetails> SelectLevel6Data(SearchER search)
{
var list = new List <ErTracersbyEPDetails>();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand("ustERReport_TracerByTJCStandard_ByEPDetails", cn);
cmd.CommandTimeout = 900;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SiteIDs", search.SelectedSiteIDs);
cmd.Parameters.AddWithValue("ProgramID", Convert.ToInt32(search.ProgramIDs));
cmd.Parameters.AddWithValue("ChapterIDs", search.SelectedChapterIDs == "-1" ? "" : search.SelectedChapterIDs);
cmd.Parameters.AddWithValue("StandardTextIDs", search.SelectedStandardIDs == "-1" ? "" : search.SelectedStandardIDs);
cmd.Parameters.AddWithValue("EPTextIDs", search.EPTextIDs == "-1" ? "" : search.EPTextIDs);
cmd.Parameters.AddWithValue("CycleID", AppSession.CycleID);
cmd.Parameters.AddWithValue("IncludeNA", 1);
cmd.Parameters.AddWithValue("FSA", search.IncludeFsa ? 1 : 0);
cmd.Parameters.AddWithValue("OrgActive", -1);
cmd.Parameters.AddWithValue("ResponseStartDate", search.StartDate);
cmd.Parameters.AddWithValue("ResponseEndDate", search.EndDate);
#if DEBUG
CreateSQLExecuted("ustERReport_TracerByTJCStandard_ByEPDetails", cmd);
System.Diagnostics.Debug.WriteLine(_SQLExecuted);
#endif
SqlDataAdapter da = new SqlDataAdapter(cmd);
using (cn)
using (cmd)
using (da)
{
da.Fill(ds);
}
}
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
list = dt.ToList <ErTracersbyEPDetails>();
}
}
catch (Exception ex)
{
ex.Data.Add(TSQL, _SQLExecuted);
throw ex;
}
return(list);
}
public List <ErTracersbyProgramData> GetLevel1Data(SearchER search)
{
var Level1Data = new List <ErTracersbyProgramData>();
Level1Data = GetLevel1DataSet(search).Tables[0].ToList <ErTracersbyProgramData>();
Level1Data.ForEach(z => z.NACompliancePercent = Decimal.Equals(z.Numerator, 0) & Decimal.Equals(z.Denominator, 0) ? 100.0m : 0.0m);
Level1Data.ForEach(z => z.NonCompliancePercent = Decimal.Equals(z.Numerator, 0) & Decimal.Equals(z.Denominator, 0) ? 0.0m : z.NonCompliancePercent);
return(Level1Data);
}
public List <ErTracersbySiteData> GetLevel2Data(SearchER search)
{
var Level2Data = new List <ErTracersbySiteData>();
Level2Data = GetLevel2DataSet(search).Tables[0].ToList <ErTracersbySiteData>();
Level2Data.ForEach(z => z.SiteFullName = z.HCOID == 0 || z.HCOID == null ? z.SiteName + " " + z.SiteID.ToString() + " (SiteID) " : z.SiteName + " " + z.HCOID.ToString());
Level2Data.ForEach(z => z.NACompliancePercent = Decimal.Equals(z.Numerator, 0) & Decimal.Equals(z.Denominator, 0) ? 100.0m : 0.0m);
Level2Data.ForEach(z => z.NonCompliancePercent = Decimal.Equals(z.Numerator, 0) & Decimal.Equals(z.Denominator, 0) ? 0.0m : z.NonCompliancePercent);
return(Level2Data);
}
public DataSet GetLevel1DataSet(SearchER search)
{
var list = new List <ErTracersbyProgramData>();
DataSet ds = new DataSet();
try
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand("ustERReport_TracerComplianceSummary_ByProgram", cn);
cmd.CommandTimeout = 900;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SiteIDs", search.SelectedSiteIDs);
cmd.Parameters.AddWithValue("ProgramIDs", search.ProgramIDs == "-1" ? "" : search.ProgramIDs);
cmd.Parameters.AddWithValue("TracerIDs", search.TracerListIDs == "-1" ? "" : search.TracerListIDs);
cmd.Parameters.AddWithValue("CycleID", AppSession.CycleID);
cmd.Parameters.AddWithValue("IncludeNA", 1);
cmd.Parameters.AddWithValue("FSA", search.IncludeFsa ? 1 : 0);
cmd.Parameters.AddWithValue("OrgActive", -1);
cmd.Parameters.AddWithValue("ResponseStartDate", search.StartDate);
cmd.Parameters.AddWithValue("ResponseEndDate", search.EndDate);
#if DEBUG
CreateSQLExecuted("ustERReport_TracerComplianceSummary_ByProgram", cmd);
System.Diagnostics.Debug.WriteLine(_SQLExecuted);
#endif
SqlDataAdapter da = new SqlDataAdapter(cmd);
using (cn)
using (cmd)
using (da)
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
ex.Data.Add(TSQL, _SQLExecuted);
throw ex;
}
return(ds);
}
public ActionResult SendERPDFEmail(string ExcelGridName, Email email, string ERReportName, string SortBy = "", string SortOrder = "")
{
var emailService = new CommonService();
bool emailSuccess = true;
var emailMessage = WebConstants.Excel_Email_Success;
try
{
byte[] fileContents = null;
SearchER ERsearchcriteria = Session["ERsearchcriteria"] as SearchER;
// Session.Remove("ERsearchcriteria");
if (ERReportName == "TracersByTJCStandard")
{
var tjcStandardService = new TracersByTJCStandard();
fileContents = tjcStandardService.TracerByTJCStandardRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
else if (ERReportName == "ERTracerByQuestion")
{
var ERTracerByQuestion = new ERTracerByQuestion();
fileContents = ERTracerByQuestion.ERTracerByQuestionRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
else
{
var tcsService = new TracersComplianceSummary();
fileContents = tcsService.TracersComplianceSummaryRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
email.AttachmentLocation[0] = emailService.SavePDF(ExcelGridName, fileContents);
email.FileContents = fileContents;
emailSuccess = emailService.SendExcelEmailAttachemnt(email, true);
if (emailSuccess)
{
emailMessage = WebConstants.Excel_Email_Success;
}
else
{
emailMessage = WebConstants.Email_Failed;
}
}
catch (Exception)
{
emailMessage = WebConstants.Excel_Email_Failed;
}
finally
{
Session.Remove("ERsearchcriteria");
}
return(Json(emailMessage));
}
public ActionResult LoadTracerHeatMap(SearchER search /*, string tracerName*/)
{
ERSearchInputService reportservice = new ERSearchInputService();
var tcList = Array.ConvertAll(search.TracerListNames.Split('€'), p => p.Trim()).ToList();
if (tcList.Any(i => string.Equals("All", i, StringComparison.CurrentCultureIgnoreCase)))
{
tcList = null;
tcList = reportservice.GetMultiSiteTracersList(search.SelectedSiteIDs, search.ProgramIDs).TracersLists.Select(x => x.TracerCustomName).ToList();
tcList.RemoveAt(0);
}
ViewBag.Header = tcList;
return(PartialView("HeatMapGraph"));
}
public ActionResult createErPdf(string ExcelGridName, string ERReportName, string SortBy = "", string SortOrder = "")
{
var emailService = new CommonService();
var createErPdf = "failed";
string fileGuid = "";
try
{
byte[] fileContents = null;
SearchER ERsearchcriteria = Session["ERsearchcriteria"] as SearchER;
Session.Remove("ERsearchcriteria");
if (ERReportName == "TracersByTJCStandard")
{
var tjcStandardService = new TracersByTJCStandard();
fileContents = tjcStandardService.TracerByTJCStandardRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
else if (ERReportName == "ERTracerByQuestion")
{
var ERTracerByQuestion = new ERTracerByQuestion();
fileContents = ERTracerByQuestion.ERTracerByQuestionRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
else
{
var tcsService = new TracersComplianceSummary();
fileContents = tcsService.TracersComplianceSummaryRDLC(ERsearchcriteria, (int)WebConstants.ReportFormat.PDF, SortBy, SortOrder);
}
if (fileContents != null)
{
createErPdf = "success";
}
else
{
createErPdf = "failed";
}
fileGuid = emailService.SavePDF(ExcelGridName, fileContents);
}
catch (Exception)
{
createErPdf = "failed";
}
finally
{
Session.Remove("ERsearchcriteria");
}
return(Json(new { exportCreated = createErPdf, fileGuid = fileGuid }));
}
private List <CompliaceByTracerHeatMap> TransformDashboardTracer(List <ComplianceByTracerTransform> tracerTransform, SearchER search)
{
List <CompliaceByTracerHeatMap> tracerComplianceHeatMap = new List <CompliaceByTracerHeatMap>();
ERSearchInputService reportservice = new ERSearchInputService();
var tracerList = Array.ConvertAll(search.TracerListNames.Split('€'), p => p.Trim()).ToList();
if (tracerList.Any(i => string.Equals("All", i, StringComparison.CurrentCultureIgnoreCase)))
{
tracerList = null;
tracerList = reportservice.GetMultiSiteTracersList(search.SelectedSiteIDs, search.ProgramIDs).TracersLists.Select(x => x.TracerCustomName).ToList();
tracerList.RemoveAt(0);
}
tracerList.OrderBy(i => PadNumbers(i));
foreach (var site in tracerTransform)
{
if (tracerComplianceHeatMap.Any(item => site.SiteID == item.SiteID))
{
var newSite = tracerComplianceHeatMap.First(item => string.Equals(site.SiteName, item.SiteName, StringComparison.CurrentCultureIgnoreCase));
AddTracerwiseSite(newSite, site);
}
else
{
var newSite = PopulateTracerwiseInfo(tracerList);
newSite.SiteName = site.SiteName;
newSite.SiteID = site.SiteID;
newSite.HCOID = site.HCOID.ToString();
tracerComplianceHeatMap.Add(AddTracerwiseSite(newSite, site));
}
}
return(tracerComplianceHeatMap);
}
public ActionResult TracerComplianceHeatMap([DataSourceRequest] DataSourceRequest request, SearchER search)
{
/*
* ERSearchInputService reportservice = new ERSearchInputService();
* var tcList = reportservice.GetUHSTracersList(search.SelectedSiteIDs, search.ProgramIDs).TracersLists.ToList();
* tcList.RemoveAt(0);
* ViewBag.Header = tcList.Where(x=>x.TracerCategoryID == tracerCategoryID).Select(x => x.TracerCustomName).Distinct().ToList();
*/
var tcService = new ERComplianceByTracer();
DataSourceResult result = tcService.TracerComplianceHeatMap(request, search);
if (result.Errors != null && result.Errors.ToString() != "")
{
ModelState.AddModelError("Error", result.Errors.ToString());
}
var val = Json(result, JsonRequestBehavior.AllowGet);
val.MaxJsonLength = int.MaxValue;
return(val);
}
public byte[] TracersComplianceSummaryRDLC(SearchER search, int reportType, string SortBy = "", string SortOrder = "")
{
byte[] fileContents = null;
string reportDateTitle = "";
string rdlcName = String.Empty;
string dsName = String.Empty;
DataView dv = null;
ReportParameterCollection reportParameterCollection = null;
try
{
if (AppSession.ReportScheduleID > 0)
{
search.ReportTitle = String.Concat(search.ReportTitle, " - Report ID: ", AppSession.ReportScheduleID);
}
reportDateTitle = CommonService.InitializeReportDateTitle("Observation", search.StartDate, search.EndDate);
search.EndDate = (search.EndDate != null && search.EndDate.ToString() != "") ? search.EndDate.Value.Date.AddHours(23).AddMinutes(29).AddSeconds(59) : search.EndDate;
// Setup ReportViewer
ReportViewer reportViewer = new ReportViewer();
reportViewer.ProcessingMode = ProcessingMode.Local;
reportViewer.SizeToReportContent = true;
reportViewer.LocalReport.DisplayName = search.ReportTitle;
// Add Initial Common Report Parameter
// Set Parameters
switch (search.LevelIdentifier)
{
case (int)WebConstants.TracerComplianceSummaryLevels.Level1_Program:
default:
{
rdlcName = "rptReportTracerComplianceSummary_ByProgram.rdlc";
dsName = "dsReport_TracerComplianceSummaryByProgram";
dv = new DataView(GetLevel1DataSet(search).Tables[0]);
ReportParameter p1 = new ReportParameter("ReportTitle", search.ReportTitle.ToString());
ReportParameter p2 = new ReportParameter("Programs", search.ProgramNames.ToString());
ReportParameter p3 = new ReportParameter("Tracers", search.TracerListNames.ToString());
ReportParameter p4 = new ReportParameter("HCOID", search.SelectedSiteHCOIDs.ToString());
ReportParameter p5 = new ReportParameter("ReportDateTitle", reportDateTitle.ToString());
ReportParameter p6 = new ReportParameter("Copyright", "© " + DateTime.Now.Year.ToString() + WebConstants.Tracer_Copyright.ToString());
ReportParameter p7 = new ReportParameter("ReportSubTitle", "Overall Compliance by Program");
ReportParameter p8 = new ReportParameter("ReportType", search.ReportType.ToString());
ReportParameter p9 = new ReportParameter("FSA", search.IncludeFsa ? "1" : "0");
ReportParameter p10 = new ReportParameter("IsCMSProgram", AppSession.IsCMSProgram ? "1" : "0");
reportParameterCollection = new ReportParameterCollection {
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
};
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level2_Site:
{
rdlcName = "rptReportTracerComplianceSummary_BySite.rdlc";
dsName = "dsReport_TracerComplianceSummaryBySite";
dv = new DataView(GetLevel2DataSet(search).Tables[0]);
ReportParameter p1 = new ReportParameter("ReportTitle", search.ReportTitle.ToString());
ReportParameter p2 = new ReportParameter("Programs", search.ProgramNames.ToString());
ReportParameter p3 = new ReportParameter("Tracers", search.TracerListNames.ToString());
ReportParameter p4 = new ReportParameter("HCOID", search.SelectedSiteHCOIDs.ToString());
ReportParameter p5 = new ReportParameter("ReportDateTitle", reportDateTitle.ToString());
ReportParameter p6 = new ReportParameter("Copyright", "© " + DateTime.Now.Year.ToString() + WebConstants.Tracer_Copyright.ToString());
ReportParameter p7 = new ReportParameter("ReportSubTitle", "Compliance by Site");
ReportParameter p8 = new ReportParameter("ReportType", search.ReportType.ToString());
ReportParameter p9 = new ReportParameter("FSA", search.IncludeFsa ? "1" : "0");
ReportParameter p10 = new ReportParameter("IsCMSProgram", AppSession.IsCMSProgram ? "1" : "0");
reportParameterCollection = new ReportParameterCollection {
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
};
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level3_Tracer:
{
rdlcName = "rptReportTracerComplianceSummary_ByTracer.rdlc";
dsName = "dsReport_TracerComplianceSummaryByTracer";
dv = new DataView(GetLevel3DataSet(search).Tables[0]);
ReportParameter p1 = new ReportParameter("ReportTitle", search.ReportTitle.ToString());
ReportParameter p2 = new ReportParameter("Programs", search.ProgramNames.ToString());
ReportParameter p3 = new ReportParameter("ReportDateTitle", reportDateTitle.ToString());
ReportParameter p4 = new ReportParameter("Copyright", "© " + DateTime.Now.Year.ToString() + WebConstants.Tracer_Copyright.ToString());
ReportParameter p5 = new ReportParameter("ReportSubTitle", "Compliance by Tracer");
ReportParameter p6 = new ReportParameter("SiteName", search.SelectedSiteHCOIDs.ToString());
ReportParameter p7 = new ReportParameter("Tracers", search.TracerListNames.ToString());
ReportParameter p8 = new ReportParameter("ReportType", search.ReportType.ToString());
ReportParameter p9 = new ReportParameter("HCOID", search.SelectedSiteHCOIDs.ToString());
ReportParameter p10 = new ReportParameter("FSA", search.IncludeFsa ? "1" : "0");
ReportParameter p11 = new ReportParameter("IsCMSProgram", AppSession.IsCMSProgram ? "1" : "0");
reportParameterCollection = new ReportParameterCollection {
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11
};
break;
}
case (int)WebConstants.TracerComplianceSummaryLevels.Level4_Question:
{
rdlcName = "rptReportTracerComplianceSummary_ByQuestion.rdlc";
dsName = "dsReport_TracerComplianceSummaryByQuestion";
dv = new DataView(GetLevel4DataSet(search).Tables[0]);
ReportParameter p1 = new ReportParameter("ReportTitle", search.ReportTitle.ToString());
ReportParameter p2 = new ReportParameter("Programs", search.ProgramNames.ToString());
ReportParameter p3 = new ReportParameter("Tracers", search.TracerListNames.ToString());
ReportParameter p4 = new ReportParameter("ReportDateTitle", reportDateTitle.ToString());
ReportParameter p5 = new ReportParameter("Copyright", "© " + DateTime.Now.Year.ToString() + WebConstants.Tracer_Copyright.ToString());
ReportParameter p6 = new ReportParameter("ReportSubTitle", "Compliance by Question");
ReportParameter p7 = new ReportParameter("SiteName", search.SelectedSiteHCOIDs.ToString());
ReportParameter p8 = new ReportParameter("ReportType", search.ReportType.ToString());
ReportParameter p9 = new ReportParameter("FSA", search.IncludeFsa ? "1" : "0");
ReportParameter p10 = new ReportParameter("IsCMSProgram", AppSession.IsCMSProgram ? "1" : "0");
reportParameterCollection = new ReportParameterCollection {
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
};
break;
}
}
if (SortBy != "")
{
dv.Sort = SortBy + " " + SortOrder;
}
// Setup Data sources for report
reportViewer.LocalReport.DataSources.Clear();
reportViewer.LocalReport.ReportPath = HttpContext.Current.Request.MapPath(HttpContext.Current.Request.ApplicationPath) + @"Areas\TracerER\Reports\" + rdlcName.ToString();
reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dsName, dv));
reportViewer.LocalReport.SetParameters(reportParameterCollection);
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string format = WebConstants.REPORT_FORMAT_PDF; // PDF is default
if (reportType == (int)WebConstants.ReportFormat.EXCEL)
{
format = WebConstants.REPORT_FORMAT_EXCEL; // If Excel option chosen
}
fileContents = reportViewer.LocalReport.Render(format, null, out mimeType, out encoding, out extension, out streamIds, out warnings);
}
catch (Exception ex)
{
ExceptionLog exceptionLog = new ExceptionLog
{
ExceptionText = "Reports: " + ex.Message,
PageName = "TracerComplianceSummaryRDLC",
MethodName = "TracerComplianceSummaryRDLC",
UserID = Convert.ToInt32(AppSession.UserID),
SiteId = Convert.ToInt32(AppSession.SelectedSiteId),
TransSQL = "",
HttpReferrer = null
};
_exceptionService.LogException(exceptionLog);
}
return(fileContents);
}
private DataSet GetComplianceTracerData(SearchER searchParams, string reportType)
{
DataSet ds = new DataSet();
var qutList = searchParams.QuarterListNames.ToString();
DateTime?startDate = null;
DateTime?endDate = null;
System.Globalization.CultureInfo culture = System.Threading.Thread.CurrentThread.CurrentCulture;
string year = qutList.Substring(0, 4);
if (qutList != "")
{
if (qutList.Contains("Q1"))
{
startDate = Convert.ToDateTime(year + "-01-01 12:00:00 AM");
endDate = Convert.ToDateTime(year + "-03-31 11:59:00 PM");
}
else if (qutList.Contains("Q2"))
{
startDate = Convert.ToDateTime(year + "-04-01 12:00:00 AM");
endDate = Convert.ToDateTime(year + "-06-30 11:59:00 PM");
}
else if (qutList.Contains("Q3"))
{
startDate = Convert.ToDateTime(year + "-07-01 12:00:00 AM");
endDate = Convert.ToDateTime(year + "-09-30 11:59:00 PM");
}
else
{
startDate = Convert.ToDateTime(year + "-10-01 12:00:00 AM");
endDate = Convert.ToDateTime(year + "-12-31 11:59:00 PM");
}
}
try
{
if (searchParams.TracerListIDs == "-1")
{
searchParams.TracerListIDs = string.Empty;
}
if (searchParams.ProgramIDs == "-1")
{
searchParams.ProgramIDs = string.Empty;
}
// searchParams.ProgramIDs = 2;
string sp = "ustERReport_ERCompliance";
if (reportType == "summary")
{
sp = "ustERReport_ERComplianceSummary";
}
else if (reportType == "details")
{
sp = "ustERReport_ERComplianceDetails";
}
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(sp, cn);
if (sp == "ustERReport_ERCompliance")
{
cmd.Parameters.AddWithValue("ShowAllSites", searchParams.IncludeAllSite == true?1:0);
}
cmd.CommandTimeout = 900;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SiteIDs", searchParams.SelectedSiteIDs);
cmd.Parameters.AddWithValue("ProgramID", AppSession.SelectedProgramId);
cmd.Parameters.AddWithValue("TracerIDs", searchParams.TracerListIDs);
cmd.Parameters.AddWithValue("ResponseStartDate", startDate);
cmd.Parameters.AddWithValue("ResponseEndDate", endDate);
//Get the SQL statement for logging
CreateSQLExecuted(sp, cmd);
#if DEBUG
System.Diagnostics.Debug.WriteLine(_SQLExecuted);
#endif
SqlDataAdapter da = new SqlDataAdapter(cmd);
using (cn)
using (cmd)
using (da)
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
ex.Data.Add(TSQL, _SQLExecuted);
throw ex;
}
//If no data exists, throw an exception to display the no rows found message in the UI
if (ds.Tables[0].Rows.Count == 0)
{
throw (new Exception("No Data"));
}
return(ds);
}
//Summary Report
public DataSourceResult TracerComplianceSummary([DataSourceRequest] DataSourceRequest request, SearchER search)
{
List <CompliaceByTracerSummary> tracerComplianceSummary = new List <CompliaceByTracerSummary>();
DataTable dt = new DataTable();
DataSourceResult result = new DataSourceResult();
try
{
dt = this.GetComplianceTracerData(search, "summary").Tables[0];
var categoryTransform = dt.ToList <ComplianceByTracerTransform>();
categoryTransform.ForEach(z => z.ScheduleCompliance = z.ExpectedObs == 0 ? 0.0m: (decimal)((100 * z.ObservationCount) / (z.ExpectedObs)));
categoryTransform.ForEach(z => z.ScheduleCompliance = z.ScheduleCompliance > 100 ? 100.0m : z.ScheduleCompliance);
var results = (from row in categoryTransform
group row by new { row.SiteID, row.SiteName, row.HCOID, row.TracerCategoryName } into grp
select new ComplianceByCategoryTransform
{
SiteID = grp.Key.SiteID,
SiteName = grp.Key.SiteName,
HCOID = grp.Key.HCOID,
TracerCategoryName = grp.Key.TracerCategoryName,
SchComplianceAverage = (grp.Average(row => Convert.ToInt64(row.ScheduleCompliance))).ToString("0.0"),
TracerComplianceAverage = ((decimal)(100 * grp.Sum(row => Convert.ToDecimal(row.Numerator)) / grp.Sum(row => Convert.ToDecimal(row.Denominator)))).ToString("0.0")
}).ToList <ComplianceByCategoryTransform>();
//convert datatable to list
tracerComplianceSummary = TransformComplianceCategory(results, search);
tracerComplianceSummary = tracerComplianceSummary.OrderBy(c => c.SiteName).ToList();
result = tracerComplianceSummary.ToDataSourceResult(request, tc => new CompliaceByTracerSummary
{
SiteName = tc.HCOID.ToString() + " " + tc.SiteName,
SitewiseCategory = tc.SitewiseCategory
});
}
catch (Exception ex)
{
if (ex.Message.ToString() == "No Data")
{
result.Errors = WebConstants.NO_DATA_FOUND_EXCEL_VIEW;
}
else if (ex.Message.ToString() == "Limit")
{
result.Errors = "Maximum limit of " + ConfigurationManager.AppSettings["ReportOutputLimit"].ToString() + " records reached. Refine your criteria to narrow the result.";
}
if (ex.Message.ToString() != "No Data" && ex.Message.ToString() != "Limit")
{
ExceptionLog exceptionLog = new ExceptionLog
{
ExceptionText = "Reports: " + ex.Message,
PageName = "ERComplianceByTracer",
MethodName = "TracerComplianceSummary",
UserID = Convert.ToInt32(AppSession.UserID),
SiteId = Convert.ToInt32(AppSession.SelectedSiteId),
TransSQL = "",
HttpReferrer = null
};
_exceptionService.LogException(exceptionLog);
}
}
return(result);
}
private List <CompliaceByTracerSummary> TransformComplianceCategory(List <ComplianceByCategoryTransform> categoryTransform, SearchER search)
{
List <CompliaceByTracerSummary> CategoryComplianceSummary = new List <CompliaceByTracerSummary>();
List <string> categoryList = new List <string>();
categoryList.Add("Documentation");
categoryList.Add("Infection Control");
// var categoryList = categoryTransform.Select(item => item.TracerCategoryName).Distinct().OrderBy(item => PadNumbers(item)).ToList();
foreach (var site in categoryTransform)
{
if (CategoryComplianceSummary.Any(item => site.SiteID == item.SiteID))
{
var newSite = CategoryComplianceSummary.First(item => string.Equals(site.SiteName, item.SiteName, StringComparison.CurrentCultureIgnoreCase));
AddCategorywiseSite(newSite, site);
}
else
{
var newSite = PopulateCategorywiseInfo(categoryList);
newSite.SiteName = site.SiteName;
newSite.SiteID = site.SiteID;
newSite.HCOID = site.HCOID;
CategoryComplianceSummary.Add(AddCategorywiseSite(newSite, site));
}
}
return(CategoryComplianceSummary);
}
public DataSourceResult TracerComplianceDetails([DataSourceRequest] DataSourceRequest request, SearchER search)
{
List <CompliaceByTracerDetails> tracerComplianceDetails = new List <CompliaceByTracerDetails>();
DataTable dt = new DataTable();
DataSourceResult result = new DataSourceResult();
try
{
dt = this.GetComplianceTracerData(search, "details").Tables[0];
var siteTransform = dt.ToList <ComplianceBySiteTransform>();
//convert datatable to list
tracerComplianceDetails = TransformDashboardSite(siteTransform, search);
result = tracerComplianceDetails.ToDataSourceResult(request, tc => new CompliaceByTracerDetails
{
QuestionText = tc.QuestionText,
QuesNo = tc.QuesNo,
OverallTracerCompliance = tc.OverallTracerCompliance,
QuestionwiseSite = tc.QuestionwiseSite,
Tracer = tc.Tracer
});
}
catch (Exception ex)
{
if (ex.Message.ToString() == "No Data")
{
result.Errors = WebConstants.NO_DATA_FOUND_EXCEL_VIEW;
}
else if (ex.Message.ToString() == "Limit")
{
result.Errors = "Maximum limit of " + ConfigurationManager.AppSettings["ReportOutputLimit"].ToString() + " records reached. Refine your criteria to narrow the result.";
}
if (ex.Message.ToString() != "No Data" && ex.Message.ToString() != "Limit")
{
ExceptionLog exceptionLog = new ExceptionLog
{
ExceptionText = "Reports: " + ex.Message,
PageName = "ERComplianceByTracer",
MethodName = "TracerComplianceDetails",
UserID = Convert.ToInt32(AppSession.UserID),
SiteId = Convert.ToInt32(AppSession.SelectedSiteId),
TransSQL = "",
HttpReferrer = null
};
_exceptionService.LogException(exceptionLog);
}
}
return(result);
}
private List <CompliaceByTracerDetails> TransformDashboardSite(List <ComplianceBySiteTransform> siteTransform, SearchER search)
{
List <CompliaceByTracerDetails> SiteComplianceDetails = new List <CompliaceByTracerDetails>();
ERSearchInputService reportservice = new ERSearchInputService();
var siteList = reportservice.GetSitesList(search.SelectedSiteIDs);
// var siteList = siteTransform.Select(item => item.SiteName).Distinct().OrderBy(item => PadNumbers(item)).ToList();
foreach (var tracerquestion in siteTransform)
{
if (SiteComplianceDetails.Any(item => tracerquestion.tracerquestion_rank == item.tracerquestion_rank && tracerquestion.tracercustom_rank == item.tracercustom_rank))
{
var newQuestion = SiteComplianceDetails.FirstOrDefault(item => string.Equals(tracerquestion.questiontext, item.QuestionText, StringComparison.CurrentCultureIgnoreCase));
AddSitewiseQuestion(newQuestion, tracerquestion);
}
else
{
var newQuestion = PopulateSitewiseInfo(siteList);
newQuestion.QuestionText = tracerquestion.questiontext;
newQuestion.tracerquestion_rank = tracerquestion.tracerquestion_rank;
newQuestion.tracercustom_rank = tracerquestion.tracercustom_rank;
newQuestion.QuesNo = tracerquestion.QuesNo;
newQuestion.Tracer = tracerquestion.TracerCustomName;
SiteComplianceDetails.Add(AddSitewiseQuestion(newQuestion, tracerquestion));
}
}
return(SiteComplianceDetails);
}
public DataSourceResult TracerDashboardHeatMap([DataSourceRequest] DataSourceRequest request, SearchER search)
{
List <CompliaceByTracerHeatMap> tracerComplianceHeatMap = new List <CompliaceByTracerHeatMap>();
DataTable dt = new DataTable();
DataSourceResult result = new DataSourceResult();
try
{
dt = this.GetComplianceTracerData(search, "heatmap").Tables[0];
var tracerTransform = dt.ToList <ComplianceByTracerTransform>();
//convert datatable to list
tracerComplianceHeatMap = TransformDashboardTracer(tracerTransform, search);
result = tracerComplianceHeatMap.ToDataSourceResult(request, tc => new CompliaceByTracerHeatMap
{
// TO DO Get Excel View DataSet
SiteName = tc.SiteName,
HCOID = tc.HCOID.ToString() == "0" ? "" : tc.HCOID.ToString(),
OverallTotalCompletedObservation = tc.OverallTotalCompletedObservation,
OverallTracerCompliance = tc.OverallTracerCompliance,
SitewiseTracer = tc.SitewiseTracer
});
}
catch (Exception ex)
{
if (ex.Message.ToString() == "No Data")
{
result.Errors = WebConstants.NO_DATA_FOUND_EXCEL_VIEW;
}
else if (ex.Message.ToString() == "Limit")
{
result.Errors = "Maximum limit of " + ConfigurationManager.AppSettings["ReportOutputLimit"].ToString() + " records reached. Refine your criteria to narrow the result.";
}
if (ex.Message.ToString() != "No Data" && ex.Message.ToString() != "Limit")
{
ExceptionLog exceptionLog = new ExceptionLog
{
ExceptionText = "Reports: " + ex.Message,
PageName = "ERTracerDashboard",
MethodName = "TracerDashboardHeatMap",
UserID = Convert.ToInt32(AppSession.UserID),
SiteId = Convert.ToInt32(AppSession.SelectedSiteId),
TransSQL = "",
HttpReferrer = null
};
_exceptionService.LogException(exceptionLog);
}
}
return(result);
}
public ActionResult HierarchyBinding_Category([DataSourceRequest] DataSourceRequest request, SearchER search)
{
ERSearchInputService reportservice = new ERSearchInputService();
var tcList = reportservice.GetUHSTracersList(search.SelectedSiteIDs, search.ProgramIDs).TracersLists.DistinctBy(x => x.TracerCategoryName).ToList();
tcList.RemoveAt(0);
//var tDicList = tcList.Where(x=>x.TracerCategoryID == i).Select(x=>x.TracerCustomName).Distinct().ToList();
//ViewBag.Header = tDicList;
//i++;
DataSourceResult result = tcList.ToDataSourceResult(request, tc => new Tracers
{
TracerCategoryName = tc.TracerCategoryName,
TracerCategoryID = tc.TracerCategoryID
});
var val = Json(result, JsonRequestBehavior.AllowGet);
val.MaxJsonLength = int.MaxValue;
return(val);
}
public ActionResult TracerDashordHeatMap([DataSourceRequest] DataSourceRequest request, SearchER search)
{
var tcService = new ERTracerDashboard();
DataSourceResult result = tcService.TracerDashboardHeatMap(request, search);
if (result.Errors != null && result.Errors.ToString() != "")
{
ModelState.AddModelError("Error", result.Errors.ToString());
}
var val = Json(result, JsonRequestBehavior.AllowGet);
val.MaxJsonLength = int.MaxValue;
return(val);
}
private DataSet GetComplianceTracerData(SearchER searchParams, string reportType)
{
DataSet ds = new DataSet();
try
{
searchParams.EndDate = (searchParams.EndDate != null && searchParams.EndDate.ToString() != "") ? searchParams.EndDate.Value.Date.AddHours(23).AddMinutes(29).AddSeconds(59) : searchParams.EndDate;
if (searchParams.TracerListIDs == "-1")
{
searchParams.TracerListIDs = string.Empty;
}
if (searchParams.ProgramIDs == "-1")
{
searchParams.ProgramIDs = string.Empty;
}
// searchParams.ProgramIDs = 2;
string sp = "ustERReport_ERTracerDashboardHeatMap";
if (reportType == "details")
{
sp = "ustERReport_ERTracerDashboardDetails";
}
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(sp, cn);
if (sp == "ustERReport_ERTracerDashboardHeatMap")
{
cmd.Parameters.AddWithValue("ShowAllSites", searchParams.IncludeAllSite == true ? 1 : 0);
}
cmd.CommandTimeout = 900;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SiteIDs", searchParams.SelectedSiteIDs);
cmd.Parameters.AddWithValue("TracerIDs", searchParams.TracerListIDs);
cmd.Parameters.AddWithValue("ResponseStartDate", searchParams.StartDate);
cmd.Parameters.AddWithValue("ResponseEndDate", searchParams.EndDate);
//Get the SQL statement for logging
CreateSQLExecuted(sp, cmd);
#if DEBUG
System.Diagnostics.Debug.WriteLine(_SQLExecuted);
#endif
SqlDataAdapter da = new SqlDataAdapter(cmd);
using (cn)
using (cmd)
using (da)
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
ex.Data.Add(TSQL, _SQLExecuted);
throw ex;
}
//If no data exists, throw an exception to display the no rows found message in the UI
if (ds.Tables[0].Rows.Count == 0)
{
throw (new Exception("No Data"));
}
return(ds);
}
public ActionResult CreateERSessionCriteria(SearchER ERsearch)
{
Session["ERsearchcriteria"] = ERsearch;
return(Json(new { success = true }, JsonRequestBehavior.AllowGet));
}
public ActionResult TracersByTJCStandard_Data(SearchER search, int LevelIdentifier)
{
TracersByTJCStandard reportservice = new TracersByTJCStandard();
JsonResult jr = new JsonResult();
switch (LevelIdentifier)
{
case (int)WebConstants.TracerByTJCStandardLevels.Level1_Program:
{
List <ErTracersbyProgramData> Level1Data = new List <ErTracersbyProgramData>();
Level1Data = reportservice.GetLevel1Data(search);
jr = Json(Level1Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerByTJCStandardLevels.Level2_Site:
{
List <ErTracersbySiteData> Level2Data = new List <ErTracersbySiteData>();
Level2Data = reportservice.GetLevel2Data(search);
jr = Json(Level2Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerByTJCStandardLevels.Level3_Chapter:
{
List <ErTracersbyChapterData> Level3Data = new List <ErTracersbyChapterData>();
Level3Data = reportservice.GetLevel3Data(search);
jr = Json(Level3Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerByTJCStandardLevels.Level4_Standard:
{
List <ErTracersbyStandardData> Level4Data = new List <ErTracersbyStandardData>();
Level4Data = reportservice.GetLevel4Data(search);
jr = Json(Level4Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerByTJCStandardLevels.Level5_EP:
{
List <ErTracersbyEPData> Level5Data = new List <ErTracersbyEPData>();
Level5Data = reportservice.GetLevel5Data(search);
jr = Json(Level5Data, JsonRequestBehavior.AllowGet);
break;
}
case (int)WebConstants.TracerByTJCStandardLevels.Level6_EPDetails:
{
List <ErTracersbyEPDetails> Level6Data = new List <ErTracersbyEPDetails>();
Level6Data = reportservice.GetLevel6Data(search);
jr = Json(Level6Data, JsonRequestBehavior.AllowGet);
break;
}
}
jr.MaxJsonLength = Int32.MaxValue;
jr.RecursionLimit = 100;
return(jr);
}
