public List <EPNotScoredInPeriodExcel> GetEPNotScoredInPeriodExcel(SearchEPNotScoredInPeriodParams search) { var GetEPNotScoredInPeriodData = new List <EPNotScoredInPeriodExcel>(); GetEPNotScoredInPeriodData = GetReportDataView(search, "Excel").Tables[0].ToList <EPNotScoredInPeriodExcel>(); GetEPNotScoredInPeriodData.ForEach(a => a.CoPHTML = string.IsNullOrEmpty(a.CoPHTML) == false ? a.CoPHTML.Replace("<", "<").Replace(">", ">") : null); GetEPNotScoredInPeriodData.ForEach(a => a.CoPExcel = string.IsNullOrEmpty(a.CoPExcel) == false ? a.CoPExcel.Trim().TrimEnd(';') : null); return(GetEPNotScoredInPeriodData); }
public PartialViewResult _GetEPNotScoredInPeriodRDLCData(SearchEPNotScoredInPeriodParams search, Email emailInput, string ReportType = "Summary") { ReportViewer reportViewer = new ReportViewer(); try { if (String.IsNullOrEmpty(search.ScoreValueList)) { search.ScoreValueList = ""; } var findingsService = new EPNotScoredInPeriod(); if (emailInput.To != null) { ViewBag.FromEmail = true; ViewBag.FromEmailSuccess = WebConstants.Email_Success; } reportViewer = findingsService.EPNotScoredInPeriodRDLC(search, emailInput, ReportType); if (Session["EmailSuccess"] != null) { if (Session["EmailSuccess"].ToString() == "false") { ViewBag.FromEmailSuccess = WebConstants.Email_Failed; } } } catch (Exception ex) { if (ex.Message.ToString() != "Email") { if (ex.Message.ToString() == "No Data") { ModelState.AddModelError("Error", WebConstants.NO_DATA_FOUND_RDLC_VIEW_TSR); } } else { ViewBag.FromEmail = true; ModelState.AddModelError("Error", WebConstants.Email_Failed); } } finally { if (Session["EmailSuccess"] != null) { Session.Remove("EmailSuccess"); } } Session["MyReportViewer"] = reportViewer; return(PartialView("_ReportViewer")); }
public ActionResult _GetEPNotScoredInPeriodExcelData(SearchEPNotScoredInPeriodParams search) { JsonResult jr = new JsonResult(); List <EPNotScoredInPeriodExcel> EpExcelData = new List <EPNotScoredInPeriodExcel>(); try { var findingsService = new EPNotScoredInPeriod(); EpExcelData = findingsService.GetEPNotScoredInPeriodExcel(search); jr = Json(EpExcelData, JsonRequestBehavior.AllowGet); jr.MaxJsonLength = Int32.MaxValue; jr.RecursionLimit = 100; return(jr); } catch (Exception ex) { if (ex.Message.ToString() == "No Data") { jr = Json(EpExcelData, JsonRequestBehavior.AllowGet); return(jr); } else { ExceptionLog exceptionLog = new ExceptionLog { ExceptionText = "Reports: " + ex.Message, PageName = "EPNotScoredInPeriodSummary", MethodName = "_GetEPNotScoredInPeriodExcelData", UserID = Convert.ToInt32(AppSession.UserID), SiteId = Convert.ToInt32(AppSession.SelectedSiteId), TransSQL = "", HttpReferrer = null }; exceptionService.LogException(exceptionLog); return(RedirectToAction("Error", "Transfer")); } } }
private DataSet GetReportDataView(SearchEPNotScoredInPeriodParams searchParams, string reportType = "Summary") { DataSet ds = new DataSet(); try { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { cn.Open(); string spname = string.Empty; if (reportType == "Summary") { spname = "amp.usmReportEPNotScoredInPeriodSummary"; } else if (reportType == "Detail") { spname = "amp.usmReportEPNotScoredInPeriodDetail"; } else { spname = "amp.usmReportEPNotScoredInPeriodExcel"; } SqlCommand cmd = new SqlCommand(spname, cn); cmd.CommandTimeout = Convert.ToInt32(ConfigurationManager.AppSettings["SPCmdTimeout"].ToString()); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("SiteID", searchParams.SiteID); cmd.Parameters.AddWithValue("ProgramID", searchParams.ProgramID); if (searchParams.ChapterList == "-1") { cmd.Parameters.AddWithValue("ChapterList", null); } else { cmd.Parameters.AddWithValue("ChapterList", searchParams.ChapterList); } if (searchParams.StandardList == "-1") { cmd.Parameters.AddWithValue("StandardList", null); } else { cmd.Parameters.AddWithValue("StandardList", searchParams.StandardList); } cmd.Parameters.AddWithValue("ScoreType", searchParams.ScoreType); cmd.Parameters.AddWithValue("IncludeFSA", searchParams.FSA); cmd.Parameters.AddWithValue("DocRequired", searchParams.DocRequiredValue); cmd.Parameters.AddWithValue("NewChangedEPs", searchParams.NewChangedEPs); cmd.Parameters.AddWithValue("PlanOfAction", searchParams.PlanOfAction); cmd.Parameters.AddWithValue("OrgFindings", searchParams.OrgFindings); cmd.Parameters.AddWithValue("OrgNotes", searchParams.OrgNotes); cmd.Parameters.AddWithValue("LinkedDocs", searchParams.LinkedDocs); cmd.Parameters.AddWithValue("DateStart", searchParams.DateStart); cmd.Parameters.AddWithValue("DateEnd", searchParams.DateEnd); cmd.Parameters.AddWithValue("StandardEffBeginDate", null); cmd.Parameters.AddWithValue("CertificationItemID", null); if (reportType == "Detail" || reportType == "Excel") { cmd.Parameters.AddWithValue("IncludeCMS", searchParams.chkIncludeCMS); } #if DEBUG CreateSQLExecuted(spname, 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; } int rowsCount = ds.Tables[0].Rows.Count; if (rowsCount == 0) { throw (new Exception("No Data")); } return(ds); }
public ReportViewer EPNotScoredInPeriodRDLC(SearchEPNotScoredInPeriodParams searchParams, Email emailInput, string reportType = "Summary") { DateTime?startDate = null; DateTime?endDate = null; string reportDate = "All Dates"; SearchFormat searchoutput = new SearchFormat(); if (searchParams.DateStart != null) { startDate = searchParams.DateStart; if (searchParams.DateEnd != null) { reportDate = searchParams.DateStart.Value.ToShortDateString() + " - " + searchParams.DateEnd.Value.ToShortDateString(); } else { reportDate = "since " + searchParams.DateStart.Value.ToShortDateString(); } } if (searchParams.DateEnd != null) { endDate = searchParams.DateEnd.Value.Date.AddHours(23).AddMinutes(59).AddSeconds(59); if (searchParams.DateStart == null) { reportDate = "through " + searchParams.DateEnd.Value.ToShortDateString(); } } ReportViewer reportViewer = new ReportViewer(); reportViewer.ProcessingMode = ProcessingMode.Local; reportViewer.SizeToReportContent = true; try { if (AppSession.ReportScheduleID > 0 && searchParams.ReportTitle != null) { searchParams.ReportTitle = String.Concat(searchParams.ReportTitle, " - Report ID: ", AppSession.ReportScheduleID); } else { if (reportType == "Summary") { searchParams.ReportTitle = "EPs Not Scored in Period - Summary"; } else { searchParams.ReportTitle = "EPs Not Scored in Period - Detail"; } } string rdlcfilename = string.Empty; string dsName = string.Empty; if (reportType == "Summary") { rdlcfilename = "rptEP_Not_Scored_In_Period_Summary.rdlc"; dsName = "dsEPNotScoredInPeriodSummaryView"; } else { rdlcfilename = "rptEP_Not_Scored_In_Period_Detail.rdlc"; dsName = "dsEPNotScoredInPeriodDetail"; } DataView dvReportResultForEPScoring = new DataView(GetReportDataView(searchParams, reportType).Tables[0]); reportViewer.LocalReport.DisplayName = searchParams.ReportTitle; reportViewer.LocalReport.ReportPath = HttpContext.Current.Request.MapPath(HttpContext.Current.Request.ApplicationPath) + @"Areas\Corporate\Reports\" + rdlcfilename; if (searchParams.ScoreType == 3)//for Final View Type { reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dsName, new DataView())); reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dsName + "_Final", dvReportResultForEPScoring)); } else { reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dsName, dvReportResultForEPScoring)); reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dsName + "_Final", new DataView())); } string filterBy = string.Empty; if (searchParams.FSA == 1) { filterBy = "\nInclude FSA Eps"; } if (searchParams.DocRequiredValue == 1) { filterBy += "\nDocumentation Required"; } if (searchParams.NewChangedEPs == 1) { filterBy += "\nNew Eps"; } searchParams.ScoreTypeNameList = string.Empty; if (searchParams.ScoreType == 1) { searchParams.ScoreTypeNameList = "Individual"; } if (searchParams.ScoreType == 2) { searchParams.ScoreTypeNameList = "Preliminary"; } if (searchParams.ScoreType == 3) { searchParams.ScoreTypeNameList = "Final"; } ReportParameter p1 = new ReportParameter("ReportTitle", searchParams.ReportTitle); ReportParameter p2 = new ReportParameter("Copyright", "© " + DateTime.Now.Year.ToString() + WebConstants.Copyright.ToString()); ReportParameter p3 = new ReportParameter("ProgramName", searchParams.ProgramName); ReportParameter p4 = new ReportParameter("SiteName", searchParams.SiteName); ReportParameter p5 = new ReportParameter("ReportDateTitle", DateTime.Now.ToString()); ReportParameter p6 = new ReportParameter("ChapterNameList", searchParams.ChapterNameList); ReportParameter p7 = new ReportParameter("StandardNameList", searchParams.StandardNameList); ReportParameter p9 = new ReportParameter("ScoreDateRange", reportDate); ReportParameter p11 = new ReportParameter("ScoreTypeNameList", Regex.Replace(searchParams.ScoreTypeNameList, ", $", "")); ReportParameter p12 = new ReportParameter("FilterByList", filterBy == "" ? "None" : filterBy); string DocumentationList = string.Empty; if (searchParams.OrgFindings == 1) { DocumentationList = "All Organizational Findings\n"; } else if (searchParams.OrgFindings == 2) { DocumentationList = "Presence of Organizational Findings\n"; } else if (searchParams.OrgFindings == 3) { DocumentationList = "Absence of Organizational Findings\n"; } if (searchParams.PlanOfAction == 1) { DocumentationList = DocumentationList + "All Plan of Action\n"; } else if (searchParams.PlanOfAction == 2) { DocumentationList = DocumentationList + "Presence of Plan of Action\n"; } else if (searchParams.PlanOfAction == 3) { DocumentationList = DocumentationList + "Absence of Plan of Action\n"; } if (searchParams.OrgNotes == 1) { DocumentationList = DocumentationList + "All Internal Notes\n"; } else if (searchParams.OrgNotes == 2) { DocumentationList = DocumentationList + "Presence of Internal Notes\n"; } else if (searchParams.OrgNotes == 3) { DocumentationList = DocumentationList + "Absence of Internal Notes\n"; } if (searchParams.LinkedDocs == 1) { DocumentationList = DocumentationList + "All Linked Documents\n"; } else if (searchParams.LinkedDocs == 2) { DocumentationList = DocumentationList + "Presence of Linked Documents\n"; } else if (searchParams.LinkedDocs == 3) { DocumentationList = DocumentationList + "Absence of Linked Documents"; } ReportParameter p13 = new ReportParameter("DocumentationList", DocumentationList); ReportParameter p15 = new ReportParameter("ProgramID", searchParams.ProgramID.ToString()); ReportParameter p16 = new ReportParameter("IncludeCMS", searchParams.chkIncludeCMS.ToString()); ReportParameterCollection reportParameterCollection = new ReportParameterCollection { p1, p2, p3, p4, p5, p6, p7, p9, p11, p12, p13, p15, p16 }; reportParameterCollection.Add(new ReportParameter("ScoreType", searchParams.ScoreType.ToString())); reportViewer.LocalReport.SetParameters(reportParameterCollection); if (emailInput.To != null) { CommonService emailService = new CommonService(); int actionTypeId = (int)ActionTypeEnum.EPAssignmentReport; if (emailService.SendReportEmail(emailInput, actionTypeId, emailService.SetRdlcEmail(reportViewer))) { HttpContext.Current.Session["EmailSuccess"] = "true"; } else { HttpContext.Current.Session["EmailSuccess"] = "false"; } } } catch (Exception ex) { if (ex.Message.ToString() != "No Data") { ExceptionLog exceptionLog = new ExceptionLog { ExceptionText = "Reports: " + ex.Message + ";" + ex.InnerException.Message, PageName = "EPNotScoredInPeriodSummary", MethodName = "EPNotScoredInPeriodSummaryRDLC", UserID = Convert.ToInt32(AppSession.UserID), SiteId = Convert.ToInt32(AppSession.SelectedSiteId), TransSQL = "", HttpReferrer = null }; _exceptionService.LogException(exceptionLog); } throw ex; } return(reportViewer); }