//[HttpPost] public CrystalReportPdfResult CaseDashboardPDF(CaseDashboardRptInput model) { DataTable table = reportRepository.CaseDashboard(model); //List<CaseDashboardrpt> table = reportRepository.CaseDashboardExcel(model); string reportPath = Path.Combine(Server.MapPath("~/Reports"), "CaseDashboardNew.rpt"); return(new CrystalReportPdfResult(reportPath, table)); }
public ActionResult CaseDashboard(string Command, CaseDashboardRptInput model) { bool hasAccess = workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.Reporting, Constants.Controllers.Report, Constants.Actions.CaseDashboard, true); if (!hasAccess) { WebHelper.CurrentSession.Content.ErrorMessage = "You are not eligible to do this action"; return(RedirectToAction(Constants.Actions.AccessDenied, Constants.Controllers.Home, new { Area = String.Empty })); } try { if (ModelState.IsValid) { if (Command == "CaseDashboardExcel") { return(CaseDashboardExcel(model)); } if (Command == "CaseDashboardPDF") { return(CaseDashboardPDF(model)); } } else { foreach (var modelStateValue in ViewData.ModelState.Values) { foreach (var error in modelStateValue.Errors) { model.ErrorMessage = error.ErrorMessage; break; } if (model.ErrorMessage.IsNotNullOrEmpty()) { break; } } } } catch (CustomException ex) { model.ErrorMessage = ex.UserDefinedMessage; } catch (Exception ex) { ExceptionManager.Manage(ex); model.ErrorMessage = Constants.Messages.UnhandelledError; } return(View(model)); }
//[HttpPost] public ActionResult CaseDashboardExcel(CaseDashboardRptInput model) { List <CaseDashboardrpt> table = reportRepository.CaseDashboardExcel(model); //table.Columns.Remove("Region"); //Closed XML using (XLWorkbook wb = new XLWorkbook()) { //Add DataTable as Worksheet. using (var ws = wb.AddWorksheet("CaseDashboard")) { int headcount = 1; ws.Cell("B1").SetValue("Families With Enrollment Date >=January 1, 2015"); ws.Cell("B1").Style.Alignment.WrapText = true; ws.Cell("B1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("B1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("B1").Style.Border.LeftBorder = XLBorderStyleValues.Medium; ws.Cell("B1").Style.Border.RightBorder = XLBorderStyleValues.Medium; ws.Cell("B1").Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Cell("B1").Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Range("B1:E1").Merge(); ws.Cell("F1").SetValue("Family Members Entered in OJCMS -Data Quality Context"); //ws.Cell("F1").Style.Alignment.WrapText = true; ws.Cell("F1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("F1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("F1").Style.Border.LeftBorder = XLBorderStyleValues.Medium; ws.Cell("F1").Style.Border.RightBorder = XLBorderStyleValues.Medium; ws.Cell("F1").Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Cell("F1").Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Range("F1:Q1").Merge(); ws.Cell("R1").SetValue("Active QoL Families"); //ws.Cell("R1").Style.Alignment.WrapText = true; ws.Cell("R1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("R1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("R1").Style.Border.LeftBorder = XLBorderStyleValues.Medium; ws.Cell("R1").Style.Border.RightBorder = XLBorderStyleValues.Medium; ws.Cell("R1").Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Cell("R1").Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Range("R1:W1").Merge(); ws.Cell("X1").SetValue("Family Status - case overview / case progress"); //ws.Cell("X1").Style.Alignment.WrapText = true; ws.Cell("X1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("X1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("X1").Style.Border.LeftBorder = XLBorderStyleValues.Medium; ws.Cell("X1").Style.Border.RightBorder = XLBorderStyleValues.Medium; ws.Cell("X1").Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Cell("X1").Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Range("X1:AU1").Merge(); ws.Rows(headcount, headcount).Style.Fill.BackgroundColor = XLColor.FromArgb(91, 155, 213); ws.Rows(headcount, headcount).Style.Font.Bold = true; ws.Rows(headcount, headcount).Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Rows(headcount, headcount).Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Row(1).Height = 50; headcount += 1; string[] columnsheader = GetColumnHeader(); ws.Column(1).Width = 15; for (int clcnt = 0; clcnt < columnsheader.Length; clcnt++) { // Adding HeaderRow. ws.Cell(headcount, clcnt + 1).SetValue(columnsheader[clcnt]); ws.Cell(headcount, clcnt + 1).Style.Border.LeftBorder = XLBorderStyleValues.Medium; ws.Cell(headcount, clcnt + 1).Style.Border.RightBorder = XLBorderStyleValues.Medium; ws.Cell(headcount, clcnt + 1).Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Cell(headcount, clcnt + 1).Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Column(clcnt + 2).Width = 4; } //Alignment align = new Alignment(); //align.TextRotation.Value = (UInt32Value)90U; //CellFormat cellFormat1 = new CellFormat(){ // NumberFormatId = (UInt32Value)0U, // FontId = (UInt32Value)0U, // FillId = (UInt32Value)0U, // BorderId = (UInt32Value)0U, // FormatId = (UInt32Value)0U, // ApplyAlignment = true }; //cellFormat1.Append(align); ws.Rows(headcount, headcount).Style.Fill.BackgroundColor = XLColor.FromArgb(91, 155, 213); ws.Rows(headcount, headcount).Style.Font.Bold = true; ws.Rows(headcount, headcount).Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Rows(headcount, headcount).Style.Border.TopBorder = XLBorderStyleValues.Medium; ws.Rows(headcount, headcount).Style.Alignment.TextRotation = -90; ws.Rows(headcount, headcount).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; headcount += 1; int columncnt; // Adding DataRows. foreach (CaseDashboardrpt row in table) { columncnt = 0; ws.Cell(headcount, ++columncnt).SetValue(row.SubProgram); //Section1 ws.Cell(headcount, ++columncnt).SetValue(row.TotalFamilies); ws.Cell(headcount, ++columncnt).SetValue(row.NoofJKS); ws.Cell(headcount, ++columncnt).SetValue(row.WithLICO); ws.Cell(headcount, ++columncnt).SetValue(row.LicoPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; //Section2 ws.Cell(headcount, ++columncnt).SetValue(row.TotalFamilyMembers); ws.Cell(headcount, ++columncnt).SetValue(row.AvgFamilyMember); ws.Cell(headcount, ++columncnt).SetValue(row.TotalMemberProfile); ws.Cell(headcount, ++columncnt).SetValue(row.MemberProfilePer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.InitAssessment); ws.Cell(headcount, ++columncnt).SetValue(row.InitialAssessmentPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.CaseGoalIdentified); ws.Cell(headcount, ++columncnt).SetValue(row.CaseGoalIdentifiedPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.CaseGoalSet); ws.Cell(headcount, ++columncnt).SetValue(row.CaseGoalSetPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.CaseActionDefined); ws.Cell(headcount, ++columncnt).SetValue(row.CaseActionDefinedPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; //Section3 ws.Cell(headcount, ++columncnt).SetValue(row.NoOfActiveQOLFamilies); ws.Cell(headcount, ++columncnt).SetValue(row.NoOfActiveQOLFamiliesPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedGoalCount); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedGoalCountPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedActionCount); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedActionCountPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; //Section4 ws.Cell(headcount, ++columncnt).SetValue(row.MonFamNotReady); ws.Cell(headcount, ++columncnt).SetValue(row.MonFamNotReadyPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.MonRefExtAgency); ws.Cell(headcount, ++columncnt).SetValue(row.MonRefExtAgencyPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedNotQualified); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedNotQualifiedPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ActiveInProgress); ws.Cell(headcount, ++columncnt).SetValue(row.ActiveInProgressPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ActiveOnBoarding); ws.Cell(headcount, ++columncnt).SetValue(row.ActiveOnBoardingPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.MonitoringCompleted); ws.Cell(headcount, ++columncnt).SetValue(row.MonitoringCompletedPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.Hold); ws.Cell(headcount, ++columncnt).SetValue(row.HoldPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedCompleted); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedCompletedPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedExternalAgencyFulfilled); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedExternalAgencyFulfilledPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedFamilyDeclineCasePlan); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedFamilyDeclineCasePlanPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedFamilyWithdrew); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedFamilyWithdrewPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; ws.Cell(headcount, ++columncnt).SetValue(row.ClosedLackofFamilyEngagement); ws.Cell(headcount, ++columncnt).SetValue(row.ClosedLackofFamilyEngagementPer); ws.Cell(headcount, columncnt).Style.NumberFormat.Format = @"0\%;[Red](0\%)"; if (row.SubProgram.Contains("Summary")) { // Changing color to green. ws.Rows(headcount, headcount).Style.Font.Bold = true; ws.Rows(headcount, headcount).Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Rows(headcount, headcount).Style.Border.TopBorder = XLBorderStyleValues.Medium; } if (row.Region == "2") { // Changing color to green. ws.Rows(headcount, headcount).Style.Font.Bold = true; ws.Rows(headcount, headcount).Style.Fill.BackgroundColor = XLColor.FromArgb(91, 155, 213); ws.Rows(headcount, headcount).Style.Border.BottomBorder = XLBorderStyleValues.Medium; ws.Rows(headcount, headcount).Style.Border.TopBorder = XLBorderStyleValues.Medium; } headcount += 1; } } //wb.Worksheets.Add(table); //Export the Excel file. Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=CaseDashboard.xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); return(new FileStreamResult(MyMemoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); } } //Open XML //MemoryStream memoryStream = new ExcelHelper().WriteToStream(table); //string excelfile = "CaseDashboard.xlsx"; //// Prepare the response //Response.Clear(); //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=\"" + excelfile + ""); //memoryStream.WriteTo(Response.OutputStream); //memoryStream.Close(); //Response.End(); //return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //Crystal Report //ReportDocument rd = new ReportDocument(); //rd.Load(Path.Combine(Server.MapPath("~/Reports"), "CaseDashboardNew.rpt")); //rd.SetDataSource(table); ////rd.Subreports[0].SetDataSource(table); //Response.Buffer = false; //Response.ClearContent(); //Response.ClearHeaders(); //Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); //stream.Seek(0, SeekOrigin.Begin); //return File(stream, "application/pdf", "CustomerList.pdf"); }
public string GetReportQuery(CaseDashboardRptInput model) { //string regionid = String.Join(",", model.RegionID); //string programid = String.Join(",", model.ProgramID); //string subprogramid = String.Join(",", model.SubProgramID); //string jamatkhanaid = String.Join(",", model.JamatkhanaID); //string sqlquery = string.Format(GetReportQuery(), regionid, programid, subprogramid, jamatkhanaid); StringBuilder sqlquery = new StringBuilder(); sqlquery.Append("SELECT rpt.Region,rpt.SubProgram "); sqlquery.Append(",ISNULL(SUM(rpt.TotalFamilies),0) AS TotalFamilies,ISNULL(SUM(rpt.NoofJKS),0) AS NoofJKS,ISNULL(SUM(rpt.WithLICO),0) AS WithLICO,ISNULL(SUM(rpt.TotalFamilyMembers),0) AS TotalFamilyMembers "); sqlquery.Append(",ISNULL(SUM(rpt.TotalMemberProfile),0) AS TotalMemberProfile,ISNULL(SUM(rpt.InitAssessment),0) AS InitAssessment,ISNULL(SUM(rpt.CaseGoalIdentified),0) AS CaseGoalIdentified "); sqlquery.Append(",ISNULL(SUM(rpt.CaseGoalSet),0) AS CaseGoalSet,ISNULL(SUM(rpt.CaseActionDefined),0) AS CaseActionDefined,ISNULL(SUM(rpt.NoOfActiveQOLFamilies),0) AS NoOfActiveQOLFamilies "); sqlquery.Append(",ISNULL(SUM(rpt.ClosedGoalCount),0) AS ClosedGoalCount,ISNULL(SUM(rpt.ClosedActionCount),0) AS ClosedActionCount "); sqlquery.Append(",ISNULL(SUM(rpt.MonFamNotReady),0) AS MonFamNotReady,ISNULL(SUM(rpt.MonRefExtAgency),0) AS MonRefExtAgency "); sqlquery.Append(",ISNULL(SUM(rpt.MonRefExtAgency),0) AS MonRefExtAgency,ISNULL(SUM(rpt.ClosedNotQualified),0) AS ClosedNotQualified "); sqlquery.Append(",ISNULL(SUM(rpt.ActiveInProgress),0) AS ActiveInProgress,ISNULL(SUM(rpt.ActiveOnBoarding),0) AS ActiveOnBoarding "); sqlquery.Append(",ISNULL(SUM(rpt.MonitoringCompleted),0) AS MonitoringCompleted,ISNULL(SUM(rpt.Hold),0) AS Hold "); sqlquery.Append(",ISNULL(SUM(rpt.ClosedCompleted),0) AS ClosedCompleted,ISNULL(SUM(rpt.ClosedExternalAgencyFulfilled),0) AS ClosedExternalAgencyFulfilled "); sqlquery.Append(",ISNULL(SUM(rpt.ClosedFamilyDeclineCasePlan),0) AS ClosedFamilyDeclineCasePlan,ISNULL(SUM(rpt.ClosedFamilyWithdrew),0) AS ClosedFamilyWithdrew "); sqlquery.Append(",ISNULL(SUM(rpt.ClosedLackofFamilyEngagement),0) AS ClosedLackofFamilyEngagement "); sqlquery.Append("FROM "); sqlquery.Append("(SELECT C.RegionID, C.SubProgramID,R.Name AS Region,SP.Name AS SubProgram "); // sqlquery.Append("---section1------------------ "); sqlquery.Append(",COUNT(DISTINCT C.ID) AS TotalFamilies "); sqlquery.Append(",COUNT(DISTINCT C.JamatkhanaID) AS NoofJKS "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN (CAL.QualityOfLifeID = 101 OR CAL.QualityOfLifeID = 102) AND (CAL.CaseAssessmentID IS NOT NULL) THEN C.ID END) AS WithLICO "); //sqlquery.Append("---section2------------------ "); sqlquery.Append(",COUNT(DISTINCT CM.ID) AS TotalFamilyMembers "); //sqlquery.Append("-- We need distinct here as one case member can have multiple profiles. Also DISTINCT CMP.CaseMemberID should be used because of left join "); //sqlquery.Append("--with CaseMember "); sqlquery.Append(",COUNT(DISTINCT CMP.CaseMemberID) AS TotalMemberProfile "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN CAS.AssessmentTypeID = 1 THEN CAS.CaseMemberID END) AS InitAssessment "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN CG.CaseMemberID IS NOT NULL THEN CG.CaseMemberID END) AS CaseGoalIdentified "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN CG.CaseMemberID IS NOT NULL AND CSG.CaseGoalID IS NOT NULL THEN CG.CaseMemberID END) AS CaseGoalSet "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN CA.CaseMemberID IS NOT NULL THEN CA.CaseMemberID END) AS CaseActionDefined "); //sqlquery.Append("---section3------------------ "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID < 7 THEN C.ID END) AS NoOfActiveQOLFamilies "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID < 7 AND CSG.IsCompleted = 1 AND CSG.CaseGoalID IS NOT NULL THEN C.ID END) AS ClosedGoalCount "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID < 7 AND CA.IsCompleted = 1 AND CA.CaseMemberID IS NOT NULL THEN C.ID END) AS ClosedActionCount "); //sqlquery.Append("---section4------------------ "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 4 THEN C.ID END) as MonFamNotReady "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 6 THEN C.ID END) as MonRefExtAgency "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 10 THEN C.ID END) as ClosedNotQualified "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 1 THEN C.ID END) as ActiveInProgress "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 2 THEN C.ID END) as ActiveOnBoarding "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 5 THEN C.ID END) as MonitoringCompleted "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 3 THEN C.ID END) as Hold "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 9 THEN C.ID END) as ClosedCompleted "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 8 THEN C.ID END) as ClosedExternalAgencyFulfilled "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 7 THEN C.ID END) as ClosedFamilyDeclineCasePlan "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 14 THEN C.ID END) as ClosedFamilyWithdrew "); sqlquery.Append(",COUNT(DISTINCT CASE WHEN C.CaseStatusID = 13 THEN C.ID END) as ClosedLackofFamilyEngagement "); sqlquery.Append("FROM [dbo].[Case] AS C "); sqlquery.Append("INNER JOIN Region AS R ON C.RegionID = R.ID "); sqlquery.Append("INNER JOIN SubProgram AS SP ON C.SubProgramID = SP.ID "); sqlquery.Append("LEFT JOIN CaseMember AS CM ON C.ID = CM.CaseID "); sqlquery.Append("LEFT JOIN CaseMemberProfile AS CMP ON CM.ID = CMP.CaseMemberID "); //--Initial Assessment sqlquery.Append("LEFT JOIN CaseAssessment AS CAS ON CM.ID = CAS.CaseMemberID "); sqlquery.Append("LEFT JOIN CaseAssessmentLivingCondition AS CAL ON CAS.ID = CAL.CaseAssessmentID "); sqlquery.Append("LEFT JOIN CaseGoal AS CG ON CM.ID = CG.CaseMemberID "); sqlquery.Append("LEFT JOIN CaseSmartGoal AS CSG ON CG.ID = CSG.CaseGoalID "); sqlquery.Append("LEFT JOIN CaseAction AS CA ON CM.ID = CA.CaseMemberID "); sqlquery.Append("WHERE 1=1 "); //sqlquery.Append("--C.ID =7006 AND "); if (model.RegionID != null) { sqlquery.Append("AND C.RegionID IN (" + String.Join(",", model.RegionID) + ") "); } if (model.ProgramID != null) { sqlquery.Append("AND C.ProgramID IN (" + String.Join(",", model.ProgramID) + ") "); } if (model.SubProgramID != null) { sqlquery.Append("AND C.SubProgramID IN (" + String.Join(",", model.SubProgramID) + ") "); } if (model.JamatkhanaID != null) { sqlquery.Append("AND C.JamatkhanaID IN (" + String.Join(",", model.JamatkhanaID) + ") "); } //sqlquery.Append("AND C.EnrollDate >= '2015-01-01 00:00:00.000' "); sqlquery.Append(" AND C.EnrollDate BETWEEN @StartDate AND @EndDate "); sqlquery.Append("AND C.Comments NOT LIKE '[Old%' AND C.CaseStatusID <> 15 "); //Case Status Not Added in error //sqlquery.Append("--GROUP BY GROUPING SETS((C.RegionID, C.SubProgramID,R.Name,SP.Name) ,(R.Name)) "); sqlquery.Append("GROUP BY C.RegionID, C.SubProgramID,R.Name,SP.Name "); sqlquery.Append(") AS rpt "); sqlquery.Append("WHERE rpt.SubProgram IS NOT NULL "); sqlquery.Append("GROUP BY GROUPING SETS((rpt.Region,rpt.SubProgram),(rpt.Region) ,(rpt.SubProgram),()) "); //sqlquery.Append("GROUP BY rpt.Region,rpt.SubProgram "); sqlquery.Append("ORDER BY rpt.Region,rpt.SubProgram "); return(sqlquery.ToString()); }
public DataTable CaseDashboard(CaseDashboardRptInput model) { string constr = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(GetReportQuery(model))) { cmd.Parameters.Add(new SqlParameter("@StartDate", model.StartDate)); cmd.Parameters.Add(new SqlParameter("@EndDate", model.EndDate)); using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataSet ds = new DataSet()) { DataTable dt = new DataTable(); sda.Fill(dt); DataTable regionDT = dt.Select("Region is not NULL", "Region,SubProgram").CopyToDataTable(); DataTable regionGrpDT = dt.Select("Region is NULL").CopyToDataTable(); regionDT.Merge(regionGrpDT); GetDatatableDefinition(ref regionDT); int totalfamilies = 0, totalFamilyMembers = 0, totalActiveFamilies = 0; foreach (DataRow row in regionDT.Rows) { if (row["SubProgram"] == DBNull.Value && row["Region"] == DBNull.Value) // getting the row to edit , change it as you need { row["SubProgram"] = "National Summary"; row["Region"] = "2"; //Set it with 1, Will be used in report for formatting. } else if (row["SubProgram"] == DBNull.Value) { row["SubProgram"] = string.Format("{0} Summary", Convert.ToString(row["Region"])); row["Region"] = "1"; //Set it with 1, Will be used in report for formatting. } else if (row["Region"] == DBNull.Value) // getting the row to edit , change it as you need { row["Region"] = "2"; //Set it with 1, Will be used in report for formatting. } totalfamilies = Convert.ToInt32(row["TotalFamilies"]); if (totalfamilies > 0) { //row["Lico%"] = (int)Math.Ceiling((Convert.ToInt32(row["WithLICO"]) * 100) / totalfamilies); row["Lico%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["WithLICO"]) * 100), totalfamilies)); } totalFamilyMembers = Convert.ToInt32(row["TotalFamilyMembers"]); if (totalFamilyMembers > 0) { row["AvgFamilyMember"] = (int)Math.Round(Decimal.Divide(totalFamilyMembers, totalfamilies)); row["MemberProfile%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["TotalMemberProfile"]) * 100), totalFamilyMembers)); row["InitialAssessment%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["InitAssessment"]) * 100), totalFamilyMembers)); row["CaseGoalIdentified%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["CaseGoalIdentified"]) * 100), totalFamilyMembers)); row["CaseGoalSet%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["CaseGoalSet"]) * 100), totalFamilyMembers)); row["CaseActionDefined%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["CaseActionDefined"]) * 100), totalFamilyMembers)); } totalActiveFamilies = Convert.ToInt32(row["NoOfActiveQOLFamilies"]); if (totalActiveFamilies > 0) { row["ActiveFamily%"] = (int)Math.Round(Decimal.Divide((totalActiveFamilies * 100), totalfamilies)); row["ClosedAction%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedActionCount"]) * 100), totalActiveFamilies)); row["ClosedGoal%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedGoalCount"]) * 100), totalActiveFamilies)); } row["MonFamNotReady%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["MonFamNotReady"]) * 100), totalfamilies)); row["MonRefExtAgency%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["MonRefExtAgency"]) * 100), totalfamilies)); row["ClosedNotQualified%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedNotQualified"]) * 100), totalfamilies)); row["ActiveInProgress%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ActiveInProgress"]) * 100), totalfamilies)); row["ActiveOnBoarding%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ActiveOnBoarding"]) * 100), totalfamilies)); row["MonitoringCompleted%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["MonitoringCompleted"]) * 100), totalfamilies)); row["Hold%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["Hold"]) * 100), totalfamilies)); row["ClosedCompleted%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedCompleted"]) * 100), totalfamilies)); row["ClosedExternalAgencyFulfilled%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedExternalAgencyFulfilled"]) * 100), totalfamilies)); row["ClosedFamilyDeclineCasePlan%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedFamilyDeclineCasePlan"]) * 100), totalfamilies)); row["ClosedFamilyWithdrew%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedFamilyWithdrew"]) * 100), totalfamilies)); row["ClosedLackofFamilyEngagement%"] = (int)Math.Round(Decimal.Divide((Convert.ToInt32(row["ClosedLackofFamilyEngagement"]) * 100), totalfamilies)); } //ds.Tables.Add(regionDT); return(regionDT); //return results; } } } } }
public List <CaseDashboardrpt> CaseDashboardExcel(CaseDashboardRptInput model) { SqlParameter startdate = new SqlParameter("@StartDate", model.StartDate); SqlParameter enddate = new SqlParameter("@EndDate", model.EndDate); object[] parameters = new object[] { startdate, enddate }; List <CaseDashboardrpt> reportlist = context.Database.SqlQuery <CaseDashboardrpt>(GetReportQuery(model), parameters).ToList(); List <CaseDashboardrpt> regionlist = reportlist.Where(m => m.Region != null).OrderBy(m => m.Region).ThenBy(m => m.SubProgram).ToList(); List <CaseDashboardrpt> regionGrplist = reportlist.Where(m => m.Region == null).OrderBy(m => m.Region).ToList(); foreach (CaseDashboardrpt row in regionGrplist) { regionlist.Add(row); } //int totalfamilies = 0, totalFamilyMembers = 0, totalActiveFamilies = 0; foreach (CaseDashboardrpt row in regionlist) { if (row.SubProgram == null && row.Region == null) // getting the row to edit , change it as you need { row.SubProgram = "National Summary"; row.Region = "2"; //Set it with 1, Will be used in report for formatting. } else if (row.SubProgram == null) { row.SubProgram = string.Format("{0} Summary", row.Region); row.Region = "1"; //Set it with 1, Will be used in report for formatting. } else if (row.Region == null) // getting the row to edit , change it as you need { row.Region = "2"; //Set it with 1, Will be used in report for formatting. } //totalfamilies = Convert.ToInt32(row.TotalFamilies); if (row.TotalFamilies > 0) { row.LicoPer = (int)Math.Round(Decimal.Divide((row.WithLICO * 100), row.TotalFamilies)); } //totalFamilyMembers = Convert.ToInt32(row.TotalFamilyMembers); if (row.TotalFamilyMembers > 0) { row.AvgFamilyMember = (int)Math.Round(Decimal.Divide(row.TotalFamilyMembers, row.TotalFamilies)); row.MemberProfilePer = (int)Math.Round(Decimal.Divide(row.TotalMemberProfile * 100, row.TotalFamilyMembers)); row.InitialAssessmentPer = (int)Math.Round(Decimal.Divide(row.InitAssessment * 100, row.TotalFamilyMembers)); row.CaseGoalIdentifiedPer = (int)Math.Round(Decimal.Divide(row.CaseGoalIdentified * 100, row.TotalFamilyMembers)); row.CaseGoalSetPer = (int)Math.Round(Decimal.Divide(row.CaseGoalSet * 100, row.TotalFamilyMembers)); row.CaseActionDefinedPer = (int)Math.Round(Decimal.Divide(row.CaseActionDefined * 100, row.TotalFamilyMembers)); } //totalActiveFamilies = Convert.ToInt32(row.NoOfActiveQOLFamilies); if (row.NoOfActiveQOLFamilies > 0) { row.NoOfActiveQOLFamiliesPer = (int)Math.Round(Decimal.Divide(row.NoOfActiveQOLFamilies * 100, row.TotalFamilies)); row.ClosedActionCountPer = (int)Math.Round(Decimal.Divide(row.ClosedActionCount * 100, row.NoOfActiveQOLFamilies)); row.ClosedGoalCountPer = (int)Math.Round(Decimal.Divide(row.ClosedGoalCount * 100, row.NoOfActiveQOLFamilies)); } row.MonFamNotReadyPer = (int)Math.Round(Decimal.Divide(row.MonFamNotReady * 100, row.TotalFamilies)); row.MonRefExtAgencyPer = (int)Math.Round(Decimal.Divide(row.MonRefExtAgency * 100, row.TotalFamilies)); row.ClosedNotQualifiedPer = (int)Math.Round(Decimal.Divide(row.ClosedNotQualified * 100, row.TotalFamilies)); row.ActiveInProgressPer = (int)Math.Round(Decimal.Divide(row.ActiveInProgress * 100, row.TotalFamilies)); row.ActiveOnBoardingPer = (int)Math.Round(Decimal.Divide(row.ActiveOnBoarding * 100, row.TotalFamilies)); row.MonitoringCompletedPer = (int)Math.Round(Decimal.Divide(row.MonitoringCompleted * 100, row.TotalFamilies)); row.HoldPer = (int)Math.Round(Decimal.Divide(row.Hold * 100, row.TotalFamilies)); row.ClosedCompletedPer = (int)Math.Round(Decimal.Divide(row.ClosedCompleted * 100, row.TotalFamilies)); row.ClosedExternalAgencyFulfilledPer = (int)Math.Round(Decimal.Divide(row.ClosedExternalAgencyFulfilled * 100, row.TotalFamilies)); row.ClosedFamilyDeclineCasePlanPer = (int)Math.Round(Decimal.Divide(row.ClosedFamilyDeclineCasePlan * 100, row.TotalFamilies)); row.ClosedFamilyWithdrewPer = (int)Math.Round(Decimal.Divide(row.ClosedFamilyWithdrew * 100, row.TotalFamilies)); row.ClosedLackofFamilyEngagementPer = (int)Math.Round(Decimal.Divide(row.ClosedLackofFamilyEngagement * 100, row.TotalFamilies)); } //ds.Tables.Add(regionDT); return(regionlist); //return results; }