//[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;
}
